SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.2.328 Pro] Postgresql - "admin" schema
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.2.328 Pro] Postgresql - "admin" schema
Author Message
michalk



Joined: 29 Aug 2014
Posts: 142

Post [SA 7.2.328 Pro] Postgresql - "admin" schema Reply with quote
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
Mon Jun 08, 2015 12:10 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
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
Tue Jun 09, 2015 12:24 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
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.
Wed Jun 10, 2015 9:19 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
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.
Wed Jun 10, 2015 11:36 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
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
Thu Jun 11, 2015 7:23 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
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?
Thu Jun 11, 2015 9:46 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
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)
Thu Jun 11, 2015 10:13 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
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
Thu Jun 11, 2015 11:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
Very interesting observation. Would you be able to email results of the Objects query to support?
Thu Jun 11, 2015 11:13 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
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.
Thu Jun 11, 2015 11:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
The support email can be fount in the Options dialog on the About tab. Thank you.
Thu Jun 11, 2015 12:57 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
I found no email. I did send it using support form available on SoftTree mainpage.
Fri Jun 12, 2015 5:29 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
Thank you
Fri Jun 12, 2015 9:08 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
BTW I've sent data for the "another" database. In which case, stats schema is afflicted one
Fri Jun 12, 2015 9:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6513

Post Reply with quote
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
Sat Jun 13, 2015 1:06 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.