pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15873: Attaching a partition fails because it sees deleted columns
6+ messages / 4 participants
[nested] [flat]
* BUG #15873: Attaching a partition fails because it sees deleted columns
@ 2019-06-25 21:30 PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2019-06-25 21:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dwilches@gmail.com
The following bug has been logged on the website:
Bug reference: 15873
Logged by: DWilches
Email address: dwilches@gmail.com
PostgreSQL version: 11.2
Operating system: Red Hat 4.8.5-11
Description:
I'm trying to attach a partition to one of my partitioned tables and I'm
getting an error:
ERROR: 42703: attribute 4 of type my_table_000000_partition has been
dropped
If I query the pg_attribute table I can see a column in position 4 that was
deleted years ago, it says now `pg.dropped.4`. The new 4th column is the one
used for the range partition (`my_timestamp`):
select attrelid::regclass, attnum, attname
from pg_attribute
where attrelid = 'my_table_000000_partition'::regclass::oid and
attnum > 0;
Field `my_timestamp` is the field I'm using for range partitioning.
attrelid | attnum | attname
-------------------------------+--------+------------------------------
my_table_000000_partition | 1 | id
my_table_000000_partition | 2 | cust_id
my_table_000000_partition | 3 | some_field_A
my_table_000000_partition | 4 | ........pg.dropped.4........
my_table_000000_partition | 5 | my_timestamp
my_table_000000_partition | 6 | ........pg.dropped.6........
my_table_000000_partition | 7 | some_field_B
.... some more fields ...
I can reproduce the problem locally, but only with this table that already
has the issue.
This is how I created my partition:
CREATE TABLE my_table_201906_partition (LIKE my_table_000000_partition
INCLUDING ALL)
This CREATE TABLE worked ok. `my_table_000000_partition` is the default
partition of its parent `my_table`.
And this is how I'm trying to attach it to the table:
ALTER TABLE my_table ATTACH PARTITION my_table_201906_partition
FOR VALUES FROM ('2019-06-01 00:00:00+00') TO ('2019-07-01
00:00:00+00');
The full error is:
```
INFO: 00000: partition constraint for table "my_table_201906_partition" is
implied by existing constraints
LOCATION: QueuePartitionConstraintValidation, tablecmds.c:14540
ERROR: 42703: attribute 4 of type my_table_000000_partition has been
dropped
LOCATION: CheckVarSlotCompatibility, execExprInterp.c:1880
Time: 10.571 ms
```
So far my workaround is:
1. Detach the default partition
2. Attach the new partition
3. Reattach the default partition
This way the issue doesn't manifest.
And some technical details that maybe you'll ask for:
* I'm running on RDS. This DB used to be a Postgres 9.5 then we migrated to
9.6 then to 10.x and now to 11.2
* Exact version as returned by `select version()` is:
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)
There is a bit more of info here:
https://stackoverflow.com/questions/56744481/postgres-error-on-partitioned-table-error-42703-attribu...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #15873: Attaching a partition fails because it sees deleted columns
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
@ 2019-06-25 21:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-06-26 04:32 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-06-25 21:49 UTC (permalink / raw)
To: dwilches@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Bug reference: 15873
> PostgreSQL version: 11.2
> I'm trying to attach a partition to one of my partitioned tables and I'm
> getting an error:
> ERROR: 42703: attribute 4 of type my_table_000000_partition has been
> dropped
FWIW, I tried to reproduce this using the attached example. Works
fine here. However, this report sounds quite a lot like some partitioning
bugs that have been fixed in the past. Could you update to 11.4 and try
again? If it still fails, then there's some aspect of your problem table
that you have not told us about.
regards, tom lane
drop table if exists pparent;
create table pparent(f1 int, z int, f2 int, my_timestamp timestamptz, f3 int)
partition by range (my_timestamp);
alter table pparent drop column z;
create table defchild (f1 int, f2 int,
bogus1 text, my_timestamp timestamptz, bogus2 text, f3 int);
alter table defchild drop column bogus1, drop column bogus2;
alter table pparent attach partition defchild default;
create table otherchild (like defchild including all);
alter table pparent attach partition otherchild
for values from ('2019-01-01') to ('2020-01-01');
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #15873: Attaching a partition fails because it sees deleted columns
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-06-26 04:32 ` Amit Langote <amitlangote09@gmail.com>
2019-06-28 05:34 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
2019-06-28 19:11 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: Amit Langote @ 2019-06-26 04:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dwilches@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jun 26, 2019 at 6:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > Bug reference: 15873
> > PostgreSQL version: 11.2
>
> > I'm trying to attach a partition to one of my partitioned tables and I'm
> > getting an error:
> > ERROR: 42703: attribute 4 of type my_table_000000_partition has been
> > dropped
>
> FWIW, I tried to reproduce this using the attached example. Works
> fine here. However, this report sounds quite a lot like some partitioning
> bugs that have been fixed in the past. Could you update to 11.4 and try
> again? If it still fails, then there's some aspect of your problem table
> that you have not told us about.
I was able to reproduce the error that OP is seeing and some more.
To reproduce, you'd need to rejigger defchild's attributes such that
it contains a dropped column in the attnum position where pparent
contains its partition key.
> drop table if exists pparent;
> create table pparent(f1 int, z int, f2 int, my_timestamp timestamptz, f3 int)
> partition by range (my_timestamp);
So, change this:
> create table defchild (f1 int, f2 int,
> bogus1 text, my_timestamp timestamptz, bogus2 text, f3 int);
to:
create table defchild (f1 int, f2 int, bogus1 text, bogus2 text,
my_timestamp timestamptz, f3 int);
> alter table defchild drop column bogus1, drop column bogus2;
Now, pparent contains my_timestamp (the partition key) in 4th
position, whereas defchild contains a dropped column in that position.
> alter table pparent attach partition defchild default;
>
> create table otherchild (like defchild including all);
>
> alter table pparent attach partition otherchild
> for values from ('2019-01-01') to ('2020-01-01');
Finally, you need to insert at least one row into the default
partition before attaching 'otherrel'.
insert into pparent values (1, 1, '2019-01-01', 1);
alter table pparent attach partition otherchild
for values from ('2019-01-01') to ('2020-01-01');
ERROR: attribute 4 of type defchild has been dropped
This error is thrown when defchild is scanned to check if it contains
any rows that better have not been there (because they belonged to the
partition being attached).
The bug seems to be that the partition constraint qual expression
that's used for the scan hasn't been translated to bear the default
partition's attnos. Attached patch fixes it.
When fixing this, I noticed another problem that's caused by a
different instance of the same bug. See the following example:
-- normal case
create table p (a int, b int) partition by list (b);
create table p_def partition of p default;
alter table p_def add check (b < 0);
create table p_1 partition of p for values in (1);
INFO: updated partition constraint for default partition "p_def" is
implied by existing constraints
-- buggy case
create table p (a int, b int) partition by list (b);
create table p_def (a int, c int, b int);
alter table p_def drop c;
alter table p attach partition p_def default;
alter table p_def add check (b < 0);
-- no INFO message
create table p_1 partition of p for values in (1);
The problem here is that an partition constraint qual expression
passed to predtest.c hasn't been translated to match p_def's attnos.
Attached fixed that too.
Then I noticed a bug that's worse than the above two, whereby
check_default_partition_contents() scans the default partition with
only a partial expression, preventing the scan to detect violating
rows. It uses only the first element of the list of expressions
returned by make_ands_implicit(), assuming that there'd be only one,
but the Assert(list_length(<default-partition-constr-expr-list>) == 1)
fails make check. See the following example:
create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
create table rlp3_default partition of rlp3 default;
create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
insert into rlp3_default values ('xy');
-- this should've thrown an error that rlp3_default contains 'xy'
which shouldn't
-- be there
create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
insert into rlp3 values ('xy');
-- so now, there's 'xy' in two partitions!
select tableoid::regclass, * from rlp3;
tableoid │ b │ a
──────────────┼────┼───
rlp3nullxy │ xy │
rlp3_default │ xy │
(2 rows)
Attached fixes that too.
Thanks,
Amit
Attachments:
[application/octet-stream] default-part-qual-bugs.patch (8.3K, ../../CA+HiwqHApJRxNRv5N5Z70E7OcajLqxNwC3P=Z5qBdCp_CqW6cg@mail.gmail.com/2-default-part-qual-bugs.patch)
download | inline diff:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ba59fc708a..3a99bbd3e2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15666,6 +15666,13 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
defaultrel = table_open(defaultPartOid, NoLock);
defPartConstraint =
get_proposed_default_constraint(partBoundConstraint);
+ /*
+ * Map the Vars in the constraint expression from rel's attnos to
+ * defaultrel's.
+ */
+ defPartConstraint =
+ map_partition_varattnos(defPartConstraint,
+ 1, defaultrel, rel, NULL);
QueuePartitionConstraintValidation(wqueue, defaultrel,
defPartConstraint, true);
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 99d26de7e6..f3c9236ad5 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -1237,6 +1237,13 @@ check_default_partition_contents(Relation parent, Relation default_rel,
: get_qual_for_range(parent, new_spec, false);
def_part_constraints =
get_proposed_default_constraint(new_part_constraints);
+ /*
+ * Map the Vars in the constraint expression from parent's attnos to
+ * default_rel's.
+ */
+ def_part_constraints =
+ map_partition_varattnos(def_part_constraints, 1, default_rel,
+ parent, NULL);
/*
* If the existing constraints on the default partition imply that it will
@@ -1265,7 +1272,6 @@ check_default_partition_contents(Relation parent, Relation default_rel,
{
Oid part_relid = lfirst_oid(lc);
Relation part_rel;
- Expr *constr;
Expr *partition_constraint;
EState *estate;
ExprState *partqualstate = NULL;
@@ -1281,6 +1287,15 @@ check_default_partition_contents(Relation parent, Relation default_rel,
part_rel = table_open(part_relid, NoLock);
/*
+ * Map the Vars in the constraint expression from default_rel's
+ * the sub-partition's.
+ */
+ partition_constraint = make_ands_explicit(def_part_constraints);
+ partition_constraint = (Expr *)
+ map_partition_varattnos((List *) partition_constraint, 1,
+ part_rel, default_rel, NULL);
+
+ /*
* If the partition constraints on default partition child imply
* that it will not contain any row that would belong to the new
* partition, we can avoid scanning the child table.
@@ -1297,7 +1312,10 @@ check_default_partition_contents(Relation parent, Relation default_rel,
}
}
else
+ {
part_rel = default_rel;
+ partition_constraint = make_ands_explicit(def_part_constraints);
+ }
/*
* Only RELKIND_RELATION relations (i.e. leaf partitions) need to be
@@ -1318,10 +1336,6 @@ check_default_partition_contents(Relation parent, Relation default_rel,
continue;
}
- constr = linitial(def_part_constraints);
- partition_constraint = (Expr *)
- map_partition_varattnos((List *) constr,
- 1, part_rel, parent, NULL);
estate = CreateExecutorState();
/* Build expression execution states for partition check quals */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index a639601f66..e687150511 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4008,7 +4008,8 @@ DROP USER regress_alter_table_user1;
-- default partition
create table defpart_attach_test (a int) partition by list (a);
create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
-create table defpart_attach_test_d (like defpart_attach_test);
+create table defpart_attach_test_d (b int, a int);
+alter table defpart_attach_test_d drop b;
insert into defpart_attach_test_d values (1), (2);
-- error because its constraint as the default partition would be violated
-- by the row containing 1
@@ -4019,6 +4020,12 @@ alter table defpart_attach_test_d add check (a > 1);
-- should be attached successfully and without needing to be scanned
alter table defpart_attach_test attach partition defpart_attach_test_d default;
INFO: partition constraint for table "defpart_attach_test_d" is implied by existing constraints
+-- check that attaching a partition correctly reports any rows in the default
+-- partition that should not be there for the new partition to be attached
+-- successfully
+create table defpart_attach_test_2 (like defpart_attach_test_d);
+alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
+ERROR: updated partition constraint for default partition would be violated by some row
drop table defpart_attach_test;
-- check combinations of temporary and permanent relations when attaching
-- partitions.
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 204441e759..262abf2bfb 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1119,3 +1119,17 @@ select tableoid::regclass from volatile_partbound_test;
(1 row)
drop table volatile_partbound_test;
+-- test the case where a check constraint on default partition allows
+-- to avoid scanning it when adding a new partition
+create table defcheck (a int, b int) partition by list (b);
+create table defcheck_def (a int, c int, b int);
+alter table defcheck_def drop c;
+alter table defcheck attach partition defcheck_def default;
+alter table defcheck_def add check (b <= 0 and b is not null);
+create table defcheck_1 partition of defcheck for values in (1, null);
+INFO: updated partition constraint for default partition "defcheck_def" is implied by existing constraints
+-- test that complex default partition constraints are enforced correctly
+insert into defcheck_def values (0, 0);
+create table defcheck_0 partition of defcheck for values in (0);
+ERROR: updated partition constraint for default partition "defcheck_def" would be violated by some row
+drop table defcheck;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 0369909b50..8016f8a823 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2648,7 +2648,8 @@ DROP USER regress_alter_table_user1;
-- default partition
create table defpart_attach_test (a int) partition by list (a);
create table defpart_attach_test1 partition of defpart_attach_test for values in (1);
-create table defpart_attach_test_d (like defpart_attach_test);
+create table defpart_attach_test_d (b int, a int);
+alter table defpart_attach_test_d drop b;
insert into defpart_attach_test_d values (1), (2);
-- error because its constraint as the default partition would be violated
@@ -2660,6 +2661,12 @@ alter table defpart_attach_test_d add check (a > 1);
-- should be attached successfully and without needing to be scanned
alter table defpart_attach_test attach partition defpart_attach_test_d default;
+-- check that attaching a partition correctly reports any rows in the default
+-- partition that should not be there for the new partition to be attached
+-- successfully
+create table defpart_attach_test_2 (like defpart_attach_test_d);
+alter table defpart_attach_test attach partition defpart_attach_test_2 for values in (2);
+
drop table defpart_attach_test;
-- check combinations of temporary and permanent relations when attaching
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 751c0d39f5..9c6d86a0bf 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -856,3 +856,17 @@ create table volatile_partbound_test2 partition of volatile_partbound_test for v
insert into volatile_partbound_test values (current_timestamp);
select tableoid::regclass from volatile_partbound_test;
drop table volatile_partbound_test;
+
+-- test the case where a check constraint on default partition allows
+-- to avoid scanning it when adding a new partition
+create table defcheck (a int, b int) partition by list (b);
+create table defcheck_def (a int, c int, b int);
+alter table defcheck_def drop c;
+alter table defcheck attach partition defcheck_def default;
+alter table defcheck_def add check (b <= 0 and b is not null);
+create table defcheck_1 partition of defcheck for values in (1, null);
+
+-- test that complex default partition constraints are enforced correctly
+insert into defcheck_def values (0, 0);
+create table defcheck_0 partition of defcheck for values in (0);
+drop table defcheck;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #15873: Attaching a partition fails because it sees deleted columns
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Tom Lane <tgl@sss.pgh.pa.us>
2019-06-26 04:32 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
@ 2019-06-28 05:34 ` Amit Langote <amitlangote09@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Amit Langote @ 2019-06-28 05:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: dwilches@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jun 26, 2019 at 1:32 PM Amit Langote <amitlangote09@gmail.com> wrote:
> Attached fixes that too.
I've added this to the upcoming CF:
https://commitfest.postgresql.org/23/2182/
Thanks,
Amit
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #15873: Attaching a partition fails because it sees deleted columns
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Tom Lane <tgl@sss.pgh.pa.us>
2019-06-26 04:32 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
@ 2019-06-28 19:11 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-06-29 05:13 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2019-06-28 19:11 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dwilches@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Thank you, Amit. I have pushed this now to both branches.
Gracias, Daniel.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #15873: Attaching a partition fails because it sees deleted columns
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Tom Lane <tgl@sss.pgh.pa.us>
2019-06-26 04:32 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Amit Langote <amitlangote09@gmail.com>
2019-06-28 19:11 ` Re: BUG #15873: Attaching a partition fails because it sees deleted columns Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-06-29 05:13 ` Amit Langote <amitlangote09@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Amit Langote @ 2019-06-29 05:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; dwilches@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sat, Jun 29, 2019 at 4:11 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Thank you, Amit. I have pushed this now to both branches.
Great, thanks.
Regards,
Amit
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-06-29 05:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-06-25 21:30 BUG #15873: Attaching a partition fails because it sees deleted columns PG Bug reporting form <noreply@postgresql.org>
2019-06-25 21:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-06-26 04:32 ` Amit Langote <amitlangote09@gmail.com>
2019-06-28 05:34 ` Amit Langote <amitlangote09@gmail.com>
2019-06-28 19:11 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-06-29 05:13 ` Amit Langote <amitlangote09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox