Here is some vanilla SQL:
CREATE TABLE RoomTypes
(
RoomType VARCHAR(12) NOT NULL,
UNIQUE (RoomType)
);
CREATE TABLE Zones
(
Zone VARCHAR(10) NOT NULL,
UNIQUE (Zone)
);
CREATE TABLE Rooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);
CREATE TABLE NumberedRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Numbered'),
UNIQUE (Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
CREATE TABLE AncillaryRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Ancillary'),
UNIQUE (Zone, Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
The CHECK
constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name
in table NumberedRooms
represents an integer.
The idea that every row in the supertype table Rooms
will have exactly one row in the union of AncillaryRooms
and NumberedRooms
. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.
Note NumberedRooms
has a simple key Name
alone, whereas AncillaryRooms
has a compound key on (Zone, Name)
. All three (non-lookup) tables have a key on (RoomType, Zone, Name)
throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType
values.
When you issue an ALTER TABLE
in PostgreSQL it will take an ACCESS EXCLUSIVE
lock that blocks everything including SELECT
. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE
, CHECK
or FOREIGN KEY
constraints need expensive full-table scans to verify, etc.
If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE
if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.
Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t
and you want to change column customercode integer NOT NULL
to text
because the customer has decided all customer codes must now begin with an X
, you could write:
ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );
... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT
. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:
ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;
This will only prevent writes to t
during the process; the lock name EXCLUSIVE
is somewhat deceptive in that it excludes everything except SELECT
; the ACCESS EXCLUSIVE
mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE
, but at worst you'll just have to do it again.
You can even avoid that lock and do the whole thing live by creating a trigger function on t
that whenever an INSERT
or UPDATE
comes in, automatically populates customercode_new
from customercode
.
There are also built-in tools like CREATE INDEX CONCURRENTLY
and ALTER TABLE ... ADD table_constraint_using_index
that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.
The pg_reorg
tool or its successor pg_repack
can be used for some table restructuring operations as well.
Best Answer
We've ended up writing our own application that manages the Table and Column renames