Re: [GENERAL] initdb createuser commands

On 10/28/2016 4:55 PM, Samuel Williams wrote: Even if maintaining the old commands, they could print out to stderr "This command is deprecated and now wraps `pg_ctl init`. Please use that command in the future". except its the other way around. pg_ctl initdb invokes initdb, not the other

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 2:17 PM, Guyren Howe wrote: > On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > > On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > > ​On its face the statement "DISTINCT ON removes results" is

Re: [GENERAL] initdb createuser commands

On 10/28/2016 04:55 PM, Samuel Williams wrote: as these commands have been around for like 20 years, its kinda late to change them I understand your position. For new documentation, it should be better to say `pg_ctl init` rather than `initdb`. I think that would guide users in the right

Re: [GENERAL] initdb createuser commands

> as these commands have been around for like 20 years, its kinda late to > change them I understand your position. For new documentation, it should be better to say `pg_ctl init` rather than `initdb`. I think that would guide users in the right direction from the start. Even if maintaining the

Re: [GENERAL] initdb createuser commands

On 10/28/2016 04:31 PM, Samuel Williams wrote: Hello, Perhaps I'm missing something. However, it seems to me that there are several "generically" named commands, e.g. initdb, createuesr which come as part of postgresql. In my mind, these commands are sufficiently generic that they might clash

Re: [GENERAL] initdb createuser commands

On 10/28/2016 4:31 PM, Samuel Williams wrote: Just wondering as the naming of these commands seems overly generic and for a new user it's hard to know what commands are available since there is no common prefix (e.g. pg_) for these commands. as these commands have been around for like 20

[GENERAL] initdb createuser commands

Hello, Perhaps I'm missing something. However, it seems to me that there are several "generically" named commands, e.g. initdb, createuesr which come as part of postgresql. In my mind, these commands are sufficiently generic that they might clash with other commands. It's also not obvious they

Re: [GENERAL] SELECT DISTINCT ON removes results

> On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: > > On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe > wrote: > Using 9.5, this query: Unless I'm missing something, this ought to be impossible. Two queries

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 14:15 , Guyren Howe wrote: > > On Oct 28, 2016, at 13:50 , David G. Johnston > wrote: >> >> ​On its face the statement "DISTINCT ON removes results" is not at all >> surprising given its

Re: [GENERAL] SELECT DISTINCT ON removes results

On Oct 28, 2016, at 13:50 , David G. Johnston wrote: > > ​On its face the statement "DISTINCT ON removes results" is not at all > surprising given its definition. What is surprising is that it removes *all* results…

Re: [GENERAL] SELECT DISTINCT ON removes results

On Fri, Oct 28, 2016 at 1:39 PM, Guyren Howe wrote: > Using 9.5, this query: > > SELECT o.id, >a.number AS awb > FROM pt.orders o > LEFT JOIN ( > SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, > ','::text))) >

[GENERAL] SELECT DISTINCT ON removes results

