Re: BUG #2168: 45.000.000 records too much?

Tom Lane wrote:

> "Steven Mooij" <steven(at)mooij(dot)name> writes:
>
>
>> testsearch=> insert into t_documentword2 (SELECT document_id,
>> t_word2.id,
>> frequency from t_documentword, t_word2 where t_documentword.word =
>> t_word2.word);
>> server closed the connection unexpectedly
>>
>
>
> There's not enough information here to guess what the problem is.
> (But it's not table size; people routinely manipulate tables much
> bigger than that in Postgres.) A stack trace from the core dump
> would be really helpful, or even better a self-contained test case
> that other people could replicate the failure with. See the bug
> reporting guidelines at
> http://www.postgresql.org/docs/8.1/static/bug-reporting.html
>
>
>

I created a self-contained test case, here's a script that produces a
similar error:

CREATE TABLE
t_test1
(
x bigint,
y bigint,
PRIMARY KEY(x)
)
WITHOUT OIDS;

CREATE TABLE
t_test2
(
x bigint,
z bigint,
PRIMARY KEY (x),
FOREIGN KEY (x) REFERENCES t_test1(x)
)
WITHOUT OIDS;

INSERT INTO t_test1 VALUES (1, 2);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);
INSERT INTO t_test1 (SELECT x + (SELECT count(*) FROM t_test1), y +
(SELECT count(*) FROM t_test1) FROM t_test1);

INSERT INTO t_test2 (SELECT x, x * 2 FROM t_test1);

The first block of inserts completes flawlessly and fills t_test1 with
over 64.000.000 records. It's the last statement copying (no join
involved this time) from t_test1 to t_test2 that results in:

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: Failed.

I am not 100% sure, but i think this is the relevant part of the logfile
that goes with it:

LOG: database system was interrupted at 2006-01-18 00:07:48 CET
LOG: checkpoint record is at B/A2857768
LOG: redo record is at B/A28535B0; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 2724; next OID: 75506450
LOG: database system was not properly shut down; automatic recovery in
progressLOG: incomplete startup packet
LOG: redo starts at B/A28535B0
LOG: record with zero length at B/A28577EC
LOG: redo done at B/A28577A8
FATAL: the database system is starting up
LOG: database system is ready

>> I got the same result in version 7.5.15.
>>
>
>
> There is no version of Postgres named 7.5.anything.
>
>
>
I wrongfully looked at the version of the Debian package. When I start
psql it says "Welcome to psql 7.4.9, the PostgreSQL interactive terminal."

Hopefully this is enough information for you to look into it, but if you
do need anything else please let me know...