Re: Duplicate history file?

Hi Horiguchi-san, Regarding "test ! -f", I am wondering how many people are using the test command for archive_command. If I remember correctly, the guide provided by NTT OSS Center that we are using does not recommend using the test command. I think, as the PG-REX documentation says, the simp

Re: Multiple hosts in connection string failed to failover in non-hot standby mode

On Sun, Jun 06, 2021 at 05:27:49PM -0400, Tom Lane wrote: > It seems like nobody's terribly interested in figuring out why > pg_GSS_have_cred_cache() is misbehaving on Windows. I have been investigating that for a couple of hours in total, but nothing to report yet. > So I took > a look at disabl

Re: Fast COPY FROM based on batch insert

On 4/6/21 13:45, tsunakawa.ta...@fujitsu.com wrote: From: Andrey Lepikhov We still have slow 'COPY FROM' operation for foreign tables in current master. Now we have a foreign batch insert operation And I tried to rewrite the patch [1] with this machinery. I haven't looked at the patch, but n

Re: Decoding of two-phase xacts missing from CREATE_REPLICATION_SLOT command

On Fri, Jun 4, 2021 at 2:29 PM Ajin Cherian wrote: > > On Fri, Jun 4, 2021 at 1:06 PM Amit Kapila wrote: > > > I think we can try but not sure if we can get it by then. So, here is > > my suggestion: > > a. remove the change in CreateReplicationSlotCmd > > b. prepare the patches for protocol chan

Re: locking [user] catalog tables vs 2pc vs logical rep

On Mon, Jun 7, 2021 at 9:26 AM vignesh C wrote: > > On Mon, Jun 7, 2021 at 4:18 AM osumi.takami...@fujitsu.com > wrote: > > > > On Thursday, June 3, 2021 7:07 PM Amit Kapila > > wrote: > > > On Thu, Jun 3, 2021 at 9:18 AM osumi.takami...@fujitsu.com > > > wrote: > > > > Thank you for providing

Re: Race condition in recovery?

Sorry, some extra words are left alone. At Mon, 07 Jun 2021 13:57:35 +0900 (JST), Kyotaro Horiguchi wrote in > As I said upthread the relationship between receiveTLI and > recoveryTargetTLI is not confirmed yet at the point. - findNewestTimeLine() simply searches for the history file with the -

Re: Race condition in recovery?

At Fri, 4 Jun 2021 10:56:12 -0400, Robert Haas wrote in > On Fri, Jun 4, 2021 at 5:25 AM Kyotaro Horiguchi > wrote: > > I think that's right. And the test script detects the issue for me > > both on Linux but doesn't work for Windows. > > > > '"C:/../Documents/work/postgresql/src/test/recovery/t

Re: Asynchronous Append on postgres_fdw nodes.

On Fri, Jun 4, 2021 at 7:26 PM Etsuro Fujita wrote: > On Tue, Jun 1, 2021 at 6:30 PM Etsuro Fujita wrote: > > On Fri, May 28, 2021 at 10:53 PM Etsuro Fujita > > wrote: > > > On Fri, May 28, 2021 at 5:29 PM Kyotaro Horiguchi > > > wrote: > > > > The patch drops some "= NULL" (initial) initializ

Re: locking [user] catalog tables vs 2pc vs logical rep

On Mon, Jun 7, 2021 at 4:18 AM osumi.takami...@fujitsu.com wrote: > > On Thursday, June 3, 2021 7:07 PM Amit Kapila wrote: > > On Thu, Jun 3, 2021 at 9:18 AM osumi.takami...@fujitsu.com > > wrote: > > > Thank you for providing the patch. > > > I have updated your patch to include some other view

Re: Decoding speculative insert with toast leaks memory

On Mon, 7 Jun 2021 at 8:30 AM, Amit Kapila wrote: > On Wed, Jun 2, 2021 at 11:52 AM Amit Kapila > wrote: > > > > On Wed, Jun 2, 2021 at 11:38 AM Dilip Kumar > wrote: > > > > > > On Wed, Jun 2, 2021 at 11:25 AM Amit Kapila > wrote: > > > > > > > > I think the same relation case might not create

Re: Decoding speculative insert with toast leaks memory

On Wed, Jun 2, 2021 at 11:52 AM Amit Kapila wrote: > > On Wed, Jun 2, 2021 at 11:38 AM Dilip Kumar wrote: > > > > On Wed, Jun 2, 2021 at 11:25 AM Amit Kapila wrote: > > > > > > I think the same relation case might not create a problem because it > > > won't find the entry for it in the toast_has

contrib/pg_visibility fails regression under CLOBBER_CACHE_ALWAYS

husky just reported $SUBJECT: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=husky&dt=2021-06-05%2013%3A42%3A17 and I find I can reproduce that locally: diff -U3 /home/postgres/pgsql/contrib/pg_visibility/expected/pg_visibility.out /home/postgres/pgsql/contrib/pg_visibility/results/pg_

Re: please update ps display for recovery checkpoint

On Mon, Dec 14, 2020 at 12:01:33PM +0900, Michael Paquier wrote: > On Sat, Dec 12, 2020 at 12:41:25AM +, Bossart, Nathan wrote: > > On 12/11/20, 4:00 PM, "Michael Paquier" wrote: > >> My counter-proposal is like the attached, with the set/reset part not > >> reversed this time, and the code in

Re: ALTER SUBSCRIPTION REFRESH PUBLICATION has default copy_data = true?

On Thu, Jun 3, 2021 at 12:52 AM Peter Eisentraut wrote: > > Is that a deliberate functionality, or is it a quirk / bug? > > copy_data is an option of the action, not a property of the > subscription. The difference between those two things is admittedly not > clearly (at all?) documented. > > H

Re: Transactions involving multiple postgres foreign servers, take 2

> 2021/06/04 21:38、Masahiko Sawada のメール: > > On Fri, Jun 4, 2021 at 5:16 PM Masahiko Sawada wrote: >> >> On Fri, Jun 4, 2021 at 3:58 PM ikeda...@oss.nttdata.com >> wrote: >>> >>> >>> >>> 2021/06/04 12:28、Masahiko Sawada のメール: >>> >>> >>> Thank you for pointing it out. This idea has been

Re: Transactions involving multiple postgres foreign servers, take 2

> 2021/06/04 17:16、Masahiko Sawada のメール: > > On Fri, Jun 4, 2021 at 3:58 PM ikeda...@oss.nttdata.com > wrote: >> >> >> >> 2021/06/04 12:28、Masahiko Sawada のメール: >> >> >> Thank you for pointing it out. This idea has been proposed several >> times and there were discussions. I'd like to sum

RE: locking [user] catalog tables vs 2pc vs logical rep

On Thursday, June 3, 2021 1:09 PM vignesh C wrote: > On Thu, Jun 3, 2021 at 9:18 AM osumi.takami...@fujitsu.com > wrote: > > Thank you for providing the patch. > > I have updated your patch to include some other viewpoints. > > > > I also included the description about TRUNCATE on user_catalog_ta

RE: locking [user] catalog tables vs 2pc vs logical rep

On Thursday, June 3, 2021 7:07 PM Amit Kapila wrote: > On Thu, Jun 3, 2021 at 9:18 AM osumi.takami...@fujitsu.com > wrote: > > Thank you for providing the patch. > > I have updated your patch to include some other viewpoints. > > > > I suggest creating a synchronous replication part of the patch

Re: when the startup process doesn't

On Fri, Jun 04, 2021 at 07:49:21PM +0530, Nitin Jadhav wrote: > I have added the proper logs in all of the above scenarios. > > Following is the sample log displayed during server startup when the > time period is set to 10ms. > > 2021-06-04 19:40:06.390 IST [51116] LOG: Syncing data directory,

Re: Move pg_attribute.attcompression to earlier in struct for reduced size?

Em sáb., 5 de jun. de 2021 às 11:16, Ranier Vilela escreveu: > > Alvaro Herrera writes: > > > > Now, while this patch does seem to work correctly, it raises a number > of > > > weird cpluspluscheck warnings, which I think are attributable to the > > > new macro definitions. I didn't look into it

Re: Strangeness with UNIQUE indexes and UTF-8

Omar Kilani writes: > How is anyone supposed to know about this issue? We're working on infrastructure to help detect OS locale changes, but it's not shipped yet. regards, tom lane

Re: Multiple hosts in connection string failed to failover in non-hot standby mode

It seems like nobody's terribly interested in figuring out why pg_GSS_have_cred_cache() is misbehaving on Windows. So I took a look at disabling GSSENC in these test cases to try to silence hamerkop's test failure that way. Here's a proposed patch. It relies on setenv() being available, but I thi

Re: Strangeness with UNIQUE indexes and UTF-8

I mean, maybe it's because I've been awake since... 7am yesterday, but it seems to me that if Postgres fails catastrophically silently (and I would say "it looks like all your data in this table disappeared because of some arcane locale / btree issue that no one except Tom Lane even knows exists" -

Re: Strangeness with UNIQUE indexes and UTF-8

We do use ON CONFLICT… it doesn’t work because the index is both “good” and “bad” at the same time. On Sun, Jun 6, 2021 at 2:03 PM Justin Pryzby wrote: > On Sun, Jun 06, 2021 at 03:54:48AM -0700, Omar Kilani wrote: > > What I sort of don't get is... before we insert anything into these > > table

Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, Jun 06, 2021 at 03:54:48AM -0700, Omar Kilani wrote: > What I sort of don't get is... before we insert anything into these > tables, we always check to see if a value already exists. And Postgres > must be returning no results for some reason. So it goes to insert a > duplicate value which

Re: installcheck failure in indirect_toast with default_toast_compression = lz4

On Sat, Jun 05, 2021 at 09:20:43AM +0900, Michael Paquier wrote: > As said in $subject, installcheck fails once I set up a server with > default_toast_compression = lz4 in the test indirect_toast. Please > see the attached for the diffs. > > The issue is that the ordering of the tuples returned b

Re: PoC/WIP: Extended statistics on expressions

Tomas Vondra writes: > On 6/6/21 9:17 PM, Tom Lane wrote: >> I'm curious about how come the buildfarm didn't notice this. The >> animals using COPY_PARSE_PLAN_TREES should have failed. The fact >> that they didn't implies that there's no test case that makes use >> of a nonzero value for this fi

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

On Sun, Jun 06, 2021 at 07:26:22PM +0200, Matthias van de Meent wrote: > I think it would be helpful for further debugging if we would have the > state of the all tuples on that page (well, the tuple headers with > their transactionids and their line pointers), as that would help with > determining

Re: PoC/WIP: Extended statistics on expressions

On 6/6/21 9:17 PM, Tom Lane wrote: > Tomas Vondra writes: >> On 6/6/21 7:37 AM, Noah Misch wrote: >>> This added a "transformed" field to CreateStatsStmt, but it didn't mention >>> that field in src/backend/nodes. Should those functions handle the field? > >> Yup, that's a mistake - it should

Re: list of extended statistics on psql (\dX)

Hi, Here's a slightly more complete patch, tweaking the regression tests a bit to detect this. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 2abf255798..eba659705e 100644

Re: speed up verifying UTF-8

On Thu, Jun 3, 2021 at 3:22 PM Heikki Linnakangas wrote: > > On 03/06/2021 22:16, Heikki Linnakangas wrote: > > On 03/06/2021 22:10, John Naylor wrote: > >> On Thu, Jun 3, 2021 at 3:08 PM Heikki Linnakangas >> > wrote: > >> > x1 = half1 + UINT64CONST(0x7f

Re: PoC/WIP: Extended statistics on expressions

Tomas Vondra writes: > On 6/6/21 7:37 AM, Noah Misch wrote: >> This added a "transformed" field to CreateStatsStmt, but it didn't mention >> that field in src/backend/nodes. Should those functions handle the field? > Yup, that's a mistake - it should do whatever CREATE INDEX is doing. Not > sure

Re: PoC/WIP: Extended statistics on expressions

On 6/6/21 7:37 AM, Noah Misch wrote: > On Sat, Mar 27, 2021 at 01:17:14AM +0100, Tomas Vondra wrote: >> OK, pushed after a bit more polishing and testing. > > This added a "transformed" field to CreateStatsStmt, but it didn't mention > that field in src/backend/nodes. Should those functions han

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

I wrote: > We could make use of COMPARE_COERCIONFORM_FIELD 100% correct by removing > these two tests of the funcformat value, but on the whole I doubt that > would be better. On still closer inspection, that seems like it'd be fine. All of the gram.y productions that emit COERCE_SQL_SYNTAX also

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

On Sun, Jun 6, 2021 at 11:43 AM Justin Pryzby wrote: > Sorry, but I already killed the process to try to follow Matthias' suggestion. > I have a core file from "gcore" but it looks like it's incomplete and the > address is now "out of bounds"... Based on what you said about ending up back in lazy

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

On Sun, Jun 06, 2021 at 11:00:38AM -0700, Peter Geoghegan wrote: > On Sun, Jun 6, 2021 at 9:35 AM Justin Pryzby wrote: > > I'll leave the instance running for a little bit before restarting (or > > kill-9) > > in case someone requests more info. > > How about dumping the page image out, and shar

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

Hi, On Sun, Jun 6, 2021, at 10:59, Tom Lane wrote: > Matthias van de Meent writes: > > On Sun, 6 Jun 2021 at 18:35, Justin Pryzby wrote: > >> However, I also found an autovacuum chewing 100% CPU, and it appears the > >> problem is actually because autovacuum has locked a page of pg-statistic,

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

On Sun, Jun 6, 2021 at 9:35 AM Justin Pryzby wrote: > I'll leave the instance running for a little bit before restarting (or kill-9) > in case someone requests more info. How about dumping the page image out, and sharing it with the list? This procedure should work fine from gdb: https://wiki.po

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

Matthias van de Meent writes: > On Sun, 6 Jun 2021 at 18:35, Justin Pryzby wrote: >> However, I also found an autovacuum chewing 100% CPU, and it appears the >> problem is actually because autovacuum has locked a page of pg-statistic, and >> every other process then gets stuck waiting in the plan

Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

On Sun, 6 Jun 2021 at 18:35, Justin Pryzby wrote: > > An internal instance was rejecting connections with "too many clients". > I found a bunch of processes waiting on a futex and I was going to upgrade the > kernel (3.10.0-514) and dismiss the issue. > > However, I also found an autovacuum chewin

Re: Strangeness with UNIQUE indexes and UTF-8

Hey Chap, Yeah, I understand. Just ruling out the bad hardware scenario. Plus the next person to Google this will hopefully stumble upon this thread. :) Regards, Omar On Sun, Jun 6, 2021 at 9:36 AM Chapman Flack wrote: > On 06/06/21 11:08, Omar Kilani wrote: > > I'm running pg_verify_checksum

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

I wrote: > Noah Misch writes: >> equalfuncs.c has been using COMPARE_COERCIONFORM_FIELD() to ignore >> differences >> in fields of this type. Does this spot have cause to depart from the >> pattern? > Oversight, I think. Will fix. After looking closer, I see that there are a couple of very v

Re: Strangeness with UNIQUE indexes and UTF-8

On 06/06/21 11:08, Omar Kilani wrote: > I'm running pg_verify_checksums on the cluster, but the database is > many TB so it'll be a bit. Index corruption because of a locale change would not be the sort of thing checksums would detect. Entries would be put into the index in the correct order accor

pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic

An internal instance was rejecting connections with "too many clients". I found a bunch of processes waiting on a futex and I was going to upgrade the kernel (3.10.0-514) and dismiss the issue. However, I also found an autovacuum chewing 100% CPU, and it appears the problem is actually because aut

Re: Strangeness with UNIQUE indexes and UTF-8

Hey Magnus, Hmmm, okay -- that's unfortunate. :) I apparently wrote a script in 2017 to find duplicates from this issue on the other table and fix them up. Maybe a similar locale thing happened back then? Anyway, for what it's worth: Checksum scan completed Data checksum version: 1 Files scanne

Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, Jun 6, 2021 at 5:19 PM Omar Kilani wrote: > > Hey Tom, > > The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on > a RHEL 7.x machine. > > The only other upgrade has been to RHEL 8.x. So the locale data change > might have changed something -- thanks for that information.

Re: Strangeness with UNIQUE indexes and UTF-8

Hmmm. Is it possible that in some version of 11.x, the corrupt index stopped "working"? As in, yes, it may have been corrupt but still returned data on version 11.y, whereas on version 11.z it's no longer working and returns nothing? David mentions that change in 11.11...? I guess I can try some

Re: Strangeness with UNIQUE indexes and UTF-8

Hey Tom, The database was pg_dump'ed out of 10.4 and pg_restore'd into 11.2 on a RHEL 7.x machine. The only other upgrade has been to RHEL 8.x. So the locale data change might have changed something -- thanks for that information. We've seen this issue on a different table before upgrading to RH

Re: Misplaced superuser check in pg_log_backend_memory_contexts()

Julien Rouhaud writes: > On Sun, Jun 06, 2021 at 03:53:10PM +0900, Michael Paquier wrote: >> It seems to me that we had better check for a superuser at the >> beginning of the function, like in the attached. > However +1 for the patch, as it seems more consistent to always get a > permission fail

Re: Strangeness with UNIQUE indexes and UTF-8

I was able to restore a snapshot where the database was fully consistent. 2021-06-06 14:52:34.748 UTC [0/48529] LOG: database system was interrupted while in recovery at log time 2021-06-06 06:57:27 UTC 2021-06-06 14:52:34.748 UTC [0/48529] HINT: If this has occurred more than once some data mig

Re: Strangeness with UNIQUE indexes and UTF-8

Omar Kilani writes: > This is a very old database (2004) that has moved forward via pg_upgrade. I > think we did a pg_dump and pg_restore every time we hit some major > incompatibility like float vs integer date times. If it's that old, it's likely also survived multiple OS upgrades. It seems cle

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Noah Misch writes: > equalfuncs.c has been using COMPARE_COERCIONFORM_FIELD() to ignore differences > in fields of this type. Does this spot have cause to depart from the pattern? Oversight, I think. Will fix. regards, tom lane

Re: Strangeness with UNIQUE indexes and UTF-8

I just remembered, I have… many… snapshots of the on disk data prior to starting 11.12. It should be possible to start at a specific point in time with the index in the state it was in prior to the insert. How do I prove or disprove… hardware issues? Also… I ran the select on 3 of our standby se

Re: Strangeness with UNIQUE indexes and UTF-8

Hey David, Hmmm… it wasn’t init on 11.x. This is a very old database (2004) that has moved forward via pg_upgrade. I think we did a pg_dump and pg_restore every time we hit some major incompatibility like float vs integer date times. The current DB started as a pg_restore into 10.x. Then was pg_

Re: Misplaced superuser check in pg_log_backend_memory_contexts()

On Sun, Jun 6, 2021 at 12:23 PM Michael Paquier wrote: > > Hi all, > > While reading the code of pg_log_backend_memory_contexts(), I have > been surprised to see that the code would attempt to look at a PROC > entry based on the given input PID *before* checking if the function > has been called b

Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, 6 Jun 2021 at 22:55, Omar Kilani wrote: > There seems to be a weird bug in Postgres (last tested 11.12) where it > allows an INSERT into a table with a UNIQUE / UNIQUE CONSTRAINT index > on a TEXT/VARCHAR when there's already a value present in that index, > but only for UTF-8 input. It w

Re: Strangeness with UNIQUE indexes and UTF-8

On Sun, 2021-06-06 at 03:54 -0700, Omar Kilani wrote: > There seems to be a weird bug in Postgres (last tested 11.12) where it > allows an INSERT into a table with a UNIQUE / UNIQUE CONSTRAINT index > on a TEXT/VARCHAR when there's already a value present in that index, > but only for UTF-8 input.

Strangeness with UNIQUE indexes and UTF-8

Hi, There seems to be a weird bug in Postgres (last tested 11.12) where it allows an INSERT into a table with a UNIQUE / UNIQUE CONSTRAINT index on a TEXT/VARCHAR when there's already a value present in that index, but only for UTF-8 input. I just had this happen on our user table and it somehow

Re: SQL-standard function body

On Sat, Jun 05, 2021 at 09:44:18PM -0700, Noah Misch wrote: > On Wed, Apr 07, 2021 at 09:55:40PM +0200, Peter Eisentraut wrote: > > Committed. Thanks! > > I get a NULL pointer dereference if the function body has a doubled semicolon: > > create function f() returns int language sql begin atomi

Re: Misplaced superuser check in pg_log_backend_memory_contexts()

On Sun, Jun 06, 2021 at 03:53:10PM +0900, Michael Paquier wrote: > > While reading the code of pg_log_backend_memory_contexts(), I have > been surprised to see that the code would attempt to look at a PROC > entry based on the given input PID *before* checking if the function > has been called by