pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate in trigger
4+ messages / 2 participants
[nested] [flat]
* Update in trigger
@ 2002-04-03 03:38 Iklódi Lajos <il@mithrandir.hu>
2002-04-03 16:44 ` Re: Update in trigger Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Iklódi Lajos @ 2002-04-03 03:38 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a problem with trigger in 7.1.3.
I have a table with after insert trigger, which is making updates in
another table.
I recognized that the more records I have in first table the slower is
my insert.
If my trigger is doing something else, for example inserts in another
table, it becomes * 20 times * quicker, and the speed of my insert
doesn't depend on tablesize.
Don't I have to make updates in trigger?
Thanks in advance for any help.
Lajos
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update in trigger
2002-04-03 03:38 Update in trigger Iklódi Lajos <il@mithrandir.hu>
@ 2002-04-03 16:44 ` Jan Wieck <janwieck@yahoo.com>
2002-04-04 03:41 ` Re: Update in trigger Iklódi Lajos <il@mithrandir.hu>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Wieck @ 2002-04-03 16:44 UTC (permalink / raw)
To: [Ikl_di] Lajos <il@mithrandir.hu>; +Cc: pgsql-sql
[Ikl_di] Lajos wrote:
[Charset iso-8859-2 unsupported, filtering to ASCII...]
> Hi,
> I have a problem with trigger in 7.1.3.
> I have a table with after insert trigger, which is making updates in
> another table.
> I recognized that the more records I have in first table the slower is
> my insert.
> If my trigger is doing something else, for example inserts in another
> table, it becomes * 20 times * quicker, and the speed of my insert
> doesn't depend on tablesize.
> Don't I have to make updates in trigger?
Are there appropriate indexes on the table updated in the
trigger and is the database "VACUUM ANALYZE"ed so the indexes
get used?
Even if it is an AFTER INSERT trigger, your INSERT has to
wait until the trigger finishes, because it still has a right
to abort the transaction.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update in trigger
2002-04-03 03:38 Update in trigger Iklódi Lajos <il@mithrandir.hu>
2002-04-03 16:44 ` Re: Update in trigger Jan Wieck <janwieck@yahoo.com>
@ 2002-04-04 03:41 ` Iklódi Lajos <il@mithrandir.hu>
2002-04-04 15:05 ` Re: Update in trigger Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Iklódi Lajos @ 2002-04-04 03:41 UTC (permalink / raw)
To: Jan Wieck <janwieck@yahoo.com>; +Cc: pgsql-sql; William Meloney <bmeloney@mindspring.com>
Hi,
Jan Wieck írta:
> Are there appropriate indexes on the table updated in the
> trigger and is the database "VACUUM ANALYZE"ed so the indexes
> get used?
There is a primary key.
VACUUM ANALYZE is really helpful, but only for a few minutes (3000 inserted
records).
William Meloney írta:
> Please forward a copy of the trigger. This is exactly the same thing I
> am trying to do but I have not had any success.
Here is the whole structure:
CREATE TABLE nyadat (
nykod serial primary key,
nymekod int4 not null references mekonf,
nyatip int4 not null references nyatip,
nymeido timestamp,
nytarido timestamp default now(),
nyhossz int4,
nyadat bytea
) ;
CREATE TABLE szm (
kod serial primary key,
konfx int4,
csatx int4,
dparx int4,
nparx int4,
nyugtax int4,
parancsx int4,
esemenyx int4,
kalibrx int4,
nyx int4,
feldx int4,
kparx int4
) ;
create function nyx_fnc() returns opaque as '
begin
update szm set nyx = new.nykod where kod=1 ;
return new ;
end ;
' language 'plpgsql' ;
create trigger ny_x_trig after insert
on nyadat for each row execute procedure nyx_fnc() ;
Thanks for any help.
Lajos
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Update in trigger
2002-04-03 03:38 Update in trigger Iklódi Lajos <il@mithrandir.hu>
2002-04-03 16:44 ` Re: Update in trigger Jan Wieck <janwieck@yahoo.com>
2002-04-04 03:41 ` Re: Update in trigger Iklódi Lajos <il@mithrandir.hu>
@ 2002-04-04 15:05 ` Jan Wieck <janwieck@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2002-04-04 15:05 UTC (permalink / raw)
To: [Ikl_di] Lajos <il@mithrandir.hu>; +Cc: Jan Wieck <janwieck@yahoo.com>; pgsql-sql; William Meloney <bmeloney@mindspring.com>
[Ikl_di] Lajos wrote:
[Charset iso-8859-2 unsupported, filtering to ASCII...]
> Hi,
>
> Jan Wieck _rta:
>
> > Are there appropriate indexes on the table updated in the
> > trigger and is the database "VACUUM ANALYZE"ed so the indexes
> > get used?
>
> There is a primary key.
> VACUUM ANALYZE is really helpful, but only for a few minutes (3000 inserted
> records).
>
>
> William Meloney _rta:
>
> > Please forward a copy of the trigger. This is exactly the same thing I
> > am trying to do but I have not had any success.
>
> Here is the whole structure:
>
> [...]
That's a well known problem with PostgreSQL's non-overwriting
storage management system. After your 3000 INSERTs, you also
have UPDATEed szm 3000 times, leaving 3000 dead rows and 3000
dead index entries in it. Thus, another UPDATE looking for
kod=1 will find 3000 index entries and has to check 3000 data
rows only to find that they have to be ignored.
The only way I see at the time beeing is to vacuum tables
that have very few rows with a high update frequency "very
often". No need to vacuum the rest of the database at the
same rate, just these special tables.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-04-04 15:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-03 03:38 Update in trigger Iklódi Lajos <il@mithrandir.hu>
2002-04-03 16:44 ` Jan Wieck <janwieck@yahoo.com>
2002-04-04 03:41 ` Iklódi Lajos <il@mithrandir.hu>
2002-04-04 15:05 ` Jan Wieck <janwieck@yahoo.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