Re: Emitting JSON to file using COPY TO

This would be a very special case for COPY. It applies only to a single column of JSON values. The original problem can be solved with psql --tuples-only as David wrote earlier. $ psql -tc 'select json_agg(row_to_json(t)) from (select * from public.tbl_json_test) t;' [{"id":1,"t

Re: Installing extension temporal_tables for PG13

On 11/27/23 18:18, H wrote: On November 27, 2023 8:48:35 PM GMT-05:00, Adrian Klaver wrote: On 11/27/23 17:41, H wrote: On 11/27/2023 08:38 PM, Adrian Klaver wrote: On 11/27/23 17:17, H wrote: I don't use PGXN so I am flying blind here. You may need to do: sudo pgxn install temporal_tabl

Re: Installing extension temporal_tables for PG13

On November 27, 2023 8:48:35 PM GMT-05:00, Adrian Klaver wrote: >On 11/27/23 17:41, H wrote: >> On 11/27/2023 08:38 PM, Adrian Klaver wrote: >>> On 11/27/23 17:17, H wrote: > I have both make and gmake installed on the system but the issue >seems to be that the file pgxs.mk does not exist? I

Re: Installing extension temporal_tables for PG13

On 11/27/23 17:41, H wrote: On 11/27/2023 08:38 PM, Adrian Klaver wrote: On 11/27/23 17:17, H wrote: I have both make and gmake installed on the system but the issue seems to be that the file pgxs.mk does not exist? In fact, I can see that /usr/pgsql-13/lib/pgxs directory does not exist. S

Re: Installing extension temporal_tables for PG13

On 11/27/2023 08:38 PM, Adrian Klaver wrote: > On 11/27/23 17:17, H wrote: >> On 11/27/2023 07:26 PM, Adrian Klaver wrote: >>> On 11/27/23 16:18, H wrote: >>> The link to the pgxn client on the arkhipov's page leads to a non-functioning website: pgxnclient.projects.pgfoundry.org. >>

Re: Installing extension temporal_tables for PG13

On 11/27/23 17:17, H wrote: On 11/27/2023 07:26 PM, Adrian Klaver wrote: On 11/27/23 16:18, H wrote: The link to the pgxn client on the arkhipov's page leads to a non-functioning website: pgxnclient.projects.pgfoundry.org. Does anyone if the pgxn client can be found somewhere else? Or: h

Re: Installing extension temporal_tables for PG13

On 11/27/2023 07:26 PM, Adrian Klaver wrote: > On 11/27/23 16:18, H wrote: > >> The link to the pgxn client on the arkhipov's page leads to a >> non-functioning website: pgxnclient.projects.pgfoundry.org. >> >> Does anyone if the pgxn client can be found somewhere else? >> > > Or: > > https://pgxn

Re: IPV6 issue

On 11/27/23 16:58, Atul Kumar wrote: I Don't know how postgres was installed, 1) Someone installed it. Ask around on where it came from. 2) Query the package manager to see if it was installed that way? How do I check if I have more than one version of psql installed ? A quick and dirty

Re: IPV6 issue

I Don't know how postgres was installed, How do I check if I have more than one version of psql installed ? Regards. On Tue, Nov 28, 2023 at 6:26 AM Adrian Klaver wrote: > On 11/27/23 16:42, Atul Kumar wrote: > > Hi, > > > > unix_socket_directories is set to default i.e. /tmp and I could see

Re: IPV6 issue

On 11/27/23 16:42, Atul Kumar wrote: Hi, unix_socket_directories is set to default i.e. /tmp and I could see the socket in /tmp directory. You have not answered: How did you install Postgres? Do you have more then one version of psql installed? Though I am pretty sure I know the ans

Re: IPV6 issue

Hi, unix_socket_directories is set to default i.e. /tmp and I could see the socket in /tmp directory. Regards. On Tue, Nov 28, 2023 at 2:11 AM Tom Lane wrote: > Adrian Klaver writes: > > On 11/27/23 12:11, Atul Kumar wrote: > >> I found that localhost was set to .bash_profile and when I r

Re: IPV6 issue

listen_address is set to '*', version= psql (12.15, server 12.15.19) Regards. On Tue, Nov 28, 2023 at 2:01 AM Adrian Klaver wrote: > On 11/27/23 12:11, Atul Kumar wrote: > > Hi, > > > > I found that localhost was set to .bash_profile and when I removed it > > and then re-attempted to connecte

Re: malloc errors in psql with libpq 16.1 on Mac

Don Seiler writes: > On Mon, Nov 27, 2023 at 6:02 PM Tom Lane wrote: >> Is OpenSSL in use? If so, what version? I'm wondering if this is a >> variant of the OpenSSL 3.2.0 compatibility problem we just heard of: > My colleague does confirm it is OpenSSL 3.2.0: > ==> openssl@3: stable 3.2.0 (bot

Re: malloc errors in psql with libpq 16.1 on Mac

On Mon, Nov 27, 2023 at 6:02 PM Tom Lane wrote: > > Is OpenSSL in use? If so, what version? I'm wondering if this is a > variant of the OpenSSL 3.2.0 compatibility problem we just heard of: > My colleague does confirm it is OpenSSL 3.2.0: ==> openssl@3: stable 3.2.0 (bottled) Does this mean

Re: Installing extension temporal_tables for PG13

On 11/27/23 16:18, H wrote: The link to the pgxn client on the arkhipov's page leads to a non-functioning website: pgxnclient.projects.pgfoundry.org. Does anyone if the pgxn client can be found somewhere else? Or: https://pgxn.github.io/pgxnclient/ -- Adrian Klaver adrian.kla...@aklaver.c

Re: Installing extension temporal_tables for PG13

On 11/27/23 16:18, H wrote: On November 27, 2023 6:58:40 PM GMT-05:00, Adrian Klaver wrote: The link to the pgxn client on the arkhipov's page leads to a non-functioning website: pgxnclient.projects.pgfoundry.org. Does anyone if the pgxn client can be found somewhere else? https://

Re: Installing extension temporal_tables for PG13

On November 27, 2023 6:58:40 PM GMT-05:00, Adrian Klaver wrote: >On 11/27/23 15:37, H wrote: >> Running Centos7 and PG13. I'd like to install the temporal_tables >extension but it does not seem to exist in postgresql13-contrib. > >If it is not here: > >https://www.postgresql.org/docs/13/contrib.h

Re: malloc errors in psql with libpq 16.1 on Mac

Don Seiler writes: > I have a colleague trying to use psql to connect to a remote DB, but he's > getting this error: > FATAL: no PostgreSQL user name specified in startup packet > psql(42705,0x7ff84a07b700) malloc: *** error for object 0xa6: pointer being > freed was not allocated > psql(42705,0x7

Re: Installing extension temporal_tables for PG13

On 11/27/23 15:37, H wrote: Running Centos7 and PG13. I'd like to install the temporal_tables extension but it does not seem to exist in postgresql13-contrib. If it is not here: https://www.postgresql.org/docs/13/contrib.html then it won't be in postgresql13-contrib. I did find the source

malloc errors in psql with libpq 16.1 on Mac

I have a colleague trying to use psql to connect to a remote DB, but he's getting this error: FATAL: no PostgreSQL user name specified in startup packet psql(42705,0x7ff84a07b700) malloc: *** error for object 0xa6: pointer being freed was not allocated psql(42705,0x7ff84a07b700) malloc: *** set a

Installing extension temporal_tables for PG13

Running Centos7 and PG13. I'd like to install the temporal_tables extension but it does not seem to exist in postgresql13-contrib. I did find the source code at https://github.com/arkhipov3/temporal_tables which suggests to use the pgxn client to install it. The link to this client found on thi

Dynamically generate a nested json

Hi, I want to dynamically generate a nested json file. I have written a function for it in PL/PGSQL that accepts 3 arrays. First one is an array of all json fields, second one is an array of all json fields with columns from tables present in db, third one mentions the type for all the fields insi

Re: suppress notices from inside a stored a plpgqsl function

On 11/27/23 12:51, David Gauthier wrote: Hi: I have a plpgsql function that has this...     drop table if exists tmp_diff_blkviews; Even with the "if exists", I still get... NOTICE:  table "tmp_diff_blkviews" does not exist, skipping CONTEXT:  SQL statement "drop table if exists tmp_diff_blk

suppress notices from inside a stored a plpgqsl function

Hi: I have a plpgsql function that has this... drop table if exists tmp_diff_blkviews; Even with the "if exists", I still get... NOTICE: table "tmp_diff_blkviews" does not exist, skipping CONTEXT: SQL statement "drop table if exists tmp_diff_blkviews" PL/pgSQL function dvm.blkview_diffs(c

Re: IPV6 issue

Adrian Klaver writes: > On 11/27/23 12:11, Atul Kumar wrote: >> I found that localhost was set to .bash_profile and when I removed it >> and then re-attempted to connected the database using "psql postgres", I >> got this new error: >> >> psql postgres -p 5432 >> psql: error: could not connect

Re: IPV6 issue

On 11/27/23 12:11, Atul Kumar wrote: Hi, I found that localhost was set to .bash_profile and when I removed it and then re-attempted to connected the database using "psql postgres", I got this new error: psql postgres -p 5432 psql: error: could not connect to server: No such file or director

Re: IPV6 issue

Hi, I found that localhost was set to .bash_profile and when I removed it and then re-attempted to connected the database using "psql postgres", I got this new error: psql postgres -p 5432 psql: error: could not connect to server: No such file or directory Is the server running locally an

Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

On Mon, 2023-11-27 at 11:50 +, Sri Mrudula Attili wrote: > ERROR:  could not access status of transaction 16087052 > DETAIL:  Could not read from file "pg_subtrans/00F5" at offset 122880: > Success. > STATEMENT:  SELECT distinct  That's data corruption. Time to restore your backup. Investiga

Re: Ways to aggregate table info from variable number of schemas

> On Nov 27, 2023, at 10:16, Dominique Devienne wrote: > Which means you can't do a declarative SQL query for those > metadata across projects, since you can't do static / non-dynamic SQL across > schemas. I'm not sure I understand this. Schemas are just namespaces, and all queries have acc

Ways to aggregate table info from variable number of schemas

Our PostgreSQL-based system uses a small number of fixed schemas, and an arbitrary number of per-project schemas (one schema per project). This will is given, sorry, cannot be changed. Really. The fixed schemas contain metadata about the projects (including which schema a given project uses). My

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 5:04 PM Adrian Klaver wrote: > On 11/27/23 01:44, Dominique Devienne wrote: > > On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne > > wrote: > > On second thought, I guess that's COPY in its text modes doing the > escaping? > > Interesting. T

Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

On 11/27/23 03:50, Sri Mrudula Attili wrote: Hello, Post refresh of a Delphix VDB , while trying to connect from application users are getting the below error. Could also see these errors in postgresql.log Error Message: < 2023-11-24 12:24:10.031 GMT >ERROR:  could not access status of tra

Re: Emitting JSON to file using COPY TO

On 11/27/23 01:44, Dominique Devienne wrote: On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne > wrote: On second thought, I guess that's COPY in its text modes doing the escaping? Interesting. The text-based modes of COPY are configurable. There's even a JSON m

Re: Parallel Index Scan Implementation

On Mon, Nov 27, 2023 at 4:11 AM Brajendra Pratap Singh < singh.bpratap...@gmail.com> wrote: > Hi Postgresql Experts, > > We have a basic requirement where we need to implement the parallel index > scan instead of parallel seq scan at table level. Please suggest the best > way to do this. > > Postg

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 3:56 PM Tom Lane wrote: > "David G. Johnston" writes: > > I agree there should be a copy option for “not formatted” so if you dump > a > > single column result in that format you get the raw unescaped contents of > > the column. > > I'm not sure I even buy that. JSON dat

Re: PostgreSql: Canceled on conflict out to old pivot

On Mon, Nov 27, 2023 at 2:17 AM Wirch, Eduard wrote: > Hi > > We have a PostgreSql 15 server serving around 30 databases, one schema > each with the same layout. Each database is used by one application > instance. The application consistently uses transactions with isolation > level serializable

Re: Emitting JSON to file using COPY TO

"David G. Johnston" writes: > I agree there should be a copy option for “not formatted” so if you dump a > single column result in that format you get the raw unescaped contents of > the column. I'm not sure I even buy that. JSON data in particular is typically multi-line, so how will you know w

Re: Removing oids with pg_repack

On Wednesday, November 22, 2023 at 12:38:54 PM EST, Achilleas Mantzios wrote: Στις 22/11/23 15:14, ο/η CG έγραψε: On Wednesday, November 22, 2023 at 01:20:18 AM EST, Achilleas Mantzios wrote: Στις 21/11/23 20:41, ο/η CG έγραψε: I have a very large

Re: Emitting JSON to file using COPY TO

On Monday, November 27, 2023, Pavel Stehule wrote: > Hi > > po 27. 11. 2023 v 14:27 odesílatel David G. Johnston < > david.g.johns...@gmail.com> napsal: > >> On Monday, November 27, 2023, Dominique Devienne >> wrote: >> >>> There's even a JSON mode. >>> By miracle, would the JSON output mode rec

Re: Emitting JSON to file using COPY TO

Hi po 27. 11. 2023 v 14:27 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Monday, November 27, 2023, Dominique Devienne > wrote: > >> There's even a JSON mode. >> By miracle, would the JSON output mode recognize JSON[B] values, and >> avoid the escaping? >> > > I agree t

Re: Emitting JSON to file using COPY TO

On Monday, November 27, 2023, Dominique Devienne wrote: > There's even a JSON mode. > By miracle, would the JSON output mode recognize JSON[B] values, and avoid > the escaping? > I agree there should be a copy option for “not formatted” so if you dump a single column result in that format you ge

Re:Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

Hello, Post refresh of a Delphix VDB , while trying to connect from application users are getting the below error. Could also see these errors in postgresql.log Error Message: < 2023-11-24 12:24:10.031 GMT >ERROR:  could not access status of transaction 16087052 < 2023-11-24 12:24:10.031 GM

Re: Emitting JSON to file using COPY TO

On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne wrote: > On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver > wrote: > >> On 11/25/23 11:21, Davin Shearer wrote: >> > Hello! >> > >> > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY >> > TO, but I'm running into problems w

Re: Emitting JSON to file using COPY TO

On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver wrote: > On 11/25/23 11:21, Davin Shearer wrote: > > Hello! > > > > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY > > TO, but I'm running into problems with COPY TO double quoting the > > output. Here is a minimal example

Re: How to eliminate extra "NOT EXISTS"-query here?

On Sat, Nov 25, 2023 at 5:53 PM hector vass wrote: > Not sure you need to use array why not simple table joins, so a table with > your criteria x y z t joined to stuff to give you candidates that do match, > then left join with coalesce to add the 'd' > > select > > --a.id,b.test_id, > > coalesce

Parallel Index Scan Implementation

Hi Postgresql Experts, We have a basic requirement where we need to implement the parallel index scan instead of parallel seq scan at table level. Please suggest the best way to do this. Postgresql DB Version : 12.6 Table size : 2-5 GB OS : Centos-7 RAM : 96 GB CPU : 32 shared_buffer=20GB Eff