Re: [HACKERS] pg_stat_activity.waiting_start

Attached is a patch implementing the seconds-resolution wait_start, but presented as a timestamptz to the user, just like the other *_start fields: commit c001e5c537e36d2683a7e55c7c8bfcc154de4c9d Author: Joel Jacobson Date: Sat Dec 24 13:20:09 2016 +0700 Add OUT

Re: [HACKERS] pg_stat_activity.waiting_start

On Sat, Dec 24, 2016 at 9:56 AM, Joel Jacobson wrote: >> The difficulty with that is it'd require a gettimeofday() call for >> every wait start. Even on platforms where those are relatively cheap, I just realized how this can be optimized. We only need to set wait_start for

Re: [HACKERS] pg_stat_activity.waiting_start

On Sat, Dec 24, 2016 at 9:00 AM, Tom Lane wrote: >> I would like to propose adding a fourth such column, "waiting_start", >> which would tell how long time a backend has been waiting. > > The difficulty with that is it'd require a gettimeofday() call for > every wait start.

Re: [HACKERS] Indirect indexes

Alvaro Herrera wrote: > There are a few broken things yet, such as "REINDEX TABLE pg_class" and > some other operations specifically on pg_class. This one in particular > breaks the regression tests, but that shouldn't be terribly difficult to > fix. This version fixes this problem, so the

Re: [HACKERS] pg_stat_activity.waiting_start

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Joel Jacobson writes: > > We already have xact_start, query_start and backend_start > > to get the timestamptz for when different things happened. > > > I would like to propose adding a fourth such column, "waiting_start", > > which

Re: [HACKERS] pg_stat_activity.waiting_start

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> The difficulty with that is it'd require a gettimeofday() call for >> every wait start. Even on platforms where those are relatively cheap, >> the overhead would be nasty --- and on some platforms, it'd be >>

Re: [HACKERS] pg_stat_activity.waiting_start

Joel Jacobson writes: > We already have xact_start, query_start and backend_start > to get the timestamptz for when different things happened. > I would like to propose adding a fourth such column, "waiting_start", > which would tell how long time a backend has been waiting.

Re: [HACKERS] pg_stat_activity.waiting_start

Actually, "wait_start" is a better name to match the others ("wait_event_type" and "wait_event"). On Sat, Dec 24, 2016 at 8:20 AM, Joel Jacobson wrote: > Hi hackers, > > We already have xact_start, query_start and backend_start > to get the timestamptz for when different

[HACKERS] pg_stat_activity.waiting_start

Hi hackers, We already have xact_start, query_start and backend_start to get the timestamptz for when different things happened. I would like to propose adding a fourth such column, "waiting_start", which would tell how long time a backend has been waiting. The column would be NULL when

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

On 12/23/16 7:26 PM, Tom Lane wrote: > David Steele writes: >> What about a ./configure option that basically removes the min/max >> limits of every setting where it makes sense? > > It's pretty much never the case that anything goes; for example, > are we going to insist

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

David Steele writes: > What about a ./configure option that basically removes the min/max > limits of every setting where it makes sense? It's pretty much never the case that anything goes; for example, are we going to insist that the code be able to respond sanely to

Re: [HACKERS] Clarifying "server starting" messaging in pg_ctl start without --wait

On 12/23/16 6:10 PM, Tom Lane wrote: Michael Paquier writes: Is there still a use case for --no-wait in the real world? Sure. Most system startup scripts aren't going to want to wait. If we take it out those people will go back to starting the postmaster by hand.

[HACKERS] Compiler warning

I am seeing this compiler warning in the 9.4 branch: 9.4: basebackup.c:1284:6: warning: variable 'wait_result' set but not used [-Wunused-but-set-variable] This is on Debian Jessie with gcc version 4.9.2. It is from this commit: commit f6508827afe76b2c3735a9ce073620e708d60c79

Re: [HACKERS] Time to retire Windows XP buildfarm host?

Alvaro Herrera writes: > Andrew Dunstan wrote: >> ... and miraculously it has fixed itself. > And it failed again today, once. > Today I noticed that it's running gcc 4.5.0. But for the 4.5 branch, > the GCC guys put out a few releases before abandoning it, and there

Re: [HACKERS] Clarifying "server starting" messaging in pg_ctl start without --wait

Michael Paquier writes: > Is there still a use case for --no-wait in the real world? Sure. Most system startup scripts aren't going to want to wait. If we take it out those people will go back to starting the postmaster by hand. regards, tom

Re: [HACKERS] proposal: session server side variables

On 12/23/16 4:24 PM, Fabien COELHO wrote: I think that a special purpose variable infrastructure implied by your remark is just starting from the end point. The first three points seem relevant too because they help focus on other issues. If you want to ignore performance, there are things you

Re: [HACKERS] propose to pushdown qual into EXCEPT

Alvaro Herrera writes: > Tom Lane wrote: >> That is not an adequate argument for such a change being okay. Postgres, >> with its extensible set of datatypes, has to be much more careful about >> the semantic soundness of optimizations than some other DBs do. > Can we

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

On 12/23/16 6:12 PM, Michael Paquier wrote: > On Sat, Dec 24, 2016 at 6:02 AM, David Steele wrote: >> On 12/23/16 10:24 AM, Tom Lane wrote: >>> Andres Freund writes: While it's not a particularly good idea to set it to 1s on a production system,

Re: [HACKERS] Time to retire Windows XP buildfarm host?

On Sat, Dec 24, 2016 at 7:48 AM, Alvaro Herrera wrote: > Andrew Dunstan wrote: > >> ... and miraculously it has fixed itself. > > And it failed again today, once. > > Today I noticed that it's running gcc 4.5.0. But for the 4.5 branch, > the GCC guys put out a few

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

On Sat, Dec 24, 2016 at 6:02 AM, David Steele wrote: > On 12/23/16 10:24 AM, Tom Lane wrote: >> Andres Freund writes: >>> While it's not a particularly good idea to set it to 1s on a production >>> system, I don't see why we need to prevent that. It's not

Re: [HACKERS] Time to retire Windows XP buildfarm host?

Andrew Dunstan wrote: > ... and miraculously it has fixed itself. And it failed again today, once. Today I noticed that it's running gcc 4.5.0. But for the 4.5 branch, the GCC guys put out a few releases before abandoning it, and there are some compiler segmentation faults fixed in some of

Re: [HACKERS] Incautious handling of overlength identifiers

On 12/23/2016 12:44 PM, Tom Lane wrote: > I wrote: >> So what to do? We could run around and fix these individual cases >> and call it good, but if we do, I will bet a very fine dinner that >> more such errors will sneak in before long. Seems like we need a >> coding convention that discourages

Re: [HACKERS] Clarifying "server starting" messaging in pg_ctl start without --wait

On Fri, Dec 23, 2016 at 10:47 PM, Peter Eisentraut wrote: > On 12/20/16 3:43 PM, Peter Eisentraut wrote: >> On 12/20/16 3:31 PM, Ryan Murphy wrote: >>> I'm concerned some new users may not understand this behavior of pg_ctl, >>> so I wanted to suggest that we add

Re: [HACKERS] proposal: session server side variables

Hello Tom, Hmmm. If a variable is implemented as a one row table, then constraints are already available there, as well as grant & revoke, they can be any type including composite, nearly nothing to implement to get... A "one row" table would be a CREATE + one INSERT, UPDATE allowed,

Re: [HACKERS] propose to pushdown qual into EXCEPT

Tom Lane wrote: > "=?ISO-8859-1?B?QXJtb3I=?=" writes: > > Because PG does not pushdown qual to the none of the subquery. And I > > check the source code, find some comments in > > src/backend/optimizer/path/allpaths.c, which says "If the subquery > > contains EXCEPT

Re: [HACKERS] propose to pushdown qual into EXCEPT

"=?ISO-8859-1?B?QXJtb3I=?=" writes: > Because PG does not pushdown qual to the none of the subquery. And I > check the source code, find some comments in > src/backend/optimizer/path/allpaths.c, which says "If the subquery > contains EXCEPT or EXCEPT ALL set ops we

Re: [HACKERS] Improvements in psql hooks for variables

Rahila Syed wrote: > Kindly consider following comments, Sorry for taking so long to post an update. > There should not be an option to the caller to not follow the behaviour of > setting valid to either true/false. OK, fixed. > In following examples, incorrect error message is begin

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

On 12/23/16 10:24 AM, Tom Lane wrote: > Andres Freund writes: >> While it's not a particularly good idea to set it to 1s on a production >> system, I don't see why we need to prevent that. It's not like 30s is >> likely to be a good idea either. > >> Hence I'd like to set the

Re: [HACKERS] Incautious handling of overlength identifiers

I wrote: > So what to do? We could run around and fix these individual cases > and call it good, but if we do, I will bet a very fine dinner that > more such errors will sneak in before long. Seems like we need a > coding convention that discourages just randomly treating a C string > as a valid

Re: [HACKERS] Parallel Index-only scan

On Fri, Dec 23, 2016 at 3:03 PM, Tom Lane wrote: > Or in words of one syllable: please do not use nabble to post to the > community mailing lists. Many of those words have two syllables, and one has four. Anyhow, I think three castigating emails from committers in a span of

Re: [HACKERS] Parallel Index-only scan

Robert Haas writes: > On Fri, Dec 23, 2016 at 12:04 AM, rafia.sabih > wrote: >> Please find the attached file for the latest version of patch. >> parallel_index_only_v2.patch >>

[HACKERS] Incautious handling of overlength identifiers

Pursuant to the report here: https://www.postgresql.org/message-id/7d0809ee-6f25-c9d6-8e74-5b2967830...@enterprisedb.com I tried to test all the built-in functions that take "text" (rather than "name") arguments representing cataloged objects. I was able to provoke the same assertion failure in

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

On Fri, Dec 23, 2016 at 1:39 PM, Anastasia Lubennikova wrote: >> On Thu, Dec 22, 2016 at 12:22 PM, Claudio Freire >> wrote: >>> >>> On Thu, Dec 22, 2016 at 12:15 PM, Anastasia Lubennikova >>> wrote: The

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

Alvaro Herrera wrote: > With your WARM and my indirect indexes, plus the additions for for-key > locks, plus identity columns, there is no longer a real expectation that > we can exit early from the function. In your patch, as well as mine, > there is a semblance of optimization that tries to

Re: [HACKERS] Parallel Index-only scan

Robert Haas wrote: > On Fri, Dec 23, 2016 at 12:04 AM, rafia.sabih > wrote: > > Please find the attached file for the latest version of patch. > > > > parallel_index_only_v2.patch > > > > We

Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)

I noticed that this patch changes HeapSatisfiesHOTAndKeyUpdate() by adding one more set of attributes to check, and one more output boolean flag. My patch to add indirect indexes also modifies that routine to add the same set of things. I think after committing both these patches, the API is

Re: [HACKERS] Parallel Index-only scan

On Fri, Dec 23, 2016 at 12:04 AM, rafia.sabih wrote: > Please find the attached file for the latest version of patch. > > parallel_index_only_v2.patch > We want to have the patch actually

Re: [HACKERS] proposal: session server side variables

2016-12-23 19:28 GMT+01:00 Fabien COELHO : > > because MySQL variables are not declared - and allows assign everywhere - >> > > Ok. I do not do MySQL. > > and MSSQL variables are not persistent. >> > > Yep, but they might be? > > In one session you can use lot of roles - some

Re: [HACKERS] proposal: session server side variables

Fabien COELHO writes: >> In first iteration the constraint can be implemented with domains - but >> there is not any break to implement constraints directly on variables. > Hmmm. If a variable is implemented as a one row table, then constraints > are already available

Re: [HACKERS] proposal: session server side variables

because MySQL variables are not declared - and allows assign everywhere - Ok. I do not do MySQL. and MSSQL variables are not persistent. Yep, but they might be? In one session you can use lot of roles - some code can be used for securing interactive work, some can be for securing some

Re: [HACKERS] proposal: session server side variables

On 12/23/2016 08:20 AM, Pavel Stehule wrote: > 2016-12-23 16:27 GMT+01:00 Fabien COELHO: >> I have often wished I had such a feature, psql client side :-variables are >> just awful raw text things. Agreed. >> A few comments, mostly about the design: >> >> 1. persistent objects with temporal

Re: [HACKERS] proposal: session server side variables

2016-12-23 18:46 GMT+01:00 Fabien COELHO : > > Hello, > > I little bit dislike this style - in my proposal the session variables are >> very near to a sequences - and we have not any special symbols for >> sequences. >> > > Yep, but we do not need a syntax to reference a

Re: [HACKERS] proposal: session server side variables

Hello, I little bit dislike this style - in my proposal the session variables are very near to a sequences - and we have not any special symbols for sequences. Yep, but we do not need a syntax to reference a sequence either... it is automatic and usually hidden behind SERIAL. I know there

Re: [HACKERS] varlena beyond 1GB and matrix

