3

I would like to use GET DIAGNOSTICS integer_var = ROW_COUNT in plpgsql code to get the number of rows inserted into a table by a INSERT INTO statement.

If I run multiple INSERT INTO statements almost simultaneously can I be sure that GET DIAGNOSTICS returns the number of inserted rows correctly for each statement?

I can illustrate this with an example, if this happens in the following sequence:

  1. User A runs a insert into statement that inserts 10 rows into a table.
  2. user B runs a insert into statement that inserts 5 rows into a table.
  3. User A calls GET DIAGNOSTICS rowcount = ROW_COUNT;
  4. User B calls GET DIAGNOSTICS rowcount = ROW_COUNT;

Will user A get a rowcount of 10 and user B of 5? Or will both get 5?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Thomas
  • 962
  • 2
  • 9
  • 18

1 Answers1

5

PostgreSQL PLpgSQL statement GET DIAGNOSTICS shows info about last statement in transaction (inside transaction you are isolated from other users). So users A will see 10, and user B will see 5.

PostgreSQL is ACID database and users are strongly isolated (usually). In one session you cannot to see in big detail work of other users. Maximum what is possible to see a committed changes of data.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94