Thread: Re: Postgres on QNX
Am 12:37 09.11.01 -0600 schrieb Igor Kovalenko:
>Hi Bernd
Hi Igor
>I ran across your notes in the postgres distribution about regression
>some tests failing on QNX4. I did QNX6 port (slightly modified your SYSV
>emulation stuff) and i see some similar test failures.
>
>In particular, i have NUMERIC test failing with same 'ERROR: cannot
>create unique index. Table contains noon-unique values'. Also CREATE
>VIEW fails with 'cannot insert a duplicate key into unique index
>pg_rewrite_rulename_index' (note, there is no issue with shared
>libraries in QNX6).
Good to hear that.
>You notes mention errors like that are 'subject to further
>investigation'.
That sentence was written by my predecessor, Andreas but AFAIK neither
he nor I investigated this further.
> Have you figured anything yet?
I just did a few tests, see psql log below. It looks like there is
something wrong with the test for uniqueness when creating a unique
index. I deleted rows from the table until I could create the index
then added the deleted rows again while the index was in place.
I haven't tried to find the bug in the code yet.
>Did you try to post into postgres-ports list?
I'm crossposting this now.
>Thanks,
>- igor
pmc=> \d num_exp_add
Table "num_exp_add"
Attribute | Type | Modifier
-----------+-----------------+----------
id1 | integer |
id2 | integer |
expected | numeric(210,10) |
pmc=> select * from num_exp_add;
id1 | id2 | expected
-----+-----+----------------------
0 | 0 | 0.0000000000
0 | 1 | 0.0000000000
0 | 2 | -34338492.2153970470
0 | 3 | 4.3100000000
0 | 4 | 7799461.4119000000
0 | 5 | 16397.0384910000
0 | 6 | 93901.5776302600
0 | 7 | -83028485.0000000000
0 | 8 | 74881.0000000000
0 | 9 | -24926804.0450474200
1 | 0 | 0.0000000000
1 | 1 | 0.0000000000
1 | 2 | -34338492.2153970470
1 | 3 | 4.3100000000
1 | 4 | 7799461.4119000000
1 | 5 | 16397.0384910000
1 | 6 | 93901.5776302600
1 | 7 | -83028485.0000000000
1 | 8 | 74881.0000000000
1 | 9 | -24926804.0450474200
(20 rows)
pmc=> CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
pmc=> delete from num_exp_add where id1 > 0;
DELETE 10
pmc=> CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
CREATE
pmc=> INSERT INTO num_exp_add VALUES (1,0,'0');
INSERT 19232 1
pmc=> INSERT INTO num_exp_add VALUES (1,1,'0');
INSERT 19233 1
pmc=> INSERT INTO num_exp_add VALUES (1,2,'-34338492.215397047');
INSERT 19234 1
pmc=> INSERT INTO num_exp_add VALUES (1,3,'4.31');
INSERT 19235 1
pmc=> INSERT INTO num_exp_add VALUES (1,4,'7799461.4119');
INSERT 19236 1
pmc=> INSERT INTO num_exp_add VALUES (1,5,'16397.038491');
INSERT 19237 1
pmc=> INSERT INTO num_exp_add VALUES (1,6,'93901.57763026');
INSERT 19238 1
pmc=> INSERT INTO num_exp_add VALUES (1,7,'-83028485');
INSERT 19239 1
pmc=> INSERT INTO num_exp_add VALUES (1,8,'74881');
INSERT 19240 1
pmc=> INSERT INTO num_exp_add VALUES (1,9,'-24926804.045047420');
INSERT 19241 1
pmc=> drop index num_exp_add_idx;
DROP
pmc=> CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
ERROR: Cannot create unique index. Table contains non-unique values
pmc=>
----
>> In particular, i have NUMERIC test failing with same 'ERROR: cannot
>> create unique index. Table contains noon-unique values'. Also CREATE
>> VIEW fails with 'cannot insert a duplicate key into unique index
>> pg_rewrite_rulename_index'
Hm. Is this with current Postgres sources (7.1 or later)?
I had always assumed that the bizarre behavior in the QNX port was
caused by our old not-too-portable function call interface. But if
you're seeing it with 7.1 or later then that theory is wrong ---
the new-style call interface doesn't use any unprototyped function
calls, so it adheres to all ANSI C rules.
At this point it seems that we must be dealing with either a compiler
bug (which compiler are you using?) or some so-far-unrecognized
portability mistake in the Postgres code. If it's the latter then
I'd like to find and fix it. Can you dig into it with a debugger and
try to determine where the code goes wrong? The CREATE VIEW case would
probably be easier to analyze than the numeric index creation.
regards, tom lane
At 11:45 10.11.01 -0500, Tom Lane wrote:
> >> In particular, i have NUMERIC test failing with same 'ERROR: cannot
> >> create unique index. Table contains noon-unique values'. Also CREATE
> >> VIEW fails with 'cannot insert a duplicate key into unique index
> >> pg_rewrite_rulename_index'
>
>Hm. Is this with current Postgres sources (7.1 or later)?
That was 7.1.3, but I get the same result in the regression test with
the cvs from last week.
>At this point it seems that we must be dealing with either a compiler
>bug (which compiler are you using?) or some so-far-unrecognized
>portability mistake in the Postgres code. If it's the latter then
>I'd like to find and fix it. Can you dig into it with a debugger and
>try to determine where the code goes wrong? The CREATE VIEW case would
>probably be easier to analyze than the numeric index creation.
I have rebuild postgres with -g2 and Watcom-C. The error message is
generated in comparetup_index@tuplesort.c when enforceUnique is set
and it's tuples compare identical. As far as I can see, it is
called from qsort_comparetup which is called by the QNX qsort library
function. It looks like QNX qsort does in fact call the comparison
function to compare a tuple with itself (tuple pointers a and b have
the same value).
Changing line 1873 (in 7.1.3, line 1885 in my last cvs version)
! if (state->enforceUnique && !equal_hasnull && tuple1 != tuple2)
elog(ERROR, "Cannot create unique index. Table contains non-uniq
makes the error go away.
Regards, Bernd Tegge
"Tegge, Bernd" <tegge@repas-aeg.de> writes:
> It looks like QNX qsort does in fact call the comparison
> function to compare a tuple with itself (tuple pointers a and b have
> the same value).
Interesting. That would explain the failure during CREATE INDEX,
but we don't use qsort() while inserting into an existing index.
So I'm still wondering about Igor's report of a duplicate-key
failure in CREATE VIEW. Are you able to duplicate that?
regards, tom lane
At 13:48 10.11.01 -0500, Tom Lane wrote: >"Tegge, Bernd" <tegge@repas-aeg.de> writes: > > It looks like QNX qsort does in fact call the comparison > > function to compare a tuple with itself (tuple pointers a and b have > > the same value). > >Interesting. That would explain the failure during CREATE INDEX, >but we don't use qsort() while inserting into an existing index. Yup, that is why I was able to insert the rows after creating the index but not to recreate it after the drop. >So I'm still wondering about Igor's report of a duplicate-key >failure in CREATE VIEW. Are you able to duplicate that? Sorry, but the QNX4 version doesn't even get to that point during the regression tests (due to lack of support for dynamic loading). I have to let Igor look into this.
"Tegge, Bernd" <tegge@repas-aeg.de> writes:
> Changing line 1873 (in 7.1.3, line 1885 in my last cvs version)
> ! if (state->enforceUnique && !equal_hasnull && tuple1 != tuple2)
> elog(ERROR, "Cannot create unique index. Table contains non-uniq
> makes the error go away.
I've committed this change to CVS. Igor, we still need to know about
the CREATE VIEW case.
Bernd, please remember to send in an update for FAQ_QNX after the dust
settles ...
regards, tom lane
At 17:02 11.11.01 -0500, Tom Lane wrote: >"Tegge, Bernd" <tegge@repas-aeg.de> writes: > > Changing line 1873 (in 7.1.3, line 1885 in my last cvs version) > > ! if (state->enforceUnique && !equal_hasnull && tuple1 != tuple2) > > elog(ERROR, "Cannot create unique index. Table contains non-uniq > > makes the error go away. > >I've committed this change to CVS. Igor, we still need to know about >the CREATE VIEW case. > >Bernd, please remember to send in an update for FAQ_QNX after the dust I have received some interesting patches from Marcelo Nicolet. I just had a look at them when Igor's mail came in. One of these patches makes dl object files (like plpgsql.o) link statically and fakes the dl. If I can find a way to integrate this into the standard tree there will be more changes to the FAQ.