Thread: help with plug-in function for additional (partition/shard) visibility checks
help with plug-in function for additional (partition/shard) visibility checks
From
Hannu Krosing
Date:
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/
Re: help with plug-in function for additional (partition/shard) visibility checks
From
PostgreSQL - Hans-Jürgen Schönig
Date:
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 1insert 2commit
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.
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/proxycode.
it would then behind the scene arrange the filter passed on.
what do you think?
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
Re: help with plug-in function for additional (partition/shard) visibility checks
From
Hannu Krosing
Date:
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 > >
Re: help with plug-in function for additional (partition/shard) visibility checks
From
PostgreSQL - Hans-Jürgen Schönig
Date:
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
Re: help with plug-in function for additional (partition/shard) visibility checks
From
Hannu Krosing
Date:
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 > >
Re: help with plug-in function for additional (partition/shard) visibility checks
From
PostgreSQL - Hans-Jürgen Schönig
Date:
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
giventhe 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