Thread: Partitioned table and trigger/insert result horribleness

Greetings,

I recently successfully partitioned a 100+GB table into many smaller
ones for improved select performance, etc.  This works extremely well,
I must say.  SELECTs, UPDATEs and DELETEs are handled automatically by
Pg, my trigger takes care of distributing INSERTs, etc.

Only problem is (and to my horror), an INSERT always returns 0
(whereas UPDATE/DELETE return 1) - which is *technically* the correct
answer, since that's the return value from the trigger.  However, this
is counter-intuitive since an insert into a parent table which is
partitioned should return a result similar to what an update/delete
would.  argh!

This presents a terrible problem with existing code which diligently
checks the return values from SQL statements.

Is this weirdness scheduled to be addressed in 8.4, or is there some
other hack I can try (without changing all SQL), or is the *only*
solution to mess with existing (working) front-end code to work around
this issue?

Right now, the untenable situation is to simply ignore the return
codes and act like all is well in la-la land.

Comments, admonishments, hope for the future, all welcome.

Cheers!
Henry

On Mon, 2009-04-06 at 09:57 +0200, Henry wrote:

> Is this weirdness scheduled to be addressed in 8.4, or is there some
> other hack I can try (without changing all SQL), or is the *only*
> solution to mess with existing (working) front-end code to work around
> this issue?

No, not in 8.4

Use Rules is the current answer, though that has other issues also.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

I have to say, that this 'feature' also annoys me.

at least ability to update row count would be appreciated. Not to
mention proper partitioning, but that requires quite few changes in
the guts.
Currently there was a guy offering patch that adds partitioning, but
it uses built in trigger, which is mid-way solution.  (still better
than having to write everything manually).

Quoting "Simon Riggs" <simon@2ndQuadrant.com>:
> Use Rules is the current answer, though that has other issues also.

Hi Simon - as you say, Rules have issues.  From my understanding,
partitioning with rules is impractical.

Anyway, thanks for clarifying.

Cheers
Henry