Limiting/disallowing changes of certain columns

Melvin Call wrote

Hello all,

I am creating a data model that contains time-dependent data. I only need
a
snapshot solution, where we capture the initial state of all fields in the
record, and we then store subsequent changes as a new row in a child
table.
What I am looking at is creating a primary table that contains the fields
we do not need to track, or do not want to allow changes to, and a child
table that contains the ones we do need to track. My question regards the
columns in the parent table that we want to disallow changes to, or once
they have been changed to a certain value to prevent further changes. An
example would be an isvalid Boolean. Once a record has been marked invalid
(perhaps it was created by mistake), we do not want it to accidentally be
marked valid but we want to preserve it and any history associated with
it.
Other cases would be the initial creation timestamp or the initial
creation
person.

I'm assuming that I can create a BEFORE trigger that will prevent the
changes (and probably even return a custom error), but is that the only
and/or best way to handle such a case?

Most common solution probably; its hard to state what might be best and one
possible alternative is to move those "invalid" records to an insert-only
archive table so they are available for reference but do not clutter up the
main table.

Probably the main reason you'd want to avoid a trigger is if performance was
suffering intolerably. But until that happens in reality you might as well
take the most common and likely least complicated solution.

An aside: Look into "hstore" (or maybe json) for key-store functionality
which may be a technical tool you can use in your ultimate solution.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Limiting-disallowing-changes-of-certain-columns-tp5777805p5777813.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Nov 11, 2013 at 4:28 PM, David Johnston <polobo@yahoo.com> wrote:

Melvin Call wrote

Hello all,

I am creating a data model that contains time-dependent data. I only need
a
snapshot solution, where we capture the initial state of all fields in

the

record, and we then store subsequent changes as a new row in a child
table.
What I am looking at is creating a primary table that contains the fields
we do not need to track, or do not want to allow changes to, and a child
table that contains the ones we do need to track. My question regards the
columns in the parent table that we want to disallow changes to, or once
they have been changed to a certain value to prevent further changes. An
example would be an isvalid Boolean. Once a record has been marked

invalid

(perhaps it was created by mistake), we do not want it to accidentally be
marked valid but we want to preserve it and any history associated with
it.
Other cases would be the initial creation timestamp or the initial
creation
person.

I'm assuming that I can create a BEFORE trigger that will prevent the
changes (and probably even return a custom error), but is that the only
and/or best way to handle such a case?

Most common solution probably; its hard to state what might be best and one
possible alternative is to move those "invalid" records to an insert-only
archive table so they are available for reference but do not clutter up the
main table.

Probably the main reason you'd want to avoid a trigger is if performance
was
suffering intolerably. But until that happens in reality you might as well
take the most common and likely least complicated solution.

An aside: Look into "hstore" (or maybe json) for key-store functionality
which may be a technical tool you can use in your ultimate solution.

David J.

Hi David,

Thanks for the quick reply. Based on historical activity of the system we
are in the process of correcting, performance is not likely to be an issue.
We are tracking specific research activities related to a single past event
with the majority of the research funding expected to go out over the next
two years but with some historical datasets already collected and
available. We have approximately 700 records in a bit less than a year but
that is expected to double or triple over the next year and then stabilize
there for a few years before beginning to drop. The total number of
invalidated records so far has been less than 50. Apologies for the
omissions, I didn't think to include that information for some reason. The
majority of our activity will be people searching for and downloading our
stored data. So a small amount of inserts, not many changes, and a lot of
queries. But still probably less than a couple of hundred hits a day at max.

I do like your idea of moving invalid records to a different archive table,
and my initial feeling is that it may be an even more elegant solution for
our needs. I will look into your other suggestions as well. But at the
least it doesn't look like I am way out of line in my thinking.

Melvin