Re: [GENERAL] Replication Postgre > Oracle

Hi, >From Oracle to PostgreSQL, you could have a look at Goldengate. It does not support PostgreSQL as the source database. Regards, Jayadevan On Wed, Aug 7, 2013 at 1:54 PM, BOUVARD Aurélien < aurelien.bouv...@supinfo.com> wrote: > Hi all, > > ** ** > > My compagny would like to configure

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys wrote: > On Aug 8, 2013, at 4:11, Sergey Konoplev wrote: > > > create table node as ( > > id integer primary key, > > r integer, s integer, > > children integer[] element references node > > ); > > > > so you could download 9.3rc2 and experimant

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys wrote: > On Aug 8, 2013, at 4:11, Sergey Konoplev wrote: >> create table node as ( >> id integer primary key, >> r integer, s integer, >> children integer[] >> ); >> >> and check integrity by triggers. > > > Or, instead of attempting to reference

Re: [GENERAL] Self referencing composite datatype

On Aug 8, 2013, at 4:11, Sergey Konoplev wrote: > create table node as ( > id integer primary key, > r integer, s integer, > children integer[] element references node > ); > > so you could download 9.3rc2 and experimant with it. > > Now (on <=9.2.x) you can create the table without FK > >

Re: [GENERAL] Adding ip4r to Postgresql core?

On Wed, Aug 7, 2013 at 9:44 PM, Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> That's been proposed before, and rejected before, on the grounds that > >> since it doesn't support IPv6 its days are numbered. > > > Actually, that's not true. > > Ah, my information is obsolete. >

Re: [GENERAL] How to avoid Force Autovacuum

cat /proc/meminfo | grep -i huge AnonHugePages: 31576064 kB HugePages_Total: 0 HugePages_Free:0 HugePages_Rsvd:0 HugePages_Surp:0 Hugepagesize: 2048 kB cat /sys/kernel/mm/redhat_transparent_hugepage/enabled [always] never cat /sys/kernel/mm/redhat_transparent

Re: [GENERAL] How to avoid Force Autovacuum

On Wed, Aug 7, 2013 at 9:32 PM, Vishalakshi Navaneethakrishnan wrote: > cat /etc/issue > CentOS release 6.3 (Final) > > cat /proc/meminfo |grep Hugepagesize > Hugepagesize: 2048 kB Please show what commands below print. cat /proc/meminfo | grep -i huge cat /sys/kernel/mm/transparent_hugepa

Re: [GENERAL] Adding ip4r to Postgresql core?

Alvaro Herrera writes: > Tom Lane wrote: >> That's been proposed before, and rejected before, on the grounds that >> since it doesn't support IPv6 its days are numbered. > Actually, that's not true. Ah, my information is obsolete. > Quoting from the linked readme file, > : IP4R therefore suppo

Re: [GENERAL] Self referencing composite datatype

> > On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur wrote: > >> Hello, >> I wanted to create a composite datatype to represent a Node. So it would >> have a few attributes and an array of type Node which is the children of >> this node. >> create type Node as (r integer, s integer, children Node []);

Re: [GENERAL] How to avoid Force Autovacuum

Hi, > vacuumdb -F -z -h localhost -U postgres dbname It is not necessary to do. Autovacuum does it itself where and when needed. If we did not do this, then autovacuum will occur, Load was very high at that time and the server was unresponsive, To avoid this we are executing vacuum freeze analyz

Re: [GENERAL] Adding ip4r to Postgresql core?

Tom Lane wrote: > Joe Van Dyk writes: > > Any chance ip4r could be an official postgresql extension? > > That's been proposed before, and rejected before, on the grounds that > since it doesn't support IPv6 its days are numbered. Actually, that's not true. Quoting from the linked readme file,

Re: [GENERAL] Adding ip4r to Postgresql core?

Joe Van Dyk writes: > Any chance ip4r could be an official postgresql extension? That's been proposed before, and rejected before, on the grounds that since it doesn't support IPv6 its days are numbered. That consideration isn't getting any weaker as time passes. If we accepted ip4r, then how t

Re: [GENERAL] inserting huge file into bytea cause out of memory

On Wed, Aug 7, 2013 at 6:41 PM, liuyuanyuan wrote: > ** > > > Thanks for your last reply！ > I've test Large Object ( oid type )， and it seems better on out of > memory. > But, for the out of memory problem of bytea, we really have no idea > to > solve it ? Why there's no way to s

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 7:11 PM, Sergey Konoplev wrote: > so you could download 9.3rc2 and experimant with it. Sorry, 9.3beta2 of course. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gr

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur wrote: > I wanted to create a composite datatype to represent a Node. So it would > have a few attributes and an array of type Node which is the children of > this node. > create type Node as (r integer, s integer, children Node []); > But i get error

[GENERAL] Performance of ORDER BY RANDOM to select random rows?

Hi, I have a Django application where we need to pull random rows out of a table. According to the Django documentation: https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by Note: order_by('?') queries may be expensive and slow, depending on the > database backend you’re using.

Re: [GENERAL] Pl/Python runtime overhead

On Wed, Aug 7, 2013 at 7:43 AM, Seref Arikan wrote: > When a pl/python based function is invoked, does it keep a python runtime > running across calls to same function? That is, if I use connection pooling, > can I save on the python runtime initialization and loading costs? You can use the follo

Re: [GENERAL] inserting huge file into bytea cause out of memory

liuyuanyuan From: Michael Paquier Date: 2013-08-07 15:26 To: Albe Laurenz CC: liuyuanyuan; Chris Travers; tv; pgsql-general Subject: Re: [GENERAL] inserting huge file into bytea cause out of memory On Wed, Aug 7, 2013 at 3:56 PM, Albe Laurenz wrote: > liuyuanyuan wrote: >>> By the way, my pr

Re: [GENERAL] How to avoid Force Autovacuum

On Wed, Aug 7, 2013 at 2:46 AM, Vishalakshi Navaneethakrishnan wrote: > Daily once we are executing "Vacuum Freeze analyze" -- To prevent > transaction id wraparound > using this command > vacuumdb -F -z -h localhost -U postgres dbname It is not necessary to do. Autovacuum does it itself where an

Re: [GENERAL] Commit problem in read-commited isolation level

On 08/07/2013 04:54 PM, S H wrote: Hi, I have faced very strange problem in one of psotgresql query in one of the production environment. It is working fine in development and other environment. Current value in colname = 5; Update tablename set colname = 0 where key = 18; commit , in parallel

[GENERAL] Commit problem in read-commited isolation level

Hi, I have faced very strange problem in one of psotgresql query in one of the production environment. It is working fine in development and other environment. Current value in colname = 5;Update tablename set colname = 0 where key = 18;commit , in parallel to above queries ( either vacuum or re

Re: [GENERAL] Exit code -1073741819

On Wed, Aug 7, 2013 at 2:46 PM, Carlos Henrique Reimer wrote: > Could finally fix it. Used the binary search approach to identify the wrong > tuples and removed them by ctid, 9 rows were removed and all of them > belonged to the same block. It is good. I still highly recommend to recreate the tab

Re: [GENERAL] Exit code -1073741819

Hi, Could finally fix it. Used the binary search approach to identify the wrong tuples and removed them by ctid, 9 rows were removed and all of them belonged to the same block. I believe it is not easy to identify the root cause for the corruption but does any one have some directions I could fol

[GENERAL] Adding ip4r to Postgresql core?

Hi, Any chance ip4r could be an official postgresql extension? It's got a lot of advantages over the existing cidr/inet stuff. https://github.com/RhodiumToad/ip4r-historical/blob/master/README.ip4r Joe

Re: [GENERAL] [tsearch2] Problem with case sensitivity (or with creating own dictionary)

Ok, so to be sure if I understand everything - first I should install a postgresql-contrib extension. Next, there will appear a contrib/dict_int directory with dict_int sourcecode inside, which I can modify. Then, I'll be able to install this modified dictionary, and it would be working properl

Re: [GENERAL] PostrgreSQL Commercial restrictions?

Richard, * Richard Broersma (richard.broer...@gmail.com) wrote: > On Wed, Aug 7, 2013 at 8:43 AM, David Johnston wrote: > > Where the PostgreSQL license comes into play is if you make alterations to > > the PostgreSQL database itself - the underlying engine implemented in C and > > to some degree

Re: [GENERAL] Populating array of composite datatype

Just wanted to put a short thank you note in here. I've been using PostgreSQL for a while, but only using basic SQL. The information in this thread pointed me to the information that I needed to read about to solve a problem which has been tormenting me, off and on, for a couple of months. I just c

Re: [GENERAL] PostrgreSQL Commercial restrictions?

On Aug 7, 2013, at 8:23 AM, Eliseo Viola wrote: > Hello. > I have been reading the -http://opensource.org/licenses/postgresql- to know > if i can use PostgreSQL in a Privative Closed Commercial Software (The worst > of the worst :P ). in the company where i work. > Is there any restriction or

Re: [GENERAL] How to prevent clear screen when query finish ?

On 08/07/2013 08:53 AM, Condor wrote: Hello, sorry for dumb question, did any one can tell me how the hell I can remove clear screen after finish the sql query from console ? This probably have some idea, but for me look like very ... not good idea. When I run query from console like: SELECT *

Re: [GENERAL] PostrgreSQL Commercial restrictions?

On Wed, Aug 7, 2013 at 8:43 AM, David Johnston wrote: > Where the PostgreSQL license comes into play is if you make alterations to > the PostgreSQL database itself - the underlying engine implemented in C and > to some degree the supporting utilities written in various languages. > Anything contr

Re: [GENERAL] Staging Database

On 08/07/2013 03:38 AM, BladeOfLight16 wrote: On Wed, Aug 7, 2013 at 4:43 AM, Luca Ferrari > wrote: ... I wasn't very careful with my wording. Sorry about that. There will be updates and possibly deletions as well as additions. Furthermore, the public versio

[GENERAL] How to prevent clear screen when query finish ?

Hello, sorry for dumb question, did any one can tell me how the hell I can remove clear screen after finish the sql query from console ? This probably have some idea, but for me look like very ... not good idea. When I run query from console like: SELECT * FROM table_x; I got the result ...

Re: [GENERAL] PostrgreSQL Commercial restrictions?

On Wed, Aug 7, 2013 at 8:23 AM, Eliseo Viola wrote: > I have been reading the -http://opensource.org/licenses/postgresql- to know > if i can use PostgreSQL in a Privative Closed Commercial Software (The > worst of the worst :P ). in the company where i work. > Is there any restriction or limit to

Re: [GENERAL] PostrgreSQL Commercial restrictions?

On 08/07/2013 08:23 AM, Eliseo Viola wrote: Hello. I have been reading the -http://opensource.org/licenses/postgresql- to know if i can use PostgreSQL in a Privative Closed Commercial Software (The worst of the worst :P ). in the company where i work. Is there any restriction or limit to do it.?

Re: [GENERAL] PostrgreSQL Commercial restrictions?

I am not a lawyer and cannot evaluate your risk profile and usage but: Simply connecting to, creating objects, and running queries against PostgreSQL does not encumber your external application at all. The database schema you create (the objects inside the database) is not affected. Deploying Po

Re: [GENERAL] Staging Database

BladeOfLight16 wrote > The company I work for has a client who has expressed interest in having a > staging database for their data. Staging as in they add data, do some > QCing, then push to a database with an identical schema to make it public. > Fortunately, we're not doing that right now, but i

[GENERAL] PostrgreSQL Commercial restrictions?

Hello. I have been reading the -http://opensource.org/licenses/postgresql- to know if i can use PostgreSQL in a Privative Closed Commercial Software (The worst of the worst :P ). in the company where i work. Is there any restriction or limit to do it.?

Re: [GENERAL] Self referencing composite datatype

Igor Neyman wrote >> >> create type node as (r integer, s integer); alter type node add attribute >> children node[]; >> > > Under 9.2.2 I'm getting an error: > > ERROR: composite type node cannot be made a member of itself I'm not sure why the limitation exists (probably something to do with

Re: [GENERAL] Self referencing composite datatype

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of David Johnston > Sent: Wednesday, August 07, 2013 10:35 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Self referencing composite datatype > > Sameer

Re: [GENERAL] Populating array of composite datatype

Sameer Thakur wrote > insert into test values (ARRAY[abc(1,2)]); but got error When you use a function form of casting like this you are basically short-circuiting the type conversion mechanisms built into PostgreSQL by directly calling the conversion function instead of actually telling the syste

[GENERAL] Pl/Python runtime overhead

Greetings, Somehow I have failed to find the appropriate keywords for successful results for my question. When a pl/python based function is invoked, does it keep a python runtime running across calls to same function? That is, if I use connection pooling, can I save on the python runtime initiali

Re: [GENERAL] Self referencing composite datatype

Sameer Thakur wrote > Hello, > I wanted to create a composite datatype to represent a Node. So it would > have a few attributes and an array of type Node which is the children of > this node. > create type Node as (r integer, s integer, children Node []); > But i get error type Node[] does not exis

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur wrote: > Hello, > I wanted to create a composite datatype to represent a Node. So it would > have a few attributes and an array of type Node which is the children of > this node. > create type Node as (r integer, s integer, children Node []); > But i

[GENERAL] Self referencing composite datatype

Hello, I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node. create type Node as (r integer, s integer, children Node []); But i get error type Node[] does not exist. I understand that Node is not

Re: [GENERAL] Populating array of composite datatype

Thank you Raghavendra and Chris, both approaches work.

Re: [GENERAL] Populating array of composite datatype

> On Wed, Aug 7, 2013 at 3:53 AM, Raghavendra < > raghavendra@enterprisedb.com> wrote: > >> >> postgres=# insert into foo values (array[row(1,2)::abc]); >> >> > Also because all array members must be of the same db type, you can: > > insert into foo values (array[row(1,2)]::abc[]). This can be

Re: [GENERAL] Populating array of composite datatype

On Wed, Aug 7, 2013 at 3:53 AM, Raghavendra < raghavendra@enterprisedb.com> wrote: > > postgres=# insert into foo values (array[row(1,2)::abc]); > > Also because all array members must be of the same db type, you can: insert into foo values (array[row(1,2)]::abc[]). This can be helpful if yo

Re: [GENERAL] Populating array of composite datatype

On Wed, Aug 7, 2013 at 4:04 PM, Sameer Thakur wrote: > Hello, > I have a composite datatype abc which has two integer fields x,y. > I have a table Test which has an array of abc. > I am trying to populate Test. Tried > insert into test values (ARRAY[abc(1,2)]); but got error > ERROR: function ab

Re: [GENERAL] Staging Database

On Wed, Aug 7, 2013 at 4:43 AM, Luca Ferrari wrote: > Not really helpful, but here are my considerations. > The low frequency and the preference for a single server suggest me a > dump and restore cycle on two databases, assuming this is possible due > to not high volume data. > I would also cons

[GENERAL] Populating array of composite datatype

Hello, I have a composite datatype abc which has two integer fields x,y. I have a table Test which has an array of abc. I am trying to populate Test. Tried insert into test values (ARRAY[abc(1,2)]); but got error ERROR: function abc(integer, integer) does not exist Is there anyway for doing this?

Re: [GENERAL] Hierarchical numeric data type

This depends on exactly what you need. In the end usually you are going to want to convert things into integer arrays. In LedgerSMB we use integers and foreign keys to handle hierarchies, and then convert them to int arrays via WITH RECURSIVE CTE's and text strings . This is one of those things

[GENERAL] How to avoid Force Autovacuum

Hi All, We have one production database server , having 6 DBs, Postgres 9.2.1 version. This is my vacuum settings in Production database #autovacuum = on# Enable autovacuum subprocess? 'on' # requires track_counts to also be on. lo

Re: [GENERAL] Replication Postgre > Oracle

Postgres Plus Advanced Server (PPAS) is a licensed version of PostgreSQL owned by EnterpriseDB. You need to buy a support contract to use this software (although there is a 45 day trial period). The component you are talking about is xDB Replication Server, which is part of the PPAS product or can

Re: [GENERAL] Replication Postgre > Oracle

On 07/08/13 20:24, BOUVARD Aurélien wrote: Hi all, My compagny would like to configure replication between PostgreSQL 9.2.4 and Oracle Database (11g and maybe 12c soon). We saw that/Postgres Plus Database Replication /could be a good solution for us.// // We also thank to develop a soluti

Re: [GENERAL] Staging Database

On Wed, Aug 7, 2013 at 6:00 AM, BladeOfLight16 wrote: > The company I work for has a client who has expressed interest in having a > staging database for their data. Staging as in they add data, do some QCing, > then push to a database with an identical schema to make it public. > Fortunately, we'

[GENERAL] Replication Postgre > Oracle

Hi all, My compagny would like to configure replication between PostgreSQL 9.2.4 and Oracle Database (11g and maybe 12c soon). We saw that Postgres Plus Database Replication could be a good solution for us. We also thank to develop a solution based on trigger and/or WAL , but we didn't know if

Re: [GENERAL] inserting huge file into bytea cause out of memory

On Wed, Aug 7, 2013 at 3:56 PM, Albe Laurenz wrote: > liuyuanyuan wrote: >> By the way, my project is about migrating Oracle data of BLOB type to >> PostgreSQL database. The out of memory error occurred between migrating >> Oracle BLOB to PostgreSQL bytea. Another question, if I can't migrate BL