BUG #15766: discard plans bug. database CRASH

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15766: discard plans bug. database CRASH

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15766
Logged by:          Zhou Digoal
Email address:      [hidden email]
PostgreSQL version: 11.1
Operating system:   CENTOS 7.X X64
Description:        

discard plans bug. database CRASH

for exp:

```
postgres=# create table tc (id int ,c1 int, c2 int);
CREATE TABLE
postgres=# insert into tc values (1,1,1);
INSERT 0 1
postgres=# prepare x (int) as select * from tc where  id=$1;
PREPARE
postgres=# execute x(1);
 id | c1 | c2
----+----+----
  1 |  1 |  1
(1 row)

postgres=# alter table tc add column c3 int;
ALTER TABLE
postgres=# execute x(1);
ERROR:  0A000: cached plan must not change result type
LOCATION:  RevalidateCachedQuery, plancache.c:723
postgres=# \h discard
Command:     DISCARD
Description: discard session state
Syntax:
DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }

postgres=# discard plans;
DISCARD PLANS
```


and then reexecute , database will crash.

```
postgres=# execute x(1);
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q
```

best regards,
digoal

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15766: discard plans bug. database CRASH

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> PostgreSQL version: 11.1

> discard plans bug. database CRASH

This seems to be fixed already; I just get additional reports of

ERROR:  cached plan must not change result type

in all branches.  I didn't go through the git logs to try to
identify the relevant fix, so possibly it was post-11.2.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

回复:BUG #15766: discard plans bug. database CRASH

周正中(德歌)
Thank you , it's likely a bug caused by pg_hint_plan extension , it's not a pg's bug. 
i comment the pg_hint_plan , and will not crash now.

```
#shared_preload_libraries = 'oracle_fdw,pg_hint_plan'   # (change requires restart)
restart pg.


postgres=# prepare a(int) as select * from tf where id=$1;
PREPARE
postgres=# \d tf
                            Table "public.tf"
 Column |  Type   | Collation | Nullable |            Default             
--------+---------+-----------+----------+--------------------------------
 id     | integer |           | not null | nextval('tf_id_seq'::regclass)

postgres=# execute a(1);
 id 
----
(0 rows)

postgres=# insert into tf values (1);
INSERT 0 1
postgres=# insert into tf values (2);
INSERT 0 1
postgres=# execute a(1);
 id 
----
  1
(1 row)

postgres=# alter table tf add column c1 int;
ALTER TABLE
postgres=# execute a(1);
ERROR:  cached plan must not change result type
postgres=# \set VERBOSITY verbose
postgres=# execute a(1);
ERROR:  0A000: cached plan must not change result type
LOCATION:  RevalidateCachedQuery, plancache.c:723
postgres=# discard plans;
DISCARD PLANS
postgres=# execute a(1);
ERROR:  0A000: cached plan must not change result type
LOCATION:  RevalidateCachedQuery, plancache.c:723
postgres=# execute a(1);
ERROR:  0A000: cached plan must not change result type
LOCATION:  RevalidateCachedQuery, plancache.c:723
```

best regards, 
digoal.
------------------------------------------------------------------
发件人:Tom Lane <[hidden email]>
发送时间:2019年4月18日(星期四) 01:09
收件人:digoal <[hidden email]>
抄 送:pgsql-bugs <[hidden email]>
主 题:Re: BUG #15766: discard plans bug. database CRASH

PG Bug reporting form <[hidden email]> writes:
> PostgreSQL version: 11.1

> discard plans bug. database CRASH 

This seems to be fixed already; I just get additional reports of

ERROR:  cached plan must not change result type

in all branches.  I didn't go through the git logs to try to
identify the relevant fix, so possibly it was post-11.2.

   regards, tom lane