Re: [GENERAL] Numeric user names
On Tue, 12 Oct 2004, Ed Stoner wrote: I am unable to use the CREATE USER command with numeric user names (i.e. CREATE USER 35236 WITH PASSWORD '1234';). Is this a limitation or a problem somewhere with how I have things configured? Is there are workaround? I believe you can create a user with a quoted identifier that is all numbers (ie CREATE USER 35236) but then you have to quote the username for sql statements like grant and revoke as well. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] plpython question
I have the following function and I am getting an invalid syntax error when I try to run it. I have tried the function a number of ways including with named parameters and non-named parameters using the args array. I also tried it with a tab at the beginning and without. I've also tried with the $$ and with single quotes and the double single quoting all the existing single quotes. Any help would be greatly appreciated. create or replace function BatchBalanceStatus(balance int, needed int, freestock int) returns varchar as $$ if balance 0: return 'Unhandled' elif freestock = needed: return 'OK' else: return 'Ordered' $$ language plpythonu Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython question
I don't know plpythonu but python ;-) As I really understood your problem you want to return strings. In Pytho return(OK ) should work ;-) Hagen Sim Zacks wrote: I have the following function and I am getting an invalid syntax error when I try to run it. I have tried the function a number of ways including with named parameters and non-named parameters using the args array. I also tried it with a tab at the beginning and without. I've also tried with the $$ and with single quotes and the double single quoting all the existing single quotes. Any help would be greatly appreciated. create or replace function BatchBalanceStatus(balance int, needed int, freestock int) returns varchar as $$ if balance 0: return 'Unhandled' elif freestock = needed: return 'OK' else: return 'Ordered' $$ language plpythonu Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Another list for windows port...
Am Dienstag, 19. Oktober 2004 02:31 schrieb Robby Russell: Isn't the purpose to promote PostgreSQL? not *nix-only flavors? It could be argued that the purpose is to get one's job done. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpython question
I had tried it exactly the way I wrote it in python as a function and it worked. Just in case I tried changing it to return (OK) as you suggested and got the same error. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax I don't know plpythonu but python ;-) As I really understood your problem you want to return strings. In Pytho return(OK ) should work ;-) Hagen Sim Zacks wrote: I have the following function and I am getting an invalid syntax error when I try to run it. I have tried the function a number of ways including with named parameters and non-named parameters using the args array. I also tried it with a tab at the beginning and without. I've also tried with the $$ and with single quotes and the double single quoting all the existing single quotes. Any help would be greatly appreciated. create or replace function BatchBalanceStatus(balance int, needed int, freestock int) returns varchar as $$ if balance 0: return 'Unhandled' elif freestock = needed: return 'OK' else: return 'Ordered' $$ language plpythonu Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] Win32 Question
Please forgive me if I'm posting to the wrong mailing list. I am working with Windows XP and the beta2-dev3 version of PostgreSQL. The issue I am having is under a login name that has administrative rights to the machine the program is running on (a program I wrote), database access is allowed and the program works fine. However, I cannot use any of the command line tools that come packaged with PostgreSQL (pgAdmin III runs but I get errors when trying to do any database work). When I'm logged in as a lesser user, I can use the command line tools but, the program will not run. The specific error message is The driver cannot be loaded due to system error 998 (PostgreSQL). This may not be word for word but it is close. Has anyone experienced this? Is this normal behavior? Is there anyway around this? Thanks for the help! Do you by any chance have more than one libpq.dll on your system? That these users might pick up in a different order because of different PATHs or permissions? (Error 998 is Invalid access to memory location, which is exactly the kind of symptoms we've seen with invalid libpq.dll:s, though I've never seen it reported as an errorcode before) There is a problem in beta2 (and beta-3) with two conflicting versions of libpq.dll on the system - the mingw built one is not compatible with the msvc built. This has been fixed in cvs and will be in the next version of the installer. The bug is in the mingw DLL generation process. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpython question
Thanks for the link. I had already looked at that page, and it gives basically no help at all. I think the problem is how Pgadmin handles new lines or something like that. because I can't get any 2 line program to work. I also tried the code in PSQL and it worked when I indented manually (hitting the spacebar 5 times). I tried that in PGAdmin and it also failed. Also in PSql it only worked with non-named parameters. So my list was (int,int,int) and I referred to them as args[0-2] It looks like plpython support hasn't hit the mainstream yet. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax Me again ;-) I do not have a running postgresql with scripting support so i googled for some ideas ;-) The first syntax error must arrive while parsing the argument list, is'nt it. Postgres means Syntax error at int. The problem is, that you can not use the function as you do in python. The argument list contains only the data types: here ( int, int, int ). The arguments are given by args[0], args[1] and so on. Furthermore the code of your function has to be encapsulates in '...' . So you can not use '...' to encapsulate strings. For more Information take a look at: http://www.signal42.com/pgsql/plpython.html#PLPYTHON-FUNCS Sim Zacks wrote: I had tried it exactly the way I wrote it in python as a function and it worked. Just in case I tried changing it to return (OK) as you suggested and got the same error. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax I don't know plpythonu but python ;-) As I really understood your problem you want to return strings. In Pytho return(OK ) should work ;-) Hagen Sim Zacks wrote: I have the following function and I am getting an invalid syntax error when I try to run it. I have tried the function a number of ways including with named parameters and non-named parameters using the args array. I also tried it with a tab at the beginning and without. I've also tried with the $$ and with single quotes and the double single quoting all the existing single quotes. Any help would be greatly appreciated. create or replace function BatchBalanceStatus(balance int, needed int, freestock int) returns varchar as $$ if balance 0: return 'Unhandled' elif freestock = needed: return 'OK' else: return 'Ordered' $$ language plpythonu Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Proper Sizing of Shared Buffer Cache
Don Kelloway wrote: I'm a first-time user with PostgreSQL so please forgive my ignorance. I've purchased (and read) Practical PostgreSQL (O'Reilly) and PostgreSQL Essential Reference (New Riders). So far, so good. I think learning PostgreSQL will not be as difficult as I thought it would be. I've also been googling for the last few days, but I have a question in regards to determining the proper size of the buffer cache parameter. Another good reference point is http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html The only real way to find the best value is by testing different values against actual usage. There's also a performance mailing list you might find useful. Archive available on the website. -- Richard Huxton Archonet Ltd ---(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] Another list for windows port...
On 18 Oct 2004 at 17:38, Eric wrote: It is possible to mainteners of this list to keep this list from /$?%/$?/? windows problems? I don't care about windows. The worst thing never made was porting an excellent product like PostgreSQL to a poor server OS like windows. Now, we are invade with users that aren't used to read the manual... even the howto install readme!!! Users that have problems often relate to bug in OS... It's very unfair to confuse users of Windows with users who don't RTFM. The opinion expressed above is also totally at odds with what I have come to expect on this list - friendly, professional, expert help, not to mention reasoned debate. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Online system transactions
Hello, You probably should look at the documentation for this. http://www.postgresql.org/docs/current/interactive/sql-begin.html PostgreSQL supports _exactly_ this notion, in exactly the way you describe. But this is normal 'transaction' - and I didn't find way, how can I 'store' transaction to disc storage and 'reopen' later. So, begin/commin works within ONE connection and doesn't help in my general problem. Or is there some way to do that with normal transactions ? My problem is that related data changes are done in 'longer' time - e.g. hour or so (it take hour to change all datas together) and within various database connections. Thx Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. - Original Message - From: Chris Browne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 18, 2004 7:22 PM Subject: Re: [GENERAL] Online system transactions [EMAIL PROTECTED] (Lada 'Ray' Lostak) writes: Imagine some online system, based on PgSql. There are 'many' parts which depends on each other. I will give small example, but instead of simple table imagine changing tenths various tables (editing). So, 'change table A' can be work for hours. There are tables tableA and tableB. Changes are made by thin client. If you change tableA, you have to also change tableB to 'finish' operation. Between starting changing tableA until tableB is finished, data are 'not valid'. Record may be changes, deleted, inserted, whatever. Is there any simple way, how to create 'transactions' over database connections ? These relations are really complex in real live and doesn't fit table foundaries, so, it is basically not realistic to make it on application layer and need to be done somehow on DB layer The best solution should be possibility to 'begin' transaction, 'store' somehow on server, and next time, instead of begin new transaction return to stored one. Transactions solves all these problems, but they don't work across various/multiple connections Any ideas/references please ? You probably should look at the documentation for this. http://www.postgresql.org/docs/current/interactive/sql-begin.html PostgreSQL supports _exactly_ this notion, in exactly the way you describe. If you need to have multiple transactions active at once, you will just need to establish multiple connections. -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/linuxxian.html A VAX is virtually a computer, but not quite. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Online update races
Hi, Suppose you have a web form to edit data from a table... you add a field in your table which contains a version identifier for that data, then you UPDATE ... WHERE ... AND version_id = the old version id. The version_id is passed around in a session variable or in hidden form fields. The hidden form fields are better because they will prevent breakage if the same user edits the same data in different windows, or refreshes his browser window at the wrong time. Then, if the UPDATE has a rowcount of 1, you know it's OK, but if it has a rowcount of 0, you know something happens, and can check if the record still exists and its version id was modified, or if the record was deleted. A version id can be a counter, a sequence... it can also be a MD5 of the row contents for instance, its sole purpose being to detect change. Using a sequence might be the easiest. This way works but still looks like band-aid ; moreover, if you do a complex operation which modifies several tables, you have to take care of modification order, and the problem becomes more complex. It would be nice to have a framework for that kind of thing which is common in web apps. One of postgresql's good points is that it does not lock things, thanks to MVCC, unlike MySQL which locks the table on every write. This model is in the same spirit than MVCC, because it will not prevent reads to records which are being updated. However, a recurrent problem in web applications is that there is no logout, logout can only be implemented with certainty using timeouts, so you can't use locking, because you really don't know when the locks will be released. If you use locking, some information will get locked waiting for a timeout if a user closes his browser without explicitely logging out ; besides you'd have to have a cron to log users out as a disconnected user, by definition makes no action to signal the fact that h's gone away. You could implement this by adding a version_id serial field to the relevant tables, and then an ON UPDATE trigger which would check that the version_id of the updater is the same than the version_id in the updated row, or else raise an exception. You can also have a special value to bypass checks, to be able to update in all cases, and not get stuck if you have a problem. The trigger would then increment the version_id before updating. Thank you for reply. I think this is basically the 'only' way how to solve the problem. Have some 'row changes count'. This scheme is easy implementable for smaller systems. But as you wrote above, if you prepare 'user datas' for various tables, joins, whatever, it is very hard to take care of this 'modified serial'. I would like to move this way to 'database' itselfs somehow. I was thinking abotu something like this... But I don't know PgSql internals, so, I don't know if it is possible... I suppose DB engine have to 'hold' some kind of row version' (chnages count, timestamp, combination, whatever) - because it should be needed while transactions. If DB engine can 'collect' within one transaction these ID's which were used while selecting datas, I can keep then in 'POST' data and verify (while transaction) if they are still valid. I have no clue if this is possible with PlSQL or another server-side scripting language or some improvement of PqSql is needed. And if improvement is needed, if it is possible. And if it is possible, if some PG developer will want to do that - and if they will like this kind of improvement. I am open to pay for solving this problem, because the system we are doing will be used commerically. And I think, this is general problem, which every bigger system have to solve. Sooner or later. I fully agree with you, that locking is not a way. But is there any other 'more automatized' way than take care of row versions by ourself ? But before contacting developers, I would like to ask other about opinions... Have a nice day, Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Another list for windows port...
On 10/19/2004 7:11 AM, Raymond O'Donnell wrote: On 18 Oct 2004 at 17:38, Eric wrote: It is possible to mainteners of this list to keep this list from /$?%/$?/? windows problems? I don't care about windows. The worst thing never made was porting an excellent product like PostgreSQL to a poor server OS like windows. Now, we are invade with users that aren't used to read the manual... even the howto install readme!!! Users that have problems often relate to bug in OS... It's very unfair to confuse users of Windows with users who don't RTFM. The opinion expressed above is also totally at odds with what I have come to expect on this list - friendly, professional, expert help, not to mention reasoned debate. The PostgreSQL team has not spent a great deal of effort porting the database system to the Win32 platform in order to have people like Eric spoil the reputation of our mailing lists. If Eric doesn't care about windows, he can ignore discussions related to it. If he doesn't want to see messages related to it, maybe he can show his capabilities to RTFM by setting up appropriate procmail filters. Or does this sound too difficult to you, Eric? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] About upper() and lower to handle multibyte char
Hi,
while upgrade to 8.0 (beta3) we got some problem:
we have a database which encoding is UNICODE,
when we do queries like:
select upper(''); --select some multibyte character,
then postgresql response:
ERROR: invalid multibyte character for locale
but when we do it in a SQL_ASCII encoding database,
it's ok and return unchanged string, that's what we think correct result.
I've searched the archive and found that in 8.0, the upper()/lower()
function have been changed to could handle multibyte character,
but, what's the expected behavior of these two function in coping with
multibyte character?
Another question: from the archive, I know that on system with
wctype.h toupper/tolower functions, the postgresql would support
multibyte upper/lower function; my system (slackware 10) got wctype.h,
but why still I get the ERROR? How can I check if my postgresql installation
come with multibyte upper/lower support?
The problem make us very difficlut when using upper/lower to deal with
columns with more then one encoding char, like Chinese and English char
in Unicode
database, because the transaction would abort with the error above, that
breaks
our application a lot.
Thanks and any help would be appreciated
Laser
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] CLI describe error: Out of memory while reading tuples.
Experimenting with Beta 8 on Windows; we got this message while running PC-SAS queries against a table with over 7 million rows. Which setting(s) should I be looking at? Thanks Mark R. Taber Database Administrator, Infrastructure and Architecture State of California Department of Finance 916.323.3104 x 2945 [EMAIL PROTECTED] To know that one doesn't know is best. - Lao Tzu ---(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] clustering
On Thu, Oct 14, 2004 at 10:29:11AM +0530, Nageshwar Rao wrote: Hi, We would like use Postgresql as our database. For high availability is it possible to cluster DB in Postgresql. Appreciate if you can let me know how this can be achieved. Else is there any other way to achieve High Availability in POstgresql as this is mission critical system. My recommendation for HA PostgreSQL is to use two servers with one of those nifty HA options, and let the postmaster on the taking-over machine recover the database just like after a crash. Also, you'll be wanting to use slony (www.slony.info). If you're asking whether Postgres has something akin to Oracle's RAC, the answer is not yet. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] About upper() and lower to handle multibyte char
Weiping [EMAIL PROTECTED] writes:
we have a database which encoding is UNICODE,
when we do queries like:
select upper('ÖÐÎÄ'); --select some multibyte character,
then postgresql response:
ERROR: invalid multibyte character for locale
What locale did you initdb in? The most likely explanation for this
is that the LC_CTYPE setting is not unicode-compatible.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] code in docs gives me an error
Hi I tried creating the function (from sec 19.3.1). But it gives me the following error: ERROR: type tablename does not exist. Any ideas of what i might be doing wrong? CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS ' DECLARE in_t ALIAS FOR $1; BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; ' LANGUAGE 'plpgsql'; Thanks, --Hicham ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] code in docs gives me an error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 19 Oct 2004, Hicham G. Elmongui wrote: I tried creating the function (from sec 19.3.1). But it gives me the following error: ERROR: type tablename does not exist. Any ideas of what i might be doing wrong? You have to write the data type while defining the function parameters. For example, CREATE FUNCTION use_many_fields(varchar) RETURNS TEXT AS ' ...and then declare it as you've done below: DECLARE in_t ALIAS FOR $1; Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBdTIVtl86P3SPfQ4RAj5vAKC2ugU2wcXHDgMMybyMqm14L4+9UwCfUd7m 7WQxt0yl0PHuezSucptNLtE= =Z94K -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] code in docs gives me an error
Hicham G. Elmongui [EMAIL PROTECTED] writes: I tried creating the function (from sec 19.3.1). But it gives me the following error: ERROR: type tablename does not exist. Any ideas of what i might be doing wrong? CREATE FUNCTION use_many_fields(tablename) RETURNS TEXT AS ' The example assumes you already created a table or composite type named tablename. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Online system transactions
On Tue, Oct 19, 2004 at 01:29:41PM +0200, Lada 'Ray' Lostak wrote: But this is normal 'transaction' - and I didn't find way, how can I 'store' transaction to disc storage and 'reopen' later. That you can't do. The usual recipe for this is something like optimistic locking -- for instance, set an int8 column to nextval() of some sequence, and then later, when you're ready to commit, check to make sure the value hasn't changed and do all your work in an SQL transaction. If the value has changed anywhere, you know you have to throw away the work you did (or present the intermediate changes to the user, or whatever). A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(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] delayed input
I need to select all tuples from a table, but need them to be fetched with a constant delay (say 1 sec) between every consecutive tuples. The first idea that came up to my mind is to create a DelayedSeqScan operator, and put delay before returning the scanned tuple. Can I do this functionality using table functions? Regards, --h ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] delayed input
Why not do this on the client side? I'm just curious as to the benfit of doing this on the server. On Tue, 19 Oct 2004 11:10:58 -0500, Hicham G. Elmongui [EMAIL PROTECTED] wrote: I need to select all tuples from a table, but need them to be fetched with a constant delay (say 1 sec) between every consecutive tuples. The first idea that came up to my mind is to create a DelayedSeqScan operator, and put delay before returning the scanned tuple. Can I do this functionality using table functions? Regards, --h ---(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] delayed input
Hicham G. Elmongui wrote: I need to select all tuples from a table, but need them to be fetched with a constant delay (say 1 sec) between every consecutive tuples. The first idea that came up to my mind is to create a DelayedSeqScan operator, and put delay before returning the scanned tuple. Can I do this functionality using table functions? Could you not just use a cursor and fetch each row in turn based on some timer in your application? -- 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])
Re: [GENERAL] delayed input
Use a cursor... I need to select all tuples from a table, but need them to be fetched with a constant delay (say 1 sec) between every consecutive tuples. The first idea that came up to my mind is to create a DelayedSeqScan operator, and put delay before returning the scanned tuple. Can I do this functionality using table functions? Regards, --h ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] [Fwd: Re: [pgsql-fr-generale] Décision Micro ?]
---BeginMessage--- Il me parait un peu illusoire de contester un test alors que CleverAge a pris la peine de contacter la communauté francophone de PostgreSQL et que personne ne s'est déplacée (pour des raisons que je ne critique pas). Donc à partir de ce moment là, je trouve abhérant de contester l'article et/ou les résultats. Je reviendrais sur quelques points qui ont été critiqué précédement : - Version Windows : La fin de l'article précise qu'une version Windows est prévue. Effectivement, PostgreSQL 7.x peut tourner sous Windows (avec le couple Cygwin/ipc) ou la version 8.x en natif. Mais ce sont des versions récentes ou en encore en développement et ne peuvent donc pas être prise en compte dans le test. - Problème d'installation : Effectivement, PostgreSQL n'ai pas une bdd simple à installer. Pour une bdd public (a contrario d'une bdd de dev), la gestion de droit (pour ne prendre que cette exemple), est relativement peu évident pour un novice. - Pas de sauvegarde/restauration : Le dump de la base de donnée (si il a été utilisé dans les tests) ne constitue pas le meilleur moyen de sauvegarde. Je n'ai que quelques 10e de milliers d'enregistrements, mais cela me prend une ou deux minutes à restaurer. Dans le cas de base avec plusieurs millions, la restauration de la bdd via un dump est à mon avie plus qu'ilusoir. Je pense que l'article parle de sauvegarde de l'architecture de la bdd, permettant une restauration dependant de la vitesse de lecture d'un DAT/DLT et non de la reconstruction totale de la bdd via un dump. Je prendrais l exemple de ArcServeIT qui stop les services pour effectuer la sauvegarde des BDDs, permetant une restauration rapide du systeme en cas de panne. Personnellement, après avoir lu le pdf, je le trouve effectivement léger par rapport à tout ce qui pourrait être dit sur postgresql (mais egalement sur les autres bdds) mais il reste techniquement tres correct pour son corps de cible (des utilisateurs non chevronnés dans tout ce qui est bdd). Je partage l'avis final sur Postgresql qui se réserve pour des administrateurs en herbe (ou non) chevronnées. Meme si je comprends l'ensemble des personnes qui auraient souhaité une meilleure note à leur produit fetiche, je pense qu'une lettre serait aussi inutile que discriminatoire. ---End Message--- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] download postgreql problem
I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Thanks, JD
Re: [GENERAL] vacuum schema
On Tue, 19 Oct 2004 00:48:34 -0500, Thomas F. O'Connell [EMAIL PROTECTED] wrote: Ah, yes. You're right. Presumably you could use the hidden query from \dn in psql to get the tables necessary for a script. That's the next best thing I can think of. I wrote a script to do the vacumming. I just have a config file where you can list the tables you want vacuumed. Can you use those tables via normal sql? It would be nice to do that via sql. -Josh ---(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] download postgreql problem
[EMAIL PROTECTED] wrote: I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Try this URL: ftp://ftp9.us.postgresql.org/mirrors/postgresql Thanks, JD -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ 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] removing idle connections
Is there a way to remove idle connections? My postgres server is getting serveral hundred idle connections. It's due to a postgres .NET provider not closing the connections properly. I don't want to kill them all, or restart postgres everytime the connections go crazy. -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] capacity of datatype text
On Oct 19, 2004, at 1:14 AM, David Garamond wrote: btw, TEXT is one of those postgres-specific features that makes you stick (stuck? :-) with postgres. nowadays, i never ever have to bother to think whether to give a column a max width of 32, 50, 64, 100, 150, ... is that a bad thing I don't think it is a bad thing. Unless there is a clear and specific limit related to the domain, why create one? You can create a last name field with 20, 30, or 50 characters but there are no rules in the world that will ensure you picked the right limit. I guess it is only a bad thing if you have to consider porting your PostgreSQL database. Hopefully it won't ever happen :). Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] download postgreql problem
No dice. It tried ftp as the userid and anonymous as the password. I tried using anonymous and supplying my email [EMAIL PROTECTED] Joshua D. Drake [EMAIL PROTECTED] 10/19/2004 01:53 PM To:[EMAIL PROTECTED] cc:[EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem [EMAIL PROTECTED] wrote: I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Try this URL: ftp://ftp9.us.postgresql.org/mirrors/postgresql Thanks, JD -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL jd.vcf Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] vacuum schema
Sure. The system tables can be accessed via SQL. I frequently use the output of psql's hidden queries to glean more information about postgres internals for needs like yours where postgres has the information but not the interface to accomplish a specific task. -tfo On Oct 19, 2004, at 12:50 PM, Josh Close wrote: On Tue, 19 Oct 2004 00:48:34 -0500, Thomas F. O'Connell [EMAIL PROTECTED] wrote: Ah, yes. You're right. Presumably you could use the hidden query from \dn in psql to get the tables necessary for a script. That's the next best thing I can think of. I wrote a script to do the vacumming. I just have a config file where you can list the tables you want vacuumed. Can you use those tables via normal sql? It would be nice to do that via sql. -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] removing idle connections
On 19 Oct 2004 at 13:00, Josh Close wrote: Is there a way to remove idle connections? My postgres server is getting serveral hundred idle connections. It's due to a postgres .NET provider not closing the connections properly. I don't want to kill them all, or restart postgres everytime the connections go crazy. I would have though it would be better to fix the client application. If the app is not closing connections then you may be leaking handles and memory. What .NET provider is this? Are you sure it is not just normal connection pooling? Cheers, Gary. ---(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] removing idle connections
On Tue, 19 Oct 2004 19:24:23 +0100, Gary Doades [EMAIL PROTECTED] wrote: I would have though it would be better to fix the client application. If the app is not closing connections then you may be leaking handles and memory. What .NET provider is this? Are you sure it is not just normal connection pooling? Cheers, Gary. The provider is corelabs. The programmer that wrote the code says he's closing the connections, but they aren't actually closing. Any ideas? Or better yet, do you know of a good postgres .net provider? -Josh ---(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] removing idle connections
On 19 Oct 2004 at 13:32, Josh Close wrote: The provider is corelabs. The programmer that wrote the code says he's closing the connections, but they aren't actually closing. Any ideas? Or better yet, do you know of a good postgres .net provider? Hmm, I've had lots of problems with the Corelabs provider. The open source provider (Npgsql) available on GBorg is a lot better for most things. I reverted to using the ODBC driver for Postgres with the .NET Odbc provider. Works fine. With connection pooling enabled you will see several idle connections around waiting to be re-used by a provider connection supplying the same credentials and options as a disconnected pooled-but-idle connection. If you connect with a different username/password everytime then this may be a problem, otherwise it is a big performance gain in the disconnected recordset world of .NET. Cheers, Gary. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] delayed input
I need this for a side project. Is there a way to do something like this:
SELECT *
FROM DelayedTable('tablename', 5);
DelayedTable provides me with one tuple every 5 seconds.
Regards,
--h
Hicham G. Elmongui [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I need to select all tuples from a table, but need them to be fetched with
a
constant delay (say 1 sec) between every consecutive tuples.
The first idea that came up to my mind is to create a DelayedSeqScan
operator, and put delay before returning the scanned tuple.
Can I do this functionality using table functions?
Regards,
--h
---(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] delayed input
On Tue, Oct 19, 2004 at 01:44:34PM -0500, Hicham G. Elmongui wrote:
I need this for a side project. Is there a way to do something like this:
SELECT *
FROM DelayedTable('tablename', 5);
You can probably build a sleep() function in C, and then use that to
cause delaying in a PL/pgSQL set-returning function. Something like
#include postgres.h
#include fmgr.h
PG_FUNCTION_INFO_V1(sleep);
Datum
sleep(PG_FUNCTION_ARGS)
{
int32 delay = PG_GETARG_INT32(0);
sleep(delay);
PG_RETURN_VOID();
}
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon buscar gente que tengan sexo con
ciervos incendiándose, y el computador dirá especifique el tipo de ciervo
(Jason Alexander)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] delayed input
Hicham G. Elmongui wrote:
I need this for a side project. Is there a way to do something like this:
SELECT *
FROM DelayedTable('tablename', 5);
No, at my knowledge you'll obtain the first tuple only when
the function exit.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Segmentation Fault using PHP code
I'm trying to add some functionality to an already existing application...this applications works fine, and uses the PHP function pg_pconnect to make a persistent connection to the database. This works very well. My part is to make a new non-persistent connection (or whatever), to a newly created database on the same server. I simply do global $sys_dbhost,$sys_dbuser,$sys_dbpasswd,$sys_dbname; $pg_conn_string = user=$sys_dbuser dbname=$sys_dbname host=$sys_dbhost password=$sys_dbpasswd; $conn = pg_connect($pg_conn_string); *which is functional PHP code and in my apache logs I have [Tue Oct 19 14:11:29 2004] [notice] child pid 28586 exit signal Segmentation fault (11) This may seem like a strictly PHP question, but I was just wondering if perhaps this had anything do with postgres configuration or anything. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] User Defined Functions/management of OS_Directories
Hallo! I have a big problem, I am under time pressure to finish my project. can anybody please help me? I am developing a DB System associated with a File server. The DB Server should manage directories which could be, I thought, created through fired Triggers. I wrote a C function and compiled as -shared, created function and created trigger to execute procedure. But its not working. Is there somewhere any example? Or c functions guidelines? Or knows anybody how could I do it? After insert into command I got following Meldung and none of the commands inside the c function like mkdir(dir) are not executed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Thanks ___ Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] download postgreql problem
Works fine from here ... what FTP program are you using? On Tue, 19 Oct 2004 [EMAIL PROTECTED] wrote: No dice. It tried ftp as the userid and anonymous as the password. I tried using anonymous and supplying my email [EMAIL PROTECTED] Joshua D. Drake [EMAIL PROTECTED] 10/19/2004 01:53 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem [EMAIL PROTECTED] wrote: I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Try this URL: ftp://ftp9.us.postgresql.org/mirrors/postgresql Thanks, JD -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] removing idle connections
Josh Close wrote: Is there a way to remove idle connections? My postgres server is getting serveral hundred idle connections. It's due to a postgres .NET provider not closing the connections properly. I don't want to kill them all, or restart postgres everytime the connections go crazy. I do not think is problem of not close the connections. I bet the driver is acting like this: On connection: . Connect . start transaction On Commit: . commit transaction . start transaction On Abort: . abort transaction . start transaction On statemet: . execute statement As you can see you are always inside a transaction, idle I mean. BTW this is the behaviour of python driver PgDB ( I suggest to use psycopg instead ) and before the 8.0 series the JDBC driver did the same. The way to solve it is, delay the begin till the first statement: On connection: . Connect On Commit: . commit transaction On Abort: . abort transaction On statemet: . If is the first statement after a connection or a commit or an abort execute the: start transaction . execute statement For rpm mantainer: why do not include the psycopg instead of the actual python driver ? Regards Gaetano Mendola ---(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] download postgreql problem
Internet explorer. Marc G. Fournier [EMAIL PROTECTED] 10/19/2004 04:39 PM To:[EMAIL PROTECTED] cc:Joshua D. Drake [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem Works fine from here ... what FTP program are you using? On Tue, 19 Oct 2004 [EMAIL PROTECTED] wrote: No dice. It tried ftp as the userid and anonymous as the password. I tried using anonymous and supplying my email [EMAIL PROTECTED] Joshua D. Drake [EMAIL PROTECTED] 10/19/2004 01:53 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem [EMAIL PROTECTED] wrote: I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Try this URL: ftp://ftp9.us.postgresql.org/mirrors/postgresql Thanks, JD -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
Re: [GENERAL] download postgreql problem
On Tue, 19 Oct 2004 16:24:06 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Internet explorer. That would be your problem right there. IE's handling of FTP sessions is notoriously quirky. Do yourself a favor and download a shareware FTP program, or open up a DOS prompt. Regards, aaron.glenn ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] download postgreql problem
oops on Joshua's part: ftp://ftp9.us.postgresql.org/pub/mirrors/postgresql just accessed it from here using IE ... On Tue, 19 Oct 2004 [EMAIL PROTECTED] wrote: Internet explorer. Marc G. Fournier [EMAIL PROTECTED] 10/19/2004 04:39 PM To: [EMAIL PROTECTED] cc: Joshua D. Drake [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem Works fine from here ... what FTP program are you using? On Tue, 19 Oct 2004 [EMAIL PROTECTED] wrote: No dice. It tried ftp as the userid and anonymous as the password. I tried using anonymous and supplying my email [EMAIL PROTECTED] Joshua D. Drake [EMAIL PROTECTED] 10/19/2004 01:53 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] download postgreql problem [EMAIL PROTECTED] wrote: I am trying to download postgresql from one of the mirror sites. I get prompted for a username and password. I try anonymous login and my password and I get an error stating either the server doesn't support anonymous logins or that my email address wasn't accepted. What am I doing incorrectly? Try this URL: ftp://ftp9.us.postgresql.org/mirrors/postgresql Thanks, JD -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] how much ram do i give postgres?
I know this is kinda a debate, but how much ram do I give postgres? I've seen many places say around 10-15% or some say 25%... If all this server is doing is running postgres, why can't I give it 75%+? Should the limit be as much as possible as long as the server doesn't use any swap? Any thoughts would be great, but I'd like to know why. Thanks. -Josh ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Change query priority
Thats fine, but you do understand that nice (linux) will have *no* effect on I/O? For any non-trivial table (that can't be held entirely in memory), re-nice will almost certainly have no effect. -Barry In article [EMAIL PROTECTED], Gaetano Mendola wrote: Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: I feel that renice a backend will not kill your system. It won't kill the system, but it probably won't accomplish what you hoped for, either. That's true but right now renice a backend is the only way to procede in order to *try* to slow down some queries Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] attn.: psycopg users -- simple question
Sorry to burden this list (again) with a psycopg question. I'm stuck at a table creation step and am not getting results from their list. Question: what's the appropriate syntax for specifying a primary key? My very simple table creation test (based on their first.py example is failing ... Here's what I'm trying. Non-pythonated syntax works in pgsql: no go in psycopg: curs.execute(CREATE TABLE key_test ( key_col CHAR(9) PRIMARY KEY, nother_col CHAR(256))) pure joy in pgsql: cs_test=# CREATE TABLE key_test ( cs_test(# key_col CHAR(9) PRIMARY KEY, cs_test(# nother_col CHAR(256) cs_test(# ); Thanks again! Scott ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] reusing column labels in select
Hello, Is there any way to reuse a column label in a select list like this: SELECT 1 + 1 AS a, a + 1 AS b; I vaguely remember being able to do something like this in oracle. Any ideas? Thanks! ~RvR ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Online update races
Lada 'Ray' Lostak wrote: I will also appreciate any links to web resources, talking about this problem. I didn't find anything usefull around. I'm working with developing a fairly big warehouse management system, and there we see this problem every day. We've settled (many years ago) for the following: loop begin select * from my_table where status = :status (read into record structure) update my_table set a=1, b=2 where and status = :status and another_status = :status2 and lates_updater =:record.latest_updater and latest_date = :record.latest_date and latest_time = :record.latest_time commit exit loop exception when transaction_conflict | no_such_row = rollback delay small time end loop; transaction_conflict | no_such_row should be interpreted as no row matched. The flaw this design has, is that you can't separate 'real transaction conflicts' with situation where other conditions failed, as no row had another_status = status2 However, we find the design good enough to keep using it. It has the advantage that no table is ever looked, we don't use select for update at all. -- /Björn --- http://lundin.homelinux.net Registered Linux User No. 267342 http://counter.li.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Problem
Hallo! I have a big problem, could you please help me? I am developing a DB System accociated with a File server. The DB Server schould manage directories which coulkd be, I thought, fired with after insert Triggers. I wrote a C function and compiled as -shared. But its not working. Is there somewhere any example to? Or how could I do it? Paudel ___ Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How do I recover from pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
Hello, Is there a way to recover from the following error? I have (had) an existing database and wish not to lose the data tables. Thanks for any help, Pete [EMAIL PROTECTED] /]$ pg_ctl start postmaster successfully started [EMAIL PROTECTED] /]$ LOG: database system shutdown was interrupted at 2004-10-18 11:41:55 PDT LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid primary checkpoint record LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid secondary checkpoint record PANIC: unable to locate a valid checkpoint record LOG: startup process (pid 2803) was terminated by signal 6 LOG: aborting startup due to startup process failure [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])
Re: [GENERAL] attn.: psycopg users -- simple question
Ack! I sent this msg on October 14. Since then, I've gotten the primary key stuff working. Please disregard this msg. Thanks Scott On Oct 14, 2004, at 4:16 PM, Scott Frankel wrote: Sorry to burden this list (again) with a psycopg question. I'm stuck at a table creation step and am not getting results from their list. Question: what's the appropriate syntax for specifying a primary key? My very simple table creation test (based on their first.py example is failing ... Here's what I'm trying. Non-pythonated syntax works in pgsql: no go in psycopg: curs.execute(CREATE TABLE key_test ( key_col CHAR(9) PRIMARY KEY, nother_col CHAR(256))) pure joy in pgsql: cs_test=# CREATE TABLE key_test ( cs_test(# key_col CHAR(9) PRIMARY KEY, cs_test(# nother_col CHAR(256) cs_test(# ); Thanks again! Scott ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] how much ram do i give postgres?
Josh Close [EMAIL PROTECTED] writes: I know this is kinda a debate, but how much ram do I give postgres? I've seen many places say around 10-15% or some say 25%... If all this server is doing is running postgres, why can't I give it 75%+? Should the limit be as much as possible as long as the server doesn't use any swap? The short answer is no; the sweet spot for shared_buffers is usually on the order of 1 buffers, and trying to go for 75% of RAM isn't going to do anything except hurt. For the long answer see the pgsql-performance list archives. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How do I recover from pg_xlog/0000000000000000 (log file 0, segment 0) failed: No such file or directory
peter Willis [EMAIL PROTECTED] writes: [EMAIL PROTECTED] /]$ LOG: database system shutdown was interrupted at 2004-10-18 11:41:55 PDT LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid primary checkpoint record LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid secondary checkpoint record PANIC: unable to locate a valid checkpoint record LOG: startup process (pid 2803) was terminated by signal 6 LOG: aborting startup due to startup process failure pg_resetxlog would probably get you to a point where you could start the server, but you should not have any great illusions about the consistency of your database afterward. How did you get into this state, anyway? And what PG version is it? 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] [PERFORM] Performance on Win32 vs Cygwin
Have you looked at the 7.3 configuration file vs. the 8.0. It's possible that the 7.3 file is tweakled better then the 8.0. Have you anaylzed the tables after loading the data into 8.0 On Thu, 14 Oct 2004 12:01:38 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, We are experiencing slow performance on 8 Beta 2 Dev3 on Win32 and are trying to determine why. Any info is appreciated. We have a Web Server and a DB server both running Win2KServer with all service packs and critical updates. An ASP page on the Web Server hits the DB Server with a simple query that returns 205 rows and makes the ASP page delivered to the user about 350K. On an ethernet lan a client pc perceives just under 1 sec performance with the following DB Server configuration: PIII 550Mhz 256MB RAM 7200 RPM HD cygwin Postgresql 7.1.3 PGODBC 7.3.2 We set up another DB Server with 8 beta (same Web Server, same network, same client pc) and now the client pc perceives response of just over 3 sec with the following DB server config: PIII 700 Mhz 448MB RAM 7200 RPM HD 8 Beta 2 Dev3 on Win32 running as a service Is the speed decrease because it's a beta? Is the speed decrease because it's running on Win instead of cygwin? We did not install cygwin on the new DB Server. Thanks, Mike ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] reusing column labels in select
There might be a better way then this but this works SELECT a, a+1 as b from (SELECT 1+1 as a) as ab; On 16 Oct 2004 11:18:48 -0700, ryan [EMAIL PROTECTED] wrote: Hello, Is there any way to reuse a column label in a select list like this: SELECT 1 + 1 AS a, a + 1 AS b; I vaguely remember being able to do something like this in oracle. Any ideas? Thanks! ~RvR ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] reusing column labels in select
* ryan [EMAIL PROTECTED] [2004-10-16 11:18:48 -0700]: Is there any way to reuse a column label in a select list like this: SELECT 1 + 1 AS a, a + 1 AS b; I vaguely remember being able to do something like this in oracle. Any ideas? Thanks! You could nest them... xinu= select (1 + 1) + 1 as answer; answer 3 (1 row) -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] how much ram do i give postgres?
On Tue, 19 Oct 2004 17:42:16 -0400, Tom Lane [EMAIL PROTECTED] wrote: The short answer is no; the sweet spot for shared_buffers is usually on the order of 1 buffers, and trying to go for 75% of RAM isn't going to do anything except hurt. For the long answer see the pgsql-performance list archives. regards, tom lane Well, I didn't find a whole lot in the list-archives, so I emailed that list whith a few more questions. My postgres server is just crawling right now :( -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How do I recover from pg_xlog/0000000000000000 (log
Tom Lane wrote: peter Willis [EMAIL PROTECTED] writes: [EMAIL PROTECTED] /]$ LOG: database system shutdown was interrupted at 2004-10-18 11:41:55 PDT LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid primary checkpoint record LOG: open of /web2-disk1/grip/database/pg_xlog/ (log file 0, segment 0) failed: No such file or directory LOG: invalid secondary checkpoint record PANIC: unable to locate a valid checkpoint record LOG: startup process (pid 2803) was terminated by signal 6 LOG: aborting startup due to startup process failure pg_resetxlog would probably get you to a point where you could start the server, but you should not have any great illusions about the consistency of your database afterward. How did you get into this state, anyway? And what PG version is it? regards, tom lane The server was running with postgres on terabyte firewire 800 drive. A tech decided to 'hot-plug' another terabyte drive into the system without downing the server, umounting the first drive, and then remounting both drives. Since ohci drivers tend to enumerate and mount without using the hardware ID of the drive , the poor kernel got confused and decided that the new drive was first in lineclang! I had a database backup from the previous day. I just used that. I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. I love that 'date' command .. :) date +%F-%H%M%S nice :) Peter ---(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] Database Disappeared
Tom, Thanks so much for your reply. In fact we were not vacuuming this particular db at all! While we have other postgres db's on this same host, this was a recently added db and it was never added to the individual db list to be vacuumed. We have since modified the cronjob to perform a vacuumdb -a -z to avoid such an omission in the future. However, I would like to make doubly sure that the behavior we saw is explainable by a trans id wraparound failure and that we don't have something else lurking. We originally discovered the problem last Friday because the nightly pg_dumpall did not export any rows from any of the tables associated with this instance. From our failure in May, we learned to monitor this backup for problems. Also, when connecting to the instance, (either by the OpenACS application or via psql) selecting, add/del/modifying data from tables in the db all acted completely normal. But, if we attempted to execute a \d or \df from psql connected either as postgres or as the db owner (nsadmin), we would get a No relations found. error. So, in order to preserve the data as much as possible, we actually performed a copy to... stmt for each individual table based on our known list of tables so that we could re-import the data back into a completely rebuilt instance (we dropped and recreated the instance in order to restore all functionality). The behavior remained consistent even after bouncing the postmaster and aolserver. So, my main question is, is it reasonable to say that a trans id wraparound failure could create a situation in which you could use/manipulate user data tables if you refer to the data tables directly but if you tried to use a util (such as pgdump) or an internal psql query like \d or \df it would appear that you have no data tables? Thanks again for your help - we are reviewing all routine maint tasks thoroughly for our environment. On Mon, 2004-10-18 at 16:23, Tom Lane wrote: Roberts, Adam [EMAIL PROTECTED] writes: The following is a link to the last post we had with this same problem: http://openacs.org/forums/message-view?message_id=185837 Given the comment about 500M pg_clog, I would venture that you aren't doing adequate vacuuming and have therefore run into a transaction ID wraparound failure. 500Mb clog corresponds to 2 billion transactions which is right about where things would blow up if you'd not done the necessary database-wide vacuums; and the fact that clog hasn't gotten truncated is pretty strong evidence that you didn't. Please read the administrator's guide: http://www.postgresql.org/docs/7.4/static/maintenance.html (adjust for your PG version as needed) 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] How do I recover from pg_xlog/0000000000000000 (log
On Tue, Oct 19, 2004 at 03:49:04PM -0700, pw wrote: I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. Huh ... be sure to keep some older backup anyway! There was just someone on a list (this one?) whose last two weeks of backups contained no data (a guy with OpenACS or something). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) The eagle never lost so much time, as when he submitted to learn of the crow. (William Blake) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How do I recover from pg_xlog/0000000000000000 (log
On Tue, Oct 19, 2004 at 03:49:04PM -0700, pw wrote: I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. Huh ... be sure to keep some older backup anyway! There was just someone on a list (this one?) whose last two weeks of backups contained no data (a guy with OpenACS or something). Also, if you don't routinely test your backups every now and then, how can you be sure they'll work when you NEED them to? -- Mike Nolan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Where clause limited to 8 items?
Hello Searched around, but could not find this mentioned. I've noticed the following behaviour in 7.4.5: [explain analyse] select * from foo where col1 = 1 or col1 = 2 or col1 = 3 or col1 = 4 or col1 = 5 or col1 = 6 or col1 = 7 or col1 = 8; where an index on foo.col1 exists. The above works fine - the index is used. However, extend the where clause with an extra line (say, col1 = 9) and the index is no longer used. Is there a parameter I can SET to extend the number of items allowed for index usage? Any pointers would be appreciated. Henry This message was sent using MetroWEB's AirMail service. http://www.metroweb.co.za/ - full access for only R73. Free Web Accelerator, WebMail, Calendar, Anti-Virus, Anti-Spam, 10 emails, 100MB personal webspace, and more! Phone Now! 086 11 11 440 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] About upper() and lower to handle multibyte char
Tom Lane wrote: What locale did you initdb in? The most likely explanation for this is that the LC_CTYPE setting is not unicode-compatible. emm, I initdb --no-locale, which means LC_CTYPE=C, but if I don't use it there are some other issue in multibyte comparing (= operator) operation, will try again. Thanks! Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Where clause limited to 8 items?
On Wed, 20 Oct 2004, Henry Combrinck wrote: Hello Searched around, but could not find this mentioned. I've noticed the following behaviour in 7.4.5: [explain analyse] select * from foo where col1 = 1 or col1 = 2 or col1 = 3 or col1 = 4 or col1 = 5 or col1 = 6 or col1 = 7 or col1 = 8; where an index on foo.col1 exists. The above works fine - the index is used. However, extend the where clause with an extra line (say, col1 = 9) and the index is no longer used. Check the estimated number of rows returned. It's presumably believing that the a sequential scan will be cheaper for the estimated number of rows. If the estimated number of rows is significantly off, you may wish to change the statistics target (see ALTER TABLE) for col1 and analyze the table again. If it still is choosing a sequential scan over an index scan and the number of rows is similar, you may want to look at the random_page_cost variable. You have to be careful not too lower it too far that other queries are pessimized the other direction, but some experimentation comparing the real times and estimated costs of queries with and without enable_seqscan=off may help. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How do I recover from pg_xlog/0000000000000000 (log
Mike Nolan wrote: On Tue, Oct 19, 2004 at 03:49:04PM -0700, pw wrote: I set up a cron job to pg_dump and gzip every hour and dump any backup gz files older than 1 week. Huh ... be sure to keep some older backup anyway! There was just someone on a list (this one?) whose last two weeks of backups contained no data (a guy with OpenACS or something). Also, if you don't routinely test your backups every now and then, how can you be sure they'll work when you NEED them to? -- Mike Nolan Hello, If vacuumdb and pg_dump don't work then I have bigger problems than just a hardware burp. It's just like any other (MS incuded) software. You have to trust it until it proves otherwise. I've seen oracle go south because of hardware, etc. too. At least I'm not spending $30,000 for the adventure. I don't get any more satisfaction for the $30 grand than rebuilding from a backup anyway. If I really felt paranoid about it I could have a test server set up and make a cron job that scps the current backup over and builds a database from it. Then queries every table for the last updated record and compares it to the local server. A days work tops. I'm pretty sure the current backup method is OK though. It can even move the database backup off site in case the place burns down. In the case of the fellow with no data, It's difficult to say whether that's real or not. I moved a DB over to another machine and had to open the tar file that came from pg_dump, edit the 'restore.sql' in several places, and run the script manually so I could watch the error logging. All the data was there, it just wasn't going through the COPY command properly (path issues). Also, the proceedural language that I was using for a trigger needed to be installed by 'postgres' user *first* before I was able to make part of the script work. It's pretty easy to forget all the schema stuff in a database over time. Did that guy look in the '.dat' files to see if there was data? Peter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] About upper() and lower to handle multibyte char
Weiping wrote: Tom Lane wrote: What locale did you initdb in? The most likely explanation for this is that the LC_CTYPE setting is not unicode-compatible. finally I get it work, while initdb, we should use matched locale setting and database encoding, like: initdb --locale=zh_CN.utf8 -E UNICODE ... then everything ok (on my platform: slackware 10 and RH9). Emm, I think it's better to add some words in our docs to tell the uesr to do so, because we always to use --no-locale while initdb, because the default locale setting of many Linux destro (normally en_US), would cause the multibyte character compare operaction fail (like select '' = '', that's select 'one'='two' in Chinese, but it return true), and we use UNICODE as database encoding to store multi-language characters (like Japanese and Korean), don't know if the locale setting (zh_CN.utf8) would conflict with those setting. Any better suggestion? Thanks Laser ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sim Zacks wrote: | I have the following function and I am getting an invalid syntax error | when I try to run it. I have tried the function a number of ways | including with named parameters and non-named parameters using the | args array. I also tried it with a tab at the beginning and without. | I've also tried with the $$ and with single quotes and the double | single quoting all the existing single quotes. | | Any help would be greatly appreciated. Easier to help if you actually give the error message, and what version of PostgreSQL you are running might be significant too. | create or replace function BatchBalanceStatus(balance int, needed int, freestock int) returns varchar as | $$ | if balance 0: | return 'Unhandled' | elif freestock = needed: | return 'OK' | else: | return 'Ordered' | $$ language plpythonu create or replace function BatchBalanceStatus(int, int, int) returns varchar as ' ~balance, needed, freestock = args ~if balance 0: ~return Unhandled ~elif freestock = needed: ~return OK ~else: ~return Ordered ' language plpythonu; Works just fine here on 7.4.5 - -- Stuart Bishop [EMAIL PROTECTED] http://www.stuartbishop.net/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBdfmqAfqZj7rGN0oRAiipAJ9X3IoxinVNx/JRwF9OlzSsZMAATQCgh636 b4kuADMg75BBHqaDjV55c+4= =LMiW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpython question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stuart Bishop wrote: | create or replace function BatchBalanceStatus(int, int, int) returns | varchar as ' | ~balance, needed, freestock = args | ~if balance 0: | ~return Unhandled | ~elif freestock = needed: | ~return OK | ~else: | ~return Ordered | ' language plpythonu; | | Works just fine here on 7.4.5 Urgh... minus the ~ characters of course that my mail program helpfully inserted :-P - -- Stuart Bishop [EMAIL PROTECTED] http://www.stuartbishop.net/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBdfo0AfqZj7rGN0oRAu0DAKCX1RknM3U+iDMAixKrJtQlSMPVIgCfYA5A YVvTTcARsnzB8EHVVIc1J+8= =cdg/ -END PGP SIGNATURE- ---(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
