Re: [GENERAL] Actual row order in UPDATE and SELECT FOR UPDATE

[Nikolai Zhubr]

Hello all,
16.02.2016 2:41, Vitaly Burovoy:
[...]

UPDATE deals with rows in order they are fetched from a heap. In a
common case it can be considered as unordered.

[...]

However SELECT can fetch rows in a specific order and locking by FOR
UPDATE clause is applied _after_ sorting. Then you can use CTE[1] with
Ok. So if I get it correctly, plain UPDATE statement by itself can not 
be constructed in such way that reliably avoids deadlocks in case of 
possibly overlapping concurrent updates. So in order to be safe, UPDATE 
statements will need to always be 'protected' by respective SELECT FOR 
UPDATE first. I'd suppose this fact deserves some more explicit mention 
in the manual, as it is not so obvious...


Thanks a lot for your code example and comprehensive advice.
I think I'm able to fix my deadlocks now.


Regards,
Nikolai


SELECT ... ORDER BY ... FOR UPDATE to pass all necessary data to the
UPDATE statement. Since UPDATE still deals with unordered rows they
have already locked, and parallel queries are waiting in SELECT
statement rather than in UPDATE:

WITH lck AS (
 SELECT
id,  -- for WHERE clause in UPDATE

-- you can do calculations here or in-place (see "field2" below)
field1 + 1 as field1,
...
 FROM your_table
 WHERE ...
 ORDER BY id  -- for example
 FOR UPDATE
)
UPDATE your_table t
SET
   field1=lck.field1,  -- lhs is always field of updatable table;
-- rhs must be pointed by a "table" if they are the same in both "tables"

   field2=field2 + 2, -- or if you make changes in-place and it doesn't appear
--in a table mentioned in "FROM" clause, you can avoid table/alias name
...
FROM lck
WHERE
 t.id=lck.id



Thank you,
Nikolai


[1]http://www.postgresql.org/docs/current/static/queries-with.html




--
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] Actual row order in UPDATE and SELECT FOR UPDATE

[Vitaly Burovoy]

On 2/15/16, Nikolai Zhubr  wrote:
> Hello all,
>
> I can't find any clear description of how to reliably figure and/or
> enforce specific row (locking) order within UPDATE and SELECT FOR UPDATE
> statements dealing with multiple rows.
> I'd like to get rid of some deadlocks (caused by share locks). While the
> manual explains locks and deadlocks themselves pretty fine (in e.g.
> http://www.postgresql.org/docs/9.5/static/explicit-locking.html
> ) it somehow avoids discussing multi-row updates there. On the other
> hand, the UPDATE section of the manual somehow avoids discussing actual
> update order and respective locking too.

UPDATE deals with rows in order they are fetched from a heap. In a
common case it can be considered as unordered.

> So is it defined anywhere explicitely? Or do I rather have to convert
> all multi-row UPDATE statements into single-row updates and then wrap
> them into e.g. plpgsql loops?

Not a good thought: it'll ruin performance at all.

> That would look quite strange...
> Any hints?

However SELECT can fetch rows in a specific order and locking by FOR
UPDATE clause is applied _after_ sorting. Then you can use CTE[1] with
SELECT ... ORDER BY ... FOR UPDATE to pass all necessary data to the
UPDATE statement. Since UPDATE still deals with unordered rows they
have already locked, and parallel queries are waiting in SELECT
statement rather than in UPDATE:

WITH lck AS (
SELECT
   id,  -- for WHERE clause in UPDATE

-- you can do calculations here or in-place (see "field2" below)
   field1 + 1 as field1,
   ...
FROM your_table
WHERE ...
ORDER BY id  -- for example
FOR UPDATE
)
UPDATE your_table t
SET
  field1=lck.field1,  -- lhs is always field of updatable table;
-- rhs must be pointed by a "table" if they are the same in both "tables"

  field2=field2 + 2, -- or if you make changes in-place and it doesn't appear
--in a table mentioned in "FROM" clause, you can avoid table/alias name
...
FROM lck
WHERE
t.id=lck.id

>
> Thank you,
> Nikolai

[1]http://www.postgresql.org/docs/current/static/queries-with.html
-- 
Best regards,
Vitaly Burovoy


-- 
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] Actual row order in UPDATE and SELECT FOR UPDATE

[David G. Johnston]

On Mon, Feb 15, 2016 at 3:17 PM, Nikolai Zhubr  wrote:

> Hello all,
>
> I can't find any clear description of how to reliably figure and/or
> enforce specific row (locking) order within UPDATE and SELECT FOR UPDATE
> statements dealing with multiple rows.


​SQL is a set-oriented language.  Sets do not have order.  Therefore I
don't understand your goal.  That said subqueries and ORDER BY may be
solution.

David J.
​

Re: [GENERAL] Actual row order in UPDATE and SELECT FOR UPDATE

[Adrian Klaver]

On 02/15/2016 02:17 PM, Nikolai Zhubr wrote:

Hello all,

I can't find any clear description of how to reliably figure and/or
enforce specific row (locking) order within UPDATE and SELECT FOR UPDATE
statements dealing with multiple rows.
I'd like to get rid of some deadlocks (caused by share locks). While the
manual explains locks and deadlocks themselves pretty fine (in e.g.
http://www.postgresql.org/docs/9.5/static/explicit-locking.html
) it somehow avoids discussing multi-row updates there. On the other
hand, the UPDATE section of the manual somehow avoids discussing actual
update order and respective locking too.
So is it defined anywhere explicitely? Or do I rather have to convert
all multi-row UPDATE statements into single-row updates and then wrap
them into e.g. plpgsql loops? That would look quite strange...
Any hints?


http://www.postgresql.org/docs/9.4/interactive/transaction-iso.html

Might also help if you give a code example of what you are trying to do?




Thank you,
Nikolai





--
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] Actual row order in UPDATE and SELECT FOR UPDATE

[Nikolai Zhubr]

Hello all,

I can't find any clear description of how to reliably figure and/or 
enforce specific row (locking) order within UPDATE and SELECT FOR UPDATE 
statements dealing with multiple rows.
I'd like to get rid of some deadlocks (caused by share locks). While the 
manual explains locks and deadlocks themselves pretty fine (in e.g.

http://www.postgresql.org/docs/9.5/static/explicit-locking.html
) it somehow avoids discussing multi-row updates there. On the other 
hand, the UPDATE section of the manual somehow avoids discussing actual 
update order and respective locking too.
So is it defined anywhere explicitely? Or do I rather have to convert 
all multi-row UPDATE statements into single-row updates and then wrap 
them into e.g. plpgsql loops? That would look quite strange...

Any hints?


Thank you,
Nikolai


--
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] Trouble installing PostGIS on Amazon Linux server

[Chris Mair]

I was told that "The amazon linux is compatible with Centos 6.x".  Does that 
correspond to RHEL 6? Is there a command I could use to find out?


Not quite.

Amazon Linux is RHEl/CentOS/Fedora derived, but it's not based on exactly 
RHEl/CentOS 6 or
exactly RHEl/CentOS 7.

This is its current libpoppler:

$ rpm -qf /usr/lib64/libpoppler.so.37
poppler-0.22.5-6.15.amzn1.x86_64

I don't know if this is an option to you, but CentOS 6 is available as an AMI 
on AWS,
so you could directly run that? Also AWS offers RDBMS as a service and they do 
have
PostgreSQL + PostGIS in their offerings (see "RDS").

Bye,
Chris.




--
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] Trouble installing PostGIS on Amazon Linux server

[John R Pierce]

On 2/15/2016 12:41 PM, Augori wrote:


I was told that "The amazon linux is compatible with Centos 6.x".  
Does that correspond to RHEL 6? Is there a command I could use to 
find out?


Is there a way I can force yum to install the RHEL 6 version?


I saw .el6. in several of your RPMs on an earlier post, so yes, you're 
running a RHEL/CentOS 6 derived system.


note the RPM version numbers don't necessarily directly match with the 
file versions.  on my CentOS 6 system, i have...


# rpm -qf /usr/lib64/libpoppler.so.5
poppler-0.12.4-3.el6_0.1.x86_64

poppler-0.12.4-3.el6_0.1.x86_64.rpm is from the centos6 base/updates 
repository.


--
john r pierce, recycling bits in santa cruz

[GENERAL] using npgsql and EF6 under asp.net5

[FarjadFarid(ChkNet)]

Hi All, 

 

I have managed to make npgsql with EF6 to work under asp.net5. 

 

Let me if you needs the info as to how to set it up. 

 

Best Regards

 


Farjad

 

 

 

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

[Augori]

Hi Devrim,

I was told that "The amazon linux is compatible with Centos 6.x".  Does
that correspond to RHEL 6? Is there a command I could use to find
out?

Is there a way I can force yum to install the RHEL 6 version?

Thank you,
Augori



On Mon, Feb 15, 2016 at 3:26 PM, Devrim GÜNDÜZ  wrote:

>
> Hi,
>
> Just checked my RHEL 6 and RHEL 7 boxes. RHEL 6 provides libpoppler.so.5,
> and
> RHEL 7 provides libpoppler.so.46 . I cannot find any reference to .37 :(
> Where
> did you get it from?
>
> I am not an Amazon AMI user -- is your version based on RHEL 6 or RHEL 7?
>
> Regards, Devrim
>
> On Mon, 2016-02-15 at 10:05 -0500, Augori wrote:
> > It installed poppler successfully but still gives the same error.  I did
> > notice that it installed libpoppler 37 and the postgis2_93 install error
> > seems to be looking for version 5
> > [root@ip-user]# ls /usr/lib64/libpo*
> > /usr/lib64/libpoppler-cpp.so/usr/lib64/libpoppler.so
> > /usr/lib64/libpoppler-cpp.so.0  /usr/lib64/libpoppler.so.37
> > /usr/lib64/libpoppler-cpp.so.0.2.0  /usr/lib64/libpoppler.so.37.0.0
> >
> > [root@ip-ec2-user]# yum install postgis2_93 --enablerepo=epel   (same
> > results both with and without the enable repo flag)
> > .
> > . (lots of stuff not included here)
> > .
> > Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
> >Requires: libpoppler.so.5()(64bit)
> >  You could try using --skip-broken to work around the problem
> >  You could try running: rpm -Va --nofiles --nodigest
> >
> > Perhaps I should uninstall the 37 version and install the 5.0
> version?  Any
> > idea how to get it to install the 5.0 version?
> >
> > Thanks
> >
> >
> > On Sun, Feb 14, 2016 at 11:14 PM, Chris Mair  wrote:
> >
> > >
> > > Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
> > > > Requires: libpoppler.so.5()(64bit)
> > > >   You could try using --skip-broken to work around the problem
> > > >   You could try running: rpm -Va --nofiles --nodigest
> > > >
> > >
> > > Hi,
> > >
> > > what happens if you try to install libpoppler (it is in the standard
> > > Amazon repo)?
> > >
> > > yum install poppler poppler-devel poppler-cpp poppler-cpp-devel
> > >
> > > Bye,
> > > Chris.
> > >
> > >
> > >
>
> --
> Devrim GÜNDÜZ
> Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
> Twitter: @DevrimGunduz , @DevrimGunduzTR
>
>
>

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

[Devrim GÜNDÜZ]

Hi,

Just checked my RHEL 6 and RHEL 7 boxes. RHEL 6 provides libpoppler.so.5, and
RHEL 7 provides libpoppler.so.46 . I cannot find any reference to .37 :( Where
did you get it from?

I am not an Amazon AMI user -- is your version based on RHEL 6 or RHEL 7?

Regards, Devrim

On Mon, 2016-02-15 at 10:05 -0500, Augori wrote:
> It installed poppler successfully but still gives the same error.  I did
> notice that it installed libpoppler 37 and the postgis2_93 install error
> seems to be looking for version 5
> [root@ip-user]# ls /usr/lib64/libpo*
> /usr/lib64/libpoppler-cpp.so/usr/lib64/libpoppler.so
> /usr/lib64/libpoppler-cpp.so.0  /usr/lib64/libpoppler.so.37
> /usr/lib64/libpoppler-cpp.so.0.2.0  /usr/lib64/libpoppler.so.37.0.0
> 
> [root@ip-ec2-user]# yum install postgis2_93 --enablerepo=epel   (same
> results both with and without the enable repo flag)
> .
> . (lots of stuff not included here)
> .
> Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
>    Requires: libpoppler.so.5()(64bit)
>  You could try using --skip-broken to work around the problem
>  You could try running: rpm -Va --nofiles --nodigest
> 
> Perhaps I should uninstall the 37 version and install the 5.0 version?  Any
> idea how to get it to install the 5.0 version?
> 
> Thanks
> 
> 
> On Sun, Feb 14, 2016 at 11:14 PM, Chris Mair  wrote:
> 
> > 
> > Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
> > > Requires: libpoppler.so.5()(64bit)
> > >   You could try using --skip-broken to work around the problem
> > >   You could try running: rpm -Va --nofiles --nodigest
> > > 
> > 
> > Hi,
> > 
> > what happens if you try to install libpoppler (it is in the standard
> > Amazon repo)?
> > 
> > yum install poppler poppler-devel poppler-cpp poppler-cpp-devel
> > 
> > Bye,
> > Chris.
> > 
> > 
> > 

-- 
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] Suggest note in index documentation about long running transactions

[Chris Travers]

Hi;

Today I ran into a question from a client as to why an index was not used.
The index had been freshly created and was on a relatively small table (16k
live rows, but 300k dead tuples).  The resulting sequential scan was taking
half a second.

I found that even when setting enable_seqscan to off it was still refusing
to use the index.  After reading carefully through the index documentation
yet again, it was not clear why it was not used.

After much research I came across an email by Tom Lane about how the HOT
enhancements in 8.3 meant that indexes might not be usable until after the
longest running transaction committed.  This turned out to be the culpret
(we had a transaction that took about 15 hours to complete and when it
committed the index was used).

It might help if there is a note that indexes in some cases cannot be used
until the min xid advances to the point where the index was created.

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Manage SCD 2 table using the INSERT --- ON CONFLICT

[Adrian Klaver]

On 02/15/2016 01:54 AM, Johann Kerdal wrote:

Hello,

I am trying to use the INSERT ON CONFLICT syntax to build an SCD 2 table
loader.

here are the behaviors I need to achieve:
SCD 2 table: TAB_OUT
new candidate records: TAB_IN

The table TAB_IN contains the new candidate records to be submitted to
TAB_OUT.
*Case 1*: The record presented by TAB_IN is already present in TAB_OUT
and the fields were not modified
-> DO NOTHING

*Case 2*: The record presented by TAB_IN is not present in TAB_OUT
-> INSERT NEW RECORD (*OK*)

*Case 3*: The record presented by TAB_IN is already presente in TAB_OUT
and the fields were modified
   -> CLOSE existing record in TAB_OUT(*OK*)
   -> /INSERT new RECORD (*KO*)/
/
/
I can't find a way to retrieve the list of records that were updated ON
CONFLICT. I have tried the RETURNING but, it is retrieving the list of
records processed either in the INSERT or the UPDATE.


I was with you until Case 3. Are you trying to UPDATE the existing 
record with a closed flag and INSERT the new modified record?




Am I missing something or it is not possible with this syntax?


What is the command you are using?


Thanks in advance

Johann





--
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] Postgresql Server Upgarde

[Felipe Santos]

2016-02-15 15:40 GMT-02:00 Melvin Davidson :

>
>
> On Tue, Feb 9, 2016 at 1:57 AM, subhan alimy  wrote:
>
>> Hello Everyone,
>>
>> I want to upgrade PostgreSQL 9.1 to PostgreSQL 9.4, currently my database
>> hold millions of records, shall anyone please help me the recommend steps
>> to
>> upgrade my servers without any technical impact.
>>
>> Thanks In Advance.
>>
>> --
>> View this message in context:
>> http://postgresql.nabble.com/Postgresql-Server-Upgarde-tp5886543.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
>>
>
>
> Please refer to the following documentation:
>
> http://www.postgresql.org/docs/9.4/static/pgupgrade.html
>
> CAVEAT EMPTOR: It is always advisable to have a valid pg_dump/backup
> before proceeding with pg_upgrade.
> pg_upgrade is faster than a reload, but in the event of unexplained
> problems, a backup is always good to have as a last resort.
> Also, practice upgrading in a development environment before attempting to
> do so in production.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


Hi Subhan Alimy,

I would recommend that, in an environment apart from production (maybe your
DEV env), you should:

1. install PG 9.4 in a different dir than PG 9.1
2. create a new cluster, apart from dev/test/qa/etc in a new data dir
3. do a dump from the PG 9.1 cluster
4. restore the dump to the PG 9.4 cluster
5. test your application on the new cluster

Given that you have enough storage space on the production environment, you
could use the same approach to upgrade your prod database.

Regards,

Felipe

Re: [GENERAL] how do you determine if you have a healthy database

[Adrian Klaver]

On 02/08/2016 04:28 PM, Gregery L. Thompson wrote:

Two quick questions

1.How do you determine if you have a healthy database?


I would say start by looking at the database logs. If you are seeing 
FATAL and PANIC errors something is not happy.


A lot of this depends on what you consider healthy?

There are conditions within the database, i.e. file corruption and then 
there are conditions outside of the database, memory issues, lack of 
space, etc. The two can also be connected, where bad memory or hard 
drive causes database file corruption.




2.It needs to be healthy before rebooting correct?


It depends on what is causing the problem. Postgres has a single user 
mode that can be started to fix some problems. Also if the database 
issue was that it was resource constrained and you fixed that then the 
database could be started.




Thanks

Greg

_Greg Thompson_

Sr. Database Administrator

Redflex Traffic Systems, Inc.

4402 W. Calle Lejos

Glendale, AZ 85310 USA

Ph: +1 (623) 207-2227

Cell: +1 (623) 221-3728

Fx: +1 (623) 207-2050

www.redflex.com

Making A Safer World!





--
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] Postgresql Server Upgarde

[Melvin Davidson]

On Tue, Feb 9, 2016 at 1:57 AM, subhan alimy  wrote:

> Hello Everyone,
>
> I want to upgrade PostgreSQL 9.1 to PostgreSQL 9.4, currently my database
> hold millions of records, shall anyone please help me the recommend steps
> to
> upgrade my servers without any technical impact.
>
> Thanks In Advance.
>
> --
> View this message in context:
> http://postgresql.nabble.com/Postgresql-Server-Upgarde-tp5886543.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
>


Please refer to the following documentation:

http://www.postgresql.org/docs/9.4/static/pgupgrade.html

CAVEAT EMPTOR: It is always advisable to have a valid pg_dump/backup before
proceeding with pg_upgrade.
pg_upgrade is faster than a reload, but in the event of unexplained
problems, a backup is always good to have as a last resort.
Also, practice upgrading in a development environment before attempting to
do so in production.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Help me get started with moving away from Firebird

[ioan ghip]

I was able to create all the domains, tables, views, etc, but I have
trouble creating stored procedures and triggers. Also, a question, does
Postgres support events, for example in Firebird I could do something like
this and then receive the event in the GUI:

  if ((NEW.MUSED_M_>=NEW.MLIMIT_M_) and (NEW.MLIMIT_M_>0) and
(NEW.ISACTIVE=1) and (NEW.FAXTOFAXFLAG=1)) then
  begin
POST_EVENT 'deactivate_f2f';
  end

Please help me translate the examples bellow so I can understand the
differences:


CREATE GENERATOR GENADMINID START WITH 0 INCREMENT BY 1;
SET GENERATOR GENADMINID TO 108;

CREATE TRIGGER B_UPDATE_COMPANY FOR COMPANY
ACTIVE AFTER UPDATE POSITION 10
AS
begin
  if (old.AGENTID != new.AGENTID) then
  begin
update USERS set USERS.AGENTID=new.AGENTID where USERS.COMPANYID =
new.COMPANYID;
  end
end

CREATE PROCEDURE GET_ATA_STATUS (
MAC VARCHAR(128),
NOW_D_ INTEGER)
RETURNS (
ATA_STATUS INTEGER)
AS
begin
  SELECT IIF((a.TIMESTAMP_D_ + a.EXPIRE) > :NOW_D_, 1, 0) FROM ATA a WHERE
a.ATAMAC = :MAC into :ATA_STATUS;
  SUSPEND;
end

Thanks a lot.

Re: [GENERAL] Windows performance

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sterpu Victor
Sent: Friday, February 12, 2016 10:38 AM
To: PostgreSQL General 
Subject: [GENERAL] Windows performance

Hello

Why is Postgres so slow on Windows compared to linux?
Can I do something to match the performance?
I have 2 servers:
- one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs in 17 
seconds
- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs in 2 
seconds
I run the query on the same database.

CPU is not used at max on the servers, RAM is fine.
Is there a problem with Windows? My gues is that Windows is not using the 
hardware resources as it should be.
Can I do something to fix this?

Thank you.


More details would be helpful:

-  PG version

-  PG config on both servers

-  Actual query you are using

Regards,
Igor Neyman

[GENERAL] Postgresql Server Upgarde

[subhan alimy]

Hello Everyone,

I want to upgrade PostgreSQL 9.1 to PostgreSQL 9.4, currently my database
hold millions of records, shall anyone please help me the recommend steps to
upgrade my servers without any technical impact.


Thanks In Advance.



--
View this message in context: 
http://postgresql.nabble.com/Postgresql-Server-Upgarde-tp5886543.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

[GENERAL] how do you determine if you have a healthy database

[Gregery L. Thompson]

Two quick questions


1.   How do you determine if you have a healthy database?


2.   It needs to be healthy before rebooting correct?

Thanks

Greg


Greg Thompson
Sr. Database Administrator
Redflex Traffic Systems, Inc.
4402 W. Calle Lejos
Glendale, AZ 85310 USA
Ph: +1 (623) 207-2227
Cell: +1 (623) 221-3728
Fx: +1 (623) 207-2050
www.redflex.com

Making A Safer World!







[cid:logo_new_a2f0a457-4caf-49f8-a807-9708b3a2f14711.png]


Gregery Thompson | Senior Database Administrator | Redflex Traffic Systems, Inc.
T +1 623 207 2227 | M +1 623 221 3728 | E gthomp...@redflex.com
5651 West Talavi Blvd, Suite 200, Glendale AZ 85306-1884, United States


Confidentiality Note:  This e-mail, and any attachment to it, is intended only 
for the use of the individual(s) or entity named on the e-mail, and may contain 
confidential or proprietary information (including copyrighted materials).  If 
the reader of is not an authorized recipient, you are hereby notified that 
reading it or further distributing it (other than to the author or the intended 
recipient) is prohibited and is potentially an infringement of the rights of 
the sender or intended recipient.  If you have received this e-mail in error, 
please immediately return it to the sender and delete it from your system. 
Thank you.

[GENERAL] Proper use of Groups and Users (Roles).

[Melvin Davidson]

Some years ago, while working at Computer Associates as a tech support
specialist for the Ingres database, I wrote a short article to explain the
proper use of Group and Userss in the database. I thought it would be
worthwhile to do the same for PostgreSQL, as I've seen a lot of cases where
this was not implemented properly. Since I am not found of Wiki's, I've
attached it here for sharing.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
Clarifying the use of Groups and Roles,

First, a little backgrond history. Prior to PostgreSQL v8.1, the division 
between a Group and a Role was much clearer. However, because “There is no 
CREATE GROUP statement in the SQL”, with 8.1, CREATE GROUP became an alias 
for CREATE ROLE, with the option to allow (or prevent) login, and CREATE USER 
was changed to CREATE ROLE.

Confused yet? Don’t worry, hopefully I’m going to clear up the distinction.

In the practical world of database use, a GROUP is a collection of USERS  
(ROLEs that can login. 
GROUPs normally are not given the option to login.

So to be a little more succinct:

Groups usually identify a class of users (roles) with similar functions. 
Consider the following example.

Since several users (roles) may all perform accounting functions, it makes 
sense to have an 'accounting' group (role). Likewise, managers who have extra 
privileges could be part of the 'officers' group (role). It is also possible 
for a user to belong to more than one group. However, for this example will use 
the simple case of just one group (role).

Creating Groups
Consider the following example.

A company has 3 different types of users: Order takers (group name orders), 
Administrative (group name admin) and managers (group name mgr). The list of 
users follows:

orders |admin  | mgr

Bob| Carol | Dick
Alice  | Ted   | Jane
Jocelyn| Edna  | 

According to the company rules, Order takers can only view and update the order 
table. 
Administrative users can view and update the employee table, and can view but 
not update the orders table. Managers have full access to both the orders table 
and the employee table.

To implement this, you would follow these steps: 
1.  Create the three groups:

CREATE ROLE orders WITH NOLOGIN;
CREATE ROLE admin  WITH NOLOGIN;
CREATE ROLE mgrWITH NOLOGIN;

Note: It is best to use lowercase for object names in PostgreSQL, otherwise 
they must be enclosed in quotes 

2.  GRANT the appropriate permissions to the groups:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO orders;
GRANT SELECT ON TABLE orders TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO mgr;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO mgr;

Note: I specifically did NOT use the GRANT ALL option in above. 
Although GRANT ALL, at first appears to simplify granting permissions, it is 
actually a very bad practice that is often misused. That is because doing so 
would also allow groups and ordinary users the following additional privileges: 
TRUNCATE, REFERENCES & TRIGGER. 
Only the table owner (usually the dba and/or postgres), should have those 
privileges. 

3.  Create the users and add them to the Groups..

CREATE ROLE bob WITH INHERIT LOGIN PASSWORD 'bobspw' IN ROLE orders;
CREATE ROLE alice   WITH INHERIT LOGIN PASSWORD 'alicespw'   IN ROLE orders;
CREATE ROLE jocelyn WITH INHERIT LOGIN PASSWORD 'jocelynspw' IN ROLE orders;
CREATE ROLE carol   WITH INHERIT LOGIN PASSWORD 'carolspw'   IN ROLE admin;
CREATE ROLE ted WITH INHERIT LOGIN PASSWORD 'tedspw' IN ROLE admin;
CREATE ROLE ednaWITH INHERIT LOGIN PASSWORD 'ednaspw'IN ROLE admin;
CREATE ROLE dickWITH INHERIT LOGIN PASSWORD 'dickspw'IN ROLE mgr;
CREATE ROLE janeWITH INHERIT LOGIN PASSWORD 'janespw'IN ROLE mgr;

Note: the INHERIT option is critical, otherwise the user would have to do a 
“SET ROLE group_name;” to obtain the group permissions

Now, any time the users/roles connect to the database, they automatically have 
the correct permissions to access the tables they need. This simplifies the 
task of maintaining user permissions, because whenever a new user enters the 
company, they only have to be created in the appropriate group, and there is no 
need to grant user specific rights to any tables.

-- 
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] Trouble installing postgresql server on Amazon Linux

[Pierre HILBERT]

Hello,

Are u sur that it's not a network issue ?
Maybe just try to telnet or check your ec2 security group.

Pierre

*Pierre HILBERT*
*Database Responsible | Decatec*
*Phone : +33 6 67 63 54 87*

On Sun, Feb 7, 2016 at 10:35 PM, Augori  wrote:

> Thanks for the reply.   Yes, it seems that you're right about having
> multiple pgdg93 repos,.
>
> # yum repolist
> Loaded plugins: priorities, update-motd, upgrade-helper
> Repository pgdg93 is listed more than once in the configuration
> Repository pgdg93-source is listed more than once in the configuration
> 8 packages excluded due to repository priority protections
> repo id  repo name
> status
> !amzn-main/latestamzn-main-Base
> 5,443+50
> !amzn-updates/latest amzn-updates-Base
> 692+48
> percona-release-noarch/latestPercona-Release YUM repository -
> n   37
> percona-release-x86_64/latest/x86_64 Percona-Release YUM repository - x
> 724+4
> pgdg93/x86_64PostgreSQL 9.3 - Amazon Linux AMI
> 284+4
> repolist: 7,180
>
> Can you suggest how I can go about removing the duplicate?
> # ls -l /etc/yum.repos.d /etc/yum.repos.d
> /etc/yum.repos.d:
> total 36
> -rw-r--r-- 1 root root  696 Feb  2 17:52 amzn-main.repo
> -rw-r--r-- 1 root root  328 Sep 16 00:48 amzn-nosrc.repo
> -rw-r--r-- 1 root root  694 Sep 16 00:48 amzn-preview.repo
> -rw-r--r-- 1 root root  714 Feb  2 17:52 amzn-updates.repo
> -rw-r--r-- 1 root root  957 Mar  1  2013 epel.repo
> -rw-r--r-- 1 root root 1056 Mar  1  2013 epel-testing.repo
> -rw-r--r-- 1 root root 2501 Sep 22  2014 percona-release.repo
> -rw-r--r-- 1 root root  490 Oct 21 09:07 pgdg-93-ami201503.repo
> -rw-r--r-- 1 root root  442 May  8  2013 pgdg-93-redhat.repo
>
>
> ... In fact, it seems like both of them must be the wrong repos?  So I
> need to remove these?
> # rpm -e pgdg-93-redhat.repo
> error: package pgdg-93-redhat.repo is not installed
> #  rpm -e pgdg-93-ami201503.repo
> error: package pgdg-93-ami201503.repo is not installed
>
>
>
>
> On Sun, Feb 7, 2016 at 2:52 PM, Adrian Klaver 
> wrote:
>
>> On 02/07/2016 11:18 AM, Augori wrote:
>>
>>> Hello,
>>>
>>> I'm trying to install postgresql on an Amazon Linux AMI release
>>> 2015.09.  I got the rpm for Amazon Linux AMI 2015.03 - x86_64 under
>>> PostgreSQL 9.3 linked here:
>>> http://yum.postgresql.org/repopackages.php
>>>
>>> But a yum install  complains that it requires systemd-units, which I
>>> haven't figure out how to get.  The yum command and result:
>>>
>>> ]# yum -y install postgresql93 postgresql93-server postgresql93-contrib
>>> postgresql93-libs
>>> Loaded plugins: priorities, update-motd, upgrade-helper
>>> Repository pgdg93 is listed more than once in the configuration
>>>
>>
>> The above seems to be part of the problem. Looks like you have two
>> repositories for pgdg93.
>>
>> Are you sure the AMI did not already have a repo for Postgres?
>>
>> Repository pgdg93-source is listed more than once in the configuration
>>> 8 packages excluded due to repository priority protections
>>> Resolving Dependencies
>>> --> Running transaction check
>>> ---> Package postgresql93.x86_64 0:9.3.10-1PGDG.rhel7 will be installed
>>>
>>
>> The above would seem to be the second problem and related to the first.
>> When I peeked into:
>>
>>
>> http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-ami201503-93-9.3-2.noarch.rpm
>>
>> it showed that it was looking for:
>>
>> https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-$basearch
>>
>>
>> Seems you are trying to install a RH 7 package on an OS that is looking
>> for a RH 6 package. In other words yum is finding the RH 7 repo before the
>> RH 6 repo.
>>
>>
>>
>> ---> Package postgresql93-contrib.x86_64 0:9.3.10-1PGDG.rhel7 will be
>>> installed
>>> ---> Package postgresql93-libs.x86_64 0:9.3.10-1PGDG.rhel7 will be
>>> installed
>>> ---> Package postgresql93-server.x86_64 0:9.3.10-1PGDG.rhel7 will be
>>> installed
>>> --> Processing Dependency: systemd-units for package:
>>> postgresql93-server-9.3.10-1PGDG.rhel7.x86_64
>>> --> Processing Dependency: systemd-units for package:
>>> postgresql93-server-9.3.10-1PGDG.rhel7.x86_64
>>> --> Finished Dependency Resolution
>>> Error: Package: postgresql93-server-9.3.10-1PGDG.rhel7.x86_64 (pgdg93)
>>> Requires: systemd-units
>>>   You could try using --skip-broken to work around the problem
>>>   You could try running: rpm -Va --nofiles --nodigest
>>>
>>>
>>> 
>>>
>>> The --skip-broken option returns Errno 256
>>> # yum -y install --skip-broken postgresql93 postgresql93-server
>>> postgresql93-contrib postgresql93-libs
>>> .
>>> .
>>> .
>>> Error downloading packages:
>>>postgresql93-contrib-9.3.10-1PGDG.rhel7.x86_64: failure:
>>> postgresql93-contrib-9.3.10-1PGDG.rhel7.x86_64.rpm from pgdg93: [Errno
>>> 256] No more mirrors to try.
>>>

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > Mart�n Marqu�s wrote:
> >> This really gives little use for recovery_target_xid. :(
> 
> > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL,
> > which has the correct XIDs.  It's obviously a worse solution though from
> > the user's POV, because it's hard to figure out what WAL record
> > corresponds to the change you care about ...
> 
> To what extent does the commit_ts infrastructure fix this?

I don't think it does at all.  You could try to find out the XID using a
timestamp you obtain from the log file (knowing that the lookups are the
opposite way, i.e. you give it an XID and it returns a timestamp); but
if that's the scenario, I think it's simpler to use the timestamp in
recovery_target_time directly.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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] Trouble installing PostGIS on Amazon Linux server

[Augori]

It installed poppler successfully but still gives the same error.  I did
notice that it installed libpoppler 37 and the postgis2_93 install error
seems to be looking for version 5
[root@ip-user]# ls /usr/lib64/libpo*
/usr/lib64/libpoppler-cpp.so/usr/lib64/libpoppler.so
/usr/lib64/libpoppler-cpp.so.0  /usr/lib64/libpoppler.so.37
/usr/lib64/libpoppler-cpp.so.0.2.0  /usr/lib64/libpoppler.so.37.0.0

