Re: Bulkloading using COPY - ignore duplicates?

I agree with Lee, I also like Oracle's options for a discard file, so
you can look at what was rejected, fix your problem and reload if
necessary just the rejects.

Jim

> Peter Eisentraut writes:
> > I think allowing this feature would open up a world of new
> > dangerous ideas, such as ignoring check contraints or foreign keys
> > or magically massaging other tables so that the foreign keys are
> > satisfied, or ignoring default values, or whatever. The next step
> > would then be allowing the same optimizations in INSERT. I feel
> > COPY should load the data and that's it. If you don't like the
> > data you have then you have to fix it first.
>
> I agree that PostgreSQL's checks during COPY are a bonus and I
> wouldn't dream of not having them. Many database systems provide a
> fast bulkload by ignoring these constraits and cross references -
> that's a tricky/horrid situation.
>
> However I suppose the question is should such 'invalid data' abort the
> transaction, it seems a bit drastic...
>
> I suppose i'm not really after a IGNORE DUPLICATES option, but rather
> a CONTINUE ON ERROR kind of thing.
>
> Regards, Lee.
>
>

Jim Buttafuoco writes:

> I agree with Lee, I also like Oracle's options for a discard file, so
> you can look at what was rejected, fix your problem and reload if
> necessary just the rejects.

How do you know which one is the duplicate and which one is the good one?
More likely you will have to fix the entire thing. Anything else would
undermine the general data model except in specific use cases.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut writes:
> Jim Buttafuoco writes:
> > I agree with Lee, I also like Oracle's options for a discard file, so
> > you can look at what was rejected, fix your problem and reload if
> > necessary just the rejects.
> How do you know which one is the duplicate and which one is the good one?
> More likely you will have to fix the entire thing. Anything else would
> undermine the general data model except in specific use cases.

In the general case most data is sequential, in which case it would be
normal to assume that the first record is the definitive one. Most
database systems go with this assumption apart from MySQL which gives
the user a choice between IGNORE or UPDATE...

Lee.

Peter Eisentraut writes:
> Jim Buttafuoco writes:
> > I agree with Lee, I also like Oracle's options for a discard file, so
> > you can look at what was rejected, fix your problem and reload if
> > necessary just the rejects.
> How do you know which one is the duplicate and which one is the good one?
> More likely you will have to fix the entire thing. Anything else would
> undermine the general data model except in specific use cases.

Consider SELECT DISTINCT - which is the 'duplicate' and which one is
the good one?

Lee.

Lee Kindness writes:

> Consider SELECT DISTINCT - which is the 'duplicate' and which one is
> the good one?

It's not the same thing. SELECT DISTINCT only eliminates rows that are
completely the same, not only equal in their unique contraints.

Maybe you're thinking of SELECT DISTINCT ON (). Observe the big warning
that the result of that statement are random unless ORDER BY is used. --
But that's not the same thing either. We've never claimed that the COPY
input has an ordering assumption. In fact you're asking for a bit more
than an ordering assumption, you're saying that the earlier data is better
than the later data. I think in a random use case that is more likely
*not* to be the case because the data at the end is newer.

Btw., here's another concern about this proposed feature: If I do a
client-side COPY, how will you sent the "ignored" rows back to the client?

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut writes:
> Lee Kindness writes:
> > Consider SELECT DISTINCT - which is the 'duplicate' and which one is
> > the good one?
> It's not the same thing. SELECT DISTINCT only eliminates rows that are
> completely the same, not only equal in their unique contraints.
> Maybe you're thinking of SELECT DISTINCT ON (). Observe the big warning
> that the result of that statement are random unless ORDER BY is used. --
> But that's not the same thing either. We've never claimed that the COPY
> input has an ordering assumption. In fact you're asking for a bit more
> than an ordering assumption, you're saying that the earlier data is better
> than the later data. I think in a random use case that is more likely
> *not* to be the case because the data at the end is newer.

You're right - I was meaning 'SELECT DISTINCT ON ()'. However I'm only
using it as an example of where the database is choosing (be it
randomly) the data to discarded. While I've said in this thread that
'COPY FROM IGNORE DUPLICATES' would ignore later duplicates I'm not
really that concerned about what it ignores; first, later, random,
... I agree if it was of concern then it should be pre-processed.

> Btw., here's another concern about this proposed feature: If I do
> a client-side COPY, how will you sent the "ignored" rows back to
> the client?

Again a number of different ideas have been mixed up in the
discussion. Oracle's logging option was only given as an example of
how other database systems deal with this option - If it wasn't
explicitly given then it's reasonable to discard the extra
information.

What really would be nice in the SQL-world is a standardised COPY
statement...

Best regards, Lee Kindness.

Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> You're right - I was meaning 'SELECT DISTINCT ON ()'. However I'm only
> using it as an example of where the database is choosing (be it
> randomly) the data to discarded.

Not a good example to support your argument. The entire point of
DISTINCT ON (imho) is that the rows that are kept or discarded are
*not* random, but can be selected by the user by specifying additional
sort columns. DISTINCT ON would be pretty useless if it weren't for
that flexibility. The corresponding concept in COPY will need to
provide flexible means for deciding which row to keep and which to
drop, else it'll be pretty useless.

regards, tom lane

Tom Lane writes:
> Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> > You're right - I was meaning 'SELECT DISTINCT ON ()'. However I'm only
> > using it as an example of where the database is choosing (be it
> > randomly) the data to discarded.
> Not a good example to support your argument. The entire point of
> DISTINCT ON (imho) is that the rows that are kept or discarded are
> *not* random, but can be selected by the user by specifying additional
> sort columns. DISTINCT ON would be pretty useless if it weren't for
> that flexibility. The corresponding concept in COPY will need to
> provide flexible means for deciding which row to keep and which to
> drop, else it'll be pretty useless.

At which point it becomes quicker to resort to INSERT...

Here's the crux question - how can I get management to go with
PostgreSQL when a core operation (import of data into a transient
database) is at least 6 times slower than the current version?

With a lot of work investigating the incoming data, the number of
incoming duplicates has been massively reduced by fixing/tackling at
source. However rouge values do still crop up (the data originates
from a real-time system with multiple hardware inputs from multiple
hardware vendors) and when they do (even just 1) the performance dies.
Add to this terrabytes of legacy data...

While you may see the option of ignoring duplicates in COPY as 'pretty
useless', it obviously has its place/use otherwise every other
database system wouldn't have support for it! (not that following the
pack is always a good idea)

In an ideal world 'COPY FROM' would only be used with data output by
'COPY TO' and it would be nice and sanitised. However in some fields
this often is not a possibility due to performance constraints!

Best regards,

--
Lee Kindness, Senior Software Engineer, Concept Systems Limited.
http://services.csl.co.uk/ http://www.csl.co.uk/

Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> In an ideal world 'COPY FROM' would only be used with data output by
> 'COPY TO' and it would be nice and sanitised. However in some fields
> this often is not a possibility due to performance constraints!

Of course, the more bells and whistles we add to COPY, the slower it
will get, which rather defeats the purpose no?

regards, tom lane

Tom Lane writes:
> Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> > In an ideal world 'COPY FROM' would only be used with data output by
> > 'COPY TO' and it would be nice and sanitised. However in some fields
> > this often is not a possibility due to performance constraints!
> Of course, the more bells and whistles we add to COPY, the slower it
> will get, which rather defeats the purpose no?

Indeed, but as I've mentioned in this thread in the past, the code
path for COPY FROM already does a check against the unique index (if
there is one) but bombs-out rather than handling it...

It wouldn't add any execution time if there were no duplicates in the
input!

regards, Lee.

Lee Kindness wrote:
> Tom Lane writes:
> > Lee Kindness <lkindness(at)csl(dot)co(dot)uk> writes:
> > > In an ideal world 'COPY FROM' would only be used with data output by
> > > 'COPY TO' and it would be nice and sanitised. However in some fields
> > > this often is not a possibility due to performance constraints!
> > Of course, the more bells and whistles we add to COPY, the slower it
> > will get, which rather defeats the purpose no?
>
> Indeed, but as I've mentioned in this thread in the past, the code
> path for COPY FROM already does a check against the unique index (if
> there is one) but bombs-out rather than handling it...
>
> It wouldn't add any execution time if there were no duplicates in the
> input!

I know many purists object to allowing COPY to discard invalid rows in
COPY input, but it seems we have lots of requests for this feature, with
few workarounds except pre-processing the flat file. Of course, if they
use INSERT, they will get errors that they can just ignore. I don't see
how allowing errors in COPY is any more illegal, except that COPY is one
command while multiple INSERTs are separate commands.

Seems we need to allow such a capability, if only crudely. I don't
think we can create a discard file because of the problem with remote
COPY.

I think we can allow something like:

COPY FROM '/tmp/x' WITH ERRORS 2

meaning we will allow at most two errors and will report the error line
numbers to the user. I think this syntax clearly indicates that errors
are being accepted in the input. An alternate syntax would allow an
unlimited number of errors:

COPY FROM '/tmp/x' WITH ERRORS

The errors can be non-unique errors, or even CHECK constraint errors.

