most node types used in expression trees (both before and after parse
analysis).  This allows us to place an error cursor in many situations
where we formerly could not, because the information wasn't available
beyond the very first level of parse analysis.  There's a fair amount
of work still to be done to persuade individual ereport() calls to actually
include an error location, but this gets the initdb-forcing part of the
work out of the way; and the situation is already markedly better than
before for complaints about unimplementable implicit casts, such as
CASE and UNION constructs with incompatible alternative data types.
Per my proposal of a few days ago.

it possible to call them without specifying a column list.

Jaime Casanova

and anti joins.  To do this, pass the SpecialJoinInfo struct for the current
join as an additional optional argument to operator join selectivity
estimation functions.  This allows the estimator to tell not only what kind
of join is being formed, but which variable is on which side of the join;
a requirement long recognized but not dealt with till now.  This also leaves
the door open for future improvements in the estimators, such as accounting
for the null-insertion effects of lower outer joins.  I didn't do anything
about that in the current patch but the information is in principle deducible
from what's passed.

The patch also clarifies the definition of join selectivity for semi/anti
joins: it's the fraction of the left input that has (at least one) match
in the right input.  This allows getting rid of some very fuzzy thinking
that I had committed in the original 7.4-era IN-optimization patch.
There's probably room to estimate this better than the present patch does,
but at least we know what to estimate.

Since I had to touch CREATE OPERATOR anyway to allow a variant signature
for join estimator functions, I took the opportunity to add a couple of
additional checks that were missing, per my recent message to -hackers:
* Check that estimator functions return float8;
* Require execute permission at the time of CREATE OPERATOR on the
operator's function as well as the estimator functions;
* Require ownership of any pre-existing operator that's modified by
the command.
I also moved the lookup of the functions out of OperatorCreate() and
into operatorcmds.c, since that seemed more consistent with most of
the other catalog object creation processes, eg CREATE TYPE.

This completes my project of improving usage of hashing for duplicate
elimination (aggregate functions with DISTINCT remain undone, but that's
for some other day).

As with the previous patches, this means we can INTERSECT/EXCEPT on datatypes
that can hash but not sort, and it means that INTERSECT/EXCEPT without ORDER
BY are no longer certain to produce sorted output.

but seem like a separate patch since most of the remaining work is on the
executor side.)  I took the opportunity to push selection of the grouping
operators for set operations into the parser where it belongs.  Otherwise this
is just a small exercise in making prepunion.c consider both alternatives.

As with the recent DISTINCT patch, this means we can UNION on datatypes that
can hash but not sort, and it means that UNION without ORDER BY is no longer
certain to produce sorted output.

as methods for implementing the DISTINCT step.  This eliminates the former
performance gap between DISTINCT and GROUP BY, and also makes it possible
to do SELECT DISTINCT on datatypes that only support hashing not sorting.

SELECT DISTINCT ON is still always implemented by sorting; it would take
executor changes to support hashing that, and it's not clear it's worth
the trouble.

This is a release-note-worthy incompatibility from previous PG versions,
since SELECT DISTINCT can no longer be counted on to deliver sorted output
without explicitly saying ORDER BY.  (Anyone who can't cope with that
can consider turning off enable_hashagg.)

Several regression test queries needed to have ORDER BY added to preserve
stable output order.  I fixed the ones that manifested here, but there
might be some other cases that show up on other platforms.

only type categories in which the previous coding made *every* type
preferred; so there is no change in effective behavior, because the function
resolution rules only do something different when faced with a choice
between preferred and non-preferred types in the same category.  It just
seems safer and less surprising to have CREATE TYPE default to non-preferred
status ...

with system catalog lookups, as was foreseen to be necessary almost since
their creation.  Instead put the information into two new pg_type columns,
typcategory and typispreferred.  Add support for setting these when
creating a user-defined base type.

