Postgres FDW could not serialize access due to concurrent update

Question

This is similar to this question Postgres could not serialize access due to concurrent update, however because of characteristics of FDW the suggestions aren't applicable.

The error is "could not serialize access due to concurrent update" from the following code:

update fdw_table set last_new_data=clock_timestamp() where fn_name='something' and id=1234

The problem seems to be that fdw has a stricter transaction level which can't be changed - or at least that is my understanding of the documentation postgresql.org/docs/current/postgres-fdw.html.

I tried using the following

update fdw_table set last_new_data=clock_timestamp() 
where fn_name='something' and id=( 
     select id from fdw_table
     where fn_name='something' and id=1234
     FOR UPDATE SKIP LOCKED 
);

But this still throws the error.

score 0 · Answer 1 · answered Jun 10 '22 at 01:25

That is expected; see the following comment from begin_remote_xact in contrib/postgres_fdw/connection.c:

/*
 * Start remote transaction or subtransaction, if needed.
 *
 * Note that we always use at least REPEATABLE READ in the remote session.
 * This is so that, if a query initiates multiple scans of the same or
 * different foreign tables, we will get snapshot-consistent results from
 * those scans.  A disadvantage is that we can't provide sane emulation of
 * READ COMMITTED behavior --- it would be nice if we had some other way to
 * control which remote queries share a snapshot.
 */

To explain that, imagine a query with an execution plan like this:

 Nested Loop
   ->  Seq Scan on local_table
   ->  Foreign Scan on remote_table

Here, the foreign table is scanned for each row found in the local table. All these scans on the foreign table are executed in the same transaction, but if postgres_fdw were to use the default READ COMMITTED transaction isolation level, a concurrent data modification could cause these queries on the remote table to get inconsistent results.

Since postgres_fdw uses REPEATABLE READ (or SERIALIZABLE, if that is the local transaction isolation level), there is always the possibility that a DML statement on the foreign table gets the serialization errors you observe. You cannot avoid that, but you can handle it in the correct fashion, which is to rollback and retry the operation until it succeeds.

Postgres FDW could not serialize access due to concurrent update

1 Answers1