Thread: First draft of new FE/BE protocol spec posted for comments

I have committed a first-draft revision of the FE/BE protocol document;
you can read it at
http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol.html
or in a few hours at
http://developer.postgresql.org/docs/postgres/protocol.html
I'd appreciate it if people would look it over for both presentation
and content.

There are a couple of loose ends that are still bothering me --- please
comment:

The new Execute command (part of the extended query protocol) has a
field saying whether to return data in text or binary format.  When
retrieving from a cursor, it is not clear whether this should override
the declaration of the cursor (BINARY or not).  I am inclined to think
that it should, but a possible compromise is to add a third value of the
field meaning "don't care", in which case you'd get back text in all
cases except when reading a cursor declared BINARY.  This would be
strictly for backwards compatibility though, and so maybe it doesn't
matter.  Old apps will probably be going through the simple-Query
interface, which will give them the old behavior.

I have dropped the CursorResponse message from the protocol, as it
didn't seem to be doing anything useful; does anyone care about it?

The document as it stands is a little bit schizoid about binary data
formats.  The new message types I've added are currently specified to
use a representation that matches the COPY BINARY file format: an int16
typlen (replaced with 0 if NULL), followed by a field value, where if
typlen = -1 the first four bytes of the field value are self-inclusive
length.  The existing message types that handle binary data (BinaryRow,
FunctionCall, FunctionResult) do it differently: a physical length (not
counting self) followed by data.  This is a bit of a mess, and I think
it would make sense to standardize the representation one way or the
other.  The reason I'm inclined to move away from the old representation
is that it's effectively broken on machines where MAXALIGN is greater
than four: because it strips the length word out of the "contents" of
varlena datatypes, the remainder of the varlena is not correctly aligned
when stored in libpq memory.  (The mail list archives seem to be down at
the moment, so I can't give a URL, but there was a discussion of this
point in pgsql-hackers on 2-Aug-99.)  This will clearly be a
user-visible change for people using binary cursors, but I think we
*must* change it now or be stuck with the old mistake forever.

An alternative approach, assuming we get as far as implementing
architecture-independent binary representations, is to change the COPY
BINARY file format to use them, and then the issue largely goes away ---
we can stick with the existing layout for BinaryRow and make the other
FE/BE messages use a similar format.  But in either case, something
breaks --- either binary cursors or COPY BINARY files.  Any preference
which to break?
        regards, tom lane

As it currently stands, we have functions that are capable of returning
multi-column rows. Would the result of a FunctionCall message be to return
a FunctionCallResult and optional RowDescription, RowData messages?

I dont read that into the document as it stands tho'
http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol-flow.html

Regards,
Grant

Tom Lane wrote:
> I have committed a first-draft revision of the FE/BE protocol document;
> you can read it at
> http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol.html
> or in a few hours at
> http://developer.postgresql.org/docs/postgres/protocol.html
> I'd appreciate it if people would look it over for both presentation
> and content.
> 
> There are a couple of loose ends that are still bothering me --- please
> comment:
> 
> The new Execute command (part of the extended query protocol) has a
> field saying whether to return data in text or binary format.  When
> retrieving from a cursor, it is not clear whether this should override
> the declaration of the cursor (BINARY or not).  I am inclined to think
> that it should, but a possible compromise is to add a third value of the
> field meaning "don't care", in which case you'd get back text in all
> cases except when reading a cursor declared BINARY.  This would be
> strictly for backwards compatibility though, and so maybe it doesn't
> matter.  Old apps will probably be going through the simple-Query
> interface, which will give them the old behavior.
> 
> I have dropped the CursorResponse message from the protocol, as it
> didn't seem to be doing anything useful; does anyone care about it?
> 
> The document as it stands is a little bit schizoid about binary data
> formats.  The new message types I've added are currently specified to
> use a representation that matches the COPY BINARY file format: an int16
> typlen (replaced with 0 if NULL), followed by a field value, where if
> typlen = -1 the first four bytes of the field value are self-inclusive
> length.  The existing message types that handle binary data (BinaryRow,
> FunctionCall, FunctionResult) do it differently: a physical length (not
> counting self) followed by data.  This is a bit of a mess, and I think
> it would make sense to standardize the representation one way or the
> other.  The reason I'm inclined to move away from the old representation
> is that it's effectively broken on machines where MAXALIGN is greater
> than four: because it strips the length word out of the "contents" of
> varlena datatypes, the remainder of the varlena is not correctly aligned
> when stored in libpq memory.  (The mail list archives seem to be down at
> the moment, so I can't give a URL, but there was a discussion of this
> point in pgsql-hackers on 2-Aug-99.)  This will clearly be a
> user-visible change for people using binary cursors, but I think we
> *must* change it now or be stuck with the old mistake forever.
> 
> An alternative approach, assuming we get as far as implementing
> architecture-independent binary representations, is to change the COPY
> BINARY file format to use them, and then the issue largely goes away ---
> we can stick with the existing layout for BinaryRow and make the other
> FE/BE messages use a similar format.  But in either case, something
> breaks --- either binary cursors or COPY BINARY files.  Any preference
> which to break?
> 
>             regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
> 

