RE: [Extern] Re: Advice on binary installation

-Original Message- From: Zwettler Markus (OIZ) Sent: Tuesday, April 13, 2021 8:08 AM > -Ursprüngliche Nachricht- > Von: Paul Förster mailto:paul.foers...@gmail.com>> > Gesendet: Dienstag, 13. April 2021 15:02 > Betreff: [Extern] Re: Advice on binary installation > > Hi Marku

RE: Ways to "serialize" result set for later use?

From: Adam Brusselback Sent: Monday, April 12, 2021 12:51 PM > Checking data (DML), if functions are doing the right things is something we > do in our code unit tests. This is exactly what I am writing, unit tests for my code (which is pl/pgsql). This is an ELT pipeline for my customers to b

RE: Ways to "serialize" result set for later use?

From: Adam Brusselback Sent: Saturday, April 10, 2021 9:06 PM To: pgsql-general Subject: Ways to "serialize" result set for later use? Hey there everyone, I am going through the process of writing my first pgtap tests for my database, and I wanted to get some feedback on if my solution seems f

RE: Check constraint failure messages

From: Miles Elam Sent: Tuesday, April 6, 2021 4:19 PM Sadly, this is a cloud-managed database without direct access to 5432 from outside the VPC and bastian instances are frowned upon by our security folks. Guess I'm stuck with bisecting. Thanks for the confirmation. It'd

RE: Localizing stored functions by replacing placeholders in their body

-Original Message- From: Alvaro Herrera Sent: Tuesday, March 2, 2021 2:19 PM To: Alexander Farber Cc: pgsql-general Subject: Re: Localizing stored functions by replacing placeholders in their body On 2021-Mar-02, Alexander Farber wrote: > CREATE OR REPLACE FUNCTION localize_hello() >

RE: Syntax checking DO blocks and ALTER TABLE statements?

>From: Ron > >How does one go about syntax checking this? > >do $$ >begin if exists (select 1 from information_schema.table_constraints >where constraint_name = 'error_to_web_service_error') then > raise notice 'EXISTS error_to_web_service_error'; > else > ALTER TAB

RE: Is there a good discussion of optimizations?

From Guyren Howe >Actually, I’m mostly going to talk about the relational model, rather than >SQL. Our industry seems to always settle for third-best, and SQL is the worst >of all the examples of this. The world desperately needs a good relational >database based on a better query language — dat

RE: Is there a good discussion of optimizations?

From: Guyren Howe >Most folks, in my experience, who use relational databases don’t really >understand the basic theory or even more important the why - the philosophy - >of what a relational database is and how to get the most out of them. I see a >lot of folks trying to use SQL in an imperat

RE: Alter the column data type of the large data volume table.

>From: Olivier Gautherot >>5) If you're brave enough, convert your current table as a partition (rename >>it to something like table_hist), duplicate the table model under the same >>name as now (adjusting the primary key type) and set the INHERITS on the >>primary key range. The inheritance s

RE: New "function tables" in V13 documentation

>From: Adrian Klaver >>On 11/15/20 9:00 AM, David G. Johnston wrote: > >> In hindsight it could have been handled better. Waiting longer at >> different points and making pronouncements on -announce to solicit >> feedback from people who don't follow -docs. That doesn't change the > Yes and at

RE: New "function tables" in V13 documentation

