From: teodor Date: Tue, 27 Dec 2011 16:15:57 +0000 (+0000) Subject: add spgist_print X-Git-Url: http://www.sigaev.ru/git/gitweb.cgi?p=gevel.git;a=commitdiff_plain;h=3cc93ce2ee4d6fac298a059d30f6e4b11f174c3c add spgist_print --- diff --git a/README.gevel b/README.gevel index 46e7619..35b81d1 100644 --- a/README.gevel +++ b/README.gevel @@ -144,6 +144,34 @@ regression=# select gist_tree('pix'); leafRedirects: 0 + innerRedirects: 0 + * spgist_print(INDEXNAME) - prints objects stored in GiST tree, + works only if objects in index have textual representation + (type_out functions should be implemented for given object type). + Note 1. in example below we used quad_point_ops which uses point + for leaf and prefix value, but doesn't use node_label at all. + Use type 'int' as dummy type for prefix or/and node_label. + Note 2 + quad_point_ops: prefix point, node_label int, leaf_value point + kd_point_ops: prefix float, node_label int, leaf_value point + text_ops: prefix text, node_label char, leaf_value text + +# SELECT * FROM spgist_print('spgist_idx') as t + ( + tid tid, + node_n int, + level int, + tid_pointer tid, + prefix point, + node_label int, + leaf_value point + ) where level = 1; + tid | node_n | level | tid_pointer | prefix | node_label | leaf_value +-------+--------+-------+-------------+-------------------------------------+------------+------------ + (1,1) | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | | + (1,1) | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | | + (1,1) | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | | + (1,1) | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | | + * gin_stat(INDEXNAME) prints estimated counts for each indexed values Note: since 8.4 gin_stat function has gin_stat(INDEXNAME, COLNUMBER) prototype, single-argument function will return result for a first diff --git a/expected/gevel.out.9.2 b/expected/gevel.out.9.2 index 915c789..7a4d393 100644 --- a/expected/gevel.out.9.2 +++ b/expected/gevel.out.9.2 @@ -6,6 +6,7 @@ CREATE TABLE gevelt ( t box ); SELECT center(t) AS p INTO gevelp FROM gevelt; CREATE INDEX gist_idx ON gevelt USING gist ( t ); CREATE INDEX spgist_idx ON gevelp USING spgist ( p ); +CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops); --GiST SELECT gist_stat('gist_idx'); gist_stat @@ -111,6 +112,99 @@ SELECT spgist_stat('spgist_idx'); innerRedirects: 0 (1 row) +SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point); + tid | node_n | level | tid_pointer | prefix | node_label | leaf_value +----------+--------+-------+-------------+---------+------------+------------------- + (1,1) | 0 | 1 | (5,1) | 23030 | | + (1,1) | 1 | 1 | (5,2) | 23030 | | + (5,2) | 0 | 2 | (5,6) | 21664 | | + (5,2) | 1 | 2 | (5,3) | 21664 | | + (5,3) | 0 | 3 | (5,5) | 37159.5 | | + (5,3) | 1 | 3 | (5,12) | 37159.5 | | + (5,12) | 0 | 4 | (5,13) | 36357.5 | | + (5,12) | 1 | 4 | (14,226) | 36357.5 | | + (14,226) | | 5 | | | | (43240,47256.5) + (5,13) | 0 | 5 | (2,112) | 43507 | | + (5,13) | 1 | 5 | (5,25) | 43507 | | + (5,25) | 0 | 6 | (22,116) | 28862 | | + (5,25) | 1 | 6 | (22,115) | 28862 | | + (22,115) | | 7 | | | | (30902,44532) + (22,116) | | 7 | | | | (23048,47416) + (2,112) | | 6 | | | | (35218.5,42124) + (5,5) | 0 | 4 | (5,11) | 33416 | | + (5,5) | 1 | 4 | (5,22) | 33416 | | + (5,22) | 0 | 5 | (20,225) | 30991 | | + (5,22) | 1 | 5 | (5,23) | 30991 | | + (5,23) | 0 | 6 | (9,112) | 41820 | | + (5,23) | 1 | 6 | (9,114) | 41820 | | + (9,114) | | 7 | | | | (44732,32182) + (9,112) | | 7 | | | | (35580,33526.5) + (20,225) | | 6 | | | | (47724.5,27185.5) + (5,11) | 0 | 5 | (13,101) | 29986.5 | | + (5,11) | 1 | 5 | (13,100) | 29986.5 | | + (13,100) | | 6 | | | | (24069,30850.5) + (13,101) | | 6 | | | | (29539,25566) + (5,6) | 0 | 3 | (5,7) | 36774 | | + (5,6) | 1 | 3 | (5,19) | 36774 | | + (5,19) | 0 | 4 | (5,20) | 10075.5 | | + (5,19) | 1 | 4 | (18,225) | 10075.5 | | + (18,225) | | 5 | | | | (20920.5,49105.5) + (5,20) | 0 | 5 | (10,110) | 44171.5 | | + (5,20) | 1 | 5 | (10,113) | 44171.5 | | + (10,113) | | 6 | | | | (93,46797) + (10,110) | | 6 | | | | (28.5,38640.5) + (5,7) | 0 | 4 | (7,113) | 9517 | | + (5,7) | 1 | 4 | (5,21) | 9517 | | + (5,21) | 0 | 5 | (19,115) | 28907.5 | | + (5,21) | 1 | 5 | (19,112) | 28907.5 | | + (19,112) | | 6 | | | | (11916.5,31668) + (19,115) | | 6 | | | | (20622.5,27462.5) + (7,113) | | 5 | | | | (9296,35157) + (5,1) | 0 | 2 | (5,8) | 26938 | | + (5,1) | 1 | 2 | (5,4) | 26938 | | + (5,4) | 0 | 3 | (5,10) | 9532 | | + (5,4) | 1 | 3 | (5,14) | 9532 | | + (5,14) | 0 | 4 | (5,15) | 38603 | | + (5,14) | 1 | 4 | (15,222) | 38603 | | + (15,222) | | 5 | | | | (41926.5,17934.5) + (5,15) | 0 | 5 | (8,113) | 16345 | | + (5,15) | 1 | 5 | (8,112) | 16345 | | + (8,112) | | 6 | | | | (32425,20702.5) + (8,113) | | 6 | | | | (29134,15559.5) + (5,10) | 0 | 4 | (12,94) | 38800.5 | | + (5,10) | 1 | 4 | (5,24) | 38800.5 | | + (5,24) | 0 | 5 | (21,108) | 4752 | | + (5,24) | 1 | 5 | (21,107) | 4752 | | + (21,107) | | 6 | | | | (49822.5,7097.5) + (21,108) | | 6 | | | | (40315.5,1689.5) + (12,94) | | 5 | | | | (30295.5,5090) + (5,8) | 0 | 3 | (5,17) | 11733.5 | | + (5,8) | 1 | 3 | (5,9) | 11733.5 | | + (5,9) | 0 | 4 | (6,114) | 11993 | | + (5,9) | 1 | 4 | (5,16) | 11993 | | + (5,16) | 0 | 5 | (16,123) | 17591 | | + (5,16) | 1 | 5 | (16,127) | 17591 | | + (16,127) | | 6 | | | | (18352.5,19366) + (16,123) | | 6 | | | | (24795,14921) + (6,114) | | 5 | | | | (6706,16676) + (5,17) | 0 | 4 | (5,18) | 13329.5 | | + (5,17) | 1 | 4 | (17,226) | 13329.5 | | + (17,226) | | 5 | | | | (23690,10214.5) + (5,18) | 0 | 5 | (11,113) | 6375 | | + (5,18) | 1 | 5 | (11,109) | 6375 | | + (11,109) | | 6 | | | | (5501.5,9916) + (11,113) | | 6 | | | | (1072.5,4752) +(79 rows) + +SELECT * FROM spgist_print('spgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1; + tid | node_n | level | tid_pointer | prefix | node_label | leaf_value +-------+--------+-------+-------------+-------------------------------------+------------+------------ + (1,1) | 0 | 1 | (5,4) | (24530.2070484581,23595.7092511013) | | + (1,1) | 1 | 1 | (5,3) | (24530.2070484581,23595.7092511013) | | + (1,1) | 2 | 1 | (5,2) | (24530.2070484581,23595.7092511013) | | + (1,1) | 3 | 1 | (5,1) | (24530.2070484581,23595.7092511013) | | +(4 rows) + --GIN CREATE TABLE test__int( a int[] ); \copy test__int from 'data/test__int.data' diff --git a/gevel.c b/gevel.c index ef8f3d9..d13c724 100644 --- a/gevel.c +++ b/gevel.c @@ -11,6 +11,7 @@ #if PG_VERSION_NUM >= 90200 #include "access/spgist_private.h" #include "access/spgist.h" +#include "utils/datum.h" #endif #include "access/heapam.h" #include "catalog/index.h" @@ -975,3 +976,229 @@ spgist_stat(PG_FUNCTION_ARGS) #endif } +#if PG_VERSION_NUM >= 90200 + +typedef struct SPGistPrintStackElem { + ItemPointerData iptr; + int16 nlabel; + int level; +} SPGistPrintStackElem; + +typedef struct SPGistPrint { + SpGistState state; + Relation index; + Datum dvalues[7 /* see CreateTemplateTupleDesc call */]; + char nulls[7 /* see CreateTemplateTupleDesc call */]; + List *stack; +} SPGistPrint; + +static void +pushSPGistPrint(FuncCallContext *funcctx, SPGistPrint *prst, ItemPointer ip, int level) { + MemoryContext oldcontext; + SPGistPrintStackElem *e; + + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + e = palloc(sizeof(*e)); + e->iptr = *ip; + e->nlabel = 0; + e->level = level; + prst->stack = lcons(e, prst->stack); + + MemoryContextSwitchTo(oldcontext); +} + +static void +close_spgist_print(SPGistPrint *prst) { + index_close(prst->index, AccessExclusiveLock); +} +#endif + +PG_FUNCTION_INFO_V1(spgist_print); +Datum spgist_print(PG_FUNCTION_ARGS); +Datum +spgist_print(PG_FUNCTION_ARGS) +{ +#if PG_VERSION_NUM < 90200 + elog(NOTICE, "Function is not working under PgSQL < 9.2"); + + PG_RETURN_TEXT_P(CStringGetTextDatum("???")); +#else + FuncCallContext *funcctx; + SPGistPrint *prst; + SPGistPrintStackElem *s; + HeapTuple htuple; + Datum result; + MemoryContext oldcontext; + + if (SRF_IS_FIRSTCALL()) { + text *name=PG_GETARG_TEXT_P(0); + RangeVar *relvar; + Relation index; + ItemPointerData ipd; + TupleDesc tupdesc; + + funcctx = SRF_FIRSTCALL_INIT(); + relvar = makeRangeVarFromNameList(textToQualifiedNameList(name)); + index = relation_openrv(relvar, AccessExclusiveLock); + + if (!IS_INDEX(index) || !IS_SPGIST(index)) + elog(ERROR, "relation \"%s\" is not an SPGiST index", + RelationGetRelationName(index)); + + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + prst = palloc(sizeof(*prst)); + + prst->index = index; + initSpGistState(&prst->state, index); + + tupdesc = CreateTemplateTupleDesc(3 /* types */ + 1 /* level */ + 1 /* nlabel */ + 2 /* tids */, false); + TupleDescInitEntry(tupdesc, 1, "tid", TIDOID, -1, 0); + TupleDescInitEntry(tupdesc, 2, "node", INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, 3, "level", INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, 4, "tid_pointer", TIDOID, -1, 0); + TupleDescInitEntry(tupdesc, 5, "prefix", + (prst->state.attPrefixType.type == VOIDOID) ? INT4OID : prst->state.attPrefixType.type, -1, 0); + TupleDescInitEntry(tupdesc, 6, "label", + (prst->state.attLabelType.type == VOIDOID) ? INT4OID : prst->state.attLabelType.type, -1, 0); + TupleDescInitEntry(tupdesc, 7, "leaf", + (prst->state.attType.type == VOIDOID) ? INT4OID : prst->state.attType.type, -1, 0); + + funcctx->slot = TupleDescGetSlot(tupdesc); + funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); + + funcctx->user_fctx = (void*)prst; + + MemoryContextSwitchTo(oldcontext); + + ItemPointerSet(&ipd, SPGIST_HEAD_BLKNO, FirstOffsetNumber); + prst->stack = NIL; + pushSPGistPrint(funcctx, prst, &ipd, 1); + + PG_FREE_IF_COPY(name,0); + } + + funcctx = SRF_PERCALL_SETUP(); + prst = (SPGistPrint*)(funcctx->user_fctx); + +next: + for(;;) { + if ( prst->stack == NIL ) { + close_spgist_print(prst); + SRF_RETURN_DONE(funcctx); + } + + CHECK_FOR_INTERRUPTS(); + + s = (SPGistPrintStackElem*)linitial(prst->stack); + prst->stack = list_delete_first(prst->stack); + + if (ItemPointerIsValid(&s->iptr)) + break; + free(s); + } + + do { + Buffer buffer; + Page page; + SpGistDeadTuple dtuple; + ItemPointer tid; + + buffer = ReadBuffer(prst->index, ItemPointerGetBlockNumber(&s->iptr)); + LockBuffer(buffer, BUFFER_LOCK_SHARE); + + page = BufferGetPage(buffer); + if (ItemPointerGetOffsetNumber(&s->iptr) > PageGetMaxOffsetNumber(page)) { + UnlockReleaseBuffer(buffer); + pfree(s); + goto next; + } + + dtuple = (SpGistDeadTuple)PageGetItem(page, PageGetItemId(page, ItemPointerGetOffsetNumber(&s->iptr))); + + if (dtuple->tupstate != SPGIST_LIVE) { + UnlockReleaseBuffer(buffer); + pfree(s); + goto next; + } + + if (SpGistPageIsLeaf(page)) { + SpGistLeafTuple leafTuple = (SpGistLeafTuple)dtuple; + + tid = palloc(sizeof(ItemPointerData)); + *tid = s->iptr; + prst->dvalues[0] = PointerGetDatum(tid); + prst->nulls[0] = ' '; + prst->nulls[1] = 'n'; + prst->dvalues[2] = s->level; + prst->nulls[2] = ' '; + prst->nulls[3] = 'n'; + prst->nulls[4] = 'n'; + prst->nulls[5] = 'n'; + prst->dvalues[6] = datumCopy(SGLTDATUM(leafTuple, &prst->state), + prst->state.attType.attbyval, prst->state.attType.attlen); + prst->nulls[6] = ' '; + } else { + SpGistInnerTuple innerTuple = (SpGistInnerTuple)dtuple; + int i; + SpGistNodeTuple node; + + SGITITERATE(innerTuple, i, node) { + if (ItemPointerIsValid(&node->t_tid)) { + if (i >= s->nlabel) + break; + } + } + + if (i >= innerTuple->nNodes) { + UnlockReleaseBuffer(buffer); + pfree(s); + goto next; + } + + tid = palloc(sizeof(ItemPointerData)); + *tid = s->iptr; + prst->dvalues[0] = PointerGetDatum(tid); + prst->nulls[0] = ' '; + prst->dvalues[1] = Int32GetDatum(s->nlabel); + prst->nulls[1] = ' '; + prst->dvalues[2] = s->level; + prst->nulls[2] = ' '; + tid = palloc(sizeof(ItemPointerData)); + *tid = node->t_tid; + prst->dvalues[3] = PointerGetDatum(tid); + prst->nulls[3] = ' '; + if (prst->state.attPrefixType.attbyval != VOIDOID && innerTuple->prefixSize > 0) { + prst->dvalues[4] = datumCopy(SGITDATUM(innerTuple, &prst->state), + prst->state.attPrefixType.attbyval, prst->state.attPrefixType.attlen); + prst->nulls[4] = ' '; + } else + prst->nulls[4] = 'n'; + if (prst->state.attLabelType.attbyval != VOIDOID && !IndexTupleHasNulls(node)) { + prst->dvalues[5] = datumCopy(SGNTDATUM(node, &prst->state), + prst->state.attLabelType.attbyval, prst->state.attLabelType.attlen); + prst->nulls[5] = ' '; + } else + prst->nulls[5] = 'n'; + prst->nulls[6] = 'n'; + + pushSPGistPrint(funcctx, prst, &node->t_tid, s->level + 1); + s->nlabel = i + 1; + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + prst->stack = lcons(s, prst->stack); + MemoryContextSwitchTo(oldcontext); + s = NULL; + } + + UnlockReleaseBuffer(buffer); + if (s) pfree(s); + } while(0); + + htuple = heap_formtuple(funcctx->attinmeta->tupdesc, prst->dvalues, prst->nulls); + result = TupleGetDatum(funcctx->slot, htuple); + + SRF_RETURN_NEXT(funcctx, result); +#endif +} + diff --git a/gevel.sql.in b/gevel.sql.in index dc7784f..1f031ed 100644 --- a/gevel.sql.in +++ b/gevel.sql.in @@ -49,5 +49,11 @@ create or replace function spgist_stat(text) language C with (isstrict); +create or replace function spgist_print(text) + returns setof record + as 'MODULE_PATHNAME' + language C + with (isstrict); + END; diff --git a/sql/gevel.sql b/sql/gevel.sql index 734bba3..976e9aa 100644 --- a/sql/gevel.sql +++ b/sql/gevel.sql @@ -12,6 +12,7 @@ SELECT center(t) AS p INTO gevelp FROM gevelt; CREATE INDEX gist_idx ON gevelt USING gist ( t ); CREATE INDEX spgist_idx ON gevelp USING spgist ( p ); +CREATE INDEX kdspgist_idx ON gevelp USING spgist ( p kd_point_ops); --GiST SELECT gist_stat('gist_idx'); @@ -20,6 +21,8 @@ SELECT * FROM gist_print('gist_idx') as t(level int, valid bool, a box) where le --SPGiST SELECT spgist_stat('spgist_idx'); +SELECT * FROM spgist_print('kdspgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix float8, node_label int, leaf_value point); +SELECT * FROM spgist_print('spgist_idx') as t(tid tid, node_n int, level int, tid_pointer tid, prefix point, node_label int, leaf_value point) WHERE level = 1; --GIN CREATE TABLE test__int( a int[] );