I ran into an error while upgrading a timescale instance in kubernetes from 2.6.0 to 2.9.1
I had updated my image and then tried to run the alter extension command with
psql -X -d mydb -c 'ALTER EXTENSION timescaledb UPDATE;'
I got the following error:
ERROR: could not create unique index “compression_chunk_size_pkey”
DETAIL: Key (chunk_id)=(582) is duplicated.
Finding a reference to ‘compression_chunk_size_pkey’ on the timescale github timescaledb/sql/pre_install/tables.sql, it seemed to refer to the internal table _timescaledb_catalog.compression_chunk_size
Querying the table:
SELECT * FROM _timescaledb_catalog.compression_chunk_size WHERE chunk_id = 582;
returned 1 row.
SELECT chunk_id, count(chunk_id) as dupecount FROM _timescaledb_catalog.compression_chunk_size GROUP BY chunk_id HAVING count(chunk_id)>1;
gave me 20 rows, including chunk_id 582
TBH I don’t understand how those 2 contradicting query results are possible, but I really needed to move forward with this upgrade, so I found that if I decompress chunk name
then updating the extension failed again, now citing the next id on my list as being duplicated. So then I just blasted them with:
SELECT decompress_chunk('_timescaledb_internal._hyper_1_' || chunk_id || '_chunk') FROM _timescaledb_catalog.compression_chunk_size GROUP BY chunk_id HAVING count(chunk_id) > 1;
And could upgrade successfully.
But of course, now I am unable to call compress_chunk on the chunks I had decompressed, since I get the same error about key violation.
I have quite a few other dbs on my cluster that I would like to upgrade, but I can see that about half of them have this same issue, and I am reluctant to apply the same hack to all - is there anything I can do to clean it up?
I have 5 hypertables on each db, however in most instances, only 1 or 2 of them are used. I checked whether the numeric value for chunk id was repeated anywhere in other chunk names with the following
select * from timescaledb_information.chunks where chunk_name like '%_582_%';
but found only the 1 chunk, so I really can’t see where the duplicates come from.
With all the various issues I am having to deal with, I am starting to wonder if some of my database instances are just foobared.