Re: [GENERAL] Getting table metadata

[ntinos]

SELECT * FROM pg_attribute WHERE attrelid=;  

The problem I'm running into however, is that given a table name, there
doesn't seem to be any way to get the table oid. Is there some function
or query that does this?
I think a way to get the table oid is: 

select oid from pg_class where relname= 

Ntinos Katsaros
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Getting table metadata

[Dann Corbit]

I am not the original poster.

Here is what PSQL does to get the table name list:
connxdatasync=# \d
* QUERY *
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as
"Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'v'
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'view'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'v'
  AND not exists (select 1 from pg_user where usesysid = c.relowner)
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
  AND c.relname !~ '^pg_'
ORDER BY "Name"
*

Here is the sort of queries that would be made by PSQL to collect
information about a single table:

connxdatasync=# \d "LastActions"
* QUERY *
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='LastActions'
*

* QUERY *
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'LastActions'
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
*

* QUERY *
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'LastActions' AND c.oid = i.indrelid AND i.indexrelid
= c2.oid
ORDER BY c2.relname
*

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 19, 2005 12:29 AM
To: Dann Corbit
Cc: Ken Tozier; PostgreSQL
Subject: Re: Getting table metadata

> 
> SELECT * FROM pg_attribute WHERE attrelid=;  
> 
> The problem I'm running into however, is that given a table name,
there
> doesn't seem to be any way to get the table oid. Is there some
function
> or query that does this?

I think a way to get the table oid is: 

select oid from pg_class where relname= 

Ntinos Katsaros

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

   http://archives.postgresql.org

[GENERAL] Postgres crashed when adding a sequence column

[Clive Page]

I have a largish table (71 million rows) to which I needed to add a new
integer column containing a unique identifier - a simple sequence seemed
to be good enough.  I discovered the CREATE SEQUENCE command which looked
as if it would do the job, and did the following:

ALTER TABLE intwfs ADD COLUMN id int ;
CREATE SEQUENCE myseq;
UPDATE intwfs SET id = nextval('myseq');

I expected it to take under an hour, but the process was still running
after several hours, taking ~15% cpu and a modest amount of memory.
Later on other users reported the machine was almost unusable and I found
that postmaster was hogging over 99% of cpu and all of memory.  I was
about to stop the process, but before I could do that the postmaster
crashed.

Obviously I'm doing something that Postgres doesn't support, but I'm not
quite clear what.  Any suggestions on how to achieve the same objective
more easily?

-- 
Clive Page


---(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] PL/PgSQL Index Usage with Trigger Variables

[Michael Fuhr]

On Wed, Jan 19, 2005 at 01:46:33AM -0600, Thomas F.O'Connell wrote:

> Follow-up question: are indexes used in dynamically executed queries?
>
> Rather than SEQSCAN or INDEXSCAN in the DETAIL item, I see RESULT 
> followed by a large amount of unreadable (by me) output.

Are you sure you're looking at the right DETAIL?  Adding some
RAISE INFO or RAISE DEBUG statements can help you pinpoint which
log output belongs to which part of the function.

I just wrote a test function that used EXECUTE to do an UPDATE and
a SELECT loop and saw INDEXSCAN in the relevant DETAIL sections.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] ECPG Segfaulting on EXEC SQL connect

[Michael Meskes]

On Tue, Jan 18, 2005 at 05:49:05PM +0100, Hans-Michael Stahl wrote:
> This also happens with Postgresql 7.4. under SUSE Linux 9.1 (kernel 
> 2.6.4-52)  The last version for which embedded SQL worked well for me 
> was 7.3. The same source which worked fine under 7.3 for many months 
> stopped working with my switch to 7.4.2. :-(

Interesting. 

> And it not only happens during connect, but also during a select. It is 
> very difficult to debug, since often the stack is destroyed and a 
> backtrace in gdb does not work.  From what I can see it is always 
> related to calls of malloc/free/realloc issued by libpq.

I would be happy to help debugging this. But up to now I didn't even
know about this problem. Could you please send me a test case so I can
reproduce this? Also did you try using 8.0?

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

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

   http://archives.postgresql.org

[GENERAL] what happened to the website?

[Dick Davies]

http://www.postgresql.org

looks bloody awful in firefox on debian, until I switch font
(on the site) from 'normal' to 'large'.

Anyone else seeing that? I'm sure it was fine a couple of weeks back.

-- 
'My life, and by extension everyone else's, is meaningless.'
-- Bender
Rasputin :: Jack of All Trades - Master of Nuns

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

Re: [GENERAL] cron & backup

[Martijn van Oosterhout]

On Tue, Jan 18, 2005 at 06:30:32PM -0500, Allen wrote:
> I had a problem in the past with the vaccumdb command on 7.2.3 
> mysteriously never returning when run from cron on FreeBSD, but it ran 
> fine from cmdline. Had to be an environmental issue. Not having time to 
> track it down, I tried an alternative lazy method...
> 
> /usr/local/bin/vacuumdb -z -f dbname   <-- sometimes never returned

If it never returned it would still be running right. So it should show
up when you run "ps". You would be able to strace to see what the
problem was. Note, if a script has no output you don't get an email
either.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpsL35PwTV29.pgp
Description: PGP signature

Re: [GENERAL] Multiline plpython procedure

[Martijn van Oosterhout]

On Wed, Jan 19, 2005 at 06:28:25PM +1100, Stuart Bishop wrote:
> Michael Fuhr wrote:
> >If Python's behavior is intentional then the newline burden would
> >seem to be on the user or on plpythonu.  I think Tom's point is
> >that that's just silly
> 
> Changing this behavior in Python would break backwards compatibility. In 
> particular, the exec() function accepts strings that have already been 
> unescaped:
> 
> >>> exec('print """\n\r\n\r\n"""')
> 
> In the above example, the exec function is being passed a string 
> containing carridge returns and line feeds - not '\n' and '\r' character 
> sequences.

Ofcourse, if the \r is within a literal string, then ofcourse you can't
ignore it. Other languages like C and Perl also maintain any character
within a string. The point is that outside of character strings, there
is no need to consider a \n different form a \r (unless there is a
place in Python where an extra newline changes the meaning).

Sure, you can't just run dos2unix over the code, but within the parser
this is a simple change.

> It is too late for the Python 2.3 series anyway - 2.3.5 is being 
> released Jan 26th and there won't be a 2.3.6. If it was championed and 
> it decided that the above example is a bug and not a feature and a patch 
> produced, it could get into 2.4.1 due April and 2.5+
> 
> I suspect this means fixing this problem in plpythonu for 8.1.

I suggest adding to the Release Notes:

  User defined functions using the Python language must use the newline
  delimiter of the server OS. There is currently no standard way of
  determining the newline delimiter of the server. Note this also
  affects the portability of pg_dump output. 

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgphUIMBs1ZAu.pgp
Description: PGP signature

Re: [GENERAL] Postgres crashed when adding a sequence column

[Richard Huxton]

Clive Page wrote:
I have a largish table (71 million rows) to which I needed to add a new
integer column containing a unique identifier - a simple sequence seemed
to be good enough.  I discovered the CREATE SEQUENCE command which looked
as if it would do the job, and did the following:
ALTER TABLE intwfs ADD COLUMN id int ;
CREATE SEQUENCE myseq;
UPDATE intwfs SET id = nextval('myseq');
Nowt wrong with that.
I expected it to take under an hour, but the process was still running
after several hours, taking ~15% cpu and a modest amount of memory.
Later on other users reported the machine was almost unusable and I found
that postmaster was hogging over 99% of cpu and all of memory.  I was
about to stop the process, but before I could do that the postmaster
crashed.
Obviously I'm doing something that Postgres doesn't support, but I'm not
quite clear what.  Any suggestions on how to achieve the same objective
more easily?
Nothing wrong with what you're doing, however, you are running a 
transaction that touches 142 million rows (expiring the old rows and 
adding new ones). Still, unless you are particularly short of memory, or 
haven't tuned PostgreSQL it should be fine.

Some questions:
1. Is the table particularly wide (i.e. number/size of columns)?
2. Do you have any foreign keys/triggers on the table?
I suspect point 2, but that's just me guessing. What I'm guessing is 
that there is a load of pending/deferred triggers working their way through.
--
  Richard Huxton
  Archonet Ltd

---(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] Postgres crashed when adding a sequence column

[Clive Page]

On Wed, 19 Jan 2005, Richard Huxton wrote:

> Nothing wrong with what you're doing, however, you are running a
> transaction that touches 142 million rows (expiring the old rows and
> adding new ones). Still, unless you are particularly short of memory, or
> haven't tuned PostgreSQL it should be fine.
>
> Some questions:
> 1. Is the table particularly wide (i.e. number/size of columns)?

Well 28 columns, one varchar, one box, rest 4-byte real/integer.

> 2. Do you have any foreign keys/triggers on the table?

No triggers, but there's one R-tree on the box, and one B-tree on an
integer column.  I guess I should have dropped those and re-created
afterwards, but the recreation takes some time, so I hoped to avoid the
need to do that.

I've now managed to restart the server, which took ~1hr to clean itself
up, and am doing a VACUUM FULL ANALYZE on the table.  Looks as if the data
are ok, but the new column is still empty of course.

Thanks very much for your help, Richard.  Will try again without indices,
keeping an eye on the cpu/memory consumption.

-- 
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH,  U.K.


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

   http://archives.postgresql.org

Re: [GENERAL] Postgres crashed when adding a sequence column

[Martijn van Oosterhout]

On Wed, Jan 19, 2005 at 09:35:15AM +, Clive Page wrote:
> I have a largish table (71 million rows) to which I needed to add a new
> integer column containing a unique identifier - a simple sequence seemed
> to be good enough.  I discovered the CREATE SEQUENCE command which looked
> as if it would do the job, and did the following:
> 
> ALTER TABLE intwfs ADD COLUMN id int ;
> CREATE SEQUENCE myseq;
> UPDATE intwfs SET id = nextval('myseq');

You're not doing anything unusual there. Do you have any deferred
constrints or foreign keys. I can't think of anything else that might
take memory during the transaction.

The pg_dump output of the schema of that table would clear this up
probably.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpmZdA8lSp6O.pgp
Description: PGP signature

Re: [GENERAL] Multiline plpython procedure

[Marco Colombo]

On Tue, 18 Jan 2005, Tom Lane wrote:
Michael Fuhr <[EMAIL PROTECTED]> writes:
http://docs.python.org/ref/physical.html

"A physical line ends in whatever the current platform's convention
is for terminating lines.  On Unix, this is the ASCII LF (linefeed)
character.  On Windows, it is the ASCII sequence CR LF (return
followed by linefeed).  On Macintosh, it is the ASCII CR (return)
character."
Seems like Guido has missed a bet here: namely the case of a script
generated on one platform and fed to an interpreter running on another.
I think you're missing that vendors define what a 'text file' is on their
platform, not Guido. Guido just says that a Python program is a text file,
which is a very sound decision, since it makes perfectlty sense to be able
to edit it with native tools (text editors which do not support alien
textfile formats).
What you seem to be missing is that before scripts are "fed to interpreter
running on another [platform]" they need to be transferred there!
Conversion must happen (if necessary) at that point. That's why the
2000 years old protocol FTP (well, maybe not 2000 years but it _is_ old)
has an ASCII transfer mode. Is this situation unfortunate? Yes. Every
(programming) language is (or should be) affected by the same problem,
since I expect the source file being a _text_ file, everywhere.
A \n line-terminated file is not a text file under Windows, per specs.
A \r\n line-terminated is not a text file under Unix, per specs.
A \r line-terminated is not a text file neither Win or Unix, per specs.
(I'm not sure what the specs are under Mac).
Those are facts of life you have to deal with everytime you move a text
file (such as the source of a program) from platform X to platform Y.
It may affect a Cobol or Lisp or C compiler as well.
If I were designing it, I would say that any Python interpreter should
take all three variants no matter which platform the interpreter itself
is sitting on.  Or is cross-platform support not a Python goal?
Changing what a text file is under all platforms Python aims to run on
is not. (I can't speak for Guido of course, but I'm pretty sure it isn't).
I'm not against your suggestion, but that won't help with the simple fact
that text files need to be converted to what the platform they sit on
defines a text file to be. Otherwise, many other native tools fail in
treating them as text file.
In short, any bug report on this ought to go to the Python project.
Definitely not a bug report for Python. It seems to me is works as expected
(that is, as documented). The bug is on the application that transferred
the text file over the wire from platform X to platform Y.
OTOH, it's true that on the client-server "wire" no text file is tranferred,
strictly speaking. Just a string which happens to be a valid python
program. Moreover, Python is used more like an embedded scripting language
(not as a standalone programming language). So you're right when you expect
it to be more tolerant.
This is a grey area. It is pretty clear that a text file is a sequence
of lines: the separator is platform specific but the user/application
becomes aware of it only when the text file is accessed as in binary mode
(with some quirks... most native unix tool will precisely that,
think of md5sum, since there's no way to recognise a "text file").
It happens that when a text file is _correctly_ accessed, the platform
hides the separator (or should do).
For "multiline strings" (which is the right data type for a python
embedded script), everything is just worse (there's nothing even close
to a vague definition). IMHO, everytime such a string is handed to a
native tool, it should be converted to the platform specific multiline
format (that is, with the right separator). You shouldn't expect the
external tool to be able to cope with alien line formats.
Alternatively, you should _define_ what the separator is for python
embedded script in PostgreSQL, and have the interpreter accept it on
every platform unconditionally (I'm not sure whether this is easy or not).
Just my 0.03 eurocents.
.TM.
--
  /  /   /
 /  /   /   Marco Colombo
___/  ___  /   /  Technical Manager
   /  /   /  ESI s.r.l.
 _/ _/  _/ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: 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] what happened to the website?

[J. Greenlees]

Dick Davies wrote:
http://www.postgresql.org
looks bloody awful in firefox on debian, until I switch font
(on the site) from 'normal' to 'large'.
Anyone else seeing that? I'm sure it was fine a couple of weeks back.
don't know about the fonts, but 15 minutes and still trying to load it.
several other sites, fast, fully loaded in less than a second.
--

only plain text format email accepted.
smaller file size, no virus transfer
no proprietary file formats.



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] DBD::Pg behavior for large queries

[Peter Haworth]

On Tue, 18 Jan 2005 10:38:24 -0800, Chris wrote:
> The DBD::Pg man page say's this about cursors. What I don't
> understand is the nested selects being in a different transactions.
> Can someone clarify for me what is being said here? Does this
> basically mean that I can use cursors in DBD::Pg, I just can't use
> the built in DBI cursor functions? And If I'm reading this correctly
> you can't do nested selects within cursors period?
>
>   "Although PostgreSQL has a cursor concept, it has not been used in
>   the current implementation. Cursors in PostgreSQL can only be used
>   inside a transaction block.

Well, it's accurate up to this point. However, it then goes on to
explain why DBD::Pg doesn't use cursors behind the scenes to reduce
memory usage, and it starts to get misleading.

>   Because only one transaction block at a time is allowed, this
>   would have implied the restriction, not to use any nested SELECT
>   statements.

Cursors can certainly be used on queries which contain subselects,
which is what I think you're concerned about. What I think the man
page it trying to say though, is that DBD::Pg couldn't use cursors
becuase then you could only have one active query at a time. But if it
was going to use cursors, it could just use a separate cursor for each
query, so I don't understand what the problem is.

>   Hence the execute method fetches all data at once into data
>   structures located in the frontend application. This has to be
>   considered when selecting large amounts of data!"

This bit is accurate. The fetching of the entire result set is a
feature of the underlying C library, so it's a bit tricky for DBD::Pg
to work around without using cursors. However, I don't think it should
start using cursors behind the scenes, because it would make the
module much more complex and error prone. For one, there's the issue
of transactions; either al users would be forced to turn AutoCommit
off, or there would have to be different code paths for AutoCommit on
and off. Secondly, cursors are a handy thing to be able to use
explicitly, so either DBD::Pg would have to bar their use so as not to
interfere with its own usage, or it would have to examine every query
to see whether it involved cursors, and behave differently on the cursor/non-
cursor cases.

My vote would be for a documentation change which mentioned how to use
cursors explicitly to avoid excessive memory usage.

-- 
Peter Haworth   [EMAIL PROTECTED]
Q: Why do ducks have flat feet?  A: To stamp out forest fires.
Q: Why do elephants have flat feet?  A: To stamp out flaming ducks
-- UNIX fortune

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

Re: [GENERAL] Multiline plpython procedure

[Martijn van Oosterhout]

On Wed, Jan 19, 2005 at 12:20:23PM +0100, Marco Colombo wrote:
> On Tue, 18 Jan 2005, Tom Lane wrote:
> >>followed by linefeed).  On Macintosh, it is the ASCII CR (return)
> >>character."
> >
> >Seems like Guido has missed a bet here: namely the case of a script
> >generated on one platform and fed to an interpreter running on another.
> 
> I think you're missing that vendors define what a 'text file' is on their
> platform, not Guido. Guido just says that a Python program is a text file,
> which is a very sound decision, since it makes perfectlty sense to be able
> to edit it with native tools (text editors which do not support alien
> textfile formats).

Sure, some text editors don't. Some text editors do. But the C compiler
accepts programs in any of these formats. And consider multiple
machines working off the same file server. There is no "standard" text
format and everyone should just get along.

The C standard explicitly defines \r and \n as whitespace, thus neatly
avoiding the entire issue. Many other languages do the same. The fact
is the python is the odd one out.

Be liberal in what you receive. After, what's the benefit of having
python source that's not runnable on every computer. Without
conversion.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgp8FTDRGYu2a.pgp
Description: PGP signature

Re: [GENERAL] Postgres crashed when adding a sequence column

[Richard Huxton]

Clive Page wrote:
On Wed, 19 Jan 2005, Richard Huxton wrote:

Nothing wrong with what you're doing, however, you are running a
transaction that touches 142 million rows (expiring the old rows and
adding new ones). Still, unless you are particularly short of memory, or
haven't tuned PostgreSQL it should be fine.
Some questions:
1. Is the table particularly wide (i.e. number/size of columns)?
Well 28 columns, one varchar, one box, rest 4-byte real/integer.
Nothing terribly dramatic.
2. Do you have any foreign keys/triggers on the table?

No triggers, but there's one R-tree on the box, and one B-tree on an
integer column.  I guess I should have dropped those and re-created
afterwards, but the recreation takes some time, so I hoped to avoid the
need to do that.
Hmm - wonder if there might be some memory leak in updates to the R-tree 
- it gets used less than B-tree, so it could be. If you reply to this, 
make sure you mention your version of PG - one of the developers might 
know more. Probably also worth looking in the documentation for 8.0 and 
check the release notes section to see if any changes look applicable to 
your problem.

Perhaps a quick search of the -bugs mailing list archives too.
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] update in triggers

[Alban Hertroys]

Jamie Deppeler wrote:
Trigger
CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE
ON "chargeratetest" FOR EACH ROW
EXECUTE PROCEDURE "chargeratetest"();
function
CREATE OR REPLACE FUNCTION "chargeratetest" () RETURNS trigger AS'
begin
 UPDATE chargeratetest
 set notes=''hello''
 where new."primary" = chargeratetest."primary";
 return null;
end;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
If you're only going to modify the updated/inserted record, you should 
definitely take a look at RULEs (Chapter 34). They RULE for this kind of 
thing ;)

I haven't used them yet, as I only knew about triggers until recently, 
but you could do something like this:

CREATE RULE new_rule AS ON UPDATE
TO chargeratetest
DO INSTEAD
UPDATE chargeratetest
   SET notes = 'hello'
 WHERE primary = NEW.primary;
Alban.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] PostgreSQL 8.0.0 Released

[Marc G. Fournier]

After more than a year of hard work, the PostgreSQL Global Development 
Group proudly announces the release of version 8.0.0 of the PostgreSQL 
ORDBMS.  This release involves possibly more major changes than any prior 
version of PostgreSQL since 6.3, including:

Native Windows port
Savepoints/nested transactions
Exception handling inside functions
Tablespaces
Point in time recovery
Improved shared buffer management
Background writer process to reduce checkpoint impact
Tunable delays to reduce the impact of VACUUM
Overhaul of pg_dump and pg_restore
Expanded log format options and log rotation
Improved embedded quote handling for functions
ALTER TABLE can alter the data type of an existing column
Improved ALTER capabilities for all database objects
Download it now:
FTP Mirrors: http://wwwmaster.postgresql.org/ftp/latest
Windows Version: http://wwwmaster.postgresql.org/ftp/win32/
Bittorrent: http://www.postgresql.org/download/bittorrent
Sourceforge: http://sourceforge.net/projects/pgsql/
Some packages for popular Linux distributions are now available at:
 http://wwwmaster.postgresql.org/ftp/binary/v8.0/linux/
... and more will be coming soon.
Members of the press and the public unfamiliar with PostgreSQL should read 
our press release here:

http://www.postgresql.org/about/press/
Others can review the release notes for a full list of 8.0 changes, 
features, and upgrade warnings:

http://www.postgresql.org/docs/8.0/static/release.html
Download and have fun!
---(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

[GENERAL] (S)RPMs for PostgreSQL 8.0.0 are ready.

[Devrim GUNDUZ]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
- -
PostgreSQL New RPM Set
2005-01-19
Version: 8.0.0
Set labels: 8.0.0-1PGDG
- -
- -
Release Info:
PostgreSQL RPM Building Project has released RPMs for 8.0.0, and they are
available in main FTP site and its mirrors.
RPMs and SRPMs for Fedora Core {1-2-3} and Red Hat Linux 9, Red Hat
Enterprise Linux Enterprise Server 3.0 are available now, more to come 
later.

Each RPM has been signed by the builder, and each directory contains a
CURRENT_MAINTAINER file which includes the name/email of the package
builder and link to their PGP key.
If you experience problems with the RPMs or if you have a feature
requests, please join
[EMAIL PROTECTED]
More info about the list is found at:
http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general
The project page is:
http://pgfoundry.org/projects/pgsqlrpms
Please do not use these resources for issue running or using PostgreSQL
once it is installed.
Please download these files from:
http://www.postgresql.org/ftp/binary/v8.0/linux/
or from Bittorrent (Thanks to David Fetter) :
http://bt.PostgreSQL.org
Regards,
PostgreSQL RPM Build Project
- --
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFB7mHGtl86P3SPfQ4RAlaTAJsFk+Ufp27wb6jJqhyJIJ9PL5JAegCfQHvy
zBNEbDL/ljOMaJ6alIGx8Ag=
=dKY7
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] pgEdit 1.0 Released

[John DeSoi]

pgEdit 1.0 (http://pgedit.com/) is a high performance SQL editor and 
development environment for PostgreSQL. pgEdit features include SQL 
syntax coloring, direct source code execution, Unicode support, 
integrated documentation, and extensive customizable editing 
facilities. pgEdit is available for Macintosh OS X 10.3 and Windows 
NT/2000/XP.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Postgres crashed when adding a sequence column

[Clive Page]

On Wed, 19 Jan 2005, Richard Huxton wrote:

> Hmm - wonder if there might be some memory leak in updates to the R-tree
> - it gets used less than B-tree, so it could be. If you reply to this,
> make sure you mention your version of PG - one of the developers might
> know more. Probably also worth looking in the documentation for 8.0 and
> check the release notes section to see if any changes look applicable to
> your problem.

That's a possibility.  I've now dropped all indices, and am trying again.

I forgot to post the version in use, it was 7.4.1 - will try to upgrade to
v8.0 soon, as it clearly solves another problem I encountered recently.
Thanks for all the help.

-- 
Clive Page
Dept of Physics & Astronomy,
University of Leicester,Tel +44 116 252 3551
Leicester, LE1 7RH,  U.K.   Fax +44 116 252 3311


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

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

[GENERAL] Infinite recursion detected... How do I prevent that?

[Alban Hertroys]

I have a rule similar to this:
CREATE RULE rule_branch_delete AS
ON DELETE TO tree
DO DELETE
 FROM tree
WHERE ancestor_id IS NOT NULL
  AND OLD.child_id = ancestor_id;
The data is oraganized like this:
ancestor_id child_id

NULL1
1   2
1   3
1   4
1   5
1   6
1   7
1   8
2   4
2   5
2   6
3   7
3   8
etc.
This is an optimization table that relates all nodes in a branch to all 
it's ancestor nodes; to it's parent, to it's grand parent to it's grand 
grand parent, etc.

The tree is 3 to 5 levels deep. The intention is to delete all children 
of a branch that share the same ancestor when an ancestor gets deleted. 
This happens recursively, of course.

If I try a delete on the tree table I get "Infinite recursion detected 
on rules on tree". I'm pretty sure it's not "infinite" in my case, how 
can I make it delete the records regardless this "infinity"?

At the moment I use a trigger, but I would prefer a rule.
Alban.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Best Linux Distribution

[Esteban Kemp]

I'm starting to develop a production enviroment 
with Postgres and Tomcat, And I have to choose between some free linux 
distribution like:
 
whiteboxRHELFedora
Suse
 
Which is the better distribution in terms of 
postgres? if this has an answer

Re: [GENERAL] what happened to the website?

[Lonni J Friedman]

On Wed, 19 Jan 2005 03:32:05 -0800, J. Greenlees <[EMAIL PROTECTED]> wrote:
> Dick Davies wrote:
> > http://www.postgresql.org
> >
> > looks bloody awful in firefox on debian, until I switch font
> > (on the site) from 'normal' to 'large'.
> >
> > Anyone else seeing that? I'm sure it was fine a couple of weeks back.
> >
> 
> don't know about the fonts, but 15 minutes and still trying to load it.
> several other sites, fast, fully loaded in less than a second.

It got slashdotted a little while ago.


-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

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

Re: [GENERAL] Best Linux Distribution

[Devrim GUNDUZ]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Sat, 8 Jan 2005, Esteban Kemp wrote:
I'm starting to develop a production enviroment with Postgres and Tomcat, And I 
have to choose between some free linux distribution like:
whitebox
RHEL
RHEL is not free (of charge).
Fedora
Suse
SLES is again not free of charge.
Which is the better distribution in terms of postgres? if this has an answer
All have PostgreSQL included within the distribution.
IMHO, Red Hat Enterprise Linux (Or WBEL, its clone...) is the best among 
these... Red Hat also has an application server which has Tomcat 
installed, AFAIR.

Regards,
- --
Devrim GUNDUZ 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD4DBQFB7m0ltl86P3SPfQ4RAri9AKCHMJgrgb2V9+U/4KlwWPk3zHjrOQCY02WE
hiHnUsWBk/6xkOTcV0DD/Q==
=gbuj
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Retrieving a field from the NEW record

[Eric E]

Howdy Michael,
   Thanks for the help.  I 'm presently using 8.0-beta2 on Windows.  I 
will upgrade that to 8.0 release today.  However, my production server 
is 7.4.2 (on Linux), and I'm somewhat reticent to migrate to 8.0 because 
it is still pretty new.  I belive I came across a message that indicated 
that you could do such a thing in TCL.  Do you know if this is true?  
Can TCL in 7.4.2 get a field from a rowtype varaible like this?

What you outline with Perl is exactly the approach I would take, and 
that's what led to crashing the server process.  Perhaps PL/PERL is not 
totally stable on Windows yet?  If that's the case I will indeed submit 
a bug report with a test case.

Many thanks,
Eric
Michael Fuhr wrote:
On Tue, Jan 18, 2005 at 04:21:51PM -0500, Eric E wrote:
 

  I'm tearing my hair out trying to solve the following problem.  I 
want to be able to retrieve the value of the primary key in a trigger 
function.  I have tried using PL/PGSQL and PL/PERL, but a PL/PERL 
trigger function crashes the postgress process.
   

What versions of PostgreSQL and Perl are you using?  If the backend
is crashing then check to see if the bug has been fixed in a newer
version of PostgreSQL.  If not then please submit a bug report with
a repeatable test case.
I wrote a simple PL/Perl trigger in 8.0.0 and was able to access
$_TD->{new}{$pk}, where $pk held the name of a column.  Maybe the
bug has been fixed in the latest code.
 

In other words, I want:
NEW.pkName, where pkName is a variable holding the name of my primary key.
   

I'm not aware of a way to do this in a PL/pgSQL trigger.  I didn't
think EXECUTE would work and indeed it doesn't, at least not the
way I tried:
ERROR:  NEW used in query that is not in a rule
 


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

Re: [GENERAL] Postgres crashed when adding a sequence column

[Clive Page]

This is just to report success: I dropped all indices and repeated:

  UPDATE intwfs SET id = nextval('myseq');

and it worked fine - took 3681 secs (my estimate of an hour wasn't far
out).  Now doing a VACUUM FULL to remove the old tuples.


-- 
Clive Page
Dept of Physics & Astronomy,
University of Leicester,Tel +44 116 252 3551
Leicester, LE1 7RH,  U.K.   Fax +44 116 252 3311


---(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] Infinite recursion detected... How do I prevent that?

[Alban Hertroys]

Martijn van Oosterhout wrote:
Sorry, RULEs are like macros, they essentially expand and transform
your original query. This also means the expansion does not depend on
the data in your database. So postgresql continaually expands the
query, leading to your infinite recursion error.
I just found out. makes sense...
Why do you want a RULE anyway, trigger are much easier to understand.
It may be possile to setup some RULEs to avoid recursion but it won't
be easy...
Well, actually the easiest way to do this turns out to be defining my 
foreign key constraint with "on delete cascade". That does exactly what 
I intend to do, and at the right time too :)

Alban.
---(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] Best Linux Distribution

[Martijn van Oosterhout]

No difference whatsoever from PostgreSQL's point of view. Use whichever
distribution is easiest for you to administer. After all, there's no
point installing Postgres on a machine you don't know how to maintain
or tune :)

Hope this helps,

On Sat, Jan 08, 2005 at 11:14:00AM -0300, Esteban Kemp wrote:
> I'm starting to develop a production enviroment with Postgres and
> Tomcat, And I have to choose between some free linux distribution
> like:
> 
> whitebox
> RHEL
> Fedora
> Suse
> 
> Which is the better distribution in terms of postgres? if this has an answer
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpytXyBWktrd.pgp
Description: PGP signature

[GENERAL] Unique Index

[Alex]

Hi,
i have a unique index on a table over multiple columns. If now one of 
the records has a null value in one of the indexed columns i can insert 
the same record multiple times.

Is this a problem within postgres or expected?
Example:
index unique, btree (colA, colB, colC);
would still allow me to insert
, ,
, ,
Thanks for any help on that.
A

---(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] Infinite recursion detected... How do I prevent that?

[Martijn van Oosterhout]

On Wed, Jan 19, 2005 at 02:57:47PM +0100, Alban Hertroys wrote:
> I have a rule similar to this:
> 
> CREATE RULE rule_branch_delete AS
> ON DELETE TO tree
> DO DELETE
>  FROM tree
> WHERE ancestor_id IS NOT NULL
>   AND OLD.child_id = ancestor_id;



> If I try a delete on the tree table I get "Infinite recursion detected 
> on rules on tree". I'm pretty sure it's not "infinite" in my case, how 
> can I make it delete the records regardless this "infinity"?
> 
> At the moment I use a trigger, but I would prefer a rule.

Sorry, RULEs are like macros, they essentially expand and transform
your original query. This also means the expansion does not depend on
the data in your database. So postgresql continaually expands the
query, leading to your infinite recursion error.

Why do you want a RULE anyway, trigger are much easier to understand.
It may be possile to setup some RULEs to avoid recursion but it won't
be easy...

Hope this helps,

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpOkrTnLjpgs.pgp
Description: PGP signature

Re: [GENERAL] PostgreSQL 8.0.0 Released

[Robby Russell]

On Wed, 2005-01-19 at 09:02 -0400, Marc G. Fournier wrote:
> After more than a year of hard work, the PostgreSQL Global Development 
> Group proudly announces the release of version 8.0.0 of the PostgreSQL 
> ORDBMS.  This release involves possibly more major changes than any prior 
> version of PostgreSQL since 6.3, including:

Good work everyone!

:-)

-- 
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | [EMAIL PROTECTED]
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now hosting PostgreSQL 8.0! ---
/


---(end of broadcast)---
TIP 3: 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

[GENERAL] Need help recovering

[Michael Garriss]

We had a significant production outage with a box running 8.0 Beta 4, 
140GB data, 190GB index.  We think it was a bad RAID controller card.  
Our transaction logs are gone but we have raw data.

How can we recover this data?  Can the transaction logs be reset?  Can 
we safely set this zero_damaged_pages parameter?

Ahh!!
--
Michael Garriss
Senior Software Engineer
Absolute Performance, Inc.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Unique Index

[Stephan Szabo]

On Thu, 20 Jan 2005, Alex wrote:

> i have a unique index on a table over multiple columns. If now one of
> the records has a null value in one of the indexed columns i can insert
> the same record multiple times.
>
> Is this a problem within postgres or expected?

Expected. NULLs are effectively not considered as the same for the
purposes of UNIQUE.

The predicate basically functions as:

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"", then the result of the  is true; otherwise, the result of the 
is false.

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

Re: [GENERAL] Infinite recursion detected... How do I prevent that?

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-01-19 14:57:47 +0100:
> I have a rule similar to this:
> 
> CREATE RULE rule_branch_delete AS
> ON DELETE TO tree
> DO DELETE
>  FROM tree
> WHERE ancestor_id IS NOT NULL
>   AND OLD.child_id = ancestor_id;

> If I try a delete on the tree table I get "Infinite recursion detected 
> on rules on tree". I'm pretty sure it's not "infinite" in my case, how 
> can I make it delete the records regardless this "infinity"?

cover the table with a view, as in:

CREATE TABLE _tree (
  ancestor_id int,
  child_id int
);

CREATE VIEW tree AS
  SELECT * FROM _tree;

CREATE RULE rule_branch_delete AS
ON DELETE TO tree
DO INSTEAD (
  DELETE FROM _tree ...; (the original DELETE redirected to _tree)
  DELETE FROM _tree
WHERE ancestor_id IS NOT NULL
  AND OLD.child_id = ancestor_id;
);


-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

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

[GENERAL] stored procedure from oracle to pgsql

[Nefnifi, Kasem]

Dear,
I'm new in pgsql, come from oracle and sql server.
any one can help by transferring a pl/sql procedure that imports data from a 
flat file, using UTL_FILE, and inserts or updates tables.
bellow the proc:

CREATE OR REPLACE Procedure UPD is
sOracleMsg  varchar2(512);
sCustomerMsgvarchar2(100);
fHandle UTL_FILE.FILE_Type;
sPath   varchar2(200)   := '/home';
sfile   varchar2(30):= 'prod_2903.txt';
sBuffer varchar2(4000);
contractvertraege."Vertrags_Nr"%TYPE;
phonevertraege."Phone"%TYPE;
status   vertraege."Status"%TYPE;
   -- Declare program variables as shown above
BEGIN
fHandle := UTL_FILE.FOPEN(sPath, '/'||sfile, 'r');
IF UTL_FILE.Is_OPEN(fHandle) THEN
loop
begin
UTL_FILE.GET_LINE(fHandle, sBuffer);
phone := substr(sBuffer,1,9);
status := upper(substr(sBuffer,10,1));
contract := substr(sBuffer,10,7);
update vertraege vr
set vr.phone = phone
where vr.vertrags_nr = contract;
commit;
 exception
when no_data_found then
insert into vertaraege (phone, vertrags_nr)
  values (phone, contract);
  commit;
when others then
raise;
end;
end loop;
ELSE
raise_application_error(-20001,'can not open file' || sfile || '. or 
file not exist');
END IF;
COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
rollback;
raise_application_error(-20002, 'can not open path:' || sPath);
END; -- Procedure

Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche 
Grüße !!! 
Kasem NEFNIFI 
AtosOrigin Belgium N.V. 
Minervastraat  7 
1930 Zaventem (Belgium) 
Tel  : +32(0)2 712 28 30 
Fax : +32(0)2 712 28 63 
GSM   : +32 495 25 12 33 
Email : [EMAIL PROTECTED]   
www.atosorigin.com   

Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.


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

Re: [GENERAL] Best Linux Distribution

[Geoffrey]

Devrim GUNDUZ wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Sat, 8 Jan 2005, Esteban Kemp wrote:
I'm starting to develop a production enviroment with Postgres and 
Tomcat, And I have to choose between some free linux distribution like:

whitebox
RHEL

RHEL is not free (of charge).
Fedora
Suse

SLES is again not free of charge.
You can download a variation of SuSE 9.2 pro now.  I say a variation 
because it's a dvd iso which is around 4g, whereas the dvd that comes 
with the boxed 9.2 pro is a dual-layer and +7g.

I don't know what the differences are between the two.
--
Until later, Geoffrey
---(end of broadcast)---
TIP 3: 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] what happened to the website?

[Dick Davies]

* Omar Kilani <[EMAIL PROTECTED]> [0146 15:46]:
> Hi,
> 
> If your font issues are still occuring after doing a Ctrl-Reload (and 
> switching back to 'Normal' text), then, can you please send me a screenshot?

Sorted now, thanks. Didn't need to shift-relod, it just worked...

-- 
'zzz..Kill all humans. Kill all humans..zzz .
I was having the most wonderous dream. You were in it.'
-- Bender
Rasputin :: Jack of All Trades - Master of Nuns

---(end of broadcast)---
TIP 3: 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] PostgreSQL 8.0.0 Released

[Együd Csaba (Freemail)]

Great! Thank you guys.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier
Sent: Wednesday, January 19, 2005 2:03 PM
To: pgsql-announce@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL 8.0.0 Released


After more than a year of hard work, the PostgreSQL Global Development Group
proudly announces the release of version 8.0.0 of the PostgreSQL ORDBMS.
This release involves possibly more major changes than any prior version of
PostgreSQL since 6.3, including:

Native Windows port
Savepoints/nested transactions
Exception handling inside functions
Tablespaces
Point in time recovery
Improved shared buffer management
Background writer process to reduce checkpoint impact Tunable delays to
reduce the impact of VACUUM Overhaul of pg_dump and pg_restore Expanded log
format options and log rotation Improved embedded quote handling for
functions ALTER TABLE can alter the data type of an existing column Improved
ALTER capabilities for all database objects

Download it now:
FTP Mirrors: http://wwwmaster.postgresql.org/ftp/latest
Windows Version: http://wwwmaster.postgresql.org/ftp/win32/
Bittorrent: http://www.postgresql.org/download/bittorrent
Sourceforge: http://sourceforge.net/projects/pgsql/

Some packages for popular Linux distributions are now available at:

  http://wwwmaster.postgresql.org/ftp/binary/v8.0/linux/

... and more will be coming soon.

Members of the press and the public unfamiliar with PostgreSQL should read
our press release here:

http://www.postgresql.org/about/press/

Others can review the release notes for a full list of 8.0 changes,
features, and upgrade warnings:

http://www.postgresql.org/docs/8.0/static/release.html

Download and have fun!


---(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


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.
 



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 2005.01.17.


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

Re: [GENERAL] Unique Index

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100:
> i have a unique index on a table over multiple columns. If now one of 
> the records has a null value in one of the indexed columns i can insert 
> the same record multiple times.
> 
> Is this a problem within postgres or expected?

In SQL, NULL means "unknown value". How could you assert that two
NULLs are equal?

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

---(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] Retrieving a field from the NEW record

[Michael Fuhr]

On Wed, Jan 19, 2005 at 09:26:11AM -0500, Eric E wrote:

> I belive I came across a message that indicated that you could do
> such a thing in TCL.  Do you know if this is true?  Can TCL in 7.4.2
> get a field from a rowtype varaible like this?

See the PL/Tcl trigger documentation -- it has an example of accessing
columns whose names are in variables.

> What you outline with Perl is exactly the approach I would take, and 
> that's what led to crashing the server process.  Perhaps PL/PERL is not 
> totally stable on Windows yet?  If that's the case I will indeed submit 
> a bug report with a test case.

Please do, but try 8.0.0 first.  I don't know how stable PL/Perl
is on Windows, but there have been bug fixes in PL/Perl since the
8.0.0beta2 version you said you were running.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(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] Best Linux Distribution

[Bruno Almeida do Lago]

You should look www.linuxiso.org.
There you may find the ISO of a great variety of distros.


C ya,
Bruno Almeida do Lago
 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Geoffrey
Sent: Wednesday, January 19, 2005 1:01 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Best Linux Distribution

Devrim GUNDUZ wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> 
> Hi,
> 
> On Sat, 8 Jan 2005, Esteban Kemp wrote:
> 
>> I'm starting to develop a production enviroment with Postgres and 
>> Tomcat, And I have to choose between some free linux distribution like:
>>
>> whitebox
>> RHEL
> 
> 
> RHEL is not free (of charge).
> 
>> Fedora
>> Suse
> 
> 
> SLES is again not free of charge.

You can download a variation of SuSE 9.2 pro now.  I say a variation 
because it's a dvd iso which is around 4g, whereas the dvd that comes 
with the boxed 9.2 pro is a dual-layer and +7g.

I don't know what the differences are between the two.

-- 
Until later, Geoffrey

---(end of broadcast)---
TIP 3: 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


---(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] Best Linux Distribution

[Joshua D. Drake]

Martijn van Oosterhout wrote:
No difference whatsoever from PostgreSQL's point of view. Use whichever
distribution is easiest for you to administer. After all, there's no
point installing Postgres on a machine you don't know how to maintain
or tune :)
 

Actually there is a difference from PostgreSQL's point of view :)
Namely in filesystems. The default filesystem on whitebox, RHEL and
Fedora is EXT3 which really isn't that great.
Sincerely,
Joshua D. Drake

Hope this helps,
On Sat, Jan 08, 2005 at 11:14:00AM -0300, Esteban Kemp wrote:
 

I'm starting to develop a production enviroment with Postgres and
Tomcat, And I have to choose between some free linux distribution
like:
whitebox
RHEL
Fedora
Suse
Which is the better distribution in terms of postgres? if this has an answer
   


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: 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] what happened to the website?

[Richard_D_Levine]

What does that mean?



 
  Lonni J Friedman  
 
  <[EMAIL PROTECTED]>   To:   
pgsql-general@postgresql.org  
  Sent by:   cc:
 
  [EMAIL PROTECTED]Subject:  Re: [GENERAL] what 
happened to the website?   
  tgresql.org   
 

 

 
  01/19/2005 09:10 AM   
 
  Please respond to Lonni   
 
  J Friedman
 

 

 




On Wed, 19 Jan 2005 03:32:05 -0800, J. Greenlees <[EMAIL PROTECTED]> wrote:
> Dick Davies wrote:
> > http://www.postgresql.org
> >
> > looks bloody awful in firefox on debian, until I switch font
> > (on the site) from 'normal' to 'large'.
> >
> > Anyone else seeing that? I'm sure it was fine a couple of weeks back.
> >
>
> don't know about the fonts, but 15 minutes and still trying to load it.
> several other sites, fast, fully loaded in less than a second.

It got slashdotted a little while ago.


--
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

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




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

   http://archives.postgresql.org

Re: [GENERAL] what happened to the website?

[Lonni J Friedman]

http://it.slashdot.org/article.pl?sid=05/01/19/1312224&tid=221&tid=198&tid=185


On Wed, 19 Jan 2005 11:06:56 -0500, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> What does that mean?
> 
>   Lonni J Friedman
>   <[EMAIL PROTECTED]>   To:   
> pgsql-general@postgresql.org
>   Sent by:   cc:
>   [EMAIL PROTECTED]Subject:  Re: [GENERAL] what 
> happened to the website?
>   tgresql.org
> 
>   01/19/2005 09:10 AM
>   Please respond to Lonni
>   J Friedman
> 
> 
> On Wed, 19 Jan 2005 03:32:05 -0800, J. Greenlees <[EMAIL PROTECTED]> wrote:
> > Dick Davies wrote:
> > > http://www.postgresql.org
> > >
> > > looks bloody awful in firefox on debian, until I switch font
> > > (on the site) from 'normal' to 'large'.
> > >
> > > Anyone else seeing that? I'm sure it was fine a couple of weeks back.
> > >
> >
> > don't know about the fonts, but 15 minutes and still trying to load it.
> > several other sites, fast, fully loaded in less than a second.
> 
> It got slashdotted a little while ago.

-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

---(end of broadcast)---
TIP 3: 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] Best Linux Distribution

[Bricklen Anderson]

Joshua D. Drake wrote:
Martijn van Oosterhout wrote:
No difference whatsoever from PostgreSQL's point of view. Use whichever
distribution is easiest for you to administer. After all, there's no
point installing Postgres on a machine you don't know how to maintain
or tune :)
 

Actually there is a difference from PostgreSQL's point of view :)
Namely in filesystems. The default filesystem on whitebox, RHEL and
Fedora is EXT3 which really isn't that great.
Sincerely,
Joshua D. Drake
Out of curiousity, which fs would you recommend for a ~terabyte oltp db?
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(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] stored procedure from oracle to pgsql

[Joshua D. Drake]

Nefnifi, Kasem wrote:
Dear,
I'm new in pgsql, come from oracle and sql server.
any one can help by transferring a pl/sql procedure that imports data from a flat file, using UTL_FILE, and inserts or updates tables.
bellow the proc:
 

You can't do this from pl/PgSQL. You will need to use plPerl,
plPython, PlPHP or the like.
Sincerely,
Joshua D. Drake
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


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

Re: [GENERAL] ECPG Segfaulting on EXEC SQL connect

[Hans-Michael Stahl]

Michael Meskes wrote the following on 19.01.2005 11:16:

I would be happy to help debugging this. But up to now I didn't even
know about this problem. Could you please send me a test case so I can
reproduce this? 
It is from a very large production example. I have to work a bit to trim 
this down to a small example.  I'llt try to provide an example. I now 
also remember that the problem *only* occurs with dynamically prepared 
statements with cursors, static statements with cursors work w/o problems.

Also did you try using 8.0?
No, When I encountered the problem I switched back to Informix.
> Go SF 49ers!
What's that?
> Go Rhein Fire!
And that?
> Use Debian GNU/Linux!
I Use Gentoo and SuSE.
> Use PostgreSQL!
Yes, when Embedded SQL works again :-)

--
With kind regards | Mit freundlichen Gruessen
Hans-Michael Stahl
Condat AG
Alt-Moabit 91d | 10559 Berlin | Germany | Old Europe
tel: +49.30.3949-1155 | fax: +49.30.3949-2221155
http://www.condat.de/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Best Linux Distribution

[Ron Mayer]

When I had customers faced with this decision, we made the
recommendation based on which distro employs major contributors
of the software project in question.
For Postgresql's case, RedHat's employment of Tom made
our recommendation to use Red Hat.
Some of our clients are running .NET front ends, so we're
recommending Novel/SuSE for those.
It's a mix of superstition that the vendors platform is
may see earlier testing, along with rewarding the vendor
for supporting the project.
   Ron
PS: All you open source vendors who employ important
developers -- Thank You - this contribution does not go unnoticed.

Martijn van Oosterhout wrote:
No difference whatsoever from PostgreSQL's point of view. Use whichever
distribution is easiest for you to administer. After all, there's no
point installing Postgres on a machine you don't know how to maintain
or tune :)
Hope this helps,
On Sat, Jan 08, 2005 at 11:14:00AM -0300, Esteban Kemp wrote:
I'm starting to develop a production enviroment with Postgres and
Tomcat, And I have to choose between some free linux distribution
like:
whitebox
RHEL
Fedora
Suse
Which is the better distribution in terms of postgres? if this has an answer
---(end of broadcast)---
TIP 3: 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] Best Linux Distribution

