Hi Hackers
I have noticed an issue with deferrable triggers not always being deferrable. Eg: alter table subsession add constraint fk_subsession_session foreign key (session_id) references session (session_id) deferrable; alter table subsession2 add constraint fk_subsession2_session foreign key (session_id) references session (session_id) on delete restrict on update restrict deferrable; select tgconstrname, tgtype, tgenabled, tgdeferrable, tginitdeferred from pg_trigger where tgisconstraint; tgconstrname | tgtype | tgenabled | tgdeferrable | tginitdeferred ------------------------+--------+-----------+--------------+---- fk_subsession_session | 21 | t | t | f fk_subsession_session | 9 | t | t | f fk_subsession_session | 17 | t | t | f fk_subsession2_session | 21 | t | t | f fk_subsession2_session | 9 | t | f | f fk_subsession2_session | 17 | t | f | f 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? Regards Grant ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html