Re: [GENERAL] sql (Stored procedure) design question

Assad Jarrahian schrieb: > what drove me to store it that way was more of a performance issue. > > So if I store a documentID and then have a seperate table names, to_field > I will have to store the username/groupname as such > to_field: > documentID username > docuementID groupname > docuementID

Re: [GENERAL] Finding orphan records

On 1/12/06 12:23 AM, "Jonel Rienton" <[EMAIL PROTECTED]> wrote: > Resending sample query, darn where clause didn't wrap > > select a.*,b.* from a > left outer join b on a.id = b.a_id > where b.id is null; I tried something along those lines a while back, and it was orders of magnitude slower. T

Re: [GENERAL] Finding orphan records

Resending sample query, darn where clause didn't wrap select a.*,b.* from a left outer join b on a.id = b.a_id where b.id is null; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wes Sent: Wednesday, January 11, 2006 11:51 PM To: Postgresql-General Subj

Re: [GENERAL] Finding orphan records

I would use an outer join and check only those null-value records in the right table with id's referencing table A Sample query: select a.*,b.* from a left outer join b on a.id = b.a_id -- assuming a_id is my referencing column to a where b.id is null; This will yield all columns in table a wh

Re: [GENERAL] Granting Privleges on All Tables in One Comand?

Adam wrote: Is it possible to 'GRANT SELECT ON ALL TABLES TO User' in 1 command ? No it is not. But you easily loop it with a one line script :) I saw the question in 2003 and wonder if it's still true. ---(end of broadcast)--- TIP 4: Have y

Re: [GENERAL] sql (Stored procedure) design question

what drove me to store it that way was more of a performance issue. So if I store a documentID and then have a seperate table names, to_field I will have to store the username/groupname as such to_field: documentID username docuementID groupname docuementID usernamename ... based on that it seem

[GENERAL] Finding orphan records

I'm trying to find/delete all records in table A that are no longer referenced by tables B or C. There are about 4 million records in table A, and several hundred million in tables B and C. Is there something more efficient than: select address_key, address from addresses where ( not exists(sele

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

On Wed, Jan 11, 2006 at 09:22:16PM -0700, Assad Jarrahian wrote: > Am I building what from source? cube.sql or postgis. > for cube.sql > I just took the cube.sql from windows and tried to run it in psql > (debian) in script mode. The .sql files are only part of what you need; you also need the mod

Re: [GENERAL] Granting Privleges on All Tables in One Comand?

Bruce Momjian writes: > Adam wrote: >> Is it possible to 'GRANT SELECT ON ALL TABLES TO User' in 1 command ? >> >> I saw the question in 2003 and wonder if it's still true. > Still true, but on the TODO list: Also, you can do it today by making a plpgsql function that iterates through all the t

Re: [GENERAL] sql (Stored procedure) design question

Assad Jarrahian <[EMAIL PROTECTED]> writes: > A column of type text contains username's and groupname's followed > by comma (eg. 'jarraa, mooreg3, keith') [it is stored this way > because it will be displayed in this format]. You should *not* allow display concerns to drive your database layout

Re: [GENERAL] Granting Privleges on All Tables in One Comand?

Adam wrote: > Is it possible to 'GRANT SELECT ON ALL TABLES TO User' in 1 command ? > > I saw the question in 2003 and wonder if it's still true. Still true, but on the TODO list: o %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command

[GENERAL] sql (Stored procedure) design question

Problem: A column of type text contains username's and groupname's followed by comma (eg. 'jarraa, mooreg3, keith') [it is stored this way because it will be displayed in this format]. What is needed is a constrain check. To make sure that every time a row is inserted into the table, this colum

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

On Wed, Jan 11, 2006 at 08:38:49PM -0700, Assad Jarrahian wrote: >Sorry for my hasty respone. The problem really seems to be that I > dont have $libdir set > How would I go about setting that? [...] > ERROR: could not access file "$libdir/cube": No such file or directory $libdir refers to the

[GENERAL] Granting Privleges on All Tables in One Comand?

