Thread

1. gset updated patch

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-11-03T18:45:36Z

   Hello
   
   here is a updated patch
   
   Regards
   
   Pavel
   

2. Re: gset updated patch

   Karl O. Pinc <kop@meme.com> — 2012-11-16T05:08:06Z

   On 11/03/2012 01:45:36 PM, Pavel Stehule wrote:
   > Hello
   > 
   > here is a updated patch
   
   This message does not appear to be threaded so I'm not
   sure I've read the whole back-history.  Also, I don't
   really know what I'm doing.  Never the less, I want
   to try to contribute to somebody else's patch so
   here's my comments.  Make of them what you will.
   
   I know there's been criticism for touching too many
   different parts of the code, but writing your own
   mini-lexical analyzer does not make sense to me.
   There ought to be a clean way to move that into psqlscan.l
   and let lex do it's job.
   
   Since the result of a \gset is undefined if the query
   fails it makes me nervous that psql would
   continue running after \gset failure in a
   non-interactive environment.  Perhaps \gset/psql
   should distinguish between interactive and
   non-interactive environments in the same way
   shell does?  Do you have any use-cases where it 
   makes sense to continue after error in a
   non-interactive environment?
   
   As long as I'm talking crazy talk, why not
   abandon psql as a shell language and instead make a
   pl/pgsql interpreter with readlne() in front
   of it?   Solve all these language-related
   issues by using an actual programming language.  :-)
   
   I hope at least some of the above is helpful
   and I'm not just injecting noise into the system.
   
   Regards,
   
   Karl <kop@meme.com>
   Free Software:  "You don't pay back, you pay forward."
                    -- Robert A. Heinlein
   
   
   
   

3. Re: gset updated patch

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-11-16T06:21:11Z

   Hello
   
   2012/11/16 Karl O. Pinc <kop@meme.com>:
   > On 11/03/2012 01:45:36 PM, Pavel Stehule wrote:
   >> Hello
   >>
   >> here is a updated patch
   >
   > This message does not appear to be threaded so I'm not
   > sure I've read the whole back-history.  Also, I don't
   > really know what I'm doing.  Never the less, I want
   > to try to contribute to somebody else's patch so
   > here's my comments.  Make of them what you will.
   >
   > I know there's been criticism for touching too many
   > different parts of the code, but writing your own
   > mini-lexical analyzer does not make sense to me.
   > There ought to be a clean way to move that into psqlscan.l
   > and let lex do it's job.
   
   it is about 20 rows only, so it is not critical and for me is not
   important where parser is
   
   >
   > Since the result of a \gset is undefined if the query
   > fails it makes me nervous that psql would
   > continue running after \gset failure in a
   > non-interactive environment.  Perhaps \gset/psql
   > should distinguish between interactive and
   > non-interactive environments in the same way
   > shell does?  Do you have any use-cases where it
   > makes sense to continue after error in a
   > non-interactive environment?
   
   No, I have not a use case for continuing after error - but any
   continue after error is risk - so it is similar like any other code -
   it is same risk as broken output for \g statement forwarded to file
   and processed later.
   
   It can be mentioned in documentation - so preferred usage of this
   command is in stop error mode
   
   >
   > As long as I'm talking crazy talk, why not
   > abandon psql as a shell language and instead make a
   > pl/pgsql interpreter with readlne() in front
   > of it?   Solve all these language-related
   > issues by using an actual programming language.  :-)
   >
   
   I though about it more time, but I don't thinking so this has a sense.
   Actually we cannot do perfect autocomplete for significantly simpler
   SQL and there are lot of client side interprets - is not reason for
   next one. I use psql together bash and it works well. But just very
   simple task as storing some volatile data for repetitive usage is
   relative laborious and it is a motivation for this patch. In psql I
   can simply work with any fields of returned record - what is more
   terrible work outside psql
   
   Regards
   
   Pavel Stehuke
   
   > I hope at least some of the above is helpful
   > and I'm not just injecting noise into the system.
   >
   > Regards,
   >
   > Karl <kop@meme.com>
   > Free Software:  "You don't pay back, you pay forward."
   >                  -- Robert A. Heinlein
   >
   
   
   

4. Re: gset updated patch

   Karl O. Pinc <kop@meme.com> — 2012-11-16T15:42:01Z

   Hi Pavel,
   
   On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
   > 2012/11/16 Karl O. Pinc <kop@meme.com>:
   
   > > As long as I'm talking crazy talk, why not
   > > abandon psql as a shell language and instead make a
   > > pl/pgsql interpreter with readlne() in front
   > > of it?   Solve all these language-related
   > > issues by using an actual programming language.  :-)
   
   > I though about it more time, but I don't thinking so this has a 
   > sense.
   > Actually we cannot do perfect autocomplete for significantly simpler
   > SQL and there are lot of client side interprets - is not reason for
   > next one. I use psql together bash and it works well. But just very
   > simple task as storing some volatile data for repetitive usage is
   > relative laborious and it is a motivation for this patch. In psql I
   > can simply work with any fields of returned record - what is more
   > terrible work outside psql
   
   You might consider using "do".
   
   http://www.postgresql.org/docs/9.1/static/sql-do.html
   
   If you need to maintain a single connection you can do
   some interesting things with socat to feed a running psql
   in the background.
   
    socat -u UNIX-RECV:/tmp/msock EXEC:psql &
   
   Followed by lots of
   
    echo bar | socat -u STDIN UNIX-SENDTO:/tmp/mysock
   
   \o can be used to send output for pickup, although
   you do need to fuss around with the asynchronous nature
   of things to be sure you're waiting for output.
   I used inotifywait for this.  YMMV.
   
   Regards,
   
   Karl <kop@meme.com>
   Free Software:  "You don't pay back, you pay forward."
                    -- Robert A. Heinlein
   
   
   
   

5. Re: gset updated patch

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-11-19T08:30:49Z

   Hello
   
   2012/11/16 Karl O. Pinc <kop@meme.com>:
   > Hi Pavel,
   >
   > On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
   >> 2012/11/16 Karl O. Pinc <kop@meme.com>:
   >
   >> > As long as I'm talking crazy talk, why not
   >> > abandon psql as a shell language and instead make a
   >> > pl/pgsql interpreter with readlne() in front
   >> > of it?   Solve all these language-related
   >> > issues by using an actual programming language.  :-)
   >
   >> I though about it more time, but I don't thinking so this has a
   >> sense.
   >> Actually we cannot do perfect autocomplete for significantly simpler
   >> SQL and there are lot of client side interprets - is not reason for
   >> next one. I use psql together bash and it works well. But just very
   >> simple task as storing some volatile data for repetitive usage is
   >> relative laborious and it is a motivation for this patch. In psql I
   >> can simply work with any fields of returned record - what is more
   >> terrible work outside psql
   >
   > You might consider using "do".
   
   it is reason, why I don't thinking about plpgsql on client side. But I
   don't understand how it is related to gset ?
   
   I remember, there is one significant limit of DO statement - it cannot
   return table - so it cannot substitute psql simple scripts. But I
   don't would open this topic now - it is related to real stored
   procedures implementation, and it is long time task. So gset allow
   some simple tasks solve simply
   
   Regards
   
   Pavel Stehule
   
   >
   > http://www.postgresql.org/docs/9.1/static/sql-do.html
   >
   > If you need to maintain a single connection you can do
   > some interesting things with socat to feed a running psql
   > in the background.
   >
   >  socat -u UNIX-RECV:/tmp/msock EXEC:psql &
   >
   > Followed by lots of
   >
   >  echo bar | socat -u STDIN UNIX-SENDTO:/tmp/mysock
   >
   > \o can be used to send output for pickup, although
   > you do need to fuss around with the asynchronous nature
   > of things to be sure you're waiting for output.
   > I used inotifywait for this.  YMMV.
   >
   > Regards,
   >
   > Karl <kop@meme.com>
   > Free Software:  "You don't pay back, you pay forward."
   >                  -- Robert A. Heinlein
   >
   
   
   

6. Re: gset updated patch

   Karl O. Pinc <kop@meme.com> — 2012-11-19T15:17:19Z

   On 11/19/2012 02:30:49 AM, Pavel Stehule wrote:
   > Hello
   > 
   > 2012/11/16 Karl O. Pinc <kop@meme.com>:
   > > Hi Pavel,
   > >
   > > On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
   > >> 2012/11/16 Karl O. Pinc <kop@meme.com>:
   > >
   > >> > As long as I'm talking crazy talk, why not
   > >> > abandon psql as a shell language and instead make a
   > >> > pl/pgsql interpreter with readlne() in front
   > >> > of it?   Solve all these language-related
   > >> > issues by using an actual programming language.  :-)
   > >
   > >> I though about it more time, but I don't thinking so this has a
   > >> sense.
   
   
   > > You might consider using "do".
   > 
   > it is reason, why I don't thinking about plpgsql on client side. But 
   > I
   > don't understand how it is related to gset ?
   
   Because the plpgsql SELECT INTO sets variables from query results,
   exactly what \gset does.  You have to use EXECUTE
   in plpgsql to do the substitution into statements, but that's
   syntax.
   
   > 
   > I remember, there is one significant limit of DO statement - it 
   > cannot
   > return table - so it cannot substitute psql simple scripts.
   
   Yes. I'm wrong.  For some reason I thought you could use DO to make
   an anonymous code block that would act as a SETOF function,
   allowing RETURN NEXT expr (et-al) to be used in the
   plpgsql code, allowing DO to return table results.
   (Or, perhaps, instead, be used in place of a table in a SELECT
   statement.)  Oh well.
   
   Regards,
   
   Karl <kop@meme.com>
   Free Software:  "You don't pay back, you pay forward."
                    -- Robert A. Heinlein
   
   
   
   

7. Re: gset updated patch

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-11-19T15:25:43Z

   2012/11/19 Karl O. Pinc <kop@meme.com>:
   > On 11/19/2012 02:30:49 AM, Pavel Stehule wrote:
   >> Hello
   >>
   >> 2012/11/16 Karl O. Pinc <kop@meme.com>:
   >> > Hi Pavel,
   >> >
   >> > On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
   >> >> 2012/11/16 Karl O. Pinc <kop@meme.com>:
   >> >
   >> >> > As long as I'm talking crazy talk, why not
   >> >> > abandon psql as a shell language and instead make a
   >> >> > pl/pgsql interpreter with readlne() in front
   >> >> > of it?   Solve all these language-related
   >> >> > issues by using an actual programming language.  :-)
   >> >
   >> >> I though about it more time, but I don't thinking so this has a
   >> >> sense.
   >
   >
   >> > You might consider using "do".
   >>
   >> it is reason, why I don't thinking about plpgsql on client side. But
   >> I
   >> don't understand how it is related to gset ?
   >
   > Because the plpgsql SELECT INTO sets variables from query results,
   > exactly what \gset does.  You have to use EXECUTE
   > in plpgsql to do the substitution into statements, but that's
   > syntax.
   
   yes, but I cannot set a psql variable
   
   but a original proposal for gset was \exec .... into var :)
   
   although \gset is more simply and there are no problem with multiline queries
   
   >
   >>
   >> I remember, there is one significant limit of DO statement - it
   >> cannot
   >> return table - so it cannot substitute psql simple scripts.
   >
   > Yes. I'm wrong.  For some reason I thought you could use DO to make
   > an anonymous code block that would act as a SETOF function,
   > allowing RETURN NEXT expr (et-al) to be used in the
   > plpgsql code, allowing DO to return table results.
   > (Or, perhaps, instead, be used in place of a table in a SELECT
   > statement.)  Oh well.
   
   yes, I understand - "batch" model for DO is more natural, than
   function - but it is not true :(.
   
   I hope, so one time we will have a real stored procedures with unbound
   queries. Then we can redefine DO behave, because it doesn't break
   compatibility. But it is long way - maybe 9.5
   
   Regards
   
   Pavel
   
   >
   > Regards,
   >
   > Karl <kop@meme.com>
   > Free Software:  "You don't pay back, you pay forward."
   >                  -- Robert A. Heinlein
   >
   
   
   

8. Re: gset updated patch

   Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-11-19T16:12:57Z

   "Karl O. Pinc" <kop@meme.com> writes:
   > Yes. I'm wrong.  For some reason I thought you could use DO to make
   > an anonymous code block that would act as a SETOF function,
   > allowing RETURN NEXT expr (et-al) to be used in the
   > plpgsql code, allowing DO to return table results.
   > (Or, perhaps, instead, be used in place of a table in a SELECT
   > statement.)  Oh well.
   
   My key for remembering about that point is that DO is a utility command,
   not a query. Now, the proposal I pushed last time we opened that very
   can of worms was to have inline functions rather than anonymous code
   blocks:
   
      WITH FUNCTION foo(integer) returns bigint language SQL AS $$
       SELECT $1 + 1;
      $$,
   
   Not sure how much that relates to $topic, but still something that
   raises in my mind with enough presence that I need to write about it so
   that it stops calling for attention :)
      
   Regards,
   -- 
   Dimitri Fontaine
   http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
   
   
   

9. Re: gset updated patch

   Piyush Newe <piyush.newe@enterprisedb.com> — 2012-11-27T09:49:23Z

   Just another thought !
   
   When we are setting up the variable using \gset, I feel their should be a
   provision
   to drop a particular variable.
   
   Internally, all the variables are set into "VariableSpace" linked-list. We
   should provide
   a command to Drop a particular variable, because in some cases unnecessary
   the
   variable count is increasing & consuming a VariableSpace.
   
   We might use two different variables for two different queries, but if we
   are not going
   to use the first variable in further execution, then unnecessary we are
   consuming a
   space for 1st variable in the "VariableSpace". In such cases, user will
   drop the 1st
   variable.
   
   This particular feature/mechanism is useful for a queries which returns a
   single row.
   So user will be using such technique for multiple queries. In such cases,
   user might
   need to create multiple variables. Hence I thoughts so.
   
   Let me know if such mechanism is already exists & I am missing the same.
   
   
   On Mon, Nov 19, 2012 at 9:42 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr>wrote:
   
   > "Karl O. Pinc" <kop@meme.com> writes:
   > > Yes. I'm wrong.  For some reason I thought you could use DO to make
   > > an anonymous code block that would act as a SETOF function,
   > > allowing RETURN NEXT expr (et-al) to be used in the
   > > plpgsql code, allowing DO to return table results.
   > > (Or, perhaps, instead, be used in place of a table in a SELECT
   > > statement.)  Oh well.
   >
   > My key for remembering about that point is that DO is a utility command,
   > not a query. Now, the proposal I pushed last time we opened that very
   > can of worms was to have inline functions rather than anonymous code
   > blocks:
   >
   >    WITH FUNCTION foo(integer) returns bigint language SQL AS $$
   >     SELECT $1 + 1;
   >    $$,
   >
   > Not sure how much that relates to $topic, but still something that
   > raises in my mind with enough presence that I need to write about it so
   > that it stops calling for attention :)
   >
   > Regards,
   > --
   > Dimitri Fontaine
   > http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
   >
   >
   > --
   > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   >
   
   
   
   -- 
   -- 
   Piyush S Newe
   Principal Engineer
   EnterpriseDB
   office: +91 20 3058 9500
   www.enterprisedb.com
   
   Website: www.enterprisedb.com
   EnterpriseDB Blog: http://blogs.enterprisedb.com/
   Follow us on Twitter: http://www.twitter.com/enterprisedb
   
   This e-mail message (and any attachment) is intended for the use of the
   individual or entity to whom it is addressed. This message contains
   information from EnterpriseDB Corporation that may be privileged,
   confidential, or exempt from disclosure under applicable law. If you are
   not the intended recipient or authorized to receive this for the intended
   recipient, any use, dissemination, distribution, retention, archiving, or
   copying of this communication is strictly prohibited. If you have received
   this e-mail in error, please notify the sender immediately by reply e-mail
   and delete this message.
   

