[BUGS] BUG #3965: UNIQUE constraint fails on long column values
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 UNIQUE constraint which includes columns with long values. The reason seems to be that UNIQUE is implemented using b-tree index, which cannot handle values longer than 8191 bytes. 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. 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. 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 efficient for this specific case, since the size of a hash is independent of pre-hash data size. Exact error message: ** kuvat=# alter table pictures ADD constraint pic_unique unique (safe); NOTICE: 0: ALTER TABLE / ADD UNIQUE will create implicit index "pic_unique" for table "pictures" LOCATION: DefineIndex, indexcmds.c:434 ERROR: 54000: index row requires 47148 bytes, maximum size is 8191 LOCATION: index_form_tuple, indextuple.c:170 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #3966: problem with implicit cast array parameter
The following bug has been logged online: Bug reference: 3966 Logged by: Pawel Kasperek Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 Operating system: openSuse 10.3 Description:problem with implicit cast array parameter Details: In PostgreSQL 8.2 I can call function: test_func(p1 text[]) as "PERFORM test_func(ARRAY[0,1])". The server could be implicit cast array of integer elements to array of text. In PostgreSQL 8.3 I have error: function test_func(integer[]) does not exist. For me problem is missed implicit casting elements of array to text type. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Bug (#3484) - Missing pg_clog/0AE6
We did some analyzing and found out some mysterious things. First we used the pg_check tool to analyze the table. The result is: # select pgcheck_page( 211593798 ); WARNING: relation 'adresse_080103', tuple (46,11): start of tuple is not aligned properly WARNING: relation 'adresse_080103', tuple (46,11): has t_xmax < t_xmin pgcheck_page -- 9808 (1 row) # select pgcheck_index_full( 211593798 ); NOTICE: name of tested relation: 'adresse_080103': NOTICE: relation 'adresse_080103' doesn't have index pgcheck_index_full 211593798 (1 row) So we looked up the row: SELECT * FROM public.adresse_080103 where ctid = '(46,10)'::tid; and did an pg_filedump on the database file and searched for the data of that recordset. After that recordset we found corrupted data! It seems to be corrupted in the same way as the error before. The dump of the corrupted data is appended. ( We replaced some sensitive data with '*' at the beinning and the end ) We went on with analyzing: - the table was created at 2008/01/03 17:56h - the nightly dump started at 2008/01/03 22:00h - it tried to copy the table 'adresse_080103' at 22:00:08 - the dump crashed at 22:32:10 ( because of the error we reported 2007/12/14; we repaired the database not till 2008/01/11 ) The stat of the database file returns this: File: "/postgres/database/data/base/23144/211593798" Size: 1835008 Blocks: 3592 IO Block: 4096 reguläre Datei Device: 811h/2065d Inode: 18121638Links: 1 Access: (0600/-rw---) Uid: ( 1001/postgres) Gid: (2/ daemon) Access: 2008-02-15 18:19:44.0 +0100 Modify: 2008-01-03 22:00:34.0 +0100 Change: 2008-01-03 22:00:34.0 +0100 We are wondering, that the pg_dump seems to have modified the file. But we think, that the file couldn't be corrupted at this time. Each night from 2008/01/11 (the repair day) up to 2008/02/01 the nightly dump ran successful. Suddenly at 2008/02/02 it crashed. It seems that something corrupted the data without changing the modification time of the file. At the moment we have no idea, how this situation comes. Could you please answer, if the pg_dump modifies the access timestamp in some cases? Thanks in advance, Alex ---(end of broadcast)--- TIP 1: 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: [BUGS] Bug (#3484) - Missing pg_clog/0AE6
Alexandra Nitzschke napsal(a): We went on with analyzing: - the table was created at 2008/01/03 17:56h - the nightly dump started at 2008/01/03 22:00h - it tried to copy the table 'adresse_080103' at 22:00:08 - the dump crashed at 22:32:10 ( because of the error we reported 2007/12/14; we repaired the database not till 2008/01/11 ) The stat of the database file returns this: File: "/postgres/database/data/base/23144/211593798" Size: 1835008 Blocks: 3592 IO Block: 4096 reguläre Datei Device: 811h/2065d Inode: 18121638Links: 1 Access: (0600/-rw---) Uid: ( 1001/postgres) Gid: (2/ daemon) Access: 2008-02-15 18:19:44.0 +0100 Modify: 2008-01-03 22:00:34.0 +0100 Change: 2008-01-03 22:00:34.0 +0100 We are wondering, that the pg_dump seems to have modified the file. Could you please answer, if the pg_dump modifies the access timestamp in some cases? Just a idea that pg_dump invoked checkpoint but I don't expect that table data spent four hour in a buffer cache. Especially in case when max checkpoint_timeout is one hour. Zdenek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
"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 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 efficient for this specific > case, since the size of a hash is independent of pre-hash data size. With expression indexes you can do this yourself with something like CREATE INDEX pk_hash on tab ((hashtext(safe))) We can't do this automatically since it wouldn't enforce the UNIQUE constraint. Conceivably we could actually do something about that but there's nothing like that now. We have hash indexes too but in practice a btree over a hash seems to work just as well or better. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: 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: [BUGS] BUG #3966: problem with implicit cast array parameter
"Pawel Kasperek" <[EMAIL PROTECTED]> writes: > In PostgreSQL 8.2 I can call function: test_func(p1 text[]) as "PERFORM > test_func(ARRAY[0,1])". The server could be implicit cast array of integer > elements to array of text. In PostgreSQL 8.3 I have error: function > test_func(integer[]) does not exist. For me problem is missed implicit > casting elements of array to text type. This is not a bug, it's an intentional change; see the 8.3 release notes. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
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 implicit index "test_x_key"
for table "test"
CREATE TABLE
test=> insert into test values (repeat('a', 5));
INSERT 0 1
Even this works:
test=> insert into test values (repeat('a', 5) || 'b');
I believe the index only indexes 8192 bytes but checks the heap for
longer values to check the full length.
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] BUG #3968: ssh tunnel instructions could use improvement
The following bug has been logged online: Bug reference: 3968 Logged by: Faheem Mitha Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.11 Operating system: Debian etch Description:ssh tunnel instructions could use improvement Details: Hi, Currently http://www.postgresql.org/docs/8.3/static/ssh-tunnels.html has instructions that say to set up a local port forward to do ssh -L 333ssh -L :foo.com:5432 [EMAIL PROTECTED] I think this should be changed to ssh -L :localhost:5432 [EMAIL PROTECTED] The reason is that this assumes the postgres server on foo.com allows connections from foo.com, since trying to connect to port on the local machine using the instructions given in the docs, will attempt to initiate a connection to the postgres server, which will appear to it to be coming from foo.com. However, it appears more likely, and is the Debian default, that the server only allows connections on localhost. This is a major source of potential confusion for people not familar with port forwarding. Also, I'd suggest mentioning that you can put other addresses in place of localhost, but that the database needs to give permission to connect from those addresses, and in particular for ssh -L :localhost:5432 [EMAIL PROTECTED] psql -h localhost -p postgres to work, the database needs to allow a TCP/IP connection from localhost. This seems a pretty standard default, though. Faheem. ---(end of broadcast)--- TIP 1: 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
[BUGS] BUG #3967: EXISTS clause on subquery
The following bug has been logged online:
Bug reference: 3967
Logged by: Bernard Le Jour
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.3
Operating system: i386-apple-darwin8.9.1
Description:EXISTS clause on subquery
Details:
Please see the following SQL:
SELECT CASE WHEN (aropen_doctype='I') THEN 'Invc.'
WHEN (aropen_doctype='D') THEN 'D/M'
WHEN (aropen_doctype='C') THEN 'C/M'
WHEN (aropen_doctype='R') THEN 'C/D'
ELSE 'Misc.'
END AS doctype,
CASE WHEN EXISTS (select * from cohead, aropen where cohead_type =
'C' and aropen_ordernumber = cohead_number and aropen_doctype = 'I') THEN
'R'
ELSE 'N'
END AS type,
AROPEN_PONUMBER,
aropen_docnumber, formatDate(aropen_docdate) AS f_docdate,
CASE WHEN (aropen_doctype='I') THEN formatDate(aropen_duedate)
ELSE ''
END AS f_duedate,
CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_amount)
WHEN (aropen_doctype IN ('C', 'R')) THEN
formatMoney(aropen_amount * -1)
ELSE formatMoney(aropen_amount)
END AS f_amount,
CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_paid)
WHEN (aropen_doctype IN ('C', 'R')) THEN formatMoney(aropen_paid
* -1)
ELSE formatMoney(aropen_paid)
END AS f_applied,
CASE WHEN (aropen_doctype IN ('I', 'D')) THEN
formatMoney(aropen_amount - aropen_paid)
WHEN (aropen_doctype IN ('C', 'R')) THEN
formatMoney((aropen_amount - aropen_paid) * -1)
ELSE formatMoney(aropen_amount - aropen_paid)
END AS f_balance,
CASE WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount -
aropen_paid)
WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount -
aropen_paid) * -1)
ELSE (aropen_amount - aropen_paid)
END AS balance
FROM aropen
WHERE ( (aropen_cust_id=)
AND (aropen_open)
AND ((aropen_amount - aropen_paid) > 0) )
ORDER BY aropen_docdate;
I get 'R' in type all the time. I should get some 'N' and 'R' results, not
all 'R's.
Thanks,
--
Bernard Le Jour
AS Plus Informatique Inc.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
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);
> NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_x_key"
> for table "test"
> CREATE TABLE
> test=> insert into test values (repeat('a', 5));
> INSERT 0 1
That test only works because it's eminently compressible.
The short answer to this bug report is that we're not very concerned
about fixing this because there is seldom a good reason to have an
index (unique or not) on fields that can get so wide. As was already
noted, if you do need a uniqueness check you can easily make a 99.%
solution by indexing the md5 hash (or some similar digest) of the
column. It doesn't really seem worthwhile to expend development work
on something that would benefit so few people.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
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');
This only works because it gets toasted before being put in the index.
Since you've selected something real compressible, you can fit 50k chars
into it.
Kris Jurka
---(end of broadcast)---
TIP 1: 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: [BUGS] BUG #3965: UNIQUE constraint fails on long column values
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=> create table test (x text unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_x_key" for table
"test"
CREATE TABLE
test=> insert into test values (repeat('a', 5));
INSERT 0 1
That test only works because it's eminently compressible.
The short answer to this bug report is that we're not very concerned
about fixing this because there is seldom a good reason to have an
index (unique or not) on fields that can get so wide. As was already
noted, if you do need a uniqueness check you can easily make a 99.%
solution by indexing the md5 hash (or some similar digest) of the
column. It doesn't really seem worthwhile to expend development work
on something that would benefit so few people.
regards, tom lane
But the documentation needs to be updated to mention this nonetheless.
It is a nasty surprise if it hits unawares.
Besides, it's not such an impossible scenario. I encountered this bug
when making an Usenet image archival system. Since the same images tend
to be reposted a lot, it makes sense to store them only once, and simply
reference the stored image from each context it was posted in. Currently
my program does the uniqueness constraining by itself; I was examining
having the database enforce it when I ran into this issue.
Such applications are not exactly rare: bayimg, img.google.com, etc. and
of course the innumerable Usenet archival sites could all conceivably
want to do something like this. So could any application which monitors
potentially repeating phenomena, for that matter. After all, saving a
single state of the system only once not only reduces the amount of data
stored, but could also help in actual analysis of it, since it becomes
trivial to recognize most and least often recurring states.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
