Before anything else I'd like to say that I'm entirely familiar with transactions in the SQL context. However I've been having a bit of difficulty sorting out the relationship between a TPQConnection, multiple TSQLQuery, and transaction object(s).

I'm roughing out a program which I am trying to keep responsive even if getting updated data from the backend using SQL is slow. To do that I've got two TSQLQuery objects, one of which can be queried by code which updates the UI and the other of which will be updated the next time a query is to be run (at which point they'll be swapped).

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.

Am I correct in my understanding that in order to close the transaction I have to set Action (e.g. to caRollback) and then Active to False? It appears that if I don't do this the query never sees data being updated at the backend.

To support the two TSQLQuery objects, do I need one or two TSQLTransaction objects? If I have to have two TSQLTransaction objects, does that imply that I also have to have two TPQConnection objects?

This was the main reason I gave up on sqldb and moved to Zeos.

2010/11/23 Mark Morgan Lloyd

Mark Morgan Lloyd wrote

Before anything else I'd like to say that I'm entirely familiar with transactions in the SQL context. However I've been having a bit of difficulty sorting out the relationship between a TPQConnection, multiple TSQLQuery, and transaction object(s).

In the case of Postgres it's a little bit hard to understand, because the transaction-paradigm of Firebird/Interbase is used. Postgres' transaction can be used in this manner, but you have to know how.

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.

Am I correct in my understanding that in order to close the transaction I have to set Action (e.g. to caRollback) and then Active to False? It appears that if I don't do this the query never sees data being updated at the backend.

To close the transaction, use .commit or .rollback. (In your case .rollback)

That the query isn't updated, isn't that because it's cached? Did you close and reopened the query?

If that does not help, you have to set the proper
transaction-properties, so that it can read data committed in other transactions.

To support the two TSQLQuery objects, do I need one or two TSQLTransaction objects? If I have to have two TSQLTransaction objects, does that imply that I also have to have two TPQConnection objects?

Just one.

Joost.

Just got there- I'd forgotten that one :-)

Yes, I was always seeing the same data until I started forcibly closing the transaction. When I queried server statistics I could see that the same transaction had been active for an extended period, and when running manual tests (using the psql utility) I determined that that was the way that PostgreSQL behaved.

Thanks, working on it.

I think the statement "without a transaction, Postgres will do nothing, ever". For example: you can write a small C program using libpq that executes a simple SELECT without any transaction SQL code. Here's a comment from PostgreSQL's website [1]:

/*
* Our test case here involves using a cursor, for which we must be inside
* a transaction block. We could do the whole thing with a single
* PQexec() of "select * from pg_database", but that's too trivial to make
* a good example.
*/

Been there, done that. I've been programming C + libpq for almost a decade now. There's another example at another website that do not use transactions at all: http://momjian.us/main/writings/pgsql/writing_apps/

[1] http://www.postgresql.org/docs/current/static/libpq-example.html

2010/11/23 Joost van der Sluis

Alexsander Rosa wrote

I think the statement "without a transaction, Postgres will do nothing, ever". For example: you can write a small C program using libpq that executes a simple SELECT without any transaction SQL code. Here's a comment

[...]
It uses an implicit transaction. MSEgui version of tsqltransaction has the flag tao_fake in options property which enables implicit transactions for database connections which support them, for Firebird it is not supported. MSEgui version of tsqldataset has the flag dso_offline which disconnects from transaction after loading the data. It is possible to reconnect in order to write the update packages to database.

Martin

Thanks Martin, noted.

Implicit transaction even in C using libpq, even for a mere SELECT? Are you sure?

2010/11/23 Mark Morgan Lloyd

Alexsander Rosa wrote

Implicit transaction even in C using libpq, even for a mere SELECT? Are you sure?

Yes:
http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html "
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. "
Martin

But it is transparent to the libpq programmer; why it's not transparent with sqldb?

2010/11/24 Martin Schreiber

Alexsander Rosa wrote

But it is transparent to the libpq programmer; why it's not transparent with sqldb?

Correction: it can be transparant in libpq.

But we can make it so in SQLDB.

The SQLDB model is in fact modeled after Firebird. Firebird offers more control over the transactions.

We did not want to take away this possibility, so we modeled sqldb on the most powerful RDBMS. This adds some overhead for the others.

What we neglected to do is add what Martin added: offer a less intrusive way for the programmer to use it (i.e. create a default transaction in the background if none is specified, and close the transaction once the data is read).

But we'll do that too, all in good time.

If someone is in a hurry, patches are definitely accepted.

Michael.

Could you point which files should be patched?

2010/11/24 Michael Van Canneyt

Alexsander Rosa wrote

Could you point which files should be patched?

I think it could be done by simply patching sqldb.pp itself.

My suggestion:

1. Add an option 'autotransaction' to TSQLConnection. Best in the form of

    TSQLConnectionOption = (coAutoTransaction);
    TSQLConnectionOptions = set of TSQLConnectionOption;
    Property TSQLConnection.ConnectionOptions = TSQLConnectionOptions

2. Add code to TSQLQuery to automatically create a TSQLTransaction (owned and available as function from TSQLConnection) if none is assigned explicitly and coAutoTransaction is in TSQLConnection.ConnectionOptions. (create an internal TSQLAutoTransaction component, and create that)
3. If such a transaction is created (you can check if the class of the transaction is TSQLAutoTransaction), do a commit after all Executed statements.

If it is done like this, the behaviour can be refined later on with more options.

Michael.

Alexsander Rosa wrote

But it is transparent to the libpq programmer; why it's not transparent with sqldb?

It is, with tao_fake set (MSEgui). The different DB's have very different transaction models. As Michael writes, sqldb is designed to get the full power of all DB's.
Firebird has the ability for 'named transactions'. It is possible to run multiple transactions in parallel on a single connection. PostgreSQL does not offer this possibility. In PQ there is for every transaction a separate connection and even an additional server process AFAIK! In MSEgui tsqlquery can have different transactions for reading and writing. So the longtime read transaction can be a cheap readonly transaction and the expensive write transaction must be active a short time only, this saves server resources.
I don't know if ZEOS provides the possibility to work with different transactions on a single connection.

Martin

Martin Schreiber wrote

Firebird has the ability for 'named transactions'. It is possible to run multiple transactions in parallel on a single connection. PostgreSQL does not offer this possibility. In PQ there is for every transaction a separate connection and even an additional server process AFAIK!

It was the multiplicity of handles- a long-term one for the connection object and short-term ones for transaction objects- that gave me problems with listen/notify.

I've got quite a lot of Delphi code here that relies on transactions and (in a limited number of cases) table/row locking. It's going to be "interesting" porting that to Lazarus.

MSEgui uses the handle of the connection component for the first PQ transaction. Additional connections are opened if parallel transactions are started. I don't know if the original FPC sqldb works the same.

Martin

I've ported my OPF from DBExpress to Zeos IFDEF'ing less than 10 lines. As DBExpress, Zeos supports a single transaction per connection. My OPF uses a single connection with 3 TSQLQuery/TZQuery for different purposes. It's very rare to have the actual need of more than one transaction per connection -- in these cases, it's simpler to create another connection.

2010/11/25 Martin Schreiber

The idea for SQLDB's architecture comes from IBX, not DBExpress. This explains the difference and your 'difficulties'.

Michael.

Which probably is less performant with Firebird...

Well, DBExpress is the "de facto" standard for Delphi apps since D6. IMHO sqldb should have used its architecture as inspiration.

2010/11/25

Alexsander Rosa wrote

Well, DBExpress is the "de facto" standard for Delphi apps since D6. IMHO sqldb should have used its architecture as inspiration.

It was. Partially.

Because it doesn't offer full transaction support for Firebird, and I require that, I changed the design there.

Not everything Borland does is good by definition :-)

Michael.

Op 2010-11-25 18:29, michael.vancanneyt at xxxxx het geskryf:

Not everything Borland does is good by definition :-)

In you subconscious you should have known I was going to reply to a statement like that. The temptation is just too much for me to resist. :) All I can say is:

Thank goodness!!! Now spread the word. ;-)

If I wanted 100% Delphi, I would simply have stayed with Delphi. Free Pascal (and Lazarus) are mature enough to innovate on their own. Take you strengths and run with it! Stop being held back by commercial software that is only driven by money and what Microsoft preaches.

Regards,
- Graeme -

Don't get me wrong, I'm all for innovations in Free Pascal/Lazarus. However, in this particular case, I don't think the "multiple transactions per connection" approach, supported only by selected databases, is the best solution. IMHO it should be an option, not the only imposed way.

I've tried to use sqldb but it adds too much unneeded overhead. Under PostgreSQL it actually locks the tables for DDL modifications by default, rendering the development machines ridden with "IDLE in transaction" connections while the apps are running, even for mere SELECT's. In these machines, it's normal to add columns and constraints to tables while people are coding and testing. To create a simple FK constraint I would need to kill all connections or ask to everybody stop working -- otherwise the CREATE CONSTRAINT will wait several seconds/minutes while the SELECT's are being made. If someone is programming an yearly report that takes one minute to run, everybody has to wait.

That's why I moved to Zeos.

2010/11/26 Graeme Geldenhuys

Alexsander Rosa wrote

Don't get me wrong, I'm all for innovations in Free Pascal/Lazarus. However, in this particular case, I don't think the "multiple transactions per connection" approach, supported only by selected databases, is the best solution. IMHO it should be an option, not the only imposed way.

Agreed. That's why I also want to provide the 'easy access' option.

But by making it as it is, we made sure from the start that we support the maximum options. If we had started with the DBExpress approach, the additional options would have been very unnatural and probably rather complicated.

Well, that's the easy way, which doesn't help us.
(I understand the move, if you're in need of a quick solution, this is not a reproach.)

In the long term, however, it is better to try
and convince us to make the model more open to other databases.

We will do so, but bugging us (or providing patches) is the best way of getting faster results.

Michael.

Michael Van Canneyt wrote

But by making it as it is, we made sure from the start that we support the maximum options. If we had started with the DBExpress approach, the additional options would have been very unnatural and probably rather complicated.

It's curious that Borland didn't similarly attempt to support all facilities provided by Firebird/Interbase. They probably forgot about it.

Which locks- Postgres or sqldb?

Allowing that what I'm doing now is the first time that I've played with the FPC/Lazarus database stuff "in anger", I'm generally pleased with what's available. However I'm fudging around with a thread to get updates from the table when notified so as not to risk having an unresponsive app if that takes a few seconds, what would be really useful from my point of view would be an idle callback from the connection or query object.

So far I'm not familiar enough with the system to volunteer to do the work.

2010/11/26 Mark Morgan Lloyd

PostgreSQL gets "locked", I mean, the tables being used by sqldb can't be modified (new columns, new constraints, etc) while the sqldb connection is active, even if it's a read-only SELECT. If someone is testing a heavy report the tables affected can't be changed by the DBA -- it's a major headache.

With DBExpress or Zeos this "problem" does no occur. They use the so-called "implicit transaction" is some different way that does not lock anything -- it seems they do NOT use any transaction at all unless explicitly requested, so the PostgreSQL database somehow creates one internally (does it?) that is different of the explicit ones, I do not know. All I know is this: DBExpress and Zeos work.

Alexsander Rosa wrote

With DBExpress or Zeos this "problem" does no occur. They use the so-called

Zeos ? This is simply NOT true, at least with Zeos 6.6.5. If you chosen correct tiTransactionIsolation (eg. tiReadCommited) then pgsql connections automatically opens transactions (so you have "idle in transaction") for nothing.I've already fixed that for myself, but that's "a real problem". sqldb have another problem because it opens another connection for query (so with 100 connections you can have 200 connections because each client started one query), so must think about it when setting up postgresql.conf (client connections param).

zeljko

I'm using it from SVN too.

2010/11/26 zeljko

Dear members:

i am new with the TDBF product and i have a lot of lacks in its use.

i have prepared an app. to record some information on the airplanes transportation, using as index the field "idBitacora".

it pretty works and finally we can see a LazReport listing all the entries from the first to the last.

Now I need to prepare some summaries with a different sort. When I work with linked lists, it was easy to obtain a sintetic "id" and Insert in the list sorted by this sintetic "id".

I think that the way is to copy the original table in another "tableTemp", add the new sintetic "id" declared as index and the new table will result sorted by the new sintetic "id".

My trouble is that I dont know how to do it.

I try to download a TDBF tutorial from SourceForge, but you know it is prohibed to us. If anyone have this document, I will appreciate very much the sending of it to my email.

Thanks very much for your help,

yours truly,

Ing. H?ctor F. Fiandor Rosario

Could you give some examples about this? postgres vs firebird?

Examples of what exactly ?

In Postgres (and in fact most other engines), you have essentially 1 simultaneous transaction per connection. (implicit or explicit). When running a query, you must simply specify the connection; The transaction context is automatically known. There is no way to alter it. Transaction control is limited to BEGIN; COMMIT; and SAVEPOINT. (a bit overimplified, but essentially that is it).

Firebird allows you to have multiple transactions simultaneously per connection. So when running a query, you must always specify
- Database handle
- Transaction handle
This is in fact very convenient in a multi-tier server app: There can be 1 connection to the database, but all logged-in users operate in their own transaction context.
User A commits something, and this has no effect whatsoever on what user B is doing.

Michael.

Alexsander Rosa wrote

But it is transparent to the libpq programmer; why it's not transparent with sqldb?

To learn people that they have to use transactions. There are only a very few cases in which you don't need a transaction.

But most programmers do a: not know how why transactions are there and how they should be used, or b: are too sloppy to use them properly.

That most people do not understand what a transaction is and where it is for, is demonstrated by you thinking that it was possible to do something with Postgres without a transaction. For each transactional database it is completely impossible to work without transactions. Or else it woudn't be a transactional database anymore.

I tried to explain this to you earlier, but you preferred to use Zeos. That's ok with me. But it would be better if you learn how to use transactions properly. (With Zeos or sqldb, doesn't matter)

