Re: Exposing quals - Mailing list pgsql-hackers

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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