From: Robert Haas <robertmhaas@gmail.com>
To: Jaime Casanova <jcasanov@systemguards.com.ec>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Nikhil Sontakke <nikhil.sontakke@enterprisedb.com>
Cc: Emmanuel Cecchet <manu@frogthinker.org>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Date: Thu, 27 Nov 2008 09:47:27 -0500
Message-ID: <603c8f070811270647g1f069ed5ucefbb755dac7aa58@mail.gmail.com> (raw)
In-Reply-To: <3073cc9b0811270601m6a7691dcg5f2ea6d4172b0d38@mail.gmail.com>
References: <3073cc9b0810220744y987173avd560a33fcb58ede5@mail.gmail.com>
<3073cc9b0811052047o4ebe24b4vd0ab24fd3095d342@mail.gmail.com>
<3073cc9b0811261335q5e35d336i33faa17eede7d417@mail.gmail.com>
<603c8f070811261352ta6da0b8xcbb8aaf87f050e6e@mail.gmail.com>
<3073cc9b0811261613l18e5283ft448ea74fed414dd@mail.gmail.com>
<603c8f070811262009v459d0701w151c16533d4ab947@mail.gmail.com>
<a301bfd90811270106k39d60318s7fc871a477a2b58d@mail.gmail.com>
<20081127120402.GC4586@alvh.no-ip.org>
<603c8f070811270507i36e18e0bu5d461a4f957e24d3@mail.gmail.com>
<3073cc9b0811270601m6a7691dcg5f2ea6d4172b0d38@mail.gmail.com>
>> I like the idea of using table inheritance as a foundation for this
>> feature, but I think it's not going to be very useful for real-world
>> applications without cross-table indexes. Suppose for example that I
>> have five years worth of data (thus, 60 partitions) and each
>> transaction has a unique identifier of some sort that is unrelated to
>> the date. It's bad enough that a query like this has to check every
>> partition:
> you haven't. the WHERE clause in your hipotetical CREATE PARTITION
> should create a check constraint on the child (inherited) table and if
> you have constraint_exclusion to on you will check just the
> partition(s) that match with the check constraint.
The problem is that constraint exclusion will not be able to exclude
anything for queries unrelated to the partition key. If my
transactions are identified by UUIDs or similar, there's no way to
predict which table will contain any particular value. You end up
having to scan them all, and even if they all have individual indices
on the column in question, that's still 60 index scans instead of 1.
>> What's even worse (at least IMHO) is that there's no way to use
>> transaction (uuid) as a reference for a foreign key.
> not directly, but you always can create a trigger instead of the
> foreign key constraint...
> mmm...the docs says that there is no good workaround, what about
> mention a trigger?
I think it's pretty hard to make this bulletproof. I think the
triggers that enforce ordinary foreign key constraints contain some
magical cross-checks on transaction commit that can't easily be
emulated by user-written triggers. In any case, it's a long way from
"Oh, yeah, that just works."
...Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: robertmhaas@gmail.com, jcasanov@systemguards.com.ec, alvherre@commandprompt.com, nikhil.sontakke@enterprisedb.com, manu@frogthinker.org, pgsql-hackers@postgresql.org
Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
In-Reply-To: <603c8f070811270647g1f069ed5ucefbb755dac7aa58@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox