Re: adding partitioned tables to publications

Hello Rafia, Great to hear that you are interested in this feature and thanks for testing the patch. On Thu, Oct 10, 2019 at 10:13 PM Rafia Sabih wrote: > Lately I was exploring logical replication feature of postgresql and I found > this addition in the scope of feature for partitioned tables

Re: dropping column prevented due to inherited index

On Thu, Oct 10, 2019 at 05:28:02PM +0900, Amit Langote wrote: > Actually, the code initializes it on the first call (recursing is > false) and asserts that it must have been already initialized in a > recursive (recursing is true) call. I have actually kept your simplified version. > Okay, sure.

Re: dropping column prevented due to inherited index

On Fri, Oct 11, 2019 at 4:16 PM Michael Paquier wrote: > On Thu, Oct 10, 2019 at 05:28:02PM +0900, Amit Langote wrote: > > Actually, the code initializes it on the first call (recursing is > > false) and asserts that it must have been already initialized in a > > recursive (recursing is true)

Re: Standby accepts recovery_target_timeline setting?

On Thu, Oct 10, 2019 at 5:52 AM Peter Eisentraut wrote: > > On 2019-09-30 03:48, Fujii Masao wrote: > > Also we need to do the same thing for other recovery options like > > restore_command. Attached is the patch which makes crash recovery > > ignore restore_command and recovery_end_command. > >

Re: maintenance_work_mem used by Vacuum

On Fri, Oct 11, 2019 at 7:36 AM Masahiko Sawada wrote: > > On Thu, Oct 10, 2019 at 6:38 PM Amit Kapila wrote: > > > > > > It seems you want to say about commit id > > a1b395b6a26ae80cde17fdfd2def8d351872f399. > > Yeah thanks. > > > I wonder why they have not > > changed it to

Re: BRIN index which is much faster never chosen by planner

On Fri, 11 Oct 2019 at 17:48, Michael Lewis wrote: > > On Thu, Oct 10, 2019 at 6:22 PM David Rowley > wrote: >> The planner will just estimate the selectivity of now() - interval '10 >> days' by using DEFAULT_INEQ_SEL, which is 0., so it >> thinks it'll get 1/3rd of the table.

PostgreSQL, C-Extension, calling other Functions

I´ve written some PostgreSQL C-Extensions (for the first time...) and they work as expected. But now I want to call other functions from inside the C-Extensions (but not via SPI_execute), for example "regexp_match()" or from other extensions like PostGIS "ST_POINT" etc... I think "fmgr" is the

Re: PostgreSQL, C-Extension, calling other Functions

Hi pá 11. 10. 2019 v 10:15 odesílatel Stefan Wolf napsal: > I´ve written some PostgreSQL C-Extensions (for the first time...) and they > work as expected. > > But now I want to call other functions from inside the C-Extensions (but > not > via SPI_execute), > for example "regexp_match()" or

Re: dropping column prevented due to inherited index

On Fri, Oct 11, 2019 at 04:23:51PM +0900, Amit Langote wrote: > Thanks. The index on b is not really necessary for testing because it > remains unaffected, but maybe it's fine. That's on purpose. Any more comments? -- Michael signature.asc Description: PGP signature

Re: BRIN index which is much faster never chosen by planner

On Thu, Oct 10, 2019 at 6:13 PM Tomas Vondra wrote: > > So this seems like a combination of multiple issues. Firstly, the bitmap > index scan on rec_insert_time_brin_1000 estimate seems somewhat poor. It > might be worth increasing stats target on that column, or something like > that. Not sure,

Re: Connect as multiple users using single client certificate

On 10/11/19 1:05 PM, Tom Lane wrote: Kyle Bateman writes: On 10/11/19 12:12 PM, Andrew Dunstan wrote: I think the short answer is: No. The client certificate should match the username and nothing else. If you don't want to generate certificates for all your users I suggest using some other

Re: stress test for parallel workers

Thomas Munro writes: > Yeah, I don't know anything about this stuff, but I was also beginning > to wonder if something is busted in the arch-specific fault.c code > that checks if stack expansion is valid[1], in a way that fails with a > rapidly growing stack, well timed incoming signals, and

Re: pgsql: Remove pqsignal() from libpq's official exports list.

Re: Tom Lane 2019-10-10 <10247.1570731...@sss.pgh.pa.us> > OK, done. Thanks, that made quite a few QA pipeline jobs happy here. Christoph

Re: Connect as multiple users using single client certificate

Kyle Bateman writes: > On 10/11/19 1:05 PM, Tom Lane wrote: >> I agree with Andrew that that's just silly. If you give all your users >> the same cert then any of them can masquerade as any other. You might >> as well just tell them to share the same login id. > In my implementation, I'm not

Re: stress test for parallel workers

On Sat, Oct 12, 2019 at 08:41:12AM +1300, Thomas Munro wrote: > On Sat, Oct 12, 2019 at 7:56 AM Tom Lane wrote: > > This matches up with the intermittent infinite_recurse failures > > we've been seeing in the buildfarm. Those are happening across > > a range of systems, but they're (almost) all

Re: stress test for parallel workers

Hi, On 2019-10-11 14:56:41 -0400, Tom Lane wrote: > I still don't have a good explanation for why this only seems to > happen in the pg_upgrade test sequence. However, I did notice > something very interesting: the postmaster crashes after consuming > only about 1MB of stack space. This is

v12.0 ERROR: trying to store a heap tuple into wrong type of slot

I'm not sure why we have that index, and my script probably should have known to choose a better one to cluster on, but still.. ts=# CLUSTER huawei_m2000_config_enodebcell_enodeb USING huawei_m2000_config_enodebcell_enodeb_coalesce_idx ; DEBUG: 0: building index "pg_toast_1840151315_index"

Re: stress test for parallel workers

On Sat, Oct 12, 2019 at 9:40 AM Tom Lane wrote: > Andres Freund writes: > > On 2019-10-11 14:56:41 -0400, Tom Lane wrote: > >> ... So it's really hard to explain > >> that as anything except a kernel bug: sometimes, the kernel > >> doesn't give us as much stack as it promised it would. And the

Re: stress test for parallel workers

I wrote: > It's not very clear how those things would lead to an intermittent > failure though. In the case of the postmaster crashes, we now see > that timing of signal receipts is relevant. For infinite_recurse, > maybe it only fails if an sinval interrupt happens at the wrong time? > (This

Re: Connect as multiple users using single client certificate

Kyle Bateman writes: > On 10/11/19 12:12 PM, Andrew Dunstan wrote: >> I think the short answer is: No. The client certificate should match the >> username and nothing else. If you don't want to generate certificates >> for all your users I suggest using some other form of auth (e.g. >>

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On 10/11/19 4:44 PM, Dave Cramer wrote: > On Fri, 11 Oct 2019 at 16:41, Chapman Flack >> On 10/11/19 4:30 PM, Robert Haas wrote: >>> allow it to be done via SQL. Otherwise, the user can break the driver >>> by using SQL to set the list to something that the driver's not >>> expecting, and

Re: Connect as multiple users using single client certificate

On 10/11/19 1:58 PM, Kyle Bateman wrote: > I have some JS middleware that needs to securely connect to the > postgresql back end.  Any number of different users may connect via > websocket to this middleware to manage their connection to the > database.  I want the JS process to have a client

Re: Connect as multiple users using single client certificate

On 10/11/19 12:12 PM, Andrew Dunstan wrote: On 10/11/19 1:58 PM, Kyle Bateman wrote: I have some JS middleware that needs to securely connect to the postgresql back end.  Any number of different users may connect via websocket to this middleware to manage their connection to the database.  I

Re: BRIN index which is much faster never chosen by planner

On Fri, Oct 11, 2019 at 09:08:05AM -0500, Jeremy Finzel wrote: On Thu, Oct 10, 2019 at 7:22 PM David Rowley wrote: The planner might be able to get a better estimate on the number of matching rows if the now() - interval '10 days' expression was replaced with 'now'::timestamptz - interval '10

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On Fri, 11 Oct 2019 at 16:41, Chapman Flack wrote: > On 10/11/19 4:30 PM, Robert Haas wrote: > > > But, if it does need to be changed, it seems like a terrible idea to > > allow it to be done via SQL. Otherwise, the user can break the driver > > by using SQL to set the list to something that the

Re: dropping column prevented due to inherited index

On 2019-Oct-11, Michael Paquier wrote: > + if (!recursing) > + { > + /* > + * The resursing lookup for inherited child relations is done. > All > + * the child relations have been scanned and the object > addresses of > + * all the

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

Hi, On 2019-10-11 16:30:17 -0400, Robert Haas wrote: > On Fri, Oct 11, 2019 at 8:21 AM Dave Cramer wrote: > > So off the top of my head providing a system function seems like the way to > > go here unless you were contemplating adding something to the protocol ? > > Since the list of

Re: stress test for parallel workers

I wrote: > What we've apparently got here is that signals were received > so fast that the postmaster ran out of stack space. I remember > Andres complaining about this as a theoretical threat, but I > hadn't seen it in the wild before. > I haven't finished investigating though, as there are

Re: stress test for parallel workers

On Sat, Oct 12, 2019 at 7:56 AM Tom Lane wrote: > This matches up with the intermittent infinite_recurse failures > we've been seeing in the buildfarm. Those are happening across > a range of systems, but they're (almost) all Linux-based ppc64, > suggesting that there's a longstanding

Re: stress test for parallel workers

On 10/11/19 11:45 AM, Tom Lane wrote: > Andrew Dunstan writes: >>> At least on F29 I have set /proc/sys/kernel/core_pattern and it works. > FWIW, I'm not excited about that as a permanent solution. It requires > root privilege, and it affects the whole machine not only the buildfarm, > and

Re: stress test for parallel workers

Andrew Dunstan writes: > On 10/11/19 11:45 AM, Tom Lane wrote: >> FWIW, I'm not excited about that as a permanent solution. It requires >> root privilege, and it affects the whole machine not only the buildfarm, >> and making it persist across reboots is even more invasive. > OK, but I'm not

Re: v12.0: ERROR: could not find pathkey item to sort

Justin Pryzby writes: > On Fri, Oct 11, 2019 at 10:48:37AM -0400, Tom Lane wrote: >> Could you provide a self-contained test case please? > I'm suspecting this; is it useful to test with this commit reverted ? I wouldn't bother; we'd still need a test case to find out what the problem is.

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On Fri, Oct 11, 2019 at 8:21 AM Dave Cramer wrote: > So off the top of my head providing a system function seems like the way to > go here unless you were contemplating adding something to the protocol ? Since the list of reportable GUCs is for the benefit of the driver, I'm not sure why this

Re: stress test for parallel workers

Andres Freund writes: > On 2019-10-11 14:56:41 -0400, Tom Lane wrote: >> ... So it's really hard to explain >> that as anything except a kernel bug: sometimes, the kernel >> doesn't give us as much stack as it promised it would. And the >> machine is not loaded enough for there to be any

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On 10/11/19 4:30 PM, Robert Haas wrote: > But, if it does need to be changed, it seems like a terrible idea to > allow it to be done via SQL. Otherwise, the user can break the driver > by using SQL to set the list to something that the driver's not > expecting, and there's nothing the driver can

Connect as multiple users using single client certificate

I have some JS middleware that needs to securely connect to the postgresql back end.  Any number of different users may connect via websocket to this middleware to manage their connection to the database.  I want the JS process to have a client certificate authorizing it to connect to the

v12.0: ERROR: could not find pathkey item to sort

I've reduced the failing query as much as possible to this: -- This is necessary to fail: SET enable_nestloop=off; SELECT * FROM (SELECT start_time, t1.site_id FROM pgw_kpi_view t1 -- Apparently the where clause is necessary to fail... WHERE

Re: PostgreSQL, C-Extension, calling other Functions

> "Stefan" == Stefan Wolf writes: Stefan> I´ve written some PostgreSQL C-Extensions (for the first Stefan> time...) and they work as expected. Stefan> But now I want to call other functions from inside the Stefan> C-Extensions (but not via SPI_execute), for example Stefan>

Re: stress test for parallel workers

On 10/10/19 6:01 PM, Andrew Dunstan wrote: > On 10/10/19 5:34 PM, Tom Lane wrote: >> I wrote: > Yeah, I've been wondering whether pg_ctl could fork off a subprocess > that would fork the postmaster, wait for the postmaster to exit, and then > report the exit status. >>> [ pushed at

Re: v12.0: ERROR: could not find pathkey item to sort

Justin Pryzby writes: > I've reduced the failing query as much as possible to this: > -- This is necessary to fail: > SET enable_nestloop=off; > SELECT * FROM > (SELECT start_time, t1.site_id > FROM pgw_kpi_view t1 > -- Apparently the where clause is necessary to fail...

Re: stress test for parallel workers

Andrew Dunstan writes: >> At least on F29 I have set /proc/sys/kernel/core_pattern and it works. FWIW, I'm not excited about that as a permanent solution. It requires root privilege, and it affects the whole machine not only the buildfarm, and making it persist across reboots is even more

Re: v12.0: ERROR: could not find pathkey item to sort

On Fri, Oct 11, 2019 at 10:48:37AM -0400, Tom Lane wrote: > Justin Pryzby writes: > > The view is actually a join of two relkind=p partitioned tables (which I > > will acknowledge probably performs poorly). > > Could you provide a self-contained test case please? Working on it. FWIW explain

v12.0: segfault in reindex CONCURRENTLY

One of our servers crashed last night like this: < 2019-10-10 22:31:02.186 EDT postgres >STATEMENT: REINDEX INDEX CONCURRENTLY child.eric_umts_rnc_utrancell_hsdsch_eul_201910_site_idx < 2019-10-10 22:31:02.399 EDT >LOG: server process (PID 29857) was terminated by signal 11: Segmentation

Re: [Proposal] Global temporary tables

pá 11. 10. 2019 v 15:50 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal: > > > On 11.10.2019 15:15, 曾文旌(义从) wrote: > > Dear Hackers, > > This propose a way to develop global temporary tables in PostgreSQL. > > I noticed that there is an "Allow temporary tables to exist as empty

Re: BTP_DELETED leaf still in tree

On Fri, Oct 11, 2019 at 12:44 AM Daniel Wood wrote: > > Actually, I take it back -- the looping part is not normal. The > > btpo_next->btpo_next page has no business linking back to the > > original/first deleted page you mentioned. That's just odd. > > btpo_next->btpo_next does NOT link directly

Re: maintenance_work_mem used by Vacuum

On Fri, Oct 11, 2019 at 5:13 PM Amit Kapila wrote: > > On Fri, Oct 11, 2019 at 7:36 AM Masahiko Sawada wrote: > > > > On Thu, Oct 10, 2019 at 6:38 PM Amit Kapila wrote: > > > > > > > > > It seems you want to say about commit id > > > a1b395b6a26ae80cde17fdfd2def8d351872f399. > > > > Yeah

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

Andres Freund writes: > On 2019-10-11 16:30:17 -0400, Robert Haas wrote: >> But, if it does need to be changed, it seems like a terrible idea to >> allow it to be done via SQL. Otherwise, the user can break the driver >> by using SQL to set the list to something that the driver's not >>

Re: [HACKERS] Block level parallel vacuum

On Fri, Oct 11, 2019 at 4:47 PM Mahendra Singh wrote: > > > I did some analysis and found that we are trying to free some already freed > memory. Or we are freeing palloced memory in vac_update_relstats. > for (i = 0; i < nindexes; i++) > { > if (stats[i] == NULL ||

Re: Change atoi to strtol in same place

Here's v6 of the patch. [x] Rebase on 20961ceaf0 [x] Don't call exit(1) after pg_fatal() [x] Use Tom Lane's suggestion for %lld in _() string [x] Allow full unsigned 16-bit range for ports (don't disallow ports 0-1023) [x] Explicitly cast parsed values to smaller integers -- Joe Nelson

Re: [HACKERS] Block level parallel vacuum

Hi On Thu, 10 Oct 2019 at 13:18, Masahiko Sawada wrote: > On Thu, Oct 10, 2019 at 2:19 PM Amit Kapila > wrote: > > > > On Fri, Oct 4, 2019 at 4:18 PM Amit Kapila > wrote: > > > > > > On Wed, Oct 2, 2019 at 7:29 PM Masahiko Sawada > wrote: > > >> > > > > Few more comments: > > Thank you for

Re: BRIN index which is much faster never chosen by planner

Dear Michael, On Thu, Oct 10, 2019 at 5:20 PM Michael Lewis wrote: > Since the optimizer is choosing a seq scan over index scan when it seems > like it has good row estimates in both cases, to me that may mean costs of > scanning index are expected to be high. Is this workload on SSD? Has the >

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On Thu, 10 Oct 2019 at 12:05, Andres Freund wrote: > Hi, > > On 2019-10-09 16:29:07 -0400, Dave Cramer wrote: > > I've added functionality into libpq to be able to set this STARTUP > > parameter as well as changed it to _pq_.report. > > Still need to document this and figure out how to test it.

Re: BRIN index which is much faster never chosen by planner

On Thu, Oct 10, 2019 at 7:22 PM David Rowley wrote: > The planner might be able to get a better estimate on the number of > matching rows if the now() - interval '10 days' expression was > replaced with 'now'::timestamptz - interval '10 days'. However, care > would need to be taken to ensure the

Re: Collation versioning

Re: Thomas Munro 2019-10-11 > While testing pg_upgrade scenarios I noticed that initdb-created > collations' versions are not preserved, potentially losing track of > information about corrupted indexes. That's a preexisting condition, > and probably well understood, but it made me realise that

Re: [PATCH] use separate PartitionedRelOptions structure to store partitioned table options

В Thu, 10 Oct 2019 15:50:05 +0900 Amit Langote пишет: > > I think it is bad idea to suggest option adder to ad it to > > StdRdOption, we already have a big mess there. Better if he add it > > to an new empty structure. > > I tend to agree that this improves readability of the reloptions code >

Re: [PATCH] use separate PartitionedRelOptions structure to store partitioned table options

> > I think it is bad idea to suggest option adder to ad it to > > StdRdOption, we already have a big mess there. Better if he add it > > to an new empty structure. > > I tend to agree that this improves readability of the reloptions code > a bit. > > Some comments on the patch: > > How about

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

В письме от четверг, 10 октября 2019 г. 17:17:30 MSK пользователь Amit Langote написал: > I read comments that Tomas left at: > https://www.postgresql.org/message-id/20190727173841.7ypzo4xuzizvijge%40deve > lopment > > I'd like to join Michael in reiterating one point from Tomas' review. > I

Re: Collation versioning

On Thu, Oct 10, 2019 at 8:38 AM Peter Eisentraut wrote: > On 2019-10-09 21:19, Peter Eisentraut wrote: > > On 2019-10-03 14:25, Thomas Munro wrote: > >>> The only open question on this patch was whether it's a good version to > >>> use. I think based on subsequent discussions, there was the

[Proposal] Global temporary tables

Dear Hackers, This propose a way to develop global temporary tables in PostgreSQL. I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist. https://wiki.postgresql.org/wiki/Todo In

Re: [Proposal] Global temporary tables

On 11.10.2019 15:15, 曾文旌(义从) wrote: Dear Hackers, This propose a way to develop global temporary tables in PostgreSQL. I noticed that there is an "Allow temporary tables to exist as empty by default in all sessions" in the postgresql todolist. https://wiki.postgresql.org/wiki/Todo In

Re: Remove size limitations of vacuums dead_tuples array

On Thu, 10 Oct 2019 at 17:05, Tomas Vondra wrote: > There already was a attempt to make this improvement, see [1]. There was > a fairly long discussion about how to best do that (using other data > structure, not just a simple array). It kinda died about a year ago, but > I suppose there's a lot