Postgres Pro
Downloads
Home   >   mailing lists

Thread: prepare-alter-exec issue

prepare-alter-exec issue

From
"Qingqing Zhou"
Date:
I encounter a server(8.1.1) problem like this:

    create table tt(id int);
    prepare p1(int) as insert into tt values($1);
    execute p1(3);
    alter table tt alter id type char(10);
    execute p1(9999999);
    select * from tt;
    ^ server core dumps here

Command "execute p1(9999999)" works because the prepared plan still treat
9999999 as an integer, but "select * from tt" causes core dump because it
treats the attribute as type varlena char - so 9999999 becomes the varlen.

This might be a known issue, but seems not mentioned in the document:
http://www.postgresql.org/docs/current/static/sql-prepare.html


Regards,
Qingqing

Re: prepare-alter-exec issue

From
Dhanaraj
Date:
The same bug was filed already in the end of Nov.

There are two ways to fix this.

1) After altering the table, prepare buffer should be cleared
or
2) The prepare stmt should be updated whenever alter query is executed.

I hope that this bug will be fixed in the next version..

Regards
Dhanaraj
Qingqing Zhou wrote:
> I encounter a server(8.1.1) problem like this:
>
>     create table tt(id int);
>     prepare p1(int) as insert into tt values($1);
>     execute p1(3);
>     alter table tt alter id type char(10);
>     execute p1(9999999);
>     select * from tt;
>     ^ server core dumps here
>
> Command "execute p1(9999999)" works because the prepared plan still treat
> 9999999 as an integer, but "select * from tt" causes core dump because it
> treats the attribute as type varlena char - so 9999999 becomes the varlen.
>
> This might be a known issue, but seems not mentioned in the document:
> http://www.postgresql.org/docs/current/static/sql-prepare.html
>
>
> Regards,
> Qingqing
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Re: prepare-alter-exec issue

From
Bruce Momjian
Date:
Added to TODO:

    * Invalidate prepared queries, like INSERT, when the table definition
      is altered


---------------------------------------------------------------------------

Dhanaraj wrote:
> The same bug was filed already in the end of Nov.
>
> There are two ways to fix this.
>
> 1) After altering the table, prepare buffer should be cleared
> or
> 2) The prepare stmt should be updated whenever alter query is executed.
>
> I hope that this bug will be fixed in the next version..
>
> Regards
> Dhanaraj
> Qingqing Zhou wrote:
> > I encounter a server(8.1.1) problem like this:
> >
> >     create table tt(id int);
> >     prepare p1(int) as insert into tt values($1);
> >     execute p1(3);
> >     alter table tt alter id type char(10);
> >     execute p1(9999999);
> >     select * from tt;
> >     ^ server core dumps here
> >
> > Command "execute p1(9999999)" works because the prepared plan still treat
> > 9999999 as an integer, but "select * from tt" causes core dump because it
> > treats the attribute as type varlena char - so 9999999 becomes the varlen.
> >
> > This might be a known issue, but seems not mentioned in the document:
> > http://www.postgresql.org/docs/current/static/sql-prepare.html
> >
> >
> > Regards,
> > Qingqing
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +