Query issues on Foreign tables

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