PostgreSQL crashes when using pgcrypto functions

Problem

After upgrading PostgreSQL from 7.4 to 8.2 it started crashing sporadically with the following entries in the log file:

LOG:  server process (pid 21460) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing shared memory and semaphores

Cause

There were changes between PostgreSQL releases in functions definition used by pgcrypto module.

According to [1] and [3] in PostgreSQL 8.0 the pgcrypto functions were non-strict and checked for NULLs, in PostgreSQL 8.1 they were made STRICT, in PostgreSQL 8.2 the NULL checks were removed from code.

Solution

In order to correct the problem these functions are needed to be recreated. Required SQL commands are defined in pgcrypto.sql which is usually located in /usr/local/pgsql/share/contrib when pgcrypto is installed.

psql -d DBNAME -f /usr/local/pgsql/share/contrib/pgcrypto.sql

Also it is a good idea to add functions to template1 database if you use pgcrypto often. template1 is the default source database name when using CREATE DATABASE command.

References:

  1. http://marc.info/?l=postgresql-general&m=118794006505296&w=2
  2. http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg109917.html
  3. http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pgcrypto/pgcrypto.sql.in

Leave a Reply

You may use simple HTML to add links or lists to your comment. Also use <pre><code class="language-*">...</code></pre> to mark up code snippets. We support language-js, language-markup and language-css for comments.
(Optional)