Previously after the 9.2 merge, we had a hack that short-circuits the
execution of the join when ORCA is on. This hack stops working with the
Postgres 12 merge because the FIXME subquery gets executed on the QEs as
well rather than just on the QD. This patch simply turns ORCA off for
the query.
Co-authored-by: NJesse Zhang <sbjesse@gmail.com>
Co-authored-by: NAlexandra Wang <lewang@pivotal.io>
(cherry picked from commit 891e77d1)

Consider the scenario below

```
create table tenk1 (c1 int, ten int);
create temp sequence ts1;
explain select * from (select distinct ten from tenk1) ss where ten < 10 + nextval('ts1') order by 1;
```

The filter outside the subquery is a candidate to be pushed below the
'distinct' in the sub-query.  But since 'nextval' is a volatile function, we
should not push it.

Volatile functions give different results with each execution. We don't want
aggs to use result of a volatile function before it is necessary. We do it for
all aggs - DISTINCT and GROUP BY.

Also see commit 6327f25d.

ORCA Explain plans will now contain:
`Optimizer: Pivotal Optimizer (GPORCA) version 3.35.0`

instead of:
`Optimizer: PQO version 3.35.0`

Authored-by: Chris Hajas chajas@pivotal.io

As we merge with upstream and by that keep refining the Postgres
planner, legacy planner is no longer a suitable name. This changes
all variations of the spelling (legacy planner, legacy optimizer,
legacy query optimizer etc) to say "Postgres" rather than "legacy".
Reviewed-by: NVenkatesh Raghavan <vraghavan@pivotal.io>
Reviewed-by: NDavid Yozie <dyozie@pivotal.io>
Reviewed-by: NGeorgios Kokolatos <gkokolatos@pivotal.io>

We used to call some node types different names in EXPLAIN output,
depending on whether the plan was generated by ORCA or the Postgres
planner. Also, a Bitmap Heap Scan used to be called differently, when the
table was an AO or AOCS table, but only in planner-generated plans. There
was some historical justification for this, because they used to
be different executor node types, but commit db516347 removed last such
differences.

Full list of renames:

Table Scan -> Seq Scan
Append-only Scan -> Seq Scan
Append-only Columnar Scan -> Seq Scan
Dynamic Table Scan -> Dynamic Seq Scan
Bitmap Table Scan -> Bitmap Heap Scan
Bitmap Append-Only Row-Oriented Scan -> Bitmap Heap Scan
Bitmap Append-Only Column-Oriented Scan -> Bitmap Heap Scan
Dynamic Bitmap Table Scan -> Dynamic Bitmap Heap Scan

Previously GPDB would refuse to pull up ANY sublink if the subquery
returns a set-returning function (SRF) in the targetlist, while
PostgreSQL will do that kind of pull-up.

This restriction was added in GPDB years ago to workaround MPP-7085.
That issue has been fixed in other place, so we can remove the
restriction now.

The " Check that whole-row Vars ..." test query was backported earlier,
as part of catching up to 8.3.23, before the GPDB 5 release. In the 9.0
merge, it was accidentally moved to a different location than where it
is in the upstream. Move it back.

It was removed years ago, presumably because GPDB didn't support updates
on views. But it seems to work fine now, so put it back.

This reverts commit 4750e1b6.

This is the final batch of commits from PostgreSQL 9.2 development,
up to the point where the REL9_2_STABLE branch was created, and 9.3
development started on the PostgreSQL master branch.

Notable upstream changes:

* Index-only scan was included in the batch of upstream commits. It
  allows queries to retrieve data only from indexes, avoiding heap access.

* Group commit was added to work effectively under heavy load. Previously,
  batching of commits became ineffective as the write workload increased,
  because of internal lock contention.

* A new fast-path lock mechanism was added to reduce the overhead of
  taking and releasing certain types of locks which are taken and released
  very frequently but rarely conflict.

* The new "parameterized path" mechanism was added. It allows inner index
  scans to use values from relations that are more than one join level up
  from the scan. This can greatly improve performance in situations where
  semantic restrictions (such as outer joins) limit the allowed join orderings.

* SP-GiST (Space-Partitioned GiST) index access method was added to support
  unbalanced partitioned search structures. For suitable problems, SP-GiST can
  be faster than GiST in both index build time and search time.

* Checkpoints now are performed by a dedicated background process. Formerly
  the background writer did both dirty-page writing and checkpointing. Separating
  this into two processes allows each goal to be accomplished more predictably.

* Custom plan was supported for specific parameter values even when using
  prepared statements.

* API for FDW was improved to provide multiple access "paths" for their tables,
  allowing more flexibility in join planning.

* Security_barrier option was added for views to prevents optimizations that
  might allow view-protected data to be exposed to users.

* Range data type was added to store a lower and upper bound belonging to its
  base data type.

* CTAS (CREATE TABLE AS/SELECT INTO) is now treated as utility statement. The
  SELECT query is planned during the execution of the utility. To conform to
  this change, GPDB executes the utility statement only on QD and dispatches
  the plan of the SELECT query to QEs.
Co-authored-by: NAdam Lee <ali@pivotal.io>
Co-authored-by: NAlexandra Wang <lewang@pivotal.io>
Co-authored-by: NAshwin Agrawal <aagrawal@pivotal.io>
Co-authored-by: NAsim R P <apraveen@pivotal.io>
Co-authored-by: NDaniel Gustafsson <dgustafsson@pivotal.io>
Co-authored-by: NGang Xiong <gxiong@pivotal.io>
Co-authored-by: NHaozhou Wang <hawang@pivotal.io>
Co-authored-by: NHeikki Linnakangas <hlinnakangas@pivotal.io>
Co-authored-by: NJesse Zhang <sbjesse@gmail.com>
Co-authored-by: NJinbao Chen <jinchen@pivotal.io>
Co-authored-by: NJoao Pereira <jdealmeidapereira@pivotal.io>
Co-authored-by: NMelanie Plageman <mplageman@pivotal.io>
Co-authored-by: NPaul Guo <paulguo@gmail.com>
Co-authored-by: NRichard Guo <guofenglinux@gmail.com>
Co-authored-by: NShujie Zhang <shzhang@pivotal.io>
Co-authored-by: NTaylor Vesely <tvesely@pivotal.io>
Co-authored-by: NZhenghua Lyu <zlv@pivotal.io>

Commit ce3153fa, about to be merged from PostgreSQL 9.0 soon, removes
the -w option from pg_regress's "diff" invocation. That commit will fix
all the PostgreSQL regression tests to pass without it, but we need to
also fix all the GPDB tests. That's what this commit does.

I did much of this in commit 06a2bb64, but now that we're about to
actually merge that, more cases popped up.

Co-Author: Daniel Gustafsson <dgustafsson@pivotal.io>

These were added in GPDB a long time ago, probably before gpdiff.pl was
introduced to mask row order differences in regression test output. But
now that gpdiff.pl can do that, these are unnecessary. Revert to match
the upstream more closely.

