4 ms·

My list of unexpected but very welcome changes: - pg_hba.conf and pg_ident.conf can include other files - Logical replication apply can use non-PK btree index

My list of unexpected but very welcome changes: - pg_hba.conf and pg_ident.conf can include other files - Logical replication apply can use non-PK btree indexes - Integer literals in non-decimal bases - Underscores in numeric literals - Subqueries in the FROM clause can omit aliases - Addition and subtraction of timestamptz values - pg_upgrade can override new cluster's locale and encoding

MuffinFlavored 3y ago
Not a bad list! https://commitfest.postgresql.org/19/1741/ https://commitfest.postgresql.org/19/1741/ (index skip scan/loose index scans) would be very welcomed... I think. Not sure how many people run into it in the wild. It says "target version: 16" but "returned with feedback" and hasn't been bumped in 14 months. :( First opened in 2018. https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan
seanhunter 3y ago
> - Subqueries in the FROM clause can omit aliases This is great. It never made any sense to me that this was required. For people who are unaware, say you want to understand a table a natural way of doing it might be select * from the_table order by some_metric desc limit 10 so you'd think you can do the same for queries like select * from ( select blah blah blah the rest of the query ) a order by some_metric desc limit 10 you need to put the alias 'a' to placate existing postgres even though it's never actually used, which never made any sense to me.
- paulddraper 3y ago
  It never made sense. And FYI this comes from ANSI SQL.
  - Tostino 3y ago
    It does the second you want to join to the results of that subquery...this is an optimization that recognizes that the alias is never used and thus can be omitted. Nice QOL feature.
- jimktrains2 3y ago
  It makes sense in that every table needs a name to reference, however if you only have the one table there isn't any ambiguity.

MuffinFlavored 3y ago

Not a bad list! https://commitfest.postgresql.org/19/1741/ https://commitfest.postgresql.org/19/1741/ (index skip scan/loose index scans) would be very welcomed... I think. Not sure how many people run into it in the wild. It says "target version: 16" but "returned with feedback" and hasn't been bumped in 14 months. :( First opened in 2018. https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan

seanhunter 3y ago

> - Subqueries in the FROM clause can omit aliases This is great. It never made any sense to me that this was required. For people who are unaware, say you want to understand a table a natural way of doing it might be select * from the_table order by some_metric desc limit 10 so you'd think you can do the same for queries like select * from ( select blah blah blah the rest of the query ) a order by some_metric desc limit 10 you need to put the alias 'a' to placate existing postgres even though it's never actually used, which never made any sense to me.

paulddraper 3y ago
It never made sense. And FYI this comes from ANSI SQL.
- Tostino 3y ago
  It does the second you want to join to the results of that subquery...this is an optimization that recognizes that the alias is never used and thus can be omitted. Nice QOL feature.
jimktrains2 3y ago
It makes sense in that every table needs a name to reference, however if you only have the one table there isn't any ambiguity.