Re: [GENERAL] How is sorting work?

2014-06-01 Thread Rajeev rastogi

As your query contains order by on host_id, so it will be sorted only based on 
host_id. Since in your case host_id is same for both rows, the order in which 
host_name will be selected will be absolutely random.
If you want query to returns rows sorted on host_name also, then you should add 
host_name in order by clause as below:

Select host_id, host_name from host_view order by host_id, host_name;
So in this case first it will be sort based on host_id and then on host_name.

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Quang Thoi
Sent: 31 May 2014 05:44
To: pgsql-general@postgresql.org
Subject: [GENERAL] How is sorting work?

Any one knows how sorting works?

I am using postgresql 9.3 and runs on Linux machines.
I see different sorting order for the same set of return data.

On linux machines, databases are configured the same.
Database have encoding set to 'utf8' and locale='C'

query:
Select host_id, host_name from host_view order by host_id

hos_id (character varying 128)
host_name (character varying 255)

- On one linux machine (locate in U.S) , the query returned following:

host_idhost_name
  --
"00017486";"lnx2.xx.yy.com"
"00017486";"lnx1.xx.yy.com"

- On a different linux machine (locate in India), the query returned following:

host_idhost_name
  --
"00017486";"lnx1.xx.yy.com"
"00017486";"lnx2.xx.yy.com"

Thanks,
Quang.



Re: [GENERAL] Merge a sharded master into a single read-only slave

2014-06-01 Thread John R Pierce

On 6/1/2014 12:59 PM, Sébastien Lorion wrote:
it would have been nice to avoid the additional complexity if it had 
been possible to merge sharded tables on a binary level (which should 
be much faster than statement level), given that their records will 
never overlap (i.e. the same record is never present in many shards).


not even remotely possible, since binary replication is at a block 
level, NOT a tuple level...   Also, the index on this merged table will 
be considerably different than the index on any one of the sharded 
'masters' (and in binary replication, indexes are also block replicated).




--
john r pierce  37N 122W
somewhere on the middle of the left coast



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


Re: [GENERAL] Merge a sharded master into a single read-only slave

2014-06-01 Thread Sébastien Lorion
On Thu, May 29, 2014 at 12:58 PM, Sébastien Lorion  wrote:

> I have a master database sharded by user_id, with globally unique IDs for
> everything, except shared configuration data stored in global tables
> (resources strings, system parameters, etc).
>
> What would be the best (ie both fast and reliable, simple to maintain as a
> bonus) to merge all shards into a single read-only slave that will then be
> replicated and used for read queries ? I took a look at Londiste and
> repmgr, and can see some ways to accomplish that, but would appreciate the
> advice of people here.
>
> Thank you,
>
> Sébastien
>

​Answering myself, please correct me if my findings are wrong.

I cannot find a way to accomplish the above without using statement level
replication. That kind of defeat the point since if my DB is sharded, it's
to avoid having to vertically scale to sustain the write charge, but by
using statement level replication, I will now have to vertically scale the
slave, bringing me back to square one.

So my conclusion is that for now, the best way to scale read-only queries
for a sharded master is to implement map-reduce at the application level.
Fortunately, most of the time, read queries scope can be limited to a
single shard, but nonetheless, it would have been nice to avoid the
additional complexity if it had been possible to merge sharded tables on a
binary level (which should be much faster than statement level), given that
their records will never overlap (i.e. the same record is never present in
many shards).

​Sébastien​


Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread Adrian Klaver

On 06/01/2014 11:42 AM, David Wall wrote:


On 6/1/2014 9:05 AM, Adrian Klaver wrote:



The JDBC code above, if I am following correctly, is picking up a
default timezone of 'PST' and then in the first if returning that as
the tz value to SET TimeZone in the startup packet.

Two things.

1) Where is it getting PST from ?

2) Should the driver even be returning an abbreviation given that
Postgres will not accept it as a TimeZone value?




Thanks for the extra help, Adrian.

It led me to investigate the 35 webapps we deploy on Tomcat and I found
2 rogue apps that set their timezone to "PST".  Once I fixed these two,
all is working great again.

I guess there's no bug, per se, except in our configuration.  Once we
changed it to PST8PDT, all was good again.


Not so much a bug as a misplaced error. Given that TimeZone does not 
accept abbreviations, it would seem that JDBC code should throw an 
exception at that point. It would not change the end result, but make it 
easier to determine where the error is occurring. In the long run it may 
be moot though, because of this:


http://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html

Three-letter time zone IDs
For compatibility with JDK 1.1.x, some other three-letter time zone IDs 
(such as "PST", "CTT", "AST") are also supported. However, their use is 
deprecated because the same abbreviation is often used for multiple time 
zones (for example, "CST" could be U.S. "Central Standard Time" and 
"China Standard Time"), and the Java platform can then only recognize 
one of them.




Thanks,
David






--
Adrian Klaver
adrian.kla...@aklaver.com


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


Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread David Wall


On 6/1/2014 9:05 AM, Adrian Klaver wrote:


I ask because a look at the PG JDBC code shows this, which did not 
show up until Sept 22, 2011. Not sure what release, but it looks like 
9.2+:


// Construct and send a startup packet.
String[][] params = {
{ "user", user },
{ "database", database },
{ "client_encoding", "UTF8" },
{ "DateStyle", "ISO" },
{ "extra_float_digits", "2" },
{ "TimeZone", 
createPostgresTimeZone() },



 /**
 * Convert Java time zone to postgres time zone.
 * All others stay the same except that GMT+nn changes to GMT-nn and
 * vise versa.
 *
 * @return The current JVM time zone in postgresql format.
 */
private String createPostgresTimeZone() {
String tz = TimeZone.getDefault().getID();
if (tz.length() <= 3 || !tz.startsWith("GMT")) {
return tz;
}
char sign = tz.charAt(3);
String start;
if (sign == '+') {
start = "GMT-";
} else if (sign == '-') {
start = "GMT+";
} else {
// unknown type
return tz;
}

return start + tz.substring(4);

Ahh I see the problem.

From here:
A time zone abbreviation, for example PST. Such a specification merely 
defines a particular offset from UTC, in contrast to full time zone 
names which can imply a set of daylight savings transition-date rules 
as well. The recognized abbreviations are listed in the 
pg_timezone_abbrevs view (see Section 47.70). You cannot set the

  ^^
configuration parameters TimeZone or log_timezone to a time zone

abbreviation, but you can use abbreviations in date/time input values
^
and with the AT TIME ZONE operator.


So:

test=> select version();
   version

- 

 PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit

(1 row)

test=> set TimeZone='PST';
ERROR:  invalid value for parameter "TimeZone": "PST"
test=> set TimeZone='PST8PDT';
SET

The JDBC code above, if I am following correctly, is picking up a 
default timezone of 'PST' and then in the first if returning that as 
the tz value to SET TimeZone in the startup packet.


Two things.

1) Where is it getting PST from ?

2) Should the driver even be returning an abbreviation given that 
Postgres will not accept it as a TimeZone value?





Thanks for the extra help, Adrian.

It led me to investigate the 35 webapps we deploy on Tomcat and I found 
2 rogue apps that set their timezone to "PST".  Once I fixed these two, 
all is working great again.


I guess there's no bug, per se, except in our configuration.  Once we 
changed it to PST8PDT, all was good again.


Thanks,
David



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


Re: [GENERAL] [JDBC] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread Tom Lane
David Wall  writes:
> The exception occurs when JDBC tries to connect to PG:

> 2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException: 
> ConnectionPool.makeConnection(esf) to URL: 
> jdbc:postgresql://localhost.localdomain:25432/zingr:
> 2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid 
> value for parameter "TimeZone": "PST"
> org.postgresql.util.PSQLException: FATAL: invalid value for parameter 
> "TimeZone": "PST"

Hm.  libpq will try to set TimeZone at connection time if it sees a value
for the environment variable "PGTZ"; so, if you were using libpq, this
would be explainable by different environment settings in different cases.
There is probably some comparable behavior in the JDBC driver, but I don't
know exactly what.

One hole in this type of theory is that it doesn't explain a behavioral
difference between 8.3.x and 9.3.x; unless maybe the JDBC driver's
behavior changed in this regard since then.  libpq has done the PGTZ thing
for a very long time.

regards, tom lane


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


Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread Adrian Klaver

On 06/01/2014 12:16 AM, David Wall wrote:

On 5/31/2014 11:47 PM, John R Pierce wrote:

On 5/31/2014 11:41 PM, David Wall wrote:


 FATAL:  invalid value for parameter "TimeZone": "PST"


I'd be using "America/Los_Angeles" as the timezone rather than PST,
as the TLA timezones are ambiguous (CST is both Central Standard Time
in the USA, and China Standard Time).

but thats probably not the problem, hard to guess what is, sounds
like you have a lot going on.




PG itself has the timezone US/Pacific set in postgresql.conf

In Linux, we show:
lrwxrwxrwx 1 root root 39 Apr 23 16:00 /etc/localtime ->
/usr/share/zoneinfo/America/Los_Angeles

Even when we use PST in our Java code, we use PST8PDT.

The exception occurs when JDBC tries to connect to PG:

2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException:
ConnectionPool.makeConnection(esf) to URL:
jdbc:postgresql://localhost.localdomain:25432/zingr:
2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid
value for parameter "TimeZone": "PST"
org.postgresql.util.PSQLException: FATAL: invalid value for parameter
"TimeZone": "PST"



And of course none seems to explain why it works often enough, but then
fails as more apps are deployed.  Ugh!


What version of Postgres JDBC are you using on your 8.3.3 machine?

I ask because a look at the PG JDBC code shows this, which did not show 
up until Sept 22, 2011. Not sure what release, but it looks like 9.2+:


// Construct and send a startup packet.
String[][] params = {
{ "user", user },
{ "database", database },
{ "client_encoding", "UTF8" },
{ "DateStyle", "ISO" },
{ "extra_float_digits", "2" },
{ "TimeZone", 
createPostgresTimeZone() },



 /**
 * Convert Java time zone to postgres time zone.
 * All others stay the same except that GMT+nn changes to GMT-nn and
 * vise versa.
 *
 * @return The current JVM time zone in postgresql format.
 */
private String createPostgresTimeZone() {
String tz = TimeZone.getDefault().getID();
if (tz.length() <= 3 || !tz.startsWith("GMT")) {
return tz;
}
char sign = tz.charAt(3);
String start;
if (sign == '+') {
start = "GMT-";
} else if (sign == '-') {
start = "GMT+";
} else {
// unknown type
return tz;
}

return start + tz.substring(4);

Ahh I see the problem.

From here:
A time zone abbreviation, for example PST. Such a specification merely 
defines a particular offset from UTC, in contrast to full time zone 
names which can imply a set of daylight savings transition-date rules as 
well. The recognized abbreviations are listed in the pg_timezone_abbrevs 
view (see Section 47.70). You cannot set the

  ^^
configuration parameters TimeZone or log_timezone to a time zone

abbreviation, but you can use abbreviations in date/time input values
^
and with the AT TIME ZONE operator.


So:

test=> select version();
   version 



- 

 PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit 


(1 row)

test=> set TimeZone='PST';
ERROR:  invalid value for parameter "TimeZone": "PST"
test=> set TimeZone='PST8PDT';
SET

The JDBC code above, if I am following correctly, is picking up a 
default timezone of 'PST' and then in the first if returning that as the 
tz value to SET TimeZone in the startup packet.


Two things.

1) Where is it getting PST from ?

2) Should the driver even be returning an abbreviation given that 
Postgres will not accept it as a TimeZone value?











--
Adrian Klaver
adrian.kla...@aklaver.com


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


Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread John R Pierce

On 6/1/2014 12:16 AM, David Wall wrote:
at 
com.esignforms.db.ConnectionPool.makeConnection(ConnectionPool.java:302) 


that sounds like some form of connection pool.

perhaps connections are being shared between client processes that 
expect different timezones?


--
john r pierce  37N 122W
somewhere on the middle of the left coast



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


Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

2014-06-01 Thread David Wall

On 5/31/2014 11:47 PM, John R Pierce wrote:

On 5/31/2014 11:41 PM, David Wall wrote:


 FATAL:  invalid value for parameter "TimeZone": "PST" 


I'd be using "America/Los_Angeles" as the timezone rather than PST, 
as the TLA timezones are ambiguous (CST is both Central Standard Time 
in the USA, and China Standard Time).


but thats probably not the problem, hard to guess what is, sounds 
like you have a lot going on.




PG itself has the timezone US/Pacific set in postgresql.conf

In Linux, we show:
lrwxrwxrwx 1 root root 39 Apr 23 16:00 /etc/localtime -> 
/usr/share/zoneinfo/America/Los_Angeles


Even when we use PST in our Java code, we use PST8PDT.

The exception occurs when JDBC tries to connect to PG:

2014-05-31 22:14:34,351 ERROR (eSignForms) SQLException: 
ConnectionPool.makeConnection(esf) to URL: 
jdbc:postgresql://localhost.localdomain:25432/zingr:
2014-05-31 22:14:34,352 ERROR (eSignForms)   Message:   FATAL: invalid 
value for parameter "TimeZone": "PST"
org.postgresql.util.PSQLException: FATAL: invalid value for parameter 
"TimeZone": "PST"
at 
org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:574)
at 
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:177)
at 
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at 
org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:138)
at 
org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:29)
at 
org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:21)
at 
org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:31)
at 
org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:24)

at org.postgresql.Driver.makeConnection(Driver.java:410)
at org.postgresql.Driver.connect(Driver.java:280)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at 
com.esignforms.db.ConnectionPool.makeConnection(ConnectionPool.java:302)


And of course none seems to explain why it works often enough, but then 
fails as more apps are deployed.  Ugh!



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