[GENERAL] foreign key restrictions
Hi All, on numerous times I had fell onto postgress complaining, that I try to create foreign key, pointing to a set not embraced within a unique key constraint. Here is the case: CREATE TABLE one (id int not null unique, info text); CREATE TABLE two (id int not null unique, ofone int references one(id), info text); now when I try to: CREATE TABLE three(one int not null, two int, info text, foreign key (one, two) references two (one, id)); I get the following error: ERROR: there is no unique constraint matching given keys for referenced table two But. Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair will also be unique, obviously. Naturaly I can, and I do, add the requested constraint to the table TWO, but to me it looks spurious - not providing any additional constraint (which is already quearanteed by unique(ID), just a 'syntax glue'. I must have missed something here. Can someone help me understand this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Pgpool-general] Trying to switch from PgPool1 to PgPool2
I scratched around some more, found doc/pgpool-en.html and my ignorance has been somewhat lessened. My previous config was almost right except for: pgpool2_hostname = '' #backend_data_directory0 #backend_data_directory1 and my Linux distro had assigned hostname 'slave1' to localhost, on which Pg wasn't LISTENing at time... a deal-killer :P Regards Henry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Pgpool-general] Trying to switch from PgPool1 to PgPool2
On Sun, August 10, 2008 3:03 pm, Henry wrote: I scratched around some more, found doc/pgpool-en.html and my ignorance has been somewhat lessened. oi, wrong list /blushes I really should *not* use multi-users under one login in squirrelmail... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
On Sun, Aug 10, 2008 at 1:15 AM, [EMAIL PROTECTED] wrote: CREATE TABLE two (id int not null unique, ofone int references one(id), CREATE TABLE three(one int not null, two int, info text, foreign key (one, two) references two (one, id)); I get the following error: ERROR: there is no unique constraint matching given keys for referenced table two But. Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair will also be unique, obviously. This statement is not completely true. The only part of the pair that is true is ID. Also there is not unique constraint on the pare. So there is no way to PG to build a foreing key on the pair. I must have missed something here. Can someone help me understand this? A foreign Key can only reference a field(s) that has some type of unique constraint ( primary key or unique ). Try this with table two: CREATE TABLE two ( id int not null unique, ofone int references one(id), txt text not null, PRIMARY KEY ( id, ofone )); Once you've created you two field primary key, would will be able to reference it in table three. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
Richard Broersma [EMAIL PROTECTED] writes: On Sun, Aug 10, 2008 at 1:15 AM, [EMAIL PROTECTED] wrote: Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair will also be unique, obviously. This statement is not completely true. The only part of the pair that is true is ID. Also there is not unique constraint on the pare. So there is no way to PG to build a foreing key on the pair. Uhm, afaics he's right. if ID is unique not null then ID, OFONE also has to be unique. That is, there could be duplicate values of OFONE but they'll all have different values of ID anyways. I'm not sure if there's a fundamental reason why there has to be an index that exactly matches the foreign key or not -- offhand I can't think of one. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
On Sun, Aug 10, 2008 at 07:10:10AM -0700, Richard Broersma wrote: On Sun, Aug 10, 2008 at 1:15 AM, [EMAIL PROTECTED] wrote: CREATE TABLE two (id int not null unique, ofone int references one(id), CREATE TABLE three(one int not null, two int, info text, foreign key (one, two) references two (one, id)); Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair will also be unique, obviously. This statement is not completely true. The only part of the pair that is true is ID. Also there is not unique constraint on the pare. So there is no way to PG to build a foreing key on the pair. Eh? If ID is unique, then (ID,OFONE) is also unique. You don't need to add another unique constraint because they're already guarenteed to be unique. While I admit the table structure is a bit odd, it should be fairly easy to support it in postgres. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] foreign key restrictions
Gregory Stark [EMAIL PROTECTED] writes: I'm not sure if there's a fundamental reason why there has to be an index that exactly matches the foreign key or not -- offhand I can't think of one. The reason why is that the SQL spec says so: a) If the referenced table and columns specifies a reference column list, then the set of column names contained in that reference column list shall be equal to the set of column names contained in the unique column list of a unique constraint of the referenced table. Let referenced columns be the column or columns identified by that reference column list and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. I'm not entirely sure, but I think the restrictive definition might be necessary with some of the more complex options for foreign keys, such as MATCH PARTIAL. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
Gregory Stark [EMAIL PROTECTED] writes: I'm not sure if there's a fundamental reason why there has to be an index that exactly matches the foreign key or not -- offhand I can't think of one. The reason why is that the SQL spec says so: a) If the referenced table and columns specifies a reference column list, then the set of column names contained in that reference column list shall be equal to the set of column names contained in the unique column list of a unique constraint of the referenced table. Let referenced columns be the column or columns identified by that reference column list and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. I'm not entirely sure, but I think the restrictive definition might be necessary with some of the more complex options for foreign keys, such as MATCH PARTIAL. I must admit, the standard is not very easy reading for me; what exactly does the standarad mean by unique column list: 1. is that a requirement for mathematical properties of that list, or 2. is that a requirement for explicit SQL UNIQUE INDEX existing over the entire list. Since column list is a unique column list whenever a subset of column list is a unique column list, then if interpretation nr.1 of the standard is OK, there is no real requirement to install (and require to install) an additional unique constraint on the target column list. -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
[EMAIL PROTECTED] writes: The reason why is that the SQL spec says so: a) If the referenced table and columns specifies a reference column list, then the set of column names contained in that reference column list shall be equal to the set of column names contained in the unique column list of a unique constraint of the referenced table. I must admit, the standard is not very easy reading for me; what exactly does the standarad mean by unique column list: 1. is that a requirement for mathematical properties of that list, or The point is it says shall be equal to, not shall be a superset of. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign key restrictions
[EMAIL PROTECTED] writes: The reason why is that the SQL spec says so: a) If the referenced table and columns specifies a reference column list, then the set of column names contained in that reference column list shall be equal to the set of column names contained in the unique column list of a unique constraint of the referenced table. I must admit, the standard is not very easy reading for me; what exactly does the standarad mean by unique column list: 1. is that a requirement for mathematical properties of that list, or The point is it says shall be equal to, not shall be a superset of. So its the meaning nr.2. The syntax glue not the actual math of sets. This is strange, I must say. But no further questions when this is a syntax requirement. Thenx, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
Gregory Stark [EMAIL PROTECTED] writes: That's just a special case of what would be expected to happen with memory allocation anyways though. Few allocators return memory to the OS anyways. Well, that does happen on Linux for instance. Since Matt knew in his original report that the xpath leak was intra-transaction, I assumed he must be using a platform where malloc/free can release memory back to the OS --- else he couldn't have seen that behavior from outside the backend. Still, it's entirely possible that some sort of high-water-mark is involved somewhere, perhaps in malloc's internal data structures. I was really going on a hunch, as I noticed a definite trend of postgres processes using more and more memory over time, and it only started after switching to 8.3 and starting to use xpath() quite heavily. Most of the memory data I have comes from Linux x64 systems with Postgres compiled as 64-bit. But I did also notice what appeared to be a similar trend on an OS X PPC system. In any event, I'm sorry I can't provide any more helpful tests, but I'll report back how the system changes after I've patched these systems. I'm following up on this thread from a month ago on PG 8.3 memory use. I'm afraid even after updating to 8.3.3 + this patch, I still see the same overall memory trend. You can see what I'm looking at here with a couple of memory graphs. The first comes from our old primary database server, which started on 8.1 and then we switched to 8.3. I've added a callout on the chart to when we switched to 8.3. Before the switch, the application memory use (bright green) was pretty consistent, around 250MB. After the switch, you can see the application memory trends upwards over time. http://msqr.us/pg83-mem/pg83-memlead-1.png Around June, we then switched to some new hardware. It was on this machine about a month ago I updated to 8.3.3 + the xml.c patch referred to in this thread. I've pointed this out on this chart: http://msqr.us/pg83-mem/pg83-memlead-2.png Here the application memory continues to trend upwards over time since the 8.3.3 server was started, up to 1.2GB as of now. Am I right in my understanding that in general I should not see this every-increasing use of app memory by PostgreSQL? Or will PostgreSQL slowly work towards consuming all the shared memory we've allocated (4GB in this case) and retain that? The only apps running on this box are PostgreSQL and Slony for replication. -- m@ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL: Database schema for messaging service (similar to facebook)
Hello,
I'm trying to create a messageing service, like in facebook. Basically
a member can write messages to another member. It will have three main
functions. One, basic messaging to another member. Two, notification
from system to a group of members (a list of members), Three, an
update report to a group of members (a list of members).
I was looking over the net for examples and found this schema:
(http://lh4.ggpht.com/arrival123/SJ-XLk2257I/Bhs/eRY9Nd4VLkw/
facebook_emails_model.gif)
But its a little bit odd for me because I don't need to extend from
My_Email_Logins table, actually I do not fully understand why the
schema creator decided to extends the four tables from
My_EMail_Logins.
Also, the schema will have duplicate data for Subject and Message
within the four tables (Mail_Boxes, Sent_Messages, Notifications, and
Updates)
My current schema, which is a work in progress looks like this:
CREATE SEQUENCE tm_Messages_MessageID_seq;
CREATE TABLE tm_Messages (
MessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Messages_MessageID_seq'),
SentDate timestamp NOT NULL
);
CREATE SEQUENCE tm_ReceivedMessages_ReceivedMessageID_seq;
CREATE TABLE tm_ReceivedMessages (
ReceivedMessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_ReceivedMessages_ReceivedMessageID_seq'),
Username varchar(256) NOT NULL
default '',
Subjectvarchar(128) NOT NULL,
Body text NOT NULL,
MessageReadboolean NOT NULL default
'0'
);
CREATE SEQUENCE tm_SentMessages_SentMessageID_seq;
CREATE TABLE tm_SentMessages (
SentMessageID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_SentMessages_SentMessageID_seq'),
-- MessageID integer NOT NULL REFERENCES
tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
Subjectvarchar(128) NOT NULL,
Body text NOT NULL
);
CREATE SEQUENCE tm_Notifications_NotificationID_seq;
CREATE TABLE tm_Notifications (
NotificationID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Notifications_NotificationID_seq'),
-- MessageID integer NOT NULL
REFERENCES tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
NotificationType integer NOT NULL,
FromUsername varchar(256) NOT NULL
default '',
Subjectvarchar(128) NOT NULL,
Body text NOT NULL,
NotificationCheckedboolean NOT NULL default
'0'
);
CREATE SEQUENCE tm_Updates_UpdateID_seq;
CREATE TABLE tm_Updates (
UpdateID integer NOT NULL PRIMARY
KEY DEFAULT nextval('tm_Updates_UpdateID_seq'),
-- MessageID integer NOT NULL
REFERENCES tm_Messages (MessageID),
ToUsername varchar(256) NOT NULL
default '',
FromUsername varchar(256) NOT NULL
default '',
Fullname varchar(128) NOT NULL
default,
Subjectvarchar(256) NOT NULL,
Body text NOT NULL,
MessageReadboolean NOT NULL default
'0'
);
Its very similar to the schema in the picture, but with the
My_Email_Logins table renamed as tm_Messages table, holding just the
date.
Can someone give me pointers or share their knowledge of a messaging
system they have implemented? I also found that this schema although
common is hard to google because of the query strings are ambiguous.
PS. I hope at my code will help someone out there as well.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
Matt Magoffin [EMAIL PROTECTED] writes: I'm following up on this thread from a month ago on PG 8.3 memory use. I'm afraid even after updating to 8.3.3 + this patch, I still see the same overall memory trend. You can see what I'm looking at here with a couple of memory graphs. These numbers don't even have any demonstrable connection to Postgres, let alone to an xpath-related memory leak. You're going to need to come up with a concrete test case if you want anyone to investigate. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Response time between shared buffer cache and operating system
On Thu, 7 Aug 2008, RASHA OSMAN wrote: How long it takes the operating system to fulfil a page demand, ie, reading the page from disk or from the OS cache to the Postgres shared buffer. Also how long it takes the bgwriter to flush a page from the shared buffer into the OS cache or disk. PostgreSQL doesn't actually know any of this information. Pages get read; maybe they came from disk, maybe from the OS's cache, the database doesn't know. Similarly, pages gets written to the OS cache, and PostgreSQL has no idea when that actually makes its way onto disk. Also, there is zero internal timing of these low-level operations inside the database right now. You can get statement-level timing out of PostgreSQL using things like \timing and EXPLAIN ANALYZE, that's about it. Right now you need operating system profiling tools to figure all this out. If you were using the latest development rev of PostgreSQL on Solaris/FreeBSD/MacOS, you might collect this information with dtrace, but none of that helps on the 8.1/Linux combo you're running. Maybe you could convince oprofile to collect the data you want for you on Linux. I would also suggest estimating these values by writing some SQL-based benchmark for your purposes. You could look at the pg_stat* views (see http://www.postgresql.org/docs/8.1/static/monitoring-stats.html ) to get an idea how many calls were made to the OS. If you started from a clean PostgreSQL and OS cache (stop database, remount database disk, start database), carefully controlled what you looked for via SELECT, and timed the results, you could estimate all these values from there. This would be more useful than the source-code level modifications you were asking about IMHO, because even if you had that you'd still need to go through much of the exercise I just described to figure out how to translate the per-page figures into something useful for the application you want these measurements for. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 100% CPU pg processes that don't die.
On Sat, Aug 9, 2008 at 2:54 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Sat, Aug 9, 2008 at 2:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Scott Marlowe [EMAIL PROTECTED] writes: I'm load testing a machine, and i'm seeing idle in transaction processes that are no longer hooked to any outside client, that pull 100% CPU and can't be kill -9ed. To my knowledge, the only way a process can't be kill -9'd is if it's stuck inside the kernel (typically, doing I/O to a nonresponsive disk). There's certainly no way for a userland process to defend itself against kill -9. So my immediate response would have been to look for a hardware problem, or failing that a kernel bug. I see from the subsequent thread that indeed hardware failure looks to be the answer, but that should have been your first assumption. It was before this. That's why I'd swapped the RAID cards. It's just that this is the first time this has happened without killing the box, so I wanted to be sure it didn't look like something else to anybody. Just as a followup several hours later the other machine started producing the same effects. I'm gonna go trawl through the lkml to see if they have any info on this problem. The good news is that both Centos 5.2 and Ubuntu 7.10 seem immune to this particular bug, and have been running 13 hours now without a hitch. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] mailing list/newsgroup disconnect
On Tuesday 05 August 2008 03:12:26 Sim Zacks wrote:
There seems to be a disconnect between the mailing list and the
newsgroup right now. I received a bunch of replies via email that did
not show up in the newsgroup. (I did not receive any messages that were
sent to the mailing list and not to me personally).
Is there someone I should mention this to or does he already know?
Problems like this should be reported to [EMAIL PROTECTED] It would
likely be helpful to include emails with full header information, though the
folks there can tell you what they need.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