[Lonni J Friedman]

On Wed, 19 Jan 2005 09:03:31 -0800, Joshua D. Drake
<[EMAIL PROTECTED]> wrote:
> Martijn van Oosterhout wrote:
> 
> >No difference whatsoever from PostgreSQL's point of view. Use whichever
> >distribution is easiest for you to administer. After all, there's no
> >point installing Postgres on a machine you don't know how to maintain
> >or tune :)
> >
> >
> Actually there is a difference from PostgreSQL's point of view :)
> Namely in filesystems. The default filesystem on whitebox, RHEL and
> Fedora is EXT3 which really isn't that great.

On whitebox & RHEL ext3 is really the only choice.  However, FC3
provides all the other major filesystems as choices (XFS, reiser).

-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

---(end of broadcast)---
TIP 3: 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] Best Linux Distribution

[Joshua D. Drake]

Sincerely,
Joshua D. Drake
 

Out of curiousity, which fs would you recommend for a ~terabyte oltp db?
   

XFS without a doubt.  XFS has excellent large file (and filesystem) support.
 

I second the XFS statement.
Sincerely,
Joshua D. Drake

 


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


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

Re: [GENERAL] what happened to the website?

[Scott Marlowe]

http://en.wikipedia.org/wiki/Slashdot_effect


On Wed, 2005-01-19 at 10:06, [EMAIL PROTECTED] wrote:
> What does that mean?
> 
> 
>   
>
>   Lonni J Friedman
>
>   <[EMAIL PROTECTED]>   To:   
> pgsql-general@postgresql.org  
>   Sent by:   cc:  
>
>   [EMAIL PROTECTED]Subject:  Re: [GENERAL] what 
> happened to the website?   
>   tgresql.org 
>
>   
>
>   
>
>   01/19/2005 09:10 AM 
>
>   Please respond to Lonni 
>
>   J Friedman  
>
>   
>
>   
>
> 
> 
> 
> 
> On Wed, 19 Jan 2005 03:32:05 -0800, J. Greenlees <[EMAIL PROTECTED]> wrote:
> > Dick Davies wrote:
> > > http://www.postgresql.org
> > >
> > > looks bloody awful in firefox on debian, until I switch font
> > > (on the site) from 'normal' to 'large'.
> > >
> > > Anyone else seeing that? I'm sure it was fine a couple of weeks back.
> > >
> >
> > don't know about the fonts, but 15 minutes and still trying to load it.
> > several other sites, fast, fully loaded in less than a second.
> 
> It got slashdotted a little while ago.
> 
> 
> --
> ~
> L. Friedman[EMAIL PROTECTED]
> LlamaLand   http://netllama.linux-sxs.org
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org

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

   http://archives.postgresql.org

Re: [GENERAL] what happened to the website?

[Bruno Wolff III]

On Wed, Jan 19, 2005 at 11:06:56 -0500,
  [EMAIL PROTECTED] wrote:
> What does that mean?

http://en.wikipedia.org/wiki/Slashdot_effect

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

Re: [GENERAL] Best Linux Distribution

[Lonni J Friedman]

On Wed, 19 Jan 2005 09:19:33 -0800, Bricklen Anderson
<[EMAIL PROTECTED]> wrote:
> Joshua D. Drake wrote:
> > Martijn van Oosterhout wrote:
> >
> >> No difference whatsoever from PostgreSQL's point of view. Use whichever
> >> distribution is easiest for you to administer. After all, there's no
> >> point installing Postgres on a machine you don't know how to maintain
> >> or tune :)
> >>
> >>
> > Actually there is a difference from PostgreSQL's point of view :)
> > Namely in filesystems. The default filesystem on whitebox, RHEL and
> > Fedora is EXT3 which really isn't that great.
> > 
> > Sincerely,
> >
> > Joshua D. Drake
> 
> Out of curiousity, which fs would you recommend for a ~terabyte oltp db?

XFS without a doubt.  XFS has excellent large file (and filesystem) support.


-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

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

   http://archives.postgresql.org

Re: [GENERAL] what happened to the website?

[Randal L. Schwartz]

> "Bruno" == Bruno Wolff <[EMAIL PROTECTED]> writes:

Bruno> http://en.wikipedia.org/wiki/Slashdot_effect

Except that wikipedia itself is suffering from the Slashdot effect
right now. :)

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

---(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] what happened to the website?

[Magnus Hagander]

The website went down long before the news was posted to slashdot. By
the time it hit slashdot, things were fixed already. Luckily.

//Magnus

> http://en.wikipedia.org/wiki/Slashdot_effect
> 
> 
> On Wed, 2005-01-19 at 10:06, [EMAIL PROTECTED] wrote:
> > What does that mean?
> > 
> > 
> > 
>   
>
> >   Lonni J Friedman  
>   
>
> >   <[EMAIL PROTECTED]>   To:
>pgsql-general@postgresql.org   
>
> >   Sent by:   cc:
>   
>
> >   [EMAIL PROTECTED]
> Subject:  Re: [GENERAL] what happened to the website? 
>   
> >   tgresql.org   
>   
>
> > 
>   
>
> > 
>   
>
> >   01/19/2005 09:10 AM   
>   
>
> >   Please respond to Lonni   
>   
>
> >   J Friedman
>   
>
> > 
>   
>
> > 
>   
>
> > 
> > 
> > 
> > 
> > On Wed, 19 Jan 2005 03:32:05 -0800, J. Greenlees 
> <[EMAIL PROTECTED]> wrote:
> > > Dick Davies wrote:
> > > > http://www.postgresql.org
> > > >
> > > > looks bloody awful in firefox on debian, until I switch 
> font (on 
> > > > the site) from 'normal' to 'large'.
> > > >
> > > > Anyone else seeing that? I'm sure it was fine a couple 
> of weeks back.
> > > >
> > >
> > > don't know about the fonts, but 15 minutes and still 
> trying to load it.
> > > several other sites, fast, fully loaded in less than a second.
> > 
> > It got slashdotted a little while ago.
> > 
> > 
> > --
> > 
> ~
> > L. Friedman[EMAIL PROTECTED]
> > LlamaLand   http://netllama.linux-sxs.org
> > 
> > ---(end of 
> > broadcast)---
> > TIP 1: subscribe and unsubscribe commands go to 
> > [EMAIL PROTECTED]
> > 
> > 
> > 
> > 
> > ---(end of 
> > broadcast)---
> > TIP 6: Have you searched our list archives?
> > 
> >http://archives.postgresql.org
> 
> ---(end of 
> broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

---(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

[GENERAL] pg 8.0 on freebsd 5.3 install problem

[pginfo]

Hi all,
I just installed pg 8.0 on freeBSD 5.3.
All  is ok, but if I try to increase max_connections to 256 (or more) pg 
do not starts.

Yes, I readet the docs and I know that I need to increase shared memory 
and semaphores.

I recompile my kernel and set :
options SYSVSHM
options SHMMAXPGS=16382
options SHMSEG=9182
options SYSVSEM
options SEMMNI=9182
options SEMMNS=9182
options SEMMNU=9182
options SEMMAP=9182
And also after installing the new kernel I got the same error.
Pls., help me to find the values of  semaphors in my kernel.
Exist any tools for this info?
many thanks,
ivan.

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

[GENERAL] Strange results of ORDER BY clause when item begins with slash or backslash

[SCassidy]

I am seeing some unexpected results for an ORDER BY in a query.   It looks
to me as if the sorting is confused about how to handle the slash or
backslash character in a string.  It acts as if ignoring it. Here is a
sample:


  Table "public.test_table"
 Column  |  Type  | Modifiers
-++---
 item1   | character varying(500) |
 numitem | integer|


testdb1=> select * from test_table order by upper(item1);
  item1  | numitem
-+-
 a   | 123
 b   | 234
 test| 666
 \test\item1 | 555
 total info  | 876
 userdir example | 787
 /usr/otherdir   | 999
 variation   | 777
 \var\overland   | 444
 /var/somedir| 888
(10 rows)

testdb1=> show LC_COLLATE;
 lc_collate
-
 en_US.UTF-8
(1 row)

I would have expected all the items beginning with a backslash to sort
together, and not be interspersed like this.  Can anyone advise on how I
can avoid this?   I would just reorder the data myself, but I am using
OFFSET and LIMIT, and think this will cause problems if the database does
not handle the sorting.

The backslashes were all properly escaped before insertion.

Any ideas appreciated.

Thanks,
Susan C.





--
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
--


---(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] Best Linux Distribution

[Guy Rouillier]

Lonni J Friedman wrote:
> 
> On whitebox & RHEL ext3 is really the only choice.  However, FC3
> provides all the other major filesystems as choices (XFS, reiser). 

I just tried to install FC3 AMD64, and the only choice it would give me
for an installation was ext3.  Since I prefer Reiser, I gave up and
installed Gentoo.

-- 
Guy Rouillier


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

Re: [GENERAL] PostgreSQL 8.0.0 Released

[A. Mous]

Hooray!  Thank you to all responsible for and involved in this great
release!  This database truly rocks!

:)




-Original Message-
From: Marc G. Fournier [mailto:[EMAIL PROTECTED] 
Sent: January 19, 2005 6:03 AM
To: pgsql-announce@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: PostgreSQL 8.0.0 Released


After more than a year of hard work, the PostgreSQL Global Development 
Group proudly announces the release of version 8.0.0 of the PostgreSQL 
ORDBMS.  This release involves possibly more major changes than any prior 
version of PostgreSQL since 6.3, including:

Native Windows port
Savepoints/nested transactions
Exception handling inside functions
Tablespaces
Point in time recovery
Improved shared buffer management
Background writer process to reduce checkpoint impact
Tunable delays to reduce the impact of VACUUM
Overhaul of pg_dump and pg_restore
Expanded log format options and log rotation
Improved embedded quote handling for functions
ALTER TABLE can alter the data type of an existing column
Improved ALTER capabilities for all database objects

Download it now:
FTP Mirrors: http://wwwmaster.postgresql.org/ftp/latest
Windows Version: http://wwwmaster.postgresql.org/ftp/win32/
Bittorrent: http://www.postgresql.org/download/bittorrent
Sourceforge: http://sourceforge.net/projects/pgsql/

Some packages for popular Linux distributions are now available at:

  http://wwwmaster.postgresql.org/ftp/binary/v8.0/linux/

... and more will be coming soon.

Members of the press and the public unfamiliar with PostgreSQL should read 
our press release here:

http://www.postgresql.org/about/press/

Others can review the release notes for a full list of 8.0 changes, 
features, and upgrade warnings:

http://www.postgresql.org/docs/8.0/static/release.html

Download and have fun!



---(end of broadcast)---
TIP 3: 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] Best Linux Distribution

[Joshua D. Drake]

Guy Rouillier wrote:
Lonni J Friedman wrote:
 

On whitebox & RHEL ext3 is really the only choice.  However, FC3
provides all the other major filesystems as choices (XFS, reiser). 
   

I just tried to install FC3 AMD64, and the only choice it would give me
for an installation was ext3.  Since I prefer Reiser, I gave up and
 

I am running FC3 with XFS :)
installed Gentoo.'
 




--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


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

Re: [GENERAL] Best Linux Distribution

[Devrim GUNDUZ]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
On Wed, 19 Jan 2005, Guy Rouillier wrote:
On whitebox & RHEL ext3 is really the only choice.  However, FC3
provides all the other major filesystems as choices (XFS, reiser).
I just tried to install FC3 AMD64, and the only choice it would give me
for an installation was ext3.  Since I prefer Reiser, I gave up and
installed Gentoo.
AFAIR, if you run anaconda with
linux reiserfs
then reiserfs will appear among available file lists.
Regards,
- --
Devrim GUNDUZ 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFB7rdutl86P3SPfQ4RAkV1AJ46FLnDuLRDYvNQj/OGC8ck1JpobQCbBih+
+/gKEgPZFRBfMAJPP5uWbVM=
=NwkB
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Debian Sarge, Postgres 7.4.6 + PAM

