Skip site navigation (1) Skip section navigation (2)

Re: Import: I need help

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: Quan Zongliang <quanzongliang(at)gmail(dot)com>
Cc: pgadmin-hackers(at)postgresql(dot)org(dot)
Subject: Re: Import: I need help
Date: 2009-02-13 09:00:34
Message-ID: 499536B2.7060100@lelarge.info (view raw or flat)
Thread:
Lists: pgadmin-hackers
Hi,

Quan Zongliang a écrit :
> [...]
> I haven't any experience with libpq. So there are a lot of questions.
> 
> When import to db using INSERT statement. Like this:
>    res = PQexec(m_conn->connection(), "BEGIN");
>    LOOP: res = PQexec(m_conn->connection(), "INSERT INTO ...");
>    res = PQexec(m_conn->connection(), "COMMIT");
> If any INSERT statement failed in the middle, the whole transaction is aborted.
> In my plan, the end user can select a number of rows to commit their work.
> (Option: commit every xxx rows.)
> Is there a approach to ingnore error statement and leave current transaction
> in normal status?

No. If you have an error, the whole transaction is dead. You can deal
with this in two ways. The first one involves doing multiple
COMMIT;BEGIN; statements:

res = PQexec(m_conn->connection(), "BEGIN");
LOOP:
  res = PQexec(m_conn->connection(), "INSERT INTO ...");
  if more than x INSERTs
    res = PQexec(m_conn->connection(), "COMMIT");
    res = PQexec(m_conn->connection(), "BEGIN");
ENDLOOP
res = PQexec(m_conn->connection(), "COMMIT");

The second way is to use SAVEPOINT.

> [...]
> First, import function with INSERT statement will be implemented.
> The questions about COPY command will come soon. ^-^
> 

I think you should take a look at pgloader:
  http://pgfoundry.org/projects/pgloader/

It's written in Python but Dimitri had to deal with the same issues than
you. pgloader is able to understand which INSERT fails, and can put them
in a file. It can also use COPY statements.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

In response to

Responses

pgadmin-hackers by date

Next:From: Quan ZongliangDate: 2009-02-13 09:06:51
Subject: Re: [pgadmin-support] about bit varying
Previous:From: Dave PageDate: 2009-02-13 08:55:00
Subject: Re: Import: I need help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group