Set timeout for idle transactions at session level

[goncalo-oliveira]

On some managed databases, the timeout for idle transactions is relatively low (for the scope of the work that osm2pgsql does). On Neon, for example, the timeout value for idle transactions is 5 minutes.

When we are processing a large file, it's very easy for this timeout to trigger. On Neon this will lead to something like

[CRITICAL]: Exception during execution: SSL connection has been closed unexpectedly

To workaround this, we can change the timeout value for the role used by osm2pgsql

ALTER ROLE role_name SET idle_in_transaction_session_timeout='1200s';

However, I think it would be nice if this was done automatically by osm2pgsql at the session level.

[joto]

Generally osm2pgsql should not hold open connections for a long time and the situation should have improved with changes in recent versions. This goes double for sessions that have a transaction open. Open sessions use ressources while they are open, but more importantly open transactions can lead to bloat.

I think we should first try to get the root cause solved in the application or at least try to figure out how long we need this timeout to be before setting this. It would help if you can give us more info about the situation where this happens, i.e. which connection is the problem. If you enable debug logging, you'll hopefully get more information on when this happens.

[goncalo-oliveira]

I mentioned this on a different discussion, before I knew what was causing the issue. I'll repost the logs. Hope it helps... if needed, I could do further testing on a separate instance.

osm2pgsql-replication update -v 
2024-12-09 11:41:17 [INFO]: Using replication service 'https://download.geofabrik.de/europe/united-kingdom-updates'.
2024-12-09 11:41:17 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:41:17 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/state.txt HTTP/1.1" 200 123
2024-12-09 11:41:17 [DEBUG]: Applying 3 sequence(s) (382 → 379), covering 2 day(s) 23 hour(s) 59 minute(s) 27 second(s) (259167 sec) of changes (2024-12-05T21:21:18Z → 2024-12-08T21:20:45Z)
2024-12-09 11:41:17 [DEBUG]: Calling osm2pgsql with: /usr/bin/osm2pgsql --append --slim --prefix planet_osm /tmp/tmpiveprhuw/osm2pgsql_diff.osc.gz
2024-12-09 11:41:17 [DEBUG]: Importing from sequence 379
2024-12-09 11:41:17 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:41:17 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/state.txt HTTP/1.1" 200 123
2024-12-09 11:41:17 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:41:18 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/000/000/380.osc.gz HTTP/1.1" 200 2547392
2024-12-09 11:41:18 [DEBUG]: Downloaded change 380. (501038 kB available in download buffer)
2024-12-09 11:41:18 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:41:18 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/000/000/381.osc.gz HTTP/1.1" 200 2412863
2024-12-09 11:41:18 [DEBUG]: Downloaded change 381. (490515 kB available in download buffer)
2024-12-09 11:41:18 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:41:19 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/000/000/382.osc.gz HTTP/1.1" 200 2320764
2024-12-09 11:41:19 [DEBUG]: Downloaded change 382. (480543 kB available in download buffer)
2024-12-09 11:41:20  osm2pgsql version 2.0.1
2024-12-09 11:41:20  Database version: 17.2
2024-12-09 11:41:20  PostGIS version: 3.5
2024-12-09 11:41:20  Loading properties from table '"public"."osm2pgsql_properties"'.
2024-12-09 11:41:21  Not using flat node file (same as on import).
2024-12-09 11:41:21  Using output 'flex' (same as on import).
2024-12-09 11:41:21  Using style file '/data/geocoder.lua' (same as on import).
2024-12-09 11:41:21  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-12-09 11:41:22  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-12-09 11:49:31  Reading input files done in 489s (8m 9s).                            
2024-12-09 11:49:31    Processed 234592 nodes in 40s - 6k/s
2024-12-09 11:49:31    Processed 50206 ways in 398s (6m 38s) - 126/s
2024-12-09 11:49:31    Processed 851 relations in 51s - 17/s
2024-12-09 11:49:35  Going over 10276 pending ways (using 4 threads)
Left to process: 0.......
2024-12-09 11:51:35  Processing 10276 pending ways took 119s (1m 59s) at a rate of 86.35/s
2024-12-09 11:51:35  Going over 2639 pending relations (using 4 threads)
Left to process: 0......
2024-12-09 11:53:17  Processing 2639 pending relations took 102s (1m 42s) at a rate of 25.87/s
2024-12-09 11:53:17  Skipping stage 1c for nodes (no marked nodes).
2024-12-09 11:53:17  Skipping stage 1c for ways (no marked ways).
2024-12-09 11:53:17  No marked nodes or ways (Skipping stage 2).
2024-12-09 11:53:17  Done postprocessing on table 'planet_osm_nodes' in 0s
2024-12-09 11:53:17  Done postprocessing on table 'planet_osm_ways' in 0s
2024-12-09 11:53:17  Done postprocessing on table 'planet_osm_rels' in 0s
2024-12-09 11:53:17  All postprocessing on table 'boundaries' done in 0s.
2024-12-09 11:53:17  All postprocessing on table 'roads' done in 0s.
2024-12-09 11:53:17  All postprocessing on table 'addrs' done in 0s.
2024-12-09 11:53:17  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-12-09 11:53:17  osm2pgsql took 717s (11m 57s) overall.
2024-12-09 11:53:17 [DEBUG]: Starting new HTTPS connection (1): download.geofabrik.de:443
2024-12-09 11:53:17 [DEBUG]: https://download.geofabrik.de:443 "GET /europe/united-kingdom-updates/000/000/382.state.txt HTTP/1.1" 200 123
2024-12-09 11:53:17 [CRITICAL]: Exception during execution: SSL connection has been closed unexpectedly

[joto]

Ah, this is about the replication issue again. You should have said so from the beginning. We are aware of that issue. Closing as duplicate of #2273.

[goncalo-oliveira]

Yes, apologies for not being clear, but since the other issue was a Q/A and contained more than one issue, I thought it would be best to separate the issues and categorize them as Idea.