tags

2021/10/01

That there’s a postgres keyword DEALLOCATE to remove prepared statements from memory: https://www.postgresql.org/docs/current/sql-deallocate.html

2021/11/03

That

There are two accepted formats for these strings: plain keyword/value strings and URIs. URIs generally follow RFC 3986, except that multi-host connection strings are allowed.
– https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
you can pass a application_name parameter to a connection string – https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-APPLICATION-NAME

2021/11/29

that there’s a pg_sleep(seconds) function: see https://til.hashrocket.com/posts/8a6f68519d-sleeping-in-postgresql

2022/01/09

1: That you can set statement timeouts:

ALTER DATABASE mydatabase SET statement_timeout = '60s';

Source: https://blog.crunchydata.com/blog/five-tips-for-a-healthier-postgres-database-in-the-new-year

2: that you can query your cache and index hit metrics (src)

3: writes to indexed columns don’t necessarily update indices:

If an indexed column has to have new values inserted, or existing values updated or deleted, the corresponding index does get updated as well. Ironically, that might make queries take more time to evaluate. If write operations are frequently run against a particular column then you may need to more carefully evaluate creating an index here.
If there’s only one thing you take away from this blog post, I hope it’s this: success with indexes will involve some planning, investigation, and maintenance!
– https://blog.crunchydata.com/blog/three-easy-things-to-remember-about-postgres-indexes

2022/05/04

Not to use BETWEEN for timestamps Not to use char(n), or varchar(n), since they’re not natively fixed-width and will defy expectations. See https://wiki.postgresql.org/wiki/Don't_Do_This

Also, I learned that you can’t run chsh(1) with sudo; chsh needs to run as the user who’s changing their shell.

2022/05/09

that sudo can be configured with the visudo command and that sudo can produce audit logs. See https://www.sudo.ws/posts/2022/05/sudo-for-blue-teams-how-to-control-and-log-better/
that there’s an Element.closest(selector) method, which means you can run this function to set the url to the closest link to an element:
```
((el) => {
  let id = el.closest("[id]")?.id;
  if (!id) return;
  else location.hash = id;
})($0);
```
that you can use
```
  SELECT /*...*/
  FROM one_table
  JOIN other_table
    USING (common_column)
```
as shorthand for one_table.common_column = other_table.common_column. See https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM

That pg_dumpall exists. That you need to use pg_dumpall to dump tablespaces, roles, and subscriptions. That pg_dumpall will dump the postgres role by default, causing resultant dump to fail to restore. Fortunately, I’m able to grep -ve '^CREATE ROLE postgres

2022/11/14

That pg_catalog.pg_shdepend exists, and “records the dependency relationships between database objects and shared objects, such as roles.” See https://www.postgresql.org/docs/current/catalog-pg-shdepend.html

2022/12/14

that it’s possible to create indices on materialized views in postgres: https://www.postgresql.org/docs/current/rules-materializedviews.html#RULES-MATERIALIZEDVIEWS

2023/01/01

The difference between triggers and event triggers in postgres:

Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events. Like regular triggers, event triggers can be written in any procedural language that includes event trigger support, or in C, but not in plain SQL.
– https://www.postgresql.org/docs/current/event-triggers.html

2023/01/30

That psql et al. support colors:

export PG_COLOR=auto # only colorize output to ttys

learned from https://fluca1978.github.io/2023/01/23/PostgreSQLColors.html See also:

Today I Learned