Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

[Adrian Klaver]

On 01/14/2016 08:24 AM, Joshua D. Drake wrote:

On 01/13/2016 06:00 PM, Berend Tober wrote:


Whether or not it is a foregone conclusion that this community will
adopt a CoC, it seems like a mailing list is not the place to do
revision control. Can you people start a github project or something to
develope your ideas and come back when you have something solid, please.
This thread is creating a lot of spam.


The community discussion around this is not spam.


Still, moving it off-list to a repo or the Wiki is a good idea. Then 
those that care about this can wordsmith to their hearts content.




JD











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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Joshua D. Drake]

tl;dr;

 * added being tolerant of opposing views

== PostgreSQL Community Code of Conduct (CoC) ==

This document is intended to provide community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place for any person who is willing to contribute in
a safe, respectful, productive and collaborative way.  It applies
to all "collaborative space", which is defined as community
communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.).

* We are tolerant of people’s right to have opposing views.

* Participants must ensure that their language and actions are free
of personal attacks and disparaging personal remarks.

* When interpreting the words and actions of others, participants
should always assume good intentions.

* Participants must avoid sustained disruption of the collaborative
space, or any pattern of behavior which could reasonably be
considered harassment.

--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Adrian Klaver]

On 01/14/2016 09:11 AM, Joshua D. Drake wrote:

On 01/14/2016 08:53 AM, Geoff Winkless wrote:

On 14 January 2016 at 16:37, Joshua D. Drake 
wrote:

If someone stands up in a respectful way in a public place and argues
a position, they should not be demonized or punished for that.


I completely agree with you, unfortunately there are enough people who
are so militant about their particular beliefs that they can make life
very difficult for both the individual and the organisation they
represents (cf eg Brendan Eich).

If you are well known (outside of the community) as representing
postgres then I'm afraid extreme opinions will reflect on postgres,
whether you like it or not. On the flip side, I imagine that being
that well-known brings positives (job offers, paid - or at least
expenses-paid in nice locations - speaking engagements etc) in return.


Right but here is the rub. Being anti-gay marriage isn't an extreme
opinion. It is a minority opinion for sure but it is certainly not extreme.

Another issue, consider the statement:

"We do not need more women in the community"

Some will say, "Well yeah, that's true."

Others will say, "You are sexist, you violate the CoC"

We have both of those in this community, and I would argue the "others"
are actually the ones violating the CoC. They are personally disparaging
someone for a perfectly valid opinion.


There is the faulty assumption that the whole CoC movement is based on, 
that the individual communities can control the conversation. You start 
down this path you have to accept the fact that you are letting the 
whole world into community and out of community conversations. I will 
leave to others to say whether that is good or bad, but it is a reality 
that you will need to deal with. In other words, should a CoC be agreed 
upon here, it will end up being subjected to editing from the world at 
large and you need to be prepared for that going in directions that you 
do not want.




Sincerely,

JD

P.S. before too many people get their hackles up remember that the word
need does not imply want or vice versa.





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Joshua D. Drake]

On 01/14/2016 09:14 AM, Neil wrote:


The community needs to decide between the following:

1. Does it want to eliminate participation from people with strong but opposing 
views.

or

2. Does it want to enforce respect and tolerance that allows people with strong 
but opposing views to contribute.

I would rather have #2 because I have a strong regard for freedom of speech.  
The solution is clearer if this can be decided.


#2 of course and I don't think the majority would argue with that.



* When interpreting the words and actions of others, participants
should be tolerant of people’s right to have opposing views and always assume 
good intentions.


Very nice.

JD



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[S McGraw]

On 01/14/2016 09:53 AM, Geoff Winkless wrote:
> On 14 January 2016 at 16:37, Joshua D. Drake  wrote:
>> If someone stands up in a respectful way in a public place and argues
>> a position, they should not be demonized or punished for that.

I am not a contributor to Postgresql (tho I have contributed in 
small ways to other projects) so my opinion is worth less than 2
cents but...

I strongly agree with the above.

> I completely agree with you, unfortunately there are enough people who
> are so militant about their particular beliefs that they can make life
> very difficult for both the individual and the organisation they
> represents (cf eg Brendan Eich).
> 
> If you are well known (outside of the community) as representing
> postgres then I'm afraid extreme opinions will reflect on postgres,
> whether you like it or not. 

That can't be helped, people being the way they are.  It seems to
be a sad fact that many people are willing to use their free speech
rights to suppress the free speech rights of others.  But that is 
not IMO a good reason to submit to or appease them.

The Postgresql community could mitigate this somewhat by having 
a CC that explicitly states that the opinions and expressions of
its "members" do not reflect those of the organization or its 
other members.  Disclaimers like this are common in all sorts of 
organizations (eg commercial media) that present diverse points of 
views.  And the CC itself prohibits off topic and inflammatory 
opinions within the community boundaries.

> On the flip side, I imagine that being
> that well-known brings positives (job offers, paid - or at least
> expenses-paid in nice locations - speaking engagements etc) in return.

That trade-off should be decided by the individual involved, not 
by the organization.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Geoff Winkless]

On 14 January 2016 at 17:11, Joshua D. Drake  wrote:
> Right but here is the rub. Being anti-gay marriage isn't an extreme opinion.
> It is a minority opinion for sure but it is certainly not extreme.

Well it is - it's an extremity in the range of potential view points.

> Another issue, consider the statement:
>
> "We do not need more women in the community"
>
> Some will say, "Well yeah, that's true."
>
> Others will say, "You are sexist, you violate the CoC"
>
> We have both of those in this community, and I would argue the "others" are
> actually the ones violating the CoC. They are personally disparaging someone
> for a perfectly valid opinion.

Well yes, to say "you are sexist" is violating the CoC, because it's a
personal attack. The correct thing to do is to state that the opinion
is a violation of the CoC, without making any judgement about the
person making that statement :)

Geoff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

[David Rowley]

On 14 January 2016 at 03:48, David Grelaud  wrote:

> 3) Always avoid nested-loop join when no indexes are available?
>
> Tom Lane said "There might be some cases where this would help, but there
> would be many more where it would be useless or counterproductive."
> Who is right between Tom Lane and the Leis Viktor's paper above?
>
> We tried to disable nested_loop all the time in a production environment
> and we observed an overall improvement in all queries where Indexes are not
> useful or not available (CTEs), which confirms the paper.
> In fact, one of our production environment is still running with
> "nested_loop off" because benefits are a lot greater than drawbacks as long
> as some tables are relatively small (Indexes not used).
>

