large table

[Luke Coldiron]

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_tablesWHERE 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

[Alan Hodgson]

Post by Luke Coldiron
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

I'd guess that some other process held a transaction open for a couple of
week, and that prevented any vacuuming from taking place.

[Luke Coldiron]

Subject: Re: [GENERAL] large table
Date: Mon, 22 Sep 2014 11:34:45 -0700

Post by Luke Coldiron
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

I'd guess that some other process held a transaction open for a couple of
week, and that prevented any vacuuming from taking place.

Interesting idea, on the surface I'm not sure how this would have happened in the system but I can certainly explore forcing this to happen and see if the result is similar.

--
http://www.postgresql.org/mailpref/pgsql-general