[Bruno Lavoie]

Hello, 

is there a way to easily configure postgresql to auth with pam on debian? 

thanx a lot!
Bruno

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

Re: [GENERAL] Best Linux Distribution

[Lonni J Friedman]

On Wed, 19 Jan 2005 21:39:23 +0200 (EET), Devrim GUNDUZ
<[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Hi,
> 
> On Wed, 19 Jan 2005, Guy Rouillier wrote:
> 
> >> On whitebox & RHEL ext3 is really the only choice.  However, FC3
> >> provides all the other major filesystems as choices (XFS, reiser).
> >
> > I just tried to install FC3 AMD64, and the only choice it would give me
> > for an installation was ext3.  Since I prefer Reiser, I gave up and
> > installed Gentoo.
> 
> AFAIR, if you run anaconda with
> 
> linux reiserfs
> 
> then reiserfs will appear among available file lists.

That is correct.  It works similarly for xfs.


-- 
~
L. Friedman[EMAIL PROTECTED]
LlamaLand   http://netllama.linux-sxs.org

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

Re: [GENERAL] Debian Sarge, Postgres 7.4.6 + PAM

[Peter Eisentraut]

Bruno Lavoie wrote:
> is there a way to easily configure postgresql to auth with pam on
> debian?

Yes, the same way as on any other platform.  What particular problem are 
you having?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(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

[GENERAL] need an advice on running Database

[Mark]

Hi,
I have a small data base ~ 10 tables. each table get
insert/update/delete few times a day. postgresql is running for a
month.

The load will increase in the near future: insert/update/delete
activity will be at least one in 5 minutes.

What maintenance should I need to do?

Thanks,
Mark.



__ 
Do you Yahoo!? 
All your favorites on one personal page – Try My Yahoo!
http://my.yahoo.com 

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

Re: [GENERAL] Need help recovering

[Alvaro Herrera]

On Wed, Jan 19, 2005 at 07:39:29AM -0700, Michael Garriss wrote:
> We had a significant production outage with a box running 8.0 Beta 4, 
> 140GB data, 190GB index.  We think it was a bad RAID controller card.  
> Our transaction logs are gone but we have raw data.
> 
> How can we recover this data?  Can the transaction logs be reset?  Can 
> we safely set this zero_damaged_pages parameter?

What transaction logs?  Do you mean pg_clog, or pg_xlog?  If the former,
use your latest backup (i.e. forget it, unless you rarely rolled back
any transaction).  If the latter, try pg_resetxlog (you will likely lose
some things after the last checkpoint, and some indexes may not be in
correct shape).

What are the exact error messages you are getting?

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"The ability to monopolize a planet is insignificant
next to the power of the source"

---(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] pg 8.0 on freebsd 5.3 install problem

[Chris]

On Wed, 19 Jan 2005 18:51:40 +0100, pginfo <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> I just installed pg 8.0 on freeBSD 5.3.
> 
> All  is ok, but if I try to increase max_connections to 256 (or more) pg
> do not starts.
> 
> Yes, I readet the docs and I know that I need to increase shared memory
> and semaphores.
> 
> I recompile my kernel and set :
> options SYSVSHM
> options SHMMAXPGS=16382
> options SHMSEG=9182
> 
> options SYSVSEM
> options SEMMNI=9182
> options SEMMNS=9182
> options SEMMNU=9182
> options SEMMAP=9182
> 
> And also after installing the new kernel I got the same error.
> 
The following works for me on 5.3

options SHMMAXPGS=131072
options SEMMNI=128
options SEMMNS=512
options SEMUME=100
options SEMMNU=256


Chris

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

Re: [GENERAL] Strange results of ORDER BY clause when item begins

[Ragnar Hafstað]

On Wed, 2005-01-19 at 11:24 -0800, [EMAIL PROTECTED] wrote:
> [non-word character being ignored by ORDER BY]
...
> testdb1=> show LC_COLLATE;
>  lc_collate
> -
>  en_US.UTF-8
> (1 row)

this is a 'feature' of your en_US locale:

bash$ export LC_COLLATE=en_US
bash$ (echo "usra";echo "usrq";echo "/usr/lib")| sort
usra
/usr/lib
usrq
bash$ export LC_COLLATE=C
bash$ (echo "usra";echo "usrq";echo "/usr/lib")| sort
/usr/lib
usra
usrq

maybe you should have run initdb with LC_COLLATE=C

gnari



---(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] need an advice on running Database

[Dann Corbit]

Once per day dump database to disk.
Once per day do a vacuum full.
That should be plenty.

Since there are 1440 minutes per day, you are only looking at 288
transactions per day.  Not exactly a taxing transaction load.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark
Sent: Wednesday, January 19, 2005 12:41 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] need an advice on running Database

Hi,
I have a small data base ~ 10 tables. each table get
insert/update/delete few times a day. postgresql is running for a
month.

The load will increase in the near future: insert/update/delete
activity will be at least one in 5 minutes.

What maintenance should I need to do?

Thanks,
Mark.



__ 
Do you Yahoo!? 
All your favorites on one personal page - Try My Yahoo!
http://my.yahoo.com 

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

---(end of broadcast)---
TIP 3: 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] need an advice on running Database

[Richard_D_Levine]

Do you not recommend autovacuum?

Rick



 
  "Dann Corbit" 
 
  <[EMAIL PROTECTED]>To:   "Mark" <[EMAIL 
PROTECTED]>,  
  Sent by:   cc:
 
  [EMAIL PROTECTED]Subject:  Re: [GENERAL] need an 
advice on running Database  
  tgresql.org   
 

 

 
  01/19/2005 04:30 PM   
 

 

 




Once per day dump database to disk.
Once per day do a vacuum full.
That should be plenty.

Since there are 1440 minutes per day, you are only looking at 288
transactions per day.  Not exactly a taxing transaction load.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark
Sent: Wednesday, January 19, 2005 12:41 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] need an advice on running Database

Hi,
I have a small data base ~ 10 tables. each table get
insert/update/delete few times a day. postgresql is running for a
month.

The load will increase in the near future: insert/update/delete
activity will be at least one in 5 minutes.

What maintenance should I need to do?

Thanks,
Mark.



__
Do you Yahoo!?
All your favorites on one personal page - Try My Yahoo!
http://my.yahoo.com

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

---(end of broadcast)---
TIP 3: 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




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

[GENERAL] PL/TCL functions won't run

[Eric E]

Hi all,
   In working through some problems with rows and variables, I 
installed PL/TCL into my Postgres 8.0 installation on Windows XP.  
Unfortunately, when I try to run any PL/TCL functions, I get the response:
  
ERROR:  could not create "normal" interpreter

I have ActiveTCL 8.4.9 installed, and createlang succeeded.  Does anyone 
have any idea what needs to be done to get PL/TCL to run on my installation?

Thanks,
Eric
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] need an advice on running Database

[Karsten Hilbert]

> The load will increase in the near future: insert/update/delete
> activity will be at least one in 5 minutes.
> 
> What maintenance should I need to do?
You should make sure PostgreSQL is properly tuned for your
hardware and you should run vacuum, say, daily or so.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(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

[GENERAL] sorting library of congress numbers

[Rick Schumeyer]

I have a table where one of the columns is a library of
congress number.

I would like to be able to ORDER BY this column.

 

Currently, the column is a varchar.  Unfortunately, a
normal string comparison

will not work.

 

First, has anyone done this already?

 

If not, I’m thinking I would need to:

1)  
define a new type to hold LOC numbers.

2)  
write a function that determines less than, greater
than

 

It seems like I’m missing something here.  I have
not yet defined a new

type…is defining less/greater part of that process?

 

Is there a simpler way to do this?

 

Any advice is appreciated.

 

Re: [GENERAL] sorting library of congress numbers

[Scott Marlowe]

On Wed, 2005-01-19 at 16:37, Rick Schumeyer wrote:
> I have a table where one of the columns is a library of congress
> number.
> 
> I would like to be able to ORDER BY this column.
> 
>  
> 
> Currently, the column is a varchar.  Unfortunately, a normal string
> comparison
> 
> will not work.
> 
>  
> 
> First, has anyone done this already?
> 
>  
> 
> If not, Iâm thinking I would need to:
> 
> 1)  define a new type to hold LOC numbers.
> 
> 2)  write a function that determines less than, greater than
> 
>  
> 
> It seems like Iâm missing something here.  I have not yet defined a
> new
> 
> typeâis defining less/greater part of that process?
> 
>  
> 
> Is there a simpler way to do this?

You might want to do something along the lines of storing the PARTs of
the loc number, then using lpad/rpad in a function to put them together
in such a way to as to render them orderable.  Is there an URL on how
LOC numbers are ordered?

---(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] Multiline plpython procedure

[Stuart Bishop]

Martijn van Oosterhout wrote:
On Wed, Jan 19, 2005 at 06:28:25PM +1100, Stuart Bishop wrote:
Michael Fuhr wrote:
If Python's behavior is intentional then the newline burden would
seem to be on the user or on plpythonu.  I think Tom's point is
that that's just silly
Changing this behavior in Python would break backwards compatibility. In 
particular, the exec() function accepts strings that have already been 
unescaped:


exec('print """\n\r\n\r\n"""')
In the above example, the exec function is being passed a string 
containing carridge returns and line feeds - not '\n' and '\r' character 
sequences.

Ofcourse, if the \r is within a literal string, then ofcourse you can't
ignore it. Other languages like C and Perl also maintain any character
within a string. The point is that outside of character strings, there
is no need to consider a \n different form a \r (unless there is a
place in Python where an extra newline changes the meaning).
Sure, you can't just run dos2unix over the code, but within the parser
this is a simple change.
Oh - I had confused myself. Your point about dos2unix shows that 
plpythonu might not be able to do this 'correctly' unless it understands 
a good chunk of Python syntax. It could do it 'good enough' if that is 
acceptible.

I'll take this to python-dev, but unfortunately I think my comment below 
about the 2.3 series still stands unless higher powers believe this is a 
show stopper.

It is too late for the Python 2.3 series anyway - 2.3.5 is being 
released Jan 26th and there won't be a 2.3.6. If it was championed and 
it decided that the above example is a bug and not a feature and a patch 
produced, it could get into 2.4.1 due April and 2.5+

I suspect this means fixing this problem in plpythonu for 8.1.

I suggest adding to the Release Notes:
  User defined functions using the Python language must use the newline
  delimiter of the server OS. There is currently no standard way of
  determining the newline delimiter of the server. Note this also
  affects the portability of pg_dump output. 
I don't see how it affects the portability of pg_dump. If you have a 
working Python function (with unix line endings), won't pg_dump dump the 
source with unix line endings?

--
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Unique Index

[J. Greenlees]

Roman Neuhauser wrote:
# [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100:
i have a unique index on a table over multiple columns. If now one of 
the records has a null value in one of the indexed columns i can insert 
the same record multiple times.

Is this a problem within postgres or expected?

In SQL, NULL means "unknown value". How could you assert that two
NULLs are equal?
which doesn't make mathematical sense.
mathwise null is an empty result.
so setting the logic up using the math logic, null values are always equal.
--

only plain text format email accepted.
smaller file size, no virus transfer
no proprietary file formats.



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Multiline plpython procedure

[Alvaro Herrera]

On Thu, Jan 20, 2005 at 09:50:43AM +1100, Stuart Bishop wrote:
> Martijn van Oosterhout wrote:

> >  User defined functions using the Python language must use the newline
> >  delimiter of the server OS. There is currently no standard way of
> >  determining the newline delimiter of the server. Note this also
> >  affects the portability of pg_dump output. 
> 
> I don't see how it affects the portability of pg_dump. If you have a 
> working Python function (with unix line endings), won't pg_dump dump the 
> source with unix line endings?

It will ... so it won't work on Windows (maybe Mac OS X) servers.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"La grandeza es una experiencia transitoria.  Nunca es consistente.
Depende en gran parte de la imaginación humana creadora de mitos"
(Irulan)

---(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] sorting library of congress numbers

[Mike Rylander]

On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer <[EMAIL PROTECTED]> wrote:
> 
> I have a table where one of the columns is a library of congress number. 
> 
> I would like to be able to ORDER BY this column. 
> 

First off, by LOC numbers do you mean Title Control Numbers like
"o00325992" or "i0824506030" with an optional vendor name in front of
them?

>   
> 
> Currently, the column is a varchar.  Unfortunately, a normal string
> comparison will not work. 
>
> 
> First, has anyone done this already? 
> 

If you mean ISBN or ISSN numbers then there is a contrib module that
may help, though I don't think it implements a new type, just
functions to validate and generate them.
   
> 
> If not, I'm thinking I would need to: 
> 
> 1)   define a new type to hold LOC numbers. 
> 
> 2)   write a function that determines less than, greater than 
> 
>   
> 
> It seems like I'm missing something here.  I have not yet defined a new 
> typeâis defining less/greater part of that process?
> Is there a simpler way to do this? 

You could probably just write some comparison functions, create a new
operator class, and assign them names like "~>" and "~<".  See the
documentation here: 
http://www.postgresql.org/docs/8.0/interactive/xoper.html .

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

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

Re: [GENERAL] sorting library of congress numbers

[Rick Schumeyer]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Scott Marlowe
> Sent: Wednesday, January 19, 2005 5:54 PM
> To: Rick Schumeyer
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] sorting library of congress numbers
> 
> On Wed, 2005-01-19 at 16:37, Rick Schumeyer wrote:
> > I have a table where one of the columns is a library of congress
> > number.
> >
> > I would like to be able to ORDER BY this column.
> >
> >
> >
> > Currently, the column is a varchar.  Unfortunately, a normal string
> > comparison
> >
> > will not work.
> >
> >
> >
> > First, has anyone done this already?
> >
> >
> >
> > If not, I'm thinking I would need to:
> >
> > 1)  define a new type to hold LOC numbers.
> >
> > 2)  write a function that determines less than, greater than
> >
> >
> >
> > It seems like I'm missing something here.  I have not yet defined a
> > new
> >
> > type.is defining less/greater part of that process?
> >
> >
> >
> > Is there a simpler way to do this?
> 
> You might want to do something along the lines of storing the PARTs of
> the loc number, then using lpad/rpad in a function to put them together
> in such a way to as to render them orderable.  Is there an URL on how
> LOC numbers are ordered?

LOC numbers are described at:

http://library.dts.edu/Pages/RM/Helps/lc_call.shtml

I thought about breaking the pieces up.  I will have to think a bit more
about whether that will work.


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

Re: [GENERAL] Unique Index

[vhikida]

According to Date you should never use NULLs. This is because a NULL can
mean many different things. It can mean not known (e.g. I know he has an
age but I don't know what it is), It can be not applicable (e.g. in a
Party table of organizations and people, people would be of a certain sex
but an organization would not), It can mean a number divided by zero, It
can also mean I don't know if the value is unknown or if the value is not
applicable etc. etc. In Date's view there is an infinite number of
meanings for null and for a database to handle it correctly would require
an equivalent set of operators for each type of null.

I think though that Nulls are a very useful feature of SQL databases. It's
just that when I write a program, I have to know in a particular context
what a null means and handle it appropriately (of course controlling nulls
in an index is a different matter). I don't think there is only one
accepted way to use nulls.

> Roman Neuhauser wrote:
>> # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100:
>>
>>>i have a unique index on a table over multiple columns. If now one of
>>>the records has a null value in one of the indexed columns i can insert
>>>the same record multiple times.
>>>
>>>Is this a problem within postgres or expected?
>>
>>
>> In SQL, NULL means "unknown value". How could you assert that two
>> NULLs are equal?
>>
> which doesn't make mathematical sense.
> mathwise null is an empty result.
> so setting the logic up using the math logic, null values are always
> equal.
>
> --
> 
>
> only plain text format email accepted.
>
> smaller file size, no virus transfer
> no proprietary file formats.
>
> 
>



---(end of broadcast)---
TIP 3: 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] sorting library of congress numbers

[Rick Schumeyer]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Mike Rylander
> Sent: Wednesday, January 19, 2005 6:10 PM
> To: Rick Schumeyer; PgSql General
> Subject: Re: [GENERAL] sorting library of congress numbers
> 
> On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer <[EMAIL PROTECTED]>
> wrote:
> >
> > I have a table where one of the columns is a library of congress number.
> >
> > I would like to be able to ORDER BY this column.
> >
> 
> First off, by LOC numbers do you mean Title Control Numbers like
> "o00325992" or "i0824506030" with an optional vendor name in front of
> them?

I mean the library of congress classification numbers.  For example, 
the book "PostgreSQL" by Douglas & Douglas has the classification:
QA76.9.D3 D685 2003




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

Re: [GENERAL] need an advice on running Database

[Dann Corbit]

Yes, autovacuum is better.

I am a fossil from 7.1.3 days.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 19, 2005 2:10 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED];
Mark
Subject: Re: [GENERAL] need an advice on running Database

Do you not recommend autovacuum?

Rick


 

  "Dann Corbit"

  <[EMAIL PROTECTED]>To:   "Mark"
<[EMAIL PROTECTED]>,  
  Sent by:   cc:

  [EMAIL PROTECTED]Subject:  Re:
[GENERAL] need an advice on running Database

  tgresql.org

 

 

  01/19/2005 04:30 PM

 

 





Once per day dump database to disk.
Once per day do a vacuum full.
That should be plenty.

Since there are 1440 minutes per day, you are only looking at 288
transactions per day.  Not exactly a taxing transaction load.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark
Sent: Wednesday, January 19, 2005 12:41 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] need an advice on running Database

Hi,
I have a small data base ~ 10 tables. each table get
insert/update/delete few times a day. postgresql is running for a
month.

The load will increase in the near future: insert/update/delete
activity will be at least one in 5 minutes.

What maintenance should I need to do?

Thanks,
Mark.



__
Do you Yahoo!?
All your favorites on one personal page - Try My Yahoo!
http://my.yahoo.com

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

---(end of broadcast)---
TIP 3: 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




---(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] Unique Index

[Dann Corbit]

Null values are a big surprise to almost every end-user (though the
programmers are OK with them).

Look at the astonishment on the face of your end user when you tell them
that:
SELECT COUNT(*) FROM clothing WHERE clothing_color = 'green'
+
SELECT COUNT(*) FROM clothing WHERE NOT clothing_color = 'green'

Is not the count of all clothing because clothing without a color
recorded will not be counted.

Or (perhaps better yet, violating trichotomy) ...
If  has a null numeric value, then ALL of the following are
FALSE for that case:

Some_column < 0
Some_column > 0
Some_column = 0 
Some_column <> 0 // This is the one that many find surprising
Some_column <= 0
Some_column >= 0

You can probably see why Null values can do strange things in (for
instance) an index.

Even at that, I think that being able to insert more than one null value
into a unique index should be considered as a bug (or diagnosed as an
error).

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Wednesday, January 19, 2005 3:30 PM
To: J. Greenlees
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index

According to Date you should never use NULLs. This is because a NULL can
mean many different things. It can mean not known (e.g. I know he has an
age but I don't know what it is), It can be not applicable (e.g. in a
Party table of organizations and people, people would be of a certain
sex
but an organization would not), It can mean a number divided by zero, It
can also mean I don't know if the value is unknown or if the value is
not
applicable etc. etc. In Date's view there is an infinite number of
meanings for null and for a database to handle it correctly would
require
an equivalent set of operators for each type of null.

I think though that Nulls are a very useful feature of SQL databases.
It's
just that when I write a program, I have to know in a particular context
what a null means and handle it appropriately (of course controlling
nulls
in an index is a different matter). I don't think there is only one
accepted way to use nulls.

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

Re: [GENERAL] Multiline plpython procedure

[Frank D. Engel, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Uh, does the Python doc specify "platform" line endings, or "normal 
(\n)" line endings?  It sounded to me like it always wanted the 
UNIX-style \n line endings, so that using those would result in 
portability...

On Jan 19, 2005, at 6:03 PM, Alvaro Herrera wrote:
On Thu, Jan 20, 2005 at 09:50:43AM +1100, Stuart Bishop wrote:
Martijn van Oosterhout wrote:

 User defined functions using the Python language must use the 
newline
 delimiter of the server OS. There is currently no standard way of
 determining the newline delimiter of the server. Note this also
 affects the portability of pg_dump output.
I don't see how it affects the portability of pg_dump. If you have a
working Python function (with unix line endings), won't pg_dump dump 
the
source with unix line endings?
It will ... so it won't work on Windows (maybe Mac OS X) servers.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
"La grandeza es una experiencia transitoria.  Nunca es consistente.
Depende en gran parte de la imaginación humana creadora de mitos"
(Irulan)
---(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


- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB7vYZ7aqtWrR9cZoRAszxAKCJpwXuWU/icjj8YSyKej/daEhQyQCfRB4I
mk5qcMxWeDRa5RHIxP+9lfw=
=UUu8
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Fwd: [GENERAL] sorting library of congress numbers

[Mike Rylander]

Opps... resending to list as well.  Perhaps someone can add more insight below.

And check the documentation at
http://borg.postgresql.org/docs/8.0/interactive/extend.html .


-- Forwarded message --
From: Mike Rylander <[EMAIL PROTECTED]>
Date: Thu, 20 Jan 2005 00:05:40 +
Subject: Re: [GENERAL] sorting library of congress numbers
To: Rick Schumeyer <[EMAIL PROTECTED]>


On Wed, 19 Jan 2005 18:35:42 -0500, Rick Schumeyer <[EMAIL PROTECTED]> wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of Mike Rylander
> > Sent: Wednesday, January 19, 2005 6:10 PM
> > To: Rick Schumeyer; PgSql General
> > Subject: Re: [GENERAL] sorting library of congress numbers
> >
> > On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer <[EMAIL PROTECTED]>
> > wrote:
> > >
> > > I have a table where one of the columns is a library of congress number.
> > >
> > > I would like to be able to ORDER BY this column.
> > >
> >
> > First off, by LOC numbers do you mean Title Control Numbers like
> > "o00325992" or "i0824506030" with an optional vendor name in front of
> > them?
>
> I mean the library of congress classification numbers.  For example,
> the book "PostgreSQL" by Douglas & Douglas has the classification:
> QA76.9.D3 D685 2003
>

Ah, it wasn't clear that you meant Call Numbers from your original post.

The easiest thing to do would be to create a composite type to hold
each part of the call number.  Then you can use the standard
comparison ops for each part and just sort in a normal ORDER BY
clause.  I haven't tested any of this, but it may help you get
started.

CREATE DOMAIN txt_loc AS TEXT NOT NULL CHECK ( LENGTH(VALUE) BETWEEN 1 AND 3);
CREATE DOMAIN num_loc AS NUMERIC NOT NULL CHECK (VALUE BETWEEN 0 AND .99);

-- probably want a domain for each part to supply checks...

CREATE TYPE call_number AS (
  topic_letters txt_loc, -- QA
  topic_number num_loc, -- 76.9
  cutter TEXT, -- D3
  opt_topic TEXT, -- D685
  opt_year INT,
  opt_vol INT
);

Then, a function to display them:

CREATE FUNCTION format_cn ( call_number ) RETURNS TEXT AS '
   SELECT $1.topic_letters || $1.topic_number || ''.'' || $1.cutter ||
COALESCE('' '' || $1.opt_topic,) || COALESCE('' '' ||
$1.opt_year,) ||
COALESCE('' V.'' || $1.opt_vol,);
' LANGUAGE SQL;

Now, to sort a table like this:

CREATE TABLE title_list (
  id  SERIAL,
  call_num call_number,
  title  TEXT
);

 use:

SELECT title, format_cn(call_num)
  FROM title_list
  ORDER BY call_num.topic_letters, call_num.topic_number, call_num.cutter,
call_num.opt_topic, call_num.opt_year, call_num.opt_vol;


--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org


-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(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] sorting library of congress numbers

[Frank D. Engel, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Why not write a function that converts the LOC number to an integer, 
such that sorting on those integers will sort the numbers correctly?  
Apparently you can even build an index on this type of function now, to 
accelerate the sort.

cvt_loc(TEXT) -> INTEGER
ORDER BY cvt_loc(loc_num)
or something like that...
On Jan 19, 2005, at 6:35 PM, Rick Schumeyer wrote:
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Mike Rylander
Sent: Wednesday, January 19, 2005 6:10 PM
To: Rick Schumeyer; PgSql General
Subject: Re: [GENERAL] sorting library of congress numbers
On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer 
<[EMAIL PROTECTED]>
wrote:
I have a table where one of the columns is a library of congress 
number.

I would like to be able to ORDER BY this column.
First off, by LOC numbers do you mean Title Control Numbers like
"o00325992" or "i0824506030" with an optional vendor name in front of
them?
I mean the library of congress classification numbers.  For example,
the book "PostgreSQL" by Douglas & Douglas has the classification:
QA76.9.D3 D685 2003

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


- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB7vdJ7aqtWrR9cZoRAvd9AJwO9Ev7ZaU3IEXtssjmV6pyj5VcbwCcCtcL
oNbZSA44H4GVfGXMGC5tFZM=
=coyv
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Unique Index

[Tom Lane]

"Dann Corbit" <[EMAIL PROTECTED]> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If  has a null numeric value, then ALL of the following are
> FALSE for that case:

> Some_column < 0
> Some_column > 0
> Some_column = 0 
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.

> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

Direct your complaints to the ISO SQL standards committee.

regards, tom lane

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

[GENERAL] Why?

[Vladimir S. Petukhov]

select * from nets;

 name | note |  net
--+--+---
  |  | 172.16.0.0/16
(1 row)

select * from nets where net >>= '172.16.4.0/8';
 name | note | net
--+--+-
(0 rows)

??

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

Re: [GENERAL] Unique Index

[Dann Corbit]

True, but the standard says nothing about the creation of an index, so
you can make it behave in any way that you see fit.

-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 19, 2005 4:27 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]; J. Greenlees; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index


On Wed, 19 Jan 2005, Dann Corbit wrote:

> Even at that, I think that being able to insert more than one null
value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

AFAICT the UNIQUE constraint that it's used to model explicitly allows
multiple NULLs in the spec so I don't see making it error as being
terribly workable.


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

Re: [GENERAL] Unique Index

[Dann Corbit]

The ISO SQL Standard does not even define an index, and so any index is
an extension to the standard (though primary keys and foreign keys imply
them).

At least in the SQL Standard that I have (ANSI/ISO/IEC 9075-1-1999 and
related documents) has no definition of an index.  Perhaps the newer
version contains such a definition.

So, in the creation of an extension, I think it is up to the programmer
to do whatever is best.

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 19, 2005 4:18 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]; J. Greenlees; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Unique Index 

"Dann Corbit" <[EMAIL PROTECTED]> writes:
> Or (perhaps better yet, violating trichotomy) ...
> If  has a null numeric value, then ALL of the following
are
> FALSE for that case:

> Some_column < 0
> Some_column > 0
> Some_column = 0 
> Some_column <> 0 // This is the one that many find surprising
> Some_column <= 0
> Some_column >= 0

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.

> Even at that, I think that being able to insert more than one null
value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

Direct your complaints to the ISO SQL standards committee.

regards, tom lane

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

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

Re: [GENERAL] Unique Index

[Stephan Szabo]

On Wed, 19 Jan 2005, Dann Corbit wrote:

> Even at that, I think that being able to insert more than one null value
> into a unique index should be considered as a bug (or diagnosed as an
> error).

AFAICT the UNIQUE constraint that it's used to model explicitly allows
multiple NULLs in the spec so I don't see making it error as being
terribly workable.


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

Re: [GENERAL] Why?

[Vladimir S. Petukhov]

Sorry, of course... :)
On Thursday 20 January 2005 03:15, Vladimir S. Petukhov wrote:
> select * from nets;
>
>  name | note |  net
> --+--+---
>
>   |  | 172.16.0.0/16
>
> (1 row)
>
> select * from nets where net >>= '172.16.4.0/8';
>  name | note | net
> --+--+-
> (0 rows)
>
> ??
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

