Force Postgres index creation

Question

I have a very long running function, which is an import process, and it is currently in the middle of running. It's been running for 10 hours, and according to my calculations - will take about 16-24 hours to finish.

However, one of the reasons it is taking so long is because one of the tables it is using for a lookup (as well as writing to) is not indexed properly.

I cannot interrupt the function, since that would cause it to roll back the transaction and undo its last 10 hours of work. However, I cannot create the index while it is mid-operation because it is retaining the lock on the table.

Is there any way I can force Postgres to obtain an explicit lock on the table and create the index (pausing the function execution while it is creating it)?

If not, is there anyway I can interrupt the function without having it roll back the work it did so far?

user330315 · Accepted Answer · 2016-02-02 09:26:08Z

1

You can create the index concurrently which takes longer to build the index but allows concurrent access to the table, while the index is created.

However, that will still not help you because the running statement will not suddenly change its execution plan "in-flight" to switch to one that uses the index.

And no, a statement is always atomic. If a statement is cancelled all work done so far is rolled back.

As far as I can tell you have two options:

wait for the process to finish
kill the import and re-run it, hoping that it will run faster than the remaining 10 hours once the index is in place.

answered Feb 2, 2016 at 9:26

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Ruslan Over a year ago

Thanks so much. The second statement is what I'm not too sure about; technically, it is one statement because it is running a select func(), but the func() is structured like a stored proc and runs a bunch of queries in a loop. So, it might decide to use the new index in the loop iterations. I'll try it! Thanks!

Ruslan Over a year ago

Also, the function is using a temp table (CREATE LOCAL TEMP TABLE ... ON COMMIT DROP) for its loop. Is there some hacky way I can just truncate the remaining rows in that table?

Collectives™ on Stack Overflow

Force Postgres index creation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related