Re: RES: Lock Problem - Mailing list pgsql-sql

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