News for hackers. Stuff that matters.
Re:PostgreSQL 10 Released by mohaine on Thursday October 05, 2017 @02:19PM (#15411348)
The warts from a dev perspective:

1) Oracle is really lacking in modern features/usability. Features where frozen in roughly 1999 and they are pretty still the same (mostly). (You are STILL limited to 30 chars for a table name FFS). They do add new stuff from time to time but anything existing isn't modified. Works but not fun to work with.

2) MSSQL needs NOLOCK everywhere (I've seen codebases with this on EVERY query). The default locking really sucks. I'm sure a DBA can make the locking sane system wide but I've never seen this on any of the DBs I've worked with. Also, SQL Manager is a PITA IMHO. Toad it is not. Almost all DB interactions via a 1G windows only install is a "bad idea"

3) MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

4) Postgres. Lots of nice features and easy to work with but the optimizer will sometimes do something silly. Sometimes you have to cast your bound variable just use an index. (id=? => id=?::NUMBER just because you do a setObject in jdbc)

Re:PostgreSQL 10 Released by gnud on Thursday October 05, 2017 @02:32PM (#15411462)
For the MSSQL locking, you should probably change the transaction isolation level instead of using NOLOCK everywhere.

I think it's a horrible wart that you have to do that for every session, though. The default can't be changed.

Re:PostgreSQL 10 Released by adzm on Thursday October 05, 2017 @02:54PM (#15411662)
Change the mssql translation to run in read committed snapshot to get similar behavior to Oracle.

NOLOCK is usually a bad idea.

Re:PostgreSQL 10 Released by skissane on Thursday October 05, 2017 @03:56PM (#15412120)
> (You are STILL limited to 30 chars for a table name FFS).

In the latest release, 12.2, the limit has been increased to 128.

https://docs.oracle.com/database/122/NEWFT/new-features.htm#...

Re:PostgreSQL 10 Released by mohaine on Friday October 06, 2017 @11:54AM (#15417767)
Thanks, I didn't realized they finally improved that.

Of course I doubt I ever notice since I doubt my current employer will never upgrade that far. We have currently frozen our large oracle database as a way to force long term migration off it.

Pretty much everything is moving to Postgres on AWS with a bit of other databases thrown in for spice.

Re:PostgreSQL 10 Released by beefield on Friday October 06, 2017 @01:39AM (#15414649)
> MySQL is nice but will just silently eat your data from time to time. Auto truncate is evil, as is missing enums. These have both hit me multiple times in production. Note: Not sure if this is still the case since I avoid it now for this reason.

Looks like I am forced to use MySQL (or some of its variants ) in the near future. This thing about MySQL eating data is a statement I have read about occasionally. Is there any way to identify and beware use cases where this could happen? Would there be any more thorough documentation of this issue anywhere?

Re:PostgreSQL 10 Released by erulabs on Friday October 06, 2017 @04:00AM (#15415131)
Any modern distribution of MySQL or MariaDB should come configured to throw query errors rather than truncate data on insert.

See STRICT_ALL_TABLES / https://mariadb.com/kb/en/library/sql-mode/

Modern MySQL is extremely well suited for data that cannot be lost - as is I'm sure, Postgres.

That said, if you're pre 5.6, I _strongly_ suggest upgrading to 5.6 or all the way to MariaDB 10. The performance, safety, stability, etc have skyrocketed in recent years.

Re:PostgreSQL 10 Released by mohaine on Friday October 06, 2017 @12:05PM (#15417882)
Older versions would silently allow you to insert 75 chars in to a 50 char column. The extra was just gone. Of course without an error, nobody notices until somebody notices the missing data. This is usually an end user in production and the data is just gone.

Also watch out/avoid enums.

Example:

CREATE TABLE shirts ( ... size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));

You have to specify all the values in the alter so to add xx-small it is

('xx-small','x-small', 'small', 'medium', 'large', 'x-large')

and then later if you add xx-large and forgot about the xx-small add:

('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large')

You just silently lost all the xx-small values, they have been promoted to different values that exist. (Unless this has been fixed as well). Migration scripts are the real issue as they don't know about any custom values that may have been added out of band.

Re:PostgreSQL 10 Released by beefield on Friday October 06, 2017 @01:34PM (#15418608)
Thank you. Helpful.
Re:PostgreSQL 10 Released by reednj on Thursday October 05, 2017 @10:06PM (#15413935)
Theres a server wide option to turn off auto-truncate for MySQL, which I believe is on by default, at least on ubuntu
Re:PostgreSQL 10 Released by tatersolid on Sunday October 08, 2017 @05:07PM (#15430005)
Sane and safe defaults matter in programming. MySQL has failed at this since its inception.