Re: [GENERAL] Help with Database Recovery
"Hancock, David (DHANCOCK)" [EMAIL PROTECTED] writes: Sorry I didn't give more detail. OS is Linux 2.2 kernel, PostgreSQL is 6.5.3. The problem is that I copied the .../base/* directories elsewhere in preparation for making base a symlink to a different filesystem with more space. I then screwed up and removed everything in /var/lib/pgsql, not just the base directories. This necessitated a reinstall of PostgreSQL. I know, I know ... it was a very stupid maneuver on my part, but it's a strange feeling to know that I've GOT the database files, I just can't use 'em. Yet. Unfortunately, you've only got *part* of the database. The above maneuver destroyed your pg_log file, which is essential. Without it, you've got a lot of tuples but you don't know which ones are valid. If you did a VACUUM just before all this, then there's a reasonable chance that the tuples you have left are mostly just valid ones. Otherwise I'd say it's hopeless. In any case you will not be able to reconstruct data that you can trust except after painstaking manual examination. How far back was your last regular whole-file-system backup? Restoring all of /var/lib/pgsql off that is likely to be your best shot at getting to a state that's somewhat trustworthy. regards, tom lane
Re: [GENERAL] Help needed : temp tables.
"Vilson farias" [EMAIL PROTECTED] writes: I'm having problems with temporary tables in postgre. I've been using Postgre 7.02 in a red hat 6.2 and I'm accessing using Delphi 5 + Winzeos Postgre Components. I've been using for some mounths and now I checked that there are lots of temp tables in my system. These tables where created by my application and I think everytime it crashed the temp tables where not removed. Yeah, you're right :-(. 7.0 through 7.0.2 fail to do proper abort processing if the client application disconnects partway through a transaction. One of the effects is that temp tables aren't deleted. relatorio=# drop table pg_temp.10863.1; ERROR: parser: parse error at or near ".10863" You'd need to quote the name, eg drop table "pg_temp.10863.1"; since dot isn't considered a regular identifier character. And if I try vacuum verbose analyze, I get this message: NOTICE: --Relation pg_temp.10863.0-- NOTICE: mdopen: couldn't open pg_temp.10863.0: No such file or directory ERROR: cannot open relation pg_temp.10863.0 Odd. Did you try to manually remove the pg_temp files at some point? I think that 7.0.2 will let you do the drop anyway, but if not you might have to create dummy files before dropping, eg touch $PGDATA/base/relatorio/pg_temp.10863.1 and then drop the table. 7.0.3 will contain a fix for the failure-to-cleanup bug. regards, tom lane
Re: [GENERAL] Help on analyzing performance problem.
"Travis Bauer" [EMAIL PROTECTED] writes: I have a problem with a _very_ slow query. I'm not sure how to understand the explain output. There's some basic info about EXPLAIN in the "understanding performance" section of the user manual (at least today, it's at http://www.postgresql.org/docs/postgres/c4962.htm). The thing I usually try to do first is understand how the estimated row counts (rows=N) relate to reality. For this you need to know how large the tables are and how restrictive the WHERE clauses related to each table are, so that you can figure out the true output row count for each part of the query plan. The EXPLAIN output alone is not too useful without seeing both the query text and the context information about table sizes etc. regards, tom lane
Re: [GENERAL] help -- cursor inside a function
hello , i have a procedure as below. -- --- create function retrec(integer) returns integer as 'declare uid alias for $1; begin declare retrec_cur cursor for select * from jd_user_master where um_user_iduid; if found then fetch 1 from retrec_cur; move next from retrec_cur; return 1; end if; end;' language 'plpgsql'; -- --- this gets created , but while running it i get the error below, -- - NOTICE: plpgsql: ERROR during compile of retrec near line 5 ERROR: parse error at or near "cursor" -- why this is so ? can anyone help me out ? thanx in advance. You may use query loops, such as: DECLARE nextrow RECORD; BEGIN FOR nextrow IN SELECT * FROM table WHERE condition ORDER BY field LOOP ... END LOOP; END; See postgresql HTML documentation for further info. Alex Bolenok.
Re: [GENERAL] Help: How to store query output in a file !!!
Does anyone knows how do you send query output to a file??? Welcome to Unix(TM): psql database -c "select * from table name, etc." file name See also: http://www.isu.edu/departments/comcom/unix/workshop/io.html or, type "unix tutorial" in any web search engine. --Gene I tried following but it does not work: select * from table name \o file name ; Please let me know if anyone knows!! Thanks in advance Neeraj [EMAIL PROTECTED]
Re: [GENERAL] Help: How to store query output in a file !!!
From psql: test = \o ouput-file-name test = Enter your query here test = \o This will leave the results of your query in the file "output-file-name" This IS covered in the documentation and from within psql with \h len morgan -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: NEERAJ BANSAL [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Wednesday, June 14, 2000 2:46 AM Subject: Re: [GENERAL] Help: How to store query output in a file !!! Does anyone knows how do you send query output to a file??? Welcome to Unix(TM): psql database -c "select * from table name, etc." file name See also: http://www.isu.edu/departments/comcom/unix/workshop/io.html or, type "unix tutorial" in any web search engine. --Gene I tried following but it does not work: select * from table name \o file name ; Please let me know if anyone knows!! Thanks in advance Neeraj [EMAIL PROTECTED]
Re: [GENERAL] HELP needed : COPY datas from file
Hi! On Wed, 16 Jun 1999, Ming-Hung Eng wrote: I have a problem to copy datas from unix file, I get the following message when I try to import a file with more 20 lines : pgReadData() -- backend closed the channel unexpectedly This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. I use postgres 6.4.2 on solaris. There is a memory leak in 6.4.2. Split your data into smaller chunks... Thanks for your help! -- ENG Ming Hung Oleg. Oleg Broytmann http://members.xoom.com/phd2/ [EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN.
