[SA 7.2.328 Pro] Postgresql - "admin" schema

[SA 7.2.328 Pro] Postgresql - "admin" schema

Looks like assistance for "admin" schema doesn't work.
In our DB we have "admin" schema consists of tables and functions.

When I write
SELECT * FROM ad
and press CTRL+SPACE, schema is going to be completed with double-quote around the name. But after adding a dot next to name there are no tables nor functions suggested.
Also

This scenario works for other schemas very well.

with regards

I'm unable to reproduce this issue.

Could you please provide DDL for your admin schema and objects which we can reproduce ina test environment? Please email it to support(AT)softtreetech.com

What I found more - probably related - the admin schema branch in Explorer contains no children. IN other words it cannot be unfolded.

I suspect issue to be related with a case where data about objects are gathered from information_schema. I informed about it in DLL related thread.
Unfortunately I cannot get to clue right now.
Is there any method to debug all sent by SA sql queries? I think ODBC has such feature but there is no any SA connection defined. Maybe you can point me to sql script used to gather data from db for the schema children.

All queries are in the Options for users to see and customize if needed. You can copy them to a SQL editor and and execute them directly. There is no need to run a trace. For example, to see which schema objects are returned for "admin" schema, copy from the Options the text of "Objects (PostgreSQL)" query, paste into an editor and replace :SCHEMA_NAME with 'admin' then execute the query. What you get might in the result is what should appear in the explorer and in the popups and other places. Hope this will provide a clue as what is special in case of the admin schema. Could be a permissions issue or something else.

A few new findings:

1. It's not related to admin schema only. There is at least another one behaving the same way
2. While hovering over such schema, pupup appear with "Count database objects" method. But execution of it shows: No objects found
3. Still don't now for sure which query is fired for gathering Explorer tree data for specific schema. My guess is: Objects. I took this query and got it run in SA query window. And it worked: it returned expected list of tables and functions. What can I do more?

Last edited by michalk on Thu Jun 11, 2015 9:46 am; edited 1 time in total

If that query works in the editor, it must be a different cause. Perhaps it's trying to execute it in a wrong database. Please try changing the database in the top drop-down in the toolbar (that's above the explorer). I assume you are using SA SQL Editor. Am I correct? Does changing the database have any impact on the results?

Yes, you are correct assuming I'm working with SQL editor.

We have more databases. some are just development versions of production ones, which means DDL is the same including ACL.
But we have another database, completely different structure and what is important for our test case: there is only one instance of this database. So there is no way to connect to other db with similar structure (difference in object tree would be obvious)

Fortunately (for test case of course) I also found here one schema which conform described issue.
I compared this schema ACLs to other schemas which are shown properly and really see no difference.
OBJECTS query also returns all expected objects.

So, if OBJECTS query is run on wrong DB, it must be true for specific schema only, cause other schemas are unique across our whole system.

Edit:
I looked into db logs (we log every SQL query sent to db). And there are OBJECT queries executed for those unfolded schemas. So problem must lay in processing returned data (which doesn't look unusual, except of long names of some objects - I can paste result data here if you want)

Please try executing "Schema" query too and check what it returns for the "admin" schema. Maybe there is a role or something else with the same name confusing SA

Very interesting observation. Would you be able to email results of the Objects query to support?

Do you mean "SCHEMAS" query?
I run it in both databases. Afflicted schemas are shown with SC in second column. So nothing unusual here.
yes I can send you results, please give me the email address.

The support email can be fount in the Options dialog on the About tab. Thank you.

I found no email. I did send it using support form available on SoftTree mainpage.

Thank you

BTW I've sent data for the "another" database. In which case, stats schema is afflicted one

Hello.

Could you please also email the results of the Schemas query?

We think there is something else, like a user role or something confusing SA, that's why it's bot showing admin schema objects