Thread: drop index without accessexclusivelock in transaction (JAVA)

Hello,

Through java jdbc, is it possible that we do the following steps without
accessexclusivelock for index:

setautocommit(false);

drop index1, 2,....;
insert millions records
set index1,2...
commit;

Found this post, but it says only within psql block begin/commit, users
are able to do it.
http://stackoverflow.com/questions/19324637/postgres-transaction-seems-to-take-accessexclusivelock-for-no-reason

On 11/18/15 10:48 AM, Emi wrote:
> Hello,
>
> Through java jdbc, is it possible that we do the following steps without
> accessexclusivelock for index:
>
> setautocommit(false);
>
> drop index1, 2,....;
> insert millions records
> set index1,2...
> commit;
>
> Found this post, but it says only within psql block begin/commit, users
> are able to do it.
> http://stackoverflow.com/questions/19324637/postgres-transaction-seems-to-take-accessexclusivelock-for-no-reason

You can't do it because the DROP needs an exclusive lock to ensure that
no one else is trying to use the index when it's dropped. There is DROP
INDEX CONCURRENTLY but IIRC you can't do that in a transaction.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com