Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Trevor Talbot wrote: It's a pre-commit hook, so it has the same problem as the triggers. I haven't tried modifying the code to add a post-commit hook, but sqlite3_commit_hook() is actually a good place to start: in main.c it sets db->xCommitCallback, which is called by vdbeCommit() in vdbeaux.c. It looks like you could add a call at the end of that function, after testing needXcommit. You'll still want the triggers to note what specific data changed, but you could simply have them store some state in a global variable that your new post-commit function checks to decide what semaphore to post to. Although I'm well up to the task technically, the idea of depending on a modified SQLite is ... unappealing. I think instead, I'll just keep using SQLite the way it was intended, and keep the external-process-event-signaling stuff where it belongs: not inside of the database manager. :) But should I change my mind, I'm gonna bookmark your email just in case! b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > If your platform has a file modification notification mechanism, you > > may be able to sleep on that instead. Of course the problem with this > > approach is that it's only a coarse-grained "something changed" > > notification, and doesn't tell you what changed. You may be able to > > counter that by having the trigger store a note about what changed in > > a separate table that your GUI queries when it wants to know > > specifics. > Perhaps, but that may just move the problem. The "GUI information" > table might not be committed before the GUI process wakes up to find out > what happened. If it's created in a trigger, it will be part of the same transaction as the updated data. The database file won't be modified until commit time. Depending on the platform, it may fire the notification before the commit has finished writing data, but since sqlite holds an exclusive lock on the file during that time, the GUI process will see SQLITE_BUSY and can simply sleep a short time and retry. You'd have to handle that case anyway, as another writer might start committing before the GUI process has a chance to respond to the previous notification. > > If you don't have a cheap file notification, something more creative > > might be necessary. Do you control the sqlite library used by the > > data writers? Perhaps you can modify it to provide notifications > > immediately after commit instead. (I'm assuming you don't want to > > modify the writers themselves directly, by having them signal after > > they issue a COMMIT.) > I control the code on both ends. I looked at the sqlite3 code, and the > places to modify didn't exactly jump off the page at me. :) > What about sqlite3_commit_hook()? Though the (very few) examples I've > managed to find don't make it clear how to figure out the rowid that was > updated. And without that, I can't easily determine which semaphore I > need to post to... Can I pass the database handle as the argument to > the callback? It's a pre-commit hook, so it has the same problem as the triggers. I haven't tried modifying the code to add a post-commit hook, but sqlite3_commit_hook() is actually a good place to start: in main.c it sets db->xCommitCallback, which is called by vdbeCommit() in vdbeaux.c. It looks like you could add a call at the end of that function, after testing needXcommit. You'll still want the triggers to note what specific data changed, but you could simply have them store some state in a global variable that your new post-commit function checks to decide what semaphore to post to. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
John Stanton wrote: Bill, You have an interesting situation, and a general one since you have data distributed across a network. I would be tempted to define a remote procedure call using TCP/IP or whatever and use that to notify database changes. Use a message passing approach. The remote GUI requirement is still a ways off, but I'll definitely look into what you're talking about when it gets closer. BTW, in a new system you would do well not to use the deprecated sqlite3_exec API call. In many places, I don't. But it's a darned convenient API for places where I know I'm going to use a SQL statement only once... b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Bill, You have an interesting situation, and a general one since you have data distributed across a network. I would be tempted to define a remote procedure call using TCP/IP or whatever and use that to notify database changes. Use a message passing approach. BTW, in a new system you would do well not to use the deprecated sqlite3_exec API call. Bill Gatliff wrote: John Stanton wrote: Perhaps your application sjould post its signal after the COMMIT has executed. A pause to give time for the COMMIT is a fragile approach. It is indeed! And just for the record, it's an approach that deserves absolutely no consideration by any system you want to depend on. I wasn't ever considering it, in fact. Just so I'm clear on that. You know, for the lawyers out there. :) In my application, I have a lot of databases and tables, but the file holding the data of interest to the GUI has other data in it as well, so waking up on file change e.g. select(2) or poll(2) will cause the GUI process to wake up far more often than it needs to. Combining a semaphore-in-trigger with a select() by the companion process doesn't work either, it turns out. Think about high system loads, where the wakeup from the semaphore posting is delayed until after the commit for that update has happened. Then a subsequent select() will sleep until somebody else touches the database, which might not happen for a while. Combine that with the fact that a select() might release because some non-GUI data was committed just before the GUI data--- but after the semaphore posting and wakeup, and I can't come up logic that doesn't get confused when things start to look like real-life. The solution seems to be to not use an AFTER UPDATE trigger to post to a semaphore used as a notifier for any other context. Period. (I think that with sqlite3_enable_shared_cache(), you could still use semaphore-in-trigger notifications for same-context connections, but I haven't tested this). I can live with this, it's a minor code change. And finally, the answer to the obvious question. There are several data generators in this system, and none of them has any awareness of the GUI. In fact, some embodiments of this system don't even have a GUI, and in others, the GUI lies across a LAN. So simply moving all the GUI-interesting data into its own database really isn't an option, though it would otherwise be the ideal solution because then I _could_ use select(2) et al. Right now, though, the schemas are designed to meet the needs of the system as a whole; the GUI has to take what's left. :) b.g. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
John Stanton wrote: Perhaps your application sjould post its signal after the COMMIT has executed. A pause to give time for the COMMIT is a fragile approach. It is indeed! And just for the record, it's an approach that deserves absolutely no consideration by any system you want to depend on. I wasn't ever considering it, in fact. Just so I'm clear on that. You know, for the lawyers out there. :) In my application, I have a lot of databases and tables, but the file holding the data of interest to the GUI has other data in it as well, so waking up on file change e.g. select(2) or poll(2) will cause the GUI process to wake up far more often than it needs to. Combining a semaphore-in-trigger with a select() by the companion process doesn't work either, it turns out. Think about high system loads, where the wakeup from the semaphore posting is delayed until after the commit for that update has happened. Then a subsequent select() will sleep until somebody else touches the database, which might not happen for a while. Combine that with the fact that a select() might release because some non-GUI data was committed just before the GUI data--- but after the semaphore posting and wakeup, and I can't come up logic that doesn't get confused when things start to look like real-life. The solution seems to be to not use an AFTER UPDATE trigger to post to a semaphore used as a notifier for any other context. Period. (I think that with sqlite3_enable_shared_cache(), you could still use semaphore-in-trigger notifications for same-context connections, but I haven't tested this). I can live with this, it's a minor code change. And finally, the answer to the obvious question. There are several data generators in this system, and none of them has any awareness of the GUI. In fact, some embodiments of this system don't even have a GUI, and in others, the GUI lies across a LAN. So simply moving all the GUI-interesting data into its own database really isn't an option, though it would otherwise be the ideal solution because then I _could_ use select(2) et al. Right now, though, the schemas are designed to meet the needs of the system as a whole; the GUI has to take what's left. :) b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Bill Gatliff wrote: The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. Is this expected behavior? Is there a better workaround than simply sleeping after getting unblocked? Am I missing an entirely different--- and better--- way of synchronizing my GUI to the database? Bill, As others have pointed out this is expected behavior. You should be able to set a flag in the update trigger, and then check that flag in a commit hook function (see http://www.sqlite.org/capi3ref.html#sqlite3_commit_hook). If the flag is set the commit hook can post to the semaphore and wakeup your GUI process, and then clear the flag. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Bill Gatliff wrote: Guys: I'm a relatively-new SQLite user, but I'm luuuvin' it! :) My application is a mobile platform with a GUI that wants to display frequently-updated data in a database. The GUI is a separate process from the one providing the data, and is one of several consumers of that data. I prefer not to poll for changes, because the system is performance-constrained. So instead, I'm using an AFTER UPDATE trigger in the data-generating process to launch a C function that posts to a semaphore. The GUI process is thus unblocked, and it then does a SELECT to get the data. I'm using the C/C++ API everywhere. The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. Is this expected behavior? Is there a better workaround than simply sleeping after getting unblocked? Am I missing an entirely different--- and better--- way of synchronizing my GUI to the database? Thanks! b.g. Perhaps your application sjould post its signal after the COMMIT has executed. A pause to give time for the COMMIT is a fragile approach. Can you use a message queuue to send the updated data to the companion process? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Have the trigger function set a flag or put an item in a work queue after it updates the database. After the commit, check/clear the flag or empty the work queue, raising the semaphore if necessary. --- Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data. > > I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
Trevor Talbot wrote: On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: I prefer not to poll for changes, because the system is performance-constrained. So instead, I'm using an AFTER UPDATE trigger in the data-generating process to launch a C function that posts to a semaphore. The GUI process is thus unblocked, and it then does a SELECT to get the data. I'm using the C/C++ API everywhere. The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. Bummer. :) If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. Perhaps, but that may just move the problem. The "GUI information" table might not be committed before the GUI process wakes up to find out what happened. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) I control the code on both ends. I looked at the sqlite3 code, and the places to modify didn't exactly jump off the page at me. :) Does sqlite3_enable_shared_cache() factor into this any? I turned it on, and it didn't seem to help. What about sqlite3_commit_hook()? Though the (very few) examples I've managed to find don't make it clear how to figure out the rowid that was updated. And without that, I can't easily determine which semaphore I need to post to... Can I pass the database handle as the argument to the callback? b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using POSIX semaphores and triggers to signal updates
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data. > I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem using POSIX semaphores and triggers to signal updates
Guys: I'm a relatively-new SQLite user, but I'm luuuvin' it! :) My application is a mobile platform with a GUI that wants to display frequently-updated data in a database. The GUI is a separate process from the one providing the data, and is one of several consumers of that data. I prefer not to poll for changes, because the system is performance-constrained. So instead, I'm using an AFTER UPDATE trigger in the data-generating process to launch a C function that posts to a semaphore. The GUI process is thus unblocked, and it then does a SELECT to get the data. I'm using the C/C++ API everywhere. The problem I'm seeing is that the GUI process is getting stale data in its SELECT, unless it does a brief sleep between the sem_wait() and the sqlite3_exec(). Specifically, the value returned is the value immediately before the UPDATE. It's as if the trigger in the data-generating process and the query in the GUI process are both running before the new value is actually committed to the database file and/or whatever caches are in between. Is this expected behavior? Is there a better workaround than simply sleeping after getting unblocked? Am I missing an entirely different--- and better--- way of synchronizing my GUI to the database? Thanks! b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
