Horton

Re: postgres_fdw: batch inserts vs. before row triggers

Matthias van de Meent <boekewurm+postgres@gmail.com>

View thread View on postgr.es
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, Etsuro Fujita <etsuro.fujita@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-08-03T22:35:13Z
Lists: pgsql-hackers 
On Wed, 3 Aug 2022 at 23:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.

Thank you for this pointer.

Looking around a bit, it seems like this behaviour for functions is
indeed documented in xfunc.sgml, but rendered docs page [0] does not
seem to mention triggers, nor does the triggers page link to that part
of the xfunc document. This makes it quite easy to overlook that this
is expected (?) behaviour for VOLATILE functions only.

Kind regards,

Matthias van de Meent

[0] https://www.postgresql.org/docs/current/xfunc-volatility.html

Commits

  1. Remove new structure member from ResultRelInfo.

  2. Fix handling of pending inserts in nodeModifyTable.c.

  3. postgres_fdw: Disable batch insertion when there are WCO constraints.

  4. postgres_fdw: Disable batch insert when BEFORE ROW INSERT triggers exist.

  5. Implement support for bulk inserts in postgres_fdw