Re: postgres_fdw: commit remote (sub)transactions in parallel during pre-commit

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. So ISTM that it's
half-baked and not enough to provide the option that controls
whether postgres_fdw issues commit command in parallel or serial way.

> but I might be too worried about that, so I want to hear
> the opinions of people.

Yes.

> IIUC I think the overheads of WaitLatchOrSocket() incurred by a series
> of epoll system calls are much larger compared to the overheads of
> PQconsumeInput() incurred by a recv system call in non-blocking mode
> when no data is available. I didn’t do testing, though.

Understood.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION