Обсуждение: plpgsql function error after alter table add

NOTE:  Please retract similar, earlier entry (hit send by mistake :-)


Synopsis:

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.


Error:

ERROR:  query didn't return correct # of attributes for $1


Reproduce:

Of course you'll need to add plpgsql as a valid language on your database in
order for this to work:

   CREATE TABLE car_make(
       make     text primary key);
   CREATE TABLE car(
       vin      text primary key,
       make     text references car_make(make));
   INSERT INTO car_make VALUES('Toyota');
   INSERT INTO car VALUES('mytoyota','Toyota');
   CREATE FUNCTION carname(car) RETURNS text AS '
       declare
           tmp alias for $1;
       begin
           return tmp.vin || ''_'' || tmp.make;
       end;
   ' LANGUAGE 'plpgsql';
   SELECT carname(car) FROM car;
   ALTER TABLE car ADD purchase_date timestamp;
   SELECT carname(car) FROM car;
   DROP FUNCTION carname(car);
   CREATE FUNCTION carname(car) RETURNS text AS '
       declare
           tmp alias for $1;
       begin
           return tmp.vin || ''_'' || tmp.make;
       end;
   ' LANGUAGE 'plpgsql';
   SELECT carname(car) FROM car;
   /* above causes error */


Results:

   CREATE FUNCTION carname(car) RETURNS text AS '
junk'#        declare
junk'#            tmp alias for $1;
junk'#        begin
junk'#            return tmp.vin || ''_'' || tmp.make;
junk'#        end;
junk'#    ' LANGUAGE 'plpgsql';
       end;
   ' LANGUAGE 'plpgsql';
CREATE
junk=#    SELECT carname(car) FROM car;
     carname
-----------------
 mytoyota_Toyota
(1 row)

junk=#    ALTER TABLE car ADD purchase_date timestamp;
   SELECT carname(car) FROM car;
ALTER
junk=#    SELECT carname(car) FROM car;
     carname
-----------------
 mytoyota_Toyota
(1 row)

junk-#    DROP FUNCTION carname(car);
DROP
junk=#    CREATE FUNCTION carname(car) RETURNS text AS '
junk'#        declare
junk'#            tmp alias for $1;
junk'#        begin
junk'#            return tmp.vin || ''_'' || tmp.make;
junk'#        end;
junk'#    ' LANGUAGE 'plpgsql';
CREATE
junk=#    SELECT carname(car) FROM car;
ERROR:  query didn't return correct # of attributes for $1
junk=#    /* above causes error */


--
Tim Dunnington
Sr. Integration Engineer
Healthcare.com

"Tim Dunnington" <timbert@timshouse.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

Tom Lane wrote:
>
> "Tim Dunnington" <timbert@timshouse.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.

Seems a plpgsql's simple bug.

> It doesn't go through
> and alter any actual tuples in the table, it just adds the column to the
> schema.

It seems a nice implementation.

Regards.
Hiroshi Inoue