[GENERAL] Row versioning
Hi all, has anyone implemented row versions/timestamps in PostgreSQL or any thoughts on this? Did I hit the right term? What I want to achieve is optimistic concurrency beyound transaction boundaries. When retrieving data I would also retrieve the row version and later on, in a different transaction, before updating the data, I could check if was unchanged. (row version at read time = row version at update time) What are the pro's and con's about adding a "sequence" row that is incremented by a trigger each time the row is updated? Is having a timestamp row instand better? Regards Ruediger -- Geschenkt: 3 Monate GMX ProMail + 3 Ausgaben der TV Movie mit DVD Jetzt anmelden und testen http://www.gmx.net/de/go/mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Row versioning
thanks for your replay. This approach sounds very comfy. As I read the documentation this is kind of a "transaction sequence" or better a "unique transaction id". Am I right with this? So every row inserted or updated within the same transaction is tagged with the same xmin. Is there any information wether this approach is future proof? I heard the OID is depecated now, maybe XMIN is next, no idea... TIA Ruediger > > has anyone implemented row versions/timestamps in > PostgreSQL or any > > thoughts on this? > > Did I hit the right term? What I want to achieve > is optimistic > > concurrency beyound transaction boundaries. When > retrieving data > > I would also retrieve the row version and later > on, in a different > > transaction, before updating the data, I could > check if was unchanged. > > You could use the xmin system column for this. > > regards, tom lane > > > -- Geschenkt: 3 Monate GMX ProMail + 3 Ausgaben der TV Movie mit DVD Jetzt anmelden und testen http://www.gmx.net/de/go/mail ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] returning inserted rows, derived tables design
Hello again, as I am new to PostgreSQL (great pice of softwork btw) this probably won't be the last question. I access PostgreSQL from Java via the PostgreSQL JDBC driver. I am currently building a little framework that provides basic CRUD operations by mapping class properties to database columns. All my primary keys are artificial and built by sequences (datat type bigserial). Now I need to (re-)read the row that was inserted/updated because triggers may have changed the column values, row versions (CMAX, thanks to Tom Lane) are different and so on. For the update operation I "know" wich row to select since the primary key alread exists. But what about the insert? When I know the sequence for a particular primary key I could obtain its last value select the row with this very primary key... This would imply that my framework must "know" wich sequence belongs to a primary key. I don't like this idea. To come to an end, a RETURNNG clause for the insert/update statement would be the perfect solution, but there isn't any, right? Another uncertainty: Is it wise to have most tables derive from one base table (concering performace, concurrence, maintainability, etc). Let's say 99% of my tables have an Id (artificial primary key), CreatedBy, UpdatedBy column. Should I put those columns in a base table and derive from it or let each table have these columns by itself. As for the Id column (bigserial) in the "derived solution" there would be only one sequence wich makes it unique for the whole database. That sounds very appealing from the application point of view. Thanks for your patience Ruediger -- +++ GMX DSL Premiumtarife 3 Monate gratis* + WLAN-Router 0,- EUR* +++ Clevere DSL-Nutzer wechseln jetzt zu GMX: http://www.gmx.net/de/go/dsl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
