Re: Pluggable Storage - Andres's take

On Tue, Oct 16, 2018 at 12:37 AM Alexander Korotkov wrote: > > I've examined code in pluggable-zheap branch and EDB github [1] and I > didn't found anything related to "delete-marking" indexes as stated on > slide #25 of presentation [2]. So, basically contract between heap > and indexes is remai

Re: Large writable variables

Hi, On 2018-10-16 01:59:00 -0400, Tom Lane wrote: > I wrote: > > Andres Freund writes: > >> top unitialized allocations: > >> 08435040 00085280 b DCHCache > >> 08391168 00043840 b NUMCache > > > Here's a patch to improve that situation. > > Hm, looking at that mo

Re: Large writable variables

On 2018-10-15 22:02:00 -0700, Andres Freund wrote: > Hi, > > On 2018-10-15 21:50:51 -0700, Andres Freund wrote: > > .data 0028 spi_printtupDR > > .data 0028 printsimpleDR > > .data 0028 donothingDR > > .data 0028 debugtupDR > > > > These we could ac

Re: Large writable variables

Hi, On 2018-10-16 01:49:22 -0400, Tom Lane wrote: > Andres Freund writes: > > [ let's rearrange the GUC structs ] > > I find it hard to believe that the API breaks you'll cause for extensions > are a good trade for a few kB reduction in the size of the .data segment. I'm doubtful that it's wort

Re: Large writable variables

I wrote: > Andres Freund writes: >> top unitialized allocations: >> 08435040 00085280 b DCHCache >> 08391168 00043840 b NUMCache > Here's a patch to improve that situation. Hm, looking at that more closely, there's a problem with the idea of allocating the cache s

Re: Large writable variables

Andres Freund writes: > [ let's rearrange the GUC structs ] I find it hard to believe that the API breaks you'll cause for extensions are a good trade for a few kB reduction in the size of the .data segment. regards, tom lane

Re: Large writable variables

Hi, On 2018-10-15 21:50:51 -0700, Andres Freund wrote: > .data 0028 spi_printtupDR > .data 0028 printsimpleDR > .data 0028 donothingDR > .data 0028 debugtupDR > > These we could actually make constant, but CreateDestReceiver() as an > API makes that

Re: Large writable variables

Hi, After the last few changes (including a proposed one), we now have in the .data segment (i.e. mutable initialized variables): $ objdump -j .data -t ~/build/postgres/dev-optimize/vpath/src/backend/postgres|awk '{print $4, $5, $6}'|sort -r|lessGreetings, .data 4380 ConfigureNamesI

Re: Multi-insert into a partitioned table with before insert row trigger causes server crash on latest HEAD

> > Yea, that anlysis sounds right. Peter? > Thanks Andres for the confirmation on my initial analysis. I'm not sure why Peter hasn't commented on it so far. However, I continued investigating on it further and here are findings I think, the root cause of this problem is that CopyFrom() is using t

Re: Large writable variables

Hi, On 2018-10-15 16:45:03 -0700, Andres Freund wrote: > I've a patch making .data variables constant, fwiw. Will post in a > bit. Just so we don't duplicate work too much. I pushed a few very obvious ones. An additional fix, for the system attributes in heap.c, is attached. But that's a bit mor

Re: WIP: Avoid creation of the free space map for small tables

On Mon, Oct 15, 2018 at 4:09 PM John Naylor wrote: > On 10/15/18, Amit Kapila wrote: > > Few comments on your latest patch: > > - > > targetBlock = GetPageWithFreeSpace(relation, len + saveFreeSpace); > > - > > - /* > > - * If the FSM knows nothing of the rel, try the last page before we > > - *

Re: Large writable variables

Andres Freund writes: > top unitialized allocations: > 08435040 00085280 b DCHCache > 08391168 00043840 b NUMCache Here's a patch to improve that situation. regards, tom lane diff --git a/src/backend/utils/adt/formatting.c b/src/backend/ut

Re: Pluggable Storage - Andres's take

On Tue, Oct 9, 2018 at 1:46 PM Haribabu Kommi wrote: > On Wed, Oct 3, 2018 at 3:16 PM Andres Freund wrote: > >> On 2018-09-27 20:03:58 -0700, Andres Freund wrote: >> > On 2018-09-28 12:21:08 +1000, Haribabu Kommi wrote: >> > > Here I attached further cleanup patches. >> > > 1. Re-arrange the GUC

Re: Creating Certificates

On Tue, Oct 16, 2018 at 11:45:53AM +0900, Tatsuo Ishii wrote: > > I'm not opposed to simplifying the instructions, however. > > Ok, attached is a proposal to simplify the instructions. I am against this simplification for the reasons I stated in this thread. -

Re: Creating Certificates

> I'm not opposed to simplifying the instructions, however. Ok, attached is a proposal to simplify the instructions. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sg

Re: speeding up planning with partitions

Hi David, I've managed to get back to the rest of your comments. Sorry that it took me a while. On 2018/09/11 8:23, David Rowley wrote: > On 30 August 2018 at 21:29, Amit Langote > wrote: >> Attached updated patches, with 0002 containing the changes mentioned above. > > Here's my first pass r

Re: Large writable variables

Hi, I've a patch making .data variables constant, fwiw. Will post in a bit. Just so we don't duplicate work too much. On 2018-10-15 19:41:49 -0400, Tom Lane wrote: > I wrote: > > Andres Freund writes: > >> 08560224 00023440 b tzdefrules_s > >> 08536704 00023440 b

Re: Large writable variables

I wrote: > Andres Freund writes: >> 08560224 00023440 b tzdefrules_s >> 08536704 00023440 b gmtmem.7009 > I think that tzdefrules_s is not used in common cases (though I could be > wrong about that), so we could win by alloc-on-first-use. The same might > be true

Re: Large writable variables

On Tue, Oct 16, 2018 at 10:57 AM Andres Freund wrote: > > Subject: [PATCH] Move the replication lag tracker into heap memory. > > > > Andres Freund complained about the 128KB of .bss occupied by LagTracker. > > It's only needed in the walsender process, so allocate it in heap > > memory there. > >

Re: Large writable variables

On 2018-10-16 10:16:34 +1300, Thomas Munro wrote: > On Tue, Oct 16, 2018 at 9:07 AM Andres Freund wrote: > > $ nm -t d --size-sort -r -S src/backend/postgres|grep '\b[bB]\b'|head > > 08251872 00131144 b LagTracker > ... > > So we have a two variables sized 130kb. Yikes. > ... > > t

Re: PostgreSQL 11 RC1 + GA Dates

On 10/15/18 1:46 AM, Sandeep Thakkar wrote: > Hi, > > On Tue, Oct 2, 2018 at 7:28 PM Jonathan S. Katz > wrote: > > Hi, > > Based on the current status of the open items and where we are at in the > release cycle, the date for the first release candidate

Re: Large writable variables

Andres Freund writes: > On 2018-10-15 16:54:53 -0400, Tom Lane wrote: >> Yeah. It also seems like doing it this way would improve locality of >> access: the pieces of the giant string would presumably be in the same >> order as the ScanKeywords entries, whereas with the current setup, >> who know

Re: Large writable variables

On Tue, Oct 16, 2018 at 9:07 AM Andres Freund wrote: > $ nm -t d --size-sort -r -S src/backend/postgres|grep '\b[bB]\b'|head > 08251872 00131144 b LagTracker ... > So we have a two variables sized 130kb. Yikes. ... > that's not actually used very often, nor in all processes... Thom

Re: Large writable variables

Hi, On 2018-10-15 16:54:53 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2018-10-15 16:36:26 -0400, Tom Lane wrote: > >> Andres Freund writes: > >>> 08585088 00131104 b hist_entries > >>> 08716192 00016384 b hist_start > > >> I'm unsure what fraction of p

Re: Large writable variables

Andres Freund writes: > On 2018-10-15 16:36:26 -0400, Tom Lane wrote: >> Andres Freund writes: >>> 08585088 00131104 b hist_entries >>> 08716192 00016384 b hist_start >> I'm unsure what fraction of processes would have use for these. > Yea, I'm not sure either. A

Re: Large writable variables

Hi, On 2018-10-15 16:36:26 -0400, Tom Lane wrote: > Andres Freund writes: > > So we have 500kb of not-initialized memory mapped into every > > process. That's, uh, not nothing. > > Bleah. Yea... > > 08585088 00131104 b hist_entries > > 08716192 00016384 b hist_s

Re: Large writable variables

Andres Freund writes: > So we have 500kb of not-initialized memory mapped into every > process. That's, uh, not nothing. Bleah. > 08585088 00131104 b hist_entries > 08716192 00016384 b hist_start I'm unsure what fraction of processes would have use for these. >

Re: Inadequate failure reporting from poll_query_until

On 10/15/2018 04:19 PM, Tom Lane wrote: I just spent some time puzzling over why we've seen so many recoveryCheck failures in the buildfarm recently --- excluding failures with known causes, such as the pg_verify_checksums business, that's basically all we've seen for the last week. I am susp

Inadequate failure reporting from poll_query_until

I just spent some time puzzling over why we've seen so many recoveryCheck failures in the buildfarm recently --- excluding failures with known causes, such as the pg_verify_checksums business, that's basically all we've seen for the last week. I am suspicious that there's some timing-dependent pro

Re: Large writable variables

Hi, On 2018-10-15 13:07:54 -0700, Andres Freund wrote: > top itialized allocations: > $ nm -t d --size-sort -r -S src/backend/postgres|grep '\b[dD]\b'|head > 08086944 00087904 D fmgr_builtins > 08201120 00017280 d ConfigureNamesInt > 08218400 0001368

Large writable variables

Hi, while briefly thinking about the per-process overhead of postgres, I looked at the section sizes of a modern postgres. In particular which memory areas are *not* shared between processes. If you look at the section sizes that are mapped read-write: $ size --format=sysv src/backend/postgres s

Re: pg11rc1 DROP INDEX: NOTICE: drop_trigger

On Mon, Oct 15, 2018 at 03:44:52PM -0400, Tom Lane wrote: > Justin Pryzby writes: > > ts=# CREATE INDEX ON t(i) ; > > CREATE INDEX > > ts=# DROP INDEX t_i_idx ; > > NOTICE: 0: drop_trigger > > LOCATION: exec_stmt_raise, pl_exec.c:3748 > > DROP INDEX > > Maybe you've got an event trigger h

Re: Use of pager, help, localization:

Daniele Varrazzo writes: > From psql/help.c > /* > * Keep this line count in sync with the number of lines printed below! > * Use "psql --help=variables | wc" to count correctly; but notice that > * Windows builds currently print one more line than non-Windows builds. > * Using

Re: pg11rc1 DROP INDEX: NOTICE: drop_trigger

Justin Pryzby writes: > ts=# CREATE INDEX ON t(i) ; > CREATE INDEX > ts=# DROP INDEX t_i_idx ; > NOTICE: 0: drop_trigger > LOCATION: exec_stmt_raise, pl_exec.c:3748 > DROP INDEX Maybe you've got an event trigger hanging around in that DB? regards, tom lane

Use of pager, help, localization:

>From psql/help.c /* * Keep this line count in sync with the number of lines printed below! * Use "psql --help=variables | wc" to count correctly; but notice that * Windows builds currently print one more line than non-Windows builds. * Using the larger number is fine. */

pg11rc1 DROP INDEX: NOTICE: drop_trigger

ts=# CREATE INDEX ON t(i) ; CREATE INDEX ts=# DROP INDEX t_i_idx ; NOTICE: 0: drop_trigger LOCATION: exec_stmt_raise, pl_exec.c:3748 DROP INDEX ts=# ts=# \d t Table "public.t" Column | Type | Collation | Nullable | Default +-+---+--+--

Re: Pluggable Storage - Andres's take

Hi! On Wed, Oct 3, 2018 at 8:16 AM Andres Freund wrote: > I've pushed an updated version, with a fair amount of pending changes, > and I hope all your pending (and not redundant, by our concurrent > development), patches merged. I'd like to also share some patches. I've used current state of pl

Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.

Kyotaro HORIGUCHI writes: > At Thu, 01 Mar 2018 15:39:18 -0500, Tom Lane wrote in > <22609.1519936...@sss.pgh.pa.us> >> ... After some poking >> around I found that the bug could be exhibited using just btree_gist's >> gist_inet_ops, since the core inet_ops class indexes the same datatype and >

Re: CopyFrom() has become way too complicated

> > I think the code needs to be split up so that CopyFrom() in the loop > body calls CopyFromOneTuple(), which then also splits out the tuple > routing into its own CopyFromOneTupleRoute() function (that's 200 LOC on > its own...). I suspect it'd also be good to refactor the > partition-change cod

Re: Error handling expectations

Perfect. Thanks for the pointer, Andres, that's exactly what I was looking for. On Mon, Oct 15, 2018 at 1:28 PM Andres Freund wrote: > Hi, > > On 2018-10-15 13:21:04 -0400, James Coleman wrote: > > This my first real foray into the Postgres codebase, so an beginner > > question: I noticed that e

Re: Error handling expectations

Hi, On 2018-10-15 13:21:04 -0400, James Coleman wrote: > This my first real foray into the Postgres codebase, so an beginner > question: I noticed that existing C functions in pageinspect often do error > checking and then report the error with ereport but still continue on to > execute the rest o

Error handling expectations

I'm working on adding a tuple_data_record function to pageinspect to parallel tuple_data_split (returning the record type of the relation being examined rather than an array of attributes as on-disk byte data). This my first real foray into the Postgres codebase, so an beginner question: I noticed

Re: Re[2]: [PATCH] Change simple_heap_insert() to a macro

=?UTF-8?B?QW5kcmV5IEtseWNoa292?= writes: >>  simple_heap_insert() is used in catalog updates and such. Does that have >>  any measurable performance impact? > I guess this change doesn't give us an incredible performance increase except > there will no redundant function call. > I don't see any

Re: constraints names on partitions

Robert Haas writes: > On Fri, Oct 12, 2018 at 12:39 PM Alvaro Herrera > wrote: >> I just realized that the current code to assign constraint names to >> partitions is going against the SQL standard's idea that constraint >> names must be unique within a schema. When a partition is created, the >

Re: constraints names on partitions

On Fri, Oct 12, 2018 at 12:39 PM Alvaro Herrera wrote: > I just realized that the current code to assign constraint names to > partitions is going against the SQL standard's idea that constraint > names must be unique within a schema. When a partition is created, the > foreign key gets exactly th

Re: pgsql: Add TAP tests for pg_verify_checksums

On 10/15/2018 11:05 AM, Tom Lane wrote: Andrew Dunstan writes: We should fix this in PG11 rather than ship a broken utility. If everyone is happy, I can apply this. At this point I'd be happier if you waited till after 11.0 wraps... there is no margin for error today.

Re: pgsql: Add TAP tests for pg_verify_checksums

Andrew Dunstan writes: > We should fix this in PG11 rather than ship a broken utility. If > everyone is happy, I can apply this. At this point I'd be happier if you waited till after 11.0 wraps... there is no margin for error today. regards, tom lane

Re: pgsql: Add TAP tests for pg_verify_checksums

On 10/14/2018 09:11 PM, Andrew Dunstan wrote: What do you think about the updated version attached? Looks good to me. We should fix this in PG11 rather than ship a broken utility. If everyone is happy, I can apply this. cheers andrew -- Andrew Dunstanhttps://w

Re: Function to promote standby servers

Just curious to know, is promotion via function only allowed for hot-standby or works for any standby? On Mon, Oct 15, 2018 at 7:16 AM Laurenz Albe wrote: > Michael Paquier wrote: > > > diff --git a/src/backend/access/transam/xlog.c > b/src/backend/access/transam/xlog.c > > > index 7375a78ffc..3

Re: Requesting advanced Group By support

On 10/10/2018 19:59, Andres Freund wrote: > Isn't the spec compliant thing that's missing dealing with unique not null? I don't think that's what the original poster complained about, but you are right that it's a known missing part. The reason it's missing is that we can't record dependencies on

Re: Function to promote standby servers

Michael Paquier wrote: > > diff --git a/src/backend/access/transam/xlog.c > > b/src/backend/access/transam/xlog.c > > index 7375a78ffc..3a1f49e83a 100644 > > --- a/src/backend/access/transam/xlog.c > > +++ b/src/backend/access/transam/xlog.c > > @@ -81,8 +81,6 @@ extern uint32 bootstrap_data_check

Re: [HACKERS] pgbench - allow to store select results into variables

Here is yet another rebase. Here is yet another rebase after Peter's exit status changes on pgbench. Whether Stephen will have time to commit this patch is unclear. I'd suggest that I remove his name from the committer column so that another committer may consider it. What do you think?

Re: pgbench - add pseudo-random permutation function

Hello Hironobu-san, I reviewed pgbench-prp-func-6.patch. Thanks. (1) modular_multiply() In modular_multiply(), the numbers of digits of inputs are checked in order to determine using the interleaved modular multiplication algorithm or not. However, the check condition absolutely depends o

Re: Undo logs

On Sun, Sep 2, 2018 at 12:19 AM Thomas Munro wrote: > > On Fri, Aug 31, 2018 at 10:24 PM Dilip Kumar > wrote: > > On Fri, Aug 31, 2018 at 3:08 PM, Dilip Kumar wrote: > > > As Thomas has already mentioned upthread that we are working on an > > > undo-log based storage and he has posted the patch

Re: WIP: Avoid creation of the free space map for small tables

On 10/15/18, Amit Kapila wrote: > Few comments on your latest patch: > - > +static bool > +allow_write_to_fsm(Relation rel, BlockNumber heapBlk) > +{ > + BlockNumber heap_nblocks; > + > + if (heapBlk > HEAP_FSM_EXTENSION_THRESHOLD || > + rel->rd_rel->relkind != RELKIND_RELATION) > + return true; >

Re: Speeding up INSERTs and UPDATEs to partitioned tables

Hi, On 2018/10/15 19:04, Krzysztof Nienartowicz wrote: > > We see quite prohibitive 5-6x slowdown with native partitioning on in > comparison to trigger based in PG9.5. > This is clearly visible with highly parallel inserts (Can share > flamediagrams comparing the two). > > This basically exclu

Re: Speeding up INSERTs and UPDATEs to partitioned tables

We see quite prohibitive 5-6x slowdown with native partitioning on in comparison to trigger based in PG9.5. This is clearly visible with highly parallel inserts (Can share flamediagrams comparing the two). This basically excludes native partitioning from being used by us. Do you think your chan

Re: speeding up planning with partitions

Hi Dilip, Thanks for your review comments. Sorry it took me a while replying to them. On 2018/09/29 22:30, Dilip Kumar wrote: > I was going through your patch (v3-0002) and I have some suggestions > > 1. > - if (nparts > 0) > + /* > + * For partitioned tables, we just allocate space for RelOptI

Re: pgbench exit code

On 12/10/2018 21:22, Fabien COELHO wrote: > No further remarks. I turned the patch as ready on the CF app. Committed, thanks. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services