Thread

Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T18:51:19Z

This is an attempt to lay out a road map for updating the frontend/backend
protocol in 7.4.  I don't at this point want to get into details on any
one of the TODO items, just get consensus that this is the set of tasks
to be tackled.  Are there any areas that I've missed (that require
protocol changes)?


* Extend ERROR and NOTICE messages to carry multiple fields, including
(as appropriate) a spec-compliant error code, a textual position in the
original query string, information about the source-code location where
the error was reported, etc.

* Consider extending NOTIFY messages to allow a parameter to be carried.

* Fix COPY protocol to allow graceful error recovery (more graceful than
aborting the connection, at least) and to support COPY BINARY to/from
frontend.

* Redesign fastpath function call protocol to eliminate the problems cited
in the source code comments (src/backend/tcop/fastpath.c), and to
eliminate the security hole of accepting unchecked internal representation
from frontend.  Also consider a fastpath for execution of PREPAREd queries.

* Re-institute type-specific send/receive conversion routines to allow
some modest amount of architecture independence for binary data.  This'd
provide a place to check for bogus internal representation during fastpath
input and COPY BINARY IN, too, thus alleviating security concerns.

* Get rid of hardwired field sizes in StartupPacket --- use
variable-length null-terminated strings.  Fixes problem with usernames
being limited to 32 characters, gets around unreasonable limitation on
PGOPTIONS length.  Also can remove unused fields.

* Backend should pass its version number, database encoding, default
client encoding, and possibly other data (any ideas?) to frontend during
startup, to avoid need for explicit queries to get this info.  We could
also consider eliminating SET commands sent by libpq in favor of adding
variable settings to startup packet's PGOPTIONS field.  Ideally we could
get back to the point where a standard connection startup takes only one
packet in each direction.

* Backend's ReadyForQuery message (Z message) should carry an indication
of current transaction status (idle/in transaction/in aborted transaction)
so that frontend need not guess at state.  Perhaps also indicate
autocommit status.  (Is there anything else that frontends would Really
Like To Know?)

* XML support?  If we do anything, I'd want some extensible solution to
allowing multiple query-result output formats from the backend, not an
XML-specific hack.  For one thing, that would allow the actual appearance
of any XML support to happen later.


One of the $64 questions that has to be answered is how much work we're
willing to expend on backwards compatibility.  The path of least
resistance would be to handle it the same way we've done protocol
revisions in the past: the backend will be able to handle both old and new
protocols (so it can talk to old clients) but libpq would be revised to
speak only the new protocol (so new/recompiled clients couldn't talk to
old backends).  We've gotten away with this approach in the past, but the
last time was release 6.4.  I fully expect to hear more complaints now.

One way to tamp down expectations of client backwards compatibility
would be to call the release 8.0 instead of 7.4 ;-)

Comments?

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Justin Clift <justin@postgresql.org> — 2003-03-10T19:05:22Z

Tom Lane wrote:
<snip>
> One way to tamp down expectations of client backwards compatibility
> would be to call the release 8.0 instead of 7.4 ;-)
> 
> Comments?

Actually, I've been thinking about the numbering of the next PostgreSQL 
version for a few days now.

The scenario that's appealing to me the most is this for the next release:

PostgreSQL 8.0
**************

+ Includes PITR and the Win32 port

+ Not sure where Satoshi is up to with his 2 phase commit proposal, but 
that might make sense to incorporate into a wire protocol revision. 
 From memory he received funding to work on it, so it might be coming 
along nicely.

+ Other things optional of course.

Personally, I'd rather we go for PostgreSQL 8.0, waiting a while extra 
for PITR and Win32 if needed, and also properly co-ordinate all of the 
release process information (website updates, package builds, Announce 
to the mailing lists and news sources).

Regards and best wishes,

Justin Clift

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

Re: Roadmap for FE/BE protocol redesign

Rod Taylor <rbt@rbt.ca> — 2003-03-10T19:14:58Z

> + Not sure where Satoshi is up to with his 2 phase commit proposal, but 
> that might make sense to incorporate into a wire protocol revision. 
>  From memory he received funding to work on it, so it might be coming 
> along nicely.

One should note that his protocol changes had absolutely nothing to do
with 2 phase commits -- but were used as a marker to direct replication.

We may want to consider leaving some space for a server / server style
communication (Cluster ID, etc.)

-- 
Rod Taylor <rbt@rbt.ca>

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

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-10T19:15:02Z

Tom Lane wrote:
> * Backend should pass its version number, database encoding, default
> client encoding, and possibly other data (any ideas?) to frontend during
> startup, to avoid need for explicit queries to get this info.  We could
> also consider eliminating SET commands sent by libpq in favor of adding
> variable settings to startup packet's PGOPTIONS field.  Ideally we could
> get back to the point where a standard connection startup takes only one
> packet in each direction.

Should we pass this in a way where we can add stuff later, like passing
it as a simple NULL-terminated string that can get split up on the
client end.

> One of the $64 questions that has to be answered is how much work we're
> willing to expend on backwards compatibility.  The path of least
> resistance would be to handle it the same way we've done protocol
> revisions in the past: the backend will be able to handle both old and new
> protocols (so it can talk to old clients) but libpq would be revised to
> speak only the new protocol (so new/recompiled clients couldn't talk to
> old backends).  We've gotten away with this approach in the past, but the
> last time was release 6.4.  I fully expect to hear more complaints now.

I think such compatibility is sufficient.  We can mention in the
releases notes that they should upgrade there servers before their
clients.

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T19:17:17Z

Justin Clift <justin@postgresql.org> writes:
> The scenario that's appealing to me the most is this for the next release:
> PostgreSQL 8.0
> + Includes PITR and the Win32 port

If the folks doing those things can get done in time, great.  I'm even
willing to push out the release schedule (now, not later) to make it
more likely they can get done.  What I'm not willing to do is define
the release in terms of "it happens when these things are done".  We
learned the folly of that approach in 7.1 and 7.2.  Setting a target
date and sticking to it works *much* better.

> + Not sure where Satoshi is up to with his 2 phase commit proposal, but 
> that might make sense to incorporate into a wire protocol revision. 

I can't see any need for protocol-level support for such a thing.
Why wouldn't it just be some more SQL commands?

(Not that I believe in 2PC as a real-world solution anyway, but that's
a different argument...)

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Rod Taylor <rbt@rbt.ca> — 2003-03-10T19:20:17Z

> * Backend's ReadyForQuery message (Z message) should carry an indication
> of current transaction status (idle/in transaction/in aborted transaction)
> so that frontend need not guess at state.  Perhaps also indicate
> autocommit status.  (Is there anything else that frontends would Really
> Like To Know?)

Could it include transaction depth with the assumption nested
transactions will arrive at some point?

> * XML support?  If we do anything, I'd want some extensible solution to
> allowing multiple query-result output formats from the backend, not an
> XML-specific hack.  For one thing, that would allow the actual appearance
> of any XML support to happen later.

> One of the $64 questions that has to be answered is how much work we're
> willing to expend on backwards compatibility.  The path of least
> resistance would be to handle it the same way we've done protocol
> revisions in the past: the backend will be able to handle both old and new
> protocols (so it can talk to old clients) but libpq would be revised to
> speak only the new protocol (so new/recompiled clients couldn't talk to
> old backends).  We've gotten away with this approach in the past, but the
> last time was release 6.4.  I fully expect to hear more complaints now.

I wouldn't worry about backward compatibility complaints too much BUT
I'd be tempted to make a startup packet that will allow libpq to revert
back to old protocols easily enough for the future so that we can do 
incremental changes to the protocol.

-- 
Rod Taylor <rbt@rbt.ca>

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

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T19:24:54Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> * Backend should pass its version number, database encoding, default
>> client encoding, and possibly other data (any ideas?) to frontend during
>> startup, to avoid need for explicit queries to get this info.

> Should we pass this in a way where we can add stuff later, like passing
> it as a simple NULL-terminated string that can get split up on the
> client end.

Yeah, I was envisioning something with multiple labeled fields so that
more stuff can be added later without a protocol change (likewise for
StartupPacket and ErrorMessage).  But again, I don't want this thread to
get into any details about specific tasks --- let's try to get a view of
the forest before we start hewing down individual trees...

>> We've gotten away with this approach in the past, but the
>> last time was release 6.4.  I fully expect to hear more complaints now.

> I think such compatibility is sufficient.  We can mention in the
> releases notes that they should upgrade there servers before their
> clients.

I'd be really happy if we can make that stick.  There's enough work to
be done here without trying to develop a multiprotocol version of
libpq.

It would be good to hear some words from the JDBC and ODBC developers
about what sort of plans they'd have for updating those interfaces.

			regards, tom lane

Re: 7.4 vs 8.0 WAS Roadmap for FE/BE protocol redesign

Robert Treat <xzilla@users.sourceforge.net> — 2003-03-10T19:29:13Z

On Mon, 2003-03-10 at 14:05, Justin Clift wrote: 
> Tom Lane wrote:
> <snip>
> > One way to tamp down expectations of client backwards compatibility
> > would be to call the release 8.0 instead of 7.4 ;-)
> > 
> > Comments?
> 
> Actually, I've been thinking about the numbering of the next PostgreSQL 
> version for a few days now.
> 
> The scenario that's appealing to me the most is this for the next release:
> 
> PostgreSQL 8.0
> **************
> 
> + Includes PITR and the Win32 port
> 
> + Not sure where Satoshi is up to with his 2 phase commit proposal, but 
> that might make sense to incorporate into a wire protocol revision. 
>  From memory he received funding to work on it, so it might be coming 
> along nicely.
> 
> + Other things optional of course.
> 
> 
> Personally, I'd rather we go for PostgreSQL 8.0, waiting a while extra 
> for PITR and Win32 if needed, and also properly co-ordinate all of the 
> release process information (website updates, package builds, Announce 
> to the mailing lists and news sources).
> 
I don't think PITR or Win32 (or even replication) warrant an 8.0, since
none of those should effect client/server interaction and/or backward
compatibility. (Or at least not as much as schema support did, which
required most "adminy" apps to be worked over) 

A protocol change however, would warrant a version number bump IMHO. I
would guess that by the time all of the protocol changes could be
completed, we'd have win32 or pitr, so it will hopefully be moot. 

Robert Treat

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T19:30:22Z

Rod Taylor <rbt@rbt.ca> writes:
> I'd be tempted to make a startup packet that will allow libpq to revert
> back to old protocols easily enough for the future so that we can do=20
> incremental changes to the protocol.

We already have that: you send a startup packet with a version less than
the latest, and the backend speaks that version to you.

One thing I want to do though is relax the protocol-level constraints
on certain message contents: for example, if ErrorMessage becomes a
collection of labeled fields, it should be possible to add new field
types without calling it a protocol revision.  The protocol need only
specify "ignore any fields whose label you do not recognize".

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Rod Taylor <rbt@rbt.ca> — 2003-03-10T19:41:36Z

On Mon, 2003-03-10 at 14:30, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > I'd be tempted to make a startup packet that will allow libpq to revert
> > back to old protocols easily enough for the future so that we can do=20
> > incremental changes to the protocol.
> 
> We already have that: you send a startup packet with a version less than
> the latest, and the backend speaks that version to you.

Yes, but that requires you know the backend is less than the latest.

If you send version A, and the backend responds don't know A, but I know
A - 2, then libpq may want to try speaking A - 2.

> types without calling it a protocol revision.  The protocol need only
> specify "ignore any fields whose label you do not recognize".

This is probably just as good, if it's done for both sides.

-- 
Rod Taylor <rbt@rbt.ca>

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

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T19:52:55Z

Rod Taylor <rbt@rbt.ca> writes:
>> We already have that: you send a startup packet with a version less than
>> the latest, and the backend speaks that version to you.

> Yes, but that requires you know the backend is less than the latest.

As opposed to knowing what?  You send the version number you wish to speak;
either the backend can handle it, or not.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Ashley Cambrell <ash@freaky-namuh.com> — 2003-03-10T21:37:28Z

Tom Lane wrote:

>This is an attempt to lay out a road map for updating the frontend/backend
>protocol in 7.4.  I don't at this point want to get into details on any
>one of the TODO items, just get consensus that this is the set of tasks
>to be tackled.  Are there any areas that I've missed (that require
>protocol changes)?
>
>  
>
What about binding variables ala oracle's ociparse -> ocibindbyname -> 
ociexecute -> ocifetch ?  I know you can do most of it via SQL 
(PREPARE/EXECUTE) but you can't do 'RETURN x INTO :x' as it stands.  
This would also get around the problem of getting values from newly 
inserted rows (eg PKs) without resorting to OIDs.  Not entirely a FE/BE 
issue... but worth considering in any redesign.

Ashley Cambrell

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Badger <bruce_badger@badgerse.com> — 2003-03-10T21:41:10Z

On Tue, 2003-03-11 at 06:52, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> >> We already have that: you send a startup packet with a version less than
> >> the latest, and the backend speaks that version to you.
> 
> > Yes, but that requires you know the backend is less than the latest.
> 
> As opposed to knowing what?  You send the version number you wish to speak;
> either the backend can handle it, or not.

If the backend can not handle the version I request, but can handle a
prior version, I'd like to know.  I am planning on having handlers for
multiple protocol versions in the same memory space (I'm using
Smalltalk, BTW) so that one application can talk to various databases of
various vintages.

I suppose that the client can just keep retrying the connection with
different versions until it gets a match, though.

Re: Roadmap for FE/BE protocol redesign

Rod Taylor <rbt@rbt.ca> — 2003-03-10T21:42:58Z

On Mon, 2003-03-10 at 14:52, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> >> We already have that: you send a startup packet with a version less than
> >> the latest, and the backend speaks that version to you.
> 
> > Yes, but that requires you know the backend is less than the latest.
> 
> As opposed to knowing what?  You send the version number you wish to speak;
> either the backend can handle it, or not.

At some point PostgreSQL will have enough users that changing it will
piss them off.  If the backend cannot handle whats been requested, we
may want to consider negotiating a protocol that both can handle.

Anyway, it doesn't really affect me one way or the other.  So whatever
you like is probably fine.
-- 
Rod Taylor <rbt@rbt.ca>

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

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T21:51:23Z

Bruce Badger <bruce_badger@badgerse.com> writes:
> I suppose that the client can just keep retrying the connection with
> different versions until it gets a match, though.

Right now, the backend just barfs with
	FATAL: unsupported frontend protocol
so you have to do a blind search to see what it will take.

It would probably be a good idea to improve that message to mention
exactly what range of protocol versions the backend does support.
However, this will do you little good for talking to existing backends
:-(

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Neil Conway <neilc@samurai.com> — 2003-03-10T21:59:12Z

On Mon, 2003-03-10 at 16:37, Ashley Cambrell wrote:
> This would also get around the problem of getting values from newly 
> inserted rows (eg PKs) without resorting to OIDs.

That's not a problem: ensure that the newly inserted row has a SERIAL
column, and use currval().

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T22:06:00Z

Neil Conway <neilc@samurai.com> writes:
> On Mon, 2003-03-10 at 16:37, Ashley Cambrell wrote:
>> This would also get around the problem of getting values from newly 
>> inserted rows (eg PKs) without resorting to OIDs.

> That's not a problem: ensure that the newly inserted row has a SERIAL
> column, and use currval().

There was some talk awhile back of inventing INSERT ... RETURNING and
UPDATE ... RETURNING commands so that you could pass back computed
values to the frontend without an extra query.  It doesn't seem to have
gotten further than a TODO item yet, though.  AFAICS this does not need
a protocol extension, anyway --- it'd look just the same as a SELECT
at the protocol level.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Ashley Cambrell <ash@freaky-namuh.com> — 2003-03-10T22:07:03Z

Neil Conway wrote:

>On Mon, 2003-03-10 at 16:37, Ashley Cambrell wrote:
>  
>
>>This would also get around the problem of getting values from newly 
>>inserted rows (eg PKs) without resorting to OIDs.
>>    
>>
>
>That's not a problem: ensure that the newly inserted row has a SERIAL
>column, and use currval().
>  
>
Ok.  I forget about that.  Even so, it would still be nice to have a 
bind like interface... :-)

Ashley Cambrell

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-10T23:30:44Z

Tom Lane wrote:
> Justin Clift <justin@postgresql.org> writes:
> > The scenario that's appealing to me the most is this for the next release:
> > PostgreSQL 8.0
> > + Includes PITR and the Win32 port
> 
> If the folks doing those things can get done in time, great.  I'm even
> willing to push out the release schedule (now, not later) to make it
> more likely they can get done.  What I'm not willing to do is define
> the release in terms of "it happens when these things are done".  We
> learned the folly of that approach in 7.1 and 7.2.  Setting a target
> date and sticking to it works *much* better.

Well, we had that 7.4 discussion a few days ago, and only had two people
comment on our scheduling.  At that time, the discussion was for 7.4
beta starting May 1 vs June 1.  June 1 would be roughly six months from
our 7.3 final release, which is typical.

I agree with Tom that we should decided sooner rather than later on a
beta date.  Right now I don't think we have enough to must-have features
to justify a release, and as everyone knows, an upgrade isn't easy for
our users.

I also agree with Tom that we shouldn't peg our beta schedule on specific
features.

So, what should we do?  Should we go another month or two and just wait
until we have enough must-have features?  While not waiting on specific
features, it _is_ waiting for something to warrant a release.  I guess
the big question is whether we release on a scheduled-basis or a
enough-features-basis.

-- 
  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, Pennsylvania 19073

Beta Schedule (was Re: Roadmap for FE/BE protocol redesign)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-10T23:36:27Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> So, what should we do?  Should we go another month or two and just wait
> until we have enough must-have features?  While not waiting on specific
> features, it _is_ waiting for something to warrant a release.  I guess
> the big question is whether we release on a scheduled-basis or a
> enough-features-basis.

"Enough features" is such a judgment call that no one can predict what
the schedule will be, if that's part of the decision.

I had been leaning to May 1 beta, but am happy to switch to June 1 if
you feel that makes an improvement in the odds of completing the Windows
port.  (I think it will also improve the odds of finishing this protocol
stuff I've taken on...)  I don't want to see it pushed further than that
without good concrete arguments for doing so.

			regards, tom lane

Re: Beta Schedule (was Re: Roadmap for FE/BE protocol redesign)

Joe Conway <mail@joeconway.com> — 2003-03-11T00:30:35Z

Tom Lane wrote:
> "Enough features" is such a judgment call that no one can predict what
> the schedule will be, if that's part of the decision.
> 
> I had been leaning to May 1 beta, but am happy to switch to June 1 if
> you feel that makes an improvement in the odds of completing the Windows
> port.  (I think it will also improve the odds of finishing this protocol
> stuff I've taken on...)  I don't want to see it pushed further than that
> without good concrete arguments for doing so.
> 

FWIW, if we're voting, I'd say:

1. on "firm date" vs "when feature x is ready": I vote "firm date" -- I 
know that last August the firm beta start date motivated me to get some 
things done that I would have dragged out without the scheduled cutoff.

2. June 1 beta cutoff sounds about right.

Joe

Re: Roadmap for FE/BE protocol redesign

Justin Clift <justin@postgresql.org> — 2003-03-11T02:40:49Z

Bruce Momjian wrote:
<snip>
> So, what should we do?  Should we go another month or two and just wait
> until we have enough must-have features?  While not waiting on specific
> features, it _is_ waiting for something to warrant a release.  I guess
> the big question is whether we release on a scheduled-basis or a
> enough-features-basis.

Hmmm, I feel we should decide on features that will make an 8.0 release 
meaningful, and *somehow* work to making sure they are ready for the 
release.

With 7.1/7.2, Tom mentioned us being delayed because specific features 
we were waiting for became dependant on one person.

Would it be feasible to investigate approaches for having the Win32 and 
PITR work be shared amongst a few very-interested volunteers, so that 
people can cover for each other's downtime?  Not sure of the 
confidentiality level of the Win32/PITR patches at present, but I'd 
guess there would be at least a few solid volunteers willing to 
contribute to the Win32/PITR ports if we asked for people to step forwards.

:-)

Regards and best wishes,

Justin Clift

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

Re: Beta Schedule (was Re: Roadmap for FE/BE protocol redesign)

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-03-11T02:43:21Z

> I had been leaning to May 1 beta, but am happy to switch to June 1 if
> you feel that makes an improvement in the odds of completing the Windows
> port.  (I think it will also improve the odds of finishing this protocol
> stuff I've taken on...)  I don't want to see it pushed further than that
> without good concrete arguments for doing so.

There really is no rush...

I'm well-versed in PostgreSQL, and even I haven't upgraded some of our
production servers to even 7.3 yet (thanks to pg_dump dependency
nightmare)..

BTW, so no-one conflicts, I'm doing up pg_get_triggerdef(oid) at the moment.
Once that's done, I'll be able to submit a redone psql \d output that
includes the trigger definition.

Cheers,

Chris

Re: Beta schedule (was Roadmap for FE/BE protocol redesign)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-11T05:32:01Z

Justin Clift <justin@postgresql.org> writes:
> With 7.1/7.2, Tom mentioned us being delayed because specific features 
> we were waiting for became dependant on one person.

> Would it be feasible to investigate approaches for having the Win32 and 
> PITR work be shared amongst a few very-interested volunteers, so that 
> people can cover for each other's downtime?

It would certainly be good to bring as much manpower to bear on those
problems as we can.  But that doesn't really address my concern: if the
schedule is defined as "we go beta when feature X is done", then no one
who's working on stuff other than feature X knows how to plan their
time.  The only fair way to run the project is "we go beta at time T";
that way everyone knows what they need to shoot for and can plan
accordingly.

I don't mind setting the planned time T on the basis of what we think
it will take for certain popular feature X's to be done.  But if the
guys working on X aren't done at T, it's not fair to everyone else to
hold our breaths waiting for them to be done at T-plus-who-knows-what.

I don't really have any sympathy for the argument that "it won't be a
compelling release if we don't have feature X".  If the release isn't
compelling for someone, they don't have to upgrade; they can wait for
the next release.  The folks who *are* eager for what's been gotten done
will be glad of having a release now rather than N months from now.
And do I need to point out that "it runs on Windoze" is not of
earth-shattering importance for everyone?

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Justin Clift <justin@postgresql.org> — 2003-03-11T07:24:24Z

Tom Lane wrote:
> Justin Clift <justin@postgresql.org> writes:
> 
>>The scenario that's appealing to me the most is this for the next release:
>>PostgreSQL 8.0
>>+ Includes PITR and the Win32 port
> 
> If the folks doing those things can get done in time, great.  I'm even
> willing to push out the release schedule (now, not later) to make it
> more likely they can get done.  What I'm not willing to do is define
> the release in terms of "it happens when these things are done".  We
> learned the folly of that approach in 7.1 and 7.2.  Setting a target
> date and sticking to it works *much* better.

Yep, we both seem to be saying that we'd like these features, but we 
don't want to see them become delay-points.


>>+ Not sure where Satoshi is up to with his 2 phase commit proposal, but 
>>that might make sense to incorporate into a wire protocol revision. 
> 
> I can't see any need for protocol-level support for such a thing.
> Why wouldn't it just be some more SQL commands?

Not sure.  It seems like 2PC will be required/desirable within the year 
for better support of some clustering scenarios, so we "might as well 
look at it now".  When I was reading Satoshi's stuff a while ago I 
thought it was a protcol level thing, not a SQL command level thing, but 
don't really care either way.  :)

Regards and best wishes,

Justin Clift


> (Not that I believe in 2PC as a real-world solution anyway, but that's
> a different argument...)
>
> 			regards, tom lane


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

Re: Roadmap for FE/BE protocol redesign

Inoue, Hiroshi <inoue@tpf.co.jp> — 2003-03-11T10:07:07Z

Tom Lane wrote:
> 
> > I think such compatibility is sufficient.  We can mention in the
> > releases notes that they should upgrade there servers before their
> > clients.
> 
> I'd be really happy if we can make that stick.  There's enough work to
> be done here without trying to develop a multiprotocol version of
> libpq.
> 
> It would be good to hear some words from the JDBC and ODBC developers
> about what sort of plans they'd have for updating those interfaces.

Psqlodbc driver couldn't use the library unless
the library could handle multiple protocol.

What the driver has suffered from is to get the
fields' info of a query result or the parameters'
info of a statement. The info is needed even before
the execution of the statement(i.e it's only prepared).

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Re: Beta schedule (was Roadmap for FE/BE protocol redesign)

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-11T11:31:26Z

I agree, let's not wait for specific features.  The issue was whether we
had enough significant features done for a release --- I didn't think we
did, so I am saying, let's get more features, rather than let's get
feature X.

As we fill in missing features, there will be less must-have features to
add, so we are left with continuing with our present release pace or
releasing less frequently with the same number of feature additions.

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

Tom Lane wrote:
> Justin Clift <justin@postgresql.org> writes:
> > With 7.1/7.2, Tom mentioned us being delayed because specific features 
> > we were waiting for became dependant on one person.
> 
> > Would it be feasible to investigate approaches for having the Win32 and 
> > PITR work be shared amongst a few very-interested volunteers, so that 
> > people can cover for each other's downtime?
> 
> It would certainly be good to bring as much manpower to bear on those
> problems as we can.  But that doesn't really address my concern: if the
> schedule is defined as "we go beta when feature X is done", then no one
> who's working on stuff other than feature X knows how to plan their
> time.  The only fair way to run the project is "we go beta at time T";
> that way everyone knows what they need to shoot for and can plan
> accordingly.
> 
> I don't mind setting the planned time T on the basis of what we think
> it will take for certain popular feature X's to be done.  But if the
> guys working on X aren't done at T, it's not fair to everyone else to
> hold our breaths waiting for them to be done at T-plus-who-knows-what.
> 
> I don't really have any sympathy for the argument that "it won't be a
> compelling release if we don't have feature X".  If the release isn't
> compelling for someone, they don't have to upgrade; they can wait for
> the next release.  The folks who *are* eager for what's been gotten done
> will be glad of having a release now rather than N months from now.
> And do I need to point out that "it runs on Windoze" is not of
> earth-shattering importance for everyone?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-11T14:45:32Z

Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> What the driver has suffered from is to get the
> fields' info of a query result or the parameters'
> info of a statement. The info is needed even before
> the execution of the statement(i.e it's only prepared).

Hm.  Are you saying that you would like PREPARE to send back a
RowDescription ('T') message?  Or is there more to it than that?

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-12T01:17:51Z

On Mon, 10 Mar 2003, Tom Lane wrote:

> One of the $64 questions that has to be answered is how much work we're
> willing to expend on backwards compatibility.  The path of least
> resistance would be to handle it the same way we've done protocol
> revisions in the past: the backend will be able to handle both old and
> new protocols (so it can talk to old clients) but libpq would be revised
> to speak only the new protocol (so new/recompiled clients couldn't talk
> to old backends).  We've gotten away with this approach in the past, but
> the last time was release 6.4.  I fully expect to hear more complaints
> now.

Personally ... as long as a v8.x client can talk to a v7.x backend, you
have my vote ... I'm more apt to upgrade my clients before my servers
anyway ...

Re: Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-12T01:19:56Z

On Mon, 10 Mar 2003, Tom Lane wrote:

> Justin Clift <justin@postgresql.org> writes:
> > The scenario that's appealing to me the most is this for the next release:
> > PostgreSQL 8.0
> > + Includes PITR and the Win32 port
>
> If the folks doing those things can get done in time, great.  I'm even
> willing to push out the release schedule (now, not later) to make it
> more likely they can get done.  What I'm not willing to do is define
> the release in terms of "it happens when these things are done".  We
> learned the folly of that approach in 7.1 and 7.2.  Setting a target
> date and sticking to it works *much* better.

The thing is, IMHO, everyone knew the release scheduale for v7.4, so if
they aren't ready now, I can't really see justifying pushing things back
in hopes that they will be ready then ...

Re: Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-12T01:21:26Z

On Mon, 10 Mar 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Justin Clift <justin@postgresql.org> writes:
> > > The scenario that's appealing to me the most is this for the next release:
> > > PostgreSQL 8.0
> > > + Includes PITR and the Win32 port
> >
> > If the folks doing those things can get done in time, great.  I'm even
> > willing to push out the release schedule (now, not later) to make it
> > more likely they can get done.  What I'm not willing to do is define
> > the release in terms of "it happens when these things are done".  We
> > learned the folly of that approach in 7.1 and 7.2.  Setting a target
> > date and sticking to it works *much* better.
>
> Well, we had that 7.4 discussion a few days ago, and only had two people
> comment on our scheduling.  At that time, the discussion was for 7.4
> beta starting May 1 vs June 1.  June 1 would be roughly six months from
> our 7.3 final release, which is typical.
>
> I agree with Tom that we should decided sooner rather than later on a
> beta date.  Right now I don't think we have enough to must-have features
> to justify a release, and as everyone knows, an upgrade isn't easy for
> our users.
>
> I also agree with Tom that we shouldn't peg our beta schedule on specific
> features.
>
> So, what should we do?  Should we go another month or two and just wait
> until we have enough must-have features?  While not waiting on specific
> features, it _is_ waiting for something to warrant a release.  I guess
> the big question is whether we release on a scheduled-basis or a
> enough-features-basis.

Schedualed basis ... if we released on an 'enough features basis', I could
see alot longer then 6 mos between releases happening very quickly ... we
have enough problems staying within the scheduale as it is, let alot
moving it to a 'sliding scale' ...

Re: Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-12T01:23:51Z

On Tue, 11 Mar 2003, Justin Clift wrote:

> Bruce Momjian wrote:
> <snip>
> > So, what should we do?  Should we go another month or two and just wait
> > until we have enough must-have features?  While not waiting on specific
> > features, it _is_ waiting for something to warrant a release.  I guess
> > the big question is whether we release on a scheduled-basis or a
> > enough-features-basis.
>
> Hmmm, I feel we should decide on features that will make an 8.0 release
> meaningful, and *somehow* work to making sure they are ready for the
> release.
>
> With 7.1/7.2, Tom mentioned us being delayed because specific features
> we were waiting for became dependant on one person.
>
> Would it be feasible to investigate approaches for having the Win32 and
> PITR work be shared amongst a few very-interested volunteers, so that
> people can cover for each other's downtime?  Not sure of the
> confidentiality level of the Win32/PITR patches at present, but I'd
> guess there would be at least a few solid volunteers willing to
> contribute to the Win32/PITR ports if we asked for people to step
> forwards.

Why should we be the ones to ask for ppl to step forward to volunteer to
help?  Shouldn't it be the responsibility of the developer working on it
to admit that there is no way they will make the scheduale and call for
help?

Re: Roadmap for FE/BE protocol redesign

Inoue, Hiroshi <inoue@tpf.co.jp> — 2003-03-12T01:44:23Z

Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > What the driver has suffered from is to get the
> > fields' info of a query result or the parameters'
> > info of a statement. The info is needed even before
> > the execution of the statement(i.e it's only prepared).
> 
> Hm.  Are you saying that you would like PREPARE to send back a
> RowDescription ('T') message?

I'm not sure if PREPARE should return the info directly.
Maybe it should be returned only when it is requested.

> Or is there more to it than that?

More detailed field info is needed anyway.
For example the RowDescription contains neither 
the base column name, the table name nor the
schema name currently and so the current odbc
driver couldn't return the info correctly.
The ODBC function SQLDescribeCol or SQLColAttribute
requires various kind of fields' info.

It's almost impossible to get parameters' info
currently. The ODBC function SQLDescribeParam
isn't implemented yet....

regards,
Hiroshi Inoue
	http://www.geocities.jp/inocchichichi/psqlodbc/

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-12T02:02:06Z

Marc G. Fournier wrote:
> On Mon, 10 Mar 2003, Tom Lane wrote:
> 
> > One of the $64 questions that has to be answered is how much work we're
> > willing to expend on backwards compatibility.  The path of least
> > resistance would be to handle it the same way we've done protocol
> > revisions in the past: the backend will be able to handle both old and
> > new protocols (so it can talk to old clients) but libpq would be revised
> > to speak only the new protocol (so new/recompiled clients couldn't talk
> > to old backends).  We've gotten away with this approach in the past, but
> > the last time was release 6.4.  I fully expect to hear more complaints
> > now.
> 
> Personally ... as long as a v8.x client can talk to a v7.x backend, you
> have my vote ... I'm more apt to upgrade my clients before my servers
> anyway ...

Actually, it is usually the opposite, where old clients can talk to
newer servers, but not the reverse.

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-12T03:35:42Z

On Tue, 11 Mar 2003, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > On Mon, 10 Mar 2003, Tom Lane wrote:
> >
> > > One of the $64 questions that has to be answered is how much work we're
> > > willing to expend on backwards compatibility.  The path of least
> > > resistance would be to handle it the same way we've done protocol
> > > revisions in the past: the backend will be able to handle both old and
> > > new protocols (so it can talk to old clients) but libpq would be revised
> > > to speak only the new protocol (so new/recompiled clients couldn't talk
> > > to old backends).  We've gotten away with this approach in the past, but
> > > the last time was release 6.4.  I fully expect to hear more complaints
> > > now.
> >
> > Personally ... as long as a v8.x client can talk to a v7.x backend, you
> > have my vote ... I'm more apt to upgrade my clients before my servers
> > anyway ...
>
> Actually, it is usually the opposite, where old clients can talk to
> newer servers, but not the reverse.

D'oh, mis-read Tom's ... you are correct, and it does make sense to do so
... its not like old libraries aren't available if someone wanted to make
a pre-compiled version that is 'backwards compatible' ...

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-12T03:50:47Z

Marc G. Fournier wrote:
> > So, what should we do?  Should we go another month or two and just wait
> > until we have enough must-have features?  While not waiting on specific
> > features, it _is_ waiting for something to warrant a release.  I guess
> > the big question is whether we release on a scheduled-basis or a
> > enough-features-basis.
> 
> Schedualed basis ... if we released on an 'enough features basis', I could
> see alot longer then 6 mos between releases happening very quickly ... we
> have enough problems staying within the scheduale as it is, let alot
> moving it to a 'sliding scale' ...

I guess the big question is that if we can't get enough big features in
6 months, do we still stay on the 6 month schedule?  I know Tom said
folks don't have to upgrade --- that is true, but our releases do seem a
little lighter lately.

Six months would be June 1 beta, so maybe that is still a good target. 
I agree we should not hold up beta for any feature.  So maybe the plan
is June 1 beta, and we don't care if we have enough big features or not
--- does that sound good to everyone?  Or should we be looking at May 1
as Tom originally suggested?

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Greg Stark <gsstark@mit.edu> — 2003-03-12T05:10:31Z

"Marc G. Fournier" <scrappy@hub.org> writes:

> Personally ... as long as a v8.x client can talk to a v7.x backend, you
> have my vote ... I'm more apt to upgrade my clients before my servers
> anyway ...

Surely that's not true for a production environment. You have one database but
potentially dozens of various programs around that access it. The main
application, some backend scripts for batch jobs, your backup process, your
monitoring systems... Not all of these are necessarily on the same machine.

It's upgrading the database that's likely to be the driving motivation for new
sql or storage features. People usually don't get excited about upgrading the
client libraries :)

--
greg

Re: Roadmap for FE/BE protocol redesign

Justin Clift <justin@postgresql.org> — 2003-03-12T05:24:58Z

Marc G. Fournier wrote:
<snip>
 >>Would it be feasible to investigate approaches for having the Win32 and
 >>PITR work be shared amongst a few very-interested volunteers, so that
 >>people can cover for each other's downtime?  Not sure of the
 >>confidentiality level of the Win32/PITR patches at present, but I'd
 >>guess there would be at least a few solid volunteers willing to
 >>contribute to the Win32/PITR ports if we asked for people to step
 >>forwards.
 >
 > Why should we be the ones to ask for ppl to step forward to volunteer to
 > help?  Shouldn't it be the responsibility of the developer working on it
 > to admit that there is no way they will make the scheduale and call for
 > help?

Hadn't thought of that.  Um.. how about "whatever works"?  It's sounds 
like the kind of thing where some people may be offended if we suddenly 
started asking for extra volunteers for their bits, and others in the 
same situation wouldn't.

Now that you mention it, it's probably a matter of getting that bit 
correct I suppose.  :)

Regards and best wishes,

Justin Clift

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

Re: Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-12T08:06:51Z

> * Backend should pass its version number, database encoding, default
> client encoding, and possibly other data (any ideas?) to frontend during
> startup, to avoid need for explicit queries to get this info.  We could
> also consider eliminating SET commands sent by libpq in favor of adding
> variable settings to startup packet's PGOPTIONS field.  Ideally we could
> get back to the point where a standard connection startup takes only one
> packet in each direction.

This handles the JDBC needs (currently on startup the jdbc driver 
selects the database encoding and version number and sets the datestyle 
and autocommit parameters).

One addition I would personally like to see (it comes up in my apps 
code) is the ability to detect wheather the server is big endian or 
little endian.  When using binary cursors this is necessary in order to 
read int data.  Currently I issue a 'select 1' statement at connection 
startup to determine what format the server is using.


Other things I would like to see to help jdbc:

1) More information about the attributes selected in a query (I see 
there is an entire thread on this already) to minimize the work 
necessary to implement updateable result sets as defined by the jdbc spec.

2) Better support for domains.  Currently the jdbc driver is broken with 
regards to domains (although no one has reported this yet).  The driver 
will treat a datatype that is a domain as an unknown/unsupported 
datatype.  It would be great if the T response included the 'base' 
datatype for a domain attribute so that the driver would know what 
parsing routines to call to convert to/from the text representation the 
backend expects.

3) Protocol level support for CURSORs.  It would be nice if cursor 
support was done at the protocol level and not as a SQL command.  The 
current default behavior of returning all results from a query in the 
query response message is often a problem (can easily lead to out of 
memory problems for poorly written queries).  So it is desirable to use 
cursors.  But with the current implementation in SQL, cursors are not 
the appropriate choice if a query is only going to return one or a few 
rows.  The reason is that using a cursor requires a minimum of three SQL 
statements:  DECLARE, FETCH, CLOSE.  The jdbc driver issues the DECLARE 
and FETCH in one server call, but the CLOSE needs to be a second call. 
Thus for simple one row selects (which in many cases are the majority of 
selects issued) using CURSORS requires two roundtrips to the server vs. 
one for the nonCursor case.
This leaves me with a problem in the jdbc driver, I can either use 
standard fast/performant queries for single row selects that blowup with 
out of memory errors for large results, or I can use cursors and avoid 
large memory usage but hurt overall performance.  What I have currently 
done is require that the developer call an extra method to turn on the 
use of cursors when they know that the cursor is going to return a large 
number of rows and leave the default be the non-cursor case.  This works 
but requires that developers who are writing code to interact with 
multiple different databases, code differently for the postgres jdbc 
driver.  And this is a problem since one of the goals of jdbc is to be 
able to write code that works against multiple different databases.
So I would request the ability of the client to set a max rows parameter 
   for query results.  If a query were to return more than the max 
