Re: [GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down

[Sylvain MARECHAL]

What is the best practice to make sure the DDL operation will
fail, possibly after a timeout, if one of the node is down?


statement_timeout


Ok. Thank-you for pointing this. I have just tried it, and this work 
great even for nodes that are not properly power off (plug removed).


I could check the state of the node before issuing the DDL
operation, but this solution is far from being perfect as the node
may fail right after this.


Correct, but it's still useful to do.

I'd check to see all nodes are connected in pg_stat_replication then 
I'd issue the DDL with a statement_timeout set.


Ok. For the first check, I was using 
|bdr.bdr_test_remote_connectback(/peer_dsn/, /local_dsn/), getting the 
dsn from the bdr.bdr_nodes table; but using the |pg_stat_replication 
table is problably quicker and simpler.


Thank-you again,

Sylvain

[GENERAL] Re: [pgsql-pkg-yum] Missing RHEL rpm(pg_catcheck-95) in postgres 9.5 repo.

[Devrim GÜNDÜZ]

Hi,

On Mon, 2016-01-18 at 06:26 +, Kaliappa, Karthic wrote:
> We are looking to upgrade our application from postgres 9.4 to 9.5,
> but we are unable to find the RPM named 'pg_catcheck-95' for RHEL (ht
> tp://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/).
> Whereas it is available for Fedora, we request your help in getting
> this rpm for RHEL as well, at the earliest.

Thanks for the heads up. Pushed to 9.5 repos.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR




signature.asc
Description: This is a digitally signed message part

[GENERAL] Error installing 9.5 on Win 2012 R2: data dir not created

[DerekW]

I am running Windows Server 2012 R2 x64 and upgrading from 9.4.5 to 9.5. I
was unable to get pg_upgrade to work so my upgrade path is:1) Backup with
pg_dumpall2) Uninstall 9.43) Install 9.54) Restore dataThis worked
sucessfully on one server. I am now having issues following the same
procedure on a second server, in that the /data directory is not being
created and I get the following error messages near the end of the install
procedure:*Failed to load SQL modules into the database cluster.Problem
running post-install step. Installation may not complete properly.Error
reading file C:/Program
Files/PostgreSQL/9.5/data/postgresql.conf*postgresql.conf doesn't exist
since the /data subdirectory does not exist. I have tried deleting and
re-creating the postgres user account, with no luck.Please can some help
with what is preventing the data directory being created.



--
View this message in context: 
http://postgresql.nabble.com/Error-installing-9-5-on-Win-2012-R2-data-dir-not-created-tp5882726.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Error installing 9.5 on Win 2012 R2: data dir not created

[Adrian Klaver]

On 01/18/2016 07:40 AM, DerekW wrote:

I am running Windows Server 2012 R2 x64 and upgrading from 9.4.5 to 9.5.
I was unable to get pg_upgrade to work so my upgrade path is: 1) Backup
with pg_dumpall 2) Uninstall 9.4 3) Install 9.5 4) Restore data This
worked sucessfully on one server. I am now having issues following the
same procedure on a second server, in that the /data directory is not


So what is different between the servers and/or method of installation?


being created and I get the following error messages near the end of the
install procedure: *Failed to load SQL modules into the database
cluster. Problem running post-install step. Installation may not
complete properly. Error reading file C:/Program
Files/PostgreSQL/9.5/data/postgresql.conf* postgresql.conf doesn't exist
since the /data subdirectory does not exist. I have tried deleting and


I know the installer is saying /data does not exist, but is that true or 
not?



re-creating the postgres user account, with no luck. Please can some
help with what is preventing the data directory being created.




--
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] CoC [Final]

[Joshua D. Drake]

Hello,

O.k. so I let every thing sit with V7 for several days and we have 
received no further feedback. I believe we have reached a point where we 
can reasonably consider this Final or at least Final Draft.


This final draft incorporates all reasonable feedback I have received as 
well as rewriting it in a more conversational tone from Kevin Grittner's 
efforts.


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

This document provides community guidelines for a safe, respectful, 
productive, and collaborative place for any person who is willing to 
contribute to the PostgreSQL community. 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 who disrupt the collaborative space, or participate in a 
pattern of behaviour which could be considered harassment will not be 
tolerated.


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] CoC [Final]

[Kevin Grittner]

On Mon, Jan 18, 2016 at 12:02 PM, Joshua D. Drake  
wrote:

> * Participants who disrupt the collaborative space, or participate in a
> pattern of behaviour which could be considered harassment will not be
> tolerated.

Personally, I was comfortable with the rest of it, but this one
made me squirm a little.  Could we spin that to say that those
behaviors will not be tolerated, versus not tolerating the people?
Maybe:

* Disruption of the collaborative space or any pattern of
behaviour which could be considered harassment will not be
tolerated.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] CoC [Final]

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Joshua D. Drake wrote:

> This final draft incorporates all reasonable feedback I have received as 
> well as rewriting it in a more conversational tone from Kevin Grittner's 
> efforts.

Looks great to me. Thanks for all your efforts in this.

- -- 
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201601181316
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAladLAkACgkQvJuQZxSWSsjOZwCgqKxe5Uw9ujJIwCzWj4YHfVm9
etQAmwa1R5P8iPONXCgV9OMDiAaaTu/5
=awgH
-END PGP SIGNATURE-




-- 
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] CoC [Final]

[Joshua D. Drake]

On 01/18/2016 10:15 AM, Kevin Grittner wrote:

On Mon, Jan 18, 2016 at 12:02 PM, Joshua D. Drake  
wrote:


* Participants who disrupt the collaborative space, or participate in a
pattern of behaviour which could be considered harassment will not be
tolerated.


Personally, I was comfortable with the rest of it, but this one
made me squirm a little.  Could we spin that to say that those
behaviors will not be tolerated, versus not tolerating the people?
Maybe:

* Disruption of the collaborative space or any pattern of
behaviour which could be considered harassment will not be
tolerated.


No argument from me. I think they both service the same gist.

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] CoC [Final]

[Karsten Hilbert]

On Mon, Jan 18, 2016 at 10:02:33AM -0800, Joshua D. Drake wrote:

> O.k. so I let every thing sit with V7 for several days and we have received
> no further feedback. I believe we have reached a point where we can
> reasonably consider this Final or at least Final Draft.

While the verbiage seems OK with me -- has there been
consensus as to whether we actually want/need a CoC ?

Thanks,
Karsten Hilbert
-- 
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] CoC [Final]

[Joshua D. Drake]

On 01/18/2016 10:38 AM, Karsten Hilbert wrote:

On Mon, Jan 18, 2016 at 10:02:33AM -0800, Joshua D. Drake wrote:


O.k. so I let every thing sit with V7 for several days and we have received
no further feedback. I believe we have reached a point where we can
reasonably consider this Final or at least Final Draft.


While the verbiage seems OK with me -- has there been
consensus as to whether we actually want/need a CoC ?


I believe this question is answered in the various threads.

Sincerely,

JD



Thanks,
Karsten Hilbert




--
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] CoC [Final]

[Stéphane Schildknecht]

On 18/01/2016 19:36, Joshua D. Drake wrote:
> On 01/18/2016 10:15 AM, Kevin Grittner wrote:
>> On Mon, Jan 18, 2016 at 12:02 PM, Joshua D. Drake  
>> wrote:
>>
>>> * Participants who disrupt the collaborative space, or participate in a
>>> pattern of behaviour which could be considered harassment will not be
>>> tolerated.
>>
>> Personally, I was comfortable with the rest of it, but this one
>> made me squirm a little.  Could we spin that to say that those
>> behaviors will not be tolerated, versus not tolerating the people?
>> Maybe:
>>
>> * Disruption of the collaborative space or any pattern of
>> behaviour which could be considered harassment will not be
>> tolerated.
> 
> No argument from me. I think they both service the same gist.
> 
> Sincerely,
> 
> JD
> 
> 
> 

I would also vote in favour of not tolerating the behaviour. I guess it would
be less open to critics than saying a participant is not tolerated...



-- 
Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
Loxodata - Conseil, expertise et formations
06.17.11.37.42


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

[GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[drum.lu...@gmail.com]

I've created a function that allows me to do an huge update.

But I need to limit this function. I need to do 50k rows (example) and then
stop it. After that I need to continue from the rows that I've stopped...
I'll have to modify the call function *select batch_number()* as well.

How can I do that? Using for?

The function below is already working, but, the table has 40m rows. And
it's taking years.

FUNCTION:

CREATE or REPLACE FUNCTION batch_number()
RETURNS INTEGER AS $$
declare
   batch_num integer;
   offset_num integer;begin
offset_num = 0;
batch_num = 1;

while (select true from gorfs.nfs_data where batch_number is null
limit 1) loop
with ids(id) as
(
select
file_id
from
gorfs.nfs_data
order by
file_id
offset offset_num
limit 1000
)
update
gorfs.nfs_data
set
batch_number = batch_num
from ids
where file_id = ids.id;

offset_num = offset_num + 1000;
batch_num = batch_num + 1;
end loop;

return batch_num;end
$$ language 'plpgsql';

[GENERAL] Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[David G. Johnston]

On Mon, Jan 18, 2016 at 2:37 PM, drum.lu...@gmail.com 
wrote:

> I've created a function that allows me to do an huge update.
>
> But I need to limit this function. I need to do 50k rows (example) and
> then stop it. After that I need to continue from the rows that I've
> stopped... I'll have to modify the call function *select batch_number()* as
> well.
>
> How can I do that? Using for?
>
It is customary to restrict emails to a single list or at least make others
aware when you do have a legitmate need to cross-post.

Specifically your post on -admin <“Break” in plpgsql Function - PostgreSQL
9.2>

Yes, it didn't belong on -admin in the first place but posting it here with
a different title and not indicating on either thread that the other exists
and/or is the valid one just makes it harder for others to follow along.


As for your general question I try to approach this problem in the
following manner:

SELECT however many of something that you need (FOR UPDATE)
UPDATE those selected to indicate that they have been seen
PROCESS them as needed
​repeat step 1 until it returns no records

​It doesn't always work - and given a sufficiently large number of records
it may be unadvisable - but it is set-oriented which is generally a plus in
SQL.

The other way to assign batches is to use the integer modulus operator
(e.g., 10 % 3 = 1 : read 3 *remainder of 1*) or integer division (10 / 3 =
3) to derive the batch number based upon an attribute of the data itself as
opposed to its order of appearance in a result set.

David J.


​

[GENERAL] Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[drum.lu...@gmail.com]

>
> It is customary to restrict emails to a single list or at least make
> others aware when you do have a legitmate need to cross-post.
>
> Specifically your post on -admin <“Break” in plpgsql Function - PostgreSQL
> 9.2>
>
> Yes, it didn't belong on -admin in the first place but posting it here
> with a different title and not indicating on either thread that the other
> exists and/or is the valid one just makes it harder for others to follow
> along.
>
>
Sorry about that. It wasn't on purpose... It won't happen again.


>
> As for your general question I try to approach this problem in the
> following manner:
>
> SELECT however many of something that you need (FOR UPDATE)
> UPDATE those selected to indicate that they have been seen
> PROCESS them as needed
> ​repeat step 1 until it returns no records
>
> ​It doesn't always work - and given a sufficiently large number of records
> it may be unadvisable - but it is set-oriented which is generally a plus in
> SQL.
>
> The other way to assign batches is to use the integer modulus operator
> (e.g., 10 % 3 = 1 : read 3 *remainder of 1*) or integer division (10 / 3
> = 3) to derive the batch number based upon an attribute of the data itself
> as opposed to its order of appearance in a result set.
>
> David J.
>
>
> ​
>
>

[GENERAL] Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[dinesh kumar]

Hello,

On Mon, Jan 18, 2016 at 1:37 PM, drum.lu...@gmail.com 
wrote:

> I've created a function that allows me to do an huge update.
>
> But I need to limit this function. I need to do 50k rows (example) and
> then stop it. After that I need to continue from the rows that I've
> stopped... I'll have to modify the call function *select batch_number()* as
> well.
>
> How can I do that? Using for?
>
> The function below is already working, but, the table has 40m rows. And
> it's taking years.
>
>
Do you need to run the function on any Staging(Not Production).  I mean, do
you want to run this batch processes on a single transaction.

If not, I had the similar problem, where I needed to implement a function,
which we can run in multiple sessions. I ran this function in one of the BI
servers, where we have around 5 Million records.

Find this
link
about the implementation details.

If your question was about "Using Loops", then please ignore my comments.

FUNCTION:
>
> CREATE or REPLACE FUNCTION batch_number()
> RETURNS INTEGER AS $$
> declare
>batch_num integer;
>offset_num integer;begin
> offset_num = 0;
> batch_num = 1;
>
> while (select true from gorfs.nfs_data where batch_number is null limit 
> 1) loop
> with ids(id) as
> (
> select
> file_id
> from
> gorfs.nfs_data
> order by
> file_id
> offset offset_num
> limit 1000
> )
> update
> gorfs.nfs_data
> set
> batch_number = batch_num
> from ids
> where file_id = ids.id;
>
> offset_num = offset_num + 1000;
> batch_num = batch_num + 1;
> end loop;
>
> return batch_num;end
> $$ language 'plpgsql';
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

[GENERAL] Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[drum.lu...@gmail.com]

On 19 January 2016 at 11:44, dinesh kumar  wrote:

> Hello,
>
> On Mon, Jan 18, 2016 at 1:37 PM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>> I've created a function that allows me to do an huge update.
>>
>> But I need to limit this function. I need to do 50k rows (example) and
>> then stop it. After that I need to continue from the rows that I've
>> stopped... I'll have to modify the call function *select batch_number()* as
>> well.
>>
>> How can I do that? Using for?
>>
>> The function below is already working, but, the table has 40m rows. And
>> it's taking years.
>>
>>
> Do you need to run the function on any Staging(Not Production).  I mean,
> do you want to run this batch processes on a single transaction.
>

I don't want to run this batch in a single transaction. I'm already doing
that and, as it has 40 million rows, it's taking years.

So, the new plan is:

1 - Select 50.000 rows and gives it a batch number.
2 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.
3 - Select *MORE* 50,000 rows and gives it a *NEW* batch number.
4 - etc  etc etc


>
> If not, I had the similar problem, where I needed to implement a function,
> which we can run in multiple sessions. I ran this function in one of the BI
> servers, where we have around 5 Million records.
>
> Find this
> link
> about the implementation details.
>
> If your question was about "Using Loops", then please ignore my comments.
>

Re: [GENERAL] Execute commands in single-user mode

[Jim Nasby]

On 1/10/16 3:44 PM, Andreas Joseph Krogh wrote:


It might be about time to come up with an extension that's a replacement
for large objects.

What would it take to fund such an extension?


Time and/or money.


It would "have to" support:
- Migrate existing LOs away from pg_largeobject
- Proper driver-support (JDBC)


Would probably be possible to extend JDBC (looks like that's what the 
current support does).



- Possible to exclude from pg_dump


That'd be up to the extension dump control semantics.


- Support pg_upgrade

>

And -hackers should agree on the goal to ultimately being merged into
core and replace pg_largeobject.


Well, there's a reason I suggested an extension. I think it's very 
unlikely -hackers would want to add another LO format to the database. 
Now-a-days, it's generally preferred to do most things as extensions, 
and only incorporate things in the backend that really can't be done 
with an extension.


If this theoretical new replacement for LOs took the world by storm and 
everyone was using it, maybe it'd be a different thing. The xml and JSON 
types are examples of that; they started life as add-ons and were 
eventually pulled in because they became extremely popular.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Error installing 9.5 on Win 2012 R2: data dir not created

[DerekW]

There are no differences between the 2 servers that I can imagine would stop
this install. Both were sucessfully running PostgrSQL 9.4.5 prior to the
update.

The /data directory is not even being created. After uninstalling there is
no /PostgreSQL subdirectory under C:/Program Files/. The installer is able
to create /PostgreSQL/9.5/ and subdirectories under this. Comparing the 2
installs the following are missing under C:/Program Files/PostgreSQL/9.5/ on
the unsucessfull install:
./data/
./gdal-data/ (does this only get created when PostGIS is installed?)
./utils/

My suspicion is that a security setting somewhere is preventing these being
created, but I don't knopw where to look.



--
View this message in context: 
http://postgresql.nabble.com/Error-installing-9-5-on-Win-2012-R2-data-dir-not-created-tp5882726p5882909.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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