Hello:
I'm trying to get pgreplicator running under 7.2b4.
When issuing the "select pgr_replica_table('some_table')", it fails
with:
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.
I tried to make sense of what's doing and ran into this:
DEBUG: query: alter table "prueba" add column pgr_grid int8 not null
Under 7.1, the column is appended to the table without the NOT NULL
constraint, silently ignoring the constraint. Under 7.2 the command is
noted as invalid, as it should be (I wonder why the pgreplicator
developers never noted that the NOT NULL constraint never made into the
table). But it breaks pgreplicator. For now I'm sticking to 7.1.3, but
I'm wondering whether the "right" behaviour is to wait for ALTER TABLE
... NOT NULL to be implemented.
I'm currently trying to hack pgreplicator into issuing the right ALTER
TABLE ADD CONSTRAINT, but cannot find the right way to add a NOT NULL
clause. In CREATE TABLE, NOT NULL is only a column_constraint, and ALTER
TABLE seems to like only table_constraint syntax (or so suggest the \h
command in pqsl and the reference docs). I cannot figure it out, and the
ALTER TABLE reference for 7.2 in Bruce's site doesn't give hints on it.
Also, if adding NOT NULL constraints is not supported by ALTER TABLE ADD
CONTRAINT, I think the error message for ALTER TABLE ADD COLUMN should
not suggest otherwise.
There is a workaround by Joel Burton
(http://techdocs.postgresql.org/techdocs/updatingcolumns.php) by hacking
pg_attribute manually, but the gotchas are dangerous (and anyway, now
that I think of it, what value should one populate the new NOT NULL
column with?).
--
Alvaro Herrera (<alvherre@atentus.com>)