ads' corner

Advent of Code 2020: "Report Repair" - Day 1

Started “Advent of Code” with the kid, the kid is polishing the Python (and English) skills. I thought I better do this in SQL.


SELECT pg_sleep_until('#800Monies');

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?


How long will a 64 bit Transaction-ID last in PostgreSQL?

At FOSDEM someone asked how long 64 bit Transaction-IDs will last.

To refresh: PostgreSQL is currently using 32 bits for the TXID, and is good for around 4 billion transactions:


PostgreSQL with natural language support

In today’s fast development cycles and with the available, rapidly changing technologies there is less time for the rational planning of a new application. Since the database is usually the last in the development chain, programmers tend to avoid learning yet another programming language for relational databases. The PostgreSQL Project is aware of this problem.


PostgreSQL 9.0: Includes the new MySQL Emulation Layer

PostgreSQL 9.0.0, released today, contains the MySQL Emulation Layer.

To enable this feature, set the mysql_compatible option GUC to on.


PostgreSQL 8.4: Column Permissions

Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem: create the view with the required (allowed) columns, revoke all permissions from the underlaying table, grant permissions to the view.


Speed up your Serendipity blog running on PostgreSQL

In the last days I monitored the PostgreSQL logfile on my webserver to find slow queries in the Serendipity blog software. I was hunting another bug in the “events” module, therefore I decided it’s woth the additional work and maybe I can tweak some settings to speed up the response time a bit.


Single line input for psql copy command

I have the following problem:

A database server and another client machine. I have to execute several \copy commands in psql on the client machine to retrieve data and store the output in CSV files. The PostgreSQL COPY command is not an option because it stores the results on the database server, not the client machine. All \copy commands are executed from several files containing SQL commands, which are concated together by a Makefile and feeded into psql. So far so good.


role ... cannot be dropped because some objects depend on it

I want to drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But PostgreSQL has objections:


create language if not exist

In a customer project I have to setup a database from a Makefile. Part of my problem: on Windows the installer may or may not install plpgsql into template1, therefore in consequence this language may or may not be activated in every new database. But that’s not predictable. This problem can appear on different Linux/Unix distributions too.