摆脱重复的表。 [英] Getting rid of duplicate tables.

问题描述

首先，我希望我知道这是怎么造成的，但这是我们的问题。


最近有时我们得到了一张重复的表格。这是一个pg_dump的结果，其中pg_restore只包含该表。


-

- TOC条目59 （OID 11462032）

- 姓名：order_to_do;类型：TABLE;架构：公共;所有者：www

- 数据位：0

-


CREATE TABLE order_to_do（

order_id integer DEFAULT 0 NOT NULL，

to_do_id text DEFAULT'''':: text NOT NULL，

date_time timestamp without time zone DEFAULT''0001-01-01 00:00:00''::没有时区的时间戳NOT NULL，

csr_id整数默认值0 NOT NULL，

补充文本DEFAULT'''':: text NOT NULL

）;

-

- TOC条目60（OID 11462032）

- 名称： order_to_do;类型：TABLE;架构：公共;所有者：www

- 数据位：0

-


CREATE TABLE order_to_do（

order_id integer DEFAULT 0 NOT NULL，

to_do_id text DEFAULT'''':: text NOT NULL，

date_time timestamp without time zone DEFAULT''0001-01-01 00:00:00''::没有时区的时间戳NOT NULL，

csr_id整数默认值0 NOT NULL，

补充文本DEFAULT'''':: text NOT NULL

）;


所以不知何故，我们设法得到了表格的*完全*副本至少
$ b $在重要的地方重复参考表格。


首先我们如何摆脱这个额外的表格？


注意：

live = #drop table order_to_do;

错误：重复键违反了唯一约束pg_class_oid_index


其次是关于它如何到达那里的任何想法？


这可能发生吗？或者有人在这里不小心做了一些事情

来创建它。


很抱歉没有任何想法导致这个，但感谢您的帮助可以

给。


Jared


-------------- -------------（广播结束）---------------------------

提示2：你可以使用取消注册命令一次性取消所有列表

（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql.org ）

First I wish I knew how this was caused but here is our problem.

Sometime in the recent past we got a duplicate table. Here is the
result of a pg_dump with a pg_restore for just that table.

--
-- TOC entry 59 (OID 11462032)
-- Name: order_to_do; Type: TABLE; Schema: public; Owner: www
-- Data Pos: 0
--

CREATE TABLE order_to_do (
order_id integer DEFAULT 0 NOT NULL,
to_do_id text DEFAULT ''''::text NOT NULL,
date_time timestamp without time zone DEFAULT ''0001-01-01 00:00:00''::timestamp without time zone NOT NULL,
csr_id integer DEFAULT 0 NOT NULL,
supplement text DEFAULT ''''::text NOT NULL
);
--
-- TOC entry 60 (OID 11462032)
-- Name: order_to_do; Type: TABLE; Schema: public; Owner: www
-- Data Pos: 0
--

CREATE TABLE order_to_do (
order_id integer DEFAULT 0 NOT NULL,
to_do_id text DEFAULT ''''::text NOT NULL,
date_time timestamp without time zone DEFAULT ''0001-01-01 00:00:00''::timestamp without time zone NOT NULL,
csr_id integer DEFAULT 0 NOT NULL,
supplement text DEFAULT ''''::text NOT NULL
);

So somehow it appears that we managed to get an *exact* duplicate of the table well at least
a duplicate reference to the table somewhere important.

First of all how do we get rid of this "extra" table?

Note:
live=# drop table order_to_do;
ERROR: duplicate key violates unique constraint "pg_class_oid_index"

Secondly any ideas on how it got there in the first place?

Can this just happen? Or did someone here have to accidentally do something
to create it.

Sorry for not having any idea on what caused this, but thanks for any help you can
give.

Jared

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

推荐答案

Jared Carr< ; JA *** @ 89glass.com>写道：

Jared Carr <ja***@89glass.com> writes:

