[pgpool-general: 7355] Database does not exists after CREATE DATABASE on PgPool

[shacky]

Hi all,

I have the following PostgreSQL cluster configuration:
- 3 Postgres nodes with PostgreSQL 12.4-1 running on Debian Stable with
Repmgr 5.1.0
- 3 PgPool nodes with PgPool-II 4.1.1 on CentOS 8

PgPool is configured with master_slave_mode on, replication_mode off and
load_balance_mode off.

This is the nodes status:
================================================
bash-4.4$ psql -h 127.0.0.1 -p 5432 -U repmgr -c "show pool_nodes;"
 node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
load_balance_node | replication_delay | replication_state |
replication_sync_state | last_status_change
---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | postgres1 | 5432 | up     | 0.333333  | standby | 0          |
false             | 0                 |                   |
       | 2020-12-07 14:16:34
 1       | postgres2 | 5432 | up     | 0.333333  | primary | 299        |
true              | 0                 |                   |
       | 2020-12-07 14:16:34
 2       | postgres3 | 5432 | up     | 0.333333  | standby | 0          |
false             | 0                 |                   |
       | 2020-12-07 14:16:34
(3 rows)
================================================

When I create a new database I get the following errors if I try to connect
it in the same script after the CREATE statement:
================================================
# psql -f psql.sql -U testuser -h proxy -d postgres -a
Password for user testuser:
DROP DATABASE test;
DROP DATABASE
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE
= 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE
ALTER DATABASE test OWNER TO postgres;
ALTER DATABASE
\connect test
psql:psql_no_sleep_without_sets.sql:4: \connect: ERROR:  unable to read
message kind
DETAIL:  kind does not match between master(45) slot[1] (53)
================================================

On the PostgreSQL slave nodes (not on the primary one!) I see this error:
================================================
testuser at test FATAL:  database "test" does not exist
================================================

If I add a pg_sleep(0.05) after the ALTER DATABASE it works:
================================================
# psql -f psql_with_sleep.sql -U testuser -h proxy -d postgres -a
Password for user testuser:
DROP DATABASE test;
DROP DATABASE
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE
= 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE
ALTER DATABASE test OWNER TO postgres;
ALTER DATABASE
select pg_sleep(0.05);
 pg_sleep
----------

(1 row)

\connect test
psql (11.7 (Debian 11.7-0+deb10u1), server 12.4 (Debian 12.4-1.pgdg100+1))
WARNING: psql major version 11, server major version 12.
         Some psql features might not work.
You are now connected to database "test" as user "testuser".
================================================

The strange thing is that if I run the same script (without sleep) on the
PosgtreSQL primary node (postgres2), bypassing PgPool, it works.

Another strange thing is that when I run the script on PgPool, the CREATE
DATABASE statement takes 4-5 seconds, but if I run directly on PostgreSQL
primary node it is run immediately.

Could you help me to understand what's going on, please?
Thank you very much!
Bye
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20201207/9f7f5320/attachment.htm>