I don't really think any of them are wrong. Simply Tom is talking in
general terms for no specific workload, and the paper is dealing with one
specific workload. Of course there are cases when a non-parameterised
nested loop are the fastest way, I mean what could possibility be faster if
there's only 1 row to be joined, for example. It's just that it's not that
much faster since such a join is likely to perform very quickly no matter
which join algorithm is used.

On the other hand, if your tables are not tiny, or you're never just
joining to just a few rows, and you are suffering from stats
underestimations, then it's quite probable that you'll improve your
workload overall by doing enable_nestloop = off. But you have to remember
that if you do this, then you miss out on parameterised inner scans on
nested loops. Quite often these are the fastest option, even when the
number of rows is fairly large, as it might save building a hash table on a
very large relation, or having to sort that relation for a merge join.

Perhaps separating out enable_nestloop so that it only disables
non-parameterised nested loops, and add another GUC for parameterised
nested loops would be a good thing to do. Likely setting enable_nestloop to
off in production would be a slightly easier decision to make, if that was
the case.

It looks pretty simple to do this, so I hacked it up, and attached it here.
There's no doc changes and I'm not that interested in fighting for this
change, it's more just an idea for consideration.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


enable_paramnestloop.patch
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] master slave failover - secondary slaves

[Steven Livingstone]

Hi all, I am relatively new to Postgres but after some some work
master/slave replication and failover working.

I can use a trigger file to promote my first slave to a new master but
where I am confused (from reading various docs) is quite how the second,
third and so on slaves know there is a new master and point to it for
replication etc.

One place says it should be automatic (I don't find that) and another
suggests you need to reconfigure each slave to point to the new Master (and
I guess restart each).

Can anyone clear this up?

Many Thanks,
steven

Re: [GENERAL] pg_dump problem with dropped NOT NULL on child table

[Karsten Hilbert]

On Wed, Jan 13, 2016 at 12:10:15PM -0800, Adrian Klaver wrote:

> On 01/13/2016 11:38 AM, Karsten Hilbert wrote:

> > create table parent (
> > not_null_in_parent integer not null
> > );
> >
> > create table child() inherits (parent);
> > alter table child
> > alter column not_null_in_parent
> > drop not null
> > ;
> >
> >Is this a bug or am I doing things I shouldn't hope work ?
> 
> The latter if I am following the below correctly:
> 
> http://www.postgresql.org/docs/9.5/static/ddl-inherit.html
> 
> "All check constraints and not-null constraints on a parent table are
> automatically inherited by its children. Other types of constraints (unique,
> primary key, and foreign key constraints) are not inherited."

Hello Adrian, thanks for chipping in. I am aware of the above
paragraph. In fact, it made me choose the inheritance
approach to the problem at hand in the first place :-)

Note though that, usually, inheriting is a one-time act --
such as during child table creation. What stays behind is the
legacy - which can be changed (DROP NOT NULL).

I was, then, surprised by the fact that the pg_dump /
pg_restore cycle did not "faithfully" reproduce the child
table. That made me question my ways.

Maybe I shouldn't have been surprised because PG inheritance
doesn't end at table creation time (child and parent are
still linked through data even in the future).

Meatspace inheritance is more like

CREATE TABLE pseudo_child_table AS SELECT FROM pseudo_parent_table ...

While PG inheritance is a bit more like view-on-steroids.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump problem with dropped NOT NULL on child table

[Karsten Hilbert]

On Wed, Jan 13, 2016 at 03:32:12PM -0500, Tom Lane wrote:

> Karsten Hilbert  writes:

> > create table parent (
> > not_null_in_parent integer not null
> > );
> 
> > create table child() inherits (parent);
> > alter table child
> > alter column not_null_in_parent
> > drop not null
> > ;
> 
> > Is this a bug or am I doing things I shouldn't hope work ?
> 
> You should not expect this to work; sooner or later we will make
> the backend reject it.  See
> http://www.postgresql.org/message-id/21633.1448383...@sss.pgh.pa.us

Thanks Tom, that about pins it down for me.

> In the meantime, you could get the effect you want if the parent
> were marked with CHECK (not_null_in_parent IS NOT NULL) NO INHERIT.

The NO INHERIT won't do because this is, again, part of a
larger scheme of things:

The GNUmed EMR uses a common parent table for all tables
holding clinical data:

 Table "clin.clin_root_item"
Column |   Type   | 
  Modifiers   | Storage  | Stats target |   
Description 
  

---+--+---+--+--+-
 pk_audit  | integer  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain|  
| 
 row_version   | integer  | not null default 0  
  | plain|  | 
 modified_when | timestamp with time zone | not null default now()  
  | plain|  | 
 modified_by   | name | not null default 
"current_user"() | plain|  
| 
 pk_item   | integer  | not null default 
nextval('clin.clin_root_item_pk_item_seq'::regclass) | plain|  
| the primary key, not named "id" or "pk" as usual since child  
 +
   |  | 
  |  |  |  
tables will have "id"/"pk"-named primary keys already and   
  +
   |  | 
  |  |  |  
we would get duplicate columns while inheriting from this   
  +
   |  | 
  |  |  |  
table
 clin_when | timestamp with time zone | not null default now()  
  | plain|  | when this 
clinical item became known, can be different from   
 +
   |  | 
  |  |  |  
when it was entered into the system (= audit.audit_fields.modified_when)
 fk_encounter  | integer  | not null
  | plain|  | the 
encounter this item belongs to
 fk_episode| integer  | not null
  | plain|  | the 
episode this item belongs to
 narrative | text | 
  | extended |  | each 
clinical item by default inherits a free text field for clinical narrative
 soap_cat  | text | 
  | extended |  | each 
clinical item must be either one of the S, O, A, P, U   
  +
   |  | 
  |  |  |  
categories or NULL to indicate a non-clinical item, U meaning 
Unspecified-but-clinical
Indexes:
"clin_root_item_pkey" PRIMARY KEY, btree (pk_item)
"idx_cri_encounter" btree (fk_encounter)
"idx_cri_episode" btree (fk_episode)
Check constraints:
"clin_root_item_sane_soap_cat" CHECK (soap_cat IS NULL OR 

Re: [GENERAL] master slave failover - secondary slaves

[Andreas Kretschmer]

Steven Livingstone  wrote:

> Hi all, I am relatively new to Postgres but after some some work master/slave
> replication and failover working.
> 
> I can use a trigger file to promote my first slave to a new master but where I
> am confused (from reading various docs) is quite how the second, third and so
> on slaves know there is a new master and point to it for replication etc.
> 
> One place says it should be automatic (I don't find that) and another suggests
> you need to reconfigure each slave to point to the new Master (and I guess
> restart each).
> 
> Can anyone clear this up?

Yeah, you have to change the recovery.conf to point to the new master.
Read more here:

http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] master slave failover - secondary slaves

[Steven Livingstone]

Thanks Andreas - that looks ideal.

Steven Livingstone  wrote:

> Hi all, I am relatively new to Postgres but after some some work master/slave
> replication and failover working.
>
> I can use a trigger file to promote my first slave to a new master but where I
> am confused (from reading various docs) is quite how the second, third and so
> on slaves know there is a new master and point to it for replication etc.
>
> One place says it should be automatic (I don't find that) and another suggests
> you need to reconfigure each slave to point to the new Master (and I guess
> restart each).
>
> Can anyone clear this up?

Yeah, you have to change the recovery.conf to point to the new master.
Read more here:
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


On Thu, Jan 14, 2016 at 9:25 AM, Steven Livingstone  wrote:

> Hi all, I am relatively new to Postgres but after some some work
> master/slave replication and failover working.
>
> I can use a trigger file to promote my first slave to a new master but
> where I am confused (from reading various docs) is quite how the second,
> third and so on slaves know there is a new master and point to it for
> replication etc.
>
> One place says it should be automatic (I don't find that) and another
> suggests you need to reconfigure each slave to point to the new Master (and
> I guess restart each).
>
> Can anyone clear this up?
>
> Many Thanks,
> steven
>

Re: [GENERAL] Function error

[Albe Laurenz]

Sachin Srivastava wrote:
> In my function the problem is that global variables defined inside the 
> function.
> These variables are visible to functions defined inside a function.
> If we move these inner functions to outside of the main function,
> they will lose the visibility of the global variables.
> So I think that's why we have to correct this functions rather then creating 
> it as individual.

I see.
You'll have to rewrite the functions then, probably by passing the shared
values as parameters instead of having them in global variables.

> We have migrated our data from Oracle to Postgres through ORA2PG Tool
> after that we are getting this error for this function.

ora2pg cannot translate all PL/SQL code, you have to be prepared to rewrite
most nontrivial functions.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

[Joshua D. Drake]

On 01/14/2016 08:30 AM, Adrian Klaver wrote:

On 01/14/2016 08:24 AM, Joshua D. Drake wrote:

On 01/13/2016 06:00 PM, Berend Tober wrote:


Whether or not it is a foregone conclusion that this community will
adopt a CoC, it seems like a mailing list is not the place to do
revision control. Can you people start a github project or something to
develope your ideas and come back when you have something solid, please.
This thread is creating a lot of spam.


The community discussion around this is not spam.


Still, moving it off-list to a repo or the Wiki is a good idea. Then
those that care about this can wordsmith to their hearts content.


Meh. Those that don't want to read the thread don't have to.

Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Geoff Winkless]

On 14 January 2016 at 16:37, Joshua D. Drake  wrote:
> If someone stands up in a respectful way in a public place and argues
> a position, they should not be demonized or punished for that.

I completely agree with you, unfortunately there are enough people who
are so militant about their particular beliefs that they can make life
very difficult for both the individual and the organisation they
represents (cf eg Brendan Eich).

If you are well known (outside of the community) as representing
postgres then I'm afraid extreme opinions will reflect on postgres,
whether you like it or not. On the flip side, I imagine that being
that well-known brings positives (job offers, paid - or at least
expenses-paid in nice locations - speaking engagements etc) in return.

Geoff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump problem with dropped NOT NULL on child table

[Adrian Klaver]

On 01/14/2016 12:59 AM, Karsten Hilbert wrote:

On Wed, Jan 13, 2016 at 12:10:15PM -0800, Adrian Klaver wrote:


On 01/13/2016 11:38 AM, Karsten Hilbert wrote:



create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Is this a bug or am I doing things I shouldn't hope work ?


The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints (unique,
primary key, and foreign key constraints) are not inherited."


Hello Adrian, thanks for chipping in. I am aware of the above
paragraph. In fact, it made me choose the inheritance
approach to the problem at hand in the first place :-)

Note though that, usually, inheriting is a one-time act --
such as during child table creation. What stays behind is the
legacy - which can be changed (DROP NOT NULL).

I was, then, surprised by the fact that the pg_dump /
pg_restore cycle did not "faithfully" reproduce the child
table. That made me question my ways.

Maybe I shouldn't have been surprised because PG inheritance
doesn't end at table creation time (child and parent are
still linked through data even in the future).


Actually more than that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

INHERITS ( parent_table [, ... ] )

Use of INHERITS creates a persistent relationship between the new child 
table and its parent table(s). Schema modifications to the parent(s) 
normally propagate to children as well, and by default the data of the 
child table is included in scans of the parent(s).





Meatspace inheritance is more like

CREATE TABLE pseudo_child_table AS SELECT FROM pseudo_parent_table ...

While PG inheritance is a bit more like view-on-steroids.

Thanks,
Karsten




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

[Joshua D. Drake]

On 01/13/2016 06:00 PM, Berend Tober wrote:


Whether or not it is a foregone conclusion that this community will
adopt a CoC, it seems like a mailing list is not the place to do
revision control. Can you people start a github project or something to
develope your ideas and come back when you have something solid, please.
This thread is creating a lot of spam.


The community discussion around this is not spam.

JD








--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Joshua D. Drake]

On 01/14/2016 08:53 AM, Geoff Winkless wrote:

On 14 January 2016 at 16:37, Joshua D. Drake  wrote:

If someone stands up in a respectful way in a public place and argues
a position, they should not be demonized or punished for that.


I completely agree with you, unfortunately there are enough people who
are so militant about their particular beliefs that they can make life
very difficult for both the individual and the organisation they
represents (cf eg Brendan Eich).

If you are well known (outside of the community) as representing
postgres then I'm afraid extreme opinions will reflect on postgres,
whether you like it or not. On the flip side, I imagine that being
that well-known brings positives (job offers, paid - or at least
expenses-paid in nice locations - speaking engagements etc) in return.


