Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Purusothaman A wrote: Richard Huxton, Thanks for your detailed reply. I am maintaining various database of same kind in postgresql. Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xx; in 5 databases. I have used '\o e:\\filename.xml' before executing query and inspected the output in that file. Kindly look at the end of line in all 5 outputs. First 3 output shows few missing characters. But last 2 output is the perfect one. 1. Output of SFRS2 database: 2. Output of SFRS1 database: 3. Output of FASP_AVT database: Let's have a look at the last few bytes of these three rows. For example, below is a very short file that finishes with endNL. SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject WHERE loid=340161 AND pageno=0; loid | pageno | length | encode +++ 340161 | 0 | 31 | 54686973206973206120746573742066696c650a6c696e6520320a656e640a (1 row) It doesn't seem to be random garbage at the end of your rows, so lets see what it is. Oh - things we can rule out: 1. Index corruption. We're not missing the last row. Adding the length check above will show us if the row is truncated or full of spaces. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] the future of pljava development
Guy Rouillier pisze: Nothing productive will come of a language debate; let's just say there are things you can do with a complete programming language at your disposal that you can't do with PL/pgsql. I use Java in the database, Exactly - sometimes we need to write something more complicated than simple trigger. and the current PL/Java works very well. I know C and Java well, and would be willing to contribute to keeping PL/Java going. I hadn't heard Great to hear that, please contact with Thomas, lets try to make a roadmap what need to be done. Best wishes, Marek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] the future of pljava development
Dave Page pisze: Joshua D. Drake wrote: Now this I agree with. I am sure postgresql hackers are happy to help the pljava team get things where they need to be, but -hackers needs to be focusing on releasing 8.3. pl/java is actually one of the problems that pgInstaller is currently facing. In a nutshell, if we don't get a release for 8.3 before we go to beta, we'll probably have to pull it form the distribution which would be a shame. We have a few weeks in order to have it ready for 8.3 and because of that I've started this discussion. I hope that we will be able to make pljava ready for 8.3 and continue the development. From windows point of view this is really great feature to have pljava inside the pgInstaller (I'm using it on my laptop) so for sure we need to make it ready for 8.3. Best wishes, Marek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Get the exeption error description
Hi, Is there a way to know the error description / message in a BEGIN END block in a plpgsql functioin as in the 'Appendix A. PostgreSQL Error Codes' ? I would like to pass it back to the function caller DECLARE the_error varchar; BEGIN .. ... ... .. ... ... EXCEPTION WHEN OTHERS THEN the_error := ERROR DESCRIPTION; ? END; Thanks, Paolo
Re: [GENERAL] the future of pljava development
Thomas Kellerer pisze: Marek Lewczuk wrote on 23.05.2007 21:37: I would like to know your opinion about pljava and its future I have never used Java-in-the-database with any of the DBMS I have used and I have never understood the reasoning behind it. My personal opinion is, that it's not needed. Most of the time the same code can run just as efficiently inside the application (server) as well. Not entirely true - it all depends on the type of the application. My database is quite large - hundreds of tables, tons of data - many things can be done within the database and it helps making things easier. If you have a simple database then you probably won't need pljava - if you need a trigger then you can write it in plpgsql, however when we talk about very complicated things then pljava (or any other more advanced language) can make your live much easier and things can be done much faster. Sometimes database need to be extended by some external library - for example json decoding/encoding (just an example...) and having pljava makes it easier for Java developers to do it. PG already has a very good language for stored procedure and I think that is enough. Don't get me wrong: I'm a long time Java developer and architect. As I said: it all depends. I'm not saying that I would write all triggers in Java, but ability to use huge number of Java libraries inside PostgreSQL is great thing. Regards, Marek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] the future of pljava development
Hi, This sounds great. Sorry for not being responsive on the pgsql-general. I'll read up on this thread during the weekend. Short term, this is what I think needs to be done: 1. Create a PL/Java 1.4 from current CVS. It compiles and runs with PostgreSQL 8.2. 2. Do whatever it takes to make PL/Java run with 8.3. 3. When 8.3 is released, verify that everything is really ok, then a 1.5 release should come out. After that, there are improvements in the Java domain. We should support Java 1.5 for instance. 1.4 is soon starting its End Of Life cycle. Would you like to become committers to the PL/Java project? Regards, Thomas Hallgren Marek Lewczuk wrote: Guy Rouillier pisze: Nothing productive will come of a language debate; let's just say there are things you can do with a complete programming language at your disposal that you can't do with PL/pgsql. I use Java in the database, Exactly - sometimes we need to write something more complicated than simple trigger. and the current PL/Java works very well. I know C and Java well, and would be willing to contribute to keeping PL/Java going. I hadn't heard Great to hear that, please contact with Thomas, lets try to make a roadmap what need to be done. Best wishes, Marek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Richard Huxton, In my system also its 2048 bytes chunk. The below output shows clearly that the last chunk differs in its length. You might have noticed in my previous mail that the string /haarcascade_frontalface_default\015\012/opencv_storage\015\012 is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. Have a look at it, In this mail I have bolded the corrucpted part. sfrs2= select loid, pageno, length(data) from pg_largeobject where loid = 101177 and pageno = 630; loid | pageno | length ++ 101177 |630 |181 (1 row) sfrs1= select loid, pageno, length(data) from pg_largeobject where loid = 41642 and pageno = 630; loid | pageno | length ---++ 41642 |630 |193 (1 row) fasp_avt= select loid, pageno, length(data) from pg_largeobject where loid = 101800 and pageno = 630; loid | pageno | length ++ 101800 |630 |193 (1 row) sfrs= select loid, pageno, length(data) from pg_largeobject where loid = 24038 and pageno = 630; loid | pageno | length ---++ 24038 |630 |205 (1 row) fasp_test= select loid, pageno, length(data) from pg_largeobject where loid = 106310 and pageno = 630; loid | pageno | length ++ 106310 |630 |205 (1 row) 1. Output of SFRS2 database: 101177 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages/haarcascade_frontalface_defau (631 rows) 2. Output of SFRS1 database: - 41642 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668 /stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/openc (631 rows) 3. Output of FASP_AVT database: -- 101800 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/openc (631 rows) 4. Output of SFRS database: (not yet corrupted) -- 24038 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/opencv_storage\015\012 (631 rows) 5. Output of FASP_TEST database: (Not yet corrupted) 106310 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/opencv_storage\015\012 (631 rows) Have you noticed the bolded strings? What could have caused to damage that part? Is it bug in Posstgresql or is they any way to solve this problem. Thanks. Regards, Purusothaman A On 5/24/07, Richard Huxton [EMAIL PROTECTED] wrote: Purusothaman A wrote: Richard Huxton, Thanks for your detailed reply. I am maintaining various database of same kind in postgresql. Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xx; in 5 databases. I have used '\o e:\\filename.xml' before executing query and inspected the output in that file. Kindly look at the end of line in all 5 outputs. First 3 output shows few missing characters. But last 2 output is the perfect one. 1. Output of SFRS2 database: 2. Output of SFRS1 database: 3. Output of FASP_AVT database: Let's have a look at the last few bytes of these three rows. For example, below is a very short file that finishes with endNL. SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject WHERE loid=340161 AND pageno=0; loid | pageno | length | encode +++ 340161 | 0 | 31 | 54686973206973206120746573742066696c650a6c696e6520320a656e640a (1 row) It doesn't seem to be random garbage at the end of your rows, so lets see what it is. Oh - things we can rule out: 1. Index corruption. We're not missing the last row. Adding the length check above will show us if the row is truncated or full of spaces. -- Richard Huxton Archonet Ltd -- http://PurusothamanA.wordpress.com/
[GENERAL] why postgresql over other RDBMS
Hi I was wondering, apart from extensive procedural language support and being free, what are other major advantages of Postgresql over other major RDBMS like oracle and sql server. Any pointers would be highly appreciated. Thanks, ~Jas
Re: [GENERAL] Get the exeption error description
Is there a way to know the error description / message in a BEGIN END block in a plpgsql functioin as in the 'Appendix A. PostgreSQL Error Codes' ? The variable SQLERRM as described in http://www.postgresql.org/docs/current/static/plpgsql-control-structures .html#PLPGSQL-ERROR-TRAPPING Yours, Laurenz Albe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Purusothaman A wrote: Richard Huxton, In my system also its 2048 bytes chunk. The below output shows clearly that the last chunk differs in its length. You might have noticed in my previous mail that the string /haarcascade_frontalface_default\015\012/opencv_storage\015\012 is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. Have a look at it, In this mail I have bolded the corrucpted part. Yep, spotted that. Hence asking for the length, and it looks like... loid | pageno | length ++ 101177 |630 |181 41642 |630 |193 101800 |630 |193 24038 |630 |205 The data has just been truncated rather than corrupted. Is it bug in Posstgresql or is they any way to solve this problem. Well, something is setting the length too short on these entries. Can you tell me whether the following statements are all correct? 1. Each database is on a separate machine (that would rule out a hardware problem) 2. All systems are running on Windows 2000/XP/2003. 3. All systems are version 8.2.4 (if not, please give details) 4. You upload the data with lo_import (once) and download it with lo_export (many times) and don't alter it in-between. 5. Where the data has been truncated, you know for a fact you downloaded it OK before (or do you just suspect it was OK?) If you're not changing the data, and you know it was OK at some point then there are only two things I can think of: 1. A hardware problem (which we might rule out above) 2. A bug in PostgreSQL's vacuum code Nothing else should be writing to those blocks. If it looks like a bug in vacuum, we can try to reproduce it, and also examine the actual contents of the on-disk files (to see if the data is there on the disk or not). I'll also copy this message over to the hackers list and see what the developers have to say. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Delete with subquery deleting all records
Francisco Reyes wrote: When I try to run: delete from export_messages where export_id in (SELECT distinct export_messages.export_id as id FROM export_messages LEFT OUTER JOIN exports ON (export_messages.export_id = exports.export_id) ); Why not use EXISTS? DELETE FROM export_messages WHERE NOT EXISTS ( SELECT 1 FROM exports WHERE exports.export_id = export_messages.export_id ) I suppose you run those queries in a transaction block, right? -- 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 6: explain analyze is your friend
Re: [GENERAL] Tsearch2: upgrading from postgres 8.1.x to 8.2.x
Le mercredi 23 mai 2007 à 11:08 -0400, Rick Schumeyer a écrit : I have a database running under pg 8.1.4 that uses tsearch2. I am upgrading to pg 8.2.4. I dumped the pg 8.1.x database and tried to install it in pg 8.2.4. This does not seem to work. Are you using snowball stemmer ? Is there a procedure for this that someone can point me to? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] the future of pljava development
Thomas Hallgren pisze: Hi, This sounds great. Sorry for not being responsive on the pgsql-general. I'll read up on this thread during the weekend. Short term, this is what I think needs to be done: 1. Create a PL/Java 1.4 from current CVS. It compiles and runs with PostgreSQL 8.2. 2. Do whatever it takes to make PL/Java run with 8.3. 3. When 8.3 is released, verify that everything is really ok, then a 1.5 release should come out. After that, there are improvements in the Java domain. We should support Java 1.5 for instance. 1.4 is soon starting its End Of Life cycle. We should support 1.6 (I'm using it with pljava without problems - however I'm not using all new features of Java 6). Would you like to become committers to the PL/Java project? You can count on me (only as Java coder or tester). Regards, Marek ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] composite type array and index
Hello everyone, it's great to have in next release (8.3) great feature: composite type array. I'm waiting to see how it works, however I wonder whether it will be possible to create an index on a column of composite type array just like we have an index on integer[] column (using gin or intarray module) ? Regards, Marek ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] why postgresql over other RDBMS
You've addressed cost and performance. Not much left. Try it out for yourself and see if it works for you. On May 24, 2007, at 4:06 AM, Jasbinder Singh Bali wrote: Hi I was wondering, apart from extensive procedural language support and being free, what are other major advantages of Postgresql over other major RDBMS like oracle and sql server. Any pointers would be highly appreciated. Thanks, ~Jas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] using bytea vartype non-prepared statements
I would like to build a sql statement in perl and execute it without
binding parameters if possible.
But I also need to use bytea variable type because I'm storing two
byte characters (Big5, utf8...)
In case of using a varchar and ASCII I would simply write a sql
statement like this:
INSERT INTO foo values('bar');
by building the SQL ...
my $sql = INSERT INTO foo VALUES('$string');
$dbh-do($sql);
I'm not sure if I can even do this if the underlying table has a
field of type bytea.
I was reading in the archives an entry which said I may not be able
to do this because the variable $string might contain null characters...
If you are asking why the bind has to happen at all, it is partly
because
libpq does not support returning the data types yet, and partly because
unlike most other data types, it is very important that DBD::Pg (and
libpq,
and the backend) be told explicitly that a binary string is being used,
so that the length can be sent, as a null character may not represent
the
end of the string.
In order to address this I was using a SQL statement previously where
I knew that the number of parameters was only two and I could write
the perl to handle this:
my $sth = $dbh-prepare(insert into quarantine values (?,?));
$sth-bind_param(1, $idx);
$sth-bind_param(2, $text, { pg_type = DBD::Pg::PG_BYTEA });
$sth-execute();
In this case, I don't actually know before hand just how many
variables I need to bind. Rather, I don't know at compile time.
Using these examples I am not sure how I can incorporate building a
SQL string like the first INSERT statement which will be able to
correctly handle byte data.
Or is this a case of my reading too much into it and just leaving
things up to the magic of the libraries to sort it out?
---(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] why postgresql over other RDBMS
Tom Allison wrote: You've addressed cost and performance. Not much left. Try it out for yourself and see if it works for you. + elephant in logo - unpronounceable name + excellent mailing lists + excellent developer community - you can download as many copies as you like and a salesman still won't take you out to lunch If you want specific vs each DB... MSSQL obviously integrates nicely with the rest of the MS developer tools. If you're planning a .NET (TM) deployment over a range of Windows (TM) systems and have a lot of experiences MS developers in-house then it's perhaps the default choice. Oracle has much more experience running on top-end hardware than PG. If you've got the in-depth knowledge and/or the money then you can push it further. On the other hand, I can buy a 2-cpu x 4-core machine with 16GB RAM and half-a-dozen disks for £5k from dell (not that you necessarily would). That's a lot of oomph for the money - think what it would have cost five years ago. Add Debian + PostgreSQL, total cost=£5k. Add Windows + SQL Server, total cost = £12k -- Richard Huxton Archonet Ltd ---(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] using bytea vartype non-prepared statements
Tom Allison wrote:
In order to address this I was using a SQL statement previously where I
knew that the number of parameters was only two and I could write the
perl to handle this:
my $sth = $dbh-prepare(insert into quarantine values (?,?));
$sth-bind_param(1, $idx);
$sth-bind_param(2, $text, { pg_type = DBD::Pg::PG_BYTEA });
$sth-execute();
In this case, I don't actually know before hand just how many variables
I need to bind. Rather, I don't know at compile time.
I always have a small wrapper function that builds my queries for me.
Assuming you have a list of values and their types (or a list of
structures)...
1. Build a list of comma-separated qmarks: join(',', map {'?'} @params)
2. loop through your params looking up the corresponding pg_type for
your internal type - bind the param accordingly
3. execute
Or am I missing something here?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Geographic data sources, queries and questions
* Chuck D. [EMAIL PROTECTED] [20070524 01:26]: 2) I've spent an accumulated total of around a month and a half trying to consolidate geographic name data from several free sources on the net and realize this isn't the best use of my time and errors will be had. Does anyone know of a reliable source of geo data that isn't costly? Most want to charge a server license, annual rate, etc. I'm not sure about the free sources because one I used actually had mixed values in a column and drove me nuts. I primarily need: country state county if applicable city latitude longitude This is primarily input from an HTML form to calculate distances between users. Anyone who has any experience with geo name data I would appreciate hearing your solution. We are using this data which seems to be fairly extensive and accurate, and is free: http://earth-info.nga.mil/gns/html/gis_countryfiles.htm I haven't fully understood the meaning of all the fiels in there however, we're using it only to compute alternative spellings for city and country names and came up with these conditions that seem to return the desired results: For city alternatives: select lower(full_name) as full_name from geo_names gn1 where gn1.ufi in (select ufi from geo_names gn2 where lower(gn2.full_name)=:city and gn2.fc='P') For country alternatives: select lower(full_name) as full_name from geo_names gn1 where gn1.ufi in (select ufi from geo_names gn2 where lower(gn2.full_name)=:country and gn2.fc='A' and gn2.dsg='PCLI') Til ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] the future of pljava development
Marek Lewczuk wrote: I understand that most of PostgreSQL core team aren't fans of Java, however ignoring this language (most popular programing language) and developers that uses Java in everyday work is not advisable if we want to make PostgreSQL more popular. Look at competing RDBMS - Oracle and DB2 have very strong support for Java, not only as procedural language but in many other areas. Being a fan or not of the language really isn't the issue, and nobody is *ignoring* the language and platform. We all know the others have it. And we want it. But the core team (or more importantly in this case, the people doing backend development, which is partially a different thing) aren't *java programmers* (at least not good ones, AFAIK), so they really can't do much there. This is one of the reasons we have the pl/java project separately - to make it possible to have people focus on what they know and are good at. Oh, and both DB2 and SQLServer has .Net support for stored procedures, and we don't have that at all - so you could make an equally strong argument that the core people should focus on that. Which has even less chance of happening :-) I would like to know your opinion about pljava and its future and maybe also there are people among PostgreSQL developers (core team or contrib team) that are able to help with the development. If you (or whomever picks up where the project is at now) have any specific issues wrt the backend integration and such things, there are a lot of people on the -hackers list that will be happy to help out. But since most of us aren't Java people, you're probably going to have to look elsewhere if you need help with the Java parts. We can make a survey how many PostgreSQL users would like to use pljava - I'm sure that the results would show that it is required to have pljava as one of the most important features of PostgreSQL. We did have one not too long ago. It showed that about 14% of our users did, which puts it in fourth place, after perl, php and python. But not far behind. See http://www.postgresql.org/community/survey.44 So saying that it's one of the most important features is definitely wrong. Saying it's important or very important is absolutely not wrong. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why postgresql over other RDBMS
On Thursday 24 May 2007 10:06, Jasbinder Singh Bali wrote: Hi I was wondering, apart from extensive procedural language support and being free, what are other major advantages of Postgresql over other major RDBMS like oracle and sql server. Any pointers would be highly appreciated. Thanks, ~Jas A free (as in free speech) license. You can still use postgresql if the developers decide to stop making postgres, or stop making it free, as it is likely it will be forked and continued by others (like XFree86 forked into Xorg). And, you won't run into annoying licence agreements that you can't use it on machines with two CPU's... In my experience as sysadmin, I prefer free licenses (open source) software so much above commercial software. Something to think about. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] the future of pljava development
Magnus Hagander pisze: Being a fan or not of the language really isn't the issue, and nobody is *ignoring* the language and platform. We all know the others have it. And we want it. But the core team (or more importantly in this case, the people doing backend development, which is partially a different thing) aren't *java programmers* (at least not good ones, AFAIK), so they really can't do much there. This is one of the reasons we have the pl/java project separately - to make it possible to have people focus on what they know and are good at. I didn't wanted to say that all PostgreSQL core developers should start working on pljava - I just wanted to ask whether we can ask for help with C,JNI and PostgreSQL backend cause that is the problem. Oh, and both DB2 and SQLServer has .Net support for stored procedures, and we don't have that at all - so you could make an equally strong argument that the core people should focus on that. Which has even less chance of happening :-) Good point, but from the other hand there are plenty of programing languages but Java is the most popular :-) (source: http://www.tiobe.com/tpci.htm). I would like to know your opinion about pljava and its future and maybe also there are people among PostgreSQL developers (core team or contrib team) that are able to help with the development. If you (or whomever picks up where the project is at now) have any specific issues wrt the backend integration and such things, there are a lot of people on the -hackers list that will be happy to help out. But since most of us aren't Java people, you're probably going to have to look elsewhere if you need help with the Java parts. There is no problem with Java parts but rather connecting Java with PostgreSQL backend which requires knowledge of C and JNI. We did have one not too long ago. It showed that about 14% of our users did, which puts it in fourth place, after perl, php and python. But not far behind. See http://www.postgresql.org/community/survey.44 Count +1 for pljava, I didn't vote (unfortunately) :-( So saying that it's one of the most important features is definitely wrong. Saying it's important or very important is absolutely not wrong. You are right, it's very important and I hope that all of us agree with that statement (maybe I go too far saying one of the most...) :-) Regards, Marek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] the future of pljava development
Marek Lewczuk wrote: Magnus Hagander pisze: Being a fan or not of the language really isn't the issue, and nobody is *ignoring* the language and platform. We all know the others have it. And we want it. But the core team (or more importantly in this case, the people doing backend development, which is partially a different thing) aren't *java programmers* (at least not good ones, AFAIK), so they really can't do much there. This is one of the reasons we have the pl/java project separately - to make it possible to have people focus on what they know and are good at. I didn't wanted to say that all PostgreSQL core developers should start working on pljava - I just wanted to ask whether we can ask for help with C,JNI and PostgreSQL backend cause that is the problem. C and the backend you can certainly get help with from the -hackers crowd. You'll have a harder time with the JNI stuff, I assume. Oh, and both DB2 and SQLServer has .Net support for stored procedures, and we don't have that at all - so you could make an equally strong argument that the core people should focus on that. Which has even less chance of happening :-) Good point, but from the other hand there are plenty of programing languages but Java is the most popular :-) (source: http://www.tiobe.com/tpci.htm). Well, as with most such studies, you can find one that shows anything you want :-) But again, that's irrelevant really - it's an important platform that we want to support, doesn't matter where on the toplist it is, we all know it's there somewhere. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Timestamp with time zone: why not?
On Wed, 23 May 2007, [EMAIL PROTECTED] wrote: Would I be right in thinking that, in general, a column to hold timestamp values is best created with type 'TIMESTAMP WITH TIME ZONE' and not 'TIMESTAMP' nor 'TIMESTAMP WITHOUT TIME ZONE'? If the application's users are in different time zones, or accurate time intervals are important, then time zones should be part of the stored data. If this is not an issue, and will not be an issue, it does not matter. However, using 'WITH TIME ZONE' in the DDL means that if the situation changes you are covered. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] the future of pljava development
Magnus Hagander pisze: C and the backend you can certainly get help with from the -hackers crowd. You'll have a harder time with the JNI stuff, I assume. Great to hear that - what we need to do now is to sit down (I mean Thomas Hallgren and others) on the pljava code and see where help is needed (in order to have it prepared for 8.3 version) and plan further development. Thanks. Regards, Marek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Geographic data sources, queries and questions
I don't believe this is good design. You'll have to have a trigger or something to verify that the country_id+state_id on the city table are exactly equal to the country_id+state_id on the state table. If you don't, you might have something like (using US city names...) country: USA - state: NY - country: Zimbabwe - state: NY - city: New York. It isn't a problem of any country and any state on the city table, but a problem of this state inside that particular country. I'd drop the country column. You are right, this is a bad design. The country_id on the city table has to go. I'm not sure it is a bad design. Country has a country_id. That's the primary key. State has a state_id, and exactly one country, so really state has a compound primary key, namely (country_id, state_id). And similarly to produce relational integrity between state and city, city needs to reference the state primary key, which means state has to have all three (country_id, state_id, city_id) as it's primary key. This ties in with a subject dear to my heart and discussed at great length starting here: http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php; The tie-in is not that you necessarily need the infamous gap-less sequence, but that you don't necessarily need three sequences per se. While it would be temptingly easy to simply declare all three separately in each table as country: country_id SERIAL, ... state: country_id integer, state_id SERIAL, ... city: country_id integer, state_id integer, city_id SERIAL, ... with that naive approach, every row in state has a unique state_id, and every row in city has a unique city_id. Then you'll notice that values of country_id are repeated in state, and state_id values are repeated in city. And then you'll realize that really it is the combination of (country_id, state_id) that defines a unique state, and (country_id, state_id, city_id) that defines a unique city. It would require the use of stored programs and triggers to manage these compound keys. But that's what stored programs and triggers are for. If you allow the fact that two countries could lay claim to the same geographic sub-region, then you need a separate table for an n-m relation. Then throw in postal codes. Not only can a city have multiple postal codes, but a postal code can serve more than one city. And the cities served might be in different states! (I used to have an example of that, but I can't find it right now.) Same with telephone area codes. (You are going there, eventually, right?) ---(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] composite type array and index
On 5/24/07, Marek Lewczuk [EMAIL PROTECTED] wrote: Hello everyone, it's great to have in next release (8.3) great feature: composite type array. I'm waiting to see how it works, however I wonder whether it will be possible to create an index on a column of composite type array just like we have an index on integer[] column (using gin or intarray module) ? good question. one small point here. you can index int[] with standard btree and not gist which optimizes equality searches, etc. The problem is not the array but that composite types have no defined comparison function. You can create an index on table with composite array type but it's not really any use because the operator class is not defined for the type. Array orderings decompose to orderings on the internal type. If you really need the functionality you can create an operator class for btree (for simple operations) or gist (for more complex searches inside the array), gin, etc. I guess it would be kinda nice if composite types had a 'operator of last resort' which would work as the sql-row comparison does, and do ordering left to right for purposes of comparison. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
Hi, I already know that transaction is impossible inside a function, but I think I really need a way to counter this I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can't wait the procedure to finish I must find a way to make commit between INSERT. Thanks in advance, Célestin Here is the skeleton of my code : CREATE OR REPLACE FUNCTION F2(...) AS $$ DECLARE ... BEGIN ... FOR ligne IN ... ... LOOP NSERT INTO place I wish I could put a commit END LOOP ... END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION F1(...) AS $$ DECLARE BEGIN FOR all IN ... LOOP PERFORM F2(...) another place I could put my commit END LOOP ... END $$ LANGUAGE plpgsql; 2007 - Maporama International - Outgoing mail scanned by BlackSpider
[GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
Hi, I already know that transaction is impossible inside a function, but I think I really need a way to counter this I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can't wait the procedure to finish I must find a way to make commit between INSERT. Thanks in advance, Célestin Here is the skeleton of my code : CREATE OR REPLACE FUNCTION F2(...) AS $$ DECLARE ... BEGIN ... FOR ligne IN ... ... LOOP NSERT INTO place I wish I could put a commit END LOOP ... END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION F1(...) AS $$ DECLARE BEGIN FOR all IN ... LOOP PERFORM F2(...) another place I could put my commit END LOOP ... END $$ LANGUAGE plpgsql; 2007 - Maporama International - Outgoing mail scanned by BlackSpider
Re: [GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
On Thu, May 24, 2007 at 03:59:15PM +0200, Célestin HELLEU wrote: Hi, I already know that transaction is impossible inside a function, but I think I really need a way to counter this I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can't wait the procedure to finish In general making seperate transactions slows things down, not speeds things up. Have you actually check what the cause of the slowness is? Are there any triggers, foreign key, etc defined. Is the query in the loop fast enough? You're going to have to provide more details. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] 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] why postgresql over other RDBMS
Better support! Where else can you get feedback from the actual programmers (sometimes within minutes of writing a message) than here? Ben Hi I was wondering, apart from extensive procedural language support and being free, what are other major advantages of Postgresql over other major RDBMS like oracle and sql server. Any pointers would be highly appreciated. Thanks, ~Jas ---(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] the future of pljava development
Thomas Hallgren wrote: Hi, This sounds great. Sorry for not being responsive on the pgsql-general. I'll read up on this thread during the weekend. Short term, this is what I think needs to be done: 1. Create a PL/Java 1.4 from current CVS. It compiles and runs with PostgreSQL 8.2. 2. Do whatever it takes to make PL/Java run with 8.3. 3. When 8.3 is released, verify that everything is really ok, then a 1.5 release should come out. After that, there are improvements in the Java domain. We should support Java 1.5 for instance. 1.4 is soon starting its End Of Life cycle. Would you like to become committers to the PL/Java project? Sure, I'm willing to help. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database Security
Look at changing your pg_hba.conf file If you have a line in the file like: hostall all 127.0.0.1/32 trust change it to: hostall all 127.0.0.1/32 md5 then run: pg_ctl reload should get you whare you want to be. Ben Danilo Freitas da Costa [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi all!!! I'm using Postgres at company I work for few time. I already tried many ways to create a security for my database but not sucessfull. The postgres was installed with default configuration and I had definied postgres as root user. However, someone else user I create have full access on all databases in my server, with some limitations. How can I configure access level to a database? How can I force every user type your password to access the database? Thanks, Danilo ---(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] why postgresql over other RDBMS
On 5/24/07, Richard Huxton [EMAIL PROTECTED] wrote: Tom Allison wrote: You've addressed cost and performance. Not much left. Try it out for yourself and see if it works for you. + elephant in logo - unpronounceable name + excellent mailing lists + excellent developer community - you can download as many copies as you like and a salesman still won't take you out to lunch + Friendly toolset in the box. [1] + Transactional DDL. [2] + Table inheritance, if you care to use unportable features. + Extensibility. [3] + PostGIS for spatial extensions. - Replication offerings suck. - Warm standby involve a lot of manual labour. - Point-in-time recovery involve a lot of manual labour. [1] psql+readline, pg_dump etc. are a breeze compared to crusty Oracle tools; psql feels distinctly modern compared to MySQL's crummy interpreter. [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. [3] PostgreSQL can be extended with new domain types, stored-procedure languages (eg., PL/Python, PL/Perl), functions (eg., dblink, fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows projects such as TSearch2 and PostGIS to be implemented as separate extensions to PostgreSQL. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
Well, with any database, if I had to insert 20 000 000 record in a table, I wouldntt do it in one transaction, it makes very big intermediate file, and the commit at the end is really heavy. I would cut the transaction in midi-transaction, of let's say 1000 records. There is either not really more code, no trigger, no key, etc. Imagine something like this : FOR all IN (select * from TABLE1) LOOP FOR some IN (select * from) LOOP INSERT INTO TABLE2 VALUES (all.id, some.id) END LOOP END LOOP I with I could put a commit in the inside for !! -Message d'origine- De : Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Envoyé : jeudi 24 mai 2007 16:48 À : Célestin HELLEU Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ? On Thu, May 24, 2007 at 03:59:15PM +0200, Célestin HELLEU wrote: Hi, I already know that transaction is impossible inside a function, but I think I really need a way to counter this I have a stored procedure in pl/sql that makes about 2 000 000 insert. With the way it works, PostGreSQL il making a unique transaction with all this, resulting so bad performances I can't wait the procedure to finish In general making seperate transactions slows things down, not speeds things up. Have you actually check what the cause of the slowness is? Are there any triggers, foreign key, etc defined. Is the query in the loop fast enough? You're going to have to provide more details. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. 2007 - Maporama International - Outgoing mail scanned by BlackSpider ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Very big transaction in a stored procedure : how can i commit in the middle of it ?
=?iso-8859-1?Q?C=E9lestin_HELLEU?= [EMAIL PROTECTED] writes: Well, with any database, if I had to insert 20 000 000 record in a table, I= wouldntt do it in one transaction, it makes very big intermediate file, an= d the commit at the end is really heavy. There may be some databases where the above is correct thinking, but Postgres isn't one of them. The time to do COMMIT, per se, is independent of the number of rows inserted. You need to find out where your bottleneck actually is, without any preconceptions inherited from some other database. 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] Very big transaction in a stored procedure : how can i commit in the middle of it ?
On Thu, May 24, 2007 at 05:34:00PM +0200, Célestin HELLEU wrote: Well, with any database, if I had to insert 20 000 000 record in a table, I wouldntt do it in one transaction, it makes very big intermediate file, and the commit at the end is really heavy. I would cut the transaction in midi-transaction, of let's say 1000 records. Postgres does not create an intermediate file and the cost of commit is independant of the number of statements within the transaction. Postgres uses a form of MVCC which means you get costs for rollback, but commit is very cheap. I beleive your costs are down to the fact that there are 20 000 000 statements. There is a cost per statement, so if you can write your function to do less statements, you're better off... FOR all IN (select * from TABLE1) LOOP FOR some IN (select * from) LOOP INSERT INTO TABLE2 VALUES (all.id, some.id) END LOOP END LOOP I'd replace the whole loop with a single INSERT statement: INSERT INTO TABLE2 SELECT all.id, some.id FROM all, some WHERE... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] 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] Integrity on large sites
Dave Page wrote: problem with your setup. Granted, MySQL is a pretty bad database, but it's not *that* bad -- your example implies that heavily MyISAM-based (you don't say whether this is MyISAM or InnoDB) sites such as Slashdot and Flickr should be falling over every hour. I'm not going to comment on who's fault it is, but the OP quoted 100 updates and 600 selects per *second*. I can't imagine Flickr or Slashdot (which is heavily csched for reading) are under anything like that sort of constant load. Uhmmm I would not be surprised at *all* at slashdot or flickr doing that type of velocity. We have customers right now that under peak are doing 10 times that and yes these are customers that have similar types of websites. Sincerely, Joshua D. Drake Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === 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] Postgresql 8.2.4 crash with tsearch2
Pls, check your steps or say me where I'm wrong :)
If you still have a problems, I can solve it if I'll have access to your
developer server...
% cd PGSQL_SRC
% zcat ~/tmp/tsearch_snowball_82-20070504.gz| patch -p0
% cd contrib/tsearch2
% gmake su -c 'gmake install' gmake installcheck
% cd gendict
% cp ~/tmp/libstemmer_c/src_c/stem_UTF_8_french.c stem.c
% cp ~/tmp/libstemmer_c/src_c/stem_UTF_8_french.h stem.h
% ./config.sh -n fr -s -p french_UTF_8 -v -C'Snowball stemmer for
French - UTF8'
% cd ../../dict_fr
% gmake su -c 'gmake install'
% psql contrib_regression dict_fr.sql
contrib_regression=# select lexize('fr', 'sortir'), lexize('fr',
'service'), lexize('fr', 'chose');
lexize | lexize | lexize
+--+
{sort} | {servic} | {chos}
(1 row)
contrib_regression=# select lexize('fr',
'as');
lexize
{}
---(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] Integrity on large sites
Flickr uses InnoDB, by the way. On Thu, 24 May 2007 18:07:21 +0200, Joshua D. Drake [EMAIL PROTECTED] wrote: Dave Page wrote: problem with your setup. Granted, MySQL is a pretty bad database, but it's not *that* bad -- your example implies that heavily MyISAM-based (you don't say whether this is MyISAM or InnoDB) sites such as Slashdot and Flickr should be falling over every hour. I'm not going to comment on who's fault it is, but the OP quoted 100 updates and 600 selects per *second*. I can't imagine Flickr or Slashdot (which is heavily csched for reading) are under anything like that sort of constant load. Uhmmm I would not be surprised at *all* at slashdot or flickr doing that type of velocity. We have customers right now that under peak are doing 10 times that and yes these are customers that have similar types of websites. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] why postgresql over other RDBMS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/24/07 10:30, Alexander Staubo wrote: On 5/24/07, Richard Huxton [EMAIL PROTECTED] wrote: Tom Allison wrote: You've addressed cost and performance. Not much left. Try it out for yourself and see if it works for you. + elephant in logo - unpronounceable name + excellent mailing lists + excellent developer community - you can download as many copies as you like and a salesman still won't take you out to lunch + Friendly toolset in the box. [1] + Transactional DDL. [2] + Table inheritance, if you care to use unportable features. + Extensibility. [3] + PostGIS for spatial extensions. - Replication offerings suck. - Warm standby involve a lot of manual labour. - Point-in-time recovery involve a lot of manual labour. [1] psql+readline, pg_dump etc. are a breeze compared to crusty Oracle tools; psql feels distinctly modern compared to MySQL's crummy interpreter. [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also have transactional DDL. Actually, I was quite stunned to discover that Oracle doesn't do that. Interbase/Firebird probably also has transactional DDL. [3] PostgreSQL can be extended with new domain types, stored-procedure languages (eg., PL/Python, PL/Perl), functions (eg., dblink, fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows projects such as TSearch2 and PostGIS to be implemented as separate extensions to PostgreSQL. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGVcaiS9HxQb37XmcRAu69AJ0YtBw6zpuR6zAZzEIjVWfisKeM5ACeKz+x bJGv1Wh7YrUeYglaOHg1n7I= =YKt9 -END PGP SIGNATURE- ---(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] bytea perl
Hi,
First, I would advise never using insert into xx values (y,x) without
explicitly naming the columns; same for select statements - never use
select * (a table change can mess things up).
By the way, I just noticed in the release notes for the very latest couple
of versions of DBD:Pg that some placeholder changes were made. You might
want to check the release notes and your version of DBD:Pg about your
placeholder issues.
You might want to try using $dbh-quote instead of what you are using for
quoting values, since it is database-specific. Something like:
my ($stmt, $list, @data);
my @list=(it's, 'a', 'quick', 'brown', 'fox', 'that', 'jumped',
'over');
$list.=(join ', ',(map {$dbh-quote($_)} @list));
$stmt=EOF;
select id1, txtval1 from test1 where txtval1 in ($list)
EOF
print stmt:\n$stmt\n;
$sth=$dbh-prepare($stmt) ||errexit(bad prepare for stmt $stmt,
error: $DBI::errstr);
$rc=$sth-execute() || errexit(can't execute statement:\n$stmt; DB
error: $DBI::errstr);
while (@data = $sth-fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
print '',(join \t,@data),\n;
}
#check for problems with premature termination
errexit($sth-errstr) if $sth-err;
This produces the output:
stmt:
select id1, txtval1 from test1 where txtval1 in ('it''s', 'a',
'quick', 'brown', 'fox', 'that', 'jumped', 'over')
24 quick
25 brown
26 fox
I currently have PostgreSQL 7.4, DBI 1.46, DBD:Pg 1.32. You may have
newer versions with different behavior.
Here is some simple bytea stuff that works (a simple test I was playing
with a while back for storing images, and displaying them back via the
web). I still had to escape certain characters, not just tell DBD that I
was using bytea:
my $infile='/var/www/html/test_scr_cap.png'; #image file
my ($buf, $imgdata);
open (IMG, $infile) or die Cannot open $infile, $!;
while (read(IMG,$buf,512)) {
$imgdata.=$buf;
}
close IMG;
print Size of imgdata is ,length($imgdata),\n;
my $stmt=EOF;
INSERT into imagedata (idval, imagedata) values (1, ?)
EOF
$sth=$dbh-prepare($stmt) ||errexit(bad prepare for stmt $stmt,
error: $DBI::errstr);
my $rc=$sth-bind_param(1, escape_bytea($imgdata), { pg_type =
DBD::Pg::PG_BYTEA });
$rc=$sth-execute() ||errexit(can't execute statement:\n$stmt\nreturn
code $rc: DB error: $DBI::errstr);
$dbh-commit();
#retrieve the data:
$stmt=EOF;
SELECT idval, imagedata from imagedata where idval = 1
EOF
my $outfile='/var/www/html/test_scr_cap_out.png';
open (IMG, $outfile) or die Cannot open $outfile, $!;
execute_db_statement($stmt, __LINE__);
my ($idval, $imagedata_read);
$sth-bind_col(1, \$idval);
$sth-bind_col(2, \$imagedata_read);
$sth-fetch;
print size of data read is ,length($imagedata_read),\n;
print IMG $imagedata_read;
close IMG;
print Output file is $outfile\n;#when viewed again, image looks
fine.
sub escape_bytea {
my ($instring)[EMAIL PROTECTED];
my $returnstring=join ('',map {
my $tmp=ord($_);
($tmp = 32 and $tmp = 126 and $tmp != 92) ? $_ :
sprintf('\%03o',$tmp);} split (//,$instring));
return $returnstring;
} # end sub escape_bytea
sub execute_db_statement {
#this subroutine will prepare and execute a statement for the database,
and errexit if it fails either step
my ($statement, $lineno)[EMAIL PROTECTED];
my ($rc);
#get basic machine info
$sth=$dbh-prepare($statement) ||errexit(bad prepare for stmt
$statement at line $lineno, error: $DBI::errstr);
$rc=$sth-execute() ||errexit(can't execute
statement:\n$statement\n at line $lineno, , return code $rc: DB
error: $DBI::errstr);
} # end sub execute_db_statement
Hope this helps.
Susan Cassidy
Tom Allison [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/23/2007 06:21 PM
To
General PostgreSQL List pgsql-general@postgresql.org
cc
Subject
[GENERAL] bytea perl
I've been running into problems with some characters that I believe
can be solved using bytea variable type instead of varchar()
I'm picking up data from email and trying to put it into a table.
I'm trying to merge two different types of SQL and I'm really not
sure how this can be done...
I had a previous version of my SQL that looked like:
my $sth = $dbh-prepare(insert into quarantine values (?,?));
$sth-bind_param(1, $idx);
$sth-bind_param(2, $text, { pg_type = DBD::Pg::PG_BYTEA });
$sth-execute();
In this case I was inserting an entire email content into the second
parameter as type bytea. Originally I was doing this as text and
running into problems inserting records when there were weird characers.
I want to be able to use the BYTEA data type for inserting records
into another SQL that looks like:
my $sql=SQL;
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING ) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
SQL
NOTE: TOKEN_LIST_STRING
Re: [GENERAL] why postgresql over other RDBMS
For us? Stability. Pure and simple. It ended up that the site was faster too. One issue with postgresql is that connection time is slower than mysql. Otherwise most everything else is just as fast or faster. So with Ruby on Rails, there is a persistent connections from the container, and that disadvantage dissapears. If there's anything slow in the site, its ruby template rendering, database access generally is 10% or less of total time it takes to generate our most popular pages. Ericson Smith Developer http://www.funadvice.com -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/24/07 10:30, Alexander Staubo wrote: On 5/24/07, Richard Huxton [EMAIL PROTECTED] wrote: Tom Allison wrote: You've addressed cost and performance. Not much left. Try it out for yourself and see if it works for you. + elephant in logo - unpronounceable name + excellent mailing lists + excellent developer community - you can download as many copies as you like and a salesman still won't take you out to lunch + Friendly toolset in the box. [1] + Transactional DDL. [2] + Table inheritance, if you care to use unportable features. + Extensibility. [3] + PostGIS for spatial extensions. - Replication offerings suck. - Warm standby involve a lot of manual labour. - Point-in-time recovery involve a lot of manual labour. [1] psql+readline, pg_dump etc. are a breeze compared to crusty Oracle tools; psql feels distinctly modern compared to MySQL's crummy interpreter. [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also have transactional DDL. Actually, I was quite stunned to discover that Oracle doesn't do that. Interbase/Firebird probably also has transactional DDL. [3] PostgreSQL can be extended with new domain types, stored-procedure languages (eg., PL/Python, PL/Perl), functions (eg., dblink, fuzzystrmatch, cube), and indexes (GiST, GIN). Together this allows projects such as TSearch2 and PostGIS to be implemented as separate extensions to PostgreSQL. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGVcaiS9HxQb37XmcRAu69AJ0YtBw6zpuR6zAZzEIjVWfisKeM5ACeKz+x bJGv1Wh7YrUeYglaOHg1n7I= =YKt9 -END PGP SIGNATURE- ---(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 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] why postgresql over other RDBMS
On 5/24/07, Ron Johnson [EMAIL PROTECTED] wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also have transactional DDL. Not exactly mainstream databases, and Codasyl isn't even a relational database as far as I remember. Interbase/Firebird probably also has transactional DDL. Apparently. I'm surprised, given how InterBase and Firebird otherwise feel so awfully ancient. All right, so not unique, but very rare. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] postgres - oid question
Hi... In looking over a few sites via google, I'm trying to figure out what has to be added to the sql I have for creating TBLs that use OIDs. I'd rather have the cmds added to the sql file, than the postgres conf file... I can't find out what I have to add! I understand that OIDs are depricated, but the app I'm dealing with has them. I'm using v8.1 of postgres on Fedora 5. Any pointers are appreciated. thanks -bruce [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 4:39 AM, Richard Huxton wrote: - unpronounceable name post-gres-queue-el Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] why postgresql over other RDBMS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/24/07 12:48, Alexander Staubo wrote: On 5/24/07, Ron Johnson [EMAIL PROTECTED] wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. Rdb/VMS and CODASYL DBMS (both Oracle, formerly DEC, products) also have transactional DDL. Not exactly mainstream databases, and Codasyl isn't even a relational database as far as I remember. True, they are legacy systems, but are still actively developed by Oracle, for Alpha and Itanium VMS. (The VAX products still are actually maintained, but only for bug-fixes.) And yes, DBMS uses the network model. (Since both it and Rdb use the same back end and on-disk structures, it's not that much extra effort to also keep on actively developing DBMS.) Interbase/Firebird probably also has transactional DDL. Apparently. I'm surprised, given how InterBase and Firebird otherwise feel so awfully ancient. InterBase was devloped by a man who worked for DEC at the same time that Rdb was in the works. His DECisms are still apparent in Firebird. God, I'm going to hate it when we migrate to Oracle... All right, so not unique, but very rare. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGVdNvS9HxQb37XmcRAkK4AKCxilH8XRM9es1RZ/Vma/AUrLoVnACfaGgP VSce0lLj/mw2Vj435EDEaII= =Gkbz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Integrity on large sites
Joshua D. Drake wrote: Dave Page wrote: problem with your setup. Granted, MySQL is a pretty bad database, but it's not *that* bad -- your example implies that heavily MyISAM-based (you don't say whether this is MyISAM or InnoDB) sites such as Slashdot and Flickr should be falling over every hour. I'm not going to comment on who's fault it is, but the OP quoted 100 updates and 600 selects per *second*. I can't imagine Flickr or Slashdot (which is heavily csched for reading) are under anything like that sort of constant load. Uhmmm I would not be surprised at *all* at slashdot or flickr doing that type of velocity. We have customers right now that under peak are doing 10 times that and yes these are customers that have similar types of websites. Well taking the /. case, it's well known that they generate static pages periodically in much the same way as we do for our website and serve those, rather than hitting the comments database for every web hit. As for the updates, I imagine they are next to non-existent on /. but if you look at the comment posts instead which are probably their most frequent type of non-select query, at 100 inserts per second, that equates to 8,640,000 comments added per day. Assuming they publish, say 20 stories per day, that averages at 432,000 comments, per story, per day. I don't recall the last time I saw that sort of response to a /. story... Regards, Dave. ---(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] Timestamp with time zone: why not?
[EMAIL PROTECTED] wrote: Would I be right in thinking that, in general, a column to hold timestamp values is best created with type 'TIMESTAMP WITH TIME ZONE' and not 'TIMESTAMP' nor 'TIMESTAMP WITHOUT TIME ZONE'? To put it another way, for what reasons might the 'TIMESTAMP' type be preferred to 'TIMESTAMP WITH TIME ZONE'? We use the database to store one minute roll up stats from our main application. Each of these has a timestamp on it. There's really no need for timestamptz in this instance. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres - oid question
Hi Oliver. Thanks for the reply. I was hoping that there was/is a single cmd that I could use at the beginning of the sql file, that would allow all the tables that are created to be created using the OID. Kind of a use OID create tbl Foo1 create tbl Foo2 create tbl Foo3 create tbl Foo4 create tbl Foo5 stop OID create tbl Foo6 create tbl Foo7 so that everything created between use/stop would be created using OID... thanks -bruce -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 11:19 AM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgres - oid question On Thu, 2007-05-24 at 10:58 -0700, bruce wrote: Hi... In looking over a few sites via google, I'm trying to figure out what has to be added to the sql I have for creating TBLs that use OIDs. I'd rather have the cmds added to the sql file, than the postgres conf file... I can't find out what I have to add! I understand that OIDs are depricated, but the app I'm dealing with has them. I'm using v8.1 of postgres on Fedora 5. Any pointers are appreciated. TO create one table with oids, use CREATE TABLE table_name (...) WITH OIDS; To have all tables created with oids, set the config parameter default_with_oids to true. (It defaults to false from 8.1 onwards.) Oliver Elphick ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why postgresql over other RDBMS
On Thursday 24. May 2007 19:57, Erik Jones wrote: On May 24, 2007, at 4:39 AM, Richard Huxton wrote: - unpronounceable name post-gres-queue-el Somebody probably wants to put that pot-grass-kewl thing in his pipe and smoke it. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why postgresql over other RDBMS
On Thursday 24 May 2007 17:30, Alexander Staubo wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. If this is such a rare feature, I'm very glad we chose postgresql. I use it all the time, and wouldn't know what to do without it. We circumvented Ruby on Rails' migrations, and just implemented them in SQL. Writing migrations is a breeze this way, and you don't have to hassle with atomicity, or the pain when you discover the migration doesn't work on the production server. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres - oid question
On Thu, 2007-05-24 at 10:58 -0700, bruce wrote: Hi... In looking over a few sites via google, I'm trying to figure out what has to be added to the sql I have for creating TBLs that use OIDs. I'd rather have the cmds added to the sql file, than the postgres conf file... I can't find out what I have to add! I understand that OIDs are depricated, but the app I'm dealing with has them. I'm using v8.1 of postgres on Fedora 5. Any pointers are appreciated. TO create one table with oids, use CREATE TABLE table_name (...) WITH OIDS; To have all tables created with oids, set the config parameter default_with_oids to true. (It defaults to false from 8.1 onwards.) Oliver Elphick ---(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] postgres - oid question
On Thursday 24 May 2007 11:31, bruce [EMAIL PROTECTED] wrote: Hi Oliver. Thanks for the reply. I was hoping that there was/is a single cmd that I could use at the beginning of the sql file, that would allow all the tables that are created to be created using the OID. Kind of a set default_with_oids = true; create table ... create table ... -- `Gun-wielding recluse gunned down by local police isn't the epitaph I want. I am hoping for Witnesses reported the sound up to two hundred kilometers away or Last body part finally located.' --- James Nicoll ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Geographic data sources, queries and questions
Tilmann Singer wrote: We are using this data which seems to be fairly extensive and accurate, and is free: http://earth-info.nga.mil/gns/html/gis_countryfiles.htm We use that, but it is only non-US, so we combine it with this: http://geonames.usgs.gov/domestic/download_data.htm We also have a hodge-podge of other sources, but those are the main ones. (By the way, we have found USGS to very amenable to dumping their data in arbitrary ways. Those state files essentially try to fit everything into a single CSV format, but they have given us other custom dumps.) Note that both of these main sources have multiple names for the same location, so our schema is highly normalized - we have a separate table for names (so the string Springfield occurs in only one place :). Because we are interested in all sorts of geographic entities, not just city/state/country, we have only a single table for these, with fields for type, lat/long, primary name, and a few other things. All other relationships are represented in separate linking tables, using our internal IDs for locations and names, e.g., location_has_name, location_contained_in_location, etc. As far as FIPS and ISO codes are concerned, we have a separate table mapping (locationID, standards body) to codes. We are interested in sharing this stuff, so I'd be happy to pass along the schema and/or the data, although all of it is kind of beta. - John D. Burger MITRE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] bytea perl
On 5/24/2007, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
Hi,
First, I would advise never using insert into xx values (y,x) without
explicitly naming the columns; same for select statements - never use
select * (a table change can mess things up).
By the way, I just noticed in the release notes for the very latest couple
of versions of DBD:Pg that some placeholder changes were made. You might
want to check the release notes and your version of DBD:Pg about your
placeholder issues.
I have the latest from CPAN and Postgresql.
So I should be able to build out the SQL string without using bind_params
by using:
$list.=(join ', ',(map {$dbh-quote($_)} @list));
I am not sure about the E (escape) function, but I don' t think I should
be using the perl quotemeta anymore.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Corrupted index file after restoring WAL on warm spare server
Recently I went through the steps to refresh the database (8.3.4) on my development server (running Fedora Core 5 Linux), making a tarball of the live database, then restoring it on the development server, and running all the archived WAL files. Everything worked fine as far as I can tell, I don't see any errors in any of my logs. Howevever, when I go to access one table, the index appears to be corrupted because the record I get for one query doesn't match the record key I give. The search works correctly on the live server. Reindexing that table on the warm spare system fixes the problem. I redid setting up the warm spare server a second time to see if it was a one-time anomaly, but I have the exact same condition again. What could be causing this? If this is a bug, what can I do to help track it down? -- Mike Nolan [EMAIL PROTECTED]
Re: [GENERAL] Corrupted index file after restoring WAL on warm spare server
Sorry, I meant 8.2.4 (darn typo virus) On 5/24/07, Michael Nolan [EMAIL PROTECTED] wrote: Recently I went through the steps to refresh the database (8.3.4) on my development server (running Fedora Core 5 Linux), making a tarball of the live database, then restoring it on the development server, and running all the archived WAL files. Everything worked fine as far as I can tell, I don't see any errors in any of my logs. Howevever, when I go to access one table, the index appears to be corrupted because the record I get for one query doesn't match the record key I give. The search works correctly on the live server. Reindexing that table on the warm spare system fixes the problem. I redid setting up the warm spare server a second time to see if it was a one-time anomaly, but I have the exact same condition again. What could be causing this? If this is a bug, what can I do to help track it down? -- Mike Nolan [EMAIL PROTECTED]
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 14:29 , Wiebe Cazemier wrote: On Thursday 24 May 2007 17:30, Alexander Staubo wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. If this is such a rare feature, I'm very glad we chose postgresql. I use it all the time, and wouldn't know what to do without it. We circumvented Ruby on Rails' migrations, and just implemented them in SQL. Writing migrations is a breeze this way, and you don't have to hassle with atomicity, or the pain when you discover the migration doesn't work on the production server. Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Unfortunately, the postgresql architecture wouldn't lend itself well to this. Still, it seems like a basic extension of the notion of sub- transactions. Cheers, M ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Corrupted index file after restoring WAL on warm spare server
Michael Nolan [EMAIL PROTECTED] writes: Howevever, when I go to access one table, the index appears to be corrupted because the record I get for one query doesn't match the record key I give. Reindexing that table on the warm spare system fixes the problem. I redid setting up the warm spare server a second time to see if it was a one-time anomaly, but I have the exact same condition again. What could be causing this? If this is a bug, what can I do to help track it down? It sounds like you have a reproducible test case --- can you make it available to someone else? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] why postgresql over other RDBMS
A.M. wrote: On May 24, 2007, at 14:29 , Wiebe Cazemier wrote: On Thursday 24 May 2007 17:30, Alexander Staubo wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. If this is such a rare feature, I'm very glad we chose postgresql. I use it all the time, and wouldn't know what to do without it. We circumvented Ruby on Rails' migrations, and just implemented them in SQL. Writing migrations is a breeze this way, and you don't have to hassle with atomicity, or the pain when you discover the migration doesn't work on the production server. Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Unfortunately, the postgresql architecture wouldn't lend itself well to this. Still, it seems like a basic extension of the notion of sub- transactions. Hmm, doesn't this Just Work with two-phase commit? -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Integrity on large sites
On 5/24/07, PFC [EMAIL PROTECTED] wrote: Flickr uses InnoDB, by the way. The master does. The slaves use MyISAM, according to Cal Henderson: http://www.slideshare.net/coolpics/flickr-44054 Alexander. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] the future of pljava development
On 5/25/07, Marek Lewczuk [EMAIL PROTECTED] wrote: Good point, but from the other hand there are plenty of programing languages but Java is the most popular :-) (source: http://www.tiobe.com/tpci.htm). Guess it depends on what survey you read ... there java is also ran ... http://www.dedasys.com/articles/language_popularity.html We did have one not too long ago. It showed that about 14% of our users did, which puts it in fourth place, after perl, php and python. But not far behind. See http://www.postgresql.org/community/survey.44 Count +1 for pljava, I didn't vote (unfortunately) :-( -1 , I didn't vote either (unfortunately) :} C or perl would have been my choices. Regards, Marek Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 15:57 , Alvaro Herrera wrote: A.M. wrote: Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Unfortunately, the postgresql architecture wouldn't lend itself well to this. Still, it seems like a basic extension of the notion of sub- transactions. Hmm, doesn't this Just Work with two-phase commit? 2PC requires that the modifications already be in concrete. What I suggest is a method for a new connection to insert itself into an existing (sub-)transaction SQL stream, make changes, and commit to the root or parent transaction. In the scenario where changes are pending, only one connection can test the changes- it must be the connection that opened the transaction. Concurrency issues cannot be tested before committing, for example. The implementation could be as simple as decoupling connections from transactions- then a connection could make serialized requests to other backends. A proof-of-concept could certainly be cobbled together with pipes and pl/perl, but the real beef would be the ability to pass responsibility for transactions from one connection to another. Cheers, M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Large Database \d: ERROR: cache lookup failed for relation ...
I'm dealing with a database where there are ~150,000 rows in information_schema.tables. I just tried to do a \d, and it came back with this: ERROR: cache lookup failed for relation [oid] Is this indicative of corruption, or is it possibly a resource issue? I don't see a lot of evidence of this error in the archives. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005
[Re] Re: [Re] Re: [Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2
Cyril VELTER wrote: Cyril VELTER wrote: No I'm not. It's not even complied in the server nor in the pg_dump binary. The server is built on windows using MSYS simply with ./configure make all make install I've been able to reproduce the problem 6 times (at random points in the process, but it never complete successfully). Is there any test I can do to help investigate the problem ? Sorry I haven't gotten back to you for a while. Yeah, if you can attach a debugger to the backend (assuming you have a predictable backend it happens to - but if you're loading, you are using a single session, I assume?), add a breakpoint around the area of the problem, and get a backtrace from exactly where it shows up, that would help. Thanks for your reply. I'll try to do this. I've installed gdb on the problematic machine and recompiled postgres with debug symbols (configure --enable-debug) I'm not very familiar with gdb. Could you give some direction on setting the breakpoint. After running gdb on the postgres.exe file, I'm not able to set the breakpoint (b socket.c:574 give me an error). Hmm, I keep forgetting that. There is some serious black magic required to get gdb to even approach working state on win32. I'm too used to working with the msvc build now. I've never actually got it working myself, but I know others have. Hopefully someone can speak up here? :-) I don't have msvc available. Searching the source files, it seems the error message is generated in port/win32/socket.c line 594. Right, but the important thing is which path down to that function is it generated in. Which is why a backtrace would help. Yes, I understand that. Looking at the code, the problem is probably somewhere in pgwin32_recv(). Now, it really shouldn't end up doing what you're seeing, but obviously it is. After looking at the code of pgwin32_recv(), I don't understand why pgwin32_waitforsinglesocket() is called with the FD_ACCEPT argument. Perhaps we just need to have it retry if it gets the WSAEWOULDBLOCK? Thoughts? I've modified pgwin32_recv() to do that (repeat the pgwin32_waitforsinglesocket() / WSARecv while the error is WSAEWOULDBLOCK and not raising this error. I've an upgrade running right now (I will have the result in the next hours). cyril ---(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] Large Database \d: ERROR: cache lookup failed for relation ...
Thomas F. O'Connell [EMAIL PROTECTED] writes: I'm dealing with a database where there are ~150,000 rows in information_schema.tables. I just tried to do a \d, and it came back with this: ERROR: cache lookup failed for relation [oid] Is this indicative of corruption, or is it possibly a resource issue? It'd not be unexpected if other transactions were concurrently dropping tables. If it's persistent (with the same OID mentioned) then you need to worry. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgres - oid question
On 24/05/2007 18:58, bruce wrote: In looking over a few sites via google, I'm trying to figure out what has to be added to the sql I have for creating TBLs that use OIDs. I'd rather have Something like - create table (...) with oids; - but check the docs (under CREATE TABLE) to be sure. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Return rows from function with expressions
I've written many stored procedures in ms sql and a good many functions in postgres, but I'm rather unsure of how to get a list back from a postgres function which is not based on a table. Example from sql server: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[procPatient] @PatStatusID int = 0 AS BEGIN SELECT PatID, PatFName + ' ' + PatLName as pname FROM tblPatient WHERE PatStatusID = @PatStatusID END Output from the sproc above is like PatID pname 123Merton Baffled 129Jim Puzzled I've spent a good deal of time reading here and in the pg docs about functions that use SETOF. I can't find a plain and simple way to do something like the above. Is there? I simply need to pass in some params, and do some evals inside the function and return a set that includes some cols that are not part of the base table. I've seen solutions that involve OUT params; a method which uses RECORD that requires the columns to be defined in the call; refcursors (which I don't really understand); are those the only options? A simple example like the one I've used for sql server above would be ideal. -- View this message in context: http://www.nabble.com/Return-rows-from-function-with-expressions-tf3812759.html#a10792602 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why postgresql over other RDBMS
We're derailing the thread, but... On 5/24/07, A.M. [EMAIL PROTECTED] wrote: 2PC requires that the modifications already be in concrete. What I suggest is a method for a new connection to insert itself into an existing (sub-)transaction SQL stream, make changes, and commit to the root or parent transaction. The problem with long-running transactions is that they need to avoid locking the resources they touch. Short-running transactions are bad enough as they stand -- until fairly recently (8.1? 8.2?), merely inserting or updating a row that had a foreign-key reference to another table row would lock the referenced row until the end of the transaction, by issuing an implicit select ... for update. Although a mechanism whereby multiple connections can share a single session/transaction is probably easy to implement, using long-running transactions to isolate DDL changes is not feasible at the moment because PostgreSQL currently acquires an AccessExclusiveLock on the modified table until the transaction ends, which means that concurrent transactions would be blocked from even querying the table. I don't know PostgreSQL's internals, so I can only postulate that this locking occurs because PostgreSQL holds a single copy of the schema and related bookeeping structures in memory. Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] why postgresql over other RDBMS
Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Actually you can hack this by starting your webserver with only 1 thread, use persistent connections, and disable all commits in the application. But I'd call that a very hackish hack. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 18:12 , PFC wrote: Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Actually you can hack this by starting your webserver with only 1 thread, use persistent connections, and disable all commits in the application. But I'd call that a very hackish hack. Not really- then I can't use any transactions, in which case, I might as well use MySQL. I would like to be able to pass transaction state between connections. -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] why postgresql over other RDBMS
[EMAIL PROTECTED] (A.M.) writes: On May 24, 2007, at 14:29 , Wiebe Cazemier wrote: On Thursday 24 May 2007 17:30, Alexander Staubo wrote: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. If this is such a rare feature, I'm very glad we chose postgresql. I use it all the time, and wouldn't know what to do without it. We circumvented Ruby on Rails' migrations, and just implemented them in SQL. Writing migrations is a breeze this way, and you don't have to hassle with atomicity, or the pain when you discover the migration doesn't work on the production server. Indeed. Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? That way, you could make your schema changes and test them with any number of test clients (which designate the state to connect with) and then you would commit when everything works. Unfortunately, the postgresql architecture wouldn't lend itself well to this. Still, it seems like a basic extension of the notion of sub- transactions. Jan Wieck had a proposal to a similar effect, namely to give some way to get one connection to duplicate the state of another one. This would permit doing a neat parallel decomposition of pg_dump: you could do a 4-way parallelization of it that would function something like the following: - connection 1 opens, establishes the usual serialized mode transaction - connection 1 dumps the table metadata into one or more files in a specified directory - then it forks 3 more connections, and seeds them with the same serialized mode state - it then goes thru and can dump 4 tables concurrently at a time, one apiece to a file in the directory. This could considerably improve speed of dumps, possibly of restores, too. Note that this isn't related to subtransactions... -- output = reverse(ofni.secnanifxunil @ enworbbc) http://www3.sympatico.ca/cbbrowne/internet.html Unless there really are chrono-synclastic infundibula. (Roll on the Church of God the Utterly Indifferent...) -- Will Deakin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Return rows from function with expressions
On fim, 2007-05-24 at 13:59 -0700, novnov wrote: I've written many stored procedures in ms sql and a good many functions in postgres, but I'm rather unsure of how to get a list back from a postgres function which is not based on a table. Example from sql server: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[procPatient] @PatStatusID int = 0 AS BEGIN SELECT PatID, PatFName + ' ' + PatLName as pname FROM tblPatient WHERE PatStatusID = @PatStatusID END Output from the sproc above is like PatID pname 123Merton Baffled 129Jim Puzzled test=# create table pats (patid int, patfname text, patlname text, patstatus int); CREATE TABLE test=# insert into pats values (123,'Merton','Baffled',2); INSERT 0 1 test=# insert into pats values (129,'Jim','Puzzled',2); INSERT 0 1 test=# insert into pats values (132,'Joe','Confused',1); INSERT 0 1 test=# create type patrec as (patid int, patname text); CREATE TYPE test=# create or replace function getpats(int) returns setof patrec as $$ select patid,patfname|| ' ' || patlname from pats where patstatus=$1 $$ language SQL; CREATE FUNCTION test=# select * from getpats(2); patid |patname ---+ 123 | Merton Baffled 129 | Jim Puzzled (2 rows) hope this helps gnari ---(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] why postgresql over other RDBMS
Alexander Staubo wrote on 24.05.2007 17:30: [2] Nobody else has this, I believe, except possibly Ingres and NonStop SQL. This means you can do a begin transaction, then issue create table, alter table, etc. ad nauseum, and in the mean time concurrent transactions will just work. Beautiful for atomically upgrading a production server. Oracle, of course, commits after each DDL statements. I do have to commit a CREATE TABLE in SQL Server (Express) 2005 (and I believe in 2000 as well), and I can rollback a DROP TABLE. I haven't checked how this behaves with concurrent access though. Thomas ---(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] why postgresql over other RDBMS
Chris Browne wrote: [EMAIL PROTECTED] (A.M.) writes: Jan Wieck had a proposal to a similar effect, namely to give some way to get one connection to duplicate the state of another one. This would permit doing a neat parallel decomposition of pg_dump: you could do a 4-way parallelization of it that would function something like the following: Interesting ... Note that this isn't related to subtransactions... I think what needs to happen is that the snapshot is duplicated. Probably not that hard to do actually ... the difficult thing is coming up with an API that makes sense. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo (G. Lama) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 18:21 , Chris Browne wrote: Jan Wieck had a proposal to a similar effect, namely to give some way to get one connection to duplicate the state of another one. This would permit doing a neat parallel decomposition of pg_dump: you could do a 4-way parallelization of it that would function something like the following: - connection 1 opens, establishes the usual serialized mode transaction - connection 1 dumps the table metadata into one or more files in a specified directory - then it forks 3 more connections, and seeds them with the same serialized mode state - it then goes thru and can dump 4 tables concurrently at a time, one apiece to a file in the directory. This could considerably improve speed of dumps, possibly of restores, too. Note that this isn't related to subtransactions... Well, I was thinking that since transactions are now serializable, it should be possible to move the state between existing open transactions. -M ---(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] Corrupted index file after restoring WAL on warm spare server
On 5/24/07, Tom Lane [EMAIL PROTECTED] wrote: It sounds like you have a reproducible test case --- can you make it available to someone else? Not very easily, Tom. The tarball files are around 35 GB (uncompressed) and I assume they'd only work on a fairly similar system anyway. I assume shell level access would be needed. I may be able to set up a way to get to someone through the firewall, but there are some security issues I'd have to clear with our Executive Director, since there's quite a bit of confidential information in the database and elsewhere on the server. Is this something where I could call someone and have them walk me through checking things? I tried the same thing on a 3rd server (similar hardware and same O/S), and the query is working correctly there. I'm not sure what that tells me. -- Mike Nolan
[GENERAL] index vs. seq scan choice?
I am trying to figure out how the distribution of data affects index usage by the query because I am seeing some behavior that does not seem optimal to my uneducated eye. I am on PG 8.1.8. I have two tables foo and foo_detail, both have been vacuum analyzed recently. Both have a property_id column, both have an index on it. The foo table has a state_code, also indexed, and the relative share of rows for the two state_codes used in the example below is: PA28422.80% MN28582.81% The distribution of distinct property_ids is fairly similar: PA719 2.90% MN765 3.09% A simple query filtered by PA vs. MN produces different results (see below). The PA query does a Seq Scan, the MN query uses the index and is 20 times faster. Both return about the same number of rows. I tried it with all state_codes that have rows in foo and it seems that the cutoff is somewhere around 3%, but there isn't a direct correlation (there are state_codes that are 3% that trigger a Seq Scan and there are ones above 3% that result in an Index scan). I am curious what could make the PA query to ignore the index. What are the specific stats that are being used to make this decision? Would it perform better if it were to use the index? Anything I can do to nudge it towards using the index, which seems like a rather beneficial thing? The actual queries: explain analyze select f.property_id from foo f inner join foo_detail fd using (property_id) where f.state_code = 'PA' Merge Join (cost=17842.71..18436.30 rows=3347 width=4) (actual time=594.538..972.032 rows=2842 loops=1) Merge Cond: (outer.property_id = inner.property_id) - Sort (cost=4381.72..4390.09 rows=3347 width=4) (actual time=14.092..18.497 rows=2842 loops=1) Sort Key: f.property_id - Bitmap Heap Scan on foo f (cost=22.71..4185.78 rows=3347 width=4) (actual time=0.826..7.008 rows=2842 loops=1) Recheck Cond: (state_code = 'PA'::bpchar) - Bitmap Index Scan on mv_search_state (cost=0.00..22.71 rows=3347 width=0) (actual time=0.734..0.734 rows=2842 loops=1) Index Cond: (state_code = 'PA'::bpchar) - Sort (cost=13460.99..13732.84 rows=108742 width=4) (actual time=580.312..754.012 rows=110731 loops=1) Sort Key: fd.property_id - Seq Scan on foo_detail fd (cost=0.00..4364.42 rows=108742 width=4) (actual time=0.006..210.846 rows=108742 loops=1) Total runtime: 991.852 ms explain analyze select f.property_id from foo f inner join foo_detail fd using (property_id) where f.state_code = 'MN' Nested Loop (cost=7.62..8545.85 rows=1036 width=4) (actual time=0.877..44.196 rows=2858 loops=1) - Bitmap Heap Scan on foo f (cost=7.62..2404.44 rows=1036 width=4) (actual time=0.852..6.579 rows=2858 loops=1) Recheck Cond: (state_code = 'MN'::bpchar) - Bitmap Index Scan on mv_search_state (cost=0.00..7.62 rows=1036 width=0) (actual time=0.744..0.744 rows=2858 loops=1) Index Cond: (state_code = 'MN'::bpchar) - Index Scan using ix_fd on foo_detail fd (cost=0.00..5.92 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=2858) Index Cond: (outer.property_id = fd.property_id) Total runtime: 48.439 ms ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Parallel backups (was Re: [GENERAL] why postgresql over other RDBMS)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/24/07 17:21, Chris Browne wrote: [snip] This would permit doing a neat parallel decomposition of pg_dump: you could do a 4-way parallelization of it that would function something like the following: - connection 1 opens, establishes the usual serialized mode transaction - connection 1 dumps the table metadata into one or more files in a specified directory - then it forks 3 more connections, and seeds them with the same serialized mode state - it then goes thru and can dump 4 tables concurrently at a time, one apiece to a file in the directory. This could considerably improve speed of dumps, possibly of restores, too. What about a master thread that establishes the usual serialized mode transaction and then issues N asynchronous requests to the database, and as they return with data, pipe the data to N number of corresponding writer threads. Matching N to the number of tape drives comes to mind. Yes, the master thread would be the choke point, but CPUs and RAM are still a heck of a lot faster than disks, so maybe it wouldn't be such a problem after all. Of course, if libpq(??) doesn't handle async IO, then it's not such a good idea after all. Note that this isn't related to subtransactions... - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGViC4S9HxQb37XmcRAgkAAKC4pyZQWDF01S17uITbOkcj+KY8lgCg40pi 2B3xg2tnp554GGP0VsgACWE= =eIUP -END PGP SIGNATURE- ---(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] Limiting number of rows returned at a time in select query
Jon Clements ha escrito: Hi All. Is there a way inside a query (or connection) to limit the amount of records returned each chunk by the server? At the moment, I have 22 million records trying to be returned in one-go as the result set. I have a .NET driver that has a FetchSize option which allows the above (say returning 10k rows at a time without holding them all in memory); I'm not sure though if that's a property of the driver / server, as none of the other interfaces I have seem to include it. It just makes sense it might be server-side; although, if it's not I'm quite happy to be corrected. Any pointers are appreciated. Cheers, Jon. See: http://www.postgresql.org/docs/8.2/static/queries-limit.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Delete with subquery deleting all records
Alban Hertroys writes: Why not use EXISTS? DELETE FROM export_messages WHERE NOT EXISTS ( SELECT 1 FROM exports WHERE exports.export_id = export_messages.export_id ) Didn't think of it. Thanks for the code. I suppose you run those queries in a transaction block, right? Correct. Also I do a backup before doing the deletions. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] why postgresql over other RDBMS
On Thu, May 24, 2007 at 03:25:52PM -0400, A.M. wrote: Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? You could do this using an incredibly evil, carefully implemented hack in a connection pool. I'm shuddering at the thought of it, to be honest, so details are left as an exervisse for the reader. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Inheritance question
First, I want to confess that I am not an SQL expert or even remotely close. :-) Second, I believe I pretty much know the answer to my question, but I would like to have some confirmation if you fine people don't mind. My situation is this: I have a PHP script that some what dynamically generates two or more tables (but for the sake of this email lets stick with two), and a view to display the results of those tables. The tables might be defined like this: Table A == id bigint not null (Primary Key) b0 int not null b1 int not null b2 int not null Table B == id bigint not null (Primary Key) b0 int not null b1 int not null b2 int not null b3 int not null And the view ultimately maps those tables in combination with Table C which contains a serial column that the id in Tables A B use. The view might have been created like this: create view View1 as select c.id, a.b0 as r0, a.b1 as r1, a.b2 as r2, b.b0 as r3, b.b1 as r4, b.b2 as r5, b.b3 as r6 from TableC c left join TableA a on c.id = a.id left join TableB b on c.id = b.id This results in a virtual table/view that contains all of the columns from tables A B, in addition to another column from table C. That's fine for most of my purposes. I only recently learned about the inheritance features, and I was hoping that I could find a way to implement a table that is the child of tables A B that would allow me to map the columns as I desire rather than automatically merging them. Based on the documentation and my own testing, I would have to say that this isn't possible in 8.1 (which I currently use) or 8.2 (which I'll be moving to soon). The question, and point, is this: Is there an alternate way of accomplishing read and write functionality similar to what inheritance offers but allowing me to map the columns as I desire? The read only view is nice, but it would save me a lot of work if I could take advantage of inheritance in this way. Thanks for your time and patience! Raymond ---(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] index vs. seq scan choice?
George Pavlov [EMAIL PROTECTED] writes: I am curious what could make the PA query to ignore the index. What are the specific stats that are being used to make this decision? The frequency of the specific value being searched for, and the overall order-correlation of the column. Since the latter is not dependent on a particular value, my guess at the reason for the inconsistent results is that you don't have the column's statistics target set high enough to track all the interesting values --- or maybe just not high enough to acquire sufficiently accurate frequency estimates for them. Take a look at the pg_stats row for the column ... (The default statistics target is 10, which is widely considered too low --- you might find 100 more suitable.) 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
[GENERAL] Uhm, so, yeah, speaking of /.
Just trying to post, and getting this error message: Invalid form key: c3pVklZBr9 Chances are, you're behind a firewall or proxy, or clicked the Back button to accidentally reuse a form. Please try again. If the problem persists, and all other options have been tried, contact the site administrator. Now the facts are: I am not behind a proxy nor did I hit the back button. People who don't use /. all the time might now realize the simple truth: it's a flaky hacky site. Anybody holding it up as an example of a robust high-volume site ought to have their head examined ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why postgresql over other RDBMS
On May 24, 2007, at 20:39 , Andrew Sullivan wrote: On Thu, May 24, 2007 at 03:25:52PM -0400, A.M. wrote: Wouldn't it be a cool feature to persists transaction states across connections so that a new connection could get access to a sub- transaction state? You could do this using an incredibly evil, carefully implemented hack in a connection pool. I'm shuddering at the thought of it, to be honest, so details are left as an exervisse for the reader. Actually, a sample implementation could be done using stored procedures and some IPC. It would however require that the receiver poll for requests- the API would probably look very similar to dblink. -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Cannot get autovacuum configured
Configuring autovacuum shouldn't be so hard. :( I had a similar problem to this months ago, and I can't seem to fix it again opentaps=# SELECT name, setting from pg_settings where name like '%stats_%'; name | setting -+- stats_block_level | off stats_command_string| on stats_reset_on_server_start | off stats_row_level | on stats_start_collector | on (5 rows) opentaps=# SELECT name, setting from pg_settings where name like '%autovacuum%'; name | setting -+--- autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold| 250 autovacuum_freeze_max_age | 2 autovacuum_naptime | 60 autovacuum_vacuum_cost_delay| -1 autovacuum_vacuum_cost_limit| -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 500 (9 rows) select last_autovacuum, last_autoanalyze from pg_stat_all_tables; last_autovacuum | last_autoanalyze -+-- | [about 6 entries] [about 20 entries] | | (978 rows) It looks like I have a huge configuration issue. The database has 769 tables... And I can run vacuum from pgadminIII just fine It completes in about 30 seconds. But postgresql seems to slow down fast, so I believe it's not running correctly, # ps uax | grep postgres | grep 4: pgsql 23538 0.0 1.3 107528 55664 ?? Ss4:32PM 0:01.20 postgres: writer pgsql 23539 0.0 0.2 15352 6788 ?? Ss4:32PM 0:06.72 postgres: stats c # pkg_info | grep post postgresql-client-8.2.4 PostgreSQL database (client) postgresql-server-8.2.4 The most advanced open-source database available anywhere # uname -a FreeBSD 7.0-CURRENT FreeBSD 7.0-CURRENT #6: Mon May 21 13:56:15 EDT 2007 :/usr/obj/usr/src/sys/MYKERNEL amd64 For performance issues I was encouraged to run with Configured kernel with SCHED_ULE sched.kern.pick_pri=0 opentaps=# SELECT name, setting from pg_settings where name like '%title%'; name | setting --+- update_process_title | off CPU: Intel(R) Xeon(R) CPU E5335 @ 2.00GHz (2004.98-MHz K8-class CPU) usable memory = 4279037952 (4080 MB) avail memory = 4092456960 (3902 MB) FreeBSD/SMP: Multiprocessor System Detected: 8 CPUs acpi0: INTEL S5000XVN on motherboard 4 more Gig to be installed tomorrow, thus the 64bit OS in the mix... If someone could point me to some kind of rule of thumb, I would be ever so grateful. This is maddening that I cannot even get a starting point setup. -- Walter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] index vs. seq scan choice?
Tom Lane wrote: (The default statistics target is 10, which is widely considered too low --- you might find 100 more suitable.) Does this mean that we should look into raising the default a bit? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] index vs. seq scan choice?
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: (The default statistics target is 10, which is widely considered too low --- you might find 100 more suitable.) Does this mean that we should look into raising the default a bit? Probably ... the question is to what. The default of 10 was chosen in our usual spirit of conservatism --- and IIRC it was replacing code that tracked only *one* most common value, so it was already a factor of 10 better (and more expensive) than what was there before. But subsequent history suggests it's too small. I'm not sure I want to vote for another 10x increase by default, though. 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] index vs. seq scan choice?
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: (The default statistics target is 10, which is widely considered too low --- you might find 100 more suitable.) Does this mean that we should look into raising the default a bit? Probably ... the question is to what. The default of 10 was chosen in our usual spirit of conservatism --- and IIRC it was replacing code that tracked only *one* most common value, so it was already a factor of 10 better (and more expensive) than what was there before. But subsequent history suggests it's too small. I'm not sure I want to vote for another 10x increase by default, though. Outside of longer analyze times, and slightly more space taken up by the statistics, what is the downside? I mean in reality... what is setting to 100 going to do to effect actual production usage of even a modest machine? Sincerely, Joshua D. Drake 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 -- === 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
Re: [GENERAL] index vs. seq scan choice?
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not sure I want to vote for another 10x increase by default, though. Outside of longer analyze times, and slightly more space taken up by the statistics, what is the downside? Longer plan times --- several of the selfuncs.c routines grovel over all the entries in the pg_statistic row. AFAIK no one's measured the real impact of that, but it could easily be counterproductive for simple queries. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] index vs. seq scan choice?
On May 24, 2007, at 8:26 PM, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not sure I want to vote for another 10x increase by default, though. Outside of longer analyze times, and slightly more space taken up by the statistics, what is the downside? Longer plan times --- several of the selfuncs.c routines grovel over all the entries in the pg_statistic row. AFAIK no one's measured the real impact of that, but it could easily be counterproductive for simple queries. The lateness of the hour is suppressing my supposed statistics savvy, so this may not make sense, but... Would it be possible to look at a much larger number of samples during analyze, then look at the variation in those to generate a reasonable number of pg_statistic samples to represent our estimate of the actual distribution? More datapoints for tables where the planner might benefit from it, fewer where it wouldn't. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
