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. Since index creation--especially concurrent index creation on tables with lots of writes--takes a long time, it would be great to be able to trigger index creation in the background, without having to dedicate a process+connection that keeps a statement open. 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? 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 <kgri...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
