From: | Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com> |
---|---|
To: | Venkata Balaji N <nag1010(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: avoid lock conflict between SELECT and TRUNCATE |
Date: | 2015-09-11 09:50:07 |
Message-ID: | CAGuFTBXETfa7b4cFxZLf9PjtE974joWapgNPYcqBoCNPY1Ot+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
as there is no option for incremental update/insert on user and renaming
will have app query errors
I guess
1) creating temporary table (say temp_users) on table users with required
data/columns-list and index on column user_id,
...this will be faster as there will be no joins with other tables
2) also need index on table auths_with_trans column user_id
3) replacing users with temp_users in BEGIN block
with this may reduce impact/dependency on regular crontab
Thanks
Sridhar BN
On Fri, Sep 11, 2015 at 10:52 AM, Venkata Balaji N <nag1010(at)gmail(dot)com>
wrote:
>
> On Thu, Sep 10, 2015 at 3:54 AM, Florin Andrei <florin(at)andrei(dot)myip(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andri Möll | 2015-09-11 11:12:53 | Domains, check_violation and undefined column field in error |
Previous Message | Venkata Balaji N | 2015-09-11 05:22:52 | Re: avoid lock conflict between SELECT and TRUNCATE |