On Tue, Apr 15, 2003 at 07:08:30PM -0400, Tom Lane wrote:
> I have committed a first-draft revision of the FE/BE protocol document;
> you can read it at
> http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol.html
> or in a few hours at
> http://developer.postgresql.org/docs/postgres/protocol.html
> I'd appreciate it if people would look it over for both presentation
> and content.

Just showing my ignorance: I read 46.2.3 Extended Query, and wondered "What
is a portal?" (portal == cursor?)
also noted that SQL EXECUTE (prepared statement) != Execute (portal)

and in 46.3. Message Data Types, reading "There is no predefined limit on
the length of a string that can be returned by the backend." one might say
Oh no! but of course you know what it's maximum size is by then, as you know
the length of the response which contains the string..

Cheers,

Patrick

Kudos on the spec!  Some feedback:

46.2.3 - Is bind required when the portal has no parameters?  It would be
useful to be able to avoid the bind message in this case.

46.2.9 - SSL - Is there any way to detect the start of an SSL session sooner?
This is not for security purposes, but I currently have this scenario:

Win/ODBC client <------> fw machine <=========> database server

Both fw machine and database server have stunnel running on them, since
the protocol start up requirements of PostgreSQL prevent me from just having
stunnel connect to the database service.

I'm not saying don't support the old method as well.  A quick look at rfc2246
shows that the client sends the TLS/SSL hello message first, meaning that we
could detect it.  Since the client is also responsible for sending the first
message anyway, we just need to check if the first bytes indicate a TLS
hello or a PostgreSQL startup message.  If we got a TLS hello, start
cryptographic negotiations by stuffing bytes back into a buffer.  IIRC,
OpenSSL stream abstraction will allow us to do this easily.  Otherwise,
continue as before.  If we receive an SSLRequest while SSL encrypted,
respond N.  (I'm no cryptographer, but I've seen a couple of instances where
double-encryption is less secure than single-encryption.)

This would allow me to get rid of the stoopid stunnel on the database server,
and provide an excellent general case for encrypting connections from
non-SSL-aware clients.

-Jay 'Eraserhead' Felice

On Wed, Apr 16, 2003 at 12:44:52PM +0100, Patrick Welche wrote:
> On Tue, Apr 15, 2003 at 07:08:30PM -0400, Tom Lane wrote:
> > I have committed a first-draft revision of the FE/BE protocol document;
> > you can read it at
> > http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol.html
> > or in a few hours at
> > http://developer.postgresql.org/docs/postgres/protocol.html
> > I'd appreciate it if people would look it over for both presentation
> > and content.
> 
> Just showing my ignorance: I read 46.2.3 Extended Query, and wondered "What
> is a portal?" (portal == cursor?)
> also noted that SQL EXECUTE (prepared statement) != Execute (portal)
> 
> and in 46.3. Message Data Types, reading "There is no predefined limit on
> the length of a string that can be returned by the backend." one might say
> Oh no! but of course you know what it's maximum size is by then, as you know
> the length of the response which contains the string..
> 
> Cheers,
> 
> Patrick
> 

Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> Just showing my ignorance: I read 46.2.3 Extended Query, and wondered "What
> is a portal?" (portal == cursor?)

It's the thing that holds the state of an executable command.  I was
going to call 'em cursors, but a cursor only works for a SELECT command,
so it didn't seem like the right word.  I seized on "portal" which is
the term already used internally in the backend.  If you've got a better
idea, let's hear it ...
        regards, tom lane

Grant Finnemore <grantf@guruhut.co.za> writes:
> As it currently stands, we have functions that are capable of returning
> multi-column rows. Would the result of a FunctionCall message be to return
> a FunctionCallResult and optional RowDescription, RowData messages?

Frankly, I'd remove the whole fastpath function thing if I had my
druthers, but I don't want to reimplement libpq's large-object routines.
I'm just planning to leave it as supporting a scalar return value.

I should mark it deprecated in the docs, probably.  You can get the same
results more cleanly by doing a SELECT --- and if the overhead is what's
bothering you, there's PREPARE.
        regards, tom lane

"Jason M. Felice" <jfelice@cronosys.com> writes:
> 46.2.3 - Is bind required when the portal has no parameters?  It would be
> useful to be able to avoid the bind message in this case.

Yes, because you don't have a portal till you bind.  I don't see that
avoiding the message is an issue --- it's only a few bytes, and you
don't need to wait for a round-trip time.  In practice you're going to
bundle it with either the Parse or the Execute, I would think.

> 46.2.9 - SSL - Is there any way to detect the start of an SSL session sooner?

I am not personally willing to take any responsibility for altering the
SSL startup protocol.  If someone else wants to do it, fine.  I'm not
real sure how you are going to "stuff bytes back into a buffer" though;
at the very least, that's going to require unseemly intimacy with the
SSL library.  Also, it's not obvious to me that the first few bytes of
TLS' ClientHello can reliably be distinguished from our StartupMessage
... especially given that both protocols put version numbers, lengths,
and other not-very-constant data here.
        regards, tom lane

On Thu, 2003-04-17 at 00:06, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > Just showing my ignorance: I read 46.2.3 Extended Query, and wondered "What
> > is a portal?" (portal == cursor?)
> 
> It's the thing that holds the state of an executable command.  I was
> going to call 'em cursors, but a cursor only works for a SELECT command,
> so it didn't seem like the right word.  I seized on "portal" which is
> the term already used internally in the backend.  If you've got a better
> idea, let's hear it ...

Context, perhaps?

Tom,

I have finally finished reading the doc and I have a couple of 
questions.  But first I really want to thank you for the work you have 
done here.  It looks great.  And I believe it will allow for much better  implementations for the client interfaces.

The Bind message contains a field for the typlen of the parameter value.  This field as documented signifies null with
thespecial value of 0. 
 
This implies that there cannot be any datatype that allows an empty 
value to mean something other than null, as there would be no way to 
distinguish null from a value that was 0 bytes long.  I am not sure if 
this is a problem or not, but it caught my eye.

Why does the message EmptyQueryResponse still exist?  It is documented 
as existing for "historical reasons".  Can't it be removed in a major 
overhaul of the protocol like this?

My final question is more of an implementation question than comments on 
the protocol itself.  Since the jdbc driver needs to be backwardly 
compatible with 7.3 and earlier servers that will speak the 2.0 
protocol, what is the recomended way to write the client so that it can 
'detect' which protocol to use.  The only thing I can think of is to 
start by sending a version 2 protocol message (which will work across 
all the database versions that jdbc will be supporting) then if it finds 
it it talking to a 7.4 server, close the connection and reconnect using 
the version 3 protocol.  This will double connection time when 
connecting to a 7.4 server but is the only way I can think of to do 
this.  Is there a better way that I am missing?

thanks,
--Barry

Tom Lane wrote:
> I have committed a first-draft revision of the FE/BE protocol document;
> you can read it at
> http://candle.pha.pa.us/main/writings/pgsql/sgml/protocol.html
> or in a few hours at
> http://developer.postgresql.org/docs/postgres/protocol.html
> I'd appreciate it if people would look it over for both presentation
> and content.
> 
> There are a couple of loose ends that are still bothering me --- please
> comment:
> 
> The new Execute command (part of the extended query protocol) has a
> field saying whether to return data in text or binary format.  When
> retrieving from a cursor, it is not clear whether this should override
> the declaration of the cursor (BINARY or not).  I am inclined to think
> that it should, but a possible compromise is to add a third value of the
> field meaning "don't care", in which case you'd get back text in all
> cases except when reading a cursor declared BINARY.  This would be
> strictly for backwards compatibility though, and so maybe it doesn't
> matter.  Old apps will probably be going through the simple-Query
> interface, which will give them the old behavior.
> 
> I have dropped the CursorResponse message from the protocol, as it
> didn't seem to be doing anything useful; does anyone care about it?
> 
> The document as it stands is a little bit schizoid about binary data
> formats.  The new message types I've added are currently specified to
> use a representation that matches the COPY BINARY file format: an int16
> typlen (replaced with 0 if NULL), followed by a field value, where if
> typlen = -1 the first four bytes of the field value are self-inclusive
> length.  The existing message types that handle binary data (BinaryRow,
> FunctionCall, FunctionResult) do it differently: a physical length (not
> counting self) followed by data.  This is a bit of a mess, and I think
> it would make sense to standardize the representation one way or the
> other.  The reason I'm inclined to move away from the old representation
> is that it's effectively broken on machines where MAXALIGN is greater
> than four: because it strips the length word out of the "contents" of
> varlena datatypes, the remainder of the varlena is not correctly aligned
> when stored in libpq memory.  (The mail list archives seem to be down at
> the moment, so I can't give a URL, but there was a discussion of this
> point in pgsql-hackers on 2-Aug-99.)  This will clearly be a
> user-visible change for people using binary cursors, but I think we
> *must* change it now or be stuck with the old mistake forever.
> 
> An alternative approach, assuming we get as far as implementing
> architecture-independent binary representations, is to change the COPY
> BINARY file format to use them, and then the issue largely goes away ---
> we can stick with the existing layout for BinaryRow and make the other
> FE/BE messages use a similar format.  But in either case, something
> breaks --- either binary cursors or COPY BINARY files.  Any preference
> which to break?
> 
>             regards, tom lane
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
> 

Hi,

> My final question is more of an implementation question than comments on
> the protocol itself.  Since the jdbc driver needs to be backwardly
> compatible with 7.3 and earlier servers that will speak the 2.0
> protocol, what is the recomended way to write the client so that it can
> 'detect' which protocol to use.  The only thing I can think of is to
> start by sending a version 2 protocol message (which will work across
> all the database versions that jdbc will be supporting) then if it finds
> it it talking to a 7.4 server, close the connection and reconnect using
> the version 3 protocol.  This will double connection time when
> connecting to a 7.4 server but is the only way I can think of to do
> this.  Is there a better way that I am missing?

Since I guess there will be a lot more 7.4+ servers than older servers I
think you should at least try protocol v3 first, and if that fails try v2.
Try to keep it as fast as possible for the most used version.

Could be that that this isn't possible though. I don't know the protocol
well enough to be sure. Maybe its an idea to introduce a new messagetype at
protocol level with which the client can ask the server which protocol
versions it supports. That would make protocol changes a lot easier in the
future. And if the server doesn't understand that new messagetype you know
you are talking to a v2-only server :)

Bye
Sander

Barry Lind <blind@xythos.com> writes:

> The Bind message contains a field for the typlen of the parameter value. 
>   This field as documented signifies null with the special value of 0. 
> This implies that there cannot be any datatype that allows an empty 
> value to mean something other than null, as there would be no way to 
> distinguish null from a value that was 0 bytes long.

No, typlen is the value from pg_type.typlen; there is no meaning to the
value zero in that column.  A variable-length datum that happened to be
zero bytes long would still be associated with a negative typlen.  A
fixed-length datum of zero bytes isn't real likely ...

> Why does the message EmptyQueryResponse still exist?  It is documented 
> as existing for "historical reasons".  Can't it be removed in a major 
> overhaul of the protocol like this?

I was ready to get rid of it and then realized that it has a use now.
With it, it's guaranteed that the response to Execute will end with
exactly one of these messages: CommandComplete, EmptyQueryResponse (if
the portal was created from an empty query string), ErrorResponse, or
PortalSuspended.  Without it, we'd need to invent some other message
to signify the end of an Execute cycle.

> My final question is more of an implementation question than comments on 
> the protocol itself.  Since the jdbc driver needs to be backwardly 
> compatible with 7.3 and earlier servers that will speak the 2.0 
> protocol, what is the recomended way to write the client so that it can 
> 'detect' which protocol to use.

I'd do it the other way: send a 3.0 connection request and then, if you
get a "bad protocol" error response, send a 2.0 connection request.
You do have to be a little careful about parsing the connection response
since if it's 'E' it might be either 2.0 or 3.0 layout, but that can be
handled without too much difficulty I think.  (Look at the
already-committed code in libpq's fe-connect.c.)
        regards, tom lane

Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
>>My final question is more of an implementation question than comments on 
>>the protocol itself.  Since the jdbc driver needs to be backwardly 
>>compatible with 7.3 and earlier servers that will speak the 2.0 
>>protocol, what is the recomended way to write the client so that it can 
>>'detect' which protocol to use.
> 
> I'd do it the other way: send a 3.0 connection request and then, if you
> get a "bad protocol" error response, send a 2.0 connection request.
> You do have to be a little careful about parsing the connection response
> since if it's 'E' it might be either 2.0 or 3.0 layout, but that can be
> handled without too much difficulty I think.  (Look at the
> already-committed code in libpq's fe-connect.c.)
> 
OK.  So this is what I plan to do:
First try a 3.0 connection.
If an error response is received from the connection attempt then check 
if the error is in a 3.0 format, if yes then report the error, else if 
not 3.0 format then try a 2.0 connection, if that returns an error then 
report it.

thanks,
--Barry