Right but here is the rub. Being anti-gay marriage isn't an extreme 
opinion. It is a minority opinion for sure but it is certainly not extreme.


Another issue, consider the statement:

"We do not need more women in the community"

Some will say, "Well yeah, that's true."

Others will say, "You are sexist, you violate the CoC"

We have both of those in this community, and I would argue the "others" 
are actually the ones violating the CoC. They are personally disparaging 
someone for a perfectly valid opinion.


Sincerely,

JD

P.S. before too many people get their hackles up remember that the word 
need does not imply want or vice versa.



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Neil]

> On Jan 14, 2016, at 10:37 AM, Joshua D. Drake  wrote:
> 
> Now, how does this apply (as an example)? There are very loud people in this 
> community who are pro-gay marriage and they are unable to respect those who 
> don't agree with the position. There are also those who are anti-gay marriage 
> that do the same.
> 
> If someone stands up in a respectful way in a public place and argues
> a position, they should not be demonized or punished for that. I am
> 100% certain the below will cause issues. We need different wording
> if we are going to take that into account.

The community needs to decide between the following:

1. Does it want to eliminate participation from people with strong but opposing 
views.

or 

2. Does it want to enforce respect and tolerance that allows people with strong 
but opposing views to contribute.

I would rather have #2 because I have a strong regard for freedom of speech.  
The solution is clearer if this can be decided.

For 2 a statement that the community respects the rights of people to have 
opposing or non-popular views as freedom of speech and since this project is 
non political we expect a certain amount of tolerance with regards to people’s 
views and opinions outside of their work on this project.

or 

> 
> * When interpreting the words and actions of others, participants
> should always assume good intentions.

Could be.

* When interpreting the words and actions of others, participants
should be tolerant of people’s right to have opposing views and always assume 
good intentions.

Neil

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

[David Rowley]

On 15 January 2016 at 04:00, Tom Lane  wrote:

> David Rowley  writes:
> > Perhaps separating out enable_nestloop so that it only disables
> > non-parameterised nested loops, and add another GUC for parameterised
> > nested loops would be a good thing to do. Likely setting enable_nestloop
> to
> > off in production would be a slightly easier decision to make, if that
> was
> > the case.
> > It looks pretty simple to do this, so I hacked it up, and attached it
> here.
> > There's no doc changes and I'm not that interested in fighting for this
> > change, it's more just an idea for consideration.
>
> I'm not terribly excited by this idea either.  If making such a change
> actually makes things better for someone consistently, I'd argue that
> the problem is a mistaken cost estimate elsewhere, and we'd be better off
> to find and fix the real problem.  (There have already been discussions
> of only believing single-row rowcount estimates when they're provably
> true, which might help if we can figure out how to do it cheaply enough.)
>

Actually, it's not very hard to hit a bad underestimate at all. All you
need is a join on two columns which are co-related. Since PostgreSQL
multiplies the estimated selectivities the row count is going to come out
too low. This also tricks the planner into thinking that this is a good
join to perform early, since (it thinks that) it does not produce many rows
at all. You only need 1 more join to occur after that to choose a nested
loop join mistakenly to hit the issue.

FWIW TPC-H Q9 has this exact trip hazard with the partsupp table, which is
the exact reason why this patch was born:
https://commitfest.postgresql.org/7/210/

I also think that the attitude that we can *always* fix the costs and
estimates is not the right one. The planner is never going to get it right
100% of the time. If we ever think we can build such a planner then someone
needs to come along and direct us back into the real world.

-- 
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Adrian Klaver]

On 01/14/2016 01:47 PM, Williamson, Michael wrote:

Please excuse my mistake.

We were dropping a view that had the trigger on it beforehand, then in
trying to re-build the entire schema had scripts that attempted to drop
trigger if they existed before re-creating the view and triggers.  I
over-sanitized the example I posted and made it unclear.

I did some more testing and DROP TRIGGER IF EXISTS indeed works as
expected if the table/view is there and the trigger is not there.

As a follow-up, it would be nice if "IF EXISTS" could apply to either
the trigger or the relation it is applied to, as I don't think a
trigger can't exist without a relation to apply it to.


That is the part Tom and David where trying to explain to me and I was 
not getting, the behavior has changed.


Some testing revealed this:

test=# select version();
 version 


--
 PostgreSQL 9.0.20 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE 
Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit

(1 row)


test=# drop trigger if exists test_trg on test_tbl;
ERROR:  relation "test_tbl" does not exist


test=# select version();
   version 


-
 PostgreSQL 9.4.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit

(1 row)


test=# drop trigger if exists test_trg on test_tbl;
NOTICE:  relation "test_tbl" does not exist, skipping
DROP TRIGGER

In either case though IF EXISTS did the right thing, so I am wondering 
if upgrading to a more recent version of 9.1 will help you.





Michael






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] regexp_replace

[Andy Colson]

Hi all.

This is not doing as I'd expected:

select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');

 regexp_replace

 71096.013
(1 row)

It acts the same with dashes:
select regexp_replace('71-09-6-01-3', '(\d)[.-](\d)', '\1\2', 'g');

 regexp_replace

 71096-013
(1 row)

I cannot use translate because there is other text in the field.  I'm 
trying to strip masking characters from a parcel number in a larger text 
field (for example:  "the parcel 12-34-56 has caught on fire")


I seem to be missing something, any hints?

I'm on PG 9.3.9 on Slackware64.

Thanks for your time,

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Chris Travers]

On Thu, Jan 14, 2016 at 5:37 PM, Joshua D. Drake 
wrote:

> Updated.
>
> Tl;dr;
>
>  * Removed excess wording
>  * Removed non-.org controlled spaces in first paragraph
>  * Added explicit discussion on explicit problem with last paragraph of
> Kevin's last version.
>
> == PostgreSQL Community Code of Conduct (CoC) ==
>
> This document is intended to provide community guidelines for
> creating and enforcing a safe, respectful, productive, and
> collaborative place for any person who is willing to contribute in
> a safe, respectful, productive and collaborative way.  It applies
> to all "collaborative space", which is defined as community
> communications channels (such as mailing lists, IRC, submitted
> patches, commit comments, etc.).
>
> * Participants must ensure that their language and actions are free
> of personal attacks and disparaging personal remarks.
>
> * When interpreting the words and actions of others, participants
> should always assume good intentions.
>
> * Participants must avoid sustained disruption of the collaborative
> space, or any pattern of behavior which could reasonably be
> considered harassment.
>
>
> === END ===
>
> I have serious problems with the below[1]. My main concern is this and I
> am going to use real people to
> illustrate the issue:
>
>  * I am a left leaning realist with some libretarian leanings.
>(I voted for Obama)
>
>  * Andrew D is a left leaning lefist
>(He might vote for Sanders)
>
> We are both high profile contributors and often have public discourse
> on who is right or wrong. This is good. It is fun and we poke at
> each other but we continue to respect each other.
>
> However, there are people in this community that are far right
> and far left. It is not the responsibility of .Org to determine
> who is right or wrong there. Period.
>
> Now, how does this apply (as an example)? There are very loud people in
> this community who are pro-gay marriage and they are unable to respect
> those who don't agree with the position. There are also those who are
> anti-gay marriage that do the same.
>