-- 
Как сексапильно молвишь ты "Дурак"...
  -- Владимир Вишневский

Now playing: 13 - Wayne Johnson - Goodnight Moon.mp3
   AutoGenerated by fortune & xmms...

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

Re: [GENERAL] Why?

[Tom Lane]

"Vladimir S. Petukhov" <[EMAIL PROTECTED]> writes:
> select * from nets;
>  name | note |  net
> --+--+---
>   |  | 172.16.0.0/16
> (1 row)

> select * from nets where net >>= '172.16.4.0/8';
>  name | note | net
> --+--+-
> (0 rows)

Are you confusing >>= with <<= perhaps?  The >>= operator tests

ip_bits(a1) <= ip_bits(a2)
&& bitncmp(ip_addr(a1), ip_addr(a2), ip_bits(a1)) == 0

which looks reasonable enough to me.  By that logic a /16 net
can never be a supernet of a /8 net.

regards, tom lane

---(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] need an advice on running Database

[Uwe C. Schroeder]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Although with your very little load a manual or cron based vacuum full once a 
week will be more than enough.
I'm doing a biweekly vacuum full with one of my customer's machines  (an 
office application that uses pg as backend) and never had complaints or 
problems. That setup makes about 5000 transactions a day - still a very low 
load for postgres.

On Wednesday 19 January 2005 03:42 pm, Dann Corbit wrote:
> Yes, autovacuum is better.
>
> I am a fossil from 7.1.3 days.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 19, 2005 2:10 PM
> To: Dann Corbit
> Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED];
> Mark
> Subject: Re: [GENERAL] need an advice on running Database
>
> Do you not recommend autovacuum?
>
> Rick
>
>
>
>
>   "Dann Corbit"
>
>   <[EMAIL PROTECTED]>To:   "Mark"
> <[EMAIL PROTECTED]>, 
>   Sent by:   cc:
>
>   [EMAIL PROTECTED]Subject:  Re:
> [GENERAL] need an advice on running Database
>
>   tgresql.org
>
>
>
>
>
>   01/19/2005 04:30 PM
>
>
>
>
>
>
>
>
>
> Once per day dump database to disk.
> Once per day do a vacuum full.
> That should be plenty.
>
> Since there are 1440 minutes per day, you are only looking at 288
> transactions per day.  Not exactly a taxing transaction load.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Mark
> Sent: Wednesday, January 19, 2005 12:41 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] need an advice on running Database
>
> Hi,
> I have a small data base ~ 10 tables. each table get
> insert/update/delete few times a day. postgresql is running for a
> month.
>
> The load will increase in the near future: insert/update/delete
> activity will be at least one in 5 minutes.
>
> What maintenance should I need to do?
>
> Thanks,
> Mark.
>
>
>
> __
> Do you Yahoo!?
> All your favorites on one personal page - Try My Yahoo!
> http://my.yahoo.com
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
> ---(end of broadcast)---
> TIP 3: 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
>
>
>
>
> ---(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

- -- 
UC

- --
Open Source Solutions 4U, LLC   2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFB7v/NjqGXBvRToM4RAsB5AKCTeELPQA9R/kce4rD78X0mJE+JdQCeIIak
rs7QSe2KHopOt9AkADg+ofU=
=KRuh
-END PGP SIGNATURE-


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

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

Re: [GENERAL] Unique Index

[Stephan Szabo]

On Wed, 19 Jan 2005, Dann Corbit wrote:

> True, but the standard says nothing about the creation of an index, so
> you can make it behave in any way that you see fit.

The unique index is however used to model the unique constraint in
PostgreSQL which I had thought was clear from my statement so giving the
unique index behavior which makes it unable to model the constraint
wouldn't be terribly workable (without rewriting the constraint to be
modeled in a separate fashion).

> -Original Message-
> From: Stephan Szabo [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 19, 2005 4:27 PM
> To: Dann Corbit
> Cc: [EMAIL PROTECTED]; J. Greenlees; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Unique Index
>
>
> On Wed, 19 Jan 2005, Dann Corbit wrote:
>
> > Even at that, I think that being able to insert more than one null
> value
> > into a unique index should be considered as a bug (or diagnosed as an
> > error).
>
> AFAICT the UNIQUE constraint that it's used to model explicitly allows
> multiple NULLs in the spec so I don't see making it error as being
> terribly workable.
>

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

   http://archives.postgresql.org

Re: [GENERAL] Unique Index

[Stephan Szabo]

On Wed, 19 Jan 2005, Stephan Szabo wrote:

>
> On Wed, 19 Jan 2005, Dann Corbit wrote:
>
> > True, but the standard says nothing about the creation of an index, so
> > you can make it behave in any way that you see fit.
>
> The unique index is however used to model the unique constraint in
> PostgreSQL which I had thought was clear from my statement so giving the
> unique index behavior which makes it unable to model the constraint
> wouldn't be terribly workable (without rewriting the constraint to be
> modeled in a separate fashion).

Actually, thinking about it, I think I generally disagree with the idea
that unique is the correct word to use in any case.  It's used in both the
constraint and in the predicate to have its current meaning (as described
in my earlier message) and I think the word distinct already the semantics
of treating two NULLs in the fashion such that an index wouldn't allow
two.

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

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

[GENERAL] Ways to check the status of a long-running transaction

[Jim C. Nasby]

I recall this being discussed before, but I couldn't manage to find it
in the archives.

Is there any way to see how many rows a running transaction has written?
vacuum analyze verbose only reports visible rows.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 3: 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] Unique Index

[Alex]

I actually just wanted to know if there is a way around this problem. 
Obviously it is implemented that way for whatever reason.

I still though think some arguments given in some of the replies, while 
probably correct, are besides the point.

I use a unique index that may contain null values. On an insert or 
update I can now not rely on the exception thrown but actually have to 
write a select statement to check if the same row exists, which I 
believe defies ONE purpose of having unique indices. Whether Null is 
associated with "unknown value", "divided by zero"... or however one 
wants to interpret it is not the issue here, in my view NULL in the same 
column have the same value or at least should be treated the same. (If I 
want to differentiate the state, I would use a code instead of NULL as a 
NULL does not give any indication of its meaning, thus we could safely 
assume they are treated as equal).

Maybe there could be an option in the creation of the index to indicate 
on how to use NULL values.

How do other DBMS handle this?
A


Tom Lane wrote:
"Dann Corbit" <[EMAIL PROTECTED]> writes:
 

Or (perhaps better yet, violating trichotomy) ...
If  has a null numeric value, then ALL of the following are
FALSE for that case:
   

 

Some_column < 0
Some_column > 0
Some_column = 0 
Some_column <> 0 // This is the one that many find surprising
Some_column <= 0
Some_column >= 0
   

It's worse than that: the above do *not* yield FALSE, they yield NULL.
Which does act like FALSE in a simple WHERE clause, but there are other
cases (like CHECK clauses) where it doesn't.  "x NOT IN (SELECT ...)"
is a case that newbies routinely get bitten by.
 

Even at that, I think that being able to insert more than one null value
into a unique index should be considered as a bug (or diagnosed as an
error).
   

Direct your complaints to the ISO SQL standards committee.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
 


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

Re: [GENERAL] Unique Index

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> "Dann Corbit" <[EMAIL PROTECTED]> writes:
> 
> > Even at that, I think that being able to insert more than one null value
> > into a unique index should be considered as a bug (or diagnosed as an
> > error).
> 
> Direct your complaints to the ISO SQL standards committee.

The SQL standard generally treats NULLs as a escape hatch for constraints.

That's true for CHECK constraints as well: if you have a "CHECK a>0"
constraint but a is nullable then a NULL value is allowed even though the
check constraint can't be verified.

This isn't an unreasonable approach. Once you have NULLs it makes more sense
to make them useful than to treat them as just another value. You can always
make the column NOT NULL if you don't want any exceptions to your constraints.

-- 
greg


---(end of broadcast)---
TIP 3: 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