Re: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
On Aug 2, 2006, at 12:47 , Joe Conway wrote: regression=# create rule r2 as on update to src do insert into log values(old.*, 'old'), (new.*, 'new'); ERROR: VALUES must not contain OLD or NEW references Would it make sense to add a HINT as well, recommending the UNION construct Tom mentioned earlier? On Aug 2, 2006, at 11:52 , Tom Lane wrote: create rule r2 as on update to src do insert into log select old.*, 'old' union all new.*, 'new'; or just use two separate INSERT commands in the rule. Or is the general case too, uh, general for a HINT? 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: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
Bruce Momjian wrote: Should we wait for someone to actually ask for this before adding it to the TODO list? Probably worth adding it to the TODO list so it doen't get lost. Does it cause a crash now? Nope: regression=# create table log(f1 int, f2 int, tag text); CREATE TABLE regression=# insert into src values(1,2); INSERT 0 1 regression=# create rule r2 as on update to src do insert into log values(old.*, 'old'), (new.*, 'new'); ERROR: VALUES must not contain OLD or NEW references Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
Should we wait for someone to actually ask for this before adding it to the TODO list? Does it cause a crash now? --- Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> What I'm inclined to do for 8.2 is to disallow OLD/NEW references in > >> multi-element VALUES clauses; the feature is still tremendously useful > >> without that. > > > Given the timing, this sounds like a reasonable approach. I agree that > > the feature has lots of interesting uses -- I'd hate to see us lose > > that. Disallowing OLD/NEW references doesn't contradict the spec in any > > way AFAIK either. > > I don't think rules are in the spec at all ;-) ... so no, that's not > a problem. My example demonstrated a pretty likely use: > > create rule r2 as on update to src do > insert into log values(old.*, 'old'), (new.*, 'new'); > > but for the moment we can tell people to work around it the way > they always have: > > create rule r2 as on update to src do > insert into log select old.*, 'old' union all new.*, 'new'; > > or just use two separate INSERT commands in the rule. > > We oughta fix it later, but I don't feel ashamed to have a restriction > like this in the first cut. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: What I'm inclined to do for 8.2 is to disallow OLD/NEW references in multi-element VALUES clauses; the feature is still tremendously useful without that. Given the timing, this sounds like a reasonable approach. I agree that the feature has lots of interesting uses -- I'd hate to see us lose that. Disallowing OLD/NEW references doesn't contradict the spec in any way AFAIK either. Joe ---(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: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: One of the things I'm struggling with is lack of column aliases. Would it be reasonable to require something like this? Requiring column aliases is counter to spec ... SELECT ... FROM (VALUES ...) AS foo(col1 type1, col2 type2, ...) ... and this is even further away from it. I figured as much, but thought I'd ask anyway :-). I did find something in the appendix to the spec after sending this: Annex C (informative) Implementation-dependent elements 18) Subclause 7.3, “”: a) The column names of a or a typed table value constructor> are implementation-dependent. As for the names, just use "?column?", same as we do now in INSERT ... VALUES. Anyone who wants to refer to those columns explicitly will need to assign aliases, but if they don't assign aliases, we don't have to do anything very intelligent. OK, I just thought "?column?" was ugly and useless. As for the types, I believe that the spec pretty much dictates that we apply the same type resolution algorithm as for a UNION. This is fairly expensive and we should avoid it in the case of INSERT ... VALUES, but for VALUES appearing anywhere else I think we have little choice. Where do I find that algorithm -- somewhere in nodeAppend.c? Thanks, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: If I'm reading the spec correctly, VALUES is exactly parallel to SELECT in the grammar, which means that to use it in FROM you would need parentheses and an alias: SELECT ... FROM (SELECT ...) AS foo SELECT ... FROM (VALUES ...) AS foo One of the things I'm struggling with is lack of column aliases. Would it be reasonable to require something like this? SELECT ... FROM (VALUES ...) AS foo(col1, col2, ...) The other issue is how to determine column type. Even better would be to require (similar to SRF returning record): SELECT ... FROM (VALUES ...) AS foo(col1 type1, col2 type2, ...) This would unambiguously identify the column aliases and types. Assuming we stick with the spec: SELECT ... FROM (VALUES ...) AS foo 1. How should we assign column names? values1, values2, ...? or col1, col2, ...? or ??? 2. How should we assign datatypes? Use the first "row" and try to coerce the rest to that type? Joe ---(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
