Обсуждение: [ADMIN] Dealing with a cursor in libpq c program
Hi all,
This posting is really two parts and here's part 1. As a test, I've been building text indexes on some datasets from our data warehouse. I've built both trigram and gin indexes. My two largest sets threw warnings while the gin index was being built of "Cannot index word. Words greater than 2047 characters cannot be indexed." Overall, this is not a very helpful message as the two sets contain more than 25 million rows each. It would have really nice to get a row number or a sample of the errant word with the warning message.res = PQexec(conn, "FETCH FORWARD 500 FROM note_text_cursor");
printf("res = %d\n", PQresultStatus(res));
while (PQresultStatus(res) == PGRES_TUPLES_OK)
{
printf("Got a PGRES_TUPLES_OK from the FETCH FORWARD command, parsing each row\n");
for (int i = 0; i < PQntuples(res); ++i)
{
char line[4000];
strcpy(line, PQgetvalue(res, i, 0));
printf("retrieved line: %s\n", line);
strcpy(line, "");
}
}
--
John Scalia wrote: > This posting is really two parts and here's part 1. As a test, I've been building text indexes on some > datasets from our data warehouse. I've built both trigram and gin indexes. My two largest sets threw > warnings while the gin index was being built of "Cannot index word. Words greater than 2047 characters > cannot be indexed." Overall, this is not a very helpful message as the two sets contain more than 25 > million rows each. It would have really nice to get a row number or a sample of the errant word with > the warning message. > > > Part 2 is I'm trying to write a C program to actually find the bad data, and figured that I would use > a cursor to traverse the table.field and break the field into individual words and test for length. I > would then output any word over 2000 character long. I cannot find any references however, for using a > cursor where I only want to process a certain number of rows at a time, say 500. I'm just trying to > minimize memory as my first attempt at this was an unsuccessful python attempt. I'm testing each > operation in the c program and see that BEGIN was successful and the cursor declaration was also > successful. It would be helpful to know the DECLARE statement... > Not sure if I need to call an OPEN CURSOR command as no examples I found actually do that and my > attempt at opening it returns failure. No big deal as I just let the code continue anyway. You don't OPEN cursors declare with DECLARE. OPEN is PL/pgSQL only. Before you write a C program for that, try to run your DECLARE and FETCH statement from psql. That should give you the same error message with less efort. > My question is the next step where I want to fetch the next 50 rows, and it's currently failing with > res = 7. Here's the code fragment which is in a search loop: > > res = PQexec(conn, "FETCH FORWARD 500 FROM note_text_cursor"); > printf("res = %d\n", PQresultStatus(res)); 7 is PGRES_FATAL_ERROR. You should call PQerrorMessage(conn) to get the error message. Yours, Laurenz Albe