From 3bde4ccddbb9473fff56752d7100332d48affa1a Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Mon, 4 Aug 2008 01:24:51 +0200 Subject: [PATCH 1/1] Fix another deadlock-when-many-people-upload bug, like the one we fixed in r14 (see that revision for full explanation). --- perl/Sesse/pr0n/Index.pm | 4 ++-- perl/Sesse/pr0n/Listing.pm | 2 +- perl/Sesse/pr0n/Rotate.pm | 6 +++--- perl/Sesse/pr0n/WebDAV.pm | 6 +++--- sql/pr0n.sql | 2 +- 5 files changed, 10 insertions(+), 10 deletions(-) diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm index 08491bb..3597dee 100644 --- a/perl/Sesse/pr0n/Index.pm +++ b/perl/Sesse/pr0n/Index.pm @@ -150,14 +150,14 @@ sub handler { my ($date, $name); if ($event eq '+all' || defined($tag)) { - $ref = $dbh->selectrow_hashref("SELECT EXTRACT(EPOCH FROM MAX(last_update)) AS last_update FROM events WHERE vhost=?", + $ref = $dbh->selectrow_hashref("SELECT EXTRACT(EPOCH FROM MAX(last_update)) AS last_update FROM last_picture_cache WHERE vhost=?", undef, $r->get_server_name) or error($r, "Could not list events", 404, "File not found"); $date = undef; $name = Sesse::pr0n::Templates::fetch_template($r, 'all-event-title'); $r->set_last_modified($ref->{'last_update'}); } else { - $ref = $dbh->selectrow_hashref("SELECT name,date,EXTRACT(EPOCH FROM last_update) AS last_update FROM events WHERE vhost=? AND event=?", + $ref = $dbh->selectrow_hashref("SELECT name,date,EXTRACT(EPOCH FROM last_update) AS last_update FROM events NATURAL JOIN last_picture_cache WHERE vhost=? AND event=?", undef, $r->get_server_name, $event) or error($r, "Could not find event $event", 404, "File not found"); diff --git a/perl/Sesse/pr0n/Listing.pm b/perl/Sesse/pr0n/Listing.pm index 03285a1..a006945 100644 --- a/perl/Sesse/pr0n/Listing.pm +++ b/perl/Sesse/pr0n/Listing.pm @@ -24,7 +24,7 @@ sub handler { } # find the last modification - my $ref = $dbh->selectrow_hashref('SELECT EXTRACT(EPOCH FROM last_update) AS last_update FROM events WHERE vhost=? ORDER BY last_update DESC LIMIT 1', + my $ref = $dbh->selectrow_hashref('SELECT EXTRACT(EPOCH FROM last_update) AS last_update FROM last_picture_cache WHERE vhost=? ORDER BY last_update DESC LIMIT 1', undef, $r->get_server_name) or error($r, "Could not any events", 404, "File not found"); $r->set_last_modified($ref->{'last_update'}); diff --git a/perl/Sesse/pr0n/Rotate.pm b/perl/Sesse/pr0n/Rotate.pm index c3769f5..be56225 100644 --- a/perl/Sesse/pr0n/Rotate.pm +++ b/perl/Sesse/pr0n/Rotate.pm @@ -37,7 +37,7 @@ sub handler { if ($rotval == 90 || $rotval == 270) { my $q = $dbh->do('UPDATE images SET height=width,width=height WHERE id=?', undef, $id) or dberror($r, "Size clear of $id failed"); - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )', + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )', undef, $id) or dberror($r, "Cache invalidation at $id failed"); } @@ -46,7 +46,7 @@ sub handler { { eval { - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )', + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE (vhost,event)=( SELECT vhost,event FROM images WHERE id=? )', undef, $id); $dbh->do('INSERT INTO deleted_images SELECT * FROM images WHERE id=?', undef, $id); @@ -67,7 +67,7 @@ sub handler { } my $event = $apr->param('event'); - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event) + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event) or dberror($r, "Cache invalidation failed"); Sesse::pr0n::Common::footer($r); diff --git a/perl/Sesse/pr0n/WebDAV.pm b/perl/Sesse/pr0n/WebDAV.pm index 3a7751c..c38e7b2 100644 --- a/perl/Sesse/pr0n/WebDAV.pm +++ b/perl/Sesse/pr0n/WebDAV.pm @@ -468,7 +468,7 @@ EOF $dbh->do('INSERT INTO images (id,vhost,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?,?)', undef, $newid, $r->get_server_name, $event, $user, $takenby, $filename); - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event); # Now save the file to disk @@ -606,7 +606,7 @@ EOF eval { $dbh->do('INSERT INTO images (id,vhost,event,uploadedby,takenby,filename) VALUES (?,?,?,?,?,?)', undef, $newid, $r->get_server_name, $event, $user, $takenby, $filename); - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event); # Now save the file to disk @@ -708,7 +708,7 @@ EOF $dbh->do('DELETE FROM images WHERE vhost=? AND event=? AND filename=?', undef, $r->get_server_name, $event, $filename) or dberror($r, "Couldn't remove file"); - $dbh->do('UPDATE events SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', + $dbh->do('UPDATE last_picture_cache SET last_update=CURRENT_TIMESTAMP WHERE vhost=? AND event=?', undef, $r->get_server_name, $event) or dberror($r, "Couldn't invalidate cache"); $r->status(200); diff --git a/sql/pr0n.sql b/sql/pr0n.sql index 7ce156a..5f89f6c 100644 --- a/sql/pr0n.sql +++ b/sql/pr0n.sql @@ -3,7 +3,6 @@ CREATE TABLE events ( "date" character varying NOT NULL, name character varying NOT NULL, vhost character varying NOT NULL, - last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (vhost, event) ); @@ -13,6 +12,7 @@ CREATE TABLE last_picture_cache ( vhost varchar NOT NULL, event varchar NOT NULL, last_picture timestamp without time zone, + last_update timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (vhost,event), FOREIGN KEY (vhost,event) REFERENCES events(vhost,event) -- 2.39.2