Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction within plpgsql

Transaction within plpgsql

From
"Walker, Jed S"
Date:

I am coding a procedure to purge data from a table. I want to commit the data I'm purging at intervals (for example, every 1000 rows). The problem I'm encountering is that when I have

begin transaction;
commit;

statements in my plpgsql function it doesn't compile.

How can you explicitly create transactions within a plpgsql function?

Thanks,

Jed.

Re: Transaction within plpgsql

From
Bruce Momjian
Date:
Walker, Jed S wrote:
> I am coding a procedure to purge data from a table. I want to commit the
> data I'm purging at intervals (for example, every 1000 rows). The
> problem I'm encountering is that when I have
>
> begin transaction;
> commit;
>
> statements in my plpgsql function it doesn't compile.
>
> How can you explicitly create transactions within a plpgsql function?

No, you can not, sorry, but you might as well just do the entire thing
in the single transaction of the function.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Transaction within plpgsql

From
Kretschmer Andreas
Date:
Bruce Momjian <pgman@candle.pha.pa.us> schrieb:

> Walker, Jed S wrote:
> > I am coding a procedure to purge data from a table. I want to commit the
> > data I'm purging at intervals (for example, every 1000 rows). The
> > problem I'm encountering is that when I have
> >
> > begin transaction;
> > commit;
> >
> > statements in my plpgsql function it doesn't compile.
> >
> > How can you explicitly create transactions within a plpgsql function?
>
> No, you can not, sorry, but you might as well just do the entire thing
> in the single transaction of the function.

Hi Bruce,

I think, he can use savepoints within the procedure.
http://www.postgresql.org/docs/8.0/static/sql-savepoint.html
But, the whole procedure is one transaction.

I'm right?


Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Transaction within plpgsql

From
Bruce Momjian
Date:
Kretschmer Andreas wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> schrieb:
>
> > Walker, Jed S wrote:
> > > I am coding a procedure to purge data from a table. I want to commit the
> > > data I'm purging at intervals (for example, every 1000 rows). The
> > > problem I'm encountering is that when I have
> > >
> > > begin transaction;
> > > commit;
> > >
> > > statements in my plpgsql function it doesn't compile.
> > >
> > > How can you explicitly create transactions within a plpgsql function?
> >
> > No, you can not, sorry, but you might as well just do the entire thing
> > in the single transaction of the function.
>
> Hi Bruce,
>
> I think, he can use savepoints within the procedure.
> http://www.postgresql.org/docs/8.0/static/sql-savepoint.html
> But, the whole procedure is one transaction.
>
> I'm right?

Right, he can use savepoints, for sure.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073