--skip-collations option fails with sqlite error for objects using non-default collations

[dverite]

Hi, When cloning a database with --skip-collations, if the database has views using the built-in "C" collation, pgcopydb 0.7 fails at Step 1 with this error:

2024-10-28 12:31:29.998 4714 ERROR  catalog.c:7577            Failed to execute statement: insert into s_coll(oid, collname, description, restore_list_name) values($1, $2, $3, $4)
2024-10-28 12:31:29.998 4714 ERROR  catalog.c:7578            [SQLite 19] constraint failed
2024-10-28 12:31:29.998 4714 ERROR  catalog.c:7550            Failed to execute SQLite query, see above for details

The issue can be reproduced with a simple database containing only these two objects:

create view v as select 'foo' collate "C";
create view v2 as select * from v;

Cloning this database results in the following error:

$ pgcopydb clone  --skip-collations --source=postgres:///testcopy_src --target=postgres:///testcopy_dest
16:13:16.298 36623 INFO   Running pgcopydb version 0.17-1.pgdg20.04+1 from "/usr/bin/pgcopydb"
16:13:16.308 36623 INFO   [SOURCE] Copying database from "postgres:///testcopy_src?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
16:13:16.308 36623 INFO   [TARGET] Copying database into "postgres:///testcopy_dest?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
16:13:16.374 36623 INFO   Using work dir "/tmp/pgcopydb"
16:13:16.375 36632 INFO   STEP 1: fetch source database tables, indexes, and sequences
16:13:16.392 36632 INFO   Fetched information for 0 tables (including 0 tables split in 0 partitions total), with an estimated total of 0 tuples and 0 B on-disk
16:13:16.394 36632 INFO   Fetched information for 0 indexes (supporting 0 constraints)
16:13:16.395 36632 INFO   Fetching information for 0 sequences
16:13:16.400 36632 ERROR  Failed to execute statement: insert into s_coll(oid, collname, description, restore_list_name) values($1, $2, $3, $4)
16:13:16.400 36632 ERROR  [SQLite 19] constraint failed
16:13:16.400 36632 ERROR  Failed to execute SQLite query, see above for details
16:13:16.400 36632 ERROR  Failed to list non-default collations in use in database
16:13:16.400 36632 ERROR  Failed to clone source database, see above for details
16:13:16.525 36623 ERROR  clone process 36632 has terminated [6]

The problem I see in pgcopydb is that the s_coll table has a unique index on oid, but schema_list_collations() runs a query that produces duplicates in colloid. In the case of the above database with its two views, the results of that query are:

 colloid | collname |     pg_describe_object     |        format         
---------+----------+----------------------------+-----------------------
     950 | C        | column ?column? of view v2 | pg_catalog C postgres
     950 | C        | column ?column? of view v  | pg_catalog C postgres

So if you have any particular collation referred to more than once in the source database, storing these results into s_coll cannot work.

s_coll is created in catalog.c as:

    "create table s_coll("
    "  oid integer primary key, collname text, description text, "
    "  restore_list_name text"
    ")",

[dimitri]

Hi @dverite; thanks for this bug report. Two things come to mind:

1. why use pgcopydb v0.7? did you try with the current release (the only one supported)?
2. can you contribute a fixed SQL query?

[qfritz]

1. This is a typo, we used v0.17 🙇