>From: David G. Johnston >>On Fri, Nov 13, 2020 at 12:20 PM Kevin Brannen <mailto:kbran...@efji.com> >>wrote: >>Designing pages to the smallest media just frustrates those users on larger >>media (cue the many examples on the web where the left/right ma

RE: New "function tables" in V13 documentation

>From: David G. Johnston >On Mon, Nov 9, 2020 at 1:41 PM Tom Lane wrote: >Alvaro Herrera writes: >> On 2020-Nov-08, Adrian Klaver wrote: >>> Yeah, I would agree with the mobile first design comments. Then again that >>> plague is hittin

RE: how to slow down parts of Pg

>From: Ron > >What you need is async replication instead of synchronous replication. The only way I can think of to do that in our present situation would be to buy DRBD-Proxy, which becomes a single-point-of-failure and goes against the idea of HA (it seems like a good product for disaster reco

RE: how to slow down parts of Pg

>From: Peter J. Holzer >On 2020-04-21 21:16:57 +, Kevin Brannen wrote: >> From: Michael Loftis >> > drbdsetup allows you to control the sync rates. >> >> I was hoping not to have to do that, but the more I think about this >> I'm realizing t

RE: how to slow down parts of Pg

>From: Laurenz Albe > >>On Tue, 2020-04-21 at 20:30 +, Kevin Brannen wrote: >> I have an unusual need: I need Pg to slow down. I know, we all want >> our DB to go faster, but in this case it's speed is working against me in 1 >> area. >> >>

RE: how to slow down parts of Pg

From: Virendra Kumar >Autovacuum does takes care of dead tuples and return space to table's >allocated size and can be re-used by fresh incoming rows or any updates. > >Index bloat is still not being taken care of by autovacuum process. You should >use pg_repack to do index rebuild. Keep in min

RE: how to slow down parts of Pg

From: Michael Loftis >>From: Kevn Brannen >> I don't particularly like doing the vacuum full, but when it will release >> 20-50% of disk space for a large table, then it's something we live with. As >> I understand, a normal vacuum won't release all the old pages that a "full" >> does, hence w

RE: how to slow down parts of Pg

From: Michael Loftis >>From: Kevn Brannen >>I have an unusual need: I need Pg to slow down. I know, we all want our DB >>to go faster, but in this case it's speed is working against me in 1 area. >> >>We have systems that are geo-redundant for HA, with the redundancy being >>handled by DRBD to

RE: how to slow down parts of Pg

From: Michael Lewis > You say 12.2 is in testing but what are you using now? Have you tuned configs > much? Would you be able to implement partitioning such that your deletes > become truncates or simply a detaching of the old partition? Generally if you > are doing a vacuum full, you perhaps

how to slow down parts of Pg

I have an unusual need: I need Pg to slow down. I know, we all want our DB to go faster, but in this case it's speed is working against me in 1 area. We have systems that are geo-redundant for HA, with the redundancy being handled by DRBD to keep the disks in sync, which it does at the block le

RE: Is PostgreSQL SQL Database Command Syntax Similar to MySQL/MariaDB?

From: Turritopsis Dohrnii Teo En Ming > Is PostgreSQL SQL database command syntax similar to MySQL/MariaDB? If you stuck to "standard SQL" in Mysql, you'll generally be fine; the differences will be pretty minor. The further you strayed from the standard will cause you more work -- usually. T

RE: Loading 500m json files to database

From: Rob Sargent > Sorry if I missed it, but have we seen the size range of these json files? Not that I've seen, but that's an implementation detail for whoever is doing the work. As someone else pointed out, pick the value as needed, whether that's 10, 100, 1000, or whatever. But issuing 10

RE: Loading 500m json files to database

From: pinker > it's a cloud and no plpythonu extension avaiable unfortunately You're misunderstanding him. See David's post for an example, but the point was that you can control all of this from an *external* Perl, Python, Bash, whatever program on the command line at the shell. In pseudo-c

RE: Temporary tablespaces on a RAM disk

Daniel Westermann wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences with > that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it is a bad idea nor the opposite. Well, we point our "stats_temp_directory

RE: vacuum full doubled database size

>Fabio Ugo Venchiarutti wrote: >On 13/03/2020 15:15, Ron wrote: >> This is why I'd VACUUM FULL in a planned manner, one or two tables at >> a time, and *locally* from crontab. > >That's not really viable on any remotely busy system: VACUUM FULL claims >exclusive table locks, causing queries to han

RE: pg_restore restores out of order

>Adrian Klaver wrote: >> Kevin Brannen wrote: >> It goes on to say a trigger is the right way to do this. So yeah, a >> "tightening of the code" seems to be hitting me because of bad code from the >> past. >> >> Problem identified and I'l

RE: pg_restore restores out of order

>Adrian Klaver wrote: >On 3/11/20 2:46 PM, Kevin Brannen wrote: >> I'm working thru our system to upgrade from 9.6.5 to 12.2. One of the last >> things I'm verifying is backup/restore and it's failing for no reason I can >> figure out. So I'm looki

pg_restore restores out of order

I'm working thru our system to upgrade from 9.6.5 to 12.2. One of the last things I'm verifying is backup/restore and it's failing for no reason I can figure out. So I'm looking for pointers on this. If it matters, the code is from the version 12.2 from the Pg site, RPMs for Centos 6 (.10). Th

RE: Patterns to look for in the PostgreSQL server log

>From: Mageshwaran Janarthanam > >Hi Team...I am trying to setup some monitoring over the PostgreSQL server log. >I am not clear which error I should be most concerned about. Could you please >share your thoughts on what pattern I should search in the log file? I'd treat it like any other log a

RE: trouble making PG use my Perl

From: Alan Hodgson On Mon, 2020-03-02 at 18:23 -0500, Tom Lane wrote: Kevin Brannen < Centos 8 ships with 5.14 (IIRC). I don't have an actual Centos 8 machine handy to disprove that, but the info I have says that RHEL8/Centos 8 branched off from Fedora 28, and F28 most de

RE: trouble making PG use my Perl

From: Steven Lembark >Funny thing is that both PG and Perl are easy enough to build from scratch and >the centos compile of Perl at least is both ancient and horrid enough (5.00503 >compatibility, really?) that it's easier to just shell-script both builds and >run it overnight. >Q: How un-opt

RE: trouble making PG use my Perl

From: Tom Lane >Kevin Brannen writes: >> The issue is that I've not been able to make Pg use our Perl (in >> /opt/perl) instead of the system one (in /usr). > >plperl.so will typically have a more or less hard-coded path to libperl.so, eg > >$ ldd ...installdir

trouble making PG use my Perl

We're trying to upgrade our Pg 9.6 installs up to 12.2. In a break from tradition where we grab source and compile our own, I've downloaded the community RPMs for Centos 6 and installed them (they default into /usr/pgsql-12 it seems). I can make Pg come up, initdb, that sort of stuff just fine.

RE: Add column with default value in big table - splitting of updates can help?

>From: Durumdara > >a.) >PG version is mainly 9.6, but some of the servers are 10.x or 11.x. >b.) >We have semi-automatic tool which get the a new modifications on databases, >and execute them at once by database. >So one SQL script by one database, under one transaction - whole or nothing. >If

RE: ERROR: COPY escape must be a single one-byte character (multi-delimiter appears to work on Postgres 9.0 but does not on Postgres 9.2)

>Adrian Klaver wrote: >On 11/13/19 4:40 PM, Brandon Ragland wrote: >> Hello, >> >> I have a Talend enterprise job that loads data into a PostgreSQL >> database via the COPY command. When migrating to a new server this >> command fails with the following error message: >> org.postgresql.util.PSQLExc

RE: Upgrade procedure

>> For us, we always use pg_upgrade even for minor updates because it >> feels safer to me. That being said, we rarely do minor updates and >> just do majors because upgrading is just hard enough (lots of >> testing!) we tend to wait and then jump further. Upgrading is known to >> take a maintenanc

RE: Upgrade procedure

>> >From: rihad >> >>> Hi, all. Why is it normally suggested to stop the server, upgrade it, >>> then start it? Wouldn't it be easier & quicker to simply upgrade the >>> package in-place and restart the service? On OSen that allow >>> modification of currently running binaries, which is most Unix

RE: Upgrade procedure

>From: rihad >Hi, all. Why is it normally suggested to stop the server, upgrade it, then >start it? Wouldn't it be easier & quicker to simply upgrade the package >in-place and restart the service? On OSen that allow modification of currently >running binaries, which is most Unix OS, M$ Windows

RE: SQL pretty pritner?

>From: Adrian Klaver >On 10/27/19 3:21 PM, stan wrote: >> I have a presentation later in the week, and i plan on printing out >> some files containing SQL commands. I have used some "pretty printers" >> in the past for things like Perl scripts. What I am thinking of s >> something that bolds keywo

RE: date function bug

From: Ravi Krishna > postgres=# select to_date('2018150X','MMDD'); > to_date > 2019-03-03 > postgres=# select to_date('20181501','MMDD'); > to_date > > 2019-03-03 is this a cut-n-paste mistake? Surprisingly (to me), no…. db=# select

RE: can't install pg 12 beta on centos 6

>From: Adrian Klaver >On 9/23/19 2:00 PM, Kevin Brannen wrote: >>> Adrian Klaver wrote: >>> On 9/23/19 12:04 PM, Kevin Brannen wrote: >>>> >>>> I’ve spun up a new VM with Centos 6.10 (the latest). I found >>>> https://yum.postgresql.o

RE: can't install pg 12 beta on centos 6

> Adrian Klaver wrote: > On 9/23/19 12:04 PM, Kevin Brannen wrote: > > I thought I’d get a jump on testing this since we’re a little slow > > sometimes. ?? > > > > I’ve spun up a new VM with Centos 6.10 (the latest). I found > > https://yum.postgresql.org/test

RE: citext, actually probably using extensions

> From: Morris de Oryx > > Not sure about best practices, but what I'm going is like this: > > * Create a schema named extensions. > * Install extensions in this special schema only. I don't put anything else > in there. > * Put the extensions schema early (left) in the search_path for each role.

can't install pg 12 beta on centos 6

I thought I’d get a jump on testing this since we’re a little slow sometimes. 😊 I’ve spun up a new VM with Centos 6.10 (the latest). I found https://yum.postgresql.org/testing/12/redhat/rhel-6-x86_64/repoview/ to get the packages (BTW, that was kind of hard to find). I ended up with: postgresql