首先，我希望我知道这是怎么造成的，但这是我们的问题。
在最近的某个时候我们得到了一个重复的表。以下是pg_dump的结果，其中pg_restore仅用于该表。

First I wish I knew how this was caused but here is our problem.
Sometime in the recent past we got a duplicate table. Here is the
result of a pg_dump with a pg_restore for just that table.

这应该很容易修复：手动删除额外的pg_class

条目（由ctid选择它，因为没有其他方法可以选择

只是其中之一）。但是，在我们筋疲力尽之前请不要这样做。

有机会弄清楚这是怎么发生的。


对于初学者来说，PG版本是什么这是？目前在pg_class中有多少条目

？ （VACUUM VERBOSE pg_class

的输出对我们来说很有用。）


问候，汤姆巷


---------------------------（播出结束）-------------- -------------

提示6：您是否搜索了我们的列表档案？

http://archives.postgresql.org

This should be fairly easy to fix: manually DELETE the extra pg_class
entry (selecting it by ctid, since there isn''t any other way to select
just one of them). However, please don''t do that until we''ve exhausted
the opportunity to figure out how this happened.

For starters, what PG version is this? And how many entries are there
in pg_class at the moment? (The output from "VACUUM VERBOSE pg_class"
would be useful to look at.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane写道：

Tom Lane wrote:

Jared Carr< ja *** @ 89glass.com>写道：

Jared Carr <ja***@89glass.com> writes:

首先，我希望我知道这是怎么造成的，但这是我们的问题。
在最近的某个时候我们得到了一个重复的表格。以下是pg_dump的结果，其中pg_restore仅用于该表。

First I wish I knew how this was caused but here is our problem.
Sometime in the recent past we got a duplicate table. Here is the
result of a pg_dump with a pg_restore for just that table.

这应该很容易修复：手动删除额外的pg_class
输入（由ctid选择，因为没有其他方法可以选择其中一个）。但是，请不要这样做，直到我们已经筋疲力尽有机会弄清楚这是怎么发生的。

对于初学者来说，这是什么PG版本？目前pg_class中有多少条目？ （VACUUM VERBOSE pg_class
的输出对我们来说很有用。）

问候，tom lane

This should be fairly easy to fix: manually DELETE the extra pg_class
entry (selecting it by ctid, since there isn''t any other way to select
just one of them). However, please don''t do that until we''ve exhausted
the opportunity to figure out how this happened.

For starters, what PG version is this? And how many entries are there
in pg_class at the moment? (The output from "VACUUM VERBOSE pg_class"
would be useful to look at.)

regards, tom lane

这是版本7.4.1（从大约3周前的7.4升级）。


INFO：vacuuming" pg_catalog.pg_class"

INFO：index" pg_class_oid_index"现在包含7个页面中的412行版本

详细信息：0个索引页面已被删除，0个当前可重复使用。

CPU 0.00s / 0.00u秒已过去0.00秒。

INFO：index" pg_class_relname_nsp_index"现在包含412行版本

25页

详细信息：0索引页面已被删除，0目前可重复使用。

CPU 0.00s / 0.00秒已过去0.00秒。

INFO：" pg_class"：找到0个可移动的，412个不可移动的行版本

31页
详情：0死行版本尚未删除。

有1216个未使用的项目指针。

0页面完全为空。

CPU 0.00s /0.00u秒已过去0.00秒。


查询成功返回，但在20毫秒内没有结果。


与此相关的pg_class的内容表格。


live =＃select * from pg_class where relname =''order_to_do'';

relname | relnamespace | reltype | relowner | relam | relfilenode

| relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |

relisshared | relkind | relnatts |重新检查| reltriggers | relukeys |

relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |

relhassubclass | relacl

------------- + -------------- + ---------- + - --------- ------- + ------------- + + ---------- + ------- ---- + --------------- + --------------- + ------------- + ------------- + --------- + ---------- + ----------- + - ----------- ---------- + ---------- + + --------- + ------ ------ + ------------ + ------------- + ---------------- + --------

order_to_do | 2200 | 11462033 | 101 | 0 | 11462032

| 506 | 59401 | 11462039 | 0 | t |

f | r | 5 | 0 | 1 | 0

| 0 | 0 | t | t | f |

f |

order_to_do | 2200 | 11462033 | 101 | 0 | 11462032

| 453 | 53407 | 11462039 | 0 | t |

f | r | 5 | 0 | 1 | 0

| 0 | 0 | t | t | f |

f |

（2行）


感谢您的帮助，如果您需要更多信息，请与我们联系。


Jared


------------------------ ---（播出结束）---------------------------

提示3：如果发布/阅读通过Usenet，请发送适当的

subscribe-nomail命令到 ma ******* @ postgresql.org 以便您的

消息可以干净地通过邮件列表

This is version 7.4.1 (upgraded from 7.4 about 3 weeks ago).

INFO: vacuuming "pg_catalog.pg_class"
INFO: index "pg_class_oid_index" now contains 412 row versions in 7 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_class_relname_nsp_index" now contains 412 row versions
in 25 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_class": found 0 removable, 412 nonremovable row versions in
31 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1216 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

Query returned successfully with no result in 20 ms.

And the contents of pg_class relating to this table.

live=# select * from pg_class where relname=''order_to_do'';
relname | relnamespace | reltype | relowner | relam | relfilenode
| relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex |
relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
-------------+--------------+----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
order_to_do | 2200 | 11462033 | 101 | 0 | 11462032
| 506 | 59401 | 11462039 | 0 | t |
f | r | 5 | 0 | 1 | 0
| 0 | 0 | t | t | f |
f |
order_to_do | 2200 | 11462033 | 101 | 0 | 11462032
| 453 | 53407 | 11462039 | 0 | t |
f | r | 5 | 0 | 1 | 0
| 0 | 0 | t | t | f |
f |
(2 rows)

Thanks for the help, and let me know if you need any more information.

Jared

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Jared Carr< ja***@89glass.com>写道：

Jared Carr <ja***@89glass.com> writes:

对于初学者来说，这是什么PG版本？目前pg_class中有多少条目？ （VACUUM VERBOSE pg_class
的输出对于查看是有用的。）

For starters, what PG version is this? And how many entries are there
in pg_class at the moment? (The output from "VACUUM VERBOSE pg_class"
would be useful to look at.)

这是版本7.4.1（从大约3周前的7.4升级）。


好​​吧，这让我想到这是一个老虫子。我越来越怀疑有一些角落问题

在7.4中有重复行，因为我们现在已经看到两三个报告了

意外重复问题。报告都有不同的

症状，但我感觉到一个共同的主题...

和这个表有关的pg_class的内容。

直播= #select * from pg_class where relname =''order_to_do'';

This is version 7.4.1 (upgraded from 7.4 about 3 weeks ago).
Okay, that shoots down my faint hope that this was an old bug. I am
growing increasingly suspicious that there is some corner-case problem
with duplicate rows in 7.4, because we''ve seen two or three reports now
of problems with unexpected duplicates. The reports all have different
symptoms but I sense a common theme ...
And the contents of pg_class relating to this table.

live=# select * from pg_class where relname=''order_to_do'';

我正要问这个，但我需要查看系统列

也是：


从pg_class中选择oid，ctid，cmax，xmax，cmin，xmin，*

其中relname =''order_to_do '';


另外，你会检查这个查询是否得到相同的结果

并关闭了enable_indexscan？


问候，tom lane


---------------------------（...结束广播）---------------------------

提示8：解释分析是你的朋友

I was just about to ask for that, but I need to see the system columns
too:

select oid,ctid,cmax,xmax,cmin,xmin,* from pg_class
where relname=''order_to_do'';

Also, would you check whether you get the same results from this query
with enable_indexscan switched off?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

这篇关于摆脱重复的表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！