[GENERAL] libecpg versions and libecpg_compat
Hi list, I've been building ECPG (embedded SQL/C) programs on a system with Pg version 8.0 installed. When I tried to run them recently on version 8.4 I found that there was a libecpg library incompatibility: v8.0 uses libecpg.so.5, whereas 8.4 uses libecpg.so.6. Then I noticed libecpg_compat in the lib area. What is this used for? "compat" suggests compatibility between different versions? But Pg 8.0 has libecpg_compat.so.2, whereas Pg 8.4 has libecpg_compat.so.3. Is there some way of building with Pg v8.4 ECPG lib and running on a system with Pg v8.0 ECPG lib? or vice versa? and is libecpg_compat intended for that purpose? Thanks, Rob -- 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] Verify a record has a column in a plpgsql trigger
Mike Ginsburg wrote: > [...] > Thanks for the help! I'll look into the exceptions to see > how expensive they are. On a related note, I was just told > by our sysadmins that pg 8.4 might not be installed by the > time this needs to be rolled out, leaving me in a bind since > I have been using "EXECUTE ... USING" queries. A sample of > my trigger is below: > FOR colRow IN SELECT attname FROM pg_catalog.pg_attribute > WHERE attnum >> 0 AND attrelid = TG_RELID LOOP > EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO n USING NEW; > EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO o USING OLD; > IF n <> o THEN >q := 'INSERT INTO change_log (...) VALUES (...); >EXECUTE q; > END IF; >END LOOP; > Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING? Wouldn't it be *much* easier to just have /two/ trigger functions? Your "editor" columns probably don't pop up and disappear randomly. Tim -- 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] playr (or similar tool)?
There was a thread about this within the last month or two. Check the archives, but as I recall the consensus was basically that there are several things in early stages, but until they're done, tsung is as good as it gets today. :( On Feb 4, 2010, at 3:19 PM, Kevin Kempter wrote: > Hi All; > > I need a tool that will duplicate ALL messages from one db to another > (including begin, commit, etc). I think Playr does (did) this but the > myyearbook links from the past pg conference talks (the one from PG East 2009 > in particular) no longer work. > > Anyone know how to get Playr? > > If it duplicates ALL statements? > > other tools that will do this for me? > > > Thanks in advance > > -- > 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
[GENERAL] playr (or similar tool)?
Hi All; I need a tool that will duplicate ALL messages from one db to another (including begin, commit, etc). I think Playr does (did) this but the myyearbook links from the past pg conference talks (the one from PG East 2009 in particular) no longer work. Anyone know how to get Playr? If it duplicates ALL statements? other tools that will do this for me? Thanks in advance -- 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] Shall I apply normalization in the following case?
On Wed, 2010-02-03 at 23:56 -0200, Jorge Godoy wrote: > You can always normalize and not use an artificial key. > > You'd end up with: > > > measurement (normalization) > === > id | value | measurement_unit_id | measurement_type_id > -- > 1 0.23 mmwidth > 2 0.38 mmwidth > 2 0.72 mmwidth How is this different? It looks like you just added some FKs, which don't change the normal form. Regards, Jeff Davis -- 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] Shall I apply normalization in the following case?
On Wed, 2010-02-03 at 17:20 -0800, Yan Cheng Cheok wrote: > For example, for the following table, > > > measurement (without normalization) > === > id | value | measurement_unit | measurement_type > > 1 0.23 mmwidth > 2 0.38 mmwidth > 2 0.72 mmwidth That looks normalized to me. Can you describe the redundancy that I don't see? Do units of "mm" always imply "width" (I would think they might also be used for "length"). Regards, Jeff Davis -- 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] Re: PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
On Feb 4, 2010, at 3:47 PM, Magnus Hagander wrote: > 2010/2/4 Owen Hartnett : >> >> On Aug 9, 2009, at 3:10 PM, Magnus Hagander wrote: >> >>> On Sun, Aug 9, 2009 at 10:22, Abraham, Danny wrote: Does anybody have a proven way to regenerate this problem? SO that I can tell that the patch really does fix it? >>> >>> No. That's the reason it has taken so long to figure out how to fix it >>> (which we hopefully have done now). >> >> Has a consensus been arrived at? Is this patch rolled into the current >> system? > > The patch is included in the latest versions, yes. I don't recall > seeing any reports of the problem remaining after the patch, but it's > not 100% sure of course. Thanks, Magnus, I can see no reason why we shouldn't attempt to migrate to Server 2008 now. -Owen -- 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] Verify a record has a column in a plpgsql trigger
Merlin Moncure wrote: On Thu, Feb 4, 2010 at 11:41 AM, Mike Ginsburg wrote: I have a plpgsql function that serves as a change log for a few tables in my db (8.4.2). In most of the tables that I am logging, there is an "editor" column that stores the ID of the user who made the change, so as part of the function I set editor := NEW.editor; There are a few of the tables that don't store editor, in which case I am ok with inserting it into the log as NULL. The problem is I can't seem to come up with a conditional to see if NEW has a column named "editor". There's no way to do query now/old for columns directly in pl/pgsql. Some alternatives: 1) use begin/exception/end to try and set it, and catch the error. would likely be the best route but be aware that functions with exception handlers have a higher cost than those without 2) query system catalogs or information schema 3) build a cache (a list of tables that support editor in a table you query) If it was me, I'd do #3 if performance was critical, otherwise #1. merlin Thanks for the help! I'll look into the exceptions to see how expensive they are. On a related note, I was just told by our sysadmins that pg 8.4 might not be installed by the time this needs to be rolled out, leaving me in a bind since I have been using "EXECUTE ... USING" queries. A sample of my trigger is below: FOR colRow IN SELECT attname FROM pg_catalog.pg_attribute WHERE attnum > 0 AND attrelid = TG_RELID LOOP EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO n USING NEW; EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO o USING OLD; IF n <> o THEN q := 'INSERT INTO change_log (...) VALUES (...); EXECUTE q; END IF; END LOOP; Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING? Mike Ginsburg mginsb...@collaborativefusion.com
Re: [GENERAL] Re: PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
On Aug 9, 2009, at 3:10 PM, Magnus Hagander wrote: > On Sun, Aug 9, 2009 at 10:22, Abraham, Danny wrote: >> Does anybody have a proven way to regenerate this problem? >> >> SO that I can tell that the patch really does fix it? > > No. That's the reason it has taken so long to figure out how to fix it > (which we hopefully have done now). Has a consensus been arrived at? Is this patch rolled into the current system? I have a client wants to install W2K8 server release 2 and he's asking if there's any conflict. I can update him to the latest when he does. Thanks, -Owen > > > -- > 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 > -- 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] Re: PG fails on Windows Server 2008: could not reattach to shared memory ... : 487
2010/2/4 Owen Hartnett : > > On Aug 9, 2009, at 3:10 PM, Magnus Hagander wrote: > >> On Sun, Aug 9, 2009 at 10:22, Abraham, Danny wrote: >>> Does anybody have a proven way to regenerate this problem? >>> >>> SO that I can tell that the patch really does fix it? >> >> No. That's the reason it has taken so long to figure out how to fix it >> (which we hopefully have done now). > > Has a consensus been arrived at? Is this patch rolled into the current > system? The patch is included in the latest versions, yes. I don't recall seeing any reports of the problem remaining after the patch, but it's not 100% sure of course. -- 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] Literals in foreign key definitions
Alban Hertroys wrote: > [...] > Now the intent here is to restrict foreign keys referencing the base class to > unitclass records that describe a baseclass and to restrict foreign keys > referencing a derived class to unitclass records that do NOT describe a > baseclass. > Basically I'm trying to disallow derived classes to be derived of other > derived classes. > I can of course add a few triggers to force that constraint, but I think it > would be nice if the above syntax could be made to work. Or is this already > in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? If you want to avoid triggers, another, simpler approach is to have a otherwise superfluous column "is_baseclass" with a default "TRUE" and constraints "NOT NULL" and "CHECK(is_baseclass)" and then use a "normal" foreign key constraint. I usually find that easier to read as it's more familiar. Tim -- 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] Any Good Postgresql Courses Offered in the US Area?
On 02/04/2010 08:28 AM, Wang, Mary Y wrote: Hi PostgreSQL Community, Are there any good PostgreSQL courses offered in the US Westcoast area? Would be great if there is one in California. Company's budget is tight. However, if the course is really great (need recommendations from the community), travel any where in the US is OK too. I need to submit the request to my management ASAP. Thanks Please let me know. Mary Another option is to join a local Postgres User Group. See here for a list: http://pugs.postgresql.org/ Note: A group just started up in Seattle(http://www.seapug.org/) -- Adrian Klaver adrian.kla...@gmail.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] Verify a record has a column in a plpgsql trigger
On Thu, Feb 4, 2010 at 11:41 AM, Mike Ginsburg wrote: > I have a plpgsql function that serves as a change log for a few tables in my > db (8.4.2). In most of the tables that I am logging, there is an "editor" > column that stores the ID of the user who made the change, so as part of the > function I set > > editor := NEW.editor; > > There are a few of the tables that don't store editor, in which case I am ok > with inserting it into the log as NULL. The problem is I can't seem to come > up with a conditional to see if NEW has a column named "editor". There's no way to do query now/old for columns directly in pl/pgsql. Some alternatives: 1) use begin/exception/end to try and set it, and catch the error. would likely be the best route but be aware that functions with exception handlers have a higher cost than those without 2) query system catalogs or information schema 3) build a cache (a list of tables that support editor in a table you query) If it was me, I'd do #3 if performance was critical, otherwise #1. 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] How to set default database
On Thu, Feb 4, 2010 at 1:40 PM, Krzysztof Walkiewicz wrote: > Hello! > > I created database "krzysztof" for practice postgresql. After few days i > deleted that database and i created the next one "cwiczebna". Now when I > type "psql" i get strange info, something like: "CRITICAL: base "krzysztof" > don't exist". Now i need to type "psql cwiczebna" to log in to new > database. How to set the default database or connect automatically to > "cwiczebna" database when I type "psql" in terminal. I am using Debian Lenny > distro. > Setup the 'PGDATABASE' environment variable http://www.postgresql.org/docs/current/interactive/libpq-envars.html --Scott
Re: [GENERAL] How to set default database
On 02/04/2010 10:40 AM, Krzysztof Walkiewicz wrote: Hello! I created database "krzysztof" for practice postgresql. After few days i deleted that database and i created the next one "cwiczebna". Now when I type "psql" i get strange info, something like: "CRITICAL: base "krzysztof" don't exist". Now i need to type "psql cwiczebna" to log in to new database. How to set the default database or connect automatically to "cwiczebna" database when I type "psql" in terminal. I am using Debian Lenny distro. By default if you do not specify a database psql does -d 'your_username'. If you do not have a database with that name you will need to specify the database you want to connect to explicitly. -- Adrian Klaver adrian.kla...@gmail.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] Need Advice In Improving Database Disc Usage
On Tue, Feb 2, 2010 at 11:00 PM, Yan Cheng Cheok wrote: > (1) Is there any need for me to create idx_fk_measurement_type_id and > idx_fk_measurement_unit_id > > The above 2 are the index for measurement's foreign key. However, these 2 > foreign key are only used in JOIN statement, but never used in WHERE > statement. So, if I remove the 2 index, will I still achieve the same query > performance? For the following situation create table a ( a_id PRIMARY KEY, baz INTEGER ); create table b ( b_id PRIMARY KEY, a_id INTEGER REFERENCES a(a_id) ON DELETE CASCADE ); you only want to have an index on b.a_id IF you delete a_id from table a regularly. That is, when the reverse of the FK is tested during the delete, you want it to be fast to find and delete the referring rows as well (the CASCADE). If you never or rarely do such a delete, and can live with doing a sequence scan on table b for such deletes, then you don't need to waste space and time with the index. > > (2) How can I increase the compression ratio for my database? Whatever compression happens is automatic. Sound like you may need to investigate how often your indexes are used and perhaps prune some of them. That and be sure you run vacuum regularly to keep the tables from bloating too much. Have you done bulk deletes and re-loads of your DB? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to set default database
Hello! I created database "krzysztof" for practice postgresql. After few days i deleted that database and i created the next one "cwiczebna". Now when I type "psql" i get strange info, something like: "CRITICAL: base "krzysztof" don't exist". Now i need to type "psql cwiczebna" to log in to new database. How to set the default database or connect automatically to "cwiczebna" database when I type "psql" in terminal. I am using Debian Lenny distro. -- Krzysztof Walkiewicz --- ba...@op.pl -- 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] Performance Tuning - Any easy things that I can do ?
On Thu, Feb 4, 2010 at 11:41 AM, Gauthier, Dave wrote: > analyze? Does the empirical data the optimizer use develop good queries get > updated with/after a restore? Nope. -- 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] Performance Tuning - Any easy things that I can do ?
Gauthier, Dave escribió: > analyze? Does the empirical data the optimizer use develop good queries get > updated with/after a restore? Not automatically, you have to invoke it manually. (In recent releases, autovacuum would do it, but 7.1 didn't have it). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Any Good Postgresql Courses Offered in the US Area?
On Thu, Feb 4, 2010 at 9:28 AM, Wang, Mary Y wrote: > Hi PostgreSQL Community, > > Are there any good PostgreSQL courses offered in the US Westcoast area? > Would be great if there is one in California. > Company's budget is tight. However, if the course is really great (need > recommendations from the community), travel any where in the US is OK too. I > need to submit the request to my management ASAP. Take a look at the articles on Greg Smith's site. There are soem gems there. http://www.westnet.com/~gsmith/content/postgresql/ -- 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] Performance Tuning - Any easy things that I can do ?
analyze? Does the empirical data the optimizer use develop good queries get updated with/after a restore? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Thursday, February 04, 2010 1:32 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Performance Tuning - Any easy things that I can do ? On Thu, Feb 4, 2010 at 8:54 AM, Wang, Mary Y wrote: > Hi, > > I restored my database. However, I noticed performance is poor as compared > to before. > Are there some easy things that I can do to improve the performance (besides > rewriting the code)? > > My pgversion is 7.1.3 (I know, I know that I need to upgrade). Yeah, I ran pg as far back as 6.5 or so, but I can't remember enough to really hope to help. I'm guessing that upgrading will be less painful than trying to performance tune such an old release. Tuning 7.1 is kinda like performance tuning a 1916 Apperson Jackrabbit... http://en.wikipedia.org/wiki/Apperson -- 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] Performance Tuning - Any easy things that I can do ?
On 02/04/2010 10:32 AM, Scott Marlowe wrote: > On Thu, Feb 4, 2010 at 8:54 AM, Wang, Mary Y wrote: >> Hi, >> >> I restored my database. However, I noticed performance is poor as compared >> to before. >> Are there some easy things that I can do to improve the performance (besides >> rewriting the code)? >> >> My pgversion is 7.1.3 (I know, I know that I need to upgrade). > > Yeah, I ran pg as far back as 6.5 or so, but I can't remember enough > to really hope to help. I'm guessing that upgrading will be less > painful than trying to performance tune such an old release. One thing you might try if you haven't already done so is run VACUUM ANALYZE. Joe signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Performance Tuning - Any easy things that I can do ?
On Thu, Feb 4, 2010 at 8:54 AM, Wang, Mary Y wrote: > Hi, > > I restored my database. However, I noticed performance is poor as compared > to before. > Are there some easy things that I can do to improve the performance (besides > rewriting the code)? > > My pgversion is 7.1.3 (I know, I know that I need to upgrade). Yeah, I ran pg as far back as 6.5 or so, but I can't remember enough to really hope to help. I'm guessing that upgrading will be less painful than trying to performance tune such an old release. Tuning 7.1 is kinda like performance tuning a 1916 Apperson Jackrabbit... http://en.wikipedia.org/wiki/Apperson -- 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] Any Good Postgresql Courses Offered in the US Area?
Wang, Mary Y wrote: Are there any good PostgreSQL courses offered in the US Westcoast area? Would be great if there is one in California. There is a list of upcoming training events from various members of the PostgreSQL community available at http://www.postgresql.org/about/eventarchive ; there are some in California in the near future. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Any Good Postgresql Courses Offered in the US Area?
Mary, EnterpriseDB (my employer) runs both public and custom training classes: http://www.enterprisedb.com/tservices/training/schedule.do If you're interested, let me know and I'll get you talking to someone asap. --Scott Mead scott.m...@enterprisedb.com On Thu, Feb 4, 2010 at 12:06 PM, Wang, Mary Y wrote: > Hi Rich, > > I'm a database admin who needs to install and maintain postgres for a > application. I'm also an application developer. As for now, I need to have > more knowledge about being a Postgresql database admin. I'm also very > interested in the database performance tuning area. > > I do have about three different PostgreSQL books that I use frequently > anytime I encountered a problem or need help. Of course, this community has > been great by providing lots of help!! > > Mary > > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard > Sent: Thursday, February 04, 2010 8:52 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Any Good Postgresql Courses Offered in the US Area? > > On Thu, 4 Feb 2010, Wang, Mary Y wrote: > > > Are there any good PostgreSQL courses offered in the US Westcoast area? > > Would be great if there is one in California. Company's budget is tight. > > However, if the course is really great (need recommendations from the > > community), travel any where in the US is OK too. I need to submit > > the request to my management ASAP. > > Mary, > > It might help you to get relevant answers if you explain what sort of > training you want or need. Are you a database administrator who needs to > install and maintain postgres with multiple appliations and users? Are you > an application developer who needs to learn SQL? Are you a technical person > who supports postgres users? > > Rich > > -- > 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] Any Good Postgresql Courses Offered in the US Area?
Hi Rich, I'm a database admin who needs to install and maintain postgres for a application. I'm also an application developer. As for now, I need to have more knowledge about being a Postgresql database admin. I'm also very interested in the database performance tuning area. I do have about three different PostgreSQL books that I use frequently anytime I encountered a problem or need help. Of course, this community has been great by providing lots of help!! Mary -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard Sent: Thursday, February 04, 2010 8:52 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Any Good Postgresql Courses Offered in the US Area? On Thu, 4 Feb 2010, Wang, Mary Y wrote: > Are there any good PostgreSQL courses offered in the US Westcoast area? > Would be great if there is one in California. Company's budget is tight. > However, if the course is really great (need recommendations from the > community), travel any where in the US is OK too. I need to submit > the request to my management ASAP. Mary, It might help you to get relevant answers if you explain what sort of training you want or need. Are you a database administrator who needs to install and maintain postgres with multiple appliations and users? Are you an application developer who needs to learn SQL? Are you a technical person who supports postgres users? Rich -- 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] Any Good Postgresql Courses Offered in the US Area?
On Thu, 4 Feb 2010, Wang, Mary Y wrote: Are there any good PostgreSQL courses offered in the US Westcoast area? Would be great if there is one in California. Company's budget is tight. However, if the course is really great (need recommendations from the community), travel any where in the US is OK too. I need to submit the request to my management ASAP. Mary, It might help you to get relevant answers if you explain what sort of training you want or need. Are you a database administrator who needs to install and maintain postgres with multiple appliations and users? Are you an application developer who needs to learn SQL? Are you a technical person who supports postgres users? Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Verify a record has a column in a plpgsql trigger
I have a plpgsql function that serves as a change log for a few tables in my db (8.4.2). In most of the tables that I am logging, there is an "editor" column that stores the ID of the user who made the change, so as part of the function I set editor := NEW.editor; There are a few of the tables that don't store editor, in which case I am ok with inserting it into the log as NULL. The problem is I can't seem to come up with a conditional to see if NEW has a column named "editor". Any help is greatly appreciated. Thanks. Mike Ginsburg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Any Good Postgresql Courses Offered in the US Area?
Hi PostgreSQL Community, Are there any good PostgreSQL courses offered in the US Westcoast area? Would be great if there is one in California. Company's budget is tight. However, if the course is really great (need recommendations from the community), travel any where in the US is OK too. I need to submit the request to my management ASAP. Thanks Please let me know. Mary -- 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 wal shipping from 8.33 to 8.42.
On Thu, Feb 4, 2010 at 8:58 AM, Thom Brown wrote: > On 4 February 2010 15:54, Scott Marlowe wrote: >> On Thu, Feb 4, 2010 at 7:54 AM, Chris Barnes >> wrote: >>> We are trying to minimize our downtime in production to upgrade from 8.33 to >>> 8.42. >>> >>> What we would like to be able to do is this: >>> Upgrade the slave we are currently shipping to, to version 8.4.2. Continue >>> to pitr to this server. And switch over and then upgrade the master. >>> >>> My question is, will pitr wal logs ship and be processed from a machine >>> running 8.33 to 8.42. >> >> Nope. But you can use slony to upgrade. >> > > Or pg_migrator? http://pgfoundry.org/projects/pg-migrator For minimal downtime, slony would win, but for reducing effort, pg_migrator wins. -- 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] Performance Tuning - Any easy things that I can do ?
On 4 February 2010 15:54, Wang, Mary Y wrote: > Hi, > > I restored my database. However, I noticed performance is poor as compared > to before. > Are there some easy things that I can do to improve the performance (besides > rewriting the code)? > > My pgversion is 7.1.3 (I know, I know that I need to upgrade). > Need to upgrade? That's a bit of an understatement. If you're able to do so, I'd strongly recommend it as PostgreSQL has dramatically improved in every area since then. I think you'll need a PostgreSQL veteran to help you out there. All I can think of is you take a look at the earliest available documentation (7.2 from what I can tell) and try looking into those settings: http://www.postgresql.org/docs/7.2/static/runtime-config.html Regards Thom -- 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 wal shipping from 8.33 to 8.42.
On 4 February 2010 15:54, Scott Marlowe wrote: > On Thu, Feb 4, 2010 at 7:54 AM, Chris Barnes > wrote: >> We are trying to minimize our downtime in production to upgrade from 8.33 to >> 8.42. >> >> What we would like to be able to do is this: >> Upgrade the slave we are currently shipping to, to version 8.4.2. Continue >> to pitr to this server. And switch over and then upgrade the master. >> >> My question is, will pitr wal logs ship and be processed from a machine >> running 8.33 to 8.42. > > Nope. But you can use slony to upgrade. > Or pg_migrator? http://pgfoundry.org/projects/pg-migrator Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance Tuning - Any easy things that I can do ?
Hi, I restored my database. However, I noticed performance is poor as compared to before. Are there some easy things that I can do to improve the performance (besides rewriting the code)? My pgversion is 7.1.3 (I know, I know that I need to upgrade). Thanks Mary -- 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 wal shipping from 8.33 to 8.42.
On Thu, Feb 4, 2010 at 7:54 AM, Chris Barnes wrote: > We are trying to minimize our downtime in production to upgrade from 8.33 to > 8.42. > > What we would like to be able to do is this: > Upgrade the slave we are currently shipping to, to version 8.4.2. Continue > to pitr to this server. And switch over and then upgrade the master. > > My question is, will pitr wal logs ship and be processed from a machine > running 8.33 to 8.42. Nope. But you can use slony 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] Shall I apply normalization in the following case?
Sim Zacks wrote: 2) querying on an int is quicker then querying on a string, so if you query on the values without the join you will have better performance. The point of Jorge's approach is that it allows a query without a join. With the OP's normalization, using integer keys, a join was required, obviating any advantage to the integer key. The assertion that a query involving integer keys is always faster than one with string keys is unsafe at best. The first rule of performance optimization is that optimizations don't. You have to test and measure to find out when and if they do. Misuse of surrogate keys to obtain optimization is a prime example. I've seen situations in the field several times when such abuse forces extra joins in queries and an increase in the number of indexes to maintain. Without measurement under representative workloads, especially concurrent activity, it's impossible to know whether the cost outweighs the benefit. -- Lew -- 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 wal shipping from 8.33 to 8.42.
In response to Chris Barnes : > We are trying to minimize our downtime in production to upgrade from 8.33 to > 8.42. > > What we would like to be able to do is this: > Upgrade the slave we are currently shipping to, to version 8.4.2. Continue to > pitr to this server. And switch over and then upgrade the master. > > My question is, will pitr wal logs ship and be processed from a machine > running > 8.33 to 8.42. No. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Versions RSS page is missing version(s)
On Mon, Feb 1, 2010 at 15:33, Greg Sabino Mullane wrote: > >>> I'm not sure how useful that is. Surely while we encourage people to run >>> a recent major version, we also want to encourage people who will not >>> or cannot upgrade to at least be running the latest revision of a branch, >>> no matter how old it is? > >> We don't support 7.3. Not even if you run the latest version. > > No, but I imagine we still would encourage people to run the latest revision > of it. Come this time next year, I hope that we'll tell people on 7.4.2 to Do we really, officially, care? > upgrade to 9.0 as soon as possible, but to upgrade to 7.4.27 *immaediately*. We should be, and afaik are, telling people to upgrade away from 7.4 immidiately *already*. >>> How about a compromise? We add a new field to that XML so we can state >>> that it is unsupported, but leave it in there. That way, programs such >>> as check_postgres can not only distinguish between old but valid versions >>> and invalid versions (e.g. "7.typo.oops") but can act in a more intelligent >>> way for unsupported versions. Heck, maybe an estimated end-of-life date >>> field for all versions as well? > >> How do you add that field in a backwards compatible way? Meaning that >> people or tools relying on it should *not* see 7.3 or 6.1 or whatever. >> And it needs to be done within the RSS spec (which does allow custom >> namespaces though, so that may not be a problem) > > Well I don't know what people are reading the XML, so let's discuss tools. > Do you have a use case in mind where adding old versions would break > something? I don't know what tools people use. That's the point of using RSS, people can use whatever tool they want. > Has this always been advertised as a list of *supported* versions, or as a > list > of the *latest* revisions? I've always assumed the latter was more important > that the former. The *meaning* has always been supported versions, but if you read the contents of the feed it does say latest. Does anybody know if it's actually supported to have multiple channels in one RSS feed? If it is, we could add a second channel with unsupported versions, still listing the latest version of them. -- 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] Literals in foreign key definitions
Alban Hertroys writes: > FOREIGN KEY (base, true) REFERENCES unitclass (name, is_baseclass), > FOREIGN KEY (derived, false) REFERENCES unitclass(name, is_baseclass) > I can of course add a few triggers to force that constraint, but I think it > would be nice if the above syntax could be made to work. Or is this already > in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? Yes. FK constraints have to be columns vs. columns --- otherwise they can't be represented in the information_schema views. 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] Postgres wal shipping from 8.33 to 8.42.
We are trying to minimize our downtime in production to upgrade from 8.33 to 8.42. What we would like to be able to do is this: Upgrade the slave we are currently shipping to, to version 8.4.2. Continue to pitr to this server. And switch over and then upgrade the master. My question is, will pitr wal logs ship and be processed from a machine running 8.33 to 8.42. Any thoughts on this would be appreciated. Thanks, Chris _
Re: [GENERAL] confusting results from pg_database_size
On Thursday 04 February 2010 1:52:36 am AutoVHC Dev Team wrote:
> As part of an testing an archive solution I've updated and deleted 3 or 4
> million rows in different tables. I wanted to see how much this shrunk the
> database size by running, so I ran a VACUUM FULL FREEZE ANALYZE on both
> databases - this took a long time, which is ok considering the number of
> rows I updated/deleted
>
> To get the db size I ran the following:
>
> SELECT pg_size_pretty(pg_database_size('deleted_rows_db')),
> pg_size_pretty(pg_database_size('original_db'));
>
> Obviously you'd expect the database that is minus 3 million rows to be
> smaller, however it was 300Mb larger. (This is a test environment where no
> one else has access to the database, both databases were restored from the
> same backup. Running a count on the tables shows the rows have definitely
> been deleted. - This is the second time I've done this as I considered that
> I must have done something wrong the first time).
>
> Does anyone know why the database with fewer rows is larger? how I can find
> the real size?, or do something to the database to lose this bloat? (It is
> as if the vacuum didn't work - though it appeared to).
>
> Thanks in advance.
>
> -Dan Shoubridge
Do you have indexes on the tables? If so see here:
http://www.postgresql.org/docs/8.4/interactive/routine-vacuuming.html
"Another disadvantage of VACUUM FULL is that while it reduces table size, it
does not reduce index size proportionally; in fact it can make indexes larger.
Generally, therefore, administrators should strive to use standard VACUUM and
avoid VACUUM FULL. '
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Literals in foreign key definitions
Hello all, I was trying something on my 8.3 server that's a bit controversial, and I wasn't surprised it didn't work. I think it would be nice if it were possible though... The case at hand is that I have a table: CREATE TABLE unitclass ( name TEXT NOT NULL PRIMARY KEY, is_baseclass BOOLEAN NOT NULL DEFAULT true ); That has a many-to-many relationship with: CREATE TABLE unitclass_relation ( basetext NOT NULL, exponentint NOT NULL, derived text NOT NULL, FOREIGN KEY (base, true) REFERENCES unitclass (name, is_baseclass), FOREIGN KEY (derived, false) REFERENCES unitclass(name, is_baseclass) ); This does give an error (not unexpected): ERROR: syntax error at or near "true" LINE 8: FOREIGN KEY (base, true) REFERENCES unitclass (name... Now the intent here is to restrict foreign keys referencing the base class to unitclass records that describe a baseclass and to restrict foreign keys referencing a derived class to unitclass records that do NOT describe a baseclass. Basically I'm trying to disallow derived classes to be derived of other derived classes. I can of course add a few triggers to force that constraint, but I think it would be nice if the above syntax could be made to work. Or is this already in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? Cheers, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b6ad4b910441146016476! -- 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 columns with replication/cluster
Sean, take a look at UUID type. It might suit you better than serial. Changing the increment also works, but puts a lot of restrictions on you (such as planning to prevent collision, having to change the increment on several nodes when adding a new node, etc.). Regards, -- Jorge Godoy On Wed, Feb 3, 2010 at 23:21, Sean Hsien wrote: > Dear all, > > At work we're considering using postgres in a cluster. But I am > wondering what implications does multi-master write replication have > on auto-incrementing serial columns? > E.g. Do we need to fiddle with the increment amount depending on the > number of nodes we have? > > -- > Thanks and regards, > Sean > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Setting permissions to access schema and language
Hi, I know how to set the permissions for tables. Could anyone tell me how to restrict people accessing and creating schemas? Also, is it possible to restrict language permissions? Suppose I want only few users should use C language and for rest of users it should be missing. How can I restrict permissions on languages? Thanks in advanced, Jignesh
[GENERAL] confusting results from pg_database_size
As part of an testing an archive solution I've updated and deleted 3 or 4
million rows in different tables. I wanted to see how much this shrunk the
database size by running, so I ran a VACUUM FULL FREEZE ANALYZE on both
databases - this took a long time, which is ok considering the number of
rows I updated/deleted
To get the db size I ran the following:
SELECT pg_size_pretty(pg_database_size('deleted_rows_db')),
pg_size_pretty(pg_database_size('original_db'));
Obviously you'd expect the database that is minus 3 million rows to be
smaller, however it was 300Mb larger. (This is a test environment where no
one else has access to the database, both databases were restored from the
same backup. Running a count on the tables shows the rows have definitely
been deleted. - This is the second time I've done this as I considered that
I must have done something wrong the first time).
Does anyone know why the database with fewer rows is larger? how I can find
the real size?, or do something to the database to lose this bloat? (It is
as if the vacuum didn't work - though it appeared to).
Thanks in advance.
-Dan Shoubridge
--
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] using new Hstore in PG8.4
W dniu 2010-02-04 10:46, Marek Lewczuk pisze: Hello, there are a lot of improvements in hstore, that is planned for PG8.5 - as far I remember it compiles fine with PG8.4 so is it save to use it with PG8.4 ? Is it SAFE not "is it save"... ML -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using new Hstore in PG8.4
Hello, there are a lot of improvements in hstore, that is planned for PG8.5 - as far I remember it compiles fine with PG8.4 so is it save to use it with PG8.4 ? Thanks. ML -- Pozdrawiam Marek Lewczuk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] serial columns with replication/cluster
Dear all, At work we're considering using postgres in a cluster. But I am wondering what implications does multi-master write replication have on auto-incrementing serial columns? E.g. Do we need to fiddle with the increment amount depending on the number of nodes we have? -- Thanks and regards, Sean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using new Hstore in PG8.4
Hello, there are a lot of improvements in hstore, that is planned for PG8.5 - as far I remember it compiles fine with PG8.4 so is it save to use it with PG8.4 ? Thanks. ML -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
