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
