[GENERAL] php postgresql
I am wondering how i can call stored procedure from php If anyone can help it would great for me Thanks Mary ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] php postgresql
Mary Adel wrote:
I am wondering how i can call stored procedure from php
If anyone can help it would great for me
Very small code snippet.
$sql = SELECT my_func('para');
$Result = pg_query($sql);
...
Regards
Russell Smith
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Thread scheduling in postgresql 8.1.3 on windows
I'm writing my thesis on performance testing. First I had postgresql 8.0.1 installed. However when stress testing with multiple threads I got an unacceptable amount of context switches. Thats why I installed 8.1.3 . With 8.1.3 I dont have the context switch problem, however now there is a problem with the standard deviation of response times. With my stress test software I use 5 concurrent threads issueing the same request, when a response has been received by one of these threads immediatly a new request is issued. Now the strange thing is that with postgresql 8.1.3 the standard deviation of the response time is large, in fact it is even much larger than for 8.0.1 . I think this is strange since all requests are similar. Hence, they should all be served in approximatly the same amount of time (as is the case in 8.0.1). The only reason I can think of is that postgresql allows some of its workerthreads to run more frequenly than others. However, since all requests are similar I don't see why some threads are allowed to execute more frequently than others. Below is the standard deviation of two requests that I've tested. standard deviation 8.0.1 8.1.3 request 1 14 ms. 111 ms. request 2 44 ms. 706 ms. Does anyone know why CPU time is not spread evenly over the worker threads. Thanx in advance, Ingmar
[GENERAL] Installing PosygreSQL 8.1 on Windows2003 server in terminal session (remote desktop)
Hi, I try to install PostgreSQL 8.1.3 on Windows2003 server. I have no phisical access to the server, only through remote desktop. When I run installer, i get this message: The PostgreSQL installer must be run on the system console, not in a terminal service session. Is there anything I can do to install using installer, or do I need to instal POstgresql by hand? If so (by hand), is there any step-by-step manual describing installation on windows? Thanks in advance, Pawel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Looking for a fix to index bloat
This isn't what you asked for, but if you can afford a modest amount of downtime you could always drop/recreate the indexes. Unfortunately, we cannot afford any down time. That is why we are looking to pay someone to help us implement Tom Lane's suggested solution. Brent
Re: [GENERAL] Special offer with a possible dontation to the project
Hmmm... you should name a date. The first day of Spring doesn't start here until 1st September. Regards Andrew -Original Message- From: Dave Page [mailto:[EMAIL PROTECTED] Sent: Thursday, 2 March 2006 06:48 To: Tony Caduto Cc: pgsql-general@postgresql.org Subject: Re: Special offer with a possible dontation to the project -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto Sent: 01 March 2006 19:44 Cc: pgsql-general@postgresql.org Subject: [GENERAL] Special offer with a possible dontation to the project Our goal is to reach 1000 sales by the first day of spring, You might want to name a date for that - the first day of spring is just ending here :-) Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postgres does not accept socket connections
Hi All, I have the following problem. I am able to access a PostgreSQL database only as localhost. If I try to open psql from a different machine with -h option, then I get the following error message. The database resides on a server running Fedora Core 4 and PostgreSQL 8.1.1 server. I am trying to access the database from another server running RedHat 9 and PostgreSQL 7.3.2. Command psql -U postgres -h server-name database-name Error psql: could not connect to server: Connection refused Is the server running on host server-name and accepting TCP/IP connections on port 5432? Similarly, I am unable to access the database using PHP's postgres API, with the host set as server-name. However, I am able to locally access if I specify host as localhost. I need the PostgreSQL server on the machine which contains the database to accept socket connections. Any help in this regard will be appreciated. Thanks in advance - Kishore ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Default Access Exclusive Lock on Update?
This may be a newbie question, but according to the 7.4 docs, an ACCESS EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. However, when viewing pg_locks during the execution of a stored procedure that does not perform any of the above commands, I see that the table it is working on is locked by ACCESS EXCLUSIVE. I have also tested that I can not perform a simple SELECT on the locked table while the SP is executing. Can anyone provide some insight? Thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Looking for a fix to index bloat
Have you tried vacuuming more frequently? Yes, we vacuum every45-60 minutes. We boarder on having a vacuum running continuously vacuumingsome table in the database. Brent
[GENERAL] Without OIDs
Hello, I'm creating a temporary table in a function and using Alter Table ... Without OIDs, but when the function is called for the second time I get an error relation with OIDs ... doesn't exist on an insert statement to the table. I'm using Postgre 8.0 which says that supports without OIDs. Any suggestions? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres does not accept socket connections
After a long battle with technology, [EMAIL PROTECTED], an earthling, wrote: I need the PostgreSQL server on the machine which contains the database to accept socket connections. Any help in this regard will be appreciated. Look for the configuration file postgresql.conf. It is doubtless set up to only accept local connections. That is the default configuration, and represents a decent default security measure. Look in that file for a variable called listen_addresses. You'll doubtless find it commented out. The suggested value is *, that is, to accept connections coming to all IP interfaces. That's probably what you want to change it to. Restart the database (reloading config won't suffice, I don't think). You should have remote access. You may then get complaints that pg_hba.conf won't admit the connections; that file is in the same directory. If you change it, you need only reload PostgreSQL configuration; you don't need to restart the database... -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://linuxdatabases.info/info/slony.html REALITY is a policy phased out early in the Eisenhower administration. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres does not accept socket connections
am 02.03.2006, um 4:37:02 -0800 mailte [EMAIL PROTECTED] folgendes: Hi All, I have the following problem. I am able to access a PostgreSQL database only as localhost. If I try to open psql from a different machine with -h option, then I get the following error message. The database resides on a server running Fedora Core 4 and PostgreSQL 8.1.1 server. I am trying to access the database from another server running RedHat 9 and PostgreSQL 7.3.2. Command psql -U postgres -h server-name database-name Error psql: could not connect to server: Connection refused Is the server running on host server-name and accepting TCP/IP connections on port 5432? Check the postgresql.conf for the value listen_addresses. My guess: listen_address is at the moment 'localhost'. Change this to the LAN-address or '*'. Read also the pg_hba.conf - file! http://www.postgresql.org/docs/current/static/client-authentication.html#AUTH-PG-HBA-CONF HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a web framework for postgresql?
Drupal is really nice extensible web framework written in PHP. http://drupal.org John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] a web framework for postgresql?
falcon wrote: Hi, Most of the web applications I work on are nothing more than front-ends to postgresql. I have used Perl (CGI), Java, C# and am now looking at Django. Each generation of frameworks lessens the pain of donig web-apps, but it still seems redundant. Does any one know of a framework where the database server (or a web/app server integrated with a DB server) serves web pages? The database contains the data-model; names of tables, names of attributes, their types, foreign key relationships among tables...that's A LOT of information. Sql query, views or stored procs could serve as 'reports' served off the data. Perhaps the only thing that needs to be outside a database is something that describes how the data is to be displayed (CSS). There could be some java/c#/python/ruby/whatever engine which takes all the information provided in the database and generate html/xhtml, default css, javascript validation, etcbut all that should be invisible to the user. Any one know of such a framework? (I'm asking this in pgsql because such a framework will have to be fairly closely linked to a database...and I mainly use pgsql). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings You could probably do put together a single executable with ZILD http://zild.org/index.csp and a DB libraray (sqlite, other embeddable DB) or package ZILD linked to libpq along with a PostgreSQL installation. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing PosygreSQL 8.1 on Windows2003 server in terminal session (remote desktop)
Are you connecting to the console session on your Win2K3 server or to one of the other two available remote desktop sessions? (To connect to the console session using the RDP client from XP, you need to inclde /console at the _end_ of your command line: ie: mstsc /v:MyRemoteServerName /console. Using the Win2K3 RDP client, I think there is a checkbox to do this.) Don't know if this will help with the PostgreSQL install since I still do all mine at the servers at the moment, but it does work with many other apps that want to be installed at the console. - Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of koder Sent: Wednesday, March 01, 2006 1:56 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Installing PosygreSQL 8.1 on Windows2003 server in terminal session (remote desktop) Hi, I try to install PostgreSQL 8.1.3 on Windows2003 server. I have no phisical access to the server, only through remote desktop. When I run installer, i get this message: The PostgreSQL installer must be run on the system console, not in a terminal service session. Is there anything I can do to install using installer, or do I need to instal POstgresql by hand? If so (by hand), is there any step-by-step manual describing installation on windows? Thanks in advance, Pawel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Thread scheduling in postgresql 8.1.3 on windows
Ingmar Brouns [EMAIL PROTECTED] writes: With 8.1.3 I dont have the context switch problem, however now there is a problem with the standard deviation of response times. Interesting, but if you don't provide a test case it's unlikely we can comment usefully. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres does not accept socket connections
A. Kretschmer [EMAIL PROTECTED] writes: am 02.03.2006, um 4:37:02 -0800 mailte [EMAIL PROTECTED] folgendes: psql: could not connect to server: Connection refused Is the server running on host server-name and accepting TCP/IP connections on port 5432? Check the postgresql.conf for the value listen_addresses. My guess: listen_address is at the moment 'localhost'. Change this to the LAN-address or '*'. If listen_addresses is correct and you still get Connection refused, the other thing to look at is the kernel's packet filtering rules (iptables). I believe the default configuration on FC4 will reject external connections to 5432. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Insert fails when it shouldn't
The table: CREATE TABLE DILOSIS ( DL_AEM INT NOT NULL, DL_CODE VARCHAR(6) NOT NULL, DL_YEAR INT NOT NULL, DL_GRADE FLOAT(3) NOT NULL, DL_LESTYPE INT NOT NULL, DL_SEMESTER INT NOT NULL, DL_DM INT NOT NULL, DL_COEF FLOAT(3), --Syntelestis ptyxiou DL_CAT_NORM BOOLEAN NOT NULL, --TRUE NORMAL, FALSE APALLAGI DL_TEI INT NOT NULL, DL_THERINO BOOLEAN NOT NULL ); ALTER TABLE DILOSIS ADD CONSTRAINT pk_dilosis PRIMARY KEY (DL_AEM, DL_CODE, DL_YEAR, DL_TEI, DL_SEMESTER); The table is empty and statements stored in a file are executed with psql. = Statements === \encoding iso_8859_7 delete from dilosis; INSERT INTO DILOSIS VALUES(1, 'ΓΕ0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true); INSERT INTO DILOSIS VALUES(1, 'ΗΥ0100', 2000, 0.00, 1, 1, 5, 5.00, true, 1, true); The second insert fails with an error message: psql:a.sql:4: ERROR: duplicate key violates unique constraint pk_dilosis The second insert is identical to the first one except the DL_CODE value. 1:ΓΕ0100 (Gamma Epsilon ..) 2:ΗΥ0100 (Heta Ypsilon ..) I think what is hapening is that the two first letters are ignored and the two values are found identical since the postfix is 0100 for bouth of them. What can be done so the second instert does not fail? Anakreon -- Three words describe our society:homo homini lupus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [SQL] Without OIDs
On Thu, Mar 02, 2006 at 05:14:54AM -0800, Emil Rachovsky wrote: I'm creating a temporary table in a function and using Alter Table ... Without OIDs, but when the function is Why alter the table? Why not specify WITHOUT OIDS when you create the table? called for the second time I get an error relation with OIDs ... doesn't exist on an insert statement to the table. I'm using Postgre 8.0 which says that supports without OIDs. Any suggestions? This problem isn't related to WITHOUT OIDS. See FAQ 4.19 for an explanation: http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Question about pg_upgrade
Hello, We have a question about pg_upgrade. We work with PostGreSQL 8.1.2. We have seen that with the 8.1.0 release, some programs from contrib are now included in PostGreSQL, such as dbsize. Is it the same thing with pg_upgrade ? Does it still exist ? If not, is there another tool to replace it ? Regards, Agnès Alexandra ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Question about the contrib rpm ?
Hello, We have generated a rpm with PostGreSQL 8.1.2, from the tar.gz file, compiled with the icc Intel compiler on Red Hat Enterprise Linux 4 AS. During the compilation, we have had few warnings, but nevertheless, we work with this rpm without trouble. We have a question about the contrib rpm. To have some tools like the start_scripts, should we : 1. specify an option or anything else during the generation of the rpm ? (from tar.gz file) 2. download the contrib rpm from the PostGreSQL web site and use it with our rpm ? 3. download the contrib rpm from the PostGreSQL web site and recompile it with the icc Intel compiler ? We think that it is a newbie question, but we haven't found some tips about this. Thanks a lot. Regards, Agnès Alexandra ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
CLI = Command Line Interface GUI = Graphical User Interface WSI = Web Server Interface I need a CLI app. Sorry I was not clear on this. On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote: On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. signature.asc Description: This is a digitally signed message part
[GENERAL] How to save and restore a template database ?
Hello, We have a question concerning backup / restore of database. Is it right if we say that for having a complete backup, we have do : 1. use pg_dumpall 2. do a backup of the modified template1 database with pg_dump ? Our question is how to save and restore a template database, as template1 ? Regards, Agnès BOCCHINO Alexandra DANTE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about pg_upgrade
DANTE ALEXANDRA wrote: Hello, We have a question about pg_upgrade. We work with PostGreSQL 8.1.2. We have seen that with the 8.1.0 release, some programs from contrib are now included in PostGreSQL, such as dbsize. Is it the same thing with pg_upgrade ? Does it still exist ? If not, is there another tool to replace it ? pg_upgrade has not worked for several releases, and was moved to pgfoundry or gborg. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to save and restore a template database ?
On Thu, Mar 02, 2006 at 05:17:12PM +0100, DANTE ALEXANDRA wrote: We have a question concerning backup / restore of database. Is it right if we say that for having a complete backup, we have do : 1. use pg_dumpall 2. do a backup of the modified template1 database with pg_dump ? Our question is how to save and restore a template database, as template1 ? pg_dumpall should dump the modified contents of template1. Is it not doing so? What version of PostgreSQL are you using? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Oh no, you were perfectly clear. I wrote my post because I don't know of any CLI tools for windows that do this either, so it would seem that a decent CLI tool wrapped in a GUI would still be acceptable. I mean, it's a question of symatics at that point. So, that's why I suggested some of the GUI tools that have a type your query here window. Sometimes you just gotta use what you can get. Some of them have history and all that fancy stuff, they just live inside a GUI app. Me personally, I switched of Windows long ago due to issues like this. On Thu, 2006-03-02 at 10:34, Roy Souther wrote: CLI = Command Line Interface GUI = Graphical User Interface WSI = Web Server Interface I need a CLI app. Sorry I was not clear on this. On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote: On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Solaris 10 ZFS Postgresql request for comments
Hi all, I'm querying for feedback/comments. Wondering what the list thinks of the following. Assume this is to provide a production database for a small company or a department. Production hours 5am-9pm for the most part so night-time downtime if/when necessary would not be a problem. Platform: SUN ultra 20 or intel/amd based PC Hard drive1 = OS Solaris 10(+) Hard drives2 3 = ZFS mirrored pool with PostgreSQL intalled Hard drives4-N = ZFS raided PGDATA Would this be considered viable? Has anyone implemented anything similar? Any obvious pitfalls that anyone is aware of? Thanks, reid ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Actually not semantics. I need to script the program and there is no way to script a GUI app. I have started using PHP. Not a great solution, extreme overkill, but it does work well. On Thu, 2006-03-02 at 11:29 -0600, Scott Marlowe wrote: Oh no, you were perfectly clear. I wrote my post because I don't know of any CLI tools for windows that do this either, so it would seem that a decent CLI tool wrapped in a GUI would still be acceptable. I mean, it's a question of symatics at that point. So, that's why I suggested some of the GUI tools that have a type your query here window. Sometimes you just gotta use what you can get. Some of them have history and all that fancy stuff, they just live inside a GUI app. Me personally, I switched of Windows long ago due to issues like this. On Thu, 2006-03-02 at 10:34, Roy Souther wrote: CLI = Command Line Interface GUI = Graphical User Interface WSI = Web Server Interface I need a CLI app. Sorry I was not clear on this. On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote: On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
You could easily create a console application with Lazarus or Delphi to do this, the console app could read the script from params, standard input, or a text file and execute it against the server of your choice using ODBC or a native interface to libpq. Tony ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Then yeah, php or perl are your only choices. Again, as is often the case, better to tell us what you're tying to do, and ask for suggestions, then to tell us what tool you think you need, and leave us guessing as to motivation. Happens a lot though, don't worry about it. It's just better to describe the problem generally. Nice thing about using php or perl is that if you reach a point to where ODBC can't give you what you need, there's native connect libraries for most anything out there you can switch over to. On Thu, 2006-03-02 at 11:40, Roy Souther wrote: Actually not semantics. I need to script the program and there is no way to script a GUI app. I have started using PHP. Not a great solution, extreme overkill, but it does work well. On Thu, 2006-03-02 at 11:29 -0600, Scott Marlowe wrote: Oh no, you were perfectly clear. I wrote my post because I don't know of any CLI tools for windows that do this either, so it would seem that a decent CLI tool wrapped in a GUI would still be acceptable. I mean, it's a question of symatics at that point. So, that's why I suggested some of the GUI tools that have a type your query here window. Sometimes you just gotta use what you can get. Some of them have history and all that fancy stuff, they just live inside a GUI app. Me personally, I switched of Windows long ago due to issues like this. On Thu, 2006-03-02 at 10:34, Roy Souther wrote: CLI = Command Line Interface GUI = Graphical User Interface WSI = Web Server Interface I need a CLI app. Sorry I was not clear on this. On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote: On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Scott Marlowe wrote: Then yeah, php or perl are your only choices. Again, as is often the case, better to tell us what you're tying to do, and ask for suggestions, then to tell us what tool you think you need, and leave us guessing as to motivation. Happens a lot though, don't worry about it. It's just better to describe the problem generally. Nice thing about using php or perl is that if you reach a point to where ODBC can't give you what you need, there's native connect libraries for most anything out there you can switch over to. ruby is another option if you're looking at scripting languages with DB interfaces. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Reid Thompson schrieb: Scott Marlowe wrote: Then yeah, php or perl are your only choices. Again, as is often the case, better to tell us what you're tying to do, and ask for suggestions, then to tell us what tool you think you need, and leave us guessing as to motivation. Happens a lot though, don't worry about it. It's just better to describe the problem generally. Nice thing about using php or perl is that if you reach a point to where ODBC can't give you what you need, there's native connect libraries for most anything out there you can switch over to. ruby is another option if you're looking at scripting languages with DB interfaces. And of course python .-) (which has built in interactive mode) regards Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
On Mar 2, 2006, at 8:34 AM, Roy Souther wrote: CLI = Command Line Interface GUI = Graphical User Interface WSI = Web Server Interface I need a CLI app. Sorry I was not clear on this. t4esql? Never used it, but it may be what you need. It may or may not be open source, so if that's a personal hangup rather than a best-tool-for-the-job thing that may be an issue. Cheers, Steve On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote: On Wed, 2006-03-01 at 17:00, Roy Souther wrote: I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Not for ODBC. Have you looked at any of the GUIs to see if they have a type a query here window in them? ---(end of broadcast)--- TIP 6: explain analyze is your friend Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] query timeout
Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query timeout
Never-mind that. I'm assuming statement_timeout is what I need? On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELinux strangeness with 8.1.2 and 8.1.3
Just Someone [EMAIL PROTECTED] writes: Some more clues that might help you see if there's a real problem, is that the /var/lib/pgsql/data/postmaster.pid file is created with the a SELinux context that's different from the rest. It is created with system_u:object_r:file_t while the rest of the files are created with root:object_r:postgresql_db_t. And the postmaster (when using runuser) fails on accessing it according to the audit log. Hmm. That seems like a SELinux policy bug. It doesn't happen for me: the pid file is created with the same context the other files have. -rw--- postgres postgres root:object_r:postgresql_db_tpostmaster.pid Are you sure that your SELinux policy is up-to-date? Maybe you need to do a restorecon on the postgres binaries and/or /var/lib/pgsql/data. Some more info about the system: * FC4 fully updated * Postgres 8.1.3 built from the PGDG SRPMs * Dual Opteron I tried it myself on a freshly-updated FC4 x86_64 system, using the current FC5 SRPMs, and couldn't see a problem. Red Hat's SRPMs are not exactly like the PGDG ones, but the only difference I can find that looks at all relevant to SELinux is this one in the init script: 132c134 [ -x /usr/bin/chcon ] /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t $PGLOG --- [ -x /usr/bin/chcon ] /usr/bin/chcon -t postgresql_log_t $PGLOG and that's not about the pid file. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Since you mentioned Java, I thought I'd mention this. There is no reason
you cannot write a command line tool using Java. I'm no Java guru, but I
quickly wrote a simple Java program to connect to PostgreSQL (on a Linux
box) from a Windows box. I used a nice command line parser library called
JSAP to allow me to specify fancy command line parameters for queries. You
can then easily script the use of your Java program.
Sample output (remember this is quick and dirty, you can fancy it up all
you want):
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q
select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q
select id, testdata from test2 -v
Number of cols specified: 2, query: select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli --cols
2 --query select id, testdata from test2 -v
Number of cols specified: 2, query: select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli
Usage: java test_db_cli
(-c|--cols) cols (-q|--query) query [-v|--verbose]
Of course, you could add parameters for database name, etc.
My sample program is 95 lines (including comments, etc.). It may not be
great Java, so don't laugh:
Installing the PostgreSQL .jar driver is as simple as downloading it.
/* Java sample jdbc program test_db_cli.java
*/
import java.io.*;
import java.sql.*;
import com.martiansoftware.jsap.*;
public class test_db_cli
{
Connection conn; // holds database
connection
Statement stmt; // holds SQL
statement
public test_db_cli(int returnVals, String queryStr) throws
ClassNotFoundException, FileNotFoundException, IOException, SQLException
{
int i = 0;
int c = 0;
Class.forName(org.postgresql.Driver); // load database
interface
// connect to the database - ip address, user, etc. have been
munged - just insert your real values:
conn =
DriverManager.getConnection(jdbc:postgresql://10.20.xx.xx/testdb1,
yourdbuser, yourpwifnecessary);
stmt = conn.createStatement();
ResultSet res = stmt.executeQuery(queryStr);
if (res != null)
while(res.next())
{
i++;
System.out.print(Row: + i + :\t); //output
the data any way you want
for (c=1;c=returnVals;c++) {
String item1 = res.getString(c);
System.out.print(res.getString(c) + \t);
}
System.out.println();
}
res.close();
stmt.close();
conn.close();
}
public static void main(String args[]) throws Exception {
JSAP jsap = new JSAP();
int numcols;
String query;
FlaggedOption opt1 = new FlaggedOption(cols)
.setStringParser(JSAP.INTEGER_PARSER)
.setDefault(1)
.setRequired(true)
.setShortFlag('c')
.setLongFlag(cols);
jsap.registerParameter(opt1);
FlaggedOption opt2 = new FlaggedOption(query)
.setStringParser(JSAP.STRING_PARSER)
.setRequired(true)
.setShortFlag('q')
.setLongFlag(query);
jsap.registerParameter(opt2);
Switch verbose = new Switch(verbose)
.setShortFlag('v')
.setLongFlag(verbose);
jsap.registerParameter(verbose);
JSAPResult config = jsap.parse(args);
// check whether the command line was valid, and if it wasn't,
// display usage information and exit.
if (!config.success()) {
System.err.println();
System.err.println(Usage: java
+ test_db_cli.class.getName());
System.err.println(
+ jsap.getUsage());
System.err.println();
System.exit(1);
}
numcols=config.getInt(cols);
query=config.getString(query);
Re: [GENERAL] Question about the contrib rpm ?
DANTE ALEXANDRA [EMAIL PROTECTED] writes: We have a question about the contrib rpm. To have some tools like the start_scripts, should we : I don't believe the RPM distribution includes contrib/start-scripts (mainly because that contrib directory has no Makefile that would install the scripts). I cannot see why you'd want them, since the postgresql-server RPM already has an init script that is considerably more appropriate for RPM-based distros than the one that's in contrib. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Exception: An I/O error occured while sending to the backend
Hello, While doing performance tests on Windows Server 2003 I get the following: An I/O error occured while sending to the backend. org.postgresql.util.PSQLException: An I/O error occured while sending to the backend. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250) In my server log, I have: 2006-03-02 12:31:02 5692 6436342 LOG: could not receive data from client: A non-blocking socket operation could not be completed immediately. At the time my box is fairly heavy loaded, but still responsive. Server and JBoss appserver live on the same dual 2Ghz Opteron. A quick Google told me that: 1. More people have seen this. 2. No solutions. 3. The server message appears to indicate an unhandled WSAEWOULDBLOCK winsock error on recv(), which MSDN said is to be expected and should be retried. Is this a known bug? jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
On Wed, 2006-03-01 at 16:00 -0700, Roy Souther wrote: I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. The Perl DBI Shell is intended to be the kind of thing your asking for: http://search.cpan.org/~tlowery/DBI-Shell-11.93/ It's very basic, but functional. I have used it on Windows for accessing SQLite. It can connect using DBD::ODBC, but can also use any other native DBD drivers you might have installed. Regards Grant ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
You should also be able to Windows Scripting Host for this.
It has complete ODBC connection support, generally comes installed on all
versions of windows, and Microsoft has buckets of example scripts available
on their site.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 12:14 PM
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.
Since you mentioned Java, I thought I'd mention this. There is no reason
you cannot write a command line tool using Java. I'm no Java guru, but I
quickly wrote a simple Java program to connect to PostgreSQL (on a Linux
box) from a Windows box. I used a nice command line parser library called
JSAP to allow me to specify fancy command line parameters for queries. You
can then easily script the use of your Java program.
Sample output (remember this is quick and dirty, you can fancy it up all
you want):
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q
select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q
select id, testdata from test2 -v
Number of cols specified: 2, query: select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli --cols
2 --query select id, testdata from test2 -v
Number of cols specified: 2, query: select id, testdata from test2
Row: 1: 1 abcde
Row: 2: 2 fghijkl
Row: 3: 3 mnopqrs
Row: 4: 4 the quick brown
Row: 5: 5 fox jumped over the
Row: 6: 7 lazy dog.
C:\JavaPgms\databasejava -classpath
.;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli
Usage: java test_db_cli
(-c|--cols) cols (-q|--query) query [-v|--verbose]
Of course, you could add parameters for database name, etc.
My sample program is 95 lines (including comments, etc.). It may not be
great Java, so don't laugh:
Installing the PostgreSQL .jar driver is as simple as downloading it.
/* Java sample jdbc program test_db_cli.java
*/
import java.io.*;
import java.sql.*;
import com.martiansoftware.jsap.*;
public class test_db_cli
{
Connection conn; // holds database
connection
Statement stmt; // holds SQL
statement
public test_db_cli(int returnVals, String queryStr) throws
ClassNotFoundException, FileNotFoundException, IOException, SQLException
{
int i = 0;
int c = 0;
Class.forName(org.postgresql.Driver); // load database
interface
// connect to the database - ip address, user, etc. have been
munged - just insert your real values:
conn =
DriverManager.getConnection(jdbc:postgresql://10.20.xx.xx/testdb1,
yourdbuser, yourpwifnecessary);
stmt = conn.createStatement();
ResultSet res = stmt.executeQuery(queryStr);
if (res != null)
while(res.next())
{
i++;
System.out.print(Row: + i + :\t); //output
the data any way you want
for (c=1;c=returnVals;c++) {
String item1 = res.getString(c);
System.out.print(res.getString(c) + \t);
}
System.out.println();
}
res.close();
stmt.close();
conn.close();
}
public static void main(String args[]) throws Exception {
JSAP jsap = new JSAP();
int numcols;
String query;
FlaggedOption opt1 = new FlaggedOption(cols)
.setStringParser(JSAP.INTEGER_PARSER)
.setDefault(1)
.setRequired(true)
.setShortFlag('c')
.setLongFlag(cols);
jsap.registerParameter(opt1);
FlaggedOption opt2 = new FlaggedOption(query)
.setStringParser(JSAP.STRING_PARSER)
.setRequired(true)
.setShortFlag('q')
.setLongFlag(query);
jsap.registerParameter(opt2);
Switch verbose = new Switch(verbose)
.setShortFlag('v')
.setLongFlag(verbose);
jsap.registerParameter(verbose);
JSAPResult config = jsap.parse(args);
// check whether the
[GENERAL] Linux cluster application
I'm writing a parallel/distributed application that assesses the performance impact of frequent insertions/selects to databases on parallel file systems in a linux cluster environment. Currently, the application calls for a database server to be running on each node of the cluster. Since my end-users may or may not have root access to the machines in the various clusters being used, I'm installing postgres from within the user's account to facilitate the starting and stopping of the postmaster without the need of root access. My general question is if anyone has experience with such a situation where the database server would need to be started on each machine in a parallel environment. If so, are there any suggestions for working with such a condition? Thanks. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Linux cluster application
On Thu, 2006-03-02 at 14:41, Andrew Watkins wrote: I'm writing a parallel/distributed application that assesses the performance impact of frequent insertions/selects to databases on parallel file systems in a linux cluster environment. Currently, the application calls for a database server to be running on each node of the cluster. Since my end-users may or may not have root access to the machines in the various clusters being used, I'm installing postgres from within the user's account to facilitate the starting and stopping of the postmaster without the need of root access. My general question is if anyone has experience with such a situation where the database server would need to be started on each machine in a parallel environment. If so, are there any suggestions for working with such a condition? You could set up ssh keys with no passphrase and use ssh to do it in a short shell script. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] [Fwd: Schema Question]
Hi,
I have some tables and functions in the public schema. This was
probably not the right thing to do.
I have a seperate schema with a function and table. The function does a
select on the table.
The table and function exist in public schema and betteridea schema. I
have a user betteridea who owns the betteridea schema.
The table in public schema has identical structure but different data to
the table in betteridea schema.
table employee_list
function zfunc_get_employee
If I connect as the betteridea user and do
select * from zfunc_get_employee('1234');
or
select * from betteridea.zfunc_get_employee('1234');
I get the correct result the result based on the records in the
betteridea.employee_list table in both cases.
If I connect as postgres user and do select * from
betteridea.zfunc_get_employee('1234');
or
select * from zfunc_get_employee('1234');
with no schema in front of it.
It is returning the data from the public.employee_list in both cases!
I would have expected that the function in the betteridea schema would
access the table in the betteridea schema without having to specifically
prefix the table name with the schema name.
My whole plan here is to have a schema per client and use the schemas to
segregate the data. I would like not to have to change the functions to
prefix table names with schema names.
The betteridea.zfunc_get_employee function below.
Any thoughts?
Oisin
CREATE OR REPLACE FUNCTION newschema.zfunc_get_employee( int4) RETURNS
varchar AS
$BODY$DECLARE
v_extn integer;
v_employee varchar;
BEGIN
v_extn:=$1;
select employee into v_employee from employee_list where extension =
v_extn;
return v_employee;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Problem with Function
Hi, I'm new with pgSQL and I'm not sure why, but I
keep getting the same error. I want to call a function
that returns a cursor and I keep getting the following
exception:
org.postgresql.util.PSQLException: ERROR: cursor
unnamed portal 1 does not exist
I would appreciate any help with this problem.
This is my JDBC code:
Connection objlConnection =
ConnectionPool.getConnection(saJNDI);
CallableStatement statement = null;
ResultSet rs = null;
try{
statement = objlConnection.prepareCall({? = call
ret_user(?)});
statement.registerOutParameter(1, Types.OTHER);
statement.setObject(2, munoze);
statement.execute();
rs = (ResultSet)statement.getObject(1);
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
rs.close();
statement.close();
} catch (SQLExcepton e){
...
This is my function code:
CREATE OR REPLACE FUNCTION
traer_usuario(pnombreusuario varchar)
RETURNS refcursor AS
$BODY$
DECLARE
ccursor refcursor;
BEGIN
open ccursor for
select nombreusuario,
contrasena,
idrol
from usuario
where nombreusuario = quote_literal($1);
RETURN ccursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with Function
On Thu, Mar 02, 2006 at 03:18:14PM -0600, Eduardo Muoz wrote: Hi, I'm new with pgSQL and I'm not sure why, but I keep getting the same error. I want to call a function that returns a cursor and I keep getting the following exception: org.postgresql.util.PSQLException: ERROR: cursor unnamed portal 1 does not exist Cursors are closed at the end of the transaction that created them. Is your JDBC code calling the function and using the cursor in the same transaction? I'd guess that autocommit is enabled, so unless you explicitly start a transaction then each command has its own transaction. where nombreusuario = quote_literal($1); You shouldn't need quote_literal here since you're not building a string for EXECUTE. In fact, not only is it unecessary but it'll probably cause the query not to match what you were expecting. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Data corruption zero a file - help!!
Thanks for the pointers Michael! Which config file will tell us how big the bock sizes are? Cheers Noel Michael Fuhr wrote: On Wed, Mar 01, 2006 at 04:12:53PM +1100, Noel Faux wrote: Now after doing some searches I managed to work out that the data corruption starts at 902292.137 using this sql: SELECT * FROM gap WHERE ctid = '(902292,$x)' Where $x I changed from 1-150. as mentioned on this post:http://archives.postgresql.org/pgsql-general/2005-11/msg01148.php Following this post it seems all we need to do is re-zero from this point on. However we're not sure which file to do this in. This earlier message in that thread should be helpful: http://archives.postgresql.org/pgsql-general/2005-11/msg01141.php I've worked out the database/relation files are $PGDATA/37958/111685332.* with the max * being 101. Is your table really over 100G? Anyway, if the block size is 8192 then 902292 sould be in the .6 file. If you can spare the time then you might run the dd and od commands that Tom Lane mentions in the above message and post the output. I think the command would be dd bs=8k skip=115860 count=1 if=$PGDATA/37958/111685332.6 | od -x See Tom's message for how I arrived at .6 and 115860 (and verify the math yourself). begin:vcard fn:Noel Faux n:Faux;Noel org:Monash University;Biochemistry and Molecular Biology adr:;;;Clayton;Vic;3800;Australia email;internet:[EMAIL PROTECTED] tel;work:+61 03 9905 1418 url:http://vbc.med.monash.edu.au/~fauxn version:2.1 end:vcard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Data corruption zero a file - help!!
On Fri, Mar 03, 2006 at 09:56:40AM +1100, Noel Faux wrote: Which config file will tell us how big the bock sizes are? Run the query SHOW block_size in the database or use pg_controldata from the shell. It's probably 8192; changing it is done at compile time. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ERROR: column datpath does not exist
Hi, Whenever I try to pg_dump -i my database from the bash, i receive the following error. pg_dump: server version: pg_dump.mo; pg_dump version: 7.3.10-RH pg_dump: proceeding despite version mismatch pg_dump: SQL command failed pg_dump: Error message from server: ERROR: column datpath does not exist pg_dump: The command was: select (select usename from pg_user where usesysid = datdba) as dba, encoding, datpath from pg_database where datname = 'mpv' What's that datpath about? Is it a version problem? postgres (PostgreSQL) 7.3.10-RH Thanks in advance, Sascha ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: column datpath does not exist
Sascha Nepper [EMAIL PROTECTED] writes: What's that datpath about? Is it a version problem? postgres (PostgreSQL) 7.3.10-RH Yes. Your server must be PG 8.0 or 8.1. Using the -i switch to override pg_dump's version check is hardly ever a good idea --- instead, find a newer version of pg_dump. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Default Access Exclusive Lock on Update?
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] would write:
For anyone that is interested, my problem was solved on another list.
Turns out the TRUNCATE command that I run at the beginning of the SP
creates and holds an access exclusive lock on the table for the entire
duration of the SP. I changed it to DELETE FROM and my problem was
fixed.
I was thinking of mentioning it; didn't think to...
By the way, if you're taking the approach of emptying the table out
entirely this way, you'll want to either:
a) Vacuum the table each time, or
b) Make sure you do use TRUNCATE once in a while
Otherwise the number of dead tuples will grow ungraciously, and you'll
find efficiency drops :-(.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/slony.html
Inclusion of very old messages from others makes for an impressive show.
-- from the Symbolics Guidelines for Sending Mail
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Negative offsets
Bruce Momjian wrote: CSN wrote: I was playing around with negative offsets: select * from table1 order by col1 offset -5 limit 25; select * from table1 order by col1 offset -25 limit 25; select * from table1 order by col1 offset -250 limit 25; They all return the same resultset (offset 0). Is there even any point in allowing negative offsets - such as maybe someday they'll offset backwards? Actually we allow negative offsets and limits. Seems we should disallow them. Yes, it would be interesting if they did offsets relative to the end of the result set. Seems most people just want to leave our current behavior unchanged. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Solaris 10 ZFS Postgresql request for comments
After a long battle with technology, [EMAIL PROTECTED] (Reid Thompson), an earthling, wrote: Hi all, I'm querying for feedback/comments. Wondering what the list thinks of the following. Assume this is to provide a production database for a small company or a department. Production hours 5am-9pm for the most part so night-time downtime if/when necessary would not be a problem. Platform: SUN ultra 20 or intel/amd based PC Hard drive1 = OS Solaris 10(+) Hard drives2 3 = ZFS mirrored pool with PostgreSQL intalled Hard drives4-N = ZFS raided PGDATA Would this be considered viable? Has anyone implemented anything similar? Any obvious pitfalls that anyone is aware of? This is, in principle, a pretty generic sort of configuration that ought to be fine for a not-overly-heavily loaded departmental sort of application. We have much smaller systems than that in production for applications that don't take much load. There are also much bigger systems to cope with cases that are definitely bigger than what you describe. You haven't described the nature of your application, so there is no particular reason to believe this hardware to either be adequate or inadequate... Something worth considering is that if you use replication (e.g. - something like Slony-I), you could quite likely live with having several cheaper boxes. Individually, they may not be as reliable as the pricier one, but in aggregate, you should, via techniques like moving master, keep higher uptimes than a single cheap box could support. -- cbbrowne,@,gmail.com http://cbbrowne.com/info/slony.html There is no psychiatrist in the world like a puppy licking your face. -- Ben Williams ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a web framework for postgresql?
We also use WebObjects with PostgreSQL and it's awesome as a web development framework. We have it deployed on Solaris and OS X Server. Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Mar 1, 2006, at 11:58 AM, Guido Neitzer wrote: On 01.03.2006, at 19:39 Uhr, falcon wrote: Any one know of such a framework? (I'm asking this in pgsql because such a framework will have to be fairly closely linked to a database...and I mainly use pgsql). Hmm. No. I don't think you can have this combined with what is often called business logic. A lot of stuff is described in the db model, but that's only one thing. What about process flows, permissions, login handling, session handling, design, performance optimization and so on? I use WebObjects (http://www.apple.com/webobjects) as my main development environment and I found that every application is unique in a good percentage of its features. For administration application the included DirectToWeb-Framwork in combination with a powerful community based framework (ProjectWonder) is a VERY powerful tool when it comes to rapid development. It saves me days and weeks of work - I just couldn't do my job anymore without it and developing applications in a standard way. But it has a very steep learning curve and is sometimes hard to handle and often frustration - until you learn how not to fight the tool. Frontend applications like online shops, portals and so on are then developed in a more conventional way with components and handwritten xhtml/css. If you have a Mac for development it's worth a look. Deployment can go anywhere (in theory - we have only used Linux and Mac OS X Server so far) as long as a Java runtime is installed. cug -- PharmaLine, Essen, GERMANY Software and Database Development smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Linux cluster application
On Mar 2, 2006, at 2:44 PM, Scott Marlowe wrote: On Thu, 2006-03-02 at 14:41, Andrew Watkins wrote: I'm writing a parallel/distributed application that assesses the performance impact of frequent insertions/selects to databases on parallel file systems in a linux cluster environment. Currently, the application calls for a database server to be running on each node of the cluster. Since my end-users may or may not have root access to the machines in the various clusters being used, I'm installing postgres from within the user's account to facilitate the starting and stopping of the postmaster without the need of root access. My general question is if anyone has experience with such a situation where the database server would need to be started on each machine in a parallel environment. If so, are there any suggestions for working with such a condition? You could set up ssh keys with no passphrase and use ssh to do it in a short shell script. Thanks. I suppose my question is less about the mechanisms for actually starting the servers and more about where to install the servers, where they should be running, etc. For example, if I'm using a shared file system across each node in the cluster and postgres has been installed in, say, /home/user/pgres, and initdb has initialized the database on, say, /home/user/pgres/data, then it would seem like there would end up being conflicts in file names when trying to launch a local server on each node. On the other hand, if there is disk space local to each node, then running the servers there would not allow for the assessing of the impact on a parallel file system. -- Andrew Watkins, PhD Department of Computer Science and Engineering Mississippi State University Box 9637, Mississippi State, MS, 39762 Office: (662) 325-7515 Fax: (662) 325-8997 http://www.cse.msstate.edu/~andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] extended index info
Hi all, I'm trying to work out which fields an index relates to. If I look at an index: \di+ news_pkey Schema | Name | Type | Owner | Description | Table +---+---+---+-+ public | news_pkey | index | chris | | news It doesn't show me which fields it actually applies to, only the table. I'm sure there is a way to include which fields (whether it comes back as an array or as multiple lines in the output I don't really care), but I don't know enough about the pg_* tables to know where to start :) I can see the create definition in pg_indexes but I'm after just the fields that it applies to. Any suggestions? Thanks! -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] extended index info
On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote: If I look at an index: \di+ news_pkey Schema | Name | Type | Owner | Description | Table +---+---+---+-+ public | news_pkey | index | chris | | news It doesn't show me which fields it actually applies to, only the table. \d news_pkey -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] extended index info
Michael Fuhr wrote: On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote: If I look at an index: \di+ news_pkey Schema | Name | Type | Owner | Description | Table +---+---+---+-+ public | news_pkey | index | chris | | news It doesn't show me which fields it actually applies to, only the table. \d news_pkey Derr. Thanks :) -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgres automatically inserts chr(13) whenever chr(10) is inserted
create table sample(column_sample varchar(500))
insert into sample(column_sample) values('this is first row of text' ||
chr(10) || 'this is second row of text')
Now, instead of just inserting chr(10), postgres inserts chr(13) +
chr(10). Is there a way to avoid this? Database is on a linux server
with SQL_ASCII encoding, clients are winXP communicating thru ODBC.
Tnx in advance
DRagan Matic
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
