Re: [GENERAL] LD_LIBRARY_PATH

[Andrew W. Nosenko]

Peter Eisentraut wrote:
: Mike Castle writes:
: 
: > Speaking of administrative nightmares, did I read correctly that 7.1
: > now users -R by default when linking?  Ack!  Doesn't that make it nearly
: > impossible for an administrator to move libraries around as necessary?
: 
: If you move files to a place that is different from the one they are
  ^
: supposed to be in you're going to have more severe problems than this one.
: This is not only true for PostgreSQL.

As I understand Mike speak not about files in general but about
libraries (and possible have in mind executables too). And hardcoded
patch to libraries is nightmare, IMO.

-- 
Andrew W. Nosenko([EMAIL PROTECTED])

RE: [GENERAL] Closest SQL dialect to PostgreSQL for ERwin?

[elein]

Sorry for answering a really old message...
Since I worked on all of these databases I could
not resist.

Miro-Montage-Illustra was the product that was derived
directly from postgres.  It was bought by informix and
re-implemented in version 9.* of the informix server.

ingres was based on a much much much earlier version of
the university code.

The illustra code base is very recognizably postgres.

The dialect of SQL is unique to the ORDBMS crowd.  You
can get a pure relational schema from ERWin that will
work, but you have to manipulate it afterwards if you
use any non-SQL92 features.  Boy, wouldn't I love to
have a good ORDBMS schema tool... Too bad informix's isn't
good.

elein
ex-ingres, ex-miro/illustra, ex-informix
currently postgreSQL database architect
[EMAIL PROTECTED] 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Alex Pilosov
Sent: Thursday, November 30, 2000 10:57 PM
To: Ed Loehr
Cc: pggeneral
Subject: Re: [GENERAL] Closest SQL dialect to PostgreSQL for ERwin?


Genealogicaley, Ingres would indeed be closest to pgsql (both are direct
descendants of University Ingres).

Probably a nicer thing would be for ERwin to have SQL-92 dialect, but
since nobody other than postgres even tried to comply to it, ERWin doesn't
support it ;)

On Thu, 30 Nov 2000, Ed Loehr wrote:

> I'm using ERwin to reverse engineer an ER diagram from PostgreSQL SQL
> scripts (via pg_dump).  ERwin does not officially support PostgreSQL's
> flavor of SQL, though having it read PostgreSQL scripts as Ingres scripts
> seems to work ok.
> 
> Is there another well-known RDBMS vendor (Informix?) whose dialect and
> SQL idiosyncrasies (sp?) are closer to PostgreSQL's SQL than Ingres? 
> ERwin supports most of the big RDBMS names (Pg is the only one I noticed
> missing), and I don't know much about the various dialect differences.
> 
> Regards,
> Ed Loehr
> 
> 

[GENERAL] replacing a word..

[Anand Raman]

hi guys

I have to remove a entire word from a database column in a sql statement..

One of my database column contains strings like '###TOP_IMAGE###' ,
'###BOTTOM_IMAGE' , '###QUOTE###' and '###ACTIVITYBAR###'

This is later picked up by the rendering agent and the appropriate
HTML element is inserted in its place..

But i want to prohibit my full text indexer from seeing this text.

How can i replace all occurences of such strings with a zero length
string..

e.g 
S ###TOP_IMAGE### pain's Davis Cup hero Juan Carlos Ferrero has withdrawn

should be seen by indexer as 

Spain's Davis Cup hero Juan Carlos Ferrero has withdrawn


Is it possible.. I tried using translate() and the ~ operator to no
avail..

Could some please shed some light on this..

Thanx
Anand Raman

RE: [GENERAL] void UPDATE function?

[Francis Solomon]

Hi Adam,

Would this work?

UPDATE bugs SET somefield=somefield WHERE pkey='3';

i.e. set a field to itself. I don't know whether or not this would be
optimised away - but it could be worth a try.

Hope this helps.

Francis Solomon

>
>   What is a good way to trigger an UPDATE of a row
> without actually
> changing anything?  I have a trigger that will update the timestamp
> of a record, and in this case, want to trigger it because I am
> adding a related row in another table.
>
> UPDATE bugs WHERE pkey='3'
>
> Doesn't seem to work.

[GENERAL] void UPDATE function?

[Adam Haberlach]

What is a good way to trigger an UPDATE of a row without actually
changing anything?  I have a trigger that will update the timestamp
of a record, and in this case, want to trigger it because I am
adding a related row in another table.

UPDATE bugs WHERE pkey='3'

Doesn't seem to work.

-- 
Adam Haberlach|A cat spends her life conflicted between a
[EMAIL PROTECTED]   |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.

[GENERAL] Output function

[Uro Gruber]

Hi!

This is some function.

CREATE FUNCTION my_func(text) RETURNS text AS '
   DECLARE
 name TEXT;
 lastname TEXT;
   BEGIN
 name := ''Uros'';
 lastname := ''Gruber'';
 RETURN ime;
   END;
' LANGUAGE 'plpgsql';

Output is like this

 my_func

 Uros
(1 row)

What i want to do is output like this


   name |lastname
+--
   uros | gruber
(1 row)

How can i write this function to return both values?


-- 
Thanks, Uros

Re: [GENERAL] How I can join between the other database's tables?

[Adam Rossi]

On Thursday 28 December 2000 11:12 am, Tom Lane wrote:
> Jens Hartwig <[EMAIL PROTECTED]> writes:
> > In fact, I also would be very content if PostgreSQL would be enabled to
> > work with different databases (perhaps in some future release?).
>
> I do not think that that's very likely to happen --- at least not in the
> foreseeable future.  What is likely to happen for 7.2 is support for
> schemas as defined by the SQL92 standard.  Schemas are basically a level
> of naming in between databases and individual tables.  By assigning
> separate schemas to each user, you could avoid table naming conflicts
> between users, but it would still be possible to access a table in
> another schema of the same database.
>
>   regards, tom lane

Schema support would be a major step forward, and I can't wait to see it 
implemented. Thanks for the heads up Tom.

-- 
Adam Rossi
PlatinumSolutions, Inc.
[EMAIL PROTECTED]
http://www.platinumsolutions.com
P.O. Box 31  Oakton, VA 22124
PH: 703.471.9793  FAX: 703.471.7140

Re: [GENERAL] How I can join between the other database's tables?

[Tom Lane]

Jens Hartwig <[EMAIL PROTECTED]> writes:
> In fact, I also would be very content if PostgreSQL would be enabled to
> work with different databases (perhaps in some future release?).

I do not think that that's very likely to happen --- at least not in the
foreseeable future.  What is likely to happen for 7.2 is support for
schemas as defined by the SQL92 standard.  Schemas are basically a level
of naming in between databases and individual tables.  By assigning
separate schemas to each user, you could avoid table naming conflicts
between users, but it would still be possible to access a table in
another schema of the same database.

regards, tom lane

Re: [GENERAL] How I can join between the other database's tables?

[Jens Hartwig]

Hello all,

I think you are both right. The one says that tables which are related
enough to be joined should stay in one database and the other says that
it could be necessary for many reasons to hold the tables in different
databases. Even if I never was in need to join tables from different
databases (in this point I agree with Adam Haberlach), we have many
applications which use separate databases for lookup-queries or for the
sake of synchronization or replication of databases, therefore I
absolutely agree with Adam Rossi.

In fact, I also would be very content if PostgreSQL would be enabled to
work with different databases (perhaps in some future release?).

Best regards, Jens

Adam Rossi wrote:
> 
> On Wednesday 27 December 2000 08:44 pm, Adam Haberlach wrote:
> 
> >   I'm pretty sure you are right.  If your data is related enough to be
> > joined, it should be related enough to be in the same database.
> 
> I have to disagree. When you start getting into the hundreds of tables, some
> form of partitioning is helpful for any number of reasons - security,
> backups, data ownership, management, etc. I have seen oracle installations
> with hundreds of databases, each with hundreds of tables, and often the users
> would write queries that linked across databasesfor example linking from
> the employee table in the HR database to the log tables in an application
> database. If this installation had been "flattened" to one giant database, it
> would have been a nightmare.
> 
> I for one really wish that PostgreSQL had this functionality. It is one of
> the biggest things that I miss from other databases.
> [...]

