Can the return value of DELETE be modified in PostgreSQL

Question

I am trying to prevent users from being able to irrevocably delete spaces in a Confluence wiki. My first quick thought was to rename the spaces table to allspaces, add a new column deleted, and create a view spaces in place of the old table. The view will only return non-deleted spaces. I have created three rules to allow INSERT, UPDATE, and DELETE on the spaces view. The DELETE rule just changes the deleted field and thus removes it from the view yet all the data stays in the database.

The problem is now DELETE statements on spaces return DELETE 0 from PostgreSQL. This causes Hibernate to flip out and toss an exception and Confluence blows up.

Is there anyway to have PostgreSQL return rows modified instead of rows deleted on an INSTEAD rule.

Why do your users have direct table access to Confluence tables?! — OMG Ponies
– OMG Ponies, Commented May 17, 2010 at 21:39
They don't, Confluence has a button inside "Remove Space" that will delete the space from the database. Currently this is permanent which IMHO is very anti-wiki. I'm attempting to fix this issue at the database level. — Jeff Mc
– Jeff Mc, Commented May 17, 2010 at 22:48

Justin · Accepted Answer · 2011-04-30 17:37:57Z

1

Not sure how well this would scale (depending on the number of spaces you have to manage), but I would periodically backup the space to XML. This can be done easily through the API using Confluence CLI:

confluence --action exportSpace --space "spaceName" --file "target/output/confluencecli/spaceName.xml"

You could rotate these backups based on age and keep only the most recent ones in the event of a space deletion by a user.

To take this a step further, you could modify the action (confluence/spaces/removespace.vm) that actually deletes the space and insert the logic to backup the space to XML before the removal is confirmed. This would scale much nicer!

answered Apr 30, 2011 at 17:37

Justin

5954 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeff Mc Over a year ago

Unfortunately we have several thousand spaces so a scheduled export isn't feasible, but I do like the idea of just hijacking the remove space action.

bobflux · Accepted Answer · 2011-05-01 23:06:55Z

1

You could add an ON DELETE trigger which saves the deleted row to an archive table. You can add a rescue feature to your application to recover deleted rows.

answered May 1, 2011 at 23:06

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

3 Comments

Jeff Mc Over a year ago

Yeah that was my first attempt too, although this has the same effect as my rules do except in the opposite direction, a single row delete returns two rows modified and Hibernate throws an exception.

bobflux Over a year ago

I can't believe Hibernate does not allow to use triggers in the database, there must be an option somewhere !

Jeff Mc Over a year ago

Yes, Hibernate allows it, but it's a code change. And the whole problem is that this is a closed source product.

John P · Accepted Answer · 2010-06-28 05:38:09Z

0

I'm not really following what you want to achieve, but perhaps you could put the delete statement in a function that returns VOID and then call it 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';

To use:

select * from delete_space(3);

answered Jun 28, 2010 at 5:38

John P

15.3k4 gold badges51 silver badges58 bronze badges

Collectives™ on Stack Overflow

Can the return value of DELETE be modified in PostgreSQL

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related