Data corruption after using drop_chunks()

TimescaleDB and PostgreSQL
1

Within our monitoring stack we collect data (telegraf) and save these dat into a PostgreSQL database with Timescale hyper tables.

As the database had exploded, I deleted old data that was before 1.1.2024. A DELETE took too long and I wanted to solve this efficiently using the drop_chunks() function. I then also found a suitable statement:

SELECT DROP_CHUNKS (
	FORMAT('%I.%I', HYPERTABLE_SCHEMA, HYPERTABLE_NAME)::REGCLASS,
	OLDER_THAN => DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
)
FROM TIMESCALEDB_INFORMATION.HYPERTABLES;

So far, so good. After that I deleted unnecessary tags in the corresponding tag tables, for example:

DELETE FROM ASE_BLOCKER_TAG AS T
WHERE NOT EXISTS (
	SELECT *
	FROM ASE_BLOCKER
	WHERE TAG_ID = T.TAG_ID
);

Once that was done, I tried to VACUUM the database:

VACCUM FULL;

Damn!:

ERROR: pg_attribute catalog is missing 8 attribute(s) for relation OID 1380158

FEHLER: pg_attribute catalog is missing 8 attribute(s) for relation OID 1380158
SQL state: XX000

The statement …

SELECT DISTINCT relname 
FROM pg_class 
WHERE NOT EXISTS (
	SELECT 1
	FROM pg_attribute
	WHERE attrelid = relfilenode
);

… returned over 5,8k rows with many hyper tables and chunks.

What have I done wrong?

There is no backup, as this was not previously considered necessary for monitoring databases. I know: No backup, no mercy! We will learn from this.

But is it still possible to salvage something here and restore missing relationships in another way? How can I at least recreate the hypertables?

Thanks a lot!

2

Hi @kmp , are you sure that the OID belongs to this scenario? Because the function is a public API and should keep it consistent.

Also, when you drop chunks, there’s no need to VACUUM because each chunk stores its statistics, so everything is updated after the chunk is dropped.

Can you confirm the OID is related to the hypertable?

4

Hi @jonatasdp,

how can I prove this? After executing the statement …

SELECT DISTINCT relname 
FROM pg_class 
WHERE NOT EXISTS (
	SELECT 1
	FROM pg_attribute
	WHERE attrelid = relfilenode
);

… the results look like this:

image

5

By the way, also SELECTs on hyper tables are no longer working:

image

6

This OID seems to be related to a hyper table:

image

7

Sorry, what I forgot:

PostgreSQL version: PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
Timescale version: 2.5.0