Thread: PREPARE(d) statements and modifying columns

There are problems when a prepared statement's underlying table's column's
type is modified.  With 7.5 and using the new ALTER TYPE it causes a
backend crash.  Using the old ADD/UPDATE/DROP/RENAME method causes
incorrect data to be returned.

CREATE TABLE mytab (a int);
INSERT INTO mytab VALUES (1);
PREPARE mystat AS SELECT a FROM mytab;

and then:

ALTER TABLE mytab ALTER COLUMN a TYPE bigint USING (a+1);
EXECUTE mystat;

or:

ALTER TABLE mytab ADD COLUMN b bigint;
UPDATE mytab SET b = a+1;
ALTER TABLE mytab DROP COLUMN a;
ALTER TABLE mytab RENAME COLUMN b TO a;
EXECUTE mystat;


Kris Jurka

I'd like to re-raise this bug I reported some time ago and got no response
on.  Generally any plain sql commands shouldn't crash the backend and I
don't like a query reporting incorrect results.

On Sun, 27 Jun 2004, Kris Jurka wrote:

> There are problems when a prepared statement's underlying table's column's
> type is modified.  With 7.5 and using the new ALTER TYPE it causes a
> backend crash.  Using the old ADD/UPDATE/DROP/RENAME method causes
> incorrect data to be returned.
>
> CREATE TABLE mytab (a int);
> INSERT INTO mytab VALUES (1);
> PREPARE mystat AS SELECT a FROM mytab;
>
> and then:
>
> ALTER TABLE mytab ALTER COLUMN a TYPE bigint USING (a+1);
> EXECUTE mystat;
>
> or:
>
> ALTER TABLE mytab ADD COLUMN b bigint;
> UPDATE mytab SET b = a+1;
> ALTER TABLE mytab DROP COLUMN a;
> ALTER TABLE mytab RENAME COLUMN b TO a;
> EXECUTE mystat;
>
> Kris Jurka
>