Hi gang,
I wrote a PL/Tcl stored proc to do paging updates (we have very large
tables, and updates are these frustrating things that we stare at, not
knowing if they are in a deadlock, or progressing, or when they will
finish).
So, I wrote a tcl proc that runs like this:
SELECT paging_update(
'mdx_core.facility',
'facility_type_code = ''U''',
'facility_id IN (SELECT facility_id FROM temp_up)',
1000
);
Which replaces this:
UPDATE 'mdx_core.facility'
SET facility_type_code = 'U'
WHERE facility_id IN (SELECT facility_id FROM temp_up)
This pages through the table, applying the updates one page (1000 rows) at a
time. Every page, it uses RAISE INFO to feed back the progress. While
purists will argue that I've achieved nothing and that given patience the
UPDATWE will do the same thing, the fact that we can see the progress, know
that we do not have a locking problem and can estimate when the update is
finished is a massive improvement. Better yet, this proc can be used in our
SQL scripts and ad-hoc queries.
Was there something else I could have done? I would love to hear
suggestions. Otherwise, considering that this is a Tcl proc that builds a
dynamic query and excutes as an untrusted function, can I expect any
problems? My main concern:
Will it run within the scope of the current transaction? It uses spi_exec
and there are no START TRANSACTION/COMMIT/etc commands in the code.
Thanks!
Carlo
