Re: [GENERAL] clustering without locking
On Thu, May 01, 2008 at 05:12:52PM -0700, fschmidt wrote: > > An implementation of clustering without locking would start by comparing the > index to the table from the beginning to find the first mismatch. Rows > before the mismatch are fine, and can be left alone. From here on, go > through the index and rewrite each row in order. This will put the rows at > the end of the table in cluster order. When done, vacuum the table. This > will result in a clustered table without any locking needed. Those few > records that were updated while clustering was happening will be out of > order, but that should only be a few. Huh? If I'm understanding you correctly you'll end up with rows in order, but with a really big hole in the middle of the table. I'm not sure if that qualifies as "clusters". > So, could this work? I could really use clustering without locking. Nice idea, but I don't think it's going to work. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] How to modify ENUM datatypes?
>> Maybe I'm some crazy, radical DBA, but I've never had a version of >> pgsql get EOLed out from underneath me. Just for fun, I did a bit of digging in the release notes http://developer.postgresql.org/pgdocs/postgres/release.html and came up with this table about PG major releases and their follow-on bug fix/minor releases: Version Release date# updates Days till final update Days till next major 6.0 1997-01-29 0 0 130 6.1 1997-06-08 1 44 116 6.2 1997-10-02 1 15 150 6.3 1998-03-01 2 37 243 6.4 1998-10-30 2 51 222 6.5 1999-06-09 3 126 334 7.0 2000-05-08 3 187 340 7.1 2001-04-13 3 124 297 7.2 2002-02-04 8 1190296 7.3 2002-11-27 21 1867355 7.4 2003-11-17 19+ ? 429 8.0 2005-01-19 15+ ? 293 8.1 2005-11-08 11+ ? 392 8.2 2006-12-05 7+ ? 426 8.3 2008-02-04 1+ ? ? It's pretty clear that there was a sea-change around 7.2/7.3 --- before that, nobody thought that PG releases were anything that might be long-lived. And there's nothing in this table that suggests we've really settled on a new lifespan ... other than that we're still putting out new majors at a constant rate, and the community hasn't got the resources or interest to maintain an ever-increasing number of back branches. 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] How to modify ENUM datatypes?
> Maybe I'm some crazy, radical DBA, but I've never had a version of > pgsql get EOLed out from underneath me. I migrated from 7.4 to 8.1 > right around the time 8.2 came out then upgraded to 8.2 around 6 > months later. > > Where I work now we are looking at migrating from 8.1 to 8.2 or 8.3 > (depending on whether or not we have the man power to fix a few issues > with type coercion, our app, and 8.3) These aren't "the DBA got a > wild hair and just had to upgrade" upgrades. Each time I've migrated > it's been because there were performance or maintenance issues that > were solved by upgrading. Perhaps I'm in a unique situation as well, but as the DBA of a data-tank style DB, I haven't had a problem at all finding opportunities to upgrade to later versions of postgresql. My schema isn't all that complicated; it's just a very large amount of data and some very complex queries on that data- but the queries have been kept to extremely standard SQL specifically for migration and cross-platform reasons. It's definitely been annoying on occasion to find that I need to do a dump and restore to move to a new version, but at the same time cheap, large storage is extremely inexpensive when compared to the sort of storage acceptable for day-to-day use, so size isn't generally a problem- just dump to a big, cheap disk and then restore. I'm probably lucky in that I manage a shop that can tolerate a day's downtime for such a situation, but at the same time, we also demand the most from database performance for complex queries, so a day's downtime here could easily save many days' worth of query time down the line. 8.3, FWIW, was particularly attractive in this regard. I couldn't quite justify upgrading to the release candidates, but the performance improvements were pretty tempting. -- - David T. Wilson [EMAIL PROTECTED] -- 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 modify ENUM datatypes?
On Thu, May 1, 2008 at 3:57 PM, Chris Browne <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] (Robert Treat) writes: > > On Thursday 01 May 2008 13:40, Tom Lane wrote: > > >> 7.4 was released 2003-11-17, so I think that it will very likely get > >> obsoleted at the end of 2008. > >> > > If that's the case, it'd be nice to get an official statement of that now. > :-) > > People have been making noises suggesting the idea already; I expect > that the flurry of counterarguments will start diminishing at that > point. Maybe I'm some crazy, radical DBA, but I've never had a version of pgsql get EOLed out from underneath me. I migrated from 7.4 to 8.1 right around the time 8.2 came out then upgraded to 8.2 around 6 months later. Where I work now we are looking at migrating from 8.1 to 8.2 or 8.3 (depending on whether or not we have the man power to fix a few issues with type coercion, our app, and 8.3) These aren't "the DBA got a wild hair and just had to upgrade" upgrades. Each time I've migrated it's been because there were performance or maintenance issues that were solved by upgrading. OTOH, a db I set up YEARS ago on 7.2 was still running last year I believe. they dump, initdb and reload it every year or two and it still works for what they designed the app on top of it to do. -- 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] Problem revoking a user's 'create' privilege on schema public
"James Dietrich" <[EMAIL PROTECTED]> writes: > Why does user2 still have create privilege on schema public? You revoked that privilege in database postgres, which has little to do with its state in any other database (and certainly not in template1 which is what you cloned to make db1). 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] Problem revoking a user's 'create' privilege on schema public
I am having trouble revoking a user's create privilege on
schema public.
Here is the sequence of commands that demonstrates the problem:
[EMAIL PROTECTED]:~$ su
Password:
saturn:/home/jdietrch# su postgres
[EMAIL PROTECTED]:/home/jdietrch$ psql
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# revoke all on schema public from public;
REVOKE
postgres=# create role user1 password 'user1' login createdb;
CREATE ROLE
postgres=# create role user2 password 'user2' login;
CREATE ROLE
postgres=# revoke all on schema public from user2;
REVOKE
postgres=# grant usage on schema public to user2;
GRANT
postgres=# \q
[EMAIL PROTECTED]:/home/jdietrch$ psql -U user1 template1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> create database db1;
CREATE DATABASE
template1=> \q
[EMAIL PROTECTED]:/home/jdietrch$ psql -U user1 db1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db1=> create table table1(id integer);
CREATE TABLE
db1=> select has_schema_privilege('public', 'create');
has_schema_privilege
--
t
(1 row)
db1=> \q
[EMAIL PROTECTED]:/home/jdietrch$ psql -U user2 db1
Password for user user2:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db1=> create table table2(id integer);
CREATE TABLE
db1=> select has_schema_privilege('public', 'create');
has_schema_privilege
--
t
(1 row)
db1=> \q
[EMAIL PROTECTED]:/home/jdietrch$
Notice that both user1 and user2 were allowed to create
a table in the database.
Why does user2 still have create privilege on schema public?
I am expecting that user2 should not be permitted to
create a table in the database that user1 created.
If someone could point out to me what I'm doing wrong,
I'd be very grateful.
Thank you,
James Dietrich
P.S. I'm running Debian GNU/Linux:
[EMAIL PROTECTED]:~$ uname -a
Linux saturn 2.6.22-3-vserver-k7 #1 SMP Mon Nov 12 11:47:04 UTC 2007
i686 GNU/Linux
[EMAIL PROTECTED]:~$ psql -U user1 template1
Password for user user1:
Welcome to psql 8.3.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> select version();
version
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Debian 4.2.3-2)
(1 row)
template1=> \q
[EMAIL PROTECTED]:~$
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] clustering without locking
An implementation of clustering without locking would start by comparing the index to the table from the beginning to find the first mismatch. Rows before the mismatch are fine, and can be left alone. From here on, go through the index and rewrite each row in order. This will put the rows at the end of the table in cluster order. When done, vacuum the table. This will result in a clustered table without any locking needed. Those few records that were updated while clustering was happening will be out of order, but that should only be a few. So, could this work? I could really use clustering without locking. -- View this message in context: http://www.nabble.com/clustering-without-locking-tp16996348p16996348.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to modify ENUM datatypes?
[EMAIL PROTECTED] (Robert Treat) writes: > On Thursday 01 May 2008 13:40, Tom Lane wrote: >> 7.4 was released 2003-11-17, so I think that it will very likely get >> obsoleted at the end of 2008. >> > If that's the case, it'd be nice to get an official statement of that now. :-) People have been making noises suggesting the idea already; I expect that the flurry of counterarguments will start diminishing at that point. -- let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/sap.html "My dog appears to require more PM than my car, although he also seems to be cheaper to service." -- GSB -- 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 modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > On Thursday 01 May 2008 13:40, Tom Lane wrote: >> I'm not sure how you're doing the math, but my copy of the release notes >> dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five >> years plus that we provided bug-fix releases for 7.3. > The whole thing started with "If I were to have installed postgres 5 years > ago", which would be 2003-05-01, then I would not have gotten 5 years of > support from that system. Essentially that statement is true of any install > up to the 7.4 release. I have never heard of anyone measuring product support lifespans from any point other than the original release date. If you want to define it in some random other fashion, that's your privilege, but it doesn't change how I'm going to think about it. 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] inheritance. more.
"Nathan Boley" <[EMAIL PROTECTED]> writes: > Because people can be smarter about the data partitioning. > > Consider a table of users. Some are active, most are not. The active > users account for nearly all of the users table access, but I still > (occasionally) want to access info about the inactive users. > Partitioning users into active_users and inactive_users allows me to > tell the database (indirectly) that the active users index should stay > in memory, while the inactive users can relegated to disk. (Someone's going to mumble something about partial indexes here.) The 50,000 ft view of partitioning is it: a) Lets the database do some work in query plan time instead of at run-time. So yes, an index would let you skip scanning parts of the table but you still have to do a few comparisons and page accesses on your index at run-time. On a partitioned table you do that same work (and it's harder) but at plan time. b) Lets you partition based on a key which isn't indexed at all. Consider in the above scenario if you then run a query across *all* active users. Even partial indexes won't be very fast but a partitioned table can do a sequential scan of a single partition. c) Makes loading pre-organized segments of data and dropping segments O(1) which is makes the data much more manageable. It's really (c) which is the killer app for partitioned tables. (a) and (b) are usually just nice side-shows. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- 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] inheritance. more.
Nathan Boley wrote: Because people can be smarter about the data partitioning. Consider a table of users. Some are active, most are not. The active users account for nearly all of the users table access, but I still (occasionally) want to access info about the inactive users. Partitioning users into active_users and inactive_users allows me to tell the database (indirectly) that the active users index should stay in memory, while the inactive users can relegated to disk. -Nathan On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris <[EMAIL PROTECTED]> wrote: Gurjeet Singh wrote: One of the advantages of breaking up your data into partitions, as professed by Simon (I think) (and I agree), is that you have smaller indexes, which improve performance. And maybe having one huge index managing the uniqueness across partitioned data just defeats the idea of data partitioning! Isn't "large indexes are a performance problem" just saying "we don't implement indexes very well"? And why are they a problem - surely a tree-structured index is giving you range-partitioned subsets as you traverse it? Why is this different from manual partitioning into (inherited) tables? Agreed, data placement is one reason for partitioning. But won't this happen automatically? Won't, in your example, the active part of a one-large-index stay in memory while the inactive parts get pushed out? Cheers, Jeremy -- 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 modify ENUM datatypes?
On Thursday 01 May 2008 13:40, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > And again, if you do the math, any install before 2008-11-17 would have
> > been on 7.3, which is less than 5 years.
>
> I'm not sure how you're doing the math, but my copy of the release notes
> dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five
> years plus that we provided bug-fix releases for 7.3.
>
The whole thing started with "If I were to have installed postgres 5 years
ago", which would be 2003-05-01, then I would not have gotten 5 years of
support from that system. Essentially that statement is true of any install
up to the 7.4 release.
> > Or, looking forward, I'm not expecting
> > 7.4 will be supported beyond 2010 (there have already been calls to stop
> > supporting it for some time) which is what would be required if we really
> > have an expectation of support for more than 5 years.
>
> 7.4 was released 2003-11-17, so I think that it will very likely get
> obsoleted at the end of 2008.
>
If that's the case, it'd be nice to get an official statement of that now. :-)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} 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] inheritance. more.
Because people can be smarter about the data partitioning. Consider a table of users. Some are active, most are not. The active users account for nearly all of the users table access, but I still (occasionally) want to access info about the inactive users. Partitioning users into active_users and inactive_users allows me to tell the database (indirectly) that the active users index should stay in memory, while the inactive users can relegated to disk. -Nathan On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris <[EMAIL PROTECTED]> wrote: > Gurjeet Singh wrote: > > > One of the advantages > > of breaking up your data into partitions, as professed by Simon (I think) > > (and I agree), is that you have smaller indexes, which improve > performance. > > And maybe having one huge index managing the uniqueness across partitioned > > data just defeats the idea of data partitioning! > > > > Isn't "large indexes are a performance problem" just saying > "we don't implement indexes very well"? And why are they > a problem - surely a tree-structured index is giving you > range-partitioned subsets as you traverse it? Why is this > different from manual partitioning into (inherited) tables? > > Thanks, > Jeremy > > > > -- > 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] question about join
2008/5/1 Ottavio Campana <[EMAIL PROTECTED]>: > Osvaldo Kussama ha scritto: > > > > > > > To further explain, the following query selects both the rows from the > join > > > where id_ref_first_tab has the desired value and default_value = true, > while > > > I want to select the row corresponding to default_value = true only in > case > > > no row corresponding to id_ref_first_tab exists. > > > > > > select * from second_table join third_table on second_table.id = > > > third_table.id_ref_second_tab where id_ref_first_tab = 1 or > default_value = > > > true; > > > > > > I hope I've been clear enough... > > > > > > > Try: > > select * from second_table join third_table on second_table.id = > > third_table.id_ref_second_tab > > where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = > true); > > > > it's not what I want, because it can return two rows, while I want only one > row back, checking the first condition and optionally the second one only if > the first one is not matched. > > I don't know if it is possible, but if it could, it would be great. > > -- > Non c'e' piu' forza nella normalita', c'e' solo monotonia. > > SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE id_ref_first_tab = 1 UNION SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE default_value = true AND NOT EXISTS(SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE id_ref_first_tab = 1); Osvaldo -- 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] SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall
Tom Lane wrote: "vyang" <[EMAIL PROTECTED]> writes: I'm having trouble with postgres filling the log with SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall. That was fixed some time ago: 2007-05-17 21:20 tgl * src/backend/libpq/: be-secure.c (REL7_4_STABLE), be-secure.c (REL8_1_STABLE), be-secure.c (REL8_0_STABLE), be-secure.c (REL8_2_STABLE), be-secure.c: Remove redundant logging of send failures when SSL is in use. While pqcomm.c had been taught not to do that ages ago, the SSL code was helpfully bleating anyway. Resolves some recent reports such as bug #3266; however the underlying cause of the related bug #2829 is still unclear. Update to a newer release. Actually, this only fixes the repeated logging that could take down your entire server. The underlying issue is still there AFAIK, and can kill a single connection. Changing the SSL code to get around that is on my TODO for 8.4. //Magnus -- 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 join
Osvaldo Kussama ha scritto: To further explain, the following query selects both the rows from the join where id_ref_first_tab has the desired value and default_value = true, while I want to select the row corresponding to default_value = true only in case no row corresponding to id_ref_first_tab exists. select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = true; I hope I've been clear enough... Try: select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = true); it's not what I want, because it can return two rows, while I want only one row back, checking the first condition and optionally the second one only if the first one is not matched. I don't know if it is possible, but if it could, it would be great. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] How to modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > And again, if you do the math, any install before 2008-11-17 would have been > on 7.3, which is less than 5 years. I'm not sure how you're doing the math, but my copy of the release notes dates 7.3 as 2002-11-27 and 7.3.21 as 2008-01-07, which makes it five years plus that we provided bug-fix releases for 7.3. > Or, looking forward, I'm not expecting > 7.4 will be supported beyond 2010 (there have already been calls to stop > supporting it for some time) which is what would be required if we really > have an expectation of support for more than 5 years. 7.4 was released 2003-11-17, so I think that it will very likely get obsoleted at the end of 2008. 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] How to modify ENUM datatypes?
Robert Treat <[EMAIL PROTECTED]> writes: > This all sounds nice, but I don't see any movement from the project to > increase community commitment to 5 years for any release, so I think it's all > moot. "Movement"? We did in fact support 7.3 for five years. Other than the special case of deciding to obsolete pre-8.2 Windows ports, I don't see anything on the horizon that would cause us to obsolete the current releases earlier. If anything, I foresee pressure to support the latest releases longer than that, since as Greg said, they are more credible long-term prospects than 7.x ever was. 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] How to modify ENUM datatypes?
On Wednesday 30 April 2008 11:00, Craig Ringer wrote:
> Robert Treat wrote:
> > If one were to have built something on postgresql 5 years ago, they would
> > have had to do it on 7.3. Whenever anyone posts a problem on 7.3, the
> > first thing people do now days is jump up and down waving thier arms
> > about while exclaiming how quickly they should upgrade.
>
> [snip]
>
> > I'd have to
> > say that the core developers for this project do not release software
> > with the expectation that you will use if for more than 5 years.
>
> That says nothing about the people out there still using 7.3 and similar
> without problems, running well within its capabilities and happy with
> what it's doing. I doubt many people would advise them to upgrade - at
> least not in a hurry and not with any jumping and hand-waving.
>
> My impression from using PostgreSQL is that people using old versions
> are taken seriously. Data corruption, crash and security bug fixes get
> applied to very old versions. For example, 7.3.21 was released on Jan
> 2008, and includes several fixes:
>
> http://www.postgresql.org/docs/current/static/release-7-3-21.html
>
from those very release notes "This is expected to be the last PostgreSQL
release in the 7.3.X series. Users are encouraged to update to a newer
release branch soon."
If you are on any version of 7.3, the official response is "you need to
upgrade to a newer major version" regardless of your problems. You're
overlooking data-loss level bugs that can bite people even if they aren't
currently suffering from any issues.
And again, if you do the math, any install before 2008-11-17 would have been
on 7.3, which is less than 5 years. Or, looking forward, I'm not expecting
7.4 will be supported beyond 2010 (there have already been calls to stop
supporting it for some time) which is what would be required if we really
have an expectation of support for more than 5 years.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} 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] How to modify ENUM datatypes?
On Thursday 01 May 2008 01:30, Greg Smith wrote:
> On Wed, 30 Apr 2008, Robert Treat wrote:
> > Whenever anyone posts a problem on 7.3, the first thing people do now
> > days is jump up and down waving thier arms about while exclaiming how
> > quickly they should upgrade. While I am certain there are even older
> > versions of postgres still running in production out there, I'd have to
> > say that the core developers for this project do not release software
> > with the expectation that you will use if for more than 5 years.
>
> You could easily make a case that 7.3 wasn't quite mature enough overall
> to be useful for 5 years. There's little reason to keep pumping support
> effort into something with unfixable flaws. I know when I was using 7.4
> heavily, I never felt like that was something I could keep going for that
> long; the VACUUM issues in particular really stuck out as something I
> wouldn't be likely to handle on future hardware having larger databases.
>
> 8.1, on the other hand, is the first release I thought you could base a
> long-term effort on, and 8.2 and 8.3 have moved further in that direction.
> 8.1 has been out for 2.5 years now, and it seems like it's got plenty of
> useful left in it still (except on Windows). The improvements in 8.2 and
> 8.3 are significant but not hugely important unless you're suffering
> performance issues.
>
> Compare with 7.3, which came out at the end of 2002. By 2.5 years after
> that, the project was well into 8.0, which was clearly a huge leap.
> PITR, tablespaces, whole new buffer strategy, these are really fundamental
> and compelling rather than the more incremental improvements coming out
> nowadays.
>
This all sounds nice, but I don't see any movement from the project to
increase community commitment to 5 years for any release, so I think it's all
moot.
> (Obligatory Oracle comparison: for customers with standard support
> levels, Oracle 8.1 was EOL'd after slightly more than 4 years. It wasn't
> until V9 that they pushed that to 5 years)
>
And even that isn't full support. IIRC Oracle certified applications can only
be done within the first 3 years of the product. I think there are other
scenarios under 5 years as well.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} 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] Text search with multiple tables
I found a way to do this but I don't know if there is a better way. What I did was to create a separate index on each table and construct a query like: SELECT * FROM a WHERE (to_tsvector(...) @@ to_tsquery(...)) OR primaryKey IN (SELECT distinct(foreign_key) FROM b WHERE to_tsvector(...) @@ to_tsquery(...)) Is there a better way to do this? Thanks, -Mont On Thu, May 1, 2008 at 8:48 AM, Mont Rothstein <[EMAIL PROTECTED]> wrote: > Is it possible to perform a text search with tables A-->>B returning A for > matches in B? > What I want to do is to be able take columns from both A and B and perform > a search based on the keywords entered by the user that matches A, B, or > both but always returns A. > > Can this be done? > > Thanks, > -Mont > >
Re: [GENERAL] Deadlock situation?
On Thu, May 1, 2008 at 9:38 AM, Dan Armbrust <[EMAIL PROTECTED]> wrote: > On Wed, Apr 30, 2008 at 12:36 PM, <[EMAIL PROTECTED]> wrote: > > > In this case, Postgres had been started in the foreground on a > > > terminal, so I went to that terminal, and did a ctrl-c. Eventually, > > > postgres stopped, but the terminal wouldn't respond either - and I had > > > to close it. > > > > Just out of curiosity, could you maybe have XOFF'd the terminal? I've had > > cases in the past where Postgres got unhappy and froze when it couldn't > > write logging information to stderr. (Though, granted, in the cases I hit > > the server's stderr was redirected to a pipe that was full, so it's not > > 100% analogous) > > Its certainly possible that something "interesting" happened to the > terminal. We do have an issue with a KVM switch on this machine that > sometimes causes all sorts of unpredictable random garbage input to > hit the screen when you switch to the system. Place I worked at 5 to 10 years or so ago, I specifically did NOT allow them to hook up the KVM switch to my primary servers for just this reason, plus it kept the junior super administrators from doing stupid things like hitting CTRL-ALT-DELETE on one of my servers. On the very very rare occasion I needed to actually sit in front of the server I'd hook up my one kvm connector and do what I had to, then I'd disconnect it and do everything else remotely. Admittedly, it wasn't the KVM that was at fault there, but it sure did cause some heart ache when someone sat down and hit CTRL-ALT-DEL to log into windows without looking at the screen and seeing that the last machine it was on was a Red Hat box... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Text search with multiple tables
Is it possible to perform a text search with tables A-->>B returning A for matches in B? What I want to do is to be able take columns from both A and B and perform a search based on the keywords entered by the user that matches A, B, or both but always returns A. Can this be done? Thanks, -Mont
Re: [GENERAL] Deadlock situation?
On Wed, Apr 30, 2008 at 12:36 PM, <[EMAIL PROTECTED]> wrote: > > In this case, Postgres had been started in the foreground on a > > terminal, so I went to that terminal, and did a ctrl-c. Eventually, > > postgres stopped, but the terminal wouldn't respond either - and I had > > to close it. > > Just out of curiosity, could you maybe have XOFF'd the terminal? I've had > cases in the past where Postgres got unhappy and froze when it couldn't > write logging information to stderr. (Though, granted, in the cases I hit > the server's stderr was redirected to a pipe that was full, so it's not > 100% analogous) Its certainly possible that something "interesting" happened to the terminal. We do have an issue with a KVM switch on this machine that sometimes causes all sorts of unpredictable random garbage input to hit the screen when you switch to the system. If I can't reproduce the problem (which so far I have not been able to) I'll probably let it go, perhaps naively believeing that some terminal issue set off the chain of events. Thanks, Dan -- 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 join
2008/5/1 Ottavio Campana <[EMAIL PROTECTED]>: > Hi, I'm having a problem trying to write a query using join, and I hope you > can give me a hint. > > suppose you have a three tables like these: > > create table first_table ( > id serial primary key, > description1 text); > > create table second_table ( > id serial primary key, > description2 text); > > create table third_table ( > id serial primary key, > description3 text, > id_ref_first_tab integer references first_table(id), > id_ref_second_tab integer references second_table(id), > default_value boolean); > > create unique index idx1 on third_table > (id_ref_first_tab,id_ref_second_tab); > > create unique index idx2 on third_table (id_ref_second_tab) where > default_value = true; > > What I'm trying to do is joining the second and the third tables on > second_table.id = third_table.id_ref_second_tab to extract all the values in > third_table where id_ref_first_tab has a given value or, in case it is not > present, to extract only row that has default_values = true; > > To further explain, the following query selects both the rows from the join > where id_ref_first_tab has the desired value and default_value = true, while > I want to select the row corresponding to default_value = true only in case > no row corresponding to id_ref_first_tab exists. > > select * from second_table join third_table on second_table.id = > third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = > true; > > I hope I've been clear enough... > Try: select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = true); Osvaldo -- 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] SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall
"vyang" <[EMAIL PROTECTED]> writes: > I'm having trouble with postgres filling the log with SSL SYSCALL error: A > blocking operation was interrupted by a call to WSACancelBlockingCall. That was fixed some time ago: 2007-05-17 21:20 tgl * src/backend/libpq/: be-secure.c (REL7_4_STABLE), be-secure.c (REL8_1_STABLE), be-secure.c (REL8_0_STABLE), be-secure.c (REL8_2_STABLE), be-secure.c: Remove redundant logging of send failures when SSL is in use. While pqcomm.c had been taught not to do that ages ago, the SSL code was helpfully bleating anyway. Resolves some recent reports such as bug #3266; however the underlying cause of the related bug #2829 is still unclear. Update to a newer release. 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] inheritance. more.
Gurjeet Singh wrote: One of the advantages of breaking up your data into partitions, as professed by Simon (I think) (and I agree), is that you have smaller indexes, which improve performance. And maybe having one huge index managing the uniqueness across partitioned data just defeats the idea of data partitioning! Isn't "large indexes are a performance problem" just saying "we don't implement indexes very well"? And why are they a problem - surely a tree-structured index is giving you range-partitioned subsets as you traverse it? Why is this different from manual partitioning into (inherited) tables? Thanks, Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compiling trigger function with MinGW
Hello All. Now I try to link dll with MinGW from Example in Postgres Help. Linker show me this error: D:\users\anthony\kursor\abzcrm\c\foo>gcc -shared foo.o -o foo.dll -L "d:/files/local/PostgreSQL/8.3/lib" -l postgres Cannot export ⌂postgres_NULL_THUNK_DATA: symbol not found collect2: ld returned 1 exit status What should I do? -- Anton Burkun +380 66 757 70 27 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about join
Hi, I'm having a problem trying to write a query using join, and I hope you can give me a hint. suppose you have a three tables like these: create table first_table ( id serial primary key, description1 text); create table second_table ( id serial primary key, description2 text); create table third_table ( id serial primary key, description3 text, id_ref_first_tab integer references first_table(id), id_ref_second_tab integer references second_table(id), default_value boolean); create unique index idx1 on third_table (id_ref_first_tab,id_ref_second_tab); create unique index idx2 on third_table (id_ref_second_tab) where default_value = true; What I'm trying to do is joining the second and the third tables on second_table.id = third_table.id_ref_second_tab to extract all the values in third_table where id_ref_first_tab has a given value or, in case it is not present, to extract only row that has default_values = true; To further explain, the following query selects both the rows from the join where id_ref_first_tab has the desired value and default_value = true, while I want to select the row corresponding to default_value = true only in case no row corresponding to id_ref_first_tab exists. select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = true; I hope I've been clear enough... Thanks in advance, Ottavio signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PITR problem
On 29 Kwi, 17:16, [EMAIL PROTECTED] (Erik Jones) wrote: > On Apr 29, 2008, at 3:20 AM, wstrzalka wrote: > > > > >> What is the full pg_standby command string (restore_command=) in > >> your recovery.conf. It sound's like you have pg_standby set to > >> delete > >> archived WALs and possibly have that a little too aggressive. Do you > >> have the -k flag set in your pg_standby call in your restore_command? > > > My restore command is: > > - > > restore_command = 'pg_standby -l -d -s 5 -w 0 -t /tmp/ > > pgsql.promote_trigger.5432 ~postgres/incoming_wal %f %p %r 2>&1 | > > logger -p local1.info -t pitr-standby' > > - > > > As you can see I didn't set -k to keep fixed number of WALs, but %r > > parameter and the PostgreSQL controls number of keeped files > > automatically (or at least it should) > > Ok, I hadn't yet set up a standby on 8.3 and so hadn't seen that the > %r macro obviates the need for the -k flag. So... > > The output from pg_standby: > > Trigger file : /tmp/pgsql.promote_trigger.5432 > Waiting for WAL file : 0001.history > WAL file path: /var/lib/pgsql/incoming_wal/ > 0001.history > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 5 seconds > Max wait interval: 0 forever > Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ > 0001.history" "pg_xlog/RECOVERYHISTORY" > Keep archive history : 0001000100DB and later > running restore : OK > > Trigger file : /tmp/pgsql.promote_trigger.5432 > Waiting for WAL file : 0001000100D9.0020.backup > WAL file path: /var/lib/pgsql/incoming_wal/ > 0001000100D9.0020.backup > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 5 seconds > Max wait interval: 0 forever > Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ > 0001000100D9.0020.backup" "pg_xlog/RECOVERYHISTORY" > Keep archive history : 0001000100DB and later > running restore : OK > > Note that here, from the start, postgres is telling the recovery > command that it only needs from 0001000100DB and on. > > Here's where it gets to restoring the first actual log file: > > Trigger file : /tmp/pgsql.promote_trigger.5432 > Waiting for WAL file : 0001000100D9 > WAL file path: /var/lib/pgsql/incoming_wal/ > 0001000100D9 > Restoring to... : pg_xlog/RECOVERYXLOG > Sleep interval : 5 seconds > Max wait interval: 0 forever > Command for restore : ln -s -f "/var/lib/pgsql/incoming_wal/ > 0001000100D9" "pg_xlog/RECOVERYXLOG" > Keep archive history : 0001000100DB and later > running restore : OK > removing "/var/lib/pgsql/incoming_wal/0001000100D9" > removing "/var/lib/pgsql/incoming_wal/0001000100DA" > > Since it says 'OK' but then fails my guess is that the order of > operations goes something along the lines of this (I could be totally > off): > > 1. Is /var/lib/pgsql/incoming/0001000100D9 present? -> OK > 2. Clean up files older than 0001000100DB -> Delete /var/ > lib/pgsql/incoming/0001000100D9 and /var/lib/pgsql/ > incoming/0001000100DA > 3. Restore /var/lib/pgsql/incoming/0001000100D9 -> This is > where it breaks. > > So, the question is: why does does the server say that it only needs > 0001000100DB and later? Did you clear out your pg_xlog > directory before starting up the standby? > Yes - the param passed to %r looks bad from start. Generally I like the %r because I don't need to worry if there are enough WALs to continue recovery after standby reboot and I don't keep many of the files at the same time, but I think something is wrong with it. And answering your question - I don't delete any files before standby start. So it looks like a bug for me - probably I should submit it to pgsql.bugs - unfortunatelly ( or fortunatelly :D ) my test environment is production now so I'll not be able to reproduce it easily. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall
Hello, I'm having trouble with postgres filling the log with SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall. I've googled search this error but came up with mostly 2 year old questions and no answers. Can anyone help or point in the right direction to fix this error. vyang -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
