[PG] Default schema should be `public` on MyDuck for PG protocol

[VWagen1989]

This example is based on the connection established on pgpool.

postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)

postgres=# show pool_nodes;
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | pgsql    | 5432 | up     | up        | 0.000000  | primary | primary | 2          | false             | 0                 |                   |                        | 2024-11-21 01:54:12
 1       | myduck   | 5432 | up     | up        | 1.000000  | standby | standby | 10         | true              | 150456            | streaming         | async                  | 2024-11-21 01:54:12
(2 rows)

postgres=# select * from public.test;
 id | name 
----+------
  1 | tt
(1 row)

postgres=# show pool_nodes;
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | pgsql    | 5432 | up     | up        | 0.000000  | primary | primary | 2          | false             | 0                 |                   |                        | 2024-11-21 01:54:12
 1       | myduck   | 5432 | up     | up        | 1.000000  | standby | standby | 11         | true              | 150456            | streaming         | async                  | 2024-11-21 01:54:12
(2 rows)

postgres=# begin;
BEGIN
postgres=*# update test set name = 'ttt';
UPDATE 1
postgres=*# select * from test;
 id | name 
----+------
  1 | ttt
(1 row)

postgres=*# show pool_nodes;
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | pgsql    | 5432 | up     | up        | 0.000000  | primary | primary | 3          | false             | 0                 |                   |                        | 2024-11-21 01:54:12
 1       | myduck   | 5432 | up     | up        | 1.000000  | standby | standby | 11         | true              | 150456            | streaming         | async                  | 2024-11-21 01:54:12
(2 rows)

postgres=# commit;
COMMIT
postgres=# select * from test;
ERROR:  Catalog Error: Table with name test does not exist!
Did you mean "public.test"?
LINE 1: select * from test;
                      ^ (errno 1105) (sqlstate HY000)
postgres=# show pool_nodes;
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | pgsql    | 5432 | up     | up        | 0.000000  | primary | primary | 3          | false             | 0                 |                   |                        | 2024-11-21 01:54:12
 1       | myduck   | 5432 | up     | up        | 1.000000  | standby | standby | 12         | true              | 150736            | streaming         | async                  | 2024-11-21 01:54:12
(2 rows)

postgres=# select * from public.test;
 id | name 
----+------
  1 | tt
(1 row)

postgres=# show pool_nodes;
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | pgsql    | 5432 | up     | up        | 0.000000  | primary | primary | 3          | false             | 0                 |                   |                        | 2024-11-21 01:54:12
 1       | myduck   | 5432 | up     | up        | 1.000000  | standby | standby | 13         | true              | 150736            | streaming         | async                  | 2024-11-21 01:54:12
(2 rows)

As we can see, the statement select * from test; failed if it was sent to myduck(the replica), but succeeded while it was sent to pgsql(the primary) in an active transaction.

The reason may be that myduck use main as the default schema, while PostgreSQL use public.

[fanyang01]

It appears the solution is to set the default schema to public for each new connection.