Comment by tibbar | Hacker News Reader

Comment by tibbar | Hacker News Reader

I did a little AI-assisted bug-hunting in Postgres this summer in between jobs, and the haul was staggering. Multiple serialization anomalies (eg [0] [1] [2]), foreign key integrity issues ([3] [4]), planner correctness bugs such as [5]. Various CVE-worthy vulnerabilities. An unfixable timezone bug [6]. The list got so long that I had to triage what I even had time to report, and settled for a dozen or two out of hundreds of candidates. Now imagine the entire world doing this.

[0] Write skew observed under serializable isolation: https://www.postgresql.org/message-id/flat/CA+COZaBo+ZpKgMvx...

[1] Serializable anomaly: duplicate primary keys: https://www.postgresql.org/message-id/flat/CA+COZaBOiiRPmEfX...

[2] SERIALIZABLE can commit write skew after SSI summarization: https://www.postgresql.org/message-id/flat/CA+COZaCtK=UQbeQw...

[3] Fix temporal foreign key validation of pre-existing rows https://www.postgresql.org/message-id/flat/CA+COZaChq_ukm2o7...

[4] Lost lock during toasting allows FK violation https://www.postgresql.org/message-id/flat/CA+COZaC-2-GQabq+...

[5] Partition pruning can incorrectly exclude a RANGE default partition https://www.postgresql.org/message-id/flat/CA+COZaDXrfTaBjLE...

[6] timestamp vs. timestamptz comparisons inconsistently ordered under DST : https://www.postgresql.org/message-id/flat/c22286ad6268347c6...

By the way, Robert and the other maintainers are incredible to interact with. There are so many reports to triage and bugs to fix, but they're unflaggingly polite and quite generous with their time. I don't know of another community like it.

I've been engaged with Postgres community for over 20 years now, and this has been my experience, too, throughout that time. Off and on I have interacted with a few other large projects, and nowhere (except at CodeBlocks) have I found such a welcoming and responsive community. The CodeBlocks IDE project has been one exception where I felt welcomed; but my interaction there was quite limited, and spanned over just a few days, so I can't extrapolate too much from that.

BTW, the Postgres community is also quite protective of their culture, so I expect this welcoming nature of the community to persist for a while.

> unfixable timezone bug

Very curious about the details and why it’s unfixable. Is it just because timezones are such a mess it’s logically impossible to satisfy every constraint?

I will add more example links in the top-level comment, but here is the thread for that bug. [0]. tl;dr timestamp vs timestamptz comparisons aren't consistently ordered in a pathological edge case, which means that index scans can return the wrong answer. There's no way to fix it without either redefining the operators in a surprising way or disabling index scans in this case.

[0] https://www.postgresql.org/message-id/flat/c22286ad6268347c6...

OT: Crazy that TZ-less are default in so many RDBMS. TZ included should be the default, and no-TZ opt in like TIMESTAMP NO TIME ZONE.

Drives me crazy that the engineers I work with are perfectly happy writing TZ-less datetime strings to the hundreds of tables we manage. Every user has to guess, or be informed, that they’re UTC.

They must yearn for the before times when text could be in an encoding specified only as: "your guess is as good mine!"

In cases where the data itself doesn't carry the unit (or TZ in this case), I usually ask that the unit-less columns at least include the unit in the name. A TZ-less column is not that bad if it is suffixed "UTC", e.g. `ExecutionTimeUtc`.

There are probably real world cases where dropping the TZ/offset provides meaningful performance/storage savings, but it hasn't been the typical case in my current sector.

> Every user has to guess, or be informed, that they’re UTC.

At least they are UTC and not EST or PST...

> or disabling index scans in this case

This seems rather extreme. The pathological case only seems to make a given timestamp ~1h bigger, so you could still use an index scan, just with a bigger input followed by a filter that removes the timestamps that shouldn't be in the query.

