loader question

accessor · Post by **accessor** » Fri 27 Jul 2007 07:06

Hello,

I am loading a lot of records in my database at once from a text-file (244000 records).

But how to handele if one or more records conflicts with the primary key?

I like to skip those record, but the loader just drops all the records i try to add.

This way i will only work if the database is emty to start with.

Any suggestions how to solve this?

Thanks

Martijn Pijpers

Alexey · Post by **Alexey** » Fri 27 Jul 2007 08:35

Loader's concept provides you only one thing - the speed of loading.
You should take care of data validity yourself, because any additional constraint checking at our side will decelerate loader greatly.

accessor · Post by **accessor** » Mon 30 Jul 2007 08:34

Thank you for your reply,

Now i am trying to trap any error.
In order to do that i read the documentation, but i don't understand how to catch the errors with the following:

Public Delegate Sub PgSqlLoaderErrorEventHandler(ByVal sender As Object, ByVal e As PgSqlLoaderErrorEventArgs)

and

Public Event Error() As PgSqlLoaderErrorEventHandler

when i know the error then i want to decide to use e.ignore or not.

Please can you show me how to set up this error-hanling.

ps. i tried to use a try - catch routine but then i do not have the ignore property and many times the errors are not shown.

thank you in advance,

Martijn

Alexey · Post by **Alexey** » Mon 30 Jul 2007 09:59

Please take a look at our Loader demo project. It quite clearly shows how to go.

accessor · Post by **accessor** » Mon 30 Jul 2007 11:51

Again thanks for your quick reply.

in the loaderdemo i only see :

Private Sub PgSqlLoader_Error(ByVal sender As System.Object, ByVal e As CoreLab.PostgreSql.PgSqlLoaderErrorEventArgs)
e.Ignore = True
End Sub

copying this does not work in my app.
after reading the documentation i think i need the
Public Delegate Sub PgSqlLoaderErrorEventHandler(ByVal sender As Object, ByVal e As PgSqlLoaderErrorEventArgs)

and

Public Event Error() As PgSqlLoaderErrorEventHandler

but i connot see how in the loader-demo.
Just to be clear about what i want:

I need the loader to insert many records 244000.
But when a primary key violation happens just ignore the error and go on with the next row.

To do that i need the e.ignore = true. (i Think).

So please tell if it is possible and how to do it.

Because inserting all those records with sql-statements will result in an out of memory errror from the Postgressql-server when reaching about 133000 records.

if you can solve the out of memory problem it is ok for me.

thank you in advance...

Martijn Pijpers

Alexey · Post by **Alexey** » Mon 30 Jul 2007 12:56

You should add PgSqlLoader_Error handler to the error. You can do this using Properties window or just make PgSqlLoader_Error procedure read:

Code: Select all

         Private Sub PgSqlLoader_Error(ByVal sender As System.Object, ByVal e As CoreLab.PostgreSql.PgSqlLoaderErrorEventArgs) Handles PgSqlLoader.Error
            e.Ignore = True
        End Sub

Regarding this:

Because inserting all those records with sql-statements will result in an out of memory errror from the Postgressql-server when reaching about 133000 records.
if you can solve the out of memory problem it is ok for me.

I don't think we can fix this. This is server problem. You need to upgrade your hardware I suppose.

craig · Post by **craig** » Tue 01 Apr 2008 23:09

This is actually a separate loader question:

Does the loader use the PostgreSQL COPY command? In the documentation it mentions that its LoadTable() is the recommended method for bulk loading. There's nothing faster, right?

Thanks,
-Craig

Alexey.mdr · Post by **Alexey.mdr** » Wed 02 Apr 2008 09:34

Yes, it does use PostgreSQL COPY command.
Yes, it's considered the fastest method.