Subject: Re: create_immv issue on aws Ubuntu even after create extention

From: Ron Johnson <[email-masked]>
Date: Fri, 28 Feb 2025 13:40:36 -0500

When you know that the SELECT statement runs on the local machines, then on
each server, run these SQL statements:

SHOW search_path;

SELECT * FROM pg_extension;

On Fri, Feb 28, 2025 at 12:26 PM Krishnakant Mane <[email-masked]>
wrote:

>
> On 2/28/25 22:53, Ron Johnson wrote:
>
>
> Differing search_path values can lead to problems like this.
>
> So what can I do to resolve this?
>
> On Fri, Feb 28, 2025 at 12:01 PM Krishnakant Mane <[email-masked]>
> wrote:
>
>> Ok here you go.
>>
>> select create_immv('plantskillpayment', 'select distinct kwapp_plant.id
>> as plantid, kwapp_skill.id as skillid, kwapp_skill.skillname,
>> kwapp_plant.plantname, kwapp_pltsklpaylvl.level, kwapp_pltsklpaylvl.pay
>> from kwapp_skill, kwapp_plant,  kwapp_pltsklpaylvl  where kwapp_skill.id
>> = kwapp_pltsklpaylvl.skillid_id  and kwapp_plant.id =
>> kwapp_pltsklpaylvl.plantid_id');
>>
>>
>> You see, the point is this works perfectly on all local machines with
>> exact same versions and same data.
>>
>> Regards.
>> On 2/28/25 22:24, Ron Johnson wrote:
>>
>> On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane <[email-masked]>
>> wrote:
>>
>>> Hello all.
>>>
>>> I am not able to use create_immv in postgresql 16.6 even after
>>> installing it and doing create extention.
>>>
>>> I did a git clone of the repository and then make sudo make install to
>>> install it.
>>>
>>> The issue is not happening on my local ubuntu 24.04 machine but on
>>> ubuntu 24.04 on aws free tier.
>>>
>>> All dependencies are in place, that's why the extention got installed in
>>> the first place.
>>>
>>> I get the error "function create_immv(unknown, unknown) does not exist.
>>>
>>> all the tables related to this view exist, data is present and same
>>> setup works perfectly on my local machine, same OS and postgresql version.
>>>
>>> Can someone suggest what could be the solution?
>>>
>> Show your work!
>>
>> What command did you run to get that error?
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>> --
>> *Krishnakant Mane*
>> Software Architecture Design & Implementation Specialist
>>
>> Mobile:
>> +91 8424039903
>> https://www.linkedin.com/in/krishnakant-r-mane/
>>
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
> --
> *Krishnakant Mane*
> Software Architecture Design & Implementation Specialist
>
> Mobile:
> +91 8424039903
> https://www.linkedin.com/in/krishnakant-r-mane/
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Krishnakant Mane <[email-masked]>
Date: Sat, 1 Mar 2025 10:59:20 +0530

All done, but no result.

I feel it is possible that the latest code must be flawed or something.

Well, as a matter of fact, I never had to compile the extention on my 
local ubuntu 24.04 machine.

I just did a create extention and it worked just fine.

But on my aws server I had to compile it manually and still it does not 
work.

Regards.

On 3/1/25 00:10, Ron Johnson wrote:
> When you know that the SELECT statement runs on the local machines, 
> then on each server, run these SQL statements:
>
> SHOW search_path;
> |SELECT * FROM pg_extension;|
>
> On Fri, Feb 28, 2025 at 12:26 PM Krishnakant Mane 
> <[email-masked]> wrote:
>
>
>>     On Fri, Feb 28, 2025 at 12:01 PM Krishnakant Mane
>>     <[email-masked]> wrote:
>>
>>         Ok here you go.
>>
>>         select create_immv('plantskillpayment', 'select distinct
>>         kwapp_plant.id <http://kwapp_plant.id> as plantid,
>>         kwapp_skill.id <http://kwapp_skill.id> as skillid,
>>         kwapp_skill.skillname, kwapp_plant.plantname,
>>         kwapp_pltsklpaylvl.level, kwapp_pltsklpaylvl.pay from
>>         kwapp_skill, kwapp_plant,  kwapp_pltsklpaylvl  where
>>         kwapp_skill.id <http://kwapp_skill.id>  =
>>         kwapp_pltsklpaylvl.skillid_id  and kwapp_plant.id
>>         <http://kwapp_plant.id> = kwapp_pltsklpaylvl.plantid_id');
>>
>>
>>         You see, the point is this works perfectly on all local
>>         machines with exact same versions and same data.
>>
>>         Regards.
>>
>>>         On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane
>>>         <[email-masked]> wrote:
>>>
>>>             Hello all.
>>>
>>>             I am not able to use create_immv in postgresql 16.6 even
>>>             after installing it and doing create extention.
>>>
>>>             I did a git clone of the repository and then make sudo
>>>             make install to install it.
>>>
>>>             The issue is not happening on my local ubuntu 24.04
>>>             machine but on ubuntu 24.04 on aws free tier.
>>>
>>>             All dependencies are in place, that's why the extention
>>>             got installed in the first place.
>>>
>>>             all the tables related to this view exist, data is
>>>             present and same setup works perfectly on my local
>>>             machine, same OS and postgresql version.
>>>
-- 
Email Signature
*Krishnakant Mane*

Mobile: 	
	+91 8424039903

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 1 Mar 2025 08:47:48 -0800

> local ubuntu 24.04 machine.

How did you install Postgres on the AWS EC2 instance?

Did you install the postgresql-server-dev-16 package?

> But on my aws server I had to compile it manually and still it does not 

Define not work.

-- 
Adrian Klaver
[email-masked]

---

From: Krishnakant Mane <[email-masked]>
Date: Sun, 2 Mar 2025 06:55:25 +0530

> Did you install the postgresql-server-dev-16 package?
Yes I did.
>> But on my aws server I had to compile it manually and still it does 
>> not work.
>
> Define not work.
>

create extension command works successfully.

But when I try creating the same immv on the server it says function 
create_immv (unknown, unknown ) does not exist.

Note that this works on my local machine.

Mobile: 	
	+91 8424039903

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 1 Mar 2025 17:33:17 -0800

>> How did you install Postgres on the AWS EC2 instance?

Please answer above.

> create_immv (unknown, unknown ) does not exist.

Connect with psql then do:

1) \dx
This will show what extensions are installed and in what schema.

2) show search_path;

3) \df *.create_immv

Provide the outputs from the above three commands in your response.

---

From: Krishnakant Mane <[email-masked]>
Date: Sun, 2 Mar 2025 07:09:09 +0530

On 3/2/25 07:03, Adrian Klaver wrote:
>>>> Well, as a matter of fact, I never had to compile the extention on 
>>>> my local ubuntu 24.04 machine.
>>>
> sudo apt-get install postgresql postgresql-server-dev-16
>>>

Mobile: 	
	+91 8424039903