On Wed, Dec 17, 2025, at 6:43 PM, GRANT ZHOU wrote:
> On Wed, Dec 17, 2025 at 12:50 PM Euler Taveira <[email protected]> wrote:
>> Each table needs to say what's its row identifier. The user created a table
>> without primary key. Well, create a primary key. There are dozens of
>> thousands
>> of objects. Use a script.
> However, I’d like to share a user perspective regarding the "use a
> script" approach. The main value of `FOR TABLES IN SCHEMA` is
> *in-database automation*. If users still need to maintain external
> scripts to monitor and `ALTER` new tables to prevent replication
> errors, it significantly diminishes the value of that automation.
>
As I tried to explain in the previous email, the problem with FOR ALL TABLES
and FOR TABLES IN SCHEMA syntax is that the is no catalog information about the
relations; the list of relations is collected at runtime.
When I suggested "use a script" I was referring to fix the logical replication
setup regarding the lack of primary key. There is no need to have an automation
outside the database, use an event trigger. If your lazy user doesn't create
the primary key, assign REPLICA IDENTITY FULL. Something like
-- This example is far from being a complete solution for fixing the lack of
-- primary key in a logical replication scenario.
-- ALTER TABLE should be supported too
CREATE OR REPLACE FUNCTION event_trigger_for_replica_identity()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
rec record;
ricnt integer := 0;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
IF obj.command_tag = 'CREATE TABLE' THEN
SELECT COUNT(*) INTO ricnt FROM pg_index WHERE indrelid = obj.objid
AND indisprimary;
RAISE NOTICE 'ricnt: %', ricnt;
IF ricnt = 0 THEN
EXECUTE 'ALTER TABLE ' || obj.object_identity || ' REPLICA
IDENTITY FULL';
END IF;
END IF;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER event_trigger_for_replica_identity
ON ddl_command_end
EXECUTE FUNCTION event_trigger_for_replica_identity();
CREATE TABLE event_trigger_test_1 (a int);
\d+ event_trigger_test_1
CREATE TABLE event_trigger_test_2 (a int primary key);
\d+ event_trigger_test_2
CREATE TABLE event_trigger_test_3 (a int, b text not null, primary key(b));
\d+ event_trigger_test_3
--ALTER TABLE event_trigger_test_3 DROP CONSTRAINT event_trigger_test_3_pkey;
--\d+ event_trigger_test_3
DROP EVENT TRIGGER event_trigger_for_replica_identity;
DROP FUNCTION event_trigger_for_replica_identity;
DROP TABLE event_trigger_test_1, event_trigger_test_2, event_trigger_test_3;
8<----------------------------------------------------------------------------8<
> Additionally, tables without Primary Keys are valid SQL and extremely
> common in enterprise environments (e.g., audit logs, data warehousing).
> In large-scale deployments, enforcing PKs on every single table isn't
> always practical.
>
I'm not saying users shouldn't create tables without a primary key. I'm arguing
that this decision should take into account what adjustments need to be made to
use these tables in logical replication.
>
> I think the goal of this proposal is not to change the underlying table
> property design, but rather to seek a mechanism (like a Publication
> option) to ensure this automation functions safely without external
> intervention. It is simply about allowing the database to handle these
> valid, common scenarios gracefully when automation is enabled.
>
You didn't get it. You already have one property to handle it and you are
proposing to add a second property to handle it.
I think you are pursuing the wrong solution. IMO we need a solution to enforce
that the logical replication contract is valid. If you create or modify a table
that is part of a publication, there is no validation that that table complies
with the publication properties (update and delete properties should require an
appropriate replica identity). We should close the gaps in both publication and
table.
--
Euler Taveira
EDB https://www.enterprisedb.com/
