Hi,

Ever since I began working with Postgres, I have had one little irritating
problem with psql.  It may be that I am mis-using this program; if so, my
suggestion is not helpful, however, if others have encountered this problem,
perhaps the developers can look at a fix for 7.0?

When I develop a new DB schema using psql, I usually first create a file, say
"mySchema.sql". I then "createdb" the database, start up psql, and use the
command "\i mySchema.sql" to load in my new schema. There will be, needless to
say, several errors.  These fall nicely below the offending line and I can look
at fixing them. I drop the DB, re-edit my SQL file and re-do the "\i" command.

Sometimes, however, rather than using the "\i" command, I would like to simply
load my schema directly into psql and capture the output on STDOUT (ie "psql <
mySchema.sql >&  myOutput").  The problem that arises is that the errors and
notices all come out on STDERR. I am not sure this is the right choice. Because
of the lack of synchronization between STDOUT and STDERR, it becomes impossible
to associate an SQL statement with either a CREATE or an ERROR message. The
option, "-e", is supposed to echo the query, but it doesn't help.

While I can see wanting to separate STDERR and STDOUT when one uses psql to run
an SQL query against a DB from within a shell script, it makes it much more
difficult when developing, and if I were to run several SQL queries into psql,
exactly the same association problem would occur.

Perhaps a combination of the function "isatty()" plus the -e flag would work? So
that if STDOUT "isatty()" then echo errors to STDOUT, otherwise send them to
STDERR. And if the -e flag is set, echo the queries to STDERR, so the
correlation between ERROR, CREATE, etc and SQL could be made.

Just my $0.02.

Mark

PS I only recently learned of the setting of the PAGER environment variable to
make it so I needn't scroll back up 400 lines to find my errors; perhaps this
could be made more prominent in the documentation as it would be a big help.
Then again, perhaps I should completely re-read the docs to see if this is
mentioned; I haven't done that for several releases now.

--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)




Wed, 08 Dec 1999 10:03:46 -0800
Mark Dalphin <mdalphin@amgen.com>
Suggested "minor" change to psql


	Somebody can help me?

	I need to know which is the maximum size of the database in
Postgresql and how many records I can keeps into it?

		Tnaks!

Sonia Sanchez Diaz
	UNAM_FCA_CIFCA_Admon.Red
	e-mail: sony@dec.contad.unam.mx



Wed, 8 Dec 1999 12:27:44 -0600 (CST)
Sanchez Diaz Sonia <sony@dec.contad.unam.mx>
Size of database
   When I develop a new DB schema using psql, I usually first create a file, say
   "mySchema.sql". I then "createdb" the database, start up psql, and use the
   command "\i mySchema.sql" to load in my new schema. There will be, needless to
   say, several errors.  These fall nicely below the offending line and I can look
   at fixing them. I drop the DB, re-edit my SQL file and re-do the "\i" command.

I think the new stuff allows separating or merging different output
"channels" so that psql can be run in the different ways you wish.

However, this does raise another issue that might make debugging
scripts run through psql easier.  I have found that emacs compile
buffer semantics are extremely useful for debugging source code, and
suggest that error messages from psql follow something similar (at
least as an option) to aid in script debugging.  The output of
compiler error messages generally gives a filename:linenumber prefix
to the message; emacs can parse that an put you exactly at the correct
point for fixing the error.

If psql would also output messages in the same form, i.e.,

   filename:linenumber: error message

then scripts run in emacs compile buffer (easily done either directly
or with make) could be rapidly debugged using the normal mechanisms
available for "normal" source code debugging.

I realize that everyone does not use emacs, but I can't see how
including that information would be detrimental to anyone.  It gives
more information useful for anyone debugging scripts.

Cheers,
Brook

Wed, 8 Dec 1999 11:54:26 -0700 (MST)
Brook Milligan <brook@biology.nmsu.edu>
Re: [GENERAL] Suggested "minor" change to psql
What's wrong with pgsql -d xxxx -c '\i myschema' > databaseload.logfile ?

Seems to work OK for me. 

You can always use the 2>&1 syntax to redirect STDERR to STDOUT as well.

Yours,
Moray







Wed, 8 Dec 1999 19:06:15 +0000 (GMT)
Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
Re: [GENERAL] Suggested "minor" change to psql
Look at:

http://www.postgresql.org/docs/faq-english.html#4.6

Daniel Stolk

Sanchez Diaz Sonia wrote:
> 
>         Somebody can help me?
> 
>         I need to know which is the maximum size of the database in
> Postgresql and how many records I can keeps into it?
> 
>                 Tnaks!
> 
> Sonia Sanchez Diaz
>         UNAM_FCA_CIFCA_Admon.Red
>         e-mail: sony@dec.contad.unam.mx
> 
> ************

