Обсуждение: [HACKERS] Regarding B-Tree Lookup
Hi,
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.
Suggesting a postgres extensions which does B-Tree lookup will also helps me.
Awaiting your response.
Thanks & Best Regards
- Mahi
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
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.
>Why don't you just use SPI within your extension? No need to copy the
> Please suggest me the easiest way to lookup into PK's B-Tree index for
> getting TIDs.
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
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:
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