[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] RES: Lock Problem
From:       Christopher Browne <cbbrowne () acm ! org>
Date:       2006-08-25 3:00:55
Message-ID: 87d5apzfh4.fsf () wolfe ! cbbrowne ! com
[Download RAW message or body]

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]