Executing any SHOW query gives rowcount to -1

[khushboovashi]

After executing "SHOW data_directory", I was expecting the result of cursor.rowcount would be 1 but instead it gives -1. So, I can't identify whether the result is empty or not.

[dvarrazzo]

That's really curious, you are right...

>>> cur.execute("select 1")
<psycopg.Cursor [TUPLES_OK] [INTRANS] (database=piro) at 0x7f99ad344100>
>>>  cur.rowcount
1

>>> cur.execute("show timezone")
<psycopg.Cursor [TUPLES_OK] [INTRANS] (database=piro) at 0x7f99ad344100>
>>> cur.rowcount
-1

For psycopg, the two queries are not treated differently, they follow exactly the same code path.

The difference comes from the libpq function PQcmdTuples(), which normally does the right thing, but it seems it doesn't return anything on a SHOW command, whereas PQntuples reports the right value (if it didn't, fetch*() would be broken).

The libpq docs state that the result is available only on certain operations and the list doesn't include SHOW indeed.

It seems that we should rather use PQntuples on anything that returns row, because the result will be consistent with what fetchall() would return. The situation might be a bit confused with server-side cursors, so I'll need to be some more tests.

So, will fix. However, I don't think you should rely on rowcount for your use case (I have personally never used it this way >15 years I've used psycopg...) The best way to verify if a query has returned 0 or 1 record is to call cur.fetchone() and verify if it returns None or a record.

cur.execute("SHOW thing")
rec = cur.fetchone()
if rec:
    # use rec[0]
else:
    # nothing was found

In your case, by the way, I expect SHOW to throw an exception rather than an empty resultset if the entry is not found...

>>> cur.execute("SHOW nothing")
...
# UndefinedObject: unrecognized configuration parameter "nothing"

[khushboovashi]

We (pgAdmin team) have extensively used rowcount in our code and it was working fine with psycopg2. While migrating to psycopg3, I came across this issue. So, for time being I am going to use cursor.pgresult.ntuples instead of cursor.rowcount.

[dvarrazzo]

Ok, I thought the same issue was in psycopg2 too.

I will fix the issue in 3.0.16 so you can use cursor.rowcount from there on.

[dvarrazzo]

Psycopg 3.0.16 released, including this fix.