Re: Implementing Incremental View Maintenance

On Thu, 16 Jan 2020 12:59:11 +0900 nuko yokohama wrote: > Aggregate operation of user-defined type cannot be specified > (commit e150d964df7e3aeb768e4bae35d15764f8abd284) > > A SELECT statement using the MIN() and MAX() functions can be executed on a > user-defined type column that implements th

Re: FETCH FIRST clause PERCENT option

On 17/01/2020 07:20, Kyotaro Horiguchi wrote: > Thank you for the notification, Tomas. > > At Thu, 16 Jan 2020 22:33:58 +0100, Tomas Vondra > wrote in >> This patch is marked as RFC since September. Since then there was no >> discussion on this thread, but Andrew proposed an alternative approach

Re: Implementing Incremental View Maintenance

On Thu, 16 Jan 2020 18:50:40 +0900 nuko yokohama wrote: > Error occurs when updating user-defined type columns. > > Create an INCREMENTAL MATERIALIZED VIEW by specifying a query that includes > user-defined type columns. > After the view is created, an error occurs when inserting into the view >

RE: Complete data erasure

Hello, Horiguchi-san Thank you for comment. At Wed, 15 Jan 2020 03:46 +, "Kyotaro Horiguchi " wrote in > shred(1) or wipe(1) doesn't seem to contribute to the objective on > journaled or copy-on-write file systems. I'm not sure, but maybe the > same can be true for read-modify-write devices

Re: Incremental View Maintenance: ERROR: out of shared memory

On Sun, 29 Dec 2019 12:27:13 -0500 Tom Lane wrote: > Tatsuo Ishii writes: > >> here is an unexpected error found while testing IVM v11 patches > >> ... > >> ERROR: out of shared memory > > > I think we could avoid such an error in IVM by reusing a temp table in > > a session or a transaction.

Re: [HACKERS] Block level parallel vacuum

