large table

[Luke Coldiron <lukecoldiron@xxxxxxxxxxx>]

I am trying to figure out what would have caused a table in a PostgreSQL 8.4.16 to get into a state where there is only 1 live tuple and has only ever had one 1 tuple but the size of the table is huge.

CREATE TABLE public.myTable

(  myColumn timestamp with time zone NOT NULL

);

Note: there is no primary key or index on this table.

CREATE OR REPLACE FUNCTION public.myFunc()

RETURNS VOID AS $$

BEGIN

   UPDATE public.myTable SET myColumn = CLOCK_TIMESTAMP();

   IF NOT FOUND THEN

      INSERT INTO public.myTable(myColumn) VALUES (CLOCK_TIMESTAMP());

   END IF;

END;

$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.wasUpdated(OUT was_updated boolean)

RETURNS BOOLEAN AS $$

BEGIN

   was_updated := COALESCE((SELECT myColumn FROM public.myTable) > (CLOCK_TIMESTAMP() - INTERVAL '5 SECOND'), 

                                                    FALSE);

END;

$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;

SELECT *

FROM pg_stat_all_tables

WHERE relname = 'myTable';

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

16713

public

myTable

3991833

3992001

0

3775409

0

3771173

949135

183

2014-09-18 11:28:47.63545+00

2014-09-18 11:27:47.134432+00

The stats are very far off with n_live_tup at 949135 when there is only a single row in the table. Autovacuum appears to be running on a regular basis.

SELECT *
FROM pgstattuple('public.myTable');

table_len	tuple_count	tuple_len	tuple_percent	dead_tuple_count	dead_tuple_len	dead_tuple_percent	free_space	free_precent
34709504	1	32	0	105	3360	0.01	30757308	88.61

The actual size of the table is around 33 MB.

The myFunc function is called every 2.5 seconds and the wasUpdated function every 2 seconds by separate processes. 

I realize that running a FULL VACUUM or CLUSTER command on the table will resolve the issue but I am more interested in a root cause that explains why this table would end up in this state. I have tried to reproduce this issue by running the exact setup and have not been able to get the table to grow like this example. Any plausible cause'es or explanations would be much appreciated.

Luke