Обсуждение: BUG #19389: pg_dump output differs after setting schema comment to NULL

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

It does, if you try this with any ordinary schema.  But the public
schema is special because (a) it's created at initdb and (b) initdb
gives it a comment.  pg_dump is aware of this and does not emit any
COMMENT ON SCHEMA command when public's comment is the built-in
default.  However, if you've removed public's comment, it has to
emit a command to replicate that state of affairs, so it does

    COMMENT ON SCHEMA public IS '';

It'd be slightly nicer perhaps if it said

    COMMENT ON SCHEMA public IS NULL;

but the end result is the same since the backend treats these two
commands equivalently.

If you actually want to revert the public schema to its initial state,
what you need to do is

COMMENT ON SCHEMA public IS 'standard public schema';

            regards, tom lane