[BUGS] Hi

2010-01-20 Thread beulah prasanthi 
Helo
Can i insert all the data into all the tables(multiple tables) with a
single trip to the database,by creating rules/triggers
 using postgres 8.4 if possible please help me how can i insert the data
and your answer is valuable to me

Re: [BUGS] Hi

2010-01-20 Thread Kevin Grittner 
beulah prasanthi  wrote:
 
> Can i insert all the data into all the tables(multiple tables) with
> a single trip to the database,by creating rules/triggers
 
This is not a bug.  Please repost to another list; perhaps
pgsql-general.
 
When you re-post, you may want to provide more detail about the
problem you are trying to solve.  What you included in this message
probably isn't enough for people to make useful suggestions.
 
-Kevin



-- 
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 #5293: constant function (date_trunc) is repeatedly evaluated inside loop

2010-01-20 Thread Richard Neill 

The following bug has been logged online:

Bug reference:  5293
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.4.2
Operating system:   Linux
Description:constant function (date_trunc) is repeatedly evaluated
inside loop
Details: 

SUMMARY
---

If I have a WHERE clause such as this:

WHERE srep_timestamp >= date_trunc('day', timestamp '2010-01-20 10:16:55')
...

then I'd expect the query planner to evaluate the constant function 
  date_trunc('day', timestamp '2010-01-20 10:16:55')
once, outside the loop.

However, it doesn't do this. 

As a result, the query time doubles from 160ms to 340ms
compared to: 

WHERE srep_timestamp >= '2010-01-20 00:00:00') ...




DETAILS
---

Here are some actual results from a 250k row table. 
srep_timestamp has times roughly linearly distributed over a 2 day period
(with about 20% nulls). 
There is an index tbl_tracker_srepz_timestamp_idx on srep_timestamp WHERE
srep_timestamp is not null.

The measured times are consistent and repeatable.


SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= '2010-01-20
00:00:00') AND (srep_timestamp <  '2010-01-21 00:00:00') );
 count

 198577
(1 row)

Time: 158.084 ms

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= date_trunc('day',
timestamp '2010-01-20 10:16:55') AND srep_timestamp <  date_trunc('day',
timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' )) ;
 count

 198577
(1 row)

Time: 341.155 ms


explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
'2010-01-20 00:00:00') AND (srep_timestamp <  '2010-01-21 00:00:00') );

 QUERY PLAN


--
 Aggregate  (cost=3181.17..3181.18 rows=1 width=0) (actual
time=663.651..663.652 rows=1 loops=1)
   ->  Bitmap Heap Scan on tbl_tracker  (cost=29.39..3177.97 rows=1279
width=0) (actual time=101.197..396.428 rows=198577 loops=1)
 Recheck Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
 ->  Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx 
(cost=0.00..29.07 rows=1279 width=0) (actual time=98.417..98.417 rows=198577
loops=1)
   Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
 Total runtime: 663.769 ms
(6 rows)

Time: 665.087 ms


explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp '2010-01-20 10:16:55') AND srep_timestamp < 
date_trunc('day', timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' ))
;

 QUERY PLAN


--
 Aggregate  (cost=3181.17..3181.18 rows=1 width=0) (actual
time=827.424..827.425 rows=1 loops=1)
   ->  Bitmap Heap Scan on tbl_tracker  (cost=29.39..3177.97 rows=1279
width=0) (actual time=276.367..563.503 rows=198577 loops=1)
 Recheck Cond: ((srep_timestamp >= '2010-01-20 00:00:00'::timestamp
without time zone) AND (srep_timestamp < '2010-01-21 00:00:00'::timestamp
without time zone))
 ->  Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx 
(cost=0.00..29.07 rows=1279 width=0) (actual time=275.020..275.020
rows=198577 loops=1)
   Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00'::timestamp without time zone) AND (srep_timestamp < '2010-01-21
00:00:00'::timestamp without time zone))
 Total runtime: 827.534 ms
(6 rows)

Time: 828.763 ms



Thanks very much - Richard

-- 
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 #5292: Corrupted installer

2010-01-20 Thread Adam Rakowski 

The following bug has been logged online:

Bug reference:  5292
Logged by:  Adam Rakowski
Email address:  foo-scr...@o2.pl
PostgreSQL version: 8.3
Operating system:   Windows
Description:Corrupted installer
Details: 

Both one-click installer and zip archive from postgresql.org are corrupted.
During installation .msi an error appears (damaged file) and when testing
.zip, 7zip reports CRC error.

-- 
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 #5290: Simple loop with insert into and check to avoid duplicate values fails

2010-01-20 Thread Wild Wezyr 
2010/1/19 Tom Lane 

>
> What that sounds like is a locale/encoding conflict.  Postgres depends
> on strcoll() or local equivalent to produce consistent sort results,
> and sometimes if strcoll is presented with data that it thinks is
> invalidly encoded, it doesn't behave sanely.
>
> What locale setting and database encoding are you using?
>
>regards, tom lane
>

Here are my settings:

CREATE DATABASE spb
  WITH OWNER = wwspb
   ENCODING = 'UTF8'
   LC_COLLATE = 'Polish_Poland.1250'
   LC_CTYPE = 'Polish_Poland.1250'
   CONNECTION LIMIT = -1;

Is anything wrong with them? And if so, how should I fix it? Keep in mind
that I must deal with words containing different national characters from
many languages (not only polish).

  (WW)

Re: [BUGS] BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop

2010-01-20 Thread Kevin Grittner 
"Richard Neill"  wrote:
 
> date_trunc('day', timestamp '2010-01-20 10:16:55')
 
What happens with a "timestamp with time zone" literal?
 
-Kevin

-- 
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 #5292: Corrupted installer

2010-01-20 Thread Kevin Grittner 
"Adam Rakowski"  wrote:
 
> Both one-click installer and zip archive from postgresql.org are
> corrupted.
 
Where did you get them (e.g., a URL)?
 
Any chance of download problems?
 
-Kevin

-- 
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 #5293: constant function (date_trunc) is repeatedly evaluated inside loop

2010-01-20 Thread Kevin Grittner 
Richard Neill  wrote:
 
> #fast
> WHERE column <  '2010-010-20 00:00:00'
> 
> #fast
> WHERE column <  date_trunc('day', timestamp with time zone
>   '2010-01-20 10:16:55')
> 
> #slow
> WHERE column <  date_trunc('day', timestamp
>   '2010-01-20 10:16:55')
> 
> 
> Why is that, I wonder?
 
The column is timestamp with time zone, and the fast options
generate a value of the same type (the first because the literal is
treated as UNKNOWN type until the comparison).  It's generally a
good idea to use literals which match the type of the column.  How
much work it would be to optimize the slow case to insert a cast of
the date_trunc function return value to a different type before
entering the loop where the value is tested, I don't know offhand.
 
-Kevin

-- 
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 #5293: constant function (date_trunc) is repeatedly evaluated inside loop

2010-01-20 Thread Richard Neill 



Kevin Grittner wrote:

"Richard Neill"  wrote:
 

date_trunc('day', timestamp '2010-01-20 10:16:55')
 
What happens with a "timestamp with time zone" literal?
 
-Kevin





Good call!

This query is fast:

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= 
date_trunc('day', timestamp with time zone '2010-01-20 10:16:55') AND 
srep_timestamp <  date_trunc('day', timestamp with time zone '2010-01-20 
10:16:55') + INTERVAL '24 hour' )) ;



In other words:

#fast
WHERE column <  '2010-010-20 00:00:00'

#fast
WHERE column <  date_trunc('day', timestamp with time zone
 '2010-01-20 10:16:55')

#slow
WHERE column <  date_trunc('day', timestamp
 '2010-01-20 10:16:55')


Why is that, I wonder?

Richard

--
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 #5290: Simple loop with insert into and check to avoid duplicate values fails

2010-01-20 Thread Tom Lane 
Wild Wezyr  writes:
> 2010/1/19 Tom Lane 
>> What locale setting and database encoding are you using?

> Here are my settings:

> CREATE DATABASE spb
>   WITH OWNER = wwspb
>ENCODING = 'UTF8'
>LC_COLLATE = 'Polish_Poland.1250'
>LC_CTYPE = 'Polish_Poland.1250'
>CONNECTION LIMIT = -1;

I tried the test case on Linux (Fedora 11) with locale pl_PL.utf8,
and unsurprisingly failed to reproduce the problem.  So it's something
specific to Windows.  Can anyone else reproduce it?

regards, tom lane

-- 
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 #5290: Simple loop with insert into and check to avoid duplicate values fails

2010-01-20 Thread Wild Wezyr 
2010/1/20 Tom Lane 

>
> I tried the test case on Linux (Fedora 11) with locale pl_PL.utf8,
> and unsurprisingly failed to reproduce the problem.  So it's something
> specific to Windows.  Can anyone else reproduce it?
>
>regards, tom lane
>


If it is of any help: changing LC_COLLATE and LC_CTYPE to 'C' eliminates the
error.

But when DB is created with LC_COLLATE and LC_CTYPE set to
'Polish_Poland.1250' I get errors (sometimes fast, sometimes I have to wait
for several minutes).

This corellation to locale 'C' / 'Polish_Poland.1250' was tested on two
Windows Vista boxes. Elimination of polish national characters from
generated words for locale 'Polish_Poland.1250' leads to same behavior - no
errors.

  (WW)

Re: [BUGS] BUG #5290: Simple loop with insert into and check to avoid duplicate values fails

2010-01-20 Thread Heikki Linnakangas 
Tom Lane wrote:
> Wild Wezyr  writes:
>> 2010/1/19 Tom Lane 
>>> What locale setting and database encoding are you using?
> 
>> Here are my settings:
> 
>> CREATE DATABASE spb
>>   WITH OWNER = wwspb
>>ENCODING = 'UTF8'
>>LC_COLLATE = 'Polish_Poland.1250'
>>LC_CTYPE = 'Polish_Poland.1250'
>>CONNECTION LIMIT = -1;
> 
> I tried the test case on Linux (Fedora 11) with locale pl_PL.utf8,
> and unsurprisingly failed to reproduce the problem.  So it's something
> specific to Windows.  Can anyone else reproduce it?

I happen to have access to a Win32 virtual machine just now. CVS HEAD,
built from sources on the VM.

Seems to work fine. The test case runs for ages, I'm at about 1/3
through it, and no errors this far. I'm going to have to kill it now.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
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 #5285: contrib\xml2 package's xpath_table function

2010-01-20 Thread Robert Haas 
On Mon, Jan 18, 2010 at 5:13 AM, heyong  wrote:
>
> The following bug has been logged online:
>
> Bug reference:  5285
> Logged by:  heyong
> Email address:  hewy...@126.com
> PostgreSQL version: postgresql-8.3
> Operating system:   solarias
> Description:contrib\xml2 package's xpath_table function
> Details:
>
> 
> unit="天内发货" isneeded="N" fieldflag="sendlimit" />
> fid="1835" isneeded="Y" />
> fid="4441" isneeded="Y" fieldflag="providertype" />
> 
>
> when I run sql:
> SELECT * FROM
> xpath_table('id'
>,'member_define_properties'
>,'aquery.hy_test1'
>,'/FEATURELIST/FEATURE/@fid
>|/FEATURELIST/FEATURE/@name
>|/FEATURELIST/FEATURE/@value
>|/FEATURELIST/FEATURE/@fieldflag
>'
>,'1=1 and id=155015037'
>)
> AS t(   offer_idNUMERIC
>,fidtext
>,name   text
>,VALUE  text
>,fieldflag  text
>)
>
> the result is
>
>  offer_id  | fid  |name|   value|  fieldflag
> ---+--+++--
>  155015037 | 874  | 发货期限   | 10 | sendlimit
>  155015037 | 1835 | 类别   | 洗衣房设备 | providertype
>  155015037 | 4441 | 供应商类型 | 代理商 |
>
>
> which fieldflag's values is err

Please attach (as an attachment, not in the body of the email) the
complete pg_dump output of a test database that can be used to
reproduce this problem.  If possible, a test case that can be
reproduced using only ASCII characters would be helpful.

By the way, I notice that the 8.4 documentation for contrib/xml2
states that it will be removed in 8.4.  Apparently, we didn't.  But I
guess maybe we should go ahead and do that.  I crashed the server
several times while attempting to reproduce the above bug, which makes
me feel like having this code around is not a good thing.

$ psql
Line style is old-ascii.
psql (8.5devel)
Type "help" for help.

rhaas=# load 'pgxml';
LOAD
rhaas=# \i pgxml.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
psql:pgxml.sql:79: ERROR:  could not find function "xslt_process" in
file "/home/rhaas/project/lib/postgresql/pgxml.so"
psql:pgxml.sql:86: ERROR:  could not find function "xslt_process" in
file "/home/rhaas/project/lib/postgresql/pgxml.so"
rhaas=# create table articles (article_id integer, article_xml xml,
date_entered date);
CREATE TABLE
rhaas=# insert into articles (article_id, article_xml, date_entered)
values (2, 'test37',
now());
INSERT 0 1
rhaas=# SELECT * FROM
xpath_table('article_id',
'article_xml',
'articles',
'/article/author|/article/pages|/article/title',
'date_entered > ''2003-01-01'' ')
AS t(article_id integer, author text, page_count integer, title text);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

...Robert

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs