Re: [HACKERS] SQL procedures
On Wed, Nov 8, 2017 at 11:03 AM, Peter Eisentraut wrote: > On 11/8/17 11:11, Merlin Moncure wrote: >> On Wed, Nov 8, 2017 at 9:13 AM, Peter Eisentraut >> wrote: >>> I have already submitted a separate patch that addresses these questions. >> >> Maybe I'm obtuse, but I'm not seeing it? In very interested in the >> general approach to transaction management; if you've described it in >> the patch I'll read it there. Thanks for doing this. > > https://www.postgresql.org/message-id/178d3380-0fae-2982-00d6-c43100bc8...@2ndquadrant.com All right, thanks. So, *) Are you sure you want to go the SPI route? 'sql' language (non-spi) procedures might be simpler from implementation standpoint and do not need any language adjustments? *) Is it possible to jump into SPI without having a snapshot already set up. For example? If I wanted to set isolation level in a procedure, would I get impacted by this error? ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 11/8/17 11:11, Merlin Moncure wrote: > On Wed, Nov 8, 2017 at 9:13 AM, Peter Eisentraut > wrote: >> I have already submitted a separate patch that addresses these questions. > > Maybe I'm obtuse, but I'm not seeing it? In very interested in the > general approach to transaction management; if you've described it in > the patch I'll read it there. Thanks for doing this. https://www.postgresql.org/message-id/178d3380-0fae-2982-00d6-c43100bc8...@2ndquadrant.com -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On Wed, Nov 8, 2017 at 9:13 AM, Peter Eisentraut wrote: > I have already submitted a separate patch that addresses these questions. Maybe I'm obtuse, but I'm not seeing it? In very interested in the general approach to transaction management; if you've described it in the patch I'll read it there. Thanks for doing this. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 11/8/17 09:23, Merlin Moncure wrote: > I do wonder how transaction control could be added later. > > The last time I (lightly) looked at this, I was starting to think that > working transaction control into the SPI interface was the wrong > approach; pl/pgsql would have to adopt a very different set of > behaviors if it was called in a function or a proc. If you restricted > language choice to purely SQL, you could work around this problem; SPI > languages would be totally abstracted from those sets of > considerations and you could always call an arbitrary language > function if you needed to. SQL has no flow control but I'm not too > concerned about that. I have already submitted a separate patch that addresses these questions. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 11/8/17 09:33, Pavel Stehule wrote: > We can create auto session variable STATUS. This variable can be 0 > if procedure was returned without explicit RETURN value. Or it can > hold different value specified by RETURN expr. > > This value can be read by GET DIAGNOSTICS xxx = STATUS > > or some similar. > > The motivation is allow some mechanism cheaper than our exceptions. I suppose this could be a separately discussed feature. We'd also want to consider various things that PL/pgSQL pretends to be compatible with. One of the main motivations for procedures is to do more complex and expensive things including transaction control. So saving exception overhead is not really on the priority list there. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 11/6/17 16:27, Simon Riggs wrote: > You mention PARALLEL SAFE is not used for procedures. Isn't it an > architectural restriction that procedures would not be able to execute > in parallel? (At least this year) I'm not sure what you are referring to here. I don't think the functionality I'm proposing does anything in parallel or has any interaction with it. > I think we need an explanatory section of the docs, but there doesn't > seem to be one for Functions, so there is no place to add some text > that says the above. > > I found it confusing that ALTER and DROP ROUTINE exists but not CREATE > ROUTINE. At very least we should say somewhere "there is no CREATE > ROUTINE", so its absence is clearly intentional. I did wonder whether > we should have it as well, but its just one less thing to review, so > good. I'll look for a place to add some documentation around this. > Was surprised that pg_dump didn't use DROP ROUTINE, when appropriate. It's not clear to me why that would be preferred. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
Peter Eisentraut writes: > On 10/31/17 14:23, Tom Lane wrote: >> Why not use VOIDOID for the prorettype value? > We need a way to distinguish functions that are callable by SELECT and > procedures that are callable by CALL. Do procedures of this ilk belong in pg_proc at all? It seems like a large fraction of the attributes tracked in pg_proc are senseless for this purpose. A new catalog might be a better approach. In any case, I buy none of your arguments that 0 is a better choice than a new pseudotype. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 08.11.2017 17:23, Merlin Moncure wrote: On Tue, Oct 31, 2017 at 12:23 PM, Peter Eisentraut wrote: - Transaction control in procedure bodies This feature is really key, since it enables via SQL lots of things that are not possible without external coding, including: *) very long running processes in a single routine *) transaction isolation control inside the procedure (currently client app has to declare this) *) certain error handling cases that require client side support *) simple in-database threading *) simple construction of daemon scripts (yeah, you can use bgworker for this, but pure sql daemon with a cron heartbeat hook is hard to beat for simplicity) I do wonder how transaction control could be added later. The last time I (lightly) looked at this, I was starting to think that working transaction control into the SPI interface was the wrong approach; pl/pgsql would have to adopt a very different set of behaviors if it was called in a function or a proc. If you restricted language choice to purely SQL, you could work around this problem; SPI languages would be totally abstracted from those sets of considerations and you could always call an arbitrary language function if you needed to. SQL has no flow control but I'm not too concerned about that. merlin I am also very interested in answer on this question: how you are going to implement transaction control inside procedure? Right now in PostgresPRO EE supports autonomous transactions. Them are supported both for SQL and plpgsql/plpython APIs. Them are implemented by saving/restoring transaction context, so unlike most of other ATX implementations, in pgpro autonomous transaction is executed by the same backend. But it is not so easy to do: in Postgres almost any module have its own static variables which keeps transaction specific data. So we have to provide a dozen of suspend/resume functions: SuspendSnapshot(), SuspendPredicate(), SuspendStorage(), SuspendInvalidationInfo(), SuspendPgXact(), PgStatSuspend(), TriggerSuspend(), SuspendSPI()... and properly handle local cache invalidation. Patch consists of more than 5 thousand lines. So my question is whether you are going to implement something similar or use completely different approach? In first case it will be good to somehow unite our efforts... For example we can publish our ATX patch for Postgres 10. We have not done it yet, because there seems to be no chances to push this patch to community. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
2017-11-08 15:31 GMT+01:00 Pavel Stehule : > > > 2017-11-08 15:23 GMT+01:00 Peter Eisentraut com>: > >> On 10/31/17 16:50, Pavel Stehule wrote: >> > Not sure if disabling RETURN is good idea. I can imagine so optional >> > returning something like int status can be good idea. Cheaper than >> > raising a exception. >> >> We could allow a RETURN without argument in PL/pgSQL, if you just want >> to exit early. That syntax is currently not available, but it should >> not be hard to add. >> >> I don't understand the point about wanting to return an int. How would >> you pass that around, since there is no declared return type? >> > > We can create auto session variable STATUS. This variable can be 0 if > procedure was returned without explicit RETURN value. Or it can hold > different value specified by RETURN expr. > > This value can be read by GET DIAGNOSTICS xxx = STATUS > > or some similar. > The motivation is allow some mechanism cheaper than our exceptions. Regards Pavel > > > >> -- >> Peter Eisentraut http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >> > >
Re: [HACKERS] SQL procedures
2017-11-08 15:23 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > On 10/31/17 16:50, Pavel Stehule wrote: > > Not sure if disabling RETURN is good idea. I can imagine so optional > > returning something like int status can be good idea. Cheaper than > > raising a exception. > > We could allow a RETURN without argument in PL/pgSQL, if you just want > to exit early. That syntax is currently not available, but it should > not be hard to add. > > I don't understand the point about wanting to return an int. How would > you pass that around, since there is no declared return type? > We can create auto session variable STATUS. This variable can be 0 if procedure was returned without explicit RETURN value. Or it can hold different value specified by RETURN expr. This value can be read by GET DIAGNOSTICS xxx = STATUS or some similar. > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: [HACKERS] SQL procedures
On Tue, Oct 31, 2017 at 12:23 PM, Peter Eisentraut wrote: > - Transaction control in procedure bodies This feature is really key, since it enables via SQL lots of things that are not possible without external coding, including: *) very long running processes in a single routine *) transaction isolation control inside the procedure (currently client app has to declare this) *) certain error handling cases that require client side support *) simple in-database threading *) simple construction of daemon scripts (yeah, you can use bgworker for this, but pure sql daemon with a cron heartbeat hook is hard to beat for simplicity) I do wonder how transaction control could be added later. The last time I (lightly) looked at this, I was starting to think that working transaction control into the SPI interface was the wrong approach; pl/pgsql would have to adopt a very different set of behaviors if it was called in a function or a proc. If you restricted language choice to purely SQL, you could work around this problem; SPI languages would be totally abstracted from those sets of considerations and you could always call an arbitrary language function if you needed to. SQL has no flow control but I'm not too concerned about that. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 10/31/17 16:50, Pavel Stehule wrote: > Not sure if disabling RETURN is good idea. I can imagine so optional > returning something like int status can be good idea. Cheaper than > raising a exception. We could allow a RETURN without argument in PL/pgSQL, if you just want to exit early. That syntax is currently not available, but it should not be hard to add. I don't understand the point about wanting to return an int. How would you pass that around, since there is no declared return type? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 10/31/17 14:23, Tom Lane wrote: > Putting 0 in prorettype seems like a pretty bad idea. It seemed like the natural thing to do, since we use a zero OID to indicate "nothing" in many other places. > Why not use VOIDOID for the prorettype value? We need a way to distinguish functions that are callable by SELECT and procedures that are callable by CALL. > Or if there is some reason why "void" isn't the > right pseudotype, maybe you should invent a new one, analogous to the > "trigger" and "event_trigger" pseudotypes. I guess that would be doable, but I think it would make things more complicated without any gain that I can see. In the case of the pseudotypes you mention, those are the actual types mentioned in the CREATE FUNCTION command. If we invented a new pseudotype, that would run the risk of existing code creating nonsensical reverse compilations like CREATE FUNCTION RETURNS PROCEDURE. Catalog queries using prorettype == 0 would behave sensibly by default. For example, an inner or outer join against pg_type would automatically make sense. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 31 October 2017 at 17:23, Peter Eisentraut wrote: > I've been working on SQL procedures. (Some might call them "stored > procedures", but I'm not aware of any procedures that are not stored, so > that's not a term that I'm using here.) Looks good > Everything that follows is intended to align with the SQL standard, at > least in spirit. +1 > This first patch does a bunch of preparation work. It adds the > CREATE/ALTER/DROP PROCEDURE commands and the CALL statement to call a > procedure. I guess it would be really useful to have a cut-down language to use as an example, but its probably easier to just wait for PLpgSQL. You mention PARALLEL SAFE is not used for procedures. Isn't it an architectural restriction that procedures would not be able to execute in parallel? (At least this year) > It also adds ROUTINE syntax which can refer to a function or > procedure. I think we need an explanatory section of the docs, but there doesn't seem to be one for Functions, so there is no place to add some text that says the above. I found it confusing that ALTER and DROP ROUTINE exists but not CREATE ROUTINE. At very least we should say somewhere "there is no CREATE ROUTINE", so its absence is clearly intentional. I did wonder whether we should have it as well, but its just one less thing to review, so good. Was surprised that pg_dump didn't use DROP ROUTINE, when appropriate. > I have extended that to include aggregates. And then there > is a bunch of leg work, such as psql and pg_dump support. The > documentation is a lot of copy-and-paste right now; that can be > revisited sometime. The provided procedural languages (an ever more > confusing term) each needed a small touch-up to handle pg_proc entries > with prorettype == 0. > > Right now, there is no support for returning values from procedures via > OUT parameters. That will need some definitional pondering; and see > also below for a possible alternative. > > With this, you can write procedures that are somewhat compatible with > DB2, MySQL, and to a lesser extent Oracle. > > Separately, I will send patches that implement (the beginnings of) two > separate features on top of this: > > - Transaction control in procedure bodies > > - Returning multiple result sets Both of those would be good, though my suggested priority would be transaction control first and then multiple result sets, if we cannot have both this release. > (In various previous discussions on "real stored procedures" or > something like that, most people seemed to have one of these two > features in mind. I think that depends on what other SQL systems one > has worked with previously.) Almost all of the meat happens in later patches, so no other review comments. That seems seems strange in a patch of this size, but its true. Procedures are just a new type of object with very little interaction with replication, persistence or optimization. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
2017-10-31 18:23 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > I've been working on SQL procedures. (Some might call them "stored > procedures", but I'm not aware of any procedures that are not stored, so > that's not a term that I'm using here.) > > Everything that follows is intended to align with the SQL standard, at > least in spirit. > > This first patch does a bunch of preparation work. It adds the > CREATE/ALTER/DROP PROCEDURE commands and the CALL statement to call a > procedure. It also adds ROUTINE syntax which can refer to a function or > procedure. I have extended that to include aggregates. And then there > is a bunch of leg work, such as psql and pg_dump support. The > documentation is a lot of copy-and-paste right now; that can be > revisited sometime. The provided procedural languages (an ever more > confusing term) each needed a small touch-up to handle pg_proc entries > with prorettype == 0. > > Right now, there is no support for returning values from procedures via > OUT parameters. That will need some definitional pondering; and see > also below for a possible alternative. > > With this, you can write procedures that are somewhat compatible with > DB2, MySQL, and to a lesser extent Oracle. > > Separately, I will send patches that implement (the beginnings of) two > separate features on top of this: > > - Transaction control in procedure bodies > > - Returning multiple result sets > > (In various previous discussions on "real stored procedures" or > something like that, most people seemed to have one of these two > features in mind. I think that depends on what other SQL systems one > has worked with previously.) > Not sure if disabling RETURN is good idea. I can imagine so optional returning something like int status can be good idea. Cheaper than raising a exception. Regards Pavel > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
Re: [HACKERS] SQL procedures
2017-10-31 18:23 GMT+01:00 Peter Eisentraut < peter.eisentr...@2ndquadrant.com>: > I've been working on SQL procedures. (Some might call them "stored > procedures", but I'm not aware of any procedures that are not stored, so > that's not a term that I'm using here.) > > Everything that follows is intended to align with the SQL standard, at > least in spirit. > > This first patch does a bunch of preparation work. It adds the > CREATE/ALTER/DROP PROCEDURE commands and the CALL statement to call a > procedure. It also adds ROUTINE syntax which can refer to a function or > procedure. I have extended that to include aggregates. And then there > is a bunch of leg work, such as psql and pg_dump support. The > documentation is a lot of copy-and-paste right now; that can be > revisited sometime. The provided procedural languages (an ever more > confusing term) each needed a small touch-up to handle pg_proc entries > with prorettype == 0. > > Right now, there is no support for returning values from procedures via > OUT parameters. That will need some definitional pondering; and see > also below for a possible alternative. > > With this, you can write procedures that are somewhat compatible with > DB2, MySQL, and to a lesser extent Oracle. > > Separately, I will send patches that implement (the beginnings of) two > separate features on top of this: > > - Transaction control in procedure bodies > > - Returning multiple result sets > > (In various previous discussions on "real stored procedures" or > something like that, most people seemed to have one of these two > features in mind. I think that depends on what other SQL systems one > has worked with previously.) > great. I hope so I can help with testing Regards Pavel > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
Re: [HACKERS] SQL procedures
Peter Eisentraut writes: > I've been working on SQL procedures. No comment yet on the big picture here, but ... > The provided procedural languages (an ever more > confusing term) each needed a small touch-up to handle pg_proc entries > with prorettype == 0. Putting 0 in prorettype seems like a pretty bad idea. Why not use VOIDOID for the prorettype value? Or if there is some reason why "void" isn't the right pseudotype, maybe you should invent a new one, analogous to the "trigger" and "event_trigger" pseudotypes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL procedures
On 31 October 2017 at 18:23, Peter Eisentraut wrote: > I've been working on SQL procedures. (Some might call them "stored > procedures", but I'm not aware of any procedures that are not stored, so > that's not a term that I'm using here.) I guess that the DO command might have a variant to allow you to execute a procedure that isn't stored? Not suggesting you implement that, just thinking about why/when the "stored" word would be appropriate. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
