Re: [GENERAL] script variables
Paul Cunningham wrote: I use a bash script (similar to following example) to update tables. psql -v passed_in_var=\'some_value\' -f script_name Is it possible to pass a value back from psql to the bash script? You can pass the textual output of psql back with backticks: echo `psql ...` -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] New to Schemas - Good for Multi Company in one DB ?
Hi, Im a convert from Firebird so I consider myself a newbie to Postgresql. We have a requirement to host 400 600 companies data inside a single database for connection pooling and scalability reasons as well as our business logic requirements. We have therefore been very busy adding a company id to each relevant table and adjusting all our queries to be company specific such that if a company says show me all my clients we would use a query such as Select * from client where comp_id = CompA But, Ive just discovered Postgresql Schemas . If I were to create a schema for each company and therefore remove the comp_id from our tables and sql would this work ? Could we have 600 schemas in the db ? Would performance be hindered ? If this is OK what is the best way to maintain all the db structures ? In other words if I have an update script do I need to run it against each schema ? I would realy appreciate someones help with this asap .. Thanks Paul Newman (Development Director Tripoint Ltd)
Re: [GENERAL] plpython function problem workaround
I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. I tried this function, as an example: create or replace function crtest() returns int as $$ x=plpy.execute(select * from pg_proc where prosrc like '%\r%') return x.nrows() $$ language 'plpythonu' and it considered the \r to be text and not a CR. Therefore the update function removed the CR at the end of the line but ignored the \r that was specifically mentioned. Is there any case that someone can think of where an actual CR, not a textual representation of it, would purposefully be put into the code? Sim Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sim Zacks [EMAIL PROTECTED] writes: I don't think that this type of solution should be discussed as an official patch. If it was, I would recommend solving the problem in source code when the function is passed to the translator. Indeed, but first we need to work out what the necessary translation is. A hack using a VALIDATOR function isn't an unreasonable way to prototype the conversion logic. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Convert Cursor to array
Title: RE: [GENERAL] Convert Cursor to array ok, i think that i will try to use pl/perl or pl/python but i don't know which one is better for my problem maybe you can help me. thanks -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Richard Huxton Envoyé : lundi 14 mars 2005 09:31 À : GIROIRE Nicolas (COFRAMI) Cc : 'pgsql-general@postgresql.org' Objet : Re: [GENERAL] Convert Cursor to array GIROIRE Nicolas (COFRAMI) wrote: my sort algorithm is a very particular solution. In fact, I put record in array to allow me to sort record. I sort them by using one column of my table but the column indx can have duplicate value and it's volontary. I still can't see why you can't sort by that column with order by There's nothing obvious in your description that rules that out. Are you aware that PostgreSQL offers functional and partial indexes? They can make complex selection/ordering quite efficient. here is the order algorithm : for i in 1..recordcount loop tmp_row := children[i]; indx := tmp_row[5]; if (indxi) then -- on déplace les éléments pour laisser la place à l'élément qu'on déplace for j in 0..(i-indx-1) loop children[i-j] := children[i-j-1]; end loop; -- on met l'élément à sa nouvelle position children[indx] := tmp_row; end if; end loop; It's particular to my system. I won't go to explain my choice but if someone has an idea to save time. If you are going to sort, this looks a particularly slow algorithm for large values of recordcount. Personally, I'd use plperl/plpython or something with built-in hash sorting capabilities. That will almost certainly be tens or hundreds of times faster. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message.
Re: [GENERAL] plpython function problem workaround
Now that I think about it, FTP programs currently do this when they transfer ASCII files. If you have a planned CR in your program and FTP from mac to windows the CR becomes a CRLF and if you FTP from mac to unix the CR changes to an LF. Sim Zacks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. I tried this function, as an example: create or replace function crtest() returns int as $$ x=plpy.execute(select * from pg_proc where prosrc like '%\r%') return x.nrows() $$ language 'plpythonu' and it considered the \r to be text and not a CR. Therefore the update function removed the CR at the end of the line but ignored the \r that was specifically mentioned. Is there any case that someone can think of where an actual CR, not a textual representation of it, would purposefully be put into the code? Sim Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sim Zacks [EMAIL PROTECTED] writes: I don't think that this type of solution should be discussed as an official patch. If it was, I would recommend solving the problem in source code when the function is passed to the translator. Indeed, but first we need to work out what the necessary translation is. A hack using a VALIDATOR function isn't an unreasonable way to prototype the conversion logic. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] Convert Cursor to array
FERREIRA William (COFRAMI) wrote: ok, i think that i will try to use pl/perl or pl/python but i don't know which one is better for my problem Whichever you know better is the best solution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, Mar 14, 2005 at 06:04:01PM -0500, Chris Browne wrote: [EMAIL PROTECTED] (Mark Rae) writes: Clients 1 2 3 4 6 812163264 128 -- mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 Could you elaborate on what the measures are here? I don't quite follow what 0.8 means as compared to 2.38. The figures are generated from a representative set of ~500 queries which each client issues in a random order. The clients all connect in parallel and the elapsed time taken for them all to complete is recorded. The numbers given above are these times converted into relative throughput figures. The baseline being a single mysql client, a performance of 1.00 is equivalent to an average of 5.82 queries per second. i.e. with a single client postgres runs at 65% the speed of mysql by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster and once we get to 32 clients, mysql is tripping over itself and is actually running slower than if the clients ran one after another. Looking at how the database scales, i.e. all figures are relative to the speed of a single client connection. Clients 1 2 3 4 6 812163264 128 -- Theoretical 1.00 2.00 3.00 4.00 4.00 4.00 4.00 4.00 4.00 4.00 4.00 mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 1.00 1.94 2.91 3.79 3.75 3.82 3.80 3.84 3.82 3.66 3.64 The theoretical line being is how much we should expect things to scale given that this is a 4 cpu machine. -Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Wierd error message
I'm working with mod_python and pygresql (although I get virtualy the same error with psycopg too) Anyone know what the following error actualy means: Exception pg.InternalError: 'Connection already closed' in ignored Does it mean the obvious: Some code tries to access a connection object that was already closed? but why the in ignored then? Thanks, Alex Turner netEconomist ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpython function problem workaround
The following is how I understand it, but please let's delay further
discussion until someone tests the program under Windows.
On Mon, 14 Mar 2005, Michael Fuhr wrote:
Hmmm...I think that would be inconsistent with previous reports.
For example, in the following message, the poster said that everything
(PostgreSQL, pgAdmin) was running on Windows 2003:
http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00066.php
I'm sorry, he's wrong.
The initial report was by Hong Yuan:
http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php
later he clarified:
http://archives.postgresql.org/pgsql-general/2005-01/msg00858.php
I am using pgAdmin III Version 1.2.0 under Chinese Windows XP, while
the database is 7.4.6 under Linux.
BTW I just noticed someone else provided a simpler example:
http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php
someone should try and complile those under windows.
I suggested that he strip the CRs from pg_proc.prosrc and he said
it worked:
http://archives.postgresql.org/pgsql-interfaces/2005-03/msg00014.php
It's not clear that the test in the second message was run on a
Windows server (apparently pgAdmin was run on a Windows client),
but I think the beginning of the message is saying that he didn't
reply for so long because he didn't have access to a Windows server.
From that I infer that he tried my suggestion and posted the results
when he finally did get access to a customer's Windows server. I
could be misreading that, however.
I have no idea of where Michele Bendazzoli ran that code. He's not
the original poster, tho.
A couple of months ago Stuart Bishop brought this issue up in
python-dev. Most of the thread is along the lines of strip the
carriage returns:
http://mail.python.org/pipermail/python-dev/2005-January/051203.html
Sorry again, but he's assuming Unix on the server side, and
Windows or Mac on the client side.
If anyone manages to compile the following code on Windows...
...
I bet on windows the first program fails and the second is ok.
Hopefully somebody will do a Windows test of the code you posted.
I'd find it bizarre that the Python code embedded in a C program
had to care whether it was running on *nix or Windows.
I find it perfectly consistent! Face it, _any_ C program that's handling
data of type _text_ has to know which platform it is running on. If you
don't like the behaviour of Python functions, think of printf().
C programmers under Windows are used to write:
printf(Hello World!\r\n);
as much (old) Mac programmers write:
printf(Hello World!\r);
and Unix programmers write:
printf(Hello World!\n);
_ANY_ C program that processes multiline text input has to know which
platform it is running on, otherwise it reads or proceduces garbage.
Python just requires the input being text, which seems reasonable to me,
since, by design, a python program is more that just a sequence of ';'-
separated statements (ala C or Perl), with _optional_ intermixing
whitespaces. White spaces ('eol' included) do have a meaning in Python
syntax.
BTW, this attitude reminds me of PostgreSQL strict validation of input,
compared to more relaxed checking made by MySQL. I really don't feel
the need to enumerate the merits of input validation on this list.
Python functions want 'valid text' and the definition is platform
dependent, unfortunately. Why should it be relaxed, and accept invalid
text as input?
If you are to compile any unix text processing utility, such as grep,
on a mac, you'll have to change the source and have the program split
lines at \r (under windows, splitting lines at \n still works, but
leaves a spurious \r around). Python is the same, since it expects
programs as _text_.
The universal newline support is totally another matter. And it applies
to files only (it's a wrapper around file functions). It's a tool
for lazy programmers.
It is possible that in the feature the Python lexxer is changed to
recognize different line endings (since there's no valid case for a
\r at the end of a line that I can think of). But requiring the
input being text is not 'bizarre' at all.
The issue about text representation affects _any_ application.
Treating text as binary data is plain wrong, IMHO, and will always
lead to problems.
.TM.
--
/ / /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_/ _/ _/ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Localization problems between Access and PostgresSQL 8
Hello, I've dumped the content of MS-Access 2002 SP3tables on a PC with Windows XP Pro in French localization.Then I COPY these files, on the same PC hosting an PostgreSQL 8.0.1 database.I've problems with the accents !? Why ? What kind of encoding must I use to create the PG database under Win XP pro French ?Thanks for all your commentsLuc
Re: [GENERAL] Question about database restrict
On Tue, Mar 15, 2005 at 12:57:52 +0800, Qingqing Zhou [EMAIL PROTECTED] wrote: Yu Jie [EMAIL PROTECTED] writes: Hi all, If I want to restrict the maximum size of one table to 5MB, restrict the maximum size of database file to 100MB, how can I do that restriction? Is PostgreSQL8.0 support this function? I can't find it in the manual of PostgreSQL. PG does not support this function directly. However, you may use OS provided functions to do this more or less. However, this isn't really a good idea as if the database runs out of disk space cleanup might not be fun. Perhaps if you explain more about what you are doing we might have some other possible solutions for you? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Le mardi 15 mars 2005 à 12:26 +, Mark Rae a écrit : Clients 1 2 3 4 6 812163264 128 -- mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster That is very interesting!!! I have several webapps on my server each one opens several queries to the database from _each_ JSP - often more than three... So the hunch that I had all along was right: PostgreSQL is a much better back end for Tomcat/JSP than MySQL. Cheers Tony ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Localization problems between Access and PostgresSQL
Secrtariat wrote: Hello, I've dumped the content of MS-Access 2002 SP3 tables on a PC with Windows XP Pro in French localization. Then I COPY these files, on the same PC hosting an PostgreSQL 8.0.1 database. I've problems with the accents !? Why ? Luc, what encoding did you set for your PostgreSQL database? Did you set client encoding in the dump file to the Frech encoding that your Windows use? What kind of encoding must I use to create the PG database under Win XP pro French ? I think you shouldn't use Unicode if you need correct collating order. In this case you should use French encoding aswell (my opinion). Thanks for all your comments Luc Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 3: 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] Internal account lookup failure
hello on windows 2000 (chinese) during postgres8.0.0-rc1.msi installation, it fails in 'Service configuration': Internal account lookup failure sylvain smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Internal account lookup failure
sferriol wrote: hello on windows 2000 (chinese) during postgres8.0.0-rc1.msi installation, it fails in 'Service configuration': Internal account lookup failure Is there a reason for not using version 8.0.1? Maybe this one will install without problem. sylvain Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Filesystem level backup and 32 / 64 bit
I have situation where I have one box with linux native 64 bit distribution (Gentoo on AMD) running pg 8.x and other box running a 32 bit distro running version of pg 8.x Is it posssible to take a filesystem level backup (copyiing all in $PGDATA directory) from 64 bit system and use it as $PGDATA in the native 32 bit system with the same version of postgresql ? Ie to have one big 64 bit server and eventually a small but cheap 32 bit box as an emergency backup ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Localization problems between Access and PostgresSQL
Le mardi 15 mars 2005 à 13:52 +0100, Secrétariat a écrit : I've dumped the content of MS-Access 2002 SP3 tables on a PC with Windows XP Pro in French localization. Then I COPY these files, on the same PC hosting an PostgreSQL 8.0.1 database. I've problems with the accents !? Why ? What kind of encoding must I use to create the PG database under Win XP pro French ? I always create my database as -E LATIN1 because I have a mix of server versions and OS on client and server (Macs mostly). This is the only way I have managed to keep everything clean. I use functions in my JSPs to get the right accents in output. http://www.fracdespaysdelaloire.com database links on the bottom of the page are an example of JSP accessing French text in PostgreSQL with data being input from Macs. We use a mix of OpenOffice.org and pgaccess with no accent problems. Salut Tony ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 08:09:47AM +0200, Sim Zacks wrote: I don't think that this type of solution should be discussed as an official patch. If it was, I would recommend solving the problem in source code when the function is passed to the translator. That way each platform could fix the code to work with as is needed and the code would be portable. I ran into this problem about half a year ago and it didn't go anywhere, then I saw a long discussion about it that also didn't go anywhere. I had given up on using plpython until now because I actually need it. I'd like to insert one note here. While I'm not particularly familiar with either perl or python, when I encountered the referred-to thread regarding Windows/Unix newline incompatibilities in Python, I ran some tests on my Linux system. I wrote some very simple scripts in Perl and Python and tested them. They ran OK when I saved them in native Linux newlines, they ran, but if I saved them in DOS mode (vim), neither the Python nor the Perl scripts would run. I noted that some of the writers in that thread were critical of Python for not taking care of the conversions, and it may be that the Perl interfaces in postgresql may take care of this, I have not tried this, but it seems that my tests, if valid, indicate that this problem is not restricted to Python alone as far as the interpreters are concerned. This observation may have nothing to do with the pl/Perl and pl/Python interfaces, but I thought I'd pass this along FWIW. So I figured out how to make it work and thought that it would be helpful to others, but I couldn't figure out how to automate the fix. Tom Lane [EMAIL PROTECTED] wrote in message I don't know Python at all, so I don't know how complicated its lexical structure is, but ISTM you'd at least need enough smarts to distinguish literals from unquoted whitespace. The other small fly in the ointment is that when the server is running on Windows, I suppose we would have to *put in* rather than remove CRs. Sim probably doesn't care about that case, but we couldn't accept an official patch that doesn't handle it. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Filesystem level backup and 32 / 64 bit
On Tue, Mar 15, 2005 at 02:29:51PM +0100, NTPT wrote: I have situation where I have one box with linux native 64 bit distribution (Gentoo on AMD) running pg 8.x and other box running a 32 bit distro running version of pg 8.x Is it posssible to take a filesystem level backup (copyiing all in $PGDATA directory) from 64 bit system and use it as $PGDATA in the native 32 bit system with the same version of postgresql ? Doubt it, it's not explicitly supported. It might work, but padding changes, certain width changes, may screw you up. Ie to have one big 64 bit server and eventually a small but cheap 32 bit box as an emergency backup ? Use something like Slony, it's specifically supports this kind of usage... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpksLawR0pa6.pgp Description: PGP signature
[GENERAL] CURRENT_TIME
Title: RE: [GENERAL] Convert Cursor to array hi i need to know the execution time of a part of my pl/pgsql code so, i tried to use CURRENT_TIME : t_time_d TIME;t_time_f TIME;t_diff TIME; select CURRENT_TIME into t_time_d; //the traitment select CURRENT_TIME into t_time_f;t_diff=t_time_f-t_time_d;raise notice 'DEBUT : % | FIN : % | DIFF : % | POUR : %',t_time_d,t_time_f,t_diff; my function is recursive and the problem is that i always get the same result for each iteration t_time_d is set at the first iteration and doesn't change thanks
Re: [GENERAL] Problem with special character
Hi Gnari, I'll do some more test tonight to figure out if it's a tomcat problem and I'll get back to you with this info. Thanks! /David maybe some difference in the environments that the two tomcats run in? are their locales the same ? gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] pg/plsql question
Hello all I am trying to grant privs to a user on all tables. I think I understood there was no command to do that :// so I wrote the following: create or replace function granting() RETURNS integer AS ' declare v_schema varchar; v_user varchar; begin v_user := user v_schema := public FOR t in select tablename from pg_tables where schemaname = v_schema LOOP grant select on t to v_user; END LOOP; return 1; end; ' LANGUAGE plpgsql; I then login to psql, and do a \i myscript.sql. It returns CREATE FUNCTION, but I cannot see anything. The tables are not granted, etc... Also I am trying to find out how to debug this. How can I print out to STDOUT the value of t for example? Thanks for any help Best, fred signature.asc Description: This is a digitally signed message part
Re: [GENERAL] CURRENT_TIME
On Mar 15, 2005, at 23:21, FERREIRA William (COFRAMI) wrote: i need to know the execution time of a part of my pl/pgsql code so, i tried to use CURRENT_TIME : snip / my function is recursive and the problem is that i always get the same result for each iteration t_time_d is set at the first iteration and doesn't change You're probably looking for timeofday(). See the docs at http://www.postgresql.org/docs/8.0/interactive/functions- datetime.html#FUNCTIONS-DATETIME-CURRENT Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function problem workaround
Sim Zacks [EMAIL PROTECTED] writes: I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. Does python actually disallow newlines in string literals? That is x = 'foo bar' Whether you think this is good style is not the question --- is it allowed by the language? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pg/plsql question
Hi Fred, On Mar 15, 2005, at 9:35 AM, Fred Blaise wrote: I am trying to grant privs to a user on all tables. I think I understood there was no command to do that :// so I wrote the following: You can find some code to do this here: http://pgedit.com/node/view/20 I then login to psql, and do a \i myscript.sql. It returns CREATE FUNCTION, but I cannot see anything. The tables are not granted, etc... Also I am trying to find out how to debug this. How can I print out to STDOUT the value of t for example? Unfortunately, you can't print to stdout because the procedure is executed on the database server. About the best you can do is to is to use a raise log statement: raise log ''t is %'', t; This will write to the PostgreSQL log. Be sure to declare t -- this may be one of your problems. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Filesystem level backup and 32 / 64 bit
Martijn van Oosterhout kleptog@svana.org writes: On Tue, Mar 15, 2005 at 02:29:51PM +0100, NTPT wrote: Is it posssible to take a filesystem level backup (copyiing all in=20 $PGDATA directory) from 64 bit system and use it as $PGDATA in the native= 32 bit system with the same version of postgresql ? Doubt it, it's not explicitly supported. It might work, but padding changes, certain width changes, may screw you up. It might work if the main server were compiled as a 32-bit application ... but AFAIK the Intel-ish 64bit architectures mostly suck in 32-bit emulation mode, so you'd not want to do that. It will almost certainly *not* work to just cram 64-bit files onto a 32-bit machine, because the file layout is dependent on MAXALIGN which will likely be different. Use something like Slony, it's specifically supports this kind of usage... Agreed. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pg/plsql question
On Tue, 2005-03-15 at 09:58 -0500, John DeSoi wrote: Hi Fred, On Mar 15, 2005, at 9:35 AM, Fred Blaise wrote: I am trying to grant privs to a user on all tables. I think I understood there was no command to do that :// so I wrote the following: You can find some code to do this here: http://pgedit.com/node/view/20 nice :) I then login to psql, and do a \i myscript.sql. It returns CREATE FUNCTION, but I cannot see anything. The tables are not granted, etc... Also I am trying to find out how to debug this. How can I print out to STDOUT the value of t for example? Unfortunately, you can't print to stdout because the procedure is executed on the database server. About the best you can do is to is to use a raise log statement: raise log ''t is %'', t; Yes, that's what I thought... but oddly nothing gets written. I see other things get written to the postgres log, but not those. I have tried raise log and raise notice. This will write to the PostgreSQL log. Be sure to declare t -- this may be one of your problems. Declared now as varchar. Just to make sure... Once the function is created, you would call it as 'execute function()' from psql, correct? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL Thanks a lot fred signature.asc Description: This is a digitally signed message part
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 09:46:54AM -0500, Tom Lane wrote: Sim Zacks [EMAIL PROTECTED] writes: I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. Does python actually disallow newlines in string literals? That is x = 'foo bar' Whether you think this is good style is not the question --- is it allowed by the language? You can do x = this has newlines embedded -- Alvaro Herrera ([EMAIL PROTECTED]) Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo (Jaime Salinas) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg/plsql question
On Mar 15, 2005, at 10:19 AM, Fred Blaise wrote: Just to make sure... Once the function is created, you would call it as 'execute function()' from psql, correct? Try: select function(); As a top level SQL command, EXECUTE is for executing prepared statements: http://www.postgresql.org/docs/8.0/interactive/sql-execute.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg/plsql question
Fred Blaise [EMAIL PROTECTED] writes:
On Tue, 2005-03-15 at 09:58 -0500, John DeSoi wrote:
raise log ''t is %'', t;
Yes, that's what I thought... but oddly nothing gets written.
Fred, your original example made it look like you were writing
(one double quote mark) where what you need to write is ''
(two single quote marks). The reason is that you are trying to
embed a single quote mark in the value of a string literal.
(If you are using PG 8.0 I'd suggest adopting the dollar-quoting
style for entering the function body, instead.)
Another problem I noticed is you were leaving off required
statement-ending semicolons, which could also prevent the plpgsql
parser from recognizing the RAISE command properly.
You might try something simpler just to get your feet wet:
create function hello_world(text) returns text as '
begin
raise notice ''I got %'', $1;
return $1;
end' language plpgsql;
select hello_world('Hi there!');
Once you get past that you'll have some idea about the quote marks
anyway ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
On Tue, 2005-03-15 at 07:33 -0600, David wrote: [about the line-termination problem in plpython] I'd like to insert one note here. While I'm not particularly familiar with either perl or python, when I encountered the referred-to thread regarding Windows/Unix newline incompatibilities in Python, I ran some tests on my Linux system. I wrote some very simple scripts in Perl and Python and tested them. They ran OK when I saved them in native Linux newlines, they ran, but if I saved them in DOS mode (vim), neither the Python nor the Perl scripts would run. actually, perl scripts with \r\n line endings will run just fine in unix/linux. what you might have been experiencing, is the fact that the unix shell is expecting \n lineendings, and the #! line could have been failing to run the perl executable. gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Dump all in several files
Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. With version 7.3 of PostgreSQL (and the following) the data are not recorded any more in a repertory with the name of the database, but with the OID of the basebase. Do you know how I can adapt this script? If a developer of pg_dumpall reads this post, is it possible to add to the command pg_dumpall an option to record one database per file? Regards. #! /bin/bash # for p in $(find /var/lib/postgres/data/base/ -type d -print -mindepth 1 -maxdepth 1 ) do base=`basename $p` pg_dump -d -f /var/backup/postgresql/dump_$base $base done -- == | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:[EMAIL PROTECTED] | ===Debian=GNU/Linux=== ---(end of broadcast)--- TIP 3: 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] Dump all in several files
On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. With version 7.3 of PostgreSQL (and the following) the data are not recorded any more in a repertory with the name of the database, but with the OID of the basebase. Do you know how I can adapt this script? If a developer of pg_dumpall reads this post, is it possible to add to the command pg_dumpall an option to record one database per file? Regards. #! /bin/bash # for p in $(find /var/lib/postgres/data/base/ -type d -print -mindepth 1 -maxdepth 1 ) do base=`basename $p` pg_dump -d -f /var/backup/postgresql/dump_$base $base done Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME $DB_NAME where $DB_NAME is the name of each database on the box? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dump all in several files
Lonni J Friedman wrote: On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. [...] Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME $DB_NAME where $DB_NAME is the name of each database on the box? This script is called in a crontab the every day at 6 o'clock in the morning. -- == | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:[EMAIL PROTECTED] | ===Debian=GNU/Linux=== ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres jobs mailing list?
On Friday 11 March 2005 18:49, Jerry Sievers wrote:
Hello.
The PG mailing lists web page contains artifacts of a jobs list but no
such list appeared in the dropdown of available lists.
I am referring to;
http://www.postgresql.org/community/lists/subscribe
This has been fixed now, thanks for bringing it up.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: 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] Dump all in several files
On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote: Lonni J Friedman wrote: On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME $DB_NAME where $DB_NAME is the name of each database on the box? This script is called in a crontab the every day at 6 o'clock in the morning. You can obtain the list of databases for scripting with psql -tlA | cut -d\| -f1 -- Alvaro Herrera ([EMAIL PROTECTED]) Si quieres ser creativo, aprende el arte de perder el tiempo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Dump all in several files
On Tue, 15 Mar 2005 17:01:39 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Lonni J Friedman wrote: On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot [EMAIL PROTECTED] wrote: Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. [...] Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME $DB_NAME where $DB_NAME is the name of each database on the box? This script is called in a crontab the every day at 6 o'clock in the morning. i have a /etc/backup/data.pgsql where i list all databases i want to dump, then a script in /etc/cron.daily reads this file and call pg_dump: pg_dump -U postgres $1$1.dump.sql javier ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython function problem workaround
On Tue, 15 Mar 2005, Tom Lane wrote: Sim Zacks [EMAIL PROTECTED] writes: I've been looking at the possibility of having a planned CR in the source code and I don't see a case where it would happen. Does python actually disallow newlines in string literals? That is x = 'foo bar' Whether you think this is good style is not the question --- is it allowed by the language? You can with triple-quoting and by escaping it with backslash. The following code, admitedly ugly, is valid python: a = 'a\ bc' print a b = '''a bc''' print b and produces: abc a bc as output. \newline in any non raw literal is allowed and ignored, while a bare newline in a triple-quoted string literal is allowed and retained. Moreover, this is not an execise of bad style only. It's customary to write docstrings as multiline triple-quoted string literals: def afunction(a, b, c): This is a function. Its arguments are: a - first argument b - second argument c - third argument. It does ans returns nothing. pass It's more or less the recommended way to document a function (or class or module or whatever). See PEP 257 for more examples: http://www.python.org/peps/pep-0257.html So, to answer to your question, newlines are more than allowed in string literals. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Problems building postgresql 8.0.1 on OS X 10.3.8
Title: Problems building postgresql 8.0.1 on OS X 10.3.8 Hi, I get the following error message when trying to build postgresql: checking for C compiler default output... configure: error: C compiler cannot create executables I have installed XCode which has the gcc compiler. I use the following configure command to start the build process: ./configure --with-perl --with-python --with-tcl --without-tk --with-openssl --without-readline --enable-debug ANT=/library/ant/bin/ant I have used the above combination before with no problems. What is the issue here and how can I resolve it? regards Uzo
Re: [GENERAL] Dump all in several files
On Tue, 15 Mar 2005 12:15:39 -0400, Alvaro Herrera
[EMAIL PROTECTED] wrote:
On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote:
Lonni J Friedman wrote:
On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot
[EMAIL PROTECTED] wrote:
Hi,
On the PostgreSQL 6.5 server I use this shell script (see below) for the
backup all of the database in several files, one file per database.
Maybe i'm just not following you, but why can't you just do:
pg_dump $DBNAME $DB_NAME
where $DB_NAME is the name of each database on the box?
This script is called in a crontab the every day at 6 o'clock in the
morning.
You can obtain the list of databases for scripting with
psql -tlA | cut -d\| -f1
i didn't know that. very nice. try:
psql -Upostgres -tlA |cut -d\| -f1|xargs -i pg_dump -Upostgres -f
'{}'.dump.sql '{}'
javier
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Dump all in several files
Alvaro Herrera wrote: On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote: [...] You can obtain the list of databases for scripting with psql -tlA | cut -d\| -f1 Great !!! :o) Thank you. -- == | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:[EMAIL PROTECTED] | ===Debian=GNU/Linux=== ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Filesystem level backup and 32 / 64 bit
On Tue, 2005-03-15 at 07:29, NTPT wrote: I have situation where I have one box with linux native 64 bit distribution (Gentoo on AMD) running pg 8.x and other box running a 32 bit distro running version of pg 8.x Is it posssible to take a filesystem level backup (copyiing all in $PGDATA directory) from 64 bit system and use it as $PGDATA in the native 32 bit system with the same version of postgresql ? Ie to have one big 64 bit server and eventually a small but cheap 32 bit box as an emergency backup ? Following up on what Tom wrote, I'd suggest using Slony-I to keep the two machines in sync. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] New to Schemas - Good for Multi Company in one DB ?
On Tue, 2005-03-15 at 02:14, Paul Newman wrote: Hi, Im a convert from Firebird so I consider myself a newbie to Postgresql. We have a requirement to host 400 600 companies data inside a single database for connection pooling and scalability reasons as well as our business logic requirements. We have therefore been very busy adding a company id to each relevant table and adjusting all our queries to be company specific such that if a company says show me all my clients we would use a query such as Select * from client where comp_id = CompA But, Ive just discovered Postgresql Schemas . If I were to create a schema for each company and therefore remove the comp_id from our tables and sql would this work ? Could we have 600 schemas in the db ? Would performance be hindered ? If this is OK what is the best way to maintain all the db structures ? In other words if I have an update script do I need to run it against each schema ? I just set up a simple test that created 500 or so schemas and the performance seemed fine to me. I imagine your system catalogs will be a fair bit bigger than if you had it all in one table, but the payoff is that when you're looking for the data for one customer you don't have to go through a huge table of 599 other customers just to get their data. I'm betting multiple schemas will be a win as long as you aren't needing to union a bunch of schemas together all the time. And yes, an update script would have to hit each schema. If the data between different companies aren't related then schemas would seem a nice solution to me. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 01:40:23PM +0100, Marco Colombo wrote: On Mon, 14 Mar 2005, Michael Fuhr wrote: Hmmm...I think that would be inconsistent with previous reports. For example, in the following message, the poster said that everything (PostgreSQL, pgAdmin) was running on Windows 2003: http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00066.php I'm sorry, he's wrong. Wrong about what? He reported that he was having the same problem and that both client and server were running on Windows 2003. Here's his first message: http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00063.php The initial report was by Hong Yuan: http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php That was a different thread. Same problem, but an earlier thread that Michele apparently didn't know about until I mentioned it. later he clarified: http://archives.postgresql.org/pgsql-general/2005-01/msg00858.php I am using pgAdmin III Version 1.2.0 under Chinese Windows XP, while the database is 7.4.6 under Linux. A problem with Windows = Linux doesn't preclude the same problem from happening with Windows = Windows. At issue is that pgAdmin on Windows apparently adds carriage returns, and whether Python on any platform doesn't like that (that's what we're still trying to determine). BTW I just noticed someone else provided a simpler example: http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php That somebody was me. I have no idea of where Michele Bendazzoli ran that code. He's not the original poster, tho. He was the original poster of *his* thread, the one with a subject of plpythonu strange syntax error. He wasn't the first person to report the problem, but his first message didn't reference any previous messages. http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00063.php I'll postpone commenting on the rest until we find out how the example programs run on Windows. If nobody follows up here then maybe I'll wander over to comp.lang.python. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython function problem workaround
On Tue, 15 Mar 2005, Michael Fuhr wrote: [...] That somebody was me. Ok, sorry. I'll postpone commenting on the rest until we find out how the example programs run on Windows. If nobody follows up here then maybe I'll wander over to comp.lang.python. Yeah, there's no point in discussing until we have some real world data. I can't compile on windows, so I'll have to wait someone else to do that. I'm basing my opinions on Python documentation only. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] Massive performance differences
Hi all, I'm running the same database on two systems: A) Debian PostgreSQL 7.4.7 B) SuSE PostgreSQL 7.3.4 Both machines have approx. 1GHz and 1GB RAM. The amount of data is almost equal (+- 10%). But I'm facing huge performance differences. For instance, a simple sequential scan results in the following query plans: explain analyze select * from veranstaltung_original order by semester; A) Sort (cost=734.74..747.77 rows=5210 width=232) (actual time=89.935..92.730 rows=5210 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..413.10 rows=5210 width=232) (actual time=0.011..7.852 rows=5210 loops=1) Total runtime: 96.900 ms B) Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual time=2568.10..2573.02 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467 width=223) (actual time=1936.68..2506.83 rows=5467 loops=1) Total runtime: 2579.08 msec Could this be due to the different PostgreSQL versions? Is there a typical cause for such performance problems? How can I find out what's wrong with installation (B)? Thanks in advance! -- Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pg/plsql question
While I have accomplished what I needed with the pgedit script given by John, I am still curious as to why mine is not working... Here is the latest version: /* */ create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_t varchar; begin v_user := ''user''; v_schema := ''public''; FOR v_t in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', t; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; Please note that all ticks above are single ticks. Here is what I do to execute it: excilan=# \i grant.sql CREATE FUNCTION excilan=# select fred_on_all(); ERROR: missing .. at end of SQL expression CONTEXT: compile of PL/pgSQL function fred_on_all near line 8 If anyone could shade some lights... Much appreciated. fred On Tue, 2005-03-15 at 15:35 +0100, Fred Blaise wrote: Hello all I am trying to grant privs to a user on all tables. I think I understood there was no command to do that :// so I wrote the following: create or replace function granting() RETURNS integer AS ' declare v_schema varchar; v_user varchar; begin v_user := user v_schema := public FOR t in select tablename from pg_tables where schemaname = v_schema LOOP grant select on t to v_user; END LOOP; return 1; end; ' LANGUAGE plpgsql; I then login to psql, and do a \i myscript.sql. It returns CREATE FUNCTION, but I cannot see anything. The tables are not granted, etc... Also I am trying to find out how to debug this. How can I print out to STDOUT the value of t for example? Thanks for any help Best, fred signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Massive performance differences
Andreas Hartmann wrote: Hi all, I'm running the same database on two systems: A) Debian PostgreSQL 7.4.7 B) SuSE PostgreSQL 7.3.4 Both machines have approx. 1GHz and 1GB RAM. The amount of data is almost equal (+- 10%). But I'm facing huge performance differences. For instance, a simple sequential scan results in the following query plans: [...] I just imported a dump of (B) into (A) to be sure to have the same data set. When I executed the query on (A) the first time, it was as slow as on (B). But the next time it was significantly faster: vvz_dev= explain analyze select * from veranstaltung_original order by semester; QUERY PLAN --- Sort (cost=587.08..600.74 rows=5467 width=229) (actual time=3188.975..3192.020 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..247.67 rows=5467 width=229) (actual time=2.330..1587.832 rows=5467 loops=1) Total runtime: 3205.281 ms (4 Zeilen) vvz_dev= explain analyze select * from veranstaltung_original order by semester; QUERY PLAN Sort (cost=587.08..600.74 rows=5467 width=229) (actual time=95.061..98.143 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..247.67 rows=5467 width=229) (actual time=0.015..7.749 rows=5467 loops=1) Total runtime: 102.948 ms (4 Zeilen) This leads to the idea that it might be a caching problem. How can I confirm this? -- Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Massive performance differences
On Tue, 2005-03-15 at 11:10, Andreas Hartmann wrote: Hi all, I'm running the same database on two systems: A) Debian PostgreSQL 7.4.7 B) SuSE PostgreSQL 7.3.4 Both machines have approx. 1GHz and 1GB RAM. The amount of data is almost equal (+- 10%). But I'm facing huge performance differences. For instance, a simple sequential scan results in the following query plans: explain analyze select * from veranstaltung_original order by semester; A) Sort (cost=734.74..747.77 rows=5210 width=232) (actual time=89.935..92.730 rows=5210 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..413.10 rows=5210 width=232) (actual time=0.011..7.852 rows=5210 loops=1) Total runtime: 96.900 ms B) Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual time=2568.10..2573.02 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467 width=223) (actual time=1936.68..2506.83 rows=5467 loops=1) Total runtime: 2579.08 msec Could this be due to the different PostgreSQL versions? Is there a typical cause for such performance problems? How can I find out what's wrong with installation (B)? I would expect your I/O subsystem and or kernel revision are more likely the cause of differences here than the pg version. Note that the time spent is in the seq scan, not the sort. Otherwise I'd worry about what version of sort was being used. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 06:03:01PM +0100, Marco Colombo wrote:
On Tue, 15 Mar 2005, Michael Fuhr wrote:
I'll postpone commenting on the rest until we find out how the
example programs run on Windows. If nobody follows up here then
maybe I'll wander over to comp.lang.python.
Yeah, there's no point in discussing until we have some real world
data. I can't compile on windows, so I'll have to wait someone else
to do that. I'm basing my opinions on Python documentation only.
I've been looking through the Python source code (2.4.1c1) and I've
found several places that use only \n in embedded code. One is
Modules/main.c, which says it's the Python interpreter main program.
The Py_Main() function is processing command-line options and does
the following:
if (c == 'c') {
/* -c is the last option; following arguments
that look like options are left for the
command to interpret. */
command = malloc(strlen(_PyOS_optarg) + 2);
if (command == NULL)
Py_FatalError(
not enough memory to copy -c argument);
strcpy(command, _PyOS_optarg);
strcat(command, \n);
break;
}
Later, without further changes to the command variable, it does this:
if (command) {
sts = PyRun_SimpleStringFlags(command, cf) != 0;
free(command);
Modules/cPickle.c has additional examples:
if (!( r=PyRun_String(
def __init__(self, *args): self.args=args\n\n
def __str__(self):\n
return self.args and ('%s' % self.args[0]) or '(what)'\n,
Py_file_input,
module_dict, t) )) return -1;
and
if (!( r=PyRun_String(
def __init__(self, *args): self.args=args\n\n
def __str__(self):\n
a=self.args\n
a=a and type(a[0]) or '(what)'\n
return 'Cannot pickle %s objects' % a\n
, Py_file_input,
module_dict, t) )) return -1;
The code in Demo/embed/demo.c uses only \n to terminate its lines:
PyRun_SimpleString(import sys\n);
PyRun_SimpleString(print sys.builtin_module_names\n);
PyRun_SimpleString(print sys.modules.keys()\n);
PyRun_SimpleString(print sys.executable\n);
PyRun_SimpleString(print sys.argv\n);
If these examples are intended to run on Windows, then presumably
using \n works on that platform. That doesn't necessarily preclude
\r\n from working as well, but apparently a platform-specific
sequence isn't required. I'd still be interested in having somebody
run the test programs we've both posted to find out for sure.
Is anybody who's following this thread running a PostgreSQL server
with PL/Python on Windows? We could use a test platform to answer
some questions, since the PostgreSQL aspect of this discussion is
about problems with PL/Python.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] pg/plsql question
On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote: While I have accomplished what I needed with the pgedit script given by John, I am still curious as to why mine is not working... Here is the latest version: /* */ create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_t varchar; begin v_user := ''user''; v_schema := ''public''; FOR v_t in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', t; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; Please note that all ticks above are single ticks. Here is what I do to execute it: excilan=# \i grant.sql CREATE FUNCTION excilan=# select fred_on_all(); ERROR: missing .. at end of SQL expression CONTEXT: compile of PL/pgSQL function fred_on_all near line 8 taken from http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING quote Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR loops (integer or query result) by checking whether the target variable mentioned just after FOR has been declared as a record or row variable. If not, it's presumed to be an integer FOR loop. This can cause rather nonintuitive error messages when the true problem is, say, that one has misspelled the variable name after the FOR. Typically the complaint will be something like missing .. at end of SQL expression. /quote try (untested): create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_rec RECORD; begin v_user := ''user''; v_schema := ''public''; FOR v_rec in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', v_REC.tablename; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Installation on XP
Hi, I am trying to install postgresql-8.0.1 on a machine running XP Professional. On the web site I went to the download area, then to V8.0.1 and then to win32. I downloaded postgresql.8.0.1.zip . I unzip that and executed postgresql-8.0.msi . When I hit the Start button, I get the following: This insallation package could not be opened. verify that the package I cannot seem to find any way to get past this problem. Did I just miss something simple? Any Help? Thanks, Glenn ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Wierd error message
On Tue, Mar 15, 2005 at 07:32:43AM -0500, Alex Turner wrote: I'm working with mod_python and pygresql (although I get virtualy the same error with psycopg too) What's the exact error you get with psycopg? Anyone know what the following error actualy means: Exception pg.InternalError: 'Connection already closed' in ignored Does it mean the obvious: Some code tries to access a connection object that was already closed? That would be my guess. What are you doing that results in the error? Is it easily repeatable? Does anything else show up in the web server or database logs? but why the in ignored then? Can you tell us more about the setup? What are all the pieces involved and their versions? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Massive performance differences
On Tue, 2005-03-15 at 18:10 +0100, Andreas Hartmann wrote: explain analyze select * from veranstaltung_original order by semester; Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual time=2568.10..2573.02 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467 width=223) (actual time=1936.68..2506.83 rows=5467 loops=1) ^^^ isn't this value (1936.68) suspiscious for a seq scan ? can a lot of dead tuples cause this? maybe VACUUM FULL ANALYSE time ? gnari ---(end of broadcast)--- TIP 3: 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] Massive performance differences
On Tue, 2005-03-15 at 12:18, Ragnar Hafsta wrote: On Tue, 2005-03-15 at 18:10 +0100, Andreas Hartmann wrote: explain analyze select * from veranstaltung_original order by semester; Sort (cost=3054.08..3067.74 rows=5467 width=223) (actual time=2568.10..2573.02 rows=5467 loops=1) Sort Key: semester - Seq Scan on veranstaltung_original (cost=0.00..2714.67 rows=5467 width=223) (actual time=1936.68..2506.83 rows=5467 loops=1) ^^^ isn't this value (1936.68) suspiscious for a seq scan ? can a lot of dead tuples cause this? maybe VACUUM FULL ANALYSE time ? It's not unreasonable for the first run when the machine has to hit the hard drives, but if it's that slow on subsequent reads, then there's likely some problem. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] New user: Windows, Postgresql, Python
[EMAIL PROTECTED] (Magnus Hagander) writes: I suppose my first (lazy) question is, is there a Python 2.4 compatible plpython.dll available anywhere? Alternatively, is there a way I can build one for myself? I'm happy enough doing my own build (I have mingw and msys available), but I'd rather not build the whole of postgresql if possible, just for the sake of one DLL Not that I know of. IFF the libraries export the same entrypoints without changing things, you could try just copying python24.dll to python23.dll. I don't know how the Python guys are with binary compatibility, though. Might be worth a shot. As per my earlier posting, I actually found that building postgresql wasn't at all hard. Once I'd built with Python 2.4 support, I had a compatible plpython.dll I could just copy in. I'm not sure renaming the Python DLL would have worked - it's definitely frowned on... On a different note, can't you have both python 2.3 *and* 2.4 on the asme system? Considering they put the version number in the filename, it seems this should be possible? I could, but I try to avoid this, as it involves double installs of any extensions I want to use, or incompatible environments. More laziness on my part, really :-) Thanks for the suggestions, Paul. PS Thanks to the developers who made building postgresql on Windows such an easy job! I was very impressed - I genuinely didn't think that building such a large piece of software would be so hassle-free. -- Never keep up with the Joneses. Drag them down to your level. -- Quentin Crisp ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] New user: Windows, Postgresql, Python
On Tue, Mar 15, 2005 at 07:05:22PM +, Paul Moore wrote: As per my earlier posting, I actually found that building postgresql wasn't at all hard. Once I'd built with Python 2.4 support, I had a compatible plpython.dll I could just copy in. Pardon the interruption, but do you have a PostgreSQL server with PL/Python running on Windows? Have you been following the plpython function problem workaround thread? http://archives.postgresql.org/pgsql-general/2005-03/msg00599.php We (the thread participants) could use somebody with a Windows server to do some testing. Specifically, we're wondering if Python on Windows requires embedded Python code to have CRLF (\r\n) as a line ending, or if it requires (or at least permits) LF (\n) only. If you're able to help, could you could post the results of the following? CREATE FUNCTION pytest_lf() RETURNS integer AS 'x = 1\nreturn x\n' LANGUAGE plpythonu; CREATE FUNCTION pytest_crlf() RETURNS integer AS 'x = 1\r\nreturn x\r\n' LANGUAGE plpythonu; SELECT pytest_lf(); SELECT pytest_crlf(); With PostgreSQL 8.0.1, Python 2.4.1c1, and Solaris 9, I get this: test=# SELECT pytest_lf(); pytest_lf --- 1 (1 row) test=# SELECT pytest_crlf(); ERROR: plpython: could not compile function pytest_crlf DETAIL: exceptions.SyntaxError: invalid syntax (line 2) If you have the ability to compile standalone C programs with embedded Python, we'd also be interested in seeing what happens if you run the programs in the following messages: http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php http://archives.postgresql.org/pgsql-general/2005-03/msg00630.php Any test results or comments you can provide would be appreciated. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Installation on XP
On Mar 15, 2005, at 1:14 PM, Glenn Sullivan wrote: I am trying to install postgresql-8.0.1 on a machine running XP Professional. On the web site I went to the download area, then to V8.0.1 and then to win32. I downloaded postgresql.8.0.1.zip . I unzip that and executed postgresql-8.0.msi . When I hit the Start button, I get the following: This insallation package could not be opened. verify that the package I cannot seem to find any way to get past this problem. Did I just miss something simple? Any Help? You need to copy the .msi files out of the .zip archive. It does not work to try to open the file from within the archive using the zip viewer feature of the Windows shell. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, 2005-03-15 at 14:07 +0100, tony wrote: by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster That is very interesting!!! I have several webapps on my server each one opens several queries to the database from _each_ JSP - often more than three... So the hunch that I had all along was right: PostgreSQL is a much better back end for Tomcat/JSP than MySQL. Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Peculiar performance observation....
On Mon, 2005-03-14 at 21:14, Net Virtual Mailing Lists wrote: On Mon, 2005-03-14 at 06:11, Net Virtual Mailing Lists wrote: Hello, I am sorry to bring this up again Does anyone have any idea what might be going on here?... I'm very worried about this situation.. ;-( It looks to me like either you're not analyzing often enough, or your statistics target is too low to get a good sample. Note your estimated versus real rows are off by a factor of 70 (28 est. versus 1943 actual rows). That's a pretty big difference, and where you should be looking. - Seq Scan on jobdata (cost=0.00..7567.88 rows=28 width=52) (actual time=11.498..4800.907 rows=1943 loops=1) Yes, this is because PostgreSQL is using an index to approximate a sequential scan, which is not a good thing since PostgreSQL can't get all the information it needs from just an index, but has to visit the table to check visibility. All of these were after a vacuum full analyze, which I actually do nightly on the database. I probably confused the issue with all of my posts, this is the query which has me concerned. When running it on my system here, the disk thrashes (and I mean *THRASHES*) the entire 12-20 seconds it takes to run... WHen running on our production servers, I can't hear the disk, but see an equally troubling performance loss when using the index. I'll call this query 1: database= explain analyze select id from table1 where category @ 'a.b'; QUERY PLAN - - - Index Scan using table1_category_full_gist_idx on jobdata (cost=0.00..113.48 rows=28 width=4) (actual time=43.814..12201.528 rows=1943 loops=1) Index Cond: (category @ 'a.b'::ltree) Filter: (category @ 'a.b'::ltree) Total runtime: 1.258 ms I can do this to speed things up (this results in very little disk activity, certainly not the thrashing the original query did): create table yuck (id integer, category ltree[]); insert into yuck select id, category from table1; create index category_idx on yuck using gist(category); vacuum analyze yuck; jobs= explain analyze select id from table1 where id in (select id from yuck where category @ 'a.b'); QUERY PLAN - - - Nested Loop (cost=108.64..114.28 rows=1 width=52) (actual time=654.645..1245.212 rows=1943 loops=1) - HashAggregate (cost=108.64..108.64 rows=1 width=4) (actual time=654.202..690.709 rows=1943 loops=1) - Index Scan using category_idx on yuck (cost=0.00..108.57 rows=28 width=4) (actual time=2.046..623.436 rows=1943 loops=1) Index Cond: (category @ 'a.b'::ltree) Filter: (category @ 'a.b'::ltree) - Index Scan using table1_pkey on jobdata (cost=0.00..5.64 rows=1 width=52) (actual time=0.219..0.235 rows=1 loops=1943) Index Cond: (table1.id = outer.id) Total runtime: 1261.551 ms (8 rows) If I drop the index table1_category_full_gist_idx, the query speeds up dramatically (10-15 times faster on both dev and prod uction systems). So my concern, in short: why is it so much slower when actually using an index and why is it trying to make mince meat out of my hard drive? I'll explain it again, sorry if my quoting originally was a bit of a mess. I meant to post the last comment I made after some other comment in your original post that I think I deleted. Anyway, the reason it's slow is that PostgreSQL, unlike most other databases, cannot get the answers from an index. It can only get a pointer to the right place in the table to look for the answer. After that, due to visibility issues caused by the way postgresql implements MVCC, it then has to look IN THE TABLE to find out if the value is visible to your transaction or not. So it's going Index then table, then index, then table, then index, then table, for however many rows it's gonna grab. In this case 1943. In query 1, the number of rows being returned by the index scan is 1943, but the planner only thinks it's gonna get back 28. So, with a 70:1 ratio of incorrectness here, the planner thinks an index scan is a good idea. It's not, it's a terrible idea for your table. The problem is likely that the query planner is not getting the right numbers for this table, and I'm not even sure how accurate statistics can be for ltrees, as I've only ever used btree indexes in postgresql. But, upping the statistics target for the column producing this bad behavior and rerunning
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, 2005-03-14 at 12:43 +, Richard Huxton wrote: Take 30 minutes to read through the article below. It covers the basics of how to manage your configuration settings. http://www.powerpostgresql.com/PerfList That's an informative article. I was hoping, however, that it would have a few details about the effects of the statistics settings on performance. Which statistics options affect the planner? Do they potentially affect autovacuum? Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? Regards, Jeff Davis PS: A minor typo in Disk and WAL - checkpoint_segments: s/Depening/Depending/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 03:41:37PM +, Ragnar Hafstað wrote:
actually, perl scripts with \r\n line endings will run just fine in
unix/linux.
Indeed, and PL/Perl doesn't care. I just tested several PLs with
PostgreSQL 8.0.1 on Solaris 9 and here are the results:
PL/pgSQL CRLF ok
PL/PerlCRLF ok
PL/RubyCRLF ok
PL/Tcl CRLF ok
PL/Python CRLF fails
PL/R CRLF fails
Details:
CREATE FUNCTION test_pgsql() RETURNS integer AS
'DECLARE x integer;\r\nBEGIN\r\nx := 123;\r\nRETURN x;\r\nEND;\r\n'
LANGUAGE plpgsql;
CREATE FUNCTION test_perl() RETURNS integer AS
'$x = 123;\r\nreturn $x;\r\n'
LANGUAGE plperl;
CREATE FUNCTION test_ruby() RETURNS integer AS
'x = 123\r\nreturn x\r\n'
LANGUAGE plruby;
CREATE FUNCTION test_tcl() RETURNS integer AS
'set x 123\r\nreturn $x\r\n'
LANGUAGE pltcl;
CREATE FUNCTION test_python() RETURNS integer AS
'x = 123\r\nreturn x\r\n'
LANGUAGE plpythonu;
CREATE FUNCTION test_r() RETURNS integer AS
'x - 123\r\nreturn(x)\r\n'
LANGUAGE plr;
SELECT test_pgsql();
test_pgsql
123
(1 row)
SELECT test_perl();
test_perl
---
123
(1 row)
SELECT test_ruby();
test_ruby
---
123
(1 row)
SELECT test_tcl();
test_tcl
--
123
(1 row)
SELECT test_python();
ERROR: plpython: could not compile function test_python
DETAIL: exceptions.SyntaxError: invalid syntax (line 2)
SELECT test_r();
ERROR: R interpreter parse error
DETAIL: R parse error caught in PLR36865 - function() {x - 123
return(x)
}.
CONTEXT: In PL/R function test_r
If I remove the CRs from the Python and R functions then they work:
CREATE OR REPLACE FUNCTION test_python() RETURNS integer AS
'x = 123\nreturn x\n'
LANGUAGE plpythonu;
CREATE OR REPLACE FUNCTION test_r() RETURNS integer AS
'x - 123\nreturn(x)\n'
LANGUAGE plr;
SELECT test_python();
test_python
-
123
(1 row)
SELECT test_r();
test_r
123
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? It entirely depends on the database but not that the 32bit limit of 4GB is per CPU. So if you have 4 CPUs you can have 16GB of ram. However, you should be running Opterons anyway. J Regards, Jeff Davis PS: A minor typo in Disk and WAL - checkpoint_segments: s/Depening/Depending/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] New user: Windows, Postgresql, Python
[EMAIL PROTECTED] (Michael Fuhr) writes: We (the thread participants) could use somebody with a Windows server to do some testing. Glad to help... This is with postgresql 8.0.1, Python 2.4. Specifically, we're wondering if Python on Windows requires embedded Python code to have CRLF (\r\n) as a line ending, or if it requires (or at least permits) LF (\n) only. If you're able to help, could you could post the results of the following? CREATE FUNCTION pytest_lf() RETURNS integer AS 'x = 1\nreturn x\n' LANGUAGE plpythonu; CREATE FUNCTION pytest_crlf() RETURNS integer AS 'x = 1\r\nreturn x\r\n' LANGUAGE plpythonu; SELECT pytest_lf(); SELECT pytest_crlf(); With PostgreSQL 8.0.1, Python 2.4.1c1, and Solaris 9, I get this: test=# SELECT pytest_lf(); pytest_lf --- 1 (1 row) test=# SELECT pytest_crlf(); ERROR: plpython: could not compile function pytest_crlf DETAIL: exceptions.SyntaxError: invalid syntax (line 2) I get exactly the same results. If you have the ability to compile standalone C programs with embedded Python, we'd also be interested in seeing what happens if you run the programs in the following messages: http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php I get: test1 What hath Guido wrought? http://archives.postgresql.org/pgsql-general/2005-03/msg00630.php I get: test2 Initialized. Python 2.4 (#60, Nov 30 2004, 11:49:19) [MSC v.1310 32 bit (Intel)] running: print 1 print 2 1 2 end running: print 1 print 2 File string, line 1 print 1 ^ SyntaxError: invalid syntax end Finalized. I don't know if this helps? It seems reasonable to me - as far as Python C code is concerned, code strings should be \n-separated, just like in Unix. The only place CRLF is applicable is in code read from files, where the C runtime converts it to \n-delimited before the Python APIs see it (as far as I understand it, which isn't very far...) The long and short of it is that I believe you just use \n to delimit lines on Windows, just like anywhere else. Regards, Paul. -- SCSI is not magic. There are fundamental technical reasons why it is necessary to sacrifice a young goat to your SCSI chain now and then. -- John Woods ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Installation on XP - Permissions
Hi, When running the installer on XP, I get to the Service Configuration panel. When I enter the password and click OK, I get: Failed to open local computer policy. Unable to determine user account rights(5). If I click OK to that, the install continues until it fails complaining about needing access to C:\WINDOWS\system32 to write libpq.dll . The first problem may be causing the second one. Anyone have an idea why It fails to open local computer policy? Thanks, Glenn ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. -Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote: Mark Rae wrote: Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? It tried it with 8.0.0rc3, and had previously tried a 7.4 version -Mark ---(end of broadcast)--- TIP 3: 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] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote: Mark Rae wrote: Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? It tried it with 8.0.0rc3, and had previously tried a 7.4 version Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major change that allows scaling in SMP environments. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 06:03:01PM +0100, Marco Colombo wrote: On Tue, 15 Mar 2005, Michael Fuhr wrote: I'll postpone commenting on the rest until we find out how the example programs run on Windows. If nobody follows up here then maybe I'll wander over to comp.lang.python. Yeah, there's no point in discussing until we have some real world data. I can't compile on windows, so I'll have to wait someone else to do that. I'm basing my opinions on Python documentation only. Paul Moore has run some tests on a Windows server. I don't see his message in the archives yet, but when it shows up it should be a followup to my request for his help (posted in another thread): http://archives.postgresql.org/pgsql-general/2005-03/msg00717.php Paul's tests on Windows show the same results as tests on *nix, viz., that lines in embedded Python need to end in LF, not CRLF. Thanks to Paul for running the tests. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] New user: Windows, Postgresql, Python
On Tue, Mar 15, 2005 at 10:46:09PM +, Paul Moore wrote: The long and short of it is that I believe you just use \n to delimit lines on Windows, just like anywhere else. Many thanks -- your test results contain the info we've been seeking. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 07:00:25PM -0500, Bruce Momjian wrote: Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major change that allows scaling in SMP environments. Ok, I'll give it a try in the next couple of days when there is some free time available on the machine. -Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. -Mark a bit of info re mysql and speed with concurrent transactions. a community site I was working to get running a bit better was using phpnuke and mysql. ( not my site, was a contract ) with 56,000 members the site was bogged down almost to the point of timing out, this was with only 100 or so users online. another community site, with custom script using mysql backend, sperad over several servers rather than one machine, and 250,000 members. ( 4 terabytes data transfer a month minimum. ) it's often slow responding, but doesn't get close to a timeout. while these are subjective observations, they show that tuning, and structure of application will have a significant affect, more than would generally be assumed. mysql is a good application, for lower traffic applications [ local intranet with 100 users ], but I would never actually recommend using mysql on a large database.[ large query useage would be horrendously slow ] Jaqui smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Problems building postgresql 8.0.1 on OS X 10.3.8
beyarecords.com [EMAIL PROTECTED] writes: I get the following error message when trying to build postgresql: checking for C compiler default output... configure: error: C compiler cannot create executables Hm, it works for me and for other people on OS X. Look into the config.log file for more details. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Wierd error message
I'm running apache 2.0.52 on pentium III 866 using RedHat EL3 - kernel 2.4.21-27.0.2.EL I'm using mod_python 3.1.3 and the postgresql-python-8.0.1-1PGDG RPM. Unfortunately the error is not readibly reproducable. Sometimes it happens, and sometimes it doesn't. Normaly if I reload the page it goes away. There are cron jobs that make HTTP calls to the system every 5 minutes also written in python. When httpd is isdle lsof | grep httpd | grep 5432 shows nothing. We have only been experiencing this error recently. It may just be conincidence, but it has been noticed since upgrading to 8.0. much to my amazement, error_log contains: [Wed Mar 16 00:38:41 2005] [error] [client 66.216.147.134] PythonHandler ContentGenerator: STDERR Contained data: Exception pg.InternalError: 'Connection already closed' in bound method PostgresqlDBConnection.__del__ of PGDBC.PostgresqlDBConnection instance at 0x8b3428c ignored\n, referer: http://www.totalleads.com/client_administration/display_employee_postal_code_list.nece?eid=13071section=users The STDERR part I can explain: my web page evals and calls some python code. I think I know the problem. The current release is printing an error to stderr if a database connection that is aready closed is tried to be closed again. I am catching anything on stderr and raising it as an exception. So I think the problem is solved. Alex Turner netEconomist On Tue, 15 Mar 2005 11:13:56 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Tue, Mar 15, 2005 at 07:32:43AM -0500, Alex Turner wrote: I'm working with mod_python and pygresql (although I get virtualy the same error with psycopg too) What's the exact error you get with psycopg? Anyone know what the following error actualy means: Exception pg.InternalError: 'Connection already closed' in ignored Does it mean the obvious: Some code tries to access a connection object that was already closed? That would be my guess. What are you doing that results in the error? Is it easily repeatable? Does anything else show up in the web server or database logs? but why the in ignored then? Can you tell us more about the setup? What are all the pieces involved and their versions? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
