From dpandey@secf.com Thu Jun 2 09:48:49 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 06CB052926; Thu, 2 Jun 2005 06:48:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23987-07; Thu, 2 Jun 2005 09:48:47 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id E510852923; Thu, 2 Jun 2005 06:48:45 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v8.0.2.R) with ESMTP id md50001005986.msg; Thu, 02 Jun 2005 02:48:43 -0700 Reply-To: From: "Dinesh Pandey" To: "'Ramakrishnan Muralidharan'" , , "'PostgreSQL'" Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Date: Thu, 1 Jan 2004 15:13:48 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C3D079.DD6B68D0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcVnQWMS5ZegD05mSvW9iHeSQj1olwAFBl9wZcJ2XzA= In-reply-to: <02767D4600E59A4487233B23AEF5C5992A408F@blrmail1.aus.pervasive.com> X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Thu, 02 Jun 2005 02:48:43 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDAV-Processed: mail.secf.com, Thu, 02 Jun 2005 02:48:45 -0700 Message-Id: <20050602094845.E510852923@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.874 tagged_above=0 required=5 tests=AWL, DATE_IN_PAST_96_XX, FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200506/65 X-Sequence-Number: 78773 This is a multi-part message in MIME format. ------=_NextPart_000_002E_01C3D079.DD6B68D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, One of the columns in primary key is of type "TEXT". I am able to insert with small data, but for around 3000 characters it's failing. How to handle that? Thanks Dinesh Pandey _____ From: Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com] Sent: Thursday, June 02, 2005 3:11 PM To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index width exceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE -----------+-----------------------+----------- Column | Type -----------+-----------------------+----------- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error "index row size 2728 exceeds btree maximum, 2713" How to solve this problem? ------=_NextPart_000_002E_01C3D079.DD6B68D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

One of the columns in primary key = is of type “TEXT”. I am able to insert with small data, but for = around 3000 characters it’s failing. How to handle = that?

Thanks
Dinesh Pandey

From: = Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com]
Sent: Thursday, June 02, = 2005 3:11 PM
To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL
Subject: RE: [SQL] index = row size 2728 exceeds btree maximum, 2713<= /p>

= Hi,

= The issue looks like your Index width exceeds the maximum width = of the index key limit, Please review the keys used in the = index.<= /p>

Regards,<= /p>

R.Muralidharan<= /p>

<= /p>

-----Original = Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey
Sent: Thursday, June 02, = 2005 12:35 PM
To: = pgsql-general@postgresql.org; 'PostgreSQL'
Subject: [SQL] index row = size 2728 exceeds btree maximum, 2713<= /p>
TABLE =

-----------+-----------------------+-----------=

Column   |         Type

-----------+-----------------------+-----------=

scan_id   | bigint           &= nbsp;

host_ip   | character = varying(15)

port_num | integer           =

plugin_id | integer             &= nbsp;

severity | character varying(50) =

data      | = text     &n= bsp;

Indexes:

    "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, = data)

=

On inserting record I am getting = this error “index row size 2728 exceeds btree maximum, = 2713”

=
How to solve this problem?

------=_NextPart_000_002E_01C3D079.DD6B68D0-- From dpandey@secf.com Thu Jun 2 10:13:51 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0991852819; Thu, 2 Jun 2005 07:13:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35253-02; Thu, 2 Jun 2005 10:13:42 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id 9FCC75280A; Thu, 2 Jun 2005 07:13:40 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v8.0.2.R) with ESMTP id md50001006163.msg; Thu, 02 Jun 2005 03:13:59 -0700 Reply-To: From: "Dinesh Pandey" To: "'Richard Huxton'" Cc: , "'PostgreSQL'" Subject: Re: index row size 2728 exceeds btree maximum, 2713 Date: Thu, 1 Jan 2004 15:39:06 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcVnWkV1Mm76j8ipSz6UvMf8+l3BQmXC9A+w In-reply-to: <429ED859.1070308@archonet.com> X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Thu, 02 Jun 2005 03:13:59 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDAV-Processed: mail.secf.com, Thu, 02 Jun 2005 03:14:04 -0700 Message-Id: <20050602101340.9FCC75280A@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.882 tagged_above=0 required=5 tests=AWL, DATE_IN_PAST_96_XX, FORGED_RCVD_HELO, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200506/73 X-Sequence-Number: 78781 I am inserting some log messages in the column "data". (Basically I am inserting records from reading an xml file) In the PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) data is of type TEXT and can contain long string values. The question is how to remove this error "index row size 2728 exceeds btree maximum, 2713" by increasing the btree size? The big problem is "I can not add any additional column in this table." Thanks Dinesh Pandey -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton Sent: Thursday, June 02, 2005 3:29 PM To: dpandey@secf.com Cc: pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [GENERAL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey wrote: > -----------+-----------------------+----------- > Column | Type > -----------+-----------------------+----------- > scan_id | bigint > host_ip | character varying(15) > port_num | integer > plugin_id | integer > severity | character varying(50) > data | text > > Indexes: > "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, > plugin_id, severity, data) > > On inserting record I am getting this error "index row size 2728 exceeds > btree maximum, 2713" Well - the error message is clear enough. The question is, what to do. Without knowing what the table "means", it's difficult to say what the primary-key should be, but it seems unlikely to include an unlimited-length text-field called "data". If the data itself doesn't offer any suitable candidate keys (as can well be the case) then common practice is to generate a unique number and use that as an ID - in PostgreSQL's case by use of the SERIAL pseudo-type. Does that help? -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings From dpandey@secf.com Thu Jun 2 07:10:27 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BFD3352867; Thu, 2 Jun 2005 04:10:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89961-03; Thu, 2 Jun 2005 07:10:22 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id 512DC5286E; Thu, 2 Jun 2005 04:10:21 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v8.0.2.R) with ESMTP id md50001005212.msg; Thu, 02 Jun 2005 00:10:00 -0700 Reply-To: From: "Dinesh Pandey" To: , "'PostgreSQL'" Subject: index row size 2728 exceeds btree maximum, 2713 Date: Thu, 2 Jun 2005 12:34:58 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C5676F.837179D0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcVnQWMS5ZegD05mSvW9iHeSQj1olw== X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Thu, 02 Jun 2005 00:10:00 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDAV-Processed: mail.secf.com, Thu, 02 Jun 2005 00:10:01 -0700 Message-Id: <20050602071021.512DC5286E@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.431 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_90_100, HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200506/57 X-Sequence-Number: 78765 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C5676F.837179D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit TABLE -----------+-----------------------+----------- Column | Type -----------+-----------------------+----------- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error "index row size 2728 exceeds btree maximum, 2713" How to solve this problem? ------=_NextPart_000_000F_01C5676F.837179D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

TABLE =

-----------+-----------------------+-----------=

Column = | = Type

-----------+-----------------------+-----------=

scan_id | = bigint &= nbsp;

host_ip | character = varying(15)

port_num | = integer =

plugin_id | integer = &= nbsp;

severity | character varying(50) =

data | = text &nb= sp;

Indexes:

"pk_scanned_port_info" = PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, = data)

