Re: [HACKERS] psql metaqueries with \gexec
On Mon, Apr 4, 2016 at 3:31 PM, Tom Lane wrote: > Corey Huinker writes: > > Patch attached. Changes are thus: > > - rebased > > - pset.gexec_flag unconditionally set to false at end of SendQuery > > - wording of documentation describing execution order of results > > - rebasing allowed for undoing the re-wrap of enumerated slash commands. > > I whacked this around some and committed it. The main thing that was > Hooray! > broken is that it didn't work nicely at all if you'd set FETCH_COUNT. > Mmm, yeah, I hadn't considered cursor fetches, but in the use cases (at least the ones I can imagine for this) you wouldn't want fetches. > I experimented with different approaches to that, and ultimately decided > that the best answer is to disable use of ExecQueryUsingCursor for the > \gexec master query. We can still let it be used for the individual > generated queries, though. > Fine by me. > > I didn't much like the regression test setup, either. Tests that > have to be at the end of their test files aren't very nice, unless > you give them their very own test file, which checking ON_ERROR_STOP > didn't seem worth. To me it's far more important that the code > respond to cancel_pressed (which, ahem, it wasn't) and we have no > mechanism for testing that in a pg_regress script. So I just dropped > that aspect of it and put the test in a more logical place in the file. > I think it was Jim that added the ON_ERROR_STOP check. I wasn't sure how to properly test that. Thanks for finding (and fixing) the cancel_pressed issue.
Re: [HACKERS] psql metaqueries with \gexec
Corey Huinker writes: > Patch attached. Changes are thus: > - rebased > - pset.gexec_flag unconditionally set to false at end of SendQuery > - wording of documentation describing execution order of results > - rebasing allowed for undoing the re-wrap of enumerated slash commands. I whacked this around some and committed it. The main thing that was broken is that it didn't work nicely at all if you'd set FETCH_COUNT. I experimented with different approaches to that, and ultimately decided that the best answer is to disable use of ExecQueryUsingCursor for the \gexec master query. We can still let it be used for the individual generated queries, though. I didn't much like the regression test setup, either. Tests that have to be at the end of their test files aren't very nice, unless you give them their very own test file, which checking ON_ERROR_STOP didn't seem worth. To me it's far more important that the code respond to cancel_pressed (which, ahem, it wasn't) and we have no mechanism for testing that in a pg_regress script. So I just dropped that aspect of it and put the test in a more logical place in the file. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On Sun, Apr 3, 2016 at 8:42 PM, Corey Huinker wrote: > On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane wrote: > >> Corey Huinker writes: >> >>> + The secondary queries are executed in top-to-bottom, >> >>> left-to-right order, so the command >> >> >> I took that as meaning what I said above. >> >> > Would using the term https://en.wikipedia.org/wiki/Row-major_order be >> more >> > clear? >> >> Meh, I suspect a lot of people don't know that term. Perhaps something >> like "The generated queries are executed in the order in which the rows >> are returned, and left-to-right within each row if there is more than one >> column." >> >> regards, tom lane >> > > > I like it. Change forthcoming. > Changes since last submission: Patch attached. Changes are thus: - rebased - pset.gexec_flag unconditionally set to false at end of SendQuery - wording of documentation describing execution order of results - rebasing allowed for undoing the re-wrap of enumerated slash commands. Still not changed: - exuberant braces, can remove if someone wants me to diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index e8afc24..1fb4b55 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1767,6 +1767,92 @@ Tue Oct 26 21:40:57 CEST 1999 +\gexec + + + + Sends the current query input buffer to the server and treats + every column of every row of query output (if any) as a separate + SQL statement to be immediately executed. For example: + +=> SELECT 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' +-> UNION ALL +-> SELECT 'select true as is_true', 'select ''2000-01-01''::date as party_over' +-> \gexec +ones + + 1 +(1 row) + +y double +- -- +1 2 +2 4 +3 6 +4 8 +(4 rows) + +is_true +--- +t +(1 row) + +party_over +-- +01-01-2000 +(1 row) + + + +The generated queries are executed in the order in which the rows are returned, and +left-to-right within each row if there is more than one column. So, the command +above is the equivalent of: + +=> select 1 as ones; +=> select x.y, x.y*2 as double from generate_series(1,4) as x(y); +=> select true as is_true; +=> select '2000-01-01'::date as party_over; + + + +If the query returns no rows, no error is raised, but no secondary query +is executed, either. + +=%gt; SELECT 'select 1 as expect_zero_rows ' where false +-> \gexec + + + + +Results that are not valid SQL will of course fail, and the execution of further +secondary statements is subject to the current \ON_ERROR_STOP setting. + +=> SELECT 'a', 'select 1', 'b' +-> \gexec +ERROR: syntax error at or near "a" +LINE 1: a +^ +?column? + + 1 +(1 row) +ERROR: syntax error at or near "b" +LINE 1: b +^ +=> \set ON_ERROR_STOP 1 +=> SELECT 'a', 'select 1', 'b' +-> \gexec +ERROR: syntax error at or near "a" +LINE 1: a +^ + + +The results of the main query are sent directly to the server, without +evaluation by psql. Therefore, they cannot contain psql vars or \ commands. + + + + \gset [ prefix ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 3401b51..1baff8e 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -871,6 +871,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and treat every result cell as a query to be executed */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /* \gset [prefix] -- send query and store result into variables */ else if (strcmp(cmd, "gset") == 0) { diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index a2a07fb..0db5de2 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -796,6 +796,46 @@ StoreQueryTuple(const PGresult *result) return success; } +/* + * ExecQueryTuples: assuming query result is OK, execute every query + * result as its own statement + * + * Returns true if successful, false otherwise. + */ +static bool +ExecQueryTuples(const PGresult *result) +{ + boolsuccess = true; + int nrows = PQntuples(result); + int ncolumns = PQnfields(result); + int r, c; + + for (r = 0; r < nrows; r++) + { + for (c = 0; c < ncolumns; c++) + { + if (! PQgetisnull(result, r, c)) + { + if ( ! SendQuery(PQgetvalue(result, r, c)) ) + { + if (pset.on_error_st
Re: [HACKERS] psql metaqueries with \gexec
On Sun, Apr 3, 2016 at 7:43 PM, Tom Lane wrote: > Corey Huinker writes: > >>> + The secondary queries are executed in top-to-bottom, > >>> left-to-right order, so the command > > >> I took that as meaning what I said above. > > > Would using the term https://en.wikipedia.org/wiki/Row-major_order be > more > > clear? > > Meh, I suspect a lot of people don't know that term. Perhaps something > like "The generated queries are executed in the order in which the rows > are returned, and left-to-right within each row if there is more than one > column." > > regards, tom lane > I like it. Change forthcoming.
Re: [HACKERS] psql metaqueries with \gexec
Corey Huinker writes: >>> + The secondary queries are executed in top-to-bottom, >>> left-to-right order, so the command >> I took that as meaning what I said above. > Would using the term https://en.wikipedia.org/wiki/Row-major_order be more > clear? Meh, I suspect a lot of people don't know that term. Perhaps something like "The generated queries are executed in the order in which the rows are returned, and left-to-right within each row if there is more than one column." regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
> > + The secondary queries are executed in top-to-bottom, > left-to-right order, so the command > > I took that as meaning what I said above. > > Would using the term https://en.wikipedia.org/wiki/Row-major_order be more clear? The secondary queries are executed in row-major order, so the command... If so, it will probably aide in translation as well. > >> It should clear that in all the same > >> places where gfname or gset_prefix get cleared. > > > I'm only seeing one place where those two vars are deallocated and > nulled, > > and that's at the tail end of SendQuery. Were you expecting more than > just > > that? > > That may be the only place; I've not looked around. > Yeah, seems that there might have been multiple ones in the past, but all paths now funnel through the sendquery_cleanup: goto.
Re: [HACKERS] psql metaqueries with \gexec
Corey Huinker writes: > On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane wrote: >> I'm not Jim, but I have a question: what's the motivation for the >> Fortran-order traversal of the result (down rows before across columns)? > If I am understanding you correctly, it does work the way you find > intuitive: all results from the first row are executed before any in the > second row, so Oh, I hadn't checked the code closely enough to realize that, but I see you're right. The patch's documentation seems very confusing on the point, though: + The secondary queries are executed in top-to-bottom, left-to-right order, so the command I took that as meaning what I said above. >> It should clear that in all the same >> places where gfname or gset_prefix get cleared. > I'm only seeing one place where those two vars are deallocated and nulled, > and that's at the tail end of SendQuery. Were you expecting more than just > that? That may be the only place; I've not looked around. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On Sun, Apr 3, 2016 at 4:26 PM, Tom Lane wrote: > Robert Haas writes: > > Jim, can you re-review this? > > I'm not Jim, but I have a question: what's the motivation for the > Fortran-order traversal of the result (down rows before across columns)? > It seems less than intuitive to do it that way. Perhaps there's a good > reason, but I do not see any defense of this choice in the thread. > If I am understanding you correctly, it does work the way you find intuitive: all results from the first row are executed before any in the second row, so SELECT a, b UNION ALL SELECT c, d will execute the queries in order: a, b, c, d as is shown in the changes to the sgml and the test cases. Did you get the impression of Fortran-ordering from the phrase "top-to-bottom, left-to-right order" in the sgml patch? If so, would calling it "rows first" or something else be more clear? Or am I misunderstanding you and you find the order a, c, b, d more intuitive? I also note that the patch seems to be missing resetting gexec_flag > in some error exit paths, possibly allowing the \gexec to be applied > to the next query unexpectedly. It should clear that in all the same > places where gfname or gset_prefix get cleared. > Will do! I'm only seeing one place where those two vars are deallocated and nulled, and that's at the tail end of SendQuery. Were you expecting more than just that?
Re: [HACKERS] psql metaqueries with \gexec
Robert Haas writes: > Jim, can you re-review this? I'm not Jim, but I have a question: what's the motivation for the Fortran-order traversal of the result (down rows before across columns)? It seems less than intuitive to do it that way. Perhaps there's a good reason, but I do not see any defense of this choice in the thread. I also note that the patch seems to be missing resetting gexec_flag in some error exit paths, possibly allowing the \gexec to be applied to the next query unexpectedly. It should clear that in all the same places where gfname or gset_prefix get cleared. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On Mon, Mar 14, 2016 at 7:54 AM, Corey Huinker wrote: > Patch attached. Changes are thus: > - proper assignment of success var > - added documentation to psql manpage/html with examples pulled from > regression tests. > > Not changed are: > - exuberant braces, can remove if someone wants me to > - attempt at line-wrappng the enumerated slash commands, leave that to > pg_indent Jim, can you re-review this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
> > > I'm getting a warning from this patch: > > 1 warning generated. > Fixed that one. (note that I'm using CC='ccache clang -Qunused-arguments > -fcolor-diagnostics') > > for (r = 0; r < nrows; r++) >> { >> for (c = 0; c < ncolumns; c++) >> { >> > etc... > > Normally we don't use gratuitous {'s, and I don't think it's helping > anything in this case. But I'll let whoever commits this decide. > Good to know in the future. I can remove or leave to the committer. > diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c >> index 5f27120..0f87f29 100644 >> --- a/src/bin/psql/tab-complete.c >> +++ b/src/bin/psql/tab-complete.c >> @@ -1280,8 +1280,8 @@ psql_completion(const char *text, int start, int >> end) >> "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", >> "\\dS", >> "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", >> "\\e", "\\echo", "\\ef", "\\encoding", "\\ev", >> - "\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", >> "\\ir", "\\l", >> - "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", >> + "\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", >> "\\H", "\\i", "\\ir", >> + "\\l", "\\lo_import", "\\lo_export", "\\lo_list", >> "\\lo_unlink", >> > > FWIW, it's generally better to leave that kind of re-wrapping to the next > pg_indent run. > Good to know in the future. Not much point in undoing it now, I suppose. > > I added tests for ON_ERROR_STOP. New patch attached. > I was wondering if ON_ERROR_STOP tests were verbotten because you only get to kick the tires on one feature... > > The patch still needs to document this feature in the psql docs (and maybe > the manpage? not sure how that's generated...) doc/src/sgml/ref/psql-ref.sgml is the source for both html and man pagers. I'm on it. I didn't expect the name "gexec" to survive first contact with the community. Patch attached. Changes are thus: - proper assignment of success var - added documentation to psql manpage/html with examples pulled from regression tests. Not changed are: - exuberant braces, can remove if someone wants me to - attempt at line-wrappng the enumerated slash commands, leave that to pg_indent diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 8a85804..acb0eb7 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1753,6 +1753,91 @@ Tue Oct 26 21:40:57 CEST 1999 +\gexec + + + + Sends the current query input buffer to the server and treats + every column of every row of query output (if any) as a separate + SQL statement to be immediately executed. For example: + +=> SELECT 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' +-> UNION ALL +-> SELECT 'select true as is_true', 'select ''2000-01-01''::date as party_over' +-> \gexec +ones + + 1 +(1 row) + +y double +- -- +1 2 +2 4 +3 6 +4 8 +(4 rows) + +is_true +--- +t +(1 row) + +party_over +-- +01-01-2000 +(1 row) + + + +The secondary queries are executed in top-to-bottom, left-to-right order, so the command +above is the equivalent of: + +=> select 1 as ones; +=> select x.y, x.y*2 as double from generate_series(1,4) as x(y); +=> select true as is_true; +=> select '2000-01-01'::date as party_over; + + + +If the query returns no rows, no error is raised, but no secondary query +is executed, either. + +=%gt; SELECT 'select 1 as expect_zero_rows ' where false +-> \gexec + + + + +Results that are not valid SQL will of course fail, and the execution of further +secondary statements is subject to the current \ON_ERROR_STOP setting. + +=> SELECT 'a', 'select 1', 'b' +-> \gexec +ERROR: syntax error at or near "a" +LINE 1: a +^ +?column? + + 1 +(1 row) +ERROR: syntax error at or near "b" +LINE 1: b +^ +=> \set ON_ERROR_STOP 1 +=> SELECT 'a', 'select 1', 'b' +-> \gexec +ERROR: syntax error at or near "a" +LINE 1: a +^ + + +The results of the main query are sent directly to the server, without +evaluation by psql. Therefore, they cannot contain psql vars or \ commands. + + + + \gset [ prefix ] diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 9750a5b..5ca769f 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -849,6 +849,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and treat every result cell as a query to be executed */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /
Re: [HACKERS] psql metaqueries with \gexec
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:not tested Still needs documentation. The new status of this patch is: Waiting on Author -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On 2/22/16 1:01 PM, Corey Huinker wrote: In the mean time, update patch attached. Really attached this time. I'm getting a warning from this patch: common.c:947:8: warning: variable 'success' is used uninitialized whenever 'if' condition is true [-Wsometimes-uninitialized] if (pset.gexec_flag) ^~~ common.c:995:9: note: uninitialized use occurs here return success; ^~~ common.c:947:4: note: remove the 'if' if its condition is always false if (pset.gexec_flag) ^~~~ common.c:937:15: note: initialize the variable 'success' to silence this warning boolsuccess; ^ = '\0' 1 warning generated. (note that I'm using CC='ccache clang -Qunused-arguments -fcolor-diagnostics') for (r = 0; r < nrows; r++) { for (c = 0; c < ncolumns; c++) { etc... Normally we don't use gratuitous {'s, and I don't think it's helping anything in this case. But I'll let whoever commits this decide. diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5f27120..0f87f29 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1280,8 +1280,8 @@ psql_completion(const char *text, int start, int end) "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\e", "\\echo", "\\ef", "\\encoding", "\\ev", - "\\f", "\\g", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", "\\l", - "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", + "\\f", "\\g", "\\gexec", "\\gset", "\\h", "\\help", "\\H", "\\i", "\\ir", + "\\l", "\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink", FWIW, it's generally better to leave that kind of re-wrapping to the next pg_indent run. I added tests for ON_ERROR_STOP. New patch attached. The patch still needs to document this feature in the psql docs (and maybe the manpage? not sure how that's generated...) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 9750a5b..5ca769f 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -849,6 +849,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and treat every result cell as a query to be executed */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /* \gset [prefix] -- send query and store result into variables */ else if (strcmp(cmd, "gset") == 0) { diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 2cb2e9b..54b7790 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -710,6 +710,46 @@ StoreQueryTuple(const PGresult *result) return success; } +/* + * ExecQueryTuples: assuming query result is OK, execute every query + * result as its own statement + * + * Returns true if successful, false otherwise. + */ +static bool +ExecQueryTuples(const PGresult *result) +{ + boolsuccess = true; + int nrows = PQntuples(result); + int ncolumns = PQnfields(result); + int r, c; + + for (r = 0; r < nrows; r++) + { + for (c = 0; c < ncolumns; c++) + { + if (! PQgetisnull(result, r, c)) + { + if ( ! SendQuery(PQgetvalue(result, r, c)) ) + { + if (pset.on_error_stop) + { + return false; + } + else + { + success = false; + } + } + } + } + } + + /* Return true if all queries were successful */ + return success; +} + + /* * ProcessResult: utility function for use by SendQuery() only @@ -903,8 +943,14 @@ PrintQueryResults(PGresult *results) switch (PQresultStatus(results)) { case PGRES_TUPLES_OK: - /* store or print the data ... */ - if (pset.gset_prefix) + /* execute or store or print the data ... */ + if
Re: format() changes discussion (was: Re: [HACKERS] psql metaqueries with \gexec)
On 2/22/16 5:16 PM, Corey Huinker wrote: (One thing I had to come up with was processing of arrays, which you also see in that example JSON -- it's the specifiers that have a colon inside the {}. The part after the colon is used as separator between the array elements, and each element is expanded separately.) I'm splitting the subject line because it seems like two very different patches may come out of this. Oops. Just saw this. I don't think it'd make sense to make format() itself as capable as what Alvaro did for event triggers. Something that geared towards dealing with catalog objects should be it's own thing. What would be very useful IMHO is a version of format() that accepted hstore as it's second argument and then let you use names in the format specification. IE: format( 'blahblah name1%I blahblah literal_a%L blah "some string"%s' , 'name1 => name, literal_a => a, "some string" => string ) I suggest hstore over json because it's easier to work with by hand than json is, and there's no ambiguity with things like objects or arrays. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On 2/22/16 5:13 PM, Alvaro Herrera wrote: Jim Nasby wrote: On 2/22/16 11:47 AM, Alvaro Herrera wrote: Pavel Stehule wrote: The design of the "format" function is not closed. Try to send prototype and patch. The possibility to do PostgreSQL customization was strong reason why we didn't implemented "sprintf" and we implemented "format". Probably not terribly useful here, but for the DDL-deparse patch I came up with a syntax to format JSON objects, which used %-escapes; each escaped element corresponds to a string literal, or to an object. So you'd have %{table}D where the "table" element in the JSON object could be a simple string which is expanded verbatim (plus quoting if necessary), or it could be a JSON object with something like { schema => "public", name => "students" }, where each element is expanded and quoted as necessary; if the "schema" is null or it doesn't exist, it expands only the name, obviously omitting the dot. Where did the "D" in "%{table}D" come from? The I in %{foo}I was for "identifier" (of course) and I *think* the D was for "double identifiers" (that is, qualified). I expanded the idea afterwards to allow for a third name for things like catalog.schema.name, so I guess it's a misnomer already. It's not released code yet. You can see an example here https://www.postgresql.org/message-id/%3C20150224175152.GI5169%40alvh.no-ip.org%3E just scroll down a few hundred lines to about 7/16ths of the page (yes, really) (One thing I had to come up with was processing of arrays, which you also see in that example JSON -- it's the specifiers that have a colon inside the {}. The part after the colon is used as separator between the array elements, and each element is expanded separately.) Ahh, very interesting. Something that would probably be helpful for these kind of things is if we had a set of complex types available that represented things like the arguments to a function. Something like (parameter_mode enum(IN, OUT, INOUT), parameter_name name, parameter_type regtype, parameter_default text). A function might be represented by (function_schema name, function_name name, function_parameters ..., function_language, function_options, function_body). In any case, having anything along these lines in core would be useful, assuming that the individual facility was exposed as well (as opposed to only being available inside an event trigger). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
format() changes discussion (was: Re: [HACKERS] psql metaqueries with \gexec)
> > (One thing I had to come up with was processing of arrays, which you > also see in that example JSON -- it's the specifiers that have a colon > inside the {}. The part after the colon is used as separator between > the array elements, and each element is expanded separately.) > > I'm splitting the subject line because it seems like two very different patches may come out of this.
Re: [HACKERS] psql metaqueries with \gexec
Jim Nasby wrote: > On 2/22/16 11:47 AM, Alvaro Herrera wrote: > >Pavel Stehule wrote: > > > >>The design of the "format" function is not closed. Try to send prototype > >>and patch. The possibility to do PostgreSQL customization was strong reason > >>why we didn't implemented "sprintf" and we implemented "format". > > > >Probably not terribly useful here, but for the DDL-deparse patch I came > >up with a syntax to format JSON objects, which used %-escapes; each > >escaped element corresponds to a string literal, or to an object. So > >you'd have %{table}D where the "table" element in the JSON object could > >be a simple string which is expanded verbatim (plus quoting if > >necessary), or it could be a JSON object with something like { schema => > >"public", name => "students" }, where each element is expanded and > >quoted as necessary; if the "schema" is null or it doesn't exist, it > >expands only the name, obviously omitting the dot. > > Where did the "D" in "%{table}D" come from? The I in %{foo}I was for "identifier" (of course) and I *think* the D was for "double identifiers" (that is, qualified). I expanded the idea afterwards to allow for a third name for things like catalog.schema.name, so I guess it's a misnomer already. It's not released code yet. You can see an example here https://www.postgresql.org/message-id/%3C20150224175152.GI5169%40alvh.no-ip.org%3E just scroll down a few hundred lines to about 7/16ths of the page (yes, really) (One thing I had to come up with was processing of arrays, which you also see in that example JSON -- it's the specifiers that have a colon inside the {}. The part after the colon is used as separator between the array elements, and each element is expanded separately.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On 2/22/16 11:47 AM, Alvaro Herrera wrote: Pavel Stehule wrote: The design of the "format" function is not closed. Try to send prototype and patch. The possibility to do PostgreSQL customization was strong reason why we didn't implemented "sprintf" and we implemented "format". Probably not terribly useful here, but for the DDL-deparse patch I came up with a syntax to format JSON objects, which used %-escapes; each escaped element corresponds to a string literal, or to an object. So you'd have %{table}D where the "table" element in the JSON object could be a simple string which is expanded verbatim (plus quoting if necessary), or it could be a JSON object with something like { schema => "public", name => "students" }, where each element is expanded and quoted as necessary; if the "schema" is null or it doesn't exist, it expands only the name, obviously omitting the dot. Where did the "D" in "%{table}D" come from? BTW, the syntax I chose for [1] is similar to format's, except I elected to stick with % instead of $. So you do %parameter_name%type where type is s, L or I. I don't think it'd be hard to support an object with 'schema' and 'name' keys. [1] https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc#2-template-specification -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
> > In the mean time, update patch attached. > > Really attached this time. diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 9750a5b..5ca769f 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -849,6 +849,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and treat every result cell as a query to be executed */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /* \gset [prefix] -- send query and store result into variables */ else if (strcmp(cmd, "gset") == 0) { diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 2cb2e9b..54b7790 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -710,6 +710,46 @@ StoreQueryTuple(const PGresult *result) return success; } +/* + * ExecQueryTuples: assuming query result is OK, execute every query + * result as its own statement + * + * Returns true if successful, false otherwise. + */ +static bool +ExecQueryTuples(const PGresult *result) +{ + boolsuccess = true; + int nrows = PQntuples(result); + int ncolumns = PQnfields(result); + int r, c; + + for (r = 0; r < nrows; r++) + { + for (c = 0; c < ncolumns; c++) + { + if (! PQgetisnull(result, r, c)) + { + if ( ! SendQuery(PQgetvalue(result, r, c)) ) + { + if (pset.on_error_stop) + { + return false; + } + else + { + success = false; + } + } + } + } + } + + /* Return true if all queries were successful */ + return success; +} + + /* * ProcessResult: utility function for use by SendQuery() only @@ -903,8 +943,14 @@ PrintQueryResults(PGresult *results) switch (PQresultStatus(results)) { case PGRES_TUPLES_OK: - /* store or print the data ... */ - if (pset.gset_prefix) + /* execute or store or print the data ... */ + if (pset.gexec_flag) + { + /* Turn off gexec_flag to avoid infinite loop */ + pset.gexec_flag = false; + ExecQueryTuples(results); + } + else if (pset.gset_prefix) success = StoreQueryTuple(results); else success = PrintQueryTuples(results); diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 59f6f25..251dd1e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -173,6 +173,7 @@ slashUsage(unsigned short int pager) fprintf(output, _("General\n")); fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n")); fprintf(output, _(" \\g [FILE] or ; execute query (and send results to file or |pipe)\n")); + fprintf(output, _(" \\gexec execute query and treat every result cell as a query to be executed )\n")); fprintf(output, _(" \\gset [PREFIX] execute query and store results in psql variables\n")); fprintf(output, _(" \\q quit psql\n")); fprintf(output, _(" \\watch [SEC] execute query every SEC seconds\n")); diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h index 20a6470..9f1e94b 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -91,6 +91,9 @@ typedef struct _psqlSettings char *gfname; /* one-shot file output argument for \g */ char *gset_prefix;/* one-shot prefix argument for \gset */ + boolgexec_flag; /* true if query results are to be treated as +* queries to be executed. Set by \gexec */ + boolnotty; /* stdin or stdout is not a tty (as determined * on startup) */ enum trivalue getPassword; /* prompt the user for a username and password */ diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5f27120..0f87f29 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-com
Re: [HACKERS] psql metaqueries with \gexec
On Mon, Feb 22, 2016 at 11:30 AM, Corey Huinker wrote: > On Mon, Feb 22, 2016 at 10:08 AM, Daniel Verite > wrote: > >> Corey Huinker wrote: >> >> > ...and query text visibility, and result visibility, and error handling, >> > etc. In this case, we're leveraging the psql environment we'd already >> set >> > up, and if there's an error, \set ECHO queries shows us the errant SQL >> as >> > if we typed it ourselves.. >> >> BTW, about error handling, shouldn't it honor ON_ERROR_STOP ? >> >> With the patch when trying this: >> >> => set ON_ERROR_STOP on >> => select * from (values ('select 1/0', 'select 1/0')) AS n \gexec >> >> it produces two errors: >> ERROR: division by zero >> ERROR: division by zero >> >> I'd rather have the execution stop immediately after the first error, >> like it's the case with successive queries entered normally via the >> query buffer: >> >> => \set ON_ERROR_STOP on >> => select 1/0; select 1/0; >> ERROR: division by zero >> >> as opposed to: >> >> => \set ON_ERROR_STOP off >> => select 1/0; select 1/0; >> ERROR: division by zero >> ERROR: division by zero >> >> > Yes, I would like it to honor ON_ERROR_STOP. I'll look into that. > > Well, that was easy enough. Turns out that pset.on_error_stop is checked in MainLoop, whereas the other pset.on_* vars are checked in SendQuery(). My original idea had been to push each cell into a in-memory temp file handle and call MainLoop() on each. Pavel suggested that temp files of any sort were a bad idea, hence using SendQuery instead. It's probably for the best. # select 'select 1,2,3', 'select 1/0', 'select 4,5,6' ... # \gexec ?column? | ?column? | ?column? --+--+-- 1 |2 |3 (1 row) Time: 0.151 ms ERROR: 22012: division by zero LOCATION: int4div, int.c:719 Time: 0.528 ms ?column? | ?column? | ?column? --+--+-- 4 |5 |6 (1 row) Time: 0.139 ms Time: 0.595 ms # \set ON_ERROR_STOP 1 # select 'select 1,2,3', 'select 1/0', 'select 4,5,6' \gexec ?column? | ?column? | ?column? --+--+-- 1 |2 |3 (1 row) Time: 0.137 ms ERROR: 22012: division by zero LOCATION: int4div, int.c:719 Time: 0.165 ms Time: 0.284 ms Does \set ON_ERROR_STOP mess up regression tests? If not, I'll add the test above (minus the \set VERBOSITY verbose-isms) to the regression. In the mean time, update patch attached.
Re: [HACKERS] psql metaqueries with \gexec
Pavel Stehule wrote: > The design of the "format" function is not closed. Try to send prototype > and patch. The possibility to do PostgreSQL customization was strong reason > why we didn't implemented "sprintf" and we implemented "format". Probably not terribly useful here, but for the DDL-deparse patch I came up with a syntax to format JSON objects, which used %-escapes; each escaped element corresponds to a string literal, or to an object. So you'd have %{table}D where the "table" element in the JSON object could be a simple string which is expanded verbatim (plus quoting if necessary), or it could be a JSON object with something like { schema => "public", name => "students" }, where each element is expanded and quoted as necessary; if the "schema" is null or it doesn't exist, it expands only the name, obviously omitting the dot. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
On Mon, Feb 22, 2016 at 10:08 AM, Daniel Verite wrote: > Corey Huinker wrote: > > > ...and query text visibility, and result visibility, and error handling, > > etc. In this case, we're leveraging the psql environment we'd already set > > up, and if there's an error, \set ECHO queries shows us the errant SQL as > > if we typed it ourselves.. > > BTW, about error handling, shouldn't it honor ON_ERROR_STOP ? > > With the patch when trying this: > > => set ON_ERROR_STOP on > => select * from (values ('select 1/0', 'select 1/0')) AS n \gexec > > it produces two errors: > ERROR: division by zero > ERROR: division by zero > > I'd rather have the execution stop immediately after the first error, > like it's the case with successive queries entered normally via the > query buffer: > > => \set ON_ERROR_STOP on > => select 1/0; select 1/0; > ERROR: division by zero > > as opposed to: > > => \set ON_ERROR_STOP off > => select 1/0; select 1/0; > ERROR: division by zero > ERROR: division by zero > > Yes, I would like it to honor ON_ERROR_STOP. I'll look into that.
Re: [HACKERS] psql metaqueries with \gexec
Corey Huinker wrote: > ...and query text visibility, and result visibility, and error handling, > etc. In this case, we're leveraging the psql environment we'd already set > up, and if there's an error, \set ECHO queries shows us the errant SQL as > if we typed it ourselves.. BTW, about error handling, shouldn't it honor ON_ERROR_STOP ? With the patch when trying this: => set ON_ERROR_STOP on => select * from (values ('select 1/0', 'select 1/0')) AS n \gexec it produces two errors: ERROR: division by zero ERROR: division by zero I'd rather have the execution stop immediately after the first error, like it's the case with successive queries entered normally via the query buffer: => \set ON_ERROR_STOP on => select 1/0; select 1/0; ERROR: division by zero as opposed to: => \set ON_ERROR_STOP off => select 1/0; select 1/0; ERROR: division by zero ERROR: division by zero Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql metaqueries with \gexec
> > FWIW, I also wish we had something better than format() for this stuff. I > did create [1] towards that end, but it currently depends on some C code, > which is cumbersome. For the most party, I'm pretty thrilled with format(), though: - I'll admit to being grumpy about the %1$s notation, but I have no better suggestion. - I'd also like it if there were a %I variant that accepted schema qualified names and %I-ed both, though I see the inability to tell the difference between a schema dot and a really-named-that dot. - I'd love it if there were a %C format that took a pg_class oid and formatted the qualified schema name. As it is i just use %s and cast the parameter as ::regclass The design of the "format" function is not closed. Try to send prototype and patch. The possibility to do PostgreSQL customization was strong reason why we didn't implemented "sprintf" and we implemented "format". Regards Pavel
Re: [HACKERS] psql metaqueries with \gexec
> > I like what you've proposed, though I am wondering if you considered doing > something server-side instead? It seems a shame to do all this work and > exclude all other tools. > I have, but my solutions closely mirror the one you mention in the next paragraph. > I frequently find myself creating a function that is just a wrapper on > EXECUTE for this purpose, but obviously that has transactional limitations. > ...and query text visibility, and result visibility, and error handling, etc. In this case, we're leveraging the psql environment we'd already set up, and if there's an error, \set ECHO queries shows us the errant SQL as if we typed it ourselves.. > > FWIW, I also wish we had something better than format() for this stuff. I > did create [1] towards that end, but it currently depends on some C code, > which is cumbersome. For the most party, I'm pretty thrilled with format(), though: - I'll admit to being grumpy about the %1$s notation, but I have no better suggestion. - I'd also like it if there were a %I variant that accepted schema qualified names and %I-ed both, though I see the inability to tell the difference between a schema dot and a really-named-that dot. - I'd love it if there were a %C format that took a pg_class oid and formatted the qualified schema name. As it is i just use %s and cast the parameter as ::regclass > > [1] > https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc That's intense. I'll ask you about that in an off-list thread.
Re: [HACKERS] psql metaqueries with \gexec
On 2/19/16 7:32 PM, Corey Huinker wrote: Wow, and I thought *I* liked metaprogramming! :) I like what you've proposed, though I am wondering if you considered doing something server-side instead? It seems a shame to do all this work and exclude all other tools. I frequently find myself creating a function that is just a wrapper on EXECUTE for this purpose, but obviously that has transactional limitations. FWIW, I also wish we had something better than format() for this stuff. I did create [1] towards that end, but it currently depends on some C code, which is cumbersome. I am not sure that "gexec" is the right name for this command. Others considered were \execute_each, \meta, \gmeta, \geach, as well as adding a "<" parameter to the \g command. \gexec sounds fine to me. I would think \g < would be something done at the shell level... [1] https://github.com/decibel/trunklet-format/blob/master/doc/trunklet-format.asc -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql metaqueries with \gexec
Often, I'm faced with a long .sql script that builds some objects, then builds things on top of them. This means that some of the queries I wish to run are dependent on the state of things that are unknown at the time of writing the script. I could give up, and make a python script that mostly just strings together SQL statements. That's ugly and cumbersome. I could do some wizardry like this: $ create table foo( a integer, b text, c date); $ select coalesce( ( select string_agg(format('create index foo(%I);',attname),E'\n') from pg_attribute where attrelid = 'foo'::regclass and attnum > 0 order by attnum), '') as sql_statements \gset :sql_statements For those of you not willing to parse that, that's a dictionary query with a 1-column result set formatted into sql with a ';' appended, string aggregated with a newline delimiter, with the final result set coalesced with an empty string because \gset will error on an empty result set. I then immediately put that psql variable back into the command buffer, where I hope that I meta-wrote valid SQL. If it hurt to read, you can imagine what it was like to write. I could use \g and pipe the results to another psql session...but that will happen in another transaction where my objects might not exist yet. I would also like the log to show what commands were run. For that reason, I created the psql command \gexec It is like \g and \gset in the sense that it executes the query currently in the buffer. However, it treats every cell in the result set as a query which itself should be immediately executed. $ create temporary table gexec_temp( a int, b text, c date, d float); CREATE TABLE $ select format('create index on gexec_temp(%I)',attname) from pg_attribute where attrelid = 'gexec_temp'::regclass and attnum > 0 order by attnum \gexec create index on gexec_temp(a) CREATE INDEX create index on gexec_temp(b) CREATE INDEX create index on gexec_temp(c) CREATE INDEX create index on gexec_temp(d) CREATE INDEX Execution order of the statements is top to bottom, left to right. $ select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' union all select 'select true as is_true', 'select ''2000-01-01''::date as party_over' \gexec ones 1 (1 row) y double - -- 1 2 2 4 3 6 4 8 (4 rows) is_true --- t (1 row) party_over -- 01-01-2000 (1 row) Empty result sets do nothing: $ select 'select 1 as expect_zero_rows ' where false \gexec The results are just strings which are sent to SendQuery(), where they succeed or fail on their own merits $ select 'do $$ begin raise notice ''plpgsql block executed''; end;$$' as block from generate_series(1,2) \gexec do $$ begin raise notice 'plpgsql block executed'; end;$$ NOTICE: plpgsql block executed DO do $$ begin raise notice 'plpgsql block executed'; end;$$ NOTICE: plpgsql block executed DO I am not sure that "gexec" is the right name for this command. Others considered were \execute_each, \meta, \gmeta, \geach, as well as adding a "<" parameter to the \g command. Many thanks to Pavel Stěhule for giving me some direction in this endeavor, though he might not agree with the design. diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 9750a5b..5ca769f 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -849,6 +849,13 @@ exec_command(const char *cmd, status = PSQL_CMD_SEND; } + /* \gexec -- send query and treat every result cell as a query to be executed */ + else if (strcmp(cmd, "gexec") == 0) + { + pset.gexec_flag = true; + status = PSQL_CMD_SEND; + } + /* \gset [prefix] -- send query and store result into variables */ else if (strcmp(cmd, "gset") == 0) { diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 2cb2e9b..35bbeb9 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -710,6 +710,39 @@ StoreQueryTuple(const PGresult *result) return success; } +/* + * ExecQueryTuples: assuming query result is OK, execute every query + * result as its own statement + * + * Returns true if successful, false otherwise. + */ +static bool +ExecQueryTuples(const PGresult *result) +{ + boolsuccess = true; + int nrows = PQntuples(result); + int ncolumns = PQnfields(result); + int r, c; + + for (r = 0; r < nrows; r++) + { + for (c = 0; c < ncolumns; c++) + { + if (! PQgetisnull(result, r, c)) + { + if ( ! SendQuery(PQgetvalue(result, r, c)) ) + { + success = false; + } + } + } +