pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger vs Rule
4+ messages / 2 participants
[nested] [flat]
* Trigger vs Rule
@ 2006-04-02 08:31 Ключников А.С. <alexs@analytic.mv.ru>
2006-04-02 21:08 ` Re: Trigger vs Rule Niklas Johansson <spot@tele2.se>
0 siblings, 1 reply; 4+ messages in thread
From: Ключников А.С. @ 2006-04-02 08:31 UTC (permalink / raw)
To: pgsql-performance
Hi all.
There are two tables:
create table device_types (
id int,
name varchar
);
about 1000 rows
create table devices (
id int,
type int REFERENCES device_types(id),
name varchar,
data float
);
about 200000 rows
And about 1000 functions:
create function device_type1(int) returns ..
create function device_type2(int) returns ..
...
create function device_type1000(int) returns ..
What is faster?
One trigger with 1000 ELSE IF
if old.type=1 then
select device_type1(old.id);
else if old.type=2 then
select device_type2(old.id);
...
else if old.type=1000 then
select device_type1000(old.id);
end if;
Or 1000 rules
create rule device_type1 AS ON update to devices
where old.type=1
DO select device_type1(old.id);
create rule device_type2 AS ON update to devices
where old.type=2
DO select device_type2(old.id);
...
create rule device_type1000 AS ON update to devices
where old.type=1000
DO select device_type1000(old.id);
thx.
--
С уважением,
Ключников А.С.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger vs Rule
2006-04-02 08:31 Trigger vs Rule Ключников А.С. <alexs@analytic.mv.ru>
@ 2006-04-02 21:08 ` Niklas Johansson <spot@tele2.se>
2006-04-03 09:04 ` Re: Trigger vs Rule Niklas Johansson <spot@tele2.se>
0 siblings, 1 reply; 4+ messages in thread
From: Niklas Johansson @ 2006-04-02 21:08 UTC (permalink / raw)
To: Ключников А.С. <alexs@analytic.mv.ru>; +Cc: pgsql-performance
On 2 apr 2006, at 10.31, Ключников А.С. wrote:
> What is faster?
> One trigger with 1000 ELSE IF
> Or 1000 rules
Faster to write and easier to maintain would be to write a trigger
function in pl/pgsql which executes the right function dynamically:
CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
EXECUTE "SELECT device_type" || OLD.type || "(OLD.id)";
$$ LANGUAGE plpgsql;
Best would probably be to refactor your device_typeN() functions into
one, that would take N as an argument.
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger vs Rule
2006-04-02 08:31 Trigger vs Rule Ключников А.С. <alexs@analytic.mv.ru>
2006-04-02 21:08 ` Re: Trigger vs Rule Niklas Johansson <spot@tele2.se>
@ 2006-04-03 09:04 ` Niklas Johansson <spot@tele2.se>
2006-04-03 09:17 ` Re: Trigger vs Rule Ключников А.С. <alexs@analytic.mv.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Niklas Johansson @ 2006-04-03 09:04 UTC (permalink / raw)
To: "Ключников А.С." <alexs@analytic.mv.ru>; +Cc: pgsql-performance
On 2 apr 2006, at 23.08, Niklas Johansson wrote:
> CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
> EXECUTE "SELECT device_type" || OLD.type || "(OLD.id)";
> $$ LANGUAGE plpgsql;
Sorry, I was bitten by the bedbug there: a plpgsql function needs a
little more than that to be functional :)
CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
BEGIN
EXECUTE 'SELECT device_type' || OLD.type || '(OLD.id)';
RETURN NEW/OLD/NULL; -- Depending on your application.
END;
$$ LANGUAGE plpgsql;
But really, you should consider reworking your schema structure.
Having a thousand functions doing almost the same thing is neither
efficient, nor maintainable.
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trigger vs Rule
2006-04-02 08:31 Trigger vs Rule Ключников А.С. <alexs@analytic.mv.ru>
2006-04-02 21:08 ` Re: Trigger vs Rule Niklas Johansson <spot@tele2.se>
2006-04-03 09:04 ` Re: Trigger vs Rule Niklas Johansson <spot@tele2.se>
@ 2006-04-03 09:17 ` Ключников А.С. <alexs@analytic.mv.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Ключников А.С. @ 2006-04-03 09:17 UTC (permalink / raw)
To: pgsql-performance
* Niklas Johansson <spot@tele2.se> [2006-04-03 11:04:25 +0200]:
>
> On 2 apr 2006, at 23.08, Niklas Johansson wrote:
>
> >CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
> > EXECUTE "SELECT device_type" || OLD.type || "(OLD.id)";
> >$$ LANGUAGE plpgsql;
>
>
> Sorry, I was bitten by the bedbug there: a plpgsql function needs a
> little more than that to be functional :)
>
> CREATE OR REPLACE FUNCTION exec_device_type() RETURNS trigger AS $$
> BEGIN
> EXECUTE 'SELECT device_type' || OLD.type || '(OLD.id)';
> RETURN NEW/OLD/NULL; -- Depending on your application.
> END;
> $$ LANGUAGE plpgsql;
>
> But really, you should consider reworking your schema structure.
> Having a thousand functions doing almost the same thing is neither
> efficient, nor maintainable.
Things are very diferent.
For many types functions not needed, jast update.
I.e. This is a way One trigger with ~1000 else if.
Here was a diametral opinion.
>
>
>
> Sincerely,
>
> Niklas Johansson
>
>
>
>
--
С уважением,
Ключников А.С.
Ведущий инженер ПРП "Аналитприбор"
432030 г.Ульяновск, а/я 3117
тел./факс +7 (8422) 43-44-78
mailto: alexs@analytic.mv.ru
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-03 09:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-02 08:31 Trigger vs Rule Ключников А.С. <alexs@analytic.mv.ru>
2006-04-02 21:08 ` Niklas Johansson <spot@tele2.se>
2006-04-03 09:04 ` Niklas Johansson <spot@tele2.se>
2006-04-03 09:17 ` Ключников А.С. <alexs@analytic.mv.ru>
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