Thread: BUG #6379: SQL Function Causes Back-end Crash

BUG #6379: SQL Function Causes Back-end Crash

From

pramsey@cleverelephant.ca

Date:

04 January 2012, 18:17:36

The following bug has been logged on the website:

Bug reference:      6379
Logged by:          Paul Ramsey
Email address:      pramsey@cleverelephant.ca
PostgreSQL version: 9.1.2
Operating system:   OSX 10.6.8
Description:=20=20=20=20=20=20=20=20

CREATE OR REPLACE FUNCTION kill_backend()
RETURNS VOID
AS $$
  DROP TABLE if EXISTS foo;
  CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
$$ LANGUAGE 'SQL';

Re: BUG #6379: SQL Function Causes Back-end Crash

From

hubert depesz lubaczewski

Date:

04 January 2012, 18:25:40

On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey@cleverelephant.ca wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6379
> Logged by:          Paul Ramsey
> Email address:      pramsey@cleverelephant.ca
> PostgreSQL version: 9.1.2
> Operating system:   OSX 10.6.8
> Description:
>
> CREATE OR REPLACE FUNCTION kill_backend()
> RETURNS VOID
> AS $$
>   DROP TABLE if EXISTS foo;
>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
> $$ LANGUAGE 'SQL';

Cannot replicate:

(depesz@localhost:5910) 20:23:43 [depesz]
$ CREATE OR REPLACE FUNCTION kill_backend()
>> RETURNS VOID
>> AS $$
>>   DROP TABLE if EXISTS foo;
>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>> $$ LANGUAGE 'SQL';
CREATE FUNCTION
(depesz@localhost:5910) 20:23:49 [depesz]
$ select kill_backend();
NOTICE:  table "foo" does not exist, skipping
CONTEXT:  SQL function "kill_backend" statement 1
 kill_backend
--------------
 [null]
(1 row)

(depesz@localhost:5910) 20:23:55 [depesz]
$ select kill_backend();
 kill_backend
--------------
 [null]
(1 row)

(depesz@localhost:5910) 20:23:56 [depesz]
$ select kill_backend();
 kill_backend
--------------
 [null]
(1 row)

(depesz@localhost:5910) 20:23:58 [depesz]
$ select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real (Debian 4.6.2-5) 4.6.2, 64-bit
(1 row)

Side note - definition as is, doesn't work on 9.2:
$ CREATE OR REPLACE FUNCTION kill_backend()
                                                                         
>> RETURNS VOID
                                                                          
>> AS $$
                                                                          
>>   DROP TABLE if EXISTS foo;
                                                                          
>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
                                                                          
>> $$ LANGUAGE 'SQL';
                                                                          
ERROR:  language "SQL" does not exist

changing it to proper sql (not uppercase) fixed this problem.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Pavel Stehule

Date:

04 January 2012, 18:32:53

Hello

I can replicate it

postgres=3D# select kill_backend();
NOTICE:  table "foo" does not exist, skipping
CONTEXT:  SQL function "kill_backend" statement 1
The connection to the server was lost. Attempting reset: Failed.
!>

bash-4.2$ uname -a
Linux nemesis 2.6.41.4-1.fc15.x86_64 #1 SMP Tue Nov 29 11:53:48 UTC
2011 x86_64 x86_64 x86_64 GNU/Linux

Program terminated with signal 11, Segmentation fault.
#0  0x00000000005aaacd in postquel_end (es=3D0x2c77298) at functions.c:637
637        (*es->qd->dest->rDestroy) (es->qd->dest);

Missing separate debuginfos, use: debuginfo-install glibc-2.14-5.x86_64
(gdb) bt
#0  0x00000000005aaacd in postquel_end (es=3D0x2c77298) at functions.c:637
#1  0x00000000005abb3e in fmgr_sql (fcinfo=3D<optimized out>) at functions.=
c:902
#2  0x00000000005a44aa in ExecMakeFunctionResult (fcache=3D0x2c6acd0,
econtext=3D0x2c6aaa8, isNull=3D0x2c6b680 "", isDone=3D0x2c6b7c0)
    at execQual.c:1832
#3  0x00000000005a79e2 in ExecTargetList (isDone=3D0x7fff58a0fedc,
itemIsDone=3D0x2c6b7c0, isnull=3D0x2c6b680 "", values=3D0x2c6b660,
    econtext=3D0x2c6aaa8, targetlist=3D0x2c6b788) at execQual.c:5112
