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

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