[GENERAL] Postgresql8.4 install breaks Evolution on Ubuntu 9.10
Hi all, I'm wondering if someone here know how to go about fixing this problem that apparently affects everyone who manually install Postgresql8.4 on Ubuntu Karmic(9.10). Postgres installation seems to mess with something that renders other applications unable to function. For instance my problem is with Evolution Mail. This is the output I started getting after installing postgres: evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by evolution) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libemiscwidgets.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libetable.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libebook-1.2.so.9) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libedataserver-1.2.so.11) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libsoup-2.4.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: relocation error: /usr/lib/evolution/2.28/libeutil.so.0: symbol xmlFirstElementChild, version LIBXML2_2.7.3 not defined in file libxml2.so.2 with link time reference I can't be sure(no logs) but VMWare Workstation seems to have been affected here too, can't get it to work anymore. Other people having the same problem: http://ubuntuforums.org/showthread.php?t=1307864 https://bugs.launchpad.net/ubuntu/+bug/461105 Best regards, Leonardo C.
[GENERAL] Locale query
Hi, I have a query re localization. I am running Centos 4.3 and am using a CMS using Postgresql 8.1.18-2 (installed via yum). The documentation for the CMS advise to ensure Postgresql is initialised with C locale for performance reasons. To accomplish they advise: 1. in /etc/sysconfig/i18n replace LANG=en_US.UTF-8 with LANG= 2. After installing Postgresql with: initdb -D /var/lib/pgsql/data --locale=C I have done the above but when I run su postgres then 'locale' I get the following: LANG= LC_CTYPE=POSIX LC_NUMERIC=POSIX LC_TIME=POSIX LC_COLLATE=POSIX LC_MONETARY=POSIX LC_MESSAGES=POSIX LC_PAPER=POSIX LC_NAME=POSIX LC_ADDRESS=POSIX LC_TELEPHONE=POSIX LC_MEASUREMENT=POSIX LC_IDENTIFICATION=POSIX LC_ALL= My Postgresql.conf however has the following locale settings: **# - Locale and Formatting -** #datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ # environment setting #australian_timezones = off #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii # actually, defaults to database # encoding # These settings are initialized by initdb -- they might be changed lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting Is everything ok with the above or something has gone wrong? I guess I was expecting the locale command would show C instead of POSIX. Thanks!! Andy -- 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] Postgresql8.4 install breaks Evolution on Ubuntu 9.10
A quick-fix solution is deleting the file '/opt/PostgreSQL/8.4/lib/libxml2.so.2'. On 11/28/2009 04:23 PM, Leonardo Camargo wrote: Hi all, I'm wondering if someone here know how to go about fixing this problem that apparently affects everyone who manually install Postgresql8.4 on Ubuntu Karmic(9.10). Postgres installation seems to mess with something that renders other applications unable to function. For instance my problem is with Evolution Mail. This is the output I started getting after installing postgres: evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by evolution) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libemiscwidgets.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libetable.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libebook-1.2.so.9) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libedataserver-1.2.so.11) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libsoup-2.4.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: relocation error: /usr/lib/evolution/2.28/libeutil.so.0: symbol xmlFirstElementChild, version LIBXML2_2.7.3 not defined in file libxml2.so.2 with link time reference I can't be sure(no logs) but VMWare Workstation seems to have been affected here too, can't get it to work anymore. Other people having the same problem: http://ubuntuforums.org/showthread.php?t=1307864 https://bugs.launchpad.net/ubuntu/+bug/461105 Best regards, Leonardo C. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] Postgresql8.4 install breaks Evolution on Ubuntu 9.10
On Sat, Nov 28, 2009 at 11:53, Leonardo Camargo camargoleona...@gmail.com wrote: Hi all, I'm wondering if someone here know how to go about fixing this problem that apparently affects everyone who manually install Postgresql8.4 on Ubuntu Karmic(9.10). Postgres installation seems to mess with something that renders other applications unable to function. For instance my problem is with Evolution Mail. This is the output I started getting after installing postgres: evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by evolution) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libemiscwidgets.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgdata-1.2.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libetable.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/evolution/2.28/libeutil.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libebook-1.2.so.9) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libedataserver-1.2.so.11) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libsoup-2.4.so.1) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: /opt/PostgreSQL/8.4/lib/libxml2.so.2: no version information available (required by /usr/lib/libgnomevfs-2.so.0) evolution: relocation error: /usr/lib/evolution/2.28/libeutil.so.0: symbol xmlFirstElementChild, version LIBXML2_2.7.3 not defined in file libxml2.so.2 with link time reference I can't be sure(no logs) but VMWare Workstation seems to have been affected here too, can't get it to work anymore. Other people having the same problem: http://ubuntuforums.org/showthread.php?t=1307864 https://bugs.launchpad.net/ubuntu/+bug/461105 This looks like an install from the 1-clicks, right? It looks to me that it's not karmic-compatible - try installing the debian packages instead (should be a simple apt-get install postgresql-8.4 - it's included by default in Karmic IIRC). I've done that many times without any issues like this. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Locale query
Hi, I have a query re localization. I am running Centos 4.3 and am using a CMS using Postgresql 8.1.18-2 (installed via yum). The documentation for the CMS advise to ensure Postgresql is initialised with C locale for performance reasons. To accomplish they advise: 1. in /etc/sysconfig/i18n replace LANG=en_US.UTF-8 with LANG= 2. After installing Postgresql with: initdb -D /var/lib/pgsql/data --locale=C I have done the above but when I run su postgres then 'locale' I get the following: LANG= LC_CTYPE=POSIX LC_NUMERIC=POSIX LC_TIME=POSIX LC_COLLATE=POSIX LC_MONETARY=POSIX LC_MESSAGES=POSIX LC_PAPER=POSIX LC_NAME=POSIX LC_ADDRESS=POSIX LC_TELEPHONE=POSIX LC_MEASUREMENT=POSIX LC_IDENTIFICATION=POSIX LC_ALL= My Postgresql.conf however has the following locale settings: **# - Locale and Formatting -** #datestyle = 'iso, mdy' #timezone = unknown # actually, defaults to TZ # environment setting #australian_timezones = off #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii # actually, defaults to database # encoding # These settings are initialized by initdb -- they might be changed lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting Is everything ok with the above or something has gone wrong? I guess I was expecting the locale command would show C instead of POSIX. Thanks!! Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date with time zone
Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a date with time zone data type? I mean, in the same manner that every country does not have the same time (due to the time zone they are in), they also don't have to be in the same day (for the same reason). Maybe it's January 10th in one place, and January 11st a couple of time zones ahead. So, in the same way that a simple time data type is not enough for precise time specification on multi time zone setups, a simple date data type is also not enough for a precise date specification in those setups. Of course you can always set another column, specifying that that date actually corresponds to a specific timezone, but in the same manner that u dont need an extra column for time values (cause u have the time with time zone), you shouldn't be needing to create another one to host the time zone for the date. I don't know, am I crazy? Thanks a lot. Eduardo.
[GENERAL] Strange bug in dump ?
Dear all, When doing a dump of a 8.1.17, database (that I have to reimport daily into a 7.4.6), I have a strange bugs with accents... First point, I had to add --insert. But there is still a problem with accents. If I say nothing the dump is utf8, or I can add -E=latin1. But in both cases it fails, and there is a very strange error ! There is a E before the street field ! This is an extract of the dump : 'ST311PHANIE ROBERT', NULL, E'RTE DES TATTES D\\''OIE 5', 311 is shown instead of accent Does someone knows what could be this problem ? Denis -- 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] Strange bug in dump ?
On Sat, Nov 28, 2009 at 12:59, Denis BUCHER dbuche...@hsolutions.ch wrote: [...] There is a E before the street field ! This is an extract of the dump : 'ST311PHANIE ROBERT', NULL, E'RTE DES TATTES D\\''OIE 5', Sounds like escape quoting to me. You can read about it at http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html. I'm not sure how good 7.4.6 is at unicode-quoting, but I surmise this could be the source of the error that it is not able to handle the correct string quoting. -- - Rikard - http://bos.hack.org/cv/ -- 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] Strange bug in dump ?
Rikard Bosnjakovic a écrit : There is a E before the street field ! This is an extract of the dump : 'ST311PHANIE ROBERT', NULL, E'RTE DES TATTES D\\''OIE 5', Sounds like escape quoting to me. You can read about it at http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html. I'm not sure how good 7.4.6 is at unicode-quoting, but I surmise this could be the source of the error that it is not able to handle the correct string quoting. Oh yes, great, then it's not a bug but String Constants with C-Style Escapes ! But this seems to be a 8.4 feature not supported by 7.4.6 ? If you have an idea on how to have 7.4.6 accept it... Thanks a lot for your help :-) Denis -- 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] Locale query
On Sat, Nov 28, 2009 at 09:46:18PM +1100, ad...@buydirect.net.au wrote: Is everything ok with the above or something has gone wrong? I guess I was expecting the locale command would show C instead of POSIX. The C and POSIX locales are one and the same thing. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. signature.asc Description: Digital signature
[GENERAL] Postgres security article
FYI I thought it was a good read, hope someone else finds it useful http://www.ibm.com/developerworks/opensource/library/os- postgresecurity/index.html Colin -- You own a dog, but you can only feed a cat. -- 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] Postgres security article
Colin Streicher a écrit : FYI I thought it was a good read, hope someone else finds it useful http://www.ibm.com/developerworks/opensource/library/os- postgresecurity/index.html Colin Very nice page indeed, Colin, Thanks! -- Aliquid melius quam pessimum optimum non est. -- 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] Postgres security article
On 11/28/2009 7:47 AM, Colin Streicher wrote: http://www.ibm.com/developerworks/opensource/library/os- postgresecurity/index.html Nice link, but it got broken for me: http://tinyurl.com/ylro9y9 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres log file mode
Is it possible to force file mode of postgres log files, eg. as if it was changed by chmod 640 /log/file.log at present (I use 8.4.1) it is generated with mode 600. the goal is to let users in postgtres group to read these files. thanks in advance. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- 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] Cannot allocate memory for output buffer
On Fri, Nov 27, 2009 at 05:55:30PM -0500, Peter Erickson wrote: Thanks. Out of curiosity, if memory exhaustion was the problem, any idea why the task manager would show that I'm only using 1.2GB of the 3GB of memory? Note that what usually kills you first on 32-bit systems is that you want 250MB of *contiguous* memory. Depending on how the program is started and if you have memory map randomization, it becomes entirely possible to have more than 1GB free, yet no blocks big enough to hold 250MB together. 64-bit systems don't suffer from this problem, the address space is so large it's just not an issue. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Strange bug in dump ?
Denis BUCHER dbuche...@hsolutions.ch writes: But this seems to be a 8.4 feature not supported by 7.4.6 ? If you have an idea on how to have 7.4.6 accept it... Better idea: expend your effort on getting off 7.4.x. It's going to be EOL'd soon. Running a release as old as 7.4.6 doesn't seem very advisable anyway. 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] return value for PQbinaryTuples
On Fri, Nov 27, 2009 at 3:33 AM, bbhe bbhe_2...@163.com wrote: hi all, I don't why PQbinaryTuples function returns 1 even the select statement only returns two integer fields. Although there are some columns with type bytea in the table. PQbinaryTuples is basically going to return whatever you passed into resultformat when you executed the query (in the case of PQexec, it's going to be 1 always). The specific types of fields you are querying is immaterial. See the documentation for PQexecParams(). If you want data returned in binary you should ask for it that way (this means not using PQexec to issue queries). If you are looking for a broader way of dealing with binary with libpq (especially if you are using 8.4), you will want to check out libpqtypes (which uses binary protocol always): /* send some data */ PGresult *res = PQexecf(conn, INSERT INTO t VALUES (%int4, %text), 654321, some text); /* read some data out of a result */ PQgetf(res, 0, #int4 #text, a, i4, t, text); http://libpqtypes.esilo.com/ merlin -- 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] Date with time zone
On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote: Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a date with time zone data type? I mean, in the same manner that every country does not have the same time (due to the time zone they are in), they also don't have to be in the same day (for the same reason). Maybe it's January 10th in one place, and January 11st a couple of time zones ahead. So, in the same way that a simple time data type is not enough for precise time specification on multi time zone setups, a simple date data type is also not enough for a precise date specification in those setups. Of course you can always set another column, specifying that that date actually corresponds to a specific timezone, but in the same manner that u dont need an extra column for time values (cause u have the time with time zone), you shouldn't be needing to create another one to host the time zone for the date. I don't know, am I crazy? Thanks a lot. Eduardo. The best explanation I can offer comes from the manual. http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are: * Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries. * The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries. To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Public and Grants
I have a database with a schema called ISS. This is where all of our application defintions are stored. We did add 2 contribute modules (citext) and guid generator and both of these by default went to the public schema. It is our intent to not allow any access to public by our users. A few questions 1. Can I reinstall the contrib modules in the ISS schema only or do they need to be in the public schema 2. If they need to stay in the public schema and I don't want to give any insert, update, delete or select access to public, can I revoke those privileges and just give execute on the functions that were added by the contrib module. 3. If I can reinstall the contrib modules in the application schema, can I delete the public schema or does it still need to be there and I would just revoke all except for the superuser id which would be for our installer or tech support if needed. We have a separate userid for the security administrator. All of the functions that the security administrator needs are provided by a application module and they will not be directly accessing the database via a SQL utility at all. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Access a Field / Column of a resultset by Number
hy scott, thanks for your awnser. yes thats clear. but i can't find any sourcecode. the pl* language doesnt matter, i need that feature only in one function all over my db. did you have any sourcecode examples? http://www.postgresql.org/docs/8.4/interactive/plperl-database.html nothing about how to access a column by fieldname. daniel Scott Marlowe schrieb: On Fri, Nov 27, 2009 at 10:09 AM, Daniel Schuchardt d.schucha...@prodat-sql.de wrote: thats exactly the same i'm looking for: http://wiki.postgresql.org/wiki/Todo http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php (todo list for plpgsql) *Server-Side Languages *PL/pgSQL * *[D] Allow listing of record column names, and access to record columns *via variables, e.g. columns := r.(*), tval2 := r.(colname) * *Re: PL/PGSQL: Dynamic Record Introspection is that is possible in any pl* language? Yes, if the language has the architecture to handle it. plpgsql doesn't right now. pltcl, plperl, and plain old C functions can examine records and do dynamic stuff with them. Any attempt at doing dynamic queries right now in plpgsql leads to madness, or so I've been told. -- Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- 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] vacuumdb -z do a reindex?
Hi Scott, Scott Marlowe wrote: On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg ib...@noao.edu wrote: I've had a simple update running for over 4 hours now (see results from pg_top below). The sql is: Have you looked in pg_locks and pg_stat_activity? Yes, I did look at pg_stat_activity and did not see anything alarming. What would have been indicators of something bad? The runtime was the only alarming thing I saw. The database has 1016789 records, vacuumdb -z is ran once a day. I have not ran 'reindexdb' in weeks. The system is a: 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB SAS drives configured with software RAID10 So do you have autovacuum disabled? What pg version are you running? Yes. It seems simpler than trying to configure the many options. an 8 drive RAID array is usually pretty fast, unless it's on a bad RAID controller or something. What do vmstat 10 and iostat -x 10 say about your io activity? -bash-3.2$ vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 0 0 0 21143944 471304 892801600 0 401 2 1 97 0 0 -bash-3.2$ iostat -x 10 Linux 2.6.18-128.1.10.el5 (archdbn1)11/28/09 avg-cpu: %user %nice %system %iowait %steal %idle 1.910.001.420.000.00 96.67 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.01 5.19 0.03 3.27 2.7167.6921.37 0.025.71 0.12 0.04 sda1 0.00 0.00 0.00 0.00 0.00 0.0011.66 0.001.84 1.27 0.00 sda2 0.01 4.07 0.02 3.21 2.6958.2418.84 0.025.69 0.12 0.04 sda3 0.00 0.00 0.00 0.00 0.01 0.0411.15 0.000.83 0.80 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 2.00 0.008.75 8.75 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.0115.44 0.000.82 0.70 0.00 sda6 0.00 1.12 0.00 0.05 0.01 9.41 171.06 0.007.70 0.13 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.000.000.010.000.00 99.99 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda2 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 I did a reindexdb today, and it took less than 2 minutes. So I don't think it had anything to do with a bloated db or index. I need some utilities and training to be able to convince myself when a problem is with the 'system' (PostgreSQL+hw+config) vs design of the db. Thanks to all who responded. Cheers, --irene Your comments are appreciated. --irene last pid: 1185; load avg: 2.17, 2.21, 1.60; up 38+01:36:40 13:52:27 14 processes: 2 running, 12 sleeping CPU states: 14.0% user, 0.0% nice, 10.5% system, 75.4% idle, 0.0% iowait Memory: 11G used, 20G free, 456M buffers, 8724M cached Swap: PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 28508 postgres 170 93M 38M run 265:53 58.42% 99.08% postgres: postgres metadata 140.252.26.34(34717) UPDATE 31609 postgres 160 91M 36M run 7:05 57.85% 98.09% postgres: system_admin metadata 140.252.26.34(43303) SELECT 25156 postgres 160 102M 46M sleep 7:28 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(40350) idle 25363 postgres 180 93M 37M sleep 5:08 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(35951) idle 31622 postgres 150 95M 38M sleep 1:45 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(51917) idle 31624 postgres 150 95M 38M sleep 0:14 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(53908) idle 28755 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41270) idle 28757 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41272) idle 28756 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41271)
Re: [GENERAL] vacuumdb -z do a reindex?
Irene Barg wrote: avg-cpu: %user %nice %system %iowait %steal %idle 0.000.000.010.000.00 99.99 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda2 0.00 0.60 0.00 1.00 0.0012.8012.80 0.000.00 0.00 0.00 sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 that appears to be a system thats close to totally idle. what volume is sda2 ? It had an average of 1 write of 12.8 sectors/second thats about 6.5 kbyte/sec, whihc easily could be .bash_history or syslog or other background activity -- 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] Date with time zone
Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to Dec 19th without adding an innecesary reference to a dummy time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say Dec 19th (GMT-3) ? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? I'm no guru, but I don't see any obvious technical impossibility to do so. Is this so just because SQL standard says so? Can it be possible that SQL standard is a little short on this kind of need? Again, of course I can always use a timestamp set to 00:00:00 just to use its time zone tracking capabilities, but It is just as dirty as any other patch. A date is a date, and a timestamp is a timestamp, and both, used independently, should be able to keep track of its associated time zone, I think. Am I wrong on this? Apart from what SQL Standard may say, for instance. On Sat, Nov 28, 2009 at 4:00 PM, Adrian Klaver akla...@comcast.net wrote: On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote: Hello list, this is my first msg here. I hope this is the correct place for this subject, I couldn't find any more specific list for this. This thought had been bugging me for some time now and I thought it was time to share it with you pg gurus. Why in god's sake is there not a date with time zone data type? I mean, in the same manner that every country does not have the same time (due to the time zone they are in), they also don't have to be in the same day (for the same reason). Maybe it's January 10th in one place, and January 11st a couple of time zones ahead. So, in the same way that a simple time data type is not enough for precise time specification on multi time zone setups, a simple date data type is also not enough for a precise date specification in those setups. Of course you can always set another column, specifying that that date actually corresponds to a specific timezone, but in the same manner that u dont need an extra column for time values (cause u have the time with time zone), you shouldn't be needing to create another one to host the time zone for the date. I don't know, am I crazy? Thanks a lot. Eduardo. The best explanation I can offer comes from the manual. http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are: * Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset can vary through the year with daylight-saving time boundaries. * The default time zone is specified as a constant numeric offset from UTC. It is therefore impossible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries. To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time. -- Adrian Klaver akla...@comcast.net
Re: [GENERAL] vacuumdb -z do a reindex?
On Sat, Nov 28, 2009 at 3:12 PM, Irene Barg ib...@noao.edu wrote: Hi Scott, Scott Marlowe wrote: On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg ib...@noao.edu wrote: I've had a simple update running for over 4 hours now (see results from pg_top below). The sql is: Have you looked in pg_locks and pg_stat_activity? Yes, I did look at pg_stat_activity and did not see anything alarming. What would have been indicators of something bad? The runtime was the only alarming thing I saw. In pg_stat_activity anything that's waiting and has been for any length of time. The database has 1016789 records, vacuumdb -z is ran once a day. I have not ran 'reindexdb' in weeks. The system is a: 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB SAS drives configured with software RAID10 So do you have autovacuum disabled? What pg version are you running? Yes. It seems simpler than trying to configure the many options. Bad idea usually unless you're sure it's a problem. an 8 drive RAID array is usually pretty fast, unless it's on a bad RAID controller or something. What do vmstat 10 and iostat -x 10 say about your io activity? -bash-3.2$ vmstat 10 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 0 21143944 471304 8928016 0 0 0 4 0 1 2 1 97 0 0 The first line is since the machine started up, the lines AFTER that are what's important really. -bash-3.2$ iostat -x 10 Linux 2.6.18-128.1.10.el5 (archdbn1) 11/28/09 avg-cpu: %user %nice %system %iowait %steal %idle 1.91 0.00 1.42 0.00 0.00 96.67 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.01 5.19 0.03 3.27 2.71 67.69 21.37 0.02 5.71 0.12 0.04 sda1 0.00 0.00 0.00 0.00 0.00 0.00 11.66 0.00 1.84 1.27 0.00 sda2 0.01 4.07 0.02 3.21 2.69 58.24 18.84 0.02 5.69 0.12 0.04 sda3 0.00 0.00 0.00 0.00 0.01 0.04 11.15 0.00 0.83 0.80 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 2.00 0.00 8.75 8.75 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.01 15.44 0.00 0.82 0.70 0.00 sda6 0.00 1.12 0.00 0.05 0.01 9.41 171.06 0.00 7.70 0.13 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.00 0.00 0.01 0.00 0.00 99.99 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.60 0.00 1.00 0.00 12.80 12.80 0.00 0.00 0.00 0.00 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda2 0.00 0.60 0.00 1.00 0.00 12.80 12.80 0.00 0.00 0.00 0.00 sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 This machine looks idle, was this WHILE the bad queries etc were running? The rest of the time it'll tell us nothing. -- 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] Date with time zone
On Saturday 28 November 2009 3:41:42 pm Eduardo Piombino wrote: Hi Adrian, thanks for your answer. I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? If i just want to store a reference to Dec 19th without adding an innecesary reference to a dummy time, like 00:00:00 (for time zone tracking's sake), wouldn't it be elegant to be able to say Dec 19th (GMT-3) ? The problem arises around the dates when DST starts and ends. For instance here, Washington State USA, Nov 1st was the change over date. This occurred at 2:00 AM in the morning, so on Nov 1st we where in two time zones PDT then PST. Without a reference to time it makes it hard to keep track. On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? I'm no guru, but I don't see any obvious technical impossibility to do so. Is this so just because SQL standard says so? Can it be possible that SQL standard is a little short on this kind of need? I will let the SQL gurus answer this one. Again, of course I can always use a timestamp set to 00:00:00 just to use its time zone tracking capabilities, but It is just as dirty as any other patch. As stated above time zones only have meaning with respect to date and time together. A date is a date, and a timestamp is a timestamp, and both, used independently, should be able to keep track of its associated time zone, I think. Am I wrong on this? Apart from what SQL Standard may say, for instance. I would suggest searching the archives. There has been discussions in the past about 'tagged' fields that would track timezones independent of a time/date/timestamp field. -- Adrian Klaver akla...@comcast.net -- 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] Date with time zone
Eduardo Piombino drak...@gmail.com writes: I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considering that many jurisdictions don't switch daylight savings time at local midnight. How would you know which zone applied on a DST transition date? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? Because the SQL committee were smoking something strange that day. You won't find anybody around here who will defend the existence of TIME WITH TIME ZONE. We only put it in for minimal spec compliance. 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
[GENERAL] Re: vacuumdb: vacuuming of database xy failed: PANIC: corrupted item pointer: 19227
On 19 nov., 16:36, t...@sss.pgh.pa.us (Tom Lane) wrote: Tech 2010 tch...@gmail.com writes: xy=# reindex table xy_data; ERROR: concurrent insert in progress Should I drop and recreate indexes? I think that database is pretty well hosed. (What happened to it to cause all this, anyway?) A dump/initdb/reload is probably indicated at this point. reindex did not help. pg_dump crashes and select * into new_tables crashes as well. Any idea? Thanks. -- 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] incorrect restore from pg_dumpall
Yes, You were right, the problem was in the different libraries. I worked around by installing the postgis version 1.3.6, reloading the databases and subsequently uprading the postgis to 1.4.1. Thank You for Your help Tomas p.s.: what is really a mystery that also the mentioned table with no data were successfully reloaded, those tables did not contain spatial data ... :) On Fri, 2009-11-27 at 16:28 -0800, Paul Ramsey wrote: In order to restore a backup taken with pg_dumpall you'll want to ensure that the postgis installed in your new system is identical to the postgis in your old one. This is because the postgis function definitions will be looking for a particular postgis library name... the name of the library from your old database. You can hack around this, and have your cake and eat it too, to an extent, by symlinking the name of your old postgis to your new postgis library. P On Fri, Nov 27, 2009 at 4:11 PM, Tomas Lanczos lanc...@t-zones.sk wrote: Hello, I am trying to restore my databases stored by a pg_dumpall command in the Karmic Koala box. The restore command is the following: psql -f /media/disk/.../backup -U postgres I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. I recognized that the tables with spatial geometries were not restored but at the moment I am almost sure that it's caused by that the older version of postgis in the stored database. What is a kind of mystery for me that data in several tables were not restored, although the table definitions did (it means that I got several tables without data) althoug the data are there in the backup file (I checked physically). I really don't understand what's going on there, I did the same many times before, without any problem. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Date with time zone
Speaking of timestamps, I think it would be convenient to have a single-word alias for timestamp with time zone. This is the date type I use almost exclusively and its name is annoyingly big. On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considering that many jurisdictions don't switch daylight savings time at local midnight. How would you know which zone applied on a DST transition date? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? Because the SQL committee were smoking something strange that day. You won't find anybody around here who will defend the existence of TIME WITH TIME ZONE. We only put it in for minimal spec compliance. 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 -- 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] Date with time zone
timestamptz On Sat, Nov 28, 2009 at 7:25 PM, silly silly8...@gmail.com wrote: Speaking of timestamps, I think it would be convenient to have a single-word alias for timestamp with time zone. This is the date type I use almost exclusively and its name is annoyingly big. On Sat, Nov 28, 2009 at 6:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considering that many jurisdictions don't switch daylight savings time at local midnight. How would you know which zone applied on a DST transition date? On the other hand, I don't really see the reasons of this statement: Although the date type *cannot *have an associated time zone, the time type can. Why is this so? Because the SQL committee were smoking something strange that day. You won't find anybody around here who will defend the existence of TIME WITH TIME ZONE. We only put it in for minimal spec compliance. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- When fascism comes to America, it will be intolerance sold as diversity. -- 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] Where do you store key for encryption
David Wall wrote: In our open-esignforms project we use a layered approach for keys in which we have a boot key for the application that requires dual passwords which we then combine into a single password for PBE encryption of the boot key. We then have session keys that are encrypted with the boot key, and the session keys are used to encrypt one-up keys for encrypted blobs. In your case, you could encrypt your key using PBE assuming you have a way to provide the password to unlock it. This would allow you to protect the key with a password, which is the most basic way to go if you don't have a keystore to use. I covered this a little bit in my recent security presentation: http://momjian.us/main/presentations.html#securing -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.45.1 on mac - Plist problems
Hi all,
Using a MacBook Pro. Snow Leopard installed along with XCode
I have 8.4.1 compiled from tar.gz. It lives in
/usr/local/postgresql-8.4.1 which is owned by root. the data
subdirectory is owned by postgres. A link exists from
/usr/local/postgres to postgresql-8.4.1.
PGDATA is /usr/local/postgres/data
I can run okay with /usr/local/postgres/bin/postmaster -D ${PGDATA}
I have tried to copy/build/modify a plist to start up automatically.
The file below is named org.postgres.launchd.plist and it is in
/Library/LaunchDaemons. I have fashioned it after dBEnterprises'.
file:
?xml version=1.0 encoding=UTF-8?
!DOCTYPE plist PUBLIC -//Apple Computer//DTD PLIST 1.0//EN
http://www.apple.com/DTDs/PropertyList-1.0.dtd;
plist version=1.0
dict
keyDisabled/key
false/
keyLabel/key
stringorg.postgres.launchd/string
keyProgramArguments/key
array
string/usr/local/postgres-8.4.1/bin/postmaster/string
string-D/string
string/usr/local/postgresql-8.4.1/bin/string
/array
keyRunAtLoad/key
true/
keyUserName/key
stringpostgres/string
/dict
/plist
after saving, I
launchctl load /Library/LaunchDarmons/org.postgres.launchd.plist
then
launchctl list | grep postgres returns
2 org.postgres.launchd
but when I
launchctl start org.postgres.launchd
Nothing happens. ps ax |grep postmaster does not show
postmaster running.
Any ideas or assistance is greatfully appreciated.
Bob
--
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] Date with time zone
On Sat, Nov 28, 2009 at 4:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I see current criteria and all the SQL-standard compliance policy, but wouldn't it still make sense to be able to store a date reference, along with a time zone reference? Wouldn't it be useful, wouldn't it be elegant? It seems pretty ill-defined to me, considering that many jurisdictions don't switch daylight savings time at local midnight. How would you know which zone applied on a DST transition date? Yeah, I think the only reasonable way to define a date with a timezone would be as some kind of interval, starting at 00:00:00 and going until 23:59:59.9 (or 00:00:00 next day, whichever is more accurate. On spring forward / fall back days it would be 23 or 25 hours respectively. I'm not sure what you'd DO with it though. TIME WITH TIME ZONE. We only put it in for minimal spec compliance. Yeah, it's kinda twilight zonish to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Time zone 'GMT+8'
The target pgsql is compiled from 8.4rc1. 'GMT+8' can be found in installed binary file .../share/postgresql/timezone/Etc/GMT+8. This is the recorded script: -BEGIN record-- db1=# select now(); now --- 2009-11-29 14:44:37.322414+08 (1 row) db1=# set timezone to 'GMT+8'; SET db1=# select now(); now --- 2009-11-28 22:45:03.397545-08 (1 row) db1=# set timezone to GMT-8'; SET db1=# select now(); now --- 2009-11-29 14:45:39.160701+08 (1 row) db1=# set timezone to '0'; SET db1=# select now(); now --- 2009-11-29 06:45:54.347482+00 (1 row) -END record-- I thought time zone 'GMT+8' was '8' or UTC+8, and 'GMT-8' was '-8' or UTC-8. Does the original time zone settings shipped with source have special interpretation that is different from my understanding? Regards, CN -- 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] Time zone 'GMT+8'
From http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. On Sun, Nov 29, 2009 at 2:08 AM, seil...@so-net.net.tw wrote: The target pgsql is compiled from 8.4rc1. 'GMT+8' can be found in installed binary file .../share/postgresql/timezone/Etc/GMT+8. This is the recorded script: -BEGIN record-- db1=# select now(); now --- 2009-11-29 14:44:37.322414+08 (1 row) db1=# set timezone to 'GMT+8'; SET db1=# select now(); now --- 2009-11-28 22:45:03.397545-08 (1 row) db1=# set timezone to GMT-8'; SET db1=# select now(); now --- 2009-11-29 14:45:39.160701+08 (1 row) db1=# set timezone to '0'; SET db1=# select now(); now --- 2009-11-29 06:45:54.347482+00 (1 row) -END record-- I thought time zone 'GMT+8' was '8' or UTC+8, and 'GMT-8' was '-8' or UTC-8. Does the original time zone settings shipped with source have special interpretation that is different from my understanding? Regards, CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
