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

On Wed, Jan 04, 2012 at 07:17:17PM +0000, pramsey(at)cleverelephant(dot)ca wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6379
> Logged by: Paul Ramsey
> Email address: pramsey(at)cleverelephant(dot)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(at)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(at)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(at)localhost:5910) 20:23:55 [depesz]
$ select kill_backend();
kill_backend
--------------
[null]
(1 row)

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

(depesz(at)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/