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’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.
Thanks.
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
statement1
COMMIT // implicitly executed by statement2
BEGIN TRANSACTION // implicit
statement2
COMMIT // implicitly executed by statement3
BEGIN TRANS…
[/code]
…and only statement3 would rollback.