Thread: Roadmap for FE/BE protocol redesign
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
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, Pennsylvania19073
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
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
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
> * 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. How about a bitmap field of "additional features" supported by client/backend that can be negotiated? (Doesn't have to be bitmap, of course, but the idea..) That way the server (or client - doesn't matter who goes first, I guess) could say "I support X and Y, but not Z and Q". If the client supports both X and Y, both are enabled. If it supports only X, then only X is enabled. X and Y? Well, the first thing that comes to mind is SSL support. I'm not sure if it's still that way, but at least it used to be a pretty ugly kludge there with the connection being dropped and re-connected in some cases. I also seem to recall there was some talk about on-wire compression support - that would also be ideal for something like this. I'm sure there can be more... It should be easy to make it version-independent - just make both client and server reject any "protocol features" that are unknown. These are features that can be enabled/disabled in a backend/client of the same version. It cannot (at least not easily) be handled with protocol versioning, since you can have for example 7.4 with or without SSL. //Magnus
"Magnus Hagander" <mha@sollentuna.net> writes: > X and Y? Well, the first thing that comes to mind is SSL support. I'm > not sure if it's still that way, but at least it used to be a pretty > ugly kludge there with the connection being dropped and re-connected in > some cases. SSL support is a bad example, since it would have to be negotiated long before any more general-purpose negotiation could occur. (You do want the connection authentication exchange to happen under cover of SSL, no?) ISTM most of the other features you might want to turn on and off can be handled as SET commands: the client tries to SET a variable, the backend either accepts it or returns an error. No need for special protocol support if you do it that way. Can you point to any examples that have to have a special protocol feature instead? regards, tom lane
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.
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 withFATAL: 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
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
> > X and Y? Well, the first thing that comes to mind is SSL > support. I'm > > not sure if it's still that way, but at least it used to be > a pretty > > ugly kludge there with the connection being dropped and > re-connected > > in some cases. > > SSL support is a bad example, since it would have to be > negotiated long before any more general-purpose negotiation > could occur. (You do want the connection authentication > exchange to happen under cover of SSL, no?) Certainly - it would be kind of stupid otherwise... The idea was to make it possible to negotiate more than just SSL at this early stage (such as compression) in a more easy-to-maintain-backwards-compatibility way. Could be that only SSL needs to be enabled that early, and in that case having a generic mechanism in place to handle it would be unnecessary. > ISTM most of the other features you might want to turn on and > off can be handled as SET commands: the client tries to SET a > variable, the backend either accepts it or returns an error. > No need for special protocol support if you do it that way. > Can you point to any examples that have to have a special > protocol feature instead? Umm. Not really. I'm sure such a thing as compression could be enabled with "SET COMPRESSION=1" (as long as it's clearly defined when compression starts - e.g. after the server has sent it response, but before the next information is sent). The general idea was to make a framework there to make it easier to add something like that in the future. Something that came up when adding the SSL negotiation - since that was very kludgy to do with the current protocol. But again, if you foresee that no othe rfeatures will require negotiation at that early stage, it's probably overkill. //Magnus
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 Inouehttp://www.geocities.jp/inocchichichi/psqlodbc/
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
"Magnus Hagander" <mha@sollentuna.net> writes: > The general idea was to make a framework there to make it easier to add > something like that in the future. Something that came up when adding > the SSL negotiation - since that was very kludgy to do with the current > protocol. But again, if you foresee that no othe rfeatures will require > negotiation at that early stage, it's probably overkill. I can't think of anything except compression that could be interesting at that early stage, so I'm not seeing a reason to invent a general negotiation mechanism. Even if we thought we needed one, we don't have a lot of flexibility at that stage of the game, because neither side yet knows what version the other is, and so assuming that the other side knows of the mechanism is bogus. The existing method for requesting SSL is a tad klugy, granted, but I'm not sure we can do any better for transport-option negotiation. regards, tom lane
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 ...
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 Inouehttp://www.geocities.jp/inocchichichi/psqlodbc/
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, Pennsylvania19073
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' ...
"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
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