Re: adding partitioned tables to publications

On 2019-11-12 02:11, Amit Langote wrote: I don't understand why you go through great lengths to ensure that the relkinds match between publisher and subscriber. We already ensure that only regular tables are published and only regular tables are allowed as subscription target. In the future, we

Re: adding partitioned tables to publications

On 2019-11-18 09:53, Amit Langote wrote: I have spent some time hacking on this. With the attached updated patch, adding a partitioned table to publication results in publishing the inserts, updates, deletes of the table's leaf partitions as inserts, updates, deletes of the table itself (it all

Re: [PATCH] Do not use StdRdOptions in Access Methods

On Fri, Nov 15, 2019 at 10:34:55AM +0900, Michael Paquier wrote: > It seems to me that if the plan is to have one option structure for > each index AM, which has actually the advantage to reduce the bloat of > each relcache entry currently relying on StdRdOptions, then we could > have those extra a

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Tue, Nov 19, 2019 at 4:58 PM Amit Khandekar wrote: > > On Tue, 19 Nov 2019 at 14:07, Amit Kapila wrote: > > > > > > Have you tried by injecting some error? After getting the error > > mentioned above in email, when I retried the same query, I got the > > below message. > > > > postgres=# SELE

Re: pg_waldump and PREPARE

I did not see any problems in this version of the patch. The information displayed by pg_waldump for the PREPARE record is sufficient for use. Thanks Andrey and Michael for the review! I committed the patch. Regards, Hi, There is a mistake in the comment in the definition of xl_xact_relfil

Re: [HACKERS] WAL logging problem in 9.4.3?

I'm in the benchmarking week.. Thanks for reviewing!. At Sun, 17 Nov 2019 20:54:34 -0800, Noah Misch wrote in > On Tue, Nov 05, 2019 at 02:53:35PM -0800, Noah Misch wrote: > > I started pre-commit editing on 2019-10-28, and comment+README updates have > > been the largest part of that. I'll ch

Re: range_agg

On Tue, Nov 19, 2019 at 1:17 AM Pavel Stehule wrote: > Hi > I tested last patches. I found some issues Thank you for the review! > 1. you should not to try patch catversion. I've seen discussion on pgsql-hackers going both ways, but I'll leave it out of future patches. :-) > 2. there is warnin

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Tue, Nov 19, 2019 at 5:23 PM Amit Kapila wrote: > > On Mon, Nov 18, 2019 at 5:02 PM Dilip Kumar wrote: > > > > On Fri, Nov 15, 2019 at 4:19 PM Amit Kapila wrote: > > > > > > On Fri, Nov 15, 2019 at 4:01 PM Dilip Kumar wrote: > > > > > > > > On Fri, Nov 15, 2019 at 3:50 PM Amit Kapila > > >

Re: backup manifests

Hi, Since now we are generating the backup manifest file with each backup, it provides us an option to validate the given backup. Let's say, we have taken a backup and after a few days, we want to check whether that backup is validated or corruption-free without restarting the server. Please find

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 5:44 PM Amit Kapila wrote: > On Wed, Nov 20, 2019 at 12:28 AM Thomas Munro wrote: > > + if (GetLastError() == ERROR_HANDLE_EOF) > > + return 0; > Yes, this works for me. Thanks, pushed.

Re: [HACKERS] Block level parallel vacuum

On Mon, 18 Nov 2019 at 15:38, Masahiko Sawada wrote: > > On Mon, 18 Nov 2019 at 15:34, Amit Kapila wrote: > > > > On Mon, Nov 18, 2019 at 11:37 AM Masahiko Sawada > > wrote: > > > > > > On Wed, 13 Nov 2019 at 14:31, Amit Kapila wrote: > > > > > > > > > > > > Based on these needs, we came up wit

Re: backup manifests

On Tue, Nov 19, 2019 at 7:19 PM Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > > On 11/19/19 5:00 AM, Rushabh Lathia wrote: > > > > > > My colleague Suraj did testing and noticed the performance impact > > with the checksums. On further testing, he found that specifically with > > sha

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Tue, Nov 19, 2019 at 4:58 PM Amit Khandekar wrote: > > On Tue, 19 Nov 2019 at 14:07, Amit Kapila wrote: > > > > On Mon, Nov 18, 2019 at 5:50 PM Amit Khandekar > > wrote: > > > > > > For the API's that use VFDs (like PathNameOpenFile), the files opened > > > are always recorded in the VfdCach

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 4:54 PM Amit Khandekar wrote: > - BufFileLoadBuffer() seems to deliberately ignore FileRead()'s return > value if it is -1 > if (file->nbytes < 0) file->nbytes = 0; Ok, that's a different problem we need to fix then. But it does explain how we didn't know. And sure e

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 12:28 AM Thomas Munro wrote: > > On Wed, Nov 20, 2019 at 1:14 AM Juan José Santamaría Flecha > wrote: > > On Tue, Nov 19, 2019 at 12:49 PM Thomas Munro > > wrote: > >> On Wed, Nov 20, 2019 at 12:28 AM Amit Khandekar > >> wrote: > >> > On Windows, it is documented that

