Horton

Thread

Commits

  1. Doc: fix ancient mistake, or at least obsolete info, in rules example.

  2. Revise SELECT reference page for outer joins, subselect in FROM,

  1. Rules documentation example

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2019-11-11T18:03:13Z

    Hello,

I'm reading the docs about the Postgres Rule system here:

https://www.postgresql.org/docs/12/rules-views.html

That page says:

> It turns out that the planner will collapse this tree into a two-level query tree: the bottommost SELECT commands will be “pulled up” into the middle SELECT since there's no need to process them separately. But the middle SELECT will remain separate from the top, because it contains aggregate functions. If we pulled those up it would change the behavior of the topmost SELECT, which we don't want.

But I don't see an aggregate function. Is it referring to MIN? But
that is this two-param version defined on the same page. It isn't an
aggregate:

CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
    SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;

Is that an error in the docs, or am I missing something?

Does a non-aggregate function also prevent the subqueries from being
pulled up? Will all levels of that query actually get combined, or
does something else prevent it?

Thanks,
Paul

  2. Re: Rules documentation example

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-11-11T18:38:49Z

    Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> I'm reading the docs about the Postgres Rule system here:
> https://www.postgresql.org/docs/12/rules-views.html
> That page says:

>> It turns out that the planner will collapse this tree into a two-level query tree: the bottommost SELECT commands will be “pulled up” into the middle SELECT since there's no need to process them separately. But the middle SELECT will remain separate from the top, because it contains aggregate functions. If we pulled those up it would change the behavior of the topmost SELECT, which we don't want.

> But I don't see an aggregate function. Is it referring to MIN?

Perhaps.  Digging in the git history, that text seems to be mine
(commit 1045304a3), but the example that it's talking about was
pre-existing.  I think I might've just misread it.  It's also
likely (assuming that I was documenting a behavior that I actually
saw at the time) that the real issue is that MIN(), as presented,
defaults to being volatile which would also prevent such flattening.
But this example is so old that I'm not sure whether that particular
optimization behavior existed then.

I'm inclined to:

(1) get rid of the example's MIN() function in favor of using
LEAST(), which is standard and less confusing;

(2) change the text to just say that the planner flattens these
subqueries, so we don't pay any execution-time penalty from the
way the view replacements are handled.

			regards, tom lane