Re: [PATCH] postgres_fdw connection caching - cause remote sessions linger till the local session exit

On Mon, Dec 14, 2020 at 11:00 PM Fujii Masao
<masao.fujii@oss.nttdata.com> wrote:
> > One more point for the above scenario: if the user mapping is dropped
> > in another session, then cache lookup for that entry in the
> > postgres_fdw_get_connections() returns a null tuple which I plan to
> > not throw an error, but just to skip in that case and continue. But if
> > the user mapping is not dropped in another session but altered, then
> > postgres_fdw_get_connections() still can show that in the output.
>
> Yes, so *if* we really want to return even connection invalidated by drop of
> user mapping, the cached connection entry may need to store not only
> user mapping id but also server id so that we can get the server name without
> user mapping entry.

We can do that, but what happens if the foreign server itself get
dropped with cascade option in another session, use case is as
follows:

1) Run a foreign query in session 1 with server 1, user mapping 1
2) Try to drop foreign server 1, then we would not be allowed to do so
because of dependency, if we use CASCADE, then the dependent user
mapping 1 and foreign tables get dropped too.
3) Run the postgres_fdw_get_connections(), at the start of txn, the
cached entry gets invalidated via pgfdw_inval_callback() and we try to
use the stored server id of the invalid entry (for which the foreign
server would have been dropped) and lookup in sys catalogues, so again
a null tuple is returned.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com