Re: [GENERAL] How is sorting work?
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
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
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"
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"
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"
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"
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"
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"
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