on line numbers, drop table errors, and log files

From: Jon Lapham <jlapham(at)gandalf(dot)bioqmed(dot)ufrj(dot)br>
To: pgsql-sql(at)postgresql(dot)org
Subject: on line numbers, drop table errors, and log files
Date: 2000-08-01 13:56:13
Message-ID: 20000801105613.A14959@gandalf.bioqmed.ufrj.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello-

A few quick questions, I'm interested to know how people solve these
issues. Thanks!

(BTW: Pg 7.0.2)

1) Is there a way to find discover the line number of a script that causes
an error? I mean, if I "\i script.sql" a 2000 line script, it is often
difficult to track down that it was line #1211 that has a problem. What I
have been doing recently (don't laugh) is to iteratively chop the script
up into smaller pieces until I find the offensive command.

2) Is there a way to write a script such that it only drops a table IF it
exists? Currently, I just place a "drop table blah" in front of a "create
table blah" and live with the error message. I've noticed that some
people make a "drop table" script and a "create table" script, is this the
generally best way to do things? The problems arise in that sometimes I
do not know (because things are automated) if the table exists yet or not.

3) Is it possible to have psql make a log file of a session? This would
solve problem #1 above, because one could look in the log file to find the
first error message from the large script.

4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump main_v0_6" in
order to create a backup of my "main_v0_6" database. In order to
regenerate the database, I first run a "\i create_tables.sql" script, then
I run "\i backup.dump". However, I end up having to hand edit the
"backup.dump" file to place "drop sequence" statements before the "create
sequence" statements because they are created by my
"create_tables.sql" script. I'm sure you Postgres gurus out there are not
doing it this way, what is your method of database backup/recovery?

TIA, Jon

--

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: jlapham(at)gandalf(dot)bioqmed(dot)ufrj(dot)br
***-*--*----*-------*------------*--------------------*---------------

Browse pgsql-sql by date

  From Date Subject
Next Message Roberto Mello 2000-08-01 17:43:10 PL/Tcl
Previous Message Christopher Sawtell 2000-08-01 08:47:48 Re: New book on Postgres