12 thoughts on “How to run short ALTER TABLE without long locking concurrent queries”

1. You can also do a variation of this where you start a transaction and then attempt to take a NOWAIT table lock on the table you need to alter. It will error the transaction immediately if it can’t take the lock and then you can loop with whatever wait time you want.

   Has the downside that you will never succeed if you can’t find a time that doesn’t have a conflicting lock already, but has the upside of you don’t block for 50ms (or whatever your statement_timeout needs to be) repeatedly.

   Still a good idea to put a statement_timeout on this method also just in case your change takes longer than you thought it should, or you attempted to bundle something else in that transaction that you expected to be fast that isn’t.

2. Curious what you see as pro/con on statement_timeout vs lock_timeout here?

3. @Jeremy Schneider:
   well, now that I’ve read it – it looks that lock_time is/would be even better. Forgot about it 🙂

4. Is there any specific reason why PL/pgSQL LOOP was not used? If I apply my Oracle experience to this task using that lock_timeout Jeremy Schneider pointed out, I would rather do something like this:

   DO
   $do$
   DECLARE
      lock_timeout CONSTANT text := '50ms';
      max_attempts CONSTANT INT := 1000;
      ddl_completed BOOLEAN := FALSE;
   BEGIN 
    
      PERFORM set_config('lock_timeout', lock_timeout, FALSE);
    
      FOR i IN 1..max_attempts LOOP
         BEGIN
            EXECUTE 'ALTER TABLE test add column whatever2 INT4';
            ddl_completed := TRUE;
            EXIT;
         EXCEPTION 
            WHEN lock_not_available THEN
              NULL;
         END;
      END LOOP;
    
      IF ddl_completed THEN
         RAISE INFO 'DDL has been successfully completed';
      ELSE
         RAISE EXCEPTION 'Failed to perform DDL';
      END IF;
   END
   $do$;

5. @Mikhail Velikikh:

   No real reason, aside form the fact that I somehow try to steer clear of exception handling in plpgsql. Probably in this case it wasn’t necessary.

   On the other hand – my code seems to be shorter 🙂

6. This is genius, I love it! I took the approach a couple of days ago of doing the DDL change out of hours, but this would have been much better.

7. There are basically 2 ways to do this:
   1. Using timeout logic as explained in this blog, or
   2. Using NOWAIT feature, shown below:

   DO LANGUAGE plpgsql $$
   BEGIN
   FOR get_lock IN 1 .. 10 LOOP
     BEGIN
       LOCK TABLE mytableparent IN ACCESS EXCLUSIVE MODE NOWAIT;
       ALTER TABLE mytablechild_01 NO INHERIT mytableparent;
       ALTER TABLE mytablechild_02 NO INHERIT mytableparent;
       RAISE INFO 'locked table and DISINHERITED tables successfully';
       EXIT;
     END;
   END LOOP;
   END;
   $$;

8. @Michael:

   yeah, but NOWAIT will exit immediately, not even leaving the chance to wait for 50ms (or whatever is acceptable). That’s why I didn’t use it.

9. Good point, @depesz, but the loop variable iteration tries to account for that by trying multiple times before giving up, so you could adjust it higher. Don’t get me wrong, I like your way as well!

10. @Michael:

    sure, you can add loop, but – the way it’s written will cause “busy-loop” using all of CPU, and if we’d add sleeps – it is very possible that checking for locks would never succeed on busy systems, while a waiting solution might.

11. @Depesz, not trying to belabor this point too much but in your opinion can you think of a scenario in which using NOWAIT might be a better solution?

12. @Michael:
    only if you really don’t want to wait. As in: if it locks, do something, otherwise do something else.

Comments are closed.