Thread: BUG #15766: discard plans bug. database CRASH
The following bug has been logged on the website:
Bug reference: 15766
Logged by: Zhou Digoal
Email address: digoal@126.com
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
PG Bug reporting form <noreply@postgresql.org> 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
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
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 <tgl@sss.pgh.pa.us>发送时间：2019年4月18日(星期四) 01:09收件人：digoal <digoal@126.com>抄 送：pgsql-bugs <pgsql-bugs@lists.postgresql.org>主 题：Re: BUG #15766: discard plans bug. database CRASHPG Bug reporting form <noreply@postgresql.org> 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