Re: [GENERAL] yum repository packages 9.0 and 9.1 libpq conflict

On 03/14/12 11:40 PM, Devrim GÜNDÜZ wrote: I have created a ticket for that, but could not close yet. You can see the progress from here: http://wiki.pgrpms.org/ticket/77 I don't see anything but the ticket itself? anyways, I'm quite sure this is the problem... # cat /etc/ld.so.conf.d/postg

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

The 8.1 version of the docu explicitly outlined the migration, the 9.1 version no longer covers the way things were before 8.1. In the meantime I also found which cleared things up exhaustively and by example. Alex Am 14.

Re: [GENERAL] copy in date string "00-00-00 00:00:00"

On Wed, 2012-03-14 at 21:52 -0700, Mark Phillips wrote: > I am not familiar with sed, except for some trivial bits I nicked off > the web. Enough to know it works, and to be dangerous. Nonetheless, > using SED may be the way to go as there are two tables that contain a > bit over 3,000,000 rows eac

[GENERAL] Backups

Hello all Very simple question - does pg_dump/dumpall hit the server in terms of database performance? We currently do nightly backups and I want to move to hourly backups but not at the expense of hogging all the resources for 5 mins. Pg_dumpall is currently producing a 1GB file - that's t

Re: [GENERAL] Backups

Hi Richard, it's no easy answer. If your server has plenty of free resources there won't be trouble, but I do have customers who cannot even imagine of launching a dump in normal traffic hours. How loaded is your box, currently? Cheerio Bèrto On 15 March 2012 12:15, Richard Harley wrote: > **

Re: [GENERAL] Backups

Thanks for a quick reply. The server has 6 cores, 6GB ram and top gets to 2.3-2.5 load average when running the dumpall. So I assume we are nowhere near this causing performance issues for users? Thanks Rich On 15/03/12 12:21, Bèrto ëd Sèra wrote: Hi Richard, it's no easy answer. If your s

Re: [GENERAL] Backups

Hi yes, if you get like ~2.5 when running it in peak hour it should definitely be harmless. Bèrto On 15 March 2012 12:37, Richard Harley wrote: > ** > Thanks for a quick reply. The server has 6 cores, 6GB ram and top gets to > 2.3-2.5 load average when running the dumpall. So I assume we are n

[GENERAL] WHERE IN (subselect) versus WHERE IN (1,2,3,)

My apologies, I'm sure this question has been asked before but I couldn't find anything on the list that meant anything to me. We have a table "contexts" with 1.6 million rows, and a table "articles" with 1.4 million rows, where an "article" is a particular kind of "context". We want to select fr

[GENERAL] Fetch from cursor with indexed sorting

Hello I have CURSOR for query SELECT a FROM table ORDER BY xyz, where table a is extremely large and xyz is hard-to-evaluate, but indexable expression(using gist). As far as I understand, on SELECT a FROM table ORDER BY xyz LIMIT X query postgres do not fetch entire index or table, but it uses "n

Re: [GENERAL] Backups

On Thu, Mar 15, 2012 at 6:15 AM, Richard Harley wrote: > Hello all > > Very simple question -  does pg_dump/dumpall hit the server in terms of > database performance? We currently do nightly backups and I want to move to > hourly backups but not at the expense of hogging all the resources for 5 >

Re: [GENERAL] yum repository packages 9.0 and 9.1 libpq conflict

John R Pierce writes: > CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.1' . > muh oh. why is that?!? It's distro policy on all Red Hat-based systems (and probably others as well) not to use rpath; you're supposed to use ldconfig instead. It's thus totally unsurprising that removing a

Re: [GENERAL] Fetch from cursor with indexed sorting

Andrey Chursin writes: > I need to create cursor, open it, fetch records one-by-one and > aggregate. Then on some condition I stop fetching and return > aggregated value. In fact in most cases my procedure scans a little > part of table. > But does postgres understand such usage of index and curs

Re: [GENERAL] Backups

Richard Harley wrote: > Very simple question - does pg_dump/dumpall hit the server in terms of database performance? We > currently do nightly backups and I want to move to hourly backups but not at the expense of hogging > all the resources for 5 mins. > > Pg_dumpall is currently producing a 1GB

[GENERAL] Using copy with a file containing blank rows

Hi All, I am trying to use COPY to import postgresql logs into a postgresql database for further review and sorting. The problem I'm encountering happens when COPY hits a blank row: development=# COPY log development-# FROM 'D:/PostgreSQL/9.1/pg_log/postgresql-2012-03-15_093730.log' develo

Re: [GENERAL] Did xpath_table get dropped.

On 03/14/2012 09:49 PM, Rob Sargent wrote: On 03/14/2012 08:57 PM, Tom Lane wrote: Rob Sargent writes: I don't see anything to that effect in the release notes I've looked at. That's cause it's still there. Did you remember to build/install contrib/xml2? The xpath functions you're listing are

Re: [GENERAL] Backups

Perhaps a RAM DISK could be considered in the equation On Thu, 2012-03-15 at 16:30 +0100, Albe Laurenz wrote: > Richard Harley wrote: > > Very simple question - does pg_dump/dumpall hit the server in terms > of database performance? We > > currently do nightly backups and I want to move to ho

[GENERAL] how to measure wal_buffer usage

After reading this interesting article on shared_buffers and wal_buffers: http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html it got me wondering if my settings were ideal. Is there some way to measure wal_buffer usage in real time, so that I could simply monitor it for som

Re: [GENERAL] Using copy with a file containing blank rows

On 03/15/2012 09:17 AM, George Weaver wrote: > Hi All, > > I am trying to use COPY to import postgresql logs into a postgresql > database for further review and sorting. Are you using the CSV format to log to the Postgres log?: http://www.postgresql.org/docs/9.1/interactive/runtime-config-loggin

[GENERAL] How to perform full text search

User can enter any number of words as search string. In shopping cart the following query is used to find products, eq. if "red cat" is entered: select * from products where productname ilike '%'||'red cat'||'%' or productdescription ilike '%'||'red cat'||'%' limit 100 This does not find pro

Re: [GENERAL] psql latex and newlines

Excerpts from Wim Bertels's message of jue feb 23 12:46:29 -0300 2012: > > > > > the problem arises when u have a field value that contains a newline > > > > > character, when this field is not the first column, then all the data > > > > > after this newline comes in the first column.. > > > > >

Re: [GENERAL] yum repository packages 9.0 and 9.1 libpq conflict

On 03/15/12 7:27 AM, Tom Lane wrote: It's distro policy on all Red Hat-based systems (and probably others as well) not to use rpath; you're supposed to use ldconfig instead. It's thus totally unsurprising that removing a package's /etc/ld.so.conf.d file breaks it. ouch. I didn't realize that.

[GENERAL] undo update

Hi all. Today an accident happened on one of my databases. I have a table named "payments" with about 5400 rows. I have done a query "update payments set amount = 0; where id in (2354,2353,1232)". Please note the semicolon inside — I missed it =( Now all my data is lost. And after this happened I

Re: [GENERAL] undo update

On Thu, Mar 15, 2012 at 8:22 AM, Ivan wrote: > Hi all. > > Today an accident happened on one of my databases. I have a table named > "payments" with about 5400 rows. I have done a query "update payments set > amount = 0; where id in (2354,2353,1232)". Please note the semicolon inside > — I missed

[GENERAL] Indexing MS/Open Office and PDF documents

Hi, We are looking to use Postgres 9 for the document storing and would like to take advantage of the full text search capabilities. We have hard time identifying MS/Open Office and PDF parsers to index stored documents and make them available for text searching. Any advice would be appreciated.

Re: [GENERAL] undo update

I have installed xlogviewer and it gives me data like that: [cur:0/5770E87C, xid:355075, rmid:10(Heap), len:88/116, prev:0/5770E840] > update: s/d/r:1663/90693/107093 block 1 off 36 to block 107 off 30 > [cur:0/5770E8F0, xid:355075, rmid:11(Btree), len:34/62, prev:0/5770E87C] > insert_leaf: s/d/r:

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com wrote: > Hi, > > We are looking to use Postgres 9 for the document storing and would > like to take advantage of the full text search capabilities. We have > hard time identifying MS/Open Office and PDF parsers to index stored > d

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On 15/03/12 21:12, Jeff Davis wrote: On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com We have hard time identifying MS/Open Office and PDF parsers to index stored documents and make them available for text searching. The first step is to find a library that can parse such

Re: [GENERAL] Indexing MS/Open Office and PDF documents

On Thu, Mar 15, 2012 at 4:12 PM, Jeff Davis wrote: > On Fri, 2012-03-16 at 01:57 +0530, alexander.bager...@cognizant.com > wrote: >> Hi, >> >> We are looking to use Postgres 9 for the document storing and would >> like to take advantage of the full text search capabilities. We have >> hard time id

Re: [GENERAL] undo update

On 03/15/2012 07:22 AM, Ivan wrote: Hi all. Today an accident happened on one of my databases. I have a table named "payments" with about 5400 rows. I have done a query "update payments set amount = 0; where id in (2354,2353,1232)". Please note the semicolon inside — I missed it =( Now all

[GENERAL] Commit hits time-out before CommandTimeout

Hello, I'm not sure if it is a Npgsql or a PostgreSQL issue which we run into at the office. I have also posted this on the Npgsql forum (see http://pgfoundry.org/forum/forum.php?thread_id=11381&forum_id=519). For a project we have a windows service which synchronizes an online database from a su

Re: [GENERAL] Temporal foreign keys

On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote: > Hey, > > how can I implement temporal foreign keys with postgresql? Is writing > triggers the only way to enforce temporal referential integrity > currently? Yes, currently that's the only way. Look at CREATE CONSTRAINT TRIGGER. Regards,

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin < atsaloli.t...@gmail.com> wrote: > > Is there any way to consolidate the pages on the slave without taking > replication offline? > maybe CLUSTER? filip@dev=> create table foobar (id serial primary key, load text); CREATE TABLE filip@dev=> in

Re: [GENERAL] Indexing MS/Open Office and PDF documents

Word documents can be processed by Abiword into any msword document into html, latex, postscript, text formats with very simple commands; i guess it also exposes some api which can be integrated into document parsers/indexers. Spreadsheets can be processed by utilizing *ExcelFormat *library http:/

Re: [GENERAL] Optimise PostgreSQL for fast testing

Hi all, Just a follow-up. I found the biggest bottleneck and now my specs run as fast as the SQLite ones. TL;DR - the issue was the database cleanup that did the truncation. Apparently SQLite is way too fast there. To "fix" it I open a transaction before each test and roll it back at the end.