Re: [GENERAL] Avoiding deadlocks on mass delete / update
Craig Ringer wrote: Роман Маширов wrote: I've got a simple 'spool' table, one process 'worker' reads and updates this table, other 'stat' performs 'delete ... where ... returning *'. Sometimes I've got dedlocks on delete operation in 'stat', seems like at the moment of expiration of data by timeout some state changes arrived from worker. So the question, is it possible to somehow set order of row deletion in such bulk delete operation, to avoid deadlocks? OK, so for the sake of example, WORKER is UPDATEing rows that stat is trying to DELETE at the same time, such that worker holds a lock on row A and wants a lock on row B, but stat holds B and wants A? In other words, the deadlock is an _interaction_ between 'stat' and 'worker'? yes Can you post the queries? as dumb as possible :) worker parses several thousand events and do update queue set state=$1 where queue_id in (id list) and state in (previous state list) for each target state, so it performs 1-4 update queries. stat do delete from queue where queue_stamp now()-'1day'::interval or state in (terminal state list) returning * The main reason for such thing is to reduce amount of queries to perform, since this queue could work at about 50 objects per second with 4 state changes. One option is to SELECT ... FOR UPDATE NOWAIT before your UPDATE or DELETE. Yep, thank you very much! But, it would be good feature to somehow allow to explicitly set order of multi-row update / delete, or to 'delete but locked', meaning delete all rows by given query parameters, except locked ones. -- MRJ
[GENERAL] Avoiding deadlocks on mass delete / update
I've got a simple 'spool' table, one process 'worker' reads and updates this table, other 'stat' performs 'delete ... where ... returning *'. Sometimes I've got dedlocks on delete operation in 'stat', seems like at the moment of expiration of data by timeout some state changes arrived from worker. So the question, is it possible to somehow set order of row deletion in such bulk delete operation, to avoid deadlocks? Thank you beforehand -- MRJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pasting into psql garbles text
In my case (FreeBSD 6 and 7) psql does not freezes, but for some reason part of pasted code get corrupted when size of the code relatively big. It seems like some timeout used, or buffer overflows: 1. locally with xterm for the first time pasting function of 9K: base= create or replace function football_recalc_match_stat(bigint) returns void as $$ declare here i see rows 1-52 of function without 'base$' prefix where football_match = match and match_event_type in (3, 12)base$ declare base$ match alias for $1; here i see rows 1-117 of function with 'base$' prefix here i see rows 166-till the end of the func with 'base$' prefix and got error in function body at the row 118. 2-10 locally and via ssh to localhost with xterm -- no problem. 11 - to remote host via ssh with xterm base= create or replace function football_recalc_match_stat(bigint) returns void as $$ declare here i see rows 1-52 of function without 'base$' prefix where football_match = match and match_event_type in (3, 12)sovsport$ declare base$ match alias for $1; here i see rows 1-23 of function with 'base$' prefix, last row truncated base$ and that's all 11 stable reproduced for several times with problems on the same rows. uname -a FreeBSD 6.3-RELEASE-p2 FreeBSD 6.3-RELEASE-p2 #0: Wed Sep 3 09:41:48 MSD 2008 i386 set | grep LANG LANG=ru_RU.UTF-8 pkg_info -r postgresql-client-8.2.7 Information for postgresql-client-8.2.7: Depends on: Dependency: libiconv-1.11_1 Dependency: gettext-0.16.1_3 Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: I'm starting to feel like my problems start appearing at a very fixed size (like you, a few hundred or so). Do you see this in other programs (bash, vim, etc)? or only psql? I've only noticed it in psql, but there are not that many other programs that I tend to paste lots of input into. (experiments...) Hmm, and another interesting thing is that it only seems to happen on my HPUX system, which is (intentionally) running a pretty ancient version of readline ... 4.2a looks like. My Fedora 10 box with readline 5.2 eats the same amount of pasted text without indigestion. What readline version are you using? regards, tom lane
[GENERAL] Timezone calculation question
Seems I've missed something important about time zones. On my server i've got local timezone 'W-SU' (Moscow time): = show timezone; TimeZone -- W-SU = select now(); now --- 2009-03-24 13:23:39.655057+03 Till now all seems ok. Than i'm trying to figure out local time at near region: = select now() at time zone 'UTC+4'; timezone 2009-03-24 06:28:30.383373 select now() at time zone 'UTC+3'; timezone 2009-03-24 07:24:11.011075 Seems like zone offset been added twice... Server 8.3.5, OS FreeBSD 7.0-RELEASE Which way should i get correct local time, when i've got timestamp with timezone and name of the target timezone? Thanks beforehand -- MRJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date - null casted to integer?
Ups, sorry, I'm idiot... changes from the default casting to text is
really helpful in clearing brain bugs...
Роман Маширов wrote:
Hi!
Excuse me, if this been discussed before, but following thing seems to
me a little bit strange:
select '2009-01-12'::date - null::date '1 day'::interval;
ERROR: operator does not exist: integer interval
LINE 1: select '2009-11-12'::date - null::date '1 day'::interval;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
select ('2009-11-12'::date - null::date)::interval '1 day'::interval;
ERROR: cannot cast type integer to interval
LINE 1: select ('2009-11-12'::date - null::date)::interval '1 day'...
server 8.3.5 It's really not a problem in queries, but could became a
real pain with plpgsql. I'm not sure, but it seems to me that any
operation with null should product null from the standart's point of
view?
Thank you beforehand
--
MRJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] date - null casted to integer?
Hi!
Excuse me, if this been discussed before, but following thing seems to
me a little bit strange:
select '2009-01-12'::date - null::date '1 day'::interval;
ERROR: operator does not exist: integer interval
LINE 1: select '2009-11-12'::date - null::date '1 day'::interval;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
select ('2009-11-12'::date - null::date)::interval '1 day'::interval;
ERROR: cannot cast type integer to interval
LINE 1: select ('2009-11-12'::date - null::date)::interval '1 day'...
server 8.3.5 It's really not a problem in queries, but could became a
real pain with plpgsql. I'm not sure, but it seems to me that any
operation with null should product null from the standart's point of view?
Thank you beforehand
--
MRJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
