We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
PostgreSQL queries returning a tsrange fail
### Issue Summary

Running a query which includes a tsrange column fail with a JSON error like this:

```
DateTimeRange(datetime.datetime(2016, 8, 25, 15, 21, 45, 760521), datetime.datetime(2016, 10, 14, 5, 56, 52, 452297), '[)') is not JSON serializable
```

### Steps to Reproduce

1. Create a table with a tsrange column
2. Insert some data into it
3. Create a `SELECT * FROM table` query
4. Run the query

This is turning out to be confusing for our users - I'm getting regular complaints about queries failing with this error because some of our tables have range columns.

### Technical details:

* Redash Version: 1.0.1+b2845
* Browser/OS: Linux
* How did you install Redash: Docker image


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `redash/query_runner/pg.py`
Content:
```
1 import os
2 import logging
3 import select
4 
5 import psycopg2
6 
7 from redash.query_runner import *
8 from redash.utils import json_dumps, json_loads
9 
10 logger = logging.getLogger(__name__)
11 
12 types_map = {
13     20: TYPE_INTEGER,
14     21: TYPE_INTEGER,
15     23: TYPE_INTEGER,
16     700: TYPE_FLOAT,
17     1700: TYPE_FLOAT,
18     701: TYPE_FLOAT,
19     16: TYPE_BOOLEAN,
20     1082: TYPE_DATE,
21     1114: TYPE_DATETIME,
22     1184: TYPE_DATETIME,
23     1014: TYPE_STRING,
24     1015: TYPE_STRING,
25     1008: TYPE_STRING,
26     1009: TYPE_STRING,
27     2951: TYPE_STRING
28 }
29 
30 
31 def _wait(conn, timeout=None):
32     while 1:
33         try:
34             state = conn.poll()
35             if state == psycopg2.extensions.POLL_OK:
36                 break
37             elif state == psycopg2.extensions.POLL_WRITE:
38                 select.select([], [conn.fileno()], [], timeout)
39             elif state == psycopg2.extensions.POLL_READ:
40                 select.select([conn.fileno()], [], [], timeout)
41             else:
42                 raise psycopg2.OperationalError("poll() returned %s" % state)
43         except select.error:
44             raise psycopg2.OperationalError("select.error received")
45 
46 
47 class PostgreSQL(BaseSQLQueryRunner):
48     noop_query = "SELECT 1"
49 
50     @classmethod
51     def configuration_schema(cls):
52         return {
53             "type": "object",
54             "properties": {
55                 "user": {
56                     "type": "string"
57                 },
58                 "password": {
59                     "type": "string"
60                 },
61                 "host": {
62                     "type": "string",
63                     "default": "127.0.0.1"
64                 },
65                 "port": {
66                     "type": "number",
67                     "default": 5432
68                 },
69                 "dbname": {
70                     "type": "string",
71                     "title": "Database Name"
72                 },
73                 "sslmode": {
74                    "type": "string",
75                    "title": "SSL Mode",
76                    "default": "prefer"
77                 }
78             },
79             "order": ['host', 'port', 'user', 'password'],
80             "required": ["dbname"],
81             "secret": ["password"]
82         }
83 
84     @classmethod
85     def type(cls):
86         return "pg"
87 
88     def _get_definitions(self, schema, query):
89         results, error = self.run_query(query, None)
90 
91         if error is not None:
92             raise Exception("Failed getting schema.")
93 
94         results = json_loads(results)
95 
96         for row in results['rows']:
97             if row['table_schema'] != 'public':
98                 table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])
99             else:
100                 table_name = row['table_name']
101 
102             if table_name not in schema:
103                 schema[table_name] = {'name': table_name, 'columns': []}
104 
105             schema[table_name]['columns'].append(row['column_name'])
106 
107     def _get_tables(self, schema):
108         '''
109         relkind constants per https://www.postgresql.org/docs/10/static/catalog-pg-class.html
110         r = regular table
111         v = view
112         m = materialized view
113         f = foreign table
114         p = partitioned table (new in 10)
115         ---
116         i = index
117         S = sequence
118         t = TOAST table
119         c = composite type
120         '''
121 
122         query = """
123         SELECT s.nspname as table_schema,
124                c.relname as table_name,
125                a.attname as column_name
126         FROM pg_class c
127         JOIN pg_namespace s
128         ON c.relnamespace = s.oid
129         AND s.nspname NOT IN ('pg_catalog', 'information_schema')
130         JOIN pg_attribute a
131         ON a.attrelid = c.oid
132         AND a.attnum > 0
133         AND NOT a.attisdropped
134         WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')
135         """
136 
137         self._get_definitions(schema, query)
138 
139         return schema.values()
140 
141     def _get_connection(self):
142         connection = psycopg2.connect(user=self.configuration.get('user'),
143                                       password=self.configuration.get('password'),
144                                       host=self.configuration.get('host'),
145                                       port=self.configuration.get('port'),
146                                       dbname=self.configuration.get('dbname'),
147                                       sslmode=self.configuration.get('sslmode'),
148                                       async_=True)
149 
150         return connection
151 
152     def run_query(self, query, user):
153         connection = self._get_connection()
154         _wait(connection, timeout=10)
155 
156         cursor = connection.cursor()
157 
158         try:
159             cursor.execute(query)
160             _wait(connection)
161 
162             if cursor.description is not None:
163                 columns = self.fetch_columns([(i[0], types_map.get(i[1], None)) for i in cursor.description])
164                 rows = [dict(zip((c['name'] for c in columns), row)) for row in cursor]
165 
166                 data = {'columns': columns, 'rows': rows}
167                 error = None
168                 json_data = json_dumps(data, ignore_nan=True)
169             else:
170                 error = 'Query completed but it returned no data.'
171                 json_data = None
172         except (select.error, OSError) as e:
173             error = "Query interrupted. Please retry."
174             json_data = None
175         except psycopg2.DatabaseError as e:
176             error = e.message
177             json_data = None
178         except (KeyboardInterrupt, InterruptException):
179             connection.cancel()
180             error = "Query cancelled by user."
181             json_data = None
182         finally:
183             connection.close()
184 
185         return json_data, error
186 
187 
188 class Redshift(PostgreSQL):
189     @classmethod
190     def type(cls):
191         return "redshift"
192 
193     def _get_connection(self):
194         sslrootcert_path = os.path.join(os.path.dirname(__file__), './files/redshift-ca-bundle.crt')
195 
196         connection = psycopg2.connect(user=self.configuration.get('user'),
197                                       password=self.configuration.get('password'),
198                                       host=self.configuration.get('host'),
199                                       port=self.configuration.get('port'),
200                                       dbname=self.configuration.get('dbname'),
201                                       sslmode=self.configuration.get('sslmode', 'prefer'),
202                                       sslrootcert=sslrootcert_path,
203                                       async_=True)
204 
205         return connection
206 
207     @classmethod
208     def configuration_schema(cls):
209 
210         return {
211             "type": "object",
212             "properties": {
213                 "user": {
214                     "type": "string"
215                 },
216                 "password": {
217                     "type": "string"
218                 },
219                 "host": {
220                     "type": "string"
221                 },
222                 "port": {
223                     "type": "number"
224                 },
225                 "dbname": {
226                     "type": "string",
227                     "title": "Database Name"
228                 },
229                 "sslmode": {
230                    "type": "string",
231                    "title": "SSL Mode",
232                    "default": "prefer"
233                 }
234             },
235             "order": ['host', 'port', 'user', 'password'],
236             "required": ["dbname", "user", "password", "host", "port"],
237             "secret": ["password"]
238         }
239 
240     def _get_tables(self, schema):
241         # Use svv_columns to include internal & external (Spectrum) tables and views data for Redshift
242         # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html
243         # Use PG_GET_LATE_BINDING_VIEW_COLS to include schema for late binding views data for Redshift
244         # https://docs.aws.amazon.com/redshift/latest/dg/PG_GET_LATE_BINDING_VIEW_COLS.html
245         # Use HAS_SCHEMA_PRIVILEGE(), SVV_EXTERNAL_SCHEMAS and HAS_TABLE_PRIVILEGE() to filter
246         # out tables the current user cannot access.
247         # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_SCHEMA_PRIVILEGE.html
248         # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_SCHEMAS.html
249         # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html
250         query = """
251         WITH tables AS (
252             SELECT DISTINCT table_name,
253                             table_schema,
254                             column_name,
255                             ordinal_position AS pos
256             FROM svv_columns
257             WHERE table_schema NOT IN ('pg_internal','pg_catalog','information_schema')
258             UNION ALL
259             SELECT DISTINCT view_name::varchar AS table_name,
260                             view_schema::varchar AS table_schema,
261                             col_name::varchar AS column_name,
262                             col_num AS pos
263             FROM pg_get_late_binding_view_cols()
264                  cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
265         )
266         SELECT table_name, table_schema, column_name
267         FROM tables
268         WHERE
269             HAS_SCHEMA_PRIVILEGE(table_schema, 'USAGE') AND
270             (
271                 table_schema IN (SELECT schemaname FROM SVV_EXTERNAL_SCHEMAS) OR
272                 HAS_TABLE_PRIVILEGE('"' || table_schema || '"."' || table_name || '"', 'SELECT')
273             )
274         ORDER BY table_name, pos
275         """
276 
277         self._get_definitions(schema, query)
278 
279         return schema.values()
280 
281 
282 class CockroachDB(PostgreSQL):
283 
284     @classmethod
285     def type(cls):
286         return "cockroach"
287 
288 register(PostgreSQL)
289 register(Redshift)
290 register(CockroachDB)
291
```
--- 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/redash/query_runner/pg.py b/redash/query_runner/pg.py
--- a/redash/query_runner/pg.py
+++ b/redash/query_runner/pg.py
@@ -3,9 +3,10 @@
 import select
 
 import psycopg2
+from psycopg2.extras import Range
 
 from redash.query_runner import *
-from redash.utils import json_dumps, json_loads
+from redash.utils import JSONEncoder, json_dumps, json_loads
 
 logger = logging.getLogger(__name__)
 
@@ -28,6 +29,26 @@
 }
 
 
+class PostgreSQLJSONEncoder(JSONEncoder):
+    def default(self, o):
+        if isinstance(o, Range):
+            # From: https://github.com/psycopg/psycopg2/pull/779
+            if o._bounds is None:
+                return ''
+
+            items = [
+                o._bounds[0],
+                str(o._lower),
+                ', ',
+                str(o._upper),
+                o._bounds[1]
+            ]
+
+            return ''.join(items)
+
+        return super(PostgreSQLJSONEncoder, self).default(o)
+
+
 def _wait(conn, timeout=None):
     while 1:
         try:
@@ -165,7 +186,7 @@
 
                 data = {'columns': columns, 'rows': rows}
                 error = None
-                json_data = json_dumps(data, ignore_nan=True)
+                json_data = json_dumps(data, ignore_nan=True, cls=PostgreSQLJSONEncoder)
             else:
                 error = 'Query completed but it returned no data.'
                 json_data = None

{"golden_diff": "diff --git a/redash/query_runner/pg.py b/redash/query_runner/pg.py\n--- a/redash/query_runner/pg.py\n+++ b/redash/query_runner/pg.py\n@@ -3,9 +3,10 @@\n import select\n \n import psycopg2\n+from psycopg2.extras import Range\n \n from redash.query_runner import *\n-from redash.utils import json_dumps, json_loads\n+from redash.utils import JSONEncoder, json_dumps, json_loads\n \n logger = logging.getLogger(__name__)\n \n@@ -28,6 +29,26 @@\n }\n \n \n+class PostgreSQLJSONEncoder(JSONEncoder):\n+    def default(self, o):\n+        if isinstance(o, Range):\n+            # From: https://github.com/psycopg/psycopg2/pull/779\n+            if o._bounds is None:\n+                return ''\n+\n+            items = [\n+                o._bounds[0],\n+                str(o._lower),\n+                ', ',\n+                str(o._upper),\n+                o._bounds[1]\n+            ]\n+\n+            return ''.join(items)\n+\n+        return super(PostgreSQLJSONEncoder, self).default(o)\n+\n+\n def _wait(conn, timeout=None):\n     while 1:\n         try:\n@@ -165,7 +186,7 @@\n \n                 data = {'columns': columns, 'rows': rows}\n                 error = None\n-                json_data = json_dumps(data, ignore_nan=True)\n+                json_data = json_dumps(data, ignore_nan=True, cls=PostgreSQLJSONEncoder)\n             else:\n                 error = 'Query completed but it returned no data.'\n                 json_data = None\n", "issue": "PostgreSQL queries returning a tsrange fail\n### Issue Summary\r\n\r\nRunning a query which includes a tsrange column fail with a JSON error like this:\r\n\r\n```\r\nDateTimeRange(datetime.datetime(2016, 8, 25, 15, 21, 45, 760521), datetime.datetime(2016, 10, 14, 5, 56, 52, 452297), '[)') is not JSON serializable\r\n```\r\n\r\n### Steps to Reproduce\r\n\r\n1. Create a table with a tsrange column\r\n2. Insert some data into it\r\n3. Create a `SELECT * FROM table` query\r\n4. Run the query\r\n\r\nThis is turning out to be confusing for our users - I'm getting regular complaints about queries failing with this error because some of our tables have range columns.\r\n\r\n### Technical details:\r\n\r\n* Redash Version: 1.0.1+b2845\r\n* Browser/OS: Linux\r\n* How did you install Redash: Docker image\r\n\n", "before_files": [{"content": "import os\nimport logging\nimport select\n\nimport psycopg2\n\nfrom redash.query_runner import *\nfrom redash.utils import json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntypes_map = {\n    20: TYPE_INTEGER,\n    21: TYPE_INTEGER,\n    23: TYPE_INTEGER,\n    700: TYPE_FLOAT,\n    1700: TYPE_FLOAT,\n    701: TYPE_FLOAT,\n    16: TYPE_BOOLEAN,\n    1082: TYPE_DATE,\n    1114: TYPE_DATETIME,\n    1184: TYPE_DATETIME,\n    1014: TYPE_STRING,\n    1015: TYPE_STRING,\n    1008: TYPE_STRING,\n    1009: TYPE_STRING,\n    2951: TYPE_STRING\n}\n\n\ndef _wait(conn, timeout=None):\n    while 1:\n        try:\n            state = conn.poll()\n            if state == psycopg2.extensions.POLL_OK:\n                break\n            elif state == psycopg2.extensions.POLL_WRITE:\n                select.select([], [conn.fileno()], [], timeout)\n            elif state == psycopg2.extensions.POLL_READ:\n                select.select([conn.fileno()], [], [], timeout)\n            else:\n                raise psycopg2.OperationalError(\"poll() returned %s\" % state)\n        except select.error:\n            raise psycopg2.OperationalError(\"select.error received\")\n\n\nclass PostgreSQL(BaseSQLQueryRunner):\n    noop_query = \"SELECT 1\"\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            \"type\": \"object\",\n            \"properties\": {\n                \"user\": {\n                    \"type\": \"string\"\n                },\n                \"password\": {\n                    \"type\": \"string\"\n                },\n                \"host\": {\n                    \"type\": \"string\",\n                    \"default\": \"127.0.0.1\"\n                },\n                \"port\": {\n                    \"type\": \"number\",\n                    \"default\": 5432\n                },\n                \"dbname\": {\n                    \"type\": \"string\",\n                    \"title\": \"Database Name\"\n                },\n                \"sslmode\": {\n                   \"type\": \"string\",\n                   \"title\": \"SSL Mode\",\n                   \"default\": \"prefer\"\n                }\n            },\n            \"order\": ['host', 'port', 'user', 'password'],\n            \"required\": [\"dbname\"],\n            \"secret\": [\"password\"]\n        }\n\n    @classmethod\n    def type(cls):\n        return \"pg\"\n\n    def _get_definitions(self, schema, query):\n        results, error = self.run_query(query, None)\n\n        if error is not None:\n            raise Exception(\"Failed getting schema.\")\n\n        results = json_loads(results)\n\n        for row in results['rows']:\n            if row['table_schema'] != 'public':\n                table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])\n            else:\n                table_name = row['table_name']\n\n            if table_name not in schema:\n                schema[table_name] = {'name': table_name, 'columns': []}\n\n            schema[table_name]['columns'].append(row['column_name'])\n\n    def _get_tables(self, schema):\n        '''\n        relkind constants per https://www.postgresql.org/docs/10/static/catalog-pg-class.html\n        r = regular table\n        v = view\n        m = materialized view\n        f = foreign table\n        p = partitioned table (new in 10)\n        ---\n        i = index\n        S = sequence\n        t = TOAST table\n        c = composite type\n        '''\n\n        query = \"\"\"\n        SELECT s.nspname as table_schema,\n               c.relname as table_name,\n               a.attname as column_name\n        FROM pg_class c\n        JOIN pg_namespace s\n        ON c.relnamespace = s.oid\n        AND s.nspname NOT IN ('pg_catalog', 'information_schema')\n        JOIN pg_attribute a\n        ON a.attrelid = c.oid\n        AND a.attnum > 0\n        AND NOT a.attisdropped\n        WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')\n        \"\"\"\n\n        self._get_definitions(schema, query)\n\n        return schema.values()\n\n    def _get_connection(self):\n        connection = psycopg2.connect(user=self.configuration.get('user'),\n                                      password=self.configuration.get('password'),\n                                      host=self.configuration.get('host'),\n                                      port=self.configuration.get('port'),\n                                      dbname=self.configuration.get('dbname'),\n                                      sslmode=self.configuration.get('sslmode'),\n                                      async_=True)\n\n        return connection\n\n    def run_query(self, query, user):\n        connection = self._get_connection()\n        _wait(connection, timeout=10)\n\n        cursor = connection.cursor()\n\n        try:\n            cursor.execute(query)\n            _wait(connection)\n\n            if cursor.description is not None:\n                columns = self.fetch_columns([(i[0], types_map.get(i[1], None)) for i in cursor.description])\n                rows = [dict(zip((c['name'] for c in columns), row)) for row in cursor]\n\n                data = {'columns': columns, 'rows': rows}\n                error = None\n                json_data = json_dumps(data, ignore_nan=True)\n            else:\n                error = 'Query completed but it returned no data.'\n                json_data = None\n        except (select.error, OSError) as e:\n            error = \"Query interrupted. Please retry.\"\n            json_data = None\n        except psycopg2.DatabaseError as e:\n            error = e.message\n            json_data = None\n        except (KeyboardInterrupt, InterruptException):\n            connection.cancel()\n            error = \"Query cancelled by user.\"\n            json_data = None\n        finally:\n            connection.close()\n\n        return json_data, error\n\n\nclass Redshift(PostgreSQL):\n    @classmethod\n    def type(cls):\n        return \"redshift\"\n\n    def _get_connection(self):\n        sslrootcert_path = os.path.join(os.path.dirname(__file__), './files/redshift-ca-bundle.crt')\n\n        connection = psycopg2.connect(user=self.configuration.get('user'),\n                                      password=self.configuration.get('password'),\n                                      host=self.configuration.get('host'),\n                                      port=self.configuration.get('port'),\n                                      dbname=self.configuration.get('dbname'),\n                                      sslmode=self.configuration.get('sslmode', 'prefer'),\n                                      sslrootcert=sslrootcert_path,\n                                      async_=True)\n\n        return connection\n\n    @classmethod\n    def configuration_schema(cls):\n\n        return {\n            \"type\": \"object\",\n            \"properties\": {\n                \"user\": {\n                    \"type\": \"string\"\n                },\n                \"password\": {\n                    \"type\": \"string\"\n                },\n                \"host\": {\n                    \"type\": \"string\"\n                },\n                \"port\": {\n                    \"type\": \"number\"\n                },\n                \"dbname\": {\n                    \"type\": \"string\",\n                    \"title\": \"Database Name\"\n                },\n                \"sslmode\": {\n                   \"type\": \"string\",\n                   \"title\": \"SSL Mode\",\n                   \"default\": \"prefer\"\n                }\n            },\n            \"order\": ['host', 'port', 'user', 'password'],\n            \"required\": [\"dbname\", \"user\", \"password\", \"host\", \"port\"],\n            \"secret\": [\"password\"]\n        }\n\n    def _get_tables(self, schema):\n        # Use svv_columns to include internal & external (Spectrum) tables and views data for Redshift\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html\n        # Use PG_GET_LATE_BINDING_VIEW_COLS to include schema for late binding views data for Redshift\n        # https://docs.aws.amazon.com/redshift/latest/dg/PG_GET_LATE_BINDING_VIEW_COLS.html\n        # Use HAS_SCHEMA_PRIVILEGE(), SVV_EXTERNAL_SCHEMAS and HAS_TABLE_PRIVILEGE() to filter\n        # out tables the current user cannot access.\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_SCHEMA_PRIVILEGE.html\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_SCHEMAS.html\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html\n        query = \"\"\"\n        WITH tables AS (\n            SELECT DISTINCT table_name,\n                            table_schema,\n                            column_name,\n                            ordinal_position AS pos\n            FROM svv_columns\n            WHERE table_schema NOT IN ('pg_internal','pg_catalog','information_schema')\n            UNION ALL\n            SELECT DISTINCT view_name::varchar AS table_name,\n                            view_schema::varchar AS table_schema,\n                            col_name::varchar AS column_name,\n                            col_num AS pos\n            FROM pg_get_late_binding_view_cols()\n                 cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)\n        )\n        SELECT table_name, table_schema, column_name\n        FROM tables\n        WHERE\n            HAS_SCHEMA_PRIVILEGE(table_schema, 'USAGE') AND\n            (\n                table_schema IN (SELECT schemaname FROM SVV_EXTERNAL_SCHEMAS) OR\n                HAS_TABLE_PRIVILEGE('\"' || table_schema || '\".\"' || table_name || '\"', 'SELECT')\n            )\n        ORDER BY table_name, pos\n        \"\"\"\n\n        self._get_definitions(schema, query)\n\n        return schema.values()\n\n\nclass CockroachDB(PostgreSQL):\n\n    @classmethod\n    def type(cls):\n        return \"cockroach\"\n\nregister(PostgreSQL)\nregister(Redshift)\nregister(CockroachDB)\n", "path": "redash/query_runner/pg.py"}], "after_files": [{"content": "import os\nimport logging\nimport select\n\nimport psycopg2\nfrom psycopg2.extras import Range\n\nfrom redash.query_runner import *\nfrom redash.utils import JSONEncoder, json_dumps, json_loads\n\nlogger = logging.getLogger(__name__)\n\ntypes_map = {\n    20: TYPE_INTEGER,\n    21: TYPE_INTEGER,\n    23: TYPE_INTEGER,\n    700: TYPE_FLOAT,\n    1700: TYPE_FLOAT,\n    701: TYPE_FLOAT,\n    16: TYPE_BOOLEAN,\n    1082: TYPE_DATE,\n    1114: TYPE_DATETIME,\n    1184: TYPE_DATETIME,\n    1014: TYPE_STRING,\n    1015: TYPE_STRING,\n    1008: TYPE_STRING,\n    1009: TYPE_STRING,\n    2951: TYPE_STRING\n}\n\n\nclass PostgreSQLJSONEncoder(JSONEncoder):\n    def default(self, o):\n        if isinstance(o, Range):\n            # From: https://github.com/psycopg/psycopg2/pull/779\n            if o._bounds is None:\n                return ''\n\n            items = [\n                o._bounds[0],\n                str(o._lower),\n                ', ',\n                str(o._upper),\n                o._bounds[1]\n            ]\n\n            return ''.join(items)\n\n        return super(PostgreSQLJSONEncoder, self).default(o)\n\n\ndef _wait(conn, timeout=None):\n    while 1:\n        try:\n            state = conn.poll()\n            if state == psycopg2.extensions.POLL_OK:\n                break\n            elif state == psycopg2.extensions.POLL_WRITE:\n                select.select([], [conn.fileno()], [], timeout)\n            elif state == psycopg2.extensions.POLL_READ:\n                select.select([conn.fileno()], [], [], timeout)\n            else:\n                raise psycopg2.OperationalError(\"poll() returned %s\" % state)\n        except select.error:\n            raise psycopg2.OperationalError(\"select.error received\")\n\n\nclass PostgreSQL(BaseSQLQueryRunner):\n    noop_query = \"SELECT 1\"\n\n    @classmethod\n    def configuration_schema(cls):\n        return {\n            \"type\": \"object\",\n            \"properties\": {\n                \"user\": {\n                    \"type\": \"string\"\n                },\n                \"password\": {\n                    \"type\": \"string\"\n                },\n                \"host\": {\n                    \"type\": \"string\",\n                    \"default\": \"127.0.0.1\"\n                },\n                \"port\": {\n                    \"type\": \"number\",\n                    \"default\": 5432\n                },\n                \"dbname\": {\n                    \"type\": \"string\",\n                    \"title\": \"Database Name\"\n                },\n                \"sslmode\": {\n                   \"type\": \"string\",\n                   \"title\": \"SSL Mode\",\n                   \"default\": \"prefer\"\n                }\n            },\n            \"order\": ['host', 'port', 'user', 'password'],\n            \"required\": [\"dbname\"],\n            \"secret\": [\"password\"]\n        }\n\n    @classmethod\n    def type(cls):\n        return \"pg\"\n\n    def _get_definitions(self, schema, query):\n        results, error = self.run_query(query, None)\n\n        if error is not None:\n            raise Exception(\"Failed getting schema.\")\n\n        results = json_loads(results)\n\n        for row in results['rows']:\n            if row['table_schema'] != 'public':\n                table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])\n            else:\n                table_name = row['table_name']\n\n            if table_name not in schema:\n                schema[table_name] = {'name': table_name, 'columns': []}\n\n            schema[table_name]['columns'].append(row['column_name'])\n\n    def _get_tables(self, schema):\n        '''\n        relkind constants per https://www.postgresql.org/docs/10/static/catalog-pg-class.html\n        r = regular table\n        v = view\n        m = materialized view\n        f = foreign table\n        p = partitioned table (new in 10)\n        ---\n        i = index\n        S = sequence\n        t = TOAST table\n        c = composite type\n        '''\n\n        query = \"\"\"\n        SELECT s.nspname as table_schema,\n               c.relname as table_name,\n               a.attname as column_name\n        FROM pg_class c\n        JOIN pg_namespace s\n        ON c.relnamespace = s.oid\n        AND s.nspname NOT IN ('pg_catalog', 'information_schema')\n        JOIN pg_attribute a\n        ON a.attrelid = c.oid\n        AND a.attnum > 0\n        AND NOT a.attisdropped\n        WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')\n        \"\"\"\n\n        self._get_definitions(schema, query)\n\n        return schema.values()\n\n    def _get_connection(self):\n        connection = psycopg2.connect(user=self.configuration.get('user'),\n                                      password=self.configuration.get('password'),\n                                      host=self.configuration.get('host'),\n                                      port=self.configuration.get('port'),\n                                      dbname=self.configuration.get('dbname'),\n                                      sslmode=self.configuration.get('sslmode'),\n                                      async_=True)\n\n        return connection\n\n    def run_query(self, query, user):\n        connection = self._get_connection()\n        _wait(connection, timeout=10)\n\n        cursor = connection.cursor()\n\n        try:\n            cursor.execute(query)\n            _wait(connection)\n\n            if cursor.description is not None:\n                columns = self.fetch_columns([(i[0], types_map.get(i[1], None)) for i in cursor.description])\n                rows = [dict(zip((c['name'] for c in columns), row)) for row in cursor]\n\n                data = {'columns': columns, 'rows': rows}\n                error = None\n                json_data = json_dumps(data, ignore_nan=True, cls=PostgreSQLJSONEncoder)\n            else:\n                error = 'Query completed but it returned no data.'\n                json_data = None\n        except (select.error, OSError) as e:\n            error = \"Query interrupted. Please retry.\"\n            json_data = None\n        except psycopg2.DatabaseError as e:\n            error = e.message\n            json_data = None\n        except (KeyboardInterrupt, InterruptException):\n            connection.cancel()\n            error = \"Query cancelled by user.\"\n            json_data = None\n        finally:\n            connection.close()\n\n        return json_data, error\n\n\nclass Redshift(PostgreSQL):\n    @classmethod\n    def type(cls):\n        return \"redshift\"\n\n    def _get_connection(self):\n        sslrootcert_path = os.path.join(os.path.dirname(__file__), './files/redshift-ca-bundle.crt')\n\n        connection = psycopg2.connect(user=self.configuration.get('user'),\n                                      password=self.configuration.get('password'),\n                                      host=self.configuration.get('host'),\n                                      port=self.configuration.get('port'),\n                                      dbname=self.configuration.get('dbname'),\n                                      sslmode=self.configuration.get('sslmode', 'prefer'),\n                                      sslrootcert=sslrootcert_path,\n                                      async_=True)\n\n        return connection\n\n    @classmethod\n    def configuration_schema(cls):\n\n        return {\n            \"type\": \"object\",\n            \"properties\": {\n                \"user\": {\n                    \"type\": \"string\"\n                },\n                \"password\": {\n                    \"type\": \"string\"\n                },\n                \"host\": {\n                    \"type\": \"string\"\n                },\n                \"port\": {\n                    \"type\": \"number\"\n                },\n                \"dbname\": {\n                    \"type\": \"string\",\n                    \"title\": \"Database Name\"\n                },\n                \"sslmode\": {\n                   \"type\": \"string\",\n                   \"title\": \"SSL Mode\",\n                   \"default\": \"prefer\"\n                }\n            },\n            \"order\": ['host', 'port', 'user', 'password'],\n            \"required\": [\"dbname\", \"user\", \"password\", \"host\", \"port\"],\n            \"secret\": [\"password\"]\n        }\n\n    def _get_tables(self, schema):\n        # Use svv_columns to include internal & external (Spectrum) tables and views data for Redshift\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html\n        # Use PG_GET_LATE_BINDING_VIEW_COLS to include schema for late binding views data for Redshift\n        # https://docs.aws.amazon.com/redshift/latest/dg/PG_GET_LATE_BINDING_VIEW_COLS.html\n        # Use HAS_SCHEMA_PRIVILEGE(), SVV_EXTERNAL_SCHEMAS and HAS_TABLE_PRIVILEGE() to filter\n        # out tables the current user cannot access.\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_SCHEMA_PRIVILEGE.html\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_SCHEMAS.html\n        # https://docs.aws.amazon.com/redshift/latest/dg/r_HAS_TABLE_PRIVILEGE.html\n        query = \"\"\"\n        WITH tables AS (\n            SELECT DISTINCT table_name,\n                            table_schema,\n                            column_name,\n                            ordinal_position AS pos\n            FROM svv_columns\n            WHERE table_schema NOT IN ('pg_internal','pg_catalog','information_schema')\n            UNION ALL\n            SELECT DISTINCT view_name::varchar AS table_name,\n                            view_schema::varchar AS table_schema,\n                            col_name::varchar AS column_name,\n                            col_num AS pos\n            FROM pg_get_late_binding_view_cols()\n                 cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)\n        )\n        SELECT table_name, table_schema, column_name\n        FROM tables\n        WHERE\n            HAS_SCHEMA_PRIVILEGE(table_schema, 'USAGE') AND\n            (\n                table_schema IN (SELECT schemaname FROM SVV_EXTERNAL_SCHEMAS) OR\n                HAS_TABLE_PRIVILEGE('\"' || table_schema || '\".\"' || table_name || '\"', 'SELECT')\n            )\n        ORDER BY table_name, pos\n        \"\"\"\n\n        self._get_definitions(schema, query)\n\n        return schema.values()\n\n\nclass CockroachDB(PostgreSQL):\n\n    @classmethod\n    def type(cls):\n        return \"cockroach\"\n\nregister(PostgreSQL)\nregister(Redshift)\nregister(CockroachDB)\n", "path": "redash/query_runner/pg.py"}]}