Thread: handling transactions from C (libpq)

Hello!

I am working on a program that would eventually permit querying,
inserting, deleting and modifying some data stored in a table.
(I use postgresql 6.5.3, g++, ncurses, libpq on Debian GNU/Linux).

The program generates Sql commands according to the
user's actions ( insert into .. , delete from .. , etc. ) and sends
them for execution to the postgresql server with PQexec().

I want the user to be able to choose between commiting and
rollbacking the changes he/she has made to the table.
Therefore:
1. when the user presses KEY_END,  I execute: PQexec(conn,"commit")
2. when the user presses KEY_HOME, I execute: PQexec(conn,"rollback")


Here comes the problem:
Imagine the user has inserted 5 new records. He enters the 6th record
but makes a mistake ( for example enters a bad date format ).
The program generates an INSERT command and executes it with PQexec.
An error is generated and the program prints it on the screen.

Because of the error, the transaction is compromised and has to be
aborted, and the user has to enter the first 5 records... again...
Very annoying.

The program should report the error and let the user correct the
typing mistake, without affecting the other records (although
there is only one transaction).
But, I don't know how to achieve this.

(I've noticed that even in psql if I enter an erroneous insert
command, the transaction is left in an unusual state , and queries
are no longer executed until "rollback" or "abort" is entered).

Please tell me, is there a way to abort only the last command of
a transaction?  Or, is it possible to use "subtransactions"
inside a transaction?

Thanks in advance for any hint or idea.

Adrian Maier
(am@fx.ro)

On Fri, Aug 09, 2002 at 05:35:00PM +0300, am@fx.ro wrote:
> Please tell me, is there a way to abort only the last command of
> a transaction?  Or, is it possible to use "subtransactions"
> inside a transaction?

No, at least not yet.

By the way, the version of Postgres you're using is well and truly
ancient, and should probably be upgraded.  Given that you're using
Debian, just upgrading tot he latest -stable should do the trick for
you.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

am@fx.ro a écrit:
...
>
> I want the user to be able to choose between commiting and
> rollbacking the changes he/she has made to the table.
> Therefore:
> 1. when the user presses KEY_END,  I execute: PQexec(conn,"commit")
> 2. when the user presses KEY_HOME, I execute: PQexec(conn,"rollback")
>
>
> Here comes the problem:
> Imagine the user has inserted 5 new records. He enters the 6th record
> but makes a mistake ( for example enters a bad date format ).
> The program generates an INSERT command and executes it with PQexec.
> An error is generated and the program prints it on the screen.
>
> Because of the error, the transaction is compromised and has to be
> aborted, and the user has to enter the first 5 records... again...
> Very annoying.
> The program should report the error and let the user correct the
> typing mistake, without affecting the other records (although
> there is only one transaction).
> But, I don't know how to achieve this.

your client should keep the records in a temporary area (table or local structure)
or "a pre-commit table",
and restore the records to the screen if an error occurs.

transaction is not an undo.

> (I've noticed that even in psql if I enter an erroneous insert
> command, the transaction is left in an unusual state , and queries
> are no longer executed until "rollback" or "abort" is entered).
> Please tell me, is there a way to abort only the last command of
> a transaction?  Or, is it possible to use "subtransactions"
> inside a transaction?

As far as I know, subtransactions are not supported