public inbox for [email protected]
help / color / mirror / Atom feedFrom: jack <[email protected]>
To: [email protected] <[email protected]>
Subject: re: constant crashing
Date: Sun, 14 Apr 2024 20:11:24 +0000
Message-ID: <JWQE7Aq1vAWcUOHIsLlKWJ9mIpXDebOtrYpzXpdyx0q07Zz2ExgfrpgmmFku0Rekl1eu5hCUVoUgO5CQrMCwDA8ziWAebL9Cc2XATZRgF8Q=@a7q.com> (raw)
To show you how bi-polar this is really becoming, I tried a work-around...
I took the table called us113 with 113 million records and tried to break it down into 10 smaller tables each having about 10 million records, using the following code:
\set ECHO all
\set ON_ERROR_STOP on
-- Create 10 new tables (us113_01 to us113_10) similar to the original table (us113)
CREATE TABLE us113_01 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num <= 10000000;
CREATE TABLE us113_02 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 10000000 AND row_num <= 20000000;
CREATE TABLE us113_03 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 20000000 AND row_num <= 30000000;
CREATE TABLE us113_04 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 30000000 AND row_num <= 40000000;
CREATE TABLE us113_05 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 40000000 AND row_num <= 50000000;
CREATE TABLE us113_06 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 50000000 AND row_num <= 60000000;
CREATE TABLE us113_07 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 60000000 AND row_num <= 70000000;
CREATE TABLE us113_08 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 70000000 AND row_num <= 80000000;
CREATE TABLE us113_09 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 80000000 AND row_num <= 90000000;
CREATE TABLE us113_10 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 90000000;
...and of course it crashed after creating 7 tables.
2024-04-14 15:59:12.294 EDT [1212] LOG: database system is ready to accept connections
2024-04-14 16:00:39.326 EDT [1668] postgres@lf ERROR: could not access status of transaction 3687904299
2024-04-14 16:00:39.326 EDT [1668] postgres@lf DETAIL: Could not open file "pg_xact/0DBD": No such file or directory.
2024-04-14 16:00:39.326 EDT [1668] postgres@lf STATEMENT: CREATE TABLE us113_08 AS SELECT * FROM ( SELECT *, ROW_NUMBER() OVER () AS row_num FROM us113) sub WHERE row_num > 70000000 AND row_num <= 80000000;
This is what I am dealing with.
Every turn I take to get around a problem, I get more errors.
I am not sure if this makes a difference but the machine actually has 144 GIG of RAM not 128 GIG. I know that Windows may have an issue with this, but I would not think ubuntu would. But I thought I'd throw that into the mess anyway.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: re: constant crashing
In-Reply-To: <JWQE7Aq1vAWcUOHIsLlKWJ9mIpXDebOtrYpzXpdyx0q07Zz2ExgfrpgmmFku0Rekl1eu5hCUVoUgO5CQrMCwDA8ziWAebL9Cc2XATZRgF8Q=@a7q.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox