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 defa

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.

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 befor

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 perms

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 encryptio

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

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

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: 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

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: 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 line,

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 to

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 sw

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, whic

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 Insigh

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 wi

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: 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 on

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 LD_

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 transp

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 a

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 a

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: MemoryContextAlloc,

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 double

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 idle

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 mon

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 fail

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 t

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 qu

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 th

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&

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 va

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

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 Postgres

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 that

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: 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

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: Formating psql query output

On 7/19/21 3:05 PM, Rich Shepard wrote: On Mon, 19 Jul 2021, Adrian Klaver wrote: You need them in the JOIN and/or WHERE sections, but not necessarily in the field list in the SELECT portion. Adrian, I wondered about that and thought I needed to include them in the SELECT phrase. If that w

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

On 7/22/21 4:52 AM, Beat Hoedl wrote: [snip] BTW: There is no virus scanner on the postgres folder and it's a productive system, I cant just update. There's *always* a maintenance window, even if it's just once a year on Christmas.  Or are you saying that Windows isn't being patched either?

Re: pg-audit extension

On 7/26/21 1:24 PM, Olagoke Akinyemi wrote: Hello, Could someone please, give me a quick guide? I am trying to install pgaudit extension on an existing PostgreSQL instance but I want to place it on a different this mount. How can i do this? "but I want to place it on a different this mount"

Re: Partitioning a table by integer value (preferably in place)

On 8/9/21 9:14 AM, Pól Ua Laoínecháin wrote: Hi again all, Just a quick follow-up - could I script the creation of 1000 partitions using bash Sure.  That's what scripting languages are for. or PL/pgSQL? I think (rightly/wrongly?) that this may be the best solution? I've found samples on t

Re: Alter and move corresponding: was The tragedy of SQL

On 9/15/21 11:52 AM, Steve Litt wrote: Gavin Flower said on Wed, 15 Sep 2021 13:49:39 +1200 Hi Michael, [snip] COBOL has strange verbs like 'move corresponding' that could accomplish complicated tasks in a few lines but you have to be careful that you knew what you were asking for! MOVE CO

Re: The tragedy of SQL

On 9/16/21 3:21 PM, Gavin Flower wrote: On 17/09/21 04:26, Michael Nolan wrote: In the same 1971 seminar where we studied Algol-68, we had to read and write a short paper on the 1970 Codd paper on relational  theory, which had only been out for about a year.  The professor running the seminar

Re: The tragedy of SQL

On 9/16/21 6:29 PM, Guyren Howe wrote: [snip] Missing my original point here. The set theory is the _point_. SQL is a gargantuan distraction from using it efficiently. Imagine if COBOL was the only widely-available programming language with functions. You might use it, because functions are re

Re: Postgres incremental backups per db (not per cluster)

On 9/23/21 2:57 PM, Jaime Solorzano wrote: According to the WAL Point-in-time recovery and incremental backup documentation, pg_basebackup and wals are used to take backups and incremental backups of a running PostgreSQL cluster. I wonder if there is a way to take incremental backups per da

Re: Problem in pg_basebackup of 1.7 TB database while replicating in PostgreSQL 9.4.( Master and slave both are in postgreSQL 9.4)

On 10/4/21 2:28 AM, Amal Chakravarty wrote: Hi all. I am working with a database of 1.7 TB size which is in PostgreSQL 9.4. Kindly suggest how to take the pg_basebackup of such a huge data while replicating from master to slave. Obligatory "9.4 is EOL" comment. -- Angular momentum makes the w

Re: Growth planning

On 10/4/21 12:36 PM, Israel Brewster wrote: [snip] Indeed. Table per station as opposed to partitioning? The *most* I can reasonably envision needing is to query two stations, i.e. I could see potentially wanting to compare station a to some “baseline” station b. In general, though, the station

Re: type bug?

What version of Postgresql do you think has the bug? Where did you get the Postgresql binaries from? What platform? On 10/5/21 10:50 PM, m...@ft-c.de wrote: Hello, there is a bug with types in functions. Here is an example: --drop type  xyz; create type xyz as ( x numeric,  y numeric,  z num

Re: Error: server closed the connection unexpectedly

On 10/9/21 3:26 PM, Adrian Klaver wrote: On 10/9/21 9:01 AM, Anil wrote: Hi Team, I am using Postgres (AWS instance, version: PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) with python for programming and pgAdmin for querying. However

Re: Error: server closed the connection unexpectedly

On 10/10/21 11:27 AM, Adrian Klaver wrote: On 10/9/21 23:18, Ron wrote: On 10/9/21 3:26 PM, Adrian Klaver wrote: On 10/9/21 9:01 AM, Anil wrote: Hi Team, I am using Postgres (AWS instance, version: PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3

Re: Error: server closed the connection unexpectedly

On 10/10/21 2:20 PM, Tom Lane wrote: Ron writes: I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you're looking in the right place, then. What do you get from show log_de

Re: Error: server closed the connection unexpectedly

On 10/10/21 3:00 PM, Tom Lane wrote: Ron writes: This is AWS RDS Postgresql.  The db server log is accessed via a web interface, and is /the/ postgresql log just like you see in vanilla Postgresql. I've attached it.  My attempt to connect happened some time between between 04:10:30 and

Re: Error: server closed the connection unexpectedly

On 10/10/21 3:12 PM, Adrian Klaver wrote: On 10/10/21 12:30, Ron wrote: On 10/10/21 2:20 PM, Tom Lane wrote: Ron writes: I think Anil mentioned that there's *nothing* in the Postgresql server log, even (in my case) with log_min_messages cranked to debug5. I'm skeptical that you&

Re: "two time periods with only an endpoint in common do not overlap" ???

On 10/14/21 7:02 PM, Adrian Klaver wrote: [snip] or the third example in the docs: SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS    (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS    (DATE '2001-10-30', DATE '2002-10-

Re: "two time periods with only an endpoint in common do not overlap" ???

On 10/15/21 8:59 AM, Adrian Klaver wrote: On 10/15/21 06:52, Ron wrote: On 10/14/21 7:02 PM, Adrian Klaver wrote: [snip] or the third example in the docs: SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS    (DATE '2001-10-30', DATE '2002-10-30&#x

Re: "two time periods with only an endpoint in common do not overlap" ???

inclusive, but the ending time is exclusive.   So [1:00,2:00), and [2:00,3:00), do not overlap. On Fri, Oct 15, 2021 at 2:16 PM Ron <mailto:ronljohnso...@gmail.com>> wrote: On 10/15/21 8:59 AM, Adrian Klaver wrote: > On 10/15/21 06:52, Ron wrote: >> On 10/14/21

Re: "two time periods with only an endpoint in common do not overlap" ???

On 10/15/21 11:35 PM, David G. Johnston wrote: On Friday, October 15, 2021, Ron <mailto:ronljohnso...@gmail.com>> wrote: The numeric ranges 0-10 and 10-19 overlap, just as the time ranges 00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00. *It's the programmer's

Re: "two time periods with only an endpoint in common do not overlap" ???

On 10/15/21 11:08 PM, Adrian Klaver wrote: On 10/15/21 19:42, Ron wrote: The numeric ranges 0-10 and 10-19 overlap, just as the time ranges 00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00. *It's the programmer's responsibility* to say what s/he really means, not for "the

Re: Force re-compression with lz4

On 10/17/21 10:12 AM, Florents Tselai wrote: Hello, I have a table storing mostly text data (40M+ rows) that has pg_total_relation_size ~670GB. I’ve just upgraded to postgres 14 and I’m now eager to try the new LZ4 compression. I’ve altered the column to use the new lz4 compression, but that

Re: Force re-compression with lz4

On 10/17/21 11:36 AM, Ron wrote: On 10/17/21 10:12 AM, Florents Tselai wrote: Hello, I have a table storing mostly text data (40M+ rows) that has pg_total_relation_size ~670GB. I’ve just upgraded to postgres 14 and I’m now eager to try the new LZ4 compression. I’ve altered the column to

Re: How to copy rows into same table efficiently

On 10/26/21 1:04 AM, Arun Suresh wrote: Dear PG experts, We have a tenant discriminator column in our tables to enable storage of data from multiple tenants. This column is also part of the composite primary key. The customers may request creation of a tenant copy, which means if they curren

Re: Need help understanding error message

On 10/26/21 11:58 AM, Rich Shepard wrote: In a database table I have these rows: # select * from contacts where person_nbr=6;  person_nbr | contact_date | contact_type |  not es   | next_conta

Re: Need help understanding error message [RESOLVED]

On 10/26/21 1:17 PM, Rich Shepard wrote: On Tue, 26 Oct 2021, Rob Sargent wrote: Aren’t lines 3 and 6 duplicates? Ah, shoot! The second was supposed to be 16 and that's how I saw it when I scanned the list. The second thing I deeply learned about computer programming: it's almost certainly

Re: ZFS filesystem - supported ?

On 10/26/21 7:55 PM, Mladen Gogala wrote: On 10/26/21 20:50, Imre Samu wrote: > Phoronix has some very useful benchmarks: > https://www.phoronix.com/scan.php?page=news_item&px=Linux-5.14-File-Systems > Ext4 is much

Re: Incremental backup

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. -- Angular momentum makes the world go 'round.

Re: Incremental backup

On 10/28/21 10:36 AM, Adrian Klaver wrote: On 10/28/21 05:23, Edu Gargiulo wrote: Hi all, Is there any way to make incremental backups in postgres-12? It would helpful to be more explicit about what you are trying to achieve. Do you want: 1) A continuous process or scheduled one? 2) Local

Re: Incremental backup

On 10/28/21 11:40 AM, Adrian Klaver wrote: On 10/28/21 09:25, Ron wrote: On 10/28/21 10:36 AM, Adrian Klaver wrote: On 10/28/21 05:23, Edu Gargiulo wrote: Hi all, Is there any way to make incremental backups in postgres-12? It would helpful to be more explicit about what you are trying to

Re: Incremental backup

On 10/28/21 1:00 PM, Adrian Klaver wrote: On 10/28/21 10:51, Ron wrote: Except we have no way of knowing what the situation is. I prefer not to assume a context. You make it sound like incremental (and differential) backups are some complicated thing that needs context.  That's utte

Re: Incremental backup

On 10/28/21 2:06 PM, Adrian Klaver wrote: On 10/28/21 11:48, Ron wrote: On 10/28/21 1:00 PM, Adrian Klaver wrote: On 10/28/21 10:51, Ron wrote: Not doable in Postgresql because WAL files are global to cluster. I've read multiple times that will not be changed. Yet somehow lo

Re: Incremental backup

On 10/28/21 3:43 PM, Adrian Klaver wrote: On 10/28/21 12:23, Ron wrote: On 10/28/21 2:06 PM, Adrian Klaver wrote: On 10/28/21 11:48, Ron wrote: " Logical replication is built with an architecture similar to physical streaming replication (see Section 27.2.5). It is implemented by “wals

Re: Incremental backup

On 10/28/21 3:08 PM, Bruce Momjian wrote: On Thu, Oct 28, 2021 at 02:23:39PM -0500, Ron wrote: On 10/28/21 2:06 PM, Adrian Klaver wrote: On 10/28/21 11:48, Ron wrote: On 10/28/21 1:00 PM, Adrian Klaver wrote: On 10/28/21 10:51, Ron wrote: Not doable in Postgresql because WAL files are

Re: Incremental backup

On 10/28/21 5:07 PM, Andreas Joseph Krogh wrote: På torsdag 28. oktober 2021 kl. 23:09:19, skrev Ron mailto:ronljohnso...@gmail.com>>: [...] It's still a bunch of transaction logs, whereas differential and incremental backups only backup the changed pages, no matt

Re: DBeaver does not show all tables in a Schema

On 10/29/21 5:46 AM, Shaozhong SHI wrote: I used a DBeaver to connect to postgres but it does not show all tables in a schema. Can anyone shed light on this? Permissions? -- Angular momentum makes the world go 'round.

Re: Why does the OID jump by 3 when creating tables?

On 10/30/21 3:55 AM, Daniel Westermann (DWE) wrote: Hi all, as I could not find the reason in the source code, can someone tell me why the OID counter jumps by 3 between two create table statements? postgres=# create table t1 ( a int ); CREATE TABLE postgres=# create table t2 ( a int ); CREATE

Re: ZFS filesystem - supported ?

On 11/1/21 12:00 PM, Stephen Frost wrote: [snip] Having the database offline for 10 minutes is a luxury that many don't have; I'm a bit surprised that it's not an issue here, but if it isn't, then that's great. Exactly.  Shutting down the database is easy... shutting down the "application" can

Re: check scripts after database code change

There's a regression test suite. https://www.postgresql.org/docs/current/regress.html On 11/19/21 2:01 AM, Dennis wrote: > Who made what changes in what code? I want to make special developments in the database code. > Development of what and whose beta? to check after making changes and cu

Re: insert column monetary type ver 2

On 11/20/21 11:01 AM, Tom Lane wrote: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes: I can't figure out the problem. Digit group group separator causes an insertion error, what should I do? lc_monetary = 'ru_RU.UTF-8' On my RHEL8 (moderately recent glibc) platform, that locale's mon

Re: insert column monetary type ver 2

On 11/21/21 10:27 AM, Tom Lane wrote: Ron writes: On 11/20/21 11:01 AM, Tom Lane wrote: A quick lookup later, that's U+202F or "narrow no-break space". cash_in is picky about this, and won't take plain ASCII space as a substitute. Not sure if it should. It probably

Re: insert column monetary type ver 2

On 11/21/21 7:33 PM, Adrian Klaver wrote: On 11/21/21 17:08, Ron wrote: On 11/21/21 10:27 AM, Tom Lane wrote: Ron writes: The contrary argument is that people tend not to like "best guess" behavior when working with monetary amounts. Best Guess is relative.  Breaking because

Re: insert column monetary type ver 2

On 11/21/21 8:43 PM, David G. Johnston wrote: On Sunday, November 21, 2021, Ron <mailto:ronljohnso...@gmail.com>> wrote: On 11/21/21 7:33 PM, Adrian Klaver wrote: On 11/21/21 17:08, Ron wrote: On 11/21/21 10:27 AM, Tom Lane wrote:

Re: insert column monetary type ver 2

On 11/21/21 9:35 PM, Rob Sargent wrote: On Nov 21, 2021, at 8:21 PM, Ron wrote: Why then does the money type exist? At the very least, why isn't it deprecated? Some of us like it as an easy formatter trick for reports to_char() can do the same, no? -- Angular momentum makes the wor

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'll increas

Re: As a Linux distro, how to package multiple postgres major versions?

On 11/25/21 10:43 AM, Chocimier wrote: Hi, How to provide Postgres for a binary rolling release Linux distribution? Currently 9.6, 12 and 13 major versions are packaged in Void by me in way described below. No one reported practical problems with that, but some concerns arised, mainly around sha

Re: Merge into does not work

On 11/26/21 1:08 PM, Shaozhong SHI wrote: CREATE TABLE Stock(item_id int UNIQUE, balance int); INSERT INTO Stock VALUES (10, 2200); INSERT INTO Stock VALUES (20, 1900);CREATE TABLE Buy(item_id int, volume int); INSERT INTO Buy values(10, 1000); INSERT INTO Buy values(30, 300); MERGE INTO Stock

Re: Match 2 words and more

On 11/27/21 6:49 PM, Alvaro Herrera wrote: On 2021-Nov-28, Shaozhong SHI wrote: [snip] It appears that regex is not robust. This was my attitude when I first started to learn computer programming. /My code is perfect, i//t can't be my fault!! There must be a compiler bug!!!/ Nah. Just i

Re: Linux: directory change .../lib to .../lib64

On 12/1/21 8:14 AM, Matthias Apitz wrote: [snip] From the file config.log: ... configure:2687: loading site script /usr/share/site/x86_64-unknown-linux-gnu | #!/bin/sh | # Site script for configure. It is resourced via $CONFIG_SITE environment varaible. | | # If user did not specify libdir, gues

Re: timestamp default current_timestamp not working

On 12/14/21 9:36 AM, Sanjay Minni wrote: Hi I am not getting the timestamp value which i am expecting to be automatically inserted in the column defined: ... "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP, ... (I see the above definition in pgadmin4 in the tab SQL. I have set i

Re: timestamp default current_timestamp not working

Show us the code (especially since there is no value "nil" in SQL). On 12/14/21 10:00 AM, Sanjay Minni wrote: Its an insert and my insert SQL contains the column timestamp and value nil. Will it work if the SQL contains timestamp through value is nil On Tue, 14 Dec, 2021, 9:20 pm Adrian Klaver,

Re: Best Strategy for Large Number of Images

On 12/15/21 11:45 AM, Estevan Rech wrote: Hi, I don't currently use PostgreSQL, but I plan to migrate and I have a question about the best way/strategy for storing images. I have about 2 million images with a growth trend of around 1 million images per year. I plan to store in bytea format in

Re: Sorting difference in version 10 vs 13

Aurora is sufficiently different from vanilla Postgresql that you need to ask Amazon. On 12/20/21 11:31 PM, Akheel Ahmed wrote: Both are Amazon AWS Aurora instances. 10.x is on intel/amd64. 13.x is on their ARM based graviton. select version();  

Re: Postgresql crashing during pg_dump

On 12/22/21 8:40 AM, Tom Lane wrote: Paulo Silva writes: I have a huge table with 141456059 records on a PostgreSQL 10.18 database. When I try to do a pg_dump on that table, postgresql gives a segfault, displaying this message: 2021-12-22 14:08:03.437 UTC [15267] LOG: server process (PID 25854

Re: create query with date values

On 12/31/21 7:12 AM, ourdiaspora wrote: Recipients, How to create a query such that when a date value is entered, the date may be associated with the result of another query. The scenario is that a date value is entered (the plan is these dates should be a separate table), then a query result

Re: Create and access a dictionary type

On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array, and e.g. in Python can map to dict data type. The question confuses me.

Re: Create and access a dictionary type

On 1/5/22 10:35 AM, Tomas Vondra wrote: On 1/5/22 17:24, Ron wrote: On 1/5/22 7:22 AM, Tomas Vondra wrote: On 1/5/22 14:17, Shaozhong SHI wrote: Any examples in Postgres to create a dictionary type to store and access key value pairs? I'd say JSONB can be used as an associative array

Re: How to write such a query?

On 1/6/22 12:39 AM, Igor Korot wrote: Hi, ALL, In SQLite you can write: SELECT a, b, c FROM foo WHERE id = :id; where ":id" is the named parameter. The query above is similar to SELECT a,b,c FROM foo WHERE id = ?; except that the parameter has a name. Is there a way to write such a SELECT s

Re: How to write such a query?

Ron... TIMTOWTDI -- Angular momentum makes the world go 'round.

<    6   7   8   9   10   11   12   13   14   15   >