Thread: Reltuples/n_live_tup values wrong

Hi,

I'm facing a problem which seems like a bug to me. I'm running:

centos 5.9
postgresql92.x86_64 (9.2.4-1PGDG.rhel5)

I have a table whith many inserts/deletes

> select * from pg_stat_user_tables where schemaname = 'lobby' and
> relname = 'lobby_player' order by n_live_tup desc;
> relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
> idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
> n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze
> | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count |
> autoanalyze_count
>
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+------
>
-------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
> 16594 | lobby | lobby_player | 231335 | 7156825 | 3726165 | 3334044 |
> 277457 | 124 | 277380 | 118 | 608965 | 187 | 2013-08-07
> 11:38:08.877226+02 | 2013- 08-07 10:48:16.282828+02 | 2013-08-07
> 11:17:50.551531+02 | 2013-08-06 19:48:41.649425+02 | 8 | 25 | 4 | 24
> (1 row)


> select reltuples from pg_class where relname = 'lobby_player';
> reltuples
> -----------
> 608985
> (1 row)


But my Problem is the following:

> select count(*) from lobby_player;
> count
> -------
> 10
> (1 row)

If I do an analyze, the stats change:

> select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
> from pg_stat_user_tables where schemaname = 'lobby' and relname =
> 'lobby_player' order by n_live_tup desc;
> relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum
> --------------+------------+------------+-------------------------------+-------------------------------
> lobby_player | 15 | 609186 | 2013-08-07 11:38:08.877226+02 |
> 2013-08-07 10:48:16.282828+02
> (1 row)

Reltuples stays at the 600k value, after another vacuum the tuples are
live again.

I already did a reindex table, dropped the index+pkey, vacuum full but
it doesn't change anything.

Another thing I see is:

> \dt+ lobby_player
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+--------------+-------+-------+-------+-------------
> lobby | lobby_player | table | adm | 46 MB |
> (1 row)

> select oid from pg_class where relname = 'lobby_player';
> oid
> -------
> 16594
> (1 row)

> postgres@database:~ # ls -alh 9.2/data/base/*/16594*
> -rw------- 1 postgres postgres 0 Aug 7 11:34 9.2/data/base/16384/16594

So the 46MB are not there

> vacuum ANALYZE VERBOSE lobby_player;
> INFO:  vacuuming "lobby.lobby_player"
> INFO:  index "lobby_player_pkey" now contains 609319 row versions in
> 2348 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 8.03 sec.
> INFO:  index "idx_lobby_player_gtype_player_type" now contains 609319
> row versions in 1674 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 1.00 sec.
> INFO:  "lobby_player": found 0 removable, 609319 nonremovable row
> versions in 5937 out of 5937 pages
> DETAIL:  609299 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 16.18 sec.
> INFO:  vacuuming "pg_toast.pg_toast_16594"
> INFO:  index "pg_toast_16594_index" now contains 0 row versions in 1 pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "pg_toast_16594": found 0 removable, 0 nonremovable row
> versions in 0 out of 0 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  analyzing "lobby.lobby_player"
> INFO:  "lobby_player": scanned 5937 of 5937 pages, containing 21 live
> rows and 609302 dead rows; 21 rows in sample, 21 estimated total rows
> VACUUM

Thanks for any help!
-Sebastian

Sebastian Kornehl <webmaster@sourcebase.org> writes:
> I'm facing a problem which seems like a bug to me. I'm running:

It seems like most of your problem is explained by this:

>> DETAIL:  609299 dead row versions cannot be removed yet.

You need to get rid of whatever old open transaction is preventing
those rows from getting vacuumed away.  Perhaps you have a prepared
transaction lying around?

            regards, tom lane

Hi Tom,

thanks for your Reply!

You might be right, I found some pg_prepared_xacts from 2013-05-23. This
was a test with a XA using application, but the application is already
offline. There is also no pid available for the given transaction id's.

Do you have any idea how to close these xa transactions without
restarting the whole db?

Thank you.

-Sebastian

On 08/07/2013 02:12 PM, Tom Lane wrote:
> Sebastian Kornehl <webmaster@sourcebase.org> writes:
>> I'm facing a problem which seems like a bug to me. I'm running:
> It seems like most of your problem is explained by this:
>
>>> DETAIL:  609299 dead row versions cannot be removed yet.
> You need to get rid of whatever old open transaction is preventing
> those rows from getting vacuumed away.  Perhaps you have a prepared
> transaction lying around?
>
>             regards, tom lane

On Wed, Aug 7, 2013 at 1:50 PM, Sebastian Kornehl
<webmaster@sourcebase.org>wrote:

> Do you have any idea how to close these xa transactions without restarting
> the whole db?


Restarting the database wouldn't accomplish anything. Prepared transactions
are persistent across reboots.

http://www.postgresql.org/docs/9.3/static/sql-rollback-prepared.html


--
greg

Sebastian Kornehl <webmaster@sourcebase.org> writes:
> You might be right, I found some pg_prepared_xacts from 2013-05-23. This
> was a test with a XA using application, but the application is already
> offline. There is also no pid available for the given transaction id's.

> Do you have any idea how to close these xa transactions without
> restarting the whole db?

Just use ROLLBACK PREPARED with the ID you see in pg_prepared_xacts.

            regards, tom lane

Thank you, that did it!

On 08/07/2013 03:30 PM, Tom Lane wrote:
> Sebastian Kornehl <webmaster@sourcebase.org> writes:
>> You might be right, I found some pg_prepared_xacts from 2013-05-23. This
>> was a test with a XA using application, but the application is already
>> offline. There is also no pid available for the given transaction id's.
>> Do you have any idea how to close these xa transactions without
>> restarting the whole db?
> Just use ROLLBACK PREPARED with the ID you see in pg_prepared_xacts.
>
>             regards, tom lane