More WITH

EXPLAIN [ANALYZE]

Would be tricky. We don't currently have any way to wrap an EXPLAIN in
any larger statement, do we? Would be very useful for automated query
analysis, though.

SHOW

Not very useful, easy to work around (pg_settings).

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Aug 17, 2015 at 10:22 AM, Josh Berkus <josh@agliodbs.com> wrote:

Would be tricky. We don't currently have any way to wrap an EXPLAIN in
any larger statement, do we? Would be very useful for automated query
analysis, though.

No. In the grammar, ExplainStmt expects the EXPLAIN to be at the
top-level. Having it work any other way would require significant
refactoring.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Aug 17, 2015 at 10:22:11AM -0700, Josh Berkus wrote:

EXPLAIN [ANALYZE]

Would be tricky. We don't currently have any way to wrap an EXPLAIN
in any larger statement, do we?

We do, but it's kinda horrible.

CREATE OR REPLACE FUNCTION get_something_from_explain(your_query)
RETURNS TEXT
LANGUAGE plpgsql /* uh oh */
AS $$
DECLARE
foo JSON;
BEGIN
EXECUTE format('EXPLAIN (FORMAT json), your_query) INTO foo;
RETURN foo #>> '{bar,baz,quux}';
END;
$$;

Would be very useful for automated query analysis, though.

Among many other things, certainly :)

SHOW

Not very useful, easy to work around (pg_settings).

This particular one is just about being consistent, or the way I look
at it, about avoiding surprising users with inconsistencies.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Geoghegan <pg@heroku.com> writes:

On Mon, Aug 17, 2015 at 10:22 AM, Josh Berkus <josh@agliodbs.com> wrote:

Would be tricky. We don't currently have any way to wrap an EXPLAIN in
any larger statement, do we? Would be very useful for automated query
analysis, though.

No. In the grammar, ExplainStmt expects the EXPLAIN to be at the
top-level. Having it work any other way would require significant
refactoring.

You can use EXPLAIN as the source of rows in a plpgsql FOR-over-query
loop, so there's a workaround available that way when you need to read
EXPLAIN output programmatically. I'm not convinced there's sufficient
value in trying to make EXPLAIN a full-fledged subquery otherwise.

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

On 08/17/2015 01:30 PM, Peter Geoghegan wrote:

On Mon, Aug 17, 2015 at 10:22 AM, Josh Berkus <josh@agliodbs.com> wrote:

Would be tricky. We don't currently have any way to wrap an EXPLAIN in
any larger statement, do we? Would be very useful for automated query
analysis, though.

No. In the grammar, ExplainStmt expects the EXPLAIN to be at the
top-level. Having it work any other way would require significant
refactoring.

Slightly apropos, I have wrapped EXPLAIN calls inside a function, such
as one that gets back the result and then sends it off to
http://explain.depesz.com, returning the URL

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Aug 17, 2015 at 1:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Geoghegan <pg@heroku.com> writes:

On Mon, Aug 17, 2015 at 10:22 AM, Josh Berkus <josh@agliodbs.com> wrote:

Would be tricky. We don't currently have any way to wrap an EXPLAIN in
any larger statement, do we? Would be very useful for automated query
analysis, though.

No. In the grammar, ExplainStmt expects the EXPLAIN to be at the
top-level. Having it work any other way would require significant
refactoring.

You can use EXPLAIN as the source of rows in a plpgsql FOR-over-query
loop, so there's a workaround available that way when you need to read
EXPLAIN output programmatically. I'm not convinced there's sufficient
value in trying to make EXPLAIN a full-fledged subquery otherwise.

I think a lot of people would find that handy - I would - but I don't
know how hard it is.

--
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

On 18 August 2015 at 01:18, David Fetter <david@fetter.org> wrote:

FETCH [in WITH]

I'd be a huge fan of this one. I'd love to see FETCH in subqueries,
too. Currently doing anything like this requires an ugly PL/PgSQL
wrapper.

The cursor would have to be known at plan-time so it could be
interrogated for its types.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 18 August 2015 at 01:18, David Fetter <david@fetter.org> wrote:

Folks,

In the interest of consistency, which is to say, of not hitting
barriers that are essentially implementation details, I'd like to
propose that we allow the rest of the row-returning commands inside
WITH clauses. We currently have:

SELECT
VALUES
INSERT/UPDATE/DELETE ... RETURNING

We don't yet have:

EXPLAIN [ANALYZE]
SHOW
FETCH

A little further out there, although this would be an API change, we
might consider allowing the results of VACUUM and ANALYZE as row sets,
which would also be good to wrap in WITH.

Is there a good reason, or more than one, why we shouldn't have all
the row-returning commands in WITH?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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

Craig Ringer <craig@2ndquadrant.com> writes:

