Thread: problem with CR+LF in files in psql \i command

problem with CR+LF in files in psql \i command

From
"Luiz K. Matsumura"
Date:
Hi,
I need to backup my database with pg_dump and recover it in other
windows machine.
I recover my database running the pg_dump generated script in psql with
\i command
At each time I do this operation ,  misteriously the space between lines
multiplies in my functions and comments.

Well, I discover that when I use \i <file> command in psql with a file
generated in windows format ( CR+LF terminated lines) psql actually
sends the CR character at each line break to postgres, causing this effect.

I make a change in input.c that resolve this problem, but I'm not a
expert in c, therefore if someone have a better solution,
please do the necessary changes.

Thanks in advance.


*** input.c.ori    2005-03-09 21:05:36.000000000 -0300
--- input.c    2005-03-09 22:40:32.000000000 -0300
***************
*** 142,147 ****
--- 142,152 ----
          if (buffer.data[buffer.len - 1] == '\n')
          {
              buffer.data[buffer.len - 1] = '\0';
+             /* in case of CR + LF  */
+             if (buffer.data[buffer.len - 2] == '\r')
+             {
+                 buffer.data[buffer.len - 2] = '\0';
+             }
              return buffer.data;
          }
      }

Re: problem with CR+LF in files in psql \i command

From
Bruce Momjian
Date:
The \i file is opened in binary mode because we don't want control-Z to
mark end-of-file.  We have to do this because bytea allows a stream of
binary data and control-Z might be in there.  The parser knows if the
control-Z is in quotes or not, but the file I/O routines to not and this
would cause a problem.  There is also the problem if literal carriage
returns in COPY, though that shouldn't happen.

What is happening in your function and comments is that the original
line endings are preserved in the dump file and are recreated on the
Unix side.  The good news is that it shouldn't affect the operation of
the database, but I can see how it would be annoying.

Function and comments are just _strings_ to PostgreSQL, so we have no
good way of cleaning the output up, unless we hack pg_dump to somehow
change line endings when outputting such information, though currently we
don't.  The would perhaps cause problems in viewing the functions if
restored in Win32.

Fixing it at the \i level is too crude because it might remove carriage
returns that are wanted in the input stream.

We are open to ideas on how to improve this.

---------------------------------------------------------------------------

Luiz K. Matsumura wrote:
> Hi,
> I need to backup my database with pg_dump and recover it in other
> windows machine.
> I recover my database running the pg_dump generated script in psql with
> \i command
> At each time I do this operation ,  misteriously the space between lines
> multiplies in my functions and comments.
>
> Well, I discover that when I use \i <file> command in psql with a file
> generated in windows format ( CR+LF terminated lines) psql actually
> sends the CR character at each line break to postgres, causing this effect.
>
> I make a change in input.c that resolve this problem, but I'm not a
> expert in c, therefore if someone have a better solution,
> please do the necessary changes.
>
> Thanks in advance.
>
>

> *** input.c.ori    2005-03-09 21:05:36.000000000 -0300
> --- input.c    2005-03-09 22:40:32.000000000 -0300
> ***************
> *** 142,147 ****
> --- 142,152 ----
>           if (buffer.data[buffer.len - 1] == '\n')
>           {
>               buffer.data[buffer.len - 1] = '\0';
> +             /* in case of CR + LF  */
> +             if (buffer.data[buffer.len - 2] == '\r')
> +             {
> +                 buffer.data[buffer.len - 2] = '\0';
> +             }
>               return buffer.data;
>           }
>       }

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

Re: problem with CR+LF in files in psql \i command

From
Euler Taveira de Oliveira
Date:
Hi Bruce,

> Function and comments are just _strings_ to PostgreSQL, so we have no
> good way of cleaning the output up, unless we hack pg_dump to somehow
> change line endings when outputting such information, though
> currently we
> don't.  The would perhaps cause problems in viewing the functions if
> restored in Win32.
>
Agreed.

> Fixing it at the \i level is too crude because it might remove
> carriage
> returns that are wanted in the input stream.
>
Agreed.

> We are open to ideas on how to improve this.
>
What about another option to pg_dump to indicate such a thing? Maybe -l
or --change-line [m|w|u]

Another TODO item?


Euler Taveira de Oliveira
euler[at]yahoo_com_br

__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/