i assume the magnitude of impact for a bunch of [now] glaring holes in popular software is unknowable.

question is, how long do you think this persists before there's only a long tail of minor exploits left?

I am pretty sure we have always been able to add holes faster than we can find them and fill them, and that this has not changed with the advent of faster hole finding tools that are also faster hole producing tools.

It feels like we need AI methods to write provably correct code, along with a way for AI to automatically translate existing code into corresponding provably correct code.

(And hope performance stays in the same ballpark as the old code.)

Yes. Allowing a naive agent to fix bugs can quickly become pollutive.

I suspect both old codebases and new ones that have a very intentional architecture are both dangerous to let loose.

The agent doing the fixing must have not only an incredible map of the project and how it is put together, but the ability to reach into annotated examples in the past and refer to them as it goes about a fix.

I’m seeing that frontier models will not follow intended patterns or know when to deviate if their primary charge is “fix this bug.”

It is perhaps a better idea to have agents’ primary job be “maintain this codebase” and one way that they do it is to fix bugs, and “oh by the way here is a bug.”

Before that we need human methods to determine what 'correct' should mean for our code.

(Performance can be part of 'correct', and can be just as hard to actually get right in specification!)

Do you think AI is as good at writing bug-free code as finding bugs? If that's the case, this should be a one-time event during human coders -> AI transition. It's like catching a lot of bugs during a handover review.

Most software used to be written in assembly language. Microsoft DOS was mostly .ASM files, for example.

That era is over, compilers are used for almost all software, eliminating entire categories of bugs.

What categories of bugs would you say we eliminated going from macro assemblers to compilers?

(Operating systems, by the way, are the exception to the rule for assembly: the Linux kernel today has an x86/x64 assembly source component around the same size as MSDOS 5.0: ~250kloc of it. Windows is smaller, based on the NT4 kernel leak at around 70kloc. As a percent of total they're much less, of course, but they also do much more. And have correspondingly more bugs, too.)

> What categories of bugs would you say we eliminated going from macro assemblers to compilers?

Entire classes of 'safety' bugs that just don't exist in modern compilers.

E.g.: forgetting to "POP" something you "PUSH"-ed, especially when it was a conditional allocation for a temporary.

Similarly, type errors were much more common, especially signed/unsigned, or accidentally overwriting things in scenarios that just don't occur even with ordinary C code. Function args were easy to push "correctly enough" that it "worked", but not really.

The code it writes in the first attempt is at least as buggy as a human's. So the question is more whether it is as good at spotting bugs in its own code as it is at spotting bugs in human code

I'm tempted to say "no, it isn't". But I don't have numbers to back that up

I dunno about holes, but…

SQLite has gone into a frenzy of bug fixing this past year, changing long stable processes to ease things up: opening a specific report channel just for these kinds of bugs, adding features to Fossil to help manage things on their end (and that's just the visible stuff).

And with all that, the latest release has unfortunately been the buggiest I've experienced so far.

I skipped the .0 because of a crash, and only released .1 after giving up waiting for a .2 that never came, with a patch to fix a serious performance regression.

I spent a month supposedly shipping old bugs no one had a problem with, rather than shipping new bugs that instantly crashed things or caused measurable regressions.

That's on a software with stellar testing, and a good engineering team known to favour stability over the new shiny.

I don't think this is exactly true; adding holes only happens when adding features, and while LLMs can add features fast, deciding what features are actually wanted and needed is still a human factor.

So the amount of new code added is limited by processes and product management. Add to that that newly added code can get a lot more checks on it now, and new code will be more secure too.

I also don't think your main premise (adding holes faster than we can find them) holds up, but that's another matter. TL;DR I think we're in a high intensity period right now, but this time next year it'll be quiet in comparison - possibly quieter than before AI code scanners were a thing.

it's wild isn't it, that at the same time it's an enormously widely-used and reliable piece of software

Reply on news.ycombinator.com