Thread: BUG #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4

The following bug has been logged on the website:

Bug reference:      8128
Logged by:          Adrian Vondendriesch
Email address:      Adrian.Vondendriesch@credativ.de
PostgreSQL version: 9.1.9
Operating system:   Debian GNU/Linux 7.0
Description:        =


Hi,

while browsing on bugs.debian.org I saw the following bug:
'pg_upgradecluster
fails with "OLD used in query that is not in a rule"'
(http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=3D688960).

Because I don't found any reference to that report in pgsql-bugs I report it
by
my self.

Since I have tested it on my own I can confirm it is reproducible:

1. In a 8.4 cluster create a scheme called "old":
> CREATE SCHEME "old";

2. Create a test table:
> CREATE TABLE "old".test(id int);

3. insert some test data:
> INSERT INTO "old".test VALUES (1),(2),(3);

4. dump it with pg_dumpall (9.1):
/usr/lib/postgresql/9.1/bin/pg_dumpall -s > foo

This will produce the following error:
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  OLD used in query that is not
in a rule
> LINE 1: LOCK TABLE old.test IN ACCESS SHARE MODE
>                    ^
> pg_dump: The command was: LOCK TABLE old.test IN ACCESS SHARE MODE
> pg_dumpall: pg_dump failed on database "postgres", exiting

If pg_dumpall version 8.4 is used, the dump runs smoothly. This is because
pg_dump(all) in version 8.x uses quotes. (2013-04-30 16:24:49 CEST LOG:
statement: LOCK TABLE "old".test IN ACCESS SHARE MODE).

I think it's a bad idea to name a scheme 'old', but it's possible, so it
should be
supported. I saw no clue to resolve that issue in the pg_dump
documentation.

I'm running Debian Wheezy using the apt.postgresql.org repository.

Regards

    - Adrian

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



> Hi,
>
> while browsing on bugs.debian.org I saw the following bug:
> 'pg_upgradecluster fails with "OLD used in query that is not in a
> rule"' (http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=688960).
>
> Because I don't found any reference to that report in pgsql-bugs I
> report it by my self.
>
> Since I have tested it on my own I can confirm it is reproducible:
>
> 1. In a 8.4 cluster create a scheme called "old":
>> CREATE SCHEME "old";
>
> 2. Create a test table:
>> CREATE TABLE "old".test(id int);
>
> 3. insert some test data:
>> INSERT INTO "old".test VALUES (1),(2),(3);
>
> 4. dump it with pg_dumpall (9.1):
> /usr/lib/postgresql/9.1/bin/pg_dumpall -s > foo
>
> This will produce the following error:
>> pg_dump: SQL command failed pg_dump: Error message from server:
>> ERROR:  OLD used in query that is not
> in a rule
>> LINE 1: LOCK TABLE old.test IN ACCESS SHARE MODE ^ pg_dump: The
>> command was: LOCK TABLE old.test IN ACCESS SHARE MODE pg_dumpall:
>> pg_dump failed on database "postgres", exiting
>
> If pg_dumpall version 8.4 is used, the dump runs smoothly. This is
> because pg_dump(all) in version 8.x uses quotes. (2013-04-30
> 16:24:49 CEST LOG: statement: LOCK TABLE "old".test IN ACCESS SHARE
> MODE).

The error also happened with pg_dump in 9.3.

>
> I think it's a bad idea to name a scheme 'old', but it's possible,
> so it should be supported. I saw no clue to resolve that issue in
> the pg_dump documentation.
>
> I'm running Debian Wheezy using the apt.postgresql.org repository.
>
> Regards
>
> - Adrian
>
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRf+jBAAoJEJg+KbLX08eJV5YH/RLriFo3r9eDrbeO6UKwBhBq
RMYcs0GvqoDz2QtDRFiyjwl6s12rarTP//dK70oA587XBjWX3FN3k1BkHDrkrEFQ
XxP8hwtUKIYDYQgMx5OX8FrrLfmXcoQtkM7mISS3DxPb436Cv4+JVukZWDHVGmJj
nVKXwlB/spTSKMVcyi5Be8Gjf6b+7ArYfP6334nSOEznWrdbs+f9lWokTCOkSRW6
DxWC1ELbXkOTXK03qLXtieVGIrs8r6tUnAu/PeIDb3L5nsNZzs6FLfxkq6FckMDI
hVPnksW633eUQM/O1Jibr5o82rzvda2b/R/Soo1HjaChSGnnFH3HvodefS5cgTU=
=EIwd
-----END PGP SIGNATURE-----

