BUG #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4

"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:

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:

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
 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.

If we know the types of everything, is it possible to make both cases work?

--
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:

 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.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company