From 8f1521bba9249d02f17a4bc9be8de315bb527ec0 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Wed, 18 Apr 2018 11:06:20 +0530 Subject: [PATCH 2/3] Error out if one iteration of non-direct DML affects more than one row on the foreign server When a foreign table points to a partitioned table or an inheritance parent on the foreign server, a non-direct DML can affect multiple rows when only one row is intended to be affected. This happens because postgres_fdw uses only ctid to identify a row to work on. Though ctid uniquely identifies a row in a single table, in a partitioned table or in an inheritance hierarchy, there can be be multiple rows, in different partitions, with the same ctid. So a DML statement sent to the foreign server by postgres_fdw ends up affecting more than one rows, only one of which is intended to be affected. In such a case it's good to throw an error instead of corrupting remote database with unwanted UPDATE/DELETEs. Subsequent commits will try to fix this situation. Ashutosh Bapat and Kyotaro Horiguchi --- contrib/postgres_fdw/expected/postgres_fdw.out | 32 +++++++++------- contrib/postgres_fdw/postgres_fdw.c | 48 +++++++++++++++++++----- 2 files changed, 57 insertions(+), 23 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 5156002..77d24ea 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6953,10 +6953,11 @@ UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa (5 rows) UPDATE fa SET aa = (CASE WHEN random() <= 1 THEN 'zzzz' ELSE NULL END) WHERE aa = 'aaa'; +ERROR: foreign server updated 2 rows when only one row was expected to be updated SELECT tableoid::regclass, ctid, * FROM fa; - tableoid | ctid | aa -----------+-------+------ - fa | (0,2) | zzzz + tableoid | ctid | aa +----------+-------+----- + fa | (0,1) | aaa fa | (0,1) | bbb (2 rows) @@ -6977,11 +6978,13 @@ DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END); (6 rows) DELETE FROM fa WHERE aa = (CASE WHEN random() <= 1 THEN 'aaa' ELSE 'bbb' END); +ERROR: foreign server deleted 2 rows when only one row was expected to be deleted SELECT tableoid::regclass, ctid, * FROM fa; - tableoid | ctid | aa -----------+------+---- + tableoid | ctid | aa +----------+-------+----- + fa | (0,1) | aaa fa | (0,1) | bbb -(1 rows) +(2 rows) DROP FOREIGN TABLE fa; DROP TABLE a CASCADE; @@ -8407,10 +8410,11 @@ UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1; (5 rows) UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1; +ERROR: foreign server updated 2 rows when only one row was expected to be updated SELECT tableoid::regclass, ctid, * FROM fplt; - tableoid | ctid | a | b -----------+-------+---+---- - fplt | (0,2) | 1 | 10 + tableoid | ctid | a | b +----------+-------+---+--- + fplt | (0,1) | 1 | 1 fplt | (0,1) | 2 | 2 (2 rows) @@ -8430,11 +8434,13 @@ DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END); (6 rows) DELETE FROM fplt WHERE a = (CASE WHEN random() <= 1 THEN 1 ELSE 10 END); +ERROR: foreign server deleted 2 rows when only one row was expected to be deleted SELECT tableoid::regclass, ctid, * FROM fplt; - tableoid | ctid | a | b -----------+-------+---+---- - fplt | (0,1) | 2 | 2 -(1 row) + tableoid | ctid | a | b +----------+-------+---+--- + fplt | (0,1) | 1 | 1 + fplt | (0,1) | 2 | 2 +(2 rows) DROP TABLE plt; DROP FOREIGN TABLE fplt; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 30e5726..469c7dd 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1810,7 +1810,8 @@ postgresExecForeignUpdate(EState *estate, bool isNull; const char **p_values; PGresult *res; - int n_rows; + int n_rows_returned; + int n_rows_updated; /* Set up the prepared statement on the remote server, if we didn't yet */ if (!fmstate->p_name) @@ -1853,22 +1854,35 @@ postgresExecForeignUpdate(EState *estate, pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query); /* Check number of rows affected, and fetch RETURNING tuple if any */ + n_rows_updated = atoi(PQcmdTuples(res)); if (fmstate->has_returning) { - n_rows = PQntuples(res); - if (n_rows > 0) + n_rows_returned = PQntuples(res); + if (n_rows_returned > 0) store_returning_result(fmstate, slot, res); } else - n_rows = atoi(PQcmdTuples(res)); + n_rows_returned = 0; /* And clean up */ PQclear(res); MemoryContextReset(fmstate->temp_cxt); + /* No rows should be returned if no rows were updated. */ + if (n_rows_updated == 0 && n_rows_returned != 0) + elog(ERROR, "foreign server returned %d rows when no row was updated", + n_rows_returned); + + /* ERROR if more than one row was updated on the remote end */ + if (n_rows_updated > 1) + ereport(ERROR, + (errcode (ERRCODE_FDW_ERROR), /* XXX */ + errmsg ("foreign server updated %d rows when only one row was expected to be updated", + n_rows_updated))); + /* Return NULL if nothing was updated on the remote end */ - return (n_rows > 0) ? slot : NULL; + return (n_rows_updated > 0) ? slot : NULL; } /* @@ -1886,7 +1900,8 @@ postgresExecForeignDelete(EState *estate, bool isNull; const char **p_values; PGresult *res; - int n_rows; + int n_rows_returned; + int n_rows_deleted; /* Set up the prepared statement on the remote server, if we didn't yet */ if (!fmstate->p_name) @@ -1929,22 +1944,35 @@ postgresExecForeignDelete(EState *estate, pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query); /* Check number of rows affected, and fetch RETURNING tuple if any */ + n_rows_deleted = atoi(PQcmdTuples(res)); if (fmstate->has_returning) { - n_rows = PQntuples(res); - if (n_rows > 0) + n_rows_returned = PQntuples(res); + if (n_rows_returned > 0) store_returning_result(fmstate, slot, res); } else - n_rows = atoi(PQcmdTuples(res)); + n_rows_returned = 0; /* And clean up */ PQclear(res); MemoryContextReset(fmstate->temp_cxt); + /* No rows should be returned if no rows were deleted. */ + if (n_rows_deleted == 0 && n_rows_returned != 0) + elog(ERROR, "foreign server returned %d rows when no row was deleted", + n_rows_returned); + + /* ERROR if more than one row was updated on the remote end */ + if (n_rows_deleted > 1) + ereport(ERROR, + (errcode (ERRCODE_FDW_ERROR), /* XXX */ + errmsg ("foreign server deleted %d rows when only one row was expected to be deleted", + n_rows_deleted))); + /* Return NULL if nothing was deleted on the remote end */ - return (n_rows > 0) ? slot : NULL; + return (n_rows_deleted > 0) ? slot : NULL; } /* -- 1.7.9.5