Search code examples
postgresqlpostgresql-9.6pgadmin-4

pgAdmin says n records affected, output shows n*x records affected

I'm using pgAdmin 4v2.1, postgresql 9.6, running locally.

I didn't notice any issues within pgAdmin for queries until yesterday, where I added a call to row_number to create an insert script (details below) with a primary key value based on the row number.

After running this, it seems that pgAdmin is getting screwed up and appending data output together? I add the question mark because I haven't looked into the actual data too much, but the output seems to be getting n rows longer every time, n being the number of actual rows the query should be returning.

Example Create a table and insert some data. My table is as below:

CREATE TABLE sample
(
    name_1 character varying(250),
    name_2 character varying(250),
    name_1_id integer,
    name_2_id integer
);

This is a temp table meant for staging a data import for clean up, then I map to existing IDs for name_1 and name_2. All that worked well.

My total count for rows is 2473, which is correct. This returns the correct value every time.

select count(*) from sample;

Now the tricky part:

  • I can run the following code and it works (2473 records returned)

    select name_1_id, name_2_id from sample;

  • I can run this, and it works (2098 records, after grouping)

    select name_1_id, name_2_id from sample group by name_1_id, name_2_id;

  • Now if I run either of the queries above a second time, I start getting nonsensical rows back.
    • Group by query returns 4473 rows.
    • Each additional run of either query in the same pgAdmin session seems to return 1000 more rows in the record set, though there may be more than that in some queries. The additional rows are duplicates of actual data, and using row_number to generate a row number also duplicates. (I was migrating existing data and using row_number to generate a primary key for lookup data, when scripting insert scripts.)

I'm assuming this is a pgAdmin bug. Is there a way around this without using a separate tool or a new query window (session) every time?

Solution

  • Yes, It's a bug and will be fixed in next release. Ref: https://redmine.postgresql.org/issues/3039

    You can use DEV release until it release publicly. Download: https://developer.pgadmin.org/~dpage/runtime-revamp/

    Make sure to upgrade to Stable release once it out.