[PATCH] Allow transaction to partition table using FDW
Hi,
I would like to use transactions with partitioning table using FDW, but transactions can not be used with the following error.
'ERROR: could not serialize access due to concurrent update
So, I tried to write a very simple patch.
This patch works for my purpose, but I do not know if it matches general usage.
I'd like to improve this feature which can be used generally, so please check it.
Please find attached file.
Regards,
--
mitani <mitani@sraw.co.jp>
Attachments:
allow-transaction-to-partition-table-using-FDW.patchapplication/octet-stream; name=allow-transaction-to-partition-table-using-FDW.patchDownload
--- connection.c 2019-02-21 17:13:49.882419448 +0900
+++ connection.c.new 2019-02-21 17:13:34.090424889 +0900
@@ -430,6 +430,9 @@ begin_remote_xact(ConnCacheEntry *entry)
sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
else
sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
+ if (IsTransactionState()) {
+ sql = "begin";
+ }
entry->changing_xact_state = true;
do_sql_command(entry->conn, sql);
entry->xact_depth = 1;
Hi there,
I modified my patch in response to Ishii-san's pointed out.
I always set 'COMMITTED READ' to SQL in 'begin_remote_xact()', but changed to set it only when 'XactIsoLevel' == 'XACT_READ_COMMITTED'.
I tested transaction query to partition tables on remote servers as follows,
(sent BEGIN - UPDATE - COMMIT query in two sessions)
target record on the same server target record on a different server
--------------------------------------------------------------------------------------------------------
target table is same (wait) (wait)
target table is defferent (no wait) (no wait)
(wait): Session 2 is kept waiting until session 1 commits
(no wait): Session 2 can be committed before session 1 commits
I do not understand FDW's design philosophy, so please let me know if there is a problem with my patch.
The target version of PostgreSQL is 11.2, and target file of this patch is 'contrib/postgresql/connection.c'.
Regards,
--
mitani <mitani@sraw.co.jp>
Attachments:
002-allow-transaction-to-partition-table-using-FDW.patchapplication/octet-stream; name=002-allow-transaction-to-partition-table-using-FDW.patchDownload
--- contrib/postgres_fdw/connection.c 2019-02-22 09:31:10.801333555 +0900
+++ contrib/postgres_fdw/connection.c.new 2019-02-22 09:31:04.153283090 +0900
@@ -429,7 +429,10 @@ begin_remote_xact(ConnCacheEntry *entry)
if (IsolationIsSerializable())
sql = "START TRANSACTION ISOLATION LEVEL SERIALIZABLE";
else
- sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
+ if (XactIsoLevel == XACT_READ_COMMITTED)
+ sql = "START TRANSACTION ISOLATION LEVEL READ COMMITTED";
+ else
+ sql = "START TRANSACTION ISOLATION LEVEL REPEATABLE READ";
entry->changing_xact_state = true;
do_sql_command(entry->conn, sql);
entry->xact_depth = 1;