Fix another deadlock-when-many-people-upload bug, like the one we fixed in r14

(see that revision for full explanation).

diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm
index 08491bb775830224f4e99a2d281c46a0276e0853..3597deeaff849d37d19cacffd3da4d9a4ce9eb08 100644 (file)
--- 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 03285a142f969265cf2ae340ed8b7fe68fc1b536..a0069455b9107b6582466555c4bd0f74a00edd53 100644 (file)
--- 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 c3769f532730cd43c3b037a705307e71d159b4eb..be562255da39bb134ac488c9b7c1d2894d3a7290 100644 (file)
--- 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 3a7751cb55f479d9cc17872caa29d1c6edf9957b..c38e7b2f159a1888822dc3d3d230b5011bdf7056 100644 (file)
--- 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 7ce156a30a4ad2f7912f366414d68ee11b5d213c..5f89f6c91e03c8bf62e76684a3718612d8c53218 100644 (file)
--- 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)