PostgreSQL 11.3 and 10.8

Post by hans_castorp » Thu, May 09, 2019, 6:16 PM UTC

Alex3917 wrote: post #5

micmil wrote: post #4

I'm not a database guy so have no clue, but why are there so many versions receiving support? Is there just that much legacy crap they can't get away from, like Python?

Postgres users actually generally upgrade faster than those using other databases because there are a lot of new features each year. But once your database gets huge then upgrading still becomes a pain, so that's why they keep providing security support and bug fixes for older versions as well.

Post by atombender » Thu, May 09, 2019, 6:21 PM UTC

Deimorz wrote: post #95

atombender wrote: post #81

Earlier quoted context omitted.

One big argument: Transactional DDL. For example: begin; alter table foos add answer int not null default 42; alter table foos drop column plumbus; update foos set name = upper(name); create table bars (t serial); drop table dingbats; rollback; // Or, of course, commit What's the benefit? Atomic migrations. You can create, alter, drop tables, update data, etc. in a single transaction, and it will either commit comple…

I use transactional DDL in my tests. All the tables, triggers, etc. are set up inside a transaction, and then the actual tests run inside nested transactions. At the end of the test run, the outer transaction gets rolled back, and everything disappears. I don't know if it accomplishes anything truly new (other than ideas that aren't very useful in practice like being able to have multiple test runs going in parallel)…

Post by [deleted] » Thu, May 09, 2019, 6:30 PM UTC

atombender wrote: post #104

Deimorz wrote: post #95

Earlier quoted context omitted.

I use transactional DDL in my tests. All the tables, triggers, etc. are set up inside a transaction, and then the actual tests run inside nested transactions. At the end of the test run, the outer transaction gets rolled back, and everything disappears. I don't know if it accomplishes anything truly new (other than ideas that aren't very useful in practice like being able to have multiple test runs going in parallel)…

Transactional tests have some downsides, unfortunately. If your tests test transactional code, that code itself cannot create transactions; they have to use savepoints, which aren't quite the same. Transactional tests also don't work with testing anything concurrent, unless you share the session across threads/goroutines/whatever. Lastly, if a test fails you'd typically like to leave the data behind so that you can i…

Post by evanelias » Thu, May 09, 2019, 6:41 PM UTC

hans_castorp wrote: post #101

evanelias wrote: post #85

Earlier quoted context omitted.

> Indexing on function expression MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda... > JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html

Indexing a generated/computed column is not the same as creating an index on an expression. If you want to support several different expressions you need to create a new column each time. Additionally, an ALTER TABLE blocks access to the table. Indexes can be created concurrently while other transactions can still read and write the table. But MySQL doesn't support indexing the complete JSON value for arbitrary queri…

Post by noir_lord » Thu, May 09, 2019, 6:46 PM UTC

kangoo1707 wrote: post #51

eberkund wrote: post #44

I maintain a couple of MySQL based applications. I don't really use any features outside of "standard SQL" is there a reason to switch over to Pg? I haven't used Pg before and usually default to MySQL.

At my PHP-shop company, most projects are limited to MySQL 5.7 (legacy reason, dependency reason, boss-likes-MySQL reason...). They are all handicapped by MySQL featureset, and can't update to 8 yet. If they had used Postgres some years ago, they would get: - JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres) - Window functions (available in MySQL 8x only, while this has been availab…