This project is archived and is in readonly mode.
Return number of rows for copy operations
-
Federico Di GregorioIf I remember correctly the libpq docs don't say if it is possible to
retrieve the number of inserted columns and we're not sending data
line-by-line but as chunks so counting the lines isn't possible. Maybe
(a big maybe) the final PGresult contains that but I can't find this documented anywhere. We'll need to instrument psycopg to print PGresult
fields and do a bit of guesswork. -
Rui Da CostaI'm no expert, but isn't it described here?:
www.postgresql.org/docs/9.3/static/libpq-exec.html#LIBPQ-PQCMDTUPLESThis function returns a string containing the number of rows affected by the SQL statement that generated the PGresult. This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, ...
-
Nicholas Chammas
On a related note, I have been using psycopg2 with Amazon Redshift and recently asked a similar question about getting the rowcount for COPY statements executed via cursor.execute. I've attached a PDF of the linked AWS forum page in case you can't access it.
Amazon claims that Redshift correctly reports rowcount to the calling interface and that psycopg2 doesn't seem to pick it up.
How can I validate Amazon's claim?
-
Daniele Varrazzo- State changed from open to resolved
- Milestone set to psycopg2 2.5.3
As Rui pointed out the number of rows is returned by PQcmdTuples. We weren't calling this function on copy results but only after normal opreations.
Fixed in 2.5.3. Nicholas, try checking out https://github.com/psycopg/psycopg2/tree/maint_2_5 and see if it works as expected, thank you.
-
Bilal Aslam
Daniele, thanks for checking in the fix. I tried this out on May 27 using the branch you suggested, but I am still getting a rowcount of -1.
In [36]: cursor.rowcount
Out[36]: -1Nicholas, did you have any better luck than me?
-
Daniele Varrazzo
Hello Bilal,
yes, it works for me: check the test suite. The changeset is released in psycopg 2.5.3
Note that old postgres versions (before 8.2) don't return the rowcount. So it depends on a message the server must send. If the server doesn't send it we cannot help.
-
Nicholas Chammas
Hey Daniele,
It appears that we are still seeing the -1 rowcount on COPYs in psycopg 2.5.3 when testing against Redshift 1.0.789. This may well be an issue on Redshift's side, and I think it's beyond psycopg2's mandate to work cleanly with forks of PostgreSQL. (I think Bilal is also testing against Redshift.)
FYI: Redshift is a distant fork of PostgreSQL 8 with a column-oriented storage engine, hosted as a service by Amazon. Its primary use case is for massive analytics workloads. Amazon recommends using PostgreSQL drivers to interact with the database.
Anyway, I am following up with Amazon support here to get to the bottom of this. They claim it should work.
