new field content lost

Started by Gaetano Mendolaover 17 years ago3 messages
#1Gaetano Mendola
mendola@gmail.com

Hi all,
since long time I have implemented a materialized view, today I had to add a
new field and I faced the following (I believe) bug.

The bug can be replicated on a 8.2.7

-- SETUP
create table test (a integer, b integer);
create table test_trigger (a integer);

CREATE OR REPLACE FUNCTION trigger_test()
RETURNS TRIGGER AS'
DECLARE

BEGIN
update test set b = b*10 where a = NEW.a;

RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER trigger_test
AFTER INSERT OR UPDATE ON test_trigger DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trigger_test();

insert into test values (1,1),(2,2),(3,3);
insert into test_trigger values (1),(2),(3);

-- FROM CONNECTION A
update test_trigger set a=1 where a=1;

-- FROM CONNECTION B
alter table test add column c integer;
update test set c = 15;
select * from test;

-- FROM CONNECTION A
update test_trigger set a=2 where a=2;

--FROM CONNECTION B
select * from test;

you can see that the value c=15 for a=2 has been nullified

Regards
Gaetano

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gaetano Mendola (#1)
Re: new field content lost

Gaetano Mendola <mendola@gmail.com> writes:

since long time I have implemented a materialized view, today I had to add a
new field and I faced the following (I believe) bug.
The bug can be replicated on a 8.2.7

Cached plan for the function's UPDATE. Should work okay in 8.3 ...

regards, tom lane

#3Gaetano Mendola
mendola@gmail.com
In reply to: Tom Lane (#2)
Re: new field content lost

Tom Lane wrote:

Gaetano Mendola <mendola@gmail.com> writes:

since long time I have implemented a materialized view, today I had to add a
new field and I faced the following (I believe) bug.
The bug can be replicated on a 8.2.7

Cached plan for the function's UPDATE. Should work okay in 8.3 ...

It does.

Regards
Gaetano Mendola