yesterday, tom lane committed another great feature. actually it's so great, that i really wonder how could i live without it earlier.
patch came from itagaki takahiro, and was heavily modified by tom. what it does?
according to commit log:
Log Message: ----------- Report the current queries of all backends involved in a deadlock (if they'd be visible to the current user in pg_stat_activity). This might look like it's subject to race conditions, but it's actually pretty safe because at the time DeadLockReport() is constructing the report, we haven't yet aborted our transaction and so we can expect that everyone else involved in the deadlock is still blocked on some lock. (There are corner cases where that might not be true, such as a statement timeout triggering in another backend before we finish reporting; but at worst we'd report a misleading activity string, so it seems acceptable considering the usefulness of reporting the queries.) Original patch by Itagaki Takahiro, heavily modified by me.
now. how does it look in real-life usage scenario?
consider this table:
# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
x | text |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)with this content:
# SELECT * FROM test; id | x ----+--- 1 | a 2 | b (2 ROWS)
now, we have 2 sessions which try to modify the table at the same time:
| step | session 1 | session 2 |
|---|---|---|
| 1. | begin; | |
| 2. | begin; | |
| 3. | update test set x = ‘c' where x = ‘a'; | |
| 4. | update test set x = ‘d' where x = ‘b'; | |
| 5. | update test set x = ‘f' where x = ‘b'; | |
| 6. | update test set x = ‘g' where x = ‘a'; |
session 1, after getting command (update) will hang, waiting for lock.
but when i enter final, 6th command in session 2, i will get this output:
ERROR: deadlock detected DETAIL: Process 13370 waits for ShareLock on transaction 5301136; blocked by process 13284. Process 13284 waits for ShareLock on transaction 5301137; blocked by process 13370.
now. it certainly has some level of details, but not really helpful details.
but with this new patch, situation looks differently:
ERROR: deadlock detected DETAIL: Process 26283 waits FOR ShareLock ON TRANSACTION 1648; blocked BY process 26275. Process 26275 waits FOR ShareLock ON TRANSACTION 1649; blocked BY process 26283. CONTEXT: Process 26283: UPDATE test SET x = 'g' WHERE x = 'a'; Process 26275: UPDATE test SET x = 'f' WHERE x = 'b';
now the error message shows both queries. these queries are not actually locking each other, but having them together will definitely help you pinpoint the part of your code which generates deadlocks.
another great feature 🙂
Is it possible to apply this patch to 8.3 branch? I can’t wait a year to fix the deadlocks occuring in my app under heavy load…
possibly – yes. patch is available – you can try to patch it yourself.
as for backporting change to official 8.3 – i highly doubt. this is “new functionality”, and 8.3 is in state that only bugfixes will be added.
Another great feature missing
If you have a column with duplicated values and you want to create an UNIQUE Index, PostgreSQL sends a message:
ERROR: could not create unique index “pk_table_id”
SQL state: 23505
DETAIL: Table contains duplicated values.
The message should show the duplicated value, like below message:
ERROR: could not create unique index “pk_table_id”
SQL state: 23505
DETAIL: Table contains duplicated values. Column (id) Value: 2987100 duplicated.
So, If you leave only one value of the duplicated, the Index will be created. If there are another value duplicated the same message should appear again whith the new value.
ERROR: could not create unique index “pk_table_id”
SQL state: 23505
DETAIL: Table contains duplicated values. Column (id) Value: 2987200 duplicated.
Maybe you can tell to Tom Lane to add the new error message in 8.4
@Hugo:
i can’t tell tom anything 🙂 i’m not his boss. i could mail to pgsql-general, but so can you.
on the other hand – i think that such a patch would be actually counter-productive. it would show only one value that is non-unique, while there might be many.
on the other hand – there is perfectly simple query that shows *all* duplicated values:
select id, count(*) from table group by id having count(*) > 1;
so basically – you can easily get more information than this patch could provide. and by doing it in a simple function you can have it reduced to simple:
select * from show_duplicates(‘table’, ‘field’);