-4

Statements:

    1. Vacuum analyze;
    1. Vacuum full;
    1. Create unique index concurrently on users ....
    1. Create index on users .....
    1. Insert into users....
    1. Alter table users add constraint 'new_check_name' CHECK (....) not valid;
    1. update users set column = $1;
    1. update users set column_name = $2 where pk = $2;
2
  • 2
    Some of the answers will depend on your version of Postgres. Could you please provide your Postgres version? – Arkhena Mar 20 at 8:54
  • 2
    Please don't vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right, under the CC BY-SA 4.0 license, for Stack Exchange to distribute that content (i.e. regardless of your future choices). By Stack Exchange policy, the non-vandalized version of the post is the one which is distributed, and thus, any vandalism will be reverted. If you want to know more about deleting a post please see: How does deleting work? – cigien Mar 22 at 6:42
3

Here are the answers for Postgres 13, with reference to the official documentation, I let you navigate to your own version of Postgres to see if there were changes:

    1. will allow writes on the table (see "Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained." in the official documentation https://www.postgresql.org/docs/current/sql-vacuum.html)
    1. won't allow writes on the table (see "VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed." in the official documentation https://www.postgresql.org/docs/current/sql-vacuum.html)
    1. will allow writes (see "CONCURRENTLY When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table" in the official documentation https://www.postgresql.org/docs/current/sql-createindex.html)
    1. won't allow writes (see "Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table." in the official documentation https://www.postgresql.org/docs/current/sql-createindex.html)
    1. won't allow concurrent writes on the newly inserted rows until the transaction is over, but will allow other transactions to change other rows (see "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)." in the official documentation https://www.postgresql.org/docs/current/transaction-iso.html)
    1. won't allow writes on the table (see "Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock." and the matrix that shows that ACCESS EXCLUSIVE lock is the highest level of lock in the official documentation https://www.postgresql.org/docs/9.6/sql-altertable.html and https://www.postgresql.org/docs/current/explicit-locking.html)
    1. won't allow concurrent writes on the rows being updated until the end of the transaction (see "UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)." in the official documentation https://www.postgresql.org/docs/current/transaction-iso.html)
    1. see point 7

Not the answer you're looking for? Browse other questions tagged or ask your own question.