[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Custom FDW - the results of a nested query/join not being passed as qual to the outer query
From:       Kai Daguerre <kai () turbot ! com>
Date:       2021-01-28 11:57:02
Message-ID: CAFWgLQLBSKyMeDRnAmRG30tEWmdk8cN=cG2zJ1Ni0zw4gzvjsg () mail ! gmail ! com
[Download RAW message or body]

Many thanks for the fast response.

Using an SRF is an interesting idea, I'll have a play and see if we can
make that work.

Cheers,
Kai

On Wed, Jan 27, 2021 at 3:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Kai Daguerre <kai@turbot.com> writes:
> > We often have virtual tables where a list operation is not
> viable/possible
> > without providing quals. For example we have implemented a 'whois' table,
> > which will retrieve whois information for specified domains. It is
> clearly
> > not practical to do an unqualified 'list' of *all* domains.
>
> In that case you're going to have to resign yourself to some queries
> failing.  This is unavoidable, consider "select * from whois".  But
> just because the query has a WHERE condition doesn't mean that a useful
> restriction clause can be extracted for any particular table.
>
> I think the best you can do is (1) fail at runtime if there's no qual
> and (2) at plan time, return an extremely high cost estimate for a
> qual-less scan, in hopes of discouraging the planner from choosing
> that.  (Instead of (2), you could perhaps not generate a scan path
> at all, but that's likely to lead to an unintelligible error message.)
>
> Perhaps you should rethink whether you really want a foreign table
> rather than a set-returning function.  With the SRF approach it's
> automatic that the user must supply the restricting argument(s) you need.
>
>                         regards, tom lane
>

[Attachment #3 (text/html)]

<div dir="ltr">Many thanks for the fast response.  <div><br></div><div>Using an SRF \
is an interesting idea, I&#39;ll have a play and see if we can make that \
work.</div><div><br></div><div>Cheers,</div><div>Kai</div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Jan 27, 2021 at 3:27 PM \
Tom Lane &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Kai Daguerre &lt;<a \
href="mailto:kai@turbot.com" target="_blank">kai@turbot.com</a>&gt; writes:<br> &gt; \
We often have virtual tables where a list operation is not viable/possible<br> &gt; \
without providing quals. For example we have implemented a &#39;whois&#39; table,<br> \
&gt; which will retrieve whois information for specified domains. It is clearly<br> \
&gt; not practical to do an unqualified &#39;list&#39; of *all* domains.<br> <br>
In that case you&#39;re going to have to resign yourself to some queries<br>
failing.   This is unavoidable, consider &quot;select * from whois&quot;.   But<br>
just because the query has a WHERE condition doesn&#39;t mean that a useful<br>
restriction clause can be extracted for any particular table.<br>
<br>
I think the best you can do is (1) fail at runtime if there&#39;s no qual<br>
and (2) at plan time, return an extremely high cost estimate for a<br>
qual-less scan, in hopes of discouraging the planner from choosing<br>
that.   (Instead of (2), you could perhaps not generate a scan path<br>
at all, but that&#39;s likely to lead to an unintelligible error message.)<br>
<br>
Perhaps you should rethink whether you really want a foreign table<br>
rather than a set-returning function.   With the SRF approach it&#39;s<br>
automatic that the user must supply the restricting argument(s) you need.<br>
<br>
                                    regards, tom lane<br>
</blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]