PL/pgSQL function trigger does not insert/update/delete records even though it is inteded and ought to

Lately, I developed an insert trigger that was supposed to check whether for specific cases a column of the new was filled or not and raise an exception if appropriate. However, it did not. It turns out that PL/pgSQL functions need to return a record, if one wants the original operation to happen. If NULL is returned, the operation is not carried out. Coming from Oracle, I missed it, even though it is quite clearly documented.

In hhe PostgreSQL way, it is easy to write a trigger that just does nothing if necessary. I would not know by heart how to do that in Oracle.

Syntax error at "%" … "invalid type name" error in variable declaration of PL/pgSQL

There I was hunting down the cause mentioned error message, being quite clueless. Thankfully, the PostgreSQL e-mail list was helpful, nudging me in the right direction. The message was a bit obfuscated by my use of unusual characters in the names of my objects and variables, in my case: ⠒

I was believing that for some strange reason, PL/pgSQL was unable to handle this character in the type clause, e.g.

variable⠒name  table⠒name.column⠒name%TYPE;

The point was, that I had messed up the order of installation of the things. The table, I was trying to copy the type from, had not been recreated by the installation script yet. I would expect that the parser would return with a missing table/view message, but "invalid type name" is not wrong, if not so straight forward as it could be. The developers of that part actually were aware of a problem there. Maybe the will get around to improve the error message.

Database objects missed in standby

Ok, this is a stupid one of mine. I just did not qualify the object in the create statements with schema name etc. so they were created in the default locations where I was looking for them in the freshly database and schema.

Streaming replication problem: Master makes committed change which standby follows but master gets locked

Problem

Streaming replication works somewhat in the sense that a transaction in master gets locked after commit but the changes can be seen in standby.

Cause

Master cannot associate success of replication with standby. I do not now why this is needed anyway, why it is not sufficient to know that there are as many success stories.

Solution

resolve.config (standby) contains the variable primary_conninfo which needs to contain the parameter application_name. Its associated value must show up in the list of masters postgres.config synchronous_standby_names.

primary_conninfo = 'application_name=main2 ...'
synchronous_standby_names = main2,main3,main4

Streaming replication FATAL: database system identifier differs between the primary and standby

Cause (in my case)

I tried to set-up replication without making standby a clone of master.

Solution

Take a base backup with pg_basebackup and make standby use it. For freshly initialised databases copying the generated folder and file structure into the data directory of the standby was sufficient.

Streaming replication problem: connection from repuser@standby fails

Apparently, libpq is a bit picky with passwords provided in connection strings. If no special characters are present, it works like a charm. No matter what password parameter of primary_conninfo is expected to get the plain text password.

Working config for synchronous stream replication with both nodes on same host

== Hot standby ==

/etc/postgresql/10/main2/pg_hba.conf
host    replication     all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     repuser         ::1/0                   md5
host    replication     repuser         0.0.0.1/0               md5
local   replication     repuser                                 peer

/etc/postgresql/10/main2/postgresql.conf
wal_level = replica
#synchronous_commit = on
max_replication_slots = 12
synchronous_standby_names = 'main'
hot_standby = on
log_min_messages = warning
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'

/etc/postgresql/10/main2/recovery.conf
standby_mode = 'on'
primary_conninfo = 'application_name=main2 host=localhost user=repuser port=5432 password=<plain text>'

== master ==
/etc/postgresql/10/main/pg_hba.conf
host    replication     all             ::1/128                 md5
host    replication     all             127.0.0.1/32            md5
host    replication     repuser         ::1/0                   md5
host    replication     repuser         0.0.0.1/0               md5
local   replication     repuser                                 peer

/etc/postgresql/10/main/postgresql.conf
wal_level = replica
#synchronous_commit = on
archive_mode = off
max_wal_senders = 12
max_replication_slots = 12
synchronous_standby_names = 'main2'
hot_standby = on
wal_receiver_timeout = 60s
log_min_messages = warning
log_connections = on
log_statement = 'ddl'
log_replication_commands = on
lc_messages = 'C.UTF-8'

/etc/postgresql/10/main/recovery.conf
standby_mode = 'off'
primary_conninfo = 'application_name=main host=localhost user=repuser port=5433 password=<plain text>'