View Issue Details

IDProjectCategoryView StatusLast Update
0000492Pgpool-II[All Projects] Generalpublic2019-04-09 14:32
ReportervanAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionopen 
Product Version3.7.5 
Target VersionFixed in Version 
Summary0000492: pgpool sending SET schema 'Owner'; to primary node only
Descriptionpgppol sending SET schema query to primary node only because of that if any select query send to standby node it is getting failed because schema is not set in standby node
TagsNo tags attached.

Activities

van

2019-04-06 17:00

reporter  

pgpool.conf (29,727 bytes)

van

2019-04-06 17:02

reporter   ~0002526

According to documentation :
These queries are sent to both the primary node and the standby node

SET

DISCARD

DEALLOCATE ALL

t-ishii

2019-04-06 21:35

developer   ~0002527

The documentation is a little bit inaccurate. In streaming replication mode, SET command is sent to primary and "load balance node". The load balance node is chosen when client connects to Pgpool-II. For example if there are 3 nodes.
test=# show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------
 0 | /tmp | 11002 | up | 0.333333 | primary | 0 | false | 0
 1 | /tmp | 11003 | up | 0.333333 | standby | 0 | true | 0
 2 | /tmp | 11004 | up | 0.333333 | standby | 0 | false | 0

Then set command will be sent to node 0 and 1. No need to send set command because node 2 will not be sent any queries in this session. Now let's create new schema and create a table in it.
test=# create schema foo;
CREATE SCHEMA
test=# create table foo.t1(i int);
CREATE TABLE

Then set schema search path to foo:
test=# set schema 'foo';
SET

Now we should be able to SELECT t1 without schema qualification:
test=# set schema 'foo';
SET
test=# select * from t1;
 i
---
(0 rows)

van

2019-04-06 22:08

reporter   ~0002528

Hi,
Thanks for replying.

In my case

node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+-----------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0 | statefulset-primary.default.svc.cluster.local | 5432 | up | 0.500000 | primary | 2 | false | 0
 1 | statefulset-replica.default.svc.cluster.local | 5432 | up | 0.500000 | standby | 3 | true | 0
(2 rows)

set schema is going to the primary node only .
i am not sure its because of my configuration or other issue .

t-ishii

2019-04-06 23:50

developer   ~0002529

That's pretty odd. Can you add "log_min_messages = debug5" to pgpool.conf to get full debug log and share the log file?

van

2019-04-07 00:12

reporter   ~0002530

2019-04-06 15:08:15: pid 45: LOG: statement: SET schema 'Owner';SET role 'Owner';
2019-04-06 15:08:15: pid 45: DEBUG: session context: setting query in progress. DONE
2019-04-06 15:08:15: pid 45: DEBUG: pool_virtual_master_db_node_id: virtual_master_node_id:0 load_balance_node_id:0 PRIMARY_NODE_ID:0

According to logs
Primary node and load balance node id are same [logs attached]

logs (1,590,535 bytes)

t-ishii

2019-04-07 00:20

developer   ~0002531

Last edited: 2019-04-07 00:20

View 2 revisions

This is a "multi statement".
SET schema 'Owner';SET role 'Owner';
i.e. two SQLs are combined with ";".

Unfortunately Pgpool-II does not handle this very well: Send it always primary, regardless the kind of query.
Can you try to split them into separate lines?

SET schema 'Owner';
SET role 'Owner';

van

2019-04-07 00:32

reporter   ~0002532

Hey that's work , thanks a lots :) , can we logs this somewhere (in some doc ) so in future if anyone face this kind of issue they can refer.

t-ishii

2019-04-07 05:30

developer   ~0002533

Glad to hear that.

> can we logs this somewhere (in some doc ) so in future if anyone face this kind of issue they can refer.

Probably we should add this to "Restrictions" section:
http://www.pgpool.net/docs/37/en/html/restrictions.html

t-ishii

2019-04-07 09:50

developer   ~0002534

I have added this to "Load Balancing in Streaming Replication" for now.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=05cbf04b4965b946249830237cb70f8fbaa7b5a6
I am going to change the issue status to "resolved".

Issue History

Date Modified Username Field Change
2019-04-06 17:00 van New Issue
2019-04-06 17:00 van File Added: pgpool.conf
2019-04-06 17:02 van Note Added: 0002526
2019-04-06 21:35 t-ishii Note Added: 0002527
2019-04-06 22:08 van Note Added: 0002528
2019-04-06 23:50 t-ishii Note Added: 0002529
2019-04-07 00:12 van File Added: logs
2019-04-07 00:12 van Note Added: 0002530
2019-04-07 00:20 t-ishii Note Added: 0002531
2019-04-07 00:20 t-ishii Note Edited: 0002531 View Revisions
2019-04-07 00:20 t-ishii Assigned To => t-ishii
2019-04-07 00:20 t-ishii Status new => assigned
2019-04-07 00:20 t-ishii Description Updated View Revisions
2019-04-07 00:32 van Note Added: 0002532
2019-04-07 05:30 t-ishii Note Added: 0002533
2019-04-07 09:50 t-ishii Note Added: 0002534
2019-04-09 14:32 t-ishii Status assigned => resolved