[SQL] Foreign key reference counting strategy?
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.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Foreign key reference counting strategy?
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 with the same key (IF EXISTS()), and deletes the referenced row otherwise. It won't hurt to have an index on the referencing column, for speed reasons. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Assigning a timestamp without timezone to a timestamp
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 timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 09:20:00 EST5EDT" when I change timezone_ch to EST it works like a charm: protocal2=> select start_datetime,cast(cast(cast(start_datetime as timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as timestamp(0) with time zone) from reservation A, location B where appt_key = 7 and locn_key = 102 ; start_datetime | timestamptz + 2006-07-13 09:20:00-04 | 2006-07-13 10:20:00-04 (1 row) Any thoughts? On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote: > If only all time zones were fixed offset timezones life would be so much > simpler. Indeed. > Unfortunately the main area of deployment of my app will beToronto which > is > on EDT which is not a fixed offsets timezone. I hope/assume your solution > works with "EDT" instead of "-3", I will test it soon. Should do, although you'll need more than EDT. EDT is also fixed: it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need to improve your schema, though, because you had char(3) there, and not all time zones are 3 characters long). But to answer your question, yes, it works. I just tried it. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(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 -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6815181 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Assigning a timestamp without timezone to a timestamp
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 think that a timezone name shouldn't contain digits ... which is bogus, but we'll have to think carefully about how to improve it ... regards, tom lane ---(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] Foreign key reference counting strategy?
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 referencing table that checks > whether there still are records with the same key (IF EXISTS()), and > deletes the referenced row otherwise. In a concurrent environment that delete can fail with a foreign key constraint violation because IF EXISTS won't see uncommitted changes in other transactions. If changes in another transaction reference the same key then the delete will block until the other transaction commits or rolls back; if the other transaction commits then the delete will fail. In PL/pgSQL you can trap that failure with an EXCEPTION clause that catches foreign_key_violation. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Foreign key reference counting strategy?
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 a concurrent environment that delete can fail with a foreign key > constraint violation because IF EXISTS won't see uncommitted changes > in other transactions. 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 should go through and then the inserters will fail. Which is not necessarily better of course ... but if you want logic like this, you probably should have code to make the FK inserters deal with no-such-key failures. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Foreign key reference counting strategy?
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 (IF EXISTS()), and > >> deletes the referenced row otherwise. > > > In a concurrent environment that delete can fail with a foreign key > > constraint violation because IF EXISTS won't see uncommitted changes > > in other transactions. > > 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 should go > through and then the inserters will fail. 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 fails. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Foreign key reference counting strategy?
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 should go >> through and then the inserters will fail. > 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 fails. Well, if the inserters get a lock on the PK row before the DELETE does, then of course. I was just disputing the assertion that doing IF EXISTS in an after trigger would add a new way for the DELETE to fail. At that point, any uncommitted inserts must be blocked waiting for the delete. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Foreign key reference counting strategy?
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 fails. > > Well, if the inserters get a lock on the PK row before the DELETE does, > then of course. I was just disputing the assertion that doing IF EXISTS > in an after trigger would add a new way for the DELETE to fail. 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 said there were no referencing rows. -- Michael Fuhr ---(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] Foreign key reference counting strategy?
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 said > there were no referencing rows. Actually, it's me that was confused --- I was thinking in terms of the original DELETE being done on the PK row, but of course that's not the case here --- the original DELETE would be on some FK row. It might be possible to make it work by adding a SELECT FOR UPDATE that locks the PK row in the trigger before doing the IF EXISTS test, but I think that that could create deadlock failures. You'd basically be making the transaction upgrade a share row lock to an exclusive lock, and that's generally unsafe. If you could ensure that your trigger runs before the RI integrity trigger, maybe it would work... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
