Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Primary key showing the key mixed from another table with same name on diferent schema on postgres #842

Closed
KohlerCia opened this issue Jan 7, 2020 · 5 comments

Comments

@KohlerCia
Copy link

@KohlerCia KohlerCia commented Jan 7, 2020

Firts: Sorry for some type errors, English is not my main language.

Steps to reproduce this issue

  1. Step 1;
    Create 2 schemas with diferent names like 'laboratorio' and 'ppcp'
  2. Step 2;
    Create 1 table named as 'ordemservico03' on schema 'laboratorio' with the columns 'codord', 'codamo'
    Set the columns 'codord' and 'codamo' as primary key.
  3. Step 3;
    Create 1 table named as 'ordemservico03' on schema 'ppcp' with the columns 'codord', 'seqord', 'pecpro', 'quipro'
    Set the columns 'codord' and 'seqord' as primary key

Current behavior

1 - On the 'indexes' view on each table the primary key show the columns 'codord', 'codamo', 'codord', 'seqord' as show on the pictures below.
ppcp_ordemservico03
laboratorio_ordemservico03

2 - I cant change the columns values on the records on this tables manualy, because show the error: Selected columns don't contain a sufficient set of key columns to allow editing. Please select primary or unique key columns, or ajust all columns.
error

Is possible to change the values with the sql editor .

Expected behavior

Should show only the primary key on each table selected on the schema, and should be possible to change the values directly on the table.

Possible solution

I believe the heidi is not geting the current schema of the selected table to get the primary key.

Environment

  • HeidiSQL version:10.3.0.5771
  • Database system and version:postgre 10.11.0
  • Operating system:windows 10
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 19, 2020

Could you please update HeidiSQL to the latest build and retry?
I refactored a big part of the abstraction layer where columns and keys are retrieved. And I just could not reproduce the issue here, so maybe it's already fixed.

@KohlerCia

This comment has been minimized.

Copy link
Author

@KohlerCia KohlerCia commented Jan 20, 2020

Hi.

The problem persists.
I downloaded and installed the compilation 5837.

Im using heidy for 2 or 3 years now and i remember having this problem for a long time. but along the road, in one of the compilation this issue was fixed, but after that the problem came back.
I can't remember in what compilation this issue was fixed but i belived was 1 or 2 compilations before the color change on the column, like when the value on the column is the same in 2 or more rows the values got a yellow background color.

Can you point me out what version/compilation was added this color? so i can download and test.
Id i found the right compilation when this issue was fixed you can compare the codes so it will be easy to find.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 20, 2020

That field color commit could be 7e28c51

However, I just found Heidi's internal query for retrieving table indexes on PG (the one starting with WITH ndx_list AS...) has no "schema='currentschema'" clause. Similar to what I found in #859 btw. So, HeidiSQL mixes indexes of two equally named tables in different schemata.

Maybe you can help out and fix that SQL query so it contains a schema clause?

@KohlerCia

This comment has been minimized.

Copy link
Author

@KohlerCia KohlerCia commented Jan 21, 2020

hi.

I got the code and review the sql.

i added this 2 lines

AND pg_class.relnamespace = pg_namespace.oid
AND pg_namespace.nspname = current_schema

and added the table on from

FROM pg_index, pg_class, pg_namespace

on the sql below and it should work i believe.

KeyQuery := GetResults('WITH ndx_list AS ('+
' SELECT pg_index.indexrelid, pg_class.oid'+
' FROM pg_index, pg_class, pg_namespace'+
' WHERE pg_class.relname = '+EscapeString(Table.Name)+
' AND pg_class.oid = pg_index.indrelid'+
' AND pg_class.relnamespace = pg_namespace.oid'+
' AND pg_namespace.nspname = current_schema'+
' ),'+
' ndx_cols AS ('+
' SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx,'+
' CASE i.indisprimary WHEN true THEN '+EscapeString('PRIMARY')+' ELSE CASE i.indisunique ``WHEN true THEN '+EscapeString('UNIQUE')+' ELSE '+EscapeString('KEY')+' END END AS ``CONSTRAINT_TYPE,'+
' pg_class.oid'+
' FROM pg_class'+
' JOIN pg_index i ON (pg_class.oid = i.indexrelid)'+
' JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid)'+
' )'+
'SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME '+
'FROM pg_attribute a '+
'JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) '+
'JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid)'
);

Executing this sql on the heidi when im on the schema brings only the keys for the table on that schema. before those 2 lines it will bring all keys with same table name on diferent schemas.

Edited: also just adding this line should work
AND pg_table_is_visible(pg_class.oid)

I hope this will help.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 21, 2020

Thank you for your help! I overtook that pg_table_is_visible() clause, and already saw this works for me correctly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.