PostgresQL Delete Trigger does not delete row

Question

I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would insert the deleted data into the archive table. The trigger seems to run just fine, with the data being replicated to the archive table. The original data, however, is not deleted. The trigger only seems to run on the first delete query. If I run the same delete statement again (on the original data), both the row in the original table and the archive table is deleted. I've also tried to create trigger functions that are specifically tailored to inserting into a specific table, but the same result occurs. Any help is greatly appreciated.

Trigger function

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
 RAISE NOTICE 'Running trigger';
 EXECUTE format('INSERT INTO %I.%I SELECT 1ドル.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
 USING OLD;
 RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

Example trigger

CREATE TRIGGER delete_test
 AFTER DELETE
 ON test
 FOR EACH ROW
 EXECUTE PROCEDURE archive_record();

Example table

create table test
(
 id serial primary key,
 name varchar(128) not null,
);

Example archive table

CREATE TABLE test_archive (
) INHERITS(test);

• jjanes: You have only shown us how you set up the system, not how you tested the system. You are probably doing the test wrong or interpreting the results wrong. When you delete from test, it moves the rows to test_archive. But they still show up in test when selected, because that is how inheritance works. You would have to select from only test to exclude the tuples that had been moved.

Answer 1

Inheritance could be a useful tool, but for now it has its many flaws, as you noticed.

So you should dismiss it, if the system don't react to the way you think

create table test
(
 id serial primary key,
 name varchar(128) not null
);

create table test_archive
(
 id serial primary key,
 name varchar(128) not null
);

CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
 RAISE NOTICE 'Running trigger';
 EXECUTE format('INSERT INTO %I.%I SELECT 1ドル.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
 USING OLD;
 RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER delete_test
 AFTER DELETE
 ON test
 FOR EACH ROW
 EXECUTE PROCEDURE archive_record();

insert into test values (1,'test1'),(2,'test2')

2 rows affected

DELETE FROM test WHERE id = 1

1 rows affected

SELECT * FROM test_archive

id | name 
-: | :----
 1 | test1

SELECT * FROM test

id | name 
-: | :----
 2 | test2

db<>fiddle here

Answer 2

Since test_archive inherits from test, deleting rows from test will also delete from test_archive.

If you insist on using inheritance, you would have to delete with

DELETE FROM ONLY test ...

But you should abandon inheritance and use

CREATE TABLE test_archive (LIKE test);