Re: Accessing non catalog table in backend

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.

Please advice.

Regards,

Atri

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

Thanks!

I was wary to use SPI inside the executor for node evaluation functions.
Does it seem safe?

Regards,

Atri
On 5 Jan 2016 12:20 am, "Jim Nasby" <Jim(dot)Nasby(at)bluetreble(dot)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... :)
> --
> 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 1/4/16 12:53 PM, Atri Sharma wrote:
Please don't top-post.
> On 5 Jan 2016 12:20 am, "Jim Nasby" <Jim(dot)Nasby(at)bluetreble(dot)com
> <mailto:Jim(dot)Nasby(at)bluetreble(dot)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

On Tue, Jan 5, 2016 at 9:54 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> 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
>
>
>
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.

Regards,

Atri

Atri Sharma <atri(dot)jiit(at)gmail(dot)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(at)lab(dot)ntt(dot)co(dot)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

Sorry, I missed this email.

I was specifically targeting accessing tables inside Node evaluation hence
do not want to add new nodes.

Thanks for your inputs!

Regards,

Atri

On Tue, Jan 5, 2016 at 11:43 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> On 2016/01/05 14:30, Atri Sharma wrote:
> > On Tue, Jan 5, 2016 at 9:54 AM, Amit Langote <
> Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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
>
>
>

--
Regards,

Atri
*l'apprenant*

On Tue, Jan 5, 2016 at 11:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Atri Sharma <atri(dot)jiit(at)gmail(dot)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?
>
>
Thanks!

This was a weird requirement and managed to work around it but I will keep
this hack for future reference.

Regards,

Atri

On Mon, Jan 11, 2016 at 10:48 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:

> Sorry, I missed this email.
>
> I was specifically targeting accessing tables inside Node evaluation hence
> do not want to add new nodes.
>
> Thanks for your inputs!
>
> Regards,
>
> Atri
>
> On Tue, Jan 5, 2016 at 11:43 AM, Amit Langote <
> Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
>> On 2016/01/05 14:30, Atri Sharma wrote:
>> > On Tue, Jan 5, 2016 at 9:54 AM, Amit Langote <
>> Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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.
>>
>>

Sorry for top posting.

Regards,

Atri