This includes updates to the 'rules' and 'inherit' tests, although they
are disabled and still doesn't pass after these changes.

Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Tue Jul 8 14:03:32 2014 -0400

    While the x output of "select x from t group by x" can be presumed unique,
    this does not hold for "select x, generate_series(1,10) from t group by x",
    because we may expand the set-returning function after the grouping step.
    (Perhaps that should be re-thought; but considering all the other oddities
    involved with SRFs in targetlists, it seems unlikely we'll change it.)
    Put a check in query_is_distinct_for() so it's not fooled by such cases.

    Back-patch to all supported branches.

    David Rowley

(cherry picked from commit 2e7469dc8b3bac4fe0f9bd042aaf802132efde85)

Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Tue Dec 10 16:10:36 2013 -0500

An expression such as WHERE (... x IN (SELECT ...) ...) IN (SELECT ...)
could produce an invalid plan that results in a crash at execution time,
if the planner attempts to flatten the outer IN into a semi-join.
This happens because convert_testexpr() was not expecting any nested
SubLinks and would wrongly replace any PARAM_SUBLINK Params belonging
to the inner SubLink.  (I think the comment denying that this case could
happen was wrong when written; it's certainly been wrong for quite a long
time, since very early versions of the semijoin flattening logic.)

Per report from Teodor Sigaev.  Back-patch to all supported branches.

(cherry picked from commit 884c6384a2db34f6a65573e6bfd4b71dfba0de90)

commit c4ac2ff765d9b68a3ff2a3461804489721770d06
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jun 21 00:14:54 2010 +0000

    Fix mishandling of whole-row Vars referencing a view or sub-select.
    If such a Var appeared within a nested sub-select, we failed to translate it
    correctly during pullup of the view, because the recursive call to
    replace_rte_variables_mutator was looking for the wrong sublevels_up value.
    Bug was introduced during the addition of the PlaceHolderVar mechanism.
    Per bug #5514 from Marcos Castedo.

Commit 038c36b6 from Postgres 8.3 was
merged into Greenplum in a453004e. Commit 038c36b6 is a partial back
port of commit 688aafa1 from Postgres 8.4. What's partial about 038c36b6
is the omission of a test case containing CTE: a whole-row variable can
refer to either an aliased `FROM` clause, or it can refer to a CTE. The
CTE case was omitted because upstream 8.3 didn't have CTE.

The non-CTE test case was slightly modified to add an `ORDER BY` clause
because atmsort is confused by the `ORDER BY` inside the subselect:
semantically we expect the differ to canonicalize (sort) the output
before comparison, because sorted order of a subselect is not preserved
according to SQL standard, but in this case atmsort believes the output
is already sorted (by virtue of the presence of `ORDER BY`, even though
it's within the subselect).

Original commit message of 688aafa1 is enclosed:

> Fix whole-row Var evaluation to cope with resjunk columns (again).
>
> When a whole-row Var is reading the result of a subquery, we need it to
> ignore any "resjunk" columns that the subquery might have evaluated for
> GROUP BY or ORDER BY purposes.  We've hacked this area before, in commit
> 68e40998, but that fix only covered
> whole-row Vars of named composite types, not those of RECORD type; and it
> was mighty klugy anyway, since it just assumed without checking that any
> extra columns in the result must be resjunk.  A proper fix requires getting
> hold of the subquery's targetlist so we can actually see which columns are
> resjunk (whereupon we can use a JunkFilter to get rid of them).  So bite
> the bullet and add some infrastructure to make that possible.
>
> Per report from Andrew Dunstan and additional testing by Merlin Moncure.
> Back-patch to all supported branches.  In 8.3, also back-patch commit
> 292176a1, which for some reason I had
> not done at the time, but it's a prerequisite for this change.

(cherry picked from commit 688aafa15d8d83077c686d2b5b88226528e29840)

QD used to send a transient types table to QEs, then QE would remap the
tuples with this table before sending them to QD. However in complex
queries QD can't discover all the transient types so tuples can't be
correctly remapped on QEs. One example is like below:

    SELECT q FROM (SELECT MAX(f1) FROM int4_tbl
                   GROUP BY f1 ORDER BY f1) q;
    ERROR:  record type has not been registered

To fix this issue we changed the underlying logic: instead of sending
the possibly incomplete transient types table from QD to QEs, we now
send the tables from motion senders to motion receivers and do the remap
on receivers. Receivers maintain a remap table for each motion so tuples
from different senders can be remapped accordingly. In such way, queries
contain multi-slices can also handle transient record type correctly
between two QEs.

The remap logic is derived from the executor/tqueue.c in upstream
postgres. There is support for composite/record types and arrays as well
as range types, however as range types are not yet supported in GPDB so
the logic is put under a conditional compilation macro, in theory it
shall be automatically enabled when range types are supported in GPDB.

One side effect for this approach is that on receivers a performance
down is introduced as the remap requires recursive checks on each tuple
of record types. However optimization is made to make this side effect
minimum on non-record types.

Old logic that building transient types table on QD and sending them to
QEs are retired.
Signed-off-by: NGang Xiong <gxiong@pivotal.io>
Signed-off-by: NNing Yu <nyu@pivotal.io>

This reverts commit ab4398dd.
[#142986717]

QD used to send a transient types table to QEs, then QE would remap the
tuples with this table before sending them to QD. However in complex
queries QD can't discover all the transient types so tuples can't be
correctly remapped on QEs. One example is like below:

    SELECT q FROM (SELECT MAX(f1) FROM int4_tbl
                   GROUP BY f1 ORDER BY f1) q;
    ERROR:  record type has not been registered

To fix this issue we changed the underlying logic: instead of sending
the possibly incomplete transient types table from QD to QEs, we now
send the tables from motion senders to motion receivers and do the remap
on receivers. Receivers maintain a remap table for each motion so tuples
from different senders can be remapped accordingly. In such way, queries
contain multi-slices can also handle transient record type correctly
between two QEs.

The remap logic is derived from the executor/tqueue.c in upstream
postgres. There is support for composite/record types and arrays as well
as range types, however as range types are not yet supported in GPDB so
the logic is put under a conditional compilation macro, in theory it
shall be automatically enabled when range types are supported in GPDB.

One side effect for this approach is that on receivers a performance
down is introduced as the remap requires recursive checks on each tuple
of record types. However optimization is made to make this side effect
minimum on non-record types.

Old logic that building transient types table on QD and sending them to
QEs are retired.
Signed-off-by: NGang Xiong <gxiong@pivotal.io>
Signed-off-by: NNing Yu <nyu@pivotal.io>