We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
PostgreSQL Cache: Deadlock
```
(psycopg2.extensions.TransactionRollbackError) deadlock detected
DETAIL:  Process 12750 waits for ShareLock on transaction 632693475; blocked by process 10080.
Process 10080 waits for ShareLock on transaction 632693479; blocked by process 12750.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (426,1) in relation "cache"
 [SQL: 'DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;']
```

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `kinto/core/cache/postgresql/__init__.py`
Content:
```
1 import logging
2 import os
3 
4 from kinto.core.cache import CacheBase
5 from kinto.core.storage.postgresql.client import create_from_config
6 from kinto.core.utils import json
7 
8 
9 logger = logging.getLogger(__name__)
10 
11 
12 class Cache(CacheBase):
13     """Cache backend using PostgreSQL.
14 
15     Enable in configuration::
16 
17         kinto.cache_backend = kinto.core.cache.postgresql
18 
19     Database location URI can be customized::
20 
21         kinto.cache_url = postgres://user:pass@db.server.lan:5432/dbname
22 
23     Alternatively, username and password could also rely on system user ident
24     or even specified in :file:`~/.pgpass` (*see PostgreSQL documentation*).
25 
26     .. note::
27 
28         Some tables and indices are created when ``kinto migrate`` is run.
29         This requires some privileges on the database, or some error will
30         be raised.
31 
32         **Alternatively**, the schema can be initialized outside the
33         python application, using the SQL file located in
34         :file:`kinto/core/cache/postgresql/schema.sql`. This allows to
35         distinguish schema manipulation privileges from schema usage.
36 
37 
38     A connection pool is enabled by default::
39 
40         kinto.cache_pool_size = 10
41         kinto.cache_maxoverflow = 10
42         kinto.cache_max_backlog = -1
43         kinto.cache_pool_recycle = -1
44         kinto.cache_pool_timeout = 30
45         kinto.cache_poolclass =
46             kinto.core.storage.postgresql.pool.QueuePoolWithMaxBacklog
47 
48     The ``max_backlog``  limits the number of threads that can be in the queue
49     waiting for a connection.  Once this limit has been reached, any further
50     attempts to acquire a connection will be rejected immediately, instead of
51     locking up all threads by keeping them waiting in the queue.
52 
53     See `dedicated section in SQLAlchemy documentation
54     <http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html>`_
55     for default values and behaviour.
56 
57     .. note::
58 
59         Using a `dedicated connection pool <http://pgpool.net>`_ is still
60         recommended to allow load balancing, replication or limit the number
61         of connections used in a multi-process deployment.
62 
63     :noindex:
64     """  # NOQA
65     def __init__(self, client, *args, **kwargs):
66         super().__init__(*args, **kwargs)
67         self.client = client
68 
69     def initialize_schema(self, dry_run=False):
70         # Check if cache table exists.
71         query = """
72         SELECT 1
73           FROM information_schema.tables
74          WHERE table_name = 'cache';
75         """
76         with self.client.connect(readonly=True) as conn:
77             result = conn.execute(query)
78             if result.rowcount > 0:
79                 logger.info("PostgreSQL cache schema is up-to-date.")
80                 return
81 
82         # Create schema
83         here = os.path.abspath(os.path.dirname(__file__))
84         sql_file = os.path.join(here, 'schema.sql')
85 
86         if dry_run:
87             logger.info("Create cache schema from '{}'".format(sql_file))
88             return
89 
90         # Since called outside request, force commit.
91         with open(sql_file) as f:
92             schema = f.read()
93         with self.client.connect(force_commit=True) as conn:
94             conn.execute(schema)
95         logger.info('Created PostgreSQL cache tables')
96 
97     def flush(self):
98         query = """
99         DELETE FROM cache;
100         """
101         # Since called outside request (e.g. tests), force commit.
102         with self.client.connect(force_commit=True) as conn:
103             conn.execute(query)
104         logger.debug('Flushed PostgreSQL cache tables')
105 
106     def ttl(self, key):
107         query = """
108         SELECT EXTRACT(SECOND FROM (ttl - now())) AS ttl
109           FROM cache
110          WHERE key = :key
111            AND ttl IS NOT NULL;
112         """
113         with self.client.connect(readonly=True) as conn:
114             result = conn.execute(query, dict(key=self.prefix + key))
115             if result.rowcount > 0:
116                 return result.fetchone()['ttl']
117         return -1
118 
119     def expire(self, key, ttl):
120         query = """
121         UPDATE cache SET ttl = sec2ttl(:ttl) WHERE key = :key;
122         """
123         with self.client.connect() as conn:
124             conn.execute(query, dict(ttl=ttl, key=self.prefix + key))
125 
126     def set(self, key, value, ttl):
127         if isinstance(value, bytes):
128             raise TypeError("a string-like object is required, not 'bytes'")
129 
130         query = """
131         INSERT INTO cache (key, value, ttl)
132         VALUES (:key, :value, sec2ttl(:ttl))
133         ON CONFLICT (key) DO UPDATE
134         SET value = :value,
135             ttl = sec2ttl(:ttl);
136         """
137         value = json.dumps(value)
138         with self.client.connect() as conn:
139             conn.execute(query, dict(key=self.prefix + key,
140                                      value=value, ttl=ttl))
141 
142     def get(self, key):
143         purge = "DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;"
144         query = "SELECT value FROM cache WHERE key = :key;"
145         with self.client.connect() as conn:
146             conn.execute(purge)
147             result = conn.execute(query, dict(key=self.prefix + key))
148             if result.rowcount > 0:
149                 value = result.fetchone()['value']
150                 return json.loads(value)
151 
152     def delete(self, key):
153         query = "DELETE FROM cache WHERE key = :key RETURNING value;"
154         with self.client.connect() as conn:
155             result = conn.execute(query, dict(key=self.prefix + key))
156             if result.rowcount > 0:
157                 value = result.fetchone()['value']
158                 return json.loads(value)
159         return None
160 
161 
162 def load_from_config(config):
163     settings = config.get_settings()
164     client = create_from_config(config, prefix='cache_', with_transaction=False)
165     return Cache(client=client, cache_prefix=settings['cache_prefix'])
166
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/kinto/core/cache/postgresql/__init__.py b/kinto/core/cache/postgresql/__init__.py
--- a/kinto/core/cache/postgresql/__init__.py
+++ b/kinto/core/cache/postgresql/__init__.py
@@ -140,8 +140,17 @@
                                      value=value, ttl=ttl))
 
     def get(self, key):
-        purge = "DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;"
-        query = "SELECT value FROM cache WHERE key = :key;"
+        purge = """
+        DELETE FROM cache c
+        USING (
+            SELECT key
+            FROM cache
+            WHERE ttl IS NOT NULL AND now() > ttl
+            ORDER BY key ASC
+            FOR UPDATE
+        ) del
+        WHERE del.key = c.key;"""
+        query = "SELECT value FROM cache WHERE key = :key AND now() < ttl;"
         with self.client.connect() as conn:
             conn.execute(purge)
             result = conn.execute(query, dict(key=self.prefix + key))

{"golden_diff": "diff --git a/kinto/core/cache/postgresql/__init__.py b/kinto/core/cache/postgresql/__init__.py\n--- a/kinto/core/cache/postgresql/__init__.py\n+++ b/kinto/core/cache/postgresql/__init__.py\n@@ -140,8 +140,17 @@\n                                      value=value, ttl=ttl))\n \n     def get(self, key):\n-        purge = \"DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;\"\n-        query = \"SELECT value FROM cache WHERE key = :key;\"\n+        purge = \"\"\"\n+        DELETE FROM cache c\n+        USING (\n+            SELECT key\n+            FROM cache\n+            WHERE ttl IS NOT NULL AND now() > ttl\n+            ORDER BY key ASC\n+            FOR UPDATE\n+        ) del\n+        WHERE del.key = c.key;\"\"\"\n+        query = \"SELECT value FROM cache WHERE key = :key AND now() < ttl;\"\n         with self.client.connect() as conn:\n             conn.execute(purge)\n             result = conn.execute(query, dict(key=self.prefix + key))\n", "issue": "PostgreSQL Cache: Deadlock\n```\r\n(psycopg2.extensions.TransactionRollbackError) deadlock detected\r\nDETAIL:  Process 12750 waits for ShareLock on transaction 632693475; blocked by process 10080.\r\nProcess 10080 waits for ShareLock on transaction 632693479; blocked by process 12750.\r\nHINT:  See server log for query details.\r\nCONTEXT:  while deleting tuple (426,1) in relation \"cache\"\r\n [SQL: 'DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;']\r\n```\n", "before_files": [{"content": "import logging\nimport os\n\nfrom kinto.core.cache import CacheBase\nfrom kinto.core.storage.postgresql.client import create_from_config\nfrom kinto.core.utils import json\n\n\nlogger = logging.getLogger(__name__)\n\n\nclass Cache(CacheBase):\n    \"\"\"Cache backend using PostgreSQL.\n\n    Enable in configuration::\n\n        kinto.cache_backend = kinto.core.cache.postgresql\n\n    Database location URI can be customized::\n\n        kinto.cache_url = postgres://user:pass@db.server.lan:5432/dbname\n\n    Alternatively, username and password could also rely on system user ident\n    or even specified in :file:`~/.pgpass` (*see PostgreSQL documentation*).\n\n    .. note::\n\n        Some tables and indices are created when ``kinto migrate`` is run.\n        This requires some privileges on the database, or some error will\n        be raised.\n\n        **Alternatively**, the schema can be initialized outside the\n        python application, using the SQL file located in\n        :file:`kinto/core/cache/postgresql/schema.sql`. This allows to\n        distinguish schema manipulation privileges from schema usage.\n\n\n    A connection pool is enabled by default::\n\n        kinto.cache_pool_size = 10\n        kinto.cache_maxoverflow = 10\n        kinto.cache_max_backlog = -1\n        kinto.cache_pool_recycle = -1\n        kinto.cache_pool_timeout = 30\n        kinto.cache_poolclass =\n            kinto.core.storage.postgresql.pool.QueuePoolWithMaxBacklog\n\n    The ``max_backlog``  limits the number of threads that can be in the queue\n    waiting for a connection.  Once this limit has been reached, any further\n    attempts to acquire a connection will be rejected immediately, instead of\n    locking up all threads by keeping them waiting in the queue.\n\n    See `dedicated section in SQLAlchemy documentation\n    <http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html>`_\n    for default values and behaviour.\n\n    .. note::\n\n        Using a `dedicated connection pool <http://pgpool.net>`_ is still\n        recommended to allow load balancing, replication or limit the number\n        of connections used in a multi-process deployment.\n\n    :noindex:\n    \"\"\"  # NOQA\n    def __init__(self, client, *args, **kwargs):\n        super().__init__(*args, **kwargs)\n        self.client = client\n\n    def initialize_schema(self, dry_run=False):\n        # Check if cache table exists.\n        query = \"\"\"\n        SELECT 1\n          FROM information_schema.tables\n         WHERE table_name = 'cache';\n        \"\"\"\n        with self.client.connect(readonly=True) as conn:\n            result = conn.execute(query)\n            if result.rowcount > 0:\n                logger.info(\"PostgreSQL cache schema is up-to-date.\")\n                return\n\n        # Create schema\n        here = os.path.abspath(os.path.dirname(__file__))\n        sql_file = os.path.join(here, 'schema.sql')\n\n        if dry_run:\n            logger.info(\"Create cache schema from '{}'\".format(sql_file))\n            return\n\n        # Since called outside request, force commit.\n        with open(sql_file) as f:\n            schema = f.read()\n        with self.client.connect(force_commit=True) as conn:\n            conn.execute(schema)\n        logger.info('Created PostgreSQL cache tables')\n\n    def flush(self):\n        query = \"\"\"\n        DELETE FROM cache;\n        \"\"\"\n        # Since called outside request (e.g. tests), force commit.\n        with self.client.connect(force_commit=True) as conn:\n            conn.execute(query)\n        logger.debug('Flushed PostgreSQL cache tables')\n\n    def ttl(self, key):\n        query = \"\"\"\n        SELECT EXTRACT(SECOND FROM (ttl - now())) AS ttl\n          FROM cache\n         WHERE key = :key\n           AND ttl IS NOT NULL;\n        \"\"\"\n        with self.client.connect(readonly=True) as conn:\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                return result.fetchone()['ttl']\n        return -1\n\n    def expire(self, key, ttl):\n        query = \"\"\"\n        UPDATE cache SET ttl = sec2ttl(:ttl) WHERE key = :key;\n        \"\"\"\n        with self.client.connect() as conn:\n            conn.execute(query, dict(ttl=ttl, key=self.prefix + key))\n\n    def set(self, key, value, ttl):\n        if isinstance(value, bytes):\n            raise TypeError(\"a string-like object is required, not 'bytes'\")\n\n        query = \"\"\"\n        INSERT INTO cache (key, value, ttl)\n        VALUES (:key, :value, sec2ttl(:ttl))\n        ON CONFLICT (key) DO UPDATE\n        SET value = :value,\n            ttl = sec2ttl(:ttl);\n        \"\"\"\n        value = json.dumps(value)\n        with self.client.connect() as conn:\n            conn.execute(query, dict(key=self.prefix + key,\n                                     value=value, ttl=ttl))\n\n    def get(self, key):\n        purge = \"DELETE FROM cache WHERE ttl IS NOT NULL AND now() > ttl;\"\n        query = \"SELECT value FROM cache WHERE key = :key;\"\n        with self.client.connect() as conn:\n            conn.execute(purge)\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                value = result.fetchone()['value']\n                return json.loads(value)\n\n    def delete(self, key):\n        query = \"DELETE FROM cache WHERE key = :key RETURNING value;\"\n        with self.client.connect() as conn:\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                value = result.fetchone()['value']\n                return json.loads(value)\n        return None\n\n\ndef load_from_config(config):\n    settings = config.get_settings()\n    client = create_from_config(config, prefix='cache_', with_transaction=False)\n    return Cache(client=client, cache_prefix=settings['cache_prefix'])\n", "path": "kinto/core/cache/postgresql/__init__.py"}], "after_files": [{"content": "import logging\nimport os\n\nfrom kinto.core.cache import CacheBase\nfrom kinto.core.storage.postgresql.client import create_from_config\nfrom kinto.core.utils import json\n\n\nlogger = logging.getLogger(__name__)\n\n\nclass Cache(CacheBase):\n    \"\"\"Cache backend using PostgreSQL.\n\n    Enable in configuration::\n\n        kinto.cache_backend = kinto.core.cache.postgresql\n\n    Database location URI can be customized::\n\n        kinto.cache_url = postgres://user:pass@db.server.lan:5432/dbname\n\n    Alternatively, username and password could also rely on system user ident\n    or even specified in :file:`~/.pgpass` (*see PostgreSQL documentation*).\n\n    .. note::\n\n        Some tables and indices are created when ``kinto migrate`` is run.\n        This requires some privileges on the database, or some error will\n        be raised.\n\n        **Alternatively**, the schema can be initialized outside the\n        python application, using the SQL file located in\n        :file:`kinto/core/cache/postgresql/schema.sql`. This allows to\n        distinguish schema manipulation privileges from schema usage.\n\n\n    A connection pool is enabled by default::\n\n        kinto.cache_pool_size = 10\n        kinto.cache_maxoverflow = 10\n        kinto.cache_max_backlog = -1\n        kinto.cache_pool_recycle = -1\n        kinto.cache_pool_timeout = 30\n        kinto.cache_poolclass =\n            kinto.core.storage.postgresql.pool.QueuePoolWithMaxBacklog\n\n    The ``max_backlog``  limits the number of threads that can be in the queue\n    waiting for a connection.  Once this limit has been reached, any further\n    attempts to acquire a connection will be rejected immediately, instead of\n    locking up all threads by keeping them waiting in the queue.\n\n    See `dedicated section in SQLAlchemy documentation\n    <http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html>`_\n    for default values and behaviour.\n\n    .. note::\n\n        Using a `dedicated connection pool <http://pgpool.net>`_ is still\n        recommended to allow load balancing, replication or limit the number\n        of connections used in a multi-process deployment.\n\n    :noindex:\n    \"\"\"  # NOQA\n    def __init__(self, client, *args, **kwargs):\n        super().__init__(*args, **kwargs)\n        self.client = client\n\n    def initialize_schema(self, dry_run=False):\n        # Check if cache table exists.\n        query = \"\"\"\n        SELECT 1\n          FROM information_schema.tables\n         WHERE table_name = 'cache';\n        \"\"\"\n        with self.client.connect(readonly=True) as conn:\n            result = conn.execute(query)\n            if result.rowcount > 0:\n                logger.info(\"PostgreSQL cache schema is up-to-date.\")\n                return\n\n        # Create schema\n        here = os.path.abspath(os.path.dirname(__file__))\n        sql_file = os.path.join(here, 'schema.sql')\n\n        if dry_run:\n            logger.info(\"Create cache schema from '{}'\".format(sql_file))\n            return\n\n        # Since called outside request, force commit.\n        with open(sql_file) as f:\n            schema = f.read()\n        with self.client.connect(force_commit=True) as conn:\n            conn.execute(schema)\n        logger.info('Created PostgreSQL cache tables')\n\n    def flush(self):\n        query = \"\"\"\n        DELETE FROM cache;\n        \"\"\"\n        # Since called outside request (e.g. tests), force commit.\n        with self.client.connect(force_commit=True) as conn:\n            conn.execute(query)\n        logger.debug('Flushed PostgreSQL cache tables')\n\n    def ttl(self, key):\n        query = \"\"\"\n        SELECT EXTRACT(SECOND FROM (ttl - now())) AS ttl\n          FROM cache\n         WHERE key = :key\n           AND ttl IS NOT NULL;\n        \"\"\"\n        with self.client.connect(readonly=True) as conn:\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                return result.fetchone()['ttl']\n        return -1\n\n    def expire(self, key, ttl):\n        query = \"\"\"\n        UPDATE cache SET ttl = sec2ttl(:ttl) WHERE key = :key;\n        \"\"\"\n        with self.client.connect() as conn:\n            conn.execute(query, dict(ttl=ttl, key=self.prefix + key))\n\n    def set(self, key, value, ttl):\n        if isinstance(value, bytes):\n            raise TypeError(\"a string-like object is required, not 'bytes'\")\n\n        query = \"\"\"\n        INSERT INTO cache (key, value, ttl)\n        VALUES (:key, :value, sec2ttl(:ttl))\n        ON CONFLICT (key) DO UPDATE\n        SET value = :value,\n            ttl = sec2ttl(:ttl);\n        \"\"\"\n        value = json.dumps(value)\n        with self.client.connect() as conn:\n            conn.execute(query, dict(key=self.prefix + key,\n                                     value=value, ttl=ttl))\n\n    def get(self, key):\n        purge = \"\"\"\n        DELETE FROM cache c\n        USING (\n            SELECT key\n            FROM cache\n            WHERE ttl IS NOT NULL AND now() > ttl\n            ORDER BY key ASC\n            FOR UPDATE\n        ) del\n        WHERE del.key = c.key;\"\"\"\n        query = \"SELECT value FROM cache WHERE key = :key AND now() < ttl;\"\n        with self.client.connect() as conn:\n            conn.execute(purge)\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                value = result.fetchone()['value']\n                return json.loads(value)\n\n    def delete(self, key):\n        query = \"DELETE FROM cache WHERE key = :key RETURNING value;\"\n        with self.client.connect() as conn:\n            result = conn.execute(query, dict(key=self.prefix + key))\n            if result.rowcount > 0:\n                value = result.fetchone()['value']\n                return json.loads(value)\n        return None\n\n\ndef load_from_config(config):\n    settings = config.get_settings()\n    client = create_from_config(config, prefix='cache_', with_transaction=False)\n    return Cache(client=client, cache_prefix=settings['cache_prefix'])\n", "path": "kinto/core/cache/postgresql/__init__.py"}]}