Re: [HACKERS] Re: Referential Integrity In PostgreSQL

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 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.
---

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>.
---

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