Re: undefined symbol: PQcancelStart
On Fri, 2025-06-20 at 13:49 +0200, Peter Röthlisberger wrote: > Hello all, > > Platform: Rocky Linux release 9.4 (Blue Onyx) > > I try to migrate from version 16.7 to 17.5. When executing pg_upgrade —check > I get the following: > > > could not load library "$libdir/dblink": ERROR: could not load library > "/opt/postgresql-17.5/lib/dblink.so": /opt/postgresql-17.5/lib/dblink.so: > undefined symbol: PQcancelStart > In database: zen_dev1 > In database: zen_prod2 > could not load library "$libdir/postgres_fdw": ERROR: could not load library > "/opt/postgresql-17.5/lib/postgres_fdw.so": > /opt/postgresql-17.5/lib/postgres_fdw.so: undefined symbol: PQcancelStart > In database: zen_dev1 > > I tried hard to find a hint on the net. No success hence my first post here. > I was able to execute the update on another server having the same OS. How did you install "dblink.so" and "postgres_fdw.so"? It looks like you copied them from the old installation. That won't work. Yours, Laurenz Albe
Re: Extension disappearing act
On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe wrote: > > On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote: > > Hi. Little mystery we don't understand. v17. > > We're stumped for now. > > So are we. Why do you keep us guessing instead of posting a reproducer? Hi. Simply because there's too much proprietary stuff, I'm afraid. And it's likely some stupid mistakes on our part anyway. That I can't see... Still, the fact I see nothing extension-related in the libpq trace is intriguing, isn't it?
Re: Extension disappearing act
On Thu, Jun 19, 2025 at 8:09 AM Dominique Devienne wrote: > Hi. Little mystery we don't understand. v17. > > Create new DB, owned by dedicated new ROLE. > Create extension (pgcrypto) in our case. Installed in public, owned by > DB owner role. > Create schemas and populate them inside the DB. > This also creates roles associated to those schemas. > One of the schema is owned by the DB owner (in case that matters). > Creates functions using pgcrypto, in some of those schemas. > Drop all schemas (and associated roles), thus pgcrypto-using functins are > gone. > Of course, the DB owner role was not dropped. Can't in fact. > Somehow, the pgcrypto extension has disappeared, as side-effects of the > drops. > We did a LIBPQ trace of the command to does all the drops, > Suggestion: 1. Turn on statement logging to 'all'. Make sure times are logged 2. Install a trace. this could be as simple as: select now(), count(*) FILTER (WHERE extname = 'pgcrypto') from pg_extension ; \watch ...in psql 3. that should nail the time of the drop. at that time, you can then find the offending statement merlin
Re: Extension disappearing act
On 6/19/25 07:54, Dominique Devienne wrote: On Thu, Jun 19, 2025 at 4:18 PM Adrian Klaver wrote: On 6/19/25 06:09, Dominique Devienne wrote: Hi. Little mystery we don't understand. v17. Does 'all' include the public schema? No. We don't touch `public` at all, beside pgcrypto ending up inside it. Of course, the DB owner role was not dropped. Can't in fact. Somehow, the pgcrypto extension has disappeared, as side-effects of the drops. Was it in fact installed in the public schema? Sure was. It's my own command, but good enough. --DD D:\>ppg -c my17 -d dd_v168a database_ --extensions Connected OK (postgresql://ddevienne@localhost:5417/dd_v168a) === | Name | Version | Owner | Schema | === | pgcrypto | 1.3 | "Acme-DBA:000ik2" | public | | plpgsql | 1.0 | postgres | pg_catalog | === 2 installed extensions (out of 61) Which runs SELECT extname, extversion, extowner::regrole::text as owner, extnamespace::regnamespace::text as "schema" FROM pg_extension ORDER BY 1 After you run the DROP commands the above query does not return pgcrypto, correct? -- Adrian Klaver adrian.kla...@aklaver.com
Re: undefined symbol: PQcancelStart
Hello Laurenz, thanks for your quick reply. The issue was having the old postgres library in LD_LIBRARY_PATH. Once I removed it, everything started to look good! In all the past years this was not a problem though. But anyway: All good now! Thanks again: peter > On 20 Jun 2025, at 14:00, Laurenz Albe wrote: > > On Fri, 2025-06-20 at 13:49 +0200, Peter Röthlisberger wrote: >> Hello all, >> >> Platform: Rocky Linux release 9.4 (Blue Onyx) >> >> I try to migrate from version 16.7 to 17.5. When executing pg_upgrade —check >> I get the following: >> >> >> could not load library "$libdir/dblink": ERROR: could not load library >> "/opt/postgresql-17.5/lib/dblink.so": /opt/postgresql-17.5/lib/dblink.so: >> undefined symbol: PQcancelStart >> In database: zen_dev1 >> In database: zen_prod2 >> could not load library "$libdir/postgres_fdw": ERROR: could not load >> library "/opt/postgresql-17.5/lib/postgres_fdw.so": >> /opt/postgresql-17.5/lib/postgres_fdw.so: undefined symbol: PQcancelStart >> In database: zen_dev1 >> >> I tried hard to find a hint on the net. No success hence my first post >> here. I was able to execute the update on another server having the same OS. > > How did you install "dblink.so" and "postgres_fdw.so"? > It looks like you copied them from the old installation. That won't work. > > Yours, > Laurenz Albe Peter Roethlisberger Senior System & Database Architect Zen Innovations AG Bernstrasse 99 3122 Kehrsatz Switzerland 46°54’57” N 007°28’03” E www.zen-innovations.com Phone: +41 (0)31 550 07 65 Mobile: +41 (0)79 785 79 35 D-U-N-S® Number: 48-405-0989 peter.roethlisber...@zen-innovations.com
undefined symbol: PQcancelStart
Hello all, Platform: Rocky Linux release 9.4 (Blue Onyx) I try to migrate from version 16.7 to 17.5. When executing pg_upgrade —check I get the following: could not load library "$libdir/dblink": ERROR: could not load library "/opt/postgresql-17.5/lib/dblink.so": /opt/postgresql-17.5/lib/dblink.so: undefined symbol: PQcancelStart In database: zen_dev1 In database: zen_prod2 could not load library "$libdir/postgres_fdw": ERROR: could not load library "/opt/postgresql-17.5/lib/postgres_fdw.so": /opt/postgresql-17.5/lib/postgres_fdw.so: undefined symbol: PQcancelStart In database: zen_dev1 I tried hard to find a hint on the net. No success hence my first post here. I was able to execute the update on another server having the same OS. Any help is highly appreciated Thanks: peter
Re: Convert JSON value back to postgres representation
Good to know. Thank you!
On Fri, Jun 20, 2025 at 7:17 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thursday, June 19, 2025, Laurenz Albe wrote:
>
>> On Thu, 2025-06-19 at 23:05 +0200, Phillip Diffley wrote:
>> > Postgres has a to_jsonb function that will convert a value into its
>> jsonb representation.
>> > I am now trying to turn a json value back into its postgres type. I was
>> hoping there would
>> > be something like a from_jsonb function that, along with a type hint,
>> could be used as an
>> > inverse of to_jsonb, like
>> >
>> > from_jsonb(to_jsonb('{1,2,3}'::int[]) as int[]
>> >
>> > but I do not see a function like this. I was able to convert a json
>> value back to its
>> > postgres representation using the jsonb_to_record function, as used in
>> the WHERE expression
>> > below, but I feel like there might be a better way to do this.
>> >
>> > CREATE TABLE mytable (id int, col1 int[]);
>> > INSERT INTO mytable VALUES (1, '{1, 2, 3}'), (2, '{3, 4, 5}');
>> > SELECT * from mytable WHERE col1 = (select col1 from
>> json_to_record('{"col1": [1, 2, 3]}'::JSON) as x(col1 int[]));
>> >
>> > Is there a preferred method for turning a JSON value back to its
>> postgres representation?
>>
>> I think jsonb_populate_record() is the closest thing to what you envision.
>>
>
> jsonb_to_record avoids the temporary type.
>
> select * from jsonb_to_record('{"ia":[1,2,3]}'::jsonb) as r (ia integer[]);
>
> There is a gap for arrays. Scalars you can just cast and composites have
> these functions. But no simple/direct way to go from json array to sql
> array is presently implemented.
>
> Though since 17 json_query can apparently do it.
>
> select pg_typeof( json_query('[1,2,3]'::jsonb, '$' returning integer[]) )
> -> integer[]
>
>
> David J.
>
>
Re: Extension disappearing act
On 6/20/25 09:35, Dominique Devienne wrote: > On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe wrote: >> >> On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote: >>> Hi. Little mystery we don't understand. v17. >>> We're stumped for now. >> >> So are we. Why do you keep us guessing instead of posting a reproducer? > > Hi. Simply because there's too much proprietary stuff, I'm afraid. > And it's likely some stupid mistakes on our part anyway. That I can't see... > Still, the fact I see nothing extension-related in the libpq trace is > intriguing, isn't it? > PQtrace logs client-server communication. I would not expect it to say anything about actions that happen on the server, like for example automatically dropping objects in a schema, after the schema is dropped. I think the best way to move this forward is sharing a reproducer. If you have too much proprietary stuff, you'll have to remove those bits, or rather replace them with something you can share. In fact, a reproducer is meant to be "minimal" - the smallest example causing the issue. So creating reproducers generally means simplifying the example as much as possible anyway. And I wouldn't be surprised if in the process of doing that you find the answer yourself. regards -- Tomas Vondra
Re: Retrieving current date
Thanks all, I just changed the timezone setting in postgresql.conf file. That resolved this issue. On Thursday 19 June, 2025 at 07:41:16 pm IST, Adrian Klaver wrote: On 6/18/25 23:23, sivapostg...@yahoo.com wrote: > Hello, > May be a very basic question. We all here are new to Linux / PostgreSQL > > Ubuntu Server 22.04 > PostgreSQL 15 > PgAdmin4 6.16 > > When I run the following query in pg_admin > > Select today_now > From (Select localtimestamp(0) as today_now) a; > > I get UTC time and not the IST time, which I expect. > > How to change the setting(s), if any, to retrieve the current date and > time in IST? For setting see: https://www.postgresql.org/docs/current/runtime-config-client.html "TimeZone (string) Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information. " For a quick fix, maybe?: select localtimestamp(0) at time zone 'Asia/Kolkata' as today_now; > > Happiness Always > BKR Sivaprakash > -- Adrian Klaver adrian.kla...@aklaver.com
