Nested transactions in postgres

[bjg2]

Hi, thanks for this lib as this is really needed.

Looking at the code, I'm not sure I understand how nested transactions work... As far as I understand, Beginx() uses the same Node object and just does a savepoint and overrides the last savepoint that happened?

If I understand code correctly, below code with 2 savepoints would not work?

create table testtable (
	field1 text,
	field2 int
)

begin transaction

insert into testtable (field1, field2) values ('edsa', 1)

SAVEPOINT sp_1;

insert into testtable (field1, field2) values ('eds22a', 13)

SAVEPOINT sp_2;

insert into testtable (field1, field2) values ('eds22eea', 123)

-- 3 rows
select * from testtable

ROLLBACK TO SAVEPOINT sp_2;

-- 2 rows
select * from testtable

ROLLBACK TO SAVEPOINT sp_1;

-- 1 row
select * from testtable

rollback transaction

[skateinmars]

Hello, and sorry for the very late reply!

Indeed, the savepoint ID is overridden https://github.com/heetch/sqalx/blob/bc03be6a919942c7bddefb3cf835a7071669a245/sqalx.go#L147

So multiple savepoints will not work, the second time Rollback will get called it will roll back the entire transaction instead of rollbacking to the previous savepoint.

I think this could be solved by using a slice of savepoints?