On inserting record I am getting = this error “index row size 2728 exceeds btree maximum, = 2713”

How to solve this problem?

------=_NextPart_000_000F_01C5676F.837179D0-- From ramakrishnanm@pervasive-postgres.com Thu Jun 2 09:41:02 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5CD895283A; Thu, 2 Jun 2005 06:41:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20331-09; Thu, 2 Jun 2005 09:40:45 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id 6951E52800; Thu, 2 Jun 2005 06:40:37 -0300 (ADT) Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Thu, 02 Jun 2005 04:40:40 -0500 Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 2 Jun 2005 04:40:39 -0500 content-class: urn:content-classes:message Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C56757.21D9FB62" Date: Thu, 2 Jun 2005 15:10:37 +0530 Message-ID: <02767D4600E59A4487233B23AEF5C5992A408F@blrmail1.aus.pervasive.com> X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] index row size 2728 exceeds btree maximum, 2713 Thread-Index: AcVnQWMS5ZegD05mSvW9iHeSQj1olwAFBl9w From: "Ramakrishnan Muralidharan" To: , , "PostgreSQL" X-OriginalArrivalTime: 02 Jun 2005 09:40:39.0838 (UTC) FILETIME=[2315E3E0:01C56757] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/62 X-Sequence-Number: 78770 This is a multi-part message in MIME format. ------_=_NextPart_001_01C56757.21D9FB62 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 The issue looks like your Index width exceeds the maximum = width of the index key limit, Please review the keys used in the index. =20 Regards, R.Muralidharan =20 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE=20 -----------+-----------------------+----------- Column | Type =20 -----------+-----------------------+----------- scan_id | bigint =20 host_ip | character varying(15)=20 port_num | integer =20 plugin_id | integer =20 severity | character varying(50)=20 data | text =20 Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, = port_num, plugin_id, severity, data) =20 =20 On inserting record I am getting this error "index row size 2728 exceeds = btree maximum, 2713" =20 How to solve this problem? =20 =20 =20 ------_=_NextPart_001_01C56757.21D9FB62 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

= Hi,

&nbs= p; =20 The issue looks like your Index width exceeds the maximum width=20 of the index = key limit,=20 Please review the keys used in the index.

Regards,

R.Muralidharan

-----Original Message-----
From:=20 pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On=20 Behalf Of Dinesh Pandey
Sent: Thursday, June 02, 2005 = 12:35=20 PM
To: pgsql-general@postgresql.org; = 'PostgreSQL'
Subject:=20 [SQL] index row size 2728 exceeds btree maximum, = 2713

TABLE=20

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

= Column  =20 |        =20 Type

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

scan_id  =20 |=20 = bigint           &= nbsp;   =20

host_ip  =20 | character varying(15)

port_num |=20 = integer           =    =20

plugin_id = |=20 integer =20 =             &= nbsp;

severity = |=20 character varying(50)

data     =20 |=20 = text           &nb= sp;     =20

Indexes:

   =20 "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, = plugin_id, severity, data)

On = inserting=20 record I am getting this error “index=20 row size 2728 exceeds btree maximum, 2713”

How=20 to solve this problem?

------_=_NextPart_001_01C56757.21D9FB62-- From dev@archonet.com Thu Jun 2 10:01:07 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8F2B2528D1; Thu, 2 Jun 2005 07:01:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29564-03; Thu, 2 Jun 2005 10:00:58 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 4633D528C5; Thu, 2 Jun 2005 07:00:57 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id D0A6841534F; Thu, 2 Jun 2005 11:00:12 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 13C4F15ED6; Thu, 2 Jun 2005 10:58:52 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17369-08; Thu, 2 Jun 2005 10:58:49 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7FF8915ED5; Thu, 2 Jun 2005 10:58:49 +0100 (BST) Message-ID: <429ED859.1070308@archonet.com> Date: Thu, 02 Jun 2005 10:58:49 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: dpandey@secf.com Cc: pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: index row size 2728 exceeds btree maximum, 2713 References: <20050602071021.512DC5286E@svr1.postgresql.org> In-Reply-To: <20050602071021.512DC5286E@svr1.postgresql.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/68 X-Sequence-Number: 78776 Dinesh Pandey wrote: > -----------+-----------------------+----------- > Column | Type > -----------+-----------------------+----------- > scan_id | bigint > host_ip | character varying(15) > port_num | integer > plugin_id | integer > severity | character varying(50) > data | text > > Indexes: > "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, > plugin_id, severity, data) > > On inserting record I am getting this error "index row size 2728 exceeds > btree maximum, 2713" Well - the error message is clear enough. The question is, what to do. Without knowing what the table "means", it's difficult to say what the primary-key should be, but it seems unlikely to include an unlimited-length text-field called "data". If the data itself doesn't offer any suitable candidate keys (as can well be the case) then common practice is to generate a unique number and use that as an ID - in PostgreSQL's case by use of the SERIAL pseudo-type. Does that help? -- Richard Huxton Archonet Ltd From kleptog@svana.org Thu Jun 2 10:12:51 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 33419528C1; Thu, 2 Jun 2005 07:12:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35253-01; Thu, 2 Jun 2005 10:12:47 +0000 (GMT) Received: from svana.org (svana.org [203.20.62.76]) by svr1.postgresql.org (Postfix) with ESMTP id 68D3752825; Thu, 2 Jun 2005 07:12:44 -0300 (ADT) Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian)) id 1Ddmgm-00051y-00; Thu, 02 Jun 2005 20:12:36 +1000 Date: Thu, 2 Jun 2005 12:12:36 +0200 From: Martijn van Oosterhout To: Dinesh Pandey Cc: 'Ramakrishnan Muralidharan' , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Message-ID: <20050602101236.GC16799@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Dinesh Pandey , 'Ramakrishnan Muralidharan' , pgsql-general@postgresql.org, 'PostgreSQL' References: <02767D4600E59A4487233B23AEF5C5992A408F@blrmail1.aus.pervasive.com> <20050602094845.E510852923@svr1.postgresql.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="3uo+9/B/ebqu+fSQ" Content-Disposition: inline In-Reply-To: <20050602094845.E510852923@svr1.postgresql.org> User-Agent: Mutt/1.3.28i X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6 X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522 48A7 6394 F08A 0DC6 7BE6 X-PGP-Key-URL: X-Warning: Normally my messages are signed, but not on the ozTiVo list due to legacy application support X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/72 X-Sequence-Number: 78780 --3uo+9/B/ebqu+fSQ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Jan 01, 2004 at 03:13:48PM +0530, Dinesh Pandey wrote: > One of the columns in primary key is of type "TEXT". I am able to insert > with small data, but for around 3000 characters it's failing. How to hand= le > that? Easy, btree indexes can't handle data with more that 2713 bytes. You need to decide if having a single index on all your columns is actually what you want. Depending on your queries it may not even be used. Hope this helps, > From: Ramakrishnan Muralidharan > [mailto:ramakrishnanm@pervasive-postgres.com]=20 > Sent: Thursday, June 02, 2005 3:11 PM > To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL > Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 >=20 > =20 >=20 > Hi, >=20 > =20 >=20 > The issue looks like your Index width exceeds the maximum wi= dth > of the index key limit, Please review the keys used in the index. >=20 > =20 >=20 > Regards, >=20 > R.Muralidharan >=20 > =20 >=20 > =20 >=20 > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey > Sent: Thursday, June 02, 2005 12:35 PM > To: pgsql-general@postgresql.org; 'PostgreSQL' > Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 >=20 > TABLE=20 >=20 > -----------+-----------------------+----------- >=20 > Column | Type =20 >=20 > -----------+-----------------------+----------- >=20 > scan_id | bigint =20 >=20 > host_ip | character varying(15)=20 >=20 > port_num | integer =20 >=20 > plugin_id | integer =20 >=20 > severity | character varying(50)=20 >=20 > data | text =20 >=20 > Indexes: >=20 > "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, > plugin_id, severity, data) >=20 > =20 >=20 > =20 >=20 > On inserting record I am getting this error "index row size 2728 exceeds > btree maximum, 2713" >=20 > =20 >=20 > How to solve this problem? >=20 > =20 >=20 > =20 >=20 > =20 >=20 --=20 Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. --3uo+9/B/ebqu+fSQ Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQFCntuUY5Twig3Ge+YRAlihAKChY17HSt7Xn1DyuHUp6aZ7981lZQCgqJCD Fal150UEC3YcZoUR8qMtqos= =u64w -----END PGP SIGNATURE----- --3uo+9/B/ebqu+fSQ-- From dev@archonet.com Thu Jun 2 10:45:23 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DFACC52894; Thu, 2 Jun 2005 07:45:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42119-02; Thu, 2 Jun 2005 10:45:21 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 12A16528D0; Thu, 2 Jun 2005 07:45:18 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id C66ED40DF86; Thu, 2 Jun 2005 11:45:13 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id DB01F15ED6; Thu, 2 Jun 2005 11:43:48 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18469-01; Thu, 2 Jun 2005 11:43:46 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 46F5A15ED5; Thu, 2 Jun 2005 11:43:46 +0100 (BST) Message-ID: <429EE2E1.30904@archonet.com> Date: Thu, 02 Jun 2005 11:43:45 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: dpandey@secf.com Cc: pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 References: <20050602101340.9FCC75280A@svr1.postgresql.org> In-Reply-To: <20050602101340.9FCC75280A@svr1.postgresql.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/75 X-Sequence-Number: 78783 Dinesh Pandey wrote: > I am inserting some log messages in the column "data". (Basically I am > inserting records from reading an xml file) > > In the PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, > data) data is of type TEXT and can contain long string values. I'm still not convinced that "data" makes a sensible part of the primary key. Can you give an example of "data" and explain why the whole value determines unique-ness? > The question is how to remove this error "index row size 2728 exceeds btree > maximum, 2713" by increasing the btree size? > > The big problem is "I can not add any additional column in this table." Why not? -- Richard Huxton Archonet Ltd From ramakrishnanm@pervasive-postgres.com Thu Jun 2 11:13:51 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E3FC952802; Thu, 2 Jun 2005 08:13:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48940-09; Thu, 2 Jun 2005 11:13:49 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id 8C12B52816; Thu, 2 Jun 2005 08:13:37 -0300 (ADT) Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Thu, 02 Jun 2005 06:13:40 -0500 Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 2 Jun 2005 06:13:38 -0500 content-class: urn:content-classes:message Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C56764.1D66E1EF" Date: Thu, 2 Jun 2005 16:43:33 +0530 Message-ID: <02767D4600E59A4487233B23AEF5C5992A4090@blrmail1.aus.pervasive.com> X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] index row size 2728 exceeds btree maximum, 2713 Thread-Index: AcVnQWMS5ZegD05mSvW9iHeSQj1olwAFBl9wZcJ2XzBlv12uwA== From: "Ramakrishnan Muralidharan" To: , , "PostgreSQL" X-OriginalArrivalTime: 02 Jun 2005 11:13:39.0204 (UTC) FILETIME=[20A59440:01C56764] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/78 X-Sequence-Number: 78786 This is a multi-part message in MIME format. ------_=_NextPart_001_01C56764.1D66E1EF Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=20 =20 It is not advisable to add a variable length data field in = the Index key, since it is very difficult predict the size of the field = which may vary from record to record. =20 =20 are you included this field for Full text search on data = field? =20 Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, January 01, 2004 3:14 PM To: Ramakrishnan Muralidharan; pgsql-general@postgresql.org; = 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, =20 One of the columns in primary key is of type "TEXT". I am able to insert = with small data, but for around 3000 characters it's failing. How to = handle that? =20 Thanks Dinesh Pandey _____ =20 From: Ramakrishnan Muralidharan = [mailto:ramakrishnanm@pervasive-postgres.com]=20 Sent: Thursday, June 02, 2005 3:11 PM To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 =20 Hi, =20 The issue looks like your Index width exceeds the maximum = width of the index key limit, Please review the keys used in the index. =20 Regards, R.Muralidharan =20 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE=20 -----------+-----------------------+----------- Column | Type =20 -----------+-----------------------+----------- scan_id | bigint =20 host_ip | character varying(15)=20 port_num | integer =20 plugin_id | integer =20 severity | character varying(50)=20 data | text =20 Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, = port_num, plugin_id, severity, data) =20 =20 On inserting record I am getting this error "index row size 2728 exceeds = btree maximum, 2713" =20 How to solve this problem? =20 =20 =20 ------_=_NextPart_001_01C56764.1D66E1EF Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi =

&nbs= p; =20 It is not advisable to add a variable length data field in the Index = key, since=20 it is very difficult predict the size of the field which may vary from = record to=20 record.

&nbs= p; =20 are you included this field for Full text search on data=20 field?

Regards,

R.Muralidharan

-----Original Message-----
From:=20 pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On=20 Behalf Of Dinesh Pandey
Sent: Thursday, January 01, 2004 = 3:14=20 PM
To: Ramakrishnan Muralidharan; = pgsql-general@postgresql.org;=20 'PostgreSQL'
Subject: Re: [SQL] index row size 2728 exceeds = btree=20 maximum, 2713

Hi,

One of = the columns=20 in primary key is of type “TEXT”. I am able to insert with = small data, but for=20 around 3000 characters it’s failing. How to handle=20 that?

Thanks
Dinesh=20 Pandey

From:=20 Ramakrishnan Muralidharan = [mailto:ramakrishnanm@pervasive-postgres.com]=20
Sent: Thursday, = June 02,=20 2005 3:11 PM
To:=20 dpandey@secf.com;=20 pgsql-general@postgresql.org; PostgreSQL
Subject: RE: [SQL] index row = size 2728=20 exceeds btree maximum, 2713

        =20 Hi,

           = =20 The issue looks like your Index width exceeds the maximum width=20 of the = index key=20 limit, Please review the keys used in the index.

Regards,

R.Muralidharan



-----Original=20 Message-----
From:=20 pgsql-sql-owner@postgresql.org=20 [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh = Pandey
Sent: Thursday, June 02, 2005 = 12:35=20 PM
To:=20 pgsql-general@postgresql.org; 'PostgreSQL'
Subject: [SQL] index row size = 2728=20 exceeds btree maximum, 2713

TABLE=20

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

= Column  =20 |        =20 Type       =

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

scan_id  =20 |=20 = bigint           &= nbsp;   =20

host_ip  =20 | character varying(15)

port_num |=20 = integer           =    =20

plugin_id = |=20 integer =20 =             &= nbsp;

severity = |=20 character varying(50)

data     =20 | text           &nbs= p;     =20

Indexes:

   =20 "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, = port_num,=20 plugin_id, severity, data)

On=20 inserting record I am getting this error = “index=20 row size 2728 exceeds btree maximum, 2713”

How=20 to solve this problem?

= ------_=_NextPart_001_01C56764.1D66E1EF-- From robert.koepferl@sonorys.at Thu Jun 2 12:09:20 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1AAB252806 for ; Thu, 2 Jun 2005 09:09:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82518-01 for ; Thu, 2 Jun 2005 12:09:10 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id 162A652894 for ; Thu, 2 Jun 2005 09:09:07 -0300 (ADT) Received: (qmail-ldap/ctrl 63131 invoked from network); 2 Jun 2005 12:09:07 -0000 Received: from unknown (HELO io.sonorys.at) ([213.235.243.42]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with SMTP for ; 2 Jun 2005 12:09:07 -0000 Received: from neptun.sonorys.at (neptun.sonorys.at [172.24.1.17]) by io.sonorys.at (8.11.7+Sun/8.11.7) with ESMTP id j52C8tG07841; Thu, 2 Jun 2005 14:09:03 +0200 (MEST) Received: by neptun.sonorys.at with Internet Mail Service (5.5.2653.19) id ; Thu, 2 Jun 2005 14:08:55 +0200 Message-ID: From: =?ISO-8859-1?Q?K=D6PFERL_Robert?= To: "'dpandey@secf.com'" , "'Richard Huxton'" Cc: pgsql-general@postgresql.org, "'PostgreSQL'" Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 Date: Thu, 2 Jun 2005 14:08:54 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200506/82 X-Sequence-Number: 78790 To me it seems that the definer of this table missed the concept index = ... or the concept database One usually looks up data using a key, but if the whole row is the key, = what data shall be looked up. So short story long: Remove data from your index. The data column = seems like the data to be looked up using the key=20 (scan_id, host_ip, port_num, plugin_id, severity) or even less. Postgres is able to take several indices over distinct columns into = account. Thus reducing the possible candidates to a hand full. So several indices are also an option |-----Original Message----- |From: Dinesh Pandey [mailto:dpandey@secf.com] |Sent: Donnerstag, 01. J=E4nner 2004 11:09 |To: 'Richard Huxton' |Cc: pgsql-general@postgresql.org; 'PostgreSQL' |Subject: Re: [SQL] [GENERAL] index row size 2728 exceeds btree = maximum, |2713 | | |I am inserting some log messages in the column "data". (Basically I am |inserting records from reading an xml file) | |In the PRIMARY KEY, btree (scan_id, host_ip, port_num,=20 |plugin_id, severity, |data) data is of type TEXT and can contain long string values. | |The question is how to remove this error "index row size 2728=20 |exceeds btree |maximum, 2713" by increasing the btree size? | |The big problem is "I can not add any additional column in this = table." | |Thanks |Dinesh Pandey | |-----Original Message----- |From: pgsql-general-owner@postgresql.org |[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard = Huxton |Sent: Thursday, June 02, 2005 3:29 PM |To: dpandey@secf.com |Cc: pgsql-general@postgresql.org; 'PostgreSQL' |Subject: Re: [GENERAL] index row size 2728 exceeds btree maximum, 2713 | |Dinesh Pandey wrote: |> -----------+-----------------------+----------- |> Column | Type =20 |> -----------+-----------------------+----------- |> scan_id | bigint =20 |> host_ip | character varying(15)=20 |> port_num | integer =20 |> plugin_id | integer =20 |> severity | character varying(50)=20 |> data | text =20 |>=20 |> Indexes: |> "pk_scanned_port_info" PRIMARY KEY, btree (scan_id,=20 |host_ip, port_num, |> plugin_id, severity, data) |>=20 |> On inserting record I am getting this error "index row size=20 |2728 exceeds |> btree maximum, 2713" | |Well - the error message is clear enough. The question is, what to do. | |Without knowing what the table "means", it's difficult to say what the = |primary-key should be, but it seems unlikely to include an=20 |unlimited-length text-field called "data". | |If the data itself doesn't offer any suitable candidate keys (as can=20 |well be the case) then common practice is to generate a unique number=20 |and use that as an ID - in PostgreSQL's case by use of the SERIAL=20 |pseudo-type. | |Does that help? |--=20 | Richard Huxton | Archonet Ltd | |---------------------------(end of=20 |broadcast)--------------------------- |TIP 7: don't forget to increase your free space map settings | | | |---------------------------(end of=20 |broadcast)--------------------------- |TIP 2: you can get off all lists at once with the unregister command | (send "unregister YourEmailAddressHere" to=20 |majordomo@postgresql.org) | From dpandey@secf.com Thu Jun 2 12:23:26 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 623B45286E; Thu, 2 Jun 2005 09:23:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81553-05; Thu, 2 Jun 2005 12:23:22 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id 17F4F52825; Thu, 2 Jun 2005 09:23:21 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v8.0.2.R) with ESMTP id md50001007291.msg; Thu, 02 Jun 2005 05:23:43 -0700 Reply-To: From: "Dinesh Pandey" To: "'Ramakrishnan Muralidharan'" , , "'PostgreSQL'" Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Date: Thu, 2 Jun 2005 17:48:47 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01C5679B.563FA2D0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcVnQWMS5ZegD05mSvW9iHeSQj1olwAFBl9wZcJ2XzBlv12uwIZb344g In-reply-to: <02767D4600E59A4487233B23AEF5C5992A4090@blrmail1.aus.pervasive.com> X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Thu, 02 Jun 2005 05:23:43 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDAV-Processed: mail.secf.com, Thu, 02 Jun 2005 05:23:43 -0700 Message-Id: <20050602122321.17F4F52825@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.35 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE, MSGID_FROM_MTA_ID X-Spam-Level: * X-Archive-Number: 200506/83 X-Sequence-Number: 78791 This is a multi-part message in MIME format. ------=_NextPart_000_0079_01C5679B.563FA2D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Yes I am storing some "error messages" in data column, and the PK columns are party of search criteria. Thanks Dinesh Pandey _____ From: Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com] Sent: Thursday, June 02, 2005 4:44 PM To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi It is not advisable to add a variable length data field in the Index key, since it is very difficult predict the size of the field which may vary from record to record. are you included this field for Full text search on data field? Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, January 01, 2004 3:14 PM To: Ramakrishnan Muralidharan; pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, One of the columns in primary key is of type "TEXT". I am able to insert with small data, but for around 3000 characters it's failing. How to handle that? Thanks Dinesh Pandey _____ From: Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com] Sent: Thursday, June 02, 2005 3:11 PM To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index width exceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE -----------+-----------------------+----------- Column | Type -----------+-----------------------+----------- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error "index row size 2728 exceeds btree maximum, 2713" How to solve this problem? ------=_NextPart_000_0079_01C5679B.563FA2D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Yes I am = storing some “error messages” in data column, and the PK columns are = party of search criteria.

<= /o:p>

Thanks
Dinesh Pandey

From: = Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com]
Sent: Thursday, June 02, = 2005 4:44 PM
To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL
Subject: RE: [SQL] index = row size 2728 exceeds btree maximum, 2713<= /p>

= Hi

= It is not advisable to add a variable length data field in the Index = key, since it is very difficult predict the size of the field which may vary from = record to record. <= /p>

= are you included this field for Full text search on data = field?<= /p>

Regards,<= /p>

R.Muralidharan<= /p>

-----Original = Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey
Sent: Thursday, January = 01, 2004 3:14 PM
To: Ramakrishnan = Muralidharan; pgsql-general@postgresql.org; 'PostgreSQL'
Subject: Re: [SQL] index = row size 2728 exceeds btree maximum, 2713<= /p>

Hi,

One of the columns in primary key = is of type “TEXT”. I am able to insert with small data, but for = around 3000 characters it’s failing. How to handle = that?

=
Thanks
Dinesh Pandey

From: = Ramakrishnan Muralidharan [mailto:ramakrishnanm@pervasive-postgres.com]
Sent: Thursday, June 02, = 2005 3:11 PM
To: dpandey@secf.com; pgsql-general@postgresql.org; PostgreSQL
Subject: RE: [SQL] index = row size 2728 exceeds btree maximum, 2713<= /p>

      =    Hi,

      =        The issue looks like your Index width exceeds the maximum width = of the index key limit, Please review the keys used in the = index.<= /p>

Regards,<= /p>

R.Muralidharan<= /p>

     <= /p>

-----Original = Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Dinesh Pandey
Sent: Thursday, June 02, = 2005 12:35 PM
To: = pgsql-general@postgresql.org; 'PostgreSQL'
Subject: [SQL] index row = size 2728 exceeds btree maximum, 2713<= /p>
TABLE =

-----------+-----------------------+-----------=

Column   |         Type

-----------+-----------------------+-----------=

scan_id   | bigint           &= nbsp;

host_ip   | character = varying(15)

port_num | integer           =

plugin_id | integer             &= nbsp;

severity | character varying(50) =

data      | = text     &n= bsp;

Indexes:

    "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, = data)

=

On inserting record I am getting = this error “index row size 2728 exceeds btree maximum, = 2713”

=
How to solve this problem?

