Re: [GENERAL] Serializable Isolation and read/write conflict with index and different keys
I have inserted a row that i don't touch, runned ANALYZE and tried again but with the same result. I'm using postgres 9.6.3 and EXPLAIN ANALYZE shows this: Index Scan using users_username_idx on users (cost=0.13..8.14 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=1) Index Cond: (name = 'bob'::text) 2017-08-30 0:13 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: > On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.loo...@gmail.com> wrote: > > I'm trying to use the serializable isolation but i'm getting read/write > > dependencies error even if i have an unique index on the column used and > the > > transactions are using different keys. > > For an example see this gist: > > https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e > > Is this an expected behavior? > > Take a look at the EXPLAIN plan for the SELECT. You'll get fine > grained SIREAD locks if it's doing a btree index scan, but table-level > SIREAD locks for other kinds of plans. Sometimes simple SSI tests can > show a lot of false positives just because of empty tables or missing > statistics (ANALYZE). > > -- > Thomas Munro > http://www.enterprisedb.com >
Re: [GENERAL] Serializable Isolation and read/write conflict with index and different keys
I thinking that i have found the culprit. SSI locks leaf pages on the btree index so if the different values are covered by the same page i will get a serialization failure Is this correct? On mer 30 ago 2017, 08:45 Luca Looz <luca.loo...@gmail.com> wrote: > I have inserted a row that i don't touch, runned ANALYZE and tried again > but with the same result. > I'm using postgres 9.6.3 and EXPLAIN ANALYZE shows this: > > Index Scan using users_username_idx on users (cost=0.13..8.14 rows=1 > width=4) (actual time=0.020..0.020 rows=0 loops=1) > >Index Cond: (name = 'bob'::text) > > > 2017-08-30 0:13 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: > >> On Wed, Aug 30, 2017 at 4:28 AM, Luca Looz <luca.loo...@gmail.com> wrote: >> > I'm trying to use the serializable isolation but i'm getting read/write >> > dependencies error even if i have an unique index on the column used >> and the >> > transactions are using different keys. >> > For an example see this gist: >> > https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e >> > Is this an expected behavior? >> >> Take a look at the EXPLAIN plan for the SELECT. You'll get fine >> grained SIREAD locks if it's doing a btree index scan, but table-level >> SIREAD locks for other kinds of plans. Sometimes simple SSI tests can >> show a lot of false positives just because of empty tables or missing >> statistics (ANALYZE). >> >> -- >> Thomas Munro >> http://www.enterprisedb.com >> > >
[GENERAL] Serializable Isolation and read/write conflict with index and different keys
I'm trying to use the serializable isolation but i'm getting read/write dependencies error even if i have an unique index on the column used and the transactions are using different keys. For an example see this gist: https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e Is this an expected behavior?
Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check
Thanks for the explanation! Can these checks be implemented or the data needed is not there and adding it will only add an overhead for the majority of use cases? 2017-07-19 20:42 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>: > Luca Looz <luca.loo...@gmail.com> writes: > > After some tests it seems that this happens when the same row is covered > by > > more than 1 update in the same transaction even without any change. > > Is this an expected behavior? Why it happens? > > Yes, see comment in RI_FKey_fk_upd_check_required: > > * If the original row was inserted by our own transaction, we > * must fire the trigger whether or not the keys are equal. > This > * is because our UPDATE will invalidate the INSERT so that the > * INSERT RI trigger will not do anything; so we had better do > the > * UPDATE check. (We could skip this if we knew the INSERT > * trigger already fired, but there is no easy way to know > that.) > > Although this is talking about the BEGIN; INSERT; UPDATE; COMMIT case, > the code has no way to tell that apart from BEGIN; UPDATE; UPDATE; COMMIT. > > regards, tom lane >
[GENERAL] UPDATE column without FK fires other FK triggers constraint check
I was analyzing an update function and i have noticed "Trigger for constraint" entries for foreign keys that i wasn't using or referring. After some tests it seems that this happens when the same row is covered by more than 1 update in the same transaction even without any change. Here is a dbfiddle example: http://dbfiddle.uk/?rdbms=postgres_9.6=368289a7338031b8a7b7a90f0fd25d7c Is this an expected behavior? Why it happens? I have initially asked this on dba stackexchange: https://dba.stackexchange.com/questions/180012/
