Thread: patch: shared session variables
Hello patch that implements "shared" client/server session variables Regards Pavel Stehule
Attachment
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 > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company
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 > > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
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 > > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
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 > > 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