diff options
43 files changed, 1421 insertions, 941 deletions
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out index cb5c396c519..6de02323d7c 100644 --- a/contrib/pg_overexplain/expected/pg_overexplain.out +++ b/contrib/pg_overexplain/expected/pg_overexplain.out @@ -37,7 +37,7 @@ EXPLAIN (DEBUG) SELECT 1; Subplans Needing Rewind: none Relation OIDs: none Executor Parameter Types: none - Parse Location: 16 for 8 bytes + Parse Location: 0 to end (11 rows) EXPLAIN (RANGE_TABLE) SELECT 1; @@ -119,7 +119,7 @@ $$); Subplans Needing Rewind: none Relation OIDs: NNN... Executor Parameter Types: none - Parse Location: 41 to end + Parse Location: 0 to end RTI 1 (relation, inherited, in-from-clause): Eref: vegetables (id, name, genus) Relation: vegetables @@ -240,7 +240,7 @@ $$); <Subplans-Needing-Rewind>none</Subplans-Needing-Rewind> + <Relation-OIDs>NNN...</Relation-OIDs> + <Executor-Parameter-Types>none</Executor-Parameter-Types> + - <Parse-Location>53 to end</Parse-Location> + + <Parse-Location>0 to end</Parse-Location> + </PlannedStmt> + <Range-Table> + <Range-Table-Entry> + @@ -344,7 +344,7 @@ $$); Subplans Needing Rewind: none Relation OIDs: NNN... Executor Parameter Types: none - Parse Location: 28 to end + Parse Location: 0 to end (37 rows) SET debug_parallel_query = false; @@ -372,7 +372,7 @@ $$); Subplans Needing Rewind: none Relation OIDs: NNN... Executor Parameter Types: 0 - Parse Location: 28 to end + Parse Location: 0 to end (15 rows) -- Create an index, and then attempt to force a nested loop with inner index @@ -436,7 +436,7 @@ $$); Subplans Needing Rewind: none Relation OIDs: NNN... Executor Parameter Types: 23 - Parse Location: 75 for 62 bytes + Parse Location: 0 to end (47 rows) RESET enable_hashjoin; diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 75e785e1719..8213fcd2e61 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -206,37 +206,37 @@ EXPLAIN (COSTS OFF) SELECT 1 UNION SELECT 2; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+-------------------------------------------------------------------- - f | 1 | DELETE FROM stats_track_tab + toplevel | calls | query +----------+-------+--------------------------------------------------------------------- t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2) + f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2); t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) + f | 1 | EXPLAIN (COSTS OFF) (TABLE test_table); t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) + f | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)); t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab + f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + + f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)); + t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); t | 1 | EXPLAIN (COSTS OFF) SELECT $1 t | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2 + f | 1 | EXPLAIN (COSTS OFF) SELECT $1 UNION SELECT $2; + f | 1 | EXPLAIN (COSTS OFF) SELECT $1; t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab + f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 + f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2; t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - f | 1 | INSERT INTO stats_track_tab VALUES (($1)) - f | 1 | MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | SELECT $1 - f | 1 | SELECT $1 UNION SELECT $2 - f | 1 | SELECT $1, $2 + f | 1 | EXPLAIN (COSTS OFF) VALUES ($1); t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 1 | TABLE stats_track_tab - f | 1 | TABLE test_table - f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 - f | 1 | VALUES ($1) - f | 1 | VALUES ($1, $2) (23 rows) -- EXPLAIN - top-level tracking. @@ -405,20 +405,20 @@ EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4\; EXPLAIN (COSTS OFF) (SELECT SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+----------------------------------------------------------------- - f | 1 | (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 + toplevel | calls | query +----------+-------+--------------------------------------------------------------------------------------------------------------------- t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6 + f | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3); EXPLAIN (COSTS OFF) (SELECT 1, 2, 3, 4); t | 1 | EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4) + f | 1 | EXPLAIN (COSTS OFF) (SELECT 1, 2, 3); EXPLAIN (COSTS OFF) (SELECT $1, $2, $3, $4); t | 1 | EXPLAIN (COSTS OFF) SELECT $1 t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4 - f | 1 | SELECT $1 - f | 1 | SELECT $1, $2 - f | 1 | SELECT $1, $2 UNION SELECT $3, $4 - f | 1 | SELECT $1, $2, $3 - f | 1 | SELECT $1, $2, $3, $4 + f | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2 UNION SELECT $3, $4; EXPLAIN (COSTS OFF) (SELECT 1, 2, 3) UNION SELECT 3, 4, 5; + f | 1 | EXPLAIN (COSTS OFF) SELECT $1; EXPLAIN (COSTS OFF) SELECT 1, 2; + f | 1 | EXPLAIN (COSTS OFF) SELECT 1, 2 UNION SELECT 3, 4; EXPLAIN (COSTS OFF) (SELECT $1, $2, $3) UNION SELECT $4, $5, $6; + f | 1 | EXPLAIN (COSTS OFF) SELECT 1; EXPLAIN (COSTS OFF) SELECT $1, $2; t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (13 rows) @@ -494,29 +494,29 @@ EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1))\; EXPLAIN (COSTS OF SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+-------------------------------------------------------------------- - f | 1 | DELETE FROM stats_track_tab - f | 1 | DELETE FROM stats_track_tab WHERE x = $1 + toplevel | calls | query +----------+-------+---------------------------------------------------------------------------------------------------------------------------------- t | 1 | EXPLAIN (COSTS OFF) (TABLE test_table) t | 1 | EXPLAIN (COSTS OFF) (VALUES ($1, $2)) t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab t | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1 + f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = $1; + f | 1 | EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab; EXPLAIN (COSTS OFF) DELETE FROM stats_track_tab WHERE x = 1; t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2) t | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)) + f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (($1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES (1), (2); + f | 1 | EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ((1)); EXPLAIN (COSTS OFF) INSERT INTO stats_track_tab VALUES ($1), ($2); t | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab + f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table); + f | 1 | EXPLAIN (COSTS OFF) TABLE stats_track_tab; EXPLAIN (COSTS OFF) (TABLE test_table); t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 t | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2 + f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1 WHERE x = $2; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1; + f | 1 | EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = 1 WHERE x = 1; EXPLAIN (COSTS OFF) UPDATE stats_track_tab SET x = $1; t | 1 | EXPLAIN (COSTS OFF) VALUES ($1) - f | 1 | INSERT INTO stats_track_tab VALUES ($1), ($2) - f | 1 | INSERT INTO stats_track_tab VALUES (($1)) + f | 1 | EXPLAIN (COSTS OFF) VALUES ($1); EXPLAIN (COSTS OFF) (VALUES (1, 2)); + f | 1 | EXPLAIN (COSTS OFF) VALUES (1); EXPLAIN (COSTS OFF) (VALUES ($1, $2)); t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 1 | TABLE stats_track_tab - f | 1 | TABLE test_table - f | 1 | UPDATE stats_track_tab SET x = $1 - f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 - f | 1 | VALUES ($1) - f | 1 | VALUES ($1, $2) (21 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -547,18 +547,21 @@ EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------- - t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id+ - | | WHEN MATCHED THEN UPDATE SET x = id + + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------------ + t | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT 1, 2, 3, 4, 5; + f | 1 | EXPLAIN (COSTS OFF) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series(1, 10) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5; t | 1 | EXPLAIN (COSTS OFF) SELECT $1, $2, $3, $4, $5 - f | 1 | MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($1, $2) id) ON x = id+ - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | SELECT $1, $2, $3, $4, $5 t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (5 rows) @@ -786,29 +789,29 @@ EXPLAIN (COSTS OFF) WITH a AS (select 4) SELECT 1 UNION SELECT 2; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------------------ + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------------------- t | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3)) + f | 1 | EXPLAIN (COSTS OFF) (WITH a AS (SELECT $1) (SELECT $2, $3)); t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) DELETE FROM stats_track_tab; t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)) - t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)); + t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) MERGE INTO stats_track_tab + + | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id); t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2 + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) SELECT $2; t | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3 + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3; t | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3 + f | 1 | EXPLAIN (COSTS OFF) WITH a AS (select $1) SELECT $2 UNION SELECT $3; t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 1 | WITH a AS (SELECT $1) (SELECT $2, $3) - f | 1 | WITH a AS (SELECT $1) DELETE FROM stats_track_tab - f | 1 | WITH a AS (SELECT $1) INSERT INTO stats_track_tab VALUES (($2)) - f | 1 | WITH a AS (SELECT $1) MERGE INTO stats_track_tab + - | | USING (SELECT id FROM generate_series($2, $3) id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) - f | 1 | WITH a AS (SELECT $1) SELECT $2 - f | 1 | WITH a AS (SELECT $1) UPDATE stats_track_tab SET x = $2 WHERE x = $3 - f | 1 | WITH a AS (select $1) SELECT $2 UNION SELECT $3 (15 rows) -- EXPLAIN with CTEs - top-level tracking @@ -918,13 +921,14 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+------------------------------------------------------------------------------ - t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + + toplevel | calls | query +----------+-------+------------------------------------------------------------------------------- + t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab + f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) + + | | DECLARE foocur CURSOR FOR SELECT * FROM stats_track_tab; t | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1 - f | 1 | SELECT $1 - f | 1 | SELECT * FROM stats_track_tab + f | 1 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT $1; t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (5 rows) @@ -1047,10 +1051,10 @@ SELECT toplevel, calls, query FROM pg_stat_statements toplevel | calls | query ----------+-------+----------------------------------------------------------------- t | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1 + f | 1 | CREATE TEMPORARY TABLE pgss_ctas_1 AS SELECT $1; t | 1 | CREATE TEMPORARY TABLE pgss_ctas_2 AS EXECUTE test_prepare_pgss - f | 1 | SELECT $1 + f | 1 | PREPARE test_prepare_pgss AS select generate_series($1, $2) t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 1 | select generate_series($1, $2) (5 rows) -- CREATE TABLE AS, top-level tracking. @@ -1088,10 +1092,10 @@ EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT 1; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------------- + toplevel | calls | query +----------+-------+---------------------------------------------------------------------------- t | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1 - f | 1 | SELECT $1 + f | 1 | EXPLAIN (COSTS OFF) CREATE TEMPORARY TABLE pgss_explain_ctas AS SELECT $1; t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (3 rows) @@ -1136,14 +1140,14 @@ CLOSE foocur; COMMIT; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------- + toplevel | calls | query +----------+-------+---------------------------------------------------------- t | 1 | BEGIN t | 1 | CLOSE foocur t | 1 | COMMIT t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab + f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * from stats_track_tab; t | 1 | FETCH FORWARD 1 FROM foocur - f | 1 | SELECT * from stats_track_tab t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (7 rows) @@ -1203,25 +1207,25 @@ COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout; 2 SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - toplevel | calls | query -----------+-------+--------------------------------------------------------------------------- + toplevel | calls | query +----------+-------+----------------------------------------------------------------------------- + f | 1 | COPY (DELETE FROM stats_track_tab WHERE x = $1 RETURNING x) TO stdout t | 1 | COPY (DELETE FROM stats_track_tab WHERE x = 2 RETURNING x) TO stdout + f | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x) TO stdout t | 1 | COPY (INSERT INTO stats_track_tab (x) VALUES (1) RETURNING x) TO stdout - t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + + f | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout + t | 1 | COPY (MERGE INTO stats_track_tab USING (SELECT 1 id) ON x = id + + | | WHEN MATCHED THEN UPDATE SET x = id + + | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x) TO stdout + f | 1 | COPY (SELECT $1 UNION SELECT $2) TO stdout + f | 1 | COPY (SELECT $1) TO stdout t | 1 | COPY (SELECT 1 UNION SELECT 2) TO stdout t | 1 | COPY (SELECT 1) TO stdout + f | 1 | COPY (UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x) TO stdout t | 1 | COPY (UPDATE stats_track_tab SET x = 2 WHERE x = 1 RETURNING x) TO stdout - f | 1 | DELETE FROM stats_track_tab WHERE x = $1 RETURNING x - f | 1 | INSERT INTO stats_track_tab (x) VALUES ($1) RETURNING x - f | 1 | MERGE INTO stats_track_tab USING (SELECT $1 id) ON x = id + - | | WHEN MATCHED THEN UPDATE SET x = id + - | | WHEN NOT MATCHED THEN INSERT (x) VALUES (id) RETURNING x - f | 1 | SELECT $1 - f | 1 | SELECT $1 UNION SELECT $2 t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - f | 1 | UPDATE stats_track_tab SET x = $1 WHERE x = $2 RETURNING x (13 rows) -- COPY - top-level tracking. diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out index 3ee1928cbe9..9effd11fdc8 100644 --- a/contrib/pg_stat_statements/expected/planning.out +++ b/contrib/pg_stat_statements/expected/planning.out @@ -58,7 +58,7 @@ SELECT 42; (1 row) SELECT plans, calls, rows, query FROM pg_stat_statements - WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C"; + WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; plans | calls | rows | query -------+-------+------+---------------------------------------------------------- 0 | 1 | 0 | ALTER TABLE stats_plan_test ADD COLUMN x int @@ -72,10 +72,10 @@ SELECT plans, calls, rows, query FROM pg_stat_statements -- for the prepared statement we expect at least one replan, but cache -- invalidations could force more SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements - WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C"; - plans_ok | calls | rows | query -----------+-------+------+-------------------------------------- - t | 4 | 4 | SELECT COUNT(*) FROM stats_plan_test + WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; + plans_ok | calls | rows | query +----------+-------+------+------------------------------------------------------- + t | 4 | 4 | PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test (1 row) -- Cleanup diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 038ae110364..75c896f3885 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -208,6 +208,7 @@ DEALLOCATE pgss_test; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | rows | query -------+------+------------------------------------------------------------------------------ + 1 | 1 | PREPARE pgss_test (int) AS SELECT $1, $2 LIMIT $3 4 | 4 | SELECT $1 + | | -- but this one will appear + | | AS "text" @@ -221,7 +222,6 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 2 | 2 | SELECT $1 AS "int" ORDER BY 1 1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i 1 | 1 | SELECT $1 || $2 - 1 | 1 | SELECT $1, $2 LIMIT $3 2 | 2 | SELECT DISTINCT $1 AS "int" 0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t @@ -267,6 +267,36 @@ SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 (4 rows) +-- with the last element being an explicit function call with an argument, ensure +-- the normalization of the squashing interval is correct. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE 1 IN (1, int4(1), int4(2)); +-- +(1 row) + +SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2)]); +-- +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +------------------------------------------------------------------------+------- + SELECT WHERE $1 IN ($2 /*, ... */) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 +(3 rows) + -- -- queries with locking clauses -- diff --git a/contrib/pg_stat_statements/expected/squashing.out b/contrib/pg_stat_statements/expected/squashing.out index 7b138af098c..7b935d464ec 100644 --- a/contrib/pg_stat_statements/expected/squashing.out +++ b/contrib/pg_stat_statements/expected/squashing.out @@ -2,9 +2,11 @@ -- Const squashing functionality -- CREATE EXTENSION pg_stat_statements; +-- +-- Simple Lists +-- CREATE TABLE test_squash (id int, data int); --- IN queries --- Normal scenario, too many simple constants for an IN query +-- single element will not be squashed SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -16,42 +18,150 @@ SELECT * FROM test_squash WHERE id IN (1); ----+------ (0 rows) +SELECT ARRAY[1]; + array +------- + {1} +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------+------- + SELECT * FROM test_squash WHERE id IN ($1) | 1 + SELECT ARRAY[$1] | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- more than 1 element in a list will be squashed +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + SELECT * FROM test_squash WHERE id IN (1, 2, 3); id | data ----+------ (0 rows) +SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5); + id | data +----+------ +(0 rows) + +SELECT ARRAY[1, 2, 3]; + array +--------- + {1,2,3} +(1 row) + +SELECT ARRAY[1, 2, 3, 4]; + array +----------- + {1,2,3,4} +(1 row) + +SELECT ARRAY[1, 2, 3, 4, 5]; + array +------------- + {1,2,3,4,5} +(1 row) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls -------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 1 - SELECT * FROM test_squash WHERE id IN ($1) | 1 + SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 3 + SELECT ARRAY[$1 /*, ... */] | 3 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); +-- built-in functions will be squashed +-- the IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE 1 IN (1, int4(1), int4(2), 2); +-- +(1 row) + +SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]); +-- +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------+------- + SELECT WHERE $1 IN ($2 /*, ... */) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- external parameters will not be squashed +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) \bind 1 2 3 4 5 +; id | data ----+------ (0 rows) -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); +SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind 1 2 3 4 5 +; id | data ----+------ (0 rows) -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +---------------------------------------------------------------------------+------- + SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) | 1 + SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) + +-- neither are prepared statements +-- the IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +PREPARE p1(int, int, int, int, int) AS +SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5); +EXECUTE p1(1, 2, 3, 4, 5); + id | data +----+------ +(0 rows) + +DEALLOCATE p1; +PREPARE p1(int, int, int, int, int) AS +SELECT * FROM test_squash WHERE id = ANY(ARRAY[$1, $2, $3, $4, $5]); +EXECUTE p1(1, 2, 3, 4, 5); id | data ----+------ (0 rows) +DEALLOCATE p1; SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -------------------------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) | 4 - SELECT * FROM test_squash WHERE id IN ($1) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 - SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 -(4 rows) + query | calls +------------------------------------------------------------+------- + DEALLOCATE $1 | 2 + PREPARE p1(int, int, int, int, int) AS +| 2 + SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) | + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- More conditions in the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -75,10 +185,25 @@ SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND da ----+------ (0 rows) +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) AND data = 2; + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AND data = 2; + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]) AND data = 2; + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ---------------------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) AND data = $2 | 3 + SELECT * FROM test_squash WHERE id IN ($1 /*, ... */) AND data = $2 | 6 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) @@ -107,24 +232,46 @@ SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) ----+------ (0 rows) +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls -------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)+| 3 + SELECT * FROM test_squash WHERE id IN ($1 /*, ... */)+| 6 AND data IN ($2 /*, ... */) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) --- No constants simplification for OpExpr SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- t (1 row) --- In the following two queries the operator expressions (+) and (@) have --- different oppno, and will be given different query_id if squashed, even though --- the normalized query will be the same +-- No constants squashing for OpExpr +-- The IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + SELECT * FROM test_squash WHERE id IN (1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9); id | data @@ -137,19 +284,35 @@ SELECT * FROM test_squash WHERE id IN ----+------ (0 rows) +SELECT * FROM test_squash WHERE id = ANY(ARRAY + [1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9]); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY(ARRAY + [@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9']); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ----------------------------------------------------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN +| 1 + SELECT * FROM test_squash WHERE id IN +| 2 ($1 + $2, $3 + $4, $5 + $6, $7 + $8, $9 + $10, $11 + $12, $13 + $14, $15 + $16, $17 + $18) | - SELECT * FROM test_squash WHERE id IN +| 1 + SELECT * FROM test_squash WHERE id IN +| 2 (@ $1, @ $2, @ $3, @ $4, @ $5, @ $6, @ $7, @ $8, @ $9) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (3 rows) +-- -- FuncExpr +-- -- Verify multiple type representation end up with the same query_id CREATE TABLE test_float (data float); +-- The casted ARRAY expressions will have the same queryId as the IN clause +-- form of the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -181,12 +344,38 @@ SELECT data FROM test_float WHERE data IN (1.0, 1.0); ------ (0 rows) +SELECT data FROM test_float WHERE data = ANY(ARRAY['1'::double precision, '2'::double precision]); + data +------ +(0 rows) + +SELECT data FROM test_float WHERE data = ANY(ARRAY[1.0::double precision, 1.0::double precision]); + data +------ +(0 rows) + +SELECT data FROM test_float WHERE data = ANY(ARRAY[1, 2]); + data +------ +(0 rows) + +SELECT data FROM test_float WHERE data = ANY(ARRAY[1, '2']); + data +------ +(0 rows) + +SELECT data FROM test_float WHERE data = ANY(ARRAY['1', 2]); + data +------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls ------------------------------------------------------------+------- - SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 5 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------+------- + SELECT data FROM test_float WHERE data = ANY(ARRAY[$1 /*, ... */]) | 3 + SELECT data FROM test_float WHERE data IN ($1 /*, ... */) | 7 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- Numeric type, implicit cast is squashed CREATE TABLE test_squash_numeric (id int, data numeric(5, 2)); @@ -201,12 +390,18 @@ SELECT * FROM test_squash_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ----+------ (0 rows) +SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls ------------------------------------------------------------------+------- - SELECT * FROM test_squash_numeric WHERE data IN ($1 /*, ... */) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +--------------------------------------------------------------------------+------- + SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[$1 /*, ... */]) | 1 + SELECT * FROM test_squash_numeric WHERE data IN ($1 /*, ... */) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) -- Bigint, implicit cast is squashed CREATE TABLE test_squash_bigint (id int, data bigint); @@ -221,14 +416,20 @@ SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1 ----+------ (0 rows) +SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------------+------- - SELECT * FROM test_squash_bigint WHERE data IN ($1 /*, ... */) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 -(2 rows) + query | calls +-------------------------------------------------------------------------+------- + SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[$1 /*, ... */]) | 1 + SELECT * FROM test_squash_bigint WHERE data IN ($1 /*, ... */) | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(3 rows) --- Bigint, explicit cast is not squashed +-- Bigint, explicit cast is squashed SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -242,15 +443,22 @@ SELECT * FROM test_squash_bigint WHERE data IN ----+------ (0 rows) +SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[ + 1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint, + 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ----------------------------------------------------+------- - SELECT * FROM test_squash_bigint WHERE data IN +| 1 - ($1 /*, ... */::bigint) | + SELECT * FROM test_squash_bigint WHERE data IN +| 2 + ($1 /*, ... */) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) --- Bigint, long tokens with parenthesis +-- Bigint, long tokens with parenthesis, will not squash SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -264,44 +472,47 @@ SELECT * FROM test_squash_bigint WHERE id IN ----+------ (0 rows) +SELECT * FROM test_squash_bigint WHERE id = ANY(ARRAY[ + abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700), + abs(800), abs(900), abs(1000), ((abs(1100)))]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls -------------------------------------------------------------------------+------- - SELECT * FROM test_squash_bigint WHERE id IN +| 1 + SELECT * FROM test_squash_bigint WHERE id IN +| 2 (abs($1), abs($2), abs($3), abs($4), abs($5), abs($6), abs($7),+| abs($8), abs($9), abs($10), ((abs($11)))) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) --- CoerceViaIO, SubLink instead of a Const -CREATE TABLE test_squash_jsonb (id int, data jsonb); +-- Multiple FuncExpr's. Will not squash SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- t (1 row) -SELECT * FROM test_squash_jsonb WHERE data IN - ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, - (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, - (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, - (SELECT '"10"')::jsonb); - id | data -----+------ -(0 rows) +SELECT WHERE 1 IN (1::int::bigint::int, 2::int::bigint::int); +-- +(1 row) + +SELECT WHERE 1 = ANY(ARRAY[1::int::bigint::int, 2::int::bigint::int]); +-- +(1 row) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -----------------------------------------------------------------------+------- - SELECT * FROM test_squash_jsonb WHERE data IN +| 1 - ((SELECT $1)::jsonb, (SELECT $2)::jsonb, (SELECT $3)::jsonb,+| - (SELECT $4)::jsonb, (SELECT $5)::jsonb, (SELECT $6)::jsonb,+| - (SELECT $7)::jsonb, (SELECT $8)::jsonb, (SELECT $9)::jsonb,+| - (SELECT $10)::jsonb) | - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +----------------------------------------------------+------- + SELECT WHERE $1 IN ($2 /*, ... */) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) +-- -- CoerceViaIO +-- -- Create some dummy type to force CoerceViaIO CREATE TYPE casttesttype; CREATE FUNCTION casttesttype_in(cstring) @@ -349,15 +560,25 @@ SELECT * FROM test_squash_cast WHERE data IN ----+------ (0 rows) +SELECT * FROM test_squash_cast WHERE data = ANY (ARRAY + [1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype, + 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype, + 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype, + 10::int4::casttesttype, 11::int4::casttesttype]); + id | data +----+------ +(0 rows) + SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ----------------------------------------------------+------- - SELECT * FROM test_squash_cast WHERE data IN +| 1 - ($1 /*, ... */::int4::casttesttype) | + SELECT * FROM test_squash_cast WHERE data IN +| 2 + ($1 /*, ... */) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Some casting expression are simplified to Const +CREATE TABLE test_squash_jsonb (id int, data jsonb); SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- @@ -366,8 +587,16 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_jsonb WHERE data IN (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb, - ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb, - ( '"9"')::jsonb, ( '"10"')::jsonb); + ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb, + ('"9"')::jsonb, ('"10"')::jsonb); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY + [('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb, + ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb, + ('"9"')::jsonb, ('"10"')::jsonb]); id | data ----+------ (0 rows) @@ -375,28 +604,144 @@ SELECT * FROM test_squash_jsonb WHERE data IN SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ----------------------------------------------------+------- - SELECT * FROM test_squash_jsonb WHERE data IN +| 1 - (($1 /*, ... */)::jsonb) | + SELECT * FROM test_squash_jsonb WHERE data IN +| 2 + ($1 /*, ... */) | SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) +-- CoerceViaIO, SubLink instead of a Const. Will not squash +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM test_squash_jsonb WHERE data IN + ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, + (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, + (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, + (SELECT '"10"')::jsonb); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash_jsonb WHERE data = ANY(ARRAY + [(SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, + (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, + (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, + (SELECT '"10"')::jsonb]); + id | data +----+------ +(0 rows) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------------------------+------- + SELECT * FROM test_squash_jsonb WHERE data IN +| 2 + ((SELECT $1)::jsonb, (SELECT $2)::jsonb, (SELECT $3)::jsonb,+| + (SELECT $4)::jsonb, (SELECT $5)::jsonb, (SELECT $6)::jsonb,+| + (SELECT $7)::jsonb, (SELECT $8)::jsonb, (SELECT $9)::jsonb,+| + (SELECT $10)::jsonb) | + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- Multiple CoerceViaIO wrapping a constant. Will not squash +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT WHERE 1 IN (1::text::int::text::int, 1::text::int::text::int); +-- +(1 row) + +SELECT WHERE 1 = ANY(ARRAY[1::text::int::text::int, 1::text::int::text::int]); +-- +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +-------------------------------------------------------------------------+------- + SELECT WHERE $1 IN ($2::text::int::text::int, $3::text::int::text::int) | 2 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(2 rows) + +-- -- RelabelType +-- SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- t (1 row) -SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid); +-- if there is only one level of RelabelType, the list will be squashable +SELECT * FROM test_squash WHERE id IN + (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid); + id | data +----+------ +(0 rows) + +SELECT ARRAY[1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid]; + array +--------------------- + {1,2,3,4,5,6,7,8,9} +(1 row) + +-- if there is at least one element with multiple levels of RelabelType, +-- the list will not be squashable +SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid::int::oid); + id | data +----+------ +(0 rows) + +SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid, 2::oid::int::oid]); id | data ----+------ (0 rows) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - query | calls -------------------------------------------------------------+------- - SELECT * FROM test_squash WHERE id IN ($1 /*, ... */::oid) | 1 - SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + query | calls +--------------------------------------------------------------------+------- + SELECT * FROM test_squash WHERE id IN +| 1 + ($1 /*, ... */) | + SELECT * FROM test_squash WHERE id IN ($1::oid, $2::oid::int::oid) | 2 + SELECT ARRAY[$1 /*, ... */] | 1 + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 +(4 rows) + +-- +-- edge cases +-- +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- for nested arrays, only constants are squashed +SELECT ARRAY[ + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] + ]; + array +----------------------------------------------------------------------------------------------- + {{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}} +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------+------- + SELECT ARRAY[ +| 1 + ARRAY[$1 /*, ... */], +| + ARRAY[$2 /*, ... */], +| + ARRAY[$3 /*, ... */], +| + ARRAY[$4 /*, ... */] +| + ] | + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 (2 rows) -- Test constants evaluation in a CTE, which was causing issues in the past @@ -409,23 +754,59 @@ FROM cte; -------- (0 rows) --- Simple array would be squashed as well SELECT pg_stat_statements_reset() IS NOT NULL AS t; t --- t (1 row) -SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]; - array ------------------------- - {1,2,3,4,5,6,7,8,9,10} +-- Rewritten as an OpExpr, so it will not be squashed +select where '1' IN ('1'::int, '2'::int::text); +-- +(1 row) + +-- Rewritten as an ArrayExpr, so it will be squashed +select where '1' IN ('1'::int, '2'::int); +-- (1 row) SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; query | calls ----------------------------------------------------+------- - SELECT ARRAY[$1 /*, ... */] | 1 SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + select where $1 IN ($2 /*, ... */) | 1 + select where $1 IN ($2::int, $3::int::text) | 1 +(3 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- Both of these queries will be rewritten as an ArrayExpr, so they +-- will be squashed, and have a similar queryId +select where '1' IN ('1'::int::text, '2'::int::text); +-- +(1 row) + +select where '1' = ANY (array['1'::int::text, '2'::int::text]); +-- +(1 row) + +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + query | calls +----------------------------------------------------+------- + SELECT pg_stat_statements_reset() IS NOT NULL AS t | 1 + select where $1 IN ($2 /*, ... */) | 2 (2 rows) +-- +-- cleanup +-- +DROP TABLE test_squash; +DROP TABLE test_float; +DROP TABLE test_squash_numeric; +DROP TABLE test_squash_bigint; +DROP TABLE test_squash_cast CASCADE; +DROP TABLE test_squash_jsonb; diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out index aa4f0f7e628..060d4416dd7 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -540,7 +540,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -------+------+---------------------------------------------------- 2 | 0 | DEALLOCATE $1 2 | 0 | DEALLOCATE ALL - 2 | 2 | SELECT $1 AS a + 2 | 2 | PREPARE stat_select AS SELECT $1 AS a 1 | 1 | SELECT $1 as a 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (5 rows) diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 129001c70c8..ecc7f2fb266 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -2810,14 +2810,12 @@ generate_normalized_query(JumbleState *jstate, const char *query, { char *norm_query; int query_len = *query_len_p; - int i, - norm_query_buflen, /* Space allowed for norm_query */ + int norm_query_buflen, /* Space allowed for norm_query */ len_to_wrt, /* Length (in bytes) to write */ quer_loc = 0, /* Source query byte location */ n_quer_loc = 0, /* Normalized query byte location */ last_off = 0, /* Offset from start for previous tok */ last_tok_len = 0; /* Length (in bytes) of that tok */ - bool in_squashed = false; /* in a run of squashed consts? */ int num_constants_replaced = 0; /* @@ -2832,16 +2830,13 @@ generate_normalized_query(JumbleState *jstate, const char *query, * certainly isn't more than 11 bytes, even if n reaches INT_MAX. We * could refine that limit based on the max value of n for the current * query, but it hardly seems worth any extra effort to do so. - * - * Note this also gives enough room for the commented-out ", ..." list - * syntax used by constant squashing. */ norm_query_buflen = query_len + jstate->clocations_count * 10; /* Allocate result buffer */ norm_query = palloc(norm_query_buflen + 1); - for (i = 0; i < jstate->clocations_count; i++) + for (int i = 0; i < jstate->clocations_count; i++) { int off, /* Offset from start for cur tok */ tok_len; /* Length (in bytes) of that tok */ @@ -2856,65 +2851,24 @@ generate_normalized_query(JumbleState *jstate, const char *query, if (tok_len < 0) continue; /* ignore any duplicates */ + /* Copy next chunk (what precedes the next constant) */ + len_to_wrt = off - last_off; + len_to_wrt -= last_tok_len; + Assert(len_to_wrt >= 0); + memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); + n_quer_loc += len_to_wrt; + /* - * What to do next depends on whether we're squashing constant lists, - * and whether we're already in a run of such constants. + * And insert a param symbol in place of the constant token; and, if + * we have a squashable list, insert a placeholder comment starting + * from the list's second value. */ - if (!jstate->clocations[i].squashed) - { - /* - * This location corresponds to a constant not to be squashed. - * Print what comes before the constant ... - */ - len_to_wrt = off - last_off; - len_to_wrt -= last_tok_len; + n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d%s", + num_constants_replaced + 1 + jstate->highest_extern_param_id, + jstate->clocations[i].squashed ? " /*, ... */" : ""); + num_constants_replaced++; - Assert(len_to_wrt >= 0); - - memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); - n_quer_loc += len_to_wrt; - - /* ... and then a param symbol replacing the constant itself */ - n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d", - num_constants_replaced++ + 1 + jstate->highest_extern_param_id); - - /* In case previous constants were merged away, stop doing that */ - in_squashed = false; - } - else if (!in_squashed) - { - /* - * This location is the start position of a run of constants to be - * squashed, so we need to print the representation of starting a - * group of stashed constants. - * - * Print what comes before the constant ... - */ - len_to_wrt = off - last_off; - len_to_wrt -= last_tok_len; - Assert(len_to_wrt >= 0); - Assert(i + 1 < jstate->clocations_count); - Assert(jstate->clocations[i + 1].squashed); - memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); - n_quer_loc += len_to_wrt; - - /* ... and then start a run of squashed constants */ - n_quer_loc += sprintf(norm_query + n_quer_loc, "$%d /*, ... */", - num_constants_replaced++ + 1 + jstate->highest_extern_param_id); - - /* The next location will match the block below, to end the run */ - in_squashed = true; - } - else - { - /* - * The second location of a run of squashable elements; this - * indicates its end. - */ - in_squashed = false; - } - - /* Otherwise the constant is squashed away -- move forward */ + /* move forward */ quer_loc = off + tok_len; last_off = off; last_tok_len = tok_len; @@ -3005,6 +2959,9 @@ fill_in_constant_lengths(JumbleState *jstate, const char *query, Assert(loc >= 0); + if (locs[i].squashed) + continue; /* squashable list, ignore */ + if (loc <= last_loc) continue; /* Duplicate constant, ignore */ diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql index 9cfe206b3b0..46f5d9b951c 100644 --- a/contrib/pg_stat_statements/sql/planning.sql +++ b/contrib/pg_stat_statements/sql/planning.sql @@ -20,11 +20,11 @@ SELECT 42; SELECT 42; SELECT 42; SELECT plans, calls, rows, query FROM pg_stat_statements - WHERE query NOT LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C"; + WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C"; -- for the prepared statement we expect at least one replan, but cache -- invalidations could force more SELECT plans >= 2 AND plans <= calls AS plans_ok, calls, rows, query FROM pg_stat_statements - WHERE query LIKE 'SELECT COUNT%' ORDER BY query COLLATE "C"; + WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C"; -- Cleanup DROP TABLE stats_plan_test; diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql index 189d405512f..11662cde08c 100644 --- a/contrib/pg_stat_statements/sql/select.sql +++ b/contrib/pg_stat_statements/sql/select.sql @@ -87,6 +87,14 @@ SELECT WHERE (1, 2) IN ((1, 2), (2, 3)); SELECT WHERE (3, 4) IN ((5, 6), (8, 7)); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- with the last element being an explicit function call with an argument, ensure +-- the normalization of the squashing interval is correct. +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT WHERE 1 IN (1, int4(1), int4(2)); +SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2)]); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- -- queries with locking clauses -- diff --git a/contrib/pg_stat_statements/sql/squashing.sql b/contrib/pg_stat_statements/sql/squashing.sql index 03efd4b40c8..bd3243ec9cd 100644 --- a/contrib/pg_stat_statements/sql/squashing.sql +++ b/contrib/pg_stat_statements/sql/squashing.sql @@ -3,101 +3,160 @@ -- CREATE EXTENSION pg_stat_statements; -CREATE TABLE test_squash (id int, data int); +-- +-- Simple Lists +-- --- IN queries +CREATE TABLE test_squash (id int, data int); --- Normal scenario, too many simple constants for an IN query +-- single element will not be squashed SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash WHERE id IN (1); +SELECT ARRAY[1]; +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- more than 1 element in a list will be squashed +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash WHERE id IN (1, 2, 3); +SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4); +SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5); +SELECT ARRAY[1, 2, 3]; +SELECT ARRAY[1, 2, 3, 4]; +SELECT ARRAY[1, 2, 3, 4, 5]; SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9); -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); -SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +-- built-in functions will be squashed +-- the IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT WHERE 1 IN (1, int4(1), int4(2), 2); +SELECT WHERE 1 = ANY (ARRAY[1, int4(1), int4(2), 2]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; --- More conditions in the query +-- external parameters will not be squashed SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5) \bind 1 2 3 4 5 +; +SELECT * FROM test_squash WHERE id::text = ANY(ARRAY[$1, $2, $3, $4, $5]) \bind 1 2 3 4 5 +; +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- neither are prepared statements +-- the IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +PREPARE p1(int, int, int, int, int) AS +SELECT * FROM test_squash WHERE id IN ($1, $2, $3, $4, $5); +EXECUTE p1(1, 2, 3, 4, 5); +DEALLOCATE p1; +PREPARE p1(int, int, int, int, int) AS +SELECT * FROM test_squash WHERE id = ANY(ARRAY[$1, $2, $3, $4, $5]); +EXECUTE p1(1, 2, 3, 4, 5); +DEALLOCATE p1; +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- More conditions in the query +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND data = 2; SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND data = 2; SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND data = 2; +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) AND data = 2; +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AND data = 2; +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]) AND data = 2; SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Multiple squashed intervals SELECT pg_stat_statements_reset() IS NOT NULL AS t; - SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9) AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9); SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); SELECT * FROM test_squash WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11) AND data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9]); +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]); +SELECT * FROM test_squash WHERE id = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]) + AND data = ANY (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; - --- No constants simplification for OpExpr SELECT pg_stat_statements_reset() IS NOT NULL AS t; --- In the following two queries the operator expressions (+) and (@) have --- different oppno, and will be given different query_id if squashed, even though --- the normalized query will be the same +-- No constants squashing for OpExpr +-- The IN and ARRAY forms of this statement will have the same queryId +SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash WHERE id IN (1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9); SELECT * FROM test_squash WHERE id IN (@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9'); +SELECT * FROM test_squash WHERE id = ANY(ARRAY + [1 + 1, 2 + 2, 3 + 3, 4 + 4, 5 + 5, 6 + 6, 7 + 7, 8 + 8, 9 + 9]); +SELECT * FROM test_squash WHERE id = ANY(ARRAY + [@ '-1', @ '-2', @ '-3', @ '-4', @ '-5', @ '-6', @ '-7', @ '-8', @ '-9']); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- -- FuncExpr +-- -- Verify multiple type representation end up with the same query_id CREATE TABLE test_float (data float); +-- The casted ARRAY expressions will have the same queryId as the IN clause +-- form of the query SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT data FROM test_float WHERE data IN (1, 2); SELECT data FROM test_float WHERE data IN (1, '2'); SELECT data FROM test_float WHERE data IN ('1', 2); SELECT data FROM test_float WHERE data IN ('1', '2'); SELECT data FROM test_float WHERE data IN (1.0, 1.0); +SELECT data FROM test_float WHERE data = ANY(ARRAY['1'::double precision, '2'::double precision]); +SELECT data FROM test_float WHERE data = ANY(ARRAY[1.0::double precision, 1.0::double precision]); +SELECT data FROM test_float WHERE data = ANY(ARRAY[1, 2]); +SELECT data FROM test_float WHERE data = ANY(ARRAY[1, '2']); +SELECT data FROM test_float WHERE data = ANY(ARRAY['1', 2]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Numeric type, implicit cast is squashed CREATE TABLE test_squash_numeric (id int, data numeric(5, 2)); SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_numeric WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT * FROM test_squash_numeric WHERE data = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Bigint, implicit cast is squashed CREATE TABLE test_squash_bigint (id int, data bigint); SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_bigint WHERE data IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11); +SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; --- Bigint, explicit cast is not squashed +-- Bigint, explicit cast is squashed SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_bigint WHERE data IN (1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint, 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint); +SELECT * FROM test_squash_bigint WHERE data = ANY(ARRAY[ + 1::bigint, 2::bigint, 3::bigint, 4::bigint, 5::bigint, 6::bigint, + 7::bigint, 8::bigint, 9::bigint, 10::bigint, 11::bigint]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; --- Bigint, long tokens with parenthesis +-- Bigint, long tokens with parenthesis, will not squash SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_bigint WHERE id IN (abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700), abs(800), abs(900), abs(1000), ((abs(1100)))); +SELECT * FROM test_squash_bigint WHERE id = ANY(ARRAY[ + abs(100), abs(200), abs(300), abs(400), abs(500), abs(600), abs(700), + abs(800), abs(900), abs(1000), ((abs(1100)))]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; --- CoerceViaIO, SubLink instead of a Const -CREATE TABLE test_squash_jsonb (id int, data jsonb); +-- Multiple FuncExpr's. Will not squash SELECT pg_stat_statements_reset() IS NOT NULL AS t; -SELECT * FROM test_squash_jsonb WHERE data IN - ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, - (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, - (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, - (SELECT '"10"')::jsonb); +SELECT WHERE 1 IN (1::int::bigint::int, 2::int::bigint::int); +SELECT WHERE 1 = ANY(ARRAY[1::int::bigint::int, 2::int::bigint::int]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- -- CoerceViaIO +-- -- Create some dummy type to force CoerceViaIO CREATE TYPE casttesttype; @@ -141,19 +200,73 @@ SELECT * FROM test_squash_cast WHERE data IN 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype, 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype, 10::int4::casttesttype, 11::int4::casttesttype); +SELECT * FROM test_squash_cast WHERE data = ANY (ARRAY + [1::int4::casttesttype, 2::int4::casttesttype, 3::int4::casttesttype, + 4::int4::casttesttype, 5::int4::casttesttype, 6::int4::casttesttype, + 7::int4::casttesttype, 8::int4::casttesttype, 9::int4::casttesttype, + 10::int4::casttesttype, 11::int4::casttesttype]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Some casting expression are simplified to Const +CREATE TABLE test_squash_jsonb (id int, data jsonb); SELECT pg_stat_statements_reset() IS NOT NULL AS t; SELECT * FROM test_squash_jsonb WHERE data IN (('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb, - ( '"5"')::jsonb, ( '"6"')::jsonb, ( '"7"')::jsonb, ( '"8"')::jsonb, - ( '"9"')::jsonb, ( '"10"')::jsonb); + ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb, + ('"9"')::jsonb, ('"10"')::jsonb); +SELECT * FROM test_squash_jsonb WHERE data = ANY (ARRAY + [('"1"')::jsonb, ('"2"')::jsonb, ('"3"')::jsonb, ('"4"')::jsonb, + ('"5"')::jsonb, ('"6"')::jsonb, ('"7"')::jsonb, ('"8"')::jsonb, + ('"9"')::jsonb, ('"10"')::jsonb]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- CoerceViaIO, SubLink instead of a Const. Will not squash +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM test_squash_jsonb WHERE data IN + ((SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, + (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, + (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, + (SELECT '"10"')::jsonb); +SELECT * FROM test_squash_jsonb WHERE data = ANY(ARRAY + [(SELECT '"1"')::jsonb, (SELECT '"2"')::jsonb, (SELECT '"3"')::jsonb, + (SELECT '"4"')::jsonb, (SELECT '"5"')::jsonb, (SELECT '"6"')::jsonb, + (SELECT '"7"')::jsonb, (SELECT '"8"')::jsonb, (SELECT '"9"')::jsonb, + (SELECT '"10"')::jsonb]); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- Multiple CoerceViaIO wrapping a constant. Will not squash +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT WHERE 1 IN (1::text::int::text::int, 1::text::int::text::int); +SELECT WHERE 1 = ANY(ARRAY[1::text::int::text::int, 1::text::int::text::int]); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- -- RelabelType +-- + SELECT pg_stat_statements_reset() IS NOT NULL AS t; -SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid); +-- if there is only one level of RelabelType, the list will be squashable +SELECT * FROM test_squash WHERE id IN + (1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid); +SELECT ARRAY[1::oid, 2::oid, 3::oid, 4::oid, 5::oid, 6::oid, 7::oid, 8::oid, 9::oid]; +-- if there is at least one element with multiple levels of RelabelType, +-- the list will not be squashable +SELECT * FROM test_squash WHERE id IN (1::oid, 2::oid::int::oid); +SELECT * FROM test_squash WHERE id = ANY(ARRAY[1::oid, 2::oid::int::oid]); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- +-- edge cases +-- + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +-- for nested arrays, only constants are squashed +SELECT ARRAY[ + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10], + ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10] + ]; SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; -- Test constants evaluation in a CTE, which was causing issues in the past @@ -163,7 +276,26 @@ WITH cte AS ( SELECT ARRAY['a', 'b', 'c', const::varchar] AS result FROM cte; --- Simple array would be squashed as well SELECT pg_stat_statements_reset() IS NOT NULL AS t; -SELECT ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]; +-- Rewritten as an OpExpr, so it will not be squashed +select where '1' IN ('1'::int, '2'::int::text); +-- Rewritten as an ArrayExpr, so it will be squashed +select where '1' IN ('1'::int, '2'::int); +SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +-- Both of these queries will be rewritten as an ArrayExpr, so they +-- will be squashed, and have a similar queryId +select where '1' IN ('1'::int::text, '2'::int::text); +select where '1' = ANY (array['1'::int::text, '2'::int::text]); SELECT query, calls FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- +-- cleanup +-- +DROP TABLE test_squash; +DROP TABLE test_float; +DROP TABLE test_squash_numeric; +DROP TABLE test_squash_bigint; +DROP TABLE test_squash_cast CASCADE; +DROP TABLE test_squash_jsonb; diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fcd1cb85352..96936bcd3ae 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2223,8 +2223,9 @@ REVOKE ALL ON accounts FROM PUBLIC; <para> Allows <command>VACUUM</command>, <command>ANALYZE</command>, <command>CLUSTER</command>, <command>REFRESH MATERIALIZED VIEW</command>, - <command>REINDEX</command>, and <command>LOCK TABLE</command> on a - relation. + <command>REINDEX</command>, <command>LOCK TABLE</command>, + and database object statistics manipulation functions + (see <xref linkend="functions-admin-statsmod"/>) on a relation. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 2295df62d03..8c88b07dcc8 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -923,7 +923,8 @@ PostgreSQL documentation <term><option>--with-data</option></term> <listitem> <para> - Dump data. This is the default. + Output commands to restore data, if the archive contains them. + This is the default. </para> </listitem> </varlistentry> @@ -932,7 +933,8 @@ PostgreSQL documentation <term><option>--with-schema</option></term> <listitem> <para> - Dump schema (data definitions). This is the default. + Output commands to restore schema (data definitions), if the archive + contains them. This is the default. </para> </listitem> </varlistentry> @@ -941,7 +943,8 @@ PostgreSQL documentation <term><option>--with-statistics</option></term> <listitem> <para> - Dump statistics. This is the default. + Output commands to restore statistics, if the archive contains them. + This is the default. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8f7d8758ca0..15e3f3a8492 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1103,6 +1103,15 @@ SELECT $1 \parse stmt1 Outputs information about the current database connection, including TLS-related information if TLS is in use. </para> + <para> + Note that the <structfield>Client User</structfield> field shows + the user at the time of connection, while the + <structfield>Superuser</structfield> field indicates whether + the current user (in the current execution context) has + superuser privileges. These users are usually the same, but they can + differ, for example, if the current user was changed with the + <command>SET ROLE</command> command. + </para> </listitem> </varlistentry> @@ -3734,6 +3743,10 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput> </para> <para> + <command>COPY</command> is not supported while in pipeline mode. + </para> + + <para> Example: <programlisting> \startpipeline @@ -3853,7 +3866,7 @@ SELECT 1 \bind \sendpipeline (if given) is reached, or the query no longer returns the minimum number of rows. Wait the specified number of seconds (default 2) between executions. The default wait can be changed with the variable - <xref linkend="app-psql-variables-watch-interval"/>). + <xref linkend="app-psql-variables-watch-interval"/>. For backwards compatibility, <replaceable class="parameter">seconds</replaceable> can be specified with or without an <literal>interval=</literal> prefix. @@ -4752,9 +4765,10 @@ bar <term><varname>WATCH_INTERVAL</varname></term> <listitem> <para> - This variable sets the default interval which <command>\watch</command> - waits between executing the query. Specifying an interval in the - command overrides this variable. + This variable sets the default interval, in seconds, which + <command>\watch</command> waits between executing the query. The + default is 2 seconds. Specifying an interval in the command overrides + this variable. </para> </listitem> </varlistentry> diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 070f14c8b91..36544ecfd58 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -2282,9 +2282,12 @@ _bt_readfirstpage(IndexScanDesc scan, OffsetNumber offnum, ScanDirection dir) * previously-saved right link or left link. lastcurrblkno is the page that * was current at the point where the blkno link was saved, which we use to * reason about concurrent page splits/page deletions during backwards scans. + * In the common case where seized=false, blkno is either so->currPos.nextPage + * or so->currPos.prevPage, and lastcurrblkno is so->currPos.currPage. * - * On entry, caller shouldn't hold any locks or pins on any page (we work - * directly off of blkno and lastcurrblkno instead). Parallel scan callers + * On entry, so->currPos shouldn't be locked by caller. so->currPos.buf must + * be InvalidBuffer/unpinned as needed by caller (note that lastcurrblkno + * won't need to be read again in almost all cases). Parallel scan callers * that seized the scan before calling here should pass seized=true; such a * caller's blkno and lastcurrblkno arguments come from the seized scan. * seized=false callers just pass us the blkno/lastcurrblkno taken from their @@ -2301,8 +2304,8 @@ _bt_readfirstpage(IndexScanDesc scan, OffsetNumber offnum, ScanDirection dir) * success exit (except during so->dropPin index scans, when we drop the pin * eagerly to avoid blocking VACUUM). * - * If there are no more matching records in the given direction, we drop all - * locks and pins, invalidate so->currPos, and return false. + * If there are no more matching records in the given direction, we invalidate + * so->currPos (while ensuring it retains no locks or pins), and return false. * * We always release the scan for a parallel scan caller, regardless of * success or failure; we'll call _bt_parallel_release as soon as possible. diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c index 86293cba5ab..c71d1b6f2e1 100644 --- a/src/backend/access/nbtree/nbtutils.c +++ b/src/backend/access/nbtree/nbtutils.c @@ -63,7 +63,7 @@ static bool _bt_check_compare(IndexScanDesc scan, ScanDirection dir, bool *continuescan, int *ikey); static bool _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, int tupnatts, TupleDesc tupdesc, - ScanDirection dir, bool *continuescan); + ScanDirection dir, bool forcenonrequired, bool *continuescan); static void _bt_checkkeys_look_ahead(IndexScanDesc scan, BTReadPageState *pstate, int tupnatts, TupleDesc tupdesc); static int _bt_keep_natts(Relation rel, IndexTuple lastleft, @@ -2902,10 +2902,8 @@ _bt_check_compare(IndexScanDesc scan, ScanDirection dir, /* row-comparison keys need special processing */ if (key->sk_flags & SK_ROW_HEADER) { - Assert(!forcenonrequired); /* forbidden by _bt_set_startikey */ - if (_bt_check_rowcompare(key, tuple, tupnatts, tupdesc, dir, - continuescan)) + forcenonrequired, continuescan)) continue; return false; } @@ -3062,7 +3060,8 @@ _bt_check_compare(IndexScanDesc scan, ScanDirection dir, */ static bool _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, int tupnatts, - TupleDesc tupdesc, ScanDirection dir, bool *continuescan) + TupleDesc tupdesc, ScanDirection dir, + bool forcenonrequired, bool *continuescan) { ScanKey subkey = (ScanKey) DatumGetPointer(skey->sk_argument); int32 cmpresult = 0; @@ -3102,7 +3101,11 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, int tupnatts, if (isNull) { - if (subkey->sk_flags & SK_BT_NULLS_FIRST) + if (forcenonrequired) + { + /* treating scan's keys as non-required */ + } + else if (subkey->sk_flags & SK_BT_NULLS_FIRST) { /* * Since NULLs are sorted before non-NULLs, we know we have @@ -3156,8 +3159,12 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, int tupnatts, */ Assert(subkey != (ScanKey) DatumGetPointer(skey->sk_argument)); subkey--; - if ((subkey->sk_flags & SK_BT_REQFWD) && - ScanDirectionIsForward(dir)) + if (forcenonrequired) + { + /* treating scan's keys as non-required */ + } + else if ((subkey->sk_flags & SK_BT_REQFWD) && + ScanDirectionIsForward(dir)) *continuescan = false; else if ((subkey->sk_flags & SK_BT_REQBKWD) && ScanDirectionIsBackward(dir)) @@ -3209,7 +3216,7 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, int tupnatts, break; } - if (!result) + if (!result && !forcenonrequired) { /* * Tuple fails this qual. If it's a required qual for the current diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 1914859b2ee..47ffc0a2307 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -7498,6 +7498,10 @@ CreateCheckPoint(int flags) if (PriorRedoPtr != InvalidXLogRecPtr) UpdateCheckPointDistanceEstimate(RedoRecPtr - PriorRedoPtr); +#ifdef USE_INJECTION_POINTS + INJECTION_POINT("checkpoint-before-old-wal-removal", NULL); +#endif + /* * Delete old log files, those no longer needed for last checkpoint to * prevent the disk holding the xlog from growing full. diff --git a/src/backend/executor/execGrouping.c b/src/backend/executor/execGrouping.c index 255bd795361..b5400749353 100644 --- a/src/backend/executor/execGrouping.c +++ b/src/backend/executor/execGrouping.c @@ -144,7 +144,7 @@ execTuplesHashPrepare(int numCols, * hashfunctions: FmgrInfos of datatype-specific hashing functions to use * collations: collations to use in comparisons * nbuckets: initial estimate of hashtable size - * additionalsize: size of data stored in ->additional + * additionalsize: size of data that may be stored along with the hash entry * metacxt: memory context for long-lived allocation, but not per-entry data * tablecxt: memory context in which to store table entries * tempcxt: short-lived context for evaluation hash and comparison functions @@ -288,7 +288,7 @@ ResetTupleHashTable(TupleHashTable hashtable) * * If isnew isn't NULL, then a new entry is created if no existing entry * matches. On return, *isnew is true if the entry is newly created, - * false if it existed already. ->additional_data in the new entry has + * false if it existed already. The additional data in the new entry has * been zeroed. */ TupleHashEntry diff --git a/src/backend/executor/nodeTidrangescan.c b/src/backend/executor/nodeTidrangescan.c index ab2eab9596e..26f7420b64b 100644 --- a/src/backend/executor/nodeTidrangescan.c +++ b/src/backend/executor/nodeTidrangescan.c @@ -128,9 +128,11 @@ TidExprListCreate(TidRangeScanState *tidrangestate) * TidRangeEval * * Compute and set node's block and offset range to scan by evaluating - * the trss_tidexprs. Returns false if we detect the range cannot + * node->trss_tidexprs. Returns false if we detect the range cannot * contain any tuples. Returns true if it's possible for the range to - * contain tuples. + * contain tuples. We don't bother validating that trss_mintid is less + * than or equal to trss_maxtid, as the scan_set_tidrange() table AM + * function will handle that. * ---------------------------------------------------------------- */ static bool diff --git a/src/backend/nodes/gen_node_support.pl b/src/backend/nodes/gen_node_support.pl index c8595109b0e..9ecddb14231 100644 --- a/src/backend/nodes/gen_node_support.pl +++ b/src/backend/nodes/gen_node_support.pl @@ -1329,7 +1329,7 @@ _jumble${n}(JumbleState *jstate, Node *node) # Node type. Squash constants if requested. if ($query_jumble_squash) { - print $jff "\tJUMBLE_ELEMENTS($f);\n" + print $jff "\tJUMBLE_ELEMENTS($f, node);\n" unless $query_jumble_ignore; } else diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 25e08ba3426..eaf391fc2ab 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -653,6 +653,8 @@ _outA_Expr(StringInfo str, const A_Expr *node) WRITE_NODE_FIELD(lexpr); WRITE_NODE_FIELD(rexpr); + WRITE_LOCATION_FIELD(rexpr_list_start); + WRITE_LOCATION_FIELD(rexpr_list_end); WRITE_LOCATION_FIELD(location); } diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index ac3cb3d9caf..fb33e6931ad 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -61,9 +61,9 @@ static void AppendJumble(JumbleState *jstate, const unsigned char *value, Size size); static void FlushPendingNulls(JumbleState *jstate); static void RecordConstLocation(JumbleState *jstate, - int location, bool squashed); + int location, int len); static void _jumbleNode(JumbleState *jstate, Node *node); -static void _jumbleElements(JumbleState *jstate, List *elements); +static void _jumbleElements(JumbleState *jstate, List *elements, Node *node); static void _jumbleA_Const(JumbleState *jstate, Node *node); static void _jumbleList(JumbleState *jstate, Node *node); static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node); @@ -373,15 +373,17 @@ FlushPendingNulls(JumbleState *jstate) /* - * Record location of constant within query string of query tree that is - * currently being walked. + * Record the location of some kind of constant within a query string. + * These are not only bare constants but also expressions that ultimately + * constitute a constant, such as those inside casts and simple function + * calls. * - * 'squashed' signals that the constant represents the first or the last - * element in a series of merged constants, and everything but the first/last - * element contributes nothing to the jumble hash. + * If length is -1, it indicates a single such constant element. If + * it's a positive integer, it indicates the length of a squashable + * list of them. */ static void -RecordConstLocation(JumbleState *jstate, int location, bool squashed) +RecordConstLocation(JumbleState *jstate, int location, int len) { /* -1 indicates unknown or undefined location */ if (location >= 0) @@ -396,9 +398,14 @@ RecordConstLocation(JumbleState *jstate, int location, bool squashed) sizeof(LocationLen)); } jstate->clocations[jstate->clocations_count].location = location; - /* initialize lengths to -1 to simplify third-party module usage */ - jstate->clocations[jstate->clocations_count].squashed = squashed; - jstate->clocations[jstate->clocations_count].length = -1; + + /* + * Lengths are either positive integers (indicating a squashable + * list), or -1. + */ + Assert(len > -1 || len == -1); + jstate->clocations[jstate->clocations_count].length = len; + jstate->clocations[jstate->clocations_count].squashed = (len > -1); jstate->clocations_count++; } } @@ -408,12 +415,12 @@ RecordConstLocation(JumbleState *jstate, int location, bool squashed) * deduce that the expression is a constant: * * - Ignore a possible wrapping RelabelType and CoerceViaIO. - * - If it's a FuncExpr, check that the function is an implicit + * - If it's a FuncExpr, check that the function is a builtin * cast and its arguments are Const. * - Otherwise test if the expression is a simple Const. */ static bool -IsSquashableConst(Node *element) +IsSquashableConstant(Node *element) { if (IsA(element, RelabelType)) element = (Node *) ((RelabelType *) element)->arg; @@ -421,32 +428,50 @@ IsSquashableConst(Node *element) if (IsA(element, CoerceViaIO)) element = (Node *) ((CoerceViaIO *) element)->arg; - if (IsA(element, FuncExpr)) + switch (nodeTag(element)) { - FuncExpr *func = (FuncExpr *) element; - ListCell *temp; + case T_FuncExpr: + { + FuncExpr *func = (FuncExpr *) element; + ListCell *temp; - if (func->funcformat != COERCE_IMPLICIT_CAST && - func->funcformat != COERCE_EXPLICIT_CAST) - return false; + if (func->funcformat != COERCE_IMPLICIT_CAST && + func->funcformat != COERCE_EXPLICIT_CAST) + return false; - if (func->funcid > FirstGenbkiObjectId) - return false; + if (func->funcid > FirstGenbkiObjectId) + return false; - foreach(temp, func->args) - { - Node *arg = lfirst(temp); + /* + * We can check function arguments recursively, being careful + * about recursing too deep. At each recursion level it's + * enough to test the stack on the first element. (Note that + * I wasn't able to hit this without bloating the stack + * artificially in this function: the parser errors out before + * stack size becomes a problem here.) + */ + foreach(temp, func->args) + { + Node *arg = lfirst(temp); + + if (!IsA(arg, Const)) + { + if (foreach_current_index(temp) == 0 && + stack_is_too_deep()) + return false; + else if (!IsSquashableConstant(arg)) + return false; + } + } + + return true; + } - if (!IsA(arg, Const)) /* XXX we could recurse here instead */ + default: + if (!IsA(element, Const)) return false; - } - - return true; } - if (!IsA(element, Const)) - return false; - return true; } @@ -461,35 +486,29 @@ IsSquashableConst(Node *element) * expressions. */ static bool -IsSquashableConstList(List *elements, Node **firstExpr, Node **lastExpr) +IsSquashableConstantList(List *elements) { ListCell *temp; - /* - * If squashing is disabled, or the list is too short, we don't try to - * squash it. - */ + /* If the list is too short, we don't try to squash it. */ if (list_length(elements) < 2) return false; foreach(temp, elements) { - if (!IsSquashableConst(lfirst(temp))) + if (!IsSquashableConstant(lfirst(temp))) return false; } - *firstExpr = linitial(elements); - *lastExpr = llast(elements); - return true; } #define JUMBLE_NODE(item) \ _jumbleNode(jstate, (Node *) expr->item) -#define JUMBLE_ELEMENTS(list) \ - _jumbleElements(jstate, (List *) expr->list) +#define JUMBLE_ELEMENTS(list, node) \ + _jumbleElements(jstate, (List *) expr->list, node) #define JUMBLE_LOCATION(location) \ - RecordConstLocation(jstate, expr->location, false) + RecordConstLocation(jstate, expr->location, -1) #define JUMBLE_FIELD(item) \ do { \ if (sizeof(expr->item) == 8) \ @@ -517,36 +536,36 @@ do { \ #include "queryjumblefuncs.funcs.c" /* - * We jumble lists of constant elements as one individual item regardless - * of how many elements are in the list. This means different queries - * jumble to the same query_id, if the only difference is the number of - * elements in the list. + * We try to jumble lists of expressions as one individual item regardless + * of how many elements are in the list. This is know as squashing, which + * results in different queries jumbling to the same query_id, if the only + * difference is the number of elements in the list. + * + * We allow constants to be squashed. To normalize such queries, we use + * the start and end locations of the list of elements in a list. */ static void -_jumbleElements(JumbleState *jstate, List *elements) +_jumbleElements(JumbleState *jstate, List *elements, Node *node) { - Node *first, - *last; + bool normalize_list = false; - if (IsSquashableConstList(elements, &first, &last)) + if (IsSquashableConstantList(elements)) { - /* - * If this list of elements is squashable, keep track of the location - * of its first and last elements. When reading back the locations - * array, we'll see two consecutive locations with ->squashed set to - * true, indicating the location of initial and final elements of this - * list. - * - * For the limited set of cases we support now (implicit coerce via - * FuncExpr, Const) it's fine to use exprLocation of the 'last' - * expression, but if more complex composite expressions are to be - * supported (e.g., OpExpr or FuncExpr as an explicit call), more - * sophisticated tracking will be needed. - */ - RecordConstLocation(jstate, exprLocation(first), true); - RecordConstLocation(jstate, exprLocation(last), true); + if (IsA(node, ArrayExpr)) + { + ArrayExpr *aexpr = (ArrayExpr *) node; + + if (aexpr->list_start > 0 && aexpr->list_end > 0) + { + RecordConstLocation(jstate, + aexpr->list_start + 1, + (aexpr->list_end - aexpr->list_start) - 1); + normalize_list = true; + } + } } - else + + if (!normalize_list) { _jumbleNode(jstate, (Node *) elements); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 8c90ab54af8..48b5d13b9b6 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -526,6 +526,8 @@ _readA_Expr(void) READ_NODE_FIELD(lexpr); READ_NODE_FIELD(rexpr); + READ_LOCATION_FIELD(rexpr_list_start); + READ_LOCATION_FIELD(rexpr_list_end); READ_LOCATION_FIELD(location); READ_DONE(); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index a16fdd65601..34f7c17f576 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -239,102 +239,23 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState, } /* - * setQueryLocationAndLength - * Set query's location and length from statement and ParseState - * - * Some statements, like PreparableStmt, can be located within parentheses. - * For example "(SELECT 1)" or "COPY (UPDATE ...) to x;". For those, we - * cannot use the whole string from the statement's location or the SQL - * string would yield incorrectly. The parser will set stmt_len, reflecting - * the size of the statement within the parentheses. Thus, when stmt_len is - * available, we need to use it for the Query's stmt_len. - * - * For other cases, the parser can't provide the length of individual - * statements. However, we have the statement's location plus the length - * (p_stmt_len) and location (p_stmt_location) of the top level RawStmt, - * stored in pstate. Thus, the statement's length is the RawStmt's length - * minus how much we've advanced in the RawStmt's string. If p_stmt_len - * is 0, the SQL string is used up to its end. - */ -static void -setQueryLocationAndLength(ParseState *pstate, Query *qry, Node *parseTree) -{ - ParseLoc stmt_len = 0; - - switch (nodeTag(parseTree)) - { - case T_InsertStmt: - qry->stmt_location = ((InsertStmt *) parseTree)->stmt_location; - stmt_len = ((InsertStmt *) parseTree)->stmt_len; - break; - - case T_DeleteStmt: - qry->stmt_location = ((DeleteStmt *) parseTree)->stmt_location; - stmt_len = ((DeleteStmt *) parseTree)->stmt_len; - break; - - case T_UpdateStmt: - qry->stmt_location = ((UpdateStmt *) parseTree)->stmt_location; - stmt_len = ((UpdateStmt *) parseTree)->stmt_len; - break; - - case T_MergeStmt: - qry->stmt_location = ((MergeStmt *) parseTree)->stmt_location; - stmt_len = ((MergeStmt *) parseTree)->stmt_len; - break; - - case T_SelectStmt: - qry->stmt_location = ((SelectStmt *) parseTree)->stmt_location; - stmt_len = ((SelectStmt *) parseTree)->stmt_len; - break; - - case T_PLAssignStmt: - qry->stmt_location = ((PLAssignStmt *) parseTree)->location; - break; - - default: - qry->stmt_location = pstate->p_stmt_location; - break; - } - - if (stmt_len > 0) - { - /* Statement's length is known, use it */ - qry->stmt_len = stmt_len; - } - else if (pstate->p_stmt_len > 0) - { - /* - * The top RawStmt's length is known, so calculate the statement's - * length from the statement's location and the RawStmt's length and - * location. - */ - qry->stmt_len = pstate->p_stmt_len - (qry->stmt_location - pstate->p_stmt_location); - } - - /* The calculated statement length should be calculated as positive. */ - Assert(qry->stmt_len >= 0); -} - -/* * transformTopLevelStmt - * transform a Parse tree into a Query tree. * - * This function is just responsible for storing location data - * from the RawStmt into the ParseState. + * This function is just responsible for transferring statement location data + * from the RawStmt into the finished Query. */ Query * transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree) { Query *result; - /* Store RawStmt's length and location in pstate */ - pstate->p_stmt_len = parseTree->stmt_len; - pstate->p_stmt_location = parseTree->stmt_location; - /* We're at top level, so allow SELECT INTO */ result = transformOptionalSelectInto(pstate, parseTree->stmt); + result->stmt_location = parseTree->stmt_location; + result->stmt_len = parseTree->stmt_len; + return result; } @@ -503,7 +424,6 @@ transformStmt(ParseState *pstate, Node *parseTree) /* Mark as original query until we learn differently */ result->querySource = QSRC_ORIGINAL; result->canSetTag = true; - setQueryLocationAndLength(pstate, result, parseTree); return result; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0b5652071d1..50f53159d58 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -154,7 +154,6 @@ static void base_yyerror(YYLTYPE *yylloc, core_yyscan_t yyscanner, const char *msg); static RawStmt *makeRawStmt(Node *stmt, int stmt_location); static void updateRawStmtEnd(RawStmt *rs, int end_location); -static void updatePreparableStmtEnd(Node *n, int end_location); static Node *makeColumnRef(char *colname, List *indirection, int location, core_yyscan_t yyscanner); static Node *makeTypeCast(Node *arg, TypeName *typename, int location); @@ -178,13 +177,13 @@ static void insertSelectOptions(SelectStmt *stmt, SelectLimit *limitClause, WithClause *withClause, core_yyscan_t yyscanner); -static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location); +static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Float *v); static Node *makeAndExpr(Node *lexpr, Node *rexpr, int location); static Node *makeOrExpr(Node *lexpr, Node *rexpr, int location); static Node *makeNotExpr(Node *expr, int location); -static Node *makeAArrayExpr(List *elements, int location); +static Node *makeAArrayExpr(List *elements, int location, int end_location); static Node *makeSQLValueFunction(SQLValueFunctionOp op, int32 typmod, int location); static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, @@ -523,7 +522,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem %type <node> def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound - columnref in_expr having_clause func_table xmltable array_expr + columnref having_clause func_table xmltable array_expr OptWhereClause operator_def_arg %type <list> opt_column_and_period_list %type <list> rowsfrom_item rowsfrom_list opt_col_def_list @@ -3417,7 +3416,6 @@ CopyStmt: COPY opt_binary qualified_name opt_column_list { CopyStmt *n = makeNode(CopyStmt); - updatePreparableStmtEnd($3, @4); n->relation = NULL; n->query = $3; n->attlist = NIL; @@ -12240,7 +12238,6 @@ InsertStmt: $5->onConflictClause = $6; $5->returningClause = $7; $5->withClause = $1; - $5->stmt_location = @$; $$ = (Node *) $5; } ; @@ -12431,7 +12428,6 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->whereClause = $6; n->returningClause = $7; n->withClause = $1; - n->stmt_location = @$; $$ = (Node *) n; } ; @@ -12506,7 +12502,6 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->whereClause = $7; n->returningClause = $8; n->withClause = $1; - n->stmt_location = @$; $$ = (Node *) n; } ; @@ -12584,7 +12579,6 @@ MergeStmt: m->joinCondition = $8; m->mergeWhenClauses = $9; m->returningClause = $10; - m->stmt_location = @$; $$ = (Node *) m; } @@ -12825,20 +12819,7 @@ SelectStmt: select_no_parens %prec UMINUS ; select_with_parens: - '(' select_no_parens ')' - { - SelectStmt *n = (SelectStmt *) $2; - - /* - * As SelectStmt's location starts at the SELECT keyword, - * we need to track the length of the SelectStmt within - * parentheses to be able to extract the relevant part - * of the query. Without this, the RawStmt's length would - * be used and would include the closing parenthesis. - */ - n->stmt_len = @3 - @2; - $$ = $2; - } + '(' select_no_parens ')' { $$ = $2; } | '(' select_with_parens ')' { $$ = $2; } ; @@ -12960,7 +12941,6 @@ simple_select: n->groupDistinct = ($7)->distinct; n->havingClause = $8; n->windowClause = $9; - n->stmt_location = @1; $$ = (Node *) n; } | SELECT distinct_clause target_list @@ -12978,7 +12958,6 @@ simple_select: n->groupDistinct = ($7)->distinct; n->havingClause = $8; n->windowClause = $9; - n->stmt_location = @1; $$ = (Node *) n; } | values_clause { $$ = $1; } @@ -12999,20 +12978,19 @@ simple_select: n->targetList = list_make1(rt); n->fromClause = list_make1($2); - n->stmt_location = @1; $$ = (Node *) n; } | select_clause UNION set_quantifier select_clause { - $$ = makeSetOp(SETOP_UNION, $3 == SET_QUANTIFIER_ALL, $1, $4, @1); + $$ = makeSetOp(SETOP_UNION, $3 == SET_QUANTIFIER_ALL, $1, $4); } | select_clause INTERSECT set_quantifier select_clause { - $$ = makeSetOp(SETOP_INTERSECT, $3 == SET_QUANTIFIER_ALL, $1, $4, @1); + $$ = makeSetOp(SETOP_INTERSECT, $3 == SET_QUANTIFIER_ALL, $1, $4); } | select_clause EXCEPT set_quantifier select_clause { - $$ = makeSetOp(SETOP_EXCEPT, $3 == SET_QUANTIFIER_ALL, $1, $4, @1); + $$ = makeSetOp(SETOP_EXCEPT, $3 == SET_QUANTIFIER_ALL, $1, $4); } ; @@ -13590,7 +13568,6 @@ values_clause: { SelectStmt *n = makeNode(SelectStmt); - n->stmt_location = @1; n->valuesLists = list_make1($3); $$ = (Node *) n; } @@ -15287,49 +15264,50 @@ a_expr: c_expr { $$ = $1; } (Node *) list_make2($5, $7), @2); } - | a_expr IN_P in_expr + | a_expr IN_P select_with_parens { - /* in_expr returns a SubLink or a list of a_exprs */ - if (IsA($3, SubLink)) - { - /* generate foo = ANY (subquery) */ - SubLink *n = (SubLink *) $3; + /* generate foo = ANY (subquery) */ + SubLink *n = makeNode(SubLink); - n->subLinkType = ANY_SUBLINK; - n->subLinkId = 0; - n->testexpr = $1; - n->operName = NIL; /* show it's IN not = ANY */ - n->location = @2; - $$ = (Node *) n; - } - else - { - /* generate scalar IN expression */ - $$ = (Node *) makeSimpleA_Expr(AEXPR_IN, "=", $1, $3, @2); - } + n->subselect = $3; + n->subLinkType = ANY_SUBLINK; + n->subLinkId = 0; + n->testexpr = $1; + n->operName = NIL; /* show it's IN not = ANY */ + n->location = @2; + $$ = (Node *) n; } - | a_expr NOT_LA IN_P in_expr %prec NOT_LA + | a_expr IN_P '(' expr_list ')' { - /* in_expr returns a SubLink or a list of a_exprs */ - if (IsA($4, SubLink)) - { - /* generate NOT (foo = ANY (subquery)) */ - /* Make an = ANY node */ - SubLink *n = (SubLink *) $4; - - n->subLinkType = ANY_SUBLINK; - n->subLinkId = 0; - n->testexpr = $1; - n->operName = NIL; /* show it's IN not = ANY */ - n->location = @2; - /* Stick a NOT on top; must have same parse location */ - $$ = makeNotExpr((Node *) n, @2); - } - else - { - /* generate scalar NOT IN expression */ - $$ = (Node *) makeSimpleA_Expr(AEXPR_IN, "<>", $1, $4, @2); - } + /* generate scalar IN expression */ + A_Expr *n = makeSimpleA_Expr(AEXPR_IN, "=", $1, (Node *) $4, @2); + + n->rexpr_list_start = @3; + n->rexpr_list_end = @5; + $$ = (Node *) n; + } + | a_expr NOT_LA IN_P select_with_parens %prec NOT_LA + { + /* generate NOT (foo = ANY (subquery)) */ + SubLink *n = makeNode(SubLink); + + n->subselect = $4; + n->subLinkType = ANY_SUBLINK; + n->subLinkId = 0; + n->testexpr = $1; + n->operName = NIL; /* show it's IN not = ANY */ + n->location = @2; + /* Stick a NOT on top; must have same parse location */ + $$ = makeNotExpr((Node *) n, @2); + } + | a_expr NOT_LA IN_P '(' expr_list ')' + { + /* generate scalar NOT IN expression */ + A_Expr *n = makeSimpleA_Expr(AEXPR_IN, "<>", $1, (Node *) $5, @2); + + n->rexpr_list_start = @4; + n->rexpr_list_end = @6; + $$ = (Node *) n; } | a_expr subquery_Op sub_type select_with_parens %prec Op { @@ -16764,15 +16742,15 @@ type_list: Typename { $$ = list_make1($1); } array_expr: '[' expr_list ']' { - $$ = makeAArrayExpr($2, @1); + $$ = makeAArrayExpr($2, @1, @3); } | '[' array_expr_list ']' { - $$ = makeAArrayExpr($2, @1); + $$ = makeAArrayExpr($2, @1, @3); } | '[' ']' { - $$ = makeAArrayExpr(NIL, @1); + $$ = makeAArrayExpr(NIL, @1, @2); } ; @@ -16894,17 +16872,6 @@ trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); } | expr_list { $$ = $1; } ; -in_expr: select_with_parens - { - SubLink *n = makeNode(SubLink); - - n->subselect = $1; - /* other fields will be filled later */ - $$ = (Node *) n; - } - | '(' expr_list ')' { $$ = (Node *) $2; } - ; - /* * Define SQL-style CASE clause. * - Full specification @@ -18748,47 +18715,6 @@ updateRawStmtEnd(RawStmt *rs, int end_location) rs->stmt_len = end_location - rs->stmt_location; } -/* - * Adjust a PreparableStmt to reflect that it doesn't run to the end of the - * string. - */ -static void -updatePreparableStmtEnd(Node *n, int end_location) -{ - if (IsA(n, SelectStmt)) - { - SelectStmt *stmt = (SelectStmt *) n; - - stmt->stmt_len = end_location - stmt->stmt_location; - } - else if (IsA(n, InsertStmt)) - { - InsertStmt *stmt = (InsertStmt *) n; - - stmt->stmt_len = end_location - stmt->stmt_location; - } - else if (IsA(n, UpdateStmt)) - { - UpdateStmt *stmt = (UpdateStmt *) n; - - stmt->stmt_len = end_location - stmt->stmt_location; - } - else if (IsA(n, DeleteStmt)) - { - DeleteStmt *stmt = (DeleteStmt *) n; - - stmt->stmt_len = end_location - stmt->stmt_location; - } - else if (IsA(n, MergeStmt)) - { - MergeStmt *stmt = (MergeStmt *) n; - - stmt->stmt_len = end_location - stmt->stmt_location; - } - else - elog(ERROR, "unexpected node type %d", (int) n->type); -} - static Node * makeColumnRef(char *colname, List *indirection, int location, core_yyscan_t yyscanner) @@ -19167,14 +19093,11 @@ insertSelectOptions(SelectStmt *stmt, errmsg("multiple WITH clauses not allowed"), parser_errposition(exprLocation((Node *) withClause)))); stmt->withClause = withClause; - - /* Update SelectStmt's location to the start of the WITH clause */ - stmt->stmt_location = withClause->location; } } static Node * -makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location) +makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg) { SelectStmt *n = makeNode(SelectStmt); @@ -19182,7 +19105,6 @@ makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg, int location) n->all = all; n->larg = (SelectStmt *) larg; n->rarg = (SelectStmt *) rarg; - n->stmt_location = location; return (Node *) n; } @@ -19300,12 +19222,14 @@ makeNotExpr(Node *expr, int location) } static Node * -makeAArrayExpr(List *elements, int location) +makeAArrayExpr(List *elements, int location, int location_end) { A_ArrayExpr *n = makeNode(A_ArrayExpr); n->elements = elements; n->location = location; + n->list_start = location; + n->list_end = location_end; return (Node *) n; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 1f8e2d54673..d66276801c6 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1223,6 +1223,8 @@ transformAExprIn(ParseState *pstate, A_Expr *a) newa->element_typeid = scalar_type; newa->elements = aexprs; newa->multidims = false; + newa->list_start = a->rexpr_list_start; + newa->list_end = a->rexpr_list_end; newa->location = -1; result = (Node *) make_scalar_array_op(pstate, @@ -2165,6 +2167,8 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a, /* array_collid will be set by parse_collate.c */ newa->element_typeid = element_type; newa->elements = newcoercedelems; + newa->list_start = a->list_start; + newa->list_end = a->list_end; newa->location = a->location; return (Node *) newa; diff --git a/src/backend/replication/logical/logical.c b/src/backend/replication/logical/logical.c index 1d56d0c4ef3..f1eb798f3e9 100644 --- a/src/backend/replication/logical/logical.c +++ b/src/backend/replication/logical/logical.c @@ -29,6 +29,7 @@ #include "postgres.h" #include "access/xact.h" +#include "access/xlog_internal.h" #include "access/xlogutils.h" #include "fmgr.h" #include "miscadmin.h" @@ -41,6 +42,7 @@ #include "storage/proc.h" #include "storage/procarray.h" #include "utils/builtins.h" +#include "utils/injection_point.h" #include "utils/inval.h" #include "utils/memutils.h" @@ -1825,9 +1827,13 @@ LogicalConfirmReceivedLocation(XLogRecPtr lsn) { bool updated_xmin = false; bool updated_restart = false; + XLogRecPtr restart_lsn pg_attribute_unused(); SpinLockAcquire(&MyReplicationSlot->mutex); + /* remember the old restart lsn */ + restart_lsn = MyReplicationSlot->data.restart_lsn; + /* * Prevent moving the confirmed_flush backwards, as this could lead to * data duplication issues caused by replicating already replicated @@ -1881,6 +1887,18 @@ LogicalConfirmReceivedLocation(XLogRecPtr lsn) /* first write new xmin to disk, so we know what's up after a crash */ if (updated_xmin || updated_restart) { +#ifdef USE_INJECTION_POINTS + XLogSegNo seg1, + seg2; + + XLByteToSeg(restart_lsn, seg1, wal_segment_size); + XLByteToSeg(MyReplicationSlot->data.restart_lsn, seg2, wal_segment_size); + + /* trigger injection point, but only if segment changes */ + if (seg1 != seg2) + INJECTION_POINT("logical-replication-slot-advance-segment", NULL); +#endif + ReplicationSlotMarkDirty(); ReplicationSlotSave(); elog(DEBUG1, "updated xmin: %u restart: %u", updated_xmin, updated_restart); diff --git a/src/backend/replication/slot.c b/src/backend/replication/slot.c index 600b87fa9cb..c64f020742f 100644 --- a/src/backend/replication/slot.c +++ b/src/backend/replication/slot.c @@ -424,6 +424,7 @@ ReplicationSlotCreate(const char *name, bool db_specific, slot->candidate_restart_valid = InvalidXLogRecPtr; slot->candidate_restart_lsn = InvalidXLogRecPtr; slot->last_saved_confirmed_flush = InvalidXLogRecPtr; + slot->last_saved_restart_lsn = InvalidXLogRecPtr; slot->inactive_since = 0; /* @@ -1165,20 +1166,41 @@ ReplicationSlotsComputeRequiredLSN(void) { ReplicationSlot *s = &ReplicationSlotCtl->replication_slots[i]; XLogRecPtr restart_lsn; + XLogRecPtr last_saved_restart_lsn; bool invalidated; + ReplicationSlotPersistency persistency; if (!s->in_use) continue; SpinLockAcquire(&s->mutex); + persistency = s->data.persistency; restart_lsn = s->data.restart_lsn; invalidated = s->data.invalidated != RS_INVAL_NONE; + last_saved_restart_lsn = s->last_saved_restart_lsn; SpinLockRelease(&s->mutex); /* invalidated slots need not apply */ if (invalidated) continue; + /* + * For persistent slot use last_saved_restart_lsn to compute the + * oldest LSN for removal of WAL segments. The segments between + * last_saved_restart_lsn and restart_lsn might be needed by a + * persistent slot in the case of database crash. Non-persistent + * slots can't survive the database crash, so we don't care about + * last_saved_restart_lsn for them. + */ + if (persistency == RS_PERSISTENT) + { + if (last_saved_restart_lsn != InvalidXLogRecPtr && + restart_lsn > last_saved_restart_lsn) + { + restart_lsn = last_saved_restart_lsn; + } + } + if (restart_lsn != InvalidXLogRecPtr && (min_required == InvalidXLogRecPtr || restart_lsn < min_required)) @@ -1216,7 +1238,9 @@ ReplicationSlotsComputeLogicalRestartLSN(void) { ReplicationSlot *s; XLogRecPtr restart_lsn; + XLogRecPtr last_saved_restart_lsn; bool invalidated; + ReplicationSlotPersistency persistency; s = &ReplicationSlotCtl->replication_slots[i]; @@ -1230,14 +1254,33 @@ ReplicationSlotsComputeLogicalRestartLSN(void) /* read once, it's ok if it increases while we're checking */ SpinLockAcquire(&s->mutex); + persistency = s->data.persistency; restart_lsn = s->data.restart_lsn; invalidated = s->data.invalidated != RS_INVAL_NONE; + last_saved_restart_lsn = s->last_saved_restart_lsn; SpinLockRelease(&s->mutex); /* invalidated slots need not apply */ if (invalidated) continue; + /* + * For persistent slot use last_saved_restart_lsn to compute the + * oldest LSN for removal of WAL segments. The segments between + * last_saved_restart_lsn and restart_lsn might be needed by a + * persistent slot in the case of database crash. Non-persistent + * slots can't survive the database crash, so we don't care about + * last_saved_restart_lsn for them. + */ + if (persistency == RS_PERSISTENT) + { + if (last_saved_restart_lsn != InvalidXLogRecPtr && + restart_lsn > last_saved_restart_lsn) + { + restart_lsn = last_saved_restart_lsn; + } + } + if (restart_lsn == InvalidXLogRecPtr) continue; @@ -1455,6 +1498,7 @@ ReplicationSlotReserveWal(void) Assert(slot != NULL); Assert(slot->data.restart_lsn == InvalidXLogRecPtr); + Assert(slot->last_saved_restart_lsn == InvalidXLogRecPtr); /* * The replication slot mechanism is used to prevent removal of required @@ -1766,6 +1810,8 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, */ SpinLockAcquire(&s->mutex); + Assert(s->data.restart_lsn >= s->last_saved_restart_lsn); + restart_lsn = s->data.restart_lsn; /* we do nothing if the slot is already invalid */ @@ -1835,7 +1881,10 @@ InvalidatePossiblyObsoleteSlot(uint32 possible_causes, * just rely on .invalidated. */ if (invalidation_cause == RS_INVAL_WAL_REMOVED) + { s->data.restart_lsn = InvalidXLogRecPtr; + s->last_saved_restart_lsn = InvalidXLogRecPtr; + } /* Let caller know */ *invalidated = true; @@ -2079,6 +2128,12 @@ CheckPointReplicationSlots(bool is_shutdown) SaveSlotToPath(s, path, LOG); } LWLockRelease(ReplicationSlotAllocationLock); + + /* + * Recompute the required LSN as SaveSlotToPath() updated + * last_saved_restart_lsn for slots. + */ + ReplicationSlotsComputeRequiredLSN(); } /* @@ -2354,6 +2409,7 @@ SaveSlotToPath(ReplicationSlot *slot, const char *dir, int elevel) if (!slot->just_dirtied) slot->dirty = false; slot->last_saved_confirmed_flush = cp.slotdata.confirmed_flush; + slot->last_saved_restart_lsn = cp.slotdata.restart_lsn; SpinLockRelease(&slot->mutex); LWLockRelease(&slot->io_in_progress_lock); @@ -2569,6 +2625,7 @@ RestoreSlotFromDisk(const char *name) slot->effective_xmin = cp.slotdata.xmin; slot->effective_catalog_xmin = cp.slotdata.catalog_xmin; slot->last_saved_confirmed_flush = cp.slotdata.confirmed_flush; + slot->last_saved_restart_lsn = cp.slotdata.restart_lsn; slot->candidate_catalog_xmin = InvalidTransactionId; slot->candidate_xmin_lsn = InvalidXLogRecPtr; diff --git a/src/backend/storage/aio/method_io_uring.c b/src/backend/storage/aio/method_io_uring.c index cc312b641ca..b78048328e1 100644 --- a/src/backend/storage/aio/method_io_uring.c +++ b/src/backend/storage/aio/method_io_uring.c @@ -400,9 +400,9 @@ pgaio_uring_wait_one(PgAioHandle *ioh, uint64 ref_generation) while (true) { pgaio_debug_io(DEBUG3, ioh, - "wait_one io_gen: %llu, ref_gen: %llu, cycle %d", - (long long unsigned) ioh->generation, - (long long unsigned) ref_generation, + "wait_one io_gen: %" PRIu64 ", ref_gen: %" PRIu64 ", cycle %d", + ioh->generation, + ref_generation, waited); if (pgaio_io_was_recycled(ioh, ref_generation, &state) || diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index f2182e91825..c4b6214d618 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -712,9 +712,9 @@ usage(const char *progname) printf(_(" --use-set-session-authorization\n" " use SET SESSION AUTHORIZATION commands instead of\n" " ALTER OWNER commands to set ownership\n")); - printf(_(" --with-data dump the data\n")); - printf(_(" --with-schema dump the schema\n")); - printf(_(" --with-statistics dump the statistics\n")); + printf(_(" --with-data restore the data\n")); + printf(_(" --with-schema restore the schema\n")); + printf(_(" --with-statistics restore the statistics\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 81a5ba844ba..1f7635d0c23 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -778,6 +778,7 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch) int ssl_in_use, password_used, gssapi_used; + int version_num; char *paramval; if (!active_branch) @@ -793,7 +794,9 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch) /* Get values for the parameters */ host = PQhost(pset.db); hostaddr = PQhostaddr(pset.db); - protocol_version = psprintf("%d", PQprotocolVersion(pset.db)); + version_num = PQfullProtocolVersion(pset.db); + protocol_version = psprintf("%d.%d", version_num / 10000, + version_num % 10000); ssl_in_use = PQsslInUse(pset.db); password_used = PQconnectionUsedPassword(pset.db); gssapi_used = PQconnectionUsedGSSAPI(pset.db); diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 47352b7faed..b53cd8ab698 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -1867,18 +1867,30 @@ ExecQueryAndProcessResults(const char *query, { FILE *copy_stream = NULL; - if (pset.piped_syncs > 1) + if (PQpipelineStatus(pset.db) != PQ_PIPELINE_OFF) { /* - * When reading COPY data, the backend ignores sync messages - * and will not send a matching ReadyForQuery response. Even - * if we adjust piped_syncs and requested_results, it is not - * possible to salvage this as the sync message would still be - * in libpq's command queue and we would be stuck in a busy - * pipeline state. Thus, we abort the connection to avoid - * this state. + * Running COPY within a pipeline can break the protocol + * synchronisation in multiple ways, and psql shows its limits + * when it comes to tracking this information. + * + * While in COPY mode, the backend process ignores additional + * Sync messages and will not send the matching ReadyForQuery + * expected by the frontend. + * + * Additionally, libpq automatically sends a Sync with the + * Copy message, creating an unexpected synchronisation point. + * A failure during COPY would leave the pipeline in an + * aborted state while the backend would be in a clean state, + * ready to process commands. + * + * Improving those issues would require modifications in how + * libpq handles pipelines and COPY. Hence, for the time + * being, we forbid the use of COPY within a pipeline, + * aborting the connection to avoid an inconsistent state on + * psql side if trying to use a COPY command. */ - pg_log_info("\\syncpipeline after COPY is not supported, aborting connection"); + pg_log_info("COPY in a pipeline is not supported, aborting connection"); exit(EXIT_BADCONN); } diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 403b51325a7..ce05b3a5132 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -463,8 +463,9 @@ helpVariables(unsigned short int pager) " VERSION_NAME\n" " VERSION_NUM\n" " psql's version (in verbose string, short string, or numeric format)\n"); - HELP0(" WATCH_INTERVAL\n" - " if set to a number, overrides the default two second \\watch interval\n"); + HELPN(" WATCH_INTERVAL\n" + " number of seconds \\watch waits between executions (default %s)\n", + DEFAULT_WATCH_INTERVAL); HELP0("\nDisplay settings:\n"); HELP0("Usage:\n"); diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl index ae5c1d66405..f42c3961e09 100644 --- a/src/bin/psql/t/001_basic.pl +++ b/src/bin/psql/t/001_basic.pl @@ -483,8 +483,8 @@ psql_like($node, "copy (values ('foo'),('bar')) to stdout \\g | $pipe_cmd", my $c4 = slurp_file($g_file); like($c4, qr/foo.*bar/s); -# Tests with pipelines. These trigger FATAL failures in the backend, -# so they cannot be tested via SQL. +# Test COPY within pipelines. These abort the connection from +# the frontend so they cannot be tested via SQL. $node->safe_psql('postgres', 'CREATE TABLE psql_pipeline()'); my $log_location = -s $node->logfile; psql_fails_like( @@ -493,53 +493,41 @@ psql_fails_like( COPY psql_pipeline FROM STDIN; SELECT 'val1'; \\syncpipeline -\\getresults \\endpipeline}, - qr/server closed the connection unexpectedly/, - 'protocol sync loss in pipeline: direct COPY, SELECT, sync and getresult' -); + qr/COPY in a pipeline is not supported, aborting connection/, + 'COPY FROM in pipeline: fails'); $node->wait_for_log( qr/FATAL: .*terminating connection because protocol synchronization was lost/, $log_location); +# Remove \syncpipeline here. psql_fails_like( $node, qq{\\startpipeline -COPY psql_pipeline FROM STDIN \\bind \\sendpipeline -SELECT 'val1' \\bind \\sendpipeline -\\syncpipeline -\\getresults -\\endpipeline}, - qr/server closed the connection unexpectedly/, - 'protocol sync loss in pipeline: bind COPY, SELECT, sync and getresult'); - -# This time, test without the \getresults and \syncpipeline. -psql_fails_like( - $node, - qq{\\startpipeline -COPY psql_pipeline FROM STDIN; +COPY psql_pipeline TO STDOUT; SELECT 'val1'; \\endpipeline}, - qr/server closed the connection unexpectedly/, - 'protocol sync loss in pipeline: COPY, SELECT and sync'); + qr/COPY in a pipeline is not supported, aborting connection/, + 'COPY TO in pipeline: fails'); -# Tests sending a sync after a COPY TO/FROM. These abort the connection -# from the frontend. psql_fails_like( $node, qq{\\startpipeline -COPY psql_pipeline FROM STDIN; +\\copy psql_pipeline from stdin; +SELECT 'val1'; \\syncpipeline \\endpipeline}, - qr/\\syncpipeline after COPY is not supported, aborting connection/, - 'sending sync after COPY FROM'); + qr/COPY in a pipeline is not supported, aborting connection/, + '\copy from in pipeline: fails'); + +# Sync attempt after a COPY TO/FROM. psql_fails_like( $node, qq{\\startpipeline -COPY psql_pipeline TO STDOUT; +\\copy psql_pipeline to stdout; \\syncpipeline \\endpipeline}, - qr/\\syncpipeline after COPY is not supported, aborting connection/, - 'sending sync after COPY TO'); + qr/COPY in a pipeline is not supported, aborting connection/, + '\copy to in pipeline: fails'); done_testing(); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index f2971485d8f..67fbe9c9292 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202506021 +#define CATALOG_VERSION_NO 202506121 #endif diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index dd00ab420b8..ba12678d1cb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -351,6 +351,14 @@ typedef struct A_Expr List *name; /* possibly-qualified name of operator */ Node *lexpr; /* left argument, or NULL if none */ Node *rexpr; /* right argument, or NULL if none */ + + /* + * If rexpr is a list of some kind, we separately track its starting and + * ending location; it's not the same as the starting and ending location + * of the token itself. + */ + ParseLoc rexpr_list_start; + ParseLoc rexpr_list_end; ParseLoc location; /* token location, or -1 if unknown */ } A_Expr; @@ -506,6 +514,8 @@ typedef struct A_ArrayExpr { NodeTag type; List *elements; /* array element expressions */ + ParseLoc list_start; /* start of the element list */ + ParseLoc list_end; /* end of the elements list */ ParseLoc location; /* token location, or -1 if unknown */ } A_ArrayExpr; @@ -2100,8 +2110,6 @@ typedef struct InsertStmt ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ OverridingKind override; /* OVERRIDING clause */ - ParseLoc stmt_location; /* start location, or -1 if unknown */ - ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ } InsertStmt; /* ---------------------- @@ -2116,8 +2124,6 @@ typedef struct DeleteStmt Node *whereClause; /* qualifications */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ - ParseLoc stmt_location; /* start location, or -1 if unknown */ - ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ } DeleteStmt; /* ---------------------- @@ -2133,8 +2139,6 @@ typedef struct UpdateStmt List *fromClause; /* optional from clause for more tables */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ - ParseLoc stmt_location; /* start location, or -1 if unknown */ - ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ } UpdateStmt; /* ---------------------- @@ -2150,8 +2154,6 @@ typedef struct MergeStmt List *mergeWhenClauses; /* list of MergeWhenClause(es) */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ - ParseLoc stmt_location; /* start location, or -1 if unknown */ - ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ } MergeStmt; /* ---------------------- @@ -2221,8 +2223,6 @@ typedef struct SelectStmt bool all; /* ALL specified? */ struct SelectStmt *larg; /* left child */ struct SelectStmt *rarg; /* right child */ - ParseLoc stmt_location; /* start location, or -1 if unknown */ - ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ /* Eventually add fields for CORRESPONDING spec here */ } SelectStmt; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 7d3b4198f26..01510b01b64 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1397,6 +1397,10 @@ typedef struct ArrayExpr List *elements pg_node_attr(query_jumble_squash); /* true if elements are sub-arrays */ bool multidims pg_node_attr(query_jumble_ignore); + /* location of the start of the elements list */ + ParseLoc list_start; + /* location of the end of the elements list */ + ParseLoc list_end; /* token location, or -1 if unknown */ ParseLoc location; } ArrayExpr; diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 994284019fb..f7d07c84542 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -108,20 +108,6 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param, * byte-wise locations in parse structures to character-wise cursor * positions.) * - * p_stmt_location: location of the top level RawStmt's start. During - * transformation, the Query's location will be set to the statement's - * location if available. Otherwise, the RawStmt's start location will - * be used. Propagating the location through ParseState is needed for - * the Query length calculation (see p_stmt_len below). - * - * p_stmt_len: length of the top level RawStmt. Most of the time, the - * statement's length is not provided by the parser, with the exception - * of SelectStmt within parentheses and PreparableStmt in COPY. If the - * statement's location is provided by the parser, the top-level location - * and length are needed to accurately compute the Query's length. If the - * statement's location is not provided, the RawStmt's length can be used - * directly. - * * p_rtable: list of RTEs that will become the rangetable of the query. * Note that neither relname nor refname of these entries are necessarily * unique; searching the rtable by name is a bad idea. @@ -207,8 +193,6 @@ struct ParseState { ParseState *parentParseState; /* stack link */ const char *p_sourcetext; /* source text, or NULL if not available */ - ParseLoc p_stmt_location; /* start location, or -1 if unknown */ - ParseLoc p_stmt_len; /* length in bytes; 0 means "rest of string" */ List *p_rtable; /* range table so far */ List *p_rteperminfos; /* list of RTEPermissionInfo nodes for each * RTE_RELATION entry in rtable */ diff --git a/src/include/replication/slot.h b/src/include/replication/slot.h index eb0b93b1114..ffacba9d2ae 100644 --- a/src/include/replication/slot.h +++ b/src/include/replication/slot.h @@ -215,6 +215,14 @@ typedef struct ReplicationSlot * recently stopped. */ TimestampTz inactive_since; + + /* + * Latest restart_lsn that has been flushed to disk. For persistent slots + * the flushed LSN should be taken into account when calculating the + * oldest LSN for WAL segments removal. + */ + XLogRecPtr last_saved_restart_lsn; + } ReplicationSlot; #define SlotIsPhysical(slot) ((slot)->data.database == InvalidOid) diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build index cb983766c67..92429d28402 100644 --- a/src/test/recovery/meson.build +++ b/src/test/recovery/meson.build @@ -54,6 +54,8 @@ tests += { 't/043_no_contrecord_switch.pl', 't/044_invalidate_inactive_slots.pl', 't/045_archive_restartpoint.pl', + 't/046_checkpoint_logical_slot.pl', + 't/047_checkpoint_physical_slot.pl' ], }, } diff --git a/src/test/recovery/t/046_checkpoint_logical_slot.pl b/src/test/recovery/t/046_checkpoint_logical_slot.pl new file mode 100644 index 00000000000..b4265c4a6a5 --- /dev/null +++ b/src/test/recovery/t/046_checkpoint_logical_slot.pl @@ -0,0 +1,139 @@ +# Copyright (c) 2025, PostgreSQL Global Development Group +# +# This test verifies the case when the logical slot is advanced during +# checkpoint. The test checks that the logical slot's restart_lsn still refers +# to an existed WAL segment after immediate restart. +# +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; + +use Test::More; + +if ($ENV{enable_injection_points} ne 'yes') +{ + plan skip_all => 'Injection points not supported by this build'; +} + +my ($node, $result); + +$node = PostgreSQL::Test::Cluster->new('mike'); +$node->init; +$node->append_conf('postgresql.conf', + "shared_preload_libraries = 'injection_points'"); +$node->append_conf('postgresql.conf', "wal_level = 'logical'"); +$node->start; +$node->safe_psql('postgres', q(CREATE EXTENSION injection_points)); + +# Create a simple table to generate data into. +$node->safe_psql('postgres', + q{create table t (id serial primary key, b text)}); + +# Create the two slots we'll need. +$node->safe_psql('postgres', + q{select pg_create_logical_replication_slot('slot_logical', 'test_decoding')} +); +$node->safe_psql('postgres', + q{select pg_create_physical_replication_slot('slot_physical', true)}); + +# Advance both slots to the current position just to have everything "valid". +$node->safe_psql('postgres', + q{select count(*) from pg_logical_slot_get_changes('slot_logical', null, null)} +); +$node->safe_psql('postgres', + q{select pg_replication_slot_advance('slot_physical', pg_current_wal_lsn())} +); + +# Run checkpoint to flush current state to disk and set a baseline. +$node->safe_psql('postgres', q{checkpoint}); + +# Generate some transactions to get RUNNING_XACTS. +my $xacts = $node->background_psql('postgres'); +$xacts->query_until( + qr/run_xacts/, + q(\echo run_xacts +SELECT 1 \watch 0.1 +\q +)); + +# Insert 2M rows; that's about 260MB (~20 segments) worth of WAL. +$node->safe_psql('postgres', + q{insert into t (b) select md5(i::text) from generate_series(1,1000000) s(i)} +); + +# Run another checkpoint to set a new restore LSN. +$node->safe_psql('postgres', q{checkpoint}); + +# Another 2M rows; that's about 260MB (~20 segments) worth of WAL. +$node->safe_psql('postgres', + q{insert into t (b) select md5(i::text) from generate_series(1,1000000) s(i)} +); + +# Run another checkpoint, this time in the background, and make it wait +# on the injection point) so that the checkpoint stops right before +# removing old WAL segments. +note('starting checkpoint\n'); + +my $checkpoint = $node->background_psql('postgres'); +$checkpoint->query_safe( + q(select injection_points_attach('checkpoint-before-old-wal-removal','wait')) +); +$checkpoint->query_until( + qr/starting_checkpoint/, + q(\echo starting_checkpoint +checkpoint; +\q +)); + +# Wait until the checkpoint stops right before removing WAL segments. +note('waiting for injection_point\n'); +$node->wait_for_event('checkpointer', 'checkpoint-before-old-wal-removal'); +note('injection_point is reached'); + +# Try to advance the logical slot, but make it stop when it moves to the next +# WAL segment (this has to happen in the background, too). +my $logical = $node->background_psql('postgres'); +$logical->query_safe( + q{select injection_points_attach('logical-replication-slot-advance-segment','wait');} +); +$logical->query_until( + qr/get_changes/, + q( +\echo get_changes +select count(*) from pg_logical_slot_get_changes('slot_logical', null, null) \watch 1 +\q +)); + +# Wait until the slot's restart_lsn points to the next WAL segment. +note('waiting for injection_point\n'); +$node->wait_for_event('client backend', + 'logical-replication-slot-advance-segment'); +note('injection_point is reached'); + +# OK, we're in the right situation: time to advance the physical slot, which +# recalculates the required LSN, and then unblock the checkpoint, which +# removes the WAL still needed by the logical slot. +$node->safe_psql('postgres', + q{select pg_replication_slot_advance('slot_physical', pg_current_wal_lsn())} +); + +# Continue the checkpoint. +$node->safe_psql('postgres', + q{select injection_points_wakeup('checkpoint-before-old-wal-removal')}); + +# Abruptly stop the server (1 second should be enough for the checkpoint +# to finish; it would be better). +$node->stop('immediate'); + +$node->start; + +eval { + $node->safe_psql('postgres', + q{select count(*) from pg_logical_slot_get_changes('slot_logical', null, null);} + ); +}; +is($@, '', "Logical slot still valid"); + +done_testing(); diff --git a/src/test/recovery/t/047_checkpoint_physical_slot.pl b/src/test/recovery/t/047_checkpoint_physical_slot.pl new file mode 100644 index 00000000000..454e56b9bd2 --- /dev/null +++ b/src/test/recovery/t/047_checkpoint_physical_slot.pl @@ -0,0 +1,133 @@ +# Copyright (c) 2025, PostgreSQL Global Development Group +# +# This test verifies the case when the physical slot is advanced during +# checkpoint. The test checks that the physical slot's restart_lsn still refers +# to an existed WAL segment after immediate restart. +# +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; + +use Test::More; + +if ($ENV{enable_injection_points} ne 'yes') +{ + plan skip_all => 'Injection points not supported by this build'; +} + +my ($node, $result); + +$node = PostgreSQL::Test::Cluster->new('mike'); +$node->init; +$node->append_conf('postgresql.conf', + "shared_preload_libraries = 'injection_points'"); +$node->append_conf('postgresql.conf', "wal_level = 'replica'"); +$node->start; +$node->safe_psql('postgres', q(CREATE EXTENSION injection_points)); + +# Create a simple table to generate data into. +$node->safe_psql('postgres', + q{create table t (id serial primary key, b text)}); + +# Create a physical replication slot. +$node->safe_psql('postgres', + q{select pg_create_physical_replication_slot('slot_physical', true)}); + +# Advance slot to the current position, just to have everything "valid". +$node->safe_psql('postgres', + q{select pg_replication_slot_advance('slot_physical', pg_current_wal_lsn())} +); + +# Run checkpoint to flush current state to disk and set a baseline. +$node->safe_psql('postgres', q{checkpoint}); + +# Insert 2M rows; that's about 260MB (~20 segments) worth of WAL. +$node->safe_psql('postgres', + q{insert into t (b) select md5(i::text) from generate_series(1,100000) s(i)} +); + +# Advance slot to the current position, just to have everything "valid". +$node->safe_psql('postgres', + q{select pg_replication_slot_advance('slot_physical', pg_current_wal_lsn())} +); + +# Run another checkpoint to set a new restore LSN. +$node->safe_psql('postgres', q{checkpoint}); + +# Another 2M rows; that's about 260MB (~20 segments) worth of WAL. +$node->safe_psql('postgres', + q{insert into t (b) select md5(i::text) from generate_series(1,1000000) s(i)} +); + +my $restart_lsn_init = $node->safe_psql('postgres', + q{select restart_lsn from pg_replication_slots where slot_name = 'slot_physical'} +); +chomp($restart_lsn_init); +note("restart lsn before checkpoint: $restart_lsn_init"); + +# Run another checkpoint, this time in the background, and make it wait +# on the injection point) so that the checkpoint stops right before +# removing old WAL segments. +note('starting checkpoint'); + +my $checkpoint = $node->background_psql('postgres'); +$checkpoint->query_safe( + q{select injection_points_attach('checkpoint-before-old-wal-removal','wait')} +); +$checkpoint->query_until( + qr/starting_checkpoint/, + q(\echo starting_checkpoint +checkpoint; +\q +)); + +# Wait until the checkpoint stops right before removing WAL segments. +note('waiting for injection_point'); +$node->wait_for_event('checkpointer', 'checkpoint-before-old-wal-removal'); +note('injection_point is reached'); + +# OK, we're in the right situation: time to advance the physical slot, which +# recalculates the required LSN and then unblock the checkpoint, which +# removes the WAL still needed by the physical slot. +$node->safe_psql('postgres', + q{select pg_replication_slot_advance('slot_physical', pg_current_wal_lsn())} +); + +# Continue the checkpoint. +$node->safe_psql('postgres', + q{select injection_points_wakeup('checkpoint-before-old-wal-removal')}); + +my $restart_lsn_old = $node->safe_psql('postgres', + q{select restart_lsn from pg_replication_slots where slot_name = 'slot_physical'} +); +chomp($restart_lsn_old); +note("restart lsn before stop: $restart_lsn_old"); + +# Abruptly stop the server (1 second should be enough for the checkpoint +# to finish; it would be better). +$node->stop('immediate'); + +$node->start; + +# Get the restart_lsn of the slot right after restarting. +my $restart_lsn = $node->safe_psql('postgres', + q{select restart_lsn from pg_replication_slots where slot_name = 'slot_physical'} +); +chomp($restart_lsn); +note("restart lsn: $restart_lsn"); + +# Get the WAL segment name for the slot's restart_lsn. +my $restart_lsn_segment = $node->safe_psql('postgres', + "SELECT pg_walfile_name('$restart_lsn'::pg_lsn)"); +chomp($restart_lsn_segment); + +# Check if the required wal segment exists. +note("required by slot segment name: $restart_lsn_segment"); +my $datadir = $node->data_dir; +ok( -f "$datadir/pg_wal/$restart_lsn_segment", + "WAL segment $restart_lsn_segment for physical slot's restart_lsn $restart_lsn exists" +); + +done_testing(); diff --git a/src/test/regress/expected/psql_pipeline.out b/src/test/regress/expected/psql_pipeline.out index a30dec088b9..e78e6bfa0ad 100644 --- a/src/test/regress/expected/psql_pipeline.out +++ b/src/test/regress/expected/psql_pipeline.out @@ -228,192 +228,6 @@ BEGIN \bind \sendpipeline INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline COMMIT \bind \sendpipeline \endpipeline --- COPY FROM STDIN --- with \sendpipeline and \bind -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\endpipeline - ?column? ----------- - val1 -(1 row) - --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\endpipeline - ?column? ----------- - val1 -(1 row) - --- COPY FROM STDIN with \flushrequest + \getresults --- with \sendpipeline and \bind -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\flushrequest -\getresults - ?column? ----------- - val1 -(1 row) - -message type 0x5a arrived from server while idle -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\flushrequest -\getresults - ?column? ----------- - val1 -(1 row) - -message type 0x5a arrived from server while idle -\endpipeline --- COPY FROM STDIN with \syncpipeline + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\syncpipeline -\getresults - ?column? ----------- - val1 -(1 row) - -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\syncpipeline -\getresults - ?column? ----------- - val1 -(1 row) - -\endpipeline --- COPY TO STDOUT --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\endpipeline - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\endpipeline - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 --- COPY TO STDOUT with \flushrequest + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\flushrequest -\getresults - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\flushrequest -\getresults - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 -\endpipeline --- COPY TO STDOUT with \syncpipeline + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\syncpipeline -\getresults - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\syncpipeline -\getresults - ?column? ----------- - val1 -(1 row) - -1 \N -2 test2 -20 test2 -3 test3 -30 test3 -4 test4 -40 test4 -\endpipeline -- Use \parse and \bind_named \startpipeline SELECT $1 \parse '' @@ -740,7 +554,7 @@ SELECT COUNT(*) FROM psql_pipeline \bind \sendpipeline count ------- - 7 + 1 (1 row) -- After an error, pipeline is aborted and requires \syncpipeline to be diff --git a/src/test/regress/sql/psql_pipeline.sql b/src/test/regress/sql/psql_pipeline.sql index 16e1e1e84cd..5945eca1ef7 100644 --- a/src/test/regress/sql/psql_pipeline.sql +++ b/src/test/regress/sql/psql_pipeline.sql @@ -105,106 +105,6 @@ INSERT INTO psql_pipeline VALUES ($1) \bind 1 \sendpipeline COMMIT \bind \sendpipeline \endpipeline --- COPY FROM STDIN --- with \sendpipeline and \bind -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\endpipeline -2 test2 -\. --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\endpipeline -20 test2 -\. - --- COPY FROM STDIN with \flushrequest + \getresults --- with \sendpipeline and \bind -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\flushrequest -\getresults -3 test3 -\. -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\flushrequest -\getresults -30 test3 -\. -\endpipeline - --- COPY FROM STDIN with \syncpipeline + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -COPY psql_pipeline FROM STDIN \bind \sendpipeline -\syncpipeline -\getresults -4 test4 -\. -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -COPY psql_pipeline FROM STDIN; -\syncpipeline -\getresults -40 test4 -\. -\endpipeline - --- COPY TO STDOUT --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\endpipeline - --- COPY TO STDOUT with \flushrequest + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\flushrequest -\getresults -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\flushrequest -\getresults -\endpipeline - --- COPY TO STDOUT with \syncpipeline + \getresults --- with \bind and \sendpipeline -\startpipeline -SELECT $1 \bind 'val1' \sendpipeline -copy psql_pipeline TO STDOUT \bind \sendpipeline -\syncpipeline -\getresults -\endpipeline --- with semicolon -\startpipeline -SELECT 'val1'; -copy psql_pipeline TO STDOUT; -\syncpipeline -\getresults -\endpipeline - -- Use \parse and \bind_named \startpipeline SELECT $1 \parse '' |