patch: shared session variables

On Tue, Aug 14, 2012 at 3:46 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

patch that implements "shared" client/server session variables

I don't really see what we can do with this that we can't do without this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2012/8/30 Robert Haas <robertmhaas@gmail.com>:

On Tue, Aug 14, 2012 at 3:46 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

patch that implements "shared" client/server session variables

I don't really see what we can do with this that we can't do without this.

a motivation for this patch was discussion about parametrised DO
statement - and simple possibility of access to host variables (psql)
variables from server - PL scripts.

It is based on Tom's and Magnus's ideas - it is secure, because only
variables explicitly mentioned in shared namespace are "shared".

http://archives.postgresql.org/pgsql-hackers/2012-06/msg01506.php

Regards

Pavel

On Thu, Aug 30, 2012 at 2:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2012/8/30 Robert Haas <robertmhaas@gmail.com>:

On Tue, Aug 14, 2012 at 3:46 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

patch that implements "shared" client/server session variables

I don't really see what we can do with this that we can't do without this.

a motivation for this patch was discussion about parametrised DO
statement - and simple possibility of access to host variables (psql)
variables from server - PL scripts.

It is based on Tom's and Magnus's ideas - it is secure, because only
variables explicitly mentioned in shared namespace are "shared".

Sure, but you could get to the same place by issuing a SET command for
just the particular variable you want to use with DO. You don't
really need a magic facility for it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Aug 30, 2012 at 2:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2012/8/30 Robert Haas <robertmhaas@gmail.com>:

On Tue, Aug 14, 2012 at 3:46 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

patch that implements "shared" client/server session variables

I don't really see what we can do with this that we can't do without this.

a motivation for this patch was discussion about parametrised DO
statement - and simple possibility of access to host variables (psql)
variables from server - PL scripts.

It is based on Tom's and Magnus's ideas - it is secure, because only
variables explicitly mentioned in shared namespace are "shared".

Sure, but you could get to the same place by issuing a SET command for
just the particular variable you want to use with DO. You don't
really need a magic facility for it.

FWIW, I don't particularly care for this idea either. It may be less
klugy than the original proposal, but it's still a kluge. Also, it's
not very sensible to consider extensions of this sort unless we have
ambitions of turning psql into a full-fledged scripting language,
with conditionals and iteration at the very least. I do not want
to go there. If you need scripting capability, there are lots of
better tools out there already.

regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:

a motivation for this patch was discussion about parametrised DO
statement - and simple possibility of access to host variables (psql)
variables from server - PL scripts.

Pavel, you didn't say what you think about the WITH FUNCTION proposal?

And you didn't say how do you want to turn a utility statement into
something that is able to return a result set.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2012/8/31 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

a motivation for this patch was discussion about parametrised DO
statement - and simple possibility of access to host variables (psql)
variables from server - PL scripts.

Pavel, you didn't say what you think about the WITH FUNCTION proposal?

I don't like it - this proposal is too "lispish" - it is not SQL

And you didn't say how do you want to turn a utility statement into
something that is able to return a result set.

if we support "real" procedures ala sybase procedures (MySQL, MSSQL..)
- then we can return result with same mechanism - there are no
significant difference between DO and CALL statements - you don't know
what will be result type before you call it.

Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:

Pavel, you didn't say what you think about the WITH FUNCTION proposal?

I don't like it - this proposal is too "lispish" - it is not SQL

We're not doing lambda here, only extending a facility that we rely on
today. The function would be named, for one. I don't know what you mean
by SQL being lispish here, and I can't imagine why it would be something
to avoid.

And you didn't say how do you want to turn a utility statement into
something that is able to return a result set.

if we support "real" procedures ala sybase procedures (MySQL, MSSQL..)
- then we can return result with same mechanism - there are no
significant difference between DO and CALL statements - you don't know
what will be result type before you call it.

Currently we don't have CALL, and we have DO which is not a query but a
utility statement. Are you proposing to implement CALL? What would be
the difference between making DO a query and having CALL?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2012/8/31 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

Pavel, you didn't say what you think about the WITH FUNCTION proposal?

I don't like it - this proposal is too "lispish" - it is not SQL

We're not doing lambda here, only extending a facility that we rely on
today. The function would be named, for one. I don't know what you mean
by SQL being lispish here, and I can't imagine why it would be something
to avoid.

And you didn't say how do you want to turn a utility statement into
something that is able to return a result set.

if we support "real" procedures ala sybase procedures (MySQL, MSSQL..)
- then we can return result with same mechanism - there are no
significant difference between DO and CALL statements - you don't know
what will be result type before you call it.

Currently we don't have CALL, and we have DO which is not a query but a
utility statement. Are you proposing to implement CALL? What would be
the difference between making DO a query and having CALL?

defacto a CALL statement implementation can solve this issue.

The core of this issue is an impossibility using parameters for
utility statements. CALL and DO are utility statements - and if we
can use parameters for CALL, then we can do it for DO too.

CALL statement starts a predefined batch - inside this batch, you can
do anything - can use COMMIT, ROLLBACK, SELECTs, ... DO is some batch
with immediate start. Sure, there is relative significant between
stored procedures implemented in popular RDBMS and although I don't
like T-SQL too much, I like sybase concept of stored procedures - it
is strong and very useful for maintaining tasks.

Regards

Pavel

Having read through this thread, the consensus seems to be that we don't
want this patch as it is (and I agree with that).

As I understand it, you are trying to solve two problems:

1. Passing parameters to a DO statement. You could quote the parameters
and inline them in the block itself in the client, but quoting can be
difficult to do correctly and is not as readable anyway, so I agree it
would be good to be able to pass them separately.

2. Returning results from a DO statement. At the moment, a DO statement
is a utility statement, so it cannot return a result set.

Two different approaches to these problems have been discussed that have
some support:

A) WITH FUNCTION syntax, to allow defining a "temporary function" for a
single query, similar to the current WITH syntax.
(http://archives.postgresql.org/pgsql-hackers/2012-07/msg00426.php)

B) DO ... USING <parameter list> syntax. This is a straightforward
extension of the current DO syntax, just adding a parameter list to it.
Not sure how to return a result set from this, perhaps also support a
RETURNS keyword, similar to CREATE FUNCTION.

I'm ok with either of those approaches. A) would be more flexible, while
B) would be straightforward extension of what we already have.

I'm marking this patch as rejected in the commitfest app. Please pursue
the WITH FUNCTION or DO ... USING syntax instead.

Thanks!

On 31.08.2012 21:27, Pavel Stehule wrote:

2012/8/31 Dimitri Fontaine<dimitri@2ndquadrant.fr>:

Pavel Stehule<pavel.stehule@gmail.com> writes:

Pavel, you didn't say what you think about the WITH FUNCTION proposal?

I don't like it - this proposal is too "lispish" - it is not SQL

We're not doing lambda here, only extending a facility that we rely on
today. The function would be named, for one. I don't know what you mean
by SQL being lispish here, and I can't imagine why it would be something
to avoid.

And you didn't say how do you want to turn a utility statement into
something that is able to return a result set.

if we support "real" procedures ala sybase procedures (MySQL, MSSQL..)
- then we can return result with same mechanism - there are no
significant difference between DO and CALL statements - you don't know
what will be result type before you call it.

Currently we don't have CALL, and we have DO which is not a query but a
utility statement. Are you proposing to implement CALL? What would be
the difference between making DO a query and having CALL?

defacto a CALL statement implementation can solve this issue.

The core of this issue is an impossibility using parameters for
utility statements. CALL and DO are utility statements - and if we
can use parameters for CALL, then we can do it for DO too.

CALL statement starts a predefined batch - inside this batch, you can
do anything - can use COMMIT, ROLLBACK, SELECTs, ... DO is some batch
with immediate start. Sure, there is relative significant between
stored procedures implemented in popular RDBMS and although I don't
like T-SQL too much, I like sybase concept of stored procedures - it
is strong and very useful for maintaining tasks.

Regards

Pavel

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

- Heikki

Hello

2012/9/24 Heikki Linnakangas <hlinnakangas@vmware.com>:

Having read through this thread, the consensus seems to be that we don't
want this patch as it is (and I agree with that).

As I understand it, you are trying to solve two problems:

1. Passing parameters to a DO statement. You could quote the parameters and
inline them in the block itself in the client, but quoting can be difficult
to do correctly and is not as readable anyway, so I agree it would be good
to be able to pass them separately.

2. Returning results from a DO statement. At the moment, a DO statement is a
utility statement, so it cannot return a result set.

Two different approaches to these problems have been discussed that have
some support:

A) WITH FUNCTION syntax, to allow defining a "temporary function" for a
single query, similar to the current WITH syntax.
(http://archives.postgresql.org/pgsql-hackers/2012-07/msg00426.php)

B) DO ... USING <parameter list> syntax. This is a straightforward extension
of the current DO syntax, just adding a parameter list to it. Not sure how
to return a result set from this, perhaps also support a RETURNS keyword,
similar to CREATE FUNCTION.

I'm ok with either of those approaches. A) would be more flexible, while B)
would be straightforward extension of what we already have.

I'm marking this patch as rejected in the commitfest app. Please pursue the
WITH FUNCTION or DO ... USING syntax instead.

A basic discussion should be about a character of DO statements. What
it should be? Temporary function or temporary stored procedure. Both
variants are correct. I prefer idea, where DO is temporary procedure.
A reply on this question solves a question about returning result from
DO statement. We can support more calling context for DO statements
like we do with general functions - and then both your @A and @B
variants should be possible supported.

A blocker for @B is unsupported parametrization for utility
statements. If we can support a CALL statement, then we will have
solution for parametrized DO statement too.

From my perspective, missing parametrized DO is not blocker for
anything. Is not super elegant, but workaround needs only a few lines
more. \gsets solves last missing functionality.

So I would to close this topic - we should to implement procedures and
CALL statement first - this functionality is relative clean - a base
is described by ANSI/SQL - and as next step we can reimplement
anynymous (temporary) functions or procedures.

Regards

Pavel