Dyamic updates of NEW with pl/pgsql
in [PgSql]
|
Prev: patch (for 9.1) string functions
Next: [HACKERS] Access violation from palloc, Visual Studio 2005, C-language function
From: Merlin Moncure on 15 Mar 2010 09:39 On Sat, Mar 13, 2010 at 1:38 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > I wonder if it could work to treat the result of a "record->fieldname" > operator as being of UNKNOWN type initially, and resolve its actual > type in the parser in the same way we do for undecorated literals > and parameters, to wit > * you can explicitly cast it, viz > (record->fieldname)::bigint > * you can let it be inferred from context, such as the type > of whatever it's compared to > * throw error if type is not inferrable > Then at runtime, if the actual type of the field turns out to not be > what the parser inferred, either throw error or attempt a run-time > type coercion. Throwing error seems safer, because it would avoid > surprises of both semantic (unexpected behavior) and performance > (expensive conversion you weren't expecting to happen) varieties. > But possibly an automatic coercion would be useful enough to justify > those risks. the casting rules are completely reasonable. Throwing an error seems like a better choice. Better to be strict now and relax the rules later. record->fieldname takes a string (possibly a variable)? If so, his would nail the problem. This would work with run time typed records (new, etc)? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 15 Mar 2010 10:02 Merlin Moncure wrote: > record->fieldname takes a string (possibly a variable)? If it doesn't we have a communication problem. :-) > If so, his would nail the problem. Not quite, but close. We also need a nice way of querying for field names (at least) at run time. I've seen that requested several times. > This would work with run time typed > records (new, etc)? > > Again, if it doesn't we have a communication problem. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 15 Mar 2010 11:18 On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew(a)dunslane.net> wrote: > Not quite, but close. We also need a nice way of querying for field names > (at least) at run time. I've seen that requested several times. ok. just making sure we were on the same page. wasn't there a technical objection to querying the fields at runtime? If not, maybe you could get by with something like: Integer variant of operator pulls fields by index somettype v := recvar->3; integer n := nfields(recordtype); text[] fields := fieldnames(recordtype); text fieldname := fieldname(recordtype, 3); int fieldpos := fieldpos(recordtype, 'a_field'); OK, from archives (Tom wrote) quoting: So, inventing syntax at will, what you're imagining is something like modified := false; for name in names(NEW) loop -- ignore modified_timestamp continue if name = 'modified_timestamp'; -- check all other columns if NEW.{name} is distinct from OLD.{name} then modified := true; exit; end if; end loop; if modified then ... While this is perhaps doable, the performance would take your breath away ... and I don't mean that in a positive sense. The only way we could implement that in plpgsql as it stands would be that every single execution of the IF would invole a parse/plan cycle for the "$1 IS DISTINCT FROM $2" expression. At best we would avoid a replan when successive executions had the same datatypes for the tested columns (ie, adjacent columns in the table have the same types). Which would happen some of the time, but the cost of the replans would still be enough to sink you. /end quote does the parse/plan objection still hold? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 15 Mar 2010 11:37 Merlin Moncure <mmoncure(a)gmail.com> writes: > On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew(a)dunslane.net> wrote: >> Not quite, but close. We also need a nice way of querying for field names >> (at least) at run time. I've seen that requested several times. > does the parse/plan objection still hold? Yeah. Providing the field names isn't the dubious part --- the dubious part is what are you going to *do* with them. It's difficult to see applications in which you can make the simplifying assumption that the actual field datatypes are known/fixed. Using field numbers instead of names doesn't get you out from under that. (Though I like the idea insofar as it simplifies the looping mechanism.) If we make the implementation be such that "(rec->field)::foo" forces a runtime cast to foo (rather than throwing an error if it's not type foo already), then it's possible to suppose that this sort of application could be catered to by forcing all the fields to text, or some other generic datatype. This at least puts the text dependency out where the user can see it, though it still seems rather inelegant. It also takes away possible error detection in other circumstances where a forced cast isn't really wanted. The cost of looking up the ever-changing cast function could still be unpleasant, although I think we could hide it in the executor expression node instead of forcing a whole new parse/plan cycle each time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 15 Mar 2010 12:14
On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > If we make the implementation be such that "(rec->field)::foo" forces a runtime cast to foo (rather than throwing an error if it's not type foo already) yeah...explicit cast should always do 'best effort' > The cost of looking up the ever-changing cast function could still be > unpleasant, although I think we could hide it in the executor expression > node instead of forcing a whole new parse/plan cycle each time. right. if you do that, it's still going to be faster than the dyna-sql/information schema/perl hacks people are doing right now (assuming they didn't give up and code it in the app). This is rtti for plpgsql, and functions that use it are going have to be understood as being slower and to be avoided if possible, like exception handlers. IMNSHO, this is a small price to pay. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |