yeah, this is all a bit confusing, but I *hope* I can clarify things here as I think I got a better understanding now.

before that, let me just clarify that the performance improvements in the patch have nothing to do with the escaping mechanizm. Escapes could change. The performance gain in due to a buffered processing with minimal line/attribute buffer loads.

I think that the basic issue is that there are some database users that would like to take their data and put it into the database without pre-processing it - regardless if there are any backslashes in it or 0x0D (CR's) etc... these are the users I am targeting in my patch as these are the users I ran into in the field. The only responsibility of these users is to explicitly escape any delimiter or 0x0A (LF) characters that they intend to have as DATA. that's all.

On the other hand there are users that would like to pre-process their data with C-escape sequences (or alternatevly, users that already have their data escaped) - this is what the postgres COPY targets these days.

2 different ways to do it... none of them is right or wrong.

Examples:

users that my patch targets may have a data row as such (delim = '|', EOL = [LF]):

c:\one\two|d:\ten\nine[LF]

using the way i do escaping in my patch those 2 fields of data will end up in the DB as
Field 1: c:\one\two
Field 2: d:\ten\nine

which is what the user would want. If they wanted to have a pipe char in the second field they could escape it as such: d:\ten\nine here is a pipe \| [LF] and no error will occur, and result will be:

Field 2: d:\ten\nine here is a pipe |

If you try to insert that first data line above using the existing COPY command you will get an undesired result:

Field 1: cne wo
Field 2: d:
ine


Now, the other way around, users that do intend for their data to have escape sequences in it may have a line like this:

that's a \t tab| and this is a \nline feed [LF]

and will get the desired result of:

Field 1: that's a tab
Field 2: and this is a
line feed

while using my code they will get undesired results:
Field 1: that's a \t tab
Field 2: and this is a \nline feed


so, basically it really depends on the target audience...

Bruce, does that sounds right to you?

Alon.





-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Steve Atkins
Sent: Wed 6/1/2005 10:47 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] NOLOGGING option, or ?

On Wed, Jun 01, 2005 at 07:35:33PM -0700, Luke Lonergan wrote:
> >> I propose an extended syntax to COPY with a change in semantics to remove
> >> the default of "WITH ESCAPE '\'".
> >
> > Er, doesn't this break existing database dumps?
>
> Yes, one of the previously stated reasons to create another command for
> loading data.
>
> Another possible approach is to keep the default, but allow the escape
> processing to be turned off.

I've been following this thread, and I'm a little confused. Could you
possibly clarify what you mean, by providing a couple of lines of
input as it would be formatted with escape processing turned off -
containing a text field with an embedded newline and tab and a null field.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alon Goldshuv wrote:

> I think that the basic issue is that there are some database users that would like to take their data and put it into the database without pre-processing it [...]
> The only responsibility of these users is to explicitly escape any delimiter or 0x0A (LF) characters that they intend to have as DATA. that's all.

Haven't you just replaced one preprocessing step with another, then?

-O

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Oliver,

> Haven't you just replaced one preprocessing step with another, then?

Generally not. The most common problem with the current choice of escape
character is that there are *lots* of data load scenarios with backslash in
the text strings. The extra preprocessing to escape them is unnecessary on
other databases and, in effect, causes the load to be even slower because
you have to prepare the data ahead of time.

Also, note that this patch can also do escape processing and the net result
will still be 5+ times faster than what is there.

In the data warehousing industry, data conversion and manipulation is
normally kept distinct from data loading. Conversion is done by tools
called ETL (Extract Transform Load) and the database will have a very fast
path for direct loading of the resulting data. PostgreSQL is definitely a
strange database right now in that there is a default filter applied to the
data on load.

It's even more strange because the load path is so slow, and now that we've
found that the slowness is there mostly because of non-optimized parsing and
attribute conversion routines. The question of how to do escape processing
is a separate one, but is wrapped up in the question of whether to introduce
a new loading routine or whether to optimize the old one.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Jun 02, 2005 at 07:33:13AM -0700, Luke Lonergan wrote:
> Oliver,
>
> > Haven't you just replaced one preprocessing step with another, then?
>
> Generally not. The most common problem with the current choice of escape
> character is that there are *lots* of data load scenarios with backslash in
> the text strings.

I can only think of one where it's common. Windows filenames. But if
you're going to support arbitrary data in a load then whatever escape
character you choose will appear sometimes.

> The extra preprocessing to escape them is unnecessary on
> other databases and, in effect, causes the load to be even slower because
> you have to prepare the data ahead of time.

> Also, note that this patch can also do escape processing and the net result
> will still be 5+ times faster than what is there.

I strongly suspect that a patch to improve performance without changing
behaviour would be accepted with no questions asked.

One that allowed specifying the field and record delimiters and the
escape character and null symbol might require more discussion about
an appropriate syntax at the very least. So you may want to separate
the two.

> In the data warehousing industry, data conversion and manipulation is
> normally kept distinct from data loading. Conversion is done by tools
> called ETL (Extract Transform Load) and the database will have a very fast
> path for direct loading of the resulting data. PostgreSQL is definitely a
> strange database right now in that there is a default filter applied to the
> data on load.

> It's even more strange because the load path is so slow, and now that we've
> found that the slowness is there mostly because of non-optimized parsing and
> attribute conversion routines. The question of how to do escape processing
> is a separate one, but is wrapped up in the question of whether to introduce
> a new loading routine or whether to optimize the old one.

There are already two loader routines. One of them is text-based and is
designed for easy generation of data load format using simple text
manipulation tools by using delimiters. It also allows (unlike your
suggestion) for loading of arbitrary data from a text file.

Because it allows for arbitrary data and uses delimiters to separate
fields it has to use an escaping mechanism.

If you want to be able to load arbitrary data and not have to handle
escape characters there's are two obvious ways to do it.

The first is that used by MIME and suggested by you. That is to use a
separator that you believe will not appear in the data. That can be
done by using a long multicharacter separator containing random
characters and assuming that sequence won't appear, it can be done by
parsing the input data twice, looking for strings that don't appear
for use as delimiters or it can take advantage of knowledge about
what characters can and can not appear in the input data. (I can't
imagine any case involving data-mining of web logs where the last
is likely to be relevant).

The other is to use length+data format for each tuple, avoiding all
issues of escapes in the data and allowing arbitrary data to be
represented. That's how the binary load format PG supports works, I
believe. If you're really concerned about speed of load that may be a
better format for your front-end to generate, perhaps?

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Steve,

> I can only think of one where it's common. Windows filenames.

Nearly all weblog data then.

> But if
> you're going to support arbitrary data in a load then whatever escape
> character you choose will appear sometimes.

If we allow an 8-bit character set in the "text" file, then yes, any
delimiter you choose has the potential to appear in your input data. In
practice, with *mostly* 7-bit ASCII characters and even with international
8-bit text encodings, you can choose a delimiter and newline that work well.
Exceptions are handled by the forthcoming single row error handling patch.

> I strongly suspect that a patch to improve performance without changing
> behaviour would be accepted with no questions asked.

Understood - not sure it's the best thing for support of the users yet.
We've found a large number of issues from customers with the unmodified
behavior.

> There are already two loader routines. One of them is text-based and is
> designed for easy generation of data load format using simple text
> manipulation tools by using delimiters. It also allows (unlike your
> suggestion) for loading of arbitrary data from a text file.

Not to distract, but try loading a binary null into a text field. The
assumption of null terminated strings penetrates deep into the codebase.
The existing system does not allow for loading arbitrary data from a text
file.

Our suggestion allows for escapes, but requires the ability to specify
alternate characters or none.

> Because it allows for arbitrary data and uses delimiters to separate
> fields it has to use an escaping mechanism.
>
> If you want to be able to load arbitrary data and not have to handle
> escape characters there's are two obvious ways to do it.

Let's dispense with the notion that we're suggesting no escapes (see above).

Binary with a bookends format is a fine idea and would be my personal
preference if it were fast, which it isn't. Customers in the web log
analysis and other data warehousing fields prefer "mostly 7-bit" ascii text
input, which we're trying to support with this change.

- Luke



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

"Luke Lonergan" <llonergan@greenplum.com> writes:

> In the data warehousing industry, data conversion and manipulation is
> normally kept distinct from data loading.

It's a bit strange to call this conversion or manipulation. One way or another
you have to escape whatever your delimiters are. How would you propose loading
strings that contain newlines?

The ETL transformations you're talking about are a different beast entirely.
You're talking about things like canonicalizing case or looking up foreign key
ids to replace strings and such.

Simply parsing the file format properly isn't part of that game. Otherwise
where do you stop? You could take this to a silly extreme and just say
postgres should just load each line as a record with single text field and let
"tools" deal with actually parsing. Or better yet, load the whole thing as a
single big blob.

Personally I would prefer to make prepared inserts as efficient as COPY and
deprecate COPY. Then we could have an entirely client-side tool that handled
as many formats as people want to implement without complicating the server.
Things like various vintages of Excel, fixed column files, etc should all be
handled as plugins for such a tool.

That would have the side benefit of allowing people to do other batch jobs
efficiently. Pipelining parameters to hundreds of executions of a prepared
query in the network.

Actually it seems like there's no particular reason the NOLOGGING option Tom
described (where it only inserts on new pages, doesn't have any special WAL
entries, just fsyncs at the end instead of WAL logging) can't work with
arbitrary inserts. Somehow some state has to be preserved remembering which
pages the nologging inserts have created and hold locks on.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend