davidfetter / pgbouncer_wrapper Goto Github PK

https://wiki.debian.org/Packaging

Hi David,

Thanks for making pgbounder_wrapper.

I'm getting a strange error upon make install.

Below is the full log:

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 20.04.2 LTS
Release:	20.04
Codename:	focal

$ psql --version
psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

$ make
cp sql/pgbouncer_wrapper.sql sql/pgbouncer_wrapper--1.2.0.sql

$ sudo make install
/bin/mkdir -p '/usr/share/postgresql/13/extension'
/bin/mkdir -p '/usr/share/postgresql/13/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-13/extension'
/usr/bin/install -c -m 644 .//pgbouncer_wrapper.control '/usr/share/postgresql/13/extension/'
/usr/bin/install -c -m 644 .//sql/pgbouncer_wrapper--1.2.0.sql .//sql/pgbouncer_wrapper--1.2.0.sql  '/usr/share/postgresql/13/extension/'
/usr/bin/install: will not overwrite just-created '/usr/share/postgresql/13/extension/pgbouncer_wrapper--1.2.0.sql' with './/sql/pgbouncer_wrapper--1.2.0.sql'
make: *** [/usr/lib/postgresql/13/lib/pgxs/src/makefiles/pgxs.mk:233: install] Error 1

Per conversation with and thanks to @fabriziomello, modify pgbouncer_wrapper as folllows:

• Add a config table with (bouncer_name TEXT PRIMARY KEY, bouncer_connect_string TEXT, active BOOLEAN NOT NULL DEFAULT true);
• Instead of creating a server, do a LATERAL JOIN to the config table with the connect string instead of the server that's been dropped. The view needs to include the bouncer_name column at a minimum to disambiguate info from multiple bouncers.
• This way, it's as easy to monitor N pgbouncers as it is to monitor one.

Hi David,

Attached is a new sql file that works for pgbouncer 1.9.0. I had to change the extension from .sql to .txt since it prevented me from uploading it.
pgbouncer_wrapper--1.9.0.txt

Ha, I can't even type correctly! title should be 1.9.0 not 9.1.0

Here are the columns missing for pgbouncer version 1.16:

show databases --> missing min_pool_size (after pool_size and before reserve_pool)
show pools --> missing cl_cancel_req (after cl_waiting and before sv_active)

I get an error trying to execute the sample set command in the README:
SELECT set('default_pool_size', '300');

ERROR:  function dblink_exec(unknown, text) does not exist
LINE 3:     dblink_exec('pgbouncer', format('SET %s=%L', key, value)...
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT
    dblink_exec('pgbouncer', format('SET %s=%L', key, value));

CONTEXT:  SQL function "set" during inlining

https://rpm-packaging-guide.github.io/

active_sockets --> application_name text (right after the tls column and before the recv_pos column)
comment --> A string containing the application_name set on the linked client connection, or empty if this is not set, or if there is no linked connection.

clients --> application_name text (last column, right after tls column)

-- see my view below where --v18 comment is:
CREATE VIEW pgbouncer.pools AS
SELECT * FROM dblink('pgbouncer', 'show pools') AS _(
database text,
"user" text,
cl_active integer,
cl_waiting integer,
-- cl_cancel_req integer, --v18 removed
cl_active_cancel_req integer, --v18
cl_waiting_cancel_req integer, --v18
sv_active integer,
sv_active_cancel integer, --v18
sv_being_canceled integer, --v18
sv_idle integer,
sv_used integer,
sv_tested integer,
sv_login integer,
maxwait integer,
maxwait_us integer,
pool_mode text
);

remove comment on removed column above!!!!!

COMMENT ON COLUMN pgbouncer.pools."cl_active_cancel_req" IS $$Client connections that have forwarded query cancellations to the server and are waiting for the server response.$$;
COMMENT ON COLUMN pgbouncer.pools."cl_waiting_cancel_req" IS $$Client connections that have not forwarded query cancellations to the server yet.$$;
COMMENT ON COLUMN pgbouncer.pools."sv_active_cancel IS $$Server connections that are currently forwarding a cancel request.$$;
COMMENT ON COLUMN pgbouncer.pools."sv_being_canceled IS $$Servers that normally could become idle but are waiting to do so until all in-flight cancel requests have completed that were sent to cancel a query on this server.$$;

servers --> application_name text (last column, right after tls column)

sockets --> application_name text (after tls column)

In pgbouncer 1.14.0 I noticed a slight change to the SQL mappings for one of the SHOW commands:

Show fds has 2 additional text columns:
scram_client_key
scram_server_key

I have verified all the other SHOW commands and everything else is still in sync.