[SQL] DO INSTEAD in rule
Hi, In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO INSTEAD in the rule and the default value for j is 0. Am I missing something? DROP TABLE t1 CASCADE; DROP TABLE CREATE TABLE t1 ( i INTEGER, j INTEGER DEFAULT 0 ); CREATE TABLE CREATE rule t1_ins AS ON INSERT TO t1 WHERE (EXISTS (SELECT 1 FROM t1 WHERE i = new.i)) DO INSTEAD UPDATE t1 SET j = j + 1 WHERE i = new.i; CREATE RULE INSERT INTO t1 VALUES (1); INSERT 1690668 1 SELECT * FROM t1; i | j ---+--- 1 | 1 (1 row) -- Tatsuo Ishii ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] DO INSTEAD in rule
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO > INSTEAD in the rule and the default value for j is 0. Am I missing > something? > CREATE rule t1_ins AS ON INSERT TO t1 > WHERE (EXISTS (SELECT 1 FROM t1 > WHERE i = new.i)) > DO INSTEAD UPDATE t1 SET j = j + 1 > WHERE i = new.i; Hm. The problem is that the rule query runs after the INSERT and so it sees the inserted row as something to update. The logic is essentially if (not (EXISTS ...)) then do the INSERT; if (EXISTS ...) then do the UPDATE; and the second command sees the inserted row as existing, so it updates it. Without an if-then-else kind of control structure for the executor, I'm not sure we can do better. (Even with one, I'm not sure how to handle cases where the INSERT inserts multiple rows.) Consider using a trigger instead of a rule to do this. Or, accept that the UPDATE will happen unconditionally, and start J off one less than it should be. Note that either solution will have race conditions if multiple processes try to insert the same row at the same time. There are discussions in the archives about how to avoid that, but I'm not sure anyone found a really satisfactory answer that didn't involve an unpleasant amount of locking. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Historic Query using a view/function ?
...Postgresql 7.2...
I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared at a point-in-time. I
can't seem to wrap my brain around the problem, tho.
Given some tables
CREATE TABLE list (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;
CREATE TABLE list_log (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
mod_type varchar(3),
log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;
And some rules...
CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");
CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");
It'd be great to be able to do something like...
SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';
... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?
CG
__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] "on insert" rules happen before the insert?
The postgresql documentation claims that "on insert" rules are executed after the insert. This is not happening for me. I have a rule that is being executed even though the insert should have failed due to a foreign key check. The rule does an insert that is failing, and the whole insert fails. So in this case it's not a huge problem. But I get a completely useless error message, and as far as I can tell there is no way to determine exactly what caused the failure (except for the fact that I happen to know what rule can cause that error). I'd like the person inserting a row that violates the foreign key check to get a useful error message. Is this a documentation bug? How can I get the desired behavior? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Complex Update
Problem: I have a table that has a field I want to plug with a value from other tables. I can do it with a perl program and a loop, but want to do it with a single SQL statement. Is there a way to do an update that combines a select statement containing a "where" that uses a field from the row to be updated? I would expect it to look something like this: update orderheader set total = (select sum(amount) from orderdetail where orderdetail.orderno=orderheader.orderno) ; Any ideas? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] not in vs not exists - vastly diferent performance
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: > "Iain" <[EMAIL PROTECTED]> writes: > >> I found this interesting and thought I'd offer it up for comment. > > You didn't say what PG version you are using, but I'd venture to bet > it is pre-7.4. This must've been one of the enhancements. =) -- Sir Randolf, noble spam fighter - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] functional index
HAPPY NEW YEAR EVERYONE ! Hi all.. Is there any method of creating functinal index using date() function? I can't create such an index because of uncacheable function... I have to select gathered data from specific day of year... So I should create an index for better performance. How can I achive that? Regards, Arek P.S. I am using PostgreSQL 7.4.0 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Radius of a zip code
"[EMAIL PROTECTED] (Tom Lane)" wrote in comp.databases.postgresql.sql: [sNip] > I'm guessing that the big problem is that you didn't measure longitude > and latitude in identical units in your table, so your "circle" isn't > real circular, and the smaller problem is that "miles" converts to > "degrees of arc" differently at different latitudes. Don't forget that there are two different types of "miles" which need to be considered when measuring distances: 1 statute/land mile = 1.609 km 1 nautical/sea mile = 1.85 km Since kilometers are consistent over land and water (and in the great vacuum of space), the metric system should always be used to ensure clarity, unless the only land masses the user is concerned with have no bodies of water. =) -- Sir Randolf, noble spam fighter - [EMAIL PROTECTED] Vancouver, British Columbia, Canada Please do not eMail me directly when responding to my postings in the newsgroups. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] DO INSTEAD in rule
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > In the last SELECT I exepcted j = 0, rather than j = 1 since I use DO > > INSTEAD in the rule and the default value for j is 0. Am I missing > > something? > > > CREATE rule t1_ins AS ON INSERT TO t1 > > WHERE (EXISTS (SELECT 1 FROM t1 > > WHERE i = new.i)) > > DO INSTEAD UPDATE t1 SET j = j + 1 > > WHERE i = new.i; > > Hm. The problem is that the rule query runs after the INSERT and so it > sees the inserted row as something to update. The logic is essentially > > if (not (EXISTS ...)) then do the INSERT; > if (EXISTS ...) then do the UPDATE; > > and the second command sees the inserted row as existing, so it updates > it. Oh I see. I think I can live with it. However I guess documentations should be clearner about this... > Consider using a trigger instead of a rule to do this. Or, accept > that the UPDATE will happen unconditionally, and start J off one less > than it should be. > > Note that either solution will have race conditions if multiple > processes try to insert the same row at the same time. There are > discussions in the archives about how to avoid that, but I'm not > sure anyone found a really satisfactory answer that didn't involve > an unpleasant amount of locking. Now I remember the discussion. Probably I should not use rules like my examples for real world applications. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] not in vs not exists - vastly diferent performance
Hi Tom, > You didn't say what PG version you are using, but I'd venture to bet > it is pre-7.4. Sorry I didn't give the version number, here is the output from select version(): PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3 This is the first time that we have compiled the system from source, previously we used rpm, but it seems very straight forward. I don't know if there may have been something we did wrong there. I understand that every DB environment is different, but it sounds like for the example I gave, you expected that the NOT IN optimization would have chosen a better plan. As to why it didn't I have no idea at this stage. If there is anything you want me to do to test it, for example on the regression test DB, then let me know. regards Iain ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] "on insert" rules happen before the insert?
[EMAIL PROTECTED] (Thomas Reat) writes: > The postgresql documentation claims that "on insert" rules are executed > after the insert. This is not happening for me. I have a rule that is being > executed even though the insert should have failed due to a foreign key check. The INSERT certainly happens before any rules that it triggers. But foreign key checks probably don't happen until all the dust has settled. This is arguably not a bug --- there are situations where it's essential. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
