Re: help with generation_series in pg10

[Alvaro Herrera]

Márcio A. Sepp wrote:

> how can i have the same in pg10 as i have had in pg 9.x? 

Move the function call to the FROM clause:

select g, (g - 1) % 5 + 1 from generate_series(1, 10) g; 

> I need it to date type to...  if possible. 

There is a generate_series() variant that can return dates (more
precisely, timestamp with time zone).  But what exactly would you like
returned?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: help with generation_series in pg10

[Vincenzo Romano]

2018-01-09 11:01 GMT+01:00 Alvaro Herrera :
> Márcio A. Sepp wrote:
>
>> how can i have the same in pg10 as i have had in pg 9.x?
>
> Move the function call to the FROM clause:
>
> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;
>
>> I need it to date type to...  if possible.
>
> There is a generate_series() variant that can return dates (more
> precisely, timestamp with time zone).  But what exactly would you like
> returned?
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

You can add a generated series as seconds, minutes, hours ... to a
base timestamp.
Yes, it's not an easy going expression, but I'd do it like this.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Sv: Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[Andreas Joseph Krogh]

På fredag 29. januar 2016 kl. 02:30:59, skrev Joshua D. Drake <
j...@commandprompt.com >:
On 01/28/2016 05:23 PM, drum.lu...@gmail.com wrote:
 > Hi there!
 >
 > I'm running this command: *(Trying to copy a full DB (2TB) from a
 > hot-standby server to a master (test) server)*
 >
 > |ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
 > --exclude-table-data='junk.*' -—format=custom
 > master_db"|/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp
 > --exit-on-error —-verbose|
 >
 > Then, after 3 GB I got this error:
 >
 > |pg_dump:Dumping the contents
 > oftable"invoices"failed:PQgetResult()failed.pg_dump:Error message
 > fromserver:ERROR:canceling statement due toconflict withrecovery
 > DETAIL:Userwas holding a relation lock fortoo long.pg_dump:The command
 > was:COPY dm.invoices
 > 
(invoice_id,format_version,ts_issue,ts_ack,customer_id,code,tag,account_data,customer_data,invoice_data,invoice_items_data)TOstdout;|
 >
 >     I've tried the pg_dump command even with: "--no-unlogged-table-data"
 >     option, but it was unsuccessful
 >    
 
(http://www.postgresql.org/message-id/e1tutdo-0001hb...@wrigleys.postgresql.org)
 >
 >     DB size: 2 TB
 >
 > How can I solve the problem? What's going on? Thanks
 >

 Back up everything from the master.

 JD
 
Hi.
 
I'm still getting this on standby running 10.1 (both on primary and standby)
 
Isn't this supposed to work? Isn't some of the point of having a hot-standby 
like this to be able to backup from it?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

[Jeff Janes]

On Jan 9, 2018 03:49, "Andreas Joseph Krogh"  wrote:

På fredag 29. januar 2016 kl. 02:30:59, skrev Joshua D. Drake <
j...@commandprompt.com>:

On 01/28/2016 05:23 PM, drum.lu...@gmail.com wrote:
> Hi there!
>
> I'm running this command: *(Trying to copy a full DB (2TB) from a
> hot-standby server to a master (test) server)*
>
> |ssh postgres@myslaveserver "/usr/pgsql-9.2/bin/pg_dump
> --exclude-table-data='junk.*' -—format=custom
> master_db"|/usr/pgsql-9.2/bin/pg_restore --dbname=master_db_temp
> --exit-on-error —-verbose|
>
> Then, after 3 GB I got this error:
>
> |pg_dump:Dumping the contents
> oftable"invoices"failed:PQgetResult()failed.pg_dump:Error message
> fromserver:ERROR:canceling statement due toconflict withrecovery
> DETAIL:Userwas holding a relation lock fortoo long.pg_dump:The command
> was:COPY dm.invoices
> (invoice_id,format_version,ts_issue,ts_ack,customer_id,code,
tag,account_data,customer_data,invoice_data,invoice_items_data)TOstdout;|
>


Hi.

I'm still getting this on standby running 10.1 (both on primary and standby)


Surely you are not getting recovery conflicts on the master.


Isn't this supposed to work?



You can avoid recovery conflicts by setting

max_standby_streaming_delay=-1

On the hot standby.  This might render the hot standby less useful for
other purposes, in which case you might need different hot standbys for
different purposes.

> Isn't some of the point of having a hot-standby like this to be able to
backup from it?

Not for me.  I might use one to dump one or a few tables, but if I wanted a
full backup I'd use pg_basebackup, not pg_dump.

Cheers,

Jeff

Number of rows returned by Sort node

[Vitaliy Garnashevich]

Hi,

How, according to EXPLAIN ANALYZE, the Sort node could return more rows 
as output (rows=767662), than it had for input from its child node 
(rows=135627)?


->  Merge Join   (actual time=1977.388..333626.072 rows=725757 loops=1)
  ->  Index Scan using  (actual time=0.013..312144.441 
rows=49176765 loops=1)

  ->  Sort   (actual time=1977.363..2274.092 rows=767662 loops=1)
    ->  Hash Left Join   (actual time=97.123..1887.956 
rows=135627 loops=1)


(full plan attached, PostgreSQL 9.3)

Regards,
Vitaliy


  QUERY PLAN
   
---
 GroupAggregate  (cost=6051876.28..6062663.17 rows=359563 width=65) (actual 
time=604213.422..614449.378 rows=86325 loops=1)
   ->  Sort  (cost=6051876.28..6052775.19 rows=359563 width=65) (actual 
time=604213.378..609416.622 rows=5563369 loops=1)
 Sort Key: cmdb_program_daily_usage.used_from, 
cmdb_program_daily_usage."user", cmdb_software_product_version.friendly_name, 
cmdb_software_product_version.id
 Sort Method: external merge  Disk: 445928kB
 ->  Hash Join  (cost=197462.19..6018696.01 rows=359563 width=65) 
(actual time=4136.668..533171.915 rows=5563369 loops=1)
   Hash Cond: (cmdb_ci.id = cmdb_ci_computer.id)
   ->  Nested Loop  (cost=194838.34..6011562.23 rows=359563 
width=73) (actual time=3909.509..527042.088 rows=5563369 loops=1)
 ->  Nested Loop  (cost=194837.91..5794445.19 rows=459672 
width=65) (actual time=3909.321..467581.211 rows=5828546 loops=1)
   ->  Hash Left Join  (cost=194837.21..3206507.24 
rows=343355 width=37) (actual time=3909.179..337915.666 rows=725757 loops=1)
 Hash Cond: (cmdb_program_instance.program = 
cmdb_program.id)
 Filter: ((NOT 
cmdb_program.ssi_exclude_from_usage) OR (cmdb_program.ssi_exclude_from_usage IS 
NULL))
 ->  Merge Join  (cost=120277.29..3123792.65 
rows=343355 width=45) (actual time=1977.388..333626.072 rows=725757 loops=1)
   Merge Cond: (cmdb_program_instance.spkg 
= cmdb_ci_spkg.id)
   ->  Index Scan using 
idx_fk_2005379175958 on cmdb_program_instance  (cost=0.57..5178525.28 
rows=88519832 width=24) (actual time=0.013..312144.441 rows=49176765 loops=1)
   ->  Sort  (cost=120276.66..120493.07 
rows=86563 width=37) (actual time=1977.363..2274.092 rows=767662 loops=1)
 Sort Key: cmdb_ci_spkg.id
 Sort Method: quicksort  Memory: 
16740kB
 ->  Hash Left Join  
(cost=2024.59..113177.86 rows=86563 width=37) (actual time=97.123..1887.956 
rows=135627 loops=1)
   Hash Cond: 
(cmdb_ci_spkg.software = cmdb_software_product_version.id)
   ->  Bitmap Heap Scan on 
cmdb_ci_spkg  (cost=2021.27..111984.31 rows=86563 width=16) (actual 
time=96.993..1780.596 rows=135627 loops=1)
 Recheck Cond: 
((software IS NOT NULL) AND (software = 2))
 Filter: ((NOT os) OR 
(os IS NULL))
 ->  Bitmap Index Scan 
on idx_fk_1998473864224  (cost=0.00..1999.63 rows=86957 width=0) (actual 
time=65.665..65.665 rows=136536 loops=1)
   Index Cond: 
((software IS NOT NULL) AND (software = 2))
   ->  Hash  (cost=3.30..3.30 
rows=1 width=29) (actual time=0.088..0.088 rows=1 loops=1)
 Buckets: 1024  
Batches: 1  Memory Usage: 1kB
 ->  Index Scan using 
cmdb_software_product_version_pkey on cmdb_software_product_version  
(cost=0.29..3.30 rows=1 width=29) (actual time=0.068..0.083 rows=1 loops=1)
   Index Cond: (id 
= 2)
 ->  Hash  (cost=59002.74..59002.74 
rows=1244574 width=9) (actual time=1931.213..1931.213 rows=1230216 loops=1)
   Buckets: 131072  Batches: 1  Memory 
Usage: 49257kB
   ->  Seq Scan on cmdb_program  
(cost=0.00..59002.74 rows=1244574 width=9) (

Re: Number of rows returned by Sort node

[Tom Lane]

Vitaliy Garnashevich  writes:
> How, according to EXPLAIN ANALYZE, the Sort node could return more rows 
> as output (rows=767662), than it had for input from its child node 
> (rows=135627)?

Unsurprising when it's the inner input of a merge join --- that reflects
the merge join rescanning parts of the inner input.  It has to do that
whenever the outer input has duplicate keys.

regards, tom lane

Getting started with first user.

[Agnar Renolen]

I have just installed PostGIS (Postgres9.6) on a Debian server using
apt-get.

But I have problems doing anything:

I installed as root, but trying doing things as my local user "me"

me> createuser me
createuser: could not connect to database postgres: FATAL: role "me" does
not exist

Then, trying the same as root, but gettinge the same result.

root> createuser me
createuser: could not connect to database postgres: FATAL: role "root" does
not exist

Then trying with the -U postgres option.

root> createuser -U postgres me
createuser: could not connect to database postgres: FATAL: Peer
authentication failed for user "postgres"

How do I get started?

Re: Getting started with first user.

[Adrian Klaver]

On 01/09/2018 01:48 AM, Agnar Renolen wrote:
I have just installed PostGIS (Postgres9.6) on a Debian server using 
apt-get.


But I have problems doing anything:

I installed as root, but trying doing things as my local user "me"

me> createuser me
createuser: could not connect to database postgres: FATAL: role "me" 
does not exist


Then, trying the same as root, but gettinge the same result.

root> createuser me
createuser: could not connect to database postgres: FATAL: role "root" 
does not exist


Then trying with the -U postgres option.

root> createuser -U postgres me
createuser: could not connect to database postgres: FATAL: Peer 
authentication failed for user "postgres"


How do I get started?



https://wiki.debian.org/PostgreSql#User_access


--
Adrian Klaver
adrian.kla...@aklaver.com

RES: help with generation_series in pg10

[Márcio A . Sepp]

> > how can i have the same in pg10 as i have had in pg 9.x?
> 
> Move the function call to the FROM clause:
> 
> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;

thank you. That is exact what i need.

 
> > I need it to date type to...  if possible.
> 
> There is a generate_series() variant that can return dates (more
> precisely, timestamp with time zone).  But what exactly would you like
> returned?

In the past i use querys like this to generate some timestamp field:
select generate_series (1, 10), generate_series('2018-01-01
10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');


in some case I need to order the timestamp data field and in others I just
need an interval. So, in some moments an random data already help me and in
others it is necessary to have both fields aligned.


thanks

Re: Getting started with first user.

[Jaime Soler]

please try su postgres -c 'createuser -U postgres me' or change auth method
in your pg_hba.conf



2018-01-09 10:48 GMT+01:00 Agnar Renolen :

> I have just installed PostGIS (Postgres9.6) on a Debian server using
> apt-get.
>
> But I have problems doing anything:
>
> I installed as root, but trying doing things as my local user "me"
>
> me> createuser me
> createuser: could not connect to database postgres: FATAL: role "me" does
> not exist
>
> Then, trying the same as root, but gettinge the same result.
>
> root> createuser me
> createuser: could not connect to database postgres: FATAL: role "root"
> does not exist
>
> Then trying with the -U postgres option.
>
> root> createuser -U postgres me
> createuser: could not connect to database postgres: FATAL: Peer
> authentication failed for user "postgres"
>
> How do I get started?
>

Re: Getting started with first user.

[John McKown]

On Tue, Jan 9, 2018 at 3:48 AM, Agnar Renolen 
wrote:

> I have just installed PostGIS (Postgres9.6) on a Debian server using
> apt-get.
>
> But I have problems doing anything:
>
> I installed as root, but trying doing things as my local user "me"
>
> me> createuser me
> createuser: could not connect to database postgres: FATAL: role "me" does
> not exist
>
> Then, trying the same as root, but gettinge the same result.
>
> root> createuser me
> createuser: could not connect to database postgres: FATAL: role "root"
> does not exist
>
> Then trying with the -U postgres option.
>
> root> createuser -U postgres me
> createuser: could not connect to database postgres: FATAL: Peer
> authentication failed for user "postgres"
>
> How do I get started?
>

​It's been a long time for me, but as I recall, I did something like:

me$ sudo su - # change to root
root# su - postgres # change to postgres user
postgres$​ createuser -s me #create "me"
postgres$ exit # go back to root
root# exit # go back to me
me$ createdb me # create my default database

In /var/lib/pgsql/data/pg_hba.conf , I have lines like:

# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 trust






-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown

Re: RES: help with generation_series in pg10

[Alvaro Herrera]

Márcio A. Sepp wrote:

> > There is a generate_series() variant that can return dates (more
> > precisely, timestamp with time zone).  But what exactly would you like
> > returned?
> 
> In the past i use querys like this to generate some timestamp field:
> select generate_series (1, 10), generate_series('2018-01-01
> 10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
> 
> 
> in some case I need to order the timestamp data field and in others I just
> need an interval. So, in some moments an random data already help me and in
> others it is necessary to have both fields aligned.

Maybe something like this

select *
from generate_series(date '2018-01-01 10:00', '2018-01-02 10:00', '10 hours')
   with ordinality;

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Getting started with first user.

[Raymond O'Donnell]

On 09/01/18 09:48, Agnar Renolen wrote:

Then trying with the -U postgres option.

root> createuser -U postgres me
createuser: could not connect to database postgres: FATAL: Peer 
authentication failed for user "postgres"


You need to specify the host as well, to force a TCP/IP connection:

  createuser -U postgres -h localhost me

The default pg_hba.conf on Debian uses a Unix socket and peer 
authentication, hence the error you're seeing.


Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: Number of rows returned by Sort node

[Vitaliy Garnashevich]

Thanks, Tom!

I've found some more information in PostgreSQL source code:

For example, consider the following relations:

    outer: (0 ^1 1 2 5 5 5 6 6 7)    current tuple: 1
    inner: (1 ^3 5 5 5 5 6)  current tuple: 3

...suppose that the executor has
just joined the first outer "5" with the last inner "5". The
next step is of course to join the second outer "5" with all
the inner "5's". This requires repositioning the inner "cursor"
to point at the first inner "5". This is done by "marking" the
first inner 5 so we can restore the "cursor" to it before joining
with the second outer 5. The access method interface provides
routines to mark and restore to a tuple.


Essential operation of the merge join algorithm is as follows:

Join {
    get initial outer and inner tuples INITIALIZE
    do forever {
    while (outer != inner) { SKIP_TEST
    if (outer < inner)
    advance outer SKIPOUTER_ADVANCE
    else
    advance inner SKIPINNER_ADVANCE
    }
    mark inner position SKIP_TEST
    do forever {
    while (outer == inner) {
    join tuples JOINTUPLES
    advance inner position NEXTINNER
    }
    advance outer position NEXTOUTER
    if (outer == mark) TESTOUTER
    restore inner position to mark TESTOUTER
    else
    break    // return to top of outer loop
    }
    }
}


So, even that the Sort node was returning unique values, the join 
algorithm still had to do a lot of mark/restore, which were reflected in 
EXPLAIN's row count. Anyway, that's clear now.


Regards,
Vitaliy

On 2018-01-09 17:23, Tom Lane wrote:

Vitaliy Garnashevich  writes:

How, according to EXPLAIN ANALYZE, the Sort node could return more rows
as output (rows=767662), than it had for input from its child node
(rows=135627)?

Unsurprising when it's the inner input of a merge join --- that reflects
the merge join rescanning parts of the inner input.  It has to do that
whenever the outer input has duplicate keys.

regards, tom lane

Re: How Many Partitions are Good Performing

[Andrew Staller]

This is the blog post that Rakesh referenced:
https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5

Please note, this analysis is done in the context of working with
time-series data, where 1000s of chunks is not uncommon because of the
append-mostly nature of the workload.

On Mon, Jan 8, 2018 at 6:54 PM, Rakesh Kumar 
wrote:

>
>  You should have read carefully what I wrote.  1000 is not an upper
> limit.  1000 partition is the number after which performance starts
> dropping .
>
> There is a blog in www.timescale.com which also highlights the same.
>
> Sent: Monday, January 08, 2018 at 6:20 PM
> From: "Kumar, Virendra" 
> To: "pgsql-gene...@postgresql.org" 
> Subject: How Many Partitions are Good Performing
>
> Can somebody tell us how many partitions are good number without impacting
> the performance. We are hearing around a thousand, is that a limit. Do we
> have plan to increase the number of partitions for a table. We would
> appreciate if somebody can help us with this?
>
> Regards,
> Virendra
>
>
> 
> This message is intended only for the use of the addressee and may contain
> information that is PRIVILEGED AND CONFIDENTIAL.
>
> If you are not the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please erase all copies of the
> message
> and its attachments and notify the sender immediately. Thank you.
>
>


-- 
TimescaleDB* | *Growth & Developer Evangelism
c: 908.581.9509

335 Madison Ave.
New York, NY 10017
http://www.timescale.com/
https://github.com/timescale/timescaledb

Re: Supartitions in PGSQL 10

[Andrew Staller]

Virenda, is this time-series data you're working with?

On Mon, Jan 8, 2018 at 4:12 PM, Kumar, Virendra 
wrote:

> Team,
>
>
>
> Can you please let us know if Sub-partitions are supported in PGSQL
> (declarative partitions) 10.1. If yes can it be list-list partitions. We
> have a situation where a table is very big having around 2 billion rows and
> is growing. We want to use partitions but not sure if sub-partitions are
> available. The partition key we are looking for is having around 8000
> different values so it will be 8000 partitions and I think that number is
> really too big number of partitions. For your information RDBMS is not yet
> Postgres, we are evaluating it to see if it can support. Please suggest.
>
>
>
> Regards,
>
> Virendra
>
> --
>
> This message is intended only for the use of the addressee and may contain
> information that is PRIVILEGED AND CONFIDENTIAL.
>
> If you are not the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please erase all copies of the
> message
> and its attachments and notify the sender immediately. Thank you.
>



-- 
TimescaleDB* | *Growth & Developer Evangelism
c: 908.581.9509

335 Madison Ave.
New York, NY 10017
http://www.timescale.com/
https://github.com/timescale/timescaledb

RE: How Many Partitions are Good Performing

[Kumar, Virendra]

Thank you Rakesh and Andrew!

We will not be doing time scaling but we have list of value based of which we 
will be partitioning the table and list is something around 7500 now.
For short term we are thinking of putting around a thousand partitions and when 
PG11 releases we will go for each value a partition.

Regards,
Virendra

From: Andrew Staller [mailto:and...@timescale.com]
Sent: Tuesday, January 09, 2018 12:15 PM
To: Rakesh Kumar
Cc: Kumar, Virendra; pgsql-gene...@postgresql.org
Subject: Re: How Many Partitions are Good Performing

This is the blog post that Rakesh referenced:
https://blog.timescale.com/time-series-data-postgresql-10-vs-timescaledb-816ee808bac5

Please note, this analysis is done in the context of working with time-series 
data, where 1000s of chunks is not uncommon because of the append-mostly nature 
of the workload.

On Mon, Jan 8, 2018 at 6:54 PM, Rakesh Kumar 
mailto:rakeshkumar...@mail.com>> wrote:

 You should have read carefully what I wrote.  1000 is not an upper limit.  
1000 partition is the number after which performance starts dropping .

There is a blog in www.timescale.com which also 
highlights the same.

Sent: Monday, January 08, 2018 at 6:20 PM
From: "Kumar, Virendra" 
mailto:virendra.ku...@guycarp.com>>
To: "pgsql-gene...@postgresql.org" 
mailto:pgsql-gene...@postgresql.org>>
Subject: How Many Partitions are Good Performing

Can somebody tell us how many partitions are good number without impacting the 
performance. We are hearing around a thousand, is that a limit. Do we have plan 
to increase the number of partitions for a table. We would appreciate if 
somebody can help us with this?

Regards,
Virendra



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.



--
TimescaleDB | Growth & Developer Evangelism
c: 908.581.9509

335 Madison Ave.
New York, NY 10017
http://www.timescale.com/
https://github.com/timescale/timescaledb




This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Re: How Many Partitions are Good Performing

[Vincenzo Romano]

2018-01-09 18:15 GMT+01:00 Andrew Staller :

> This is the blog post that Rakesh referenced:
> https://blog.timescale.com/time-series-data-postgresql-
> 10-vs-timescaledb-816ee808bac5
>
> Please note, this analysis is done in the context of working with
> time-series data, where 1000s of chunks is not uncommon because of the
> append-mostly nature of the workload.
>
> On Mon, Jan 8, 2018 at 6:54 PM, Rakesh Kumar 
> wrote:
>
>>
>>  You should have read carefully what I wrote.  1000 is not an upper
>> limit.  1000 partition is the number after which performance starts
>> dropping .
>>
>> There is a blog in www.timescale.com which also highlights the same.
>>
>> Sent: Monday, January 08, 2018 at 6:20 PM
>> From: "Kumar, Virendra" 
>> To: "pgsql-gene...@postgresql.org" 
>> Subject: How Many Partitions are Good Performing
>>
>> Can somebody tell us how many partitions are good number without
>> impacting the performance. We are hearing around a thousand, is that a
>> limit. Do we have plan to increase the number of partitions for a table. We
>> would appreciate if somebody can help us with this?
>>
>> Regards,
>> Virendra
>>
>>
>> 
>> This message is intended only for the use of the addressee and may contain
>> information that is PRIVILEGED AND CONFIDENTIAL.
>>
>> If you are not the intended recipient, you are hereby notified that any
>> dissemination of this communication is strictly prohibited. If you have
>> received this communication in error, please erase all copies of the
>> message
>> and its attachments and notify the sender immediately. Thank you.
>>
>>
>
>
> --
> TimescaleDB* | *Growth & Developer Evangelism
> c: 908.581.9509
>
> 335 Madison Ave.
> 
> New York, NY 10017
> 
> http://www.timescale.com/
> https://github.com/timescale/timescaledb
>


The data about the query performances would have shed more light on the
situation.
Unluckily there's none. Weird!

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

data-checksums

[Rakesh Kumar]

--data-checksums
Use checksums on data pages to help detect corruption by the I/O system that 
would otherwise be silent. Enabling checksums may incur a noticeable 
performance penalty. This option can only be set during initialization, and 
cannot be changed later. If set, checksums are calculated for all objects, in 
all databases.


If I understand it correctly, the performance penalty is when the blocks are 
written to the disk by the background writer and/or during checkpoint.  Given 
that the process is async and application does not wait on it, is the 
performance penalty really a big concern.

thanks

Re: data-checksums

[Stephen Frost]

Greetings,

* Rakesh Kumar (rakeshkumar...@mail.com) wrote:
> --data-checksums
> Use checksums on data pages to help detect corruption by the I/O system that 
> would otherwise be silent. Enabling checksums may incur a noticeable 
> performance penalty. This option can only be set during initialization, and 
> cannot be changed later. If set, checksums are calculated for all objects, in 
> all databases.
> 
> 
> If I understand it correctly, the performance penalty is when the blocks are 
> written to the disk by the background writer and/or during checkpoint.  Given 
> that the process is async and application does not wait on it, is the 
> performance penalty really a big concern.

There's also a hit when pages are read back in, since we need to
calculate the checksum and verify it hasn't changed.

That said, imv anyway, the performance hit is small and having checksums
is well worth it.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: data-checksums

[Rakesh Kumar]

> That said, imv anyway, the performance hit is small and having checksums
> is well worth it.

I also would like to believe that the hit is small, but when PG official 
document writes "noticeable performance penalty", it becomes difficult to 
convince management that the hit is small :-)

Re: data-checksums

[Andres Freund]

On 2018-01-09 18:58:41 +0100, Rakesh Kumar wrote:
> 
> > That said, imv anyway, the performance hit is small and having checksums
> > is well worth it.
> 
> I also would like to believe that the hit is small, but when PG
> official document writes "noticeable performance penalty", it becomes
> difficult to convince management that the hit is small :-)

noticeable != huge.

- Andres

Re: data-checksums

[Alvaro Herrera]

Rakesh Kumar wrote:
> 
> > That said, imv anyway, the performance hit is small and having
> > checksums is well worth it.
> 
> I also would like to believe that the hit is small, but when PG
> official document writes "noticeable performance penalty", it becomes
> difficult to convince management that the hit is small :-)

Why believe, when you can measure?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: data-checksums

[Rakesh Kumar]

Hello Mr. Pedantic,

> noticeable != huge.

and noticeable != small/negligible either, at least from English point of view.

Re: data-checksums

[Rakesh Kumar]

> > I also would like to believe that the hit is small, but when PG
> > official document writes "noticeable performance penalty", it becomes
> > difficult to convince management that the hit is small :-)
> 
> Why believe, when you can measure?

yup doing that.  But I still feel that PG documentation should stay away from 
such scare mongering.  Or did the lawyers write that :)

Re: data-checksums

[Jeff Janes]

On Tue, Jan 9, 2018 at 12:58 PM, Rakesh Kumar 
wrote:

>
> > That said, imv anyway, the performance hit is small and having checksums
> > is well worth it.
>
> I also would like to believe that the hit is small, but when PG official
> document writes "noticeable performance penalty", it becomes difficult to
> convince management that the hit is small :-)
>
>
Why ask us questions if you won't believe our answers?

Noticeable means it probably isn't important for most real-world cases, but
if you work at it you can probably detect it.

Cheers,

Jeff

Re: Getting started with first user.

[Agnar Renolen]

Found out a way out.

Logged in as *root*, and created a password for the *postgres* user.

Then logged in as *postgres* and and ran "createuser -s me".

Then logging in as *me*, ran "createdb"

Agnar

On 09/01/2018 17:13, Adrian Klaver wrote:

On 01/09/2018 01:48 AM, Agnar Renolen wrote:
I have just installed PostGIS (Postgres9.6) on a Debian server using 
apt-get.


But I have problems doing anything:

I installed as root, but trying doing things as my local user "me"

me> createuser me
createuser: could not connect to database postgres: FATAL: role "me" 
does not exist


Then, trying the same as root, but gettinge the same result.

root> createuser me
createuser: could not connect to database postgres: FATAL: role 
"root" does not exist


Then trying with the -U postgres option.

root> createuser -U postgres me
createuser: could not connect to database postgres: FATAL: Peer 
authentication failed for user "postgres"


How do I get started?



https://wiki.debian.org/PostgreSql#User_access

Hi i like to unscribe me, sorry for the incovenient.

[Moises Lima dos Anjos]

Em ter, 9 de jan de 2018 às 16:20, Jeff Janes 
escreveu:

> On Tue, Jan 9, 2018 at 12:58 PM, Rakesh Kumar 
> wrote:
>
>>
>> > That said, imv anyway, the performance hit is small and having checksums
>> > is well worth it.
>>
>> I also would like to believe that the hit is small, but when PG official
>> document writes "noticeable performance penalty", it becomes difficult to
>> convince management that the hit is small :-)
>>
>>
> Why ask us questions if you won't believe our answers?
>
> Noticeable means it probably isn't important for most real-world cases,
> but if you work at it you can probably detect it.
>
> Cheers,
>
> Jeff
>

Re: data-checksums

[George Neuner]

"On Tue, 9 Jan 2018 20:02:37 +0100, "Rakesh Kumar"
 wrote:

