Horton

Thread

  1. Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-01T11:04:41Z

    Hello,

I'm in the process of porting a large application from Ingres to
PostgreSQL. We make heavy use of bulkloading using the 'COPY'
statement in ESQL/C. Consider the SQL statements below (in a psql
session on an arbitrary database):

 CREATE TABLE copytest(f1 INTEGER, f2 INTEGER);
 CREATE UNIQUE INDEX copytest_idx ON copytest USING BTREE(f1, f2);
 COPY copytest FROM '/tmp/copytest';

Given the file /tmp/copytest:

 1	1
 2	2
 3	3
 4	4
 4	4
 5	5
 6	6

will result in the following output:

 ERROR:  copy: line 5, Cannot insert a duplicate key into unique index copytest_idx

However my application code is assuming that duplicate rows will
simply be ignored (this is the case in Ingres, and I believe Oracle's
bulkloader too). I propose modifying _bt_check_unique() in
/backend/access/nbtree/nbtinsert.c to emit a NOTICE (rather than
ERROR) elog() and return NULL (or appropriate) to the calling function
if a duplicate key is detected and a 'COPY FROM' is in progress (add
new parameter to flag this).

Would this seem a reasonable thing to do? Does anyone rely on COPY
FROM causing an ERROR on duplicate input? Would:

 WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)

need to be added to the COPY command (I hope not)?

Thanks,

-- 
 Lee Kindness, Senior Software Engineer
 Concept Systems Limited.

  2. Re: Bulkloading using COPY - ignore duplicates?

    Justin Clift <justin@postgresql.org> — 2001-10-01T11:40:41Z

    Lee Kindness wrote:
> 
<snip>
> 
>  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)

I would suggest :

WITH ON_DUPLICATE = IGNORE|TERMINATE

Or maybe IGNORE_DUPLICATE

purely for easier understanding, given there is no present standard nor
other databases' syntax to conform to.

:)

Regards and best wishes,

Justin Clift

> 
> need to be added to the COPY command (I hope not)?
> 
> Thanks,
> 
> --
>  Lee Kindness, Senior Software Engineer
>  Concept Systems Limited.

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

  3. Re: Bulkloading using COPY - ignore duplicates?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-01T13:36:36Z

    Lee Kindness <lkindness@csl.co.uk> writes:
> Would this seem a reasonable thing to do? Does anyone rely on COPY
> FROM causing an ERROR on duplicate input?

Yes.  This change will not be acceptable unless it's made an optional
(and not default, IMHO, though perhaps that's negotiable) feature of
COPY.

The implementation might be rather messy too.  I don't much care for the
notion of a routine as low-level as bt_check_unique knowing that the
context is or is not COPY.  We might have to do some restructuring.

> Would:
>  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
> need to be added to the COPY command (I hope not)?

It occurs to me that skip-the-insert might be a useful option for
INSERTs that detect a unique-key conflict, not only for COPY.  (Cf.
the regular discussions we see on whether to do INSERT first or
UPDATE first when the key might already exist.)  Maybe a SET variable
that applies to all forms of insertion would be appropriate.

			regards, tom lane

  4. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-01T13:40:07Z

    Justin Clift writes:
 > Lee Kindness wrote:
 > >  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
 > I would suggest :
 > WITH ON_DUPLICATE = IGNORE|TERMINATE
 > purely for easier understanding, given there is no present standard nor
 > other databases' syntax to conform to.

Personally I don't see the need, and think that 'COPY FROM' could well
just go with the new semantics...

Onto an implementation issue - _bt_check_unique() returns a
TransactionId, my plans were to return NullTransactionId on a
duplicate key but naturally this is used in the success
scenario. Looking in backend/transam/transam.c I see:

 TransactionId NullTransactionId = (TransactionId) 0;
 TransactionId AmiTransactionId = (TransactionId) 512;
 TransactionId FirstTransactionId = (TransactionId) 514;

From this I'd gather <514 can be used as magic-values/constants, So
would I be safe doing:

 TransactionId XXXXTransactionId = (TransactionId) 1;

and return XXXXTransactionId from _bt_check_unique() back to
_bt_do_insert()? Naturally XXXX is something meaningful. I presume all
I need to know is if 'xwait' in _bt_check_unique() is ever '1'...

Thanks,

--
 Lee Kindness, Senior Software Engineer
 Concept Systems Limited.

  5. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-01T13:54:25Z

    Tom Lane writes:
 > Lee Kindness <lkindness@csl.co.uk> writes:
 > > Would this seem a reasonable thing to do? Does anyone rely on COPY
 > > FROM causing an ERROR on duplicate input?
 > Yes.  This change will not be acceptable unless it's made an optional
 > (and not default, IMHO, though perhaps that's negotiable) feature of
 > COPY.

I see where you're coming from, but seriously what's the use/point of
COPY aborting and doing a rollback if one duplicate key is found? I
think it's quite reasonable to presume the input to COPY has had as
little processing done on it as possible. I could loop through the
input file before sending it to COPY but that's just wasting cycles
and effort - Postgres has btree lookup built in, I don't want to roll
my own before giving Postgres my input file!

 > The implementation might be rather messy too.  I don't much care
 > for the notion of a routine as low-level as bt_check_unique knowing
 > that the context is or is not COPY.  We might have to do some
 > restructuring.

Well in reality it wouldn't be "you're getting run from copy" but
rather "notice on duplicate, rather than error & exit". There is a
telling comment in nbtinsert.c just before _bt_check_unique() is
called:

	/*
	 * If we're not allowing duplicates, make sure the key isn't already
	 * in the index.  XXX this belongs somewhere else, likely
	 */

So perhaps dupes should be searched for before _bt_doinsert is called,
or somewhere more appropriate?

 > > Would:
 > >  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
 > > need to be added to the COPY command (I hope not)?
 > It occurs to me that skip-the-insert might be a useful option for
 > INSERTs that detect a unique-key conflict, not only for COPY.  (Cf.
 > the regular discussions we see on whether to do INSERT first or
 > UPDATE first when the key might already exist.)  Maybe a SET variable
 > that applies to all forms of insertion would be appropriate.

That makes quite a bit of sense.

-- 
 Lee Kindness, Senior Software Engineer
 Concept Systems Limited.

  6. Re: Bulkloading using COPY - ignore duplicates?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-01T14:02:54Z

    Lee Kindness <lkindness@csl.co.uk> writes:
> I see where you're coming from, but seriously what's the use/point of
> COPY aborting and doing a rollback if one duplicate key is found?

Error detection.  If I'm loading what I think is valid data, having the
system silently ignore certain types of errors is not acceptable ---
I'm especially not pleased at the notion of removing an error check
that's always been there because someone else thinks that would make it
more convenient for his application.

> I think it's quite reasonable to presume the input to COPY has had as
> little processing done on it as possible.

The primary and traditional use of COPY has always been to reload dumped
data.  That's why it doesn't do any fancy processing like DEFAULT
insertion, and that's why it should be quite strict about error
conditions.  In a reload scenario, any sort of problem deserves
careful investigation.

			regards, tom lane

  7. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-01T14:17:43Z

    Tom Lane writes:
 > I'm especially not pleased at the notion of removing an error check
 > that's always been there because someone else thinks that would make it
 > more convenient for his application.

Please, don't get me wrong - I don't want to come across arrogant. I'm
simply trying to improve the 'COPY FROM' command in a situation where
speed is a critical issue and the data is dirty... And that must be a
relatively common scenario in industry.

And I never said the duplicate should be silently ignored - an
elog(NOTICE) should still be output.

Lee.

  8. Re: Bulkloading using COPY - ignore duplicates?

    Thomas Swan <tswan@olemiss.edu> — 2001-10-01T14:21:22Z

    Lee Kindness wrote:

>Tom Lane writes:
> > Lee Kindness <lkindness@csl.co.uk> writes:
> > > Would this seem a reasonable thing to do? Does anyone rely on COPY
> > > FROM causing an ERROR on duplicate input?
> > Yes.  This change will not be acceptable unless it's made an optional
> > (and not default, IMHO, though perhaps that's negotiable) feature of
> > COPY.
>
>I see where you're coming from, but seriously what's the use/point of
>COPY aborting and doing a rollback if one duplicate key is found? I
>think it's quite reasonable to presume the input to COPY has had as
>little processing done on it as possible. I could loop through the
>input file before sending it to COPY but that's just wasting cycles
>and effort - Postgres has btree lookup built in, I don't want to roll
>my own before giving Postgres my input file!
>
> > The implementation might be rather messy too.  I don't much care
> > for the notion of a routine as low-level as bt_check_unique knowing
> > that the context is or is not COPY.  We might have to do some
> > restructuring.
>
>Well in reality it wouldn't be "you're getting run from copy" but
>rather "notice on duplicate, rather than error & exit". There is a
>telling comment in nbtinsert.c just before _bt_check_unique() is
>called:
>
>	/*
>	 * If we're not allowing duplicates, make sure the key isn't already
>	 * in the index.  XXX this belongs somewhere else, likely
>	 */
>
>So perhaps dupes should be searched for before _bt_doinsert is called,
>or somewhere more appropriate?
>
> > > Would:
> > >  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
> > > need to be added to the COPY command (I hope not)?
> > It occurs to me that skip-the-insert might be a useful option for
> > INSERTs that detect a unique-key conflict, not only for COPY.  (Cf.
> > the regular discussions we see on whether to do INSERT first or
> > UPDATE first when the key might already exist.)  Maybe a SET variable
> > that applies to all forms of insertion would be appropriate.
>
>That makes quite a bit of sense.
>
This is tring to avoid one step.

IMHO, you should copy into a temporary table and the do a select 
distinct from it into the table that you want.

A.  You can validate your data before you put it into your permanent table.
B.    This doesn't cost you much.

Don't make the assumption that bulk copies have not been checked or 
validated.  The assumption should be correct data or you shouldn't be 
using COPY.





>

  9. Re: Bulkloading using COPY - ignore duplicates?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-01T16:03:51Z

    > However my application code is assuming that duplicate rows will
> simply be ignored (this is the case in Ingres, and I believe Oracle's
> bulkloader too). I propose modifying _bt_check_unique() in
> /backend/access/nbtree/nbtinsert.c to emit a NOTICE (rather than
> ERROR) elog() and return NULL (or appropriate) to the calling function
> if a duplicate key is detected and a 'COPY FROM' is in progress (add
> new parameter to flag this).

If you have a UNIQUE index on the table, just throwing away duplicates
seems really bad to me.  I know Ingres had that heapsort structure that
would remove duplicates.  That may be an interesting feature to add as
an operation that can be performed.

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

  10. Re: Bulkloading using COPY - ignore duplicates?

    Peter Eisentraut <peter_e@gmx.net> — 2001-10-02T22:13:46Z

    Tom Lane writes:

> It occurs to me that skip-the-insert might be a useful option for
> INSERTs that detect a unique-key conflict, not only for COPY.  (Cf.
> the regular discussions we see on whether to do INSERT first or
> UPDATE first when the key might already exist.)  Maybe a SET variable
> that applies to all forms of insertion would be appropriate.

What we need is:

1. Make errors not abort the transaction.

2. Error codes

Then you can make your client deal with this in which ever way you want,
at least for single-value inserts.

However, it seems to me that COPY ignoring duplicates can easily be done
by preprocessing the input file.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  11. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-03T10:01:30Z

    Peter Eisentraut writes:
 > However, it seems to me that COPY ignoring duplicates can easily be
 > done by preprocessing the input file.

Or by post-processing, like (error checking cut):

 void import_shots(char *impfile, int lineshoot_id)
 {
  char tab_name[128];
  char tab_temp[128];

  frig_file(impfile); /* add the postgres header */
  sprintf(tab_name, "shot_%d", lineshoot_id);
  sprintf(tab_temp, "shot_%d_tmp", lineshoot_id);

  sprintf(cmd, "CREATE TEMPORARY TABLE %s AS SELECT * FROM shot",
	  tab_temp);
  EXEC SQL EXECUTE IMMEDIATE :cmd;
  EXEC SQL COMMIT WORK; /* will not work without comit here! */

  sprintf(cmd, "COPY BINARY %s FROM '%s'", tab_temp, impfile);
  append_page_alloc(cmd, tab_name, impfile, 1);
  EXEC SQL EXECUTE IMMEDIATE :cmd;
 
  sprintf(cmd, "INSERT INTO %s SELECT DISTINCT ON(shot_time) * FROM %s",
	  tab_name, tab_temp);
 
  EXEC SQL EXECUTE IMMEDIATE :cmd;

  sprintf(cmd, "DROP TABLE %s", tab_temp);
  EXEC SQL EXECUTE IMMEDIATE :cmd;

  EXEC SQL COMMIT WORK ;
  remove(impfile);
 }

However this is adding significant time to the import
operation. Likewise I could loop round the input file first and hunt
for duplicates, again with a performance hit.

My main point is that Postgres can easily and quickly check for
duplicates during the COPY (as it does currently) and it adds zero
execution time to simply ignore these duplicate rows. Obviously this
is a useful feature otherwise Oracle, Ingres and other commercial
relational databases wouldn't feature similiar functionality.

Yes, in an ideal world the input to COPY should be clean and
consistent with defined indexes. However this is only really the case
when COPY is used for database/table backup and restore. It misses the
point that a major use of COPY is in speed optimisation on bulk
inserts...

Lee.

  12. Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-10-08T12:41:27Z

    Guys,

I've made some inroads towards adding 'ignore duplicates'
functionality to PostgreSQL's COPY command. I've updated the parser
grammar for COPY FROM to now accept:

 COPY [ BINARY ] table [ WITH OIDS ]
    FROM { 'filename' | stdin }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH [NULL AS 'null string']
           [IGNORE DUPLICATES] ]

and added code to propagate this setting down to the CopyFrom function
in backend/commands/copy.c.

I also played around with _bt_check_unique, _bt_do_insert and btinsert
to return NULL on duplicate rather than elog(ERROR). Likewise
ExecInsertIndexTuples and index_insert were passed the
ignore_duplicate flag and index_insert changed to elog(ERROR) if the
return from the insert function was NULL and ignore_duplicate flag was
false.

These changes worked and gave the desired result for the COPY FROM
command, however as many mentioned these changes are far too low
level... After assessing the situation more fully, I believe the
following change in CopyFrom would be more suitable:

		/* BEFORE ROW INSERT Triggers */
		if (resultRelInfo->ri_TrigDesc &&
			resultRelInfo->ri_TrigDesc->n_before_row[TRIGGER_EVENT_INSERT] > 0)
		{
			HeapTuple	newtuple;
			newtuple = ExecBRInsertTriggers(estate, resultRelInfo, tuple);

			if (newtuple == NULL)		/* "do nothing" */
				skip_tuple = true;
			else if (newtuple != tuple) /* modified by Trigger(s) */
			{
				heap_freetuple(tuple);
				tuple = newtuple;
			}
		}

		/* new code */
		if( ignore_duplicates == true )
		{
				if( duplicate index value )
					skip_tuple = true;
		}

		if (!skip_tuple)
		{


Now I imagine 'duplicate index value' would be functionally similar to
_bt_check_unique but obviously higher level. Is there any existing
code with the functionality I desire? Can anyone point me in the right
way...

Thanks,

Lee Kindness.

Lee Kindness writes:
 > I'm in the process of porting a large application from Ingres to
 > PostgreSQL. We make heavy use of bulkloading using the 'COPY'
 > statement in ESQL/C. Consider the SQL statements below (in a psql
 > session on an arbitrary database):
 > 
 >  CREATE TABLE copytest(f1 INTEGER, f2 INTEGER);
 >  CREATE UNIQUE INDEX copytest_idx ON copytest USING BTREE(f1, f2);
 >  COPY copytest FROM '/tmp/copytest';
 > 
 > Given the file /tmp/copytest:
 > 
 >  1	1
 >  2	2
 >  3	3
 >  4	4
 >  4	4
 >  5	5
 >  6	6
 > 
 > will result in the following output:
 > 
 >  ERROR:  copy: line 5, Cannot insert a duplicate key into unique index copytest_idx
 > 
 > However my application code is assuming that duplicate rows will
 > simply be ignored (this is the case in Ingres, and I believe Oracle's
 > bulkloader too). I propose modifying _bt_check_unique() in
 > /backend/access/nbtree/nbtinsert.c to emit a NOTICE (rather than
 > ERROR) elog() and return NULL (or appropriate) to the calling function
 > if a duplicate key is detected and a 'COPY FROM' is in progress (add
 > new parameter to flag this).
 > 
 > Would this seem a reasonable thing to do? Does anyone rely on COPY
 > FROM causing an ERROR on duplicate input? Would:
 > 
 >  WITH ON_DUPLICATE = CONTINUE|TERMINATE (or similar)
 > 
 > need to be added to the COPY command (I hope not)?
 > 
 > Thanks,
 > 
 > -- 
 >  Lee Kindness, Senior Software Engineer
 >  Concept Systems Limited.

  13. Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-11T16:05:34Z

    Gents,

I started quite a long thread about this back in September. To
summarise I was proposing that COPY FROM would not abort the
transaction when it encountered data which would cause a uniqueness
violation on the table index(s).

Generally I think this was seen as a 'Good Thing'TM for a number of
reasons:

 1. Performance enhancements when doing doing bulk inserts - pre or
post processing the data to remove duplicates is very time
consuming. Likewise the best tool should always be used for the job at
and, and for searching/removing things it's a database.

2. Feature parity with other database systems. For example Oracle's
SQLOADER has a feature to not insert duplicates and rather move
them to another file for later investigation.

Naturally the default behaviour would be the current one of assuming
valid data. Also the duplicate check would not add anything to the
current code path for COPY FROM - it would not take any longer.

I attempted to add this functionality to PostgreSQL myself but got as
far as an updated parser and a COPY FROM which resulted in a database
recovery!

So (here's the question finally) is it worthwhile adding this
enhancement to the TODO list?

Thanks, Lee.

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

  14. Re: Bulkloading using COPY - ignore duplicates?

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-12-13T12:31:14Z

    On Mon, Oct 01, 2001 at 03:17:43PM +0100, Lee Kindness wrote:
> Tom Lane writes:
>  > I'm especially not pleased at the notion of removing an error check
>  > that's always been there because someone else thinks that would make it
>  > more convenient for his application.
> 
> Please, don't get me wrong - I don't want to come across arrogant. I'm
> simply trying to improve the 'COPY FROM' command in a situation where
> speed is a critical issue and the data is dirty... And that must be a
> relatively common scenario in industry.

Isn't that when you do your bulk copy into into a holding table, then
clean it up, and then insert into your live system?

Patrick

  15. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-13T13:25:11Z

    Patrick Welche writes:
 > On Mon, Oct 01, 2001 at 03:17:43PM +0100, Lee Kindness wrote:
 > > Please, don't get me wrong - I don't want to come across arrogant. I'm
 > > simply trying to improve the 'COPY FROM' command in a situation where
 > > speed is a critical issue and the data is dirty... And that must be a
 > > relatively common scenario.
 > Isn't that when you do your bulk copy into into a holding table, then
 > clean it up, and then insert into your live system?

That's what I'm currently doing as a workaround - a SELECT DISTINCT
from a temporary table into the real table with the unique index on
it. However this takes absolute ages - say 5 seconds for the copy
(which is the ballpark figure I aiming toward and can achieve with
Ingres) plus another 30ish seconds for the SELECT DISTINCT.

The majority of database systems out there handle this situation in
one manner or another (MySQL ignores or replaces; Ingres ignores;
Oracle ignores or logs; others...). Indeed PostgreSQL currently checks
for duplicates in the COPY code but throws an elog(ERROR) rather than
ignoring the row, or passing the error back up the call chain.

My use of PostgreSQL is very time critical, and sadly this issue alone
may force an evaluation of Oracle's performance in this respect!

Best regards, Lee Kindness.

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

  16. Re: Bulkloading using COPY - ignore duplicates?

    Hannu Krosing <hannu@tm.ee> — 2001-12-13T13:59:33Z

    Lee Kindness wrote:
> 
> Patrick Welche writes:
>  > On Mon, Oct 01, 2001 at 03:17:43PM +0100, Lee Kindness wrote:
>  > > Please, don't get me wrong - I don't want to come across arrogant. I'm
>  > > simply trying to improve the 'COPY FROM' command in a situation where
>  > > speed is a critical issue and the data is dirty... And that must be a
>  > > relatively common scenario.
>  > Isn't that when you do your bulk copy into into a holding table, then
>  > clean it up, and then insert into your live system?
> 
> That's what I'm currently doing as a workaround - a SELECT DISTINCT
> from a temporary table into the real table with the unique index on
> it. However this takes absolute ages - say 5 seconds for the copy
> (which is the ballpark figure I aiming toward and can achieve with
> Ingres) plus another 30ish seconds for the SELECT DISTINCT.
> 
> The majority of database systems out there handle this situation in
> one manner or another (MySQL ignores or replaces; Ingres ignores;
> Oracle ignores or logs; others...). Indeed PostgreSQL currently checks
> for duplicates in the COPY code but throws an elog(ERROR) rather than
> ignoring the row, or passing the error back up the call chain.

I guess postgresql will be able to do it once savepoints get
implemented.

> My use of PostgreSQL is very time critical, and sadly this issue alone
> may force an evaluation of Oracle's performance in this respect!

Can't you clean the duplicates _outside_ postgresql, say

cat dumpfile | sort | uniq | psql db -c 'copy mytable from stdin'

with your version of uniq.

or perhaps

psql db -c 'copy mytable to stdout' >> dumpfile
sort dumpfile | uniq | psql db -c 'copy mytable from stdin'

if you already have something in mytable.

------------
Hannu

  17. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-13T14:56:52Z

    Hannu Krosing writes:
 > Lee Kindness wrote:
 > > The majority of database systems out there handle this situation in
 > > one manner or another (MySQL ignores or replaces; Ingres ignores;
 > > Oracle ignores or logs; others...). Indeed PostgreSQL currently checks
 > > for duplicates in the COPY code but throws an elog(ERROR) rather than
 > > ignoring the row, or passing the error back up the call chain.
 > I guess postgresql will be able to do it once savepoints get
 > implemented.

This is encouraging to hear. I can see how this would make the code
changes relatively minimal and more manageable - the changes to the
current code are simply over my head!

Are savepoints relatively high up on the TODO list, once 7.2 is out the
door?

 > > My use of PostgreSQL is very time critical, and sadly this issue alone
 > > may force an evaluation of Oracle's performance in this respect!
 > Can't you clean the duplicates _outside_ postgresql, say
 > cat dumpfile | sort | uniq | psql db -c 'copy mytable from stdin'

This is certainly a possibility, however it's just really moving the
processing elsewhere. The combined time is still around the same.

I've/we've done a lot of investigation with approaches like this and
also with techniques assuming the locality of the duplicates (which is
a no-goer). None improve the situation.

I'm not going to compare the time of just using INSERTs rather than
COPY...

Thanks for your response, Lee Kindness.

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

  18. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-13T15:00:53Z

    Lee Kindness writes:
 > I'm not going to compare the time of just using INSERTs rather than
 > COPY...

Ooops, I'm NOW going to... Obviously my subconscious is telling me
otherwise - bring on the Christmas party!

Lee.

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

  19. Re: Bulkloading using COPY - ignore duplicates?

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-12-13T15:29:57Z

    On Thu, Dec 13, 2001 at 01:25:11PM +0000, Lee Kindness wrote:
> That's what I'm currently doing as a workaround - a SELECT DISTINCT
> from a temporary table into the real table with the unique index on
> it. However this takes absolute ages - say 5 seconds for the copy
> (which is the ballpark figure I aiming toward and can achieve with
> Ingres) plus another 30ish seconds for the SELECT DISTINCT.

Then your column really isn't unique, so how about dropping the unique index,
import the data, fix the duplicates, recreate the unique index - just as
another possible work around ;)

Patrick

  20. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-13T15:44:31Z

    Patrick Welche writes:
 > On Thu, Dec 13, 2001 at 01:25:11PM +0000, Lee Kindness wrote:
 > > That's what I'm currently doing as a workaround - a SELECT DISTINCT
 > > from a temporary table into the real table with the unique index on
 > > it. However this takes absolute ages - say 5 seconds for the copy
 > > (which is the ballpark figure I aiming toward and can achieve with
 > > Ingres) plus another 30ish seconds for the SELECT DISTINCT.
 > Then your column really isn't unique,

That's another discussion entirely ;) - it's spat out by a real-time
system which doesn't have the time or resources to check this. Further
precision loss later in the data's life adds more duplicates...

 > so how about dropping the unique index, import the data, fix the
 > duplicates, recreate the unique index - just as another possible
 > work around ;) 

This is just going to be the same(ish) time, no?

 CREATE TABLE tab (p1 INT, p2 INT, other1 INT, other2 INT);
 COPY tab FROM 'file';
 DELETE FROM tab WHERE p1, p2 NOT IN (SELECT DISTINCT p1, p2
                                      FROM tab);
 CREATE UNIQUE INDEX tab_idx ON tab USING BTREE(p1, p2);

or am I missing something?

Thanks, Lee.

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

  21. Re: Bulkloading using COPY - ignore duplicates?

    Ross Reedstrom <reedstrm@rice.edu> — 2001-12-13T16:22:59Z

    On Thu, Dec 13, 2001 at 03:44:31PM +0000, Lee Kindness wrote:
> Patrick Welche writes:
>  > On Thu, Dec 13, 2001 at 01:25:11PM +0000, Lee Kindness wrote:
>  > > That's what I'm currently doing as a workaround - a SELECT DISTINCT
>  > > from a temporary table into the real table with the unique index on
>  > > it. However this takes absolute ages - say 5 seconds for the copy
>  > > (which is the ballpark figure I aiming toward and can achieve with
>  > > Ingres) plus another 30ish seconds for the SELECT DISTINCT.
>  > Then your column really isn't unique,
> 
> That's another discussion entirely ;) - it's spat out by a real-time
> system which doesn't have the time or resources to check this. Further
> precision loss later in the data's life adds more duplicates...

Hmm, the data has a later life - sounds like you'll need to remove dups
then, anyway, so can you get away with just letting the dups in? Remove
the UNIQUE requirement, and let the real time system just dump away.
How critical is it to later steps that there be no dups? And how many
(potential) dups is your RTS producing, anyway?

Your later processing (which apparently can _generate_ dups) might be
the out of the critical time path place to worry about removing dups.

Ross

P.S. This falls into the class of problem solving characterized by
"if you can't solve the problem as stated, restate the problem to be
one you _can_ solve" ;-)

> 
>  > so how about dropping the unique index, import the data, fix the
>  > duplicates, recreate the unique index - just as another possible
>  > work around ;) 
> 
> This is just going to be the same(ish) time, no?
> 
>  CREATE TABLE tab (p1 INT, p2 INT, other1 INT, other2 INT);
>  COPY tab FROM 'file';
>  DELETE FROM tab WHERE p1, p2 NOT IN (SELECT DISTINCT p1, p2
>                                       FROM tab);
>  CREATE UNIQUE INDEX tab_idx ON tab USING BTREE(p1, p2);
> 
> or am I missing something?
> 
> Thanks, Lee.
> 
> -- 
>  Lee Kindness, Senior Software Engineer, Concept Systems Limited.
>  http://services.csl.co.uk/ http://www.csl.co.uk/ +44 131 5575595
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

  22. Re: Bulkloading using COPY - ignore duplicates?

    Peter Eisentraut <peter_e@gmx.net> — 2001-12-13T18:20:18Z

    Lee Kindness writes:

>  1. Performance enhancements when doing doing bulk inserts - pre or
> post processing the data to remove duplicates is very time
> consuming. Likewise the best tool should always be used for the job at
> and, and for searching/removing things it's a database.

Arguably, a better tool for this is sort(1).  For instance, if you have a
typical copy input file with tab-separated fields and the primary key is
in columns 1 and 2, you can remove duplicates with

sort -k 1,2 -u INFILE > OUTFILE

To get a record of what duplicates were removed, use diff.

-- 
Peter Eisentraut   peter_e@gmx.net

  23. Re: Bulkloading using COPY - ignore duplicates?

    Peter Eisentraut <peter_e@gmx.net> — 2001-12-13T18:20:59Z

    Lee Kindness writes:

> Yes, in an ideal world the input to COPY should be clean and
> consistent with defined indexes. However this is only really the case
> when COPY is used for database/table backup and restore. It misses the
> point that a major use of COPY is in speed optimisation on bulk
> inserts...

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.

-- 
Peter Eisentraut   peter_e@gmx.net

  24. Re: Bulkloading using COPY - ignore duplicates?

    Ross Reedstrom <reedstrm@rice.edu> — 2001-12-13T18:36:27Z

    O.K., time to start looking into the _nature_ of the dups in your
data, to see if there's anything specific to take advantage of, since
the general solution (tell the DBMS to ignore dups) isn't available,
and isn't likely to get there real soon. 

So what does your data look like, and how do the dups occur?

Any chance it's in a really simple format, and the dups are also really
simple, like 'one record per line, dups occur as identical adjacent
lines?' if so, 'uniq' will solve the problem with little to no speed
penalty. (it's the sort that kills ...)

Or are you only gettinga dup'ed field,m and the rule 'ignore later
records?' I could see this happen if the dta is timestamped at a 
granularity that doesn't _exactly_ match the repetition rate: e.g.
stamp to the second, record once a second.

So, what's it look like? Since it's one format, I bet a small, simple
pipe filter could handle dup elimination on the fly.

Ross

On Thu, Dec 13, 2001 at 05:02:15PM +0000, Lee Kindness wrote:
> 
> The RTS outputs to a file which is then subsequently used as input to
> other packages, one of which is the application i'm concerned
> with. While fixing at source is the ideal solution there are terabytes
> of legacy data around (this is raw seismic navigational data). Also
> there are more than one competing packages...
> 
> Our package post-processes (we're still very concerned about speed as
> this is normally done while 'shooting' the seismic data) this data to
> produce the final seismic navigational data, which is then later used
> by other products...
> 
> The problem at hand is importing the initial data - no duplicates are
> produced by the program itself later (nor in its output data).
> 
> Sadly a large number of later SQL queries assume no duplicates and
> would result in incorrect processing calculations, amongst other
> things. The shear number of these queries makes changing them
> impractical.
> 
>  > P.S. This falls into the class of problem solving characterized by
>  > "if you can't solve the problem as stated, restate the problem to be
>  > one you _can_ solve" ;-)
> 
> Which is what i've been knocking my head against for the last few
> weeks ;) The real problem is a move away from our current RDMS
> (Ingres) to PostgreSQL will not happen if the performance of the
> product significantly decreases (which it currently has for the import
> stage) and since Ingres already just ignores the duplicates...
> 
> I really want to move to PostgreSQL...
> 
> Thanks for your input,
> 
> -- 
>  Lee Kindness, Senior Software Engineer, Concept Systems Limited.
>  http://services.csl.co.uk/ http://www.csl.co.uk/ +44 131 5575595

  25. Re: Bulkloading using COPY - ignore duplicates?

    Lee Kindness <lkindness@csl.co.uk> — 2001-12-14T11:30:17Z

    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.