Partitioned table and trigger/insert result horribleness

Started by Henryabout 17 years ago4 messagesgeneral
Jump to latest
#1Henry
henry@zen.co.za

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

#2Simon Riggs
simon@2ndQuadrant.com
In reply to: Henry (#1)
Re: Partitioned table and trigger/insert result horribleness

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

#3Grzegorz Jaśkiewicz
gryzman@gmail.com
In reply to: Simon Riggs (#2)
Re: Partitioned table and trigger/insert result horribleness

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).

#4Henry
henry@zen.co.za
In reply to: Simon Riggs (#2)
Re: Partitioned table and trigger/insert result horribleness

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