[SQL] diary constraints
Hi folks I know this has been discussed in the past, but no amount of keywords has returned anything from the archives. I want to create a courtesy car diary diary system where I have a table containing all of the cortesy cars in the pool, and then an allocation table which has two timestamps, one for the start date/time and one for the return date/time. How do I go about creating constraints on inserts and updates to ensure that a) the finish is after the start b) two allocations for a single vehicle don't overlap. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] diary constraints
Hi Gary, I've actually just done the same thing - but for renting property. I've implemented the constraint as a trigger (Before insert/update, for each row), that first checks if the start_date is < end_date, and then performs a select on the bookings table using the OVERLAPS function. If there are more than 0 records returned, an exception is raised. I've included the code below. You shouldn't need too many changes to adapt it to your needs! /* This trigger function is responsible for ensuring temporal integrity within the calendar_entries table (And it's children). It ensures that only entries with no overlapping entries. */ BEGIN /* First, check that the start_date > end_date */ IF NEW.start_date > NEW.end_date THEN RAISE EXCEPTION 'ERROR: start_date must not be greater than end_date'; END IF; IF EXISTS ( SELECT 1 FROM calendar_entries WHERE ((start_date,end_date) OVERLAPS (NEW.start_date, NEW.end_date)) AND property_id = NEW.property_id LIMIT 1 ) THEN RAISE EXCEPTION 'cannot add booking - overlapping calendar entries detected'; END IF; RETURN NEW; END; This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] diary constraints
On 8/23/05, Gary Stainburn <[EMAIL PROTECTED]> wrote: Hi folksI know this has been discussed in the past, but no amount of keywordshas returned anything from the archives.I want to create a courtesy car diary diary system where I have a tablecontaining all of the cortesy cars in the pool, and then an allocation table which has two timestamps, one for the start date/time and one forthe return date/time.How do I go about creating constraints on inserts and updates to ensurethata) the finish is after the start check constraint like -> "finish_time > start_time" this will do b) two allocations for a single vehicle don't overlap. Use "overlaps" function in a trigger to validate the above. --Gary StainburnThis email does not contain private or confidential material as it may be snooped on by interested government parties for unknownand undisclosed purposes - Regulation of Investigatory Powers Act, 2000---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [SQL] diary constraints
On Aug 23, 2005, at 5:33 PM, Gary Stainburn wrote: I want to create a courtesy car diary diary system where I have a table containing all of the cortesy cars in the pool, and then an allocation table which has two timestamps, one for the start date/time and one for the return date/time. How do I go about creating constraints on inserts and updates to ensure that a) the finish is after the start b) two allocations for a single vehicle don't overlap. This is an interesting problem. You might want to take a look at this book, previously mentioned on the one of the lists (by George Essig, I believe): Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass The book is out of print, but the author has made the PDF available on his website at: http://www.cs.arizona.edu/people/rts/tdbbook.pdf Hope this helps! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] diary constraints
a) the finish is after the start well, finish > start b) two allocations for a single vehicle don't overlap. this one is a bit tricky ! - Check that there is no allocation in the table whose time period start, end includes either the start of the end of the reservation to insert, and that the time period of the reservation to insert does not contain either the start or end of any reservation in the table. This is 4 selects, playing with order by limit 1 and indexes, it will be fast. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] diary constraints
am 23.08.2005, um 9:33:58 +0100 mailte Gary Stainburn folgendes: > Hi folks > > I know this has been discussed in the past, but no amount of keywords > has returned anything from the archives. > > I want to create a courtesy car diary diary system where I have a table > containing all of the cortesy cars in the pool, and then an allocation > table which has two timestamps, one for the start date/time and one for > the return date/time. > > How do I go about creating constraints on inserts and updates to ensure > that > > a) the finish is after the start with a check-constraint like this: create table foobar (t1 timestamp, t2 timestamp check (t2>t1)); > b) two allocations for a single vehicle don't overlap. possibly with a trigger. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem calling stored procedure
Thank you everybody for your help - You were indeed correct - Removing the DATE worked, and ran the function from the client (Although now it fails in the debugger). I'm a little concerned why this didn't work in EMS PostgreSQL Manager debugger - I'll raise a bug tracker regarding this. However, I'm now having another problem regarding getting results back from the SELECT INTO query. OPEN cur_overlap FOR SELECT *, pg_class.RELNAME AS table FROM calendar_entries WHERE (start_date, end_date) OVERLAP (new_start_date, new_end_date) AND property_id = X AND pg_class.oid = tableoid; When I run the query directly in psql (Less the OPEN cur_overlap FOR, and substituting in my test data '2005-09-13', '2005-09-15'), I get one row. However, when the query runs in the function it returns 0 rows. The only thing I can think of is that when the query runs in the psql I get: NOTICE: added missing FROM-clause entry for table "pg_class" I understand why this is happening, but don't know how I would go about re-writing the query to explicitly reference pg_class - I can't write calendar_entries.table_oid, because that changes the meaning of the query. Is this what causing the problem? When I was using the DATE casts in PostgreSQL Manager and stepping through in the debugger, the same query returned 1 row, as expected. I'm very confused and so would appreciate any advice that anyone can offer! This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problem calling stored procedure
<[EMAIL PROTECTED]> writes: > OPEN cur_overlap FOR SELECT *, pg_class.RELNAME AS table FROM > calendar_entries WHERE (start_date, end_date) OVERLAP (new_start_date, > new_end_date) AND property_id = X AND pg_class.oid = tableoid; > The only thing I can think of is that when the query runs in the psql I get: > NOTICE: added missing FROM-clause entry for table "pg_class" > I understand why this is happening, but don't know how I would go > about re-writing the query to explicitly reference pg_class - I can't > write calendar_entries.table_oid, because that changes the meaning of > the query. How so? It'd be the same as far as I can see. However, you could avoid any explicit use of pg_class by using the regclass type instead: OPEN cur_overlap FOR SELECT *, tableoid::regclass AS table FROM calendar_entries WHERE (start_date, end_date) OVERLAP (new_start_date, new_end_date) AND property_id = X; As far as the reason for the difference between function execution and manual execution: check for unintended variable substitutions. Which words in the query match variable names in the plpgsql function? Are those only the ones you intended? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Tuple insert missing query in ongoing transaction
Dear Gurus, I know this is the typical case of transaction use, I just seem to lack the appropriate education of what exactly happens and whether I may be able to detect it. I have two queries, one affecting the other. 1. INSERT INTO barcode. A BEFORE INSERT/UPDATE trigger checks if there's an appropriate tuple in table "shift" for this tuple (matching day, shift-of-the-day and workplace) and denormalizes fields. 2. INSERT INTO shift. An AFTER INSERT/UPDATE/DELETE trigger updates rows in table barcode, forcing the abovementioned check for shift. The second one is a long process, taking about 20 sec to finish. Imagine the following scenario: x:xx:00 INSERT INTO shift. Transaction "S" begins. It updates several rows, but not the not-yet-inserted row. x:xx:10 INSERT INTO barcode (... appropriate for above-inserted shift ...) Transaction "B" begins. It checks but does not find the corresponding shift. x:xx:11 Transaction "B" ends. x:xx:20 Transaction "S" ends. In such scenarios, sometimes we get "Deadlock detected." That's OK since the transactions actually cross each other's way. But not always. In about 100 inserts, now we have the first case that did not show any trace of that something went wrong. 1. Is there a way to detect such "crossing" transactions? 2. Is there a thorough article on deadlocks, how and when do they happen? 3. Maybe a section of the postgresql doc clarifying when do tuples get locked? TIA, -- G. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem calling stored procedure
Hi Tom, Thanks for the advice; But I'm still can't get it working. I only have three variables: prop_id, new_start_date, new_end_date, and into_table. I've used the regclass type, and I've tried changing new_start_date and new_end_date to nsd and ned respectively, but still no luck. If there any other avenue of investigation you can think of? I've tried re-writing it as a dynamic query, but Postgres doesn't seem to like that! Thanks once again, Neil. This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problem calling stored procedure
Just a quick addendum; I'm finding the number of rows by using GET DIAGNOSTICS num_entries = ROW_COUNT after I open the query, and then branching depending on this value - Is there any issue with using this in conjunction with cursors and OPEN FOR SELECT? I've just tried using OPEN FOR EXECUTE and quoting the query string, but still no luck - num_entries is still 0. Thanks again, Neil. This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem calling stored procedure
Ok, the whole thing is done and dusted - Thank you everybody for your
input. Apologies if I sometimes missed the obvious. For the sake of
anyone having the same problems and happens across this post, I learned
that:
- The result of a cursor assignment cannot be row counted, unless you
iterate through it
- Fetching a row at the end of a cursor returns an empty set
- Fancy IDEs can be deceiving, especially when trying to debug a
function.
Included below is the finished procedure.
Thanks again,
Neil.
-Original Message-
DECLARE
cur_overlap refcursor;
new_id INTEGER;
row_one record;
row_two record;
clashes record;
BEGIN
LOCK TABLE calendar_entries IN EXCLUSIVE MODE;
SELECT INTO clashes * FROM bookings WHERE (start_date, end_date)
OVERLAPS (new_start_date - interval '1 day',new_end_date + interval '1
day') AND property_id = prop_id LIMIT 1;
IF NOT FOUND THEN
DELETE FROM calendar_entries WHERE property_id = prop_id
AND (start_date >= new_start_date) AND (end_date <= new_end_date);
OPEN cur_overlap FOR EXECUTE 'SELECT *, pg_class.relname
AS table FROM calendar_entries, pg_class WHERE (start_date, end_date)
OVERLAPS (DATE ''' || new_start_date || ''' - interval ''2 days'', DATE
''' || new_end_date || ''' + interval ''2 days'') AND property_id = ' ||
prop_id || ' AND pg_class.oid = calendar_entries.tableoid ORDER BY
start_date';
FETCH cur_overlap INTO row_one;
FETCH cur_overlap INTO row_two;
IF (row_two.id IS NULL) THEN
/* We're overlapping one row. Either we're enveloped by a
single row,
or we have one row overlapping either the start date or
the end
date.
*/
IF (row_one.start_date <= new_start_date) AND
(row_one.end_date >= new_end_date) THEN
/* We're enveloped. The enveloping row needs to be split
in to
two so that we can insert ourselves. */
IF row_one.table = into_table THEN
/* This period is already marked appropriately. Do
nothing. */
RETURN row_one.id;
ELSE
/* We need to perform a split/insert.
1. Adjust the end date of the enveloping row to
the new
start - 1 day.
2. Insert a new row as the same type as the
enveloping row
from new_end_date + 1 to the existing end date.
3. Insert the new row in to the required table */
EXECUTE 'UPDATE ' || row_one.table || ' SET end_date
= DATE ''' || new_start_date || ''' - interval ''1 day'' WHERE id = ' ||
row_one.id;
EXECUTE 'INSERT INTO ' || row_one.table || '
(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_end_date || ''' + interval ''1 day'', DATE ''' ||
row_one.end_date || ''')';
EXECUTE 'INSERT INTO ' || into_table || '
(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_start_date || ''', DATE ''' || new_end_date || ''')';
SELECT currval('calendar_id_seq') INTO new_id;
RETURN new_id;
END IF;
ELSIF row_one.start_date <= new_start_date THEN
/* This row is earlier than the proposed
period - It's overlapping
our start date - But is it of the same type? */
IF row_one.table = into_table THEN
/* A single row overlapping the start only and of
the same
type - Update the end date and return the
existing row ID */
EXECUTE 'UPDATE ' || into_table
|| ' SET end_date = DATE ''' || new_end_date || ''' WHERE id = ' ||
row_one.id;
RETURN row_one.id;
ELSE
/* Single row, overlapping the start, and of a
different type.
Trim back the existing row and Insert and return
newly generated ID. */
EXECUTE 'INSERT INTO ' || into_table ||
'(property_id, start_date, end_date) VALUES (' || prop_id || ', DATE '''
|| new_start_date || ''', DATE ''' || new_end_date || ''')';
SELECT
currval('calendar_id_seq') INTO new_id;
RETURN new_id;
END IF;
ELSIF row_one.start_date > new_end_date THEN
/* This period is after the proposed
period */
IF row_one.table = into_table THEN
/* Single row, ove
