Which schema should I install pgunit scripts to?

[yallie]

Hello Adrian,

should the pgunit be installed in every schema where I have test_case_xxx functions? I'd prefer to install it once and use it from all my schemas, is it possible?

I tried installing it to the public schema, but I seem to have problems using it that way. It works well when I call it from the same public shema, but fails when I call it from another schema I currently work with, like this:

select * from public.test_run_all();

-- or like this
do $$ begin
    select * from public.test_run_all();
end $$;

Postgres reports this error:

Severity: ERROR, Code: 42883, Line: 523, Position:
ErrorMessage: function test_run_suite(unknown) does not exist
Hint: No function matches the given name and argument types. 
You might need to add explicit type casts.

Looks like Postgres searches for the function being called in the current schema, not the schema where test_run_all and test_run_suite functions are defined. I see that in your scripts all function calls are not qualified with the schema name, and it looks like the cause of my trouble.

Sorry if that's a dumb question, I'm new to Postgres. Thanks.

[adrianandrei-ca]

Hi Alexey,

It is a very good question and if anything it pinpoints a lack on my documentation.

It was my intention to let the develop deploy the code wherever he/she wants and as such I make the assumption the code is accessible. It may be probably better to impose a naming convention and have the code secured this way but I make the assumption a developer using this code is familiar with the pitfalls of navigating from schema to schema.

It is my assumption you are using set schema xxx; to avoid qualifying the code and if so them maybe is better to deploy the testing code in schema xxx. I would recommend though to make the assumption you are in public schema at all times and qualify your statements so you deal with down the road schema additions.

But yes, you are pinpointing a weakness which that the code needs to be visible from the schema you are currently in. I will investigate if I can detect the schema I am in and schema where the test support code is in and qualify the calls.

I apologize for your inconvenience.

Regards, Adrian

On Wed, Nov 30, 2016 at 12:01 PM, Alexey Yakovlev notifications@github.com wrote:

Hello Adrian,

should the pgunit be installed in every schema where I have test_case_xxx functions? I'd prefer to install it once and use it from all my schemas, is it possible?

I tried installing it to the public schema, but I seem to have problems using it that way. It works well when I call it from the same public shema, but fails when I call it from another schema I currently work with, like this:

select from public.test_run_all(); -- or like this do $$ begin select from public.test_run_all(); end $$;

Postgres reports this error:

Severity: ERROR, Code: 42883, Line: 523, Position: ErrorMessage: function test_run_suite(unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Looks like Postgres searches for the function being called in the current schema, not the schema where test_run_all and test_run_suite functions are defined. I see that in your scripts all function calls are not qualified with the schema name, and it looks like the cause of my trouble.

Sorry if that's a dumb question, I'm new to Postgres. Thanks.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/adrianandrei-ca/pgunit/issues/2, or mute the thread https://github.com/notifications/unsubscribe-auth/AQR4avRKuHQF96_W5erGrT4Fna5hN6E-ks5rDav9gaJpZM4LAb03 .

[yallie]

Hello Adrian, thanks for your reply!

It is my assumption you are using set schema xxx; to avoid qualifying the code.

Looks like my query tool invokes this automatically when I select a schema in the list.

maybe is better to deploy the testing code in schema xxx

I did that, and it gave the same error about dblink_connect, so I have to duplicate it in the current schema, too.

I will investigate if I can detect the schema I am in and schema where the test support code is in and qualify the calls.

Thanks Adrian, that would be great! Please consider qualifying dblink_connect function calls as well.

Regards, Alexey.

[yallie]

I did that, and it gave the same error about dblink_connect, so I have to duplicate it in the current schema, too. ... Please consider qualifying dblink_connect function calls as well.

Actually, all used dblink_* functions: dblink_connect, dblink_disconnect, dblink_exec.

[adrianandrei-ca]

Hi Alexey,

I found a solution for your issue. If you are switching from public schema to your schema please follow the steps here: http://stackoverflow.com/questions/2516842/add-schema-to-path-in-postgresql

For your convenience please use the statement below after the set schema statement. SELECT set_config( 'search_path', current_setting('search_path') || ',public', false ) WHERE current_setting('search_path') !~ '(^|,)public(,|$)';

That will add public to the search path if is not already there.

I reproduced your problem and the query above fixed the issue; as such you do not have to have the code duplicated.

Have fun, Adrian

On Thu, Dec 1, 2016 at 4:41 AM, Alexey Yakovlev notifications@github.com wrote:

I did that, and it gave the same error about dblink_connect, so I have to duplicate it in the current schema, too.

Actually, all used dblink_* functions: dblink_connect, dblink_disconnect, dblink_exec.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/adrianandrei-ca/pgunit/issues/2#issuecomment-264125972, or mute the thread https://github.com/notifications/unsubscribe-auth/AQR4atgcnEm47ydUZHKXW12IGXStUvFhks5rDpY1gaJpZM4LAb03 .

[yallie]

Hello Adrian,

thanks for your help! Unfortunately, that doesn't fix the problem because in my case search_path is being reset by the client software on each query. I guess I'll have to investigate the auto-detection of pgunit and dblink schemas.

[adrianandrei-ca]

                                                                                  That is unfortunate.                                                                                                                                                                                                                                                                                                                                        Sent from my BlackBerry 10 smartphone on the Bell network.                                                                                                                                                                                                                From: Alexey YakovlevSent: Friday, December 2, 2016 5:56 AMTo: adrianandrei-ca/pgunitReply To: adrianandrei-ca/pgunitCc: adrianandrei-ca; State changeSubject: Re: [adrianandrei-ca/pgunit] Which schema should I install pgunit scripts to? (#2)Hello Adrian,

thanks for your help! Unfortunately, that doesn't help because in my case search_path is being reset by the client software on each query. I guess I'll have to investigate the auto-detection of pgunit and dblink schemas.

—You are receiving this because you modified the open/close state.Reply to this email directly, view it on GitHub, or mute the thread.

{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/adrianandrei-ca/pgunit","title":"adrianandrei-ca/pgunit","subtitle":"GitHub repository","main_image_url":"https://cloud.githubusercontent.com/assets/143418/17495839/a5054eac-5d88-11e6-95fc-7290892c7bb5.png","avatar_image_url":"https://cloud.githubusercontent.com/assets/143418/15842166/7c72db34-2c0b-11e6-9aed-b52498112777.png","action":{"name":"Open in GitHub","url":"https://github.com/adrianandrei-ca/pgunit"}},"updates":{"snippets":[{"icon":"PERSON","message":"@yallie in #2: Hello Adrian,\r\n\r\nthanks for your help!\r\nUnfortunately, that doesn't help because in my case \r\nsearch_path is being reset by the client software on each query.\r\nI guess I'll have to investigate the auto-detection of pgunit and dblink schemas."}],"action":{"name":"View Issue","url":"https://github.com/adrianandrei-ca/pgunit/issues/2#issuecomment-264428371"}}}