[GENERAL] dropping an index inside a transaction
Hi All, If one drops an index inside a transaction, is that index still usable by other transactions? My use-case is loading millions of records into a heavily indexed table. I'd like to speed up the load by temporarily dropping the indices. I'm wondering if I can do this inside a transaction so that the rest of the application can still benefit from the indices. Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dropping an index inside a transaction
On Sat, Mar 17, 2012 at 8:37 PM, Andy Chambers achamb...@mcna.net wrote: Hi All, If one drops an index inside a transaction, is that index still usable by other transactions? My use-case is loading millions of records into a heavily indexed table. I'd like to speed up the load by temporarily dropping the indices. I'm wondering if I can do this inside a transaction so that the rest of the application can still benefit from the indices. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dropping an index inside a transaction
On Sat, Mar 17, 2012 at 8:37 PM, Andy Chambers achamb...@mcna.net wrote: Hi All, If one drops an index inside a transaction, is that index still usable by other transactions? My use-case is loading millions of records into a heavily indexed table. I'd like to speed up the load by temporarily dropping the indices. I'm wondering if I can do this inside a transaction so that the rest of the application can still benefit from the indices. If another transaction is already relying on the index you can't drop it. Once you drop it in a transaction all other queries against the tables will be waiting behind the exclusive lock on the transaction to finish to access the table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
