[Question] how to handle pg-boss bumps regarding SQL functions?

Hello! I am facing an issue with the duplicate key value violates unique constraint "queue_pkey" error in my db logs, at each deployment (=> call start on pg-boss client, creating __pgboss__send-it)

2024-09-05 17:21:16 2024-09-05 15:21:16.520 UTC [5248] CONTEXT:  SQL statement "INSERT INTO pgboss.queue (
2024-09-05 17:21:16             name,
2024-09-05 17:21:16             policy,
2024-09-05 17:21:16             retry_limit,
2024-09-05 17:21:16             retry_delay,
2024-09-05 17:21:16             retry_backoff,
2024-09-05 17:21:16             expire_seconds,
2024-09-05 17:21:16             retention_minutes,
2024-09-05 17:21:16             dead_letter,
2024-09-05 17:21:16             partition_name
2024-09-05 17:21:16           )
2024-09-05 17:21:16           VALUES (
2024-09-05 17:21:16             queue_name,
2024-09-05 17:21:16             options->>'policy',
2024-09-05 17:21:16             (options->>'retryLimit')::int,
2024-09-05 17:21:16             (options->>'retryDelay')::int,
2024-09-05 17:21:16             (options->>'retryBackoff')::bool,
2024-09-05 17:21:16             (options->>'expireInSeconds')::int,
2024-09-05 17:21:16             (options->>'retentionMinutes')::int,
2024-09-05 17:21:16             options->>'deadLetter',
2024-09-05 17:21:16             table_name
2024-09-05 17:21:16           )"
2024-09-05 17:21:16     PL/pgSQL function pgboss.create_queue(text,json) line 6 at SQL statement
2024-09-05 17:21:16 2024-09-05 15:21:16.520 UTC [5248] STATEMENT:  SELECT pgboss.create_queue($1, $2)
2024-09-05 17:21:28 2024-09-05 15:21:28.888 UTC [5253] ERROR:  duplicate key value violates unique constraint "queue_pkey"
2024-09-05 17:21:28 2024-09-05 15:21:28.888 UTC [5253] DETAIL:  Key (name)=(__pgboss__send-it) already exists.

→ Looking into the SQL functions for this database, I see the create_queue with:


    DECLARE
      table_name varchar := 'j' || encode(sha224(queue_name::bytea), 'hex');
    BEGIN

      INSERT INTO pgboss.queue (
        name,
        policy,
        retry_limit,
        retry_delay,
        retry_backoff,
        expire_seconds,
        retention_minutes,
        dead_letter,
        partition_name
      )
      VALUES (
        queue_name,
        options->>'policy',
        (options->>'retryLimit')::int,
        (options->>'retryDelay')::int,
        (options->>'retryBackoff')::bool,
        (options->>'expireInSeconds')::int,
        (options->>'retentionMinutes')::int,
        options->>'deadLetter',
        table_name
      );

      EXECUTE format('CREATE TABLE pgboss.%I (LIKE pgboss.job INCLUDING DEFAULTS)', table_name);

      EXECUTE format('ALTER TABLE pgboss.%1$I ADD PRIMARY KEY (name, id)', table_name);
      EXECUTE format('ALTER TABLE pgboss.%1$I ADD CONSTRAINT q_fkey FOREIGN KEY (name) REFERENCES pgboss.queue (name) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED', table_name);
      EXECUTE format('ALTER TABLE pgboss.%1$I ADD CONSTRAINT dlq_fkey FOREIGN KEY (dead_letter) REFERENCES pgboss.queue (name) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED', table_name);
      EXECUTE format('CREATE UNIQUE INDEX %1$s_i1 ON pgboss.%1$I (name, COALESCE(singleton_key, '''')) WHERE state = ''created'' AND policy = ''short'';', table_name);
      EXECUTE format('CREATE UNIQUE INDEX %1$s_i2 ON pgboss.%1$I (name, COALESCE(singleton_key, '''')) WHERE state = ''active'' AND policy = ''singleton''', table_name);
      EXECUTE format('CREATE UNIQUE INDEX %1$s_i3 ON pgboss.%1$I (name, state, COALESCE(singleton_key, '''')) WHERE state <= ''active'' AND policy = ''stately''', table_name);
      EXECUTE format('CREATE UNIQUE INDEX %1$s_i4 ON pgboss.%1$I (name, singleton_on, COALESCE(singleton_key, '''')) WHERE state <> ''cancelled'' AND singleton_on IS NOT NULL', table_name);
      EXECUTE format('CREATE INDEX %1$s_i5 ON pgboss.%1$I (name, start_after) INCLUDE (priority, created_on, id) WHERE state < ''active''', table_name);

      EXECUTE format('ALTER TABLE pgboss.%I ADD CONSTRAINT cjc CHECK (name=%L)', table_name, queue_name);
      EXECUTE format('ALTER TABLE pgboss.job ATTACH PARTITION pgboss.%I FOR VALUES IN (%L)', table_name, queue_name);
    END;

This does is not expected as I am running 10.1.1 which contains a change to avoid throwing on existing queue (from https://github.com/timgit/pg-boss/releases/tag/10.0.4)

Is there an action to take in order to migrate the SQL functions when bumping to new versions? Should I be dropping the pg-boss schema each time I do?

Cheers

timgit / pg-boss

[Question] how to handle pg-boss bumps regarding SQL functions? #511