Andrew,
Thanks for your input, comments below.
On Thu, 2007-07-26 at 13:30, Andrew Kroeger wrote:
> After reading through this thread, I have an idea that should accomplish
> what I believe are your 3 main goals (avoid any negative performance
> impact on the user's inserts, do not lose any data from those inserts,
> and do not double process any data) and possibly improve performance
> (I'm not sure what the overhead is for triggers, so there may not be any
> benefit).
The essential changes you're proposing are:
1) use a fixed table instead of temp table -> this is a reasonable one,
temp tables are not exactly free if they are created and dropped too
often;
2) use an "insert selected/delete" scheme instead of the "delete/insert
via trigger". I doubt this would be faster... in fact I wonder if
instead of the trigger it is not possible to use a rule to do it - I've
never used the postgres rule system, but it seems to me it is possible
to create a rule which inserts the row in another table when deleting
it. I'm not sure how cheap that would be compared to the trigger
version.
In any case, on top of all these thoughts there is one more goal I have:
the solution used for postgres should be as close to the other DBs as
possible, for obvious maintenance reasons.
Cheers,
Csaba.