New answers tagged locking
You could stagger survey_task yourself by calling the following functions IS_FREE_LOCK() GET_LOCK() RELEASE_LOCK() This supports the paradigm known as cooperative or advisory locking. Use in the survey_task as follows: DELIMITER $$ CREATE PROCEDURE survey_task() BEGIN WHILE IS_FREE_LOCK('survey_task_lock') = 0 DO SELECT SLEEP(0.1) INTO @x; END ...
Yes, it is possible and this may or may not be desirable. The database theory has a concept of isolation, which is about transaction visibility to other processes. MySql's documentation about set transaction has a discussion about product specific syntax.
To use an analogy with banks, pessimistic locking is like having a guard at the bank door who checks your account number when you try to enter; if someone else (a spouse, or a merchant to whom you wrote a check) is already in the bank accessing your account, then you cannot enter until that other person finishes her transaction and leaves. Optimistic ...
Is the autovac "to prevent wraparound" (as reported in pg_stat_activity)? If so, PostgreSQL considers that an emergency and refuses to yield the lock. Which is rather unfortunate in this case, as once you drop the table the situation will be resolved. But it doesn't know why you want the lock.
If no delete or update happened, autovacuum shouldn't process the table, per autovacuum_analyze_threshold. See Automatic Vacuuming for the relevant configuration parameters. You may set log_autovacuum_min_duration to zero to help figure out what's being autovacuumed. In any case, it is possible to completely disable autovacuum for a particular table, with: ...
If You use Linux you should list processes with ps aux and find Autovacuum process. Then just kill it(autovacuum should be working as another process if it's turned on).
Top 50 recent answers are included