Re: [GENERAL] Transaction ID not logged if no explicit transaction used

Tom Lane wrote: > Alvaro Herrera writes: > > Mart�n Marqu�s wrote: > >> This really gives little use for recovery_target_xid. :( > > > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, > > which has the correct XIDs. It's obviously a worse

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

El 10/02/16 a las 20:11, Adrian Klaver escribió: >> >> So, my question is: Is this a bug, or a feature? I recall being able to >> log xids on DDLs but can't find the correct settings now. > > Maybe?: > > %v Virtual transaction ID (backendID/localXID) AFAICS that value won't help if I need

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

Alvaro Herrera writes: > Martín Marqués wrote: >> This really gives little use for recovery_target_xid. :( > Hmm, you can still use pg_xlogdump to figure it out from the actual WAL, > which has the correct XIDs. It's obviously a worse solution though from > the user's

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

Martín Marqués wrote: > El 10/02/16 a las 21:46, Tom Lane escribió: > > We could maybe fix this by redefining %x as "the current or most recent > > xid", so that it'd still be valid for messages issued post-commit. > > But I'm afraid that would add about as many bad behaviors as it would > >

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

El 10/02/16 a las 21:46, Tom Lane escribió: > > Think you're outta luck on that. If we logged the duration before > commit, it would be entirely misleading for short commands, because > the time needed to commit wouldn't be included. So we log it after, > when there's no longer any active

[GENERAL] Transaction ID not logged if no explicit transaction used

Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd result was that I always got a zero as the xid.

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

=?UTF-8?Q?Mart=c3=adn_Marqu=c3=a9s?= writes: > [ log_line_prefix %x frequently reports zero ] > <2016-02-10 17:41:19 EST [5729]: [1] xid=0 > db=data,user=postgres,app=psql,client=[local]>LOG: duration: 17.242 ms > statement: create table test_xid (id int); > <2016-02-10

Re: [GENERAL] Transaction ID not logged if no explicit transaction used

On 02/10/2016 02:58 PM, Martín Marqués wrote: Hi, I've been fooling around on a test environment where I wanted to run some PITR tests using recovery_target_xid. So I started setting up postgresql.conf with log_statement='ddl' (and 'mod' also) and the %x placeholder in log_line_prefix: Odd

Re: [GENERAL] Transaction ID Wraparound Monitoring

On Tue, Aug 4, 2015 at 6:24 AM, William Dunn dunn...@gmail.com wrote: Hello Jan, I think your calculation is slightly off because per the docs when PostgreSQL comes within 1 million of the age at which an actual wraparound occurs it will go into the safety shutdown mode. Thus the calculation

Re: [GENERAL] Transaction ID Wraparound Monitoring

On 8/4/15 2:47 AM, Jan Keirse wrote: CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS real) AS perc_until_wraparound_server_freeze (Note that we do this at the table level rather than the database level like you did, though, so that we have the information we need to tune

Re: [GENERAL] Transaction ID Wraparound Monitoring

Hello Jan, I think your calculation is slightly off because per the docs when PostgreSQL comes within 1 million of the age at which an actual wraparound occurs it will go into the safety shutdown mode. Thus the calculation should be ((2^32)-1)/2-100 rather than just ((2^32)-1)/2 as I think

Re: [GENERAL] Transaction ID Wraparound Monitoring

On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am

Re: [GENERAL] Transaction ID Wraparound Monitoring

On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in

Re: [GENERAL] Transaction ID Wraparound Monitoring

On 07/30/2015 08:41 AM, Jan Keirse wrote: On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/30/2015 02:55 AM, Jan Keirse wrote: Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be

[GENERAL] Transaction ID Wraparound Monitoring

Hello, we have some very write heavy databases and I have our monitoring system watch the transaction age of my databases to be alerted before we get into problems in case autovacuum can't keep up to avoid transaction ID wraparound. The query I am executing is this: SELECT

[GENERAL] Transaction ID wraparound, Oracle style

Here is an article on a recently discovered Oracle flaw, which allows SCN to reach its limit. http://www.computerworld.com/s/article/9223506/Fundamental_Oracle_flaw_revea led?taxonomyId=18pageNumber=1 Please don't beat me for posting a link for an Oracle related article. If you despise a very

Re: [GENERAL] Transaction ID wraparound, Oracle style

On Wed, Jan 18, 2012 at 11:21 AM, Igor Polishchuk i...@powerreviews.com wrote: Here is an article on a recently discovered Oracle flaw, which allows SCN to reach its limit. http://www.computerworld.com/s/article/9223506/Fundamental_Oracle_flaw_revea led?taxonomyId=18pageNumber=1 Please don't

Re: [GENERAL] Transaction ID wraparound, Oracle style

On Jan 18, 2012, at 2:15 PM, Scott Marlowe wrote: On Wed, Jan 18, 2012 at 11:21 AM, Igor Polishchuk i...@powerreviews.com wrote: Here is an article on a recently discovered Oracle flaw, which allows SCN to reach its limit.

[GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?

Hi, In old version of Postgres we have to execute 'VACUUM FULL' to solve problem of transaction ID wraparound, do we need to execute 'VACUUM FULL' in Postgres 8.3 or 8.4 to avoid this problem? How to check using SQL if transaction ID is close to wraparound? Michal Szymanski

Re: [GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?

Michal Szymanski wrote: Hi, In old version of Postgres we have to execute 'VACUUM FULL' to solve problem of transaction ID wraparound, do we need to execute 'VACUUM FULL' in Postgres 8.3 or 8.4 to avoid this problem? No, plain VACUUM suffices. How to check using SQL if transaction ID is

Re: [GENERAL] transaction ID wraparound - should I use 'VACUUM' or 'VACUUM FULL' ?

Alvaro Herrera alvhe...@commandprompt.com writes: Michal Szymanski wrote: In old version of Postgres we have to execute 'VACUUM FULL' to solve problem of transaction ID wraparound, do we need to execute 'VACUUM FULL' in Postgres 8.3 or 8.4 to avoid this problem? No, plain VACUUM suffices.

[GENERAL] Transaction id wraparound problem

I've encountered transaction wraparound problems in a long-running test using postgresql 7.4.8. There is no critical data at risk, but I do need to understand the problem and get a fix into our product as quickly as possible. My postgres log file has messages like this: 2006-11-14 04:08:19

Re: [GENERAL] Transaction id wraparound problem

On Wednesday November 15 2006 4:18 pm, Morris Goldstein wrote: If I'm vacuuming every day (or two), and not running anywhere near 1 billion transactions a day, why am I running into transaction id wraparound problems? Is this just complaining that template0 and template1 haven't been

[GENERAL] Transaction id wraparound questions

It seems that we have not been vacuuming our production database properly. We have been explicitly vacuuming all tables individually but have not vacuumed the entire database. A recent vacuum of the entire database gave us the dreaded You may have already suffered transaction-wraparound data

Re: [GENERAL] Transaction id wraparound questions

Marc Munro [EMAIL PROTECTED] writes: A recent vacuum of the entire database gave us the dreaded You may have already suffered transaction-wraparound data loss. warning. If you have in fact been vacuuming *every* table including all the system catalogs, then you don't need to panic; this

Re: [GENERAL] Transaction id

[EMAIL PROTECTED] wrote: Is it possible to find system change ID in SQL or stored procedures? By system change ID I understand an internal serialized number which could be used to serialize all SQL submitted into the database? Thank you in advance, Laimis Not sure what you exactly envision here.