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

41–50 of 114 posts

Re: Postgres gets support for upsert

#41

Post by kbendyk »

vinodc wrote: post #38
kbendyk wrote: post #37

I know it doesn't matter to some developers out there, but for me, the only thing left is the ability to add a column after another one. Even if it's only logical order, it's easier for me to read a table structure when fields are ordered nicely.

Unsure what you mean. A subset of columns can be selected in any order, across tables. Were you referring to the select order used by "*"?

I know, but when I look at a table structure in PgAdmin, all newly added columns appear at the end, of course this can handled properly in select col2, col1... , or by creating a view, but by any other way as this wiki [1] page says. But during development, ability to create logical column order is somehow important to me.

[1]https://wiki.postgresql.org/wiki/Alter_column_position

Re: Postgres gets support for upsert

#42

Post by kbendyk »

pmontra wrote: post #40
vinodc wrote: post #38

Earlier quoted context omitted.

Unsure what you mean. A subset of columns can be selected in any order, across tables. Were you referring to the select order used by "*"?

Yes, I think it's about changing the default presentation order. That could be a feature of the client, psql, pgcli, pgadmin3, but it won't be portable across them unless it's some metadata stored inside the db. Is there any db with that feature? Edit: munro answered that. PostgreSQL does it but it's expensive because it has to rewrite the table.

Yes, MySQL: ALTER TABLE t1 ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`

Re: Postgres gets support for upsert

#44

Post by pilif »

threeseed wrote: post #33
pilif wrote: post #27

Earlier quoted context omitted.

> late version 7 or early version 8, back when MySQL was the more featureful and performant of the two MySQL was never more featureful. The reason I started migrating back in the 7.1 time frame (when TOAST tables were added and you could finally store more than 8K of text in a TEXT column) was the lack of sub-selects in MySQL. Even aside of that, Postgres was far ahead when considering basic SQL support: stored proce…

MariaDB, WebScaleSQL, Percona are just three MySQL forks that are completely open source in every single way. Likewise MySQL-Server is GPL so it definitely should be considered as open source. 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. No doubt that PostgreSQL is better at MySQL in many areas th…

>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.

Either that or they have staff and infrastructure in place to deal with the lack of robustness. Yes, you need to be prepared to deal with corruption anyways, but the more robust your solution, the more time is left to deal with other things.

Over the years I have seen multiple instances of MySQL table corruption, index corruption and mysqldump exiting with a zero exit code after aborting mid-dump due to table corruption.

Of course I was prepared for this and I always had backups ready, but it was still time-consuming and annoying.

With Postgres I've yet to see any kind of data corruption even though my postgres usage is much heavier than my MySQL usage.

But this is why I said "anecdotal evidence": For me personally, Postgres has proven to be way more robust than MySQL. Is this my inability to properly administer MySQL? Is it me being unlucky with hardware (though Postgres is fine on the same hardware)? Is it me just being unlucky? I really don't know.

Re: Postgres gets support for upsert

#48

Post by stingraycharles »

saurik wrote: post #29
colanderman wrote: post #11

Earlier quoted context omitted.

Just "using a transaction" is insufficient. You must be prepared to handle the case that neither the INSERT nor the UPDATE succeeds (with READ COMMITTED isolation), or that the transaction fails (with REPEATABLE READ isolation or better), by repeating the transaction. And if latency is at all a concern to you, you must wrap this all in a stored procedure to avoid the necessary round-trip-time between the commands. He…

...and then you additionally have to verify that the specific reason both the update and the insert failed was due to concurrency, or you can make the mistake I did a couple years ago where I had transactions spinning against my database for weeks on end due to an unrelated constraint failure I had failed to notice with some transactions that I kept retrying in a loop as part of my hacked together upsert implementati…

Is my conclusion correct that this implies sequence increments are run outside of the transaction?

Re: Postgres gets support for upsert

#49

Post by ryanjshaw »

anarazel wrote: post #24
viraptor wrote: post #19

I love that it's there now. I've been waiting for it for a long time, but one thing I don't get is... why does every single implementation have to have their own slightly different syntax? pgsql -> on conflict mysql -> replace / on duplicate oracle -> merge mssql -> merge sqlite -> insert or replace firebird -> merge / update or insert

MERGE is actually a separate feature. We're still considering implementing it. But it's syntax is too cumbersome for many usages. There's also some unrelated complexity - so implementing merge support does get easier by the infrastructure in here, but a fair amount of work remains.

This seems MUCH better than MERGE for the common case.

I tried implementing some MERGE logic on MSSQL recently and at first it seemed great until I realised:

(a) maintaining/debugging a MERGE that uses most of its syntactical features is an absolute nightmare involving psychically debugging 20+ lines of opaque code - I ended up copying & pasting pieces of the statement into temporary tables/variables and confirming results that way, making me question the value of MERGE as the performance was similar to the original code, but less flexible (MERGE doesn't make available some of the values you need in some cases, so you need to do pre-mapping of data in those cases anyway, resulting in lots of similar code)

(b) it doesn't save you from needing to know and use the correct locks, something many people don't seem to realise!

I then discovered this page, listing all kinds of issues with the MERGE implementation (even on MS SQL 2014, years after MERGE had been introduced): http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...

I realised that even if I followed my debugging approach in (a), one day I was going to run into a problem with MERGE that couldn't be replicated in decomposed statements because MERGE was doing 'something' else. And course, if you look at some of those issues, many of them suffer from the typical Microsoft "Won't fix and won't say why" attitude. In the end I decided to just keep the original/decomposed code, which was clearer and easier to work with.

MERGE is probably okay if you're staging data; I wouldn't use it for transactional processing. It is far too complicated. I really like that PG has focused on efficiently implementing the common use case and avoided the kitchen sink that is MERGE.

Re: Postgres gets support for upsert

#50

Post by slapresta »

threeseed wrote: post #33
pilif wrote: post #27

Earlier quoted context omitted.

> late version 7 or early version 8, back when MySQL was the more featureful and performant of the two MySQL was never more featureful. The reason I started migrating back in the 7.1 time frame (when TOAST tables were added and you could finally store more than 8K of text in a TEXT column) was the lack of sub-selects in MySQL. Even aside of that, Postgres was far ahead when considering basic SQL support: stored proce…

MariaDB, WebScaleSQL, Percona are just three MySQL forks that are completely open source in every single way. Likewise MySQL-Server is GPL so it definitely should be considered as open source. 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. No doubt that PostgreSQL is better at MySQL in many areas th…

> 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 you're interested on knowing why using MySQL isn't a good idea, there's this (highly opinionated, obviously) post about it: http://grimoire.ca/mysql/choose-something-else

Post reply on HN