Query issues on Foreign tables

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Query issues on Foreign tables

Shmv

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

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Query issues on Foreign tables

Masahiko Sawada


On Wed, Feb 6, 2019 at 11:08 PM shmv <[hidden email]> 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