SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.166 Pro] - PostgreSql - no arguments for some func
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.166 Pro] - PostgreSql - no arguments for some func
Author Message
michalk



Joined: 29 Aug 2014
Posts: 142

Post [SA 9.0.166 Pro] - PostgreSql - no arguments for some func Reply with quote
I found recently, SA doesn't suggest arguments for some functions/table functions.
This finding is valid in all places functin name can be extended by this information: hovering bubble over function name, inline function suggestion list, function list in object browser.

Currently I figured out no pattern of this behaviour. Doesn't matter arguments, types, defaults nor return type (saw for boolean, void, table as yet). But a lot of other functions are extended by argument information as expected.

Do you have any suggestion regarding point I could start from to better specify the reason?
Fri Nov 04, 2016 8:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
Would you please provide a DDL of such function or functions so that we can try to reproduce the issue in our test environment?
Fri Nov 04, 2016 9:54 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
I'll try
Fri Nov 04, 2016 9:55 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
OK. here is.


Code:
CREATE FUNCTION "michalk"."add_log"
(
   query VARCHAR
)
RETURNS void AS
$$
INSERT INTO michalk.tab_log
(
   log_time,
   log_ip,
   log_user,
   log_query   
)
VALUES (now(), inet_client_addr(), SESSION_USER, $1);

$$
LANGUAGE SQL;

Fri Nov 04, 2016 10:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
I'm unable to reproduce this issue.

Can you please tell me if the function for which you don't see paramaters appears in information_schema.parameters and information_schema.routines system views.
Fri Nov 04, 2016 12:16 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
I probably figured out what happens.
It seems :OBJECT_ID is treated as SIGNED INTEGER, while
following postgresql documentation you can read: The oid type is currently implemented as an unsigned four-byte integer.

Query executed in SQL editor:

Quote:
SELECT oid FROM pg_proc WHERE proname = 'add_log'
----
-1107746896


And the same query run in pgAdmin:

Quote:
SELECT oid FROM pg_proc WHERE proname = 'add_log'
----
3187220400


Finally running "Arguments" query using 3187220400 as :OBJECT_ID I'm getting expected result.



with regards[/url]
Fri Nov 04, 2016 2:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
Thank you so much for investigating this issue and getting to the bottom of it. I strongly suspect it's related to the other issue you reported earlier about negative integer numbers converted to zeroes when using the native LibPq interface; if you change your connection to ADO.NET or ODBC, then it should work for all functions.
Fri Nov 04, 2016 4:21 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Despite supposed dependency on already fixed 'negative integers' issue, I can confirm the issue reported in this thread has not been fixed in build 9.0.185.
The query still returns negative values for OID
Mon Nov 21, 2016 10:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
So sorry about that. Apparently it's a different issue.

Can you suggest how this issue can be reproduced? Is there a way to update catalog with large OID or some other way to recreate that setup in a custom table that I can send to the development team. A SQL snippet would be most helpful.
Mon Nov 21, 2016 12:00 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Reproducing it using clean database can be a challenge because the database would generate too low values for new created objects.
I did some tests which proved that it is :OBJECT_ID which provide negative value.

Then I make sure I, that casting to BIGINT is enough:
Code:

SELECT
    oid AS oid_orig,
    oid::bigint AS oid_bigint
FROM pg_proc
WHERE proname = 'add_log'
------
oid_orig   oid_bigint
-1107746896   3187220400


Following this experiment I had added cast to BIGINT to all oids returned in Objects query.
But it didn't change anything. :OBJECT_ID remains negative.

Maybe I missed something or it's a matter of how :OBJECT_ID is handled internally by the program. Just a guess.
Tue Nov 22, 2016 6:57 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
Thank you very much. This is very helpful indeed. You did say when using ODBC interface, the same works correctly, right? If that's the case it indicates that when using native driver, the OID data type is converted to INT instead of BIGINT.

There is an easy way to test that theory and a fix for that. Please modify the Objects (PostgreSQL) query in SQL Assistant Options and after each oid reference in the SELECT clause add ::BIGINT. Here is the modified query

Code:
SELECT relname,
       CASE relkind
          WHEN 'r' THEN 'TA'
          WHEN 'f' THEN 'TA'
          WHEN 'S' THEN 'SQ'
          WHEN 'v' THEN 'VI'
      WHEN 'm' THEN 'MA'
       END ,
       oid::BIGINT,
       NULL::TIMESTAMP,
       NULL::TIMESTAMP
FROM   pg_catalog.pg_class
WHERE  relnamespace = :SCHEMA_ID
       AND relkind IN ('r', 'f', 'v', 'S', 'm')

UNION ALL

SELECT p.proname,
       CASE format_type (t.oid, NULL)
          WHEN 'trigger'    THEN 'PC'
          WHEN 'void'    THEN 'PR'
          WHEN 'record'    THEN 'FT'
       ELSE              'FU'
       END ,
       p.oid::BIGINT,
       NULL::TIMESTAMP,
       NULL::TIMESTAMP
FROM   pg_catalog.pg_proc          p,
       pg_catalog.pg_type          t,
       pg_catalog.pg_namespace     nt
WHERE  pronamespace = :SCHEMA_ID
       AND t.oid = p.prorettype
       AND t.typnamespace = nt.oid

UNION ALL

SELECT t.tgname,
       'TR',
       t.oid::BIGINT,
       NULL::TIMESTAMP,
       NULL::TIMESTAMP
FROM   pg_catalog.pg_trigger     t,
       pg_catalog.pg_class       c
WHERE  t.tgrelid = c.oid
       AND c.relnamespace = :SCHEMA_ID

Tue Nov 22, 2016 10:55 am View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
This is what I actually have done (mentioned in one of my last sentences). It didn't work though.
I didn't check it with ODBC. I'll try it tomorrow.
Tue Nov 22, 2016 1:51 pm View user's profile Send private message
michalk



Joined: 29 Aug 2014
Posts: 142

Post Reply with quote
Hello again.
It's worse than I expected :(

With ODBC, my exemplary sql query:
Code:
SELECT
    oid AS oid_orig,
    oid::bigint AS oid_bigint
FROM pg_proc
WHERE proname = 'add_log'
------
oid_orig   oid_bigint
3187220400    3187220400
 


Returns the same values. And it's the last good news.

Unfortunately Editor stopped to properly recognize schemas' content with ODBC connector. In database explorer I can see schemas, but no objects inside. Intelisense doesn's suggest objects (functions, tables) after entering a dot preceded by schema name. Forcing object list to open (by CTRL+SPACE), the list contains only objects from default space (public schema I believe). CTRL+LMB on function name doesn't show function DDL. Error message appears instead: '<some_name>' is not recognized as a valid object name in the current database, where <some_name> is name of function I wanted to show.

I checked this behaviour using default settings of SA by switching connector between native psql and odbc mutliple times.
used version:
postgresql: 9.2
SA: 9.0.185
please note, I don't know if broken ODBC appeared in recent version or it's present since the first release of 9.x. I'm using native connection only since it's available.

Right now I have to turn back to native connector, since it's more functional for obvious reasons.
I would like to help you more, but I literally have no enough time to pursue it. I hope you get rid of those problems soon.

EDit: Speaking about Object explorer. If I expand name of function which returns table, I can see table icon with [+] symbol, suggesting ability to expand returning it more, probably getting list of returned columns. However after expanding, no new items appear, [+] symbol disappear.
Wed Nov 23, 2016 6:27 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
Quote:
In database explorer I can see schemas, but no objects inside. Intelisense doesn's suggest objects (functions, tables) after entering a dot preceded by schema name.


It's part of the same issue. The issue is not specific to functions, it impacts all database queries where OID values are passed as parameters in the WHERE clause. But the issue is a bit more complicated than that. The OBJECT_ID parameter used in queries is an INT4 which is the same across all supported database systems. The OID values appear to be UINT4, not BIGINT.

Good news, we are testing a fix supporting UINT4 based object identifiers. I hope this fix will make into the next public build tentatively planned for the end of this week.
Wed Nov 23, 2016 7:07 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
This issue is fixed in the latest released build 9.0.194.
Fri Dec 02, 2016 1:59 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 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.