Обсуждение: Howto prevent write based on date

I'm a newbie to database admin, but I'm not afraid to try - but this one
has me stumped.

I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly)
Debian Testing, i386.

My fiscal year is over and I would _like_ to prevent any changes to the
data from last year.

I looked/searched in the manual, but I don't even know what to call what
it is that I'm trying to do!

"lock" has another meaning for databases. :)

"Write" seems to bring up lots of user-related stuff.

So, I'm hoping one of you geniuses can tell me where to look, what to
look for, or how to do it. *smile*

Thanks!

               Philip, wanabe-admin

On Wednesday 04 May 2005 09:35 am, Fmiser wrote:
> I'm a newbie to database admin, but I'm not afraid to try - but this one
> has me stumped.
>
> I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly)
> Debian Testing, i386.
>
> My fiscal year is over and I would _like_ to prevent any changes to the
> data from last year.
>
> I looked/searched in the manual, but I don't even know what to call what
> it is that I'm trying to do!
>
> "lock" has another meaning for databases. :)
>
> "Write" seems to bring up lots of user-related stuff.
>
> So, I'm hoping one of you geniuses can tell me where to look, what to
> look for, or how to do it. *smile*
>
> Thanks!
>
>                Philip, wanabe-admin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
If I remember correctly SQL-Ledger has just such an option in the
administrator options. This would save you from having to play around with
the back end.

--
Adrian Klaver
aklaver@comcast.net

"Fmiser" <fmiser@gmail.com> wrote in message
news:20050504113559.6529e8cf.fmiser@gmail.com...
> I'm a newbie to database admin, but I'm not afraid to try - but this one
> has me stumped.
>
> I'm using SQL-Ledger as a front end to postgresql 7.4.2. on (mostly)
> Debian Testing, i386.
>
> My fiscal year is over and I would _like_ to prevent any changes to the
> data from last year.

A simple trigger would work well.  A rule could do it too, but rules can
have gotchas if you don't know exactly what you are doing, and triggers give
finer grained control, because you get to use procedural logic.

>
> I looked/searched in the manual, but I don't even know what to call what
> it is that I'm trying to do!
>
> "lock" has another meaning for databases. :)
>
> "Write" seems to bring up lots of user-related stuff.
>
> So, I'm hoping one of you geniuses can tell me where to look, what to
> look for, or how to do it. *smile*
>
> Thanks!
>
>               Philip, wanabe-admin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

CREATE OR REPLACE FUNCTION checkDate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS '
DECLARElimitDate DATE DEFAULT current_date-''1 year''::INTERVAL;
BEGINIF (OLD.date<=limitDate) THEN	RAISE EXCEPTION ''Cannot change record.'';END IF;
RETURN NEW;
END;
';

CREATE TRIGGER xxxx_tg1 BEFORE UPDATE OR DELETE ON xxxx FOR EACH ROW EXECUTE PROCEDURE checkDate();

Franco Bruno Borghesi wrote:
> You could write a trigger like this:
>
>
> CREATE OR REPLACE FUNCTION checkDate() RETURNS TRIGGER LANGUAGE 'plpgsql' AS '
> DECLARE
>     limitDate DATE DEFAULT current_date-''1 year''::INTERVAL;
> BEGIN
>     IF (OLD.date<=limitDate) THEN
>         RAISE EXCEPTION ''Cannot change record.'';
>     END IF;
>
>     RETURN NEW;
> END;
> ';
>
> CREATE TRIGGER xxxx_tg1 BEFORE UPDATE OR DELETE ON xxxx FOR EACH ROW
> EXECUTE PROCEDURE checkDate();
>
> This should do the job :)

Franco's trigger function should do the job just fine, but speaking from
experience you'll want to take further steps.

Take a backup of the database, restore it to another system and also
burn a copy to a CD.

If the auditors come round it's simple to explain what you've done and
demonstrate the data on the CD and backup system match. It also means
that should any changes occur to your historical data despite your
precautions you can prove that this happened.

--
   Richard Huxton
   Archonet Ltd

rumor has it that Richard wrote:

> Franco Bruno Borghesi wrote:
> > You could write a trigger like this:
> >
> >
> > CREATE OR REPLACE FUNCTION checkDate() RETURNS TRIGGER LANGUAGE
> > 'plpgsql' AS ' DECLARE
> >     limitDate DATE DEFAULT current_date-''1 year''::INTERVAL;
> > BEGIN
> >     IF (OLD.date<=limitDate) THEN
> >         RAISE EXCEPTION ''Cannot change record.'';
> >     END IF;
> >
> >     RETURN NEW;
> > END;
> > ';
> >
> > CREATE TRIGGER xxxx_tg1 BEFORE UPDATE OR DELETE ON xxxx FOR EACH ROW
> > EXECUTE PROCEDURE checkDate();
> >
> > This should do the job :)

I feel like I'm 1 meter tall and the wave on the beach are more than 3
meters high...

Thank you for the code.

It looks like it would need to be a part of any access to the database,
so I imagine I would have to figure out where to put it into the
front-end code. Is this correct?

> Franco's trigger function should do the job just fine, but speaking
> from  experience you'll want to take further steps.
>
> Take a backup of the database, restore it to another system and also
> burn a copy to a CD.
>
> If the auditors come round it's simple to explain what you've done and
>
> demonstrate the data on the CD and backup system match. It also means
> that should any changes occur to your historical data despite your
> precautions you can prove that this happened.

Ahh, that is a good idea! A database dump is a part of my daily backup.

I guess I could also make a read-only copy of the year-end as a second
database on the same system. That could make it easy to keep an eye on
the main database so I (hopefully) spot any ripples that reach back to
last year.

            Thanks for the help!   Philip