On 21 Dec 2011, at 24:56, Culley Harrelson wrote:
> Several years ago I added table_b_rowcount to table A in order to minimize
> queries on table B. And now, as the application has grown, I am starting to
> having locking problems on table A. Any change to table B requires the that
> table_b_rowcount be updated on table A... The application has outgrown this
> solution.
When you update rowcount_b in table A, that locks the row in A of course, but
there's more going on. Because a new version of that row gets created, the
references from B to A also need updating to that new version (creating new
versions of rows in B as well). I think that causes a little bit more locking
than originally anticipated - it may even be the cause of your locking problem.
Instead, if you'd create a new table C that only holds the rowcount_b and a
reference to A (in a 1:1 relationship), most of those problems go away. It does
add an extra foreign key reference to table A though, which means it will weigh
down updates and deletes there some more.
CREATE TABLE C (
table_a_id int PRIMARY KEY
REFERENCES table_a (id) ON UPDATE CASCADE ON DELETE CASCADE,
table_b_rowcount int NOT NULL DEFAULT 0
);
Yes, those cascades are on purpose - the data in C is useless without the
accompanying record in A. Also, the PK makes sure it stays a 1:1 relationship.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
