View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000677 | Pgpool-II | Bug | public | 2020-12-25 15:42 | 2021-01-11 12:50 |
| Reporter | chenjiaoqian | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | have not tried |
| Status | assigned | Resolution | open | ||
| Product Version | 4.1.3 | ||||
| Summary | 0000677: In replication mode, the number of retrieved records is not correct after inserting data into the foreign table. | ||||
| Description | I use "pgpool_setup" command to create an environment with two nodes in replication mode. Then connect to the pgpool server and create a foreign table. Finally insert 10 rows of data into the foreign table, but retrieve 20 rows of data. Why is the number of retrieved records 20? Execute log: postgres=# create table test(id int,create_time timestamp default '2020-01-01'); CREATE TABLE postgres=# create server srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS; CREATE SERVER postgres=# create USER MAPPING FOR pgpool2 SERVER srv OPTIONS; CREATE USER MAPPING postgres=# CREATE FOREIGN TABLE ft_test (id int,create_time timestamp default '2020-01-01') SERVER srv OPTIONS (table_name 'test'); CREATE FOREIGN TABLE postgres=# insert into ft_test select generate_series(1,10); INSERT 0 10 postgres=# select * from ft_test; id | create_time ----+--------------------- 1 | 2020-01-01 00:00:00 2 | 2020-01-01 00:00:00 3 | 2020-01-01 00:00:00 4 | 2020-01-01 00:00:00 5 | 2020-01-01 00:00:00 6 | 2020-01-01 00:00:00 7 | 2020-01-01 00:00:00 8 | 2020-01-01 00:00:00 9 | 2020-01-01 00:00:00 10 | 2020-01-01 00:00:00 1 | 2020-01-01 00:00:00 2 | 2020-01-01 00:00:00 3 | 2020-01-01 00:00:00 4 | 2020-01-01 00:00:00 5 | 2020-01-01 00:00:00 6 | 2020-01-01 00:00:00 7 | 2020-01-01 00:00:00 8 | 2020-01-01 00:00:00 9 | 2020-01-01 00:00:00 10 | 2020-01-01 00:00:00 (20 rows) | ||||
| Tags | No tags attached. | ||||
|
|
use "pgpool_setup" command to create an environment with two nodes in replication mode under the same host. |
|
|
This actually doesn't work because of a syntax error: > create server srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS; and what you are trying to do is depending the OPTIONS. Please describe the actual command. |
|
|
actual command: postgres=# create table test(id int,create_time timestamp default '2020-01-01'); CREATE TABLE postgres=# create server srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS(dbname 'postgres', port '11000'); CREATE SERVER postgres=# create USER MAPPING FOR pgpool2 SERVER srv OPTIONS(user 'pgpool2'); CREATE USER MAPPING postgres=# CREATE FOREIGN TABLE ft_test (id int,create_time timestamp default '2020-01-01') SERVER srv OPTIONS (table_name 'test'); CREATE FOREIGN TABLE |
|
|
Ok, I see a problem with this: insert into ft_test select generate_series(1,10); This command is executed as follows: 1) client sends the INSERT to pgpool 2) pgpool sends the INSERT to postgres 0 and executes the INSERT 3) postgres 0 connects to another pgpool and execute the INSERT, which reults in inserting 10 rows. 4) pgpool sends the INSERT to postgres 1 and executes the INSERT 5) postgres 1 connects to another pgpool and execute the INSERT again, which reults in inserting another 10 rows. As a result, table "test" has 20 rows, rather than 10. A workaround for this is, executing the INSERT against table "test", without using foreign server: i.e. insert into test ... |
|
|
I wonder why you need foreign table in the first place because Pgpool-II already does the replication for you. What is the use case you have in your mind to use such a foreign table using postgres_fdw? |
|
|
At present, I only test this scenario without considering the specific use case. However, This problem is not happened in master mode. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2020-12-25 15:42 | chenjiaoqian | New Issue | |
| 2020-12-25 15:47 | chenjiaoqian | Note Added: 0003687 | |
| 2020-12-25 19:29 | t-ishii | Note Added: 0003688 | |
| 2020-12-30 14:56 | chenjiaoqian | Note Added: 0003693 | |
| 2021-01-03 10:05 | t-ishii | Assigned To | => t-ishii |
| 2021-01-03 10:05 | t-ishii | Status | new => assigned |
| 2021-01-04 15:23 | t-ishii | Status | assigned => feedback |
| 2021-01-04 16:14 | t-ishii | Note Added: 0003706 | |
| 2021-01-08 10:54 | t-ishii | Note Added: 0003714 | |
| 2021-01-11 12:50 | chenjiaoqian | Note Added: 0003715 | |
| 2021-01-11 12:50 | chenjiaoqian | Status | feedback => assigned |