------=_NextPart_000_0079_01C5679B.563FA2D0-- From bruno@wolff.to Thu Jun 2 12:32:52 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9C4C052862 for ; Thu, 2 Jun 2005 09:32:52 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83571-09 for ; Thu, 2 Jun 2005 12:32:44 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 69C955281C for ; Thu, 2 Jun 2005 09:32:42 -0300 (ADT) Received: (qmail 7734 invoked by uid 500); 2 Jun 2005 12:31:50 -0000 Date: Thu, 2 Jun 2005 07:31:50 -0500 From: Bruno Wolff III To: =?iso-8859-1?Q?K=D6PFERL?= Robert Cc: "'dpandey@secf.com'" , 'Richard Huxton' , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: index row size 2728 exceeds btree maximum, 27 Message-ID: <20050602123150.GB6785@wolff.to> Mail-Followup-To: Bruno Wolff III , =?iso-8859-1?Q?K=D6PFERL?= Robert , "'dpandey@secf.com'" , 'Richard Huxton' , pgsql-general@postgresql.org, 'PostgreSQL' References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/85 X-Sequence-Number: 78793 On Thu, Jun 02, 2005 at 14:08:54 +0200, K�PFERL Robert wrote: > To me it seems that the definer of this table missed the concept index ... > or the concept database > One usually looks up data using a key, but if the whole row is the key, what > data shall be looked up. You sometimes do want to make a whole role a key to avoid duplicate keys. A common case is when you use a table to connect two other tables with a many to many relation. It would be rare to want to do that with large text values though. From bruno@wolff.to Thu Jun 2 12:36:16 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0775452875 for ; Thu, 2 Jun 2005 09:36:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84120-10 for ; Thu, 2 Jun 2005 12:36:14 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 6822652825 for ; Thu, 2 Jun 2005 09:36:12 -0300 (ADT) Received: (qmail 7866 invoked by uid 500); 2 Jun 2005 12:35:20 -0000 Date: Thu, 2 Jun 2005 07:35:20 -0500 From: Bruno Wolff III To: Dinesh Pandey Cc: 'Ramakrishnan Muralidharan' , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: index row size 2728 exceeds btree maximum, 2713 Message-ID: <20050602123520.GC6785@wolff.to> Mail-Followup-To: Bruno Wolff III , Dinesh Pandey , 'Ramakrishnan Muralidharan' , pgsql-general@postgresql.org, 'PostgreSQL' References: <02767D4600E59A4487233B23AEF5C5992A4090@blrmail1.aus.pervasive.com> <20050602122321.17F4F52825@svr1.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050602122321.17F4F52825@svr1.postgresql.org> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/87 X-Sequence-Number: 78795 On Thu, Jun 02, 2005 at 17:48:47 +0530, Dinesh Pandey wrote: > Yes I am storing some "error messages" in data column, and the PK columns > are party of search criteria. If you need to be able to search based on the entire stored error message, than you might try adding an indexed hash column to the table and using that to speed up searches. You can still compare the full string in case you have a hash collision, but those should be very rare. From dev@archonet.com Thu Jun 2 13:00:32 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 989D65281C; Thu, 2 Jun 2005 10:00:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94692-01; Thu, 2 Jun 2005 13:00:23 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 0758C5289D; Thu, 2 Jun 2005 10:00:20 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 26893415684; Thu, 2 Jun 2005 14:00:14 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id A438415F0F; Thu, 2 Jun 2005 13:56:01 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27244-09; Thu, 2 Jun 2005 13:55:56 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 2D9B815ED9; Thu, 2 Jun 2005 13:40:54 +0100 (BST) Message-ID: <429EFE55.3050601@archonet.com> Date: Thu, 02 Jun 2005 13:40:53 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?K=D6PFERL_Robert?= Cc: "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/92 X-Sequence-Number: 78800 K=D6PFERL Robert wrote: > To me it seems that the definer of this table missed the concept index = =2E.. > or the concept database > One usually looks up data using a key, but if the whole row is the key,= what > data shall be looked up. > So short story long: Remove data from your index. The data column seem= s > like the data to be looked up using the key=20 > (scan_id, host_ip, port_num, plugin_id, severity) or even less. > Postgres is able to take several indices over distinct columns into acc= ount. > Thus reducing the possible candidates to a hand full. > So several indices are also an option Actually, Dinesh didn't mention he was using this for the speed of=20 lookup. He'd defined the columns as being the PRIMARY KEY, presumably=20 because he feels they are/should be unique. Given that they are rows=20 from a logfile, I'm not convinced this is the case. --=20 Richard Huxton Archonet Ltd From systemguards@gmail.com Thu Jun 2 15:48:21 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 21B3B52918 for ; Thu, 2 Jun 2005 12:48:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38461-06 for ; Thu, 2 Jun 2005 15:48:17 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.195]) by svr1.postgresql.org (Postfix) with ESMTP id 1AFAE52931 for ; Thu, 2 Jun 2005 12:48:16 -0300 (ADT) Received: by rproxy.gmail.com with SMTP id i8so259715rne for ; Thu, 02 Jun 2005 08:48:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=WvEd0mMZBfwLyUrxn0KjhCrDGipHdDnnxJ9qKfugjl8PihjcySzzWeEF3cdyuZ16tYkwnPYmG4TuUm/PMa6Tg1eZV6Gn3WbBF4StU5VBv25/2PN93aelgJGIYcgM2KSi90x/WXa2YiBtmVZWs5nvdvHC836rB9VECPonWr99TlY= Received: by 10.38.92.63 with SMTP id p63mr416409rnb; Thu, 02 Jun 2005 08:48:21 -0700 (PDT) Received: by 10.38.97.37 with HTTP; Thu, 2 Jun 2005 08:48:21 -0700 (PDT) Message-ID: Date: Thu, 2 Jun 2005 10:48:21 -0500 From: Jaime Casanova Reply-To: Jaime Casanova To: Richard Huxton Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 Cc: =?ISO-8859-1?Q?K=D6PFERL_Robert?= , "dpandey@secf.com" , pgsql-general@postgresql.org, PostgreSQL In-Reply-To: <429EFE55.3050601@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <429EFE55.3050601@archonet.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.341 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/101 X-Sequence-Number: 78809 On 6/2/05, Richard Huxton wrote: > K=D6PFERL Robert wrote: > > To me it seems that the definer of this table missed the concept index = ... > > or the concept database > > One usually looks up data using a key, but if the whole row is the key,= what > > data shall be looked up. >=20 > > So short story long: Remove data from your index. The data column seem= s > > like the data to be looked up using the key > > (scan_id, host_ip, port_num, plugin_id, severity) or even less. > > Postgres is able to take several indices over distinct columns into acc= ount. > > Thus reducing the possible candidates to a hand full. > > So several indices are also an option >=20 > Actually, Dinesh didn't mention he was using this for the speed of > lookup. He'd defined the columns as being the PRIMARY KEY, presumably > because he feels they are/should be unique. Given that they are rows > from a logfile, I'm not convinced this is the case. >=20 If this a log he will need a timestamp field to be usefull, making that field part of the primary key and letting the data out of the primary has more sense to me. --=20 regards, Jaime Casanova (DBA: DataBase Aniquilator ;) From bruno@wolff.to Thu Jun 2 16:36:37 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7C37C52906 for ; Thu, 2 Jun 2005 13:36:37 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52665-06 for ; Thu, 2 Jun 2005 16:36:35 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id CBD55528CB for ; Thu, 2 Jun 2005 13:36:33 -0300 (ADT) Received: (qmail 23661 invoked by uid 500); 2 Jun 2005 16:35:42 -0000 Date: Thu, 2 Jun 2005 11:35:42 -0500 From: Bruno Wolff III To: Richard Huxton Cc: =?iso-8859-1?Q?K=D6PFERL?= Robert , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 Message-ID: <20050602163542.GA23150@wolff.to> Mail-Followup-To: Bruno Wolff III , Richard Huxton , =?iso-8859-1?Q?K=D6PFERL?= Robert , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' References: <429EFE55.3050601@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <429EFE55.3050601@archonet.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/106 X-Sequence-Number: 78814 On Thu, Jun 02, 2005 at 13:40:53 +0100, Richard Huxton wrote: > > Actually, Dinesh didn't mention he was using this for the speed of > lookup. He'd defined the columns as being the PRIMARY KEY, presumably > because he feels they are/should be unique. Given that they are rows > from a logfile, I'm not convinced this is the case. Even for case you could still use hashes. The odds of a false collision using SHA-1 are so small that some sort of disaster is more likely. Another possibility is if there are a fixed number of possible messages, is that they could be entered in their own table with a serail PK and the other table could reference the PK. From dev@archonet.com Thu Jun 2 17:05:23 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 19C5E52887; Thu, 2 Jun 2005 14:05:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61286-03; Thu, 2 Jun 2005 17:05:14 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 1336752896; Thu, 2 Jun 2005 14:05:13 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 7AC2C4164B9; Thu, 2 Jun 2005 18:05:05 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 3FBC015ED5; Thu, 2 Jun 2005 18:01:49 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01971-09; Thu, 2 Jun 2005 18:01:44 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 4E61415F17; Thu, 2 Jun 2005 18:00:18 +0100 (BST) Message-ID: <429F3B21.9020209@archonet.com> Date: Thu, 02 Jun 2005 18:00:17 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruno Wolff III Cc: =?ISO-8859-1?Q?K=D6PFERL_Robert?= , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 References: <429EFE55.3050601@archonet.com> <20050602163542.GA23150@wolff.to> In-Reply-To: <20050602163542.GA23150@wolff.to> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/107 X-Sequence-Number: 78815 Bruno Wolff III wrote: > On Thu, Jun 02, 2005 at 13:40:53 +0100, > Richard Huxton wrote: > >>Actually, Dinesh didn't mention he was using this for the speed of >>lookup. He'd defined the columns as being the PRIMARY KEY, presumably >>because he feels they are/should be unique. Given that they are rows >>from a logfile, I'm not convinced this is the case. > > > Even for case you could still use hashes. The odds of a false collision > using SHA-1 are so small that some sort of disaster is more likely. > Another possibility is if there are a fixed number of possible messages, > is that they could be entered in their own table with a serail PK and > the other table could reference the PK. Certainly, but if the text in the logfile row is the same, then hashing isn't going to make a blind bit of difference. That's the root of my concern, and something only Dinesh knows. -- Richard Huxton Archonet Ltd From bruno@wolff.to Thu Jun 2 19:34:20 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5238352838 for ; Thu, 2 Jun 2005 16:34:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95107-08 for ; Thu, 2 Jun 2005 19:34:14 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 31A89528CB for ; Thu, 2 Jun 2005 16:34:13 -0300 (ADT) Received: (qmail 2959 invoked by uid 500); 2 Jun 2005 19:33:24 -0000 Date: Thu, 2 Jun 2005 14:33:24 -0500 From: Bruno Wolff III To: Richard Huxton Cc: =?iso-8859-1?Q?K=D6PFERL?= Robert , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 Message-ID: <20050602193324.GA2861@wolff.to> Mail-Followup-To: Bruno Wolff III , Richard Huxton , =?iso-8859-1?Q?K=D6PFERL?= Robert , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' References: <429EFE55.3050601@archonet.com> <20050602163542.GA23150@wolff.to> <429F3B21.9020209@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <429F3B21.9020209@archonet.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/112 X-Sequence-Number: 78820 On Thu, Jun 02, 2005 at 18:00:17 +0100, Richard Huxton wrote: > > Certainly, but if the text in the logfile row is the same, then hashing > isn't going to make a blind bit of difference. That's the root of my > concern, and something only Dinesh knows. Sure it is. Because the hash can be used in the primary key instead of of the error message which should reduce the size of the key enough that he can use a btree index. From dev@archonet.com Fri Jun 3 07:25:21 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 820E852867; Fri, 3 Jun 2005 04:25:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61983-09; Fri, 3 Jun 2005 07:25:20 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id CCA0052824; Fri, 3 Jun 2005 04:25:19 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 62D4340DCB9; Fri, 3 Jun 2005 08:25:16 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id C880615ED6; Fri, 3 Jun 2005 08:22:23 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22228-03; Fri, 3 Jun 2005 08:22:21 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 32B8C15ED5; Fri, 3 Jun 2005 08:22:21 +0100 (BST) Message-ID: <42A0052C.6040505@archonet.com> Date: Fri, 03 Jun 2005 08:22:20 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruno Wolff III Cc: =?ISO-8859-1?Q?K=D6PFERL_Robert?= , "'dpandey@secf.com'" , pgsql-general@postgresql.org, 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 27 References: <429EFE55.3050601@archonet.com> <20050602163542.GA23150@wolff.to> <429F3B21.9020209@archonet.com> <20050602193324.GA2861@wolff.to> In-Reply-To: <20050602193324.GA2861@wolff.to> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/136 X-Sequence-Number: 78844 Bruno Wolff III wrote: > On Thu, Jun 02, 2005 at 18:00:17 +0100, > Richard Huxton wrote: > >>Certainly, but if the text in the logfile row is the same, then hashing >>isn't going to make a blind bit of difference. That's the root of my >>concern, and something only Dinesh knows. > > > Sure it is. Because the hash can be used in the primary key instead of > of the error message which should reduce the size of the key enough > that he can use a btree index. Sorry - obviously not being clear. Since he's using the index via a primary key he'll need the columns that key is over to be unique. If the columns fail that test in the real world, hashing will replace the index-size error with an "unable to insert duplicates" error. -- Richard Huxton Archonet Ltd