pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] index row size 2728 exceeds btree maximum, 2713
18+ messages / 7 participants
[nested] [flat]
* index row size 2728 exceeds btree maximum, 2713
@ 2005-06-02 07:04 Dinesh Pandey <dpandey@secf.com>
2005-06-02 09:58 ` Re: index row size 2728 exceeds btree maximum, 2713 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 18+ messages in thread
From: Dinesh Pandey @ 2005-06-02 07:04 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index row size 2728 exceeds btree maximum, 2713
2005-06-02 07:04 index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
@ 2005-06-02 09:58 ` Richard Huxton <dev@archonet.com>
2004-01-01 10:09 ` Re: index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
0 siblings, 1 reply; 18+ messages in thread
From: Richard Huxton @ 2005-06-02 09:58 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index row size 2728 exceeds btree maximum, 2713
2005-06-02 07:04 index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
2005-06-02 09:58 ` Re: index row size 2728 exceeds btree maximum, 2713 Richard Huxton <dev@archonet.com>
@ 2004-01-01 10:09 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 10:43 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 18+ messages in thread
From: Dinesh Pandey @ 2004-01-01 10:09 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
2005-06-02 07:04 index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
2005-06-02 09:58 ` Re: index row size 2728 exceeds btree maximum, 2713 Richard Huxton <dev@archonet.com>
2004-01-01 10:09 ` Re: index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
@ 2005-06-02 10:43 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 18+ messages in thread
From: Richard Huxton @ 2005-06-02 10:43 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
@ 2005-06-02 09:40 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2004-01-01 09:43 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
0 siblings, 1 reply; 18+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-06-02 09:40 UTC (permalink / raw)
To: dpandey@secf.com; pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
2005-06-02 09:40 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
@ 2004-01-01 09:43 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 10:12 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 1 reply; 18+ messages in thread
From: Dinesh Pandey @ 2004-01-01 09:43 UTC (permalink / raw)
To: 'Ramakrishnan Muralidharan' <ramakrishnanm@pervasive-postgres.com>; pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
2005-06-02 09:40 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2004-01-01 09:43 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
@ 2005-06-02 10:12 ` Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 18+ messages in thread
From: Martijn van Oosterhout @ 2005-06-02 10:12 UTC (permalink / raw)
To: Dinesh Pandey <dpandey@secf.com>; +Cc: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>; pgsql-sql
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 handle
> 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]
> 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?
>
>
>
>
>
>
>
--
Martijn van Oosterhout <kleptog@svana.org> 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
@ 2005-06-02 11:13 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-06-02 12:18 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
0 siblings, 1 reply; 18+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-06-02 11:13 UTC (permalink / raw)
To: dpandey@secf.com; pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 2713
2005-06-02 11:13 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
@ 2005-06-02 12:18 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 12:35 ` Re: index row size 2728 exceeds btree maximum, 2713 Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 18+ messages in thread
From: Dinesh Pandey @ 2005-06-02 12:18 UTC (permalink / raw)
To: 'Ramakrishnan Muralidharan' <ramakrishnanm@pervasive-postgres.com>; pgsql-general@postgresql.org; pgsql-sql
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?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index row size 2728 exceeds btree maximum, 2713
2005-06-02 11:13 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-06-02 12:18 ` Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
@ 2005-06-02 12:35 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 18+ messages in thread
From: Bruno Wolff III @ 2005-06-02 12:35 UTC (permalink / raw)
To: Dinesh Pandey <dpandey@secf.com>; +Cc: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>; pgsql-sql
On Thu, Jun 02, 2005 at 17:48:47 +0530,
Dinesh Pandey <dpandey@secf.com> 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
@ 2005-06-02 12:08 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:31 ` Re: index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 18+ messages in thread
From: KÖPFERL Robert @ 2005-06-02 12:08 UTC (permalink / raw)
To: 'dpandey@secf.com' <dpandey@secf.com>; 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
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
(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änner 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,
|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
|
|
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 2: you can get off all lists at once with the unregister command
| (send "unregister YourEmailAddressHere" to
|majordomo@postgresql.org)
|
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-06-02 12:31 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 18+ messages in thread
From: Bruno Wolff III @ 2005-06-02 12:31 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: 'dpandey@secf.com' <dpandey@secf.com>; Richard Huxton <dev@archonet.com>; pgsql-sql
On Thu, Jun 02, 2005 at 14:08:54 +0200,
KÖPFERL Robert <robert.koepferl@sonorys.at> 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-06-02 12:40 ` Richard Huxton <dev@archonet.com>
2005-06-02 15:48 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Jaime Casanova <systemguards@gmail.com>
2005-06-02 16:35 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
1 sibling, 2 replies; 18+ messages in thread
From: Richard Huxton @ 2005-06-02 12:40 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: 'dpandey@secf.com' <dpandey@secf.com>; pgsql-sql
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.
> So short story long: Remove data from your index. The data column seems
> 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 account.
> 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
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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
@ 2005-06-02 15:48 ` Jaime Casanova <systemguards@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: Jaime Casanova @ 2005-06-02 15:48 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; dpandey@secf.com <dpandey@secf.com>; pgsql-sql
On 6/2/05, Richard Huxton <dev@archonet.com> wrote:
> 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.
>
> > So short story long: Remove data from your index. The data column seems
> > 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 account.
> > 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
> 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.
>
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.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
@ 2005-06-02 16:35 ` Bruno Wolff III <bruno@wolff.to>
2005-06-02 17:00 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 18+ messages in thread
From: Bruno Wolff III @ 2005-06-02 16:35 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; 'dpandey@secf.com' <dpandey@secf.com>; pgsql-sql
On Thu, Jun 02, 2005 at 13:40:53 +0100,
Richard Huxton <dev@archonet.com> 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 16:35 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
@ 2005-06-02 17:00 ` Richard Huxton <dev@archonet.com>
2005-06-02 19:33 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 18+ messages in thread
From: Richard Huxton @ 2005-06-02 17:00 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; 'dpandey@secf.com' <dpandey@secf.com>; pgsql-sql
Bruno Wolff III wrote:
> On Thu, Jun 02, 2005 at 13:40:53 +0100,
> Richard Huxton <dev@archonet.com> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 16:35 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-02 17:00 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
@ 2005-06-02 19:33 ` Bruno Wolff III <bruno@wolff.to>
2005-06-03 07:22 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 18+ messages in thread
From: Bruno Wolff III @ 2005-06-02 19:33 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; 'dpandey@secf.com' <dpandey@secf.com>; pgsql-sql
On Thu, Jun 02, 2005 at 18:00:17 +0100,
Richard Huxton <dev@archonet.com> 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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] index row size 2728 exceeds btree maximum, 27
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 16:35 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-02 17:00 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 19:33 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
@ 2005-06-03 07:22 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 18+ messages in thread
From: Richard Huxton @ 2005-06-03 07:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: KÖPFERL Robert <robert.koepferl@sonorys.at>; 'dpandey@secf.com' <dpandey@secf.com>; pgsql-sql
Bruno Wolff III wrote:
> On Thu, Jun 02, 2005 at 18:00:17 +0100,
> Richard Huxton <dev@archonet.com> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2005-06-03 07:22 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-02 07:04 index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey <dpandey@secf.com>
2005-06-02 09:58 ` Richard Huxton <dev@archonet.com>
2004-01-01 10:09 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 10:43 ` Richard Huxton <dev@archonet.com>
2005-06-02 09:40 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2004-01-01 09:43 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 10:12 ` Martijn van Oosterhout <kleptog@svana.org>
2005-06-02 11:13 Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-06-02 12:18 ` Dinesh Pandey <dpandey@secf.com>
2005-06-02 12:35 ` Bruno Wolff III <bruno@wolff.to>
2005-06-02 12:08 Re: [SQL] index row size 2728 exceeds btree maximum, 27 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-06-02 12:31 ` Re: index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-02 12:40 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 15:48 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Jaime Casanova <systemguards@gmail.com>
2005-06-02 16:35 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-02 17:00 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
2005-06-02 19:33 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Bruno Wolff III <bruno@wolff.to>
2005-06-03 07:22 ` Re: [SQL] index row size 2728 exceeds btree maximum, 27 Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox