Postgres Pro
Downloads
Home   >   mailing lists

Thread: CLUSTERing a partition?

CLUSTERing a partition?

From
Dave Johansen
Date:
I'm using Postgres 8.4 and I would like to cluster a partition but keep running into issues with locks. CLUSTER requires an Exclusive Lock and so it gets "stuck behind" running queries that have a Share Lock on the partition even though they are not using the partition. I would be fine if the cluster waited until after the existing queries finished, but this then causes all new queries to back up behind it. I tried doing "NO INHERIT" to remove the partition while the cluster is happening, but that also requires an Exclusive Lock and runs into the same problem.

So is there some way that I can CLUSTER a partition? I would be fine with removing the partition while the CLUSTER is happening, if that makes it possible.

Thanks,
Dave

Re: CLUSTERing a partition?

From
jaime soler
Date:
El vie, 13-03-2015 a las 16:33 -0700, Dave Johansen escribió:
> I'm using Postgres 8.4 and I would like to cluster a partition but
> keep running into issues with locks. CLUSTER requires an Exclusive
> Lock and so it gets "stuck behind" running queries that have a Share
> Lock on the partition even though they are not using the partition. I
> would be fine if the cluster waited until after the existing queries
> finished, but this then causes all new queries to back up behind it. I
> tried doing "NO INHERIT" to remove the partition while the cluster is
> happening, but that also requires an Exclusive Lock and runs into the
> same problem.
>
uff postgresql 8.4, are you from the past?
>
> So is there some way that I can CLUSTER a partition? I would be fine
> with removing the partition while the CLUSTER is happening, if that
> makes it possible.

There is a extension pg_repack http://pgxn.org/dist/pg_repack/1.3.1/ ,
that allows to retore order of a table without using a exclusive lock,
but i don't know how it behavior with partitioned tables.

good luck
>
>
> Thanks,
>
> Dave

Re: CLUSTERing a partition?

From
Dave Johansen
Date:
On Sat, Mar 14, 2015 at 4:26 AM, jaime soler <jaime.soler@gmail.com> wrote:
El vie, 13-03-2015 a las 16:33 -0700, Dave Johansen escribió:
> I'm using Postgres 8.4 and I would like to cluster a partition but
> keep running into issues with locks. CLUSTER requires an Exclusive
> Lock and so it gets "stuck behind" running queries that have a Share
> Lock on the partition even though they are not using the partition. I
> would be fine if the cluster waited until after the existing queries
> finished, but this then causes all new queries to back up behind it. I
> tried doing "NO INHERIT" to remove the partition while the cluster is
> happening, but that also requires an Exclusive Lock and runs into the
> same problem.
>
uff postgresql 8.4, are you from the past?
>
> So is there some way that I can CLUSTER a partition? I would be fine
> with removing the partition while the CLUSTER is happening, if that
> makes it possible.

There is a extension pg_repack http://pgxn.org/dist/pg_repack/1.3.1/ ,
that allows to retore order of a table without using a exclusive lock,
but i don't know how it behavior with partitioned tables.

Thanks for the info, but https://github.com/reorg/pg_repack/issues/23 scares me way too much to consider using it.

Re: CLUSTERing a partition?

From
Matheus de Oliveira
Date:

On Fri, Mar 13, 2015 at 8:33 PM, Dave Johansen <davejohansen@gmail.com> wrote:
So is there some way that I can CLUSTER a partition? I would be fine with removing the partition while the CLUSTER is happening, if that makes it possible.

You can remove the partition first (with NO INHERIT), this will indeed cause a lock on the parent, but that should be really fast.

Then you can CLUSTER it, and latter re-add it as child.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres