Re: help with plug-in function for additional (partition/shard) visibility checks
От | PostgreSQL - Hans-Jürgen Schönig |
---|---|
Тема | Re: help with plug-in function for additional (partition/shard) visibility checks |
Дата | |
Msg-id | 692F6DBD-0DA0-432B-B291-55FB9BC5091C@cybertec.at обсуждение исходный текст |
Ответ на | Re: help with plug-in function for additional (partition/shard) visibility checks (Hannu Krosing <hannu@krosing.net>) |
Ответы |
Re: help with plug-in function for additional
(partition/shard) visibility checks
|
Список | pgsql-hackers |
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 clusterGENERATEFILTER 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? 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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Hannu KrosingДата:
Сообщение: Re: help with plug-in function for additional (partition/shard) visibility checks
Следующее
От: Hannu KrosingДата:
Сообщение: Re: help with plug-in function for additional (partition/shard) visibility checks