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

Commitfc2ac1f

Browse files
committed
Allow CHECK constraints to be placed on foreign tables.
As with NOT NULL constraints, we consider that such constraints are merelyreports of constraints that are being enforced by the remote server (orother underlying storage mechanism). Their only real use is to allowplanner optimizations, for example in constraint-exclusion checks. Thus,the code changes here amount to little more than removal of the error thatwas formerly thrown for applying CHECK to a foreign table.(In passing, do a bit of cleanup of the ALTER FOREIGN TABLE reference page,which had accumulated some weird decisions about ordering etc.)Shigeru Hanada and Etsuro Fujita, reviewed by Kyotaro Horiguchi andAshutosh Bapat.
1 parentce01548 commitfc2ac1f

File tree

11 files changed

+432
-62
lines changed

11 files changed

+432
-62
lines changed

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -62,7 +62,7 @@ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
6262
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
6363

6464
CREATE FOREIGN TABLE agg_text (
65-
aint2,
65+
aint2 CHECK (a >= 0),
6666
bfloat4
6767
) SERVER file_server
6868
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter '', null '\N');
@@ -72,11 +72,13 @@ CREATE FOREIGN TABLE agg_csv (
7272
bfloat4
7373
) SERVER file_server
7474
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
75+
ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
7576
CREATE FOREIGN TABLE agg_bad (
7677
aint2,
7778
bfloat4
7879
) SERVER file_server
7980
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
81+
ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
8082

8183
-- per-column options tests
8284
CREATE FOREIGN TABLE text_csv (
@@ -134,6 +136,18 @@ DELETE FROM agg_csv WHERE a = 100;
134136
-- but this should be ignored
135137
SELECT * FROM agg_csv FOR UPDATE;
136138

139+
-- constraint exclusion tests
140+
\t on
141+
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
142+
\t off
143+
SELECT * FROM agg_csv WHERE a < 0;
144+
SET constraint_exclusion = 'on';
145+
\t on
146+
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
147+
\t off
148+
SELECT * FROM agg_csv WHERE a < 0;
149+
RESET constraint_exclusion;
150+
137151
-- privilege tests
138152
SET ROLE file_fdw_superuser;
139153
SELECT * FROM agg_text ORDER BY a;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -78,7 +78,7 @@ ERROR: COPY null representation cannot use newline or carriage return
7878
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
7979
ERROR: filename is required for file_fdw foreign tables
8080
CREATE FOREIGN TABLE agg_text (
81-
aint2,
81+
aint2 CHECK (a >= 0),
8282
bfloat4
8383
) SERVER file_server
8484
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter '', null '\N');
@@ -88,11 +88,13 @@ CREATE FOREIGN TABLE agg_csv (
8888
bfloat4
8989
) SERVER file_server
9090
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
91+
ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
9192
CREATE FOREIGN TABLE agg_bad (
9293
aint2,
9394
bfloat4
9495
) SERVER file_server
9596
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
97+
ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
9698
-- per-column options tests
9799
CREATE FOREIGN TABLE text_csv (
98100
word1 text OPTIONS (force_not_null 'true'),
@@ -219,6 +221,34 @@ SELECT * FROM agg_csv FOR UPDATE;
219221
42 | 324.78
220222
(3 rows)
221223

224+
-- constraint exclusion tests
225+
\t on
226+
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
227+
Foreign Scan on public.agg_csv
228+
Output: a, b
229+
Filter: (agg_csv.a < 0)
230+
Foreign File: @abs_srcdir@/data/agg.csv
231+
232+
\t off
233+
SELECT * FROM agg_csv WHERE a < 0;
234+
a | b
235+
---+---
236+
(0 rows)
237+
238+
SET constraint_exclusion = 'on';
239+
\t on
240+
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
241+
Result
242+
Output: a, b
243+
One-Time Filter: false
244+
245+
\t off
246+
SELECT * FROM agg_csv WHERE a < 0;
247+
a | b
248+
---+---
249+
(0 rows)
250+
251+
RESET constraint_exclusion;
222252
-- privilege tests
223253
SET ROLE file_fdw_superuser;
224254
SELECT * FROM agg_text ORDER BY a;

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2588,6 +2588,91 @@ select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
25882588
407 | 100
25892589
(13 rows)
25902590

2591+
-- ===================================================================
2592+
-- test check constraints
2593+
-- ===================================================================
2594+
-- Consistent check constraints provide consistent results
2595+
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
2596+
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
2597+
QUERY PLAN
2598+
-------------------------------------------------------------------
2599+
Aggregate
2600+
Output: count(*)
2601+
-> Foreign Scan on public.ft1
2602+
Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 < 0))
2603+
(4 rows)
2604+
2605+
SELECT count(*) FROM ft1 WHERE c2 < 0;
2606+
count
2607+
-------
2608+
0
2609+
(1 row)
2610+
2611+
SET constraint_exclusion = 'on';
2612+
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
2613+
QUERY PLAN
2614+
--------------------------------
2615+
Aggregate
2616+
Output: count(*)
2617+
-> Result
2618+
One-Time Filter: false
2619+
(4 rows)
2620+
2621+
SELECT count(*) FROM ft1 WHERE c2 < 0;
2622+
count
2623+
-------
2624+
0
2625+
(1 row)
2626+
2627+
RESET constraint_exclusion;
2628+
-- check constraint is enforced on the remote side, not locally
2629+
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
2630+
ERROR: new row for relation "T 1" violates check constraint "c2positive"
2631+
DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null).
2632+
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
2633+
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
2634+
ERROR: new row for relation "T 1" violates check constraint "c2positive"
2635+
DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo).
2636+
CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
2637+
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
2638+
-- But inconsistent check constraints provide inconsistent results
2639+
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
2640+
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
2641+
QUERY PLAN
2642+
--------------------------------------------------------------------
2643+
Aggregate
2644+
Output: count(*)
2645+
-> Foreign Scan on public.ft1
2646+
Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 >= 0))
2647+
(4 rows)
2648+
2649+
SELECT count(*) FROM ft1 WHERE c2 >= 0;
2650+
count
2651+
-------
2652+
821
2653+
(1 row)
2654+
2655+
SET constraint_exclusion = 'on';
2656+
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
2657+
QUERY PLAN
2658+
--------------------------------
2659+
Aggregate
2660+
Output: count(*)
2661+
-> Result
2662+
One-Time Filter: false
2663+
(4 rows)
2664+
2665+
SELECT count(*) FROM ft1 WHERE c2 >= 0;
2666+
count
2667+
-------
2668+
0
2669+
(1 row)
2670+
2671+
RESET constraint_exclusion;
2672+
-- local check constraint is not actually enforced
2673+
INSERT INTO ft1(c1, c2) VALUES(1111, 2);
2674+
UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
2675+
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
25912676
-- ===================================================================
25922677
-- test serial columns (ie, sequence-based defaults)
25932678
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -405,6 +405,36 @@ commit;
405405
select c2,count(*)from ft2where c2<500group by1order by1;
406406
select c2,count(*)from"S 1"."T 1"where c2<500group by1order by1;
407407

408+
-- ===================================================================
409+
-- test check constraints
410+
-- ===================================================================
411+
412+
-- Consistent check constraints provide consistent results
413+
ALTER FOREIGN TABLE ft1 ADDCONSTRAINT ft1_c2positiveCHECK (c2>=0);
414+
EXPLAIN (VERBOSE, COSTS false)SELECTcount(*)FROM ft1WHERE c2<0;
415+
SELECTcount(*)FROM ft1WHERE c2<0;
416+
SET constraint_exclusion='on';
417+
EXPLAIN (VERBOSE, COSTS false)SELECTcount(*)FROM ft1WHERE c2<0;
418+
SELECTcount(*)FROM ft1WHERE c2<0;
419+
RESET constraint_exclusion;
420+
-- check constraint is enforced on the remote side, not locally
421+
INSERT INTO ft1(c1, c2)VALUES(1111,-2);-- c2positive
422+
UPDATE ft1SET c2=-c2WHERE c1=1;-- c2positive
423+
ALTER FOREIGN TABLE ft1 DROPCONSTRAINT ft1_c2positive;
424+
425+
-- But inconsistent check constraints provide inconsistent results
426+
ALTER FOREIGN TABLE ft1 ADDCONSTRAINT ft1_c2negativeCHECK (c2<0);
427+
EXPLAIN (VERBOSE, COSTS false)SELECTcount(*)FROM ft1WHERE c2>=0;
428+
SELECTcount(*)FROM ft1WHERE c2>=0;
429+
SET constraint_exclusion='on';
430+
EXPLAIN (VERBOSE, COSTS false)SELECTcount(*)FROM ft1WHERE c2>=0;
431+
SELECTcount(*)FROM ft1WHERE c2>=0;
432+
RESET constraint_exclusion;
433+
-- local check constraint is not actually enforced
434+
INSERT INTO ft1(c1, c2)VALUES(1111,2);
435+
UPDATE ft1SET c2= c2+1WHERE c1=1;
436+
ALTER FOREIGN TABLE ft1 DROPCONSTRAINT ft1_c2negative;
437+
408438
-- ===================================================================
409439
-- test serial columns (ie, sequence-based defaults)
410440
-- ===================================================================

0 commit comments

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