[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Bug#928942: marked as done (udd: cannot import current dump due to matview referencing function without schema)

Your message dated Sun, 28 Jun 2020 21:46:33 +0200
with message-id <20200628194633.GA1700@xanadu.blop.info>
and subject line Re: Bug#928942: udd: cannot import database dump into PostgreSQL ("function release_name(text) does not exist")
has caused the Debian Bug report #928942,
regarding udd: cannot import current dump due to matview referencing function without schema
to be marked as done.

This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.

(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact owner@bugs.debian.org
immediately.)


-- 
928942: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=928942
Debian Bug Tracking System
Contact owner@bugs.debian.org with problems
--- Begin Message --- 
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

--- End Message ---
--- Begin Message --- 
On 13/05/19 at 15:44 -0300, Leandro Doctors wrote:
> 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...

Hi,

I think this is fixed now (I dropped the view). Please let me know if it
isn't the case.

Thanks

Lucas

--- End Message ---
Reply to: