Why am I getting "PG::UndefinedFunction: ERROR: function gen_random

I have an existing Rails 7/PostgreSQL I can run locally and is currently deployed on Heroku. I am migrating it to a VPS running Ubuntu 22 LTS (originally Ubuntu 20 LTS) but when I attempted to run the migrations (directly or via Capistrano) they failed with the error "PG::UndefinedFunction: ERROR: function gen_random_uuid() does not exist"

The first (of ten!) migrations was failing so, rather than rewrite history and insert/modify an early migration, I fixed it on the server by manually creating the pgcrypto extension:

            myApp$  sudo su - postgres
         postgres$  psql
        postgres=#  \c myapp_production
myapp_production=#  CREATE EXTENSION pgcrypto;
                    CREATE EXTENSION
myapp_production=#  exit

My question is, why did I need to create/enable this extension on Ubuntu but not macOS or Heroku? As far as I recall I didn't do anything to enable the extension on my Mac, I simply ran rails new myapp --database=postgresql and used create_table :people, id: :uuid do |t| in my first migration.

On my Mac I'm using Postgres 14.5 (psql (PostgreSQL) 14.5 (Homebrew)). On the Ubuntu 22 LTS server I'm also using Postgres 14.5 (psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)).

When I first noticed it I was using a different version of PostgreSQL on the server (psql (PostgreSQL) 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1).

According to the Heroku Postgres web dashboard I'm using version 13.9 on Heroku.

Even stranger, when I check installed extensions for that database in both development and prod, pgcrypto is not even listed:

# macOS using psql
myapp_development=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
--------- --------- ------------ ------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

# Heroku using `heroku pg:psql`
myapp::DATABASE=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
--------- --------- ------------ ------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

How are my :uuid columns working on these two platforms!?

CodePudding user response：

The explanation is simple after all:
gen_random_uuid() was added to the main distribution with Postgres 13. (Check the Postgres version with SELECT version();)

This is unrelated to the OS of the server.
This is also unrelated to the version of the interactive terminal psql (which can differ from the server version).

The release notes of Postgres 13:

Add function gen_random_uuid() to generate version-4 UUIDs (Peter Eisentraut)

Previously UUID generation functions were only available in the external modules uuid-ossp and pgcrypto.