PostgreSQL 主鍵從測試表中消失

我有一個有點複雜的場景，我用主鍵創建的測試表不會返回主鍵。pgAdmin III 報告沒有限制。我有整個 PostgreSQL 查詢日誌，下面有我用來創建測試表的查詢。然後，我將主鍵放在另一個測試表上，並使用生成的查詢（這不是我手動執行的查詢）來幫助我搜尋 pgAdmin III，將主鍵放在有問題的表上，但沒有找到任何搜尋：

ALTER TABLE public.delete_key_bigserial DROP CONSTRAINT

字元串“DROP CONSTRAINT”僅在可追溯到 2014-12-02 的查詢日誌中出現一次，這比我創建測試表還要早幾週。我現在明白，主鍵可能會或可能不會被設置為，bigserial或者serial甚至已經創建了一個沒有主鍵的表，將 id 設置為整數，然後設置id為主鍵（*整個“第二天”*的另一罐蠕蟲）。

在較早的一個問題中，我詢問瞭如何獲取data_type包含的內容，如果它是bigserial或者serialErwin Brandstetter 有一個很好的答案。他特別提供了兩個查詢，一個用於獲取data_type所有列的 s，一個用於獲取data_type主鍵的 s。不幸的是，我一直在測試的其中一個測試表沒有返回任何結果。

SELECT a.attrelid::regclass::text, a.attname,
CASE a.atttypid
WHEN 'int'::regtype  THEN 'serial'
WHEN 'int8'::regtype THEN 'bigserial'
WHEN 'int2'::regtype THEN 'smallserial'
END AS serial_type
FROM   pg_attribute  a
JOIN   pg_constraint c ON c.conrelid  = a.attrelid AND c.conkey[1] = a.attnum 
JOIN   pg_attrdef   ad ON ad.adrelid  = a.attrelid
                      AND ad.adnum   = a.attnum
WHERE  a.attrelid = 'delete_key_bigserial'::regclass
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND    c.contype = 'p'                                -- PK
AND    array_length(c.conkey, 1) = 1                  -- single column
AND    ad.adsrc = 'nextval('''
           || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
           || '''::regclass)';

該查詢適用於所有其他表。

我從 2014 年 11 月開始使用 PostgreSQL，從大約 2011 年開始使用 MySQL，所以我能做的最好的事情就是盡可能多地獲取相關數據。這是用於delete_key_bigserial從查詢日誌創建表的查詢：

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL)
WITH (OIDS = FALSE);

我簡化了 Erwin 的查詢並在表上使用它來將我的查詢工具中的結果與不同的測試表進行比較，該查詢在這些表上執行良好（在所有四個data_types 上）：

SELECT * FROM pg_attribute a 
WHERE a.attrelid = 'delete_key_bigserial'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
AND attname='id'
ORDER BY a.attnum;

+----------+---------+----------+---------------+--------+--------+----------+-------------+
| attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff |
+----------+---------+----------+---------------+--------+--------+----------+-------------+
| 46390    | id      | 20       | -1            | 8      | 20     | 0        | -1          |
+----------+---------+----------+---------------+--------+--------+----------+-------------+

+-----------+----------+------------+----------+------------+-----------+--------------+
| atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped |
+-----------+----------+------------+----------+------------+-----------+--------------+
| -1        | f        | p          | d        | t          | t         | f            |
+-----------+----------+------------+----------+------------+-----------+--------------+

+------------+-------------+--------------+--------+------------+---------------+
| attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions |
+------------+-------------+--------------+--------+------------+---------------+
| t          | 0           |              |        |            |               |
+------------+-------------+--------------+--------+------------+---------------+

atttypidErwin在滿足其他條件時通過列派生類型，但是結果列/行與其他工作表相同。我在嘗試確定data_type主鍵是什麼時使用了另一個目錄表，因此我決定也通過以下查詢比較該表的結果：

SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='delete_key_bigserial'
AND is_nullable='NO';

返回的任何返回的列/行的唯一區別（除了table_name和column_default列中的表名）是dtd_identifier列。該表delete_key_bigserial返回dtd_identifier具有值的列20，對於工作表，查詢返回1。PostgreSQL element_types 文件的（底部）將該列描述為：

元素的數據類型描述符的標識符。這目前沒有用。

我猜這是一種已棄用/較舊的時尚，儘管它可以簡單地引用描述本身，但它是為遺留目的而保留的？我不確定，但這就是我所在的地方，我什至不確定我是否走在正確的道路上。

我寧願處理這個問題並從場景中學習然後忽略它，因為它是一個測試表，因為有一天我確信當它不是一個測試表時我將不得不處理這個問題。我很樂意用相關資訊更新我的問題，這些資訊可能有助於找出問題所在。

對於這樣創建的表：

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

…我在上一個答案中的查詢（以及 pgAdmin、psql 或任何其他體面的客戶端）都會找到 PK 約束。如果它不存在，您以某種方式將其刪除。請注意，我的第一個查詢僅在它是PK和
類型 時才返回該列——範例就是這種情況。serial

造成混淆的另一個可能原因：也許您的數據庫中有多個表命名 delete_key_bigserial？表名僅在單個模式中是唯一的。測試：

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

為了使您的查詢明確，模式限定表名：

WHERE  a.attrelid = '**public.**delete_key_bigserial'::regclass

有一些方法可以使約束“消失”而不DROP CONSTRAINT在日誌中留下 a。

• 刪除並重新創建表。
• 刪除並重新創建架構或數據庫。
• （臨時）設置log_statement或其他相關設置，以便不記錄該語句。
• 直接作業系統目錄（作為超級使用者）在內部，主鍵contype = 'p'在表中設置pg_constraint。
• 編輯日誌文件。
• 等等。

引用自：https://dba.stackexchange.com/questions/90651