[BUGS] perl5 interface Pg.xs compile
Laurent Larquere ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description perl5 interface Pg.xs compile Long Description Not really a bug, but ... PG version 7.0.2 and 7.0.3 On Solaris 8, perl 5.6.0 : In the file src/interfaces/perl5/Pg.xs # lines 584 and 1295 : change na by PL_na # line 1255 : change &sv_undef by &PL_sv_undef to pass compile Sample Code No file was uploaded with this report
[BUGS] Aggregate Function (AVG) not calculated correctly
Gavin Evans ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description Aggregate Function (AVG) not calculated correctly Long Description Using the following code: SELECT airline_code, AVG(distance) FROM flights GROUP BY airline_code; The first line of the result set (AI | -2864) is calculated wrongly. I have included all code needed to prove the bug, this is a SERIOUS BUG! G Sample Code eg3ab=# select * from flights; flight_number | aircraft | distance | airline_code | origin | destination ---+--+--+--++- AI434 | 310 | 3417 | AI | BOM| PER AI306 | 747 | 1724 | AI | BOM| BKK AI410 | 310 | 2012 | AI | BOM| KUL AI158 | 740 | 2720 | AI | FRA| BOM AI112 | 744 | 9632 | AI | JFK| HKG CX254 | 744 | 8736 | CX | LHR| HKG VS200 | 340 | 8736 | VS | LHR| HKG VS501 | 744 |14737 | VS | LHR| SYD BA009 | 744 |12882 | BA | LHR| SYD QF10 | AB3 |13368 | QF | LHR| SYD GA881 | M11 | 8035 | GA | LGW| BKK PR731 | 74E | 9148 | PR | LGW| BKK TG915 | 744 |10905 | TG | LHR| BKK TG911 | 744 | 9422 | TG | LHR| SIN BA011 | 744 | 9422 | BA | LHR| SIN AI180 | 747 | 6422 | AI | LHR| SIN QF12 | AB3 | 1839 | QF | MEL| PER BA014 | 767 | 321 | BA | LHR| FRA AI200 | 747 | 3705 | AI | JFK| FRA AI181 | 747 | 6422 | AI | SIN| LHR AI201 | 747 | 3705 | AI | FRA| JFK (21 rows) eg3ab=# \i ex13.query airline_code | avg --+--- AI | -2864 BA | 7541 CX | 8736 GA | 8035 PR | 9148 QF | 7603 TG | 10163 VS | 11736 (8 rows) No file was uploaded with this report
Re: [BUGS] Aggregate Function (AVG) not calculated correctly
[EMAIL PROTECTED] wrote: > > Gavin Evans ([EMAIL PROTECTED]) reports a bug with a severity of 1 > The lower the number the more severe it is. > > Short Description > Aggregate Function (AVG) not calculated correctly > > Long Description > Using the following code: > SELECT airline_code, AVG(distance) FROM flights GROUP BY airline_code; > > The first line of the result set (AI | -2864) is calculated wrongly. > I have included all code needed to prove the bug, this is a SERIOUS BUG! You have not included all information required to understand the problem. In particular, what is the schema? What version of PostgreSQL are you using? What kind of machine? Do you happen to be using int2 for the distance fields? In that case you are likely (silently) overflowing your calculation. Please post the schema and the data in such a way that we can read it in! I'd suggest using pg_dump on the relevant table, but other techniques can be used. Regards. - Thomas
Re: [BUGS] Aggregate Function (AVG) not calculated correctly
[EMAIL PROTECTED] writes: > I have included all code needed to prove the bug, No, you haven't: where are the table declarations? But I'll take a guess anyway: you declared "distance" as int2, didn't you? AVG(int2) currently uses an int2 accumulator, and you're suffering overflow. Try "AVG(distance::float8)" instead. We've changed AVG() to use a numeric accumulator for 7.1, but that won't help you today. regards, tom lane
[BUGS] createlang requires entering password 4 times
Tim Dunnington ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description createlang requires entering password 4 times Long Description When you run createlang, and you are using password authentication, you have to type in the password 4 times. If you mistype one of those, the language is not properly created. If you try to drop the language, it fails because it doesn't exist. If you try to re-create the language, it will fail because the language already exists. Sample Code No file was uploaded with this report
[BUGS] bug: UNION
Hi,
There seems to be a bug in postgresql concerning UNION.
I use 7.0.3 (BLCKSIZE=32)
I have the following tables:
Table "best_ez"
Attribute | Type |
Modifier
+--+--
id | integer | not null default
nextval('best_ez_id_seq'::text)
ins_id | char(16) |
hotel_id | varchar(200) | not null default ''
datum | date | not null
corporate | float8 | default '0.'
preis | float8 | not null default '0.'
menge | integer | not null default '0'
reserviert | integer | default '0'
gebucht| integer | default '0'
Indices: best_ez_hotel_id_key,
best_ez_pkey
tcon=> SELECT * from best_ez where hotel_id='ADMIN' order by datum;
id | ins_id | hotel_id | datum|preis | menge |
reserviert | gebucht
--+--+--++--+---++-
...
476 | 21012201 | ADMIN| 2000-01-30 |4 | 5
| 0 | 0
477 | 21012201 | ADMIN| 2000-01-31 |4 | 5
| 0 | 0
478 | 21012201 | ADMIN| 2000-02-01 |4 | 5
| 0 | 0
2955 | 2001050120010510 | ADMIN| 2001-05-01 | 20 |10
| 0 | 0
2956 | 2001050120010510 | ADMIN| 2001-05-02 | 20 |10
| 0 | 0
...
Table "ints"
Attribute | Type | Modifier
---+-+--
n | integer | not null default nextval('ints_n_seq'::text)
Index: ints_pkey
It contains the numbers from 1 to 31
The Query:
=> SELECT n, menge, preis from ints, BEST_DZ where n <= 28 and
hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND
'2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all
select n, 0, 0 from ints where n<28 AND n not in (select
date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM
between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1) order by n;
n | menge | preis
+---+---
1 | 0 | 0
2 | 0 | 0
3 | 0 | 0
4 | 0 | 0
5 | 0 | 0
6 | 0 | 0
7 | 0 | 0
8 | 0 | 0
9 |11 | 3.7
10 | 0 | 0
11 | 0 | 0
...
The Query (same as above, but now with ins_id and '0'):
=> SELECT n, ins_id, menge, preis from ints, BEST_DZ where n <= 28 and
hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND
'2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all
select n, '0', 0, 0 from ints where n<28 AND n not in (select
date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM
between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1) order by n;
n | ins_id | menge |preis
+--+---+--
1 | 0| 0 |0
2 | 0| 0 |0
3 | 0| 0 |0
4 | 0| 0 |0
5 | 0| 0 |0
6 | 0| 0 |0
7 | 0| 0 |0
8 | 0| 0 |0
9 | 2001020920010209 | 959459888 | 3.11924535422124e-33
10 | 0| 0 |0
11 | 0| 0 |0
The same query as above but without order by:
=> SELECT n, ins_id, menge, preis from ints, BEST_DZ where n <= 28 and
hotel_id='ADMIN' AND DATUM between '2001-02-01'::date AND
'2001-02-01'::date + 28 - 1 and n=date_part('day', datum) union all
select n, '0', 0, 0 from ints where n<28 AND n not in (select
date_part('day', datum) from BEST_DZ where hotel_id='ADMIN' AND DATUM
between '2001-02-01'::date AND '2001-02-01'::date + 28 - 1);
n | ins_id | menge | preis
+--+---+---
9 | 2001020920010209 |11 | 3.7
1 | 0|16 | 2.0467033885567e-269
2 | 0|16 | 2.04646077043258e-269
3 | 0|16 | 2.0467033885567e-269
4 | 0|16 | 2.04646077043258e-269
5 | 0|16 | 2.0467033885567e-269
6 | 0|16 | 2.04646077043258e-269
7 | 0|16 | 2.0467033885567e-269
8 | 0|16 | 2.04646077043258e-269
10 | 0|16 | 2.0467033885567e-269
11 | 0|16 | 2.04646077043258e-269
(even the integer row "menge" is wrong)
The last query (e.g.) works exactly 3 times after a fresh c
Re: [BUGS] Rules with Conditions: Still Doesn't Work (Bug Rpt)
"Joel Burton" <[EMAIL PROTECTED]> writes: > Tom Lane pointed out that, given the current handling of > conditional rules, this should be one rule, with two > actions. > For, this either fails to work, or in some circumstances, > or in some circumstances, crashes the backend. It turns out that all of these failures come from the same place, which is that I had broken the handling of INSERT ... SELECT actions inside rules. I have committed a patch that appears to fix the problem. I think that Marc has already tagged beta1, however, so the patch will not be in the beta1 tarball due to appear Real Soon Now. You can pull current sources from the CVS server, or grab a nightly snapshot tarball after tonight. Thanks for the detailed, easy-to-reproduce bug example! These sorts of reports are by far the nicest from a developer's point of view ;-) regards, tom lane
Re: [BUGS] bug: UNION
Nabil Sayegh <[EMAIL PROTECTED]> writes: > There seems to be a bug in postgresql concerning UNION. Cross-datatype UNION doesn't work very well at all in existing releases. I recommend making sure the constants in the second SELECT exactly match the types of the variables in the first select, using explicit casts if necessary. For example, since preis is float8, the 0 that's being unioned with it should be written 0.0 or 0::float8. I believe that mess is all fixed for 7.1. > P.S.: Is there hope for OUTER JOIN in 7.1 ? Done and working. regards, tom lane
Re: [BUGS] Rules with Conditions: Still Doesn't Work (Bug Rpt)
> It turns out that all of these failures come from the same place, > which is that I had broken the handling of INSERT ... SELECT actions > inside rules. > > I have committed a patch that appears to fix the problem. > I think that Marc has already tagged beta1, however, so the patch will > not be in the beta1 tarball due to appear Real Soon Now. You can pull > current sources from the CVS server, or grab a nightly snapshot > tarball after tonight. Fast work! (Boy, I sure wish I had a commercial Oracle/M$ contract instead. I'm sure they could have done it faster ;-) ). Thanks! Just to be clear (as much for other as for me): this should fix the syntax as my (more recent) bug report submitted? That is, one non- conditional rule that tries to handle both inserting and updating together, as opposed to 2 different conditional rules that, together, try to handle all cases. > Thanks for the detailed, easy-to-reproduce bug example! These sorts > of reports are by far the nicest from a developer's point of view ;-) My Mom always said I loved finding problems. :-) -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [BUGS] Rules with Conditions: Still Doesn't Work (Bug Rpt)
"Joel Burton" <[EMAIL PROTECTED]> writes: > Just to be clear (as much for other as for me): this should fix the > syntax as my (more recent) bug report submitted? That is, one non- > conditional rule that tries to handle both inserting and updating > together, as opposed to 2 different conditional rules that, together, > try to handle all cases. Right. The unconditional rule definitely *should* work; you were just running into bugs in rewriting INSERT...SELECT actions, which really had nothing to do with whether the rule is conditional or not. (Had you written INSERT ... VALUES, you'd have seen no problem.) I'm not convinced yet whether the system should accept conditional rules to implement views or not. BTW, the following hack seems to work fine (as of current sources anyway) if you are really intent on a bunch of conditional rules: make an unconditional "do instead nothing" plus as many conditional add-on rules as you want. For example, create view foo as select * from int4_tbl; select * from foo; f1 - 0 123456 -123456 2147483647 -2147483647 (5 rows) insert into foo values (23); ERROR: Cannot insert into a view without an appropriate rule -- Just to keep the rewriter from complaining: create rule foo1 as on insert to foo do instead nothing; create rule foo2 as on insert to foo where new.f1 > 100 do insert into int4_tbl values(new.f1); create rule foo3 as on insert to foo where new.f1 < 0 do insert into int4_tbl values(- new.f1); insert into foo values (123); INSERT 145131 1 insert into foo values (-1000); INSERT 145132 1 insert into foo values (10); INSERT 0 0 select * from foo; f1 - 0 123456 -123456 2147483647 -2147483647 123 1000 (7 rows) This also gives you well-defined behavior if none of the conditional rules fire: nothing happens. regards, tom lane
[BUGS] foreign key check makes a big LOCK
Szima Gabor ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description foreign key check makes a big LOCK Long Description in: src/backend/utils/adt/ri_triggers.c RI_FKey_check(), RI_FKey_noaction_upd(), RI_FKey_noaction_del(), etc.. checking the referential with SELECT FOR UPDATE. After BEGIN TRANSACTION: the INSERT/DELETE/UPDATE calling foreign-key checks, and the SELECT FOR UPDATE locking ALL matched rows in referential table. I modify ri_triggers.c (remove "FOR UPDATE"). This working.. but is correct? Sample Code No file was uploaded with this report
[BUGS] FreeBSD 4.2 readlib issue
Branson Matheson ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description FreeBSD 4.2 readlib issue Long Description Compiling under freebsd gives this problem: gcc -I../../include -I../../backend -O2 -m486 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../interfaces/libpq -o psql command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o tab-complete.o -L../../interfaces/libpq -lpq -lcrypt -lcompat -lln -lm -lutil -lreadline -ltermcap -lncurses -export-dynamic tab-complete.o: In function `psql_completion': tab-complete.o(.text+0x53): undefined reference to `rl_completion_append_character' tab-complete.o(.text+0x1496): undefined reference to `rl_completion_append_character' Sample Code No file was uploaded with this report
Re: [BUGS] FreeBSD 4.2 readlib issue
[EMAIL PROTECTED] writes: > tab-complete.o: In function `psql_completion': > tab-complete.o(.text+0x53): undefined reference to `rl_completion_append_character' > tab-complete.o(.text+0x1496): undefined reference to `rl_completion_append_character' What Postgres version are you using? There's been a configure test for ancient libreadlines without rl_completion_append_character in there for awhile now. If you don't want to update your postgres, updating your libreadline would work too ;-) regards, tom lane