On Thu, Dec 22, 2016 at 8:44 PM, Kohei KaiGai wrote: > 2016-12-23 8:23 GMT+09:00 Robert Haas : >> On Wed, Dec 7, 2016 at 10:44 PM, Kohei KaiGai wrote: Handling objects >1GB at all seems like the harder part of the

Re: [HACKERS] Declarative partitioning vs. sql_inheritance

Robert Haas writes: > On Fri, Dec 23, 2016 at 11:59 AM, Tom Lane wrote: >> Do you have any particular objection to taking the next step of removing >> enum InhOption in favor of making inhOpt a bool? > No, not really. I don't feel like it's an

Re: [HACKERS] Declarative partitioning vs. sql_inheritance

On Fri, Dec 23, 2016 at 11:59 AM, Tom Lane wrote: > Robert Haas writes: >> Great, committed. I realize just now that I forgot to credit anyone >> as a reviewer, but hopefully nobody's going to mind that too much >> considering this is a purely

Re: [HACKERS] Declarative partitioning vs. sql_inheritance

Robert Haas writes: > Great, committed. I realize just now that I forgot to credit anyone > as a reviewer, but hopefully nobody's going to mind that too much > considering this is a purely mechanical patch I wrote in 20 minutes. Do you have any particular objection to

Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem

22.12.2016 21:18, Claudio Freire: On Thu, Dec 22, 2016 at 12:22 PM, Claudio Freire wrote: On Thu, Dec 22, 2016 at 12:15 PM, Anastasia Lubennikova wrote: The following review has been posted through the commitfest application: make

Re: [HACKERS] proposal: session server side variables

2016-12-23 16:27 GMT+01:00 Fabien COELHO : > > Hello Pavel, > > The session variables should be: >> > > I have often wished I had such a feature, psql client side :-variables are > just awful raw text things. > > A few comments, mostly about the design: > > 1. persistent

Re: [HACKERS] Server Crash while running sqlsmith [TRAP: FailedAssertion("!(keylen < 64)", File: "hashfunc.c", Line: 139) ]

tushar writes: > While running sqlsmith against PG v10 , found a crash . Not sure > whether it is reported earlier or not . Please refer the standalone > testcase for the same - Hmm, so that can be boiled down to regression=# select

Re: [HACKERS] proposal: session server side variables

Hello Pavel, The session variables should be: I have often wished I had such a feature, psql client side :-variables are just awful raw text things. A few comments, mostly about the design: 1. persistent objects with temporal unshared typed content. The life of content should be limited

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

Andres Freund writes: > While it's not a particularly good idea to set it to 1s on a production > system, I don't see why we need to prevent that. It's not like 30s is > likely to be a good idea either. > Hence I'd like to set the lower limit to 1s. OK, but the documentation

Re: [HACKERS] Remove lower limit on checkpoint_timeout?

Hello Andres, for testing - like yesterday's 6ef2eba3f - it's annoying that checkpoint_timeout has 30s minimum. I've now locally patched that to be 1s a significant number of times. While it's not a particularly good idea to set it to 1s on a production system, I don't see why we need to

[HACKERS] propose to pushdown qual into EXCEPT

Recently, we find PG fails to generate an effective plan for following SQL: select * from (select * from table1 execpt select * from table2) as foo where foo.a > 0; Because PG does not pushdown qual to the none of the subquery. And I check the source code, find some comments in

Re: [HACKERS] Clarifying "server starting" messaging in pg_ctl start without --wait

On 12/20/16 3:43 PM, Peter Eisentraut wrote: > On 12/20/16 3:31 PM, Ryan Murphy wrote: >> I'm concerned some new users may not understand this behavior of pg_ctl, >> so I wanted to suggest that we add some additional messaging after >> "server starting" - something like: >> >> $ pg_ctl -D datadir

Re: [HACKERS] Minor correction in alter_table.sgml

Amit, * Amit Langote (amitlangot...@gmail.com) wrote: > On Fri, Dec 23, 2016 at 12:07 AM, Stephen Frost wrote: > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > >> (Of course, maybe the question we ought to be asking here is why > >> ATTACH/DETACH PARTITION failed to go with the

[HACKERS] Remove lower limit on checkpoint_timeout?

Hi, for testing - like yesterday's 6ef2eba3f - it's annoying that checkpoint_timeout has 30s minimum. I've now locally patched that to be 1s a significant number of times. While it's not a particularly good idea to set it to 1s on a production system, I don't see why we need to prevent that.

Re: [HACKERS] Parallel Index Scans

22.12.2016 07:19, Amit Kapila: On Wed, Dec 21, 2016 at 8:46 PM, Anastasia Lubennikova wrote: The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant:

Re: [HACKERS] pgstattuple documentation clarification

On 12/21/2016 09:04 AM, Andrew Dunstan wrote: Yes, I agree. In any case, before we change anything can we agree on a description of what we currently do? Here's a second attempt: The table_len will always be greater than the sum of the tuple_len, dead_tuple_len and free_space.

Re: [HACKERS] Logical decoding on standby

On 12/22/2016 01:21 AM, Craig Ringer wrote: + my @fields = ('plugin', 'slot_type', 'datoid', 'database', 'active', 'active_pid', 'xmin', 'catalog_xmin', 'restart_lsn'); + my $result = $self->safe_psql('postgres', 'SELECT ' . join(', ', @fields) . " FROM pg_catalog.pg_replication_slots

Re: [HACKERS] Declarative partitioning vs. sql_inheritance

On Mon, Dec 19, 2016 at 12:25 AM, Amit Langote wrote: >> I agree. Patch attached, just removing the GUC and a fairly minimal >> amount of the supporting infrastructure. > > +1 to removing the sql_inheritance GUC. The patch looks good to me. Great, committed. I

[HACKERS] Server Crash while running sqlsmith [TRAP: FailedAssertion("!(keylen < 64)", File: "hashfunc.c", Line: 139) ]

Hi, While running sqlsmith against PG v10 , found a crash . Not sure whether it is reported earlier or not . Please refer the standalone testcase for the same - [centos@tusharcentos7 bin]$ ./psql postgres -p 9000 psql (10devel) Type "help" for help. postgres=# select postgres-#

Re: [HACKERS] Parallel Index Scans

>> 5. Comment for _bt_parallel_seize() says: >> "False indicates that we have reached the end of scan for >> current scankeys and for that we return block number as P_NONE." >> >> What is the reason to check (blkno == P_NONE) after checking (status == false) >> in _bt_first() (see code below)?

Re: [HACKERS] Parallel Index Scans

On 12/23/2016 05:38 PM, Robert Haas wrote: So why are you reporting it here rather than on a separate thread? We found it -while testing parallel index scan and later it turned out to be crash in general. Sure- make sense ,will do that. -- regards,tushar -- Sent via pgsql-hackers mailing

Re: [HACKERS] Parallel Index Scans

On Fri, Dec 23, 2016 at 1:35 AM, tushar wrote: > In addition to that, we run the sqlsmith against PG v10+PIS (parallel index > scan) patches and found a crash but that is coming on plain PG v10 > (without applying any patches) as well So why are you reporting it

Re: [HACKERS] Minor correction in alter_table.sgml

On Fri, Dec 23, 2016 at 12:07 AM, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> (Of course, maybe the question we ought to be asking here is why >> ATTACH/DETACH PARTITION failed to go with the flow and be a >> combinable action.) > > I did wonder that

Re: [HACKERS] Logical decoding on standby

On 22 December 2016 at 14:21, Craig Ringer wrote: changes-in-0001-v2.diff shows the changes to PostgresNode.pm per Michael's comments, and applies on top of 0001. I also attach a patch to add a new CREATE_REPLICATION_SLOT option per Petr's suggestion, so you can request a

Re: [HACKERS] [PATCH] Transaction traceability - txid_status(bigint)

On 23 December 2016 at 01:26, Robert Haas wrote: > This patch contains unresolved merge conflicts. Ah, it conflicts with fe0a0b59, the PostmasterRandom changes. I thought I'd rebased more recently than that. > Maybe SetPendingTransactionIdLimit could happen in

Re: [HACKERS] BUG: pg_stat_statements query normalization issues with combined queries

Yes. I'll try to put together a patch and submit it to the next CF. Here it is. I'll add this to the next CF. This patch fixes the handling of compound/combined queries by pg_stat_statements (i.e. several queries sent together, eg with psql: "SELECT 1 \; SELECT 2;"). This bug was found

Re: [HACKERS] [COMMITTERS] pgsql: Simplify LWLock tranche machinery by removing array_base/array_s

2016-12-21 14:00 GMT+01:00 Pavel Stehule : > > > 2016-12-21 13:54 GMT+01:00 Robert Haas : > >> On Wed, Dec 21, 2016 at 2:14 AM, Pavel Stehule >> wrote: >> > Is there some help for extensions developers, how to fix