[GENERAL] Re: Partial Indexes Not being Used [WAS]Re: Partial_indexes (Immutable?)
On Tue, 2008-08-26 at 00:58 -0400, Tom Lane wrote:
Ow Mun Heng [EMAIL PROTECTED] writes:
On Mon, 2008-08-25 at 10:18 -0400, Tom Lane wrote:
I suppose code_id is varchar or some such?
Yep
After a few more investigation on the usefulness of the partial indexes,
I found that, it really isn't all that useful, perhaps some experts can
shed some light.
I poked at that example a bit more earlier today, and found that 8.3
has a problem that's interfering with optimizing x IN ('y','z') type
clauses when x is varchar. If you don't mind building a local copy,
see if this patch helps you any:
http://archives.postgresql.org/pgsql-committers/2008-08/msg00254.php
I wouldn't mind if I have a box with a compiler installed or if i have a
NON-Production Box at all.
:-(
--
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] Easy upgrade on Cpanel *without* downtime
On 8/26/08, Scott Marlowe [EMAIL PROTECTED] wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. We just upgraded from an 8.1 server to an 8.3 server via slony, and it went smooth as silk. db downtime was measured in seconds. Thanks for this Scott. Sounds promising. But where can I find the instructions to install Slony, then install new PG 8.3.3, then start it with similar CONF settings and stuff, then setup the master and slave (which I am not familiar with), and then switch master and slave when everything is working? To others who keep telling us that PG is complex and if you want it to be less so then contribute -- well, sorry I am not that technical. If the intended target audience of PG is only super-techsavvy folk who can write C++ patches for every little functionality they need, then perhaps I chose the wrong DB? I doubt it. It would be really nice if the PG official community can have some simple instructions to make a seamless upgrade, if no simpler patches exist. At the very least the instructions will help us plentiful folk who do NOT use PG in the exalted enterprise setting, but to run busy websites. This is how MySQL became big too, by being convenient and reliable (until recently anyway), but I see no point in that discussion. Anyhow, it would be really nice to have simple instructions. Searching on Google for words like Slony Postgresql upgrade or install slony with postgresql 8.3 returns stuff that makes a lot of presumptions! I have a CentOS 4 with Cpanel/WHM running. PG is in the usual place: whereis pgsql pgsql: /usr/lib/pgsql /usr/include/pgsql /usr/share/pgsq Now how can I install Slony so that it install PGSQL and allows me to continue working with Apache/PHP for my website? I am reading this -- http://slony.info/documentation/installation.html -- but while it textually mentions the stuff in the writeup, I don't see full instructions to install Slony, then new PGSQL, then switching, and so on. So many thanks for any help anyone can provide! Or point me to some resource that exists but is hiding from Google. -- 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] Easy upgrade on Cpanel *without* downtime
On 8/26/08, Phoenix Kiula [EMAIL PROTECTED] wrote: On 8/26/08, Scott Marlowe [EMAIL PROTECTED] wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. We just upgraded from an 8.1 server to an 8.3 server via slony, and it went smooth as silk. db downtime was measured in seconds. Thanks for this Scott. Sounds promising. But where can I find the instructions to install Slony, then install new PG 8.3.3, then start it with similar CONF settings and stuff, then setup the master and slave (which I am not familiar with), and then switch master and slave when everything is working? To others who keep telling us that PG is complex and if you want it to be less so then contribute -- well, sorry I am not that technical. If the intended target audience of PG is only super-techsavvy folk who can write C++ patches for every little functionality they need, then perhaps I chose the wrong DB? I doubt it. It would be really nice if the PG official community can have some simple instructions to make a seamless upgrade, if no simpler patches exist. At the very least the instructions will help us plentiful folk who do NOT use PG in the exalted enterprise setting, but to run busy websites. This is how MySQL became big too, by being convenient and reliable (until recently anyway), but I see no point in that discussion. Anyhow, it would be really nice to have simple instructions. Searching on Google for words like Slony Postgresql upgrade or install slony with postgresql 8.3 returns stuff that makes a lot of presumptions! I have a CentOS 4 with Cpanel/WHM running. PG is in the usual place: whereis pgsql pgsql: /usr/lib/pgsql /usr/include/pgsql /usr/share/pgsq Now how can I install Slony so that it install PGSQL and allows me to continue working with Apache/PHP for my website? I am reading this -- http://slony.info/documentation/installation.html -- but while it textually mentions the stuff in the writeup, I don't see full instructions to install Slony, then new PGSQL, then switching, and so on. So many thanks for any help anyone can provide! Or point me to some resource that exists but is hiding from Google. See, this is where I get confused. I want to upgrade from 8.2.3 to 8.3.3. The recommendation is to try Slony. So I download Slony and try to configure it. The configure command gives me this: checking for correct version of PostgreSQL... error configure: error: Your version of PostgreSQL (8.2) is lower than the required 8.3. Slony-I needs functionality included in a newer version. Well, if Slony needs a newer version, then how can it be used to upgrade?! -- 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] playing with catalog tables limits? dangers? was: seq bug 2073 and time machine
On Mon, 25 Aug 2008 21:57:52 +0200 Joris Dobbelsteen [EMAIL PROTECTED] wrote: The 'regular interface', that you will find in the documentation, ensures that the assumptions remain valid. It will not allow changes that cause these assumption to become invalid. (As a side note: you might see conditions where the assumption are violated when using the regular interface: these will be called bugs). Most of the assumptions are undocumented, but if they happen to be documented, there is little reason to actually assume they will remain valid over different versions (even between e.g. 8.3 and 8.3.1, though that will probably happen, but for other reasons). They are not intended to be used by regular users, rather by the system itself. Stability wouldn't be a problem. I was just thinking to directly accessing the catalog for refactoring. eg. I need to temporarily disable a set of constraint or I need to globally change the name of a column or get rid of serial in a set of columns or change the signature of a function... But stability have an effect on documentation... If I had to mass change the db (schema, functions, constraints..) at the moment I can think about these options: - use a script that parse my SQL code and write another SQL script to achieve the change - backup and use sed - use a script that read the catalog and generate the needed SQL Sometimes the safety net could be a burden. [snip] However there are cases where strange and unexpected things happen and editing the catalog directly can get the database server back into a proper/consistent state. You seem to have encountered such a situation. I think such situations are rare... and I can ask the list ;) I was thinking if learning how the catalog works could open some refactoring path I would generally consider too expensive. pgadmin3 let you retrieve the schema, the definition of functions, dependencies. psql \d doesn't list the dependencies (yeah it does somehow) and it is scriptable... somehow... I was looking for something that could work like grep and sed (actually some languages have more complex refactoring tools) on DB definition, without having to use grep and sed on a plain text backup. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] Easy upgrade on Cpanel *without* downtime
Phoenix Kiula wrote: On 8/26/08, Phoenix Kiula [EMAIL PROTECTED] wrote: On 8/26/08, Scott Marlowe [EMAIL PROTECTED] wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. We just upgraded from an 8.1 server to an 8.3 server via slony, and it went smooth as silk. db downtime was measured in seconds. Thanks for this Scott. Sounds promising. But where can I find the instructions to install Slony, then install new PG 8.3.3, then start it with similar CONF settings and stuff, then setup the master and slave (which I am not familiar with), and then switch master and slave when everything is working? To others who keep telling us that PG is complex and if you want it to be less so then contribute -- well, sorry I am not that technical. If the intended target audience of PG is only super-techsavvy folk who can write C++ patches for every little functionality they need, then perhaps I chose the wrong DB? I doubt it. It would be really nice if the PG official community can have some simple instructions to make a seamless upgrade, if no simpler patches exist. At the very least the instructions will help us plentiful folk who do NOT use PG in the exalted enterprise setting, but to run busy websites. This is how MySQL became big too, by being convenient and reliable (until recently anyway), but I see no point in that discussion. Anyhow, it would be really nice to have simple instructions. Searching on Google for words like Slony Postgresql upgrade or install slony with postgresql 8.3 returns stuff that makes a lot of presumptions! I have a CentOS 4 with Cpanel/WHM running. PG is in the usual place: whereis pgsql pgsql: /usr/lib/pgsql /usr/include/pgsql /usr/share/pgsq Now how can I install Slony so that it install PGSQL and allows me to continue working with Apache/PHP for my website? I am reading this -- http://slony.info/documentation/installation.html -- but while it textually mentions the stuff in the writeup, I don't see full instructions to install Slony, then new PGSQL, then switching, and so on. So many thanks for any help anyone can provide! Or point me to some resource that exists but is hiding from Google. See, this is where I get confused. I want to upgrade from 8.2.3 to 8.3.3. The recommendation is to try Slony. So I download Slony and try to configure it. The configure command gives me this: checking for correct version of PostgreSQL... error configure: error: Your version of PostgreSQL (8.2) is lower than the required 8.3. Slony-I needs functionality included in a newer version. Well, if Slony needs a newer version, then how can it be used to upgrade?! It looks like you have downloaded Slony 2.0.0RC1, which has in it's release notes It drops all support for databases prior to Postgres version 8.3. . It's also just a release candidate, not an actual release. You need to download Slony 1.2.14, which supports both 8.2 and 8.3. You'll find it under Quick downloads. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] web form development ide/api..
Hi there, i'm looking at building a series of web forms for maintaining data stored in postgis.. and am wondering if there is some form of python/php/java development environment (free or commercial) similar to oracle's application express (aka HTML DB) that handles the generic form style functionality such as listing, filtering, sorting, editing etc. I suppose to make the form development more like the MS Access experience. If anyone knows of such a thing i'd greatly appreciate some advice.. otherwise all that will need to be written which seems inefficient. cheers, -ivan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Triggers et clefs primaires
Bonjour,
J'ai un trigger (AFTER FOR EACH ROW) sur une table qui à chaque fois
qu'il y a un enregistrement sur cette même table, exécute une fonction
pgplsql qui éxécute elle-même une fonction PL/sh qui exécute un script
PHP.
Dans ce script PHP, je créer 10 enregistrements dans une autre tables où
il y a un champ qui fait référence à la table sur laquelle il y a le
trigger.
Exemple d'application:
INSERT INTO table1 (champ2, champ3) VALUES ('salut', 'samuel');
(la table1 as un champ1 (alias id) qui est un serial en clef primaire)
- La fonction pgplsql (appelée par le trigger) récupère l'ID de
l'enregistrement (NEW.id), le donne à la fonction pl/sh qui le donne au
script PHP.
J'enregistre des données dans la table2, avec un champ qui fait
référence à l'id de la table1. Des données avec comme id de table1, l'ID
qui vient juste d'être enregistré...
ERREUR de clef étrangère... :(
Note: Les deux tables sont dans deux schémas différents, avec deux
utilisateurs différents. Cependant, aucun problème de droit a priori
(GRANT USAGE ON SCHEMA... GRANT REFERENCES ON TABLES...)
Avez-vous une idée ?
Merci à vous,
Cordialement, Samuel.
--
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] SERIAL datatype
Mark Roberts írta: On Mon, 2008-08-25 at 11:02 -0600, Scott Marlowe wrote: Well, of course a 64 bit int is gonna be bigger than a 32 bit, but with alignment issues and on 64 bit hardware, I'm guessing the difference isn't exactly twice as slow / twice as much storage. And it's way faster than a GUID which was what I think started this thread. ... The integer version is 599752704 bytes, and the bigint version is 673120256 bytes (a ~12% size increase). When joining the table to itself (keys = 1 date, 5 (big)ints, no indexes), the bigint version performs a join to itself with an average of 44.1 sec, and the integer version in 29.6 sec (a 48% performance hit). While granted that it's not twice as big and twice as slow, I think it's a fairly valid reason to want to stay within (small)int ranges. Sometimes the initial performance hit on insert would really be worth the continuing space/performance savings down the road. The development version of PostgreSQL (to-be 8.4) was modified in a way so on 64-bit hardware 64-bit types (bigint, date, timestamp, etc.) are compile-time configurable to be passed as value instead of as reference. This way, most of the performance hit disappears because there is no malloc() overhead in passing bigints back and forth. Of course, the on-disk size difference will be the same. Of course, this wasn't very scientific and the benchmarks aren't very thorough (for instance I assumed that bigserial is implemented as a bigint), but it should remain a valid point. Of course, it probably has no bearing on the OP's problem. So my advice to the OP: have you considered not keying such a volatile table on a serial value? -Mark -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Triggers et clefs primaires
Samuel ROZE a écrit :
[...]
J'ai un trigger (AFTER FOR EACH ROW) sur une table qui à chaque fois
qu'il y a un enregistrement sur cette même table, exécute une fonction
pgplsql qui éxécute elle-même une fonction PL/sh qui exécute un script
PHP.
Dans ce script PHP, je créer 10 enregistrements dans une autre tables où
il y a un champ qui fait référence à la table sur laquelle il y a le
trigger.
Exemple d'application:
INSERT INTO table1 (champ2, champ3) VALUES ('salut', 'samuel');
(la table1 as un champ1 (alias id) qui est un serial en clef primaire)
- La fonction pgplsql (appelée par le trigger) récupère l'ID de
l'enregistrement (NEW.id), le donne à la fonction pl/sh qui le donne au
script PHP.
J'enregistre des données dans la table2, avec un champ qui fait
référence à l'id de la table1. Des données avec comme id de table1, l'ID
qui vient juste d'être enregistré...
ERREUR de clef étrangère... :(
The row on table1 is inserted at the end of the trigger.
Note: Les deux tables sont dans deux schémas différents, avec deux
utilisateurs différents. Cependant, aucun problème de droit a priori
(GRANT USAGE ON SCHEMA... GRANT REFERENCES ON TABLES...)
Avez-vous une idée ?
Merci à vous,
Cordialement, Samuel.
This is an english channel. If you want a french one, try
pgsql-fr-generale (http://archives.postgresql.org/pgsql-fr-generale/).
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.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] Triggers et clefs primaires
Le mardi 26 août 2008 à 11:01 +0200, Guillaume Lelarge a écrit :
Samuel ROZE a écrit :
[...]
J'ai un trigger (AFTER FOR EACH ROW) sur une table qui à chaque fois
qu'il y a un enregistrement sur cette même table, exécute une fonction
pgplsql qui éxécute elle-même une fonction PL/sh qui exécute un script
PHP.
Dans ce script PHP, je créer 10 enregistrements dans une autre tables où
il y a un champ qui fait référence à la table sur laquelle il y a le
trigger.
Exemple d'application:
INSERT INTO table1 (champ2, champ3) VALUES ('salut', 'samuel');
(la table1 as un champ1 (alias id) qui est un serial en clef primaire)
- La fonction pgplsql (appelée par le trigger) récupère l'ID de
l'enregistrement (NEW.id), le donne à la fonction pl/sh qui le donne au
script PHP.
J'enregistre des données dans la table2, avec un champ qui fait
référence à l'id de la table1. Des données avec comme id de table1, l'ID
qui vient juste d'être enregistré...
ERREUR de clef étrangère... :(
The row on table1 is inserted at the end of the trigger.
Yes but... my trigger is a AFTER INSERT trigger... So, data might be
insered... no?
Note: Les deux tables sont dans deux schémas différents, avec deux
utilisateurs différents. Cependant, aucun problème de droit a priori
(GRANT USAGE ON SCHEMA... GRANT REFERENCES ON TABLES...)
Avez-vous une idée ?
Merci à vous,
Cordialement, Samuel.
This is an english channel. If you want a french one, try
pgsql-fr-generale (http://archives.postgresql.org/pgsql-fr-generale/).
Okay :)
--
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] Easy upgrade on Cpanel *without* downtime
You need to download Slony 1.2.14, which supports both 8.2 and 8.3. You'll find it under Quick downloads. Ok done. Slony is installed. Now what? How should I install a new database which is 8.3.3? I was also told that the postgresql.conf settings across 8.2 and 8.3 are different so I cannot (or should not) use the same old postgresql.conf for the new database install. Is this true? Where can I find the instructions? I read this lengthy document -- http://www.postgresql.org/docs/8.3/static/release-8-3.html -- but it is not clear which variables I need to change. 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] Easy upgrade on Cpanel *without* downtime
On 2008-08-26 13:39, Phoenix Kiula wrote: Ok done. Slony is installed. Now what? http://www.slony.info/documentation/versionupgrade.html I think nobody would guide you step by step. Either read documentation and do it yourself or hire an expert: http://www.postgresql.org/support/professional_support I was also told that the postgresql.conf settings across 8.2 and 8.3 are different so I cannot (or should not) use the same old postgresql.conf for the new database install. Find out what changes were made to your old postgresql.conf (compare it to the default) and make the same changes to new posgresql.conf. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] Easy upgrade on Cpanel *without* downtime
On 8/26/08, Tomasz Ostrowski [EMAIL PROTECTED] wrote: I think nobody would guide you step by step. Either read documentation and do it yourself or hire an expert: Thanks. I suppose that spirit is quite evident in the documentation. Why make it easy or easily understandable when you can win fanatical fans by requiring them to invest months of their time! Cheers. -- 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] Easy upgrade on Cpanel *without* downtime
On Tue, Aug 26, 2008 at 5:02 AM, Tomasz Ostrowski [EMAIL PROTECTED] wrote: http://www.slony.info/documentation/versionupgrade.html I think nobody would guide you step by step. Well they may, then is nothing wrong with asking especially when breaking new ground. Since my databases are currently pretty small, a dump and reload is not a problem. However, I am following this tread with keen interest for future reference. Perhaps others are also? Either read documentation and do it yourself or hire an expert: http://www.postgresql.org/support/professional_support Reading the documentation is always good advice. Ofcourse, I think that DBA/SA wants and should be able to perform all tasks associated with maintaining a database. However, I very much agree that buying a support aggreement for mission critical applications is an important safety net to have. Find out what changes were made to your old postgresql.conf (compare it to the default) and make the same changes to new posgresql.conf. I very much agree. There wasn't that much that changed between 8.2 and 8.3. Just look for the uncommented postgresql.conf setting in 8.2 and then compare with 8.3. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Easy upgrade on Cpanel *without* downtime
On Mon, Aug 25, 2008 at 08:36:34PM -0600, Scott Marlowe wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. Pleased as I am to hear accounts of Slony being used successfully to solve the upgrade problem -- it was one of our design goals in the early discussions at Afilias -- I have to confess that if you find Postgres administration arcane, Slony administration is going to seem very like interpreting runes. I have heard that Londiste (in the skytools package) can also do this, and it is intended to be easier to administer. I have no personal experience trying it. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Easy upgrade on Cpanel *without* downtime
On Tue, Aug 26, 2008 at 5:31 AM, Phoenix Kiula [EMAIL PROTECTED] wrote: I suppose that spirit is quite evident in the documentation. All kidding aside, the problem that you are having IS recognized as a weakness with PostgreSQL. This is why some are already working on solving the problem of in place upgrades. Some time in the future perhaps 8.4 this will be a mute point. In the meantime, there are other workarounds (which of course can be complex) to mitigate this problem. Why make it easy or easily understandable when you can win fanatical fans by requiring them to invest months of their time! I sense your frustration. But it is important to remember that the PostgreSQL project is largely supported by volunteers. I am sure that you agree that no one intentionally designs any give task to be more complicated than necessary. As a side note there are many useful how to articles on the web. Many of these HOWTOs were created by users (like yourself) that have faced a difficult problem with no apparent solution. However, after struggling with the problem and then finding a solution, they document it for all to benefit from. Perhaps some good with come from overcoming this challenge. So in the mean time, the best advice is not to rush time upgrade but take your time to insure that you do it right the first time. Some useful article that you should consider are: http://www.depesz.com/index.php/2008/05/05/error-operator-does-not-exist-integer-text-how-to-fix-it/ http://people.planetpostgresql.org/greg/index.php?/archives/136-Upgrading-to-8.3-MediaWiki-lessons-learned.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Easy upgrade on Cpanel *without* downtime
On Tue, Aug 26, 2008 at 5:39 AM, Phoenix Kiula [EMAIL PROTECTED] wrote: You need to download Slony 1.2.14, which supports both 8.2 and 8.3. You'll find it under Quick downloads. Ok done. Slony is installed. Now what? How should I install a new database which is 8.3.3? Which OS are you running? How was 8.2 installed? I was also told that the postgresql.conf settings across 8.2 and 8.3 are different so I cannot (or should not) use the same old postgresql.conf for the new database install. Is this true? Where can I find the instructions? I read this lengthy document -- http://www.postgresql.org/docs/8.3/static/release-8-3.html -- but it is not clear which variables I need to change. Well, look at the entries that aren't commented out in your 8.2 and make the same changes in your 8.3 postgresql.conf I just want to point out that the reason there aren't a lot of step by step guides on this is that it's a complex subject. There are many different OSes and several ways you could have installed postgresql, and step by step instructions for RHEL4 may not be the same as for Ubuntu 8.04 etc... -- 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] Easy upgrade on Cpanel *without* downtime
On Tue, Aug 26, 2008 at 6:31 AM, Phoenix Kiula [EMAIL PROTECTED] wrote: On 8/26/08, Tomasz Ostrowski [EMAIL PROTECTED] wrote: I think nobody would guide you step by step. Either read documentation and do it yourself or hire an expert: Thanks. I suppose that spirit is quite evident in the documentation. Why make it easy or easily understandable when you can win fanatical fans by requiring them to invest months of their time! Christ, remind to do you no favors. I'm sure if I had written a step by step guide and one part of it didn't work you'd be right back here threatening to sue me or something. Look, it's a comlex subject, and you need to have a pretty good clue what you're doing so if something goes wrong you're not making a big mistake and losing all your data. And you can stop with the histrionics. It took me less than one weekend to install and test slony for migration / backup at work. Remember, for every problem, there is a simple, elegant, easy solution. Which is wrong. -- 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] Easy upgrade on Cpanel *without* downtime
[EMAIL PROTECTED] (Phoenix Kiula) writes: See, this is where I get confused. I want to upgrade from 8.2.3 to 8.3.3. The recommendation is to try Slony. So I download Slony and try to configure it. The configure command gives me this: checking for correct version of PostgreSQL... error configure: error: Your version of PostgreSQL (8.2) is lower than the required 8.3. Slony-I needs functionality included in a newer version. Well, if Slony needs a newer version, then how can it be used to upgrade?! I expect that what you downloaded was a pre-release candidate for version 2.0, which does indeed eschew older versions (for some good reasons - you can't support *all* versions of *everything*, *forever*). With version 1.2.14, the latest *official* release, versions of PostgreSQL as far back as 7.4 are certainly supported. -- output = reverse(moc.enworbbc @ enworbbc) http://cbbrowne.com/info/lsf.html Dickson's Gardening Rule: When weeding, the best way to make sure you are removing a weed and not a valuable plant is to pull on it. If it comes out of the ground easily, it is a valuable plant. -- 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] Easy upgrade on Cpanel *without* downtime
On Tue, Aug 26, 2008 at 12:10 AM, Phoenix Kiula [EMAIL PROTECTED] wrote: On 8/26/08, Scott Marlowe [EMAIL PROTECTED] wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. We just upgraded from an 8.1 server to an 8.3 server via slony, and it went smooth as silk. db downtime was measured in seconds. Thanks for this Scott. Sounds promising. But where can I find the instructions to install Slony, then install new PG 8.3.3, then start it with similar CONF settings and stuff, then setup the master and slave (which I am not familiar with), and then switch master and slave when everything is working? Problem here is that while each piece of the puzzle probably has it's own useful instructions, no one has put them all together into one document, and the reason for that is choice. With a choice of versions to be upgrading from and to, options of whether or not you need to replicate and entire database or not, versions of slony, and ways to install each version of pgsql on each of those different OSes, it's hard to make documents that don't start with This thing was only test with pgsql v7.4.7 to v8.0.3 with slony 1.2.09 on RHEL 3. Suddenly you've got a guide that's 3 years old and most of the info in it is now not the right thing to do if you're upgrading from 8.1.4 to 8.3.3 with slony 1.2.14 and could cause you problems. I used to write docs like that for my last company, and within a year or two they're useless. To others who keep telling us that PG is complex and if you want it to be less so then contribute -- well, sorry I am not that technical. Then you should find someone in your neighborhood who's been busy learning pgsql on an intranet and wishing he could build a production system on it and hire him. Or something like that. Or become a little bit of him. Note that you can also contribute by whipping out your checkbook and hiring one of the hackers who regularly work on pgsql to implement something. It's how Slony got started. Thanks Afilias and folks. If the intended target audience of PG is only super-techsavvy folk who can write C++ patches for every little functionality they need, then perhaps I chose the wrong DB? I doubt it. No, I don't think that's entirely the case. If you need a corporate office db to handle a few thousand users a dozen at a time, with weekend down time and a small to medium database I'm pretty sure my mom could keep the machine happy. But if you're running a 24/7 no down time outside of scheduled maintenance db with high load then you're not going to find something that just bolts in and works. In some ways PostgreSQL is actually pretty close. But you're gonna have to at least learn how to plug it into Nagios or something. If you then need a 365.25 system with no down days, no scheduled maintenance, you've reached the point where the air is thin and the cost of doing in business gets high. Entire applications were written in the past to migrate living databases. Slony is one of the slickest replication tricks I've ever seen when it comes to the ability to upgrade in place. You do it once every 1 to 4 years, so it takes planning and testing. Build a test environment, using Xen if you don't have enough physical machines and need virtual ones, and make sure you understand what's happening and catch your simple mistakes. Then backup production and try it there. But testing is key. It would be really nice if the PG official community can have some simple instructions to make a seamless upgrade, if no simpler patches exist. You're as official as me or anybody else. At the very least the instructions will help us plentiful folk who do NOT use PG in the exalted enterprise setting, but to run busy websites. Well, if you can't be down for a saturday morning from hrs to 0800hrs then you are indeed in the exalted enterprise setting. This is how MySQL became big too, by being convenient and reliable (until recently anyway), but I see no point in that discussion. Some could argue they're a victim of their own success. They were stuck supporting old version for far too long, and spent a lot of energy trying to make things easy, but many of those things, like replication, aren't actually guaranteed to work. Add in the system catalogs are stored in myisam table and your whole replicated innodb based system could get corrupted because it was replicating DDL when the power went out. Slony is built, like the rest of pgsql, to survive the power going out. Anyhow, it would be really nice to have simple instructions. Searching on Google for words like Slony Postgresql upgrade or install slony with postgresql 8.3 returns stuff that makes a lot of presumptions! Well, throw at us what you got and what you got questions about. I have a CentOS 4 with Cpanel/WHM running. PG is in the usual place: I'm running Centos 5. Using yum / rpm to update packages, admin from the cmd line. Here's a major
Re: [GENERAL] Easy upgrade on Cpanel *without* downtime
[EMAIL PROTECTED] (Phoenix Kiula) writes: On 8/26/08, Tomasz Ostrowski [EMAIL PROTECTED] wrote: I think nobody would guide you step by step. Either read documentation and do it yourself or hire an expert: Thanks. I suppose that spirit is quite evident in the documentation. Why make it easy or easily understandable when you can win fanatical fans by requiring them to invest months of their time! I think that the level of detail that we *DID* give in the page on version upgrades was not unsuitable. http://www.slony.info/documentation/versionupgrade.html It doesn't try to explain *EVERYTHING*; it tries to give enough detail to help you construct whatever procedure you need to construct. The trouble with it all is that: a) Flexibility comes at a cost, namely the cost of there being some not-inconsiderable complexity. Unfortunately, it's not easy, and wishing it were so is just wishful thinking. I wish it *could* be easy, but it simply isn't, and I don't see anything to change that *would* make it easy. b) People are using different sorts of environments that can vary quite a bit. This means that if we write up a super-detailed procedure, that procedure will ONLY be valid for the specific environment it was written for. For instance, I could go into gratuitous detail about how to do PostgreSQL and Slony-I compiles and installs on our AIX systems, unfortunately, that detail would NOT be useful to you when you're using something else. If, instead, I went into deep detail as to how to deal with a 2-node cluster on Ubuntu Linux, in conjunction with CPanel, this would: a) Not be relevant to our staff that aren't using that flavour of Linux, and b) Have portions that are totally irrelevant to anyone not using CPanel. Super-detailed procedures aren't worth having, sorry. -- (format nil [EMAIL PROTECTED] cbbrowne linuxfinances.info) http://linuxfinances.info/info/advocacy.html Be warned that typing ``killall name'' may not have the desired effect on non-Linux systems, especially when done by a privileged user. -- From the killall manual page -- 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] Easy upgrade on Cpanel *without* downtime
On Aug 26, 2008, at 10:22 AM, Scott Marlowe wrote: On Tue, Aug 26, 2008 at 12:10 AM, Phoenix Kiula [EMAIL PROTECTED] wrote: On 8/26/08, Scott Marlowe [EMAIL PROTECTED] wrote: Slony replication lets postgresql accomplish this, which is really quite impressive. We just upgraded from an 8.1 server to an 8.3 server via slony, and it went smooth as silk. db downtime was measured in seconds. Thanks for this Scott. Sounds promising. But where can I find the instructions to install Slony, then install new PG 8.3.3, then start it with similar CONF settings and stuff, then setup the master and slave (which I am not familiar with), and then switch master and slave when everything is working? Problem here is that while each piece of the puzzle probably has it's own useful instructions, no one has put them all together into one document, and the reason for that is choice. With a choice of Phoenix, you might want to start with some hands on postgres training to quickly get up to speed on slony. I've used http://www.otg- nc.com/ . They are very good. 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] web form development ide/api..
On Tue, Aug 26, 2008 at 2:41 AM, Ivan Price [EMAIL PROTECTED] wrote: If anyone knows of such a thing i'd greatly appreciate some advice.. Gedafe - http://isg.ee.ethz.ch/tools/gedafe/index.en.html DaDaBIK - http://www.dadabik.org/ Good luck. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Do I have a corrupted database?
I fear I have a corrupted database, and I'm not sure what to do. Environment: Windows Server 2003 8GB RAM Dual processor, quad core 2.6Ghz Postgres 8.2.3 (The IT dept wants to upgrade to 8.2.9, but they are asking me what to do about this corrupt database before they proceed) The database files and logs are stored on a SAN drive 2008-08-23 06:57:06 FATAL: could not create sigchld waiter thread: error code 1816 *** ack! 13 hour hole! What the...? 2008-08-23 20:00:27 ERROR: xlog flush request E0/293CF278 is not satisfied --- flushed only to E0/21B1B7F0 2008-08-23 20:00:27 CONTEXT: writing block 94218 of relation 16712/16713/16725 2008-08-23 20:04:36 DETAIL: Multiple failures --- write error may be permanent. 2008-08-23 20:04:36 ERROR: xlog flush request E0/4FC5BEB8 is not satisfied --- flushed only to E0/21B9E270 2008-08-23 20:04:36 CONTEXT: writing block 81033 of relation 16712/16713/16725 2008-08-23 20:04:36 STATEMENT: BEGIN TRANSACTION; ... just a normal SQL stored proc... 2008-08-23 20:04:36 DETAIL: Multiple failures --- write error may be permanent. 2008-08-23 20:04:36 ERROR: xlog flush request E0/314D8248 is not satisfied --- flushed only to E0/21B9E358 2008-08-23 20:04:36 CONTEXT: writing block 371418 of relation 16712/16713/16719 2008-08-23 20:04:36 STATEMENT: BEGIN TRANSACTION;... just a normal SQL stored proc... repeats for quite a while. A few days later, after a restart, we are seeing these showing up quite often: 2008-08-26 11:59:42 FATAL: the database system is starting up 2008-08-26 11:59:42 FATAL: the database system is starting up 2008-08-26 11:59:43 FATAL: the database system is starting up 2008-08-26 11:59:43 FATAL: the database system is starting up 2008-08-26 11:59:43 FATAL: the database system is starting up 2008-08-26 11:59:43 LOG: database system is ready 2008-08-26 11:59:55 PANIC: right sibling's left-link doesn't match 2008-08-26 11:59:55 STATEMENT: BEGIN TRANSACTION;INSERT INTO ...SQL scrubbed... This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2008-08-26 11:59:55 LOG: server process (PID 2228) exited with exit code 3 2008-08-26 11:59:55 LOG: terminating any other active server processes 2008-08-26 11:59:55 LOG: all server processes terminated; reinitializing 2008-08-26 11:59:55 LOG: database system was interrupted at 2008-08-26 11:59:43 Pacific Daylight Time 2008-08-26 11:59:55 LOG: checkpoint record is at E2/F88B6C0 2008-08-26 11:59:55 LOG: redo record is at E2/F88B6C0; undo record is at 0/0; shutdown TRUE 2008-08-26 11:59:55 LOG: next transaction ID: 0/396816257; next OID: 58100 2008-08-26 11:59:55 LOG: next MultiXactId: 3; next MultiXactOffset: 5 2008-08-26 11:59:55 LOG: database system was not properly shut down; automatic recovery in progress 2008-08-26 11:59:55 LOG: redo starts at E2/F88B710 2008-08-26 11:59:55 LOG: record with zero length at E2/F984928 2008-08-26 11:59:55 LOG: redo done at E2/F9848F8 2008-08-26 11:59:55 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 FATAL: the database system is starting up 2008-08-26 11:59:56 LOG: database system is ready That section is repeating over and over. Oddly enough, the system actually seems to be running mostly. I need to do some diagnostics of our app to see what is going on at that layer and what is and isn't working. I found an article online with a similar problem, but no resolution: http://www.mydatabasesupport.com/forums/postgresql/399079-general-failing-recover-after-panic-shutdown.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQL Question - Recursion
Hi - Was wondering if anyone could help / had some thoughts. I am building a model for a client, and right now doing customer attrition modeling. Basically, the number of customers in this period is equal to: Beg # Customers + customers added this period - attrition Ending # Customers Obviously getting the beginning number of customers and # of added has been easy via SQL. Also, the above is fairly straightforward in a spreadsheet program like Excel. But I can't seem to come to grips with how to model the attrition line item in SQL, as it's based on last months ending balance ... which in turn is based on beg + add - attr = end for the previous period, and so on. So it seems to be a recursive function as far as I can tell ... any idea how to model this via SQL? I can get running totals for the # of adds with a correlated subquery, but I can't seem to figure out how to build the attrition into that running total. All help is greatly appreciated! Thanks, Kevin -- 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] SQL Question - Recursion
ktr73 wrote: Hi - Was wondering if anyone could help / had some thoughts. I am building a model for a client, and right now doing customer attrition modeling. Basically, the number of customers in this period is equal to: Beg # Customers + customers added this period - attrition Ending # Customers Obviously getting the beginning number of customers and # of added has been easy via SQL. Also, the above is fairly straightforward in a spreadsheet program like Excel. But I can't seem to come to grips with how to model the attrition line item in SQL, as it's based on last months ending balance ... which in turn is based on beg + add - attr = end for the previous period, and so on. So it seems to be a recursive function as far as I can tell ... any idea how to model this via SQL? I can get running totals for the # of adds with a correlated subquery, but I can't seem to figure out how to build the attrition into that running total. Don't understand why you need recursion. Seems like attrition can be calculated. You say you can get the beginning number of customers. The ending number of customers for month N = beginning number of customers for month N+1. So, if you have the beginning, ending and # customers added, then attrition = ending - beginning - #added. -- Guy Rouillier -- 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] [ADMIN] PITR - base backup question
Hi Richard,
This means a file systems backup. eg.
tar -cvpf data_bakup.tar /var/lib/pgsql/data
Here's a script I use to automate this process. It may be helpful to
customize for yourself.
#!/bin/bash
#
# PostgreSQL Weekly Backup
#
DATE=$(date +%G%m%d)
MAILLOG=/backup/weekly_$DATE.log
WALARCH=/pgbackup
DESTINATION=X.X.X.X::rsyncshare/
EMAILDEST=
touch $MAILLOG
echo -e ::: $DATE Pallas1 Weekly WAL Backup :::
$MAILLOG
psql -U postgres -c select pg_start_backup ('$DATE');
cd /var/lib/pgsql
tar -cvpzf pgbackup_$DATE.tar.gz data/ $MAILLOG 21
psql -U postgres -c select pg_stop_backup ();
find $WALARCH -perm 600 -mmin +360 -exec rm {} \; $MAILLOG 21
find $WALARCH/data_dir_backup -mtime +5 -exec rm {} \; $MAILLOG 21
ls -la $WALARCH $MAILLOG 21
mv /var/lib/pgsql/pgbackup_$DATE.tar.gz /$WALARCH/data_dir_backup/.
rsync -a -v -v --progress --stats --delete /$WALARCH $DESTINATION
$MAILLOG 21
/bin/cat $MAILLOG | mail -s Weekly WAL Backup - Successful $EMAILDEST
rm $MAILLOG
On Tue, 26 Aug 2008 15:53:33 -0700, Richard Broersma
[EMAIL PROTECTED] wrote:
From the following link:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP
Step 3 says to perform the back up.
Does this mean a File System Backup of the Data directory?
OR
Does this mean performing a pg_dumpall and backing up the dump file?
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
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] PITR - base backup question
On Tue, Aug 26, 2008 at 6:53 PM, Richard Broersma [EMAIL PROTECTED] wrote: From the following link: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP Step 3 says to perform the back up. Does this mean a File System Backup of the Data directory? OR Does this mean performing a pg_dumpall and backing up the dump file? File system backup. You tar up the database folder except pg_xlog (and maybe pg_log). You then expand it on the other side (making sure to set up pg_xlog properly). If you ever want to mess around with log shipping I strongly suggest you go through the motions of setting up a warm standby vi the pg_standby utility and practice popping the standby out of recovery. It will give you a good understanding of the process. 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] [ADMIN] PITR - base backup question
On Tue, Aug 26, 2008 at 5:19 PM, [EMAIL PROTECTED] wrote: This means a file systems backup. eg. tar -cvpf data_bakup.tar /var/lib/pgsql/data Thanks also for the script. I will take a close look. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] PITR - base backup question
On Tue, Aug 26, 2008 at 5:38 PM, Merlin Moncure [EMAIL PROTECTED] wrote: If you ever want to mess around with log shipping I strongly suggest you go through the motions of setting up a warm standby vi the pg_standby utility and practice popping the standby out of recovery. Thanks for the reply! I want to be certain that I understand the point you are making: Is setting up a warm standby server good pratice for gaining experience in PITR or do you mean that PITR is a good starting point for setting up a warm standby server? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Do I have a corrupted database?
William Garrison wrote: I fear I have a corrupted database, and I'm not sure what to do. First, make sure you have a recent backup. If your backups rotate, stop the rotation so that all currently available historical copies of the database are preserved from now on - just in case you need them. Now, if possible dump your database with pg_dump. Restore the dump to a test database instance and make sure that it all goes OK. Once that's done, so you know you have a decent recovery point to work from in case you make a mistake during your recovery efforts. After that I don't have all that much to offer, especially as you're using an operating system I don't have much experience with Pg on and you're using an (unspecified) SAN. Normally I'd ask if you'd verified your RAID array / tested your disks. In this case, I'm wondering if there's any chance there was a service interruption on the SAN that might've caused some sort of intermittent or partial writes. 2008-08-23 20:00:27 ERROR: xlog flush request E0/293CF278 is not satisfied --- flushed only to E0/21B1B7F0 2008-08-23 20:00:27 CONTEXT: writing block 94218 of relation 16712/16713/16725 2008-08-23 20:04:36 DETAIL: Multiple failures --- write error may be permanent. Yeah, I'm really wondering about the SAN and SAN connection. What sort of SAN is it? How is the host connected? Does it have any sort of logging and monitoring that might let you see if there was a problem around the time Pg was complaining? Have you checked the Windows error logs? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgre connect on Postgre
Hi All How I can connect a postgre database on another postgre database, and manipulate the datas on both database? Thanks!!
Re: [GENERAL] Postgre connect on Postgre
On Aug 26, 2008, at 7:10 PM, Anderson dos Santos Donda wrote: How I can connect a postgre database on another postgre database, and manipulate the datas on both database? There is a module in contrib just for such a purpose: http://www.postgresql.org/docs/8.3/interactive/dblink.html -- 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] Postgre connect on Postgre
Thanks man!! I'll study this module!! On Tue, Aug 26, 2008 at 11:19 PM, Christophe [EMAIL PROTECTED] wrote: On Aug 26, 2008, at 7:10 PM, Anderson dos Santos Donda wrote: How I can connect a postgre database on another postgre database, and manipulate the datas on both database? There is a module in contrib just for such a purpose: http://www.postgresql.org/docs/8.3/interactive/dblink.html -- 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 math - Bug or expected behavior?
I traced a bug in our application down to this basic operation: set timezone to 'US/Eastern'; select '11/02/2008'::timestamptz, '12:10'::time, '11/02/2008'::timestamptz + '12:10'::time; I have a date and a time stored separately and I want to combine them, and use them in some timezone-aware calculations. When I add the time 12:10 to the date 11/2/08, I expect the timestamp 11/2/08 12:10 but instead, I get 11/2/08 11:10. It's probably not coincidence that daylight saving time rolls back one hour on the morning of 11/2. Still, I would have expected the above behavior when adding an interval to a timestamp, but not a time. Is the time being cast to an interval before the add? Is there a better way to combine a date with a time and get a timestamptz ? (the values are stored in the database, and are not literals as in my example) -- 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 math - Bug or expected behavior?
Adam Rich [EMAIL PROTECTED] writes: I traced a bug in our application down to this basic operation: set timezone to 'US/Eastern'; select '11/02/2008'::timestamptz, '12:10'::time, '11/02/2008'::timestamptz + '12:10'::time; I have a date and a time stored separately and I want to combine them, and use them in some timezone-aware calculations. When I add the time 12:10 to the date 11/2/08, I expect the timestamp 11/2/08 12:10 but instead, I get 11/2/08 11:10. I don't see why that surprises you in the least. '11/02/2008'::timestamptz means 11/02/2008 00:00 local time, and (given US DST rules) 11/2/08 11:10 is what the time will be 12 hours and 10 minutes later. If you want non-DST-aware timekeeping, use timestamp without time zone. Still, I would have expected the above behavior when adding an interval to a timestamp, but not a time. How could a time work differently from an interval in this context? 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