number of rows, the client would be given a handle (essentially a cursor 
name) that it could use to fetch additional sets of rows.

4) Protocol level support of PREPARE.  In jdbc and most other 
interfaces, there is support for parameterized SQL.  If you want to take 
advantage of the performance benefits of reusing parsed plans you have 
to use the PREPARE SQL statement.  My complaint on doing this at the SQL 
level vs the protocol level is similar to the problem with cursors 
above.  To use prepare you need to issue three SQL statements: PREPARE, 
EXCECUTE, DEALLOCATE.  If you know ahead of time that you are going to 
reuse a statement many times doing PREPARE, EXECUTE, EXECUTE, ..., 
DEALLOCATE makes sense and can be a big win in performance.  However if 
you only ever execute the statement once then you need to use two round 
trips (one for the PREPARE, EXECUTE and another for the DEALLOCATE) 
versus one round trip to execute the statement 'normally'.  So it 
decreases performance to use prepares for all parameterized sql 
statements.  So the current implementation in jdbc requires the user to 
issue a postgres specific call to turn on the use of prepared statements 
for those cases the developer knows will be a performance win.  But this 
requires coding differently for postgres jdbc than for other databases.
So being better able to handle this in the protocol would be nice.

5) Better support for "large values".  Generally I recommend that users 
of jdbc use bytea to store large binary values.  I generally tell people 
to avoid using LOs (Large Objects).  The reason for this is that LOs 
have two significant problems:  1) security - any user on the database 
can access all LOs even though they may not be able to access the row 
that contains the LO reference, 2) cleanup - deleting the row containing 
the LO reference doesn't delete the LO requireing extra code or triggers 
to behave like a regular value in a regular column.  Bytea works OK for 
small to medium sized values, but doesn't work for very large values, 
where by very large I mean over a few Megabytes.  The reason very large 
values are a problem is memory usage.  There is no way to 'stream' bytea 
values from the server like you can do with LOs, so the driver ends up 
storeing the entire value in memory as it reads the result from the 
backend for a query.  And if the query returns multiple rows each with a 
large value you quickly run out of memory.
So what I would like to see is the ability for the client to set a MAX 
VALUE size parameter.  The server would send up to this amount of data 
for any column.  If the value was longer than MAX VALUE, the server 
would respond with a handle that the client could use to get the rest of 
the value (in chunks of MAX VALUE) if it wanted to.  This would allow 
the client to get the entire result set which could contain perhaps many 
large bytea values, but not use a lot of memory up front.  Then fetch 
the entire values only when/if the application asked for them and stream 
the result to the application and never bring the entire contents of the 
column into memory at once.  (There are probably a number of different 
implementation posibilities so use this one as a suggestion to explain 
what I would like to see not necessarily how it should be implemented).

6)  Better over the wire support for bytea.  The current encoding of 
binary data \000 results in a significant expansion in the size of data 
transmitted.  It would be nice if bytea data didn't result in 2 or 3 
times data expansion. (and all the cpu cycles to convert to/from the 
escaped format).  This may not be a protocol issue, but IMHO the best 
way to fix this would be in the protocol.

thanks,
--Barry

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-12T16:19:40Z

Barry Lind <blind@xythos.com> writes:
> One addition I would personally like to see (it comes up in my apps 
> code) is the ability to detect wheather the server is big endian or 
> little endian.  When using binary cursors this is necessary in order to 
> read int data.

Actually, my hope is to eliminate that business entirely by
standardizing the on-the-wire representation for binary data; note the
reference to send/receive routines in the original message.  For integer
data this is simple enough: network byte order will be it.  I'm not sure
yet what to do about float data.

> 2) Better support for domains.  Currently the jdbc driver is broken with 
> regards to domains (although no one has reported this yet).  The driver 
> will treat a datatype that is a domain as an unknown/unsupported 
> datatype.  It would be great if the T response included the 'base' 
> datatype for a domain attribute so that the driver would know what 
> parsing routines to call to convert to/from the text representation the 
> backend expects.

I'm unconvinced that we need do this in the protocol, as opposed to
letting the client figure it out with metadata inquiries.  If we should,
I'd be inclined to just replace the typeid field with the base typeid,
and not mention the domain to the frontend at all.  Comments?

> So I would request the ability of the client to set a max rows parameter 
>    for query results.  If a query were to return more than the max 
> number of rows, the client would be given a handle (essentially a cursor 
> name) that it could use to fetch additional sets of rows.

How about simply erroring out if the query returns more than X rows?

> 4) Protocol level support of PREPARE.  In jdbc and most other 
> interfaces, there is support for parameterized SQL.  If you want to take 
> advantage of the performance benefits of reusing parsed plans you have 
> to use the PREPARE SQL statement.

This argument seems self-contradictory to me.  There is no such benefit
unless you're going to re-use the statement many times.  Nor do I see
how pushing PREPARE down to the protocol level will create any
improvement in its performance.

> So what I would like to see is the ability for the client to set a MAX 
> VALUE size parameter.  The server would send up to this amount of data 
> for any column.  If the value was longer than MAX VALUE, the server 
> would respond with a handle that the client could use to get the rest of 
> the value (in chunks of MAX VALUE) if it wanted to.

I don't think I want to embed this in the protocol, either; especially
not when we don't have even the beginnings of backend support for it.
I think such a feature should be implemented and proven as callable
functions first, and then we could think about pushing it down into the
protocol.

> 6)  Better over the wire support for bytea.  The current encoding of 
> binary data \000 results in a significant expansion in the size of data 
> transmitted.  It would be nice if bytea data didn't result in 2 or 3 
> times data expansion.

AFAICS the only context where this could make sense is binary
transmission of parameters for a previously-prepared statement.  We do
have all the pieces for that on the roadmap.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-03-13T01:57:43Z

> > One addition I would personally like to see (it comes up in my apps
> > code) is the ability to detect wheather the server is big endian or
> > little endian.  When using binary cursors this is necessary in order to
> > read int data.
>
> Actually, my hope is to eliminate that business entirely by
> standardizing the on-the-wire representation for binary data; note the
> reference to send/receive routines in the original message.  For integer
> data this is simple enough: network byte order will be it.  I'm not sure
> yet what to do about float data.

Is that something that the 'XDR' spec deals with?  (Thinking back to 3rd
year networking unit)...

Chris

Re: Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-13T02:27:57Z


Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
> 
>>One addition I would personally like to see (it comes up in my apps 
>>code) is the ability to detect wheather the server is big endian or 
>>little endian.  When using binary cursors this is necessary in order to 
>>read int data.
> 
> 
> Actually, my hope is to eliminate that business entirely by
> standardizing the on-the-wire representation for binary data; note the
> reference to send/receive routines in the original message.  For integer
> data this is simple enough: network byte order will be it.  I'm not sure
> yet what to do about float data.
> 

Great.

> 
>>2) Better support for domains.  Currently the jdbc driver is broken with 
>>regards to domains (although no one has reported this yet).  The driver 
>>will treat a datatype that is a domain as an unknown/unsupported 
>>datatype.  It would be great if the T response included the 'base' 
>>datatype for a domain attribute so that the driver would know what 
>>parsing routines to call to convert to/from the text representation the 
>>backend expects.
> 
> 
> I'm unconvinced that we need do this in the protocol, as opposed to
> letting the client figure it out with metadata inquiries.  If we should,
> I'd be inclined to just replace the typeid field with the base typeid,
> and not mention the domain to the frontend at all.  Comments?
> 

I don't have a strong opinion on this one.  I can live with current 
functionality.  It isn't too much work to look up the base type.

> 
>>So I would request the ability of the client to set a max rows parameter 
>>   for query results.  If a query were to return more than the max 
>>number of rows, the client would be given a handle (essentially a cursor 
>>name) that it could use to fetch additional sets of rows.
> 
> 
> How about simply erroring out if the query returns more than X rows?
> 
This shouldn't be an error condition.  I want to fetch all of the rows, 
I just don't want to have to buffer them all in memory.  Consider the 
following example.  Select statement #1 is 'select id from foo', 
statement #2 is 'update bar set x = y where foo_id = ?'.  The program 
logic issues statement #1 and then starts iterating through the results 
and the issues statement #2 for some of those results.  If statement #1 
returns a large number of rows the program can run out of memory if all 
the rows from #1 need to be buffered in memory.  What would be nice is 
if the protocol allowed getting some rows from #1 but not all so that 
the connection could be used to issue some #2 statements.

> 
>>4) Protocol level support of PREPARE.  In jdbc and most other 
>>interfaces, there is support for parameterized SQL.  If you want to take 
>>advantage of the performance benefits of reusing parsed plans you have 
>>to use the PREPARE SQL statement.
> 
> 
> This argument seems self-contradictory to me.  There is no such benefit
> unless you're going to re-use the statement many times.  Nor do I see
> how pushing PREPARE down to the protocol level will create any
> improvement in its performance.
> 
There is a benefit if you do reuse the statement multiple times.  The 
performance problem is the two round trips minimum to the server that 
are required.  A protocol solution to this would be to allow the client 
to send multiple requests at one time to the server.  But as I type that 
I realize that can already be done, by having multiple semi-colon 
separated SQL commands sent at once.  So I probably have everything I 
need for this already.  I can just cue up the 'deallocate' calls and 
piggyback them on to the next real call to the server.

> 
>>So what I would like to see is the ability for the client to set a MAX 
>>VALUE size parameter.  The server would send up to this amount of data 
>>for any column.  If the value was longer than MAX VALUE, the server 
>>would respond with a handle that the client could use to get the rest of 
>>the value (in chunks of MAX VALUE) if it wanted to.
> 
> 
> I don't think I want to embed this in the protocol, either; especially
> not when we don't have even the beginnings of backend support for it.
> I think such a feature should be implemented and proven as callable
> functions first, and then we could think about pushing it down into the
> protocol.
> 

That is fine.

> 
>>6)  Better over the wire support for bytea.  The current encoding of 
>>binary data \000 results in a significant expansion in the size of data 
>>transmitted.  It would be nice if bytea data didn't result in 2 or 3 
>>times data expansion.
> 
> 
> AFAICS the only context where this could make sense is binary
> transmission of parameters for a previously-prepared statement.  We do
> have all the pieces for that on the roadmap.
> 
Actually it is the select of binary data that I was refering to.  Are 
you suggesting that the over the wire format for bytea in a query result 
will be binary (instead of the ascii encoded text format as it currently 
exists)?

> 			regards, tom lane
> 

I am looking forward to all of the protocol changes.

thanks,
--Barry

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-13T05:37:58Z

Greg Stark <gsstark@mit.edu> writes:
> It's upgrading the database that's likely to be the driving motivation
> for new sql or storage features. People usually don't get excited
> about upgrading the client libraries :)

Usually not.  This cycle might be different though, if we are able to
finish the proposed improvements in error reporting and other issues
that are handicapping clients.  None of that work will help un-upgraded
clients...

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Hannu Krosing <hannu@tm.ee> — 2003-03-13T06:29:19Z

Greg Stark kirjutas K, 12.03.2003 kell 07:10:
> "Marc G. Fournier" <scrappy@hub.org> writes:
> 
> > Personally ... as long as a v8.x client can talk to a v7.x backend, you
> > have my vote ... I'm more apt to upgrade my clients before my servers
> > anyway ...
> 
> Surely that's not true for a production environment. You have one database but
> potentially dozens of various programs around that access it. The main
> application, some backend scripts for batch jobs, your backup process, your
> monitoring systems... Not all of these are necessarily on the same machine.

For more radical protocol changes a viable approach could be "protocol
proxies", i.e. set up a _separate_ daemon which listens on a separate
port and translates v7.x wire protocol to v8.x of the database proper.

Then those needing it can keep it around and those who need it not don't
get the overhead. It could also be maintained by inerested parties long
after being dropped by core developers.

> It's upgrading the database that's likely to be the driving motivation for new
> sql or storage features. People usually don't get excited about upgrading the
> client libraries :)

But our SQL itself is slowly drifting towards ANSI/ISO compliance and
that has often brought subtle changes that break _applications_. It is
not a big issue to changes libraries if you have to change the
application anyway.

-----------------
Hannu

Re: Roadmap for FE/BE protocol redesign

Hannu Krosing <hannu@tm.ee> — 2003-03-13T06:46:13Z

Tom Lane kirjutas K, 12.03.2003 kell 18:19:
> Barry Lind <blind@xythos.com> writes:
> > One addition I would personally like to see (it comes up in my apps 
> > code) is the ability to detect wheather the server is big endian or 
> > little endian.  When using binary cursors this is necessary in order to 
> > read int data.
> 
> Actually, my hope is to eliminate that business entirely by
> standardizing the on-the-wire representation for binary data; note the
> reference to send/receive routines in the original message.  For integer
> data this is simple enough: network byte order will be it.  I'm not sure
> yet what to do about float data.

Use IEEE floats or just report the representation in startup packet.

the X11 protocol does this for all data, even integers - the client
expresses a wish what it wants and the server tells it what it gets (so
two intel boxes need not to convert to "network byte order" at both
ends).

> > 2) Better support for domains.  Currently the jdbc driver is broken with 
> > regards to domains (although no one has reported this yet).  The driver 
> > will treat a datatype that is a domain as an unknown/unsupported 
> > datatype.  It would be great if the T response included the 'base' 
> > datatype for a domain attribute so that the driver would know what 
> > parsing routines to call to convert to/from the text representation the 
> > backend expects.
> 
> I'm unconvinced that we need do this in the protocol, as opposed to
> letting the client figure it out with metadata inquiries.  If we should,
> I'd be inclined to just replace the typeid field with the base typeid,
> and not mention the domain to the frontend at all.  Comments?
> 
> > So I would request the ability of the client to set a max rows parameter 
> >    for query results.  If a query were to return more than the max 
> > number of rows, the client would be given a handle (essentially a cursor 
> > name) that it could use to fetch additional sets of rows.
> 
> How about simply erroring out if the query returns more than X rows?

Or just using prepare/execute - fetch - fetch - fetch ...

> > 4) Protocol level support of PREPARE.  In jdbc and most other 
> > interfaces, there is support for parameterized SQL.  If you want to take 
> > advantage of the performance benefits of reusing parsed plans you have 
> > to use the PREPARE SQL statement.
> 
> This argument seems self-contradictory to me.  There is no such benefit
> unless you're going to re-use the statement many times.  Nor do I see
> how pushing PREPARE down to the protocol level will create any
> improvement in its performance.

I suspect that he actually means support for binary transmission of
parameters for a previously-prepared statement here.

> > So what I would like to see is the ability for the client to set a MAX 
> > VALUE size parameter.  The server would send up to this amount of data 
> > for any column.  If the value was longer than MAX VALUE, the server 
> > would respond with a handle that the client could use to get the rest of 
> > the value (in chunks of MAX VALUE) if it wanted to.
>
> I don't think I want to embed this in the protocol, either; especially
> not when we don't have even the beginnings of backend support for it.
> I think such a feature should be implemented and proven as callable
> functions first, and then we could think about pushing it down into the
> protocol.

IIRC, Oracle has such a feature in its support for Large Objects (LONG
datatype). If the object data is longer than xxx bytes you will need
special ized access to it.

also when stepping with single fetches, you will always get handles for
LONG objects, if fetching more than one row you'll get raw data. 

BTW, I'm not advocating such behaviour .


----------------
Hannu

Re: Roadmap for FE/BE protocol redesign

Sean Chittenden <sean@chittenden.org> — 2003-03-13T07:04:35Z

> > One addition I would personally like to see (it comes up in my
> > apps code) is the ability to detect wheather the server is big
> > endian or little endian.  When using binary cursors this is
> > necessary in order to read int data.
> 
> Actually, my hope is to eliminate that business entirely by
> standardizing the on-the-wire representation for binary data; note
> the reference to send/receive routines in the original message.  For
> integer data this is simple enough: network byte order will be it.
> I'm not sure yet what to do about float data.

When were talking sending data across the wire, are we talking about a
format that would let the server use sendfile() for sending the data
to the client? Having a database that can send data to the client
efficiently would be a nice change of pace given most databases since
RDBMSs are notoriously slow (slower than NFS) at sending files to
clients.  -sc

-- 
Sean Chittenden

Re: Roadmap for FE/BE protocol redesign

Mike Mascari <mascarm@mascari.com> — 2003-03-13T07:33:27Z

Hannu Krosing wrote:
> Tom Lane kirjutas K, 12.03.2003 kell 18:19:
>
>>Actually, my hope is to eliminate that business entirely by
>>standardizing the on-the-wire representation for binary data; note the
>>reference to send/receive routines in the original message.  For integer
>>data this is simple enough: network byte order will be it.  I'm not sure
>>yet what to do about float data.
> 
> 
> Use IEEE floats or just report the representation in startup packet.
> 
> the X11 protocol does this for all data, even integers - the client
> expresses a wish what it wants and the server tells it what it gets (so
> two intel boxes need not to convert to "network byte order" at both
> ends).

IIOP/CDR behaves similarly for performance reasons- "receiver 
makes it right". It also defines a representation for all of the 
CORBA idl basic types, wide characters, fixed-point types, 
structures, etc. A far-reaching, wild suggestion would be to 
replace the postmaster with a CORBA-based server process with a 
well defined interface. At a minimum, if a binary protocol is 
the ultimate destination, perhaps some of the mapping of various 
types could be borrowed from the specs.

Mike Mascari
mascarm@mascari.com

Re: Roadmap for FE/BE protocol redesign

Greg Stark <gsstark@mit.edu> — 2003-03-13T15:29:03Z

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Barry Lind <blind@xythos.com> writes:
> 
> > 4) Protocol level support of PREPARE.  In jdbc and most other 
> > interfaces, there is support for parameterized SQL.  If you want to take 
> > advantage of the performance benefits of reusing parsed plans you have 
> > to use the PREPARE SQL statement.
> 
> This argument seems self-contradictory to me.  There is no such benefit
> unless you're going to re-use the statement many times.  Nor do I see
> how pushing PREPARE down to the protocol level will create any
> improvement in its performance.

"you're going to re-use the statement many times" is true (or should be true)
for every statement in every web site and other OLTP system. Even if the query
appears on only a single web page and is executed only once on that web page,
the nature of high volume web sites is that that page will be executed
hundreds or thousands of times per minute.

This is why the Perl DBI, for example, has a prepare_cached() which provides a
automatic caching of prepared handles. With Oracle I was able to use this
exclusively on a large high volume web site to keep thousands of prepared
handles. Every query was prepared only once per apache process.

There is a performance benefit to using placeholders and prepared queries in
that the plan doesn't need to be regenerated repeatedly. Ideally every query
should either be a big DSS query where the time spent in the optimizer is
irrelevant, or an OLTP transaction using placeholders where again the time
spent in the optimizer is irrelevant because it only needs to be run once.

This would allow the optimizer to grow in complexity. For example it could
explore both sides of the decision tree in places where now we have heuristics
to pick the probable better plan. Postgres's optimizer is pretty impressive
currently, but the constant attention to avoiding high cost optimizations
limits it.

There is also a security benefit. The idea of mixing parameters into the
queries even at the driver level gives me the willies. The database then has
to parse them back out of the query string. If there's a bug in the driver or
any kind of mismatch between the backend parser and the driver quoting then
there could be security holes.

--
greg

Re: Roadmap for FE/BE protocol redesign

Christof Petig <christof@petig-baender.de> — 2003-03-13T15:40:25Z

Barry Lind wrote:
> 3) Protocol level support for CURSORs.  It would be nice if cursor 
> support was done at the protocol level and not as a SQL command.

I want to second this proposal. Currently I avoid using cursors in my 
programs since
a) they need much more logic and _string_concatenation_ to be handled 
transparently by a library (prepend the query with DECLARE X CURSOR 
FOR), then (FETCH n FROM X), then (CLOSE X). That's inefficient.
b) I have a really bad feeling to have the backend parse (FETCH FROM X) 
every time I ask for a (single) row
c) I hate that the backend retransmits column names etc. for every fetch 
I issue. This information is mostly unneeded but the backend cannot know 
better

Of course these issues can be addressed by using FETCH n (n>10) but this 
  kludge is only needed because the FETCH protocol is so inefficient. 
Think about the amount of bytes transferred for "select 2000 lines of 
integers" with and without declare/fetch/close. Imagine a result set of 
1 to 20000 integers given back (depending on parameters) for an 
interactive program (e.g. browsing a customer list by initials). Prefer 
a cursor (much more constant overhead even for single results) or all in 
one (and wait longer for a first result)?

I'd love to tell the backend to give a "descriptor" for this query back 
and use it efficiently to get data and/or metadata (see ODBC, JDBC, 
sqlda or dynamic sql). Perhaps it's most efficient to ask for N initial 
results (which are instantly returned).

    Christof (who implemented dynamic sql for ecpg)

PS: perhaps this protocol infrastructure is also well suited to return 
large bytea values (<M bytes : return inline, > return a descriptor). 
[Also proposed by Barry Lind.]

PPS: I'm perfectly fine with returning attrelid/attnum. Then the client 
can control how many effort is spent for determining only the asked for 
metadata.

Re: Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-13T16:07:26Z

On Tue, 11 Mar 2003, Bruce Momjian wrote:

> Six months would be June 1 beta, so maybe that is still a good target.

We released v7.3 just before Dec 1st, so six months is May 1st, not June
1st ...

Re: Roadmap for FE/BE protocol redesign

Marc Fournier <scrappy@hub.org> — 2003-03-13T16:12:33Z

On Wed, 12 Mar 2003, Justin Clift wrote:

> Marc G. Fournier wrote:
> <snip>
>  >>Would it be feasible to investigate approaches for having the Win32 and
>  >>PITR work be shared amongst a few very-interested volunteers, so that
>  >>people can cover for each other's downtime?  Not sure of the
>  >>confidentiality level of the Win32/PITR patches at present, but I'd
>  >>guess there would be at least a few solid volunteers willing to
>  >>contribute to the Win32/PITR ports if we asked for people to step
>  >>forwards.
>  >
>  > Why should we be the ones to ask for ppl to step forward to volunteer to
>  > help?  Shouldn't it be the responsibility of the developer working on it
>  > to admit that there is no way they will make the scheduale and call for
>  > help?
>
> Hadn't thought of that.  Um.. how about "whatever works"?  It's sounds
> like the kind of thing where some people may be offended if we suddenly
> started asking for extra volunteers for their bits, and others in the
> same situation wouldn't.

The thing is, its not often, but ppl have been pop'ng up on the list
asking for things to do ... we have the TODO list that we point ppl to,
but if someone is working on a project and needs help, that is the perfect
time for them to pop up and try and lure the person over :)

But it also doesn't negate someone from asking for help as well ... hell,
how many ppl are working on something that someone else is working on from
a different angle, that would end up stronger by working together?

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-13T17:41:41Z

Barry Lind <blind@xythos.com> writes:
>> AFAICS the only context where this could make sense is binary
>> transmission of parameters for a previously-prepared statement.  We do
>> have all the pieces for that on the roadmap.
>> 
> Actually it is the select of binary data that I was refering to.  Are 
> you suggesting that the over the wire format for bytea in a query result 
> will be binary (instead of the ascii encoded text format as it currently 
> exists)?

See binary cursors ...

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-13T20:15:04Z

Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
> 
>>>AFAICS the only context where this could make sense is binary
>>>transmission of parameters for a previously-prepared statement.  We do
>>>have all the pieces for that on the roadmap.
>>>
>>
>>Actually it is the select of binary data that I was refering to.  Are 
>>you suggesting that the over the wire format for bytea in a query result 
>>will be binary (instead of the ascii encoded text format as it currently 
>>exists)?
> 
> 
> See binary cursors ...

Generally that is not an option.  It either requires users to code to 
postgresql specific sql syntax, or requires the driver to do it 
magically for them.  The later runs into all the issues that I raised on 
cursor support.

In general the jdbc driver is expected to execute arbitrary sql 
statements any application might want to send it.  The driver is 
handicaped because it doesn't know really anything about that sql 
statement (other than it is a select vs an update or delete). 
Specifically it doesn't know what tables or columns that SQL will access 
  or how many rows a select will return.  All of this knowledge is in 
the backend, and short of implementing a full sql parser in java this 
knowledge will never exist in the front end.  Many of the things I put 
on my wish list for the protocol stem from this.

Where there are two ways to do something (use cursors or not, use 
prepared statements or not, use binary cursors or not) the driver either 
needs to a) choose one way and always use it, b) infer from the sql 
statement which way will be better, or c) require the user to tell us. 
The problem with a) is that it may not always be the correct choice. 
The problem with b) is that generally this isn't possible and the 
problem with c) is it requires that the user write code that isn't 
portable across different databases.

I would like to simply do a) in all cases.  But that means that one of 
the two options should always (or almost always) be the best choice.  So 
in the case of "use cursors or not", it would be nice if using cursors 
added little or no overhead such that it could always be used.  In the 
case of "use prepared statements vs not", it would be nice if prepared 
statements added little or no overhead so that they could always be 
used.  And finally in the case of "use binary or regular cursors" it 
would be nice if binary cursors could always be used.

The Oracle SQLNet protocol supports most of this.  Though it has been a 
few years since I worked with it, the oracle protocol has many of the 
features I am looking for (and perhaps the reason I am looking for them, 
is that I have seen them used there before).  Essentially the Oracle 
protocol lets you do the following operations:  open, parse, describe, 
bind, execute, fetch, close.  A request from the client to the server 
specifies what operations it wants to perform on a sql statement.  So a 
client could request to do all seven operations (which is essentially 
what the current postgres protocol does today).  Or it could issue an 
open,parse call which essentially is that same thing as the PREPARE sql 
statement, followed by a describe,bind,execute,fetch which is similar to 
an EXECUTE and FETCH sql statement and finally a close which is similar 
to a CLOSE and DEALLOCATE sql.  The describe request is generally only 
done once even though you may do multiple fetchs (unlike todays protocol 
which includes the describe information on every fetch, even if you are 
fetching one row at a time).  The oracle approach gives the client 
complete flexibility to do a lot, without requiring that the client 
start parsing sql statements and doing things like appending on DECLARE 
CURSOR, or FETCH in order to reformate the applications sql statement 
into the postgresql sql way of doing this.

--Barry

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-13T22:40:35Z

Barry Lind <blind@xythos.com> writes:
> Tom Lane wrote:
>> See binary cursors ...

> Generally that is not an option.  It either requires users to code to 
> postgresql specific sql syntax, or requires the driver to do it 
> magically for them.

Fair enough.  I don't see anything much wrong with a GUC option that
says "send SELECT output in binary format".  This is not really a
protocol issue since the ASCII and BINARY choices both exist at the
protocol level --- there is nothing in the protocol saying binary data
can only be returned by FETCH and not by SELECT.  The main problem with
it in present releases is that binary data is architecture-dependent and
so encouraging its general use seems like a really bad idea.  But if we
manage to get send/receive conversion routines in there, most of that
issue would go away.

> The describe request is generally only 
> done once even though you may do multiple fetchs (unlike todays protocol 
> which includes the describe information on every fetch, even if you are 
> fetching one row at a time).

I'm less than excited about changing that, because it breaks clients
that don't want to remember past RowDescriptions (libpq being the
front-line victim), and it guarantees loss-of-synchronization failures
anytime the client misassociates rowdescription with query.  In exchange
for that, we get what exactly?  Fetching one row at a time is
*guaranteed* to be inefficient.  The correct response if that bothers
you is to fetch multiple rows at a time, not to make a less robust
protocol.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-14T00:31:04Z


Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
> 
>>Tom Lane wrote:
>>
>>>See binary cursors ...
> 
> 
>>Generally that is not an option.  It either requires users to code to 
>>postgresql specific sql syntax, or requires the driver to do it 
>>magically for them.
> 
> 
> Fair enough.  I don't see anything much wrong with a GUC option that
> says "send SELECT output in binary format".  This is not really a
> protocol issue since the ASCII and BINARY choices both exist at the
> protocol level --- there is nothing in the protocol saying binary data
> can only be returned by FETCH and not by SELECT.  The main problem with
> it in present releases is that binary data is architecture-dependent and
> so encouraging its general use seems like a really bad idea.  But if we
> manage to get send/receive conversion routines in there, most of that
> issue would go away.
> 
That would be great.

> 
>>The describe request is generally only 
>>done once even though you may do multiple fetchs (unlike todays protocol 
>>which includes the describe information on every fetch, even if you are 
>>fetching one row at a time).
> 
> 
> I'm less than excited about changing that, because it breaks clients
> that don't want to remember past RowDescriptions (libpq being the
> front-line victim), and it guarantees loss-of-synchronization failures
> anytime the client misassociates rowdescription with query.  In exchange
> for that, we get what exactly?  Fetching one row at a time is
> *guaranteed* to be inefficient.  The correct response if that bothers
> you is to fetch multiple rows at a time, not to make a less robust
> protocol.
I don't feel strongly either way on this one, but IIRC the SQL standard 
for cursors only specifies fetching one record at a time (at least that 
is how MSSQL and DB2 implement it).  Thus portable code is likely to 
only fetch one record at a time.  The current row description isn't too 
big, but with the changes being suggested it might become so.

thanks,
--Barry

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-14T02:32:36Z

Marc G. Fournier wrote:
> On Tue, 11 Mar 2003, Bruce Momjian wrote:
> 
> > Six months would be June 1 beta, so maybe that is still a good target.
> 
> We released v7.3 just before Dec 1st, so six months is May 1st, not June
> 1st ...

Six months is June 1 --- December (1), January-May (5) == 6.

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Larry Rosenman <ler@lerctr.org> — 2003-03-14T02:37:50Z


--On Thursday, March 13, 2003 21:32:36 -0500 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

> Marc G. Fournier wrote:
>> On Tue, 11 Mar 2003, Bruce Momjian wrote:
>>
>> > Six months would be June 1 beta, so maybe that is still a good target.
>>
>> We released v7.3 just before Dec 1st, so six months is May 1st, not June
>> 1st ...
>
> Six months is June 1 --- December (1), January-May (5) == 6.
>
PostgreSQL agrees:

ler=# select date('2002-12-01') +
ler-# '6 months'::interval;
      ?column?
---------------------
 2003-06-01 00:00:00
(1 row)

ler=#


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Roadmap for FE/BE protocol redesign

Christof Petig <christof@petig-baender.de> — 2003-03-14T09:11:06Z

Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
>>The describe request is generally only 
>>done once even though you may do multiple fetchs (unlike todays protocol 
>>which includes the describe information on every fetch, even if you are 
>>fetching one row at a time).
> 
> 
> I'm less than excited about changing that, because it breaks clients
> that don't want to remember past RowDescriptions (libpq being the
> front-line victim), and it guarantees loss-of-synchronization failures
> anytime the client misassociates rowdescription with query.  In exchange
> for that, we get what exactly?  Fetching one row at a time is
> *guaranteed* to be inefficient.  The correct response if that bothers
> you is to fetch multiple rows at a time, not to make a less robust
> protocol.

I don't think that protocol support for cursors should change the 
behavior of executing all seven stages by default. A "FETCH ..." 
commmand would get processed like any other (e.g. "SELECT ...") and 
metadata is sent back, too (which corresponds to decribe stage IIRC).

New programs have the option to use the backwards compatible high level 
access via PQexec(c,"FETCH FROM X") which does all seven steps at once, 
or use the new low level way e.g. PQexec_new(c,"SELECT ...", 
query_parameter_descriptor, what_to_do (*), 
lines_to_return_without_cursor_overhead) which should return at most the 
specified lines and (if needed) a cursor descriptor (most likely an int) 
for subsequent PQfetch and PQclose calls.

I really like the idea of PGresult as an argument (cursor descriptor) 
for PQfetch (instead of an int) because it may even copy the metadata to 
the new PGresult, or perhaps replace the values in the original PGresult 
(if we decide to go this way). [proposed signature: PGresult 
*PQfetch(PGresult*result_of_the_select, how_many_lines, 
perhaps_even_offset/position)]

Additional there should be a PQclose and perhaps a PQprocess(PGresult *, 
things_to_do (*)) if we want to be able to separate every step.

If you know you are never interested in metadata, you can omit the 
describe flag at all. [null indication and type specification is of 
course always needed to access the actual data]

    Christof

*) open, parse, describe, bind, execute, fetch, close

PS: If we decide to omit the lines_to_return_without_cursor_overhead 
optimization, the new architecture would still be a big win for *DBC.

This optimization can not get a GUC variable instead of a protocol 
parameter since this would break clients: should they specify 
fetch+close to enable it? If yes, there's no easy way to implement the 
old behavior (all seven stages, no limit on returned lines). If no, the 
client cannot specify to omit the fetch without changing it (limit 0).

PPS: Query parameter passing is another topic, but I tend to propose a 
PGresult variant for specifying them (of course each with its type).

Re: Roadmap for FE/BE protocol redesign

Christof Petig <christof@petig-baender.de> — 2003-03-14T09:39:27Z

Christof Petig wrote:
> If you know you are never interested in metadata, you can omit the 
> describe flag at all. [null indication and type specification is of 
> course always needed to access the actual data]

More exactly they are sent separately:
null indication is per row 'D'/'B' and type specification is per query 'T'.

If the client does not ask for metadata one might omit attrelid,attnum 
(*) and field name in the 'T' packet. One might argue whether this small 
win per query times column rectifies to implement the feature. But then 
we'd need a method to query them lateron (otherwise *DBC could never 
omit them at first).

   Christof

*) they are not there, yet ;-)

Re: Roadmap for FE/BE protocol redesign

Greg Stark <gsstark@mit.edu> — 2003-03-14T15:22:19Z

So, just to throw out a wild idea: If you're talking about making large
changes to the on-the-wire protocol. Have you considered using an existing
database protocol? This would avoid having to reinvent the wheel every time
postgres implements a new feature like prepared queries, bind arrays, or
metadata information.

There is a free implementation of the TDS (Tabular DataStream) protocol used
by Sybase and MSSQL. I don't know how much of it would be interesting for
postgres and how much is Sybase/MSSQL-specific.

It would be pretty neat if postgres could use precisely the same on-the-wire
protocol as other major databases, just requiring a separate high level driver
to interpret the semantic meaning of the data.

At the very least it sounds like interesting to do a compare and contrast as
far as understanding what advantages the features TDS has have and what
disadvantages, before postgres possibly misses good ideas or makes the same
mistakes.


-- 
greg

Re: Roadmap for FE/BE protocol redesign

cbbrowne@cbbrowne.com — 2003-03-14T17:07:30Z

A long time ago, in a galaxy far, far away, gsstark@mit.edu (Greg Stark) wrote:
> So, just to throw out a wild idea: If you're talking about making large
> changes to the on-the-wire protocol. Have you considered using an existing
> database protocol? This would avoid having to reinvent the wheel every time
> postgres implements a new feature like prepared queries, bind arrays, or
> metadata information.
>
> There is a free implementation of the TDS (Tabular DataStream) protocol used
> by Sybase and MSSQL. I don't know how much of it would be interesting for
> postgres and how much is Sybase/MSSQL-specific.
>
> It would be pretty neat if postgres could use precisely the same on-the-wire
> protocol as other major databases, just requiring a separate high level driver
> to interpret the semantic meaning of the data.
>
> At the very least it sounds like interesting to do a compare and contrast as
> far as understanding what advantages the features TDS has have and what
> disadvantages, before postgres possibly misses good ideas or makes the same
> mistakes.

Let me take the liberty of pointing people to documentation to the TDS
protocol:
  <http://www.freetds.org/tds.html>

I agree that there are far worse ideas, when opening up the DB
protocol, than to look at some existing protocols, and TDS would seem
a reasonable one.  It doesn't look overly efficient, but it's not
overtly gratuitously inefficient...
-- 
(reverse (concatenate 'string "moc.enworbbc@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/linuxxian.html
DOS: n.,  A small annoying  boot virus that causes  random spontaneous
system crashes, usually just  before saving a massive project.  Easily
cured  by Unix.   See also  MS-DOS,  IBM-DOS, DR-DOS.   
-- from  David Vicker's .plan

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-14T17:15:06Z

Greg Stark <gsstark@mit.edu> writes:
> So, just to throw out a wild idea: If you're talking about making large
> changes to the on-the-wire protocol. Have you considered using an existing
> database protocol?

Yeah, I have.  Didn't look promising --- there's no percentage unless
we're 100% compatible, which creates a lot of problems (eg, can't ship
type OIDs to frontend anymore).

What I actually looked into was RDA, but I doubt that TDS would be any
closer to our needs...

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Hannu Krosing <hannu@tm.ee> — 2003-03-16T20:41:15Z

Tom Lane kirjutas R, 14.03.2003 kell 19:15:
> Greg Stark <gsstark@mit.edu> writes:
> > So, just to throw out a wild idea: If you're talking about making large
> > changes to the on-the-wire protocol. Have you considered using an existing
> > database protocol?
> 
> Yeah, I have.  Didn't look promising --- there's no percentage unless
> we're 100% compatible, which creates a lot of problems (eg, can't ship
> type OIDs to frontend anymore).

Surely there is a way to ship type info, even for UDT's (user defined
types) as nowadays most big databases support those.

> What I actually looked into was RDA, but I doubt that TDS would be any
> closer to our needs...

I remember someone started cleaning up IO in order to move it into a
separate module with the aim of making multiple implementations (RDA,
TDS, XML, native JDBC wire protocol if it ever becomes a reality, etc.)
possible.

While not exactly pertinent to new wire protocol this effort if
completed would make it much easier to have backwards compatibility on
the wire level.

------------
Hannu