Adrian.Vondendriesch@credativ.de writes:
> [ recent pg_dump fails against an 8.4 server if "old" is used as a name ]

Yeah.  The reason for this is that "old" was considered a reserved word
in 8.4 and before, but since 9.0 it is not reserved (indeed it isn't a
keyword at all anymore), so 9.0 and later pg_dump don't think they need
to quote it in commands.

De-reserving a keyword happens sufficiently rarely that it doesn't
really seem worth teaching pg_dump about such cases.  There is a
workaround, which is to use the --quote-all-identifiers switch when
dumping from a server with an incompatible idea of the set of reserved
keywords.

For the archives' sake, it might be worth noting that
--quote-all-identifiers was added in 9.1, which means that 9.0 pg_dump
is vulnerable to this problem and has no workaround.  That's a bit
annoying, but I rather doubt we'll take the trouble to back-port
--quote-all-identifiers into 9.0 at this point.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 01.05.2013 01:53, schrieb Tom Lane:
> Adrian.Vondendriesch@credativ.de writes:
>> [ recent pg_dump fails against an 8.4 server if "old" is used as
>> a name ]
>
> Yeah.  The reason for this is that "old" was considered a reserved
> word in 8.4 and before, but since 9.0 it is not reserved (indeed it
> isn't a keyword at all anymore), so 9.0 and later pg_dump don't
> think they need to quote it in commands.
>
> De-reserving a keyword happens sufficiently rarely that it doesn't
> really seem worth teaching pg_dump about such cases.  There is a
> workaround, which is to use the --quote-all-identifiers switch
> when dumping from a server with an incompatible idea of the set of
> reserved keywords.

I've tested the workaround and it works. But, because the error
originaly happens when upgrading from 8.4 to 9.1, I'm interested in a
way how I could force pg_upgrade to tell pg_dump to use this switch.

IMO: When there is such a switch, it should be on by default when
upgrading from 8.4 (or lower) to a version >= 9.1 to prevent this kind
of error. Did I miss something that would break in this scenario when
- --quote-all-identifiers is used by default while upgrading from <= 9.0?

>
> For the archives' sake, it might be worth noting that
> --quote-all-identifiers was added in 9.1, which means that 9.0
> pg_dump is vulnerable to this problem and has no workaround.
> That's a bit annoying, but I rather doubt we'll take the trouble to
> back-port --quote-all-identifiers into 9.0 at this point.
>
> regards, tom lane
>

Regards

    - Adrian

  * Englisch - erkannt
  * Englisch
  * Deutsch

  * Englisch
  * Deutsch

 <javascript:void(0);>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJRgQN7AAoJEJg+KbLX08eJo+QH/3WUV9qj2WTv1YaBRL2A0BxJ
0xwYUEKKZn++E7wU7hJSstY9vS7uQJEnSicQT1GcEZREYwJkR2vqMZUUKX1aB2KU
KnHLmRE3+4OWAIwNYIDOL00BMlAZ+brRkxWy7zERpMDkcsuo7v29rsWiwI0RggDf
U+Dt/JZaMfWCEobo9dBFyW1jGOkOs378JMltWV71JUEW2MfAh24KTL35IjlK47Vi
ZPYpFWsnLJDJlq33Lx7+GM9PIK/xRefXJZZLkjHaXcNMkTdu05nEsehuARcZt9Vz
OW1k2W7IcktzlLsUOz2fuLwSDSITQKT5HvLeubFJOoFwtQrVOIBhw2VGC5phOYE=
=nBRc
-----END PGP SIGNATURE-----