Re: BUG #15000: Cache lookup failure

Tom Lane schrieb am 08.01.2018 um 16:12:
>> We have a script that copies data from one database to another using pg_dump
>> (plain text format) and the pipes the output to psql
>> The script consistently fails with:
>
>> pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
>> relation 16693
>> pg_dump: [archiver (db)] query was: SELECT tableoid, oid, conname,
>> pg_catalog.pg_get_constraintdef(oid) AS consrc, conislocal, convalidated
>> FROM pg_catalog.pg_constraint WHERE conr elid =
>> '16693'::pg_catalog.oid AND contype = 'c' ORDER BY conname
>
> Hmph. Is it always the same OID in the message and the reported query?

No, it's not always the same OID.

I just re-ran the script and got

ERROR: cache lookup failed for relation 16816

> If so, what table does that correspond to (try select '16693'::regclass)

When I do that right after the error occurred then of course, nothing is shown:

prod=# select '16816'::regclass;
regclass
----------
16816
(1 row)

Same result on the source database.

The OIDs for everything that the script creates (in the target database) are

oid | relname
-------+-------------------
16790 | shop_pkey
16792 | language_seq
16794 | languages
16797 | languages_pkey
16799 | l10n_seq
16787 | shop
16847 | l10n_pkey
16849 | l10n_unique
16851 | l10n_value_unique
16832 | l10n
16838 | l10n_value

(For the above run, with "cache lookup failed for relation 16816")

> and is there anything odd about that table's declaration or usage?

No, nothing special.

For completeness, here is the DDL that is run by the shell script:

CREATE TABLE shop
(
shop_id bigint NOT NULL
);

ALTER TABLE shop
ADD CONSTRAINT shop_pkey
PRIMARY KEY (shop_id);

INSERT INTO shop VALUES (1), (2), (3);

DROP SEQUENCE IF EXISTS language_seq;

CREATE SEQUENCE language_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483646
CACHE 1
NO CYCLE;

DROP TABLE IF EXISTS languages CASCADE;

CREATE TABLE languages
(
language_id char(2) NOT NULL,
image_url varchar(300) NOT NULL,
description varchar(100)
);

ALTER TABLE languages
ADD CONSTRAINT languages_pkey
PRIMARY KEY (language_id);

DROP SEQUENCE IF EXISTS l10n_seq;

CREATE SEQUENCE l10n_seq
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483646
CACHE 1
NO CYCLE;

DROP TABLE IF EXISTS l10n CASCADE;

CREATE TABLE l10n
(
l10n_id bigint NOT NULL,
l10n_key varchar(80) NOT NULL,
shop_id bigint NOT NULL,
description varchar(4000),
image_url varchar(256)
);

ALTER TABLE l10n
ADD CONSTRAINT l10n_pkey
PRIMARY KEY (l10n_id);

ALTER TABLE l10n
ADD CONSTRAINT l10n_shop_fk FOREIGN KEY (shop_id)
REFERENCES shop (shop_id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;

ALTER TABLE l10n
ADD CONSTRAINT l10n_unique UNIQUE (l10n_key, shop_id);

DROP TABLE IF EXISTS l10n_value CASCADE;

CREATE TABLE l10n_value
(
l10n_id bigint NOT NULL,
language_id varchar(5) NOT NULL,
l10n_value varchar(4000) NOT NULL,
dirty char(1) DEFAULT '0'::bpchar,
cms_user varchar(20),
modified_at timestamp DEFAULT now(),
CONSTRAINT l10n_value_check_dirty CHECK ((dirty = '0'::bpchar) OR (dirty = '1'::bpchar))
);

ALTER TABLE l10n_value
ADD CONSTRAINT l10n_value_l10n_fk FOREIGN KEY (l10n_id)
REFERENCES l10n (l10n_id)
ON UPDATE NO ACTION
ON DELETE CASCADE;

ALTER TABLE l10n_value
ADD CONSTRAINT l10n_value_unique UNIQUE (l10n_id, language_id);