På tirsdag 03. oktober 2017 kl. 21:51:30, skrev Nico Williams < n...@cryptonector.com <mailto:n...@cryptonector.com>>: Attached are patches to add an ALWAYS DEFERRED option to CONSTRAINTs and CONSTRAINT TRIGGERs, meaning: SET CONSTRAINTS .. IMMEDIATE will not make immediate any constraint/trigger that is declared as ALWAYS DEFERRED.
I.e., the opposite of NOT DEFERRED. Perhaps I should make this NOT IMMEDIATE? Making it NOT IMMEDIATE has the benefit of not having to change the precedence of ALWAYS to avoid a shift/reduce conflict... It may also be more in keeping with NOT DEFERRED. Motivation: - I have trigger procedures that must run at the end of the transaction (after the last statement prior to COMMIT sent by the client/user), which I make DEFERRABLE, INITIALLY DEFERRED CONSTRAINT TRIGGERs out of, but SET CONSTRAINTS can be used to foil my triggers. I have written SQL code to detect that constraint triggers have fired too soon, but I'd rather not need it. - Symmetry. If we can have NOT DEFERRABLE constraints, why not also NOT IMMEDIABLE? :) Naturally "immediable" is not a word, but you get the point. - To learn my way around PostgreSQL source code in preparation for other contributions. Anyways, this patch is NOT passing tests at the moment, and I'm not sure why. I'm sure I can figure it out, but first I need to understand the failures. E.g., I see this sort of difference: \d testschema.test_index1 Index "testschema.test_index1" Column | Type | Definition --------+--------+------------ id | bigint | id -btree, for table "testschema.test_default_tab" +f, for table "testschema.btree", predicate (test_default_tab) which means, I think, that I've screwed up in src/bin/psql/describe.c, don't it's not obvious to me yet how. Some questions for experienced PostgreSQL developers: Q0: Is this sort of patch welcomed? Q1: Should new columns for pg_catalog.pg_constraint go at the end, or may they be added in the middle? Q2: Can I add new columns to information_schema tables, or are there standards-compliance issues with that? Q3: Is the C-style for PG documented somewhere? (sorry if I missed this) Q4: Any ideas what I'm doing wrong in this patch series? Nico +1. While we're in deferrable constraints land...; I even more often need deferrable conditional unique-indexes. In PG you now may have: ALTER TABLE email_folder ADD CONSTRAINT some_uk UNIQUE (owner_id, folder_type, name) DEFERRABLE INITIALLY DEFERRED; But this isn't supported: CREATE UNIQUE INDEX some_uk ON email_folder(owner_id, folder_type, name) WHERE parent_idIS NULL DEFERRABLE INITIALLY DEFERRED; Are there any plans to support this? Thanks. -- Andreas Joseph Krogh
