pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlocks and triggers. give me an advice please
3+ messages / 2 participants
[nested] [flat]
* locks and triggers. give me an advice please
@ 2004-07-20 05:36 sad <sad@bankir.ru>
2004-07-20 18:30 ` Re: locks and triggers. give me an advice please Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 3+ messages in thread
From: sad @ 2004-07-20 05:36 UTC (permalink / raw)
To: pgsql-sql
Good day.
often, I am turning triggers off and on to perform a mass operation on a
table, and i am interested how should i care of another user operations.
the scene is:
table t1 with user defined triggers
and many tables reference t1, (so FK triggers defined on t1)
the operation i want to perform on t1 makes a great load to a server
and have no use in triggers at all.
the best way to perform this operation is to delete all records, modify, and
insert them back without changing any adjuscent table.
(this way takes a few seconds.)
so i turn off triggers on t1 completely (updating pg_class.reltriggers)
operate
and turn on triggers on t1.
it works fine.
the question is:
what should i do to prevent other users of data modification on the t1 and the
adjuscent tables while triggers is off ?
thnx.
P.S.
...what about TEXT to REGCLASS casting ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: locks and triggers. give me an advice please
2004-07-20 05:36 locks and triggers. give me an advice please sad <sad@bankir.ru>
@ 2004-07-20 18:30 ` Steve Crawford <scrawford@pinpointresearch.com>
2004-07-21 05:01 ` Re: locks and triggers. give me an advice please sad <sad@bankir.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Crawford @ 2004-07-20 18:30 UTC (permalink / raw)
To: sad <sad@bankir.ru>; pgsql-sql
> often, I am turning triggers off and on to perform a mass operation
> on a table, and i am interested how should i care of another user
> operations.
>
> the scene is:
> table t1 with user defined triggers
> and many tables reference t1, (so FK triggers defined on t1)
>
> the operation i want to perform on t1 makes a great load to a
> server and have no use in triggers at all.
> the best way to perform this operation is to delete all records,
> modify, and insert them back without changing any adjuscent table.
> (this way takes a few seconds.)
> so i turn off triggers on t1 completely (updating
> pg_class.reltriggers) operate
> and turn on triggers on t1.
>
> it works fine.
>
> the question is:
>
> what should i do to prevent other users of data modification on the
> t1 and the adjuscent tables while triggers is off ?
If I understand your question correctly you should use a transaction
and lock the table;
begin transaction;
lock t1 in access exclusive mode;
Turn off triggers and do your updates.
(Note, "truncate t1" is faster than "delete from t1" followed by a
"vacuum full" and you might consider running "reindex table t1" after
your mass update or if appropriate drop your indexes, load the data,
then recreate them.)
Re-establish triggers.
commit; --end of transaction unlocks the table
Cheers,
Steve
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: locks and triggers. give me an advice please
2004-07-20 05:36 locks and triggers. give me an advice please sad <sad@bankir.ru>
2004-07-20 18:30 ` Re: locks and triggers. give me an advice please Steve Crawford <scrawford@pinpointresearch.com>
@ 2004-07-21 05:01 ` sad <sad@bankir.ru>
0 siblings, 0 replies; 3+ messages in thread
From: sad @ 2004-07-21 05:01 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; pgsql-sql
thnx.
i try to sound the idea to ensure myself that you are right.
> begin transaction;
> lock t1 in access exclusive mode;
>
> Turn off triggers and do your updates.
> (Note, "truncate t1" is faster than "delete from t1" followed by a
> "vacuum full" and you might consider running "reindex table t1" after
> your mass update or if appropriate drop your indexes, load the data,
> then recreate them.)
>
> Re-establish triggers.
>
> commit; --end of transaction unlocks the table
in case another user inserts a record into an adjuscent table with the value
of reference field NOT IN t1.
the constraint causes reading of t1 to look up FK value IN t1.
so my EXCLUSIVE lock prevents even reading and this user operation will be
queued.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-07-21 05:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-20 05:36 locks and triggers. give me an advice please sad <sad@bankir.ru>
2004-07-20 18:30 ` Steve Crawford <scrawford@pinpointresearch.com>
2004-07-21 05:01 ` sad <sad@bankir.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