Thread: v7.3.1 psql against a v7.2.x database ...

Is there any way of fixing the following?

164_459_openacs=> \d
ERROR:  parser: parse error at or near "."
164_459_openacs=>

We've started to upgrade the client machines, before upgrading the server
itself, but it looks like the psql client isn't backwards compatible?

On Fri, 2003-01-10 at 12:30, Marc G. Fournier wrote:
> 
> Is there any way of fixing the following?
> 
> 164_459_openacs=> \d
> ERROR:  parser: parse error at or near "."
> 164_459_openacs=>
> 
> We've started to upgrade the client machines, before upgrading the server
> itself, but it looks like the psql client isn't backwards compatible?
> 

It's not so much that the psql client isn't backward compatible, but the
\ commands arn't.  Remember that \d is merely an alias for :

SELECT c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type", u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','') AND c.relname !~ '^pg_'
ORDER BY 1;

in 7.2.x and:

SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type", u.usename as "Owner"
FROM pg_catalog.pg_class c    LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner    LEFT JOIN
pg_catalog.pg_namespacen ON n.oid = c.relnamespace
 
WHERE c.relkind IN ('r','v','S','')     AND n.nspname NOT IN ('pg_catalog', 'pg_toast')     AND
pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

in 7.3

if you run the 7.2 sql from a 7.3 psql client against a 7.2 server it
will work.  One solution might be to create files with the 7.2 queries
in them so you could do something like \i relations to get a list of all
relations in the database. 

If someone we're ambitious enough, you probably could modify psql to
store which version of the server it is connected and the use some type
of class structure to call the appropriate sql for the given \ command. 
Thats the approach we've taken with phppgadmin 3, and while it
complicates things it does have it's benefits.

Robert Treat 

Marc G. Fournier writes:

> We've started to upgrade the client machines, before upgrading the server
> itself, but it looks like the psql client isn't backwards compatible?

The meta-commands are not, because they now need to be schema aware.

-- 
Peter Eisentraut   peter_e@gmx.net

On Fri, 10 Jan 2003, Peter Eisentraut wrote:

> Marc G. Fournier writes:
>
> > We've started to upgrade the client machines, before upgrading the server
> > itself, but it looks like the psql client isn't backwards compatible?
>
> The meta-commands are not, because they now need to be schema aware.

How hard would it be to add in a simple version check, like Robert Treat
suggested?  Where, when you type in \d, it uses a pre-v7.3 schema if
attached to a pre-v7.3 server?  With the changes that have gone in since
v7.3.1, we're going to need to do a v7.3.2 anyway ...

Marc G. Fournier wrote:
> On Fri, 10 Jan 2003, Peter Eisentraut wrote:
> 
> > Marc G. Fournier writes:
> >
> > > We've started to upgrade the client machines, before upgrading the server
> > > itself, but it looks like the psql client isn't backwards compatible?
> >
> > The meta-commands are not, because they now need to be schema aware.
> 
> How hard would it be to add in a simple version check, like Robert Treat
> suggested?  Where, when you type in \d, it uses a pre-v7.3 schema if
> attached to a pre-v7.3 server?  With the changes that have gone in since
> v7.3.1, we're going to need to do a v7.3.2 anyway ...

There are quite a few queries in there, so they would have to be doubled
up for pre/post 7.3.

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

On Fri, 10 Jan 2003, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Fri, 10 Jan 2003, Peter Eisentraut wrote:
> >
> > > Marc G. Fournier writes:
> > >
> > > > We've started to upgrade the client machines, before upgrading the server
> > > > itself, but it looks like the psql client isn't backwards compatible?
> > >
> > > The meta-commands are not, because they now need to be schema aware.
> >
> > How hard would it be to add in a simple version check, like Robert Treat
> > suggested?  Where, when you type in \d, it uses a pre-v7.3 schema if
> > attached to a pre-v7.3 server?  With the changes that have gone in since
> > v7.3.1, we're going to need to do a v7.3.2 anyway ...
>
> There are quite a few queries in there, so they would have to be doubled
> up for pre/post 7.3.

Right ... understood ... but all the queries should just be a matter of
pulling them from v7.2.x sources, no?

Marc G. Fournier wrote:
> > > How hard would it be to add in a simple version check, like Robert Treat
> > > suggested?  Where, when you type in \d, it uses a pre-v7.3 schema if
> > > attached to a pre-v7.3 server?  With the changes that have gone in since
> > > v7.3.1, we're going to need to do a v7.3.2 anyway ...
> >
> > There are quite a few queries in there, so they would have to be doubled
> > up for pre/post 7.3.
> 
> Right ... understood ... but all the queries should just be a matter of
> pulling them from v7.2.x sources, no?

Right.  It is just the _cruft_ factor that has prevented us from doing
it in the past.

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

> Right.  It is just the _cruft_ factor that has prevented us from doing
> it in the past.

Well, you could always have the function library for each different
version in a different shared lib which you load on demand.

Alternatively, follow the phpPgAdmin3 style and have a class 'Postgres71'
and from that inherit 'Postgres72' and override any functions that have
changed, etc.

Chris

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Right.  It is just the _cruft_ factor that has prevented us from doing
> it in the past.

We've never before attempted to make psql cope with back-version
servers.  It might be a good idea in future --- but it strikes me
as a new feature (and not a trivial one).  I don't think we should
try to shoehorn it into 7.3.2.
        regards, tom lane

On Fri, 10 Jan 2003, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Right.  It is just the _cruft_ factor that has prevented us from doing
> > it in the past.
>
> We've never before attempted to make psql cope with back-version
> servers.  It might be a good idea in future --- but it strikes me
> as a new feature (and not a trivial one).  I don't think we should
> try to shoehorn it into 7.3.2.

That was the aspect I feared ... almost an argument in itself for why psql
should be in gborg as a seperate project ;)

On Friday 10 January 2003 23:50, Marc G. Fournier wrote:
> That was the aspect I feared ... almost an argument in itself for why psql
> should be in gborg as a seperate project ;)

You've got to be kidding.  The main command-line interface NEEDS to be part of 
the main package.  The solution is to use the 7.2 psql with the 7.2 
backends...and enjoy the thrills of upgrading.....

Methinks 7.3 should have been 8.0 with all the changes that have happened.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

On Sat, 11 Jan 2003, Lamar Owen wrote:

> On Friday 10 January 2003 23:50, Marc G. Fournier wrote:
> > That was the aspect I feared ... almost an argument in itself for why psql
> > should be in gborg as a seperate project ;)
>
> You've got to be kidding.  The main command-line interface NEEDS to be
> part of the main package.

Needs to be *packaged* with the main package for a release, but there is
no reason why it can't have its own release cycle between relesaes ...

On Sat, 11 Jan 2003 03:34:22 -0500, Marc G. Fournier wrote:

> On Sat, 11 Jan 2003, Lamar Owen wrote:
> 
>> On Friday 10 January 2003 23:50, Marc G. Fournier wrote:
>> > That was the aspect I feared ... almost an argument in itself for why
>> > psql should be in gborg as a seperate project ;)
>>
>> You've got to be kidding.  The main command-line interface NEEDS to be
>> part of the main package.
> 
> Needs to be *packaged* with the main package for a release, but there is
> no reason why it can't have its own release cycle between relesaes ...
> 

Well, it is open source, so there's no reason why someone couldn't make
these changes for 7.4 and also release a binary version in the mean time.
I have a copy of a 7.2 psql binary for linux that that has some of the
7.3 psql improvments in it, sometimes it comes in very handy. I'd be
interested in helping out with this, though Christopher would probably
start throwing things at me if I volunteered :-)

Robert Treat

> these changes for 7.4 and also release a binary version in the mean time.
> I have a copy of a 7.2 psql binary for linux that that has some of the
> 7.3 psql improvments in it, sometimes it comes in very handy. I'd be
> interested in helping out with this, though Christopher would probably
> start throwing things at me if I volunteered :-)

Hey - I don't mind :)  So long as you don't mind if I fix all your
privileges stuff in phpPgAdmin :)

Chris

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


> Well, it is open source, so there's no reason why someone couldn't make
> these changes for 7.4 and also release a binary version in the mean time.
> I have a copy of a 7.2 psql binary for linux that that has some of the
> 7.3 psql improvments in it, sometimes it comes in very handy. I'd be
> interested in helping out with this, though Christopher would probably
> start throwing things at me if I volunteered :-)

I have strongly considered doing this, and even started on the project some 
time ago. (I've stopped now). At first I wanted to add 7.3 and 7.4 features 
to a 7.2 psql. Then I considered writing a master psql that could handle 
any backend. In the end, however, I realized that with 7.3 well out the door, 
it was better to encourage people to upgrade to 7.3 and spend my energies 
on other things. If there is still a strong interest however, I can easily 
help out and share what I have already done.

--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200301161656

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+JyrzvJuQZxSWSsgRAmdUAJ4upWRFGKi1K5WYAwCVf36w1V4aAQCgvuD3
kCy+Q6EE/pum7Sojim+TJdM=
=Tvn6
-----END PGP SIGNATURE-----

greg@turnstep.com wrote:
<snip>
> I have strongly considered doing this, and even started on the project some 
> time ago. (I've stopped now). At first I wanted to add 7.3 and 7.4 features 
> to a 7.2 psql. Then I considered writing a master psql that could handle 
> any backend. In the end, however, I realized that with 7.3 well out the door, 
> it was better to encourage people to upgrade to 7.3 and spend my energies 
> on other things. If there is still a strong interest however, I can easily 
> help out and share what I have already done.

With ever more larger businesses adopting PostgreSQL, and that leading 
on to more places having several versions of PostgreSQL in operation 
simultaneously (i.e. development vs production) we're probably going to 
need to give psql the ability to handle whichever version of the PG 
backend it happens to connect to.

Marc's suggestion of breaking psql into it's own sub-project makes good 
sense from that point of view.

So... this stuff is interesting Greg.

:-)

Regards and best wishes,

Justin Clift


> --
> Greg Sabino Mullane greg@turnstep.com
> PGP Key: 0x14964AC8 200301161656
> 
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
> 
> iD8DBQE+JyrzvJuQZxSWSsgRAmdUAJ4upWRFGKi1K5WYAwCVf36w1V4aAQCgvuD3
> kCy+Q6EE/pum7Sojim+TJdM=
> =Tvn6
> -----END PGP SIGNATURE-----
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html


-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

> With ever more larger businesses adopting PostgreSQL, and that leading
> on to more places having several versions of PostgreSQL in operation
> simultaneously (i.e. development vs production) we're probably going to
> need to give psql the ability to handle whichever version of the PG
> backend it happens to connect to.
>
> Marc's suggestion of breaking psql into it's own sub-project makes good
> sense from that point of view.

Subproject or not, why don't we just rearrange psql to dynamically load a
library of functions, eg:

libpsql72.so
libpsql73.so
etc...

And in them you have functions like:

printTableDef();
printViewDef();
etc...

Chris

Christopher Kings-Lynne wrote:
>>With ever more larger businesses adopting PostgreSQL, and that leading
>>on to more places having several versions of PostgreSQL in operation
>>simultaneously (i.e. development vs production) we're probably going to
>>need to give psql the ability to handle whichever version of the PG
>>backend it happens to connect to.
>>
>>Marc's suggestion of breaking psql into it's own sub-project makes good
>>sense from that point of view.

Hey, good point.  Giving psql the ability to handle multiple backend 
versions could be done in a number of ways.


> Subproject or not, why don't we just rearrange psql to dynamically load a
> library of functions, eg:
> 
> libpsql72.so
> libpsql73.so
> etc...
> 
> And in them you have functions like:
> 
> printTableDef();
> printViewDef();
> etc...

Is this very different from how it's done at present?

:-)

Regards and best wishes,

Justin Clift


> Chris
> 
> 


-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

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


> Is this very different from how it's done at present?

Yes. :)

I'd like to play Devil's Advocate a bit on the whole backward-compatible 
psql issue. First, I have not seen a lot of clamor for this sort of thing. 
Second, psql comes bundled with the project; you cannot build the 
postgresql binary without getting the latest and greatest psql installed 
as well. So it is not as if this is a standalone app that someone may not 
have the latest version of. Having a working version of psql is actually 
a prerequisite for releasing a new version of Postgres! Third, the changes 
from 7.2 to 7.3 in psql were fairly severe with the addition of schemas, 
and don't really lend themselves well to a rewrite to handle previous 
versions. I recall someone (Tom?) asked if anyone wanted to step up 
to the plate on making something like that some time ago, but nobody did.
Fourth, my custom version is an enhanced 7.2, not a compatible 7.3, 
so my existing work would not be too helpful in this case.

I'd support making psql 7.3 and forward be aware of the backend they 
are connecting to, and support them being able to work against all 7.3+ 
servers, but I still fail to see the pressing need for a backward-compatible 
version when the correct one is always shipped with the server. 

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200301221120

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+LsVsvJuQZxSWSsgRAlBtAJ95xL+YRgkSwE554ucIFjgAVoaj6ACeOzNs
nyenGFcy4lY2X3vrOJln/HY=
=I0Lw
-----END PGP SIGNATURE-----

>>>greg@turnstep.com said:> I'd support making psql 7.3 and forward be aware of the backend they > are connecting to,
andsupport them being able to work against all 7.3+ > servers, but I still fail to see the pressing need for a
backward-compatible    > version when the correct one is always shipped with the server. 
 

This hits the nail in the head. I was just counting the pros' and cons to 
upgrade an large production system from 7.2.3 to 7.3.x.

The trouble is, there is need to access both types of databases, new and old. 
Often from the same psql executable.

So psql should definitely be backward compatible!

Daniel

On Wed, 2003-01-22 at 11:11, greg@turnstep.com wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Is this very different from how it's done at present?
>
> Yes. :)
>
> I'd like to play Devil's Advocate a bit on the whole backward-compatible
> psql issue. First, I have not seen a lot of clamor for this sort of thing.
> Second, psql comes bundled with the project; you cannot build the
> postgresql binary without getting the latest and greatest psql installed
> as well. So it is not as if this is a standalone app that someone may not
> have the latest version of. Having a working version of psql is actually
> a prerequisite for releasing a new version of Postgres! Third, the changes
> from 7.2 to 7.3 in psql were fairly severe with the addition of schemas,
> and don't really lend themselves well to a rewrite to handle previous
> versions. I recall someone (Tom?) asked if anyone wanted to step up
> to the plate on making something like that some time ago, but nobody did.
> Fourth, my custom version is an enhanced 7.2, not a compatible 7.3,
> so my existing work would not be too helpful in this case.
>
> I'd support making psql 7.3 and forward be aware of the backend they
> are connecting to, and support them being able to work against all 7.3+
> servers, but I still fail to see the pressing need for a backward-compatible
> version when the correct one is always shipped with the server.

New commands in psql are probably worth the upgrade right there --
especially in the case of 7.3.  Protocol changes make it all but
impossible (like what 7.4 is probably going to get).

But, 1/2 the problem can be removed if we stuff the logic into the
backend.  This includes moving the queries as well as the list of
available commands.

I say we abuse prepared queries.

Create a table in the database to hold the list of available psql
commands (\dt, \dD, \dS, etc.) along with a preparable query, and the
number of expected arguments:

CREATE TABLE pg_psql_commandset

-- Command as typed in psql (\dt, \dD, \dS, etc)
( command varchar(5)

-- Number of arguments after the command
, nargs smallint

-- The sql whose output will be used directly to display a table
, tablesql text

-- The sql whose output will be displayed as additional lineitems after
-- the table, like foreign keys and primary keys after the table
-- information
, extrasql text

-- A command may be different based on the number of arguments.
, primary key (command, nargs)
);

The entry for \dt would be:

command:    \dt
nargs:        0
tablesql:    SELECT ....
extrasql:    NULL


The entry for '\dt a' would be:

command:    \dt
nargs:        1
tablesql:    SELECT .. WHERE relname like '^' || ? || '$' ...
extrasql:    SELECT key, name, text FROM ....


Execution:
0. Start psql client
1. User types \dt.
2. psql checks to see if '\dt with 0 args' has been prepared (it hasn't)
3. Find info on \dt with 0 args, and pull it out from pg_psql_commandset
4. Since found, prepare \dt with 0 args (assume all args are text for
simplicity).  If a command isn't available, tell the user Postgresql 7.x
doesn't support command.
5. Fetch results of the tablesql and extrasql (where not null), and
display to the user.


So long as the structure of the pg_psql_commandset table doesn't change,
all future versions (barring a protocol change) should be able to use
any backend version with an appropriate command set for the backend.

The trickiest part is taking the *.* style args and using them
appropriately, but thats not too difficult.

tablesql and extrasql could (in some cases) simply call system functions
-- but I'd not want to hardcode the function structure as psql would
need to be taught how to deal with varying input types (may end up with
several versions of the function list).

It would be better if prepared statements didn't insist on knowledge of
the datatypes from the client -- but I think they can be discovered from
the context of the variable use instead (Neil?).

Anyway, it's just a thought.  psql in 7.4 is free game due to the
anticipated protocol change which will make prior versions
non-functioning anyway.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

greg@turnstep.com writes:
> I'd support making psql 7.3 and forward be aware of the backend they 
> are connecting to, and support them being able to work against all 7.3+ 
> servers, but I still fail to see the pressing need for a backward-compatible 
> version when the correct one is always shipped with the server. 

The scenario where it's valuable involves multiple machines: if you
connect to another machine that is running an older Postgres, it'd be
nice not to have to have a matching psql installed locally.  For
example, consider someone who does development work (so has the latest
PG installed on his workstation) but also must admin a production box
with an older PG version installed there.  As things currently stand,
he has to keep an extra copy of psql on his workstation to use for
talking to the production server.

But, while I see the value in it, I'm not personally prepared to put in
the work needed to make it happen.
        regards, tom lane

The keep-annoying-everybody-until-it-really-works caompain

gpg: armor header: Version: GnuPG v1.2.1 (FreeBSD)
gpg: Signature made Mit 22 Jan 2003 18:43:21 CET using DSA key ID 8C3ABF0C
gpg: BAD signature from "Rod Taylor (Database Developer) <rod.taylor@inquent.com>"

On Mit, 2003-01-22 at 18:43, Rod Taylor wrote:
> X-Mailer: Ximian Evolution 1.2.1

Arrgh! I really thought evo should be able to verify its own signatures
by now.

Same evo version on my side. I could not see any special oddities in
your mail.

cheers
-- vbi

--
featured product: SpamAssassin - http://spamassassin.org