Обсуждение: statement-level trigger sample out there?
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
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 >
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 >
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 >
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 alast 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 invokinga "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with one updatefor 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
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
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"
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
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 needa 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 byinvoking a "select max(id) where foreign_key = $1" but this cost a lot of performance. I could do this easily with oneupdate 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)