pq: unexpected Parse response 'D'

[rothskeller]

You can probably close this issue immediately; it was user error on my part. But I searched your issues list looking for help debugging it and couldn't find any, so I'm adding this issue for future people searching for it.

In my case, this wonderfully cryptic message resulted from trying to start a new statement before reading all of the rows of the preceding statement. Should the future searcher get this error, that would be the first thing to look for.

[maddyblue]

This is a bug. Which commit were you running? We just added something similar to this in the most recent commit (https://github.com/lib/pq/commit/dd1fe2071026ce53f36a39112e645b4d4f5793a4).

[rothskeller]

I'm running 8837942.

[rothskeller]

What I was trying to do, by the way, was read the rows of a Query and, while processing each one, insert a corresponding row to a different table; all of this within the same transaction. Of course I should have realized that's not legal. It's too bad, though; I really don't want to cache all of the data in memory, because there's a lot of it. But I can't think of a better answer.

[tamird]

@rothskeller you can probably do what you're after using cursors: https://www.postgresql.org/docs/current/static/plpgsql-cursors.html

[rothskeller]

Bless you, @tamird, that's exactly what I need. Learn something new every day....

[hypnoglow]

Example on using cursors for your case (with some random data):

if _, err := tx.Exec(`
    DECLARE orders_cursor CURSOR FOR
    SELECT id
    FROM order
    WHERE client_id = $1
    ORDER BY createdAt
    FOR UPDATE
    `,
    client.ID,
); err != nil {
    return err
}
defer tx.Exec("CLOSE orders_cursor")

var id int64

for {
    if err := tx.QueryRow(
        `FETCH NEXT FROM orders_cursor`,
    ).Scan(&id); err != nil {
        if err == sql.ErrNoRows {
            // End of rows.
            break
        }

        return err
    }

    if _, err := tx.Exec(`
        UPDATE order
        SET amount = amount - 1
        WHERE CURRENT OF orders_cursor
        `,
    ); err != nil {
        return err
    }
}

return nil

[timbunce]

Thanks for the example code @hypnoglow.

What I was trying to do, by the way, was read the rows of a Query and, while processing each one, insert a corresponding row to a different table; all of this within the same transaction. Of course I should have realized that's not legal.

It works in the official libpq (and so all drivers that build on it or emulate it closely) because libpq defaults to fetching and buffering all the rows in memory. It's arguably a cheat but a very effectively one that's very widely used.

See also #81.

[hsblhsn]

Is there anyone alive to fix it? @bartekn can you please have a deeper look into this?

[johto]

Is there anyone alive to fix it? @bartekn can you please have a deeper look into this?

What's there to fix?

[hsblhsn]

What's there to fix?

I want to run 5 concurrent select query using a single transaction using gorm. But these errors like driver: bad connection, pq: unexpected Parse response 'C', no rows returned keeps happening.

libraries like github.com/jackc/pgx is working without any issue. But lib/pq keeps failing.

Can we please safely handle this without confusing with memory?

[maddyblue]

lib/pq isn't well maintained and I'm not sure anyone here will fix this problem. You'll probably need to either write that PR yourself, or use pgx (I recommend using pgx).

[andreypanin]

> I'm adding this issue for future people searching for it.

Future people thank you!

[woswos]

still not fixed, can confirm

[dovydas55]

still not fixed, can confirm

[kmorake]

still not fixed, can confirm