--Boundary-00=_Bo3EEkLbD+KpdTO
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Folks,

Version: 8.1.3
Platform: SuSE Linux, GCC
Severity: mild data corruption
Reproducability: 100%

Steps to Reproduce:
(sample code attached)
1) Create a table.
2) Create a function which inserts a row into that table.
3) Run the function once.
4) ALTER the table with a new column and SET DEFAULT for that column.
5) Run the function again.
6) Re-load the function (via REPLACE)
7) Insert one more row using the function.
8) The table will have NULL values in the first TWO rows, not the first ONE
row as it should. This is because the DEFAULT value is not being "seen"
by the cached plan of the function. As an example, the attached code
produces:

ltreetest=# select * from bugtest;
id | name | is_true
----+----------------+---------
1 | Before ALTER |
2 | Look, its null |
3 | Now its true. | t

When it should produce:

ltreetest=# select * from bugtest;
id | name | is_true
----+----------------+---------
1 | Before ALTER |
2 | Look, its null | t
3 | Now its true. | t

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

--Boundary-00=_Bo3EEkLbD+KpdTO
Content-Type: text/x-objcsrc;
charset="us-ascii";
name="alter_bug.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="alter_bug.sql"

create table bugtest (
id serial not null primary key,
name text not null unique
);

create function insert_bugtest (
vname text )
returns int as $f$
begin
insert into bugtest ( name ) values ( vname );
return currval('bugtest_id_seq');
end; $f$ language plpgsql security definer;

select insert_bugtest('Before ALTER');

alter table bugtest add is_true boolean;
alter table bugtest alter is_true set default true;

select insert_bugtest('Look, its null');

create or replace function insert_bugtest (
vname text )
returns int as $f$
begin
insert into bugtest ( name ) values ( vname );
return currval('bugtest_id_seq');
end; $f$ language plpgsql security definer;

select insert_bugtest('Now its true.');

select * from bugtest order by id;
--Boundary-00=_Bo3EEkLbD+KpdTO
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

--Boundary-00=_Bo3EEkLbD+KpdTO--

Josh Berkus wrote:

I don't think this is really surprising, because the plan of the insert
query will be saved in the function parsetree. There is no way for the
function to notice that the default has changed with current
infrastructure, until we have the plan-dependency stuff in.

If this really harms you, you could use EXECUTE. Or reconnect after you
change the table, whatever.

create or replace function insert_bugtest (
vname text )
returns int as $f$
begin
execute $e$ insert into bugtest ( name ) values
($e$ || quote_literal(vname) || $e$) $e$;
return currval('bugtest_id_seq');
end; $f$ language plpgsql security definer;


--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend