Обсуждение: BUG #4673: pl/PgSQL: Bug, when updating changed composite types.
The following bug has been logged online:
Bug reference: 4673
Logged by: Oleg
Email address: serovOv@gmail.com
PostgreSQL version: 8.3.6
Operating system: i686-redhat-linux-gnu
Description: pl/PgSQL: Bug, when updating changed composite types.
Details:
If you added a field into composite type(table type), plpgsql won't update
it, until you recompile function.
DEMO SQL:
ROLLBACK;
BEGIN;
CREATE TABLE bug_composite (
column_a INT,
column_b INT
);
CREATE TABLE bug_parent (
id INT,
info bug_composite
);
INSERT INTO bug_parent(id, info) VALUES (1, ROW(1, 2));
CREATE OR REPLACE FUNCTION bug_update(r bug_parent) RETURNS void
AS
$BODY$
BEGIN
UPDATE bug_parent
SET info = r.info
WHERE id = r.id;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
SELECT bug_update(ROW(1, ROW(3, 5)));
SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5));
ALTER TABLE bug_composite ADD COLUMN column_c INT;
SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5, NULL));
SELECT bug_update(ROW(1, ROW(4, 6, 9)));
SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(4, 6, NULL));
SELECT version(); -- "PostgreSQL 8.3.6 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)"
Hello!? anybody will fix this bug? Hey! On Mon, Feb 23, 2009 at 5:11 PM, Oleg <serovOv@gmail.com> wrote: > > The following bug has been logged online: > > Bug reference: 4673 > Logged by: Oleg > Email address: serovOv@gmail.com > PostgreSQL version: 8.3.6 > Operating system: i686-redhat-linux-gnu > Description: pl/PgSQL: Bug, when updating changed composite types. > Details: > > If you added a field into composite type(table type), plpgsql won't update > it, until you recompile function. > > DEMO SQL: > > ROLLBACK; > BEGIN; > CREATE TABLE bug_composite ( > column_a INT, > column_b INT > ); > > CREATE TABLE bug_parent ( > id INT, > info bug_composite > ); > > INSERT INTO bug_parent(id, info) VALUES (1, ROW(1, 2)); > > CREATE OR REPLACE FUNCTION bug_update(r bug_parent) RETURNS void > AS > $BODY$ > BEGIN > UPDATE bug_parent > SET info =3D r.info > WHERE id =3D r.id; > END; > $BODY$ LANGUAGE 'plpgsql' VOLATILE; > > SELECT bug_update(ROW(1, ROW(3, 5))); > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5)); > > ALTER TABLE bug_composite ADD COLUMN column_c INT; > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5, NULL)); > > SELECT bug_update(ROW(1, ROW(4, 6, 9))); > > SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(4, 6, NULL)); > > SELECT version(); -- "PostgreSQL 8.3.6 on i686-redhat-linux-gnu, compiled > by > GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)" > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD =EF=CC=C5=C7 =F3=C5=D2=CF=D7
Just to save anyone else reading through these the time it took for me to figure this out, this was resubmitted as bug #5353, and Tom replied to that one. ...Robert 2010/2/26 Oleg Serov <serovov@gmail.com>: > Hello!? anybody will fix this bug? Hey! > > On Mon, Feb 23, 2009 at 5:11 PM, Oleg <serovOv@gmail.com> wrote: >> >> The following bug has been logged online: >> >> Bug reference: =9A =9A =9A4673 >> Logged by: =9A =9A =9A =9A =9AOleg >> Email address: =9A =9A =9AserovOv@gmail.com >> PostgreSQL version: 8.3.6 >> Operating system: =9A i686-redhat-linux-gnu >> Description: =9A =9A =9A =9Apl/PgSQL: Bug, when updating changed composi= te types. >> Details: >> >> If you added a field into composite type(table type), plpgsql won't upda= te >> it, until you recompile function. >> >> DEMO SQL: >> >> ROLLBACK; >> BEGIN; >> CREATE TABLE bug_composite ( >> =9A =9A =9A =9Acolumn_a INT, >> =9A =9A =9A =9Acolumn_b INT >> ); >> >> CREATE TABLE bug_parent ( >> =9A =9A =9A =9Aid INT, >> =9A =9A =9A =9Ainfo bug_composite >> ); >> >> INSERT INTO bug_parent(id, info) VALUES (1, ROW(1, 2)); >> >> CREATE OR REPLACE FUNCTION bug_update(r bug_parent) RETURNS void >> AS >> $BODY$ >> BEGIN >> =9A =9A =9A =9AUPDATE bug_parent >> =9A =9A =9A =9A =9A =9A =9A =9ASET info =3D r.info >> =9A =9A =9A =9A =9A =9A =9A =9AWHERE id =3D r.id; >> END; >> $BODY$ LANGUAGE 'plpgsql' VOLATILE; >> >> SELECT bug_update(ROW(1, ROW(3, 5))); >> >> SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5)); >> >> ALTER TABLE bug_composite ADD COLUMN column_c INT; >> >> SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(3, 5, NULL)); >> >> SELECT bug_update(ROW(1, ROW(4, 6, 9))); >> >> SELECT * FROM bug_parent; -- WE WILL HAVE ROW(1, ROW(4, 6, NULL)); >> >> SELECT version(); -- "PostgreSQL 8.3.6 on i686-redhat-linux-gnu, compiled >> by >> GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)" >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs > > > > -- > =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD > > =EF=CC=C5=C7 =F3=C5=D2=CF=D7 >