>Hello Mr. Pedantic,
>
>> noticeable != huge.
>
>and noticeable != small/negligible either, at least from English
>point of view.

small != negligible. 

The word "noticable" does not imply any particular magnitude of event.
It means only that  can be observed.

There is no canon technical usage.  In layman's use, "noticable" often
*does* imply that an effect is small, and that one might not see it if
not looking specifically for it.


Unfortunately, English is a slippery language.  Perhaps technical
documents should be written in Sumerian.

YMMV,
George

Re: data-checksums

[Andres Freund]

On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote:
> 
> > > I also would like to believe that the hit is small, but when PG
> > > official document writes "noticeable performance penalty", it becomes
> > > difficult to convince management that the hit is small :-)
> > 
> > Why believe, when you can measure?
> 
> yup doing that.  But I still feel that PG documentation should stay
> away from such scare mongering.  Or did the lawyers write that :)

So we should rather lie about it having a potential for performance
impact? Who'd be helped by that?

Greetings,

Andres Freund

Re: data-checksums

[Joshua D. Drake]

On 01/09/2018 12:22 PM, Andres Freund wrote:

On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote:

I also would like to believe that the hit is small, but when PG
official document writes "noticeable performance penalty", it becomes
difficult to convince management that the hit is small :-)

Why believe, when you can measure?

