Repository: rucio/rucio
Issue: postgres requires ::text conversion of uuid for md5

Motivation
----------

It seems that some PSQL instances do not autoconvert uuid to text when calling md5(). We can force the conversion, e.g., this line:
               `query = query.filter(text('mod(abs((\\'x\\'||md5(id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))`
should actually be:
               `query = query.filter(text('mod(abs((\\'x\\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))`

Changed files:
lib/rucio/core/request.py, lib/rucio/core/rse_counter.py, lib/rucio/core/transfer.py, lib/rucio/core/message.py

diff --git a/lib/rucio/core/message.py b/lib/rucio/core/message.py
index 4bfc13397..f179d908f 100644
--- a/lib/rucio/core/message.py
+++ b/lib/rucio/core/message.py
@@ -71,7 +71,7 @@ def retrieve_messages(bulk=1000, thread=None, total_threads=None, event_type=Non
             elif session.bind.dialect.name == 'mysql':
                 subquery = subquery.filter('mod(md5(id), %s) = %s' % (total_threads - 1, thread))
             elif session.bind.dialect.name == 'postgresql':
-                subquery = subquery.filter('mod(abs((\\'x\\'||md5(id))::bit(32)::int), %s) = %s' % (total_threads - 1, thread))
+                subquery = subquery.filter('mod(abs((\\'x\\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_threads - 1, thread))
 
         if event_type:
             subquery = subquery.filter_by(event_type=event_type)
diff --git a/lib/rucio/core/request.py b/lib/rucio/core/request.py
index ddde43cf2..c22b37a28 100644
--- a/lib/rucio/core/request.py
+++ b/lib/rucio/core/request.py
@@ -729,7 +729,7 @@ def list_stagein_requests_and_source_replicas(total_workers=0, worker_number=0,
         elif session.bind.dialect.name == 'mysql':
             sub_requests = sub_requests.filter(text('mod(md5(id), %s) = %s' % (total_workers + 1, worker_number)))
         elif session.bind.dialect.name == 'postgresql':
-            sub_requests = sub_requests.filter(text('mod(abs((\\'x\\'||md5(id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
+            sub_requests = sub_requests.filter(text('mod(abs((\\'x\\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
 
     if limit:
         sub_requests = sub_requests.limit(limit)
diff --git a/lib/rucio/core/rse_counter.py b/lib/rucio/core/rse_counter.py
index 74091d128..68e36dfd7 100644
--- a/lib/rucio/core/rse_counter.py
+++ b/lib/rucio/core/rse_counter.py
@@ -113,7 +113,7 @@ def get_updated_rse_counters(total_workers, worker_number, session=None):
         elif session.bind.dialect.name == 'mysql':
             query = query.filter('mod(md5(rse_id), %s) = %s' % (total_workers + 1, worker_number))
         elif session.bind.dialect.name == 'postgresql':
-            query = query.filter('mod(abs((\\'x\\'||md5(rse_id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))
+            query = query.filter('mod(abs((\\'x\\'||md5(rse_id::text))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number))
 
     results = query.all()
     return [result.rse_id for result in results]
diff --git a/lib/rucio/core/transfer.py b/lib/rucio/core/transfer.py
index a090b38a4..ec4c94af8 100644
--- a/lib/rucio/core/transfer.py
+++ b/lib/rucio/core/transfer.py
@@ -250,7 +250,7 @@ def get_next_transfers_new(request_type, state, limit=100, older_than=None, rse=
             elif session.bind.dialect.name == 'mysql':
                 query = query.filter(text('mod(md5(id), %s) = %s' % (total_workers + 1, worker_number)))
             elif session.bind.dialect.name == 'postgresql':
-                query = query.filter(text('mod(abs((\\'x\\'||md5(id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
+                query = query.filter(text('mod(abs((\\'x\\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
 
         if share:
             query = query.limit(activity_shares[share])
@@ -973,7 +973,7 @@ def __list_transfer_requests_and_source_replicas(total_workers=0, worker_number=
         elif session.bind.dialect.name == 'mysql':
             sub_requests = sub_requests.filter(text('mod(md5(id), %s) = %s' % (total_workers + 1, worker_number)))
         elif session.bind.dialect.name == 'postgresql':
-            sub_requests = sub_requests.filter(text('mod(abs((\\'x\\'||md5(id))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
+            sub_requests = sub_requests.filter(text('mod(abs((\\'x\\'||md5(id::text))::bit(32)::int), %s) = %s' % (total_workers + 1, worker_number)))
 
     if limit:
         sub_requests = sub_requests.limit(limit)