Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Fri, 31 Oct 2025 16:02:05 -0300
Message-Id: <DDWQNL7GDSXK.2OK2O16JOATAE@gmail.com>
Cc: "Tomas Vondra" <tomas@vondra.me>, <pgsql-hackers@postgresql.org>
Subject: Re: postgres_fdw: Use COPY to speed up batch inserts
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Andrew Dunstan" <andrew@dunslane.net>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>, "jian he" <jian.universality@gmail.com>
References: <DDIZJ217OUDK.2R5WE4OGL5PTY@gmail.com>
 <af99c828-ef24-4355-92d5-b5f631cda9c9@vondra.me>
 <CAFY6G8ePwjT8GiJX1AK5FDMhfq-sOnny6optgTPg98HQw7oJ0g@mail.gmail.com>
 <DDP9DDKVIYQ0.2LTDOCAWGG9M6@gmail.com>
 <CACJufxGgnX91Kzp-kJ03uZp2-sAPtuOpxxjhcigN72Z7BcYRZA@mail.gmail.com>
 <DDQRIXD8K0Z4.YO9AP5L8F4TI@gmail.com>
 <CACJufxFXhAuFDfQS=N0nCaFFTi08__cfwh6E7LhDuuJiAEo=Ww@mail.gmail.com>
 <DDV8CK8EJQQ0.DWQGFW2NOCGU@gmail.com>
 <5c52f4c7-74fa-4594-8378-53fd49f39329@dunslane.net>
In-Reply-To: <5c52f4c7-74fa-4594-8378-53fd49f39329@dunslane.net>
Archived-At: 
 <https://www.postgresql.org/message-id/DDWQNL7GDSXK.2OK2O16JOATAE%40gmail.com>
Precedence: bulk

On Thu Oct 30, 2025 at 1:32 PM -03, Andrew Dunstan wrote:
>> It seems to me that we need to disable the COPY usage when the foreign
>> table has triggers enabled.
>>
>
> I think it's probably worth finding out why COPY is so much worse in the=
=20
> presence of triggers. Is there something we can do to improve that, at=20
> least so it's no worse?
>
I did a bit of reading on CopyFrom() and found comments that clarifies
why the use of COPY for batch inserts is slower when the target foreign
table has triggers.
	/*
	 * It's generally more efficient to prepare a bunch of tuples for
	 * insertion, and insert them in one
	 * table_multi_insert()/ExecForeignBatchInsert() call, than call
	 * table_tuple_insert()/ExecForeignInsert() separately for every tuple.
	 * However, there are a number of reasons why we might not be able to do
	 * this.  These are explained below.
	 */
	if (resultRelInfo->ri_TrigDesc !=3D NULL &&
		(resultRelInfo->ri_TrigDesc->trig_insert_before_row ||
		 resultRelInfo->ri_TrigDesc->trig_insert_instead_row))
	{
		/*
		 * Can't support multi-inserts when there are any BEFORE/INSTEAD OF
		 * triggers on the table. Such triggers might query the table we're
		 * inserting into and act differently if the tuples that have already
		 * been processed and prepared for insertion are not there.
		 */
		insertMethod =3D CIM_SINGLE;
	}

It forces the use of CIM_SINGLE if a BEFORE or INSTEAD OF trigger is
present. This method then relies on table_tuple_insert() or
ExecForeignInsert() to insert one tuple at a time.

IUUC we cannot determine during execute_foreign_modify() whether a
foreign table has triggers enabled on the target remote table. This lack
of information suggests that we would need to query the foreign server
to find out. If it's the only viable path I think that we would have
issues if the the user configured to access the foreign server don't
have access on catalog tables.

It's showing a bit complicated to decide at runtime if we should use the
COPY or INSERT for batch insert into a foreign table. Perhaps we could
add a new option on CREATE FOREIGN TABLE to enable this usage or not? We
could document the performance improvements and the limitations so the
user can decide if it should enable or not.

--
Matheus Alcantara