10. Re: gset updated patch

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-11-27T09:59:07Z

    Hello
    
    2012/11/27 Piyush Newe <piyush.newe@enterprisedb.com>:
    >
    > Just another thought !
    >
    > When we are setting up the variable using \gset, I feel their should be a
    > provision
    > to drop a particular variable.
    >
    > Internally, all the variables are set into "VariableSpace" linked-list. We
    > should provide
    > a command to Drop a particular variable, because in some cases unnecessary
    > the
    > variable count is increasing & consuming a VariableSpace.
    >
    > We might use two different variables for two different queries, but if we
    > are not going
    > to use the first variable in further execution, then unnecessary we are
    > consuming a
    > space for 1st variable in the "VariableSpace". In such cases, user will drop
    > the 1st
    > variable.
    >
    > This particular feature/mechanism is useful for a queries which returns a
    > single row.
    > So user will be using such technique for multiple queries. In such cases,
    > user might
    > need to create multiple variables. Hence I thoughts so.
    >
    
    sorry, I don't understand
    
    now, when we set variable by empty value, then we remove variable
    
    ???
    
    regards
    
    Pavel
    
    > Let me know if such mechanism is already exists & I am missing the same.
    >
    >
    > On Mon, Nov 19, 2012 at 9:42 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr>
    > wrote:
    >>
    >> "Karl O. Pinc" <kop@meme.com> writes:
    >> > Yes. I'm wrong.  For some reason I thought you could use DO to make
    >> > an anonymous code block that would act as a SETOF function,
    >> > allowing RETURN NEXT expr (et-al) to be used in the
    >> > plpgsql code, allowing DO to return table results.
    >> > (Or, perhaps, instead, be used in place of a table in a SELECT
    >> > statement.)  Oh well.
    >>
    >> My key for remembering about that point is that DO is a utility command,
    >> not a query. Now, the proposal I pushed last time we opened that very
    >> can of worms was to have inline functions rather than anonymous code
    >> blocks:
    >>
    >>    WITH FUNCTION foo(integer) returns bigint language SQL AS $$
    >>     SELECT $1 + 1;
    >>    $$,
    >>
    >> Not sure how much that relates to $topic, but still something that
    >> raises in my mind with enough presence that I need to write about it so
    >> that it stops calling for attention :)
    >>
    >> Regards,
    >> --
    >> Dimitri Fontaine
    >> http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
    >>
    >>
    >> --
    >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    >> To make changes to your subscription:
    >> http://www.postgresql.org/mailpref/pgsql-hackers
    >
    >
    >
    >
    > --
    > --
    > Piyush S Newe
    > Principal Engineer
    > EnterpriseDB
    > office: +91 20 3058 9500
    > www.enterprisedb.com
    >
    > Website: www.enterprisedb.com
    > EnterpriseDB Blog: http://blogs.enterprisedb.com/
    > Follow us on Twitter: http://www.twitter.com/enterprisedb
    >
    > This e-mail message (and any attachment) is intended for the use of the
    > individual or entity to whom it is addressed. This message contains
    > information from EnterpriseDB Corporation that may be privileged,
    > confidential, or exempt from disclosure under applicable law. If you are not
    > the intended recipient or authorized to receive this for the intended
    > recipient, any use, dissemination, distribution, retention, archiving, or
    > copying of this communication is strictly prohibited. If you have received
    > this e-mail in error, please notify the sender immediately by reply e-mail
    > and delete this message.
    >