Extensibility of the PostgreSQL wire protocol

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Extensibility of the PostgreSQL wire protocol

Jan Wieck-3
The following is a request for discussion and comments, not a refined proposal accompanied by a working patch.

As recently publicly announced Amazon Web Services is working on Babelfish, a set of extensions that will allow PostgreSQL to be compatible with other database systems. One part of this will be an extension that allows PostgreSQL to listen on a secondary port and process a different wire protocol. The first extension we are creating in this direction is handling of the Tabular Data Stream (TDS), used by Sybase and Microsoft SQL-Server databases. It is more efficient to build an extension, that can handle the TDS protocol inside the backend, than creating a proxy process that translates from TDS to libpq protocol and back.

Creating the necessary infrastructure in the postmaster and backend will open up more possibilities, that are not tied to our compatibility efforts. Possible use cases for wire protocol extensibility include the development of a completely new, not backwards compatible PostgreSQL protocol or extending the existing wire protocol with things like 3rd party connection pool specific features (like transfer of file descriptors between pool and working backend for example).

Our current plan is to create a new set of API calls and hooks that allow to register additional wire protocols. The existing backend libpq implementation will be modified to register itself using the new API. This will serve as a proof of concept as well as ensure that the API definition is not slanted towards a specific protocol. It is also similar to the way table access methods and compression methods are added.

A wire protocol extension will be a standard PostgreSQL dynamic loadable extension module. The wire protocol extensions to load will be listed in the shared_preload_libraries GUC. The extension's Init function will register a hook function to be called where the postmaster is currently creating the libpq server sockets. This hook callback will then create the server sockets and register them for monitoring via select(2) in the postmaster main loop, using a new API function. Part of the registration information are callback functions to invoke for accepting and authenticating incoming connections, error reporting as well as a function that will implement the TCOP loop for the protocol. Ongoing work on the TDS protocol has shown us that different protocols make it desirable to have separate implementations of the TCOP loop. The TCOP function will return only after the connection has been terminated. Fortunately half the interface already exists since the sending of result sets is implemented via callback functions that are registered as the dest receiver, which works pretty well in our current code.


Regards, Jan

--
Jan Wieck
Principal Database Engineer
Amazon Web Services
Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jonah H. Harris-2
On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
The following is a request for discussion and comments, not a refined proposal accompanied by a working patch.

After implementing this three different ways inside the backend over the years, I landed on almost this identical approach for handling the MySQL, TDS, MongoDB, and Oracle protocols for NEXTGRES.

Initially, each was implemented as an background worker extension which had to handle its own networking, passing the fd off to new protocol-specific connections, etc. This worked, but duplicate a good amount of logic. It would be great to have a standard, loadable, way to add support for a new protocol.

--
Jonah H. Harris

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jan Wieck-3
Hi Jonah,

On Mon, Jan 25, 2021 at 10:18 AM Jonah H. Harris <[hidden email]> wrote:
On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
The following is a request for discussion and comments, not a refined proposal accompanied by a working patch.

After implementing this three different ways inside the backend over the years, I landed on almost this identical approach for handling the MySQL, TDS, MongoDB, and Oracle protocols for NEXTGRES.

Could any of that be open sourced? It would be an excellent addition to add one of those as example code.


Regards, Jan

 

Initially, each was implemented as an background worker extension which had to handle its own networking, passing the fd off to new protocol-specific connections, etc. This worked, but duplicate a good amount of logic. It would be great to have a standard, loadable, way to add support for a new protocol.

--
Jonah H. Harris



--
Jan Wieck
Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Robert Haas
In reply to this post by Jan Wieck-3
On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
> Our current plan is to create a new set of API calls and hooks that allow to register additional wire protocols. The existing backend libpq implementation will be modified to register itself using the new API. This will serve as a proof of concept as well as ensure that the API definition is not slanted towards a specific protocol. It is also similar to the way table access methods and compression methods are added.

If we're going to end up with an open source implementation of
something useful in contrib or whatever, then I think this is fine.
But, if not, then we're just making it easier for Amazon to do
proprietary stuff without getting any benefit for the open-source
project. In fact, in that case PostgreSQL would ensure have to somehow
ensure that the hooks don't get broken without having any code that
actually uses them, so not only would the project get no benefit, but
it would actually incur a small tax. I wouldn't say that's an
absolutely show-stopper, but it definitely isn't my first choice.

--
Robert Haas
EDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

fabriziomello

On Wed, Feb 10, 2021 at 1:43 PM Robert Haas <[hidden email]> wrote:

>
> On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
> > Our current plan is to create a new set of API calls and hooks that allow to register additional wire protocols. The existing backend libpq implementation will be modified to register itself using the new API. This will serve as a proof of concept as well as ensure that the API definition is not slanted towards a specific protocol. It is also similar to the way table access methods and compression methods are added.
>
> If we're going to end up with an open source implementation of
> something useful in contrib or whatever, then I think this is fine.
> But, if not, then we're just making it easier for Amazon to do
> proprietary stuff without getting any benefit for the open-source
> project. In fact, in that case PostgreSQL would ensure have to somehow
> ensure that the hooks don't get broken without having any code that
> actually uses them, so not only would the project get no benefit, but
> it would actually incur a small tax. I wouldn't say that's an
> absolutely show-stopper, but it definitely isn't my first choice.

As far I understood Jan's proposal is to add enough hooks on PostgreSQL to enable us to extend the wire protocol and add a contrib module as an example (maybe TDS, HTTP or just adding new capabilities to current implementation).

Regards,

--
   Fabrízio de Royes Mello
   PostgreSQL Developer at OnGres Inc. - https://ongres.com
--
Fabrízio de Royes Mello
PostgreSQL Contributor
Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jonah H. Harris-2
In reply to this post by Robert Haas
On Wed, Feb 10, 2021 at 11:43 AM Robert Haas <[hidden email]> wrote:
On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
> Our current plan is to create a new set of API calls and hooks that allow to register additional wire protocols. The existing backend libpq implementation will be modified to register itself using the new API. This will serve as a proof of concept as well as ensure that the API definition is not slanted towards a specific protocol. It is also similar to the way table access methods and compression methods are added.

If we're going to end up with an open source implementation of
something useful in contrib or whatever, then I think this is fine.
But, if not, then we're just making it easier for Amazon to do
proprietary stuff without getting any benefit for the open-source
project. In fact, in that case PostgreSQL would ensure have to somehow
ensure that the hooks don't get broken without having any code that
actually uses them, so not only would the project get no benefit, but
it would actually incur a small tax. I wouldn't say that's an
absolutely show-stopper, but it definitely isn't my first choice.

Agreed on adding substantial hooks if they're not likely to be used. While I haven't yet seen AWS' implementation or concrete proposal, given the people involved, I assume it's fairly similar to how I implemented it. Assuming that's correct and it doesn't require substantial redevelopment, I'd certainly open-source my MySQL-compatible protocol and parser implementation. From my perspective, it would be awesome if these could be done as extensions.

While I'm not planning to open source it as of yet, for my Oracle-compatible stuff, I don't think I'd be able to do anything other than the protocol as an extension given the core-related changes similar to what EDB has to do. I don't think there's any easy way to get around that. But, for the protocol and any type of simple translation to Postgres' dialect, I think that could easily be hook-based.

--
Jonah H. Harris

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Tom Lane-2
In reply to this post by Robert Haas
Robert Haas <[hidden email]> writes:
> If we're going to end up with an open source implementation of
> something useful in contrib or whatever, then I think this is fine.
> But, if not, then we're just making it easier for Amazon to do
> proprietary stuff without getting any benefit for the open-source
> project. In fact, in that case PostgreSQL would ensure have to somehow
> ensure that the hooks don't get broken without having any code that
> actually uses them, so not only would the project get no benefit, but
> it would actually incur a small tax. I wouldn't say that's an
> absolutely show-stopper, but it definitely isn't my first choice.

As others noted, a test module could be built to add some coverage here.

What I'm actually more concerned about, in this whole line of development,
is the follow-on requests that will surely occur to kluge up Postgres
to make its behavior more like $whatever.  As in "well, now that we
can serve MySQL clients protocol-wise, can't we pretty please have a
mode that makes the parser act more like MySQL".  If we start having
modes for MySQL identifier quoting, Oracle outer join syntax, yadda
yadda, it's going to be way more of a maintenance nightmare than some
hook functions.  So if we accept any patch along this line, I want to
drive a hard stake in the ground that the answer to that sort of thing
will be NO.

Assuming we're going to keep to that, though, it seems like people
doing this sort of thing will inevitably end up with a fork anyway.
So maybe we should just not bother with the first step either.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jonah H. Harris-2
On Wed, Feb 10, 2021 at 1:10 PM Tom Lane <[hidden email]> wrote:
What I'm actually more concerned about, in this whole line of development,
is the follow-on requests that will surely occur to kluge up Postgres
to make its behavior more like $whatever.  As in "well, now that we
can serve MySQL clients protocol-wise, can't we pretty please have a
mode that makes the parser act more like MySQL".  If we start having
modes for MySQL identifier quoting, Oracle outer join syntax, yadda
yadda, it's going to be way more of a maintenance nightmare than some
hook functions.  So if we accept any patch along this line, I want to
drive a hard stake in the ground that the answer to that sort of thing
will be NO.

Actually, a substantial amount can be done with hooks. For Oracle, which is substantially harder than MySQL, I have a completely separate parser that generates a PG-compatible parse tree packaged up as an extension. To handle autonomous transactions, database links, hierarchical query conversion, hints, and some execution-related items requires core changes. But, the protocol and parsing can definitely be done with hooks. And, as was mentioned previously, this isn't tied directly to emulating another database - it would enable us to support an HTTP-ish interface directly in the server as an extension as well. A lot of this can be done with background worker extensions now, which is how my stuff was primarily architected, but it's hacky when it comes to areas where the items Jan discussed could clean things up and make them more pluggable.

Assuming we're going to keep to that, though, it seems like people
doing this sort of thing will inevitably end up with a fork anyway.
So maybe we should just not bother with the first step either.

Perhaps I'm misunderstanding you, but I wouldn't throw this entire idea out (which enables a substantial addition of extensible functionality with a limited set of touchpoints) on the premise of future objections.

--
Jonah H. Harris

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Tom Lane-2
"Jonah H. Harris" <[hidden email]> writes:
> On Wed, Feb 10, 2021 at 1:10 PM Tom Lane <[hidden email]> wrote:
>> ...  If we start having
>> modes for MySQL identifier quoting, Oracle outer join syntax, yadda
>> yadda, it's going to be way more of a maintenance nightmare than some
>> hook functions.  So if we accept any patch along this line, I want to
>> drive a hard stake in the ground that the answer to that sort of thing
>> will be NO.

> Actually, a substantial amount can be done with hooks. For Oracle, which is
> substantially harder than MySQL, I have a completely separate parser that
> generates a PG-compatible parse tree packaged up as an extension. To handle
> autonomous transactions, database links, hierarchical query conversion,
> hints, and some execution-related items requires core changes.

That is a spot-on definition of where I do NOT want to end up.  Hooks
everywhere and enormous extensions that break anytime we change anything
in the core.  It's not really clear that anybody is going to find that
more maintainable than a straight fork, except to the extent that it
enables the erstwhile forkers to shove some of their work onto the PG
community.

My feeling about this is if you want to use Oracle, go use Oracle.
Don't ask PG to take on a ton of maintenance issues so you can have
a frankenOracle.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jonah H. Harris-2
On Wed, Feb 10, 2021 at 2:04 PM Tom Lane <[hidden email]> wrote:
That is a spot-on definition of where I do NOT want to end up.  Hooks
everywhere and enormous extensions that break anytime we change anything
in the core.  It's not really clear that anybody is going to find that
more maintainable than a straight fork, except to the extent that it
enables the erstwhile forkers to shove some of their work onto the PG
community.

Given the work over the last few major releases to make several other aspects of Postgres pluggable, how is implementing a pluggable protocol API any different?

To me, this sounds more like a philosophical disagreement with how people could potentially use Postgres than a technical one. My point is only that, using current PG functionality, I could equally write a pluggable storage interface for my Oracle and InnoDB data file readers/writers, which would similarly allow for the creation of a Postgres franken-Oracle by extension only.

I don't think anyone is asking for hooks for all the things I mentioned - a pluggable transaction manager, for example, doesn't make much sense. But, when it comes to having actually done this vs. posited about its usefulness, I'd say it has some merit and doesn't really introduce that much complexity or maintenance overhead to core - whether the extensions still work properly is up to the extension authors... isn't that the whole point of extensions?

--
Jonah H. Harris

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jan Wieck-3
In reply to this post by Robert Haas


On Wed, Feb 10, 2021 at 11:43 AM Robert Haas <[hidden email]> wrote:
On Mon, Jan 25, 2021 at 10:07 AM Jan Wieck <[hidden email]> wrote:
> Our current plan is to create a new set of API calls and hooks that allow to register additional wire protocols. The existing backend libpq implementation will be modified to register itself using the new API. This will serve as a proof of concept as well as ensure that the API definition is not slanted towards a specific protocol. It is also similar to the way table access methods and compression methods are added.

