[SQLite 100] another row available

[nakatlam]

Migration fails to filter already processed constraints, resulting errors during pg_restore of post-filtered.list

2024-04-24 14:06:24.496 42 SQLite catalog.c:7248            [SQLite]   select pid, start_time_epoch, done_time_epoch, duration     from summary    where indexoid = $1 or conoid = $2
2024-04-24 14:06:24.496 42 SQLite catalog.c:7627            [SQLite] 16978, 16978
2024-04-24 14:06:24.496 42 ERROR  catalog.c:7395            Failed to execute statement:   select pid, start_time_epoch, done_time_epoch, duration     from summary    where indexoid = $1 or conoid = $2
2024-04-24 14:06:24.496 42 ERROR  catalog.c:7396            [SQLite 100] another row available
2024-04-24 14:06:24.496 42 ERROR  catalog.c:7321            Failed to execute SQLite query, see above for details
2024-04-24 14:06:24.496 42 SQLite catalog.c:7248            [SQLite]   select oid, restore_list_name, kind     from filter    where oid = $1
2024-04-24 14:06:24.496 42 SQLite catalog.c:7627            [SQLite] 16978
2024-04-24 14:06:24.496 42 SQLite catalog.c:7248            [SQLite]   select oid, restore_list_name, kind     from filter    where restore_list_name = $1
2024-04-24 14:06:24.496 42 SQLite catalog.c:7627            [SQLite] public t_data_file t_data_file_pkey sa

The SQLite has got entries with same OIDs between indexoid and conoids

sqlite> SELECT * FROM summary WHERE indexoid = 16978 OR conoid = 16978;
70||||16978|1713967577|1713967577|43||ALTER TABLE public.t_data_file ADD CONSTRAINT t_data_file_pkey PRIMARY KEY USING INDEX t_data_file_pkey
68|||16978||1713967583|1713967583|34||CREATE UNIQUE INDEX t_thumbnail_pkey ON testtenant01.t_thumbnail USING btree (id);
sqlite> 
sqlite> select * from s_index where oid=16978;
16978|testtenant01.t_thumbnail_pkey|testtenant01|t_thumbnail_pkey|testtenant01 t_thumbnail_pkey sa|16974|1|1|id|CREATE UNIQUE INDEX t_thumbnail_pkey ON testtenant01.t_thumbnail USING btree (id)
sqlite> select * from s_constraint where oid=16978;
16978|t_data_file_pkey|16449|0|0|PRIMARY KEY (id)

[nakatlam]

Also, when SQLite returns multiple rows for some reason, pgcopydb should be able to consume those.

[dimitri]

Also, when SQLite returns multiple rows for some reason, pgcopydb should be able to consume those.

Oh, you would prefer a silent bug rather than an “explicit” error message? Mmm, I wouldn't. That said the situation is not ideal and should be improved/fixed. In that instance I'm very curious about how we get the same Postgres OID for the index and the constraint. It's not even the supporting index for the constraint.

Do you have a source schema definition that would reproduce the problem? Could you look into the Postgres source database and see if you can find the OID 16978 in the Postgres catalogs pg_index, pg_class, and pg_constraint?

[nakatlam]

Well, I too dont prefer silent bug. What I meant was, as that step was about filtering the already processed constraints, I was saying if we can remove those constrains we got in SQLite response. Not sure exact logic in pgcopydb, so you can ignore this suggestion if that doesn't fit in the logic.

[nakatlam]

Hi @dimitri ,

got access to server pg_index, pg_class, pg_constraint catalog tables. Below are the results for OID 16978

pg_constraint

oid | conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --
16978 | t_data_file_pkey | 16476 | p | FALSE | FALSE | TRUE | 16438 | 0 | 16449 | 0 | 0 |   |   |   | TRUE | 0 | TRUE | {1} | NULL | NULL | NULL | NULL | NULL | NULL

pg_index

indexrelid | indrelid | indnatts | indnkeyatts | indisunique | indisprimary | indisexclusion | indimmediate | indisclustered | indisvalid | indcheckxmin | indisready | indislive | indisreplident | indkey | indcollation | indclass | indoption | indexprs | indpred
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --
16978 | 16974 | 1 | 1 | TRUE | TRUE | FALSE | TRUE | FALSE | TRUE | FALSE | TRUE | TRUE | FALSE | 1 | 0 | 3124 | 0 | NULL | NULL

pg_class

oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | --
16978 | t_thumbnail_pkey | 16477 | 0 | 0 | 16388 | 403 | 16978 | 0 | 1 | 0 | 0 | 0 | FALSE | FALSE | p | i | 1 | 0 | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | n | FALSE | 0 | 0 | 0 | NULL | NULL | NULL