Seeing Queries in Postgres

[Alex Ezell]

We often do diagnostics on our PostgreSQL systems by looking at
currently running queries with some sql like this:

select procpid, to_char((now() - query_start), 'HH24:MI:SS.MS') as
query_time, client_addr as client_host, current_query
from pg_stat_activity
where current_query not ilike '<idle>'
order by query_time desc;

However, since we've moved to sqlalchemy, we've found that we can no
longer see the full text of the current_query because of all the
aliasing that sqlalchemy does in its select statements. Has anyone had
this issue or know of any workarounds whether they be sqlalchemy-based
or in postgres?

Sorry if this is completely off-topic. I'm just at a loss for where to
turn.

Thanks!

[Michael Bayer]

can you be more specific how using aliases in SELECT statements prevents
them from being seen ? do you mean that they're more difficult to read ?
or just they're too long ?

[Alex Ezell]

Sorry the double and top post, but please disregard. I've been
informed by a coworker that this is a PostgreSQL limitation on the
length of the current_query column and that thus far, the PostgreSQL
devs will not change it.

Sorry for the trouble.

/alex

[Alex Ezell]

They are too long to see the full query and PostgreSQL has a
limitation on the length of that current_query column that cannot be
overcome. This really isn't a sqlalchemy issue at all.

Thanks for the quick reply!

/alex

[Michael Bayer]

there is a label_length parameter you can pass to create_engine() which
will shrink the size of column labels. this can dramatically reduce the
size of the query. If you set it to any value below 6, you'll get labels
like:

select table.foo as _1, table.bar as _2

etc.

[Wichert Akkerman]

Previously Alex Ezell wrote:
>
> Sorry the double and top post, but please disregard. I've been
> informed by a coworker that this is a PostgreSQL limitation on the
> length of the current_query column and that thus far, the PostgreSQL
> devs will not change it.

Postgres has a log_statement configuration settings which you can use to
make it log all queries to its logfile. Perhaps that would be useful for
you?

Wichert.

--
Wichert Akkerman <wic...@wiggy.net> It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.