On Thu, Nov 18, 2021 at 1:09 PM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
> On 2021/11/16 18:55, Etsuro Fujita wrote:
> > Sorry, my explanation was not enough, but I don’t think this is always
> > true. Let me explain using an example:
> >
> > create server loopback foreign data wrapper postgres_fdw options
> > (dbname 'postgres', parallel_commit 'true');
> > create user mapping for current_user server loopback;
> > create table t1 (a int, b int);
> > create table t2 (a int, b int);
> > create foreign table ft1 (a int, b int) server loopback options
> > (table_name 't1');
> > create foreign table ft2 (a int, b int) server loopback options
> > (table_name 't2');
> > create role view_owner superuser;
> > create user mapping for view_owner server loopback;
> > grant SELECT on ft1 to view_owner;
> > create view v1 as select * from ft1;
> > alter view v1 owner to view_owner;
> >
> > begin;
> > insert into v1 values (10, 10);
> > insert into ft2 values (20, 20);
> > commit;
> >
> > For this transaction, since the first insert is executed as the view
> > owner while the second insert is executed as the current user, we
> > create a connection to the foreign server for each of the users to
> > execute the inserts. This leads to sending two commit commands to the
> > foreign server at the same time during pre-commit.
> >
> > To avoid spike loads on a remote server induced by such a workload, I
> > think it’s a good idea to have a server option to control whether this
> > is enabled,
>
> I understand your point. But even if the option is disabled (i.e.,
> commit command is sent to each foreign server in serial way),
> multiple queries still can run on the server concurrently and
> which may cause performance "spike". Other clients may open several
> sessions to the server and issue queries at the same time. Other
> sessions using postgres_fdw may send commit command at the same time.
> If we want to avoid that "spike", probably we need to decrease
> max_connections or use connection pooling, etc.
I think that what you are discussing here would be a related but
different issue, because the patch doesn't increase the number of
connections to the remote server that are needed for processing a
single transaction than before.
My concern about the patch is that in parallel-commit mode,
transactions like the above example might increase the remote server's
load at transaction end than before, while using the same number of
connections to the remote server as before, because multiple COMMIT
commands are sent to the remote server at the same time, not
sequentially as before. The option could be used to avoid such a
spike load without changing any settings on the remote server if
necessary. Also, the option could be added at no extra cost, so there
seems to me to be no reason to remove it.
Anyway, I'd like to hear the opinions of others.
Thanks!
Best regards,
Etsuro Fujita