SQLDB Postgresql Transactions Issue

[tionov]

Hello everybody,

I'm trying to use Lazarus to develop a multiuser client application for a Postgresql database. I am a slightly     somewhat experienced PG developer. So I am experimenting with the various approaches to connect to PG, one is SQLdb. Here I see an issue, which makes it IMO impossible to use SQLdb in a multiuser environment.

As I see, I have to connect to db via TPQConnection and then use TSQLTransaction together with TSQLQuery and TDataSource to query some rows of a table via SQL and show them in a Form. Even when I choose Action->caNone in the definition of the transaction, the query that is created and sent to Postgresql starts with "BEGIN;" (I see it in the log of PG). This transaction remains open, until I close the form, which started the query, then a "ROLLBACK;" is sent to the Database.

To understand, why this is an issue, one has to understand the way, PG handles transactions. Normally, you connect to PG at start of the application and then send a couple of queries to fill your form with the received data. None of them needs to be enclosed in "BEGIN;...ROLLBACK;" because transactions in PG are only used in one special case:

To combine a number of of queries (they can do all sort of CRUD), which are executed (COMMIT) or not (ROLLBACK) as an atomic(!) operation.

Therefor PG allows only one transaction at a time.  And this should occur only for a period as short as possible.

But if one uses SQLdb to fill a form, this one possible transaction is opened and will not be closed until the end of the "living" of the form. As a programmer you are not able to use transactions in the normal way, PG needs it. And even worse: With an opened transaction for the whole time of living of the form one prevents other users to change the same data, which is queried by the form.

So for me SQLdb seems unusable with Postgresql in a multiuser env.

In short: The error is, that in TSQLTransaction the Action caNone has not the expected behavior to prevent "BEGIN;" and "ROLLBACK;".

Thank you.

[taazz]

That is true SQLDB was designed to always use a transaction and can' t be used outside one.

[tionov]

Ok, thanks, but then SQLdb should not been advertised as a possible solution for connecting to PG in the FPC wiki. Or a warning cold prevent new users wasting time trying out SQLdb for multiuser environments. I needed at least one day to find this out.

[taazz]

I don't think that multi user means what you think it means. To lock the data that are open on an other user until that user releases them is one of the many models you will find out there for multi user environments, some of the others are
  1) Decline all changes a user made if the data that he started editing have changed and force him to start over   
  2) Serialize the changes one after an other and the last usually defines the data that will be kept.

PostgreSQL obviously can support all of the well known ones it is probably you that has decided that your model will be the one that serializes the changes, that does not make SQLDB unsuitable for multi user environments nor incapable to connecto to SQLDB. It makes it a pure choice only if there are  other libraries that can do more than SQLDB including what SQLDB can do otherwise its simple a different model from what you want to use.


At this point I have to say that I'm only a user of lazarus and fpc, I'm not part of the support or development team and what ever you read above is my personal opinion only.
Having said that I do agree with you that SQLDB is by far not complete and needs to be extended. Do you care to contribute?

[marcov]

To understand, why this is an issue, one has to understand the way, PG handles transactions. Normally, you connect to PG at start of the application and then send a couple of queries to fill your form with the received data. None of them needs to be enclosed in "BEGIN;...ROLLBACK;" because transactions in PG are only used in one special case:

To combine a number of of queries (they can do all of CRUD), which are executed (COMMIT) or not (ROLLBACK) as an atomic(!) operation.

And while this might be acceptable for manual SQL use, and much more difficult for components, where the grid doesn't know on query time if a mutation is going to follow.

Therefor PG allows only one transaction at a time.  And this should occur only for a period as short as possible.

I don't see the "therefore" logic there, but fact is that PG stands alone in this pretty much. Probably some form of connection pooling needs to be implemented to circumvent this.

I'm actually considering migrating to Firebird because of this. (I originally started out with PG because firebird was not portable, now it is)

   

[tionov]

I don't think that multi user means what you think it means. To lock the data that are open on an other user until that user releases them is one of the many models you will find out there for multi user environments, some of the others are
  1) Decline all changes a user made if the data that he started editing have changed and force him to start over   
  2) Serialize the changes one after an other and the last usually defines the data that will be kept.

PostgreSQL obviously can support all of the well known ones it is probably you that has decided that your model will be the one that serializes the changes, that does not make SQLDB unsuitable for multi user environments nor incapable to connecto to SQLDB. It makes it a pure choice only if there are  other libraries that can do more than SQLDB including what SQLDB can do otherwise its simple a different model from what you want to use.

I now see that Zeoslib is able to query without transaction and I will use that because in my application editing of certain tuples is locked by the application itself (the application reads and writes a common lock-table), not by the db. Inserts, updates and deletes are inside explicit declared transactions, as far as they use more than one query at once. That is a design choice, I agree.

BTW Postgresql does autocommit for single queries by default.

Having said that I do agree with you that SQLDB is by far not complete and needs to be extended. Do you care to contribute?

Sorry, I can't do that. Maybe later, unfortunately I am a FPC and Lazarus beginner ...

[tionov]

Therefor PG allows only one transaction at a time.  And this should occur only for a period as short as possible.

I don't see the "therefore" logic there, but fact is that PG stands alone in this pretty much. Probably some form of connection pooling needs to be implemented to circumvent this.

I'm actually considering migrating to Firebird because of this. (I originally started out with PG because firebird was not portable, now it is)

Ahm I meant "Therefor PG allows only one transaction at a time per connection". I am working with PG for about 15 years and now I am planning to replace the current MS Access frontend (a fat client) with a Lazarus program. Until now I write SQL queries by hand and I want to do this in future too. Let this do ORM models makes me feel uncomfortable, when I watch the generated statements...

... IMO it is nice for querying and displaying data, but INSERT, UPDATE especially in joined tables I do by hand (in code).

PG is a database which fulfils SQL Standards pretty well:
http://www.postgresql.org/docs/9.3/static/features.html

Until now I never had problems with stability or missing features.

[mse]

MSEgui version of SQLDB has ttransaction.options tao_fake which can be used for DB's which support implicit transactions. Another problem with Postgres is that transactions are aborted in case of an error. The tmsepqconnection option pqco_usesavepoint automatically recovers by use of savepoints. tmsesqlquery has the option to disconnect from DB after fetching the data and to reconnect in order to post the deltas.

[taazz]

Therefor PG allows only one transaction at a time.  And this should occur only for a period as short as possible.

I don't see the "therefore" logic there, but fact is that PG stands alone in this pretty much. Probably some form of connection pooling needs to be implemented to circumvent this.

I'm actually considering migrating to Firebird because of this. (I originally started out with PG because firebird was not portable, now it is)

Ahm I meant "Therefor PG allows only one transaction at a time per connection".

No that is incorrect. You don't have to use a single transaction for everything, you can always use mutilple transaction components and as far as I know those are not nested.

[tionov]

Ahm I meant "Therefor PG allows only one transaction at a time per connection".

No that is incorrect. You don't have to use a single transaction for everything, you can always use mutilple transaction components and as far as I know those are not nested.

I  meant:

BEGIN;
INSERT INTO y ...;
DELETE FROM z WHERE ...;
UPDATE z ...;
COMMIT;

Between "BEGIN;" and "COMMIT;" is one SQL transaction. Either it is completed or not. That is atomic.

But if a form is opened with data via SQLdb from Postgresql and therefor a transaction was automatically started by SQLdb with "BEGIN;" there is no way to do such a transaction (and a eventually rollback) as long as the form lives and shows data. As far as you use the same connection.

Agree?

[taazz]

My experience with postgreSQL is limited so I have to check this out but my experience with various sql servers is that I can have multiple transactions per connection and they are not nested every single one of them is autonomous. Do you have a simple sample that demonstrates this behavior in postgreSQL?

[tionov]

Sure you can have multiple transactions in Postgresql per connection, but just one after the other:

BEGIN;
do something ...
COMMIT;

BEGIN;
do another ...
COMMIT;

works. But not:

BEGIN;
do something ...
  BEGIN;
  do another ...
  COMMIT;
COMMIT;

The last were nested transactions, and those do not work in PG. And that is good. Nested transactions are difficult to handle.

[taazz]

