Re: Query issues on Foreign tables - Mailing list 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