Thread: Suggested "minor" change to psql
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)
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
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
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 > > ************
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
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