date:20041006

Glen Eustace [EMAIL PROTECTED] writes:
 Having read this, it would seem I might be jumping the gun a bit.  The
 method given could be used to copy a running database to another system
 but I'm not sure how it would work if the mirrored database is required
 to be online at the same time.

It wouldn't.  You can have a hot spare database via log shipping, but
there is zero chance of allowing the backup to run any independent
transactions.  (If it did, its xact counter and pg_clog would get out of
sync with the master, causing all subsequently copied data to be
invalid.  AFAICS, even read-only transactions would be problematic.)

Not sure if you are familiar with the Slony project, but that is
probably your best bet for data propagation to a live slave at present.

regards, tom lane

---(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] lower/upper support and cyrillic

2004-10-06 Thread pginfo





Hi Oleg and Alvaro,

It is great news for me.

I will test it next week in beta 3 and will waiting for the release.

regards,
iavn.

Alvaro Herrera wrote:

  On Wed, Oct 06, 2004 at 08:00:57AM +0200, pginfo wrote:

Hi,

  
  
I tesetd the lower/upper support and cyrillic and it looks broken.

My  system:
pg 7.4.3
freebsd 5.2.1.

I created my database with 'UNICODE'.

Is it my mistake or realy pg do not support correctly lower and upper ?

  
  
This was fixed in 8.0, so yes, you are right that 7.4 does not behave
correctly.

  
  
I readet also the ToDo and do not find this  task.

  
  
Done tasks were removed from the to-do list few days ago.

Re: OT: spherical geometry (Re: [GENERAL] earthdistance is not giving ...)

On Wed, Oct 06, 2004 at 08:52:55 +0200,
  Holger Klawitter [EMAIL PROTECTED] wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 On Sunday 03 October 2004 20:22, Bruno Wolff III wrote:
  Latitudes greater than 90 degrees have a reasonable
  meaning and it can be useful to use 0 to 180 instead of -90 to 90.
 
 Just a curious question: What is 100°N latitude supposed to mean?

It means 80 degrees north and longitude + 180 degrees.

I shouldn't have used 0 to 180 as the example for latitude, because it
really needs to range from 0 to 360, since 0 to 180 is all in the
northern hemisphere. Longitude works similarly in that you can use
0 to 360 instead of -180 to +180.

The advantage of this is that your application can do things like add degrees
to a position and not have to check for wrapping around. You can get similar
issues due to rounding after switching coordinate systems where you might
get a value slightly greater than 90 degrees for latitude or get a value
slightly greater than 180 degrees for longitude.

As long as the principal values are returned when going from cartesian
coordinates (which is how earth distance stores points) to latitude
and longitude accepting values outside of the principal ones when
going from spherical coordinates to cartesian coordinates isn't a problem.

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

Re: [GENERAL] two digit years in inserts

Justin Wyer [EMAIL PROTECTED] writes:
 I have this problem, I wrote a script to insert data into a table, one 
 of the columns is a birthdate now I only have the last two year digits, 
 and this all worked fine. Until I did a portupgrade (i am running on bsd 
 5.2.1 and 4.8 and postgresql 7.3.6  7.3.7 respectively) script worked 
 fine before the upgrade, now however, any year before 70 gets inserted 
 as 20xx and not 19xx.

 My question is between which versions did this behaviour change, and is 
 there any way for me to force the old behaviour?

Don't you think it's time to fix your data?  2-digit years were a bad
idea from the get-go, but it was possible to get away with it for awhile
near the end of a century.

regards, tom lane

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

[GENERAL] Cache lookup failed for relation, when trying to DROP TABLE.

Hello,
  I've just encountered a problem that I don't know how to deal with.
After having a play with SlonyI, I dropped the entire slony cluster
schema, and then tried to drop a schema which held some slave tables
for the slony cluster.
I kept getting the following error:
ERROR:  cache lookup failed for relation 4667548
So, I cleared out everything I could manually from the schema,
leaving four remaining tables that refuse to be dropped, giving
the error above (the relation oid varies though).
Anyone know what this means?
Is there a way to forcefully remove these tables and the schema
using the pg_catalog tables?
Is there any other information I should provide that may help?
Specs:
Redhat Enterprise Linux 3
PostgreSQL 7.4.5
Slony-I 1.0.2
Cheers
--
Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk
Web Developer  Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Mailing

On Tue, Oct 05, 2004 at 10:00:29 -0500,
  Kevin Barnard [EMAIL PROTECTED] wrote:
 SELECT trim(leading '0'  from to_char(now(), 'HH:MM AM'))
 
 I think is what you really want.  This gets rid of the nasty leasing 0.

Won't that be a problem for times between  and 0059?

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

   http://archives.postgresql.org

[GENERAL] Commands to browse current connections and processes

2004-10-06 Thread Ying Lu

Hello,
In mysql, we use show processlist to see all current process. Could 
someone let me know in PostgreSQL, what commands that we can check the 
current connections and processes please?

Thanks a lot,
Ly

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

Re: [GENERAL] two digit years in inserts

2004-10-06 Thread Mike Nolan

 its not my data i am stuck moving this data from a sad access database 
 designed by a moron. anyway it means i will have to do alot of work on 
 the script to make it fix that, or just install an old version of pgsql 
 on a box here and pg_dump the table which seems easier.

I've ported a lot of data from legacy systems with 6 digit dates in 
them (mmddyy), not all of which were addressed for Y2K.  

For each system I usually wind up writing one or more to_date functions, 
so that I have a consistent set of rules being applied.

Depending upon the specific application, those functions will have 
different switchover points between 1900 and 2000 as the base century.

Sometimes I have to supply an additional parameter to help decide when 
to switch over.

For example, if the data includes other age-based qualifications, such
as whether someone is a child or a senior citizen, that offers another clue 
as to whether '02' in a birthdate is 2002 or 1902.  Depending on how 
clean the data is in the first place, that might not fix all inconsistencies, 
though.

Another reason for having my own date conversion function is consistency
in dealing with bad dates, like 05/32/2004 or 11/31/2004.  

In general, I think that date conversion decisions are an application
design issue, not a database system issue, and the fewer such decisions 
that are made by the database, the better.
--
Mike Nolan

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

Re: [GENERAL] Commands to browse current connections and processes

Ying Lu wrote:
Hello,
In mysql, we use show processlist to see all current process. Could 
someone let me know in PostgreSQL, what commands that we can check the 
current connections and processes please?
SELECT * FROM pg_catalog.pg_stat_activity;
--
Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk
Web Developer  Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Cache lookup failed for relation, when trying to DROP TABLE.

Mark Gibson [EMAIL PROTECTED] writes:
 I kept getting the following error:
 ERROR:  cache lookup failed for relation 4667548

This implies that something someplace still has a link to the table with
that OID.  You could do \set VERBOSITY verbose so that the code
location the error occurs at gets reported; that might be enough
information to guess what sort of thing is linking to the table.
Or you could just dig through the system catalogs looking for 4667548
in an OID column that links to pg_class.

regards, tom lane

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

[GENERAL] database constraints

2004-10-06 Thread Ben

If I have have the table:
create table foo
(
a int references bar(id),
b int references baz(id)
)
... how do I make sure one and only one of the columns a and b are 
non-null? Is it even reasonable?

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

Re: [GENERAL] Random not so random

2004-10-06 Thread Michael Fuhr

On Tue, Oct 05, 2004 at 07:23:32AM -0600, Michael Fuhr wrote:
 On Tue, Oct 05, 2004 at 02:39:13PM +0200, Harald Fuchs wrote:
 
  I think we don't need the randomness provided by /dev/[u]random.  How
  about XORing in getpid?
 
 What about making the seeding mechanism and perhaps random()'s
 behavior configurable?

Regarding a configurable seeding mechanism, I was thinking along
the lines of Apache's SSLRandomSeed directive:

http://httpd.apache.org/docs-2.0/mod/mod_ssl.html#sslrandomseed

The builtin source could use a seed based on the time and
possibly the process ID, similar to the current implementation.

The file source would allow admins to use /dev/random or
/dev/urandom, whichever they prefer, or even an ordinary
file if they always wanted the same seed for testing purposes.
The backend wouldn't know or care what the source was: it
would simply open the specified file and read from it.

The exec source would read the seed from an external program,
which could generate it by whatever means desired.

The Apache directive also supports egd to obtain the seed
from an Entropy Gathering Daemon.

By making the seeding mechanism configurable, then everybody
can have it their own way.

Comments?

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

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

Re: [GENERAL] Cache lookup failed for relation, when trying to DROP

Tom Lane wrote:
Mark Gibson [EMAIL PROTECTED] writes:
I kept getting the following error:
ERROR:  cache lookup failed for relation 4667548

This implies that something someplace still has a link to the table with
that OID.  You could do \set VERBOSITY verbose so that the code
location the error occurs at gets reported; that might be enough
information to guess what sort of thing is linking to the table.
Or you could just dig through the system catalogs looking for 4667548
in an OID column that links to pg_class.

\set VERBOSITY verbose gave me the following:
ERROR:  XX000: cache lookup failed for relation 4667548
LOCATION:  getRelationDescription, dependency.c:1755
and...
After sticking 4667548 everywhere an oid can be stuck, I've found an
entry in the pg_rewrite table.
It's from a rule I added myself, but I dropped it manually, strange that
is still lurking around.
So, I tried deleting the offending entries from pg_rewrite, and now get:
ERROR:  XX000: could not find tuple for rule 5173132
LOCATION:  getObjectDescription, dependency.c:1669
I'm guessing I'm gonna have to route through pg_catalog for this and
delete all deps manually, but it this going to be safe?
Would I be better off dumping and restoring the whole database?
--
Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk
Web Developer  Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] database constraints

2004-10-06 Thread sklassen

* Ben [EMAIL PROTECTED] [2004-10-06 08:23:11 -0700]:

 If I have have the table:
 
 create table foo
 (
   a int references bar(id),
   b int references baz(id)
 )
 
 ... how do I make sure one and only one of the columns a and b are 
 non-null? Is it even reasonable?

ALTER TABLE foo ADD CONSTRAINT must_have_a_or_b CHECK (a::int IS NULL
AND b::int IS NOT NULL OR a::int IS NOT NULL AND b::int IS NULL);

-- 
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication  Support Services, (503) 667-4564

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

Re: [GENERAL] database constraints

2004-10-06 Thread David Fetter

On Wed, Oct 06, 2004 at 08:23:11AM -0700, Ben wrote:
 If I have have the table:
 
 create table foo
 (
   a int references bar(id),
   b int references baz(id)
 )
 
 ... how do I make sure one and only one of the columns a and b are 
 non-null?

You could write it like this:

CREATE TABLE foo
(
a INT REFERENCES bar(id),
b INT REFERENCES baz(id),
CHECK((a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL))
);

 Is it even reasonable?

What's reasonable? ;)

BTW, id is a terrible name for a column.  Better call it foo_id.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(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] passing new.* in trigger

2004-10-06 Thread Alessandro Vincelli

I must pass NEW row to function count_room(rowtype) as in this code, but 
pgsql return _ NEW used in query that is not in a rule.
I have a bad syntax or is impossible pass NEW.*?

thanks,  Ale
_
CREATE OR REPLACE FUNCTION public.dr_aggiorna_consistenze()
 RETURNS trigger AS
'
declare
room int;   
BEGIN

SELECT INTO camere  count_room(NEW.*) FROM NEW ;
   NEW.camere = camere;
   RETURN NEW;
   END;
'
LANGUAGE 'plpgsql' VOLATILE;
--
---
Alessandro Vincelli 
W4B - web for business s.r.l.
Firenze 
via Pellicceria 10 - 50123
E-mail: [EMAIL PROTECTED]
tel: 055-2654270 

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

Re: [GENERAL] Cache lookup failed for relation, when trying to DROP

Mark Gibson wrote:
I'm guessing I'm gonna have to route through pg_catalog for this and
delete all deps manually, but it this going to be safe?
Would I be better off dumping and restoring the whole database?
Right then, I think I've got this sorted,
DROP TABLE worked after a swift:
DELETE FROM pg_depend WHERE objid = 5173132;
Then DROP SCHEMA also worked.
Do you think I should be safe to continue working with the database now,
without a full dump and restore?
(It's not in production use, but it would take quite some time for
dump/restore)
Cheers.
--
Mark Gibson gibsonm |AT| cromwell |DOT| co |DOT| uk
Web Developer  Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] database constraints

2004-10-06 Thread Dennis Gearon

Use a post trigger function, ON UPDATE, INSERT which essentially has 
this in it:

if ( ISNULL(new.a) AND ISNULL(new.b) ){ RAISE NOTICE blah blah; }
I work with PHP a lot, just a little plpgsql, so, the grammar may be 
wrong above.
Ben [EMAIL PROTECTED] wrote:
quote ---
If I have have the table:

create table foo
(
   a int references bar(id),
   b int references baz(id)
)
... how do I make sure one and only one of the columns a and b are 
non-null? Is it even reasonable?
/quote --

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

Re: [GENERAL] Cache lookup failed for relation, when trying to DROP

Mark Gibson [EMAIL PROTECTED] writes:
 Right then, I think I've got this sorted,
 DROP TABLE worked after a swift:
 DELETE FROM pg_depend WHERE objid = 5173132;

There's something awfully flaky going on here.  The system should never
have let you get into this state in the first place: the entire point of
pg_depend is that you can't delete the referenced object without
deleting the referencing object.  Had you been doing anything odd before
this (like perhaps manually deleting catalog rows)?  You mentioned
having dropped the troublesome rule; did you do that by-the-book with
DROP RULE, or did you just DELETE FROM pg_rewrite?

 Do you think I should be safe to continue working with the database now,
 without a full dump and restore?

Hard to say.  If you haven't been sticking your fingers where they
shouldn't go, then this definitely represents a failure of the
dependency mechanism.  Aside from the possibility of plain old bugs,
I'd be wondering about corrupted indexes on pg_depend.

regards, tom lane

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

Re: [GENERAL] database constraints

2004-10-06 Thread David Fetter

On Wed, Oct 06, 2004 at 09:32:02AM -0700, Joshua D. Drake wrote:

 What's reasonable? ;)
 
 BTW, id is a terrible name for a column.  Better call it foo_id.
 
 Hello,
 
 I disagree with the idea that id is a terrible name for a column.  The 
 only negative to it, is that you will have to be explicit in your 
 declarations when doing joins and such... ex:
 
 SELECT * FROM foo
JOIN bar on (foo.id = bar.id)
 
 Personally I would rather see, and write that then:
 
 SELECT * FROM foo
JOIN bar on (foo_id = bar_id)

With all due respect, Josh, naming your columns with decipherable
names, i.e. *not* having 50 different things called id in your db
helps enormously with maintenance, especially when the current
maintainer has never met the designer, a common situation.  Also, many
databases have documents that are inadequate, out of date, or both, so
decipherable names, along with as much other self-documentation, is a
big plus.

Cheers,
D

P.S.  As a rule, SELECT * doesn't belong in production code./nit
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

Re: [GENERAL] Random not so random

I am going to keep this on general for now, since it seems like other people
might be interested even though it is straying a somewhat off topic.

On Wed, Oct 06, 2004 at 18:02:39 +0200,
  Marco Colombo [EMAIL PROTECTED] wrote:
 
 It depends. What's wrong with a SQL function taking long to
 complete? It could be a long computation, maybe days long. As far as

Days long SQL queries are hardly normal. Common things you might want
to generate secure random numbers for aren't going to be queries you
want to run a long time. For example you might want to generate
session ids to store in a cookie handed out for web requests.

 the client is concerned, there's no difference. Of course you'll have
 to use it with care, just as you would do with any potentially long
 function (that is, don't lock everything and then sit down waiting).

This might be reasonable if there was a significant benefit to doing so.
My argument is that /dev/random does not produce significantly more
secure random numbers under normal circumstances.

 SHA1 is pretty safe for this purpose. The recent weaknesses of related
 hashes isn't going to be of much help in predicting the output of
 /dev/urandom. If SHA1 were to be broken that badly /dev/random would
 also be broken.
 
 No, because /dev/random returns only after the internal state has been
 perturbed enough. An observer may (in theory) get to know all
 bits of the internal pool right _after_ a read() from /dev/random,
 but still isn't able to guess the value of all of them right before the
 _next_ read() returns, exactly because the kernel waits until the return
 is safe in this regard.

OK, I'll buy that for cases where the estimates of entropy acquired are
reasonable. This may still be a problem for some uses though. It may allow
an attacker to figure out other data returned by /dev/random that they
weren't able to observe.

 Now, if the attacker gets the output, and breaks SHA1, he'll know the
 internal state again. But if the output goes elsewhere, he won't be
 able to guess it. That's why /dev/random is 'safe'. It won't return
 until the output is random enough.

You don't necessarily need to break SHA1 to be able to track the internal
state.

 Now, my esteem is 0 entropy bits for my pool, since you drained them all.
 
 Secure Application X needs to generate a session key (128bits),
 so reads them from /dev/urandom. Let's assume the entropy count is still 0.
 I (the kernel) provide it with 128bits that come from my PRNG + SHA1 engine.
 Now, you can predict those 128 bits easily, and thus you know the session
 key. The attack succeeds.
 
 Really Secure Application Y needs 128 bits, too. But this time it
 reads from /dev/random (assume entropy count is still 0).
 Now, I can't fulfill the request. So have Y wait on read().
 
 As time passes, I start collecting entropy bits, from IRQ timings,
 or a hardware RNG. These bits change the internal pool in a way you
 don't know. Eventually I get the count up to 128 bits.
 Now, I run my PRNG (from the now-changed state) + SHA1 engine, and return
 128 bits to Y. Can you guess the session key? Of course. You know
 how internal state was before, you can go through all possible entropy
 values. How many of them? 2^128. That's, no wonder, the same of trying
 to guess the key directly. Now you're telling me if you had the key,
 you could know the internal state again? Man, if you had the key, you
 already broke application Y, and the attack already succeeded by other
 means!

Assuming that all 128 bits were grabbed in one call to /dev/random, you
should be safe. However if the bits were returned a byte at a time
with intervening bytes returned to /dev/urandom to the attacker, the
key could be vunerable.

 My memory of looking at the /dev/[u]random code is that
 there is just one entropy pool and entropy is added to it as it is
 obtained. So that if values are obtained from /dev/[u]random at a high
 enough rate the above attack is practical.
 
 So the only case where you might want to use /dev/random over /dev/urandom
 is where the internal state is vunerable, the attacker has access to a 
 large
 fraction of the output values and where there are at least some gaps 
 between
 samples where large amounts of entropy are collected.
 
 No. SHA1 protects the internal pool from an attacker who knows all the
 output. That's easy, just read from /dev/random until it blocks. If you're
 fast enough, you can assume no one else read from /dev/random. Now,
 _if you can break SHA1_, you have enough cleartext output of the internal
 PRNG to guess it's state. You may have used /dev/urandom as well, there's
 no difference.

I think you misunderstood the context. The attacker is assumed to have
initially gotten the state through some means. By watching the output
from /dev/urandom it is possible to figure out what the current state is
if not too much entropy has been added since the attacker knew the state.

 The purpose of /dev/random blocking is not protecting the

Re: [GENERAL] Random not so random

2004-10-06 Thread Marco Colombo

On Tue, 5 Oct 2004, Tom Lane wrote:
now.tv_sec, and it's perfectly portable.  No one in their right mind
expects random(3) to be cryptographically secure anyway, so doing more
doesn't seem warranted.
Tom, having a source of real random data isn't useful just for crypto
applications. No PRNG is perfect, when it comes to statistics.
The various proposals to create a more-secure, less-portable variant
of random() don't seem appropriate to me for beta.  But I'd not object
to someone whipping up a contrib module for 8.1 or beyond.
Agreed.
.TM.
--
  /  /   /
 /  /   /   Marco Colombo
___/  ___  /   /  Technical Manager
   /  /   /  ESI s.r.l.
 _/ _/  _/ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] postgres.h location?

2004-10-06 Thread Scott Frankel

I'm looking for the file postgres.h in my recent install of 
postgres-7.4.5 on
a MacOS 10.3.5 system.

I'm attempting to build PyGreSQL-3.5, which appears to require the 
postgres
include dir.

My build of postgres-7.4.5 did produce an include dir, 
/usr/local/pgsql/include;
but it does not contain postgres.h.  Curiously, it does contain a file 
called postgres_ext.h.
I'm sure they're not one-and-the-same ...

Suggestions?
Thanks in advance!
Scott

---(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] database constraints

2004-10-06 Thread David Fetter

On Wed, Oct 06, 2004 at 10:07:43AM -0700, Joshua D. Drake wrote:
 
 SELECT * FROM foo
   JOIN bar on (foo.id = bar.id)
 
 Personally I would rather see, and write that then:
 
 SELECT * FROM foo
   JOIN bar on (foo_id = bar_id)
 
 
 With all due respect, Josh, naming your columns with decipherable
 names, i.e. *not* having 50 different things called id in your db
 helps enormously with maintenance, 
 
 I agree 100%. For us, it makes sense to have id, because if we have
 table foo, bar, and baz. We know that the primary key is always, foo.id,
 bar.id and baz.id.

Coding standards are a Good Thing(TM). :)

 especially when the current maintainer has never met the designer,
 a common situation.  
 
 What is what documentation is for.

And in a perfect world... ;)

 Also, many databases have documents that are inadequate, out of
 date, or both, so decipherable names, along with as much other
 self-documentation, is a big plus.
 
 Agreed. Which is why we make exhaustive use of the comment command
 :)

This brings up an interesting idea.  What if it were possible to set
some kind of rules on DDL at database creation time?  For example, I'd
like to be able to throw an error if somebody tries to name an object
any of the SQL keywords.

Other possible rules:

* Every table must have a comment
* noCamelCaps
* downcase all entities
* underbar_separators_required
* abbrev_w_beg, nt_by_rmvng_vwls
* Your favorite algorithmic coding standard here

What do y'all think?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(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] database constraints

On Wed, Oct 06, 2004 at 08:35:21 -0700,
  [EMAIL PROTECTED] wrote:
 * Ben [EMAIL PROTECTED] [2004-10-06 08:23:11 -0700]:
 
 ALTER TABLE foo ADD CONSTRAINT must_have_a_or_b CHECK (a::int IS NULL
 AND b::int IS NOT NULL OR a::int IS NOT NULL AND b::int IS NULL);

A simpler constraint is:
check (a is null  b is null);

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

   http://archives.postgresql.org

Re: [GENERAL] database constraints

2004-10-06 Thread Joshua D. Drake

This brings up an interesting idea. What if it were possible to set
some kind of rules on DDL at database creation time? For example, I'd
like to be able to throw an error if somebody tries to name an object
any of the SQL keywords.
Other possible rules:
* Every table must have a comment
* noCamelCaps
* downcase all entities
* underbar_separators_required
* abbrev_w_beg, nt_by_rmvng_vwls
* Your favorite algorithmic coding standard here
Hmmm.. like a PostgreSQL syntax mode... in the PostgreSQL.conf:
syntax_mode = lazy, standard, strict
Where lazy is the current , standard would throw exceptions if you try
to use a reserved word or use uppercase, and strict would do things like
force a comment.
Ooooh... Bastard Operator From Hell here we come.
Sincerely,
From a managerial perspective I like it... Just like you have to be
explicit when using cvs or subversion to NOT comment.

Sincerely,
Joshua D. Drake

What do y'all think?
Cheers,
D

--
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
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard

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

[GENERAL] Activestate Perl and DBD-Pg?

2004-10-06 Thread David Siebert

Anyone using Activestate Perl and DBD-Pg?
I am using perl 5.8.3 ppm does not seem to work. I downloaded the DBD-Pg 
.zip file I found through google but ppm could not seem to install that. 
Any suggestions?

---(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] postgres.h location?

Scott Frankel [EMAIL PROTECTED] writes:
 My build of postgres-7.4.5 did produce an include dir, 
 /usr/local/pgsql/include;
 but it does not contain postgres.h.

You need to do make install-all-headers while installing to install
the server-side headers there.

regards, tom lane

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

Re: [GENERAL] passing new.* in trigger

Alessandro Vincelli [EMAIL PROTECTED] writes:
 I must pass NEW row to function count_room(rowtype) as in this code, but 
 pgsql return _ NEW used in query that is not in a rule.
 I have a bad syntax or is impossible pass NEW.*?

That will work in 8.0, but not before.

regards, tom lane

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

Re: [GENERAL] Help on copy function

[EMAIL PROTECTED] writes:
 And an error occurs :
 psql:lineitem.loader.psql:1: ERROR:  could not extend relation
 24342131/24342133
 /24342324: There is not enough space in the file system.
 HINT:  Check free disk space.

 but my fileSystem has something like 2Go free when copy fails!

Maybe you are running the postmaster under a disk-space-usage limit?
I'm not sure that there's a separate errno for you can't have any
more space as opposed to there isn't any more space.

regards, tom lane

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

Re: [GENERAL] Mirroring databases in v8

2004-10-06 Thread Glen Eustace

On Thu, 2004-10-07 at 03:06, Tom Lane wrote:
 Not sure if you are familiar with the Slony project, but that is
 probably your best bet for data propagation to a live slave at present

Hi Tom,

Thanks for the comment.  I was aware of Slony but didn't want the
mirroring to require addition of triggers etc. if it could be avoided.

I had come across a system in Ingres, and I know Ingres isn't PostgreSQL
but it was still a cool idea.

The logging system in Ingres produces journal files which are then
available for roll-forward recovery, but there is a tool called dbaudit
( I think ) which allows one to determine what the changes where.  From
this info, ordinary SQL can be generated that one can then use to do
other things. In this case the data was used to generate a who changed
what when report that was feed back into the database into an audit
table.


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

Re: [GENERAL] postgres.h location?

2004-10-06 Thread Michael Fuhr

On Wed, Oct 06, 2004 at 03:27:39PM -0400, Tom Lane wrote:
 Scott Frankel [EMAIL PROTECTED] writes:
  My build of postgres-7.4.5 did produce an include dir, 
  /usr/local/pgsql/include;
  but it does not contain postgres.h.
 
 You need to do make install-all-headers while installing to install
 the server-side headers there.

BTW, did this just change in 8.0?  The latest CVS sources no longer
have an install-all-headers target (GNUmakefile.in 1.41); it looks
like the install target installs all headers now.

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

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

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

Re: [GENERAL] postgres.h location?

Michael Fuhr [EMAIL PROTECTED] writes:
 On Wed, Oct 06, 2004 at 03:27:39PM -0400, Tom Lane wrote:
 You need to do make install-all-headers while installing to install
 the server-side headers there.

 BTW, did this just change in 8.0?

There was a proposal to change it, but I dunno if it was committed.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Help on copy function

2004-10-06 Thread Doug McNaught

Tom Lane [EMAIL PROTECTED] writes:

 [EMAIL PROTECTED] writes:
 And an error occurs :
 psql:lineitem.loader.psql:1: ERROR:  could not extend relation
 24342131/24342133
 /24342324: There is not enough space in the file system.
 HINT:  Check free disk space.

 but my fileSystem has something like 2Go free when copy fails!

 Maybe you are running the postmaster under a disk-space-usage limit?
 I'm not sure that there's a separate errno for you can't have any
 more space as opposed to there isn't any more space.

It's also possible that PG is trying to create a new table file and
he's out of inodes...

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

---(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] Help on copy function