From: | jack <jack4pg(at)a7q(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | re: constant crashing |
Date: | 2024-04-14 20:11:24 |
Message-ID: | JWQE7Aq1vAWcUOHIsLlKWJ9mIpXDebOtrYpzXpdyx0q07Zz2ExgfrpgmmFku0Rekl1eu5hCUVoUgO5CQrMCwDA8ziWAebL9Cc2XATZRgF8Q=@a7q.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)lf ERROR: could not access status of transaction 3687904299
2024-04-14 16:00:39.326 EDT [1668] postgres(at)lf DETAIL: Could not open file "pg_xact/0DBD": No such file or directory.
2024-04-14 16:00:39.326 EDT [1668] postgres(at)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.
From | Date | Subject | |
---|---|---|---|
Next Message | jack | 2024-04-14 20:18:30 | re: constant crashing |
Previous Message | Adrian Klaver | 2024-04-14 19:57:15 | Re: constant crashing |