Re: reclaiming space from heavily used tables?

I don’t see how I can use that extension. If some people are saying it’s dangerous and others are saying it’s fine, I don’t have the time to drill down into PG internals so that I can judge for myself. I’ll probably try the two table idea outlined in my original message. > On Dec 18, 2017, at 5

Re: reclaiming space from heavily used tables?

> > Maybe the nature of the corruption caused is different. It took months > of running large databases on production for corruption to become > apparent from multixact bugs, for example. Or maybe because the > relfrozenxid is fixed by other activity in the system, any bugs are > masked -- but th

Re: reclaiming space from heavily used tables?

Jeremy Finzel wrote: > > > > It's been around, but is it trusted? I for one do not trust it. See > > for example > > https://www.postgresql.org/message-id/CA+TgmoaWoU+BpBG4nwz1L > > gkmdsbl6_hm9r8jnqte-5vrdls...@mail.gmail.com > > Needs some discussion. Has anyone actually reported corruption re

Re: reclaiming space from heavily used tables?

> > It's been around, but is it trusted? I for one do not trust it. See > for example > https://www.postgresql.org/message-id/CA+TgmoaWoU+BpBG4nwz1L > gkmdsbl6_hm9r8jnqte-5vrdls...@mail.gmail.com > > Needs some discussion. Has anyone actually reported corruption related to this? I don't doubt t

Re: Re: PostgreSQL needs percentage function

