On Wed, Jan 04, 2006 at 01:08:47AM +0200, Krasimir Angelov wrote:

Hi John Goerzen,

I wonder which design decisions are causing you troubles. Could you explain this? All features which you mentioned can be added easily to HSQL as well. It is better to share the effort on single library rather than to have multiple similar libraries. I am willing to work on HSQL improvement.

Hi Krasimir, First off, thank you for all your work on HSQL. It is great to have a database layer like that for Haskell, and despite my troubles with it, I think you have done a wonderful service for the community. I continue to have HSQL code in production and have found it a useful tool. The final thing that prompted me to do this was that the PostgreSQL -- and possibly the Sqlite -- module for HSQL was segfaulting. I spent quite a bit of time with gdb and the HSQL code, and even with Simon Marlow's assistance, was unable to track down the precise cause. To make matters worse, the problem was intermittent. The Haskell program in question was pure Haskell, and switching it to HDBC solved this issue. I also had extremely high memory usage when dealing with large result sets -- somewhere on the order of 700MB; the same consumes about 12MB with HDBC. My guess from looking briefly at the code is that the entire result set is being read into memory up front. There were a number of other problems as well: * No support for prepared queries or for supplying replacable parameters. (Supported everywhere in HDBC, which removes the need to have escaping.) That's really my #1 complaint (well, aside from the segfaulting <g>). * Escaping function was global, rather than per-DB, which caused some trouble with Sqlite3 at least. (See SF bug 1324873 that I submitted on Oct. 12 with no replies since then) * No way to retrieve result data by column index instead of column name * HSQL provided no way to see the result set as a lazy list, and the public API provided no way to implement that. (There is 'fetch', but it seems that the entire result set was read into memory in advance anyway.) * The code wasn't very easy to understand. (This may be just me though.) * Unclear semantics in multithreaded programs. * No testsuite. I knew I couldn't fix it the right way in HSQL (since I had trouble following the code), and it also seemed like these weren't high-priority issues for you. (No blame here; it's the same way for me with the code I maintain. I can't expect you to fix my bugs in something that's free.) In hindsight, I should have contacted you first, and I apologize for not doing that. I just sorta sat down to design a DB API that I'd like, and pretty soon had a working prototype, and then some drivers... I'm dangerous when I'm on vacation ;-) I'm not quite sure where to go from here. Both packages have features that the other lack. I don't think that it's possible to merge all the HDBC features into HSQL without a major API and architecture refactoring. The HSQL features that HDBC lacks are mostly in progress already, and I've tried to design the HDBC API with them in mind. So, I'd invite you to take a look at the HDBC API at http://darcs.complete.org/hdbc/doc/Database-HDBC.html and let me know how you think we might be able to collaborate. If nothing else, I'm sure we can share ideas. (Some of that you'll see in HDBC, I'm sure.) Perhaps we could even have a HDBC backend for HSQL and vice-versa. -- John

On 2006-01-04, Krasimir Angelov <kr.angelov@gmail.com> wrote:

2006/1/4, John Goerzen <jgoerzen@complete.org>:

...

The final thing that prompted me to do this was that the PostgreSQL -- and possibly the Sqlite -- module for HSQL was segfaulting. I spent quite a bit of time with gdb and the HSQL code, and even with Simon Marlow's assistance, was unable to track down the precise cause. To make matters worse, the problem was intermittent. The Haskell program in question was pure Haskell, and switching it to HDBC solved this issue.

Can you send me a short code that causes the segfaulting?

Unfortunately, I was never able to reproduce the problem with some shorter code. It seemed to only crop up after things had been running a few minutes with heavy database activity. It also didn't crop up from the very beginning. There's a thread about it from the ghc list here: http://thread.gmane.org/gmane.comp.lang.haskell.glasgow.user/8802 At first, I had thought the problem was with GHC, but that turned out to not be the case. You can get the code with: darcs get --partial '--tag=Last rev before switch to HDBC' \ http://darcs.complete.org/gopherbot

...

I also had extremely high memory usage when dealing with large result sets -- somewhere on the order of 700MB; the same consumes about 12MB with HDBC. My guess from looking briefly at the code is that the entire result set is being read into memory up front.

I can't understand this. The result set isn't read in memory unless you want do to it. If you are using collectRows then yes, you will end with the entire result set read in memory, but you can use fetch to read the set row by row as well. The forEachRow function is also helpfull in this case.

I was not using collectRows. In fact, I was using fetch only.

...

There were a number of other problems as well:

* No support for prepared queries or for supplying replacable parameters. (Supported everywhere in HDBC, which removes the need to have escaping.) That's really my #1 complaint (well, aside from the segfaulting <g>).

This one is really the most critical to me. While I haven't optimized any of the HDBC drivers for it yet, it is already helpful simply from a coding perspective (it's a lot easier for me to pass a list of values than bother with generting these large query strings.)

...

* No way to retrieve result data by column index instead of column name

This is already available in CVS.

Very nice.

...

* HSQL provided no way to see the result set as a lazy list, and the public API provided no way to implement that. (There is 'fetch', but it seems that the entire result set was read into memory in advance anyway.)

This is intentional. The trouble is that you can close the database connection before to evaluate the entire lazy list. This is different

That is true. Of course, one can close a DB connection and try to call fetch on an open statement later as well. I have documented the proper semantics on the HDBC API docs.

...

So, I'd invite you to take a look at the HDBC API at

http://darcs.complete.org/hdbc/doc/Database-HDBC.html

I saw the API and some of the code. It is quite similar to HSQL in some cases but also differs in other. I saw that you are always fetching the entire row in list of SqlValue. I decided to provide function to get the values one by one because usually I preffer to represent each row as application specific data type. In your case you

It's probably the same amount of coding either way: fetch sth h1 <- get sth "col1" h2 <- get sth "col2" func h1 h2 vs. l <- fetchRow sth let h1 = l !! 0 let h2 = l !! 1 func (fromSql h1) (fromSql h2) Performance will probably be roughly similar too, since HDBC isn't having to map names to numbers. Of course, one could also do: [h1, h2] <- fetchRow sth with HDBC. Personally, I like the latter because it feels more "Haskelly" and pulls more out of the IO monad. Though of course it would be possible to do it both ways with both APIs. -- John

The point isn't in the amount of coding but in the performance. It isn't required to build intermediate data structures. 2006/1/4, John Goerzen <jgoerzen@complete.org>:

On 2006-01-04, John Goerzen <jgoerzen@complete.org> wrote:

...

It's probably the same amount of coding either way:

[hsql-esque example]

...

fetch sth h1 <- get sth "col1" h2 <- get sth "col2" func h1 h2

I should add that yet another option with HDBC is:

l colname colmap = Map.lookup colname colmap >>= fromSql

row <- fetchRowMap sth h1 <- l "col1" row h2 <- l "col2" row func h1 h2

So I think you should be able to use HDBC as-is in that usage scenario pretty easily.

I could add a function like "l" to HDBC if people think it would be generically useful.

-- John

_______________________________________________ Haskell mailing list Haskell@haskell.org http://www.haskell.org/mailman/listinfo/haskell

On 2006-01-04, Krasimir Angelov <kr.angelov@gmail.com> wrote:

...

I also had extremely high memory usage when dealing with large result sets -- somewhere on the order of 700MB; the same consumes about 12MB with HDBC. My guess from looking briefly at the code is that the entire result set is being read into memory up front.

I can't understand this. The result set isn't read in memory unless you want do to it. If you are using collectRows then yes, you will end with the entire result set read in memory, but you can use fetch to read the set row by row as well. The forEachRow function is also helpfull in this case.

After looking at the code again, it's possible that it's because you're never calling pqClear on the result set. So the results returned by PostgreSQL linger in memory forever. I did have memory issues with Sqlite3 as well, but a quick inspection isn't finding an obvious culprit. I use ForeignPtrs everywhere in HDBC to try to make sure that nothing like this happens, and also that The Right Thing happens if a database handle gets garbage collected without being explicitly closed first. There's a small C wrapper in each database driver to help ensure that nothing ever gets finalized more than once. -- John

On 2006-01-04, Krasimir Angelov <kr.angelov@gmail.com> wrote:

...

I use ForeignPtrs everywhere in HDBC to try to make sure that nothing like this happens, and also that The Right Thing happens if a database handle gets garbage collected without being explicitly closed first.

I prefer not to rely on ForeignPtrs. It isn't guaranteed that they will be run. Another problem is that the order in which finalizers are executed isn't specified. In this case the connection handle can be

Well, yes and no. It would be impossible to garbage collect (and thus finalize) any object for which references to it still exist. Statement handles in HDBC maintain references to the database handle pointers, either directly or indirectly, so I can't see how it is possible for a database handle to be finalized before the statement handle in this situation.

closed before the statement handle. The usage of raw pointers has the advantage that it is easier for the developer to see that there is a space leak.

The advantage of ForeignPtrs is that it's almost impossible for a space leak to exist in the first place ;-)

I saw that you are using unsafe foreign imports everywhere in Database.HDBC.PostgreSQL. The trouble with them is that all Haskell threads will be suspended during the call. Maybe this explains why you don't see the crash which you saw with HSQL. I still don't know where was the problem with HSQL.

I think you have that backwards, but I'm unsure. According to the FFI spec, section 3.3: "Optionally, an import declaration can specify, after the calling convention, the safety level that should be used when invoking an external entity. A safe call is less efficient, but guarantees to leave the Haskell system in a state that allows callbacks from the external code. In contrast, an unsafe call, while carrying less overhead, must not trigger a callback into the Haskell system." There is no reason for any of these calls to trigger a callback into Haskell, so they can all be imported unsafe for greater efficiency. But it doesn't directly address threads, so I don't know what to make of that. Do you have a reference? -- John -- John Goerzen <jgoerzen@complete.org> GPG: 0x8A1D9A1F www.complete.org "Value your freedom, or you will lose it, teaches history. `Don't bother us with politics,' respond those who don't want to learn."

John Goerzen wrote:

I think you have that backwards, but I'm unsure. According to the FFI spec, section 3.3:

"Optionally, an import declaration can specify, after the calling convention, the safety level that should be used when invoking an external entity. A safe call is less efficient, but guarantees to leave the Haskell system in a state that allows callbacks from the external code. In contrast, an unsafe call, while carrying less overhead, must not trigger a callback into the Haskell system."

There is no reason for any of these calls to trigger a callback into Haskell, so they can all be imported unsafe for greater efficiency.

But it doesn't directly address threads, so I don't know what to make of that. Do you have a reference?

In GHC, other threads cannot run while an unsafe foreign call is in progress. Our documentation is slightly lacking here, but this paper describes it all: http://www.haskell.org/~simonmar/papers/conc-ffi.pdf If you have foreign calls which might block or just take a long time, it is good practice not to mark them unsafe. Cheers, Simon

Benjamin Franksen wrote:

On Wednesday 04 January 2006 20:13, John Goerzen wrote:

...

Well, yes and no. It would be impossible to garbage collect (and thus finalize) any object for which references to it still exist. Statement handles in HDBC maintain references to the database handle pointers, either directly or indirectly, so I can't see how it is possible for a database handle to be finalized before the statement handle in this situation.

Hi John,

I fear it /is/ possible. This is a very unfortunate situation and one I had quite some difficulties to understand, when Simon Marlow explained it to me.

The problem is that finalization of the statement handle might be delayed indefinitely. The data dependencies between statement and connection handle only ensures that whenever the statement handle is alive, then too is the connection handle. But it does not say anything about what happens in which order after /both/ are dead (garbage). As soon as the connection handle to garbage, too, bothe handles can be finalized in /any/ order.

As I pointed out before, this is a very bad thing, because it makes finalizers a whole lot less useful than they could be if an order between finalizations could be specified (directly or indirectly). The arguments against such a solution are mostly: (1) it is difficult to implement efficienty and (2) the programmer could accidentally cause finalizer deadlocks by specifying circular dependencies.

Ben

This is also mentioned in the documentation: http://www.haskell.org/ghc/docs/6.4.1/html/libraries/base/Foreign-ForeignPtr...

touchForeignPtr :: ForeignPtr a -> IO ()

This function ensures that the foreign object in question is alive at the given place in the sequence of IO actions. In particular withForeignPtr does a touchForeignPtr after it executes the user action.

Note that this function should not be used to express liveness dependencies between ForeignPtrs. For example, if the finalizer for a ForeignPtr F1 calls touchForeignPtr on a second ForeignPtr F2, then the only guarantee is that the finalizer for F2 is never started before the finalizer for F1. They might be started together if for example both F1 and F2 are otherwise unreachable, and in that case the scheduler might end up running the finalizer for F2 first.

In general, it is not recommended to use finalizers on separate objects with ordering constraints between them. To express the ordering robustly requires explicit synchronisation using MVars between the finalizers, but even then the runtime sometimes runs multiple finalizers sequentially in a single thread (for performance reasons), so synchronisation between finalizers could result in artificial deadlock.

Keean et al, On Sun, 2006-01-08 at 14:51 +0000, Keean Schupke wrote:

My solution to this when developing a database library for my own use was to define the API in a bracket notation style, and only provide safe functions. The idea is that the function obtains the resource, calls a function passed as an argument, then frees the resource, so all resouces are guaranteed to be freed in the correct order... for example:

dbConnectWith :: DbName -> (DbHandle -> IO Result) -> Result dbConnectWith name workFn = do handle <- dbConnectTo name workFn handle `finally` dbDisconnect handle

In this way you avoid finalizers... and everthing is safe providing you only export the "with" style functions from the library... Here's an example from the library, the connect function:

I suppose you meant to write "result" rather than "Result". This style of functions is only safe if the user ensures that DbHandle is never returned as part of the result. You should have that in your documentation. As far as I can tell, the only general solution is to use finalizers and, if you really need to enforce a sequence of finialization, touchForeignPtr. A practical issue with touchForeignPtr is that it cannot be conveniently called from another finalizer, since the latter live in C. I can think of three solutions: a) You keep all pointers to your database (handles, connections, etc) in one C structure and keep one ForeignPtr to that. On deallocation, you call a custom C function that frees all the objects in the C data structure in the right sequence. b) You wrap all C data structures in reference-counting objects. On creation of a connection you increment the reference count of the database. Each foreign pointer you create will also increment the reference count. On deallocation of either, the reference count is decremented and the object freed if it has dropped to zero. c) In your special case you could wrap the data base in a StablePtr whenever you create the first connection and subsequently count the number of connections you create. You free the StablePtr when there are no more connections. Solution b) might be impossible to implement if the library itself frees the pointers without consulting your reference count first. If the library frees objects without asking you first, but allows some sort of finalizers, then solution a) makes it possible to at least mark the object as invalid (which all your Haskell functions would have to check). Otherwise solution a) is a bit inefficient, especially if your library calls Haskell functions and passes points to them -- in this case you need to check if this points is already in your data structure and insert it if necessary. Solution c) might be the easiest in your case where the data base always outlives connections and pointers to connections are never passed to Haskell except when they are created. Axel.

Chris Kuklewicz wrote:

...

Benjamin Franksen wrote:

...

On Wednesday 04 January 2006 20:13, John Goerzen wrote:

...

Well, yes and no. It would be impossible to garbage collect (and thus finalize) any object for which references to it still exist. Statement handles in HDBC maintain references to the database handle pointers, either directly or indirectly, so I can't see how it is possible for a database handle to be finalized before the statement handle in this situation.

Hi John,

I fear it /is/ possible. This is a very unfortunate situation and one I had quite some difficulties to understand, when Simon Marlow explained it to me.

The problem is that finalization of the statement handle might be delayed indefinitely. The data dependencies between statement and connection handle only ensures that whenever the statement handle is alive, then too is the connection handle. But it does not say anything about what happens in which order after /both/ are dead (garbage). As soon as the connection handle to garbage, too, bothe handles can be finalized in /any/ order.

As I pointed out before, this is a very bad thing, because it makes finalizers a whole lot less useful than they could be if an order between finalizations could be specified (directly or indirectly). The arguments against such a solution are mostly: (1) it is difficult to implement efficienty and (2) the programmer could accidentally cause finalizer deadlocks by specifying circular dependencies.

Ben

This is also mentioned in the documentation:

http://www.haskell.org/ghc/docs/6.4.1/html/libraries/base/Foreign-ForeignPtr...

...

touchForeignPtr :: ForeignPtr a -> IO ()

This function ensures that the foreign object in question is alive at the given place in the sequence of IO actions. In particular withForeignPtr does a touchForeignPtr after it executes the user action.

Note that this function should not be used to express liveness dependencies between ForeignPtrs. For example, if the finalizer for a ForeignPtr F1 calls touchForeignPtr on a second ForeignPtr F2, then the only guarantee is that the finalizer for F2 is never started before the finalizer for F1. They might be started together if for example both F1 and F2 are otherwise unreachable, and in that case the scheduler might end up running the finalizer for F2 first.

In general, it is not recommended to use finalizers on separate objects with ordering constraints between them. To express the ordering robustly requires explicit synchronisation using MVars between the finalizers, but even then the runtime sometimes runs multiple finalizers sequentially in a single thread (for performance reasons), so synchronisation between finalizers could result in artificial deadlock.

_______________________________________________ Haskell mailing list Haskell@haskell.org http://www.haskell.org/mailman/listinfo/haskell

_______________________________________________ Haskell mailing list Haskell@haskell.org http://www.haskell.org/mailman/listinfo/haskell

On Mon, 2006-01-09 at 11:33 +0100, Benjamin Franksen wrote:

On Monday 09 January 2006 10:03, Axel Simon wrote:

...

On Sun, 2006-01-08 at 14:51 +0000, Keean Schupke wrote:

...

My solution to this when developing a database library for my own use was to define the API in a bracket notation style, and only provide safe functions. The idea is that the function obtains the resource, calls a function passed as an argument, then frees the resource, so all resouces are guaranteed to be freed in the correct order... for example:

dbConnectWith :: DbName -> (DbHandle -> IO Result) -> Result dbConnectWith name workFn = do handle <- dbConnectTo name workFn handle `finally` dbDisconnect handle

In this way you avoid finalizers... and everthing is safe providing you only export the "with" style functions from the library... Here's an example from the library, the connect function:

I suppose you meant to write "result" rather than "Result". This style of functions is only safe if the user ensures that DbHandle is never returned as part of the result. You should have that in your documentation.

I wanted to mention this too, but you were quicker ;)

...

As far as I can tell, the only general solution is to use finalizers and, if you really need to enforce a sequence of finialization, touchForeignPtr.

Repeat: touchForeignPtr can NOT be used to enforce finalization order.

Ok, I re-read that thread. I agree. However, it is likely that the library you're using allows connections to be freed after the database is freed. If not, you can still all three methods I posted to get around the problem: Each connection needs to ref the database pointer and deref that pointer when the finalizer is run. That way, the database is never dead until all finalizers of all connections are run.

...

A practical issue with touchForeignPtr is that it cannot be conveniently called from another finalizer, since the latter live in C.

What do you mean "live in C"? Can't or shouldn't finalizers be written in Haskell, too?

Well, before ghc 6.0 and the "new FFI", finalizers where Haskell functions. Since this was tricky to implement, so finalizers are now C functions. This has other implications, e.g. you can't pass a closure of several pointers to the C function, the C finalizer will just get the one pointer it wraps. Axel.