Re: [SQL] Convert int to hex
Fernando Grijalba wrote: I want to be able to change an int4 from a sequence and store it as varchar in the database as a hex number. Is this possible? Try the to_hex() function - in the "Functions and operators" chapter of the manual - "Strings" section. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Convert int to hex
Thank you, I found out after I posted the message. I did read the docs but must have looked somewhere else and I search the lists for hex only and it did not bring the answer back. It wasn't until I search for int4 to hex that I was able to find the answer. Thank you again. Fernando On 6/1/05, Tony Wasson <[EMAIL PROTECTED]> wrote: > On 6/1/05, Fernando Grijalba <[EMAIL PROTECTED]> wrote: > > I want to be able to change an int4 from a sequence and store it as > > varchar in the database as a hex number. > > > > Is this possible? > > > > Thank you, > > > > Fernando > > Sure you can go from integer to hex and back... > http://www.varlena.com/varlena/GeneralBits/104.php > > Here's the example queries to get you started. > >=# select to_hex(11); > to_hex > > b >(1 row) > >=# select x'ab'::integer; > int4 >-- > 171 >(1 row) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 2713
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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?
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:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 3:11 PM To: [EMAIL PROTECTED]; [email protected]; 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: [email protected]; '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?
Re: [SQL] [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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Thursday, June 02, 2005 3:29 PM To: [EMAIL PROTECTED] Cc: [email protected]; '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 [EMAIL PROTECTED])
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27
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:[EMAIL PROTECTED] |Sent: Donnerstag, 01. Jänner 2004 11:09 |To: 'Richard Huxton' |Cc: [email protected]; '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: [EMAIL PROTECTED] |[mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton |Sent: Thursday, June 02, 2005 3:29 PM |To: [EMAIL PROTECTED] |Cc: [email protected]; '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 |[EMAIL PROTECTED]) | ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] index row size 2728 exceeds btree maximum, 2713
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:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 4:44 PM To: [EMAIL PROTECTED]; [email protected]; 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, January 01, 2004 3:14 PM To: Ramakrishnan Muralidharan; [email protected]; '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:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 3:11 PM To: [EMAIL PROTECTED]; [email protected]; 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: [email protected]; '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?
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27
On Thu, Jun 02, 2005 at 14:08:54 +0200, KÖPFERL Robert <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] index row size 2728 exceeds btree maximum, 2713
On Thu, Jun 02, 2005 at 17:48:47 +0530, Dinesh Pandey <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] getting details about integrity constraint violation
Hi, how would I find out details about for example what constraint was violated by an insert statement? The SQL state tells me, that a unique constraint was violated, but it doesn't say which one. I cannot sensibly react to such errors if I don't know what exactly happened. I'd like to avoid parsing the text error message because it can be different depending on the LC_MESSAGES the server / libpq runs with. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
[SQL] Splitting a table for performance reasons
Hi, I've got a table whose records are more less big. There's however jus one Int-column changed frequently. According to postgres' MVCC a whole record gets written, even if just one bit was changed. I think of splitting the table now in two parts, connected via the former PK. so like: a|b|data | othercol | int ->a|b| data|othercol + a|int Will this solve my ongoing performance problem? Or is the overhead for referencial intergrity and system columns bigger than what this gives? BTW: How can I find out how many bytes a record consumes (having just fixed size data)? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] getting details about integrity constraint violation
Markus Bertheau <[EMAIL PROTECTED]> writes: > how would I find out details about for example what constraint was > violated by an insert statement? You can't, at the moment, except by parsing the text message. The "error fields" facility in the FE/BE protocol could be extended in that direction, and I think there's already been some discussion about it; but no one has stepped up with a concrete proposal, much less volunteered to do the work ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27
On 6/2/05, Richard Huxton 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 ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] index row size 2728 exceeds btree maximum, 27
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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] index row size 2728 exceeds btree maximum, 27
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] index row size 2728 exceeds btree maximum, 27
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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
