Once in a while, I have a report running a complex query such as this:
BEGIN;declare "SQL_CUR0000000004919850" cursor with hold for SELECT
"auths_with_trans"."user_id" AS "user_id (auths_with_trans)",
MAX("auths_with_trans"."user_created") AS
"TEMP(attr:user_created:ok)(2099950671)(0)",
MIN("auths_with_trans"."user_created") AS
"TEMP(attr:user_created:ok)(99676510)(0)",
MIN("auths_with_trans"."trans_time") AS
"usr:Calculation_6930907163324031:ok",
MIN("auths_with_trans"."auth_created") AS
"usr:Calculation_9410907163052141:ok"
FROM "public"."users" "users"
LEFT JOIN "public"."auths_with_trans" "auths_with_trans" ON
("users"."user_id" = "auths_with_trans"."user_id")
GROUP BY 1;fetch 100 in "SQL_CUR0000000004919850"
But it takes a long time to complete, and meanwhile a cron job tries to
rebuild the users table by first doing "TRUNCATE TABLE users" and then
repopulating it with data. Obviously, TRUNCATE is blocked until the long
SELECT finishes.
I'm looking for ways to avoid the conflict. One way would be to do
incremental updates to the users table - that's not an option yet.
What if I rename the users table to users_YYYYMMDD? Would that still be
blocked by SELECT? If it's not blocked, then I could rename users out of
the way, and then recreate it with fresh data as plain 'users'. Then I'd
have a cron job dropping old users tables when they get too old.
--
Florin Andrei
http://florin.myip.org/