Thread
-
Add YAML option to explain
Greg Sabino Mullane <greg@turnstep.com> — 2009-08-28T18:16:12Z
Attached patch adds YAML output option to explain: explain (format YAML) select * from information_schema.columns; -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200908281414 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-
Re: Add YAML option to explain
Andrew Dunstan <andrew@dunslane.net> — 2009-08-28T19:32:55Z
Greg Sabino Mullane wrote: > Attached patch adds YAML output option to explain: > > I thought the consensus was that we didn't want to get into supporting more formats. What does YAML provide that JSON does not? cheers andrew
-
Re: Add YAML option to explain
Greg Sabino Mullane <greg@turnstep.com> — 2009-08-28T20:13:30Z
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I thought the consensus was that we didn't want to get into supporting > more formats. What does YAML provide that JSON does not? Readability and easy editing. All the power of JSON without the annoying quotes, braces, and brackets. By the way, Magnus pointed out an error in the patch: the hunk at - -1693,7 +1736,6 should be ignored. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200908281552 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqYOmUACgkQvJuQZxSWSsjMogCfVV8hj0QkRQFmOq2nkUV+1bqM QDcAoKAlc6lpuV9jbeIL8ms9HIfhLF2W =nb+s -----END PGP SIGNATURE-----
-
Re: Add YAML option to explain
Florian Weimer <fw@deneb.enyo.de> — 2009-08-28T20:32:56Z
* Greg Sabino Mullane: >> I thought the consensus was that we didn't want to get into supporting >> more formats. What does YAML provide that JSON does not? > > Readability and easy editing. All the power of JSON without the > annoying quotes, braces, and brackets. But YAML is much more difficult to parse than JSON. Anybody who can afford a YAML parser can also afford a JSON parser, it is miniscule in comparison. 8-)
-
Re: Add YAML option to explain
Josh Berkus <josh@agliodbs.com> — 2009-08-28T21:23:43Z
On 8/28/09 1:13 PM, Greg Sabino Mullane wrote: > >> I thought the consensus was that we didn't want to get into supporting >> more formats. What does YAML provide that JSON does not? > > Readability and easy editing. All the power of JSON without the > annoying quotes, braces, and brackets. How many lines of code does YAML support add to the codebase? While I personally like YAML, it's not like it has broad industry support. And people wouldn't interface with the XML or JSON directly; they'd use a library for that. That's the whole point of having those outputs. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
-
Re: Add YAML option to explain
Joshua D. Drake <jd@commandprompt.com> — 2009-08-28T21:26:48Z
On Fri, 2009-08-28 at 14:23 -0700, Josh Berkus wrote: > On 8/28/09 1:13 PM, Greg Sabino Mullane wrote: > > > >> I thought the consensus was that we didn't want to get into supporting > >> more formats. What does YAML provide that JSON does not? > > > > Readability and easy editing. All the power of JSON without the > > annoying quotes, braces, and brackets. > > How many lines of code does YAML support add to the codebase? > > While I personally like YAML, it's not like it has broad industry > support. And people wouldn't interface with the XML or JSON directly; > they'd use a library for that. That's the whole point of having those > outputs. I am not keen on having YAML support. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering
-
Re: Add YAML option to explain
Peter Eisentraut <peter_e@gmx.net> — 2009-08-28T21:34:17Z
On fre, 2009-08-28 at 20:13 +0000, Greg Sabino Mullane wrote: > Readability and easy editing. All the power of JSON without the > annoying quotes, braces, and brackets. But these are supposed to be machine-readable formats. So readability and editability are not high priority criteria.
-
Re: Add YAML option to explain
Greg Sabino Mullane <greg@turnstep.com> — 2009-08-28T21:42:50Z
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > How many lines of code does YAML support add to the codebase? About 80. > While I personally like YAML, it's not like it has broad industry > support. And people wouldn't interface with the XML or JSON directly; > they'd use a library for that. That's the whole point of having those > outputs. Not sure how one measures "broad industry support" or why we would care that much. Nor do I wish to turn this thread into a YAML flamewar. It's a small patch that should be useful to the many people that prefer to program using YAML. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200908281741 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqYT0QACgkQvJuQZxSWSshPMACg+ho+gTxeKR27pOJrbX7kTJ9E kZIAnRIz57KzuLAAr6PtjupBwletuXRe =ZJO7 -----END PGP SIGNATURE-----
-
Re: Add YAML option to explain
Stephen Frost <sfrost@snowman.net> — 2009-08-28T22:45:13Z
* Greg Sabino Mullane (greg@turnstep.com) wrote: > Attached patch adds YAML output option to explain: > > explain (format YAML) select * from information_schema.columns; +1 from me. I've read the other comments and just plain don't agree with them. It's a small patch, adds a useful format for EXPLAIN, and would be used. One of the best things about PG is the flexibility and usability. Thanks, Stephen
-
Re: Add YAML option to explain
David Wheeler <david@kineticode.com> — 2009-08-28T23:37:41Z
On Aug 28, 2009, at 3:45 PM, Stephen Frost wrote: > +1 from me. I've read the other comments and just plain don't agree > with them. It's a small patch, adds a useful format for EXPLAIN, and > would be used. > > One of the best things about PG is the flexibility and usability. I agree, I tend to prefer YAML output where it's parseable (and I expect it the EXPLAIN YAML output won't be doing anything tricky). That said, maybe there should be a way to create modules add formats, instead of adding them to core? Best, David
-
Re: Add YAML option to explain
Ron Mayer <rm_pg@cheapcomplexdevices.com> — 2009-08-29T00:03:00Z
Peter Eisentraut wrote: > On fre, 2009-08-28 at 20:13 +0000, Greg Sabino Mullane wrote: >> Readability and easy editing. All the power of JSON without the >> annoying quotes, braces, and brackets. > > But these are supposed to be machine-readable formats. So readability > and editability are not high priority criteria. > Greg, can we see a few examples of the YAML output compared to both json and text? IMVHO, an advantage of YAML is human readability of structured data even compared to most non-computer-parseable human-intended text formats. But maybe that's just because I read too much yaml.
-
Re: Add YAML option to explain
daveg <daveg@sonic.net> — 2009-08-29T04:21:43Z
On Fri, Aug 28, 2009 at 04:37:41PM -0700, David E. Wheeler wrote: > On Aug 28, 2009, at 3:45 PM, Stephen Frost wrote: > > >+1 from me. I've read the other comments and just plain don't agree > >with them. It's a small patch, adds a useful format for EXPLAIN, and > >would be used. > > > >One of the best things about PG is the flexibility and usability. > > I agree, I tend to prefer YAML output where it's parseable (and I > expect it the EXPLAIN YAML output won't be doing anything tricky). > > That said, maybe there should be a way to create modules add formats, > instead of adding them to core? +1 -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
-
Re: Add YAML option to explain
Greg Sabino Mullane <greg@turnstep.com> — 2009-08-31T13:43:13Z
On 08/28/2009 02:16 PM, Greg Sabino Mullane wrote: > Attached patch adds YAML output option to explain: > > explain (format YAML) select * from information_schema.columns; Updated version of the patch attached, fixes two small errors. -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200908310847 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-
Re: Add YAML option to explain
Greg Sabino Mullane <greg@turnstep.com> — 2009-08-31T14:15:08Z
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Greg, can we see a few examples of the YAML output > compared to both json and text? Sure. Be warned it will make this email long. Because email may wrap things funny, I'll post the same thing here: Query 1: http://pgsql.privatepaste.com/298pqiSwdH Note that YAML quotes things like JSON does, but only when the quotes are needed. Query 2: http://pgsql.privatepaste.com/610uDDyMu6 greg=# explain (format text, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3; QUERY PLAN - -------------------------------------------------------------------------------------------------------------- Sort (cost=12.82..13.10 rows=111 width=185) (actual time=1.176..1.401 rows=105 loops=1) Sort Key: relname, relnamespace, reltype Sort Method: quicksort Memory: 44kB -> Seq Scan on pg_class (cost=0.00..9.05 rows=111 width=185) (actual time=0.066..0.828 rows=105 loops=1) Filter: (relname ~ 'x'::text) Total runtime: 1.676 ms greg=# explain (format json, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3; QUERY PLAN - ----------------------------------------------------------- [ { "Plan": { "Node Type": "Sort", "Startup Cost": 12.82, "Total Cost": 13.10, "Plan Rows": 111, "Plan Width": 185, "Actual Startup Time": 1.152, "Actual Total Time": 1.373, "Actual Rows": 105, "Actual Loops": 1, "Sort Key": ["relname", "relnamespace", "reltype"], "Sort Method": "quicksort", "Sort Space Used": 44, "Sort Space Type": "Memory", "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Relation Name": "pg_class", "Alias": "pg_class", "Startup Cost": 0.00, "Total Cost": 9.05, "Plan Rows": 111, "Plan Width": 185, "Actual Startup Time": 0.067, "Actual Total Time": 0.817, "Actual Rows": 105, "Actual Loops": 1, "Filter": "(relname ~ 'x'::text)" } ] }, "Triggers": [ ], "Total Runtime": 1.649 } ] greg=# explain (format yaml, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3; QUERY PLAN - --------------------------------------- - Plan: Node Type: Sort Startup Cost: 12.82 Total Cost: 13.10 Plan Rows: 111 Plan Width: 185 Actual Startup Time: 1.159 Actual Total Time: 1.391 Actual Rows: 105 Actual Loops: 1 Sort Key: - relname - relnamespace - reltype Sort Method: quicksort Sort Space Used: 44 Sort Space Type: Memory Plans: - Node Type: Seq Scan Parent Relationship: Outer Relation Name: pg_class Alias: pg_class Startup Cost: 0.00 Total Cost: 9.05 Plan Rows: 111 Plan Width: 185 Actual Startup Time: 0.067 Actual Total Time: 0.829 Actual Rows: 105 Actual Loops: 1 Filter: (relname ~ 'x'::text) Triggers: Total Runtime: 1.671 greg=# explain (format xml, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3; QUERY PLAN - ------------------------------------------------------------ <explain xmlns="http://www.postgresql.org/2009/explain"> <Query> <Plan> <Node-Type>Sort</Node-Type> <Startup-Cost>12.82</Startup-Cost> <Total-Cost>13.10</Total-Cost> <Plan-Rows>111</Plan-Rows> <Plan-Width>185</Plan-Width> <Actual-Startup-Time>1.154</Actual-Startup-Time> <Actual-Total-Time>1.382</Actual-Total-Time> <Actual-Rows>105</Actual-Rows> <Actual-Loops>1</Actual-Loops> <Sort-Key> <Item>relname</Item> <Item>relnamespace</Item> <Item>reltype</Item> </Sort-Key> <Sort-Method>quicksort</Sort-Method> <Sort-Space-Used>44</Sort-Space-Used> <Sort-Space-Type>Memory</Sort-Space-Type> <Plans> <Plan> <Node-Type>Seq Scan</Node-Type> <Parent-Relationship>Outer</Parent-Relationship> <Relation-Name>pg_class</Relation-Name> <Alias>pg_class</Alias> <Startup-Cost>0.00</Startup-Cost> <Total-Cost>9.05</Total-Cost> <Plan-Rows>111</Plan-Rows> <Plan-Width>185</Plan-Width> <Actual-Startup-Time>0.066</Actual-Startup-Time> <Actual-Total-Time>0.837</Actual-Total-Time> <Actual-Rows>105</Actual-Rows> <Actual-Loops>1</Actual-Loops> <Filter>(relname ~ 'x'::text)</Filter> </Plan> </Plans> </Plan> <Triggers> </Triggers> <Total-Runtime>1.655</Total-Runtime> </Query> </explain> An example with embedded quotes: greg=# explain (format text, analyze on) select 1 from pg_class where relname = 'foo"bar"'; QUERY PLAN - ---------------------------------------------------------------------------------------------------------------------------- Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=1 width=0) (actual time=0.018..0.018 rows=0 loops=1) Index Cond: (relname = 'foo"bar"'::name) Total runtime: 0.056 ms greg=# explain (format json, analyze on) select 1 from pg_class where relname = 'foo"bar"'; QUERY PLAN - ------------------------------------------------------ [ { "Plan": { "Node Type": "Index Scan", "Scan Direction": "Forward", "Index Name": "pg_class_relname_nsp_index", "Relation Name": "pg_class", "Alias": "pg_class", "Startup Cost": 0.00, "Total Cost": 8.27, "Plan Rows": 1, "Plan Width": 0, "Actual Startup Time": 0.015, "Actual Total Time": 0.015, "Actual Rows": 0, "Actual Loops": 1, "Index Cond": "(relname = 'foo\"bar\"'::name)" }, "Triggers": [ ], "Total Runtime": 0.046 } ] greg=# explain (format yaml, analyze on) select 1 from pg_class where relname = 'foo"bar"'; QUERY PLAN - -------------------------------------------------- - Plan: Node Type: Index Scan Scan Direction: Forward Index Name: pg_class_relname_nsp_index Relation Name: pg_class Alias: pg_class Startup Cost: 0.00 Total Cost: 8.27 Plan Rows: 1 Plan Width: 0 Actual Startup Time: 0.019 Actual Total Time: 0.019 Actual Rows: 0 Actual Loops: 1 Index Cond: "(relname = 'foo\"bar\"'::name)" Triggers: Total Runtime: 0.058 greg=# explain (format xml, analyze on) select 1 from pg_class where relname = 'foo"bar"'; QUERY PLAN - ------------------------------------------------------------- <explain xmlns="http://www.postgresql.org/2009/explain"> <Query> <Plan> <Node-Type>Index Scan</Node-Type> <Scan-Direction>Forward</Scan-Direction> <Index-Name>pg_class_relname_nsp_index</Index-Name> <Relation-Name>pg_class</Relation-Name> <Alias>pg_class</Alias> <Startup-Cost>0.00</Startup-Cost> <Total-Cost>8.27</Total-Cost> <Plan-Rows>1</Plan-Rows> <Plan-Width>0</Plan-Width> <Actual-Startup-Time>0.013</Actual-Startup-Time> <Actual-Total-Time>0.013</Actual-Total-Time> <Actual-Rows>0</Actual-Rows> <Actual-Loops>1</Actual-Loops> <Index-Cond>(relname = 'foo"bar"'::name)</Index-Cond> </Plan> <Triggers> </Triggers> <Total-Runtime>0.049</Total-Runtime> </Query> </explain> - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200908311000 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqb2r0ACgkQvJuQZxSWSshbEACgzAIXc6dNM/+dDmE8Xvjyg147 SrsAniMfB5RBhnq9EWY95+fiDSkLCRPy =G8Al -----END PGP SIGNATURE----- -
Re: Add YAML option to explain
Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-08-31T16:57:16Z
"Greg Sabino Mullane" <greg@turnstep.com> wrote: > - > Plan: > Node Type: Index Scan > Scan Direction: Forward > Index Name: pg_class_relname_nsp_index > Relation Name: pg_class > Alias: pg_class > Startup Cost: 0.00 > Total Cost: 8.27 > Plan Rows: 1 > Plan Width: 0 > Actual Startup Time: 0.019 > Actual Total Time: 0.019 > Actual Rows: 0 > Actual Loops: 1 > Index Cond: "(relname = 'foo\"bar\"'::name)" > Triggers: > Total Runtime: 0.058 +1 for including this format. On a ten point scale for human readability, I'd give this about a nine. It's something I'd be comfortable generating in order to annotate and include in an email to programmers or managers who wouldn't have a clue how to read the current text version of a plan. -Kevin
-
Re: Add YAML option to explain
daveg <daveg@sonic.net> — 2009-08-31T20:06:29Z
On Mon, Aug 31, 2009 at 02:15:08PM -0000, Greg Sabino Mullane wrote: > > Greg, can we see a few examples of the YAML output > > compared to both json and text? ... > greg=# explain (format json, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3; > QUERY PLAN > - ----------------------------------------------------------- An interesting property of json, it is almost exactly the same as python data structure syntax. If I paste the following into python: plan = [ { "Plan": { "Node Type": "Sort", "Startup Cost": 12.82, "Total Cost": 13.10, "Plan Rows": 111, "Plan Width": 185, "Actual Startup Time": 1.152, "Actual Total Time": 1.373, "Actual Rows": 105, "Actual Loops": 1, "Sort Key": ["relname", "relnamespace", "reltype"], "Sort Method": "quicksort", "Sort Space Used": 44, "Sort Space Type": "Memory", "Plans": [ { "Node Type": "Seq Scan", "Parent Relationship": "Outer", "Relation Name": "pg_class", "Alias": "pg_class", "Startup Cost": 0.00, "Total Cost": 9.05, "Plan Rows": 111, "Plan Width": 185, "Actual Startup Time": 0.067, "Actual Total Time": 0.817, "Actual Rows": 105, "Actual Loops": 1, "Filter": "(relname ~ 'x'::text)" } ] }, "Triggers": [ ], "Total Runtime": 1.649 } ] I get a python data structure. Which can be manipulated directly, or pretty printed: >>> import pprint >>> pprint.pprint(plan) [{'Plan': {'Actual Loops': 1, 'Actual Rows': 105, 'Actual Startup Time': 1.1519999999999999, 'Actual Total Time': 1.373, 'Node Type': 'Sort', 'Plan Rows': 111, 'Plan Width': 185, 'Plans': [{'Actual Loops': 1, 'Actual Rows': 105, 'Actual Startup Time': 0.067000000000000004, 'Actual Total Time': 0.81699999999999995, 'Alias': 'pg_class', 'Filter': "(relname ~ 'x'::text)", 'Node Type': 'Seq Scan', 'Parent Relationship': 'Outer', 'Plan Rows': 111, 'Plan Width': 185, 'Relation Name': 'pg_class', 'Startup Cost': 0.0, 'Total Cost': 9.0500000000000007}], 'Sort Key': ['relname', 'relnamespace', 'reltype'], 'Sort Method': 'quicksort', 'Sort Space Type': 'Memory', 'Sort Space Used': 44, 'Startup Cost': 12.82, 'Total Cost': 13.1}, 'Total Runtime': 1.649, 'Triggers': []}] I'm not sure if all json can be read this way, but the python and json notations are very similar. -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects. -
Re: Add YAML option to explain
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-11-16T03:15:48Z
Greg Sabino Mullane <greg@turnstep.com> wrote: > On 08/28/2009 02:16 PM, Greg Sabino Mullane wrote: > > Attached patch adds YAML output option to explain: > > explain (format YAML) select * from information_schema.columns; > > Updated version of the patch attached, fixes two small errors. I've reviewed your patch. I had a trouble on assertion failure. Maybe es->indent-- in ExplainEndOutput() is wrong. TRAP: FailedAssertion("!(es.indent == 0)", File: "explain.c", Line: 198) LOG: server process (PID 28750) was terminated by signal 6: Aborted Second issue is a linebreak at the first line of yaml list. Can we remove the linebreak between '-' and 'Plan' ? =# EXPLAIN (format yaml) SELECT * FROM pgbench_accounts; QUERY PLAN ------------------------------------- - Plan: Node Type: Seq Scan Relation Name: pgbench_accounts ... I tried to fix the above issues in the attached v3 patch. I also rewrite grouping_stack field in ExplainState into a *real* stack variable using ExplainStateStack struct. Other changes are only for minor cleanup: - Normalize "es->indent * 2" and "2 * es->indent". - Adjust posisions of '{' and '}'. - Rewrite if-expressions to strchr(). My rewrite is relatively large. Please reversely-review the patch. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -
Re: Add YAML option to explain
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-11-30T03:34:57Z
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> wrote: > My rewrite is relatively large. Please reversely-review the patch. I rethink the code cleanup should be done with another patch even if needed. Here is a lite version of yaml YAML explan patch. All of the logic for indent is done in ExplainYAMLLineStarting(). It can remove undesirable linebreaks from the item list and end of output. Please check whether the v3.1 patch works as your expectation. If ok, we can move it to "Ready for Committer". Regards, --- ITAGAKI Takahiro NTT Open Source Software Center