Re: [HACKERS] SQL procedures

[Merlin Moncure]

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

[Peter Eisentraut]

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

[Merlin Moncure]

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

[Peter Eisentraut]

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

[Peter Eisentraut]

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

[Peter Eisentraut]

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

[Tom Lane]

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

[Konstantin Knizhnik]

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

[Pavel Stehule]

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

[Pavel Stehule]

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

[Merlin Moncure]

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

[Peter Eisentraut]

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

[Peter Eisentraut]

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

[Simon Riggs]

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

[Pavel Stehule]

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

[Pavel Stehule]

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

[Tom Lane]

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

[Simon Riggs]

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