[SQL] Foreign key reference counting strategy?

2006-10-14 Thread Joost Kraaijeveld
Hi, Is there a strategy to implement reference counting for foreign keys so that if the last reference to the key is deleted, the record is deleted also? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.as

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Markus Schaber
Hi, Joost, Joost Kraaijeveld wrote: > Is there a strategy to implement reference counting for foreign keys so > that if the last reference to the key is deleted, the record is deleted > also? Create an "after delete" trigger on the referencing table that checks whether there still are records wi

Re: [SQL] Assigning a timestamp without timezone to a timestamp

2006-10-14 Thread chrisj
Hi Andrew, Finally got around to trying to implement your solution. It works fine with fixed offset timezones, but when I try it with EST5EDT I get the following: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as

Re: [SQL] Assigning a timestamp without timezone to a timestamp

2006-10-14 Thread Tom Lane
chrisj <[EMAIL PROTECTED]> writes: > It works fine with fixed offset timezones, but when I try it with EST5EDT > I get the following: > ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 > 09:20:00 EST5EDT" Try it with "America/New_York". The datetime parser seems to thin

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote: > Joost Kraaijeveld wrote: > > Is there a strategy to implement reference counting for foreign keys so > > that if the last reference to the key is deleted, the record is deleted > > also? > > Create an "after delete" trigger on the r

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote: >> Create an "after delete" trigger on the referencing table that checks >> whether there still are records with the same key (IF EXISTS()), and >> deletes the referenced row otherwise. > In

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote: > >> Create an "after delete" trigger on the referencing table that checks > >> whether there still are records with the same key

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote: >> No, I don't think so, because the DELETE will already be holding >> exclusive lock on the doomed PK row, which any would-be inserters of >> matching FK rows will be blocked on. AFAICS the DELETE

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
On Sat, Oct 14, 2006 at 08:20:10PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Unless the inserters got there first. I just tested both ways; if > > the insert acquires the lock first then the delete fails, but if the > > delete acquires the lock first then the insert fai

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > My intent wasn't to assert that IF EXISTS adds a new way for the > DELETE to fail. I was just pointing out that the test "if no > referencing rows exist then delete the referenced row" isn't > foolproof, viz., the DELETE can fail even though IF EXISTS sai