Re: Pinning buffers for long times like outer joins might do. - Mailing list pgsql-general

From Tzahi Fadida
Subject Re: Pinning buffers for long times like outer joins might do.
Date
Msg-id 00b001c506c2$d4a3bdd0$0b00a8c0@llord
Whole thread Raw
In response to Re: Pinning buffers for long times like outer joins might do.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
> -----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?


>
> > 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so
> that your
>       message can get through to the mailing list cleanly
>
>



pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: changing sort_mem on the fly?
Next
From: John Sidney-Woollett
Date:
Subject: Re: PL/pgSQL functions and RETURN NEXT