SQLDB Postgresql Transactions Issue lazarus / 16-Databases ---------------------------------------------------------------------- tionov · 2014-05-11 15:44 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 · 2014-05-11 16:13 That is true SQLDB was designed to always use a transaction and can' t be used outside one. ---------------------------------------------------------------------- tionov · 2014-05-11 16:34 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 · 2014-05-11 17:15 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 · 2014-05-11 17:23 Quote from: tionov on May 11, 2014, 05:44:47 pm 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. QuoteTherefor 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 · 2014-05-11 18:13 Quote from: taazz on May 11, 2014, 07:15:47 pmI 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. QuoteHaving 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 · 2014-05-11 18:39 Quote from: marcov on May 11, 2014, 07:23:09 pmQuoteTherefor 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 · 2014-05-11 18:42 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 · 2014-05-11 19:32 Quote from: tionov on May 11, 2014, 08:39:18 pmQuote from: marcov on May 11, 2014, 07:23:09 pmQuoteTherefor 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 · 2014-05-11 20:45 Quote from: taazz on May 11, 2014, 09:32:54 pmQuote from: tionov on May 11, 2014, 08:39:18 pmAhm 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 · 2014-05-11 20:58 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 · 2014-05-11 21:14 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 · 2014-05-11 21:30 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 · 2014-05-12 05:21 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 (Inil) 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 · 2014-05-12 06:16 Yes! You got it. Quote from: taazz on May 11, 2014, 11:30:13 pmactually 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. QuoteI 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. Quote2) 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. Quote3) 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. Quoteall 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? ---------------------------------------------------------------------- tionov · 2014-05-12 06:41 Quote from: mse on May 12, 2014, 07:21:30 amTPQConnection uses for every transaction a separate connection. Thank you, but using another connection for each transaction in PG would IMO decrease stability of a PG server (if one has a lot of clients working), as described in the other post. I want to avoid connection pooling if ever possible. And in the case I described at the beginning of this thread, it would be absolutely unneccessary, if SQLdb would not force and keep open transactions even for single queries used to display just some data of one table. ---------------------------------------------------------------------- mse · 2014-05-12 06:48 I repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal. ---------------------------------------------------------------------- tionov · 2014-05-12 08:10 Quote from: mse on May 12, 2014, 08:48:42 amI repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal. Yes, thank you. I looked at that. But I'm working with Lazarus now. And as the topic tells, with Postgresql. ---------------------------------------------------------------------- taazz · 2014-05-12 12:32 Quote from: tionov on May 12, 2014, 08:16:19 am Quote3) 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. Not really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple. Quote from: tionov on May 12, 2014, 08:16:19 amOk. What does Action->caNone in SQLdb transactions really do? Do you know it? Action is used to decide how to handle open transactions only it does not influence how the transactions starts but when a dataset is closed with an acitve transaction action is used to execute the default eg commit rollback etc. ---------------------------------------------------------------------- mse · 2014-05-12 13:01 Quote from: taazz on May 12, 2014, 02:32:54 pmNot really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple. The mentioned tmsesqlquery option dso_offline does it. ---------------------------------------------------------------------- stoppok · 2014-05-12 19:53 I experienced the same problems as Tionov with SQLite, the situation here is even worse: On a second PC I could not even Select the data from my database, the query resulted in a timeout. ---------------------------------------------------------------------- Zoran · 2014-05-13 04:21 Quote from: mse on May 12, 2014, 03:01:24 pmQuote from: taazz on May 12, 2014, 02:32:54 pmNot really, the mechanism is already there every TQuery or TDataset you use buffers the data in the client side for you to browse, they simple clear the buffer when the connection/transaction is closed changing that to keep the data should be fairly simple. The mentioned tmsesqlquery option dso_offline does it. Quote from: mse on May 12, 2014, 08:48:42 amI repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal. Hello, Martin, Have you thought about contributing these SQLDB options to FCL? ---------------------------------------------------------------------- tionov · 2014-05-13 05:39 Quote from: stoppok on May 12, 2014, 09:53:11 pmI experienced the same problems as Tionov with SQLite, the situation here is even worse: On a second PC I could not even Select the data from my database, the query resulted in a timeout. Do not use it. SQLite locks the whole table on a started transaction (same as Mysql did in early days). If one writes "BEGIN;" before reading, the table is locked. SQLdb does that and you can't prevent it from doing this. In common database development, the use of "BEGIN;", "COMMIT;" or "ROLLBACK;" is in the hand of the person, which writes the code for a database-centric application. Not so here. Firebird seems to have support for nested transactions*. So SQLdb's behavior may work here. *But nested transactions are usually bad. ---------------------------------------------------------------------- tionov · 2014-05-13 05:40 Quote from: mse on May 12, 2014, 08:48:42 amI repeat: MSEgui version of SQLDB has the ttransaction flag "tao_fake" for that purpose. If one needs full Firebird support the ZEOS approach is not ideal. What does "tao_fake" mean? ---------------------------------------------------------------------- mse · 2014-05-13 06:20 @zoran: The code is here: https://gitorious.org/mseide-msegui/mseide-msegui/source/lib/common/db Feel free to backport to FCL. @stoppok: MSEgui version of tsqlite3connection has options slo_transactions flag which is by default off. If it is off, it uses implicit transactions. Please note, Sqlite3 holds a file lock while reading the resultset. So if packagerecord of tsqlquery <> -1 it is possible that a pending read operation blocks other applications from writing data. Even an open design time component blocks the compiled, running and debugged application. @tionov: tao_fake means that for attached components (datasets, tsqlresult, tsqlstatement,tsqllookupbuffer...) tmsesqltransaction behaves as normal but actually doesn't call begin-/endtransaction. BTW: Firebird has support for *parallel* transactions in a single connection. Another great and unique feature is "commitretaining". ---------------------------------------------------------------------- tionov · 2014-05-13 07:16 Quote from: mse on May 13, 2014, 08:20:56 am@tionov: tao_fake means that for attached components (datasets, tsqlresult, tsqlstatement,tsqllookupbuffer...) tmsesqltransaction behaves as normal but actually doesn't call begin-/endtransaction. BTW: Firebird has support for *parallel* transactions in a single connection. Another great and unique feature is "commitretaining". URGS. Ok, now I know, why I would never use Firebird. http://www.firebirdsql.org/file/documentation/papers_presentations/htm l/paper-fbent-impacting.html QuoteWith Firebird (and InterBase), Commit Retaining causes transactions to remain interesting indefinitely. Garbage collection effectively ceases on the “standard” Borland RAD tools database application and any other applications that make use of Commit Retaining. Such systems are fraught with problems of progressively degrading performance that cannot be resolved except by shutting down the database and allowing these old transactions to die. These are all ill concepts if you are used to work with a database like Postgresql, where a lot of clients hammer on the whole day long for months without deadlocks, performance drops, data lost. Postgresql is a rock you can rely on and it is absolutely transparent, if you are working with it as a developer. Commit Retaining is not SQL Standard. And yes, Firebird is unique. Thank you for clarification. ---------------------------------------------------------------------- mse · 2014-05-13 07:31 You don't need to use "commitretaining" but you can. ;-) ---------------------------------------------------------------------- mangakissa · 2014-05-13 07:48 Firebird / Interbase must have transaction to work properly with unsaved data. Commit Retaining does nothing more than save your data to the databasefile, so other users can read the new/modified data. MySQL does it similar with savepoints. I know enough people who uses Firebird in a multi environment ---------------------------------------------------------------------- tionov · 2014-05-13 08:03 Quote from: mse on May 13, 2014, 09:31:54 amYou don't need to use "commitretaining" but you can. ;-) :-) I do not want to bash Firebird here. I see, as a beginner of Lazarus programming, that I find a "traditional" context here, which led to solutions that may work good with FB. But it should be clear that SQLdb is not really useful together with SQL databases that fulfil "only" SQL standard. Ok, for me the thread is over now. I'll use Zeoslib or write my own db-layer (if I understand FPC enough, which will last for a while) or do a combination of both. ---------------------------------------------------------------------- marcov · 2014-05-13 14:07 Quote from: mangakissa on May 13, 2014, 09:48:28 amMySQL does it similar with savepoints. Oracle does the same with savepoints, and iirc can use named transactions for multiple transaction/connection too. ---------------------------------------------------------------------- goodname · 2014-05-14 22:51 When using SQLdb in the past found that odd client stability issues could be cleared up by frequently using TSQLTransaction.CommitRetaining on transactions that handled readonly queries. These readonly queries never needed to be in a transaction and it became standard practice to have two transactions per form, one for read queries and one for write queries. Just saying transactions don't always make sense and can add needless overhead. ---------------------------------------------------------------------- mse · 2014-05-15 05:13 MSEgui version of SQLDB has an additional "transactionwrite" property for that purpose. So the normal read "Transaction" can have set "tao_fake", the write transaction can be opened while updating the database only by setting the "dso_autocommit" instead the "dso_autocommitret" flag. ---------------------------------------------------------------------- tionov · 2014-05-15 06:40 Quote from: goodname on May 15, 2014, 12:51:14 amWhen using SQLdb in the past found that odd client stability issues could be cleared up by frequently using TSQLTransaction.CommitRetaining on transactions that handled readonly queries. These readonly queries never needed to be in a transaction and it became standard practice to have two transactions per form, one for read queries and one for write queries. Just saying transactions don't always make sense and can add needless overhead. As I wrote before, transactions are made for doing a couple of things in atomic (ACID) manner. http://en.wikipedia.org/wiki/Database_transaction Opening a transaction for reading and then keeping it open just for nothing is a really bad idea. But SQLdb does that always ...  :/ As a developer of db client software I know, that I have to use a SQL(!) transaction (I do not mean TSQLtransaction) usually only in the moment, where data is to be written and I have to keep it's time frame a short as possible. This prevents the server from unnecessary load and the danger, that two clients send each other in database deadlocks, is very small. These stability issues become the more important as the number of clients (and/or connections) grows. ---------------------------------------------------------------------- allanregistos · 2014-05-22 08:03 Hi tionov, As a user of PostgreSQL and sqldb, I have this concern after reading this thread. I used sqldb to connect to PG, components using these: TPQConnection,TSQLTransaction,TSQLQuery and TDatasource. I dropped them on the form and use tdbgrid to display data.  My software may not involve a lot of users at least one user and at most up to 3.  Since I am no expert of how sqldb perform under the hood, may I ask for your advice if it is okay for me to use this in my few apps, since rewriting them using another components may take some time. Also, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed. What's your advice? Thanks in advance. -Allan ---------------------------------------------------------------------- tionov · 2014-05-22 10:16 Hi allanregistos, QuoteMy software may not involve a lot of users First: I'm no experienced Lazarus developer and I will not continue developing with Lazarus. I switched over to Gambas which suits better for my requirements. If you work with your software together with other users on the same database and you see deadlocks (your application stops responding because it waits for the database which also waits for some action of other users) then you should consider changing the database layer of your software and use Zeoslib instead of SQLdb. Zeoslib offers the opportunity to use no SQL transaction just for reading. Personally I am not a fan of ORM (SQLdb and Zeoslib are kind of ORM). These add complexity and one has to understand not only SQL but also the ORM-Layer to predict, what is really happening inside the database. In my applications I find it easier to write SQL by hand (in the code, obviously) and send it to the database on certain events. If that happens too often, I write a set of methods, which help me doing that. In the end I am faster in application development because I know what is happening and I am not in the need of fiddling around with complex libraries which want to make things easier, but are very hard to understand (this is a major part of the last 10 % in software- development which cost 90% time). Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that. QuoteAlso, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed. As long as you are able to control the SQL transaction by yourself, for me it seems no problem, to use that controls. But with SQLdb you cannot control the SQL transaction (BEGIN ... COMMIT/ROLLBACK). So my advice is: If you see deadlocks, use Zeoslib and/or do SQL by hand. ---------------------------------------------------------------------- marcov · 2014-05-22 13:38 Quote from: tionov on May 22, 2014, 12:16:40 pm Personally I am not a fan of ORM (SQLdb and Zeoslib are kind of ORM).  These add complexity and one has to understand not only SQL but also the ORM-Layer to predict, what is really happening inside the database. They are not ORM. There are no Objects or OOP principles involved.  They are just a general DB layer, and better comparable with ADO,ODBC,JDBC etc. QuoteIn my applications I find it easier to write SQL by hand (in the code, obviously) and send it to the database on certain events. This depends on your scenario. Delphi/Lazarus are mostly geared towards being highly productive in client apps. A high percentage of work is then spent in making simple "list of items"  editors (add/delete/modify with some variants like Master/detail etc). Most of these packages are geared towards that (grid + navigator), to be both productive and less repetitive. The database matters less, and most SQL is the same. IOW one doesn't use a generic db layer, and then start micromanaging SQL. Or at least not for the bulk of the application. QuoteIn the end I am faster in application development because I know what is happening and I am not in the need of fiddling around with complex libraries which want to make things easier, but are very hard to understand (this is a major part of the last 10 % in software- development which cost 90% time). Then try rewiring your app to a different db. QuoteOften they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that. The problem is that this is not black or write. What you say is correct for the difficult parts of the application. But those are intensive but relatively small. Example: in the past we had a CMS/ERP like system with 1200 tables. Only a few dozen of those were complex entities with complex relations. But all those other tables needed basic editors with some simple customized logic to making entry for the customer easy, etc etc. ---------------------------------------------------------------------- tionov · 2014-05-22 15:16 Hi markov, QuoteThey are not ORM. There are no Objects or OOP principles involved.  They are just a general DB layer, and better comparable with ADO,ODBC,JDBC etc. Ok, right. They are database abstraction layer. But ODBC etc don't force you into starting a transaction when you open a table. SQLdb does. QuoteThis depends on your scenario. Delphi/Lazarus are mostly geared towards being highly productive in client apps. A high percentage of work is then spent in making simple "list of items"  editors (add/delete/modify with some variants like Master/detail etc). Most of these packages are geared towards that (grid + navigator), to be both productive and less repetitive. The database matters less, and most SQL is the same. Yes. QuoteIOW one doesn't use a generic db layer, and then start micromanaging SQL. Or at least not for the bulk of the application. Ok. yes. One can do both. QuoteThen try rewiring your app to a different db. I did this two times in my life and I know how much work that is. And I've learned from that. The most I have learned is: Use Postgresql. ;-) The second is: Write my own db-layer. QuoteQuoteOften they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that. The problem is that this is not black or write. What you say is correct for the difficult parts of the application. But those are intensive but relatively small. Example: in the past we had a CMS/ERP like system with 1200 tables. Only a few dozen of those were complex entities with complex relations. But all those other tables needed basic editors with some simple customized logic to making entry for the customer easy, etc etc. Ok. My personal conclusion after 20 years of db programming is: If I write a small application with about up to five tables it's no problem to write a few SQL-strings by hand. SQL is easy and very mighty, if you know it. But if you don't know it because you use a drag-and-drop-db-layer you normally don't know what your application does. This is bad, when your tables grow by and by. It is even worse when you are in production with your five tables and it gets slow and slower fast (for example SQLdb has no LIMIT, FetchRow x does nothing with PG). So: Learn SQL first. If I have to write a larger application I write my own db layer and I may even write the components by my own. Because: First I understand my own db layer. Second, if I use a db-layer written by somone else it may occur that this is gonna be developped in a wrong direction. I had this situation with MS Access, when MS stopped the support for a certain procedure to connect to other dbs. A procedure, they recommended before and I have used 700 times in code over the years. Thank you, MS... Luckily the last seldom occurs in open source software. ---------------------------------------------------------------------- allanregistos · 2014-05-23 01:08 Quote from: tionov on May 22, 2014, 12:16:40 pmHi allanregistos, QuoteMy software may not involve a lot of users First: I'm no experienced Lazarus developer and I will not continue developing with Lazarus.Me either, but that doesn't stop me from using Lazarus, and now I am primarily developing with it. It takes time, and now I can say that it is much easier to program in lazarus than in VB.net, my previous PL. Quote from: tionov on May 22, 2014, 12:16:40 pm I switched over to Gambas which suits better for my requirements. .Then you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro.  Lazarus/fpc's made software will run anywhere as long as you compile it to the target OS without any needed runtime dependencies.  As a user of BASIC(VB.net) Gambas is the first choice when programming in Linux.  But I missed my Pascal days(Turbo Pascal) and began investigating if the language was still available or was upgraded. Delphi is expensive so I've found Lazarus and fpc.  There are tons of advantages of using Lazarus over Gambas, but I respect your choice, your requirements must be that your software will only run on Linux. Quote from: tionov on May 22, 2014, 12:16:40 pmIf you work with your software together with other users on the same database and you see deadlocks (your application stops responding because it waits for the database which also waits for some action of other users) then you should consider changing the database layer of your software and use Zeoslib instead of SQLdb.So far, the performance is acceptable. I think I really need to replace SQLdb. I am also interested to do SQL queries by hand if it is easy. Quote from: tionov on May 22, 2014, 12:16:40 pm............... Often they are announced with the argument "you can change the database easily". But that's a lie, as far as you use the benefits and features of a really mature DB like Postgresql, like triggers, rules, functions and so on. And if you work with a mature database for longer time, you will use that. The TIOPF approach maybe[Advertised as you can change any DB on the fly], but I've found that tool difficult to use firsthand because it is blind and cannot see PostgreSQL clients lib. Quote from: tionov on May 22, 2014, 12:16:40 pmQuoteAlso, I am also using the components to perform insert/delete and updates  but using code to create them as needed instead of dropping the controls on the form.  So I am using these components separately, the visible components I'd dropped on the form to display data,  while if I am doing delete/update/insert, I will use dynamically created sqldb components created via code as needed. As long as you are able to control the SQL transaction by yourself, for me it seems no problem, to use that controls. But with SQLdb you cannot control the SQL transaction (BEGIN ... COMMIT/ROLLBACK). So my advice is: If you see deadlocks, use Zeoslib and/or do SQL by hand. I will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent. ---------------------------------------------------------------------- LacaK · 2014-05-23 06:07 See, what SQL standard says about SQL transactions: Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction. An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating . So sqlDB implementation is not so far from what SQL standard expects: Each SQL statement is associated with transaction context, if none transaction is active new is initiated. And about CommitRetaining, SQL standard knows COMMIT AND CHAIN: If those statements specify AND CHAIN, then they also initiate a new SQL-transaction with the same characteristics as the SQL-transaction that was just terminated, ---------------------------------------------------------------------- tionov · 2014-05-23 06:15 Quote from: allanregistos on May 23, 2014, 03:08:30 amThen you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro.  Yes I know. But I write software for my own firm, so that's not a problem. We work on Linux solely. And i had no "Pascal days", so for me FPC is a complete new language with much things I would have to learn from the ground up. At Gambas I'm much more "at home". QuoteI will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent. I f you have your business logic on the PG Server that is very good! In FPC you can use the class postgres to connect a Lazarus client to the server.  How(?) I did not really figure out because I stopped my experiments with Lazarus. But there is the class postgres (and maybe postgres3) to look at it and there are examples in /usr/share/fpcsrc/2.6.4/packages/postgres/examples (on my linux computer) which can be adopted. So I only can tell, what  I am doing in Gambas or other languages: 1) Build a static class (that is a singleton) to do only one connection per PG database at start of your program. This is your channel to talk to PG. In PGAdmin you have the server tools, there you can see the connections. If your program opens more than one you should know why, that is a source of deadlocks (maybe you used a control with SQLdb in parallel). In Gambas I do this in a method (this is a static class/aka singleton): Code: [Select]Public DB As New Connection Public Sub _init()   startconnection End Private Sub startconnection()   If Not PGdb.Opened Then     PGdb.Type = "postgresql"     PGdb.Host = "hostname"     PGdb.Name = "dbname"     PGdb.Login = "User"     PGdb.Password = "password"     PGdb.Open()   Endif Catch   myownError.Handler End Ok, you have to rewrite this for FPC like in the example. 2)In your program send your query over this connection and receive the result (usually as array). arrayresult = DB.exec("SELECT * FROM this WHERE ...") 3) Use the data in the array to fill your controls. 4) Write methods which send queries over the connection to update data in database when the user changed the data in the controls. DB.exec("DELETE FROM this WHERE ...") DB.exec("UPDATE this SET y = " &  blah & "  WHERE ...") If you want to change a couple of things in the database at once, do it so: DB.exec("BEGIN;") send a couple of queries ... DB.exec("COMMIT;") Ok this is very basic, but ensures, that you get a rockstable client. For better stability use prepared statements (do not forget DEALLOCATE): http://www.postgresql.org/docs/9.3/static/sql-prepare.html If you work with larger tables, ensure that you use LIMIT if you query the data. Or use cursors: http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html So people using your client can scroll data. ---------------------------------------------------------------------- tionov · 2014-05-23 06:53 Quote from: LacaK on May 23, 2014, 08:07:53 amSee, what SQL standard says about SQL transactions: Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction. An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating . Yes. If you start a transaction, it is started. If you do something in SQL on the same connection, it is done within that transaction until you close the transaction. QuoteSo sqlDB implementation is not so far from what SQL standard expects: Each SQL statement is associated with transaction context, if none transaction is active new is initiated. Yeah SQLdb uses SQL Standard ... but the wrong way. Normally a db like PG does autocommit on each query. if you send "SELECT *" it makes "BEGIN;SELECT *;COMMIT;". For small queries this normally happens in a split second. But PG does that not, if you send a query that explicit starts with "BEGIN;". Then it waits until you send a "COMMIT;". It waits for hours and days, if you want. Ok, SQLdb does SQL. But the problem is, as long as you see your data in a grid powered by SQLdb other users are blocked updating that data because SQLdb opened a transaction and does not close it until the end of ... maybe ... your form ... your program? QuoteAnd about CommitRetaining, SQL standard knows COMMIT AND CHAIN: If those statements specify AND CHAIN, then they also initiate a new SQL-transaction with the same characteristics as the SQL-transaction that was just terminated, But not within that transaction. I think that's quite of a different nature. And i don't know enough of Firebird. ---------------------------------------------------------------------- allanregistos · 2014-05-23 07:35 Quote from: tionov on May 23, 2014, 08:15:57 amQuote from: allanregistos on May 23, 2014, 03:08:30 amThen you will missed the opportunity to distribute your software on any OS platform.  Gambas's produced software only runs on Linux as far as I know and it also needs the Gambas runtime before your software will run on a Linux distro.  Yes I know. But I write software for my own firm, so that's not a problem. We work on Linux solely. And i had no "Pascal days", so for me FPC is a complete new language with much things I would have to learn from the ground up. At Gambas I'm much more "at home".Tried Gambas, but find it difficult, really it depends on our past experience with programming languages. I am still a newbie in computer programming, even if I had those Pascal days with me.  The benefits of using Lazarus/fpc over Gambas are huge even if you only work for Linux, that is a guarantee that Lazarus/fpc people can make for you.  It is even easier to use than VB.Net, however, if you are comfortable enough with Gambas, then its ok. Quote from: tionov on May 23, 2014, 08:15:57 amQuoteI will replace sqldb if that is the case.  Can you give me hints on how to do SQL by hand? I mean you do the SQL queries in Gambas? Or call the SQL procedures/functions at the PG server?  I am comfortable with SQL statements, since most business logic are in the DB anyway and PostgreSQL docs are excellent. I f you have your business logic on the PG Server that is very good! In FPC you can use the class postgres to connect a Lazarus client to the server.  How(?) I did not really figure out because I stopped my experiments with Lazarus. But there is the class postgres (and maybe postgres3) to look at it and there are examples in /usr/share/fpcsrc/2.6.4/packages/postgres/examples (on my linux computer) which can be adopted.Thanks for the tips, I'm looking at it. It is quite similar to npgsql for .NET. Quote from: tionov on May 23, 2014, 08:15:57 amSo I only can tell, what  I am doing in Gambas or other languages: 1) Build a static class (that is a singleton) to do only one connection per PG database at start of your program. This is your channel to talk to PG. In PGAdmin you have the server tools, there you can see the connections. If your program opens more than one you should know why, that is a source of deadlocks (maybe you used a control with SQLdb in parallel). In Gambas I do this in a method (this is a static class/aka singleton): Code: [Select]Public DB As New Connection Public Sub _init()   startconnection End Private Sub startconnection()   If Not PGdb.Opened Then     PGdb.Type = "postgresql"     PGdb.Host = "hostname"     PGdb.Name = "dbname"     PGdb.Login = "User"     PGdb.Password = "password"     PGdb.Open()   Endif Catch   myownError.Handler End Ok, you have to rewrite this for FPC like in the example. 2)In your program send your query over this connection and receive the result (usually as array). arrayresult = DB.exec("SELECT * FROM this WHERE ...") 3) Use the data in the array to fill your controls. 4) Write methods which send queries over the connection to update data in database when the user changed the data in the controls. DB.exec("DELETE FROM this WHERE ...") DB.exec("UPDATE this SET y = " &  blah & "  WHERE ...") If you want to change a couple of things in the database at once, do it so: DB.exec("BEGIN;") send a couple of queries ... DB.exec("COMMIT;") Ok this is very basic, but ensures, that you get a rockstable client. For better stability use prepared statements (do not forget DEALLOCATE): http://www.postgresql.org/docs/9.3/static/sql-prepare.html If you work with larger tables, ensure that you use LIMIT if you query the data. Or use cursors: http://www.postgresql.org/docs/9.3/static/plpgsql-cursors.html So people using your client can scroll data. Thank you for the tips. ---------------------------------------------------------------------- allanregistos · 2014-05-23 07:57 Quote from: tionov on May 23, 2014, 08:53:04 amQuote from: LacaK on May 23, 2014, 08:07:53 amSee, what SQL standard says about SQL transactions: Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction. An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating . Yes. If you start a transaction, it is started. If you do something in SQL on the same connection, it is done within that transaction until you close the transaction. This is correct. Quote from: tionov on May 23, 2014, 08:53:04 amQuoteSo sqlDB implementation is not so far from what SQL standard expects: Each SQL statement is associated with transaction context, if none transaction is active new is initiated. Yeah SQLdb uses SQL Standard ... but the wrong way. Normally a db like PG does autocommit on each query. if you send "SELECT *" it makes "BEGIN;SELECT *;COMMIT;". For small queries this normally happens in a split second. But PG does that not, if you send a query that explicit starts with "BEGIN;". Then it waits until you send a "COMMIT;". It waits for hours and days, if you want. There is also the rollback function on which if an error occurs, whatever you are doing after the BEGIN statement would be discarded.  http://en.wikipedia.org/wiki/ACID#Atomicity Quote from: tionov on May 23, 2014, 08:53:04 amOk, SQLdb does SQL. But the problem is, as long as you see your data in a grid powered by SQLdb other users are blocked updating that data because SQLdb opened a transaction and does not close it until the end of ... maybe ... your form ... your program?tionov, I need to show you something.  If that statement of yours reflects what SQLdb does under the hood, then I must be doing something right, because, what I am doing with sqldb as I stated earlier in this thread is I am dropping those controls to the form for the tdbgrid to display data. Those same data that _being_ displayed through the data grid(tdbgrid), I was able to manipulate(update/delete/insert). It is like my app have two PG users at the same time, one user that uses sqldb to display data and one user that manipulates then on the fly. To display data, here is the code: Code: [Select]procedure FillWholeRollsGrid(RollsGrid: TDBGrid; SQLQueryRolls: TSQLQuery; SQLTransactionRolls: TSQLTransaction;   DatasourceRolls: TDatasource); begin     SQLStrg :='SELECT ' +             'paper_trim.value || ''X'' || gsm.value || ''-'' || liner.value as description, ' +             'CASE rolls.status_id ' +             ' WHEN 33 THEN roll_history.weight ELSE rolls.weight END AS weight, ' +             'CASE rolls.status_id ' +             ' WHEN 33 THEN roll_history.diameter ELSE rolls.diameter END AS diameter, ' +             'rolls.roll_no,'+             'contact.organization_name as supplier,rolls.id ' +             'FROM rolls ' +             'JOIN paper_trim ON paper_trim.id=rolls.trim_id ' +             'JOIN gsm ON gsm.id=rolls.gsm_id ' +             'JOIN liner ON liner.id=rolls.liner_id ' +             'LEFT JOIN roll_history ON roll_history.roll_id=rolls.id ' +             'JOIN contact ON contact.id=rolls.supplier_id WHERE (status_id=30 OR status_id=33 and latest_remnant=1) ' +             'ORDER BY rolls.id DESC;';     try       SQLQueryRolls.close;       SQLQueryRolls.SQL.Text := SQLStrg;       SQLTransactionRolls.Active:=True;       RollsGrid.DataSource := DatasourceRolls;       SQLQueryRolls.Open;     Except       ON E: Exception do       begin         raise exception.create('Exception Caught: ' + E.Message);       end;     end; end;  And then I will call those SQL queries(del/update/insert) using a separate sqldb components that were created using code and release them after each use, for example after deleting a row of data, I will call again the procedure above to update the data grid for the change that takes place. And that remains open for the whole session as long as the user will not close the program. I am using the TSQLQuery's afterscroll event to edit/delete/update a row within that data grid. And its perfect. See the screenshot attachment. So even if sqldb created a BEGIN transaction, I was still able to manipulate the data being displayed. ---------------------------------------------------------------------- zeljko · 2014-05-23 10:23 Quote from: LacaK on May 23, 2014, 08:07:53 amSee, what SQL standard says about SQL transactions: Each direct invocation of SQL that executes an SQL-statement of an SQL-transaction is associated with that SQL-transaction. An SQL-transaction is initiated when no SQL-transaction is currently active by direct invocation of SQL that results in the execution of a transaction-initiating . Where's link to that SQL standard ? I don't like way of: 1.Login -> START TRANSACTION (sitting in transaction on the database) 2.Do something 3.COMMIT/ROLLBACK and then START TRANSACTION again (goto 1.) 4.Logout My suff work like this: 1.Login 2.Wait some query to start (any kind of). 3.START TRANSACTION 4.COMMIT/ROLLBACK  (goto 2.). 5.Logout. In 1st example all database connections are "in transaction", in 2nd example client connection is in transaction only when it's really doing something, so much better for rdbms server performance. ---------------------------------------------------------------------- taazz · 2014-05-23 10:47 We are talking only for the second example here, Postgresql doesn't like the begin transaction on read/select operations it does not support concurrent transaction either so begin transaction select * from X locks the connection in that transaction until the user logs out of the application or the closes all open browsers. I agree that sqldb needs to be extended to allow sql operations without a transaction and let it be required on firebird connection where the database needs them. ---------------------------------------------------------------------- tionov · 2014-05-23 11:49 Quote from: allanregistos on May 23, 2014, 09:57:47 amAnd then I will call those SQL queries(del/update/insert) using a separate sqldb components that were created using code and release them after each use, for example after deleting a row of data, I will call again the procedure above to update the data grid for the change that takes place. And that remains open for the whole session as long as the user will not close the program. I am using the TSQLQuery's afterscroll event to edit/delete/update a row within that data grid. And its perfect. See the screenshot attachment. So even if sqldb created a BEGIN transaction, I was still able to manipulate the data being displayed. Yes you can do this (using more connections and transactions in one client and keeping transactions open) but in a multiuser-environment this will produce problems especially if the clients occasionally edit the same data, try experimenting ... Please understand that I will not invest more time, for further reading I recommend this: http://www.postgresql.org/docs/9.2/static/transaction-iso.html Here you can see, how PG handles concurrent transactions and the visibility of data changes. ---------------------------------------------------------------------- LacaK · 2014-05-23 12:35 Quote from: zeljko on May 23, 2014, 12:23:22 pmWhere's link to that SQL standard ? I don't like way of: 1.Login -> START TRANSACTION (sitting in transaction on the database) 2.Do something 3.COMMIT/ROLLBACK and then START TRANSACTION again (goto 1.) 4.Logout In 1st example all database connections are "in transaction", in 2nd example client connection is in transaction only when it's really doing something, so much better for rdbms server performance. I do not think so. When you open connection it does not mean, that you start transaction at same time. Transaction is not started until you execute any SQL statement. Which is okay as far as any SQL statament must be executed in context of any transaction. Second, I do not think, that when I start transaction and I do not perform any SQL statement, that it affect server performance. For example when I select data for using in DBGrid with later possibility of editing. I do: 1. Open TSQLQuery 2. CommitRetaining at this point I have active transaction, but it does not block others transactions on SQL server (nor holds locks on tables or so) 3. perform any updates 4. again CommitRetaining or Commit ---------------------------------------------------------------------- taazz · 2014-05-23 12:52 True but since postgreSQL does not support multiple transactions on a single connection that means that I have to open a new connection on each table browser I open or on each stored procedure I run etc, making it a bit hard to predict and manage connections ee instead one per client now I have god knows how many depending on the use case of each user. As far as postgresql goes that might not be a problem (I have no idea) but what is going to happen on commercial servers where you pay per cpu/connection or you are restricted by them eg the 5 concurrent connection limit of the MSSQL express 2000, how I'm supposed to manage that (don't say very carefully)? It is imperative that the mandatory transaction requirement is lifted from the library and placed on the connection or the server used. ---------------------------------------------------------------------- swierzbicki · 2014-05-23 13:12 I'm also surprised the way SQLDB is working. Like other users reported, I'm working like this : 1.Connecting 2.Opening queries (no need any transaction here) 3.START TRANSACTION 4.Modifying some data, SQL Update Script and so on 4.COMMIT/ROLLBACK  5.Refreshing certains queries if needed 5.Logout. Automatic transaction should be an option and not a standard. BR ---------------------------------------------------------------------- elidorio · 2014-05-23 18:40 Hello, Has a project called JDO is based on SQLDB. Seems to me he is something like Zeos Automatic transactions. Check the link below, suddenly, you can even take some ideas and implement in SQLDB. https://github.com/silvioprog/jdo []'s Edson ---------------------------------------------------------------------- allanregistos · 2014-05-24 00:40 Hi, Can we file a bug for this so that the developers will take notice? Quote from: swierzbicki on May 23, 2014, 03:12:43 pmI'm also surprised the way SQLDB is working. Like other users reported, I'm working like this : 1.Connecting 2.Opening queries (no need any transaction here) 3.START TRANSACTION 4.Modifying some data, SQL Update Script and so on 4.COMMIT/ROLLBACK  5.Refreshing certains queries if needed 5.Logout. Automatic transaction should be an option and not a standard. BR ---------------------------------------------------------------------- allanregistos · 2014-05-24 03:58 Quote from: LacaK on May 23, 2014, 02:35:40 pmQuote from: zeljko on May 23, 2014, 12:23:22 pmWhere's link to that SQL standard ? I don't like way of: 1.Login -> START TRANSACTION (sitting in transaction on the database) 2.Do something 3.COMMIT/ROLLBACK and then START TRANSACTION again (goto 1.) 4.Logout In 1st example all database connections are "in transaction", in 2nd example client connection is in transaction only when it's really doing something, so much better for rdbms server performance. I do not think so. When you open connection it does not mean, that you start transaction at same time. Transaction is not started until you execute any SQL statement. Yes, which is what sqldb behaves? I do not need to call BEGIN/COMMIT; when I only need to SELECT * data for display in a grid which is what PG wants me to do for the sake of performance. Quote from: LacaK on May 23, 2014, 02:35:40 pmWhich is okay as far as any SQL statament must be executed in context of any transaction. You mean any 'SELECT * FROM MyTable' must exist inside a transaction? Can you define what transaction you are talking about? Quote from: LacaK on May 23, 2014, 02:35:40 pmSecond, I do not think, that when I start transaction and I do not perform any SQL statement, that it affect server performance.I think it affects somehow because the database engine will see your 'session' as a 'transaction' and it allocates resources for you in that respect. It expects BEGIN COMMIT; therefore it allocates resources for you based on what you need. If I only have to update one table of course it only needs a single SQL statement* and I do not need to call BEGIN/COMMIT for it so the Database engine will see and will provide me with the resources I need for that single SQL statement, so compare this to your Started Transaction and then can you see that the DB server will allocate more CPU cycles and memory for you than with my single SQL statement. Quote from: LacaK on May 23, 2014, 02:35:40 pmFor example when I select data for using in DBGrid with later possibility of editing. I do: 1. Open TSQLQuery 2. CommitRetaining at this point I have active transaction, but it does not block others transactions on SQL server (nor holds locks on tables or so) 3. perform any updates 4. again CommitRetaining or Commit This is I think fine with Firebird as I learned from this thread. So, the more I will recommend PosgreSQL from now on. Thanks to this thread. I think it is now even more safer for me to just use PostgreSQL rather than support multiple database with my applications. Regards, Allan *of course, as a DB administrator, I must decide for myself if an update of one table requires further checking or updates to other tables and this will require multiple SQL statements and therefore it needs to reside under a Transaction session for this is what Transaction was made for: http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html ---------------------------------------------------------------------- LacaK · 2014-05-24 04:49 From your link: PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block. Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might get the effect of transaction blocks without asking. Check the documentation for the interface you are using. ---------------------------------------------------------------------- allanregistos · 2014-05-24 05:21 Quote from: LacaK on May 24, 2014, 06:49:41 amFrom your link: PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block. Note: Some client libraries issue BEGIN and COMMIT commands automatically, so that you might get the effect of transaction blocks without asking. Check the documentation for the interface you are using. Yes, that's correct. If this is the case as the note described the interface we discuss so far is sqldb and the OP stated that interfacing with PostgreSQL must not use the BEGIN/COMMIT automatically and need to be turned off as an option to avoid deadlocks. However, if it can be turned off as in zeosdb, but PostgreSQL treated every SQL statement as within in the realm of transaction anyway, so what's the point of improving sqldb to adapt to PG's requirements? I still have to understand what tdbgrid was doing when it display data. I think as of this moment that whenever I see data on the grid(even if empty as long as it is connected to the datasource), it is in the context of open transaction. This is where the 'Transaction' we've been talking about differs. The single SQL statement that was treated by PG automatically with an implicit BEGIN/COMMIT block which will happen in an instant and the 'Transaction' that is open as long as the program is active, so if it is active, then deadlocks might occur in a multi-user environment. Imagine tens to hundreds of users using the same application with all of them are active(i.e., connected to the same DB and are all with active transactions).  Single SQL statements with implicit BEGIN/COMMIT might be different in this context because it will happen so fast, than calling BEGIN/COMMIT explicitly with SQL statement like this: ' SELECT * FROM MyTable;' and the interface will wait for the user for the component.close/free commands before closing the transaction. As I understood, the OP did not like this behavior, so if we follow the OP's requirements, we must not use tdbgrid at all but instead use TStringGrid to display data. Query for data and store them in the array using SQL by hand (postgres unit) Loop through the data in the array and store each of them in TStringGrid A more laborious approach of fetching data. Regards, Allan ---------------------------------------------------------------------- taazz · 2014-05-24 06:02 you took a simple matter of allowing or not implicit transactions in to a different level. 1) there is no reference of locking anything inside a transaction. Where is the documentation on locking? 2) from what I can tell the OP was talking about deadlocks inside a single client which might start to happen if you use multiple threads to update the database and you want each of the threads to be enclosed in their own transaction, then you have a problem. There is not mentioned anywhere that a transaction blocks other connections from changing or accessing data even the same record and no there is no way a single thread application could dead lock it self or other clients, as far as I can understand that is. So we do like to see that artificial requirement lifted or to put it better we would like to have the choice to use it instead of imposing it on us. I do not think that there is going to be any noticeable difference for single threaded application other than the fact that now you know how to choose wiser, that is. ---------------------------------------------------------------------- allanregistos · 2014-05-24 06:17 Quote from: taazz on May 24, 2014, 08:02:10 amyou took a simple matter of allowing or not implicit transactions in to a different level. 1) there is no reference of locking anything inside a transaction. Where is the documentation on locking? PG Explicit locking: http://www.postgresql.org/docs/9.3/static/explicit-locking.html Quote from: taazz on May 24, 2014, 08:02:10 am2) from what I can tell the OP was talking about deadlocks inside a single client which might start to happen if you use multiple threads to update the database and you want each of the threads to be enclosed in their own transaction, then you have a problem. There is not mentioned anywhere that a transaction blocks other connections from changing or accessing data even the same record and no there is no way a single thread application could dead lock it self or other clients, as far as I can understand that is. Yes, as I have tested in my app. Quote from: taazz on May 24, 2014, 08:02:10 amSo we do like to see that artificial requirement lifted or to put it better we would like to have the choice to use it instead of imposing it on us. I do not think that there is going to be any noticeable difference for single threaded application other than the fact that now you know how to choose wiser, that is. ---------------------------------------------------------------------- taazz · 2014-05-24 06:37 Took a look on the page you linked it says nothing about transactions locking anything, only the commands place locks eg the select will place a lock on the table and as long that server side cursor is open ee as long as you have grid showing data it will keep that lock regardless of the transaction it was in, so over all  it has no difference (on the locking that is) with the implicit transaction the server starts for you, as long as you use a new connection for each thread you should be fine. Non the less I would like to see your demo app showing the problem if I'm not imposing to much on your schedule. ---------------------------------------------------------------------- allanregistos · 2014-05-24 06:48 Quote from: taazz on May 24, 2014, 08:37:16 amTook a look on the page you linked it says nothing about transactions locking anything, only the commands place locks eg the select will place a lock on the table and as long that server side cursor is open ee as long as you have grid showing data it will keep that lock regardless of the transaction it was in, so over all  it has no difference (on the locking that is) with the implicit transaction the server starts for you, as long as you use a new connection for each thread you should be fine. Yes, I am using one transaction for displaying data and another implicitly as sqldb will do for each delete/insert/update etc.