pg_locks table has lot of simple select statements

Question

we are connecting to our Postgresql (RDS) server from our django backend as well as lambda, sometimes django backend queries time out and I run the following query to see the locks:

                SELECT
                    pg_stat_activity.client_addr,
                    pg_stat_activity.query
                FROM pg_class
                JOIN
                    pg_locks ON pg_locks.relation = pg_class.oid
                JOIN
                    pg_stat_activity ON pg_locks.pid =
                    pg_stat_activity.pid
                WHERE
                    pg_locks.granted='t' AND
                    pg_class.relname='accounts_user'

This gives me 30 rows of simple select queries executed from lambda like this:

SELECT first_name, picture, username FROM accounts_user WHERE id = $1

why does this query hold a lock? should I be worried?

I'm using pg8000 library to connect from Lambda

    with pgsql.cursor() as cursor:
        cursor.execute(
            """
            SELECT first_name, picture, username
            FROM accounts_user
            WHERE id = %s
            """,
            (author_user_id,),
        )
        row = cursor.fetchone()
    # use the row ..

I opened an issue at Github maybe it's because I'm using the library wrong. https://github.com/tlocke/pg8000/issues/16

@LaurenzAlbe should I make SELECT * in the first query and paste the output? — EralpB, Jun 19 '19 at 11:16
Either that, or the complete `pg_locks` (if it is not too large). — Laurenz Albe, Jun 19 '19 at 11:56
@LaurenzAlbe thank you very much, I will definitely update, I'm not the DBA so don't have access so it will take some time! — EralpB, Jun 19 '19 at 16:38

score 0 · Answer 1 · answered Jun 19 '19 at 15:15

You can also try to reuse the database connection, see https://docs.djangoproject.com/en/2.2/ref/settings/#conn-max-age

    DATABASES = {
        'default': {
            ...
            'CONN_MAX_AGE': 600,             # reuse database connection
        }
    }

pg_locks table has lot of simple select statements

1 Answers1