Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles.
Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_.
Tag: postgresql
Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics
On 11st of February 2023, Andres Freund committed patch:
Add pg_stat_io view, providing more detailed IO statistics Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation. Rows of the view show IO operations for a particular backend type, IO target object, IO context combination (e.g. a client backend's operations on permanent relations in shared buffers) and each column in the view is the total number of IO Operations done (e.g. writes). So a cell in the view would be, for example, the number of blocks of relation data written from shared buffers by client backends since the last stats reset. In anticipation of tracking WAL IO and non-block-oriented IO (such as temporary file IO), the "op_bytes" column specifies the unit of the "reads", "writes", and "extends" columns for a given row. Rows for combinations of IO operation, backend type, target object and context that never occur, are ommitted entirely. For example, checkpointer will never operate on temporary relations. Similarly, if an IO operation never occurs for such a combination, the IO operation's cell will be null, to distinguish from 0 observed IO operations. For example, bgwriter should not perform reads. Note that some of the cells in the view are redundant with fields in pg_stat_bgwriter (e.g. buffers_backend). For now, these have been kept for backwards compatibility. Bumps catversion. Author: Melanie Plageman <melanieplageman@gmail.com> Author: Samay Sharma <smilingsamay@gmail.com> Reviewed-by: Maciek Sakrejda <m.sakrejda@gmail.com> Reviewed-by: Lukas Fittl <lukas@fittl.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Justin Pryzby <pryzby@telsasoft.com> Discussion: https://postgr.es/m/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de
How to get a row, and all of it’s dependencies?
This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?
Decided to take a look at this task.
Continue reading How to get a row, and all of it's dependencies?
Now you can \d table not only in psql :)
Whenever people look for help with queries, one of the first things that I ask is: what is \d of the table.
Which works great, with the caveat, that the person on the other end must be somewhat familiar with psql. And quite often it's not the case.
So I figured, I can try to fix it.
When to use VACUUM FULL
Well, the short answer is: NEVER. But given how often I see people ask about it, I'll try to expand my answer a bit…
Waiting for PostgreSQL 16 – Allow underscores in integer and numeric constants.
On 4th of February 2023, Dean Rasheed committed patch:
Allow underscores in integer and numeric constants.
This allows underscores to be used in integer and numeric literals,
and their corresponding type input functions, for visual grouping.
For example:
1_500_000_000
3.14159_26535_89793
0xffff_ffff
0b_1001_0001
A single underscore is allowed between any 2 digits, or immediately
after the base prefix indicator of non-decimal integers, per SQL:202x
draft.
Peter Eisentraut and Dean Rasheed
Discussion: https://postgr.es/m/84aae844-dc55-a4be-86d9-4f0fa405cc97%40enterprisedb.comContinue reading Waiting for PostgreSQL 16 – Allow underscores in integer and numeric constants.
Can you use fzf for psql history?
FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands.
But, can I use it in psql?
Waiting for PostgreSQL 16 – Invent random_normal() to provide normally-distributed random numbers.
On 9th of January 2023, Tom Lane committed patch:
Invent random_normal() to provide normally-distributed random numbers. There is already a version of this in contrib/tablefunc, but it seems sufficiently widely useful to justify having it in core. Paul Ramsey Discussion: https://postgr.es/m/CACowWR0DqHAvOKUCNxTrASFkWsDLqKMd6WiXvVvaWg4pV1BMnQ@mail.gmail.com
Waiting for PostgreSQL 16 – Non-decimal integer literals
On 14th of December 2022, Peter Eisentraut committed patch:
Non-decimal integer literals
Add support for hexadecimal, octal, and binary integer literals:
0x42F
0o273
0b100101
per SQL:202x draft.
This adds support in the lexer as well as in the integer type input
functions.
Reviewed-by: John Naylor <john.naylor@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.comContinue reading Waiting for PostgreSQL 16 – Non-decimal integer literals
Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.
On 14th of December 2022, Jeff Davis committed patch:
Add grantable MAINTAIN privilege and pg_maintain role. Allows VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE. Effectively reverts 4441fc704d. Instead of creating separate privileges for VACUUM, ANALYZE, and other maintenance commands, group them together under a single MAINTAIN privilege. Author: Nathan Bossart Discussion: https://postgr.es/m/20221212210136.GA449764@nathanxps13 Discussion: https://postgr.es/m/45224.1670476523@sss.pgh.pa.us
Continue reading Waiting for PostgreSQL 16 – Add grantable MAINTAIN privilege and pg_maintain role.