Wed, 08 Dec 1999 11:22:54 -0800
Daniel Stolk <stolkd@email.com>
Re: [GENERAL] Size of database
Mark Dalphin wrote:

> Sometimes, however, rather than using the "\i" command, I would like to simply
> load my schema directly into psql and capture the output on STDOUT (ie "psql <
> mySchema.sql >&  myOutput").  The problem that arises is that the errors and
> notices all come out on STDERR. I am not sure this is the right choice. Because
> of the lack of synchronization between STDOUT and STDERR, it becomes impossible
> to associate an SQL statement with either a CREATE or an ERROR message. The
> option, "-e", is supposed to echo the query, but it doesn't help.

I have experienced this problem as well.  It is a bit of a pain.  I would love to
hear how others are handling this.  I have one partial workaround.

   % psql -d test -f createdb.sql 2>&1 | less

For whatever reason, the above seems to keep the msgs fairly synchronized (at least
on Redhat 6.0), making it useful for visual inspection of short loads.
Unfortunately, that approach far exceeds my patience for my situation.  I'm
frequently recreating 150 tables and redoing ~1400 INSERTs via psql with input
scripts.  That takes about 4 minutes on a dual PII 450 and generates ~15K lines of
output (~500 PAGER pages @30 lines/page).  Instead, I pipe STDERR/STDOUT to a file,
and then grep the file for 'INSERT 0 0', 'ERROR', and other problem signs.  I've
gotten pretty good at matching up the error msgs with the problem by interspersing
judiciously comments and queries, but it's still a pain.

It'd be nice to be able to get all psql msgs sync'ed on either STDERR or STDOUT.

Cheers.
Ed





Wed, 08 Dec 1999 13:25:14 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [GENERAL] Suggested "minor" change to psql
On 1999-12-08, Mark Dalphin mentioned:

> Sometimes, however, rather than using the "\i" command, I would like to simply
> load my schema directly into psql and capture the output on STDOUT (ie "psql <
> mySchema.sql >&  myOutput").  The problem that arises is that the errors and
> notices all come out on STDERR. I am not sure this is the right choice. Because
> of the lack of synchronization between STDOUT and STDERR, it becomes impossible
> to associate an SQL statement with either a CREATE or an ERROR message. The
> option, "-e", is supposed to echo the query, but it doesn't help.

You might be glad to hear that I've been addressing these issues. The way
it currently looks is that everything that is related to backend traffic
(query results, INSERT xxx, notices, errors) will all go to the same
stream (the \o one) in the order they arrive. I think this is what
everyone wanted. If you are running interactively, it doesn't make a
difference anyway, but in a automated script you'll rarely have the need
to have the errors without the commands that caused them.

The only thing that will keep going to stderr are fatal notices from psql
itself. The only thing that always goes to stdout is psql internal
messages ("Turned on expanded mode.").

One additional feature that's coming up, which you might like, is the
possibility to stop such a psql script after the first error it
encounters.

> While I can see wanting to separate STDERR and STDOUT when one uses psql to run
> an SQL query against a DB from within a shell script, it makes it much more
> difficult when developing, and if I were to run several SQL queries into psql,
> exactly the same association problem would occur.

You can check the return code and decide what to do with the output that
way.

> Perhaps a combination of the function "isatty()" plus the -e flag would work? So
> that if STDOUT "isatty()" then echo errors to STDOUT, otherwise send them to
> STDERR. And if the -e flag is set, echo the queries to STDERR, so the
> correlation between ERROR, CREATE, etc and SQL could be made.

There are already about 4 or 5 different output sources and 2 or 3 states
controlling them; I'm hesitant to adding more confusion, especially
subtle things.

Also, the meaning of the -e flag has been adjusted. In interactive mode it
doesn't do anything, in script mode it prints every line as it reads it.
If you don't give it, you don't see the code of your script. That is more
like a regular shell.

> PS I only recently learned of the setting of the PAGER environment variable to
> make it so I needn't scroll back up 400 lines to find my errors; perhaps this
> could be made more prominent in the documentation as it would be a big help.

That part has been changed, because the purpose of the PAGER environment
variable in general is not to toggle the use of the pager in psql. There
is now an internal switch.

> Then again, perhaps I should completely re-read the docs to see if this is
> mentioned; I haven't done that for several releases now.

Well, I rewrote the complete manual, so you're in for a great work of
literature. :)


When will you be able to reach this promised land? You could start by
flaming the hackers list about a 6.6 release in Feb/Mar ... ;)

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Fri, 10 Dec 1999 02:28:41 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [GENERAL] Suggested "minor" change to psql