pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPeer-review requested of soft-delete scheme
2+ messages / 2 participants
[nested] [flat]
* Peer-review requested of soft-delete scheme
@ 2013-04-16 16:24 Mark Stosberg <mark@summersault.com>
2013-04-18 11:44 ` Re: Peer-review requested of soft-delete scheme Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 2+ messages in thread
From: Mark Stosberg @ 2013-04-16 16:24 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm working on designing a soft-delete scheme for our key entity-- there
are 17 other tables that reference our key table via RI. Let's call the
table "foo".
I understand there are a couple common design patterns for soft-deletes:
1. Use a trigger to move the rows to a "tombstone table".
2. Add an "deleted flag" to the table.
The "tombstone table" approach is out for us because all the RI.
The "deleted flag" approach would be a natural fit for us. There's
already a "state" column in the table, and there will only be a small
number rows in the "soft-deleted" state at a time, as we'll hard-delete
them after a few months. The table has only about about 10,000 rows in
it anyway.
My challenge is that I want to make very hard or impossible to access
the soft-deleted rows through SELECT statements. There are lots of
selects statements in the system.
My current idea is to rename the "foo" table to something that would
stand-out like "foo_with_deleted_rows". Then we would create a view
named "foo" that would select all the rows except the soft-deleted views.
I think that would make it unlikely for a developer or reviewer to mess
up SELECTs involving the statement. Inserts/Updates/Delete statements
against the table are view, and coud reference the underlying table
directly.
Is this sensible? Is there another approach to soft-deletes I should be
considering?
Thanks!
Mark
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Peer-review requested of soft-delete scheme
2013-04-16 16:24 Peer-review requested of soft-delete scheme Mark Stosberg <mark@summersault.com>
@ 2013-04-18 11:44 ` Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 2+ messages in thread
From: Jasen Betts @ 2013-04-18 11:44 UTC (permalink / raw)
To: pgsql-sql
On 2013-04-16, Mark Stosberg <mark@summersault.com> wrote:
> My challenge is that I want to make very hard or impossible to access
> the soft-deleted rows through SELECT statements. There are lots of
> selects statements in the system.
>
> My current idea is to rename the "foo" table to something that would
> stand-out like "foo_with_deleted_rows". Then we would create a view
> named "foo" that would select all the rows except the soft-deleted views.
[...]
> Is this sensible? Is there another approach to soft-deletes I should be
> considering?
yes, rename the table and replace it with a view that excludes the soft
deleted records. Make "do instead" rules to handle inserts, updates
and deletes on the view by rediecting them to the base table.
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-04-18 11:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-16 16:24 Peer-review requested of soft-delete scheme Mark Stosberg <mark@summersault.com>
2013-04-18 11:44 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox