agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 1/2] explain.c: refactor ExplainNode() 3+ messages / 2 participants [nested] [flat]
* [PATCH 1/2] explain.c: refactor ExplainNode() @ 2021-04-15 16:55 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2021-04-15 16:55 UTC (permalink / raw) --- src/backend/commands/explain.c | 110 ++++++++++++++------------------- 1 file changed, 47 insertions(+), 63 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index cb13227db1f..06e089a1220 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -118,6 +118,8 @@ static void show_instrumentation_count(const char *qlabel, int which, PlanState *planstate, ExplainState *es); static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es); static void show_eval_params(Bitmapset *bms_params, ExplainState *es); +static void show_loop_info(Instrumentation *instrument, bool isworker, + ExplainState *es); static const char *explain_get_index_name(Oid indexId); static void show_buffer_usage(ExplainState *es, const BufferUsage *usage, bool planning); @@ -1615,36 +1617,7 @@ ExplainNode(PlanState *planstate, List *ancestors, if (es->analyze && planstate->instrument && planstate->instrument->nloops > 0) - { - double nloops = planstate->instrument->nloops; - double startup_ms = 1000.0 * planstate->instrument->startup / nloops; - double total_ms = 1000.0 * planstate->instrument->total / nloops; - double rows = planstate->instrument->ntuples / nloops; - - if (es->format == EXPLAIN_FORMAT_TEXT) - { - if (es->timing) - appendStringInfo(es->str, - " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)", - startup_ms, total_ms, rows, nloops); - else - appendStringInfo(es->str, - " (actual rows=%.0f loops=%.0f)", - rows, nloops); - } - else - { - if (es->timing) - { - ExplainPropertyFloat("Actual Startup Time", "ms", startup_ms, - 3, es); - ExplainPropertyFloat("Actual Total Time", "ms", total_ms, - 3, es); - } - ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); - ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); - } - } + show_loop_info(planstate->instrument, false, es); else if (es->analyze) { if (es->format == EXPLAIN_FORMAT_TEXT) @@ -1673,44 +1646,14 @@ ExplainNode(PlanState *planstate, List *ancestors, for (int n = 0; n < w->num_workers; n++) { Instrumentation *instrument = &w->instrument[n]; - double nloops = instrument->nloops; - double startup_ms; - double total_ms; - double rows; - if (nloops <= 0) + if (instrument->nloops <= 0) continue; - startup_ms = 1000.0 * instrument->startup / nloops; - total_ms = 1000.0 * instrument->total / nloops; - rows = instrument->ntuples / nloops; ExplainOpenWorker(n, es); - + show_loop_info(instrument, true, es); if (es->format == EXPLAIN_FORMAT_TEXT) - { - ExplainIndentText(es); - if (es->timing) - appendStringInfo(es->str, - "actual time=%.3f..%.3f rows=%.0f loops=%.0f\n", - startup_ms, total_ms, rows, nloops); - else - appendStringInfo(es->str, - "actual rows=%.0f loops=%.0f\n", - rows, nloops); - } - else - { - if (es->timing) - { - ExplainPropertyFloat("Actual Startup Time", "ms", - startup_ms, 3, es); - ExplainPropertyFloat("Actual Total Time", "ms", - total_ms, 3, es); - } - ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); - ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); - } - + appendStringInfoChar(es->str, '\n'); ExplainCloseWorker(n, es); } } @@ -4039,6 +3982,47 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors, ExplainCloseGroup("Target Tables", "Target Tables", false, es); } +void +show_loop_info(Instrumentation *instrument, bool isworker, ExplainState *es) +{ + double nloops = instrument->nloops; + double startup_ms = 1000.0 * instrument->startup / nloops; + double total_ms = 1000.0 * instrument->total / nloops; + double rows = instrument->ntuples / nloops; + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + if (isworker) + ExplainIndentText(es); + else + appendStringInfo(es->str, " ("); + + if (es->timing) + appendStringInfo(es->str, + "actual time=%.3f..%.3f rows=%.0f loops=%.0f", + startup_ms, total_ms, rows, nloops); + else + appendStringInfo(es->str, + "actual rows=%.0f loops=%.0f", + rows, nloops); + + if (!isworker) + appendStringInfoChar(es->str, ')'); + } + else + { + if (es->timing) + { + ExplainPropertyFloat("Actual Startup Time", "ms", startup_ms, + 3, es); + ExplainPropertyFloat("Actual Total Time", "ms", total_ms, + 3, es); + } + ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); + ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); + } +} + /* * Explain the constituent plans of an Append, MergeAppend, * BitmapAnd, or BitmapOr node. -- 2.17.1 --LWVQOr/QoF/fPPTS Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0002-Add-extra-statistics-to-explain-for-Nested-Loop.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH 1/3] explain.c: refactor ExplainNode() @ 2021-04-15 16:55 Justin Pryzby <pryzbyj@telsasoft.com> 0 siblings, 0 replies; 3+ messages in thread From: Justin Pryzby @ 2021-04-15 16:55 UTC (permalink / raw) --- src/backend/commands/explain.c | 110 ++++++++++++++------------------- 1 file changed, 47 insertions(+), 63 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 891ad0e717..db99739cc5 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -118,6 +118,8 @@ static void show_instrumentation_count(const char *qlabel, int which, PlanState *planstate, ExplainState *es); static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es); static void show_eval_params(Bitmapset *bms_params, ExplainState *es); +static void show_loop_info(Instrumentation *instrument, bool isworker, + ExplainState *es); static const char *explain_get_index_name(Oid indexId); static void show_buffer_usage(ExplainState *es, const BufferUsage *usage, bool planning); @@ -1609,36 +1611,7 @@ ExplainNode(PlanState *planstate, List *ancestors, if (es->analyze && planstate->instrument && planstate->instrument->nloops > 0) - { - double nloops = planstate->instrument->nloops; - double startup_ms = 1000.0 * planstate->instrument->startup / nloops; - double total_ms = 1000.0 * planstate->instrument->total / nloops; - double rows = planstate->instrument->ntuples / nloops; - - if (es->format == EXPLAIN_FORMAT_TEXT) - { - if (es->timing) - appendStringInfo(es->str, - " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)", - startup_ms, total_ms, rows, nloops); - else - appendStringInfo(es->str, - " (actual rows=%.0f loops=%.0f)", - rows, nloops); - } - else - { - if (es->timing) - { - ExplainPropertyFloat("Actual Startup Time", "ms", startup_ms, - 3, es); - ExplainPropertyFloat("Actual Total Time", "ms", total_ms, - 3, es); - } - ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); - ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); - } - } + show_loop_info(planstate->instrument, false, es); else if (es->analyze) { if (es->format == EXPLAIN_FORMAT_TEXT) @@ -1667,44 +1640,14 @@ ExplainNode(PlanState *planstate, List *ancestors, for (int n = 0; n < w->num_workers; n++) { Instrumentation *instrument = &w->instrument[n]; - double nloops = instrument->nloops; - double startup_ms; - double total_ms; - double rows; - if (nloops <= 0) + if (instrument->nloops <= 0) continue; - startup_ms = 1000.0 * instrument->startup / nloops; - total_ms = 1000.0 * instrument->total / nloops; - rows = instrument->ntuples / nloops; ExplainOpenWorker(n, es); - + show_loop_info(instrument, true, es); if (es->format == EXPLAIN_FORMAT_TEXT) - { - ExplainIndentText(es); - if (es->timing) - appendStringInfo(es->str, - "actual time=%.3f..%.3f rows=%.0f loops=%.0f\n", - startup_ms, total_ms, rows, nloops); - else - appendStringInfo(es->str, - "actual rows=%.0f loops=%.0f\n", - rows, nloops); - } - else - { - if (es->timing) - { - ExplainPropertyFloat("Actual Startup Time", "ms", - startup_ms, 3, es); - ExplainPropertyFloat("Actual Total Time", "ms", - total_ms, 3, es); - } - ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); - ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); - } - + appendStringInfoChar(es->str, '\n'); ExplainCloseWorker(n, es); } } @@ -4030,6 +3973,47 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors, ExplainCloseGroup("Target Tables", "Target Tables", false, es); } +void +show_loop_info(Instrumentation *instrument, bool isworker, ExplainState *es) +{ + double nloops = instrument->nloops; + double startup_ms = 1000.0 * instrument->startup / nloops; + double total_ms = 1000.0 * instrument->total / nloops; + double rows = instrument->ntuples / nloops; + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + if (isworker) + ExplainIndentText(es); + else + appendStringInfo(es->str, " ("); + + if (es->timing) + appendStringInfo(es->str, + "actual time=%.3f..%.3f rows=%.0f loops=%.0f", + startup_ms, total_ms, rows, nloops); + else + appendStringInfo(es->str, + "actual rows=%.0f loops=%.0f", + rows, nloops); + + if (!isworker) + appendStringInfoChar(es->str, ')'); + } + else + { + if (es->timing) + { + ExplainPropertyFloat("Actual Startup Time", "ms", startup_ms, + 3, es); + ExplainPropertyFloat("Actual Total Time", "ms", total_ms, + 3, es); + } + ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); + ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es); + } +} + /* * Explain the constituent plans of an Append, MergeAppend, * BitmapAnd, or BitmapOr node. -- 2.17.0 --fblc08uBQ7kpPybH Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0002-Re-PATCH-Add-extra-statistics-to-explain-for-Nested-.patch" ^ permalink raw reply [nested|flat] 3+ messages in thread
* [PATCH v3 2/2] Error out if one iteration of non-direct DML affects more than one row on the foreign server @ 2025-07-18 14:52 Jehan-Guillaume de Rorthais <jgdr@dalibo.com> 0 siblings, 0 replies; 3+ messages in thread From: Jehan-Guillaume de Rorthais @ 2025-07-18 14:52 UTC (permalink / raw) 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. Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Author: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Rebased by Jehan-Guillaume de Rorthais <jgdr@dalibo.com> --- .../postgres_fdw/expected/postgres_fdw.out | 26 ++++++++------ contrib/postgres_fdw/postgres_fdw.c | 36 +++++++++++++++---- 2 files changed, 46 insertions(+), 16 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 62019eaa881..b0ef54a2889 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8984,10 +8984,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 affected 2 rows when only one was expected 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) @@ -9008,11 +9009,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 affected 2 rows when only one was expected SELECT tableoid::regclass, ctid, * FROM fa; - tableoid | ctid | aa + tableoid | ctid | aa ----------+-------+----- + fa | (0,1) | aaa fa | (0,1) | bbb -(1 row) +(2 rows) -- cleanup DROP FOREIGN TABLE fa; @@ -9048,10 +9051,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 affected 2 rows when only one was expected 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) @@ -9071,11 +9075,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 affected 2 rows when only one was expected SELECT tableoid::regclass, ctid, * FROM fplt; tableoid | ctid | a | b ----------+-------+---+--- - fplt | (0,1) | 2 | 2 -(1 row) + 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 e0a34b27c7c..09c87d0e5d8 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -4132,7 +4132,8 @@ execute_foreign_modify(EState *estate, ItemPointer ctid = NULL; const char **p_values; PGresult *res; - int n_rows; + int n_rows_returned; + int n_rows_affected; StringInfoData sql; /* The operation should be INSERT, UPDATE, or DELETE */ @@ -4213,27 +4214,50 @@ execute_foreign_modify(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_affected = atoi(PQcmdTuples(res)); if (fmstate->has_returning) { Assert(*numSlots == 1); - n_rows = PQntuples(res); - if (n_rows > 0) + n_rows_returned = PQntuples(res); + if (n_rows_returned > 0) store_returning_result(fmstate, slots[0], res); + + // FIXME: shouldn't we check the max number of rows returned is one? } else - n_rows = atoi(PQcmdTuples(res)); + n_rows_returned = 0; /* And clean up */ PQclear(res); MemoryContextReset(fmstate->temp_cxt); - *numSlots = n_rows; + /* + * UPDATE & DELETE command can only affect one row, make sure this contract + * is respected. + * CMD_INSERT can insert multiple row when called from ForeignBatchInsert. + */ + if (operation != CMD_INSERT) + { + /* No rows should be returned if no rows were affected */ + if (n_rows_affected == 0 && n_rows_returned != 0) + elog(ERROR, "foreign server returned %d rows when no row was affected", + n_rows_returned); + + /* ERROR if more than one row was updated on the remote end */ + if (n_rows_affected > 1) + ereport(ERROR, + (errcode (ERRCODE_FDW_ERROR), /* XXX */ + errmsg ("foreign server affected %d rows when only one was expected", + n_rows_affected))); + } + + *numSlots = n_rows_returned; /* * Return NULL if nothing was inserted/updated/deleted on the remote end */ - return (n_rows > 0) ? slots : NULL; + return (n_rows_affected > 0) ? slots : NULL; } /* -- 2.50.0 --MP_/4ZRYdF7Ah.pt5w65PuZdaPz-- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-07-18 14:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-04-15 16:55 [PATCH 1/2] explain.c: refactor ExplainNode() Justin Pryzby <pryzbyj@telsasoft.com> 2021-04-15 16:55 [PATCH 1/3] explain.c: refactor ExplainNode() Justin Pryzby <pryzbyj@telsasoft.com> 2025-07-18 14:52 [PATCH v3 2/2] Error out if one iteration of non-direct DML affects more than one row on the foreign server Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox