0

Context

Schema setup

create table users( id serial primary key );

Query 1

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1;

Expectations

1

Result

1

Query 2

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t2;

Expectations

2

Result

1

Query 3

with t1 as (
    insert into users default values
    returning id
), t2 as (
    insert into users default values
    returning id
)
select id from t1
union all
select id from t2;

Expectations (taking in account results of Query 2)

1
1

Result

1
2

Question

What's wrong with my expectations and how to get the expected value from Query 2?

bessgeor
  • 3
  • 1

1 Answers1

2

This is an expected and documented behaviour

Quote from the manual

Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable

In your second query the database apparently chose to run t2 before t1 and thus it returns 1.

In the third query there have to be two different values, because that's what a sequence guarantees: it never generates the same number twice (unless you have enabled cycle)

  • Thank you for the clarification. So there is no other way to get the latest inserted Id with that query than select max( id ) from ( select Id from t1 union all ... from tN )? – bessgeor Dec 19 '17 at 09:31
  • @bessgeor If you need to distinguish between the id inserted in the first statement and the one inserted in the second you need to use two separate statements, not a data modifying CTE (and then you can easily use lastval()) –  Dec 19 '17 at 09:33
  • @a_horse_with_no_name I think you can also chain the CTEs, so the order of their execution is predictable. – ypercubeᵀᴹ Dec 19 '17 at 09:55
  • @a_horse_with_no_name Is there any doc on how database is choosing when to run one statement or another in CTE or is it an implementation detail on which one should not relate? – bessgeor Dec 19 '17 at 09:56
  • @yper-trollᵀᴹ what do you mean under CTE chaining? – bessgeor Dec 19 '17 at 09:57
  • @bessgeor it's a detail. The link provided in the answer is what you need to know. But what I don't understand, is why do you need two CTEs to be inserting into the same table? You know you can insert 2 (or more) rows in a single CTE, right? – ypercubeᵀᴹ Dec 19 '17 at 09:57
  • "Chaining": use t1 inside t2. Something like: with t1 as ( insert into users default values returning id ), t2 as ( insert into users (select nextval('users_id_seq') where exists (table t1)) returning id ) select id from t2; – ypercubeᵀᴹ Dec 19 '17 at 10:06
  • @yper-trollᵀᴹ yes, I know it but don't want to rely on unguaranteed order. BTW it will make it impossible to do related entities insert (oversimplified example: add two users and one login for each, where login "John" should be bound to user 1 and login "Doe" to user 2 because some of the user data is specific to a particular login).

    To add some context, I am writing simple CR-only sql generator, so I have to use pretty generic statements not to overcomplicate query generation.

     – bessgeor Dec 19 '17 at 10:18
  • 2
    @bessgeor: the simple solution is: don't use a CTE. Use single INSERT statements in the correct order in a single transaction. –  Dec 19 '17 at 10:20