Use public.locks as locking area.

Since the central packages table was introduced we used FOR UPDATE
for synchronization between different wanna-build calls.  This
causes a write for every locked row, which is unhelpful for WAL
traffic considering that merge-v3 needs to lock everything.

Let's synchronize by using FOR UPDATE on public.locks instead,
which should ease the WAL load a lot.

diff --git a/bin/wanna-build b/bin/wanna-build
index 78e80ba119b75fe8c2837c00db31dc105eddd0a3..0d8bd1ad0777a1f117eaaad270a6ff3c6705a30e 100755 (executable)
--- a/bin/wanna-build
+++ b/bin/wanna-build
@@ -1917,9 +1917,9 @@ sub get_readonly_source_info {
 sub get_source_info {
        my $name = shift;
         return get_readonly_source_info($name) if $simulate;
+       lock_table();
        my $pkg = $dbh->selectrow_hashref('SELECT *, extract(days from date_trunc(\'days\', now() - state_change)) as state_days, floor(extract(epoch from now()) - extract(epoch from state_change)) as state_time FROM ' . 
-               table_name() . ' WHERE package = ? AND distribution = ?' .
-               ' FOR UPDATE',
+               table_name() . ' WHERE package = ? AND distribution = ?',
                undef, $name, $distribution);
        return $pkg;
 }
@@ -2114,8 +2114,10 @@ sub add_user_info {
 
 sub lock_table {
         return if $simulate;
-       $dbh->do('SELECT 1 FROM ' . table_name() .
-               ' WHERE distribution = ? FOR UPDATE', undef, $distribution) or die $dbh->errstr;
+       $q = 'SELECT 1 AS result FROM public.locks' .
+            ' WHERE architecture = ? AND distribution = ? FOR UPDATE';
+       my $result = $dbh->selectrow_hashref($q, undef, $arch, $distribution) or die $dbh->errstr;
+       die unless $result->{'result'} == 1;
 }
 
 sub parse_argv {

diff --git a/schema/old-migrations/lock_table.sql b/schema/old-migrations/lock_table.sql
new file mode 100644 (file)
index 0000000..318e722
--- /dev/null
+++ b/schema/old-migrations/lock_table.sql
@@ -0,0 +1,10 @@
+BEGIN;
+
+CREATE TABLE public.locks AS
+       SELECT DISTINCT distribution, architecture FROM packages;
+ALTER TABLE public.locks OWNER TO wbadm;
+REVOKE ALL ON public.locks FROM PUBLIC;
+GRANT SELECT ON public.locks TO PUBLIC;
+GRANT UPDATE ON public.locks TO wb_all;
+
+COMMIT;