sql-info.de
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | Index

For many years the documentation has contained the following statement:

If the file includes a function named _PG_fini, that function will be called
immediately before unloading the file. Likewise, the function receives no
parameters and should return void. Note that _PG_fini will only be called
during an unload of the file, not during process termination. (Presently,
unloads are disabled and will never occur, but this may change in the
future.)

meaning all those instances of _PG_fini() dutifully included in shared library code have never actually done anything (see: src/backend/utils/fmgr/dfmgr.c).

We are now finally in that future, but it has been decided that the never-implemented idea of unloading shared libraries is offically abandoned, and _PG_fini() has been removed completely (commit ab02d702).

In practical terms this will have no effect on existing code containing _PG_fini(), and the code will compile and function as before. However as of PostgreSQL 15, any _PG_fini() function will be demoted from an effective no-op to a complete waste of space, so it can be removed entirely. For that matter it can just as well be removed from code meant to build against earlier PostgreSQL versions.

Posted at 9:24 PM

Commit b3b4d8e6 moves and renames the PostgresNode and TestLib Perl modules, which form the basis of the TAP tests, to a custom namespace (PostgreSQL::Test). While this makes sense for the reasons mentioned in the commit, it does mean that extensions providing TAP tests intended to run on PostgreSQL 14 and earlier will fail "out-of-the-box" on PostgreSQL 15 with errors like:

# Looks like your test exited with 25 before it could output anything.
Dubious, test returned 25 (wstat 6400, 0x1900)

and the regress_log file may contain something like:

Undefined subroutine ... get_new_node called at ... line ....

Fortunately it's simple enough to rectify this. The PostgreSQL version number is available during the build process as VERSION_NUM, so it's basically a case of exporting this as an environment variable to make it available to the test script(s) and adjusting those appropriately.

Posted at 12:34 AM

There have been a lot of articles about new features in the recent PostgreSQL 13 release, but one set of improvements which seems to have escaped much attention is in pg_rewind, which has gained some additional useful functionality deserving of closer examination.

New option "-R /--write-recovery-conf"

As the name implies, this option causes pg_rewind to write recovery (replication) configuration (and additionally the "standby.signal" file) after rewinding a server, in exactly the same way that pg_basebackup does. This is useful for example when restoring a former primary to become a standby. Note that it can only be used in conjunction with the option --source-server, and (unless the --dry-run option is supplied) that the configuration will be written regardless of whether a rewind is needed.

As replication configuration via the recovery.conf file was removed in PostgreSQL 12, the generated replication configuration is actually appended to postgresql.auto.conf, which is the one configuration file which is guaranteed to be in a known location (the data directory) and which will be read last when PostgreSQL starts up, ensuring any prior replication configuration is overridden by whatever pg_rewind has written.

Currently the only replication configuration item actually written is primary_conninfo - be aware that this is generated from a blend of the connection information passed in --source-server and the default parameters generated by libpq, which may be different to the primary_conninfo string you would normally generate and will look something like this:

primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=node1 port=5432
sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

Note that currently replication slot configuration (primary_slot_name) cannot be generated by pg_rewind.

Also be aware that if attempting to rewind a standby which was not shut down properly, the standby.signal file must be removed otherwise pg_rewind will attempt to start the server in single user mode to run recovery, which cannot be done with replication configuration in place. See also the section "Automatic crash recovery" below.

This option was added in commit 927474ce

Posted at 10:31 AM

Let's assume you have a CSV file which looks like this:

    $ head -5 /tmp/prod.csv
    1,14,ACADEMY ACADEMY,PENELOPE GUINESS,25.99,0,1976
    2,6,ACADEMY ACE,EWAN RICKMAN,20.99,0,6289
    3,6,ACADEMY ADAPTATION,VIVIEN KAHN,28.99,0,7173
    4,3,ACADEMY AFFAIR,ALAN MARX,14.99,0,8042
    5,3,ACADEMY AFRICAN,CARRIE HANNAH,11.99,1,2183
Posted at 3:34 PM

Recently I needed to put together some simple design diagrams in a hurry and it occurred to me that pgAdmin's Graphical Query Builder would be just the thing. Admittedly its layout functionality is limited to creating boxes with tablename and columns, and drawing lines without fancy arrows between columns on different tables, but it's enough to sketch out basic entity relationships.

Posted at 11:44 AM

A recent thread on -general sparked my interest as I'm working with a large and heterogeneous code base recently converted from Oracle which makes heavy use of TO_DATE(), and it seems this function has a couple of gotchas worthy of consideration.

Posted at 2:28 AM

January 18th (Saturday):

3rd PostgreSQL Unconference@Tokyo

Time: 13:30 ~ 17:30
Location: Asushisuto Seminar Room, 4-2-1 Kudanshita-kita, Chiyoda-ku (東京都千代田区九段北4-2-1) (map)
Closest station: Ichigaya
Link: http://atnd.org/events/46187

Informal "unconference" event with no fixed agenda. Presentations mainly in Japanese, but English-speakers (and speakers in English) more than welcome. Unfortunately I won't be able to attend this time :(

February 1st (Saturday):

28th PostgreSQL Study Group

Time: 13:00 ~ 17:30
Location: AIIT Satellite Campus, 1-18-13 Soto-kanda, Chiyoda-ku (map)
Closest station: Akihabara
Link: http://www.postgresql.jp/wg/shikumi/shikumi28/view

Speakers:

  1. SAWADA Masahiko: Understanding the VACUUM source code
  2. HAYAMIZU Yuto: Performance Measurements Pt. II - Practical Session
  3. NAKANISHI Yoshinori: Monitoring PostgreSQL with Zabbix

(subject titles are my own translation)

Posted at 1:35 AM

Well I've finally fulfilled one of my 2013 New Year's resolutions, which was to write some kind of foreign data wrapper, so with no further ado here is the initial implementation. It is basically working but is still very much in the proof-of-concept/unstable/may-eat-your-data stage. Having said that, if anyone has a practical use for this I'd be very interested to hear about it - my main motivation for creating this FDW is to learn more about PostgreSQL internals.

Posted at 8:28 AM

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | Index