Thread: Re: Referential Integrity In PostgreSQL

Re: Referential Integrity In PostgreSQL

From
wieck@debis.com (Jan Wieck)
Date:
>
> Hi , Jan
>
> my name is Max .

Hi Max,

>
> I have contributed to SPI interface ,
> that with external Trigger try to make
> a referential integrity.
>
> If I can Help , in something ,
> I'm here .
>

    You're welcome.

    I've  CC'd  the  hackers list because we might get some ideas
    from there too (and to  surface  once  in  a  while  -  Bruce
    already missed me).

    Currently  I'm  very  busy  for  serious work so I don't find
    enough  spare  time  to  start  on  such  a  big  change   to
    PostgreSQL.   But  I'd like to give you an overview of what I
    have in mind so far so you can decide if you're able to help.

    Referential integrity (RI) is based on constraints defined in
    the schema of a database. There are some different  types  of
    constraints:

    1.  Uniqueness constraints.

    2.  Foreign key constraints that ensure that a key value used
        in  an  attribute  exists  in   another   relation.   One
        constraint  must ensure you're unable to INSERT/UPDATE to
        a value that doesn't  exist,  another  one  must  prevent
        DELETE  on  a  referenced  key item or that it is changed
        during UPDATE.

    3.  Cascading deletes that let rows referring to a key follow
        on DELETE silently.

    Even  if  not  defined in the standard (AFAIK) there could be
    others like letting references automatically follow on UPDATE
    to a key value.

    All constraints can be enabled and/or default to be deferred.
    That means, that the RI checks aren't performed when they are
    triggerd.  Instead,  they're checked at transaction end or if
    explicitly invoked by some special statement.  This is really
    important  because  someone  must  be able to setup cyclic RI
    checks that could never be satisfied if the checks  would  be
    performed  immediately.  The  major  problem  on  this is the
    amount of data affected until the checks must  be  performed.
    The number of statements executed, that trigger such deferred
    constraints,   shouldn't   be   limited.   And   one   single
    INSERT/UPDATE/DELETE could affect thousands of rows.

    Due  to these problems I thought, it might not be such a good
    idea to remember CTID's or the like to get back OLD/NEW  rows
    at the time the constraints are checked. Instead I planned to
    misuse the rule system for it. Unfortunately, the rule system
    has  damned  tricky problems itself when it comes to having-,
    distinct and other clauses and extremely  on  aggregates  and
    subselects. These problems would have to get fixed first.  So
    it's a solution that cannot be implemented right now.

    Fallback to CTID remembering though. There are  problems  too
    :-(.   Let's  enhance  the  trigger mechanism with a deferred
    feature. First this requires two additional  bool  attributes
    in  the  pg_trigger  relation  that  tell  if this trigger is
    deferrable and if it is deferred by default.  While at it  we
    should  add another bool that tells if the trigger is enabled
    (ALTER TRIGGER {ENABLE|DISABLE} trigger).

    Second we  need  an  internal  list  of  triggers,  that  are
    currently DEFINED AS DEFERRED. Either because they default to
    it, or the user explicitly asked to deferr it.

    Third we need an internal  list  of  triggers  that  must  be
    invoked later because at the time an event occured where they
    should have been triggered, they appeared in the  other  list
    and  their  execution  is  delayed  until  transaction end or
    explicit execution. This list must remember the  OID  of  the
    trigger   to  invoke  (to  identify  the  procedure  and  the
    arguments), the relation that  caused  the  trigger  and  the
    CTID's of the OLD and NEW row.

    That  last  list  could  grow  extremely!  Think of a trigger
    that's executing commands over SPI  which  in  turn  activate
    deferred  triggers.  Since  the order of trigger execution is
    very  important  for  RI,  I  can't   see   any   chance   to
    simplify/condense  this  information.  Thus it is 16 bytes at
    least per deferred trigger call (2 OID's plus  2  CTID's).  I
    think one or more temp files would fit best for this.

    A last tricky point is if one of a bunch of deferred triggers
    is explicitly called for execution. At this time, the entries
    for  it  in  the  temp  file(s) must get processed and marked
    executed (maybe by overwriting  the  triggers  OID  with  the
    invalid  OID)  while  other  trigger events still have to get
    recorded.

    Needless to say that reading thousands of those entries  just
    to find a few isn't good on performance. But better have this
    special case slow that dealing with hundreds of temp files or
    other overhead slowing down the usual case where ALL deferred
    triggers get called at transaction end.

    Trigger invocation is simple now - fetch the OLD and NEW rows
    by  CTID  and  execute  the  trigger  as  done by the trigger
    manager. Oh - well - vacuum shouldn't touch  relations  where
    deferred   triggers  are  outstanding.   Might  require  some
    special lock entry - Vadim?

    Did I miss something?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Re: Referential Integrity In PostgreSQL

From
Vadim Mikheev
Date:
Jan Wieck wrote:
> 
>     Third we need an internal  list  of  triggers  that  must  be
>     invoked later because at the time an event occured where they
>     should have been triggered, they appeared in the  other  list
>     and  their  execution  is  delayed  until  transaction end or
>     explicit execution. This list must remember the  OID  of  the
>     trigger   to  invoke  (to  identify  the  procedure  and  the
>     arguments), the relation that  caused  the  trigger  and  the
>     CTID's of the OLD and NEW row.
> 
>     That  last  list  could  grow  extremely!  Think of a trigger
>     that's executing commands over SPI  which  in  turn  activate
>     deferred  triggers.  Since  the order of trigger execution is
>     very  important  for  RI,  I  can't   see   any   chance   to
>     simplify/condense  this  information.  Thus it is 16 bytes at
>     least per deferred trigger call (2 OID's plus  2  CTID's).  I
>     think one or more temp files would fit best for this.
> 
>     A last tricky point is if one of a bunch of deferred triggers
>     is explicitly called for execution. At this time, the entries
>     for  it  in  the  temp  file(s) must get processed and marked
>     executed (maybe by overwriting  the  triggers  OID  with  the
>     invalid  OID)  while  other  trigger events still have to get
>     recorded.

I believe that things are much simpler.
For each deferable constraint (trigger) we have to remember
the LastCommandIdProccessedByConstraint. When the mode of
a constraint changes from defered to immediate (SET CONSTRAINT MODE), 
modified tuple will be fetched from WAL from down to up until
tuple modified by LastCommandIdProccessedByConstraint is fetched
and this is show stopper. Now we remember CommandId of 
SET CONSTRAINT MODE as new LastCommandIdProccessedByConstraint.
When LastCommandIdProccessedByConstraint is changed by
SET CONSTRAINT MODE DEFERRED we remeber this in flag to
update LastCommandIdProccessedByConstraint later with higher 
CommandId of first modification of triggered table (to reduce 
amount of data to read from WAL).

?

Vadim


Re: [HACKERS] Re: Referential Integrity In PostgreSQL

From
wieck@debis.com (Jan Wieck)
Date:
Vadim wrote:

> I believe that things are much simpler.
> For each deferable constraint (trigger) we have to remember
> the LastCommandIdProccessedByConstraint. When the mode of
> a constraint changes from defered to immediate (SET CONSTRAINT MODE),
> modified tuple will be fetched from WAL from down to up until
> tuple modified by LastCommandIdProccessedByConstraint is fetched
> and this is show stopper. Now we remember CommandId of
> SET CONSTRAINT MODE as new LastCommandIdProccessedByConstraint.
> When LastCommandIdProccessedByConstraint is changed by
> SET CONSTRAINT MODE DEFERRED we remeber this in flag to
> update LastCommandIdProccessedByConstraint later with higher
> CommandId of first modification of triggered table (to reduce
> amount of data to read from WAL).

Hmmm,

    I'm  not sure what side effects it could have if the triggers
    at the time of

        SET CONSTRAINTS c1, c2 IMMEDIATE

    arent fired in the same order they have been recorded -  must
    think  about that for a while. In that case I must be able to
    scan WAL from one command ID until another regardless of  the
    resultrelation. Is that possible?

    Another  issue  is  this: isn't it possible to run a database
    (or maybe an entire installation) without WAL? Does  it  make
    the  code better maintainable to have WAL and RI coupled that
    strong?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Re: Referential Integrity In PostgreSQL

From
Vadim Mikheev
Date:
Jan Wieck wrote:
> 
>     I'm  not sure what side effects it could have if the triggers
>     at the time of
> 
>         SET CONSTRAINTS c1, c2 IMMEDIATE
> 
>     arent fired in the same order they have been recorded -  must
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Did you mean - in the same order as tables were modified?

>     think  about that for a while. In that case I must be able to
>     scan WAL from one command ID until another regardless of  the
>     resultrelation. Is that possible?

WAL records are in the same order as tuples (regardless of
result relation) was modified. Example: UPDATE of T1
fires (immediate) after row trigger inserting tuple
into T2. WAL records: 
--> up
{old_T1_tuple version ID, new_T1_tuple version ID and values}
{new_T2_tuple ID and values}
...
T1 update record
T2 insert record
...
--> down

But records will be fetched from WAL in reverse order, from
down to up.

Does it matter?
Order of modifications made by UPDATE/DELETE is undefined.
Though, order has some sence for INSERT ... SELECT ORDER BY -:)
Nevertheless, I don't see in standard anything about order
of constraint checks.

BTW, I found what standard means by "immediate":
---  The checking of a constraint depends on its constraint mode within  the current SQL-transaction. If the constraint
modeis immediate,
 
|  then the constraint is effectively checked at the end of each
^^^^^^^^^^^^^^^^^^
|  ___________________________________________________________________
|  ANSI Only-SQL3
|  ___________________________________________________________________
|  SQL-statement S, unless S is executed because it is a <triggered  ^^^^^^^^^^^^^^^
|  SQL statement>, in which case, the constraint is effectively
|  checked at the end of the SQL-statement that is the root cause
|  of S.
---

And now about triggers (regardless of ROW or STATEMENT level!):
---  4.22.2  Execution of triggered actions
  The execution of triggered actions depends on the cursor mode of     the current SQL-transaction. If the cursor mode
isset to cascade  off, then the execution of the <triggered SQL statement>s is effec-  tively deferred until enacted
implicitlybe execution of a <commit   statement> or a <close statement>. Otherwise, the <triggered SQL  statement>s are
effectivelyexecuted either before or after the                                              ^^^^^^^^^^^^^^^^^^^
executionof each SQL-statement, as determined by the specified  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^  <trigger action
time>.
---

Hm. 

>     Another  issue  is  this: isn't it possible to run a database
>     (or maybe an entire installation) without WAL? Does  it  make

Do you worry about disk space? -:)
With archive mode off only log segments (currently, 64M each)
required by active transactions (which made some changes)
will present on disk.

>     the  code better maintainable to have WAL and RI coupled that
>     strong?

This doesn't add any complexity to WAL manager.

Vadim


Re: [HACKERS] Re: Referential Integrity In PostgreSQL

From
wieck@debis.com (Jan Wieck)
Date:
Vadim wrote:

> But records will be fetched from WAL in reverse order, from
> down to up.
>
> Does it matter?

    Might require to teach the WAL-manager to do it top-down too.
    And even then it might be better on performance  to  scan  my
    constraint-log  for events to the same tuple.  It has records
    of a fixed, very small size and fetching tuples by CTID  from
    the heap (direct block access) is required anyway because for
    delayed trigger invocation I neen OLD values too - and that's
    not in WAL if I read it right.

    But as I said I'd like to leave that coupling for later.

> BTW, I found what standard means by "immediate":
> ---
>    The checking of a constraint depends on its constraint mode within
>    the current SQL-transaction. If the constraint mode is immediate,
> |  then the constraint is effectively checked at the end of each
>                                               ^^^^^^^^^^^^^^^^^^
> |  ___________________________________________________________________
> |  ANSI Only-SQL3
> |  ___________________________________________________________________
> |  SQL-statement S, unless S is executed because it is a <triggered
>    ^^^^^^^^^^^^^^^
> |  SQL statement>, in which case, the constraint is effectively
> |  checked at the end of the SQL-statement that is the root cause
> |  of S.
> ---

    Ah  -  so  ALL  constraint-triggers must be AFTER <event> and
    deferred at least until the end of the USER-query.

>
> And now about triggers (regardless of ROW or STATEMENT level!):
> ---
>    4.22.2  Execution of triggered actions
>
>    The execution of triggered actions depends on the cursor mode of
>    the current SQL-transaction. If the cursor mode is set to cascade
>    off, then the execution of the <triggered SQL statement>s is effec-
>    tively deferred until enacted implicitly be execution of a <commit
>    statement> or a <close statement>. Otherwise, the <triggered SQL
>    statement>s are effectively executed either before or after the
>                                                ^^^^^^^^^^^^^^^^^^^
>    execution of each SQL-statement, as determined by the specified
>    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>    <trigger action time>.
> ---

    We do not have FOR UPDATE cursors. So (even if to be kept  in
    mind) there is no CURSOR mode to care for right now.

    Changing  BEFORE  triggers  to behave exactly like that would
    require to do the execution of the plan twice,  one  time  to
    fire  triggers,  another time to perform the action itself. I
    don't think that the perfomance cost  is  worth  this  little
    amount  of  accuracy.  Such  a  little  difference  should be
    mentioned in the product notes and period.

    AFTER triggers could simply be treated  half  like  IMMEDIATE
    constraints  -  deferred  until the end of a single statement
    (not user-query). So there are four times where the  deferred
    trigger  queue  is  run  (maybe  partially).  At the end of a
    statement, end of a user-query, at a syncpoint (not  sure  if
    we have them up to now) and end of transaction.

    Things are getting much clearer - Tnx.

>
> Do you worry about disk space? -:)
> With archive mode off only log segments (currently, 64M each)
> required by active transactions (which made some changes)
> will present on disk.

    Never  -  my motto is "don't force it - use a bigger hammer".
    But the above seems to be exactly like the  Oracle  behaviour
    where online-redolog's aren't affected by archive mode.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #