Contents
What to do when Postgres blocks a query?
By setting lock_timeout, the DDL command will fail if it ends up waiting for a lock, and thus blocking queries for more than 2 seconds. The downside is that your ALTER TABLE might not succeed, but you can try again later. You may want to query pg_stat_activity to see if there are long-running queries before starting the DDL command.
How to check for lock problems in PostgreSQL?
Looking at pg_locks shows you what locks are granted and what processes are waiting for locks to be acquired. A good query to start looking for lock problems: The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
What happens when you add a column to a PostgreSQL table?
If you add a column with a default, PostgreSQL will rewrite the whole table to fill in the default for every row, which can take hours on large tables. In the meantime, all queries will block, so your database will be unavailable.
Do you have to have select privilege in PostgreSQL?
You must have SELECT privilege on each column used in a SELECT command. The use of FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE or FOR KEY SHARE requires UPDATE privilege as well (for at least one column of each table so selected).
How to know if a lock is held in PostgreSQL?
When you want to know which locks are currently being held/granted in PostgreSQL you can query pg_locks: There is one lock for session 2992 which is not granted and that is the session which currently is trying to insert a row in the table (see above).
Which is an alter sequence option in PostgreSQL?
These forms alter the sequence that underlies an existing identity column. sequence_option is an option supported by ALTER SEQUENCE such as INCREMENT BY. This form sets the per-column statistics-gathering target for subsequent ANALYZE operations.