Logging DB statements becomes useless with Proletarian

[p-himik]

Recently made a switch to Proletarian and it made my overall setup noticeably simpler.

However, before I had been using log-statement=all in PostgreSQL during development, and with Proletarian it's completely useless because the logs are inundated with its messages.

I couldn't find a way have my cake and eat it too, at least not without some extensions, but maybe you know have some ideas?

[p-himik]

I have just been told that it should be possible to adjust logging settings on a per-user basis. Will try.

Update: won't work in my case - I'm using Heroku and their PG setup is quite limiting. Possible to create additional users via WUI or CLI but can't change their logging settings.

[msolli]

Yeah, this is a tough one. The polling really does flood the Postgres log.

In development I've worked around this by filtering the logs before viewing - I use https://github.com/p6spy/p6spy to log db statements to the application log, and then I exclude the Proletarian statements (using regexes). I guess you could do something similar on the raw log with tail -f <logfile> | grep -v …, but the default format logs statements over several lines, which makes regex filtering hard. Maybe logging with a different format could help?

In production (on RDS) I don't log statements (log-statement=none).

I'm going to dig a little more into this. It would be good to at least provide some operational advice for this in the readme.

[p-himik]

What do you think about LISTEN/NOTIFY as an alternative to polling?

[msolli]

Update: won't work in my case - I'm using Heroku and their PG setup is quite limiting. Possible to create additional users via WUI or CLI but can't change their logging settings.

It should be possible to change the log_statement parameter for a database or role (user) according to the docs:

ALTER DATABASE foo SET log_statement = 'mod';

or:

ALTER ROLE your_user SET log_statement = 'mod';

Does this work on Heroku, or have they tied down their Postgres too tight?

[msolli]

What do you think about LISTEN/NOTIFY as an alternative to polling?

I'm a little hesitant, because polling is conceptually and mechanically simpler.

Pro's for LISTEN/NOTIFY, though:

• The log flooding problem discussed here goes away.
• The wait time for when a job is processed is reduced iff the workers are idle.

[p-himik]

have they tied down their Postgres too tight?

Yes, that's what I meant by "can't change their logging settings". I can change logging of the whole database, but I'd rather have 'all' for the database and 'mod' for the stuff that Proletarian needs.

I'm a little hesitant, because polling is conceptually and mechanically simpler.

If you're open to the idea, I might have some time to create a PR at some point that replaces polling with notifying or adds notifying as an optional alternative.

[msolli]

I've created a discussion thread for LISTEN/NOTIFY. Let's try to get all the facts on the table there, and see if we can find a design that can be implemented.