On 18 August 2015 at 01:18, David Fetter <david@fetter.org> wrote:

FETCH [in WITH]

I'd be a huge fan of this one. I'd love to see FETCH in subqueries,
too. Currently doing anything like this requires an ugly PL/PgSQL
wrapper.

The cursor would have to be known at plan-time so it could be
interrogated for its types.

That's barely the tip of the iceberg of the problems with this idea.

How many rows would be fetched from the cursor? What row would it be
left on? Whatever answer you give will be wrong from some perspective,
but particularly that of giving the planner any freedom-of-action
to optimize such a query.

More generally, what would you hope to accomplish with such a construct
that wouldn't be better done by writing the cursor's underlying query
directly in the WITH clause?

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

On Tue, Aug 18, 2015 at 11:23:32PM -0400, Tom Lane wrote:

Craig Ringer <craig@2ndquadrant.com> writes:

On 18 August 2015 at 01:18, David Fetter <david@fetter.org> wrote:

FETCH [in WITH]

I'd be a huge fan of this one. I'd love to see FETCH in
subqueries, too. Currently doing anything like this requires an
ugly PL/PgSQL wrapper.

The cursor would have to be known at plan-time so it could be
interrogated for its types.

That's barely the tip of the iceberg of the problems with this idea.

How many rows would be fetched from the cursor? What row would it
be left on? Whatever answer you give will be wrong from some
perspective, but particularly that of giving the planner any
freedom-of-action to optimize such a query.

More generally, what would you hope to accomplish with such a
construct that wouldn't be better done by writing the cursor's
underlying query directly in the WITH clause?

So FETCH is not a good candidate for inclusion in WITH, at least until
someone comes up with some meaningful definition of what this would
mean.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Aug 18, 2015 at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

More generally, what would you hope to accomplish with such a construct
that wouldn't be better done by writing the cursor's underlying query
directly in the WITH clause?

Maybe I'm stupid today, but it seems like the obvious use case would
be fetching some but not all rows from the cursor?

--
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

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Aug 18, 2015 at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

More generally, what would you hope to accomplish with such a construct
that wouldn't be better done by writing the cursor's underlying query
directly in the WITH clause?

Maybe I'm stupid today, but it seems like the obvious use case would
be fetching some but not all rows from the cursor?

And how many rows would that be? As I said, the proposed syntax leaves
it completely unclear how many rows get fetched or what the ending cursor
position is; but especially so if you want the answer to be something
other than "all/the end".

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

On Fri, Aug 21, 2015 at 2:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Aug 18, 2015 at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

More generally, what would you hope to accomplish with such a construct
that wouldn't be better done by writing the cursor's underlying query
directly in the WITH clause?

Maybe I'm stupid today, but it seems like the obvious use case would
be fetching some but not all rows from the cursor?

And how many rows would that be? As I said, the proposed syntax leaves
it completely unclear how many rows get fetched or what the ending cursor
position is; but especially so if you want the answer to be something
other than "all/the end".

/me is bemused.

The existing syntax for FETCH already includes a way to specify the
number of rows you want to fetch, as in this example from the
documentation:

FETCH FORWARD 5 FROM liahona;

Why wouldn't that work here too?

--
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

Robert Haas <robertmhaas@gmail.com> writes:

The existing syntax for FETCH already includes a way to specify the
number of rows you want to fetch, as in this example from the
documentation:
FETCH FORWARD 5 FROM liahona;
Why wouldn't that work here too?

Mm, okay, but you still have the other objections to address.

(And it remains the case that you can do this today with a plpgsql
function, which solves the indeterminate-rowtype problem by nailing
down the rowtype at the function result level.)

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

On Fri, Aug 21, 2015 at 2:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

The existing syntax for FETCH already includes a way to specify the
number of rows you want to fetch, as in this example from the
documentation:
FETCH FORWARD 5 FROM liahona;
Why wouldn't that work here too?

Mm, okay, but you still have the other objections to address.

(And it remains the case that you can do this today with a plpgsql
function, which solves the indeterminate-rowtype problem by nailing
down the rowtype at the function result level.)

Sure, I'm not wildly in love with the feature and am not volunteering
to implement it. The EXPLAIN case seems more useful to me, but I'm
not volunteering to implement that either. But I don't think they are
insane propositions as you seem to be suggesting. Creating a wrapper
function works, but it's not obvious to non-experts that that's what
you need to do, and it's not terribly convenient anyway. It's kind of
silly to say, well, we can generate these rows on the server and ship
them back to the client, but we can't generate them on the server and
then post-process them in some way. That kind of composability is a
central advantage of SQL, and I'm sure that if we had it here it would
get used. But it's not a five-minute job to make it work properly,
either. Or even a five-day job.

--
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