Question 389717 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL after-trigger for table constraint

In PostgreSQL, I am not sure I understand after-triggers:

  • Within an AFTER insert or update FOR EACH STATEMENT trigger (function), is it correct that (contrary to e.g. MSSQL), if I query the trigger’s table, I see the data before any changes are applied to the table?

  • And, if that is correct, how can I enforce a constraint over a whole table (for example, that the sum over some column does not exceed a certain value, or that at most N records have some boolean column set to true, etc.)?

The only approach I have been able to concoct is querying the table joining it with the deleted and inserted records in order to recover what the data would be after the statement, but I am new to PG and this seems a bit too much work for such a common requirement.

I have tried reading the docs as well as searching the web but I could not find a clear answer to the above, in fact not even an example.

Thanks for any clarification/advice.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Within an AFTER insert [trigger], is it correct that, if I query the trigger's table, I see the data before any changes are applied to the table?

    No, that rather depends on the volatility category of the trigger function: a VOLATILE function will see the table changes, a STABLE or IMMUTABLE function will not. -- Indeed, I had declared my function STABLE, so I was not seeing the table changes.

    See 38.7. Function Volatility Categories for more details. Here is an extract:

    For functions written in SQL or in any of the standard procedural languages, there is a second important property determined by the category, volatility namely the visibility of any data changes that have been made by the SQL command that is calling the function. A VOLATILE function will see such changes, a STABLE or IMMUTABLE function will not. This behavior is implemented using the snapshotting behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

    Also, SQL Commands / CREATE FUNCTION mentions it:

    [STABLE] is inappropriate for AFTER triggers that wish to query rows modified by the current command.

    Finally, I have put together a basic example on DB Fiddle (cannot do much on DB Fiddle, e.g. RAISE NOTICE does not write anywhere, so it's very minimal).


  2. 0

    In an AFTER trigger you already see the effects of the triggering statement.

    But that won’t help you: you cannot reliably enforce a constraint over the whole table in a trigger because you don’t see uncommitted data from concurrent transactions. So there is always a race condition with using triggers to enforce such a constraint unless you use one of the following remedies

    • use the SERIALIZABEL isolation level

    • use heavy locking to prevent concurrent transactions

    See this article for a more detailed explanation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search