[SQL] query
Hello, I want to create a trigger in PostgresSQL In trigger, Before inserting the record, if data is already in the table, the trigger fire the mesaage that data is already there, and after that trigger ckeck for next insert statement. How can I do this , Please reply. Regards, _ Aftab Alam
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
delete my email from the list Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 8:29 PM To: pgsql-sql@postgresql.org Subject: [SQL] Referential integrity broken (8.0.3), sub-select help Hello, I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to "url" via FK. Somehow I ended up with some rows in B referencing non-existent rows in U. This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this happen. I'm using 8.0.3. Here are the table references I just mentioned: Table "bookmark": id SERIAL CONSTRAINT pk_bookmark_id PRIMARY KEY Table "url": url_id INTEGER CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) Problem #1: Strange that PG allowed this to happen. Maybe my DDL above allows this to happen and needs to be tightened? I thought the above would ensure referential integrity, but maybe I need to specify something else? Problem #2: I'd like to find all rows in B that point to non-existent rows in U. I can do it with the following sub-select, I believe, but it's rather inefficient (EXPLAIN shows both tables would be sequentially scanned): SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id); Is there a more efficient way to get the rows from "bookmark"? Thanks, Otis ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Power cut and performance problem
unsubscribe Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Caune Sent: Tuesday, March 21, 2006 9:44 PM To: Jeff Frost Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Power cut and performance problem > > For example, the execution of the following query is fast as it used to > > be (gslog_event_id is the primary key on gslog_event): > > > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > > > > while the following query is really slow (several minutes): > > > > select min(gslog_event_id) from gslog_event; (index on the primary key > > is taken) > > > > > > I'm not a hardware expert at all, but I supposed that the whole > > performance would be degraded when a problem occurs with RAID disks. Am > > I wrong? Could it be something else? Are there some tools that check > > the state of a PostgreSQL database? > > You would be correct, a hardware problem should manifest itself on both > those > queries. What is the explain analyze output of those two queries? It's > possible you have a corrupt index on gslog_event. If that's the case, a > reindex would likely remedy the problem. Is postgres logging any errors? > The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---(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 <> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Power cut and performance problem
unsubscribe Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeff Frost Sent: Tuesday, March 21, 2006 9:19 PM To: Daniel Caune Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Power cut and performance problem On Tue, 21 Mar 2006, Daniel Caune wrote: > For example, the execution of the following query is fast as it used to > be (gslog_event_id is the primary key on gslog_event): > > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms) > > > while the following query is really slow (several minutes): > > select min(gslog_event_id) from gslog_event; (index on the primary key > is taken) > > > I'm not a hardware expert at all, but I supposed that the whole > performance would be degraded when a problem occurs with RAID disks. Am > I wrong? Could it be something else? Are there some tools that check > the state of a PostgreSQL database? You would be correct, a hardware problem should manifest itself on both those queries. What is the explain analyze output of those two queries? It's possible you have a corrupt index on gslog_event. If that's the case, a reindex would likely remedy the problem. Is postgres logging any errors? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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 ---(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
