Pgloader: The value NIL is not of type PGLOADER.CATALOG:COLUMN

[drouarb]

• [x] pgloader --version

   pgloader version "3.5.ab2cadf"
   compiled with SBCL 1.3.1.debian

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

Yes

• [x] did you search for other similar issues?

Yes, nothing

• [x] how can I reproduce the bug?

Load this database into a new Postgres database:
https://mega.nz/#!PQsCACQK!Dos6ndyL3ng0hBVC0Zyg9mkmjQxlzkEpAphPgP9xAD4

load database
     from '/sqlite/plex.db'
     into postgresql://plex:[email protected]:5432/plex

 with include drop, create tables, create indexes, reset sequences
 excluding table names like 'spellfix_metadata_titles', 'spellfix_metadata_titles_vocab', 'spellfix_tag_titles', 'spellfix_tag_titles_vocab'

 set work_mem to '16MB', maintenance_work_mem to '512 MB';

• [x] pgloader output you obtain

The value NIL is not of type PGLOADER.CATALOG:COLUMN

Full debug log:
https://gist.github.com/drouarb/3d1911bb5ee453dbac7ba0540b456d5c

[dimitri]

Hi, is there a way that you can send me the SQLite file by email? that would help having more information from the backtrace and being able to debug the problem here... thanks.

[drouarb]

The sqlite file is in the mega link, it's a freshly created Plex Media Server database, the extension isn't .db but it's an sqlite file

[dimitri]

Thanks. I could reproduce the problem and I believed I fixed it. Please try from fresh source! Use either the classic make or the new make save to produce your pgloader binary in ./build/bin/pgloader.

[drouarb]

Now another error appeared:

2018-12-01T21:07:28.155000Z ERROR PostgreSQL Database error 42601: syntax error at or near "order"
QUERY: CREATE INDEX idx_61581_media_subscriptions_on_order ON media_subscriptions (order);

It looks like when creating an index, the column name should be quoted:.
When I do it with psql command:

psql (10.5, server 10.0)
Type "help" for help.

plex=# CREATE INDEX idx_61581_media_subscriptions_on_order ON media_subscriptions (order);
ERROR:  syntax error at or near "order"
LINE 1: ...media_subscriptions_on_order ON media_subscriptions (order);
                                                                ^
plex=# CREATE INDEX idx_61581_media_subscriptions_on_order ON media_subscriptions ("order");
CREATE INDEX
plex=#

[dimitri]

Hi again,

Seems I was too fast with the previous patch, done in between other things, sorry about that. The real fix is to apply our quoting rules to SQLite index column names, which we failed to do before. With the index entries column names quoted the same way as the table entries column names in the catalogs, now we can match them, and also creating the index doesn't fail.

FYI here's the file I've been using. Notice the workers = 2 in particular: one SQLite reader and one PostgreSQL writer at the same time and that's it. When using more than that (default for databases is 4), then I would hit SQLite locks which would prevent the load to go through. It might be the local version of SQLite that I have on my laptop's OS though, so I'm not making the reduced concurrency for SQLite the default at this point.

load database
     from plex.db
     into postgresql:///copy

 with include drop, create tables, create indexes, reset sequences,
      workers = 2

 excluding table names like 'spellfix_metadata_titles',
                            'spellfix_metadata_titles_vocab',
                            'spellfix_tag_titles',
                            'spellfix_tag_titles_vocab'

 set work_mem to '16MB', maintenance_work_mem to '512 MB';

[drouarb]

Thanks, it's perfectly working now