From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dbman@sqlexec.com
Subject: BUG #19013: When creating a table with the "...LIKE...INCLUDING ALL" construct, REPLICA IDENTITY output is wrong
Date: Tue, 05 Aug 2025 21:26:56 +0000
Message-ID: <19013-04a5827d56b97202@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19013
Logged by: Michael Vitale
Email address: dbman@sqlexec.com
PostgreSQL version: 17.5
Operating system: CentOS 8 Streams
Description:
Here is a simple schema to test with:
DROP SCHEMA IF EXISTS testing CASCADE;
CREATE SCHEMA testing;
CREATE TABLE testing.notifications
(
id uuid NOT NULL DEFAULT uuid_generate_v4(),
employee_id uuid NOT NULL,
seen_at timestamp with time zone,
created_at timestamp with time zone DEFAULT now(),
last_changed timestamp with time zone NOT NULL DEFAULT now(),
file_id uuid NOT NULL,
conversation_id uuid,
document_id uuid,
message_id uuid,
CONSTRAINT notifications_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;
ALTER TABLE ONLY testing.notifications REPLICA IDENTITY FULL;
ALTER TABLE IF EXISTS testing.notifications OWNER to postgres;
-- Validate table is defined with REPLICA IDENTITY FULL
SELECT n.nspname AS schema_name, c.oid, c.relname AS table_name,
c.relreplident, i.indisreplident, idx_c.relname AS
replica_identity_index_name,
CASE c.relreplident WHEN 'd' THEN 'DEFAULT' WHEN 'n' THEN 'NOTHING' WHEN 'f'
THEN 'FULL' WHEN 'i' THEN 'INDEX' END AS replica_identity_setting
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN
pg_index i ON i.indrelid = c.oid AND i.indisreplident = TRUE LEFT JOIN
pg_class idx_c ON idx_c.oid = i.indexrelid
WHERE n.nspname = 'testing' AND c.relkind = 'r';
schema_name | oid | table_name | relreplident | indisreplident |
replica_identity_index_name | replica_identity_setting
-------------+-------+---------------+--------------+----------------+-----------------------------+--------------------------
testing | 58431 | notifications | f | |
| FULL
-- Create another table using the LIKE construct
CREATE TABLE testing.notifications2 (LIKE testing.notifications INCLUDING
ALL);
-- Run the query again:
SELECT n.nspname AS schema_name, c.oid, c.relname AS table_name,
c.relreplident, i.indisreplident, idx_c.relname AS
replica_identity_index_name,
CASE c.relreplident WHEN 'd' THEN 'DEFAULT' WHEN 'n' THEN 'NOTHING' WHEN 'f'
THEN 'FULL' WHEN 'i' THEN 'INDEX' END AS replica_identity_setting
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN
pg_index i ON i.indrelid = c.oid AND i.indisreplident = TRUE LEFT JOIN
pg_class idx_c ON idx_c.oid = i.indexrelid
WHERE n.nspname = 'testing' AND c.relkind = 'r';
schema_name | oid | table_name | relreplident | indisreplident |
replica_identity_index_name | replica_identity_setting
-------------+-------+----------------+--------------+----------------+-----------------------------+--------------------------
testing | 58431 | notifications | f | |
| FULL
testing | 58439 | notifications2 | d | |
| DEFAULT
(2 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dbman@sqlexec.com
Subject: Re: BUG #19013: When creating a table with the "...LIKE...INCLUDING ALL" construct, REPLICA IDENTITY output is wrong
In-Reply-To: <19013-04a5827d56b97202@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox