How to determine Payment Processor for "pending" transactions (via SQL or otherwise)?

Question

Is there a way to determine the Payment Processor for "pending" transactions? Our site has both Authorize.Net and PayPal Standard configured as payment methods, and I'm seeing and trying to debug lots of "pending" transactions (running on WordPress v.5.4.2, Civi v.5.25.0 -- just upgraded to 5.28.0, but the pending transactions have been happening since before then).

Or: should all of the "pending" transactions always only be from PayPal, since Auth.Net should complete immediately without IPN callback? And even for Auth.Net recurring transactions? (Since in principle one can configure and use multiple PayPal Payment Processors, it would still be helpful to be able to disambiguate pending transactions for those.)

I've explored the database, and I see that once a transaction has completed, I can get the Payment Processor via the civicrm_financial_trxn table (SQL below). But for 'pending', I haven't been able to track anything down.

select y.name `Payment Processor`
    , p.title
    , c.receive_date
    , c.id `contrib_id`
    , c.contribution_recur_id
    , c.invoice_id
    , c.trxn_id
    , case when c.contribution_status_id = 1 then 'Completed'
        when c.contribution_status_id = 2 then 'Pending'
        when c.contribution_status_id = 4 then 'Failed'
        else c.contribution_status_id end `Contrib Status`
    , t.payment_processor_id
    , y.url_site `Processor URL`
    , c.contact_id
    , c.total_amount
    , c.fee_amount
    , c.net_amount
    from civicrm_contribution c
    inner join civicrm_contribution_page p on p.id = c.contribution_page_id
    left join civicrm_financial_trxn t on t.trxn_id = c.trxn_id
    left join civicrm_payment_processor y on t.payment_processor_id = y.id
    where c.receive_date >= '2020-06-01'
    and c.is_test = 0 and c.is_pay_later = 0
    and t.from_financial_account_id is null
    order by c.contribution_status_id, c.receive_date, y.name, c.id desc;

(The t.from_financial_account_id is null clause prevents multiple matches with civicrm_financial_trxn table, since has multiple records for a single transaction when there are transaction fees.)

Answer 1

Below query should give you all pending contributions from payment processor

SELECT cc.id, cc.contact_id, cc.total_amount, cpp.title, 
  cc.receive_date, cft.trxn_date, cc.invoice_id, cc.invoice_number, cc.trxn_id
FROM civicrm_contribution cc 
  INNER JOIN civicrm_entity_financial_trxn ceft
    ON ceft.entity_id = cc.id AND ceft.entity_table = 'civicrm_contribution'
  INNER JOIN civicrm_financial_trxn cft
    ON cft.id = ceft.financial_trxn_id
  INNER JOIN civicrm_payment_processor cpp
    ON cpp.id = cft.payment_processor_id
WHERE cc.contribution_status_id = 2 AND cc.is_pay_later = 0;