Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option forconstraints - Mailing list pgsql-hackers

From Andreas Joseph Krogh
Subject Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option forconstraints
Date
Msg-id VisenaEmail.40.b04b9c0cd0de94e.15ee3d81ba4@tc7-visena
Whole thread Raw
In response to [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints  (Nico Williams <nico@cryptonector.com>)
Responses Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints
Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints
List pgsql-hackers
På tirsdag 03. oktober 2017 kl. 21:51:30, skrev Nico Williams <nico@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_id IS NULL DEFERRABLE INITIALLY DEFERRED;
 
Are there any plans to support this?
 
Thanks.
 
--
Andreas Joseph Krogh
 

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: [HACKERS] [sqlsmith] stuck spinlock in pg_stat_get_wal_receiverafter OOM
Next
From: Nico Williams
Date:
Subject: Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints