Обсуждение: psql feature thought
Hello,
I was dinking around wand came across something that may (or may not be
useful).
What if single line statements that were seperated by ; within psql were
implicitly within a transaction?
E.g;
postgres=# select * from foo; update foo set bar = 'baz'; delete from bing;
Would be a single transaction ? The begin/commit would be implicit.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> What if single line statements that were seperated by ; within psql were
> implicitly within a transaction?
Seems like this would risk breaking a lot of scripts.
regards, tom lane
""Joshua D. Drake"" <jd@commandprompt.com> wrote > > What if single line statements that were seperated by ; within psql were > implicitly within a transaction? > > E.g; > > postgres=# select * from foo; update foo set bar = 'baz'; delete from bing; > > Would be a single transaction ? The begin/commit would be implicit. > This is not in a single transaction currently. This is not a back compatible behavior. Also, I am not sure how difficult to implement it in current psql code. Think of this case: begin; select; update; delete; end; Line 2 is already in a transaction, so psql must be able to tell it (maybe we already be able to do so?). Not to speak of the SAVEPOINT control points. Regards, Qingqing
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What if single line statements that were seperated by ; within psql were
>> implicitly within a transaction?
>
> Seems like this would risk breaking a lot of scripts.
I wouldn't assume that it would be a default feature of course. Perhaps
something that can be passed from the command line or from a .psqlrc ?
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On May 16, 2006, at 13:42 , Joshua D. Drake wrote: > Tom Lane wrote: >> "Joshua D. Drake" <jd@commandprompt.com> writes: >>> What if single line statements that were seperated by ; within >>> psql were implicitly within a transaction? >> Seems like this would risk breaking a lot of scripts. > > I wouldn't assume that it would be a default feature of course. > Perhaps something that can be passed from the command line or from > a .psqlrc ? What use case are you envisioning? Just saving ... *counts ... 14 keystrokes in this case? (I'm not saying there *isn't* a use case. I just don't see a big benefit here.) Michael Glaesemann grzm seespotcode net
Michael Glaesemann <grzm@seespotcode.net> writes:
> What use case are you envisioning? Just saving ... *counts ... 14
> keystrokes in this case? (I'm not saying there *isn't* a use case. I
> just don't see a big benefit here.)
Quite aside from the compatibility and how-useful-is-it-really
arguments, I think this'd be a bad idea in the abstract. SQL is not one
of those languages that assigns semantic significance to the shape of
whitespace [1]. We should NOT introduce any such concept into psql,
because it'd fundamentally break the lexical structure of the language.
To take just one example of the difficulties you'd get into, consider
select 1; select 2; select 3;
How many transactions is that exactly? And on what grounds are you
deciding?
(No, I don't like python. Why do you ask? ;-))
regards, tom lane
[1] Mostly. There's the infamous continued-string-literal construct...
Tom Lane wrote:
> Michael Glaesemann <grzm@seespotcode.net> writes:
>> What use case are you envisioning? Just saving ... *counts ... 14
>> keystrokes in this case? (I'm not saying there *isn't* a use case. I
>> just don't see a big benefit here.)
>
> Quite aside from the compatibility and how-useful-is-it-really
> arguments, I think this'd be a bad idea in the abstract. SQL is not one
> of those languages that assigns semantic significance to the shape of
> whitespace [1]. We should NOT introduce any such concept into psql,
> because it'd fundamentally break the lexical structure of the language.
> To take just one example of the difficulties you'd get into, consider
>
> select 1; select
> 2; select 3;
>
> How many transactions is that exactly? And on what grounds are you
> deciding?
Well, my use case was that I was doing a quick batch process that had
about half a dozen statements in a row that were insert/update/delete.
If I had forgotten to type by begin; , I could have a problem with
consistency of various things if one in the middle failed.
I understand that this is completely a sugar feature and I am not
actually arguing for as much as I am saying, "Hey this was an
interesting thought".
>
> (No, I don't like python. Why do you ask? ;-))
Heh... I happen to like Python quite a bit:
But even in Python you can do:
select 1; select 2; select 3;
From an implementation perspective I would suggest that if the line has
a carriage return then it doesn't behave as a single transaction.. e.g;
BEGIN; SELECT 1; SELECT 2;
COMMIT;
Would be the same as:
SELECT 1; SELECT 2;
But not the same as:
SELECT 1;
SELECT 2;
Egad... forget I even mentioned it... These looks like a whole lot of
pgsql-novice posts having to be answerd.
Thanks for entertaining me though.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
> [1] Mostly. There's the infamous continued-string-literal construct...
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Tom Lane wrote: > Quite aside from the compatibility and how-useful-is-it-really > arguments, I think this'd be a bad idea in the abstract. SQL is not one > of those languages that assigns semantic significance to the shape of > whitespace [1]. We should NOT introduce any such concept into psql, > because it'd fundamentally break the lexical structure of the language. +1 Regards, Thomas Hallgren
On May 16, 2006, at 14:17 , Tom Lane wrote: > [1] Mostly. There's the infamous continued-string-literal > construct... Are you referring to this? est=# SELECT 'foo' 'bar'; -- invalid ERROR: syntax error at or near "'bar'" at character 17 LINE 1: SELECT 'foo' 'bar'; ^ test=# SELECT 'foo' test-# 'bar'; -- valid ?column? ---------- foobar (1 row) I've been exploiting that (mis)feature for line-wrapping comments after seeing it used that way somewhere. Very useful in that case, but very strange, I'll give you that. Michael Glaesemann grzm seespotcode net
Michael Glaesemann <grzm@seespotcode.net> writes:
> On May 16, 2006, at 14:17 , Tom Lane wrote:
>> [1] Mostly. There's the infamous continued-string-literal
>> construct...
> Are you referring to this?
> ^
> test=# SELECT 'foo'
> test-# 'bar'; -- valid
Yeah. It seems weird that a newline should be *required* there.
I think I understand why the SQL committee did that: they thoughtSELECT 'a' 'b'
was much too likely to be a syntax error and shouldn't be made into
a valid construct. But it's weird nonetheless, and awkward to
implement.
regards, tom lane
Joshua D. Drake wrote: > Hello, > > I was dinking around wand came across something that may (or may not be > useful). > > What if single line statements that were seperated by ; within psql were > implicitly within a transaction? > > E.g; > > postgres=# select * from foo; update foo set bar = 'baz'; delete from bing; > > Would be a single transaction ? The begin/commit would be implicit. > > Joshua D. Drake > > > In psql (and in bash, etc) I like to type multiple commands separated by ';' if I want them all to run but I don't want to wait for the first to complete before typing the second, third, etc. (Possibly I would rather go get a coffee while the commands are running.) So, I might type: psql# <LONG RUNNING INSERT STATEMENT>; <LONG RUNNING SELECT STATEMENT> and let it fly. It would violate the principle of least surprise if doing this on one line caused the semantics to change. mark