[root@ip-ec2-user]# yum install postgis2_93 --enablerepo=epel   (same
results both with and without the enable repo flag)
.
. (lots of stuff not included here)
.
Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
   Requires: libpoppler.so.5()(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Perhaps I should uninstall the 37 version and install the 5.0 version?  Any
idea how to get it to install the 5.0 version?

Thanks


On Sun, Feb 14, 2016 at 11:14 PM, Chris Mair  wrote:

>
> Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
>> Requires: libpoppler.so.5()(64bit)
>>   You could try using --skip-broken to work around the problem
>>   You could try running: rpm -Va --nofiles --nodigest
>>
>
> Hi,
>
> what happens if you try to install libpoppler (it is in the standard
> Amazon repo)?
>
> yum install poppler poppler-devel poppler-cpp poppler-cpp-devel
>
> Bye,
> Chris.
>
>
>

Re: [GENERAL] PosgreSQL Security Architecture

[FarjadFarid(ChkNet)]

LJK, Firstly thanks for bring this topic up. As many companies have to handle 
attacks on their network. 

Adrian thanks for the links specially the one covering connection pooling in 
the first link. Answered by Pavel Stehule . 
Definitely something to avoid. 

I have done a fair bit of work on security issues from Ecommerce perspective. 
Hope these comment might be of some value.

Please avoid giving guarantees on security issues. Rather try to give a level 
of confidence based on current understanding of known attacks, their purpose, 
potential solutions, their costs and mitigating issues and very importantly 
performance issues. These days specially on ecommerce. Search engines penalise 
site's ranking based on their performance. So a site could be very secure but 
it could end up losing business.

Can I break this question up a little bit. 

* Repeated security reports indicate that by far the most serious security 
breaches are insider jobs or lack of adherence to proper internal procedures. 
* DDOS attacks tend to have two aims. Simply stopping the website or causing 
stack over flow. Hoping to catch the server on the hop. These are not DB 
engines job and are best handled elsewhere before the transaction reaches the 
DB engine. 
* Man in the middle is a big problem. Too often under estimated. 
As an example.  I live in UK. Tracing a Tcp/Ip transaction going from a UK 
location to a UK site can end up going to an Eastern block country then US 
before returning back to UK.  Last I heard. AOL's (or now talk talk) traffic 
still goes to outside UK before returning back to UK. 
Mitigating potential actions of man in the middle is a collective job of the 
O/S, network layer, firewall and the DB.
*Several years ago there was a research done where during authentication 
process the tcp/ip traffic was intercepted by a fake man in the middle and fed 
back to the server with the result of creating a new session. (sorry don't 
recall the O/S or SSL they used) The potential solution lies at the network 
layer not the DB engine. 
*OpenSSL. For a small outlay of few hundred dollars it is safer to have an 
higher over security of the system by securing the communication layer. I 
normally use a mid-cost company like Comodo's SSL service. They tie the server, 
ip address to SSL certificate. 
*Personally I use a customised encryption techniques on the database. In the 
event of a failure or security breach at say a cloud computing partner. These 
will be a lot more difficult to crack but also easier to recover. My past 
experience is that often DB engines (not postgresql)  find it difficult to 
recover encrypted fields that were encrypted by *DB engine itself*. Please bear 
in mind this has an impact on searching and indexing and performance so be 
careful but can be resolved.
*Logs files needs to be just as secure as the main db files. 
*Lastly but certainly not least. Adrian raised a very important point in his 
post about use of applications for connection pooling. It goes without saying 
that all application installed on the server needs to be checked for potential 
security issues but

Good luck. I hope these might be of some use to you and others. 



Farjad Farid


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Albe Laurenz
Sent: 15 February 2016 10:48
To: 'John R Pierce *EXTERN*'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PosgreSQL Security Architecture

John R Pierce wrote:
> On 2/12/2016 5:20 AM, Lesley Kimmel wrote:
>> Thanks for the reply Laurenz. Of course the first thing that I 
>> thought of to prevent man-in-the-middle was SSL. However, I also like 
>> to try to address the issue in a way that seems to get at what they 
>> are intending. It seemed to me that they wanted to do some 
>> configuration within the database related to session IDs.
> 
> when the connection is broken, the process exits and the session ceases
> to exist. there are no 'session IDs' to speak of (they are process
> IDs instead, but a new process mandates new authentication, there's no 
> residual authorizations associated with a PID).

I might be misunderstanding, but is there any connection to a man-in-the-middle 
attack?

Without SSL, anybody who can tap into the TCP communication can inject SQL 
statements.  No session ID is required.

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



-- 
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] PosgreSQL Security Architecture

[Albe Laurenz]

John R Pierce wrote:
> On 2/12/2016 5:20 AM, Lesley Kimmel wrote:
>> Thanks for the reply Laurenz. Of course the first thing that I thought
>> of to prevent man-in-the-middle was SSL. However, I also like to try
>> to address the issue in a way that seems to get at what they are
>> intending. It seemed to me that they wanted to do some configuration
>> within the database related to session IDs.
> 
> when the connection is broken, the process exits and the session ceases
> to exist. there are no 'session IDs' to speak of (they are process
> IDs instead, but a new process mandates new authentication, there's no
> residual authorizations associated with a PID).

I might be misunderstanding, but is there any connection to a
man-in-the-middle attack?

Without SSL, anybody who can tap into the TCP communication can inject
SQL statements.  No session ID is required.

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

[GENERAL] Manage SCD 2 table using the INSERT --- ON CONFLICT

[Johann Kerdal]

Hello,

I am trying to use the INSERT ON CONFLICT syntax to build an SCD 2 table
loader.

here are the behaviors I need to achieve:
SCD 2 table: TAB_OUT
new candidate records: TAB_IN

The table TAB_IN contains the new candidate records to be submitted to
TAB_OUT.
*Case 1*: The record presented by TAB_IN is already present in TAB_OUT and
the fields were not modified
   -> DO NOTHING

*Case 2*: The record presented by TAB_IN is not present in TAB_OUT
   -> INSERT NEW RECORD (*OK*)

*Case 3*: The record presented by TAB_IN is already presente in TAB_OUT and
the fields were modified
  -> CLOSE existing record in TAB_OUT(*OK*)
  -> *INSERT new RECORD (KO)*

I can't find a way to retrieve the list of records that were updated ON
CONFLICT. I have tried the RETURNING but, it is retrieving the list of
records processed either in the INSERT or the UPDATE.

Am I missing something or it is not possible with this syntax?

Thanks in advance

Johann