yup doing that.  But I still feel that PG documentation should stay
away from such scare mongering.  Or did the lawyers write that :)

So we should rather lie about it having a potential for performance
impact? Who'd be helped by that?


It isn't a lie, it depends on the workload and hardware. Adjusting the 
documentation to say something like the following probably isn't a bad idea:


The use of the data checksum feature may incur a performance penalty. 
However, this does depend on your particular workload and provisioned 
hardware. It is wise to test the feature based on your specific 
requirements.




JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Sv: Re: data-checksums

[Andreas Joseph Krogh]

På tirsdag 09. januar 2018 kl. 21:37:16, skrev Joshua D. Drake <
j...@commandprompt.com >:
On 01/09/2018 12:22 PM, Andres Freund wrote:
 > On 2018-01-09 20:04:04 +0100, Rakesh Kumar wrote:
  I also would like to believe that the hit is small, but when PG
  official document writes "noticeable performance penalty", it becomes
  difficult to convince management that the hit is small :-)
 >>> Why believe, when you can measure?
 >> yup doing that.  But I still feel that PG documentation should stay
 >> away from such scare mongering.  Or did the lawyers write that :)
 > So we should rather lie about it having a potential for performance
 > impact? Who'd be helped by that?

 It isn't a lie, it depends on the workload and hardware. Adjusting the
 documentation to say something like the following probably isn't a bad idea:

 The use of the data checksum feature may incur a performance penalty.
 However, this does depend on your particular workload and provisioned
 hardware. It is wise to test the feature based on your specific
 requirements.
 
Does PG use HW-accellerated crc if CPU supports it[1]?
 
[1] https://en.wikipedia.org/wiki/SSE4
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: data-checksums

[Andres Freund]

Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD compatible.

The checksum computations have some impact, but if there's bigger impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

Andres Freund

Sv: Re: Sv: Re: data-checksums

[Andreas Joseph Krogh]

På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund mailto:and...@anarazel.de>>:
Hi,

 On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
 > Does PG use HW-accellerated crc if CPU supports it[1]?

 Yes we do, for WAL checksums. The page checksums are a different
 algorithm though, one which has the advantage of being SIMD compatible.

 The checksum computations have some impact, but if there's bigger impact
 it's much more likely to be related to the fact that some hint bit
 writes to a page now needs to be WAL logged.
 
But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?
 
So, if these CRCs all are HW-accelerated the penalty chould be next to 
neglishable?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: Sv: Re: data-checksums

[Rob Sargent]

On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund 
mailto:and...@anarazel.de>>:


Hi,

On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
> Does PG use HW-accellerated crc if CPU supports it[1]?

Yes we do, for WAL checksums. The page checksums are a different
algorithm though, one which has the advantage of being SIMD
compatible.

The checksum computations have some impact, but if there's bigger
impact
it's much more likely to be related to the fact that some hint bit
writes to a page now needs to be WAL logged.

But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?
So, if these CRCs all are HW-accelerated the penalty chould be next to 
neglishable?



Leading directly back to JD's proposed documentation update.

Sv: Re: Sv: Re: Sv: Re: data-checksums

[Andreas Joseph Krogh]

På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent <
robjsarg...@gmail.com >:
 

   On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <
and...@anarazel.de >:
Hi,

 On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
 > Does PG use HW-accellerated crc if CPU supports it[1]?

 Yes we do, for WAL checksums. The page checksums are a different
 algorithm though, one which has the advantage of being SIMD compatible.

 The checksum computations have some impact, but if there's bigger impact
 it's much more likely to be related to the fact that some hint bit
 writes to a page now needs to be WAL logged.
 
