table row type and query-specified row type do not match on insert

[Envek]

Hi all.

While playing with something on virtual machines I've encountered weird error from pg_pathman while trying to insert a record into partitioned table:

INSERT INTO tracker_points (evented_at, tracker_id) VALUES ('2017-01-10T00:00:00Z'::timestamp, uuid_generate_v4());
ERROR:  table row type and query-specified row type do not match
DETAIL:  Query provides a value for a dropped column at ordinal position 6.

Steps to reproduce:

1. Install PostgreSQL 9.5.5 from apt.postgresql.org on Ubuntu Linux 14.04

2. Install pg_pathman 1.2.1:

    sudo apt-get install -y build-essential git postgresql-server-dev-9.5 libkrb5-dev libssl-dev
    git clone https://github.com/postgrespro/pg_pathman.git
    cd pg_pathman
    git checkout 1.2.1
    make USE_PGXS=1
    sudo make install USE_PGXS=1
    # Added pg_pathman into preload_shared_libraries
    # Restart postgresql
   

3. Create database and set up tables

4. Set up pg_pathman

   CREATE EXTENSION pg_pathman;
   SELECT create_range_partitions( 'tracker_points', 'evented_at', '2015-06-01'::timestamp, '1 month'::interval, 0, false )
   SELECT add_range_partition( 'tracker_points', '2015-06-01'::timestamp, '2015-06-01'::timestamp + '1 month'::interval, 'tracker_points_2015_06' )
   SELECT append_range_partition('tracker_points', 'tracker_points_2015_07')
   -- and so on
   SELECT append_range_partition('tracker_points', 'tracker_points_2017_01')
   SELECT set_enable_parent('tracker_points', false)
   SELECT partition_table_concurrently('tracker_points')

   All commands executed successfully and in server logs appeared

    2017-01-10 19:53:40 MSK [2944-1] LOG:  ConcurrentPartWorker: loaded pg_pathman's config [2944]
   

   At this point of time all tables are empty.

5. Try to insert record:

   INSERT INTO tracker_points (evented_at, tracker_id) VALUES ('2017-01-10T00:00:00Z'::timestamp, uuid_generate_v4());

   Which fails with next error:

   ERROR:  table row type and query-specified row type do not match
   DETAIL:  Query provides a value for a dropped column at ordinal position 6.
   

Both parent and child tables look the same. These exact setup and this exact query is working in production on pg_pathman 1.1 and on staging environment on pg_pathman 1.2.1 upgraded from 1.1, but on virtual environment with fresh install is broken.

\d+ for parent table (tracker_points):

                                                  Table "public.tracker_points"
       Column        |            Type             |              Modifiers              | Storage  | Stats target | Description
---------------------+-----------------------------+-------------------------------------+----------+--------------+-------------
 id                  | uuid                        | not null default uuid_generate_v4() | plain    |              |
 tracker_id          | uuid                        | not null                            | plain    |              |
 data                | jsonb                       | not null default '{}'::jsonb        | extended |              |
 evented_at          | timestamp without time zone | not null                            | plain    |              |
 created_at          | timestamp without time zone |                                     | plain    |              |
 ambulance_status_id | integer                     |                                     | plain    |              |
 in_zone             | boolean                     | not null default true               | plain    |              |
 is_stop             | boolean                     | default false                       | plain    |              |
Indexes:
    "tracker_points_pkey" PRIMARY KEY, btree (id)
    "index_tracker_points_on_evented_at" brin (evented_at)
    "index_tracker_points_on_tracker_id" btree (tracker_id)
    "main_tracker_points_search_index" btree (tracker_id, evented_at DESC)
    "tracker_points_by_status_index" btree (ambulance_status_id, evented_at)
Child tables: tracker_points_2015_06,
              tracker_points_2015_07,
              and many more

\d+ for target child table (tracker_points_2017_01):

                                              Table "public.tracker_points_2017_01"
       Column        |            Type             |              Modifiers              | Storage  | Stats target | Description
---------------------+-----------------------------+-------------------------------------+----------+--------------+-------------
 id                  | uuid                        | not null default uuid_generate_v4() | plain    |              |
 tracker_id          | uuid                        | not null                            | plain    |              |
 data                | jsonb                       | not null default '{}'::jsonb        | extended |              |
 evented_at          | timestamp without time zone | not null                            | plain    |              |
 created_at          | timestamp without time zone |                                     | plain    |              |
 ambulance_status_id | integer                     |                                     | plain    |              |
 in_zone             | boolean                     | not null default true               | plain    |              |
 is_stop             | boolean                     | default false                       | plain    |              |
Indexes:
    "tracker_points_2017_01_pkey" PRIMARY KEY, btree (id)
    "tracker_points_2017_01_ambulance_status_id_evented_at_idx" btree (ambulance_status_id, evented_at)
    "tracker_points_2017_01_evented_at_idx" brin (evented_at)
    "tracker_points_2017_01_tracker_id_evented_at_idx" btree (tracker_id, evented_at DESC)
    "tracker_points_2017_01_tracker_id_idx" btree (tracker_id)
Check constraints:
    "pathman_tracker_points_2017_01_4_check" CHECK (evented_at >= '2017-01-01 00:00:00'::timestamp without time zone AND evented_at < '2017-02-01 00:00:00'::timestamp without time zone)
Inherits: tracker_points

Am I configured something wrong? How to debug this?

Thank you in advance.

[maksm90]

Hi, @Envek!

Your problem usually arises when tuple descriptor (i.e. schema, existing and deleted attributes) of parent table doesn't correspond to child's one. You can check this fact by executing the following query for parent and attached child tables:

select attname, attnum,  attisdropped
from pg_attribute
where attrelid='<table_name>'::regclass

