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:

Test 2:

Best Regards,

Shahram MOINVAZIRI