#4  ExecProject (projInfo=3D<optimized out>, isDone=3D0x7fff58a0fedc) at
execQual.c:5327
#5  0x00000000005ba73a in ExecResult (node=3D0x2c6a990) at nodeResult.c:155
#6  0x00000000005a0658 in ExecProcNode (node=3D0x2c6a990) at execProcnode.c=
:372
#7  0x000000000059d1f2 in ExecutePlan (dest=3D0x2c2d3c0,
direction=3D<optimized out>, numberTuples=3D0, sendTuples=3D1 '\001',
    operation=3DCMD_SELECT, planstate=3D0x2c6a990, estate=3D0x2c6a878) at
execMain.c:1449
#8  standard_ExecutorRun (queryDesc=3D0x2c6a468, direction=3D<optimized
out>, count=3D0) at execMain.c:323
#9  0x0000000000681487 in PortalRunSelect (portal=3D0x2c68458,
forward=3D<optimized out>, count=3D0, dest=3D0x2c2d3c0) at pquery.c:942
#10 0x0000000000682a90 in PortalRun (portal=3D0x2c68458,
count=3D9223372036854775807, isTopLevel=3D1 '\001', dest=3D0x2c2d3c0,
    altdest=3D0x2c2d3c0, completionTag=3D0x7fff58a102e0 "") at pquery.c:786
#11 0x000000000067eb99 in exec_simple_query (query_string=3D0x2c2ba38
"select kill_backend();") at postgres.c:1021
#12 PostgresMain (argc=3D<optimized out>, argv=3D<optimized out>,
username=3D<optimized out>) at postgres.c:3881
#13 0x0000000000636da9 in BackendRun (port=3D0x2baa940) at postmaster.c:3587
#14 BackendStartup (port=3D0x2baa940) at postmaster.c:3272
#15 ServerLoop () at postmaster.c:1350
#16 0x0000000000637798 in PostmasterMain (argc=3D<optimized out>,
argv=3D0x2b8a440) at postmaster.c:1110
#17 0x0000000000455f9a in main (argc=3D3, argv=3D0x2b8a440) at main.c:199

Regards

Pavel


2012/1/4 hubert depesz lubaczewski <depesz@depesz.com>:
> On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey@cleverelephant.ca wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: =C2=A0 =C2=A0 =C2=A06379
>> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Paul Ramsey
>> Email address: =C2=A0 =C2=A0 =C2=A0pramsey@cleverelephant.ca
>> PostgreSQL version: 9.1.2
>> Operating system: =C2=A0 OSX 10.6.8
>> Description:
>>
>> CREATE OR REPLACE FUNCTION kill_backend()
>> RETURNS VOID
>> AS $$
>> =C2=A0 DROP TABLE if EXISTS foo;
>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>> $$ LANGUAGE 'SQL';
>
> Cannot replicate:
>
> (depesz@localhost:5910) 20:23:43 [depesz]
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>> =C2=A0 DROP TABLE if EXISTS foo;
>>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> CREATE FUNCTION
> (depesz@localhost:5910) 20:23:49 [depesz]
> $ select kill_backend();
> NOTICE: =C2=A0table "foo" does not exist, skipping
> CONTEXT: =C2=A0SQL function "kill_backend" statement 1
> =C2=A0kill_backend
> --------------
> =C2=A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:55 [depesz]
> $ select kill_backend();
> =C2=A0kill_backend
> --------------
> =C2=A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:56 [depesz]
> $ select kill_backend();
> =C2=A0kill_backend
> --------------
> =C2=A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:58 [depesz]
> $ select version();
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0version
> -------------------------------------------------------------------------=
------------------------------
> =C2=A0PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.r=
eal (Debian 4.6.2-5) 4.6.2, 64-bit
> (1 row)
>
> Side note - definition as is, doesn't work on 9.2:
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>> =C2=A0 DROP TABLE if EXISTS foo;
>>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> ERROR: =C2=A0language "SQL" does not exist
>
> changing it to proper sql (not uppercase) fixed this problem.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact wi=
th it.
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 http://depes=
z.com/
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Paul Ramsey

Date:

04 January 2012, 18:33:14

One extra detail, my PostgreSQL is compiled with --enable-cassert.
This seems to be what sets off the killer function.

On Wed, Jan 4, 2012 at 11:25 AM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey@cleverelephant.ca wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: =A0 =A0 =A06379
>> Logged by: =A0 =A0 =A0 =A0 =A0Paul Ramsey
>> Email address: =A0 =A0 =A0pramsey@cleverelephant.ca
>> PostgreSQL version: 9.1.2
>> Operating system: =A0 OSX 10.6.8
>> Description:
>>
>> CREATE OR REPLACE FUNCTION kill_backend()
>> RETURNS VOID
>> AS $$
>> =A0 DROP TABLE if EXISTS foo;
>> =A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>> $$ LANGUAGE 'SQL';
>
> Cannot replicate:
>
> (depesz@localhost:5910) 20:23:43 [depesz]
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>> =A0 DROP TABLE if EXISTS foo;
>>> =A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> CREATE FUNCTION
> (depesz@localhost:5910) 20:23:49 [depesz]
> $ select kill_backend();
> NOTICE: =A0table "foo" does not exist, skipping
> CONTEXT: =A0SQL function "kill_backend" statement 1
> =A0kill_backend
> --------------
> =A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:55 [depesz]
> $ select kill_backend();
> =A0kill_backend
> --------------
> =A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:56 [depesz]
> $ select kill_backend();
> =A0kill_backend
> --------------
> =A0[null]
> (1 row)
>
> (depesz@localhost:5910) 20:23:58 [depesz]
> $ select version();
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0version
> -------------------------------------------------------------------------=
------------------------------
> =A0PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real=
 (Debian 4.6.2-5) 4.6.2, 64-bit
> (1 row)
>
> Side note - definition as is, doesn't work on 9.2:
> $ CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>> =A0 DROP TABLE if EXISTS foo;
>>> =A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
> ERROR: =A0language "SQL" does not exist
>
> changing it to proper sql (not uppercase) fixed this problem.
>
> Best regards,
>
> depesz
>
> --
> The best thing about modern society is how easy it is to avoid contact wi=
th it.
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 http://depesz.com/

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Pavel Stehule

Date:

04 January 2012, 18:35:11

2012/1/4 Paul Ramsey <pramsey@cleverelephant.ca>:
> One extra detail, my PostgreSQL is compiled with --enable-cassert.
> This seems to be what sets off the killer function.

me too

Pavel


