[HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints - Mailing list pgsql-hackers

From Nico Williams
Subject [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints
Date
Msg-id 20171004175627.GR1251@localhost
Whole thread Raw
In response to [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints  (Nico Williams <nico@cryptonector.com>)
Responses Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints
Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints
List pgsql-hackers
[make check-world passes.  Tests and docs included.  Should be ready for
code review.]

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.

Motivation:

 - Security.

   One may have triggers they need to always be deferred and they
   cannot give direct PG access because of SET CONSTRAINTS .. IMMEDIATE.

   I have such triggers 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.

   I have written SQL code to detect that constraint triggers have fired
   too soon, but I'd rather not need it as it does slow things down (it
   uses DDL event triggers and per-table triggers).

   Making it easier to write secure code DEFERRED CONSTRAINT TRIGGERs
   seems like a good idea to me.

 - Symmetry.

   Not using NOT DEFERRABLE is not the inverse of NOT DEFERRABLE.  There
   is no inverse at this time.

   If we can have NOT DEFERRABLE constraints, why not also the inverse,
   a constraint that cannot be made IMMEDIATE with SET CONSTRAINTs?


I've *not* cleaned up C style issues in surrounding -- I'm not sure
if that's desired.  Not cleaning up makes it easier to see what I
changed.

Some questions for experienced PostgreSQL developers:

Q0: Is this sort of patch welcomed?

Q1: Should new columns for pg_catalog tables go at the end, or may they
    be added in the middle?

    FYI, I'm adding them in the middle, so they are next to related
    columns.

Q2: Can I add new columns to information_schema tables, or are there
    standards-compliance issues with that?

    This is done in the second patch, and it can be dropped safely.

Q3: Perhaps I should make this NOT IMMEDIATE rather than ALWAYS DEFERRED?
    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.  Thoughts?

Nico
--

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Attachment

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: [HACKERS] Re: [BUGS] Re: [PATCH] BUG #13416: Postgres >= 9.3 doesn't useoptimized shared memory on Solaris anymore
Next
From: Nico Williams
Date:
Subject: Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints