SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.2.328 Pro] Postgresql - "admin" schema
Goto page Previous  1, 2
 
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: 211

Post Reply with quote
I've sent it.
Meantime I did another test. I replaced OBJECTS sql script by one which takes data from pg_catalog:

Code:

SELECT
    relname table_name,
    CASE
        WHEN relkind='r' THEN 'TA'
        WHEN relkind='v' THEN 'VI'
    END,
    NULL create_time,
   NULL alter_time
FROM pg_catalog.pg_class AS pc
JOIN pg_catalog.pg_namespace AS pn ON pc.relnamespace=pn.oid
WHERE pn.nspname= :SCHEMA_NAME
    AND relkind IN ('r', 'v')


This code retrieve data for only tables and views.
And you know what? It also doesn't work for afflicted schema.
Of course, query itself works returning expected result. When going through Explorer tree, it is sent to a server and causes no error. And it works for other schemas.
In log I saw another two queries sent to DB after this one (to retrieve object description), but those queries returns no records for neither of tested schema
really strange.
Mon Jun 15, 2015 8:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Thank you so much. Let's check the provided data..
Mon Jun 15, 2015 12:45 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
We are still unable to reproduce this issue. We would like to request schema dump for the stats schema so we can recreate it locally.

Command line to get schema dump:
pg_dump -i -h localhost -p 5432 -n schema_name -U user_name db_name > schema_dump.ddl
Mon Jun 15, 2015 11:20 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Hello.
I've sent the DDL of investigated database on support email
Fri Jun 19, 2015 7:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Just to restate what I've heard so far. We are unable to reproduce this issue using the provided DDL. There are 2 possible reasons for that. Our test environment is not exactly the same. WE may have tested a more recent version of SQL Assistant.

If you are interested in further testing, we can provide a private build of version 7.3 (this is an incomplete and potentially not very stable version).
Thu Jun 25, 2015 9:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Our developers asked to try a simple test to see if that issue is schema specific. They asked to temporarily comment out all lines in the "Objects (PostgreSQL)" query in SA options that refer to :SCHEMA_NAME, I mean lines like

Code:
where
   routine_schema = :SCHEMA_NAME



This should show the same objects in all schemas. This way we can see if the same objects will appear in your admin and stats schemas, and we can also see if it's a client side issue or a server side issue. In other words, check if any schemas show no objects at all as you described in the subject of this issue. If yes, that may indicate some issues with the internal cache or something else on the client side.
Fri Jul 03, 2015 10:49 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
I commented the schema limiting condition. Now all schemas contain all tables and views.
By all schemas I mean also those affected by the issue.
After that tables branch contains also tables from issued schemas.

The issue re-appeared after condition has been uncommented.
Tue Jul 07, 2015 5:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Hi again. Our developers suspect there might be an issue with parameter binding in your specific PG client libraries version.

They are asking to do another quick test to confirm that hypothesis. Please modify the queries one more time and replace references to :SCHEMA_NAME with '$SCHEMA_NAME$'
Please include the quotes as specified here. This change will make SQL Assistant use macro parameters for schema names instead of bind variables. Please let us know if that resolves the issue.
Tue Jul 07, 2015 4:58 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
After the last requested change it started to work. Issued schemas now contain expected objects
Good work!
Wed Jul 08, 2015 5:06 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
After the last requested change it started to work. Issued schemas now contain expected objects
Good work!
Wed Jul 08, 2015 5:56 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
After installing SA v7.3.429 and resetting settings to default the issue returned
using :SCHEMA_NAME - still doesn't work

Fortunately replacing tag by '$SCHEMA_NAME$' (with single-quotes) does the trick.
Fri Oct 16, 2015 5:06 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
In our tracking system the casue of the issue is stated as a suspected bug in Pg ODBC driver not always handling correctly parameters binding.

The replacement of binding parameter reference :SCHEMA_NAME with macro variable $SCHEMA_NAME$ makes SQL Assistant not to use parameters and instead inject the value directly into the code before the code is sent to the database. Someone suggested upgrading to a more recent version of Pg ODBC driver v9.4 which we think may have this issue fixed. Another solution is using ADO.Net interface instead of ODBC when connecting to PG servers.
Fri Oct 16, 2015 9:36 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 211

Post Reply with quote
Thanks, I'm going to check that then back with info
Fri Oct 16, 2015 10:35 am 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 Previous  1, 2
Page 2 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.