From: | konst583(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | konst583(at)gmail(dot)com |
Subject: | BUG #14879: Bug connected with table structure modification and trigger function query plan invalidation |
Date: | 2017-10-30 12:53:45 |
Message-ID: | 20171030125345.1448.24038@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14879
Logged by: Konstantin Evteev
Email address: konst583(at)gmail(dot)com
PostgreSQL version: 9.4.12
Operating system: Debian GNU/Linux 8 (jessie)
Description:
There is a bug connected with table structure modification and trigger
function query plan invalidation:
1) 1st session - create table:
create table test(test_id int, new_test_id bigint)
2) 1st session - create func and trigger after insert or update
CREATE OR REPLACE FUNCTION test_trg_func()
RETURNS trigger AS
$BODY$
begin
NEW.new_test_id = NEW.test_id;
return NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER test_trg
BEFORE INSERT OR UPDATE
ON test
FOR EACH ROW
EXECUTE PROCEDURE test_trg_func();
3) 1st session
insert into test (test_id) values (5)
4) 2nd session(another session) alter table :
alter table test rename column test_id to old_test_id;
alter table test rename column new_test_id to test_id;
alter table test rename column old_test_id to new_test_id;
5) 1st session:
insert into test (test_id) values (5)
ERROR: type of parameter 15 (bigint) does not match that when preparing the
plan (integer)
CONTEXT: PL/pgSQL function test_trg_func() line 5 at assignment
********** Error **********
ERROR: type of parameter 15 (bigint) does not match that when preparing the
plan (integer)
SQL state: 42804
Context: PL/pgSQL function test_trg_func() line 5 at assignment
-----------------------------------------
Workaround - recreate trg_func to invalidate plan
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2017-10-30 18:08:21 | Re: pg_trgm word_similarity inconsistencies or bug |
Previous Message | Henri KY | 2017-10-30 11:14:16 | Re: BUG #14874: Dublicate values in primary key |