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

[Marko Schütz Schmuck]

On Tue, 04 Apr 2017 07:37:01 -0400,
Adam Chlipala wrote:
>
> On 04/03/2017 11:17 PM, Artyom Shalkhakov wrote:
> > 2017-04-04 4:42 GMT+06:00 Ziv Scully <zivscully at gmail.com>:
> >> 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.)
> >>
> > Does Ur/Web implement application transactions transparently to the
> > programmer? Or does it use database transactions throughout?
>
> Every page handler invocation runs in its own transaction.

That takes care of it!!!

The only thing that is not possible from within Ur/Web is setting up
the mutual reference of the foreign keys. But if I modify the SQL
generated by the compiler, separating table creation from setting the
foreign key constraints appropriately deferred, then one can insert
two tuples e.g. from main. Foreign key constraints are checked at the
end of the transaction and we get Success!

I attach a minimal example.

Best regards,

Marko
-------------- next part --------------
database dbname=sandbox
sql chickenAndEgg.sql
safeGet ChickenAndEgg/main
safeGet ChickenAndEgg/main1

chickenAndEgg
-------------- next part --------------
CREATE TABLE uw_ChickenAndEgg_chicken(uw_id int8 NOT NULL, uw_egg int8 NOT NULL,
 PRIMARY KEY (uw_id)
 );

 CREATE TABLE uw_ChickenAndEgg_egg(uw_id int8 NOT NULL,
                                    uw_chicken int8 NOT NULL,
  PRIMARY KEY (uw_id)
  );

ALTER TABLE uw_ChickenAndEgg_egg ADD CONSTRAINT uw_ChickenAndEgg_egg_Chicken
FOREIGN KEY (uw_chicken) REFERENCES uw_ChickenAndEgg_chicken (uw_id)
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE uw_ChickenAndEgg_chicken ADD CONSTRAINT uw_ChickenAndEgg_chicken_Egg
FOREIGN KEY (uw_egg) REFERENCES uw_ChickenAndEgg_egg (uw_id)
DEFERRABLE INITIALLY DEFERRED;
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: chickenAndEgg.ur
URL: <http://www.impredicative.com/pipermail/ur/attachments/20170404/d7b782a1/attachment.ksh>
-------------- next part --------------

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 181 bytes
Desc: OpenPGP Digital Signature
URL: <http://www.impredicative.com/pipermail/ur/attachments/20170404/d7b782a1/attachment.sig>