pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstatement-level trigger sample out there?
9+ messages / 6 participants
[nested] [flat]
* statement-level trigger sample out there?
@ 2007-11-29 13:01 Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Christian Kindler @ 2007-11-29 13:01 UTC (permalink / raw)
To: pgsql-sql
Hi
Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values.
Thanks
Chris
--
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
@ 2007-11-29 13:48 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Re: statement-level trigger sample out there? Stephen Cook <sclists@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2007-11-29 13:48 UTC (permalink / raw)
To: Christian Kindler <christian.kindler@gmx.net>; +Cc: pgsql-sql
Hello
You cannot to access to values in statement trigger. Postgres doesn't
support it.
Regards
Pavel Stehule
On 29/11/2007, Christian Kindler <christian.kindler@gmx.net> wrote:
> Hi
>
> Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values.
>
> Thanks
> Chris
> --
> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
> Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
@ 2007-11-29 14:16 ` Stephen Cook <sclists@gmail.com>
2007-11-29 14:26 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 19:18 ` Re: statement-level trigger sample out there? Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 2 replies; 9+ messages in thread
From: Stephen Cook @ 2007-11-29 14:16 UTC (permalink / raw)
To: pgsql-sql
I am curious (coming from a MS SQL Server background, I just started
playing with PostgreSQL recently).
What type of situation would warrant a statement-level trigger that
can't access the old and new values? Without that access, isn't the
only information you get is the fact that an operation occurred on the
table? Or am I missing something?
-- Stephen
Pavel Stehule wrote:
> Hello
>
> You cannot to access to values in statement trigger. Postgres doesn't
> support it.
>
> Regards
> Pavel Stehule
>
> On 29/11/2007, Christian Kindler <christian.kindler@gmx.net> wrote:
>> Hi
>>
>> Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values.
>>
>> Thanks
>> Chris
>> --
>> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
>> Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Re: statement-level trigger sample out there? Stephen Cook <sclists@gmail.com>
@ 2007-11-29 14:26 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2007-11-29 14:26 UTC (permalink / raw)
To: Stephen Cook <sclists@gmail.com>; +Cc: pgsql-sql
On 29/11/2007, Stephen Cook <sclists@gmail.com> wrote:
> I am curious (coming from a MS SQL Server background, I just started
> playing with PostgreSQL recently).
yes, I belive. I was too. There are total different style of triggeres
in Postgres. But If I can say, programming in Postgres is much more
confortable. But It is different. You have to use ROW triggers.
>
> What type of situation would warrant a statement-level trigger that
> can't access the old and new values? Without that access, isn't the
> only information you get is the fact that an operation occurred on the
> table? Or am I missing something?
>
> -- Stephen
>
Statement triggers can be used in some kind of auditing. Maybe. I
didn't use it. And I use Postgres five years.
Pavel
>
>
> Pavel Stehule wrote:
> > Hello
> >
> > You cannot to access to values in statement trigger. Postgres doesn't
> > support it.
> >
> > Regards
> > Pavel Stehule
> >
> > On 29/11/2007, Christian Kindler <christian.kindler@gmx.net> wrote:
> >> Hi
> >>
> >> Can anyone provide me a simple example of a statement-level trigger? Problem is I do not know how to access the old.*, new.* values.
> >>
> >> Thanks
> >> Chris
> >> --
> >> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
> >> Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 1: if posting/reading through Usenet, please send an appropriate
> >> subscribe-nomail command to majordomo@postgresql.org so that your
> >> message can get through to the mailing list cleanly
> >>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Re: statement-level trigger sample out there? Stephen Cook <sclists@gmail.com>
@ 2007-11-29 19:18 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-11-29 19:17 ` Re: statement-level trigger sample out there? Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 9+ messages in thread
From: Gerardo Herzig @ 2007-11-29 19:18 UTC (permalink / raw)
To: pgsql-sql
Stephen Cook wrote:
> I am curious (coming from a MS SQL Server background, I just started
> playing with PostgreSQL recently).
>
> What type of situation would warrant a statement-level trigger that
> can't access the old and new values? Without that access, isn't the
> only information you get is the fact that an operation occurred on the
> table? Or am I missing something?
>
> -- Stephen
What about this. Suppose you have this table "planets":
planet_name | star_id|....
There is a lot of stars, right? And if a very common query involves a
"select planet_name, count(*) from planets group by star_id"....Well, if
there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per
galaxy...Thats a lot of planets to count!!! So maybe you want a helper
table who maintains such of subtotals.
Well, each time you discover a new galaxy, insert every planet in the
monster table, and *after* all the inserts, run a trigger for updating
the helper table.
Cheers.
Gerardo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Re: statement-level trigger sample out there? Stephen Cook <sclists@gmail.com>
2007-11-29 19:18 ` Re: statement-level trigger sample out there? Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2007-11-29 19:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-29 20:08 ` Re: statement-level trigger sample out there? Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2007-11-29 19:17 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig escribió:
> Stephen Cook wrote:
>
>> I am curious (coming from a MS SQL Server background, I just started
>> playing with PostgreSQL recently).
>>
>> What type of situation would warrant a statement-level trigger that can't
>> access the old and new values? Without that access, isn't the only
>> information you get is the fact that an operation occurred on the table?
>> Or am I missing something?
>
> What about this. Suppose you have this table "planets":
> planet_name | star_id|....
>
> There is a lot of stars, right? And if a very common query involves a
> "select planet_name, count(*) from planets group by star_id"....Well, if
> there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per
> galaxy...Thats a lot of planets to count!!! So maybe you want a helper
> table who maintains such of subtotals.
>
> Well, each time you discover a new galaxy, insert every planet in the
> monster table, and *after* all the inserts, run a trigger for updating the
> helper table.
Right, but it would be much more useful if you can access the NEW set
and instead of counting all the planets from scratch, you just take the
current count and add the number of planets being added.
You can do it with FOR EACH ROW triggers, but it's much worse because
you need one UPDATE on the counter for each new planet.
Perhaps the usefulness is that you store _in memory_ the number of
planets added during the FOR EACH ROW trigger, and when that's done,
call the FOR EACH STATEMENT trigger that does a single update adding the
number in memory. This would work only if the FOR EACH STATEMENT
trigger was promised to be executed after all the FOR EACH ROW triggers
were called.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"No reniegues de lo que alguna vez creíste"
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Re: statement-level trigger sample out there? Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Re: statement-level trigger sample out there? Stephen Cook <sclists@gmail.com>
2007-11-29 19:18 ` Re: statement-level trigger sample out there? Gerardo Herzig <gherzig@fmed.uba.ar>
2007-11-29 19:17 ` Re: statement-level trigger sample out there? Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2007-11-29 20:08 ` Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 0 replies; 9+ messages in thread
From: Gerardo Herzig @ 2007-11-29 20:08 UTC (permalink / raw)
To: pgsql-sql
Alvaro Herrera wrote:
>Gerardo Herzig escribió:
>
>
>>Stephen Cook wrote:
>>
>>
>>
>>>I am curious (coming from a MS SQL Server background, I just started
>>>playing with PostgreSQL recently).
>>>
>>>What type of situation would warrant a statement-level trigger that can't
>>>access the old and new values? Without that access, isn't the only
>>>information you get is the fact that an operation occurred on the table?
>>>Or am I missing something?
>>>
>>>
>>What about this. Suppose you have this table "planets":
>>planet_name | star_id|....
>>
>>There is a lot of stars, right? And if a very common query involves a
>>"select planet_name, count(*) from planets group by star_id"....Well, if
>>there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per
>>galaxy...Thats a lot of planets to count!!! So maybe you want a helper
>>table who maintains such of subtotals.
>>
>>Well, each time you discover a new galaxy, insert every planet in the
>>monster table, and *after* all the inserts, run a trigger for updating the
>>helper table.
>>
>>
>
>Right, but it would be much more useful if you can access the NEW set
>and instead of counting all the planets from scratch, you just take the
>current count and add the number of planets being added.
>
>You can do it with FOR EACH ROW triggers, but it's much worse because
>you need one UPDATE on the counter for each new planet.
>
>Perhaps the usefulness is that you store _in memory_ the number of
>planets added during the FOR EACH ROW trigger, and when that's done,
>call the FOR EACH STATEMENT trigger that does a single update adding the
>number in memory. This would work only if the FOR EACH STATEMENT
>trigger was promised to be executed after all the FOR EACH ROW triggers
>were called.
>
>
>
Shure. In that case, i will do the initial inserts into a temporary
table, do the counting, updating the helper table, and then insert into
the planets table.
I use that approach and works fine to me.
Gerardo
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
@ 2007-11-29 15:04 Christian Kindler <christian.kindler@gmx.net>
2007-11-30 09:41 ` Re: statement-level trigger sample out there? hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 9+ messages in thread
From: Christian Kindler @ 2007-11-29 15:04 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Stephen Cook <sclists@gmail.com>; pgsql-sql
SORRRY - have lost the subject :-)
Hmmm .... deamn ...
I have a realy big table (> 2'000'000 records). every second there are several inserts and updates. the thing is i need a last row reference depending on a foreing_key.
something like this:
id, foreign_key, last_id, value1, value1, date
>1<, 3, null, 12, 13, 2007-01-01
>2<, 4, null, 11, 10, 2007-01-01
4, 3, >1<, 12, 13, 2007-01-02
5, 4, >2<, 11, 10, 2007-01-02
...
of course the sequence can have holes so I have to calculate the real last row id. for now i calculate for each row by invoking a "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with one update for the whole query - if i could know which foreign_key and which date range was performed.
any ideas?
thanks chris
On Thu, November 29, 2007 3:26 pm, Pavel Stehule wrote:
> On 29/11/2007, Stephen Cook <sclists@gmail.com> wrote:
>> I am curious (coming from a MS SQL Server background, I just started
>> playing with PostgreSQL recently).
>
> yes, I belive. I was too. There are total different style of triggeres
> in Postgres. But If I can say, programming in Postgres is much more
> confortable. But It is different. You have to use ROW triggers.
>
>>
>> What type of situation would warrant a statement-level trigger that
>> can't access the old and new values? Without that access, isn't the
>> only information you get is the fact that an operation occurred on the
>> table? Or am I missing something?
>>
>> -- Stephen
>>
>
> Statement triggers can be used in some kind of auditing. Maybe. I
> didn't use it. And I use Postgres five years.
>
> Pavel
>>
>>
>> Pavel Stehule wrote:
>> > Hello
>> >
>> > You cannot to access to values in statement trigger. Postgres doesn't
>> > support it.
>> >
>> > Regards
>> > Pavel Stehule
>> >
>> > On 29/11/2007, Christian Kindler <christian.kindler@gmx.net> wrote:
>> >> Hi
>> >>
>> >> Can anyone provide me a simple example of a statement-level trigger?
>> Problem is I do not know how to access the old.*, new.* values.
>> >>
>> >> Thanks
>> >> Chris
>> >> --
>> >> GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
>> >> Alle Infos und kostenlose Anmeldung:
>> http://www.gmx.net/de/go/freemail
>> >>
>> >> ---------------------------(end of
>> broadcast)---------------------------
>> >> TIP 1: if posting/reading through Usenet, please send an appropriate
>> >> subscribe-nomail command to majordomo@postgresql.org so that
>> your
>> >> message can get through to the mailing list cleanly
>> >>
>> >
>> > ---------------------------(end of
>> broadcast)---------------------------
>> > TIP 1: if posting/reading through Usenet, please send an appropriate
>> > subscribe-nomail command to majordomo@postgresql.org so that
>> your
>> > message can get through to the mailing list cleanly
>> >
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
cu
Chris
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
--
cu
Chris
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: statement-level trigger sample out there?
2007-11-29 15:04 Re: statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
@ 2007-11-30 09:41 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 9+ messages in thread
From: hubert depesz lubaczewski @ 2007-11-30 09:41 UTC (permalink / raw)
To: Christian Kindler <christian.kindler@gmx.net>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Stephen Cook <sclists@gmail.com>; pgsql-sql
On Thu, Nov 29, 2007 at 04:04:54PM +0100, Christian Kindler wrote:
> I have a realy big table (> 2'000'000 records). every second there are several inserts and updates. the thing is i need a last row reference depending on a foreing_key.
>
> something like this:
>
> id, foreign_key, last_id, value1, value1, date
> >1<, 3, null, 12, 13, 2007-01-01
> >2<, 4, null, 11, 10, 2007-01-01
> 4, 3, >1<, 12, 13, 2007-01-02
> 5, 4, >2<, 11, 10, 2007-01-02
> ...
>
> of course the sequence can have holes so I have to calculate the real last row id. for now i calculate for each row by invoking a "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with one update for the whole query - if i could know which foreign_key and which date range was performed.
create index q on table (foreing_key, id);
and then:
select max(id) from table where foreing_key = ?;
should be very fast.
if it is not:
select id from table where foreing_key = ? order by foreing_key desc, id desc limit 1;
will be fast.
of course - remember about vacuum/analyze.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-11-30 09:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-29 13:01 statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-29 13:48 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 14:16 ` Stephen Cook <sclists@gmail.com>
2007-11-29 14:26 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-11-29 19:18 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-11-29 19:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-29 20:08 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-11-29 15:04 Re: statement-level trigger sample out there? Christian Kindler <christian.kindler@gmx.net>
2007-11-30 09:41 ` hubert depesz lubaczewski <depesz@depesz.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