But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?
 
So, if these CRCs all are HW-accelerated the penalty chould be next to 
neglishable?
 
 Leading directly back to JD's proposed documentation update.  
Ducumentation mentioning "Depends on hardware" and "you should test" isn't 
really helpfull imo. as it is too general and can be said about just about 
anything.
 
Being explicit about usage of HW-accelerated (in CPU) instructions in 
algorithms used is helpfull, and might help users choose enabling 
data-checksums.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

[Andres Freund]

On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent <
> robjsarg...@gmail.com >:
>  
> 
>    On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
> På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <
> and...@anarazel.de >:
> Hi,
> 
>  On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
>  > Does PG use HW-accellerated crc if CPU supports it[1]?
> 
>  Yes we do, for WAL checksums. The page checksums are a different
>  algorithm though, one which has the advantage of being SIMD compatible.
> 
>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.
>  
> But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

Sure. Still measurable, but even if weren't, it's irrelevant given my
primary point:

>  The checksum computations have some impact, but if there's bigger impact
>  it's much more likely to be related to the fact that some hint bit
>  writes to a page now needs to be WAL logged.

which isn't mitigated by SIMD / hardware CRC / whatnot.

Greetings,

Andres Freund

Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

[Andreas Joseph Krogh]

På onsdag 10. januar 2018 kl. 01:01:26, skrev Andres Freund mailto:and...@anarazel.de>>:
On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
 > På tirsdag 09. januar 2018 kl. 23:42:45, skrev Rob Sargent <
 > robjsarg...@gmail.com >:
 >  
 >
 >    On 01/09/2018 03:30 PM, Andreas Joseph Krogh wrote:
 > På tirsdag 09. januar 2018 kl. 23:06:06, skrev Andres Freund <
 > and...@anarazel.de >:
 > Hi,
 >
 >  On 2018-01-09 21:47:17 +0100, Andreas Joseph Krogh wrote:
 >  > Does PG use HW-accellerated crc if CPU supports it[1]?
 >
 >  Yes we do, for WAL checksums. The page checksums are a different
 >  algorithm though, one which has the advantage of being SIMD compatible.
 >
 >  The checksum computations have some impact, but if there's bigger impact
 >  it's much more likely to be related to the fact that some hint bit
 >  writes to a page now needs to be WAL logged.
 >  
 > But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?

 Sure. Still measurable, but even if weren't, it's irrelevant given my
 primary point:

 >  The checksum computations have some impact, but if there's bigger impact
 >  it's much more likely to be related to the fact that some hint bit
 >  writes to a page now needs to be WAL logged.

 which isn't mitigated by SIMD / hardware CRC / whatnot.
 
Aha, so enabling CRC causes hint-bits to be written causing extra WAL-logging, 
which woudn't be the case without CRC enabled?
Thanks for pointing that out.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

[Stephen Frost]

Greetings,

* Andreas Joseph Krogh (andr...@visena.com) wrote:
> Aha, so enabling CRC causes hint-bits to be written causing extra 
> WAL-logging, 
> which woudn't be the case without CRC enabled?
> Thanks for pointing that out.

Yes, having checksums enabled forces logging of hint bits.  You can
enable wal_log_hints independently too, without having checksums, to see
what kind of an impact it'll have on your environment.

A useful documentation update might be:

---
With checksums enabled, wal_log_hints 
will be enabled and each page read or write will involve calculating the
checksum for the page.
---

I'd probably just replace the "Enabling checksums may incur a noticeable
performance penalty" with the above, as it should be clear that doing
more work implies an impact on performance and that avoids the whole
question of trying to characterize in a general way something that can't
be generalized (as it's workload dependent).

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

[Andres Freund]

On 2018-01-10 01:31:58 +0100, Andreas Joseph Krogh wrote:
> På onsdag 10. januar 2018 kl. 01:01:26, skrev Andres Freund 
>  >:
> On 2018-01-10 00:25:08 +0100, Andreas Joseph Krogh wrote:
>  > But SIMD-instructions are also HW-accellerated by modern CPUs IIUC?
> 
>  Sure. Still measurable, but even if weren't, it's irrelevant given my
>  primary point:
> 
>  >  The checksum computations have some impact, but if there's bigger impact
>  >  it's much more likely to be related to the fact that some hint bit
>  >  writes to a page now needs to be WAL logged.
> 
>  which isn't mitigated by SIMD / hardware CRC / whatnot.
>  
> Aha, so enabling CRC causes hint-bits to be written causing extra 
> WAL-logging, 
> which woudn't be the case without CRC enabled?
> Thanks for pointing that out.

Well, enabling checksums enables that. CRCs don't play a role for data
checksums. CRCs are a specific class of checksums, a specific one of
those is used in our WAL logging, but the data checksum algorithm isn't
in that class.

Greetings,

Andres Freund