pgloader
pgloader copied to clipboard
dimitri
quote identifiers not applied to foreign key constraints from sqlite3
-
[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://[email protected]/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://[email protected]/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
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.)
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://[email protected]: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
