Question about ALTER TABLE DROP CONSTRAINT on 9.1 -- psql crashes

From: Greg Williamson <gwilliamson39(at)yahoo(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Question about ALTER TABLE DROP CONSTRAINT on 9.1 -- psql crashes
Date: 2012-08-10 00:04:42
Message-ID: 1344557082.43876.YahooMailNeo@web125906.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

We have a small database (few hundred megs of data, lass than half that in indexes) that suffers from index bloat. Currently we handle this with an hourly REINDEX command. This works but causes some small issues, so I have been expploring other methods.

When I try to to drop constraints (typically UNIQUE) I sometimes get a crash of the psql client at the ALTER TABLE X DROP CONSTRAINT, and sometimes after I recreate the constraint at a commit. Transcript below:

usher=# BEGIN;
BEGIN
gusher=# ALTER TABLE ourcodes DROP CONSTRAINT IF EXISTS ourcodes_pkey CASCADE;
ALTER TABLE
gusher=# ALTER TABLE ourcodes ADD CONSTRAINT ourcodes_pkey PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "ourcodes_pkey" for table "ourcodes"
ALTER TABLE
gusher=# COMMIT;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
gusher=# \d ourcodes
                       Unlogged table "public.ourcodes"
   Column    |  Type  |                        Modifiers
-------------+--------+---------------------------------------------------------
 id          | bigint | not null default nextval('ourcodes_id_seq'::regclass)
 source      | bigint | not null
 destination | bigint |
 started_on  | bigint | default (date_part('epoch'::text, now()))::bigint
 type        | text   |
 status      | text   |
 updated_on  | bigint | default date_part('epoch'::text, now())
 worker      | text   |
 version     | text   |
Indexes:
    "ourcodes_pkey" PRIMARY KEY, btree (id)
    "ourcodes_source" btree (source, type)

gusher=# select count(*) from ourcodes;LTER TABLE
gusher=# ALTER TABLE ourcodes ADD CONSTRAINT ourcodes_pkey PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "ourcodes_pkey" for table "ourcodes"
ALTER TABLE
gusher=# COMMIT;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
gusher=# \d ourcodes
                       Unlogged table "public.ourcodes"
   Column    |  Type  |                        Modifiers
-------------+--------+---------------------------------------------------------
 id          | bigint | not null default nextval('ourcodes_id_seq'::regclass)
 source      | bigint | not null
 destination | bigint |
 started_on  | bigint | default (date_part('epoch'::text, now()))::bigint
 type        | text   |
 status      | text   |
 updated_on  | bigint | default date_part('epoch'::text, now())
 worker      | text   |
 version     | text   |
Indexes:
    "ourcodes_pkey" PRIMARY KEY, btree (id)
    "ourcodes_source" btree (source, type)

gusher=# select count(*) from ourcodes;
 count
-------
  1626
(1 row)

 count
-------
  1626
(1 row)

gusher=# select version();
                                                    version                                                     
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row)

 ========

This table is only updated a few times a minute (mostly inserts and deletes, some updates), others get hit a lot more (many times a second). pgsql is the same version and I am on the same server.

Is this known / documented ? Any suggestions (other than not to do this ; -- )

Thanks,

Greg Williamson

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2012-08-10 00:19:44 Re: Question about ALTER TABLE DROP CONSTRAINT on 9.1 -- psql crashes
Previous Message Kevin Grittner 2012-08-09 17:11:29 Re: Wiki link for max_connections? (Fwd: Re: [ADMIN] PostgreSQL oom_adj postmaster process to -17)