Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/22/20 5:12 PM, Adrian Klaver wrote: On 2/22/20 2:39 PM, Andrus wrote: [snip] This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software. I don't know where

Re: R: Postgresql 12.x on Windows (vs Linux)

Your comment assumes that OP does *not* have have a site license, and *does* have Linux expertise.  Neither assumption is always valid. (And, of course, the Windows server might already exist.) On 3/2/20 9:06 AM, Tim Clarke wrote: But why even bother paying for MS licenses? Postgres runs like

Re: R: Postgresql 12.x on Windows (vs Linux)

Linux "expertise" is 1/10 the cost of Windows expertise. Time to plan for getting rid of the site license. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 02/03/2020 15:32, Ron wrote: Your comment assumes that OP does *not* have have a site license, and *

Re: Advice request : simultaneous function/data updates on many databases

On 3/4/20 4:33 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the s

Re: Real application clustering in postgres.

On 3/5/20 6:07 AM, Laurenz Albe wrote: On Thu, 2020-03-05 at 07:45 +, Daulat Ram wrote: Is there any possibility/options to setup a real application clustering in Postgres as in Oracle we have a RAC feature. No, and as far as I know nobody feels interested in providing it. RAC is a compl

Re: Real application clustering in postgres.

On 3/6/20 8:55 AM, Laurenz Albe wrote: On Fri, 2020-03-06 at 03:25 -0600, Ron wrote: RAC is not really a high availability solution: because of the shared storage, it has a sibgle point of failure. This is utter nonsense. Dual redundant storage controllers connected to disks in RAID-10

Re: duplicate key value violates unique constraint

Asking the same question on multiple lists is strongly frowned upon. On 3/7/20 1:29 PM, Ashkar Dev wrote: Hi all, how to fix a problem, suppose there is a table with id and username if I set the id to bigint so the limit is 9223372036854775807 if I insert for example 3 rows id    username -- 

Re: How to discover what table is

On 3/8/20 12:26 PM, PegoraroF10 wrote: I have a publication/subscription replication. Then this week started to see this message on Log of replica server. Message is "duplicate key value violates unique constraint "pksyslookup"" Detail is "Key (lookup_id)=(56) already exists." and on production

Re: Migrating from Db2

On 3/12/20 3:35 PM, Joe Jess wrote: We are evaluating migrating from Db2. Would appreciate if you can point me to useful resources.  And any guidance on things to what out for. Our set up - About 100+ tables - 300+ user definable functions and tables.  Mainly for analysis and reporting. - S

Re: vacuum full doubled database size

This is why I'd VACUUM FULL in a planned manner, one or two tables at a time, and *locally* from crontab. On 3/13/20 8:41 AM, Zwettler Markus (OIZ) wrote: We did a "vacuum full" on a database which had been interrupted by a network outage. We found the database size doubled afterwards. Aut

Re: vacuum full doubled database size

On 3/13/20 10:32 AM, 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 lock

Re: Temporary tablespaces on a RAM disk

On 3/17/20 12:19 PM, David G. Johnston wrote: On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) > wrote: is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archiv

Re: Passwordcheck configuration

On 3/19/20 6:19 PM, Tom Lane wrote: Dave Hughes writes: I have a requirement to set some password complexity for our database such as length of password, upper case, lower case, special characters, expiration limit, reuse, etc. Usually, if you have to do something like that, we recommend setti

Re: Could postgres12 support millions of sequences? (like 10 million)

On 3/21/20 12:02 PM, Rob Sargent wrote: On Mar 21, 2020, at 10:47 AM, Adrian Klaver wrote: On 3/20/20 8:13 PM, pabloa98 wrote: Nothing I saw that said int could not become bigint. My bad. The code cannot be a bigint. Or it could be a bigint between 1 to :) Aah, that was the cou

Re: Ident authentication failed

You're only allowing "trust" authentication, not "ident" authentication. On 3/26/20 2:29 PM, Ted To wrote: Hi, I'm trying to configure miniflux and am struggling to figure out how to configure postgres for that purpose.  (The miniflux instructions are very terse.)  Regardless of whether I ru

Re: Postgres 12 backup in 32 bit windows client

On 3/28/20 12:57 PM, Andrus wrote: Hi! What are you going to do with this backup? If server disk crashes, it can used to restore data. Do you have shell access to the Debian machine in order to do the backup there? I have but my application user in 32bit windows does not have. I want to a

Re: keeping images in a bytea field on AWS RDS

On 3/31/20 2:49 PM, Richard Bernstein wrote: I am using postgresql on RDS. I need to upload an image to the table. I understand that I need to set the PGDATA directory and place the image file in it, before setting the path in the bytea field. But how do I set PGDATA if I don't have the ability

Re: how to slow down parts of Pg

What you need is async replication instead of synchronous replication. On 4/21/20 3:30 PM, 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. We have systems that are geo-

Re: psql \copy

You might want to investigate pg_bulkload for this activity. On 4/24/20 10:55 AM, Steve Clark wrote: Hello, I am using psql to copy data extracted from an InfluxDB in csv format into postgresql. I have a key field on the time field which I have defined as a bigint since the time I get from I

Re: Practical usage of large objects.

Our databases use bytea instead.  (I don't know why the application vendor decided on that.) On 5/13/20 12:53 PM, Dmitry Igrishin wrote: Hello all, As you know, PostgreSQL has a large objects facility [1]. I'm curious are there real systems which are use this feature? I'm asking because and I'

Re: Inherited an 18TB DB & need to backup

For a database that size, I'd install pgbackrest, since it features parallel backups and compression.  With it, I'd do monthly full backups with daily differential backups. (If it's mostly historical data, I'd split the database into multiple instances, so that older data rarely needs to be ba

Re: Inherited an 18TB DB & need to backup

On 5/15/20 6:01 PM, Michael Nolan wrote: On Fri, May 15, 2020 at 12:51 PM Ravi Krishna > wrote: Why should the backup land in S3, and not local somewhere? Any good reason why one should pay for the additional storage and transfer costs? Good q

Re: Inherited an 18TB DB & need to backup

On 5/16/20 7:18 AM, Rob Sargent wrote: O Another problem is storage devices fail. S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations so you're not vulnerable to a site disaster. Tape FTW!! Or WTF Tape??

Re: Inherited an 18TB DB & need to backup

On 5/16/20 3:30 PM, Gavin Flower wrote: On 17/05/2020 08:12, Ron wrote: On 5/16/20 7:18 AM, Rob Sargent wrote: O Another problem is storage devices fail.  S3 storage lakes _should_ be checking your data integrity on a regular basis and possibly maintaining copies of it iin multiple locations

Re: Huge tables, trying to delete OID's taking 6+hours per table

On 5/19/20 11:51 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 6:40 AM Tom Lane > wrote: Tory M Blue mailto:tmb...@gmail.com>> writes: > The command i'm using is > ALTER TABLE tablename SET WITHOUT OIDS; > Would a drop column oid be better? Unf

Re: Huge tables, trying to delete OID's taking 6+hours per table

On 5/20/20 11:22 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 10:06 AM Ron <mailto:ronljohnso...@gmail.com>> wrote: On 5/19/20 11:51 AM, Tory M Blue wrote: On Tue, May 19, 2020 at 6:40 AM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Tory M Blue mailto:t

Re: pg_dump crashes

> pg_dump: The command was: COPY public.file (fileid, fileindex, jobid, pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout; What happens when you run that COPY ... TO stdout; command (but redirecting it to /dev/null)? On 5/22/20 8:40 AM, Nico De Ranter wrote: I was just trying that. 

Re: Oracle vs. PostgreSQL - a comment

On 6/1/20 4:58 AM, Peter J. Holzer wrote: [snip] As a developer (and part time DBA) I have a hard time thinking of any Oracle feature that I'm missing in PostgreSQL. The inability to do a point-in-time restoration of a *single* database in a multi-db cluster is a serious -- and fundamental --

Re: Oracle vs. PostgreSQL - a comment

On 6/2/20 3:27 AM, Tim Clarke wrote: On 02/06/2020 09:22, Ron wrote: The inability to do a point-in-time restoration of a *single* database in a multi-db cluster is a serious -- and fundamental -- missing feature (never to be implemented because of the fundamental design). In SQL Server

Re: Oracle vs. PostgreSQL - a comment

On 6/2/20 4:59 AM, Grigory Smolkin wrote: On 6/2/20 11:22 AM, Ron wrote: The inability to do a point-in-time restoration of a *single* database in a multi-db cluster is a serious -- and fundamental -- missing feature (never to be implemented because of the fundamental design). It is

Re: Oracle vs. PostgreSQL - a comment

On 6/2/20 1:30 PM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 6/2/20 4:59 AM, Grigory Smolkin wrote: On 6/2/20 11:22 AM, Ron wrote: The inability to do a point-in-time restoration of a *single* database in a multi-db cluster is a serious -- and fundamental

Re: Oracle vs. PostgreSQL - a comment

On 6/2/20 1:56 PM, Tim Clarke wrote: On 02/06/2020 19:43, Stephen Frost wrote: But require a new port, and Enterprises have Processes that must be followed. Sure they do. Automate them. :) Thanks, Stephen +1 for automation, isoX != slow It is when FW rules must be manually approved (and t

Re: Can we get SQL Server-like cross database queries

On 6/3/20 2:57 PM, Rob Sargent wrote: On Jun 3, 2020, at 1:46 PM, Tom Lane wrote: Guyren Howe writes: Is it practical to provide the SQL Server-like feature in Postgres? No. regards, tom lane That got me chuckling. I had just decided not to bother posting, but wel

Re: Oracle vs. PostgreSQL - a comment

On 6/5/20 10:02 AM, Achilleas Mantzios wrote: On 5/6/20 5:19 μ.μ., Thomas Kellerer wrote: Achilleas Mantzios schrieb am 05.06.2020 um 14:05: Plus PG does not directly support cross database queries using 3 part name, something sqlserver excels at. Maybe because SQL server does not have real d

Re: When to use PARTITION BY HASH?

On 6/5/20 8:51 AM, Jeff Janes wrote: On Fri, Jun 5, 2020 at 6:12 AM Oleksandr Shulgin mailto:oleksandr.shul...@zalando.de>> wrote: [snip] For a bulk load you'd likely want to go with an empty partition w/o indexes and build them later, after loading the tuples. That only works if the

Re: When to use PARTITION BY HASH?

On 6/8/20 3:40 AM, Oleksandr Shulgin wrote: [snip] I've found the original commit adding this feature in version 11: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1aba8e651ac3e37e1d2d875842de1e0ed22a651e It says: "Hash partitioning is useful when you want to partition a growing

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

On 6/8/20 3:14 AM, Thorsten Schöning wrote: Hi all, I have an app exposing web services to generate reports. Those web services accept multiple reports per request and calculate them concurrently. There's one transaction spanning each entire request and used by ALL spawned threads. The app makes

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

On 6/8/20 9:23 AM, Thorsten Schöning wrote: Guten Tag Ron, am Montag, 8. Juni 2020 um 15:35 schrieben Sie: Would a set of GLOBAL temporary tables be a better fit for your problem (eliminating the need to create temporary tables each time)? Do you mean plain tables simply created using CREATE

Returning SELECTed rows immediately instead of all at the end?

I'm running amcheck on a set of indices (test machine, not prod) and want to track the progress.  Is there a SELECT clause that makes rows display as they are created, or do I have to explicitly call bt_index_check() from a shell script or SQL function in order to see the output as each index

Re: Returning SELECTed rows immediately instead of all at the end?

On 6/13/20 12:10 AM, Tom Lane wrote: Ron writes: I'm running amcheck on a set of indices (test machine, not prod) and want to track the progress.  Is there a SELECT clause that makes rows display as they are created, or do I have to explicitly call bt_index_check() from a shell script o

Re: Returning SELECTed rows immediately instead of all at the end?

On 6/13/20 12:04 AM, David G. Johnston wrote: On Friday, June 12, 2020, Ron <mailto:ronljohnso...@gmail.com>> wrote: I'm running amcheck on a set of indices (test machine, not prod) and want to track the progress.  Is there a SELECT clause that makes rows displ

Re: Oracle vs. PostgreSQL - a comment

On 6/13/20 1:46 PM, Bruce Momjian wrote: On Wed, Jun 3, 2020 at 08:53:45PM +0200, Andreas Joseph Krogh wrote: I agree these are all technical issues, but nevertheless - "implementation details", which DBAs don't care about. What's important from a DBA's perspective is not whether WAL is cluster

Re: BUG #11141: Duplicate primary key values corruption

9.5.5 is pretty old.  I'd strongly think about updating to one of the *seventeen* subsequent patch releases. https://www.postgresql.org/docs/release/9.5.22/ On 6/14/20 2:17 AM, Abraham, Danny wrote: Hi, PG 9.5.5 on AIX tends to compromise Primary Key and create duplicates when in stress and m

Re: Getting error on

On 6/15/20 8:43 AM, M Tarkeshwar Rao wrote: Hi all, I have the c++ postgres client. When I restarted my client process I am getting following error on insertion. Can you please suggest why this error is coming and how to resolve it? The error hint says how to resolve it. 2020-04-17 06:3

Re: Netapp SnapCenter

On 6/18/20 11:49 AM, Paul Förster wrote: Hi Magnus, On 18. Jun, 2020, at 16:19, Magnus Hagander wrote: I don't know specifically about SnapCenter, but for snapshots in general, it does require backup mode *unless* all your data is on the same disk and you have an atomic snapshot across that

Re: The backup API and general purpose backup software

Peter, I don't understand that last step "5. copy the result from the previous step to the backup medium."  It seems to be a duplication of "3. copy the contents of the data directory to the backup medium". On 6/21/20 8:28 AM, Peter J. Holzer wrote: This is inspired by the thread with the su

Re: The backup API and general purpose backup software

On 6/21/20 10:45 AM, Peter J. Holzer wrote: On 2020-06-21 10:32:16 -0500, Ron wrote: On 6/21/20 8:28 AM, Peter J. Holzer wrote: To make a full backup with the "new" (non-exclusive) API, a software must do the following 1. open a connection to the database 2. invoke pg_start_bac

Re: Can the current session be notified and refreshed with a new credentials context?

On 6/22/20 4:07 PM, AC Gomez wrote: Suppose you have the following scenario: 1: Call some function with a certain user and password 2: From inside that function, have several calls using DBLink 3: At some point during the running of that function a password rotation(a separate process) comes al

Re: n_distinct off by a factor of 1000

Maybe I missed it, but did you run "ANALYZE VERBOSE bigtable;"? On 6/23/20 7:42 AM, Klaudie Willis wrote: Friends, I run Postgresql 12.3, on Windows. I have just discovered a pretty significant problem with Postgresql and my data.  I have a large table, 500M rows, 50 columns. It is split in 3

Re: UPDATE on 20 Million Records Transaction or not?

On 6/23/20 8:32 AM, Jason Ralph wrote: Hello List, PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (R ed Hat 4.4.7-23), 64-bit I am planning an update on a table with 20Million records, I have been researching the best practices.  I will remove all indexes and

Re: UPDATE on 20 Million Records Transaction or not?

ithout a transaction? >Always make a backup. Agreed How would you guys do it? >It depends on what percentage of the rows are being updated, which columns are being updated and how big the records are. Please see above, thanks *Jason Ralph* *From:* Ron *Sent:* Tuesday, June 23, 20

Re: survey: psql syntax errors abort my transactions

On 7/3/20 1:54 AM, Laurenz Albe wrote: On Thu, 2020-07-02 at 08:54 -0700, Jeremy Schneider wrote: Maybe it's just me, but I'm wondering if it's worth changing the default behavior of psql so it doesn't abort transactions in interactive mode when I mistakenly mis-spell "select" or something sill

Re: Degraded performance during table rewrite

On 7/3/20 3:24 PM, Mohamed Wael Khobalatte wrote: Hi all, I am attempting to do a bunch of table rewrites to shrink a table in the absence of pg_repack and vacuum full (both not an option). The database is growing fast and has had significant bloat in both heaps and indexes, so index rebuilds

Re: Clustering solution ?

For high availability, for load sharing or for disaster recovery? On 7/9/20 8:53 AM, Laurent FAILLIE wrote: Hello, I've been asked by one of my customer to know which are the possible clustering solution for PostgreSQL ? Active/passive ? Active/active ? if possible free or not too expensive

Re: backup -restore question

On 7/13/20 2:32 PM, Adrian Klaver wrote: On 7/13/20 12:12 PM, Julie Nishimura wrote: Hello there, One of our PostgreSQL 9.4.1  databases has been backed up as *.gz file with the compression 9 "pg_dump -Z 9". What is the right format of restore this file when needed? Can I run the restore from

Re: backup -restore question

On 7/13/20 7:37 PM, Adrian Klaver wrote: On 7/13/20 2:56 PM, Ron wrote: On 7/13/20 2:32 PM, Adrian Klaver wrote: On 7/13/20 12:12 PM, Julie Nishimura wrote: Hello there, One of our PostgreSQL 9.4.1  databases has been backed up as *.gz file with the compression 9 "pg_dump -Z 9". W

Re: 2 million queries against a table

On 7/15/20 10:10 AM, Adam Sanchez wrote: Hi I need to run 2 million queries against a three columns table t (s,p,o) which size is 10 billions rows. The data type of each column is string. The server has 512G RAM, 32 cores and 14T SSD (RAID 0) Only two types of queries: select s p o from t whe

Re: Security Vulnerability on PostgreSQL VMs

There has to be some "yum" or "rpm" option to show what depends on those packages. On 7/17/20 10:44 AM, Hilbert, Karin wrote: We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various Linux VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) OS.  We're also running repmgr v

Re: Problem with pg_service.conf

On 7/21/20 4:35 PM, Michał Lis wrote: Hello, I can't connect to a database via service defined in pg_service.conf file from remote machine. Connection from local machine using this service definition works fine. I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the localizati

Re: TDE implementation in postgres which is in docker container

On 7/24/20 11:38 AM, Vasu Madhineni wrote: Hi All, How to implement TDE in postgres which is running docker containers. Postgres does not have TDE.  You need to encrypt at the filesystem level. -- Angular momentum makes the world go 'round.

Re: How to rebuild index efficiently

On 8/3/20 12:58 PM, Christophe Pettus wrote On Aug 3, 2020, at 10:20, Konireddy Rajashekar wrote: Could you please suggest any ideal approach to tackle this ? You can do CREATE INDEX CONCURRENTLY to build a new index with the same definition, and when that is complete, drop the old index. The

Re: ERROR: canceling statement due to user request

File a bug report to that these messages are Informationals instead of errors. On 8/5/20 5:50 AM, FOUTE K. Jaurès wrote: Ok, Just want if we can avoid this error message, With some config for exemple ? Le mer. 5 août 2020 à 11:07, Paul Förster > a écrit :

Re: SQL Question about like

On 8/10/20 11:37 AM, p...@gmx.de wrote: Hello, my SQL question is, why psql doesn't return the record? create table lll (text char(100)); insert into lll (text) values ('10% - Ersthelfer'); select * from lll where text like '10% - Ersthelfer'; Other databases (Maria, SQL Server, YARD) do this

Re: UUID or auto-increment

On 8/10/20 11:38 AM, Ravi Krishna wrote: [snip] Finally UUID results in write amplication in wal logs.  Keep that in mind if your app does lot of writes. Because UUID is 32 bytes, while SERIAL is 4 bytes? -- Angular momentum makes the world go 'round.

Re: Bytea Example

OP originally wrote that he needs to exports and import them, so presumably he's just using psql. On 8/16/20 12:33 PM, Francisco Olarte wrote: Naveen: 1st, do not top post, it is frowned upon here. AAMOF I was tempted to skip your message due to this, as it makes replying too hard. AK aske

Re: Point in time recovery

Search the log file for errors? Query the database(s) to verify that the latest data s there? On 8/18/20 5:10 AM, Daulat Ram wrote: Hello Team, I want to know the best way to ensure/verify that the Point in time recovery has done successfully after the crash and the restore. Thanks, --

Re: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 9:57 AM, Ko, Christina wrote: -Original Message- From: Adrian Klaver Sent: Thursday, August 20, 2020 10:42 AM To: Ko, Christina (US) ; pgsql-general@lists.postgresql.org Cc: Ho, Chuong Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql On 8/20/20 7:30 AM, Ko,

Re: Postgres DB hacked.

On 8/30/20 11:58 PM, Yogesh Sharma wrote: Dear All, Thanks for your support. We faced the below issue and our all DB backup data has lost.  It seems Hacker deleted all the tables in the DB and created a single table called "warning". when I am trying to select this table so it's showing Error

Re: compatibility matrix between client and server

On 9/7/20 9:06 AM, Laurenz Albe wrote: [snip] However, if I were you, I'd refuse to support any PostgreSQL major version that is no longer supported by the project: https://www.postgresql.org/support/versioning/ So, nothing older than 9.5. That's grossly unrealistic. -- Angular momentum makes

Re: Autovacuum of independent tables

On 9/8/20 3:27 AM, Michael Paquier wrote: On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote: Autovacuum does not clean dead tuples of closed transactions in tableB while there is an open transaction on tableA. But the tables have nothing in common. They are handled by separate a

Re: Effective IO Concurrency

On 9/14/20 11:03 AM, Laurenz Albe wrote: On Mon, 2020-09-14 at 10:39 -0300, luis.robe...@siscobra.com.br wrote: In PostgreSQL 13, the way of using effective_io_concurrency has changed. Until v12, I used 200 for this value (using SSD drives). Using the new formula described in https://www.pos

Re: Obvious data mismatch in View2 which basically SELECT * from View1

On 9/15/20 10:40 PM, Ben wrote: Dear list, Recently I am getting feedback, data in my analytic report is not repeatable. From time to time they get different data for the same time span. (but IIRC previously it was OK). Therefore I started debuging the View chain for that report, during which

Re: How to write such a query

On 9/18/20 1:49 PM, Igor Korot wrote: Hi, Adrian, On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver wrote: On 9/18/20 10:46 AM, Igor Korot wrote: Hi, Johnathan, On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong mailto:jonathanrstr...@gmail.com>> wrote: Are you looking to arbitrarily update

Re: How to write such a query

On 9/18/20 3:18 PM, Igor Korot wrote: Hi, Ken, On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer > wrote: > How to find what the primary key (or UNIQUE identifier) value is > for row 5 in the recordset? You're missing the point: as mentioned befor

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

On 9/24/20 6:20 PM, Bruce Momjian wrote: On Wed, Sep 23, 2020 at 12:28:14AM +0200, tutilu...@tutanota.com wrote: Sep 21, 2020, 7:53 PM by j...@commandprompt.com: See my comment about Google. The information is out there and easy to find. I guess I'm the worst idiot in the world, then, who

Re: Returning timestamp with timezone at specified timezone irrespective of client timezone

On 9/27/20 4:16 PM, aNullValue (Drew Stemen) wrote: Hello, I've attempted to obtain help with this problem from several other places, but numerous individuals recommended I ask this mailing list. What I need is for the ability to return a timestamp with timezone, using the UTC offset that co

Re: DOMAIN/composite TYPE vs. base TYPE

On 9/28/20 4:31 PM, Joe Abbate wrote: Hello Rob, On 28/9/20 17:17, Rob Sargent wrote: just record all three fields (day, month, year) with nulls and do the to-date as needed. That is not sufficient.  An earlier implementation had something like a CHAR(8) to record MMDD, but how can you i

Re: Restoring a database problem

On 9/30/20 7:11 PM, Bruce Momjian wrote: On Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote: I have had to do this so rarely and it has almost always been in a bit of a panic so may well be missing something really obvious. What I want to know is how to quiese a database to that I can

Re: What version specification used by PG community developers?

On 10/7/20 2:52 AM, WanCheng wrote: Is same to the SemVer?(https://semver.org/) It used to be, but starting with v10 it's MAJOR PATCH -- Angular momentum makes the world go 'round.

Re: precautions/prerequisites to take for specific table

On 11/5/20 7:49 AM, Vasu Madhineni wrote: Hi All, In my organisation a newly built project application team requirement on tables like have a column (text type), with size can reach around 3 MB, and 45 million records annually. Are there any specific precautions/prerequisites we have to take

Re: New "function tables" in V13 documentation

On 11/9/20 2:47 PM, David G. Johnston wrote: On Mon, Nov 9, 2020 at 1:41 PM Tom Lane > wrote: Alvaro Herrera mailto:alvhe...@alvh.no-ip.org>> writes: > On 2020-Nov-08, Adrian Klaver wrote: >> Yeah, I would agree with the mobile first design comments. Then

Re: New "function tables" in V13 documentation

On 11/9/20 3:05 PM, David G. Johnston wrote: On Mon, Nov 9, 2020 at 2:01 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: My suggestion is to add a "table of contents" at the top of non-trivial sections that simply lists available functions by name (generall

Re: identify partitioning columns and best practices of partitioning in prod enviornments

On 11/11/20 4:31 PM, Atul Kumar wrote: Hi, I want to about best practices of partitioning in prod environments and how to identify partitioning columns. It depends on what you want to do.  If your purpose is to simplify the deletion of old records, then partition by an unchanging date field.

Re: vacuum vs vacuum full

On 11/18/20 2:33 AM, Atul Kumar wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: 1. What should be perform on the table Vacuum or Vacuum full ? The documentation *clearly states* the difference between VACUUM and

Re: vacuum vs vacuum full

On 11/18/20 3:41 AM, Olivier Gautherot wrote: Hi Atul, On Wed, Nov 18, 2020 at 9:33 AM Atul Kumar > wrote: Hi, We have a table of 3113GB, and we are planning to vacuum it in non business hours i.e. 12AM to 4AM, So my queries are: [snip] 3. Wil

Re: vacuum vs vacuum full

On 11/18/20 6:02 AM, Laurenz Albe wrote: On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote: No matter how long it takes, this is an excellent argument for partitioning Very Large Tables: many maintenance tasks are made *much* easier. The problem is, you can't partition every table as lon

Re: psql backward compatibility

On 11/18/20 10:13 AM, Adrian Klaver wrote: On 11/18/20 8:05 AM, Stephen Haddock wrote: Hello, When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? It looks like the recommended method is to dump t

Re: psql backward compatibility

On 11/18/20 10:30 AM, Laurenz Albe wrote: On Wed, 2020-11-18 at 11:05 -0500, Stephen Haddock wrote: When upgrading an older version of postgres, version 8.4 for example, to a newer version such as 9.6, does the data have to be migrated immediately? Since nobody mentioned that explicitly: do n

Re: pg_dump - how to force to show timestamps in client log

On 11/20/20 2:56 PM, Adrian Klaver wrote: On 11/20/20 10:01 AM, Durumdara wrote: Hello! We need to log the pg_dump's state. What objects are in copy, and what are the starting and ending times. But when I try to redirect the output, the result doesn't have timestamps. PG 11, on Windows. As I

Re: pg_dump - how to force to show timestamps in client log

On 11/20/20 3:39 PM, Adrian Klaver wrote: On 11/20/20 1:00 PM, Ron wrote: On 11/20/20 2:56 PM, Adrian Klaver wrote: On 11/20/20 10:01 AM, Durumdara wrote: Hello! We need to log the pg_dump's state. What objects are in copy, and what are the starting and ending times. But when I t

Re: pg_dump - how to force to show timestamps in client log

27;s been a while since I tried to monitor a running pg_dump.  Also, if you redirect the output to a file then doesn't the file's timestamp get updated each time something happens. (That's what I used to do with Oracle before they added timestamps). On Fri, Nov 20, 2020 at 5:0

Re: Performance hit if I create multiple DBs on same instance

On 11/24/20 8:36 AM, David Gauthier wrote: Hi: 11.3 on linux I've come up with a plan to archive data from my main DB which involves creating other DBs on the same server.  But even though there will be zero activity on the archive DBs in terms of insert/update/delete, and almost no activity

Re: Performance hit if I create multiple DBs on same instance

On 11/25/20 8:59 AM, Laurenz Albe wrote: On Tue, 2020-11-24 at 11:34 -0600, Ron wrote: And if you're afraid of autovacuum and autoanalyze stealing resources, then disable them (at the table level). Ugh, bad advice. Better would be to VACUUM (FREEZE) these static table once, then autov

Re: Is the Halloween problem an issue in Postgres

On 12/2/20 4:23 PM, raf wrote: On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver wrote: On 12/2/20 2:02 PM, Thomas Kellerer wrote: guy...@icloud.com schrieb am 02.12.2020 um 21:27: The Halloween problem is that it is a challenge for the database if you’re updating a field that is also

pg_dump of partitioned table not working.

What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version pg_dump (PostgreSQL) 12.5 (Ubuntu 12.5-1.pgdg18.04+1) postgres=# \d+ measurement    Partitioned table "public.measurement"   Column

Re: pg_dump of partitioned table not working.

On 12/2/20 5:42 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron <mailto:ronljohnso...@gmail.com>> wrote: What am I missing? postgres=# \d+ measurement    Partitioned table "public.measurement" $ /usr/lib/postgr

Re: pg_dump of partitioned table not working.

On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron <mailto:ronljohnso...@gmail.com>> wrote: What am I missing? (Specifying the whole file name because multiple versions are installed.) $ /usr/lib/postgresql/12/bin/pg_dump --version

Re: pg_dump of partitioned table not working.

On 12/2/20 5:49 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Tom Lane > wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature.

Re: pg_dump of partitioned table not working.

On 12/2/20 6:08 PM, David G. Johnston wrote: On Wed, Dec 2, 2020 at 5:06 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: On 12/2/20 5:35 PM, David G. Johnston wrote: On Wednesday, December 2, 2020, Ron mailto:ronljohnso...@gmail.com>> wrote: Wh

Re: pg_dump of partitioned table not working.

On 12/2/20 5:50 PM, Tom Lane wrote: I wrote: What you need here is something like "pg_dump -t measurement*" to indicate that you want measurement's child tables too, but AFAIR pg_dump has no such feature. Maybe we should add it. Or actually: that syntax does do something, but it selects tables

<    1   2   3   4   5   6   7   8   9   10   >