From: | Tom Allison <tom(at)tacocat(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: DeadLocks..., DeadLocks... |
Date: | 2007-06-16 03:17:37 |
Message-ID: | 46735651.9020200@tacocat.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Gregory Stark wrote:
> "Tom Allison" <tom(at)tacocat(dot)net> writes:
>
>> The other approach would be to use an external file to queue these updates and
>> run them from a crontab. Something like:
> ....
>> and then run a job daily to read all these in to a hash (to make them unique
>> values) and then run one SQL statement at the end of the day.
>
> Well probably better to keep it in the database. The database also knows how
> to use hashes to get distinct values too.
>
> So if you have a "history" table which records ids with dates and then do a
> transaction like:
>
> BEGIN;
> DELETE FROM tokens WHERE id NOT IN (select id from history);
> DELETE from history WHERE seen < now()-'3 days'::interval;
> END;
>
> This could still deadlock so it may make sense for it to do it in a
> transaction and add LOCK TABLE statements to lock the tables which refer to
> the tokens table.
>
I ended up with two steps to the solution.
First, I do handle the deadlock errors with a sleep/redo loop.
I add a bit more time with each sleep so eventually everything slows down so
much it can't deadlock.
Second, the offending SQL was to UPDATE the table that was the target of a
Foreign Key constraint. I modified the SQL from:
update tokens set last_seen = now() where token_idx in (...)
to:
update tokens set last_seen = now() where
token_idx in (...) and last_seen < current_date;
Since this only happens when things are running at full...
Previously I could deadlock on 60 emails.
Now I can't deadlock on 8000.
I would venture to say the problem is effectively fixed.
I have a question though.
I noticed a particular format for identifying dates like:
now()-'3 days'::interval;
What's '::interval' and why should I use it?
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Heusser | 2007-06-16 11:26:57 | Dynamic Log tigger (plpgsql) |
Previous Message | Francisco Reyes | 2007-06-16 02:07:00 | Re: pg_restore out of memory |