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

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/