Files
Vinod Sridharan c6bc2bcb73 Merged PR 1694458: [POC|Order by] Order by Index & Pushdown improvements
### Does this PR have any customer impact?
Yes

### Type (Feature, Refactoring, Bugfix, DevOps, Testing, Perf, etc)
Feature

### Does it involve schema level changes? (Table, Column, Index, UDF, etc level changes)
Yes

### Are you introducing any new config? If yes, do you have tests with and without them being set?
Yes

### ChangeLog (Refer [Template](../oss/CHANGELOG.md))

### Description
More general improvements to order by and composite:
Create Indexes
1) Support creating a composite index side by side with a regular index
2) Block creation of descending or wildcard and unsupported set ups for composite indexes

Planner:
1) Support ordering by the Nth path of an index if the prefixes are equality.
2) Validate that if it's not the first column, prefixes are provided
3) Support incremental sort with index pushdown.

----
#### AI description  (iteration 1)
#### PR Classification
This pull request delivers a new feature and API enhancement to enable order-by index pushdown and refined composite index support.

#### PR Summary
The update streamlines composite index processing by introducing stricter equivalency checks, new UDF functions for full-scan order-by pushdown, and enhanced validations during index creation while updating explain outputs and tests.
- `src/metadata/index.c` refines index options equivalency logic to correctly compare composite term settings.
- `src/opclass/bson_gin_composite_entrypoint.c` and `src/index_am/rum.c` add functions to support order-by pushdown and manage duplicate entries efficiently.
- `sql/udfs/query/bson_dollar_comparison--0.105-0.sql` (and related operator files) introduce new UDFs including `bson_dollar_fullscan` to enable full scan operations.
- `src/commands/create_indexes.c` enforces new composite index validations (restricting use to ascending, non-wildcard regular indexes) with precise error reporting.
- Test scripts and explain plan outputs in the `regress` and `pgmongo` directories are updated to confirm the new composite order-by behavior.
<!-- GitOpsUserAgent=GitOps.Apps.Server.pullrequestcopilot -->
2025-06-13 15:13:47 +08:00

436 lines
16 KiB
C

/*-------------------------------------------------------------------------
* Copyright (c) Microsoft Corporation. All rights reserved.
*
* include/metadata/metadata_cache.h
*
* Common declarations for metadata caching functions.
*
*-------------------------------------------------------------------------
*/
#ifndef METADATA_CACHE_H
#define METADATA_CACHE_H
#include <nodes/parsenodes.h>
#include "utils/type_cache.h"
extern PGDLLIMPORT char *ApiDataSchemaName;
extern PGDLLIMPORT char *ApiAdminRole;
extern PGDLLIMPORT char *ApiAdminRoleV2;
extern PGDLLIMPORT char *ApiReadOnlyRole;
extern PGDLLIMPORT char *ApiSchemaName;
extern PGDLLIMPORT char *ApiSchemaNameV2;
extern PGDLLIMPORT char *ApiInternalSchemaName;
extern PGDLLIMPORT char *ApiInternalSchemaNameV2;
extern PGDLLIMPORT char *ExtensionObjectPrefix;
extern PGDLLIMPORT char *ExtensionObjectPrefixV2;
extern PGDLLIMPORT char *CoreSchemaName;
extern PGDLLIMPORT char *CoreSchemaNameV2;
extern PGDLLIMPORT char *FullBsonTypeName;
extern PGDLLIMPORT char *ApiCatalogSchemaName;
extern PGDLLIMPORT char *ApiCatalogSchemaNameV2;
extern PGDLLIMPORT char *ApiToApiInternalSchemaName;
extern PGDLLIMPORT char *ApiCatalogToApiInternalSchemaName;
extern PGDLLIMPORT char *PostgisSchemaName;
extern PGDLLIMPORT char *DocumentDBApiInternalSchemaName;
extern PGDLLIMPORT char *ApiCatalogToCoreSchemaName;
extern MemoryContext DocumentDBApiMetadataCacheContext;
/* functions related with pg_documentdb "extension" itself */
void InitializeDocumentDBApiExtensionCache(void);
void InvalidateCollectionsCache(void);
bool IsDocumentDBApiExtensionActive(void);
Oid DocumentDBApiExtensionOwner(void);
char * GetExtensionApplicationName(void);
/* functions and procedures */
Oid ApiCollectionFunctionId(void);
Oid DocumentDBApiCollectionFunctionId(void);
Oid ApiCreateIndexesProcedureId(void);
Oid ApiReIndexProcedureId(void);
Oid BsonEqualMatchRuntimeFunctionId(void);
Oid BsonEqualMatchRuntimeOperatorId(void);
Oid BsonEqualMatchIndexFunctionId(void);
Oid BsonGreaterThanMatchRuntimeFunctionId(void);
Oid BsonGreaterThanMatchRuntimeOperatorId(void);
Oid BsonGreaterThanMatchIndexFunctionId(void);
Oid BsonGreaterThanEqualMatchRuntimeFunctionId(void);
Oid BsonGreaterThanEqualMatchRuntimeOperatorId(void);
Oid BsonGreaterThanEqualMatchIndexFunctionId(void);
Oid BsonLessThanMatchRuntimeFunctionId(void);
Oid BsonLessThanMatchRuntimeOperatorId(void);
Oid BsonLessThanMatchIndexFunctionId(void);
Oid BsonLessThanEqualMatchRuntimeFunctionId(void);
Oid BsonLessThanEqualMatchRuntimeOperatorId(void);
Oid BsonLessThanEqualMatchIndexFunctionId(void);
Oid BsonRangeMatchFunctionId(void);
Oid BsonRangeMatchOperatorOid(void);
Oid BsonFullScanFunctionOid(void);
Oid BsonInMatchFunctionId(void);
Oid BsonNinMatchFunctionId(void);
Oid BsonNotEqualMatchFunctionId(void);
Oid BsonElemMatchMatchFunctionId(void);
Oid BsonAllMatchFunctionId(void);
Oid BsonBitsAllClearFunctionId(void);
Oid BsonBitsAnyClearFunctionId(void);
Oid BsonBitsAllSetFunctionId(void);
Oid BsonBitsAnySetFunctionId(void);
Oid BsonRegexMatchFunctionId(void);
Oid BsonModMatchFunctionId(void);
Oid BsonSizeMatchFunctionId(void);
Oid BsonTypeMatchFunctionId(void);
Oid BsonExistsMatchFunctionId(void);
Oid BsonExprFunctionId(void);
Oid BsonExprWithLetFunctionId(void);
Oid BsonJsonSchemaFunctionId(void);
Oid BsonTextFunctionId(void);
Oid BsonEmptyDataTableFunctionId(void);
Oid IndexSpecAsBsonFunctionId(void);
Oid IndexBuildIsInProgressFunctionId(void);
Oid ApiCursorStateFunctionId(void);
Oid ApiCurrentCursorStateFunctionId(void);
Oid ExtensionTableSampleSystemRowsFunctionId(void);
Oid BsonInRangeNumericFunctionId(void);
Oid BsonInRangeIntervalFunctionId(void);
Oid BsonQueryMatchWithLetAndCollationFunctionId(void);
/* bson_value functions */
Oid BsonValueEqualMatchFunctionId(void);
Oid BsonValueGreaterThanMatchFunctionId(void);
Oid BsonValueGreaterThanEqualMatchFunctionId(void);
Oid BsonValueLessThanMatchFunctionId(void);
Oid BsonValueLessThanEqualMatchFunctionId(void);
Oid BsonValueSizeMatchFunctionId(void);
Oid BsonValueTypeMatchFunctionId(void);
Oid BsonValueInMatchFunctionId(void);
Oid BsonValueNinMatchFunctionId(void);
Oid BsonValueNotEqualMatchFunctionId(void);
Oid BsonValueExistsMatchFunctionId(void);
Oid BsonValueElemMatchMatchFunctionId(void);
Oid BsonValueAllMatchFunctionId(void);
Oid BsonValueRegexMatchFunctionId(void);
Oid BsonValueModMatchFunctionId(void);
Oid BsonValueBitsAllClearFunctionId(void);
Oid BsonValueBitsAnyClearFunctionId(void);
Oid BsonValueBitsAllSetFunctionId(void);
Oid BsonValueBitsAnySetFunctionId(void);
Oid BsonNotLessThanEqualFunctionId(void);
Oid BsonNotLessThanFunctionId(void);
Oid BsonNotGreaterThanFunctionId(void);
Oid BsonNotGreaterThanEqualFunctionId(void);
Oid BsonIndexBoundsEqualOperatorId(void);
Oid BsonIndexBoundsEqualOperatorFuncId(void);
/* operators */
Oid BigintEqualOperatorId(void);
Oid BigIntGreaterOperatorId(void);
Oid TextEqualOperatorId(void);
Oid TextNotEqualOperatorId(void);
Oid TextLessOperatorId(void);
Oid BsonEqualOperatorId(void);
Oid BsonEqualMatchOperatorId(void);
Oid BsonInOperatorId(void);
Oid BsonQueryOperatorId(void);
Oid BsonTrueFunctionId(void);
Oid BsonQueryMatchFunctionId(void);
Oid BsonIndexSpecSelectionFunctionId(void);
Oid BsonIndexSpecEqualOperatorId(void);
Oid BsonGreaterThanOperatorId(void);
Oid BsonLessThanOperatorId(void);
Oid BsonGreaterThanEqualOperatorId(void);
Oid BsonLessThanEqualOperatorId(void);
Oid BsonGetValueFunctionOid(void);
Oid PostgresInt4PlusFunctionOid(void);
Oid PostgresInt4LessOperatorOid(void);
Oid PostgresInt4LessOperatorFunctionOid(void);
Oid PostgresInt4EqualOperatorOid(void);
/* Opclass */
Oid IntegerOpsOpFamilyOid(void);
Oid BsonBtreeOpFamilyOid(void);
Oid BsonRumCompositeIndexOperatorFamily(void);
/* types */
Oid BsonQueryTypeId(void);
Oid VectorTypeId(void);
Oid HalfVectorTypeId(void);
Oid IndexSpecTypeId(void);
Oid MongoCatalogCollectionsTypeOid(void);
Oid GetClusterBsonQueryTypeId(void);
Oid GetBsonArrayTypeOid(void);
Oid BsonIndexBoundsTypeId(void);
Oid GetBsonIndexBoundsArrayTypeOid(void);
/* sequences */
Oid ApiCatalogCollectionIdSequenceId(void);
Oid ApiCatalogCollectionIndexIdSequenceId(void);
/* order by */
Oid BsonOrderByFunctionOid(void);
Oid BsonOrderByWithCollationFunctionOid(void);
Oid BsonOrderByPartitionFunctionOid(void);
Oid BsonOrderByPartitionWithCollationFunctionOid(void);
Oid BsonOrderByCompareFunctionOId(void);
Oid BsonOrderByLtFunctionOId(void);
Oid BsonOrderByEqFunctionOId(void);
Oid BsonOrderByGtFunctionOId(void);
Oid BsonOrderyByLtOperatorId(void);
Oid BsonOrderyByEqOperatorId(void);
Oid BsonOrderyByGtOperatorId(void);
Oid BsonOrderByIndexOperatorId(void);
/* Postgres internal functions */
Oid PostgresDrandomFunctionId(void);
Oid PostgresToTimestamptzFunctionId(void);
Oid PostgresDatePartFunctionId(void);
Oid PostgresTimestampToZoneFunctionId(void);
Oid PostgresAddIntervalToTimestampFunctionId(void);
Oid PostgresAddIntervalToDateFunctionId(void);
Oid PostgresTimestampToZoneWithoutTzFunctionId(void);
Oid PostgresToDateFunctionId(void);
Oid Float8EqualOperatorId(void);
Oid Float8LessThanEqualOperatorId(void);
Oid Float8GreaterThanEqualOperatorId(void);
Oid PostgresArrayAppendFunctionOid(void);
Oid PostgresMakeIntervalFunctionId(void);
Oid PostgresDateBinFunctionId(void);
Oid PostgresAgeBetweenTimestamp(void);
Oid PostgresDatePartFromInterval(void);
/* Index AM */
Oid RumIndexAmId(void);
Oid PgVectorIvfFlatIndexAmId(void);
Oid PgVectorHNSWIndexAmId(void);
/* IndexAM Support functions */
Oid BsonExclusionPreConsistentFunctionId(void);
/* Operator Class*/
Oid VectorIVFFlatCosineSimilarityOperatorFamilyId(void);
Oid VectorIVFFlatIPSimilarityOperatorFamilyId(void);
Oid VectorIVFFlatL2SimilarityOperatorFamilyId(void);
Oid VectorHNSWCosineSimilarityOperatorFamilyId(void);
Oid VectorHNSWIPSimilarityOperatorFamilyId(void);
Oid VectorHNSWL2SimilarityOperatorFamilyId(void);
Oid BsonRumTextPathOperatorFamily(void);
Oid BsonGistGeographyOperatorFamily(void);
Oid BsonGistGeometryOperatorFamily(void);
Oid BsonRumSinglePathOperatorFamily(void);
Oid Float8PlusOperatorId(void);
Oid Float8MinusOperatorId(void);
Oid Float8MultiplyOperatorId(void);
/* Vector Functions */
Oid PgDoubleToVectorFunctionOid(void);
Oid PgDoubleToSparseVecFunctionOid(bool missingOK);
Oid VectorAsVectorFunctionOid(void);
Oid VectorAsHalfVecFunctionOid(bool missingOK);
Oid ApiCatalogBsonExtractVectorFunctionId(void);
Oid ApiBsonSearchParamFunctionId(void);
Oid ApiBsonDocumentAddScoreFieldFunctionId(void);
/* Vector Operators */
Oid VectorOrderByQueryOperatorId(void);
Oid VectorCosineSimilarityOperatorId(void);
Oid VectorL2SimilarityOperatorId(void);
Oid VectorIPSimilarityOperatorId(void);
Oid VectorCosineSimilarityFunctionId(void);
Oid VectorL2SimilarityFunctionId(void);
Oid VectorIPSimilarityFunctionId(void);
/* Half Vector Operators */
Oid VectorHalfCosineSimilarityOperatorId(void);
Oid VectorHalfL2SimilarityOperatorId(void);
Oid VectorHalfIPSimilarityOperatorId(void);
Oid VectorHalfCosineSimilarityFunctionId(void);
Oid VectorHalfL2SimilarityFunctionId(void);
Oid VectorHalfIPSimilarityFunctionId(void);
/* Geospatial data/type/support functions */
Oid Box2dfTypeId(void);
Oid GeometryTypeId(void);
Oid GeographyTypeId(void);
Oid GIDXTypeId(void);
Oid GeometryArrayTypeId(void);
Oid BsonGeonearDistanceOperatorId(void);
Oid BsonGeonearDistanceRangeOperatorId(void);
Oid BsonDollarGeoIntersectsFunctionOid(void);
Oid BsonDollarGeowithinFunctionOid(void);
Oid BsonValidateGeometryFunctionId(void);
Oid BsonValidateGeographyFunctionId(void);
Oid BsonGistGeographyDistanceFunctionOid(void);
Oid BsonGistGeographyConsistentFunctionOid(void);
Oid PostgisGeometryBufferFunctionId(void);
Oid PostgisGeographyBufferFunctionId(void);
Oid PostgisGeometryAreaFunctionId(void);
Oid PostgisGeometryFromEWKBFunctionId(void);
Oid PostgisGeometryAsGeography(void);
Oid PostgisGeometryIsValidDetailFunctionId(void);
Oid PostgisGeometryMakeValidFunctionId(void);
Oid PostgisGeometryGistCompress2dFunctionId(void);
Oid PostgisGeometryGistConsistent2dFunctionId(void);
Oid PostgisGeographyFromWKBFunctionId(void);
Oid PostgisForcePolygonCWFunctionId(void);
Oid PostgisGeometryAsBinaryFunctionId(void);
Oid PostgisGeographyGistCompressFunctionId(void);
Oid PostgisGeographyGistConsistentFunctionId(void);
Oid PostgisMakeEnvelopeFunctionId(void);
Oid PostgisMakePointFunctionId(void);
Oid PostgisMakePolygonFunctionId(void);
Oid PostgisMakeLineFunctionId(void);
Oid PostgisGeographyCoversFunctionId(void);
Oid PostgisGeographyDWithinFunctionId(void);
Oid PostgisGeometryDistanceCentroidFunctionId(void);
Oid PostgisGeographyDistanceKNNFunctionId(void);
Oid PostgisGeometryGistDistanceFunctionId(void);
Oid PostgisGeographyGistDistanceFunctionId(void);
Oid PostgisGeometryDWithinFunctionId(void);
Oid PostgisBox2dfGeometryOverlapsFunctionId(void);
Oid PostgisGIDXGeographyOverlapsFunctionId(void);
Oid PostgisGeometryCoversFunctionId(void);
Oid PostgisGeographyIntersectsFunctionId(void);
Oid PostgisGeometryIntersectsFunctionId(void);
Oid PostgisSetSRIDFunctionId(void);
Oid PostgisGeometryExpandFunctionId(void);
Oid PostgisGeographyExpandFunctionId(void);
/* Text search functions */
Oid WebSearchToTsQueryFunctionId(void);
Oid WebSearchToTsQueryWithRegConfigFunctionId(void);
Oid RumExtractTsVectorFunctionId(void);
Oid BsonTextSearchMetaQualFuncId(void);
Oid TsRankFunctionId(void);
Oid TsVectorConcatFunctionId(void);
Oid TsMatchFunctionOid(void);
/* Aggregation functions */
Oid ApiCatalogAggregationPipelineFunctionId(void);
Oid ApiCatalogAggregationFindFunctionId(void);
Oid ApiCatalogAggregationCountFunctionId(void);
Oid ApiCatalogAggregationDistinctFunctionId(void);
Oid BsonCovariancePopAggregateFunctionOid(void);
Oid BsonCovarianceSampAggregateFunctionOid(void);
Oid BsonDollarAddFieldsFunctionOid(void);
Oid BsonDollarAddFieldsWithLetFunctionOid(void);
Oid BsonDollarAddFieldsWithLetAndCollationFunctionOid(void);
Oid BsonDollarMergeDocumentAtPathFunctionOid(void);
Oid BsonDollaMergeDocumentsFunctionOid(void);
Oid BsonDollarProjectGeonearFunctionOid(void);
Oid BsonDollarInverseMatchFunctionId(void);
Oid BsonDollarProjectFunctionOid(void);
Oid BsonDollarProjectWithLetFunctionOid(void);
Oid BsonDollarProjectWithLetAndCollationFunctionOid(void);
Oid BsonDollarRedactWithLetFunctionOid(void);
Oid BsonDollarRedactWithLetAndCollationFunctionOid(void);
Oid BsonDollarMergeHandleWhenMatchedFunctionOid(void);
Oid BsonDollarMergeAddObjectIdFunctionOid(void);
Oid BsonDollarMergeGenerateObjectId(void);
Oid BsonDollarMergeFailWhenNotMatchedFunctionOid(void);
Oid BsonDollarMergeExtractFilterFunctionOid(void);
Oid BsonDollarMergeJoinFunctionOid(void);
Oid BsonDollarProjectFindFunctionOid(void);
Oid BsonDollarProjectFindWithLetFunctionOid(void);
Oid BsonDollarProjectFindWithLetAndCollationFunctionOid(void);
Oid BsonDollarUnwindFunctionOid(void);
Oid BsonDollarUnwindWithOptionsFunctionOid(void);
Oid BsonDollarReplaceRootFunctionOid(void);
Oid BsonDollarReplaceRootWithLetFunctionOid(void);
Oid BsonDollarReplaceRootWithLetAndCollationFunctionOid(void);
Oid BsonSumAggregateFunctionOid(void);
Oid BsonIntegralAggregateFunctionOid(void);
Oid BsonDerivativeAggregateFunctionOid(void);
Oid BsonAvgAggregateFunctionOid(void);
Oid BsonRepathAndBuildFunctionOid(void);
Oid BsonExpressionGetFunctionOid(void);
Oid BsonExpressionGetWithLetFunctionOid(void);
Oid BsonExpressionGetWithLetAndCollationFunctionOid(void);
Oid BsonExpressionPartitionGetFunctionOid(void);
Oid BsonExpressionPartitionByFieldsGetFunctionOid(void);
Oid BsonExpressionPartitionGetWithLetFunctionOid(void);
Oid BsonExpressionPartitionGetWithLetAndCollationFunctionOid(void);
Oid BsonExpressionMapFunctionOid(void);
Oid BsonExpressionMapWithLetFunctionOid(void);
Oid BsonExpressionAppendCollationFunctionOid(void);
Oid BsonMaxAggregateFunctionOid(void);
Oid BsonMinAggregateFunctionOid(void);
Oid PgRandomFunctionOid(void);
Oid BsonArrayAggregateFunctionOid(void);
Oid BsonArrayAggregateAllArgsFunctionOid(void);
Oid BsonExpMovingAvgAggregateFunctionOid(void);
Oid BsonObjectAggregateFunctionOid(void);
Oid BsonMergeObjectsOnSortedFunctionOid(void);
Oid BsonMergeObjectsFunctionOid(void);
Oid BsonDollarFacetProjectFunctionOid(void);
Oid BsonFirstOnSortedAggregateFunctionOid(void);
Oid BsonFirstOnSortedAggregateAllArgsFunctionOid(void);
Oid BsonLastOnSortedAggregateFunctionOid(void);
Oid BsonLastOnSortedAggregateAllArgsFunctionOid(void);
Oid BsonFirstAggregateFunctionOid(void);
Oid BsonFirstAggregateAllArgsFunctionOid(void);
Oid BsonLastAggregateFunctionOid(void);
Oid BsonLastAggregateAllArgsFunctionOid(void);
Oid BsonFirstNAggregateFunctionOid(void);
Oid BsonFirstNAggregateAllArgsFunctionOid(void);
Oid BsonFirstNOnSortedAggregateFunctionOid(void);
Oid BsonFirstNOnSortedAggregateAllArgsFunctionOid(void);
Oid BsonLastNAggregateFunctionOid(void);
Oid BsonLastNAggregateAllArgsFunctionOid(void);
Oid BsonLastNOnSortedAggregateFunctionOid(void);
Oid BsonLastNOnSortedAggregateAllArgsFunctionOid(void);
Oid BsonAddToSetAggregateFunctionOid(void);
Oid BsonStdDevPopAggregateFunctionOid(void);
Oid BsonStdDevSampAggregateFunctionOid(void);
Oid PostgresAnyValueFunctionOid(void);
Oid BsonLookupExtractFilterExpressionFunctionOid(void);
Oid BsonDollarLookupExpressionEvalMergeOid(void);
Oid DocumentDBApiInternalBsonLookupExtractFilterExpressionFunctionOid(void);
Oid BsonDollarLookupJoinFilterFunctionOid(void);
Oid BsonLookupExtractFilterArrayFunctionOid(void);
Oid BsonLookupUnwindFunctionOid(void);
Oid BsonDistinctUnwindFunctionOid(void);
Oid BsonDollarBucketAutoFunctionOid(void);
Oid BsonDistinctAggregateFunctionOid(void);
Oid RowGetBsonFunctionOid(void);
Oid ApiChangeStreamAggregationFunctionOid(void);
Oid ApiCollStatsAggregationFunctionOid(void);
Oid ApiIndexStatsAggregationFunctionOid(void);
Oid BsonCurrentOpAggregationFunctionId(void);
Oid BsonMaxNAggregateFunctionOid(void);
Oid BsonMinNAggregateFunctionOid(void);
Oid BsonMedianAggregateFunctionOid(void);
Oid BsonPercentileAggregateFunctionOid(void);
/* Window functions*/
Oid BsonLinearFillFunctionOid(void);
Oid BsonLocfFillFunctionOid(void);
Oid BsonConstFillFunctionOid(void);
Oid BsonRankFunctionOid(void);
Oid BsonDenseRankFunctionOid(void);
Oid BsonShiftFunctionOid(void);
Oid BsonDocumentNumberFunctionOid(void);
/* Window functions */
Oid BsonDensifyRangeWindowFunctionOid(void);
Oid BsonDensifyPartitionWindowFunctionOid(void);
Oid BsonDensifyFullWindowFunctionOid(void);
/* Catalog */
Oid ApiDataNamespaceOid(void);
/* CRUD functions */
Oid UpdateWorkerFunctionOid(void);
Oid InsertWorkerFunctionOid(void);
Oid DeleteWorkerFunctionOid(void);
/* Compat functions*/
Oid DocumentDBCoreBsonToBsonFunctionOId(void);
/* Helper functions */
TypeName * ParseTypeNameCore(const char *typeName);
#endif