set_user
2024-11-23 (Saturday)
PostgreSQL set_user Extension Module
The Documentation says a bit ambiguously: First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once What kind of join? It’s source LEFT JOIN target. Consider these two tables: hjp=> create table target(id int, m text); CREATE TABLE Time: 18.459 ms hjp=> insert into target(id) values(1), (2), (3), (4); INSERT 0 4 Time: 5.
Patroni sets use_slots: true by default, but it removes the slot 30 seconds after a member disappears, rendering it effectively useless. The official(?) recommendation is to use continuous archiving (but then you don’t need slots either). The (IMHO better) alternative is to configure permanent slots: slots: nodename1: type: physical nodename2: type: physical nodename3: type: physical Alexander Kukushkin on pgsql-general Patroni Doc
Database PyPI Debian Ubuntu import Rows as named tuples PostgreSQL psycopg import psycopg import psycopg.rows csr = db.cursor(row_factory=psycopg.rows.namedtuple_row) MySQL/MariaDB mysqlclient python3-mysqldb python3-mysqldb import MySQLdb MySQL/MariaDB mysql-connector n/a python3-mysql.connector import mysql.connector csr = db.cursor(named_tuple=True)
Alvaro Herrera on pgsql-general: if you set ALTER TABLE tab ALTER COLUMN byt SET STORAGE EXTERNAL then compression is not used, and random access becomes fast.
https://sqitch.org/ For every migration you write three migration scripts: A deploy script, a revert script and a verify script. There is also a plan file which lists the necessary migrations in the right order and sqitch keeps track of the current state of the database in the sqitch schema, and applies only those migrations which haven’t been applied yet. So quite similar to Django migrations except that you have to write the migration scripts yourself, they aren’t automatically generated from the diff of two schema descriptions.
https://www.cybertec-postgresql.com/en/from-md5-to-scram-sha-256-in-postgresql/
with rows_to_move as ( delete from t where ts < '2021-05-24 14:22' returning * ) insert into t_arch (select * from rows_to_move);
(Warning: Only tested with psycopg2) Sometimes you want to execute an SQL query but don’t know whether it will return a resultset in advance. An example would be a psql-like interface where the user can type arbitrary queries (DQL, DML, or DDL) or a very generic interface class. Calling fetchone() or fetchall() raises a ProgrammingError when there is no resultset. We could catch that, but that’s not elegant. Parsing the query to determine whether it should return a resultset is not feasible.