Re: Problem with a Query

Aggressive autoanalyze and autovacuum settings solve most query problems. These are my settings: default_statistics_target = 5000 autovacuum_vacuum_scale_factor = 0.015 autovacuum_vacuum_threshold = 250 autovacuum_analyze_scale_factor = 0.015 autovacuum_analyze_threshold = 250 Such a high default_

Re: Problem with a Query

Thanks Tom. Collecting full stats on the tables involved corrected the execution. On Tue, Aug 13, 2024 at 9:57 AM Tom Lane wrote: > Siraj G writes: > > We migrated a PgSQL database from Cloud SQL to compute engine and since > > then there is a SQL we observed taking a long time. After some stud

Re: Problem with a Query

Siraj G writes: > We migrated a PgSQL database from Cloud SQL to compute engine and since > then there is a SQL we observed taking a long time. After some study, I > found that the SQL is using NESTED LOOP where the cost is too high. The core of your problem seems to be here: >

Re: problem with query

upgrade to "PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit" solved the problem regards, s On Wed, 22 May 2024 at 06:04, Sašo Gantar wrote: > ANALYZE pg_class; doesn't help > also, query is from "Hasura", so I don't have much r

Re: problem with query

ANALYZE pg_class; doesn't help also, query is from "Hasura", so I don't have much room to maneuver On Tue, 21 May 2024 at 16:18, Tom Lane wrote: > =?UTF-8?B?U2HFoW8gR2FudGFy?= writes: > > thanks for the info, but is there any solution, given that it's system > > tables? > > Given the complexity

Re: problem with query

=?UTF-8?B?U2HFoW8gR2FudGFy?= writes: > thanks for the info, but is there any solution, given that it's system > tables? Given the complexity of the query, I wonder if you're running into problems with join_collapse_limit/from_collapse_limit preventing the planner from considering all options. Al

Re: problem with query

sorry... SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES FROM (WITH partitions AS (SELECT array (WITH partitioned_tables AS (SELECT array (SELECT oid FROM pg_class WHERE relkind = 'p') AS parent_tables) SELE

Re: problem with query

On Tue, 21 May 2024 at 23:14, Laurenz Albe wrote: > We still don't know the query. hmm, it was posted on this thread: https://postgr.es/m/CAGB0_6600w5C=hvhgfmwcqo9bcwcg+3s0pxxuoqv48nlqtp...@mail.gmail.com David

Re: problem with query

On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote: > thanks for the info, but is there any solution, given that it's system tables? We still don't know the query. Yours, Laurenz Albe

Re: problem with query

thanks for the info, but is there any solution, given that it's system tables? regards On Tue, 21 May 2024 at 12:09, Laurenz Albe wrote: > On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote: > [execution plan without query text or explanation] > > The time is lost here: > > -> WindowAgg (

Re: problem with query

On Mon, 20 May 2024 at 23:09, Sašo Gantar wrote: > what helps is > SET enable_nestloop = off; > query takes less then 2seconds > but it's probably not a good idea to change this flag Looks like it's slow due to a bad selectivity estimate on the join between pgn and pgc. This results in: -> Nes

Re: problem with query

On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote: [execution plan without query text or explanation] The time is lost here: -> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401) Buffers: shared hit=1710825 Yours, Laurenz Albe

Re: problem with query

what helps is SET enable_nestloop = off; query takes less then 2seconds but it's probably not a good idea to change this flag On Wed, 15 May 2024 at 13:23, David Rowley wrote: > On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote: > > this query takes more than 8 seconds, > > if i remove "AND ((pg

Re: problem with query

> > > Aggregate (cost=512.53..512.54 rows=1 width=32) (actual > time=8430.692..8430.724 rows=1 loops=1) > Buffers: shared hit=2031540, temp read=954 written=956 > -> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152) > (actual time=8257.310..8430.532 rows=57 loops=1) > Buff

Re: problem with query

On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote: > this query takes more than 8 seconds, > if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't very useful as there's no way to tell if the planner's estimates

Re: problem loading shared lib pg_tde.so

El día martes, mayo 07, 2024 a las 07:07:22 +0200, Matthias Apitz escribió: > # ls -l /usr/local/sisis-pap/lib/libcurl* > -rw-r--r-- 1 bin bin 1315526 May 6 10:29 /usr/local/sisis-pap/lib/libcurl.a > -rwxr-xr-x 1 bin bin1004 May 6 10:29 /usr/local/sisis-pap/lib/libcurl.la > -rwxr-xr-x 1 bin

Re: problem loading shared lib pg_tde.so

El día lunes, mayo 06, 2024 a las 07:45:52 -0700, Adrian Klaver escribió: > On 5/6/24 07:42, Adrian Klaver wrote: > > On 5/6/24 04:05, Matthias Apitz wrote: > > > > > I see three different versions of OpenSSL: > > > > OPENSSL_1_1_1d  -- From error messsage > > OpenSSL 1.1.1l-fips    -- SuSE

Re: problem loading shared lib pg_tde.so

On 5/6/24 07:42, Adrian Klaver wrote: On 5/6/24 04:05, Matthias Apitz wrote: I see three different versions of OpenSSL: OPENSSL_1_1_1d  -- From error messsage OpenSSL 1.1.1l-fips    -- SuSE 15 version OpenSSL 1.1.1t    -- Your built version? Are you sure you pointing at the same

Re: problem loading shared lib pg_tde.so

On 5/6/24 04:05, Matthias Apitz wrote: I have a problem while loading the pg_tde.so shared lib. contrib/pg_tde was built with: cd postgresql-16.2/contrib/pg_tde || exit gmake clean export LDFLAGS="-L/usr/local/sisis-pap/lib -L/usr/lib64" export CFLAGS="-m64 -I/usr/local/sisis-pap/include" expor

Re: Problem with CAST-ing - am I missing something?

On 10/25/23 17:26, Christophe Pettus wrote: On Oct 25, 2023, at 17:21, Pól Ua Laoínecháin wrote: SELECT (ts, te)::TSTZRANGE FROM test; That syntax doesn't mean what you probably think it does. (ts, te) defines a record type with two fields. PostgreSQL constructs that, and then attempts

Re: Problem with CAST-ing - am I missing something?

> On Oct 25, 2023, at 17:21, Pól Ua Laoínecháin wrote: > > SELECT (ts, te)::TSTZRANGE FROM test; That syntax doesn't mean what you probably think it does. (ts, te) defines a record type with two fields. PostgreSQL constructs that, and then attempts to apply the cast. There's no conversio

Re: Problem with CAST-ing - am I missing something?

Sorry folks - fiddle is here: https://dbfiddle.uk/bT4idNK6 Rgs, Pól...

Re: Problem perhaps after upgrading to pgadmin4 7.4

On 7/13/23 06:20, Carl Erik Eriksson wrote: Query tool PGadmin on my mac If I enter a query like select count(*) from table_1I get a correct response from the server If I enter select * from table_1 I get an error message that I do not understand: Error Message:missing FROM-clause entry f

Re: Problem perhaps after upgrading to pgadmin4 7.4

> On 13/07/2023 13:20 CEST Carl Erik Eriksson wrote: > > Query tool PGadmin on my mac > > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missin

Re: Problem perhaps after upgrading to pgadmin4 7.4

Problem solved - thanks Depez! (The solution for now is to downgrade to PGadmin4 version 7.2) > On 13 Jul 2023, at 13:52, hubert depesz lubaczewski wrote: > > On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: >> If I enter a query like select count(*) from table_1I get

Re: Problem perhaps after upgrading to pgadmin4 7.4

Thanks - it seems we might be on pg 10.17-1 /Carl > On 13 Jul 2023, at 13:52, hubert depesz lubaczewski wrote: > > On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: >> If I enter a query like select count(*) from table_1I get a correct >> response from the server >> If I

Re: Problem perhaps after upgrading to pgadmin4 7.4

On Thu, Jul 13, 2023 at 01:20:03PM +0200, Carl Erik Eriksson wrote: > If I enter a query like select count(*) from table_1I get a correct > response from the server > If I enter select * from table_1 I get an error message that I do not > understand: > Error Message:missing FROM-clause entry

Re: Problem with LATERAL

On Thu, Oct 13, 2022 at 08:04:03AM +, Eagna wrote: > > > > ERROR: syntax error at or near "WHERE" > > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); > > > > There error here is because a JOIN clause requires a join condition. Adding > > an > > "ON true" is probably what you want. You wo

Re: Problem with LATERAL

> > ERROR: syntax error at or near "WHERE" > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); > There error here is because a JOIN clause requires a join condition. Adding an > "ON true" is probably what you want. You would also need to change isnull() > with coalesce(). > The final que

Re: Problem with LATERAL

Hi, On Thu, Oct 13, 2022 at 07:05:48AM +, Eagna wrote: > > relatively simple one would have thought! I tried to convert this into a > Postgres query as follows: > > SELECT  o.order_id, >   o.total_price - COALESCE(sub.paid, 0) > FROM _order o > LEFT JOIN LATERAL ( >     SELECT SUM(p.amount) A

Re: problem with on conflict / do update using psql 14.4

On 9/24/22 09:04, Barry Kimelman wrote: On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus > wrote: > On Sep 24, 2022, at 08:49, Barry Kimelman mailto:blkimel...@gmail.com>> wrote: > I thought the whole point of ON CONFLICT DO UPDATE was so that you co

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 8:49 AM Barry Kimelman wrote: > > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted > Nope, the words "DO UPDATE" mean "DO an UPDATE command instead of failing for the CONFLICTing INSERT command". As m

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 09:04, Barry Kimelman wrote: > Unfortunately, that is exactly what I need to do, I need to modify the data > on the 2nd request so that it also gets inserted. The best approach is to optimistically insert the row, catch the error if there's a conflict, and modify the da

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:49, Barry Kimelman wrote: > > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted > > ON CONFLICT DO UPDATE allows you to modify the existi

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 08:49, Barry Kimelman wrote: > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted ON CONFLICT DO UPDATE allows you to modify the existing row that conflicted with the row being inserted, but it won't a

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 10:44 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:29, Barry Kimelman wrote: > > Thanks for the response. When I ran the INSERT with your suggested > change I got an error message telling me > > "column reference 'company_name' is ambiguous" > > As previousl

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 08:29, Barry Kimelman wrote: > Thanks for the response. When I ran the INSERT with your suggested change I > got an error message telling me > "column reference 'company_name' is ambiguous" As previously noted, you'll need to do both: add "excluded." to qualify the colu

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 07:29, Barry Kimelman wrote: > > > > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; > > The issue here is that the unique index is partial (i

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 07:29, Barry Kimelman wrote: > > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; The issue here is that the unique index is partial (it has a WHERE clause). In order to use that as an arbiter, you ne

Re: problem with on conflict / do update using psql 14.4

On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin wrote: > You need to prefix the rhs column(s) with 'excluded.'. For example: > > on conflict (company_name) do update set company_name = > concat(excluded.company_name,'++',excluded.string_company_id); > > On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelma

Re: problem with on conflict / do update using psql 14.4

On Fri, Sep 23, 2022 at 4:43 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman > wrote: > >> >> CREATE UNIQUE INDEX my_companies_id_unique ON >> my_companies(string_company_id) WHERE delete_timestamp IS NULL; >> CREATE UNIQUE INDEX my_compa

Re: problem with on conflict / do update using psql 14.4

On Fri, Sep 23, 2022 at 2:28 PM Barry Kimelman wrote: > > CREATE UNIQUE INDEX my_companies_id_unique ON > my_companies(string_company_id) WHERE delete_timestamp IS NULL; > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; > > I is

Re: problem with on conflict / do update using psql 14.4

You need to prefix the rhs column(s) with 'excluded.'. For example: on conflict (company_name) do update set company_name = concat(excluded.company_name,'++',excluded.string_company_id); On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelman wrote: > > I have not been able to get the "ON CONFLICT" clau

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

From: Laurenz Albe Sent: Wednesday, July 13, 2022 3:16 AM > You configured "pg_hba.conf" so that a local login without password is > not possible.  The easiest solution is to change the "pg_hba.conf" > entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of > the upgrade. I am af

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

On Wed, 2022-07-13 at 02:53 +, Ken Yeung wrote: > From: Laurenz Albe > Sent: Wednesday, July 13, 2022 3:16 AM > > > You configured "pg_hba.conf" so that a local login without password is > > not possible.  The easiest solution is to change the "pg_hba.conf" > > entries for "localhost" in IPv4

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

Hi, From: Thomas Boussekey Sent: Tuesday, July 12, 2022 3:39 AM > Hi, > > Le lun. 11 juil. 2022 à 21:22, Ken Yeung a > > écrit : > > In the course of upgrading a bunch of database clusters from 10 to 14 using > > pg_upgrade tool, some databases result in the following error recorded in > > p

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

On Mon, 2022-07-11 at 05:37 +, Ken Yeung wrote: > In the course of upgrading a bunch of database clusters from 10 to 14 using > pg_upgrade tool, some databases result in the following error recorded in > pg_upgrade_dump_1.log, such that cluster upgrade > fails. > FATAL:  password authenticati

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

Hi, Le lun. 11 juil. 2022 à 21:22, Ken Yeung a écrit : > In the course of upgrading a bunch of database clusters from 10 to 14 > using pg_upgrade tool, some databases result in the following error > recorded in pg_upgrade_dump_1.log, such that cluster upgrade fails. > FATAL: password authentica

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

> If it is experimental you could try pg_resetwal: https://www.postgresql.org/docs/11/app-pgresetwal.html Found this, had to use -f (program hint), but no luck. Same error. Is there anything else? Problem stille here: during restore (from pg_dumpall on 11 on i386) server on OPi crashed dur

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wed, 2022-04-06 at 18:41 +0200, W.P. wrote: > W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: > > On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: > > > Is it possible to repair this? > > Not unless you tell us what exactly you did, command by command. > > > > Yours, > > Laurenz Albe > > > > > >

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 20:09, Adrian Klaver pisze: On 4/6/22 11:04 AM, W.P. wrote: W dniu 06.04.2022 o 18:54, Ron pisze: I know about restoring backup. I just don't have one (at least with reasonnable age), as it is experimental installation. (Didn't ever been thinking about locating production

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 4/6/22 11:04 AM, W.P. wrote: W dniu 06.04.2022 o 18:54, Ron pisze: I know about restoring backup. I just don't have one (at least with reasonnable age), as it is experimental installation. (Didn't ever been thinking about locating production DB on USB drive :) ). If it is experimental y

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 06/04/2022 18:20, Laura Smith wrote: On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: That was not a "command". PG was working ok, DB on encrypted (LUKS on LVM2, ext4) USB drive. I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because y

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 18:54, Ron pisze: On 4/6/22 11:41, W.P. wrote: W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: > That was not a "command". PG was working ok, DB on encrypted (LUKS on > LVM2, ext4) USB drive. > I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because you can do it, doesn't mean you sho

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 4/6/22 11:41, W.P. wrote: W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not a "command". PG was working ok, DB on

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not a "command". PG was working ok, DB on encrypted (LUKS on LVM2, ext4

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: > Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe

Re: Problem in Storing Satellite imagey(Geotiff file)

On 10/4/21 2:19 AM, Vijay Garg wrote: Hi Adrian, I am using postgres 13.4 version & postgis 3.1 version. I have used the following command to upload the data. raster2pgsql -I -C -s 4326  C:\Users\Admin\Desktop\dem.tif raster.dem | psql -U postgres -d India -h localhost -p 5432 I am able to

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

On 10/4/21 2:28 AM, Amal Chakravarty wrote: Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. Obligatory "9.4 is EOL" comment. -- Angular momentum makes the w

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

On 10/4/21 9:28 AM, Amal Chakravarty wrote: Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. And what exactly is the issue? Does it fail in some way or are

Re: Problem in Storing Satellite imagey(Geotiff file)

On 10/1/21 9:25 AM, Vijay Garg wrote: Dear Sir/madam, I am using PostgreSQL to store the Satellite imagery(Geotiff file). I have uploaded the satellite image using raster2pgsql but it's not stored in table format. I want to show a layer on Geoserver so that I could send SQL/CQL queries on it.

Re: Problem with identity column & related sequences

Thanks. I was sort of expecting that answer but I didn't see where it was addressed specifically. Unfortunately I'm stuck on v12 for the time being so I guess it's back to the workaround. On Tue, Sep 28, 2021 at 2:13 PM Tom Lane wrote: > > Jeff Hoffmann writes: > > I am using postgresql-12.8.

Re: Problem with identity column & related sequences

Jeff Hoffmann writes: > I am using postgresql-12.8. I am using I am making use of an identity > column for part of a scripts to process some updated data. Because of > the way the script is called I don't necessarily know if this column > is going to exist in the table I am working on so I have

Re: Problem with connecting with named host parameter

On Fri, 27 Aug 2021 at 15:00, Tom Lane wrote: > Mike Martin writes: > > I am getting the following error when I connect with hostname on > localhost > > > psql -h capture -U -p 5432 > > psql: error: FATAL: no pg_hba.conf entry for host > > "fe80::7285:c2ff:feb0:cd4%eth0", user "usevideo", dat

Re: Problem with connecting with named host parameter

Mike Martin writes: > I am getting the following error when I connect with hostname on localhost > psql -h capture -U -p 5432 > psql: error: FATAL: no pg_hba.conf entry for host > "fe80::7285:c2ff:feb0:cd4%eth0", user "usevideo", database "usevideos", SSL > off Evidently, your DNS setup is re

Re: Problem upgrading from Postgresql 9.5

On Wed, 2021-07-14 at 16:12 +, Adam Norwood wrote: > First post and I'm a complete beginner when it comes to Postgresql, however > I've been tasked > with updating a versionn 9.5 install on Ubuntu 20.04 to just about any later > version I can > get (latest preferably) but have hit a number

Re: Problem building psql on Mac OS 10.15 (around readline bit I think)

Hi Tom, thanks for such a quick reply - there's the rub, I really can't think of anything other than apple point updates. I checked the configure output, and it noted that neither of those functions were available (but didn't appear to change anything or not try building) I then installed readli

Re: Problem building psql on Mac OS 10.15 (around readline bit I think)

Ben Madin writes: > I am trying to build PostgreSQL 13.2 on Mac OS X 10.15.7. I was about to > build 13.1 on the same 10.15 (but probably not 10.15.7) about 8 weeks ago > without problems, so I was a bit surprised to find this error happening. > > *input.c:82:3: error: implicit declaration of func

Re: Re: Problem enabling LDAP login

Thank you joão, that solved the problem!

Re: Problem enabling LDAP login

Hi, Marcelo checks this I have a similar problem with the LDAP filter in pg_hba.conf. I don't know if helps in your case. In my case, I needed to add the OU (Organizational Unit) https://www.postgresql.org/message-id/CAM%2BzXj0b71fckDSTxPwX58ze-9mtD4UxbktzGCmUYAnjoZti3A%40mail.gmail.com Best re

Re: Problem with trigger function

On Thursday, February 11, 2021, Steve Baldwin wrote: > David, from what I can see of the docs, for 9.6 it is PROCEDURE. It seems > FUNCTION didn't appear until 11. > Indeed. I didn’t pay attention to the version. David J.

Re: Problem with trigger function

David, from what I can see of the docs, for 9.6 it is PROCEDURE. It seems FUNCTION didn't appear until 11. Steve On Fri, Feb 12, 2021 at 7:05 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > > On Thursday, February 11, 2021, Steve Baldwin > wrote: > >> Try ... EXECUTE PROCEDURE cust

Re: Problem with trigger function

On Thursday, February 11, 2021, Steve Baldwin wrote: > Try ... EXECUTE PROCEDURE customer_num_informix() > FUNCTION, not PROCEDURE David J.

Re: Problem with trigger function

FWIW, messing with serial numbers like this is pretty risky.Sequences have transactional semantics for a reason. Dave Cramer www.postgres.rocks On Thu, 11 Feb 2021 at 14:57, Steve Baldwin wrote: > Try ... EXECUTE PROCEDURE customer_num_informix() > > Steve > > On Fri, Feb 12, 2021 at 6:47 AM J

Re: Problem with trigger function

Try ... EXECUTE PROCEDURE customer_num_informix() Steve On Fri, Feb 12, 2021 at 6:47 AM James B. Byrne wrote: > I am trying to implement a trigger in a PostgreSQL-9.6.17 database: > > CREATE OR REPLACE FUNCTION customer_num_informix() > RETURNS trigger AS $$ > BEGIN > -- An Aubit/Informix 4

Re: Problem with ssl and psql in Postgresql 13

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> I think we'd be best off to always override KRB5_KTNAME if we have a >> nonempty krb_server_keyfile setting, so the attached proposed patch >> makes both functions do it the same way. (I did not make an effort >> to remove the depe

Re: Problem with ssl and psql in Postgresql 13

Greetings, First, thanks a lot for working on all of this and improving things! * Tom Lane (t...@sss.pgh.pa.us) wrote: > I've got one last complaint about the backend GSS code: we are doing > things randomly differently in the two places that install > krb_server_keyfile as the active KRB5_KTNAME

Re: Problem with ssl and psql in Postgresql 13

I've got one last complaint about the backend GSS code: we are doing things randomly differently in the two places that install krb_server_keyfile as the active KRB5_KTNAME value. secure_open_gssapi() sets KRB5_KTNAME unconditionally (and doesn't bother to check for error, either, not a good thing

Re: Problem with ssl and psql in Postgresql 13

Here's a more complete patchset. 0001 is the same libpq fixes I posted before. 0002 fixes a collection of random server-side issues, including: * The GSS encryption code figured it could just ereport(ERROR) or ereport(FATAL) for unrecoverable errors. This seems entirely unsafe, because elog.c w

Re: Problem with ssl and psql in Postgresql 13

Here's a draft patch for the libpq-side issues. The core of the fix is to get rid of pqsecure_open_gss's clearing of allow_ssl_try, and instead check whether GSS encryption is already enabled before we try to enable SSL. While I was at it I also fixed the places where we drop an attempted GSS con

Re: Problem with ssl and psql in Postgresql 13

I wrote: > So this directly explains the original report: if the only applicable > pg_hba.conf entry is "hostssl", right here is where libpq will see > that error, and it will go around and fail again because the next > try uses neither GSS nor SSL. > However, in the tests Mikael ran after backing

Re: Problem with ssl and psql in Postgresql 13

At Thu, 24 Dec 2020 11:54:32 -0500, Tom Lane wrote in > I wrote: > > Kyotaro Horiguchi writes: > >> The attached the first patch does that. > > > +1, it seems like a clear oversight that the GSSENC patches didn't adjust > > these messages. The reason SSL state is mentioned is that it's relevan

Re: Problem with ssl and psql in Postgresql 13

I wrote: > Kyotaro Horiguchi writes: >> The attached the first patch does that. > +1, it seems like a clear oversight that the GSSENC patches didn't adjust > these messages. The reason SSL state is mentioned is that it's relevant > to which pg_hba entry gets chosen; and once we invented "hostgss

Re: Problem with ssl and psql in Postgresql 13

Kyotaro Horiguchi writes: > At Wed, 23 Dec 2020 17:34:05 -0500, Tom Lane wrote in >> However, in the tests Mikael ran after backing that pg_hba.conf entry >> off to just "host", pg_hba wouldn't have caused an authentication-stage >> failure, so it's not so clear why we'd have looped back at this

Re: Problem with ssl and psql in Postgresql 13

At Wed, 23 Dec 2020 17:34:05 -0500, Tom Lane wrote in > Oh ... wait a second. I've just managed to partially reproduce this > report. Assume that we have a working Kerberos environment on both If I kninit'ed and pg_hba.conf doesn't have a hostgssenc line and has a hostssl line, I got the follo

Re: Problem with ssl and psql in Postgresql 13

Oh ... wait a second. I've just managed to partially reproduce this report. Assume that we have a working Kerberos environment on both ends. Then libpq will establish a connection with GSS encryption and continue on with the usual PG authentication exchange. If that part fails, then this logic

Re: Problem with ssl and psql in Postgresql 13

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> In the meantime, I did spot a code path that would explain the symptoms: >> pqsecure_open_gss() clears allow_ssl_try sooner than it oughta. If >> gss_wrap_size_limit() failed for some reason, we'd abandon the GSS >> connection and

Re: Problem with ssl and psql in Postgresql 13

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> However: it is true (and undocumented, so we have at least a docs bug > >> to fix) that v12-and-later libpq will try for GSS encryption first, > >> and if it succeeds then it

Re: Problem with ssl and psql in Postgresql 13

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> However: it is true (and undocumented, so we have at least a docs bug >> to fix) that v12-and-later libpq will try for GSS encryption first, >> and if it succeeds then it will not consider using SSL, regardless of >> sslmode. So ab

Re: Problem with ssl and psql in Postgresql 13

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > I wrote: > > Gustavsson Mikael writes: > >> So if i set gssencmode=disable on my pgsql-13 to postgres 13 server > >> connection i get an SSL connection. > > > It looks like, if there is a credentials cache and gssencmode isn't > > explicitly d

Re: Problem with ssl and psql in Postgresql 13

Hi. At Thu, 17 Dec 2020 07:21:08 +, Gustavsson Mikael wrote in > But not from psql 13: > $ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver -W > Password: > psql: error: FATAL: no pg_hba.conf entry for host "nnn.nn.n.nnn", user > "kalle", database "postgres", SSL off > FATAL: no pg_hb

Re: Problem with pg_notify / listen

Gustavsson Mikael writes: > After applying the latest patch we have encountered a problem with the > pg_notify queue. What do you mean by "the latest patch", exactly? regards, tom lane

Re: Problem with pg_notify / listen

On Friday, November 27, 2020, Gustavsson Mikael wrote: > Hi. > > After applying the latest patch we have encountered a problem with the > pg_notify queue. > > The queue is filling up and starts issuing warnings like > WARNING: NOTIFY queue is 87% full > DETAIL: The server process with PID 29699

Re: Problem with compiling extensions with Postgres Version 13

On Wed, 2020-11-18 at 14:46 +0100, Eric Svenson wrote: > I am not really sure if I am in the right mailing list, but I try it here > first. > > I have written a postgres C extension as DLL which was used with Postgres 9.2 > successfully. > > Now I am trying to upgrade to Postgres 13 but I ran i

Re: Problem with psprintf and intmax_t (%jd)

Jan Behrens writes: > I'm facing a problem with psprintf and the %jd format string. I used > the following C-code: > PG_RETURN_CSTRING(psprintf("%d@%jd", (int)1, (intmax_t)2)); > While this worked fine in past, I recently get (with PostgreSQL 13): > ERROR: vsnprintf failed: Invalid argument wi

Re: Problem close curser after rollback

El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane escribió: > Matthias Apitz writes: > > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz > > Albe escribió: > >> On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote: > >>> What can I do so that

Re: Problem close curser after rollback

On Wed, Sep 30, 2020 at 09:06:13PM +0200, Matthias Apitz wrote: > Btw: In all of the other DBS (Informix, Sybase, Oracle) we could define that > point with START TRANSACTION. You can always use SET SAVEPOINT. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Re: Problem close curser after rollback

On Wednesday, 30 September 2020 20:37:23 CEST, Tom Lane wrote: Matthias Apitz writes: El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió: On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote: What can I do so that the cursor is retained despite rollback

Re: Problem close curser after rollback

Matthias Apitz writes: > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe > escribió: >> On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote: >>> What can I do so that the cursor is retained despite rollback? >> You cannot start a transaction while you are readin

  1   2   >