Thread: gset updated patch

Hello

here is a updated patch

Regards

Pavel

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

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
>

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

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
>

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

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
>

"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

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.
>