Thread: BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
From
"Valentine Gogichashvili"
Date:
The following bug has been logged online: Bug reference: 5644 Logged by: Valentine Gogichashvili Email address: valgog@gmail.com PostgreSQL version: 9.0RC1 Operating system: linux Description: Selecting ROW() in variable with 9.0 not compatible with 8.4 Details: After migration to 9.0RC1 some stored procedures, working ok in 8.4 does not work in 9.0. The problem is that SELECT ROW(a,b,c) INTO var does not work any longer in 9.0. For example: BEGIN; CREATE TYPE ta AS (a1 integer, a2 text); CREATE TYPE tb AS (b1 integer, b2 ta); DO $DOIT$ DECLARE a ta; b tb; BEGIN SELECT 1, 'a' INTO a; -- ok RAISE INFO 'a is %', a; SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0 RAISE INFO 'a is %', a; SELECT 1, 'a' INTO b.b2; -- ok RAISE INFO 'b is %', b; END; $DOIT$; ROLLBACK; With best regards, -- Valentine Gogichashvili
"Valentine Gogichashvili" <valgog@gmail.com> writes: > After migration to 9.0RC1 some stored procedures, working ok in 8.4 does not > work in 9.0. > The problem is that SELECT ROW(a,b,c) INTO var does not work any longer in > 9.0. It didn't work in 8.4 either. I tried the attached and got some variant of ERROR: invalid input syntax for integer: "(1,a)" CONTEXT: PL/pgSQL function "foo" line 9 at SQL statement in every release back to 8.1. regards, tom lane CREATE TYPE ta AS (a1 integer, a2 text); CREATE TYPE tb AS (b1 integer, b2 ta); create or replace function foo() returns void language plpgsql as $$ DECLARE a ta; b tb; BEGIN SELECT 1, 'a' INTO a; -- ok RAISE INFO 'a is %', a; SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0 RAISE INFO 'a is %', a; SELECT 1, 'a' INTO b.b2; -- ok RAISE INFO 'b is %', b; END; $$; select foo();
Re: BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
From
Valentine Gogichashvili
Date:
Sorry, did not check it exactly on the 8.4, now installed 8.4 and reproduced the issue: BEGIN; CREATE TYPE ta AS (a1 integer, a2 text); CREATE TYPE tb AS (b1 integer, b2 ta); CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql AS $DOIT$ DECLARE a ta; b tb; BEGIN RAISE INFO 'Postgres %', version(); SELECT 1, 'a' INTO a; -- ok RAISE INFO 'a is %', a; SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: invalid input syntax for integer: "(10,a)"] RAISE INFO 'b.b2 is %', b.b2; SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: cannot assign non-composite value to a row variable] RAISE INFO 'b is %', b; END $DOIT$; select public.foo(); ROLLBACK; Best ragards, -- Valentine Gogichashvili On Sun, Sep 5, 2010 at 5:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Valentine Gogichashvili" <valgog@gmail.com> writes: > > After migration to 9.0RC1 some stored procedures, working ok in 8.4 does > not > > work in 9.0. > > The problem is that SELECT ROW(a,b,c) INTO var does not work any longer > in > > 9.0. > > It didn't work in 8.4 either. I tried the attached and got some variant > of > > ERROR: invalid input syntax for integer: "(1,a)" > CONTEXT: PL/pgSQL function "foo" line 9 at SQL statement > > in every release back to 8.1. > > regards, tom lane > > > CREATE TYPE ta AS (a1 integer, a2 text); > CREATE TYPE tb AS (b1 integer, b2 ta); > > create or replace function foo() returns void language plpgsql as $$ > DECLARE > a ta; > b tb; > BEGIN > > SELECT 1, 'a' INTO a; -- ok > RAISE INFO 'a is %', a; > > SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0 > RAISE INFO 'a is %', a; > > > SELECT 1, 'a' INTO b.b2; -- ok > RAISE INFO 'b is %', b; > > END; > $$; > > select foo(); >
Valentine Gogichashvili <valgog@gmail.com> writes: > CREATE TYPE ta AS (a1 integer, a2 text); > CREATE TYPE tb AS (b1 integer, b2 ta); > DECLARE > a ta; > b tb; > BEGIN > SELECT 1, 'a' INTO a; -- ok > SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: > invalid input syntax for integer: "(10,a)"] > SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: cannot > assign non-composite value to a row variable] [ pokes around for a bit ... ] This is a consequence of the plpgsql lexer rewrite I did for 9.0. In the previous code, "INTO b.b2" was treated by the lexer as an assignment to a scalar variable, regardless of the actual data type of b2. Which means that the SELECT has to produce a single column that gets assigned to b.b2, so your first case works and your second doesn't. The new code looks at the data type of b2 rather than whether it's syntactically a field reference, so it decides this is an assignment to a composite variable. That results in behavior similar to the "INTO a" case: the SELECT is supposed to produce one column for each field of the composite variable. Hence, second case works and first doesn't. I am not sure how ugly a kluge would be needed to restore the previous behavior. I'm inclined to say that the new behavior is more self-consistent and so we should call this a bug fix rather than a bug. regards, tom lane
Re: BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
From
Valentine Gogichashvili
Date:
I also suppose, that the new implementation is more consistent, but probably a hint in the compatibility list should be done, so that after migration Stored Procedures that are using the buggy implementation on the 8.4 should be rewritten. And the rewriting should be done either only after the migration, or in 8.4 to use the rec.var := ( SELECT ROW(..) .. ); syntax. With my best regards, -- Valentine Gogichashvili On Wed, Sep 8, 2010 at 5:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Valentine Gogichashvili <valgog@gmail.com> writes: > > CREATE TYPE ta AS (a1 integer, a2 text); > > CREATE TYPE tb AS (b1 integer, b2 ta); > > > DECLARE > > a ta; > > b tb; > > BEGIN > > > SELECT 1, 'a' INTO a; -- ok > > > SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: > > invalid input syntax for integer: "(10,a)"] > > > SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: > cannot > > assign non-composite value to a row variable] > > [ pokes around for a bit ... ] This is a consequence of the plpgsql > lexer rewrite I did for 9.0. In the previous code, "INTO b.b2" was > treated by the lexer as an assignment to a scalar variable, regardless > of the actual data type of b2. Which means that the SELECT has to > produce a single column that gets assigned to b.b2, so your first case > works and your second doesn't. The new code looks at the data type > of b2 rather than whether it's syntactically a field reference, so it > decides this is an assignment to a composite variable. That results in > behavior similar to the "INTO a" case: the SELECT is supposed to produce > one column for each field of the composite variable. Hence, second case > works and first doesn't. > > I am not sure how ugly a kluge would be needed to restore the previous > behavior. I'm inclined to say that the new behavior is more > self-consistent and so we should call this a bug fix rather than a bug. > > regards, tom lane >
On Wed, Sep 8, 2010 at 11:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Valentine Gogichashvili <valgog@gmail.com> writes: >> CREATE TYPE ta AS (a1 integer, a2 text); >> CREATE TYPE tb AS (b1 integer, b2 ta); > >> DECLARE >> =A0a ta; >> =A0b tb; >> BEGIN > >> =A0SELECT 1, 'a' INTO a; =A0 =A0 =A0-- ok > >> =A0SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: >> =A0invalid input syntax for integer: "(10,a)"] > >> =A0SELECT 100, 'a' INTO b.b2; =A0 -- ok in 9.0 but fails in 8.4 [ERROR: = =A0cannot >> assign non-composite value to a row variable] > > [ pokes around for a bit ... ] =A0This is a consequence of the plpgsql > lexer rewrite I did for 9.0. =A0In the previous code, "INTO b.b2" was > treated by the lexer as an assignment to a scalar variable, regardless > of the actual data type of b2. =A0Which means that the SELECT has to > produce a single column that gets assigned to b.b2, so your first case > works and your second doesn't. =A0The new code looks at the data type > of b2 rather than whether it's syntactically a field reference, so it > decides this is an assignment to a composite variable. =A0That results in > behavior similar to the "INTO a" case: the SELECT is supposed to produce > one column for each field of the composite variable. =A0Hence, second case > works and first doesn't. > > I am not sure how ugly a kluge would be needed to restore the previous > behavior. =A0I'm inclined to say that the new behavior is more > self-consistent and so we should call this a bug fix rather than a bug. If we know the types of everything, is it possible to make both cases work? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes: > On Wed, Sep 8, 2010 at 11:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: >>> invalid input syntax for integer: "(10,a)"] >>> SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: cannot assign non-composite value to a row variable] > If we know the types of everything, is it possible to make both cases work? We don't know the types of everything at the point where the decision needs to be made. Even if we did, allowing both would be a klugy unmaintainable mess IMO --- far more work than it's worth. regards, tom lane
On Thu, Sep 16, 2010 at 9:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Wed, Sep 8, 2010 at 11:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> =A0SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: >>>> =A0invalid input syntax for integer: "(10,a)"] > >>>> =A0SELECT 100, 'a' INTO b.b2; =A0 -- ok in 9.0 but fails in 8.4 [ERROR= : =A0cannot assign non-composite value to a row variable] > >> If we know the types of everything, is it possible to make both cases wo= rk? > > We don't know the types of everything at the point where the decision > needs to be made. =A0Even if we did, allowing both would be a klugy > unmaintainable mess IMO --- far more work than it's worth. Bummer. Maybe we should have more-different syntax for the two cases then. I've been bitten by this quite a few times over the years. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company