On 11/30/2018 10:38 AM, Tom Lane wrote:
Ron <ronljohnsonjr@xxxxxxxxx> writes:
We have a seemingly identical problem in v9.6 on our big prod databases. It
worked perfectly in 8.4 *and works perfectly in 9.6 our small staging servers*.
A giant sql script that drops the oldest child tables from a large set of
partitioned tables (by dropping all relevant triggers, indexes, functions
etc, dropping the oldest children, adding the new children then recreating
all the relevant triggers, indexes, functions etc).
A script like that is going to be taking exclusive locks on a whole lot
of tables. My guess is that you are running up against lock conflicts
against other activity in the DB, and that the "hang" is just waiting
for other transactions to release their table locks. Looking into
the pg_locks view could be informative.
We thought of that, but...
1. Even when that process is the only one touching the database, it hangs.
2. It worked perfectly on 8.4.
3. It works perfectly on 9.6 using the small staging database.
4. It works perfectly when running the script from psql.
Before that weekly job runs again, the application team is going to upgrade
to JDBC driver v42.2.5 (don't know what they're using now), and add
protocolVersion=2 to the connection string. I'll post an update next Wednesday.
--
Angular momentum makes the world go 'round.
