pg_repack corrupts an unlogged table

[j16sdiz]

I have no idea how it happend, but this is what I seen:

1. try to pg_repack an unlogged table
2. postgresql killed by linux oom-killer
3. pg_repack try to reconnect, but failed because postmaster is recovering
4. after recovery, pg_repack continue (?) and run repack.repack_drop
5. my application is restarted.
6. postmaster complain the index have zero page, suggest me to reindex
7. reindex the table give duplicated rows and fails.

I guess unlogged tables should not be repacked in the first place, right?

[schmiddy]

Yeah, I wouldn't be surprised if there is a glaringpg_repack bug with unlogged tables -- we never bothered to add checks detecting whether a table is logged or unlogged, and no doubt mayhem ensues when we go to switch the filenodes around at the end. BTW, what version of Postgres was this, and are you sure it was an OOM kill?

It probably wouldn't be too difficult to fix this in pg_repack, but I wonder whether supporting unlogged tables is really a good use-case for pg_repack. Presumably, if you are using an unlogged table, you are prepared to sacrifice the table and start from scratch without much pain (otherwise, what do you do in the case of a server crash, like the one you just experienced?). And if you can rebuild the table from scratch easily, what do you need pg_repack for?

[dvarrazzo]

I think the same. For me we could refuse to work on unlogged tables. However points 2. and 4. in the timeline seem suspicious.

[j16sdiz]

@schmiddy This is postgresql 9.5. Yes, sure oom-kill.

I expect the unlogged table is truncated when server restart. But somehow some data seems to persist . Maybe something to do with the initialization fork ?

[j16sdiz]

@dvarrazzo
(2) -- I am not sure this is postgresql problem or not... Other application on the same host use lots of ram too

(4) -- Yes, this is suspicious. pg_repack usually die when it can not connect. But it was retrying when this corruption occur.

I think the easiest fix is just skip all temp and unlogged tables.

[j16sdiz]

Should filter out unlogged tables
It is really easy to hit on this one because running pg_repack without command line options repacks all tables.