?
Solved

lastmod and create timestamps for records

Posted on 2005-03-29
6
Medium Priority
?
328 Views
Last Modified: 2008-03-10
For all tables in my database I have columns create_ts and last_mod_ts, which represent the create and last modification timestamps for each record. Is there a way to have PostgreSQL set these values on inserts and updates, rather than have the application pass these along with each query?
0
Comment
Question by:cxm928
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 4

Expert Comment

by:Robson
ID: 13654303
Hi,
I'm not testing it but writting 'online', hope it helps

create function set_create_ts returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';

create function set_last_mod_ts returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';

And for each TABLENAME in your database:

create trigger set_create_ts before insert on TABLENAME for each row execute procedure set_create_ts();
(altertative: create field create_ts with default value now() -- but whill work only if you skip create_ts in INSERT command).

create trigger set_create_ts before update on TABLENAME for each row execute procedure set_last_mod_ts();
0
 
LVL 4

Expert Comment

by:Robson
ID: 13654317
With some effort you could write a function that creates triggers and run it for each table in your database that contains theese fields -- but it's worth only in you have a great lot of tables :)
0
 
LVL 4

Accepted Solution

by:
Robson earned 1000 total points
ID: 13654346
Already noticed one error:

create function set_create_ts()
returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';

(missing '()', tested in 8.0 version)

Regards,
Robson.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 4

Expert Comment

by:Robson
ID: 13654384
Note that now() returns transaction start time, not current time, and transactions may be very long! If you want to use this fields for data replication use timenow() instead of now() (I didn't and regretted ;).

Robson.
0
 

Author Comment

by:cxm928
ID: 13654633
Robson,
Your thoughts and advice on using triggers is very helpful. I am surprised that this is not something that is supported by the database itself, rather than having to setup triggers. But, the solution you suggest seems worth trying. I'm not sure of the performance impact, but the desired behavior is achieved.

Thanks...
0
 
LVL 22

Expert Comment

by:earth man2
ID: 13656118
You only really need update trigger.  You can set default value to be now()
eg
create table_1 ( create_ts timestamp default now() );
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question