Re: handling transactions from C (libpq)

am(at)fx(dot)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