Re: plpgsql function error after alter table add

"Tim Dunnington" <timbert(at)timshouse(dot)com> writes:
> If you create a function with a table as the argument, and later alter that
> table and add a new column, the function fails saying "incorrect number of
> attributes for table _tablename_" Dropping and readding the function does
> not fix this problem.

I think this is not so much a plpgsql bug as a side effect of the rather
shoddy implementation of ALTER TABLE ADD COLUMN. It doesn't go through
and alter any actual tuples in the table, it just adds the column to the
schema. This works safely only because the heap-tuple access routines
will generally return a NULL without complaint when asked to access a
column number that's beyond the last column actually present in a tuple.
So it *looks* like your new column has NULLs everywhere, when in fact
no NULL is actually stored. This breaks down, however, as soon as anyone
inquires into the number of attributes actually present in any tuple.

If you'd like to live dangerously you could try removing the error check
at line 2685 of pl_exec.c (in current sources; not sure about line
number in 7.0.*, but look for the quoted error message). I'm not
inclined to do that as an official patch however. Someday we're going
to bite the bullet and rewrite ALTER TABLE ADD COLUMN anyway.

A cruder workaround is to do "UPDATE table SET col = NULL" after adding
a new column with ALTER TABLE, so that the "virtual" nulls become real.
If you've already added a few real entries, you can still do it safely
with "UPDATE table SET col = NULL WHERE col IS NULL". (Man, that's
a bizarre-looking command...)

regards, tom lane