diff options
Diffstat (limited to 'src')
58 files changed, 1343 insertions, 561 deletions
diff --git a/src/backend/access/nbtree/README b/src/backend/access/nbtree/README index 53fda66572f..34fb90e4363 100644 --- a/src/backend/access/nbtree/README +++ b/src/backend/access/nbtree/README @@ -1,4 +1,4 @@ -$PostgreSQL: pgsql/src/backend/access/nbtree/README,v 1.15 2006/12/28 23:16:39 tgl Exp $ +$PostgreSQL: pgsql/src/backend/access/nbtree/README,v 1.16 2007/01/09 02:14:10 tgl Exp $ This directory contains a correct implementation of Lehman and Yao's high-concurrency B-tree management algorithm (P. Lehman and S. Yao, @@ -483,5 +483,6 @@ Notes to operator class implementors With this implementation, we require each supported combination of datatypes to supply us with a comparison procedure via pg_amproc. This procedure must take two nonnull values A and B and return an int32 < 0, -0, or > 0 if A < B, A = B, or A > B, respectively. See nbtcompare.c for -examples. +0, or > 0 if A < B, A = B, or A > B, respectively. The procedure must +not return INT_MIN for "A < B", since the value may be negated before +being tested for sign. See nbtcompare.c for examples. diff --git a/src/backend/access/nbtree/nbtcompare.c b/src/backend/access/nbtree/nbtcompare.c index 27caa8495cf..97489c60368 100644 --- a/src/backend/access/nbtree/nbtcompare.c +++ b/src/backend/access/nbtree/nbtcompare.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.53 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.54 2007/01/09 02:14:10 tgl Exp $ * * NOTES * @@ -22,10 +22,10 @@ * * The result is always an int32 regardless of the input datatype. * - * NOTE: although any negative int32 is acceptable for reporting "<", - * and any positive int32 is acceptable for reporting ">", routines + * Although any negative int32 (except INT_MIN) is acceptable for reporting + * "<", and any positive int32 is acceptable for reporting ">", routines * that work on 32-bit or wider datatypes can't just return "a - b". - * That could overflow and give the wrong answer. Also, one should not + * That could overflow and give the wrong answer. Also, one must not * return INT_MIN to report "<", since some callers will negate the result. * * NOTE: it is critical that the comparison function impose a total order diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 2da75f547c7..fc8b18a2e90 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsearch.c,v 1.110 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsearch.c,v 1.111 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -376,12 +376,17 @@ _bt_compare(Relation rel, { if (isNull) result = 0; /* NULL "=" NULL */ + else if (scankey->sk_flags & SK_BT_NULLS_FIRST) + result = -1; /* NULL "<" NOT_NULL */ else result = 1; /* NULL ">" NOT_NULL */ } else if (isNull) /* key is NOT_NULL and item is NULL */ { - result = -1; /* NOT_NULL "<" NULL */ + if (scankey->sk_flags & SK_BT_NULLS_FIRST) + result = 1; /* NOT_NULL ">" NULL */ + else + result = -1; /* NOT_NULL "<" NULL */ } else { @@ -390,16 +395,15 @@ _bt_compare(Relation rel, * the sk_argument as right arg (they might be of different * types). Since it is convenient for callers to think of * _bt_compare as comparing the scankey to the index item, we have - * to flip the sign of the comparison result. - * - * Note: curious-looking coding is to avoid overflow if comparison - * function returns INT_MIN. There is no risk of overflow for - * positive results. + * to flip the sign of the comparison result. (Unless it's a DESC + * column, in which case we *don't* flip the sign.) */ result = DatumGetInt32(FunctionCall2(&scankey->sk_func, datum, scankey->sk_argument)); - result = (result < 0) ? 1 : -result; + + if (!(scankey->sk_flags & SK_BT_DESC)) + result = -result; } /* if the keys are unequal, return the difference */ @@ -617,11 +621,12 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * in the first row member makes the condition unmatchable, just * like qual_ok = false. */ - cur = (ScanKey) DatumGetPointer(cur->sk_argument); - Assert(cur->sk_flags & SK_ROW_MEMBER); - if (cur->sk_flags & SK_ISNULL) + ScanKey subkey = (ScanKey) DatumGetPointer(cur->sk_argument); + + Assert(subkey->sk_flags & SK_ROW_MEMBER); + if (subkey->sk_flags & SK_ISNULL) return false; - memcpy(scankeys + i, cur, sizeof(ScanKeyData)); + memcpy(scankeys + i, subkey, sizeof(ScanKeyData)); /* * If the row comparison is the last positioning key we accepted, @@ -632,21 +637,46 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * even if the row comparison is of ">" or "<" type, because the * condition applied to all but the last row member is effectively * ">=" or "<=", and so the extra keys don't break the positioning - * scheme. + * scheme. But, by the same token, if we aren't able to use all + * the row members, then the part of the row comparison that we + * did use has to be treated as just a ">=" or "<=" condition, + * and so we'd better adjust strat_total accordingly. */ if (i == keysCount - 1) { - while (!(cur->sk_flags & SK_ROW_END)) + bool used_all_subkeys = false; + + Assert(!(subkey->sk_flags & SK_ROW_END)); + for(;;) { - cur++; - Assert(cur->sk_flags & SK_ROW_MEMBER); - if (cur->sk_attno != keysCount + 1) + subkey++; + Assert(subkey->sk_flags & SK_ROW_MEMBER); + if (subkey->sk_attno != keysCount + 1) break; /* out-of-sequence, can't use it */ - if (cur->sk_flags & SK_ISNULL) + if (subkey->sk_strategy != cur->sk_strategy) + break; /* wrong direction, can't use it */ + if (subkey->sk_flags & SK_ISNULL) break; /* can't use null keys */ Assert(keysCount < INDEX_MAX_KEYS); - memcpy(scankeys + keysCount, cur, sizeof(ScanKeyData)); + memcpy(scankeys + keysCount, subkey, sizeof(ScanKeyData)); keysCount++; + if (subkey->sk_flags & SK_ROW_END) + { + used_all_subkeys = true; + break; + } + } + if (!used_all_subkeys) + { + switch (strat_total) + { + case BTLessStrategyNumber: + strat_total = BTLessEqualStrategyNumber; + break; + case BTGreaterStrategyNumber: + strat_total = BTGreaterEqualStrategyNumber; + break; + } } break; /* done with outer loop */ } diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c index 945f20729c0..a917e27a765 100644 --- a/src/backend/access/nbtree/nbtsort.c +++ b/src/backend/access/nbtree/nbtsort.c @@ -57,7 +57,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.109 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.110 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -728,39 +728,45 @@ _bt_load(BTWriteState *wstate, BTSpool *btspool, BTSpool *btspool2) ScanKey entry; Datum attrDatum1, attrDatum2; - bool isFirstNull, - isSecondNull; + bool isNull1, + isNull2; + int32 compare; entry = indexScanKey + i - 1; - attrDatum1 = index_getattr(itup, i, tupdes, - &isFirstNull); - attrDatum2 = index_getattr(itup2, i, tupdes, - &isSecondNull); - if (isFirstNull) + attrDatum1 = index_getattr(itup, i, tupdes, &isNull1); + attrDatum2 = index_getattr(itup2, i, tupdes, &isNull2); + if (isNull1) { - if (!isSecondNull) - { - load1 = false; - break; - } + if (isNull2) + compare = 0; /* NULL "=" NULL */ + else if (entry->sk_flags & SK_BT_NULLS_FIRST) + compare = -1; /* NULL "<" NOT_NULL */ + else + compare = 1; /* NULL ">" NOT_NULL */ + } + else if (isNull2) + { + if (entry->sk_flags & SK_BT_NULLS_FIRST) + compare = 1; /* NOT_NULL ">" NULL */ + else + compare = -1; /* NOT_NULL "<" NULL */ } - else if (isSecondNull) - break; else { - int32 compare; - compare = DatumGetInt32(FunctionCall2(&entry->sk_func, attrDatum1, attrDatum2)); - if (compare > 0) - { - load1 = false; - break; - } - else if (compare < 0) - break; + + if (entry->sk_flags & SK_BT_DESC) + compare = -compare; } + if (compare > 0) + { + load1 = false; + break; + } + else if (compare < 0) + break; } } else diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c index d82a93a63c5..d453a93cafa 100644 --- a/src/backend/access/nbtree/nbtutils.c +++ b/src/backend/access/nbtree/nbtutils.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.81 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.82 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -30,6 +30,7 @@ static bool _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, ScanKey leftarg, ScanKey rightarg, bool *result); +static void _bt_mark_scankey_with_indoption(ScanKey skey, int16 *indoption); static void _bt_mark_scankey_required(ScanKey skey); static bool _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, @@ -49,10 +50,12 @@ _bt_mkscankey(Relation rel, IndexTuple itup) ScanKey skey; TupleDesc itupdesc; int natts; + int16 *indoption; int i; itupdesc = RelationGetDescr(rel); natts = RelationGetNumberOfAttributes(rel); + indoption = rel->rd_indoption; skey = (ScanKey) palloc(natts * sizeof(ScanKeyData)); @@ -61,6 +64,7 @@ _bt_mkscankey(Relation rel, IndexTuple itup) FmgrInfo *procinfo; Datum arg; bool null; + int flags; /* * We can use the cached (default) support procs since no cross-type @@ -68,8 +72,9 @@ _bt_mkscankey(Relation rel, IndexTuple itup) */ procinfo = index_getprocinfo(rel, i + 1, BTORDER_PROC); arg = index_getattr(itup, i + 1, itupdesc, &null); + flags = (null ? SK_ISNULL : 0) | (indoption[i] << SK_BT_INDOPTION_SHIFT); ScanKeyEntryInitializeWithInfo(&skey[i], - null ? SK_ISNULL : 0, + flags, (AttrNumber) (i + 1), InvalidStrategy, InvalidOid, @@ -96,23 +101,27 @@ _bt_mkscankey_nodata(Relation rel) { ScanKey skey; int natts; + int16 *indoption; int i; natts = RelationGetNumberOfAttributes(rel); + indoption = rel->rd_indoption; skey = (ScanKey) palloc(natts * sizeof(ScanKeyData)); for (i = 0; i < natts; i++) { FmgrInfo *procinfo; + int flags; /* * We can use the cached (default) support procs since no cross-type * comparison can be needed. */ procinfo = index_getprocinfo(rel, i + 1, BTORDER_PROC); + flags = SK_ISNULL | (indoption[i] << SK_BT_INDOPTION_SHIFT); ScanKeyEntryInitializeWithInfo(&skey[i], - SK_ISNULL, + flags, (AttrNumber) (i + 1), InvalidStrategy, InvalidOid, @@ -157,7 +166,13 @@ _bt_freestack(BTStack stack) * the number of input keys, so->numberOfKeys gets the number of output * keys (possibly less, never greater). * - * The primary purpose of this routine is to discover how many scan keys + * The output keys are marked with additional sk_flag bits beyond the + * system-standard bits supplied by the caller. The DESC and NULLS_FIRST + * indoption bits for the relevant index attribute are copied into the flags. + * Also, for a DESC column, we commute (flip) all the sk_strategy numbers + * so that the index sorts in the desired direction. + * + * One key purpose of this routine is to discover how many scan keys * must be satisfied to continue the scan. It also attempts to eliminate * redundant keys and detect contradictory keys. (If the index opfamily * provides incomplete sets of cross-type operators, we may fail to detect @@ -219,6 +234,7 @@ _bt_preprocess_keys(IndexScanDesc scan) { BTScanOpaque so = (BTScanOpaque) scan->opaque; int numberOfKeys = scan->numberOfKeys; + int16 *indoption = scan->indexRelation->rd_indoption; int new_numberOfKeys; int numberOfEqualCols; ScanKey inkeys; @@ -254,7 +270,8 @@ _bt_preprocess_keys(IndexScanDesc scan) */ if (cur->sk_flags & SK_ISNULL) so->qual_ok = false; - memcpy(outkeys, inkeys, sizeof(ScanKeyData)); + _bt_mark_scankey_with_indoption(cur, indoption); + memcpy(outkeys, cur, sizeof(ScanKeyData)); so->numberOfKeys = 1; /* We can mark the qual as required if it's for first index col */ if (cur->sk_attno == 1) @@ -407,6 +424,9 @@ _bt_preprocess_keys(IndexScanDesc scan) memset(xform, 0, sizeof(xform)); } + /* apply indoption to scankey (might change sk_strategy!) */ + _bt_mark_scankey_with_indoption(cur, indoption); + /* check strategy this key's operator corresponds to */ j = cur->sk_strategy - 1; @@ -486,6 +506,11 @@ _bt_preprocess_keys(IndexScanDesc scan) * Note: op always points at the same ScanKey as either leftarg or rightarg. * Since we don't scribble on the scankeys, this aliasing should cause no * trouble. + * + * Note: this routine needs to be insensitive to any DESC option applied + * to the index column. For example, "x < 4" is a tighter constraint than + * "x < 5" regardless of which way the index is sorted. We don't worry about + * NULLS FIRST/LAST either, since the given values are never nulls. */ static bool _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, @@ -498,6 +523,7 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, optype, opcintype, cmp_op; + StrategyNumber strat; /* * The opfamily we need to worry about is identified by the index column. @@ -538,11 +564,18 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, * operator. (This cannot result in infinite recursion, since no * indexscan initiated by syscache lookup will use cross-data-type * operators.) + * + * If the sk_strategy was flipped by _bt_mark_scankey_with_indoption, + * we have to un-flip it to get the correct opfamily member. */ + strat = op->sk_strategy; + if (op->sk_flags & SK_BT_DESC) + strat = BTCommuteStrategyNumber(strat); + cmp_op = get_opfamily_member(rel->rd_opfamily[leftarg->sk_attno - 1], lefttype, righttype, - op->sk_strategy); + strat); if (OidIsValid(cmp_op)) { RegProcedure cmp_proc = get_opcode(cmp_op); @@ -562,13 +595,56 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, } /* + * Mark a scankey with info from the index's indoption array. + * + * We copy the appropriate indoption value into the scankey sk_flags + * (shifting to avoid clobbering system-defined flag bits). Also, if + * the DESC option is set, commute (flip) the operator strategy number. + * + * This function is applied to the *input* scankey structure; therefore + * on a rescan we will be looking at already-processed scankeys. Hence + * we have to be careful not to re-commute the strategy if we already did it. + * It's a bit ugly to modify the caller's copy of the scankey but in practice + * there shouldn't be any problem, since the index's indoptions are certainly + * not going to change while the scankey survives. + */ +static void +_bt_mark_scankey_with_indoption(ScanKey skey, int16 *indoption) +{ + int addflags; + + addflags = indoption[skey->sk_attno - 1] << SK_BT_INDOPTION_SHIFT; + if ((addflags & SK_BT_DESC) && !(skey->sk_flags & SK_BT_DESC)) + skey->sk_strategy = BTCommuteStrategyNumber(skey->sk_strategy); + skey->sk_flags |= addflags; + + if (skey->sk_flags & SK_ROW_HEADER) + { + ScanKey subkey = (ScanKey) DatumGetPointer(skey->sk_argument); + + for (;;) + { + Assert(subkey->sk_flags & SK_ROW_MEMBER); + addflags = indoption[subkey->sk_attno - 1] << SK_BT_INDOPTION_SHIFT; + if ((addflags & SK_BT_DESC) && !(subkey->sk_flags & SK_BT_DESC)) + subkey->sk_strategy = BTCommuteStrategyNumber(subkey->sk_strategy); + subkey->sk_flags |= addflags; + if (subkey->sk_flags & SK_ROW_END) + break; + subkey++; + } + } +} + +/* * Mark a scankey as "required to continue the scan". * * Depending on the operator type, the key may be required for both scan * directions or just one. Also, if the key is a row comparison header, * we have to mark the appropriate subsidiary ScanKeys as required. In * such cases, the first subsidiary key is required, but subsequent ones - * are required only as long as they correspond to successive index columns. + * are required only as long as they correspond to successive index columns + * and match the leading column as to sort direction. * Otherwise the row comparison ordering is different from the index ordering * and so we can't stop the scan on the basis of those lower-order columns. * @@ -616,9 +692,10 @@ _bt_mark_scankey_required(ScanKey skey) for (;;) { Assert(subkey->sk_flags & SK_ROW_MEMBER); - Assert(subkey->sk_strategy == skey->sk_strategy); if (subkey->sk_attno != attno) break; /* non-adjacent key, so not required */ + if (subkey->sk_strategy != skey->sk_strategy) + break; /* wrong direction, so not required */ subkey->sk_flags |= addflags; if (subkey->sk_flags & SK_ROW_END) break; @@ -731,15 +808,32 @@ _bt_checkkeys(IndexScanDesc scan, if (isNull) { - /* - * Since NULLs are sorted after non-NULLs, we know we have reached - * the upper limit of the range of values for this index attr. On - * a forward scan, we can stop if this qual is one of the "must - * match" subset. On a backward scan, however, we should keep - * going. - */ - if ((key->sk_flags & SK_BT_REQFWD) && ScanDirectionIsForward(dir)) - *continuescan = false; + if (key->sk_flags & SK_BT_NULLS_FIRST) + { + /* + * Since NULLs are sorted before non-NULLs, we know we have + * reached the lower limit of the range of values for this + * index attr. On a backward scan, we can stop if this qual is + * one of the "must match" subset. On a forward scan, + * however, we should keep going. + */ + if ((key->sk_flags & SK_BT_REQBKWD) && + ScanDirectionIsBackward(dir)) + *continuescan = false; + } + else + { + /* + * Since NULLs are sorted after non-NULLs, we know we have + * reached the upper limit of the range of values for this + * index attr. On a forward scan, we can stop if this qual is + * one of the "must match" subset. On a backward scan, + * however, we should keep going. + */ + if ((key->sk_flags & SK_BT_REQFWD) && + ScanDirectionIsForward(dir)) + *continuescan = false; + } /* * In any case, this indextuple doesn't match the qual. @@ -809,7 +903,6 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, bool isNull; Assert(subkey->sk_flags & SK_ROW_MEMBER); - Assert(subkey->sk_strategy == skey->sk_strategy); datum = index_getattr(tuple, subkey->sk_attno, @@ -818,16 +911,32 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, if (isNull) { - /* - * Since NULLs are sorted after non-NULLs, we know we have reached - * the upper limit of the range of values for this index attr. On - * a forward scan, we can stop if this qual is one of the "must - * match" subset. On a backward scan, however, we should keep - * going. - */ - if ((subkey->sk_flags & SK_BT_REQFWD) && - ScanDirectionIsForward(dir)) - *continuescan = false; + if (subkey->sk_flags & SK_BT_NULLS_FIRST) + { + /* + * Since NULLs are sorted before non-NULLs, we know we have + * reached the lower limit of the range of values for this + * index attr. On a backward scan, we can stop if this qual is + * one of the "must match" subset. On a forward scan, + * however, we should keep going. + */ + if ((subkey->sk_flags & SK_BT_REQBKWD) && + ScanDirectionIsBackward(dir)) + *continuescan = false; + } + else + { + /* + * Since NULLs are sorted after non-NULLs, we know we have + * reached the upper limit of the range of values for this + * index attr. On a forward scan, we can stop if this qual is + * one of the "must match" subset. On a backward scan, + * however, we should keep going. + */ + if ((subkey->sk_flags & SK_BT_REQFWD) && + ScanDirectionIsForward(dir)) + *continuescan = false; + } /* * In any case, this indextuple doesn't match the qual. @@ -859,6 +968,9 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, datum, subkey->sk_argument)); + if (subkey->sk_flags & SK_BT_DESC) + cmpresult = -cmpresult; + /* Done comparing if unequal, else advance to next column */ if (cmpresult != 0) break; diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 7cc6100c2a8..2e17fb7b2b0 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.85 2007/01/05 22:19:24 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.86 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -312,6 +312,8 @@ boot_index_param: n->name = LexIDStr($1); n->expr = NULL; n->opclass = list_make1(makeString(LexIDStr($2))); + n->ordering = SORTBY_DEFAULT; + n->nulls_ordering = SORTBY_NULLS_DEFAULT; $$ = n; } ; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 25f1f8a16ea..d75efdcc845 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.275 2007/01/05 22:19:24 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.276 2007/01/09 02:14:11 tgl Exp $ * * * INTERFACE ROUTINES @@ -73,6 +73,7 @@ static void AppendAttributeTuples(Relation indexRelation, int numatts); static void UpdateIndexRelation(Oid indexoid, Oid heapoid, IndexInfo *indexInfo, Oid *classOids, + int16 *coloptions, bool primary, bool isvalid); static void index_update_stats(Relation rel, bool hasindex, bool isprimary, @@ -336,11 +337,13 @@ UpdateIndexRelation(Oid indexoid, Oid heapoid, IndexInfo *indexInfo, Oid *classOids, + int16 *coloptions, bool primary, bool isvalid) { int2vector *indkey; oidvector *indclass; + int2vector *indoption; Datum exprsDatum; Datum predDatum; Datum values[Natts_pg_index]; @@ -350,13 +353,14 @@ UpdateIndexRelation(Oid indexoid, int i; /* - * Copy the index key and opclass info into arrays (should we make the - * caller pass them like this to start with?) + * Copy the index key, opclass, and indoption info into arrays (should we + * make the caller pass them like this to start with?) */ indkey = buildint2vector(NULL, indexInfo->ii_NumIndexAttrs); - indclass = buildoidvector(classOids, indexInfo->ii_NumIndexAttrs); for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) indkey->values[i] = indexInfo->ii_KeyAttrNumbers[i]; + indclass = buildoidvector(classOids, indexInfo->ii_NumIndexAttrs); + indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexAttrs); /* * Convert the index expressions (if any) to a text datum @@ -408,6 +412,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indisvalid - 1] = BoolGetDatum(isvalid); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); + values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption); values[Anum_pg_index_indexprs - 1] = exprsDatum; if (exprsDatum == (Datum) 0) nulls[Anum_pg_index_indexprs - 1] = 'n'; @@ -445,6 +450,7 @@ UpdateIndexRelation(Oid indexoid, * accessMethodObjectId: OID of index AM to use * tableSpaceId: OID of tablespace to use * classObjectId: array of index opclass OIDs, one per index column + * coloptions: array of per-index-column indoption settings * reloptions: AM-specific options * isprimary: index is a PRIMARY KEY * isconstraint: index is owned by a PRIMARY KEY or UNIQUE constraint @@ -465,6 +471,7 @@ index_create(Oid heapRelationId, Oid accessMethodObjectId, Oid tableSpaceId, Oid *classObjectId, + int16 *coloptions, Datum reloptions, bool isprimary, bool isconstraint, @@ -618,7 +625,7 @@ index_create(Oid heapRelationId, * ---------------- */ UpdateIndexRelation(indexRelationId, heapRelationId, indexInfo, - classObjectId, isprimary, !concurrent); + classObjectId, coloptions, isprimary, !concurrent); /* * Register constraint and dependencies for the index. @@ -1639,7 +1646,7 @@ validate_index(Oid heapId, Oid indexId, Snapshot snapshot) ivinfo.num_heap_tuples = -1; state.tuplesort = tuplesort_begin_datum(TIDOID, - TIDLessOperator, + TIDLessOperator, false, maintenance_work_mem, false); state.htups = state.itups = state.tups_inserted = 0; diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index ce65fda5d17..47f5e25fd20 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.4 2007/01/05 22:19:25 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.5 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -112,6 +112,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) char toast_idxname[NAMEDATALEN]; IndexInfo *indexInfo; Oid classObjectId[2]; + int16 coloptions[2]; ObjectAddress baseobject, toastobject; @@ -223,11 +224,14 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) classObjectId[0] = OID_BTREE_OPS_OID; classObjectId[1] = INT4_BTREE_OPS_OID; + coloptions[0] = 0; + coloptions[1] = 0; + toast_idxid = index_create(toast_relid, toast_idxname, toastIndexOid, indexInfo, BTREE_AM_OID, rel->rd_rel->reltablespace, - classObjectId, (Datum) 0, + classObjectId, coloptions, (Datum) 0, true, false, true, false, false); /* diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 102be0d96fe..c568f04284c 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.102 2007/01/05 22:19:25 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.103 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1297,7 +1297,7 @@ typedef struct typedef struct { FmgrInfo *cmpFn; - SortFunctionKind cmpFnKind; + int cmpFlags; int *tupnoLink; } CompareScalarsContext; @@ -1747,8 +1747,8 @@ compute_scalar_stats(VacAttrStatsP stats, bool is_varwidth = (!stats->attr->attbyval && stats->attr->attlen < 0); double corr_xysum; - RegProcedure cmpFn; - SortFunctionKind cmpFnKind; + Oid cmpFn; + int cmpFlags; FmgrInfo f_cmpfn; ScalarItem *values; int values_cnt = 0; @@ -1763,7 +1763,7 @@ compute_scalar_stats(VacAttrStatsP stats, tupnoLink = (int *) palloc(samplerows * sizeof(int)); track = (ScalarMCVItem *) palloc(num_mcv * sizeof(ScalarMCVItem)); - SelectSortFunction(mystats->ltopr, &cmpFn, &cmpFnKind); + SelectSortFunction(mystats->ltopr, false, &cmpFn, &cmpFlags); fmgr_info(cmpFn, &f_cmpfn); /* Initial scan to find sortable values */ @@ -1833,7 +1833,7 @@ compute_scalar_stats(VacAttrStatsP stats, /* Sort the collected values */ cxt.cmpFn = &f_cmpfn; - cxt.cmpFnKind = cmpFnKind; + cxt.cmpFlags = cmpFlags; cxt.tupnoLink = tupnoLink; qsort_arg((void *) values, values_cnt, sizeof(ScalarItem), compare_scalars, (void *) &cxt); @@ -2203,7 +2203,7 @@ compare_scalars(const void *a, const void *b, void *arg) CompareScalarsContext *cxt = (CompareScalarsContext *) arg; int32 compare; - compare = ApplySortFunction(cxt->cmpFn, cxt->cmpFnKind, + compare = ApplySortFunction(cxt->cmpFn, cxt->cmpFlags, da, false, db, false); if (compare != 0) return compare; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index e35d6479db6..bbaa34f758a 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.151 2007/01/05 22:19:26 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.152 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -49,10 +49,13 @@ /* non-export function prototypes */ static void CheckPredicate(Expr *predicate); -static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid *classOidP, +static void ComputeIndexAttrs(IndexInfo *indexInfo, + Oid *classOidP, + int16 *colOptionP, List *attList, Oid relId, char *accessMethodName, Oid accessMethodId, + bool amcanorder, bool isconstraint); static Oid GetIndexOpClass(List *opclass, Oid attrType, char *accessMethodName, Oid accessMethodId); @@ -115,8 +118,10 @@ DefineIndex(RangeVar *heapRelation, Relation rel; HeapTuple tuple; Form_pg_am accessMethodForm; + bool amcanorder; RegProcedure amoptions; Datum reloptions; + int16 *coloptions; IndexInfo *indexInfo; int numberOfAttributes; List *old_xact_list; @@ -290,6 +295,8 @@ DefineIndex(RangeVar *heapRelation, errmsg("access method \"%s\" does not support multicolumn indexes", accessMethodName))); + amcanorder = (accessMethodForm->amorderstrategy > 0); + amoptions = accessMethodForm->amoptions; ReleaseSysCache(tuple); @@ -419,9 +426,10 @@ DefineIndex(RangeVar *heapRelation, indexInfo->ii_Concurrent = concurrent; classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); - ComputeIndexAttrs(indexInfo, classObjectId, attributeList, + coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16)); + ComputeIndexAttrs(indexInfo, classObjectId, coloptions, attributeList, relationId, accessMethodName, accessMethodId, - isconstraint); + amcanorder, isconstraint); heap_close(rel, NoLock); @@ -439,7 +447,7 @@ DefineIndex(RangeVar *heapRelation, indexRelationId = index_create(relationId, indexRelationName, indexRelationId, indexInfo, accessMethodId, tablespaceId, classObjectId, - reloptions, primary, isconstraint, + coloptions, reloptions, primary, isconstraint, allowSystemTableMods, skip_build, concurrent); if (!concurrent) @@ -585,13 +593,19 @@ CheckPredicate(Expr *predicate) errmsg("functions in index predicate must be marked IMMUTABLE"))); } +/* + * Compute per-index-column information, including indexed column numbers + * or index expressions, opclasses, and indoptions. + */ static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid *classOidP, + int16 *colOptionP, List *attList, /* list of IndexElem's */ Oid relId, char *accessMethodName, Oid accessMethodId, + bool amcanorder, bool isconstraint) { ListCell *rest; @@ -605,6 +619,9 @@ ComputeIndexAttrs(IndexInfo *indexInfo, IndexElem *attribute = (IndexElem *) lfirst(rest); Oid atttype; + /* + * Process the column-or-expression to be indexed. + */ if (attribute->name != NULL) { /* Simple index attribute */ @@ -674,10 +691,49 @@ ComputeIndexAttrs(IndexInfo *indexInfo, errmsg("functions in index expression must be marked IMMUTABLE"))); } + /* + * Identify the opclass to use. + */ classOidP[attn] = GetIndexOpClass(attribute->opclass, atttype, accessMethodName, accessMethodId); + + /* + * Set up the per-column options (indoption field). For now, this + * is zero for any un-ordered index, while ordered indexes have DESC + * and NULLS FIRST/LAST options. + */ + colOptionP[attn] = 0; + if (amcanorder) + { + /* default ordering is ASC */ + if (attribute->ordering == SORTBY_DESC) + colOptionP[attn] |= INDOPTION_DESC; + /* default null ordering is LAST for ASC, FIRST for DESC */ + if (attribute->nulls_ordering == SORTBY_NULLS_DEFAULT) + { + if (attribute->ordering == SORTBY_DESC) + colOptionP[attn] |= INDOPTION_NULLS_FIRST; + } + else if (attribute->nulls_ordering == SORTBY_NULLS_FIRST) + colOptionP[attn] |= INDOPTION_NULLS_FIRST; + } + else + { + /* index AM does not support ordering */ + if (attribute->ordering != SORTBY_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("access method \"%s\" does not support ASC/DESC options", + accessMethodName))); + if (attribute->nulls_ordering != SORTBY_NULLS_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("access method \"%s\" does not support NULLS FIRST/LAST options", + accessMethodName))); + } + attn++; } } diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c index cfe8acede55..74a00ac8920 100644 --- a/src/backend/executor/nodeAgg.c +++ b/src/backend/executor/nodeAgg.c @@ -61,7 +61,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.147 2007/01/05 22:19:28 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.148 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -269,7 +269,7 @@ initialize_aggregates(AggState *aggstate, peraggstate->sortstate = tuplesort_begin_datum(peraggstate->inputType, - peraggstate->sortOperator, + peraggstate->sortOperator, false, work_mem, false); } diff --git a/src/backend/executor/nodeSort.c b/src/backend/executor/nodeSort.c index 73ee19cb358..97b5c4ff150 100644 --- a/src/backend/executor/nodeSort.c +++ b/src/backend/executor/nodeSort.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/nodeSort.c,v 1.59 2007/01/05 22:19:28 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/executor/nodeSort.c,v 1.60 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -84,8 +84,9 @@ ExecSort(SortState *node) tuplesortstate = tuplesort_begin_heap(tupDesc, plannode->numCols, - plannode->sortOperators, plannode->sortColIdx, + plannode->sortOperators, + plannode->nullsFirst, work_mem, node->randomAccess); node->tuplesortstate = (void *) tuplesortstate; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index e1117179ef2..08817e54a91 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.359 2007/01/05 22:19:29 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.360 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -510,6 +510,7 @@ _copySort(Sort *from) COPY_SCALAR_FIELD(numCols); COPY_POINTER_FIELD(sortColIdx, from->numCols * sizeof(AttrNumber)); COPY_POINTER_FIELD(sortOperators, from->numCols * sizeof(Oid)); + COPY_POINTER_FIELD(nullsFirst, from->numCols * sizeof(bool)); return newnode; } @@ -1283,6 +1284,7 @@ _copyPathKeyItem(PathKeyItem *from) COPY_NODE_FIELD(key); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1432,6 +1434,7 @@ _copySortClause(SortClause *from) COPY_SCALAR_FIELD(tleSortGroupRef); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1443,6 +1446,7 @@ _copyGroupClause(GroupClause *from) COPY_SCALAR_FIELD(tleSortGroupRef); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1597,7 +1601,8 @@ _copySortBy(SortBy *from) { SortBy *newnode = makeNode(SortBy); - COPY_SCALAR_FIELD(sortby_kind); + COPY_SCALAR_FIELD(sortby_dir); + COPY_SCALAR_FIELD(sortby_nulls); COPY_NODE_FIELD(useOp); COPY_NODE_FIELD(node); @@ -1646,6 +1651,8 @@ _copyIndexElem(IndexElem *from) COPY_STRING_FIELD(name); COPY_NODE_FIELD(expr); COPY_NODE_FIELD(opclass); + COPY_SCALAR_FIELD(ordering); + COPY_SCALAR_FIELD(nulls_ordering); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 8e14dafc28d..e1b3bbbbaa9 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -18,7 +18,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.293 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.294 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -600,6 +600,7 @@ _equalPathKeyItem(PathKeyItem *a, PathKeyItem *b) { COMPARE_NODE_FIELD(key); COMPARE_SCALAR_FIELD(sortop); + COMPARE_SCALAR_FIELD(nulls_first); return true; } @@ -1634,7 +1635,8 @@ _equalTypeCast(TypeCast *a, TypeCast *b) static bool _equalSortBy(SortBy *a, SortBy *b) { - COMPARE_SCALAR_FIELD(sortby_kind); + COMPARE_SCALAR_FIELD(sortby_dir); + COMPARE_SCALAR_FIELD(sortby_nulls); COMPARE_NODE_FIELD(useOp); COMPARE_NODE_FIELD(node); @@ -1666,6 +1668,8 @@ _equalIndexElem(IndexElem *a, IndexElem *b) COMPARE_STRING_FIELD(name); COMPARE_NODE_FIELD(expr); COMPARE_NODE_FIELD(opclass); + COMPARE_SCALAR_FIELD(ordering); + COMPARE_SCALAR_FIELD(nulls_ordering); return true; } @@ -1745,6 +1749,7 @@ _equalSortClause(SortClause *a, SortClause *b) { COMPARE_SCALAR_FIELD(tleSortGroupRef); COMPARE_SCALAR_FIELD(sortop); + COMPARE_SCALAR_FIELD(nulls_first); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 8f341daf9da..1ffaa08dfe9 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.291 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.292 2007/01/09 02:14:12 tgl Exp $ * * NOTES * Every node type that can appear in stored rules' parsetrees *must* @@ -510,6 +510,10 @@ _outSort(StringInfo str, Sort *node) appendStringInfo(str, " :sortOperators"); for (i = 0; i < node->numCols; i++) appendStringInfo(str, " %u", node->sortOperators[i]); + + appendStringInfo(str, " :nullsFirst"); + for (i = 0; i < node->numCols; i++) + appendStringInfo(str, " %s", booltostr(node->nullsFirst[i])); } static void @@ -1265,6 +1269,7 @@ _outPathKeyItem(StringInfo str, PathKeyItem *node) WRITE_NODE_FIELD(key); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void @@ -1499,6 +1504,8 @@ _outIndexElem(StringInfo str, IndexElem *node) WRITE_STRING_FIELD(name); WRITE_NODE_FIELD(expr); WRITE_NODE_FIELD(opclass); + WRITE_ENUM_FIELD(ordering, SortByDir); + WRITE_ENUM_FIELD(nulls_ordering, SortByNulls); } static void @@ -1565,6 +1572,7 @@ _outSortClause(StringInfo str, SortClause *node) WRITE_UINT_FIELD(tleSortGroupRef); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void @@ -1574,6 +1582,7 @@ _outGroupClause(StringInfo str, GroupClause *node) WRITE_UINT_FIELD(tleSortGroupRef); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index e4872b5814a..b90c8dd7130 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.200 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.201 2007/01/09 02:14:12 tgl Exp $ * * NOTES * Path and Plan nodes do not have any readfuncs support, because we @@ -201,6 +201,7 @@ _readSortClause(void) READ_UINT_FIELD(tleSortGroupRef); READ_OID_FIELD(sortop); + READ_BOOL_FIELD(nulls_first); READ_DONE(); } @@ -215,6 +216,7 @@ _readGroupClause(void) READ_UINT_FIELD(tleSortGroupRef); READ_OID_FIELD(sortop); + READ_BOOL_FIELD(nulls_first); READ_DONE(); } diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index c2859aba948..01178d93ddd 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.155 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.156 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -924,7 +924,7 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual, /* If subquery uses DISTINCT or DISTINCT ON, check point 4 */ if (subquery->distinctClause != NIL && - !targetIsInSortList(tle, subquery->distinctClause)) + !targetIsInSortList(tle, InvalidOid, subquery->distinctClause)) { /* non-DISTINCT column, so fail */ safe = false; diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 8906471fb7b..3fd52d48500 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.214 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.215 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -347,7 +347,7 @@ find_usable_indexes(PlannerInfo *root, RelOptInfo *rel, * how many of them are actually useful for this query. This is not * relevant unless we are at top level. */ - index_is_ordered = OidIsValid(index->ordering[0]); + index_is_ordered = OidIsValid(index->fwdsortop[0]); if (index_is_ordered && istoplevel && outer_rel == NULL) { index_pathkeys = build_index_pathkeys(root, index, diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c index 2af4cf7f9e5..4fc5a5f1250 100644 --- a/src/backend/optimizer/path/pathkeys.c +++ b/src/backend/optimizer/path/pathkeys.c @@ -11,7 +11,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.80 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.81 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -30,7 +30,8 @@ #include "utils/memutils.h" -static PathKeyItem *makePathKeyItem(Node *key, Oid sortop, bool checkType); +static PathKeyItem *makePathKeyItem(Node *key, Oid sortop, bool nulls_first, + bool checkType); static void generate_outer_join_implications(PlannerInfo *root, List *equi_key_set, Relids *relids); @@ -53,7 +54,7 @@ static Var *find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, * create a PathKeyItem node */ static PathKeyItem * -makePathKeyItem(Node *key, Oid sortop, bool checkType) +makePathKeyItem(Node *key, Oid sortop, bool nulls_first, bool checkType) { PathKeyItem *item = makeNode(PathKeyItem); @@ -78,6 +79,7 @@ makePathKeyItem(Node *key, Oid sortop, bool checkType) item->key = key; item->sortop = sortop; + item->nulls_first = nulls_first; return item; } @@ -102,9 +104,11 @@ add_equijoined_keys(PlannerInfo *root, RestrictInfo *restrictinfo) Expr *clause = restrictinfo->clause; PathKeyItem *item1 = makePathKeyItem(get_leftop(clause), restrictinfo->left_sortop, + false, /* XXX nulls_first? */ false); PathKeyItem *item2 = makePathKeyItem(get_rightop(clause), restrictinfo->right_sortop, + false, /* XXX nulls_first? */ false); List *newset; ListCell *cursetlink; @@ -903,7 +907,7 @@ get_cheapest_fractional_path_for_pathkeys(List *paths, * Build a pathkeys list that describes the ordering induced by an index * scan using the given index. (Note that an unordered index doesn't * induce any ordering; such an index will have no sortop OIDS in - * its "ordering" field, and we will return NIL.) + * its sortops arrays, and we will return NIL.) * * If 'scandir' is BackwardScanDirection, attempt to build pathkeys * representing a backwards scan of the index. Return NIL if can't do it. @@ -924,30 +928,37 @@ build_index_pathkeys(PlannerInfo *root, bool canonical) { List *retval = NIL; - int *indexkeys = index->indexkeys; - Oid *ordering = index->ordering; ListCell *indexprs_item = list_head(index->indexprs); + int i; - while (*ordering != InvalidOid) + for (i = 0; i < index->ncolumns; i++) { - PathKeyItem *item; Oid sortop; + bool nulls_first; + int ikey; Node *indexkey; + PathKeyItem *item; List *cpathkey; - sortop = *ordering; if (ScanDirectionIsBackward(scandir)) { - sortop = get_commutator(sortop); - if (sortop == InvalidOid) - break; /* oops, no reverse sort operator? */ + sortop = index->revsortop[i]; + nulls_first = !index->nulls_first[i]; } + else + { + sortop = index->fwdsortop[i]; + nulls_first = index->nulls_first[i]; + } + + if (!OidIsValid(sortop)) + break; /* no more orderable columns */ - if (*indexkeys != 0) + ikey = index->indexkeys[i]; + if (ikey != 0) { /* simple index column */ - indexkey = (Node *) find_indexkey_var(root, index->rel, - *indexkeys); + indexkey = (Node *) find_indexkey_var(root, index->rel, ikey); } else { @@ -959,7 +970,7 @@ build_index_pathkeys(PlannerInfo *root, } /* OK, make a sublist for this sort key */ - item = makePathKeyItem(indexkey, sortop, true); + item = makePathKeyItem(indexkey, sortop, nulls_first, true); cpathkey = make_canonical_pathkey(root, item); /* Eliminate redundant ordering info if requested */ @@ -967,9 +978,6 @@ build_index_pathkeys(PlannerInfo *root, retval = list_append_unique_ptr(retval, cpathkey); else retval = lappend(retval, cpathkey); - - indexkeys++; - ordering++; } return retval; @@ -1115,6 +1123,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel, /* Found a representation for this sub_key */ outer_item = makePathKeyItem(outer_expr, sub_item->sortop, + sub_item->nulls_first, true); /* score = # of mergejoin peers */ score = count_canonical_peers(root, outer_item); @@ -1230,7 +1239,8 @@ make_pathkeys_for_sortclauses(List *sortclauses, PathKeyItem *pathkey; sortkey = get_sortgroupclause_expr(sortcl, tlist); - pathkey = makePathKeyItem(sortkey, sortcl->sortop, true); + pathkey = makePathKeyItem(sortkey, sortcl->sortop, sortcl->nulls_first, + true); /* * The pathkey becomes a one-element sublist, for now; @@ -1274,7 +1284,9 @@ cache_mergeclause_pathkeys(PlannerInfo *root, RestrictInfo *restrictinfo) { oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo)); key = get_leftop(restrictinfo->clause); - item = makePathKeyItem(key, restrictinfo->left_sortop, false); + item = makePathKeyItem(key, restrictinfo->left_sortop, + false, /* XXX nulls_first? */ + false); restrictinfo->left_pathkey = make_canonical_pathkey(root, item); MemoryContextSwitchTo(oldcontext); } @@ -1282,7 +1294,9 @@ cache_mergeclause_pathkeys(PlannerInfo *root, RestrictInfo *restrictinfo) { oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo)); key = get_rightop(restrictinfo->clause); - item = makePathKeyItem(key, restrictinfo->right_sortop, false); + item = makePathKeyItem(key, restrictinfo->right_sortop, + false, /* XXX nulls_first? */ + false); restrictinfo->right_pathkey = make_canonical_pathkey(root, item); MemoryContextSwitchTo(oldcontext); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 7328f41a399..8f1d8e81cc4 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.219 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.220 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -117,7 +117,7 @@ static MergeJoin *make_mergejoin(List *tlist, Plan *lefttree, Plan *righttree, JoinType jointype); static Sort *make_sort(PlannerInfo *root, Plan *lefttree, int numCols, - AttrNumber *sortColIdx, Oid *sortOperators); + AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst); static Sort *make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys); @@ -734,7 +734,9 @@ create_unique_plan(PlannerInfo *root, UniquePath *best_path) Assert(tle != NULL); sortList = addTargetToSortList(NULL, tle, sortList, subplan->targetlist, - SORTBY_ASC, NIL, false); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, false); } plan = (Plan *) make_sort_from_sortclauses(root, sortList, subplan); plan = (Plan *) make_unique(plan, sortList); @@ -2359,11 +2361,12 @@ make_mergejoin(List *tlist, /* * make_sort --- basic routine to build a Sort plan node * - * Caller must have built the sortColIdx and sortOperators arrays already. + * Caller must have built the sortColIdx, sortOperators, and nullsFirst + * arrays already. */ static Sort * make_sort(PlannerInfo *root, Plan *lefttree, int numCols, - AttrNumber *sortColIdx, Oid *sortOperators) + AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst) { Sort *node = makeNode(Sort); Plan *plan = &node->plan; @@ -2383,6 +2386,7 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols, node->numCols = numCols; node->sortColIdx = sortColIdx; node->sortOperators = sortOperators; + node->nullsFirst = nullsFirst; return node; } @@ -2397,14 +2401,23 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols, * max possible number of columns. Return value is the new column count. */ static int -add_sort_column(AttrNumber colIdx, Oid sortOp, - int numCols, AttrNumber *sortColIdx, Oid *sortOperators) +add_sort_column(AttrNumber colIdx, Oid sortOp, bool nulls_first, + int numCols, AttrNumber *sortColIdx, + Oid *sortOperators, bool *nullsFirst) { int i; for (i = 0; i < numCols; i++) { - if (sortColIdx[i] == colIdx) + /* + * Note: we check sortOp because it's conceivable that "ORDER BY + * foo USING <, foo USING <<<" is not redundant, if <<< distinguishes + * values that < considers equal. We need not check nulls_first + * however because a lower-order column with the same sortop but + * opposite nulls direction is redundant. + */ + if (sortColIdx[i] == colIdx && + sortOperators[numCols] == sortOp) { /* Already sorting by this col, so extra sort key is useless */ return numCols; @@ -2414,6 +2427,7 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, /* Add the column */ sortColIdx[numCols] = colIdx; sortOperators[numCols] = sortOp; + nullsFirst[numCols] = nulls_first; return numCols + 1; } @@ -2441,6 +2455,7 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(pathkeys) sort columns; possibly less @@ -2448,6 +2463,7 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) numsortkeys = list_length(pathkeys); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2527,13 +2543,15 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) * So enter it only once in the sort arrays. */ numsortkeys = add_sort_column(tle->resno, pathkey->sortop, - numsortkeys, sortColIdx, sortOperators); + pathkey->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } /* @@ -2551,6 +2569,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(sortcls) sort columns; possibly less @@ -2558,6 +2577,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) numsortkeys = list_length(sortcls); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2572,13 +2592,15 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) * redundantly. */ numsortkeys = add_sort_column(tle->resno, sortcl->sortop, - numsortkeys, sortColIdx, sortOperators); + sortcl->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } /* @@ -2591,8 +2613,8 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) * This might look like it could be merged with make_sort_from_sortclauses, * but presently we *must* use the grpColIdx[] array to locate sort columns, * because the child plan's tlist is not marked with ressortgroupref info - * appropriate to the grouping node. So, only the sortop is used from the - * GroupClause entries. + * appropriate to the grouping node. So, only the sort direction info + * is used from the GroupClause entries. */ Sort * make_sort_from_groupcols(PlannerInfo *root, @@ -2606,6 +2628,7 @@ make_sort_from_groupcols(PlannerInfo *root, int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(groupcls) sort columns; possibly less @@ -2613,6 +2636,7 @@ make_sort_from_groupcols(PlannerInfo *root, numsortkeys = list_length(groupcls); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2627,14 +2651,16 @@ make_sort_from_groupcols(PlannerInfo *root, * redundantly. */ numsortkeys = add_sort_column(tle->resno, grpcl->sortop, - numsortkeys, sortColIdx, sortOperators); + grpcl->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); grpno++; } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } Material * @@ -2871,7 +2897,6 @@ make_unique(Plan *lefttree, List *distinctList) * distinctList is a list of SortClauses, identifying the targetlist items * that should be considered by the SetOp filter. */ - SetOp * make_setop(SetOpCmd cmd, Plan *lefttree, List *distinctList, AttrNumber flagColIdx) diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index fa980bb9150..bce3b1ac442 100644 --- a/src/backend/optimizer/plan/planagg.c +++ b/src/backend/optimizer/plan/planagg.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.24 2007/01/05 22:19:32 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.25 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -37,6 +37,7 @@ typedef struct Expr *target; /* expression we are aggregating on */ IndexPath *path; /* access path for index scan */ Cost pathcost; /* estimated cost to fetch first row */ + bool nulls_first; /* null ordering direction matching index */ Param *param; /* param for subplan's output */ } MinMaxAggInfo; @@ -277,6 +278,7 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) { IndexPath *best_path = NULL; Cost best_cost = 0; + bool best_nulls_first = false; ListCell *l; foreach(l, rel->indexlist) @@ -377,11 +379,16 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) { best_path = new_path; best_cost = new_cost; + if (ScanDirectionIsForward(indexscandir)) + best_nulls_first = index->nulls_first[indexcol]; + else + best_nulls_first = !index->nulls_first[indexcol]; } } info->path = best_path; info->pathcost = best_cost; + info->nulls_first = best_nulls_first; return (best_path != NULL); } @@ -390,29 +397,30 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) * Does an aggregate match an index column? * * It matches if its argument is equal to the index column's data and its - * sortop is either a LessThan or GreaterThan member of the column's opfamily. + * sortop is either the forward or reverse sort operator for the column. * - * We return ForwardScanDirection if match a LessThan member, - * BackwardScanDirection if match a GreaterThan member, + * We return ForwardScanDirection if match the forward sort operator, + * BackwardScanDirection if match the reverse sort operator, * and NoMovementScanDirection if there's no match. */ static ScanDirection match_agg_to_index_col(MinMaxAggInfo *info, IndexOptInfo *index, int indexcol) { - int strategy; + ScanDirection result; + + /* Check for operator match first (cheaper) */ + if (info->aggsortop == index->fwdsortop[indexcol]) + result = ForwardScanDirection; + else if (info->aggsortop == index->revsortop[indexcol]) + result = BackwardScanDirection; + else + return NoMovementScanDirection; /* Check for data match */ if (!match_index_to_operand((Node *) info->target, indexcol, index)) return NoMovementScanDirection; - /* Look up the operator in the opfamily */ - strategy = get_op_opfamily_strategy(info->aggsortop, - index->opfamily[indexcol]); - if (strategy == BTLessStrategyNumber) - return ForwardScanDirection; - if (strategy == BTGreaterStrategyNumber) - return BackwardScanDirection; - return NoMovementScanDirection; + return result; } /* @@ -458,6 +466,7 @@ make_agg_subplan(PlannerInfo *root, MinMaxAggInfo *info) sortcl = makeNode(SortClause); sortcl->tleSortGroupRef = assignSortGroupRef(tle, subparse->targetList); sortcl->sortop = info->aggsortop; + sortcl->nulls_first = info->nulls_first; subparse->sortClause = list_make1(sortcl); /* set up LIMIT 1 */ diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index d1ac0740c64..3250beafb67 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.227 2007/01/05 22:19:32 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.228 2007/01/09 02:14:13 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -1147,7 +1147,7 @@ has_distinct_on_clause(Query *query) continue; /* we can ignore unsorted junk cols */ return true; /* definitely not in DISTINCT list */ } - if (targetIsInSortList(tle, query->distinctClause)) + if (targetIsInSortList(tle, InvalidOid, query->distinctClause)) { if (tle->resjunk) return true; /* junk TLE in DISTINCT means DISTINCT ON */ @@ -1158,7 +1158,7 @@ has_distinct_on_clause(Query *query) /* This TLE is not in DISTINCT list */ if (!tle->resjunk) return true; /* non-junk, non-DISTINCT, so DISTINCT ON */ - if (targetIsInSortList(tle, query->sortClause)) + if (targetIsInSortList(tle, InvalidOid, query->sortClause)) return true; /* sorted, non-distinct junk */ /* unsorted junk is okay, keep looking */ } diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 353f2debe4c..9150f1d936f 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.130 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.131 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -142,7 +142,6 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, IndexOptInfo *info; int ncolumns; int i; - int16 amorderstrategy; /* * Extract info from the relation descriptor for the index. @@ -169,12 +168,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, info->ncolumns = ncolumns = index->indnatts; /* - * Need to make opfamily and ordering arrays large enough to put - * a terminating 0 at the end of each one. + * Need to make opfamily array large enough to put a terminating + * zero at the end. */ info->indexkeys = (int *) palloc(sizeof(int) * ncolumns); info->opfamily = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1)); - info->ordering = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1)); + /* initialize these to zeroes in case index is unordered */ + info->fwdsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns); + info->revsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns); + info->nulls_first = (bool *) palloc0(sizeof(bool) * ncolumns); for (i = 0; i < ncolumns; i++) { @@ -189,22 +191,42 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, /* * Fetch the ordering operators associated with the index, if any. */ - amorderstrategy = indexRelation->rd_am->amorderstrategy; - if (amorderstrategy > 0) + if (indexRelation->rd_am->amorderstrategy > 0) { - int oprindex = amorderstrategy - 1; - - /* - * Index AM must have a fixed set of strategies for it to - * make sense to specify amorderstrategy, so we need not - * allow the case amstrategies == 0. - */ - Assert(oprindex < indexRelation->rd_am->amstrategies); + int nstrat = indexRelation->rd_am->amstrategies; for (i = 0; i < ncolumns; i++) { - info->ordering[i] = indexRelation->rd_operator[oprindex]; - oprindex += indexRelation->rd_am->amstrategies; + int16 opt = indexRelation->rd_indoption[i]; + int fwdstrat; + int revstrat; + + if (opt & INDOPTION_DESC) + { + fwdstrat = indexRelation->rd_am->amdescorder; + revstrat = indexRelation->rd_am->amorderstrategy; + } + else + { + fwdstrat = indexRelation->rd_am->amorderstrategy; + revstrat = indexRelation->rd_am->amdescorder; + } + /* + * Index AM must have a fixed set of strategies for it + * to make sense to specify amorderstrategy, so we + * need not allow the case amstrategies == 0. + */ + if (fwdstrat > 0) + { + Assert(fwdstrat <= nstrat); + info->fwdsortop[i] = indexRelation->rd_operator[i * nstrat + fwdstrat - 1]; + } + if (revstrat > 0) + { + Assert(revstrat <= nstrat); + info->revsortop[i] = indexRelation->rd_operator[i * nstrat + revstrat - 1]; + } + info->nulls_first[i] = (opt & INDOPTION_NULLS_FIRST) != 0; } } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 270332812b7..f4b566cb6de 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.354 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.355 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1629,6 +1629,8 @@ transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt) iparam->name = pstrdup(key); iparam->expr = NULL; iparam->opclass = NIL; + iparam->ordering = SORTBY_DEFAULT; + iparam->nulls_ordering = SORTBY_NULLS_DEFAULT; index->indexParams = lappend(index->indexParams, iparam); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e3eae427ded..6abe0d6795a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.572 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.573 2007/01/09 02:14:14 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -175,7 +175,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) simple_select values_clause %type <node> alter_column_default opclass_item alter_using -%type <ival> add_drop +%type <ival> add_drop opt_asc_desc opt_nulls_order %type <node> alter_table_cmd alter_rel_cmd %type <list> alter_table_cmds alter_rel_cmds @@ -397,7 +397,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) KEY - LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL + LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P @@ -405,7 +405,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER - NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NUMERIC + NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER @@ -449,7 +449,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ -%token WITH_CASCADED WITH_LOCAL WITH_CHECK +%token NULLS_FIRST NULLS_LAST WITH_CASCADED WITH_LOCAL WITH_CHECK /* Special token types, not actually keywords - see the "lex" file */ %token <str> IDENT FCONST SCONST BCONST XCONST Op @@ -3712,26 +3712,32 @@ index_params: index_elem { $$ = list_make1($1); } * expressions in parens. For backwards-compatibility reasons, we allow * an expression that's just a function call to be written without parens. */ -index_elem: ColId opt_class +index_elem: ColId opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = $1; $$->expr = NULL; $$->opclass = $2; + $$->ordering = $3; + $$->nulls_ordering = $4; } - | func_expr opt_class + | func_expr opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = NULL; $$->expr = $1; $$->opclass = $2; + $$->ordering = $3; + $$->nulls_ordering = $4; } - | '(' a_expr ')' opt_class + | '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = NULL; $$->expr = $2; $$->opclass = $4; + $$->ordering = $5; + $$->nulls_ordering = $6; } ; @@ -3740,6 +3746,17 @@ opt_class: any_name { $$ = $1; } | /*EMPTY*/ { $$ = NIL; } ; +opt_asc_desc: ASC { $$ = SORTBY_ASC; } + | DESC { $$ = SORTBY_DESC; } + | /*EMPTY*/ { $$ = SORTBY_DEFAULT; } + ; + +opt_nulls_order: NULLS_FIRST { $$ = SORTBY_NULLS_FIRST; } + | NULLS_LAST { $$ = SORTBY_NULLS_LAST; } + | /*EMPTY*/ { $$ = SORTBY_NULLS_DEFAULT; } + ; + + /***************************************************************************** * * QUERY: @@ -5810,32 +5827,36 @@ sortby_list: | sortby_list ',' sortby { $$ = lappend($1, $3); } ; -sortby: a_expr USING qual_all_Op +sortby: a_expr USING qual_all_Op opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_USING; + $$->sortby_dir = SORTBY_USING; + $$->sortby_nulls = $4; $$->useOp = $3; } - | a_expr ASC + | a_expr ASC opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_ASC; + $$->sortby_dir = SORTBY_ASC; + $$->sortby_nulls = $3; $$->useOp = NIL; } - | a_expr DESC + | a_expr DESC opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_DESC; + $$->sortby_dir = SORTBY_DESC; + $$->sortby_nulls = $3; $$->useOp = NIL; } - | a_expr + | a_expr opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_ASC; /* default */ + $$->sortby_dir = SORTBY_DEFAULT; + $$->sortby_nulls = $2; $$->useOp = NIL; } ; @@ -8613,6 +8634,7 @@ unreserved_keyword: | NOTHING | NOTIFY | NOWAIT + | NULLS_P | OBJECT_P | OF | OIDS diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index e6a4f9a7ebc..e1592736f0d 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.180 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.181 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -242,6 +242,7 @@ static const ScanKeyword ScanKeywords[] = { {"nowait", NOWAIT}, {"null", NULL_P}, {"nullif", NULLIF}, + {"nulls", NULLS_P}, {"numeric", NUMERIC}, {"object", OBJECT_P}, {"of", OF}, diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 663273df48b..6db3fce8377 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.161 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.162 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -33,6 +33,7 @@ #include "parser/parse_target.h" #include "rewrite/rewriteManip.h" #include "utils/guc.h" +#include "utils/lsyscache.h" #define ORDER_CLAUSE 0 @@ -1305,13 +1306,15 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause) } static GroupClause * -make_group_clause(TargetEntry *tle, List *targetlist, Oid sortop) +make_group_clause(TargetEntry *tle, List *targetlist, + Oid sortop, bool nulls_first) { GroupClause *result; result = makeNode(GroupClause); result->tleSortGroupRef = assignSortGroupRef(tle, targetlist); result->sortop = sortop; + result->nulls_first = nulls_first; return result; } @@ -1380,8 +1383,9 @@ transformGroupClause(ParseState *pstate, List *grouplist, tle_list = list_delete_cell(tle_list, tl, prev); - /* Use the sort clause's sorting operator */ - gc = make_group_clause(tle, *targetlist, sc->sortop); + /* Use the sort clause's sorting information */ + gc = make_group_clause(tle, *targetlist, + sc->sortop, sc->nulls_first); result = lappend(result, gc); found = true; break; @@ -1408,12 +1412,18 @@ transformGroupClause(ParseState *pstate, List *grouplist, GroupClause *gc; Oid sort_op; - /* avoid making duplicate grouplist entries */ - if (targetIsInSortList(tle, result)) + /* + * Avoid making duplicate grouplist entries. Note that we don't + * enforce a particular sortop here. Along with the copying of sort + * information above, this means that if you write something like + * "GROUP BY foo ORDER BY foo USING <<<", the GROUP BY operation + * silently takes on the equality semantics implied by the ORDER BY. + */ + if (targetIsInSortList(tle, InvalidOid, result)) continue; sort_op = ordering_oper_opid(exprType((Node *) tle->expr)); - gc = make_group_clause(tle, *targetlist, sort_op); + gc = make_group_clause(tle, *targetlist, sort_op, false); result = lappend(result, gc); } @@ -1447,7 +1457,8 @@ transformSortClause(ParseState *pstate, sortlist = addTargetToSortList(pstate, tle, sortlist, *targetlist, - sortby->sortby_kind, + sortby->sortby_dir, + sortby->sortby_nulls, sortby->useOp, resolveUnknown); } @@ -1553,7 +1564,9 @@ transformDistinctClause(ParseState *pstate, List *distinctlist, { *sortClause = addTargetToSortList(pstate, tle, *sortClause, *targetlist, - SORTBY_ASC, NIL, true); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, true); /* * Probably, the tle should always have been added at the end @@ -1601,8 +1614,9 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, if (!tle->resjunk) sortlist = addTargetToSortList(pstate, tle, sortlist, targetlist, - SORTBY_ASC, NIL, - resolveUnknown); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, resolveUnknown); } return sortlist; } @@ -1610,8 +1624,7 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, /* * addTargetToSortList * If the given targetlist entry isn't already in the ORDER BY list, - * add it to the end of the list, using the sortop with given name - * or the default sort operator if opname == NIL. + * add it to the end of the list, using the given sort ordering info. * * If resolveUnknown is TRUE, convert TLEs of type UNKNOWN to TEXT. If not, * do nothing (which implies the search for a sort operator will fail). @@ -1623,49 +1636,89 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, List * addTargetToSortList(ParseState *pstate, TargetEntry *tle, List *sortlist, List *targetlist, - int sortby_kind, List *sortby_opname, - bool resolveUnknown) + SortByDir sortby_dir, SortByNulls sortby_nulls, + List *sortby_opname, bool resolveUnknown) { + Oid restype = exprType((Node *) tle->expr); + Oid sortop; + Oid cmpfunc; + bool reverse; + + /* if tlist item is an UNKNOWN literal, change it to TEXT */ + if (restype == UNKNOWNOID && resolveUnknown) + { + tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr, + restype, TEXTOID, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST); + restype = TEXTOID; + } + + /* determine the sortop */ + switch (sortby_dir) + { + case SORTBY_DEFAULT: + case SORTBY_ASC: + sortop = ordering_oper_opid(restype); + reverse = false; + break; + case SORTBY_DESC: + sortop = reverse_ordering_oper_opid(restype); + reverse = true; + break; + case SORTBY_USING: + Assert(sortby_opname != NIL); + sortop = compatible_oper_opid(sortby_opname, + restype, + restype, + false); + /* + * Verify it's a valid ordering operator, and determine + * whether to consider it like ASC or DESC. + */ + if (!get_op_compare_function(sortop, &cmpfunc, &reverse)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("operator %s is not a valid ordering operator", + strVal(llast(sortby_opname))), + errhint("Ordering operators must be \"<\" or \">\" members of btree operator families."))); + break; + default: + elog(ERROR, "unrecognized sortby_dir: %d", sortby_dir); + sortop = InvalidOid; /* keep compiler quiet */ + reverse = false; + break; + } + /* avoid making duplicate sortlist entries */ - if (!targetIsInSortList(tle, sortlist)) + if (!targetIsInSortList(tle, sortop, sortlist)) { SortClause *sortcl = makeNode(SortClause); - Oid restype = exprType((Node *) tle->expr); - - /* if tlist item is an UNKNOWN literal, change it to TEXT */ - if (restype == UNKNOWNOID && resolveUnknown) - { - tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr, - restype, TEXTOID, -1, - COERCION_IMPLICIT, - COERCE_IMPLICIT_CAST); - restype = TEXTOID; - } sortcl->tleSortGroupRef = assignSortGroupRef(tle, targetlist); - switch (sortby_kind) + sortcl->sortop = sortop; + + switch (sortby_nulls) { - case SORTBY_ASC: - sortcl->sortop = ordering_oper_opid(restype); + case SORTBY_NULLS_DEFAULT: + /* NULLS FIRST is default for DESC; other way for ASC */ + sortcl->nulls_first = reverse; break; - case SORTBY_DESC: - sortcl->sortop = reverse_ordering_oper_opid(restype); + case SORTBY_NULLS_FIRST: + sortcl->nulls_first = true; break; - case SORTBY_USING: - Assert(sortby_opname != NIL); - sortcl->sortop = compatible_oper_opid(sortby_opname, - restype, - restype, - false); + case SORTBY_NULLS_LAST: + sortcl->nulls_first = false; break; default: - elog(ERROR, "unrecognized sortby_kind: %d", sortby_kind); + elog(ERROR, "unrecognized sortby_nulls: %d", sortby_nulls); break; } sortlist = lappend(sortlist, sortcl); } + return sortlist; } @@ -1701,13 +1754,23 @@ assignSortGroupRef(TargetEntry *tle, List *tlist) /* * targetIsInSortList * Is the given target item already in the sortlist? + * If sortop is not InvalidOid, also test for a match to the sortop. + * + * It is not an oversight that this function ignores the nulls_first flag. + * We check sortop when determining if an ORDER BY item is redundant with + * earlier ORDER BY items, because it's conceivable that "ORDER BY + * foo USING <, foo USING <<<" is not redundant, if <<< distinguishes + * values that < considers equal. We need not check nulls_first + * however, because a lower-order column with the same sortop but + * opposite nulls direction is redundant. Also, we can consider + * ORDER BY foo ASC, foo DESC redundant, so check for a commutator match. * * Works for both SortClause and GroupClause lists. Note that the main * reason we need this routine (and not just a quick test for nonzeroness * of ressortgroupref) is that a TLE might be in only one of the lists. */ bool -targetIsInSortList(TargetEntry *tle, List *sortList) +targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList) { Index ref = tle->ressortgroupref; ListCell *l; @@ -1720,7 +1783,10 @@ targetIsInSortList(TargetEntry *tle, List *sortList) { SortClause *scl = (SortClause *) lfirst(l); - if (scl->tleSortGroupRef == ref) + if (scl->tleSortGroupRef == ref && + (sortop == InvalidOid || + sortop == scl->sortop || + sortop == get_commutator(scl->sortop))) return true; } return false; diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index c007613cc4e..b9c0b9a9853 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -14,7 +14,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.70 2007/01/06 19:14:17 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.71 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -97,8 +97,35 @@ filtered_base_yylex(void) /* Do we need to look ahead for a possible multiword token? */ switch (cur_token) { - case WITH: + case NULLS_P: + /* + * NULLS FIRST and NULLS LAST must be reduced to one token + */ + cur_yylval = base_yylval; + cur_yylloc = base_yylloc; + next_token = base_yylex(); + switch (next_token) + { + case FIRST_P: + cur_token = NULLS_FIRST; + break; + case LAST_P: + cur_token = NULLS_LAST; + break; + default: + /* save the lookahead token for next time */ + lookahead_token = next_token; + lookahead_yylval = base_yylval; + lookahead_yylloc = base_yylloc; + have_lookahead = true; + /* and back up the output info to cur_token */ + base_yylval = cur_yylval; + base_yylloc = cur_yylloc; + break; + } + break; + case WITH: /* * WITH CASCADED, LOCAL, or CHECK must be reduced to one token * diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 34d7b66743b..3c217c98edc 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2,7 +2,7 @@ * ruleutils.c - Functions to convert stored expressions/querytrees * back to source text * - * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.240 2006/12/29 16:44:28 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.241 2007/01/09 02:14:14 tgl Exp $ **********************************************************************/ #include "postgres.h" @@ -615,8 +615,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) int keyno; Oid keycoltype; Datum indclassDatum; + Datum indoptionDatum; bool isnull; oidvector *indclass; + int2vector *indoption; StringInfoData buf; char *str; char *sep; @@ -634,11 +636,15 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) indrelid = idxrec->indrelid; Assert(indexrelid == idxrec->indexrelid); - /* Must get indclass the hard way */ + /* Must get indclass and indoption the hard way */ indclassDatum = SysCacheGetAttr(INDEXRELID, ht_idx, Anum_pg_index_indclass, &isnull); Assert(!isnull); indclass = (oidvector *) DatumGetPointer(indclassDatum); + indoptionDatum = SysCacheGetAttr(INDEXRELID, ht_idx, + Anum_pg_index_indoption, &isnull); + Assert(!isnull); + indoption = (int2vector *) DatumGetPointer(indoptionDatum); /* * Fetch the pg_class tuple of the index relation @@ -707,6 +713,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) for (keyno = 0; keyno < idxrec->indnatts; keyno++) { AttrNumber attnum = idxrec->indkey.values[keyno]; + int16 opt = indoption->values[keyno]; if (!colno) appendStringInfoString(&buf, sep); @@ -746,12 +753,28 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) keycoltype = exprType(indexkey); } - /* - * Add the operator class name - */ + /* Add the operator class name */ if (!colno) get_opclass_name(indclass->values[keyno], keycoltype, &buf); + + /* Add options if relevant */ + if (amrec->amorderstrategy > 0) + { + /* if it supports sort ordering, report DESC and NULLS opts */ + if (opt & INDOPTION_DESC) + { + appendStringInfo(&buf, " DESC"); + /* NULLS FIRST is the default in this case */ + if (!(opt & INDOPTION_NULLS_FIRST)) + appendStringInfo(&buf, " NULLS LAST"); + } + else + { + if (opt & INDOPTION_NULLS_FIRST) + appendStringInfo(&buf, " NULLS FIRST"); + } + } } if (!colno) @@ -1905,14 +1928,30 @@ get_select_query_def(Query *query, deparse_context *context, typentry = lookup_type_cache(sortcoltype, TYPECACHE_LT_OPR | TYPECACHE_GT_OPR); if (srt->sortop == typentry->lt_opr) - /* ASC is default, so emit nothing */ ; + { + /* ASC is default, so emit nothing for it */ + if (srt->nulls_first) + appendStringInfo(buf, " NULLS FIRST"); + } else if (srt->sortop == typentry->gt_opr) + { appendStringInfo(buf, " DESC"); + /* DESC defaults to NULLS FIRST */ + if (!srt->nulls_first) + appendStringInfo(buf, " NULLS LAST"); + } else + { appendStringInfo(buf, " USING %s", generate_operator_name(srt->sortop, sortcoltype, sortcoltype)); + /* be specific to eliminate ambiguity */ + if (srt->nulls_first) + appendStringInfo(buf, " NULLS FIRST"); + else + appendStringInfo(buf, " NULLS LAST"); + } sep = ", "; } } diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 31cc62d68bb..875c7c524af 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.218 2007/01/05 22:19:42 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.219 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -5101,7 +5101,7 @@ btcostestimate(PG_FUNCTION_ARGS) if (get_attstatsslot(tuple, InvalidOid, 0, STATISTIC_KIND_CORRELATION, - index->ordering[0], + index->fwdsortop[0], NULL, NULL, &numbers, &nnumbers)) { double varCorrelation; @@ -5116,6 +5116,23 @@ btcostestimate(PG_FUNCTION_ARGS) free_attstatsslot(InvalidOid, NULL, 0, numbers, nnumbers); } + else if (get_attstatsslot(tuple, InvalidOid, 0, + STATISTIC_KIND_CORRELATION, + index->revsortop[0], + NULL, NULL, &numbers, &nnumbers)) + { + double varCorrelation; + + Assert(nnumbers == 1); + varCorrelation = numbers[0]; + + if (index->ncolumns > 1) + *indexCorrelation = - varCorrelation * 0.75; + else + *indexCorrelation = - varCorrelation; + + free_attstatsslot(InvalidOid, NULL, 0, numbers, nnumbers); + } ReleaseSysCache(tuple); } diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 82dbca9e116..6379e258126 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.141 2007/01/05 22:19:43 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.142 2007/01/09 02:14:14 tgl Exp $ * * NOTES * Eventually, the index information should go through here, too. @@ -16,6 +16,7 @@ #include "postgres.h" #include "access/hash.h" +#include "access/nbtree.h" #include "bootstrap/bootstrap.h" #include "catalog/pg_amop.h" #include "catalog/pg_amproc.h" @@ -192,7 +193,7 @@ get_op_mergejoin_info(Oid eq_op, Oid left_sortop, if (op_form->amopstrategy != BTEqualStrategyNumber) continue; - /* See if sort operator is also in this opclass with OK semantics */ + /* See if sort operator is also in this opfamily with OK semantics */ opfamily_id = op_form->amopfamily; op_strategy = get_op_opfamily_strategy(left_sortop, opfamily_id); if (op_strategy == BTLessStrategyNumber || @@ -285,6 +286,78 @@ get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, #endif /* + * get_op_compare_function + * Get the OID of the datatype-specific btree comparison function + * associated with an ordering operator (a "<" or ">" operator). + * + * *cmpfunc receives the comparison function OID. + * *reverse is set FALSE if the operator is "<", TRUE if it's ">" + * (indicating the comparison result must be negated before use). + * + * Returns TRUE if successful, FALSE if no btree function can be found. + * (This indicates that the operator is not a valid ordering operator.) + */ +bool +get_op_compare_function(Oid opno, Oid *cmpfunc, bool *reverse) +{ + bool result = false; + CatCList *catlist; + int i; + + /* ensure outputs are set on failure */ + *cmpfunc = InvalidOid; + *reverse = false; + + /* + * Search pg_amop to see if the target operator is registered as the "<" + * or ">" operator of any btree opfamily. It's possible that it might be + * registered both ways (if someone were to build a "reverse sort" + * opfamily); assume we can use either interpretation. (Note: the + * existence of a reverse-sort opfamily would result in uncertainty as + * to whether "ORDER BY USING op" would default to NULLS FIRST or NULLS + * LAST. Since there is no longer any particularly good reason to build + * reverse-sort opfamilies, we don't bother expending any extra work to + * make this more determinate. In practice, because of the way the + * syscache search works, we'll use the interpretation associated with + * the opfamily with smallest OID, which is probably determinate enough.) + */ + catlist = SearchSysCacheList(AMOPOPID, 1, + ObjectIdGetDatum(opno), + 0, 0, 0); + + for (i = 0; i < catlist->n_members; i++) + { + HeapTuple tuple = &catlist->members[i]->tuple; + Form_pg_amop aform = (Form_pg_amop) GETSTRUCT(tuple); + + /* must be btree */ + if (aform->amopmethod != BTREE_AM_OID) + continue; + + if (aform->amopstrategy == BTLessStrategyNumber || + aform->amopstrategy == BTGreaterStrategyNumber) + { + /* Found a suitable opfamily, get matching support function */ + *reverse = (aform->amopstrategy == BTGreaterStrategyNumber); + *cmpfunc = get_opfamily_proc(aform->amopfamily, + aform->amoplefttype, + aform->amoprighttype, + BTORDER_PROC); + if (!OidIsValid(*cmpfunc)) /* should not happen */ + elog(ERROR, "missing support function %d(%u,%u) in opfamily %u", + BTORDER_PROC, aform->amoplefttype, aform->amoprighttype, + aform->amopfamily); + result = true; + break; + } + } + + ReleaseSysCacheList(catlist); + + return result; +} + +/* * get_op_hash_function * Get the OID of the datatype-specific hash function associated with * a hashable equality operator. @@ -298,9 +371,9 @@ get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, Oid get_op_hash_function(Oid opno) { + Oid result = InvalidOid; CatCList *catlist; int i; - Oid result = InvalidOid; /* * Search pg_amop to see if the target operator is registered as the "=" diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index baa45447a29..c43846cd57a 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.253 2007/01/05 22:19:43 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.254 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -925,8 +925,10 @@ RelationInitIndexAccessInfo(Relation relation) HeapTuple tuple; Form_pg_am aform; Datum indclassDatum; + Datum indoptionDatum; bool isnull; oidvector *indclass; + int2vector *indoption; MemoryContext indexcxt; MemoryContext oldcontext; int natts; @@ -1019,6 +1021,9 @@ RelationInitIndexAccessInfo(Relation relation) relation->rd_supportinfo = NULL; } + relation->rd_indoption = (int16 *) + MemoryContextAllocZero(indexcxt, natts * sizeof(int16)); + /* * indclass cannot be referenced directly through the C struct, because it * comes after the variable-width indkey field. Must extract the @@ -1042,6 +1047,17 @@ RelationInitIndexAccessInfo(Relation relation) amstrategies, amsupport, natts); /* + * Similarly extract indoption and copy it to the cache entry + */ + indoptionDatum = fastgetattr(relation->rd_indextuple, + Anum_pg_index_indoption, + GetPgIndexDescriptor(), + &isnull); + Assert(!isnull); + indoption = (int2vector *) DatumGetPointer(indoptionDatum); + memcpy(relation->rd_indoption, indoption->values, natts * sizeof(int16)); + + /* * expressions and predicate cache will be filled later */ relation->rd_indexprs = NIL; @@ -3237,6 +3253,7 @@ load_relcache_init_file(void) Oid *operator; RegProcedure *support; int nsupport; + int16 *indoption; /* Count nailed indexes to ensure we have 'em all */ if (rel->rd_isnailed) @@ -3304,7 +3321,7 @@ load_relcache_init_file(void) rel->rd_operator = operator; - /* finally, read the vector of support procedures */ + /* next, read the vector of support procedures */ if ((nread = fread(&len, 1, sizeof(len), fp)) != sizeof(len)) goto read_failed; support = (RegProcedure *) MemoryContextAlloc(indexcxt, len); @@ -3313,6 +3330,16 @@ load_relcache_init_file(void) rel->rd_support = support; + /* finally, read the vector of indoption values */ + if ((nread = fread(&len, 1, sizeof(len), fp)) != sizeof(len)) + goto read_failed; + + indoption = (int16 *) MemoryContextAlloc(indexcxt, len); + if ((nread = fread(indoption, 1, len, fp)) != len) + goto read_failed; + + rel->rd_indoption = indoption; + /* set up zeroed fmgr-info vectors */ rel->rd_aminfo = (RelationAmInfo *) MemoryContextAllocZero(indexcxt, sizeof(RelationAmInfo)); @@ -3336,6 +3363,7 @@ load_relcache_init_file(void) Assert(rel->rd_operator == NULL); Assert(rel->rd_support == NULL); Assert(rel->rd_supportinfo == NULL); + Assert(rel->rd_indoption == NULL); } /* @@ -3525,10 +3553,15 @@ write_relcache_init_file(void) relform->relnatts * (am->amstrategies * sizeof(Oid)), fp); - /* finally, write the vector of support procedures */ + /* next, write the vector of support procedures */ write_item(rel->rd_support, relform->relnatts * (am->amsupport * sizeof(RegProcedure)), fp); + + /* finally, write the vector of indoption values */ + write_item(rel->rd_indoption, + relform->relnatts * sizeof(int16), + fp); } /* also make a list of their OIDs, for RelationIdIsInInitFile */ diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index f410aedda25..63c2fec28ea 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -91,7 +91,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/sort/tuplesort.c,v 1.72 2007/01/05 22:19:47 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/sort/tuplesort.c,v 1.73 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -315,7 +315,6 @@ struct Tuplesortstate */ TupleDesc tupDesc; ScanKey scanKeys; /* array of length nKeys */ - SortFunctionKind *sortFnKinds; /* array of length nKeys */ /* * These variables are specific to the IndexTuple case; they are set by @@ -330,9 +329,8 @@ struct Tuplesortstate * tuplesort_begin_datum and used only by the DatumTuple routines. */ Oid datumType; - Oid sortOperator; FmgrInfo sortOpFn; /* cached lookup data for sortOperator */ - SortFunctionKind sortFnKind; + int sortFnFlags; /* equivalent to sk_flags */ /* we need typelen and byval in order to know how to copy the Datums. */ int datumTypeLen; bool datumTypeByVal; @@ -515,8 +513,8 @@ tuplesort_begin_common(int workMem, bool randomAccess) Tuplesortstate * tuplesort_begin_heap(TupleDesc tupDesc, - int nkeys, - Oid *sortOperators, AttrNumber *attNums, + int nkeys, AttrNumber *attNums, + Oid *sortOperators, bool *nullsFirstFlags, int workMem, bool randomAccess) { Tuplesortstate *state = tuplesort_begin_common(workMem, randomAccess); @@ -543,19 +541,19 @@ tuplesort_begin_heap(TupleDesc tupDesc, state->tupDesc = tupDesc; /* assume we need not copy tupDesc */ state->scanKeys = (ScanKey) palloc0(nkeys * sizeof(ScanKeyData)); - state->sortFnKinds = (SortFunctionKind *) - palloc0(nkeys * sizeof(SortFunctionKind)); for (i = 0; i < nkeys; i++) { - RegProcedure sortFunction; + Oid sortFunction; + bool reverse; - AssertArg(sortOperators[i] != 0); AssertArg(attNums[i] != 0); + AssertArg(sortOperators[i] != 0); - /* select a function that implements the sort operator */ - SelectSortFunction(sortOperators[i], &sortFunction, - &state->sortFnKinds[i]); + if (!get_op_compare_function(sortOperators[i], + &sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperators[i]); /* * We needn't fill in sk_strategy or sk_subtype since these scankeys @@ -566,6 +564,12 @@ tuplesort_begin_heap(TupleDesc tupDesc, InvalidStrategy, sortFunction, (Datum) 0); + + /* However, we use btree's conventions for encoding directionality */ + if (reverse) + state->scanKeys[i].sk_flags |= SK_BT_DESC; + if (nullsFirstFlags[i]) + state->scanKeys[i].sk_flags |= SK_BT_NULLS_FIRST; } MemoryContextSwitchTo(oldcontext); @@ -610,12 +614,13 @@ tuplesort_begin_index(Relation indexRel, Tuplesortstate * tuplesort_begin_datum(Oid datumType, - Oid sortOperator, + Oid sortOperator, bool nullsFirstFlag, int workMem, bool randomAccess) { Tuplesortstate *state = tuplesort_begin_common(workMem, randomAccess); MemoryContext oldcontext; - RegProcedure sortFunction; + Oid sortFunction; + bool reverse; int16 typlen; bool typbyval; @@ -636,13 +641,19 @@ tuplesort_begin_datum(Oid datumType, state->readtup = readtup_datum; state->datumType = datumType; - state->sortOperator = sortOperator; - /* select a function that implements the sort operator */ - SelectSortFunction(sortOperator, &sortFunction, &state->sortFnKind); - /* and look up the function */ + /* lookup the ordering function */ + if (!get_op_compare_function(sortOperator, + &sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperator); fmgr_info(sortFunction, &state->sortOpFn); + /* set ordering flags */ + state->sortFnFlags = reverse ? SK_BT_DESC : 0; + if (nullsFirstFlag) + state->sortFnFlags |= SK_BT_NULLS_FIRST; + /* lookup necessary attributes of the datum type */ get_typlenbyval(datumType, &typlen, &typbyval); state->datumTypeLen = typlen; @@ -2083,106 +2094,26 @@ markrunend(Tuplesortstate *state, int tapenum) /* - * This routine selects an appropriate sorting function to implement - * a sort operator as efficiently as possible. The straightforward - * method is to use the operator's implementation proc --- ie, "<" - * comparison. However, that way often requires two calls of the function - * per comparison. If we can find a btree three-way comparator function - * associated with the operator, we can use it to do the comparisons - * more efficiently. We also support the possibility that the operator - * is ">" (descending sort), in which case we have to reverse the output - * of the btree comparator. - * - * Possibly this should live somewhere else (backend/catalog/, maybe?). + * Set up for an external caller of ApplySortFunction. This function + * basically just exists to localize knowledge of the encoding of sk_flags + * used in this module. */ void SelectSortFunction(Oid sortOperator, - RegProcedure *sortFunction, - SortFunctionKind *kind) + bool nulls_first, + Oid *sortFunction, + int *sortFlags) { - CatCList *catlist; - int i; - HeapTuple tuple; - Form_pg_operator optup; - Oid opfamily = InvalidOid; - Oid opinputtype = InvalidOid; + bool reverse; - /* - * Search pg_amop to see if the target operator is registered as a "<" - * or ">" operator of any btree opfamily. It's possible that it might be - * registered both ways (eg, if someone were to build a "reverse sort" - * opfamily); prefer the "<" case if so. If the operator is registered the - * same way in multiple opfamilies, assume we can use the associated - * comparator function from any one. - */ - catlist = SearchSysCacheList(AMOPOPID, 1, - ObjectIdGetDatum(sortOperator), - 0, 0, 0); - - for (i = 0; i < catlist->n_members; i++) - { - Form_pg_amop aform; + if (!get_op_compare_function(sortOperator, + sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperator); - tuple = &catlist->members[i]->tuple; - aform = (Form_pg_amop) GETSTRUCT(tuple); - - /* must be btree */ - if (aform->amopmethod != BTREE_AM_OID) - continue; - /* mustn't be cross-datatype, either */ - if (aform->amoplefttype != aform->amoprighttype) - continue; - - if (aform->amopstrategy == BTLessStrategyNumber) - { - opfamily = aform->amopfamily; - opinputtype = aform->amoplefttype; - *kind = SORTFUNC_CMP; - break; /* done looking */ - } - else if (aform->amopstrategy == BTGreaterStrategyNumber) - { - opfamily = aform->amopfamily; - opinputtype = aform->amoplefttype; - *kind = SORTFUNC_REVCMP; - /* keep scanning in hopes of finding a BTLess entry */ - } - } - - ReleaseSysCacheList(catlist); - - if (OidIsValid(opfamily)) - { - /* Found a suitable opfamily, get the matching comparator function */ - *sortFunction = get_opfamily_proc(opfamily, - opinputtype, - opinputtype, - BTORDER_PROC); - Assert(RegProcedureIsValid(*sortFunction)); - return; - } - - /* - * Can't find a comparator, so use the operator as-is. Decide whether it - * is forward or reverse sort by looking at its name (grotty, but this - * only matters for deciding which end NULLs should get sorted to). XXX - * possibly better idea: see whether its selectivity function is - * scalargtcmp? - */ - tuple = SearchSysCache(OPEROID, - ObjectIdGetDatum(sortOperator), - 0, 0, 0); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for operator %u", sortOperator); - optup = (Form_pg_operator) GETSTRUCT(tuple); - if (strcmp(NameStr(optup->oprname), ">") == 0) - *kind = SORTFUNC_REVLT; - else - *kind = SORTFUNC_LT; - *sortFunction = optup->oprcode; - ReleaseSysCache(tuple); - - Assert(RegProcedureIsValid(*sortFunction)); + *sortFlags = reverse ? SK_BT_DESC : 0; + if (nulls_first) + *sortFlags |= SK_BT_NULLS_FIRST; } /* @@ -2213,74 +2144,42 @@ myFunctionCall2(FmgrInfo *flinfo, Datum arg1, Datum arg2) /* * Apply a sort function (by now converted to fmgr lookup form) * and return a 3-way comparison result. This takes care of handling - * NULLs and sort ordering direction properly. + * reverse-sort and NULLs-ordering properly. We assume that DESC and + * NULLS_FIRST options are encoded in sk_flags the same way btree does it. */ static inline int32 -inlineApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +inlineApplySortFunction(FmgrInfo *sortFunction, int sk_flags, Datum datum1, bool isNull1, Datum datum2, bool isNull2) { - switch (kind) - { - case SORTFUNC_LT: - if (isNull1) - { - if (isNull2) - return 0; - return 1; /* NULL sorts after non-NULL */ - } - if (isNull2) - return -1; - if (DatumGetBool(myFunctionCall2(sortFunction, datum1, datum2))) - return -1; /* a < b */ - if (DatumGetBool(myFunctionCall2(sortFunction, datum2, datum1))) - return 1; /* a > b */ - return 0; - - case SORTFUNC_REVLT: - /* We reverse the ordering of NULLs, but not the operator */ - if (isNull1) - { - if (isNull2) - return 0; - return -1; /* NULL sorts before non-NULL */ - } - if (isNull2) - return 1; - if (DatumGetBool(myFunctionCall2(sortFunction, datum1, datum2))) - return -1; /* a < b */ - if (DatumGetBool(myFunctionCall2(sortFunction, datum2, datum1))) - return 1; /* a > b */ - return 0; - - case SORTFUNC_CMP: - if (isNull1) - { - if (isNull2) - return 0; - return 1; /* NULL sorts after non-NULL */ - } - if (isNull2) - return -1; - return DatumGetInt32(myFunctionCall2(sortFunction, - datum1, datum2)); + int32 compare; - case SORTFUNC_REVCMP: - if (isNull1) - { - if (isNull2) - return 0; - return -1; /* NULL sorts before non-NULL */ - } - if (isNull2) - return 1; - return -DatumGetInt32(myFunctionCall2(sortFunction, - datum1, datum2)); + if (isNull1) + { + if (isNull2) + compare = 0; /* NULL "=" NULL */ + else if (sk_flags & SK_BT_NULLS_FIRST) + compare = -1; /* NULL "<" NOT_NULL */ + else + compare = 1; /* NULL ">" NOT_NULL */ + } + else if (isNull2) + { + if (sk_flags & SK_BT_NULLS_FIRST) + compare = 1; /* NOT_NULL ">" NULL */ + else + compare = -1; /* NOT_NULL "<" NULL */ + } + else + { + compare = DatumGetInt32(myFunctionCall2(sortFunction, + datum1, datum2)); - default: - elog(ERROR, "unrecognized SortFunctionKind: %d", (int) kind); - return 0; /* can't get here, but keep compiler quiet */ + if (sk_flags & SK_BT_DESC) + compare = -compare; } + + return compare; } /* @@ -2288,11 +2187,11 @@ inlineApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, * C99's brain-dead notions about how to implement inline functions... */ int32 -ApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +ApplySortFunction(FmgrInfo *sortFunction, int sortFlags, Datum datum1, bool isNull1, Datum datum2, bool isNull2) { - return inlineApplySortFunction(sortFunction, kind, + return inlineApplySortFunction(sortFunction, sortFlags, datum1, isNull1, datum2, isNull2); } @@ -2316,8 +2215,7 @@ comparetup_heap(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) CHECK_FOR_INTERRUPTS(); /* Compare the leading sort key */ - compare = inlineApplySortFunction(&scanKey->sk_func, - state->sortFnKinds[0], + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, a->datum1, a->isnull1, b->datum1, b->isnull1); if (compare != 0) @@ -2341,8 +2239,7 @@ comparetup_heap(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) datum1 = heap_getattr(<up, attno, tupDesc, &isnull1); datum2 = heap_getattr(&rtup, attno, tupDesc, &isnull2); - compare = inlineApplySortFunction(&scanKey->sk_func, - state->sortFnKinds[nkey], + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, datum1, isnull1, datum2, isnull2); if (compare != 0) @@ -2457,8 +2354,7 @@ comparetup_index(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) CHECK_FOR_INTERRUPTS(); /* Compare the leading sort key */ - compare = inlineApplySortFunction(&scanKey->sk_func, - SORTFUNC_CMP, + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, a->datum1, a->isnull1, b->datum1, b->isnull1); if (compare != 0) @@ -2484,14 +2380,9 @@ comparetup_index(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) datum1 = index_getattr(tuple1, nkey, tupDes, &isnull1); datum2 = index_getattr(tuple2, nkey, tupDes, &isnull2); - /* see comments about NULLs handling in btbuild */ - - /* the comparison function is always of CMP type */ - compare = inlineApplySortFunction(&scanKey->sk_func, - SORTFUNC_CMP, + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, datum1, isnull1, datum2, isnull2); - if (compare != 0) return compare; /* done when we find unequal attributes */ @@ -2617,7 +2508,7 @@ comparetup_datum(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) /* Allow interrupting long sorts */ CHECK_FOR_INTERRUPTS(); - return inlineApplySortFunction(&state->sortOpFn, state->sortFnKind, + return inlineApplySortFunction(&state->sortOpFn, state->sortFnFlags, a->datum1, a->isnull1, b->datum1, b->isnull1); } diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 991fc14588c..435826cf457 100644 --- a/src/include/access/nbtree.h +++ b/src/include/access/nbtree.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.107 2007/01/05 22:19:51 momjian Exp $ + * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.108 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -327,7 +327,11 @@ typedef struct xl_btree_newroot /* * Operator strategy numbers for B-tree have been moved to access/skey.h, * because many places need to use them in ScanKeyInit() calls. + * + * The strategy numbers are chosen so that we can commute them by + * subtraction, thus: */ +#define BTCommuteStrategyNumber(strat) (BTMaxStrategyNumber + 1 - (strat)) /* * When a new operator class is declared, we require that the user @@ -458,11 +462,15 @@ typedef struct BTScanOpaqueData typedef BTScanOpaqueData *BTScanOpaque; /* - * We use these private sk_flags bits in preprocessed scan keys + * We use some private sk_flags bits in preprocessed scan keys. We're allowed + * to use bits 16-31 (see skey.h). The uppermost bits are copied from the + * index's indoption[] array entry for the index attribute. */ #define SK_BT_REQFWD 0x00010000 /* required to continue forward scan */ #define SK_BT_REQBKWD 0x00020000 /* required to continue backward scan */ - +#define SK_BT_INDOPTION_SHIFT 24 /* must clear the above bits */ +#define SK_BT_DESC (INDOPTION_DESC << SK_BT_INDOPTION_SHIFT) +#define SK_BT_NULLS_FIRST (INDOPTION_NULLS_FIRST << SK_BT_INDOPTION_SHIFT) /* * prototypes for functions in nbtree.c (external entry points for btree) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index fd67dfd27af..98a625cfbf1 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.370 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.371 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200701021 +#define CATALOG_VERSION_NO 200701081 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 8848ba12d65..32c704b71e6 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.72 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.73 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -35,6 +35,7 @@ extern Oid index_create(Oid heapRelationId, Oid accessMethodObjectId, Oid tableSpaceId, Oid *classObjectId, + int16 *coloptions, Datum reloptions, bool isprimary, bool isconstraint, diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h index a0dbc20f407..80aad731302 100644 --- a/src/include/catalog/pg_am.h +++ b/src/include/catalog/pg_am.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_am.h,v 1.48 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_am.h,v 1.49 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -39,15 +39,18 @@ CATALOG(pg_am,2601) { NameData amname; /* access method name */ - int2 amstrategies; /* total NUMBER of strategies (operators) by + int2 amstrategies; /* total number of strategies (operators) by * which we can traverse/search this AM. * Zero if AM does not have a fixed set of * strategy assignments. */ - int2 amsupport; /* total NUMBER of support functions that this + int2 amsupport; /* total number of support functions that this * AM uses */ int2 amorderstrategy;/* if this AM has a sort order, the strategy - * number of the sort operator. Zero if AM is - * not ordered. */ + * number of the default (ASC) sort operator. + * Zero if AM is not ordered. */ + int2 amdescorder; /* if this AM has a sort order, the strategy + * number of the DESC sort operator. + * Zero if AM is not ordered. */ bool amcanunique; /* does AM support UNIQUE indexes? */ bool amcanmulticol; /* does AM support multi-column indexes? */ bool amoptionalkey; /* can query omit key for the first column? */ @@ -80,46 +83,47 @@ typedef FormData_pg_am *Form_pg_am; * compiler constants for pg_am * ---------------- */ -#define Natts_pg_am 23 +#define Natts_pg_am 24 #define Anum_pg_am_amname 1 #define Anum_pg_am_amstrategies 2 #define Anum_pg_am_amsupport 3 #define Anum_pg_am_amorderstrategy 4 -#define Anum_pg_am_amcanunique 5 -#define Anum_pg_am_amcanmulticol 6 -#define Anum_pg_am_amoptionalkey 7 -#define Anum_pg_am_amindexnulls 8 -#define Anum_pg_am_amstorage 9 -#define Anum_pg_am_amclusterable 10 -#define Anum_pg_am_aminsert 11 -#define Anum_pg_am_ambeginscan 12 -#define Anum_pg_am_amgettuple 13 -#define Anum_pg_am_amgetmulti 14 -#define Anum_pg_am_amrescan 15 -#define Anum_pg_am_amendscan 16 -#define Anum_pg_am_ammarkpos 17 -#define Anum_pg_am_amrestrpos 18 -#define Anum_pg_am_ambuild 19 -#define Anum_pg_am_ambulkdelete 20 -#define Anum_pg_am_amvacuumcleanup 21 -#define Anum_pg_am_amcostestimate 22 -#define Anum_pg_am_amoptions 23 +#define Anum_pg_am_amdescorder 5 +#define Anum_pg_am_amcanunique 6 +#define Anum_pg_am_amcanmulticol 7 +#define Anum_pg_am_amoptionalkey 8 +#define Anum_pg_am_amindexnulls 9 +#define Anum_pg_am_amstorage 10 +#define Anum_pg_am_amclusterable 11 +#define Anum_pg_am_aminsert 12 +#define Anum_pg_am_ambeginscan 13 +#define Anum_pg_am_amgettuple 14 +#define Anum_pg_am_amgetmulti 15 +#define Anum_pg_am_amrescan 16 +#define Anum_pg_am_amendscan 17 +#define Anum_pg_am_ammarkpos 18 +#define Anum_pg_am_amrestrpos 19 +#define Anum_pg_am_ambuild 20 +#define Anum_pg_am_ambulkdelete 21 +#define Anum_pg_am_amvacuumcleanup 22 +#define Anum_pg_am_amcostestimate 23 +#define Anum_pg_am_amoptions 24 /* ---------------- * initial contents of pg_am * ---------------- */ -DATA(insert OID = 403 ( btree 5 1 1 t t t t f t btinsert btbeginscan btgettuple btgetmulti btrescan btendscan btmarkpos btrestrpos btbuild btbulkdelete btvacuumcleanup btcostestimate btoptions )); +DATA(insert OID = 403 ( btree 5 1 1 5 t t t t f t btinsert btbeginscan btgettuple btgetmulti btrescan btendscan btmarkpos btrestrpos btbuild btbulkdelete btvacuumcleanup btcostestimate btoptions )); DESCR("b-tree index access method"); #define BTREE_AM_OID 403 -DATA(insert OID = 405 ( hash 1 1 0 f f f f f f hashinsert hashbeginscan hashgettuple hashgetmulti hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbulkdelete hashvacuumcleanup hashcostestimate hashoptions )); +DATA(insert OID = 405 ( hash 1 1 0 0 f f f f f f hashinsert hashbeginscan hashgettuple hashgetmulti hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbulkdelete hashvacuumcleanup hashcostestimate hashoptions )); DESCR("hash index access method"); #define HASH_AM_OID 405 -DATA(insert OID = 783 ( gist 0 7 0 f t t t t t gistinsert gistbeginscan gistgettuple gistgetmulti gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbulkdelete gistvacuumcleanup gistcostestimate gistoptions )); +DATA(insert OID = 783 ( gist 0 7 0 0 f t t t t t gistinsert gistbeginscan gistgettuple gistgetmulti gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbulkdelete gistvacuumcleanup gistcostestimate gistoptions )); DESCR("GiST index access method"); #define GIST_AM_OID 783 -DATA(insert OID = 2742 ( gin 0 4 0 f f f f t f gininsert ginbeginscan gingettuple gingetmulti ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbulkdelete ginvacuumcleanup gincostestimate ginoptions )); +DATA(insert OID = 2742 ( gin 0 4 0 0 f f f f t f gininsert ginbeginscan gingettuple gingetmulti ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbulkdelete ginvacuumcleanup gincostestimate ginoptions )); DESCR("GIN index access method"); #define GIN_AM_OID 2742 diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index 413ec569b51..463e10a5da1 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.128 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.129 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -465,7 +465,8 @@ DATA(insert ( 1259 tableoid 26 0 4 -7 0 -1 -1 t p i t f f t 0)); { 0, {"indisvalid"}, 16, -1, 1, 7, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indkey"}, 22, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ { 0, {"indclass"}, 30, -1, -1, 9, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ -{ 0, {"indexprs"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ -{ 0, {"indpred"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } +{ 0, {"indoption"}, 22, -1, -1, 10, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ +{ 0, {"indexprs"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ +{ 0, {"indpred"}, 25, -1, -1, 12, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } #endif /* PG_ATTRIBUTE_H */ diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index d88b9c58e71..31c6e25fb0d 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.42 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.43 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -46,6 +46,7 @@ CATALOG(pg_index,2610) BKI_WITHOUT_OIDS /* VARIABLE LENGTH FIELDS: */ int2vector indkey; /* column numbers of indexed cols, or 0 */ oidvector indclass; /* opclass identifiers */ + int2vector indoption; /* per-column flags (AM-specific meanings) */ text indexprs; /* expression trees for index attributes that * are not simple column references; one for * each zero entry in indkey[] */ @@ -64,7 +65,7 @@ typedef FormData_pg_index *Form_pg_index; * compiler constants for pg_index * ---------------- */ -#define Natts_pg_index 11 +#define Natts_pg_index 12 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 @@ -74,7 +75,16 @@ typedef FormData_pg_index *Form_pg_index; #define Anum_pg_index_indisvalid 7 #define Anum_pg_index_indkey 8 #define Anum_pg_index_indclass 9 -#define Anum_pg_index_indexprs 10 -#define Anum_pg_index_indpred 11 +#define Anum_pg_index_indoption 10 +#define Anum_pg_index_indexprs 11 +#define Anum_pg_index_indpred 12 + +/* + * Index AMs that support ordered scans must support these two indoption + * bits. Otherwise, the content of the per-column indoption fields is + * open for future definition. + */ +#define INDOPTION_DESC 0x0001 /* values are in reverse order */ +#define INDOPTION_NULLS_FIRST 0x0002 /* NULLs are first instead of last */ #endif /* PG_INDEX_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e1fe6c0ddba..d11f9ae6ead 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.337 2007/01/05 22:19:55 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.338 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,6 +36,22 @@ typedef enum OnCommitAction ONCOMMIT_DROP /* ON COMMIT DROP */ } OnCommitAction; +/* Sort ordering options for ORDER BY and CREATE INDEX */ +typedef enum SortByDir +{ + SORTBY_DEFAULT, + SORTBY_ASC, + SORTBY_DESC, + SORTBY_USING /* not allowed in CREATE INDEX ... */ +} SortByDir; + +typedef enum SortByNulls +{ + SORTBY_NULLS_DEFAULT, + SORTBY_NULLS_FIRST, + SORTBY_NULLS_LAST +} SortByNulls; + /* * Grantable rights are encoded so that we can OR them together in a bitmask. @@ -348,14 +364,11 @@ typedef struct ResTarget /* * SortBy - for ORDER BY clause */ -#define SORTBY_ASC 1 -#define SORTBY_DESC 2 -#define SORTBY_USING 3 - typedef struct SortBy { NodeTag type; - int sortby_kind; /* see codes above */ + SortByDir sortby_dir; /* ASC/DESC/USING */ + SortByNulls sortby_nulls; /* NULLS FIRST/LAST */ List *useOp; /* name of op to use, if SORTBY_USING */ Node *node; /* expression to sort on */ } SortBy; @@ -443,6 +456,8 @@ typedef struct IndexElem char *name; /* name of attribute to index, or NULL */ Node *expr; /* expression to index, or NULL */ List *opclass; /* name of desired opclass; NIL = default */ + SortByDir ordering; /* ASC/DESC/default */ + SortByNulls nulls_ordering; /* FIRST/LAST/default */ } IndexElem; /* @@ -614,7 +629,8 @@ typedef struct RangeTblEntry * * tleSortGroupRef must match ressortgroupref of exactly one entry of the * associated targetlist; that is the expression to be sorted (or grouped) by. - * sortop is the OID of the ordering operator. + * sortop is the OID of the ordering operator (a "<" or ">" operator). + * nulls_first does about what you'd expect. * * SortClauses are also used to identify targets that we will do a "Unique" * filter step on (for SELECT DISTINCT and SELECT DISTINCT ON). The @@ -627,16 +643,21 @@ typedef struct SortClause { NodeTag type; Index tleSortGroupRef; /* reference into targetlist */ - Oid sortop; /* the sort operator to use */ + Oid sortop; /* the ordering operator ('<' op) */ + bool nulls_first; /* do NULLs come before normal values? */ } SortClause; /* * GroupClause - * representation of GROUP BY clauses * - * GroupClause is exactly like SortClause except for the nodetag value - * (it's probably not even really necessary to have two different - * nodetags...). We have routines that operate interchangeably on both. + * GroupClause is exactly like SortClause except for the nodetag value. + * We have routines that operate interchangeably on both. + * + * XXX SortClause overspecifies the semantics so far as GROUP BY is concerned + * (ditto for DISTINCT). It'd be better to specify an equality operator not + * an ordering operator. However, the two implementations are tightly entwined + * at the moment ... breaking them apart is work for another day. */ typedef SortClause GroupClause; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index acb73a39ed6..44002a9d45d 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.87 2007/01/05 22:19:55 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.88 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -385,6 +385,7 @@ typedef struct Sort int numCols; /* number of sort-key columns */ AttrNumber *sortColIdx; /* their indexes in the target list */ Oid *sortOperators; /* OIDs of operators to sort them by */ + bool *nullsFirst; /* NULLS FIRST/LAST directions */ } Sort; /* --------------- diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 75fb572de0f..4e285a765ad 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.130 2007/01/05 22:19:56 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.131 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -300,19 +300,23 @@ typedef struct RelOptInfo * and indexes, but that created confusion without actually doing anything * useful. So now we have a separate IndexOptInfo struct for indexes. * - * opfamily[], indexkeys[], and ordering[] have ncolumns entries. + * opfamily[], indexkeys[], fwdsortop[], revsortop[], and nulls_first[] + * each have ncolumns entries. Note: for historical reasons, the + * opfamily array has an extra entry that is always zero. Some code + * scans until it sees a zero entry, rather than looking at ncolumns. + * * Zeroes in the indexkeys[] array indicate index columns that are * expressions; there is one element in indexprs for each such column. * - * Note: for historical reasons, the opfamily and ordering arrays have - * an extra entry that is always zero. Some code scans until it sees a - * zero entry, rather than looking at ncolumns. + * For an unordered index, the sortop arrays contains zeroes. Note that + * fwdsortop[] and nulls_first[] describe the sort ordering of a forward + * indexscan; we can also consider a backward indexscan, which will + * generate sort order described by revsortop/!nulls_first. * * The indexprs and indpred expressions have been run through * prepqual.c and eval_const_expressions() for ease of matching to - * WHERE clauses. indpred is in implicit-AND form. + * WHERE clauses. indpred is in implicit-AND form. */ - typedef struct IndexOptInfo { NodeTag type; @@ -328,7 +332,9 @@ typedef struct IndexOptInfo int ncolumns; /* number of columns in index */ Oid *opfamily; /* OIDs of operator families for columns */ int *indexkeys; /* column numbers of index's keys, or 0 */ - Oid *ordering; /* OIDs of sort operators for each column */ + Oid *fwdsortop; /* OIDs of sort operators for each column */ + Oid *revsortop; /* OIDs of sort operators for backward scan */ + bool *nulls_first; /* do NULLs come first in the sort order? */ Oid relam; /* OID of the access method (in pg_am) */ RegProcedure amcostestimate; /* OID of the access method's cost fcn */ @@ -360,6 +366,7 @@ typedef struct PathKeyItem Node *key; /* the item that is ordered */ Oid sortop; /* the ordering operator ('<' op) */ + bool nulls_first; /* do NULLs come before normal values? */ /* * key typically points to a Var node, ie a relation attribute, but it can diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index f59f031db3c..06309613682 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.47 2007/01/05 22:19:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.48 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -38,9 +38,9 @@ extern List *addAllTargetsToSortList(ParseState *pstate, bool resolveUnknown); extern List *addTargetToSortList(ParseState *pstate, TargetEntry *tle, List *sortlist, List *targetlist, - int sortby_kind, List *sortby_opname, - bool resolveUnknown); + SortByDir sortby_dir, SortByNulls sortby_nulls, + List *sortby_opname, bool resolveUnknown); extern Index assignSortGroupRef(TargetEntry *tle, List *tlist); -extern bool targetIsInSortList(TargetEntry *tle, List *sortList); +extern bool targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList); #endif /* PARSE_CLAUSE_H */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 41e0c5162fb..15d2b8a06d9 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.110 2007/01/05 22:19:59 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.111 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -37,6 +37,7 @@ extern Oid get_opfamily_member(Oid opfamily, Oid lefttype, Oid righttype, int16 strategy); extern bool get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, Oid *right_sortop, Oid *opfamily); +extern bool get_op_compare_function(Oid opno, Oid *cmpfunc, bool *reverse); extern Oid get_op_hash_function(Oid opno); extern void get_op_btree_interpretation(Oid opno, List **opfamilies, List **opstrats); diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index ae81b7483e1..c8b78b95422 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.94 2007/01/05 22:19:59 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.95 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -189,6 +189,7 @@ typedef struct RelationData Oid *rd_operator; /* OIDs of index operators */ RegProcedure *rd_support; /* OIDs of support procedures */ FmgrInfo *rd_supportinfo; /* lookup info for support procedures */ + int16 *rd_indoption; /* per-column AM-specific flags */ List *rd_indexprs; /* index expression trees, if any */ List *rd_indpred; /* index predicate tree, if any */ void *rd_amcache; /* available for use by index AM */ diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h index 2ee315d8557..cea50b4836b 100644 --- a/src/include/utils/tuplesort.h +++ b/src/include/utils/tuplesort.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/tuplesort.h,v 1.24 2007/01/05 22:20:00 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/tuplesort.h,v 1.25 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -45,14 +45,14 @@ typedef struct Tuplesortstate Tuplesortstate; */ extern Tuplesortstate *tuplesort_begin_heap(TupleDesc tupDesc, - int nkeys, - Oid *sortOperators, AttrNumber *attNums, + int nkeys, AttrNumber *attNums, + Oid *sortOperators, bool *nullsFirstFlags, int workMem, bool randomAccess); extern Tuplesortstate *tuplesort_begin_index(Relation indexRel, bool enforceUnique, int workMem, bool randomAccess); extern Tuplesortstate *tuplesort_begin_datum(Oid datumType, - Oid sortOperator, + Oid sortOperator, bool nullsFirstFlag, int workMem, bool randomAccess); extern void tuplesort_puttupleslot(Tuplesortstate *state, @@ -84,28 +84,17 @@ extern void tuplesort_rescan(Tuplesortstate *state); extern void tuplesort_markpos(Tuplesortstate *state); extern void tuplesort_restorepos(Tuplesortstate *state); -/* - * This routine selects an appropriate sorting function to implement - * a sort operator as efficiently as possible. - */ -typedef enum -{ - SORTFUNC_LT, /* raw "<" operator */ - SORTFUNC_REVLT, /* raw "<" operator, but reverse NULLs */ - SORTFUNC_CMP, /* -1 / 0 / 1 three-way comparator */ - SORTFUNC_REVCMP /* 1 / 0 / -1 (reversed) 3-way comparator */ -} SortFunctionKind; - -extern void SelectSortFunction(Oid sortOperator, - RegProcedure *sortFunction, - SortFunctionKind *kind); +/* Setup for ApplySortFunction */ +extern void SelectSortFunction(Oid sortOperator, bool nulls_first, + Oid *sortFunction, + int *sortFlags); /* * Apply a sort function (by now converted to fmgr lookup form) * and return a 3-way comparison result. This takes care of handling - * NULLs and sort ordering direction properly. + * reverse-sort and NULLs-ordering properly. */ -extern int32 ApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +extern int32 ApplySortFunction(FmgrInfo *sortFunction, int sortFlags, Datum datum1, bool isNull1, Datum datum2, bool isNull2); diff --git a/src/test/regress/expected/circle.out b/src/test/regress/expected/circle.out index 0f8cf741e8c..a63e348acaa 100644 --- a/src/test/regress/expected/circle.out +++ b/src/test/regress/expected/circle.out @@ -81,7 +81,7 @@ SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <-> c2.f1) AS distance FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE (c1.f1 < c2.f1) AND ((c1.f1 <-> c2.f1) > 0) - ORDER BY distance, one USING < , two USING < ; + ORDER BY distance, area(c1.f1), area(c2.f1); five | one | two | distance ------+----------------+----------------+------------------ | <(100,200),10> | <(100,1),115> | 74 diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index c19794e67ed..fff65adfb60 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -62,11 +62,11 @@ SET enable_indexscan = OFF; SET enable_bitmapscan = OFF; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; home_base ----------------------- - (1444,403),(1346,344) (337,455),(240,359) + (1444,403),(1346,344) (2 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; @@ -76,14 +76,14 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; (1 row) SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; f1 --------------------- ((2,0),(2,4),(0,0)) (1 row) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); f1 --------------- <(1,2),3> @@ -112,11 +112,11 @@ SET enable_bitmapscan = ON; -- changes too often for me to want to put an EXPLAIN in the test...) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; home_base ----------------------- - (1444,403),(1346,344) (337,455),(240,359) + (1444,403),(1346,344) (2 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; @@ -126,14 +126,14 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; (1 row) SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; f1 --------------------- ((2,0),(2,4),(0,0)) (1 row) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); f1 --------------- <(1,2),3> diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out index 79763f81003..f307788cf14 100644 --- a/src/test/regress/expected/geometry.out +++ b/src/test/regress/expected/geometry.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/geometry_1.out b/src/test/regress/expected/geometry_1.out index 81e6b535ef3..3c7234b2b4d 100644 --- a/src/test/regress/expected/geometry_1.out +++ b/src/test/regress/expected/geometry_1.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/geometry_2.out b/src/test/regress/expected/geometry_2.out index bcc405e8c7d..7daddc4a420 100644 --- a/src/test/regress/expected/geometry_2.out +++ b/src/test/regress/expected/geometry_2.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/point.out b/src/test/regress/expected/point.out index 552be515d6f..96bcc6d695c 100644 --- a/src/test/regress/expected/point.out +++ b/src/test/regress/expected/point.out @@ -108,7 +108,7 @@ SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist SET geqo TO 'off'; SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist FROM POINT_TBL p1, POINT_TBL p2 - ORDER BY dist, point1 using <<, point2 using <<; + ORDER BY dist, p1.f1[0], p2.f1[0]; thirtysix | point1 | point2 | dist -----------+------------+------------+------------------ | (-10,0) | (-10,0) | 0 @@ -190,7 +190,7 @@ SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 - ORDER BY distance, point1 using <<, point2 using <<; + ORDER BY distance, p1.f1[0], p2.f1[0]; fifteen | point1 | point2 | distance ---------+------------+------------+------------------ | (-3,4) | (0,0) | 5 diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 6fcc88860d6..0b3f546bdfb 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -513,3 +513,219 @@ SELECT * FROM int8_tbl; 4567890123456789 | -4567890123456789 (9 rows) +-- +-- Test ORDER BY options +-- +CREATE TEMP TABLE foo (f1 int); +INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 ASC; -- same thing + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +-- check if indexscans do the right things +CREATE INDEX fooi ON foo (f1); +SET enable_sort = false; +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + diff --git a/src/test/regress/sql/circle.sql b/src/test/regress/sql/circle.sql index fe229b3b2c0..c0284b2b598 100644 --- a/src/test/regress/sql/circle.sql +++ b/src/test/regress/sql/circle.sql @@ -42,4 +42,4 @@ SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <-> c2.f1) AS distance FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE (c1.f1 < c2.f1) AND ((c1.f1 <-> c2.f1) > 0) - ORDER BY distance, one USING < , two USING < ; + ORDER BY distance, area(c1.f1), area(c2.f1); diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index a4bd1db9155..70d17ec68c1 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -92,15 +92,15 @@ SET enable_bitmapscan = OFF; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; @@ -115,15 +115,15 @@ SET enable_bitmapscan = ON; -- changes too often for me to want to put an EXPLAIN in the test...) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql index c53d9116faa..523bcdb76df 100644 --- a/src/test/regress/sql/geometry.sql +++ b/src/test/regress/sql/geometry.sql @@ -152,4 +152,4 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; diff --git a/src/test/regress/sql/point.sql b/src/test/regress/sql/point.sql index efbfe6905f5..1f5f7635d19 100644 --- a/src/test/regress/sql/point.sql +++ b/src/test/regress/sql/point.sql @@ -59,7 +59,7 @@ SET geqo TO 'off'; SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist FROM POINT_TBL p1, POINT_TBL p2 - ORDER BY dist, point1 using <<, point2 using <<; + ORDER BY dist, p1.f1[0], p2.f1[0]; SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 FROM POINT_TBL p1, POINT_TBL p2 @@ -69,7 +69,7 @@ SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 - ORDER BY distance, point1 using <<, point2 using <<; + ORDER BY distance, p1.f1[0], p2.f1[0]; -- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 2c813a9d8e5..f23cccd24f9 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -138,3 +138,42 @@ UNION ALL SELECT 2+2, 57 UNION ALL SELECT * FROM int8_tbl; + +-- +-- Test ORDER BY options +-- + +CREATE TEMP TABLE foo (f1 int); + +INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 ASC; -- same thing +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +-- check if indexscans do the right things +CREATE INDEX fooi ON foo (f1); +SET enable_sort = false; + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; |