Re: v7.3.1 psql against a v7.2.x database ... - Mailing list pgsql-hackers

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