RE: Should we add xid_current() or a int8->xid cast?

Hi Thomas, Please let me ask something about wraparound problem. +static FullTransactionId +convert_xid(TransactionId xid, FullTransactionId next_fxid) { - uint64 epoch; + TransactionId next_xid = XidFromFullTransactionId(next_fxid); + uint32 epoch = EpochFromFullTrans

Re: Allow CREATE OR REPLACE VIEW to rename the columns

Barring any objection, I'm thinking to commit this patch. Regards, Build and All Test has passed . Looks good to me . Regards,

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, 20 Nov 2019 at 01:05, Thomas Munro wrote: > > On Wed, Nov 20, 2019 at 7:58 AM Thomas Munro wrote: > > On Wed, Nov 20, 2019 at 1:14 AM Juan José Santamaría Flecha > > > https://devblogs.microsoft.com/oldnewthing/20150121-00/?p=44863 > > > > !?! Thanks Juan and Thomas for pointing to these

Re: initdb SegFault

At Tue, 19 Nov 2019 12:06:50 -0500, Tom Lane wrote in > Andres Freund writes: > > Agreed wrt this specific failure scenario. It does however seem not > > great that callsites for PQExpBuffer ought to check every call for > > allocation failures, in the general case. > > It is possible to check

Re: checkpointer: PANIC: could not fsync file: No such file or directory

On Tue, Nov 19, 2019 at 04:49:10PM -0600, Justin Pryzby wrote: > On Wed, Nov 20, 2019 at 09:26:53AM +1300, Thomas Munro wrote: > > Perhaps we should not panic if we failed to open (not fsync) the file, > > but it's not the root problem here which is that somehow we thought we > > should be fsyncing

RE: Planning counters in pg_stat_statements (using pgss_store)

On Tue, Nov 19, 2019 at 2:27 PM, Julien Rouhaud wrote: > On Fri, Nov 15, 2019 at 2:00 AM imai.yoshik...@fujitsu.com > wrote: > > > > Actually I also don't have strong opinion but I thought someone would > > complain about renaming of those columns and > also some tools like monitoring which use

Re: Role membership and DROP

On Tue, 2019-11-19 at 13:21 -0500, Tom Lane wrote: > Laurenz Albe writes: > > On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote: > > > Laurenz Albe writes: > > > > On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote: > > > > > It might be worth clarifying this point in section 5.7, > > > > > https:

Re: Proposal- GUC for max dead rows before autovacuum

On Tue, 2019-11-19 at 15:35 -0700, Michael Lewis wrote: > To mitigate the need for per-table tuning of autovacuum configuration, I'd > like to propose a new GUC for autovacuum_vacuum_threshold_max. > > Currently, it seems that I can either set autovacuum_vacuum_scale_factor much > smaller than d

Re: checkpointer: PANIC: could not fsync file: No such file or directory

On Wed, Nov 20, 2019 at 09:26:53AM +1300, Thomas Munro wrote: > Perhaps we should not panic if we failed to open (not fsync) the file, > but it's not the root problem here which is that somehow we thought we > should be fsyncing a file that had apparently been removed already > (due to CLUSTER, VAC

Proposal- GUC for max dead rows before autovacuum

To mitigate the need for per-table tuning of autovacuum configuration, I'd like to propose a new GUC for autovacuum_vacuum_threshold_max. Currently, it seems that I can either set autovacuum_vacuum_scale_factor much smaller than default on tables with millions of rows, or set a value globally that

Re: Invisible PROMPT2

On Tue, Nov 19, 2019 at 04:02:48PM +1300, Thomas Munro wrote: > On Tue, Nov 19, 2019 at 12:09 PM Tom Lane wrote: > > You should follow the logic in pg_wcswidth: compute PQmblen() first, > > and bail out if it's more than the remaining string length, otherwise > > it's ok to apply PQdsplen(). > >

Re: backup manifests

On 11/19/19 5:00 AM, Rushabh Lathia wrote: > > My colleague Suraj did testing and noticed the performance impact > with the checksums.   On further testing, he found that specifically with > sha its more of performance impact.   We have found that SHA1 adds about 3% overhead when the backup is al

Re: Connections hang indefinitely while taking a gin index's LWLock buffer_content lock(PG10.7)

On Sun, Nov 17, 2019 at 9:18 PM Alexander Korotkov wrote: > So, I've put this explanation into README patch. I just change > designation to better match with Lehman & Yao paper and did some minor > enchantments. > > I'm going to push this patchset if no objections. So, pushed with minor changes

Re: checkpointer: PANIC: could not fsync file: No such file or directory

On Wed, Nov 20, 2019 at 12:58 AM Justin Pryzby wrote: > < 2019-11-15 22:16:07.098 EST >PANIC: could not fsync file > "base/16491/1731839470.2": No such file or directory > < 2019-11-15 22:16:08.751 EST >LOG: checkpointer process (PID 27388) was > terminated by signal 6: Aborted > > /dev/vdb

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 7:58 AM Thomas Munro wrote: > On Wed, Nov 20, 2019 at 1:14 AM Juan José Santamaría Flecha > > https://devblogs.microsoft.com/oldnewthing/20150121-00/?p=44863 > > !?! One thing I don't understand (besides, apparently, the documentation): how did this problem escape detectio

planner support functions: handle GROUP BY estimates ?

Tom implemented "Planner support functions": https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a391ff3c3d418e404a2c6e4ff0865a107752827b https://www.postgresql.org/docs/12/xfunc-optimization.html I wondered whether there was any consideration to extend that to allow providing impro

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 1:14 AM Juan José Santamaría Flecha wrote: > On Tue, Nov 19, 2019 at 12:49 PM Thomas Munro wrote: >> On Wed, Nov 20, 2019 at 12:28 AM Amit Khandekar >> wrote: >> > On Windows, it is documented that ReadFile() (which is called by >> > pg_pread) will return false on EOF bu

Re: Role membership and DROP

Laurenz Albe writes: > On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote: >> Laurenz Albe writes: >>> On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote: It might be worth clarifying this point in section 5.7, https://www.postgresql.org/docs/devel/ddl-priv.html > I like your second sent

Re: initdb SegFault

Andres Freund writes: > Agreed wrt this specific failure scenario. It does however seem not > great that callsites for PQExpBuffer ought to check every call for > allocation failures, in the general case. It is possible to check just once at the end, using the PQExpBufferBroken API, and I believe

Re: initdb SegFault

Hi, On 2019-11-19 10:16:02 -0500, Tom Lane wrote: > vignesh C writes: > > createPQExpBuffer allocates memory and returns the pointer, there is a > > possibility that createPQExpBuffer can return NULL pointer in case of > > malloc failiure, but initdb's main function does not check this > > condit

Re: initdb SegFault

vignesh C writes: > createPQExpBuffer allocates memory and returns the pointer, there is a > possibility that createPQExpBuffer can return NULL pointer in case of > malloc failiure, but initdb's main function does not check this > condition. During malloc failure when pointer is accessed it result

Re: PITR on DROP DATABASE, deleting of the database directory despite the recovery_target_time set before.

On 11/19/19 1:40 AM, Craig Ringer wrote: > On Mon, 18 Nov 2019 at 18:48, Nicolas Lutic > wrote: > > Dear Hackers, > > After a drop database > > > with FORCE? No, we tested with PostgreSQL v 11 and we don't have this option. >   > > , he tried to reco

initdb SegFault

Hi, While checking initdb code, I found one segmentation fault, stack trace for the same is: Core was generated by `./initdb -D data6'. Program terminated with signal 11, Segmentation fault. #0 0x0040ea22 in main (argc=3, argv=0x7ffc82237308) at initdb.c:3340 3340printf(_("\nSucce

Re: Planning counters in pg_stat_statements (using pgss_store)

On Fri, Nov 15, 2019 at 2:00 AM imai.yoshik...@fujitsu.com wrote: > > Actually I also don't have strong opinion but I thought someone would > complain about renaming of those columns and also some tools like monitoring > which use those columns will not work. If we use {total, min, max, mean, >

Re: physical slot xmin dependency on logical slot?

> > I expect that you created the replica in a manner that preserved the > logical replication slot on it. You also had hot_standby_feedback enabled. > As per both you and Andres' replies, we wanted the backup to have the logical slots on it, because we wanted to allow decoding from the slots on o

Re: backup manifests

On 11/19/19 5:00 AM, Rushabh Lathia wrote: > > > My colleague Suraj did testing and noticed the performance impact > with the checksums.   On further testing, he found that specifically with > sha its more of performance impact.   > > I admit I haven't been following along closely, but why do we

Re: Hypothetical indexes using BRIN broken since pg10

On Tue, Nov 19, 2019 at 08:37:04AM +0100, Julien Rouhaud wrote: > None from me. I'm obviously biased, but I hope that it can get > backpatched. BRIN is probably seldom used, but we shouldn't make it > harder to use it, even if it's that's only for hypothetical usage, and > even if it'll still be

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Tue, Nov 19, 2019 at 12:49 PM Thomas Munro wrote: > On Wed, Nov 20, 2019 at 12:28 AM Amit Khandekar > wrote: > > On Tue, 19 Nov 2019 at 14:07, Amit Kapila > wrote: > > > No, I got this before applying the patch. However, after applying the > > > patch, I got below error in the same test: >

checkpointer: PANIC: could not fsync file: No such file or directory

I (finally) noticed this morning on a server running PG12.1: < 2019-11-15 22:16:07.098 EST >PANIC: could not fsync file "base/16491/1731839470.2": No such file or directory < 2019-11-15 22:16:08.751 EST >LOG: checkpointer process (PID 27388) was terminated by signal 6: Aborted /dev/vdb on /

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Sat, Nov 16, 2019 at 6:44 PM Amit Kapila wrote: > > On Thu, Nov 7, 2019 at 5:13 PM Amit Kapila wrote: > > > > Some notes before commit: > > -- > > 1. > > Commit message need to be changed for the first patch > > --

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Mon, Nov 18, 2019 at 5:02 PM Dilip Kumar wrote: > > On Fri, Nov 15, 2019 at 4:19 PM Amit Kapila wrote: > > > > On Fri, Nov 15, 2019 at 4:01 PM Dilip Kumar wrote: > > > > > > On Fri, Nov 15, 2019 at 3:50 PM Amit Kapila > > > wrote: > > > > > > > > > > > > Few other comments on this patch: >

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Wed, Nov 20, 2019 at 12:28 AM Amit Khandekar wrote: > On Tue, 19 Nov 2019 at 14:07, Amit Kapila wrote: > > No, I got this before applying the patch. However, after applying the > > patch, I got below error in the same test: > > > > postgres=# SELECT 1 from > > pg_logical_slot_get_changes('reg

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Tue, 19 Nov 2019 at 14:07, Amit Kapila wrote: > > On Mon, Nov 18, 2019 at 5:50 PM Amit Khandekar wrote: > > > > On Mon, 18 Nov 2019 at 17:20, Amit Kapila wrote: > > > I see that you have made changes in ReorderBufferRestoreChanges to use > > > PathNameOpenFile, but not in ReorderBufferSeriali

Re: Protect syscache from bloating with negative cache entries

I'd like to throw in food for discussion on how much SearchSysCacheN suffers degradation from some choices on how we can insert a code into the SearchSysCacheN code path. I ran the run2.sh script attached, which runs catcachebench2(), which asks SearchSysCache3() for cached entries (almost) 24

Re: fix for BUG #3720: wrong results at using ltree

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, failed This is my first PostgreSQL commitfest and review, guidance welco

Re: backup manifests

My colleague Suraj did testing and noticed the performance impact with the checksums. On further testing, he found that specifically with sha its more of performance impact. Please find below statistics: no of tables without checksum SHA256 checksum % performnce overhead with SHA-256 md5 checks

Re: Planner chose a much slower plan in hashjoin, using a large table as the inner table.

I think we have the same understanding of this issue. Sometimes use smaller costs on scanning the chain in bucket like below would be better. run_cost += outer_path_rows * some_small_probe_cost; run_cost += hash_qual_cost.per_tuple * approximate_tuple_count(); In some version of GreenPlum(a databa

Re: range_agg

Hi čt 7. 11. 2019 v 3:36 odesílatel Paul A Jungwirth < p...@illuminatedcomputing.com> napsal: > On Wed, Nov 6, 2019 at 3:02 PM Paul A Jungwirth > wrote: > > On Thu, Sep 26, 2019 at 2:13 PM Alvaro Herrera > wrote: > > > Hello Paul, I've started to review this patch. Here's a few minor > > > thi

Re: logical decoding : exceeded maxAllocatedDescs for .spill files

On Mon, Nov 18, 2019 at 5:50 PM Amit Khandekar wrote: > > On Mon, 18 Nov 2019 at 17:20, Amit Kapila wrote: > > I see that you have made changes in ReorderBufferRestoreChanges to use > > PathNameOpenFile, but not in ReorderBufferSerializeTXN. Is there a > > reason for the same? In my test enviro