Using 9.5, this query: SELECT o.id, a.number AS awb FROM pt.orders o LEFT JOIN ( SELECT DISTINCT ON ((string_agg(air_way_bills.number::text, ','::text))) string_agg(air_way_bills.number::text, ','::text) AS number, air_way_bills.order_id

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 2:54 PM, Kim Rose Carlsen wrote: >> > On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: > >> > Hi >> > >> > I was wondering if there is a way to hint that two columns in two >> > different >> > tables IS NOT DISTINCT FROM each other.

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 12:54 PM, Kim Rose Carlsen wrote: > ​​ > > CREATE VIEW view_circuit_with_status AS ( >SELECT r.*, > s.circuit_status, > s.customer_id AS s_customer_id, > p.line_speed, > p.customer_id AS p_customer_id > FROM

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

> > On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: > > Hi > > > > I was wondering if there is a way to hint that two columns in two different > > tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if > > table_a.key = 'test' THEN table_b.key =

Re: [GENERAL] Remove Standby (SLAVE) from Primary (MASTER) - Postgres9.1

On Fri, Oct 28, 2016 at 1:54 PM, Joanna Xu wrote: > > All you have to do on the slave is: > > > *sudo su postgres* > > > *touch **/opt/postgres/9.1/data/trigger'* > > > > > Then the slave will go to standalone mode./ > > -- > > > *Melvin Davidson* > > Thanks Melvin. It

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

>>JOIN a AS table_b >> ON table_a.id = table_b.id >> AND table_a.key = table_b.key > Anyways, to use an index for that join, you'd need a composite index on id > *AND* key, not two separate indexes. Its not as much as for using the index, but to be able to push the where clause

Re: [GENERAL] parallel query and row-level security?

On Oct 27, Karl Czajkowski modulated: > Hi, I am struggling to find information on how the new parallel query > features intersect with row-level security. I am wondering whether > this is expected to work at this time? > Further experimentation shows that I can get parallel plans with

Re: [GENERAL] slow performance of array_agg after upgrade from 9.2 to 9.5

jaroet writes: > Internally we upgraded from 9.2 to 9.5 en we had defined an median function. > This became about 7 to 8 times slower using the same functions. > ... > We found that the median function that fills an array is the slow part. When > we change our SQL from

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

> The bigger picture here is that if you've designed a data representation > that requires that a null be considered "equal to" another null, you're > really going to be fighting against the basic semantics of SQL. You'd > be best off to rethink the representation. We've not seen enough info >

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On 10/28/2016 9:29 AM, Kim Rose Carlsen wrote: JOIN a AS table_b ON table_a.id = table_b.id AND table_a.key = table_b.key Anyways, to use an index for that join, you'd need a composite index on id *AND* key, not two separate indexes. -- john r pierce, recycling bits in

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 1:20 PM, Merlin Moncure wrote: > On Fri, Oct 28, 2016 at 1:17 PM, Tom Lane wrote: >> Scott Marlowe writes: >>> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: I was wondering

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 1:17 PM, Tom Lane wrote: > Scott Marlowe writes: >> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: >>> I was wondering if there is a way to hint that two columns in two different >>> tables IS NOT

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

Scott Marlowe writes: > On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: >> I was wondering if there is a way to hint that two columns in two different >> tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if >> table_a.key =

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 10:25 AM, Tom Lane wrote: Steve Clark writes: On 10/28/2016 09:48 AM, Tom Lane wrote: Retrying might be a usable band-aid, but really this is an application logic error. The code that is trying to do "lock table t_unit in exclusive mode" must already

Re: [GENERAL] Remove Standby (SLAVE) from Primary (MASTER) - Postgres9.1

> All you have to do on the slave is: > sudo su postgres > touch /opt/postgres/9.1/data/trigger' > Then the slave will go to standalone mode./ -- > Melvin Davidson Thanks Melvin. It works. One question: Before touch the trigger file on the slave, do I need to run “pg_ctl stop” on the MASTER

[GENERAL] promoting a streaming warm standby once it catches up to the master.

When developing or testing application code, I like to do it against a pretty fresh snapshot of the production server. I have a script which refreshes my dev server by cloning the production server from its most recent base backup, and recovering it forward from files in the wal archive. With a

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: > Hi > > I was wondering if there is a way to hint that two columns in two different > tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if > table_a.key = 'test' THEN table_b.key = 'test' . > > The

Re: [GENERAL] Fast Primary shutdown only after wal_sender_timeout

Le 28 octobre 2016 12:40:24 GMT+02:00, Michael Banck a écrit : >Hi, > >I'm doing some failover tests on a 2-node streaming replication cluster >and shutting down the primary with 'pg_ctl -m fast' results in a >timeout >of 50-60 seconds, pg_ctl returns only after the

Re: [GENERAL] How to hint two columns IS NOT DISTINCT FROM each other in a join

Sorry for double post, just ignore this post.. From: pgsql-general-ow...@postgresql.org on behalf of Kim Rose Carlsen Sent: Thursday, October 27, 2016 6:34:58 PM To: pgsql-general@postgresql.org Subject:

[GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

Hi I was wondering if there is a way to hint that two columns in two different tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if table_a.key = 'test' THEN table_b.key = 'test' . The equals operator already does this but it does not handle NULLS very well (or not at

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

On 28 October 2016 at 13:03, Alexander Farber wrote: > Hello, > > is it please possible to rewrite the SQL query > > SELECT DISTINCT ON (uid) > uid, > female, > given, > photo, > place > FROM

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

On 28 October 2016 at 12:03, Alexander Farber wrote: > is it please possible to rewrite the SQL query > > SELECT DISTINCT ON (uid) > uid, > female, > given, > photo, > place > FROM words_social >

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > On 10/28/2016 09:48 AM, Tom Lane wrote: >> Retrying might be a usable band-aid, but really this is an application >> logic error. The code that is trying to do "lock table t_unit in >> exclusive mode" must already hold some lower-level lock on

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 09:48 AM, Tom Lane wrote: Steve Clark writes: No. But I examined the pg_log/log_file and saw an error indicating it was autovacuum: 2016-10-27 09:47:02 EDT:srm2api:12968:LOG: sending cancel to blocking autovacuum PID 12874 2016-10-27 09:47:02

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

Alexander Farber writes: > is it please possible to rewrite the SQL query > SELECT DISTINCT ON (uid) > uid, > female, > given, > photo, > place > FROM words_social > WHERE uid IN (SELECT

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > No. But I examined the pg_log/log_file and saw an error indicating it was > autovacuum: > 2016-10-27 09:47:02 EDT:srm2api:12968:LOG: sending cancel to blocking > autovacuum PID 12874 > 2016-10-27 09:47:02 EDT:srm2api:12968:DETAIL: Process

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/16 9:27 AM, Steve Clark wrote: > On 10/28/2016 09:15 AM, Adrian Klaver wrote: >> On 10/28/2016 05:28 AM, Steve Clark wrote: >>> Hello List, >>> >>> I am occasionally seeing the following error: >>> ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 >> So what exactly is it

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > I am occasionally seeing the following error: > ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 This isn't nearly enough information to determine what is going on. But if that is a report of a server-detected deadlock error, there

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 09:15 AM, Adrian Klaver wrote: On 10/28/2016 05:28 AM, Steve Clark wrote: Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 So what exactly is it doing at line 3351? from an application written using ecpg when

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 05:28 AM, Steve Clark wrote: Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 So what exactly is it doing at line 3351? from an application written using ecpg when trying an update to the table. Can

[GENERAL] deadlock error - version 8.4 on CentOS 6

Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 from an application written using ecpg when trying an update to the table. Can autovacuum be causing this, since no one else is updating this database table. Thanks, --

[GENERAL] WHERE ... IN condition and multiple columns in subquery

Hello, is it please possible to rewrite the SQL query SELECT DISTINCT ON (uid) uid, female, given, photo, place FROM words_social WHERE uid IN (SELECT player1 FROM games) OR uid IN (SELECT player2

Re: [GENERAL] Passing NULL values in dblink function call

I have created two functions similar to yours, but for me, it works perfect. Maybe you missed somethng when you call the local function.  Do you use 'select * from chdb.dblink_onlocal()' ? Because if you just use 'select chdb.dblink_onlocal()' that could give the result you describe. Attila  

[GENERAL] Fast Primary shutdown only after wal_sender_timeout

Hi, I'm doing some failover tests on a 2-node streaming replication cluster and shutting down the primary with 'pg_ctl -m fast' results in a timeout of 50-60 seconds, pg_ctl returns only after the latter message: <718042016-10-28 10:01:37.833 CEST-5808e5a4.1187c-transid:0>LOG: database

Re: [GENERAL] Auotreconnect/failover libpq

On Fri, Oct 28, 2016 at 10:08 AM, Marcin Giedz wrote: > > Hello, > > This time question about client's autoreconnect (failover to the same node) after disconnection. Let's assume I've got 1 node SQL server and clients connected to this machine. Is there any native

[GENERAL] Auotreconnect/failover libpq

Hello, This time question about client's autoreconnect (failover to the same node) after disconnection. Let's assume I've got 1 node SQL server and clients connected to this machine. Is there any native autoreconnect method in libpq (client's software uses libpq C++) - something like SELECT 1