Обсуждение: BUG #15873: Attaching a partition fails because it sees deleted columns

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-attribute-4-of-type-x-has-been

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');

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

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

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

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