Re: Import: I need help

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