Обсуждение: Open a Transaction

Hi list,

Could someone tell me what is wrong on this statement ?

Start Transaction
delete from base.something
where
id in(
41503,
41504,
41505,
41506,
41507,
41508,
41509,
41510,
41511,
41512,
41513,
41514,
41515,
41516,
41517,
41518,
41519,
41520,
41521,
41522,
41523,
41524,
41525,
41526,
41527,
41528,
)
end;
Commit;

-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=                                 Atenciosamente
(Sincerely)                      Ezequias Rodrigues da
Rocha=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Hi Ezequias,

On Thu, 2007-02-08 at 14:50 -0200, Ezequias Rodrigues da Rocha wrote:
> Hi list,
> 
> Could someone tell me what is wrong on this statement ?
> 
> Start Transaction

The above should read 

begin;

> delete from base.something
> where
> id in(
> 41503,
> 41504,
> 41505,
> 41506,
> 41507,
> 41508,
> 41509,
> 41510,
> 41511,
> 41512,
> 41513,
> 41514,
> 41515,
> 41516,
> 41517,
> 41518,
> 41519,
> 41520,
> 41521,
> 41522,
> 41523,
> 41524,
> 41525,
> 41526,
> 41527,
> 41528,

Remove the extra comma.

> )
> end;

Remove 'end'.

> Commit;

Joe

Now the sql is OK but now I have the following error:

ERROR: stack depth limit exceeded
SQL state: 54001
Hint: Increase the configuration parameter "max_stack_depth".

In the previous e-mail I hide the numbers of itens of my set (in(234,12332,1232,....) actually I have more than 36000 subsets of my IN statement.

:\

Any suggestion instead of change my max_stack_depth ?

Regards
Ezequias




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

At 17.50 08/02/2007, Ezequias Rodrigues da Rocha wrote:
>Hi list,
>
>Could someone tell me what is wrong on this statement ?
>
>Start Transaction
>delete from base.something
>where
>id in(
>41503,
>41504,
>41505,
>41506,
>41507,
>41508,
>41509,
>41510,
>41511,
>41512,
>41513,
>41514,
>41515,
>41516,
>41517,
>41518,
>41519,
>41520,
>41521,
>41522,
>41523,
>41524,
>41525,
>41526,
>41527,
>41528,     ^
this comma I guess ;-)!

>)
>end;
>Commit;
>
>-- 
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>                                  Atenciosamente (Sincerely)
>                        Ezequias Rodrigues da Rocha
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>A pior das democracias ainda é melhor do que a melhor das ditaduras
>The worst of democracies is still better than the better of dictatorships
>http://ezequiasrocha.blogspot.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>

Roberto Fichera. 

On Thu, Feb 08, 2007 at 03:13:16PM -0200, Ezequias Rodrigues da Rocha wrote:
> 
> Any suggestion instead of change my max_stack_depth ?

Well, I suppose you could put the numbers in a temp table an NOT IN
on that.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.    --Philip Greenspun

"Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> writes:
> Now the sql is OK but now I have the following error:

> ERROR: stack depth limit exceeded
> SQL state: 54001
> Hint: Increase the configuration parameter "max_stack_depth".

> In the previous e-mail I hide the numbers of itens of my set
> (in(234,12332,1232,....) actually I have more than 36000 subsets of my IN
> statement.

That's probably well past the point at which you should expect IN (list)
to give reasonable performance.  Instead consider putting the values
into a temp table and writing a join or IN (subselect) against the temp table.
        regards, tom lane

I increase the "max_stack_depth" and the In statemen run OK.

I don't know if it is good to put this variable as big as possible or as the manual report (using ulimit -s) to put the larger stack capacity of Operational System.

Ezequias




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/