Re: all empty tables

On 14/11/2009 6:12 PM, Garry Saddington wrote:
> How could I list all the tables in a database that do not contain any data?
> I have looked at reltuples but can't quite work out how to use it, any
> pointers would be much apreciated.

Define "empty". In a MVCC database, it's harder than you'd think.

A table of zero on-disk size is definitely empty. So is a table where no
tuples are visible to any currently running or future transactions.
Anything else is arguable. What if the tuples have all been DELETEd by a
transaction that's committed, but one or more statements (or
SERIALIZABLE transactions) are running that are working with the state
of the database as it was before the DELETE committed? Is the table then
empty?

The best answer I can suggest is that the following statements, run as
the super user while no other users are connected, will list the names
of tables that are completely empty:

-- First clear out dead tuples and truncate any tables to the position
-- of the last live tuple:
VACUUM;
-- Then find any zero size tables that result:
SELECT relname FROM pg_catalog.pg_class WHERE relpages = 0
AND NOT relisshared AND NOT relhassubclass AND relkind = 'r'
AND relnamespace <>
(SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog');

By "no tuples visible to any currently running transaction" I mean one
where all tuples have been deleted by a transaction that committed
before any currently running transactions started. If you VACUUM such a
table, it is truncated to zero size.

With only one session running in the database, for example, we can
create a dummy table then delete all records from it and see that it's
still non-zero size even though no transactions that can "see" the
deleted data are still running. When we VACUUM it, though, the dead
tuples are marked and the table is truncated to the position of the last
"live" tuple. As there aren't any live tuples, it gets truncated to zero
size:

# CREATE TABLE test AS SELECT * FROM generate_series(0,1000) AS x;

# SELECT pg_relation_size('test'::regclass);
32768

# DELETE FROM test;

# SELECT current_query, procpid FROM pg_stat_activity
WHERE procpid <> pg_backend_pid();
(0 rows returned - no currently running transaction can see the data we
just deleted.)

# SELECT pg_relation_size('test'::regclass);
32768

# VACUUM test;

# SELECT pg_relation_size('test'::regclass);
0

--
Craig Ringer