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 parameter "wait_start" time

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 every new waiting per

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. Even on platforms wher

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 regre

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 would tell how long tim

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 >> astonishingly bad. We

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. The difficulty with

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 things happened. > > I w

[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 waiting=

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 that the code be able to

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 negative checkpoint_timeout?

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. Presumably they could just

[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 are > some compiler segment

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 lane -- Sent via pgsql-hac

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 use the record_image_ops mec

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, I don't see why we need to prevent that.

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 releases before abandoning it, a

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 like 30s is >>> likely to be a good idea

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 thes

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 j

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 some additional messaging after >>

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, further

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 or EXCEPT ALL set op

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 cannot push quals int

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 d

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 lower limit to 1s. >

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 62 minutes is probabl

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 >> > We want to have the patch actually attache

[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 ha

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 following review has been posted through the commitfest application: make inst

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 avo

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 want to have the patch actually

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 goin

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 attached, not just stored on nabble

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 code can be used for

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 there, as well as grant & r

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 AP

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 unsha

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 sequence either... it is >

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 is

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 problem. >>> I could get your point almost. Does the last line above

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 improvement, but you and > Alvaro obviously do, so

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 mechanical patch I wrote in 20 minutes. > > Do you h

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 taking the next step of rem

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 installcheck-world: tested, failed Implements feature:

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 objects with temporal unsh

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 has_server_privilege(repeat('x',100),'y'); server closed th

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 for it needs some wo

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 preve

[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 src/backen

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 flow and be a > >> com

[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. It's

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: tested, passed Docume

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. The

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 WHERE

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 realize just now that I forgot to

[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)? I

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 li

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 here rather than on a separate

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 myself but havne't looked

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 slot be created WITHOUT

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 TruncateCLOG rather than > the cal

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 extensions >> after >> > this change? >> > >> > Orafce hits this change. >> >> arra