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

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

and

2) it still won't work for pl/proxy's main usecase - calling the same
_function_ on partition.

> i got to think about it futher but i can envision that this could be feasible ...
>
>     hans
>
>
> On Sep 2, 2011, at 2:36 PM, Hannu Krosing wrote:
>
> > On Fri, 2011-09-02 at 14:01 +0200, PostgreSQL - Hans-Jürgen Schönig
> > wrote:
> >> hello …
> >>
> >> i have been thinking about this issue for quite a while ...
> >> given your idea i am not sure how this can work at all.
> >>
> >> consider:
> >>     begin;
> >>     insert 1
> >>     insert 2
> >>     commit
> >>
> >> assume this ends up in the same node,
> >> now you split it into two …
> >> 1 and 2 will have exactly the same visibility to and transaction.
> >> i am not sure how you can get this right without looking at the data.
> >
> > It has to consider the data when determining visibility, that's the
> > whole point of the plug-in .
> >
> > The idea is, that each row "belongs" to a certain partition, as
> > determined by some function over it's fields. Most often this function
> > is hash of primary key OR-ed by a bitmap representing cluster size and
> > AND-ed by bitmap for partition(s) stored in this database.
> >
> > when you split the parition, then some row's don't belong in the old
> > partition database anymore (and if you did a full copy, then the other
> > half dont belong to the new one), so they should be handled as
> > invisible / deleted. As this can be only done by looking at the tuple
> > data, this needs an additional visibility function. And as this is only
> > needed for partitioned databases, it makes sense to implement it as a
> > plogin, so it would not wast cycles on non-partitioned databases
> >
> >> alternative idea: what if the proxy would add / generate a filter by
> >> looking at the data?
> >> a quick idea would be that once you split you add a simple directive
> >> such as "FILTER GENERATOR $1" or so to the PL/proxy code.
> >> it would then behind the scene arrange the filter passed on.
> >> what do you think?
> >
> > Hmm. I'm not sure I understand what you are trying to say. Can you
> > elaborate a little ?
> >
> >>
> >>     regards,
> >>
> >>         hans
> >>
> >>
> >>
> >> On Sep 1, 2011, at 10:13 AM, Hannu Krosing wrote:
> >>
> >>> Hallow hackers
> >>>
> >>> I have the following problem to solve and would like to get advice on
> >>> the best way to do it.
> >>>
> >>> The problem:
> >>>
> >>> When growing a pl/proxy based database cluster, one of the main
> >>> operations is splitting a partition. The standard flow is as follows:
> >>>
> >>> 1) make a copy of the partitions table(s) to another database
> >>> 2) reconfigure pl/proxy to use 2 partitions instead of one
> >>>
> >>> The easy part is making a copy of all or half of the table to another
> >>> database. The hard part is fast deletion (i mean milliseconds,
> >>> comparable to TRUNCATE) the data that should not be in a partition (so
> >>> that RUN ON ALL functions will continue to return right results).
> >>>
> >>> It would be relatively easy, if we still had the RULES for select
> >>> available for plain tables, but even then the eventual cleanup would
> >>> usually mean at least 3 passes of disk writes (set xmax, write deleted
> >>> flag, vacuum and remove)
> >>>
> >>> What I would like to have is possibility for additional visibility
> >>> checks, which would run some simple C function over tuple data (usually
> >>> hash(fieldval) + and + or ) and return visibility (is in this partition)
> >>> as a result. It would be best if this is run at so low level that also
> >>> vacuum would use it and can clean up the foreign partition data in one
> >>> pass, without doing the delete dance first.
> >>>
> >>> So finally the QUESTION :
> >>>
> >>> where in code would be the best place to check for this so that
> >>>
> >>> 1) both regular queries and VACUUM see it
> >>> 2) the tuple data (and not only system fields or just xmin/xmax) would
> >>> be available for the function to use
> >>>
> >>>
> >>> --
> >>> -------
> >>> Hannu Krosing
> >>> PostgreSQL Unlimited Scalability and Performance Consultant
> >>> 2ndQuadrant Nordic
> >>> PG Admin Book: http://www.2ndQuadrant.com/books/
> >>>
> >>>
> >>> --
> >>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> >>> To make changes to your subscription:
> >>> http://www.postgresql.org/mailpref/pgsql-hackers
> >>>
> >>
> >> --
> >> Cybertec Schönig & Schönig GmbH
> >> Gröhrmühlgasse 26
> >> A-2700 Wiener Neustadt, Austria
> >> Web: http://www.postgresql-support.de
> >>
> >>
> >
> >
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >
>
> --
> Cybertec Schönig & Schönig GmbH
> Gröhrmühlgasse 26
> A-2700 Wiener Neustadt, Austria
> Web: http://www.postgresql-support.de
>
>