469,963 Members | 1,312 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

VACUUM FULL produce warnings

look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
6 1607
sp***@yandex.ru writes:
look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295


I wouldn't worry about it. It's probably due to the fact that we tend
to release write lock on system catalogs immediately after updating the
catalog, instead of holding the lock till commit. That means it's
possible for VACUUM FULL to see uncommitted tuples in system catalogs,
which it should never do in user tables.

(This theory does assume that you are making system catalog changes
concurrently with running VACUUM, which doesn't entirely square with
your statement that database updates are "rare". Perhaps you create
and delete temp tables frequently?)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
sp***@yandex.ru writes:
look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295


I wouldn't worry about it. It's probably due to the fact that we tend
to release write lock on system catalogs immediately after updating the
catalog, instead of holding the lock till commit. That means it's
possible for VACUUM FULL to see uncommitted tuples in system catalogs,
which it should never do in user tables.

(This theory does assume that you are making system catalog changes
concurrently with running VACUUM, which doesn't entirely square with
your statement that database updates are "rare". Perhaps you create
and delete temp tables frequently?)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
>> look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295

TL> I wouldn't worry about it. It's probably due to the fact that we tend
TL> to release write lock on system catalogs immediately after updating the
TL> catalog, instead of holding the lock till commit.

ok, but what is best:
- leave as is
- do REINDEX DASTABASE before VACUUM FULL
- stop all transactions before VACUUM FULL

TL> That means it's possible for VACUUM FULL to see uncommitted
TL> tuples in system catalogs, which it should never do in user tables.

if i understand right, after some time vacuum must run without
warnings, but after first warning vacuum print this warnings every
time until reindex.

TL> (This theory does assume that you are making system catalog changes
TL> concurrently with running VACUUM, which doesn't entirely square with
TL> your statement that database updates are "rare". Perhaps you create
TL> and delete temp tables frequently?)

oops, it's my mistake.
i do
SELECT ... INTO TEMPORARY ...
instead
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
CREATE VIEW ... AS SELECT ...

what way (create temporary table or set isolation level on
transaction) is better?

ps: sorry, english it's not my native language
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
>> look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295

TL> I wouldn't worry about it. It's probably due to the fact that we tend
TL> to release write lock on system catalogs immediately after updating the
TL> catalog, instead of holding the lock till commit.

ok, but what is best:
- leave as is
- do REINDEX DASTABASE before VACUUM FULL
- stop all transactions before VACUUM FULL

TL> That means it's possible for VACUUM FULL to see uncommitted
TL> tuples in system catalogs, which it should never do in user tables.

if i understand right, after some time vacuum must run without
warnings, but after first warning vacuum print this warnings every
time until reindex.

TL> (This theory does assume that you are making system catalog changes
TL> concurrently with running VACUUM, which doesn't entirely square with
TL> your statement that database updates are "rare". Perhaps you create
TL> and delete temp tables frequently?)

oops, it's my mistake.
i do
SELECT ... INTO TEMPORARY ...
instead
START TRANSACTION ISOLATION LEVEL SERIALIZABLE
CREATE VIEW ... AS SELECT ...

what way (create temporary table or set isolation level on
transaction) is better?

ps: sorry, english it's not my native language
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5
sp***@yandex.ru writes:
if i understand right, after some time vacuum must run without
warnings, but after first warning vacuum print this warnings every
time until reindex.


Oh? I was thinking of transient conditions, but it sounds like you
have found a way to produce a permanent mismatch in the tuple counts.
Can you provide a test case that gets the catalogs into this state?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6
sp***@yandex.ru writes:
if i understand right, after some time vacuum must run without
warnings, but after first warning vacuum print this warnings every
time until reindex.


Oh? I was thinking of transient conditions, but it sounds like you
have found a way to produce a permanent mismatch in the tuple counts.
Can you provide a test case that gets the catalogs into this state?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

66 posts views Thread by Bo Lorentsen | last post: by
2 posts views Thread by lnd | last post: by
6 posts views Thread by Alex | last post: by
reply views Thread by Jim Seymour | last post: by
reply views Thread by Rajesh Kumar Mallah | last post: by
15 posts views Thread by Ed L. | last post: by
4 posts views Thread by Ilia Chipitsine | last post: by
9 posts views Thread by Aleksey Serba | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.