Thread: Re: Transform groups (more FE/BE protocol issues)

Peter Eisentraut <peter_e@gmx.net> writes:
> The SQL standard defines a concept called "transform groups", which are
> basically pairs of input/ouput functions that can be switched between.
> The standard talks about different transform groups for different host
> languages, so this essentially selects between different binary output
> formats.

> I think this would fit naturally with many things we are doing and want to
> do.

I've been thinking about this a little more; it seems to open up some
questions about the current design of the new FE/BE protocol.

* There are two places presently in the protocol where the client can
specify text/binary via a boolean (represented as int8).  To move to a
transform-group world, we could redefine that field as a group selector:
0 = standard text representation, 1 = standard binary representation,
other values = reserved for future implementation.  The obvious
question is whether we should widen these fields to more than 8 bits.
Are we likely to need more than 256 transform groups?  More than 64K?
(Read on before you answer, since some of the points below suggest we
might be transmitting a lot more of these fields than at present;
keeping them narrow might be important for bandwidth reasons.)

* The DataRow/BinaryRow distinction obviously doesn't scale to multiple
transform groups.  I propose dropping the BinaryRow message type in
protocol 3.0, and instead carrying the format code (group selector)
somewhere else.  A straightforward conversion would be to add it to the
body of DataRow, but I'm not convinced that's the best place; again,
read on.

* At what granularity do you wish to select the transform group type for
data being transferred in or out?  Right now we've essentially assumed
that you only need to specify it once for an entire command result, but
it's fairly easy to imagine scenarios where this isn't what you want.
For example, very many people are going to want to send or receive bytea
fields as raw binary, since that's more or less the native
representation for the client (nobody likes escaping or unescaping).
It does not follow that they want raw binary for, say, timestamp fields
appearing in the same table.  The problem gets even more pressing if you
want to use transform groups as a substitute for things like DateStyle,
as Peter suggested in the above-quoted message.

* ISTM the most natural granularity for specifying transform group is at
the column level.  I can't see a good use-case for varying transform
type across rows of a select result, but being able to select it for
each column has clear usefulness.

* That leaves us with two issues: where does the client say what it
wants, and where does the backend report the actual transform group used
for each column?  For SELECTs, from an efficiency point of view it'd be
nicest to have the client request desired transforms in Bind, and then
we could have RowDescription report the actual transforms used for each
column.  This way there'd be no need to include transform info in
DataRow, which would be redundant if one doesn't expect per-row changes
in transform.  I'd suggest allowing Bind to specify either a single
transform group to be applied to all columns, or per-column groups.
We'd remove the output-is-binary field from Execute.

* More or less the same considerations apply for parameter values being
sent in a Bind message.  Here I'd opt for always sending a transform
group for each parameter value being sent.

* The client can hardly be expected to select per-column transforms in
Bind if it doesn't know the result column datatypes yet.  In the
protocol document as it stands today, there's no way to find out the
result datatypes except a portal Describe --- which requires that you've
already done Bind.  I took out the result datatypes in
prepared-statement Describe because it seemed unnecessarily complicated
to implement (there's no support in the backend right now to derive a
tupdesc from a plan without starting the executor).  Clearly that'll
have to be put back though.  Presumably the RowDescriptor returned by
prepared-statement Describe will return default (zero == text) transform
groups for all columns, and the client will have to know to believe its
own requests instead if it doesn't trouble to do a portal Describe after
Bind.

* Textual COPY doesn't need any changes since it'll presumably always
use transform group zero, but what do we do with binary COPY?  Probably
the best thing is to add an optional header field showing the transform
group for each column, with the default assumption being that all
columns are transform group 1 (standard binary).  I don't know what the
user does in the COPY TO command to select other transform groups, but
that's not a protocol-level issue so it need not be solved today.

Comments?  In particular I need some feedback about how wide to make the
transform-group fields ...
        regards, tom lane

Tom Lane writes:

> I've been thinking about this a little more; it seems to open up some
> questions about the current design of the new FE/BE protocol.

A transform group is a named object attached to a type.  A transform group
converts a user-defined type to a standard SQL type or vice versa.  (The
rationale being that clients know how to handle standard SQL types.)  A
client selects a transform group as session state, either individually for
each type (SET TRANFORM GROUP FOR TYPE mytype 'mygroup') or using a
default mechanism (SET DEFAULT TRANSFORM GROUP 'mygroup'), which means
that all types that have a transform group with the given name activate
that group.

> * There are two places presently in the protocol where the client can
> specify text/binary via a boolean (represented as int8).  To move to a
> transform-group world, we could redefine that field as a group selector:
> 0 = standard text representation, 1 = standard binary representation,
> other values = reserved for future implementation.

I don't think we need any more representations than those two, and the
transform group feature can be independent from this.

Here's an example:  a set of transform groups (for various data types)
that convert SQL data types to more C-like types, for example timestamp to
struct tm.  Would you want to pass struct tm-data over the wire as a blob
of 36 bytes?  I would rather get a standard binary representation with a
length word, so a middle layer can still copy this data around without
having to be data type-aware.

In fact, this suggests that the format field should not be represented at
all in the protocol and only be handled as session state.  Users can just
say "I want these types in text and these types in binary" and that is
respected for the rest of the session.  Obviously there are some backward
compatibility problem in this.

> * The DataRow/BinaryRow distinction obviously doesn't scale to multiple
> transform groups.  I propose dropping the BinaryRow message type in
> protocol 3.0, and instead carrying the format code (group selector)
> somewhere else.  A straightforward conversion would be to add it to the
> body of DataRow, but I'm not convinced that's the best place; again,
> read on.

We would need a format code for each returned column.

It would be the decision of the transform group about which format to
return.  If you want a transform group that provides your client
application with binary data, then you write your transform functions to
return bytea.  If you want a text format, write them to return cstring.
(Other, less subtle flagging mechanisms could be invented.)
DataRow/BinaryRow basically only tells the client which method is used to
signal the end of data.  Both methods have some use, but there aren't a
lot of other methods that will come into use soon.

> * At what granularity do you wish to select the transform group type for
> data being transferred in or out?  Right now we've essentially assumed
> that you only need to specify it once for an entire command result, but
> it's fairly easy to imagine scenarios where this isn't what you want.

As mentioned above, it would be per data type and session.  I think that
makes sense.  As you mentioned, certainly you would want to have different
choices for different data types.  Also, session makes sense, because if
an application is set up to handle a given type in a given format in one
place, then it probably wants to handle it like that every time.  This
would move away the format decision from the command level to the session
level.  Most configuration parameters are session level, so that makes
sense.

> * That leaves us with two issues: where does the client say what it
> wants,

SET-like commands; see at the top.

> and where does the backend report the actual transform group used for
> each column?

If you want to be pedantic, you don't report it at all, because the client
selected it, so it got it.  The client will know how to read the data
pieces, because it knows whether they are text (terminating zero byte) or
binary (starts with length byte).  What exactly is inside doesn't
necessarily need to be reported.  (We don't report the date style in each
result either.)  If you want to report it, the RowDescription with one OID
for each column would seem the best place.

> * More or less the same considerations apply for parameter values being
> sent in a Bind message.  Here I'd opt for always sending a transform
> group for each parameter value being sent.

Same here; you don't need it.  If you set the format to X, then you send
the data in format X, and things work.  If the data does not conform to
format X, the tranform function will tell you soon enough.

> * The client can hardly be expected to select per-column transforms in
> Bind if it doesn't know the result column datatypes yet.  In the
> protocol document as it stands today, there's no way to find out the
> result datatypes except a portal Describe --- which requires that you've
> already done Bind.

This is not a problem if transform groups are per-type, not per-column.


The consequence for the protocol:  Keep the text/binary distinction, but
make it per-column in the result.  For backward compatibility, the client
can still choose between text and binary on a command-level basis, but we
should move this to a session parameter, and if command and session
settings are incompatible, one prevails or we signal an error.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> A transform group is a named object attached to a type.  A transform group
> converts a user-defined type to a standard SQL type or vice versa.  (The
> rationale being that clients know how to handle standard SQL types.)

Hmm, I hope they didn't restrict the design so that transform groups
could do *only* that; if so, they'd be far less useful than I thought.
(Your example below doesn't work if so, because "struct tm" isn't SQL.)

> Here's an example:  a set of transform groups (for various data types)
> that convert SQL data types to more C-like types, for example timestamp to
> struct tm.  Would you want to pass struct tm-data over the wire as a blob
> of 36 bytes?  I would rather get a standard binary representation with a
> length word, so a middle layer can still copy this data around without
> having to be data type-aware.

Sure, but the length word now becomes part of the protocol wrapper
rather than being considered part of the data.  A struct tm
representation seems like a fine thing to me (at least if you like
struct tm, which I don't especially --- but if that's what your app is
coded to use, then that is exactly what you want).

> In fact, this suggests that the format field should not be represented at
> all in the protocol and only be handled as session state.  Users can just
> say "I want these types in text and these types in binary" and that is
> respected for the rest of the session.

I don't think I like that, because it assumes that the client side of
things is monolithic, which it is not.  The client library (libpq, jdbc,
etc) may or may not be aware of what the client application has done,
and neither of them may be aware of what the user has done via GUC
settings or interactive SQL commands.  One of the things we're trying to
fix in this protocol revision is to ensure that the client library has
enough information to do its job, *without* any assumptions about what
the user is doing.

Barry gave a fine example of this, which was that JDBC has a hard time
parsing timestamp values (as it's supposed to be able to do) without
knowing what DateStyle is set to.  What you're proposing above would
add even more hidden state to the protocol.

Perhaps we could add ParameterStatus support for these things, though.
I see your point that it's unlikely the client's desires will change
much over a session, so having to repeat the request with each query
might be overkill.

> The consequence for the protocol:  Keep the text/binary distinction, but
> make it per-column in the result.  For backward compatibility, the client
> can still choose between text and binary on a command-level basis, but we
> should move this to a session parameter, and if command and session
> settings are incompatible, one prevails or we signal an error.

I was actually envisioning that text and binary would become
interchangeable as far as the protocol is concerned: DataRow carries a
length and some bytes, and what's in the bytes is interpreted based on
format codes.  So I don't see a need to think in terms of the formats
being classified as text and binary.
        regards, tom lane