Hi Paolo,
How's it going?
Can you remember why we made the pgsql_plugin do an exclusive table
lock? I vaguely remember us discussing it privately, but can not recall
the rational.
Everything I have read about PostgreSQL indicates that it's designed to
work will with row level locking, in comparison to MySQL's sloppy locking.
I have recently upgraded to PostgreSQL 8.0 to obtain some better
performance, but more importantly, more control over vacuuming. But have
noticed the following characteristics:
1) can't do a database VACUUM while UPDATEs are happening
2) in a situation with 3-4 routers writing to a central SQL database, Pg
puts the pmacctd SQL Updates on hold for the other routers due to the
table wide lock... even though they are updating different record IDs
(different src/dst IPs, different agent_id, etc). This is on a table by
table basis of course, since the transaction only hits one table at a time.
So I'm wondering what would break if I change the lock? I'm testing
this now, using the following change in pgsql_plugin.c:
/* "LOCK ..." stuff */
snprintf(lock_clause, sizeof(lock_clause), "BEGIN; ", config.sql_table);
//snprintf(lock_clause, sizeof(lock_clause), "BEGIN; LOCK %s IN
EXCLUSIVE MODE;", config.sql_table);
Here is some more info:
http://www.postgresql.org/docs/8.0/static/sql-lock.html
http://www.postgresql.org/docs/8.0/static/explicit-locking.html
Cheers,
Wim
