Re: help with plug-in function for additional (partition/shard) visibility checks

On Sep 2, 2011, at 2:59 PM, Hannu Krosing wrote:

> On Fri, 2011-09-02 at 14:51 +0200, PostgreSQL - Hans-Jürgen Schönig
> wrote:
>> hello …
>>
>> the goal of the entire proxy thing is to make the right query go to the right node / nodes.
>> we determine this by using a partitioning function and so on …
>> currently PL/proxy has only a handful of commands - one is RUN ON … which tells us where to put things.
>> assume you issue a select … some select will "fall out" on the target node.
>> to restrict the data coming from the node you could add an additional constraint on the way …
>>
>> say:
>> SELECT * FROM proxy_table WHERE a = 20;
>>
>> what you want to reach the node after a split is …
>>
>> SELECT * FROM proxy_table WHERE a = 20 AND col = "filter the wrong half away"
>>
>> my idea is to add an additional command to the PL/proxy command set.
>> it should call a function generating this additional filter.
>> maybe somehow like that …
>>
>> RUN ON hashtext($1) -- this one already knows about the increased cluster
>> GENERATE FILTER my_create_the_bloody_filter_func($1) -- this one would "massage" the query going to the node.
>>
>> it would actually open the door for a lot of additional trickery.
>> the function would tell the proxy what to append - and: this "what" would be under your full control.
>>
>> what do you think?
>
> Hmm, could work for simplest cases, but this has 2 main problems:
>
> 1) you need a full SQL parser to make this generally useful for plain
> SQL

i think that everything beyond a simple case is pretty hard to achieve anyway.
to me it looks pretty impossible to solve this in a generic way without same insane amount of labor input - at listen given the ideas coming to me in the past.
and yes, functions are an issue. unless you have some sort of "virtually private database" thing it is close to impossible (unless you want to try some nightmare based on views / constraint exclusion on the partitions or so).

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de