All Questions
Tagged with blocking postgresql
21 questions
1
vote
0
answers
57
views
PostgreSQL locking problem
TL;DR
We have a postgres stored procedure running as a job, which calls another stored procedure, which calls yet another stored procedure, and this internal stored procedure acquires ROW EXCLUSIVE ...
- 163
0
votes
1
answer
235
views
Postgres VIEW locking: CREATE OR REPLACE vs. DROP and CREATE
My question relates to a production incident in which it was necessary to replace a poorly performing Postgres VIEW under load. We tried numerous times to redefine the VIEW using CREATE OR REPLACE; ...
- 103
1
vote
1
answer
233
views
Why does VALIDATE CONSTRAINT on PG 14.7 take a ShareLock?
I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE ...
- 305
0
votes
0
answers
470
views
Refreshing materialized view blocks an unrelated table
Last few days I was experiencing a problem that seems really strange to me and even though I have some experience with PostgreSQL, I really don't understand what may be the cause.
We have two services ...
1
vote
2
answers
2k
views
How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?
I have a table like this in a Postgres database:
CREATE TABLE tabwithunique (
id SERIAL PRIMARY KEY,
key BIGINT NOT NULL UNIQUE,
value BIGINT NOT NULL
);
I want to design a statement that ...
- 121
-1
votes
1
answer
58
views
How to tell Postgresql what tables will be in a transaction?
RDS Postgresql 12.12
CREATE INDEX CONCURRENTLY idx_foo ON foo (f1, f2, f3); is blocked by a stored procedure which only touches tables bar and baz.
BEGIN and START TRANSACTION don't seem to have a ...
- 658
1
vote
1
answer
611
views
What could be causing the delay in synchronous replication between the master and slave nodes?
I have 2 PostgreSQL 13 database, one acting as a master and the other as a slave, using streaming replication with synchronous_commit=remote_apply. This means that all data written to the primary ...
- 31
1
vote
1
answer
5k
views
Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition
I have a simple partitioned table in PostgreSQL 14, like this:
create table entity
(
dataset_id integer not null references dataset (id),
...
) partition by list (dataset_id);
create table ...
- 210
1
vote
2
answers
2k
views
Lock specific select query until all insert transactions are commited
I have an insert-only table in my database that contains time-series data.
When my system starts, I need to populate an in-memory state with the last-month data (each row contains data for one minute) ...
- 135
0
votes
0
answers
2k
views
What could block a SELECT (read) in postgres
I have a few long running transacrions that both select and update data. When the jobs are running, I suddently cannot run a basic SELECT + JOIN query on the table? It simply hangs forever... I ...
- 1
0
votes
1
answer
1k
views
Create table with foreign keys blocked by other Table-level Lock
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.
When creating a new table B with foreign keys to table A, the operation tries to acquires ...
- 135
1
vote
1
answer
535
views
Why is PostgreSQL blocking creation of a new table on update on foreign table?
I understand why an UPDATE query on tableA can block an ALTER TABLE DDL statement on the same tableA.
ex:
UPDATE tableA SET column1 = 'test' WHERE id = 1;`
But I am trying to create a new table, say ...
- 131
1
vote
0
answers
261
views
Concurrent UPDATE statements of single row in small table takes minutes
I have a very simple table with no indexes (although scan_uuid could have a UNIQUE index if that helps):
create table public.host_scan_complete_log_counts
(
scan_uuid uuid not null,
...
- 165
0
votes
1
answer
2k
views
Postgres CREATE OR REPLACE VIEW... can it block?
I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;
that is being used, e.g. lots of SELECT * FROM ...
- 263
0
votes
2
answers
2k
views
What is the difference between lock_timeout and deadlock_timeout?
What is the play between lock_timeout and deadlock_timeout ?
What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database ...
- 101
0
votes
2
answers
1k
views
Can a SELECT statement block an ALTER TABLE?
I see in Postgres docs:
Two transactions cannot hold locks of conflicting modes on the same table at the same time
During maintenance (schema migration) we saw cases when we can't ALTER TABLE ...
- 519
3
votes
1
answer
919
views
Causes for pg_class locks except DROP TABLE
I do experience the following situation on a PostgreSQL 11:
A transaction with unknown statements creates an ACCESS SHARE lock on pg_class.
Another connection wants to execute DROP TABLE which causes ...
- 131
0
votes
1
answer
882
views
What blocks a query in Postgres
I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question).
I check the "Server status" in pgAdmin which shows the query "Active". I ...
- 239
3
votes
0
answers
1k
views
SELECT query blocking writes in postgresql
We have a web application that sends emails with a queue worker. Before an email is sent we write to a table:
INSERT INTO user_email (user_id, email_key, sent_at) VALUES (
175413579283955991,
...
33
votes
2
answers
28k
views
Database "frozen" on ALTER TABLE
Our production environment just froze* this morning for a while when altering a table, adding a column actually.
Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];
* ...
- 1,029
3
votes
0
answers
2k
views
SELECT blocking SELECT?
I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
- 352