Hacker News Reader

Comment by chrisfarms | Hacker News Reader

  > If Postgres would implement a shitty and 
  > inefficient version of an upsert statement at 
  > the very worst it could be as bad as the current 
  > implementation that people write on their own

Wow. please don't do that.

Many people do not understand why an upsert is a hard problem and would blindly use any solution presented without understanding it's limitations. At least with the current situation if a developer finds themselves in need of an upsert they are forced to seek out a solution and in the process understand why it's tricky.

View on HN

As someone who does a lot with hand-coded upsert implementations, I would say that the problems are genuinely hard. I am somewhat on the sidelines. Not only are the problems hard, but the expected behavior is sometimes even difficult to define.

The real problem is that if the desired behavior is not nailed down entirely in the first implementation then what will happen when the operation is improved is that it will break stuff. That's I think the strongest argument for waiting.

OTOH, the concurrency issues are genuinely hard and there are a large number of applications for whom they don't really apply in the real world. It might seem to be nice to have a canonical syntax.

I guess on balance I agree with you but for very different reasons. Once you have an API, you have a contract.

Or blindly copy and paste the first solution they find on Google. Even after searching and trying to understand, it took me a while to really grasp the problems. In the end, I still walked away not knowing if the solution I used was actually alright (ie fast, no race conditions, etc).

I'd still argue that at least you are aware there may be an issue with your code. So at least if something comes up (duplicates, exceptions during high load upserts) you'll know where to look.

And at least the version he's using will likely retain the subtleties of its behaviour over future postgres versions.

Depending on a "shitty & broken" implementation that goes into postgres which then subtly changes its behaviour after a few years could cause no end of problems.

Will you remember though, six months later? What about when someone inherits the codebase from you, or you inherit a codebase from someone else containing a solution someone else implemented? You'll be stuck trying to diagnose and fix issues with very little guidance, because only a handful of other people may have implemented the same solution as you.

Having a single, canonical solution, even if it it's imperfect, is usually better than having a myriad of equally imperfect options. At least then documentation and community guidance can coalesce around it. Its caveats can be properly explored and explained, and when you do run into trouble and go looking for help, you're far more likely to find people who have had the same problems in the past.

Saying "feature x is hard, so we'll leave it to developers to figure out" is a disastrous mindset for any piece of software. The whole point of frameworks, runtime libraries, DBs, etc. is to provide developers with standardised, battle-tested infrastructure to do difficult things that require specialist knowledge outside of their area of expertise to get right. The argument that the hardest stuff should be left out, because it encourages developers to try and figure it out themselves is crazy: if the PostgreSQL developers can't work out how to implement upsert perfectly, then non-DB specialists are likely to do even worse.

I certainly wasn't saying the feature should not exist because it's hard.... merely, do it right or not at all.

The culture on the Postgres development side has always taken a very conservative approach, which carries with it much trust that the features that exist work very well.

Introducing a half-baked feature would break this trust. And so I would rather wait.

When you have a huge investment in your data, a conservative approach to database servers is full of merit. Once I began to understand concurrent design my years of MySQL experience were suddenly curdled. I had to switch to a server/community that care about integrity, and do not play fast and loose with new features. It's not a popularity contest.

I would rather see a single, well-defined and correct implementation, even if it's really slow, than to not have it at all...

You left out a crucial part of that sentence.

    > implementation that people write on their own
    > and then at least, there is an established syntax
    > and a way to improve it further.

That was the point.

Being aware of the problem doesn't mean you'll actually find a good solution. At least this way, any lingering problems will eventually be fixed instead of draining more of your own resources. Chances are, the Postgres team will be able to figure out the subtleties much better than you.

The more domain knowledge your system requires, the worse off everybody is. Here's a story.

At my previous company, I implemented something close to 'upsert'. After hours of researching, implementing, and testing in as many environment I could think of, I wondered why I wasted so many hours trying to get it right. Sure, I now understand the subtleties involved. (Well, I think so anyway. I still don't know what I don't know.) But am I better off now? Is the company? Not really. What we have is a tangled hack.

And worse, I no longer work with this company. So the next guy will have to figure out what's going on when there's an issue. To be safe, I spent another day re-organizing the code for clarity and documenting the complicated workaround. A scrappy syntax shipped today and fixed tomorrow would have been ideal. That way, the new guy could search for the symptom and see that that the current implementation of UPSERT is the cause. They can then use a recommended workaround, or simply upgrade Postgres to fix it entirely.

It would also bloat the PostgreSQL codebase which crappy syntaxes which they may have to support for many years in the future.

The PostgreSQL team already merges features which are not yet perfect (see materialized views in 9.3, JSON in 9.2, and replication in 9.0 for examples), but they will not do that if they think they will have to break the API in a near future.

The problem though is that the corner cases of concurrency etc. with upserts need to be hammered out and agreed on at the beginning. Otherwise you will have "well, we improved concurrency handling in upserts and sorry, that broke your application."

Once you have a public API you have a contract, and that contract really shouldn't be broken without good reason. If PostgreSQL offered an upsert syntax with the same limitations as writeable CTE's, and promised to solve the concurrency issues in future releases, I wouldn't use it. I would rather have a hand-written implementation which is guaranteed to work in the future with the same caveats than an implementation I have no control over that may pose backwards-compatibility handling issues if I am supporting multiple Pg versions with my app.

Stability in API's is important. The idea that we will just throw something together now, and then fix it later will result in PostgreSQL getting a bad reputation.

Not to mention that any expensive ("shitty and inefficient") database operation that has a user-facing endpoint increases surface area for DoS attacks.

agreed - I really want an upsert, but putting out a subpar implementation will just give upsert a bad name - then even if they fix it to work right you have months or years of google results telling you not to use it.

Reply on news.ycombinator.com