[ADMIN] Dealing with a cursor in libpq c program

Поиск
Список
Период
Сортировка
От John Scalia
Тема [ADMIN] Dealing with a cursor in libpq c program
Дата
Msg-id CABzCKRDPfhQa9bDFrU6a4vQqK5qfq2mVzNNUdtzwaCxaS_uM6g@mail.gmail.com
обсуждение исходный текст
Ответы Re: [ADMIN] Dealing with a cursor in libpq c program
Список pgsql-admin
Дерево обсуждения
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.

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.

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.

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));

       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, "");
           }
       }

Allow me to note that I don't find any examples on the web doing anything other than a fetch all rows, which I'd rather not do. I know that the FETCH FORWARD should probably have an INTO <some record> clause, but I cannot figure out either how to declare the proper record type nor how to get this command syntactically correct. Can anyone point me the right direction or is what I'm attempting even possible?
--
Jay

В списке pgsql-admin по дате отправления: