pq: column "adv_account_sig_key" does not exist

[devalexandre]

I'm using _ "github.com/lib/pq" but when I run display a error

18:33:47.612 [Database INFO] Upgrading database to v1
18:33:49.908 [Database INFO] Upgrading database to v2
panic: failed to upgrade database: pq: column "adv_account_sig_key" does not exist

container, err := sqlstore.New("postgres", config.GetDataBaseDNS(), dbLog)

[tulir]

Are you using some weird postgres-ish server like cockroachdb?

[devalexandre]

Are you using some weird postgres-ish server like cockroachdb?

I'm using cockroachdb serverless, but in the local postgres it gives the same error

[tulir]

It works fine on real postgres, cockroach won't work right now because schema migrations aren't synchronous there.

[fnxln]

having same problem on real postgres 15.4

[Mauricio-Carrion]

Same trying to use cockroachdb here

[Mauricio-Carrion]

The error just ocurrs on upgrading database to v2 migration.

[Mauricio-Carrion]

In cockroachdb panel seems everthing right

[Mauricio-Carrion]

Now I extract the SQL statatements using ChatGPT from store/sqlstore/upgrade.go

CREATE TABLE IF NOT EXISTS whatsmeow_version (version INTEGER);

SELECT version FROM whatsmeow_version LIMIT 1;

DELETE FROM whatsmeow_version;

INSERT INTO whatsmeow_version (version) VALUES ($1);

CREATE TABLE whatsmeow_device (
    jid TEXT PRIMARY KEY,
    registration_id BIGINT NOT NULL CHECK (registration_id >= 0 AND registration_id < 4294967296),
    noise_key bytea NOT NULL CHECK (length(noise_key) = 32),
    identity_key bytea NOT NULL CHECK (length(identity_key) = 32),
    signed_pre_key bytea NOT NULL CHECK (length(signed_pre_key) = 32),
    signed_pre_key_id INTEGER NOT NULL CHECK (signed_pre_key_id >= 0 AND signed_pre_key_id < 16777216),
    signed_pre_key_sig bytea NOT NULL CHECK (length(signed_pre_key_sig) = 64),
    adv_key bytea NOT NULL,
    adv_details bytea NOT NULL,
    adv_account_sig bytea NOT NULL CHECK (length(adv_account_sig) = 64),
    adv_device_sig bytea NOT NULL CHECK (length(adv_device_sig) = 64),
    platform TEXT NOT NULL DEFAULT '',
    business_name TEXT NOT NULL DEFAULT '',
    push_name TEXT NOT NULL DEFAULT ''
);

CREATE TABLE whatsmeow_identity_keys (
    our_jid TEXT,
    their_id TEXT,
    identity bytea NOT NULL CHECK (length(identity) = 32),
    PRIMARY KEY (our_jid, their_id),
    FOREIGN KEY (our_jid) REFERENCES whatsmeow_device(jid) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Other CREATE TABLE statements similar to the ones above.

ALTER TABLE whatsmeow_device ADD COLUMN adv_account_sig_key bytea CHECK (length(adv_account_sig_key) = 32);

UPDATE whatsmeow_device SET adv_account_sig_key = (
    SELECT identity FROM whatsmeow_identity_keys
    WHERE our_jid = whatsmeow_device.jid AND their_id = concat(split_part(whatsmeow_device.jid, '.', 1), ':0')
);

-- Other SQL statements for PostgreSQL or SQLite specific updates.

CREATE TABLE whatsmeow_message_secrets (
    our_jid TEXT,
    chat_jid TEXT,
    sender_jid TEXT,
    message_id TEXT,
    key bytea NOT NULL,
    PRIMARY KEY (our_jid, chat_jid, sender_jid, message_id),
    FOREIGN KEY (our_jid) REFERENCES whatsmeow_device(jid) ON DELETE CASCADE ON UPDATE CASCADE
);

But the real problem is here:

Apparantly cockroachdb doesn't runs update and delete on databases