Re: [GENERAL] Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
On 24/07/10 00:00, Merlin Moncure wrote: I generally agree with your statements, but there is one correction to make: advisory locks are not released at end of transaction. Argh. Good point. Every other kind of lock is, but advisory locks are only released when the connection is closed or the lock is explicitly released. Thanks for the correction. -- 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] Efficiently obtaining (any) one record per group.
Hi all, I have a large table that contains redundancies as per one field. I am looking for a way to identify (or extract) a non redundant set of rows ( _any_ one record per group) from this table and for each record of this distinct set of rows, I would like to capture it's other fields. Below is a simplified example. In this example I would like to base groups on the value of field reading. CREATE TABLE foo (id INTEGER ,reading INTEGER ,entry_date TIMESTAMP ,source TEXT ,primary key(id) ); INSERT INTO foo(1,55,'2010-04-01 06:31:13','A'); INSERT INTO foo(2,55,'2010-04-01 06:31:20','X'); INSERT INTO foo(3,45,'2010-04-01 06:38:02','P'); INSERT INTO foo(6,55,'2010-04-01 06:21:44','B'); INSERT INTO foo(4,34,'2010-04-01 06:51:24','K'); INSERT INTO foo(8,61,'2010-04-01 06:22:03','A'); INSERT INTO foo(9,34,'2010-04-01 06:48:07','C'); Desired output (any record selected based on reading field). id,reading,entry_date,source 1,55,'2010-04-01 06:31:13','A' 3,45,'2010-04-01 06:38:02','P' 9,34,'2010-04-01 06:48:07','C' 8,61,'2010-04-01 06:22:03','A' I am worried that using min() and group by to generate a relation which is then used in a join may be slow. SELECT a.* FROM foo a JOIN ( SELECT min(a.id)AS id_min FROM foo a GROUP BY a.reading )b ON a.id=b.id_min ; How is the performance of rank() (window function) in general? Allan. -- 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] Efficiently obtaining (any) one record per group.
On Sat, Jul 24, 2010 at 12:56 AM, Allan Kamau kamaual...@gmail.com wrote: Hi all, I have a large table that contains redundancies as per one field. I am looking for a way to identify (or extract) a non redundant set of rows ( _any_ one record per group) from this table and for each record of this distinct set of rows, I would like to capture it's other fields. Would Select distinct on ... work for you? http://www.postgresql.org/docs/8.4/static/sql-select.html search the page for distinct on. -- 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] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
On 23 Jul 2010, at 20:39, Samuel Gilbert wrote: Hello, I have encountered a problem with inserts failing because of permissions issues when the table in which I try to insert has foreign key constraints to tables for which UPDATE has been revoked. The script bellow show how to reproduce the problem with a bare-bones test case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not even the latest revision of the 8.2 line, but it's what I have to work with :( ) I Googled the error message and a couple of meaningful keywords, searched the documentation and the mailing list archives without success. It's not a solution to your problem, but one observation I made in your test script: CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1'; CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT LOGIN; \c test afsugil You create a new user, but you still connect with the user who created the database. REVOKE UPDATE ON station FROM afsugil; And then you revoke rights from that user instead of from the test user. Effectively you're not using the 'test' user at all in your script. Was that intended? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c4abba6286216566810360! -- 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] Efficiently obtaining (any) one record per group.
On Sat, Jul 24, 2010 at 10:38 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Sat, Jul 24, 2010 at 12:56 AM, Allan Kamau kamaual...@gmail.com wrote: Hi all, I have a large table that contains redundancies as per one field. I am looking for a way to identify (or extract) a non redundant set of rows ( _any_ one record per group) from this table and for each record of this distinct set of rows, I would like to capture it's other fields. Would Select distinct on ... work for you? http://www.postgresql.org/docs/8.4/static/sql-select.html search the page for distinct on. Thank you Scott, you have made my day, this is exactly what I was looking for. Allan. -- 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] Prefix LIKE search and indexes issue.
On 23 Jul 2010, at 23:22, Marcelo de Moraes Serpa wrote: The following query works: SELECT * FROM users WHERE 'com.app.mycompany' LIKE reversed_domain || % However, it does sequential search, meaning it doesn't use any index. The database may choose to use a seqscan for several reasons, not necessarily related to how you write your query. Is it a problem in your case? An EXPLAIN ANALYSE would give us more insight. I would expect the planner to pick an index scan if there's sufficient data in that table, as a suffix-search like that suits a btree index just fine. What I would like to know is, how could I make it use an index? I've If you really have to, for testing purposes you can temporarily disable sequential scans (SET enable_seqscan TO 'off') to see if the plan resulting from that is indeed more efficient. If it is, that probably means your database statistics aren't up to date ([VACUUM ]ANALYSE) or the statistics target for that specific column is too small (ALTER TABLE users ALTER COLUMN reversed_domain SET STATISTICS ). done some research and asked around #postgres but things are still not clear to me. Some good souls hinted me at the prefix extension, but how would I use it? Is there any other simpler / extension-free way to solve this issue? I'm not familiar with said extension, but I think that one's aimed at LIKE searches where the search term _starts_ with a wildcard instead of _ending_ with one. That's a situation where a btree index is in trouble. 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,4c4abfcd286214416410229! -- 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] pg_dump, shemas, backup strategy
On 24 Jul 2010, at 24:20, Michael A. Peters wrote: I've been using MySQL for years. I switched (er, mostly) to PostgreSQL recently because I need to use PostGIS. It is all working now for the most part, and PostGIS is absolutely wonderful. Welcome, I hope you like it here :) I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the 8.1.21 is the important part. Correct. It's not a very recent version (we're at 8.4.1 now), but at least it's up to date regarding bug and security fixes - it's not 8.1.2 or something, you'd be amazed with what versions people show up here sometimes :P. In writing my backup cron job, I ran into a small problem. It seems that my version of pg_dump does not accept the -T option for excluding tables. There are a couple tables that never need to be included in the backup (IE php session data). Since I prefer not to upgrade pgsql at this time, I was wondering if this is where schemas might help? IE can I put those few tables into a different schema and then tell pg_dump to only dump the public schema? Schema is kind of a new concept to me. Schema's in Postgres are similar to different databases in MySQL. They allow you to organise your tables in groups of tables belonging to similar functionality, for example. They have their own permissions too, which is nice if you need to restrict certain users to certain functionality. And of course you can access tables cross-schema, if you aren't denied the permissions. In your case, you could move those troublesome tables into their own schema and adjust the search_path accordingly for the user your PHP application uses to connect to the DB. For my code, I use the php pear::MDB2 wrapper (which made moving from MySQL to PostgreSQL much easier, just had to fix some non standard SQL I had). If I move stuff out of the public schema, am I going to have tell MDB2 how to find which schema it is in? I guess that may be better suited for php list, but hopefully someone knows. There are several approaches to that actually: You can do it from PHP by executing SET search_path TO '...' after you connect to the database (or when you first need tables from that schema, but that seems to overcomplicate matters). You can ALTER the DATABASE to set the search_path to what you need. You can ALTER the ROLE to set the search_path for a group of users or a single user. Any of those options will work, pick which suits your needs best ;) When everything was MySQL - I ran sphyder in its own database so that a bug in sphyder code could not be exploited to hack my main database. However, I'm wondering if that is an area where schema would be better. IE create a schema called sphyder and only give the sphyder user permission to select from the sphyder schema. Is that what the concept of schemas is for? You could move Sphyder's tables into a separate schema too, but... if you disallow the accompanying role (let's say 'sphyder') access to the public schema, then it can't read various system tables either. That can cause issues with looking up FK constraints and the like. Mind that I've never been in a situation where I needed to disallow some roles to access to the public schema, I'm not 100% sure about this - a simple test case is easy to create though. I'd probably just put most (or all) of my main database in a schema other than 'public' so that the sphyder role can still access the system tables it needs (and it won't be able to change those if that role is set up with sufficiently restrictive permissions). As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is pretty good too. It's built into the main database since version 8.3, not in your version. For 8.1 there is an extension with largely the same functionality, in case you're interested. I'm not sure how easy that would be to upgrade to the builtin version once you get to 8.3 or newer though... Thanks for helping out a n00b. You're welcome, we've all been there. 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,4c4ac73d286218533513805! -- 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] prepared statements
On 24 Jul 2010, at 1:32, Scott Frankel wrote: Works! The bug in my example was not passing the INSERT statement an explicit list of column names, as per any non-prepared insert. You would have needed it for an unprepared statement just as well in this case. You expect the planner to guess which columns you left out? ;) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c4ac805286212004583493! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incorrect FTS result with GIN index
Artur,
you could get much more problems in future. Full text search problem may be
signature of more general problem with your postgres setup. So, I'd recommend
to find a source of the problem
Oleg
On Tue, 20 Jul 2010, Artur Dabrowski wrote:
Oleg,
thanks for your help.
I sent a post to pg-hackers list:
http://old.nabble.com/Query-results-differ-depending-on-operating-system-%28using-GIN%29-ts29213082.html
As to compiling pg... I will no do this since I do not really feel
comfortable doing it and cannot dedicate too much time to this problem.
Artur
Oleg Bartunov wrote:
Artur,
I recommend post your problem to -hackers mailing list. I have no idea,
what could be a problem.
My machine is:
uname -a
Linux mira 2.6.33-020633-generic #020633 SMP Thu Feb 25 10:10:03 UTC 2010
x86_64 GNU/Linux
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu
4.4.1-4ubuntu9) 4.4.1, 64-bit
As a last resort I recommend you to compile pg yourself and see if the
problem exists.
Oleg
On Tue, 20 Jul 2010, Artur Dabrowski wrote:
I tested the same backup on our CentOS 5.4 virtual machine (running on
xen
server) and the results are really weird (118 rows, comparing to 116 on
win
xp and 123 expected):
Aggregate (cost=104.00..104.01 rows=1 width=0) (actual
time=120.373..120.374 rows=1 loops=1)
- Bitmap Heap Scan on search_tab (cost=5.35..103.93 rows=25 width=0)
(actual time=59.418..120.137 rows=118 loops=1)
Recheck Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
- Bitmap Index Scan on idx_keywords_ger (cost=0.00..5.34
rows=25
width=0) (actual time=59.229..59.229 rows=495 loops=1)
Index Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
Total runtime: 120.670 ms
And here are the configuration details:
PostgreSQL:
postgresql84-server-8.4.4-1.el5_5.1
# uname -r
2.6.18-164.15.1.el5xen
# cat /etc/redhat-release
CentOS release 5.4 (Final)
# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU5140 @ 2.33GHz
stepping: 6
cpu MHz : 2333.416
cache size : 4096 KB
physical id : 0
siblings: 1
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu de tsc msr pae cx8 apic sep cmov pat clflush acpi
mmx
fxsr sse sse2 ss ht syscall lm constant_tsc pni cx16 lahf_lm
bogomips: 5835.83
clflush size: 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
Oleg Bartunov wrote:
Artur,
I don't know, but could you try linux machine ?
Oleg
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] pg_dump, shemas, backup strategy
Michael A. Peters wrote: I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the 8.1.21 is the important part. That's a bad policy with PostgreSQL. I guarantee you that the problems you will run into because you're on PostgreSQL 8.1 are far worse than any you might encounter because you've updated from RedHat's PostgreSQL to the RPM packages provided by the PostgreSQL packagers. There are hundreds of known limitations in 8.1 you will absolutely suffer from as you expand your deployment that have been fixed in later versions. Yes, you can run into a packaging problem after upgrading to the PostgreSQL provided 8.3 or 8.4 that doesn't exist with the 8.1 they ship. But that's a *possible* issue, compared to the *guaranteed* limitations that are removed by using a later version of the database. Also, take a look at http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy 8.1 will be a frozen release no longer receiving bug fixes real soon now. You've already run into the first It seems that my version of pg_dump does not accept...; expect many more of those if you decide you must stay on 8.1. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Question about SCO openserver and postgres...
On Jul 23, 2010, at 7:42 AM, Edmundo Robles L. wrote: By the way i send a mail to SCO but until now they don't answer to me. Not surprising, since they've been in bankruptcy for a while now (a couple of years?) and have almost no employees left. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Question about SCO openserver and postgres...
Scott Ribe wrote: On Jul 23, 2010, at 7:42 AM, Edmundo Robles L. wrote: By the way i send a mail to SCO but until now they don't answer to me. Not surprising, since they've been in bankruptcy for a while now (a couple of years?) and have almost no employees left. Right; SCO filed for bankruptcy in 2007, and it's amazing they still get people to buy their products at all. I thought the suggestion of moving the database server to another server was the best one here. If you have an application that must be run on SCO, hopefully the PostgreSQL part of it can at least be relocated onto another platform and system. Talking about SCO always brings a smile to my face, as my first round of serious PostgreSQL work was funded by profits I made shorting their stock in 2003. After reading http://perens.com/SCO/SCOCopiedCode.html and confirming the code mentioned was in my Lions commentary and C Programming Language books, I opened a trading account and placed my bet that they were wrong about infringement. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [GENERAL] pg_dump, shemas, backup strategy
On 24 Jul 2010, at 24:20, Michael A. Peters wrote: *snip* Schema's in Postgres are similar to different databases in MySQL. They allow you to organise your tables in groups of tables belonging to similar functionality, for example. They have their own permissions too, which is nice if you need to restrict certain users to certain functionality. And of course you can access tables cross-schema, if you aren't denied the permissions. In your case, you could move those troublesome tables into their own schema and adjust the search_path accordingly for the user your PHP application uses to connect to the DB. I spent last night playing with schemas and I must say, they absolutely rock. I especially like the fact that you can still do queries involving multiple schemas if you need to because they are still part of the same database, and pg_dump keeping track of the various user authentications granted to a schema and its tables is class. It's the right way to do things. *snip* You could move Sphyder's tables into a separate schema too, but... if you disallow the accompanying role (let's say 'sphyder') access to the public schema, then it can't read various system tables either. That can cause issues with looking up FK constraints and the like. Mind that I've never been in a situation where I needed to disallow some roles to access to the public schema, I'm not 100% sure about this - a simple test case is easy to create though. I'd probably just put most (or all) of my main database in a schema other than 'public' so that the sphyder role can still access the system tables it needs (and it won't be able to change those if that role is set up with sufficiently restrictive permissions). That's what I'm doing now. As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is pretty good too. It's built into the main database since version 8.3, not in your version. For 8.1 there is an extension with largely the same functionality, in case you're interested. I'm not sure how easy that would be to upgrade to the builtin version once you get to 8.3 or newer though... I am going to look into that. - Michael A. Peters http://www.shastaherps.org/ -- 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] pg_dump, shemas, backup strategy
Michael A. Peters wrote: I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the 8.1.21 is the important part. That's a bad policy with PostgreSQL. I guarantee you that the problems you will run into because you're on PostgreSQL 8.1 are far worse than any you might encounter because you've updated from RedHat's PostgreSQL to the RPM packages provided by the PostgreSQL packagers. There are hundreds of known limitations in 8.1 you will absolutely suffer from as you expand your deployment that have been fixed in later versions. Yes, you can run into a packaging problem after upgrading to the PostgreSQL provided 8.3 or 8.4 that doesn't exist with the 8.1 they ship. But that's a *possible* issue, compared to the *guaranteed* limitations that are removed by using a later version of the database. Also, take a look at http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy 8.1 will be a frozen release no longer receiving bug fixes real soon now. OK. I already maintain my own php RPMs because RHEL php is too old (I need the DOMDocument stuff) so maybe I need to add Postgresql to that. PHP is the only thing I currently have that links against postgresql anyway. -- 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] pg_dump, shemas, backup strategy
Michael A. Peters wrote: I already maintain my own php RPMs because RHEL php is too old (I need the DOMDocument stuff) so maybe I need to add Postgresql to that. Note that you don't even have to build them yourself; the set at https://public.commandprompt.com/projects/pgcore/wiki/ are a straightforward drop-in replacement for the ones that RedHat provides. Subscribe to that yum repo just for the postgresql* packages and you can easily run 8.3 or 8.4 instead of the system 8.1. You might need to recompile your custom PHP against that afterwards, but you shouldn't have to build the database itself completely from source. And you'll still get security updates and bug fix point upgrades from that yum repo, continuing after the ones for 8.1 slow down. I've already started playing with the beta for RHEL6 just to avoid this whole stale package mess for a number of things. Will be nice when that ships, and the cycle of enterprise releases from them starts over with up to date packages again. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] pg_dump, shemas, backup strategy
Greg Smith g...@2ndquadrant.com writes: Michael A. Peters wrote: I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the 8.1.21 is the important part. That's a bad policy with PostgreSQL. I guarantee you that the problems you will run into because you're on PostgreSQL 8.1 are far worse than any you might encounter because you've updated from RedHat's PostgreSQL to the RPM packages provided by the PostgreSQL packagers. Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for awhile --- it's the postgresql84-* package set. I would hope CentOS has copied that by now. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about SCO openserver and postgres...
Shorting stock is not my usual strategy, so I was a chicken, and only tied up about 10% of my available credit shorting it back then. Hindsight is that instead of just going based on their botched presentation and obvious lack of understanding of the provenance of their own code and Linux, I should have researched the company like I would for any other investment. Had I done that, I believe I would have gone all in on that short position... On Jul 24, 2010, at 3:09 PM, Greg Smith wrote: Talking about SCO always brings a smile to my face, as my first round of serious PostgreSQL work was funded by profits I made shorting their stock in 2003. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] pg_dump, shemas, backup strategy
Tom Lane wrote: Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for awhile --- it's the postgresql84-* package set. I would hope CentOS has copied that by now. They have, as of CentOS's 5.5 back in May, and I keep forgetting its there. I'm not sure whether I like the trade-offs that come from using that packaging in every case yet though. The dependency issues with httpd are particularly weird: http://wiki.centos.org/Manuals/ReleaseNotes/CentOS5.5 (last item in Known Issues). I personally would rather just replace the system database with the newer version directly as the PGDG yums do, but you're right that some might prefer to use the system one instead. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] pg_dump, shemas, backup strategy
Tom Lane wrote: Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for awhile --- it's the postgresql84-* package set. I would hope CentOS has copied that by now. They have, as of CentOS's 5.5 back in May, and I keep forgetting its there. I'm not sure whether I like the trade-offs that come from using that packaging in every case yet though. The dependency issues with httpd are particularly weird: http://wiki.centos.org/Manuals/ReleaseNotes/CentOS5.5 (last item in Known Issues). I personally would rather just replace the system database with the newer version directly as the PGDG yums do, but you're right that some might prefer to use the system one instead. I went with the upstream postgresql RPMs. They provided a compat package for CentOS stuff that links against older client libs, so it works out nicely. The reason I went with CentOS for server when I first started my project was because I wanted a system that had long term vendor maintenance and kept things stable rather than bleeding edge, a system that required minimal package maintenance on my part. The postgresql yum repo allows that. Since PostgreSQL has a 5 year commitment to support, even though it isn't vendor packaging I can pretty much guarantee that I'll have upgraded the server before that time limit expires, and even if they don't provide RPMs for that long, I can maintain the src.rpm for 8.4 series myself if I need to (which I hope I don't). The library version was not the issue with my php connection problem, though building against newer client libs was probably a good idea anyway. Still looking at it (yes I checked and double checked pg_hba.conf), I'll figure it out. - Michael A. Peters http://www.shastaherps.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DROP CONSTRAINT IF EXISTS - simulating in 7 and 8?
Hi, I really like the new:- ALTER TABLE *table* DROP CONSTRAINT IF EXISTS *contraint* But I need to achieve the same thing on earlier versions. I've tried googling with no luck, how do I do it? Thanks Lyle -- 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] pg_dump, shemas, backup strategy
The library version was not the issue with my php connection problem I needed to grant connect. I guess that must be new? Anyway I knew it was likely something simple. - Michael A. Peters http://www.shastaherps.org/ -- 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] Question about SCO openserver and postgres...
On 23/07/10 05:24, Edmundo Robles L. wrote: Hi! I have a problem with the max postgres connections on SCO Openserver 5.0.7, so ...my boss decided to buy the SCO Openserver 6.0 but this version comes in 2 editions: Starter and Enterprise. If SCO 5.0.7 only allows 95 ( -3 used by superuser) connections to postgres... Do you know how many connections to postgres can i have with OpenServer in Starter Edition or Enterprise edition? Upgrading from 5.0.5 / 5.0.7 to 6.0 is like upgrading from Windows 95 to Windows ME in 2010. Or Mac OS 7.1 to Mac OS 9.2. You're upgrading from the corpse of an operating system to one that's still twitching feebly. This is not going to be a good way to invest time and money. Your boss may not realize that SCO basically dropped OpenServer as a product line in favour of UnixWare in the late 90s. Since then there was no significant work done on OpenServer. There's been no work done on it at all (as far as I can tell) since Caldera bought the SCO name and OpenServer product from the original Santa Cruz Operation, fired all the software engineers, hired some lawyers and sued world+dog. The Santa Cruz Operation renamed themselves Tarantella after their primary profitable product and went on with life, but SCO as a company is history. OpenServer is dead, dead, dead. Any money put into products targeting openserver is a sunk cost, and you can't change that, but you should really avoid sinking more money into that mess. If your management is still sticking to OpenServer, they should probably read about escalation of commitment, a decision making tendency that's very dangerous and very easy to fall into if you don't think about it carefully. In case you think I'm just a Linux zealot flag-waving, I have a SCO OpenServer 5.0.5 box in the back room, running business critical applications. The apps are actually for Microsoft Xenix (yes, 1983 binaries) running in the Xenix persionality on OpenServer. I considered a port to OpenServer 6.0, but realized it was just slightly delaying the inevitable move to something modern. So ... I keep it running - in VMWare, since 5.0.5 runs about ten times faster as a VMWare guest on a Linux host than it does natively on the same hardware. It's faster because SCO doesn't use much RAM for disk cache, doesn't readahead, and is generally just sloow in its disk access and memory use strategies. The Linux guest in a vmware setup can cache the whole SCO OS and apps disk in RAM, making the whole setup much faster. It seems more stable under VMWare than running natively on modern hardware, too. I'd recommend you do much what I've done. Move your SCO instances to VMs running under Linux. Provide modern PostgreSQL on the Linux host, and just compile libpq for the SCO guest. Then start work on migrating your app to run natively on Linux/BSD/Solaris/whatever. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.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] Question about SCO openserver and postgres...
Just noticed an error I should correct. There's been no work done on it at all (as far as I can tell) since Caldera bought the SCO name and OpenServer product from the original Santa Cruz Operation, fired all the software engineers, hired some lawyers and sued world+dog. I did overstate that bit a little. They've done some small work on improving OpenServer since the transfer to Tarentella, including 5.0.7V, according to the ever-reliable Wikipedia: http://en.wikipedia.org/wiki/SCO_OpenServer Note that The SCO Group (sco.com) still push 5.0.7, not 6.0, as their primary product. http://sco.com/ and primarily the for-virtualization version 5.0.7V. Wonder why? Maybe because the only people still using it are doing so to support legacy apps in an otherwise more modern environment, doing as little as possible on SCO. http://sco.com/products/openserver507v/hyperv/ For what it's worth, SCO OpenServer (5.0.5 at least) runs fine on VMWare anyway. I wonder if they've done anything more than packaged up a VM image of 5.0.7 with this product. They certainly don't mention anything useful like paravirt drivers for network and disk I/O. Anyway, let me reiterate: virtualize, then run away as fast as you can. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
