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

-----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-----

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

-----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-----

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Apr 30, 2013 at 07:53:27PM -0400, Tom Lane wrote:

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.

According to SQL:2003 and SQL:2008 (and the draft standard, if that
matters) in section 5.2 of Foundation, both NEW and OLD are reserved
words, so we're going to need to re-reserve them to comply.

Sadly, this will cause problems for people who have tables with those
names, but we've introduced incompatibilities (in 8.3, e.g.) that hit
a much bigger part of our user base much harder than this. When we do
re-reserve, we'll need to come up with a migration path.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 05/01/2013 04:26 PM, David Fetter wrote:

On Tue, Apr 30, 2013 at 07:53:27PM -0400, Tom Lane wrote:

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.

According to SQL:2003 and SQL:2008 (and the draft standard, if that
matters) in section 5.2 of Foundation, both NEW and OLD are reserved
words, so we're going to need to re-reserve them to comply.

erm? I don't really see why we have any need to reserve something _on
purpose_ when there is no technical reason to do so...

Sadly, this will cause problems for people who have tables with those
names, but we've introduced incompatibilities (in 8.3, e.g.) that hit
a much bigger part of our user base much harder than this. When we do
re-reserve, we'll need to come up with a migration path.

so why again do we want to create an(other) incompatibility hazard?

Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David Fetter <david@fetter.org> writes:

According to SQL:2003 and SQL:2008 (and the draft standard, if that
matters) in section 5.2 of Foundation, both NEW and OLD are reserved
words, so we're going to need to re-reserve them to comply.

We don't and won't. There are very many other keywords that are less
reserved in Postgres than in the spec; this is a good thing.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, May 01, 2013 at 11:12:28AM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

According to SQL:2003 and SQL:2008 (and the draft standard, if
that matters) in section 5.2 of Foundation, both NEW and OLD are
reserved words, so we're going to need to re-reserve them to
comply.

We don't and won't.

Not so fast or so definite, if you please.

I've got a GSoC project in that implements things with both of these
keywords, and doubtless others will use other keywords either this
coming (9.4) cycle or in a later one.

If you want to have a discussion about the timing, that is a perfectly
reasonable discussion to have. Peremptorily saying, "don't and won't"
is not a great way to operate, however tempting it may be for you.

There is a case to be made, and I'm making it here, for pre-reserving
all the keywords and erroring out with "Feature not implemented" for
those not yet implemented. This would keep us, and more importantly
our user base, from wondering when the next random change to the SQL
language would affect them.

I'd suggest doing this over about 3 releases in the sense of warning
people at the appropriate juncture--I'm guessing at least CREATE,
ALTER, pg_dump(all) and pg_upgrade would be involved. Three releases
is just a suggestion intended to start a discussion.

There are very many other keywords that are less reserved in
Postgres than in the spec; this is a good thing.

How is it a good thing? Help me understand.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 05/01/2013 06:14 PM, David Fetter wrote:

On Wed, May 01, 2013 at 11:12:28AM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

According to SQL:2003 and SQL:2008 (and the draft standard, if
that matters) in section 5.2 of Foundation, both NEW and OLD are
reserved words, so we're going to need to re-reserve them to
comply.

We don't and won't.

Not so fast or so definite, if you please.

I've got a GSoC project in that implements things with both of these
keywords, and doubtless others will use other keywords either this
coming (9.4) cycle or in a later one.

past history has shown that this is relatively rare and almost always it
was possible to find a way around - not sure why we need to panic in
advance?

If you want to have a discussion about the timing, that is a perfectly
reasonable discussion to have. Peremptorily saying, "don't and won't"
is not a great way to operate, however tempting it may be for you.

There is a case to be made, and I'm making it here, for pre-reserving
all the keywords and erroring out with "Feature not implemented" for
those not yet implemented. This would keep us, and more importantly
our user base, from wondering when the next random change to the SQL
language would affect them.

as per the discussion on IRC - this would break applications left and
right for no real reason and no good, and I don't think hypothetical
features that have not even fully discused warrant anything like that.
Also this would be an uphill battle for no good (ie every few years when
a new spec comes out we break apps for a feature we might geht 10 years
later?)

I'd suggest doing this over about 3 releases in the sense of warning
people at the appropriate juncture--I'm guessing at least CREATE,
ALTER, pg_dump(all) and pg_upgrade would be involved. Three releases
is just a suggestion intended to start a discussion.

There are very many other keywords that are less reserved in
Postgres than in the spec; this is a good thing.

How is it a good thing? Help me understand.

why is breaking random applications or making it harder for people to
migrate from other databases without any reason a good thing?

Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David Fetter <david@fetter.org> writes:

On Wed, May 01, 2013 at 11:12:28AM -0400, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

According to SQL:2003 and SQL:2008 (and the draft standard, if
that matters) in section 5.2 of Foundation, both NEW and OLD are
reserved words, so we're going to need to re-reserve them to
comply.

We don't and won't.

Not so fast or so definite, if you please.

Yeah, I should have said "we don't and won't reserve keywords merely
because the SQL spec lists them as reserved".

I've got a GSoC project in that implements things with both of these
keywords, and doubtless others will use other keywords either this
coming (9.4) cycle or in a later one.

That's fine. When somebody submits a feature patch that requires
reserving a formerly-not-reserved word, we'll have a discussion about
whether the feature is worth the risk of breaking applications and
whether there isn't a way to avoid fully reserving the word. Frequently
it's possible to avoid that with some grammar rearrangement. We've
sweated blood before to avoid reserving common words, and I'm sure we
will continue that approach.

There is a case to be made, and I'm making it here, for pre-reserving
all the keywords and erroring out with "Feature not implemented" for
those not yet implemented. This would keep us, and more importantly
our user base, from wondering when the next random change to the SQL
language would affect them.

This is complete nonsense, because

(a) every time the SQL committee comes out with a new revision, there
are new reserved words, many of which are for features that Postgres
may not have for years, if ever. You are simply proposing that we break
applications on the committee's timetable rather than our own, and
perhaps break them needlessly.

(b) it's often possible to implement the spec syntax without reserving
the word, or without reserving it completely. We always do this if it's
feasible. For example, there are a lot of functions whose names are
reserved words according to the spec, but Postgres just thinks they're
ordinary functions. It would serve no purpose to make them really
reserved; in fact, it would make life harder for us as well as our
users. Even where it does make it harder for us, we've always judged
that not breaking existing applications was worth a fair amount of pain.

There are very many other keywords that are less reserved in
Postgres than in the spec; this is a good thing.

How is it a good thing? Help me understand.

Because it avoids breaking applications that had been using those
words as object names.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, May 1, 2013 at 6:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

This is complete nonsense, because

David's argument is pretty clearly not nonsense. I think they're valid
well reasoned arguments. It's just that the evidence is mixed and on
balance leans towards not unnecessarily reserving keywords. Fwiw we've
done it at least once in the past (RECURSIVE maybe?) and I recall it
didn't actually go so well in the end. Either it took multiple
revisions before the preemptively reserved word was useful or it
didn't end up needing to be reserved as we expected, I forget.

If the spec were more static and we had a real expectation of reaching
completeness on some fixed timetable then I think David would be
pretty solidly correct. I don't think anyone would stand for a C
compiler that let users use reserved words in some contexts as
identifiers without a warning by default. It would be doing a
disservice to users who would one day try to compile their code on
another compiler or be surprised that their identifiers couldn't be
used in other contexts.

If we could do a competent job of it it would be nice to support a
mode that warned users when they used non-standard syntaxes. But we
can't. If we warned about keywords that are reserved in the standard
but not known by postgres that would be hardly scratching the surface.
It would do nothing but give users a false sense of security and it
would be pretty awkward to implement even that, nevermind something
that actually reached a useful level of completeness.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers