Re: Keeping state in a foreign data wrapper

2020年8月4日(火) 1:24 Stelios Sfakianakis <sgsfak@gmail.com>:
>
> Hi,
>
> I am trying to  implement a FDW in Postgres for accessing a web api and I would like to keep information like for
examplethe total number of requests submiited. Ideally these data should be kept “per-user” and of course with the
properlocking to eliminate race conditions. So the question I have is how to design such a global (but per user and
database)state, using the C FDW API of Postgres. I have gone through the API and for example I see various fdw_private
fieldsin the query planning structures and callbacks but these do not seem to be relevant to my requirements. Another
possiblityseems to be to use shared memory (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is
evenless clear how to do it. 

Shared memory would probably work; you'd need to load the FDW via
"shared_preload_libraries" and have the FDW handler function(s) update
shared memory with whatever statistics you want to track. You could
then define SQL functions to retrieve the stored values, and possibly
persist them over server restarts by storing/retrieving them from a
file.

Look at "pg_stat_statements" for a good example of how to do that kind of thing.

Regards

Ian Barwick

--
Ian Barwick                   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services