Postgres error on partitioned table (ERROR 42703 attribute 4 of type X has been dropped)

user_303914 0
Viewed 648

I'm attaching a partition to one of my partitioned tables and I'm getting an error I don't understand.

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

Any idea what the error can mean and how to solve it?

Edit:

Result of query suggested by amil

This is the query requested by @amil:

select attrelid::regclass, attnum, attname 
    from pg_attribute
    where attrelid in ('my_table'::regclass::oid, 
                       'my_table_000000_partition'::regclass::oid)
      and attnum > 0;

In the following results I mangled the field names, but I took care of calling some_field_1 to the same field of both tables. 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_1
 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_2
 my_table_000000_partition     |      8 | some_field_3
 my_table_000000_partition     |      9 | some_field_4
 my_table_000000_partition     |     10 | some_field_5
 my_table_000000_partition     |     11 | some_field_6
 my_table_000000_partition     |     12 | some_field_7
 my_table_000000_partition     |     13 | some_field_8
 my_table_000000_partition     |     14 | some_field_9
 my_table_000000_partition     |     15 | some_field_10
 my_table_000000_partition     |     16 | some_field_11
 my_table                      |      1 | id
 my_table                      |      2 | cust_id
 my_table                      |      3 | some_field_1
 my_table                      |      4 | my_timestamp
 my_table                      |      5 | some_field_2
 my_table                      |      6 | some_field_3
 my_table                      |      7 | some_field_4
 my_table                      |      8 | some_field_5
 my_table                      |      9 | some_field_6
 my_table                      |     10 | some_field_7
 my_table                      |     11 | some_field_8
 my_table                      |     12 | some_field_9
 my_table                      |     13 | some_field_10
 my_table                      |     14 | some_field_11
(30 rows)
partitioning postgresql
2 Answers

@MondoKin's answer already gives a workaround, but let me add an answer about what the error means and why it's caused in this case.

The error seen here is an internal system error thrown by the Postgres executor when it encounters an expression that's built using invalid metadata about a table's column, which in this case is a column value expression that references a dropped attribute of my_table_000000_partition.

In most cases involving such internal consistency checks (almost all of them), users are not supposed to expect errors being thrown by them. Such internal errors could only result from a corrupted internal state (on-disk or in-memory) or a bug of Postgres', which in this case, turned out to be the latter.

Thankfully, @MondoKin reported this issue to Postgres bugs mailing list. See this link to the mailing list archive, if interested: https://postgr.es/m/flat/15873-8c61945d6b3ef87c%40postgresql.org

user_6834020

I found a workaround to attach my new partition: you need to detach the problematic partition before trying to attach the new partition, then reattaching the problematic partition:

 CREATE TABLE my_table_201906_partition (LIKE my_table_000000_partition
                                         INCLUDING ALL);

 ALTER TABLE my_table DETACH PARTITION my_table_000000_partition;

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

ALTER TABLE my_table ATTACH PARTITION my_table_000000_partition DEFAULT;

This way, the old partition's dropped column doesn't get into the way of the ATTACH PARTITION ... FOR RANGE command. Which means I don't need to vacuum full the 273GiB table.

The only issue with this workaround is that you need to do it for every partition that you want to attach (until the bug is solved)

Edit:

I thought VACUUM FULL my_table_000000_partition would permanently solve the issue by totally removing the "deleted" columns, but no, the error persisted.

user_3039140
Related