[Lazarus] Is there a way to NOT use a transaction in sqldb ?

[Michael Van Canneyt]

On Tue, 13 Oct 2009, Alexsander Rosa wrote:

> 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.