Re: [GENERAL] Logging at schema level

2017-07-20 Thread Andreas Kretschmer
On 21 July 2017 07:10:42 GMT+02:00, Nikhil wrote: >Hello, > >I am using postgresql schema feature for multi-tenancy. can we get >postgresql logs at schema level. Currently it is for the whole database >server (pg_log) > What do you want to achieve? Logging of data-changes

Re: [GENERAL] Streaming Replication archive_command is really needed?

2017-07-20 Thread Achilleas Mantzios
On 20/07/2017 23:07, Leonardo M. Ramé wrote: El 20/07/17 a las 16:57, Andreas Kretschmer escribió: On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" wrote: Hi, I wonder if archive_mode=on and archive_command parameters in postgresql.conf are really needed for

Re: [GENERAL] Logging at schema level

2017-07-20 Thread Michael Paquier
On Fri, Jul 21, 2017 at 7:10 AM, Nikhil wrote: > I am using postgresql schema feature for multi-tenancy. can we get > postgresql logs at schema level. Currently it is for the whole database > server (pg_log) Not directly. All the log-related parameters can be controlled

Re: [GENERAL] Logging at schema level

2017-07-20 Thread John R Pierce
On 7/20/2017 10:10 PM, Nikhil wrote: I am using postgresql schema feature for multi-tenancy. can we get postgresql logs at schema level. Currently it is for the whole database server (pg_log) if you /were/ able to split the logs by schema, and a query touched multiple schemas, then where

[GENERAL] Logging at schema level

2017-07-20 Thread Nikhil
Hello, I am using postgresql schema feature for multi-tenancy. can we get postgresql logs at schema level. Currently it is for the whole database server (pg_log) Best Regards, Nikhil

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Tom Lane
John R Pierce writes: > On 7/20/2017 8:40 PM, Tom Lane wrote: >> Hm, we need to update that text for the new 2-part version numbering >> scheme, don't we? > will 10 return like 100100 if its 10.1, or 11 ? The latter. The two middle digits will be zeroes henceforth,

Re: [GENERAL] Backward compatibility

2017-07-20 Thread John R Pierce
On 7/20/2017 8:40 PM, Tom Lane wrote: Applications might use this function to determine the version of the database server they are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Tom Lane
John R Pierce writes: > odds are pretty good that... > |PQserverVersion| > Returns an integer representing the backend version. > int PQserverVersion(const PGconn *conn); > Actually invokes `show server_version_num;' Just for the record, it doesn't invoke that;

Re: [GENERAL] Backward compatibility

2017-07-20 Thread John R Pierce
On 7/20/2017 7:57 PM, David G. Johnston wrote: Actually, The docs do cover how to do this directly in libpq. odds are pretty good that... |PQserverVersion| Returns an integer representing the backend version. int PQserverVersion(const PGconn *conn); Applications

Re: [GENERAL] Backward compatibility

2017-07-20 Thread John R Pierce
On 7/20/2017 7:46 PM, Igor Korot wrote: ALso, I presume there is no special libpg function, right? libpq would only be able to return the libpq version, which might not be the same as the server version. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing

Re: [GENERAL] Backward compatibility

2017-07-20 Thread David G. Johnston
On Thursday, July 20, 2017, David G. Johnston wrote: > On Thursday, July 20, 2017, Igor Korot > wrote: > >> Hi, David, >> >> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston >>

Re: [GENERAL] Backward compatibility

2017-07-20 Thread David G. Johnston
On Thursday, July 20, 2017, Igor Korot wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > > wrote: > > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot > wrote: > > >> Is there

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Igor Korot
ALso, I presume there is no special libpg function, right? Thank you. On Thu, Jul 20, 2017 at 10:44 PM, Igor Korot wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > wrote: >> On Thu, Jul 20, 2017 at 7:13 PM, Igor

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Igor Korot
Hi, David, On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston wrote: > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: >> >> Hi, ALL, >> According to the documentation PostgreSQL 9.6 (latest) supports >> >> CREATE INDEX IF NOT EXIST >> >>

Re: [GENERAL] Backward compatibility

2017-07-20 Thread David G. Johnston
On Thu, Jul 20, 2017 at 7:23 PM, Igor Korot wrote: > On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer > wrote: > > > >>Is there a query or a libpg function which can return the version of > >>the server I'm running? > > > Select version(); > >

Re: [GENERAL] Backward compatibility

2017-07-20 Thread David G. Johnston
On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: > Hi, ALL, > According to the documentation PostgreSQL 9.6 (latest) supports > > CREATE INDEX IF NOT EXIST > > However, the version 9.4 and below supports only > > CREATE INDEX. > > Is there a query or a libpg function which

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Igor Korot
Hi, guys, On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer wrote: > On 21 July 2017 04:13:47 GMT+02:00, Igor Korot wrote: >>Hi, ALL, >>According to the documentation PostgreSQL 9.6 (latest) supports >> >>CREATE INDEX IF NOT EXIST >> >>However,

Re: [GENERAL] Backward compatibility

2017-07-20 Thread Andreas Kretschmer
On 21 July 2017 04:13:47 GMT+02:00, Igor Korot wrote: >Hi, ALL, >According to the documentation PostgreSQL 9.6 (latest) supports > >CREATE INDEX IF NOT EXIST > >However, the version 9.4 and below supports only > >CREATE INDEX. > >Is there a query or a libpg function which can

[GENERAL] Backward compatibility

2017-07-20 Thread Igor Korot
Hi, ALL, According to the documentation PostgreSQL 9.6 (latest) supports CREATE INDEX IF NOT EXIST However, the version 9.4 and below supports only CREATE INDEX. Is there a query or a libpg function which can return the version of the server I'm running? And in the latter case - is there a

Re: [GENERAL] Bug in postgres 9.6.2?

2017-07-20 Thread Tom Lane
greigwise writes: > I do still have the 9.6.2 instance. > Here is the explain analyze results from the 9.6.2 instance: Oh ... you have parallel query enabled, eh? In that case I'm less surprised, there were multiple parallel-query fixes since 9.6.2. Although those still

Re: [GENERAL] Bug in postgres 9.6.2?

2017-07-20 Thread greigwise
I do still have the 9.6.2 instance. Here is the explain analyze results from the 9.6.2 instance: Aggregate (cost=764612.56..764612.57 rows=1 width=8) (actual time=695.166..695.166 rows=1 loops=1) CTE test -> Nested Loop (cost=3345.90..757594.63 rows=311908 width=4) (actual

Re: [GENERAL] Bug in postgres 9.6.2?

2017-07-20 Thread Tom Lane
greigwise writes: > So, I have this query with nothing non-deterministic in it, yet I can run it > multiple times and get different results in postgres 9.6.2: > ... > Is it possible that this is a bug in 9.6.2 that was fixed in 9.6.3? Hard to tell without more information

Re: [GENERAL] Streaming Replication archive_command is really needed?

2017-07-20 Thread Leonardo M . Ramé
El 20/07/17 a las 16:57, Andreas Kretschmer escribió: On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" wrote: Hi, I wonder if archive_mode=on and archive_command parameters in postgresql.conf are really needed for streaming replication between two servers

Re: [GENERAL] Streaming Replication archive_command is really needed?

2017-07-20 Thread Andreas Kretschmer
On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé" wrote: >Hi, I wonder if archive_mode=on and archive_command parameters in >postgresql.conf are really needed for streaming replication between two > >servers (master-slave). > >Regards, No. Andreas -- 2ndQuadrant -

[GENERAL] Streaming Replication archive_command is really needed?

2017-07-20 Thread Leonardo M . Ramé
Hi, I wonder if archive_mode=on and archive_command parameters in postgresql.conf are really needed for streaming replication between two servers (master-slave). Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924

Re: [GENERAL] hash join performance question

2017-07-20 Thread armand pirvu
> On Jul 18, 2017, at 10:30 PM, Tom Lane wrote: > > armand pirvu writes: >> testdb3=# explain analyze SELECT a.company_id FROM csischema.dim_company a, >> woc.dim_company b >> testdb3-# WHERE a.company_id = b.company_id; >>

[GENERAL] Bug in postgres 9.6.2?

2017-07-20 Thread greigwise
So, I have this query with nothing non-deterministic in it, yet I can run it multiple times and get different results in postgres 9.6.2: with test as ( select g.id2 from staging a join dim_1 e on e.id1 = a.id1 and a.created_at >= e.effective_at and a.created_at < e.expired_at join dim_2 g on

Re: [GENERAL] ~/.psqlrc file is ignored [solved: $HOME/.psqlrc]

2017-07-20 Thread vstuart
~/.bashrc alias: alias pg='echo " [sudo -u postgres -i]" && sudo -u postgres -i' -- View this message in context: http://www.postgresql-archive.org/psqlrc-file-is-ignored-tp5971773p5972153.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via

Re: [GENERAL] ~/.psqlrc file is ignored [solved: $HOME/.psqlrc]

2017-07-20 Thread David G. Johnston
On Thu, Jul 20, 2017 at 8:37 AM, vstuart wrote: > ​​ > [victoria@victoria ~]$ pg > [sudo -u postgres -i] > [sudo] password for victoria: > ​Just curious, what is going on here? David J. ​

Re: [GENERAL] ~/.psqlrc file is ignored [solved: $HOME/.psqlrc]

2017-07-20 Thread vstuart
'Solution,' for anyone else encountering this issue (Arch Linux PostgreSQL install ...). TLDR: symlink ~/.psqlrc to postgres $HOME/.psqlrc - [victoria@victoria ~]$ echo $HOME /home/victoria [victoria@victoria ~]$ ls -la .psq*

Re: [GENERAL] _page_cost parameter with values < 1

2017-07-20 Thread Tom Lane
Thomas Kellerer writes: > recently I have seen a Postgres configuration with the following values: > seq_page_cost = 0.5 > random_page_cost = 0.6 > Is there any advantage (or maybe disadvantage) compared to using e.g. 1.0 and > 1.2? That reduces these costs relative to

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

2017-07-20 Thread Tom Lane
"David G. Johnston" writes: > On Wed, Jul 19, 2017 at 8:53 PM, Guyren Howe wrote: >> Thanks. Seeking greater understanding, why is json_agg(*) not equivalent? > ​Are you referring to the fact that ​this provokes an error? > "select json_agg(*) from

[GENERAL] _page_cost parameter with values < 1

2017-07-20 Thread Thomas Kellerer
Hello, recently I have seen a Postgres configuration with the following values: seq_page_cost = 0.5 random_page_cost = 0.6 Is there any advantage (or maybe disadvantage) compared to using e.g. 1.0 and 1.2? Regards Thomas -- Sent via pgsql-general mailing list

Re: [GENERAL] Planner statistics usage for composite type

2017-07-20 Thread Dmitry Lazurkin
On 07/19/2017 06:37 PM, Tom Lane wrote: > Dmitry Lazurkin writes: >> I am trying to find workaround for cross-column statistics. >> ... >> Worn estimate. Planner doesn't use statistics. In code I see usage of >> function scalargtsel which returns default selectivity because >>