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 an error occurs the database is rolled back to the last successfully applied changeset. I don't want to get into the business of the splitting it into separate transactions and then having to revert changes that were applied in a previous transaction 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 almost transparent. 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 to create these tables is somewhere in the area of 3hours on this server. I'm also unloading these tables multiple times for separate 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 population of the database (which builds the denormalized tables, but does not determine the changeset to the previous table revision). I tried values, 256mb - 2mb and could only get the query to run with 1mb. I suspect even this was pushing the boundary, so when 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 i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. ______________________________________________________________________________________________________ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
