Hi hackers,
Was trying to understand that negated operators thread and found what I
think is a bug in ATTACH PARTITION constraint validation.
Reproducer (master at 6a2edf2a1e6, also verified on 11-19):
CREATE TABLE lp (a int) PARTITION BY LIST (a);
CREATE TABLE
CREATE TABLE lp_def PARTITION OF lp DEFAULT;
CREATE TABLE
INSERT INTO lp VALUES (1);
INSERT 0 1
CREATE TABLE lp_1 (a int);
CREATE TABLE
-- This is correctly rejected
ALTER TABLE lp ATTACH PARTITION lp_1 FOR VALUES IN (1);
psql:test3.sql:6: ERROR: updated partition constraint for default
partition "lp_def" would be violated by some row
-- This should also be rejected
ALTER TABLE lp ATTACH PARTITION lp_1 FOR VALUES IN (1, NULL);
ALTER TABLE
SELECT tableoid::regclass, a FROM lp;
tableoid | a
----------+---
lp_def | 1
(1 row)
-- a=1 shows up
SELECT * FROM lp;
a
---
1
(1 row)
-- a=1 missing
SELECT * FROM lp WHERE a = 1;
a
---
(0 rows)
SELECT pg_get_partition_constraintdef('lp_def'::regclass);
pg_get_partition_constraintdef
--------------------------------
(NOT ((a IS NULL) OR (a = 1)))
(1 row)
QueuePartitionConstraintValidation() in tablecmds.c stores only the first
element of the implicit-AND list it is handed. ATExecAttachPartition()
works around that for the partition being attached by wrapping its
constraint in a one-element list, but it passes the default partition's
proposed constraint through as is.
When the new bound is a LIST bound that includes NULL, the negation of
the bound has two conjuncts, so only the IS NOT NULL test is ever
checked. The ATTACH then succeeds with rows left in the default
partition that violate its updated constraint. Pruning trusts that
constraint, so any query whose qual lets it exclude the default
partition skips those rows.
The original workaround carries an "XXX this sure looks wrong"
comment, which turns out to have been prescient.
The attached patch stores make_ands_explicit() of the list instead and
drops the now unnecessary wrapper. Also includes a regression test.
Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/
