On Thu, Nov 29, 2012 at 8:38 AM, Mike Blackwell <mike.blackwell@rrd.com> 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
