public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: jack <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: constant crashing
Date: Mon, 15 Apr 2024 13:59:00 +1200
Message-ID: <CAApHDvqrEZr7_WpRJZxZnxOSsz80YG6mbLTyQojScxVJiOMV-g@mail.gmail.com> (raw)
In-Reply-To: <EOK1DtQPZjUx8lfmH2mKBjPejveLIfJ8M_GR-At5A9yhT-Ki5JE9GyvQ657CQZWOmZu3OsByefHlGymD3i60c-eYE7uiSYQDVZsujA3fZZA=@a7q.com>
References: <EOK1DtQPZjUx8lfmH2mKBjPejveLIfJ8M_GR-At5A9yhT-Ki5JE9GyvQ657CQZWOmZu3OsByefHlGymD3i60c-eYE7uiSYQDVZsujA3fZZA=@a7q.com>

On Mon, 15 Apr 2024 at 02:25, jack <[email protected]> wrote:
> Then I tried to update the data in blocks of 100,000 records and it crashed 4 times on 4 different blocks.
> So I updated the first crashed block down to the a block of 10 records, until it crashed.
> Then I updated each of the 10 records individually to identify the record that is problemantic, but then all 10 records updated without crashing!
> Pure insanity!

> I have been working on this for just over 1 year now, documenting every step, and I am still unable to get this to work without it crashing somewhere along the way.
> I am beginning to wonder if postgreSQL is bi-polar.

There are two things I can think of that might make PostgreSQL appear
"bi-polar" here.

1) The plan isn't the same when you update a small number of rows vs a
large number rows.  The planner may prefer an index depending on
what's in your WHERE clause.
2) JIT compilation is less likely with a small number or rows as the
plan will come out cheaper.

A stack trace as mentioned by Tom would be ideal, but if you can't get that...

I'd try:

a) Disable "jit" in postgresql.conf and reload the config file with
SELECT pg_reload_conf();

b) Still crashing? Try dropping all indexes before performing the UPDATE.

c)

Still crashing? Can you make it consistently crash with one
problematic CSV file and UPDATE statement?

Does it still crash if you drop all indexes from the table before
performing the UPDATE?
Can you narrow the UPDATE's scope by forcing a TID Range scan by
starting with a WHERE clause such as:

WHERE ctid >= '(0,0)' AND ctid <= '(4294967294,0)';

then increasly try to home in on the problematic value by "binary
searching" for the problematic record.  You can get the number of
blocks in the table with:

select pg_relation_size('your_table'::regclass) /
current_setting('block_size')::int;

replace the 4294967294 with the value from that and bisect the table
until you home in on the block with the issue.

David

view thread (2+ messages)

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], [email protected]
  Subject: Re: constant crashing
  In-Reply-To: <CAApHDvqrEZr7_WpRJZxZnxOSsz80YG6mbLTyQojScxVJiOMV-g@mail.gmail.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