Re: [GENERAL] Transaction apply speed on the standby

On Fri, Jan 27, 2017 at 3:34 AM, Rakesh Kumar wrote: > Ver 9.6.1 > > In a streaming replication can it be assumed that if both primary and > standby are of the same hardware, then the rate at which transactions are > applied on the standby will be same as that on primary. Or standbys are > always

Re: [GENERAL] Transaction apply speed on the standby

On Thu, Jan 26, 2017 at 8:34 AM, Rakesh Kumar wrote: > Ver 9.6.1 > > In a streaming replication can it be assumed that if both primary and > standby are of the same hardware, then the rate at which transactions are > applied on the standby will be same as that on primary. Or standbys are > always

Re: [GENERAL] Transaction lock granting order

Thanks a bunch Tom, appreciate the quick response. On Mon, Dec 5, 2016 at 12:33 PM, Tom Lane wrote: > Joshua Ma writes: > > Can someone point me to documentation on (or confirm) this detail on > > Postgres locking? > > > - Transaction X starts and acquires a lock on a table T > > - Transaction

Re: [GENERAL] Transaction lock granting order

Joshua Ma writes: > Can someone point me to documentation on (or confirm) this detail on > Postgres locking? > - Transaction X starts and acquires a lock on a table T > - Transaction Y starts and attempts to acquire a conflicting lock on T - it > is now blocked > - Transaction Z starts and also a

Re: [GENERAL] Transaction serialization

Thank you Thomas, this was exactly what I was looking for. On 24.6.2016 0:57, Thomas Munro wrote: On Fri, Jun 24, 2016 at 4:13 AM, Dusan Milanov wrote: Hi, A question about transactions: does postgres report serialization errors before a transaction is committed? Obviously, it does on commit

Re: [GENERAL] Transaction serialization

On Fri, Jun 24, 2016 at 4:13 AM, Dusan Milanov wrote: > Hi, > > A question about transactions: does postgres report serialization errors > before a transaction is committed? Obviously, it does on commit, but how > about previous statements? Can there be an ERRCODE_T_R_SERIALIZATION_FAILURE > error

Re: [GENERAL] Transaction Rollback Error: DeadLock Detected

On 02/15/2016 11:44 PM, subhan alimy wrote: Hello Everyone, Here I have got and error of concurrent update, Transaction Rollback Error: deadlock detected process 4801 waits for sharelock on transaction 944461 blocked by process 4700. process 4700 waits for sharelock on transaction 9444665 block

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 solution though from > > the user

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 POV, because it's hard to f

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 > > remov

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 transa

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 th

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 17:41:21 EST [5729]: [2]

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 res

Re: [GENERAL] Transaction check error installing PostGIS

Hi John, you were right. The one in use was 5.6.27-76.0 Percona Server. How did you guess that this one wasn't being use? On Fri, Jan 29, 2016 at 10:14 PM, Augori wrote: > Okay thanks, John. I will inquire about that. > > On Fri, Jan 29, 2016 at 10:11 PM, John R Pierce > wrote: > >> On 1/2

Re: [GENERAL] Transaction check error installing PostGIS

Okay thanks, John. I will inquire about that. On Fri, Jan 29, 2016 at 10:11 PM, John R Pierce wrote: > On 1/29/2016 6:49 PM, Augori wrote: > >> >> I don't know the answer to your question. I was assuming it has to do >> with the MYSQL server that's installed on this machine. I should clarify

Re: [GENERAL] Transaction check error installing PostGIS

On 1/29/2016 6:49 PM, Augori wrote: I don't know the answer to your question. I was assuming it has to do with the MYSQL server that's installed on this machine. I should clarify that I'm just one of a number of people who are trying to work together on this machine, so most of what's been

Re: [GENERAL] Transaction check error installing PostGIS

Yes, I should have said, postgis2_93. I guess it may be unrelated, but I'm not sure how to find out. Yes, thanks, I took a good look at that page which gives instructions for how to fix a different error. On Fri, Jan 29, 2016 at 6:20 PM, Adrian Klaver wrote: > On 01/29/2016 02:59 PM, Augori wr

Re: [GENERAL] Transaction check error installing PostGIS

Hi John, I don't know the answer to your question. I was assuming it has to do with the MYSQL server that's installed on this machine. I should clarify that I'm just one of a number of people who are trying to work together on this machine, so most of what's been done to this machine was not don

Re: [GENERAL] Transaction check error installing PostGIS

