Today was the first day of PGCon 2007. I sat in on the Performance tuturial. The room was overflowing, so we moved to a larger room. 58 people for a tutorial is pretty impressive. The connection to FreshPorts? I came up with an idea for solving the problem. Tonight gave me the opportunity.
I invoked logging on PostgreSQL on my development server. I did this by adding the following line to my /usr/local/pgsql/data/postgresql.conf:
log_statement = 'all'
But… I already had several other lines which ensure logging worked. How to get logging working is outside the scope of this post. But here are the customized lines I have appended to this file over the years:
listen_addresses = '*' # what IP address(es) to listen on; max_fsm_pages = 140000 # min max_fsm_relations*16, 6 bytes each #log_duration = on #log_statement = all syslog_facility = 'LOCAL0' syslog_ident = 'postgres' client_min_messages = error log_min_messages = error redirect_stderr = on log_destination = 'stderr,syslog' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 86400 stats_command_string = on log_line_prefix = '%m %d %p %u %l %x ' #ssl = yes #log_statement = 'all' # none, ddl, mod, all
Then I reran the commit. The deadlock error message for this run was:
2007-05-21 20:47:07.760 ERROR: deadlock detected
2007-05-21 20:47:07.760 DETAIL: Process 60571 waits
for ShareLock on transaction 15154008; blocked by process 60702.
Process 60702 waits for ShareLock on transaction 15154004; blocked by process 60571.
I searched the log files for 60571 and 60702. I found them easily:
2007-05-21 20:45:00.610 LOG: statement: begin 2007-05-21 20:45:00.611 LOG: statement: DELETE FROM ports_moved
BINGO!
Yes, that would make sense…. I “fork” off the processing of the MOVED file. In this case, the processing of the original commit lasted so long that cronjob that processes the MOVED file started up. This is a simplified explanation, but that is what happened.
I see an immediate and relatively easily solution: at present, as soon as the MOVED file is seen in a commit, a flag is set within the file system. I suspect that delaying the setting of this flag until the commit has been processed will do what is required. However, the BEST solution is to process MOVED inline, immediately after the commit is processed. This ensure that database updates occur in serial, not parallel.
