Thread: Limiting/disallowing changes of certain columns
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?
Sorry I can't be more specific than that, but this is still just a lot of scribbling on a blackboard at this point. However, since I know we are going to have to preserve some historical information I am thinking ahead on the best way to handle the situation.
Thanks,
Melvin
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?
Sorry I can't be more specific than that, but this is still just a lot of scribbling on a blackboard at this point. However, since I know we are going to have to preserve some historical information I am thinking ahead on the best way to handle the situation.
Thanks,
Melvin
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.
On Mon, Nov 11, 2013 at 4:28 PM, David Johnston <polobo@yahoo.com> wrote:
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
Melvin Call wrote> Hello all,Most common solution probably; its hard to state what might be best and one
>
> 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?
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