PGDAC autocommit?

[durumdara]

Hi!

D6, PGSQL9.0, PGDAC last demo.

I came from Zeos. In Zeos there is two mode I have in connect: AutoCommit On or Off.
AC property fully do that PGSQL do with AC. So it is understandable.

But I don't understand PGDAC in this theme, and the help don't containing word "AutoCOmmit"...

First: very strange that AC property is on pgQuery and pgTable, and not on pgConnection!

Because this meaning that we don't know in which transaction context we are...
In FireBird/IB world the Connection have a transaction, or an other class handle the transactions (TIBTransaction).

The next interesting thing is pgTable.
When I used AutoCOmmit = True on it, the data saved permanently to the server. It is ok.
But when I set it to False, IT IS ALSO DO IT!

And this is out of the pgConnection's transaction handling, because when I tried to rollback on it, I got error (there is not active transaction)...

Only I got good result when I set to CachedUpdates, and use StartTR, Edti, Post, ApplyUpdates, Commit...

The help is unuseful, because it is don't containing info about AutoCommit...

The pgDAC version is 2.10.0.5.

Thanks for your informations!

Regards:
dd

[bork]

Hello

We plan to make similar interface for all DAC products (PgDAC, ODAC, IbDAC, etc). So we added the AutoCommit property but it wasn't fully implemented. Therefore the AutoCommit property is not described in the help.

If you want to want to post data to the server, you should use the following code:

Code: Select all

  PgConnection1.StartTransaction;
  PgQuery1.Delete;

If you want to commit changes, you should execute:

Code: Select all

  PgConnection1.Commit;

If you want rollback changes, you should execute:

Code: Select all

  PgConnection1.Rollback;

Sometimes AutoCommit is more convenient for use then explicit start transaction (PgConnection1.StartTransaction), therefore we plan implement this functionality.

P.S.
I checked the AutoCommit property in the ZeosLib 6.6.6:

Code: Select all

  ZConnection1.AutoCommit := False;
  ZConnection1.Connect;
  ZQuery1.Open;
  ZQuery1.Delete;
  ZConnection1.Rollback;
  ZQuery1.Refresh;

And it doesn't work: deleted record wasn't restored after calling Rollback. As for me, the behavior in ZeosLib is not understandable. Can you explain behavior of the AutoCommit property in ZeosLib to me.

[durumdara]

Hello!

Hello

We plan to make similar interface for all DAC products (PgDAC, ODAC, IbDAC, etc). So we added the AutoCommit property but it wasn't fully implemented. Therefore the AutoCommit property is not described in the help.

If you want to want to post data to the server, you should use the following code:

Code: Select all

  PgConnection1.StartTransaction;
  PgQuery1.Delete;

If you want to commit changes, you should execute:

Code: Select all

  PgConnection1.Commit;

If you want rollback changes, you should execute:

Code: Select all

  PgConnection1.Rollback;

Sometimes AutoCommit is more convenient for use then explicit start transaction (PgConnection1.StartTransaction), therefore we plan implement this functionality.

So if I understand you fully, all of the changes I did with a pgDACDataSet, is embedded with:

Code: Select all

DataSet.Connection.StartTR;
DoChange(Edit, Insert, Delete);
DataSet.Connection.Commit;

Two questions then:

1.)
What happens with Opened transactions if I make some thing on DataSet have AutoCommit = True
For example:

Code: Select all

pgC.StartTR
aQuery.ExecSQL('delete from anytable where ...');
...
anAutoCommitTable.Post;

You make exception, or silently Rollback/Commit?

2.)
Why a Table do persistent modification with AutomCommit = False + CachedUpdates = False?
This mode MUST drop all modifications on Rollback.

P.S.
I checked the AutoCommit property in the ZeosLib 6.6.6:

Code: Select all

  ZConnection1.AutoCommit := False;
  ZConnection1.Connect;
  ZQuery1.Open;
  ZQuery1.Delete;
  ZConnection1.Rollback;
  ZQuery1.Refresh;

And it doesn't work: deleted record wasn't restored after calling Rollback. As for me, the behavior in ZeosLib is not understandable. Can you explain behavior of the AutoCommit property in ZeosLib to me.

The problem that you don't set ZConnection.Isolation to Repeatable Read, or Read Committed.
If none of isolation that is meaning "anybody can see the modifications", and Zeos converts this to "AutoCommit".

Try this, and you can see that Rollback revert the modifications. In my machine it did this.

Thanks:
dd

[bork]

Hello,

I will try to explain difference in PgDAC and ZeosLib behavior.

If TransactionIsolationLevel tiNone, then ZeosLib implicitly starts transaction (you can see it in the TZPostgreSQLConnection.StartTransactionSupport method of the ZDbcPostgreSql.pas unit). PgDAC doesn't implicitly start any transactions. And any changes that were made outside transaction are embedded automatically.

So in PgDAC Code 1 and Code 2 give the same result:
Code 1:

Code: Select all

DataSet.Edit;
...
DataSet.Post; // changes are embedded

Code 2:

Code: Select all

Connection.StartTransaction;
DataSet.Edit;
...
DataSet.Post;
Connection.Commit; // changes are embedded

You will get difference if you try to do several changes:
Code 1:

Code: Select all

DataSet.Edit;
...
DataSet.Post; // first changes are embedded
DataSet.Edit;
...
DataSet.Post; // second changes are embedded 

Code 2:

Code: Select all

Connection.StartTransaction;
DataSet.Edit;
...
DataSet.Post; 
DataSet.Edit;
...
DataSet.Post; 
Connection.Commit; // all changes are embedded

Q1:

As I wrote before, AutoCommit is not fully implemented yet, and you will get the same result for anAutoCommitTable.AutoCommit = true and anAutoCommitTable.AutoCommit = false: in your example data will not be embedded after calling the Post method.

Q2:
It is easier to show several samples:

Rollback changes:

Code: Select all

PgConnection1.StartTransaction;
PgQuery1.Close;
PgQuery1.CachedUpdates := true;
PgQuery1.Open;
PgQuery1.Delete;
PgQuery1.ApplyUpdates;        
PgConnection1.Rollback; // all changes are canceled
PgQuery1.Refresh;

Commit changes:
Rollback changes:

Code: Select all

PgConnection1.StartTransaction;
PgQuery1.Close;
PgQuery1.CachedUpdates := true;
PgQuery1.Open;
PgQuery1.Delete;
PgQuery1.ApplyUpdates;        
PgConnection1.Commit; // all changes are embedded 
PgQuery1.Refresh;

We plan to implement the AutoCommit property support in one of the next builds/versions of PgDAC.