Tuesday, April 1, 2014

PostgreSQL trash can

The PostgreSQL trash can is a PostgreSQL plugin that implements a trash can/wastebasket/rubbish bin/recycling container. You drop a table and it's not really gone but only moved to the trash. This allows desktop-minded users to drop tables willy-nilly while giving them the warm and fuzzy feeling of knowing that their data is still there (while giving administrators the cold and, uh, unfuzzy feeling of knowing that disk space will never really be freed again). Now they only need to think of "vacuum" as "disk defragmentation", and they'll feel right at home.
Posted by Unknown at 08:36
Labels: , ,

9 comments:

  1. Anonymous01 April, 2014 10:38

    Can we achieve the same goal using the DDL triggers?

    ReplyDelete
    Replies
    1. Unknown01 April, 2014 15:21

      No, event triggers cannot change a command into a different one.

      Delete
  2. Regina01 April, 2014 12:19

    Great extension. Does it work for anything besides tables. I have a bunch of functions and whole schemas I want to trash, but I want to be able to pull them from trash just in case.

    ReplyDelete
  3. Jim Nasby01 April, 2014 12:58

    +1 for allowing all objects, including schemas.

    Great work though in any case!

    ReplyDelete
    Replies
    1. Jim Nasby01 April, 2014 13:05

      BTW, the other thing that you'd want from this is to know when something was actually deleted so you can better manage what you empty from the trash and when (and in this case I'm in favor of not being forced to empty the entire can at once).

      We've actually implemented something similar to this with a to_delete schema, but the big thing we're missing is knowing when something was deleted (as well as the schema it originally lived in, but that hasn't been much of an issue in reality).

      Delete
  4. Crouzilles02 April, 2014 08:43

    Great addition. How can this be installed under Windows? Are all, windows users are probably going to be the ones wanting to use this the most.

    ReplyDelete
  5. Allan03 April, 2014 03:50

    This comment has been removed by a blog administrator.

    ReplyDelete
  6. Craig Ringer05 October, 2014 02:45

    Ah, bringing the great file deletion vs editing confusion to PostgreSQL ;-)

    Delete a file: oh, you can undo that.

    Delete the *contents of* a file and save it: Gone forever.

    Personally I think the Trash box is a dangerous half-measure; I've seen many (many) people burned by thinking that since you can recover a deleted file, you can recover deleted *parts of* a file too. It's a pity versioned file systems never took off, they were a much better solution despite the performance and capacity problems.

    I can see that this would be handy for some deployments but I'd want to make really sure users understood that 'DELETE FROM mytable;' won't be undo-able.

    ReplyDelete

Subscribe to: Post Comments (Atom)