Re: Can not drop partition if exist foreign keys

On 2023-Oct-11, Олег Самойлов wrote: > There are two common practice to drop partition from partitioned > table: just drop or detach-drop. But simple drop don't work if exist > foreign key. Example script attached. Yeah. Detach it first, then you should be able to drop it. > psql:test.sql:15: E

Re: Parsing libpq PQtrace files

On 2023-Oct-17, Dominique Devienne wrote: > But then on Parse, the query text, which also contains embedded > newlines but also embedded double-quotes, for column aliases, or table > names, or whatever, even why not a single char literal like '"' (i.e. > single-quote, double-quote, single-quote),

Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

On 2023-Oct-23, Achilleas Mantzios - cloud wrote: > I believe this text is false on too many accounts. So, what's the consensus > about Inheritance in PostgreSQL, I am going to give a talk on it in November > and I wouldn't like to advertise/promote/teach something that the community > has decided

Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

On 2023-Oct-23, Achilleas Mantzios wrote: > I find PostgreSQL inheritance a great feature. The caveats are the same > since a long time, nothing changed in that regard, but as you say, the > implementation/limitations exist in native table partitioning as well. For partitioning, many of the limit

Re: procedure string constant is parsed at procedure create time.

On 2023-Nov-07, jian he wrote: > 2.this will have errors. > CREATE or replace PROCEDURE template_create() LANGUAGE SQL AS $proc$ > DROP TABLE if exists test cascade; > CREATE TABLE test(id int4range,valid_at tsrange,name text); > INSERT INTO test VALUES (NULL, tsrange('2018-01-01',

Re: New addition to the merge sql standard

On 2023-Nov-16, Nick DeCoursin wrote: > In my opinion, it would be better for merge to offer the functionality to > simply ignore the rows that cause unique violation exceptions instead of > tanking the whole query. "ignore" may not be what you want, though. Perhaps the fact that insert (coming

Re: LibPQ: PQresultMemorySize as proxy to transfered bytes

On 2023-Jun-28, Dominique Devienne wrote: > And if there's a better proxy to programmatically know the network > traffic exchanged on the connection's socket, that's cross-platform? > Obviously > libpq itself knows, but I don't see any way to access that info. > > Perhaps tracing might? But will

Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

On 2023-Nov-28, Kyotaro Horiguchi wrote: > By the way, just out of curiosity, but errno should not be zero at the > time the message above was output, yet "%m" is showing "success", > which implies errno = 0 in Linux. How can that happen? If the file is exactly of the length given then seek will

Re: Cannot COPY xmin?

On 2024-Feb-27, Dominique Devienne wrote: > Hi. I just tried adding the xmin pseudo-column to my COPY, > and I get an error. I suggest you find some other way to achieve whatever it is you think you'll achieve with this. It's not reliable and you'll regret it eventually. Maybe take a step back

Re: Non-Stored Generated Columns

On 2024-Feb-29, Dominique Devienne wrote: > Honestly, I'm not sure why supporting the non-stored variant of generated > columns is so controversial... I don't think there's anything controversial about virtual generated columns, really ... it's just that it's tricky to implement and we don't have

Re: Is it possible to keep track of SELECTs?

On 2024-Mar-12, Dominique Devienne wrote: > So is it possible to track the last time a SELECT was performed on some > TABLE? Perhaps you could use the pgAudit module for this purpose. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: Question about PostgreSQL upgrade from version 12 to version 15

On 2024-Mar-21, Joseph Kennedy wrote: > I'm planning to upgrade my PostgreSQL database from version 12 to > version 15 using pg_upgrade. After completing the upgrade process, I'm > curious to know whether it's necessary to reindex the database. > > Could anyone please clarify whether reindexing is

Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

lopment team." Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php >From baa4bd18f751cde68c5637c4cb8065cf94e92c1c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 21 Mar 2024 13:27:37 +0100 Subject: [PATCH v1] fix dump of role comments with --no-role-pas

Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

On 2024-Mar-21, Daniel Gustafsson wrote: > On 21 Mar 2024, at 13:28, Alvaro Herrera wrote: > > > I very much doubt that they realized that comments were going to be > > omitted. But clearly it's just a mistake, and easily fixed. > > It sure looks like a search/rep

Re: Tracing libpq client: Only with PQtrace()?

Hello, On 2024-Apr-09, Sebastien Flaesch wrote: > Is the PQtrace() API the only way to enable libpq client tracing? Yes. Regards -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: Tracing libpq client: Only with PQtrace()?

On 2024-Apr-09, Greg Sabino Mullane wrote: > On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch > wrote: > > > Is the PQtrace() API the only way to enable libpq client tracing? > > > > I thought about some environment variable of client configuration > > setting... > > That's generally the job of

Re: Failing streaming replication on PostgreSQL 14

On 2024-Apr-15, Nicolas Seinlet wrote: > I'm using the Ubuntu/cyphered ZFS/PostgreSQL combination. I'm using > Ubuntu LTS (20.04 22.04) and provided ZFS/PostgreSQL with LTS > (PostgreSQL 12 on Ubuntu 20.04 and 14 on 22.04). What exactly is "cyphered ZFS"? Can you reproduce the problem with some

Re: Clarification Needed on Postgresql License Requirement for Hybrid Environment Cluster Configuration

Hello, On 2024-May-06, Prasanna Chavan wrote: > I hope this email finds you well. I am reaching out to seek > clarification on a matter regarding the configuration of a cluster in > a hybrid environment. > Specifically, I am wondering whether we require a PostgreSQL license > for setting up a clu

Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

On 2024-May-14, Dirschel, Steve wrote: > But when I try and run the command inside the procedure it throws this error: > > STATE: 25001 > MESSAGE: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction > block > CONTEXT: SQL statement "alter table t2.test1 detach partition > t2.tes

Re: Missed compiler optimization issue in function select_rtable_names_for_explain

On 2024-May-22, XChy wrote: > Hi everyone, > > I'm a compiler developer working on detecting missed optimization in > real-world applications. Recently, we found that LLVM missed a dead store > elimination optimization in the PostgreSQL code >

Re: Backup failure Postgres

On 2024-May-23, Jethish Jethish wrote: > I have tried by increasing the max_standby_streaming_delay but I'm facing > lag issues on the replica server. > > When i increase the max_standby_streaming_delay even if a query runs for 2 > minutes I'm facing lag issues for 2 minutes. You could use a sep

Re: UPDATE with multiple WHERE conditions

On 2024-Jun-12, David G. Johnston wrote: > On Wed, Jun 12, 2024 at 2:28 PM Rich Shepard > wrote: > > > I have a table with 3492 rows. I want to update a boolean column from > > 'false' to 'true' for 295 rows based on the value of another column. > > I'll often just use a spreadsheet to build th

Re: How to attach partition with primary key

On 2024-Jun-18, Philipp Faster wrote: > As I understand, PostgreSQL refuses to use existing primary key for > some reason and tries to create its own as a children of > "Transactions" table's primary key. Yeah. Your case sounds like the primary key in the partitioned table has some slight defini

Re: How to attach partition with primary key

On 2024-Jun-18, Philipp Faster wrote: > I've done a bad job describing my issue in the first place: I left out a > key definition statement that I thought doesn't play any role in the issue: > another unique index on the same columns as PK. I see. That unique index seems quite useless. Why not j

Re: Dropping column from big table

On 2024-Jul-11, Ron Johnson wrote: > Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time > (depending on whether or not you populate the column with a default value). Actually, ADD COLUMN with a default does not rewrite the entire table either, starting from pg11. "Major e

Re: unable to upgrade postgres extensions

On 2024-Aug-21, plsqlvids01 plsqlvids01 wrote: > AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per > https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x > am > trying to upgrade pg_cron and pgaudit extensions but i

Re: Is there a way to change email for subscription ?

On 2024-Sep-05, Muhammad Ikram wrote: > I want to change email for my PostgreSQL community subscriptions. Is there > a way to do it without unsubscribing and then subscribing to a new email ? Yes. You need to add your new email address as a secondary here https://www.postgresql.org/account/profi

Re: Error:could not extend file " with FileFallocate(): No space left on device

avior, it just report more things when a problem occurs.) I'm CCing Thomas Munro and Andres Freund, who authored the new code. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

Re: Error:could not extend file " with FileFallocate(): No space left on device

On 2024-Sep-11, Pecsök Ján wrote: > In our case: > Kernel: Linux version 4.18.0-513.18.1.el8_9.ppc64le > (mockbu...@ppc-hv-13.build.eng.rdu2.redhat.com) (gcc version 8.5.0 20210514 > (Red Hat 8.5.0-20) (GCC)) #1 SMP Thu Feb 1 02:52:53 EST 2024 > File systém type:xfs Can you please share the out

Re: update faster way

On 2024-Sep-14, yudhi s wrote: > Hello, > We have to update a column value(from numbers like '123' to codes like > 'abc' by looking into a reference table data) in a partitioned table with > billions of rows in it, with each partition having 100's millions rows. Another option is to not update an

Re: libpq pipelineing

Hello Samuel, On 2020-Jun-27, Samuel Williams wrote: > I found some discussion in the past relating to batch processing which > appears to support some kind of pipelining: > > https://2ndquadrant.github.io/postgres/libpq-batch-mode.html I just noticed this old thread of yours. I've been workin

Re: unexpected character used as group separator by to_char

On 2021-Mar-10, Vincent Veyron wrote: > On Tue, 09 Mar 2021 16:22:07 -0500 > Tom Lane wrote: > > (I suppose you could also use regexp_replace to convert random forms > > of whitespace to plain ASCII space.) > > No dice. 'G' formatting looks like a whitespace, but is different (it > appears to b

Re: unexpected character used as group separator by to_char

On 2021-Mar-10, Gavan Schneider wrote: > On 10 Mar 2021, at 16:24, Alvaro Herrera wrote: > > > That space (0xe280af) is U+202F, which appears to be used for French and > > Mongolian languages (exclusively?). It is quite possible that in the > > future some other languag

Re: Upgrading from 11 to 13

On 2021-Apr-02, Koen De Groote wrote: > I seem to recall that going from 11 to 12, a certain configuration file was > removed and the keys are now expected to be set in the regular > configuration file? The logic being there should only ever be 1 > configuration file. > > I can't find it, but at

Re: could not read from hash-join temporary file: SUCCESS && DB goes into recovery mode

om error messages where errno would be bogus. While we're modifying those strings, add block numbers and short read byte counts where appropriate. Back-patch to all supported releases. Reported-by: Amit Khandekar Reviewed-by: Melanie Plageman Reviewed-by: Alva

Re: could not read from hash-join temporary file: SUCCESS && DB goes into recovery mode

On 2021-Apr-19, Reid Thompson wrote: > Thanks - I found that, which seems to fix the error handling right? Or > does it actually correct the cause of the segfault also? Uh, what segfault? You didn't mention one. Yes, it fixes the error handling, so when the system runs out of disk space, that's

Re: DB size

On 2021-Apr-26, luis.robe...@siscobra.com.br wrote: > SELECT sum(pg_total_relation_size(relid)), > pg_size_pretty(sum(pg_total_relation_size(relid))) > FROM pg_catalog.pg_stat_all_tables > > > sum |pg_size_pretty| > +--+ > 518549716992|483 GB

Re: Partitioned Table Index Column Order

On 2021-Jun-23, Rumpi Gravenstein wrote: > As a best practice is it better to create the primary key starting or > ending with the partition column? It is not relevant from the partitioning point of view. Other factors can be used to decide the column order. -- Álvaro Herrera Valdivia, C

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

On 2021-Jun-28, Ron wrote: > 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: ERROR: cannot freeze committed xmax

Hi Sasha On 2021-Jul-14, Sasha Aliashkevich wrote: > lp | ctid | xmin| xmax | xmax_is_lock | xmax_committed | > xmax_rolled_back | xmax_multixact > +-+---+--+--++--+ > 19 | (75,21) | 571 | 572

Re: ERROR: cannot freeze committed xmax

this, with what version, when did you upgrade this to 10.14? That may help search the commit log for bugfixes that might explain the bug. I just remembered this one as my favorite candidate: Author: Alvaro Herrera Branch: master Release: REL_11_BR [d2599ecfc] 2018-05-04 18:24:45 -0300 B

Re: RDS Proxy war stories?

On 2021-Jul-27, Quinn David Weaver wrote: > Hi, > > Does anyone here have experience to share regarding Amazon's RDS > Proxy? Subjective or quantitative, positive or negative, anything you > have is great. I know one instance of its use. The RDS instance it serves is very large (it used the lar

Re: PostgreSQL reference coffee mug

On 2021-Jul-28, Adrian Klaver wrote: > On 7/28/21 8:01 AM, Georg H. wrote: > To add to above: > > \set\s allone -> alone Actually that's wrong, because \s prints history not variables. This needs to read \set alone ... I'm *not* going to get into this, because I know I'm capable of sp

Re: Getting pg_stat_database data takes significant time

Two things, 1. this depends on reading the stats file; that's done once per transaction. So if you run the query twice in a transaction, the second time will take less time. You can know how much time is spent reading that file by subtracting both times. 2. EXPLAIN (VERBOSE) will tell you which

Re: Getting pg_stat_database data takes significant time

On 2021-Aug-11, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > > 1. this depends on reading the stats file; that's done once per > > transaction. So if you run the query twice in a transaction, the second > > time

Re: Can we get rid of repeated queries from pg_dump?

On 2021-Aug-28, Tom Lane wrote: > Here is a second patch, quite independent of the first one, that > gets rid of some other repetitive queries. Another pointlessly repetitive query is in getTriggers, which we run once per table to be dumped containing triggers. We could reduce that by running it

Re: datfrozenxid not dropping after vacuum

evalidate the rd_rel portion of a relcache entry when invalid. This implies a bit of extra complexity to deal with bootstrapping, but it's not too bad. The fix for 2) is simpler, simply always remove both the shared and local init files. Author: Andres Freund Review

Re: datfrozenxid not dropping after vacuum

On 2021-Sep-01, Matthew Tice wrote: > Hi Alvaro, thanks for the quick reply. Hi. Glad to hear that your problem is now behind. > I'm scheduled to do my patching maintenance at the end of this month - > but at this point I don't think I'm going to make it. > > Other than patching, is there a wor

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On 2021-Jun-22, Mike Yeap wrote: > I have a Postgres version 11.11 configured with both physical replication > slots (for repmgr) as well as some logical replication slots (for AWS > Database Migration Service (DMS)). This morning, the server went panic with > the following messages found in the l

Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe

On 2021-Oct-15, Nick Renders wrote: > Hello, > > I have been trying to import a Postgres 11 database into Postgres 14, but > the pg_restore command exits with the following message: > > pg_restore: error: could not write to the communication channel: Broken > pipe > > The command I sent l

Re: Merge into does not work

On 2021-Nov-26, Shaozhong SHI wrote: > MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id > WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume > WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume); It does work for me: 55479 15devel 680346=# MERGE INTO Stock USING Buy

Re: Merge into does not work

On 2021-Nov-26, Adrian Klaver wrote: > On 11/26/21 11:44, Alvaro Herrera wrote: > > On 2021-Nov-26, Shaozhong SHI wrote: > > > > I am using the MERGE patch I posted here[1], on top of Postgres 15. > > > > https://postgr.es/m/20252245.byerxxac444d@alvherre.pg

Re: Match 2 words and more

On 2021-Nov-28, Shaozhong SHI wrote: > this is supposed to find those to have 2 words and more. > > select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$'; > > But, it finds only one word as well. How about something like this? '^([[:<:]][[:alpha:]]+[[:>:]]( |$)){2}$' You

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

On 2022-Jan-10, Dominique Devienne wrote: > Given max_locks_per_transaction * (max_connections > > + max_prepared_transactions >

Re: Undetected Deadlock

On 2022-Jan-25, Michael Harris wrote: > We've recently updated our application to PG 14.1, and in the test instance we > have started to see some alarming undetected deadlocks. This is indeed suspicious / worrisome / curious. What version were you using previously? I reformatted the result sets

Re: Can we go beyond the standard to make Postgres radically better?

On 2022-Feb-13, Guyren Howe wrote: > I’m back to just having no earthly idea why anyone who finds relations > to be a productive tool for building a model would think that SQL > being the only means to do that is Okay. There are aspects other than technical reasons alone why some things live on w

Re: alter function/procedure depends on extension

On 2022-Feb-17, Bryn Llewellyn wrote: > qu. 1. What is the use-case for "alter function/procedure depends on > extension"? Suppose you have an extension that implements change auditing for tables, which works by creating triggers on the audited tables. You enable auditing for specific tables by

Re: To all who wish to unsubscribe

Edson Carlos Ericksson Richter wrote: > I've been affected also, in a minor way, because I used to receive digest  2 > or 3 times a day, and now I'm receiving every e-mail again. > This is, at least, annoying. > would be better to keep the user options when migrating from one software to > another

Re: To all who wish to unsubscribe

Steve Atkins wrote: > Removing Subject tagging and footers of the messages isn't an accidental > side effect of the migration, it's (I assume) a primary goal of it. > If that weren't done, more and more people at large consumer mailbox > providers would increasingly have problems sending mail succ

Re: Nightmare? was unsubscribe

Andrew Sullivan wrote: > That would change the body of the mail. Mail that is DKIM signed (or > worse conforms to DMARC) cannot be so changed, or it will fail > validation and will be bounced. The bouncing system is then doing the > correct thing, and yet it will create "excessive bounces" to th

Re: WAL reducing size

sql2pg wrote: > how about uncommitted(open transactions) . if a segment has 1 committed and > 2 uncommitted transactions then will it keep the segment instead deleting it > after checkpoint , since it has 2 uncommitted transactions There is no connection between transaction commit and checkpoint

Re: Feature idea: Dynamic Data Making

Riccardo Bassani wrote: > As SQL Server 2016 ( > https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking > ) , or Oracle does: it could be a neat feature having Dynamic Data > Masking, even if it has some limitations, it could help in the > application development, i

Re: a back up question

Carl Karsten wrote: > Nothing wrong with lots of tables and data. > > Don't impose any constraints on your problem you don't need to. > > Like what are you backing up to?$400 for a 1T ssd or $80 fo a 2T usb3 > spinny disk. > > If you are backing up while the db is being updated, you need to

Re: a back up question

Carl Karsten wrote: > On Tue, Dec 5, 2017 at 4:15 PM, Alvaro Herrera > wrote: > > A backup generated by pg_dump never includes writes that are in flight > > while the backup is being taken. That would make the backup absolutely > > worthless! > > Hmm, i kinda g

Re: Dependency tree to tie type/function deps to a table

Jeremy Finzel wrote: > I appreciate that, Melvin. However, this doesn't do the recursive part. > It doesn't show me type or function dependencies, for example: You need a WITH RECURSIVE query ... If you do figure it out, please publish it as in this section of the wiki https://wiki.postgresql.o

Re: Logical replication blocking alter/drop

Mark Fletcher wrote: > Hi All, > > Postgres 9.6.5. We run several logical replication processes off our main > postgres server. What we've noticed is that schema changes seem to block > until we halt the logical replication processes. For example, I just did a > 'DROP INDEX CONCURRENTLY' command,

Re: Size of pg_multixact/members increases 11355

Yogesh Sharma wrote: > Dear Thomas , > > Thanks for sharing information. > > Is it possible to remove mentioned folder files in some time intervals by > some DB command? > Currently i can not upgrade to 3.6.20. > So please share if any solution is available. If you do not upgrade, you risk getti

Re: reclaiming space from heavily used tables?

Jeremy Finzel wrote: > This is what you want: https://github.com/reorg/pg_repack > > This has been around for many years and is a very trusted extension (when > will it be in core). It's been around, but is it trusted? I for one do not trust it. See for example https://www.postgresql.org/m

Re: reclaiming space from heavily used tables?

Jeremy Finzel wrote: > > > > It's been around, but is it trusted? I for one do not trust it. See > > for example > > https://www.postgresql.org/message-id/CA+TgmoaWoU+BpBG4nwz1L > > gkmdsbl6_hm9r8jnqte-5vrdls...@mail.gmail.com > > Needs some discussion. Has anyone actually reported corruption re

Re: PostgreSQL suitable?

Vincenzo Romano wrote: > I've not tested PG10. But it's not released for production yet! It definitely is, for a couple of months now. 10.1 (the first bugfix release) has been out for over a month. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Suppor

Re: What does tcop stand for?

Tatsuo Ishii wrote: > >>> currently browsing the source code in src/include/tcop. What does tcop > >>> stand for? Can not find any hints in the files. > > > >>Traffic cop. > > > > Thanks > > cop mean? > > A cop means a policeman. Probably the name came from that it is > responsible for the

Re: Deadlock between concurrent index builds on different tables

Jeremy Finzel wrote: > > > > > >> Each index build needs to wait for all other transactions > >> (Including the ones used by the other index build) to finish. > >> So I don't think a deadlock here is unexpected. > Does that mean I should never build more than one concurrent index at > a time withi

Re: Deadlock between concurrent index builds on different tables

Jeremy Finzel wrote: > On Tue, Dec 26, 2017 at 10:28 AM, Alvaro Herrera > wrote: > > > Jeremy Finzel wrote: > > > > > > > >> Each index build needs to wait for all other transactions > > > >> (Including the ones used by the other index bu

Re: Does PostgreSQL check database integrity at startup?

Edson Carlos Ericksson Richter wrote: > I'm rebuilding the standby server for two days already, with 23% of > completion status... So how do you build your standbys, exactly? Maybe there's a bug in your procedure, rather than a bug in the software. -- Álvaro Herrerahttps://www.

Re: Does PostgreSQL check database integrity at startup?

David Steele wrote: > pgBackRest will validate all page checksums (including indexes, etc.) in the > cluster during backup. Full backups check everything, incr/differential > backups check only the files that have changed. If a table or index file is of zero length when backed up, as in the desc

Re: Deadlock between concurrent index builds on different tables

Alban Hertroys wrote: > Does that mean that at step 3 one could issue this?: > > ./configure `pg_config —configure` Not exactly, because pg_config emits the arguments in quotes and the shell passes them as is to configure which doesn't like that. This works: eval ./configure `pg_config --co

Re: Does PostgreSQL check database integrity at startup?

Stephen Frost wrote: > * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > > Anyway, instead digging into rsync functionality (or bugs - I doubt, > > but who knows?), I do prefer to have a script I can run to check if > > there is obvious failures in standby servers. > > As mentio

Re: Does PostgreSQL check database integrity at startup?

Stephen Frost wrote: > It's entirely unclear to me what the problem being complained about in > this thread actually is. As Edson explained, a relfilenode in the standby server is zero bytes long when it is not that size in the primary server, and it corresponds to a persistent table. I don't ha

Re: Does PostgreSQL check database integrity at startup?

For context: this was first reported in the Barman forum here: https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ They are using Barman for the backups. Stephen Frost wrote: > > But at some point in time, slave became corrupt (one of the base > > files are zero size where it

Re: postgresql9.5 partition table

mark wrote: > I defined a table using 1000 partition tables. > When I deleted or updated this table. It used a lot of memory and > postmaster process is killed by OS. When I defined the same table > using 100 partition tables. It can normally be updated and deleted. > what is reason of this? Postg

Re: Mailing list archiver

Michelle Konzack wrote: > This is bizzar, because the URL > is perfectly working for me. It fails for me exactly as Peter described. I added this entry to /etc/hosts: 54.231.120.105 pg.rsa.pub and then I could see the site. It looks like an interesting interface. There a

Re: What generates pg_config.h?

Travis Allison wrote: > No instance of Postgres 10 running. I took Tom's suggestion: I ran dpkg -S > /usr/include/postgresql/pg_config.h > Result: libpq-dev: /usr/include/postgresql/pg_config.h > > Here is the result from dpkg-query -l | grep libpq > > ii libpq-dev

Re: help with generation_series in pg10

Márcio A. Sepp wrote: > how can i have the same in pg10 as i have had in pg 9.x? Move the function call to the FROM clause: select g, (g - 1) % 5 + 1 from generate_series(1, 10) g; > I need it to date type to... if possible. There is a generate_series() variant that can return dates (more

Re: RES: help with generation_series in pg10

Márcio A. Sepp wrote: > > There is a generate_series() variant that can return dates (more > > precisely, timestamp with time zone). But what exactly would you like > > returned? > > In the past i use querys like this to generate some timestamp field: > select generate_series (1, 10), generate_s

Re: data-checksums

Rakesh Kumar wrote: > > > That said, imv anyway, the performance hit is small and having > > checksums is well worth it. > > I also would like to believe that the hit is small, but when PG > official document writes "noticeable performance penalty", it becomes > difficult to convince management t

Re: Segmentation fault with core dump

Tom Lane wrote: > Glauco Torres writes: > > (gdb) bt > > #0 ckpt_buforder_comparator (pa=pa@entry=0x7f6fa9ef4b2c, > > pb=pb@entry=0x1be06d2d06644) > > at bufmgr.c:4137 > > #1 0x00801268 in med3 (a=0x7f6fa9ef4b2c "\177\006", > > b=0x1be06d2d06644 , > > c=0x2fc9dfbb1815c , cmp=0x6a4d20 > >

Re: Segmentation fault with core dump

Merlin Moncure wrote: > simple > SELECT version(); > ...can give a lot of hints on who/what compiled the database if you don't > know. Probably, this is why Glauco included the output in his opening letter. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 2

Re: Extra files in "base" dir not seen in relfilenodes

Tom Lane wrote: > However, assuming you've gotten that detail right, then any file > you can't match up with a relfilenode value must be an orphan you > can just "rm". Maybe look in pg_buffercache for entries referencing those files before deleting. It would be surprising to see any if no catalo

Re: "could not receive data from client" && "incomplete startup packet"

David Gauthier wrote: > Hi David, thanks for the response. > > I upgraded to 9.3.2 and recreated the DB there. So far, so good. I sincerely hope you mean 9.3.20 and not 9.3.2. There are critical bugs that can eat your data in the 9.3 series prior to 9.3.11, so by all means do upgrade to the lat

Re: "could not receive data from client" && "incomplete startup packet"

David Gauthier wrote: > I thin kit's 9.3.2 :-( > > % psql -V > psql (PostgreSQL) 9.3.2 Ah, you're screwed then. My condolences. > These are my options... > ls -ld /tool/pandora64/.package/postgresql* > drwxr-xr-x 8 pandora pandora 4096 Sep 2 2016 > /tool/pandora64/.package/postgresql-8.2.3 >

Re: Trying to the behavior of a parallel query with with a change in the transaction isolation mode

Luis Carril wrote: > The transaction isolation level is serializable. This situation does not > normally arise, because parallel query plans are not generated when the > transaction isolation level is serializable. However, it can happen if the > transaction isolation level is changed to serial

Re: CLOSE_WAIT pileup and Application Timeout

On 2024-Oct-07, KK CHN wrote: > On Mon, Oct 7, 2024 at 12:07 AM Alvaro Herrera > wrote: > Where do I have to introduce the TCP keepalives ? in the OS level or > application code level ? > > [root@dbch wildfly-27.0.0.Final]# cat /proc/sys/net/ipv4/tcp_keepalive_time > 7200

Re: CLOSE_WAIT pileup and Application Timeout

On 2024-Oct-04, KK CHN wrote: > The mobile tablets are installed with the android based vehicle > tracking app which updated every 30 seconds its location fitted inside the > vehicle ( lat long coordinates) to the PostgreSQL DB through the java > backend application to know the latest location of

Re: What are best practices wrt passwords?

On 2024-Oct-16, mb...@mbork.pl wrote: > I understand why giving the password on the command line or in an > environment variable is a security risk (because of `ps`), but I do not > understand why `psql` doesn't have an option like `--password-command` > accepting a command which then prints the p

Re: CREATE SCHEMA ... CREATE M.V. support

Hello Kirill On 2024-Nov-11, Kirill Reshke wrote: > I was exploring the PostgreSQL parser and discovered a very > interesting feature. Users can create schema along with schema objects > in single SQL. Yeah, it's pretty cool. > Support for materialized views began in 9.3. Perhaps, then, this is

Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed

On 2024-Nov-10, Tom Lane wrote: > This surprised me a bit too, because I thought we took a > slightly-less-than-exclusive lock for FK additions or deletions. > Tracing through it, I find that CloneFkReferencing opens the > referenced relation with ShareRowExclusiveLock as I expected. > But then we

Re: Fwd: A million users

On 2024-Nov-13, Vijaykumar Jain wrote: > I tried to grant select permissions to 5000 different roles on one table, > It failed with row size too big already at 2443. But you can grant select to one "reader" role, and grant that one role to however many other roles you want. This way you can have

Re: Fwd: A million users

On 2024-Nov-13, Kaare Rasmussen wrote: > Sorry if my original post was unclear, but I don't expect that there > will be much more than perhaps a hundred roles. Each may have from a > few up to a million users in them, though. In Postgres, a user is a role. So if you have a hundred roles and a mi

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

On 2024-Nov-27, Tom Lane wrote: > I doubt that there's anything actually wrong with the catalog state at > this point (perhaps Alvaro would confirm that). That leads to the > conclusion that what's wrong is the release notes' query for fingering > broken constraints, and it needs some additional

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

On 2024-Nov-27, Tom Lane wrote: > I doubt that there's anything actually wrong with the catalog state at > this point (perhaps Alvaro would confirm that). That leads to the > conclusion that what's wrong is the release notes' query for fingering > broken constraints, and it needs some additional

<    1   2   3   4   >