[Ur] solution to the DB chicken and egg problem?

[Ziv Scully]

My understanding is that Ur/Web already packages entire page handlers as a
single transaction. You don't have to worry about two dml statements in the
same page handler being interrupted in between by another thread: all the
SQL happens at "the same time" when the page handler finishes. (According
to the manual, this is guaranteed by later Postgres versions, but other
DBMSs are flaky.)

I'm not sure if you have to worry for the purposes of recursive table
constraints (which, by the way,can be defined in the database outside of
Ur/Web's generated SQL if really necessary).

In principle it's probably possible to write combinators to do multiple
dmls in one big statement sent to the database, though this would require
introducing a few new types to Basis (analogues to [Basis.query]). I don't
know if this gives you a performance win, and in the event that it does,
one has to balance the gain with the benefit of prepared statements. If you
create an insert statement at runtime from a list of rows to insert, you
probably end up not using a prepared statement because the compiler doesn't
statically know the structure of the statement. (In contrast, if you make a
prepared statement from a type-level record using a [Top.folder], then the
expansion is done at compile time and I think you're all set.)

In the particular chicken-egg case you mention, it seems like you should
just have a single table with the union of the fields, but I find it easy
to believe that there's some reason somewhere (e.g. working with existing
schemas) that one might need to use recursive constraints.


On Mon, Apr 3, 2017 at 2:42 PM, Marko Schütz Schmuck <
markoschuetz at googlemail.com> wrote:

> On Mon, 03 Apr 2017 09:57:30 -0400,
> Adam Chlipala wrote:
> >
> > I'm not familiar with a standard database feature to allow that mode,
> but it sounds like you are referencing
> > an extra annotation on constraints.  Somehow this situation has only
> come up once in the 10+ years of Ur/
> > Web's existence, and it's never been an issue for my own Ur/Web apps.
>
> AFAIK "DEFERRABLE INITIALLY DEFERRED" is standard SQL, but I agree
> that it might not be of much practical relevance.
>
> What about the other thought: explicitly merging two dml statements
> into one transaction (of course stripping the transactions around the
> individual dml statements)?
>
> Best regards,
>
> Marko
>
>
> > On 04/02/2017 10:10 AM, Marko Schütz Schmuck wrote:
> >
> >     On Sun, 02 Apr 2017 09:02:15 -0400,
> >     Adam Chlipala wrote:
> >
> >         Actually, Ur/Web won't even accept those table definitions: no
> mutually recursive
> >         definitions yet, w.r.t. constraints.  And I haven't thought
> before about allowing
> >         temporary breaking of constraints.
> >
> >     "Constraints hold after every statement" -> "Constraints hold after
> >     every transaction"?
> >
> >         On 04/02/2017 08:35 AM, Marko Schütz Schmuck wrote:
> >
> >             If I have tables
> >
> >             table chicken : { Id : int, Egg : int }
> >                    PRIMARY KEY (Id),
> >                    CONSTRAINT egg FOREIGN KEY Egg REFERENCES egg(Id)
> >
> >             table egg : { Id : int, Chicken : int }
> >                    PRIMARY KEY (Id),
> >                    CONSTRAINT chicken FOREIGN KEY Chicken REFERENCES
> chicken(Id)
> >
> >             Outside of Ur/Web this would be solved by setting the
> constraints
> >             DEFERRABLE INITIALLY DEFERRED and do the INSERTs pairwise
> inside a
> >             single transaction.
> >
> >             I doubt that there currently is a way to do this in Ur/Web
> since there
> >             is neither a way to specify DEFERRABLE INITIALLY DEFERRED
> nor a way to
> >             explicitly form transactions.
> >
> >             Would it be enough to introduce an operator
> >
> >             dml_sequence : dml -> dml -> dml
> >
> >             allowing two dml statements to be composed and run in a
> single
> >             transaction together with exposing DEFERRABLE INITIALLY
> DEFERRED at
> >             the Ur/Web language level?
> >
> >             Best regards,
> >
> >             Marko
> >
> >         _______________________________________________
> >         Ur mailing list
> >         Ur at impredicative.com
> >         http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
> >
> >         _______________________________________________
> >         Ur mailing list
> >         Ur at impredicative.com
> >         http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
> >
> >
> > [2  <text/plain; utf-8 (base64)>]
> > _______________________________________________
> > Ur mailing list
> > Ur at impredicative.com
> > http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
>
> _______________________________________________
> Ur mailing list
> Ur at impredicative.com
> http://www.impredicative.com/cgi-bin/mailman/listinfo/ur
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.impredicative.com/pipermail/ur/attachments/20170403/fa678e88/attachment.html>