Creating an index using transaction_per_chunk setting fails ifable contains an attribute that was dropped

[jocrau]

Relevant system information:

• OS: Debian GNU/Linux 10 (buster)
• PostgreSQL version (output of postgres --version): 11.9
• TimescaleDB version (output of \dx in psql): 1.7.4
• Installation method: Helm

Describe the bug

I tried to create an index using the transaction_per_chunk setting running

CREATE INDEX state_interval_right_open_index ON timeseries.state_interval (metric_ref, native_code, label) WITH (timescaledb.transaction_per_chunk) WHERE "end" IS NULL;

but it fails with

[XX000] ERROR: index attribute ........pg.dropped.3........ not found in chunk

It turns out that one of the attributes on the table was dropped:

SELECT attname, attisdropped FROM pg_attribute WHERE attrelid = 'state_interval'::REGCLASS;

+------+----------------------------+------------+
|attnum|attname                     |attisdropped|
+------+----------------------------+------------+
|-7    |tableoid                    |false       |
|-6    |cmax                        |false       |
|-5    |xmax                        |false       |
|-4    |cmin                        |false       |
|-3    |xmin                        |false       |
|-1    |ctid                        |false       |
|1     |metric_ref                  |false       |
|2     |native_code                 |false       |
|3     |........pg.dropped.3........|true        |
|4     |start                       |false       |
|5     |end                         |false       |
|6     |label                       |false       |
|7     |native_severity             |false       |
|8     |qualifier                   |false       |
|9     |description                 |false       |
|10    |sequence                    |false       |
+------+----------------------------+------------+

That might be caused by the fact that the function chunk_adjust_colref_attnos does not check whether the attribute was dropped. (edited)

[jocrau]

I tried running the blocking CREATE INDEX IF NOT EXISTS state_interval_right_open_index ON timeseries.state_interval (metric_ref, native_code, label) WHERE "end" IS NULL; but that also fails with:

[XX000] ERROR: cache lookup failed for attribute 5 of relation 0

[svenklemm]

@jocrau can you provide a test case for the transaction_per_chunk case. I could reproduce the 2nd error you got but not the first one.

[jocrau]

Thank you @svenklemm for fixing this. My apologies for not getting back with a test case for the transaction_per_chunk setting. I will test the fix against it and report back. Do you know when the fix will be released?

[jocrau]

We have upgraded TimescaleDB to v2.0.1, but the issue still persists:

tsdb_production=# CREATE INDEX IF NOT EXISTS state_interval_open_idx ON timeseries.state_interval (metric_ref, native_code) WITH (timescaledb.transaction_per_chunk) WHERE "end" IS NULL;

ERROR:  index attribute ........pg.dropped.3........ not found in chunk
tsdb_production=# \dx
                                         List of installed extensions
        Name        | Version |   Schema   |                            Description                            
--------------------+---------+------------+-------------------------------------------------------------------
 pg_stat_statements | 1.6     | timeseries | track execution statistics of all SQL statements executed
 pgcrypto           | 1.3     | public     | cryptographic functions
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb        | 2.0.1   | public     | Enables scalable inserts and complex queries for time-series data
(4 rows)

[tylerfontaine]

Re-opening since there seems to be an issue still with transaction_per_chunk, but it doesn't look like we've been able to reproduce that behavior, so if you have a reliable reproduction case you can share, that would be very helpful.

This does seem to have been fixed for index creation without transaction_per_chunk however.

[jocrau]

I ran the tests that cover the related PR in 1.7.4 and 2.0.1. It turns out that in 1.7.4 creating an index with

CREATE INDEX ON ht_dropped (c0, c1, c2) WHERE c1 IS NOT NULL;

fails with [XX000] ERROR: cache lookup failed for attribute 5 of relation 0 but

CREATE INDEX ON ht_dropped (c0, c1, c2) WITH (timescaledb.transaction_per_chunk) WHERE c2 IS NOT NULL;

succeeds. The second case is the one we were running up against. Both cases succeed in 2.0.1.

I believe the issue is, as I have pointed out in my initial post, "that the function chunk_adjust_colref_attnos does not check whether the attribute was dropped." I am not a C++ developer, but might a line like the following help?

if (!idxattr->attisdropped) { ... }

[tylerfontaine]

I have a working reproduction of this. It only happens when there's >1 chunk which does not align to the original hypertable schema, which is why this isn't caught by the current tests.

This was done in 2.0.2

create table idx_create_error (time timestamp, a int, b int, c int, d int);

select create_hypertable('idx_create_error', 'time');

insert into idx_create_error VALUES
(now(), 1, 2, 3, 4);

ALTER TABLE idx_create_error DROP COLUMN b;

insert into idx_create_error (time, a, c, d) VALUES
(now() - interval '1 year', 1, 2, 3),
(now() - interval '2 years', 1, 2, 3);

create index idx2 ON idx_create_error (a,d) WITH (timescaledb.transaction_per_chunk) WHERE c IS NOT NULL;

Results in:

ERROR:  index attribute ........pg.dropped.3........ not found in chunk