BUG #15873: Attaching a partition fails because it sees deleted columns

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15873: Attaching a partition fails because it sees deleted columns
Дата
Msg-id 15873-8c61945d6b3ef87c@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15873: Attaching a partition fails because it sees deleted columns  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Kassym Dorsel
Дата:
Сообщение: Re: BUG #15869: Custom aggregation returns null when parallelized
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #15873: Attaching a partition fails because it sees deleted columns