3

I have an net core application that connects to PostgreSQL servers through a PgPool-II cluster. It works very well, exept when I try to use the pgpool sql commands, such as "show pool_nodes", the commands seems to pass by pgpool and instead enter one PostgreSQL server which throws an error (which it should).

It seems that Npgsql uses "Extended Query Protocol" which do not work for the pgpool sql commands. They need the "Simple Query Protocol".

Is there any way to use "Simple Query Protocol" with Npgsql? If not, is there any other way sending a PostgreSQL query with net core but without using Npgsql?

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
Anthon Nilsson
  • 113
  • 1
  • 7

3 Answers3

1

No, there currently isn't a way for you to send a user-specified query via the PostgreSQL simple protocol. Npgsql uses the simple protocol for some internal commands (e.g. COMMIT), but the user-facing query APIs always use the extended protocol.

I'd raise this with PgPool-II, it seems like they should be intercepting extended protocol messages as well - after all it's part of the standard PostgreSQL protocol (please post a link to the issue here). If for some reason it really isn't possible, you can open an issue on the Npgsql repo to add some API for generating simple queries.

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
0

I solved this issue with a workaround.

From the net core code I call psql command application and let that handle the request to the pgpool cluster. Not the nice solution I would have liked, but it works.

Anthon Nilsson
  • 113
  • 1
  • 7
0

I also wanted to query pool_nodes and as of 2023, the issue is still alive and kicking. Also documentation is scarce. This is how I ended up solving it:

Run the following on the database you are querying via NpgSQL:

CREATE EXTENSION postgres_fdw;
CREATE SERVER ds1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost');
CREATE USER MAPPING FOR localuserhere SERVER ds1 OPTIONS (USER 'pcpuserhere', PASSWORD 'pcppasswordhere');
CREATE EXTENSION pgpool_adm;
  • ds1: A name for the server you will be querying. You can use anything.
  • localhost: Change if you connect via IP/hostname
  • localuserhere: This is the normal user you use in your other Npgsql queries.
  • pcpuserhere: Your pcp user.
  • pcppasswordhere: Your pcp password.

You should now be able to use this query via Npgsql to get node_info status similar to a row from show pool_nodes:

SELECT * FROM public.pcp_node_info(0,'ds1');
  • 0: Your node index (0,1,2, etc)
  • ds1: The name your entered for the server.

However, in my case, the above query returned an error:

Returned row contains 11 attributes, but query expects 4.

Probably due to a version mismatch of pgpool2/pgpool_adm?

So I had to create this function:

CREATE OR REPLACE FUNCTION public.my_node_info(node_id integer, pcp_server text, 
OUT host text, 
OUT port integer, 
OUT status text,
OUT pg_status TEXT,
OUT lb_weight REAL,
OUT status_role TEXT,
OUT pg_role TEXT,
OUT replication_delay bigint,
OUT replication_state TEXT,
OUT sync_replication_state TEXT,
OUT last_status_change timestamp

)
 RETURNS record
 LANGUAGE c
 STRICT
AS '$libdir/pgpool_adm', $function$_pcp_node_info$function$
;

So now I can run:

SELECT * FROM public.my_node_info(0,'ds1');

And I can finally view node status via npgsql.

The above were for pgpool 4.2, in 4.4 the relevant function is pgpool_adm_pcp_node_info. Maybe they fixed the bug with the mismatched arguments as well, didn't have time to test.

pkExec
  • 1,752
  • 1
  • 20
  • 39