On 2017-12-18 09:21:49 -0700, David G. Johnston wrote: > Frankly, the name "percent" is a poor choice: [...] > And why is percent >1 as opposed to (generally) between 0 and 1? That's what "per cent" means: "of hundred". The whole is 100. If the whole is 1, it's not a percentage (and yes, percent_r

Re: Re: PostgreSQL needs percentage function

On 2017-12-18 18:13:58 +0200, Nick Dro wrote: > I know how to implement this. It's not the issue. > It's very easy to implement absolute value as well yet still PostgreSQL gives > abs(x) function which is build in function. > My claim is that if there is a build in function for absolute value why n

Re: reclaiming space from heavily used tables?

Jeremy Finzel wrote: > This is what you want: https://github.com/reorg/pg_repack > > This has been around for many years and is a very trusted extension (when > will it be in core). It's been around, but is it trusted? I for one do not trust it. See for example https://www.postgresql.org/m

Re: reclaiming space from heavily used tables?

On Mon, Dec 18, 2017 at 1:03 PM, Rob Nikander wrote: > Hi, > > I've got a large table from which I'd like to completely reclaim space. I > read the docs and it sounds like I can’t run `vacuum full`, because this > table is accessed constantly and can’t have downtime. Assuming that’s true, > what

reclaiming space from heavily used tables?

Hi, I've got a large table from which I'd like to completely reclaim space. I read the docs and it sounds like I can’t run `vacuum full`, because this table is accessed constantly and can’t have downtime. Assuming that’s true, what do you think of the following idea? Is there a better alternati

Re: PostgreSQL needs percentage function

Em 18/12/2017 15:01, David G. Johnston escreveu: On Mon, Dec 18, 2017 at 9:56 AM, Edson Carlos Ericksson Richter mailto:rich...@simkorp.com.br>>wrote: But, is it possible to have a aggregate function that calculates de percent from the sum (and/or count) total (as a value from 0 ..

Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 11:01 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​Do you mean: > > SELECT id, val, val / (sum(val) OVER ()) > FROM vals;​ > You could end up with a percentage > 100 or divide by 0 with that if the values are not in a proper range. I don't know if that w

Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 9:56 AM, Edson Carlos Ericksson Richter < rich...@simkorp.com.br> wrote: > But, is it possible to have a aggregate function that calculates de > percent from the sum (and/or count) total (as a value from 0 ... 1) for > numeric (or double)? > ​Do you mean: SELECT id, val,

Re: PostgreSQL needs percentage function

Em 18/12/2017 14:28, Michael Nolan escreveu: On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro > wrote: Hi, Why PostgreSQL doesn't have build-in function to calculate percentage? somthing like percent(number,% for example: select percent(100,1) will

Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro wrote: > Hi, > Why PostgreSQL doesn't have build-in function to calculate percentage? > somthing like percent(number,% > for example: > select percent(100,1) will calculate 1% of 100 = 1 > select percent(25,20) will calculate 20% of 25 = 5 > > Seems like

Re: Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 9:13 AM, Nick Dro wrote: > > Hi, > I know how to implement this. It's not the issue. > It's very easy to implement absolute value as well yet still PostgreSQL > gives abs(x) function which is build in function. > My claim is that if there is a build in function for absolut

Re: Re: PostgreSQL needs percentage function

2017-12-18 17:13 GMT+01:00 Nick Dro : > > Hi, > I know how to implement this. It's not the issue. > It's very easy to implement absolute value as well yet still PostgreSQL > gives abs(x) function which is build in function. > My claim is that if there is a build in function for absolute value why

Re: Re: PostgreSQL needs percentage function

On 18 December 2017 at 16:13, Nick Dro wrote: > Can you give a good reason why absolute value has a build in function while > percentage is not? ABS is an ansi-standard SQL function. Geoff

RE: Re: PostgreSQL needs percentage function

Hi, I know how to implement this. It's not the issue. It's very easy to implement absolute value as well yet still PostgreSQL gives abs(x) function which is build in function. My claim is that if there is a build in function for absolute value why not for percentage? Both are very basic mathematic

Re: PostgreSQL needs percentage function

On Mon, Dec 18, 2017 at 02:23:38PM +0200, Nick Dro wrote: >Hi, >Why PostgreSQL doesn't have build-in function to calculate percentage? >somthing like percent(number,% >for example: >select percent(100,1) will calculate 1% of 100 = 1 >select percent(25,20) will calculate 20%

Re: PgBackRest question?

On 12/17/17 7:10 PM, chiru r wrote: > Thanks David for the replay. > > we are not interested to use replication/Standby configuration at this > moment with pgbackrest. Database restores all work the same way -- the only difference is a few configuration parameters. > We are looking to restore th

Re: PostgreSQL needs percentage function

On 18/12/2017 14:23, Nick Dro wrote: Hi, Why PostgreSQL doesn't have build-in function to calculate percentage? somthing like percent(number,% for example: select percent(100,1) will calculate 1% of 100 = 1 select percent(25,20) will calculate 20% of 25 = 5 CREATE OR REPLACE FUNCTION percent(x

PostgreSQL needs percentage function

Hi, Why PostgreSQL doesn't have build-in function to calculate percentage? somthing like percent(number,% for example: select percent(100,1) will calculate 1% of 100 = 1 select percent(25,20) will calculate 20% of 25 = 5   Seems like a nice addition to the math functions list: https://www.postgresq

Re: Size of pg_multixact/members increases 11355

Dear All, I have upgraded version from 9.3.6 to 9.3.20 but this issue is not resolved. can you please suggest in which version this file size are not increased. Regards, Yogesh On Thursday, December 14, 2017, Alvaro Herrera wrote: > Yogesh Sharma wrote: > > Dear Thomas , > > > > Thanks for sh

Converting timestamp to timestamptz without rewriting tables

Hi all We have generally been using timestamps without timezones in our system. As both our app servers and db server were set to UTC it so far hasn't been an issue. However, that may not always be the case, so we want to tighten things up a bit. We are also needing to do things like get the parti

Re: Converting timestamp to timestamptz without rewriting tables

On Mon, Dec 18, 2017 at 2:18 AM, Tom Dunstan wrote: > >> >> A timestamptz stores everything as UTC and the value is converted to the session time zone upon retrieval. > > > Which is exactly what we want. If a random person can interject here, I believe what Tom Dunstan is asking about here is the

Re: Converting timestamp to timestamptz without rewriting tables

On 18 December 2017 at 18:43, Thomas Kellerer wrote: > > All of the timestamps in our system represent an instant in time, not > > a clock date/time, so timestamp with time zone is more appropriate. > > All of the data that is currently on disk in timestamp columns was > > inserted in a db sessio

Re: Converting timestamp to timestamptz without rewriting tables

Tom Dunstan schrieb am 18.12.2017 um 09:08: > We have generally been using timestamps without timezones in our > system. As both our app servers and db server were set to UTC it so > far hasn't been an issue. However, that may not always be the case, > so we want to tighten things up a bit. We are

Converting timestamp to timestamptz without rewriting tables

Hi all We have generally been using timestamps without timezones in our system. As both our app servers and db server were set to UTC it so far hasn't been an issue. However, that may not always be the case, so we want to tighten things up a bit. We are also needing to do things like get the parti