BUG #3763: crash after create table with primary key defined

On Tue, 2007-11-20 at 10:12 +0000, migo wrote:

The following bug has been logged online:

Bug reference: 3763
Logged by: migo
Email address: admin@abp.pl
PostgreSQL version: 8.2.4
Operating system: Gentoo
Description: crash after create table with primary key defined
Details:

On psql console:

=> CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL
CHECK (role_id >= 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK
(auth_option_id >= 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY
(role_id, auth_option_id) );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
wolni=>

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE TABLE /
PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey" for
table
Nov 20 11:02:04 www postgres[32757]: [4-2] "phpbb_acl_roles_data"
Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]>LOG: connection
received: host=[local]
Nov 20 11:02:04 www postgres[32758]: [3-1] wolni>LOG: connection
authorized: user=wolni database=wolni
Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID 32757)
was terminated by signal 11
Nov 20 11:02:04 www postgres[32731]: [3-1] >LOG: terminating any other
active server processes
Nov 20 11:02:04 www postgres[32758]: [4-1] wolni>WARNING: terminating
connection because of crash of another server process

Looks to me that the process that crashed was not the process that
issued the CREATE TABLE command. So it could be something else entirely.

Is this behaviour repeatable?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Dnia Wt Listopada 20 2007, 12:12, Simon Riggs napisaďż˝(a):

On Tue, 2007-11-20 at 10:12 +0000, migo wrote:

The following bug has been logged online:

Bug reference: 3763
Logged by: migo
Email address: admin@abp.pl
PostgreSQL version: 8.2.4
Operating system: Gentoo
Description: crash after create table with primary key defined
Details:

On psql console:

=> CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT
NULL
CHECK (role_id >= 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK
(auth_option_id >= 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY
KEY
(role_id, auth_option_id) );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
wolni=>

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE TABLE /
PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey" for
table
Nov 20 11:02:04 www postgres[32757]: [4-2] "phpbb_acl_roles_data"
Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]>LOG: connection
received: host=[local]
Nov 20 11:02:04 www postgres[32758]: [3-1] wolni>LOG: connection
authorized: user=wolni database=wolni
Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID
32757)
was terminated by signal 11
Nov 20 11:02:04 www postgres[32731]: [3-1] >LOG: terminating any other
active server processes
Nov 20 11:02:04 www postgres[32758]: [4-1] wolni>WARNING: terminating
connection because of crash of another server process

Looks to me that the process that crashed was not the process that
issued the CREATE TABLE command. So it could be something else entirely.

Is this behaviour repeatable?

Yes, when i type this create table command allways postgresql crashes in
that same way.

Its strange that when i modify this command to:
"CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT
NULL CHECK (role_id >= 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK
(auth_option_id >= 0), auth_setting INT2 DEFAULT '0' NOT NULL );"
(PRIMARY KEY option cuted off) query works fine.

Regards,
--
Tomasz

Simon Riggs wrote:

On Tue, 2007-11-20 at 10:12 +0000, migo wrote:

=> CREATE TABLE phpbb_acl_roles_data ( role_id INT4 DEFAULT '0' NOT NULL
CHECK (role_id >= 0), auth_option_id INT4 DEFAULT '0' NOT NULL CHECK
(auth_option_id >= 0), auth_setting INT2 DEFAULT '0' NOT NULL, PRIMARY KEY
(role_id, auth_option_id) );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
wolni=>

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE TABLE /
PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey" for
table
Nov 20 11:02:04 www postgres[32757]: [4-2] "phpbb_acl_roles_data"
Nov 20 11:02:04 www postgres[32758]: [2-1] [unknown]>LOG: connection
received: host=[local]
Nov 20 11:02:04 www postgres[32758]: [3-1] wolni>LOG: connection
authorized: user=wolni database=wolni
Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID 32757)
was terminated by signal 11
Nov 20 11:02:04 www postgres[32731]: [3-1] >LOG: terminating any other
active server processes
Nov 20 11:02:04 www postgres[32758]: [4-1] wolni>WARNING: terminating
connection because of crash of another server process

Looks to me that the process that crashed was not the process that
issued the CREATE TABLE command. So it could be something else entirely.

To me it does look like it was the CREATE TABLE that crashed. The NOTICE
shows that it's process ID 32757 that's running the CREATE TABLE, and
the LOG line later on says that that's the process that crashed.

Is this behaviour repeatable?

I couldn't reproduce this on my laptop with 8.2.4. Migo, is it possible
for you to get a core dump and a back trace out of it? Is there anything
unusual about the system / compiler / compiler options used?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 2007-11-20 at 11:46 +0000, Heikki Linnakangas wrote:

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE TABLE /
PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey" for
table

Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID 32757)
was terminated by signal 11

To me it does look like it was the CREATE TABLE that crashed. The NOTICE
shows that it's process ID 32757 that's running the CREATE TABLE, and
the LOG line later on says that that's the process that crashed.

Yep, agreed. :-(

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Dnia Wt Listopada 20 2007, 14:20, Simon Riggs napisaďż˝(a):

On Tue, 2007-11-20 at 11:46 +0000, Heikki Linnakangas wrote:

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE

TABLE /

PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey"

for

table

Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID

32757)

was terminated by signal 11

To me it does look like it was the CREATE TABLE that crashed. The NOTICE
shows that it's process ID 32757 that's running the CREATE TABLE, and
the LOG line later on says that that's the process that crashed.

Yep, agreed. :-(

OK so what we can do now?

Regards,
--
Tomasz

admin@abp.pl wrote:

Dnia Wt Listopada 20 2007, 14:20, Simon Riggs napisaďż˝(a):

On Tue, 2007-11-20 at 11:46 +0000, Heikki Linnakangas wrote:

In logs after this:
Nov 20 11:02:04 www postgres[32757]: [4-1] wolni>NOTICE: CREATE

TABLE /

PRIMARY KEY will create implicit index "phpbb_acl_roles_data_pkey"

for

table
Nov 20 11:02:04 www postgres[32731]: [2-1] >LOG: server process (PID

32757)

was terminated by signal 11

To me it does look like it was the CREATE TABLE that crashed. The NOTICE
shows that it's process ID 32757 that's running the CREATE TABLE, and
the LOG line later on says that that's the process that crashed.

Yep, agreed. :-(

OK so what we can do now?

Do you have core file? It is usually stored in data postgreSQL
directory. Can you provide stack trace to determine which function
fails? It is very important.

what's happen when you create primary key by alter table command?

what's happen when you create unique index on (role_id, auth_option_id)?

Is it fresh database? Can you reproduce it on the same machine with the
same postgreSQL installation on new created database cluster?

Zdenek