On Thu, Nov 29, 2012 at 8:38 AM, Mike Blackwell <mike.blackwell@xxxxxxx> wrote: > > What I'm trying at this point is: > > BEGIN; > DROP INDEX -- only one unique index exists > DELETE FROM table; > COPY table FROM STDIN; > COMMIT; > CREATE INDEX CONCURRENTLY; > > Do I understand correctly that DROP/CREATE index are not transactional, and > thus the index will disappear immediately for other transactions? The DROP is transactional. But the way it works here is that the index is access exclusively locked when the DROP is encountered (and so is the table) so any other transaction will block on it, even though the index is still there. (Transactionality does not inherently demand this behavior, it is just the way PG implements it. For example, it could take a weaker lock at the time DROP is encountered and then escalate it to exclusive only during the commit processing. But that would greatly expand the risk of deadlock, and would certainly be more complicated to code.) > Am I > better off in that case moving the DROP INDEX outside the transaction? > > The access pattern for the table is such that I can afford the occasional > stray hit without an index during the reload time. If you don't mind queries doing doing full table scans, and not having the benefit of the unique constraint, for that period, then yes you should move the drop index into a separate transaction. But If you do keep the drop index inside the transaction, then you would probably be better off using truncate rather than delete, and rebuild the index non-concurrently and move that inside the transaction as well. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