PS. Another feature I'd like is individually turning off warnings and
notices. 

------------
Hannu

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Peter Eisentraut <peter_e@gmx.net> — 2003-03-18T00:22:55Z

Tom Lane writes:

> * Backend's ReadyForQuery message (Z message) should carry an indication
> of current transaction status (idle/in transaction/in aborted transaction)
> so that frontend need not guess at state.  Perhaps also indicate
> autocommit status.

If we do this, could we get rid of the messy autocommit GUC option and
handle autocommit in the client?  Before sending a command, the client
could check the transaction status and automatically choose to start a new
transaction.  That way we could get rid of the current mess that every
client needs to send a SET autocommit command before it can safely do
anything.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-18T00:40:39Z

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > * Backend's ReadyForQuery message (Z message) should carry an indication
> > of current transaction status (idle/in transaction/in aborted transaction)
> > so that frontend need not guess at state.  Perhaps also indicate
> > autocommit status.
> 
> If we do this, could we get rid of the messy autocommit GUC option and
> handle autocommit in the client?  Before sending a command, the client
> could check the transaction status and automatically choose to start a new
> transaction.  That way we could get rid of the current mess that every
> client needs to send a SET autocommit command before it can safely do
> anything.

What if folks want all their connections autocommit off.  Seems it is
best in the server.

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-18T01:48:41Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> * Backend's ReadyForQuery message (Z message) should carry an indication
>> of current transaction status (idle/in transaction/in aborted transaction)
>> so that frontend need not guess at state.  Perhaps also indicate
>> autocommit status.

> If we do this, could we get rid of the messy autocommit GUC option and
> handle autocommit in the client?

Hmm ... that's a thought ... not very backwards-compatible with 7.3,
but I think I like it better than continuing on with the GUC option.
As you say, that path is looking messier all the time.

Comments anyone?

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Neil Conway <neilc@samurai.com> — 2003-03-18T03:40:18Z

On Mon, 2003-03-17 at 20:48, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > If we do this, could we get rid of the messy autocommit GUC option and
> > handle autocommit in the client?
> 
> Hmm ... that's a thought ... not very backwards-compatible with 7.3,
> but I think I like it better than continuing on with the GUC option.
> As you say, that path is looking messier all the time.

Yes, definitely -- replacing the 7.3 GUC option would be great, that's a
pretty bad hack IMHO.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Peter Eisentraut <peter_e@gmx.net> — 2003-03-18T22:03:26Z

Bruce Momjian writes:

> What if folks want all their connections autocommit off.

For interactive use, people can record their preferred setting in
~/.psqlrc or something like that.

But any non-interactive program is written with a specific autocommit
setting in mind.  Either it assumes that single statements are
automatically committed or it issues explicit COMMIT commands.  That
means, the choice of autocommit mode is always ultimately determined by
the client, never by the server or its administrator.

For that very reason most standard interfaces define in their
specification whether applications must assume autocommit behavior or must
not do so.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-18T22:18:02Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > What if folks want all their connections autocommit off.
> 
> For interactive use, people can record their preferred setting in
> ~/.psqlrc or something like that.

But that only works for psql, right?  How would this be done at the
libpq level?  Environment variables?  GUC seems a whole lot cleaner.

> But any non-interactive program is written with a specific autocommit
> setting in mind.  Either it assumes that single statements are
> automatically committed or it issues explicit COMMIT commands.  That
> means, the choice of autocommit mode is always ultimately determined by
> the client, never by the server or its administrator.
> 
> For that very reason most standard interfaces define in their
> specification whether applications must assume autocommit behavior or must
> not do so.

I understand.  I just don't see any value in pushing that logic into
each client when we can do it centrally in the server.

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-03-18T23:15:49Z

On Tue, Mar 18, 2003 at 05:18:02PM -0500, Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> > 
> > > What if folks want all their connections autocommit off.
> > 
> > For interactive use, people can record their preferred setting in
> > ~/.psqlrc or something like that.
> 
> But that only works for psql, right?  How would this be done at the
> libpq level?  Environment variables?  GUC seems a whole lot cleaner.

I think an environment variable would be right.  The current method is
not clean in the sense that a client cannot decide what she wants; she
just accepts the decision from the DBA.  Thus, an application can't be
written with a certain value in mind, because the DBA can change the
setting at any time.

Client-side decision is the wiser proposal, I think.

> I understand.  I just don't see any value in pushing that logic into
> each client when we can do it centrally in the server.

The server doesn't know beforehand what the client wants.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No renuncies a nada. No te aferres a nada"

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-19T00:54:41Z

Alvaro Herrera wrote:
> On Tue, Mar 18, 2003 at 05:18:02PM -0500, Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> > > Bruce Momjian writes:
> > > 
> > > > What if folks want all their connections autocommit off.
> > > 
> > > For interactive use, people can record their preferred setting in
> > > ~/.psqlrc or something like that.
> > 
> > But that only works for psql, right?  How would this be done at the
> > libpq level?  Environment variables?  GUC seems a whole lot cleaner.
> 
> I think an environment variable would be right.  The current method is
> not clean in the sense that a client cannot decide what she wants; she
> just accepts the decision from the DBA.  Thus, an application can't be
> written with a certain value in mind, because the DBA can change the
> setting at any time.

The client can say "SET autocommit to off" or on.  It can use SHOW to
to see the setting.  Environment variables are used mostly for
connecting, and once connected, we use GUC.

In fact, an environment variable seems wrong because it isn't integrated
into the client, like a SET command is.

> Client-side decision is the wiser proposal, I think.
> 
> > I understand.  I just don't see any value in pushing that logic into
> > each client when we can do it centrally in the server.
> 
> The server doesn't know beforehand what the client wants.

True, but GUC seems like the way to go, and we have per-user/db settings
for GUC.

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Brian Bruns <camber@ais.org> — 2003-03-19T23:19:22Z

On 16 Mar 2003, Hannu Krosing wrote:

> Tom Lane kirjutas R, 14.03.2003 kell 19:15:
> > Greg Stark <gsstark@mit.edu> writes:
> > > So, just to throw out a wild idea: If you're talking about making large
> > > changes to the on-the-wire protocol. Have you considered using an existing
> > > database protocol?
> > 

> > What I actually looked into was RDA, but I doubt that TDS would be any
> > closer to our needs...
> 
> I remember someone started cleaning up IO in order to move it into a
> separate module with the aim of making multiple implementations (RDA,
> TDS, XML, native JDBC wire protocol if it ever becomes a reality, etc.)
> possible.

That was me, I did an initial cut of separating the FE/BE code from the 
rest, but ran short on time.  Hoping to get back to it one of these days.  
My primary interest was in getting the DRDA protocol supported in a clean 
fashion.  For those mentioning RDA, I believe that standard is pushing up 
the daisys.  DRDA is about the only standards game in town at this point, 
it has client side support from just about every vendor (IBM obviously, 
Oracle, Sybase, MS) and server side support of some sort from DB2 and a 
couple others (MS SNA gateway, for example is/has a DRDA server).  Mostly 
through gateways and add on products, but it's a far cry better than any 
other effort I'm aware of.

> While not exactly pertinent to new wire protocol this effort if
> completed would make it much easier to have backwards compatibility on
> the wire level.

I think this would be a good idea all around, and would make future 
changes/replacements to FE/BE protocol a lot cleaner. 

> ------------
> Hannu

Brian

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Peter Eisentraut <peter_e@gmx.net> — 2003-03-20T16:03:57Z

Bruce Momjian writes:

> True, but GUC seems like the way to go, and we have per-user/db settings
> for GUC.

But the required autocommit setting depends neither on the user nor the
database, it depends on the identity of the client application.  That type
of granularity is not offered by GUC.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-20T16:13:16Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > True, but GUC seems like the way to go, and we have per-user/db settings
> > for GUC.
> 
> But the required autocommit setting depends neither on the user nor the
> database, it depends on the identity of the client application.  That type
> of granularity is not offered by GUC.

True, but the standard also requires autocommit off, so I can imagine
folks wanting it off by default for various users/database.

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Jeff Davis <jdavis-pgsql@empires.org> — 2003-03-21T03:55:38Z

On Monday 10 March 2003 10:51 am, Tom Lane wrote:
>
> * XML support?  If we do anything, I'd want some extensible solution to
> allowing multiple query-result output formats from the backend, not an
> XML-specific hack.  For one thing, that would allow the actual appearance
> of any XML support to happen later.

It seems this would also be a good solution to a previous discussion about 
boolean representation.

The postgres output of t/f is perfectly resonable, but can be somewhat 
confusing to someone that relies on PHP's typecasting. In the discussion, 
someone mentioned that if you take in a variable directly from the database 
and cast it to boolean, both 't' and 'f' will cast to true. It turned out to 
be even more confusing because MySQL uses 0/1 which cast properly. 

If I remember correctly, there was even talk of adding a run-time parameter 
similar to the datestyle. If it were all handled in the query-result output 
formatting functions like you suggest, that would seem like a much cleaner 
solution. 

Regards,
	Jeff Davis

Re: Roadmap for FE/BE protocol redesign

Dave Cramer <dave@fastcrypt.com> — 2003-03-21T13:54:22Z

I am wondering if it would be possible to return the value of the first
serial field in the row that was incremented on an insert ?

-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T15:49:54Z

Dave Cramer <dave@fastcrypt.com> writes:
> I am wondering if it would be possible to return the value of the first
> serial field in the row that was incremented on an insert ?

That's a language issue, not a protocol issue, and no I'm not taking it
on for 7.4.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T15:54:18Z

Tom Lane wrote:
> Dave Cramer <dave@fastcrypt.com> writes:
> > I am wondering if it would be possible to return the value of the first
> > serial field in the row that was incremented on an insert ?
> 
> That's a language issue, not a protocol issue, and no I'm not taking it
> on for 7.4.

What I was wondering is if we could create a currval() call that takes
no arguments, and returns the most recent sequence id assigned.

-- 
  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, Pennsylvania 19073

Re: Roadmap for FE/BE protocol redesign

Dave Cramer <dave@fastcrypt.com> — 2003-03-21T16:06:14Z

Tom,

Forgive me for being dense, but how do you delineate this as being a
"language issue"?  

Dave
On Fri, 2003-03-21 at 10:54, Bruce Momjian wrote:
> Tom Lane wrote:
> > Dave Cramer <dave@fastcrypt.com> writes:
> > > I am wondering if it would be possible to return the value of the first
> > > serial field in the row that was incremented on an insert ?
> > 
> > That's a language issue, not a protocol issue, and no I'm not taking it
> > on for 7.4.
> 
> What I was wondering is if we could create a currval() call that takes
> no arguments, and returns the most recent sequence id assigned.
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting

Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T16:11:15Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What I was wondering is if we could create a currval() call that takes
> no arguments, and returns the most recent sequence id assigned.

Why?  That's still an extra query that the client has to issue, and
currval in that form would be an amazingly fragile programming tool.
(What if some trigger causes an autoincrement on some other sequence
than the one you are thinking about?)

I liked the INSERT ... RETURNING and UPDATE ... RETURNING syntax
extensions that Philip Warner (IIRC) proposed awhile back.  Those would
get the job done much more flexibly than anything else that's been
suggested.  That's why I think it's a language problem and not a
protocol problem.

			regards, tom lane

Re: Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T16:15:32Z

Sounds good.

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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > What I was wondering is if we could create a currval() call that takes
> > no arguments, and returns the most recent sequence id assigned.
> 
> Why?  That's still an extra query that the client has to issue, and
> currval in that form would be an amazingly fragile programming tool.
> (What if some trigger causes an autoincrement on some other sequence
> than the one you are thinking about?)
> 
> I liked the INSERT ... RETURNING and UPDATE ... RETURNING syntax
> extensions that Philip Warner (IIRC) proposed awhile back.  Those would
> get the job done much more flexibly than anything else that's been
> suggested.  That's why I think it's a language problem and not a
> protocol problem.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Peter Eisentraut <peter_e@gmx.net> — 2003-03-21T17:03:27Z

Bruce Momjian writes:

> True, but the standard also requires autocommit off, so I can imagine
> folks wanting it off by default for various users/database.

The standard only covers embedded C programs.  Other interfaces that are
covered by other standards require other settings.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T17:14:08Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > True, but the standard also requires autocommit off, so I can imagine
> > folks wanting it off by default for various users/database.
> 
> The standard only covers embedded C programs.  Other interfaces that are
> covered by other standards require other settings.

My point was that _the_ standard requires autocommit off, not that
everyone is using autocommit off.

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T17:42:25Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Peter Eisentraut wrote:
>> The standard only covers embedded C programs.  Other interfaces that are
>> covered by other standards require other settings.

> My point was that _the_ standard requires autocommit off, not that
> everyone is using autocommit off.

