I'm using Lazarus from SVN with FPC 2.2.4 under Ubuntu 9.04 connecting to an 8.3 PosgreSQL server.

I need to use it in a program that stays open all day -- it's a price check terminal. The user can read a barcode to show data about a single product or type part of a description and browse the resulting list. This list is a DBGrid whose TSQLQuery's DataSet stays open while the user browses (or simply abandon the terminal). It's a SELECT that does not change anything, it does not need a transaction. With the required transaction, there's always an "<IDLE> in transaction" query open when the Lazarus application is running:

select datname, usename, query_start, procpid, client_addr from
pg_stat_activity where current_query = '<IDLE> in transaction';

This transaction locks the changes in the database structure so you can't, for example, create a new table with a foreign key. A mere CREATE TABLE with a FOREIGN KEY referring to an existing table would take forever waiting for that lock to be released.

Did you try calling "CommitRetaining" on the transaction ? It will commit the transaction (and so free the lock), but should keep the dataset open ?

If you want the transaction closed (no transaction active), then you'll have to transfer the results of the query to a list (TCollection) and show the collection in the grid, because committing the transaction closes any dataset associated with it.

Michael.

It's weird to *require* a COMMIT on a simple SELECT, but the CommitRetaining trick seemed to work. The system table "pg_stat_activity" still shows the "<IDLE> in transaction" pending query but the lock is no more -- at least I could execute a CREATE TABLE normally.

2009/10/13 Michael Van Canneyt

Alexsander Rosa wrote

It's weird to *require* a COMMIT on a simple SELECT,

That's not strange. You can't even access Postgres (at any way) without a transaction. So you always have to start and end the transaction.

but the CommitRetaining trick seemed to work. The system table "pg_stat_activity" still shows the "<IDLE> in transaction" pending

That's because a new transaction is started immediately by CommitRetaining.

query but the lock is no more -- at least I could execute a CREATE TABLE normally.

What you should really do is to set the right transaction isolation level.

Joost

Alexsander Rosa wrote

This transaction locks the changes in the database structure so you can't, for example, create a new table with a foreign key. A mere CREATE TABLE with a FOREIGN KEY referring to an existing table would take forever waiting for that lock to be released.

It will not help you with FCL sqldb, anyway, this is how it works in MSEgui: tmsesqltransaction.options has tao_fake flag, there will be no transaction commands sent to the server if activated.
tmsesqlquery.controller.options has flag dso_offline, the DB connection can be closed after fetching the data if set. It is possible to reconnect and write the changes to the DB later.

Martin

Comments follow.

2009/10/14 Joost van der Sluis

I would disagree -- it's possible to access PostgreSQL without a transaction. I've been using libpq for ages, accessing directly from C/C++ code and starting transactions only for I/U/D commands.

Ok.

Postgresql has only two isolation levels, and i'm using the default "read commited" level. There's no "read uncommited" level, as the other one is "serializable" that would only make things worst. I think we should not *require" a transaction for every command -- the programmer should be able to choose when to use them.

Joost

Just open the connection, get your query results then close the connection, every time you query, do not keep the connection to database opened while you application run.

Just

En/na Zaher Dirkey ha escrit:

Just open the connection, get your query results then close the connection, every time you query, do not keep the connection to database opened while you application run.

Data aware components need an open connection to show data. Incidentally, I had problems regarding transactions trying to use sqldb with sqlite (a similar problem than the one Alexsander reported, i.e. it constantly maintained an open transaction precluding other connections from updating the db).
I hadn't much time to analyze the issue then and I just switched to zeosdbo.

Bye

2009/10/15 Luca Olivetti :

Data aware components need an open connection to show data.

And why moving away from data aware components is a good idea. We have done it three years ago. I then implemented a framework (model-gui-mediator ) of basic design patterns like Mediator and Observer to make standard non-db components "object aware".

No descendant or custom components required, no duplication of new features in db and non db component, etc...

Michael van Canneyt has taken my idea even further by showing a prototype implementation where completely replacing data-aware components is possible with a easy transition. Something I still think needs to be looked and considered further. Just thought I would mention it.

Incidentally, I had problems regarding transactions trying to use sqldb with sqlite (a similar problem than the one Alexsander reported, i.e. it

I use sqldb with Firebird and have no issues with blocking transactions etc.. So if there is a problem, it's limited to the abstraction of a few backend databases like sqlite etc, but definitely not all backends supported by sqldb.

So your suggestion is to ditch sqldb and switch to zeosdbo instead of try to fix sqldb?**

2009/10/15 Luca Olivetti

Firebird has multiple transactions for one connection not like Postgre, sqldb make postgre connection for every sqldb Transaction. so stoping the transaction and restart again or make CommitRetian must resolve that problem check "pgconnection.pp" procedure CommitRetaining.

En/na Alexsander Rosa ha escrit:

So your suggestion is to ditch sqldb and switch to zeosdbo instead of try to fix sqldb?//

No, mine was just an observation.
I'd much prefer to use sqldb, since it's included with lazarus and designed with fpc in mind, instead of an external package. Maybe taking a look at how zeos manages transactions and connections can give some clue on how to "fix" sqldb (provided it needs fixing), or maybe their philosophies are just different and you have to adapt on the shortcomings of either.

Bye