0

I have

db.result('DELETE FROM categories WHERE id = ${id}', category).then(function (data) { ...

and

db.many('SELECT * FROM categories').then(function (data) { ...

initially delete is called from one API call and then select on following API call, but callback for db request happens in reverse order, so I get list of categories with removed category.

Is there a way how to lock categories table with pg-promise?

user2846569
  • 2,752
  • 2
  • 23
  • 24
  • You said delete is called from one API call and then select on following API call, and you are getting list of categories with removed category. What is the problem then? – Sonu K Dec 26 '17 at 15:00
  • I want new list without removed category obiously. – user2846569 Dec 26 '17 at 15:01
  • But you are already calling delete before calling select, shouldn't it give list with deleted records? – Sonu K Dec 26 '17 at 15:04
  • I think you are misreading english. E.g. there was 5 records, I call delete on one, then retrieve and get all 5, but I want to get only 4. – user2846569 Dec 26 '17 at 15:05
  • Can you try calling the Select API inside the then/success part of the Delete API. – Sonu K Dec 26 '17 at 15:10
  • That could be an option, but my architecture has separate API callback in redux so doesnt work with rest of the code. – user2846569 Dec 26 '17 at 15:22
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161988/discussion-between-sonu-k-and-user2846569). – Sonu K Dec 26 '17 at 16:48
  • 1
    You cal also try to make these API calls synchronous so that the second one is only called when first one completes. Looks like they are being called asynchronously and second API is being called even if the first has not completed. – Sonu K Dec 27 '17 at 09:37

1 Answers1

1

If you want the result of the SELECT to always reflect the result of the previous DELETE, then you have two approaches to consider...

The standard approach is to unify the operations into one, so you end up executing all your dependent queries against the same connection:

db.task(function * (t) {
    yield t.none('DELETE FROM categories WHERE id = ${id}', category);
    return yield t.any('SELECT FROM categories');
})
  .then(data => {
      // data = only the categories that weren't deleted
  });

You can, of course, also use either the standard promise syntax or even ES7 await/async.

The second approach would be to organize an artificial lock inside your service that would hold off on executing any corresponding SELECT until the DELETE requests are all done.

However, this is a very awkward solution, typically pointing at the flaw in the architecture. Also, as the author of pg-promise, I won't be even getting into that solution, as it would be way outside of my library anyway.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138