Ciro Santilli (@cirosantilli) - undefined articles

This example is the same as nodejs/sequelize/raw/parallel_select_and_update.js, but going through Sequelize rather than with Sequelize raw queries. NONE is not supported for now to not have a transaction at all because lazy.

The examples illustrates: stackoverflow.com/questions/55452441/for-share-and-for-update-statements-in-sequelize

Sample invocation:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATE

where:

READ_COMMITTED: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html which correspond to the standard sQL isolation levels. It not given, don't set one, defaulting to the database's/sequelize's default level.
UPDATE: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html#static-get-LOCK. Update generates a SELECT FOR UPDATE in PostgreSQL for example. If not given, don't use any FOR xxx explicit locking.

Other examples:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATE

Then, the outcome is exactly as described at: nodejs/sequelize/raw/parallel_select_and_update.js:

READ_COMMITTED: fails
READ_COMMITTED UPDATE: works
REPEATABLE_READ: works, but is a bit slower, as it does rollbacks
This case also illustrates Sequelize transaction retries, since in this transaction isolation level transactions may fail:

 Read the full article

@cirosantilli/_file/nodejs/sequelize/raw/nodejs/sequelize/raw/parallel_create_delete_empty_tag.js  Updated 2024-12-15  +Created 1970-01-01

 View more

In this example, posts have tags. When a post is deleted, we check to see if there are now any empty tags, and now we want to delete any empty tags that the post deletion may have created.

If we are creating and deleting posts concurrently, a naive implementation might wrongly delete the tags of a newly created post.

This could be due to a concurrency issue of the following types.

Failure case 1:

thread 2: delete old post
thread 2: find all tags with 0 posts. Finds tag0 from the deleted old post which is now empty.
thread 1: create new post, which we want to have tag tag0
thread 1: try to create a new tag tag0, but don't because it already exists, this is done using SQLite's INSERT OR IGNORE INTO or PostgreSQL's INSERT ... ON CONFLICT DO NOTHING
thread 1: assign tag0 to the new post by adding an entry to the join table
thread 2: delete all tags with 0 posts. It still sees from its previous search that tag0 is empty, and deletes it, which then cascades into the join table

which would result in the new post incorrectly not having the tag0.

Failure case 2:

thread 2: delete old post
thread 2: find all tags with 0 posts
thread 1: create new post
thread 1: try to create a new tag tag0, but don't because it already exists
thread 2: delete all tags with 0 posts. It still sees from its previous search that tag0 is empty, and deletes it
thread 1: assign tag0 to the new post

which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens.

Failure case 3:

thread 2: delete old post
thread 1: create new post, which we want to have tag tag0
thread 1: try to create a new tag tag0, and succeed because it wasn't present
thread 2: find all tags with 0 posts, finds the tag that was just created
thread 2: delete all tags with 0 posts, deleting the new tag
thread 1: assign tag0 to the new post

which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens.

Sample executions:

node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED': PostgreSQL, 9 tags, DELETE/CREATE the tag0 test tag 1000 times, use READ COMMITTED
Execution often fails, although not always. The failure is always:
```
error: insert or update on table "PostTag" violates foreign key constraint "PostTag_tagId_fkey"
```
because the:
```
INSERT INTO "PostTag"
```
tries to insert a tag that was deleted in the other thread, as it didn't have any corresponding posts, so this is the foreign key failure.
TODO: we've never managed to observe the failure case in which tag0 is deleted. Is it truly possible? And if not, by which guarantee?
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED' 'FOR UPDATE': do a SELECT ... FOR UPDATE before trying to INSERT.
This is likely correct and the fastest correct method according to our quick benchmarking, about 20% faster than REPEATABLE READ.
We are just now 100% sure it is corret becase we can't find out if the SELECT in the DELETE subquery could first select some rows, which are then locked by the tag creator, and only then locked by DELETE after selection. Or does it re-evaludate the SELECT even though it is in a subquery?
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'REPEATABLE READ': repeatable read
We've never observed any failures with this level. This should likely fix the foreign key issue according to the PostgreSQL docs, since:
- the DELETE "Post" commit cannot start to be seen only in the middle of the thread 1 transaction
- and then if DELETE happened, the thread 1 transaction will detect it, ROLLBACK, and re-run. TODO how does it detect the need rollback? Is it because of the foreign key? It is very hard to be sure about this kind of thing, just can't find the information. Related: postgreSQL serialization failure.
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'SERIALIZABLE': serializable
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'NONE': magic value, don't use any transaction. Can blow up of course, since even less restrictions than READ COMMITTED

All executions use 2 threads.

Some theoretical notes:

Failure case 3 is averted by a READ COMMITTED transaction, because thread 2 won't see the uncommitted tag that thread 1 created, and therefore won't be able to delete it

stackoverflow.com/questions/10935850/when-to-use-select-for-update from SELECT FOR UPDATE also talks about a similar example, and has relevant answers.

 Read the full article

@cirosantilli/_file/nodejs/sequelize/raw/nodejs/sequelize/raw/parallel_select_and_update_deterministic.js  Updated 2024-12-15  +Created 1970-01-01

 View more

This example contains a deterministic demo of when postgreSQL serialization failures may happen.

Tested on PostgreSQL 13.5.

 Read the full article

@cirosantilli/_file/nodejs/sequelize/raw/nodejs/sequelize/raw/parallel_select_and_update.js  Updated 2024-12-15  +Created 1970-01-01

 View more

This example is similar to nodejs/sequelize/raw/parallel_update_async.js, but now we are doing a separate SELECT, later followed by an update:

SELECT FROM to get i
update on Js code newI = i + 1
UPDATE SET the newI

Although this specific example is useless in itself, as we could just use UPDATE "MyInt" SET i = i + 1 as in nodejs/sequelize/raw/parallel_update_async.js, which automatically solves any concurrency issue, this kind of code could be required for example if the update was a complex function not suitably implemented in SQL, or if the update depends on some external data source.

Sample execution:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED'

which does:

PostgreSQL, see other databases options at SQL example
2 threads
10 increments on each thread

Another one:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED' 'FOR UPDATE'

this will run SELECT FOR UPDATE rather than just SELECT

Observed behaviour under different SQL transaction isolation levels:

READ COMMITTED: fails. Nothing in this case prevents:
- thread 1: SELECT, obtains i = 0
- thread 2: SELECT, obtains i = 0
- thread 2: newI = 1
- thread 2: UPDATE i = 1
- thread 1: newI = 1
- thread 1: UPDATE i = 1
REPEATABLE READ: works. the manual mentions that if multiple concurrent updates would happen, only the first commit succeeds, and the following ones fail and rollback and retry, therefore preventing the loss of an update.
READ COMMITTED + SELECT FOR UPDATE: works. And does not do rollbacks, which probably makes it faster. With p 10 100, REPEATABLE READ was about 4.2s and READ COMMITTED + SELECT FOR UPDATE 3.2s on Lenovo ThinkPad P51 (2017).
SELECT FOR UPDATE should be enough as mentioned at: www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

A non-raw version of this example can be seen at: nodejs/sequelize/parallel_select_and_update.js.

 Read the full article

@cirosantilli/_file/nodejs/sequelize/raw/nodejs/sequelize/raw/parallel_update_async.js  Updated 2024-12-15  +Created 1970-01-01

 View more

nodejs/sequelize/raw/parallel_update_worker_threads.js contains a base example that can be used to test what can happen when queries are being run in parallel. But it is broken due to a sqlite3 Node.js package bug: github.com/mapbox/node-sqlite3/issues/1381...

nodejs/sequelize/raw/parallel_update_async.js is an async version of it. It should be just parallel enough to allow observing the same effects.

This is an example of a transaction where the SQL READ COMMITTED isolation level if sufficient.

These examples run queries of type:

UPDATE "MyInt" SET i = i + 1

Sample execution:

node --unhandled-rejections=strict ./parallel_update_async.js p 10 100

which does:

PostgreSQL, see other databases options at SQL example
10 threads
100 increments on each thread

The fear then is that of a classic read-modify-write failure.

But as www.postgresql.org/docs/14/transaction-iso.html page makes very clear, including with an explicit example of type UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;, that the default isolation level, SQL READ COMMITTED isolation level, already prevents any problems with this, as the update always re-reads selected rows in case they were previously modified.

If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row

Since in PostgreSQL "Read uncommitted" appears to be effectively the same as "Read committed", we won't be able to observe any failures on that database system for this example.

nodejs/sequelize/raw/parallel_create_delete_empty_tag.js contains an example where things can actually blow up in read committed.

 Read the full article

Heroku  Updated 2024-12-15  +Created 1970-01-01

 View more

This feels good.

One problem though is that Heroku is very opinionated, a likely like other PaaSes. So if you are trying something that is slightly off the mos common use case, you might be fucked.

Another problem with Heroku is that it is extremely difficult to debug a build that is broken on Heroku but not locally. We needed a way to be able to drop into a shell in the middle of build in case of failure. Otherwise it is impossible.

Deployment:

git push heroku HEAD:master

View stdout logs:

heroku logs --tail

PostgreSQL database, it seems to be delegated to AWS. How to browse database: stackoverflow.com/questions/20410873/how-can-i-browse-my-heroku-database

heroku pg:psql

Drop and recreate database:

heroku pg:reset --confirm <app-name>

All tables are destroyed.

Restart app:

heroku restart

 Read the full article

How to decide if an ORM is good?  Updated 2024-12-15  +Created 1970-01-01

 View more

How to decide if an ORM is decent? Just try to replicate every SQL query from nodejs/sequelize/raw/many_to_many.js on PostgreSQL and SQLite.

There is only a very finite number of possible reasonable queries on a two table many to many relationship with a join table. A decent ORM has to be able to do them all.

If it can do all those queries, then the ORM can actually do a good subset of SQL and is decent. If not, it can't, and this will make you suffer. E.g. Sequelize v5 is such an ORM that makes you suffer.

The next thing to check are transactions.

Basically, all of those come up if you try to implement a blog hello world world such as gothinkster/realworld correctly, i.e. without unnecessary inefficiencies due to your ORM on top of underlying SQL, and dealing with concurrency.

 Read the full article

ISO SQL TRIGGER syntax  Updated 2024-12-15  +Created 1970-01-01

 View more

TODO what is the standard compliant syntax?

PostgreSQL requires you to define a SQL stored procedure: stackoverflow.com/questions/28149494/is-it-possible-to-create-trigger-without-execute-procedure-in-postgresql Their syntax may be standard compliant, not sure about the EXECUTE part. Their docs: www.postgresql.org/docs/current/sql-createtrigger.html

SQLite does not support SQL stored procedures at all, so maybe that's why they can't be standard compliant here: stackoverflow.com/questions/3335162/creating-stored-procedure-in-sqlite

SQL:1999 11.38 covers "Trigger definition". The Abstract syntax tree starts with the CREATE TRIGGER and ends in:

<triggered SQL statement> ::=
  <SQL procedure statement>

This is defined at 13.5 "SQL procedure statement", but that is humongous and I'm not sure what it is at all.

 Read the full article

Oracle Corporation  Updated 2024-12-15  +Created 1970-01-01

 View more

Evil company that desecrated the beauty created by Sun Microsystems, and was trying to bury Java once and or all in the 2010's.

Their database is already matched by open source e.g. PostgreSQL, and ERP and CRM specific systems are boring.

Oracle basically grew out of selling one of the first SQL implementations in the late 70's, and notably to the United States Government and particularly the CIA. They did deliver a lot of value in those early pre-internet days, but now open source is and will supplant them entirely.

 Read the full article

Sequelize example  Updated 2024-12-15  +Created 1970-01-01

 View more

To run examples on a specific database:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

All examples can be tested on all databases with:

cd sequelize
./test

Overview of the examples:

nodejs/sequelize/index.js: a bunch of basic examples
nodejs/sequelize/update.js: This file is also where we are storing our expression-foo for now, e.g. how to do stuff like col1 + col2. Such knowledge can however be used basically anywhere else however, e.g. in AS or WHERE clauses, not just in UPDATE.
- stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138
- stackoverflow.com/questions/55646233/updating-with-calculated-values-in-sequelize
nodejs/sequelize/count.js: a simplified single-table count example. In practice, this will be usually done together with JOIN queries across multiple tables. Answers: stackoverflow.com/questions/22627258/how-does-group-by-works-in-sequelize/69896449#69896449
nodejs/sequelize/date.js: automatic date typecasts
nodejs/sequelize/like.js: LIKE
nodejs/sequelize/camel_case.js: trying to get everything in the database camel cased, columns starting with lowercase, and tables starting with uppercase. The defaults documented on getting started documentation do uppercase foreign keys, and lowercase non-foreign keys. It's a mess.
nodejs/sequelize/ignore_duplicates.js: ignore query on unique violation with ignoreDuplicates: true which does SQLite INSERT OR IGNORE INTO or PostgreSQL ON CONFLICT DO NOTHING. Closely related Upsert versions:
- Upsert
  - nodejs/sequelize/upsert.js: .upsert selects the conflict column automatically by unique columns. Both SQLite and PostgreSQL do INSERT INTO ON CONFLICT. PostgreSQL uses RETURNING, which was added too recently to SQLite: www.sqlite.org/lang_returning.html
    At nodejs/sequelize/composite_index.js we have a .upsert test with a composite index. Works just fine as you'd expect with a composite ON CONFLICT. Well done.
  - nodejs/sequelize/update_on_duplicate.js: .bulkCreate({}, { updateOnDuplicate: ['col1', col2'] }. Produces queries analogous to .upsert. This method is cool because it can upsert multiple columns at once. But it is annoying that you have to specify all fields to be updated one by one manually.
  - stackoverflow.com/questions/29063232/how-to-get-the-id-of-an-inserted-or-updated-record-in-sequelize-upsert/72092277#72092277
  - stackoverflow.com/questions/55531860/sequelize-bulkcreate-updateonduplicate-for-postgresql

nodejs/sequelize/inc.js: demonstrate the increment method. In SQLite, it produces a statement of type:

UPDATE `IntegerNames` SET `value`=`value`+ 1,`updatedAt`='2021-11-03 10:23:45.409 +00:00' WHERE `id` = 3

nodejs/sequelize/sync_alter.js: illustrates Model.sync({alter: true}) to modify a table definition, answers: stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138
nodejs/sequelize/truncate_key.js
nodejs/sequelize/validation.js: is handled by a third-party library: github.com/validatorjs/validator.js. They then add a few extra validators on top of that.
The args: true thing is explained at: stackoverflow.com/questions/58522387/unhandled-rejection-sequelizevalidationerror-validation-error-cannot-create-pr/70263032#70263032
nodejs/sequelize/composite_index.js: stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint
nodejs/sequelize/indent_log.js: stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint
association examples:
- nodejs/sequelize/one_to_many.js: basic one-to-many examples.
  - nodejs/sequelize/one_to_many_custom_column_name.js:
    stackoverflow.com/questions/43025861/sequelize-association-is-referencing-to-wrong-foreignkey-column-name
    stackoverflow.com/questions/49818406/sequelize-targetkey-not-working/59953948#59953948
- nodejs/sequelize/many_to_many.js: basic many-to-many examples, each user can like multiple posts. Answers: stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948
  - ORDER BY include:
    github.com/sequelize/sequelize/issues/4553
    stackoverflow.com/questions/66984410/why-the-sequelize-include-order-does-not-work
  - nodejs/sequelize/many_to_many_custom_table.js: many-to-many example, but where we craft our own table which can hold extra data. In our case, users can like posts, but likes have a integer weight associated with them. Related threads:
    stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948
    stackoverflow.com/questions/30308217/sequelize-find-by-association-through-manually-defined-join-table/69899876#69899876
  - nodejs/sequelize/many_to_many_same_model.js: association between a model and itself: users can follow other users. Related:
    stackoverflow.com/questions/43152180/node-sequelize-followering-following-relationship
    stackoverflow.com/questions/27065154/how-to-get-all-children-or-parents-in-a-many-to-many-association-if-one-model-re/72951602#72951602
    github.com/sequelize/sequelize/issues/8263
  - nodejs/sequelize/many_to_many_same_model_super.js
    nodejs/sequelize/find_duplicates.js: stackoverflow.com/questions/71235548/how-to-find-all-rows-that-have-certain-columns-duplicated-in-sequelize/71235550#71235550
  - nodejs/sequelize/many_to_many_super.js: "Super many to many": sequelize.org/master/manual/advanced-many-to-many.html This should not exist and shows how bad this library is for associations, you need all that boilerplate in order to expose certain relationships that aren't otherwise exposed by a direct hasMany with implicit join table.
- nested includes to produce queries with multiple JOIN:
  - nodejs/sequelize/nested_include.js: find all posts by users that a given user follows. Answers: stackoverflow.com/questions/42632943/sequelize-multiple-where-clause/68018083#68018083
  - nodejs/sequelize/nested_include_super.js: like nodejs/sequelize/nested_include.js but with a super many to many. We should move this to nodejs/sequelize/many_to_many_super.js.
- two relationships between two specific tables: we need to use as: to disambiguate them
  - nodejs/sequelize/many_to_many_double.js: users can both follow and like posts
  - nodejs/sequelize/one_to_many_double.js: posts have the author and a mandatory reviewer
hooks
internals:
- nodejs/sequelize/common.js: common utilities used across examples, most notably:
  - to easily setup different DBRM
- nodejs/sequelize/min_nocommon.js: to copy paste to Stack Overflow
- nodejs/sequelize/min.js: template for new exapmles in the folder

 Read the full article

cirosantilli.com content uploaded to ourbigbook.com/cirosantilli  Updated 2024-12-15  +Created 1970-01-01

 View more

The upload of OurBigBook Markup files to ourbigbook.com was done with the newly added OurBigBook CLI ourbigbook --web option. Although fully exposed to end users, the setup is not super efficient: a trully decent implementation should only upload changed files, and would basically mean reimplementing/using Git, since version diffing is what Git shines at. But I've decided not to put much emphasis on CLI upload for now, since it is expected that initially the majority of users will use the Web UI only. The functionality was added primarily to upload the reference content.

 Read the full article

Ciro Santilli @cirosantilli 34

 Incoming links: PostgreSQL