[GENERAL] A better error message for reject option in pg_hba.conf

(This is a very minor thing, but still...) Suppose we have this line in pg_hba.conf: local all all reject Now: $ psql template1 -U postgres psql: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off Actually there *is* an entry for local, user postgr

[GENERAL] Top N within groups?

[was {SOLVED?] Re: [GENERAL] functional index not used, looping simpler query just faster] Ivan Sergio Borgonovo wrote: I'm still curious to know if this could be done efficiently with just one query. [thinking out loud] Can someone familiar with the source for DISTINCT ON comment on how har

Re: [GENERAL] storing latitude and longitude

Top posting because of a challenged reader ... try for packages and documentation. Greg Williamson Senior DBA DigitalGlobe Inc -Original Message- From: [EMAIL PROTECTED] on behalf of Brent Wood Sent: Thu 7/10/2008 3:16 PM To: [EMAIL PROTECTED]; pgsql-g

Re: [GENERAL] storing latitude and longitude

Hi Mark, Look at Postgis, to do this properly. It adds full OGC spec support for managing spatial/querying spatial data within a Postgres database. It is an option included with the Windows Postgres installer, but is generally extra packages under Linux. Cheers, Brent Wood >>> mark <[EM

Re: [GENERAL] apache permission denied

ALTER ROLE apache LOGIN It now works! Thank you Devrim and Peter for your help. Devrim GÜNDÜZ wrote: On Thu, 2008-07-10 at 11:49 -0700, Chris Cosner wrote: DBI connect('dbname=db','',...) failed: FATAL: role "apache" is not permitted to log in at /home/www/cgi-bin/db.lib line 1635 What abo

Re: [GENERAL] Regex problem

On Thu, Jul 10, 2008 at 1:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: >> ...Which is not surprising. It's greedy. So, I turn off the greediness >> of the first + with a ? and then I get this > >> select substring (notes from E'LONG DB QUERY.+?time: [0-

Re: [GENERAL] Regex problem

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > ...Which is not surprising. It's greedy. So, I turn off the greediness > of the first + with a ? and then I get this > select substring (notes from E'LONG DB QUERY.+?time: [0-9]+.[0-9]+') > from table where id=1; > LONG DB QUERY (db1, 4.937628984451

Re: [GENERAL] apache permission denied

On Thu, 2008-07-10 at 11:49 -0700, Chris Cosner wrote: > DBI connect('dbname=db','',...) failed: FATAL: role "apache" is not > permitted to log in at /home/www/cgi-bin/db.lib line 1635 What about: ALTER ROLE apache LOGIN; -HTH. -- Devrim GÜNDÜZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim

[GENERAL] Regex problem

I'm usually ok at Regex stuff, but this one is driving me a bit crazy. Here's a string in a single field. I'm trying to grab the long db query bit. --- initial time: 0.0001058578491210 After _request set time: 0.0001859664916992 Bef

Re: [GENERAL] apache permission denied

Peter Eisentraut wrote: Am Donnerstag, 10. Juli 2008 schrieb Chris Cosner: Using RHEL 5, with Postgresql 8.1, Apache, mod_perl, mod_auth_pgsql, DBI, DBD::Pg Perl cgi scripts that access the database get the following in httpd error_log: DBI connect('dbname=db','',...) failed: could not connect

Re: [GENERAL] pgmemcache status

On Wed, 2008-07-09 at 20:42 -0300, [EMAIL PROTECTED] wrote: > > Is pgmemcache still being actively supported/developed? I have > > experienced a database crash with postgres 8.3.3 and pgmemcache > 1.2beta1 > > Yes and no. I just joined up, and hope to be submitting some patches > to it soon, as w

Re: [GENERAL] apache permission denied

Am Donnerstag, 10. Juli 2008 schrieb Chris Cosner: > Using RHEL 5, with Postgresql 8.1, Apache, mod_perl, mod_auth_pgsql, > DBI, DBD::Pg > > Perl cgi scripts that access the database get the following in httpd > error_log: > DBI connect('dbname=db','',...) failed: could not connect to server: > Per

Re: [GENERAL] storing latitude and longitude

Sorry I'm not of much help but... did you check the data types page? http://postgresql.mirrors-r-us.net/docs/8.2/interactive/datatype-geometric.html http://postgresql.mirrors-r-us.net/docs/8.2/interactive/datatype-numeric.html That mainly depends on how many digits you need to store, if that's no

[GENERAL] apache permission denied

Using RHEL 5, with Postgresql 8.1, Apache, mod_perl, mod_auth_pgsql, DBI, DBD::Pg Perl cgi scripts that access the database get the following in httpd error_log: DBI connect('dbname=db','',...) failed: could not connect to server: Permission denied A direct test with a simple SQL command in

[GENERAL] Moving legacy application to JAVA, programming learning curve

I have a legacy DOS application that I am rewriting with Postgresql as the back-end. The system was written in Foxpro2.6 for DOS and uses xBase tables. The new database is set up and running on an Ubuntu server. The programs have been completed that migrate the data to the Postgresql database. Now

{SOLVED?] Re: [GENERAL] functional index not used, looping simpler query just faster

On Thu, 10 Jul 2008 10:46:53 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > This sub-select is non optimizable because you've got an outer > reference in it, which compels re-evaluating it at every row of > the outer query. Try recasting as > explain select i1.brands, i1.name, i1.dataPub, i1.datains

Re: [GENERAL] How to obtain info about the user?

> > SELECT pg_has_role() , has_table_privilege(), has_database_privilege(), > has_function_privilege > Tanks, I'll make use of that Only one last thing: How do I get the users list? > > On Thu, Jul 10, 2008 at 1:15 PM, Joshua D. Drake wrote: > > > On Thu, 2008-07-10 at 11:08 -0500, Ismael

[GENERAL] storing latitude and longitude

hi.. i want to store latitude and longitude in a users table.. what is the best data type to use for this? i want to be able to find use this info to find users within a distance.. how do i go about doing this? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

Re: [GENERAL] Inaccurate row count estimation

"Vyacheslav Kalinin" <[EMAIL PROTECTED]> writes: > Here is the reproduce code: After tracing through this I see that the problem is that we don't have statistics for inheritance trees, and so you're getting a default estimate for the selectivity of the join condition. I'm not sure why the similar

Re: [GENERAL] How to obtain info about the user?

*SELECT* pg_has_role() , has_table_privilege(), has_database_privilege(), has_function_privilege On Thu, Jul 10, 2008 at 1:15 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > > On Thu, 2008-07-10 at 11:08 -0500, Ismael Almaraz Ezparza wrote: > > Hi there, > > I'm connecting to postgres from ja

Re: [GENERAL] How to obtain info about the user?

On Thu, 2008-07-10 at 11:08 -0500, Ismael Almaraz Ezparza wrote: > Hi there, > I'm connecting to postgres from java using the postgres driver... > and I need to get the info about the user that is connected to the DB, lets > say > for example: name, groups, permisions (select, update, execute)..

[GENERAL] How to obtain info about the user?

Hi there, I'm connecting to postgres from java using the postgres driver... and I need to get the info about the user that is connected to the DB, lets say for example: name, groups, permisions (select, update, execute)... Does anyone knows how can I get that info? Also, if the user is in the gr

Re: [GENERAL] functional index not used, looping simpler query just faster

On Thu, 10 Jul 2008 10:46:53 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Now I try this: > > > explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento > > from catalog_items i1 > > inner join catalog_brands b1 on upper(i1.brands)

Re: [GENERAL] SPACE FOR POSTGRESQL DATABASE

On Thu, Jul 10, 2008 at 8:18 AM, aravind chandu <[EMAIL PROTECTED]> wrote: > Hello, > > Can you please how much space does postgresql database occupies? A good rule of thumb is that it will take about 1.5 times as much space in the db as it does in a flat file. that's just an approxim

Re: [GENERAL] SPACE FOR POSTGRESQL DATABASE

am Thu, dem 10.07.2008, um 7:18:39 -0700 mailte aravind chandu folgendes: > Hello, > > Can you please how much space does postgresql database occupies? > > Thank You, Depends on the amount of data stored in the database. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz:

Re: [GENERAL] Force removing Locks

Florian Eberle <[EMAIL PROTECTED]> writes: > I've got a Problem... An Application that uses my Postgres Server always > keeps some locks open... Is there a Possibility to forcing remove the > Locks after a certain Interval or to remove them by hand? Fix your application to close its transaction oc

Re: [GENERAL] expected O^2 looks line K^O, index problem not involved: [was] looping simpler query just faster

On Thu, 10 Jul 2008 15:52:54 +0200 Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Thu, Jul 10, 2008 at 02:19:30PM +0200, Ivan Sergio Borgonovo > wrote: > > On Thu, 10 Jul 2008 11:50:01 +0200 > > Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > Hmm, I just studied your query to determin

Re: [GENERAL] SPACE FOR POSTGRESQL DATABASE

in this link have information about database size http://www.postgresql.org/docs/8.1/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE --- On Thu, 7/10/08, aravind chandu <[EMAIL PROTECTED]> wrote: > From: aravind chandu <[EMAIL PROTECTED]> > Subject: [GENERAL] SPACE FOR POSTGRESQL DATABASE >

Re: [GENERAL] functional index not used, looping simpler query just faster

Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > Now I try this: > explain select i1.brands, i1.name, i1.dataPub, i1.datainserimento > from catalog_items i1 > inner join catalog_brands b1 on upper(i1.brands)=upper(b1.name) > where i1.ItemID in ( > select i2.ItemID from catalog_items i2

[GENERAL] SPACE FOR POSTGRESQL DATABASE

Hello,     Can you please how much space does postgresql database occupies? Thank You, Aviansh

Re: [GENERAL] expected O^2 looks line K^O, index problem not involved: [was] looping simpler query just faster

On Thu, Jul 10, 2008 at 02:19:30PM +0200, Ivan Sergio Borgonovo wrote: > On Thu, 10 Jul 2008 11:50:01 +0200 > Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: Hmm, I just studied your query to determine what you're trying to do. As I understand it: For each item Determine the brand Get the t

Re: [GENERAL] Starter

On 10/07/2008 13:24, Guido Sagasti wrote: Hi I want to star with postgresql and I want to know if it´s difficult the instalation and setting up in a desktop. I have not a IT background but work a lot with databases and need a little help. Hi there, If you're working on Windows, the installer

Re: [GENERAL] Starter

On Thu, Jul 10, 2008 at 6:24 AM, Guido Sagasti <[EMAIL PROTECTED]> wrote: > Hi I want to star with postgresql and I want to know if it´s difficult the > instalation and setting up in a desktop. I have not a IT background but work > a lot with databases and need a little help. On a workstation runn

[GENERAL] Starter

Hi I want to star with postgresql and I want to know if it´s difficult the instalation and setting up in a desktop. I have not a IT background but work a lot with databases and need a little help. Thanks and waiting recomendations on how to start, Guido. ___

[GENERAL] expected O^2 looks line K^O, index problem not involved: [was] looping simpler query just faster

On Thu, 10 Jul 2008 11:50:01 +0200 Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Thu, Jul 10, 2008 at 11:40:40AM +0200, Ivan Sergio Borgonovo > wrote: > > I've this: > > What's basically killing you is this condition: > > select i2.ItemID from catalog_items i2 > > inner join catalo

Re: [GENERAL] User-Defined Variables

Don't forget to cc: the list Daniel Futerman wrote: What is the script trying to do (in a wider sense)? The variable is used as follows: SET @OTHER_CONCEPT_ID = (SELECT `concept_id` FROM `concept_name` where name = 'MRO' LIMIT 1); (SELECT COALESCE(f2.concept_id, @OTHER_CONCEPT_ID ) as '

[GENERAL] Force removing Locks

Hello, I've got a Problem... An Application that uses my Postgres Server always keeps some locks open... Is there a Possibility to forcing remove the Locks after a certain Interval or to remove them by hand? Because of this Locks autovacuum cant work properly on this table and finally System runs

Re: [GENERAL] functional index not used, looping simpler query just faster

On Thu, Jul 10, 2008 at 11:40:40AM +0200, Ivan Sergio Borgonovo wrote: > I've this: What's basically killing you is this condition: > select i2.ItemID from catalog_items i2 > inner join catalog_brands b2 on upper(i2.brands)=upper(b2.name) > where i1.brands=i2.brands <* > an

[GENERAL] functional index not used, looping simpler query just faster

I've this: CREATE TABLE catalog_brands ( brandid serial NOT NULL, "name" character varying(64) NOT NULL, delivery smallint NOT NULL DEFAULT (24 * 15), deliverymessage character varying(64), brandtypeid integer, brandgroupid integer, CONSTRAINT catalog_brands_pkey PRIMARY KEY (brandid

Re: [GENERAL] User-Defined Variables

On Thursday 10. July 2008, Daniel Futerman wrote: >Hi, > >Is there a quick solution to implementing user-defined variables in >PostgreSQL as they are used in MySQL? > >I have the following MySQL script which i want to implement in > Postgres (NOTE : all ` have been changed to " for Postgres use): >

Re: [GENERAL] User-Defined Variables

Hello PostgreSQL doesn't support this feature. There are some techniques that you can use: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables http://www.postgresql.org/docs/8.3/static/plperl-global.html Regards Pavel Stehule 2008/7/10 Daniel Futerman <[EMAIL PROTEC

[GENERAL] User-Defined Variables

Hi, Is there a quick solution to implementing user-defined variables in PostgreSQL as they are used in MySQL? I have the following MySQL script which i want to implement in Postgres (NOTE : all ` have been changed to " for Postgres use): SET @OTHER_CONCEPT_ID = (SELECT "concept_id" FROM "concept