Thread: Questions about pg_dump file
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. Second question... I have noticed that a pg_dump file keeps the text formatting, ie, tabs, spaces, etc. for any user-defined functions. The function looks like it should, and is readable, in the pg_dump file, with all of it's formatting. A view, however, ends up all on the same line. This is fine for simple views, but for more complicated view definitions it can be difficult to read. Is there a way to get postgres/pg_dump to keep it's text formatting (tabs, spaces, etc) for the view definitions like it does for functions? Or is this even feasable? Thanks for the help JB
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. > Second question... I have noticed that a pg_dump file keeps the text > formatting, ie, tabs, spaces, etc. for any user-defined functions. > The function looks like it should, and is readable, in the pg_dump > file, with all of it's formatting. A view, however, ends up all on > the same line. This is fine for simple views, but for more > complicated view definitions it can be difficult to read. Is there a > way to get postgres/pg_dump to keep it's text formatting (tabs, > spaces, etc) for the view definitions like it does for functions? Or > is this even feasable? This is improved in 8.0beta1. -- 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
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.? 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..
John Browne <jkbrowne@gmail.com> writes:
> 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.?
No, I don't think so. The job of pg_dump is to reproduce what you have,
not to do cleanup for you.
regards, tom lane
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