VACUUM FULL produce warnings

[spied]

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

[Tom Lane]

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

[Tom Lane]

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

[spied]

>> 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

[spied]

>> 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

[Tom Lane]

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

[Tom Lane]

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