Thread: multi-backend psql

multi-backend psql

From
Christopher Kings-Lynne
Date:
Hi,

Is there demand for modifying psql to work against previous backends, 
pg_dump-style?

I feel like looking into it, but tell me if I'm wasting my time...

Chris




Re: multi-backend psql

From
Alvaro Herrera
Date:
On Tue, Oct 21, 2003 at 09:26:12AM +0800, Christopher Kings-Lynne wrote:

> Is there demand for modifying psql to work against previous backends, 
> pg_dump-style?
> 
> I feel like looking into it, but tell me if I'm wasting my time...

Please do ...

I wonder what would it take.  It only needs a different set of queries
to obtain info from the syscatalogs, or is it more involved?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".


Re: multi-backend psql

From
Christopher Kings-Lynne
Date:
>>I feel like looking into it, but tell me if I'm wasting my time...
> 
> 
> Please do ...
> 
> I wonder what would it take.  It only needs a different set of queries
> to obtain info from the syscatalogs, or is it more involved?

Looks fairly straightforward to me.  Just need to get backend version 
out.  Maybe handle v2 protocol again.  Then just have different SQL for 
different backends.

Chris




Re: multi-backend psql

From
"Matthew T. O'Connor"
Date:
On Mon, 2003-10-20 at 21:45, Christopher Kings-Lynne wrote:
> Looks fairly straightforward to me.  Just need to get backend version 
> out.  Maybe handle v2 protocol again.  Then just have different SQL for 
> different backends.

Going forward if we put the sql for all the psql commands into fuctions,
then psql could be less tied to the backend version.  I thought this was
a TODO item already.




Re: multi-backend psql

From
Rod Taylor
Date:
> Going forward if we put the sql for all the psql commands into fuctions,
> then psql could be less tied to the backend version.  I thought this was
> a TODO item already.

The tricky part seems to be dealing with i10n issues since the text to
translate would be release specific it needs to go into the backend --
but that isn't so nice.

Re: multi-backend psql

From
Christopher Kings-Lynne
Date:
> The tricky part seems to be dealing with i10n issues since the text to
> translate would be release specific it needs to go into the backend --
> but that isn't so nice.

Why tricky?  I'm just going to make the 7.5 psql utility work against 
previous versions of postgresql.  Any strings in that utility are 
translatable like any other.

Chris




Re: multi-backend psql

From
Rod Taylor
Date:
On Mon, 2003-10-20 at 22:39, Christopher Kings-Lynne wrote:
> > The tricky part seems to be dealing with i10n issues since the text to
> > translate would be release specific it needs to go into the backend --
> > but that isn't so nice.
>
> Why tricky?  I'm just going to make the 7.5 psql utility work against
> previous versions of postgresql.  Any strings in that utility are
> translatable like any other.

I suppose if all you want is backward compatibility which makes sense
for pg_dump, but surely psql should be forward thinking.

Normally it's old clients with new server, not the other way around --
at least with big companies it seems easier to get a server upgraded
than everyones desktop.

Forward looking means pulling the available commands, queries, etc from
the backend. It actually works quite well (submitted a patch quite a
while ago) in all respects except string translation.

Re: multi-backend psql

From
Christopher Kings-Lynne
Date:
> I suppose if all you want is backward compatibility which makes sense
> for pg_dump, but surely psql should be forward thinking.>
> Normally it's old clients with new server, not the other way around --
> at least with big companies it seems easier to get a server upgraded
> than everyones desktop.
> 
> Forward looking means pulling the available commands, queries, etc from
> the backend. It actually works quite well (submitted a patch quite a
> while ago) in all respects except string translation.

Hmmm...string translation really is the bugger, isn't it.  I had only 
planned to do backwards compatibility really...

It had occurred to me that we could move support for each version of the 
backend into a shared lib.

eg. libpsql70.so, libpsql71.so, etc.

Then all we do is load the appropriate lib and call functions in it.  To 
support a newer version of postgres, you just need to drop in the latest 
.so or something.

Chris






Re: multi-backend psql

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> It had occurred to me that we could move support for each version of the 
> backend into a shared lib.
> eg. libpsql70.so, libpsql71.so, etc.
> Then all we do is load the appropriate lib and call functions in it.  To 
> support a newer version of postgres, you just need to drop in the latest 
> .so or something.

It doesn't strike me that that actually buys you anything, except
perhaps guaranteeing that psql cannot function on shared-lib-less
platforms.  The clear facts at the moment are that an older psql
cannot be promised to have full functionality with newer backends.
Saying "well it'll work if you install a newer shared library" does
not buy a thing that I can see --- it's no more effort to install
a whole new psql, is it?

Rod's ideas about pushing psql functionality out to the backend
(via special views etc) could ameliorate the forward-compatibility
problem to some extent.  But we usually find ourselves fixing psql
in more places than describe.c for each release, so I'm not convinced
there's a full solution available in that direction either.
        regards, tom lane


Re: multi-backend psql

From
Rod Taylor
Date:
On Mon, 2003-10-20 at 23:15, Christopher Kings-Lynne wrote:
> > I suppose if all you want is backward compatibility which makes sense
> > for pg_dump, but surely psql should be forward thinking.
>  >
> > Normally it's old clients with new server, not the other way around --
> > at least with big companies it seems easier to get a server upgraded
> > than everyones desktop.
> >
> > Forward looking means pulling the available commands, queries, etc from
> > the backend. It actually works quite well (submitted a patch quite a
> > while ago) in all respects except string translation.
>
> Hmmm...string translation really is the bugger, isn't it.  I had only
> planned to do backwards compatibility really...

The way around it is to have a 'description' query that describes how to
draw the table (title, field names) and the content query that supplies
the content for the table.

I just haven't tried it out yet.

> Then all we do is load the appropriate lib and call functions in it.  To
> support a newer version of postgres, you just need to drop in the latest
> .so or something.

That really isn't different than putting queries into the client.


Re: multi-backend psql

From
Rod Taylor
Date:
On Tue, 2003-10-21 at 00:08, Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > It had occurred to me that we could move support for each version of the
> > backend into a shared lib.
> > eg. libpsql70.so, libpsql71.so, etc.
> > Then all we do is load the appropriate lib and call functions in it.  To
> > support a newer version of postgres, you just need to drop in the latest
> > .so or something.
>
> It doesn't strike me that that actually buys you anything, except
> perhaps guaranteeing that psql cannot function on shared-lib-less
> platforms.  The clear facts at the moment are that an older psql
> cannot be promised to have full functionality with newer backends.
> Saying "well it'll work if you install a newer shared library" does
> not buy a thing that I can see --- it's no more effort to install
> a whole new psql, is it?
>
> Rod's ideas about pushing psql functionality out to the backend
> (via special views etc) could ameliorate the forward-compatibility
> problem to some extent.  But we usually find ourselves fixing psql
> in more places than describe.c for each release, so I'm not convinced
> there's a full solution available in that direction either.

There is always the biggest evil of all... Putting SHOW / DESCRIBE /
HELP commands into the backend itself. I'm sure the pgAdmin group likes
that idea (they're probably tired of maintaining 4 different versions of
queries for getting a list of tables). Any solution to make psql
backward or forward compatible should go an additional step to assist
other frontends as well.

Re: multi-backend psql

From
Andreas Pflug
Date:
Rod Taylor wrote:

> I'm sure the pgAdmin group likes that idea (they're probably tired of maintaining 4 different versions of
>queries for getting a list of tables). Any solution to make psql backward or forward compatible should go an
additionalstep to assist other frontends as well.
 
>  
>
While I can agree on the statements that frontends like backend support 
functions, in the case of tables for pgAdmin3 helper functions/views 
don't seem too helpful. pgAdmin3 will not only try to display all 
supported backend versions (currently 7.3, 7.4) correctly, but also 
enable/disable additional functionality. Having different queries for 
different backends is just a small part of the game, and the easiest one 
too. Hiding the backend differences behind views or functions wouldn't 
help too much.

Regards,
Andreas





Re: multi-backend psql

From
Rod Taylor
Date:
On Tue, 2003-10-21 at 09:03, Andreas Pflug wrote:
> Rod Taylor wrote:
>
> > I'm sure the pgAdmin group likes that idea (they're probably tired of maintaining 4 different versions of
> >queries for getting a list of tables). Any solution to make psql backward or forward compatible should go an
additionalstep to assist other frontends as well. 
> >
> >
> While I can agree on the statements that frontends like backend support
> functions, in the case of tables for pgAdmin3 helper functions/views
> don't seem too helpful. pgAdmin3 will not only try to display all
> supported backend versions (currently 7.3, 7.4) correctly, but also
> enable/disable additional functionality. Having different queries for
> different backends is just a small part of the game, and the easiest one
> too. Hiding the backend differences behind views or functions wouldn't
> help too much.

Of course, psql has the same issue in hiding functionality that doesn't
exist. My biggest beef is the psql help is often misleading if you're
connected to a different backend.

This would need to be a part of a solution if we're going to get
anything out of it.

Re: multi-backend psql

From
Andreas Pflug
Date:
Rod Taylor wrote:

>
>Of course, psql has the same issue in hiding functionality that doesn't
>exist. My biggest beef is the psql help is often misleading if you're
>connected to a different backend.
>
>This would need to be a part of a solution if we're going to get
>anything out of it.
>  
>
No problem, let's put the complete documentation into the server!
Something like pg_help(text, int2) e.g. SELECT pg_help('CREATE TABLE 
foo(..)', PG_HTML);

Regards,
Andreas



Re: multi-backend psql

From
Rod Taylor
Date:
On Tue, 2003-10-21 at 09:33, Andreas Pflug wrote:
> Rod Taylor wrote:
>
> >
> >Of course, psql has the same issue in hiding functionality that doesn't
> >exist. My biggest beef is the psql help is often misleading if you're
> >connected to a different backend.
> >
> >This would need to be a part of a solution if we're going to get
> >anything out of it.
> >
> >
> No problem, let's put the complete documentation into the server!
> Something like pg_help(text, int2) e.g. SELECT pg_help('CREATE TABLE
> foo(..)', PG_HTML);

That would certainly be a part of it. I haven't put much thought outside
of the help / describe / tab completion portions.


Re: multi-backend psql

From
Christopher Kings-Lynne
Date:
> There is always the biggest evil of all... Putting SHOW / DESCRIBE /
> HELP commands into the backend itself. I'm sure the pgAdmin group likes
> that idea (they're probably tired of maintaining 4 different versions of
> queries for getting a list of tables). Any solution to make psql
> backward or forward compatible should go an additional step to assist
> other frontends as well.

All that means for phpPgAdmin and pgAdmin is that we'll have to support 
5 different queries :P

We could use information_schema...

Chris




Re: multi-backend psql

From
Christopher Kings-Lynne
Date:
> There is always the biggest evil of all... Putting SHOW / DESCRIBE /
> HELP commands into the backend itself. I'm sure the pgAdmin group likes
> that idea (they're probably tired of maintaining 4 different versions of
> queries for getting a list of tables). Any solution to make psql
> backward or forward compatible should go an additional step to assist
> other frontends as well.

Really, is the problem with translation that you might want different 
translation in your client psql compared to the server?

Chris




Re: multi-backend psql

From
Rod Taylor
Date:
On Tue, 2003-10-21 at 21:24, Christopher Kings-Lynne wrote:
> > There is always the biggest evil of all... Putting SHOW / DESCRIBE /
> > HELP commands into the backend itself. I'm sure the pgAdmin group likes
> > that idea (they're probably tired of maintaining 4 different versions of
> > queries for getting a list of tables). Any solution to make psql
> > backward or forward compatible should go an additional step to assist
> > other frontends as well.
>
> All that means for phpPgAdmin and pgAdmin is that we'll have to support
> 5 different queries :P

Yes, but I would hope it stops at 5, and over the next 3 years you don't
have 10 different query forms.

> We could use information_schema...

Nay... I would expect a PostgreSQL specific information_schema to get
just as much mucking around as the system tables, which means you are
still maintaining a set of queries per release.

Re: multi-backend psql

From
Andreas Pflug
Date:
Rod Taylor wrote:

>On Tue, 2003-10-21 at 21:24, Christopher Kings-Lynne wrote:
>  
>
>>>There is always the biggest evil of all... Putting SHOW / DESCRIBE /
>>>HELP commands into the backend itself. I'm sure the pgAdmin group likes
>>>that idea (they're probably tired of maintaining 4 different versions of
>>>queries for getting a list of tables). Any solution to make psql
>>>backward or forward compatible should go an additional step to assist
>>>other frontends as well.
>>>      
>>>
>>All that means for phpPgAdmin and pgAdmin is that we'll have to support 
>>5 different queries :P
>>    
>>
>
>Yes, but I would hope it stops at 5, and over the next 3 years you don't
>have 10 different query forms.
>
>  
>
>>We could use information_schema...
>>    
>>
>
>Nay... I would expect a PostgreSQL specific information_schema to get
>just as much mucking around as the system tables, which means you are
>still maintaining a set of queries per release.
>  
>
The problem about information_schema is that it's restricted to show 
objects of the owner only. This is by spec, but will prevent us from 
seeing all we need.
This might get better if we get rules.

Regards,
Andreas





Re: multi-backend psql

From
Rod Taylor
Date:
> >Nay... I would expect a PostgreSQL specific information_schema to get
> >just as much mucking around as the system tables, which means you are
> >still maintaining a set of queries per release.
> >
> >
> The problem about information_schema is that it's restricted to show
> objects of the owner only. This is by spec, but will prevent us from
> seeing all we need.
> This might get better if we get rules.

Well... That, and it only describes about 1/2 of the features in
PostgreSQL.