PostgreSQL 9.6 列刪除和對帶有 CTE 的 SQL 函式的副作用

如果我有一個包含 3 列的表 - 比如說 A、B 和 D - 我必須引入一個新的 - 比如說 C 來替換 D 的目前位置。我將使用以下方法：

1. 引入 2 個新列作為 C 和 D2。
2. 將D的內容複製到D2。
3. 刪除 D。
4. 將 D2 重命名為 D。

新的順序是 A、B、C 和 D。

我認為這是一種合法的做法，因為（到目前為止）它沒有產生任何問題。

但是，今天我遇到了一個問題，當一個函式在同一個表上執行語句時返回以下錯誤：

table row type and query-specified row type do not match

以及以下細節：

Query provides a value for a dropped column at ordinal position 13

我嘗試重新啟動 PostgreSQL，按照此處和此處VACUUM FULL的建議執行並最終刪除並重新創建函式，但這些解決方案不起作用（除了他們嘗試處理系統表已更改的情況）。

擁有使用非常小的數據庫的奢侈，我將它導出，刪除它然後重新導入它，這解決了我的功能問題。

---

我知道這樣一個事實，即不應該通過修改系統表（弄髒手pg_attribute等）來弄亂列的自然順序，如下所示：

是否可以更改 Postgres 中列的自然順序？

從我的函式拋出的錯誤來看，我現在意識到用我的方法改變列的順序也是一個禁忌。任何人都可以闡明為什麼我在做的事情也是錯誤的嗎？

---

Postgres 版本是 9.6.0。

這是功能：

CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '

-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),

-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
authentication_date = current_timestamp,
facebook_id = facebookid
WHERE EXISTS (SELECT * FROM select_user)
AND
useremail = users.email
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id),

-- Third, insert the user (if user does not exist):
insert_user AS
(INSERT INTO
users (authentication_code, authentication_date, email, key, name, facebook_id)
SELECT
GEN_RANDOM_UUID(),
current_timestamp,
useremail,
GEN_RANDOM_UUID(),
COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
facebookid
WHERE NOT EXISTS (SELECT * FROM select_user)
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id)

-- Finally, select the authentication code, ID, key and Stripe ID:
SELECT
*
FROM
update_user
UNION ALL
SELECT
*
FROM
insert_user' LANGUAGE "sql" COST 100 ROWS 1
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

facebook_id我對兩個列都執行了重命名/重新排序stripe_id（在這些列之前添加了一個新列，這是重命名的原因，但此查詢沒有觸及）。

將列按特定順序排列純粹是出於對順序的興趣。但是，提出這個問題的原因是出於對列的簡單重命名和刪除可能會引發在生產模式下使用函式的人的實際問題的擔憂（就像我自己發生的那樣）。

9.6 和 9.6.1 上的可能錯誤

這對我來說完全像一個錯誤……

我不知道它為什麼會發生，但我可以確認它發生了。這是重現問題的最簡單的設置（在版本 9.6.0 和 9.6.1 中）。

CREATE TABLE users
(
   id SERIAL PRIMARY KEY,
   email TEXT NOT NULL,
   column_that_we_will_drop TEXT
) ;

-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
   (_useremail text) 
RETURNS integer AS
$$
-- Need a CTE to produce the error. A 'constant' one suffices.
WITH something_even_if_useless(a) AS
(
   VALUES (1)
)
UPDATE
   users
SET
   id = id
WHERE 
   -- The CTE needs to be referenced, if the next
   -- condition were not in place, the problem is not reproduced
   EXISTS (SELECT * FROM something_even_if_useless)
   AND email = _useremail
RETURNING
   id
$$
LANGUAGE "sql" ;

在這個設置之後，下一條語句就可以工作了

SELECT * FROM __post_users('a@b.com');

此時，我們刪除一列：

ALTER TABLE users 
   DROP COLUMN column_that_we_will_drop ;

此更改使下一條語句生成錯誤

SELECT * FROM __post_users('a@b.com');

這與@Andy 提到的相同：

ERROR: table row type and query-specified row type do not match
SQL state: 42804
Detail: Query provides a value for a dropped column at ordinal position 3.
Context: SQL function "__post_users" statement 1
   SELECT * FROM __post_users('a@b.com');

刪除並重新創建函式並不能解決問題。

VACUUM FULL（表或整個數據庫）不能解決問題。

---

錯誤報告已傳遞到相應的 PostgreSQL 郵件列表，我們得到了非常快速的響應：

我無法在 HEAD 或 9.6 分支提示中重現此內容。我相信它已經被這個更新檔修復了，它在 9.6.1 之後出現了一點：

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22

但是感謝您的報告！

問候，湯姆萊恩

---

版本 9.6.2

在 2017-03-06，我可以確認我無法在版本 9.6.2 上重現此行為。也就是說，該錯誤似乎已在此版本中得到糾正。

更新

根據@Jana 的評論：“我可以確認該錯誤存在於 9.6.1 中並已在 9.6.2 中修復。該修復也列在postgres 發佈網站上：修復虛假的“查詢為刪除的列提供值”錯誤期間刪除列的表上的 INSERT 或 UPDATE"

---

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