Re: PSQL commands not backwards-compatible

Folks,

Discovered some problems using 8.1 psql to connect to an 8.0 server:

test_stats=# \l
ERROR: relation "pg_catalog.pg_roles" does not exist
test_stats=# \dt
ERROR: relation "pg_catalog.pg_roles" does not exist
test_stats=# \dv
ERROR: relation "pg_catalog.pg_roles" does not exist

This is from CVS, the day before the beta release. Ignore me if it's
already been fixed.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

This isn't a bug, it's a feature request. We've never had backwards
compatibility of psql backslash commands.

There was some talk just a day or two ago about starting to support
that beginning in 8.2; it's not happening for 8.1 though.

regards, tom lane

On Tue, Aug 30, 2005 at 12:09:03PM -0700, Josh Berkus wrote:

> Discovered some problems using 8.1 psql to connect to an 8.0 server:
>
> test_stats=# \l
> ERROR: relation "pg_catalog.pg_roles" does not exist
> test_stats=# \dt
> ERROR: relation "pg_catalog.pg_roles" does not exist
> test_stats=# \dv
> ERROR: relation "pg_catalog.pg_roles" does not exist
>
> This is from CVS, the day before the beta release. Ignore me if it's
> already been fixed.

It's not fixed, but it's also labeled "won't fix," at least for now.

--
Alvaro Herrera <alvherre[]alvh.no-ip.org> Architect, www.EnterpriseDB.com
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)

Tom Lane wrote:
> This isn't a bug, it's a feature request. We've never had backwards
> compatibility of psql backslash commands.

In the past, most of these usually worked. As it appears now, most of
them won't work at all, so we should really put a bigger warning sign
somewhere, because in spite of this not being a bug, people will
probably consider it so and keep bothering us about it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane wrote:
>> This isn't a bug, it's a feature request. We've never had backwards
>> compatibility of psql backslash commands.

> In the past, most of these usually worked.

They've been broken on a fairly regular basis in past releases.
Certainly 7.3 broke every single one because of the addition of
schema syntax ...

regards, tom lane

Tom,

> They've been broken on a fairly regular basis in past releases.
> Certainly 7.3 broke every single one because of the addition of
> schema syntax ...

Yeah, and we warned people about it, as I recall. Also, we had about 25x
less users then. I think we should put something in the release notes:

WARNING: 8.1's "psql" is not completely backwards-compatible with previous
versions of PostgreSQL.

--Josh

--
__Aglio Database Solutions_______________
Josh Berkus Consultant
josh(at)agliodbs(dot)com www.agliodbs.com
Ph: 415-752-2500 Fax: 415-752-2387
2166 Hayes Suite 200 San Francisco, CA

Josh Berkus wrote:
> Tom,
>
> > They've been broken on a fairly regular basis in past releases.
> > Certainly 7.3 broke every single one because of the addition of
> > schema syntax ...
>
> Yeah, and we warned people about it, as I recall. Also, we had about 25x
> less users then. I think we should put something in the release notes:
>
> WARNING: 8.1's "psql" is not completely backwards-compatible with previous
> versions of PostgreSQL.
>

I am working on a patch to warn on psql startup if the backslash
commands might not work.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Tom Lane wrote:
>>> This isn't a bug, it's a feature request. We've never had backwards
>>> compatibility of psql backslash commands.

>> In the past, most of these usually worked.

> They've been broken on a fairly regular basis in past releases.
> Certainly 7.3 broke every single one because of the addition of
> schema syntax ...

I think that example is the exception, and not the rule. All
other versions /except/ 7.3 worked for the most part with older
versions. I think this is a worthy goal. We don't have to try
and go back to 7.2 or anything, but I think most users would
presume that psql would at least work on at least the previous
version.

If it's the beta timing, I can understand that, but there wasn't all
this fuss when I made psql backwards-compatible last time for
tablespaces. Also, I would think the no new feature rule could
be bent a little here, considering that psql is a relatively
small standalone app, and, as pointed out, we are definitely going
to see people complain about this on the list. A little bit of
effort now will make the project look much better in the long run.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200508301952
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFDFPHQvJuQZxSWSsgRAgtaAKDbRUWHlrqbtIipJiJjP2TaX9OesQCg2IBe
cUDudmot1bIZpWqhtVH/7OA=
=E6aE
-----END PGP SIGNATURE-----

On Tue, Aug 30, 2005 at 07:35:40PM -0400, Bruce Momjian wrote:
> Josh Berkus wrote:
> > Tom,
> >
> > > They've been broken on a fairly regular basis in past releases.
> > > Certainly 7.3 broke every single one because of the addition of
> > > schema syntax ...
> >
> > Yeah, and we warned people about it, as I recall. Also, we had
> > about 25x less users then. I think we should put something in
> > the release notes:
> >
> > WARNING: 8.1's "psql" is not completely backwards-compatible with
> > previous versions of PostgreSQL.
> >
>
> I am working on a patch to warn on psql startup if the backslash
> commands might not work.

In case of relenting on the feature freeze vs. bug thing, I am
preparing a patch that makes psql's \d commands backward-compatible to
8.0. I should have it out this evening or possible tomorrow for
review.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Josh Berkus wrote:
> Tom,
>
> > They've been broken on a fairly regular basis in past releases.
> > Certainly 7.3 broke every single one because of the addition of
> > schema syntax ...
>
> Yeah, and we warned people about it, as I recall. Also, we had about 25x
> less users then. I think we should put something in the release notes:
>
> WARNING: 8.1's "psql" is not completely backwards-compatible with previous
> versions of PostgreSQL.

