config.show_chart = true fails with postgres

[davidtolsma]

PG::UndefinedFunction: ERROR: function unix_timestamp(timestamp without time zone) does not exist
SELECT FLOOR((UNIX_TIMESTAMP(created_at) - 1783457675) / 3600) ..

UNIX_TIMESTAMP(created_at)
What UNIX_TIMESTAMP() Does (MySQL)
In MySQL, UNIX_TIMESTAMP() converts a DATETIME or TIMESTAMP column into a Unix timestamp (number of seconds since 1970-01-01 00:00:00 UTC).
Example in MySQL:
SQLSELECT UNIX_TIMESTAMP(created_at) FROM jobs;
-- Returns something like: 1720458000
This is very commonly used for time-based calculations and bucketing (e.g., grouping records by hour or day).
Why It Fails in PostgreSQL
PostgreSQL does not have a function called UNIX_TIMESTAMP().
When solid_queue_monitor runs this query on your production PostgreSQL database:
SQLSELECT FLOOR((UNIX_TIMESTAMP(created_at) - 1783457675) / 3600) ...
PostgreSQL throws:
PG::UndefinedFunction: ERROR: function unix_timestamp(timestamp without time zone) does not exist
Correct Equivalent in PostgreSQL
In PostgreSQL, the proper way to get a Unix timestamp is:
SQLEXTRACT(EPOCH FROM created_at)
So the equivalent of the failing query would be:
SQL-- MySQL (broken on Postgres)
FLOOR((UNIX_TIMESTAMP(created_at) - 1783457675) / 3600)

-- PostgreSQL (correct)
FLOOR((EXTRACT(EPOCH FROM created_at) - 1783457675) / 3600)

[vishaltps]

@davidtolsma Thanks for the detailed report, the SQL analysis is spot on.

The chart service already emits PostgreSQL-native SQL (EXTRACT(EPOCH FROM ...)) when it detects a Postgres connection, so a standard single-database Postgres app doesn't hit this. The fact that you're seeing the MySQL form (UNIX_TIMESTAMP(...)) run against Postgres points to a specific setup: the gem currently detects the database adapter from your app's primary connection (ActiveRecord::Base.connection) rather than from the connection your Solid Queue tables actually live on.

That would explain the error if your app uses a multi-database configuration where Solid Queue runs on a separate database with a different engine than the primary, for example, a MySQL/Trilogy primary database plus a dedicated PostgreSQL queue database (Solid Queue's own docs recommend a dedicated queue DB via connects_to). In that case the gem builds MySQL SQL from the primary and runs it against the Postgres queue DB, producing exactly the unix_timestamp error you're seeing.

Could you confirm a couple of things so I can be sure I'm fixing the right root cause?

1. Is your app using multiple databases (a connects_to / separate queue database in database.yml)?
2. If so, what engine is your primary database vs. your Solid Queue / queue database?
3. Which version of solid_queue_monitor are you on?

If that matches, the fix is small and low-risk: detect the adapter from the Solid Queue model's own connection instead of the primary. It won't change behavior for single-database setups.

[davidtolsma]

1. using multiple databases. primary is Msql, secondary is Postgres. The environment config.solid_queue.connects_to = { database: { writing: :queue } }, not in the database.yml. I don't see a database.yml connects_to syntax.
2. primary Mysql, solid_queue postgres
3. Latest released (implemented a day or two ago)

[vishaltps]

@davidtolsma Thanks again for the detailed report and for confirming your setup , that made the root cause easy to pin down.

Root cause: the activity chart picked its SQL dialect from ActiveRecord::Base.connection (your app's primary MySQL database) rather than from the connection Solid Queue's tables actually live on. With a dedicated PostgreSQL queue database (config.solid_queue.connects_to = { database: { writing: :queue } }), it built MySQL UNIX_TIMESTAMP(...) SQL and ran it against Postgres — producing the PG::UndefinedFunction: function unix_timestamp(...) does not exist error you saw.

Fix: the dialect is now detected from the Solid Queue model's own connection, so mixed-engine multi-database setups (MySQL primary + PostgreSQL queue, and any other combination) generate the correct SQL. Single-database and same-engine setups are unaffected the generated SQL is byte-identical.

This shipped in v2.2.1 (PR #43):

Gemfile

gem "solid_queue_monitor", "~> 2.2.1"

Then bundle update solid_queue_monitor. No config changes or migrations needed.

Release notes: https://github.com/vishaltps/solid_queue_monitor/releases/tag/v2.2.1

Please give it a try and let me know if the chart renders correctly on your setup, happy to reopen if anything's still off. Thanks for helping improve the gem!