Re: [BUGS] BUG #7606: service restart failed

[Magnus Hagander]

On Tue, Oct 16, 2012 at 4:33 AM,   wrote:
> The following bug has been logged on the website:
>
> Bug reference:  7606
> Logged by:  James
> Email address:  jameshun...@163.com
> PostgreSQL version: 9.2.1
> Operating system:   CentOS 6.2
> Description:
>
> When I modify the "listen_addresse" from 'localhost' to '*', postgresql
> service restart failed.
>
> postgresql.conf like below:
> # - Connection Settings -
> listen_addresse='*'# what IP address(es) to listen on;

Seems you've spelled listen_addresses wrong. If you look in your
logfile, it should clearly tell you what the reason for not starting
is - which in this case is that PostgreSQL has no idea what
"listen_addresse" is, since it's spelled wrong.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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 #7598: Loss of view performance after dump/restore of the view definition

[Vaclav Juza]

I was doing the test on the released 9.2.1 (without the patch).
The times of the query with the original view were floating around those
values, so it's maybe just a luck that it was slightly slower on 9.2.1.

The big difference was after the view was restored with the explicit
cast. The purpose of this updated test case was mainly that you can
check which change between 9.1.4 and 9.2.1 could have caused this
performance regression and if this change can have some negative impact
(on different queries) even after applying the patch.

Thanks for the patch anyway.

Regards,
Vaclav Juza

On 15/10/2012 17:44, Tom Lane wrote:
> Vaclav Juza  writes:
>> I have modified the test case (it is more similar to the real-word
>> query), so that now it is slower on 9.2.1 than on 9.1.4 (the version
>> with the explicit cast):
> 
> FWIW, testing this on HEAD (with the patch I committed last week),
> I get a plan that's about 10% faster than 9.1; or if I disable
> index-only scans for a more level playing field, it's just about
> equivalent to 9.1.
> 
>   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: [Spam] Re: [BUGS] BUG #7577: JDBC Driver - Compiled with Java 7

[Sean Hogan]

On 12-10-16 12:08 AM, Craig Ringer wrote:

On 10/15/2012 11:10 PM, seancompusult wrote:

Does anyone have a solution for people like me who must use Java 5?


I had no problems building current git head of the JDBC driver with 
Java SE 5, nor the REL_9_2_STABLE branch.




Ah, quite right.  I was having a problem running ant, because it itself 
was compiled with Java 7.  :-(  IMO the whole approach of relying on 
compiling with specific Java versions is flawed, but at least I've 
gotten past this issue so I'm happy.  Thanks for that.


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

Re: [Spam] Re: [BUGS] BUG #7577: JDBC Driver - Compiled with Java 7

[Craig Ringer]

On 10/16/2012 08:10 PM, Sean Hogan wrote:

On 12-10-16 12:08 AM, Craig Ringer wrote:

On 10/15/2012 11:10 PM, seancompusult wrote:

Does anyone have a solution for people like me who must use Java 5?


I had no problems building current git head of the JDBC driver with
Java SE 5, nor the REL_9_2_STABLE branch.



Ah, quite right.  I was having a problem running ant, because it itself
was compiled with Java 7.  :-(  IMO the whole approach of relying on
compiling with specific Java versions is flawed, but at least I've
gotten past this issue so I'm happy.  Thanks for that.


Yes, it's flawed, but it's also unfortunately necessary.

javac offers flags like -source 1.5 -target 1.5 for controlling bytecode 
generation and source compatibility. That's fine, but it offers 
absolutely no way to say "treat standard library packages, classes and 
methods added after Java SE 5 as missing".


This means that for testing at least it's required to build with a 
variety of JDK versions.


I agree that it'd be nice to support downlevel class generation for end 
users. It may even be possible; I haven't dug into build.xml in detail yet.


--
Craig Ringer



--
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 #7534: walreceiver takes long time to detect n/w breakdown

[Heikki Linnakangas]

On 15.10.2012 19:31, Fujii Masao wrote:

On Mon, Oct 15, 2012 at 11:27 PM, Heikki Linnakangas
  wrote:

On 15.10.2012 13:13, Heikki Linnakangas wrote:


Oh, I didn't remember that we've documented the specific structs that we
pass around. It's quite bogus anyway to explain the messages the way we
do currently, as they are actually dependent on the underlying
architecture's endianess and padding. I think we should refactor the
protocol to not transmit raw structs, but use pq_sentint and friends to
construct the messages. This was discussed earlier (see

http://archives.postgresql.org/message-id/4fe2279c.2070...@enterprisedb.com),
I think there's consensus that 9.3 would be a good time to do that as we
changed the XLogRecPtr format anyway.



This is what I came up with. The replication protocol is now
architecture-independent. The WAL format itself is still
architecture-independent, of course, but this is useful if you want to e.g
use pg_receivexlog to back up a server that runs on a different platform.

I chose the int64 format to transmit timestamps, even when compiled with
--disable-integer-datetimes.

Please review if you have the time..


Thanks for the patch!

When I ran pg_receivexlog, I encountered the following error.


Yeah, clearly I didn't test this near enough...

I fixed the bugs you bumped into, new version attached.


+   hdrlen = sizeof(int64) + sizeof(int64) + 
sizeof(int64);
+   hdrlen = sizeof(int64) + sizeof(int64) + 
sizeof(char);

These should be macro, to avoid calculation overhead?


The compiler will calculate this at compilation time, it's going to be a 
constant at runtime.


- Heikki
diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml
index 3d72a16..5a32517 100644
--- a/doc/src/sgml/protocol.sgml
+++ b/doc/src/sgml/protocol.sgml
@@ -1366,7 +1366,8 @@ The commands accepted in walsender mode are:
   WAL data is sent as a series of CopyData messages.  (This allows
   other information to be intermixed; in particular the server can send
   an ErrorResponse message if it encounters a failure after beginning
-  to stream.)  The payload in each CopyData message follows this format:
+  to stream.)  The payload of each CopyData message from server to the
+  client contains a message of one of the following formats:
  
 
  
@@ -1390,34 +1391,32 @@ The commands accepted in walsender mode are:
   
   
   
-  Byte8
+  Int64
   
   
   
-  The starting point of the WAL data in this message, given in
-  XLogRecPtr format.
+  The starting point of the WAL data in this message.
   
   
   
   
   
-  Byte8
+  Int64
   
   
   
-  The current end of WAL on the server, given in
-  XLogRecPtr format.
+  The current end of WAL on the server.
   
   
   
   
   
-  Byte8
+  Int64
   
   
   
-  The server's system clock at the time of transmission,
-  given in TimestampTz format.
+  The server's system clock at the time of transmission, as
+  microseconds since midnight on 2000-01-01.
   
   
   
@@ -1445,25 +1444,12 @@ The commands accepted in walsender mode are:
continuation records can be sent in different CopyData messages.
  
  
-   Note that all fields within the WAL data and the above-described header
-   will be in the sending server's native format.  Endianness, and the
-   format for the timestamp, are unpredictable unless the receiver has
-   verified that the sender's system identifier matches its own
-   pg_control contents.
- 
- 
If the WAL sender process is terminated normally (during postmaster
shutdown), it will send a CommandComplete message before exiting.
This might not happen during an abnormal shutdown, of course.
  
 
  
-   The receiving process can send replies back to the sender at any time,
-   using one of the following message formats (also in the payload of a
-   CopyData message):
- 
-
- 
   
   
   
@@ -1495,12 +1481,23 @@ The commands accepted in walsender mode are:
   
   
   
-  Byte8
+  Int64
   
   
   
-  The server's system clock at the time of transmission,
-  given in TimestampTz format.
+  The server's system clock at the time of transmission, as
+  microseconds since midnight on 2000-01-01.
+  
+  
+  
+  
+  
+  Byte1
+  
+  
+  
+  1 means that the client should reply to this message as soon as
+  possible, to avoid a timeout disconnect. 0 otherwise.
   
   
   
@@ -1512,6 +1509,12 @@ The commands accepted in walsender mode are:
  
 
  
+   The receiving process can send re

Re: [BUGS] BUG #7598: Loss of view performance after dump/restore of the view definition

[Vaclav Juza]

And if it helps,
see below results of my explain(analyze, buffers) where it is worse on
9.2.1 than on 9.1.4 (both on the same hardware):

Original view:
pg 9.1.4:
Nested Loop  (cost=237.46..289.36 rows=1 width=254) (actual 
time=3998.609..5870.697 rows=1848 loops=1)
  Buffers: shared hit=1044261 read=5384
  ->  Nested Loop  (cost=237.46..281.07 rows=1 width=330) (actual 
time=3998.501..5844.433 rows=1848 loops=1)
Buffers: shared hit=1040565 read=5384
->  Nested Loop  (cost=237.46..272.77 rows=1 width=672) (actual 
time=3998.357..5791.577 rows=1848 loops=1)
  Buffers: shared hit=1034973 read=5384
  ->  Nested Loop  (cost=237.46..254.75 rows=1 width=558) (actual 
time=3997.604..4086.442 rows=308 loops=1)
Buffers: shared hit=671036 read=5381
->  HashAggregate  (cost=237.46..237.47 rows=1 width=342) 
(actual time=3995.176..3996.270 rows=506 loops=1)
  Buffers: shared hit=659911 read=5138
  ->  Nested Loop  (cost=4.28..237.45 rows=1 width=342) 
(actual time=2.679..3985.891 rows=3036 loops=1)
Buffers: shared hit=659911 read=5138
->  Nested Loop  (cost=4.28..229.15 rows=1 
width=304) (actual time=2.603..3896.570 rows=3036 loops=1)
  Buffers: shared hit=650736 read=5138
  ->  Nested Loop  (cost=4.28..211.13 
rows=1 width=190) (actual time=1.114..680.538 rows=2511 loops=1)
Buffers: shared hit=51214 read=2265
->  Bitmap Heap Scan on testtable3 
ug  (cost=4.28..12.75 rows=1 width=76) (actual time=0.069..0.111 rows=5 loops=1)
  Recheck Cond: ((ida)::text = 
'a4'::text)
  Filter: (status = 'A'::bpchar)
  Buffers: shared hit=3
  ->  Bitmap Index Scan on 
pk_testtable3  (cost=0.00..4.28 rows=4 width=0) (actual time=0.034..0.034 
rows=10 loops=1)
Index Cond: 
((ida)::text = 'a4'::text)
Buffers: shared hit=2
->  Index Scan using pk_testtable1 
on testtable1 tt1  (cost=0.00..198.37 rows=1 width=222) (actual 
time=29.150..134.763 rows=502
loops=5)
  Index Cond: (((ida)::text = 
'a4'::text) AND ((idd)::text = (ug.idd)::text) AND ((parname)::text = 
'PARNAME1'::text))
  Filter: ((val = 0::numeric) 
AND (lvl = (SubPlan 6)))
  Buffers: shared hit=51211 
read=2265
  SubPlan 6
->  Result  
(cost=8.62..8.63 rows=1 width=0) (actual time=0.051..0.052 rows=1 loops=7500)
  Buffers: shared 
hit=29825 read=175
  InitPlan 5 (returns 
$17)
->  Limit  
(cost=0.00..8.62 rows=1 width=32) (actual time=0.044..0.045 rows=1 loops=7500)
  Buffers: 
shared hit=29825 read=175
  ->  Index 
Scan Backward using pk_testtable1 on testtable1 tt1a  (cost=0.00..8.62 rows=1 
width=32) (actual
time=0.040..0.040 rows=1 loops=7500)
Index 
Cond: (((tt1.ida)::text = (ida)::text) AND ((tt1.idb)::text = (idb)::text) AND
((tt1.idc)::text = (idc)::text) AND ((tt1.idd)::text = (idd)::text) AND 
((tt1.parname)::text = (parname)::text) AND (lvl IS NOT NULL) AND (lvl <= 
400::numeric))

Buffers: shared hit=29825 read=175
  ->  Index Scan using pk_testtable2 on 
testtable2 tt2  (cost=0.00..18.00 rows=1 width=146) (actual time=0.218..1.272 
rows=1 loops=2511)
Index Cond: (((ida)::text = 
'a4'::text) AND ((idb)::text = (tt1.idb)::text) AND ((idc)::text = 
(tt1.idc)::text))
Filter: (lvl = (SubPlan 4))
Buffers: shared hit=599522 read=2873
SubPlan 4
  ->  Aggregate  (cost=9.00..9.01 
rows=1 width=32) (actual time=0.162..0.163 rows=1 loops=18216)
Buffers: shared hit=575373 
read=1023
   

[BUGS] EnterpriseDB Advanced Server 8.2 Supported Platforms

[Ricky Wong]

EnterpriseDB team,

 

I was wondering which Linux OS (i.e. CentOS) the Advanced Server 8.2
supports.

 

I was able to found Documentation for 8.3 (but not 8.2):

http://www.enterprisedb.com/docs/en/8.3/server/supported-platforms.html

 

Wonder if you can provide reference for 8.2 (similar to the URL above).
Thank you.

 

 

Regards,

 

Ricky Wong | System Analyst | Isobar Hong Kong

t. + 852 3962 4431 | f. + 852 3962 4567 | m. + 852 6194 5151 | e. 
ricky.w...@isobar.com  

 

16/F | 633 King's Road | North Point | Hong Kong | w. 
http://www.isobar.hk   

(Learn about our global network at http://www.isobar.com
 )

 

 

Campaign Asia Pacific Digital Agency of the Year 2011

 

Follow us at http://www.twitter.com/isobar
  & http://www.twitter.com/isobarHK
 

Become a fan at http://www.facebook.com/isobar
 

 

NOTICE: The information contained in the email may be private and
confidential. If you are not the intended recipient(s) or have otherwise
received this email in error, please delete the email and inform the
sender as soon as possible. This email may not be disclosed, used or
copied by anyone other than the intended recipient(s). Any opinions,
statements or comments contained in this email are not necessarily those
of the company. If you wish clarification of any matter, please request
confirmation in writing. We take precautions to minimise the risk of
this email containing a software virus but you should use virus checking
software.

 

Please consider the environment before printing this email

 

<>

Re: [BUGS] BUG #7602: Select with many joins against the same table is very slow compared to 9.2.0.

[Hedén Daniel]

I had never changed  `join_collapse_limit` it is still out commented is this 
not default.

When I try to run EXPLAIN ANALYZE on my query in PostgreSQL 9.2.1 it runs for 6 
hours (21724001 ms) and then gives this error:
ERROR:  could not load library "C:/Program 
Files/PostgreSQL/9.2/lib/postgis-2.0.dll": Invalid access to memory location.


** Error **

ERROR: could not load library "C:/Program 
Files/PostgreSQL/9.2/lib/postgis-2.0.dll": Invalid access to memory location.
SQL state: 58P01

Running EXPLAIN ANALYZE  on the query under 9.2.0 is no problem and it gives 
this result:
"HashAggregate  (cost=3160.16..3160.19 rows=1 width=963) (actual 
time=191.083..192.107 rows=2171 loops=1)"
"  ->  Nested Loop  (cost=395.14..3160.10 rows=1 width=963) (actual 
time=3.147..185.751 rows=2171 loops=1)"
"->  Nested Loop  (cost=395.14..3153.84 rows=1 width=965) (actual 
time=3.128..174.052 rows=2171 loops=1)"
"  Join Filter: (o."ObjectId" = a16."ObjectId")"
"  ->  Nested Loop  (cost=395.14..3147.73 rows=1 width=1025) 
(actual time=3.125..168.864 rows=2171 loops=1)"
"->  Nested Loop  (cost=395.14..3141.50 rows=1 width=1027) 
(actual time=3.121..163.960 rows=2171 loops=1)"
"  Join Filter: (o."ObjectId" = a15."ObjectId")"
"  ->  Nested Loop  (cost=395.14..3135.38 rows=1 
width=1019) (actual time=3.118..158.695 rows=2197 loops=1)"
"->  Nested Loop  (cost=395.14..3129.12 rows=1 
width=1021) (actual time=3.114..153.753 rows=2197 loops=1)"
"  Join Filter: (o."ObjectId" = 
a14."ObjectId")"
"  ->  Nested Loop  (cost=395.14..3123.00 
rows=1 width=1013) (actual time=3.111..148.162 rows=2197 loops=1)"
"->  Nested Loop  
(cost=395.14..3116.77 rows=1 width=1015) (actual time=3.107..143.292 rows=2197 
loops=1)"
"  Join Filter: (o."ObjectId" = 
a13."ObjectId")"
"  ->  Nested Loop  
(cost=395.14..3110.65 rows=1 width=1007) (actual time=3.103..137.994 rows=2197 
loops=1)"
"->  Nested Loop  
(cost=395.14..3104.42 rows=1 width=1009) (actual time=3.100..132.970 rows=2197 
loops=1)"
"  Join Filter: 
(o."ObjectId" = a12."ObjectId")"
"  ->  Nested Loop  
(cost=395.14..3098.30 rows=1 width=1001) (actual time=3.096..127.668 rows=2197 
loops=1)"
"->  Nested 
Loop  (cost=395.14..3092.05 rows=1 width=1003) (actual time=3.092..122.811 
rows=2197 loops=1)"
"  Join 
Filter: (o."ObjectId" = a11."ObjectId")"
"  ->  
Nested Loop  (cost=395.14..3085.93 rows=1 width=995) (actual 
time=3.089..117.657 rows=2197 loops=1)"
"   
 ->  Nested Loop  (cost=395.14..3079.68 rows=1 width=997) (actual 
time=3.085..112.684 rows=2197 loops=1)"
"   
   Join Filter: (o."ObjectId" = a10."ObjectId")"
