From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
        Etsuro Fujita <etsuro.fujita@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: postgres_fdw: batch inserts vs. before row triggers
In-reply-to: 
 <CAEze2WgfGsnpfw9rPZ=RbOx6wXqm7GBXL_5rkCRx5mN_1fpRjQ@mail.gmail.com>
References: 
 <CAPmGK16_uPqsmgK0-LpLSUk54_BoK13bPrhxhfjSoSTVz414hA@mail.gmail.com>
 <CAPmGK16RDXrFm-XoNkbigpbtJm4RYPZP-nb4NvcrXHkq-Uu24w@mail.gmail.com>
 <c85d5bfb-14f8-63b8-5a9c-9ea7db031b4b@enterprisedb.com>
 <CAEze2WgfGsnpfw9rPZ=RbOx6wXqm7GBXL_5rkCRx5mN_1fpRjQ@mail.gmail.com>
Comments: In-reply-to Matthias van de Meent <boekewurm+postgres@gmail.com>
	message dated "Wed, 03 Aug 2022 23:52:02 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3245063.1659563877.1@sss.pgh.pa.us>
Date: Wed, 03 Aug 2022 17:57:57 -0400
Message-ID: <3245064.1659563877@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3245064.1659563877%40sss.pgh.pa.us>
Precedence: bulk

Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> I don't have a current version of the SQL spec, but one preliminary
> version of SQL:2012 I retrieved via the wiki details that all BEFORE
> triggers on INSERT/UPDATE/DELETE statements are all executed before
> _any_ of that statements' affected data is modified.
> ...
> I don't know about the semantics of triggers in the latest SQL
> standard versions, but based on that sample it seems like we're
> non-compliant on BEFORE trigger behaviour, and it doesn't seem like
> it's documented in the trigger documentation.

I think we're compliant if you declare the trigger functions as
stable (or immutable, but in any case where this matters, I think
you'd be lying).  They'll then run with the snapshot of the calling
query, in which those updates are not yet visible.

This is documented somewhere, but maybe not anywhere near triggers.

			regards, tom lane