Re: BUG #2168: 45.000.000 records too much? - Mailing list pgsql-bugs
From | Steven Mooij |
---|---|
Subject | Re: BUG #2168: 45.000.000 records too much? |
Date | |
Msg-id | 43CE383E.4010707@mooij.name Whole thread Raw |
In response to | Re: BUG #2168: 45.000.000 records too much? (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-bugs |
Tom Lane wrote: > "Steven Mooij" <steven@mooij.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...
pgsql-bugs by date: