Home > mailing lists

Thread: postgres_fdw: dead lock in a same transaction when postgres_fdw server is lookback

postgres_fdw: dead lock in a same transaction when postgres_fdw server is lookback

From

Xiaoran Wang

Date:

01 October 2022, 07:02:09

Hi,

I created a postgers_fdw server lookback as the test does. Then run the following SQLs

^{create table t1(c0 int);}

^{insert into t1 values(1);}

^{create foreign table ft1(}

^{c0 int}

^{) SERVER loopback OPTIONS (schema_name 'public', table_name 't1');}

Then started a transaction that runs queries on both t1 and ft1 tables:

^begin;

^{select * from ft1;}

^c0

^----

^{(1 row)}

^{select * from t1;}

^c0

^----

^{(1 row)}

^{insert into ft1 values(2);}

^{select * from ft1;}

^c0

^----

^{(2 rows)}

^{select * from t1;}

^c0

^----

^{(1 row)}

Though t1 and ft1 actually share the same data, and in the same transaction, they have different transaction ids and different snapshots, and queries are run in different processes, they see different data.

Then I attempted to run the update on them

^{update t1 set c0=8;}

^{update ft1 set c0=9;}

Then the transaction got stuck. Should the "lookback" server be disabled in the postgres_fdw?

Thoughts?

thanks,

Xiaoran

Re: postgres_fdw: dead lock in a same transaction when postgres_fdw server is lookback

From

Laurenz Albe

Date:

01 October 2022, 17:38:52

On Sat, 2022-10-01 at 04:02 +0000, Xiaoran Wang wrote:
> I created a postgers_fdw server lookback as the test does. Then run the following SQLs
> 
> [create a foreign server via loopback and manipulate the same data locally and via foreign table]
> 
> Then the transaction got stuck. Should the "lookback" server be disabled in the postgres_fdw?

It shouldn't; there are good use cases for that ("autonomous transactions").
AT most, some cautioning documentation could be added, but I am not convinced
that even that is necessary.

I'd say that this is a pretty obvious case of pilot error.

Yours,
Laurenz Albe