pgloader fails to parse sql file loaded by EXECUTE if it contains a USING placeholder ($1, $2, ..) in a single quoted string

[VisJ-zz]

Context

• MySQL (docker) to PostgreSQL (docker) migration
• pgloader docker image: dimitri/pgloader:latest
• MacOS

Description

When trying to define a load file, using AFTER LOAD EXECUTE to create some FUNCTIONS and PROCEDURES in the target database, the parsing of the load file fails with

LOG pgloader version "3.6.7~devel"
KABOOM!
END-OF-FILE: end of file on #<dynamic-extent STRING-INPUT-STREAM (unavailable) from "CREATE O...">
An unhandled error condition has been signalled:
   end of file on #<dynamic-extent STRING-INPUT-STREAM (unavailable) from "CREATE O...">

What I am doing here?

end of file on #<dynamic-extent STRING-INPUT-STREAM (unavailable) from "CREATE O...">

It seems that the issue arise when the loaded SQL file contains a dynamic SQL expression with a USING and the associated placeholders, in my case it is something like the following:

EXECUTE format(
    'SELECT id FROM %I WHERE id > $1 ',
        table_name
    ) USING value;

How to reproduce

The following minimalist example allows to reproduce the problem:

example.load

LOAD DATABASE
     FROM mysql://root:password@127.0.0.2/db
     INTO pgsql://postgres:password@127.0.0.2/db
AFTER LOAD EXECUTE 'after-load.sql'
;

with after-load.sql containing only

'$1'

If after-load.sql contains:

• $1 (without quote) => no issue
• '$q' (character instead of digit) => no issue

Question

Is there a way to work around it ? Or am I missing something ?

[GingerAdonis]

I checked the failure mode in pgferry, which is a spiritual successor to pgloader, and it does not appear to be affected by this specific issue. So you might want to give that a shot instead.

[VisJ-zz]

Thanks.
I'll have a look