Thread: Update INSERT RULE while running for Partitioning

I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am attempting to use partitioning via Inherited tables. At first I was going to create a rule per sub-table based on a date range, but found out with multiple rules postgres will only return the affected-row count on the last rule which gives Hibernate problems. So now I'm thinking the way to do it is just have one rule at a time and when I want to start appending data to a new partition, just change the rule on the parent table and also update the constraint on the last table to reflect the date ranges contained so that constraint_exclusion will work. this should perform better also. For instance

Starting off with:


Now I want to create another Partition:


Which ends up with:

Parent (Rule on insert instead insert into Child2)
  Child1 (Constraint date <= somedate1)
  Child2 (Constraint date > somedate1 AND date <= somedate2)
  Child3 (Constraint date > somedate2)

Anyone else tried this or expect it to work consistently (without stopping db)? Is it possible that there could be a race condition for the insertion and constraints or will the transaction prevent that from occurring? I've done some testing and it seems to work but I could just get lucky so far and not lose any data :)

Thanks for any help,
Gene

Hi, Gene,

Gene wrote:
> I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am
> attempting to use partitioning via Inherited tables. At first I was
> going to create a rule per sub-table based on a date range, but found
> out with multiple rules postgres will only return the affected-row count
> on the last rule which gives Hibernate problems.

This could be considered a PostgreSQL bug - maybe you should discuss
this on the appropriate list (general, hackers)?


HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

On Fri, Jul 07, 2006 at 03:51:38AM -0400, Gene wrote:
> Starting off with:
>
> Parent (Rule on insert instead insert into Child2)
>  Child1 (Constraint date <= somedate1)
>  Child2 (Constraint date > somedate1)
>
> Now I want to create another Partition:
>
> Create Table Child3
> BEGIN
> Update Parent Rule( instead insert into Child3)
> somedate2 = max(date) from Child2
> Update Child2 Constraint( date > somedate1 AND date <= somedate2 )
> Set Constraint Child3 (date > somedate2)
> END

Be aware that adding a constraint with ALTER TABLE will involve a whole
table scan (at least in 8.1.2 or earlier).  This is true even if if you
have an index such that "EXPLAIN SELECT EXISTS (SELECT date > somedate1
AND date <= somedate2 FROM Child2)" claims it will run fast.  ALTER
TABLE is coded to always do a heap scan for constraint changes.


To avoid this, this I've made a minor modification to my local
PostgreSQL to give a construct similar to Oracle's NOVALIDATE.  I allow
"ALTER TABLE ... ADD CONSTRAINT ... [CHECK EXISTING | IGNORE EXISTING]".
To use this safely without any race conditions I setup my last partition
with an explicit end time and possible extend it if needed.

E.g.
  child1 CHECK(ts >= '-infinity' and ts < t1)
  child2 CHECK(ts >= t1 and ts < t2)
  child3 CHECK(ts >= t2 and ts < t3)

Here doing:
  ALTER TABLE child3 ADD CONSTRAINT new_cstr
      CHECK(ts >= t2 and ts < t4) IGNORE EXISTING;
  ALTER TABLE child3 DROP CONSTRAINT old_cstr;
is safe if t4 >= t3.

I have a regular cron job that makes sure if CURRENT_TIMESTAMP
approaches tn (the highest constraint time) it either makes a new
partition (actually, in my case, recycles an old partition) or extends
the last partition.  My data is such that inserts with a timestamp in
the future make no sense.


> Anyone else tried this or expect it to work consistently (without stopping
> db)?

Note that using ALTER TABLE to add a constraint as well as using DROP
TABLE or TRUNCATE to remove/recycle partitions are DDL commands that
require exclusive locks.  This will block both readers and writers to
the table(s) and can also cause readers and writers to now interfere
with each other.

For example, my work load is a lot of continuous small inserts with
some long running queries (reports).  MVCC allows these to not block
each other at all.  However, if my cron job comes along and naively
attempts to do DROP TABLE, TRUNCATE, or ALTER TABLE it will block on the
long running queries.  This in turn will cause new INSERT transactions
to queue up behind my waiting exclusive lock and now I effectively have
reports blocking inserts.

Always think twice about running DDL commands on a live database;
especially in an automated fashion.

There are methods to alleviate or work around some of the issues of
getting an exclusive lock but I haven't found a true solution yet.
I'd imagine that implementing true partitioning within the PostgreSQL
back-end would solve this.  Presumably because it would know that adding
a new partition, etc can be done without locking out readers at all
and it would use something other than an exclusive lock to do the DDL
changes.

> Is it possible that there could be a race condition for the insertion
> and constraints or will the transaction prevent that from occurring?

The required exclusive locks will prevent race conditions.  (If you were
to use something like my IGNORE EXISTING you'd need to make sure you
manually got an exclusive lock before looking up the maximum value to
set as the new constraint.)

--
Dave Chapeskie