Обсуждение: BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4

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();

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

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