Thread

protocol change in 7.4

Neil Conway <neilc@samurai.com> — 2002-11-05T00:22:54Z

There has been some previous discussion of changing the FE/BE protocol
in 7.4, in order to fix several problems. I think this is worth doing:
if we can resolve all these issues in a single release, it will lessen
the upgrade difficulties for users.

I'm aware of the following problems that need a protocol change to fix
them:

        (1) Add an optional textual message to NOTIFY

        (2) Remove the hard-coded limits on database and user names
            (SM_USER, SM_DATABASE), replace them with variable-length
            fields.

        (3) Remove some legacy elements in the startup packet
            ('unused' can go -- perhaps 'tty' as well). I think the
            'length' field of the password packet is also not used,
            but I'll need to double-check that.

        (4) Fix the COPY protocol (Tom?)

        (5) Fix the Fastpath protocol (Tom?)

        (6) Protocol-level support for prepared queries, in order to
            bypass the parser (and maybe be more compatible with the
            implementation of prepared queries in other databases).

        (7) Include the current transaction status, since it's
            difficult for the client app to determine it for certain
            (Tom/Bruce?)

If I've missed anything or if there is something you think we should
add, please let me know.

I can implement (1), (2), (3), and possibly (7), if someone can tell
me exactly what is required (my memory of the discussion relating to
this is fuzzy). The rest is up for grabs.

Finally, how should we manage the transition? I wasn't around for the
earlier protocol changes, so I'd appreciate any input on steps we can
take to improve backward-compatibility.

Cheers,

Neil

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

Re: protocol change in 7.4

Mike Mascari <mascarm@mascari.com> — 2002-11-05T00:32:56Z

Neil Conway wrote:
> There has been some previous discussion of changing the FE/BE protocol
> in 7.4, in order to fix several problems. I think this is worth doing:
> if we can resolve all these issues in a single release, it will lessen
> the upgrade difficulties for users.

<snip>

> 
> If I've missed anything or if there is something you think we should
> add, please let me know.

Is there any thought about changing the protocol to support 
two-phase commit? Not that 2PC and distributed transactions 
would be implemented in 7.4, but to prevent another protocol 
change in the future?

Mike Mascari
mascarm@mascari.com

Re: protocol change in 7.4

Neil Conway <neilc@samurai.com> — 2002-11-05T00:42:55Z

Mike Mascari <mascarm@mascari.com> writes:
> Is there any thought about changing the protocol to support
> two-phase commit? Not that 2PC and distributed transactions would be
> implemented in 7.4, but to prevent another protocol change in the
> future?

My understanding is that 2PC is one way to implement multi-master
replication. If that's what you're referring to, then I'm not sure I
see the point: the multi-master replication project (pgreplication)
doesn't use 2PC, due to apparent scalability problems (not to mention
that it also uses a separate channel for communications between
backends on different nodes).

Cheers,

Neil

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

Re: protocol change in 7.4

darren@up.hrcoxmail.com — 2002-11-05T00:55:08Z

>
>
>
>I'm now implementing 2PC replication and distributed transaction. My 2PC
>needs some supports in startup packet to establish a replication session
>or a recovery session.
>
>BTW, my 2PC replication is working, and I'm implementing 2PC recovery now.
>
I would like to here more about your implementation.  Do you have some 
documentation that I
could read?  

If not, perhaps (if you have the time)  you could put together a post 
describing your work.  Like
Is it an internal or external solution.  Are you sending SQL or tuples 
in your update messages.  
How are you handling failure detection?  Is this partial or full 
replication?


Please forgive me for asking so many questions, but I'm rather intrigued 
by database
replication.

Darren


>
>

Re: protocol change in 7.4

Mike Mascari <mascarm@mascari.com> — 2002-11-05T01:10:29Z

Neil Conway wrote:
> Mike Mascari <mascarm@mascari.com> writes:
> 
>>Is there any thought about changing the protocol to support
>>two-phase commit? Not that 2PC and distributed transactions would be
>>implemented in 7.4, but to prevent another protocol change in the
>>future?
> 
> My understanding is that 2PC is one way to implement multi-master
> replication. If that's what you're referring to, then I'm not sure I
> see the point: the multi-master replication project (pgreplication)
> doesn't use 2PC, due to apparent scalability problems (not to mention
> that it also uses a separate channel for communications between
> backends on different nodes).

Actually, I was thinking along the lines of a true CREATE 
DATABASE LINK implementation, where multiple databases could 
participate in a distributed transaction. That would require the 
backend in which the main query is executing to act as the 
"coordinator" and each of the other participating databases to 
act as "cohorts". And would require a protocol change to support 
the PREPARE, COMMIT-VOTE/ABORT-VOTE reply, and an ACK message 
following the completion of the distributed COMMIT or ABORT.

Mike Mascari
mascarm@mascari.com

Re: protocol change in 7.4

Satoshi Nagayasu <snaga@snaga.org> — 2002-11-05T01:14:21Z

Hi all,

Mike Mascari <mascarm@mascari.com> wrote:
> Is there any thought about changing the protocol to support 
> two-phase commit? Not that 2PC and distributed transactions 
> would be implemented in 7.4, but to prevent another protocol 
> change in the future?

I'm now implementing 2PC replication and distributed transaction. My 2PC
needs some support in startup packet to establish a replication session
and a recovery session.

BTW, 2PC replication is working, and I'm implementing 2PC recovery now.

-- 
NAGAYASU Satoshi <snaga@snaga.org>

Re: protocol change in 7.4

Ross J. Reedstrom <reedstrm@rice.edu> — 2002-11-05T01:22:55Z

On Mon, Nov 04, 2002 at 08:10:29PM -0500, Mike Mascari wrote:

> Actually, I was thinking along the lines of a true CREATE 
> DATABASE LINK implementation, where multiple databases could 
> participate in a distributed transaction. That would require the 
> backend in which the main query is executing to act as the 
> "coordinator" and each of the other participating databases to 
> act as "cohorts". And would require a protocol change to support 
> the PREPARE, COMMIT-VOTE/ABORT-VOTE reply, and an ACK message 
> following the completion of the distributed COMMIT or ABORT.

Right, you need TPC in order for pgsql to participate in transactions
that span anything outside the DB proper. A DB link is one example,
or an external transaction manager that coordinates DB and filesystem
updates, for example. Zope could use this, to coordinate the DB with
it's internal object store.

Ross

Re: protocol change in 7.4

Satoshi Nagayasu <pgsql@snaga.org> — 2002-11-05T01:33:16Z

Hi,

Mike Mascari <mascarm@mascari.com> wrote:
> Is there any thought about changing the protocol to support 
> two-phase commit? Not that 2PC and distributed transactions 
> would be implemented in 7.4, but to prevent another protocol 
> change in the future?

I'm now implementing 2PC replication and distributed transaction. My 2PC
needs some supports in startup packet to establish a replication session
or a recovery session.

BTW, my 2PC replication is working, and I'm implementing 2PC recovery now.

-- 
NAGAYASU Satoshi <snaga@snaga.org>

Re: protocol change in 7.4

Satoshi Nagayasu <pgsql@snaga.org> — 2002-11-05T02:38:08Z

Darren Johnson <darren@up.hrcoxmail.com> wrote:
> I would like to here more about your implementation.  Do you have some 
> documentation that I
> could read?

Documentation is not available, but I have some slides for my presentation.

http://snaga.org/pgsql/20021018_2pc.pdf

Some answers for your questions may be in these slides.

And a current source code is available from:
http://snaga.org/pgsql/pgsql-20021025.tgz

> If not, perhaps (if you have the time)  you could put together a post 
> describing your work.  Like
> Is it an internal or external solution.  Are you sending SQL or tuples 
> in your update messages.  
> How are you handling failure detection?  Is this partial or full 
> replication?

It is an internal solution. In 2PC, pre-commit and commit are required.
So my implementation has some internal modifications on transaction
handling, log recording and else.

-- 
NAGAYASU Satoshi <snaga@snaga.org>

Re: protocol change in 7.4

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-05T04:46:10Z

I don't see why 2PC would require any protocol-level change.  I would
think that the API would be something like

	BEGIN;
	issue some commands ...
	PRECOMMIT;
	-- if the above does not return an error, then
	COMMIT;

In other words, 2PC would require some new commands, but a new command
doesn't affect the protocol layer.

			regards, tom lane

Re: protocol change in 7.4

Grant Finnemore <grantf@guruhut.co.za> — 2002-11-05T05:28:54Z

Questions have arisen during discussions about errors relating
to how to support error codes without changing the FE/BE
protocols. (see TODO.detail/error)

Now that the protocol is up for revision, how about supporting
sql state strings, error codes, and other information directly in
the protocol.

Regards,
Grant

Neil Conway wrote:

> There has been some previous discussion of changing the FE/BE protocol
> in 7.4, in order to fix several problems. I think this is worth doing:
> if we can resolve all these issues in a single release, it will lessen
> the upgrade difficulties for users.
>
> I'm aware of the following problems that need a protocol change to fix
> them:
>
>         (1) Add an optional textual message to NOTIFY
>
>         (2) Remove the hard-coded limits on database and user names
>             (SM_USER, SM_DATABASE), replace them with variable-length
>             fields.
>
>         (3) Remove some legacy elements in the startup packet
>             ('unused' can go -- perhaps 'tty' as well). I think the
>             'length' field of the password packet is also not used,
>             but I'll need to double-check that.
>
>         (4) Fix the COPY protocol (Tom?)
>
>         (5) Fix the Fastpath protocol (Tom?)
>
>         (6) Protocol-level support for prepared queries, in order to
>             bypass the parser (and maybe be more compatible with the
>             implementation of prepared queries in other databases).
>
>         (7) Include the current transaction status, since it's
>             difficult for the client app to determine it for certain
>             (Tom/Bruce?)
>
> If I've missed anything or if there is something you think we should
> add, please let me know.
>
> I can implement (1), (2), (3), and possibly (7), if someone can tell
> me exactly what is required (my memory of the discussion relating to
> this is fuzzy). The rest is up for grabs.
>
> Finally, how should we manage the transition? I wasn't around for the
> earlier protocol changes, so I'd appreciate any input on steps we can
> take to improve backward-compatibility.
>
> Cheers,
>
> Neil
>

Re: protocol change in 7.4

Neil Conway <neilc@samurai.com> — 2002-11-05T05:43:37Z

Grant Finnemore <grantf@guruhut.co.za> writes:
> Now that the protocol is up for revision, how about supporting
> sql state strings, error codes, and other information directly in
> the protocol.

Ah, thanks for pointing that out. Error codes would be another thing
we can ideally support in 7.4, and we'd need a protocol change to do
it properly, AFAICS. IIRC, Peter E. expressed some interest in doing
this...

Cheers,

Neil

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

Re: protocol change in 7.4

Karel Zak <zakkr@zf.jcu.cz> — 2002-11-05T08:35:31Z

On Mon, Nov 04, 2002 at 07:22:54PM -0500, Neil Conway wrote:
>         (1) Add an optional textual message to NOTIFY
> 
>         (2) Remove the hard-coded limits on database and user names
>             (SM_USER, SM_DATABASE), replace them with variable-length
>             fields.
> 
>         (3) Remove some legacy elements in the startup packet
>             ('unused' can go -- perhaps 'tty' as well). I think the
>             'length' field of the password packet is also not used,
>             but I'll need to double-check that.
> 
>         (4) Fix the COPY protocol (Tom?)
> 
>         (5) Fix the Fastpath protocol (Tom?)
> 
>         (6) Protocol-level support for prepared queries, in order to
>             bypass the parser (and maybe be more compatible with the
>             implementation of prepared queries in other databases).
> 
>         (7) Include the current transaction status, since it's
>             difficult for the client app to determine it for certain
>             (Tom/Bruce?)

 (8) Error codes (maybe needn't change protocol)
     - without this is PostgreSQL useless in real DB aplication

 (9) Think about full dynamic charset encoding (add new encoding on
     the fly)


    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Re: protocol change in 7.4

Christof Petig <christof@petig-baender.de> — 2002-11-05T22:54:57Z

Neil Conway wrote:
>         (6) Protocol-level support for prepared queries, in order to
>             bypass the parser (and maybe be more compatible with the
>             implementation of prepared queries in other databases).

Let me add
   (6b)	Protocol level support for query parameters. This would actually
	make (6) more powerful and speed up non prepared (but similar)
	queries via the query cache (which is already there IIRC).
	[I talk about   <statement> USING :var ... ]

   (n)	Platform independant binary representation of parameters and
	results (like in CORBA). This can _really_ speed up
	communication with compiled programs if you take the time to
	implement it. This was previously planned for a future
	CORBA fe/be protocol, but this does not seem to come any time
	soon.

  (n+1)	Optional additional Result qualifiers. E.g. dynamic embedded
	sql has a
	flag to indicate that this column is a key. Previously it was
	impossible to set this flag to a meaningful value. Also
	the standard has additional statistical information about the
	size of the column etc. If it's unclear what I'm talking about
	I will look up the exact location in the standard (it's embedded
	sql, dynamic sql, get descriptor)

Yours
    Christof

Re: protocol change in 7.4

Christof Petig <christof@petig-baender.de> — 2002-11-06T07:08:45Z

Christof Petig wrote:
> Neil Conway wrote:
> 
>>         (6) Protocol-level support for prepared queries, in order to
>>             bypass the parser (and maybe be more compatible with the
>>             implementation of prepared queries in other databases).
> 
> 
> Let me add
>   (6b)    Protocol level support for query parameters. This would actually
>     make (6) more powerful and speed up non prepared (but similar)
>     queries via the query cache (which is already there IIRC).
>     [I talk about   <statement> USING :var ... ]
> 
>   (n)    Platform independant binary representation of parameters and
>     results (like in CORBA). This can _really_ speed up
>     communication with compiled programs if you take the time to
>     implement it. This was previously planned for a future
>     CORBA fe/be protocol, but this does not seem to come any time
>     soon.

After one night's sleep I think that perhaps a CORBA based protocol 
might be less work (but I have no idea about a decent authentification 
schema, I'd tend to reuse the already authentificated stream). A 
corbaized query-only interface might easily cover these issues and be 
less work than a full corba backend access. JDBC (I don't know much 
about it) might give a reasonable interface design (perhaps combined 
with a libpq[++|xx] like interface if there's benefit to it).

>  (n+1)    Optional additional Result qualifiers. E.g. dynamic embedded
>     sql has a
>     flag to indicate that this column is a key. Previously it was
>     impossible to set this flag to a meaningful value. Also
>     the standard has additional statistical information about the
>     size of the column etc. If it's unclear what I'm talking about
>     I will look up the exact location in the standard (it's embedded
>     sql, dynamic sql, get descriptor)

This does not need an implementation soon. But the new protocol should 
allow future things like this.

All these proposals are motivated by (future) ecpg [C/C++] needs. So 
IMHO the ODBC, JDBC, libpqxx people might be interested in many of these 
issues, too. We definitely should make sure to have asked them.

Yours
    Christof

Re: protocol change in 7.4

Haris Peco <snpe@snpe.co.yu> — 2002-11-07T18:40:16Z

Is exists patch for 7.4devel ?

regards 
Haris Peco
On Tuesday 05 November 2002 01:14 am, Satoshi Nagayasu wrote:
> Hi all,
>
> Mike Mascari <mascarm@mascari.com> wrote:
> > Is there any thought about changing the protocol to support
> > two-phase commit? Not that 2PC and distributed transactions
> > would be implemented in 7.4, but to prevent another protocol
> > change in the future?
>
> I'm now implementing 2PC replication and distributed transaction. My 2PC
> needs some support in startup packet to establish a replication session
> and a recovery session.
>
> BTW, 2PC replication is working, and I'm implementing 2PC recovery now.

Re: protocol change in 7.4

korry <korry@starband.net> — 2002-11-07T20:24:10Z

>There has been some previous discussion of changing the FE/BE protocol
>in 7.4, in order to fix several problems. I think this is worth doing:
>if we can resolve all these issues in a single release, it will lessen
>the upgrade difficulties for users.

Here are a couple of other changes you might consider (maybe these changes 
already exist and I just don't know about them):

a)  Make much of the metadata sent to the client optional.  When I execute 
20 fetches against the same cursor, I don't need the same metadata 20 
times.  For narrow result sets, the metadata can easily double or triple 
the number of bytes sent across the net.  It looks like the protocol needs 
the field count, but everything else seems to be sent for the convenience 
of the client application.

b)  Send a decoded version of atttypmod - specifically, decode the 
precision and scale for numeric types.

Re: protocol change in 7.4

korry <korry@starband.net> — 2002-11-07T21:50:00Z

> > b)  Send a decoded version of atttypmod - specifically, decode the
> > precision and scale for numeric types.
> >
>I want decode type,length,precision and scale

Type is returned by PQftype(), length is returned by PQfsize().  Precision 
and scale are encoded in the return value from PQfmod() and you have to 
have a magic decoder ring to understand them. (Magic decoder rings are 
available, you just have to read the source code :-)

PQftype() is not easy to use because it returns an OID instead of a name 
(or a standardized symbol), but I can't think of anything better to return 
to the client.   Of course if you really want to make use of PQftype(), you 
can preload a client-side cache of type definitions.  I seem to remember 
seeing a patch a while back that would build the cache and decode precision 
and scale too.

  PQfsize() is entertaining, but not often what you really want (you really 
want the width of the widest value in the column after conversion to some 
string format - it seems reasonable to let the client applicatin worry 
about that, although maybe that would be a useful client-side libpq function).

Re: protocol change in 7.4

Haris Peco <snpe@snpe.co.yu> — 2002-11-07T22:32:43Z

> b)  Send a decoded version of atttypmod - specifically, decode the
> precision and scale for numeric types.
>
I want decode type,length,precision and scale 

regards
Haris Peco

Re: protocol change in 7.4

Haris Peco <snpe@snpe.co.yu> — 2002-11-07T22:49:11Z

On Thursday 07 November 2002 09:50 pm, korry wrote:
> > > b)  Send a decoded version of atttypmod - specifically, decode the
> > > precision and scale for numeric types.
> >
> >I want decode type,length,precision and scale
>
> Type is returned by PQftype(), length is returned by PQfsize().  Precision
> and scale are encoded in the return value from PQfmod() and you have to
> have a magic decoder ring to understand them. (Magic decoder rings are
> available, you just have to read the source code :-)
>
> PQftype() is not easy to use because it returns an OID instead of a name
> (or a standardized symbol), but I can't think of anything better to return
> to the client.   Of course if you really want to make use of PQftype(), you
> can preload a client-side cache of type definitions.  I seem to remember
> seeing a patch a while back that would build the cache and decode precision
> and scale too.
>
>   PQfsize() is entertaining, but not often what you really want (you really
> want the width of the widest value in the column after conversion to some
> string format - it seems reasonable to let the client applicatin worry
> about that, although maybe that would be a useful client-side libpq
> function).
>
>
I want this in any catalog view

regards
Haris Peco

Re: protocol change in 7.4

Satoshi Nagayasu <pgsql@snaga.org> — 2002-11-08T00:54:30Z

> Is exists patch for 7.4devel ?

A full tarball is based on 7.3devel.
There is no patch for 7.4devel.

http://snaga.org/pgsql/

-- 
NAGAYASU Satoshi <snaga@snaga.org>

Re: protocol change in 7.4

Hannu Krosing <hannu@tm.ee> — 2002-11-08T09:29:10Z

snpe kirjutas R, 08.11.2002 kell 03:49:
> >   PQfsize() is entertaining, but not often what you really want (you really
> > want the width of the widest value in the column after conversion to some
> > string format - it seems reasonable to let the client applicatin worry
> > about that, although maybe that would be a useful client-side libpq
> > function).
> >
> >
> I want this in any catalog view

But this will make such a view terribly slow, as ith has to do
max(length(field)) over the whole table for any field displayed

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

Re: protocol change in 7.4

Haris Peco <snpe@snpe.co.yu> — 2002-11-08T11:25:07Z

On Friday 08 November 2002 09:29 am, Hannu Krosing wrote:
> snpe kirjutas R, 08.11.2002 kell 03:49:
> > >   PQfsize() is entertaining, but not often what you really want (you
> > > really want the width of the widest value in the column after
> > > conversion to some string format - it seems reasonable to let the
> > > client applicatin worry about that, although maybe that would be a
> > > useful client-side libpq function).
> >
> > I want this in any catalog view
>
> But this will make such a view terribly slow, as ith has to do
> max(length(field)) over the whole table for any field displayed
>
Why not with this functions ?

Re: protocol change in 7.4

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-10T22:28:22Z

I have added the following TODO item on protocol changes:

> * Wire Protocol Changes
>       o Show transaction status in psql
>       o Allow binding of query parameters, support for prepared queries
>       o Add optional textual message to NOTIFY
>       o Remove hard-coded limits on user/db/password names
>       o Remove unused elements of startup packet (unused, tty, passlength)
>       o Fix COPY/fastpath protocol?
>       o Replication support?
>       o Error codes
>       o Dynamic character set handling
>       o Special passing of binary values in platform-neutral format (bytea?)
>       o ecpg improvements?
>       o Add decoded type, length, precision

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

snpe wrote:
> On Thursday 07 November 2002 09:50 pm, korry wrote:
> > > > b)  Send a decoded version of atttypmod - specifically, decode the
> > > > precision and scale for numeric types.
> > >
> > >I want decode type,length,precision and scale
> >
> > Type is returned by PQftype(), length is returned by PQfsize().  Precision
> > and scale are encoded in the return value from PQfmod() and you have to
> > have a magic decoder ring to understand them. (Magic decoder rings are
> > available, you just have to read the source code :-)
> >
> > PQftype() is not easy to use because it returns an OID instead of a name
> > (or a standardized symbol), but I can't think of anything better to return
> > to the client.   Of course if you really want to make use of PQftype(), you
> > can preload a client-side cache of type definitions.  I seem to remember
> > seeing a patch a while back that would build the cache and decode precision
> > and scale too.
> >
> >   PQfsize() is entertaining, but not often what you really want (you really
> > want the width of the widest value in the column after conversion to some
> > string format - it seems reasonable to let the client applicatin worry
> > about that, although maybe that would be a useful client-side libpq
> > function).
> >
> >
> I want this in any catalog view
> 
> regards
> Haris Peco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

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