and compare their attributes.

We're working on this issue. Stay tuned.

[Envek]

Oh yes, now I can see that one of the earlier migrations dropped one column before migration with pg_pathman:

smp=# SELECT attname, attnum,  attisdropped FROM pg_attribute WHERE attrelid = 'tracker_points'::regclass;
           attname            | attnum | attisdropped
------------------------------+--------+--------------
 tableoid                     |     -7 | f
 cmax                         |     -6 | f
 xmax                         |     -5 | f
 cmin                         |     -4 | f
 xmin                         |     -3 | f
 ctid                         |     -1 | f
 id                           |      1 | f
 tracker_id                   |      2 | f
 data                         |      3 | f
 evented_at                   |      4 | f
 created_at                   |      5 | f
 ........pg.dropped.6........ |      6 | t
 ambulance_status_id          |      7 | f
 in_zone                      |      8 | f
 is_stop                      |      9 | f
(15 rows)

smp=# SELECT attname, attnum,  attisdropped FROM pg_attribute WHERE attrelid = 'tracker_points_2017_01'::regclass;
       attname       | attnum | attisdropped
---------------------+--------+--------------
 tableoid            |     -7 | f
 cmax                |     -6 | f
 xmax                |     -5 | f
 cmin                |     -4 | f
 xmin                |     -3 | f
 ctid                |     -1 | f
 id                  |      1 | f
 tracker_id          |      2 | f
 data                |      3 | f
 evented_at          |      4 | f
 created_at          |      5 | f
 ambulance_status_id |      6 | f
 in_zone             |      7 | f
 is_stop             |      8 | f
(14 rows)

On the staging and production databases there is no dropped column:

smp=# SELECT attname, attnum,  attisdropped FROM pg_attribute WHERE attrelid = 'tracker_points'::regclass;
       attname       | attnum | attisdropped
---------------------+--------+--------------
 tableoid            |     -7 | f
 cmax                |     -6 | f
 xmax                |     -5 | f
 cmin                |     -4 | f
 xmin                |     -3 | f
 ctid                |     -1 | f
 id                  |      1 | f
 tracker_id          |      2 | f
 data                |      3 | f
 evented_at          |      4 | f
 created_at          |      5 | f
 ambulance_status_id |      6 | f
 in_zone             |      7 | f
 is_stop             |      8 | f
(14 rows)

I think it because of pg_upgrade from 9.4 to 9.5 was performed after the migration that dropped column was applied and (I wonder) rebuilt the original table. Or something else. Now I'm trying to apply all migrations from scratch in one run to the fresh installation and it fails.

[funbringer]

UPDATE: we've come up with a fix, but it's not publicly available yet. We're going to test it for a while.

[kataev]

A have some issue after drop columns at parent table and at all child's.
I check what all table has similar set of attributes with this script.

DO
$$
DECLARE
  row RECORD;
  i   RECORD;
BEGIN
  FOR row IN SELECT
               relname,
               relpages
             FROM pg_tables
               JOIN pg_class ON tablename = relname
             WHERE schemaname = 'main' AND tablename LIKE 'content%'
             ORDER BY relname
  LOOP
    SELECT
      count(*)
        FILTER (WHERE attisdropped != TRUE) AS non_droped,
      count(*)
        FILTER (WHERE attisdropped = TRUE)  AS dropped
    FROM pg_attribute
    WHERE attrelid = row.relname :: REGCLASS
    INTO i;
    IF i.non_droped != 11 OR i.dropped != 2
    THEN
      RAISE NOTICE '% % %', row.relname, i.non_droped, i.dropped;
    END IF;
  END LOOP;
END;
$$;

Output nothing.

But get error at insert in parent table any way:

Query provides a value for a dropped column at ordinal position 6.

But direct insert in partition work well.

Some other output:

# select attname, attnum,  attisdropped
from pg_attribute
where attrelid='content'::regclass;
           attname            | attnum | attisdropped 
------------------------------+--------+--------------
 tableoid                     |     -7 | f
 cmax                         |     -6 | f
 xmax                         |     -5 | f
 cmin                         |     -4 | f
 xmin                         |     -3 | f
 ctid                         |     -1 | f
 id                           |      1 | f
 domain_id                    |      2 | f
 source_id                    |      3 | f
 dog_id                       |      4 | f
 path_id                      |      5 | f
 ........pg.dropped.6........ |      6 | t
 ........pg.dropped.7........ |      7 | t
(13 rows)

# select attname, attnum,  attisdropped
from pg_attribute
where attrelid='content_2'::regclass;
           attname            | attnum | attisdropped 
------------------------------+--------+--------------
 tableoid                     |     -7 | f
 cmax                         |     -6 | f
 xmax                         |     -5 | f
 cmin                         |     -4 | f
 xmin                         |     -3 | f
 ctid                         |     -1 | f
 id                           |      1 | f
 domain_id                    |      2 | f
 source_id                    |      3 | f
 dog_id                       |      4 | f
 path_id                      |      5 | f
 ........pg.dropped.6........ |      6 | t
 ........pg.dropped.7........ |      7 | t
(13 rows)

[kataev]

Well add "pg_pathman.enable_partitionfilter = off" fix insert issue.

[funbringer]

Hi @kataev,

It doesn't fix anything, you're just inserting values into parent table. As I've already mentioned, we're working on a bugfix (branch rel_1_3_beta).

[kataev]

Thank you for quick answer and help.

Code from branch really helps, thank you very much!

[funbringer]

to @kataev,

Code from branch really helps, thank you very much!

Good, thanks for testing it. Don't forget to perform a clean installation after we've released 1.3. Our beta releases cannot be upgraded safely.

[funbringer]

Fixed.

[Envek]

Confirmed. Thank you!