Re: Should we add GUCs to allow partition pruning to be disabled?

Thank you both of you for looking at this. On 21 April 2018 at 06:28, Alvaro Herrera wrote: > + {"enable_partition_pruning", PGC_USERSET, QUERY_TUNING_METHOD, > + gettext_noop("Enables the planner's ability to remove > non-required

proposal: force slow part of plpgsql compilation

Hi I am plpgsql_check author and now, I am solving bug related to cached plpgsql function. I am not able fore plpgsql compilation - and sometimes the compiled version can be obsolete. As protection to false alarms, I need fresh compilation immediately before plpgsql_check call. I need a new

Re: Corrupted btree index on HEAD because of covering indexes

On Fri, Apr 20, 2018 at 7:18 AM, Teodor Sigaev wrote: > After close look I change my opinion. To have a clean code it's much better > to have new pair get/set macroses specialy to manage link to top pare during > page deletion. This removes last naked usage of >

Re: Excessive PostmasterIsAlive calls slow down WAL redo

Here's a new version, because FreeBSD's new interface changed slightly. -- Thomas Munro http://www.enterprisedb.com 0001-Use-signals-for-postmaster-death-on-Linux-v3.patch Description: Binary data 0002-Use-signals-for-postmaster-death-on-FreeBSD-v3.patch Description: Binary data

Re: Searching for: Fast windows buildfarm animal

Stephen Frost writes: > * Andres Freund (and...@anarazel.de) wrote: >> It'd be awesome if somebody could set up a windows animal that runs >> frequently (i.e. checks for build needed every minute or five) and is >> fast enough to not take ages to finish a build. > Done.

Re: Searching for: Fast windows buildfarm animal

Greetings, * Andres Freund (and...@anarazel.de) wrote: > It's common that half the buildfarm has reported back before a single > windows buildfarm animal reports. And if they report a failure one often > has to wait for hours for the next run. Yes, that's been rather annoying. > It'd be awesome

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

[...] > I've taken a look at your patch. I agree that having a plan identifier > would be great, but I'm not a big fan of the jumbling. That's a lot of > hashing that needs to be done to decide wether two plans are > essentially equivalent or not. As there is no planid available yet in core, I

Re: [sqlsmith] Unpinning error in parallel worker

On Fri, Apr 20, 2018, at 00:42, Thomas Munro wrote: > On Wed, Apr 18, 2018 at 11:43 AM, Jonathan Rudenberg > wrote: > > On Tue, Apr 17, 2018, at 19:31, Thomas Munro wrote: > >> On Wed, Apr 18, 2018 at 11:01 AM, Jonathan Rudenberg > >> wrote: > >> >

Re: Event trigger bugs (was Re: Repeated crashes in GENERATED ... AS IDENTITY tests)

I wrote: > Hence, two questions: > * Should EventTriggerTableRewrite do > if (!currentEventTriggerState || > currentEventTriggerState->commandCollectionInhibited) > return; > like most of the other functions, or should it just check for null > currentEventTriggerState? After

Re: PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS

On Wed, Apr 18, 2018 at 08:45:53PM +0800, Craig Ringer wrote: > wrOn 18 April 2018 at 19:46, Bruce Momjian wrote: > > > So, if sync mode passes the write to NFS, and NFS pre-reserves write > > space, and throws an error on reservation failure, that means that NFS > > will not

Re: Add read-only param to set_config(...) / SET that effects (at least) customized runtime options

On 04/20/2018 04:03 PM, Andrew Gierth wrote: >> "Chapman" == Chapman Flack writes: > Chapman> It seemed to me at the time to be not hard to implement, even > Chapman> just as an extension for proof of concept. One small obstacle > Chapman> is that the check hook on a

Re: Corrupted btree index on HEAD because of covering indexes

On Thu, Apr 19, 2018 at 11:41 PM, Teodor Sigaev wrote: >> heard of people using bt_index_parent_check() in production, but only >> when they already knew that their database was corrupt, and wanted to >> isolate the problem. I imagine that people that use >>

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: Amit> I haven't tried to reproduce it, but I can see the possibility of Amit> the problem described by you. What should we do next? I could see Amit> few possibilities: (a) Vacuum for main and toast table should Amit> always use

Re: Add read-only param to set_config(...) / SET that effects (at least) customized runtime options

> "Chapman" == Chapman Flack writes: Chapman> It seemed to me at the time to be not hard to implement, even Chapman> just as an extension for proof of concept. One small obstacle Chapman> is that the check hook on a variable doesn't get called during Chapman>

Re: Reopen logfile on SIGHUP

On 04/16/2018 05:54 AM, Kyotaro HORIGUCHI wrote: We can provide a new command "pg_ctl logrotate" to hide the details. (It cannot be executed by root, though.) I like this approach. I looked at the patch and changed some things: - cleaned up the error messages - moved checkLogrotateSignal to

Re: Should we add GUCs to allow partition pruning to be disabled?

diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index fa92ce2e68..c51a9270e4 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -951,6 +951,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL,

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote wrote: > Sorry, I should have said what I said after quoting only the last sentence > of what you had said. That is, I want to the new GUC to be the only > determiner of whether the pruning occurs or not for partitioned tables. > To implement that behavior, it will have to override

Re: Postgresql9.6 type cache invalidation issue - different behave of psql and pg regress

2018-04-20 15:44 GMT+02:00 Tom Lane : > Pavel Stehule writes: > > In interactive mode, the build_row_from_class has unrefreshed metadata. > But > > why this behave I see only in psql and not in my regress tests? > > The short answer is that no plpgsql

Re: [HACKERS] proposal: schema variables

2018-04-20 17:32 GMT+02:00 Robert Haas : > On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule > wrote: > > It true, so there are lot of "unused" attributes for this purpose, but > there > > is lot of shared attributes, and lot of shared code.

Re: Foreign keys and partitioned tables

Alvaro Herrera wrote: > After wasting some time trying to resolve > "minor last minute issues", I decided to reduce the scope for now: in > the current patch, it's allowed to have foreign keys in partitioned > tables, but it is not possible to have foreign keys that point to > partitioned tables.

Re: Postgres stucks in deadlock detection

Konstantin Knizhnik wrote: > As I have mentioned at the beginning of this thread the same problem with > deadlock detection timeout expiration we have with YSCB benchmark with zipf > distribution. > Here the source of contention are tuple locks. And as far as I understand > from the discussion in

Re: [HACKERS] Custom compression methods

On 30.03.2018 19:50, Ildus Kurbangaliev wrote: On Mon, 26 Mar 2018 20:38:25 +0300 Ildus Kurbangaliev wrote: Attached rebased version of the patch. Fixed conflicts in pg_class.h. New rebased version due to conflicts in master. Also fixed few errors and

Re: Add read-only param to set_config(...) / SET that effects (at least) customized runtime options

On 04/20/2018 09:55 AM, Matthias Kurz wrote: > SET READONLY my_app.some_var = 'foo'; > SET READONLY SESSION my_app.some_var = 'foo'; > SET READONLY LOCAL my_app.some_var = 'foo'; > > Of course read-only would default to false for backwards compatibility. > When setting READONLY for on SESSION

Re: Postgres stucks in deadlock detection

On 20.04.2018 18:36, Robert Haas wrote: On Wed, Apr 18, 2018 at 10:08 AM, Konstantin Knizhnik wrote: And it is very hard not to notice 17-times difference. Certainly it is true in the assumption that most deadlock timeout expiration are caused by high workload and

Re: [HACKERS] path toward faster partition pruning

Amit Langote wrote: > PS: git grep "partition by hash\|PARTITION BY HASH" on src/test indicates > that there are hash partitioning related tests in create_table, > foreign_key, and partition_join files as well. Do we want to use the > custom opclass in those files as well? By the way, let me

Re: Postgres stucks in deadlock detection

On Wed, Apr 18, 2018 at 10:08 AM, Konstantin Knizhnik wrote: > And it is very hard not to notice 17-times difference. > Certainly it is true in the assumption that most deadlock timeout expiration > are caused by high workload and contention, and not by real deadlocks.

Re: [HACKERS] proposal: schema variables

On Tue, Apr 17, 2018 at 12:28 PM, Pavel Stehule wrote: > It true, so there are lot of "unused" attributes for this purpose, but there > is lot of shared attributes, and lot of shared code. Semantically, I see > variables in family of sequences, tables, indexes, views.

Add read-only param to set_config(...) / SET that effects (at least) customized runtime options

Hi, in MS SQL Server you are able to make an entry of SESSION_CONTEXT read-only by passing the @read_only param to the sp_set_session_context function: "[ @read_only= ] { 0 | 1 } A flag of type bit. If 1, then the value for the specified key cannot be changed again on this logical connection. If

Re: Corrupted btree index on HEAD because of covering indexes

Should we use BTreeInnerTupleGetDownLink() as soon as we use BTreeInnerTupleSetDownLink() for setting this? Or even invent BTreeInnerTupleDownLinkIsValid() macro? I am not sure. Here we actually store UP link - to top parent to remove. I'm afraid using

Re: SHOW ALL does not honor pg_read_all_settings membership

On Fri, Apr 20, 2018 at 01:21:46PM +0200, Laurenz Albe wrote: > I agree; here is a patch for that. Thanks for taking care of that as well this looks fine to me. Note to committer: this needs to be merged with the first patch actually fixing the SHOW ALL issue. All the four core callers of

Re: Postgresql9.6 type cache invalidation issue - different behave of psql and pg regress

Pavel Stehule writes: > In interactive mode, the build_row_from_class has unrefreshed metadata. But > why this behave I see only in psql and not in my regress tests? The short answer is that no plpgsql version before commit 4b93f5799 will have nice behavior for cases

Re: Toast issues with OldestXmin going backwards

On Thu, Apr 19, 2018 at 4:07 PM, Andrew Gierth wrote: > Various comments in GetOldestXmin mention the possibility of the oldest > xmin going backward, and assert that this is actually safe. It's not. > > Consider: > > A table has a toastable column. A row is updated

Re: Explain buffers wrong counter with parallel plans

Hello, I tried to understand this issue and it seems Gather node only take account of this own buffer usage : create unlogged table t1 (c1 int); insert into t1 select generate_series(1,100); vacuum t1; explain (analyze,buffers,timing off,costs off) select count(*) from t1;

Re: Built-in connection pooling

>> I understand your customers like to have unlimited number of >> connections. But my customers do not. (btw, even with normal >> PostgreSQL, some of my customers are happily using over 1k, even 5k >> max_connections). > > If you have limited number of client, then you do not need pooling at >

Re: Problem while setting the fpw with SIGHUP

On Fri, Apr 20, 2018 at 11:40 AM, Kyotaro HORIGUCHI wrote: > By the way, I think I found a bug of FPW. > > The following steps yields INSERT record that doesn't have a FPI > after a checkpoint. > > (Start server with full_page_writes = off) > CREATE TABLE t (a

Re: Built-in connection pooling

On Fri., 20 Apr. 2018, 06:59 Andres Freund, wrote: > On 2018-04-19 15:01:24 -0400, Tom Lane wrote: > > Only after you can say "there's nothing wrong with this that isn't > > directly connected to its not being in-core" does it make sense to try > > to push the logic into

Re: SHOW ALL does not honor pg_read_all_settings membership

Michael Paquier wrote: > On Mon, Apr 16, 2018 at 02:32:10PM +0200, Laurenz Albe wrote: > > Now that the dust from the last commitfest is settling, I'll make a second > > attempt to attract attention for this small bug fix. > > > > The original commit was Simon's. > > Thanks for the ping. > >

Re: Boolean partitions syntax

On 2018/04/19 20:35, Kyotaro HORIGUCHI wrote: > At Wed, 18 Apr 2018 19:27:16 +0900, Amit Langote wrote: >> On 2018/04/16 16:17, Kyotaro HORIGUCHI wrote: >>> It was a bother that some rules used c_expr directly but I >>> managed to replace all of them with a_expr by lowering precedence >>> of some

Re: Built-in connection pooling

On 20.04.2018 12:02, Tatsuo Ishii wrote: I understand your customers like to have unlimited number of connections. But my customers do not. (btw, even with normal PostgreSQL, some of my customers are happily using over 1k, even 5k max_connections). If you have limited number of client,

Re: Should we add GUCs to allow partition pruning to be disabled?

On 2018/04/20 17:51, Amit Langote wrote: > On 2018/04/20 14:47, David Rowley wrote: >> On 20 April 2018 at 14:07, Amit Langote >> wrote: >>> To clarify: if we're going to add a new parameter *for partitioned tables* >>> to configure whether or not pruning occurs,

Re: Built-in connection pooling

This is only applied to external process type pooler (like Pgpool-II). > - temporary tables > - prepared statements > - GUCs and other session state These are only applied to "non session based" pooler; sharing a database connection with multiple client connections.

Postgresql9.6 type cache invalidation issue - different behave of psql and pg regress

Hi I searching a reason why result of plpgsql_check's regress tests are different when its executed from regress tests or interactive. This is simple test drop table testtable; create table testtable(a int, b int); create or replace function test() returns int as $$ declare r testtable; begin

Re: Should we add GUCs to allow partition pruning to be disabled?

Hi David. Thanks for writing the patch. On 2018/04/20 14:47, David Rowley wrote: > On 20 April 2018 at 14:07, Amit Langote wrote: >> To clarify: if we're going to add a new parameter *for partitioned tables* >> to configure whether or not pruning occurs, even if

Re: Should we add GUCs to allow partition pruning to be disabled?

On 2018/04/20 15:00, Ashutosh Bapat wrote: > On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote wrote: >> On 2018/04/19 21:50, Ashutosh Bapat wrote: >>> There's no point in confusing users >>> with by adding dependencies between these two GUCs. >> >> That's exactly what I'm trying to propose. > > Not

Re: Built-in connection pooling

On 20.04.2018 11:16, Tatsuo Ishii wrote: On 20.04.2018 01:58, Tatsuo Ishii wrote: I think there's plenty things that don't really make sense solving outside of postgres: - additional added hop / context switches due to external pooler This is only applied to external process type pooler

Re: Problem while setting the fpw with SIGHUP

I noticed that the previous patch is a mixture with another patch.. sorry. At Thu, 19 Apr 2018 19:11:43 +0530, Amit Kapila wrote in > On Thu, Apr 19, 2018 at 7:19 AM, Michael Paquier

Re: Built-in connection pooling

> On 20.04.2018 01:58, Tatsuo Ishii wrote: >>> I think there's plenty things that don't really make sense solving >>> outside of postgres: >>> - additional added hop / context switches due to external pooler >> This is only applied to external process type pooler (like Pgpool-II). >> >>> -

Re: Built-in connection pooling

On 20.04.2018 03:14, Tatsuo Ishii wrote: On Fri, Apr 20, 2018 at 07:58:00AM +0900, Tatsuo Ishii wrote: Yeah. Since SCRAM auth is implemented, some connection poolers including Pgpool-II are struggling to adopt it. Er, well. pgpool is also taking advantage of MD5 weaknesses... While SCRAM

Re: Built-in connection pooling

On 20.04.2018 01:58, Tatsuo Ishii wrote: I think there's plenty things that don't really make sense solving outside of postgres: - additional added hop / context switches due to external pooler This is only applied to external process type pooler (like Pgpool-II). - temporary tables -

Re: Built-in connection pooling

On 19.04.2018 17:27, Dave Cramer wrote: On Thu, Apr 19, 2018, 9:24 AM Konstantin Knizhnik, > wrote: On 19.04.2018 07:46, Tsunakawa, Takayuki wrote: > From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru

Re: Built-in connection pooling

>Development in built-in connection pooling will be continued in https://github.com/postgrespro/postgresql.builtin_pool.git The branch (as of 0020c44195992c6dce26baec354a5e54ff30b33f) passes pgjdbc tests: https://travis-ci.org/vlsi/pgjdbc/builds/368997672 Current tests are mostly

Re: Built-in connection pooling

Christopher>One of the things that they find likable is that by having the connection pool live Christopher>in the framework alongside the application is that this makes it easy to attach Christopher>hooks so that the pool can do intelligent things based on application-aware logic. I'm afraid I

Re: Corrupted btree index on HEAD because of covering indexes

heard of people using bt_index_parent_check() in production, but only when they already knew that their database was corrupt, and wanted to isolate the problem. I imagine that people that use bt_index_parent_check() in production do so because they want as much information as possible, and are

Re: Corrupted btree index on HEAD because of covering indexes

I tried to minimize Michael's test case and add it to patch. -if (ItemPointerIsValid(leafhikey)) +if (ItemPointerGetBlockNumberNoCheck(leafhikey) != InvalidBlockNumber) Should we use BTreeInnerTupleGetDownLink() as soon as we use BTreeInnerTupleSetDownLink() for setting this? Or even

Re: Problem while setting the fpw with SIGHUP

By the way, I think I found a bug of FPW. The following steps yields INSERT record that doesn't have a FPI after a checkpoint. (Start server with full_page_writes = off) CREATE TABLE t (a int); CHECKPOINT; INSERT INTO t VALUES (1); ALTER SYSTEM SET full_page_writes TO on; SELECT

Re: Should we add GUCs to allow partition pruning to be disabled?

On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote wrote: > On 2018/04/19 21:50, Ashutosh Bapat wrote: >> On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote >>> I can imagine having a enable_partition_pruning which defaults to true, if >>> only to avoid the performance