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)2ndQuadrant(dot)com>
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:01:51
Message-ID: 689CAF92-6D70-4761-8F64-E47DE3527EF2@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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?

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2011-09-02 12:19:35 Cascaded standby message
Previous Message Magnus Hagander 2011-09-02 10:45:34 Re: PATCH: regular logging of checkpoint progress