Hi John,
> Does that all really have to be a single transaction?
Yes - I need to ensure that of the changesets and denormalised tables are created in the same transaction, so that if
anerror occurs the database is rolled back to the last successfully applied changeset. I don't want to get into the
businessof the splitting it into separate transactions and then having to revert changes that were applied in a
previoustransaction step.
> Do you really need to use triggers for your revision tracking, and can't
> rely on your daily update cycle to manually set the revision information?
They are not necessary, but it has the cleanest code implementation and makes the revision maintenance to the tables
almosttransparent. If they are causing the problem I could change the logic...
> Is it really necessary to generate massive denormalized tables, rather
> than using view's to join the data?
Yes - to create the tables is complex, and often involves complex functions and multiple temp tables. The overall time
tocreate these tables is somewhere in the area of 3hours on this server. I'm also unloading these tables multiple times
forseparate purposes, so they would need to be materialised anyway.
> with only 1-2 connections, you certainly could increase the work_mem.
I can't increase this value at the moment on this server because I was getting out of memory errors with the initial
populationof the database (which builds the denormalized tables, but does not determine the changeset to the previous
tablerevision).
I tried values, 256mb - 2mb and could only get the query to run with 1mb. I suspect even this was pushing the boundary,
sowhen I got to the next stage in my testing - to apply incremental updates - the memory issue raised it head again.
Regards,
Jeremy
______________________________________________________________________________________________________
This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@linz.govt.nz) and
destroythe original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.
Thank you.
______________________________________________________________________________________________________