Re: [HACKERS] Re: Referential Integrity In PostgreSQL

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(at)debis(dot)com (Jan Wieck) #