Author |
Message |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
[SA 9.0.166 Pro] - PostgreSql - no arguments for some func |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
I'll try
|
|
Fri Nov 04, 2016 9:55 am |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
OK. here is.
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT oid FROM pg_proc WHERE proname = 'add_log'
----
-1107746896 |
And the same query run in pgAdmin:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
michalk
Joined: 29 Aug 2014 Posts: 211
|
|
|
|
Hello again.
It's worse than I expected :(
With ODBC, my exemplary sql query:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
This issue is fixed in the latest released build 9.0.194.
|
|
Fri Dec 02, 2016 1:59 am |
|
![](templates/GreenTech/images/spacer.gif) |
|