Re: [HACKERS] deferrable triggers
Tom Lane t...@sss.pgh.pa.us wrote: However, it's not a commit time trigger exactly -- keep in mind that SET CONSTRAINTS can override the trigger's own timing specification. Hmm. Is there a way for trigger code to check whether it is running deferred (at transaction commit time) versus immediate? (If not, I see writing a small patch in my future to allow it.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] deferrable triggers
Kevin Grittner kevin.gritt...@wicourts.gov writes: In a discussion on irc today, someone had a need to confirm that a business rule (this table has two rows for every related one row in another table) was true at commit time. I innocently suggested a deferrable (and deferred) trigger. It was pointed out that the docs: http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html#AEN68703 say: | This can only be specified for constraint triggers. Hmm. Somehow I had gotten hold of deferrable triggers as a way to solve a problem in moving our replication from our Java framework to PostgreSQL triggers. So we are using a hand-written DEFERRABLE trigger in production, with it apparently working as intended. What do you mean by hand-written DEFERRABLE trigger? AFAICS from the grammar, DEFERRABLE and related attributes can only be specified when you write CREATE CONSTRAINT TRIGGER, so the documentation's statement appears correct to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] deferrable triggers
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: In a discussion on irc today, someone had a need to confirm that a business rule (this table has two rows for every related one row in another table) was true at commit time. I innocently suggested a deferrable (and deferred) trigger. It was pointed out that the docs: http://www.postgresql.org/docs/9.1/interactive/sql-createtrigger.html#AEN68703 say: | This can only be specified for constraint triggers. Hmm. Somehow I had gotten hold of deferrable triggers as a way to solve a problem in moving our replication from our Java framework to PostgreSQL triggers. So we are using a hand-written DEFERRABLE trigger in production, with it apparently working as intended. What do you mean by hand-written DEFERRABLE trigger? AFAICS from the grammar, DEFERRABLE and related attributes can only be specified when you write CREATE CONSTRAINT TRIGGER, so the documentation's statement appears correct to me. Ah, I had forgotten that I had to use the CONSTRAINT keyword in the trigger definition; the sentence in the docs makes more sense now. I wrote a plpgsql trigger function and created a deferrable initially deferred constraint trigger which referenced it. Is that a reasonable thing to do if you need a commit-time trigger based on some particular action against a particular table? Would it be a reasonable way for the person on irc to enforce the business rule mentioned above? If so, I think there's room for the docs to clarify that CONSTRAINT TRIGGERs are usable for things other than implementing declarative constraints, which was the (apparently contagious) interpretation of the person in irc. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] deferrable triggers
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: What do you mean by hand-written DEFERRABLE trigger? Ah, I had forgotten that I had to use the CONSTRAINT keyword in the trigger definition; the sentence in the docs makes more sense now. I wrote a plpgsql trigger function and created a deferrable initially deferred constraint trigger which referenced it. Is that a reasonable thing to do if you need a commit-time trigger based on some particular action against a particular table? Would it be a reasonable way for the person on irc to enforce the business rule mentioned above? Sure. The reason we decided to document CREATE CONSTRAINT TRIGGER was exactly that people sometimes need to use it for things besides foreign keys. However, it's not a commit time trigger exactly -- keep in mind that SET CONSTRAINTS can override the trigger's own timing specification. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deferrable triggers
On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding any value and can be omitted. In my case, the SQL is generated for me by PowerDesigner. My workaround is to tweak the PowerDesigner output definition to not include this line. I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or am I misunderstanding something? Restrict is not the default, there is a difference between restrict and no action. In fact I believe the main point of restrict (which IIRC was added for sql99) is to allow you to have a deferred constraint that can do immediate checking of validity on pk changes. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Deferrable triggers
On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote: On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding any value and can be omitted. In my case, the SQL is generated for me by PowerDesigner. My workaround is to tweak the PowerDesigner output definition to not include this line. I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or am I misunderstanding something? Restrict is not the default, there is a difference between restrict and no action. In fact I believe the main point of restrict (which IIRC was added for sql99) is to allow you to have a deferred constraint that can do immediate checking of validity on pk changes. I was basing my reasoning on the CREATE TABLE documentation which says: NO ACTION Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the default action. RESTRICT Same as NO ACTION. So as you pointed out, RESTRICT is not the default, but according to the docs NO ACTION is the default and RESTRICT is the same as NO ACTION. Is the difference between the two documented anywhere? Regards Grant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Deferrable triggers
On Thu, 7 Nov 2003, Grant McLean wrote: On Fri, 2003-11-07 at 11:31, Stephan Szabo wrote: On Thu, 7 Nov 2003, Grant McLean wrote: So it would seem that if I include the clauses: on delete restrict on update restrict Then the 'deferrable' which follows is only applied to creates and not to updates or deletes. Since 'restrict' is the default, the clauses aren't adding any value and can be omitted. In my case, the SQL is generated for me by PowerDesigner. My workaround is to tweak the PowerDesigner output definition to not include this line. I have seen this behaviour in both 7.2 and 7.3. Is it a bug? Or am I misunderstanding something? Restrict is not the default, there is a difference between restrict and no action. In fact I believe the main point of restrict (which IIRC was added for sql99) is to allow you to have a deferred constraint that can do immediate checking of validity on pk changes. I was basing my reasoning on the CREATE TABLE documentation which says: NO ACTION Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the default action. RESTRICT Same as NO ACTION. So as you pointed out, RESTRICT is not the default, but according to the docs NO ACTION is the default and RESTRICT is the same as NO ACTION. Is the difference between the two documented anywhere? Hmm, I don't think so actually. I'm surprised that we hadn't had that mistake pointed out before. The restrict entry should mention the fact that it's non-deferring. To -hackers: Is it still safe to send small documentation patches for 7.4 at this point? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Deferrable triggers
Stephan Szabo [EMAIL PROTECTED] writes: To -hackers: Is it still safe to send small documentation patches for 7.4 at this point? Of course. Docs patches are fair game up till release (although I think Peter wants us to minimize edits to the reference pages, because regenerating the man pages is a bit of a PITA). regards, tom lane ---(end of broadcast)--- TIP 3: 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
