Re: Questions about pg_dump file - Mailing list pgsql-novice

John Browne wrote:
> On Wed, 25 Aug 2004 23:23:23 -0400 (EDT), Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
> > John Browne wrote:
> > > I had a-couple of questions regarding the text formatting in the pg_dump file.
> > >
> > > First, when dumping my database, I seem to be getting inconsistent
> > > end-of-line characters.  Some lines end with the *NIX-style \n and
> > > some end with Windows-style \r\n.  Now, I have created this database
> > > over about a-year or so and I have tested various clients (psql,
> > > pgadminIII, etc) during that time.  I'm suspecting they are coming
> > > from one of the Windows clients, but shouldn't the \r\n end-of-line
> > > characters be converted to \n when a dump is created?  Is this the
> > > expected behavior?  I know the output of pg_dump could be piped
> > > through perl or sed or something to strip/convert them.  I just didn't
> > > know if there was an easier way.
> >
> > We should handle \n or \r\n just fine, though there is a fix in 7.4.5
> > for that.
>
> Oh, pg_dump handles it just fine, but the problem is you end up with a
> mixture of different EOL characters in the dump file.  Shouldn't
> pg_dump create the dump file with consistent \n *NIX style EOL
> characters throughout the entire file, instead of carrying over the
> \r\n EOL characters from user-defined function definitions, views,
> etc.?

Yes, I see your point  If I create a function like this:

    test=> CREATE FUNCTION test () returns int4 AS 'SELECT 1;\r\nSELECT 2;'
    LANGUAGE 'SQL';

the dump has:

    --
    -- Name: test(); Type: FUNCTION; Schema: public; Owner: postgres
    --

    CREATE FUNCTION test() RETURNS integer
        AS $$SELECT 1;
    SELECT 2;$$
        LANGUAGE sql;

where there is \r after 'SELECT 1'.  While COPY is consistent in
escaping \r or \n that appear as data, it does not do anything to modify
strings used by function bodies.  This happens because it is a string
and we just report its exact value.  This does bring up the issue that
someone converting pg_dump output from one EOL format to another would
modify the function string.   I can't see a big downside to that,
though.

> The only reason I stumbled across the fact there were \r\n characters
> in the dump file is because I started versioning my postgres schema in
> my Subversion repository. :-)  Just thought it was odd the \r\n
> characters were there..

You suggested we add an option to COPY to output in various OS-specific
EOL formats.  Seems we would have to support MSWIN, Unix, and old-Mac.
That seems too much.  I think we are better off outputing in a native
format and allow external tools to change the EOL format.


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073