Re: [PATCH] SQL assertions prototype

On Mon, Nov 25, 2013 at 11:04:23AM -0800, Kevin Grittner wrote:
> Andrew Tipton <andrew(at)kiwidrew(dot)com> wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> So we'd need to get access to the changed rows, rather than
> >> re-executing a huge SQL command that re-checks every row of the
> >> table.  That last point will make it unusable for sensible
> >> amounts of data.
> >
> > That sounds very similar to handling incremental maintenance of
> > materialized views, which Kevin is working on.
>
> It does.
>
> > Let's assume that the "huge SQL command that re-checks every row
> > of the table" is actually a materialized view.  In that case, the
> > CREATE ASSERTION trigger would merely need to scan the matview
> > and raise an error if any rows were present.  That should be a
> > very quick operation.
>
> That would certainly be a viable way to implement this once we have
> incremental maintenance for materialized views, although I make no
> claims to having evaluated it versus the alternatives to be able to
> assert what the *best* way is.
>
> > No need to invent some sort of "get access to the changed
> > rows" mechanism especially for CREATE ASSERTION.
>
> As soon as we are out of this CF, I am planning to write code to
> capture deltas and fire functions to process them "eagerly" (within
> the creating transaction).  There has been suggestions that the
> changeset mechanism should be used for that, which I will look
> into; but my gut feel is that it will be better to build a
> tuplestore of tids flagged with "old" or "new" around the point
> that "after triggers" fire.  How close does that sound to what
> CREATE ASSERTION (as currently envisioned) would need?

It sounds *extremely* close to what we'd need for row access in
per-statement triggers, as in probably identical. The SQL syntax of
this sub-feature is described in Foundation section 11.49 and called
REFERENCING in CREATE TRIGGER.

Do you have any prototypes I could use for that purpose?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate