Re: Query cancellation on hot standby because of buffer pins - Mailing list pgsql-admin

It's largish:

              SELECT
                products.name                                         AS product,
                null                                                  AS branch_name,
                null                                                  AS credit_officer_name,
                COUNT(DISTINCT account_id)                            AS accounts,
                COUNT(DISTINCT client_id)                             AS clients,
                SUM(balance)                                          AS balance,
                ROUND((SUM(balance) / COUNT(DISTINCT account_id)), 2) AS averageBalance
              FROM deposit_accounts
                JOIN accounts ON deposit_accounts.account_id = accounts.id
                JOIN clients ON accounts.client_id = clients.id
                JOIN products ON accounts.product_id = products.id
                JOIN organisation_structure_nodes ON (clients.organisation_structure_node_id = organisation_structure_nodes.id)
              WHERE organisation_structure_nodes.tree_path <@ (SELECT tree_path FROM organisation_structure_nodes WHERE organisation_structure_nodes.id = $1)
                    AND organisation_structure_nodes.tree_path <@ (SELECT tree_path FROM user_top_visible_node_tree_paths_view WHERE user_id = $2)
                    AND accounts.status_id = 3 OR accounts.status_id = 4
              GROUP BY products.id
              ORDER BY product

 

It is already at zero. There was no vacuuming on the primary around that time. I've seen some other cancellations which were probably due to the vacuuming on the primary (even though hot_standby_feedback is set to on), but then the error on the standby looked like:

DETAIL: User was holding a relation lock for too long.

I'm not sure why that happened either, but I suppose it has a different cause. At the time they happened on the standby there was vacuuming of one table participating in the select query on the primary.

It's set to 400MB on the primary. These cancellations happened on the production system and they are very rare (maybe once or twice a week) and I don't have a way to reproduce them. So I don't think I have a way to correlate changing of the configuration parameters to query cancellation.

BTW, above mentioned cancellation because of the relation lock and vacuuming on the primary also happens very rarely and I don't know how to reproduce that either.

I don't think I can associate the problem with one table in the above query.
 

It's scheduled for today. :-)