"   
   ->  Nested Loop  (cost=395.14..3073.56 rows=1 width=989) (actual 
time=3.080..107.390 rows=2197 loops=1)"
"   
 ->  Nested Loop  (cost=395.14..3067.32 rows=1 width=991) (actual 
time=3.077..102.504 rows=2197 loops=1)"
"   
   Join Filter: (o."ObjectId" = a9."ObjectId")"
"   
   ->  Nested Loop  (cost=395.14..3061.20 rows=1 width=983) 
(actual time=3.073..97.326 rows=2197 loops=1)"
"   
 ->  Nested Loop  (cost=395.14..3054.97 rows=1 
width=985) (actual time=3.069..92.368 rows=2197 loops=1)"
"   
   Join Filter: (o."ObjectId" = a8."ObjectId")"
"   
   ->  Nested Loop  (cost=395.14..3048.85 rows=1 
width=977) (actual time=3.064..87.042 rows=2197 loops=1)"
"   
 ->  Nested Loop  (cost=395.14..3042.63 
rows=1 width=979) (actual ti

Re: [BUGS] EnterpriseDB Advanced Server 8.2 Supported Platforms

[Christopher Browne]

On Tue, Oct 16, 2012 at 2:24 AM, Ricky Wong  wrote:

> EnterpriseDB team,
>
> ** **
>
> I was wondering which Linux OS (i.e. CentOS) the Advanced Server 8.2
> supports.
>
> ** **
>
> I was able to found Documentation for 8.3 (but not 8.2):
>
> http://www.enterprisedb.com/docs/en/8.3/server/supported-platforms.html***
> *
>
> ** **
>
> Wonder if you can provide reference for 8.2 (similar to the URL above).
> Thank you.
>
> ** **
>
> ** **
>
> Regards,
>
> ** **
>
> *Ricky Wong *| System Analyst | Isobar Hong Kong
>
> t. + 852 3962 4431 | f. + 852 3962 4567 | m. + 852 6194 5151 | e.
> ricky.w...@isobar.com
>
> ** **
>
> 16/F | 633 King's Road | North Point | Hong Kong | w. http://www.isobar.hk
> 
>
> (Learn about our global network at http://www.isobar.com)
>
> ** **
>
> [image: logo1]
>
> *Campaign **Asia Pacific Digital Agency of the Year 2011*
>
> ** **
>
> Follow us at http://www.twitter.com/isobar &
> http://www.twitter.com/isobarHK
>
> Become a fan at http://www.facebook.com/isobar
>
> ** **
>
> NOTICE: The information contained in the email may be private and
> confidential. If you are not the intended recipient(s) or have otherwise
> received this email in error, please delete the email and inform the sender
> as soon as possible. This email may not be disclosed, used or copied by
> anyone other than the intended recipient(s). Any opinions, statements or
> comments contained in this email are not necessarily those of the company.
> If you wish clarification of any matter, please request confirmation in
> writing. We take precautions to minimise the risk of this email containing
> a software virus but you should use virus checking software.
>
> ** **
>
> *Please consider the environment before printing this email*
>
> ** **
>

This seems like something you should contact EnterpriseDB about, as opposed
to the PostgreSQL project's mailing lists.

Pointedly, this is not remotely close to being "a bug."

Supposing EnterpriseDB doesn't support a particular Linux distribution you
wish to use, that might be something you'd want to complain about, to them,
but it doesn't seem to represent a problem with the PostgreSQL code base
that requires rectifying.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
<>

Re: [BUGS] BUG #7578: Not able to drop user if S/he has permission on tablespace

[Alvaro Herrera]

Nikolay escribió:
> Where can I found this path?

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=412a4295b5390cfe4fd5b9fc84a25c6e2d1aa0ad

You can also download the 9.1 snapshot from
ftp.postgresql.org/pub/snapshot/9.1, which should already contain this
patch.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
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 #7610: planner get wrong rows estimate with LIKE operator

[sam]

The following bug has been logged on the website:

Bug reference:  7610
Logged by:  Sam Wong
Email address:  s...@hellosam.net
PostgreSQL version: 9.2.1
Operating system:   Windows 7 64-bit
Description:

Repro SQL:

CREATE TABLE a (id text, primary key (id));

INSERT INTO a SELECT to_char(generate_series, 'FM000') from
generate_series(1,100);

ANALYZE a;

Q1: EXPLAIN ANALYZE SELECT * from a where id like '0005000%';
-
 Index Only Scan using a_pkey on a  (cost=0.00..9.37 rows=100 width=8)
(actual time=0.170..0.173 rows=1 loops=1)
   Index Cond: ((id >= '0005000'::text) AND (id < '0005001'::text))
   Filter: (id ~~ '0005000%'::text)
   Heap Fetches: 1
 Total runtime: 0.229 ms
(5 rows)

Q2: EXPLAIN ANALYZE SELECT * from a where id >= '0005000' and id <
'0005001';
-
 Index Only Scan using a_pkey on a  (cost=0.00..9.37 rows=1 width=8) (actual
time=0.027..0.028 rows=1 loops=1)
   Index Cond: ((id >= '0005000'::text) AND (id < '0005001'::text))
   Heap Fetches: 1
 Total runtime: 0.072 ms
(4 rows)

Problems:
 * For Q1, the planner incorrectly estimates that there will be 100 rows.
For Q2, it gives a correct estimation.
 * My actual problem in the production is that - because of the much larger
estimation, it prefers a merge/hash join in the later stage of a complex
query, instead of a nested loop. The outcome is that query tooks 10 seconds
instead of 100ms.

Observations:
 * Q1 and Q2 should be the logically identical. The psql thinks the same
(refer to the Index Cond in the anazyle output)
 * The analyze output says that Q1 not only has the same index condition of
Q2, but with an additional filter, yet surprisingly it is estimated to have
more rows than Q2.



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