Hi , Please help today we migrated to v 0.54.2 version and now few queries are not running which are using pg_catalog schema.
Error :- ERROR: relation "pg_enum" does not exist
Query: - SELECT
userid,
usename,
DATE(starttime) AS execution_date,
query_id,
EXTRACT(EPOCH FROM (endtime - starttime)) AS execution_time_seconds Preformatted text
FROM
data_automation.querylogs_dump q
JOIN pg_catalog.pg_user pg ON q.userid = pg.usesysid
WHERE
date(starttime) >= current_date-7;
Hi there,
Could you please give us a bit more context so we can understand the problem better:
Logs:-
{:database_id 2,
:parameterized false,
:started_at #t "2025-04-19T08:52:22.273042989+05:30[Asia/Kolkata]",
:action_id nil,
:state "42P01",
:json_query
{:database 2,
:type "native",
:native
{:template-tags {},
:query
"WITH QueryLogs AS (\r\n SELECT\r\n userid,\r\n usename,\r\n DATE(starttime) AS execution_date,\r\n query_id,\r\n EXTRACT(EPOCH FROM (endtime - starttime)) AS execution_time_seconds -- NO AVG Here\r\n FROM\r\n data_automation.querylogs_dump q\r\n JOIN pg_user pg ON q.userid = pg.usesysid\r\n WHERE\r\n date(starttime) >= current_date-7\r\n),\r\nUserDayRank AS (\r\n SELECT\r\n userid,\r\n usename,\r\n execution_date,\r\n query_id,\r\n execution_time_seconds,\r\n RANK() OVER (PARTITION BY userid, execution_date ORDER BY execution_time_seconds DESC) AS daily_rank\r\n FROM\r\n QueryLogs\r\n),\r\nTop10UserDay AS (\r\n SELECT\r\n userid,\r\n usename,\r\n execution_date,\r\n query_id,\r\n execution_time_seconds\r\n FROM\r\n UserDayRank\r\n WHERE\r\n daily_rank <= 10\r\n),\r\nDailyAverageTop10 AS (\r\n SELECT\r\n userid,\r\n usename,\r\n execution_date,\r\n AVG(execution_time_seconds) AS avg_execution_time_seconds\r\n FROM\r\n Top10UserDay\r\n GROUP BY\r\n userid,\r\n usename,\r\n execution_date\r\n),\r\nUserFrequency AS (\r\n SELECT\r\n userid,\r\n COUNT(DISTINCT execution_date) AS execution_days\r\n FROM\r\n Top10UserDay -- Count DISTINCT DAYS, using the top 10\r\n GROUP BY\r\n userid\r\n HAVING\r\n COUNT(DISTINCT execution_date) >= 7\r\n)\r\nSELECT\r\n da.usename,\r\n da.execution_date,\r\n da.avg_execution_time_seconds\r\nFROM\r\n DailyAverageTop10 da\r\nJOIN\r\n UserFrequency uf ON da.userid = uf.userid\r\nORDER BY\r\n da.userid,\r\n da.execution_date;"},
:middleware {:js-int-to-string? true, :userland-query? true, :add-default-userland-constraints? true}},
:status :failed,
:class com.amazon.redshift.util.RedshiftException,
:stacktrace
["com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2648)"
"com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2295)"
"com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)"
"com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1878)"
"com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:375)"
"com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:521)"
"com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:442)"
"com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeWithFlags(RedshiftPreparedStatement.java:202)"
"com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeQuery(RedshiftPreparedStatement.java:117)"
"com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:743)"
"clojure.java.jdbc$execute_query_with_params.invokeStatic(jdbc.clj:1090)"
"clojure.java.jdbc$execute_query_with_params.invoke(jdbc.clj:1084)"
"clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1113)"
"clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093)"
"clojure.java.jdbc$query.invokeStatic(jdbc.clj:1182)"
"clojure.java.jdbc$query.invoke(jdbc.clj:1144)"
"clojure.java.jdbc$query.invokeStatic(jdbc.clj:1160)"
"clojure.java.jdbc$query.invoke(jdbc.clj:1144)"
"--> driver.postgres$enum_types.invokeStatic(postgres.clj:224)"
"driver.postgres$enum_types.invoke(postgres.clj:220)"
"driver.postgres$fn__94517.invokeStatic(postgres.clj:879)"
"driver.postgres$fn__94517.invoke(postgres...
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:52:27+05:30 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 4769ms (2 DB calls) App DB connections: 1/15 Jetty threads: 4/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued); redshift DB 2 connections: 0/12 (0 threads blocked) {:metabase-user-id 7}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:52:58+05:30 DEBUG metabase.server.middleware.log GET /api/collection/377 200 8ms (9 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:52:58+05:30 DEBUG metabase.server.middleware.log GET /api/collection/377/items 200 14ms (10 DB calls) App DB connections: 1/15 Jetty threads: 6/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:52:58+05:30 DEBUG metabase.server.middleware.log GET /api/collection/377/items 200 61ms (57 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:00+05:30 DEBUG metabase.server.middleware.log GET /api/collection/368 200 7ms (8 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:00+05:30 DEBUG metabase.server.middleware.log GET /api/collection/368/items 200 22ms (18 DB calls) App DB connections: 0/15 Jetty threads: 6/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:00+05:30 DEBUG metabase.server.middleware.log GET /api/collection/368/items 200 62ms (56 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:06+05:30 DEBUG metabase.server.middleware.log GET /api/card/3398 200 467ms (24 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:07+05:30 DEBUG metabase.server.middleware.log GET /api/card/3398/query_metadata 200 443ms (20 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:07+05:30 DEBUG metabase.server.middleware.log POST /api/card/3398/query 202 [ASYNC: completed] 61ms (25 DB calls) App DB connections: 1/15 Jetty threads: 5/50 (1 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued); redshift DB 2 connections: 0/3 (0 threads blocked) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:36+05:30 DEBUG metabase.server.middleware.log POST /api/card/3398/query 202 [ASYNC: completed] 18989ms (25 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (1 idle, 0 queued) (130 total active threads) Queries in flight: 1 (0 queued); redshift DB 2 connections: 0/3 (0 threads blocked) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:45+05:30 DEBUG metabase.server.middleware.log POST /api/card/3398/query/xlsx 200 [ASYNC: completed] 22876ms (18 DB calls) App DB connections: 0/15 Jetty threads: 4/50 (2 idle, 0 queued) (129 total active threads) Queries in flight: 0 (0 queued); redshift DB 2 connections: 1/3 (0 threads blocked) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:46+05:30 DEBUG metabase.server.middleware.log GET /api/session/properties 200 17ms (13 DB calls) App DB connections: 0/15 Jetty threads: 6/50 (1 idle, 0 queued) (129 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 7}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:46+05:30 DEBUG metabase.server.middleware.log GET /api/setting 200 32ms (16 DB calls) App DB connections: 0/15 Jetty threads: 6/50 (1 idle, 0 queued) (129 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 7}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:47+05:30 DEBUG metabase.server.middleware.log GET /api/setting 200 20ms (16 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (1 idle, 0 queued) (131 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 7}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:53:48+05:30 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 2ms (1 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (1 idle, 0 queued) (131 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 7}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:54:46+05:30 DEBUG metabase.server.middleware.log GET /api/card/2738 200 678ms (24 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (1 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:54:46+05:30 DEBUG metabase.server.middleware.log GET /api/card/2738/query_metadata 200 422ms (20 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (1 idle, 0 queued) (130 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:54:47+05:30 DEBUG metabase.server.middleware.log GET /api/collection/377 200 7ms (9 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (128 total active threads) Queries in flight: 0 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:54:49+05:30 DEBUG metabase.server.middleware.log GET /api/collection/179 200 6ms (8 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (129 total active threads) Queries in flight: 1 (0 queued) {:metabase-user-id 13371374}
[1b795d1b-80e0-41b8-a134-08476eb6532a] 2025-04-19T08:54:52+05:30 DEBUG metabase.server.middleware.log POST /api/card/2738/query 202 [ASYNC: completed] 4868ms (25 DB calls) App DB connections: 1/15 Jetty threads: 4/50 (2 idle, 0 queued) (129 total active threads) Queries in flight: 0 (0 queued); redshift DB 2 connections: 0/2 (0 threads blocked) {:metabase-user-id 13371374}
Thank you for reporting this. I managed to reproduce it and created a Github issue here, make sure to subscribe to it in case there are any news about the fix.
As a workaround, you can add "::text" after the usename column in your query (it should be enough to put it in the first WITH statement) and it should work normally. Can you try that out and let me know if it worked?
Thanks Marcos, It works.
