Thread: [7.4.2] Still "variable not found in subplan target lists"

Hello all..

I have a 7.4.2 pg server and the situation is the following:

Table detorders with 69 columns, one of which is called docomment. There is=
 a view based on this table called qrydorders and there are other 5 views w=
hich include qrydorders in there definitions. Today after changing (with pg=
admin III) the datatype of qrydorders.docomment from varchar(255) to varcha=
r(2500) i got the error "variable not found in subplan target lists" when a=
 SELECT * from qrydorders WHERE did=3D101015 . Everything got back working =
after i dropped qrydorders and all of the views in cascade and then recreat=
ed them.

I saw this was a problem in the 7.4.1. Before installing 7.4.2 and recreati=
ng the database i dropped the old DB, removed the directory which contained=
 the PG files, created a new one and recreated everything with initdb so i =
do not think its a mistake that i made when i installed the new PG.

Regards,

Fabrizio Mazzoni

"veramente@libero.it" <veramente@libero.it> writes:
> Today after changing (with pgadmin III) the datatype of
> qrydorders.docomment from varchar(255) to varchar(2500) i got the
> error "variable not found in subplan target lists" [ from a view
> dependent on the table ]

This is an unsupported operation.  You should perhaps complain to the
pgadmin guys that they are not correctly updating the system catalogs.

            regards, tom lane

Tom Lane wrote:

>"veramente@libero.it" <veramente@libero.it> writes:
>
>
>>Today after changing (with pgadmin III) the datatype of
>>qrydorders.docomment from varchar(255) to varchar(2500) i got the
>>error "variable not found in subplan target lists" [ from a view
>>dependent on the table ]
>>
>>
>
>This is an unsupported operation.  You should perhaps complain to the
>pgadmin guys that they are not correctly updating the system catalogs.
>
>

UPDATE pg_attribute
   SET atttypmod=2504
 WHERE attrelid=25574::oid AND attnum=2;

This is what pgAdmin3 will generate to change a varchar to 2500 bytes.
Please let me know what's wrong with that.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> This is an unsupported operation.  You should perhaps complain to the
>> pgadmin guys that they are not correctly updating the system catalogs.

> UPDATE pg_attribute
>    SET atttypmod=2504
>  WHERE attrelid=25574::oid AND attnum=2;

> This is what pgAdmin3 will generate to change a varchar to 2500 bytes.
> Please let me know what's wrong with that.

It doesn't fix views that contain references to the column.  The new
typmod would need to be propagated into the view's rule parsetree, and
perhaps to the type of the view's result column if the view directly
exposes the changed column (whereupon you need to recursively look at
the views that depend on this one, etc).

What you could probably do is find the referencing views via pg_depend.
For each one, try to do CREATE OR REPLACE VIEW using the view definition
string from pg_get_viewdef.  If it succeeds you're done (the variable
must not be propagated to any output column).  If it fails, adjust the
indicated output column's typmod.  Lather, rinse, repeat in case there
is more than one dependent output column.  Recurse once you've
successfully altered the view.

It'd probably also be a smart idea to error out if pg_depend shows any
dependencies on the column from objects that you don't know what to do
with (aren't views).

I recall there was some discussion of this stuff on pgsql-hackers the
last time it was proposed to support "ALTER COLUMN type".  We may have
thought of some additional considerations besides views.  I'd suggest
trawling the list archives to see...

            regards, tom lane

Tom Lane wrote:

>It doesn't fix views that contain references to the column.  The new
>typmod would need to be propagated into the view's rule parsetree, and
>perhaps to the type of the view's result column if the view directly
>exposes the changed column (whereupon you need to recursively look at
>the views that depend on this one, etc).
>
>What you could probably do is find the referencing views via pg_depend.
>For each one, try to do CREATE OR REPLACE VIEW using the view definition
>string from pg_get_viewdef.  If it succeeds you're done (the variable
>must not be propagated to any output column).  If it fails, adjust the
>indicated output column's typmod.  Lather, rinse, repeat in case there
>is more than one dependent output column.  Recurse once you've
>successfully altered the view.
>
>It'd probably also be a smart idea to error out if pg_depend shows any
>dependencies on the column from objects that you don't know what to do
>with (aren't views).
>
>I recall there was some discussion of this stuff on pgsql-hackers the
>last time it was proposed to support "ALTER COLUMN type".  We may have
>thought of some additional considerations besides views.  I'd suggest
>trawling the list archives to see...
>
>
>
There was discussion about altering type, mostly about changing beween
binary incompatible types (e.g. int4->numeric) requiring adding/dropping
columns and deep recreation of dependent objects.
There was a thread stating that a limited class of changes exist that
can be done without deep impact, namely changing between binary
compatible types and extending the length. This is what pgadmin3 does,
but apparently this wasn't correct.


Regards,
Andreas