Those are excellent points. We will investigate adjusting pg_dump behavior, as this is primarily a dump+restore issue.
Thank you! -Andrew J Repp (VMware) On Tue, Jan 24, 2023, at 9:56 PM, Tom Lane wrote: > Andrew <pgsqlhack...@andrewrepp.com> writes: > > I have discovered a bug in one usage of enums. If a table with hash > > partitions uses an enum as a partitioning key, it can no longer be > > backed up and restored correctly. This is because enums are represented > > simply as oids, and the hash function for enums hashes that oid to > > determine partition distribution. Given the way oids are assigned, any > > dump+restore of a database with such a table may fail with the error > > "ERROR: new row for relation "TABLENAME" violates partition constraint". > > Ugh, that was not well thought out :-(. I suppose this isn't a problem > for pg_upgrade, which should preserve the enum value OIDs, but an > ordinary dump/restore will indeed hit this. > > > I have written a patch to fix this bug (attached), by instead having the > > hashenum functions look up the enumsortorder ID of the value being > > hashed. These are deterministic across databases, and so allow for > > stable dump and restore. > > Unfortunately, I'm not sure those are as deterministic as all that. > They are floats, so there's a question of roundoff error, not to > mention cross-platform variations in what a float looks like. (At the > absolute minimum, I think we'd have to change your patch to force > consistent byte ordering of the floats.) Actually though, roundoff > error wouldn't be a problem for the normal exact-integer values of > enumsortorder. Where it could come into play is with the fractional > values used after you insert a value into the existing sort order. > And then the whole idea fails, because a dump/restore won't duplicate > those fractional values. > > Another problem with this approach is that we can't get from here to there > without a guaranteed dump/reload failure, since it's quite unlikely that > the partition assignment will be the same when based on enumsortorder > as it was when based on OIDs. Worse, it also breaks the pg_upgrade case. > > I wonder if it'd work to make pg_dump force --load-via-partition-root > mode when a hashed partition key includes an enum. > > regards, tom lane >
