Re: [GENERAL] Group by and limit

Reid Thompson writes: > I only want the first 500 for each x. > Any tips or tricks someone might know would be appreciated. > I’m using postgres 8.3.7. http://troels.arvin.dk/db/rdbms/#select-top-n Consider using a more recent version of PostgreSQL, equipped with window functions! Regards, --

Re: [GENERAL] Why Select Count(*) from table - took over 20 minutes?

On 2010-10-26, John R Pierce wrote: > > count(*) has to read the whole table to get the accurate count. The > reason for this is that different clients can see different versions of > that table, for instance, if client A is already in a transaction, and > client B then does an INSERT, the tw

[GENERAL] Compiling PostgreSql 9.0 on Sparc Solaris (64 bit)

Hallo A few weeks ago, we saw questions on the mailing list about compiling PostgreSql on Solaris Sparc. Compiling on Solaris is always an adventure. It took us nearly a 2 weeks for compiling and making a package for PostgreSql and Postgis. We heavily use spare zones. That's one of the reasons

[GENERAL] Running postgres with a different user/group

Hi, I installed latest postgres from sources inside a custom environment of mine. This environment runs various services, all under a common user/group. I need to run postgres under the same user/group, witout having to create and use postgres user. I could do it, and postgres starts. But now I ca

Re: [GENERAL] Running postgres with a different user/group

On 03/11/2010 10:29, Gabriele Bulfon wrote: Hi, I installed latest postgres from sources inside a custom environment of mine. This environment runs various services, all under a common user/group. I need to run postgres under the same user/group, witout having to create and use postgres user. I c

Re: [GENERAL] Running postgres with a different user/group

Hi Gabriele, On Wed, 3 Nov 2010 11:29:25 +0100 (CET), Gabriele Bulfon wrote: > I need to run postgres under the same user/group, witout having to create > and use postgres user. That's reasonable and perfectly doable with Postgres. > But now I can't seem to connect to the database, because it s

Re: [GENERAL] Running postgres with a different user/group

Hi Gabriele, I forgot to mention one thing. > That means that PostgreSQL is successfully running. Try and connect to the > database using a different user by passing the '-U' option to psql or by > setting the PGUSER environment variable. PostgreSQL creates a role/user inside the cluster with

Re: [GENERAL] Running postgres with a different user/group

Thanx guys, all of your solutions are fine, but I could manage it in a different manner. Modified the ident map file to map my special system user to postgres, so I don't have to specify the postgres user any more ;) Thanx! Gabriele. -= Mail sent through WebTop2 =- ---

Re: [GENERAL] Running postgres with a different user/group

On 03/11/2010 11:43, Gabriele Bulfon wrote: Thanx guys, all of your solutions are fine, but I could manage it in a different manner. Modified the ident map file to map my special system user to postgres, so I don't have to specify the postgres user any more ;) Good stuff! Glad you got it to wor

[GENERAL] Altering table with open cursors

Hi Group, I'm working on a database application which makes heavy use of cursors. pg_stat_activity shows that connected clients are always ' in transaction', with a cursor open for a select. The issue that I'm having is that anytime I make changes to the table structures (ALTER TABLE tablename), t

Re: [GENERAL] timestamps in Australia

Jasen Betts writes: > set timezone to 'Australia/Sydney'; > set timezone_abbreviations to 'Australia'; > set datestyle to 'SQL,DMY'; > select '2011-04-03 > 01:00'::timestamptz+generate_series(0,3)*'1h'::interval,generate_series(0,3); > notice how the middle two look the same. > (this is Austra

[GENERAL] PHP Web Auditing and Authorization

Dear all, Imagine I have two users "Maria" and "Ana" using a PHP site. There is a common Postgres user "phpuser" for both. I'm creating audit tables to track the actions made by each PHP site user. *I have used the following code:* CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $u

Re: [GENERAL] PHP Web Auditing and Authorization

In response to Gabriel Dinis : > Dear all, > > Imagine I have two users "Maria" and "Ana" using a PHP site. > There is a common Postgres user "phpuser" for both. > I'm creating audit tables to track the actions made by each PHP site user. > > *I have used the following code:* > > CREATE OR REPL

Re: [GENERAL] PHP Web Auditing and Authorization

Gabriel, what you are looking for is also called "session variables". There are essentially 2 kind of receipes in the wild: a) store those session information in temporary tables b) store those session information in shared memory version a) has the advantage that it can be done via plpgsql, and

Re: [GENERAL] PHP Web Auditing and Authorization

Bill, > > > We got this same kind of thing working by using PostgreSQL env variables. > First, set custom_variable_classes in your postgresql.conf. You can then > use the SET command to set variables of that class, and use them in your > functions: > > that is an interesting hack. Just googled up

Re: [GENERAL] PHP Web Auditing and Authorization

In response to "Massa, Harald Armin" : > Bill, > > > > > > > We got this same kind of thing working by using PostgreSQL env variables. > > First, set custom_variable_classes in your postgresql.conf. You can then > > use the SET command to set variables of that class, and use them in your > > fun

[GENERAL] How to configure pgsql in such a way that it is always recoverable?

Hi, I migrated a MySQL database to pgsql 9.0.1. I have never used pgsql before and therefore want to learn all the possible ways to keep the database up and running. Following are my queries: If pgsql service fails to start or due to any reason pgsql database is not connecting, how to restart

Re: [GENERAL] Return key from query

On 11/03/2010 02:08 AM, Szymon Guz wrote: > > > On 3 November 2010 00:41, Rob Sargent > wrote: > > > > On 11/02/2010 03:03 PM, Szymon Guz wrote: > > > > > > On 2 November 2010 21:59, Rob Sargent > >

Re: [GENERAL] Return key from query

Sorry, I don't get it. I usually have an application that knows if it wants to write some data to database, or not. So it writes the data, and just gets from database the id that was set by database. No need of getting the id earlier in a transaction, although the simple insert that saves the d

Re: [GENERAL] How to configure pgsql in such a way that it is always recoverable?

RP Khare wrote: > Hi, > > I migrated a MySQL database to pgsql 9.0.1. I have never used pgsql before and > therefore want to learn all the possible ways to keep the database up and Welcome. > running. Following are my queries: > > > 1. If pgsql service fails to start or due to any reason p

Re: [GENERAL] Return key from query

On 3 November 2010 17:46, Jonathan Tripathy wrote: > > >>> Sorry, I don't get it. I usually have an application that knows if it >>> wants to write some data to database, or not. So it writes the data, and >>> just gets from database the id that was set by database. No need of >>> getting the id

Re: [GENERAL] PHP Web Auditing and Authorization

Thanks to all. You are great! On Wed, Nov 3, 2010 at 3:16 PM, Bill Moran wrote: > In response to "Massa, Harald Armin" : > > > Bill, > > > > > > > > > > > We got this same kind of thing working by using PostgreSQL env > variables. > > > First, set custom_variable_classes in your postgresql.con

[GENERAL] Autovacuum not started because of misconfiguration

A customer found this in one PostgreSQL log file: EDTWARNING: autovacuum not started because of misconfiguration This has only appeared once. The database has been restarted since then (about eight days ago), and this message has not reappeared. For now, we're not going to worry about it, bu

Re: [GENERAL] Autovacuum not started because of misconfiguration

"Rob Richardson" writes: > A customer found this in one PostgreSQL log file: > EDTWARNING: autovacuum not started because of misconfiguration > This has only appeared once. The database has been restarted since then > (about eight days ago), and this message has not reappeared. For now, > we'

Re: [GENERAL] Autovacuum not started because of misconfiguration

On Wed, Nov 3, 2010 at 1:28 PM, Rob Richardson wrote: > A customer found this in one PostgreSQL log file: > > EDTWARNING:  autovacuum not started because of misconfiguration > This has only appeared once.  The database has been restarted since then > (about eight days ago), and this message has no

Re: [GENERAL] Autovacuum not started because of misconfiguration

My thanks, Tom and Scott. I'll keep those in mind if the problem shows up again. RobR -- 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] Return key from query

> -Original Message- > From: Jonathan Tripathy [mailto:jon...@abpni.co.uk] > Sent: Wednesday, November 03, 2010 12:46 PM > To: Rob Sargent; pgsql-general@postgresql.org > Subject: Re: Return key from query > > > >> > >> Sorry, I don't get it. I usually have an application that > know

Re: [GENERAL] Group by and lmit

2010/11/2 Bill Reynolds : >    I’m using postgres 8.3.7. that's a pity because in 8.4 we have window functions which make this possible in one query: select * from ( select x, y, count(*) as counter, row_number() over(partition by x order by count(*)) rn from mytable group by x, y order b

Re: [GENERAL] Problem with Crosstab (Concatenate Problem)

On 11/1/10 11:54 PM, Stefan Schwarzer wrote: >> SELECT * FROM >>crosstab( >>'SELECT name, year_start, value FROM foo ORDER BY 1', >>'SELECT DISTINCT year_start FROM foo' >>) >> AS ct(name varchar, y_2010 float8, y_2011 float8); > Hi Joe. Thanks a lot for the suggestions. Tried it o

Re: [GENERAL] Problem with Crosstab (Concatenate Problem)

> What version of PostgreSQL are you running? The error seems to indicate > that you don't have the crosstab(text,text) form of the function. > > In psql do: > contrib_regression=# \df crosstab > List of functions > Schema | Name | Result data type | Argument data type