Re: BUG #17344: Assert failed on queiring async_capable foreign table with inheritance

On Sun, Dec 26, 2021 at 8:26 AM Dmitry Dolgov <9erthalion6(at)gmail(dot)com> wrote:
> > On Sat, Dec 25, 2021 at 10:00:01AM +0000, PG Bug reporting form wrote:
> >
> > create extension postgres_fdw;
> > do $d$
> > begin
> > execute $$create server loopback foreign data wrapper postgres_fdw
> > options (dbname '$$||current_database()||$$',
> > port '$$||current_setting('port')||$$',
> > async_capable 'true'
> > )$$;
> > end;
> > $d$;
> > create user mapping for current_user server loopback;
> >
> > create table tab1 (a int);
> > insert into tab1 values(1), (2), (3);
> > create foreign table ftab1 (b int) server loopback options (table_name
> > 'tab1');
> >
> > create foreign table ftab2 () inherits (ftab1) server loopback;
> > select a from tab1 where a in (select a from ftab1);
> >
> > With async_capable = 'false' I get expected results.
> > But when ftab1 defined as
> > create foreign table ftab1 (a int) server loopback options (table_name
> > 'tab1');
> > The select fails with:
> > psql:query.sql:19: ERROR: could not connect to server "loopback"
> > DETAIL: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:
> > sorry, too many clients already
> > CONTEXT: remote SQL command: SELECT a FROM public.ftab2
> > remote SQL command: SELECT a FROM public.ftab2
> > remote SQL command: SELECT a FROM public.ftab2
> > remote SQL command: SELECT a FROM public.ftab2

Reproduced here.

> Apparently in both cases the second foreign scan of append ends up in
> recursion:
>
> -> Foreign Scan on public.ftab2 ftab1_2
> Remote SQL: SELECT NULL FROM public.ftab2
>
> This leads to a chain of the same cursor declarations:
>
> DECLARE c1 CURSOR FOR SELECT a FROM public.ftab4
>
> With a pattern:
>
> bind -> new backend with the same declare -> execute -> bind -> ...
>
> In the case of async foreign scan it hits limitation when no new
> requests be sent while there is a pending one.

Yeah, but when executing a simple query “select NULL from ftab1”,
which would essentially be the same as the sublink subquery used in
the first case, I don’t get the assertion failure. (The simple query
only throws the connection limit error.) The root cause of the
assertion failure in the first case might be something other than the
limitation. I’ll look into this in more detail.

Thanks, Alexander and Dmitry!

Best regards,
Etsuro Fujita