Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id C2C481DEF5C3 for ; Thu, 9 Aug 2012 21:04:59 -0300 (ADT) Received: from nm37-vm6.bullet.mail.ne1.yahoo.com ([98.138.229.134]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Szcim-0005z5-U0 for pgsql-admin@postgresql.org; Fri, 10 Aug 2012 00:04:58 +0000 Received: from [98.138.90.55] by nm37.bullet.mail.ne1.yahoo.com with NNFMP; 10 Aug 2012 00:04:43 -0000 Received: from [98.138.87.8] by tm8.bullet.mail.ne1.yahoo.com with NNFMP; 10 Aug 2012 00:04:43 -0000 Received: from [127.0.0.1] by omp1008.mail.ne1.yahoo.com with NNFMP; 10 Aug 2012 00:04:43 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 41072.10852.bm@omp1008.mail.ne1.yahoo.com Received: (qmail 43989 invoked by uid 60001); 10 Aug 2012 00:04:42 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1344557082; bh=VmAc/lGZQkf1qU8M5D9C4xBcXLZBE03DiZiGHg585vs=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=P95IPa6QyYEtDygW8WTziw58/kLcJYjlrw1wJgY0N9AthSqQ06/jGHAvpdQ57JId4MxFiBgFSEiFVI9xVMFKYGaa4yq4DNwK0Rf7aeTYaW/inOjI6Px1SrfNBDCzPV94LBXS+2+zfRXgvCIwV1VXk9QPX3tjaIvqu9qaCUPtwsc= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=MnRjjy5oPjC/nUIuAapA2R1YkI0x66pv1ffM6uaYuBQ3GyjvkOL0/ypy1ZLXDc/pW3uAeyH7PVfVm32hAlybe8PZfGQi1VBC+G5vJmrBVWT7tM2vPqh2d78dsD0LUP2Ls8lZoEKN1tk2Bfb3R9Y46C5HDvzxBigm1fs4GAHaCaw=; X-YMail-OSG: j5SfUjUVM1nKzY5ybjKuwlzV0_U9clqNVTSNIOu1M5LW2lc gSHCM4iUS8lzaH6wtY.0xrjqHSVvPNBoDy1H5XJGN2jqZmyarqrxgNBdBf4X I.XO1w05GPjfNEWf259vcPKcOrKb1HDQ.LxzShr_BWZRFi9FWxehtKjPZDeb 4EsgyJEjpuQgwaHlWs6FDoUWz43LmHyTP7zT5AmUrew2RJxDUVJXL.s9RajV xeVt5SPJmkxhtrCJDVR.0RYohYYO0h_uRXm17OFQNsnP1DzT4_MbCYQveEUT RIDondNae5PYhLRfc2V7HyGE0k314P3nC5yy3KVCKcKGW3QtwA8TtXDIX46j b8Cu1DLoOEJuJvu3V_slVkCcghlzxC2K0vmVJHdBmKz_AAS5g89C7vGuB2H4 YPBS6zW8Jbl_vyNbyicfFQUhjNGoyqENo_G1Q356e1yLE6ZLlHcgazN0- Received: from [199.9.251.45] by web125906.mail.ne1.yahoo.com via HTTP; Thu, 09 Aug 2012 17:04:42 PDT X-Mailer: YahooMailWebService/0.8.120.356233 Message-ID: <1344557082.43876.YahooMailNeo@web125906.mail.ne1.yahoo.com> Date: Thu, 9 Aug 2012 17:04:42 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: Question about ALTER TABLE DROP CONSTRAINT on 9.1 -- psql crashes To: "pgsql-admin@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="452707994-626500990-1344557082=:43876" X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201208/67 X-Sequence-Number: 39888 --452707994-626500990-1344557082=:43876 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable 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 ho= urly REINDEX command. This works but causes some small issues, so I have be= en expploring other methods. When I try to to drop constraints (typically UNIQUE) I sometimes get a cras= h of the psql client at the ALTER TABLE X DROP CONSTRAINT, and sometimes af= ter I recreate the constraint at a commit. Transcript below: usher=3D# BEGIN; BEGIN gusher=3D# ALTER TABLE ourcodes DROP CONSTRAINT IF EXISTS ourcodes_pkey CAS= CADE; ALTER TABLE gusher=3D# ALTER TABLE ourcodes ADD CONSTRAINT ourcodes_pkey PRIMARY KEY (i= d); NOTICE: =A0ALTER TABLE / ADD PRIMARY KEY will create implicit index "ourcod= es_pkey" for table "ourcodes" ALTER TABLE gusher=3D# COMMIT; FATAL: =A0terminating connection due to administrator command server closed the connection unexpectedly =A0 =A0 =A0 =A0 This probably means the server terminated abnormally =A0 =A0 =A0 =A0 before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. gusher=3D# \d ourcodes =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Unlogged table "public.ourco= des" =A0 =A0Column =A0 =A0| =A0Type =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Modifiers -------------+--------+----------------------------------------------------= ----- =A0id =A0 =A0 =A0 =A0 =A0| bigint | not null default nextval('ourcodes_id_s= eq'::regclass) =A0source =A0 =A0 =A0| bigint | not null =A0destination | bigint | =A0started_on =A0| bigint | default (date_part('epoch'::text, now()))::bigi= nt =A0type =A0 =A0 =A0 =A0| text =A0 | =A0status =A0 =A0 =A0| text =A0 | =A0updated_on =A0| bigint | default date_part('epoch'::text, now()) =A0worker =A0 =A0 =A0| text =A0 | =A0version =A0 =A0 | text =A0 | Indexes: =A0 =A0 "ourcodes_pkey" PRIMARY KEY, btree (id) =A0 =A0 "ourcodes_source" btree (source, type) gusher=3D# select count(*) from ourcodes;LTER TABLE gusher=3D# ALTER TABLE ourcodes ADD CONSTRAINT ourcodes_pkey PRIMARY KEY (i= d); NOTICE: =A0ALTER TABLE / ADD PRIMARY KEY will create implicit index "ourcod= es_pkey" for table "ourcodes" ALTER TABLE gusher=3D# COMMIT; FATAL: =A0terminating connection due to administrator command server closed the connection unexpectedly =A0 =A0 =A0 =A0 This probably means the server terminated abnormally =A0 =A0 =A0 =A0 before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. gusher=3D# \d ourcodes =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Unlogged table "public.ourco= des" =A0 =A0Column =A0 =A0| =A0Type =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0Modifiers -------------+--------+----------------------------------------------------= ----- =A0id =A0 =A0 =A0 =A0 =A0| bigint | not null default nextval('ourcodes_id_s= eq'::regclass) =A0source =A0 =A0 =A0| bigint | not null =A0destination | bigint | =A0started_on =A0| bigint | default (date_part('epoch'::text, now()))::bigi= nt =A0type =A0 =A0 =A0 =A0| text =A0 | =A0status =A0 =A0 =A0| text =A0 | =A0updated_on =A0| bigint | default date_part('epoch'::text, now()) =A0worker =A0 =A0 =A0| text =A0 | =A0version =A0 =A0 | text =A0 | Indexes: =A0 =A0 "ourcodes_pkey" PRIMARY KEY, btree (id) =A0 =A0 "ourcodes_source" btree (source, type) gusher=3D# select count(*) from ourcodes; =A0count ------- =A0 1626 (1 row) =A0count ------- =A0 1626 (1 row) gusher=3D# select version(); =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 version =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0 ---------------------------------------------------------------------------= ------------------------------------- =A0PostgreSQL 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) =A0=3D=3D=3D=3D=3D=3D=3D=3D 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 --452707994-626500990-1344557082=:43876 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
We have a small database (few h= undred megs of data, lass than half that in indexes) that suffers from inde= x bloat. Currently we handle this with an hourly REINDEX command. This work= s but causes some small issues, so I have been expploring other methods.

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

ushe= r=3D# BEGIN;
BEGIN<= /font>
gusher=3D# ALT= ER TABLE ourcodes DROP CONSTRAINT IF EXISTS ourcodes_pkey CASCADE;
ALTER TABLE
gusher=3D# ALTER TABLE o= urcodes ADD CONSTRAINT ourcodes_pkey PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PR= IMARY KEY will create implicit index "ourcodes_pkey" for table "ourcodes"
ALTER TABLE
gusher=3D# COMMIT= ;
FATAL:  = ;terminating connection due to administrator command
server closed the connection unexpectedly
=         This probably means the server terminated abnor= mally
  &= nbsp;     before or while processing the request.
The connection to the server = was lost. Attempting reset: Succeeded.
gusher=3D# \d ourcodes
           =            Unlogged table "public.ourcodes"<= /font>
   C= olumn    |  Type  |           =              Modifiers
= -------------+--------+---------------------------------------= ------------------
 id          | bigint | not null default= nextval('ourcodes_id_seq'::regclass)
 source      | bigint | not nul= l
 destin= ation | bigint |
 started_on  | bigint | default (date_part('epoch'::text, now(= )))::bigint
&n= bsp;type        | text   |
 status      | te= xt   |
&n= bsp;updated_on  | bigint | default date_part('epoch'::text, now())
 = worker      | text   |
 version     | text   |
Indexes:<= /div>
    "ourcod= es_pkey" PRIMARY KEY, btree (id)
    "ourcodes_source" btree (source, type)

=
gusher=3D# select count(*)= from ourcodes;LTER TABLE
gusher=3D# ALTER TABLE ourcodes ADD CONSTRAINT ourcodes_pkey PR= IMARY KEY (id);
ALTER TABLE
gusher=3D# COMMIT;
FATA= L:  terminating connection due to administrator command
serv= er closed the connection unexpectedly
       = This probably means the server terminated abnormally
  &nbs= p;     before or while processing the request.
The conn= ection to the server was lost. Attempting reset: Succeeded.
gushe= r=3D# \d ourcodes
             = ;          Unlogged table "public.ourcodes"
<= div>   Column    |  Type  |     &nb= sp;                  Modifiers=
-------------+--------+-----------------------------------------= ----------------
 id          | bigint | not null default nextval('ourcodes_id_seq'::regclass)
&= nbsp;source      | bigint | not null
 destina= tion | bigint |
 started_on  | bigint | default (date_p= art('epoch'::text, now()))::bigint
 type      = ;  | text   |
 status      | text &= nbsp; |
 updated_on  | bigint | default date_part('epoc= h'::text, now())
 worker      | text   |=
 version     | text   |
Indexes:
    "ourcodes_pkey" PRIMARY KEY, btree (id)
&n= bsp;   "ourcodes_source" btree (source, type)

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

&n= bsp;count
-------
  1626
(1 row)

gusher=3D# select version();
              &nb= sp;                     &= nbsp;               version    = ;                     &nb= sp;                     &= nbsp;     
---------------------------------------------------------------= -------------------------------------------------
 PostgreSQL 9.1.4 on x86_64-unknow= n-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
(1 row= )

 =3D=3D=3D=3D=3D=3D=3D=3D

T= his 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 t= he same version and I am on the same server.

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

Greg Williamson

= --452707994-626500990-1344557082=:43876--