Folks,

Per Neil Conway, here's some doc patches re: the autovacuum daemon's
behavior. Should this be back-patched to 8.2x?

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Sun, May 13, 2007 at 10:06:40PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > Per Neil Conway, here's some doc patches re: the autovacuum
> > daemon's behavior. Should this be back-patched to 8.2x?
>
> This fact is already documented in at least three places; do we
> really need two more?

Yes.

> The proposed addition to postgresql.conf seems particularly
> over-the-top, since there is no entry in that file that even
> pretends to offer a complete description of the associated behavior.

I think that a boolean that doesn't do what you expect booleans to do,
i.e. turn the thing all the way off, is worth a mention.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sun, 2007-13-05 at 22:06 -0400, Tom Lane wrote:
> This fact is already documented in at least three places; do we really
> need two more?

I think we need to at least modify the documentation for the autovacuum
GUC parameter, which currently states only that it "controls whether the
server should run the autovacuum launcher daemon" -- this is not
strictly true, and in any case, it isn't the whole story.

> The proposed addition to postgresql.conf seems particularly
> over-the-top

I agree that this information doesn't really belong in postgresql.conf.

-Neil



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

neilc@samurai.com (Neil Conway) writes:
> On Sun, 2007-13-05 at 22:06 -0400, Tom Lane wrote:
>> This fact is already documented in at least three places; do we really
>> need two more?
>
> I think we need to at least modify the documentation for the autovacuum
> GUC parameter, which currently states only that it "controls whether the
> server should run the autovacuum launcher daemon" -- this is not
> strictly true, and in any case, it isn't the whole story.
>
>> The proposed addition to postgresql.conf seems particularly
>> over-the-top
>
> I agree that this information doesn't really belong in postgresql.conf.

Question... (note: this does not strictly fit into the purview of the
..patches list)

Would the following 'maintenance' regimen be truly safe against XID
wraparound:

- Most tables are being vacuumed regularly, so that
pg_class.relfrozenxid is kept "safe."

- There are some tables that periodically get TRUNCATEd so that, in
principle, they never need to be vacuumed.

Is it actually true that we'd never need to vacuum those tables
(assuming 8.2+)? I suppose it would be rather cheap to VACUUM
immediately after the TRUNCATE...

The application is one where we might use partitioning, rolling from
table to table every so often, with the expectation that we'll
TRUNCATE the eldest data often enough that we shouldn't need to VACUUM
any of the partitions.
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/linuxxian.html
Why are men like blenders?
You need one, but you're not quite sure why.

Chris Browne wrote:

> Would the following 'maintenance' regimen be truly safe against XID
> wraparound:
>
> - Most tables are being vacuumed regularly, so that
> pg_class.relfrozenxid is kept "safe."
>
> - There are some tables that periodically get TRUNCATEd so that, in
> principle, they never need to be vacuumed.
>
> Is it actually true that we'd never need to vacuum those tables
> (assuming 8.2+)? I suppose it would be rather cheap to VACUUM
> immediately after the TRUNCATE...

You'd need to vacuum after the truncate. It would be pretty cheap, the
tables being empty.

I suppose it would be pretty trivial to set the relfrozenxid to
RecentXmin or something during TRUNCATE.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Alvaro Herrera <alvherre@commandprompt.com> writes:
> I suppose it would be pretty trivial to set the relfrozenxid to
> RecentXmin or something during TRUNCATE.

I had the idea we were doing that already --- at least I'm pretty sure I
remember it being discussed. But I see it's not being done in HEAD.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I suppose it would be pretty trivial to set the relfrozenxid to
> > RecentXmin or something during TRUNCATE.
>
> I had the idea we were doing that already --- at least I'm pretty sure I
> remember it being discussed. But I see it's not being done in HEAD.

Patch to do it attached. I am thinking we can do something similar in
CLUSTER as well.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, May 15, 2007 at 06:13:47PM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > I suppose it would be pretty trivial to set the relfrozenxid to
> > > RecentXmin or something during TRUNCATE.
> >
> > I had the idea we were doing that already --- at least I'm pretty sure I
> > remember it being discussed. But I see it's not being done in HEAD.
>
> Patch to do it attached. I am thinking we can do something similar in
> CLUSTER as well.

Actually, it already happens for CLUSTER because cluster calls
heap_create_with_catalog, which calls AddNewRelationTuple. See
backend/catalog/heap.c line 716.
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jim C. Nasby wrote:
> On Tue, May 15, 2007 at 06:13:47PM -0400, Alvaro Herrera wrote:
> > Tom Lane wrote:
> > > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > > I suppose it would be pretty trivial to set the relfrozenxid to
> > > > RecentXmin or something during TRUNCATE.
> > >
> > > I had the idea we were doing that already --- at least I'm pretty sure I
> > > remember it being discussed. But I see it's not being done in HEAD.
> >
> > Patch to do it attached. I am thinking we can do something similar in
> > CLUSTER as well.
>
> Actually, it already happens for CLUSTER because cluster calls
> heap_create_with_catalog, which calls AddNewRelationTuple. See
> backend/catalog/heap.c line 716.

Right, but that heap is dropped later, and only the relfilenode remains,
because they are swapped.

In any case the change is a very small patch, which I attach but I
haven't tested. This only works if the new rewriteheap stuff actually
changes Xids to follow OldestXmin, i.e. all tuples that have older
Xmin/Xmax are frozen (or marked with the current Xid). Heikki, can you
confirm that this is the case?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> I had the idea we were doing that already --- at least I'm pretty sure I
>> remember it being discussed. But I see it's not being done in HEAD.

> Patch to do it attached. I am thinking we can do something similar in
> CLUSTER as well.

Umm ... you'd have to be a lot more conservative in CLUSTER now that
it's MVCC-safe. I don't say that CLUSTER can't push up relfrozenxid,
but there's something wrong if CLUSTER and TRUNCATE are trying to
push it up the same amount.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate