CREATE TABLE ... PRIMARY KEY kills backend

Using the cvs version updated this morning, this query kills the backend,
with no explanation in the log (-d 3):

create table junk (id char(4) primary key, name text not null)

If the primary key constraint is omitted, it is OK.

This worked yesterday. Is this a solved problem, or do I need to trace it?

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Enter into his gates with thanksgiving, and into his
courts with praise. Be thankful unto him, and bless
his name." Psalms 100:4

> Using the cvs version updated this morning, this query kills the backend,
> with no explanation in the log (-d 3):
>
> create table junk (id char(4) primary key, name text not null)
>
> If the primary key constraint is omitted, it is OK.
>
> This worked yesterday. Is this a solved problem, or do I need to trace it?
>

Works for me on current sources:

test=> create table junk (id char(4) primary key, name text not null);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey'
for table 'junk'
CREATE

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

"Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> writes:
> Using the cvs version updated this morning, this query kills the backend,
> with no explanation in the log (-d 3):
>
> create table junk (id char(4) primary key, name text not null)

Works for me:

regression=# create table junk (id char(4) primary key, name text not null);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey' for table 'junk'
CREATE

Are you sure you have a complete update of the INDEX_MAX_KEYS changes?
I committed the last of them about 1am EST (6am GMT) this morning, and
it was a change to config.h.in ---- you would need to do a *full*
configure, build, initdb cycle to be sure you have working code.

If that doesn't do it for you, there may be a platform-dependent bug
still lurking; can you provide a debugger backtrace of the crashed
backend?

I'd also suggest running the regress tests ... they pass here, with
the exception of the 'array' test ...

regards, tom lane

Tom Lane wrote:
>"Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> writes:
>> Using the cvs version updated this morning, this query kills the backend,
>> with no explanation in the log (-d 3):
>>
>> create table junk (id char(4) primary key, name text not null)
>
>Works for me:
>
>regression=# create table junk (id char(4) primary key, name text not null);
>NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey' for
> table 'junk'
>CREATE
>
>Are you sure you have a complete update of the INDEX_MAX_KEYS changes?
>I committed the last of them about 1am EST (6am GMT) this morning, and
>it was a change to config.h.in ---- you would need to do a *full*
>configure, build, initdb cycle to be sure you have working code.

./configure --with-tcl --with-mb=UNICODE --with-odbc --enable-locale
--with-maxbackends=64 --with-pgport=5431 --program-prefix=pg7.

(The program-prefix doesn't seem to do anything.)

Database destoyed and initdb run...

>If that doesn't do it for you, there may be a platform-dependent bug
>still lurking; can you provide a debugger backtrace of the crashed
>backend?

Segmentation fault (in the end):
#0 0x400f068d in _IO_default_xsputn () from /lib/libc.so.6
#1 0x400e0126 in vfprintf () from /lib/libc.so.6
#2 0x400edf23 in vsnprintf () from /lib/libc.so.6
#3 0x80a8e82 in appendStringInfo ()
#4 0x80c244d in _outTypeName ()
#5 0x80c43da in _outNode ()
#6 0x80c2391 in _outColumnDef ()
...
#157128 0x80c23f6 in _outColumnDef ()
#157129 0x80c43ca in _outNode ()
#157130 0x80c407c in _outNode ()
#157131 0x80c3f7a in _outConstraint ()
#157132 0x80c475a in _outNode ()
#157133 0x80c407c in _outNode ()
#157134 0x80c23f6 in _outColumnDef ()
#157135 0x80c43ca in _outNode ()
#157136 0x80c407c in _outNode ()
#157137 0x80c219c in _outCreateStmt ()
#157138 0x80c43aa in _outNode ()
#157139 0x80c2578 in _outQuery ()
#157140 0x80c43fa in _outNode ()
#157141 0x80c47fd in nodeToString ()
#157142 0x80ed791 in pg_parse_and_plan ()
#157143 0x80eda46 in pg_exec_query_dest ()
#157144 0x80eda01 in pg_exec_query ()
#157145 0x80eeb82 in PostgresMain ()
#157146 0x80d7ee7 in DoBackend ()
#157147 0x80d7abe in BackendStartup ()
#157148 0x80d6cc9 in ServerLoop ()
#157149 0x80d66ae in PostmasterMain ()
#157150 0x80ae2cb in main ()
#157151 0x400bc7e2 in __libc_start_main () from /lib/libc.so.6

I don't have any line number info, so I'll have to rebuild in order to
do more detailed tracing.

>I'd also suggest running the regress tests ... they pass here, with
>the exception of the 'array' test ...

The regression test doesn't seem to work at all with multibyte enabled:

=============== creating new regression database... =================
createdb: "UNICODE" is not a valid encoding name.
createdb failed
ACTUAL RESULTS OF REGRESSION TEST ARE NOW IN FILE regress.out

The reason is that regress.sh uses createdb; createdb has a bad test for
the encoding value (I have sent a patch separately). So I assume no-one
has run regression tests with multibyte-encoding enabled?

I fixed the createdb bug and ran the regression test. The constraints
test failed when trying to create a table with a primary key. Every
test thereafter failed immediately [pqReadData() -- backend closed the
channel unexpectedly]; it appears that the primary key error messes up
the postmaster in some way.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"For the LORD is good; his mercy is everlasting; and
his truth endureth to all generations."
Psalms 100:5

"Oliver Elphick" wrote:
>I don't have any line number info, so I'll have to rebuild in order to
>do more detailed tracing.

QUERY: create table oljunk (id char(2) primary key, name text);

(gdb) n
134 _outNode(str, node->raw_default);
(gdb) p *str
$25 = {
data = 0x81dd788 "{ QUERY :command 5 :create oljunk { CREATE :relname oljunk :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds :arr"..., len = 263,
maxlen = 512}
(gdb) n
135 appendStringInfo(str, " :cooked_default %s :constraints ",
(gdb) n
137 _outNode(str, node->constraints);
(gdb) n

Program received signal SIGSEGV, Segmentation fault.
0x400f068a in _IO_default_xsputn () from /lib/libc.so.6

This is the backtrace before calling _outNode() at 137:
#0 _outColumnDef (str=0xbfffe8cc, node=0x81dd610) at outfuncs.c:137
str = 0xbfffe8cc
node = (ColumnDef *) 0x81dd610
#1 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
str = 0xbfffe8cc
obj = (void *) 0x81dd610
#2 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd970) at outfuncs.c:1336
l = (List *) 0x81dd970
str = 0xbfffe8cc
obj = (void *) 0x81dd970
#3 0x80c21ac in _outCreateStmt (str=0xbfffe8cc, node=0x81dd7b8)
at outfuncs.c:74
str = 0xbfffe8cc
node = (CreateStmt *) 0x81dd7b8
#4 0x80c43ba in _outNode (str=0xbfffe8cc, obj=0x81dd7b8) at outfuncs.c:1348
str = 0xbfffe8cc
obj = (void *) 0x81dd7b8
#5 0x80c2588 in _outQuery (str=0xbfffe8cc, node=0x81dd8e8) at outfuncs.c:185
str = 0xbfffe8cc
node = (Query *) 0x81dd8e8
#6 0x80c440a in _outNode (str=0xbfffe8cc, obj=0x81dd8e8) at outfuncs.c:1364
str = 0xbfffe8cc
obj = (void *) 0x81dd8e8
#7 0x80c480d in nodeToString (obj=0x81dd8e8) at outfuncs.c:1570
obj = (void *) 0x81dd8e8
str = {
data = 0x81ddc20 "{ QUERY :command 5 :create oljunk { CREATE :relname oljunk :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds :arr"..., len = 298,
maxlen = 512}
#8 0x80ed7a1 in pg_parse_and_plan (
query_string=0x8184da0 "create table oljunk (id char(2) primary key, name text)", typev=0x0, nargs=0, queryListP=0xbfffe97c, dest=Remote,
aclOverride=0 '\000') at postgres.c:435
query_string = 0x81dd8e8 "X\002"
aclOverride = 0 '\000'
querytree_list = (List *) 0x81dd8e8
plan_list = (List *) 0x0
querytree_list_item = (List *) 0x81dda60
querytree = (Query *) 0x81dd8e8
plan = (Plan *) 0x81dd8e8
new_list = (List *) 0x0
rewritten = (List *) 0xf5

Now we run on a bit, and we go into a recursive loop inside _outNode:

_outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1323
1323 if (obj == NULL)
(gdb) p *str
$21 = {
data = 0x81dde28 "{ QUERY :command 5 :create oljunk { CREATE :relname oljunk :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ", len = 823, maxlen = 1024}
(gdb)

(gdb) bt
#0 _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1323
#1 0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
at outfuncs.c:1283
#2 0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#3 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#4 0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
at outfuncs.c:137
#5 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#6 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1336
#7 0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
at outfuncs.c:1283
#8 0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#9 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#10 0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
at outfuncs.c:137
#11 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#12 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1336
#13 0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
at outfuncs.c:1283
#14 0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#15 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#16 0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
at outfuncs.c:137
#17 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#18 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd970) at outfuncs.c:1336
#19 0x80c21ac in _outCreateStmt (str=0xbfffe8cc, node=0x81dd7b8)
at outfuncs.c:74
#20 0x80c43ba in _outNode (str=0xbfffe8cc, obj=0x81dd7b8) at outfuncs.c:1348
#21 0x80c2588 in _outQuery (str=0xbfffe8cc, node=0x81dd8e8) at outfuncs.c:185
#22 0x80c440a in _outNode (str=0xbfffe8cc, obj=0x81dd8e8) at outfuncs.c:1364
#23 0x80c480d in nodeToString (obj=0x81dd8e8) at outfuncs.c:1570
#24 0x80ed7a1 in pg_parse_and_plan (
query_string=0x8184da0 "create table oljunk (id char(2) primary key, name text)", typev=0x0, nargs=0, queryListP=0xbfffe97c, dest=Remote,
aclOverride=0 '\000') at postgres.c:435

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"For the LORD is good; his mercy is everlasting; and
his truth endureth to all generations."
Psalms 100:5

> ./configure --with-tcl --with-mb=UNICODE --with-odbc --enable-locale
> --with-maxbackends=64 --with-pgport=5431 --program-prefix=pg7.

I didn't see your problem here. My configuratuion is:

./configure --with-mb=EUC_JP

> The reason is that regress.sh uses createdb; createdb has a bad test for
> the encoding value (I have sent a patch separately). So I assume no-one
> has run regression tests with multibyte-encoding enabled?

Thank you for the fix. I have just committed your changes.

I already have fixed that too with my private working files, but
forgot to commit.
--
Tatsuo Ishii

"Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> writes:
> Segmentation fault (in the end):
> #0 0x400f068d in _IO_default_xsputn () from /lib/libc.so.6
> #1 0x400e0126 in vfprintf () from /lib/libc.so.6
> #2 0x400edf23 in vsnprintf () from /lib/libc.so.6
> #3 0x80a8e82 in appendStringInfo ()
> #4 0x80c244d in _outTypeName ()
> #5 0x80c43da in _outNode ()
> #6 0x80c2391 in _outColumnDef ()
> ...
> #157128 0x80c23f6 in _outColumnDef ()
> #157129 0x80c43ca in _outNode ()
> #157130 0x80c407c in _outNode ()
^^^^^^

Hmm, I take it this is a stack-growth-limit-exceeded failure, although
your system isn't reporting it that way.

This is a known bug that's been there for quite a while: PRIMARY KEY
generates a parse tree with circular references, so if you have parse
tree dumping turned on, you get an infinite recursion in the dumper
code. It needs to be fixed, but hasn't gotten to the top of anyone's
to-do list (and a clean way to fix it isn't obvious).

> I fixed the createdb bug and ran the regression test. The constraints
> test failed when trying to create a table with a primary key.

If you had -d set high enough, it would...

> Every
> test thereafter failed immediately [pqReadData() -- backend closed the
> channel unexpectedly]; it appears that the primary key error messes up
> the postmaster in some way.

The WAL postmaster takes a few seconds to recover before it will allow
new connections (I have a proposal on the table that it should just
delay accepting the connections, instead of rejecting 'em, but there
hasn't been any discussion about that). I usually see that the next
three or four regression tests fail after a crash, but if your machine
is fast enough it might be that they all do.

I have noticed that recent versions of libpq fail to display the
connection-refused error message that I assume the postmaster is
returning. That useta work ... someone broke it ...

regards, tom lane