=
Jens Hartwig
-
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax  : +49 (0)30 2554-3187
Mobil: +49 (0)170 167-2648
E-Mail   : [EMAIL PROTECTED]
=

Re: [GENERAL] How I can join between the other database's tables?

[Anand Raman]

hi all,
I second this opnion.. 
Coming from a web development environment, it could help us to
distribute load on our servers..
regards
Anand

On Thu, Dec 28, 2000 at 08:21:26AM -0500, Adam Rossi wrote:
>On Wednesday 27 December 2000 08:44 pm, Adam Haberlach wrote:
>
>>  I'm pretty sure you are right.  If your data is related enough to be
>> joined, it should be related enough to be in the same database.
>
>I have to disagree. When you start getting into the hundreds of tables, some 
>form of partitioning is helpful for any number of reasons - security, 
>backups, data ownership, management, etc. I have seen oracle installations 
>with hundreds of databases, each with hundreds of tables, and often the users 
>would write queries that linked across databasesfor example linking from 
>the employee table in the HR database to the log tables in an application 
>database. If this installation had been "flattened" to one giant database, it 
>would have been a nightmare. 
>
>I for one really wish that PostgreSQL had this functionality. It is one of 
>the biggest things that I miss from other databases.
>
>Regards,
>
>Adam
>
>-- 
>Adam Rossi
>PlatinumSolutions, Inc.
>[EMAIL PROTECTED]
>http://www.platinumsolutions.com
>P.O. Box 31  Oakton, VA 22124
>PH: 703.471.9793  FAX: 703.471.7140

Re: [GENERAL] table design question

[Rick Vlahov]

George,
I am brand new to PostgreSQL and am just now getting my info together for a
possible deployment on AIX. I have however worked with several large(in my
frame of thinking) 6 million record databases on MSSQL.  I did a ton of
reading, here is what I've read, what I use and am pleased with:

The best columns to sort on are integers(smallest you need- smallint then
int) and character.
Integer are good because they are precise as opposed to a float which is
not.  Use a char field for text as in char(20), picking a size equal to the
largest text string you will be using. Do not use varchar(20) as your search
must first determine the size that is being used in the case of each
specific record. I do not see why there would be any advantage in converting
to hex. As far as a search goes I do not understand why that would be any
faster.

Good Luck,
Richard Vlahov
[EMAIL PROTECTED]

"George Johnson" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hello All,
>
> I need some advice regarding table column types and read speeds.
> I have a database of protein sequence data (strings of characters mostly
> including 20 alphabet characters) and nucleotide base sequence
> data (strings of characters mostly including a,t,g,c).
>
> When I wrote the homegrown backend to our database way back when, I cooked
> up some fun ways of storing the data for very fast access speeds (not
really
> interested in doing SQL-ish searching on the columns in place).
>
> I'm wondering what the best choice of storage is:  just leave them as text
> strings, do some fancy hexidecimal conversion, etc?  Or does it matter?
The
> tables for the most part contain the sequence information as well as a
> primary key.
>
> Any thoughts/suggestions would be useful.
>
> Happy Holidays (oh ... darn ... it's going to be 60's and sunny AGAIN on
the
> peninsula today [nudge to back-east bretheren] heheh)
>
> George Johnson
>

Re: [GENERAL] How I can join between the other database's tables?

[Adam Rossi]

On Wednesday 27 December 2000 08:44 pm, Adam Haberlach wrote:

>   I'm pretty sure you are right.  If your data is related enough to be
> joined, it should be related enough to be in the same database.

I have to disagree. When you start getting into the hundreds of tables, some 
form of partitioning is helpful for any number of reasons - security, 
backups, data ownership, management, etc. I have seen oracle installations 
with hundreds of databases, each with hundreds of tables, and often the users 
would write queries that linked across databasesfor example linking from 
the employee table in the HR database to the log tables in an application 
database. If this installation had been "flattened" to one giant database, it 
would have been a nightmare. 

I for one really wish that PostgreSQL had this functionality. It is one of 
the biggest things that I miss from other databases.

Regards,

Adam

-- 
Adam Rossi
PlatinumSolutions, Inc.
[EMAIL PROTECTED]
http://www.platinumsolutions.com
P.O. Box 31  Oakton, VA 22124
PH: 703.471.9793  FAX: 703.471.7140

Re: [GENERAL] User Privileges

[Jens Hartwig]

Hello,

the information can be found in the system-table-column PG_CLASS.RELACL:

SELECT RELACL
FROM   PG_CLASS
WHERE  RELNAME = 't_dummy';

The result seems to be an array of granted permissions, so you should
treat it is an array and look for the appropriate user- or group-name.

Hope this helps ...

Regards, Jens

"W. van den Akker" schrieb:
> 
> How do I retrieve this privilages?
> I want to disable menu-options within a program. For that I have to
> retrieve the privilages for some tables.
> 
> gr,
> 
> Willem
> 
> - Original Message -
> From: "Dan Wilson" <[EMAIL PROTECTED]>
> To: "Niral Trivedi" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, December 26, 2000 7:25 PM
> Subject: Re: [GENERAL] User Privileges
> 
> > > For example I have 5 tables in database A. And now I want to give
> > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according
> > to
> > > documentation, I have to execute 'GRANT' query 3 times(for
> > > select/update/insert) per table. meaning total of 15 times
> >
> > That's incorrect... you can do it all in one statement:
> >
> > GRANT select,update,insert TO "" ON table_1,table_2,table_3,etc
> >
> > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
> >
> > -Dan
> >
> >
> >

=
Jens Hartwig
-
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax  : +49 (0)30 2554-3187
Mobil: +49 (0)170 167-2648
E-Mail   : [EMAIL PROTECTED]
=

[GENERAL] Cost of max

[Stefano Bargioni]

I build a little test table with columns id serial, a text, b text. Why the cost of 
max(id) is made as a seq scan, as is for count(id)? This is a problem with very large 
tables.
IMHO, select max(id) could be done using the index prova_id_key.
Your opinion? TIA. Stefano

mydb=> explain select max(id) from prova;
NOTICE:  QUERY PLAN:

Aggregate  (cost=43.00 rows=1000 width=4)
  ->  Seq Scan on prova  (cost=43.00 rows=1000 width=4)

EXPLAIN
mydb=> explain select count(id) from prova;
NOTICE:  QUERY PLAN:

Aggregate  (cost=43.00 rows=1000 width=4)
  ->  Seq Scan on prova  (cost=43.00 rows=1000 width=4)

--
Dott. Stefano Bargioni
Biblioteca della Pontificia Universita' della Santa Croce - Roma
  
Personal web page:
--- "Si apud bibliothecam hortulum habes, nihil deerit"  (Cicerone) ---

Re: [GENERAL] User Privileges

[Dan Wilson]

SELECT relacl FROM pg_class WHERE relname = '[table name]'

The relacl column contains specific information that must then be parsed to
determine the users which have access to a the table and the privileges
granted.

Example:

{"=","dan=arw","group developers=arw"}

The first section means the PUBLIC does not have any privileges on the
table.  Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified
by a letter.  r = select, a = insert, w = update/delete, R = rule. User
"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group
"developers" has the same privileges.

For more details --
http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm

-Dan

- Original Message -
From: "W. van den Akker" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 26, 2000 11:57 PM
Subject: Re: [GENERAL] User Privileges


> How do I retrieve this privilages?
> I want to disable menu-options within a program. For that I have to
> retrieve the privilages for some tables.
>
> gr,
>
> Willem
>
> - Original Message -
> From: "Dan Wilson" <[EMAIL PROTECTED]>
> To: "Niral Trivedi" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, December 26, 2000 7:25 PM
> Subject: Re: [GENERAL] User Privileges
>
>
> > > For example I have 5 tables in database A. And now I want to give
> > > SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But
according
> > to
> > > documentation, I have to execute 'GRANT' query 3 times(for
> > > select/update/insert) per table. meaning total of 15 times
> >
> > That's incorrect... you can do it all in one statement:
> >
> > GRANT select,update,insert TO "" ON
table_1,table_2,table_3,etc
> >
> > http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm
> >
> > -Dan
> >
> >
> >
>