sql_notes.txt

# retrieve the raw SQL statement with variable bindings from a SQLAlchemy query
# https://stackoverflowteams.com/c/alcemy/questions/63
# https://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression
from sqlalchemy.dialects import postgresql
print(str(q.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})))
# If that fails due to not finding a literal renderer, just use this:
print(str(q.statement.compile(dialect=postgresql.dialect())))

# Enable SQLAlchemy logging (can help diagnose eager/lazy loading and n+1 queries): put this in main.py:
logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)

# Create local postgres database:
sudo su - postgres
createdb cube_sidecar
createuser -d cube_sidecar_user
# may be necessary?
psql
grant all privileges on database cube_sidecar to cube_sidecar_user;
ALTER ROLE cube_sidecar_user WITH SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS;

# Debugging/timing long SQL queries with explain analyze:
EXPLAIN (ANALYZE, COSTS, BUFFERS, FORMAT JSON) <sql-query>
EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE, FORMAT JSON) <sql-query>
# Probably easiest to construct in psql/pgweb; it can be tricky to do in the code, although using the above trick to get the raw SQL and then manually substituting as necessary should work.
# Write the explain query to a file and then run with psql:
psql -XqAt -f explain.sql > analyze.json
# Paste the contents into https://explain.dalibo.com/

# Check delays in receiving batch data from PCP:
SELECT batch_start_datetime, (system_info->>'transmission_datetime')::timestamptz AS transmission_datetime, created_at, (system_info->>'transmission_datetime')::timestamptz - batch_start_datetime AS transmission_delay, created_at - (system_info->>'transmission_datetime')::timestamptz AS ingest_delay FROM batch WHERE slug LIKE 'rohrdorfer__%' AND created_at > '2025-05-13'

# Check delays in Emmy aggregation processing:
SELECT delivery.slug, delivery.created_at AS delivery_created, aggregated_delivery.created_at AS agg_created, aggregated_delivery.created_at - delivery.created_at AS processing_time FROM delivery JOIN aggregated_delivery ON delivery.slug = aggregated_delivery.slug WHERE delivery.slug LIKE 'rohrdorfer__%' AND delivery.created_at > '2025-05-13'

# Check current database activity (can get log queries):
SELECT * FROM pg_stat_activity;

# Kill a process:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = '8361';

# Run SQL directly in pycharm's Evaluate Expression box:
from sqlalchemy import text
session.execute(text("SELECT * FROM user_")).fetchall()