Skip to content

Can't drop trigger function #505

Description

@Real-Gecko

As was mentioned here #148 (comment) I cannot drop trigger function.
This time I tested with pgschema 1.12.0 and https://github.com/janbjorge/pgqueuer. pgq install --dry-run generates following:

CREATE TYPE pgqueuer_status AS ENUM ('queued', 'picked', 'successful', 'exception', 'canceled', 'deleted', 'failed');
    CREATE  TABLE pgqueuer (
        id SERIAL PRIMARY KEY,
        priority INT NOT NULL,
        queue_manager_id UUID,
        created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        updated TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        heartbeat TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        execute_after TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        status pgqueuer_status NOT NULL,
        entrypoint TEXT NOT NULL,
        dedupe_key TEXT,
        payload BYTEA,
        headers JSONB,
        attempts INT NOT NULL DEFAULT 0
    );
    CREATE INDEX pgqueuer_priority_id_id1_idx ON pgqueuer (priority ASC, id DESC)
        INCLUDE (id) WHERE status = 'queued';
    CREATE INDEX pgqueuer_updated_id_id1_idx ON pgqueuer (updated ASC, id DESC)
        INCLUDE (id) WHERE status = 'picked';
    CREATE INDEX pgqueuer_queue_manager_id_idx ON pgqueuer (queue_manager_id)
        WHERE queue_manager_id IS NOT NULL;
    CREATE UNIQUE INDEX IF NOT EXISTS pgqueuer_unique_dedupe_key ON
        pgqueuer (dedupe_key) WHERE ((status IN ('queued', 'picked') AND dedupe_key IS NOT NULL));

    CREATE  TABLE pgqueuer_log (
        id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        job_id BIGINT NOT NULL,
        status pgqueuer_status NOT NULL,
        priority INT NOT NULL,
        entrypoint TEXT NOT NULL,
        traceback JSONB DEFAULT NULL,
        aggregated BOOLEAN DEFAULT FALSE
    );
    CREATE INDEX pgqueuer_log_not_aggregated ON pgqueuer_log ((1)) WHERE not aggregated;
    CREATE INDEX pgqueuer_log_created ON pgqueuer_log (created);
    CREATE INDEX pgqueuer_log_status ON pgqueuer_log (status);
    CREATE INDEX pgqueuer_log_job_id_status ON pgqueuer_log (job_id, created DESC);

    CREATE  TABLE pgqueuer_statistics (
        id SERIAL PRIMARY KEY,
        created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT DATE_TRUNC('sec', NOW() at time zone 'UTC'),
        count BIGINT NOT NULL,
        priority INT NOT NULL,
        status pgqueuer_status NOT NULL,
        entrypoint TEXT NOT NULL
    );
    CREATE UNIQUE INDEX pgqueuer_statistics_unique_count ON pgqueuer_statistics (
        priority,
        DATE_TRUNC('sec', created at time zone 'UTC'),
        status,
        entrypoint
    );

    CREATE  TABLE pgqueuer_schedules (
        id SERIAL PRIMARY KEY,
        expression TEXT NOT NULL, -- Crontab-like schedule definition (e.g., '* * * * *')
        entrypoint TEXT NOT NULL,
        heartbeat TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        updated TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        next_run TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
        last_run TIMESTAMP WITH TIME ZONE,
        status pgqueuer_status DEFAULT 'queued',
        UNIQUE (expression, entrypoint)
    );

    CREATE FUNCTION fn_pgqueuer_changed() RETURNS TRIGGER AS $$
    DECLARE
        to_emit BOOLEAN := false;  -- Flag to decide whether to emit a notification
    BEGIN
        -- Check operation type and set the emit flag accordingly
        IF TG_OP = 'UPDATE' AND OLD IS DISTINCT FROM NEW THEN
            to_emit := true;
        ELSIF TG_OP = 'DELETE' THEN
            to_emit := true;
        ELSIF TG_OP = 'INSERT' THEN
            to_emit := true;
        ELSIF TG_OP = 'TRUNCATE' THEN
            to_emit := true;
        END IF;

        -- Perform notification if the emit flag is set
        IF to_emit THEN
            PERFORM pg_notify(
                'ch_pgqueuer',
                json_build_object(
                    'channel', 'ch_pgqueuer',
                    'operation', lower(TG_OP),
                    'sent_at', NOW(),
                    'table', TG_TABLE_NAME,
                    'type', 'table_changed_event'
                )::text
            );
        END IF;

        -- Return appropriate value based on the operation
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
            RETURN OLD;
        ELSE
            RETURN NULL; -- For TRUNCATE and other non-row-specific contexts
        END IF;

    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER tg_pgqueuer_changed
    AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON pgqueuer
    EXECUTE FUNCTION fn_pgqueuer_changed();

When I execute it and do pgschema dump > schema.sql it updates schema with new tables/functions/triggers. And if I rollback schema.sql changes and run pgschema apply --file schema.sql I get following:

Plan: 6 to drop.

Summary by type:
  types: 1 to drop
  functions: 1 to drop
  tables: 4 to drop

Types:
  - pgqueuer_status

Functions:
  - fn_pgqueuer_changed

Tables:
  - pgqueuer
  - pgqueuer_log
  - pgqueuer_schedules
  - pgqueuer_statistics

DDL to be executed:
--------------------------------------------------

DROP FUNCTION IF EXISTS fn_pgqueuer_changed();

DROP TABLE IF EXISTS pgqueuer_statistics CASCADE;

DROP TABLE IF EXISTS pgqueuer_schedules CASCADE;

DROP TABLE IF EXISTS pgqueuer_log CASCADE;

DROP TABLE IF EXISTS pgqueuer CASCADE;

DROP TYPE IF EXISTS pgqueuer_status RESTRICT;

Do you want to apply these changes? (yes/no): yes

Applying changes...

Executing group 1/1...
  Executing 6 statements in implicit transaction
Error: failed to execute concatenated statements in group 1: ERROR: cannot drop function fn_pgqueuer_changed() because other objects depend on it (SQLSTATE 2BP01)

So as you can see from output for some reason it does not detect that trigger must be dropped, thus it cannot drop trigger function.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions