Обсуждение: pg_exec commit causes extremely long delays
I have a TCL app which has severe delays - measured in tens of minutes at least - running "pg_exec $conn "commit". The delays are so long that I used to think the app was hopelessly frozen. By accident, I left the app alone in its frozen state and came back a good eal later and seen that it was running again. The application loops through an import file, reading one row at a time, and issues a bunch of inserts and updates to various tables. There's a simple pg_exec $conn "start transaction" at the beginning of the loop and the commit at the end. There are a minimum of four apps running at the same time, all reading different sections of the same table, all writing to the same db and the same tables. The other apps seem unaffected by the one app that freezes. Any thoughts on the cause and possible solutions would be appreciated. Carlo
I do something similiar, but I don't see these delays. Do you have more details about your circumstance? pgtcl version, PGversion, how big the table is, what kind of processing is being done in the script, etc. Have you tried simply debuggingin the script to see where the bottleneck may lay? For example, doing a simply puts with clock at various pointsin the script. --brett ----- Original Message ---- From: Carlo Stonebanks <stonec.register@sympatico.ca> To: pgsql-interfaces@postgresql.org Sent: Tuesday, October 24, 2006 6:10:55 PM Subject: [INTERFACES] pg_exec commit causes extremely long delays I have a TCL app which has severe delays - measured in tens of minutes at least - running "pg_exec $conn "commit". The delays are so long that I used to think the app was hopelessly frozen. By accident, I left the app alone in its frozen state and came back a good eal later and seen that it was running again. The application loops through an import file, reading one row at a time, and issues a bunch of inserts and updates to various tables. There's a simple pg_exec $conn "start transaction" at the beginning of the loop and the commit at the end. There are a minimum of four apps running at the same time, all reading different sections of the same table, all writing to the same db and the same tables. The other apps seem unaffected by the one app that freezes. Any thoughts on the cause and possible solutions would be appreciated. Carlo ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
>I do something similiar, but I don't see these delays. Do you have more >details about your circumstance? pgtcl version, PG version, how big the >table is, what kind of processing is being done in the script, etc. Have >you tried simply debugging in the script to see where the bottleneck may >lay? For example, doing a simply puts with clock at various points in the >script. The slowdown is nowhere in the script - it's caused waiting for a return from the call pg_exec $cn "commit". The table contains about 400K rows, typically with about 10 columns (this varies, as the nature of the import application is dynamic). pgTcl package appears to be 1.5 (assuming the package folder pgtcl1.5 refers to v1.5). The import program imports a "flattened" import file and normalises the columns to our enterprise database. All calls to pg_tcl functions are wrapped with custom tcl procs we wrote to put a layer of abstraction between the code and the sql calls. In fact, these wrap-around procs can swtich from pgtcl calls to spi calls so that procs can be re-used in pg server-side TCL SP''s. Included in these wrap-around functions is the ability to call logging functions which write to log files, telling me the execution taime and "explains" for every SQL call, if need be. If the slowdowns were within TCL code, I would expect Ctrl-C to abort the script, but in fact that app is unresponsive to Ctrl-C when the process stalls. PG version is Windows 8.1.2 Carlo