Postgres Pro
Downloads
Home   >   mailing lists

Thread: A very simple question about rollback/commit

A very simple question about rollback/commit

From
"Della-Croce, Gregory"
Date:

I am new to working with PostgreSQL and need to create a pgSQL process that I can run against a 8.4 PostgreSQL database.   The code will need to look something like:

BEGIN;

SAVEPOINT sp1;

UPDATE location SET delete = 1 WHERE delete=0 RETRUNING count;

[code to rollback if there is an error or commit if everything was good]

 

It is that code for knowing that there was an error and doing the Rollback that I can’t remember how to do.  I looked through the PostgreSQL Documentation for an example but I couldn’t find it.   It has been a very long time since I wrote SQL code.

 

Oh, another small question, since this is not being embedded in an program like C#, VB, etc, the two tool I have are pgADMIN III and SSH.   Can this be done in pgADMIN III?

 

Thanks to those that will help me with a simple solution.

 

Greg Della-Croce

Applications Administrator

 

Greg_Della-Croce@Wycliffe.org

SKYPE: GDellaCroce55

Cell: 407-408-2572

                                                                   THINK ON THIS:

"All that is valuable in human society depends upon the opportunity for development accorded the individual" ~ Albert Einstein

“All this is valuable in Christ’s Kingdom depens upon the opportunity for development accorded the individual by the Holy Spirit to Glorify the Father”  ~ Greg Della-Croce

 

Re: A very simple question about rollback/commit

From
Jeff Davis
Date:
On Mon, 2012-07-09 at 11:09 -0400, Della-Croce, Gregory wrote:
> I am new to working with PostgreSQL and need to create a pgSQL process
> that I can run against a 8.4 PostgreSQL database.   The code will need
> to look something like:
>
> BEGIN;
>
> SAVEPOINT sp1;
>
> UPDATE location SET delete = 1 WHERE delete=0 RETRUNING count;
>
> [code to rollback if there is an error or commit if everything was
> good]

...

> Oh, another small question, since this is not being embedded in an
> program like C#, VB, etc, the two tool I have are pgADMIN III and SSH.
> Can this be done in pgADMIN III?

Because you are using 8.4, you will need to define a function, and I
recommend PL/pgSQL:

http://www.postgresql.org/docs/8.4/static/sql-createfunction.html
http://www.postgresql.org/docs/8.4/static/plpgsql.html

In particular, look at:

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

That last one will tell you about the BEGIN/EXCEPTION block, which is I
think what you want. Internally, that uses savepoints so you don't have
to. (In fact, it might be impossible to use savepoints in PL/pgSQL, and
you might have to use the BEGIN/EXCEPTION block).

If PL/pgSQL is not already installed, you may need to create it in your
database first:

http://www.postgresql.org/docs/8.4/static/app-createlang.html
or
http://www.postgresql.org/docs/8.4/static/sql-createlanguage.html

If you use later versions of postgresql, then PL/pgSQL is installed by
default, and you can use the a "DO" block rather than creating a
function:

http://www.postgresql.org/docs/9.1/static/sql-do.html

I know that's a lot of information all at once, but I hope it's helpful.

Regards,
    Jeff Davis