Re: BTree tid operators and opclass

Here's a small patch to add the full suite of btree operators for tids and the
corresponding btree opclass. This came up a while back on -hackers and a few
people were interested in it at the time. I just had a need for it again so I
added it.

I'm not sure how to allocate OIDs. I just looked for the greatest one in the
various .h files and started from there. It leads to some strange
discontinuities since there were existing = and <> operators.

Greg Stark wrote:
>
> Here's a small patch to add the full suite of btree operators for tids and the
> corresponding btree opclass. This came up a while back on -hackers and a few
> people were interested in it at the time. I just had a need for it again so I
> added it.
>
> I'm not sure how to allocate OIDs. I just looked for the greatest one in the
> various .h files and started from there. It leads to some strange
> discontinuities since there were existing = and <> operators.

Run /include/catalog/unused_oids to see the available OIDs and allocate
a large enough block.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Here's a small patch to add the full suite of btree operators for tids and the
> corresponding btree opclass.

This has been proposed and rejected before, mainly on the basis that
there's no conceivable application for an index on TID. What's your
use case?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Here's a small patch to add the full suite of btree operators for tids and the
> > corresponding btree opclass.
>
> This has been proposed and rejected before, mainly on the basis that
> there's no conceivable application for an index on TID. What's your
> use case?

IIRC the use case that was previously suggested was the common Oracle idiom
for removing duplicates. The Postgrse equivalent would something like:

delete from tab where exists (
select 1 from tab as x where x.pk = tab.pk and x.ctid > tab.ctid
)

In my case I don't want to create an index on tid, I just have a list of tids
that I want to sort so I can process them in heap order.

I was going to just write a qsort comparator but this way I can use tuplesort
and not worry about what happens if it doesn't fit in memory.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Here's a small patch to add the full suite of btree operators for tids
> and the corresponding btree opclass.

Having just felt a need for MAX(tid) and MIN(tid) to look into a
performance issue, I went ahead and committed this. I'm aware that
your later patch for online indexes subsumes this, but that didn't
seem ready to apply ...

regards, tom lane