Re: Accounting for between table correlation

On 1/15/21 9:19 AM, Alexander Stoddard wrote: I am having ongoing trouble with a pair of tables, the design of which is beyond my control. There is a 'primary' table with hundreds of millions of rows. There is then a 'subclass' table ~ 10% of the primary which has additional fields. The

Re: upgrade postgres 9.5 to 9.6

On 1/15/21 9:17 AM, Thomas Kellerer wrote: [snip] Is there a reason you are upgrading to 9.6 and not 12 or 13? The application software vendor might only certify the application on 9.6. Or /management/, in their infinite "wisdom", might insist on a One-version upgrade. Or 9.6 might be the

Re: How to REMOVE a fillfactor set by accident?

On 12/31/20 2:13 PM, Thorsten Schöning wrote: Hi all, I'm using the GUI tool pgModeler to maintain schemas and at some point in the past it used fillfactor 10 for some indexes by default. That seems to have changed in the meantime and most of my indexes don't have ANY fillfactor set explicitly.

Re: PgAdmin 4 GUI not responding

https://www.pgadmin.org/support/list/ On 2/4/21 3:46 AM, Adith Suresh wrote: Hi Team, I have installed postgres 13 on windows server and I am facing an issue while opening PgAdmin 4. The GUI is not responding. I have tried restarting the service, still the issue is persisting. Please

Re: Postgres 9.3 service no longer starts on Windows 8.1

Obligatory "you need to upgrade!!!" comment: both Postgres 9.3 and Windows 8.1 are far beyond EOL. (Don't think I'm being high and mighty, since I still must maintain Pg 9.2 and SQL Server 2005, 2008, & 2008R2 servers...) On 1/26/21 8:51 PM, Lisa Ruby wrote: Don't know why this so often

Re: CONCAT function adding extra characters

On 6/15/21 1:55 PM, AI Rumman wrote: I am using Postgresql 10 and seeing a strange behavior in CONCAT function when I am concatenating double precision and int with a separator. select concat('41.1'::double precision,':', 20); Result: 41.1014:20 Value 41.1 which

Re: Need to omit time during weekends from age calculations

On 6/7/21 2:12 PM, David Gauthier wrote: Hi: I suspect I'm not the first to ask about this but couldn't find anything after googling for a bit.  So here goes I'd like to get the "age" difference between two times which span either all or part of a weekend but exclude any time that

Re: Postgresql crashdown and recovery failure

On 6/10/21 10:30 PM, xiebin (F) wrote: Hi, My database (Postgresql 12.5) server uses remote storage as data directory. I powered off the database server and started postgresql (same version) on another server, with same data. However it failed. I checked wal logs and found that the content

Re: Temporal tables as part of main release

"Hope" does *not* mean "likely". On 6/19/21 11:24 AM, Anand Sowmithiran wrote: Sure, hence I wrote "likely"! On Sat, 19 Jun 2021, 8:10 pm Tom Lane, > wrote: Anand Sowmithiran mailto:anand@gmail.com>> writes: > This is very helpful to know , we

Re: How to automatically delete idle client connections?

On 6/21/21 5:17 AM, David Rowley wrote: On Mon, 21 Jun 2021 at 21:59, Yu Watanabe wrote: I have tried setting idle_in_transaction_session_timeout , however, this does not delete idle sessions. Would there be any expiry settings to delete client idle sessions automatically ? There is

Re: Losing data because of problematic configuration?

On 6/15/21 5:42 AM, Holtgrewe, Manuel wrote: Hi, I have a database that is meant to have high-performance for bulk insert operations. I've attached my postgres.conf file. However, I'm seeing the following behaviour. At around 12:04, I have started the database. Then, I did a bulk insert

Re: Memory alloc exception

On 6/15/21 6:09 AM, paul.m...@lfv.se wrote: Hi list, I get this error when running a SQL statement in my Java application. ERROR: Invalid memory alloc request size 1683636507 Location: File: d:\pginstaller.auto\postgres.windows-x64\src\backend\utils\mmgr\mcxt.c, Routine:

Re: Issue in PG start

On 5/8/21 10:53 AM, Adrian Klaver wrote: On 5/7/21 10:14 PM, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following message FATAL: the database

Re: Issue in PG start

On 5/8/21 9:19 PM, sivapostg...@yahoo.com wrote: 1. First why switch the computer off for lunch?     It's a development machine and I'm a developer.   We used to shut down every computer for lunch.  We shut down the windows and not just hit the power switch.   We advice and follow every

Re: Copyright vs Licence

On 5/10/21 4:34 AM, Vijaykumar Jain wrote: Hi All, I have been playing around with the pg_auto_failover extension by citus and have really enjoyed playing chaos with it. citusdata/pg_auto_failover: Postgres extension and service for automated failover and high-availability (github.com)

Re: Question about integer out of range in function

On 5/14/21 1:38 AM, Condor wrote: Hello ppl, I have a question about ERROR:  integer out of range in one function. I modify the generate_ulid() function to accept also UNIX timestamp as input parameter. I drop old function and make new one: CREATE FUNCTION generate_ulid(fromtime bigint

Re:

On 5/16/21 1:47 PM, Loles wrote: Hi! Let's see if someone can answer my question. In a default PostgreSQL 13 installation, starting the server starts a process called "logical replication launcher". I know what logical replication is but I don't know what this process does on the server.

Re: pgadmin4 SRPM

The pgAdmin mailing list is a better place to ask: https://www.pgadmin.org/support/list/ On 5/25/21 7:22 AM, Radomír Kůs wrote: Hello, I cannot find a spec file to build my own rpm of pgadmin4 for rhel8. At my company we have a security policy not to use any 3rd-party binaries, we have

Re: How different is AWS-RDS postgres?

On 5/26/21 1:24 PM, Rob Sargent wrote: My question is: Should I be surprised that the initial bulk loaded table is nowhere to be seen, given the thumbs up from the logs?  Is this frailty inherent in AWS/RDS infrastructure? Since this is an academic exorcise, I have minimal AWS support,

Re: How different is AWS-RDS postgres?

On 5/26/21 12:50 PM, Rob Sargent wrote: I have what purports to be Postgres 12 ( PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit ) running on RDS.  I testing a new release of my service with an admittedly large data set (which may be my

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 5:06 PM, Dean Gibson (DB Administrator) wrote: On 2021-05-28 12:38, Ron wrote: On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote: On 2021-05-28 08:12, Adrian Klaver wrote: On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote: I started to use PostgreSQL v7.3 in 2003

Re: AWS forcing PG upgrade from v9.6 a disaster

On 5/28/21 1:40 PM, Dean Gibson (DB Administrator) wrote: On 2021-05-28 08:12, Adrian Klaver wrote: On 5/27/21 8:41 PM, Dean Gibson (DB Administrator) wrote: I started to use PostgreSQL v7.3 in 2003 on my home Linux systems (4 at one point), gradually moving to v9.0 w/ replication in 2010.  In

Re: Postgres prepare statement caching issue in postgres command line

On 5/22/21 4:11 PM, Tom Lane wrote: Soumya Prasad Ukil writes: I have been using postgres prepare statement feature through JDBC. I could see the same prepare statement multiple times, parsing is not happening in postgres backend, which is expected. However I do the same in psql command

Re: How different is AWS-RDS postgres?

On 5/27/21 4:58 PM, Rob Sargent wrote: On 5/27/21 3:08 PM, Sam Gendler wrote: The same JDBC connection that is resulting in lost data?  Sounds to me like you aren't connecting to the DB you think you are connecting to. I almost wish that were true. However, looking at AWS "Performance

Re: symbol lookup error: /usr/lib/9.6/bin/psql: undefined symbol: PQsetErrorContextVisibility

On 6/4/21 5:21 PM, rob stan wrote: Hello all, I have a problem with connecting database via psql;|/usr/lib/9.6/bin/psql: symbol lookup error: /usr/lib/9.6/bin/psql: undefined symbol: PQsetErrorContextVisibility||| || |OS :||Debian GNU/Linux 9| || |I tried setting it didn't help;export

Re: How long to get a password reset ???

On 5/28/21 10:02 AM, Magnus Hagander wrote: On Fri, May 28, 2021 at 5:42 AM Dean Gibson (DB Administrator) wrote: It's pretty simple: Not having used this mailing list for a while, I went to https://lists.postgresql.org/ to make sure my settings were as I wanted them. I attempted to log in

Re: Fwd: Proposed Chinese Translation of Community Code of Conduct

I see the attachments in your first email. On 5/21/21 12:22 PM, Stacey Haysler wrote: And, once again, the attachments did not make it through. Re-sending from my Gmail in hope that the attachments make it through this time. Regards, Stacey Begin forwarded message: *From: *Stacey Haysler

Re: pgbackrest - hiding the encryption password

On 5/22/21 5:52 AM, Peter J. Holzer wrote: On 2021-05-19 12:49:42 -0500, Ron wrote: Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 perms. Did you mean 644? 633 would be very strange permissions (write and execute but not read for group and others). Yes, I noticed

Re: Fwd: Proposed Chinese Translation of Community Code of Conduct

On 5/22/21 5:18 AM, Peter J. Holzer wrote: On 2021-05-21 12:35:49 -0500, Ron wrote: I see the attachments in your first email. Fascinating. I don't in the normal view but do see them in the MIME structure: I 1 [multipa/alternativ, 7bit, 180K] I 2

Re: When to REINDEX a serial key?

On 7/6/21 4:52 AM, David Rowley wrote: On Tue, 6 Jul 2021 at 21:35, Ron wrote: The legacy RDBMS which I used to manage has a tool for analyzing (not in the Postgresql meaning of the word) an index, and displaying a histogram of how many layers deep various parts of an index are. Using

Re: On partitioning, PKs and FKs

On 7/8/21 3:42 AM, Wiwwo Staff wrote: Hi! I have a big table bigTable which I partitioned by hash on field columnX, by creating bigTable_0, bigTable_1etc. Since I need a PK on bigTable.id, and table is not partitioned by id, and columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id

Re: Do table-level CHECK constraints affect the query optimizer?

On 6/29/21 10:41 AM, Michael Lewis wrote: Are vacuum and analyze happening regularly on the live system? Yes.  There's a nightly cron job which vacuums those it thinks needs it (though it's INSERT-heavy), and ditto ANALYZE. Specifically, I ran ANALYZE on the prod table just before running

Do table-level CHECK constraints affect the query optimizer?

Postgresql 12.5 I've got a big (about 50M rows, but 1.4TB because of xml attachments) partitioned table full of data that we're seeing sequential scans on, even though there are supporting indices. Will adding CHECK constraints on the children, which match the partition ranges influence the

Re: Do table-level CHECK constraints affect the query optimizer?

On 6/29/21 11:42 AM, Tom Lane wrote: Ron writes: On 6/29/21 10:41 AM, Michael Lewis wrote: What's an example query that uses indexes on test and does not on live? SELECT COUNT(*) FROM sep_info_report_extract; On prod, there's a list of "Parallel Seq Scan on _partname&quo

Re: Do table-level CHECK constraints affect the query optimizer?

On 6/29/21 12:46 PM, Tom Lane wrote: Ron writes: On 6/29/21 11:42 AM, Tom Lane wrote: If there's a significant difference in relallvisible fractions, that would point to something different in your VACUUM housekeeping on the two systems. Prod is brand new.  Loaded on Saturday; we saw

Greatest of a list of columns?

Postgresql 12.5 What's the canonical Postgresql method for doing, for example, this? SELECT relname, MAXOF(last_vacuum, last_autovacuum) FROM pg_stat_user_tables; Seeing both last_vacuum and last_autovacuum is useful, of course, but sometimes I only want to see the "really" last time it was

EXPLAIN with anonymous DO block?

Postgresql 12.5 There's a query inside a DO block which -- because it's parameterized -- I'd rather analyze while it's in the FOR loop of a DO block, instead of pulling it out and hard-coding the parameters. Is this possible?  If so, where do I put the EXPLAIN statement? -- Angular momentum

CREATE INDEX ONLY on a partitioned table?

Postgresql 12.5. https://www.postgresql.org/docs/12/ddl-partitioning.html mentions creating indices on children, and then attaching them to a table which previously had CREATE INDEX ... ONLY ON executed. Can I do something similar on an existing table (for example, on a heavily partitioned

Re: ALTER TABLE ... DETACH PARTITION just sitting there

On 6/28/21 4:16 PM, Tom Lane wrote: Ron writes: AWS RDS Postgresql 12.5. We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01') TO (2011-08-01')), and I've been detaching partitions from oldest to newest, one at a time. Whenever it's failed due to a FK constraint

ALTER TABLE ... DETACH PARTITION just sitting there

AWS RDS Postgresql 12.5. We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01') TO (2011-08-01')), and I've been detaching partitions from oldest to newest, one at a time. Whenever it's failed due to a FK constraint (and there are many of them!), I dropped the "same

Re: Why can't I drop a tablespace?

On 7/10/21 10:43 AM, Phil Endecott wrote: Phil Endecott wrote: Yesterday I had a disk-nearly-full problem, and decided to try to resolve it by moving one large table to a spare disk in a new tablespace: =# create tablespace tempspace location "/db_temp"; =# alter table requests set tablespace

Re: On partitioning, PKs and FKs

The docs are pretty clear that all constraints must have the partition key as part of the key, and PK is most certainly a constraint. I welcome a counter-example. On 7/8/21 10:23 AM, Alban Hertroys wrote: On 2021-07-08 13:30, Ron wrote: > Thus, the bigTable PK must be on id, columnX, (No

When to REINDEX a serial key?

Server: RDS Postgresql 12.5 Client: Vanilla Postgresql 12.5 Like most systems, we have lots of tables indexed on sequences. Thus, all new keys are inserted into the "lower right hand corner" of the b-tree. The legacy RDBMS which I used to manage has a tool for analyzing (*not* in the

pgbackrest info of encrypted seems broken

This is on Postgresql 9.6, if it matters.  Attached is a text file showing the pgbackrest version, config, backup log and "pgbackrest info" output. I followed the instructions in https://pgbackrest.org/user-guide.html#quickstart/configure-encryption, and successfully ran these commands

Re: pgbackrest info of encrypted seems broken

On 5/19/21 12:08 PM, David Steele wrote: On 5/19/21 9:47 AM, Ron wrote: This is on Postgresql 9.6, if it matters.  Attached is a text file showing the pgbackrest version, config, backup log and "pgbackrest info" output. I followed the instructions in https://pgbackrest.org/user-

pgbackrest - hiding the encryption password

Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 perms.  Normally, that's ok, but is a horrible idea when it's a plaintext file, and stores the pgbackrest encryption password. Would pgbackrest (or something else) break if I change it to postgres:postgres 600

Re: pgbackrest - hiding the encryption password

On 5/19/21 1:33 PM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 perms.  Normally, that's ok, but is a horrible idea when it's a plaintext file, and stores the pgbackrest encryption password

Re: pgbackrest - hiding the encryption password

On 5/19/21 1:34 PM, David Steele wrote: On 5/19/21 1:49 PM, Ron wrote: Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 perms.  Normally, that's ok, but is a horrible idea when it's a plaintext file, and stores the pgbackrest encryption password. Would pgbackrest

Re: pgbackrest - hiding the encryption password

On 5/19/21 1:34 PM, David Steele wrote: On 5/19/21 1:49 PM, Ron wrote: Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 perms.  Normally, that's ok, but is a horrible idea when it's a plaintext file, and stores the pgbackrest encryption password. Would pgbackrest

Invalid byte sequence when importing Oracle BLOB

Hi, The source is an Oracle 12 db with this encoding: NLS_LANG = AMERICAN_AMERICA.AL32UTF8 NLS_NCHAR = AL32UTF8 The destination is an (RDS) Postgresql 12.5 with encoding UTF8, and is being loaded through COPY commands generated by ora2pg. The source table has a BLOB column (I think they are

Re: Invalid byte sequence when importing Oracle BLOB

On 4/26/21 7:32 AM, Peter J. Holzer wrote: On 2021-04-26 06:49:18 -0500, Ron wrote: The destination is an (RDS) Postgresql 12.5 with encoding UTF8, and is being loaded through COPY commands generated by ora2pg. The source table has a BLOB column (I think they are scanned images) which I'm

Re: Invalid byte sequence when importing Oracle BLOB

On 4/26/21 7:58 AM, Peter J. Holzer wrote: On 2021-04-26 07:45:26 -0500, Ron wrote: On 4/26/21 7:32 AM, Peter J. Holzer wrote: On 2021-04-26 06:49:18 -0500, Ron wrote: The destination is an (RDS) Postgresql 12.5 with encoding UTF8, and is being loaded through COPY commands generated by ora2pg

Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?

On 5/2/21 8:46 AM, Pól Ua Laoínecháin wrote: [snip] Now, I'm not quite sure that I completely comprehend matters: Is there a difference between Asynchronous I/O and Buffered I/O? * Asynchronous (a-syn-chron-ous) is an adjective which means "not together with time". * Buffered means "read more

Re: enable_seqscan to off -> initial cost 10000000000

On 4/23/21 10:39 AM, Luca Ferrari wrote: Hi all, this could be trivial, but I would like an explaination: if I turn off sequential scans on a table without indexes, the same access plan is increased by a 100 factor. I suspect this is a warning for me to remind that something is

Re: vacuumdb not letting me connect to db

Obviously... don't use 300 threads. On 2/5/21 2:15 AM, Atul Kumar wrote: ok, How do I resolve it ? Any suggestions ? On 2/5/21, Ron wrote: Your problem screams "IO saturation". On 2/4/21 12:07 PM, Atul Kumar wrote: There is no error message, when I try to connect the data

Re: vacuumdb not letting me connect to db

On 2/4/21 5:26 AM, Atul Kumar wrote: Hi, I have 160 GB of RAM, postgres 9.6 is running on the server. after upgrade I ran the below command: "/usr/edb/as9.6/bin/vacuumdb" -U 'enterprisedb' --verbose --all -j 300 --analyze-only after running that command I was not able to connect the

Re: vacuumdb not letting me connect to db

What about disk IO?  That's what really gets saturated when running 300 threads. On 2/4/21 11:00 AM, Atul Kumar wrote: Hi, The CPU and RAM are normal even on 300 jobs ( only 1-4% of consumption) but I don’t understand one thing here that if max_connections is set to 700 then why I am not

Re: vacuumdb not letting me connect to db

On 2/5/21 10:22 AM, Rob Sargent wrote: On 2/5/21 9:11 AM, Ron wrote: Obviously... don't use 300 threads. No, no Ron.  Clearly the answer is more CPUs I hope you're being sarcastic. -- Angular momentum makes the world go 'round.

Re: psql behavior change on upgrade from version 12.x to 13.1

On 2/9/21 1:57 PM, Bryn Llewellyn wrote: [snip] In other words, different from what you see. I'm an ordinary end user. I don't even think expllictly about “building" anything in the PostgreSQL system. I got into this mess (as I believe) because I did this: brew update brew upgrade A

Re: How to post to this mailing list from a web based interface

On 1/28/21 9:27 AM, Alvaro Herrera wrote: On 2021-Jan-28, Ravi Krishna wrote: I am planning to switch to a web based tool to read this mailing list. That's great. While reading is easy via web, how do I post a reply from web. Yeah, "how" indeed. Gmail is a web interface... :) -- Angular

Re: Row not immediately visible after commit

RDS is also a modified version of Postgresql, just not as modified as Aurora. On 3/29/21 7:06 PM, Steve Baldwin wrote: Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their postgres-flavoured Aurora product). b2bc_owner@b2bcreditonline=> select version();                    

Re: Upgrading from 11 to 13

On 3/30/21 9:53 AM, Daniel Westermann (DWE) wrote: On Tue, Mar 30, 2021 at 04:34:34PM +0200, Guillaume Lelarge wrote: Hi, Le mar. 30 mars 2021 à 16:10, Susan Joseph a écrit : I am currently using PostgreSQL 11.2 and would like to try and upgrade it to the latest version 13.  Can

Re: Check constraint failure messages

On 4/6/21 2:40 PM, Miles Elam wrote: I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error message ERROR: value for domain po.email violates check constraint "email_check" SQL state: 23514 When inserting 1000+ entries in a batch,

Re: Check constraint failure messages

On 4/6/21 3:50 PM, Miles Elam wrote: On Tue, Apr 6, 2021 at 1:03 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: On 4/6/21 2:40 PM, Miles Elam wrote: I've got a domain that validates email addresses. When inserting a bunch of entries I simply get the error message

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 10:04 AM, Rob Sargent wrote: On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time

Re: Slick way to update multiple tables.

On 4/1/21 11:16 AM, Theodore M Rolle, Jr. wrote: I SELECT items from three tables. It seems that the only way to put updated fields into their proper table is to query /each/ field, then UPDATE /each/ table. Am I missing something here? Is there a way to do this automatically? Did you

Re: postgresql order lowercase before uppercase

Exactly.  "C" collation is the opposite of what he wants. OP needs something like "de_DE". On 3/18/21 10:36 AM, Marc Millas wrote: to me, collate 'C' ask for the raw ascii order which put caps before because the hexa coding is lower did you try any thing else (POSIX is same as 'C') Marc

Re: Logical Replication, CPU load and Locking contention

On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández > wrote: Hello, I’m troubleshooting a problem at my company with a pg 12 cluster that we run. We are using Amazon DMS to replicate data from our

Re: Logical Replication, CPU load and Locking contention

On 3/10/21 11:56 AM, Martín Fernández wrote: On 10 Mar 2021, at 11:25, Ron <mailto:ronljohnso...@gmail.com>> wrote: On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández <mailto:fmarti...@gmail.com>> wrote: Hello, I

Re: Duplicate key error

On 3/4/21 12:14 AM, Andrus wrote: Hi! says something else is inserting/updating using that key value. So obviously your script is not catching all the conflicts. > At this point your best bet is to monitor the Postgres log and see what else is happening at the time of the error. I'm guessing

Re: yum update for postgresql rpms

It's standard procedure for package managers to stop daemons before update and then start them back up afterward. On 2/23/21 8:22 AM, Haas, Scott wrote: Tickling this thread to see if I can get a confirmation of the described behavior for rpm update via yum. I do not want to cross-post (but

Re: Primary keys and composite unique keys(basic question)

On 4/5/21 9:37 PM, Rob Sargent wrote: It's a small thing, but UUIDs are absolutely not memorizable by humans; they have zero semantic value.  Sequential numeric identifiers are generally easier to transpose and the value gives some clues to its age (of course, in security contexts this can be a

Re: Primary keys and composite unique keys(basic question)

On 4/7/21 11:35 AM, Rob Sargent wrote: On Apr 7, 2021, at 10:17 AM, Ron wrote:  On 4/5/21 9:37 PM, Rob Sargent wrote: It's a small thing, but UUIDs are absolutely not memorizable by humans; they have zero semantic value.  Sequential numeric identifiers are generally easier to transpose

Re: Primary keys and composite unique keys(basic question)

On 4/7/21 1:16 PM, Rob Sargent wrote: On 4/7/21 11:59 AM, Ron wrote: On 4/7/21 11:35 AM, Rob Sargent wrote: On Apr 7, 2021, at 10:17 AM, Ron wrote:  On 4/5/21 9:37 PM, Rob Sargent wrote: It's a small thing, but UUIDs are absolutely not memorizable by humans; they have zero semantic value

Re: The Amazon CloudFront distribution is configured to block access from your country.

On 4/10/21 2:00 PM, felix.quin...@yahoo.com wrote: I am trying to download the installer and I get this error. The same happens with binary files. --- 403 ERROR The request could not be satisfied. The Amazon CloudFront

Re: The Amazon CloudFront distribution is configured to block access from your country.

Then you should ask EDB, or Amazon, or use a VPN. On 4/10/21 7:03 PM, felix.quin...@yahoo.com wrote: Because it is the Postgresql installer. https://www.enterprisedb.com/downloads/postgres-postgresql-downloads On Saturday, April 10, 2021, 07:30:29 PM GMT-4, Ron wrote: On 4/10/21 2:00

Re: Reuse of REF Cursor

On 4/11/21 1:02 PM, Abraham, Danny wrote: 2021-04-09 08:00:08.692 IDTERROR: canceling statement due to statement timeout 2021-04-09 08:00:08.692 IDTCONTEXT: PL/pgSQL function orhpans_active_clean_table(character varying,integer) line 42 at FETCH PL/pgSQL function

Re: The Amazon CloudFront distribution is configured to block access from your country.

On 4/11/21 9:12 AM, Israel Brewster wrote: On Apr 11, 2021, at 1:53 AM, Stephan Knauss > wrote: Hello Felix, On 11.04.2021 02:31, felix.quin...@yahoo.com wrote: I see no reason why ask elsewhere first, the link for the

Re: The Amazon CloudFront distribution is configured to block access from your country.

On 4/11/21 10:07 AM, Israel Brewster wrote: On Apr 11, 2021, at 6:52 AM, Ron <mailto:ronljohnso...@gmail.com>> wrote: On 4/11/21 9:12 AM, Israel Brewster wrote: On Apr 11, 2021, at 1:53 AM, Stephan Knauss <mailto:pg...@stephans-server.de>> wrote: Hello Felix, On 11.04.2021

Re: where clauses including timstamptz and intervals

On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote: On Fri, Apr 09, 2021 at 07:24:54AM +, Niels Jespersen wrote: Hello all Are these two queries exactly eqivalent? The table is partitioned on r_time, which is a timestamptz. The explain plans are not exactly the same. The first wants to

Re: Who altered the database?

On 4/9/21 6:14 AM, Durumdara wrote: Dear Members! We have a server. Because of high usage we can't log DDL-s. There are too many temporary tables created by users, so it would slow down the server. A database owner changed. What can we do to get info about the change (who did it, from what

Re: How can I insert the image as a blob in the table

On 4/12/21 8:29 PM, Igor Korot wrote: Hi, guys, On Sun, Apr 11, 2021 at 7:08 PM Igor Korot wrote: Hi, David, On Sun, Apr 11, 2021 at 6:57 PM David G. Johnston wrote: On Sunday, April 11, 2021, Igor Korot wrote: Hi, David, On Sun, Apr 11, 2021 at 6:24 PM David G. Johnston wrote: If

Re: Order by not working

What would you tell pgadmin?  "Order *this* *particular* query -- out of all the billion queries I might write -- in *this particular* manner?" No, that's not how things work.  Just add an ORDER BY when you query the table. On 2/16/21 12:48 PM, Dan Nessett wrote: Thanks Peter. The listing of

Re: Order by not working

SQL is only intuitive to people who've done programming... :) Also, since your table names are only composed of lower case and underscores, the double quotes are not needed. On 2/16/21 1:41 PM, Dan Nessett wrote: Thanks to those who responded. I have solved my problem by noting the advice to

Syntax checking DO blocks and ALTER TABLE statements?

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 TABLE web_service_error   

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter statements manually or even write it using a scripting language

Re: Syntax checking DO blocks and ALTER TABLE statements?

On 2/16/21 6:19 PM, Tim Cross wrote: Ron writes: On 2/16/21 5:44 PM, Tim Cross wrote: Given the number, I think I would do the same. A good example of why being 'lazy' can be a virtue. Faster and easier to write a procedure to generate dynamic SQL than write out all those alter statements

Re: Slow index creation

On 2/16/21 12:30 PM, Paul van der Linden wrote: Hi, I have 2 functions: CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS $func$ DECLARE     retVal text; BEGIN     SELECT       CASE         WHEN a='v1' AND b='b1' THEN 'r1'         WHEN a='v1' THEN 'r2'         ... snip long list

Re: vacuumdb not letting me connect to db

Your problem screams "IO saturation". On 2/4/21 12:07 PM, Atul Kumar wrote: There is no error message, when I try to connect the database while running vacuumdb with 300 jobs, it gets stuck. On Thursday, February 4, 2021, Ravi Krishna > wrote: >The CPU and

Re: vacuumdb not letting me connect to db

, 1.5x or *maybe* 2x if the core count is low, and nothing else is running on the system. Note: even during 300 threads, my RAM utilisation is totally normal. Regards Atul On Saturday, February 6, 2021, Gavan Schneider <mailto:list.pg.ga...@pendari.org>> wrote: On 6 Feb 2021, at 3

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 It's a self-referential FK on a single (but partitioned) table. The ALTER TABLE command fails, but I queried it, and the record that it fails on exists.  I modified the original INITIALLY IMMEDIATE

ADD FOREIGN KEY fails, but the records exist

Postgresql 12.5 It's a self-referential FK on a single (but partitioned) table.  The ALTER TABLE command fails, but I queried it, and the record that it fails on exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY DEFERRED but that did not help. What am I doing wrong?

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 00:00:00) is not present in table

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 00:00:00) is not present in table

Re: ADD FOREIGN KEY fails, but the records exist

On 2/15/21 10:58 AM, Adrian Klaver wrote: On 2/15/21 8:55 AM, Ron wrote: On 2/15/21 10:27 AM, Adrian Klaver wrote: On 2/15/21 8:23 AM, Ron wrote: On 2/15/21 10:17 AM, Adrian Klaver wrote: On 2/15/21 8:12 AM, Ron wrote: Postgresql 12.5 The error: DETAIL:  Key (amended_response_id

Re: Script checking to see what database it's connected to

On 2/21/21 6:49 PM, Rob Sargent wrote: Take it up a notch?  Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of

Re: Script checking to see what database it's connected to

On 2/21/21 5:26 PM, Julien Rouhaud wrote: On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic s

Re: Script checking to see what database it's connected to

On 2/21/21 7:19 PM, Julien Rouhaud wrote: [snip] Well, the supported commands did work. You should probably look at https://www.postgresql.org/docs/current/app-psql.html, you'd see that "exit" is not a supported command and you should instead use \q[uit]. I recommend looking at the semantics of

Script checking to see what database it's connected to

Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a

Re: Script checking to see what database it's connected to

On 2/21/21 5:26 PM, Rob Sargent wrote: On 2/21/21 4:18 PM, Ron wrote: Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I

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