Re: [GENERAL] hundreds of schema vs hundreds of databases

2007-05-29 Thread Albe Laurenz
Ron Johnson wrote: > Does PG set up buffers at the postmaster level or the database level? > > If at the database level, then you'll be allocating memory to > databases that might not be in use at any one time, thus wasting it. > One database buffer pool would make more efficient use of RAM.

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread PFC
On Wed, 30 May 2007 05:24:57 +0200, Erwin Brandstetter <[EMAIL PROTECTED]> wrote: On May 30, 2:11 am, Rodrigo De León <[EMAIL PROTECTED]> wrote: (... useful code example snipped) Now see: http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures Thanks for your hints, Rodri

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Erwin Brandstetter
On May 30, 6:48 am, Rodrigo De León <[EMAIL PROTECTED]> wrote: > On May 29, 11:35 pm, Erwin Brandstetter <[EMAIL PROTECTED]> wrote: > > > EXECUTE > > 'SELECT ' > > || (SELECT array_to_string(ARRAY( > > SELECT a.attname > > FROM pg_class c, pg_namespace nc, pg_attribute a > > WHERE c.relname = 'v_

Re: [GENERAL] function retuning refcursor, order by ignored?

2007-05-29 Thread novnov
Hmm, well if both of you say that ORDER BY is not somehow ignored by refcursor functions then I'm sure you're right. I'm just very clusmy in my testing of the output...somehow the output order by is being lost. I've been testing via the results in a web app and have had issues with executing the p

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Rodrigo De León
On May 29, 11:35 pm, Erwin Brandstetter <[EMAIL PROTECTED]> wrote: > EXECUTE > 'SELECT ' > || (SELECT array_to_string(ARRAY( > SELECT a.attname > FROM pg_class c, pg_namespace nc, pg_attribute a > WHERE c.relname = 'v_event' >AND c.relnamespace = nc.oid >AND nc.nspname = 'stdat' >AND

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Erwin Brandstetter
To conclude (to the best of my current knowledge), here is a plpgsql code sample based on what was said here: EXECUTE 'SELECT ' || (SELECT array_to_string( ARRAY( SELECT column_name::text FROM information_schema.columns WHERE table_schema = 'my_schema' AND table_name = 'my_relation'

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Erwin Brandstetter
On May 30, 2:11 am, Rodrigo De León <[EMAIL PROTECTED]> wrote: (... useful code example snipped) > Now see: > > http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures Thanks for your hints, Rodrigo! I am aware I can consult pg_catalog / information_schema to retrieve the informa

Re: [GENERAL] TimeZone List

2007-05-29 Thread Stuart Cooper
No, you can't. The reason there is no "nonvolatile" list of timezones is that timezones are subject to the whims of politicians, who can and do change them at the drop of a hat. Read the historical comments in the zic source files sometime... It used to be that "fully one third of timezones in

Re: [GENERAL] prepare()

2007-05-29 Thread Tom Lane
"Stuart Cooper" <[EMAIL PROTECTED]> writes: > My system is stabilised and I'm looking at this now. I'm not getting a > lot from pg_prepared_statements yet: perhaps this view only reports on > statements you've prepared using PostgreSQL's PREPARE through their > SQL interface, and not DBI's DBD::Pg

Re: [GENERAL] [ADMIN] Problem with timestamp

2007-05-29 Thread Tom Lane
Mageshwaran <[EMAIL PROTECTED]> writes: > We have our application which uses postgres 7.1.3 version in > that we use a time functions like now(),timestamp. etc mostly > timestamp without timezone.We don't need the timezone in that variable > so we ignore it using timestamp without

Re: [GENERAL] TimeZone List

2007-05-29 Thread Naz Gassiep
Tom Lane wrote: > Naz Gassiep <[EMAIL PROTECTED]> writes: > >> Ok, that's kinda cool. But can I trust those names to not change from >> version to version? >> > > No, you can't. The reason there is no "nonvolatile" list of timezones > is that timezones are subject to the whims of politicia

Re: [GENERAL] function retuning refcursor, order by ignored?

2007-05-29 Thread Tom Lane
novnov <[EMAIL PROTECTED]> writes: > It seems that at least in the way I've written the function below, ORDER BY > is ignored. Please provide a test case backing up that statement? regards, tom lane ---(end of broadcast)---

[GENERAL] Problem with timestamp

2007-05-29 Thread Mageshwaran
Hi, We have our application which uses postgres 7.1.3 version in that we use a time functions like now(),timestamp. etc mostly timestamp without timezone.We don't need the timezone in that variable so we ignore it using timestamp without timezone. The same is not working with high

Re: [GENERAL] TimeZone List

2007-05-29 Thread Tom Lane
Naz Gassiep <[EMAIL PROTECTED]> writes: > Ok, that's kinda cool. But can I trust those names to not change from > version to version? No, you can't. The reason there is no "nonvolatile" list of timezones is that timezones are subject to the whims of politicians, who can and do change them at the

Re: [GENERAL] Delete with subquery deleting all records

2007-05-29 Thread Francisco Reyes
Lew writes: Strange? Why? Did you expect a particular statistical distribution? Perhaps The impression was that one query was returning everything.. and the other only the records that did not exist in the one table. you were surprised by the extent of the situation, not thinking there

Re: [GENERAL] function retuning refcursor, order by ignored?

2007-05-29 Thread Michael Fuhr
On Tue, May 29, 2007 at 05:18:42PM -0700, novnov wrote: > It seems that at least in the way I've written the function below, ORDER BY > is ignored. Works here (see below). Can you post a complete example that shows ORDER BY being ignored? Do you get different results from the cursor than if you

Re: [GENERAL] TimeZone List

2007-05-29 Thread Naz Gassiep
Alvaro Herrera wrote: > alvherre=# select * from pg_timezone_names ; >name | abbrev | utc_offset | is_dst > --+++ > Africa/Algiers | CET| 01:00:00 | f > Africa/Luanda

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Alvaro Herrera
Rodrigo De León escribió: > Now see: > > http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Just a quick reminder that it's usually painful to build "generic" functions in plpgsql because it's not prepared to deal with column or table names built

Re: [GENERAL] prepare()

2007-05-29 Thread Stuart Cooper
I know that a few versions back PostgreSQL's DBD driver didn't support prepared statements (the operation was still there, it just wasn't doing the whole magic), I imagine that it does today but will investigate further. There's a very extensive writeup in the documentation of DBD::Pg, perldoc D

Re: [GENERAL] prepare()

2007-05-29 Thread Stuart Cooper
The pg_prepared_statments view will give you information on prepared statments currently resident in the database backend. I suggest you run your progam (with a couple of "Enter to continue" breakpoints in the code) and, in another session, select from pg_prepared_statements and see if the prepare

Re: [GENERAL] prepare()

2007-05-29 Thread Stuart Cooper
Is there an advantage to using something like $dbh->prepare($sql) if the SQL is going to be run once within the scope of the code? The code block may be run many times in a minute as in a function call --- while (<>) { insert_something($_); } Will the prepare statement be cached @ the d

[GENERAL] function retuning refcursor, order by ignored?

2007-05-29 Thread novnov
It seems that at least in the way I've written the function below, ORDER BY is ignored. I've seen hints that one can declare the refcursor as a specific query and that apparently the order by clause there is respected. But I don't find much by way of examples in the docs or on this list. I will ev

Re: [GENERAL] SELECT from mytbl;

2007-05-29 Thread Rodrigo De León
On May 29, 5:42 pm, [EMAIL PROTECTED] wrote: > Hi! > > Title says it pretty much. I am wondering if there is a short way to > form a query that retrieves all fields of a table (of which I do not > know names and number beforehand) except for one (or more, of which I > know the name(s)). I have stum

Re: [GENERAL] TimeZone List

2007-05-29 Thread Alvaro Herrera
Naz Gassiep wrote: > I've been trying to sort out the answer to this question for a while > now, I've received different answers from different places. > > I'm looking for a definitive non-volatile list of timezones for use in a > web application. I can't use the OS's time zone list, as changing O

Re: [GENERAL] 8.3

2007-05-29 Thread Hale Boyes, Kevin
I'm not one of the developers either but this was posted to the hackers list just today: We currently have processed 15 of the 40 patches in the patch queue during our two months in feature freeze. Based on that progress, I estimate we will enter beta in September. Kevin. -Original Messag

[GENERAL] TimeZone List

2007-05-29 Thread Naz Gassiep
I've been trying to sort out the answer to this question for a while now, I've received different answers from different places. I'm looking for a definitive non-volatile list of timezones for use in a web application. I can't use the OS's time zone list, as changing OSes may cause some listed tim

[GENERAL] prepare()

2007-05-29 Thread Tom Allison
Is there an advantage to using something like $dbh->prepare($sql) if the SQL is going to be run once within the scope of the code? The code block may be run many times in a minute as in a function call --- while (<>) { insert_something($_); } Will the prepare statement be cached @ the da

Re: [GENERAL] monitor stats

2007-05-29 Thread Shoaib Mir
Make sure there are some queries been executed on the server from pg_stat_activity. Make sure your logging level for log files is such that it can log the queries in db server log file. In addition to log_statement you can also set log_min_duration_statement to 0 and that will also log queries in

Re: [GENERAL] 8.3

2007-05-29 Thread Raymond O'Donnell
On 30/05/2007 00:11, Guy Rouillier wrote: I'm not one of the developers, but I found the following information here http://www.postgresql.org/developer/roadmap: That's great - thanks. Ray. --- Raymond O'Donnell, Director of Music, G

Re: [GENERAL] 8.3

2007-05-29 Thread Guy Rouillier
Raymond O'Donnell wrote: I realise that (i) this is something of a FAQ, and (ii) the definitive answer is "When it's ready", but when (roughly) is 8.3 planned to be released? I'm not one of the developers, but I found the following information here http://www.postgresql.org/developer/roadmap:

[GENERAL] 8.3

2007-05-29 Thread Raymond O'Donnell
I realise that (i) this is something of a FAQ, and (ii) the definitive answer is "When it's ready", but when (roughly) is 8.3 planned to be released? I'm planning a server upgrade in the next 4-5 months, and a ballpark guess would be helpful. Thanks, Ray. --

Re: [GENERAL] Geographic data sources, queries and questions

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 15:28 , John D. Burger wrote: Even ISO country codes are not guaranteed to be stable I'm not sure where the idea that primary keys must be stable comes from. There's nothing necessarily wrong with updating a primary key. All a primary key does is uniquely identify a ro

[GENERAL] SELECT from mytbl;

2007-05-29 Thread brsaweda
Hi! Title says it pretty much. I am wondering if there is a short way to form a query that retrieves all fields of a table (of which I do not know names and number beforehand) except for one (or more, of which I know the name(s)). I have stumbled across the need for this a couple of time during th

Re: [GENERAL] Geographic data sources, queries and questions

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 14:50 , Oliver Elphick wrote: On Tue, 2007-05-29 at 13:49 -0500, Michael Glaesemann wrote: If you're handling more than one country, you'll most likely want to associate the states with their respective countries. -- Listing 4 CREATE TABLE countries ( country_id INTE

Re: [GENERAL] monitor stats

2007-05-29 Thread Diego Fernando Arce
show log_statement; log_statement --- all (1 fila) select pg_stat_get_backend_pid(s.backendid),pg_stat_get_backend_activity( s.backendid) from (select pg_stat_get_backend_idset() as backendid)s; pg_stat_get_backend_pid | pg_stat_get_backend_activity -+--

Re: [GENERAL] backup strategies

2007-05-29 Thread Glen Parker
Richard P. Welty wrote: but what are the consequences of backing up a WAL file if the archive process (probably scp in this case) is running when the backup copy is made? the whole thing won't make it onto tape, are there any downsides to running a recover with an incomplete WAL file? The WAL f

Re: [GENERAL] monitor stats

2007-05-29 Thread Shoaib Mir
Did you do a reload or restart of the server after doing this change? what do you see when you do this query? --> show log_statement -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 5/29/07, Diego Fernando Arce <[EMAIL PROTECTED]> wrote: this is a part of postgrsql.conf log_statement =

Re: [GENERAL] monitor stats

2007-05-29 Thread Diego Fernando Arce
this is a part of postgrsql.conf log_statement = 'all' # none, mod, ddl, all #log_hostname = off #--- # RUNTIME STATISTICS #--- # -

[GENERAL] monitor stats

2007-05-29 Thread Diego Fernando Arce
hello, I have a question, does not work monitor stats in postgres 8.1.8 I cannot see querys in execution help me please.. DiegoF

Re: [Re] Re: [GENERAL] Winsock error 10035 while trying to upgrade from 8.0 to 8.2

2007-05-29 Thread Magnus Hagander
Cyril VELTER wrote: > OK, I've the results of my tests : > > With the previous code, then message "SOCKERROR" is printed 5 times > during the > whole process (100 Gb dump import with psql). There one group of three and > one > group of two, but I don't have timestamps and am not su

Re: [GENERAL] Geographic data sources, queries and questions

2007-05-29 Thread John D. Burger
Oliver Elphick wrote: You have assumed that state codes are unique integers, but for a worldwide database that is probably a bad design. The USA knows its states by two-letter codes, as does India and one should surely not invent a new set of codes for them. I would make this field a VARCHAR(3

Re: [GENERAL] optimisation for a table with frequently used query

2007-05-29 Thread PFC
again thanks - bit of a noob question I know, but it's good to learn :-) Well not really since the answer is quite subtle... You kave two columns A and B. Say you have index on A, and index on B. These queries will make direct use of the index : A=... o

Re: [GENERAL] optimisation for a table with frequently used query

2007-05-29 Thread danmcb
I just did some checks on two seperate indexes c.f. one combined one. I saw almost no difference between making select statements. Haven't tried what happens with many updates - makes sense that more indexes will slow that down though. again thanks - bit of a noob question I know, but it's good t

Re: [GENERAL] optimisation for a table with frequently used query

2007-05-29 Thread Dann Corbit
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Lew > Sent: Tuesday, May 29, 2007 6:38 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] optimisation for a table with frequently used query > > danmcb wrote: > >> SELECT * f

Re: [GENERAL] Geographic data sources, queries and questions

2007-05-29 Thread Oliver Elphick
On Tue, 2007-05-29 at 13:49 -0500, Michael Glaesemann wrote: > > If you're handling more than one country, you'll most likely want to > associate the states with their respective countries. > > -- Listing 4 > CREATE TABLE countries > ( > country_id INTEGER PRIMARY KEY > ); > > CREATE TABL

Re: [GENERAL] optimisation for a table with frequently used query

2007-05-29 Thread Lew
danmcb wrote: SELECT * from my_table where id_1 = x and id_2 = y; Neither id_1 or id_2 or the combination of them is unique. I expect this table to become large over time. PFC wrote: Create an index on (id_1, id_2), or (id_2,id_1). What are the strengths and weaknesses compared to creating t

Re: [GENERAL] Geographic data sources, queries and questions

2007-05-29 Thread Michael Glaesemann
On May 24, 2007, at 8:57 , btober wrote: I'm not sure it is a bad design. Country has a country_id. That's the primary key. State has a state_id, and exactly one country, so really state has a compound primary key, namely (country_id, state_id). While each state may have a single state_id and

Re: [GENERAL] psql Tab Completion in Windows

2007-05-29 Thread John DeSoi
Another alternative is the SQL editor in pgEdit. It is an interface for psql and supports tab completion (and several other completion methods). John On May 29, 2007, at 3:50 AM, Hannes Dorbath wrote: I think this is because readline is not available/broken on win32. Use the cygwin versio

Re: [GENERAL] opclass for real[]

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 11:42 , ABHANG RANE wrote: I have a column real[] in my table. I need to create a gist index on it. But the error is obvious as follows. ERROR: data type real[] has no default operator class for access method "gist" HINT: You must specify an operator class for the ind

Re: [GENERAL] opclass for real[]

2007-05-29 Thread Tom Lane
ABHANG RANE <[EMAIL PROTECTED]> writes: > I have a column real[] in my table. I need to create a gist index on > it. An index to do what --- ie, what queries do you hope the index will help with? regards, tom lane ---(end of broadcast)

Re: [GENERAL] Best way to prevent overlapping dates

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 12:41 , Jeff Davis wrote: "CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and by pg_dump to create the special triggers for referential integrity. It is not intended for general use." Is there harm in using constraint triggers outside of pg_dump? It

Re: [GENERAL] Best way to prevent overlapping dates

2007-05-29 Thread Tom Lane
Jeff Davis <[EMAIL PROTECTED]> writes: > Is there harm in using constraint triggers outside of pg_dump? It seems > like deferrable constraints would be useful for many applications, but > the docs imply that it's a bad idea. It's un-deprecated as of 8.3. regards, tom lane

Re: [GENERAL] hundreds of schema vs hundreds of databases

2007-05-29 Thread Guy Rouillier
Albe Laurenz wrote: Advantages of many databases: - Each database is smaller. - No danger of one user accessing another user's data (because of misconfigured permissions and similar). - Guaranteed independence of each user's data. - More scalable: If you decide that one machine or one cluster

Re: [GENERAL] jdeveloper and postgres

2007-05-29 Thread Bhavana.Rakesh
Hi All, I have set up a jdbc connection using jdeveloper(on my windows machine). I am connecting to a postgres database on a linux machine. I used the Database connections are shown in the Navigator, in the Connections | Database folder. I tested the connection and it said "Success!" in the d

Re: [GENERAL] Best way to prevent overlapping dates

2007-05-29 Thread Jeff Davis
On Tue, 2007-05-29 at 10:59 -0500, Michael Glaesemann wrote: > On May 25, 2007, at 3:22 , Andrus wrote: > > > CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON > > puhkus > >FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey(); > > You can also use CREATE CONSTRAINT TRIGG

Re: [GENERAL] problems with SELECT query results

2007-05-29 Thread Andrei Kovalevski
Joshua wrote: I checked the table and found that none of my fields in the SELECT statement contain NULLs. Any other suggestions? Why are you using such constructions in your query: ',' || ',' || ',' ? May be this set of commas makes you think that some of your fields are empty? Do you have e

Re: [GENERAL] query log corrupted-looking entries

2007-05-29 Thread Ed L.
On Wednesday 23 May 2007 1:04 pm, George Pavlov wrote: > Hoping to resurrect this thread. I am seeing more and more of > this as the database gets more usage and it really messes up > query log analysis. > > > A quick summary: When I posted this was getting corrupted > query log entries. I still am

Re: [GENERAL] problems with SELECT query results

2007-05-29 Thread Richard Huxton
Joshua wrote: I checked the table and found that none of my fields in the SELECT statement contain NULLs. Any other suggestions? Please post the queries you used. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions b

Re: [GENERAL] problems with SELECT query results

2007-05-29 Thread Joshua
I checked the table and found that none of my fields in the SELECT statement contain NULLs. Any other suggestions? PFC wrote: SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' || round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder) || ',' || ',' || ',' || bino

[GENERAL] opclass for real[]

2007-05-29 Thread ABHANG RANE
Hi, I have a column real[] in my table. I need to create a gist index on it. But the error is obvious as follows. ERROR: data type real[] has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the

Re: [GENERAL] problems with SELECT query results

2007-05-29 Thread PFC
SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' || round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder) || ',' || ',' || ',' || binone || ',' || ',' || round(backorderqty) || ',' || ',' || round(onhold) || ',' || ',' || ',' || ',' || ',' || ',' || ',' || r

Re: [GENERAL] Postgres / Jaro Winkler fuzzy string matching

2007-05-29 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Anyone have an implementation of the Jaro Winkler fuzzy string > matching algo for Postgres? I've not seen one, but it should be a Simple Matter of Programming to whip something up in Perl or C. - -- Greg Sabino Mullane [EMAIL PROTECTED] P

Re: [GENERAL] Languages and Functions

2007-05-29 Thread PFC
2. Is there any performance or other advantage to using PL/pgsql over Pl/Perl or Python? Yes, if you want to loop over large amounts of data (FOR row IN SELECT) plpgsql will be faster since it does not have to convert the data from postgres to python/perl format. ---

[GENERAL] problems with SELECT query results

2007-05-29 Thread Joshua
Hello, I am new to this list and have been working with PostgreSQL since January. Here is my problem, I hope someone here has some experience or can point me in the right direction. I am writing the following query for a C# program I am writing: SELECT 'PV.LINEITEM:' || partnum || ',' || rou

Re: [GENERAL] feature suggestions

2007-05-29 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Which list is the most appropriate for proposing features > and ideas for postgres ? Generally, -general. If the proposal is very technically specific, you might prefer -hackers instead. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key:

Re: [GENERAL] Best way to prevent overlapping dates

2007-05-29 Thread Michael Glaesemann
On May 25, 2007, at 3:22 , Andrus wrote: CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON puhkus FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey(); You can also use CREATE CONSTRAINT TRIGGER, which allows you to have deferrable constraints. This is useful if stat

Re: [GENERAL] Languages and Functions

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 10:30 , Michael Glaesemann wrote: You can take a look in the pg_proc table, which is part of the system catalog, to see which functions are installed. file:///usr/local/pgsql/pgsql-8.2.0/doc/html/catalog-pg-proc.html Ha! That link won't be very helpful, will it :) This

Re: [GENERAL] Will a DELETE violate an FK?

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 9:48 , Robert James wrote: I'd like to be able to detect if a record has associations. I don't want to actually delete it, just know if it could be deleted. (This is to build an intelligent menu on a GUI) On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> wrote: > Is there

Re: [GENERAL] Rookie Questions: Storing the results of calculations vs. not?

2007-05-29 Thread Rich Shepard
On Tue, 29 May 2007, Ron Johnson wrote: 1) The data contains the price of the underlying stock, the strike price of the option, and the option premium. From this I can calculate the "cost basis" and the "maximum potential profit", which are elements I would like to be able to SELECT and ORDER.

Re: [GENERAL] Languages and Functions

2007-05-29 Thread Richard Huxton
Robert James wrote: 1. How can I get a list of available functions (ie, user defined or contrib) using SQL? To see how PG does it: psql -E \df 2. Is there any performance or other advantage to using PL/pgsql over Pl/Perl or Python? 1. It's more likely to be available (not relevant if y

Re: [GENERAL] Languages and Functions

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 9:49 , Robert James wrote: 1. How can I get a list of available functions (ie, user defined or contrib) using SQL? You can take a look in the pg_proc table, which is part of the system catalog, to see which functions are installed. file:///usr/local/pgsql/pgsql-8.2.0/

Re: [GENERAL] Will a DELETE violate an FK?

2007-05-29 Thread A. Kretschmer
am Tue, dem 29.05.2007, um 10:48:21 -0400 mailte Robert James folgendes: > I'd like to be able to detect if a record has associations. I don't want to > actually delete it, just know if it could be deleted. (This is to build an > intelligent menu on a GUI) Set a savepoint, try to delete the recor

Re: [GENERAL] Rookie Questions: Storing the results of calculations vs. not?

2007-05-29 Thread Ron Johnson
On 05/24/07 15:20, cjl wrote: PG: Sorry it's taken so long for anyone to answer you, but it appears that some emails were hung up for a while. I am playing around with some historical stock option data, and have decided to use a database to make my life easier. The data is "end-of- day" fo

Re: [GENERAL] hundreds of schema vs hundreds of databases

2007-05-29 Thread Ron Johnson
On 05/29/07 04:02, Albe Laurenz wrote: I have an application with some hundreds users, each one having the same data definitions, and each one storing up to 2 GB of data. A user have just access to his own data. His data will have its own tablespace. Therefore, it seems to me I have a choice

Re: [GENERAL] Will a DELETE violate an FK?

2007-05-29 Thread Ron Johnson
On 05/29/07 09:48, Robert James wrote: I'd like to be able to detect if a record has associations. I don't want to actually delete it, just know if it could be deleted. (This is to build an intelligent menu on a GUI) Are you wanting to know this in a generic way or for a specific database?

[GENERAL] Languages and Functions

2007-05-29 Thread Robert James
1. How can I get a list of available functions (ie, user defined or contrib) using SQL? 2. Is there any performance or other advantage to using PL/pgsql over Pl/Perl or Python?

Re: [GENERAL] Will a DELETE violate an FK?

2007-05-29 Thread Robert James
I'd like to be able to detect if a record has associations. I don't want to actually delete it, just know if it could be deleted. (This is to build an intelligent menu on a GUI) On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> wrote: > Is there anyway to know if a DELETE will violate an FK > withou

Re: [GENERAL] application online upgrade strategy

2007-05-29 Thread Merlin Moncure
On 5/23/07, lest mitsui <[EMAIL PROTECTED]> wrote: Does anyone have an idea on where to turn for information/research papers on online application upgrades? I am well-versed in offline upgrades, but I am now faced with customer requirements for transparent software upgrades of our application se

Re: [GENERAL] hundreds of schema vs hundreds of databases

2007-05-29 Thread Merlin Moncure
On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> wrote: > I have an application with some hundreds users, each one > having the same > data definitions, and each one storing up to 2 GB of data. > A user have just access to his own data. His data will have its own > tablespace. > > Therefore, it seems

Re: [GENERAL] [HACKERS] Users specific to a Database

2007-05-29 Thread Michael Glaesemann
On May 29, 2007, at 6:09 , Sahoo, Ranjan Rashmi wrote: I am new to this postgres and now struggling with creation of a user specific to a database, so the user can login to that specific database and can do the activities as per the given credentials and can not touch or see any informa

Re: [GENERAL] Monitoring Tool

2007-05-29 Thread Andrew Sullivan
On Thu, May 24, 2007 at 02:12:01PM -0300, Simon De Uvarow wrote: > may someone tell me where to put the source of the tool for free access? You could set up a project on pgfoundry: http://pgfoundry.org/ Better still, contact the maintainers of some of the already-started monitoring projects, and

[GENERAL] [HACKERS] exit

2007-05-29 Thread Marcos Fabrício Corso
I would like to leave the list ...  -- Acelerador POP Acelere a sua conexão discada em até 19 x. Use o Acelerador POP. É grátis, pegue já o seu. http://www.pop.com.br/acelerador

Re: [GENERAL] SQL Manager 2007 for PostgreSQL released

2007-05-29 Thread Kenneth Downs
Nikolay Samokhvalov wrote: On 22 May 2007 17:47:46 -0700, EMS Database Management Solutions (SQLManager.net) <[EMAIL PROTECTED]> wrote: We, here at EMS Database Management Solutions, are pleased to announce SQL Manager 2007 for PostgreSQL - the new major version of the powerful PostgreSQL admini

Re: [GENERAL] CUBE SYNTAX

2007-05-29 Thread Gregory Stark
"David Fetter" <[EMAIL PROTECTED]> writes: > On Sun, May 27, 2007 at 01:23:48PM -0400, ABHANG RANE wrote: >> Hi, >> I need to use the cube operator in postgresql 8.2. Whats the syntax >> for the cube or a link to the documentation which clearly explains >> how to use cube operator syntactically.

Re: [GENERAL] optimisation for a table with frequently used query

2007-05-29 Thread danmcb
Thanks! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [GENERAL] Will a DELETE violate an FK?

2007-05-29 Thread Albe Laurenz
> Is there anyway to know if a DELETE will violate an FK > without actually trying it? I don't know what you mean by 'without trying it', but does the following answer your question? CREATE TABLE a (id integer PRIMARY KEY); CREATE TABLE b (id integer PRIMARY KEY, a_id integer NOT NULL CON

Re: [GENERAL] hundreds of schema vs hundreds of databases

2007-05-29 Thread Albe Laurenz
> I have an application with some hundreds users, each one > having the same > data definitions, and each one storing up to 2 GB of data. > A user have just access to his own data. His data will have its own > tablespace. > > Therefore, it seems to me I have a choice between "one database per

Re: [GENERAL] [HACKERS] Role members

2007-05-29 Thread Jim Nasby
On May 21, 2007, at 8:25 AM, David Fetter wrote: On Mon, May 21, 2007 at 02:21:52PM +0400, Akmal Akmalhojaev wrote: For example I have a role ID1 with members ID2 and ID3. Role ID2 has also members ID4 and ID5. It means that roles ID4 and ID5 are members of ID1. The question: Is there any fun

Re: [GENERAL] feature suggestions

2007-05-29 Thread Jim Nasby
On May 21, 2007, at 1:44 PM, PFC wrote: Which list is the most appropriate for proposing features and ideas for postgres ? Well, I think hackers tend to pay more attention to -hackers. OTOH, in order to get something accepted you've got to show why it should be accepted, so if it's a bran

Re: [GENERAL] [ADMIN] increasing of the shared memory does not solve theproblem of "OUT of shared memory"

2007-05-29 Thread Jim Nasby
On May 17, 2007, at 4:14 AM, Sorin N. Ciolofan wrote: It seems that the problem was the max_fsm_relations parameter which was increased I still do not understand why in the past I received always the message: ERROR: out of shared memory Is this an appropriate message for the need for increa

Re: [GENERAL] psql Tab Completion in Windows

2007-05-29 Thread Hannes Dorbath
On 29.05.2007 09:26, Robert James wrote: Any way to get psql Tab Completion in Windows? I think this is because readline is not available/broken on win32. Use the cygwin version of psql. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP

Re: [GENERAL] application online upgrade strategy

2007-05-29 Thread Hannes Dorbath
On 23.05.2007 18:55, lest mitsui wrote: Does anyone have an idea on where to turn for information/research papers on online application upgrades? I am well-versed in offline upgrades, but I am now faced with customer requirements for transparent software upgrades of our application server/servi

[GENERAL] psql Tab Completion in Windows

2007-05-29 Thread Robert James
Any way to get psql Tab Completion in Windows?