Joost.

Problem is that I haven't found a good model for this. Martin's solution could be an idea, though.

It could help if someone could sketch the typical wanted behavior from the transactions.

Joost.

I sent an idea for an implementation in a reply to Alexsander Rosa. basically:

TConnectionOption = (coAutoTransaction)
TConnectionOptions = set of TConnectionOption;
Property TSQLConnection.options = TConnectionOptions;
Procedure TSQLConnection.CreateDefaultTransaction;

If coAutoTransaction is set, and TSQLQuery does not have a TSQLTransaction assigned, it creates one through TSQLConnection.CreateDefaultTransaction. (It should be of type TSQLAutoTransaction, a simple descendent of TSQLtransaction, so detection of 'default transaction' is simple)

Whenever a TSQLQuery statement is executed and the transaction of TSQLTransaction is of type TSQLAutoTransaction, then it automatically commits (more flags can be introduced for this).

Michael.

Thing is, that TSQLQuery by default fetches only the first 10 records, and then keeps the cursor open, so when you need more records, those are fetched too. Setting PacketRecords to -1 will make it fetch the complete then still, you have the data fetched in memory, so if you want to change the data, and apply changes, you have to keep the transaction open. (Well, it's up to you. But think through what you're doing)

And I assume that Postgres (don't remember exactly) can set the behavior of transactions. Apparently they are blocking by default.

Joost.

No, not the design. What I meant is to sketch what you want to do. Eg:

I open a dataset
A user changes some data
Data is saved

In this case it should be:

Open transaction
Open data
Edit data
Close data
Close/commit transaction

This can be done perfectly with sqldb. But which are the cases which are difficult?

Joost.

You mean "to TEACH people" and you assume too much about me.

2010/11/29 Joost van der Sluis

There aren't any. What you described above is what it is :-)

No, there are of course some tricky cases:

* There is currently no 'offline' state, in the sense that the transaction/connection can be closed as soon as you have fetched the data. There is no need to keep the transaction active. (coCommitOnFetch ?)
* In such a model, a transaction would need to be opened whenever updating the database. One can imagine this too when the transaction is kept open: use the same transaction as the select transaction, or use a separate transaction (coUpdateInPrivateTransaction).
* A tricky one is: what to do with 2 simultaneous queries A and B, both connected to the same database. If they share a transaction, then committing A will also Commit B. (which is what happens in DBExpress, BDE).
-> you could introduce a flag 'coPrivateTransactions' In short, there are many possibilities. But for starters, I would implement the case where all 'auto' TSQLQuery objects share the same 'default' transaction.

Michael.

I believe that it was I who introduced that initial misconception, the context being that I'd forgotten that PostgreSQL always had an implicit transaction. Hence the (tongue-in-cheek) subject line.

Various parameters to begin transaction, set transaction and lock. It's a few years since I've fiddled with these and I only did it by hand, i.e. fed explicit commands through BDE to the server.

If I could ask: where can I find a description of how TSQLQuery etc. should be used for non-trivial applications? For example, if I want to set up an explicit transaction, insert a few hundred rows of data, and then make an explicit decision on whether I commit or rollback?

I can see TSQLQuery.InsertSQL etc., but how should they be used? If I want to force a lock on a table how should I do it?

En/na Joost van der Sluis ha escrit:

This can be done perfectly with sqldb. But which are the cases which are difficult?

When you have a visual database component (e.g. a dbgrid) that needs the connection opened all the time (otherwise it wouldn't show any data) and locks the database for other uses.
Mind me, it happens also with zeos when you select a TransactIsolationLevel different than tiNone, but there you have the option. An use case I have (but it's not the only one) is a dbgrid opened on a read only query (i.e. just to show data), while all data modification is done with a different connection (that signals a refresh to the display one when data is changed).
I also don't understand the transaction model of zeos, so even if I'm using it, when I need transactions I issue them explicitly (DbConnection.ExecuteDirect('BEGIN')...DbConnection.ExecuteDirect('COMMIT')).

Bye

That's what I though. So setting the transaction isolation level is the issue. And the 'problem' is that sqldb uses another default transaction isolation setting then Zeos does.

Well, you can already set the transaction isolation setting with sqldb. Use TSQLTransaction.Params for that. I'll have to search what the precise setting is, though.

Joost.

This doesn't change Alexsander problem, because there's still the same transaction. Only difference is that he doesn't have to place it on the form.

Besides that, I think that disabling the explicit transaction for Postgres can be done much easier. I have to read the documentation again, but my guess is that if you pass a nil pointer for the transaction, it will use an implicit transaction. So the only change would be to remove the check if an exception is assigned...

But first I want to know what the problem is we solve with this. And if it's worth the effort/extra overhead.

Joost.

transaction.create
sqlquery.transaction := just created transaction.
sqlquery.sql := 'insert/update table ....'
for i := 0 to few hundred do
  sqlquery.execsql
transaction.commit/rollback

I can see TSQLQuery.InsertSQL etc., but how should they be used? If I want to force a lock on a table how should I do it?

In most cases you don't need them. The TSQLQuery is a TBufDataset. This dataset fetches data from a source. (In DBExpress this source is a dataprovider. In sqldb the source can be defined by a descendant, in this case the TSQLQuery. But other sources are possible. It would also be possible to create a descendant which uses a dataprovider-like component)

The dataset is kept/cached in memory. The changes are kept in an update-buffer.

When you call ApplyUpdates, all changes in the update-buffer are pushed to the underlying dataset. (Again, this is handled in a descendant, Delphi uses the dataprovider)

What TSqlQuery does when it has to push changes to the database-server, is creating update/delete/insert sql-statements for each change. Well, in fact it parses the provided SQL to see if it can create this statements (using parameters) automatically.

There is a way however to provide these update/insert/delete statements manually, that's where the .InsertSQL etc properties are for.

In fpc 2.4.2 and further the TBufDataset has one build-in mechanism of data retrieval, which is streaming to a binary format. Or, when you add the xmlstreaming unit, to the same xml format as TClientDataset uses.

Joost.

I thought that exactly this was his problem ? Namely, that he is forced to use a transaction component.

Besides that, I think that disabling the explicit transaction for Postgres can be done much easier.

Maybe, but that would be a postgres-only solution, which I don't think is good. I heavily oppose such a solution: SQLDB should behave the same on all databases.

My proposal would work on all platforms, and alleviates Alexsander's problem.

Michael.

No, his problem is that the table was locked. He thinks/though(?) that the reason for this is/was that he had to explicitly create the transaction component. While Zeos didn't use any transaction.

This is simply not true. The difference with Zeos is that Zeos by defaults opens a implicit transaction which is closed immediately after fetching the data. And all data is fetched at once, so the lock is there for only a very short time. So, depending on his implementation, he won't notice the lock, or it will pop up as a random bug in the application, which will only occur at some spare moments. (More under heavy load)

The other solution would be to change the transaction-isolation level. Maybe that Zeos by default also uses another transaction-isolation level.

I think we should not change a thing, until the problem is clear. Maybe we can come up with some 'general' transaction-isolation levels. The reason they are not implemented in a general way is that each DB has it's own locking-style.

Joost.

Here is nice explanation of isolation levels, "general" transaction-isolation levels should be implemented definitelly.
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

zeljko

I think I've got that. So TSQLQuery.ExecSql is good for statements that don't return a result set- I don't have to go down to the connection object to get this functionality.

So if I'm reading you correctly, ApplyUpdates in this case uses the .SQL property with parameters...

..while this case uses .InsertSQL etc. What determines which case applies- is it purely whether .InsertSQL etc. are defined?

Is there somewhere I can hook into the TSQLQuery to (a) see exactly what query was passed to the server (b) collect any error text and (c) get execution timing?

Don't worry about my problem, I'm not using sqldb anymore. http://www.google.com/search?q=lazarus+sqldb+zeos

2010/11/30 Joost van der Sluis

Well, these are tne ansi/sql transacion isolation levels. do you know a database which actually uses/supports these? (Except from Oracle)

It's very difficult to generalize this.

Joost.

Indeed.

Not exactly, it parses the .SQL property to obtain the tablename which has to be updated. Then it generates queries (one insert, one updte and one delete) like 'update tablename set field1=:field1, field2=:field2 where keyfield=:old_keyfield'.

Yes. If you define .InsertSQL it won't use it's own generated insert-sql.

Is there somewhere I can hook into the TSQLQuery to (a) see exactly what query was passed to the server (b) collect any error text and (c) get execution timing?

a) Easiest is to use the database-client's log mechanism. (If there is any). Else you can change the TPQConnection.Preparestatement function. The 'buf' parameter is the actual query. But as you'll see it also adds some more to the query, so it opens a cursor, before it is send to the database-server.

b) Ehm.. the error-text is in the exception?

c) Hook into TPQConnection.PrepareStatement and TPQCOnnection.Execute

