Skip site navigation (1) Skip section navigation (2)

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

From: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: Hannu Krosing <hannu(at)krosing(dot)net>
Cc: PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: help with plug-in function for additional (partition/shard) visibility checks
Date: 2011-09-02 12:51:35
Message-ID: 692F6DBD-0DA0-432B-B291-55FB9BC5091C@cybertec.at (view raw or flat)
Thread:
Lists: 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 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?
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(at)postgresql(dot)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(at)postgresql(dot)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


In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2011-09-02 12:59:40
Subject: Re: help with plug-in function for additional (partition/shard) visibility checks
Previous:From: Hannu KrosingDate: 2011-09-02 12:36:00
Subject: Re: help with plug-in function for additional (partition/shard) visibility checks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group