Thread: drop index without accessexclusivelock in transaction (JAVA)

drop index without accessexclusivelock in transaction (JAVA)

From
Emi
Date:
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





Re: drop index without accessexclusivelock in transaction (JAVA)

From
Jim Nasby
Date:
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