Re: Disallow unique index on system columns

On Wed, Apr 20, 2016 at 9:24 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> > SELECT FROM table WHERE my_func('table', ctid) ==> 'index condition'
>

> Um, why's the ctid important here, or perhaps more directly, what is
> it you're really trying to do?
>

This function is defined as my_func(regclass, tid) and simply returns the
tid value passed in. The operator is defined as ==>(tid, text).

Behind the scenes, the AM is actually backed by Elasticsearch, and the
tuple's ctid value is used as the "_id" in ES.

When Postgres decides to plan a sequential scan (or filter) to answer WHERE
clause conditions that use the ==>(tid, text) operator the AM isn't
involved but I still need to use the remote Elasticsearch server to answer
that condition.

So I came up with this "creative" approach to provide enough context in the
query plan for me to figure out a) which table is being used and b) which
physical row is being evaluated in the seqscan or filter.

When the operator's procedure is called, I notice that it's the first time
I've seen the FuncExpr on the LHS, go query Elasticsearch with the text
query from the RHS, build a hashtable of the matching ctids and lookup the
LHS's value in the hashtable. If it exists, the row matches.

There just didn't seem to be enough context in the FunctionCallInfo of the
the operator's procedure to figure this out without something in the query
that's basically statically determined at parse time.

I suspect what I should be doing for this particular problem is taking
advantage of the Custom Scan API, but I'm trying to support PG 9.3.

We weren't planning to do that.
>

Great!

eric