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

Search Discussions

  • Andrew Dunstan at Aug 28, 2009 at 7:33 pm

    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
  • Greg Sabino Mullane at Aug 28, 2009 at 8:14 pm

    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
  • Florian Weimer at Aug 28, 2009 at 9:05 pm

    * 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-)
  • Josh Berkus at Aug 28, 2009 at 9:24 pm

    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
  • Joshua D. Drake at Aug 28, 2009 at 9:29 pm

    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
  • Greg Sabino Mullane at Aug 28, 2009 at 9:43 pm

    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
  • Peter Eisentraut at Aug 28, 2009 at 9:34 pm

    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.
  • Ron Mayer at Aug 29, 2009 at 12:03 am

    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.
  • Greg Sabino Mullane at Aug 31, 2009 at 2:15 pm

    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
  • Kevin Grittner at Aug 31, 2009 at 4:57 pm

    "Greg Sabino Mullane" 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
  • Daveg at Aug 31, 2009 at 8:06 pm

    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.
  • Stephen Frost at Aug 28, 2009 at 10:45 pm

    * 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
  • David E. Wheeler at Aug 28, 2009 at 11:37 pm

    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
  • Daveg at Aug 29, 2009 at 4:22 am

    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.
  • Greg Sabino Mullane at Aug 31, 2009 at 1:43 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 28, '09 at 7:17p
activeAug 31, '09 at 8:06p
posts16
users11
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase