[sqlite] Re: Longest "real" SQL statement
Sorry for the lateness of this reply. I don't have the query anymore (wrote it at my previous job), and it wasn't specifically for sqlite, but here goes. A company I worked for built a system to calculate and report various health statistics from a huge database which was itself a composite of two dozen or more other databases, collected by various authorities and organizations. As part of demonstration, I ended up writing a query that was roughly 100kbytes / 2500 lines. It had several nested subqueries, and did joins across dozens of tables and many dozens of columns. I think it calculated the teen pregnancy rate for a given community in a given year, IIRC :). Charles -- --- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
Joe Wilson <[EMAIL PROTECTED]> wrote: > > I'll continue investigating and try to get to the bottom of this. Thanks > > for the sanity-check. > > Might there be some autocommit/sql statement batching difference for the > driver or your app on different platforms? No; one of the beauties of developing in Python is that it minimizes the amount of OS-specific code you need to write. I have to apologize for the misdiagnosis. I accepted my colleague's diagnosis of "writes from writer not appearing in view of database from reader on Solaris only" at face value. After some hours of attempting to reproduce the problem, it now appears they were showing up all along. So: sqlite list 1, list subscriber 0. Thanks again, Joe. Charles -- ----------- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Reading across processes on Solaris
Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Charles Cazabon <[EMAIL PROTECTED]> wrote: > > > > On most platforms, the reader sees new data appear in the database > > periodically as the writer creates new records. But on Solaris, the > > reader never sees any updates -- it only ever sees whatever data was in > > the database when the reader first opened it, even though the writer is > > continuing to insert new data periodically. [...] > > I thought that SQLite's use of fdatasync on Solaris should be enough to > synchronize reads and writes from various processes: > > The fdatasync() function forces all currently queued I/O > operations associated with the file indicated by file > descriptor fildes to the synchronized I/O completion state. I'd have thought so, too. I've confirmed fdatasync shows up in the symbols in the compiled sqlite library, and that two instances of the `sqlite3` SQL shell don't show the problem. Unfortunately, two minimal Python programs don't show the problem either. That points to the application code -- except that I only see this behaviour on Solaris, and there's no OS-specific code in the database-related portions of the application. I'll continue investigating and try to get to the bottom of this. Thanks for the sanity-check. Charles -- ------- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Reading across processes on Solaris
Greetings, I searched the web and the list archives but found nothing relevant to this problem, so here goes... I'm using sqlite (through the pysqlite wrapper, but this behaviour seems unrelated to the wrapper) in an application on various platforms. One process create an sqlite database and starts writing data to it; another process opens the sqlite database and reads from it. On most platforms, the reader sees new data appear in the database periodically as the writer creates new records. But on Solaris, the reader never sees any updates -- it only ever sees whatever data was in the database when the reader first opened it, even though the writer is continuing to insert new data periodically. I've seen similar behaviour with non-database files on Solaris -- writes to a file across processes aren't seen by the reader unless the reader supplies the O_RSYNC flag to the open(2) call. It seems to be a Solaris peculiarity, as I don't see this behaviour on Linux, *BSD, or other commercial Unices. I've looked at the sqlite source code, and it does not appear to be supplying the O_RSYNC or O_SYNC flags to open(2). So, my questions are: 1) Has anyone else run into this issue? Is there a known way to work around it? 2) Should the open(2) call be modified to provide the O_RSYNC flag? Would this have nasty side effects? I appreciate any responses. I'm subscribed to the list, so I don't need to be cc'd. Charles -- --- Charles Cazabon <[EMAIL PROTECTED]> --- - To unsubscribe, send email to [EMAIL PROTECTED] -
