Re: [GENERAL] Windows Installation User account - Correct database for us

> When the database is installed a postgreSQL user account is created > which in most cases will be the second user account on the PC. No, not any longer. This has changed with 9.1 or 9.2 (don't remember). The Postgres service that is installed uses the "local network" account. > The programme

Re: [GENERAL] Use of tsvector in array

Huang, Suya wrote > Hi, > > We have the requirement of using the data type tsvector [], however, I > didn't find out how to: > > * Use array operator together with tsquery operator > > o I have to unnest the array and then do query like "ts@@ > to_tsquery('ipod')" You will have to cre

Re: [GENERAL] Windows Installation User account - Correct database for us

Don Brown wrote > Thank you and appreciate any comments/suggestions Host the database in a shared-tenent arrangement and have your application remotely connect to it or to an intermediary application that will then perform the work and simply deal with input/output with the client. Dave -- V

[GENERAL] Use of tsvector in array

Hi, We have the requirement of using the data type tsvector [], however, I didn't find out how to: * Use array operator together with tsquery operator o I have to unnest the array and then do query like "ts@@ to_tsquery('ipod')" * Create GIN index on tsvector[] o ERROR: d

Re: [GENERAL] Windows Installation User account - Correct database for us

On 7/10/2014 5:01 PM, Don Brown wrote: When the database is installed a postgreSQL user account is created which in most cases will be the second user account on the PC. The result of this is the user now has to select the user account when ever the computer is restarted. I thought I saw tha

Re: [GENERAL] Windows Installation User account - Correct database for us

On Jul 10, 2014, at 5:01 PM, Don Brown wrote: > Hello > > We are writing a small application and we are trying to determine if > PostgreSQL is the right database for us. > > The application at this stage is only for a single user and commonly for > persons with little computer expertise. >

[GENERAL] how does full text searching tokenize words ? can it be altered?

I'm getting a handful of 'can not index words longer than 2047 characters' on my `gin` indexes. 1. does this 2047 character count correspond to tokens / indexed words? 2. if so, is there a way to lower this number ? 3. is there a way to profile the index for the frequency of tokens ? ( apolo

[GENERAL] Windows Installation User account - Correct database for us

Hello We are writing a small application and we are trying to determine if PostgreSQL is the right database for us. The application at this stage is only for a single user and commonly for persons with little computer expertise. When the database is installed a postgreSQL user account is cre

[GENERAL] Standby Server and Barman Backup on production system

Hello, I had followed this discuss (http://www.postgresql.org/message-id/CABRT9RAXzUa=_zT_M4Z1vyDuFkpgNCZLUnRTUO5gvK2kKkNu=a...@mail.gmail.com). I have a similar problem now: I use one Postgres Server as Master an an other one as Standby (WAL archives). I do also a daily backup of the Master Serv

Re: [GENERAL] invalid connection type "listen_addresses='*'

>> It is non-specific since it is assumed at this point in the documentation >> that you realize ALL "configuration parameters" are defined in >> postgres.conf or its includes. I think the comments in pg_hba.conf are a lot more misleading than the online documentation, and are more likely to be re

Re: [GENERAL] invalid connection type "listen_addresses='*'

David G Johnston wrote > > Aram Fingal wrote >>> >>> "listen_addresses='*'" parameter doesn't belong in pg_hba.conf >>> >>> This parameter should be in postgresql.conf >> >> >> Thanks. That was really unclear, at least the way I followed the online >> documentation: >> >> http://www.postgre

Re: [GENERAL] invalid connection type "listen_addresses='*'

Aram Fingal wrote >> >> "listen_addresses='*'" parameter doesn't belong in pg_hba.conf >> >> This parameter should be in postgresql.conf > > > Thanks. That was really unclear, at least the way I followed the online > documentation: > > http://www.postgresql.org/docs/9.1/static/auth-pg-hba-co

Re: [GENERAL] invalid connection type "listen_addresses='*'

> > "listen_addresses='*'" parameter doesn't belong in pg_hba.conf > > This parameter should be in postgresql.conf Thanks. That was really unclear, at least the way I followed the online documentation: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html …even after following th

Re: [GENERAL] invalid connection type "listen_addresses='*'

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Aram Fingal Sent: Thursday, July 10, 2014 4:40 PM To: Postgres-General General Subject: [GENERAL] invalid connection type "listen_addresses='*' I just tried to set up a PostgreSQL server on an existi

Re: [GENERAL] invalid connection type "listen_addresses='*'

> listen_addresses='*' I'm pretty sure that listen_addresses belongs in postgresql.conf, not pg_hba.conf. Paul On Thu, Jul 10, 2014 at 1:40 PM, Aram Fingal wrote: > > I just tried to set up a PostgreSQL server on an existing instillation of > Ubuntu 13.10 server but I am getting an error tr

Re: [GENERAL] Should I partition this table?

On Thu, 10 Jul 2014 13:16:05 -0700 Jeff Janes wrote: > > > > In general, yes, given the information you provided. A parition on > > ParentID % $something should improve performance. > > PostgresSQL's constraint exclusion logic is not smart enough to turn a > simple equality into a mod equality.

[GENERAL] invalid connection type "listen_addresses='*'

I just tried to set up a PostgreSQL server on an existing instillation of Ubuntu 13.10 server but I am getting an error trying to start the server and I am not finding anything relevant to the error searching the web. Here’s what I did to install: $ sudo apt-get install postgresql $ sudo apt-ge

Re: [GENERAL] Should I partition this table?

On Thu, Jul 10, 2014 at 11:36 AM, AlexK wrote: > Kevin, > > What would be the advantages of partitioning on ranges of ParentID? Each > query will touch at most one partition. I might or might not get PK indexes > one level of depth less. > > I understand that I will CLUSTER these smaller tables a

Re: [GENERAL] Should I partition this table?

On Thu, Jul 10, 2014 at 8:20 AM, Bill Moran wrote: > On Thu, 10 Jul 2014 07:59:20 -0700 (PDT) AlexK wrote: > > > My table currently uses up 62 GB of storage, and it has 450 M rows. This > > narrow table has a PK on (ParentID, ChildNumber), and it has between 20K > and > > 50K of child rows per p

Re: [GENERAL] checkpoint

On 2014-07-10 13:02, Guillaume Lelarge wrote: > 2014-07-10 20:56 GMT+02:00 Yves Dorfsman >: > > > Hi, > > If I run checkpoint from psql, is it applied to all the databases? > > What if I do it though an API? When connecting with psycopg2, I'm forced > to >

Re: [GENERAL] checkpoint

2014-07-10 20:56 GMT+02:00 Yves Dorfsman : > > Hi, > > If I run checkpoint from psql, is it applied to all the databases? > > What if I do it though an API? When connecting with psycopg2, I'm forced to > specify a database name, if I use "dbname=postgres", and execute > "checkpoint;", is it applie

Re: [GENERAL] Should I partition this table?

On Thu, 10 Jul 2014 11:36:27 -0700 (PDT) AlexK wrote: > What would be the advantages of partitioning on ranges of ParentID? Each > query will touch at most one partition. I might or might not get PK indexes > one level of depth less. You need to partition by ParentID in order for the example qu

[GENERAL] checkpoint

Hi, If I run checkpoint from psql, is it applied to all the databases? What if I do it though an API? When connecting with psycopg2, I'm forced to specify a database name, if I use "dbname=postgres", and execute "checkpoint;", is it applied to all the databases? Thanks. -- Yves. -- Sent vi

Re: [GENERAL] Should I partition this table?

Kevin, What would be the advantages of partitioning on ranges of ParentID? Each query will touch at most one partition. I might or might not get PK indexes one level of depth less. I understand that I will CLUSTER these smaller tables and benefit from that. Other than clustering, what are other

Re: [GENERAL] Should I partition this table?

AlexK wrote: > For now, all the data fits in the cache: the box has 384GB of > RAM. But I want to be ready for later, when we have more data. It > is easier to refactor my table now, when it is still smallish. Makes sense. > Children are only added to recently added parents, and they are > all

Re: [GENERAL] Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

That did the trick! Thanks, Tom! On Thu, Jul 10, 2014 at 8:47 AM, Tom Lane wrote: > Chris Hanks writes: >> CREATE VIEW tables AS >> SELECT a.*, b.col AS other_col >> FROM a >> LEFT JOIN b ON a.id = b.id >> UNION ALL >> SELECT c.*, d.col AS other_col >> FROM c >> LEFT JOIN d ON c.id

Re: [GENERAL] Should I partition this table?

On Thu, 10 Jul 2014 08:40:59 -0700 (PDT) AlexK wrote: > Bill, > > Regarding "SELECT performance improve nearly linerally to the number of > partitions," - can you elaborate why? If I split my table into several > partitions, even the index depth may stay the same, because the PK is > narrow, it

Re: [GENERAL] Standby Server and Barman Backup on production system

On Thu, Jul 10, 2014 at 2:24 AM, basti wrote: > Hello, > I had followed this discuss > (http://www.postgresql.org/message-id/CABRT9RAXzUa=_zT_M4Z1vyDuFkpgNCZLUnRTUO5gvK2kKkNu=a...@mail.gmail.com). > > I have a similar problem now: > > I use one Postgres Server as Master an an other one as Standb

Re: [GENERAL] Should I partition this table?

Kevin, For now, all the data fits in the cache: the box has 384GB of RAM. But I want to be ready for later, when we have more data. It is easier to refactor my table now, when it is still smallish. Children are only added to recently added parents, and they are all added/updated/deleted at once.

Re: [GENERAL] Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

Chris Hanks writes: > CREATE VIEW tables AS > SELECT a.*, b.col AS other_col > FROM a > LEFT JOIN b ON a.id = b.id > UNION ALL > SELECT c.*, d.col AS other_col > FROM c > LEFT JOIN d ON c.id = d.id; > EXPLAIN ANALYZE > SELECT * > FROM tables > WHERE id = 89; -- Index scans, as expec

Re: [GENERAL] Should I partition this table?

Bill, Regarding "SELECT performance improve nearly linerally to the number of partitions," - can you elaborate why? If I split my table into several partitions, even the index depth may stay the same, because the PK is narrow, it only consists of 2 4-byte integers. My selects are distributed more

Re: [GENERAL] Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

I need it the way it is. It's a foreign key in the actual query. Thanks! On Thu, Jul 10, 2014 at 8:31 AM, Igor Neyman wrote: >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of Chris Hanks >> Sent: Thursday, July

Re: [GENERAL] Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Chris Hanks > Sent: Thursday, July 10, 2014 5:02 AM > To: PostgreSQL General > Subject: [GENERAL] Joining on a view containing a UNION ALL produces a > suboptimal plan

Re: [GENERAL] Should I partition this table?

AlexK wrote: > My table currently uses up 62 GB of storage, and it has 450 M > rows. This narrow table has a PK on (ParentID, ChildNumber), and > it has between 20K and 50K of child rows per parent. > > The data is inserted daily, rarely modified, never deleted. The > performance of modifications

Re: [GENERAL] Should I partition this table?

On Thu, 10 Jul 2014 07:59:20 -0700 (PDT) AlexK wrote: > My table currently uses up 62 GB of storage, and it has 450 M rows. This > narrow table has a PK on (ParentID, ChildNumber), and it has between 20K and > 50K of child rows per parent. > > The data is inserted daily, rarely modified, never d

[GENERAL] Should I partition this table?

My table currently uses up 62 GB of storage, and it has 450 M rows. This narrow table has a PK on (ParentID, ChildNumber), and it has between 20K and 50K of child rows per parent. The data is inserted daily, rarely modified, never deleted. The performance of modifications is not an issue. The only

Re: [GENERAL] BAKUP ISSUE

De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de hubert depesz lubaczewski Envoyé : July-09-14 9:55 AM On Wed, Jul 9, 2014 at 3:28 PM, Ramesh T mailto:rameshparnandit...@gmail.com>> wrote: Yes,not an error it is a warning and archive is wo

Re: [GENERAL] php password authentication failed for user ...

On Wed, Jul 9, 2014 at 5:37 AM, basti wrote: > #hostall all 0.0.0.0 0.0.0.0 md5 > did not work. > > If it really starts with a # like you show it above, it's just a comment and pretty much guaranteed not to do anything. Cheers, Ken -- AGENCY Software A

Re: [GENERAL] php password authentication failed for user ...

Hi: On Wed, Jul 9, 2014 at 2:37 PM, basti wrote: > I don't know whats wrong there > hostmydns mydnslocalhost trust > works well and > #hostall all 0.0.0.0 0.0.0.0 md5 > did not work. > I use Postgres 9.3.4-1.pgdg70+1.

[GENERAL] Standby Server and Barman Backup on production system

Hello, I had followed this discuss (http://www.postgresql.org/message-id/CABRT9RAXzUa=_zT_M4Z1vyDuFkpgNCZLUnRTUO5gvK2kKkNu=a...@mail.gmail.com). I have a similar problem now: I use one Postgres Server as Master an an other one as Standby (WAL archives). I do also a daily backup of the Master Serv

[GENERAL] Joining on a view containing a UNION ALL produces a suboptimal plan on 9.3.4

Hi everyone - I have a slow query issue in an app I'm working on. I'm unfortunately not at liberty to share the query/schema details, but I've put together a very similar reproduction of the issue: - CREATE TABLE a (id integer primary key, col integer); CREATE TABLE b (id integer primary key