Peter's point is good: the only interface that is actually subject to the
part of the spec you are quoting is ecpg.  It could reasonably be argued
that libpq should only support autocommit-on, because that's the
historical behavior that programs written atop libpq expect.  The other
client libraries have their own specs to adhere to.

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T17:46:02Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Peter Eisentraut wrote:
> >> The standard only covers embedded C programs.  Other interfaces that are
> >> covered by other standards require other settings.
> 
> > My point was that _the_ standard requires autocommit off, not that
> > everyone is using autocommit off.
> 
> Peter's point is good: the only interface that is actually subject to the
> part of the spec you are quoting is ecpg.  It could reasonably be argued
> that libpq should only support autocommit-on, because that's the
> historical behavior that programs written atop libpq expect.  The other
> client libraries have their own specs to adhere to.

But isn't that like saying that the spec doesn't apply to libpq at all. 
Why would autocommit not apply but other queries specification apply?

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T18:00:07Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> But isn't that like saying that the spec doesn't apply to libpq at all. 
> Why would autocommit not apply but other queries specification apply?

No, you're missing the point.  Essentially all the client libraries
offer their own autocommit behavior on top of what the spec says.
libpq is alone in not having any such client-side logic.  So it's not
a foregone conclusion that we should implement autocommit on/off logic
on the server side as a substitute for adding it to libpq.  We have
now tried doing it on the server side, and we are finding that we don't
like the side-effects; so it's time to revisit that decision.

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T18:06:24Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > But isn't that like saying that the spec doesn't apply to libpq at all. 
> > Why would autocommit not apply but other queries specification apply?
> 
> No, you're missing the point.  Essentially all the client libraries
> offer their own autocommit behavior on top of what the spec says.

Well, which ones?  jdbc (which prevers the server autocommit), ecpg,
odbc (?), and Perl.  Anyone else?

> libpq is alone in not having any such client-side logic.  So it's not
> a foregone conclusion that we should implement autocommit on/off logic
> on the server side as a substitute for adding it to libpq.  We have
> now tried doing it on the server side, and we are finding that we don't
> like the side-effects; so it's time to revisit that decision.

My concern is bloating the API for all languages based on libpq, and
psql and stuff like that.  Heck, even pgadmin would have to have a
button for it because a SET couldn't control it.

I know the server-side isn't optimal, but is the alternative worse?

Also, if Peter wants clients to be able to just issue a query and know
it commits, should we just disable setting autocommit in postgresql.conf
and per-user/db, and force applications to turn it on explicitly?

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T18:33:08Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My concern is bloating the API for all languages based on libpq, and
> psql and stuff like that.  Heck, even pgadmin would have to have a
> button for it because a SET couldn't control it.

Peter's point, AIUI, is that that is a good thing.

The problem with SET for autocommit is that every client program has to
*know*, without question, which setting it is using.  Autocommit is just
about as dangerous as a GUC variable that, say, silently swaps the
meanings of INSERT and DELETE --- if you don't know which setting you
are using then you will get the wrong results.

Thus it is not "convenient" to allow autocommit to be set in all the
weird and wonderful ways that we allow GUC variables to be set; it's
more like handing a razor blade to a baby.  We've already found out that
all client-side apps have to explicitly force autocommit to the setting
they want, or they break.  How is that a good thing?

I think we *should* go back to the assumption that libpq-based programs
see autocommit-on behavior unless they take specific action to prevent
it.  And that means that the client program has to take action to select
autocommit off, not that the admin can flick a switch remotely that will
affect clients.

The real point is that both the client application and the client
library need to know what the autocommit behavior is.  This is why
adding autocommit to the library APIs is the right thing, not the wrong
thing.  When there are ways other than a library API call to set the
autocommit behavior, then one party or the other is out of the loop
about what the current behavior is, and that gets us right back into the
same mess.

Basically I think that Peter is arguing that autocommit as a GUC
variable is a wrong and dangerous idea.  And I'm forced to agree.
I was wrong to put it in, and I'm now willing to take it out again.
At the time it seemed like a reasonable shortcut around changing the
protocol --- but now that we're changing the protocol anyway, it's
better to get rid of the shortcut.

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-21T21:32:29Z

In general I agree with Tom.  GUC is the wrong mechanism for autocommit. 
  The reason being that it isn't a system administrators decision what 
value autocommit should have.  It is generally dictated by the client 
protocol or application.

Now that being said, it is nice for the client to be able to simply tell 
the server "you are in autocommit mode until I tell you otherwise". 
Instead of having to worry about trapping each commit and rollback 
request to make sure you insert to proper begin.

The current GUC autocommit is nice in that it makes it easier for the 
cleint to simply turn on or off the state.  It is a problem because it 
is a GUC parameter and therefore can be changed by the admin (thus you 
don't know what your initial state is without asking the server) or the 
user (via sql SET, thus you don't know that it has changed).

As I said in my earlier mail note from a jdbc perspective I can get it 
to work which ever way is decided (in fact the jdbc driver will probably 
need to support all of the ways, depending on if it is talking to a 7.2, 
7.3 or 7.4 backend).

My preference (given that I am detecting a willingness to make more 
significant changes in this area that I was expecting) would be to drop 
the GUC autocommit parameter.  Replacing that functionality with the 
ability to set and discover the autocommit state via the FE/BE protocol.

thanks,
--Barry

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>My concern is bloating the API for all languages based on libpq, and
>>psql and stuff like that.  Heck, even pgadmin would have to have a
>>button for it because a SET couldn't control it.
> 
> 
> Peter's point, AIUI, is that that is a good thing.
> 
> The problem with SET for autocommit is that every client program has to
> *know*, without question, which setting it is using.  Autocommit is just
> about as dangerous as a GUC variable that, say, silently swaps the
> meanings of INSERT and DELETE --- if you don't know which setting you
> are using then you will get the wrong results.
> 
> Thus it is not "convenient" to allow autocommit to be set in all the
> weird and wonderful ways that we allow GUC variables to be set; it's
> more like handing a razor blade to a baby.  We've already found out that
> all client-side apps have to explicitly force autocommit to the setting
> they want, or they break.  How is that a good thing?
> 
> I think we *should* go back to the assumption that libpq-based programs
> see autocommit-on behavior unless they take specific action to prevent
> it.  And that means that the client program has to take action to select
> autocommit off, not that the admin can flick a switch remotely that will
> affect clients.
> 
> The real point is that both the client application and the client
> library need to know what the autocommit behavior is.  This is why
> adding autocommit to the library APIs is the right thing, not the wrong
> thing.  When there are ways other than a library API call to set the
> autocommit behavior, then one party or the other is out of the loop
> about what the current behavior is, and that gets us right back into the
> same mess.
> 
> Basically I think that Peter is arguing that autocommit as a GUC
> variable is a wrong and dangerous idea.  And I'm forced to agree.
> I was wrong to put it in, and I'm now willing to take it out again.
> At the time it seemed like a reasonable shortcut around changing the
> protocol --- but now that we're changing the protocol anyway, it's
> better to get rid of the shortcut.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T22:36:03Z

Man, I lost another vote!  :-)

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

Barry Lind wrote:
> In general I agree with Tom.  GUC is the wrong mechanism for autocommit. 
>   The reason being that it isn't a system administrators decision what 
> value autocommit should have.  It is generally dictated by the client 
> protocol or application.
> 
> Now that being said, it is nice for the client to be able to simply tell 
> the server "you are in autocommit mode until I tell you otherwise". 
> Instead of having to worry about trapping each commit and rollback 
> request to make sure you insert to proper begin.
> 
> The current GUC autocommit is nice in that it makes it easier for the 
> cleint to simply turn on or off the state.  It is a problem because it 
> is a GUC parameter and therefore can be changed by the admin (thus you 
> don't know what your initial state is without asking the server) or the 
> user (via sql SET, thus you don't know that it has changed).
> 
> As I said in my earlier mail note from a jdbc perspective I can get it 
> to work which ever way is decided (in fact the jdbc driver will probably 
> need to support all of the ways, depending on if it is talking to a 7.2, 
> 7.3 or 7.4 backend).
> 
> My preference (given that I am detecting a willingness to make more 
> significant changes in this area that I was expecting) would be to drop 
> the GUC autocommit parameter.  Replacing that functionality with the 
> ability to set and discover the autocommit state via the FE/BE protocol.
> 
> thanks,
> --Barry
> 
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 
> >>My concern is bloating the API for all languages based on libpq, and
> >>psql and stuff like that.  Heck, even pgadmin would have to have a
> >>button for it because a SET couldn't control it.
> > 
> > 
> > Peter's point, AIUI, is that that is a good thing.
> > 
> > The problem with SET for autocommit is that every client program has to
> > *know*, without question, which setting it is using.  Autocommit is just
> > about as dangerous as a GUC variable that, say, silently swaps the
> > meanings of INSERT and DELETE --- if you don't know which setting you
> > are using then you will get the wrong results.
> > 
> > Thus it is not "convenient" to allow autocommit to be set in all the
> > weird and wonderful ways that we allow GUC variables to be set; it's
> > more like handing a razor blade to a baby.  We've already found out that
> > all client-side apps have to explicitly force autocommit to the setting
> > they want, or they break.  How is that a good thing?
> > 
> > I think we *should* go back to the assumption that libpq-based programs
> > see autocommit-on behavior unless they take specific action to prevent
> > it.  And that means that the client program has to take action to select
> > autocommit off, not that the admin can flick a switch remotely that will
> > affect clients.
> > 
> > The real point is that both the client application and the client
> > library need to know what the autocommit behavior is.  This is why
> > adding autocommit to the library APIs is the right thing, not the wrong
> > thing.  When there are ways other than a library API call to set the
> > autocommit behavior, then one party or the other is out of the loop
> > about what the current behavior is, and that gets us right back into the
> > same mess.
> > 
> > Basically I think that Peter is arguing that autocommit as a GUC
> > variable is a wrong and dangerous idea.  And I'm forced to agree.
> > I was wrong to put it in, and I'm now willing to take it out again.
> > At the time it seemed like a reasonable shortcut around changing the
> > protocol --- but now that we're changing the protocol anyway, it's
> > better to get rid of the shortcut.
> > 
> > 			regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> > 
> 
> 
> 

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Tom Lane <tgl@sss.pgh.pa.us> — 2003-03-21T22:46:41Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Man, I lost another vote!  :-)

Happens to us all ;-)

But this discussion is far from over ... not that many people have
weighed in yet.  Also, even if autocommit's fate is sealed, we still
have to think about how to handle the other variables Barry identified
as trouble spots.

			regards, tom lane

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-21T22:52:54Z

I was thinking if we had a SET PERMANENT that couldn't be changed,
interfaces that want to control variables could set them perminantly,
and others could let users control it.

Actually, if we reported change to the client, the interface could
override any change the user made --- just an idea.

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

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Man, I lost another vote!  :-)
> 
> Happens to us all ;-)
> 
> But this discussion is far from over ... not that many people have
> weighed in yet.  Also, even if autocommit's fate is sealed, we still
> have to think about how to handle the other variables Barry identified
> as trouble spots.
> 
> 			regards, tom lane
> 

-- 
  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, Pennsylvania 19073

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Barry Lind <blind@xythos.com> — 2003-03-21T23:28:03Z

Bruce Momjian wrote:
> I was thinking if we had a SET PERMANENT that couldn't be changed,
> interfaces that want to control variables could set them perminantly,
> and others could let users control it.

SET PERMANENT only works for those variables that can only have one 
value for a given client protocol (for example datestyle in jdbc). 
Whereas autocommit needs to be changeable by the jdbc driver since the 
jdbc spec allows the user to call an API method to change the setting.

> 
> Actually, if we reported change to the client, the interface could
> override any change the user made --- just an idea.

Reporting the change to the client seems sufficient for the client to 
take whatever actions are necessary.

--Barry

> 
> ---------------------------------------------------------------------------
> 
> Tom Lane wrote:
> 
>>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>
>>>Man, I lost another vote!  :-)
>>
>>Happens to us all ;-)
>>
>>But this discussion is far from over ... not that many people have
>>weighed in yet.  Also, even if autocommit's fate is sealed, we still
>>have to think about how to handle the other variables Barry identified
>>as trouble spots.
>>
>>			regards, tom lane
>>
> 
>

Re: [INTERFACES] Roadmap for FE/BE protocol redesign

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-03-22T01:53:09Z

Barry Lind wrote:
> 
> 
> Bruce Momjian wrote:
> > I was thinking if we had a SET PERMANENT that couldn't be changed,
> > interfaces that want to control variables could set them perminantly,
> > and others could let users control it.
> 
> SET PERMANENT only works for those variables that can only have one 
> value for a given client protocol (for example datestyle in jdbc). 
> Whereas autocommit needs to be changeable by the jdbc driver since the 
> jdbc spec allows the user to call an API method to change the setting.

Oh, good point.

> 
> > 
> > Actually, if we reported change to the client, the interface could
> > override any change the user made --- just an idea.
> 
> Reporting the change to the client seems sufficient for the client to 
> take whatever actions are necessary.

-- 
  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, Pennsylvania 19073