Today I got familiar problem with vacuum analyze
discovery=> select version();
version
------------------------------------------------------------------------
PostgreSQL 6.5.1 on i686-pc-linux-gnulibc1, compiled by gcc egcs-2.91.66
(1 row)
discovery=> vacuum analyze;
NOTICE: AbortTransaction and not in in-progress state
pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
This is the last cvs (REL6_5_PATCHES).
It's interesting, that I can do vacuum analyze for all tables in this
database without any problem !
I dump my database and reload it. After that vacuum analyze worked fine.
But after intensive testing of my Web-server I got the same problem.
I accumulate documents hits in my database using persistent connection
and this is the only update/insert operation.
I use function to workaround update/insert dilemma -
I can't just use update. This is modified function suggested by
Philip Warner. I'm sure problem somehow connects with this,
because I had no problem when I didn't accumulate statistics but just
insert every hits using simple sql.
Regards,
Oleg
create table hits ( msg_id int4 not null primary key, count int4 not null
);
CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS '
Declare keyval Alias For $1; cnt int4;
Begin Select count into cnt from hits where msg_id = keyval; if Not Found then cnt := 1; Insert Into
hits(msg_id,count) values (keyval, cnt); else cnt := cnt + 1; Update hits set count = cnt where msg_id =
keyval; End If; return cnt;
End;
' LANGUAGE 'plpgsql';
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
