Обсуждение: BUG #3648: Server crashes when trying to create a table
The following bug has been logged online:
Bug reference: 3648
Logged by: Anton
Email address: anton@orkney.co.jp
PostgreSQL version: 8.2.5 and 8.1.9
Operating system: CentOS 5 and Xubuntu 7.04
Description: Server crashes when trying to create a table
Details:
I have a plpgsql function which creates a table with following line:
CREATE TABLE vertices_tmp(id serial);
It's working fine with PostgreSQL 8.2.4 but crashes with segfault at
versions 8.1.9 and 8.2.5.
Here is a piece of log file:
2007-10-03 13:15:13 JST NOTICE: CREATE TABLE will create implicit sequence
"vertices_tmp_id_seq" for serial column "vertices_tmp.id"
2007-10-03 13:15:13 JST CONTEXT: SQL statement "CREATE TABLE "vertices_tmp"
( id serial )"
PL/pgSQL function "assign_vertex_id" line 19 at SQL statement
2007-10-03 13:15:13 JST LOG: server process (PID 10424) was terminated by
signal 11
2007-10-03 13:15:13 JST LOG: terminating any other active server processes
2007-10-03 13:15:13 JST LOG: all server processes terminated;
reinitializing
2007-10-03 13:15:13 JST LOG: database system was interrupted at 2007-10-03
13:14:00 JST
2007-10-03 13:15:13 JST LOG: checkpoint record is at 0/A27F48
2007-10-03 13:15:13 JST LOG: redo record is at 0/A27F48; undo record is at
0/0; shutdown FALSE
2007-10-03 13:15:13 JST LOG: next transaction ID: 0/877; next OID: 49152
2007-10-03 13:15:13 JST LOG: next MultiXactId: 1; next MultiXactOffset: 0
2007-10-03 13:15:13 JST LOG: database system was not properly shut down;
automatic recovery in progress
2007-10-03 13:15:13 JST FATAL: the database system is starting up
2007-10-03 13:15:13 JST LOG: redo starts at 0/A27F90
2007-10-03 13:15:13 JST LOG: invalid magic number 0000 in log file 0,
segment 0, offset 10960896
2007-10-03 13:15:13 JST LOG: redo done at 0/A73728
2007-10-03 13:15:13 JST LOG: database system is ready
Anton wrote:
>
> CREATE TABLE vertices_tmp(id serial);
>
> It's working fine with PostgreSQL 8.2.4 but crashes with segfault at
> versions 8.1.9 and 8.2.5.
Works for me:
alvherre=# create function test_anton() returns void language plpgsql as $$ begin create table anton(a serial); end;
$$;
CREATE FUNCTION
alvherre=# select test_anton();
NOTICE: CREATE TABLE will create implicit sequence "anton_a_seq" for serial column "anton.a"
CONTEXT: SQL statement "create table anton(a serial)"
PL/pgSQL function "test_anton" line 1 at SQL statement
test_anton
------------
(1 row)
alvherre=# select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.1 (GCC) 4.1.3 20070831 (prerelease) (Debian
4.1.2-16)
(1 row)
My guess is you are doing something else on the function that you
aren't showing. Whatever you do after the CREATE TABLE perhaps.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi Alvaro, In the function I'm trying to drop this table if it already exists: BEGIN DROP TABLE vertices_tmp; EXCEPTION WHEN UNDEFINED_TABLE THEN END; CREATE TABLE vertices_tmp ( id serial ); ... Thanks, Anton. > Anton wrote: > > > > > CREATE TABLE vertices_tmp(id serial); > > > > It's working fine with PostgreSQL 8.2.4 but crashes with segfault at > > versions 8.1.9 and 8.2.5. > > Works for me: > > alvherre=# create function test_anton() returns void language plpgsql as $$ begin create table anton(a serial); end; $$; > CREATE FUNCTION > alvherre=# select test_anton(); > NOTICE: CREATE TABLE will create implicit sequence "anton_a_seq" for serial column "anton.a" > CONTEXT: SQL statement "create table anton(a serial)" > PL/pgSQL function "test_anton" line 1 at SQL statement > test_anton > ------------ > > (1 row) > > alvherre=# select version(); > version > --------------------------------------------------------------------------------------------------------------------------- > PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.1 (GCC) 4.1.3 20070831 (prerelease) (Debian 4.1.2-16) > (1 row) > > My guess is you are doing something else on the function that you > aren't showing. Whatever you do after the CREATE TABLE perhaps. >
Hi Alvaro,
In the function I'm trying to drop this table if it already exists:
BEGIN
DROP TABLE vertices_tmp;
EXCEPTION
WHEN UNDEFINED_TABLE THEN
END;
CREATE TABLE vertices_tmp ( id serial );
...
Thanks,
Anton.
> Anton wrote:
>
> >
> > CREATE TABLE vertices_tmp(id serial);
> >
> > It's working fine with PostgreSQL 8.2.4 but crashes with segfault at
> > versions 8.1.9 and 8.2.5.
>
> Works for me:
>
> alvherre=# create function test_anton() returns void language plpgsql as $$ begin create table anton(a serial); end;
$$;
> CREATE FUNCTION
> alvherre=# select test_anton();
> NOTICE: CREATE TABLE will create implicit sequence "anton_a_seq" for serial column "anton.a"
> CONTEXT: SQL statement "create table anton(a serial)"
> PL/pgSQL function "test_anton" line 1 at SQL statement
> test_anton
> ------------
>
> (1 row)
>
> alvherre=# select version();
> version
>
---------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.1 (GCC) 4.1.3 20070831 (prerelease) (Debian
4.1.2-16)
> (1 row)
>
> My guess is you are doing something else on the function that you
> aren't showing. Whatever you do after the CREATE TABLE perhaps.
>
Anton A. Patrushev wrote:
> Hi Alvaro,
>
> In the function I'm trying to drop this table if it already exists:
>
> BEGIN
>
> DROP TABLE vertices_tmp;
> EXCEPTION
> WHEN UNDEFINED_TABLE THEN
> END;
>
> CREATE TABLE vertices_tmp ( id serial );
>
> ...
It still works for me. Can you try my function on your system? Does it
crash?
create or replace function test_anton() returns void language plpgsql
as $$
begin
begin
drop table anton;
exception
when undefined_table then
end;
create table anton(a serial);
end; $$;
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
"Digital and video cameras have this adjustment and film cameras don't for the
same reason dogs and cats lick themselves: because they can." (Ken Rockwell)
Thanks Alvaro, I found the reason. The problem was after the table creation. Anton. > Anton A. Patrushev wrote: > > Hi Alvaro, > > > > In the function I'm trying to drop this table if it already exists: > > > > BEGIN > > > > DROP TABLE vertices_tmp; > > EXCEPTION > > WHEN UNDEFINED_TABLE THEN > > END; > > > > CREATE TABLE vertices_tmp ( id serial ); > > > > ... > > It still works for me. Can you try my function on your system? Does it > crash? > > create or replace function test_anton() returns void language plpgsql > as $$ > begin > begin > drop table anton; > exception > when undefined_table then > end; > create table anton(a serial); > end; $$; >