Unless I hear complaints, I will add it to TODO.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I think we can allow something like:
> COPY FROM '/tmp/x' WITH ERRORS 2

This is not going to happen, at least not until after there's a
wholesale revision of error handling. As things stand we do not
have a choice: elog(ERROR) must abort the transaction, because we
can't guarantee that things are in good enough shape to continue.
See the archives for previous discussions.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I think we can allow something like:
> > COPY FROM '/tmp/x' WITH ERRORS 2
>
> This is not going to happen, at least not until after there's a
> wholesale revision of error handling. As things stand we do not
> have a choice: elog(ERROR) must abort the transaction, because we
> can't guarantee that things are in good enough shape to continue.
> See the archives for previous discussions.

Yes, I realize we need subtransactions or something, but we should add
it to the TODO list if it is a valid request, right?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I think we can allow something like:
> COPY FROM '/tmp/x' WITH ERRORS 2

> Yes, I realize we need subtransactions or something, but we should add
> it to the TODO list if it is a valid request, right?

Well, I don't like that particular API in any case. Why would I think
that 2 errors are okay and 3 are not, if I'm loading a
many-thousand-line COPY file? Wouldn't it matter *what* the errors
are, at least as much as how many there are? "Discard duplicate rows"
is one thing, but "ignore bogus data" (eg, unrecognizable timestamps)
is not the same animal at all.

As someone already remarked, the correct, useful form of such a feature
is to echo the rejected lines to some sort of output file that I can
look at afterwards. How many errors there are is not the issue.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I think we can allow something like:
> > COPY FROM '/tmp/x' WITH ERRORS 2
>
> > Yes, I realize we need subtransactions or something, but we should add
> > it to the TODO list if it is a valid request, right?
>
> Well, I don't like that particular API in any case. Why would I think
> that 2 errors are okay and 3 are not, if I'm loading a
> many-thousand-line COPY file? Wouldn't it matter *what* the errors

I threw the count idea in as a possible compromise. :-)

> are, at least as much as how many there are? "Discard duplicate rows"
> is one thing, but "ignore bogus data" (eg, unrecognizable timestamps)
> is not the same animal at all.

Yes, when we have error codes, it would be nice to specify certain
errors to ignore.

> As someone already remarked, the correct, useful form of such a feature
> is to echo the rejected lines to some sort of output file that I can
> look at afterwards. How many errors there are is not the issue.

How about for TODO:

* Allow COPY to report error lines and continue; requires
nested transactions; optionally allow error codes to be specified

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> How about for TODO:
> * Allow COPY to report error lines and continue; requires
> nested transactions; optionally allow error codes to be specified

Okay, that seems reasonable.

regards, tom lane

Tom Lane wrote:
> > How about for TODO:
> > * Allow COPY to report error lines and continue; requires
> > nested transactions; optionally allow error codes to be specified
>
> Okay, that seems reasonable.

Good. Now that I think of it, nested transactions don't seem required.
We already allow pg_dump to dump a database using INSERTs, and we don't
put those inserts in a single transaction when we load them:

CREATE TABLE "test" (
"x" integer
);

INSERT INTO "test" VALUES (1);
INSERT INTO "test" VALUES (2);

Should we be wrapping these INSERTs in a transaction? Can we do COPY
with each row being its own transaction?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:
> Tom Lane wrote:
> > > How about for TODO:
> > > * Allow COPY to report error lines and continue; requires
> > > nested transactions; optionally allow error codes to be specified
> >
> > Okay, that seems reasonable.
>
> Good. Now that I think of it, nested transactions don't seem required.
> We already allow pg_dump to dump a database using INSERTs, and we don't
> put those inserts in a single transaction when we load them:
>
> CREATE TABLE "test" (
> "x" integer
> );
>
> INSERT INTO "test" VALUES (1);
> INSERT INTO "test" VALUES (2);
>
> Should we be wrapping these INSERTs in a transaction? Can we do COPY
> with each row being its own transaction?

OK, added to TODO:

o Allow COPY to report error lines and continue; optionally
allow error codes to be specified

Seems nested transactions are not required if we load each COPY line in
its own transaction, like we do with INSERT from pg_dump.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Seems nested transactions are not required if we load each COPY line in
> its own transaction, like we do with INSERT from pg_dump.

I don't think that's an acceptable answer. Consider

BEGIN;
other stuff;
COPY ....;
other stuff;
ROLLBACK;

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Seems nested transactions are not required if we load each COPY line in
> > its own transaction, like we do with INSERT from pg_dump.
>
> I don't think that's an acceptable answer. Consider

Oh, very good point. "Requires nested transactions" added to TODO.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026