Does RecordSet.Update start a transaction?

Using a Postgres database, I’ve found that RecordSet.Update will not save data unless I call db.Commit afterwards. This implies that rs.Update is starting a transaction but that’s not documented anywhere. Is this intended or a bug?

(I did this in a test application so I know that I was not starting a transaction, and using a Prepared Statement instead works just fine.)

Do you use recordset.edit at the beginning? I think that starts a transaction and commit finishes it.

Edit at the start, Update at the end. Without Commit too, it won’t save to the database.

Any command executed against a Postgres database starts an implicit transaction. And you need to execute Commit() after that. If you don’t the next command will do an implicit commit, but I wouldn’t rely on that.

I don’t think that RecordSet.Edit starts the transaction, Update does, since only on Update the SQL command can be constructed.

I think you’re right about Update, but as for a transaction, that doesn’t seem to be the case elsewhere. For example, I can use db.Insert without calling Commit, and I can use a Prepared Statement without issue.

This is starting to feel like a bug, but I’d like confirmation from one of the engineers.

PostgreSQL 8.3.23 Documentation: START TRANSACTION

“PostgreSQL’s behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called “autocommit”. Other relational database systems might offer an autocommit feature as a convenience.”

Since I’m not starting a transaction, this page suggests that it should auto-commit. Since it doesn’t, I’ll file a bug report.


The sentece ahead of what you are citing says: [quote]it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block.[/quote] So I’d say it is not a bug.

Right, it implicitly starts a transaction block, so if you write “statement1 ; statement2 ; statement3”, and statement3 fails, I’d expect all the statements to be rolled back. But the block I quoted is quite clear. If I didn’t start a transaction, the implicit transaction is committed immediately after execution.

Anyway, I’ll report it and let the engineers decide.

Is your database set to turn auto commit off ?

Never mind - update IS starting a transaction

Only if YOU start a transaction. If not, Postgres does

[code]BEGIN TRANSACTION // implicit
COMMIT // implicitly executed by statement2

COMMIT // implicitly executed by statement3

…and only statement3 would rollback.

Thanks Norman.