Horton

Thread

Commits

  1. Fix create_lateral_join_info to handle dead relations properly.

  2. Expand partitioned table RTEs level by level, without flattening.

  1. pgsql: Expand partitioned table RTEs level by level, without flattening

    Robert Haas <rhaas@postgresql.org> — 2017-09-14T19:41:55Z

    Expand partitioned table RTEs level by level, without flattening.

Flattening the partitioning hierarchy at this stage makes various
desirable optimizations difficult.  The original use case for this
patch was partition-wise join, which wants to match up the partitions
in one partitioning hierarchy with those in another such hierarchy.
However, it now seems that it will also be useful in making partition
pruning work using the PartitionDesc rather than constraint exclusion,
because with a flattened expansion, we have no easy way to figure out
which PartitionDescs apply to which leaf tables in a multi-level
partition hierarchy.

As it turns out, we end up creating both rte->inh and !rte->inh RTEs
for each intermediate partitioned table, just as we previously did for
the root table.  This seems unnecessary since the partitioned tables
have no storage and are not scanned.  We might want to go back and
rejigger things so that no partitioned tables (including the parent)
need !rte->inh RTEs, but that seems to require some adjustments not
related to the core purpose of this patch.

Ashutosh Bapat, reviewed by me and by Amit Langote.  Some final
adjustments by me.

Discussion: http://postgr.es/m/CAFjFpRd=1venqLL7oGU=C1dEkuvk2DJgvF+7uKbnPHaum1mvHQ@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/0a480502b092195a9b25a2f0f199a21d592a9c57

Modified Files
--------------
src/backend/optimizer/path/allpaths.c  |  28 ++--
src/backend/optimizer/plan/initsplan.c |  22 +++-
src/backend/optimizer/plan/planner.c   |  80 ++++++++---
src/backend/optimizer/prep/prepunion.c | 234 +++++++++++++++++++--------------
src/include/nodes/relation.h           |   8 +-
src/test/regress/expected/inherit.out  |  22 ++++
src/test/regress/expected/join.out     |  53 ++++++++
src/test/regress/sql/inherit.sql       |  17 +++
src/test/regress/sql/join.sql          |  23 ++++
9 files changed, 350 insertions(+), 137 deletions(-)

  2. Re: pgsql: Expand partitioned table RTEs level by level, without flattening

    Andreas Seltenreich <seltenreich@gmx.de> — 2017-09-16T10:55:46Z

    Robert Haas writes:

> Expand partitioned table RTEs level by level, without flattening.

testing with sqlsmith shows that the following assertion in this commit
doesn't hold:

TRAP: FailedAssertion("!(((brel)->reloptkind == RELOPT_BASEREL || (brel)->reloptkind == RELOPT_OTHER_MEMBER_REL))", File: "initsplan.c", Line: 647)

One of the simpler queries that triggers it for me:

    select from information_schema.user_mapping_options;

regards,
Andreas

  3. Re: [COMMITTERS] pgsql: Expand partitioned table RTEs level by level, without flattening

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-09-18T07:30:40Z

    On Sun, Sep 17, 2017 at 7:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> Can you debug this on Monday?
>
> ...Robert
>
> Begin forwarded message:
>
> From: Andreas Seltenreich <seltenreich@gmx.de>
> Date: September 16, 2017 at 6:55:46 AM EDT
> To: Robert Haas <rhaas@postgresql.org>
> Cc: pgsql-committers@postgresql.org
> Subject: Re: [COMMITTERS] pgsql: Expand partitioned table RTEs level by
> level, without flattening
>
> Robert Haas writes:
>
> Expand partitioned table RTEs level by level, without flattening.
>
>
> testing with sqlsmith shows that the following assertion in this commit
> doesn't hold:
>
> TRAP: FailedAssertion("!(((brel)->reloptkind == RELOPT_BASEREL ||
> (brel)->reloptkind == RELOPT_OTHER_MEMBER_REL))", File: "initsplan.c", Line:
> 647)
>
> One of the simpler queries that triggers it for me:
>
>    select from information_schema.user_mapping_options;

Thanks Andreas for the report. Sorry for the assertion failure.

PFA patch to fix the assertion failure.

The assertion assumed that all relations in simple_rel_array[] were
either "base" relations or "other" relations. This isn't true. The
array can contain "dead" relations as well. I have removed the
assertion and instead fixed the code to skip anything which is not
"base" or "other member rel".

I have also added a test to cover dead relations and lateral
references in join.sql.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  4. Re: Re: [COMMITTERS] pgsql: Expand partitioned table RTEs level by level, without flattening

    Robert Haas <robertmhaas@gmail.com> — 2017-09-20T14:21:53Z

    On Mon, Sep 18, 2017 at 3:30 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
>> testing with sqlsmith shows that the following assertion in this commit
>> doesn't hold:
>>
>> TRAP: FailedAssertion("!(((brel)->reloptkind == RELOPT_BASEREL ||
>> (brel)->reloptkind == RELOPT_OTHER_MEMBER_REL))", File: "initsplan.c", Line:
>> 647)
>>
>> One of the simpler queries that triggers it for me:
>>
>>    select from information_schema.user_mapping_options;
>
> Thanks Andreas for the report. Sorry for the assertion failure.
>
> PFA patch to fix the assertion failure.
>
> The assertion assumed that all relations in simple_rel_array[] were
> either "base" relations or "other" relations. This isn't true. The
> array can contain "dead" relations as well. I have removed the
> assertion and instead fixed the code to skip anything which is not
> "base" or "other member rel".
>
> I have also added a test to cover dead relations and lateral
> references in join.sql.

Committed.  Thanks.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company