A thought on Index Organized Tables
in [PgSql]
|
Prev: [COMMITTERS] pgsql: Oops, don't forget to rewind the directory before scanning it to
Next: Time travel on the buildfarm
From: Tom Lane on 28 Feb 2010 10:47 Gokulakannan Somasundaram <gokul007(a)gmail.com> writes: > a) We are already going from table to index to do unique checks. This is the > same thing, which we will do to go and update the snapshot in the indexes. No, it is not the same thing. Updating index snapshots requires being able to *re-find* a previously made index entry for the current row. And it has to be done 100% reliably. The worst that happens if an index entry is not found when it should be during a uniqueness check is that the uniqueness constraint is not enforced properly; which is bad but it doesn't lead to internally-inconsistent data structures. > b) The way, it should work would be to have a check on whether the operator > is broken / function is volatile and put the onus on the user to make sure > that they are updated correctly. Pretending the problem doesn't exist doesn't make it go away ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Gokulakannan Somasundaram on 1 Mar 2010 01:35 > No, it is not the same thing. Updating index snapshots requires being > able to *re-find* a previously made index entry for the current row. > And it has to be done 100% reliably. The worst that happens if an index > entry is not found when it should be during a uniqueness check is that > the uniqueness constraint is not enforced properly; which is bad but it > doesn't lead to internally-inconsistent data structures. > Hmmm... OK Fine... I am leaving this proposal once and for all. > > Pretending the problem doesn't exist doesn't make it go away ... > > Because this is how it is done in other databases Ref: .http://www.akadia.com/services/ora_function_based_index_2.html Thanks, Gokul.
From: Gokulakannan Somasundaram on 1 Mar 2010 01:41 > > The transaction information on tuples take 18 bytes plus several info > bits. It's possible just storing a subset of that would be useful but > it's unclear. And I think it would complicate the code if it had to > sometimes fetch the heap tuple to get the rest and sometimes doesn't. > Visibility map had a similar proposal and it got accepted. Fine... I think, if you guys are going to stress so hard, then there might be some issues, which i am not foreseeing right now. > > I think you have to take up a simpler project as a first project. This > is a major overhaul of transaction information and it depends on > understanding how a lot of different areas work -- all of which are > very complex tricky areas to understand. > > Yep.. i would start by just joining in someone's project to help them out. Thanks, Gokul.
From: Gokulakannan Somasundaram on 2 Mar 2010 00:43 > > > a) We are already going from table to index to do unique checks. This is > the > > same thing, which we will do to go and update the snapshot in the > indexes. > > No, it is not the same thing. Updating index snapshots requires being > able to *re-find* a previously made index entry for the current row. > And it has to be done 100% reliably. The worst that happens if an index > entry is not found when it should be during a uniqueness check is that > the uniqueness constraint is not enforced properly; which is bad but it > doesn't lead to internally-inconsistent data structures. > > Tom, We are also going to indexes to maintain the referential integrity constraints like foreign keys. Say there are constraints like 'On Delete Cascade' and 'On Delete Restrict', they are maintained through the indexes and if we say that indexes can return wrong results, then the referential integrity is lost and we no longer are ACID compliant. Thanks, Gokul.
From: Gokulakannan Somasundaram on 2 Mar 2010 04:04
> > > I think you have to take up a simpler project as a first project. This > is a major overhaul of transaction information and it depends on > understanding how a lot of different areas work -- all of which are > very complex tricky areas to understand. > > > Greg, I just feel the fast full index scan may not be of much value, if we have to go to the table for visibility information. I think the feature needs the visibility map to get completed. Please let me know, if you feel otherwise. Thanks, Gokul. |