Обсуждение: 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