Re: Creating indexes in the background

David Lee escribió:
> Thanks for all the responses.
>
> I forgot to ask in my initial post: If not already available, is background indexing a viable feature request? (This was why I sent to pgsql-hackers).
>
> My use case is a multi-tenant CMS where indexes can be created by a web front-end. Since web requests should return quickly, it would be optimal to fire off an index creation and return immediately. Since this is not available natively in postgresql, I have to have a separate worker process that handles long-running connections, and manage communication between the worker process and the web server process.

I think the easiest way to handle this would be to have the web frontend
record the request to create the index somewhere, and a separate process
(maybe one doing LISTEN permanently) does the actual creation.

> Since I've mentioned my use case, I might as well mention another issue I stumbled across, which is that concurrent index creation cannot happen from within trigger functions. I'm able to non-concurrently create indexes from within trigger functions. Why is there this disparity?

Because concurrent index creation needs to open and commit transactions
underneath, which regular index creation does not. Functions cannot run
across transaction boundaries.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services