Getting rid of duplicate tables.

[Jared Carr]

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'::time stamp 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'::time stamp 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_i ndex"

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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

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

Tom Lane wrote:

Yes, it does appear that there was a backend crash/(operator stupidly
kill -9 'ing possibly) on the 29th.
Hmm ... could you send me that area of the log?

Dec 29 16:31:54 penguin postgres[1714]: [3-1] LOG: received smart
shutdown request
What it looks like to me is that this shutdown never completed, probably
because some client was hanging onto an open connection ("smart
shutdown" doesn't forcibly abort active sessions). The subsequent log
entries show a couple of failed attempts to start a postmaster. I would
guess the sequence was roughly this:

pg_ctl stop
... wait a couple minutes, get bored of waiting ...
pg_ctl start
... fails with
Dec 29 16:33:44 penguin postgres[5379]: [1-1] FATAL: lock file
"/var/lib/pgsql/data74/postmaster.pid" already exists
Dec 29 16:33:44 penguin postgres[5379]: [1-2] HINT: Is another
postmaster (PID 1714) running in data directory "/var/lib/pgsql/data74"?
kill -9 old postmaster
pg_ctl start
... fails with
Dec 29 16:34:12 penguin postgres[5395]: [1-1] FATAL: pre-existing
shared memory block (key 5432001, ID 0) is still in use
Dec 29 16:34:12 penguin postgres[5395]: [1-2] HINT: If you're sure
there are no old server processes still running, remove the shared
memory block with the command "ipcrm",
Dec 29 16:34:12 penguin postgres[5395]: [1-3] or just delete the file
"/var/lib/pgsql/data74/postmaster.pid" .

rm /var/lib/pgsql/data74/postmaster.pid
pg_ctl start

If those were the only steps taken, then the old postmaster's child
backends would still have been running. At that point you're pretty
well hosed because there are two independent sets of backends with
separate shared memory blocks, and any subsequent transaction status
updates from the old backends could overwrite pg_clog data from the
new ones.

So I now think it was operator error and not a software bug at all.
You need to educate whoever did this about proper database shutdown
procedures.

As a Postgres maintainer, the only thing that troubles me about this
is that the error messages from the failed postmaster start attempts
could be read as having encouraged the operator to do exactly the
worst possible things. I'm cc'ing this back to pgsql-general to see
if anyone has any thoughts about rewording these messages. In
particular it seems like the HINT for the second failure is really
disastrous; it should tell you to kill off the old backends, not to
zap the lockfile.

regards, tom lane

PS: do you know why the database was being shut down in the first place?
Was there a pre-existing problem?

PPS: at this point I think we've learned all we can from your database,
so you can go ahead and repair the damage:
delete from pg_class where ctid = '(27,43)';
should do it, at least for the one known problem. I'd encourage you
to do what you can to look for other inconsistencies that may have been
introduced.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Richard Huxton]

On Tuesday 20 January 2004 19:08, Tom Lane wrote:

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

Dec 29 16:33:44 penguin postgres[5379]: [1-1] FATAL: lock file
"/var/lib/pgsql/data74/postmaster.pid" already exists
Dec 29 16:33:44 penguin postgres[5379]: [1-2] HINT: Is another
postmaster (PID 1714) running in data directory "/var/lib/pgsql/data74"?

Dec 29 16:34:12 penguin postgres[5395]: [1-1] FATAL: pre-existing
shared memory block (key 5432001, ID 0) is still in use
Dec 29 16:34:12 penguin postgres[5395]: [1-2] HINT: If you're sure
there are no old server processes still running, remove the shared
memory block with the command "ipcrm",
Dec 29 16:34:12 penguin postgres[5395]: [1-3] or just delete the file
"/var/lib/pgsql/data74/postmaster.pid" .

As a Postgres maintainer, the only thing that troubles me about this
is that the error messages from the failed postmaster start attempts
could be read as having encouraged the operator to do exactly the
worst possible things. I'm cc'ing this back to pgsql-general to see
if anyone has any thoughts about rewording these messages. In
particular it seems like the HINT for the second failure is really
disastrous; it should tell you to kill off the old backends, not to
zap the lockfile.

Should we not support something like "pg_ctl cleanup" which does one or more
(as necessary) of:
1. kills the backends
2. runs ipcrm
3. rm postmater.pid
Why leave it to the operator at all?

Actually, maybe it should be "pg_ctl check" and be able to support a wider
range of checks/fixes.

--
Richard Huxton
Archonet Ltd

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

http://archives.postgresql.org

[Tom Lane]

Richard Huxton <de*@archonet.c om> writes:

On Tuesday 20 January 2004 19:08, Tom Lane wrote:

... the error messages from the failed postmaster start attempts
could be read as having encouraged the operator to do exactly the
worst possible things. I'm cc'ing this back to pgsql-general to see
if anyone has any thoughts about rewording these messages. In
particular it seems like the HINT for the second failure is really
disastrous; it should tell you to kill off the old backends, not to
zap the lockfile.

Should we not support something like "pg_ctl cleanup" which does one or more
(as necessary) of:
1. kills the backends
2. runs ipcrm
3. rm postmater.pid
Why leave it to the operator at all?

Well, given that the proximate cause of Jared's whole problem was
incorrect use of pg_ctl (viz, "pg_ctl stop" when "pg_ctl stop -m fast"
was wanted), I'm not sure that adding an even-more-destructive stop mode
to pg_ctl would have helped. Someone who doesn't know when to use -m fast
isn't likely to know when to use this either.

Also, I doubt that pg_ctl can do a better job of this than the existing
code in the postmaster. The reason that code failed in this case was
that the safety interlocks were deliberately defeated (by removing the
lockfile). There isn't anything that pg_ctl could do that wouldn't be
subject to similar problems.

So I still think what we mostly need is to improve the messages to not
encourage people to do something wrong.

regards, tom lane

PS: this whole saga is an excellent illustration of the reasons for the
rule "don't kill -9 the postmaster" ...

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html