Pgloader: Incompatibility with PostgreSQL 12 wrt def.adsrc

[adren]

After upgrading to PostgreSQL 12, when trying to load data with pgloader, some errors occurs:

`2019-10-05T20:07:26.073000Z FATAL Failed to prepare target PostgreSQL table.
2019-10-05T20:07:26.073000Z FATAL Database error 42703: la colonne def.adsrc n'existe pas
QUERY: -- params: table-type-name
-- including
-- filter-list-to-where-clause for including
-- excluding
-- filter-list-to-where-clause for excluding

select nspname, relname, c.oid, attname,
       t.oid::regtype as type,
       case when atttypmod > 0 then atttypmod - 4 else null end as typmod,
       attnotnull,
       case when atthasdef then def.adsrc end as default
  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
 where nspname !~ '^pg_' and n.nspname <> 'information_schema'
       and relkind in ('r', 'f', 'p')
       and ((n.nspname = 'public' and c.relname ~ '^ratings$'))
  order by nspname, relname, attnum;

2019-10-05T20:07:26.073000Z LOG report summary reset
table name errors rows bytes total time

---

        fetch          0          0                     0.004s
  before load          0          1                     0.007s

---

---

Sat Oct 5 22:07:26 CEST 2019
`

...leaving the database empty (only the creation of the tables was done from the pgloader scripts)

The OS is Debian buster with stock pgloader package:
pgloader version "3.5.2"
compiled with SBCL 1.4.9.debian
and
postgresql version 12.0-1.pgdg+1
taken from the apt.postgresql.org repository

[applied-machinelearning]

Unfortunately ran into the same issue with pgloader 3.6.1-1.pgdg100+1 from apt.postgresql.org.

[whitslack]

Use of the pg_attrdef.adsrc column has been discouraged for a long time, and the column was finally removed in PostgreSQL 12.

Quoting the PostgreSQL 11 documentation:

The adsrc field is historical, and is best not used, because it does not track outside changes that might affect the representation of the default value. Reverse-compiling the adbin field (with pg_get_expr for example) is a better way to display the default value.

The same page in the PostgreSQL 12 documentation does not mention the adsrc column.

[whitslack]

I'm sure this isn't the right way to work around this issue, but it allowed me to make progress.

diff --git a/src/pgsql/sql/list-all-columns.sql b/src/pgsql/sql/list-all-columns.sql
index 75f8a52..69c2b7b 100644
--- a/src/pgsql/sql/list-all-columns.sql
+++ b/src/pgsql/sql/list-all-columns.sql
@@ -7,8 +7,8 @@ with seqattr as
  (
    select adrelid, 
           adnum,
-          adsrc,
-          case when adsrc ~~ 'nextval'
+          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 '''([^'']+)'''
                     )
@@ -23,7 +23,7 @@ with seqattr as
                 else null
             end as typmod,
            attnotnull,
-           case when atthasdef then def.adsrc end as default,
+           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

[TWhidden]

ug, this was a gotcha for me today also. Don't feel like building from source today, but the patch is nice @whitslack - now just need it so apt-get built version works.

[remoteclient]

This issue also hit me today.Would be great to have an update to debian package as fast as possible. :)

[amatosg]

if pgloader has been already installed (with homebrew, in my case), is it possible to change the suggested lines by @whitslack ?

[gjvoosten]

@dimitri I can confirm that the patch suggested by @whitslack in https://github.com/dimitri/pgloader/issues/1034#issuecomment-547567845 does indeed work when migrating to PostgreSQL 12. Would you need a PR for that or can you make the update without it?

[P.S. Congrats on the [awesome new book](https://theartofpostgresql.com/); hopefully you now have some more time on your hands for pgloader. :wink: ]

[whitslack]

Don't just blindly merge my patch. It probably isn't right, and I'm sure there is a cleaner, more correct way to deal with PostgreSQL's removal of the pg_attrdef.adsrc column.

[WorldException]

Patched branch https://github.com/WorldException/pgloader/tree/issue_1034
Docker image from this branch quickes/pgloader
Thanks @whitslack!

[NelsonSR]

I'm sure this isn't the _right_ way to work around this issue, but it allowed me to make progress.

diff --git a/src/pgsql/sql/list-all-columns.sql b/src/pgsql/sql/list-all-columns.sql
index 75f8a52..69c2b7b 100644
--- a/src/pgsql/sql/list-all-columns.sql
+++ b/src/pgsql/sql/list-all-columns.sql
@@ -7,8 +7,8 @@ with seqattr as
  (
    select adrelid, 
           adnum,
-          adsrc,
-          case when adsrc ~~ 'nextval'
+          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 '''([^'']+)'''
                     )
@@ -23,7 +23,7 @@ with seqattr as
                 else null
             end as typmod,
            attnotnull,
-           case when atthasdef then def.adsrc end as default,
+           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

made the same change but the compiling stop with this error:

Fatal COMPILE-FILE-ERROR:
COMPILE-FILE-ERROR while
compiling #<CL-SOURCE-FILE "pgloader" "src" "sources" "db3" "db3-connection">
make: *** [Makefile:139: build/bin/pgloader] Error 1

[gjvoosten]

@NelsonSR The patch was against v3.6.1 I believe, not current HEAD. Checkout the tag v3.6.1 and try again (at least, that's what works for me)

[whitslack]

@NelsonSR @gjvoosten: Current HEAD doesn't build. Per mrPsycho,

master branch is not for production. try released versions

This is notwithstanding the fact that the main project README.md states that "you should always build from the current git HEAD."

[gjvoosten]

Yes, I was aware of that. :+1:

[NelsonSR]

@NelsonSR The patch was against v3.6.1 I believe, not current HEAD. Checkout the tag v3.6.1 and try again (at least, that's what works for me)

Thanks, it finally worked!

[dimitri]

Fixed in https://github.com/dimitri/pgloader/commit/8a13c02561fe030301045f9f50d94f523dd61b2c with a typo in the issue number ;-)