Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres gets support for upsert

git.postgresql.org

Post reply on HN
Print view View original

91–100 of 114 posts

Re: Postgres gets support for upsert

#91

Post by corford »

anarazel wrote: post #90
corford wrote: post #86

I've been looking forward to this for ages! The solution we use at the moment for upsert is a create or replace rule on the table but it isn't great as you need to remember to update the rule whenever you change the table schema... CREATE OR REPLACE RULE "replace_row" AS ON INSERT TO my_table WHERE EXISTS(SELECT 1 FROM my_table WHERE id=NEW.id) DO INSTEAD (UPDATE my_table SET col1=NEW.col1, col2=NEW.col2, col3=NEW.co…

That unfortunately is not safe on several fronts. For one, the WHERE EXISTS() will not see concurrent insertions by transactions that are still in progress, which means you'll still get constraint violation errors. For another, rules generally have very surprising behaviour. E.g. with this RULE you'll get into trouble if "id" isn't passed in as a explicit value, but uses a non-deterministic DEFAULT or directly passed…

Unfortunately, there's no good all round solution for upsert in current postgres versions. For our use case (one logger daemon feeding results off a message queue in to a table in an idempotent way) this approach was the simplest and worked the best.

Re: Postgres gets support for upsert

#92

Post by taco_emoji »

ianamartin wrote: post #43

In my crazy-ass opinion, if you need upserts, there are problems with your application logic and problems with your database. Get off my lawn, etc.

Why bother posting if you're not going to explain yourself? It's 2015, nobody's going to be impressed by mere contrarianism.

Re: Postgres gets support for upsert

#93

Post by anarazel »

corford wrote: post #91
anarazel wrote: post #90

Earlier quoted context omitted.

That unfortunately is not safe on several fronts. For one, the WHERE EXISTS() will not see concurrent insertions by transactions that are still in progress, which means you'll still get constraint violation errors. For another, rules generally have very surprising behaviour. E.g. with this RULE you'll get into trouble if "id" isn't passed in as a explicit value, but uses a non-deterministic DEFAULT or directly passed…

Unfortunately, there's no good all round solution for upsert in current postgres versions. For our use case (one logger daemon feeding results off a message queue in to a table in an idempotent way) this approach was the simplest and worked the best.

Can't you just do the "usual" looping pattern around a subtransaction? If events are inserted by a single callsite that shouldn't prove to onerous?

C.f. http://www.postgresql.org/docs/9.4/static/plpgsql-control-st...

Re: Postgres gets support for upsert

#94

Post by corford »

anarazel wrote: post #93
corford wrote: post #91

Earlier quoted context omitted.

Unfortunately, there's no good all round solution for upsert in current postgres versions. For our use case (one logger daemon feeding results off a message queue in to a table in an idempotent way) this approach was the simplest and worked the best.

Can't you just do the "usual" looping pattern around a subtransaction? If events are inserted by a single callsite that shouldn't prove to onerous? C.f. http://www.postgresql.org/docs/9.4/static/plpgsql-control-st...

Could have done but coming from a mysql background (i.e. where replace statement is supported), just crafting a simple replace rule was easier/faster to grok and implement. Also helped that the table schema was very unlikely to change much in the future - so we didn't have to worry too much about remembering to keep the rule up to date (and the fact the id column was defined as VARCHAR NOT NULL and had no default assigned to it).

Re: Postgres gets support for upsert

#95

Post by loopdoend »

donw wrote: post #36

Damnit, I just wrote a sweet gem that handles this... ah, okay, but it requires an index constraint, whereas I needed to merge on arbitrary conditions. So I didn't totally waste my time. Way to go Postgres team!

Is the gem open source? I checked your github and didn't see it, I think people would still find it useful!

Re: Postgres gets support for upsert

#96

Post by philsnow »

qooleot wrote: post #62
threeseed wrote: post #58

Earlier quoted context omitted.

I never said popularity was an argument for quality. You did. What I am saying is that if MySQL wasn't robust then those companies simply wouldn't be using it. Since at their scale any bug or weakness will manifest at a level far greater than say at a startup. And they have the skills, time and money to choose any technology they wan't so I don't buy your argument that they are "locked in". Some like Facebook and Lin…

" never said popularity was an argument for quality. You did." I'd tend to think your statement: "And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust." ..points out that many (popular) sites use it, and a prerequisite is robustness, which thesaurus-wise, sounds a lot like quality. I get that a) you didn't…

> many (popular) sites use it

threeseed's argument is not

    this handful of popular companies use it, therefore it is good
but rather

    this handful of companies, each of which is considered by some/many to be 'excellent' or 'top', use it, therefore it is good

Re: Postgres gets support for upsert

#97

Post by pjlegato »

threeseed wrote: post #58
slapresta wrote: post #50

Earlier quoted context omitted.

> And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust. Popularity is not an argument for quality. See: crocs, Justin Bieber, PHP. Those companies you mentioned, like many others, probably use it because they're locked in that technology, not because it's a superior one. Just like banks still use COBOL. If…

I never said popularity was an argument for quality. You did. What I am saying is that if MySQL wasn't robust then those companies simply wouldn't be using it. Since at their scale any bug or weakness will manifest at a level far greater than say at a startup. And they have the skills, time and money to choose any technology they wan't so I don't buy your argument that they are "locked in". Some like Facebook and Lin…

>What I am saying is that if MySQL wasn't robust then those companies simply wouldn't be using it.

That conclusion does not follow from the premises: that some well known large companies use a technology in no way implies that it is robust.

You are assuming that large, well known companies always intrinsically select for high robustness in their tools, which is not necessarily the case. In fact, there are many well known public cases of the opposite.

It could be (for example) that large companies simply have the spare organizational capacity to deal with a lack of robustness. Perhaps they are locked in and find the ongoing cost of dealing with lack of robustness to be lower than the costs of switching to something more robust. Or it could be that they don't notice, for whatever reason, the lack of robustness. There could be many other possible explanations.

Re: Postgres gets support for upsert

#98

Post by piker »

I am extremely happy to hear about this. We currently insert, catch the exception and update if necessary. That can lead us to race conditions in concurrent transactions. Thank you very much for your excellent work on Postgres. It continues to be an amazing product to work with.

Re: Postgres gets support for upsert

#100

Post by kkland »

girvo wrote: post #8
ryan0x00 wrote: post #5

Earlier quoted context omitted.

i've seen a system in mysql do "insert on duplicate key update" for ages, so this is that same pattern?

Yeah, it is the same as INSERT ... ON DUPLICATE KEY UPDATE, and similar to REPLACE in MySQL. Exciting to see it in Postgres!

I think the way MySQL works in doing "INSERT ... ON DUPLICATE KEY UPDATE" is to delete the old row and insert a new row if there's an auto incremental column (mostly it's ID column). Being curious if Postgres works the same way in this regard.
Post reply on HN