Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #3648: Server crashes when trying to create a table

BUG #3648: Server crashes when trying to create a table

From
"Anton"
Date:
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

Re: BUG #3648: Server crashes when trying to create a table

From
Alvaro Herrera
Date:
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

Re: BUG #3648: Server crashes when trying to create a table

From
"Anton A. Patrushev"
Date:
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.
>

Re: BUG #3648: Server crashes when trying to create a table

From
"Anton A. Patrushev"
Date:
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.
>

Re: BUG #3648: Server crashes when trying to create a table

From
Alvaro Herrera
Date:
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)

Re: BUG #3648: Server crashes when trying to create a table

From
"Anton A. Patrushev"
Date:
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; $$;
>