Re: [GENERAL] Long update query ? - Mailing list pgsql-general

From David Hartwig
Subject Re: [GENERAL] Long update query ?
Date
Msg-id 360F8D43.8CF53472@insightdist.com
Whole thread Raw
In response to Long update query ?  ("Sergei Chernev" <ser@nsu.ru>)
Responses RE: [GENERAL] Long update query ?
List pgsql-general
This is caused by a semi-well known weakness in the optimizer.  The optimizer
rewrites the WHERE clause in conjunctive normal form (CNF):

   (A and B) or (C and D) ==>  (A or C) and (A or D) and (B or C) and (B or D)

Try this with your statement and you will see the expression explodes.   Foe
now,  I would suggest that you break this up into multiple statements.

Sergei Chernev wrote:

> Hello,
> I have query:
> UPDATE userd_session_stat SET status =1 WHERE status=0 AND ((uid <>627 AND
> tty <>'ttyA03') OR (uid <> 425 AND tty <> 'ttyA05') OR (uid <> 8011 AND tty
> <> 'ttyA09') OR (uid <> 2092 AND tty <> 'ttyA0f') OR (uid <> 249 AND tty <>
> 'ttyp3') OR (uid <> 249 AND tty <> 'ttyp4') OR (uid <> 249 AND tty <>
> 'ttyp5') OR (uid <> 249 AND tty <> 'ttyp6'))
>
> But, postgres complains that:
> FATAL 1:  palloc failure: memory exhausted
>
>  I see, the query must be less than 4kB, and this query is less.
> Long SELECT queries works fine.
> Have any idea? Maybe, I have to change postmaster's settings ? Query
> executes from libpg programm.




pgsql-general by date:

Previous
From: "Jackson, DeJuan"
Date:
Subject: RE: [GENERAL] Long update query ?
Next
From: "Taral"
Date:
Subject: RE: [GENERAL] Long update query ?