[SQL] help: triggers
hello, I'm trying to update a columm on a table with a trigger but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC 2.96). when I update a row the trigger does nothing. what can I do? thanks in advance tony here is my trigger: CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''My Trigger is updating the table''; IF OLD.status=''Activo'' THEN NEW.tempo := 10; NEW.actual := now(); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER update_online BEFORE UPDATE ON iobjects FOR EACH ROW EXECUTE PROCEDURE update_my_objekt(); ## ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] help: triggers
Wei Weng wrote:
It would be better if you could provide the source of that trigger and involved
table schemas?
CREATE SEQUENCE "iobjects_id_seq" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;
GRANT ALL on "iobjects_id_seq" to "jantos";
CREATE TABLE "iobjects" (
"id" integer DEFAULT nextval('"iobjects_id_seq"'::text) NOT NULL,
"status" character varying(10) DEFAULT 'Activo' NOT NULL,
"local" character varying(80) NOT NULL,
"fig_1" character varying(80),
"fig_2" character varying(80),
"fig_3" character varying(80),
"introduzido" timestamp with time zone DEFAULT now() NOT NULL,
"actual" timestamp with time zone DEFAULT now() NOT NULL,
"tempo" integer DEFAULT 0 NOT NULL,
"deleted" character(1) DEFAULT '0',
Constraint "pk_iobjects" Primary Key ("id")
);
GRANT ALL on "iobjects" to "jantos";
Wei
- Original Message -
From: "Tony Simbine" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 29, 2003 8:29 AM
Subject: [SQL] help: triggers
hello,
I'm trying to update a columm on a table with a trigger
but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC
2.96).
when I update a row the trigger does nothing.
what can I do?
thanks in advance
tony
here is my trigger:
CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS '
BEGIN
RAISE NOTICE ''My Trigger is updating the table'';
IF OLD.status=''Activo'' THEN
NEW.tempo := 10;
NEW.actual := now();
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER update_online BEFORE UPDATE ON iobjects
FOR EACH ROW EXECUTE PROCEDURE update_my_objekt();
##
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] help
hello,
I've a query which needs too many time ca. 12-15 sec.
how can i get a better perfomance?
my table have less than 2300 rows.
thanks in advance
tony
explain select o.id from ioobeject o,dist_vertron v where
macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and
(o.status='activo') order by o.ort;
QUERY
PLAN
-
--
Sort (cost=790.49..791.63 rows=457 width=66)
Sort Key: o.ort
-> Hash Join (cost=228.88..770.31 rows=457 width=66)
Hash Cond: ("outer".id = "inner".id)
-> Append (cost=0.00..502.35 rows=3337 width=4)
-> Seq Scan on dist_vertron v (cost=0.00..0.00 rows=1
width=4)
-> Seq Scan on disposicao v (cost=0.00..136.87
rows=987 width=4)
-> Seq Scan on oponente v (cost=0.00..0.00 rows=1 width=4)
-> Seq Scan on novinho v (cost=0.00..5.14 rows=14 width=4)
-> Seq Scan on colagem_livre v (cost=0.00..194.69
rows=1369 width=4)
-> Seq Scan on jardim_contribuicao v
(cost=0.00..149.08 rows=808 width=4)
-> Seq Scan on jardim_comardia v (cost=0.00..16.57
rows=157 width=4)
-> Hash (cost=228.81..228.81 rows=27 width=62)
-> Append (cost=0.00..228.81 rows=27 width=62)
-> Seq Scan on ioobeject o (cost=0.00..0.00
rows=1 width=62)
Filter: ((macro_lid = 1123) AND (deleted <>
'1'::bpchar) AND (status = 'activo'::character varying))
-> Index Scan using iwohnung_macro_lid_index on
iwohnung o (cost=0.00..28.71 rows=3 width=17)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
izoologicowohnung_macro_lid_index on izoologicowohnung o
(cost=0.00..14.70 rows=1 width=19)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using icolagem_macro_lid_index on
icasamento o (cost=0.00..21.06 rows=2 width=18)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
iwohn_geschaefts_colagem_macro_lid__index on iwohn_geschaefts_casamento
o (cost=0.00..7.80 row
s=1 width=16)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
ialinhadocolagem_macro_lid_index on ialinhadocasamento o
(cost=0.00..8.30 rows=1 width=18)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
idobro_colagem_macro_lid_index on idobro_casamento o (cost=0.00..6.08
rows=1 width=17)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using ibauernhof_mediador_index on
ibauernhof o (cost=0.00..8.53 rows=1 width=18)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
imehrfamcolagem_mediador_index on imehrfamcasamento o (cost=0.00..11.49
rows=1 width=17)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
izoologicocolagem_macro_lid_index on izoologicocasamento o
(cost=0.00..8.03 rows=1 width=17)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using
igartenbungalow_macro_lid_index on igartenbungalow o (cost=0.00..9.20
rows=1 width=19)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using iescritor_mediador_index on
iescritor o (cost=0.00..7.16 rows=1 width=17)
Index Cond: (macro_lid = 1123)
Filter: ((deleted <> '1'::bpchar) AND
(status = 'activo'::character varying))
-> Index Scan using ivilla_macro_lid_index on
Re: [SQL] help
Josh, thanks for your help. Josh Berkus wrote: Tony, I've a query which needs too many time ca. 12-15 sec. how can i get a better perfomance? First of all, please take this to the PGSQL-PERFORMANCE list. Second, see this web page: http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines i read some tips on the above pages and my database and all my queries run very well. i've optimized the queriey from where ... IN (select ..) to EXISTS and I've done vacumdb full. my requests last between 0.009 to 0.2 sec compared with 12-15 sec. i'm very happy. thanks very much. tony explain select o.id from ioobeject o,dist_vertron v where macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and (o.status='activo') order by o.ort; Third, from your explain, ioobject and dist_vertron are obviously somewhat complex views. We need those view definitions, possibly plus schema for the underlying tables (including indexes), or we can't help you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
