Re: avoid lock conflict between SELECT and TRUNCATE - Mailing list pgsql-general

On Thu, Sep 10, 2015 at 3:54 AM, Florin Andrei <florin@andrei.myip.org> wrote:

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.

Yes. Renaming the table would interrupt the ongoing SELECT. The best approach would be (if possible) to isolate the SELECT itself. You can consider renaming the "users" table before the SELECT starts (say renamed to users_orig) and then execute the SELECT on user_orig table and drop it (if the data is no more needed) after the SELECT finishes. Instead of TRUNCATE, you can consider re-creating the "users" table and populating the data. If you take this approach, you will need to be careful regarding privileges/grants and dependencies on the table.

Or the second approach would be --

Create a table called users_orig from the "users" table and execute SELECT on user_orig table and let the TRUNCATE/data-repopulation operation run on "users" table. This will be a problem if the data is huge. It might take up your hardware resources.

Third and simple approach would be to -

Execute SELECT and TRUNCATE at different times.

All of the above approaches are without considering data-size and other critical aspects of environment, which you need to worry about.

Regards,

Venkata Balaji N

Fujitsu Australia