2026-09-20 04:18 UTC
RE: https://mastodon.au/@xrobau/117300844625946496
Edit: We have figured it out, and are manually importing to a new temp table.
---
Any #PostgreSQL exports around to help me out here? After a storage server crash, I've ended up with (at least one) table having corrupt time data in it. There is, apparently, no way to pg_dump and ignore errors, and \COPY also doesn't appear to continue-on-error.
What I want to do is just get a list of the IDs of the rows with the corrupt time entries so I can either fix them, or just delete them.
This is the 'status_stats' table from #MastodonNZ, and has about 70m rows.
My current plan is that I've copied the schema of status_stats to status_stats_new, changing the time rows to just 'text' and I'm doing 'insert into status_stats_new select * from status_stats ON CONFLICT DO NOTHING;' then a left join to find missing ones.
IS THERE A BETTER WAY? Halp!
Replies (3)
-
@xrobau@mastodon.au 2026-09-20 07:47
Here's the magic import. 1 1/2 mins for 200k records (there's 73mil that need to be copied) This is going to take a while.
-
@xrobau@mastodon.au 2026-09-20 04:42
Update: I'm chunking the inserts, because it's all being run in a single transaction. Here's my current query: insert into status_stats_new select * from status_stats where id >= (select max(id)+1 from status_stats_new) order by id limit 100000; Yes., that's doing 100k at a time, but only takes 30-40 seconds per chunk.
-
@richardh@mastodon.nz 2026-09-20 05:01
@xrobau@mastodon.au I have had good responses from #postgresql on IRC (LiberaChat). I'm not an expert, but could you SELECT with opposing ORDER BYs, so you converge on the error in the middle? Multiple errors are a bigger problem, of course.