BUG #19389: pg_dump output differs after setting schema comment to NULL - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19389
Logged by:          Myers Carpenter
Email address:      myers@maski.org
PostgreSQL version: 18.1
Operating system:   macOS
Description:

## Summary

After adding a comment to a schema and then setting the comment to NULL,
`pg_dump` produces different output compared to the initial state before any
comment operations. The expected behavior is that `COMMENT ON SCHEMA ... IS
NULL` should return the schema to its original uncommented state.

## PostgreSQL Version

- **Version tested**: PostgreSQL 18.1
- **Also affects**: PostgreSQL 15.8 (originally discovered)

## Steps to Reproduce

```sql
-- 1. Create a fresh database
CREATE DATABASE bug_test;

-- 2. Connect to the database and run pg_dump to capture initial state
-- pg_dump -U postgres -d bug_test > before.sql

-- 3. Add a comment to the public schema
COMMENT ON SCHEMA public IS 'This is a test comment';

-- 4. Remove the comment by setting it to NULL
COMMENT ON SCHEMA public IS NULL;

-- 5. Run pg_dump again to capture final state
-- pg_dump -U postgres -d bug_test > after.sql

-- 6. Compare the two dumps
-- diff before.sql after.sql
```

A complete reproduction script is attached:
`reproduce_schema_comment_bug.sh`

## Expected Behavior

After running `COMMENT ON SCHEMA public IS NULL`, the `pg_dump` output
(`after.sql`) should be identical to the initial state (`before.sql`) since
the comment has been removed.

## Actual Behavior

The `pg_dump` output differs between the initial state and the state after
setting the comment to NULL.

### Diff output:

```diff
❯ diff -u before.sql after.sql
--- before.sql  2026-01-25 11:14:06
+++ after.sql   2026-01-25 11:14:06
@@ -2,7 +2,7 @@
 -- PostgreSQL database dump
 --

-\restrict aqAx6we9KE3oH6zf20OeaMzserVgGt61Bur1We2fbtTSwOGn9oWxRkNKmV0KwIq
+\restrict mh3e27bgcLRZ3ZeCcSZ8bz9Y9CuKPNoQPfpNyaPX5ptuAquRcv8ubIefd179N02

 -- Dumped from database version 18.1 (Debian 18.1-1.pgdg13+2)
 -- Dumped by pg_dump version 18.1 (Debian 18.1-1.pgdg13+2)
@@ -20,8 +20,15 @@
 SET row_security = off;

 --
+-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pg_database_owner
+--
+
+COMMENT ON SCHEMA public IS '';
+
+
+--
 -- PostgreSQL database dump complete
 --

-\unrestrict aqAx6we9KE3oH6zf20OeaMzserVgGt61Bur1We2fbtTSwOGn9oWxRkNKmV0KwIq
+\unrestrict mh3e27bgcLRZ3ZeCcSZ8bz9Y9CuKPNoQPfpNyaPX5ptuAquRcv8ubIefd179N02
```

## Workaround

The only one I've found is once the comment is set is to dump, edit dump,
then load dump.  We have a process that copies a staging database and we
filter out.




## reproduce_schema_comment_bug.sh

#!/bin/bash
# PostgreSQL Schema Comment NULL Bug - Reproduction Script
# Tested against: PostgreSQL 18.1
#
# This script demonstrates unexpected pg_dump behavior when:
# 1. A comment is added to a schema
# 2. The comment is then set to NULL
#
# Expected: pg_dump output should be identical before adding comment
#           and after setting comment to NULL
# Actual:   [Run script to observe actual behavior]

set -e
set -x

PG_VERSION="18.1"
CONTAINER_NAME="postgres-schema-comment-bug"

# Cleanup any existing container
docker rm -f "$CONTAINER_NAME" 2>/dev/null || true

# Pull specific PostgreSQL version
echo "=== Pulling PostgreSQL $PG_VERSION ==="
docker pull "postgres:$PG_VERSION"

# Start PostgreSQL container
echo "=== Starting PostgreSQL container ==="
docker run --name "$CONTAINER_NAME" \
    -e POSTGRES_PASSWORD=password \
    -d \
    -p 5432:5432 \
    "postgres:$PG_VERSION"

# Wait for PostgreSQL to be ready
echo "=== Waiting for PostgreSQL to be ready ==="
sleep 5
until docker exec "$CONTAINER_NAME" pg_isready -U postgres; do
    echo "Waiting for postgres..."
    sleep 2
done

# Create test database
echo "=== Creating test database ==="
docker exec "$CONTAINER_NAME" psql -U postgres -c "CREATE DATABASE
bug_test;"

# Capture initial state (before any comment operations)
echo "=== Capturing initial pg_dump (before.sql) ==="
docker exec "$CONTAINER_NAME" pg_dump -U postgres -d bug_test > before.sql

# Add comment to public schema, then set it to NULL
echo "=== Adding comment to schema, then setting to NULL ==="
docker exec "$CONTAINER_NAME" psql -U postgres -d bug_test -c "
-- Step 1: Add a comment to the public schema
COMMENT ON SCHEMA public IS 'This is a test comment';
"

# Verify the comment was added
echo "=== Verifying comment was added ==="
docker exec "$CONTAINER_NAME" psql -U postgres -d bug_test -c "
SELECT obj_description('public'::regnamespace, 'pg_namespace') AS
schema_comment;
"

docker exec "$CONTAINER_NAME" psql -U postgres -d bug_test -c "
-- Step 2: Set the comment to NULL (should remove the comment)
COMMENT ON SCHEMA public IS NULL;
"

# Verify the comment was removed
echo "=== Verifying comment was removed ==="
docker exec "$CONTAINER_NAME" psql -U postgres -d bug_test -c "
SELECT obj_description('public'::regnamespace, 'pg_namespace') AS
schema_comment;
"

# Capture final state (after comment set to NULL)
echo "=== Capturing final pg_dump (after.sql) ==="
docker exec "$CONTAINER_NAME" pg_dump -U postgres -d bug_test > after.sql

# Show PostgreSQL version info for bug report
echo "=== PostgreSQL Version Information ==="
docker exec "$CONTAINER_NAME" psql -U postgres -c "SELECT version();"

# Compare dumps
echo "=== Comparing before.sql and after.sql ==="
if diff -q before.sql after.sql > /dev/null 2>&1; then
    echo "SUCCESS: Files are identical (expected behavior)"
else
    echo "BUG DETECTED: Files differ unexpectedly"
    echo ""
    echo "=== Differences ==="
    diff -u before.sql after.sql || true
fi

# Cleanup
echo "=== Cleaning up ==="
docker stop "$CONTAINER_NAME"
docker rm "$CONTAINER_NAME"

echo ""
echo "=== Output files created ==="
echo "  - before.sql (initial state)"
echo "  - after.sql (after comment added then set to NULL)"
echo ""
echo "Attach these files to your bug report."


## minimal_reproduction.sql

-- PostgreSQL Schema Comment NULL Bug - Minimal Reproduction
-- Tested on: PostgreSQL 18.1, 15.8
--
-- This demonstrates unexpected pg_dump behavior when a schema comment
-- is set and then removed with NULL.

-- Setup: Create test database
-- psql -U postgres -c "CREATE DATABASE bug_test;"

-- Connect to bug_test database for the following:

-- Step 1: Capture initial pg_dump
-- $ pg_dump -U postgres -d bug_test > before.sql

-- Step 2: Add comment to public schema
COMMENT ON SCHEMA public IS 'Test comment';

-- Step 3: Verify comment exists
SELECT
    n.nspname AS schema_name,
    obj_description(n.oid, 'pg_namespace') AS comment
FROM pg_namespace n
WHERE n.nspname = 'public';
-- Expected: 'Test comment'

-- Step 4: Remove comment by setting to NULL
COMMENT ON SCHEMA public IS NULL;

-- Step 5: Verify comment was removed
SELECT
    n.nspname AS schema_name,
    obj_description(n.oid, 'pg_namespace') AS comment
FROM pg_namespace n
WHERE n.nspname = 'public';
-- Expected: NULL

-- Step 6: Capture final pg_dump
-- $ pg_dump -U postgres -d bug_test > after.sql

-- Step 7: Compare (expected: identical, actual: differs)
-- $ diff before.sql after.sql

-- Additional diagnostic queries:

-- Check pg_description for the public schema
SELECT
    d.objoid,
    d.classoid,
    d.objsubid,
    d.description,
    c.relname AS classname
FROM pg_description d
JOIN pg_class c ON d.classoid = c.oid
WHERE d.objoid = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

-- Check schema details
SELECT
    oid,
    nspname,
    nspowner,
    nspacl
FROM pg_namespace
WHERE nspname = 'public';

## before.sql

--
-- PostgreSQL database dump
--

\restrict aqAx6we9KE3oH6zf20OeaMzserVgGt61Bur1We2fbtTSwOGn9oWxRkNKmV0KwIq

-- Dumped from database version 18.1 (Debian 18.1-1.pgdg13+2)
-- Dumped by pg_dump version 18.1 (Debian 18.1-1.pgdg13+2)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

\unrestrict aqAx6we9KE3oH6zf20OeaMzserVgGt61Bur1We2fbtTSwOGn9oWxRkNKmV0KwIq

## after.sql

--
-- PostgreSQL database dump
--

\restrict mh3e27bgcLRZ3ZeCcSZ8bz9Y9CuKPNoQPfpNyaPX5ptuAquRcv8ubIefd179N02

-- Dumped from database version 18.1 (Debian 18.1-1.pgdg13+2)
-- Dumped by pg_dump version 18.1 (Debian 18.1-1.pgdg13+2)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pg_database_owner
--

COMMENT ON SCHEMA public IS '';


--
-- PostgreSQL database dump complete
--

\unrestrict mh3e27bgcLRZ3ZeCcSZ8bz9Y9CuKPNoQPfpNyaPX5ptuAquRcv8ubIefd179N02