Re: Exposing quals

On Tue, Jul 08, 2008 at 02:41:45PM -0400, Jan Wieck wrote:
> Here,
>
> I talked to my supervisor here in Toronto (that's where I am this week)
> and Afilias actually sees enough value in this for me to go and spend
> time officially on it.

Yay!

> The ideas I have so far are as follows:
>
> Down in the exec nodes like SeqScan or IndexScan, there are several
> parts available that are important.
>
> - Scanned relation
> - Targetlist
> - Filter (for SeqScan)
> - IndexQual (for IndexScan)
>
> These pieces are available at least in the scans Init function and
> actually can be converted back into some SQL statement that effectively
> represents this one single table scan. However, parsing it back at that
> point is nonsense, as we cannot expect everything out there to actually
> be an SQL database.
>
> Also, both the qualification as well as the targetlist can contain
> things like user defined function calls. We neither want to deny nor
> require that this sort of construct is actually handed over to the
> external data source, so the interface needs to be more flexible.
> Therefore it is best to divide the functionality into several user exit
> functions.

Right :)

> The several functions that implement a scan type inside of the
> executor very much resemble opening a cursor for a single table
> query, fetching rows from it, eventually (in the case of a nested
> loop for example) close and reopen the cursor with different key
> values from the outer tuple, close the cursor. So it makes total
> sense to actually require an implementation of an external data
> source to provide functions to open a cursor, fetch rows, close the
> cursor.

That's not unreasonable, given that the simplest kind of external data
source would likely be along the lines of fopen().

> There will be some connection and transaction handling around all
> this that I have in mind but think it would distract from the
> problem to be solved right here, so more about that another time.

Maybe something like a way of setting, for each relation, what kind
(if any) of transactions are available?

> The C implementation for open cursor would be called with a scan
> handle, containing the connection, the relname, the targetlist and
> the qualification subtrees. These are modified from the real ones
> in the scan node so that all Var's have varno=1 and that all OUTER
> Var's have been replaced with a Const that reflects the current
> outer tuples values. From here there are several support functions
> available to "dumb down" each of those to whatever the external
> data source may support. In case of the targetlist, this could mean
> to filter out a unique list of Var nodes only, removing all
> expressions from it. In case of the qualification, this could mean
> remove everything that isn't a standard operator (=, <>, ...), or
> remove everything that isn't Postgres builtin. Finally, there is a
> support function that will build a SQL statement according to
> what's left inside that scan handle.

Interesting.

> The scan handle would track which modifications have been done to the
> various pieces so that the outer support framework knows if it gets back
> the originally requested targetlist, or if it has to run the projection
> on the returned unique list of Var's. And if it has to recheck the
> returned tuples for qualification, because some of the qual's had been
> removed.
>
> In order to allow the user exits to be written in PL's, I can think of
> makiing a complex data type containing the scan handle. The subtrees
> could be accessed by the PL via support functions that return them in
> nodeToString() or other formats.
>
> I'll try to write up a more complete proposal until end of next week.

Yay!

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate