Re: Slightly OT.

On Sat, 2007-06-02 at 01:44 +0200, Alexander Staubo wrote:
> On 6/2/07, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > Here is some work going on that looks like what you want:
> >
> > http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php
>
> I had no idea someone was working on WAL-log-based replication; I saw
> the TODO entry a while ago, but I missed the thread. I think WAL
> replication is a beautiful idea, so I'll gladly throw my support
> behind this. Thanks for the pointer.
>

I think that project would add some great functionality to postgres.

> > You might also seriously consider PgPool-II.
>
> pgpool-II seems like a decent idea. I'm not sure if the partitioning
> can support referential integrity though -- would they have to be
> declared as CHECK constraints that used dblink()?

You shouldn't use a volatile function in a check constraint. Use a
trigger instead, but even that is unlikely to work for enforcing
constraints correctly.

In general, for partitioning, you have to make some sacrifices. It's
very challenging (and/or expensive) to ensure uniqueness across
partitions.

> Also, it doesn't seem capable of planning a query intelligently, which
> means that a query such as "select * from foo where id = 123" is going
> to be aggregated across all nodes even though only one node has the
> partition covering the id range [0, 1000], say.

Take a closer look. There is quite a lot of intelligence in there. The
query you mention will be rewritten to send simple WHERE clauses to the
underlying partitions. That, combined with constraint exclusion (CE),
will ensure that the only partitions scanned are those not excluded by
the partition's predicate (i.e. it will only scan one partition if it's
partitioned based on "id"). Actually, I'm not sure whether it relies on
CE or not, but the point is that it won't scan all the partitions.

Also, this means it could use an index scan on that underlying
partition, which is crucial.

Regards,
Jeff Davis