Re: [GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions
But I don't understand why there are changes of the databases template1 and template0 at all? I thought they are only templates. I don't think that there were any changes to the template databases. You detected a difference in age(datfrozenxid) - try selecting datfrozenxid itself and you will probably see that it does not change over time. Yours, Laurenz Albe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
Michael Glaesemann wrote: On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote: What I don't understand, however, is exactly *why* date_trunc is not immutable ? I believe it's because the result of date_trunc depends on the time zone setting for the session. I understand the reasoning, but _under the conditions_ it is being used by the OP it could have been immutable, right? The index values will still match up with the queried values if they are in the same time zone. I'm not asking to change it back to immutable (it isn't), I just realized that the stability of functions may actually be conditional. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Feb 19, 2007, at 18:04 , Alban Hertroys wrote: Michael Glaesemann wrote: On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote: What I don't understand, however, is exactly *why* date_trunc is not immutable ? I believe it's because the result of date_trunc depends on the time zone setting for the session. I understand the reasoning, but _under the conditions_ it is being used by the OP it could have been immutable, right? *Under the conditions* doesn't really make sense wrt immutable functions. Immutable means is immutable under all conditions. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Feb 18, 2007, at 23:12 , Karsten Hilbert wrote:
On Sun, Feb 18, 2007 at 09:19:43PM +0900, Michael Glaesemann wrote:
What I don't understand, however, is exactly *why* date_trunc is
not immutable ?
I believe it's because the result of date_trunc depends on the time
zone setting for the session.
...
So, given the same arguments, ('day', and current_timestamp),
date_trunc is returning two different results. (Casting to date has
the same issue.)
Ah, I see. That makes sense.
Now, if I'd write a
date_trunc_utc(precision, timestamp with time zone)
which converts input timestamps to UTC I could fairly safely
mark that IMMUTABLE, no ?
Yeah, I think if you normalized it to UTC you could mark your new
function as immutable.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] complex referential integrity constraints
Robert Haas wrote: So, I have the following problem. Suppose you have two kinds of animals, sheep and wolves. Since they have very similar properties, you create a single table to hold both kinds of animals, and an animal_type table to specify the type of each animal: CREATE TABLE animal_type ( idinteger not null, name varchar(80) not null, primary key (id) ); INSERT INTO animal_type VALUES (1, 'Sheep'); INSERT INTO animal_type VALUES (2, 'Wolf'); CREATE TABLE animal ( idserial, type_id integer not null references animal_type (id), name varchar(80) not null, age integer not null, weight_in_pounds integer not null, primary key (id) ); The animal_type table is more or less written in stone, but the animal table will be updated frequently. Now, let's suppose that we want to keep track of all of the cases where one animal is mauled by another animal: I kind of get the feeling that you'd want it like this: CREATE TABLE predator ( ... ) INHERITS animal; And then put your foreign key constraints from predator to mauling. You may want to be more accurate about what kind of animals sheep are as well. I haven't really given this much thought though, I'm just quickly reading my mail before starting work ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
Michael Glaesemann wrote: On Feb 19, 2007, at 18:04 , Alban Hertroys wrote: Michael Glaesemann wrote: On Feb 18, 2007, at 20:29 , Karsten Hilbert wrote: What I don't understand, however, is exactly *why* date_trunc is not immutable ? I believe it's because the result of date_trunc depends on the time zone setting for the session. I understand the reasoning, but _under the conditions_ it is being used by the OP it could have been immutable, right? *Under the conditions* doesn't really make sense wrt immutable functions. Immutable means is immutable under all conditions. What I'm trying to say is not that it _is_ immutable, but that it _behaves_ immutable (under said conditions). This could imply that if a certain condition is available in a query on which such a function operates, it would behave immutable. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] open source - content management system - that uses PostGreSQL
On Sun, 2007-02-18 at 09:59 -0500, John DeSoi wrote: On Feb 16, 2007, at 4:13 PM, Andrew Kirkness wrote: I am currently setting up a website and have PostGreSQL database I'm using for the backend. I'm researching an open source Content Management System that uses PostGreSQL. Do you have any recommendations? Drupal is excellent and supports PostgreSQL: http://drupal.org Here is a link about installing Drupal with PostgreSQL: http://pgedit.com/install_drupal I'd also highly recommend eZpublish from eZsystems. http://ez.no Andy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 10:16:12AM +0100, Alban Hertroys wrote: What I'm trying to say is not that it _is_ immutable, but that it _behaves_ immutable (under said conditions). This could imply that if a certain condition is available in a query on which such a function operates, it would behave immutable. That is precisely why I didn't get the idea upfront that date_trunc() wouldn't be immutable just so. I'll solve it with a date_trunc_utc() wrapper. Thanks to all who chipped in. Something new to learn every day. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 10:36:36AM +0100, Karsten Hilbert wrote: On Mon, Feb 19, 2007 at 10:16:12AM +0100, Alban Hertroys wrote: What I'm trying to say is not that it _is_ immutable, but that it _behaves_ immutable (under said conditions). This could imply that if a certain condition is available in a query on which such a function operates, it would behave immutable. That is precisely why I didn't get the idea upfront that date_trunc() wouldn't be immutable just so. I'll solve it with a date_trunc_utc() wrapper. It should be noted the date_truc(timestamptz) is not immutable, whereas date_trunc(timestamp) is. Thus you should be able to make an index on: date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' ) OTOH, if you're only storing times in UTC, then timestamp without timezone might be better anyway. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] complex referential integrity constraints
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of elein
Sent: zondag 18 februari 2007 23:16
To: Robert Haas
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote:
So, I have the following problem.
Suppose you have two kinds of animals, sheep and wolves. Since they
have very similar properties, you create a single table to hold both
kinds of animals, and an animal_type table to specify the
type of each
animal:
CREATE TABLE animal_type (
id integer not null,
name varchar(80) not null,
primary key (id)
);
INSERT INTO animal_type VALUES (1, 'Sheep'); INSERT INTO animal_type
VALUES (2, 'Wolf');
CREATE TABLE animal (
id serial,
type_id integer not null references
animal_type (id),
name varchar(80) not null,
age integer not null,
weight_in_pounds integer not null,
primary key (id)
);
The animal_type table is more or less written in stone, but
the animal
table will be updated frequently. Now, let's suppose that
we want to
keep track of all of the cases where one animal is mauled by another
animal:
CREATE TABLE mauling (
id serial,
attacker_id integer not null references animal (id),
victim_id integer not null references animal (id),
attack_time timestamp not null,
primary key (id)
);
The problem with this is that I have a very unsettled feeling about
the foreign key constraints on this table. The victim_id constraint
is fine, but the attacker_id constraint is really
inadequate, because
the attacker CAN NEVER BE A SHEEP. I really want a way to write a
constraint that says that the attacker must be an animal, but
specifically, a wolf.
It would be really nice to be able to write:
FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id)
Or:
CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2;
-- and then
FOREIGN KEY (attacker_id) REFERENCES INDEX wolves
...but that's entirely speculative syntax. I don't think
there's any
easy way to do this. (Please tell me I'm wrong.)
The problem really comes in when people start modifying the animal
table. Every once in a while we have a case where we record
something
as a wolf, but it turns out to have been a sheep in wolf's
clothing.
In this case, we want to do something like this:
UPDATE animal SET type_id = 1 WHERE id = 572;
HOWEVER, this operation MUST NOT be allowed if it turns out
there is a
row in the mauling table where attacker_id = 572, because that would
violate my integrity constraints that says that sheep do not maul.
Any suggestions? I've thought about creating rules or triggers to
check the conditions, but I'm scared that this could either (a) get
really complicated when there are a lot more tables and constraints
involved or
(b) introduce race conditions.
Why don't you add a field in animal_types that is boolean mauler.
Then you can add a trigger on the mauling table to raise an
error when the attacker_id is an animal type mauler.
This is only partial. You need a lot more triggers to guarentee the
constraints are enforced.
Precisely you need to validate:
* mauling on insert/update of attacker_id
* animal on update of type_id
* animal_type on update of your property
Of course you need to think about the MVCC model, such that:
Transaction 1 executes
INSERT INTO mauling VALUES ('someattacker'),
Transaction 2 executes
UPDATE animal_type SET mauler = false WHERE name = 'someattacker',
such that both transaction happen in parallel.
This is perfectly possible and will make it possible to violate the
constraint, UNLESS locking of the tuples is done correctly.
These contraints are not trivial to implement (unfortunally). It would
be great if they where.
- Joris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 10:58:50AM +0100, Martijn van Oosterhout wrote: I'll solve it with a date_trunc_utc() wrapper. It should be noted the date_truc(timestamptz) is not immutable, whereas date_trunc(timestamp) is. Thus you should be able to make an index on: date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' ) Ah, that makes it clear *why* this should work. I would assume to get meaningful results from a query using that index I'd have to normalize input timestamps to UTC, too, before putting them into the query, right ? OTOH, if you're only storing times in UTC, then timestamp without timezone might be better anyway. Well, PostgreSQL itself is storing UTC anyways but we need the timezone bit since our frontend delivers timestamps from various timezones and they are note normalized to UTC before they get to the database. IOW, I want the database to force programmers to have to think about from which timezone they deliver timestamps into a date-of-birth field into. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 12:03:07PM +0100, Karsten Hilbert wrote: date_trunc( timestamptz_column AT TIME ZONE 'UTC', 'foo' ) Ah, that makes it clear *why* this should work. I would assume to get meaningful results from a query using that index I'd have to normalize input timestamps to UTC, too, before putting them into the query, right ? Well, your queries need to use the same form, ie: SELECT blah FROM foo WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' ) That seems a bit error prone though, so your idea of making a simple SQL function to wrap it will probably save you much heartache. It will also make it clearer to people reading the code *why* it is written that way. Well, PostgreSQL itself is storing UTC anyways but we need the timezone bit since our frontend delivers timestamps from various timezones and they are note normalized to UTC before they get to the database. Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the index is probably easier than everywhere else. Just checking you'd thought about it. :) IOW, I want the database to force programmers to have to think about from which timezone they deliver timestamps into a date-of-birth field into. Right. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 12:53:15PM +0100, Martijn van Oosterhout wrote: Well, your queries need to use the same form, ie: SELECT blah FROM foo WHERE date_trunc( 'entered_timestamp'::timestamptz AT TIME ZONE 'UTC', 'foo' ) Thought so. That seems a bit error prone though, so your idea of making a simple SQL function to wrap it will probably save you much heartache. It will also make it clearer to people reading the code *why* it is written that way. Yep, and the COMMENT ON FUCNTION provides for a nice place to document it :-) Well, PostgreSQL itself is storing UTC anyways but we need the timezone bit since our frontend delivers timestamps from various timezones and they are note normalized to UTC before they get to the database. Yeah, the AT TIME ZONE 'UTC' needs to be put somewhere, and in the index is probably easier than everywhere else. Just checking you'd thought about it. :) Thanks ! Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Small request re error message
Bruce Momjian wrote: Scott Ribe wrote: Oh. Yea, I can see that, but even if the endian-ness is the same, it still might not work. Even a different compiler flag will cause a failure to run properly. Sure. You can't flag every possible error. But my Intel PPC Macs look identical, and I compile with identical flags. So it would help people like me, all one or two of us ;-) What would make more sense than printing the hex is to print a specific message if the endian-ness doesn't match, but I am worried people might assume it will work when the endian does match. That doesn't make much sense because we give different error messages, each telling that one little check failed. I think adding the hex code is not helpful in the general case, but maybe we could check for endianness if the control version fails, and add that info in a HINT or something. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Synchronize tables question....
On Feb 19, 2007, at 9:05 AM, Richard Huxton wrote:
Don't forget to CC: the list
Jerry LeVan wrote:
Is there an elegant way I can merge/update the two tables so that
they will contain the same information ( with no duplicates or
omissions)?
It sounds like you'll want some form of replication, but whether
single-master or multi-master no-one can say without more
information.
I meant to say I had dblink installed :)
The following sequence of steps seems to work ok, the tables are
very small...
insert into tmpRegistrations select *
from dblink('select * from registrations ')
as (software text,
id text,
value text,
location text
)
select dblink_disconnect()
create temp table newregistrations as
(select * from registrations union select * from
tmpregistrations )
truncate table registrations
insert into registrations select * from newregistrations
A couple of points:
1. What if a registration is updated?
2. What happens if you get a new registration between CREATE TEMP
TABLE and TRUNCATE?
--
Richard Huxton
Archonet Ltd
While a generic solution would be nice, I am the only one using the
DB :)
(so point 2 is unlikely).
Point 1 will require a bit of thought..., currently I would have to do
a manual scan of the (small) table after the merge.
Thanks
Jerry
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
Alban Hertroys [EMAIL PROTECTED] writes: What I'm trying to say is not that it _is_ immutable, but that it _behaves_ immutable (under said conditions). This could imply that if a certain condition is available in a query on which such a function operates, it would behave immutable. Right, but we don't have any way to represent such a fact in date_trunc's pg_proc entry, so we have to mark it as not immutable. There was a related discussion awhile ago when designing the current set of what time is it functions --- transaction_timestamp(), statement_timestamp(), and clock_timestamp(). The original proposal had just a single function that took a parameter telling which value you wanted. The trouble with that was that it'd have had to be marked volatile, thereby defeating any ability to optimize conditions using it. By splitting into three functions, we were able to limit the volatile label to clock_timestamp(). regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Per Database Roles
Hello, Is there a way to attach roles to only certain databases so that the login [from PHP pg_connect(username, password, database)] is tied to that particular database and any creation of roles (users/groups) can be constrained into that particular database. I plan to use the roles system to be able to create the users/groups access/permissions and I would like to have them isolated on a per database basis instead of having them in a situation that Role A (user) belonging to DB C could also be used in DB D (security issue). I've seen this from the docs : db_user_namespace (boolean) This parameter enables per-database user names. It is off by default. This parameter can only be set in the postgresql.conf file or on the server command line. If this is on, you should create users as [EMAIL PROTECTED] When username is passed by a connecting client, @ and the database name are appended to the user name and that database-specific user name is looked up by the server. Note that when you create users with names containing @ within the SQL environment, you will need to quote the user name. With this parameter enabled, you can still create ordinary global users. Simply append @ when specifying the user name in the client. The @ will be stripped off before the user name is looked up by the server. *Note: * This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed. But in the CREATE ROLE portion of the docs it talks about them being global and doesn't mention anything about creating them as [EMAIL PROTECTED] to tie them to that particular DB. Thanks David
[GENERAL] RPM compat-postgresql-libs-4-2 for IA-64
Hello List, I work with PostgreSQL 8.2.3 on a IA-64 server with Red Hat Enterprise Linux 4 AS update 2 and I am looking for the RPM compat-postgresql-libs-4-2 associated. Unfortunately, I didn't find it on http://www.postgresql.org/ftp/binary/, even in the RPM available for the 8.2.1 release at http://www.postgresql.org/ftp/binary/v8.2.1/linux/rpms/redhat/rhel-as-4-ia64/... Where can I find the RPM compat-postgresql-libs-4-2 for RHEL4-AS and IA-64 ? Or where can I find the RPM source in order to rebuild it on my server ? With the good tar.gz and .spec files, I could rebuild it and give it to the community. Any help would be appreciated... Regards, Alexandra ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to force disconnections from a Perl script?
I have a Perl script that runs every night and updates a local Pg database, sitting on a Linux server. (I'll refer to this database as mydb in the following.) The update process takes about 1 hour, so the script first builds a temporary database called mydb_tmp. Once mydb_tmp is built and passes a battery of tests, the script deletes mydb and renames mydb_tmp to mydb. The script is quite solid and has been performing flawlessly for several months now, with one exception: it fails irrecoverably whenever some user forgets to disconnect from mydb at the time that the script attempts to delete it (or rename it, for that matter). The error is ERROR: database mydb is being accessed by other users. Now, we, the users of mydb, know very well that we should disconnect from it at the end of the day, but inevitably one of us forgets (including myself on occasion, I'm sorry to admit). My question is, how can I make the script handle this situation more robustly? (At the moment I do get an email message alerting to this failure when it happens, but I'd like to eliminate this type of failure altogether. It is, after all, a pretty silly reason for this script to fail.) The ideal solution, from my point of view, would be for the script to forcibly disconnect everyone from mydb at the time of updating it, maybe sending a warning a minute or so beforehand, but I have not hit upon a way to do this. (I should point out that, in the case of this particular database, mydb, such forcible disconnections would cause no major disruption to anyone.) I would greatly appreciate your ideas and suggestions. FWIW, the script is currently run by my uid, but I could have it run by the postgres user, if that's of any help here. Thanks in advance! kj ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to force disconnections from a Perl script?
On Mon, Feb 19, 2007 at 11:10:55AM -0500, Kynn Jones wrote: The script is quite solid and has been performing flawlessly for several months now, with one exception: it fails irrecoverably whenever some user forgets to disconnect from mydb at the time that the script attempts to delete it (or rename it, for that matter). The error is ERROR: database mydb is being accessed by other users. Why irrecoverably? If the command fails, you just wait and try it again. You could use the pg_stat tables to work out who is connected and use pg_cancel_backend() to kill them. You could kill -INT them yourself. You could change the pg_hba.conf to forbid logging in and then bouncing the server. Hope this gives you some ideas. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to force disconnections from a Perl script?
Why irrecoverably? If the command fails, you just wait and try it again. You could use the pg_stat tables to work out who is connected and use pg_cancel_backend() to kill them. You could kill -INT them yourself. You could change the pg_hba.conf to forbid logging in and then bouncing the server. I was going to suggest the same things you did, but then I thought better... the OP is running the thing at night from a client box, not on the DB, so restart and process listing is probably off limits... There's 0 chance somebody will close at midnight it's open connection forgotten when he left office, so wait and retry would not do any good ;-) And pg_stat will only show you running queries, not the idle connections. If you only could list all the connection's pids in a client you could loop and kill them all. Of course the loop would kill itself too if not careful enough ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] open source - content management system - that uses PostGreSQL
On Feb 16, 2007, at 4:13 PM, Andrew Kirkness wrote: I am currently setting up a website and have PostGreSQL database I'm using for the backend. I'm researching an open source Content Management System that uses PostGreSQL. Do you have any recommendations? You need to define what you want your CMS to do, before you get good recommendations. We've investigated a bunch of systems for publishing a magazine-type site, http://www.morebusiness.com/ and have discovered that pretty much all of them like to work with mysql :-( I think this is because many of the designers of the free, lower-end, software don't truly appreciate the relational SQL model and treat the DB as a dumb store. Once you move higher-up in the chain, you start to see better data models, and they lean toward using Pg instead... I can't figure out what you want to do with customer data and the CMS. Without knowing that, nobody can really say anything meaningful to you. Are your customers providing the content? Despite this lack of clarity, I can recommend that you first define the features you want and then evaluate the systems based on those features being available. Then all else being equal, use the preferred DB as your tie breaker. I wouldn't rule out some good software just because it uses mysql on the back-end. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] open source - content management system - that uses PostGreSQL
Vivek Khera wrote: On Feb 16, 2007, at 4:13 PM, Andrew Kirkness wrote: I am currently setting up a website and have PostGreSQL database I'm using for the backend. I'm researching an open source Content Management System that uses PostGreSQL. Do you have any recommendations? You need to define what you want your CMS to do, before you get good recommendations. We've investigated a bunch of systems for publishing a magazine-type site, http://www.morebusiness.com/ and have discovered that pretty much all of them like to work with mysql :-( I think this is because many of the designers of the free, lower-end, software don't truly appreciate the relational SQL model and treat the DB as a dumb store. Once you move higher-up in the chain, you start to see better data models, and they lean toward using Pg instead... I can't figure out what you want to do with customer data and the CMS. Without knowing that, nobody can really say anything meaningful to you. Are your customers providing the content? Despite this lack of clarity, I can recommend that you first define the features you want and then evaluate the systems based on those features being available. Then all else being equal, use the preferred DB as your tie breaker. I wouldn't rule out some good software just because it uses mysql on the back-end. Bricolage uses PostgreSQL as its backend. http://www.bricolage.cc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] complex referential integrity constraints
On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote:
So, I have the following problem.
Suppose you have two kinds of animals, sheep and wolves. Since they
have very similar properties, you create a single table to hold both
kinds of animals, and an animal_type table to specify the type of each
animal:
CREATE TABLE animal_type (
idinteger not null,
name varchar(80) not null,
primary key (id)
);
INSERT INTO animal_type VALUES (1, 'Sheep');
INSERT INTO animal_type VALUES (2, 'Wolf');
CREATE TABLE animal (
idserial,
type_id integer not null references animal_type (id),
name varchar(80) not null,
age integer not null,
weight_in_pounds integer not null,
primary key (id)
);
The animal_type table is more or less written in stone, but the animal
table will be updated frequently. Now, let's suppose that we want to
keep track of all of the cases where one animal is mauled by another
animal:
CREATE TABLE mauling (
id serial,
attacker_id integer not null references animal (id),
victim_id integer not null references animal (id),
attack_time timestamp not null,
primary key (id)
);
The problem with this is that I have a very unsettled feeling about the
foreign key constraints on this table. The victim_id constraint is
fine, but the attacker_id constraint is really inadequate, because the
attacker CAN NEVER BE A SHEEP. I really want a way to write a
constraint that says that the attacker must be an animal, but
specifically, a wolf.
It would be really nice to be able to write:
FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id)
Or:
CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2;
-- and then
FOREIGN KEY (attacker_id) REFERENCES INDEX wolves
...but that's entirely speculative syntax. I don't think there's any
easy way to do this. (Please tell me I'm wrong.)
The problem really comes in when people start modifying the animal
table. Every once in a while we have a case where we record something
as a wolf, but it turns out to have been a sheep in wolf's clothing. In
this case, we want to do something like this:
UPDATE animal SET type_id = 1 WHERE id = 572;
HOWEVER, this operation MUST NOT be allowed if it turns out there is a
row in the mauling table where attacker_id = 572, because that would
violate my integrity constraints that says that sheep do not maul.
Any suggestions? I've thought about creating rules or triggers to check
the conditions, but I'm scared that this could either (a) get really
complicated when there are a lot more tables and constraints involved or
(b) introduce race conditions.
Thanks,
...Robert
I'd do something like this:
CREATE TABLE animal_type (
animal_name TEXT PRIMARY KEY,
CHECK(animal_name = trim(animal_name))
);
/* Only one of {Wolf,wolf} can be in the table. */
CREATE UNIQUE INDEX just_one_animal_name
ON animal_type(LOWER(animal_name));
CREATE TABLE predator (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE prey (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE mauling (
id SERIAL PRIMARY KEY,
attacker_idINTEGER NOT NULL REFERENCES predator (animal_type_id),
victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id),
attack_timeTIMESTAMP WITH TIME ZONE NOT NULL
);
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] How to force disconnections from a Perl script?
Hi, you could let the script look into the output of ps aux. Open idle connections are usually show like this: postgres 18383 0.0 0.6 18596 4900 ?Ss 16:38 0:00 postgres: dbuser database hostname(39784) idle in transaction Then you can simply collect the PIDs and kill these processes (just kill, not with -9). If there are no demons lurking behind them reestablishing the connections, this is a quite reliable way to get rid of connections in a graceful way. Regards, Frank. On Mon, 19 Feb 2007 11:10:55 -0500 Kynn Jones [EMAIL PROTECTED] thought long, then sat down and wrote: I have a Perl script that runs every night and updates a local Pg database, sitting on a Linux server. (I'll refer to this database as mydb in the following.) The update process takes about 1 hour, so the script first builds a temporary database called mydb_tmp. Once mydb_tmp is built and passes a battery of tests, the script deletes mydb and renames mydb_tmp to mydb. The script is quite solid and has been performing flawlessly for several months now, with one exception: it fails irrecoverably whenever some user forgets to disconnect from mydb at the time that the script attempts to delete it (or rename it, for that matter). The error is ERROR: database mydb is being accessed by other users. Now, we, the users of mydb, know very well that we should disconnect from it at the end of the day, but inevitably one of us forgets (including myself on occasion, I'm sorry to admit). My question is, how can I make the script handle this situation more robustly? (At the moment I do get an email message alerting to this failure when it happens, but I'd like to eliminate this type of failure altogether. It is, after all, a pretty silly reason for this script to fail.) The ideal solution, from my point of view, would be for the script to forcibly disconnect everyone from mydb at the time of updating it, maybe sending a warning a minute or so beforehand, but I have not hit upon a way to do this. (I should point out that, in the case of this particular database, mydb, such forcible disconnections would cause no major disruption to anyone.) I would greatly appreciate your ideas and suggestions. FWIW, the script is currently run by my uid, but I could have it run by the postgres user, if that's of any help here. Thanks in advance! kj ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606Mail: [EMAIL PROTECTED] Telefax: 0271 231 8608Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651 pgpZg2f8BOaE8.pgp Description: PGP signature
Re: [GENERAL] complex referential integrity constraints
I'd do something like this:
CREATE TABLE animal_type (
animal_name TEXT PRIMARY KEY,
CHECK(animal_name = trim(animal_name))
);
/* Only one of {Wolf,wolf} can be in the table. */
CREATE UNIQUE INDEX just_one_animal_name
ON animal_type(LOWER(animal_name));
CREATE TABLE predator (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE prey (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE mauling (
id SERIAL PRIMARY KEY,
attacker_idINTEGER NOT NULL REFERENCES predator (animal_type_id),
victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id),
attack_timeTIMESTAMP WITH TIME ZONE NOT NULL
);
Just to add to David's idea, I would create two update-able views that joined
animal to predator
and another for animal to prey. This way, you only have to
insert/update/delete from 1
update-able view rather than two tables.
Of course, I am still waiting for the future version of postgresql that will
handle this
functionality seamlessly using table inheritance. :-)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Out of memory on vacuum analyze
I have a large table (~55 million rows) and I'm trying to create an index and vacuum analyze it. The index has now been created, but the vacuum analyze is failing with the following error: ERROR: out of memory DETAIL: Failed on request of size 943718400. I've played with several settings, but I'm not sure what I need to set to get this to operate. I'm running on a dual Quad core system with 4GB of memory and Postgresql 8.2.3 on W2K3 Server R2 32bit. Maintenance_work_mem is 900MB Max_stack_depth is 3MB Shared_buffers is 900MB Temp_buffers is 32MB Work_mem is 16MB Max_fsm_pages is 204800 Max_connections is 50 Any help would be greatly appreciated. Thanks, John Cole -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.441 / Virus Database: 268.18.2/692 - Release Date: 2/18/2007 4:35 PM This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Sun, Feb 18, 2007 at 12:29:17 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: The date-of-birth field in our table holding patients is of type timestamp with time zone. One of our patient search queries uses the date-of-birth field to find matches. Since users enter day, month, and year but not hour, minute, and second of the DOB we run the query with That seems like an odd choice. Is there some reason they didn't use a type of date? Maybe you could get them to change it? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Out of memory on vacuum analyze
On Mon, 2007-02-19 at 12:47 -0600, John Cole wrote: I have a large table (~55 million rows) and I'm trying to create an index and vacuum analyze it. The index has now been created, but the vacuum analyze is failing with the following error: ERROR: out of memory DETAIL: Failed on request of size 943718400. I've played with several settings, but I'm not sure what I need to set to get this to operate. I'm running on a dual Quad core system with 4GB of memory and Postgresql 8.2.3 on W2K3 Server R2 32bit. Maintenance_work_mem is 900MB Max_stack_depth is 3MB Shared_buffers is 900MB Temp_buffers is 32MB Work_mem is 16MB Max_fsm_pages is 204800 Max_connections is 50 You told PostgreSQL that you have 900MB available for maintenance_work_mem, but your OS is denying the request. Try *lowering* that setting to something that your OS will allow. That seems like an awfully high setting to me. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database performance comparison paper.
On Sat, Feb 17, 2007 at 12:02:08AM +0100, Leif B. Kristensen wrote: There ought to be a proper name for this kind of pseudo-technical Gonzo journalism. There is, but it's not the sort of word one uses in polite company ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 12:41:11PM -0600, Bruno Wolff III wrote: Karsten Hilbert [EMAIL PROTECTED] wrote: The date-of-birth field in our table holding patients is of type timestamp with time zone. One of our patient search queries uses the date-of-birth field to find matches. Since users enter day, month, and year but not hour, minute, and second of the DOB we run the query with That seems like an odd choice. Is there some reason they didn't use a type of date? Maybe you could get them to change it? What time of day were you born ? http://en.wikipedia.org/wiki/Apgar What is the technical reason that makes you wonder ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Per Database Roles
On Feb 19, 2007, at 10:32 AM, David Legault wrote: Is there a way to attach roles to only certain databases so that the login [from PHP pg_connect(username, password, database)] is tied to that particular database and any creation of roles (users/ groups) can be constrained into that particular database. I plan to use the roles system to be able to create the users/ groups access/permissions and I would like to have them isolated on a per database basis instead of having them in a situation that Role A (user) belonging to DB C could also be used in DB D (security issue). You can GRANT and REVOKE on database connect privileges. See http://www.postgresql.org/docs/8.2/interactive/sql-grant.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-es-ayuda] postgreSQL
amigo en este enlace te explican paso a paso como instalar postgresql, yo tengo debian e instale postgre 8.2.1 siguiendo los pasos de dicho link. a ver que te parece: http://www.postgresql.org.mx/?q=node/9 From: Edwin Quijada [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: [pgsql-es-ayuda] postgreSQL Date: Mon, 19 Feb 2007 14:52:00 + Hola a todos ! Tengo instalodo Debian 3.1, Quiero instalar una version superior postgresql 8.1, la version de postgresql que dispongo de mi cd. es postgresql 7.4.7. Mi pregunta es solo puedo instalar postgresql en version tar Les agradesco cualquier comentario o sugerencia! Gracias Jorge de la pena En los backports creo q esta la version 8.0.7. Al menos de ahi la instale hace un tiempo. ---(fin del mensaje)--- TIP 8: explain analyze es tu amigo _ ¿Cuánto vale tu auto? Tips para mantener tu carro. ¡De todo en MSN Latino Autos! http://latino.msn.com/autos/ ---(fin del mensaje)--- TIP 2: puedes desuscribirte de todas las listas simultáneamente (envíe unregister TuDirecciónDeCorreo a [EMAIL PROTECTED]) _ Descarga gratis la Barra de Herramientas de MSN http://www.msn.es/usuario/busqueda/barra?XAPID=2031DI=1055SU=http%3A//www.hotmail.comHL=LINKTAG1OPENINGTEXT_MSNBH ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Postgresql Kilitli Satır Kontrolü
Merhaba,
PlanetPostgreSQLi incelerken şu örnek gözüme çarptı
Greg Sabino Mullane
Determining which rows in a table are locked
CREATE OR REPLACE FUNCTION isrowlocked(text,text,text)
RETURNS BOOL
LANGUAGE plpgsql
VOLATILE
STRICT
AS
$gsm$
DECLARE
myst TEXT;
BEGIN
myst = 'SELECT 1 FROM '||quote_ident($1)||' WHERE ' ||quote_ident($2)||' =
'||$3||' FOR UPDATE NOWAIT';
EXECUTE myst;
RETURN FALSE;
EXCEPTION WHEN lock_not_available THEN
RETURN true;
END;
$gsm$;
pp=# BEGIN;
pp=# UPDATE soar SET id=id WHERE id=2;
pp=# SELECT isrowlocked('soar','id',1);
isrowlocked
-
f
pp=# SELECT isrowlocked('soar','id',2);
isrowlocked
-
1. bu kullanım şekli etik mi ? ( yani doğru kullanım şekli bu mu ? )
yukarıdaki örnekten anladığım kadarıyla bir kilit sorgusu gönderiliyor
dönen hata yakalanarak boolean bir sonuç elde ediliyor.
2. pg_locks view' ini kullanarak, kilitli satırın bulunduğu veritabanının ve
tablonun oid numaralarını alabiliyorum,
pg_locks view'inde kilitlenen satırında oid'ini görebilmek mümkünmü, eğer
row oid eklenebilirse böyle
manuel yollara gerek kalmayacağı kanaatindeyim.
İyi Çalışmalar
Don't 'kill -9' the postmaster
Re: [GENERAL] RPM compat-postgresql-libs-4-2 for IA-64
Hello, On Mon, 2007-02-19 at 16:49 +0100, DANTE Alexandra wrote: Where can I find the RPM compat-postgresql-libs-4-2 for RHEL4-AS and IA-64 ? Those libs are extracted from 8.1.X RPMs and put together to form an RPM package. If you can build and send us 8.1.8 RPMs, I can build and upload that compat RPM. Also, I'd be happy if you again share 8.2.3 RPMs with us :) Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 20:48:07 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: What time of day were you born ? http://en.wikipedia.org/wiki/Apgar What is the technical reason that makes you wonder ? Because it would make doing the queries simpler. If you aren't collecting the data, it doesn't make sense to deal with the extra headaches involved with pretending you know what time of day someone was born. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] boolean operator on interval producing strange results
We updated our production server to postgresql 8.2.3 yesterday. This query is giving different results than on our development box: development: # select ((now() - '1 day'::interval)::timestamp - now()) 0; ?column? -- t (1 row) production # select ((now() - '1 day'::interval)::timestamp - now()) 0; ?column? -- f -- looks busted to me (1 row) both servers are redhat fc4, same version postgresql. only difference I know of is development is a little behind on yum update. can anybody think of anything that might have influenced this? merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
On Mon, Feb 19, 2007 at 03:28:01PM -0600, Bruno Wolff III wrote: What is the technical reason that makes you wonder ? Because it would make doing the queries simpler. If you aren't collecting the data, it doesn't make sense to deal with the extra headaches involved with pretending you know what time of day someone was born. Oh, I see. When I said that users don't enter the hour and minute that was targetted at search time. They do enter the time part when entering a new patient, of course. So, it's surely collected. It's just not used for searching. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Database performance comparison paper.
Tom Allison wrote: Leif B. Kristensen wrote: On Friday 16. February 2007 07:10, Tom Lane wrote: Perhaps this paper can be described as comparing an F-15 to a 747 on the basis of required runway length. There ought to be a proper name for this kind of pseudo-technical Gonzo journalism. The Internet is full of it. advertalism? Lies? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database performance comparison paper.
On 2/16/2007 1:10 AM, Tom Lane wrote: extra points, use *only one* test case. Perhaps this paper can be described as comparing an F-15 to a 747 on the basis of required runway length. Oh, this one wasn't about raw speed of trivial single table statements like all the others? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pgPL/SQL Documentation
Hi, I have had a look around but I have not found any documentation on pgPL/SQL. I know it is meant to be similar to Oracle's PL/SQL but as I don't know PL/SQL so that does not do me any good. If there is documentation for pgPL/SQL it would be nice to see it as part of the documentation for the rest of pgPL/SQL. Regards, Ivan Wills ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgPL/SQL Documentation
Ivan Wills wrote: Hi, I have had a look around but I have not found any documentation on pgPL/SQL. I know it is meant to be similar to Oracle's PL/SQL but as I don't know PL/SQL so that does not do me any good. If there is documentation for pgPL/SQL it would be nice to see it as part of the documentation for the rest of pgPL/SQL. There is no such thing as pgPL/SQL. There is PL/SQL and PL/PGSQL both of which are documented in the main docs. Joshua D. Drake Regards, Ivan Wills ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
I have a question about the query optimizer of a postgres. As long as I understood through a postgres manual, the postgres query optimizer is implemented using a *genetic algorithm.* I'm thinking to modify the query optimizer. Are there any postgres version which uses typical dynamic programming approach for query optimization? Also, are there any body who have tried to modify the optimizer? jungmin
Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?
jungmin shin escribió: I have a question about the query optimizer of a postgres. As long as I understood through a postgres manual, the postgres query optimizer is implemented using a *genetic algorithm.* There is an algorithm said to be genetic, but it only kicks in with big joins; 12 tables on the default configuration. On queries with less tables, the optimizer uses exhaustive search and lots of smarts. See http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/README?rev=1.39;content-type=text%2Fplain I'm thinking to modify the query optimizer. Are there any postgres version which uses typical dynamic programming approach for query optimization? Also, are there any body who have tried to modify the optimizer? Sure, we have a very active optimizer hacker. He is too clever for the rest of us to follow though :-( (I should speak only for myself here of course). He goes by the name of Tom Lane. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to force disconnections from a Perl script?
Csaba Nagy [EMAIL PROTECTED] writes: And pg_stat will only show you running queries, not the idle connections. Nonsense. pg_stat_activity + kill -TERM should solve this problem reasonably well. Some of us don't trust kill -TERM 100%, which is why it's not currently exposed as a standard function, but if you're using a reasonably recent PG release it's probably safe. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] complex referential integrity constraints
On Mon, Feb 19, 2007 at 10:52:51AM -0800, Richard Broersma Jr wrote:
I'd do something like this:
CREATE TABLE animal_type (
animal_name TEXT PRIMARY KEY,
CHECK(animal_name = trim(animal_name))
);
/* Only one of {Wolf,wolf} can be in the table. */
CREATE UNIQUE INDEX just_one_animal_name
ON animal_type(LOWER(animal_name));
CREATE TABLE predator (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE prey (
animal_name TEXT NOT NULL
REFERENCES animal_type(animal_name)
ON DELETE CASCADE,
PRIMARY KEY(animal_name)
);
CREATE TABLE mauling (
id SERIAL PRIMARY KEY,
attacker_idINTEGER NOT NULL REFERENCES predator (animal_type_id),
victim_id INTEGER NOT NULL REFERENCES prey (animal_type_id),
attack_timeTIMESTAMP WITH TIME ZONE NOT NULL
);
Just to add to David's idea, I would create two update-able views
that joined animal to predator and another for animal to prey. This
way, you only have to insert/update/delete from 1 update-able view
rather than two tables.
You could just do a rewrite RULE on predator and prey for each of
INSERT, UPDATE and DELETE that has a DO INSTEAD action that writes to
animal. This wouldn't handle COPY, though.
Of course, I am still waiting for the future version of postgresql
that will handle this functionality seamlessly using table
inheritance. :-)
You mean writeable VIEWs? I think it would be nice to have some cases
handled, but there are several kinds of VIEWs I can think of where the
only sane way to write to them is to define the writing behavior on a
case-by-case basis.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Syncing postgres data with Pocket PC
Hello, I need a way to sync a postgres view with a table on a Windows CE device. The table will be read only on the mobile device. I am seeking to replace an access database that syncs a table with a pocket pc table via active sync. I would really like to use postgres for the desktop side of things, but need to be able to syn change to the database with pocket PCs via active sync.
Re: [GENERAL] boolean operator on interval producing strange results
Merlin Moncure [EMAIL PROTECTED] writes: # select ((now() - '1 day'::interval)::timestamp - now()) 0; ?column? -- f -- looks busted to me (1 row) If you'd casted to timestamptz then I'd agree this is busted. As-is, it might have something to do with your timezone setting, which you didn't mention? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
