Re: psql is hanging

On 11/30/2018 10:38 AM, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)com> 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.