Since this is a global project with a truly global contribution team it
actually gets worse than this because a lot of these issues are also
lifestyle/economic system issues.

I figure in this discussion it is worth mentioning my perspective as
someone who has now lived and worked on three continents.  Not only can
these political positions be *seen* as personal attacks, they can *be*
personal attacks and often times we cannot see eachothers' social realities
well enough to see that this is so.

I am going to use same-sex marriage to illustrate the issue because you
brought it up and also because it is particularly clear, but one could see
similar cases made regarding almost any major political controversial
issue.  I know a lot of people may see what I am writing below as
politically offensive   But my point here is about discourse and the merits
of "no personal attacks" not about the policies of one country or another.

In Western, industrialized countries, for the most part, marriage is seen
as an individual choice, whether one wants to have kids is seen as deeply
individual, divorce is seen as a key aspect of liberating women from men,
and men and women are seen as interchangeable.

In many other nations, marriage and children are seen as family duties,
gender is closely tied to kinship and how capital and resources are
divided, and married households are the seat of business and production
rather than primarily economically about consumption.  In these societies,
things like easy divorce, abortion, and same-sex marriage have very
different implications than in Western countries, and when you start
talking across international (rather than merely national boundaries) it is
hard for people to see where the others are coming from.

So it becomes very easy for an internationalized political stance to be
seen as obviously hostile to someone's desired way of life, and frankly on
many of these issues that goes both ways.  Far worse, there is truth to
that sense that internationalized political views are about depriving some
way of life of legitimacy.

So it seems to me there are two options.  The first is that political views
have no place in the community.  The second is that we expect people to
treat eachother with respect regardless of political differences.

Personally I am in the second camp.  I think the first is way
over-protective.  I think it is fine to shut out the crusaders.  But we
should recognize we are here to work together, come together, and do so
regardless of our differences.

Best Wishes,
Chris Travers


>
> If someone stands up in a respectful way in a public place and argues
> a position, they should not be demonized or punished for that. I am
> 100% certain the below will cause issues. We need different wording
> if we are going to take that into account.
>
>
> 1. There is a distinction between words and actions 

Re: [GENERAL] WIP: CoC V6

[Steve Petrie, P.Eng.]

Please see my two suggestions below.

Steve

- Original Message - 
From: "Joshua D. Drake" 
To: "Neil" ; "Psql_General (E-mail)" 


Sent: Thursday, January 14, 2016 4:48 PM
Subject: Re: [GENERAL] WIP: CoC V6



Hello,

I posted this earlier but ended up breaking my own silly rev scheme. 
So, I am going back to my silly rev scheme. Rev 6 it is:


tl;dr;

 * added being tolerant of opposing views
 * Removed excess wording from Grittner's version
 * Removed non-.org controlled spaces in first paragraph

== PostgreSQL Community Code of Conduct (CoC) ==

This document is intended to provide community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place for any person who is willing to contribute in
a safe, respectful, productive and collaborative way.  It applies
to all "collaborative space", which is defined as community
communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.).



It seems to me that the above paragraph is too long, especially 
considering that the following points are crisp short single sentences.


Also, why repeat the phrase "safe, respectful, productive, and 
collaborative"?


My suggestion -- make two paragraphs:

This document provides community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place. Every contributor is expected to support these 
goals.


The guidelines apply to all "collaborative space", which is defined as 
community

communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.).


* We are tolerant of people’s right to have opposing views.



I recall someone earlier mentioning the benefit of keeping discussions 
on topic.


And it seems to me that that only views actually expressed are of 
concern.


So I would offer the following revision:

* We are tolerant of people’s right to express opposing views relevant 
to the success of the project.



* Participants must ensure that their language and actions are free
of personal attacks and disparaging personal remarks.

* When interpreting the words and actions of others, participants
should always assume good intentions.

* Participants must avoid sustained disruption of the collaborative
space, or any pattern of behavior which could reasonably be
considered harassment.


--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PgDay LFNW April 23rd & 24th (can you speak?)

[Joshua D. Drake]

Folks,

The CFP is open for this, it can be found here:

http://linuxfestnorthwest.org/2016/present

As usual, we have a dedicated track to PostgreSQL. We however tend to 
lack topics of the intro variety. It would be great if we could get some 
folks to submit talks on:


PostgreSQL 101: How to get started
Why Postgres for Web development
Do you MySQL? Take the PostgreSQL aspirin

Anything along those lines would be great!

Hope to see you there.

JD
--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace

[John McKown]

How about:

select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g');

?

In your example, the (\d)[.-](\d) says find a digit followed by a period or
dash followed by another digit. The first time through 1.0 is matched and
replaced with 10 (710) with the "current location" pointing before the 9.
Go again and 9.6 is replaced by 96 for (71096) with the "current location"
pointing to the period! So ".0" doesn't match. (71096.0) next match is 1.3
and result is 13 ( 71096.013). If you don't want to eliminate the period or
dash unless it is _between_ two digits, try:

select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1', 'g');

(?=\d) is a "look ahead") match which says that the period or dash must be
followed by a digit, but the expression _does not_ "consume" the digit
matched.


On Thu, Jan 14, 2016 at 1:43 PM, Andy Colson  wrote:

> Hi all.
>
> This is not doing as I'd expected:
>
> select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');
>
>  regexp_replace
> 
>  71096.013
> (1 row)
>
> It acts the same with dashes:
> select regexp_replace('71-09-6-01-3', '(\d)[.-](\d)', '\1\2', 'g');
>
>  regexp_replace
> 
>  71096-013
> (1 row)
>
> I cannot use translate because there is other text in the field.  I'm
> trying to strip masking characters from a parcel number in a larger text
> field (for example:  "the parcel 12-34-56 has caught on fire")
>
> I seem to be missing something, any hints?
>
> I'm on PG 9.3.9 on Slackware64.
>
> Thanks for your time,
>
> -Andy
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Werner Heisenberg is driving down the autobahn. A police officer pulls
him over. The officer says, "Excuse me, sir, do you know how fast you
were going?"
"No," replies Dr. Heisenberg, "but I know where I am."

Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

