I have almost completed one web site migration from mysql to pgsql. It
is already running totally pgsql.
But there is one last conversion problem. Most of the queries use
tables populated every 3 hours.
In mysql, for the site to not be unavailable during updating i was
creating temporary tables, populating them, dropping the main tables
and then renaming the temp tables to the main tables. The updating is
not trivial (for me) and needs some coding effort to be done.
Since pgsql has MVCC I wanted to eliminate the table rotation step and
use a transaction to update the tables. But what is happening is that
the plpgsql update functions are locking the tables and this is what
the web clients are getting (from ps ax):
26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting
23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting
31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting
21601 ? S 0:00 postgres: user database 127.0.0.1(46770) SELECT waiting
I've been trying for 3 days to figure out what is happening to no
avail. What am i missing about transactions and MVCC? What could make
a plpgsql update function lock a table? The indexes are default btree.
Otherwise the functions are behaving exactly as expected.
Regards, Clodoaldo Pinto