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(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Hello

2012/11/16 Karl O. Pinc <kop(at)meme(dot)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(at)meme(dot)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(at)meme(dot)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(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

Hello

2012/11/16 Karl O. Pinc <kop(at)meme(dot)com>:
> Hi Pavel,
>
> On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
>> 2012/11/16 Karl O. Pinc <kop(at)meme(dot)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(at)meme(dot)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(at)meme(dot)com>:
> > Hi Pavel,
> >
> > On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
> >> 2012/11/16 Karl O. Pinc <kop(at)meme(dot)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(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

2012/11/19 Karl O. Pinc <kop(at)meme(dot)com>:
> On 11/19/2012 02:30:49 AM, Pavel Stehule wrote:
>> Hello
>>
>> 2012/11/16 Karl O. Pinc <kop(at)meme(dot)com>:
>> > Hi Pavel,
>> >
>> > On 11/16/2012 12:21:11 AM, Pavel Stehule wrote:
>> >> 2012/11/16 Karl O. Pinc <kop(at)meme(dot)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(at)meme(dot)com>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>

"Karl O. Pinc" <kop(at)meme(dot)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

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(at)2ndquadrant(dot)fr>wrote:

> "Karl O. Pinc" <kop(at)meme(dot)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(at)postgresql(dot)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.

Hello

2012/11/27 Piyush Newe <piyush(dot)newe(at)enterprisedb(dot)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(at)2ndquadrant(dot)fr>
> wrote:
>>
>> "Karl O. Pinc" <kop(at)meme(dot)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(at)postgresql(dot)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.
>