Re: Gratuitous use of savepoint considered silly, if not harmful

On Fri, Apr 13, 2018 at 2:32 PM, Jerry Sievers <gsievers19(at)comcast(dot)net>
wrote:

> So I'm (was) puzzled here when a big warehouse system just upgraded to
> 9.6 which I knew does only a few 100k *real* transactions/day was
> wrapping txid_current() so fast, in turn causing some big, nasty tablesl
> to age and then require painful long-running vacuums...
>
> Got the brilliant idea to full statement log for just 20 minutes or so
> and then do some digging.
>
> OMG my DW team's Pentaho/Kettle driver gizmo emulates single-line
> autocommit inserts using savepoints.
>
> It racked up ~1.8M txids meanwhile actually doing only ~900
> transactions.
>
> Symptomatic of this also is that your pg_stat_database.(xact_commit +
> xact_rollback) counter over time will be wildly smaller then the
> advancement of txid_current() perhaps helping in the confusion.
>
> This was csvlogged and field #8 is command-tag which we're pulling out
>
> and summarizing as seen below.
>
> tmp$ grep ^2018 $log-file | grep silly_etl_user | cut -d, -f8 | sort |
> uniq -c | sort -k1,1bnr -k2
>
> 1880283 "INSERT"
> 1879931 "RELEASE"
> 1879931 "SAVEPOINT"
> 314838 "SELECT"
> 314298 "UPDATE"
> 2681 "idle"
> 2677 "authentication"
> 1967 "SET"
> 898 "COMMIT"
> 897 "BEGIN"
> 160 "DELETE"
> 83 "TRUNCATE TABLE"
> 6 "DROP TABLE"
> 2 "CREATE INDEX"
> 2 "CREATE TABLE AS"
>
> Anyway, I felt this was worth sharing :-)
>
> Thx
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres(dot)consulting(at)comcast(dot)net
> p: 312.241.7800
>
>
*As Forest Gump once said "Stupid is as stupid does". *😁

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!