Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-21 Thread Gregory Stark
"Michael Fuhr" <[EMAIL PROTECTED]> writes: > On Wed, Feb 20, 2008 at 12:21:03PM +0100, Francisco Olarte Sanz wrote: >> On Wednesday 20 February 2008, Gregory Stark wrote: >> >> > Unless you need cryptographic security I would not suggest using MD5. MD5 >> > is intentionally designed to take a sub

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Tom Lane
Gregory Stark <[EMAIL PROTECTED]> writes: > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >> Return type of hash* functions is just 32 bits. I wonder if that's wide >> enough >> to avoid accidental collisions? Depends on the application of course... > Oh, I missed that you were suggesting a UN

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Michael Fuhr
On Wed, Feb 20, 2008 at 12:21:03PM +0100, Francisco Olarte Sanz wrote: > On Wednesday 20 February 2008, Gregory Stark wrote: > > > Unless you need cryptographic security I would not suggest using MD5. MD5 > > is intentionally designed to take a substantial amount of CPU resources to > > calculate.

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Gregory Stark
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >> >>> As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a >>> pretty >>> good work-around. >> >> Unle

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Heikki Linnakangas
Gregory Stark wrote: "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a pretty good work-around. Unless you need cryptographic security I would not suggest using MD5. MD5 is intentionally designed to take a substantial amo

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Francisco Olarte Sanz
On Wednesday 20 February 2008, Gregory Stark wrote: > Unless you need cryptographic security I would not suggest using MD5. MD5 > is intentionally designed to take a substantial amount of CPU resources to > calculate. I thought it was the exact opposite, quoting from RFC1321: The MD5 algorithm i

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Gregory Stark
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > As others have pointed out, CREATE UNIQUE INDEX i ON ((md5(column)) is a > pretty > good work-around. Unless you need cryptographic security I would not suggest using MD5. MD5 is intentionally designed to take a substantial amount of CPU resourc

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-20 Thread Heikki Linnakangas
Juho Saarikko wrote: I suggest implementing unique hash indexes and automatically creating one (and turning the b-tree index into a non-unique one) when a large value is inserted to fix this. Alternatively, fix b-trees so they can handle large values; however, a hash index should be far more effi

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Juho Saarikko
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Juho Saarikko wrote: While I didn't test, I'd imagine that this would also mean that any attempt to insert such values to an already unique column would fail. Works here in 8.3: test=> crea

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Kris Jurka
On Mon, 18 Feb 2008, Bruce Momjian wrote: Juho Saarikko wrote: While I didn't test, I'd imagine that this would also mean that any attempt to insert such values to an already unique column would fail. Works here in 8.3: test=> insert into test values (repeat('a', 5) || 'b');

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes: > Juho Saarikko wrote: >> While I didn't test, I'd imagine that this would also mean that any attempt >> to insert such values to an already unique column would fail. > Works here in 8.3: > test=> create table test (x text unique); > N

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Bruce Momjian
Juho Saarikko wrote: > While I didn't test, I'd imagine that this would also mean that any attempt > to insert such values to an already unique column would fail. Works here in 8.3: test=> create table test (x text unique); NOTICE: CREATE TABLE / UNIQUE will create implic

Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Gregory Stark
"Juho Saarikko" <[EMAIL PROTECTED]> writes: > It is propably impossible to fix this in a simple way, since it is an > inherent result of the underlying storage specification rather than a mere > programming error, so the documentation needs to be updated to warn about > this. Point taken. > I s

[BUGS] BUG #3965: UNIQUE constraint fails on long column values

2008-02-18 Thread Juho Saarikko
The following bug has been logged online: Bug reference: 3965 Logged by: Juho Saarikko Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3RC2 Operating system: Linux Description:UNIQUE constraint fails on long column values Details: It is impossible to add an U