Partitioned table and trigger/insert result horribleness

From: Henry <henry(at)zen(dot)co(dot)za>
To: pgsql-general(at)postgresql(dot)org
Subject: Partitioned table and trigger/insert result horribleness
Date: 2009-04-06 07:57:51
Message-ID: 20090406095751.18134039e8orzsis@zenmail.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2009-04-06 08:08:12 Re: Postgres Security Checklist
Previous Message raf 2009-04-06 06:06:36 Re: 8.3.5 problem with plpgsql selecting into an array variable