BDR: moving a node

[E]

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 total of no concrete answers.


When I say "moving", I mean I'm copying the entire filesystem with 
PostgreSQL shut down, so I assume my data integrity is taken care of. 
All I imagine I need to do is replace the DSN on the second node, which 
in my carelessness, I set up using an IP address and not an FQDN.
I looked up Node Management Functions in the doc,   but cannot come up 
with the final set of instructions to perform an update of the DSN.


What is the process to update the DSN? I assume I'll have to relay the 
changes in my pg_hba.conf, but do not understand, and don't want to 
tinker, with BDR before obtaining some educated advice.


I apologize if my question comes across as dumb. I understand I have to 
update bdr.bdr_connections, but how, and what do I restart? What about 
the server that was moved, any updates needed there?


thanks for your help!

Re: ZFS filesystem - supported ?

[E-BLOKOS]

On 10/25/2021 10:13 AM, Stephen Frost wrote:

Greetings,

* Mladen Gogala (gogala.mla...@gmail.com) wrote:

On 10/23/21 23:12, Lucas wrote:

This has proven to work very well for me. I had to restore a few backups
already and it always worked. The bad part is that I need to stop the
database before performing the Snapshot, for data integrity, so that means
that I have a hot-standby server only for these snapshots.
Lucas

Actually, you don't need to stop the database. You need to execute
pg_start_backup() before taking a snapshot and then pg_stop_backup() when
the snapshot is done. You will need to recover the database when you finish
the restore but you will not lose any data. I know that pg_begin_backup()
and pg_stop_backup() are deprecated but since PostgreSQL doesn't have any
API for storage or file system snapshots, that's the only thing that can
help you use storage snapshots as backups. To my knowledge,the only database
that does have API for storage snapshots is DB2. The API is called "Advanced
Copy Services" or ACS. It's documented here:

https://www.ibm.com/docs/en/db2/11.1?topic=recovery-db2-advanced-copy-services-acs

For Postgres, the old begin/stop backup functions should be sufficient.

No, it's not- you must also be sure to archive any WAL that's generated
between the pg_start_backup and pg_stop_backup and then to be sure and
add into the snapshot the appropriate signal files or recovery.conf,
depending on PG version, to indicate that you're restoring from a backup
and make sure that the WAL is made available via restore_command.

Just doing stat/stop backup is *not* enough and you run the risk of
having an invalid backup or corruption when you restore.

If the entire system is on a single volume then you could possibly just
take a snapshot of it (without any start/stop backup stuff) but it's
very risky to do that and then try to do PITR with it because we don't
know where consistency is reached in such a case (we *must* play all the
way through to the end of the WAL which existed at the time 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

Re: ZFS filesystem - supported ?

[E-BLOKOS]

On 10/26/2021 2:35 AM, Laura Smith wrote:

Sent with ProtonMail Secure Email.

‐‐‐ Original Message ‐‐‐

On Tuesday, October 26th, 2021 at 01:18, Benedict Holland 
 wrote:


In my opinion, ext4 will solve any and all problems without a very deep 
understanding of file system architecture. In short, i would stick with ext4 
unless you have a good reason not to. Maybe there is one. I have done this a 
long time and never thought twice about which file system should support my 
servers.



Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? 
AFAIK 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 ?

[E-BLOKOS]

On 10/26/2021 4:42 PM, Mladen Gogala wrote:


On 10/26/21 05:35, Laura Smith wrote:
Curious, when it comes to "traditional" filesystems, why ext4 and not 
xfs ? AFAIK the legacy issues associated with xfs are long gone ?


XFS is not being very actively developed any more. Ext4 is being 
actively developed and it has some features to help with SSD space 
allocation. Phoronix has some very useful benchmarks:


https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.14-File-Systems 



Ext4 is much better than XFS with SQLite tests and almost equal with 
MariaDB test. PostgreSQL is a relational database (let's forget the 
object part for now) and the IO patterns will be similar to SQLite and 
MariaDB. That benchmark is brand new, done on the kernel 5.14. Of 
course, the only guarantee is doing your own benchmark, with your own 
application.


RedHat and 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: Mongo FDW For Postgres

[Dhivya E]

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: dhivy...@heptagon.in

-- 
Disclaimer: This e-mail along with attachments (if any), transmitted may 
contain privileged, confidential or proprietary information and is for the 
sole use of the intended recipient(s). If you are not the intended 
recipient, please notify the sender, and then please delete and destroy all 
copies of the original message including the attachment. Any unauthorized 
review, use, disclosure, dissemination, forwarding, printing or copying of 
this email or any action taken on this e-mail is strictly prohibited and 
may be unlawful. Subject to applicable law, Sender may intercept, monitor, 
review and retain e-communications (EC) traveling through its 
networks/systems and may produce any such EC to regulators, law 
enforcement, in litigation and as required by law. Heptagon Technologies 
Pvt Ltd has installed active antivirus software but does not accept 
liability or responsibility for the security or reliability of transmission 
or for any virus transmitted. By retaining the  e-mail or the attachments 
(if any), the recipients of this e-mail consent to the foregoing. 

Cannot pg_dump_all anymore...

[E-BLOKOS]

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: Cannot pg_dump_all anymore...

[E-BLOKOS]

On 3/18/2025 5:49 AM, Greg Sabino Mullane wrote:
First figure out which database is having that issue, by using pg_dump 
--schema-only on each database in turn. Then run this SQL on the 
database giving the error to see if the type exists, or what is nearby:


select oid, typname, typtype, typnamespace::regnamespace from pg_type 
where oid <= 794978 order by 1 desc limit 3;


Also let us know the version of pg_dump and the version of Postgres 
being dumped.



Cheers,
Greg



select oid, typname, typtype, typnamespace::regnamespace from pg_type 
where oid <= 794978 order by 1 desc limit 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...

[E-BLOKOS]

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, including all error messages?


setpriv su - postgres -c "pg_dumpall --no-comments -h /run/postgresql -p 
5432 > out.sql"


--
E-BLOKOS

Re: Cannot pg_dump_all anymore...

[E-BLOKOS]

On 3/18/2025 5:49 AM, Greg Sabino Mullane wrote:
First figure out which database is having that issue, by using pg_dump 
--schema-only on each database in turn. Then run this SQL on the 
database giving the error to see if the type exists, or what is nearby:


select oid, typname, typtype, typnamespace::regnamespace from pg_type 
where oid <= 794978 order by 1 desc limit 3;


Also let us know the version of pg_dump and the version of Postgres 
being dumped.



Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support


ok I fixed 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

Re: Cannot pg_dump_all anymore...

[E-BLOKOS]

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 Postgres in use here.




PG 17.4

pg_repack last git

thanks

David

--
E-BLOKOS

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

[Ivan E. Panchenko]

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;

Regards,

Ivan Panchenko
Postgres Professional
the Russian PostgreSQL Company

14.03.2018 19:27, Alexander Farber пишет:

Good afternoon,

A PostgreSQL 10.3 table contains JSON data like:

[{"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"}]


Please suggest, how to extract only the "letter" values and 
concatenate them to a string like "ABCD"?


I suppose at the end I should use the ARRAY_TO_STRING function, but 
which JSON function to use for extracting the "letter" values to an array?


I keep looking at 
https://www.postgresql.org/docs/10/static/functions-json.html but 
haven't found a good one yet


Thank you
Alex

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

[Ivan E. Panchenko]

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','') 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;


# select * from words_moves where gid=656 order by played desc limit 3;
 mid  | action | gid | uid  | played | 
tiles | score

--++-+--+---+--+---
 1353 | swap   | 656 |    7 | 2018-03-14 17:22:18.430082+01 | "ЙНРР" 
| ¤
 1352 | play   | 656 | 1199 | 2018-03-14 16:55:45.898379+01 | [{"col": 
4, "row": 7, "value": 2, "letter": "С"}, {"col": 5, "row": 7, "value": 
3, "letter": "У"}]   |    19
 1351 | play   | 656 |    7 | 2018-03-14 16:38:48.132546+01 | [{"col": 
9, "row": 11, "value": 5, "letter": "Ж"}, {"col": 9, "row": 13, 
"value": 2, "letter": "М"}] |    16

(3 rows)

by trying the following:

#  select string_agg(x->>'letter', ' ') from (select 
jsonb_array_elements(tiles) from words_moves where gid=656 and 
action='play' order by played desc limit 5) x;

ERROR:  42883: operator does not exist: record ->> unknown
LINE 1: select string_agg(x->>'letter', ' ') from (select jsonb_arra...
   ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.

LOCATION:  op_error, parse_oper.c:728

I am probably missing something obvious?
Yes, here x is the alias for the record, not for the json field. So you 
need to write the query like


select string_agg(x->>'letter', ' ')
from (
   select jsonb_array_elements(tiles) x
   from words_moves
   where gid=656 and action='play'
   order by played desc limit 5
) y;



Regards
Alex



Regards,
Ivan

Re: PostgreSQL 13: native JavaScript Procedural Language support ?

[Ivan E. Panchenko]

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 by Google in the way V8 is packaged), which has decreased PL/V8
adoption.


True.

Btw, Nginx made their own JS implementation

https://nginx.org/en/docs/njs/index.html

It might be easier in integration than V8.

Re: How to do phrase search?

[Ivan E. Panchenko]

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 on my tsvector with the string: `Is it possible 
to toggle the visibility of an element`. I tried the following query:



If you want phrase search, use the phraseto_tsquery function, like

   phraseto_tsquery( 'Is it possible to toggle the visibility of an 
element')


or

   phraseto_tsquery( 'my_ts_config', 'Is it possible to toggle the 
visibility of an element')



​

|SELECT id, ts_rank(body, keywords) AS rank|

|FROM so2,|

|to_tsquery('Is & it & possible & to & toggle & the & visibility & of 
& an & element')|


|keywords|

|WHERE keywords @@ body ORDER BY rank DESC;|

​

This give the result in which the "element" string count is large in 
number, however there is a record which exactly matches the search 
string, that comes as third in my result. So logically it just checked 
in my query which word count occurs the most across my body column, 
which I don't want.


​

Can I say somehow to postgresql, that I want the pharse search rather 
than the text spilt up weight in the document. Is it possible to do?


Edit: I tried with `|ts_rank_cd\|` as well. Same result.

Numeric data types

[Godfrin, Philippe E]

Greetings Listees (frist time poster!)

Considering the difference between 64 bit and 32 bit numeric datatypes. We have 
source databases that are running 32 bit. They send their data to a larger 
cluster that is running 64 bit. Should there be something special done in order 
to accommodate the 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 which it is addressed. If the 
reader is not the intended recipient, you are hereby notified that any review, 
dissemination, or copying of this communication is strictly prohibited. If you 
have received this transmission in error, please notify the sender immediately.

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

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 +, Godfrin, Philippe E wrote:
> Greetings Listees (frist time poster!)
>
>
>
> Considering the difference between 64 bit and 32 bit numeric datatypes. We 
> have
> source databases that are running 32 bit. They send their data to a larger
> cluster that is running 64 bit. Should there be something special done in 
> order
> to accommodate the difference?

How is the data sent? In almost every case, the translation should work
fine. I think 32-bit overflow should be your only concern here.

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

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 n32 -> n64 OK, but n64->n32 chance of 32-bit
> overflow…

Uh, I was thinking that your 32-bit values could roll over and the other
end would be confused, but I can also see the 64-bit going above the
32-bit range. As long as everything stays < 32-bits, you should be
fine. We don't transfer binary values very often.

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

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 +, Godfrin, Philippe E wrote:
> Fabulous, thanks much.

You still have not told us how you are transfering the data, so we can
be sure.

---

>
> From: Bruce Momjian mailto:br...@momjian.us>>
> Sent: Monday, August 31, 2020 4:56 PM
> To: Godfrin, Philippe E 
> mailto:philippe.godf...@nov.com>>
> Cc: pgsql-gene...@postgresql.org<mailto: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 n32 -> n64 OK, but n64->n32 chance of 
> > 32-bit
> > overflow…
>
> Uh, I was thinking that your 32-bit values could roll over and the other
> end would be confused, but I can also see the 64-bit going above the
> 32-bit range. As long as everything stays < 32-bits, you should be
> fine. We don't transfer binary values very often.
>
> --
> Bruce Momjian mailto:br...@momjian.us>> 
> https://momjian.us<https://momjian.us>
> EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

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, I believe the developers are using a messaging
> intermediary.

Oh, well, then you would need to find out if they are transfering the
value via text or binary. It is kind of hard to extract query results
as binary, but it is technically possible with binary COPY or triggers.

---

>
> pg
>
>
>
> From: Bruce Momjian mailto:br...@momjian.us>>
> Sent: Monday, August 31, 2020 5:19 PM
> To: Godfrin, Philippe E 
> mailto:philippe.godf...@nov.com>>
> Cc: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>
> Subject: Re: [EXTERNAL] Re: Numeric data types
>
>
>
> On Mon, Aug 31, 2020 at 10:14:48PM +, Godfrin, Philippe E wrote:
> > Fabulous, thanks much.
>
> You still have not told us how you are transfering the data, so we can
> be sure.
>
> ---
>
> >
> > From: Bruce Momjian mailto:br...@momjian.us>>
> > Sent: Monday, August 31, 2020 4:56 PM
> > To: Godfrin, Philippe E 
> > mailto:philippe.godf...@nov.com>>
> > Cc: pgsql-gene...@postgresql.org<mailto: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 n32 -> n64 OK, but n64->n32 chance of
> 32-bit
> > > overflow…
> >
> > Uh, I was thinking that your 32-bit values could roll over and the other
> > end would be confused, but I can also see the 64-bit going above the
> > 32-bit range. As long as everything stays < 32-bits, you should be
> > fine. We don't transfer binary values very often.
> >
> > --
> > Bruce Momjian mailto:br...@momjian.us>> 
> > https://momjian.us<https://momjian.us>
> > EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
> >
> > The usefulness of a cup is in its emptiness, Bruce Lee
> >
>
> --
> Bruce Momjian mailto:br...@momjian.us>> 
> https://momjian.us<https://momjian.us>
> EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

Partitioning with FDW and table size limits

[Godfrin, Philippe E]

Greetings,
In the case where you have a 'local' server, from which you are working with 
foreign tables. And the foreign tables are partitioned. As each of the 
partitioned tables is a table in its own right, is it correct to assume the 
table (relation) size limit of 32 TB applies? For 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...@nov.com<mailto:philippe.godf...@nov.com>

Re: Incremental backup

[Ivan E. Panchenko]

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

--

Inserts and bad performance

[Godfrin, Philippe E]

Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

At first, when there were a low number of rows inserted, the inserts would run 
at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 1.5 
Billion rows, the insert rate has dropped to around 5000 inserts per second. I 
dropped the unique index , rebuilt the other indexes and no change. The 
instance is 16 vcpu and 64GB ram.

I'm perplexed, I can't see to find any reason for the slow down...
Thanks,
pg

Phil Godfrin | 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: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

My apologies for the dearth of details. No on both the cpu and errors. But I do 
believe it is IO related. I just can't find it. 
I thought maybe it was index splitting so I altered the unique index with 
filterfactor=40 and reindexed. No change. 
I then dropped the unique index. No change.
I thought maybe it was checkpoint timeouts, but there was no correlation.
Oddly enough other jobs running concurrently, are also inserting, most likely 
into different partitions, are running about 2x faster than others.

I'm rather perplexed.
pg


-Original Message-
From: Kenneth Marshall  
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, 15 million. The python code 
> commits every 5000 inserts. The table has partitioned children.
> 
> At first, when there were a low number of rows inserted, the inserts would 
> run at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 
> 1.5 Billion rows, the insert rate has dropped to around 5000 inserts per 
> second. I dropped the unique index , rebuilt the other indexes and no change. 
> The instance is 16 vcpu and 64GB ram.
> 
> I'm perplexed, I can't see to find any reason for the slow down...
> Thanks,
> pg

Hi,

With not much information, it may be I/O related. CPU and RAM cannot fix that 
once items need to be written to disk. Are there any errors in the logs or CPUs 
maxxed out?

Regards,
Ken

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Hi Tom. Good point about the index paging out of the buffer. I did that and no 
change. I do have the shared buffers at 40GB, so there's a good bit there, but 
I also did all those things on the page you referred, except for using copy. At 
this point the data has not been scrubbed, so I'm trapping data errors and 
duplicates. I am curios though, as sidebar, why copy is considered faster than 
inserts. I was unable to get COPY faster than around 25K inserts a second 
(pretty fast anyway). Frankly, initially I was running 3 concurrent insert jobs 
and getting 90K ins/sec ! but after 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...@nov.com>> writes:
> I am inserting a large number of rows, 5,10, 15 million. The python code 
> commits every 5000 inserts. The table has partitioned children.
> At first, when there were a low number of rows inserted, the inserts would 
> run at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 
> 1.5 Billion rows, the insert rate has dropped to around 5000 inserts per 
> second. I dropped the unique index , rebuilt the other indexes and no change. 
> The instance is 16 vcpu and 64GB ram.

Can you drop the indexes and not rebuild them till after the bulk load is
done? Once the indexes exceed available RAM, insert performance is going
to fall off a cliff, except maybe for indexes that are receiving purely
sequential inserts (so that only the right end of the index gets touched).

Also see

https://www.postgresql.org/docs/current/populate.html<https://www.postgresql.org/docs/current/populate.html>

regards, tom lane

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

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 
mailto:philippe.godf...@nov.com>> wrote:GreetingsI am 
inserting a large number of rows, 5,10, 15 million. The python code commits 
every 5000 inserts. The table has partitioned children.On the Python client 
side, if you're usi ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=Wr0OlPCq7rNfiTEZXZaOpA%3D%3DjZOFbItn0C5RxyoO%2BmXR2j9FVv%2BWzhFJYReW7ql2zdXPDV40mdS1DQpYOmBt2Oxoehf1bVTmKoJUhNrZa2mIi%2FQMp8dj%2B9IMl1T8FzHRYvXB5us%2BUoZgXp%2BbwqXCXYEsxTG8iZj8bV7I6oscimbLg1XRT039VTqG5EDwXI%2FlGEJpWpx1EVzIcXHenq8DwZLgCSkhj2TFk9HkbexFBWJa3mZxYASZ%2BLx4zI5WJuTtLUGhLcQi5YtrFmxK%2FhegJTn02LIFkfp7RuqaPEJ5b%2BmvbJ8AsY1UH99HbU1dTHOFyQrKRwBXKk1knkZ9ymsDQl7VgWH%2FDg%2FTpgX0URnz8tqnbDANTpMEMJZcEvbETRrqvBMlBcdZlbm2V7LiLwDiQgK3XxvyQpn2CU%2F6QxeZAZslAsvTt%2F3bWNEXmOgoEabPh96vDxjRSdEvVvVGy%2BUPtP36YKLarzhLq1nwAah0bPBgC2XSNlAi02os5URexqotMZjX5vlxMsfPVpncwWUj61%2FFTbVU04xkn2%2FuBm8Izm5oQFsq9iGBQENILj8LakGpFNY5FH1DJuKMEUba91X6mzcy4w2Ez1bPhdWCPFTy9ToiOt7F5vC4AoMD%2FzsxoJCOWQtq9OZMzqVSPaz19AicZdgiGm%2B98bZtbGBZKdIXNiM9YLLKWS9%2FxPaDhL%2FZYkVNUjo%3D>


On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:
Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

On the Python client side, if you're using psycopg, you should consider using 
using COPY instead of INSERT if you're not:

https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy<https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy>

And if using psycopg2, execute_batch might be of value:

https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch<https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch>

Regards,

Gavin

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Excellent idea David, silly me, I didn't think of that. For the other questions:
>How many partitions? 
14
>How many rows do they have when performance is slowing considerably? 
Not sure, maybe on the low millions
>Does this table get many updates or is it insert only? 
insert
>What version of PostgreSQL? 
13
>Are the inserts randomly distributed among the partitions or targeting one or 
>a few partitions? 
Sequentially one partition at a time, so each set of runs is inserting across 
each part.
>Are you able to capture an example and run it in a transaction with explain 
>(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 and bad performance

On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E  
wrote:
> Hi Tom. Good point about the index paging out of the buffer. I did that and 
> no change. I do have the shared buffers at 40GB, so there’s a good bit there, 
> but I also did all those things on the page you referred, except for using 
> copy. At this point the data has not been scrubbed, so I’m trapping data 
> errors and duplicates. I am curios though, as sidebar, why copy is considered 
> faster than inserts. I was unable to get COPY faster than around 25K inserts 
> a second (pretty fast anyway). Frankly, initially I was running 3 concurrent 
> insert jobs and getting 90K ins/sec ! but after a certain number of records, 
> the speed just dropped off.

EXPLAIN (ANALYZE, BUFFERS) works with INSERTs. You just need to be aware that 
using ANALYZE will perform the actual insert too. So you might want to use 
BEGIN; and ROLLBACK; if it's not data that you want to keep.

SET track_io_timing = on; might help you too.

David

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Hi Gavin – thanks I hadn’t realized that about psychopg. I’m on the earlier 
version, so I can’t use what you recommended at this point. But I did use 
copy_expert.

Interestingly enough the performance of the copy statement is only slightly 
better than the insert, as I was running inserts with 5000 values clauses. In 
the end, the current config couldn’t keep up with the WAL creation, so I turned 
all that off. But still no perf gains. I also turned off fsync and set the 
kernel settings to 10% and 98% for dirty pages…

I wonder if there’s a better load product than COPY But I’d still 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 
mailto:philippe.godf...@nov.com>> wrote:GreetingsI am 
inserting a large number of rows, 5,10, 15 million. The python code commits 
every 5000 inserts. The table has partitioned children.On the Python client 
side, if you're usi ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=Wr0OlPCq7rNfiTEZXZaOpA%3D%3DjZOFbItn0C5RxyoO%2BmXR2j9FVv%2BWzhFJYReW7ql2zdXPDV40mdS1DQpYOmBt2Oxoehf1bVTmKoJUhNrZa2mIi%2FQMp8dj%2B9IMl1T8FzHRYvXB5us%2BUoZgXp%2BbwqXCXYEsxTG8iZj8bV7I6oscimbLg1XRT039VTqG5EDwXI%2FlGEJpWpx1EVzIcXHenq8DwZLgCSkhj2TFk9HkbexFBWJa3mZxYASZ%2BLx4zI5WJuTtLUGhLcQi5YtrFmxK%2FhegJTn02LIFkfp7RuqaPEJ5b%2BmvbJ8AsY1UH99HbU1dTHOFyQrKRwBXKk1knkZ9ymsDQl7VgWH%2FDg%2FTpgX0URnz8tqnbDANTpMEMJZcEvbETRrqvBMlBcdZlbm2V7LiLwDiQgK3XxvyQpn2CU%2F6QxeZAZslAsvTt%2F3bWNEXmOgoEabPh96vDxjRSdEvVvVGy%2BUPtP36YKLarzhLq1nwAah0bPBgC2XSNlAi02os5URexqotMZjX5vlxMsfPVpncwWUj61%2FFTbVU04xkn2%2FuBm8Izm5oQFsq9iGBQENILj8LakGpFNY5FH1DJuKMEUba91X6mzcy4w2Ez1bPhdWCPFTy9ToiOt7F5vC4AoMD%2FzsxoJCOWQtq9OZMzqVSPaz19AicZdgiGm%2B98bZtbGBZKdIXNiM9YLLKWS9%2FxPaDhL%2FZYkVNUjo%3D>


On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:
Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

On the Python client side, if you're using psycopg, you should consider using 
using COPY instead of INSERT if you're not:

https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy<https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy>

And if using psycopg2, execute_batch might be of value:

https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch<https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch>

Regards,

Gavin

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

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 the other indexes and no change. IMNSHO, this is the worst 
possible approach.  Drop everything except the unique index, and then (if 
possible) sort the input file by the unique key.   That ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=MscIZbFcA20cZ6HlLPCzsg%3D%3Dup0ll%2Bu3Q7dCpCHjMRutwBFZuF6FlLMVKqLpzfn8crKDwB3aTItjQ7KgyC%2BQRsD5f8xbGX3jwMuXdtjE8CPSUKv%2Fnjpl7aIQazHJfSlvqupgF%2FVAjvz8Jlq7ZsTWUJt%2BWrXlTYCuU%2BCQOKhfWIIlqoUScpyv3co3gzlBlKNVgoCnnPt3fs8nGUhVxEcDxAJG9wNueH6iVn9jt7IZqlJqkRuTmS%2BLprH8Wpzbiccgs%2B%2Fe761zMa0BtyYusxWyTzOBV%2BlpQ%2FkcIStxNkVj3yVEQrTaZKxvBJ1%2BBjgcdUM3ElooA73KU%2Fw61vomhZ35fg%2BjjYpYuKP9oTWWQUuW%2FWsjpQTX%2BL45OIGuqTNH5fXB8BrPn4Y6b5ov0daaqUQGoAWwU5W5q3ZY6cXp7aUiHpF5GjNsdel3fZrHhxyVjwGII7YfDIjf21rgY9iMePiiqA6Q8VT6f18T8yyhIgcuVYOJ%2BLp3lBxaj%2Fv%2FEjQHbHECjy4LeFjie6WzC%2F4%2BkdBShGp5SA8plTtWPCeY6aaacWMN0RsqNcCXpybFU4smy8v0bb%2FeqOrfR8abX4%2FRB%2BVI5WpHUi0TihiOt%2BviFrXdWNSOopKTokuQp4DJQ2uqqh5tUZtlsXGeemkFCTgwy6WCX2Uemtupx1j4MZWdbuPQisdDvUE0XpSQARjcSjmUdHlWve%2FHd6wsLXc2abfpAW5tYy%2BL>
On 11/24/21 1:15 PM, Godfrin, Philippe E wrote:

[snip]

I dropped the unique index , rebuilt the other indexes and no change.

IMNSHO, this is the worst possible approach.  Drop everything except the unique 
index, and then (if possible) sort the input file by the unique key.   That'll 
increase buffered IO; otherwise, you're bopping all around the filesystem.

Using a bulk loader if possible would increase speeds
--
Angular momentum makes the world go 'round.

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

[Godfrin, Philippe E]

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
> to contain a couple billion records.
[...]
> What else should I be worried about ?
>
> I suspect that part of why things are running really well so far is that the
> relatively small amounts of data in these tables ends up in the DB cache and
> disk I/O is kept at a minimum.  Will that no longer be the case once queries
> start running on these big tables ?

>
>Depends a lot on how good the locality of your queries is. If most read
>only the same parts of the same indexes, those will still be in the
>cache. If they are all over the place or if you have queries which need
>to read large parts of your tables, cache misses will make your
>performance a lot less predictable, yes. That stuff is also hard to
>test, because when you are testing a query twice in a row, the second
>time it will likely hit the cache and be quite fast.
>
>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 erroneously think that a full
>table scan is faster. That can lead to a situation where a query
>normally takes less than a second, but sometimes (seemingly at random)
>it takes several minutes - users will understandably be upset about such
>behaviour. It is in any case a good idea to monitor execution times to
>find such problems (ideally before users complain), but each needs to be
>treated on an individual basis, and sometimes there seems to be no good
>solution.

To the OP, that's is a tall order to answer - basically that's wjhy DBA's still 
have
Jobs...

For Peter I have a question. What exactly causes 'unstable execution plans' ??

Besides not using bind variables, bad statistics, would you elaborate in what 
would
contribute to that instability?

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

[Godfrin, Philippe E]

>
>
>From: Peter J. Holzer hjp-pg...@hjp.at<mailto:hjp-pg...@hjp.at>
>Sent: Friday, December 10, 2021 3:43 PM
>To: 
>pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
>Subject: Re: [EXTERNAL] Re: performance expectations for table(s) with 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 erroneously think that a full
>> >table scan is faster. That can lead to a situation where a query
>> >normally takes less than a second, but sometimes (seemingly at random)
>> >it takes several minutes
>[...]
>> For Peter I have a question. What exactly causes ‘unstable execution plans’ 
>> ??
>>
>> Besides not using bind variables, bad statistics, would you elaborate
>> in what would contribute to that instability?
>
>Not using bind variables and bad statistics are certainly big factors:
>
>On one hand not using bind variables gives a lot more information to the
>optimizer, so it can choose a better plan at run time. On the other hand
>that makes hard to predict what plan it will choose.
>
>Bad statistics come in many flavours: They might just be wrong, that's
>usually easy to fix. More problematic are statistics which just don't
>describe reality very well - they may not show a correlation, causing
>the optimizer to assume that two distributions are independent when they
>really aren't (since PostgreSQL 10 you can create statistics on multiple
>columns which helps in many but not all cases) or not show some other
>peculiarity of the data. Or they may be just so close to a flipping
>point that a small change causes the optimizer to choose a wildly
>different plan.
>
>Another source is dynamically generated SQL. Your application may just
>put together SQL from fragments or it might use something like
>SQLalchemy or an ORM. In any of these cases what looks like one query
>from a user's perspective may really be a whole family of related
>queries - and PostgreSQL will try to find the optimal plan for each of
>them. Which is generally a good thing, but it adds opportunities to mess
>up.
>
>hp
>
>--
>_ | Peter J. Holzer | Story must make more sense than reality.
>|_|_) | |
>| | | h...@hjp.at<mailto:h...@hjp.at> | -- Charles Stross, "Creative writing
>__/ | http://www.hjp.at/ | challenge!"

Good answer Peter, I agree wholeheartedly. I was curious if there was something 
specific to Postgresql .
phil

Re: Counting the number of repeated phrases in a column

[Ivan E. Panchenko]

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 short of a function in the standard Postgres to do the
>> following:
>>
>> It is easy to count the number of occurrence of words, but it is
>> rather difficult to count the number of occurrence of phrases.
>>
>> For instance:
>>
>> A cell of value:  'Hello World' means 1 occurrence a phrase.
>>
>> A cell of value: 'Hello World World Hello' means no occurrence
of any
>> repeated phrase.
>>
>> But, A cell of value: 'Hello World World Hello Hello World'
means 2
>> occurrences of 'Hello World'.
>>
>> 'The City of London, London' also has no occurrences of any
repeated
>> phrase.
>>
>> Anyone has got such a function to check out the number of
occurrence
>> of any repeated phrases?
>>
>> Regards,
>>
>> David
>
> Don't know if it's exactly what you want, but you can replace all
> occurence of the phrase in the text by empty string and compute the
> diff between the initial and the result and next divide by the
length
> of your phrase.
>
> Example :
> WITH x AS (SELECT 'toto like tata and toto like titi and toto like
> tutu' , 'toto like' phrase)
> SELECT (char_length(texte) - char_length(replace(texte, phrase,
'')))
> / char_length(phrase) AS nb_occurence
> FROM x
>
This works if the user knows the phrase. As far as I understood, the
phrase is not known, and user wants to count number of repeats of any
phrases.
Of course this can be done with recursive CTE. Split into words,
generate all phrases (AFAIK requires recursion), then group and count.

But probably in PL/Perl this could be done  more effectively.


Is there an example of using recursive CTE to split a text string into 
words?


Recursion is not needed for splitting into words. This can be done by 
regexp_split_to_table function.


But generation of all possible phrases from the given list of words 
probably requires recursion. On the first step the list of words becomes 
a list of a single-worded phrases. On each iteration then, you add the 
next word to each existing phrase, if it is possible (i.e. until the 
last word is reached).




Regards,

David


Regards,
Ivan

Re: update field in jsonb

[Ivan E. Panchenko]

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->age = 30


When a record in PostgeSQL is UPDATEd, its new version is created. So 
such partial JSON update would be not more than some syntax sugar. That 
is why it is not yet implemented, though plans for that exist.


Now you have to do something like:

UPDATE test SET data = jsonb_set(data, ARRAY['age'], to_jsonb(30)) WHERE ..

Re: jsonb and where clause?

[Ivan E. Panchenko]

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 info is 
the jsonb column)


But what if the jsonb column contains an json array, how can I search then?

info -> [ { "customer" : "John Doe" } ]


If you know the index in array, you can search like
info->0->>'customer'
If you want to search in any array element, you need to use JSQUERY 
extension,

see https://github.com/postgrespro/jsquery




btw, using PostgreSQL 9.6 but will be moving to 10 soon.


Regards,

BTJ


Regards,
Ivan

Re: jsonb and where clause?

[Ivan E. Panchenko]

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 to search for a value
inside the jsonb column:

select * from orders where info ->> 'customer' = 'John Doe'(where info is 
the jsonb column)


But what if the jsonb column contains an json array, how can I search then?

info -> [ { "customer" : "John Doe" } ]

If you know the index in array, you can search like
info->0->>'customer'
If you want to search in any array element, you need to use JSQUERY
extension,
see https://github.com/postgrespro/jsquery



btw, using PostgreSQL 9.6 but will be moving to 10 soon.


Regards,

BTJ
  

Regards,
Ivan


Thx... :)

btw, just managed to use the following sql:

select * from orders where info @> '{"recs": [{ "customer":"John Doe"}]}'

(changed my json string to info -> { "recs: [ { "customer" : "John Doe" } ] }

And this seems to work but is this the "wrong" way of doing it or?

Yes, definitely it works, and is be supported by GIN index.

Nevertheless, I recommend you to have a look at JSQUERY which allows 
more complex queries, also with index support.



BTJ


Regards,
Ivan

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

[Ivan E. Panchenko]

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 demonstrating my problem: 
http://sqlfiddle.com/#!17/2c9c5/1 


In a two-player game I store user chats in a table:

CREATE TABLE chat(
    gid integer,            /* game id */
    uid integer,            /* user id */
    created timestamptz,
    msg text
);

Here I fill the table with a simple test data:

INSERT INTO chat(gid, uid, created, msg) VALUES
    (10, 1, NOW() + interval '1 min', 'msg 1'),
    (10, 2, NOW() + interval '2 min', 'msg 2'),
    (10, 1, NOW() + interval '3 min', 'msg 3'),
    (10, 2, NOW() + interval '4 min', 'msg 4'),
    (10, 1, NOW() + interval '5 min', 'msg 5'),
    (10, 2, NOW() + interval '6 min', 'msg 6'),
    (20, 3, NOW() + interval '7 min', 'msg 7'),
    (20, 4, NOW() + interval '8 min', 'msg 8'),
    (20, 4, NOW() + interval '9 min', 'msg 9');

And can fetch the data by running the SELECT query:

SELECT ARRAY_TO_JSON(
  COALESCE(ARRAY_AGG(ROW_TO_JSON(x)),
           array[]::json[])) FROM (
SELECT
    gid,
    uid,
    EXTRACT(EPOCH FROM created)::int AS created,
    msg
FROM chat) x;

which returns me a JSON-array:

[{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
 {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
 {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
 {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
 {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
 {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
 {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
 {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
 {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]

However I would like to use "gid" as JSON object properties and the 
rest data as values in that object:


{"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
        {"uid":2,"created":1514813103,"msg":"msg 2"},
        {"uid":1,"created":1514813163,"msg":"msg 3"},
        {"uid":2,"created":1514813223,"msg":"msg 4"},
        {"uid":1,"created":1514813283,"msg":"msg 5"},
        {"uid":2,"created":1514813343,"msg":"msg 6"}],

 "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
        {"uid":4,"created":1514813463,"msg":"msg 8"},
        {"uid":4,"created":1514813523,"msg":"msg 9"}]}

Is that please doable by using the PostgreSQL JSON functions?

Thank you
Alex

Re: Mailing list archiver

[Ivan E. Panchenko]

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 you think, or if you have any suggestions.
I left email addresses off the site to protect privacy.

p.s: you can click messages to collapse the tree

Thanks

Re: Slow alter sequence with PG10.1

[Ivan E. Panchenko]

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 (5 seconds 
for 10.1 and 2 - for 9.6.5), and the linear growth effect is not observed.


Removing advisory locks saves ~ 200ms in both cases, and still 9.6.5. 
seems faster.



Ivan Panchenko

Postgres Professional
the Russian PostgreSQL Company
+79104339846

22.01.2018 21:55, David G. Johnston пишет:
On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger 
mailto:michael@kruegers.email>>wrote:


Dear community,

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after
upgrading it from PG9.6.6. My application heavily uses sequences
and requires different increments of sequence numbers, e.g. a
range of 100, 1000 or 5000 numbers, so it is not possible to set a
fixed increment on a sequence that can be used by my application.

With PG10.1 the performance has dropped seriously so that my
application becomes unusable. After investigating different
aspects, I was able to isolate the issue to be related to the
sequences in Postgres 10.1.

Below shows a simple test script showing the problem:

​[...]​


On my computer I tried this code on PG9.6.6 and it executed in
roughly 3 seconds.
When running it on PG10.1 it takes over 7 minutes.

Further investigation showed that the problem is related to ALTER
SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without
providing a workaround or a way to switch to the old PG9.6
performance, at least I can't find anything in the documentation.

Is this a bug?


​Without testing/confirming I'd be inclined to agree that this is a 
regression for an unusual usage of sequences.  Work was done to make 
typical use cases of sequences more feature-full and it is quite 
possible the added effort involved hurts your specific scenario.  I'd 
expect a hacker to eventually pick this up, confirm the observation, 
and provide feedback.  This seems like sufficient amount of detail to 
get the ball rolling.


David J.

Re: How to Optimize pg_trgm Performance

[Ivan E. Panchenko]

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 rank is some popularity factor so 
that popular results will show up higher than less popular results 
given the same tri-gram distance.


I want to return results in the order of [distance], [distance_word], 
[rank].  The input comes from the user and is not known in advance.  
My query is as follows:


    SELECT title
    ,id
    ,(input <-> keywords) AS distance
        ,(input <<-> keywords) AS distance_word
    ,rank
    FROM  (VALUES (cast('red pill' AS text))) consts(input)
    ,mv_autosuggest
    ORDER BY 3, 4, 5
    LIMIT 20;

This gives me pretty good results, but it takes too long and is not 
likely to scale well.


I have created two indexes but neither seem to be used:

CREATE INDEX mv_autosuggest_keywords_tgrm_gist ON 
staging.mv_autosuggest USING gist (keywords gist_trgm_ops);


CREATE INDEX mv_autosuggest_keywords_tgrm_gin ON 
staging.mv_autosuggest USING gin (keywords gin_trgm_ops);


This is the result of explain analyze:

QUERY PLAN |
---|
Limit  (cost=356.41..356.46 rows=20 width=51) (actual 
time=163.132..163.135 rows=20 
loops=1)   |
  ->  Sort  (cost=356.41..372.96 rows=6619 width=51) (actual 
time=163.130..163.131 rows=20 
loops=1)    |
    Sort Key: (('red pill'::text <-> mv_autosuggest.keywords)), 
(('red pill'::text <<-> mv_autosuggest.keywords)), mv_autosuggest.rank |

    Sort Method: top-N heapsort  Memory: 28kB |
    ->  Seq Scan on mv_autosuggest  (cost=0.00..180.29 rows=6619 
width=51) (actual time=0.263..161.289 rows=6619 loops=1)  |

Planning time: 0.139 ms |
Execution time: 163.174 ms |

How can I improve the performance here?


This can be improved if you use sort only by distances
(try ORDER BY 3,4
or ORDER BY 3
or ORDER BY 4

Than you should get  plan  like

Index Scan using mv_autosuggest_keywords_tgrm_gist on mv_autosuggest
 Order By: ((keywords <-> 'red pill'::text) AND (keywords <->> 
'red pill'::text))


Which means that KNN  index search is enabled : 
http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf


If you want to sort it also by rank, you can make a two-level 
construction like:


SELECT * FROM (
   SELECT  ORDER BY 3,4 LIMIT   /* make some empirical 
redundant limit here */

) foo ORDER BY 5 LIMIT 


Regards,
Ivan Panchenko





Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org 

Strategy for migrating from Oracle to PG

[Johnson, Bruce E - (bjohnson)]

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 —project_base /oracle_migrate/ —init_project [schema name]

Properly set the schema username and password in the ./config/ora2pg.conf filer 
each of them

Do I need to create the users in Postgres, or is that done as part of the 
import_all.sh script?

The schemas themselves are not all that complex, mostly just tables, views and 
a handful of procedures and grants.

Installed version of Postgres is v15.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

listening on an address other than 'localhost'?

[Johnson, Bruce E - (bjohnson)]

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 postgresql the postgres 
processes are not running afterwards.

root@dhbpostgres:/home/johnson# ps -aux | grep postgres
root1452  0.0  0.0   6608  2364 pts/1S+   19:07   0:00 grep 
--color=auto postgres
root@dhbpostgres:/home/johnson# vi /etc/postgresql/15/main/postgresql.conf 
 apt repo.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Granting privileges to a schema to a role

[Johnson, Bruce E - (bjohnson)]

(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 webdata, and I 
created the role trav and granted all table rights in the schema to the role

GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav.

When I log into pgsql as trav and run \dp the privileges appear correct but 
trying a simple select fails with ‘permission denied’ error:


psql webdata -U trav

psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))

Type "help" for help.


webdata=> \dp trav.sectors

 Access privileges

 Schema |  Name   | Type  |Access privileges| Column privileges | 
Policies

+-+---+-+---+--

 trav   | sectors | table | webdata=arwdDxt/webdata+|   |

| |   | trav=arwdDxt/webdata|   |

(1 row)


webdata=> select sectorname, count(worldname) from trav.sectors group by 
sectorname order by sectorname;

ERROR:  permission denied for schema trav

LINE 1: select sectorname, count(worldname) from trav.sectors group ...

What am I missing?


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

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

[Johnson, Bruce E - (bjohnson)]

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 getting on the client is:


 password authentication failed for user "trav"

connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), 
port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.128.206.109", user 
"trav", database "webdata", no encryption

But I do have an entry that should allow it:


#Internal server mgmt range

hostssl all all 10.128.206.0/23 password

>From the manual (pg 704, 21.1. The pg_hba.conf File):


"An IP address range is specified using standard numeric notation for the 
range's starting address, then a slash (/) and a CIDR mask length. The mask 
length indicates the number of high-order bits of the client IP address that 
must match. Bits to the right of this should be zero in the given IP address. 
There must not be any white space between the IP address, the /, and the CIDR 
mask length.

Typical examples of an IPv4 address range specified this way are 
172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 
10.6.0.0/16 for a larger one. "

10.128.206.109 is definitely in that range.

The test script DOES work with my desktop running the same software, but I have 
it set in pg_hba.conf as just my systems ip:

 hostssl webdata  trav   nnn.nnn.nnn.nnn/32 password


(Ip address redacted because it is externally accessible)

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Accessing system information functions

[Johnson, Bruce E - (bjohnson)]

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 functions gives me a syntax error


postgres=# pg_database_size(avi);

ERROR:  syntax error at or near "pg_database_size"

LINE 1: pg_database_size(avi);


Do I have to add anything to the search_path?


postgres=# show search_path;

   search_path

-

 "$user", public

(1 row)



--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: [EXT]Re: Accessing system information functions

[Johnson, Bruce E - (bjohnson)]

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 SELECT, e.g.:

SELECT pg_database_size('avi');

Also, the database name must be a string.

Do I have to add anything to the search_path?

Not for objects in schema pg_catalog which is always searched whether
it's listed in the search path or not.

--
Erik


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Strange error trying to import with Ora2PG

[Johnson, Bruce E - (bjohnson)]

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.resource_id and r.requester_vis_only = 1;

A simple enough fix; Oracle happily accepts a number-like string as a number, 
and Postgres wants a string delimited,  so I went into the ./sources/views 
folder and edited the view creation to add the required single quotes:

-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0
-- Copyright 2000-2023 Gilles DAROLD. All rights reserved.
-- DATASOURCE: 
dbi:Oracle:host=dhboracle2.pharmacy.arizona.edu;sid=phmweb2;port=1521

SET client_encoding TO 'UTF8';

SET search_path = calendar3,oracle,public;
\set ON_ERROR_STOP ON

CREATE OR REPLACE VIEW reserver_visible_restriction (resource_id, 
resourcegroup_id, affil_id) AS select r.resource_id, rg.resourcegroup_id, 
a.affil_id FROM resources r, resourcegroupaffil rg, resourceaffil a where 
r.resource_id = a.resource_id and r.resource_id = rg.resource_id and 
r.requester_vis_only = '1’;

I tested the view creation with that code interactively, and it worked.

However when I re-run the import_all.sh script it errors out at the same place 
with the same error (in fact the pasted error code is from running the script 
AFTER I fixed it). The sql isn’t being cached anywhere in the system is it?

I know that I modified the correct file.

It happens even if I drop the database and recreate it.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

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

[Johnson, Bruce E - (bjohnson)]

That’s not it. export_schema.sh writes the scripts, and then emits a ora2pg 
command to pull in the data (I've managed to do this a dozen times so far 
working out how to do things. :-)

The dates on the files in the sources directory are consistent with that, and 
don’t change when I run the script.

-rw-rw-r-- 1 johnson johnson 872 Sep 27 22:02 AHSC_SCHEDULE_ROUNDED_view.sql
-rw-rw-r-- 1 johnson johnson 817 Sep 27 22:02 COP_COLLEGE_HOUR_view.sql
-rw-rw-r-- 1 johnson johnson 628 Oct  3 17:06 
RESERVER_VISIBLE_RESTRICTION_view.sql
-rw-rw-r-- 1 johnson johnson 515 Sep 27 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 with ora2pg and running into an odd 
error when the import_all.sh script gets to a specific view.
The error is:

However when I re-run the import_all.sh script it errors out at the same place 
with the same error (in fact the pasted error code is from running the script 
AFTER I fixed it). The sql isn’t being cached anywhere in the system is it?

I'm betting that every time you run import_all.sh it rewrites the scripts. 
Pretty sure if you look at the file the creation script will have been 
rewritten to its original from.

I know that I modified the correct file.
It happens even if I drop the database and recreate it.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs

--
Adrian Klaver
adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

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

[Johnson, Bruce E - (bjohnson)]

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. Clearly the script is running the other one.


~/oracle_migration$ tree calendar3

calendar3

├── config

│   ├── ora2pg.conf

│   └── ora2pg.conf.orig


SNIP


├── schema

│   ├── dblinks

│


SNIP


│   └── views

│   ├── AHSC_SCHEDULE_ROUNDED_view.sql

│   ├── COP_COLLEGE_HOUR_view.sql

│   ├── RESERVER_VISIBLE_RESTRICTION_view.sql

│   ├── RESOURCEAFFIL_WITHCODE_view.sql

│   ├── UA_COLLEGE_LOOKUP_view.sql

│   └── view.sql

└── sources



SNIP


└── views

├── AHSC_SCHEDULE_ROUNDED_view.sql

├── COP_COLLEGE_HOUR_view.sql

├── RESERVER_VISIBLE_RESTRICTION_view.sql

├── RESOURCEAFFIL_WITHCODE_view.sql

├── UA_COLLEGE_LOOKUP_view.sql

└── view.sql


29 directories, 127 files


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

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

[Johnson, Bruce E - (bjohnson)]

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=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...)
 failed: FATAL:  password authentication failed for user "trav"
FATAL:  no pg_hba.conf entry for host "150.135.124.50", user "trav", database 
"webdata", no encryption at ./pg_test.pl line 8.

Working system:

[root@avipg perl]# ./pg_test.pl
Sector Alpha Crucis has 44 worlds  
Sector Antares has 37 worlds  
Sector Core has 221 worlds …

(The test dataset is a collection of mapping data for an old RPG game) 

Note the pg_test.pl script was copied from the working server to the non 
working one.

The pg_hba.conf on the server includes this which should encompass all systems 
in this VLAN

# external 'OldMTM' site range
hostssl all all 150.135.124.0/25 password

Another system in the same address range is working just fine with the 
identical setup, in fact it’s in production without issues. 

Both systems are running Rocky Linux 9, using the perl DBI interface with 
DBD::Pg all installed from the Rocky Linux repositories.

Firewall settings, Perl version, env variables etc are the same on both client 
hosts

I know the password is correct because I just successfully logged in on the 
server with psql -U trav -d webdata -W  and used the password in the connect 
statement in the script.

Anywhere else that I should look for a cause?


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

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

[Johnson, Bruce E - (bjohnson)]

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 ...") but it barffed when it saw the '\'...
ERROR:  syntax error at or near "\"

I can do this with a command line approach, attaching to the DB  then run
using...

Duh! I just realized that what I proposed with system() is a command line 
approach.

As David Johnston mentionned, you can use the SQL COPY command.

One thing to remember with the Perl DBI is that you can use a string variable 
in the $dbh->do() command.

Perl uses 2 different string variable delimiters:

1)  ‘ ‘ , which is exactly what you enter $s= ‘\copy * from foo as json’; will 
send that to the database without the need for escaping anything (unless you 
need to enter an actual ‘ in the command, in which case method two is better)

2) “ “ , which allows for declared perl variables to be substituted in the 
string:$table=‘foo’;$type=‘json’;$cmd=‘\copy’;$s= “$cmd * from $table as $type”;

Concatenation (periods between strings) works as well: $s = ‘\copy ‘.”* from 
foo as json”;

 Then $dbh->do($s); will work in alll three cases.

Been using perl and DBI for (does quick math, ulp!) over 20 years now wrangling 
a lot of things like this.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs