Thread

1. plpgsql function error after alter table add

   Tim Dunnington <timbert@timshouse.com> — 2000-12-05T16:31:51Z

   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
   
   
   
   

2. Re: plpgsql function error after alter table add

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-12T21:14:21Z

   "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
   
   

3. Re: plpgsql function error after alter table add

   Hiroshi Inoue <inoue@tpf.co.jp> — 2000-12-13T00:21:06Z

   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