[BUGS] BUG #3892: Invalid week determination
The following bug has been logged online: Bug reference: 3892 Logged by: Roman Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: FC6 Description:Invalid week determination Details: Hello Today is 2007-01-21. select to_char(now(),'WW') returns 200803, which is wrong(?) When I set the date to 2007-01-22 I get 200804 , which is correct. Is that ok ? Should it not be 200804 also for 2007-01-21 ? Regards Roman I see in the doc: ( however it does not explain the case ) week The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only) Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005. ---(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 #5542: Query optimization problem
The following bug has been logged online: Bug reference: 5542 Logged by: Roman Email address: zo...@oe-it.ru PostgreSQL version: 9.0 Operating system: Win7 x64 Description:Query optimization problem Details: I have a query: SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d1.ID=234409763) or (d2.ID=234409763) i think what QO(Query Optimizer) can make it faster (now it seq scan and on million records works 7 sec) SELECT d1.ID, d2.ID FROM DocPrimary d1 JOIN DocPrimary d2 ON d2.BasedOn=d1.ID WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) Result of this query are identical (and can get by replace part of where use join dependence) And it works 0-10ms I think, what QO can undertstand it self. Table create query: CREATE TABLE docprimary ( id integer NOT NULL, basedon integer, CONSTRAINT id_pk PRIMARY KEY (id) ); CREATE INDEX basedon_idx ON docprimary USING btree (basedon); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6075: mergejoin early termination estimates bug or defficiency
The following bug has been logged online: Bug reference: 6075 Logged by: Roman Email address: roman_...@hotmail.com PostgreSQL version: 9.0.3 Operating system: Red Hat 4.4.1-2, 64-bit Description:mergejoin early termination estimates bug or defficiency Details: Entered PG IRC, spoke with RhodiumToad, who advised that this must be reported as a bug or deficiency. http://pgsql.privatepaste.com/09af338ded - the SQL and EXPLAIN ANALYZE output, and http://explain.depesz.com/s/RDN8 - explanation. SQL took 30 seconds to perform, HashAggregate was 8239.96..8240.28 One missing index was added, took runtime to 27 seconds. Eventually executed "set enable_mergejoin=false;" command and runtime of the SQL went down to 38 MILLISECONDS. >From 27 seconds down to 38ms with mergejoin off. Tried setting cpu tuple to 0.1, this did not improve the situation, ended up setting mergejoin to off in the postgresql.conf If more details are needed, please contact me at roman_...@hotmail.com -- In case the paste bin is emptied, here is the SQL: select r.store_id, sum(ri.amount_bought) as count, CASE WHEN sum(least(ri.total_price_before_discount, ri.total_price_after_discount))<0 AND sum(greatest(ri.total_price_before_discount, ri.total_price_after_discount))<0 THEN sum(greatest(ri.total_price_before_discount, ri.total_price_after_discount)) ELSE sum(least(ri.total_price_before_discount, ri.total_price_after_discount)) END as total_sale, CASE WHEN sum(ri.total_discount_amount)<>0 THEN sum(ri.total_discount_amount) ELSE 0 END as total_discount, ri.receipt_id, ri.barcode as barcode, ri.item_id, ri.master_product_id, null as name, null as price from receipt r, receipt_item ri where r.store_id in (0,1,2,3,5,6,7,8,9,10,12,13,14) and r.receipt_id=ri.receipt_id and r.receipt_date>'2011-06-22' and r.receipt_date<'2011-06-24' and ri.receipt_item_id in ( select distinct ri.receipt_item_id from receipt r, receipt_item ri, master_product mp, master_product_label mpl where r.receipt_id=ri.receipt_id and ri.master_product_id=mp.product_id and mp.product_id=mpl.product_id and ( (mpl.label_id= 530)) and r.receipt_date>'2011-06-22' and r.receipt_date<'2011-06-24' ) group by r.store_id, ri.receipt_id, ri.barcode, ri.item_id, ri.master_product_id; Here is the EXPLAIN ANALYZE with mergejoin on: HashAggregate (cost=8579.64..8579.68 rows=1 width=72) (actual time=30901.150..30901.391 rows=352 loops=1) -> Nested Loop (cost=8255.64..8579.61 rows=1 width=72) (actual time=30897.164..30900.399 rows=356 loops=1) -> Nested Loop (cost=8255.64..8547.23 rows=32 width=68) (actual time=30897.138..30898.823 rows=356 loops=1) -> HashAggregate (cost=8255.64..8255.96 rows=32 width=8) (actual time=30897.127..30897.252 rows=356 loops=1) -> Merge Join (cost=1165.04..8255.56 rows=32 width=8) (actual time=62.582..30896.810 rows=356 loops=1) Merge Cond: (ri.master_product_id = mpl.product_id) -> Merge Join (cost=3.19..243053.40 rows=1099 width=24) (actual time=0.038..30890.508 rows=13204 loops=1) Merge Cond: (ri.master_product_id = mp.product_id) -> Nested Loop (cost=0.00..8099569.68 rows=37762 width=16) (actual time=0.032..30852.168 rows=13204 loops=1) -> Index Scan using receipt_item_master_product_idx on receipt_item ri (cost=0.00..729693.91 rows=7400642 width=24) (actual time=0.014..4165.121 rows=7398781 loops=1) -> Index Scan using receipt_pkey on receipt r (cost=0.00..0.98 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=7398781) Index Cond: (r.receipt_id = ri.receipt_id) Filter: ((r.receipt_date > '2011-06-22 00:00:00'::timestamp without time zone) AND (r.receipt_date < '2011-06-24 00:00:00'::timestamp without time zone)) -> Index Scan using master_product_pkey on master_product mp (cost=0.00..7229.15 rows=34421 width=8) (actual time=0.004..24.787 rows=41681 loops=1) -> Sort (cost=1161.84..1168.42 rows=2629 width=8) (actual time=3.429..3.698 rows=2884 loops=1) Sort Key: mpl.product_id Sort Method: quicksort Memory: 224kB -> Bitmap Heap Scan on master_product_label mpl (cost=60.65..1012.51 rows=2629 width=8) (actual time=0.475..1.615 rows=2715 loops=1) Recheck Cond: (label_id = 530) -> Bitmap Index Scan on master_product_label_labelid_idx (cost=0.00..59.
[BUGS] BUG #6202: type of union column selection bug
The following bug has been logged online: Bug reference: 6202 Logged by: Roman Email address: zo...@oe-it.ru PostgreSQL version: 9.0.4 Operating system: Win7x64 Description:type of union column selection bug Details: Query select null union select 4 is Ok but select null union select null union select 4 is Fail i think it`s a bug because you choose type in second query twice null and null -> text text and int -> fail and i think what check type category is not true, because i can`t create implicit cast to resolve some problems, like select 'a' union select 4 if i have implicit cast int->text i think it must work!! Sorry second part is not bug it`s a feature but it`s my problem too -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6478: Foreign tables not displayed
The following bug has been logged on the website:
Bug reference: 6478
Logged by: Roman Chmela
Email address: ro...@chmela.sk
PostgreSQL version: 9.1.2
Operating system: 1) Windows 7 pro, 2) Ubuntu
Description:
Hello
I have setup successfully foreign table, fully functional, retrieving data
from external csv file:
create extension file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE my_names (
name text,
surname text
) SERVER file_server
OPTIONS (format 'text', filename 'c:\xxx\my_names.csv', delimiter ';', null
'');
-
Problem:
-
- this table is not listed among tables (using pgadmin)
---
Possible solution?
---
CREATE OR REPLACE VIEW pg_tables AS
SELECT n.nspname AS schemaname, c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace,
c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS
hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
--WHERE c.relkind = 'r'::"char";
WHERE c.relkind in ('r'::"char",'f'::"char");
=
Thank you for response
Regards
Roman Chmela
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4702: refusal to compute date_trunc('week', '0001-12-09 BC');
The following bug has been logged online:
Bug reference: 4702
Logged by: Roman Kononov
Email address: kono...@ftml.net
PostgreSQL version: 8.3.6 and older
Operating system: GNU/Linux x86_64
Description:refusal to compute date_trunc('week', '0001-12-09 BC');
Details:
test=# select date_trunc('week','0001-12-09 BC'::timestamp);
ERROR: cannot calculate week number without year information
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4718: "select +'8:00'::interval;" gives an error
The following bug has been logged online: Bug reference: 4718 Logged by: Roman Kononov Email address: kono...@ftml.net PostgreSQL version: 8.3.7 Operating system: GNU/Linux x86_64 Description:"select +'8:00'::interval;" gives an error Details: test=# select +'8:00'::interval; ERROR: operator does not exist: + interval LINE 1: select +'8:00'::interval; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. test=# select -'8:00'::interval; -08:00:00 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4739: strange behavior of EXTRACT(quarter from INTERVAL)
The following bug has been logged online: Bug reference: 4739 Logged by: Roman Kononov Email address: kono...@ftml.net PostgreSQL version: 8.3.7 Operating system: GNU/Linux x86_64 Description:strange behavior of EXTRACT(quarter from INTERVAL) Details: test=# select month, extract(quarter from (month::varchar || ' month')::interval) from tbl; month | date_part ---+--- -6 |-1 -5 | 0 -4 | 0 -3 | 0 -2 | 1 -1 | 1 0 | 1 1 | 1 2 | 1 3 | 2 4 | 2 5 | 2 6 | 3 (13 rows) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4739: strange behavior of EXTRACT(quarter from INTERVAL)
On 2009-03-28 11:34 Tom Lane said the following: If you have an actual use-case for this, what do you think it should do? I don't have a use-case (other than hypothetical "how many more years and quarters did it take to complete this particular software project than that particular project"). I think that a quarter is 1/4 year and that is it. Regards, Roman -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4748: hash join and sort-merge join make different results
The following bug has been logged online: Bug reference: 4748 Logged by: Roman Kononov Email address: kono...@ftml.net PostgreSQL version: 8.3.7 Operating system: GNU/Linux x86_64 Description:hash join and sort-merge join make different results Details: test-std=# create table t(s int,i interval); CREATE TABLE test-std=# insert into t values (0,'30 days'), (1,'1 month'); INSERT 0 2 test-std=# select * from t as a, t as b where a.i=b.i; s |i| s |i ---+-+---+- 0 | 30 days | 0 | 30 days 0 | 30 days | 1 | 1 mon 1 | 1 mon | 0 | 30 days 1 | 1 mon | 1 | 1 mon (4 rows) test-std=# analyze; ANALYZE test-std=# select * from t as a, t as b where a.i=b.i; s |i| s |i ---+-+---+- 0 | 30 days | 0 | 30 days 1 | 1 mon | 1 | 1 mon (2 rows) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4748: hash join and sort-merge join make different results
On 2009-04-03 14:57 Tom Lane said the following: I think we could fix this by having interval_hash() duplicate the total-span calculation done by interval_cmp_internal, and then return the hash of the resulting TimeOffset. This is going to break existing hash indexes on intervals, but there seems little choice... Consider hashing the result of justify_interval(). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4748: hash join and sort-merge join make different results
On 2009-04-03 23:32 Tom Lane said the following: Roman Kononov writes: On 2009-04-03 14:57 Tom Lane said the following: I think we could fix this by having interval_hash() duplicate the total-span calculation done by interval_cmp_internal, and then return the hash of the resulting TimeOffset. This is going to break existing hash indexes on intervals, but there seems little choice... Consider hashing the result of justify_interval(). Uh, what's your point? We have to match interval_eq, not justify_interval. For any two intervals a and b, saying that interval_cmp_interval(a,b)==0 is exactly the same as saying that (aj.month==bj.month && aj.day==bj.day && aj.time==bj.time), where aj=justify_interval(a) and bj=justify_interval(b). Therefore, instead of hashing interval_cmp_value() you can hash justify_interval(), where interval_cmp_value() is the transformation of intervals in interval_cmp_interval(). You said that hashing interval_cmp_value() breaks existing hash indexes. Hashing "justified" intervals avoids such breaking in some cases. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4874: vacuum doest work
The following bug has been logged online: Bug reference: 4874 Logged by: Roman Galeev Email address: i...@ncom-ufa.ru PostgreSQL version: 8.1 Operating system: Debian Sarge Description:vacuum doest work Details: I've constantly got messages like this: 2009-06-23 20:43:42 YEKST WARNING: database "testing" must be vacuumed within 10532638 transactions 2009-06-23 20:43:42 YEKST ПОДСКАЗКА: To avoid a database shutdown, execute a full-database VACUUM in "testing". but issuing vacuum does not help. I've issued vacuum, vacuum full, vacuum freeze several times. The only change I see is decreasing number of transactions. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4926: too few pathkeys for mergeclauses
The following bug has been logged online: Bug reference: 4926 Logged by: Roman Kononov Email address: kono...@ftml.net PostgreSQL version: 8.4.0 Operating system: Linux x86_64 Description:too few pathkeys for mergeclauses Details: test=# create table junk(i int); CREATE TABLE test=# select * from junk left outer join (select coalesce(i,1) as x, coalesce(i,2) as y from junk) t on coalesce(i,3)=x and coalesce(i,4)=y and coalesce(i,5)=x; ERROR: too few pathkeys for mergeclauses test=# select * from junk left outer join (select coalesce(i,1) as x, coalesce(i,2) as y from junk) t on coalesce(i,3)=x and coalesce(i,4)=y and coalesce(i,5)=y; i | x | y ---+---+--- (0 rows) I think, the first query should be handled the same way second is. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5039: 'i' flag i in regexp_replace ignored for polish letters
The following bug has been logged online:
Bug reference: 5039
Logged by: Kamil Roman
Email address: kamil.lech.ro...@gmail.com
PostgreSQL version: 8.3.7
Operating system: Windows XP
Description:'i' flag i in regexp_replace ignored for polish letters
Details:
select regexp_replace('LUBŻKOĄŚĆĘŁŃÓ','[ośżźćęąłńó]',
'_','ig');
returns 'LUBŻK_ĄŚĆĘŁŃÓ' and it should return LUB_K___
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5126: convert_to preventing index scan
The following bug has been logged online:
Bug reference: 5126
Logged by: Roman Kapusta
Email address: roman.kapu...@gmail.com
PostgreSQL version: 8.3.8-1
Operating system: fedora 11 i586 (32bit)
Description:convert_to preventing index scan
Details:
I have table with bytea column, which is indexed (1)
I want to use index during pattern matching (eg. dir like someDirectoryName
|| '/%'), but concatenation of two strings cause error (2)
So I have to use function convert_to (converting text to bytea), but this
has awful explain plan (3)
If I rewrite string concatenation to just one string (4) query plan is
optimal
I found workaround (5), but still it looks like convert_to is causing full
table scan where it should not
(1) # \d paths
Table "paths"
Column |Type | Modifiers
---+-+--
--
dev_id| bigint | not null
valid_to | bigint | not null default
9223372036854775807::bigint
name | character varying(300) | not null
dir | bytea | not null
Indexes:
"paths_dev_id_key" UNIQUE, btree (dev_id, dir, name, valid_to)
(2) # explain select * from paths p where p.dir like E'Multimedia/Videos' ||
E'/%' and p.dev_id = 14 and p.valid_to >= 486629;
ERROR: operator does not exist: bytea ~~ text
LINE 1: explain select * from paths p where p.dir like E'Multimedia/...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
(3) # explain select * from paths p where p.dir like
convert_to(E'Multimedia/Videos' || E'/%', 'UTF8') and p.dev_id = 14 and
p.valid_to >= 486629;
QUERY PLAN
---
Seq Scan on paths p (cost=0.00..212065.54 rows=1 width=333)
Filter: ((valid_to >= 486629) AND (dev_id = 14) AND (dir ~~
convert_to('Multimedia/Videos/%'::text, 'UTF8'::name)))
(4) # explain select * from paths p where p.dir like E'Multimedia/Videos/%'
and p.dev_id = 14 and p.valid_to >= 486629;
QUERY PLAN
-
Index Scan using paths_dev_id_key on paths p (cost=0.00..10.02 rows=1
width=333)
Index Cond: ((dev_id = 14) AND (dir >= 'Multimedia/Videos/'::bytea) AND
(dir < 'Multimedia/Videos0'::bytea) AND (valid_to >= 486629))
Filter: (dir ~~ 'Multimedia/Videos/%'::bytea)
(5) # explain select * from paths p where p.dir like
E'Multimedia/Videos'::bytea || E'/%'::bytea and p.dev_id = 14 and p.valid_to
>= 486629;
QUERY PLAN
-
Index Scan using paths_dev_id_key on paths p (cost=0.00..10.02 rows=1
width=333)
Index Cond: ((dev_id = 14) AND (dir >= 'Multimedia/Videos/'::bytea) AND
(dir < 'Multimedia/Videos0'::bytea) AND (valid_to >= 486629))
Filter: (dir ~~ 'Multimedia/Videos/%'::bytea)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5039: 'i' flag i in regexp_replace ignored for polish letters
Hello, yes, I have been using UTF-8. Shouldn't this behaviour be at least documented in the postgresql documentation? I am aware that it is a bug, but if it is not likely to be fixed soon IMHO it should be documented somehow. Regards, Kamil Roman 2009/10/22 Tom Lane > Robert Haas writes: > > On Sat, Sep 5, 2009 at 5:42 AM, Kamil Roman > wrote: > >> Description:'i' flag i in regexp_replace ignored for polish > letters > > > I haven't seen a response to this. Anyone think this might be a bug? > > If he's using a multibyte character set (UTF8 most likely) there is > pretty much 0 hope of it working. The existing TODO entry for this > links to > http://archives.postgresql.org/pgsql-hackers/2008-12/msg00433.php > >regards, tom lane >
[BUGS] BUG #5237: strange int->bit and bit->int conversions
The following bug has been logged online: Bug reference: 5237 Logged by: Roman Kononov Email address: kono...@ftml.net PostgreSQL version: 8.4.1 Operating system: GNU/Linux x86_64 Description:strange int->bit and bit->int conversions Details: test=# select (11::int4<<23 | 11::int4)::bit(32); 010110001011 test=# select (11::int4<<23 | 11::int4)::bit(33); 0101110001011 test=# select (11::int4<<23 | 11::int4)::bit(39); 010110110001011 test=# select (11::int4<<23 | 11::int4)::bit(40); 010110001011 The ::bit(33) and ::bit(39) conversions seem wrong. test-std=# select 1::int4::bit(32)::int4; 1 test-std=# select 1::int4::bit(33)::int4; ERROR: integer out of range Why is it out of range? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5237: strange int->bit and bit->int conversions
The bitfromint8() and bitfromint4() are hosed. They produce wrong
results when the BIT size is more than 64 and 32 respectively, and the
BIT size is not multiple of 8, and the most significant byte of the
integer value is not 0x00 or 0xff.
For example:
test=# select (11::int4<<23 | 11::int4)::bit(32);
010110001011
test=# select (11::int4<<23 | 11::int4)::bit(33);
0101110001011
test=# select (11::int4<<23 | 11::int4)::bit(39);
010110110001011
test=# select (11::int4<<23 | 11::int4)::bit(40);
010110001011
The ::bit(33) and ::bit(39) conversions are wrong.
The patch re-implements the conversion functions.
diff -urp -x '*.o' -x '*.txt' -x '*.so' postgresql-8.4.1-orig/src/backend/utils/adt/varbit.c postgresql-8.4.1/src/backend/utils/adt/varbit.c
--- postgresql-8.4.1-orig/src/backend/utils/adt/varbit.c 2009-12-12 09:19:13.0 -0600
+++ postgresql-8.4.1/src/backend/utils/adt/varbit.c 2009-12-12 10:29:59.0 -0600
@@ -1321,8 +1321,8 @@ bitfromint4(PG_FUNCTION_ARGS)
VarBit *result;
bits8 *r;
int rlen;
- int destbitsleft,
-srcbitsleft;
+ int const srcbits=sizeof(a)*BITS_PER_BYTE;
+ int i;
if (typmod <= 0)
typmod = 1;/* default bit length */
@@ -1333,32 +1333,21 @@ bitfromint4(PG_FUNCTION_ARGS)
VARBITLEN(result) = typmod;
r = VARBITS(result);
- destbitsleft = typmod;
- srcbitsleft = 32;
- /* drop any input bits that don't fit */
- srcbitsleft = Min(srcbitsleft, destbitsleft);
- /* sign-fill any excess bytes in output */
- while (destbitsleft >= srcbitsleft + 8)
- {
- *r++ = (bits8) ((a < 0) ? BITMASK : 0);
- destbitsleft -= 8;
- }
- /* store first fractional byte */
- if (destbitsleft > srcbitsleft)
- {
- *r++ = (bits8) ((a >> (srcbitsleft - 8)) & BITMASK);
- destbitsleft -= 8;
- }
- /* Now srcbitsleft and destbitsleft are the same, need not track both */
- /* store whole bytes */
- while (destbitsleft >= 8)
- {
- *r++ = (bits8) ((a >> (destbitsleft - 8)) & BITMASK);
- destbitsleft -= 8;
+ rlen=(typmod+BITS_PER_BYTE-1)/BITS_PER_BYTE;
+
+ if (srcbits>=typmod) {
+ a<<=srcbits-typmod;
+for (i=0; i!=rlen; ++i, a<<=BITS_PER_BYTE) r[i]=a>>(srcbits-BITS_PER_BYTE);
+ } else {
+ int sh=typmod%BITS_PER_BYTE;
+ int32 h=a>>sh;
+ int32 l=a<<(srcbits-sh);
+ size_t const zsize=rlen-sizeof(a)-(sh!=0);
+ bits8 sx=(a>=0)-1;
+ memset(r,sx,zsize);
+for (i=0; i!=sizeof(a); ++i, h<<=8) r[zsize+i]=h>>(srcbits-BITS_PER_BYTE);
+ if (sh!=0) r[zsize+sizeof(a)]=l>>(srcbits-BITS_PER_BYTE);
}
- /* store last fractional byte */
- if (destbitsleft > 0)
- *r = (bits8) ((a << (8 - destbitsleft)) & BITMASK);
PG_RETURN_VARBIT_P(result);
}
@@ -1396,8 +1385,8 @@ bitfromint8(PG_FUNCTION_ARGS)
VarBit *result;
bits8 *r;
int rlen;
- int destbitsleft,
-srcbitsleft;
+ int const srcbits=sizeof(a)*BITS_PER_BYTE;
+ int i;
if (typmod <= 0)
typmod = 1;/* default bit length */
@@ -1408,36 +1397,21 @@ bitfromint8(PG_FUNCTION_ARGS)
VARBITLEN(result) = typmod;
r = VARBITS(result);
- destbitsleft = typmod;
-#ifndef INT64_IS_BUSTED
- srcbitsleft = 64;
-#else
- srcbitsleft = 32; /* don't try to shift more than 32 */
-#endif
- /* drop any input bits that don't fit */
- srcbitsleft = Min(srcbitsleft, destbitsleft);
- /* sign-fill any excess bytes in output */
- while (destbitsleft >= srcbitsleft + 8)
- {
- *r++ = (bits8) ((a < 0) ? BITMASK : 0);
- destbitsleft -= 8;
- }
- /* store first fractional byte */
- if (destbitsleft > srcbitsleft)
- {
- *r++ = (bits8) ((a >> (srcbitsleft - 8)) & BITMASK);
- destbitsleft -= 8;
- }
- /* Now srcbitsleft and destbitsleft are the same, need not track both */
- /* store whole bytes */
- while (destbitsleft >= 8)
- {
- *r++ = (bits8) ((a >> (destbitsleft - 8)) & BITMASK);
- destbitsleft -= 8;
+ rlen=(typmod+BITS_PER_BYTE-1)/BITS_PER_BYTE;
+
+ if (srcbits>=typmod) {
+ a<<=srcbits-typmod;
+for (i=0; i!=rlen; ++i, a<<=BITS_PER_BYTE) r[i]=a>>(srcbits-BITS_PER_BYTE);
+ } else {
+ int sh=typmod%BITS_PER_BYTE;
+ int64 h=a>>sh;
+ int64 l=a<<(srcbits-sh);
+ size_t const zsize=rlen-sizeof(a)-(sh!=0);
+ bits8 sx=(a>=0)-1;
+ memset(r,sx,zsize);
+for (i=0; i!=sizeof(a); ++i, h<<=8) r[zsize+i]=h>>(srcbits-BITS_PER_BYTE);
+ if (sh!=0) r[zsize+sizeof(a)]=l>>(srcbits-BITS_PER_BYTE);
}
- /* store last fractional byte */
- if (destbitsleft > 0)
- *r = (bits8) ((a << (8 - destbitsleft)) & BITMASK);
PG_RETURN_VARBIT_P(result);
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5257: wrong results of SUBSTRING with SQL regular expressions
The following bug has been logged online:
Bug reference: 5257
Logged by: Roman Kononov
Email address: kono...@ftml.net
PostgreSQL version: 8.4.2
Operating system: GNU/Linux x86_64
Description:wrong results of SUBSTRING with SQL regular expressions
Details:
test=# select substring('34' from '(2|3)#"4#"' for '#');
substring
---
3
(1 row)
test=# select substring('^' from '#"^#"' for '#');
substring
---
(1 row)
test=# select substring('$' from '#"$#"' for '#');
substring
---
(1 row)
These look wrong according to the PG documentation.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5257: wrong results of SUBSTRING with SQL regular expressions
I don't know what SQL2008 says about SUBSTRING, but SQL2003 says in
ISO/IEC 9075-2:2003 (E), 6.29 , General Rules,
5), page 261:
d) If R [the regular expression] does not contain exactly two
occurrences of the two-character sequence consisting of E [the escape
character], each immediately followed by , then an
exception condition is raised: data exception - invalid use of escape
character.
This means that the following is wrong:
test-std=# select substring('a' from 'a' for '#');
substring
---
a
(1 row)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Driver Bug jdbc7.0-1.2.jar.
I have downloaded a driver for postgres to develop in java, the driver i downloaded is jdbc7.0-1.2.jar It works just fine untill i try to do something with the database from an event listener class. Then it blows up with a java null pointer exception. Everywhere else in the program i can read and write to the database but when i try to write a record into the database from the listener class i get null pointer error. I have looked through it with the debugger and i have found out that the pgstream variable was null when i tried using the driver from the listener class and it has some values when i try to use it from anywhere else. I will attach the code i am trying to build, you can run it and see what happnes, i have used Jbuilder 5.0 to run this code. Thanks in advance, Roman Levandovsky. UserInterface.java Description: java/ ImageCanvas.java Description: java/ InsertButtonClicked.java Description: java/ DatabaseDriver.java Description: java/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #2754: cache lookup failed for function 8470229
The following bug has been logged online: Bug reference: 2754 Logged by: Roman Shterenzon Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3 Operating system: RHEL3 Description:cache lookup failed for function 8470229 Details: We receive: "ERROR: cache lookup failed for function 8470229" When trying to insert into table, or even when doing \d tablename in the psql CLI. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2846: inconsistent and confusing handling of underflows, NaNs and INFs
The following bug has been logged online:
Bug reference: 2846
Logged by: Roman Kononov
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.0 and older
Operating system: linux 2.6.15-27-amd64 ubuntu
Description:inconsistent and confusing handling of underflows, NaNs
and INFs
Details:
Please compare the results of the simple queries.
==
test=# select ('NaN'::float4)::int2;
int2
--
0
(1 row)
test=# select ('NaN'::float4)::int4;
int4
-
-2147483648
(1 row)
test=# select ('NaN'::float4)::int8;
ERROR: bigint out of range
test=# select ('nan'::numeric)::int4;
ERROR: cannot convert NaN to integer
==
test=# select abs('INF'::float4);
abs
--
Infinity
(1 row)
test=# select abs('INF'::float8);
ERROR: type "double precision" value out of range: overflow
==
test=# select -('INF'::float4);
?column?
---
-Infinity
(1 row)
test=# select -('INF'::float8);
ERROR: type "double precision" value out of range: overflow
==
test=# select (1e-37::float4)*(1e-22::float4);
?column?
--
0
(1 row)
test=# select (1e-37::float4)*(1e-2::float4);
ERROR: type "real" value out of range: underflow
==
test=# select (1e-300::float8)*(1e-30::float8);
?column?
--
0
(1 row)
test=# select (1e-300::float8)*(1e-20::float8);
ERROR: type "double precision" value out of range: underflow
==
test=# select ('INF'::float8-'INF'::float8);
?column?
--
NaN
(1 row)
test=# select ('INF'::float8+'INF'::float8);
ERROR: type "double precision" value out of range: overflow
==
test=# select ('INF'::float4)::float8;
float8
--
Infinity
(1 row)
test=# select ('INF'::float8)::float4;
ERROR: type "real" value out of range: overflow
==
test=# select cbrt('INF'::float4);
cbrt
--
Infinity
(1 row)
test=# select sqrt('INF'::float4);
ERROR: type "double precision" value out of range: overflow
==
test=# select ((-32768::int8)::int2)%(-1::int2);
?column?
--
0
(1 row)
test=# select ((-2147483648::int8)::int4)%(-1::int4);
ERROR: floating-point exception
DETAIL: An invalid floating-point operation was signaled. This probably
means an out-of-range result or an invalid operation, such
as division by zero.
==
test=# create table tt (ff float8);
CREATE TABLE
test=# insert into tt values (1e308),(1e308),(1e308);
INSERT 0 3
test=# select * from tt;
ff
1e+308
1e+308
1e+308
(3 rows)
test=# select avg(ff) from tt;
avg
--
Infinity
(1 row)
test=# select stddev(ff) from tt;
stddev
NaN
(1 row)
==
All this things are in float.c
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] BUG #2913: Subscript on multidimensional array yields no value
The following bug has been logged online: Bug reference: 2913 Logged by: Roman Nowak Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.1 Operating system: Windows XP Description:Subscript on multidimensional array yields no value Details: SELECT (ARRAY[[1,2],[3,4]])[1]; does not return [1,2] (testes in psql and pgAdmin III): show info that one row was returned but does not display its value following statment works OK btw SELECT (ARRAY[[1,2],[3,4]])[1:1]; ---(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 #2979: Functional indexes ERROR while updating table
The following bug has been logged online:
Bug reference: 2979
Logged by: Roman Grigorovich
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.7
Operating system: FreeBSD 5.4-RELEASE
Description:Functional indexes ERROR while updating table
Details:
create table test1 (name varchar(64));
create index test1_name_idx on test1(upper(name));
insert into test1 values ('test');
update test1 set name = 'test1';
ERROR: attribute 1 has wrong type
DETAIL: Table has type character varying, but query expects character
varying.
What is it? On version 8.1.2 - ALL OK!
Detail of config:
bash-3.00# ./pg_config
BINDIR = /usr/local/postgres/bin
DOCDIR =
INCLUDEDIR = /usr/local/postgres/include
PKGINCLUDEDIR = /usr/local/postgres/include
INCLUDEDIR-SERVER = /usr/local/postgres/include/server
LIBDIR = /usr/local/postgres/lib
PKGLIBDIR = /usr/local/postgres/lib
LOCALEDIR = /usr/local/postgres/share/locale
MANDIR = /usr/local/postgres/man
SHAREDIR = /usr/local/postgres/share
SYSCONFDIR = /usr/local/postgres/etc
PGXS = /usr/local/postgres/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/postgres' '--without-docdir' '--enable-nls'
'--cache-file=/dev/null' 'CPPFLAGS=-I/usr/local/include'
'LDFLAGS=-L/usr/local/lib'
CC = gcc
CPPFLAGS = -I/usr/local/include
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -L/usr/local/lib -Wl,-R/usr/local/postgres/lib
LDFLAGS_SL =
LIBS = -lpgport -lintl -lz -lreadline -lcrypt -lcompat -lm -lutil
VERSION = PostgreSQL 8.1.7
Cluster info:
LC_COLLATE: ru_RU.UTF-8
LC_CTYPE: ru_RU.UTF-8
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] Inconsistent shift operator
The below test cases show the obvious inconsistency between different integer types. Regards, Roman test=# \t Showing only tuples. test=# select 1::int2 << 17; 0 test=# select 1::int4 << 33; 2 test=# select 1::int8 << 65; 2 test=# select 2::int2 >> 17; 0 test=# select 2::int4 >> 33; 1 test=# select 2::int8 >> 65; 1 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4114: Inconsistent shift operator
The following bug has been logged online: Bug reference: 4114 Logged by: Roman Kononov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.1 Operating system: x86_64 GNU/Linux Description:Inconsistent shift operator Details: The below test cases show the obvious inconsistency between different integer types. test=# \t Showing only tuples. test=# select 1::int2 << 17; 0 test=# select 1::int4 << 33; 2 test=# select 1::int8 << 65; 2 test=# select 2::int2 >> 17; 0 test=# select 2::int4 >> 33; 1 test=# select 2::int8 >> 65; 1 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Readline lib configure error. Help
Hi! Use RH Linux90. Try configure postgresql-7.4.1 Type ./configure Error is checking for readline... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. But I want use readline This is config.log configure:5999: checking for readline configure:6036: gcc -o conftest -O2 -fno-strict-aliasing -g -D_GNU_SOURCEconftest.c -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lbsd >&5/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetnum' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgoto' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetflag' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `BC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tputs' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `PC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetent' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `UP' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetstr' collect2: ld returned 1 exit status Have installed readline-4.3-5, readline41-4.1-16, readline-devel-4.3-5 Thank you. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] relation "pg_user" does not exist
I am running PostgreSQL 7.4.7 on Fedora Core 3. I get a lot of warnings from pgAdmin III: relation "pg_user" does not exist but it doesn't seem to prevent me from doing most operations. More seriously, I cannot execute a backup. I get the following message: * /usr/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "testbackupfile" rickdb pg_dump: saving encoding pg_dump: saving database definition pg_dump: SQL command failed pg_dump: Error message from server: ERROR: relation "pg_user" does not exist pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'rickdb' pg_dump: *** aborted because of error Process returned exit code 1. ** Indeed, if I log in as -U postgres -d template1 and select * from pg_user, the view does not exist. Anybody have an idea why this view was not created on install and what I can do to correct the situation? ---(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: [BUGS] relation "pg_user" does not exist
> "Rick Roman" <[EMAIL PROTECTED]> writes: >> I am running PostgreSQL 7.4.7 on Fedora Core 3. I get a lot of >> warnings from pgAdmin III: > >> relation "pg_user" does not exist > > You've probably been bit by the problems with SELinux disabling parts > of > the initdb script. You'll need to re-initdb after installing the > latest > RPMs (postgresql 7.4.7-3.FC3.1, not sure which selinux-policy-targeted > is current). Alternatively, you can shut off SELinux enforcement > while > running the older initdb. > > regards, tom lane > It looks like 7.4.7 is what's currently in distribution on up2date, so I tried running initdb with SE Linux off with my current 7.4.7. This worked fine except that, if I turn SE Linux back on, the Postgres server will no longer start up. I guess I'll just leave SE Linux off until 7.4.7-3 is available. It's just a dev box anyway. Thanks for your help! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] sequences and rollback
Do I understand correctly that you cannot roll back sequences?
JDBC example:
connect();
db.setAutoCommit(false);
Statement stmt=db.createStatement();
stmt.executeQuery("select nextval('test_seq')");
... other table updates ...
stmt.close();
db.rollback();
db.close();
In this example, the sequence does not roll back. .. other table updates
... do roll back.
---(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
[BUGS] BUG #1577: round(dp) function does not round to nearest integer properly
The following bug has been logged online: Bug reference: 1577 Logged by: Roman Schayuk Email address: [EMAIL PROTECTED] PostgreSQL version: 7.2.2 Operating system: RedHat Linux 8.0 x86 Description:round(dp) function does not round to nearest integer properly Details: SQL command: gap=> select round(42.5); round --- 42 (1 row) Result has to be 43 In contrast round(numeric, int) function works properly: gap=> select round(42.5,0); round --- 43 (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] BUG #6268: multiple update with on cascade
The following bug has been logged online:
Bug reference: 6268
Logged by: Roman Lytovchenko
Email address: roman.lytovche...@gmail.com
PostgreSQL version: 9.1.1
Operating system: Mac OS X 10.6.8
Description:multiple update with on cascade
Details:
create table t2 (
a text not null,
b text null,
constraint t2_pk primary key (a),
constraint t2_t2_fk foreign key (b)
references t2 (a) match simple
on update cascade-- !
on delete cascade
deferrable
initially deferred -- !
);
insert into t2 (a, b)
values
('www', 'www'),
('asd', 'asd');
-- run this transaction in another connection!
start transaction;
set constraints all immediate;
update t2
set a = '123'
where a = 'www';
select
a, b
from t2;
-- this update failed with
-- ERROR: insert or update on table "t2" violates foreign key
constraint "t2_t2_fk"
-- Detail: Key (b)=(123) is not present in table "t2".
update t2
set a = 'kkk'
where a = '123';
commit;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
