Supabase中SQL定时任务更新limit字段返回错误结果求助

Likely Root Causes #

1. Unordered Execution of pg_cron Jobs #

pg_cron schedules jobs independently—even if set to run at the same time, your three hourly tasks can execute in any order. For example, the limit calculation might run before total_jobs is updated from Stripe. If total_jobs is still 0 (or an outdated value) at that point, limit = 0 - jobs_applied results in a negative number.

2. Stripe Sync Logic Failures #

Your SQL for updating total_jobs may incorrectly set the value to 0 in edge cases:

• If the Stripe subscription lookup returns no results (network error, missing record), the query might default to 0 instead of retaining the existing total_jobs value.
• Missing mappings for certain price_id values could lead to unhandled cases that set total_jobs to 0.

3. Concurrent Transaction Interference #

Jobs running concurrently can read intermediate values from incomplete transactions. For example, jobs_applied might be updated, but total_jobs is still being synced when the limit calculation runs, leading to incorrect math.

Solutions & Optimizations #

1. Combine All Jobs Into a Single Atomic Transaction #

Replace three separate cron jobs with one script that runs all steps in sequence within a transaction. This enforces order and ensures no partial updates:

BEGIN;

-- Step 1: Update jobs_applied count
UPDATE users u
SET jobs_applied = (
    SELECT COUNT(*)
    FROM job_applications ja
    WHERE ja.user_id = u.id
);

-- Step 2: Update total_jobs from Stripe subscriptions (retain existing value if price_id is unknown)
UPDATE users u
SET total_jobs = (
    CASE s.price_id
        WHEN 'price_basic' THEN 50
        WHEN 'price_pro' THEN 500
        WHEN 'price_enterprise' THEN 1000
        ELSE u.total_jobs
    END
)
FROM stripe_subscriptions s
WHERE s.user_id = u.id;

-- Step 3: Calculate limit (only needed if not using generated column)
UPDATE users
SET limit = GREATEST(total_jobs - jobs_applied, 0);

COMMIT;

Schedule this single script as your hourly pg_cron job.

2. Fix Stripe Sync Logic #

Ensure your Stripe sync query doesn’t overwrite total_jobs with 0 when a subscription is missing or has an unknown price_id. Use ELSE u.total_jobs (as shown above) to preserve the current value instead of defaulting to 0.

3. Replace Stored limit with a Generated Column #

Eliminate manual updates entirely by making limit a generated column. This ensures it’s always calculated correctly whenever total_jobs or jobs_applied changes:

-- Drop existing limit column if needed
ALTER TABLE users DROP COLUMN IF EXISTS limit;

-- Add generated column with non-negative safeguard
ALTER TABLE users ADD COLUMN limit INTEGER GENERATED ALWAYS AS (GREATEST(total_jobs - jobs_applied, 0)) STORED;

4. Optimize jobs_applied Updates #

Instead of hourly counts, use a trigger to keep jobs_applied real-time:

-- Trigger function to update jobs_applied on insert/delete
CREATE OR REPLACE FUNCTION update_jobs_applied()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET jobs_applied = jobs_applied + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET jobs_applied = jobs_applied - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to job_applications table
CREATE TRIGGER trigger_update_jobs_applied
AFTER INSERT OR DELETE ON job_applications
FOR EACH ROW EXECUTE FUNCTION update_jobs_applied();

This replaces the hourly jobs_applied cron job entirely.

5. Add Safeguard Constraints #

Block invalid values with a CHECK constraint to catch bugs early:

ALTER TABLE users ADD CONSTRAINT limit_non_negative CHECK (limit >= 0);

内容的提问来源于stack exchange，提问作者Uchenna Adubasim