RE: pg_restore issues with intarray

>From: Jerry Sievers > > >Try running \dx+ for intarray on one of your deviant systems. You may find >the item pg_dump is trying to be explicit about *missing* from the extension >member list. > >In such a case, see the ALTER EXTENSION ADD... which can be run manually to >register whatever is

RE: pg_restore issues with intarray

>From: Adrian Klaver >On 9/5/19 5:05 PM, Kevin Brannen wrote: >> >> It feels like the restore is adding the intarray extension, which does >> a CREATE OPERATOR FAMILY on its own, then later the restore does >> CREATE OPERATOR FAMILY on again causing the problem. Yet

RE: pg_restore issues with intarray

>From: Jerry Sievers > >>Kevin Brannen writes: >> >> It feels like the restore is adding the intarray extension, which does >> a CREATE OPERATOR FAMILY on its own, then later the restore does >> CREATE OPERATOR FAMILY on again causing the problem. Yet t

RE: SQL equivalint of #incude directive ?

> From: stan > > I thought this would be common. But a quick Google only revealed what look to > be workarounds. > > I am defining a bunch of functions, and I would prefer to store them in a > separate file, which then gets "source" by the main DB init file. > > Is there a standard way to do thi

RE: pg_restore issues with intarray

> On 9/5/19 4:24 PM, Adrian Klaver wrote: > > On 9/5/19 4:06 PM, Kevin Brannen wrote: > >>> From: Adrian Klaver > >>> > >>> On 9/5/19 2:57 PM, Kevin Brannen wrote: > >>>> I think I need some help to understand what’s going here b

RE: pg_restore issues with intarray

