Re: [GENERAL] PostgresSQL and HIPAA compliance

[Stephen Cook]

On 2016-06-17 14:09, Paul Jungwirth wrote:
> On 06/17/2016 03:03 AM, Alex John wrote:
>> RDS is a prime candidate except for the fact that they have explicitly
>> stated that the Postgres engine is *not* HIPAA compliant.
> 
> More precisely, it is not covered by the BAA Amazon will sign.
> 
> I've helped several companies run HIPAA-compliant Postgres on regular
> EC2 instances (which *are* covered by your BAA, as long as they are
> dedicated instances---which do cost more). So you just have to do some
> of the server work yourself. If you are making the rest of your app
> HIPAA-compliant anyway, it shouldn't add a large burden to do Postgres
> that way too. Make sure your access rules are good, use SSL for the
> connections, put it on an encrypted disk (easy these days with encrypted
> EBS volumes), etc.
> 
> Slightly more effort but still very doable is handling requirements for
> auditing accesses and changes. How you do this probably depends on the
> rest of your stack.
> 
> Yours,
> Paul
> 

This is what we do, we have dedicated EC2 instances for PostgreSQL
storing PHI. From my point of view, it's the same as any other server
running Linux (I can SSH in, or tunnel my DB connection). To be honest
I'd rather have it this way than deal with the RDS interface.

Try to avoid those HIPAA compliance meetings though, they are terrible
and long.


-- Stephen




-- 
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] OT hardware recommend

[Gavin Flower]

On 18/06/16 14:23, Scott Marlowe wrote:
On Fri, Jun 17, 2016 at 2:36 PM, Andy Colson > wrote:


Hi all.

I have access to quite a few laptop HD's (10 to 15 of them at
least), and thought that might make a neat test box that might
have some good IO speed.

Needs to be cheap though, so linux with software raid, rack mount
preferred but not required.

Anyone have any experience with anything like that?  $2K might be
possible, painful, but possible.

Suggestions?


Sell them all and buy a couple of 800G SSDs? :)


You can get 4TB SSD's now - but somewhat pricey!!!

(besides, you'd still need backups)



--
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] OT hardware recommend

[Scott Marlowe]

On Fri, Jun 17, 2016 at 2:36 PM, Andy Colson  wrote:

> Hi all.
>
> I have access to quite a few laptop HD's (10 to 15 of them at least), and
> thought that might make a neat test box that might have some good IO speed.
>
> Needs to be cheap though, so linux with software raid, rack mount
> preferred but not required.
>
> Anyone have any experience with anything like that?  $2K might be
> possible, painful, but possible.
>
> Suggestions?


Sell them all and buy a couple of 800G SSDs? :)

Re: [GENERAL] UUID and Enum columns in exclusion constraints

[Paul Jungwirth]

Hi Adam,

On 06/17/2016 03:01 PM, Adam Brusselback wrote:

Just wondering what others have done for using enum or uuid columns in
exclusion constraints?
[snip]
And as a closing note on this, I really can't wait until these are
supported types for gist indexes.


Here is some work I did to add support for uuid types, with help from 
Ildus Kurbangaliev and Teodor Sigaev:


https://commitfest.postgresql.org/10/332/

I think it was basically done, but the last patch was never reviewed. 
Here is the mailing list thread:


http://postgresql.nabble.com/Review-GiST-support-for-UUIDs-td5865845.html

I would love to rebase that to the current code and re-submit. Maybe 
this weekend. :-)


Paul



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

[GENERAL] UUID and Enum columns in exclusion constraints

[Adam Brusselback]

Just wondering what others have done for using enum or uuid columns in
exclusion constraints?

I have a solution now, but I just wanted to see what others have ended up
doing as well and see if what i'm doing is sane.  If i'm doing something
unsafe, or you know of a better way, please chime in.

For enum columns, I use a function in the constraint to convert the enum
value to an oid.  The function is defined as such:

> CREATE OR REPLACE FUNCTION enum_to_oid(
> _enum_schema text,
> _enum_name text,
> _enum anyenum)
>   RETURNS oid AS
> $BODY$
> SELECT e.oid
> FROM pg_type t
> INNER JOIN pg_enum e
> ON t.oid = e.enumtypid
> INNER JOIN pg_catalog.pg_namespace n
> ON n.oid = t.typnamespace
> WHERE true
> AND n.nspname = _enum_schema
> AND t.typname = _enum_name
> AND e.enumlabel = _enum::text;
> $BODY$
>   LANGUAGE sql STABLE;


For uuid columns, I use another function in the constraint to convert it to
a bytea type defined here:

> CREATE OR REPLACE FUNCTION uuid_to_bytea(_uuid uuid)
>   RETURNS bytea AS
> $BODY$
>  select decode(replace(_uuid::text, '-', ''), 'hex');
> $BODY$
>   LANGUAGE sql IMMUTABLE;


And i'd use these functions in the constraint like this:

> CONSTRAINT claim_product_reason_code_active_range_excl EXCLUDE
>   USING gist (uuid_to_bytea(claim_product_id) WITH =,
> enum_to_oid('enum'::text, 'claim_reason_type'::text, claim_reason_type)
> WITH =, enum_to_oid('enum'::text, 'claim_reason_code'::text,
> claim_reason_code) WITH =, active_range WITH &&)


And as a closing note on this, I really can't wait until these are
supported types for gist indexes.  It would be great not to have to play
games like this to have exclusion constraints on my tables just because I
am using uuids instead of ints, and enums instead of lookup tables (when an
enum really fits the problem well).

Re: [GENERAL] OT hardware recommend

[John R Pierce]

On 6/17/2016 2:33 PM, John W Higgins wrote:

http://www.ebay.com/itm/2U-24-bay-2-5-Supermicro-Server-X8DTH-iF-2x-Xeon-Quad-Core-32GB-RAM-SAS2-216EL1-/222132081393?hash=item33b81a92f1:g:UzYAAOSwR5dXSQVw

With it being 2U you can then pop out the motherboard and go with 
anything more modern you wanted in terms of the motherboard/cpu/ram.


I would, however, also buy a LSI/Avago SAS 9207-8i card and remove that 
3ware 9750 'hardware' raid controller, which you can probably get $200 
for on ebay.


--
john r pierce, recycling bits in santa cruz



--
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] OT hardware recommend

[Gavin Flower]

On 18/06/16 08:36, Andy Colson wrote:

Hi all.

I have access to quite a few laptop HD's (10 to 15 of them at least), 
and thought that might make a neat test box that might have some good 
IO speed.


Needs to be cheap though, so linux with software raid, rack mount 
preferred but not required.


Anyone have any experience with anything like that?  $2K might be 
possible, painful, but possible.


Suggestions?

Thanks for your time,

-Andy


It would be a good idea to say what country you are in, and what city 
(or locality).


What I know about Auckland in New Zealand may not be relevant to you...  :-)


Cheers,
Gavin



--
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] OT hardware recommend

[John W Higgins]

I would start with something like this

http://www.ebay.com/itm/2U-24-bay-2-5-Supermicro-Server-X8DTH-iF-2x-Xeon-Quad-Core-32GB-RAM-SAS2-216EL1-/222132081393?hash=item33b81a92f1:g:UzYAAOSwR5dXSQVw

With it being 2U you can then pop out the motherboard and go with anything
more modern you wanted in terms of the motherboard/cpu/ram.

$1200 all in would certainly get you a nice system with that as the base.

John


On Fri, Jun 17, 2016 at 1:36 PM, Andy Colson  wrote:

> Hi all.
>
> I have access to quite a few laptop HD's (10 to 15 of them at least), and
> thought that might make a neat test box that might have some good IO speed.
>
> Needs to be cheap though, so linux with software raid, rack mount
> preferred but not required.
>
> Anyone have any experience with anything like that?  $2K might be
> possible, painful, but possible.
>
> Suggestions?
>
> 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
>

[GENERAL] OT hardware recommend

[Andy Colson]

Hi all.

I have access to quite a few laptop HD's (10 to 15 of them at least), 
and thought that might make a neat test box that might have some good IO 
speed.


Needs to be cheap though, so linux with software raid, rack mount 
preferred but not required.


Anyone have any experience with anything like that?  $2K might be 
possible, painful, but possible.


Suggestions?

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] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

[Tom Lane]

"Erdmann, Markus @ Bellevue"  writes:
> We’re trying to debug a performance issue affecting our staging database, and 
> we’ve narrowed it down to a difference in the query optimizer in 9.5.2. 
> Upgrading to 9.5 is important for us because we need the ability to import 
> foreign schemas.

I think the core of the problem is the large rowcount estimation error
here:

>   ->  Bitmap Index Scan on 
> transactions_transaction_c69e55a4  (cost=0.00..18.02 rows=161 width=0) 
> (actual time=20.153..20.153 rows=269021 loops=1)
> Index Cond: ((date_created >= 
> '2010-01-01'::date) AND (date_created <= '2015-12-31'::date))

That's a pretty simple condition and it's hard to believe that 9.5 does it
much differently than 9.4 did.  Perhaps you forgot to ANALYZE, or were
using a larger statistics target in the 9.4 installation?

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] PostgresSQL and HIPAA compliance

[Mike Sofen]

-Original Message-
From: Alex John
Sent: Friday, June 17, 2016 3:04 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgresSQL and HIPAA compliance

Hello, I have a few questions regarding the use of PostgreSQL and HIPAA
compliance. I work for a company that plans on storing protected health
information (PHI) on our servers. We have looked at various solutions for
doing so, and RDS is a prime candidate except for the fact that they have
explicitly stated that the Postgres engine is *not* HIPAA compliant.

Users on the IRC channel generally say that the guidelines are more catered
towards building better firewalls and a sane access policy, but I would like
to know if there is anything within the implementation of Postgres itself
that violates said compliance.

If anyone works at a similar company and utilizes postgresql to store PHI,
please let me know.

Thank you,
  Alex

-

HIPAA compliance does not specify (ever) the technical solution to meet the
requirements, so ANY datastore that can be properly managed within the
context of HIPAA compliance is legal and allowed.  Ignore IRCs and search on
securing PHI on relational databases, you'll find lots of details around
data access roles, documentation, processes, data obfuscation, etc.

Mike






-- 
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] PostgresSQL and HIPAA compliance

[Paul Jungwirth]

On 06/17/2016 03:03 AM, Alex John wrote:

RDS is a prime candidate except for the fact that they have explicitly
stated that the Postgres engine is *not* HIPAA compliant.


More precisely, it is not covered by the BAA Amazon will sign.

I've helped several companies run HIPAA-compliant Postgres on regular 
EC2 instances (which *are* covered by your BAA, as long as they are 
dedicated instances---which do cost more). So you just have to do some 
of the server work yourself. If you are making the rest of your app 
HIPAA-compliant anyway, it shouldn't add a large burden to do Postgres 
that way too. Make sure your access rules are good, use SSL for the 
connections, put it on an encrypted disk (easy these days with encrypted 
EBS volumes), etc.


Slightly more effort but still very doable is handling requirements for 
auditing accesses and changes. How you do this probably depends on the 
rest of your stack.


Yours,
Paul







--
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] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

[David G. Johnston]

On Thu, Jun 16, 2016 at 8:40 PM, Erdmann, Markus @ Bellevue <
markus.erdm...@cbre.com> wrote:

> Hello,
>
> We’re trying to debug a performance issue affecting our staging database,
> and we’ve narrowed it down to a difference in the query optimizer in 9.5.2.
> Upgrading to 9.5 is important for us because we need the ability to import
> foreign schemas.
>
> This is the query we’re running:
>
> CREATE TEMP TABLE tmp_joined_transactions_9gkgptn5xcp9 ( transaction_id
> integer PRIMARY KEY );
>
> INSERT INTO tmp_joined_transactions_9gkgptn5xcp9 (transaction_id)
>

​This...
​

> SELECT DISTINCT ON ("transactions_transaction"."id")
> "transactions_transaction"."id"
>

​​DISTINCT is a code smell.  DISTINCT ON less so - it helps to avoid
self-joins - but your inclusion of ON here is pointless since the only
output column is "id".

​As written there should be no way to get duplicate "id"s into the output
result.  Or, if the tmp_joined_transactions relationship is 1-to-many you
should instead use a semi-join instead of an inner join.

FROM "transactions_transaction" , "tmp_joined_transactions_75chlsokrsev"
> WHERE
>

​Here...​

(NOT ("transactions_transaction"."id"
> IN (SELECT U0."id" AS Col1
> FROM "transactions_transaction" U0
> LEFT OUTER JOIN "transactions_commission" U1
> ON ( U0."id" = U1."transaction_id" )
> WHERE U1."id" IS NULL))
>

​Making this an anti-join (NOT EXISTS instead of NOT IN) stands out as an
easy improvement to try:

​It also makes the logic clearer since you seem to have a double-negative
here which means you really want a semi-join (which I wrote below)

WHERE EXISTS (SELECT 1 FROM transactions_commission WHERE
transactions_transaction.id = transactions_commission.transaction_id)

I won't promise this gives the same answer...I don't have enough spare
brain power or the ability to test it...but its seems correct.

AND "transactions_transaction"."date_created" >= '2010-01-01'::date
> AND "transactions_transaction"."date_created" <= '2015-12-31'::date
> AND "transactions_transaction"."deal_status" IN (1)
>

​Also...
​


> AND (transactions_transaction.id =
> tmp_joined_transactions_75chlsokrsev.transaction_id))
>

​This is style but I'm really a fan of using ANSI JOIN syntax...turning the
above into a 

ORDER BY "transactions_transaction"."id" ASC;
>
>
​The regression itself someone else would need to comment on.

David J.

Re: [GENERAL] Re: regarding schema only migration from sqlserver to postgres with runmtk.sh

[Durgamahesh Manne]

On Fri, Jun 17, 2016 at 2:16 PM, Durgamahesh Manne <
maheshpostgr...@gmail.com> wrote:

> hi sir
>
> as you mentioned above
>
> members in pgsql mailing list  were not frustrating  about my tone & mail
> which i sent related to postgres
>
> as i believe they are always happy to serve for postgres
>
> as well as i like very much postgres
>
> On Thu, Jun 16, 2016 at 9:33 PM, Sameer Kumar 
> wrote:
>
>> Disclaimer: My company is a partner of EnterpriseDB and provides services
>> and consultation on EnterpriseDB tools
>>
>> On Thu, 16 Jun 2016, 11:52 p.m. Neil Anderson, 
>> wrote:
>>
>>> On 2016-06-16 11:42 AM, Durgamahesh Manne wrote:
>>> > i got another error even i specified -targetdbtype
>>> > like
>>> > ./runMTK.sh -sourcedbtype sqlserver -targetdbtype postgresql
>>> > -targetSchema public -schemaOnly -allTables dbo
>>> > Running EnterpriseDB Migration Toolkit (Build 49.0.4) ...
>>
>>
>> You have to take a pause and note that people on this list are frustrated
>> with you for your tone and top posting. In case the complain has gone
>> unregistered or has not made sense, please read this out
>> https://en.m.wikipedia.org/wiki/Posting_style
>>
>> Make sure you don't top post
>>
>> > Source database connectivity info...
>>> > conn =jdbc:jtds:sqlserver://
>>> fxserver.trustfort.com:49883/DataFeedHandler
>>> > 
>>> > user =trustfort
>>> > password=**
>>> > Target database connectivity info...
>>> > conn =jdbc:postgresql://192.168.168.201:5432/raghu
>>> > 
>>> > user =postgres
>>> > password=**
>>> > Connecting with source SQL Server database server...
>>> > Connected to Microsoft SQL Server, version '10.50.1600'
>>> > Connecting with target Postgres database server...
>>> > Exception in thread "main" java.lang.NoClassDefFoundError:
>>> > org/postgresql/Driver
>>>
>>
>> You need to ensure that postgreSQL jdbc jar files are placed in your
>> class path or under $JRE_HOME/lib/ext
>>
>> You might have already done this for SQL Server, do the same for
>> PostgreSQL too.
>>
>>
>> > at
>>> > com.edb.dbhandler.postgresql.PGConnection.(PGConnection.java:32)
>>> > at
>>> com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:228)
>>> > at
>>> >
>>> com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:5987)
>>> > at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3376)
>>> > at com.edb.MigrationToolkit.main(MigrationToolkit.java:1700)
>>> > Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > On Thu, Jun 16, 2016 at 8:59 PM, Neil Anderson
>>> > > wrote:
>>> >
>>> > On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:
>>> >
>>> > yes sir
>>> >
>>> > as per above discussion..i already looked pdf postgres plus
>>> > guide you
>>> > mentioned
>>>
>>
>> It is best that you contact EbterpriseDB support or someone who provides
>> services for EDB tools.
>>
>> >
>>> > as well as i already set password for postgres user associated
>>> with
>>> > postgres db
>>> >
>>> > On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
>>> > 
>>> > >> > >> wrote:
>>> >
>>> > hi
>>> > sir
>>> > as per above discussion same error repeated even mentioned
>>> ip
>>> > address of hostname
>>> >
>>> > ./runMTK.sh -sourcedbtype sqlserver -targetSchema public
>>> > -schemaOnly
>>> > -allTables dbo
>>> >
>>> >
>>> > TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>>> > 
>>> > 
>>> > TARGET_DB_USER=postgres
>>> > TARGET_DB_PASSWORD=*
>>> >
>>> >
>>> >
>>> > Connecting with source SQL Server database server...
>>> > Connected to Microsoft SQL Server, version '10.50.1600'
>>> > Connecting with target EnterpriseDB database server...
>>> > MTK-10045: The URL specified for the "target" database is
>>> > invalid.
>>> > Check the connectivity credentials.
>>> > Stack Trace:
>>> > com.edb.MTKException: MTK-10045: The URL specified for the
>>> > "target"
>>> > database is invalid.
>>> > Check the connectivity credentials.
>>> >
>>> > On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
>>> > >> > 
>>> > >> > >> wrote:
>>> >
>>> >   

Re: [GENERAL] PostgresSQL and HIPAA compliance

[Joshua D. Drake]

On 06/17/2016 03:03 AM, Alex John wrote:

Hello, I have a few questions regarding the use of PostgreSQL and HIPAA
compliance. I work for a company that plans on storing protected health
information (PHI) on our servers. We have looked at various solutions for doing
so, and RDS is a prime candidate except for the fact that they have explicitly
stated that the Postgres engine is *not* HIPAA compliant.



Correct but that isn't a Postgres problem, it is an RDS one.


Users on the IRC channel generally say that the guidelines are more catered
towards building better firewalls and a sane access policy, but I would like to
know if there is anything within the implementation of Postgres itself that
violates said compliance.


No.



If anyone works at a similar company and utilizes postgresql to store PHI,
please let me know.



We do (see sig) for multiple companies and it is fully compliant.

Your issue isn't PostgreSQL.

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] PostgresSQL and HIPAA compliance

[James Keener]

The method you use to store the data is irrelevant. Access to your network.
Logging. If you're encrypting the disk. How is the application presenting
this data. What kind of ACLs are you using. Asking if PG is good to store
HIPAA data is exactly as useful as asking if you can even store HIPAA data.
There are so many more important things to consider.

RDS is a hosted service. They don't have all the guarentees you'd  want for
PHI. I'm sure they're MySQL engine probably has similar warnings.

Jim

On Fri, Jun 17, 2016 at 6:03 AM, Alex John  wrote:

> Hello, I have a few questions regarding the use of PostgreSQL and HIPAA
> compliance. I work for a company that plans on storing protected health
> information (PHI) on our servers. We have looked at various solutions for
> doing
> so, and RDS is a prime candidate except for the fact that they have
> explicitly
> stated that the Postgres engine is *not* HIPAA compliant.
>
> Users on the IRC channel generally say that the guidelines are more catered
> towards building better firewalls and a sane access policy, but I would
> like to
> know if there is anything within the implementation of Postgres itself that
> violates said compliance.
>
> If anyone works at a similar company and utilizes postgresql to store PHI,
> please let me know.
>
> Thank you,
>   Alex
>
> --
> 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] PostgresSQL and HIPAA compliance

[Steve Atkins]

> On Jun 17, 2016, at 3:03 AM, Alex John  wrote:
> 
> Hello, I have a few questions regarding the use of PostgreSQL and HIPAA
> compliance. I work for a company that plans on storing protected health
> information (PHI) on our servers. We have looked at various solutions for 
> doing
> so, and RDS is a prime candidate except for the fact that they have explicitly
> stated that the Postgres engine is *not* HIPAA compliant.

There's nothing fundamental to postgresql that would make HIPAA compliance
difficult, and *probably* nothing major with the way it's deployed on RDS. 
Actual
certification takes time and money, though.

> 
> Users on the IRC channel generally say that the guidelines are more catered
> towards building better firewalls and a sane access policy, but I would like 
> to
> know if there is anything within the implementation of Postgres itself that
> violates said compliance.
> 
> If anyone works at a similar company and utilizes postgresql to store PHI,
> please let me know.

EnterpriseDB are helping provide HIPAA compliant postgresql on AWS; it
might be worth having a chat with them.

http://www.enterprisedb.com/postgres-plus-edb-blog/fred-dalrymple/postgres-meets-hipaa-cloud
http://www.slideshare.net/EnterpriseDB/achieving-hipaa-compliance-with-postgres-plus-cloud-database

Cheers,
  Steve



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

[GENERAL] PostgresSQL and HIPAA compliance

[Alex John]

Hello, I have a few questions regarding the use of PostgreSQL and HIPAA
compliance. I work for a company that plans on storing protected health
information (PHI) on our servers. We have looked at various solutions for doing
so, and RDS is a prime candidate except for the fact that they have explicitly
stated that the Postgres engine is *not* HIPAA compliant.

Users on the IRC channel generally say that the guidelines are more catered
towards building better firewalls and a sane access policy, but I would like to
know if there is anything within the implementation of Postgres itself that
violates said compliance.

If anyone works at a similar company and utilizes postgresql to store PHI,
please let me know.

Thank you,
  Alex

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

[GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

[Erdmann, Markus @ Bellevue]

Hello,

We’re trying to debug a performance issue affecting our staging database, and 
we’ve narrowed it down to a difference in the query optimizer in 9.5.2. 
Upgrading to 9.5 is important for us because we need the ability to import 
foreign schemas.

This is the query we’re running:

CREATE TEMP TABLE tmp_joined_transactions_9gkgptn5xcp9 ( transaction_id integer 
PRIMARY KEY );

INSERT INTO tmp_joined_transactions_9gkgptn5xcp9 (transaction_id)
SELECT DISTINCT ON ("transactions_transaction"."id") 
"transactions_transaction"."id"
FROM "transactions_transaction" , "tmp_joined_transactions_75chlsokrsev"
WHERE (NOT ("transactions_transaction"."id"
IN (SELECT U0."id" AS Col1
FROM "transactions_transaction" U0
LEFT OUTER JOIN "transactions_commission" U1
ON ( U0."id" = U1."transaction_id" )
WHERE U1."id" IS NULL))
AND "transactions_transaction"."date_created" >= '2010-01-01'::date
AND "transactions_transaction"."date_created" <= '2015-12-31'::date
AND "transactions_transaction"."deal_status" IN (1)
AND (transactions_transaction.id = 
tmp_joined_transactions_75chlsokrsev.transaction_id))
ORDER BY "transactions_transaction"."id" ASC;

And this is the EXPLAIN ANALYZE output in 9.5.2:

Insert on tmp_joined_transactions_9gkgptn5xcp9  (cost=42111.29..42991.60 rows=1 
width=4) (actual time=470236.029..470236.029 rows=0 loops=1)
  ->  Unique  (cost=42111.29..42991.59 rows=1 width=4) (actual 
time=1109.636..470222.609 rows=4652 loops=1)
->  Nested Loop  (cost=42111.29..42991.59 rows=1 width=4) (actual 
time=1109.635..470219.259 rows=4652 loops=1)
  Join Filter: (transactions_transaction.id = 
tmp_joined_transactions_75chlsokrsev.transaction_id)
  Rows Removed by Join Filter: 1153472704
  ->  Index Only Scan using 
tmp_joined_transactions_75chlsokrsev_pkey on 
tmp_joined_transactions_75chlsokrsev  (cost=0.28..224.61 rows=5355 width=4) 
(actual time=0.016..4.829 rows=4652 loops=1)
Heap Fetches: 4652
  ->  Materialize  (cost=42111.01..42686.66 rows=1 width=4) (actual 
time=0.240..51.639 rows=247953 loops=4652)
->  Bitmap Heap Scan on transactions_transaction  
(cost=42111.01..42686.65 rows=1 width=4) (actual time=1109.580..1238.034 
rows=247953 loops=1)
  Recheck Cond: ((date_created >= '2010-01-01'::date) 
AND (date_created <= '2015-12-31'::date))
  Filter: ((NOT (hashed SubPlan 1)) AND (deal_status = 
1))
  Rows Removed by Filter: 21068
  Heap Blocks: exact=8073
  ->  Bitmap Index Scan on 
transactions_transaction_c69e55a4  (cost=0.00..18.02 rows=161 width=0) (actual 
time=20.153..20.153 rows=269021 loops=1)
Index Cond: ((date_created >= 
'2010-01-01'::date) AND (date_created <= '2015-12-31'::date))
  SubPlan 1
->  Hash Right Join  (cost=8799.57..42092.98 rows=1 
width=4) (actual time=852.012..1086.671 rows=3395 loops=1)
  Hash Cond: (u1.transaction_id = u0.id)
  Filter: (u1.id IS NULL)
  Rows Removed by Filter: 995248
  ->  Seq Scan on transactions_commission u1  
(cost=0.00..27020.63 rows=1039763 width=8) (actual time=0.003..221.750 
rows=1039763 loops=1)
  ->  Hash  (cost=8395.92..8395.92 rows=32292 
width=4) (actual time=153.984..153.984 rows=272663 loops=1)
Buckets: 131072 (originally 32768)  
Batches: 4 (originally 1)  Memory Usage: 3425kB
->  Seq Scan on 
transactions_transaction u0  (cost=0.00..8395.92 rows=32292 width=4) (actual 
time=0.003..71.988 rows=272663 loops=1)
Planning time: 0.410 ms
Execution time: 470237.400 ms

And this is the EXPLAIN ANALYZE output in 9.4.5:

Insert on tmp_joined_transactions_9gkgptn5xcp9  (cost=88544.31..88576.62 
rows=2154 width=4) (actual time=1356.065..1356.065 rows=0 loops=1)
  ->  Unique  (cost=88544.31..88555.08 rows=2154 width=4) (actual 
time=1347.480..1350.548 rows=4715 loops=1)
->  Sort  (cost=88544.31..88549.70 rows=2154 width=4) (actual 
time=1347.477..1348.432 rows=4715 loops=1)
  Sort Key: transactions_transaction.id
  Sort Method: quicksort  Memory: 414kB
  ->  Hash Join  (cost=72700.01..88425.06 rows=2154 width=4) 
(actual time=1107.077..1345.650 rows=4715 loops=1)
Hash Cond: (transactions_transaction.id = 
tmp_joined_transactions_75chlsokrsev.transaction_id)
->  Seq Scan on transactions_transaction  
(cost=72565.61..87199.11 rows=122287 width=4) (actual time=1104.855..1269.783 
rows=251736 loops=1)
  Filter: ((NOT (hashed SubPlan 1)) AND (date_created 
>= '2010-01-01'::date) AND (date_created <= '2015-12-31'::date) AND 

Re: ***SPAM*** Re: [GENERAL] random huge delay when recreate a VIEW or FUNCTION

[Adrian Klaver]

On 06/17/2016 09:01 AM, Catalin Maftei wrote:

Please do not top post:

https://en.wikipedia.org/wiki/Posting_style


well,

the for your reply,

we have PG on linux ubuntu 14.04 distribution, but pls remember this
issue started when our DB size increase to 3-5GB, now it is 16GB.


See Merlins post about locking:

https://www.postgresql.org/message-id/CAHyXU0zm5N6WjnPuxc%3Dzx6ihDfkZai3BMS2WiR1%2BituV%3Dm8gFg%40mail.gmail.com



this happend only when we use "*CREATE OR REPLACE"*


since I can run a query and get reply all the time in less than 1sec,
why do you consider "CREATE A VIEW" on the remote server is a bad idea?


It is not necessarily a bad idea. Just that running the command locally 
on the server takes the whole intervening network out of the loop and 
establishes a base point to work from. Troubleshooting is as much about 
eliminating things as finding things.




I have 1Gb connection between my laptop (windows 7/10) and remote server.


Best regards,
Catalin Maftei
www.plationline.eu
www.livrarionline.ro
www.c-solution.biz

Skype: c-solution
Skype: catalinmaftei
Mobile: +40723 338 598

On 6/17/2016 2:55 AM, Melvin Davidson wrote:



On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei
<cata...@plationline.eu> wrote:

hei Adrian,

thx for your reply,

this is an example:

"CREATE OR REPLACE VIEW feedback.get_answers_set AS
 SELECT f.awb || '/' || r.dulapid as "awb",
q.qid,
q.question,
a.aid,
a.answer,
q.questionareid,
f.stamp_created
, c.first_name
, l.referinta_expeditor
FROM feedback.answers a
 JOIN feedback.questions q ON a.qid = q.qid
 JOIN feedback.feedback f ON f.qid = q.qid AND f.aid = a.aid
 join public.livrari_details ld on ld.awb=f.awb
 join public.livrari l on l.livrareid = ld.livrareid
 join public.customers c on l.shipto_custkey = c.custkey
 join dulap.rezervare r on r.rezid = l.rezervareid;

ALTER TABLE feedback.get_answers_set
  OWNER TO postgres;"


I use PGADMIN 1.22.1

my server is remote and is replicated Master-Slave.


my team report this random delay all the time when we recreate
VIEWS and FUNCTIONS.



Best regards,
Catalin Maftei
www.plationline.eu 
www.livrarionline.ro 
www.c-solution.biz 

Skype: catalinmaftei
Mobile: +40723 338 598 

On 6/15/2016 7:36 AM, Adrian Klaver wrote:

On 06/14/2016 02:59 PM, Catalin Maftei wrote:

when I recreate a VIEW or FUNCTION with a small change I get:


What are the actual statements you are running?

What program are you running them from?

Is the server you are running the command against local or remote?




Query returned successfully with no result in 03:58 minutes.


we have pg 9.4

--
Best regards,
Catalin Maftei
www.plationline.eu 
www.livrarionline.ro 
www.c-solution.biz 

Skype: catalinmaftei
Mobile: +40723 338 598 









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


*You have not provided information as to the O/S of the PostgreSQL
server or your PgAdmin remote system (I suspect Windows), but
regardless, you
are just asking for trouble by doing development over a remote connection.
You should verify there is no delay by connecting directly to the
PostgreSQL server and testing the CREATE OR REPLACE there. I suspect
you will have none or very minimal delay.
Hopefully PostgreSQL is on a Linux O/S and you can use Putty for
Windows
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
 to
connect directly
and develop.
*--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.





--
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: ***SPAM*** Re: [GENERAL] random huge delay when recreate a VIEW or FUNCTION

[Melvin Davidson]

On Fri, Jun 17, 2016 at 12:01 PM, Catalin Maftei 
wrote:

> well,
>
> the for your reply,
>
> we have PG on linux ubuntu 14.04 distribution, but pls remember this issue
> started when our DB size increase to 3-5GB, now it is 16GB.
>
> this happend only when we use "*CREATE OR REPLACE"*
>
>
> since I can run a query and get reply all the time in less than 1sec, why
> do you consider "CREATE A VIEW" on the remote server is a bad idea?
>
> I have 1Gb connection between my laptop (windows 7/10) and remote server.
>
>
> Best regards,
> Catalin Mafteiwww.plationline.euwww.livrarionline.rowww.c-solution.biz
>
> Skype: c-solution
> Skype: catalinmaftei
> Mobile: +40723 338 598
>
> On 6/17/2016 2:55 AM, Melvin Davidson wrote:
>
>
>
> On Wed, Jun 15, 2016 at 12:49 AM, Catalin Maftei <
> cata...@plationline.eu> wrote:
>
>> hei Adrian,
>>
>> thx for your reply,
>>
>> this is an example:
>>
>> "CREATE OR REPLACE VIEW feedback.get_answers_set AS
>>  SELECT f.awb || '/' || r.dulapid as "awb",
>> q.qid,
>> q.question,
>> a.aid,
>> a.answer,
>> q.questionareid,
>> f.stamp_created
>> , c.first_name
>> , l.referinta_expeditor
>> FROM feedback.answers a
>>  JOIN feedback.questions q ON a.qid = q.qid
>>  JOIN feedback.feedback f ON f.qid = q.qid AND f.aid = a.aid
>>  join public.livrari_details ld on ld.awb=f.awb
>>  join public.livrari l on l.livrareid = ld.livrareid
>>  join public.customers c on l.shipto_custkey = c.custkey
>>  join dulap.rezervare r on r.rezid = l.rezervareid;
>>
>> ALTER TABLE feedback.get_answers_set
>>   OWNER TO postgres;"
>>
>>
>> I use PGADMIN 1.22.1
>>
>> my server is remote and is replicated Master-Slave.
>>
>>
>> my team report this random delay all the time when we recreate VIEWS and
>> FUNCTIONS.
>>
>>
>>
>> Best regards,
>> Catalin Maftei
>> www.plationline.eu
>> www.livrarionline.ro
>> www.c-solution.biz
>>
>> Skype: catalinmaftei
>> Mobile: +40723 338 598
>>
>> On 6/15/2016 7:36 AM, Adrian Klaver wrote:
>>
>>> On 06/14/2016 02:59 PM, Catalin Maftei wrote:
>>>
 when I recreate a VIEW or FUNCTION with a small change I get:

>>>
>>> What are the actual statements you are running?
>>>
>>> What program are you running them from?
>>>
>>> Is the server you are running the command against local or remote?
>>>
>>>
>>>
>>>
 Query returned successfully with no result in 03:58 minutes.


 we have pg 9.4

 --
 Best regards,
 Catalin Maftei
 www.plationline.eu
 www.livrarionline.ro
 www.c-solution.biz

 Skype: catalinmaftei
 Mobile: +40723 338 598





>>>
>>>
>>
>>
>> --
>> Sent via pgsql-general mailing list ( 
>> pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>
>
>
>
> *You have not provided information as to the O/S of the PostgreSQL server
> or your PgAdmin remote system (I suspect Windows), but regardless, you are
> just asking for trouble by doing development over a remote connection. You
> should verify there is no delay by connecting directly to the PostgreSQL
> server and testing the CREATE OR REPLACE there. I suspect you will have
> none or very minimal delay. Hopefully PostgreSQL is on a Linux O/S and you
> can use Putty for Windows
> http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
>  to
> connect directly and develop. *--
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
>
I have personally used PgAdmin amd Putty on WIndows against a remote
PostgreSQL server on Ubuntu. I have noticed a large delay when using
PgAdmin versus Putty. Therefore I can only advise you that you are better
off connecting directly with Putty via ssh.

Have you tried what I suggested?

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

Re: [GENERAL] Question about RUM-index

[Oleg Bartunov]

On Fri, Jun 17, 2016 at 4:50 PM, Andreas Joseph Krogh 
wrote:

> På fredag 17. juni 2016 kl. 15:47:08, skrev Oleg Bartunov <
> obartu...@gmail.com>:
>
>
>
> On Fri, Jun 17, 2016 at 3:52 PM, Andreas Joseph Krogh 
> wrote:
>>
>> På fredag 17. juni 2016 kl. 13:53:34, skrev Oleg Bartunov <
>> obartu...@gmail.com>:
>>
>>
>>
>> On Fri, Jun 17, 2016 at 2:10 PM, Oleg Bartunov 
>> wrote:
>>>
>>>
>>>
>>> On Fri, Jun 17, 2016 at 9:32 AM, Andreas Joseph Krogh <
>>> andr...@visena.com> wrote:

 På torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes <
 jeff.ja...@gmail.com>:

 On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh <
 andr...@visena.com> wrote:
>
> Hi.
>
> First; Is this the correct forum to ask questions about the Postgres
> Pro's new RUM-index?
>
> If not, please point me to the right forum.
>

 I think that https://github.com/postgrespro/rum/issues might be the
 best forum.


 Oleg and friends; Should we use GitHub-issues as forum (one issue per
 question/thread?), pgsql-general or something else?

>>>
>>> Andreas,
>>>
>>> we are hardly working on our internal version of rum and will open it
>>> after resolving some issues. I think the best place to discuss it is
>>> -hackers.
>>>
>>
>> Ah, as someone corrected me, we are working hard !
>>
>>
>> He he, I figured that was what you meant:-)
>>
>> Ok, so basically - will RUM-index support the same indexing-properties as
>> GIN (being able to index tsvector, BIGINT-arrays, JSONB etc.) *and* be
>> able to use index for sorting on ie. timestamp, tsrank or some
>> BIGINT-column?
>>
>> Like my example, will it be possible to issue a query like this:
>>
>>
>> SELECT del.id
>> , del.sent
>> FROM delivery del
>> WHERE 1 = 1  AND del.fts_all @@ 
>> to_tsquery('simple', 'hi:*')
>>   AND del.folder_id = ANY(ARRAY[2,3]::BIGINT[])  
>>   ORDER BY  del.sent DESC LIMIT 101 OFFSET 0;
>>
>>
>> and have it use *one* RUM-index so the whole operation, including
>> sorting, is as efficient as possible?
>>
>>
>
> we have implementation for timestamp. One need to write opclass to deal
> with arrays, it shouldn't be difficult.
>
>
> Will the opclass dealing with bigint-arrays also handle the single-element
> case, that is only one bigint?
>

this is up to opclass author.



>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>

Re: [GENERAL] Question about RUM-index

[Andreas Joseph Krogh]

På fredag 17. juni 2016 kl. 15:47:08, skrev Oleg Bartunov >:
    On Fri, Jun 17, 2016 at 3:52 PM, Andreas Joseph Krogh > wrote: På fredag 17. juni 2016 kl. 13:53:34, skrev 
Oleg Bartunov >:
    On Fri, Jun 17, 2016 at 2:10 PM, Oleg Bartunov > wrote:     On Fri, Jun 17, 2016 at 9:32 AM, 
Andreas Joseph Krogh> wrote: På 
torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes >:
On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh > wrote: Hi.
 
First; Is this the correct forum to ask questions about the Postgres Pro's new 
RUM-index?
 
If not, please point me to the right forum.
 
I think that https://github.com/postgrespro/rum/issues 
 might be the best forum.



 
Oleg and friends; Should we use GitHub-issues as forum (one issue per 
question/thread?), pgsql-general or something else?
 
Andreas,
  
we are hardly working on our internal version of rum and will open it after 
resolving some issues. I think the best place to discuss it is -hackers.



 
Ah, as someone corrected me, we are working hard !



 
He he, I figured that was what you meant:-)
 
Ok, so basically - will RUM-index support the same indexing-properties as GIN 
(being able to index tsvector, BIGINT-arrays, JSONB etc.) and be able to use 
index for sorting on ie. timestamp, tsrank or some BIGINT-column?
 
Like my example, will it be possible to issue a query like this:
 
SELECT del.id  , del.sent FROM delivery del WHERE 1 = 1 AND 
del.fts_all @@to_tsquery('simple', 'hi:*') AND del.folder_id = ANY(ARRAY[2,3]::
BIGINT[]) ORDER BY del.sent DESC LIMIT 101 OFFSET 0;  


and have it use one RUM-index so the whole operation, including sorting, is as 
efficient as possible?
 
 
we have implementation for timestamp. One need to write opclass to deal with 
arrays, it shouldn't be difficult.



 
Will the opclass dealing with bigint-arrays also handle the single-element 
case, that is only one bigint?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Question about RUM-index

[Oleg Bartunov]

On Fri, Jun 17, 2016 at 3:52 PM, Andreas Joseph Krogh 
wrote:

> På fredag 17. juni 2016 kl. 13:53:34, skrev Oleg Bartunov <
> obartu...@gmail.com>:
>
>
>
> On Fri, Jun 17, 2016 at 2:10 PM, Oleg Bartunov 
> wrote:
>>
>>
>>
>> On Fri, Jun 17, 2016 at 9:32 AM, Andreas Joseph Krogh > > wrote:
>>>
>>> På torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes <
>>> jeff.ja...@gmail.com>:
>>>
>>> On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh <
>>> andr...@visena.com> wrote:

 Hi.

 First; Is this the correct forum to ask questions about the Postgres
 Pro's new RUM-index?

 If not, please point me to the right forum.

>>>
>>> I think that https://github.com/postgrespro/rum/issues might be the
>>> best forum.
>>>
>>>
>>> Oleg and friends; Should we use GitHub-issues as forum (one issue per
>>> question/thread?), pgsql-general or something else?
>>>
>>
>> Andreas,
>>
>> we are hardly working on our internal version of rum and will open it
>> after resolving some issues. I think the best place to discuss it is
>> -hackers.
>>
>
> Ah, as someone corrected me, we are working hard !
>
>
> He he, I figured that was what you meant:-)
>
> Ok, so basically - will RUM-index support the same indexing-properties as
> GIN (being able to index tsvector, BIGINT-arrays, JSONB etc.) *and* be
> able to use index for sorting on ie. timestamp, tsrank or some
> BIGINT-column?
>
> Like my example, will it be possible to issue a query like this:
>
>
> SELECT del.id
> , del.sent
> FROM delivery del
> WHERE 1 = 1  AND del.fts_all @@ 
> to_tsquery('simple', 'hi:*')
>   AND del.folder_id = ANY(ARRAY[2,3]::BIGINT[])   
>  ORDER BY  del.sent DESC LIMIT 101 OFFSET 0;
>
>
> and have it use *one* RUM-index so the whole operation, including
> sorting, is as efficient as possible?
>
>

we have implementation for timestamp. One need to write opclass to deal
with arrays, it shouldn't be difficult.



> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>

Re: ***SPAM*** Re: [GENERAL] random huge delay when recreate a VIEW or FUNCTION

[Merlin Moncure]

On Thu, Jun 16, 2016 at 6:55 PM, Melvin Davidson  wrote:
 You have not provided information as to the O/S of the PostgreSQL
server or your PgAdmin remote system (I suspect Windows), but
regardless, you
> are just asking for trouble by doing development over a remote connection.
> You should verify there is no delay by connecting directly to the PostgreSQL 
> server and testing the CREATE OR REPLACE there. I suspect you will have none 
> or very minimal delay.
> Hopefully PostgreSQL is on a Linux O/S and you can use Putty for Windows 
> http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html to connect 
> directly
> and develop.

I responded on his other thread.  Locks need to be ruled out before
investigating other problems.   Exotic causes of this problem could be
storage problems or weird network issues (especially on windows).  But
locks need to be ruled out first.

merlin


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

[GENERAL] Creating or modifying a tsquery from PL/pgSQL

[Tobia Conforto]

I'd like to convert a plain search string into a tsquery, much like 
plainto_tsquery(), but enabling prefix searches (:*) for every word.

I've come up with this:

select regexp_replace(
   plainto_tsquery('english', 'text to search')::text,
   '''(?= |$)', ''':*', 'g'
   )::tsquery;
--> 'text':* & 'search':*

Is this the best way to do it, without writing C code? Can I otherwise 
manipulate the tsquery data structure from PL/pgSQL without converting it 
to/from text? Would you recommend a different approach?

-Tobia

-- 
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] Table ordering in pg_dump

[Vick Khera]

On Thu, Jun 16, 2016 at 10:32 AM, Jean-Francois Prieur 
wrote:

> 1) Is there any way to tell pg_dump the order in which the tables should
> be dumped?
> 2) Am I correct to assume that if I use the --disable-trigger option in
> pg_restore it should mitigate the problem? This only works on a data-only
> dump per the documentation, so if I am doing the initial copy of the
> database to the new server, will data-only give me everything I need or is
> it only good for incremental updates to an already running copy of a
> database?
> 3) Alternatively, is there a psql command that I could run on the new
> server to disable all triggers/constraint checking, run the restore and
> then re-enable them?
> 4) Since we are in a lab environment, I can shut down the server and copy
> the folder containing the database to the new server. Is this kosher? I
> could then try the data-only dump and restore in 2) to keep them updated.
>
> Sorry for the noobish questions, thank you for your time. Coming from
> mysql so the concepts are familiar but the execution different!
>

Firstly, what version of postgres are you using?  What format are you
dumping the file to?

When you dump using the compressed format, the pg_restore process will not
have an FK's until such time that the data is entirely loaded.

Re: [GENERAL] random huge delay when recreate a VIEW or FUNCTION

[Merlin Moncure]

On Tue, Jun 14, 2016 at 4:59 PM, Catalin Maftei  wrote:
> when I recreate a VIEW or FUNCTION with a small change I get:
>
> Query returned successfully with no result in 03:58 minutes.

This is almost certainly due to locks.  Next time this happens, query
pg_stat_activity and pg_locks -- the information is there to determine
if you are locked and who the locker is.  Your view creating query
would be blocked as 'waiting' if that's the case.

merlin


-- 
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] Hot disable WAL archiving

[Sameer Kumar]

On Fri, 17 Jun 2016, 9:12 p.m. Igor Neyman,  wrote:

>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] On Behalf Of Job
> Sent: Friday, June 17, 2016 9:01 AM
> To: Albe Laurenz ; pgsql-general@postgresql.org
> Subject: [GENERAL] R: Hot disable WAL archiving
>
> Hi Albe and thank you, first of all.
>
> Is there a way to disable Wal logging only for specific table, permanently?
>

Yes.

You can set it to NO LOGGING

Alter table table_name set unlogged;

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

>
> Thank you again!
> Francesco
>
> 
> Da: Albe Laurenz [laurenz.a...@wien.gv.at]
> Inviato: venerdì 17 giugno 2016 13.48
> A: Job; pgsql-general@postgresql.org
> Oggetto: RE: Hot disable WAL archiving
>
> Job wrote:
> > is there a way in Postgresql-9.5 to disable temporarily WAL archiving
> > to speed up pg_bulkload with restarting database engine?
>
>
> You can set 'archive_command=/bin/true' and reload, then no WAL archives
> will be written.
>
> Make sure to perform a base backup as soon as your bulk load is finished.
>
> Yours,
> Laurenz Albe
>
> --
>
> Francesco,
>
> Check CREATE UNLOGGED TABLE... in the docs.
>
> Regards,
> Igor
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] Hot disable WAL archiving

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Job
Sent: Friday, June 17, 2016 9:01 AM
To: Albe Laurenz ; pgsql-general@postgresql.org
Subject: [GENERAL] R: Hot disable WAL archiving

Hi Albe and thank you, first of all.

Is there a way to disable Wal logging only for specific table, permanently?

Thank you again!
Francesco


Da: Albe Laurenz [laurenz.a...@wien.gv.at]
Inviato: venerdì 17 giugno 2016 13.48
A: Job; pgsql-general@postgresql.org
Oggetto: RE: Hot disable WAL archiving

Job wrote:
> is there a way in Postgresql-9.5 to disable temporarily WAL archiving 
> to speed up pg_bulkload with restarting database engine?


You can set 'archive_command=/bin/true' and reload, then no WAL archives will 
be written.

Make sure to perform a base backup as soon as your bulk load is finished.

Yours,
Laurenz Albe

--

Francesco,

Check CREATE UNLOGGED TABLE... in the docs.

Regards,
Igor


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

[GENERAL] R: Hot disable WAL archiving

[Job]

Hi Albe and thank you, first of all.

Is there a way to disable Wal logging only for specific table, permanently?

Thank you again!
Francesco


Da: Albe Laurenz [laurenz.a...@wien.gv.at]
Inviato: venerdì 17 giugno 2016 13.48
A: Job; pgsql-general@postgresql.org
Oggetto: RE: Hot disable WAL archiving

Job wrote:
> is there a way in Postgresql-9.5 to disable temporarily WAL archiving to 
> speed up pg_bulkload with
> restarting database engine?

You can set 'archive_command=/bin/true' and reload, then no WAL
archives will be written.

Make sure to perform a base backup as soon as your bulk load
is finished.

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] Question about RUM-index

[Andreas Joseph Krogh]

På fredag 17. juni 2016 kl. 13:53:34, skrev Oleg Bartunov >:
    On Fri, Jun 17, 2016 at 2:10 PM, Oleg Bartunov > wrote:     On Fri, Jun 17, 2016 at 9:32 AM, 
Andreas Joseph Krogh> wrote: På 
torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes >:
On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh > wrote: Hi.
 
First; Is this the correct forum to ask questions about the Postgres Pro's new 
RUM-index?
 
If not, please point me to the right forum.
 
I think that https://github.com/postgrespro/rum/issues 
 might be the best forum.



 
Oleg and friends; Should we use GitHub-issues as forum (one issue per 
question/thread?), pgsql-general or something else?
 
Andreas,
  
we are hardly working on our internal version of rum and will open it after 
resolving some issues. I think the best place to discuss it is -hackers.



 
Ah, as someone corrected me, we are working hard !



 
He he, I figured that was what you meant:-)
 
Ok, so basically - will RUM-index support the same indexing-properties as GIN 
(being able to index tsvector, BIGINT-arrays, JSONB etc.) and be able to use 
index for sorting on ie. timestamp, tsrank or some BIGINT-column?
 
Like my example, will it be possible to issue a query like this:
 
SELECT del.id  , del.sent FROM delivery del WHERE 1 = 1 AND 
del.fts_all @@to_tsquery('simple', 'hi:*') AND del.folder_id = ANY(ARRAY[2,3]::
BIGINT[]) ORDER BY del.sent DESC LIMIT 101 OFFSET 0;  


and have it use one RUM-index so the whole operation, including sorting, is as 
efficient as possible?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Pg_bulkload for PostgreSql 9.5

[Michael Paquier]

On Fri, Jun 17, 2016 at 8:29 PM, Job  wrote:
> i have some problems about compiling pg_bulkload-3.1.8 on a CentOS 5 with 
> Postgresql 9.5.
> If i use a previous version of Psql it compile and works.

The project page states that 9.5 is supported with this version:
https://github.com/ossc-db/pg_bulkload

If you have problems with it, you should directly contact the folks in
charge of maintaining this project, aka NTT-OSSC, with more details
about the failure. Without more information, anybody would have a hard
time to analyze your problem.
-- 
Michael


-- 
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] Question about RUM-index

[Oleg Bartunov]

On Fri, Jun 17, 2016 at 2:10 PM, Oleg Bartunov  wrote:

>
>
> On Fri, Jun 17, 2016 at 9:32 AM, Andreas Joseph Krogh 
> wrote:
>
>> På torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes <
>> jeff.ja...@gmail.com>:
>>
>> On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh > > wrote:
>>>
>>> Hi.
>>>
>>> First; Is this the correct forum to ask questions about the Postgres
>>> Pro's new RUM-index?
>>>
>>> If not, please point me to the right forum.
>>>
>>
>> I think that https://github.com/postgrespro/rum/issues might be the best
>> forum.
>>
>>
>> Oleg and friends; Should we use GitHub-issues as forum (one issue per
>> question/thread?), pgsql-general or something else?
>>
>
> Andreas,
>
> we are hardly working on our internal version of rum and will open it
> after resolving some issues. I think the best place to discuss it is
> -hackers.
>

Ah, as someone corrected me, we are working hard !



>
>
>
>>
>>
>> Note that GIN does almost what I want, except use the index when sorting
>>> by "sent"-timestamp.
>>>
>>> So I wonder if RUM can do any better?
>>> What I don't understand is how to have "folder_id" as part of the
>>> RUM-index so that I can search in *an array* of folders using the
>>> index, *AND* have the whole result sorted by "sent"-timestamp also
>>> using the RUM-index.
>>>
>>
>> I think you would have to implement an operator for integers for RUM much
>> like btree_gin does for GIN.  Sorry don't know how to do that, except to
>> say look in the RUM code to see how it does it for time-stamps.
>>
>>
>>>
>>> In the (limited) documentation sorting using timestamp is done like this:
>>>
>>> ORDER BY sent <-> '2000-01-01'::TIMESTAMP
>>>
>>> which I don't understand; Why must one specify a value here, and how
>>> does that value affect the result?
>>>
>>
>>
>> This is essentially identical to ORDER BY ABS(sent -
>> '2000-01-01'::TIMESTAMP);  except it can use the index.
>>
>> So maybe pick a constant outside the range of possible values, and use
>> that as one argument to <->.
>>
>>
>> This should be unnecessary and hidden from the user. Maybe some "ORDER BY
>> rum_timestamp(sent)" or something could abstract away stuff to make it much
>> clearer to the user?
>> --
>> *Andreas Joseph Krogh*
>> CTO / Partner - Visena AS
>> Mobile: +47 909 56 963
>> andr...@visena.com
>> www.visena.com
>> 
>>
>>
>
>

Re: [GENERAL] Hot disable WAL archiving

[Albe Laurenz]

Job wrote:
> is there a way in Postgresql-9.5 to disable temporarily WAL archiving to 
> speed up pg_bulkload with
> restarting database engine?

You can set 'archive_command=/bin/true' and reload, then no WAL
archives will be written.

Make sure to perform a base backup as soon as your bulk load
is finished.

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] Hot disable WAL archiving

[Job]

Hello,

is there a way in Postgresql-9.5 to disable temporarily WAL archiving to speed 
up pg_bulkload with restarting database engine?

Thank you,
Francesco

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

[GENERAL] Pg_bulkload for PostgreSql 9.5

[Job]

Hello,

i have some problems about compiling pg_bulkload-3.1.8 on a CentOS 5 with 
Postgresql 9.5.
If i use a previous version of Psql it compile and works.

Thank you if you can help me
Francesco

-- 
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] Question about RUM-index

[Oleg Bartunov]

On Fri, Jun 17, 2016 at 9:32 AM, Andreas Joseph Krogh 
wrote:

> På torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes <
> jeff.ja...@gmail.com>:
>
> On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh 
> wrote:
>>
>> Hi.
>>
>> First; Is this the correct forum to ask questions about the Postgres
>> Pro's new RUM-index?
>>
>> If not, please point me to the right forum.
>>
>
> I think that https://github.com/postgrespro/rum/issues might be the best
> forum.
>
>
> Oleg and friends; Should we use GitHub-issues as forum (one issue per
> question/thread?), pgsql-general or something else?
>

Andreas,

we are hardly working on our internal version of rum and will open it after
resolving some issues. I think the best place to discuss it is -hackers.



>
>
> Note that GIN does almost what I want, except use the index when sorting
>> by "sent"-timestamp.
>>
>> So I wonder if RUM can do any better?
>> What I don't understand is how to have "folder_id" as part of the
>> RUM-index so that I can search in *an array* of folders using the index,
>> *AND* have the whole result sorted by "sent"-timestamp also using the
>> RUM-index.
>>
>
> I think you would have to implement an operator for integers for RUM much
> like btree_gin does for GIN.  Sorry don't know how to do that, except to
> say look in the RUM code to see how it does it for time-stamps.
>
>
>>
>> In the (limited) documentation sorting using timestamp is done like this:
>>
>> ORDER BY sent <-> '2000-01-01'::TIMESTAMP
>>
>> which I don't understand; Why must one specify a value here, and how does
>> that value affect the result?
>>
>
>
> This is essentially identical to ORDER BY ABS(sent -
> '2000-01-01'::TIMESTAMP);  except it can use the index.
>
> So maybe pick a constant outside the range of possible values, and use
> that as one argument to <->.
>
>
> This should be unnecessary and hidden from the user. Maybe some "ORDER BY
> rum_timestamp(sent)" or something could abstract away stuff to make it much
> clearer to the user?
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>

Re: [GENERAL] Re: regarding schema only migration from sqlserver to postgres with runmtk.sh

[Durgamahesh Manne]

hi sir

as you mentioned above

members in pgsql mailing list  were not frustrating  about my tone & mail
which i sent related to postgres

as i believe they are always happy to serve for postgres

as well as i like very much postgres

On Thu, Jun 16, 2016 at 9:33 PM, Sameer Kumar 
wrote:

> Disclaimer: My company is a partner of EnterpriseDB and provides services
> and consultation on EnterpriseDB tools
>
> On Thu, 16 Jun 2016, 11:52 p.m. Neil Anderson, 
> wrote:
>
>> On 2016-06-16 11:42 AM, Durgamahesh Manne wrote:
>> > i got another error even i specified -targetdbtype
>> > like
>> > ./runMTK.sh -sourcedbtype sqlserver -targetdbtype postgresql
>> > -targetSchema public -schemaOnly -allTables dbo
>> > Running EnterpriseDB Migration Toolkit (Build 49.0.4) ...
>
>
> You have to take a pause and note that people on this list are frustrated
> with you for your tone and top posting. In case the complain has gone
> unregistered or has not made sense, please read this out
> https://en.m.wikipedia.org/wiki/Posting_style
>
> Make sure you don't top post
>
> > Source database connectivity info...
>> > conn =jdbc:jtds:sqlserver://
>> fxserver.trustfort.com:49883/DataFeedHandler
>> > 
>> > user =trustfort
>> > password=**
>> > Target database connectivity info...
>> > conn =jdbc:postgresql://192.168.168.201:5432/raghu
>> > 
>> > user =postgres
>> > password=**
>> > Connecting with source SQL Server database server...
>> > Connected to Microsoft SQL Server, version '10.50.1600'
>> > Connecting with target Postgres database server...
>> > Exception in thread "main" java.lang.NoClassDefFoundError:
>> > org/postgresql/Driver
>>
>
> You need to ensure that postgreSQL jdbc jar files are placed in your class
> path or under $JRE_HOME/lib/ext
>
> You might have already done this for SQL Server, do the same for
> PostgreSQL too.
>
>
> > at
>> > com.edb.dbhandler.postgresql.PGConnection.(PGConnection.java:32)
>> > at
>> com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:228)
>> > at
>> >
>> com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:5987)
>> > at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3376)
>> > at com.edb.MigrationToolkit.main(MigrationToolkit.java:1700)
>> > Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver
>> >
>> >
>> >
>> >
>> >
>> > On Thu, Jun 16, 2016 at 8:59 PM, Neil Anderson
>> > > wrote:
>> >
>> > On 2016-06-16 11:13 AM, Durgamahesh Manne wrote:
>> >
>> > yes sir
>> >
>> > as per above discussion..i already looked pdf postgres plus
>> > guide you
>> > mentioned
>>
>
> It is best that you contact EbterpriseDB support or someone who provides
> services for EDB tools.
>
> >
>> > as well as i already set password for postgres user associated
>> with
>> > postgres db
>> >
>> > On Thu, Jun 16, 2016 at 8:30 PM, Durgamahesh Manne
>> > 
>> > > > >> wrote:
>> >
>> > hi
>> > sir
>> > as per above discussion same error repeated even mentioned
>> ip
>> > address of hostname
>> >
>> > ./runMTK.sh -sourcedbtype sqlserver -targetSchema public
>> > -schemaOnly
>> > -allTables dbo
>> >
>> >
>> > TARGET_DB_URL=jdbc:postgresql://192.168.168.201:5432/raghu
>> > 
>> > 
>> > TARGET_DB_USER=postgres
>> > TARGET_DB_PASSWORD=*
>> >
>> >
>> >
>> > Connecting with source SQL Server database server...
>> > Connected to Microsoft SQL Server, version '10.50.1600'
>> > Connecting with target EnterpriseDB database server...
>> > MTK-10045: The URL specified for the "target" database is
>> > invalid.
>> > Check the connectivity credentials.
>> > Stack Trace:
>> > com.edb.MTKException: MTK-10045: The URL specified for the
>> > "target"
>> > database is invalid.
>> > Check the connectivity credentials.
>> >
>> > On Thu, Jun 16, 2016 at 7:55 PM, David G. Johnston
>> > > > 
>> > > > >> wrote:
>> >
>> > On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne
>> > > > 
>> > > > 

Re: [GENERAL] Re: regarding schema only migration from sqlserver to postgres with runmtk.sh

[Durgamahesh Manne]

hi sir

as per above conversation

i need to download jdbc driver  as sameer suggested on migration with
runmtk.sh from sql to postgres

thank you sir

super fast response  only from postgres pgdg regarding postgres issues

On Fri, Jun 17, 2016 at 1:18 AM, Adrian Klaver 
wrote:

> On 06/16/2016 11:28 AM, Durgamahesh Manne wrote:
>
>> i can able to resolve the issue on migration from sql to postgres with
>> runmtk.sh  without taking any support from edb
>>
>> even i frustated with runmtk.sh as i wanted to use postgresql life long
>> in my company
>>
>
> For completeness and to help anyone else who comes across this thread when
> having the same problem, what did you do to get it to work?
>
>
>> thank you for information
>>
>>
>>
>>
>> On Thu, Jun 16, 2016 at 11:43 PM, Durgamahesh Manne
>> > wrote:
>>
>> thank you for the information related to postgres
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Question about RUM-index

[Andreas Joseph Krogh]

På torsdag 16. juni 2016 kl. 00:50:45, skrev Jeff Janes >:
On Wed, Jun 15, 2016 at 3:56 AM, Andreas Joseph Krogh > wrote: Hi.
 
First; Is this the correct forum to ask questions about the Postgres Pro's new 
RUM-index?
 
If not, please point me to the right forum.
 
I think that https://github.com/postgrespro/rum/issues 
 might be the best forum.



 
Oleg and friends; Should we use GitHub-issues as forum (one issue per 
question/thread?), pgsql-general or something else?
 
Note that GIN does almost what I want, except use the index when sorting by 
"sent"-timestamp.
 
So I wonder if RUM can do any better?
What I don't understand is how to have "folder_id" as part of the RUM-index so 
that I can search in an array of folders using the index, AND have the whole 
result sorted by "sent"-timestamp also using the RUM-index.
 
I think you would have to implement an operator for integers for RUM much like 
btree_gin does for GIN.  Sorry don't know how to do that, except to say look in 
the RUM code to see how it does it for time-stamps.
 
 
In the (limited) documentation sorting using timestamp is done like this:
 
ORDER BY sent <-> '2000-01-01'::TIMESTAMP
 
which I don't understand; Why must one specify a value here, and how does that 
value affect the result?
 
 
This is essentially identical to ORDER BY ABS(sent - '2000-01-01'::TIMESTAMP); 
 except it can use the index.
 
So maybe pick a constant outside the range of possible values, and use that as 
one argument to <->.



 
This should be unnecessary and hidden from the user. Maybe some "ORDER BY 
rum_timestamp(sent)" or something could abstract away stuff to make it much 
clearer to the user?

-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com