On Fri, Jan 17, 2020 at 12:51 PM Dilip Kumar wrote: > > On Fri, Jan 17, 2020 at 11:39 AM Dilip Kumar wrote: > I have performed cost delay testing on the latest test(I have used > same script as attahced in [1] and [2]. > vacuum_cost_delay = 10 > vacuum_cost_limit = 2000 > > Observation: As we hav

Re: Does 'instead of delete' trigger support modification of OLD

Hello Bruce, > Triggers are designed to check and modify input data, and since DELETE > has no input data, it makes no sense. Sorry, I am still ambiguous. You say that DELETE has no input data, but doc says that it has: https://www.postgresql.org/docs/current/trigger-definition.html For a ro

Re: PATCH: standby crashed when replay block which truncated in standby but failed to truncate in master node

On Fri, Jan 17, 2020 at 1:47 PM Michael Paquier wrote: > > On Thu, Jan 16, 2020 at 11:17:36PM +0900, Fujii Masao wrote: > > OK, I updated the patch that way. > > Attached is the updated version of the patch. > > Thanks. I have few tweaks to propose to the docs. > > +raise a PANIC-level er

Re: [HACKERS] Block level parallel vacuum

On Fri, 17 Jan 2020 at 14:47, Amit Kapila wrote: > > On Fri, Jan 17, 2020 at 12:51 PM Dilip Kumar wrote: > > > > On Fri, Jan 17, 2020 at 11:39 AM Dilip Kumar wrote: > > I have performed cost delay testing on the latest test(I have used > > same script as attahced in [1] and [2]. > > vacuum_cost_

Re: Unnecessary delay in streaming replication due to replay lag

On Fri, Jan 17, 2020 at 11:08 AM Michael Paquier wrote: > > On Fri, Jan 17, 2020 at 09:34:05AM +0530, Asim R P wrote: > > > > 0001 - TAP test to demonstrate the problem. > > There is no real need for debug_replay_delay because we have already > recovery_min_apply_delay, no? That would count o

Re: TRUNCATE on foreign tables

Hi, The v3 patch updated the points below: - 2nd arg of ExecForeignTruncate was changed to DropBehavior, not bool - ExecuteTruncateGuts() uses a local hash table to track a pair of server-id and list of the foreign tables managed by the server. - Error message on truncate_check_rel() was revised

Re: SlabCheck leaks memory into TopMemoryContext

On Thu, Jan 16, 2020 at 06:04:32PM +0100, Tomas Vondra wrote: On Thu, Jan 16, 2020 at 11:43:34AM -0500, Tom Lane wrote: Tomas Vondra writes: The attached fix should do the trick - it pre-allocates the space when creating the context. There is a bit of complexity because we want to allocate the

Re: Errors when update a view with conditional-INSTEAD rules

On Fri, 17 Jan 2020 at 06:14, Pengzhou Tang wrote: > > I am wondering whether a simple auto-updatable view can have a conditional > update instead rule. Well, the decision reached in [1] was that we wouldn't allow that. We could decide to allow it now as a new feature enhancement, but it wouldn'

Re: Complete data erasure

Greetings, * asaba.takan...@fujitsu.com (asaba.takan...@fujitsu.com) wrote: > This feature erases data area just before it is returned to the OS (“erase” > means that overwrite data area to hide its contents here) > because there is a risk that the data will be restored by attackers if it is >

Re: SlabCheck leaks memory into TopMemoryContext

On Thu, Jan 16, 2020 at 01:41:39PM -0500, Tom Lane wrote: Andres Freund writes: On 2020-01-16 18:01:53 +0100, Tomas Vondra wrote: and it's only really used in debug builds anyway. So I'm not all that woried about this wasting a couple extra kB of memory. IDK, making memory usage look differ

Re: Expose lock group leader pid in pg_stat_activity

On Thu, Jan 16, 2020 at 8:28 AM Michael Paquier wrote: > > On Fri, Dec 27, 2019 at 10:15:33AM +0100, Julien Rouhaud wrote: > > I think that not using "parallel" to name this field will help to > > avoid confusion if the lock group infrastructure is eventually used > > for something else, but that'

Re: Expose lock group leader pid in pg_stat_activity

On Thu, Jan 16, 2020 at 8:49 AM Michael Paquier wrote: > > On Thu, Jan 16, 2020 at 04:27:27PM +0900, Michael Paquier wrote: > > While looking at the code, I think that we could refactor things a bit > > for raw_wait_event, wait_event_type and wait_event which has some > > duplicated code for backe

Re: [HACKERS] kqueue

Thanks Thomas, Just a quick update. I just deployed this patch into a lower environment yesterday running FreeBSD 12.1 and PostgreSQL 11.6. I see a significant reduction is CPU/system load from load highs of 500+ down to the low 20’s. System CPU time has been reduced to practically nothing.

Re: Binary support for pgoutput plugin

On Mon, 2 Dec 2019 at 14:35, Dave Cramer wrote: > Rebased against head > > Dave Cramer > > > On Sat, 30 Nov 2019 at 20:48, Michael Paquier wrote: > >> Hi, >> >> On Mon, Nov 11, 2019 at 03:24:59PM -0500, Dave Cramer wrote: >> > Attached, >> >> The latest patch set does not apply correctly. Could

Re: making the backend's json parser work in frontend code

Hi Robert, On 1/16/20 11:51 AM, Robert Haas wrote: On Thu, Jan 16, 2020 at 1:37 PM David Steele wrote: The next question in my mind is given the caveat that the error handing is questionable in the front end, can we at least render/parse valid JSON with the code? That's a real good question

Re: making the backend's json parser work in frontend code

Hi Robert, On 1/16/20 11:51 AM, Robert Haas wrote: On Thu, Jan 16, 2020 at 1:37 PM David Steele wrote: So the idea here is that json.c will have the JSON SQL functions, jsonb.c the JSONB SQL functions, and jsonapi.c the parser, and jsonfuncs.c the utility functions? Uh, I think roughly that

Re: Remove page-read callback from XLogReaderState.

On 29/11/2019 10:14, Kyotaro Horiguchi wrote: At Thu, 28 Nov 2019 21:37:03 +0900 (JST), Kyotaro Horiguchi wrote in 0dc8ead463 hit this. Rebased. Please review the pg_waldump.c hunks in 0001; they revert recent changes. Ughhh! I'l check it. Thank you for noticing!! Fixed that, re-rebased

Re: Patch to document base64 encoding

On Thu, 16 Jan 2020 14:41:33 +0100 (CET) Fabien COELHO wrote: > The "Binary String Functions and Operators" 9.5 section has only one > subsection, "9.5.1", which is about at two thirds of the page. This > structure looks weird. ISTM that a subsection is missing for the > beginning of the page,

Re: Patch to document base64 encoding

On Thu, 16 Jan 2020 14:41:33 +0100 (CET) Fabien COELHO wrote: > Some comments about v13: > > The note about get_byte reads: > >get_byte and set_byte number the first byte of a binary string as > byte 0. get_bit and set_bit number bits from the right within each > byte; for example bit 0 is t

Re: BUG #16213: segfault when running a query

I wrote: > PG Bug reporting form writes: >> The above query produces an error in the server log: >> LOG: server process (PID 108) was terminated by signal 11: Segmentation >> fault > The direct cause of the crash is that by the time we get to ExecutorEnd, > there are dangling pointers in the es_

Re: Patch to document base64 encoding

On Thu, 16 Jan 2020 15:44:44 -0300 Alvaro Herrera wrote: > Because of how the new tables look in the PDF docs, I thought it might > be a good time to research how to make each function-entry occupy two > rows: one for prototype, return type and description, and the other > for the example and its

Re: psql - add SHOW_ALL_RESULTS option

Alvaro Herrera wrote: > if this patch enables other psql features, it might be a good step > forward. Yes. For instance if the stored procedures support gets improved to produce several result sets, how is psql going to benefit from it while sticking to the old way (PGresult *r = PQexec(q

Re: psql - add SHOW_ALL_RESULTS option

"Daniel Verite" writes: > Yes. For instance if the stored procedures support gets improved to > produce several result sets, how is psql going to benefit from it > while sticking to the old way (PGresult *r = PQexec(query)) > of executing queries that discards N-1 out of N result sets? I'm not re

Re: Remove page-read callback from XLogReaderState.

On 2020-Jan-17, Heikki Linnakangas wrote: > I changed that by adding new function, XLogBeginRead(), that repositions the > reader, and removed the 'lsn' argument from XLogReadRecord() altogether. All > callers except one in findLastCheckPoint() pg_rewind.c positioned the reader > once, and then ju

Re: Let people set host(no)ssl settings from initdb

On Wed, Jan 08, 2020 at 02:53:47AM +, tsunakawa.ta...@fujitsu.com wrote: > From: David Fetter > > > But I see two problems with the proposed approach: (1) initdb > > > doesn't support setting up SSL, so the only thing you can achieve > > > here is to reject all TCP/IP connections, until you ha

Re: Let people set host(no)ssl settings from initdb

On Fri, Jan 17, 2020 at 08:47:49PM +0100, David Fetter wrote: > On Wed, Jan 08, 2020 at 02:53:47AM +, tsunakawa.ta...@fujitsu.com wrote: > > From: David Fetter > > > > But I see two problems with the proposed approach: (1) initdb > > > > doesn't support setting up SSL, so the only thing you ca

Re: Parallel leader process info in EXPLAIN

Both patches aren't applying cleanly anymore. The first patch in the set applied cleanly for me before b925a00f4ef65 It mostly seems like the default settings for the patch program were my problem, but, since I noticed that the patch tester bot was failing to apply it also, I thought I would sugge

Re: psql - add SHOW_ALL_RESULTS option

Tom Lane wrote: > I'm not really holding my breath for that to happen, considering > it would involve fundamental breakage of the wire protocol. > (For example, extended query protocol assumes that Describe > Portal only needs to describe one result set. There might be > more issues, but

Re: Implementing Incremental View Maintenance

Hello, It seems that patch v11 doesn't apply any more. Problem with "scanRTEForColumn" maybe because of change: https://git.postgresql.org/pg/commitdiff/b541e9accb28c90656388a3f827ca3a68dd2a308 Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: proposal: schema variables

er". >> >> Best regards. Philippe. >> >> The new status of this patch is: Waiting on Author >> > > Thank you very much for your comments, and notes. Updated patch attached. > rebase > Regards > > Pavel > > schema-variables-20200117.patch.gz Description: application/gzip

Re: Reorderbuffer crash during recovery

On 2020-Jan-17, vignesh C wrote: > Thanks Dilip for reviewing. > I have fixed the comments you have suggested. I ended up rewording that comment completely; I thought the original was not explaining things well enough. I also changed the comment for final_lsn in reorderbuffer.h: not only I remov

Re: Patch to document base64 encoding

Tom, you're marked as committer for this one in the commitfest app; are you still intending to get it committed? If not, I can. Thanks, -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: making the backend's json parser work in frontend code

On Thu, Jan 16, 2020 at 8:55 PM Andrew Dunstan wrote: > I'm probably responsible for a good deal of the mess, so let me say Thankyou. > > I'll have a good look at these. Thanks, appreciated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Avoid full GIN index scan when possible

On Wed, Jan 15, 2020 at 2:03 AM Tom Lane wrote: > Alexander Korotkov writes: > > On Tue, Jan 14, 2020 at 9:43 PM Tom Lane wrote: > >> One thing I'm still not happy about is the comment in > >> collectMatchesForHeapRow. v12 failed to touch that at all, so I tried to > >> fill it in, but I'm not

Re: Avoid full GIN index scan when possible

On Sat, Jan 18, 2020 at 12:33 AM Alexander Korotkov wrote: > So, I think we don't need so huge regression test to exercise this corner > case. Forgot to mention. I'm going to push v15 if no objections. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Post

Re: Crash in BRIN summarization

On 2019-Aug-28, Heikki Linnakangas wrote: > I bumped into a little bug in BRIN, while hacking on something unrelated. > This causes a segfault, or an assertion failure if assertions are enabled: Heikki, I just noticed that you haven't pushed this bugfix. Would you like me to? (If I don't hear f

Re: Avoid full GIN index scan when possible

Alexander Korotkov writes: > On Wed, Jan 15, 2020 at 2:03 AM Tom Lane wrote: >> Hmm ... yeah, these test cases are not large enough to exercise any >> lossy-page cases, are they? I doubt we should try to make a new regression >> test that is that big. (But if there is one already, maybe we coul

pg13 PGDLLIMPORT list

Hello, would it be possible to add PGDLLIMPORT to permit to build following extensions on windows pg_stat_sql_plans: src/include/pgstat.h extern PGDLLIMPORT bool pgstat_track_activities; pg_background: src/include/storage/proc.h extern PGDLLIMPORT int StatementTimeout; Thanks in advance Regards

Re: Avoid full GIN index scan when possible

On Sat, Jan 18, 2020 at 12:48 AM Tom Lane wrote: > Alexander Korotkov writes: > > On Wed, Jan 15, 2020 at 2:03 AM Tom Lane wrote: > >> Hmm ... yeah, these test cases are not large enough to exercise any > >> lossy-page cases, are they? I doubt we should try to make a new regression > >> test th

Re: Patch to document base64 encoding

Alvaro Herrera writes: > Tom, you're marked as committer for this one in the commitfest app; are > you still intending to get it committed? If not, I can. I've not been paying much attention to this thread, but I'll take another look. regards, tom lane

Re: [WIP] UNNEST(REFCURSOR): allowing SELECT to consume data from a REFCURSOR

> On 14 Jan 2020, at 14:53, Daniel Verite wrote: > > What is the expected result anyway? A single column with a "record" > type? FWIW I notice that with plpgsql, this is not allowed to happen: Hmm. How interesting. I had not really investigated what happens in the case of a function returning

Re: Decade indication

On Thu, Jan 2, 2020 at 08:52:17AM -0500, Tom Lane wrote: > Robert Haas writes: > > On Wed, Jan 1, 2020 at 11:01 PM Tom Lane wrote: > >> I see Randall Munroe has weighed in on this topic: > >> https://xkcd.com/2249/ > > > And the conclusion is ... the whole discussion is stupid? > > Well, it's

Re: jsonb_set() strictness considered harmful to data

On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote: On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule wrote: Hi po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan napsal: Updated version including docco and better error message. cheers andrew I think so my objections are solved.

Re: jsonb_set() strictness considered harmful to data

Hello, January 17, 2020 5:21 PM, "Tomas Vondra" wrote: > On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote: > >> On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule wrote: >>> Hi >>> >>> po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan >>> napsal: >> >> Updated version including docco

Re: jsonb_set() strictness considered harmful to data

> On Jan 17, 2020, at 4:28 PM, Ariadne Conill wrote: > > Hello, > > January 17, 2020 5:21 PM, "Tomas Vondra" > wrote: > > Thank you very much for coming together and finding a solution to this bug! > > Ariadne Let’s leave it at “issue” :)

longs where uint64s could be

Folks, While going over places where I might use compiler intrinsics for things like ceil(log2(n))) and next power of 2(n), I noticed that a lot of things that can't be fractional are longs instead of, say, uint64s. Is this the case for historical reasons, or is there some more specific utility to

Re: longs where uint64s could be

On Fri, Jan 17, 2020 at 4:42 PM David Fetter wrote: > While going over places where I might use compiler intrinsics for > things like ceil(log2(n))) and next power of 2(n), I noticed that a > lot of things that can't be fractional are longs instead of, say, > uint64s. Is this the case for historic

Re: Amcheck: do rightlink verification with lock coupling

On Thu, Jan 16, 2020 at 5:11 PM Peter Geoghegan wrote: > I find this argument convincing. I'll try to get this committed soon. > > While you could have used bt_index_parent_check() or heapallindexed to > detect the issue, those two options are a lot more expensive (plus the > former option won't w

Re: pg13 PGDLLIMPORT list

On Fri, Jan 17, 2020 at 03:07:48PM -0700, legrand legrand wrote: > Would it be possible to add PGDLLIMPORT to permit to build following > extensions on windows > > pg_stat_sql_plans: > src/include/pgstat.h > extern PGDLLIMPORT bool pgstat_track_activities; > > pg_background: > src/include/storage

Re: Expose lock group leader pid in pg_stat_activity

On Fri, Jan 17, 2020 at 05:07:55PM +0100, Julien Rouhaud wrote: > Oh indeed. But unless we hold some LWLock during the whole function > execution, we cannot guarantee a consistent view right? Yep. That's possible. > And isn't it already possible to e.g. see a parallel worker in > pg_stat_activi

Re: Improve errors when setting incorrect bounds for SSL protocols

On Thu, Jan 16, 2020 at 10:00:52AM +0900, Michael Paquier wrote: > Thanks for the review. Let's wait a couple of days to see if others > have objections or more comments about this patch, but I'd like to > fix the issue and backpatch down to 12 where the parameters have been > introduced. And com

Re: Use compiler intrinsics for bit ops in hash

On Wed, Jan 15, 2020 at 6:09 AM David Fetter wrote: > [v2 patch] Hi David, I have a stylistic comment on this snippet: - for (i = _hash_log2(metap->hashm_bsize); i > 0; --i) - { - if ((1 << i) <= metap->hashm_bsize) - break; - } + i = pg_leftmost_one_pos32(metap->hashm_bsize); Assert(i > 0);

Re: PATCH: standby crashed when replay block which truncated in standby but failed to truncate in master node

On Fri, Jan 17, 2020 at 07:36:51PM +0900, Fujii Masao wrote: > On Fri, Jan 17, 2020 at 1:47 PM Michael Paquier wrote: >> Thanks. I have few tweaks to propose to the docs. >> >> +raise a PANIC-level error, aborting the recovery. Setting >> Instead of "PANIC-level error", I would just use "

Re: reduce size of fmgr_builtins array

On Tue, Jan 7, 2020 at 9:08 PM Heikki Linnakangas wrote: > Yeah. Nevertheless, it would be nice to be able to demonstrate the > benefit in some test, at least. It feels hard to justify committing a > performance patch if we can't show the benefit. Otherwise, we should > just try to keep it as simp

Re: Reorderbuffer crash during recovery

On Sat, Jan 18, 2020 at 2:42 AM Alvaro Herrera wrote: > > On 2020-Jan-17, vignesh C wrote: > > > Thanks Dilip for reviewing. > > I have fixed the comments you have suggested. > > I ended up rewording that comment completely; I thought the original was > not explaining things well enough. > > I als

Re: Implementing Incremental View Maintenance

Hi. I understand. Even if the function name is min, there is a possibility that it is not an aggregation operation for finding the minimum value, so it is restricted. I understood aggregation of user-defined types is a constraint. Also, I agree with the error message improvements. 2020年1月17日(金)

Re: pg13 PGDLLIMPORT list

On Sat, Jan 18, 2020 at 7:56 AM Michael Paquier wrote: > > On Fri, Jan 17, 2020 at 03:07:48PM -0700, legrand legrand wrote: > > Would it be possible to add PGDLLIMPORT to permit to build following > > extensions on windows > > > > pg_stat_sql_plans: > > src/include/pgstat.h > > extern PGDLLIMPORT

Re: pg13 PGDLLIMPORT list

On Sat, 18 Jan 2020, 09:04 Amit Kapila On Sat, Jan 18, 2020 at 7:56 AM Michael Paquier > wrote: > > > > On Fri, Jan 17, 2020 at 03:07:48PM -0700, legrand legrand wrote: > > > Would it be possible to add PGDLLIMPORT to permit to build following > > > extensions on windows > > > > > > pg_stat_sql_p