Обсуждение: issues with dropped columns in plpgsql code again
Hello
One user reported a issues with triggers related to dropped columns.$$
LOCATION: exec_stmt_raise, pl_exec.c:2985
ERROR: 22P02: invalid input syntax for type numeric: "aaa"
CONTEXT: PL/pgSQL function f1_trg() line 4 at assignment
LOCATION: set_var_from_str, numeric.c:3253
Pavel
sorry
my testcreate table f1(a int, b int, c varchar, dropped_column numeric, d varchar);
create or replace function f1_trg()
returns trigger as $$
declare _f1_var f1;
begin raise notice 'run trigger';
_f1_var := new;
return _f1_var;
end;
$$ language plpgsql;
create trigger xx before insert on f1 for row execute procedure f1_trg();
insert into f1 values(1,1,'aaa',1.1,'aaa');
alter table f1 drop column dropped_column ;
insert into f1 values(1,1,'aaa','aaa');
2013/5/7 Pavel Stehule <pavel.stehule@gmail.com>
RegardsWhen I dropped column (I dropped numeric column) I had to finish session, because I got a error in assign$$END;RETURN somevar;// do some with somevar;somevar := NEW;BEGINDECLARE somevar targettable;He use a trigger in formWhen I checked his code, I found a more possible problems.HelloOne user reported a issues with triggers related to dropped columns.
$$
LOCATION: exec_stmt_raise, pl_exec.c:2985
ERROR: 22P02: invalid input syntax for type numeric: "aaa"
CONTEXT: PL/pgSQL function f1_trg() line 4 at assignment
LOCATION: set_var_from_str, numeric.c:3253
Pavel
On 7 May 2013 21:23, Pavel Stehule <pavel.stehule@gmail.com> wrote:
sorrymy test
create table f1(a int, b int, c varchar, dropped_column numeric, d varchar);
create or replace function f1_trg()
returns trigger as $$
declare _f1_var f1;
begin raise notice 'run trigger';
_f1_var := new;
return _f1_var;
end;
$$ language plpgsql;
create trigger xx before insert on f1 for row execute procedure f1_trg();
insert into f1 values(1,1,'aaa',1.1,'aaa');
alter table f1 drop column dropped_column ;
insert into f1 values(1,1,'aaa','aaa');
Fails for me as well. I managed to run the last query either with restarting session, or disabling the trigger.
Checked that on PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1) 4.7.2, 64-bit
regards,
Szymon
Pavel Stehule <pavel.stehule@gmail.com> writes:
> One user reported a issues with triggers related to dropped columns.
> When I checked his code, I found a more possible problems.
This is just the same kind of modified-rowtype problem we've discussed
before.
I continue to maintain that the only realistic fix is to stop using the
PLPGSQL_DTYPE_ROW infrastructure for variables of externally-defined
composite types. In the last go-round, this was rejected on the
(unproven) assumption that it would make things slower. But as a wise
man once said, you can make code arbitrarily fast if it doesn't have to
give the right answer.
regards, tom lane
<p dir="ltr"><br /> Dne 10.5.2013 22:17 "Tom Lane" <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> napsal(a):<br/> ><br /> > Pavel Stehule <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>>writes:<br /> > > One user reported a issues withtriggers related to dropped columns.<br /> > > When I checked his code, I found a more possible problems.<br />><br /> > This is just the same kind of modified-rowtype problem we've discussed<br /> > before.<p dir="ltr">Yes.It is same problém. <p dir="ltr">><br /> > I continue to maintain that the only realistic fix is to stopusing the<br /> > PLPGSQL_DTYPE_ROW infrastructure for variables of externally-defined<br /> > composite types. In the last go-round, this was rejected on the<br /> > (unproven) assumption that it would make things slower. But as a wise<br /> > man once said, you can make code arbitrarily fast if it doesn't have to<br /> > givethe right answer.<p dir="ltr">Yes. It is probably solutions. <p dir="ltr">Regards<p dir="ltr">Pavel<br /> ><br />> regards, tom lane<br />