Here is a patch that will print out (in interactive mode only) a warning
message if a newer client connects to an older major numbered server.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Could this be back patched to the older versions as well?

On Tue, 30 Aug 2005, Bruce Momjian wrote:

> Josh Berkus wrote:
>> Tom,
>>
>>> They've been broken on a fairly regular basis in past releases.
>>> Certainly 7.3 broke every single one because of the addition of
>>> schema syntax ...
>>
>> Yeah, and we warned people about it, as I recall. Also, we had about 25x
>> less users then. I think we should put something in the release notes:
>>
>> WARNING: 8.1's "psql" is not completely backwards-compatible with previous
>> versions of PostgreSQL.
>
> Here is a patch that will print out (in interactive mode only) a warning
> message if a newer client connects to an older major numbered server.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Marc G. Fournier wrote:
>
> Could this be back patched to the older versions as well?

Sure.

---------------------------------------------------------------------------

>
> On Tue, 30 Aug 2005, Bruce Momjian wrote:
>
> > Josh Berkus wrote:
> >> Tom,
> >>
> >>> They've been broken on a fairly regular basis in past releases.
> >>> Certainly 7.3 broke every single one because of the addition of
> >>> schema syntax ...
> >>
> >> Yeah, and we warned people about it, as I recall. Also, we had about 25x
> >> less users then. I think we should put something in the release notes:
> >>
> >> WARNING: 8.1's "psql" is not completely backwards-compatible with previous
> >> versions of PostgreSQL.
> >
> > Here is a patch that will print out (in interactive mode only) a warning
> > message if a newer client connects to an older major numbered server.
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I think we should put something in the release notes:

> WARNING: 8.1's "psql" is not completely backwards-compatible with previous
> versions of PostgreSQL.

Can you point to any single one of the past five major releases in which
psql's backslash commands *WERE* completely backwards-compatible with
previous versions of PostgreSQL? This is not breaking news.

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Here is a patch that will print out (in interactive mode only) a warning
> message if a newer client connects to an older major numbered server.

Why only older? It's even less likely to work if the server is newer.

(I don't agree with the premise to begin with...)

regards, tom lane

On Tue, 30 Aug 2005, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> Here is a patch that will print out (in interactive mode only) a warning
>> message if a newer client connects to an older major numbered server.
>
> Why only older? It's even less likely to work if the server is newer.
>
> (I don't agree with the premise to begin with...)

An example of where this sort of 'obvious warning' is in an environment
like ours, where we have several different version of PostgreSQL running,
and *try* to make sure each version of psql is available to our clients,
but a client inadvertantly runs the wrong version against their database
... its only psql that has the "helper commands", like \d, so the only
thing that *really* makes a different between versions ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Here is a patch that will print out (in interactive mode only) a warning
> > message if a newer client connects to an older major numbered server.
>
> Why only older? It's even less likely to work if the server is newer.
>
> (I don't agree with the premise to begin with...)

OK, this new patch prints a warning on any major version mismatch, newer
or older.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom,

> Can you point to any single one of the past five major releases in which
> psql's backslash commands *WERE* completely backwards-compatible with
> previous versions of PostgreSQL? This is not breaking news.

Because this is the first time I can remember where *none* of the \ commands
work with older servers. And because the error we give is pretty ungraceful.

Hmmm ... how difficult would it be to give a useful error message instead of a
SQL error? Something like:

"I'm sorry, that PSQL command did not work. Most likely, you are connecting
to a different version of PostgreSQL."

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Hmmm ... how difficult would it be to give a useful error message instead of a
> SQL error? Something like:

> "I'm sorry, that PSQL command did not work. Most likely, you are connecting
> to a different version of PostgreSQL."

Well, "useful" is in the eye of the beholder ... in particular, if you are
trying to debug a problem with the \d commands, you would want to see
the actual backend message. OTOH you would probably also want to see
the query actually issued. So maybe we could make this conditional on
the -E mode?

if -E: print the actual backend error message, as now

else: print "Query failed, probably because backend is a different Postgres release"

Also, we can *know* rather than guess whether the backend is a different
major version, so maybe do the "user-friendly" printout only if (a) not -E
mode and (b) there is a version discrepancy. If there's not a version
discrepancy then the failure is unexpected and we shouldn't be hiding
information about it.

I like this better than Bruce's recent proposal to print a warning at
psql startup, because it emits the info when you actually need it.
How many people pay attention to psql's startup banner at all?

regards, tom lane

On Wed, Aug 31, 2005 at 02:05:13PM -0400, Tom Lane wrote:
> I like this better than Bruce's recent proposal to print a warning at
> psql startup, because it emits the info when you actually need it.
> How many people pay attention to psql's startup banner at all?

Aren't there other compatability issues as well? The warning on \connect
seems like a good idea to me. Of course nothing says we can't do both...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Wed, Aug 31, 2005 at 02:05:13PM -0400, Tom Lane wrote:
>> I like this better than Bruce's recent proposal to print a warning at
>> psql startup, because it emits the info when you actually need it.
>> How many people pay attention to psql's startup banner at all?

> Aren't there other compatability issues as well?

Not unless you can point to one. Traditionally psql works fine with
other releases for everything except the \d commands, and AFAIK the
same is true this time around.

regards, tom lane

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Here is a patch that will print out (in interactive mode only) a warning
> > > message if a newer client connects to an older major numbered server.
> >
> > Why only older? It's even less likely to work if the server is newer.
> >
> > (I don't agree with the premise to begin with...)
>
> OK, this new patch prints a warning on any major version mismatch, newer
> or older.

Here is an updated patch that shows the major version numbers of the
client and server when there is a mismatch.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073