Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit31e5b50

Browse files
committed
postgres_fdw: be more wary about shippability of reg* constants.
Don't consider a constant of regconfig or other reg* types to beshippable unless it refers to a built-in object, or an object inan extension that's been marked shippable. Without thisrestriction, we're too likely to send a constant that will failto parse on the remote server.For the regconfig type only, consider OIDs up to 16383 to be"built in", rather than the normal cutoff of 9999. Otherwisethe initdb-created text search configurations will be consideredunshippable, which is unlikely to make anyone happy.It's possible that this new restriction will de-optimize queriesthat were working satisfactorily before. Users can restore anylost performance by making sure that objects that can be expectedto exist on the remote side are in shippable extensions. However,that's not a change that people are likely to be happy about havingto make after a minor-release update. Between that considerationand the lack of field complaints, let's just change this in HEAD.Noted while fixing bug #17483, although this is not preciselythe problem that that report complained about.Discussion:https://postgr.es/m/1423433.1652722406@sss.pgh.pa.us
1 parentf49a9fc commit31e5b50

File tree

3 files changed

+107
-2
lines changed

3 files changed

+107
-2
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,11 +37,14 @@
3737
#include"access/sysattr.h"
3838
#include"access/table.h"
3939
#include"catalog/pg_aggregate.h"
40+
#include"catalog/pg_authid.h"
4041
#include"catalog/pg_collation.h"
4142
#include"catalog/pg_namespace.h"
4243
#include"catalog/pg_operator.h"
4344
#include"catalog/pg_opfamily.h"
4445
#include"catalog/pg_proc.h"
46+
#include"catalog/pg_ts_config.h"
47+
#include"catalog/pg_ts_dict.h"
4548
#include"catalog/pg_type.h"
4649
#include"commands/defrem.h"
4750
#include"nodes/makefuncs.h"
@@ -384,6 +387,75 @@ foreign_expr_walker(Node *node,
384387
{
385388
Const*c= (Const*)node;
386389

390+
/*
391+
* Constants of regproc and related types can't be shipped
392+
* unless the referenced object is shippable. But NULL's ok.
393+
* (See also the related code in dependency.c.)
394+
*/
395+
if (!c->constisnull)
396+
{
397+
switch (c->consttype)
398+
{
399+
caseREGPROCOID:
400+
caseREGPROCEDUREOID:
401+
if (!is_shippable(DatumGetObjectId(c->constvalue),
402+
ProcedureRelationId,fpinfo))
403+
return false;
404+
break;
405+
caseREGOPEROID:
406+
caseREGOPERATOROID:
407+
if (!is_shippable(DatumGetObjectId(c->constvalue),
408+
OperatorRelationId,fpinfo))
409+
return false;
410+
break;
411+
caseREGCLASSOID:
412+
if (!is_shippable(DatumGetObjectId(c->constvalue),
413+
RelationRelationId,fpinfo))
414+
return false;
415+
break;
416+
caseREGTYPEOID:
417+
if (!is_shippable(DatumGetObjectId(c->constvalue),
418+
TypeRelationId,fpinfo))
419+
return false;
420+
break;
421+
caseREGCOLLATIONOID:
422+
if (!is_shippable(DatumGetObjectId(c->constvalue),
423+
CollationRelationId,fpinfo))
424+
return false;
425+
break;
426+
caseREGCONFIGOID:
427+
428+
/*
429+
* For text search objects only, we weaken the
430+
* normal shippability criterion to allow all OIDs
431+
* below FirstNormalObjectId. Without this, none
432+
* of the initdb-installed TS configurations would
433+
* be shippable, which would be quite annoying.
434+
*/
435+
if (DatumGetObjectId(c->constvalue) >=FirstNormalObjectId&&
436+
!is_shippable(DatumGetObjectId(c->constvalue),
437+
TSConfigRelationId,fpinfo))
438+
return false;
439+
break;
440+
caseREGDICTIONARYOID:
441+
if (DatumGetObjectId(c->constvalue) >=FirstNormalObjectId&&
442+
!is_shippable(DatumGetObjectId(c->constvalue),
443+
TSDictionaryRelationId,fpinfo))
444+
return false;
445+
break;
446+
caseREGNAMESPACEOID:
447+
if (!is_shippable(DatumGetObjectId(c->constvalue),
448+
NamespaceRelationId,fpinfo))
449+
return false;
450+
break;
451+
caseREGROLEOID:
452+
if (!is_shippable(DatumGetObjectId(c->constvalue),
453+
AuthIdRelationId,fpinfo))
454+
return false;
455+
break;
456+
}
457+
}
458+
387459
/*
388460
* If the constant has nondefault collation, either it's of a
389461
* non-builtin type, or it reflects folding of a CollateExpr.

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1163,11 +1163,34 @@ SELECT * FROM ft1 WHERE CASE c3 COLLATE "C" WHEN c6 THEN true ELSE c3 < 'bar' EN
11631163
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
11641164
(4 rows)
11651165

1166-
-- check schema-qualification of regconfig constant
1166+
-- a regconfig constant referring to this text search configuration
1167+
-- is initially unshippable
11671168
CREATE TEXT SEARCH CONFIGURATION public.custom_search
11681169
(COPY = pg_catalog.english);
11691170
EXPLAIN (VERBOSE, COSTS OFF)
11701171
SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1
1172+
WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
1173+
QUERY PLAN
1174+
-------------------------------------------------------------------------
1175+
Foreign Scan on public.ft1
1176+
Output: c1, to_tsvector('custom_search'::regconfig, c3)
1177+
Filter: (length(to_tsvector('custom_search'::regconfig, ft1.c3)) > 0)
1178+
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1" WHERE (("C 1" = 642))
1179+
(4 rows)
1180+
1181+
SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1
1182+
WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
1183+
c1 | to_tsvector
1184+
-----+-------------
1185+
642 | '00642':1
1186+
(1 row)
1187+
1188+
-- but if it's in a shippable extension, it can be shipped
1189+
ALTER EXTENSION postgres_fdw ADD TEXT SEARCH CONFIGURATION public.custom_search;
1190+
-- however, that doesn't flush the shippability cache, so do a quick reconnect
1191+
\c -
1192+
EXPLAIN (VERBOSE, COSTS OFF)
1193+
SELECT c1, to_tsvector('custom_search'::regconfig, c3) FROM ft1
11711194
WHERE c1 = 642 AND length(to_tsvector('custom_search'::regconfig, c3)) > 0;
11721195
QUERY PLAN
11731196
----------------------------------------------------------------------------------------------------------------------------------------------

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -442,14 +442,24 @@ SELECT * FROM ft1 WHERE CASE c3 WHEN c6 THEN true ELSE c3 < 'bar' END;
442442
EXPLAIN (VERBOSE, COSTS OFF)
443443
SELECT*FROM ft1WHERE CASE c3 COLLATE"C" WHEN c6 THEN true ELSE c3<'bar' END;
444444

445-
-- check schema-qualification of regconfig constant
445+
-- a regconfig constant referring to this text search configuration
446+
-- is initially unshippable
446447
CREATETEXT SEARCH CONFIGURATIONpublic.custom_search
447448
(COPY=pg_catalog.english);
448449
EXPLAIN (VERBOSE, COSTS OFF)
449450
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
450451
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
451452
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
452453
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
454+
-- but if it's in a shippable extension, it can be shipped
455+
ALTER EXTENSION postgres_fdw ADDTEXT SEARCH CONFIGURATIONpublic.custom_search;
456+
-- however, that doesn't flush the shippability cache, so do a quick reconnect
457+
\c-
458+
EXPLAIN (VERBOSE, COSTS OFF)
459+
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
460+
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
461+
SELECT c1, to_tsvector('custom_search'::regconfig, c3)FROM ft1
462+
WHERE c1=642AND length(to_tsvector('custom_search'::regconfig, c3))>0;
453463

454464
-- ===================================================================
455465
-- JOIN queries

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp