quote identifiers not applied to foreign key constraints from sqlite3

jinnatar commented 1 year ago

[x] pgloader --version

%> pgloader-bundle-3.6.9/bin/pgloader --version
pgloader version "3.6.9"
compiled with SBCL 2.1.1.debian

[x] did you test a fresh compile from the source tree?

%> build/bin/pgloader --version
pgloader version "3.6.999791d"
compiled with SBCL 2.1.1.debian

[x] did you search for other similar issues?

[x] how can I reproduce the bug? Dump from sqlite db.sqlite3:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE pages (id int PRIMARY KEY, authorId int, FOREIGN KEY(authorId) REFERENCES users("authorId"));
INSERT INTO pages VALUES(1,42);
CREATE TABLE users (id bigint PRIMARY KEY, name text NOT NULL, authorId int NOT NULL);
INSERT INTO users VALUES(1,'demo',42);
COMMIT;

[x] pgloader output you obtain

%> PGPASSWORD=foobar build/bin/pgloader --with "quote identifiers" sqlite://db.sqlite3 pgsql://postgres@postgres.service.consul/demo
2023-05-08T11:50:21.016000+03:00 LOG pgloader version "3.6.999791d"
2023-05-08T11:50:21.080002+03:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///vol/home/src/3rd-party-forks/pgloader/db.sqlite3 {10074EE4C3}>
2023-05-08T11:50:21.080002+03:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@postgres.service.consul/demo {1007644B63}>
2023-05-08T11:50:21.600015+03:00 ERROR PostgreSQL Database error 42703: column "authorid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "pages" ADD FOREIGN KEY(authorId) REFERENCES "users"(authorId) ON UPDATE NO ACTION ON DELETE NO ACTION
2023-05-08T11:50:21.608015+03:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0                     0.000s
        fetch meta data          0          5                     0.064s
         Create Schemas          0          0                     0.000s
       Create SQL Types          0          0                     0.008s
          Create tables          0          4                     0.028s
         Set Table OIDs          0          2                     0.008s
-----------------------  ---------  ---------  ---------  --------------
                "pages"          0          1     0.0 kB          0.060s
                "users"          0          1     0.0 kB          0.056s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.060s
 Index Build Completion          0          2                     0.136s
         Create Indexes          0          2                     0.028s
        Reset Sequences          0          0                     0.064s
           Primary Keys          0          0                     0.000s
    Create Foreign Keys          1          0                     0.004s
        Create Triggers          0          0                     0.000s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓          2     0.0 kB          0.292s

[x] data that is being loaded, if relevant
[x] How the data is different from what you expected, if relevant

jinnatar commented 1 year ago

Picking out the relevant error:

2023-05-08T11:50:21.600015+03:00 ERROR PostgreSQL Database error 42703: column "authorid" referenced in foreign key constraint does not exist

The query is: ALTER TABLE "pages" ADD FOREIGN KEY(authorId) REFERENCES "users"(authorId) ON UPDATE NO ACTION ON DELETE NO ACTION

Instead it should be: ALTER TABLE "pages" ADD FOREIGN KEY("authorId") REFERENCES "users"("authorId") ON UPDATE NO ACTION ON DELETE NO ACTION

(That will cause other errors to pop up, but that's just an artefact of the simplistic repro schema.)

ralgar commented 11 months ago

Were you able to figure out a way around this @Artanicus? I'm struggling with the exact same issue, sqlite3 -> postgres.

EDIT: I found this open PR which fixes the issue #1531

pgloader version: 3.6.7~devel (I'm not sure why it says this, I am using Docker tag 3.6.9, and I've also tried latest.

pgloader output:

2023-11-16T01:24:52.033000Z LOG pgloader version "3.6.7~devel"
2023-11-16T01:24:52.319999Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///data/db.sqlite3 {1007F30413}>
2023-11-16T01:24:52.320999Z LOG Migrating into #<PGSQL-CONNECTION pgsql://prowlarr@mediadb-primary.media-server.svc:5432/jellyseerr {1007F31943}>
2023-11-16T01:24:53.888996Z ERROR PostgreSQL Database error 42703: column "requestid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "season_request" ADD FOREIGN KEY(requestId) REFERENCES "media_request"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.891996Z ERROR PostgreSQL Database error 42703: column "mediaid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "season" ADD FOREIGN KEY(mediaId) REFERENCES "media"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.894996Z ERROR PostgreSQL Database error 42703: column "userid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "user_push_subscription" ADD FOREIGN KEY(userId) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.897996Z ERROR PostgreSQL Database error 42703: column "modifiedbyid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "issue" ADD FOREIGN KEY(modifiedById) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.900996Z ERROR PostgreSQL Database error 42703: column "createdbyid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "issue" ADD FOREIGN KEY(createdById) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.903996Z ERROR PostgreSQL Database error 42703: column "mediaid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "issue" ADD FOREIGN KEY(mediaId) REFERENCES "media"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.905996Z ERROR PostgreSQL Database error 42703: column "issueid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "issue_comment" ADD FOREIGN KEY(issueId) REFERENCES "issue"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.908996Z ERROR PostgreSQL Database error 42703: column "userid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "issue_comment" ADD FOREIGN KEY(userId) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.911996Z ERROR PostgreSQL Database error 42703: column "userid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "user_settings" ADD FOREIGN KEY(userId) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.914996Z ERROR PostgreSQL Database error 42703: column "modifiedbyid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "media_request" ADD FOREIGN KEY(modifiedById) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE SET NULL
2023-11-16T01:24:53.918996Z ERROR PostgreSQL Database error 42703: column "requestedbyid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "media_request" ADD FOREIGN KEY(requestedById) REFERENCES "user"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.922996Z ERROR PostgreSQL Database error 42703: column "mediaid" referenced in foreign key constraint does not exist
QUERY: ALTER TABLE "media_request" ADD FOREIGN KEY(mediaId) REFERENCES "media"(id) ON UPDATE NO ACTION ON DELETE CASCADE
2023-11-16T01:24:53.989996Z LOG report summary reset
              table name     errors       rows      bytes      total time
------------------------  ---------  ---------  ---------  --------------
                   fetch          0          0                     0.000s
         fetch meta data          0         48                     0.149s
          Create Schemas          0          0                     0.001s
        Create SQL Types          0          0                     0.012s
           Create tables          0         26                     0.189s
          Set Table OIDs          0         13                     0.014s
------------------------  ---------  ---------  ---------  --------------
            "migrations"          0         35     1.8 kB          0.093s
               "session"          0          0                     0.100s
                "season"          0          0                     0.193s
                  "user"          0          1     0.2 kB          0.293s
                 "issue"          0          0                     0.347s
         "user_settings"          0          0                     0.408s
       "discover_slider"          0         12     0.7 kB          0.536s
        "season_request"          0          0                     0.026s
                 "media"          0          0                     0.069s
"user_push_subscription"          0          0                     0.230s
         "issue_comment"          0          0                     0.202s
         "media_request"          0          0                     0.417s
             "watchlist"          0          0                     0.401s
------------------------  ---------  ---------  ---------  --------------
 COPY Threads Completion          0          4                     0.569s
          Create Indexes          0         23                     0.234s
  Index Build Completion          0         23                     0.212s
         Reset Sequences          0         12                     0.089s
            Primary Keys          0         12                     0.038s
     Create Foreign Keys         12          0                     0.036s
         Create Triggers          0          0                     0.002s
        Install Comments          0          0                     0.000s
              after load          0          2                     0.056s
------------------------  ---------  ---------  ---------  --------------
       Total import time          ✓         48     2.7 kB          1.236s

dimitri / pgloader

quote identifiers not applied to foreign key constraints from sqlite3 #1486