Concurrent DDL guard?

[dvarrazzo]

It is generally unsafe to perform DDL on your table while it is being reorg'ed, except for VACUUM or ANALYZE. Although the primary benefit of pg_reorg is that it does not hold a high-level lock on the target table while it is being reorg'ed, this also leaves us with no hard and fast way to prevent unsafe DDL. Perhaps we could add in some sanity checks of pg_class, pg_tablespace, etc. before and after the reorg finishes, and throw an error if any unexpected changes in the table attributes are detected.

[dvarrazzo]

Reading this issue: wouldn't be enough to take a "ACCESS SHARE" lock, which doesn't conflict with anything except the ACCESS EXCLUSIVE taken by ALTER TABLE?

See lock compatibility table.

[schmiddy]

Yeah, that thought had crossed my mind as well.

The only obstacle is that the LOCK TABLE ... IN ACCESS SHARE MODE would have to be performed in a separate connection, since we want the lock to be held throughout pg_reorg's multiple transactions of work. Then, when it's time to release the ACCESS SHARE lock so that the other connection's ACCESS EXCLUSIVE could go through, we'll have to be careful that there's no one else waiting for a lock on the table who could succeed in altering the table before we acquire our desired ACCESS EXCLUSIVE lock.

It looks like lock_exclusive() is called in two places: first during initial set-up, and lastly before the table swap. We'd want to avoid a race between releasing the first EXCLUSIVE lock and acquiring the ACCESS SHARE lock in the second connection.

[schmiddy]

I've got a rough implementation of this idea, in my fork at pg_reorg-1.

The basic idea is to set up a second connection which will (asynchronously) try to grab an AccessShare lock while the primary connection still holds AccessExclusive lock during the setup phase. This AccessShare lock is held until step #5, the table swap, when it is bumped to AccessExclusive lock, and the second connection performs the table swapping.

I may well have missed something obvious, and I'll do some more testing and cleanup in the next day or two. If I haven't screwed anything up too badly, perhaps this project could be moved into a branch in reorg master after that.

[schmiddy]

I tagged this issue with milestone Rel 1.2, since presumably we wouldn't want to shoehorn it into 1.1.x. I'll try to make a branch off the main git repository with these changes. If these changes are OK to merge into master at some point, we could get rid of the forbid_truncate branch and idea, since this feature would of course take care of TRUNCATE as well.