Re: Query issues on Foreign tables

On Wed, Feb 6, 2019 at 11:08 PM shmv <shmv(at)free(dot)fr> wrote:

> 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
>
>
>
>
>
>
>
postgres_fdw uses the same transaction snapshot until the end of the
local transaction. And p_test() commit only when the foreign table has row.

If the table valerie.test is empty when p_test() is executed, p_test()
never does
commit and continue to use the same transaction snapshot. Therefore it
always
says 'Not found'. On the other hand, if it has rows p_test() does commit
whenever
loops and get the latest transaction snapshot at the next read time.
Therefore the
new data are visible.

So to see the new data it might be a good idea to commit at the end of each
loops.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center