Re: [GENERAL] Altering a table with a rowtype column

On Wed, Mar 7, 2012 at 1:17 PM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
> As a followup, the workaround fails if there is data in the source table due
> to the initial null value placed in the existing data rows.
>
> [wcs1459(at)aclnx-cisp01 ~]$ psql --port=5433 -e -f x
> begin;
> BEGIN
> create table a (
>   id serial,
>   stuff text,
>   more_stuff text
> );
> psql:x:6: NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for
> ser
>     ial column "a.id"
> CREATE TABLE
> create table a_audit (
>   id serial,
>   a_old a,
>   a_new a
> );
> psql:x:12: NOTICE:  CREATE TABLE will create implicit sequence
> "a_audit_id_seq"
>                  for serial column "a_audit.id"
> CREATE TABLE
> insert into a (stuff, more_stuff) values ('some', 'thing');
> INSERT 0 1
> ALTER TABLE a ADD COLUMN even_more_stuff boolean not null;
> psql:x:17: ERROR:  column "even_more_stuff" contains null values
> ALTER TABLE a ALTER even_more_stuff set default false;
> psql:x:18: ERROR:  current transaction is aborted, commands ignored until
> end of
>        transaction block
> ALTER TABLE a DROP COLUMN even_more_stuff;
> psql:x:19: ERROR:  current transaction is aborted, commands ignored until
> end of
>        transaction block
> ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false;
> psql:x:20: ERROR:  current transaction is aborted, commands ignored until
> end of
>        transaction block
> rollback;
> ROLLBACK

yup (please respond to the list) -- you can workaround the workaround
by UPDATEing the table to set the field before applying the not null
bit. Note that if you did this, the foreign table containing the type
would have the new column all as null.

IMO, the server is being too strict on the dependency check. Perhaps
there are some defenses here that are an early form of trying to get
field constraints to pass through to the foreign column, or it's just
a plain old bug. I took a quick look at tablecmds.c to see if I could
find an easy fix, but it wasn't clear why the default was forcing an
dependency error and I punted.

merlin