Re: [GENERAL] How to generate are own apt packages for ubuntu?

[John R Pierce]

On 2/23/2016 11:29 PM, David Grelaud wrote:



We would like to deploy a modified version of PostgreSQL on our servers.
It would be easier if we could build our own apt package for Ubuntu 14.04.


I work mostly on RPM based linux (but also Solaris, BSD, AIX), and 
frankly, if I need to deploy my own postgres build, I build it to run in 
/opt/project/pgsql/9.4/..   and package it as a simple tarball.



--
john r pierce, recycling bits in santa cruz

[GENERAL] How to generate are own apt packages for ubuntu?

[David Grelaud]

Hello,

We would like to deploy a modified version of PostgreSQL on our servers.
It would be easier if we could build our own apt package for Ubuntu 14.04.

We've found this project  :
http://git.postgresql.org/gitweb/?p=pgapt.git;a=summary
Is it the right tool? How to use it (without jenkins if possible)?
Is there a better solution?

Thank you,

*David Grelaud*

Re: [GENERAL] FreeBSD x86 and x86_64

[Achilleas Mantzios]

On 24/02/2016 09:20, John R Pierce wrote:

On 2/23/2016 10:57 PM, Achilleas Mantzios wrote:
Of course it is. You can install PostgreSQL via packages, via ports or manually. I prefer the manual way and have been doing so for many years. FreeBSD peculiarities will start to bite (maybe, 
maybe not) once you start entering territories like threading, but you are far from that from what I gather.


postgres doesn't use threading, so there shouldn't be any issues there 


I was referring to projects like pljava.






--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
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] FreeBSD x86 and x86_64

[John R Pierce]

On 2/23/2016 10:57 PM, Achilleas Mantzios wrote:
Of course it is. You can install PostgreSQL via packages, via ports or 
manually. I prefer the manual way and have been doing so for many 
years. FreeBSD peculiarities will start to bite (maybe, maybe not) 
once you start entering territories like threading, but you are far 
from that from what I gather.


postgres doesn't use threading, so there shouldn't be any issues there 



--
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] FreeBSD x86 and x86_64

[John R Pierce]

On 2/23/2016 11:17 PM, Achilleas Mantzios wrote:


Quite off-topic, but did you ever manage to run postgres on identical 
jails, i.e. same user, same port? Was FreeBSD IPC ever jailified? Or 
did the recent switch to mmap resolve this? 


no, I haven't tried that, just have the one postgres instance running on 
that NAS box (a HP DL160gen8 with 16 cores, 128gb ram, and 58 SAS2 
15krpm disks).



--
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] FreeBSD x86 and x86_64

[Achilleas Mantzios]

On 23/02/2016 23:02, John R Pierce wrote:

On 2/23/2016 12:45 PM, Larry Rosenman wrote:


The ports tree has postgresql:
...
I'm running 9.5.1 on both 11-CURRENT, and 10.x 


and I might add, postgres behaves very nicely in a FreeBSD Jail.



Quite off-topic, but did you ever manage to run postgres on identical jails, 
i.e. same user, same port? Was FreeBSD IPC ever jailified? Or did the recent 
switch to mmap resolve this?


I'm running pg 9.4.6 in a FreeNAS (FreeBSD 9.3) jail without any hassle, and 
very good performance, installed from ports via pkg install ...






--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
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] FreeBSD x86 and x86_64

[Achilleas Mantzios]

On 23/02/2016 11:47, MEERA wrote:

Hi all,

Any information regarding PostgreSQL support on FreeBSD platform?


Of course it is. You can install PostgreSQL via packages, via ports or manually. I prefer the manual way and have been doing so for many years. FreeBSD peculiarities will start to bite (maybe, maybe 
not) once you start entering territories like threading, but you are far from that from what I gather.




On Wed, Feb 17, 2016 at 12:26 PM, preeti soni > wrote:

Hi,

There is no clear information available for FreeBSD supported versions.

Would you please let em know if Postgres is supported on both FreeBSD x86 
and x86_64.

Thanks in advance,

Preeti




--
thanks and regards,
Meera R Nair



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] PostgreSQL flavors

[Joshua D. Drake]

On 02/23/2016 05:49 PM, Magnus Hagander wrote:


It's Postgres Plus, not PostgreSQL plus.

And AFAIK, it was also retired some time ago and doesn't actually exist
anymore.


And, I just looked. You are correct.

Wow...

Sad.

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] PostgreSQL flavors

[Magnus Hagander]

On Tue, Feb 23, 2016 at 1:04 PM, Josh berkus  wrote:

> On 02/23/2016 07:51 AM, Sherrie Kubis wrote:
>
>> Hello, my first post to the list, thank you for this place to ask
>> questions and get help.
>>
>> Our management has tasked me with devising a plan to migrate our
>> existing databases from Oracle to PostgreSQL.  I’m researching and
>> getting familiar with PostgreSQL before getting a Linux box to start
>> learning and staging.  I have a long way to go, but it will be fun.
>>
>> Out of the gate, I can see different PostgreSQL products – PostgreSQL,
>> PostgreSQLPlus, EnterpriseDB Advanced Server.
>>
>
> So here's a quick rundown.  I'm sure I'm forgetting some, but here's a lot
> of them.  I've deliberately omitted PostgreSQL forks/versions which are no
> longer maintained or not commercially available.
>
> Open Source
> ---
>
> PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra open
> source tools included in the installer.
>

It's Postgres Plus, not PostgreSQL plus.

And AFAIK, it was also retired some time ago and doesn't actually exist
anymore.

(The advanced servers version does of course, but that's not open source).

I think the only "Plus" product now is their cloud offering?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] PostgreSQL flavors

[Tatsuo Ishii]

> I had no idea PowerGres was still going.
> 
> You know I threw out an entire box of PowerGres 7.3 CDs when I moved?

PowerGres 7.3! So old days...

> They were in the back of a closet.

Probably I still have a GreatBridge CDs in my house :-)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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 flavors

[Josh berkus]

On 02/23/2016 04:52 PM, Tatsuo Ishii wrote:

PowerGres: Surviving since 2003. Full compatible with PostgreSQL
except "PowerGres plus" which has the transparent database encryption
and the redundant WAL.


I had no idea PowerGres was still going.

You know I threw out an entire box of PowerGres 7.3 CDs when I moved? 
They were in the back of a closet.


--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--
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 flavors

[Tatsuo Ishii]

> On 02/23/2016 07:51 AM, Sherrie Kubis wrote:
>> Hello, my first post to the list, thank you for this place to ask
>> questions and get help.
>>
>> Our management has tasked me with devising a plan to migrate our
>> existing databases from Oracle to PostgreSQL.  I$B!G(Bm researching and
>> getting familiar with PostgreSQL before getting a Linux box to start
>> learning and staging.  I have a long way to go, but it will be fun.
>>
>> Out of the gate, I can see different PostgreSQL products $(Q#|(B 
>> PostgreSQL,
>> PostgreSQLPlus, EnterpriseDB Advanced Server.
> 
> So here's a quick rundown.  I'm sure I'm forgetting some, but here's a
> lot of them.  I've deliberately omitted PostgreSQL forks/versions
> which are no longer maintained or not commercially available.
> 
> Open Source
> ---
> 
> PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra
> open source tools included in the installer.
> 
> GreenPlum: fork of PostgreSQL 8.2, designed for large-scale big data,
> data mining and analytics.
> 
> PostgresXC: beta-quality open source fork designed for small clusters
> of transaction-processing, ala Oracle RAC.
> 
> PostgresXL: fork of PostgresXC, more stable, and a bit more oriented
> towards data analytics.
> 
> Stado: Version of PostgreSQL with java middleware to do big-data
> scale-out.  At various times called ExtenDB and GridSQL.
> 
> BigSQL: PostgreSQL+Hadoop for big data scale-out.
> 
> PipelineDB: streaming SQL engine built from PostgreSQL.
> 
> 
> Closed Source
> -
> 
> PostgreSQL Plus Advanced Server/EDB Server: EnterpriseDB's fork of
> PostgreSQL which has Oracle compatibility and some other tools (like
> xDB replication).  Sometimes includes features from future versions of
> PostgreSQL
> 
> CitusDB: latest/greatest big data scale-out version of PostgreSQL.
> Soon to be open-source.
> 
> Aster: prior generation of PostgreSQL MPP and Map/Reduce scale-out.
> 
> Hadapt: proprietary PostgreSQL+Hadoop fusion.  Based on HadoopDB,
> which was open source.
> 
> Paraccel: Column-oriented in-memory cluster database built from
> PostgreSQL 8.2.
> 
> RedShift: Amazon's fork of Paraccel, available only on AWS as a
> service.
> 
> Vertica: Another PostgreSQL-based column store.  Unclear on how much
> PostgreSQL code it uses, but uses a version of the PostgreSQL protocol
> and psql client.
> 
> FAST: Fujitsu's spin of PostgreSQL, optimized for high performance on
> high-end hardware.

PowerGres: Surviving since 2003. Full compatible with PostgreSQL
except "PowerGres plus" which has the transparent database encryption
and the redundant WAL.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] json function question

[Merlin Moncure]

On Tue, Feb 23, 2016 at 1:54 PM, Tom Lane  wrote:
> Dan S  writes:
>> I have this table, data and query:
>
>> create table test
>> (
>> id int,
>> txt text,
>> txt_arr text[],
>> f float
>> );
>
>> insert into test
>> values
>> (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);
>
>> select j, json_populate_record(null::test, j)
>> from
>> (
>> select to_json(t) as j from test t
>> ) r;
>
>> ERROR:  malformed array literal: "["abc","def","fgh"]"
>> DETAIL:  "[" must introduce explicitly-specified array dimensions.
>
>> Is it a bug or how am I supposed to use the populate function ?
>
> AFAICS, json_populate_record has no intelligence about nested container
> situations.  It'll basically just push the JSON text representation of any
> field of the top-level object at the input converter for the corresponding
> composite-type column.  That doesn't work if you're trying to convert a
> JSON array to a Postgres array, and it wouldn't work for sub-object to
> composite column either, because of syntax discrepancies.
>
> Ideally this would work for arbitrarily-deeply-nested array+record
> structures, but it looks like a less than trivial amount of work to make
> that happen.
>
>> If I try an equivalent example with hstore it works well.
>
> hstore hasn't got any concept of substructure in its field values, so
> it's hard to see how you'd create an "equivalent" situation.
>
> One problem with fixing this is avoiding backwards-compatibility breakage,
> but I think we could do that by saying that we only change behavior when
> (a) json sub-value is an array and target Postgres type is an array type,
> or (b) json sub-value is an object and target Postgres type is a composite
> type.  In both cases, current code would fail outright, so there's no
> existing use-cases to protect.  For other target Postgres types, we'd
> continue to do it as today, so for example conversion to a JSON column
> type would continue to work as it does now.

I hope so.  When we debated these interfaces the current behavior
accepted on the principle that nested structures could be deserialized
at some point in the future.  I think the endgame here is to be able
to do, foo::json[b]::foo for just about any postgres type.

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] json function question

[Dan S]

Yes I meant equivalence in the roundtrip conversion sense.

And of course the "feature complete" solution which can handle deep
structures would be really nice to have.

Best Regards
Dan S

2016-02-23 21:11 GMT+01:00 David G. Johnston :

> On Tue, Feb 23, 2016 at 12:54 PM, Tom Lane  wrote:
>
>> Dan S  writes:
>> > I have this table, data and query:
>>
>> > create table test
>> > (
>> > id int,
>> > txt text,
>> > txt_arr text[],
>> > f float
>> > );
>>
>> > insert into test
>> > values
>> >
>> (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);
>>
>> > select j, json_populate_record(null::test, j)
>> > from
>> > (
>> > select to_json(t) as j from test t
>> > ) r;
>>
>> > ERROR:  malformed array literal: "["abc","def","fgh"]"
>> > DETAIL:  "[" must introduce explicitly-specified array dimensions.
>>
>> > Is it a bug or how am I supposed to use the populate function ?
>>
>> AFAICS, json_populate_record has no intelligence about nested container
>> situations.  It'll basically just push the JSON text representation of any
>> field of the top-level object at the input converter for the corresponding
>> composite-type column.  That doesn't work if you're trying to convert a
>> JSON array to a Postgres array, and it wouldn't work for sub-object to
>> composite column either, because of syntax discrepancies.
>>
>> Ideally this would work for arbitrarily-deeply-nested array+record
>> structures, but it looks like a less than trivial amount of work to make
>> that happen.
>>
>> > If I try an equivalent example with hstore it works well.
>>
>> hstore hasn't got any concept of substructure in its field values, so
>> it's hard to see how you'd create an "equivalent" situation.
>>
>
> ​Equivalent in the "ability to round-trip" sense.  Since hstore doesn't
> have nested containers internal serialization of a record to hstore is
> forced to "stringify" the array which can then be fed back in as-is.  But
> the [row_]to_json​
>
> ​logic converts the PostgreSQL arrays to JSON arrays and then we fail to
> handle them on the return portion of the trip.
>
> Arrays are likely to be a much for common scenario but I agree that
> dealing with arbitrary depths and objects would make the feature complete.
>
> And yes, back-patching should only occur (and ideally behavior changing)
> for situations that today raise errors - as the example does.
>
> ​David J.
> ​
> ​
>

Re: [GENERAL] FreeBSD x86 and x86_64

[John R Pierce]

On 2/23/2016 12:45 PM, Larry Rosenman wrote:


The ports tree has postgresql:
...
I'm running 9.5.1 on both 11-CURRENT, and 10.x 


and I might add, postgres behaves very nicely in a FreeBSD Jail.

I'm running pg 9.4.6 in a FreeNAS (FreeBSD 9.3) jail without any hassle, 
and very good performance, installed from ports via pkg install ...




--
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] FreeBSD x86 and x86_64

[Larry Rosenman]

On 2016-02-23 14:40, Alvaro Herrera wrote:

MEERA wrote:

Hi all,

Any information regarding PostgreSQL support on FreeBSD platform?


You can see the list of supported platforms here:
http://buildfarm.postgresql.org/cgi-bin/show_status.pl
We seem to have FreeBSD 9.0 on gcc 4.2 and FreeBSD 10 on clang, both on
x86_64.  If you want to see it tested on 32bit x86, I suggest you put 
up

a permanent buildfarm machine to that effect; see
http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto

Cheers

--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


The ports tree has postgresql:
org.lerctr.org /home/ler $ cd /usr/ports/databases/
borg.lerctr.org /usr/ports/databases $ ls|grep postgres
fpc-postgres
libgda4-postgresql
libgda5-postgresql
luasql-postgres
nagios-check_postgres_replication
p5-Test-postgresql
postgresql-jdbc
postgresql-libpgeasy
postgresql-libpqxx
postgresql-libpqxx3
postgresql-odbc
postgresql-pllua
postgresql-plproxy
postgresql-plruby
postgresql-plv8js
postgresql-relay
postgresql-repmgr
postgresql90-client
postgresql90-contrib
postgresql90-docs
postgresql90-pgtcl
postgresql90-plperl
postgresql90-plpython
postgresql90-pltcl
postgresql90-server
postgresql91-client
postgresql91-contrib
postgresql91-docs
postgresql91-pgtcl
postgresql91-plperl
postgresql91-plpython
postgresql91-pltcl
postgresql91-server
postgresql92-client
postgresql92-contrib
postgresql92-docs
postgresql92-pgtcl
postgresql92-plperl
postgresql92-plpython
postgresql92-pltcl
postgresql92-server
postgresql93-client
postgresql93-contrib
postgresql93-docs
postgresql93-pgtcl
postgresql93-plperl
postgresql93-plpython
postgresql93-pltcl
postgresql93-server
postgresql94-client
postgresql94-contrib
postgresql94-docs
postgresql94-pgtcl
postgresql94-plperl
postgresql94-plpython
postgresql94-pltcl
postgresql94-server
postgresql95-client
postgresql95-contrib
postgresql95-docs
postgresql95-pgtcl
postgresql95-plperl
postgresql95-plpython
postgresql95-pltcl
postgresql95-server
postgresql_autodoc
proftpd-mod_sql_postgres
py-postgresql
rubygem-dm-postgres-adapter
rubygem-do_postgres
borg.lerctr.org /usr/ports/databases $

I'm running 9.5.1 on both 11-CURRENT, and 10.x


--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: l...@lerctr.org
US Mail: 7011 W Parmer Ln, Apt 1115, Austin, TX 78729-6961


--
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] FreeBSD x86 and x86_64

[Alvaro Herrera]

MEERA wrote:
> Hi all,
> 
> Any information regarding PostgreSQL support on FreeBSD platform?

You can see the list of supported platforms here:
http://buildfarm.postgresql.org/cgi-bin/show_status.pl
We seem to have FreeBSD 9.0 on gcc 4.2 and FreeBSD 10 on clang, both on
x86_64.  If you want to see it tested on 32bit x86, I suggest you put up
a permanent buildfarm machine to that effect; see 
http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto

Cheers

-- 
Á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] FreeBSD x86 and x86_64

[Adrian Klaver]

On 02/23/2016 01:47 AM, MEERA wrote:

Hi all,

Any information regarding PostgreSQL support on FreeBSD platform?


This:
http://www.postgresql.org/download/freebsd/

leads to:

http://www.freebsd.org/cgi/ports.cgi?query=postgresql=name=databases

If the above does not help, is there some specific information you are 
after?




On Wed, Feb 17, 2016 at 12:26 PM, preeti soni > wrote:

Hi,

There is no clear information available for FreeBSD supported versions.

Would you please let em know if Postgres is supported on both
FreeBSD x86 and x86_64.

Thanks in advance,

Preeti




--
thanks and regards,
Meera R Nair



--
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] FreeBSD x86 and x86_64

[MEERA]

Hi all,

Any information regarding PostgreSQL support on FreeBSD platform?

On Wed, Feb 17, 2016 at 12:26 PM, preeti soni 
wrote:

> Hi,
>
> There is no clear information available for FreeBSD supported versions.
>
> Would you please let em know if Postgres is supported on both FreeBSD x86
> and x86_64.
>
> Thanks in advance,
>
> Preeti
>



-- 
thanks and regards,
Meera R Nair

Re: [GENERAL] Live steraming replication setup issue!

[Ashish Chauhan]

Thanks Venkata, I am able to setup replication now. Just wondering when I check 
replication_delay and lag, I am getting negative number, any idea why?

receive|replay| replication_delay | lag
--+--+---+-
796/BA9D8000 | 796/BA9D7FF0 | -00:00:01.612415  |  -2

Thanks,
Ashish

From: Venkata Balaji N [mailto:nag1...@gmail.com]
Sent: Sunday, February 21, 2016 2:14 AM, 2:14
To: Ashish Chauhan
Cc: Andreas Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Live steraming replication setup issue!


On Fri, Feb 19, 2016 at 6:24 PM, Ashish Chauhan 
> wrote:
Below is recovery.conf on slave

#---
# STANDBY SERVER PARAMETERS
#---
#
# standby_mode
#
# When standby_mode is enabled, the PostgreSQL server will work as a
# standby. It will continuously wait for the additional XLOG records, using
# restore_command and/or primary_conninfo.
#
standby_mode = 'on'
#
# primary_conninfo
#
# If set, the PostgreSQL server will try to connect to the primary using this
# connection string and receive XLOG records continuously.
#
primary_conninfo = 'host= port=5432'
#
#
# By default, a standby server keeps restoring XLOG records from the
# primary indefinitely. If you want to stop the standby mode, finish recovery
# and open the system in read/write mode, specify path to a trigger file.
# The server will poll the trigger file path periodically and start as a
# primary server when it's found.
#
trigger_file = '/data/main/primary.trigger'

Can you consider putting recovery_target_timeline='latest' as well ? and can 
you help us know if you can see anything weird in the postgresql logfiles @ DR ?

Is DR in complete sync with the slave ?

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] Perfomance issue. statement in the log file..

[Adrian Klaver]

On 02/23/2016 11:57 AM, Bala Venkat wrote:

Dear All -

We are seeing lot of these statements in the log file. We have 2
functions .  Submit and update .  Between submit and update which will
int the process, the messages are appearning. The process is very slow.
We don't know if this the main reason for the performance issue

Can you please help ? if any one has come across  this issue and
resolved. Appreciate your time.


Help will require more information. For future reference you may want to 
take a look at:


https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

In mean time and as a starting point:

What version of OS and Postgres?

What client is generating the queries?

Define slow and how are you determining it?

Can you show the contents of the two functions?



2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.037 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.081 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.120 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.028 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.163 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.074 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.118 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid =
$1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.027 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.161 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName ,
db=testDETAIL:  parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT 172.26.22.145(52330)  user=postgres ,
db=testLOG:  duration: 1210.331 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.230 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.083 ms



--
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] Perfomance issue. statement in the log file..

[Roxanne Reid-Bennett]

On 2/23/2016 11:57 AM, Bala Venkat wrote:

Dear All -

   We are seeing lot of these statements in the log file. We have 
2 functions .  Submit and update .  Between submit and update which 
will int the process, the messages are appearning. The process is very 
slow. We don't know if this the main reason for the performance issue


Can you please help ? if any one has come across  this issue and 
resolved. Appreciate your time.


2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid 
= $1 AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE 
'%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , 
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:  
execute : SELECT attnotnull FROM pg_catalog.pg_attribute 
WHERE attrelid =

$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , 
db=testDETAIL:  parameters: $1 = '3003958', $2 = '1'


...
Having done a cursory search for "SELECT attnotnull FROM 
pg_catalog.pg_attribute WHERE attrelid ="
It appears your potential culprit is a JDBC or ODBC (or ORM managed) 
connection querying meta-data over and over.
To identify connection information, back in 2010 [so... your mileage may 
vary] Tom suggested the use of:


See log_connections and log_disconnections.  You probably want to
add PID to log_line_prefix, too, so that you can associate different
log entries for the same session.


Version of Postgres and the execution environment of the 2 functions. 
[e.g. pgsql, PL/?, code etc] might provide more input to help identify a 
more specific answer.


Roxanne

--
[At other schools] I think the most common fault in general is to teach 
students how to pass exams instead of teaching them the science.
Donald Knuth



--
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] json function question

[David G. Johnston]

On Tue, Feb 23, 2016 at 12:54 PM, Tom Lane  wrote:

> Dan S  writes:
> > I have this table, data and query:
>
> > create table test
> > (
> > id int,
> > txt text,
> > txt_arr text[],
> > f float
> > );
>
> > insert into test
> > values
> >
> (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);
>
> > select j, json_populate_record(null::test, j)
> > from
> > (
> > select to_json(t) as j from test t
> > ) r;
>
> > ERROR:  malformed array literal: "["abc","def","fgh"]"
> > DETAIL:  "[" must introduce explicitly-specified array dimensions.
>
> > Is it a bug or how am I supposed to use the populate function ?
>
> AFAICS, json_populate_record has no intelligence about nested container
> situations.  It'll basically just push the JSON text representation of any
> field of the top-level object at the input converter for the corresponding
> composite-type column.  That doesn't work if you're trying to convert a
> JSON array to a Postgres array, and it wouldn't work for sub-object to
> composite column either, because of syntax discrepancies.
>
> Ideally this would work for arbitrarily-deeply-nested array+record
> structures, but it looks like a less than trivial amount of work to make
> that happen.
>
> > If I try an equivalent example with hstore it works well.
>
> hstore hasn't got any concept of substructure in its field values, so
> it's hard to see how you'd create an "equivalent" situation.
>

​Equivalent in the "ability to round-trip" sense.  Since hstore doesn't
have nested containers internal serialization of a record to hstore is
forced to "stringify" the array which can then be fed back in as-is.  But
the [row_]to_json​

​logic converts the PostgreSQL arrays to JSON arrays and then we fail to
handle them on the return portion of the trip.

Arrays are likely to be a much for common scenario but I agree that dealing
with arbitrary depths and objects would make the feature complete.

And yes, back-patching should only occur (and ideally behavior changing)
for situations that today raise errors - as the example does.

​David J.
​
​

[GENERAL] Perfomance issue. statement in the log file..

[Bala Venkat]

Dear All -

   We are seeing lot of these statements in the log file. We have 2
functions .  Submit and update .  Between submit and update which will int
the process, the messages are appearning. The process is very slow. We
don't know if this the main reason for the performance issue

Can you please help ? if any one has come across  this issue and resolved.
Appreciate your time.

2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.059 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.117 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.087 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '1'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.037 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.081 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.120 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.028 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.163 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '2'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.074 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.118 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT 1  FROM pg_catalog.pg_attrdef  WHERE adrelid = $1
AND adn
um = $2   AND pg_catalog.pg_get_expr(adbin, adrelid)   LIKE '%nextval(%'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.027 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.058 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.161 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
execute : SELECT attnotnull FROM pg_catalog.pg_attribute WHERE
attrelid =
$1 AND attnum = $2
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testDETAIL:
parameters: $1 = '3003958', $2 = '3'
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.032 ms
2016-02-23 18:11:24 GMT 172.26.22.145(52330)  user=postgres , db=testLOG:
duration: 1210.331 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 1.230 ms
2016-02-23 18:11:24 GMT IpAddress(33946)  user=UserName , db=testLOG:
duration: 0.083 ms

Re: [GENERAL] json function question

[Tom Lane]

Dan S  writes:
> I have this table, data and query:

> create table test
> (
> id int,
> txt text,
> txt_arr text[],
> f float
> );

> insert into test
> values
> (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);

> select j, json_populate_record(null::test, j)
> from
> (
> select to_json(t) as j from test t
> ) r;

> ERROR:  malformed array literal: "["abc","def","fgh"]"
> DETAIL:  "[" must introduce explicitly-specified array dimensions.

> Is it a bug or how am I supposed to use the populate function ?

AFAICS, json_populate_record has no intelligence about nested container
situations.  It'll basically just push the JSON text representation of any
field of the top-level object at the input converter for the corresponding
composite-type column.  That doesn't work if you're trying to convert a
JSON array to a Postgres array, and it wouldn't work for sub-object to
composite column either, because of syntax discrepancies.

Ideally this would work for arbitrarily-deeply-nested array+record
structures, but it looks like a less than trivial amount of work to make
that happen.

> If I try an equivalent example with hstore it works well.

hstore hasn't got any concept of substructure in its field values, so
it's hard to see how you'd create an "equivalent" situation.

One problem with fixing this is avoiding backwards-compatibility breakage,
but I think we could do that by saying that we only change behavior when
(a) json sub-value is an array and target Postgres type is an array type,
or (b) json sub-value is an object and target Postgres type is a composite
type.  In both cases, current code would fail outright, so there's no
existing use-cases to protect.  For other target Postgres types, we'd
continue to do it as today, so for example conversion to a JSON column
type would continue to work as it does now.

I'm not sure if anything besides json[b]_populate_record needs to change
similarly, but we ought to look at all those conversion functions with
the thought of nested containers in mind.

regards, tom lane

PS: I'm not volunteering to do the work here, but it seems like a good
change to make.


-- 
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] json function question

[David G. Johnston]

On Tue, Feb 23, 2016 at 12:12 PM, Dan S  wrote:

> Hi !
>
> I'm running "PostgreSQL 9.5.1 on i686-pc-linux-gnu, compiled by
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit"
>
> I'm trying out json functions and stumbled on a problem with
> json_populate_record.
> To try out the function I decided to take records from table test convert
> them to json and immediately repopulate them back into records of type
> test, but it fails with this message:
>
> ERROR:  malformed array literal: "["abc","def","fgh"]"
> DETAIL:  "[" must introduce explicitly-specified array dimensions.
> ** Error **
>
> ERROR: malformed array literal: "["abc","def","fgh"]"
> SQL state: 22P02
> Detail: "[" must introduce explicitly-specified array dimensions.
>
> Is it a bug or how am I supposed to use the populate function ?
>

​Personally, I'd call it a bug - whether design or implementation doesn't
matter to me.

What json_populate_record seems to be looking for when faced with a
"text[]" typed field is something of the form:

{"key":"{\"abc\",\"def\",\"ghi\"}"}

IOW, this works:

select j, json_populate_record(null::test, j)
from
(
select
'{"id":1,"txt":"jkl","txt_arr":"{\"abc\",\"def\",\"fgh\"}","f":3.14159}'::json
AS j
) r

Namely a scalar literal that looks like a PostgreSQL array - as opposed to
an actual JSON array.

The literal text is sent through the input function for text[] and gets
parsed into a PostgreSQL text array.​

​Given that this presently errors I would suggest we fix this case so that
both forms are acceptable to the parser.  In other words, try harder in our
effort to coerce between the two formats.

David J.

[GENERAL] json function question

[Dan S]

Hi !

I'm running "PostgreSQL 9.5.1 on i686-pc-linux-gnu, compiled by
gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit"

I'm trying out json functions and stumbled on a problem with
json_populate_record.
To try out the function I decided to take records from table test convert
them to json and immediately repopulate them back into records of type
test, but it fails with this message:

ERROR:  malformed array literal: "["abc","def","fgh"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.
** Error **

ERROR: malformed array literal: "["abc","def","fgh"]"
SQL state: 22P02
Detail: "[" must introduce explicitly-specified array dimensions.

Is it a bug or how am I supposed to use the populate function ?
If I try an equivalent example with hstore it works well.

I have this table, data and query:

create table test
(
id int,
txt text,
txt_arr text[],
f float
);

insert into test
values
(1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);

select j, json_populate_record(null::test, j)
from
(
select to_json(t) as j from test t
) r


Best Regards
Dan S

Re: [GENERAL] PostgreSQL flavors

[Pavel Stehule]

2016-02-23 19:26 GMT+01:00 Josh berkus :

> On 02/23/2016 10:22 AM, Pavel Stehule wrote:
>
>>
>> Vertica was written from scratch in C++. Maybe gram.y was used from Pg.
>> But the client and SQL are strongly inspirited by Postgres. vsql is not
>> psql probably, because psql is better. Last three years is not Vertica
>> protocol compatible with Postgres. I don't know a reason why - I am
>> expecting some strange marketing
>>
>
> According to some insider sources, they broke compatibility because it was
> "faster" (without any actual performance testing).  In an update release,
> no less.  I've been told that they lost a bunch of users over this because
> it broke their tools with no warning.


Interesting

Thank you for info

Pavel


>
>
> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>

Re: [GENERAL] PostgreSQL flavors

[Josh berkus]

On 02/23/2016 10:22 AM, Pavel Stehule wrote:


Vertica was written from scratch in C++. Maybe gram.y was used from Pg.
But the client and SQL are strongly inspirited by Postgres. vsql is not
psql probably, because psql is better. Last three years is not Vertica
protocol compatible with Postgres. I don't know a reason why - I am
expecting some strange marketing


According to some insider sources, they broke compatibility because it 
was "faster" (without any actual performance testing).  In an update 
release, no less.  I've been told that they lost a bunch of users over 
this because it broke their tools with no warning.


--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--
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 flavors

[Pavel Stehule]

>
> Closed Source
> -
>
> PostgreSQL Plus Advanced Server/EDB Server: EnterpriseDB's fork of
> PostgreSQL which has Oracle compatibility and some other tools (like xDB
> replication).  Sometimes includes features from future versions of
> PostgreSQL
>
> CitusDB: latest/greatest big data scale-out version of PostgreSQL.  Soon
> to be open-source.
>
> Aster: prior generation of PostgreSQL MPP and Map/Reduce scale-out.
>
> Hadapt: proprietary PostgreSQL+Hadoop fusion.  Based on HadoopDB, which
> was open source.
>
> Paraccel: Column-oriented in-memory cluster database built from PostgreSQL
> 8.2.
>
> RedShift: Amazon's fork of Paraccel, available only on AWS as a service.
>
> Vertica: Another PostgreSQL-based column store.  Unclear on how much
> PostgreSQL code it uses, but uses a version of the PostgreSQL protocol and
> psql client.
>

Vertica was written from scratch in C++. Maybe gram.y was used from Pg. But
the client and SQL are strongly inspirited by Postgres. vsql is not psql
probably, because psql is better. Last three years is not Vertica protocol
compatible with Postgres. I don't know a reason why - I am expecting some
strange marketing

Pavel


>
> FAST: Fujitsu's spin of PostgreSQL, optimized for high performance on
> high-end hardware.
>
>
>
> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>
>
>
> --
> 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 flavors

[Josh berkus]

On 02/23/2016 07:51 AM, Sherrie Kubis wrote:

Hello, my first post to the list, thank you for this place to ask
questions and get help.

Our management has tasked me with devising a plan to migrate our
existing databases from Oracle to PostgreSQL.  I’m researching and
getting familiar with PostgreSQL before getting a Linux box to start
learning and staging.  I have a long way to go, but it will be fun.

Out of the gate, I can see different PostgreSQL products – PostgreSQL,
PostgreSQLPlus, EnterpriseDB Advanced Server.


So here's a quick rundown.  I'm sure I'm forgetting some, but here's a 
lot of them.  I've deliberately omitted PostgreSQL forks/versions which 
are no longer maintained or not commercially available.


Open Source
---

PostgreSQL Plus: EnterpriseDB's distribution of PostgreSQL with extra 
open source tools included in the installer.


GreenPlum: fork of PostgreSQL 8.2, designed for large-scale big data, 
data mining and analytics.


PostgresXC: beta-quality open source fork designed for small clusters of 
transaction-processing, ala Oracle RAC.


PostgresXL: fork of PostgresXC, more stable, and a bit more oriented 
towards data analytics.


Stado: Version of PostgreSQL with java middleware to do big-data 
scale-out.  At various times called ExtenDB and GridSQL.


BigSQL: PostgreSQL+Hadoop for big data scale-out.

PipelineDB: streaming SQL engine built from PostgreSQL.


Closed Source
-

PostgreSQL Plus Advanced Server/EDB Server: EnterpriseDB's fork of 
PostgreSQL which has Oracle compatibility and some other tools (like xDB 
replication).  Sometimes includes features from future versions of 
PostgreSQL


CitusDB: latest/greatest big data scale-out version of PostgreSQL.  Soon 
to be open-source.


Aster: prior generation of PostgreSQL MPP and Map/Reduce scale-out.

Hadapt: proprietary PostgreSQL+Hadoop fusion.  Based on HadoopDB, which 
was open source.


Paraccel: Column-oriented in-memory cluster database built from 
PostgreSQL 8.2.


RedShift: Amazon's fork of Paraccel, available only on AWS as a service.

Vertica: Another PostgreSQL-based column store.  Unclear on how much 
PostgreSQL code it uses, but uses a version of the PostgreSQL protocol 
and psql client.


FAST: Fujitsu's spin of PostgreSQL, optimized for high performance on 
high-end hardware.




--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


--
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 flavors

[Joshua D. Drake]

On 02/23/2016 07:51 AM, Sherrie Kubis wrote:

Hello, my first post to the list, thank you for this place to ask
questions and get help.

Our management has tasked me with devising a plan to migrate our
existing databases from Oracle to PostgreSQL.  I’m researching and
getting familiar with PostgreSQL before getting a Linux box to start
learning and staging.  I have a long way to go, but it will be fun.

Out of the gate, I can see different PostgreSQL products – PostgreSQL,
PostgreSQLPlus, EnterpriseDB Advanced Server.


There is only one PostgreSQL.Org database product and that is the Open 
Source PostgreSQL. PostgresPlus, and EnterpriseDB Advanced Server are 
commercially supported, closed source, enhanced versions. Therefore it 
really depends on your needs.


If management wants to avoid vendor lock in and truly reduce costs, I 
would recommend the .Org version of PostgreSQL. It allows you the 
greatest flexibility in:


 * Cost reduction
 * Features and extensibility
 * Support
Commercial support is literally available from dozens of vendors.

In short, going from one closed source environment to another closed 
source environment doesn't help a proper enterprise ecosystem nearly as 
much as investing in a community contributor driven platform.


Sincerely,

Joshua D. Drake

--
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] sha256 certificate "unknown message digest algorithm"

[Frazer McLean]

On Tue, 23 Feb 2016, at 17:20, Adrian Klaver wrote:
> Where did you install Postgres from and what version of OpenSSL was it 
> compiled against?

I now realise that I wasn't compiling PostgreSQL against my
self-compiled openssl. After adding --with-includes and --with-libraries
to configure I have it working.

Thanks


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

[GENERAL] Does pglogical support cascade replication?

[xujian]

Hello,  I want to use pglogical to setup cascade replication, however, by 
researching it, I find different answer. 
http://2ndquadrant.com/en/resources/pglogical/faqs/Q. Does pglogical support 
cascaded replication?Subscribers can be configured as publishers as well thus 
cascaded replication can be achieved  by forwarding/chaining (again no failover 
though).
http://2ndquadrant.com/en/resources/pglogical/Where will pglogical go 
next?cascading logical replication, complex replication topologies
http://postgresql.nabble.com/pglogical-logical-replication-contrib-module-td5879755.htmlcascading
 is something that's still WIP. 
I also tested 3 nodes cascade replication, node1->node2-node3I got error on 
node3:ERROR:  cache lookup failed for replication origin 
'xxx''xxx' is the replication slot name of node1
who knows if pglogical support cascade replication? thanks
James 

Re: [GENERAL] sha256 certificate "unknown message digest algorithm"

[Adrian Klaver]

On 02/23/2016 05:18 AM, Frazer McLean wrote:

Hi,
I am using PostgreSQL 9.4.6 and openssl 1.0.2f. If my server or client
certificate use SHA256, I get the following error on the client:
 psql: SSL error: tlsv1 alert decrypt error
And the following log message on the server:
 LOG:  could not accept SSL connection: unknown message digest algorithm
I could use -sha1 to generate my certificates but -sha256 should work,


Some searching indicates this is usually an OpenSSL version issue.

So the OpenSSL version you mention above is where, the client or the 
server or both?


Where did you install Postgres from and what version of OpenSSL was it 
compiled against?



right?
Thanks,
Frazer



--
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 flavors

[Steve Crawford]

Congratulations on the decision and welcome.

As an overview, there is the PostgreSQL *project* which is run by the
PostgreSQL Global Development Group (PgDG) with contributors around the
world most of whom work for a variety of companies that either use or
support PostgreSQL. PostgreSQL is BSD-licensed open-source software. PgDG
operates these mailing lists.

Within the PostgreSQL ecosystem you will find numerous commercial and
non-commercial entities that provide:

-Custom/commercial/extended products based on PostgreSQL (i.e. EnterpriseDB
and others) - some of which are designed to provide a level of Oracle
compatibility

-Commercial and open-source tools that extend, support or work with
PostgreSQL (you may want to look at Ora2Pg - a tool to migrate Oracle to
PostgreSQL)

-Development services

-Training

-DBA and support services (i.e. PGExperts)

-User-groups (very helpful but I don't see one in Florida:
http://www.postgresql.org/community/user-groups/)

-Conferences (http://www.postgresql.org/about/events/)

-Books and magazines

I'd agree with your plan to start with the core open-source PostgreSQL
software to learn then progress to other products/projects as needs dictate.

Cheers,
Steve


On Tue, Feb 23, 2016 at 7:51 AM, Sherrie Kubis <
sherrie.ku...@swfwmd.state.fl.us> wrote:

> Hello, my first post to the list, thank you for this place to ask
> questions and get help.
>
>
>
> Our management has tasked me with devising a plan to migrate our existing
> databases from Oracle to PostgreSQL.  I’m researching and getting familiar
> with PostgreSQL before getting a Linux box to start learning and staging.
> I have a long way to go, but it will be fun.
>
>
>
> Out of the gate, I can see different PostgreSQL products – PostgreSQL,
> PostgreSQLPlus, EnterpriseDB Advanced Server.
>
> For staging I’ll likely start with the vanilla version.  I’ve been
> searching for a comparison of different product sets; I’ve found some
> things, but in other places different information.  There is a lot to sift
> through.  For example, we are now using non-active Data Guard to a standby
> for disaster recovery.  What is the different on this functionality in the
> 3 products? Or perhaps there are more products than I’ve found.  Cost will
> be a factor for us, but I also consider that costs are not only product and
> maintenance costs, there are DBA and developer migration costs as well.
>
>
>
> Any insights or information is appreciated.
>
>
>
>
>
> *
>
> Sherrie Kubis
>
> Sr. Oracle DBA
>
> Information Technology Bureau
>
> Southwest Florida Water Management District
>
> 2379 Broad Street
>
> Brooksville, FL 34604-6899
>
> 352.796.7211 x4033
>
> sherrie.ku...@swfwmd.state.fl.us 
>
>
>
> Please take a moment to answer a few questions
>  and let us how we’re doing.
>
>
>
> *IMPORTANT NOTICE*
>
> *E-mails made or received in conjunction with the official business of the
> District are public records.  All e-mails sent to and from this address are
> automatically archived.  For more information regarding the State of
> Florida public records laws, please visit www.myflorida.com
> .*
>
>
>

Re: [GENERAL] PostgreSQL flavors

[Melvin Davidson]

On Tue, Feb 23, 2016 at 11:06 AM, Adrian Klaver 
wrote:

> On 02/23/2016 07:51 AM, Sherrie Kubis wrote:
>
>> Hello, my first post to the list, thank you for this place to ask
>> questions and get help.
>>
>
> Welcome.
>
>
>> Our management has tasked me with devising a plan to migrate our
>> existing databases from Oracle to PostgreSQL.  I’m researching and
>> getting familiar with PostgreSQL before getting a Linux box to start
>> learning and staging.  I have a long way to go, but it will be fun.
>>
>> Out of the gate, I can see different PostgreSQL products – PostgreSQL,
>> PostgreSQLPlus, EnterpriseDB Advanced Server.
>>
>
> Well you are looking at the community version PostgreSQL(
> http://www.postgresql.org/)  versus versions created by EnterpriseDB(
> http://www.enterprisedb.com). EDB has the community version wrapped in an
> installer which is free. Its other versions have addons and are fee based.
> There are also quite a few other companies that support Postgres:
>
> http://www.postgresql.org/support/professional_support/northamerica/
>
>
>> For staging I’ll likely start with the vanilla version.  I’ve been
>> searching for a comparison of different product sets; I’ve found some
>> things, but in other places different information.  There is a lot to
>> sift through.  For example, we are now using non-active Data Guard to a
>> standby for disaster recovery.  What is the different on this
>>
>
> I would take a look here:
>
> http://www.postgresql.org/docs/9.5/interactive/backup.html
>
> and here:
>
> http://www.postgresql.org/docs/9.5/interactive/high-availability.html
>
> in particular:
>
>
> http://www.postgresql.org/docs/9.5/interactive/different-replication-solutions.html
>
> Table 25-1
>
> functionality in the 3 products? Or perhaps there are more products than
>> I’ve found.  Cost will be a factor for us, but I also consider that
>> costs are not only product and maintenance costs, there are DBA and
>> developer migration costs as well.
>>
>
> I would suggest starting with the community version:
>
> http://www.postgresql.org/download/
>
> If you do not want to set up an entire machine, set up a Linux VM and
> install Postgres. It will not be the best indication of performance, but it
> will allow you to explore the feature set.
>
>
>> Any insights or information is appreciated.
>>
>
> Since you are looking at migrating from Oracle, see here:
>
> http://ora2pg.darold.net/
>
>
>> *
>>
>> Sherrie Kubis
>>
>> Sr. Oracle DBA
>>
>> Information Technology Bureau
>>
>> Southwest Florida Water Management District
>>
>> 2379 Broad Street
>>
>> Brooksville, FL 34604-6899
>>
>> 352.796.7211 x4033
>>
>> sherrie.ku...@swfwmd.state.fl.us 
>>
>>
>
> --
> 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
>

Basically, PostgreSQL is the free, public domain version. PostgreSQLPlus
and EDB Advanced Server are enhanced versions of PostgreSQL by
EnterpriseDB.com, However, there is a maintenance fee for the EDB Advanced
Server.
PostgreSQL Plus is a Cloud based version of PostgreSQL, and I believe there
is also a fee involved for that too. Your best path is to evaluate how much
memory and storage is currently used for the Oracle DB, then obtain a Linux
box based on that.  You should then install the latest free version of
PostgreSQL from one of the urls at http://www.postgresql.org/download/
based on which O/S you will be using.
Note that the latest GA version of PostgreSQL is 9.5.1
FYI, EnterpriseDB.com does have a Oracle to PostgreSQL migration tool (and
service) but there is also a fee involved for both.

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

Re: [GENERAL] PostgreSQL flavors

[Adrian Klaver]

On 02/23/2016 07:51 AM, Sherrie Kubis wrote:

Hello, my first post to the list, thank you for this place to ask
questions and get help.


Welcome.



Our management has tasked me with devising a plan to migrate our
existing databases from Oracle to PostgreSQL.  I’m researching and
getting familiar with PostgreSQL before getting a Linux box to start
learning and staging.  I have a long way to go, but it will be fun.

Out of the gate, I can see different PostgreSQL products – PostgreSQL,
PostgreSQLPlus, EnterpriseDB Advanced Server.


Well you are looking at the community version 
PostgreSQL(http://www.postgresql.org/)  versus versions created by 
EnterpriseDB(http://www.enterprisedb.com). EDB has the community version 
wrapped in an installer which is free. Its other versions have addons 
and are fee based. There are also quite a few other companies that 
support Postgres:


http://www.postgresql.org/support/professional_support/northamerica/



For staging I’ll likely start with the vanilla version.  I’ve been
searching for a comparison of different product sets; I’ve found some
things, but in other places different information.  There is a lot to
sift through.  For example, we are now using non-active Data Guard to a
standby for disaster recovery.  What is the different on this


I would take a look here:

http://www.postgresql.org/docs/9.5/interactive/backup.html

and here:

http://www.postgresql.org/docs/9.5/interactive/high-availability.html

in particular:

http://www.postgresql.org/docs/9.5/interactive/different-replication-solutions.html

Table 25-1


functionality in the 3 products? Or perhaps there are more products than
I’ve found.  Cost will be a factor for us, but I also consider that
costs are not only product and maintenance costs, there are DBA and
developer migration costs as well.


I would suggest starting with the community version:

http://www.postgresql.org/download/

If you do not want to set up an entire machine, set up a Linux VM and 
install Postgres. It will not be the best indication of performance, but 
it will allow you to explore the feature set.




Any insights or information is appreciated.


Since you are looking at migrating from Oracle, see here:

http://ora2pg.darold.net/



*

Sherrie Kubis

Sr. Oracle DBA

Information Technology Bureau

Southwest Florida Water Management District

2379 Broad Street

Brooksville, FL 34604-6899

352.796.7211 x4033

sherrie.ku...@swfwmd.state.fl.us 




--
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] PostgreSQL flavors

[Sherrie Kubis]

Hello, my first post to the list, thank you for this place to ask questions and 
get help.

Our management has tasked me with devising a plan to migrate our existing 
databases from Oracle to PostgreSQL.  I'm researching and getting familiar with 
PostgreSQL before getting a Linux box to start learning and staging.  I have a 
long way to go, but it will be fun.

Out of the gate, I can see different PostgreSQL products - PostgreSQL, 
PostgreSQLPlus, EnterpriseDB Advanced Server.
For staging I'll likely start with the vanilla version.  I've been searching 
for a comparison of different product sets; I've found some things, but in 
other places different information.  There is a lot to sift through.  For 
example, we are now using non-active Data Guard to a standby for disaster 
recovery.  What is the different on this functionality in the 3 products? Or 
perhaps there are more products than I've found.  Cost will be a factor for us, 
but I also consider that costs are not only product and maintenance costs, 
there are DBA and developer migration costs as well.

Any insights or information is appreciated.


*
Sherrie Kubis
Sr. Oracle DBA
Information Technology Bureau
Southwest Florida Water Management District
2379 Broad Street
Brooksville, FL 34604-6899
352.796.7211 x4033
sherrie.ku...@swfwmd.state.fl.us

Please take a moment to answer a few 
questions and let us how we're doing.

IMPORTANT NOTICE
E-mails made or received in conjunction with the official business of the 
District are public records.  All e-mails sent to and from this address are 
automatically archived.  For more information regarding the State of Florida 
public records laws, please visit www.myflorida.com.

Re: [GENERAL] Select specific tables in BDR

[Kaushal Shriyan]

On Tue, Feb 23, 2016 at 4:44 PM, Andreas Kretschmer  wrote:

>
>
> > Kaushal Shriyan  hat am 23. Februar 2016 um
> 11:43
> > geschrieben:
> >
> >
> > Hi,
> >
> > Is there a option of selecting tables in BDR which is a Multi Master PG
> > Replication (http://2ndquadrant.com/en/resources/bdr/).
> >
> > I mean if i have let's say t1,t2,t3,t4 and t5.  Can i have BDR only for
> > t1,t2 and t3 table ?
> >
> > Regards,
> >
> > Kaushal
>
>
> Yes, you have to define a replication set, see:
> http://bdr-project.org/docs/stable/replication-sets.html


Thanks Andreas for the answer.

Regards,

Kaushal

[GENERAL] sha256 certificate "unknown message digest algorithm"

[Frazer McLean]

Hi,

I am using PostgreSQL 9.4.6 and openssl 1.0.2f. If my server or client
certificate use SHA256, I get the following error on the client:

psql: SSL error: tlsv1 alert decrypt error

And the following log message on the server:

LOG:  could not accept SSL connection: unknown message digest algorithm

I could use -sha1 to generate my certificates but -sha256 should
work, right?

Thanks,

Frazer

Re: [GENERAL] Select specific tables in BDR

[Andreas Kretschmer]

> Kaushal Shriyan  hat am 23. Februar 2016 um 11:43
> geschrieben:
> 
> 
> Hi,
> 
> Is there a option of selecting tables in BDR which is a Multi Master PG
> Replication (http://2ndquadrant.com/en/resources/bdr/).
> 
> I mean if i have let's say t1,t2,t3,t4 and t5.  Can i have BDR only for
> t1,t2 and t3 table ?
> 
> Regards,
> 
> Kaushal


Yes, you have to define a replication set, see:
http://bdr-project.org/docs/stable/replication-sets.html


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

[GENERAL] Select specific tables in BDR

[Kaushal Shriyan]

Hi,

Is there a option of selecting tables in BDR which is a Multi Master PG
Replication (http://2ndquadrant.com/en/resources/bdr/).

I mean if i have let's say t1,t2,t3,t4 and t5.  Can i have BDR only for
t1,t2 and t3 table ?

Regards,

Kaushal

[GENERAL] multiple UNIQUE indices for FK

[Rafal Pietrak]

Hi,

For some time I'm struggling to get my schema "optimised" for a sort of
"message exchange" (or "document circulation") system.

For every record in the table of those messages I have:
1. SENDER
2. RECEIPIENT
3. unique (sender assigned)SN
4. ... and naturally all the other stuff, like the message itself,
timestamps, etc.

My plan is to have it unique-constraint against 1+3, for joins and to
keep the "sanity bonds" in force all the time.

So I figure to have:
ALTER ...msgs  ADD CONSTRINT sender_uniq UNIQUE (sender,SSN);

Unfortunately all that proved to be "not so good" for application level,
since there I "almost always" a need to select "MY" messages, which lead to:
SELECT * FROM msgs WHERE sender = "ME" UNION ALL SELECT * FROM msgs
WHERE receipient = "ME";

Which does not look so bad, but when one has to JOIN it with other
stuff, the application becomes "obfuscated" with complexity of those joins.

So I tried other approach. A table with columns like:
1. ME
2. THEM
3. FROMME bool (true if ME is sender, false otherwise).
4. sender unique serial (SSN)
6.  and the rest of it.

But this time I had to partition this table (on FROMME value), to be
able to correctly create different constraints depending on FROMME being
true or false. So I have:
ALTER ...msgs_from_me ADD CONSTRINT me_uniq UNIQUE (ME,SSN);
ALTER ... msgs_to_me ADD CONSTRINT them_uniq UNIQUE (THEM,SSN);

Now application level selects and joins are much simpler, like:
SELECT * FROM msgs m JOIN partners p USING (them);

But along the run, the specs for the system evolve, and currently I need
to asssign an additional unique serial, which sequentially lables every
message that "belongs" to ME irrespective if ME originated it or ME is a
recepient. And it have to be explicitly unique constrained for FK.

My problem is, that currently the table is partitioned.

Is there a way to have a unique constraint across partitions (inharited
tables)? And I'm not looking back to the initial (single table) schema,
since I'm unable to sreach my head around the concept of a unique
constraint that is able to cover IDs, which  sometimes are in the SENDER
column, while on other times in RECEPIENT.

Can anybody suggest any other way out of this mass? that is, apart from
siging off  ;7

Thenx,

-R


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