From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: RES: Lock Problem
Date: Thu, 24 Aug 2006 23:00:55 -0400
Organization: cbbrowne Computing Inc
Lines: 71
Message-ID: <87d5apzfh4.fsf@wolfe.cbbrowne.com>
References: <3FFA1818C1550246933DEA311C39B07612304A@CGEXC03.compugraf.com.br>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:WB6FJ5WjUGOutxuDNg7f4yNruQc=
To: pgsql-sql@postgresql.org

Centuries ago, Nostradamus foresaw when AGuergolet@compugraf.com.br (André José Guergolet) would write:
> Sorry, I have a table with 360 rows, in this table I control the state of machines on network:
>
>
> Ip 			State		StateDate
> 172.20.0.39		Running	2006-08-23 00:00:00
> 172.20.0.59		Running	2006-08-23 00:00:00
> 172.20.0.72		Running	2006-08-23 00:00:00
> 172.20.0.84		Running	2006-08-23 00:00:00
> 172.20.0.35		Running	2006-08-23 00:00:00
> 172.20.0.17		Running	2006-08-23 00:00:00
> 172.20.0.28		Running	2006-08-23 00:00:00
> 172.20.0.39		Running	2006-08-23 00:00:00
> 172.20.0.14		Running	2006-08-23 00:00:00
> 172.20.0.33		Running	2006-08-23 00:00:00
> 172.20.0.19		Running	2006-08-23 00:00:00
>
> My system, checks if my script is running in each machine at this
> table, this table has 360 rows and has 50-100 updates per minute in
> columns STATE and STATEDATE.

Question: How often are you vacuuming this table?

If, as you indicate, you're updating about 1/4 of the table each
minute, you should probably VACUUM the table about once a minute.

If you only VACUUM it once an hour or once a day, those 360 tuples
will be spread across 200,000 pages, and need a VACUUM FULL and a
REINDEX to draw the table back down to a decent size.

You can see how badly the table has grown by running the SQL:
  VACUUM VERBOSE machinestates;

This will list various statistics; generally, if you have many more
pages than tuples, there's probably a problem with how often you're
vacuuming...

> I list this states with a webpage. This webpage updates the list
> every 10 seconds. My page executes only "select * from
> machinestates".
>
> If I stop the updates, I never get my page stopped at the select
> command.

I don't understand what you mean by that.

In PostgreSQL, readers don't block writers and writers don't block
readers (unless you expressly ask for them to do so).

> I read about "DIRTY Transaction", is it the way?
> I have another solution?

I'm not sure you have successfully described either the observed
phenomenon or the nature of the problem; I know I don't yet quite
understand what seems wrong.

Sometimes language gets in the way; that seems possible here.  I also
think you're trying to solve the problem before understanding what it
is.

Please try to explain further what phenomenon you are observing; as
details emerge, hopefully someone will recognize what is going on.
Trying to fix it isn't the right thing to do at this point; just try
to explain what you see happening.  Getting to the point where someone
recognizes what is happening is really the goal.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/lsf.html
"Just because the code is intended to cause flaming death is no reason
to get sloppy and leave off  the casts." - Tim Smith, regarding sample
(F0 0F C7 C8) Pentium Death code on comp.os.linux.advocacy