Thread: Open a Transaction

Open a Transaction

From
"Ezequias Rodrigues da Rocha"
Date:
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/


Re: Open a Transaction

From
Joe
Date:
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



Re: Open a Transaction

From
"Ezequias Rodrigues da Rocha"
Date:
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

2007/2/8, Joe <dev@freedomcircle.net >:
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




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  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/

Re: Open a Transaction

From
Roberto Fichera
Date:
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. 



Re: Open a Transaction

From
Andrew Sullivan
Date:
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


Re: Open a Transaction

From
Tom Lane
Date:
"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


Re: Open a Transaction

From
"Ezequias Rodrigues da Rocha"
Date:
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

2007/2/8, Andrew Sullivan <ajs@crankycanuck.ca>:
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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  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/