> From: Adrian Klaver > > On 9/5/19 2:57 PM, Kevin Brannen wrote: > > I think I need some help to understand what’s going here because I > > can’t figure it out and google isn’t helping. > > > > This is for Pg 9.6.5. (Yes, we are upgrading to 12.x in a few months

pg_restore issues with intarray

I think I need some help to understand what’s going here because I can’t figure it out and google isn’t helping… This is for Pg 9.6.5. (Yes, we are upgrading to 12.x in a few months, or so the plan is.) Pg code came from the community and we compiled it with no changes. This is on Centos 6.7, t

RE: SELECT INTO question

-Original Message- From: Tom Lane Sent: Thursday, July 25, 2019 2:47 PM To: Adrian Klaver Cc: Kevin Brannen ; pgsql-generallists.postgresql.org Subject: Re: SELECT INTO question Adrian Klaver mailto:adrian.kla...@aklaver.com>> writes: > On 7/25/19 12:23 PM, Kevin Bran

SELECT INTO question

Hi, We're trying to understand what happened with a SELECT INTO. The problem can be see with this example: # create table t1 (id int, v int); CREATE TABLE # insert into t1 (select x, x from generate_series(1, 5) as g(x)); INSERT 0 5 # select * from t1; id | v + 1 | 1 2 | 2 3 |

RE: Need a DB layout gui

From: David Gauthier > I've been poking around > https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools to > see if there is anything that can read PG metadata in and display it > graphically in a gui. You know, the kind of picture that is 1 block per > table with 1->many a

RE: [EXT EMAIL] Re: First Time Starting Up PostgreSQL and Having Problems

> From: Tom Lane Brent Bates writes: > > I found the problem. I cleared everything out and started from > > scratch, then slowly added my changes back to the configuration files. The > > problem was in the postgresql.conf. At the bottom of the file I had > > uncommented all the ‘includ

RE: Drive Architecture for new PostgreSQL Environment

From: Hilbert, Karin Hello, We're in the process of building a new PostgreSQL environment on Scientific Linux release 7.6. The new environment will have a Primary & 2 Standby servers & have asynchronous replication. It will use repmgr to manage failover/switchover events. In the past,

RE: Recommendation to run vacuum FULL in parallel

From: Perumal Raj So conclude the requirement here , The only way to parallelism is multiple script. And no need to do REINDEX exclusively. Question : Do we need to consider Table dependencies while preparing script in order to avoid table locks during vacuum full ? We have a small bash scrip

RE: Key encryption and relational integrity

-Original Message- From: Moreno Andreo Sent: Tuesday, March 26, 2019 11:09 AM To: Adrian Klaver ; PostgreSQL mailing lists Subject: Re: Key encryption and relational integrity In a master-detail relation, I need to encrypt one of master table PK or detail table FK, in order to achieve

RE: How to always run UPDATE FROM despite missing records in the source table?

From: Alexander Farber * The only workaround that I could think of is - UPDATE users SET visited = now(), ip = '20.20.20.20'::inet, lat = (SELECT lat FROM geoip WHERE '20.20.20.20'::inet <<= block), lng = (SELECT lng FROM geoip WHERE '20.20.20.20'::inet <<= bloc

RE: Immutable way to cast timestamp TEXT to DATE? (for index)

From: Ken Tanzer On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth mailto:and...@tao11.riddles.org.uk>> wrote: > "Ken" == Ken Tanzer mailto:ken.tan...@gmail.com>> > writes: Ken> Hi. I've got a text field in a table that holds this style of Ken> timestamp: Ken> 2014-10-23T00:00:00 You c

RE: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

From: chiru r > I have tried to intall the RPMs with -relocate option,however it is not > working as expected and throwing below error. > > [root@Server1dev:/root/PG11]# > #-> rpm -ivh --relocate /usr/pgsql-11/=/u01/postgres/pg11_relocate/ > postgresql11-server-11.1-1PGDG.rhel7.x86_64.rpm > war

RE: Relocatable Binaries (RPMs) : custom installation path for PostgreSQL

From: chiru r > I have installed Community PostgreSQL RPMs and are going into " > /usr/pgsql-11/" by default. > Please let us know how to get the PostgreSQL-11 installed in above custom > paths using RPMs? . I've never tried it, but look at the "--relocate" option for the rpm command. The "

RE: Watching for view changes

From: Mitar > On Thu, Dec 20, 2018 at 12:07 PM Rob Sargent wrote: > > Are you hoping to see the difference in the returned values for successive > > calls to the same query? > > i would like to in as close to real-time as possible get notification when > results of a query would have changed i

why use phpPgAdmin (was: RE: Importing tab delimited text file using phpPgAdmin 5.1 GUI)

> From: Tony Shelver > Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free and > readily available? > It also has a graphical table-from-file loader as well. I can’t speak for the original poster, but there are multiple reasons that might be: * You came from the mysql wor

RE: syntax error with alter type

Tom Lane wrote: > Kevin Brannen writes: > > So, can you (or anyone) help me understand what "alter type drop > > attribute" is meant to do? I don't see "attribute" on the page you > > reference. > IIRC, that drops a column from a composite

RE: syntax error with alter type

On 12/7/18 3:17 PM, Kevin Brannen wrote: > I'm running Pg 9.6.5 if it matters... > > I'm trying to drop a value from an ENUM (type) and it seems like I'm > following the fine manual yet I still get an error. For example: > > nms=# create type alphabet as enum (&#x

syntax error with alter type

I'm running Pg 9.6.5 if it matters... I'm trying to drop a value from an ENUM (type) and it seems like I'm following the fine manual yet I still get an error. For example: nms=# create type alphabet as enum ('a', 'b', 'c', 'd'); CREATE TYPE nms=# alter type alphabet drop attribute if exists 'c'

RE: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

From: Raghavendra Rao J S V [mailto:raghavendra...@gmail.com] Sent: Thursday, October 18, 2018 7:26 AM To: pgsql-general@lists.postgresql.org Subject: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB Hi All, We are using pg_dump backup utility in order to take t

RE: Improving pg_dump performance

-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, July 23, 2018 8:56 AM To: Ron Cc: pgsql-general@lists.postgresql.org Subject: Re: Improving pg_dump performance On 07/23/2018 06:47 AM, Ron wrote: > On 07/23/2018 08:46 AM, Stephen Frost wrote: >> Gree

RE: dumping only table definitions

On Fri, Jun 29, 2018 at 6:30 PM, Kevin Brannen mailto:kbran...@efji.com>> wrote: I’m trying to figure out how to dump only the table definitions, well those and things they need directly, like sequences & types. What I do NOT want are all the millions (not literally but it feels lik

dumping only table definitions

I'm trying to figure out how to dump only the table definitions, well those and things they need directly, like sequences & types. What I do NOT want are all the millions (not literally but it feels like it :)) of functions we have. Triggers would be all right if I must, as we only have a few of

RE: Load data from a csv file without using COPY

From: Ravi Krishna [mailto:srkris...@yahoo.com] Sent: Tuesday, June 19, 2018 4:15 PM To: Steve Atkins Cc: PG mailing List Subject: Re: Load data from a csv file without using COPY > > If performance is relevant then your app should probably be using COPY > protocol, not line by line inserts. It'

RE: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

From: Eugene Poole [mailto:etpool...@comcast.net] Sent: Tuesday, December 05, 2017 12:59 PM To: pgsql-gene...@postgresql.org Subject: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ?? I've asked most of my OS based questions on another mail list, but I think this is more specific: