Re: [SQL] after delete trigger behavior
After delete worked and the a foreach execute update seems to work best. Below is a satisfactory test set. -- create test table CREATE TABLE test1 ( a int, b int, c int); -- create resort function CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS ' DECLARE eachrow RECORD; innerrow RECORD; sort INT := 0; BEGIN sort := 0; FOR eachrow IN EXECUTE ''SELECT * FROM portfolio.test1 WHERE a = '' || OLD.a LOOP IF eachrow.b != OLD.b THEN EXECUTE ''UPDATE portfolio.test1 SET c = '' || sort || '' WHERE a = '' || eachrow.a || '' AND b = '' || eachrow.b || ; sort := sort +1; END IF; END LOOP; RETURN OLD; END; ' language 'plpgsql'; -- create trigger CREATE TRIGGER u_test1 AFTER DELETE ON portfolio.test1 FOR EACH ROW EXECUTE PROCEDURE resort_test1(); -- sample data insert into test1 values(1,1,0); insert into test1 values(1,2,1); insert into test1 values(1,3,2); insert into test1 values(1,4,3); insert into test1 values(1,5,4); insert into test1 values(2,1,0); insert into test1 values(2,2,1); insert into test1 values(2,3,2); insert into test1 values(2,4,3); insert into test1 values(2,5,4); -- test delete delete from test1 where b = 2 or b = 4; -- view test results select * from test1 order by a, b, c; ---(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
[SQL] optimizer, view, union
Hi, Can pg transform SELECT * FROM ( SELECT 'foo' AS class, id FROM foo UNION ALL SELECT 'bar' AS class, id FROM bar ) AS a WHERE class = 'foo' into SELECT 'foo' AS class, * FROM foo? The subselect is a view here, that's why I don't just use the second query. Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
[SQL] Grouping Too Closely
I have a table that looks like this:CREATE TABLE my_table ( pkey serial PRIMARY KEY, fkey int NOT NULL REFERENCES my_other_table( pkey ), uid int NOT NULL REFERENCES user( pkey ), seq1 int, seq2 int);Basically, for each fkey that exists in my_table, there is a sequence represented by seq1, which covers every record corresponding to a given fkey. Then there is a subset of records covered by seq2, which increments over the course of a given fkey, but might span multiple records.E.g.,pkey | fkey | uid | seq1 | seq2---1 | 1 | 1 | 1 | 12 | 1 | 2 | 2 | 1...What I'd like to be able to do is select all records corresponding to the minimum value of seq1 for each value of seq2 corresponding to a given fkey (with a lower bound on the value of seq2).My first attempt looked like this:SELECT fkey, uid, seq2FROM my_tableWHERE seq2 > 2GROUP BY fkey, seq2, uid, seq1HAVING seq1 = min( seq1 )but this groups too closely to return the desired results.My next attempt looked like this (where I use the shorthand for min in the subquery):SELECT fkey, uid, seq2FROM my_table AS mt1WHERE mt1.seq2 > 2AND ( mt1.uid, hh1.seq1 ) IN ( SELECT mt2.player_id, mt2.order_no FROM my_table AS mt2 WHERE mt2.fkey = mt1.fkey AND mt2.seq2 = mt1.seq2 GROUP BY mt2.seq1, mt2.uid ORDER BY mt2.seq1 ASC LIMIT 1)GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_idThis seems like it works, but it is abominably slow, running on the order of days across 1.5 million rows rather than the seconds (or preferably milliseconds) I'd prefer.I have this annoying feeling that I'm overlooking a reasonably efficient in-between query.-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
Re: [SQL] optimizer, view, union
Markus Bertheau <[EMAIL PROTECTED]> writes: > Can pg transform > SELECT * FROM ( > SELECT 'foo' AS class, id FROM foo > UNION ALL > SELECT 'bar' AS class, id FROM bar > ) AS a WHERE class = 'foo' [ experiments... ] Yes, if you spell it like this: regression=# explain SELECT * FROM ( regression(# SELECT 'foo'::text AS class, id FROM foo regression(# UNION ALL regression(# SELECT 'bar'::text AS class, id FROM bar regression(# ) AS a WHERE class = 'foo'; QUERY PLAN - Append (cost=0.00..105.60 rows=4280 width=4) -> Subquery Scan "*SELECT* 1" (cost=0.00..52.80 rows=2140 width=4) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) -> Subquery Scan "*SELECT* 2" (cost=0.00..52.80 rows=2140 width=4) -> Result (cost=0.00..31.40 rows=2140 width=4) One-Time Filter: false -> Seq Scan on bar (cost=0.00..31.40 rows=2140 width=4) (7 rows) If unadorned, the literals get caught up in some type-conversion issues. (You don't really want them in the output of a view anyway; "unknown" type columns are bad news.) regards, tom lane ---(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: [SQL] Grouping Too Closely
I'm not sure if this is the best thing to do in all occasions, but I have found a great speed increase using unions over group by. select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select min(seq1) from mytable); union select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select min(seq1) from mytable); order by fkey, uid, seq2; the union clause with remove your duplicates for you as you were doing with your group by. using min on large tables can cause problems. you may want to do your select min(seq1) from mytable or even have a trigger function after insert/update that checks the new value against the current lowest stored in another table. not sure if this helps, but i hope it does. russ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] empty view, replace view, column type change?
Hi, it seems to me that the following should work but it fails: CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; CREATE TABLE link (ov TIMESTAMP); CREATE OR REPLACE VIEW co AS SELECT ov FROM link; Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [SQL] empty view, replace view, column type change?
Dnia 23-06-2005, czw o godzinie 22:03 +0200, Markus Bertheau napisał(a): > Hi, > > it seems to me that the following should work but it fails: > > CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; > CREATE TABLE link (ov TIMESTAMP); > CREATE OR REPLACE VIEW co AS SELECT ov FROM link; Erm, the error message is something to the effect of ERROR: can't change type of view column "ov" -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [SQL] empty view, replace view, column type change?
On Thu, 2005-06-23 at 22:03 +0200, Markus Bertheau wrote: > Hi, > > it seems to me that the following should work but it fails: > > CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; > CREATE TABLE link (ov TIMESTAMP); > CREATE OR REPLACE VIEW co AS SELECT ov FROM link; The data types are different, as one has the timestamp to (6) decimal places after seconds. development=# CREATE VIEW co AS SELECT LOCALTIMESTAMP::TIMESTAMP AS ov WHERE FALSE; CREATE VIEW development=# CREATE TABLE link (ov TIMESTAMP); CREATE TABLE development=# CREATE OR REPLACE VIEW cod AS SELECT ov FROM link; CREATE VIEW development=# \d co View "rbt.co" Column | Type | Modifiers ++--- ov | timestamp(6) without time zone | View definition: SELECT 'now'::text::timestamp(6) without time zone AS ov WHERE false; development=# \d cod View "rbt.cod" Column |Type | Modifiers +-+--- ov | timestamp without time zone | View definition: SELECT link.ov FROM link; -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] empty view, replace view, column type change?
Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a): > The data types are different, as one has the timestamp to (6) decimal > places after seconds. That's strange. I explicitly specified ::TIMESTAMP on both the view and the table. Is that not unambiguous? Markus -- Markus Bertheau <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [SQL] empty view, replace view, column type change?
On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote: > Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisał(a): > > > The data types are different, as one has the timestamp to (6) decimal > > places after seconds. > > That's strange. I explicitly specified ::TIMESTAMP on both the view and > the table. Is that not unambiguous? LOCALTIMESTAMP is probably more specific, so it folds the length in. If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). Cast them both to timestamp(N) and what you're trying to do should work. -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] empty view, replace view, column type change?
Rod Taylor <[EMAIL PROTECTED]> writes: > On Thu, 2005-06-23 at 22:27 +0200, Markus Bertheau wrote: >> Dnia 23-06-2005, czw o godzinie 16:19 -0400, Rod Taylor napisa³(a): >>> The data types are different, as one has the timestamp to (6) decimal >>> places after seconds. >> >> That's strange. I explicitly specified ::TIMESTAMP on both the view and >> the table. Is that not unambiguous? > LOCALTIMESTAMP is probably more specific, so it folds the length in. > If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). Yeah, this is exactly what's happening --- if you look at gram.y, LOCALTIMESTAMP is expanded to "'now'::text::timestamp(6)". I am unconvinced that the (6) is a very good idea though. The code says /* SQL99 mandates a default precision of 6 for timestamp. * Also, that is about as precise as we will get since * we are using a microsecond time interface. * - thomas 2001-12-07 */ d->typmod = 6; but it doesn't seem to me to follow from what the spec says that we need to explicitly cast the result of now() to six places. As long as it's coming from gettimeofday it can't have more than 6 places anyway, and so we might as well save the extra coercion step. (The parser *will* tack on a separate coercion function call when presented with this parse tree.) In short, I'm inclined to remove the above-quoted lines, and similarly for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] empty view, replace view, column type change?
Tom Lane wrote: > > If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). > > Yeah, this is exactly what's happening --- if you look at gram.y, > LOCALTIMESTAMP is expanded to "'now'::text::timestamp(6)". I am > unconvinced that the (6) is a very good idea though. The code says > > /* SQL99 mandates a default precision of 6 for timestamp. > * Also, that is about as precise as we will get since > * we are using a microsecond time interface. > * - thomas 2001-12-07 > */ > d->typmod = 6; > > but it doesn't seem to me to follow from what the spec says that we need > to explicitly cast the result of now() to six places. As long as it's > coming from gettimeofday it can't have more than 6 places anyway, and so > we might as well save the extra coercion step. (The parser *will* tack > on a separate coercion function call when presented with this parse > tree.) > > In short, I'm inclined to remove the above-quoted lines, and similarly > for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? Agreed. That "6" bothered me too when I was cleaning up the timestamp code a while back. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
