Re: Creating indexes in the background

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
waswhy 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
inpostgresql, I have to have a separate worker process that handles long-running connections, and manage communication
betweenthe worker process and the web server process. 

Since index creation--especially concurrent index creation on tables with lots of writes--takes a long time, it would
begreat to be able to trigger index creation in the background, without having to dedicate a process+connection that
keepsa statement open. 

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

If you can't tell, my ideal goal is to be able to launch background concurrent index creation via triggers.

--David

On Oct 28, 2012, at 2:35 PM, Kevin Grittner <kgrittn@mail.com> wrote:

> David Lee wrote:
>
>> It seems like right now when you want to create an index
>> concurrently, the index creation will get canceled if you cancel
>> the statement (i.e. you must keep your statement open).
>>
>> Is there a way to "launch" an index creation in the background so
>> that the statement doesn't need to be kept open?
>
> You are asking on the wrong list; this list is for discussions
> related to developing the PostgreSQL product itself. To ask questions
> about how to *use* PostgreSQL, please use one of the other lists.
> This sort of question belongs on the pgsql-general or pgsql-admin
> list.
>
> As long as I'm sending an email -- I usually run such queries on the
> server, either through a crontab job (if it's a recurring need), an
> "at" job (if it is a one-time off-hours need), or a background job.
> The specifics will depend on your operating system, which is
> something you should generally include in a question.
>
> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> -Kevin