Regarding B-Tree Lookup

On Tue, May 2, 2017 at 6:12 PM, Mahi Gurram <teckymahi@gmail.com> wrote:

I'm building some custom extension on top of postgres 9.6.1. As part of
that, I would like to read Heap Tuple directly from my extension using
Primary Key.

By default, postgres table index(B-Tree) its PrimaryKey(PK). So, i would
like to get TID by doing a lookup into PK's B-Tree index. Using which i
could read HeapTuple directly.

Please suggest me the easiest way to lookup into PK's B-Tree index for
getting TIDs.

Why don't you just use SPI within your extension? No need to copy the
logic for btree lookups this way.
https://www.postgresql.org/docs/9.6/static/spi.html

Suggesting a postgres extensions which does B-Tree lookup will also helps
me.

contrib/amcheck looks at raw btree data, though I am not sure that you
actually need to go down to that. But that's hard to reach a
conclusion without more details.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2 May 2017 7:34 pm, "Michael Paquier" <michael.paquier@gmail.com> wrote:

On Tue, May 2, 2017 at 6:12 PM, Mahi Gurram <teckymahi@gmail.com> wrote:

I'm building some custom extension on top of postgres 9.6.1. As part of
that, I would like to read Heap Tuple directly from my extension using
Primary Key.

By default, postgres table index(B-Tree) its PrimaryKey(PK). So, i would
like to get TID by doing a lookup into PK's B-Tree index. Using which i
could read HeapTuple directly.

Use the heapam and indexam.

There's a handy wrapper for simpler queries in genam. See
systable_beginscsn etc. AFAIK these aren't really restricted to system
tables.

Please suggest me the easiest way to lookup into PK's B-Tree index for
getting TIDs.

Why don't you just use SPI within your extension? No need to copy the
logic for btree lookups this way.
https://www.postgresql.org/docs/9.6/static/spi.html

SPI is certainly the simplest way.

Suggesting a postgres extensions which does B-Tree lookup will also helps
me.

Pglogical has lots of direct heap and index access via genam.

Michael Paquier <michael.paquier@gmail.com> writes:

On Tue, May 2, 2017 at 6:12 PM, Mahi Gurram <teckymahi@gmail.com> wrote:

Please suggest me the easiest way to lookup into PK's B-Tree index for
getting TIDs.

Why don't you just use SPI within your extension? No need to copy the
logic for btree lookups this way.

There's not actually that much code needed, though -- basically
index_beginscan, index_rescan, index_getnext, index_endscan. One possible
model to follow is systable_beginscan and friends, in genam.c.

I think that you could possibly get away with just applying
systable_beginscan to random user tables, even. But it's at least a
conceptual mismatch, and there are some things in there, like the
test on IgnoreSystemIndexes, that you probably don't want.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you all for your responses to help me out.

The below code worked for me... pasting it here (May be helpful for someone)

Relation heap;

ItemPointer ht_ctid;
heap = heap_open(50620, AccessShareLock); /* 50620 - Table/Relation Oid -
Hardcoded for better understanding */

ScanKeyInit(&skey, 1, BTEqualStrategyNumber, F_INT8EQ, Int64GetDatum(100));

/* 100 is the Value of PK of which i'm doing a lookup*/
scan = systable_beginscan(heap, 50624 , true, NULL, 1, &skey); // 50624 is
the Oid of PKIndex for this Table/Relation.

tuple = systable_getnext(scan);

if (HeapTupleIsValid(tuple)){
ht_ctid = &tuple->t_self;
}
systable_endscan(scan);
heap_close(heap, AccessShareLock);

Hope this helps for some one.

PS: You have to use different Procedure(F_INT8EQ/F_TEXTEQ etc..) and Datum
Conversion functions(Int64GetDatum/CStringGetTextDatum etc..) in
ScanKeyInit() function as per your PK Data Types.

Thanks & Best Regards,
- Mahi

On Tue, May 2, 2017 at 5:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: