D.Decoene
PPrisma
•Created by D.Decoene on 4/9/2025 in #help-and-questions
PostgreSQL Replica Identity Error Despite DEFAULT Setting
I am encountering a persistent PostgreSQL error related to logical replication and REPLICA IDENTITY on my database, and I would appreciate your assistance investigating the replication configuration.
Problem:
When attempting to delete a record from our User table, which has cascading deletes (onDelete: Cascade) set up for related tables (including Session, ProviderProfile, Subscription, etc. via Prisma), the operation fails with the following error:
ERROR: cannot delete from table "Session" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
Use code with caution.
Troubleshooting Steps Taken:
Set Replica Identity: We identified that logical replication likely requires REPLICA IDENTITY for tables involved in cascaded deletes. We created and successfully applied Prisma migrations (prisma migrate deploy) that execute ALTER TABLE "TableName" REPLICA IDENTITY DEFAULT; for all relevant tables, including "Session", "ProviderProfile", "Subscription", "PaymentHistory", etc.
Verified Table Settings: We connected to the database directly and confirmed via SQL query against pg_catalog.pg_class that the relreplident column for the "Session" table (and the other affected tables) is indeed set to 'd', corresponding to DEFAULT.
Verified Publication Settings: We confirmed that a publication named all_models exists. Querying pg_catalog.pg_publication shows that this publication has puballtables = true and pubdelete = true. This means it implicitly covers the "Session" table and is attempting to publish delete operations for it.
Issue:
Despite the "Session" table having REPLICA IDENTITY DEFAULT correctly set, the delete operation (cascading from User) still fails with the error message indicating a lack of replica identity for the publication.
6 replies
PPrisma
•Created by D.Decoene on 4/5/2025 in #help-and-questions
new guy with wierd error when deleting a record trough prisma studio
i got this message when deleting a record. same thing happens with prisma postgres.
8 replies