Thread: More WITH
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
> 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
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
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
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
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
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
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
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
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
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
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
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
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
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