[Lazarus] What is a TSQLTransaction and why do I need one?

[Mark Morgan Lloyd]

Joost van der Sluis wrote:

>> Since this is strictly read-only, I can't see why I need transactions, 
>> but I obviously respect the judgment of whoever designed the architecture.
> 
> Well, you always need a transaction. Without a transaction, Postgres
> will do nothing, ever.
> 
> I assume you are used to the fact that postgres automatically creates an
> transaction for you. With Sqldb you have to do this yourself.

Please excuse me for threading onto a fairly old message. In the 
specific case of PostgreSQL, if I use SELECT * FROM pg_stat_activity to 
examine backend state I can see that a Lazarus TSQLQuery, i.e. that has 
to have an associated transaction object, is explicitly marked as being 
in a transaction, while other methods of access (Delphi+BDE, PGAdmin3) 
are not.

The reason I mention this is that I find myself wondering whether a 
program written with Lazarus/FPC, particularly one that used db-aware 
components, could keep a table in a state where housekeeping activities 
(in particular vacuum) couldn't access it- I'm currently troubleshooting 
a similar problem created by other software.

The first row in the table below is associated with the connection 
object, the second is associated with a query which is being kept 
connected for a few minutes (I'm assuming that when it is inactivated 
the contents are inaccessible). The third isn't from Lazarus but from 
PGAdmin3. The table is select-only to both users.

  datid | datname  | procpid | usesysid | usename |     current_query   |
-------+----------+---------+----------+---------+---------------------+-
  32213 | open2300 |   23937 |    16397 | markMLl | <IDLE>              |
  32213 | open2300 |    4570 |    16397 | markMLl | <IDLE> in transactio|
  32213 | open2300 |   28688 |    16395 | jamesFP | <IDLE>              |

  waiting |          xact_start           |          query_start
---------+-------------------------------+-------------------------------
  f       |                               | 2010-12-08 09:14:21.051523+00
  f       | 2010-12-10 08:48:33.972094+00 | 2010-12-10 08:48:34.002003+00
  f       |                               | 2010-12-10 00:38:02.822295+00

|         backend_start         | client_addr  | client_port
+-------------------------------+--------------+------------
| 2010-12-08 09:14:20.41357+00  | 192.168.1.16 |       39868
| 2010-12-10 08:48:33.961539+00 | 192.168.1.16 |       51489
| 2010-10-05 12:32:48.512251+00 | 192.168.1.64 |       43538
(3 rows)

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]