Re: create_immv issue on aws Ubuntu even after create extention
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
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 , and butter sauce.
Don't boil me, I'm still alive.
lobster!
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
https://www.linkedin.com/in/krishnakant-r-mane/
Re: create_immv issue on aws Ubuntu even after create extention
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
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
> 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
>> 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 , and butter sauce.
>> Don't boil me, I'm still alive.
>> lobster!
>>
>> --
>> *Krishnakant Mane*
>> Software Architecture Design & Implementation Specialist
>>
>> Mobile:
>> +91 8424039903
>> https://www.linkedin.com/in/krishnakant-r-mane/
>>
>
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
> lobster!
>
> --
> *Krishnakant Mane*
> Software Architecture Design & Implementation Specialist
>
> Mobile:
> +91 8424039903
> https://www.linkedin.com/in/krishnakant-r-mane/
>
--
Death to , and butter sauce.
Don't boil me, I'm still alive.
lobster!
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 https://www.linkedin.com/in/krishnakant-r-mane/
Re: Azure Database for PostgreSQL flexible server: password authentication failed
Sorry, correction for the openssl command output: $ openssl s_client -connect 172.21.32.4:5432 -starttls postgres Connecting to 172.21.32.4 CONNECTED(0003) Can't use SSL_get_servername depth=2 C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2 verify return:1 depth=1 C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07 verify return:1 depth=0 C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN= c1fba9900d4d.database.azure.com verify return:1 --- Certificate chain 0 s:C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN= c1fba9900d4d.database.azure.com i:C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07 a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA384 v:NotBefore: Feb 25 14:04:55 2025 GMT; NotAfter: Aug 24 14:04:55 2025 GMT 1 s:C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07 i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2 a:PKEY: rsaEncryption, 4096 (bit); sigalg: RSA-SHA384 v:NotBefore: Jun 8 00:00:00 2023 GMT; NotAfter: Aug 25 23:59:59 2026 GMT 2 s:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2 i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2 a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256 v:NotBefore: Aug 1 12:00:00 2013 GMT; NotAfter: Jan 15 12:00:00 2038 GMT --- Server certificate -BEGIN CERTIFICATE- MIIJAjCCBuqgAwIBAgITMwFrt0ld3qCMMByM7wAAAWu3STANBgkqhkiG9w0BAQwF ADBdMQswCQYDVQQGEwJVUzEeMBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9u MS4wLAYDVQQDEyVNaWNyb3NvZnQgQXp1cmUgUlNBIFRMUyBJc3N1aW5nIENBIDA3 MB4XDTI1MDIyNTE0MDQ1NVoXDTI1MDgyNDE0MDQ1NVowdjELMAkGA1UEBhMCVVMx CzAJBgNVBAgTAldBMRAwDgYDVQQHEwdSZWRtb25kMR4wHAYDVQQKExVNaWNyb3Nv ZnQgQ29ycG9yYXRpb24xKDAmBgNVBAMTH2MxZmJhOTkwMGQ0ZC5kYXRhYmFzZS5h enVyZS5jb20wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC2DAq18XNN Z/Jmmi7CLNlwEmTXGqAU9O+mNSjoQdFXNkw+CsyvPgohhBv35R/iN0Km8r3MV793 +RgORhpj6I/0nEOTeIJwVZIjSAEO+BDnCcn58vcCNqyES0QJ9IcVFYpu9jT19mAb kvKjbcNbyJX4rKHwToXaDlxOTvaQMESci6XbY1Ixwd5MJHRUyg8c6+RbN1emA1Vm pMPukdlaCZlH9HnD/IXcY/EUJXoQxfYJPupDH5BefQrazwHgF8vCJ9tNuxk/8tu4 leTiQxth6liveloD5QvfEEffgo9kzgT6hGVbi7Rc0u52i1nij3nFlGQAWOCYfr3A 0dAS5vYug7WhAgMBAAGjggSgMIIEnDCCAX0GCisGAQQB1nkCBAIEggFtBIIBaQFn AHYAEvFONL1TckyEBhnDjz96E/jntWKHiJxtMAWE6+WGJjoAAAGVPXYABAMA RzBFAiBWJCHBbRAlwMXXEkTLba2Pzp1N8MR4ANBkmP9lgsw0SAIhAKOwOq+62T+g 0BgnVC4EEAC2jqjNPLxHdjZOogDiKQaLAHUAfVkeEuF4KnscYWd8Xv340IdcFKBO lZ65Ay/ZDowuebgAAAGVPXYAmgAABAMARjBEAiAKgJP9C2rqQVsRmN2n2qERvQcc xisnOO41cSr7d1oYTQIgLl7B30ElHd+81o3+jd4WoBTE2lmRUFPqmH3aGBEFoZEA dgAaBP9J0FQdQK/2oMO/8djEZy9O7O4jQGiYaxdALtyJfQAAAZU9dgDWAAAEAwBH MEUCICtDLEVHUfSi+PZ8jOTyBvRSbfj06loyvD2V66cOpYcfAiEAnPy1VHyO+SlE ygBp6CyUdAj5G7dPCQYzfAqy2HFiv3wwJwYJKwYBBAGCNxUKBBowGDAKBggrBgEF BQcDAjAKBggrBgEFBQcDATA8BgkrBgEEAYI3FQcELzAtBiUrBgEEAYI3FQiHvdcb gefrRoKBnS6O0AyH8NodXYKr5zCH7fEfAgFkAgEtMIG0BggrBgEFBQcBAQSBpzCB pDBzBggrBgEFBQcwAoZnaHR0cDovL3d3dy5taWNyb3NvZnQuY29tL3BraW9wcy9j ZXJ0cy9NaWNyb3NvZnQlMjBBenVyZSUyMFJTQSUyMFRMUyUyMElzc3VpbmclMjBD QSUyMDA3JTIwLSUyMHhzaWduLmNydDAtBggrBgEFBQcwAYYhaHR0cDovL29uZW9j c3AubWljcm9zb2Z0LmNvbS9vY3NwMB0GA1UdDgQWBBTNtIVCLokZd4K37tTOK5Lu JI5hXzAOBgNVHQ8BAf8EBAMCBaAwgakGA1UdEQSBoTCBnoIyY2NnLWRldmVsb3At cG9zdGdyZXNxbC5wb3N0Z3Jlcy5kYXRhYmFzZS5henVyZS5jb22CR2YxYjhmMGMw OTA2YS5jY2ctZGV2ZWxvcC1wb3N0Z3Jlc3FsLnByaXZhdGUucG9zdGdyZXMuZGF0 YWJhc2UuYXp1cmUuY29tgh9jMWZiYTk5MDBkNGQuZGF0YWJhc2UuYXp1cmUuY29t MAwGA1UdEwEB/wQCMAAwagYDVR0fBGMwYTBfoF2gW4ZZaHR0cDovL3d3dy5taWNy b3NvZnQuY29tL3BraW9wcy9jcmwvTWljcm9zb2Z0JTIwQXp1cmUlMjBSU0ElMjBU TFMlMjBJc3N1aW5nJTIwQ0ElMjAwNy5jcmwwZgYDVR0gBF8wXTBRBgwrBgEEAYI3 TIN9AQEwQTA/BggrBgEFBQcCARYzaHR0cDovL3d3dy5taWNyb3NvZnQuY29tL3Br aW9wcy9Eb2NzL1JlcG9zaXRvcnkuaHRtMAgGBmeBDAECAjAfBgNVHSMEGDAWgBTO FRY76gKjpmva2Sv95YxSvnpQqDAdBgNVHSUEFjAUBggrBgEFBQcDAgYIKwYBBQUH AwEwDQYJKoZIhvcNAQEMBQADggIBAHIlDqGGk6NsNXDVyXsheLN7L9FP4tHjdiwy +GSPgrrb5lWuqTjFWdzYokm0RW/Ez0JX2aq88BueTGUNw6XO9pq/KD44OD8VClJH WeW3NhCKn901uyV9rUMrNZ37oPlM53NP6zkC1qfOy4sLG5UHr+Ne532W0mtVga5K YeeufReC/1Ze/3xZQ6iTxrt39urvDhIpVQZap3GUwTEqiOH6T+kp8DnuwpScLTBB B9HmMModtysYLRH8Gl4jTyLfCdI+hfOavESLev8F+jmgIyEOvHH5bWf/N1Lp2NaE LdbJ5pMcACzkcG71TTUGhrDums4ukng9ggJ+jQ+dS7n5eXVF+H7GbA1bj+wKq8UB dXEHinaPin4Xer4KqKMV62lHclEMQzvzI6KH9OT4+wKi6dZ78MVmCvJJJsZKk0dP dfnK6/Nbw5khDPXqEvQru86cRU0KGrUuKOCF0yeeXMc1kyU4O6cAhScMwbQ+WXTN TpSflR4NK4+QIoc9yShP9oAQV4uvAO8WtH5fzWYKyuY4oPJlyecLXzfo1Ll+vipx DaOc/pNY6WUKNz3b4qRSP8iPArvyi8ZSRn7so1Dsuk9+225cs67WQKnA05YZc1hO S3PVFN2225qZ0NLxAFQbDp5zb9QWFOpylzwYXW1+FNzpM1RDTL6us5kn3Ip4F+FY HQ8wk+6o -END CERTIFICATE- subject=C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN= c1fba9900d4d.database.azure.com issuer=C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07 --- Acceptable client certificate CA names DC=GBL, DC=AME, CN=AME INFRA CA 01 DC=GBL, DC=AME, CN=AME Infra CA 02 DC=GBL, DC=AME, CN=AME Infra CA 03 DC=GBL, DC=AME, CN=AME Infra CA 04 DC=GBL, DC=AME, CN=AME Infra CA 05 DC=GBL, DC=AME, CN=AME I
Re: create_immv issue on aws Ubuntu even after create extention
On Fri, Feb 28, 2025 at 11:41 AM Krishnakant Mane 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 , and butter sauce. Don't boil me, I'm still alive. lobster!
Azure Database for PostgreSQL flexible server: password authentication failed
Good afternoon, I am using an Azure Database for PostgreSQL flexible server with no public ip. It is connected to a private VNet and I try to access it from an Alpine Linux container with openssl and postgresql-client installed. In Azure portal I have clicked the "Reset password" button, entered twice password12345!! and received the web browser notification "Successfully reset the password for postgresql" Then I run the commands: $ openssl s_client -connect 172.21.32.4:5432 -starttls postgres (then after some time) 2C82:error:8000274C:system library:BIO_connect:Unknown error:../openssl-3.2.3/crypto/bio/bio_sock2.c:178:calling connect() 2C82:error:1067:BIO routines:BIO_connect:connect error:../openssl-3.2.3/crypto/bio/bio_sock2.c:180: connect:errno=0 $ PGPASSWORD="password12345!!" psql "postgresql://postgres:password12345!!@ 172.21.32.4:5432/postgres?sslmode=require" psql: error: connection to server at "172.21.32.4", port 5432 failed: FATAL: password authentication failed for user "postgres" In the server logs I see entries for some other connections: 2025-02-28 14:39:35 UTC-67c1ca93.50ee-LOG: disconnection: session time: 0:00:20.024 user=azuresu database=postgres host=169.254.128.1 port=53076 2025-02-28 14:39:36 UTC-67c1caa8.5146-LOG: connection received: host=169.254.128.1 port=49016 2025-02-28 14:39:36 UTC-67c1caa8.5146-LOG: connection authenticated: identity=\"CN=azuresu.c1fba9900d4d.database.azure.com\" method=cert (/datadrive/pg/data/pg_hba.conf:17) 2025-02-28 14:39:36 UTC-67c1caa8.5146-LOG: connection authorized: user=azuresu database=postgres SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256) I do not see any logs related to my unsuccessful tries. In the "Server parameters" I have set the parameters: log_connections ON log_hostname ON log_statement MOD max_connections 500 ssl ON (cannot change that one) listen_address '*' (cannot change that one) And currently I am stuck, wonder how do others debug such problems? Best regards Alex
Re: How to debug: password authentication failed for user
Thank you for the comments, I must think some time how to handle this.
My intention has been to generate 2 random strings in an Azure pipeline,
save them as secrets in an Azure Key Vault (so that our C# ASP.Net app can
fetch them when starting up in Kubernetes) and also pass them to the
pipeline tasks, which would pass them as the build arg to the "docker
build" command:
# Generate random passwords as save them as KV secrets
- task: AzurePowerShell@5
displayName: 'Write PostgreSQL passwords to KV'
inputs:
azureSubscription: '${{ parameters.ArmConnection }}'
ScriptType: 'InlineScript'
azurePowerShellVersion: 'LatestVersion'
Inline: |
# Generate a random password for PUSH_PULL_PASS and store it in the KV
$pushPullPass = ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A) |
Get-Random -Count 20 | % {[char]$_}) )
$pushPullSecret = ConvertTo-SecureString -String $pushPullPass
-AsPlainText -Force
Set-AzKeyVaultSecret -VaultName '${{ parameters.ResourceKeyVault }}'
-Name PushPullPass -SecretValue $pushPullSecret
# Generate a random password for TIMESHIFT_PASS and store it in the KV
$timeshiftPass = ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A)
| Get-Random -Count 20 | % {[char]$_}) )
$timeshiftSecret = ConvertTo-SecureString -String $timeshiftPass
-AsPlainText -Force
Set-AzKeyVaultSecret -VaultName '${{ parameters.ResourceKeyVault }}'
-Name TimeshiftPass -SecretValue $timeshiftSecret
# Set the pipeline vars for the 2 docker builds below
Write-Host "##vso[task.setvariable
variable=PushPullPass]$pushPullPass"
Write-Host "##vso[task.setvariable
variable=TimeshiftPass]$timeshiftPass"
# build Push Pull DB docker file
- task: Docker@2
displayName: Build Push Pull DB docker image
inputs:
command: build
repository: '$(PushPullReponame)'
dockerfile:
'$(Build.SourcesDirectory)/suuCcg/src/Services/SUU.PushPullDatabase/Dockerfile'
arguments: "--no-cache --build-arg PGPASSWORD=$(PushPullPass)"
tags: $(ImageTag)
buildContext: '$(Build.SourcesDirectory)/suuCcg/src/'
# build Timeshift DB docker file
- task: Docker@2
displayName: Build Timeshift DB docker image
inputs:
command: build
repository: '$(TimeshiftReponame)'
dockerfile:
'$(Build.SourcesDirectory)/suuCcg/src/Services/SUU.TimeshiftDatabase/Dockerfile'
arguments: "--no-cache --build-arg PGPASSWORD=$(TimeshiftPass)"
tags: $(ImageTag)
buildContext: '$(Build.SourcesDirectory)/suuCcg/src/'
And then I am not done yet :-) I need to pass that random string from the
Dockerfile to the 01-create-database.sql and I have tried it as env var:
# To build locally: docker build -f
Services/SUU.TimeshiftDatabase/Dockerfile --build-arg
PGPASSWORD=timeshift_pass .
# To run locally in Git Bash: winpty docker run --rm -it -p 5432:5432
sha256:...
FROM postgres:17-alpine3.21
RUN apk update && apk upgrade && apk add --no-cache pg_top
ARG PGPASSWORD
# Tell docker-entrypoint.sh to create superuser "postgres"
# with password passed as build arg and database "postgres"
ENV POSTGRES_PASSWORD=$PGPASSWORD
# Tell docker-entrypoint.sh to change these params in postgresql.conf
ENV POSTGRES_INITDB_ARGS="--set max_connections=200 \
--set shared_buffers=16GB \
--set work_mem=8MB \
--set maintenance_work_mem=128MB \
--set effective_cache_size=8GB \
--set from_collapse_limit=24 \
--set join_collapse_limit=24 \
--set log_min_messages=notice \
--set log_connections=on \
--set log_statement=all \
--set listen_addresses='*'"
ENV PGUSER=postgres
ENV PGPASSWORD=$PGPASSWORD
ENV PGDATABASE=timeshift_database
# The files below are executed by the DB superuser "postgres"
# in alphabetical order after the database has been initialized
WORKDIR /docker-entrypoint-initdb.d
COPY ./Services/SUU.TimeshiftDatabase/01-create-database.sql .
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-create-tables.sql
./02-create-tables.sql
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-create-functions.sql
./03-create-functions.sql
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-smoke-tests.sql
./04-smoke-tests.sql
# Prepend \c timeshift_database to each SQL file using sed
RUN sed -i.bak '1ic timeshift_database' ./02-create-tables.sql
RUN sed -i.bak '1ic timeshift_database' ./03-create-functions.sql
RUN sed -i.bak '1ic timeshift_database' ./04-smoke-tests.sql
# Drop root privileges
USER postgres
But I see that the whole chain is tricky to implement and I'd like to
switch from a postgres:17-alpine3.21 based Docker image to the "Azure
PostgreSQL flexible server" product anyway, to have less maintenance.
Best regards
Alex
Re: Azure Database for PostgreSQL flexible server: password authentication failed
Hi Alex, pt., 28 lut 2025 o 16:05 Alexander Farber napisał(a): > Now the connection from a container in AKS works for me: > > $ psql "postgresql://postgresql:password12345!!@ > 172.21.32.4:5432/postgres?sslmode=require" > psql (17.4, server 16.6) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, > compression: off, ALPN: none) > Type "help" for help. > > postgres=> > good it finally worked ;) > > I must find now how to add a non-super user to it next. > You are adding users there as in any other postgres instance, just use CREATE ROLE/USER from SQL prompt or use createuser utility.
Re: Azure Database for PostgreSQL flexible server: password authentication failed
Ah my team mate has spotted the error for me. And it paid off, that he has no experience with PostgreSQL yet :-) Because the admin user for Azure Database for PostgreSQL flexible server is not "postgres" but "postgresql" Now the connection from a container in AKS works for me: $ psql "postgresql://postgresql:password12345!!@ 172.21.32.4:5432/postgres?sslmode=require" psql (17.4, server 16.6) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none) Type "help" for help. postgres=> I must find now how to add a non-super user to it next. Best regards Alex
Re: create_immv issue on aws Ubuntu even after create extention
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
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
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 , and butter sauce.
Don't boil me, I'm still alive.
lobster!
--
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
https://www.linkedin.com/in/krishnakant-r-mane/
--
Death to , and butter sauce.
Don't boil me, I'm still alive.
lobster!
--
Email Signature
*Krishnakant Mane*
Software Architecture Design & Implementation Specialist
Mobile:
+91 8424039903
https://www.linkedin.com/in/krishnakant-r-mane/
Re: Long Running query and trace potential issues
On Fri, Feb 28, 2025 at 6:50 AM KK CHN wrote: > List > > postgres=# SELECT PID, now() - pg_stat_activity.query_start AS duration, > query, state FROM pg_stat_activity WHERE (now() - pg > _stat_activity.query_start) > interval '5 minutes' AND state = 'active'; > pid |duration | query > | state > > --+-+--+ > 3957 | 17 days 12:00:30.782583 | START_REPLICATION B5/EE00 TIMELINE 1 > | active > (1 row) > postgres=# > > Postgres 16 and pgbackrest 2.52.1 on RHEL 9.4 > > Is this a potential issue ? > Only if you don't want to be replicating to a standby server. > It has been running for 17 days . ( I have configured WAL replication to > a standby server and pgbackrest to a remote server ) is this normal > running 17 days START_REPLICATION B5/EE00 TIMELINE1 active ? > Select more columns, like username,client_hostname and client_addr. You started replicating to the standby server 17 and a half days ago. This is how PG replicates to the standby server. > Any hints to further trace where the query is executing, what it's > trying to perform, how to optimize if this is not normal ? Any guidance is > much appreciated.. > It's certainly normal if you want to replicate to a standby server. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Long Running query and trace potential issues
That's harmless, it is the walsender process, and it is meant to be long-running. You can modify your query and add this: AND backend_type = 'client backend' to filter out any background processes. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support
Long Running query and trace potential issues
List postgres=# SELECT PID, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg _stat_activity.query_start) > interval '5 minutes' AND state = 'active'; pid |duration | query | state --+-+--+ 3957 | 17 days 12:00:30.782583 | START_REPLICATION B5/EE00 TIMELINE 1 | active (1 row) postgres=# Postgres 16 and pgbackrest 2.52.1 on RHEL 9.4 Is this a potential issue ? It has been running for 17 days . ( I have configured WAL replication to a standby server and pgbackrest to a remote server ) is this normal running 17 days START_REPLICATION B5/EE00 TIMELINE1 active ? Any hints to further trace where the query is executing, what it's trying to perform, how to optimize if this is not normal ? Any guidance is much appreciated.. Thank you, Krishane
Re: create_immv issue on aws Ubuntu even after create extention
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 https://www.linkedin.com/in/krishnakant-r-mane/
