Pgloader: Column pg_attrdef.adsrc removed in Postgres v12

[barbolani]

• [ X] pgloader --version

  pgloader version "3.6.1"
  compiled with SBCL 1.4.5.debian
  

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

Compiled from source on Ubuntu 18.04

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

Yes

• [ X] how can I reproduce the bug?

Attempt migration from any source to a PostgreSQL v12 instance

Changing local-projects/pgloader-3.6.1/src/pgsql/sql/list-all-columns.sql file to this makes it work with PostgreSQL v12, but it could break under older PgSQL versions, so I'm not submitting a patch from now.

```

-- params: table-type-name
-- including
-- filter-list-to-where-clause for including
-- excluding
-- filter-list-to-where-clause for excluding
with seqattr as
(
select adrelid,
adnum,
pg_get_expr(d.adbin, d.adrelid) as adsrc,
case when pg_get_expr(d.adbin, d.adrelid) ~~ 'nextval%'
then substring(pg_get_expr(d.adbin, d.adrelid)
from '''([^'']+)'''
)
else null
end as seqname
from pg_attrdef d
)
select nspname, relname, c.oid, attname,
t.oid::regtype as type,
case when atttypmod > 0
then substring(format_type(t.oid, atttypmod) from '\d+(?:,\d+)?')
else null
end as typmod,
attnotnull,
case when atthasdef then pg_get_expr(def.adbin, def.adrelid) end as default,
case when s.seqname is not null then 'auto_increment' end as extra
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_attribute a on c.oid = a.attrelid
join pg_type t on t.oid = a.atttypid and attnum > 0
left join pg_attrdef def on a.attrelid = def.adrelid
and a.attnum = def.adnum
and a.atthasdef
left join seqattr s on def.adrelid = s.adrelid
and def.adnum = s.adnum

 where nspname !~~ '^pg_' and n.nspname <> 'information_schema'
       and relkind in (~{'~a'~^, ~})
       ~:[~*~;and (~{~a~^~&~10t or ~})~]
       ~:[~*~;and (~{~a~^~&~10t and ~})~]

order by nspname, relname, attnum;
```

[remoteclient]

Related with #1034

[barbolani]

Created a fork with a 3.6.1 branch that builds cleanly https://github.com/barbolani/pgloader branch feature/pg12_compat

[SerhiyRomanov]

@barbolani Could you prepare merge request for fixing this issue? Seems like it's still are no fix in master branch here.

[dimitri]

Fixed in https://github.com/dimitri/pgloader/commit/8a13c02561fe030301045f9f50d94f523dd61b2c.

[Julien0002]

when will be release a version including this blocking defect?

[dimitri]

As soon as we can make that happen, @Julien0002. Can you help? Just telling us that you tried the bug fix and that it indeed fix your situation without introducing new bugs would go a long way.

[Julien0002]

As soon as we can make that happen, @Julien0002. Can you help? Just telling us that you tried the bug fix and that it indeed fix your situation without introducing new bugs would go a long way.

I'am facing those issues (ia m on macOS Catalina 10.15.3):

• building from master using source make me lost in debugger because of many NAME-CONFLICT issues
• building using 'brew install --HEAD pgloader', failed with error :
  Your branch is up to date with 'origin/master'. HEAD is now at 30376b2 Add support for "datetime2" data type in MS SQL. ==> Patching ==> Applying 6701fca5e5ed0fa1572b8d7922b4afcff6657f03.patch patching file src/pgsql/sql/list-all-columns.sql Hunk #1 FAILED at 7. Hunk #2 FAILED at 23. 2 out of 2 hunks FAILED -- saving rejects to file src/pgsql/sql/list-all-columns.sql.rej Error: Failure while executing; ``patch -g 0 -f -p1 -i /private/tmp/pgloader--patch-20200302-61647-qyze1/6701fca5e5ed0fa1572b8d7922b4afcff6657f03.patch`` exited with 1.

[Julien0002]

hi, building from master on centos and selecting always 'TAKE-NEW', the builded pgloader (3.6.30376b2) work fine for me.

please find under configuration use for the test :
LOAD CSV FROM '{{VAR_LOADING_PATH}}/{{VAR_FILE_IN}}' HAVING FIELDS ( ... ) INTO postgresql:///... TARGET COLUMNS ( ... WITH skip header = 0, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ';', workers = 10, concurrency = 5;

[dimitri]

For what we know it should be all ok now with release 3.6.2.

[Julien0002]

yes. I confirm. really thanks.