Is it possible to 'GRANT SELECT ON ALL TABLES TO User' in 1 command ? I saw the question in 2003 and wonder if it's still true. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

Michael, I found the files cube.sql and earthdistance.sql in the contrib directory on my windows box. I put it on my debian box and then psql'ed into the db and did \i cube.sql and thats how I got the message. But I am still stumped as to how to get it to work. Additionally, I was trying to i

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

[Please copy the mailing list on replies.] On Wed, Jan 11, 2006 at 08:02:56PM -0700, Assad Jarrahian wrote: >I tried that, but it said type cube does not exists. I tried > looking around and could not find the file where CREATE type cube is > in. cube and earthdistance are contributed modules

Re: [GENERAL] Recommend IDE for PG Development

On Jan 11, 2006, at 4:53 AM, Gevik wrote: Could anyone Recommend a nice IDE for debugging and developing code for PG on Linux or Windows. Here are some options: http://www.postgresql.org/download/commercial John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error

On Wed, 11 Jan 2006, Ari Kahn wrote: > Could you be more specific. It seems that lots of people get this > error message on many platforms. http://groups.google.ca/groups?hl=en&q=vm_allocate%20failed&sa=N&tab=wg I checked the first several pages. You will most of them point to Mac or NeXT. Re

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)

Could you be more specific. It seems that lots of people get this error message on many platforms. Ari On Jan 11, 2006, at 7:21 PM, Qingqing Zhou wrote: On Wed, 11 Jan 2006, Ari Kahn wrote: Mac OSX 10.4 Seems this is a known problem of Mac. Try to google "vm_allocate failed". Regar

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error

On Wed, 11 Jan 2006, Ari Kahn wrote: > Mac OSX 10.4 > Seems this is a known problem of Mac. Try to google "vm_allocate failed". Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)

Mac OSX 10.4 On Jan 11, 2006, at 7:09 PM, Qingqing Zhou wrote: On Wed, 11 Jan 2006, Ari Kahn wrote: I have LOTS of memory (5GB). What is the appropriate way to add more memory slots? Or increase my VM file size? Hmm ... What OS are you using? Regards, Qingqing ---

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error

On Wed, 11 Jan 2006, Ari Kahn wrote: > > I have LOTS of memory (5GB). > What is the appropriate way to add more memory slots? > Or increase my VM file size? > Hmm ... What OS are you using? Regards, Qingqing ---(end of broadcast)--- TIP 1: if po

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)

"Ari Kahn" wrote > I'm doing a query that really should be too taxing. But when I execute it > I get the following error(s): > > psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3) > > Nested Loop (cost=89.36..6086.42 rows=273312 width=88) (actual > time=0.113..24456.5

Re: [GENERAL] Different exponent in error messages

On Wed, 2006-01-11 at 16:52, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > I think this is what Andrus is seeing: > > > test=> CREATE TABLE foo (n numeric(9,3)); > > CREATE TABLE > > test=> INSERT INTO foo VALUES (100); > > ERROR: numeric field overflow > > DETAIL: The absol

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

On Wed, Jan 11, 2006 at 04:20:05PM -0700, Assad Jarrahian wrote: > my appologies. It was a typo. The method is geo_distance(point, point) Ah, I'd forgotten about that one. geo_distance is part of the contrib/earthdistance module, as your pg_admin output hints at: > CREATE OR REPLACE FUNCTION ge

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

Michael and all, my appologies. It was a typo. The method is geo_distance(point, point) I copied this from psql mingle=# \df+ geo_distance List of functions Schema | Name | Result data type | Argument data types | Owner | Lan guage | So

Re: [GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

On Wed, Jan 11, 2006 at 02:40:04PM -0700, Assad Jarrahian wrote: > I was running postgres 8.1 on a windows xp box and I recall > installing it with postgis option. > I had a method in a db there that called geodistance. Everything worked fine. Neither PostgreSQL nor PostGIS have a geodistance fu

Re: [GENERAL] Different exponent in error messages

Michael Fuhr <[EMAIL PROTECTED]> writes: > I think this is what Andrus is seeing: > test=> CREATE TABLE foo (n numeric(9,3)); > CREATE TABLE > test=> INSERT INTO foo VALUES (100); > ERROR: numeric field overflow > DETAIL: The absolute value is greater than or equal to 10^6 for field with >

Re: [GENERAL] Different exponent in error messages

On Wed, Jan 11, 2006 at 04:09:23PM -0500, Tom Lane wrote: > "Andrus" <[EMAIL PROTECTED]> writes: > > Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) > > for > > field with precision 9, scale 3 ? > > Could we see a complete test case --- ie, what are the input values > ca

Re: [GENERAL] Create Produre for DROP row

Marcos wrote: > Hi, > > Does have possible create one procedure to delete a row that return > TRUE if the deletion was success or FALSE if a error occur? > > The procedure for deletion I already create but I don't know how > detect if the deletion was success executed. How do you define succes

[GENERAL] getting access to geodistance (what do I need to install/enable) in an existing db install

Hi, I was running postgres 8.1 on a windows xp box and I recall installing it with postgis option. I had a method in a db there that called geodistance. Everything worked fine. Now I tried to restore the db on the debian server thats running 8.1 as well. The geodistance function (or any other ge

Re: [GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)

"Ari Kahn" <[EMAIL PROTECTED]> wrote > I'm doing a query that really should be too taxing. But when I execute it > I get the following error(s): > > psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3) > > Nested Loop (cost=89.36..6086.42 rows=273312 width=88) (actual > time

Re: [GENERAL] Different exponent in error messages

"Andrus" <[EMAIL PROTECTED]> writes: > Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for > field with precision 9, scale 3 ? Could we see a complete test case --- ie, what are the input values causing these messages? regards, tom lane -

Re: [GENERAL] xml export tool?

"JJC" <[EMAIL PROTECTED]> wrote > > I'm looking for an open-source tool that will allow me to extract data as > XML. > If you want to extract data from PostgreSQL, I think most XML export tool will do if they can connect to database via ODBC/JDBC. Regards, Qingqing --

Re: [GENERAL] index and MVCC

<[EMAIL PROTECTED]> wrote >a table is created and an index is created on that table > > then one row is updated > > what happens to the index file? > ( is the index to old row deleted?) > Nothing happened to the index file. The index row is not deleted since there is no MVCC information associat

[GENERAL] Different exponent in error messages

I have NUMERIC(9,3) field. Postgres produces different (!) error messages when inserted value is too big to fit into this field ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.; DETAIL: The absolute value is great

Re: [GENERAL] [SPAM] - how can we use outer join in Postures - Found word(s) if you received this in error in the Text body

This should really have been sent to the -general mailing list, so I'm adding it. You will need to use OUTER JOIN syntax to accomplish this in PostgreSQL; see http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html#QUERIES-FROM Note that most databases (including Oracle) n

Re: [GENERAL] cursors as table sources

On Wed, Jan 11, 2006 at 01:41:31PM -0500, Will Glynn wrote: > Michael Fuhr wrote: > > >... > > > >Is there a reason you'd want to use a cursor instead of, say, a view? > > > >Are you just curious or is there a problem you're trying to solve? > >If I've misunderstood what you're asking then please

Re: [GENERAL] cursors as table sources

Will Glynn <[EMAIL PROTECTED]> writes: > Why can't I SELECT multi_column_function(t.a) FROM some_table t? You can. At least if you're running a recent release ;-) regression=# create function foo(int, out f1 int, out f2 int) as $$ regression$# begin regression$# f1 := $1 + 1; regression$# f2

Re: [GENERAL] Create Produre for DROP row

On 1/11/06, Marcos <[EMAIL PROTECTED]> wrote: > Hi, > > Does have possible create one procedure to delete a row that return TRUE > if the deletion was success or FALSE if a error occur? > > The procedure for deletion I already create but I don't know how detect > if the deletion was success execute

Re: [GENERAL] cursors as table sources