The category column is just a "char" (i.e. a poor man's enum), allowing
a crude form of user extensibility of the category list: just use an
otherwise-unused character.  This seems sufficient for foreseen uses,
but we could upgrade to having an actual category catalog someday, if
there proves to be a huge demand for custom type categories.

In this patch I have attempted to hew exactly to the behavior of the
previous hardwired logic, except for introducing new type categories for
arrays, composites, and enums.  In particular the default preferred state
for user-defined types remains TRUE.  That seems worth revisiting, but it
should be done as a separate patch from introducing the infrastructure.
Likewise, any adjustment of the standard set of categories should be done
separately.

and bogus documentation (dimension arrays are int[] not anyarray).  Also the
errhint() messages seem to be really errdetail(), since there is nothing
heuristic about them.  Some other trivial cosmetic improvements.

(Unlike the original submission, this patch treats TABLE output parameters
as being entirely equivalent to OUT parameters -- tgl)

Pavel Stehule

need to deconstruct proargmodes for each pg_proc entry inspected by
FuncnameGetCandidates().  Fixes function lookup performance regression
caused by yesterday's variadic-functions patch.

In passing, make pg_proc.probin be NULL, rather than a dummy value '-',
in cases where it is not actually used for the particular type of function.
This should buy back some of the space cost of the extra column.

so long as all the trailing arguments are of the same (non-array) type.
The function receives them as a single array argument (which is why they
have to all be the same type).

It might be useful to extend this facility to aggregates, but this patch
doesn't do that.

This patch imposes a noticeable slowdown on function lookup --- a follow-on
patch will fix that by adding a redundant column to pg_proc.

Pavel Stehule

Pavel Stehule

on the most common individual lexemes in place of the mostly-useless default
behavior of counting duplicate tsvectors.  Future work: create selectivity
estimation functions that actually do something with these stats.

(Some other things we ought to look at doing: using the Lossy Counting
algorithm in compute_minimal_stats, and using the element-counting idea for
stats on regular arrays.)

Jan Urbanski

bug #4290.  The fundamental bug is that masking extParam by outer_params,
as finalize_plan had been doing, caused us to lose the information that
an initPlan depended on the output of a sibling initPlan.  On reflection
the best thing to do seemed to be not to try to adjust outer_params for
this case but get rid of it entirely.  The only thing it was really doing
for us was to filter out param IDs associated with SubPlan nodes, and that
can be done (with greater accuracy) while processing individual SubPlan
nodes in finalize_primnode.  This approach was vindicated by the discovery
that the masking method was hiding a second bug: SS_finalize_plan failed to
remove extParam bits for initPlan output params that were referenced in the
main plan tree (it only got rid of those referenced by other initPlans).
It's not clear that this caused any real problems, given the limited use
of extParam by the executor, but it's certainly not what was intended.

I originally thought that there was also a problem with needing to include
indirect dependencies on external params in initPlans' param sets, but it
turns out that the executor handles this correctly so long as the depended-on
initPlan is earlier in the initPlans list than the one using its output.
That seems a bit of a fragile assumption, but it is true at the moment,
so I just documented it in some code comments rather than making what would
be rather invasive changes to remove the assumption.

Back-patch to 8.1.  Previous versions don't have the case of initPlans
referring to other initPlans' outputs, so while the existing logic is still
questionable for them, there are not any known bugs to be fixed.  So I'll
refrain from changing them for now.

scripts, to allow running the test successfully with another database name.

of any lower outer join, even if it also references the non-nullable side and
so could not get pushed below the outer join anyway.  We need this in case
the clause is an OR clause: if it doesn't get marked outerjoin_delayed,
create_or_index_quals() could pull an indexable restriction for the nullable
side out of it, leading to wrong results as demonstrated by today's bug
report from toruvinn.  (See added regression test case for an example.)

In principle this has been wrong for quite a while.  In practice I don't
think any branch before 8.3 can really show the failure, because
create_or_index_quals() will only pull out indexable conditions, and before
8.3 those were always strict.  So though we might have improperly generated
null-extended rows in the outer join, they'd get discarded from the result
anyway.  The gating factor that makes the failure visible is that 8.3
considers "col IS NULL" to be indexable.  Hence I'm not going to risk
back-patching further than 8.3.

objects are specified, we drop them all in a single performMultipleDeletions
call.  This makes the RESTRICT/CASCADE checks more relaxed: it's not counted
as a cascade if one of the later objects has a dependency on an earlier one.
NOTICE messages about such cases go away, too.

In passing, fix the permissions check for DROP CONVERSION, which for some
reason was never made role-aware, and omitted the namespace-owner exemption
too.

Alex Hunsaker, with further fiddling by me.

devised for pg_shdepend, namely the individual dependencies are reported as
DETAIL lines rather than coming out as separate NOTICEs.  The client-side
report is capped at 100 lines, but the server log always gets a full report.

is testing.  Ah, the perils of making keywords optional ...

algorithm, replacing the original intention of a one-pass search, which
had been hacked up over time to be partially two-pass in hopes of handling
various corner cases better.  It still wasn't quite there, especially as
regards emitting unwanted NOTICE messages.  More importantly, this approach
lets us fix a number of open bugs concerning concurrent DROP scenarios,
because we can take locks during the first pass and avoid traversing to
dependent objects that were just deleted by someone else.

There is more that can be done here, but I'll go ahead and commit the
base patch before working on the options.

the associated datatype as their equality member.  This means that these
opclasses can now support plain equality comparisons along with LIKE tests,
thus avoiding the need for an extra index in some applications.  This
optimization was not possible when the pattern opclasses were first introduced,
because we didn't insist that text equality meant bitwise equality; but we
do now, so there is no semantic difference between regular and pattern
equality operators.

I removed the name_pattern_ops opclass altogether, since it's really useless:
name's regular comparisons are just strcmp() and are unlikely to become
something different.  Instead teach indxpath.c that btree name_ops can be
used for LIKE whether or not the locale is C.  This might lead to a useful
speedup in LIKE queries on the system catalogs in non-C locales.

The ~=~ and ~<>~ operators are gone altogether.  (It would have been nice to
keep them for backward compatibility's sake, but since the pg_amop structure
doesn't allow multiple equality operators per opclass, there's no way.)

A not-immediately-obvious incompatibility is that the sort order within
bpchar_pattern_ops indexes changes --- it had been identical to plain
strcmp, but is now trailing-blank-insensitive.  This will impact
in-place upgrades, if those ever happen.

Per discussions a couple months ago.

cases.  Recent buildfarm experience shows that it is sometimes possible
to execute several SQL commands in less time than the granularity of
Windows' not-very-high-resolution gettimeofday(), leading to a failure
because the tests expect the value of now() to change and it doesn't.
Also, it was recognized some time ago that the same area of the tests
could fail if local midnight passes between the insertion and the checking
of the values for 'yesterday', 'tomorrow', etc.  Clean all this up per
ideas from myself and Greg Stark.

There remains a window for failure if the transaction block is entered
exactly at local midnight (so that 'now' and 'today' have the same value),
but that seems low-probability enough to live with.

Since the point of this change is mostly to eliminate buildfarm noise,
back-patch to all versions we are still actively testing.

sequence to be reset to its original starting value.  This requires adding the
original start value to the set of parameters (columns) of a sequence object,
which is a user-visible change with potential compatibility implications;
it also forces initdb.

Also add hopefully-SQL-compatible RESTART/CONTINUE IDENTITY options to
TRUNCATE TABLE.  RESTART IDENTITY executes ALTER SEQUENCE RESTART for all
sequences "owned by" any of the truncated relations.  CONTINUE IDENTITY is
a no-op option.

Zoltan Boszormenyi

prefix matching using this facility.

Teodor Sigaev and Oleg Bartunov

Pavel Stehule

functions.

Note that because this patch changes FmgrInfo, any external C functions
you might be testing with 8.4 will need to be recompiled.

Patch by Martin Pihlak, some editorialization by me (principally, removing
tracking of getrusage() numbers)

HINT fields to a user-thrown error message, and to specify the SQLSTATE
error code to use.  The syntax has also been tweaked so that the
Oracle-compatible case "RAISE exception_name" works (though you won't get a
very nice error message if you just write that much).  Lastly, support
the Oracle-compatible syntax "RAISE" with no parameters to re-throw
the current error from within an EXCEPTION block.

In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists,
so that there is a way to trap errors with custom SQLSTATE codes.

Pavel Stehule and Tom Lane

as those for inherited columns; that is, it's no longer allowed for a child
table to not have a check constraint matching one that exists on a parent.
This satisfies the principle of least surprise (rows selected from the parent
will always appear to meet its check constraints) and eliminates some
longstanding bogosity in pg_dump, which formerly had to guess about whether
check constraints were really inherited or not.

The implementation involves adding conislocal and coninhcount columns to
pg_constraint (paralleling attislocal and attinhcount in pg_attribute)
and refactoring various ALTER TABLE actions to be more like those for
columns.

Alex Hunsaker, Nikhil Sontakke, Tom Lane

something was wrong with that tab patch.

adjust source code to be more modular.

instead of calling a bunch of individual functions.

This function can also be called directly, taking a PID as an argument, to
return only the data for a single PID.