View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000492 | Pgpool-II | General | public | 2019-04-06 17:00 | 2019-04-09 14:32 |
| Reporter | van | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Product Version | 3.7.5 | ||||
| Summary | 0000492: pgpool sending SET schema 'Owner'; to primary node only | ||||
| Description | pgppol 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 | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
According to documentation : These queries are sent to both the primary node and the standby node SET DISCARD DEALLOCATE ALL |
|
|
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) |
|
|
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 . |
|
|
That's pretty odd. Can you add "log_min_messages = debug5" to pgpool.conf to get full debug log and share the log file? |
|
|
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] |
|
|
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'; |
|
|
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. |
|
|
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 |
|
|
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". |
| 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 | |
| 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 | |
| 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 |