Joost

Yes, PostgreSQL supports all of them.

zeljko

With the same names? I know from some other db-servers, that they use different names. Because by default they use some slightly different isolation levels, but offer the ansi/sql levels as 'compatibility' option. Sometimes you have to set some flags to get the proper behavior. (Most DB-vendors find their own isolation-levels better then the default, 'old', ansi-c ones)

Joost.

Yes.
http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html

Don't know what others do, but we can handle that somehow IMO.

zeljko

Thanks Joost, all noted.

I am a firm proponent of putting a unified mechanism for a) and c) in TSQLQuery/TSQLConnection, and have, in fact, a patch ready for this (it implements a simple event mechanism for the log).

I'm tired of re-implementing this for each project...

Michael.

That's a no-brainer. It could als use TCustomApplication.Log, if Customapplication is assigned. Or, add events before and after an statement is prepared, executed or unprepared. (And when data is fetched)

But should we add this code in the connection-objects for each db-type, or in the code in the sqldb unit?

Joost.

That'll be nice.

By now there's so much in PostgreSQL that I'm worried the developers will start running out of ideas.

Would it be possible to write a server-side PostgreSQL extension using Lazarus, including friendly debugging?
http://www.postgresql.org/docs/8.4/static/plhandler.html

Or maybe both? Certainly in the case of PostgreSQL both have handles provided by the underlying client library, those handles are useful for different things, hence it might be desirable to hook either or both even if in some cases there was duplication of output.

