Horton

Re: postgres_fdw: batch inserts vs. before row triggers

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
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>
Date: 2022-08-03T21:57:57Z
Lists: pgsql-hackers 
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

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