Thread

1. Suggested "minor" change to psql

   Mark Dalphin <mdalphin@amgen.com> — 1999-12-08T18:03:46Z

   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)
   
   
   
   
   

2. Size of database

   Sanchez Diaz Sonia <sony@dec.contad.unam.mx> — 1999-12-08T18:27:44Z

   
   	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
   
   
   
   

3. Re: [GENERAL] Suggested "minor" change to psql

   Brook Milligan <brook@biology.nmsu.edu> — 1999-12-08T18:54:26Z

      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
   
   

4. Re: [GENERAL] Suggested "minor" change to psql

   Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk> — 1999-12-08T19:06:15Z

   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
   
   
   
   
   
   
   
   

5. Re: [GENERAL] Size of database

   Daniel Stolk <stolkd@email.com> — 1999-12-08T19:22:54Z

   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
   > 
   > ************
   
   

6. Re: [GENERAL] Suggested "minor" change to psql

   Ed Loehr <eloehr@austin.rr.com> — 1999-12-08T19:25:14Z

   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
   
   
   
   
   
   

7. Re: [GENERAL] Suggested "minor" change to psql

   Peter Eisentraut <peter_e@gmx.net> — 1999-12-10T01:28:41Z

   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