SELECT after WITH returns no rows if in an explicit transaction

sql postgresql transactions

55 观看

3回复

810 作者的声誉

WITH generated_id AS (
  INSERT INTO ... RETURNING id
)
SELECT id FROM generated_id;

returns 42, whereas

BEGIN;
WITH generated_id AS (
  INSERT INTO ... RETURNING id
)
SELECT id FROM generated_id;
COMMIT;

returns nothing. Why?


Update:

I just found that WITH is irrelevant, because even a single select doesn't work:

SELECT something FROM some_table;

returns rows.

BEGIN;
SELECT something FROM some_table;
COMMIT;

returns no rows.


Update 2:

I thought BEGIN and START TRANSACTION are the exact same things. Anyways, I tried all possible combinations but none of them works for me.

I'm using this free postgres service but now I tested it with SQL Fiddle and it is not complaining.

It is strange however that if I don't put a ; at the end of the SELECT line, my DB engine gives me a syntax error and if I put it there using SQL Fiddle, it tells me that Explicit commits are not allowed.

So it's still very unclear for me what is exactly happening. Whether it only works in SQL Fiddle because it really isn't running my query in an explicit transaction and if it would then the results would be the same: no rows, just as how my DB engine behaves.

I'm unfortunately not able to test it on other servers, but if someone has a reliable postgres config, maybe they could try it for me whether it runs and tell me.

作者: tom 的来源 发布者: 2017 年 12 月 27 日

回应 3


0

0 作者的声誉

Did you try saying BEGIN WORK? I think you need to start with that, then you can end it with COMMIT

作者: user8915618 发布者: 2017 年 12 月 27 日

0

4245 作者的声誉

Try to start with:

BEGIN TRANSACTION;

Then end with:

END TRANSACTION;
作者: gokcand 发布者: 2017 年 12 月 27 日

1

383469 作者的声誉

决定

This will return nothing in most clients because you only see what the last command returned:

BEGIN;
SELECT something FROM some_table;
COMMIT;

Try instead:

BEGIN;
SELECT something FROM some_table;

But don't forget to COMMIT or ROLLBACK later to terminate the open transaction.

SQL Fiddle does not allow explicit transaction wrappers. I quote:

All SQL queries are run within a transaction that gets immediately rolled-back after the SQL executes.

BEGIN; issued inside an open transaction only issues a WARNING - which is not displayed in SQL Fiddle (you see a result with 0 rows).

COMMIT; raises the error you saw.

And in your attempt with omitting the semicolon after the SELECT, COMMIT is interpreted as table alias:

BEGIN;
SELECT something FROM some_table
COMMIT;

... is equivalent to:

BEGIN;
SELECT something FROM some_table AS commit;

So that's another misunderstanding.

作者: Erwin Brandstetter 发布者: 2017 年 12 月 27 日
32x32