Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 3:26 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Luca Ferrari wrote: >>> Why is xmin greater than the current transaction id (and most notably >>> not "fixed")? > >> Something is using subtransactions there. My first guess would be that >> there are triggers with EXCEP

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Tom Lane
Alvaro Herrera writes: > Luca Ferrari wrote: >> Why is xmin greater than the current transaction id (and most notably >> not "fixed")? > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example doesn't show > any. Or may

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Alvaro Herrera
Luca Ferrari wrote: > Any other idea? None here. Maybe try attaching a debugger, setting a breakpoint on AssignTransactionId, and grab backtraces when it is hit. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 2:29 PM, Andres Freund wrote: > That doesn't look like plain postgres behaviour to me. Any chance you're > using a pooler in statement mode in front of postgres? None I'm aware of, since the machine is using postgresql locally and I'm connecting to it using the port 5432.

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 1:53 PM, Alvaro Herrera wrote: > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example doesn't show > any. Or maybe you have event triggers. Thanks, but I don't see any event trigger: > \dy

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Andres Freund
Hi, On 2017-11-06 10:36:06 +0100, Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cm

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Alvaro Herrera
Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cmax, i FROM foo; > xmin | cmin | xm

Re: [GENERAL] xmin horizon?

2015-07-29 Thread Torsten Förtsch
On 29/07/15 21:13, CS DBA wrote: > The documentation for pg_stat_activity lists this column: > > backend_xmin xid The current backend's xmin horizon. > > Can someone point me to a better understanding on "xmin horizon"? https://momjian.us/main/writings/pgsql/mvcc.pdf you can find this t

Re: [GENERAL] xmin horizon?

2015-07-29 Thread Michael Paquier
On Thu, Jul 30, 2015 at 4:13 AM, CS DBA wrote: > The documentation for pg_stat_activity lists this column: > > backend_xmin xid The current backend's xmin horizon. > > Can someone point me to a better understanding on "xmin horizon"? This defines the oldest transaction ID that a given bac

Re: [GENERAL] XMIN semantic at peril ?

2007-10-18 Thread Karsten Hilbert
On Thu, Oct 11, 2007 at 12:03:47PM -0400, Tom Lane wrote: > > If in the meantime another writer changed the data we > > originally read we would detect that by xmin having changed > > hence no row to be updated. So, yes, there is a *tiny* > > failure condition: > > Hmm. I think the failure condi

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Karsten Hilbert
On Thu, Oct 11, 2007 at 01:48:10PM -0400, Tom Lane wrote: > > If the query is always based on a primary key + XMIN, It is. > > and since > > vacuum is the only thing that sets FrozenTransactionId, would it be > > unsane to change the update to > > > - update row with "... where pk=... and XM

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Tom Lane
Erik Jones <[EMAIL PROTECTED]> writes: > If the query is always based on a primary key + XMIN, and since > vacuum is the only thing that sets FrozenTransactionId, would it be > unsane to change the update to > - update row with "... where pk=... and XMIN IN (old_xmin_from_read, > FrozenTransac

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Erik Jones
On Oct 11, 2007, at 11:03 AM, Tom Lane wrote: Karsten Hilbert <[EMAIL PROTECTED]> writes: On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote: One question I'd have though is whether "freezing" of old tuples is likely to confuse your app. Well, what we do is this: - read row includi

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Tom Lane
Karsten Hilbert <[EMAIL PROTECTED]> writes: > On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote: >> One question I'd have though is whether "freezing" of old tuples is >> likely to confuse your app. > Well, what we do is this: > - read row including XMIN > - do some UI stuff without open t

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Karsten Hilbert
On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote: > > How likely is XMIN (or equivalent) to NOT change on each > > successful (write) transaction commit anymore ? > > No chance of that, unless we abandon MVCC for something else, which > again seems highly unlikely. I figured as much b

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Karsten Hilbert
On Thu, Oct 11, 2007 at 04:18:30PM +0100, Filip Rembiałkowski wrote: > Subject: Re: [GENERAL] XMIN semantic at peril ? > > 2007/10/11, Tom Lane <[EMAIL PROTECTED]>: > > The only argument you cited that seems impressive to me is the one > > about it being a Postgres-is

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Filip Rembiałkowski
2007/10/11, Tom Lane <[EMAIL PROTECTED]>: > The only argument you cited that seems impressive to me is the one > about it being a Postgres-ism. Are you willing to have GNUmed tied > tightly to Postgres? Well, at least not in all aspects, if I may cut in. (from http://www.gnumed.org/) error ins

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Karsten Hilbert
On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote: > The only argument you cited that seems impressive to me is the one > about it being a Postgres-ism. Are you willing to have GNUmed tied > tightly to Postgres? Personally, yes, no problem and thanks for it :-)) Karsten -- GPG key ID E4

Re: [GENERAL] XMIN semantic at peril ?

2007-10-11 Thread Tom Lane
Karsten Hilbert <[EMAIL PROTECTED]> writes: > How likely is XMIN (or equivalent) to become invisible to > SQL level user space ? No one has suggested this. I suppose the argument could be made that the system columns are an unwarranted intrusion on users' column namespace, but we'd probably h

Re: [GENERAL] xmin

2006-08-03 Thread Richard Huxton
Ilja Golshtein wrote: Hello! Is it Ok to use xmin column in application programm? Purpose is obvios: I need to know the record was not changed since a moment in the past and things like this. So every UPDATE should somehow change value of the column. The choice it trigger or xmin. Which is b

Re: [GENERAL] "xmin" system column

2006-01-31 Thread Jim C. Nasby
On Fri, Jan 27, 2006 at 11:18:23AM +0100, Christian Kratzer wrote: > Hi, > > On Thu, 26 Jan 2006, Eric B. Ridge wrote: > > > > > > >hahaha, *blush*. I could just use "now()", right? pg8.1 docs say that > >now()/CURRENT_TIMESTAMP "return the start time of the current transaction; > >their valu

Re: [GENERAL] "xmin" system column

2006-01-27 Thread Christian Kratzer
Hi, On Thu, 26 Jan 2006, Eric B. Ridge wrote: hahaha, *blush*. I could just use "now()", right? pg8.1 docs say that now()/CURRENT_TIMESTAMP "return the start time of the current transaction; their values do not change during the transaction". I could use a composite of (now(), GetTopTra

Re: [GENERAL] "xmin" system column

2006-01-27 Thread Marko Kreen
On 1/26/06, Eric B. Ridge <[EMAIL PROTECTED]> wrote: > Outside of "VACUUM FREEZE", is there any way the "xmin" column in a > relation can change, assuming of course the tuple is never updated > again? I'm considering using this as a way to identify all tuples > modified in the same transaction (in

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Eric B. Ridge
On Jan 26, 2006, at 5:22 PM, Michael Fuhr wrote: I suppose a sequence is out of the question? Too easy to get it wrong? Well, I just wanted to avoid embedding this idea into my application. Would rather Postgres take care of it for me. Not in the standard installation, but I think a C fu

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Michael Fuhr
On Thu, Jan 26, 2006 at 03:22:50PM -0700, Michael Fuhr wrote: > On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote: > > Is there some kind of "TopXID" magic variable/function that I haven't > > found in the documentation? > > Not in the standard installation, but I think a C function

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Michael Fuhr
On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote: > I really need a way to create a unique identifier at the start of a > top-level transaction, and be able to use it via triggers and/or > column default values in that or its subtransactions. I suppose a sequence is out of the que

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Tom Lane
"Eric B. Ridge" <[EMAIL PROTECTED]> writes: > That's what I was afraid of. I've pondering making a "grouping" > column that gets set to "xmin" via an UPDATE trigger. At least I'd > have a constant value that would survive database dumps and reloads. That will most assuredly NOT work. You wi

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Eric B. Ridge
On Jan 26, 2006, at 4:50 PM, Michael Fuhr wrote: test=> SELECT xmin, * FROM foo; xmin | x +--- 424584 | 1 424585 | 2 424584 | 3 (3 rows) hmm. Is it possible to grab that first xmin value when the transaction first starts, then I can explicitly use when I need it? eric --

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Eric B. Ridge
On Jan 26, 2006, at 4:44 PM, Tom Lane wrote: "Eric B. Ridge" <[EMAIL PROTECTED]> writes: Outside of "VACUUM FREEZE", is there any way the "xmin" column in a relation can change, assuming of course the tuple is never updated again? If the tuple lives long enough, VACUUM will change it to Fro

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Michael Fuhr
On Thu, Jan 26, 2006 at 04:19:34PM -0500, Eric B. Ridge wrote: > Outside of "VACUUM FREEZE", is there any way the "xmin" column in a > relation can change, assuming of course the tuple is never updated > again? I'm considering using this as a way to identify all tuples > modified in the same

Re: [GENERAL] "xmin" system column

2006-01-26 Thread Tom Lane
"Eric B. Ridge" <[EMAIL PROTECTED]> writes: > Outside of "VACUUM FREEZE", is there any way the "xmin" column in a > relation can change, assuming of course the tuple is never updated > again? If the tuple lives long enough, VACUUM will change it to FrozenTransactionId eventually, even without

Re: [GENERAL] XMIN/xid vs UNION

2004-10-29 Thread Tom Lane
Karsten Hilbert <[EMAIL PROTECTED]> writes: > However, the question still holds true: Is there any > particular reason those operators aren't found in my PG > installation despite being listed as added since 7.2 ? The only thing that was added in 7.2 was xid equality. There was some talk recently

Re: [GENERAL] XMIN/xid vs UNION

2004-10-29 Thread Karsten Hilbert
> Try a cast, or just use UNION ALL Thanks. Casting didn't work (it was missing the proper cast function from xid to int4) but using UNION ALL worked. This was also possible to use in my case since both parts of the UNION do indeed return distinct sets of rows so UNION ALL does not produce duplica

Re: [GENERAL] XMIN/xid vs UNION

2004-10-29 Thread Martijn van Oosterhout
Try a cast, or just use UNION ALL On Fri, Oct 29, 2004 at 04:44:37PM +0200, Karsten Hilbert wrote: > Dear all, > > some of my views are created with help of the UNION operator. > Now, I also need to include the base table XMIN system column > into those views. Which works fine (as long as I alias