CLUSTER FREEZE

On Thu, Oct 24, 2013 at 4:58 AM, Thomas Munro <munro@ip9.org> wrote:

Hi
I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to add
that, for consistency with VACUUM. Is it useful?

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

Anyway code side, I think you need to set both feeze_min_age as well
as freeze_table_age, see VACUUM command in gram.y

CLUSTER opt_freeze opt_verbose qualified_name cluster_index_specification

  {
  ClusterStmt *n = makeNode(ClusterStmt);
- n->relation = $3;
- n->indexname = $4;
- n->verbose = $2;
+ n->relation = $4;
+ n->freeze_min_age = $2 ? 0 : -1;
+ n->indexname = $5;
+ n->verbose = $3;
..

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24 October 2013 05:58, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Oct 24, 2013 at 4:58 AM, Thomas Munro <munro@ip9.org> wrote:

Hi
I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to add
that, for consistency with VACUUM. Is it useful?

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

Well I can't speak for Thomas, but if you're doing such a heavy-duty
operation on a table that involves an exclusive lock, you may as well
freeze it. And in the case of CLUSTER, I imagine that in most
instances you'd be confident the table isn't going to undergo much
change (or at least not quickly). CLUSTER FREEZE would mean not
having to run a separate VACUUM FREEZE after, and on a 10GB table,
that's a big deal as it means not having to rescan the whole table
again to freeze every row.

Note that REFRESH MATERIALIZED VIEW freezes rows too as it's already
writing all the data from scratch again, and has an exclusive lock.
(this isn't the case with the CONCURRENTLY option obviously)

--
Thom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

On 2013-10-24 00:28:44 +0100, Thomas Munro wrote:

I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to
add that, for consistency with VACUUM. Is it useful?

I think you'd need to prevent that form from working on system catalogs
- xmin has a meaning there sometimes (e.g. indcheckxmin) at least.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 24, 2013 at 10:28:43AM +0530, Amit Kapila wrote:

On Thu, Oct 24, 2013 at 4:58 AM, Thomas Munro <munro@ip9.org> wrote:

Hi
I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to add
that, for consistency with VACUUM. Is it useful?

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

Anyway code side, I think you need to set both feeze_min_age as well
as freeze_table_age, see VACUUM command in gram.y

CLUSTER opt_freeze opt_verbose qualified_name cluster_index_specification

{
ClusterStmt *n = makeNode(ClusterStmt);
- n->relation = $3;
- n->indexname = $4;
- n->verbose = $2;
+ n->relation = $4;
+ n->freeze_min_age = $2 ? 0 : -1;
+ n->indexname = $5;
+ n->verbose = $3;
..

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Hi Amit,

If the FREEZE is part of the CLUSTER, you would only read/write the table
once. With a follow-up VACUUM FREEZE, you would re-read/write a second time.
I, for one, would appreciate being able to perform both in the same run. (+1)

Regards,
Ken

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 24 October 2013 05:58, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Oct 24, 2013 at 4:58 AM, Thomas Munro <munro@ip9.org> wrote:

Hi
I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to

add

that, for consistency with VACUUM. Is it useful?

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

As others have said, the goal is to freeze and cluster in a single step.
You can already do that if you know how things work under the covers with:

SET vacuum_freeze_min_age = 0;
CLUSTER my_table;

This patch lets you say CLUSTER FREEZE instead. It mirrors VACUUM, which
can freeze tuples based on the GUC and tuple age or the FREEZE keyword.

Anyway code side, I think you need to set both feeze_min_age as well
as freeze_table_age, see VACUUM command in gram.y

Ok, I attach a new version that is more like VACUUM in gram.y. (Although
I'm not sure why it isn't a single boolean flag).

Thanks,
Thomas Munro

On Thu, Oct 24, 2013 at 1:09 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/24/2013 04:55 PM, Robert Haas wrote:

On Thu, Oct 24, 2013 at 1:09 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

+1 from me. Can you think of a reason you *wouldn't* want to freeze?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

In that case you'd have to invent a NOFREEZE keyword, no? Ick.

In any case, it's very far from obvious to me that CLUSTER ought
to throw away information by default, which is what you're proposing.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 24, 2013 at 10:39 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

So do you think that other places where we are rewriting the table
with exclusive lock, we should have such mechanism or option and even
if it is not there, it is kind of Todo item and tomorrow someone can
write a patch to improve that situation.

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

Yes, this is completely right and I understand this point, but the
question I had in my mind before writing my last mail was that whether
any or all places having this concept deserves to have an option like
FREEZE?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Oct 25, 2013 at 4:29 AM, Thomas Munro <munro@ip9.org> wrote:

On 24 October 2013 05:58, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Oct 24, 2013 at 4:58 AM, Thomas Munro <munro@ip9.org> wrote:

Hi
I noticed that CLUSTER doesn't have a FREEZE option. Here is a patch to
add
that, for consistency with VACUUM. Is it useful?

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

As others have said, the goal is to freeze and cluster in a single step.
You can already do that if you know how things work under the covers with:

SET vacuum_freeze_min_age = 0;
CLUSTER my_table;

True, but in that case why don't we just mention above in the
documentation of CLUSTER command, so that if user wants to freeze
along with Cluster, he can use above way to Cluster.
Some of the reason's, I could think of adding FREEZE as an option are:
a. it's more explicit and easy to use for user.
b. if by chance underlying mechanism changes (which is less likely)
then above way of doing Cluster might not result into freeze.

This patch lets you say CLUSTER FREEZE instead. It mirrors VACUUM, which
can freeze tuples based on the GUC and tuple age or the FREEZE keyword.

Anyway code side, I think you need to set both feeze_min_age as well
as freeze_table_age, see VACUUM command in gram.y

Ok, I attach a new version that is more like VACUUM in gram.y. (Although
I'm not sure why it isn't a single boolean flag).

The reason of separate flags is that both are used to decide different things,
freeze_min_age - this is used to decide the cutoff_xid, based on which
FrozenTransactionId will be placed on tuple.
freeze_table_age - used do decide, whether to scan all pages of a
relation in Vacuum and in Cluster command it is ignored as it needs to
scan all pages anyway.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-10-24 17:17:22 -0700, Josh Berkus wrote:

On 10/24/2013 04:55 PM, Robert Haas wrote:

On Thu, Oct 24, 2013 at 1:09 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

+1 from me. Can you think of a reason you *wouldn't* want to freeze?

It makes content from the future appear when you start using the
relation in a query/session with an older snapshot. Currently CLUSTER is
safe against that.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Oct 25, 2013 at 2:12 AM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-10-24 17:17:22 -0700, Josh Berkus wrote:

On 10/24/2013 04:55 PM, Robert Haas wrote:

On Thu, Oct 24, 2013 at 1:09 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

+1 from me. Can you think of a reason you *wouldn't* want to freeze?

It makes content from the future appear when you start using the
relation in a query/session with an older snapshot. Currently CLUSTER is
safe against that.

Eh, what? We wouldn't freeze XIDs that don't precede RecentGlobalXmin.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-10-25 09:13:20 -0400, Robert Haas wrote:

On Fri, Oct 25, 2013 at 2:12 AM, Andres Freund <andres@2ndquadrant.com> wrote:

On 2013-10-24 17:17:22 -0700, Josh Berkus wrote:

On 10/24/2013 04:55 PM, Robert Haas wrote:

On Thu, Oct 24, 2013 at 1:09 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 10/23/2013 09:58 PM, Amit Kapila wrote:

I wonder why anyone would like to freeze during CLUSTER command when
they already have separate way (VACUUM FREEZE) to achieve it, do you
know or can think of any case where user wants to do it along with
Cluster command?

"If I'm rewriting the table anyway, let's freeze it".

Otherwise, you have to write the same pages twice, if both CLUSTER and
FREEZE are required.

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

+1 from me. Can you think of a reason you *wouldn't* want to freeze?

It makes content from the future appear when you start using the
relation in a query/session with an older snapshot. Currently CLUSTER is
safe against that.

Eh, what? We wouldn't freeze XIDs that don't precede RecentGlobalXmin.

Ah sorry, I thought that'd be the plan, similar to COPY FREEZE. Maybe
because I've wished for it in the past ;)

In that case I agree it should be the default. There really isn't any
reason to not to immediately freeze tuples that can be frozen according
to the xmin horizon. We don't immediately do it during normal vacuums
because it would possibly cause superflous io - but that's not the case here.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 24, 2013 at 10:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

In that case you'd have to invent a NOFREEZE keyword, no? Ick.

Only if we think anyone would ever NOT want to freeze.

In any case, it's very far from obvious to me that CLUSTER ought
to throw away information by default, which is what you're proposing.

I find it odd to referring to this as throwing away information. I
know that you have a general concern about throwing away XIDs that are
still needed for forensic purposes, but that is clearly the ONLY
purpose that those XIDs serve, and the I/O advantages of freezing by
default could be massive for many of our users. What's going to
happen in practice is that experienced users will simply recommend
CLUSTER FREEZE rather than plain CLUSTER, and you won't have the
forensic information *anyway*.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/24/2013 07:19 PM, Tom Lane wrote:

In any case, it's very far from obvious to me that CLUSTER ought
to throw away information by default, which is what you're proposing.

The problem here is that you're thinking of the 1/10 of 1% of our users
who have a serious PostgreSQL failure and post something on the lists
for help, for which XID forensic information is useful. As opposed to
the 99.9% of our users for whom deferred freezing is a performance
burden. While I realize that the 0.1% of users are more likely to have
contact with you, personally, it's still bad policy for the project.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 25 October 2013 01:17, Josh Berkus <josh@agliodbs.com> wrote:

On 10/24/2013 04:55 PM, Robert Haas wrote:

I wonder if we should go so far as to make this the default behavior,
instead of just making it an option.

+1 from me. Can you think of a reason you *wouldn't* want to freeze?

Ok, I attach an alternative patch that makes CLUSTER *always* freeze,
without any option (but doesn't affect VACUUM FULL in the same way). I will
post both alternatives to the commitfest app since there seems to be some
disagreement about whether tuple freezing should be an optional.

Thanks
Thomas Munro

On 10/26/2013 01:20 AM, Josh Berkus wrote:

On 10/24/2013 07:19 PM, Tom Lane wrote:

In any case, it's very far from obvious to me that CLUSTER ought
to throw away information by default, which is what you're proposing.

The problem here is that you're thinking of the 1/10 of 1% of our users
who have a serious PostgreSQL failure and post something on the lists
for help, for which XID forensic information is useful. As opposed to
the 99.9% of our users for whom deferred freezing is a performance
burden. While I realize that the 0.1% of users are more likely to have
contact with you, personally, it's still bad policy for the project.

Strong +1 from me. Doing the performant, sensible, low-admin thing by
default is really important if you don't want a database that requires a
two year training course and a professional DBA to use. Some DB vendors
make that part of their business model, but personally at least that's
certainly not the direction I'd like to see Pg go in.

Autovacuum wrap-around prevention already gets rid of this info, it's
not like it's kept forever anyway.

Anything that makes the mechanics of bloat and vacuum less visible is a
big win as far as I'm concerned.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-10-25 09:26:29 -0400, Robert Haas wrote:

In any case, it's very far from obvious to me that CLUSTER ought
to throw away information by default, which is what you're proposing.

I find it odd to referring to this as throwing away information. I
know that you have a general concern about throwing away XIDs that are
still needed for forensic purposes, but that is clearly the ONLY
purpose that those XIDs serve, and the I/O advantages of freezing by
default could be massive for many of our users. What's going to
happen in practice is that experienced users will simply recommend
CLUSTER FREEZE rather than plain CLUSTER, and you won't have the
forensic information *anyway*.

I think we should just apply your "preserve forensic information when
freezing" patch. Then we're good to go without big arguments ;)

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers