Re: Cannot pg_dump_all anymore...

On 3/18/2025 3:59 AM, Ron Johnson wrote: On Tue, Mar 18, 2025 at 5:14 AM E-BLOKOS wrote: I got this error: pg_dump: error: query returned 0 rows instead of one: EXECUTE dumpBaseType('794978') any clue to solve it? PG version? PG 17.4 Whole command line, inc

Re: Cannot pg_dump_all anymore...

imit 3;   oid   |  typname   | typtype | typnamespace ++-+--  794970 | log_17167  | c   | repack  794969 | _log_17167 | b   | repack  794966 | pk_17167   | c   | repack (3 rows) Arggh it's repack 🙁 I have no clue how to repair repack -- E-BLOKOS

Re: Cannot pg_dump_all anymore...

Hi, On 3/19/2025 7:08 AM, Greg Sabino Mullane wrote: On Wed, Mar 19, 2025 at 10:02 AM E-BLOKOS wrote: is it possible a crash happened with a VACUUM and a machine reboot in same time? More likely to be a problem with pg_repack.  Please tell us the exact versions of pg_repack and

Re: Cannot pg_dump_all anymore...

ixed it with: SELECT * FROM pg_depend WHERE objid IN (794964, 794968); DELETE FROM pg_depend WHERE objid IN (794964, 794968); systemctl restart postgresql is it possible a crash happened with a VACUUM and a machine reboot in same time? -- E-BLOKOS

Cannot pg_dump_all anymore...

I got this error: pg_dump: error: query returned 0 rows instead of one: EXECUTE dumpBaseType('794978') any clue to solve it? thanks David

Re: [EXT]Re: running \copy through perl dbi ?

On Dec 10, 2023, at 10:41 AM, Vincent Veyron mailto:vv.li...@wanadoo.fr>> wrote: External Email On Fri, 8 Dec 2023 10:45:28 -0500 David Gauthier mailto:dfgpostg...@gmail.com>> wrote: I'm trying to run a PG client side "\copy" command from a perl script. I tried using $dbh->do("\\copy ...") bu

Connection fails on one system in a address range allowed to connect

I am migrating an existing web application from Oracle to postgres and I’m testing the connectivity. Trying to run a test program (that works on another system in the same subnet!) I get this error: Error system: [root@dhbroomscheduling4 ~]# ./pg_test.pl DBI connect('dbname=webdata;host=dhbp

Re: [EXT]Re: Strange error trying to import with Ora2PG

On Oct 4, 2023, at 8:38 AM, Adrian Klaver wrote: If you search the source file directory for "reserver_visible_restriction" is there another file that contains it? Sigh…that was it…Thank you. There are two ‘views’ directories, one under ’sources/views’ and the other under /schema/views.

Re: [EXT]Re: Strange error trying to import with Ora2PG

22:02 RESOURCEAFFIL_WITHCODE_view.sql -rw-rw-r-- 1 johnson johnson 535 Sep 27 22:02 UA_COLLEGE_LOOKUP_view.sql On Oct 3, 2023, at 2:27 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: External Email On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote: I am trying to import an oracle schema

Strange error trying to import with Ora2PG

I am trying to import an oracle schema with ora2pg and running into an odd error when the import_all.sh script gets to a specific view. The error is: psql:schema/views/RESERVER_VISIBLE_RESTRICTION_view.sql:10: ERROR: operator does not exist: character = integer LINE 1: ...r.resource_id = rg.re

Re: [EXT]Re: Accessing system information functions

Thank you. On Sep 25, 2023, at 4:18 AM, Erik Wienhold wrote: External Email On 2023-09-22 17:37 +, Johnson, Bruce E - (bjohnson) wrote: postgres=# pg_database_size(avi); ERROR: syntax error at or near "pg_database_size" LINE 1: pg_database_size(avi); Call functions with S

Accessing system information functions

Environment: Ubuntu 22.04 lts, postgres 15 installed via postgres.org repository I am attempting to use the system information functions here: https://www.postgresql.org/docs/15/functions-info.html I’m logged on as the postgres superuser on the host Trying any of the funct

Connection not allowed because of an error 'Not in pg_hba.conf'

I am doing some testing trying to migrate some websites from using Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism) (Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux using the supplied PostgreSQL DBI and DBD::Pg packages) The error I am gett

Granting privileges to a schema to a role

(Background I’m working on migrating an existing set of Oracle schemas with a bunch of inter-schema grants to a Postgres 15 system) I’ve created the database ‘webdata', successfully used ora2pg to migrate one schema ‘trav’ to Postgres. The schema on the oracle side is called trav the owner is w

listening on an address other than 'localhost'?

per the documentation to listen for client connections on a given IP address you set listen_addresses to a commaspearated list of IP addresses in postgresql.conf That line is commented out in the default conf file, when I uncomment it, and add the address, then do sudo systemctl restart postgre

Strategy for migrating from Oracle to PG

I have an oracle database with a bunch of schemas that are the data sources for bunch of web applications; what used to be called an ‘intranet’. I’ve installed pg2sql, and it’s working, and what I want to do is pretty much copy what I existing now as closely as possible. I've done ora2pg —proje

Re: Counting the number of repeated phrases in a column

On 26.01.2022 11:11, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko mailto:i.panche...@postgrespro.ru>> wrote: On 26.01.2022 00:21, benj@laposte.net wrote: > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : >> There is a sh

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

th 2B recs > >On 2021-12-10 18:04:07 +, Godfrin, Philippe E wrote: >> >But in my experience the biggest problem with large tables are unstable >> >execution plans - for most of the parameters the optimizer will choose >> >to use an index, but for some it will err

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

On 2021-12-08 14:44:47 -0500, David Gauthier wrote: > So far, the tables I have in my DB have relatively low numbers of records > (most > are < 10K, all are < 10M). Things have been running great in terms of > performance. But a project is being brainstormed which may require some > tables > t

RE: [EXTERNAL] Re: Inserts and bad performance

Right you are sir! I figured that out a few hours ago! pg From: Ron Sent: Wednesday, November 24, 2021 10:58 PM To: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance On 11/24/21 1:15 PM, Godfrin, Philippe E wrote: [snip] I dropped the unique index , rebuilt

RE: [EXTERNAL] Re: Inserts and bad performance

like to know what separates COPY from bulk inserts… pf From: Gavin Roy Sent: Wednesday, November 24, 2021 1:50 PM To: Godfrin, Philippe E Cc: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E

RE: [EXTERNAL] Re: Inserts and bad performance

xplain >(analyze, buffers, verbose) and then rollback? Yes, I'm looking into that pg -Original Message- From: David Rowley Sent: Wednesday, November 24, 2021 7:13 PM To: Godfrin, Philippe E Cc: Tom Lane ; pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Re: Inserts a

RE: [EXTERNAL] Re: Inserts and bad performance

The notion of COPY blocks and asynchronously is very interesting From: Gavin Roy Sent: Wednesday, November 24, 2021 1:50 PM To: Godfrin, Philippe E Cc: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E

RE: [EXTERNAL] Re: Inserts and bad performance

a certain number of records, the speed just dropped off. pg From: Tom Lane Sent: Wednesday, November 24, 2021 1:32 PM To: Godfrin, Philippe E Cc: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance "Godfrin, Philippe E" mailto:philippe.godf...@n

RE: [EXTERNAL] Re: Inserts and bad performance

all Sent: Wednesday, November 24, 2021 1:20 PM To: Godfrin, Philippe E Cc: pgsql-general@lists.postgresql.org Subject: [EXTERNAL] Re: Inserts and bad performance On Wed, Nov 24, 2021 at 07:15:31PM +, Godfrin, Philippe E wrote: > Greetings > I am inserting a large number of rows, 5,10,

Inserts and bad performance

| Database Administration NOV NOV US | Engineering Data 9720 Beechnut St | Houston, Texas 77036 M 281.825.2311 E philippe.godf...@nov.com<mailto:philippe.godf...@nov.com>

Re: Mongo FDW For Postgres

Hi Team, Can i get proper documentation for Mongodb FDW extension installation For Postgres ? Will it support AWS RDS and Aurora? Please advise. -- Regards, Dhivya E Data Engineer Heptagon Technologies Pvt Ltd Website: https://heptagon.in <http://heptagon.in/> Email:

Re: Incremental backup

On 28.10.2021 15:58, Ron wrote: On 10/28/21 7:23 AM, Edu Gargiulo wrote: Hi all, Is there any way to make incremental backups in postgres-12? Using pg_dump?  No. Using pgBackRest?  Yes. https://github.com/postgrespro/pg_probackup --

Re: ZFS filesystem - supported ?

d Oracle are mostly maintaining XFS updates, and I didn't see anything saying it's not mainained actively, especially when they offering many solutions with XFS as default -- E-BLOKOS

Re: ZFS filesystem - supported ?

the legacy issues associated with xfs are long gone ? XFS is indeed for me  the most stable and performant for postgresql today. EXT4 was good too, but less performant. -- E-BLOKOS

Re: ZFS filesystem - supported ?

ime of the snapshot in order to reach consistency). In the end though, really, it's much, much, much better to use a proper backup and archiving tool that's written specifically for PG than to try and roll your own, using snapshots or not. Thanks, Stephen what about BTRFS since it's the successor of ZFS? -- E-BLOKOS

Partitioning with FDW and table size limits

or example, provided I had the disk space, with 10 partitioned tables, I could have 320TB of data on 1 or more remote servers. Am I correct? Thanks, phil Phil Godfrin | Database Administrator NOV NOV US | Engineering Data 9720 Beechnut St | Houston, Texas 77036 M 281.825.2311 E philippe.godf.

RE: [EXTERNAL] Re: Numeric data types

Very well, thanks very much. pg From: Bruce Momjian Sent: Monday, August 31, 2020 5:31 PM To: Godfrin, Philippe E Cc: pgsql-gene...@postgresql.org Subject: Re: [EXTERNAL] Re: Numeric data types On Mon, Aug 31, 2020 at 10:20:51PM +, Godfrin, Philippe E wrote: > Frankly, I’m not certain

RE: [EXTERNAL] Re: Numeric data types

Frankly, I’m not certain, I believe the developers are using a messaging intermediary. pg From: Bruce Momjian Sent: Monday, August 31, 2020 5:19 PM To: Godfrin, Philippe E Cc: pgsql-gene...@postgresql.org Subject: Re: [EXTERNAL] Re: Numeric data types On Mon, Aug 31, 2020 at 10:14:48PM +

RE: [EXTERNAL] Re: Numeric data types

Fabulous, thanks much. From: Bruce Momjian Sent: Monday, August 31, 2020 4:56 PM To: Godfrin, Philippe E Cc: pgsql-gene...@postgresql.org Subject: Re: [EXTERNAL] Re: Numeric data types On Mon, Aug 31, 2020 at 05:32:23PM +, Godfrin, Philippe E wrote: > Thanks Bruce, I suppose you mean

RE: [EXTERNAL] Re: Numeric data types

Thanks Bruce, I suppose you mean n32 -> n64 OK, but n64->n32 chance of 32-bit overflow... pg From: Bruce Momjian Sent: Monday, August 31, 2020 11:54 AM To: Godfrin, Philippe E Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: Numeric data types On Mon, Aug 31, 2020 at 04:38:05PM

Numeric data types

difference? Thanks, Pg Phil Godfrin | Database Administrator NOV NOV US | Engineering Data 9720 Beechnut St | Houston, Texas 77036 M 281.825.2311 E philippe.godf...@nov.com The information contained in this transmission is for the personal and confidential use of the individual or entity to

Re: How to do phrase search?

Hello, On 10.07.2020 21:22, Anto Aravinth wrote: Hello, I have the following table: so2, which has following column details: ​ |id, title, posts, body (tsvector). | And I created the index on the following: |"so2_pkey" PRIMARY KEY, btree (id)| |"body" gin (body)| ​ And I wanted to query

Re: PostgreSQL 13: native JavaScript Procedural Language support ?

On 26.03.2020 03:50, Bruce Momjian wrote: On Wed, Mar 25, 2020 at 05:46:27PM +0200, Marius Andreiana wrote: Thanks Tom, that makes sense. Appreciate your time to explain the context. I'll followup with Heroku. Also, I have heard PL/V8 is very hard to build for packagers (because of changes b

BDR: moving a node

Hello, As a preamble, I'm running PostgreSQL 9.4 BDR on a Debian 9. I'm moving a node to a new host. This is the node from which I started BDR and had a second replicate join it. I browsed the list archive, Googled all I could, found some threads and Github issues, but came up with a grant t

Re: Extract elements from JSON array and return them as concatenated string

14.03.2018 20:02, Alexander Farber пишет: Thank you, Ivan! I am trying to apply your suggestion to my table - On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko mailto:i.panche...@postgrespro.ru>> wrote: Hi Alex, SELECT  string_agg(x->>'letter','

Re: Extract elements from JSON array and return them as concatenated string

Hi Alex, SELECT  string_agg(x->>'letter','') FROM json_array_elements( '[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]'::json ) x; Rega

Re: How to Optimize pg_trgm Performance

Hi Igal, 29.01.2018 02:42, Igal @ Lucee.org пишет: I want to use pg_trgm for auto-suggest functionality.  I created a Materialized View with the information that I need, with the relevant columns being (keywords text, rank int). keywords is the column from which I build the tri-grams, and ra

Re: Slow alter sequence with PG10.1

I also confirm this problem: Running Michael's script on 10.1 takes 314 seconds instead of 2.3 seconds on 9.6.5. Moreover adding some timing shows that on 10.1 the iteration execution time grows linearly with each iteration. (!!) If we remove ALTER SEQUENCE, the difference is only 2.5 times

Re: Mailing list archiver

Hi, Btw, there is also another web interface to the mail list archives at Postgres Pro web site, https://postgrespro.com/list Regards, Ivan  03.01.2018 05:45, Jordan Deitch пишет: This project serves to organize and archive the postgres mailing list: http://pg.rsa.pub/ Let me know what yo

Re: Selecting a JSON object of arrays from a PostgreSQL table

Hi Alex! Why not: select json_object_agg(gid, y) from     ( select gid, jsonb_agg(row_to_json(chat)) y from chat group by gid     ) x; Regards, Ivan 01.01.2018 16:41, Alexander Farber пишет: Hello and happy new year! I have prepared a simple SQL Fiddle demonstrat

Re: jsonb and where clause?

28.11.2017 13:25, Bjorn T Johansen пишет: On Tue, 28 Nov 2017 11:28:55 +0300 "Ivan E. Panchenko" wrote: Hi Bjorn, 28.11.2017 11:18, Bjorn T Johansen пишет: Hi. Just starting to look at how to use jsonb columns and I have a question. I have found out that I can use the following

Re: jsonb and where clause?

Hi Bjorn, 28.11.2017 11:18, Bjorn T Johansen пишет: Hi. Just starting to look at how to use jsonb columns and I have a question. I have found out that I can use the following to search for a value inside the jsonb column: select * from orders where info ->> 'customer' = 'John Doe'(where i

Re: update field in jsonb

23.11.2017 04:45, support-tiger пишет: is there a way to update a single field in jsonb without replacing the entire json document - couldn't find an example for example create table test (id primary key, data jsonb); insert into test ({"name":"bill", "age":29});  ?? update test   set data-