strange locks on PG 11 with Golang programs

On Mon, Mar 09, 2020 at 09:51:21AM +0100, Josef Machytka wrote:

We are lately experiencing very strange locks on PostgreSQL 11.7 when we
process ETL tasks using our programs in Go 1.13.8 using standard libraries
sql and pq.

ETL task has to rename tables but PostgreSQL shows that this simple
operation waits for Lock on relation and select from PG wiki (
https://wiki.postgresql.org/wiki/Lock_Monitoring) which should show
blocking process shows that command "alter table .... rename to ...." is
blocked by process "COPY .... to stdout" but from completely different
table.

And from time to time even selects from some tables seem to by blocked by
copy commands running on other tables. This is shown by the same select
from PG wiki for blocking queries.

All this stuff runs from golang programs. So maybe problem is not on
PostgreSQL but in golang libraries?

The query displayed is just the query currently executing, but if the
connection is in a transaction the problematic lock could have been acquired by
any previously executed query. Did you check in pg_stat_activity if the
connection is in a transaction (e.g. query_start != xact_start)?

On 9/3/20 10:51 π.μ., Josef Machytka wrote:

We are lately experiencing very strange locks on PostgreSQL 11.7 when we process ETL tasks using our programs in Go 1.13.8 using standard libraries sql and pq.

ETL task has to rename tables but PostgreSQL shows that this simple operation waits for Lock on relation and select from PG wiki (https://wiki.postgresql.org/wiki/Lock_Monitoring) which should show
blocking process shows that command "alter table .... rename to ...." is blocked by process "COPY .... to stdout" but from completely different table.

Fully review your programs for connection / xaction leaks. Do you use a connection pool? Make sure in the code that you close any connections that you open, even after exceptions , try to identify
idle in transaction connections from pg_stat_activity , remember to either commit or rollback non-autocommit connections (just for the sake of clarity).

And from time to time even selects from some tables seem to by blocked by copy commands running on other tables. This is shown by the same select from PG wiki for blocking queries.

All this stuff runs from golang programs. So maybe problem is not on PostgreSQL but in golang libraries?

Thank you for any suggestions

Josef Machytka

Berlin

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On Mon, 9 Mar 2020 at 09:58, Julien Rouhaud <rjuju123@gmail.com> wrote:

The query displayed is just the query currently executing, but if the
connection is in a transaction the problematic lock could have been
acquired by
any previously executed query. Did you check in pg_stat_activity if the
connection is in a transaction (e.g. query_start != xact_start)?

Oh, I see. Thank you. I modified query from wiki and it shows that blocking
session actually runs for ~12 hours already. Only last COPY command started
recently. So maybe golang library did not close session properly and reused
it in another completely different task? Although I defer db.Close()
everywhere...

On Mon, Mar 09, 2020 at 10:21:23AM +0100, Josef Machytka wrote:

On Mon, 9 Mar 2020 at 09:58, Julien Rouhaud <rjuju123@gmail.com> wrote:

The query displayed is just the query currently executing, but if the
connection is in a transaction the problematic lock could have been
acquired by
any previously executed query. Did you check in pg_stat_activity if the
connection is in a transaction (e.g. query_start != xact_start)?

Oh, I see. Thank you. I modified query from wiki and it shows that blocking
session actually runs for ~12 hours already. Only last COPY command started
recently. So maybe golang library did not close session properly and reused
it in another completely different task? Although I defer db.Close()
everywhere...

I don't know anything about golang libraries, but this sounds like a problem of
a simulated non-autocommit mode in the driver/lib. You should look at
transaction handling with the lib you're using.

On 2020-03-09 11:02:37 +0200, Achilleas Mantzios wrote:

Fully review your programs for connection / xaction leaks. Do you use a
connection pool?

Go's sql package encourages the use of connection pools (type DB) over
single connections (type Conn):

| Prefer running queries from DB unless there is a specific need for a
| continuous single database connection.

All tutorials I've seen follow this recommendation, so a Go programmer
might not even be aware that connections exist.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"