Re: all empty tables - Mailing list pgsql-general

On Sat, Nov 14, 2009 at 6:01 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> 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

If all you care about is if a  table has no rows visible to the
current transaction, wouldn't:
select coalesce((select 1 from the_table limit 1), 0)::bool as not_empty;

be an efficient way to do it (hooking into your idea to get the list
of tables to check above)?

merlin