Is it possible to change DELETE value in PostgreSQL
I am trying to prevent users from being able to permanently delete spaces on the Confluence wiki. My first thought was to rename the table spaces
to allspaces
, add a new column deleted
and create a view spaces
instead of the old table. Only unfilled spaces will appear in the view. I have created three rules to allow INSERT, UPDATE and DELETE on a view spaces
. The DELETE rule simply modifies the field deleted
and thus removes it from the view, but all data remains in the database.
Now the problem is with the DELETE statements on spaces
return DELETE 0
from PostgreSQL. This causes Hibernate to roll back and throw an exception and Confluence explodes.
Is there anyway that the returned PostgreSQL rows are modified instead of the rows deleted by the INSTEAD rule.
a source to share
Not sure how much it scales (depending on the amount of whitespace you have to manage), but I would reserve space for XML periodically. This can be easily done using the API using the Confluence CLI :
confluence --action exportSpace --space "spaceName" --file "target/output/confluencecli/spaceName.xml"
You can rotate these backups based on age and keep only the most recent ones in case the space is deleted by the user.
To take this even further, you can change the action ( confluence/spaces/removespace.vm
), which will actually remove the whitespace, and insert logic to back up the space in XML before the deletion is confirmed. It will be much better!
a source to share
I am not doing what you want to achieve, but perhaps you can put a delete statement in a function that returns a VOID and then call it.
CREATE OR REPLACE FUNCTION delete_space(pid integer)
RETURNS void AS
$BODY$
DECLARE aid INTEGER;
BEGIN
delete from spaces where id=pid;
return;
END;
$BODY$
LANGUAGE 'plpgsql';
For use:
select * from delete_space(3);
a source to share