Re: [GENERAL] Warning TupleDesc reference leak
Marek Lewczuk pisze: Hello, after upgrade to 8.2 version, PostgreSQL throws following warnings: WARNING: TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f60ad0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x4203d908 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fdc410 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fbb568 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42044bf0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42038e60 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41feebc0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fa0018 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fd9c30 (16425,-1) still referenced Hello again, after suggestions that I should postgres update to the latest (currently I have 8.2.3) I thought that my problem will be solved. But no, it is not - postgres still throws warnings: WARNING: TupleDesc reference leak: TupleDesc 0x41fd7018 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f42490 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fc3490 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x4200c880 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42023400 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f48728 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x4201d728 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41ffd8b0 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fe8370 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x420329e8 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x420026b0 (16427,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f8de78 (16427,-1) still referenced What does it mean ? Thanks in advance for help. ML ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Supported plpgsql BEFORE ... EACH ROW behavior
Karl O. Pinc wrote: Hi, I want to write a plpgsql function for use as a BEFORE ... EACH ROW function. I want to modify other tables even when the function returns NULL and therefore the table on which the BEFORE trigger is defined is not updated. Sorry for being paranoid about this but I want to double check before relying on behavior that few people probably use. I think it's fairly common, actually. Returning NULL is cancelling the update *of that row* rather than aborting the transaction, so all side-effects should always survive. Otherwise you couldn't update 100 rows and just skip one or two by returning NULL from a before trigger. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Large Objects
[EMAIL PROTECTED] wrote: Hi all ! I'm working on a database that needs to handle insertion of about 10 large objects (50..60GB) a day. It should be able to run 200 days, so it will become about 10TB eventually, mostly of 200..500KB large objects. How does access to large objects work ? I give the oid and get the large object... what is done internally ? How (if at all) are the oid's indexed ? Albe's answered your actual question, but I'd wonder if you really want to do this? The key question is whether you need to have the actual objects stored under transactional control. If not, just saving them as files will prove much more efficient. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PGSQL Locking vs. Oracle's MVCC
How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles concurreny and how it differs with Oracle's Multi-Version Concurrency Control (MVCC)? In PostgreSQL, old rows remain in the table until the table is vacuumed. In Oracle, old rows are kept in the 'undo table space' until - well, until the undo table space runs out and they are recycled. Depends. I have never heard this referred to as 'MVCC'. Locking and concurrency work pretty similar in both - at least as far as the behaviour is concerned. Yours, Laurenz Albe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
Reasonably. I have no idea what visibility rules would make
any difference at all. AIUI a foreign key just takes a shared
lock on the referenced row and all the magic of MVCC makes
sure the row exists when the transaction completes.
Try this:
(sorry for any typo's in SQL, if they exist)
snip
Well, I took a look at the RI code and the only stuff I saw that looked
interesting was this:
utils/adt/ri_triggers.c:
if (IsXactIsoLevelSerializable detectNewRows)
{
CommandCounterIncrement(); /* be sure all my own
work is visible */
test_snapshot = CopySnapshot(GetLatestSnapshot());
crosscheck_snapshot = CopySnapshot(GetTransactionSnapshot());
}
It then proceeds to use that snapshot to execute the query to get the share
lock.
It's probably true that other PL's can't do this directly. Not sure how
to deal with that. I got confused because I thought the first version
of RI did use straight pl/pgsql functions, so I thought that was
enough.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] complex referential integrity constraints
Robert Haas wrote: The idea here is that a wolf can attack a sheep, or a wolf can attack another wolf, but sheep can't attack anything. I suppose I could list each wolf in both the predator and prey tables, but that seems a bit duplicative (and causes other problems). ...Robert I'm quite certain a wolf is much more likely to attack a sheep than to attack another wolf, and even more unlikely to attack for example a lion. It seems to me that just the fact that it can isn't enough information. It looks like you need weighted constraints; there's 0 chance that a sheep attacks a wolf, but there's 0 chance that a wolf attacks a sheep, 0 chance it attacks a wolf and 0 chance it attacks a lion. The exact numbers will vary, and I have absolutely no idea what they would be like. It probably requires some kind of ranking system that adjusts according to the known animals and their likelihood to attack eachother. I'm pretty sure you can't get this done without defining some triggers. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] greedy or not? regexps...
Hi,
I am trying to understand the function substring.
Here:
select SUBSTRING(cit1.summary, '=([0-9]*)') from cms_items cit1
where cit1.summary ~* '.*linkadministration.*[0-9]*';
gives me two empty strings and
select SUBSTRING(cit1.summary, '=([0-9]{1,10})') from cms_items cit1
where cit1.summary ~* '.*linkadministration.*[0-9]*';
gives me the right values.
However, bizarrely,
select SUBSTRING(ban1.url_id, '=([0-9]*)')
from banner ban1
where ban1.url_id ~* '.*linkadministration.*[0-9]*';
select SUBSTRING(ban1.url_id, '=([0-9]{1,10})')
from banner ban1
where ban1.url_id ~* '.*linkadministration.*[0-9]*';
Both give me the same result!!! The difference being that in case two
the numbers I am catching are at the end of the strings and in case 1
in the middle. Is this normal? Which is correct?
Cheers
Anton
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Writing oracle/postgress generic SQL
Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. Ben -- Ben Edwards - Brussels, Belgium Bristol, UK If you have a problem emailing me use http://www.gurtlush.org.uk/profiles.php?uid=4 (email address this email is sent from may be defunct) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PGSQL Locking vs. Oracle's MVCC
How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles concurreny and how it differs with Oracle's Multi-Version Concurrency Control (MVCC)? Well, I'm currently working on this comparison as we will need to port and support some currently Postgresql-only applications on several other rdbms in the near future, and Oracle is among them. I still don't have a final report, but from the point of a developer the databases are almost the same, especially in the field of locking, i.e. - writer never blocks reader (and vice versa, the only exception is distributed transaction in-doubt) - there is 'SELECT ... FOR UPDATE' if you need to lock - there are some 'application locks' (advisory locks in PostgreSQL) - all the queries are consistent with respect to the beginning of the query - the default transaction level is READ COMMITED, it's possible to use SERIALIZABLE Sure, there are many differences when it comes to internals (Albe Laurenz already pointed out the most obvious one), as well as the Oracle is superior in many areas (partitioning, some features in PL/SQL, etc.). This generally means that if you have an application architecture for PostgreSQL, then it will usually work fine Oracle. If the constraint is correctly enforced in PostgreSQL, then it will be enforced in Oracle etc. There is no exact definition of MVCC, especially when it comes to implementation - there are many ways to do that, PostgreSQL uses one of them, Oracle uses another one. MVCC generally means that the DB is able to serve various versions of the same row (block). PostgreSQL does not overwrite the updated rows, Oracle uses undo log. Tomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Writing oracle/postgress generic SQL
On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote: Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. You've just bumped into the problem that while standard SQL exists, only Mimer and possibly DB2 implement it. The presentation below outlines your main choices for supporting more than one DB back-end, and they're all expensive and troublesome to maintain. http://www.powerpostgresql.com/Downloads/database_depends_public.swf The cheapest, highest-quality thing to do is to choose one DB back-end and then use everything it has to offer. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Triggers inherited?
Hi - I too have encountered this issue. The work around that I created was to have every table have a set of 3 cooresponding functions that know how to 1) create the table; 2) create triggers for the table; 3) create indexes for the table. By doing so, I then am able to use a lazy partitioning technique, such that an insert trigger determines if the necessary partition exists, and if not, calls the functions needed to create it. It keeps the SQL needed for a table in a single location (DRY), and is flexible enough to be used for creating virgin databases as well as updating existing databases. - Marc On Thu, 22 Feb 2007, Bertram Scharpf wrote: Hi, it is very inconvenient for me that triggers aren't inherited: create table watch ( mod timestamp with time zone default '-infinity' not null ); create function update_mod() returns trigger ... create trigger update_mod before insert or update on watch for each row execute procedure update_mod(); create table some ( ... ) inherits (watch); create table other ( ... ) inherits (watch); Is this behaviour to be implemented at any point of time in the future? Could it be advisible to write the patch? Or is it just too easy to emulate it? Reimplemeting a trigger for each descending table definitely dosn't satisfy me. Thanks in advance, Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: I installed wikipgdia for the WPLUG wiki: http://wplug.ece.cmu.edu/wiki/ Isn't that the same wikipgedia that is found at pgFoundry? The only issue I really had the the wikipgedia port is that the codebase is 1.6alpha, and it seemed like it wasn't being actively maintained anymore (infact that is what the description says), so I am not sure it has all of the bug fixes up to 1.6.10. In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6 is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia.
[GENERAL] Ruby on Rails for PostgreSQL
Given the recent discussions of applications stacks, PHP Ruby etc. it seems an ideal time for me to introduce a project I've been working on. StackBuilder is an extension of the Windows installer for PostgreSQL that will allow the user to quickly and easily download and install additional software to build the application stack they desire around PostgreSQL. The project includes the StackBuilder wizard as well as a toolkit for building application installers. Whilst the StackBuilder itself is still in development, the first application installer, pgRails, is available for manual download and testing from http://pgfoundry.org/projects/stackbuilder/ pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL connector all preconfigured for use with PostgreSQL on Windows 2000 and above. I'd like to invite anyone who is interested to download and try it out, and report any issues or problems using the project's trackers on pgFoundry. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PGSQL Locking vs. Oracle's MVCC
On 2/23/07, Tomas Vondra [EMAIL PROTECTED] wrote: Sure, there are many differences when it comes to internals (Albe Laurenz already pointed out the most obvious one), as well as the Oracle is superior in many areas (partitioning, some features in PL/SQL, etc.). This generally means that if you have an application architecture for PostgreSQL, then it will usually work fine Oracle. If the constraint is correctly enforced in PostgreSQL, then it will be enforced in Oracle etc. be careful with that statementsome of the internals are better and some are worse. all postgresql ddl is transactional for example. how easily the code ports is going to depend on how tweaky the developers were...it's very easy to fall in love with postgresql-specific features and write unportable code. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] select all matches for a regular expression ?
Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ODBCng and OpenOffice 2.1
I tried to connect OpenOffice 2.1 Base (win32) via ODBCng to a PG 8.1.5 database. While it lists schemas, tables and views just fine, I'm unable to edit data. OO always returns: The data content could not be loaded Invalid descritor index SQL Status: 07009 You tried to set a parameter at position 1 but there is/are only 0 parameter(s) allowed. One reason may be that the property ParameterNameSubstitution is not set to TRUE in the data source. Where is the problem? -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Anton Melser [EMAIL PROTECTED] wrote: Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster yes. use like or ~ see http://www.postgresql.org/docs/8.2/static/functions-matching.html There is no need to use perl. Peter. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Warning TupleDesc reference leak
Marek Lewczuk [EMAIL PROTECTED] writes: after suggestions that I should postgres update to the latest (currently I have 8.2.3) I thought that my problem will be solved. But no, it is not - postgres still throws warnings: WARNING: TupleDesc reference leak: TupleDesc 0x41fd7018 (16427,-1) still referenced Show us a test case please. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ODBCng and OpenOffice 2.1
Hannes Dorbath wrote: I tried to connect OpenOffice 2.1 Base (win32) via ODBCng to a PG 8.1.5 database. The data content could not be loaded Invalid descritor index SQL Status: 07009 You tried to set a parameter at position 1 but there is/are only 0 parameter(s) allowed. One reason may be that the property ParameterNameSubstitution is not set to TRUE in the data source. You'll probably need to activate query logging on the server, that way people can see the SQL that is being sent. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
In response to Chad Wagner [EMAIL PROTECTED]: On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: I installed wikipgdia for the WPLUG wiki: http://wplug.ece.cmu.edu/wiki/ Isn't that the same wikipgedia that is found at pgFoundry? Yes. The only issue I really had the the wikipgedia port is that the codebase is 1.6alpha, and it seemed like it wasn't being actively maintained anymore (infact that is what the description says), so I am not sure it has all of the bug fixes up to 1.6.10. I installed it as an experiment, then (while my back was turned) a bunch of people started using it ... now it's a mission-critical part of the WPLUG organization ... Hopefully there aren't any serious bugs hiding anywhere ... In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6 is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia. I would be interested. I'm probably expected to maintain this thing ... -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Peter Childs [EMAIL PROTECTED] wrote: On 23/02/07, Anton Melser [EMAIL PROTECTED] wrote: Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster yes. use like or ~ see http://www.postgresql.org/docs/8.2/static/functions-matching.html There is no need to use perl. ... I have read and re-read that page many times - I must be stupid :-(. For me both like and ~ on an expression will return true or false, and not a set of values. I have managed to get *one* value with substring(), but I need to get them all... As an example, I need to find all the occurences of digits in the following text myvar := 'hello4 is 4 very n1ce num8er'; so select substrings(myvar, '([0-9])); will return 4 4 1 8 Is *this* possible without perl? Could you give a paragraph number on that page if the info is there so I know exactly where to look? Thanks again, Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] server closed unexpectedly while executing a function
To add to my last post, in my perl function I'm using use DBI; my $dbh=DBI-connect(dbi:Pg:dbname=xyz; host=192.168.0.120; port=5432;, , ); to connect to the same DB server. And then i have series of dbh-prepare and -execute Unofrtunately it has started crying smth like no connection to the server where ever i have these execute statements. I just executes the first sql statement and fails at the rest Jas On 2/23/07, Jasbinder Singh Bali [EMAIL PROTECTED] wrote: Hi I'm running a function in perl and it says --- NOTICE: DBD::Pg::st execute failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. CONTEXT: SQL statement SELECT sp_email( $1 , $2 ) PL/pgSQL function func_trg_email line 2 at perform NOTICE: DBD::Pg::st execute failed: no connection to the server - how can i get more verbose messages that would tell me where exactly I'm going wrong. I'm calling function sp_email from a trigger function func_trg_email here. Thanks, jas
Re: [GENERAL] complex referential integrity constraints
Alban Hertroys wrote: Robert Haas wrote: The idea here is that a wolf can attack a sheep, or a wolf can attack another wolf, but sheep can't attack anything. I suppose I could list each wolf in both the predator and prey tables, but that seems a bit duplicative (and causes other problems). I'm quite certain a wolf is much more likely to attack a sheep than to attack another wolf, and even more unlikely to attack for example a lion. It seems to me that just the fact that it can isn't enough information. It looks like you need weighted constraints; there's 0 chance that a sheep attacks a wolf, but there's 0 chance that a wolf attacks a sheep, 0 chance it attacks a wolf and 0 chance it attacks a lion. The exact numbers will vary, and I have absolutely no idea what they would be like. It probably requires some kind of ranking system that adjusts according to the known animals and their likelihood to attack eachother. Depending on what you're modelling, even this could be too simple -- for example, while a single wolf is unlikely to attack a lion, a pack of wolves have a lot more probability of doing so. Do you keep packs of wolves in your barn? If so, watch your lions. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Warning TupleDesc reference leak
Tom Lane pisze: Marek Lewczuk [EMAIL PROTECTED] writes: after suggestions that I should postgres update to the latest (currently I have 8.2.3) I thought that my problem will be solved. But no, it is not - postgres still throws warnings: WARNING: TupleDesc reference leak: TupleDesc 0x41fd7018 (16427,-1) still referenced Show us a test case please. Hello Tom, Thanks for reply. You need a test case, but what it should include ? I don't know in what situations this warning is thrown. My database is quite large - hundreds of tables, tons of data... Where I should start ? Thanks ML ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Supported plpgsql BEFORE ... EACH ROW behavior
On 02/23/2007 02:03:25 AM, Richard Huxton wrote: Karl O. Pinc wrote: I want to write a plpgsql function for use as a BEFORE ... EACH ROW function. I want to modify other tables even when the function returns NULL and therefore the table on which the BEFORE trigger is defined is not updated. I think it's fairly common, actually. Returning NULL is cancelling the update *of that row* rather than aborting the transaction, so all side-effects should always survive. Thats good news. Thanks for the reply. But... Otherwise you couldn't update 100 rows and just skip one or two by returning NULL from a before trigger. But wanting side effects _when_ those one or two rows are skipped is probably not so common. I could imagine a implimentation of Postgresql that does a SAVEPOINT before executing a BEFORE ... EACH ROW trigger and then decides whether or not to ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. That would not break the usage case you give, but would break what I want to do. So this is what I'm wanting assurance about. I suppose this is kind of silly, seeing as how it's a BEFORE trigger we're talking about the db would not have been updated so a SAVEPOINT would not really be appropriate. But I did say I was being paranoid. Thanks again for the help. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] greedy or not? regexps...
Anton Melser [EMAIL PROTECTED] writes: I am trying to understand the function substring. You haven't actually said anything that sounded surprising; and in any case, without seeing the data being operated on, we can't comment much on what's happening. I will note that '=([0-9]*)' is going to match to the *first* = in the string, whether there happen to be any digits after it or not, because the *-construct can match zero characters. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Writing oracle/postgress generic SQL
Ben Edwards [EMAIL PROTECTED] writes: Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. The only thing that means anything is testing on both :-(. Yeah, there is a SQL standard, but there is no DBMS anywhere in the world that implements all and only what is in the spec. Exhibit A in this regard is that the standard refuses to specify any user-visible index manipulation; so the moment you write anything like CREATE INDEX you are on implementation-dependent ground. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] greedy or not? regexps...
Intellectually challenged Anton strikes again! I wanted +, not *. Sometimes I think I'm not cut out for IT! :-( Thanks heaps, Anton On 23/02/07, Tom Lane [EMAIL PROTECTED] wrote: Anton Melser [EMAIL PROTECTED] writes: I am trying to understand the function substring. You haven't actually said anything that sounded surprising; and in any case, without seeing the data being operated on, we can't comment much on what's happening. I will note that '=([0-9]*)' is going to match to the *first* = in the string, whether there happen to be any digits after it or not, because the *-construct can match zero characters. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Infinite loop in transformExpr()
En un mensaje anterior, Tom Lane escribió: PG versions before 8.2 don't handle very long IN lists particularly well. This query will take a fair amount of stack space to parse, not to mention an unreasonably long time to plan. (You should consider putting the 16000 values in a temp table and doing a join, instead.) Thanks for the tip! [...] Most likely, the production machine has a kernel-enforced stack limit setting that is less than what max_stack_depth claims. Up till recently (8.2 I think), we didn't make any effort to verify that max_stack_depth was set to a sane value. If it's too high you will get crashes rather than stack depth limit exceeded, because overrunning the kernel limit is typically treated as a SIGSEGV. [...] Hm. It would appear that you are loading some custom code that sucks pthread support into the backend. This is generally a bad idea in any Not really. Only PLSQL and dblink. Anyway, my understanding is that this should be already fixed in 8.2 and is not worth looking deeply, right? Thanks for your help. Fernando. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] server closed unexpectedly while executing a function
On Fri, Feb 23, 2007 at 09:34:51AM -0500, Jasbinder Singh Bali wrote: To add to my last post, in my perl function I'm using use DBI; my $dbh=DBI-connect(dbi:Pg:dbname=xyz; host=192.168.0.120; port=5432;, , ); to connect to the same DB server. And then i have series of dbh-prepare and -execute Unofrtunately it has started crying smth like no connection to the server where ever i have these execute statements. I just executes the first sql statement and fails at the rest Do the database logs of the server you're connecting to show what might be wrong? Have you used any of DBI's tracing options (see TRACING in the DBI manual page)? Does a standalone Perl script fail the same way? I'd suggest making sure the code works in a standalone script before using it in a trigger function. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Warning TupleDesc reference leak
Marek Lewczuk [EMAIL PROTECTED] writes: Thanks for reply. You need a test case, but what it should include ? I don't know in what situations this warning is thrown. My database is quite large - hundreds of tables, tons of data... Where I should start ? I doubt it's got anything to do with your data; I'd look at complex PL functions. If you don't already know which queries trigger it, increase your logging to find out. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] select all matches for a regular expression ?
Anton Melser [EMAIL PROTECTED] writes: I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? You need plperl (or pltcl; likely plpython would work too) --- the built-in regex functions don't have any way to return more than the first match. There's a patch pending to provide more functionality here for 8.3, but it won't help you today. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] false unique constraint error...for me
Hi all,
I don't know if it's a bug or not...but things are quite strange for me.
My problem comes from a unique constraint violation whereas tha data I try
to insert in my table are different (at least for me).
My database is encoded using SQL_ASCII, postgresql 7.4.8 on a Red Hat
Advanced Server v3 or 4)
I created a table :
CREATE TABLE trace_object (
object_id serial NOT NULL,
object_barcode character varying(15) NOT NULL,
object_barcode_128 character varying(25),
);
ALTER TABLE ONLY trace_object ADD CONSTRAINT pk_trace_object PRIMARY KEY
(object_id);
ALTER TABLE ONLY trace_object ADD CONSTRAINT trace_object_object_barcode_key
UNIQUE (object_barcode);
ALTER TABLE ONLY trace_object ADD CONSTRAINT unique_barcode128 UNIQUE
(object_barcode_128);
The column object_barcode contains human readable barcode (e.g.
AB28662097) and the column object_barcode_128 contains the crypted
barcode readable by LASER scanners once printed with the corresponding font.
We can determine the object_barcode_128 content, applying a function on
object_barcode (you can find it here :
http://grandzebu.net/informatique/codbar/code128_PLpgSQL.asc, sorry the
comments are in french). Let's call this function text2code128().
If I do :
INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES
('AB28662097', text2code128('AB28662097'));
INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES
('AB28662098', text2code128('AB28662098'));
I get the error : ERROR: duplicate key violates unique constraint
unique_barcode128
But the string returned by text2code128('AB28662097') and
text2code128('AB28662098') are different!!!, i.e., respectively ÌABÇb4ÅÃÎ
and ÌABÇb4ÆÊÎ.
Why do I get an error here? I really don't understand...I get this error
using my cgi interface, phpPgAdmin and command line.
Some other things :
If I drop the unique constraint unique_barcode128, I can insert my previous
data. Then if the request is :
SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 =
(SELECT text2code128('AB28662098'))
the two rows are returned ('ÌABÇb4ÅÃÎ' and 'ÌABÇb4ÆÊÎ').
if my request is :
SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 LIKE
(SELECT text2code128('AB28662098'))
I get one row 'ÌABÇb4ÆÊÎ'
could you please help me understanding what happens
I know that the unique index is created using B-TREE (CREATE UNIQUE INDEX
unique_barcode128 ON trace_object USING btree (object_barcode_128)). Is
there a way to have a look at the content of this index? Do you know how it
works and where I can find more information abour it?
thank you for your help
Gérald
_
Gagnez des pc Windows Vista avec Live.com http://www.image-addict.fr/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] complex referential integrity constraints
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: vrijdag 23 februari 2007 9:50
To: Joris Dobbelsteen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
Reasonably. I have no idea what visibility rules would make any
difference at all. AIUI a foreign key just takes a shared
lock on the
referenced row and all the magic of MVCC makes sure the row exists
when the transaction completes.
Try this:
(sorry for any typo's in SQL, if they exist)
snip
Well, I took a look at the RI code and the only stuff I saw
that looked interesting was this:
utils/adt/ri_triggers.c:
if (IsXactIsoLevelSerializable detectNewRows)
{
CommandCounterIncrement(); /* be
sure all my own work is visible */
test_snapshot = CopySnapshot(GetLatestSnapshot());
crosscheck_snapshot =
CopySnapshot(GetTransactionSnapshot());
}
It then proceeds to use that snapshot to execute the query to
get the share lock.
It's probably true that other PL's can't do this directly. Not
sure how to deal with that. I got confused because I thought
the first version of RI did use straight pl/pgsql functions,
so I thought that was enough.
You got it right...
/*
* SPI_execute_snapshot -- identical to SPI_execute_plan, except that we
allow
* the caller to specify exactly which snapshots to use. This is
currently
* not documented in spi.sgml because it is only intended for use by RI
* triggers.
*
* Passing snapshot == InvalidSnapshot will select the normal behavior
of
* fetching a new snapshot for each query.
*/
int
SPI_execute_snapshot(void *plan,
Datum *Values, const char
*Nulls,
Snapshot snapshot, Snapshot
crosscheck_snapshot,
bool read_only, long tcount)
They got the point right: only intended for use by RI triggers. That's
exactly the type I'm trying to build ;)
They are exposed to the C versions (its in include/executor/spi.h), but
to me it looks a bit cumbersome to have triggers written in C.
What would be a good way to expose this to normal PL triggers? Since
this would open a new set of possibilities...
As part of a create trigger ... for referencial integrity?
As an extension to a statement?
Special construct in the languages?
- Joris
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Tom Lane [EMAIL PROTECTED] wrote: Anton Melser [EMAIL PROTECTED] writes: I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? You need plperl (or pltcl; likely plpython would work too) --- the built-in regex functions don't have any way to return more than the first match. There's a patch pending to provide more functionality here for 8.3, but it won't help you today. Thanks for the info Cheers Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] false unique constraint error...for me
=?iso-8859-1?B?ZGrpIGRq6Q==?= [EMAIL PROTECTED] writes:
I get the error : ERROR: duplicate key violates unique constraint
unique_barcode128
But the string returned by text2code128('AB28662097') and
text2code128('AB28662098') are different!!!, i.e., respectively ÌABÇb4ÅÃÎ
and ÌABÇb4ÆÊÎ.
What locale are you running the server in? It's possible that these
strings are equal according to the locale-specific strcoll() behavior.
In particular, if you are using a locale that expects UTF8, it's pretty
common for strcoll to go nuts when faced with non-UTF8-legal strings.
You might be better off using bytea instead of varchar.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] complex referential integrity constraints
On Fri, 23 Feb 2007, Joris Dobbelsteen wrote:
-Original Message-
From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED]
Sent: vrijdag 23 februari 2007 9:50
To: Joris Dobbelsteen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
Reasonably. I have no idea what visibility rules would make any
difference at all. AIUI a foreign key just takes a shared
lock on the
referenced row and all the magic of MVCC makes sure the row exists
when the transaction completes.
Try this:
(sorry for any typo's in SQL, if they exist)
snip
Well, I took a look at the RI code and the only stuff I saw
that looked interesting was this:
utils/adt/ri_triggers.c:
if (IsXactIsoLevelSerializable detectNewRows)
{
CommandCounterIncrement(); /* be
sure all my own work is visible */
test_snapshot = CopySnapshot(GetLatestSnapshot());
crosscheck_snapshot =
CopySnapshot(GetTransactionSnapshot());
}
It then proceeds to use that snapshot to execute the query to
get the share lock.
It's probably true that other PL's can't do this directly. Not
sure how to deal with that. I got confused because I thought
the first version of RI did use straight pl/pgsql functions,
so I thought that was enough.
You got it right...
/*
* SPI_execute_snapshot -- identical to SPI_execute_plan, except that we
allow
* the caller to specify exactly which snapshots to use. This is
currently
* not documented in spi.sgml because it is only intended for use by RI
* triggers.
*
* Passing snapshot == InvalidSnapshot will select the normal behavior
of
* fetching a new snapshot for each query.
*/
int
SPI_execute_snapshot(void *plan,
Datum *Values, const char
*Nulls,
Snapshot snapshot, Snapshot
crosscheck_snapshot,
bool read_only, long tcount)
They got the point right: only intended for use by RI triggers. That's
exactly the type I'm trying to build ;)
They are exposed to the C versions (its in include/executor/spi.h), but
to me it looks a bit cumbersome to have triggers written in C.
I was wondering if some sort of generator might work. Something that would
take what you're trying to do and generate the triggers for you, but I
haven't really worked out what that'd look like.
What would be a good way to expose this to normal PL triggers? Since
this would open a new set of possibilities...
As part of a create trigger ... for referencial integrity?
As an extension to a statement?
Special construct in the languages?
I think the first thing to do is to figure out what such triggers need to
do. Does such a trigger need to potentially run some queries on the normal
snapshot? Does it potentially need different snapshots for different
statements or is only one special snapshot sufficient? And other such
questions. From there, a -hackers discussion might be meaningful.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/22/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I use mediawiki with postgres and it works fine, except for a bug regarding timestamps. That bug is due to mysqlism of the code. Once that's fixed, it will be ready as far as I'm concerned. editorialThere have been some tragic and embarrassing data losses by some big sites that should know better because they used mysql without the heroic measures that are needed to make it safe. I don't care that much that big sites use it, big sites start small and don't always start with the best tools. Once started, it's hard to switch over to better tools. If you used enough volkswagen beetles you could move the same number of passengers on the same routes as Greyhound does with buses, but that doesn't mean they are the right tool./editorial - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
Le vendredi 23 février 2007 16:37, Ian Harding a écrit : On 2/22/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake escribió: Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And outages if you watch :) Does this mean that we believe the Wikipedia would not suffer any outages if it ran on Postgres? How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? I use mediawiki with postgres and it works fine, except for a bug regarding timestamps. That bug is due to mysqlism of the code. Once that's fixed, it will be ready as far as I'm concerned. I get an error with tsearch2 query parser, and patch that. ( http://bugzilla.wikimedia.org/show_bug.cgi?id=8958 , thanks Greg ) editorialThere have been some tragic and embarrassing data losses by some big sites that should know better because they used mysql without the heroic measures that are needed to make it safe. I don't care that much that big sites use it, big sites start small and don't always start with the best tools. Once started, it's hard to switch over to better tools. If you used enough volkswagen beetles you could move the same number of passengers on the same routes as Greyhound does with buses, but that doesn't mean they are the right tool./editorial - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Writing oracle/postgress generic SQL
On Fri, 23 Feb 2007, David Fetter wrote: On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote: Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. You've just bumped into the problem that while standard SQL exists, only Mimer and possibly DB2 implement it. The presentation below outlines your main choices for supporting more than one DB back-end, and they're all expensive and troublesome to maintain. http://www.powerpostgresql.com/Downloads/database_depends_public.swf With all due respect to Josh's presentation, there's a lot more to the story than those couple of slides. (They were meant to be given, I'm sure, along with a talk in which the speaker provided most of the value.) And I don't think launching an attack on MySql is helpful to this dialogue, though I do understand the point Josh is making... There are other choices. For example, Science Tools, back in 1997, faced with the similar but slightly different problem of being a vendor supporting multiple RDBMSes for client data, could have taken the typical choice of managing different code branches for each of the RDBMSes it supports. Instead, we wrote an SQL dialect translator that presently supports five (and soon six) RDBMS platforms - and could probably support all the rest if only someone cared enough to configure them - and this translator is available to customers, not just embeded for the exclusive use of Science Tools' applications. You link your user-application code to our library and you can send it any version of SQL, either statically or dynamically, and it automatically translates into the correct dialect for the database engine you're connected to. It does both DDL and DML and it has command-line tools available, too, so you don't have to link your apps if you don't want to. Presently supported are: Postgres (of course!), Informix, DB2, Sybase, and also Oracle - yes, of course, them, too. (OpenIngres is undergoing testing right now for certification sometime this spring.) Are there things it misses? Yes, but not much. I'll take the wild guess that more than 80% of applications are completely and adequately served. It has pass-through capability so you can still get at engine-specific features, though it does completely side-step stored procedures as these are vastly harder to automate conversion of - we just do the SQL. When calling a DBMS from our library, we handle error recovery, database reconnection, optional DBMS independent journaling and even important aspects of security. When parsing DDL, it (optionally) throws warnings of incompatability, though, as a practical matter, most engines have now removed most of their older limitations that made this vital in their earlier versions. (We support versions of all five since about 1997 and, as there were so many small changes along the way, we provide a configuration mechanism where you can tell it the limitations of your version such as attribute length, maximum length of varchar, etc.) Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PGSQL Locking vs. Oracle's MVCC
RPK wrote: How is PGSQL Locking compared with Oracle's MVCC? How PGSQL handles concurreny and how it differs with Oracle's Multi-Version Concurrency Control (MVCC)? PostgreSQL uses MVCC. http://www.postgresql.org/docs/8.2/static/mvcc.html Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? which is what mySQL does by default :-) The statement CREATE TABLE foo (bar INTEGER NOT NULL, rab VARHCAR(123) NOT NULL, oof DATETIME NOT NULL,); will be rewritten automatically by mySQL to CREATE TABLE foo (bar INTEGER NOT NULL DEFAULT 0, rab VARHCAR(123) NOT NULL DEFAULT '', oof DATETIME NOT NULL DEFAULT '-00-00 00:00'); Maybe if you really want to enforce a NOT NULL constraint in mySQL, you have to declare a column as NOT NULL DEFAULT NULL, explicitly as was suggested somewhere else in this thread. Fascinating how they probably thought that was a good idea. -- Tommy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ODBCng and OpenOffice 2.1
Hannes Dorbath wrote: I tried to connect OpenOffice 2.1 Base (win32) via ODBCng to a PG 8.1.5 database. While it lists schemas, tables and views just fine, I'm unable to edit data. OO always returns: The data content could not be loaded Invalid descritor index SQL Status: 07009 You tried to set a parameter at position 1 but there is/are only 0 parameter(s) allowed. One reason may be that the property ParameterNameSubstitution is not set to TRUE in the data source. Where is the problem? This should be addressed on the odbcng list: http://lists.commandprompt.com/mailman/listinfo/odbcng I actually don't have an answer to your question except to say, I can not duplicate it on OpenOffice for Linux. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Writing oracle/postgress generic SQL
Ben Edwards wrote: Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. I converted a fairly complex data collection application from Oracle to PG about 2 yrs ago. I was pleasantly surprised at how little DML I had to change, and some of it had deeply nested subqueries. Here are the snags I hit: (1) Stored procedures had to be rewritten by hand. You might want to look at EnterpriseDB, as they've added on to PG to enhance Oracle compatibility. (2) I had to change all the stored procedure invocations that used Oracle's call myproc() syntax. If we had used JDBC standard calling conventions, this would not have been necessary. (Just realized you didn't say which language you are using.) (3) Stay away for Oracle proprietary SQL features, like their use of (+) for outer joins. This was a version 8 oddity, and they support standard outer join syntax now. (4) We had significant use of Oracle dblinks in our SQL, and of course that doesn't translate. PG has a dblink capability in contrib, but it is not as complete an implementation as Oracle's. Hope that helps. -- Guy Rouillier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
In that case, the distinction just adds work. In that case you declare the column not null and don't use nulls. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Fri, Feb 23, 2007 at 01:49:06PM +1300, Andrej Ricnik-Bay wrote: On 2/23/07, Jim Nasby [EMAIL PROTECTED] wrote: That depends greatly on what you're doing with it. Generally, as soon as you start throwing a multi-user workload at it, MySQL stops scaling. http://tweakers.net recently did a study on that. I think I recall that wikipedia uses MySQL ... they get quite a few hits, too, I believe. And wikipedia has a massive distributed caching layer the spans the glob (IIRC there's 128 cache machines). I think a better example might be livejournal; the last time I ran the numbers it should have been very reasonable to handle the entire update load with a single database server and add slony slaves for read access as needed. Instead they have a very, very complex system of spreading user load across multiple clusters, etc. Because of that and mysql in general, they've suffered a lot of pain and some lost data. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Ruby on Rails for PostgreSQL
On Fri, Feb 23, 2007 at 01:25:25PM +, Dave Page wrote: Given the recent discussions of applications stacks, PHP Ruby etc. it seems an ideal time for me to introduce a project I've been working on. StackBuilder is an extension of the Windows installer for PostgreSQL that will allow the user to quickly and easily download and install additional software to build the application stack they desire around PostgreSQL. The project includes the StackBuilder wizard as well as a toolkit for building application installers. Whilst the StackBuilder itself is still in development, the first application installer, pgRails, is available for manual download and testing from http://pgfoundry.org/projects/stackbuilder/ pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL connector all preconfigured for use with PostgreSQL on Windows 2000 and above. I'd like to invite anyone who is interested to download and try it out, and report any issues or problems using the project's trackers on pgFoundry. Wow, that's great! Do you think something like this could eventually be used to make pgFoundry projects more 'CPAN-like'? It'd be nice if you could have a project that stated 'I rely on pgFoundry projects foo, bar and baz to function'... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Writing oracle/postgress generic SQL
I've converted stuff from PostgreSQL to Oracle before, and some of the biggest pains were OFFSET ... LIMIT ... in PostgreSQL vs. ROWNUM or ROW_NUMBER in Oracle (depending on version of Oracle, including having to wrap the query with ROWNUM/ROW_NUMBER in a subselect - I greatly prefer OFFSET and LIMIT, especially for web applications), and sequence NEXTVAL syntax. There may be some date type conversion / formatting issues, too. Temporary tables were somewhat different, too, as I recall. You might be able to hide some of the internal differences by creating database-specific views and functions, and using simpler queries from the views at a higher level of the application. Of course, there is that weird Oracle thing where you have to say SELECT from DUAL instead of just SELECT xxx to get simple function return values. Some of the Oracle stuff may differ, depending on version. Susan Cassidy Ben Edwards [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/23/2007 01:27 AM To pgsql-general@postgresql.org cc Subject [GENERAL] Writing oracle/postgress generic SQL Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. Ben -- Ben Edwards - Brussels, Belgium Bristol, UK If you have a problem emailing me use http://www.gurtlush.org.uk/profiles.php?uid=4 (email address this email is sent from may be defunct) ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com --
Re: [GENERAL] postgresql vs mysql
That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Crawford Sent: Friday, February 23, 2007 1:04 PM To: Mark Walker Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql vs mysql Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL on Windows Paper
Hi all; Microsoft has seen it fit to publish a paper I have written as an introduction to PostgreSQL on Windows. This paper covers the basics of installing and configuring the software. I thought it might be of interest here so here is the link: http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx If there are any editorial concerns they can be directed to me. It is my hope that this will help introduce our favorite RDBMS to a wider audience. Best Wishes, Chris Travers PS I hope this is appropriate to mention on -general as well as -advocacy. Since this has both advocacy and practical aspects, I figured I would cross-post. begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-7794 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Glen Parker wrote: Buy the same token, some application have no use whatsoever for the distinction between NULL and ''. In that case, the distinction just adds work. True, I suppose. But if I need that, I can live with a one-time ...not null default ''... addition to my table definition. Or a coalesce(mycolumn, '') if I only need the null to equal '' in specific queries or views. I would love to see different ways to handle NULL implemented by the server. For what I do, NULL could always compare equal to zero and ''. I have no use for NULL in text values. I do need it for numerics, however it doesn't mean unknown, it just means not entered, which is different because I always treat it as zero. If that works for your app, great. But in many (most?) cases it doesn't. A survey, for example, might ask for age or income. Some people will decline to answer one or both of those questions. When someone asks for the average age of respondents, they want exactly what avg() returns - the sum of the non-null ages divided by the count of non-null ages. If the nulls were treated as zeros, the answer could be severely skewed. Cheers, Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
On Thursday 22 February 2007 05:10, Rich Shepard wrote: On Thu, 22 Feb 2007, Tim Tassonis wrote: I do still think it is a bit of an oddity, the concept of the null column. From my experience, it creates more problems than it actually solves and generally forces you to code more rather than less in order to achieve your goals. Tim, Long ago, a lot of database applications used 99, or 999, or -1 to indicate an unknown value. However, those don't fit well with a textual field and they will certainly skew results if used in arithmetic calculations in numeric fields. The concept of NULL representing an unknown value, and therefore one that cannot be compared with any other value including other NULLs, is no different from the concept of zero which was not in mathematics for the longest time until some insightful Arab Indian, the Arabs learned of zero from the Indians. mathematician saw the need for a representation of 'nothing' in arithmetic and higher mathematics. There was probably resistance to that idea, too, as folks tried to wrap their minds around the idea that 'nothing' could be validly represented by a symbol and it was actually necessary to advance beyond what the Greeks and Romans -- and everyone else -- could do. Now, one would be thought a bit strange to question the validity of zero. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. HTH, Rich ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_autovacuum should allow NULL values
I just tried to add something to the pg_autovacuum table for the first time today (with 8.1). I wanted to make the simplest possible entry: Disable auto-vacuuming for a table. However, the data model requires that I also enter values for: vac_base_thresh vac_scale_factor anl_base_thres anl_scale_factor vac_cost_delay vac_cost_limit None of those values matter when vacuuming is disabled for the table! I suggest all these fields be nullable, and default to global values if they are NULL. These are guts and I should have to learn about them or fake them if I just want to disable vacuuming for a table. Likewise, if I just want to set one of the values, I shouldn't have to set /all/ of them if the defaults are otherwise reasonable. For the moment, I suppose I'll go and fake all these values so I can disable a table from Vacuuming. Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL on Windows Paper
On Fri, 2007-02-23 at 12:22, Chris Travers wrote: Hi all; Microsoft has seen it fit to publish a paper I have written as an introduction to PostgreSQL on Windows. This paper covers the basics of installing and configuring the software. I thought it might be of interest here so here is the link: http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx If there are any editorial concerns they can be directed to me. It is my hope that this will help introduce our favorite RDBMS to a wider audience. One point, the paper mentions that you can't run pgsql under an admin account, but I thought that changed with 8.2. Or is that with Vista or something? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL on Windows Paper
Scott Marlowe wrote: On Fri, 2007-02-23 at 12:22, Chris Travers wrote: Hi all; Microsoft has seen it fit to publish a paper I have written as an introduction to PostgreSQL on Windows. This paper covers the basics of installing and configuring the software. I thought it might be of interest here so here is the link: http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx If there are any editorial concerns they can be directed to me. It is my hope that this will help introduce our favorite RDBMS to a wider audience. One point, the paper mentions that you can't run pgsql under an admin account, but I thought that changed with 8.2. Or is that with Vista or something? You can start postgresql under and admin account, but is uses drop privelages or something like that to insure that it doesn't *run* under an admin account. Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_autovacuum should allow NULL values
Mark Stosberg wrote: I just tried to add something to the pg_autovacuum table for the first time today (with 8.1). I wanted to make the simplest possible entry: Disable auto-vacuuming for a table. However, the data model requires that I also enter values for: vac_base_thresh You can use any negative value on these settings (-1 works fine, for example). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] false unique constraint error...for me
you were right, the server uses lc_collateen_US.utf8.
quite amazing situation.
As you mentioned, I replaced the column type (bytea instead of varchar) and
some code (text2code128 now returns bytea instead of text, using decode
function).
I have been able to insert my data without problem
Then, before creating my barcode labels, I use the decode function to change
the binary data to text data, and that's it.
I really appreciated your help.
Have a nice week-end
Gérald
From: Tom Lane [EMAIL PROTECTED]
To: djé djé [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] false unique constraint error...for me Date: Fri, 23
Feb 2007 10:19:07 -0500
=?iso-8859-1?B?ZGrpIGRq6Q==?= [EMAIL PROTECTED] writes:
I get the error : ERROR: duplicate key violates unique constraint
unique_barcode128
But the string returned by text2code128('AB28662097') and
text2code128('AB28662098') are different!!!, i.e., respectively
ÌABÇb4ÅÃÎ
and ÌABÇb4ÆÊÎ.
What locale are you running the server in? It's possible that these
strings are equal according to the locale-specific strcoll() behavior.
In particular, if you are using a locale that expects UTF8, it's pretty
common for strcoll to go nuts when faced with non-UTF8-legal strings.
You might be better off using bytea instead of varchar.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
_
Personnalisez votre Messenger avec Live.com
http://www.windowslive.fr/livecom/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_autovacuum should allow NULL values
On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote: Mark Stosberg wrote: I just tried to add something to the pg_autovacuum table for the first time today (with 8.1). I wanted to make the simplest possible entry: Disable auto-vacuuming for a table. However, the data model requires that I also enter values for: vac_base_thresh You can use any negative value on these settings (-1 works fine, for example). We should really make that the default so that you don't have to worry about other fields... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [pgsql-advocacy] [GENERAL] PostgreSQL on Windows Paper
Joshua D. Drake wrote: Scott Marlowe wrote: On Fri, 2007-02-23 at 12:22, Chris Travers wrote: Hi all; Microsoft has seen it fit to publish a paper I have written as an introduction to PostgreSQL on Windows. This paper covers the basics of installing and configuring the software. I thought it might be of interest here so here is the link: http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx If there are any editorial concerns they can be directed to me. It is my hope that this will help introduce our favorite RDBMS to a wider audience. One point, the paper mentions that you can't run pgsql under an admin account, but I thought that changed with 8.2. Or is that with Vista or something? You can start postgresql under and admin account, but is uses drop privelages or something like that to insure that it doesn't *run* under an admin account. It drops privileges, which is something very much like dropping the root UID in a setuid root Unix program. So in practice you can run Postgres under the admin account, but without the admin privileges. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Steve Crawford schrieb: Mark Walker wrote: I'm not sure what you're trying to do but, it appears that you database design is incorrect. What you need is something like CREATE TABLE temp_readings ( _date Date, temperature double, source varchar(20), ) No reading, no record. Are you suggesting that you would have a weekly set of records for each row? CREATE TABLE temp_readings ( weekstart date, sun double, mon double, tues, double etc ) Not such a great way to do it. Ummm, I'm not trying to make a temperature database. I was responding to the previous poster with an extremely simple example of usefulness of the _concept_ of null. I'm afraid I hadn't considered the possibility that it would be mistaken as an example of an actual table. But since you bring it up, simply omitting rows isn't necessarily an option. A common scenario for weather observation is to take regular snapshots or a bunch of measurements (air-temperature, humidity, wind-speed, soil-temperature, leaf-wetness, UV radiation, etc.) which can easily be represented in a table with a timestamp and a column for each of the measurements. In a modular weather station where a specific instrument can be out of service, one or more of those measurements could be missing (null) for a period of time while the remaining measurements are still being inserted. Well I indeed have such a weather database, taking about 2 minute snapshots of a couple of sensors. If one sensor does not respond or is ignored due to error constraint, I just dont insert a row: timestamp, sensor_id, sensorvalue, errorvalue To do something usefull w/ the data you need to interpolate anyway. Just an example of how you can indeed avoid null values :-) Regards Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql vs mysql
Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? Because I DO need NULLS for non text fields, and I still want NULL to compare equal to, say, '' and 0. I don't think you read what I wrote... Put another way, I would like to redefine NULL to mean BLANK or NOT ENTERED. Totally different concept. -Glen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Priorities for users or queries?
Jim Nasby wrote: The problem with using simple OS priority settings is you leave yourself wide open to priority inversion. Which is why you either (a) note that papers studying priority inversion on RDBMS's find that it's a non issue on many RDBMS workloads; and (except for real-time databases) you tend to still get at least partial benefits even in the face of priority inversions. or (b) use a scheduler in your OS that supports priority inheritance or other mechanisms to avoid priority inversion problems. If you want to use priority inheritance to avoid the priority inversion settings it appears versions of Linux, BSD, Windows, and Solaris at least give you the ability to do so. There is already work being done on a queuing system; take a look at the bizgres archives. Which is cool; but not quite the same as priorities. It seems to me that Bizgres and/or PostgreSQL would not want to re-implement OS features like schedulers. On Feb 20, 2007, at 5:19 PM, Ron Mayer wrote: Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be called for. Perhaps two TODOs? * Use the OS's priority features to prioritize backends (and document that it might work better with OS's that support priority inheritance). * Investigate if postgresql could develop an additional priority mechanism instead of using the OS's. Ron Mayer wrote: Magnus Hagander wrote: ... quite likely to suffer from priority inversion ... CMU paper... tested PostgreSQL (and DB2) on TPC-C and TPC-W ...found that...I/O scheduling through CPU priorities is a big win for postgresql. http://www.cs.cmu.edu/~bianca/icde04.pdf Setting priorities seems a rather common request, supposedly coming up every couple months [5]. The paper referenced [1] suggests that even with naive schedulers, use of CPU priorities is very effective for CPU and I/O intensive PostgreSQL workloads. If someone eventually finds a workload that does suffer worse performance due to priority inversion, (a) they could switch to an OS and scheduler that supports priority inheritance; (b) it'd be an interesting case for a paper rebutting the CMU one; and (c) they don't have to use priorities. If a user does find he wants priority inheritance it seems Linux[1], BSD[2], some flavors of Windows[3], and Solaris[4] all seem to be options; even though I've only seen PostgreSQL specifically tested for priority inversion problems with Linux (which did not find problems but found additional benefit of using priority inheritance). [1] Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf [2] BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html [3] Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx [4] Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ [5] Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
That's absolutely correct. What I want is a totally non standard *optional* extension, recognizing that many, even if not most, applications could benefit from it. I think there's a clean way to do it. I would never ask for such a thing if I thought it would effect an out of the box installation. -Glen If that works for your app, great. But in many (most?) cases it doesn't. A survey, for example, might ask for age or income. Some people will decline to answer one or both of those questions. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
Glen Parker schrieb: Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? Because I DO need NULLS for non text fields, and I still want NULL to compare equal to, say, '' and 0. I don't think you read what I wrote... Put another way, I would like to redefine NULL to mean BLANK or NOT ENTERED. Totally different concept. Not wise concept, but here you go: WHERE coalesce(sometimesnull,'') = '' or WHERE coalesce(sometimesnull,0 ) = 0 ... Regards Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Password issue revisited
I assume this is not a TODO. --- Magnus Hagander wrote: The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. I believe that .pgpass on *nix won't be used if it is readable by anyone except the current user. No, root can always read it. On unix, there is one root. On windows, the concept of administrator is less clear. From the docs - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) I would think that if they are using FAT filesystem (which is only partially supported for developers benefit) then they can't use pgpass. If they are using FAT, the obviously don't care about the security of the system anyway, so it's not a problem, IMHO. So we only have to care about people who use NTFS. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? I would say the same as the *nix version - if it is readable or writable by anyone except the current user it is potentially at risk, the current user connecting to pgsql is the only use for this file. Which I believe is the whole point of the TODO entry, stop anyone using the pgpass file without proper security. Again, it's a lot harder to actually define it on Windows. What if your user has access only through a group? What about DENY permissions. Things like that. The other thing to consider is that pgpass is the file referenced by PGPASSFILE - the user can set this to point to a file anywhere on any drive available. That's a very valid point though, didn't think about that. Still doesn't take away the how part, though, but it does take away part of the why part. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Ruby on Rails for PostgreSQL
Jim C. Nasby wrote: On Fri, Feb 23, 2007 at 01:25:25PM +, Dave Page wrote: I'd like to invite anyone who is interested to download and try it out, and report any issues or problems using the project's trackers on pgFoundry. Wow, that's great! Thanks! Do you think something like this could eventually be used to make pgFoundry projects more 'CPAN-like'? It'd be nice if you could have a project that stated 'I rely on pgFoundry projects foo, bar and baz to function'... Yes, code to handle dependency hierarchys and subsequent ordered installations is already done. I'd estimate the download/installation wizard to be about 2/3rds complete at the moment. We also have plans for additional packages that would give us a dependency tree to test. In addition, the design (and the wizard code) has been written with future cross platform support in mind - it shouldn't take much effort to add RPM packages for example. There is a rough project spec on the downloads area of the pgFoundry site if you want to read more. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Restore After Changing Table Structures
Hello List! We're working on restructuring some of our tables in our databases (removing, renaming, /or adding columns to tables), adding tables to the database, etc. - and we have a database FULL of info that we want to restore this database with once the updates are done; however, I know that I cannot simply do a 'Restore' to my database if I'm removing/renaming columns, etc since some of the column names that existed in the database that we did the Backup on will not exist in the one that we attempt to do the Restore on... What is the most efficient way to copy/restore data from my old database into my new one taking these changes into consideration? (pgAdmin sure does spoil us, doesn't it?! ;)) We're running Postgres version 8.0.8. Thanks in advance for your time and help with this!! -Jeanna ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Priorities for users or queries?
My problem with [1] is that even for 10 users the percentage of time spent in locks is very high. Can priorities scale? Benjamin Ron Mayer wrote: Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be called for. Perhaps two TODOs? * Use the OS's priority features to prioritize backends (and document that it might work better with OS's that support priority inheritance). * Investigate if postgresql could develop an additional priority mechanism instead of using the OS's. Ron Mayer wrote: Magnus Hagander wrote: ... quite likely to suffer from priority inversion ... CMU paper... tested PostgreSQL (and DB2) on TPC-C and TPC-W ...found that...I/O scheduling through CPU priorities is a big win for postgresql. http://www.cs.cmu.edu/~bianca/icde04.pdf Setting priorities seems a rather common request, supposedly coming up every couple months [5]. The paper referenced [1] suggests that even with naive schedulers, use of CPU priorities is very effective for CPU and I/O intensive PostgreSQL workloads. If someone eventually finds a workload that does suffer worse performance due to priority inversion, (a) they could switch to an OS and scheduler that supports priority inheritance; (b) it'd be an interesting case for a paper rebutting the CMU one; and (c) they don't have to use priorities. If a user does find he wants priority inheritance it seems Linux[1], BSD[2], some flavors of Windows[3], and Solaris[4] all seem to be options; even though I've only seen PostgreSQL specifically tested for priority inversion problems with Linux (which did not find problems but found additional benefit of using priority inheritance). [1] Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf [2] BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html [3] Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx [4] Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ [5] Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_autovacuum should allow NULL values
Jim C. Nasby wrote: On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote: Mark Stosberg wrote: I just tried to add something to the pg_autovacuum table for the first time today (with 8.1). I wanted to make the simplest possible entry: Disable auto-vacuuming for a table. However, the data model requires that I also enter values for: vac_base_thresh You can use any negative value on these settings (-1 works fine, for example). We should really make that the default so that you don't have to worry about other fields... A default would be helpful, but I think NULL is a lot more intuitive as a placeholder don't know/ don't care, than -1 is. Adding a default of -1 seems like a more cumbersome way to express the same thing to me. Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] db stats vs table stats
I've been periodically collecting the stats stored in pg_statio_all_tables and pg_stat_database for ~30 different clusters, and have noticed a curiosity. I would have thought that for a given period, the change in pg_stat_database.blks_read would be = the sum of the changes in pg_statio_user_tables.heap_blks_read + pg_statio_user_tables.idx_blks_read + pg_statio_user_tables.toast_blks_read + pg_statio_user_tables.tidx_blks_read. In short, the total would be = heap + idx + toast + idx for user tables. It does not appear that way. The table-level IO stats appear to be typically 1-2 orders of magnitude larger than the db-level stats. Can anyone explain that? TIA. Ed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_autovacuum should allow NULL values
Mark Stosberg wrote: Jim C. Nasby wrote: On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote: Mark Stosberg wrote: I just tried to add something to the pg_autovacuum table for the first time today (with 8.1). I wanted to make the simplest possible entry: Disable auto-vacuuming for a table. However, the data model requires that I also enter values for: vac_base_thresh You can use any negative value on these settings (-1 works fine, for example). We should really make that the default so that you don't have to worry about other fields... A default would be helpful, but I think NULL is a lot more intuitive as a placeholder don't know/ don't care, than -1 is. Adding a default of -1 seems like a more cumbersome way to express the same thing to me. To be frank, I don't remember what the rationale was for not using NULLs. Simplicity of code, I guess. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 5 Weeks till feature freeze or (do you know where your patch is?)
Hello, 5 weeks to feature freeze folks. Please provide updates including if you think you will have a patch submitted before feature freeze. Be realistic, if you can't make it -- say so. Alvaro Herrera: Autovacuum improvements (maintenance window etc..) Gavin Sherry: Bitmap Indexes (on disk), possible basic Window functions Greg Stark: WITH/Recursive Queries? Andrei Kovalesvki: Some Win32 work with Magnus Magnus Hagander: VC++ support (thank goodness) Heikki Linnakangas: Vacuum for Bitmap Indexes, Group Index Tuples Oleg Bartunov: Tsearch2 in core Neil Conway: Patch Review (including enums), pg_fcache PeterE: XML ITAGAKI Takahiro: Dead space map, load distributed checkpoints Stephen Frost: Default permission per object/schema Tom Lane: Cost based functions, operator overhaul, Plan Invalidation? Simon Riggs: HOT ( you know he just is ) Pavan Deolasee: HOT ( never met him ) Teodor Sigaev: Tsearch2 in core (with Oleg) Jeff Davis: Synchronized scanning Henry Hotz: GSSAPI (with Magnus) Andrew Dunstan: Something with COPY? Andrew? David Fetter: Arrays of compound types Looking for updates on Updateable views. Anyone? Bueller? Vertical projects: Pavel Stehule: PLpsm Alexey Klyukin: PLphp Andrei Kovalesvki: ODBCng Neil Conway: pgmemcache Josh Drake: pgmemcache This close to feature freeze it is really time for people to get pounding on patch review!!! Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Re: [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
Joshua D. Drake wrote: Andrew Dunstan: Something with COPY? Andrew? The only thing I can think of is to remove the support for ancient COPY syntax from psql's \copy, as suggested here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg01078.php That's hardly a feature - more a matter of tidying up. Neil Conway: pgmemcache Josh Drake: pgmemcache what does this refer to? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Re: [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
Neil Conway: pgmemcache Josh Drake: pgmemcache what does this refer to? Neil is cleaning up the code, I am cleaning up the docs. Joshua D. Drake cheers andrew -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Re: [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
The only thing I can think of is to remove the support for ancient COPY syntax from psql's \copy, as suggested here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg01078.php That's hardly a feature - more a matter of tidying up. I thought you were being sponsored for something? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/23/07, Bill Moran [EMAIL PROTECTED] wrote: In any case if anyone is interested I was able to reproduce the changes that wikipgedia made and applied those changes (as well as others) all the way up to the 1.6.10 codebase. The only reason I mention this is because 1.6is the only choice for PHP4 users. If anyone is interested I can provide the codebase, the schema still has to be created manually as was the case with wikipgedia. I would be interested. I'm probably expected to maintain this thing ... You can download it from: http://www.postgresqlforums.com/downloads/pgmediawiki-1.6.10.tar.gz Again, like wikipgedia you have to create a schema (manually) named mediawiki and like wikipgedia (because the port more or less used some of the same mods they made) MySQL support is probably broken.
Re: [GENERAL] postgresql vs mysql
Brandon Aiken wrote: That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. No, you put them all on one table and put nulls in places where no data is available. With real database systems, there's no excuse not to. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
It cannot already do what I want, unless you blatantly ignore what I wrote. Putting coalesce() calls *everywhere* counts as more work, don't you agree? -Glen Ben wrote: But, why do you need an extension when the existing system can already do what you want? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Writing oracle/postgress generic SQL
On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote: On Fri, 23 Feb 2007, David Fetter wrote: On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote: Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later. You've just bumped into the problem that while standard SQL exists, only Mimer and possibly DB2 implement it. The presentation below outlines your main choices for supporting more than one DB back-end, and they're all expensive and troublesome to maintain. http://www.powerpostgresql.com/Downloads/database_depends_public.swf With all due respect to Josh's presentation, there's a lot more to the story than those couple of slides. With all due respect, the presentation was if anything an understatement. Unless, as with rare beasties like Science Tools, the major purpose of the application is to support multiple DBMS back-ends, it's just too expensive. Even in those rare cases, it's expensive. [sales pitch elided ;)] Are there things it misses? Yes, but not much. I'll take the wild guess that more than 80% of applications are completely and adequately served. That says something about the applications you've seen, and not about the adequacy of such a library. What point is there in using a powerful tool like an RDBMS and then hobbling yourself by only using 10% of the available features? It's certainly a bad thing to do by default. It has pass-through capability so you can still get at engine-specific features, though it does completely side-step stored procedures Oops! There went 60% of the code in some of the databases I've seen in production. 80% in at least one case I've seen in the past year. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
Joshua D. Drake [EMAIL PROTECTED] writes: Greg Stark: WITH/Recursive Queries? Uhm, I posted two weeks ago saying I had to shelve that temporarily. On the other hand I've submitted a patch to reduce the storage overhead of varlenas under 128 bytes by 3-7 bytes each. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Re: [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Greg Stark: WITH/Recursive Queries? Uhm, I posted two weeks ago saying I had to shelve that temporarily. I can't read every email :) Can someone pick this up? This would be the second time that this has been dropped. Anyone? On the other hand I've submitted a patch to reduce the storage overhead of varlenas under 128 bytes by 3-7 bytes each. Cool! Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 23, 2007 at 09:39:52AM -0500, Robert Haas wrote: Actually, what would be really nice is if there were just a button I could push that would make all of my data automatically correct. Can that go into 8.3? Thanks, ...Robert Oh, no problem. Just compile with -ldwim ;) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] db stats vs table stats
Oops, typo: I reversed the inequality. I've corrected it below. On Friday February 23 2007 2:02 pm, Ed L. wrote: I've been periodically collecting the stats stored in pg_statio_all_tables and pg_stat_database for ~30 different clusters, and have noticed a curiosity. I would have thought that for a given period, the change in pg_stat_database.blks_read would be = the sum of the changes in pg_statio_user_tables.heap_blks_read + pg_statio_user_tables.idx_blks_read + pg_statio_user_tables.toast_blks_read + pg_statio_user_tables.tidx_blks_read. In short, the total would be = heap + idx + toast + idx for user tables. It does not appear that way. The table-level IO stats appear to be typically 1-2 orders of magnitude larger than the db-level stats. Can anyone explain that? TIA. Ed ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] urgent: upgraded to 8.2, getting kernel panics
Ok, This may the wrong place to look for answers to this, but I figured it couldn't hurt...so here goes: On friday we upgraded a critical backend server to postgresql 8.2 running on fedora core 4. Since then we have received three kernel panics during periods of moderate to high load (twice during the pg_dump backup run). Platform is IBM x360 series running SCSI, software raid on the backplane. After the first crash we yum updated the system which obviously did not fix the problem. I was leaning hardware problem until this last time and I was able to catch the following off the terminal: BUG: spinlock recursion CPU0 postmaster...not tainted. bunch of other stuff ending in: Kernel Panic: not syncing: Bad locking One of the other developers snapped a picture of the kernel panic with his digital camera and is going to send over the pictures when he gets home this evening. Has anybody seen any problem like this or have any suggestions about possible resolution...should I be posting to the LKML? Any suggestions are welcome and appreciated. At this juncture we are going to downgrade the postmaster back to 8.1 and see if that fixes the panics. If it doesn't this discussion is over but if it does we are extremely curious about looking for a fix for this issue...we have about 8 weeks of development that is on hold until we can put a 8.2 server in production. Management has already authorized a new server but they want a 100% guarantee this is going to fix the problem. thanks in advance, merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Priorities for users or queries?
Benjamin Arai wrote: My problem with [1] is that even for 10 users the percentage of time spent in locks is very high. Really? In the paper referenced in the thread you quoted, figure 1H shows TCP-C with PostgreSQL and shows that time spent in locks with 10 users is extremely small (about 10% of time in locks with 5 warehouses and near 0% at 30 warehouses). This is in contrast with DB2 which shows about 80% time in locks with 5 warehouses and ten clients. Perhaps you were thinking DB2? With TCP-W, neither PostgreSQL nor DB2 shows any significant time spent in locks with 12 clients. Can priorities scale? The PostgreSQL-priority-mechanisms paper referenced in this thread used TPC-C using 500MB - 3GB databases with 10 warehouses and from 1 to 300 Clients and TPC-W with 150MB and between 12 and 150 clients. So I'd say yes, it scales to meet most needs. Benjamin Ron Mayer wrote: Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be called for. Perhaps two TODOs? * Use the OS's priority features to prioritize backends (and document that it might work better with OS's that support priority inheritance). * Investigate if postgresql could develop an additional priority mechanism instead of using the OS's. Ron Mayer wrote: Magnus Hagander wrote: ... quite likely to suffer from priority inversion ... CMU paper... tested PostgreSQL (and DB2) on TPC-C and TPC-W ...found that...I/O scheduling through CPU priorities is a big win for postgresql. http://www.cs.cmu.edu/~bianca/icde04.pdf Setting priorities seems a rather common request, supposedly coming up every couple months [5]. The paper referenced [1] suggests that even with naive schedulers, use of CPU priorities is very effective for CPU and I/O intensive PostgreSQL workloads. If someone eventually finds a workload that does suffer worse performance due to priority inversion, (a) they could switch to an OS and scheduler that supports priority inheritance; (b) it'd be an interesting case for a paper rebutting the CMU one; and (c) they don't have to use priorities. If a user does find he wants priority inheritance it seems Linux[1], BSD[2], some flavors of Windows[3], and Solaris[4] all seem to be options; even though I've only seen PostgreSQL specifically tested for priority inversion problems with Linux (which did not find problems but found additional benefit of using priority inheritance). [1] Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf [2] BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html [3] Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx [4] Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ [5] Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/23/07 15:47, Peter Eisentraut wrote: Brandon Aiken wrote: That's why you make a table for every device or every measurement, and then use a view to consolidate it. With updatable views, there's no excuse not to. No, you put them all on one table and put nulls in places where no data is available. With real database systems, there's no excuse not to. Each of the daily/hourly/etc temperature readings are independent. Therefore they should each have their own row in the meteorology readings table. I *think* that breaks 3NF. This should be 3NF: CREATE TABLE T_READING_TYPE ( READING_CODECHAR(4) PRIMARY KEY, READING_DESCRIP TEXT ); CREATE TABLE T_MET_READINGS ( _DATE DATE, _HOUR SMALLINT CHECK (HOUR BETWEEN 0 AND 23), READING_CODE CHAR(4) REFERENCES T_READING_TYPE(READING_CODE), READING_VALUE NUMERIC(8,3), PRIMARY KEY (_DATE, _HOUR) ); -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF32j3S9HxQb37XmcRAgsgAKC7m74VtyU5rnOI0gF2VXjHxk9kXgCfVY86 i5hgysDkC7EUJWlbGL+vyZM= =RN+L -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 5 Weeks till feature freeze or (do you know where your patch is?)
On Fri, 2007-02-23 at 13:24 -0800, Joshua D. Drake wrote: Jeff Davis: Synchronized scanning I am still on target. I'm scheduling some benchmarks on real hardware and real queries in the next week or two. If those show the results I expect, I'll be ready before feature freeze. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] urgent: upgraded to 8.2, getting kernel panics
On Fri, 2007-02-23 at 17:14 -0500, Merlin Moncure wrote: BUG: spinlock recursion CPU0 postmaster...not tainted. snip Has anybody seen any problem like this or have any suggestions about possible resolution...should I be posting to the LKML? AFAIR (+ some quick Googling), this is related to a problem in kernel. You may need to update to a newer Fedora release since FC4 is not supported anymore :(. Even if you report to LKML, they will probably suggest you using a newer kernel. However, I think system will not let you compile a new kernel and panic again during a high load... So... If you have a free space, install a newer Fedora release on this system, mount the existing $PGDATA and try if this fixes the problem... -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Priorities for users or queries?
On Fri, Feb 23, 2007 at 12:07:56PM -0800, Ron Mayer wrote: Jim Nasby wrote: The problem with using simple OS priority settings is you leave yourself wide open to priority inversion. Which is why you either (a) note that papers studying priority inversion on RDBMS's find that it's a non issue on many RDBMS workloads; and (except for real-time databases) you tend to still get at least partial benefits even in the face of priority inversions. or (b) use a scheduler in your OS that supports priority inheritance or other mechanisms to avoid priority inversion problems. If you want to use priority inheritance to avoid the priority inversion settings it appears versions of Linux, BSD, Windows, and Solaris at least give you the ability to do so. There is already work being done on a queuing system; take a look at the bizgres archives. Which is cool; but not quite the same as priorities. It seems to me that Bizgres and/or PostgreSQL would not want to re-implement OS features like schedulers. Actually, I believe part of the discussion also involved how to handle long-running workloads that you don't want to monopolize the machine. -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [GENERAL] 5 Weeks till feature freeze or (do you know where your patch is?)
Jeff, I am still on target. I'm scheduling some benchmarks on real hardware and real queries in the next week or two. If those show the results I expect, I'll be ready before feature freeze. Send me a patch against 8.2.3 and I'll pass it to the Sun benchmarking team. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql vs mysql
Ben wrote: What I read was that you have no use for NULLs, and that they're equivilant to zero or an empty string or some other known value. Sorry if I misunderstood that. Equivalent, yes, because NULL doesn't usually mean UNKNOWN in this system, just NOT ENTERED. I do still have use for NULL in data types that don't inherently have a blank value (numerics, dates, etc.) I can and do solve the problem by simply not using NULL in character fields, and by the rather gratuitous use of coalesce() in queries. The problem is, it places a burden on people doing ad hoc queries who, because of the type of data they work with, have no reason to understand the concept of NULL as it exists in standard SQL. These aren't computer scientists, they are accountants and managers. The result is queries that either return bad data, or that appear much more complex than should be required to people who can't see why NULL == zero is NULL. And as I said, I really don't know what a fully functional solution would look like, I just know that it would be useful to a large cross section of users. -Glen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Ron Johnson wrote: Each of the daily/hourly/etc temperature readings are independent. Therefore they should each have their own row in the meteorology readings table. I *think* that breaks 3NF. If everything is, as you say, independent, then there can be no 3NF violation, because that only happens when you have functional dependencies within a table. The question that you raise is more a matter of deciding which aspects of a problem are data and which are data structure. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
On Fri, Feb 23, 2007 at 02:50:48PM -0800, Glen Parker wrote: I can and do solve the problem by simply not using NULL in character fields, and by the rather gratuitous use of coalesce() in queries. I'm confused. If you don't use NULLs then you don't need coalesce either. The problem is, it places a burden on people doing ad hoc queries who, because of the type of data they work with, have no reason to understand the concept of NULL as it exists in standard SQL. These aren't computer scientists, they are accountants and managers. The result is queries that either return bad data, or that appear much more complex than should be required to people who can't see why NULL == zero is NULL. Is it really that hard to understand that UNKNOWN == zero is UNKNOWN? And again, if NULL is confusing on your systems, don't use it. They don't appear out of nowhere. Outer joins are really the only place you can't avoid them. And as I said, I really don't know what a fully functional solution would look like, I just know that it would be useful to a large cross section of users. Useful, maybe. Confusing, absolutly. I'm just wondering how it would interact with foreign keys for example. Different people can't have different ideas about '' = NULL, else you'd get constraints that are violated depending on who's looking. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_autovacuum should allow NULL values
Alvaro Herrera [EMAIL PROTECTED] writes: Mark Stosberg wrote: Adding a default of -1 seems like a more cumbersome way to express the same thing to me. To be frank, I don't remember what the rationale was for not using NULLs. Simplicity of code, I guess. We tend to avoid allowing fixed-size fields to be NULL in the system catalogs, because it prevents using the technique of overlaying C structs onto the catalog tuples. In fact, if you wanted to have any null fields in pg_autovacuum, you would need to find a way to prevent initdb from enforcing that policy: regression=# \d pg_autovacuum Table pg_catalog.pg_autovacuum Column | Type | Modifiers --+-+--- vacrelid | oid | not null enabled | boolean | not null vac_base_thresh | integer | not null vac_scale_factor | real| not null anl_base_thresh | integer | not null anl_scale_factor | real| not null vac_cost_delay | integer | not null vac_cost_limit | integer | not null freeze_min_age | integer | not null freeze_max_age | integer | not null Indexes: pg_autovacuum_vacrelid_index UNIQUE, btree (vacrelid) I don't find this particularly important, because we have never intended direct update of catalog entries to be a primary way of interacting with the system. The current pg_autovacuum setup is a stopgap until the dust has settled enough that we know what sort of long-term API we want for autovacuum. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] db stats vs table stats
On Friday February 23 2007 3:06 pm, Ed L. wrote: I've been periodically collecting the stats stored in pg_statio_all_tables and pg_stat_database for ~30 different clusters, and have noticed a curiosity... The table-level IO stats appear to be typically 1-2 orders of magnitude larger than the db-level stats. Can anyone explain that? Here's an example of how I'm calculating the deltas. Perhaps someone can spot an error or mistaken assumption. In this case, the deltas are not orders of magnitude out of sync with each other, but they grew from about 3% out of sync to 45% out of sync in ~35 minutes on a DB with 500 transactions/ second. drop table s; create table s as select now(), blks_read as db_blks_read, sum(case when heap_blks_read ISNULL then 0 else heap_blks_read end + case when idx_blks_read ISNULL then 0 else idx_blks_read end + case when toast_blks_read ISNULL then 0 else toast_blks_read end + case when tidx_blks_read ISNULL then 0 else tidx_blks_read end) as table_blks_read from pg_stat_database sd, pg_database d, pg_class c, pg_statio_all_tables st where sd.datname = d.datname and d.datname = current_database() and c.oid = st.relid group by blks_read; create or replace view delta_view as select now() - s.now as delta, blks_read - s.db_blks_read as db_blks_read_delta, sum(case when heap_blks_read ISNULL then 0 else heap_blks_read end + case when idx_blks_read ISNULL then 0 else idx_blks_read end + case when toast_blks_read ISNULL then 0 else toast_blks_read end + case when tidx_blks_read ISNULL then 0 else tidx_blks_read end) - s.table_blks_read as table_blks_read_delta from pg_stat_database sd, pg_database d, pg_class c, pg_statio_all_tables st, s where sd.datname = d.datname and d.datname = current_database() and c.oid = st.relid group by blks_read, s.now, db_blks_read, table_blks_read; select * from delta_view; delta | db_blks_read_delta | table_blks_read_delta -++--- 00:32:51.007703 | 384243 |556212 (1 row) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] complex referential integrity constraints
to attack eachother. Depending on what you're modelling, even this could be too simple -- for example, while a single wolf is unlikely to attack a lion, a pack of wolves have a lot more probability of doing so. Do you keep packs of wolves in your barn? If so, watch your lions. Well from the previous thread that discussed the use of the animal table and sub-set tables prey and preditor, if a preditor can attach a prey item or preditor item, then a table relation only needs to be created between preditor and animal. This way only preditors can attack, but they can attach any other animal preditor or prey. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql vs mysql
That's why you make a table for every device or every measurement, and then use a view to consolidate it. With update-able views, there's no excuse not to. I would be interested on here some of your experiences on this? I've built and made use of table hierarchies three levels deep and about twenty classification types wide that I rolled up into separate update-able view. However, I found the process of cascading the updates to all three levels of each classification type using the TID rather tedious. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ruby on Rails for PostgreSQL
On 2/24/07, Dave Page [EMAIL PROTECTED] wrote: pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL connector all preconfigured for use with PostgreSQL on Windows 2000 and above. How easy would that be to integrate on Linux? Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics
Merlin Moncure [EMAIL PROTECTED] writes: On friday we upgraded a critical backend server to postgresql 8.2 running on fedora core 4. Umm ... why that particular choice of OS? Red Hat dropped update support for FC4 some time ago, and AFAIK the Fedora Legacy project is not getting things done. How old is the kernel you're using? At this juncture we are going to downgrade the postmaster back to 8.1 and see if that fixes the panics. Even assuming that Postgres is related to the panics, I don't think you will find anyone maintaining that a kernel panic is not the kernel's problem. If an application *is* able to provoke a kernel panic, the standard description of the problem would be critical kernel security flaw. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
