pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRecord Log Trigger
4+ messages / 3 participants
[nested] [flat]
* Record Log Trigger
@ 2005-05-02 19:10 lucas@presserv.org
0 siblings, 0 replies; 4+ messages in thread
From: lucas@presserv.org @ 2005-05-02 19:10 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I am building a database in postgresql and I made a function that returns the
system time and the current user... like this:
CREATE OR REPLACE FUNCTION generate_idx() returns text as
$$
select to_char(now(),'YYYYMMDDHHMISSUSTZ')||CURRENT_USER;
$$ language 'SQL';
CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS
$$
BEGIN
NEW.idxm = generate_idx();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
And my all tables have the "idxm" field, its something like a log for the
record, to know Who and When the record have changed. I.e:
CREATE TABLE products(
id serial primary key,
description varchar(50),
...
idxm varchar(100)
);
CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW
EXECUTE PROCEDURE TG_idxm();
Okay, it runs fine... but my question is:
Is it right??? In the future (when the database will be bigger with many of
millions records) this functions for each table will depreceate my database
performance???
Is there any other way to build it???
Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Record Log Trigger
@ 2005-05-02 19:27 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 1 reply; 4+ messages in thread
From: CHRIS HOOVER @ 2005-05-02 19:27 UTC (permalink / raw)
To: lucas@presserv.org; +Cc: pgsql-sql
One change you might want to look at is not using the now() function.
According to the docs, the now() function always returns the start of the
transaction time. So, if your code is using transaction blocks, the time may
not be what you are expecting.
This is what I had do to in my trigger to get the current clock time:
to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US YYYY\')
HTH,
Chris
------------------( Forwarded letter 1 follows )---------------------
Date: Mon, 02 May 2005 16:10:46 -0300
To: pgsql-sql@postgresql.org.comp
From: lucas@presserv.org.comp
Sender: pgsql-sql-owner+m21335@postgresql.org.comp
Subject: [SQL] Record Log Trigger
Hi all,
I am building a database in postgresql and I made a function that returns the
system time and the current user... like this:
CREATE OR REPLACE FUNCTION generate_idx() returns text as
$$
select to_char(now(),'YYYYMMDDHHMISSUSTZ')||CURRENT_USER;
$$ language 'SQL';
CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS
$$
BEGIN
NEW.idxm = generate_idx();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
And my all tables have the "idxm" field, its something like a log for the
record, to know Who and When the record have changed. I.e:
CREATE TABLE products(
id serial primary key,
description varchar(50),
...
idxm varchar(100)
);
CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW
EXECUTE PROCEDURE TG_idxm();
Okay, it runs fine... but my question is:
Is it right??? In the future (when the database will be bigger with many of
millions records) this functions for each table will depreceate my database
performance???
Is there any other way to build it???
Thank you
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Record Log Trigger
@ 2005-05-03 12:14 lucas@presserv.org
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 4+ messages in thread
From: lucas@presserv.org @ 2005-05-03 12:14 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
Well...
Right, I will use to_timestamp() function instead of now() function.
But, what is the performance for those Triggers??? Considering that all tables
will have this Trigger and will check for each update or insert.
Thanks
Quoting CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>:
> One change you might want to look at is not using the now() function.
> According to the docs, the now() function always returns the start of the
> transaction time. So, if your code is using transaction blocks, the time may
> not be what you are expecting.
>
> This is what I had do to in my trigger to get the current clock time:
> to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US YYYY\')
>
>
> HTH,
>
> Chris
> ------------------( Forwarded letter 1 follows )---------------------
> Date: Mon, 02 May 2005 16:10:46 -0300
> To: pgsql-sql@postgresql.org.comp
> From: lucas@presserv.org.comp
> Sender: pgsql-sql-owner+m21335@postgresql.org.comp
> Subject: [SQL] Record Log Trigger
>
> Hi all,
> I am building a database in postgresql and I made a function that returns the
> system time and the current user... like this:
>
> CREATE OR REPLACE FUNCTION generate_idx() returns text as
> $$
> select to_char(now(),'YYYYMMDDHHMISSUSTZ')||CURRENT_USER;
> $$ language 'SQL';
>
> CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS
> $$
> BEGIN
> NEW.idxm = generate_idx();
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> And my all tables have the "idxm" field, its something like a log for the
> record, to know Who and When the record have changed. I.e:
>
> CREATE TABLE products(
> id serial primary key,
> description varchar(50),
> ...
> idxm varchar(100)
> );
> CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products
> FOR EACH ROW
> EXECUTE PROCEDURE TG_idxm();
>
> Okay, it runs fine... but my question is:
> Is it right??? In the future (when the database will be bigger with many of
> millions records) this functions for each table will depreceate my database
> performance???
> Is there any other way to build it???
>
> Thank you
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Record Log Trigger
@ 2005-05-04 04:44 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-05-04 04:44 UTC (permalink / raw)
To: lucas@presserv.org; CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
Hi,
I do not think there is a performance issue due trigger on a larger database. For improving performance, it is better to write triggers for Insert, update, delete separately rather than combining them on a single trigger.
Periodic VACCUM and RE-INDEX the table will improve the performance.
Regards,
R.Muralidharan
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of lucas@presserv.org
Sent: Tuesday, May 03, 2005 5:45 PM
To: CHRIS HOOVER
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Record Log Trigger
Well...
Right, I will use to_timestamp() function instead of now() function.
But, what is the performance for those Triggers??? Considering that all tables
will have this Trigger and will check for each update or insert.
Thanks
Quoting CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>:
> One change you might want to look at is not using the now() function.
> According to the docs, the now() function always returns the start of the
> transaction time. So, if your code is using transaction blocks, the time may
> not be what you are expecting.
>
> This is what I had do to in my trigger to get the current clock time:
> to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US YYYY\')
>
>
> HTH,
>
> Chris
> ------------------( Forwarded letter 1 follows )---------------------
> Date: Mon, 02 May 2005 16:10:46 -0300
> To: pgsql-sql@postgresql.org.comp
> From: lucas@presserv.org.comp
> Sender: pgsql-sql-owner+m21335@postgresql.org.comp
> Subject: [SQL] Record Log Trigger
>
> Hi all,
> I am building a database in postgresql and I made a function that returns the
> system time and the current user... like this:
>
> CREATE OR REPLACE FUNCTION generate_idx() returns text as
> $$
> select to_char(now(),'YYYYMMDDHHMISSUSTZ')||CURRENT_USER;
> $$ language 'SQL';
>
> CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS
> $$
> BEGIN
> NEW.idxm = generate_idx();
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> And my all tables have the "idxm" field, its something like a log for the
> record, to know Who and When the record have changed. I.e:
>
> CREATE TABLE products(
> id serial primary key,
> description varchar(50),
> ...
> idxm varchar(100)
> );
> CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products
> FOR EACH ROW
> EXECUTE PROCEDURE TG_idxm();
>
> Okay, it runs fine... but my question is:
> Is it right??? In the future (when the database will be bigger with many of
> millions records) this functions for each table will depreceate my database
> performance???
> Is there any other way to build it???
>
> Thank you
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-04 04:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-02 19:10 Record Log Trigger lucas@presserv.org
2005-05-02 19:27 Re: Record Log Trigger CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2005-05-03 12:14 ` lucas@presserv.org
2005-05-04 04:44 Re: Record Log Trigger Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
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