Pinning buffers for long times like outer joins might do.

Tzahi Fadida <tzahi_ml@myrealbox.com> writes:

I am writing an algorithm in a dynamic c library and using heap_fetch.
I want to pin strategic buffers for long times like an Outer
joins might do for the inner table.

Do i to also need to lock the table somehow?

You didn't say enough about the context. It is *always* necessary to
have some type of lock on a table you are accessing --- otherwise some
other backend could drop the table out from under you. But in many
scenarios this is taken care of at a pretty high level, like executor
startup. What is your "algorithm" and when is it applied? How does it
get the table to be accessed?

I am only reading the tuple but maybe other transactions will
want to write to it and when I am looping over
the table like in an outer join does I can get different
values and I want to avoid that.
or that can't happen when pinning?

Pinning has nothing to do with that --- maintaining a consistent
snapshot setting does.

Another question, when a column attribute is toasted,
do I need to do another heap_fetch aside from the main
table to fetch the data from the adjoined toasted table?
How do I also pin the toasted table buffer pages?

No, you do a DETOAST_DATUM call.

And last general question,
what is the best way to compare two datums?
datumIsEqual doc says its not enough because of
different representations, and if its toasted it won't
work (maybe if its detoasted first).

You really should locate the datatype's equality function and call that.
datumIsEqual is a kluge that is only safe to use in the very limited
context of equalfuncs.c (basically, it's OK to say that two Const nodes
are different if they are bitwise different, even if the represented
values are logically equal).

Look at array_eq() for an example of current best practice for this.

regards, tom lane

-----Original Message-----
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane

Tzahi Fadida <tzahi_ml@myrealbox.com> writes:

I am writing an algorithm in a dynamic c library and using

heap_fetch.

I want to pin strategic buffers for long times like an Outer joins
might do for the inner table.

Do i to also need to lock the table somehow?

You didn't say enough about the context. It is *always*
necessary to have some type of lock on a table you are
accessing --- otherwise some other backend could drop the
table out from under you. But in many scenarios this is
taken care of at a pretty high level, like executor startup.
What is your "algorithm" and when is it applied? How does it
get the table to be accessed?

Do I maintain a lock on a table with heap_open?
I was also meaning to ask if I need to also lock the buffers
I am pinning?

I am writing a full-disjunctions algorithm that is really like
a natural outer join for 2 relations and usually has no
equality in more than 2 relations.

let noj = natural outer join.
E.g. FD(A,B) will give the same as A noj B.
but FD(A,B,C) might not be as A noj B noj C and the FD
alg is here to correct problems of duplicate answers in
A noj B noj C.

So, as with a full natural outer join I can't have the tables be
dropped under me in the middle, but all the operation is read only.
Example of usage: SELECT * FROM FD(A,B,C)
I am really building the prototype now, no one has ever implemented
FD so I don't want to put it just yet in the executor which is why
I am writing a dynamic c library function.

I am only reading the tuple but maybe other transactions

will want to

write to it and when I am looping over the table like in an

outer join

does I can get different values and I want to avoid that.
or that can't happen when pinning?

Pinning has nothing to do with that --- maintaining a
consistent snapshot setting does.

How do you maintain a consistent snapshot settings
with heap_fetch?
with heap_open and heap_beginscan I just save it to a context.
do I use them here before heap_fetch the same
way I would do for heap_getnext?