Re: [HACKERS] Re: Referential Integrity In PostgreSQL - Mailing list pgsql-hackers

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) #