pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCLUSTERing a partition?
4+ messages / 3 participants
[nested] [flat]
* CLUSTERing a partition?
@ 2015-03-13 23:33 Dave Johansen <davejohansen@gmail.com>
2015-03-14 11:26 ` Re: CLUSTERing a partition? jaime soler <jaime.soler@gmail.com>
2015-03-17 14:03 ` Re: CLUSTERing a partition? Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Dave Johansen @ 2015-03-13 23:33 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLUSTERing a partition?
2015-03-13 23:33 CLUSTERing a partition? Dave Johansen <davejohansen@gmail.com>
@ 2015-03-14 11:26 ` jaime soler <jaime.soler@gmail.com>
2015-03-16 20:53 ` Re: CLUSTERing a partition? Dave Johansen <davejohansen@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: jaime soler @ 2015-03-14 11:26 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLUSTERing a partition?
2015-03-13 23:33 CLUSTERing a partition? Dave Johansen <davejohansen@gmail.com>
2015-03-14 11:26 ` Re: CLUSTERing a partition? jaime soler <jaime.soler@gmail.com>
@ 2015-03-16 20:53 ` Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dave Johansen @ 2015-03-16 20:53 UTC (permalink / raw)
To: jaime.soler@gmail.com; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLUSTERing a partition?
2015-03-13 23:33 CLUSTERing a partition? Dave Johansen <davejohansen@gmail.com>
@ 2015-03-17 14:03 ` Matheus de Oliveira <matioli.matheus@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Matheus de Oliveira @ 2015-03-17 14:03 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-03-17 14:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-13 23:33 CLUSTERing a partition? Dave Johansen <davejohansen@gmail.com>
2015-03-14 11:26 ` jaime soler <jaime.soler@gmail.com>
2015-03-16 20:53 ` Dave Johansen <davejohansen@gmail.com>
2015-03-17 14:03 ` Matheus de Oliveira <matioli.matheus@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox