CODEWITHSUNDEEP
databasepostgresqllockingpostgresql-9.1
igorrs
igorrs

Reputation: 350

Locks when adding a column to a table and to a view

Suppose I have this foo table in a PostgreSQL 9.1 database:

CREATE TABLE foo
(
  bar integer,
  flg_deleted boolean
);

Along with this vwfoo view:

CREATE VIEW vwfoo AS
  SELECT bar
  FROM foo
  WHERE flg_deleted = false;

Also assume I have an application running a couple of short-lived transactions per second, using vwfoo.

Now, I want to add a column baz to foo and I want baz to be in vwfoo too. But, of course, I don't want my application to get any errors because of those changes.

If I execute the following steps (in a single transaction) to perform the desired change:

  1. Drop vwfoo.
  2. Add column baz to foo.
  3. Create vwfoo again (now including baz).

Do I get the desired behavior (no errors in the application)?

Will there be an exclusive lock being held on vwfoo during the whole transaction (that's what I want)?

Is it possible that any transaction will try to use the view between steps 1 and 3 and then fail (instead of just blocking, waiting for a lock)?

Does the "identity" of vwfoo change when it's recreated? In other words: is it possible that any transaction will try to use the view between steps 1 and 3, block, resume after step 3 and then fail because the view was recreated?

Thanks.

Upvotes: 2

Views: 156

Answers (1)

David Wolever
David Wolever

Reputation: 154454

A quick test suggests that this will cause problems with your application. To recreate, create two connections (A and B), then run:

A: BEGIN;
A: DROP VIEW vwfoo;
B: SELECT * FROM vwfoo;
(B blocks… )
A: CREATE VIEW vwfoo AS SELECT * FROM foo;
A: COMMIT;
(B yields:
    ERROR:  could not open relation with OID 326418
    LINE 1: SELECT * FROM vwfoo
)

Instead, you should do this kind of atomic swapping by renaming the views:

A: CREATE VIEW vwfoo_new AS SELECT * FROM foo;
A: BEGIN;
A: ALTER VIEW vwfoo RENAME TO vwfoo_old;
B: SELECT * FROM vwfoo;
(B blocks…)
A: ALTER VIEW vwfoo_new RENAME TO vwfoo;
A: COMMIT;
(B completes as expected)
A: DROP TABLE vwfoo_old;

This will work as expected (and you won't need to run a (comparatively) expensive DROP TABLE inside the transaction!)

Edit: You can use the same strategy to solve your "real" problem, too:

ALTER TABLE foo ADD COLUMN bar_new TEXT;
UPDATE foo SET bar_new = bar;
CREATE VIEW vwfoo_new AS SELECT bar_new AS bar FROM foo;
… do the view switcheroo …
DROP VIEW vwfoo_old;
BEGIN;
ALTER TABLE foo RENAME bar TO bar_old;
ALTER TABLE foo RENAME bar_new TO bar;
COMMIT;
ALTER TABLE foo DROP COLUMN bar_old;

The view referencing bar_new will be correctly updated, too:

# \d vwfoo
View definition:
 SELECT foo.a
   FROM foo
  WHERE foo.b = false;
# ALTER foo RENAME a TO new_a;
ALTER TABLE
# \d vwfoo
View definition:
 SELECT foo.newa AS a
   FROM foo
  WHERE foo.b = false;

Upvotes: 1

Related Questions