That would create a dependency on custapp. If it isn't there yet, I would not introduce it.

I have now

TDBEventType = (detPrepare,detExecute,detFetch,detCommit);
TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;

And in TSQLConnection:

   Property OnLog : TDBLogNotifyEvent Read FOnLog Write FOnLog;

But we can provide a unit that, when included, logs all queries to custapp through a global hook. In fact, I think that's a good idea.

If it is OK with you, I'll implement and commit it Friday ?

But should we add this code in the connection-objects for each db-type, or in the code in the sqldb unit?

In the sqldb unit, of course.

Michael.

[GRIN] Actually, it was a serious question: can Lazarus debug a .so and would it stand any chance of debugging one which had the sort of calling convention that Postgres expects an extension to have?

Speculating that Lazarus can, in fact, debug a .so and allowing that the extension would be invoked by a forked PostgreSQL process, I think the question boils down to whether Lazarus can attach to a process that it has not started up.

GDB has a command for it (attach) , so it should be possible.

Michael.

Potentially doable rather than currently possible. Presumably it would be a functional alternative to the Run button or equivalent menu entry.

Mark Morgan Lloyd wrote

> GDB has a command for it (attach) , so it should be possible. Potentially doable rather than currently possible. Presumably it would be a functional alternative to the Run button or equivalent menu entry.

MSEide has 'Target'-'Attach Process'. Implementation was difficult IIRC.

Martin

Could explain why it's not commonly implemented in debugger frontends.

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)

This is one of the things we must still address: close the transaction after the data has been fetched. (or rather: keep data available in case the transaction is closed)

Michael.

I've not had a chance to investigate exactly what the backend thinks Delphi etc. is doing, but long-lived connections caused in particular by db-aware components could be bad news when it comes to server pooling, failover and so on. Long-lived transactions definitely will be.

Only for certain DB engines. Firebird has no problem with it. I suspect it also depends on the transaction options (readcommitted etc.) unfortunately these highly depend on the engine.

But we're aware of the issue.

Michael.