mirror of
https://github.com/postgres/postgres.git
synced 2026-10-23 13:29:09 +03:00
63920eb099
Up until now, pg_plan_advice has had a feature that allows an advice target to mention a partition name but omit the partition schema; that is, something like SEQ_SCAN(foo/bar) forces a sequential scan on every child of table foo whose partition name is bar, regardless of the schema in which bar appears. However, that feature turns out to have a nasty design flaw: while advice enforcement handles this case just fine, the advice feedback code doesn't know about it and will mark such advice as "matched, failed" even when everything worked perfectly. Unfortunately, there seems to be no simple code fix for this problem. Since the release of PostgreSQL 19 is imminent, take the conservative course and revert this feature of pg_plan_advice. In other words, require the partition schema whenever the partition name is present. You must now write e.g. SEQ_SCAN(foo/public.bar) rather than just SEQ_SCAN(foo/bar). This doesn't affect any cases where automatically generated advice is supplied, since generated advice has always included the partition schema anyway. Manually written advice will have to conform to the new, stricter rule. For a later release, we can consider whether to again relax this restriction in some way, but now is not the time to design new things. Reported-by: Noah Misch <noah@leadboat.com> Backpatch-through: 19 Discussion: https://postgr.es/m/CA+TgmoYmXy-jiP5qDhqNEiYFEBzQsArO6O2d9E8szNZqi1bePQ@mail.gmail.com
262 lines
10 KiB
Plaintext
262 lines
10 KiB
Plaintext
LOAD 'pg_plan_advice';
|
|
-- An empty string is allowed. Empty target lists are allowed for most advice
|
|
-- tags, but not for JOIN_ORDER. "Supplied Plan Advice" should be omitted in
|
|
-- text format when there is no actual advice, but not in non-text format.
|
|
SET pg_plan_advice.advice = '';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------
|
|
Result
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN()';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------
|
|
Result
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.advice = 'NESTED_LOOP_PLAIN()';
|
|
EXPLAIN (COSTS OFF, FORMAT JSON) SELECT 1;
|
|
QUERY PLAN
|
|
--------------------------------
|
|
[ +
|
|
{ +
|
|
"Plan": { +
|
|
"Node Type": "Result", +
|
|
"Parallel Aware": false,+
|
|
"Async Capable": false, +
|
|
"Disabled": false +
|
|
}, +
|
|
"Supplied Plan Advice": ""+
|
|
} +
|
|
]
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER()';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "JOIN_ORDER()"
|
|
DETAIL: Could not parse advice: JOIN_ORDER must have at least one target at or near ")"
|
|
-- Test assorted variations in capitalization, whitespace, and which parts of
|
|
-- the relation identifier are included. These should all work.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
---------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'seq_scan(x@y)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
-----------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x@y) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_scan(x#2)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
-----------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x#2) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#1_0)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x#10) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = ' SEQ_SCAN ( x / y . z ) ';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
-------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x/y.z) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("x"#2/"y"."z"@"t")';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
-----------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(x#2/y.z@t) /* not matched */
|
|
(3 rows)
|
|
|
|
-- Syntax errors.
|
|
SET pg_plan_advice.advice = 'SEQUENTIAL_SCAN(x)';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQUENTIAL_SCAN(x)"
|
|
DETAIL: Could not parse advice: syntax error at or near "SEQUENTIAL_SCAN"
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN"
|
|
DETAIL: Could not parse advice: syntax error at end of input
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN("
|
|
DETAIL: Could not parse advice: syntax error at end of input
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN(""
|
|
DETAIL: Could not parse advice: unterminated quoted identifier at end of input
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("")';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN("")"
|
|
DETAIL: Could not parse advice: zero-length delimited identifier at or near """
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("a"';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN("a""
|
|
DETAIL: Could not parse advice: syntax error at end of input
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(#';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN(#"
|
|
DETAIL: Could not parse advice: syntax error at or near "#"
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#1_0_)';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN(x#1_0_)"
|
|
DETAIL: Could not parse advice: trailing junk after numeric literal at or near "1_0_"
|
|
SET pg_plan_advice.advice = '()';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "()"
|
|
DETAIL: Could not parse advice: syntax error at or near "("
|
|
SET pg_plan_advice.advice = '123';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "123"
|
|
DETAIL: Could not parse advice: syntax error at or near "123"
|
|
-- Out of range values.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#99999999999_99)';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN(x#99999999999_99)"
|
|
DETAIL: Could not parse advice: integer out of range at or near "99999999999_99"
|
|
-- Partition schema is missing.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x/y)';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN(x/y)"
|
|
DETAIL: Could not parse advice: syntax error at or near ")"
|
|
-- Tags like SEQ_SCAN and NO_GATHER don't allow sublists at all; other tags,
|
|
-- except for JOIN_ORDER, allow at most one level of sublist. Hence, these
|
|
-- examples should error out.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN((x))';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "SEQ_SCAN((x))"
|
|
DETAIL: Could not parse advice: syntax error at or near "("
|
|
SET pg_plan_advice.advice = 'DO_NOT_SCAN((x))';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "DO_NOT_SCAN((x))"
|
|
DETAIL: Could not parse advice: syntax error at or near "("
|
|
SET pg_plan_advice.advice = 'GATHER(((x)))';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "GATHER(((x)))"
|
|
DETAIL: Could not parse advice: syntax error at or near "("
|
|
-- Legal comments.
|
|
SET pg_plan_advice.advice = '/**/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------
|
|
Result
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.advice = 'HASH_JOIN(_)/***/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
----------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
HASH_JOIN(_) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = '/* comment */ HASH_JOIN(/*x*/y)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
----------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
HASH_JOIN(y) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = '/* comment */ HASH_JOIN(y//*x*/z.zz)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
---------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
HASH_JOIN(y/z.zz) /* not matched */
|
|
(3 rows)
|
|
|
|
-- Unterminated comments.
|
|
SET pg_plan_advice.advice = '/*';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "/*"
|
|
DETAIL: Could not parse advice: unterminated comment at end of input
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER("fOO") /* oops';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "JOIN_ORDER("fOO") /* oops"
|
|
DETAIL: Could not parse advice: unterminated comment at end of input
|
|
-- Nested comments are not supported, so the first of these is legal and
|
|
-- the second is not.
|
|
SET pg_plan_advice.advice = '/*/*/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------
|
|
Result
|
|
(1 row)
|
|
|
|
SET pg_plan_advice.advice = '/*/* stuff */*/';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "/*/* stuff */*/"
|
|
DETAIL: Could not parse advice: syntax error at or near "*"
|
|
-- Foreign join requires multiple relation identifiers.
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN(a)';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "FOREIGN_JOIN(a)"
|
|
DETAIL: Could not parse advice: FOREIGN_JOIN targets must contain more than one relation identifier at or near ")"
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN((a))';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "FOREIGN_JOIN((a))"
|
|
DETAIL: Could not parse advice: FOREIGN_JOIN targets must contain more than one relation identifier at or near ")"
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN(())';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "FOREIGN_JOIN(())"
|
|
DETAIL: Could not parse advice: FOREIGN_JOIN targets must contain more than one relation identifier at or near ")"
|
|
-- Join order requires at least one relation identifier.
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER(())';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "JOIN_ORDER(())"
|
|
DETAIL: Could not parse advice: JOIN_ORDER targets must contain at least one relation identifier at or near ")"
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER(a ({}))';
|
|
ERROR: invalid value for parameter "pg_plan_advice.advice": "JOIN_ORDER(a ({}))"
|
|
DETAIL: Could not parse advice: JOIN_ORDER targets must contain at least one relation identifier at or near "}"
|
|
-- Tag keywords used as alias names work fine, because the 'identifier'
|
|
-- nonterminal accepts all token types.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(hash_join)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
-----------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(hash_join) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(seq_scan)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
----------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(seq_scan) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(gather)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
--------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(gather) /* not matched */
|
|
(3 rows)
|
|
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(join_order)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
QUERY PLAN
|
|
------------------------------------------
|
|
Result
|
|
Supplied Plan Advice:
|
|
SEQ_SCAN(join_order) /* not matched */
|
|
(3 rows)
|
|
|
|
-- Tag keywords used as partition names or plan names should also work,
|
|
-- since pgpa_identifier_string() can generate these from real partition
|
|
-- and subquery names.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t/public.hash_join)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t/hash_join.foo)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t@hash_join)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t@seq_scan)';
|