From: | "shmv" <shmv(at)free(dot)fr> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Query issues on Foreign tables |
Date: | 2019-02-06 13:46:23 |
Message-ID: | 002801d4be22$5b317380$11945a80$@free.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello,
I have a strange behavior when selecting data from foreign tables using “postgres_fdw”.
When you repeatedly select data from a foreign table, as long as the query finds results, changes made to the original table’s data are visible.
But if at a moment the query does not return any row, future changes to table’s data are no longer visible.
Test platform:
I’ve two servers (A and B) with a schema named “valerie” on each server.
The user name is also “valerie”.
Postgres version is 11.1.
Auto commit is enabled on both servers.
I use pgAdmin4 for the tests but I have the same issue when using psql or writing applications with ecpg.
On server A:
1) Create the physical table:
Create Table my_schema.Test (ID integer Not Null Primary key, Name varchar (10) Not Null);
2) Create a stored procedure inserting data into the table:
Create or replace procedure valerie.p_insert() as $$
Declare
i integer;
_first integer;
Begin
Select Coalesce (Max(ID), 0) + 1 Into _first From valerie.Test;
-- Insert 10 records
For i In _first .._first + 9 Loop
Insert into valerie.test values (i, 'name' || i);
End Loop;
End; $$ Language plpgsql;
On server B:
1) Create the foreign server and related mappings:
Create Server Server_A Foreign Data Wrapper postgres_fdw
Options (host 'PC7CA1', port '5432', dbname 'postgres');
Grant Usage On Foreign Server Server_A To valerie;
Create User Mapping For valerie Server Server_A Options(user 'valerie', password 'secret');
2) Create the foreign table
Create Foreign Table valerie.FT_Test (ID integer Not Null, Name varchar (10) Not Null)
Server Server_A
Options (schema_name 'valerie', table_name 'test');
Check:
Select * From valerie.FT_Test; <- OK (No data found)
3) Create a stored procedure reading and deleting data from the foreign table:
Create or replace procedure p_test() as $$
Declare
i integer;
pk integer;
nb integer;
Begin
For i in 1..30
Loop
Select count(*), min(id)
Into nb, pk
From Valerie.FT_Test;
If ( Not Found OR pk is null OR nb = 0 )
Then
Raise Notice '%','Not found.';
Else
Raise Notice '%','Count: '||nb||'. Deleting row '||pk||'...';
Delete from valerie.FT_Test where ID = pk;
Commit;
End if;
Perform pg_sleep(2);
End Loop;
End; $$ LANGUAGE plpgsql;
Test 1:
Server A
Server B
The table is empty!
Call valerie.p_insert();
...
Call valerie.p_test();
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found. ß 10 records added and commited
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
...
NOTICE: Not found.
NOTICE: Not found.
CALL
Test 2:
Server A
Server B
Call valerie.p_insert();
Call valerie.p_insert();
Call valerie.p_insert();
<-- The table contains 10 records
...
Call valerie.p_test();
NOTICE: Count: 10. Deleting row 1...
NOTICE: Count: 9. Deleting row 2...
NOTICE: Count: 8. Deleting row 3...
NOTICE: Count: 7. Deleting row 4...
NOTICE: Count: 6. Deleting row 5...
NOTICE: Count: 5. Deleting row 6...
NOTICE: Count: 14. Deleting row 7... ß 10 records added OK!
NOTICE: Count: 13. Deleting row 8...
NOTICE: Count: 12. Deleting row 9...
NOTICE: Count: 11. Deleting row 10...
NOTICE: Count: 10. Deleting row 11...
NOTICE: Count: 9. Deleting row 12...
NOTICE: Count: 8. Deleting row 13...
NOTICE: Count: 7. Deleting row 14...
NOTICE: Count: 6. Deleting row 15...
NOTICE: Count: 5. Deleting row 16...
NOTICE: Count: 4. Deleting row 17...
NOTICE: Count: 3. Deleting row 18...
NOTICE: Count: 2. Deleting row 19...
NOTICE: Count: 1. Deleting row 20...
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found. ß 10 records added NOT OK!
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
NOTICE: Not found.
CALL
Best Regards,
Shahram MOINVAZIRI
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-02-06 19:15:55 | Re: BUG #15613: Bug in PG Planner for Foreign Data Wrappers |
Previous Message | David Rowley | 2019-02-06 12:17:23 | Re: BUG #15572: Misleading message reported by "Drop function operation" on DB with functions having same name |