Bug#928942: udd: cannot import database dump into PostgreSQL ("function release_name(text) does not exist")
Package: qa.debian.org
Severity: grave
Justification: causes non-serious data loss
Dear Maintainer,
(Sorry for any format error in this bug. I tried to use "reportbug",
but it gave me so many problems I gave up and ended up creating this
manually...)
I am trying to create a replica from the public dump of the UDD on my
local PostgreSQL server.
As recommended in the UDD README, I am following the instructions from
https://salsa.debian.org/qa/udd/blob/master/vagrant/provision.sh
Specifically, I do as follows.
0) Install the appropriate dependencies, per the same provision.sh file:
(See details at the bottom of this message.)
Apart from avoiding to install the unneeded 'apache2', the only
relevant difference is the PostgreSQL version - I use Buster's version
11, instead of Stretch's 9.6. However, I doubt this would affect
this...
apt install -y apache2 postgresql postgresql-plperl-9.6
postgresql-9.6-debversion ruby-debian ruby-oj rsync python-yaml
python-psycopg2 ruby-pg ruby-sequel-pg
1) Start the PostgreSQL service
sudo service postgresql start
2) Create the 'udd' database (If I don't, I cannot restore the dump)
sudo -u postgres createdb -T template0 -E SQL_ASCII udd
3) Add 'debversions' extension to the database (If I don't, I get a
lot of errors)
psql udd -c 'create extension debversion'
4) Make my local user the owner of the database
sudo -u postgres psql -c 'alter database udd owner to allentiak'
5) Grant permissions (as the provision script)
sudo -u postgres psql udd -c 'GRANT usage on schema public to public'
sudo -u postgres psql udd -c 'grant select on all tables in schema
public to public'
6) Finally, I import the dump
sudo -u postgres pg_restore -j 8 --no-owner -v -C -d postgres
udd.dump.2019.05.06.dump
After step 6, I get the following error:
```
pg_restore: launching item 4101 MATERIALIZED VIEW DATA tmp
pg_restore: creating MATERIALIZED VIEW DATA "public.tmp"
pg_restore: [archiver (db)] Error from TOC entry 4101; 0 91852869
MATERIALIZED VIEW DATA tmp udd
pg_restore: [archiver (db)] could not execute query: ERROR:
function release_name(text) does not exist
LINE 1: SELECT release_name($1)
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT release_name($1)
CONTEXT: PL/pgSQL function public.bugs_rt_affects_dist(text) line
4 during statement block local variable initialization
Command was: REFRESH MATERIALIZED VIEW public.tmp;
[...]
pg_restore: finished item 4101 MATERIALIZED VIEW DATA tmp
pg_restore: finished main parallel loop
WARNING: errors ignored on restore: 4
```
On the one hand, it looks as if the 'release_name(text)' function was
used before def (if such a thing is possible in Pg/SQL)... However,
this dupm was created automatically...
On the other hand, I am aware of #899307, involving 'SELECT' statemets
when using 'ruby-sequel-pg'. However, there are plenty of SELECT
statements in the dump file, and only one of them seems to fail...
-- System Information:
Debian Release: buster/sid
APT prefers unstable
APT policy: (500, 'unstable'), (500, 'testing')
Architecture: amd64 (x86_64)
Foreign Architectures: i386
Kernel: Linux 4.19.0-4-amd64 (SMP w/8 CPU cores)
Kernel taint flags: TAINT_PROPRIETARY_MODULE, TAINT_OOT_MODULE,
TAINT_UNSIGNED_MODULE
Locale: LANG=en_IE.UTF-8, LC_CTYPE=en_IE.UTF-8 (charmap=UTF-8),
LANGUAGE=en_IE.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled
Versions of packages postgresql depends on:
ii postgresql-11 11.2-2
postgresql recommends no packages.
Versions of packages postgresql suggests:
pn postgresql-doc <none>
Versions of packages postgresql-11 depends on:
ii debconf [debconf-2.0] 1.5.71
ii libc6 2.28-10
ii libgcc1 1:8.3.0-7
ii libgssapi-krb5-2 1.17-2
ii libicu63 63.1-6
ii libldap-2.4-2 2.4.47+dfsg-3
ii libllvm7 1:7.0.1-8
ii libpam0g 1.3.1-5
ii libpq5 11.2-2
ii libselinux1 2.8-1+b1
ii libssl1.1 1.1.1b-2
ii libstdc++6 8.3.0-7
ii libsystemd0 241-3
ii libuuid1 2.33.1-0.1
ii libxml2 2.9.4+dfsg1-7+b3
ii libxslt1.1 1.1.32-2
ii locales 2.28-10
ii postgresql-client-11 11.2-2
ii postgresql-common 200+deb10u1
ii ssl-cert 1.0.39
ii tzdata 2019a-1
ii zlib1g 1:1.2.11.dfsg-1
Versions of packages postgresql-11 recommends:
ii sysstat 12.0.3-2
postgresql-11 suggests no packages.
Versions of packages postgresql-11-debversion depends on:
ii libapt-pkg5.0 1.8.1
ii libc6 2.28-10
ii postgresql-11 11.2-2
postgresql-11-debversion recommends no packages.
postgresql-11-debversion suggests no packages.
Versions of packages python-psycopg2 depends on:
ii libc6 2.28-10
ii libpq5 11.2-2
ii python 2.7.16-1
Versions of packages python-psycopg2 recommends:
ii python-egenix-mxdatetime 3.2.9-1
Versions of packages python-psycopg2 suggests:
pn python-psycopg2-doc <none>
Versions of packages python-yaml depends on:
ii libc6 2.28-10
ii libyaml-0-2 0.2.1-1
ii python 2.7.16-1
python-yaml recommends no packages.
python-yaml suggests no packages.
Versions of packages ruby-debian depends on:
ii libapt-pkg5.0 1.8.1
ii libc6 2.28-10
ii libgcc1 1:8.3.0-7
ii libgmp10 2:6.1.2+dfsg-4
ii libruby2.5 2.5.5-1
ii libstdc++6 8.3.0-7
ii ruby 1:2.5.1
ruby-debian recommends no packages.
ruby-debian suggests no packages.
Versions of packages ruby-oj depends on:
ii libc6 2.28-10
ii libgmp10 2:6.1.2+dfsg-4
ii libruby2.5 2.5.5-1
ii ruby 1:2.5.1
ruby-oj recommends no packages.
Versions of packages ruby-oj suggests:
pn ruby-activesupport <none>
Versions of packages ruby-pg depends on:
ii libc6 2.28-10
ii libgmp10 2:6.1.2+dfsg-4
ii libpq5 11.2-2
ii libruby2.5 2.5.5-1
ii ruby 1:2.5.1
ruby-pg recommends no packages.
Versions of packages ruby-pg suggests:
pn doc-base <none>
ii libjs-jquery 3.3.1~dfsg-3
Versions of packages ruby-sequel depends on:
ii ruby 1:2.5.1
ii ruby-json 2.1.0+dfsg-2+b1
Versions of packages ruby-sequel recommends:
ii ruby-sequel-pg 1.6.16-1+b2
ruby-sequel suggests no packages.
Versions of packages postgresql-plperl-11 depends on:
ii libc6 2.28-10
ii libperl5.28 5.28.1-6
ii perl 5.28.1-6
ii postgresql-11 11.2-2
postgresql-plperl-11 recommends no packages.
postgresql-plperl-11 suggests no packages.
-- debconf information:
postgresql-11/postrm_purge_data: true
tags udd + buster sid
thanks
Reply to: