Re: [GENERAL] Excessive (and slow) fsync() within single transaction

On Tue, Dec 8, 2009 at 7:28 PM, Stephen Tyler wrote: > I've been trying to track down a performance issue I have.  In simple terms, > my select performance is very good (generally either CPU limited, or disk > limited, depending upon the query), and small updates seem OK. > > But a huge UPDATE is

Re: [GENERAL] how to ensure a client waits for a previous transaction to finish?

On Tue, Dec 8, 2009 at 10:13 PM, Dan Kortschak wrote: > I've spoken to people on the torque user mailing list and tried merlin's > suggestion below (which looked like it should work - but unfortunately > did not prevent the problem). > > >From working through things with the torque list, it seems

Re: [GENERAL] how to ensure a client waits for a previous transaction to finish?

Dan Kortschak writes: > From working through things with the torque list, it seems to be the > case that postgresql is behaving differently because it is not attached > to a terminal (this has caused problems for others on that list with > sqlite and mysql). That seems unlikely. My best guess at

Re: [GENERAL] Installing PL/pgSQL by default

Bruce Momjian writes: > I installed PL/pgSQL by default via initdb with the attached patch. The > only problem is that pg_dump still dumps out the language creation: > CREATE PROCEDURAL LANGUAGE plpgsql; > ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres; > What is odd is that I us

Re: [GENERAL] how to ensure a client waits for a previous transaction to finish?

I've spoken to people on the torque user mailing list and tried merlin's suggestion below (which looked like it should work - but unfortunately did not prevent the problem). >From working through things with the torque list, it seems to be the case that postgresql is behaving differently because i

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

Harald Fuchs writes: > Tom Lane writes: >> Julian Mehnle writes: >>> So far, so good. However, can someone please explain the following to me? >>> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)+', >>> 'g'); >>> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', >>>

Re: [GENERAL] Installing PL/pgSQL by default

Tom Lane wrote: > Andrew Dunstan writes: > > Before we go too far with this, I'd like to know how we will handle the > > problems outlined here: > > > > Hm, I think that's only a problem if we define it to be a problem, > and I

Re: [GENERAL] Excessive (and slow) fsync() within single transaction

Stephen Tyler wrote: So firstly, why are there so many calls to fsync()? Every time a transaction commits, you get a fsync to the WAL file. Then, during the periodic database checkpoints, you get more fsync'd writes. The latter are more likely to be your problem. You should turn on log_chec

Re: [GENERAL] Linking pg_config (postgres 8.4 in SCO 5.0.7)

erobles writes: > Hi i have been compiling the Postgres 8.4 on SCO OpenServer 5.0.7 but > when the pg_config is linking i got the next error: > Undefined > symbol > get_html_path Seems like you are somehow linking against a pre-8.4 version of src/port/path.c. How you managed to do that,

Re: [GENERAL] Excessive (and slow) fsync() within single transaction

Stephen Tyler writes: > My expectation would be that postgresql would issue an fsync() (or perhaps a > few such calls) at the end of the transaction. But this does not seem to be > the case: fsyncs mostly happen as a result of checkpoint activity. I wonder whether you have done any tuning of ch

[GENERAL] Rules and conditions

Hello guys, I can't seem to understand why a simples if is not working on the creation of rules. I tried both ways (am i missing something?): Take a look: CREATE OR REPLACE RULE instead_update AS ON UPDATE TO foo DO INSTEAD ( IF exists(SELECT 1 FROM versioning.foo_version_1 WHERE oid =

Re: [GENERAL] Counts and percentages and such

> My experience with Squirrel was that it worked fine for very simple > queries, and as soon as you got outside the box it started doing the > stuff the OP is seeing.  For postgresql the preferred GUI is pgadmin > III, but psql is the best text only interface for a db on the planet. > I'm just us

[GENERAL] Excessive (and slow) fsync() within single transaction

I've been trying to track down a performance issue I have. In simple terms, my select performance is very good (generally either CPU limited, or disk limited, depending upon the query), and small updates seem OK. But a huge UPDATE is incredibly slow. CPU is on average below 1%, and disk IO is ge

Re: [GENERAL] Counts and percentages and such

On Tue, Dec 8, 2009 at 3:09 PM, Thomas Kellerer wrote: > jackassplus wrote on 08.12.2009 22:21: >> >> What does ::numeric signify? >> I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL >> and it asks me for the value of :numeric. > > As Scott has pointed out this is a typecast.

Re: [GENERAL] Counts and percentages and such

jackassplus wrote on 08.12.2009 22:21: What does ::numeric signify? I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL and it asks me for the value of :numeric. As Scott has pointed out this is a typecast. If Squirrel mistakes that for a parameter, it's clearly a bug in S

Re: [GENERAL] Counts and percentages and such

Btw, Squirrel sucks, I tried using it in my last job and it got in the way more than it helped with pgsql. I just use psql or pgadmin III if I need a gui. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpre

Re: [GENERAL] Counts and percentages and such

On Tue, Dec 8, 2009 at 2:21 PM, jackassplus wrote: > >> select coalesce(col,'Null'), >> (count(coalesce(col,'Null'))::numeric/(select count(*) from >> some_table))*100 from some_table group by col; >>  coalesce |        ?column? >> --+- >>  Null     | 13.33

Re: [GENERAL] Counts and percentages and such

> select coalesce(col,'Null'), > (count(coalesce(col,'Null'))::numeric/(select count(*) from > some_table))*100 from some_table group by col; >  coalesce |        ?column? > --+- >  Null     | 13.3300 >  N        | 20. >  A      

[GENERAL] Linking pg_config (postgres 8.4 in SCO 5.0.7)

Hi i have been compiling the Postgres 8.4 on SCO OpenServer 5.0.7 but when the pg_config is linking i got the next error: Undefined symbol get_html_path first referenced in file pg_config.o where can i find this function to make a success compiling of postgres??? by the way i search

Re: [GENERAL] Counts and percentages and such

On Tue, Dec 8, 2009 at 11:50 AM, jackassplus wrote: > I'm new to both pgsql and SQL in  general pas really simple stuff, so > i would like to know how to; > > Given a table with a column that can have one of NULL, (char) N, > (char) A, and (char) L. Is there a way to in a single query, ge the > pe

Re: [GENERAL] Implementing next 30 (or so) rows "sliding window"

On Tue, Dec 8, 2009 at 12:12 PM, Allan Kamau wrote: > Hi, > I did follow the basic advise and consulted the documentation for > "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW | > ROWS } ONLY]" clause which seems to satisfy my requirement. This is a cursor, which is one of two

Re: [GENERAL] how to allow a sysid to be a superuser?

On Tue, Dec 8, 2009 at 1:44 PM, Gauthier, Dave wrote: > It comes back... > > role "joetheplumber" does not exist > > The user is a sys uid on linux. You're confusing linux users with postgresql users. They aren't mapped one to the other. First you need to create a pgsql role / user: psql postg

Re: [GENERAL] how to allow a sysid to be a superuser?

It comes back... role "joetheplumber" does not exist The user is a sys uid on linux. -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Tuesday, December 08, 2009 1:26 PM To: Gauthier, Dave Cc: pgsql-general Subject: Re: [GENERAL] how to allow a sysid to be a

Re: [GENERAL] Implementing next 30 (or so) rows "sliding window"

On Tue, Dec 8, 2009 at 11:42 AM, Jaime Casanova wrote: > that's basically LIMIT, you have to combine that with OFFSET Keep in mind that offset begins to preform badly for large values. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql

[GENERAL] Counts and percentages and such

I'm new to both pgsql and SQL in general pas really simple stuff, so i would like to know how to; Given a table with a column that can have one of NULL, (char) N, (char) A, and (char) L. Is there a way to in a single query, ge the percentage of the whole rowset that each of those represents? lik

Re: [GENERAL] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

Le mardi 8 décembre 2009 à 19:10:30, David Fetter a écrit : > On Tue, Dec 08, 2009 at 09:02:41AM +0100, Guillaume Lelarge wrote: > > Hi David, > > > > Le mardi 8 décembre 2009 à 01:46:00, David Fetter a écrit : > > > Folks, > > > > > > If you can't make it to the SFPUG meeting in person on Tuesday,

Re: [GENERAL] Implementing next 30 (or so) rows "sliding window"

On Tue, Dec 8, 2009 at 2:12 PM, Allan Kamau wrote: > Hi, > I did follow the basic advise and consulted the documentation for > "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW | > ROWS } ONLY]" clause which seems to satisfy my requirement. > that's basically LIMIT, you have to c

Re: [GENERAL] Implementing next 30 (or so) rows "sliding window"

Hi, I did follow the basic advise and consulted the documentation for "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY]" clause which seems to satisfy my requirement. Allan. On Tue, Dec 8, 2009 at 9:49 PM, Allan Kamau wrote: > Hi, > I am looking for an efficient w

[GENERAL] Implementing next 30 (or so) rows "sliding window"

Hi, I am looking for an efficient way to implement a "sliding window" view of the data from a query. I am developing a simple website and would like to provide for viewing(fetching) only a predetermined maximum number of records per page. For example to view 100 records with "30" as the predetermin

Re: [GENERAL] Query using partitioned table hangs

--- On Mon, 12/7/09, Tom Lane Have you looked into pg_locks to see if it's blocked > waiting for a lock? > The TRUNCATE in particular would require exclusive lock on > the table, so it could be waiting for some other process > that's touched the table. Thanks Tom - while pg_locks did not reveal

Re: [GENERAL] how to allow a sysid to be a superuser?

On Tue, Dec 8, 2009 at 9:11 AM, Gauthier, Dave wrote: > PG 8.3.4 on Linux. > > A DB was created with a privileged account which has limited access.  I want > a specific user (sysuid) to have “all” provs on this DB.  In fact, I want > this user to have “all”  on all the dbs served by the PG instanc

Re: [GENERAL] Postgresql: daily manual tranfer

- "mrciken" wrote: > Hello, > > I have been looking for a data integration / transfer program able to > help > with Postgresql. > I have had some advice but have not found the right software yet. Probably because you have not supplied enough information :) Again it would be helpful know

Re: [sfpug] [GENERAL] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

> I suppose the video will be available later online? Yes. --Josh -- 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] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

On Tue, Dec 08, 2009 at 09:02:41AM +0100, Guillaume Lelarge wrote: > Hi David, > > Le mardi 8 décembre 2009 à 01:46:00, David Fetter a écrit : > > Folks, > > > > If you can't make it to the SFPUG meeting in person on Tuesday, > > December 8, 2009 at EZRez > > http://postgresql.meetup.com/1/calend

[GENERAL] Unsubscribe

Sent from my iPhone On Dec 8, 2009, at 5:33 AM, "Dave Page" wrote: > On behalf of the core team, I'm pleased to announce that the > PostgreSQL Project has expanded it's team of "committers", those > people who are able to make direct changes to the PostgreSQL source > code respository. As the

[GENERAL] Postgresql: daily manual tranfer

Hello, I have been looking for a data integration / transfer program able to help with Postgresql. I have had some advice but have not found the right software yet. The main feature is being able to migrate important quantities of contacts and data on a daily basis. The migration would be don

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

In article <13289.1260290...@sss.pgh.pa.us>, Tom Lane writes: > Julian Mehnle writes: >> So far, so good. However, can someone please explain the following to me? >> wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)+', >> 'g'); >> wisu-dev=# SELECT regexp_matches('q...@f

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

Tom, thanks for your reply. I wrote: > wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)+', > 'g'); >{p} > > wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', > '([...@.]|[...@.]+){1,2}', 'g'); >{...@} >{...@} >{.} >{p} > > wisu-dev=# SELECT

Re: [GENERAL] postgre...@fosdem 2010 - HOTEL room reservation

On Thu, 3 Dec 2009 10:48:22 + Dave Page wrote: > FOSDEM (http://www.fosdem.org/2010/) is a major Free and Open Source > event held annually in Brussels, Belgium, and attended by around 4000 > people. As in recent years, the PostgreSQL project will have a devroom > where we will be presenting a

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

Julian Mehnle writes: > So far, so good. However, can someone please explain the following to me? > wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)+', > 'g'); > wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', > '([...@.]|[...@.]+){1,2}', 'g'); > wisu-dev=# SE

Re: [GENERAL] Question on "best practise" for SELECTS on inherited tables

On Tue, Dec 8, 2009 at 10:30 AM, Hubertus Freiherr von F?uerstenberg wrote: > > please consider the following exemplary setup: > > I want to store information on people in a database. People can be either > internal (staff) or external (company contacts, etc.). The idea was to use > one table for

[GENERAL] how to allow a sysid to be a superuser?

Hi: PG 8.3.4 on Linux. A DB was created with a privileged account which has limited access. I want a specific user (sysuid) to have "all" provs on this DB. In fact, I want this user to have "all" on all the dbs served by the PG instance. Is there a way to do this such that when psql is inv

[GENERAL] regexp_matches() quantified-capturing-parentheses oddity

Hi all, wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|[...@.]+)', 'g'); {quux} {...@} {foo} {...@} {bar} {.} {zip} So far, so good. However, can someone please explain the following to me? wisu-dev=# SELECT regexp_matches('q...@foo@bar.zip', '([...@.]|

Re: [GENERAL] LDAP configuration changes in 8.4?

--- On Tue, 8/12/09, Magnus Hagander wrote: > > ldapserver="notts.net.mycompany.com" > > exclude the ldap:// part, and the base dn part. > Excellent, that did the trick. Thanks. Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscripti

Re: [GENERAL] SELECT ROW(t.*) FROM table t and nulls with textual representation?

On Tue, Dec 8, 2009 at 5:43 AM, Teemu Juntunen wrote: > Hello, > > is there any possibility to get null-values as text 'null' from > dynamic select expression like > > SELECT ROW(t.*) FROM table t > > Normally you get > > (t1,t2,t3,,t5,,,) > > How can you get > > (t1,t2,t3,null,t5,null,null,null)

Re: [GENERAL] LDAP configuration changes in 8.4?

2009/12/8 Glyn Astill : > Hi Chaps, > > I'm setting up a new server on 8.4, and I'm struggling to get LDAP > authentication working, even though I've got it working fine on 8.3. > > This is the format I'm using in 8.3: > > ldap "ldap://notts.net.mycompany.com/My Company/Call Centre > Users;CN=;,O

[GENERAL] Question on "best practise" for SELECTS on inherited tables

Hello, please consider the following exemplary setup: I want to store information on people in a database. People can be either internal (staff) or external (company contacts, etc.). The idea was to use one table for all people and have the tables that store specific information inherit from

[GENERAL] LDAP configuration changes in 8.4?

Hi Chaps, I'm setting up a new server on 8.4, and I'm struggling to get LDAP authentication working, even though I've got it working fine on 8.3. This is the format I'm using in 8.3: ldap "ldap://notts.net.mycompany.com/My Company/Call Centre Users;CN=;,OU=Call Centre Users,OU=My Company,DC=no

Re: [GENERAL] [HACKERS] New PostgreSQL Committers

On Mon, Dec 07, 2009 at 10:49:13AM +, Dave Page wrote: > On behalf of the core team, I'm pleased to announce that the > > Congratulations! > +1 Congrats to you all, and thanks for the contributions, both past and future. As an aside, this sort of thing is one of the best signs to an external

Re: [GENERAL] logs de postgres en debian

oscar arocha a écrit : > Good morning, i write you because, i need know where is log's postgres > into debian lenny, i've read > /var/log/postgresql/postgresql-8.1-main.log, but only could see > > 009-12-06 07:36:02 VET LOG: autovacuum: procesando la base de datos > «postgres» > 2009-12-06 07:37:

[GENERAL] logs de postgres en debian

Buenos dias, les escribo porque necesito saber como consultar los logs de postgres, tengo porsgres8.1 en debian lenny. he consultado /var/log/postgresql/postgresql-8.1-main.log, pero solo veo lineas como estas 009-12-06 07:36:02 VET LOG: autovacuum: procesando la base de datos «postgres» 2009-12

[GENERAL] SELECT ROW(t.*) FROM table t and nulls with textual representation?

Hello, is there any possibility to get null-values as text 'null' from dynamic select expression like SELECT ROW(t.*) FROM table t Normally you get (t1,t2,t3,,t5,,,) How can you get (t1,t2,t3,null,t5,null,null,null) I would like to use the result with dynamic update expression and update d

Re: [GENERAL] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

Hi David, Le mardi 8 décembre 2009 à 01:46:00, David Fetter a écrit : > Folks, > > If you can't make it to the SFPUG meeting in person on Tuesday, > December 8, 2009 at EZRez > http://postgresql.meetup.com/1/calendar/11928447/ > > You can see the live stream at: > http://media.postgresql.org/sfp