create_immv issue on aws Ubuntu even after create extention
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?
Regards.
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane <kkproghub@gmail.com>
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!
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
<kkproghub@gmail.com> 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!
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
Differing search_path values can lead to problems like this.
On Fri, Feb 28, 2025 at 12:01 PM Krishnakant Mane <kkproghub@gmail.com>
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 <kkproghub@gmail.com>
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 SpecialistMobile:
+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!
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
<kkproghub@gmail.com> 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 2/28/25 22:24, Ron Johnson wrote:
On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane
<kkproghub@gmail.com> 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 SpecialistMobile:
+91 8424039903https://www.linkedin.com/in/krishnakant-r-mane/
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
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 <kkproghub@gmail.com>
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 <kkproghub@gmail.com>
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 <kkproghub@gmail.com>
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 SpecialistMobile:
+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 SpecialistMobile:
+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!
On 2/28/25 09:26, Krishnakant Mane 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?
In psql do:
\dx
That will show you what extensions are installed and in what schema.
Then read this:
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
It explains search_path and how to modify it.
--
Adrian Klaver
adrian.klaver@aklaver.com
Thank you.
Just a quick question.
should it be in pg_catalog?
if yes then it is so.
On 3/1/25 01:39, Adrian Klaver wrote:
On 2/28/25 09:26, Krishnakant Mane 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?
In psql do:
\dx
That will show you what extensions are installed and in what schema.
Then read this:
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
It explains search_path and how to modify it.
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
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
<kkproghub@gmail.com> 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
<kkproghub@gmail.com> 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 2/28/25 22:24, Ron Johnson wrote:
On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane
<kkproghub@gmail.com> 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 SpecialistMobile:
+91 8424039903https://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 SpecialistMobile:
+91 8424039903https://www.linkedin.com/in/krishnakant-r-mane/
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On 2/28/25 21:29, Krishnakant Mane wrote:
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.
How did you install Postgres on the AWS EC2 instance?
Did you install the postgresql-server-dev-16 package?
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.
Define not work.
Regards.
On 3/1/25 00:10, Ron Johnson wrote:
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/1/25 22:17, Adrian Klaver wrote:
On 2/28/25 21:29, Krishnakant Mane wrote:
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.How did you install Postgres on the AWS EC2 instance?
Did you install the postgresql-server-dev-16 package?
Yes I did.
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.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.
Regards.
On 3/1/25 00:10, Ron Johnson wrote:
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On 3/1/25 17:25, Krishnakant Mane wrote:
On 3/1/25 22:17, Adrian Klaver wrote:
On 2/28/25 21:29, Krishnakant Mane wrote:
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.How did you install Postgres on the AWS EC2 instance?
Please answer above.
Did you install the postgresql-server-dev-16 package?
Yes I did.
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.
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.
Note that this works on my local machine.
Regards.
On 3/1/25 00:10, Ron Johnson wrote:
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation SpecialistMobile:
+91 8424039903
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/2/25 07:03, Adrian Klaver wrote:
On 3/1/25 17:25, Krishnakant Mane wrote:
On 3/1/25 22:17, Adrian Klaver wrote:
On 2/28/25 21:29, Krishnakant Mane wrote:
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.How did you install Postgres on the AWS EC2 instance?
Please answer above.
sudo apt-get install postgresql postgresql-server-dev-16Did you install the postgresql-server-dev-16 package?
Yes I did.
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.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.
Note that this works on my local machine.
Regards.
On 3/1/25 00:10, Ron Johnson wrote:
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation SpecialistMobile:
+91 8424039903
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On 3/1/25 17:39, Krishnakant Mane wrote:
But when I try creating the same immv on the server it says function
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.
See above and provide answers.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/2/25 07:23, Adrian Klaver wrote:
On 3/1/25 17:39, Krishnakant Mane wrote:
But when I try creating the same immv on the server it says
function 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.
pg_ivm | 1.10 | pg_catalog
plpgsql | 1.0 | pg_catalog.
2) show search_path;
"$user" public.
3) \df *.create_immv
pgivm | create_immv | bigint | text, text | func.
Provide the outputs from the above three commands in your response.See above and provide answers.
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On 3/1/25 18:14, Krishnakant Mane wrote:
On 3/2/25 07:23, Adrian Klaver wrote:
On 3/1/25 17:39, Krishnakant Mane wrote:
But when I try creating the same immv on the server it says
function 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.pg_ivm | 1.10 | pg_catalog
plpgsql | 1.0 | pg_catalog.
2) show search_path;
"$user" public.
3) \df *.create_immv
pgivm | create_immv | bigint | text, text | func.
Provide the outputs from the above three commands in your response.See above and provide answers.
So the extension functions are in the pgivm schema.
You will need to either always schema qualify the functions:
select pgivm.create_imv();
or add the pgivm schema to the search_path.
For how to do that see the link I provided earlier in this thread:
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
Using the info from above you will probably want to persist the
search_path setting in postgresql.conf as:
https://www.postgresql.org/docs/current/runtime-config-client.html
as
search_path
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation SpecialistMobile:
+91 8424039903
--
Adrian Klaver
adrian.klaver@aklaver.com
On Saturday, March 1, 2025, Krishnakant Mane <kkproghub@gmail.com> wrote:
pg_ivm | 1.10 | pg_catalog
plpgsql | 1.0 | pg_catalog.
2) show search_path;
"$user" public.
3) \df *.create_immv
pgivm | create_immv | bigint | text, text | func.
Provide the outputs from the above three commands in your response.See this issue for why this is different now.
https://github.com/sraoss/pg_ivm/pull/116
Then deal with the fact it is no longer installed to pg_control but instead
pgivm. With usual schema qualifications or search_path techniques.
David J.
On 3/2/25 07:54, David G. Johnston wrote:
On Saturday, March 1, 2025, Krishnakant Mane <kkproghub@gmail.com> wrote:
pg_ivm | 1.10 | pg_catalog
plpgsql | 1.0 | pg_catalog.
2) show search_path;
"$user" public.
3) \df *.create_immv
pgivm | create_immv | bigint | text, text | func.
Provide the outputs from the above three commands in your response.See this issue for why this is different now.
https://github.com/sraoss/pg_ivm/pull/116
Then deal with the fact it is no longer installed to pg_control but
instead pgivm. With usual schema qualifications or search_path techniques.
Thank you david.
Can you tell me how exactly should the syntax be?
Is my function definition wrong?
David J.
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On 3/2/25 07:54, David G. Johnston wrote:
On Saturday, March 1, 2025, Krishnakant Mane <kkproghub@gmail.com> wrote:
pg_ivm | 1.10 | pg_catalog
plpgsql | 1.0 | pg_catalog.
2) show search_path;
"$user" public.
3) \df *.create_immv
pgivm | create_immv | bigint | text, text | func.
Provide the outputs from the above three commands in your response.See this issue for why this is different now.
https://github.com/sraoss/pg_ivm/pull/116
Then deal with the fact it is no longer installed to pg_control but
instead pgivm. With usual schema qualifications or search_path techniques.
Thank you, it worked. now pgivm.create_immv works.
I should have got it before.
David J.
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
On Saturday, March 1, 2025, Krishnakant Mane <kkproghub@gmail.com> wrote:
Can you tell me how exactly should the syntax be?
https://www.postgresql.org/docs/current/ddl-schemas.html
Is my function definition wrong?
It isn’t a function definition, it is a function call.
David J.