Re: SQL:2011 application time

On Tue, Aug 6, 2024 at 10:02 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Fri, Aug 2, 2024 at 1:09 AM Paul Jungwirth
> <pj(at)illuminatedcomputing(dot)com> wrote:
> >
> > On 7/25/24 08:52, Paul Jungwirth wrote:
> > > Here is a patch moving the not-empty check into check_exclusion_or_unique_constraint. That is a more
> > > logical place for it than ExecConstraints, since WITHOUT OVERLAPS is part of the index constraint
> > > (not a CHECK constraint). At that point we've already looked up all the information we need. So
> > > there is no extra cost for non-temporal tables, and no need to change pg_class or add to the
> > > relcache. Also putting it there means we don't need any extra code to enforce non-empties when we
> > > build the index or do anything else with it.
> > >
> > > I think this is the nicest solution we can expect. It is even cleaner than the &&& ideas. So
> > > hopefully this gets us back to where we were when we decided to commit PKs & FKs to v17.
> > >
> > > As before, I've left the nonempty check as a separate patch to make reviewing easier, but when
> > > committing I would squash it with the PK patch.
> >
> > Hello,
> >
> > Here is an updated set of patches, rebased because the old patches no longer applied.
> >

hi. some minor issues.

in generateClonedIndexStmt
index->iswithoutoverlaps = (idxrec->indisprimary ||
idxrec->indisunique) && idxrec->indisexclusion;
this case, the index accessMethod will be "gist" only?

do you think it's necessary to:
index->iswithoutoverlaps = (idxrec->indisprimary ||
idxrec->indisunique) && idxrec->indisexclusion
&& strcmp(index->accessMethod, "gist") == 0);

src/bin/pg_dump/pg_dump.c and src/bin/psql/describe.c
should be "if (pset.sversion >= 180000)"?

+ (This is sometimes called a
+ temporal key, if the column is a range of dates or timestamps, but
+ PostgreSQL allows ranges over any base type.)

PostgreSQL should be decorated as
<productname>PostgreSQL</productname>
?

in DefineIndex we have:
if (stmt->unique && !stmt->iswithoutoverlaps && !amRoutine->amcanunique)
if (stmt->indexIncludingParams != NIL && !amRoutine->amcaninclude)
if (numberOfKeyAttributes > 1 && !amRoutine->amcanmulticol)
if (exclusion && amRoutine->amgettuple == NULL)

maybe we can add:
if (stmt->iswithoutoverlaps && strcmp(accessMethodName, "gist") != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("access method \"%s\" does not support WITHOUT
OVERLAPS constraints",
accessMethodName)));

+ /* exclusionOpNames can be non-NIL if we are creating a partition */
+ if (iswithoutoverlaps && exclusionOpNames == NIL)
+ {
+ indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols);
+ indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols);
+ indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols);
+ }
the comment is not 100% correct, i think.
creating a partition, "create table like INCLUDING ALL", both will go
through generateClonedIndexStmt.
generateClonedIndexStmt will produce exclusionOpNames if this index
supports exclusion constraint.