Re: Creating indexes in the background

Hey folks,

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?

--David

On Oct 27, 2012, at 19:20, David Lee <davidomundo(at)gmail(dot)com> wrote:

> Hey folks,
>
> 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?
>
> --David
>

No there is not.

David J.

Thanks. Is this something viable as a feature request?
On Oct 28, 2012 7:48 AM, "David Johnston" <polobo(at)yahoo(dot)com> wrote:

> On Oct 27, 2012, at 19:20, David Lee <davidomundo(at)gmail(dot)com> wrote:
>
> > Hey folks,
> >
> > 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?
> >
> > --David
> >
>
> No there is not.
>
> David J.

On Sat, Oct 27, 2012 at 4:20 PM, David Lee <davidomundo(at)gmail(dot)com> wrote:
> Hey folks,
>
> 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?

Launch the session itself in the background.

For example, on linux:

psql -c 'create index concurrently ....' &

At some point, you will probably want to verify that the creation succeeded.

Cheers,

Jeff

On Sat, Oct 27, 2012 at 6:20 PM, David Lee <davidomundo(at)gmail(dot)com> wrote:
> Hey folks,
>
> 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?
>

do it in the command line:

psql -c "CREATE INDEX CONCURRENTLY aname ON atable(acolumn)" adb &

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

On Sun, Oct 28, 2012 at 8:20 AM, David Lee <davidomundo(at)gmail(dot)com> wrote:

> Hey folks,
>
> 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?
>
If I understood your question, the answer is no, you need to maintain the
server session alive when doing a transaction on a PG server, cutting the
server connection automatically aborts the transaction it is running on
server side.
You can still use a batch processing for doing such operations like:
psql -c "create index concurrently foo on tab(a)" -p $port -h $host $dbname
&
But this needs to be done on the client application side that will maintain
alive a session on server.
--
Michael Paquier
http://michael.otacoo.com

On Sat, Oct 27, 2012 at 7:20 PM, David Lee <davidomundo(at)gmail(dot)com> 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?

Nope.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 28 October 2012 01:20, David Lee <davidomundo(at)gmail(dot)com> 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?

There is some work in 9.3 that will give low-level infrastructure
support to allow the concept of a "job/task scheduler" in Postgres,
which is the generic description of the facility you're looking for.
It is early days, but that will emerge sometime in 9.3 or beyond.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10/28/12 10:22 AM, David Lee wrote:
> Thanks. Is this something viable as a feature request?

Possibly, but it's not terribly high on the list.

In the meantime, we've built a user-space index daemon at $WORK that generally solves this issue. We intend to release it at some point, but if you need it now we could provide you with code.
--
Jim "Decibel!" Nasby jnasby(at)enova(dot)com
Primary: 512-569-9461 Backup: 512-579-9024

On Sun, Oct 28, 2012 at 8:22 AM, David Lee <davidomundo(at)gmail(dot)com> wrote:
> Thanks. Is this something viable as a feature request?

Just to contribute a tiny amount of data: I also get this request from
users on a semi-regular basis. It's definitely below the pains of
pg_dump/restore or fork-and-reuse-of-connections of libpq in the space
of caveats that come to mind, but it's worth noting that regular
people do notice this is a gap (disconnected session support)
reasonably frequently. However, the workaround is fairly clear and
network connections are pretty reliable, and that blunts its severity.

it's also useful for other use cases besides CREATE INDEX, like
incremental data rewrites. I think there's some indirect overlap here
with the daemon facility under discussion that is active in another
thread.

--
fdr