If we're going to end up with an open source implementation of
something useful in contrib or whatever, then I think this is fine.
But, if not, then we're just making it easier for Amazon to do
proprietary stuff without getting any benefit for the open-source
project. In fact, in that case PostgreSQL would ensure have to somehow
ensure that the hooks don't get broken without having any code that
actually uses them, so not only would the project get no benefit, but
it would actually incur a small tax. I wouldn't say that's an
absolutely show-stopper, but it definitely isn't my first choice.

At this very moment there are several parts to this. One is the hooks to make wire protocols into loadable modules, which is what this effort is about. Another is the TDS protocol as it is being implemented for Babelfish and third is the Babelfish extension itself. Both will require additional hooks and APIs I am not going to address here. I consider them not material to my effort.

As for making the wire protocol itself expandable I really see a lot of potential outside of what Amazon wants here. And I would not be advertising it if it would be for Babelfish alone. As I laid out, just the ability for a third party to add additional messages for special connection pool support would be enough to make it useful. There also have been discussions in the JDBC subproject to combine certain messages into one single message. Why not allow the JDBC project to develop their own, JDBC-optimized backend side? Last but not least, what would be wrong with listening for MariaDB clients?

I am planning on a follow up project to this, demoting libpq itself to just another loadable protocol. Just the way procedural languages are all on the same level because that is how I developed the loadable, procedural language handler all those years ago. 

Considering how spread out and quite frankly unorganized our wire protocol handling is, this is not a small order.


Regards, Jan






 

--
Robert Haas
EDB: http://www.enterprisedb.com


--
Jan Wieck
Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Robert Haas
In reply to this post by Tom Lane-2
On Wed, Feb 10, 2021 at 2:04 PM Tom Lane <[hidden email]> wrote:
> That is a spot-on definition of where I do NOT want to end up.  Hooks
> everywhere and enormous extensions that break anytime we change anything
> in the core.  It's not really clear that anybody is going to find that
> more maintainable than a straight fork, except to the extent that it
> enables the erstwhile forkers to shove some of their work onto the PG
> community.

+1.

Making the lexer and parser extensible seems desirable to me. It would
be beneficial not only for companies like EDB and Amazon that might
want to extend the grammar in various ways, but also for extension
authors. However, it's vastly harder than Jan's proposal to make the
wire protocol pluggable. The wire protocol is pretty well-isolated
from the rest of the system. As long as you can get queries out of the
packets the client sends and package up the results to send back, it's
all good. The parser, on the other hand, is not at all well-isolated
from the rest of the system. There's a LOT of code that knows a whole
lot of stuff about the structure of parse trees, so your variant
parser can't produce parse trees for new kinds of DDL, or for new
query constructs. And if it parsed some completely different syntax
where, say, joins were not explicit, it would still have to figure out
how to represent them in a way that looked just like it came out of
the regular parser -- otherwise, parse analysis and query planning and
so forth are not going to work, unless you go and change a lot of
other code too, and I don't really have any idea how we could solve
that, even in theory. But that kind of thing just isn't a problem for
the proposal on this thread.

That being said, I'm not in favor of transferring maintenance work to
the community for this set of hooks any more than I am for something
on the parsing side. In general, I'm in favor of as much extensibility
as we can reasonably create, but with a complicated proposal like this
one, the community should expect to be able to get something out of
it. And so far what I hear Jan saying is that these hooks could in
theory be used for things other than Amazon's proprietary efforts and
those things could in theory bring benefits to the community, but
there are no actual plans to do anything with this that would benefit
anyone other than Amazon. Which seems to bring us right back to
expecting the community to maintain things for the benefit of
third-party forks.

--
Robert Haas
EDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jonah H. Harris-2
On Thu, Feb 11, 2021 at 9:28 AM Robert Haas <[hidden email]> wrote:
That being said, I'm not in favor of transferring maintenance work to
the community for this set of hooks any more than I am for something
on the parsing side. In general, I'm in favor of as much extensibility
as we can reasonably create, but with a complicated proposal like this
one, the community should expect to be able to get something out of
it. And so far what I hear Jan saying is that these hooks could in
theory be used for things other than Amazon's proprietary efforts and
those things could in theory bring benefits to the community, but
there are no actual plans to do anything with this that would benefit
anyone other than Amazon. Which seems to bring us right back to
expecting the community to maintain things for the benefit of
third-party forks.

I'm quite sure I said I'd open source my MySQL implementation, which allows Postgres to appear to MySQL clients as a MySQL/MariaDB server. This is neither proprietary nor Amazon-related and makes Postgres substantially more useful for a large number of applications.

As Jan said in his last email, they're not proposing all the different aspects needed. In fact, nothing has actually been proposed yet. This is an entirely philosophical debate. I don't even know what's being proposed at this point - I just know it *could* be useful. Let's just wait and see what is actually proposed before shooting it down, yes?

--
Jonah H. Harris

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Robert Haas
On Thu, Feb 11, 2021 at 9:42 AM Jonah H. Harris <[hidden email]> wrote:
> I'm quite sure I said I'd open source my MySQL implementation, which allows Postgres to appear to MySQL clients as a MySQL/MariaDB server. This is neither proprietary nor Amazon-related and makes Postgres substantially more useful for a large number of applications.

OK. There's stuff to think about there, too: do we want that in
contrib? Is it in good enough shape to be in contrib even if we did?
If it's not in contrib, how do we incorporate it into, say, the
buildfarm, so that we know if we break something? Is it actively
maintained and stable, so that if it needs adjustment for upstream
changes we can count on that getting addressed in a timely fashion? I
don't know the answers to these questions and am not trying to
prejudge, but I think they are important and relevant questions.

> As Jan said in his last email, they're not proposing all the different aspects needed. In fact, nothing has actually been proposed yet. This is an entirely philosophical debate. I don't even know what's being proposed at this point - I just know it *could* be useful. Let's just wait and see what is actually proposed before shooting it down, yes?

I don't think I'm trying to shoot anything down, because as I said, I
like extensibility and am generally in favor of it. Rather, I'm
expressing a concern which seems to me to be justified, based on what
was posted. I'm sorry that my tone seems to have aggravated you, but
it wasn't intended to do so.

--
Robert Haas
EDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Tom Lane-2
Robert Haas <[hidden email]> writes:
> On Thu, Feb 11, 2021 at 9:42 AM Jonah H. Harris <[hidden email]> wrote:
>> As Jan said in his last email, they're not proposing all the different
>> aspects needed. In fact, nothing has actually been proposed yet. This
>> is an entirely philosophical debate. I don't even know what's being
>> proposed at this point - I just know it *could* be useful. Let's just
>> wait and see what is actually proposed before shooting it down, yes?

> I don't think I'm trying to shoot anything down, because as I said, I
> like extensibility and am generally in favor of it. Rather, I'm
> expressing a concern which seems to me to be justified, based on what
> was posted. I'm sorry that my tone seems to have aggravated you, but
> it wasn't intended to do so.

Likewise, the point I was trying to make is that a "pluggable wire
protocol" is only a tiny part of what would be needed to have a credible
MySQL, Oracle, or whatever clone.  There are large semantic differences
from those products; there are maintenance issues arising from the fact
that we whack structures like parse trees around all the time; and so on.
Maybe there is some useful thing that can be accomplished here, but we
need to consider the bigger picture rather than believing (without proof)
that a few hook variables will be enough to do anything.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Andrew Dunstan

On 2/11/21 10:06 AM, Tom Lane wrote:

> Robert Haas <[hidden email]> writes:
>> On Thu, Feb 11, 2021 at 9:42 AM Jonah H. Harris <[hidden email]> wrote:
>>> As Jan said in his last email, they're not proposing all the different
>>> aspects needed. In fact, nothing has actually been proposed yet. This
>>> is an entirely philosophical debate. I don't even know what's being
>>> proposed at this point - I just know it *could* be useful. Let's just
>>> wait and see what is actually proposed before shooting it down, yes?
>> I don't think I'm trying to shoot anything down, because as I said, I
>> like extensibility and am generally in favor of it. Rather, I'm
>> expressing a concern which seems to me to be justified, based on what
>> was posted. I'm sorry that my tone seems to have aggravated you, but
>> it wasn't intended to do so.
> Likewise, the point I was trying to make is that a "pluggable wire
> protocol" is only a tiny part of what would be needed to have a credible
> MySQL, Oracle, or whatever clone.  There are large semantic differences
> from those products; there are maintenance issues arising from the fact
> that we whack structures like parse trees around all the time; and so on.
> Maybe there is some useful thing that can be accomplished here, but we
> need to consider the bigger picture rather than believing (without proof)
> that a few hook variables will be enough to do anything.



Yeah. I think we'd need a fairly fully worked implementation to see
where it goes. Is Amazon going to release (under TPL) its TDS
implementation of this? That might go a long way to convincing me this
is worth considering.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com



Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Jim Mlodgenski-2


On Thu, Feb 11, 2021 at 10:29 AM Andrew Dunstan <[hidden email]> wrote:

On 2/11/21 10:06 AM, Tom Lane wrote:
> Robert Haas <[hidden email]> writes:
>> On Thu, Feb 11, 2021 at 9:42 AM Jonah H. Harris <[hidden email]> wrote:
>>> As Jan said in his last email, they're not proposing all the different
>>> aspects needed. In fact, nothing has actually been proposed yet. This
>>> is an entirely philosophical debate. I don't even know what's being
>>> proposed at this point - I just know it *could* be useful. Let's just
>>> wait and see what is actually proposed before shooting it down, yes?
>> I don't think I'm trying to shoot anything down, because as I said, I
>> like extensibility and am generally in favor of it. Rather, I'm
>> expressing a concern which seems to me to be justified, based on what
>> was posted. I'm sorry that my tone seems to have aggravated you, but
>> it wasn't intended to do so.
> Likewise, the point I was trying to make is that a "pluggable wire
> protocol" is only a tiny part of what would be needed to have a credible
> MySQL, Oracle, or whatever clone.  There are large semantic differences
> from those products; there are maintenance issues arising from the fact
> that we whack structures like parse trees around all the time; and so on.
> Maybe there is some useful thing that can be accomplished here, but we
> need to consider the bigger picture rather than believing (without proof)
> that a few hook variables will be enough to do anything.



Yeah. I think we'd need a fairly fully worked implementation to see
where it goes. Is Amazon going to release (under TPL) its TDS
implementation of this? That might go a long way to convincing me this
is worth considering.

Everything is planned to be released under the Apache 2.0 license so people are free to do with it as they choose. 

Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Joshua Drake-2
In reply to this post by Tom Lane-2


On Wed, Feb 10, 2021 at 11:04 AM Tom Lane <[hidden email]> wrote:
"Jonah H. Harris" <[hidden email]> writes:
> On Wed, Feb 10, 2021 at 1:10 PM Tom Lane <[hidden email]> wrote:
>> ...  If we start having
>> modes for MySQL identifier quoting, Oracle outer join syntax, yadda
>> yadda, it's going to be way more of a maintenance nightmare than some
>> hook functions.  So if we accept any patch along this line, I want to
>> drive a hard stake in the ground that the answer to that sort of thing
>> will be NO.

> Actually, a substantial amount can be done with hooks. For Oracle, which is
> substantially harder than MySQL, I have a completely separate parser that
> generates a PG-compatible parse tree packaged up as an extension. To handle
> autonomous transactions, database links, hierarchical query conversion,
> hints, and some execution-related items requires core changes.

That is a spot-on definition of where I do NOT want to end up.  Hooks
everywhere and enormous extensions that break anytime we change anything
in the core.  It's not really clear that anybody is going to find that
more maintainable than a straight fork, except to the extent that it
enables the erstwhile forkers to shove some of their work onto the PG
community.

My feeling about this is if you want to use Oracle, go use Oracle.
Don't ask PG to take on a ton of maintenance issues so you can have
a frankenOracle.

PostgreSQL over the last decade spent a considerable amount of time allowing it to become extensible outside of core. We are now useful in workloads nobody would have considered in 2004 or 2008.

The more extensibility we add, the LESS we maintain. It is a lot easier to maintain an API than it is an entire kernel. When I look at all the interesting features coming from the ecosystem, they are all built on the hooks that this community worked so hard to create. This idea is an extension of that and a result of the community's success.

The more extensible we make PostgreSQL, the more the hacker community can innovate without damaging the PostgreSQL reputation as a rock solid database system.

Features like these only enable the entire community to innovate. Is the real issue that the more extensible PostgreSQL is, the more boring it will become?

JD

 

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

fabriziomello
In reply to this post by Tom Lane-2

On Thu, Feb 11, 2021 at 12:07 PM Tom Lane <[hidden email]> wrote:

>
> Robert Haas <[hidden email]> writes:
> > On Thu, Feb 11, 2021 at 9:42 AM Jonah H. Harris <[hidden email]> wrote:
> >> As Jan said in his last email, they're not proposing all the different
> >> aspects needed. In fact, nothing has actually been proposed yet. This
> >> is an entirely philosophical debate. I don't even know what's being
> >> proposed at this point - I just know it *could* be useful. Let's just
> >> wait and see what is actually proposed before shooting it down, yes?
>
> > I don't think I'm trying to shoot anything down, because as I said, I
> > like extensibility and am generally in favor of it. Rather, I'm
> > expressing a concern which seems to me to be justified, based on what
> > was posted. I'm sorry that my tone seems to have aggravated you, but
> > it wasn't intended to do so.
>
> Likewise, the point I was trying to make is that a "pluggable wire
> protocol" is only a tiny part of what would be needed to have a credible
> MySQL, Oracle, or whatever clone.  There are large semantic differences
> from those products; there are maintenance issues arising from the fact
> that we whack structures like parse trees around all the time; and so on.
> Maybe there is some useful thing that can be accomplished here, but we
> need to consider the bigger picture rather than believing (without proof)
> that a few hook variables will be enough to do anything.
>

Just to don't miss the point, creating a compat protocol to mimic others (TDS, 
MySQL, etc) is just one use case.

There are other use cases to make wire protocol extensible, for example for 
telemetry I can use some hooks to propagate context [1] and get more detailed 
tracing information about the negotiation between frontend and backend and 
being able to implement a truly query tracing tool, for example.

Another use case is extending the current protocol to, for example, send more 
information about query execution on CommandComplete command instead of 
just the number of affected rows.

About the HTTP protocol I think PG should have it, maybe pure HTTP (no REST, 
just HTTP) because it's the most interoperable. Performance can still be very good 
with HTTP2, and you have a huge ecosystem of tools and proxies (like Envoy) that 
would do wonders with this. You could safely query a db from a web page (passing 
through proxies that would do auth, TLS, etc). Or maybe a higher performing gRPC 
version (which is also HTTP2 and is amazing), but this makes it a bit more difficult 
to query from a web page. In either case, context propagation is already built-in, and 
in a standard way.

Regards,


--
   Fabrízio de Royes Mello
   PostgreSQL Developer at OnGres Inc. - https://ongres.com
--
Fabrízio de Royes Mello
PostgreSQL Contributor
Reply | Threaded
Open this post in threaded view
|

Re: Extensibility of the PostgreSQL wire protocol

Dave Cramer-7
In reply to this post by Robert Haas


On Thu, 11 Feb 2021 at 09:28, Robert Haas <[hidden email]> wrote:
On Wed, Feb 10, 2021 at 2:04 PM Tom Lane <[hidden email]> wrote:
> That is a spot-on definition of where I do NOT want to end up.  Hooks
> everywhere and enormous extensions that break anytime we change anything
> in the core.  It's not really clear that anybody is going to find that
> more maintainable than a straight fork, except to the extent that it
> enables the erstwhile forkers to shove some of their work onto the PG
> community.

+1.

Making the lexer and parser extensible seems desirable to me. It would
be beneficial not only for companies like EDB and Amazon that might
want to extend the grammar in various ways, but also for extension
authors. However, it's vastly harder than Jan's proposal to make the
wire protocol pluggable. The wire protocol is pretty well-isolated
from the rest of the system. As long as you can get queries out of the
packets the client sends and package up the results to send back, it's
all good.

I would have to disagree that the wire protocol is well-isolated. Sending and receiving are not in a single file
The codes are not even named constants so trying to find a specific one is difficult.

Anything that would clean this up would be a benefit


That being said, I'm not in favor of transferring maintenance work to
the community for this set of hooks any more than I am for something
on the parsing side. In general, I'm in favor of as much extensibility
as we can reasonably create, but with a complicated proposal like this
one, the community should expect to be able to get something out of
it. And so far what I hear Jan saying is that these hooks could in
theory be used for things other than Amazon's proprietary efforts and
those things could in theory bring benefits to the community, but
there are no actual plans to do anything with this that would benefit
anyone other than Amazon. Which seems to bring us right back to
expecting the community to maintain things for the benefit of
third-party forks.

if this proposal brought us the ability stream results that would be a huge plus!
 
Dave Cramer
www.postgres.rocks

123