duplicate lost hat/horse when wallet is disabled

[huumn]

It might have something to do with a wallet having both sending and receiving disabled simultaneously.

[ekzyis]

This happens because the trigger that inserts the checkWallet job runs twice.¹

This is because we have this trigger:

CREATE OR REPLACE FUNCTION wallet_wallet_type_as_jsonb()
RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    UPDATE "Wallet"
    SET wallet = to_jsonb(NEW)
    WHERE id = NEW."walletId";
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER wallet_lnbits_as_jsonb
AFTER INSERT OR UPDATE ON "WalletLNbits"
FOR EACH ROW EXECUTE PROCEDURE wallet_wallet_type_as_jsonb();

which updates the Wallet table.

So this trigger:

CREATE OR REPLACE FUNCTION check_wallet_trigger() RETURNS TRIGGER AS $$
DECLARE
    user_id INTEGER;
BEGIN
    -- if TG_OP is DELETE, then NEW.userId is NULL
    user_id := CASE WHEN TG_OP = 'DELETE' THEN OLD."userId" ELSE NEW."userId" END;

    INSERT INTO pgboss.job (name, data, retrylimit, startafter, keepuntil)
    VALUES ('checkWallet', jsonb_build_object('userId', user_id), 21, now(), now() + interval '5 minutes');

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER check_wallet_trigger
AFTER INSERT OR UPDATE OR DELETE ON "Wallet"
FOR EACH ROW EXECUTE PROCEDURE check_wallet_trigger();

runs twice when the client saves a wallet; once for the wallet update in upsertWallet and once because of the wallet update in the wallet_wallet_type_as_jsonb trigger above.

I confirmed this by dropping the wallet_lnbits_as_jsonb trigger and no longer seeing two jobs per wallet save.

Footnotes

1. We don't get duplicate notifications locally though, because there is only one worker which afaict, doesn't run both jobs at the same time so the second job does actually see the previous update and does not insert or end streaks. However, in prod, we have multiple workers, so the jobs run in parallel there. ↩