Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

Post by zbentley » Fri, Apr 24, 2026, 8:05 PM UTC

russellthehippo wrote: post #66

zbentley wrote: post #60

Very neat! I like this a lot, nice work. After peeking the source, a few possible areas of improvement: - You can use `fstat` and keep a file handle around, likely further improving performance (well, reducing the performance hit to other users of the filesystem by not resolving vfs nodes). If you do this, you'll have to check for file deletions. - If you do stick with stat(2), it might be a good idea to track the in…

Wow, thanks for the great feedback. I actually looked at fstat, but the "check for deletions" piece, given I'm polling at 1kHZ, was the reason I decided not to use it. Older hardware actually made this a big issue but it's fast enough now I decided it wasn't a problem. I'll ignore the malicious ones bc [out of scope declaration]. Object paranoia is an artifact of build trama and I respect that lmao. I've just looked…

    import os
    import time
    from threading import Thread, Event

    f = '/tmp/foo.test'
    ev = Event()
    Thread(target=lambda: ev.wait() and os.unlink(f), daemon=True).start()

    with open(f, 'w+') as fh:
        print("before delete:", os.fstat(fh.fileno()).st_nlink)
        ev.set()
        time.sleep(1)
        print("after delete:", os.fstat(fh.fileno()).st_nlink)

Post by russellthehippo » Fri, Apr 24, 2026, 8:27 PM UTC

zbentley wrote: post #82

russellthehippo wrote: post #66

Earlier quoted context omitted.

Wow, thanks for the great feedback. I actually looked at fstat, but the "check for deletions" piece, given I'm polling at 1kHZ, was the reason I decided not to use it. Older hardware actually made this a big issue but it's fast enough now I decided it wasn't a problem. I'll ignore the malicious ones bc [out of scope declaration]. Object paranoia is an artifact of build trama and I respect that lmao. I've just looked…

Hope it helps! One clarification: by "check for deletions" I didn't mean that you need to read back through the filesystem; you can check for deletions for free using fstat(2)'s result. The number of hard links to a file descriptor's underlying description returned by fstat includes the "existential" hard link of the file itself, and drops to zero when the file's deleted and the open handle is an orphan: import os im…

Post by russellthehippo » Sat, Apr 25, 2026, 10:58 AM UTC

infogulch wrote: post #81

russellthehippo wrote: post #70

Earlier quoted context omitted.

I just tested this out. PRAGMA data_version uses a shared counter that any connection can use while the C API appears to use a per-connection counter that does not see other connections' commits.

Really? That's the opposite of what I understand the docs say. Care to share your code? This may become a bug report.

Post by russellthehippo » Sun, Apr 26, 2026, 2:27 AM UTC

russellthehippo wrote: post #86

infogulch wrote: post #81

Earlier quoted context omitted.

Really? That's the opposite of what I understand the docs say. Care to share your code? This may become a bug report.

Reporting back. This appears to be a bug in my original test the code of which sadly I did not commit anywhere. I went back to regenerate these tests and proved the opposite - the C API is better than PRAGMA and works across connections. I am going to make that update as I've proved across dozens of versions of SQLite that this is not in fact the case.

Post by hizihic » Mon, Apr 27, 2026, 11:57 PM UTC