Обсуждение: Accessing non catalog table in backend

On 1/4/16 12:07 PM, Atri Sharma wrote:
> Hi All,
>
> I wanted to check if it is possible to query a non catalog table in backend.
>
> I understand that cql interface is only for catalog querying hence it is
> not usable for this purpose per se.

AFAIK it's possible to do it with low level routines, but I think unless 
you have a really good reason to go that route you're much better off 
just using SPI. If it's good enough for plpgsql... :)
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

<p dir="ltr">Thanks! <p dir="ltr">I was wary to use SPI inside the executor for node evaluation functions. Does it seem
safe?<pdir="ltr">Regards, <p dir="ltr">Atri<div class="gmail_quote">On 5 Jan 2016 12:20 am, "Jim Nasby" <<a
href="mailto:Jim.Nasby@bluetreble.com">Jim.Nasby@bluetreble.com</a>>wrote:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 1/4/16 12:07 PM, Atri
Sharmawrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex">Hi All,<br /><br /> I wanted to check if it is possible to query a non catalog table in
backend.<br/><br /> I understand that cql interface is only for catalog querying hence it is<br /> not usable for this
purposeper se.<br /></blockquote><br /> AFAIK it's possible to do it with low level routines, but I think unless you
havea really good reason to go that route you're much better off just using SPI. If it's good enough for plpgsql...
:)<br/> -- <br /> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX<br /> Experts in Analytics, Data
Architectureand PostgreSQL<br /> Data in Trouble? Get it in Treble! <a href="http://BlueTreble.com" rel="noreferrer"
target="_blank">http://BlueTreble.com</a><br/></blockquote></div> 

On 1/4/16 12:53 PM, Atri Sharma wrote:
Please don't top-post.
> On 5 Jan 2016 12:20 am, "Jim Nasby" <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>> wrote:
>
>     On 1/4/16 12:07 PM, Atri Sharma wrote:
>
>         Hi All,
>
>         I wanted to check if it is possible to query a non catalog table
>         in backend.
>
>         I understand that cql interface is only for catalog querying
>         hence it is
>         not usable for this purpose per se.
>
>
>     AFAIK it's possible to do it with low level routines, but I think
>     unless you have a really good reason to go that route you're much
>     better off just using SPI. If it's good enough for plpgsql... :)
> I was wary to use SPI inside the executor for node evaluation functions.> Does it seem safe?

Oh, inside the executor? Yeah, I doubt attempting SPI there would end 
well...

What exactly are you trying to do?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 2016/01/05 3:53, Atri Sharma wrote:
> I was wary to use SPI inside the executor for node evaluation functions.
> Does it seem safe?

What is "node evaluation functions"? Is it "Plan" nodes or "Expr" nodes
that you are talking about? I guess you'd know to use ExecProcNode() or
ExecEvalExpr() for them, respectively.

Thanks,
Amit

Atri Sharma <atri.jiit@gmail.com> writes:
> I fail to see the relevance of which node is getting evaluated (its a Plan
> node BTW) for this question. The concern I had was around using SPI inside
> executor and its fail safety.

The code path executor -> PL function -> SPI certainly works, so
presumably omitting the intermediate PL function could still work.
Whether it's a good idea is another question entirely.  I do not
offhand see a good reason why knowledge of non-system tables should
exist in the core executor; so what is the need to use SPI?
        regards, tom lane

On 2016/01/05 14:30, Atri Sharma wrote:
> On Tue, Jan 5, 2016 at 9:54 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
>> On 2016/01/05 3:53, Atri Sharma wrote:
>>> I was wary to use SPI inside the executor for node evaluation functions.
>>> Does it seem safe?
>>
>> What is "node evaluation functions"? Is it "Plan" nodes or "Expr" nodes
>> that you are talking about? I guess you'd know to use ExecProcNode() or
>> ExecEvalExpr() for them, respectively.
>>
> I fail to see the relevance of which node is getting evaluated (its a Plan
> node BTW) for this question. The concern I had was around using SPI inside
> executor and its fail safety.

Sorry, I may have misunderstood your question(s). Seeing your first
question in the thread, I see that you're looking to query non-system
tables within the executor. AFAIU, most of the processing within executor
takes the form of some node in some execution pipeline of a plan tree.
Perhaps, you're imagining some kind of node, subnode or some such. By the
way, some places like ATRewriteTable(), validateCheckConstraint() scan
user tables directly using low-level utilities within a dummy executor
context. I think Jim suggested something like that upthread.

Thanks,
Amit