However if we’re talking from idealistic perspective then you’d have your own DBAs who would create the DB and write the SQL so your developers just call your (for example) stored procedures like APIs rather than writing their own - often unoptimised - SQL themselves.

As for ssh, I haven't looked at its history, but I suspect it's older than scp; the intuitive flag for "port" is -p, so that's what ssh used.

I agree the query could have written better (I am still getting my head around how to use LATERAL), but it worked fine in 9.6 and stopped working in 10. From a backward compatibility viewpoint, code working in one version should still work in the next (even if it isn't the best code.) Or at least, start issuing deprecation warnings one version before making it not work.

Anyway, posting this to HN has triggered someone to go rewrite my code for me (thanks Ants Aasma, whoever you are), so now my Postgres 10 upgrade blocker is solved :)

Postgres generally tries its best to not break users code. However sometimes it is necessary for making forward progress. In this case the undocumented behavior of set returning functions within select list had some pretty funky, mostly accidental, semantics that were getting in the way of executor improvements. For example try to figure out how to explain the output of these two queries on 9.6:

    select generate_series(1,2), generate_series(1,4);
    select generate_series(1,3), generate_series(1,4);

Deprecation warnings are nice in theory, but in practice they would require an unreasonable amount of effort to properly implement, not seeing any warnings still wouldn't be a guarantee that your application works on new version. And it seems most users ignore deprecation warnings anyway. Besides, it's not like you can avoid making the changes, you just have slightly less schedule flexibility on when to implement them.

"Generally, the Finnish legal system does not permit ex post facto laws"

> In civil matters, such as taxation, ex post facto laws may be made in some circumstances.

  1) This is not really a temporary table in the traditional
     sense; you're creating a real table and then dropping it.
     This has problems with concurrency (you can't have two
     "real" tables with the same name), among other issues.
  2) Temporary tables in Postgres suck because they bloat
     the catalogs.  Creating temporary tables regularly is
     better avoided.
  3) There's no real need to create a table of any kind.  A
     regular VALUES would do.  (Though you might have to encode
     some type information into the query.)

Do you actually play CS:GO proficiently? Because it doesn't sound like you do.

Not really:

    SELECT u.user, i.text
    FROM users u,
    LATERAL (SELECT * FROM items WHERE items.user = u.user ORDER BY items.pos DESC LIMIT 3) i;

My point being, there are some issues with regards to data purity and reasoning, particularly if you want a system where users can build custom reports.

And I just wish the original article highlighted that using floats would have these issues, and if writing SQL like this is important to you, then you might want to reconsider.

(As for your note in brackets, where I work, SQL is basically used as a scripting language. So we would do my example in SQL.)

[0] Yes, I know there is 'set rowcount', but you cannot do that within a sub select.

Are you looking for SELECT TOP 3 ...?

But you are right, I should have clarified that.