Michael Fuhr wrote: ... Is there a reason you'd want to use a cursor instead of, say, a view? Are you just curious or is there a problem you're trying to solve? If I've misunderstood what you're asking then please elaborate. I have previously thought this to be the most straightforward way

Re: [GENERAL] cursors as table sources

Michael Fuhr <[EMAIL PROTECTED]> writes: > ... However, you could > write a set-returning function that takes a refcursor argument and > iterates through the cursor, returning each row, and use that > function in the FROM clause. Whether that's a good idea or not is > something I haven't given muc

[GENERAL] Create Produre for DROP row

Hi, Does have possible create one procedure to delete a row that return TRUE if the deletion was success or FALSE if a error occur? The procedure for deletion I already create but I don't know how detect if the deletion was success executed. Languagel: plpgsql Thanks.

Re: [GENERAL] cursors as table sources

On Wed, Jan 11, 2006 at 04:11:18PM +0200, Peter Filipov wrote: > Is the idea to use cursors as table sources good? > Do you plan to implement it in the future and if you plan will it be soon? Do you mean the ability to use a cursor as one of the sources in the FROM clause? Something like the foll

Re: [GENERAL] Connection specific information - Temporary table used

Alban Hertroys <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: >> The question: Is there any feature/technique that a) can create >> temporary tables as in the SQL Standard defined > CREATE TEMPORARY TABLE, as per the SQL spec probably. PostgreSQL is > pretty standards compliant, I'd be sur

Re: [GENERAL] Recommend IDE for PG Development

PGAdmin III ""Gevik"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Could anyone Recommend a nice IDE for debugging and developing code for PG > on Linux or Windows. > > Thanx. > > > > ---(end of broadcast)--- > TIP 6: explain analyz

Re: [GENERAL] Connection specific information - Temporary table used

[EMAIL PROTECTED] wrote: The situation: While porting an existing application from Sybase ASA to PostgreSQL 7.4 I need to access connection specific login data (user data). In the Sybase solution I used a temporary table (CREATE TEMPORARY TABLE). Each connection than had the table on connect; eac

Re: [GENERAL] ./configure --with-openssl=path fails

Wes <[EMAIL PROTECTED]> writes: > On 1/6/06 2:47 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: >> Use --with-includes and --with-libraries as needed. > That doesn't get the library paths into the binary. If the libraries are > not in the default system search path, the user is screwed. Is there a wa

Re: [GENERAL] ./configure --with-openssl=path fails

On 1/6/06 2:47 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: >> ./configure --with-openssl=path >> This no longer works with 8.x. See: >> >> What is the correct way to work around this restriction? > > Use --with-includes and --

[GENERAL] cursors as table sources

Hi, Is the idea to use cursors as table sources good? Do you plan to implement it in the future and if you plan will it be soon? Regards, Peter Filipov -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--

Re: [GENERAL] Rules on 8.0.2

On Jan 11, 2006, at 21:15 , William Leite Araújo wrote: CREATE OR REPLACE RULE select_public_cliente AS ON SELECT TO public.cliente_tipo ERROR: event qualifications are not implemented for rules on SELECT As the error message indicates, RULEs are not applicable to ON SELECT. Use a vie

[GENERAL] Rules on 8.0.2

Hi, I'm trying create the rule : CREATE OR REPLACE RULE select_public_cliente AS ON SELECT TO public.cliente_tipo   WHERE NOT EXISTS ( SELECT usesysid FROM pg_user left join pg_group on ( idx( grolist, usesysid ) > 0 )                          WHERE usename = current_user

Re: [GENERAL] Sequence Manipulation Functions

On Jan 10 03:56, MG wrote: > I noticed that the phpPgAdmin has that information > > Name Last value Increment by Max value Min value Cache value Log count > Is cycled? Is called? > adr_dsnr 108 1 9223372036854775807 1 1 25 No Yes test=# \d roomsold_main_id_seq Sequence "public.room

[GENERAL] Recommend IDE for PG Development

Could anyone Recommend a nice IDE for debugging and developing code for PG on Linux or Windows. Thanx. ---(end of broadcast)--- TIP 6: explain analyze is your friend