actually I'm referring to concurrent transactions not nested. For example firebird supports concurrent transactions ee you can have multiple transactions open at the same time sending /reading data from a single connection with out those being nested. I did some reading on the net and it seems that PostreSQL does not support concurrent connections so you have 3 choices here.
1) use multiple connections as marcov said a connection pooling mechanism would be ideal to minimize connection delays.
2) Change the Transaction component your self to not open any transaction on read.
3) Create a client Side mechanism ee copy the data to a memory dataset and close the transaction. every time a change is made on the data run the insert/update/delete on sort leaved transaction.

all 3 require a bit of work to be usable with the easiest for you to implement is no 3 of course you have already taken choice 4 use zeos that does not have the problem so....

[mse]

TPQConnection uses for every transaction a separate connection.

Code: [Select]

function TPQConnection.StartdbTransaction(trans : TSQLHandle; AParams : string) : boolean;
var
  res : PPGresult;
  tr  : TPQTrans;
  i   : Integer;
  t : TPQTranConnection;
  L : TList;
begin
  result:=false;
  tr := trans as TPQTrans;

  //find an unused connection in the pool
  i:=0;
  t:=Nil;
  L:=FConnectionPool.LockList;
  try
    while (I<L.Count-1) do
      begin
      T:=TPQTranConnection(L[i]);
      if (T.FPGConn=nil) or not T.FTranActive then
        break
      else
        T:=Nil;
      i:=i+1;
      end;
    // set to active now, so when we exit critical section,
    // it will be marked active and will not be found.
    if Assigned(T) then
      T.FTranActive:=true;
  finally
    FConnectionPool.UnLockList;
  end;
  if (T=Nil) then
    begin
    T:=TPQTranConnection.Create;
    T.FTranActive:=True;
    AddConnection(T);
    end;
  if (T.FPGConn<>nil) then
    tr.PGConn:=T.FPGConn
  else
    begin
    tr.PGConn := PQconnectdb(pchar(FConnectString));
    T.FPGConn:=tr.PGConn;
    CheckConnectionStatus(tr.PGConn);
    if CharSet <> '' then
      PQsetClientEncoding(tr.PGConn, pchar(CharSet));
    end;

  res := PQexec(tr.PGConn, 'BEGIN');
  CheckResultError(res,tr.PGConn,sErrTransactionFailed);

  PQclear(res);
  result := true;
end;


[tionov]

Yes! You got it.

actually I'm referring to concurrent transactions not nested. For example firebird supports concurrent transactions ee you can have multiple transactions open at the same time sending /reading data from a single connection with out those being nested.

Ok, this seems to me a special feature of Firebird, which you find not in Oracle, AFAIK not in Mysql, not in PG. For me it seems difficult to handle, because of the increased possibilty of database deadlocks in your application at runtime. For me it is IMHO easier, to use transactions only if I write or delete data in more than one query at once. And that's IMO the reason, transactions in SQL are made for.

I did some reading on the net and it seems that PostreSQL does not support concurrent connections so you have 3 choices here.
1) use multiple connections as marcov said a connection pooling mechanism would be ideal to minimize connection delays.

Yes, but with other sideeffects. In PG one has special configuration options for connections concerning memory. If you have a database which is used by - say - 50 clients you can control consumption of memory  per connection. But if your clients are opening an unpredictable number of connections ... one or two clients running wild can eat up memory on the server. So - allowing more connections can decrease stability at work IMO.

2) Change the Transaction component your self to not open any transaction on read.

Hm. I see this thread as a suggestion to the developers of SQLdb, to allow this in future. As I now see, SQLdb seems to have been developed primary with Firebird in mind. So now I understand, why so much developers use Zeoslib when it comes to work with databases.

3) Create a client Side mechanism ee copy the data to a memory dataset and close the transaction. every time a change is made on the data run the insert/update/delete on sort leaved transaction.

This seems to me done by the most complicated means possible. ;-)
Compared to the fact, that if you query the data for maybe a DBGrid without implicit "BEGIN;" ... would solve that problem.

all 3 require a bit of work to be usable with the easiest for you to implement is no 3 of course you have already taken choice 4 use zeos that does not have the problem so....

Yes, the problem is gone by using Zeos.

Ok. What does Action->caNone in SQLdb transactions really do? Do you know it?