>
> On Wed, Jan 4, 2012 at 11:25 AM, hubert depesz lubaczewski
> <depesz@depesz.com> wrote:
>> On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey@cleverelephant.ca wrot=
e:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: =C2=A0 =C2=A0 =C2=A06379
>>> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Paul Ramsey
>>> Email address: =C2=A0 =C2=A0 =C2=A0pramsey@cleverelephant.ca
>>> PostgreSQL version: 9.1.2
>>> Operating system: =C2=A0 OSX 10.6.8
>>> Description:
>>>
>>> CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>> =C2=A0 DROP TABLE if EXISTS foo;
>>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'SQL';
>>
>> Cannot replicate:
>>
>> (depesz@localhost:5910) 20:23:43 [depesz]
>> $ CREATE OR REPLACE FUNCTION kill_backend()
>>>> RETURNS VOID
>>>> AS $$
>>>> =C2=A0 DROP TABLE if EXISTS foo;
>>>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>>> $$ LANGUAGE 'SQL';
>> CREATE FUNCTION
>> (depesz@localhost:5910) 20:23:49 [depesz]
>> $ select kill_backend();
>> NOTICE: =C2=A0table "foo" does not exist, skipping
>> CONTEXT: =C2=A0SQL function "kill_backend" statement 1
>> =C2=A0kill_backend
>> --------------
>> =C2=A0[null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:55 [depesz]
>> $ select kill_backend();
>> =C2=A0kill_backend
>> --------------
>> =C2=A0[null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:56 [depesz]
>> $ select kill_backend();
>> =C2=A0kill_backend
>> --------------
>> =C2=A0[null]
>> (1 row)
>>
>> (depesz@localhost:5910) 20:23:58 [depesz]
>> $ select version();
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0version
>> ------------------------------------------------------------------------=
-------------------------------
>> =C2=A0PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.=
real (Debian 4.6.2-5) 4.6.2, 64-bit
>> (1 row)
>>
>> Side note - definition as is, doesn't work on 9.2:
>> $ CREATE OR REPLACE FUNCTION kill_backend()
>>>> RETURNS VOID
>>>> AS $$
>>>> =C2=A0 DROP TABLE if EXISTS foo;
>>>> =C2=A0 CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>>> $$ LANGUAGE 'SQL';
>> ERROR: =C2=A0language "SQL" does not exist
>>
>> changing it to proper sql (not uppercase) fixed this problem.
>>
>> Best regards,
>>
>> depesz
>>
>> --
>> The best thing about modern society is how easy it is to avoid contact w=
ith it.
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 http://d=
epesz.com/
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Paul Ramsey

Date:

04 January 2012, 19:35:51

Further notes, from Andrew (RhodiumToad) on IRC about the cause of this crasher:

[12:03pm] RhodiumToad: what happens is this
[12:04pm] RhodiumToad: postquel_start know this statement doesn't
return the result, so it supplies None_Receiver as the dest-receiver
for the query
[12:04pm] RhodiumToad: however, it knows it's a plannedStmt, so it
fires up the full executor to run it
[12:05pm] RhodiumToad: and the executor allocates a new destreceiver
in its own memory context, replaces es->qd->dest with it,
[12:05pm] RhodiumToad: (the new destreceiver is the one that writes
tuples to the created table)
[12:06pm] RhodiumToad: then at executorEnd (called from postquel_end),
executor shutdown closes the new rel, _and then frees the executor's
memory context, including the destreceiver it created
[12:07pm] RhodiumToad: postquel_end doesn't know that its setting of
->dest was clobbered, so it goes to try and destroy it again, and gets
garbage (if assertions are on)
[12:07pm] RhodiumToad: if assertions weren't on, then the rDestroy
call is harmless
[12:07pm] RhodiumToad: well, mostly harmless
[12:07pm] RhodiumToad: sneaky one, that
[12:09pm] RhodiumToad: you can confirm it by tracing through that
second call to postquel_end and confirming that it's the call to
ExecutorEnd that stomps the content of qd->dest
[12:12pm] pramsey: confirmed, the pass through ExecutorEnd has
clobbered the value so there's garbage when it arrives at line 638
[12:14pm] RhodiumToad: if you trace through ExecutorEnd itself, it
should be the FreeExecutorState that does it
[12:15pm] RhodiumToad: wonder how far back this bug goes
[12:16pm] RhodiumToad: actually not very far
[12:17pm] RhodiumToad: older versions just figured that qd->dest was
always None_Receiver and therefore did not need an rDestroy call
[12:17pm] RhodiumToad: (which is a no-op for None_Receiver)
[12:17pm] pramsey: kills my 8.4
[12:17pm] RhodiumToad: so this is broken in 8.4+
[12:17pm] pramsey: ah
[12:18pm] RhodiumToad: 8.4 introduced the lazy-eval of selects in sql functions
[12:19pm] RhodiumToad: prior to that they were always run immediately
to completion
[12:19pm] RhodiumToad: that requires juggling the destreceiver a bit,
hence the bug
[12:20pm] RhodiumToad: btw, the first statement of the function
shouldn't be needed
[12:21pm] RhodiumToad: just  ... as $f$ create table foo as select 1
as x; $f$;  should be enough to break it
[12:31pm] RhodiumToad: there's no trivial fix


On Wed, Jan 4, 2012 at 11:32 AM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
> One extra detail, my PostgreSQL is compiled with --enable-cassert.
> This is required to set off the killer function.
>
>> On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey@cleverelephant.ca wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      6379
>>> Logged by:          Paul Ramsey
>>> Email address:      pramsey@cleverelephant.ca
>>> PostgreSQL version: 9.1.2
>>> Operating system:   OSX 10.6.8
>>> Description:
>>>
>>> CREATE OR REPLACE FUNCTION kill_backend()
>>> RETURNS VOID
>>> AS $$
>>>   DROP TABLE if EXISTS foo;
>>>   CREATE TABLE foo AS SELECT * FROM pg_class LIMIT 1;
>>> $$ LANGUAGE 'sql';
>>>
>>> SELECT kill_backend();
>>

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Tom Lane

Date:

04 January 2012, 20:21:56

Paul Ramsey <pramsey@cleverelephant.ca> writes:
> Further notes, from Andrew (RhodiumToad) on IRC about the cause of this crasher:
> [12:31pm] RhodiumToad: there's no trivial fix

IMO the main bug here is that functions.c isn't expecting qd->dest to be
overwritten,  so we could work around it by keeping a separate private
copy of the dest pointer.  However, it would also be fair to ask whether
there's not a cleaner solution.  Perhaps the intoRel stuff should be
saving/restoring the original destreceiver instead of just blindly
overwriting it.
        regards, tom lane

Re: BUG #6379: SQL Function Causes Back-end Crash

From

Tom Lane

Date:

04 January 2012, 22:37:48

I wrote:
> Perhaps the intoRel stuff should be
> saving/restoring the original destreceiver instead of just blindly
> overwriting it.

I concluded that was the best fix, and have committed it.
        regards, tom lane