Simon Riggs <si...@2ndquadrant.com> wrote: > On 9 June 2013 14:56, Kevin Grittner <kgri...@ymail.com> wrote: >> Simon Riggs <si...@2ndquadrant.com> wrote: >>> On 8 June 2013 22:25, Kevin Grittner <kgri...@ymail.com> wrote: >>>> Simon Riggs <si...@2ndquadrant.com> wrote: >> >>> There are also difficulties in semantics, since when >>> we have OLD and NEW at row level we know we are discussing the same >>> row. With sets of OLD and NEW we'd need to be able to link the >>> relations back together somehow, which couldn't be done by PK since >>> that could change. So we'd need to invent some semantics for a >>> "linking identifier" of some description. Which once we've done it >>> would be used by people to join them back together again, which is >>> what we already had in the first place. So my take is that it sounds >>> expressive, but definitely not performant. >> >> I have used a feature like this in other database products, and can >> say from experience that these relations in a statement trigger can >> be very useful without the linkage you propose. I can see how the >> linkage could potentially be useful, but if that is the only >> hang-up, we would be adding a powerful feature without it. > > What I'm trying to do is tune FKs, which are currently implemented as > after row triggers. Hence why I'm looking at ways to speed up after > row triggers. > > Suggesting I work on after statement triggers would imply you think > that FKs should/could be rewritten as after statement triggers. Is > that what you mean?
Well, I didn't make any suggestion -- I was asking questions, and then when you commented on a hypothetical feature I responded. That said, I was asking the question because I used SQL Server for years when it had triggers but no foreign key definitions. There was a pattern for enforcing foreign key relationships in AFTER EACH STATEMENT triggers using the old and new relations (with no linkage between particular updated rows) which we used so heavily I could write the triggers for a given foreign key mechanically in just a minute or two. This technique had good performance and seems to have all the semantics you're looking for, so I was wondering whether that might be a better approach to this problem. It sure seems like it would have fewer moving parts. I know it was very reliable with S2PL concurrency control, but there may be problems with adapting it to MVCC that I'm not seeing without a deeper look. If you're interested, I could try to prod those areas of my memory to recall the pattern, or find examples of it somewhere. > Doing it that way would mean rewriting a lot of code and would still > have problems 2 and 3 identified above. I can't imagine anybody would > go for that, but if you have a sketch of how it might work we can > consider it. I don't, but if there is interest I could probably sketch the outlines. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
