Error after alter main table: "ERROR: could not find attribute 43 in subquery targetlist"

[Focus1337]

We create and use immv as a cache: airline_trip_cache.

Before:

airline_trip_cache uses the following tables (DDL):

create table airline_trip_items
(
    id      uuid not null
        primary key,
    trip_id uuid
        references trips
);

create table airline_versions
(
    id                                   uuid      not null
        primary key,
    airline_trip_item_id                 uuid      not null
        references airline_trip_items,
    employee_version_id                  uuid
        references employee_versions,
    employee_document_version_id         uuid
        references employee_document_versions,
    company_id                           integer
        references companies,
    provider                             text      not null,
    creator_user_id                      uuid      not null
        references users,
    created_date                         timestamp not null,
    departure_date                       timestamp not null,
    arrival_date                         timestamp not null,
    status                               text      not null,
    service_platform                     smallint  not null,
    description                          text      not null,
    class                                smallint  not null,
    office_id                            text      not null,
    ticket_number                        text      not null,
    ticket_pnr                           text,
    ticket_pnr_locator                   text,
    ticket_void_time_limit               text,
    ticket_void_until_end_of_day         text,
    ticket_reserve_expires               timestamp,
    fare_id                              text,
    fare_flight_token                    text,
    fare_name                            text,
    fare_airline_bonus_information       text,
    fare_price                           numeric   not null,
    cart_price                           numeric   not null,
    is_carryon_included                  smallint  not null,
    carryon_places                       text,
    is_baggage_included                  smallint  not null,
    baggage_places                       text,
    can_buy_baggage                      smallint  not null,
    is_ticket_refundable                 smallint  not null,
    is_ticket_refundable_after_departure smallint  not null,
    is_ticket_changeable                 smallint  not null,
    is_ticket_changeable_after_departure smallint  not null,
    can_registration_seat                smallint  not null,
    is_business_hall_included            smallint  not null,
    have_internet_access                 smallint  not null,
    is_priority_registration             smallint  not null,
    is_priority_baggage_reception        smallint  not null,
    can_upgrade_rate                     smallint  not null,
    route_cities                         text[]    not null
);

create table employee_versions
(
    id              uuid        not null
        primary key,
    employee_id     uuid        not null
        references employees,
    created_date    timestamp   not null,
    creator_user_id uuid        not null
        references users,
    phone_number    varchar(15) not null,
    country_code    varchar(2)  not null,
    birthday        date        not null,
    sex             smallint    not null,
    first_name      varchar(64) not null,
    last_name       varchar(64) not null,
    middle_name     varchar(64),
    email           varchar(64) not null,
    status          smallint    not null
);

We create immv airline_trip_cache like this:

select create_immv('trip_airline_cache',
                   'select ati.trip_id  as trip_id,
                      ati.id            as trip_item_id,
                      av.created_date   as created_date,
                      departure_date    as checkin_date,
                      arrival_date      as checkout_date,
                      av.route_cities   as route_cities,
                      ev.employee_id    as employee_id,
                      ''Airline''       as service_type,
                      ev.last_name      as last_name,
                      ev.first_name     as first_name,
                      ev.middle_name    as middle_name,
                      av.status         as status
               from airline_trip_items ati
                        inner join airline_versions av on av.airline_trip_item_id = ati.id
                        inner join employee_versions ev on av.employee_version_id = ev.id;');

Everything worked great, but we needed to make changes.

After:

We alter airline_versions - add 1 column, drop 3 columns, create a new table airline_version_employees, change airline_trip_cache according to the new changes - first drop the table, and then create:

drop table if exists trip_airline_cache;

create table airline_version_employees
(
    airline_version_id           uuid not null references airline_versions (id),
    employee_version_id          uuid not null references employee_versions (id),
    employee_document_version_id uuid references employee_document_versions (id),
    company_id                   int references companies (id),
    primary key (airline_version_id, employee_version_id)
);

INSERT INTO airline_version_employees (airline_version_id, employee_version_id, employee_document_version_id, company_id)
SELECT av.id AS airline_version_id,
       av.employee_version_id,
       av.employee_document_version_id,
       av.company_id
FROM airline_versions av
WHERE av.employee_version_id IS NOT NULL;

ALTER TABLE airline_versions
    DROP COLUMN employee_version_id,
    DROP COLUMN employee_document_version_id,
    DROP COLUMN company_id,
    ADD COLUMN travellers_count INTEGER NOT NULL DEFAULT 1;

select create_immv('trip_airline_cache',
                   'select ati.trip_id  as trip_id,
                      ati.id            as trip_item_id,
                      av.created_date   as created_date,
                      departure_date    as checkin_date,
                      arrival_date      as checkout_date,
                      av.route_cities   as route_cities,
                      ev.employee_id    as employee_id,
                      ''Airline''       as service_type,
                      ev.last_name      as last_name,
                      ev.first_name     as first_name,
                      ev.middle_name    as middle_name,
                      av.status         as status
               from airline_trip_items ati
                        inner join airline_versions av on av.airline_trip_item_id = ati.id
                        inner join airline_version_employees ave on av.id = ave.airline_version_id
                        inner join employee_versions ev on ave.employee_version_id = ev.id;');

Problem:

We are faced with a problem that after altering the airline_versions table, when we try to insert/change/delete into it, we encounter the error "ERROR: could not find attribute 43 in subquery targetlist".

We tried to create all the tables at once in their final form (without alter, etc.), and in this case everything works. This problem only occurs when we modify the main tables. And also, apparently, the presence of records does not affect the occurrence of the problem.

I'm guessing the issue is that immv cannot process the table correctly when there are gaps in the attribute numbering. Checked the airline_versions table attributes. Results: Before:

| attname                              | attnum |
|--------------------------------------|--------|
| id                                   | 1      |
| airline_trip_item_id                 | 2      |
| employee_version_id                  | 3      |
| employee_document_version_id         | 4      |
| company_id                           | 5      |
| provider                             | 6      |
| creator_user_id                      | 7      |
| created_date                         | 8      |
| departure_date                       | 9      |
| arrival_date                         | 10     |
| status                               | 11     |
| service_platform                     | 12     |
| description                          | 13     |
| class                                | 14     |
| office_id                            | 15     |
| ticket_number                        | 16     |
| ticket_pnr                           | 17     |
| ticket_pnr_locator                   | 18     |
| ticket_void_time_limit               | 19     |
| ticket_void_until_end_of_day         | 20     |
| ticket_reserve_expires               | 21     |
| fare_id                              | 22     |
| fare_flight_token                    | 23     |
| fare_name                            | 24     |
| fare_airline_bonus_information       | 25     |
| fare_price                           | 26     |
| cart_price                           | 27     |
| is_carryon_included                  | 28     |
| carryon_places                       | 29     |
| is_baggage_included                  | 30     |
| baggage_places                       | 31     |
| can_buy_baggage                      | 32     |
| is_ticket_refundable                 | 33     |
| is_ticket_refundable_after_departure | 34     |
| is_ticket_changeable                 | 35     |
| is_ticket_changeable_after_departure | 36     |
| can_registration_seat                | 37     |
| is_business_hall_included            | 38     |
| have_internet_access                 | 39     |
| is_priority_registration             | 40     |
| is_priority_baggage_reception        | 41     |
| can_upgrade_rate                     | 42     |
| route_cities                         | 43     |

After:

| attname                              | attnum |
|--------------------------------------|--------|
| id                                   | 1      |
| airline_trip_item_id                 | 2      |
| provider                             | 6      |
| creator_user_id                      | 7      |
| created_date                         | 8      |
| departure_date                       | 9      |
| arrival_date                         | 10     |
| status                               | 11     |
| service_platform                     | 12     |
| description                          | 13     |
| class                                | 14     |
| office_id                            | 15     |
| ticket_number                        | 16     |
| ticket_pnr                           | 17     |
| ticket_pnr_locator                   | 18     |
| ticket_void_time_limit               | 19     |
| ticket_void_until_end_of_day         | 20     |
| ticket_reserve_expires               | 21     |
| fare_id                              | 22     |
| fare_flight_token                    | 23     |
| fare_name                            | 24     |
| fare_airline_bonus_information       | 25     |
| fare_price                           | 26     |
| cart_price                           | 27     |
| is_carryon_included                  | 28     |
| carryon_places                       | 29     |
| is_baggage_included                  | 30     |
| baggage_places                       | 31     |
| can_buy_baggage                      | 32     |
| is_ticket_refundable                 | 33     |
| is_ticket_refundable_after_departure | 34     |
| is_ticket_changeable                 | 35     |
| is_ticket_changeable_after_departure | 36     |
| can_registration_seat                | 37     |
| is_business_hall_included            | 38     |
| have_internet_access                 | 39     |
| is_priority_registration             | 40     |
| is_priority_baggage_reception        | 41     |
| can_upgrade_rate                     | 42     |
| route_cities                         | 43     |
| travellers_count                     | 44     |

refresh_immv, re-creating airline_trip_cache, and re-creating the extension didn't help us. Only if we don’t use airline_trip_cache, then everything works.

We tried it on Postgres 15.3, 16, as well as pg_ivm versions: 1.5, 1.7, 1.8 - none of them solved the problem.

If you need even more input, let me know. Perhaps, you need information from pg_ivm_immv. I hope you can tell me what we are missing or doing wrong.

[Focus1337]

Update. We fixed the problem as follows:

1. DROP immv trip_airline_cache drop table if exists trip_airline_cache;
2. as in the previous message, we made changes to the schema: we created airline_version_employees, inserted data from airline_versions, and altered airline_versions table.
3. Created a copy of the table - airline_versions2

   CREATE TABLE airline_versions2
   (
    LIKE airline_versions INCLUDING ALL
   );

   and this table airline_versions2 now has sequential numbering without gaps.

4. Copied all the records from airline_versions there and moved all the constraints.
5. DROP airline_versions
6. Renamed airline_versions2 to the old name
7. We created a new immv trip_airline_cache with an updated query (as in the previous message).

However, this solution is bad. Because if we need to make changes in other tables, we will have to repeat this procedure again.

We came to the conclusion that the problem lies in working with attributes in the pg_ivm extension.

We still hope to receive detailed comments from you. Thank you.

[aykuli]

I have the same issue [XX000] ERROR: could not find attribute 7 in subquery targetlist.

I saw one of the tables I wanna use in create_immv has some changes in its history, stored in pg_attrbute. Maybe it's really issue about attnum sequence

[aykuli]

I solved this issue for me like that:

SELECT create_immv('immv_assignments','SELECT a.id id, a.user_id user_id, u.position position, r.code role_code, r.id role_id, g.code agency_code, g.acronym agency_acronym FROM ' ||
                              '(SELECT id, user_id, role_id, agency_id FROM assignments) a ' ||
                              'INNER JOIN users u ON u.id = a.user_id ' ||
                              'INNER JOIN roles r ON r.id = a.role_id ' ||
                              'INNER JOIN agencies g ON g.id = a.agency_id');

assignments is the table with pg.dropped column. I pointed exact columns for table assignments to avoid dropped column.

[yugo-n]

Thank you for your reports and sorry for the late response.

I can reproduce it in a simple way as following.

test=# create table t (i int, j int);
CREATE TABLE
test=# alter table t drop column i, add column k int;
ALTER TABLE
test=# select create_immv('mv','select * from t');
...(sinp)...
 create_immv 
-------------
           0
(1 row)

test=# insert into t values (1,1);
ERROR:  could not find attribute 3 in subquery targetlist

This is definitely a bug, so I'll fix it.