He's about as useful as a wax frying pan.

Maranatha! <><
John McKown

Re: [GENERAL] regexp_replace

[Andy Colson]

On 1/14/2016 1:59 PM, Tom Lane wrote:

Andy Colson  writes:

This is not doing as I'd expected:



select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');



   regexp_replace

   71096.013
(1 row)


I think regexp_replace considers only non-overlapping substrings,
eg, once it's replaced 1.0 with 10, it then picks up searching at
the 9 rather than starting over.  The dot after 6 doesn't get
removed because the 6 can't belong to two replaceable substrings, and
it already got consumed in the process of removing the dot before 6.

I might be wrong, but I think two passes of regexp_replace would
do what you want in this example.

regards, tom lane



Ah, that would make sense, and seems to explain:

select regexp_replace('7-9-6-1-3', '(\d)[.-](\d)', '\1\2', 'g');

 regexp_replace

 79-61-3
(1 row)


select regexp_replace('71-09-56-01-53', '(\d)[.-](\d)', '\1\2', 'g');

 regexp_replace

 7109560153
(1 row)


I can work two passes in.  Thanks Tom!

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace

[Tom Lane]

Andy Colson  writes:
> This is not doing as I'd expected:

> select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');

>   regexp_replace
> 
>   71096.013
> (1 row)

I think regexp_replace considers only non-overlapping substrings,
eg, once it's replaced 1.0 with 10, it then picks up searching at
the 9 rather than starting over.  The dot after 6 doesn't get
removed because the 6 can't belong to two replaceable substrings, and
it already got consumed in the process of removing the dot before 6.

I might be wrong, but I think two passes of regexp_replace would
do what you want in this example.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace

[David G. Johnston]

On Thu, Jan 14, 2016 at 12:43 PM, Andy Colson  wrote:

> Hi all.
>
> This is not doing as I'd expected:
>
> select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g');
>
>  regexp_replace
> 
>  71096.013
> (1 row)
>
>
​Solution: select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2',
'g');

Reason: in the original the trailing "(\d)" eats ​the digit following the
symbol and then that digit is no longer available for matching the
preceding digit in the expression.  IOW the same character cannot be used
to match both the first \d and the second \d so once the first \d captures
the 6 there is no \d to match before trailing period.

By using the construct (?:\d) you are zero-width (non-capturing) asserting
the the next character is a digit but you are not consuming it and so the
continuation of the global matching still has that character to match the
first \d.

David J.

Re: [GENERAL] regexp_replace

[Andy Colson]

On 1/14/2016 2:02 PM, John McKown wrote:

How about:

select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g');

match is 1.3 and result is 13 ( 71096.013). If you don't want to
eliminate the period or dash unless it is _between_ two digits, try:

select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1', 'g');

(?=\d) is a "look ahead") match which says that the period or dash must
be followed by a digit, but the expression _does not_ "consume" the
digit matched.



Maranatha! <><
John McKown


Yes, excellent, both seem to work.  I'll run a bunch of data through 
them both and see what happens.


Thanks much for the help!

-Andy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] regexp_replace

[Andy Colson]

On 1/14/2016 2:06 PM, David G. Johnston wrote:

select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2', 'g');


Thanks David!

-Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres BDR bdr_init_copy fails

[Nikhil]

hello ,


I am trying to setup 2 -node bdr group. i have 5 databases in node 1. i
created groups for each db in node-1. then did pg_basebackup from node -2
and started bdr_init_copy. Its giving below error.


*bdr_init_copy*

bdr_init_copy: starting ...
Getting remote server identification ...
Detected 5 BDR database(s) on remote server
Updating BDR configuration on the remote node:
 db1: creating replication slot ...
 db2: creating node entry for local node ...
 prime: creating replication slot ...
 prime: creating node entry for local node ...
 cloud: creating replication slot ...
 cloud: creating node entry for local node ...
 stack: creating replication slot ...
 stack: creating node entry for local node ...
 dstack: creating replication slot ...
 dstack: creating node entry for local node ...
Creating restore point on remote node ...
Bringing local node to the restore point ...
pg_ctl: another server might be running; trying to start server anyway
Transaction log reset
Initializing BDR on the local node:
*...hangs after this...*

*node-1 pg log*

<752812016-01-14 19:46:17 GMT%ERROR:  Failed to find expected
bdr.connections row (conn_sysid,conn_timeline,conn_dboid) =
(6239712043346226652,1,16386) in bdr.bdr_connections
<457142016-01-14 19:46:17 GMT%LOG:  worker process: bdr
(6238458855251125696,1,16386,)->bdr (6239712043346226652,1, (PID 75281)
exited with exit code 1
<457142016-01-14 19:46:18 GMT%LOG:  starting background worker process "bdr
(6238458855251125696,1,16396,)->bdr (6239712043346226652,1,"
<752822016-01-14 19:46:18 GMT%ERROR:  Failed to find expected
bdr.connections row (conn_sysid,conn_timeline,conn_dboid) =
(6239712043346226652,1,16387) in bdr.bdr_connections
<457142016-01-14 19:46:18 GMT%LOG:  worker process: bdr
(6238458855251125696,1,16396,)->bdr (6239712043346226652,1, (PID 75282)
exited with exit code 1

*node-2 pg log*

<10.102.31.228(57420)postgres714142016-01-14 19:41:00 GMTmpsdb%LOG:
 duration: 1.092 ms
<10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG:
 statement: DELETE FROM pg_catalog.pg_
shseclabel WHERE provider = 'bdr';
<10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG:
 duration: 2.072 ms
<10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG:
 statement: SELECT pg_catalog.pg_repli
cation_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier;
<10.102.31.228(26942)postgres714152016-01-14 19:41:00 GMTprime%LOG:
 duration: 0.693 ms
<10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG:
 statement: DELETE FROM pg_catalog.pg
_shseclabel WHERE provider = 'bdr';
<10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG:
 duration: 2.350 ms
<10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG:
 statement: SELECT pg_catalog.pg_repl
ication_identifier_drop(riname) FROM pg_catalog.pg_replication_identifier;
<10.102.31.228(45510)postgres714162016-01-14 19:41:00 GMTcloud%LOG:
 duration: 0.672 ms
<10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG:
 statement: DELETE FROM pg_catalog
.pg_shseclabel WHERE provider = 'bdr';
<10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG:
 duration: 2.094 ms
<10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG:
 statement: SELECT pg_catalog.pg_r
eplication_identifier_drop(riname) FROM
pg_catalog.pg_replication_identifier;
<10.102.31.228(43879)postgres714172016-01-14 19:41:00 GMTstack%LOG:
 duration: 0.758 ms
<10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG:
 statement: DELETE FROM pg_catalo
g.pg_shseclabel WHERE provider = 'bdr';
<10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG:
 duration: 1.954 ms
<10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG:
 statement: SELECT pg_catalog.pg_
replication_identifier_drop(riname) FROM
pg_catalog.pg_replication_identifier;
<10.102.31.228(11976)postgres714182016-01-14 19:41:00 GMTdstack%LOG:
 duration: 0.669 ms
<713942016-01-14 19:41:00 GMT%LOG:  received smart shutdown request
<714012016-01-14 19:41:00 GMT%LOG:  autovacuum launcher shutting down
<713942016-01-14 19:41:00 GMT%LOG:  worker process: bdr supervisor (PID
71404) exited with exit code 0
<713942016-01-14 19:41:00 GMT%LOG:  unregistering background worker "bdr
supervisor"
<713982016-01-14 19:41:00 GMT%LOG:  shutting down
<713982016-01-14 19:41:00 GMT%LOG:  database system is shut down

Re: [GENERAL] regexp_replace

[David G. Johnston]

On Thu, Jan 14, 2016 at 1:27 PM, Andy Colson  wrote:

> On 1/14/2016 2:06 PM, David G. Johnston wrote:
>
>> select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2', 'g');
>>
>

​John already picked up on the fact that the "\2" in the replacement is
pointless (neither helping nor hurting) since nothing was captured at that
position.

David J.

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

[Tom Lane]

David Rowley  writes:
> Perhaps separating out enable_nestloop so that it only disables
> non-parameterised nested loops, and add another GUC for parameterised
> nested loops would be a good thing to do. Likely setting enable_nestloop to
> off in production would be a slightly easier decision to make, if that was
> the case.
> It looks pretty simple to do this, so I hacked it up, and attached it here.
> There's no doc changes and I'm not that interested in fighting for this
> change, it's more just an idea for consideration.

I'm not terribly excited by this idea either.  If making such a change
actually makes things better for someone consistently, I'd argue that
the problem is a mistaken cost estimate elsewhere, and we'd be better off
to find and fix the real problem.  (There have already been discussions
of only believing single-row rowcount estimates when they're provably
true, which might help if we can figure out how to do it cheaply enough.)

Having said that, if we did split enable_nestloop like this, what I think
you'd want to discriminate against is nestloops where the inner rel is
not parameterized *by the outer rel*.  This test isn't doing that; it will
happily accept inner rels that are parameterized by some unrelated rel.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Exclude bdr data from dump

[Adrian Klaver]

On 01/13/2016 12:18 PM, Roland van Laar wrote:

Hello,

I want to exclude all bdr data from a database dump.
My command is:

$ bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data
--data-only --exclude-table='bdr*

This results in bdr data being included.


Did you mean excluded above?



Including only bdr results in an error:

$ bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data
--data-only --table='bdr*'
bdr_dump: No matching tables were found


Per the docs:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

--table=table

Note: The behavior of the -t switch is not entirely upward compatible 
with pre-8.2 PostgreSQL versions. Formerly, writing -t tab would dump 
all tables named tab, but now it just dumps whichever one is visible in 
your default search path. To get the old behavior you can write -t 
'*.tab'. Also, you must write something like -t sch.tab to select a 
table in a particular schema, rather than the old locution of -n sch -t tab.


So have you tried schema qualifying the table name?



Please help.

Roland






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WIP: CoC V5

[Joshua D. Drake]

Updated.

Tl;dr;

 * Removed excess wording
 * Removed non-.org controlled spaces in first paragraph
 * Added explicit discussion on explicit problem with last paragraph of 
Kevin's last version.


== PostgreSQL Community Code of Conduct (CoC) ==

This document is intended to provide community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place for any person who is willing to contribute in
a safe, respectful, productive and collaborative way.  It applies
to all "collaborative space", which is defined as community
communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.).

* Participants must ensure that their language and actions are free
of personal attacks and disparaging personal remarks.

* When interpreting the words and actions of others, participants
should always assume good intentions.

* Participants must avoid sustained disruption of the collaborative
space, or any pattern of behavior which could reasonably be
considered harassment.


=== END ===

I have serious problems with the below[1]. My main concern is this and I 
am going to use real people to

illustrate the issue:

 * I am a left leaning realist with some libretarian leanings.
   (I voted for Obama)

 * Andrew D is a left leaning lefist
   (He might vote for Sanders)

We are both high profile contributors and often have public discourse
on who is right or wrong. This is good. It is fun and we poke at
each other but we continue to respect each other.

However, there are people in this community that are far right
and far left. It is not the responsibility of .Org to determine
who is right or wrong there. Period.

Now, how does this apply (as an example)? There are very loud people in 
this community who are pro-gay marriage and they are unable to respect 
those who don't agree with the position. There are also those who are 
anti-gay marriage that do the same.


If someone stands up in a respectful way in a public place and argues
a position, they should not be demonized or punished for that. I am
100% certain the below will cause issues. We need different wording
if we are going to take that into account.


1. There is a distinction between words and actions taken within the
community and words and actions outside community communication
channels and events, but there is a gray area when using public
forums or social media where a person identifies as a member of
this community.  Members of the community, especially those with a
high profile within the community, should be mindful of this and
avoid saying or doing anything in such venues which might create an
unwelcoming or hostile attitude toward the community.

--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Williamson, Michael]

Please excuse my mistake.

We were dropping a view that had the trigger on it beforehand, then in
trying to re-build the entire schema had scripts that attempted to drop
trigger if they existed before re-creating the view and triggers.  I
over-sanitized the example I posted and made it unclear.

I did some more testing and DROP TRIGGER IF EXISTS indeed works as
expected if the table/view is there and the trigger is not there.

As a follow-up, it would be nice if "IF EXISTS" could apply to either
the trigger or the relation it is applied to, as I don't think a
trigger can't exist without a relation to apply it to. 

Michael


On Wed, 2016-01-13 at 15:08 -0800, Adrian Klaver wrote:
> On 01/13/2016 02:51 PM, David Rowley wrote:
> > On 14 January 2016 at 11:32, Adrian Klaver  om
> > > wrote:
> >
> > On 01/13/2016 02:24 PM, Tom Lane wrote:
> >
> > "Williamson, Michael"  > > writes:
> >
> > I'm attempting to drop a trigger that may or may not
> exist,
> > so am using
> > the "IF EXISTS" clause. Â This works fine for tables,
> views,
> > functions,
> > domains, and types, but for some reason seems to be
> ignored for
> > triggers. Â I'd expect to see more about this online if
> it
> > were a bug,
> > so I'm thinking I may be missing something obvious.
> >
> >
> > Example:
> > DROP TRIGGER IF EXISTS udf_customer_update_trigger ON
> customer;
> >
> >
> > Expected Output:
> > NOTICE:Â Â trigger "udf_customer_update_trigger" does
> not
> > exist, skipping
> >
> >
> > Observed Output:
> > ERROR:Â Â relation "udf_customer_update_trigger" does
> not exist
> >
> >
> > Environment:
> > CentOS 6.6
> > postgresql91-server-9.1.14-1PGDG.rhel6.x86_64
> >
> >
> > This has worked the way you're imagining since (I think)
> 9.4.
> > Before
> > that the "if exists" semantics only applied to the trigger
> itself,
> > not to the relation.
> >
> >
> > Alright now I am confused. Other then changing table to
> table_name I
> > am not seeing where the below changed. In both cases a NOTICE
> is
> > supposed to be raised.
> >
> > http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.
> html
> >
> > http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.
> html
> >
> >
> > Seems to have been changed in
> > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c
> 6cd0de1827ba58756e24e18110cf902182a
> 
> I will take a look at this at some point. The part that has me
> confused 
> from the original post is this:
> 
> ERROR:  relation "udf_customer_update_trigger" does not exist
> 
> If the ERROR is because the table does not exist, why not?:
> 
> ERROR:  relation "customer" does not exist
> 
> >
> > Perhaps that commit should have also made changes to the documents
> to
> > change things such as:
> >
> > Do not throw an error if the trigger does not exist. A notice is
> issued
> > in this case.
> >
> > To
> >
> > Do not throw an error if the trigger or table does not exist. A
> notice
> > is issued in this case.
> >
> > --
> >   David Rowley http://www.2ndQuadrant.com/
> >   PostgreSQL Development, 24x7 Support, Training & Services
> 
> 
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query Questions - PostgreSQL

[Saulo Merlo]

On 13/01/2016, at 3:30 PM, Saulo Merlo  wrote:




So.. I have a Query that is taking too long to complete.
OLD QUERY:
  SELECT  file.inode_idAS file_id,  file.parent_inode_id AS file_group, 
 file.relative_path   AS file_type,  file.file_data   AS file_binary,  
file.node_full_path  AS file_name,  file.last_modified   AS date_createdFROM  
gorfs.nodes AS file  INNER JOIN  gorfs.inode_segments AS iseg ON iseg.st_ino = 
file.parent_inode_id  AND file.object_type = 
'S_IFREG'  AND iseg.nfs_migration_date IS NULL  
AND (file.last_modified <   
(transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: 
INTERVAL))  AND iseg.st_ino_target = 
file.inode_idLIMIT  100;
We created a new temporary table to store migrations, which may be the best 
option (no longer need to join new columns in query).I just need the same 
output as any of the correct above one.
NEW QUERY STRUCTURE:
table: gorfs.nfs_data:
CREATE TABLE gorfs.nfs_data(  owner_id integer NOT NULL,  file_id integer NOT 
NULL,  migration_path "text",  migration_date timestamp with time zone,  
CONSTRAINT nfs_data_pkey PRIMARY KEY ("file_id"))
INDEX: 
CREATE INDEX ix_nfs_data_owner_id  ON gorfs.nfs_data  USING btree  ("owner_id") 
 WHERE "migration_date" IS NULL;

OLD EXPLAIN ANALYZE (Using the OLD query):Link: http://explain.depesz.com/s/Swu
COLUMNS:ALTER TABLE gorfs.nfs_data ADD COLUMN owner_id integer;ALTER TABLE 
gorfs.nfs_data ALTER COLUMN owner_id SET NOT NULL;ALTER TABLE gorfs.nfs_data 
ADD COLUMN file_id integer;ALTER TABLE gorfs.nfs_data ALTER COLUMN file_id SET 
NOT NULL;ALTER TABLE gorfs.nfs_data ADD COLUMN migration_path "text";ALTER 
TABLE gorfs.nfs_data ADD COLUMN migration_date timestamp with time zone;ALTER 
TABLE gorfs.nfs_data  ADD CONSTRAINT nfs_data_pkey PRIMARY KEY("file_id");

QUESTION:How could I create the Query?Any help would be great.
Thank you!


Updating...
1: select all the file ID's of gorfs inodes/segments etc for each file and put 
them in the new nfs_data table
2: Add a column "batch_number"   
3: Select 1 rows from the nfs_data table and give  them all the same batch 
number
4: Write some sql to select how many unique batch numbers there are so we know 
how many time the script will run.
If anyone can help, I'd appreciate.ThanksSaulo  
  

  

Re: [GENERAL] WIP: CoC V6

[Joshua D. Drake]

Hello,

I posted this earlier but ended up breaking my own silly rev scheme. So, 
I am going back to my silly rev scheme. Rev 6 it is:


tl;dr;

 * added being tolerant of opposing views
 * Removed excess wording from Grittner's version
 * Removed non-.org controlled spaces in first paragraph

== PostgreSQL Community Code of Conduct (CoC) ==

This document is intended to provide community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place for any person who is willing to contribute in
a safe, respectful, productive and collaborative way.  It applies
to all "collaborative space", which is defined as community
communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.).

* We are tolerant of people’s right to have opposing views.

* Participants must ensure that their language and actions are free
of personal attacks and disparaging personal remarks.

* When interpreting the words and actions of others, participants
should always assume good intentions.

* Participants must avoid sustained disruption of the collaborative
space, or any pattern of behavior which could reasonably be
considered harassment.


--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general