On 01/29/2016 02:59 PM, Augori wrote: Hello, I am trying to install PostGIS on a CentOS Linux release 7.2.1511 (Core). I installed PostGreSQL successfully. But when I try to install PostGIS using yum install postgres2_93 The above should be postgis2_93, correct? I get transaction conflict

Re: [GENERAL] Transaction check error installing PostGIS

On 1/29/2016 2:59 PM, Augori wrote: I get transaction conflict errors (2 of many are posted below): file /usr/share/mysql/charsets/README from install of mariadb-libs-1:5.5.44-2.el7.centos.x86_64 conflicts with file from package MySQL55-server-5.5.47-1.cp1148.x86_64 where is this MySQL55-

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

Re: [GENERAL] Transaction ID Wraparound Monitoring

On Tue, Aug 4, 2015 at 6:24 AM, William Dunn 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 should > be (

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 you

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 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

Re: [GENERAL] Transaction ID Wraparound Monitoring

On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver 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 case autovacuum

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 execu

Re: [GENERAL] Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4

On 06/19/2015 04:15 AM, Gary Cowell wrote: Hello I'm aware of the automatic transaction abort that occurs in PostgreSQL if you have DML throw an error during a transaction, this prevents future selects within transaction, until rollback or commit (and hence, new transaction). I'm okay with this.

Re: [GENERAL] Transaction abort difference between psql, isql, ODBC and JDBC pgsql 8.4

> From: Gary Cowell >To: pgsql-general@postgresql.org >Sent: Friday, 19 June 2015, 12:15 >Subject: [GENERAL] Transaction abort difference between psql, isql, ODBC and >JDBC pgsql 8.4 > > > >Hello > >I'm aware of the automatic transaction abort that occurs in PostgreSQL if you >have DML throw

Re: [GENERAL] Transaction-level advisory lock unlocking and transaction end

Saimon Lim writes: >> Transaction-level lock requests ... are automatically released at the end >> of the transaction ... > So, what order will the use of transactions and unlock the lock? > The transaction would be applied at first and the lock would be unlocked > after it or vice versa? > Or

Re: [GENERAL] Transaction completion timing

You could very well be right. We are using JPA under Hibernate, using container managed transactions. So T1 and T2 above are actually container managed transactions, each running in response to REST API requests. They should be bound 1:1 with underlying PostgreSQL transactions, but perhaps t

Re: [GENERAL] Transaction completion timing

"Steve Dodd" writes: > Say we have two transactions run sequentially: T1 writes some data, and T2 > reads the written data. There is a non-zero time delay between the apparent > T1 commit, and the subsequent T2 query. > Is there any guarantee that the data written in T1 will be visible to the >

Re: [GENERAL] Transaction control in shards through PLPROXY

Hi Sergey, Thank you for your reply. Have you implemented this while sharding your database? Did it cause any performance issues? Warm regards, GB On Mon, Jul 15, 2013 at 10:51 AM, Sergey Konoplev wrote: > On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas > wrote: > > Inspite of being aware

Re: [GENERAL] Transaction control in shards through PLPROXY

On Sun, Jul 14, 2013 at 10:56 PM, Granthana Biswas wrote: > Thank you for your reply. Have you implemented this while sharding your > database? Did it cause any performance issues? I used it for just several mission critical featured in one project, not widely. What about performance issues - it

Re: [GENERAL] Transaction control in shards through PLPROXY

On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas wrote: > Inspite of being aware that PLPROXY does autocommit for DML functions called > on shards, I was wondering if there is any way around to put a set of DML > functions called from Router on shards inside a transaction so that all > updates o

Re: [GENERAL] transaction log file "000000010000097600000051" could not be archived: too many failures

Thanks...Removing the .ready files worked... Mathew On Tue, Oct 16, 2012 at 11:25 PM, Tom Lane wrote: > Mathew Thomas writes: > > I am getting the following error in my postgresql log file. > > > cp: cannot stat `pg_xlog/000109760051': No such file or > > directory > > If there's a

Re: [GENERAL] transaction log file "000000010000097600000051" could not be archived: too many failures

Mathew Thomas writes: > I am getting the following error in my postgresql log file. > cp: cannot stat `pg_xlog/000109760051': No such file or > directory If there's a .ready file corresponding to that, remove it. I'm not entirely sure how you could have ended up with a .ready file bu

Re: [GENERAL] Transaction question

On 07/11/2012 04:18 PM, Jeff Ross wrote: On 7/11/12 5:13 PM, Adrian Klaver wrote: On 07/11/2012 04:02 PM, Jeff Ross wrote: On 7/11/12 3:52 PM, Adrian Klaver wrote: Is there an index on this table? If so have you tried a REINDEX? Here's the table definition: jross@nirvana:/home/jross $ p

Re: [GENERAL] Transaction question

On 7/11/12 5:13 PM, Adrian Klaver wrote: On 07/11/2012 04:02 PM, Jeff Ross wrote: On 7/11/12 3:52 PM, Adrian Klaver wrote: Is there an index on this table? If so have you tried a REINDEX? Here's the table definition: jross@nirvana:/home/jross $ psql wykids psql (9.1.4, server 9.1.3) Type

Re: [GENERAL] Transaction question

On 07/11/2012 04:02 PM, Jeff Ross wrote: On 7/11/12 3:52 PM, Adrian Klaver wrote: Is there an index on this table? If so have you tried a REINDEX? Here's the table definition: jross@nirvana:/home/jross $ psql wykids psql (9.1.4, server 9.1.3) Type "help" for help. wykids=# \d survey_answ

Re: [GENERAL] Transaction question

On 7/11/12 4:24 PM, Marcin Mańk wrote: On Tue, Jul 10, 2012 at 10:06 PM, Jeff Ross > wrote: 2012-06-19 15:37:36.283752500 LOG: statement: update survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2 and srv_resp_pp_id = 25399 jros

Re: [GENERAL] Transaction question

On 7/11/12 3:52 PM, Adrian Klaver wrote: On 07/11/2012 02:41 PM, Jeff Ross wrote: On 7/11/12 2:07 PM, Adrian Klaver wrote: On 07/11/2012 07:01 AM, Jeff Ross wrote: On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 0

Re: [GENERAL] Transaction question

On Tue, Jul 10, 2012 at 10:06 PM, Jeff Ross wrote: > 2012-06-19 15:37:36.283752500 LOG: statement: update > survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2 > and srv_resp_pp_id = 25399 > > jross@wykids localhost# select * from survey_response where > srv_resp_submitted

Re: [GENERAL] Transaction question

On 07/11/2012 02:41 PM, Jeff Ross wrote: On 7/11/12 2:07 PM, Adrian Klaver wrote: On 07/11/2012 07:01 AM, Jeff Ross wrote: On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi a

Re: [GENERAL] Transaction question

On 7/11/12 2:07 PM, Adrian Klaver wrote: On 07/11/2012 07:01 AM, Jeff Ross wrote: On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! F

Re: [GENERAL] Transaction question

On 07/11/2012 07:01 AM, Jeff Ross wrote: On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! So would it be possible to see the actu

Re: [GENERAL] Transaction question

On 07/11/2012 07:01 AM, Jeff Ross wrote: On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! For your initial attempt everything was don

Re: [GENERAL] Transaction question

On 7/10/12 8:39 PM, Adrian Klaver wrote: On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! For your initial attempt everything was done in one session? All the inserts were d

Re: [GENERAL] Transaction question

On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! For your initial attempt everything was done in one session? All the inserts were done in one session, yes. I am also confu

Re: [GENERAL] Transaction question

On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, I have an anomaly on my hands that I'm at a loss to understand. We recently ran a small survey where participants were required to answer all the questions. After validation for skipped questions, mis-ra

Re: [GENERAL] Transaction question

On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, I have an anomaly on my hands that I'm at a loss to understand. We recently ran a small survey where participants were required to answer all the questions. After validation for skipped questions, mis-ranking answers that had to be ranked and so

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 > 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 >> l

Re: [GENERAL] Transaction ID wraparound, Oracle style

On Wed, Jan 18, 2012 at 11:21 AM, Igor Polishchuk 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=18&pageNumber=1 > > Please don't beat me for po

Re: [GENERAL] Transaction ordering on log-shipping standby

On Tue, Sep 20, 2011 at 11:46 AM, Andrew Rose wrote: > I've got a question about transaction ordering in a log-shipping replication > environment. > > Here's the setup... > > - A pair of PostgreSQL 9 servers in active/standby configuration, using > log-shipping > - A single client, using a singl

Re: [GENERAL] Transaction ordering on log-shipping standby

On Tue, Sep 20, 2011 at 13:46, Andrew Rose wrote: > Or to put the question another way, is the ordering of transactions on the > active and standby servers guaranteed to be the same? Yes. The WAL serializes the order of transactions. It is applied to slaves in the same order that it's written on

Re: [GENERAL] Transaction wraparound vacuum synchronicity

On Wed, Mar 09, 2011 at 10:52:25AM +, Michael Graham wrote: > I have a database with a number of tables that are partitioned monthly, > after that the tables are mostly read only (on rare occasions we may > delete from a table but normally we just drop the partitions). Recently > I've noticed

Re: [GENERAL] Transaction wraparound vacuum synchronicity

On Wed, Mar 09, 2011 at 10:52:25AM +, Michael Graham wrote: > Perhaps I should just force a vacuum on some of the tables the break it? It seems to me that you might want to VACUUM FREEZE the table when it becomes read-only. (I note that FREEZE is deprecated according to the 9.0 manual. Too b

Re: [GENERAL] Transaction with in function

On Wed, May 26, 2010 at 2:52 AM, Ravi Katkar wrote: > How can we achieve nested transactions? ( may be using save points )with in > functions. > Is there any work around? It unfortunately can't be done from within the database. There is only one workaround -- using dblink or similar technology

Re: [GENERAL] Transaction with in function

: pgsql-general@postgresql.org Subject: Re: [GENERAL] Transaction with in function In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/

Re: [GENERAL] Transaction with in function

In response to Ravi Katkar : > > > I looking for solution for commit, rollback with in function. You can't use transactions within functions, use savepoints instead. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035

Re: [GENERAL] transaction control in pl/pgsql

Hi Merlin, hi Alban, thank you both for your helpful answers. Now, I splitted the function into smaller parts which have to be called seperately one after another. Probably, I will write a script for calling all the functions needed. Not as nice as an all in one function, but if there is no other

Re: [GENERAL] transaction control in pl/pgsql

On Thu, Apr 1, 2010 at 6:22 AM, Birgit Laggner wrote: > Dear list, > > I have some data (big size) and I've written a long function in pl/pgsql > which processes the data in several steps. At a test run my function > aborted because of memory exhaustion. My guess is, that everything what > happens

Re: [GENERAL] transaction control in pl/pgsql

On 1 Apr 2010, at 14:38, Birgit Laggner wrote: > Hi Alban, > > thanks for your detailed answer! > > My database settings: > max connections: 20 > work_mem: 100MB > shared buffers: 12000MB > > Server memory: > physical 32GB > total memory 50GB (incl. swap) > shared memory ?? Ok, so max work_mem

Re: [GENERAL] transaction control in pl/pgsql

Hi Alban, thanks for your detailed answer! My database settings: max connections: 20 work_mem: 100MB shared buffers: 12000MB Server memory: physical 32GB total memory 50GB (incl. swap) shared memory ?? I am not sure if I use deferred constraints - the only constraints I use in the function are

Re: [GENERAL] transaction control in pl/pgsql

On 1 Apr 2010, at 12:22, Birgit Laggner wrote: > Dear list, > > I have some data (big size) and I've written a long function in pl/pgsql > which processes the data in several steps. At a test run my function > aborted because of memory exhaustion. My guess is, that everything what > happens durin

Re: [GENERAL] transaction control in pl/pgsql

2010/4/1 Birgit Laggner > Hi Grzegorz, > > sorry, but that doesn't help me, perhaps you could get a little bit > clearer: > > @a) Does the use of SAVEPOINT avoid memory overflow? I could not find an > explanation about memory use in the documentation of SAVEPOINT. > > transactions don't really us

Re: [GENERAL] transaction control in pl/pgsql

Hi Grzegorz, sorry, but that doesn't help me, perhaps you could get a little bit clearer: @a) Does the use of SAVEPOINT avoid memory overflow? I could not find an explanation about memory use in the documentation of SAVEPOINT. @b) Do you mean I should not process my data or I should not use plpg

Re: [GENERAL] transaction control in pl/pgsql

2010/4/1 Birgit Laggner : > Dear list, > > I have some data (big size) and I've written a long function in pl/pgsql > which processes the data in several steps. At a test run my function > aborted because of memory exhaustion. My guess is, that everything what > happens during the function transact

Re: [GENERAL] transaction control in pl/pgsql

a) you can't explicitly control transactions in plpgsql. If you need some sort of a form of it, use save points. b) you are trying to outsmart database software, and this is just a biiig mistake, and you should stop doing that completely.

Re: [GENERAL] Transaction table

On Sun, Mar 21, 2010 at 1:30 PM, Vick Khera wrote: > Like the two Scott M's recommended, figure out your usage patterns and > partition across those vectors to optimize those searches.  I would > not worry about optimizing the insert pattern. Note that once the partitions get small enough, on big

Re: [GENERAL] Transaction table

Vick Khera wrote: You really *never* delete this data? I would suspect then that having a partitioning scheme where the number of partitions can grow over time is going to be important to you. he said a new table is created each day, but nothing about these daily tables being partitions in

Re: [GENERAL] Transaction table

On Sat, Mar 20, 2010 at 4:47 AM, Deepa Thulasidasan wrote: > transaction table to grow by 10 times in near future. In this regard, we > would like to know if this same structure of the transaction table and the > indexing would be sufficient for quick retrivel of data  or do we have to > partit

Re: [GENERAL] Transaction table

On Sat, Mar 20, 2010 at 5:26 AM, Scott Marlowe wrote: > On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan > wrote: > > Dear All, > > > > I have a query in postgresql if any one can support. > > > > A transaction table in a vehicle tracking application is inserted with > the current position of

Re: [GENERAL] Transaction table

On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan wrote: > Dear All, > > I have a query in postgresql if any one can support. > > A transaction table in a vehicle tracking application is  inserted with the > current position of each vehicle at regular interval (seconds). > This  transaction tab

Re: [GENERAL] Transaction wraparound problem with database postgres

On Sun, Mar 7, 2010 at 6:06 PM, Markus Wollny wrote: > Hi! > > After going several months without such incidents, we now got bit by the same > problem again. We have since upgraded the hardware we ran the database > cluster on and currently use version 8.3.7. The general outline of the > proble

Re: [GENERAL] Transaction wraparound problem with database postgres

Hi! > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Do your logs show any kind of error when vacuuming about > "only owner can vacuum" a table or anything? I grepped through the logs from the last four days and, no, there were none such errors whatsoever. Last vacuum analyze run retu

Re: [GENERAL] Transaction wraparound problem with database postgres

On Sun, Mar 7, 2010 at 6:06 PM, Markus Wollny wrote: > Hi! > > After going several months without such incidents, we now got bit by the same > problem again. We have since upgraded the hardware we ran the database > cluster on and currently use version 8.3.7. The general outline of the > proble

Re: [GENERAL] Transaction wraparound problem with database postgres

Hi! After going several months without such incidents, we now got bit by the same problem again. We have since upgraded the hardware we ran the database cluster on and currently use version 8.3.7. The general outline of the problem hasn't changed much though - we still don't use the database 'p

Re: [GENERAL] transaction logging in the form of SQL statements

2010/1/12 Omar Mehmood : > Is there any way to enable transaction logging in the format of SQL > statements for committed transactions only ?  In other words, a way to log > all the SQL statements (including START TRANSACTION and COMMIT statements) > for all committed mod type statements (INSERT

Re: [GENERAL] transaction logging in the form of SQL statements

Use log_min_duration_statement=0 at postgresql.conf file to log every statement. On Tue, Jan 12, 2010 at 7:50 AM, Omar Mehmood wrote: > Is there any way to enable transaction logging in the format of SQL > statements for committed transactions only ? In other words, a way to log > all the SQL

Re: [GENERAL] Transaction started test

Hi Craig, Many thanks for the detailed and quick reply. Must admit although I'd read that every statement was implicitly in a transaction I hadn't connected that through to the operations in any associated triggers. Best regards Larry Anderson Craig Ringer wrote: On 20/12/2009 9:02 PM, Lar

Re: [GENERAL] Transaction started test

On 20/12/2009 9:02 PM, Larry Anderson wrote: Hi All, I'm new to Postgres and need a way to test if a transaction is already in progress. The test will take place inside a trigger function in pl/pgsql and will start a new transaction only if one is not in progress You can't do that, I'm afraid

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

Alvaro Herrera 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. Just to clarify: pl

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

Re: [GENERAL] Transaction Strategies for Natural Primary Keys & Cascading Updates

Richard Broersma wrote: > > For those experienced using natural primary key designs, what update > strategies do you use when updating related tuples within a > transaction when the primary key is also subject to changes? > > For example consider a table is vertically partitioned into two or

Re: [GENERAL] Transaction settings: nowait

correct. Also, if the client is still "alive" they have no effect either. On Fri, May 29, 2009 at 11:01 AM, Y W wrote: > I'm sorry that was my bad, they're only ignored when uing Unix-domain > sockets to connect instead of TCP/IP. > > On Fri, May 29, 2009 at 12:55 PM, Y W wrote: >> >> But accor

Re: [GENERAL] Transaction settings: nowait

On Fri, May 29, 2009 at 10:55 AM, Y W wrote: > But according to the documentation, are they ignored if postgres was hosted > on a Unix/linux system? They work fine on unix, as long as the user has the option to set their own timeouts. If not then set them for the OS (for linux see /etc/sysctl.co

Re: [GENERAL] Transaction settings: nowait

On Fri, May 29, 2009 at 10:18 AM, Y W wrote: > Are you referring to connection settings ? Coz if u do, the closet thing I > can find for the tcp_keepalive_timeout u're mentioning are the > tcp_keepalives_idle, tcp_keepalives_interval, and the tcp_keepalives_count. > And apart from the fact that th

Re: [GENERAL] Transaction settings: nowait

On Thu, May 28, 2009 at 9:52 PM, Yasser Idris wrote: > > Dude, all what u wrote make sense. Only your missing one thing, consider the > following scenario that u already reply to: > >>> For. example: I forget to make commit, or rollback on exception then all >>> resources I used (updated) is locke

Re: [GENERAL] Transaction settings: nowait

Dude, all what u wrote make sense. Only your missing one thing, consider the following scenario that u already reply to: >> For. example: I forget to make commit, or rollback on exception then all >> resources I used (updated) is locked. >Yes - that's an application bug. Even if it's applicati

Re: [GENERAL] Transaction settings: nowait

durumdara wrote: If set wait and timeout, the Firebird is waiting for the locked resource (record) for X seconds before it show deadlock error. But when you set no wait, the deadlock error immediately shown by the server. Waiting on a lock doesn't mean there's a deadlock. A deadlock only o

Re: [GENERAL] Transaction settings: nowait

2009/5/6 Albe Laurenz : > durumdara wrote: >> In FireBird the transactions have these settings: >> >> >> SET TRANSACTION >>    [NAME hostvar] >>    [READ WRITE | READ ONLY] >>    [ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY] >>                          | READ COMMITTED [[NO] RECORD_VERSION] } ]

Re: [GENERAL] Transaction settings: nowait

durumdara wrote: > In FireBird the transactions have these settings: > > > SET TRANSACTION >[NAME hostvar] >[READ WRITE | READ ONLY] >[ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY] > | READ COMMITTED [[NO] RECORD_VERSION] } ] >[WAIT | NO WAIT] >[LOCK

Re: [GENERAL] Transaction settings: nowait

Hi! 2009.05.06. 11:54 keltezéssel, Richard Huxton írta: durumdara wrote: So: have PGSQL same mechanism like nowait? When you take a lock: http://www.postgresql.org/docs/8.3/interactive/sql-lock.html http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE As I se

Re: [GENERAL]transaction isolation level in plpgsql function

whatever calls the function is responsible for transaction level change, because SELECT BLA(); already by default is wrapped by begin;end; - and you can only change transaction level right after BEGIN; although , I feel your pain, it is not possible.

Re: [GENERAL]transaction isolation level in plpgsql function

On Fri, Nov 21, 2008 at 1:19 PM, Sergey Moroz <[EMAIL PROTECTED]> wrote: > Is there any way to set transaction isolation level inside plpgsql > function? In my case I have no control of transaction before function is > started. > > I don't think there can be any. You are already inside a transacti

Re: [GENERAL] transaction logging

On Fri, May 16, 2008 at 1:06 PM, Michael P. Soulier <[EMAIL PROTECTED]> wrote: > I'm interested in enabling transaction logging, so that I can audit those > logs on a system having issues. > > Is there already such a log in postgres 7.4? If not, is there such a feature > and how do I enable it? > >

Re: [GENERAL] Transaction wraparound problem with database postgres

Tom Lane wrote: > "Markus Wollny" <[EMAIL PROTECTED]> writes: >> I'd still like to find out what exactly happened here so I can >> prevent the same from happening again in the future. > > Me too. It would seem that something did a vacuum of postgres with a > strange choice of xid cutoff, but I ca

Re: [GENERAL] Transaction wraparound problem with database postgres

Andreas 'ads' Scherbaum wrote: > Hello, > First of all, it would help you and most of the readers on this list, > if you have the error messages in english. There is a german > mailinglist too, if you want to ask in german. Sorry, I tried to describe the issue as best as I could and included the

Re: [GENERAL] Transaction wraparound problem with database postgres

"Markus Wollny" <[EMAIL PROTECTED]> writes: > Sorry for the quick updates to my own messages, but I didn't want to > lean back and wait - so I took to more aggressive measures. All my > other databases in this cluster are fine - and the 'postgres' database > doesn't seem to do anything really usefu

  1   2   3   >