Re: [GENERAL] Is SQL silly as an RDBMS-app interface?

[Alvaro Herrera]

On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:

 SQL is only one possible relational query language.  It didn't
 become de facto standard until the mid- to late-80s.  
 
 It is an outgrowth of SEQEL (Structured English QuEry Language), 
 which was IBM's 1st try at a descriptive query language.  DEC
 had RDML (Relational Data Manipulation Language) to access it's
 RDBMS.  I'm sure that Burroughs, etc, had their own access methods,
 too.

Of course, in the context of a PostgreSQL list you can't forget QUEL and
PostQUEL, Ingres and POSTGRES query languages respectively.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Hay quien adquiere la mala costumbre de ser infeliz (M. A. Evans)

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] select null + 0 question

[listrec]

The

select null + 0

is not the same as the

select sum(a) from a

statement.

Something equivalent would be

select sum(a) where a in (select null as a union select 1 as a)

In other words: As far as I understand it, sum() sums up all non null
values. In statement you have only one value, which happens to be null which
in return adds up to null. In your other statement you have one non null
value and sum returns the sum of this one value which is 1.


Detlef


-Ursprungliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Auftrag von Jean-Christian
Imbeault
Gesendet: Montag, 14. Juli 2003 07:42
An: [EMAIL PROTECTED]
Betreff: [GENERAL] select null + 0 question


Why is it that select null + 1 gives null but select sum(a) from
table where there are null entries returns an integer?

Shouldn't the sum() and + operators behave the same?

TAL=# select null + 0;
  ?column?
--

(1 row)

TAL=# select * from a;
  a
---


  1
(3 rows)

TAL=# select sum(a) from a;
  sum
-
1
(1 row)


Thanks,

Jean-Christian Imbeault


---(end of broadcast)---
TIP 8: explain analyze is your friend


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Fw: [GENERAL] select null + 0 question

[Vincent Hikida]

Oops forgot to cc the list.

 Unfortunately, intra-row functions using nulls return nulls. Inter-row
 functions usually ignore the nulls. I think there may be a few
exceptions.
 Though there is a relational theory which has is rigorously consistent,
 nulls are not part of the theory. Nulls are basically what
someone/somewhere
 thought of as a convenient tool (which it is) but has no theoretical
 underpinning and is dangerous. I use it because I feel that I have enough
 experience but perhaps I'll be stung one day.

 It has been discussed on the list before that in Oracle that in Oracle an
 empty string and null are the same. However Postgres treats an empty
string
 '' as an actual value and not as null.

 I just happened to notice another difference recently between Oracle and
 Postgresql for the clause

 WHERE 1 IN (1,2,NULL)

 In Oracle, this clause is false because 1 compared to a NULL is false.
 However, Postgresql will return a true. I actually don't know what the
ANSI
 standard is for this case. Perhaps someone else on this list will know.
 Perhaps the standard body never even thought of this. Yes, I was actually
 stung by this particular while using it in Oracle.

 Vincent Hikida,
 Member of Technical Staff - Urbana Software, Inc.
 A Personalized Learning Experience

 www.UrbanaSoft.com

 - Original Message -
 From: Jean-Christian Imbeault [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 10:42 PM
 Subject: [GENERAL] select null + 0 question


  Why is it that select null + 1 gives null but select sum(a) from
  table where there are null entries returns an integer?
 
  Shouldn't the sum() and + operators behave the same?
 
  TAL=# select null + 0;
?column?
  --
 
  (1 row)
 
  TAL=# select * from a;
a
  ---
 
 
1
  (3 rows)
 
  TAL=# select sum(a) from a;
sum
  -
  1
  (1 row)
 
 
  Thanks,
 
  Jean-Christian Imbeault
 
 
  ---(end of broadcast)---
  TIP 8: explain analyze is your friend
 



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] select null + 0 question

[Joe Conway]

Jean-Christian Imbeault wrote:
Shouldn't the sum() and + operators behave the same?
No, see SQL99, Section 6.16, General Rules 1.b:

Otherwise, let TX be the single-column table that is the result of 
applying the value expression to each row of T and eliminating null 
values. If one or more null values are eliminated, then a completion 
condition is raised: warning  null value eliminated in set function.

I've never seen a database that emits the required warning, though.

Joe

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] select null + 0 question

[Mike Mascari]

Jean-Christian Imbeault wrote:

 Why is it that select null + 1 gives null but select sum(a) from
 table where there are null entries returns an integer?
 
 Shouldn't the sum() and + operators behave the same?

---

SQL92 (6.5 set function specification):

1) Case:

 a) If COUNT(*) is specified, then the result is the cardinality of T.

 b) Otherwise, let TX be the single-column table that is the result of
applying the value expression to each row of T and eliminating null
values. If one or more null values are eliminated, then a completion
condition is raised: warning-null value eliminated in set function.

---

So PostgreSQL is compliant with SQL92. Reading the above should
concern you regarding COUNT() as well:

CREATE TABLE foo (value integer);

INSERT INTO foo VALUES (NULL);
INSERT INTO foo VALUES (3);

Compare:

SELECT COUNT(*) FROM foo;
vs.
SELECT COUNT(value) FROM foo;

SQL has its problems. Of course, you could avoid this entirely by not
using NULLs :-)

Mike Mascari
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] FYI: geometric means in one step without custom functions

[Vincent Hikida]

This is a great technique.  It is especially useful in finance for
compounded interest for problems like the following

total return = ((1+janReturn)(1+febReturn)(1+marReturn))-1

I first learned it from an MBA in finance when I was looking over a
spreadsheet that she wrote.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
A Personalized Learning Experience

www.UrbanaSoft.com

- Original Message -
From: Andrew Gould [EMAIL PROTECTED]
To: Postgres Mailing List [EMAIL PROTECTED]
Sent: Sunday, July 06, 2003 8:38 AM
Subject: [GENERAL] FYI: geometric means in one step without custom functions


 A long time ago, I emailed this list about calculating
 a geometric mean in PostgreSQL.  Creating a custom
 function didn't work because the process of
 multiplying the values from each record resulted in
 numbers that exceeded the limits for the size of a
 number very quickly when dealing with large
 populations.

 I have learned, since, that you can achieve the same
 end by replacing certain steps with log functions.
 (Someone who is very good at math showed me this -- I
 just tested the results and wrote the sql.)  This
 method has 2 great benefits:

 1. The method pushes the limits of deriving geometric
 mean calculations considerably.
 2. The default installation of PostgreSQL has
 everything needed to perform the calculation.

 The sql statement below calculates the geometric mean
 of the lengths of stay (gm_los) for patients, grouped
 by diagnostic related group and fiscal year.

 The population (cases) and average length of stay
 (avg_los) are also reported.

 Note 1. Make sure you are calculating geometric mean
 on a data type that has values to the right of the
 decimal point.

 Note 2. You cannot use a log function on a value = 0.
 Thus, I filtered for los  0.

 select drg_no, fy, count(pt_id) as cases,
 avg(los) as avg_los,
 exp(sum(ln(los::real)) * (1.0/count(pt_id))) as gm_los

 from case_current where los  0
 group by drg_no, fy;

 Have fun!

 Andrew Gould

 ---(end of broadcast)---
 TIP 2: you can get off all lists at once with the unregister command
 (send unregister YourEmailAddressHere to [EMAIL PROTECTED])



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: Fw: [GENERAL] select null + 0 question

[Martijn van Oosterhout]

On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote:
 Oops forgot to cc the list.
  I just happened to notice another difference recently between Oracle and
  Postgresql for the clause
 
  WHERE 1 IN (1,2,NULL)
 
  In Oracle, this clause is false because 1 compared to a NULL is false.
  However, Postgresql will return a true. I actually don't know what the
 ANSI
  standard is for this case. Perhaps someone else on this list will know.
  Perhaps the standard body never even thought of this. Yes, I was actually
  stung by this particular while using it in Oracle.

I can;t comment on what the correct answer is, but I beleive the reason it
works in Postgres is because the expression is expanded to:

WHERE (1=1) OR (1=0) OR (1=NULL)

which becomes:

WHERE TRUE OR FALSE OR NULL

which is TRUE. (standard tri-value logic)
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 the West won the world not by the superiority of its ideas or values or
 religion but rather by its superiority in applying organized violence.
 Westerners often forget this fact, non-Westerners never do.
   - Samuel P. Huntington


pgp0.pgp
Description: PGP signature

Re: Fw: [GENERAL] select null + 0 question

[Stephan Szabo]

On Sun, 13 Jul 2003, Vincent Hikida wrote:

 Oops forgot to cc the list.

  Unfortunately, intra-row functions using nulls return nulls. Inter-row
  functions usually ignore the nulls. I think there may be a few
 exceptions.
  Though there is a relational theory which has is rigorously consistent,
  nulls are not part of the theory. Nulls are basically what
 someone/somewhere
  thought of as a convenient tool (which it is) but has no theoretical
  underpinning and is dangerous. I use it because I feel that I have enough
  experience but perhaps I'll be stung one day.
 
  It has been discussed on the list before that in Oracle that in Oracle an
  empty string and null are the same. However Postgres treats an empty
 string
  '' as an actual value and not as null.
 
  I just happened to notice another difference recently between Oracle and
  Postgresql for the clause
 
  WHERE 1 IN (1,2,NULL)
 
  In Oracle, this clause is false because 1 compared to a NULL is false.

If this is really true, then I believe Oracle does not implement this
feature correctly. By my reading of SQL92, RVC IN IPV is equivalent to
RVC =ANY IPV and in 8.7 GR2c, If the implied comparison predicate is
true for at least one row RT in T, then R comp op some T is true
which I believe holds for the case above.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] different transaction handling between postgresql and oracle/mysql

[Jörg Schulz]

Suppose the following:

create table test (a int primary key);
insert into test values (1);

select * from test;
a
=
1

In Postgresql if you do the following in a transaction (either with
autocommit=off or with an explizit begin):

insert into test values (2); - ok
insert into test values (1); - error (duplicate key)
insert into test values (3); - error (transaction aborted)
commit;

You get:

select * from test;
a
=
1


In Oracle/MySQL if you do the same you get:

insert into test values (2); - ok
insert into test values (1); - error (duplicate key)
insert into test values (3); - ok
commit;

select * from test;
a
=
1
2
3

Which behavior is right? 
Is there a way to make Postgresql behave like the other databases?
Which other Databases act like Postgresql and which do it like Oracle/MySQL?

Jörg

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Optimisation, index use question [long]

[Francois Suter]

 Standard issue. When you specify an unquoted number in a query it's
 interpreted as an int4 which doesn't match your indexes. Suggestions are:
 
 - Put quotes around your numbers or   eg. '1'
 - Cast them to the right type eg. 1::bigint

Huh, yeah, I remember now reading about this. Thanks a lot.

Still any idea about why isn't it faster to query on the primary key than on
the two foreign keys?

Cheers



Francois

Home page: http://www.monpetitcoin.com/
We waste our time spending money we don't have to buy things we don't need
to impress people we don't like


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] different transaction handling between postgresql and oracle/mysql

[Martijn van Oosterhout]

Um, the behaviour you are seeing is what would happen in PostgreSQL if
everything were all in one transaction. What you show for Oracle is what
would happen if each statement were in it's own transaction.

On the postgresql server here, without transactions:

create temp table test (a int primary key);
insert into test values (1);  - ok
insert into test values (2);  - ok
insert into test values (1);  - duplicate key
insert into test values (3);  - ok

With transactions you get:

create temp table test (a int primary key);
insert into test values (1);  - ok
begin;
insert into test values (2);  - ok
insert into test values (1);  - duplicate key
insert into test values (3);  - aborted transaction
abort;

These are both correct behaviour AFAIK.

Hpoe this helps,

On Mon, Jul 14, 2003 at 09:47:30AM +0200, Jörg Schulz wrote:
 Suppose the following:
 
 create table test (a int primary key);
 insert into test values (1);
 
 select * from test;
 a
 =
 1
 
 In Postgresql if you do the following in a transaction (either with
 autocommit=off or with an explizit begin):
 
 insert into test values (2); - ok
 insert into test values (1); - error (duplicate key)
 insert into test values (3); - error (transaction aborted)
 commit;
 
 You get:
 
 select * from test;
 a
 =
 1
 
 
 In Oracle/MySQL if you do the same you get:
 
 insert into test values (2); - ok
 insert into test values (1); - error (duplicate key)
 insert into test values (3); - ok
 commit;
 
 select * from test;
 a
 =
 1
 2
 3
 
 Which behavior is right? 
 Is there a way to make Postgresql behave like the other databases?
 Which other Databases act like Postgresql and which do it like Oracle/MySQL?
 
 Jörg
 
 ---(end of broadcast)---
 TIP 4: Don't 'kill -9' the postmaster

-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 the West won the world not by the superiority of its ideas or values or
 religion but rather by its superiority in applying organized violence.
 Westerners often forget this fact, non-Westerners never do.
   - Samuel P. Huntington


pgp0.pgp
Description: PGP signature

Re: [GENERAL] different transaction handling between postgresql and

[Peter Childs]

On Mon, 14 Jul 2003, Jörg Schulz wrote:

 Suppose the following:
 
 create table test (a int primary key);
 insert into test values (1);
 
 select * from test;
 a
 =
 1
 
 In Postgresql if you do the following in a transaction (either with
 autocommit=off or with an explizit begin):
 
 insert into test values (2); - ok
 insert into test values (1); - error (duplicate key)
 insert into test values (3); - error (transaction aborted)
 commit;
 
 You get:
 
 select * from test;
 a
 =
 1
 
 
 In Oracle/MySQL if you do the same you get:
 
 insert into test values (2); - ok
 insert into test values (1); - error (duplicate key)
 insert into test values (3); - ok
 commit;
 
 select * from test;
 a
 =
 1
 2
 3
 
 Which behavior is right? 

The first I believe

Transactions have to be committed in there entirety or not at all. 
MySql does not do transactions on its standard tables anyway you have to 
switch them on at table create time (early versions could not cope with 
them at all!) I have this feeling the reason Oracle gives this result may 
be again because transactions have been switched off. If you want the 
second result in Postgres just switch auto-commit on!

Peter Childs


 Is there a way to make Postgresql behave like the other databases?
 Which other Databases act like Postgresql and which do it like Oracle/MySQL?
 
 Jörg
 
 ---(end of broadcast)---
 TIP 4: Don't 'kill -9' the postmaster
 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] OS X installation with readline support

[Adam Witney]

This worked for me...

./configure --with-libs=/sw/lib --with-includes=/sw/include



 After reading this:
 
 http://marc.theaimsgroup.com/?l=postgresql-generalm=103886532224699w=2
 
 It looks like some of you out there have successfully installed
 postgresql on OS X with readline support.  I have installed readline via
 Fink but the compiler can't find it.  How do I tell it where to look?
 
 culley
 
 
 
 ---(end of broadcast)---
 TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] different transaction handling between postgresql and oracle/mysql

[Jörg Schulz]

 ... I have this feeling the reason Oracle gives this result may
 be again because transactions have been switched off!

This snippet comes from the Oracle console:
(table name is a not test / messages are in german)

SQL show autocommit;
autocommit OFF
SQL select * from a;

 A
--
 1

SQL commit;

Transaktion mit COMMIT abgeschlossen.

SQL insert into a values (2);

1 Zeile wurde erstellt.

SQL insert into a values (3);

1 Zeile wurde erstellt.

SQL insert into a values (1);
insert into a values (1)
*
FEHLER in Zeile 1:
ORA-1: Verstoß gegen Eindeutigkeit, Regel (KLAX.SYS_C001753)


SQL insert into a values (4);

1 Zeile wurde erstellt.

SQL commit;

Transaktion mit COMMIT abgeschlossen.

SQL select * from a;

 A
--
 1
 3
 4
 2

SQL

 ...If you want the
 second result in Postgres just switch auto-commit on

Maybe I have to do other things in another table. So I must
do it inside a transaction.

Jörg

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] different transaction handling between postgresql and

[Mike Mascari]

Jörg Schulz wrote:

... I have this feeling the reason Oracle gives this result may
be again because transactions have been switched off!
 
 This snippet comes from the Oracle console:
 (table name is a not test / messages are in german)
 
...

 SQL select * from a;
 
  A
 --
  1
  3
  4
  2

Presumably Oracle is not rolling back a duplicate key violation,
allowing the transaction to continue. This is an often requested
feature not present in PostgreSQL.

Mike Mascari
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] different transaction handling between postgresql and

[Peter Childs]

On Mon, 14 Jul 2003, Mike Mascari wrote:

 Jörg Schulz wrote:
 
 ... I have this feeling the reason Oracle gives this result may
 be again because transactions have been switched off!
  
  This snippet comes from the Oracle console:
  (table name is a not test / messages are in german)
  
 ...
 
  SQL select * from a;
  
   A
  --
   1
   3
   4
   2
 
 Presumably Oracle is not rolling back a duplicate key violation,
 allowing the transaction to continue. This is an often requested
 feature not present in PostgreSQL.

Bug. Not Feature 

Transactions must be all or nothing. If one step fails for what 
ever reason all steps must be failed and rolled back. While in this simple 
case ignoring the statment may look fine in more complex examples (where 
the is more data in the table...) this can mean data loss and massive 
problems!

Peter Childs

 
 Mike Mascari
 [EMAIL PROTECTED]
 
 
 
 ---(end of broadcast)---
 TIP 7: don't forget to increase your free space map settings
 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: Fw: [GENERAL] select null + 0 question

[Csaba Nagy]

This was executed via sql+ on an Oracle 9i installation:

SQL select 1 from dual where 1 in (1,2,null);

 1
--
 1

SQL select 1 from dual where 1 in (null);

no rows selected


I would say the Oracle implementation is correct and the same as in
Postgres. For your problem I would check the other parts of the query.

Cheers,
Csaba.


On Mon, 2003-07-14 at 08:14, Vincent Hikida wrote:
 Oops forgot to cc the list.
 
  Unfortunately, intra-row functions using nulls return nulls. Inter-row
  functions usually ignore the nulls. I think there may be a few
 exceptions.
  Though there is a relational theory which has is rigorously consistent,
  nulls are not part of the theory. Nulls are basically what
 someone/somewhere
  thought of as a convenient tool (which it is) but has no theoretical
  underpinning and is dangerous. I use it because I feel that I have enough
  experience but perhaps I'll be stung one day.
 
  It has been discussed on the list before that in Oracle that in Oracle an
  empty string and null are the same. However Postgres treats an empty
 string
  '' as an actual value and not as null.
 
  I just happened to notice another difference recently between Oracle and
  Postgresql for the clause
 
  WHERE 1 IN (1,2,NULL)
 
  In Oracle, this clause is false because 1 compared to a NULL is false.
  However, Postgresql will return a true. I actually don't know what the
 ANSI
  standard is for this case. Perhaps someone else on this list will know.
  Perhaps the standard body never even thought of this. Yes, I was actually
  stung by this particular while using it in Oracle.
 
  Vincent Hikida,
  Member of Technical Staff - Urbana Software, Inc.
  A Personalized Learning Experience
 
  www.UrbanaSoft.com
 
  - Original Message -
  From: Jean-Christian Imbeault [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Sunday, July 13, 2003 10:42 PM
  Subject: [GENERAL] select null + 0 question
 
 
   Why is it that select null + 1 gives null but select sum(a) from
   table where there are null entries returns an integer?
  
   Shouldn't the sum() and + operators behave the same?
  
   TAL=# select null + 0;
 ?column?
   --
  
   (1 row)
  
   TAL=# select * from a;
 a
   ---
  
  
 1
   (3 rows)
  
   TAL=# select sum(a) from a;
 sum
   -
   1
   (1 row)
  
  
   Thanks,
  
   Jean-Christian Imbeault
  
  
   ---(end of broadcast)---
   TIP 8: explain analyze is your friend
  
 
 
 
 ---(end of broadcast)---
 TIP 4: Don't 'kill -9' the postmaster
 



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] libpq.so.2 problems

[psql-mail]

Hi,
I'm having trouble with libpg.so.2.
Specifically:

Can't load '/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/
Pg/Pg.so' for module Pg: libpq.so.2: cannot open shared object file: No 
such file or directory at /usr/lib/perl5/5.8.0/i386-linux-thread-multi/
DynaLoader.pm line 229.

I noticed someone else was having simlar trouble o nthe 11th of June.

libpq.so.2 does exist, but is in:
/usr/lib/pgsql/backup/libpq.so.2
the permission set on this directory (and the name) suggest that things 
in here shouldn't be used.
Whats the score? should i just copy libpq.so.2 back out to /usr/lib 
along with all the other libpq.so.X's ?

I have upto date versions of BDI::Pg, Pg, and 
postgresql-libs-7.3.2-3.i386.rpm (Redhat 9).

Thanks.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] different transaction handling between postgresql and

[Csaba Nagy]

Oracle does not roll back any transaction unless explicitly requested by
the client application. If there are errors while executing statements
inside a transaction, their effect is rolled back, not the whole
transaction. The application can then decide if the successful part of
the transaction is rolled back as a whole, or committed as a whole...

This is contrasting with postgreSQL behavior of rolling back the
transaction automatically after any error.

This feature is often requested because it's very useful, especially in
big imports (where you don't want to roll back your whole import because
of 2 invalid lines). 
Of course others say that input validation is the application's
responsibility, which is also a valid point, but I can tell you there
are other valid usages of this feature, like complicated transactions
where you want to take one course of action or other depending on the
success/failure of a query, which is only possible using hacks in
postgres right now, and doesn't even always work (like for a duplicate
key insert, you can't 100% make sure it won't fail). The suggested
solution on this list is to be prepared to repeat the transaction, but
this leads to more complicated application code.

The main reason why this feature is painful to implement in Postgres is
the lack of nested transactions. Until they are not implemented, chunk
your error prone transactions as small as you can, filter out invalid
data, and be prepared to repeat the cycle.

Cheers,
Csaba.


On Mon, 2003-07-14 at 10:34, Mike Mascari wrote:
 Jrg Schulz wrote:
 
 ... I have this feeling the reason Oracle gives this result may
 be again because transactions have been switched off!
  
  This snippet comes from the Oracle console:
  (table name is a not test / messages are in german)
  
 ...
 
  SQL select * from a;
  
   A
  --
   1
   3
   4
   2
 
 Presumably Oracle is not rolling back a duplicate key violation,
 allowing the transaction to continue. This is an often requested
 feature not present in PostgreSQL.
 
 Mike Mascari
 [EMAIL PROTECTED]
 
 
 
 ---(end of broadcast)---
 TIP 7: don't forget to increase your free space map settings
 



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] different transaction handling between postgresql and

[Csaba Nagy]

On Mon, 2003-07-14 at 10:43, Peter Childs wrote:
 On Mon, 14 Jul 2003, Mike Mascari wrote:
 
  Jrg Schulz wrote:
  
  ... I have this feeling the reason Oracle gives this result may
  be again because transactions have been switched off!
   
   This snippet comes from the Oracle console:
   (table name is a not test / messages are in german)
   
  ...
  
   SQL select * from a;
   
A
   --
1
3
4
2
  
  Presumably Oracle is not rolling back a duplicate key violation,
  allowing the transaction to continue. This is an often requested
  feature not present in PostgreSQL.
 
 Bug. Not Feature 
 
   Transactions must be all or nothing. If one step fails for what 
 ever reason all steps must be failed and rolled back. While in this simple 
 case ignoring the statment may look fine in more complex examples (where 
 the is more data in the table...) this can mean data loss and massive 
 problems!

Wrong. Oracle is NOT ignoring the error, it responds with an error
message, which is telling the user that the current query had an error
(in Java you get an exception).
Now the application can decide if this means an error for the whole
transaction or just for the current query. If your application rolles
back on all errors, this is the exact behavior which is forced on you by
postgres. But if the application can decide the last error is ignorable
(which can be true in some cases, as in this example), and the
transaction is still valid despite of the latest error, then you get in
a lot of cases more elegant/readable application code. You could also
easily log the failed inserts and do something else on that data, while
the valid entries are committed.
I see this as a feature, because the programmer gets more control, more
options, and the original behavior is still implemented.

Cheers,
Csaba.

 
 Peter Childs
 
  
  Mike Mascari
  [EMAIL PROTECTED]
  
  
  
  ---(end of broadcast)---
  TIP 7: don't forget to increase your free space map settings
  
 
 
 ---(end of broadcast)---
 TIP 4: Don't 'kill -9' the postmaster
 



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] different transaction handling between postgresql and

[Mike Mascari]

Peter Childs wrote:

 On Mon, 14 Jul 2003, Mike Mascari wrote: 
 
Jörg Schulz wrote:

Presumably Oracle is not rolling back a duplicate key violation,
allowing the transaction to continue. This is an often requested
feature not present in PostgreSQL.
 
 
 Bug. Not Feature 
 
   Transactions must be all or nothing. If one step fails for what 
 ever reason all steps must be failed and rolled back. While in this simple 
 case ignoring the statment may look fine in more complex examples (where 
 the is more data in the table...) this can mean data loss and massive 
 problems!

I agree. However a common scenario that has appeared on these lists is
a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
race conditions. Because Oracle doesn't rollback the transaction, it
is implementable in SQL. For PostgreSQL, you either need to use
various locking techniques which reduces concurrency or be prepared to
resubmit the entire transaction. Savepoints and/or nested transactions
may alleviate the situation in the future, however.

Mike Mascari
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] different transaction handling between postgresql and

[Shridhar Daithankar]

On 14 Jul 2003 at 5:18, Mike Mascari wrote:

 I agree. However a common scenario that has appeared on these lists is
 a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
 race conditions. Because Oracle doesn't rollback the transaction, it
 is implementable in SQL. For PostgreSQL, you either need to use
 various locking techniques which reduces concurrency or be prepared to
 resubmit the entire transaction. Savepoints and/or nested transactions
 may alleviate the situation in the future, however.

Recognising the need of such, SQL standard has been extended to accommodate a 
merge command which is create if not exists else update types.

Correct me if I am wrong..

BTW, what's wrong with select for update in such scenario?




Bye
 Shridhar

--
Feel free to contact me (flames about my english and the useless of thisdriver 
will be redirected to /dev/null, oh no, it's full...).(Michael Beck, describing 
the PC-speaker sound device)


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Auto Starting Postgresql Under Mandrake 9.1 ??

[Devrim GUNDUZ]

Hi

On 14 Jul 2003, Peter Moscatt wrote:

 I can manually start the server using: pg_ctl start -l -D
 /usr/local/pgsql/data.
 
 But when I put this line in the: /etc/rc.d/rc.local file then check the
 boot.log file to see if it's loading - I see this is not the case.

AFAIK you should write 
/usr/bin/pg_ctl start 

Regards,
-- 
Devrim GUNDUZ
[EMAIL PROTECTED]   [EMAIL PROTECTED] 
http://www.tdmsoft.com
http://www.gunduz.org


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Is SQL silly as an RDBMS-app interface?

[Peter Childs]

On Mon, 14 Jul 2003, Alvaro Herrera wrote:

 On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote:
 
  SQL is only one possible relational query language.  It didn't
  become de facto standard until the mid- to late-80s.  
  
  It is an outgrowth of SEQEL (Structured English QuEry Language), 
  which was IBM's 1st try at a descriptive query language.  DEC
  had RDML (Relational Data Manipulation Language) to access it's
  RDBMS.  I'm sure that Burroughs, etc, had their own access methods,
  too.
 
 Of course, in the context of a PostgreSQL list you can't forget QUEL and
 PostQUEL, Ingres and POSTGRES query languages respectively.
 
 

SQL is almost the worst standard I've come across. Its the 
computer equivalent of VHS. Its not readable by computer or humans. (Enough 
Flaming on to why I think this)

SQL is verbose it often make you repeat your self when its obvious 
what you mean.

INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a;

SQL has many different ways of writing the same thing for 
different purposes. eg
INSERT INTO a (b,c) VALUES (1,2);
UPDATE a set b=1, c=2 WHERE d=3;

Why not

INSERT INTO a set b=1, c=3; 

its certainly more readable and consistent.

Parsing is hard work 

No Meta Standard (How do you find out the structure of your table using 
pure SQL?

Very difficult to operate with Trees and simple hierarchal data. 

I could continue. Still its a language we all love to hate.

Peter Childs


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] libpq.so.2 problems

[psql-mail]

Ok - discovered the solution in pgsql-php, repeated below for reference:



From: Peter De Muer (Work) [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: 7.3.1 update gives PHP libpq.so.2 problem
Date: Tue, 4 Feb 2003 14:06:04 +0100

try making a soft link  libpq.so.2 to the libpq.so.3 file  that comes 
with
PHP 7.3.1.

regards,

pt3r
---
End of quoted message

So (for me) its a case of:

cd /usr/lib
ln -s libpq.so.3 libpq.so.2


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Auto Starting Postgresql Under Mandrake 9.1 ??

[Shridhar Daithankar]

On 14 Jul 2003 at 19:32, Peter Moscatt wrote:

 I have just installed postgresql from the RPMs on the MD dist CDs - all
 went to plan.
 
 I can manually start the server using: pg_ctl start -l -D
 /usr/local/pgsql/data.

You should place a logfile name after -l option. See if that log file gets 
proper entries.

HTH

Bye
 Shridhar

--
Priority:   A statement of the importance of a user or a program.  Often
expressed as a relative priority, indicating that the user doesn't  care when 
the work is completed so long as he is treated less badly than someone else.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] different transaction handling between postgresql and

[Csaba Nagy]

This has been discussed for many times on this list, but shortly: when
inserting a new row, there's no previous row to select for update. If
you have 2 concurrent transactions, both of them can execute the select
for update at the same time, select nothing, and then try to insert the
same key, and bang: one of them fails.

Cheers,
Csaba.


On Mon, 2003-07-14 at 11:31, Shridhar Daithankar wrote:
 On 14 Jul 2003 at 5:18, Mike Mascari wrote:
 
  I agree. However a common scenario that has appeared on these lists is
  a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without
  race conditions. Because Oracle doesn't rollback the transaction, it
  is implementable in SQL. For PostgreSQL, you either need to use
  various locking techniques which reduces concurrency or be prepared to
  resubmit the entire transaction. Savepoints and/or nested transactions
  may alleviate the situation in the future, however.
 
 Recognising the need of such, SQL standard has been extended to accommodate a 
 merge command which is create if not exists else update types.
 
 Correct me if I am wrong..
 
 BTW, what's wrong with select for update in such scenario?
 
 
 
 
 Bye
  Shridhar
 
 --
 Feel free to contact me (flames about my english and the useless of thisdriver 
 will be redirected to /dev/null, oh no, it's full...).(Michael Beck, describing 
 the PC-speaker sound device)
 
 
 ---(end of broadcast)---
 TIP 9: the planner will ignore your desire to choose an index scan if your
   joining column's datatypes do not match
 



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] drop function all - ?

[Együd Csaba]

Elein,
actually I do not store my source in a cvs or anything similar.
Yes, you are absolutally right I should but I'm allways busy and
do not have the time to deal enought with these nice softwares.

But I think my problem is not about this. I have the latest
source code in a separate file so I can restore my functions.

My question points the leak of an expression which can clean
the database from the old or expired stored procedures.
Actually I also can create shell scripts to delete the stored
procs (pg_dump, grep, sed, ...) but I think this could be
covered its own command in such a flexible DBMS like
Postgres.

Best Regards

-- Csaba

- Original Message -
From: elein [EMAIL PROTECTED]
To: Együd Csaba [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 10:44 PM
Subject: Re: [GENERAL] drop function all - ?


 You don't keep the SQL for your functions in
 source code control?

 When working on a database design, always keep your
 original *and* modified SQL scripts so that you can
 always create an empty version of your production
 system.  Part of that is creating drop scripts
 for each object, including functions, that you create.
 CYA.

 For those of you playing fast and loose,
 this query will produce a list of functions and arguments
 in the schema public.  Use it in a shell script loop
 to drop your functions.

 SELECT
 n.nspname || '.' || p.proname || '(' ||
pg_catalog.oidvectortypes(p.proargtypes) || ')'
 FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 WHERE p.prorettype  'pg_catalog.cstring'::pg_catalog.regtype
   AND p.proargtypes[0]  'pg_catalog.cstring'::pg_catalog.regtype
   AND NOT p.proisagg
   AND n.nspname ~ '^public$';

 DO NOT under ANY circumstances drop
 ANY functions in the schema pg_catalog.

 [EMAIL PROTECTED]


 On Sun, Jul 13, 2003 at 02:02:15PM +0200, Együd Csaba wrote:
  Hi,
  is there any way to get rid of all of my functions in a database. I mean
  such an expression: DROP FUNCTION ALL;
 
  The point of this issue is that time to time one writes and rewrites
  functions with the same name and forgets to remove the existing ones. At
a
  certain time one tries to call a function but don't know why it fails.
It
  fails bacause there is an other (or more) function with the same name
and
  with a very similar parameter list (the same number and convertable
types).
 
  I'm in this situation
  So I'd like to remove all my functions and recreate the latest ones from
a
  file. Without dropping the whole database of corse.
 
  Any suggestions
 
  Thank you,
 
  -- Csaba
 
 
 
  ---
  Outgoing mail is certified Virus Free.
  Checked by AVG anti-virus system (http://www.grisoft.com).
  Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.
 
 
  ---(end of broadcast)---
  TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
 

 ---(end of broadcast)---
 TIP 9: the planner will ignore your desire to choose an index scan if your
   joining column's datatypes do not match





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] different transaction handling between postgresql and

[Ron Johnson]

On Mon, 2003-07-14 at 04:07, Csaba Nagy wrote:
[snip]
 This feature is often requested because it's very useful, especially in

Amen!  Give the app developer the opportunity to travel down a different
code path if s/he tries, for example, to insert a duplicate key.

[snip]
 The main reason why this feature is painful to implement in Postgres is
 the lack of nested transactions. Until they are not implemented, chunk
 your error prone transactions as small as you can, filter out invalid
 data, and be prepared to repeat the cycle.

Lack of nested transactions should not be a barrier.  The RDBMS that
I use professionally (Rdb/VMS) does not have nested transactions,
yet it and Oracle do the same thing: return an error code and allow
the app to decide what to do.

Yes, many times that will be to rollback the transaction, but it
might also be to change the data and try the statement again.

Let the developer decide!!!

-- 
+---+
| Ron Johnson, Jr. Home: [EMAIL PROTECTED]  |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|   |
| 4 degrees from Vladimir Putin
+---+


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Are you frustrated with PostgreSQL

[Dan Langille]

On 14 Jul 2003 at 15:16, Terence Chang wrote:

 Hi all:
 
 I am new to PostgreSQL DB, however I have years experience with Oracle
 8i and MS SQL. I am in the process to promot PostgreSQL to my future
 client, due to the cost. I am just wondering if overall people feels
 frustrated with PostgreSQL or feels happey with it. 

I've been working with databases for about 15 years, including 
Sybase, Oracle, PostgreSQL, and MySQL.  Yes, I'm happy with 
PostgreSQL.  No frustration at all.

 I know MySQL is simpiler and cheap. With my years experience with
 enterprise level DB like Oracle and MS SQL, I just don't feel right
 with MySQL. I love stored procedure. Sorry to MySQL lovers. 

Stored procedures is one reason I moved http://www.FreshPorts.org/ 
from MySQL to PostgreSQL.

 I have the following questions. Please reply me offline, so the mailling
 list won't get flood. Thanks!

If we reply to the list, everyone who asks the same questions as you 
will be able to find them in the archives.

 1. What is your favorite GUI tool for PostgreSQL?

I usually use command line tools for PG work, but do use PGAdmin from 
time to time. 

 2. In your organization, do you have someone who works as full time
 PostgreSQL DBA?

Yes.

 3. What is the biggest challenge you have with PostgreSQL?
 Administration or Programming?' 

Finding more time to spend working with it.

 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL? 

Yes.  Yes.  Friends don't let friends use MySQL.

 5. How often do your PostgreSQL run into problem or crash? Are most of
 the problem caused by PostgreSQL itself? 

I have never seen a PostgreSQL crash.  Most if not all of the 
problems have been caused by myself.
-- 
Dan Langille : http://www.langille.org/


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] change NAMEDATALEN to 64

[Kathy Zhu]

I think I figured out what went wrong.


regression.diffs is created by compareing src/test/regress/expect/name.out with 
src/test/regress/results/name.out.

Since expected/name.out comes with the jar, so any changes I made won't be 
reflected in the that file.

Now the question is, is there a way to update the expected/*out files ?? 

thanks,
kathy



 X-Original-To: [EMAIL PROTECTED]
 Date: Mon, 14 Jul 2003 16:10:15 -0600 (MDT)
 From: Kathy Zhu [EMAIL PROTECTED]
 Subject: [GENERAL] change NAMEDATALEN to 64
 To: [EMAIL PROTECTED]
 X-Virus-Scanned: by amavisd-new at postgresql.org
 
 Hi,
 
 We have tables with long names so I modified postgres_ext.h and change 
 NAMEDATALEN to 64 (originally is 32).
 
 When I do gmake check, I failed the test on name, see below.
 
 parallel group (13 tests):  float8 boolean float4 oid int4 int8 char int2 name 
t
 ext varchar bit numeric
  boolean  ... ok
  char ... ok
  name ... FAILED
  varchar  ... ok
  text ... ok
  int2 ... ok
  int4 ... ok
  int8 ... ok
  oid  ... ok
  float4   ... ok
  float8   ... ok
  bit  ... ok
  numeric  ... ok
 test strings  ... ok
 
 
 I am attaching the regression.diffs file created by the regression test, which 
I 
 don't know how to interprate.
 
  The question is, is it ok to fail this regression test since I do change 
 the NAMEDATALEN ???
 
 
 thanks,
 kathy




---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Are you frustrated with PostgreSQL

[Andrew Gould]

--- Terence Chang [EMAIL PROTECTED] wrote:
 I have the following questions. Please reply me
 offline, so the mailling
 list won't get flood. Thanks!

But if they don't read it, they can't correct me!
;-)

 1. What is your favorite GUI tool for PostgreSQL?
I create apps using MS Access as the GUI front-end.
For administration, I use scripts and the command
line.

 2. In your organization, do you have someone who
 works as full time
 PostgreSQL DBA?
No. We wear a lot of hats around here.

 3. What is the biggest challenge you have with
 PostgreSQL? Administration or
 Programming?
Programming -- if I were a Programmer or a DBA, I
could do more.  A solid database, a solid operating
system and a little scripting make day-to-day
administration easy enough.

 4. Overall, do you like PostgreSQL? Would you
 recommend it over MySQL?
Yes, and yes.  MySQL has a reputation for ease of
administration; however, I moved from MySQL to
PostgreSQL because missing features in MySQL meant
difficult work-arounds for users.  Also, the \help
system in psql is very useful for people learning SQL.
  As an end-user, I found PostgreSQL easier to use
than MySQL.

 5. How often do your PostgreSQL run into problem or
 crash? Are most of the
 problem caused by PostgreSQL itself?
At work, I haven't had any unplanned down time since I
started using PostgreSQL over 3 years ago.  At home, I
had a hard drive crash; but the databases were
restored from backup files without any problems.
Dependable backups should _not_ be taken for granted!

Best regards,

Andrew Gould

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Are you frustrated with PostgreSQL

[Ron Johnson]

On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
 On 14 Jul 2003 at 15:16, Terence Chang wrote:
 
  Hi all:
[snip]
 
 Stored procedures is one reason I moved http://www.FreshPorts.org/
 from MySQL to PostgreSQL.

Oh, the shame!  Advertising AOL For Broadband on a FreeBSD ports
site?

-- 
+---+
| Ron Johnson, Jr. Home: [EMAIL PROTECTED]  |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|   |
| 4 degrees from Vladimir Putin
+---+


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Are you frustrated with PostgreSQL

[Timothy Brier]

I have been working with various databases since the 80's.

Terence Chang wrote:
Hi all:

I am new to PostgreSQL DB, however I have years experience with Oracle 8i
and MS SQL. I am in the process to promot PostgreSQL to my future client,
due to the cost. I am just wondering if overall people feels frustrated with
PostgreSQL or feels happey with it.
I know MySQL is simpiler and cheap. With my years experience with enterprise
level DB like Oracle and MS SQL, I just don't feel right with MySQL. I love
stored procedure. Sorry to MySQL lovers.
I have the following questions. Please reply me offline, so the mailling
list won't get flood. Thanks!
1. What is your favorite GUI tool for PostgreSQL?
I use PGAdmin for a quick conversion of Indexes and Tables from MS-SQL. 
  /access  But for other items I either use the command line or EMS 
PostgreSQL Manager - This is the favorite tool of the developers I have 
working on various projects.

We develope web applications and applicationsin Delphi, C++
2. In your organization, do you have someone who works as full time
PostgreSQL DBA?
No.

3. What is the biggest challenge you have with PostgreSQL? Administration or
Programming?
My biggest challenge is getting funding to convert other projects 
completed using MS-SQL to PostgreSQL, but they are coming around.

PostgreSQL has been very easy to program and administer.

4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?
Coming from a background where data integrity is a high priority as well 
as performance - it's PostgrSQL hands down.

5. How often do your PostgreSQL run into problem or crash? Are most of the
problem caused by PostgreSQL itself?
It hasn't for over five years.


Any suggestion and help are welcome. Thanks!



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faqs/FAQ.html


Tim.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Are you frustrated with PostgreSQL

[Dan Langille]

On 14 Jul 2003 at 20:55, Ron Johnson wrote:

 On Mon, 2003-07-14 at 17:44, Dan Langille wrote:
  On 14 Jul 2003 at 15:16, Terence Chang wrote:
  
   Hi all:
 [snip]
  
  Stored procedures is one reason I moved http://www.FreshPorts.org/
  from MySQL to PostgreSQL.
 
 Oh, the shame!  Advertising AOL For Broadband on a FreeBSD ports
 site?

Yep.  I'll take money from almost anyone.
-- 
Dan Langille : http://www.langille.org/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[GENERAL] PostgreSQL/Hermes 0.1.0 RC4, call for testers

[Chris Travers]

Hi;

Hermes is the foundation for CRM and ERP tools for business processes 
such as field service automation, sales force automation, contact 
management and some scheduling tasks as well.  It supports both MySQL 
(mostly) and PostgreSQL (fully).

The 0.1.0 release will be suitable for small consultants, and small 
businesses that need multuser, centralized contact management, the 
ability to track scheduled contacts with customers, as well as projects, 
etc.  More information can be found on the Sourceforge project page 
(http://sourceforge.net/projects/hermesweb).  I am looking for people to 
try the software, and hopefully tell me what they like/don't like. What 
makes their lives easier and what makes it harder.

Best Wishes,
Chris Travers
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] perfromance impact of vacuum

[Matthew T. O'Connor]

On Mon, 2003-07-14 at 17:13, Jay O'Connor wrote:
 What impact in performance does vacuum have on an active database?  I'm
 being asked about this...or rather...someone is questioning the use of
 postgresql because of this

There is no easy answer to this question, other than if you vacuum
appropriately (not to often, or too infrequently) the net impact on
system performance is a win.  A database system that doesn't require a
vacuum type process still has to do the same work, it just does it at
transaction time, postgres defers this work until vacuum is run, which
is typically done at off peak times.  

As the author of the pg_autovacuum daemon in 7.4 contrib, I would
suggest you try it :-)  The primary advantage of the daemon is that it
monitors activity and when needed, vacuums specific tables, not whole
databases.  The thresholds that dictate when vacuums occur can be set at
runtime, and should allow you to get it performing the way you want.  If
you have any questions or problems let me know.  I am always interested
in hearing about others real world experiences. 



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] PostgreSQL/Hermes 0.1.0 RC4, call for testers

[Matthew T. O'Connor]

The demo on the hermes website is not working.  I tried to login with
the demo username and password and got this:

Warning: Access denied for user: '[EMAIL PROTECTED]'
(Using password: YES) in
/home/groups/h/he/hermesweb/htdocs/demo/hermes/DBAL_mysql-1.0.0-b.php on
line 40

Warning: MySQL Connection Failed: Access denied for user:
'[EMAIL PROTECTED]' (Using password: YES) in
/home/groups/h/he/hermesweb/htdocs/demo/hermes/DBAL_mysql-1.0.0-b.php on
line 40

Warning: Can't connect to local MySQL server through socket
'/var/run/mysqld/mysqld.sock' (2) in
/home/groups/h/he/hermesweb/htdocs/demo/hermes/DBAL_mysql-1.0.0-b.php on
line 81

Warning: MySQL Connection Failed: Can't connect to local MySQL server
through socket '/var/run/mysqld/mysqld.sock' (2) in
/home/groups/h/he/hermesweb/htdocs/demo/hermes/DBAL_mysql-1.0.0-b.php on
line 81

Warning: MySQL: A link to the server could not be established in
/home/groups/h/he/hermesweb/htdocs/demo/hermes/DBAL_mysql-1.0.0-b.php on
line 81
Error connecting to database. Was unable to process your request!

Login Failed. Please check your password and try again.


On Mon, 2003-07-14 at 23:33, Chris Travers wrote:
 Hi;
 
 Hermes is the foundation for CRM and ERP tools for business processes 
 such as field service automation, sales force automation, contact 
 management and some scheduling tasks as well.  It supports both MySQL 
 (mostly) and PostgreSQL (fully).
 
 The 0.1.0 release will be suitable for small consultants, and small 
 businesses that need multuser, centralized contact management, the 
 ability to track scheduled contacts with customers, as well as projects, 
 etc.  More information can be found on the Sourceforge project page 
 (http://sourceforge.net/projects/hermesweb).  I am looking for people to 
 try the software, and hopefully tell me what they like/don't like. What 
 makes their lives easier and what makes it harder.
 
 Best Wishes,
 Chris Travers
 
 
 ---(end of broadcast)---
 TIP 8: explain analyze is your friend
 


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] change NAMEDATALEN to 64

[Alvaro Herrera]

On Mon, Jul 14, 2003 at 04:10:15PM -0600, Kathy Zhu wrote:

 parallel group (13 tests):  float8 boolean float4 oid int4 int8 char int2 name t
 ext varchar bit numeric
  boolean  ... ok
  char ... ok
  name ... FAILED
 
 
 I am attaching the regression.diffs file created by the regression test, which I 
 don't know how to interprate.
 
  The question is, is it ok to fail this regression test since I do change 
 the NAMEDATALEN ???

AFAICS this is testing the ability to correctly truncate the identifier
length to 31 chars, so yes, the test is supposed to fail.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo (Jaime Salinas)

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[GENERAL] plperl language_handler Problems

[Raymond]

Updated my RH80 installation with the full complement of Postgres 7.3.3 RPMs.
Added python and tcl ( already have pl/sql) language bindings to the database 
without issue. However, the compiler complains about perl:

ERROR: Load of file /usr/lib/pgsql/plperl.so failed: libperl.so: cannot open 
shared object file: No such file or directory.

plperl.so is in the aforementioned directory and libperl.so is in 
/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE

Search path issues, symlinks needed?

Help please

Raymond


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] change NAMEDATALEN to 64

[Tom Lane]

Alvaro Herrera [EMAIL PROTECTED] writes:
 On Mon, Jul 14, 2003 at 04:10:15PM -0600, Kathy Zhu wrote:
  The question is, is it ok to fail this regression test since I do change 
 the NAMEDATALEN ???

 AFAICS this is testing the ability to correctly truncate the identifier
 length to 31 chars, so yes, the test is supposed to fail.

Indeed; see the changes to that regression result made between 7.2 and 7.3:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/test/regress/expected/name.out

Kathy, you might want to think about switching to a not-yet-obsolete
release ;-)

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] drop function all - ?

[Együd Csaba]

Hi Robert,
I admit your opinion. But I thought it could be a kind of service just for
convenience.
On the other hand there are a lot of commands in the PostgreSQL language
which does not appear in the original SQL standard.
Despite I think almost everybody find them very useful. So I would not say
it is an unlikely solution but a possible extension of SQL (at least
PostgreSQL).

Anyway I must say that I'll get by with or without this feature and even if
it won't be the part of the future releses I'll continue using Postgres. I
just wanted to indicate a possible customer requirement which could make it
more popular.

Best Regards,
-- Csaba

- Original Message -
From: Robert Treat [EMAIL PROTECTED]
To: Együd Csaba [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, July 14, 2003 10:53 PM
Subject: Re: [GENERAL] drop function all - ?


this seems like an unlikely solution since i don't think it is in any
sql spec... in the mean time you could create a function that loops
through pg_proc and drops functions based on the function attributes.
check the docs for info on data in pg_proc if you do. btw, the error
messages in 7.4 are improved to help with this case.

Robert Treat

On Mon, 2003-07-14 at 05:48, Együd Csaba wrote:
 Elein,
 actually I do not store my source in a cvs or anything similar.
 Yes, you are absolutally right I should but I'm allways busy and
 do not have the time to deal enought with these nice softwares.

 But I think my problem is not about this. I have the latest
 source code in a separate file so I can restore my functions.

 My question points the leak of an expression which can clean
 the database from the old or expired stored procedures.
 Actually I also can create shell scripts to delete the stored
 procs (pg_dump, grep, sed, ...) but I think this could be
 covered its own command in such a flexible DBMS like
 Postgres.

 Best Regards

 -- Csaba

 - Original Message -
 From: elein [EMAIL PROTECTED]
 To: Együd Csaba [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Sent: Sunday, July 13, 2003 10:44 PM
 Subject: Re: [GENERAL] drop function all - ?


  You don't keep the SQL for your functions in
  source code control?
 
  When working on a database design, always keep your
  original *and* modified SQL scripts so that you can
  always create an empty version of your production
  system.  Part of that is creating drop scripts
  for each object, including functions, that you create.
  CYA.
 
  For those of you playing fast and loose,
  this query will produce a list of functions and arguments
  in the schema public.  Use it in a shell script loop
  to drop your functions.
 
  SELECT
  n.nspname || '.' || p.proname || '(' ||
 pg_catalog.oidvectortypes(p.proargtypes) || ')'
  FROM pg_catalog.pg_proc p
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  WHERE p.prorettype  'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0]  'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND n.nspname ~ '^public$';
 
  DO NOT under ANY circumstances drop
  ANY functions in the schema pg_catalog.
 
  [EMAIL PROTECTED]
 
 
  On Sun, Jul 13, 2003 at 02:02:15PM +0200, Együd Csaba wrote:
   Hi,
   is there any way to get rid of all of my functions in a database. I
mean
   such an expression: DROP FUNCTION ALL;
  
   The point of this issue is that time to time one writes and rewrites
   functions with the same name and forgets to remove the existing ones.
At
 a
   certain time one tries to call a function but don't know why it fails.
 It
   fails bacause there is an other (or more) function with the same name
 and
   with a very similar parameter list (the same number and convertable
 types).
  
   I'm in this situation
   So I'd like to remove all my functions and recreate the latest ones
from
 a
   file. Without dropping the whole database of corse.
  
   Any suggestions
  
   Thank you,
  
   -- Csaba
  

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Are you frustrated with PostgreSQL

[Ron Mayer]

 1. What is your favorite GUI tool for PostgreSQL?

Just psql in an emacs window.
Emacs lets me see large result sets, and keep a history of my commands.


 2. In your organization, do you have someone who works as full time
 PostgreSQL DBA?

Our Oracle DBA is also the DBA for our production PostgreSQL databases.
Developers administer their own databases (some Oracle, some PostgreSQL).


 3. What is the biggest challenge you have with PostgreSQL?
 Administration or Programming?

  Challenge programming:
   Familiarity in the group with Oracle makes some tasks quicker in Oracle.

  Challenge administrating:
   Remembering when to analyze (especially remembering to stick analyze
   in the middle of big nightly scripts that make large temporary tables).


 4. Overall, do you like PostgreSQL? Would you recommend it over MySQL?

Personally I like it a lot.  Definately over MySQL, and even over Oracle
for anything containing data that don't have complicated replication needs.

My DBA, however, prefers Oracle over PostgreSQL.  But he doesn't have the
budget for Oracle for all our systems.  :-)  I think he prefers PostgreSQL
over MySQL, though.


 5. How often do your PostgreSQL run into problem or crash? Are most of the
 problem caused by PostgreSQL itself?

Never had a crash with PostgreSQL itself.  One problems with a table
where analyze's sampling would generate bad stastics (correlation) for
some tables and make the planner pick slow plans. This was worked around
by reordering data in the table.


 Any suggestion and help are welcome. Thanks!

One suggestion... if you get a lot of off-the-mailing-list responses,
could you post a summary?

   Ron





---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match