Re: Fwd: SQL transactions executing from VMSS

t and the dynatrace results in your response. 3) What is "If i run single sql transaction from pg admin it is executing in same time." trying to say? The same query as you refer to earlier and how does a single query relate to the issue of running transactions in batches? My gu

Re: Fwd: SQL transactions executing from VMSS

Laurenz Albe writes: > I think that the answer you didn't get was as helpful as your problem > description: > "I got some statement that is slow, but I will neither tell you the > statement nor >show you the execution plan. What is the cause of the slowness?" The OP did say that the exec

Re: Fwd: SQL transactions executing from VMSS

On Mon, 2025-04-28 at 13:55 +0530, chandan Kumar wrote: > I did not get any answer till now.  If someone has any idea please help me is > getting this resolved. > The issue is - database deployment , sql query to database is slow when we > run database script from > vmss to postgres database in f

Fwd: SQL transactions executing from VMSS

the same subnet -- Forwarded message - From: chandan Kumar Date: Tue, Apr 22, 2025 at 4:59 PM Subject: SQL transactions executing from VMSS To: Hi Team, Greetings! Kindly help in below situation, where in I see slowness in response time while I do testing running SQL

SQL transactions executing from VMSS

Hi Team, Greetings! Kindly help in below situation, where in I see slowness in response time while I do testing running SQL transaction in batches from VMSS to postgres database in flexible server(PaaS) 14.17 version. VMSS to IaaS(postgres db on ubuntu) is faster than VMSs to PaaS. 14 version is s

Re: PgBackRest : Restore to a checkpoint shows further transactions

On Wed, Sep 25, 2024 at 2:13 AM KK CHN wrote: > PgBackRest : I tried to restore the latest backup taken at my RepoServer > to a testing EPAS server freshly deployed . > ... > Now I comment out the archive command in the test EPAS server > postgresql.conf and started the EPAS server. > * To d

Re: PgBackRest : Restore to a checkpoint shows further transactions

On 9/24/24 23:12, KK CHN wrote: List, PgBackRest :  I tried to restore the latest backup taken at my WHen I issue a query  to select few rows  To my surprise  I am seeing the records with columns with time stamp up to a time 10.36:11:968  and 10:36:13.363 : How did this happen ?   I sp

PgBackRest : Restore to a checkpoint shows further transactions

List, PgBackRest : I tried to restore the latest backup taken at my RepoServer to a testing EPAS server freshly deployed . I have a full backup, two diff and one INCR as on today morning. The latest one is INCR full backup: 20240922-232733F timestamp start/stop: 2024-09-2

Re: Ghost data from failed FDW transactions?

Any updates on this? A few replies from me inline: On Wed, Aug 28, 2024 at 12:18 PM Jacob Biesinger wrote: > There aren't many details in the docs around failure modes... is there > anything there that could cause this issue? > Nothing that I know of, but it's possible there is some sort of we

Re: Ghost data from failed FDW transactions?

> > Any value in supplying a single insert statement a la (less back and forth > perhaps?): > Yes, absolutely that would be better. This particular endpoint has some ancient + crufty code backing it (migrated from a NoSQL DB with a db-agnostic shim that we're slowly replacing). The old code likes

Re: Ghost data from failed FDW transactions?

> On Aug 28, 2024, at 10:18 AM, Jacob Biesinger > wrote: > > But to go deeper, we use the javascript knex adapter and some > application-level transaction management that automatically retries a > transaction N times when it encounters serialization errors. On this > particular endpoint, th

Re: Ghost data from failed FDW transactions?

+ tenant DBs), but connections are held for the duration of both local + remote txns, so doesn't seem like that would affect this. We don't use pgBouncer, either on the client -> DB or as an in-between on the DB -> DB FDW side. > > Through the magic of postgres_fdw, row triggers, an

Re: Ghost data from failed FDW transactions?

ke expected behavior? > No, it sounds like something is going wrong. Your setup as described should work to keep both sides in sync. Through the magic of postgres_fdw, row triggers, and distributed > transactions, > Can you expand on "distributed transactions" here? Cheers, Greg

Ghost data from failed FDW transactions?

Hi there! We have a setup where, for compliance reasons, we hoist a portion of data from several "tenant" databases into a "root" / common / untenanted DB. Through the magic of postgres_fdw, row triggers, and distributed transactions, we automatically hoist the needed columns

Re: RowLock and multiple transactions

On Wed, 2024-02-14 at 23:52 +0100, Hannes Erven wrote: > when "SELECT .. WHERE .. FOR NO KEY UPDATE" is used synchronize access, > and the transaction holding the lock completes, how does PostgreSQL > decide /which one/ of multiple waiting transactions will the lock be

RowLock and multiple transactions

Hi, when "SELECT .. WHERE .. FOR NO KEY UPDATE" is used synchronize access, and the transaction holding the lock completes, how does PostgreSQL decide /which one/ of multiple waiting transactions will the lock be granted to next? In my testing (on Ubuntu 16.1-1.pgdg20.04+1, 64b

Re: Slow down dev database transactions/second for testing?

ch run those to analyze the bottlenecks. Or > let > pgbench create load for some time (see option --time) while you debug your > Django app. > Great - thanks for the suggestion. > > The configuration of postgresql is complicated - is there a simple > method by > >

Re: Slow down dev database transactions/second for testing?

g your Django app. > The configuration of postgresql is complicated - is there a simple method by > which I could, for example limit the number of transactions/second to a > certain level by adjusting postgresql.conf? No. Postgres will execute as fast as possible with the available resources. -- Erik

Slow down dev database transactions/second for testing?

could, for example limit the number of transactions/second to a certain level by adjusting postgresql.conf? Many thanks! -- R. Richard Brockie Real-time bicycle race management - www.ontheday.net

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 19:38, Bhautik Chudasama > wrote: > > Does it mean when we commit transaction, client will wait until all replicas > successfully committed the transaction. It depends. If all the settings are the defaults, no, the client won't wait for the replicas to acknowledge the

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 16:03, Ron wrote: > > Even in asynchronous replication? Yes. Asynchronous replication controls when the client doing the transaction is told that the transaction is complete; it doesn't allow for dirty reads on either the primary or secondary.

Re: PG replicas and transactions atomicity

On 1/5/23 14:09, Christophe Pettus wrote: On Jan 5, 2023, at 12:07, Louis Laborde wrote: Are PG replicas updated atomically following the same transactions boundaries as the source DB ? Yes. The same transactional guarantees apply to the replica as do to the original transactions on the

Re: PG replicas and transactions atomicity

> On Jan 5, 2023, at 12:07, Louis Laborde wrote: > > Are PG replicas updated atomically following the same transactions boundaries > as the source DB ? Yes. The same transactional guarantees apply to the replica as do to the original transactions on the primary.

PG replicas and transactions atomicity

Are PG replicas updated atomically following the same transactions boundaries as the source DB ? For example, if 2 rows from a named table are updated atomically (with a transaction) in the source DB, is there a guarantee that both changes will be done atomically in a replica, or is there a time

Transactions starting with Cursor with Hold are not closing in database.

Hi Team, We are facing an issue with few of the transactions which are running from SAP end. Below is the case statement. Issue -: We are seeing that , the session which are generated from SAP report end to PostgreSQL Database, it starts with the "Open Cursor " Cursor Name &q

Re: Is replacing transactions with CTE a good idea?

On Fri, Apr 9, 2021 at 11:05:34PM +0800, Glen Huang wrote: > This discussion really questioned my understanding of concurrency in > PostgreSQL, thanks a lot. > > I gave the corresponding part of the doc some more read, and I’m now > in the option that insolation level has no effect on CTEs, but ple

Re: Is replacing transactions with CTE a good idea?

This discussion really questioned my understanding of concurrency in PostgreSQL, thanks a lot. I gave the corresponding part of the doc some more read, and I’m now in the option that insolation level has no effect on CTEs, but please correct me if I’m wrong. If notionally all queries execute a

Re: Is replacing transactions with CTE a good idea?

On Mon, Apr 5, 2021 at 02:32:36PM -0400, Dave Cramer wrote: > On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: > I think we are in agreement. My point was that WITH queries don't change the > isolation semantics.  My point is that when you combine individual queries in a single WITH query, thos

Re: Is replacing transactions with CTE a good idea?

On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: > On Sun, Apr 4, 2021 at 10:02:20AM -0400, Dave Cramer wrote: > > On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > > > OK, that makes sense, but I think it is wrong minded to think that > this > > > absolves one of taking isolation in

Re: Is replacing transactions with CTE a good idea?

On Sun, Apr 4, 2021 at 10:02:20AM -0400, Dave Cramer wrote: > On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > > OK, that makes sense, but I think it is wrong minded to think that this > > absolves one of taking isolation into account. > > > > When you make the first read you w

Re: Is replacing transactions with CTE a good idea?

On Sun, 4 Apr 2021 at 09:12, Bruce Momjian wrote: > On Sun, Apr 4, 2021 at 08:35:41AM -0400, Dave Cramer wrote: > > > > > > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > > > > On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > > CTE's don't change the isolation level

Re: Is replacing transactions with CTE a good idea?

On Sun, Apr 4, 2021 at 08:35:41AM -0400, Dave Cramer wrote: > > > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > > On Thu, Apr  1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > CTE's don't change the isolation level. I'm not sure what you are > getting > at > > here ?

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 15:39, Bruce Momjian wrote: > On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > > CTE's don't change the isolation level. I'm not sure what you are > getting at > > here ? > > I think what he/she means here is that all queries in a CTE use a single > snapshot, m

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 10:04 AM, Rob Sargent wrote: On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time a

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote: > CTE's don't change the isolation level. I'm not sure what you are getting at > here ? I think what he/she means here is that all queries in a CTE use a single snapshot, meaning you don't see changes by commits that happen between queri

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 11:06 AM Glen Huang wrote: > Care to expand why they are tricker? I presume they run the risk of being > referenced more than once? > There are lots of gotchas. It's also been a few years since I dug deep into this, so some of this may have changed in more recent versions.

Re: Is replacing transactions with CTE a good idea?

pr 1, 2021, at 11:10 PM, Dave Cramer wrote: >>>> >>>  >>> >>> >>>> On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: >>>> No, but are they equivalent to serializable transactions? >>> >>> No, they are not. >>

Re: Is replacing transactions with CTE a good idea?

09, Glen Huang wrote: >> No, but are they equivalent to serializable transactions? > > No, they are not. > > > > Dave Cramer > www.postgres.rocks >> >>>> On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: >>>> >>>  >>&

Re: Is replacing transactions with CTE a good idea?

able read transaction? > > On Apr 1, 2021, at 11:10 PM, Dave Cramer > wrote: > >  > > > On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: > >> No, but are they equivalent to serializable transactions? >> > > No, they are not. > > > > Dave Cramer &

Re: Is replacing transactions with CTE a good idea?

equivalent to serializable transactions? > > No, they are not. > > > > Dave Cramer > www.postgres.rocks >> >>>> On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: >>>> >>>  >>> >>> >>> >>>> On Thu, 1 Ap

Re: Is replacing transactions with CTE a good idea?

On Thu, 1 Apr 2021 at 11:09, Glen Huang wrote: > No, but are they equivalent to serializable transactions? > No, they are not. Dave Cramer www.postgres.rocks > > On Apr 1, 2021, at 11:04 PM, Dave Cramer > wrote: > >  > > > > On Thu, 1 Apr 2021 at 10:5

Re: Is replacing transactions with CTE a good idea?

No, but are they equivalent to serializable transactions? > On Apr 1, 2021, at 11:04 PM, Dave Cramer wrote: > >  > > > >> On Thu, 1 Apr 2021 at 10:50, Glen Huang wrote: >> Hi all, >> >> From application’s standpoint, it seems using CTE saves a

Re: Is replacing transactions with CTE a good idea?

> When you deal with updates/deletes, things can be trickier Care to expand why they are tricker? I presume they run the risk of being referenced more than once? > On Apr 1, 2021, at 10:58 PM, Brian Dunavant wrote: > >  >> On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: >> If I decide to r

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time and makes code way cleaner.   It's very

Re: Is replacing transactions with CTE a good idea?

alent to serializable transactions? So I > guess the downsize is that quarries can’t be run in parallel? > I do not think a CTE changes the isolation level. > > If I decide to replace all my transaction code with CTE, will I shoot > myself in the foot down the road? > Dave Cramer www.postgres.rocks

Re: Is replacing transactions with CTE a good idea?

On Thu, Apr 1, 2021 at 10:49 AM Glen Huang wrote: > If I decide to replace all my transaction code with CTE, will I shoot > myself in the foot down the road? > I do this all the time and makes code way cleaner. It's very straightforward with inserts queries. When you deal with updates/deletes

Is replacing transactions with CTE a good idea?

Hi all, From application’s standpoint, it seems using CTE saves a lot work. You no longer need to parse values out only to pass them back in, and only one round-trip to the db server. If I’m not wrong, CTE is equivalent to serializable transactions? So I guess the downsize is that quarries

Re: SQL to query running transactions with subtransactions that exceeds 64

he oldest running transaction(1422751) using the following > SQL. > But it showed that there was no transactions that running more than 5 > minutes. > How can I find the oldest running transaction? > > SELECT > pid, > now() - pg_stat_activity.query_start AS durati

RE: SQL to query running transactions with subtransactions that exceeds 64

ransaction(1422751) using the following SQL. But it showed that there was no transactions that running more than 5 minutes. How can I find the oldest running transaction? SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity

Re: SQL to query running transactions with subtransactions that exceeds 64

On Tue, 2021-01-05 at 10:42 +, Li EF Zhang wrote: > I am new to postgresql and sql. I want to check running transactions whose > subtransactions exceeds 64. Is there any SQL statement or other method can > do this? Thanks! You may want to read https://www.cybertec-postgres

SQL to query running transactions with subtransactions that exceeds 64

I am new to postgresql and sql. I want to check running transactions whose subtransactions exceeds 64. Is there any SQL statement or other method can do this? Thanks!

Re: survey: psql syntax errors abort my transactions

On Fri, 2020-07-03 at 12:46 -0500, Ron wrote: > > This is my favorite example why I like the way PostgreSQL does things: > > > > /* poor man's VACUUM (FULL) */ > > BEGIN; > > CREATTE TABLE t2 AS SELECT * FROM t1; > > DROP TABLE t1; > > ALTER TABLE t2 RENAME TO t1; > > COMMIT; > > How so, since it

Re: survey: psql syntax errors abort my transactions

ion environment > where damage could be caused by a different default in a future new > major version of postgresql?  (not aborting transactions in interactive > mode when syntax errors occur) No. Any production scripts would be a single transaction. I think anything else is a disaster waitin

Re: survey: psql syntax errors abort my transactions

On Fri, Jul 3, 2020 at 7:46 PM Ron wrote: > > On 7/3/20 1:54 AM, Laurenz Albe wrote: > > This is my favorite example why I like the way PostgreSQL does things: > > > > /* poor man's VACUUM (FULL) */ > > BEGIN; > > CREATTE TABLE t2 AS SELECT * FROM t1; > > DROP TABLE t1; > > ALTER TABLE t2 RENAME T

Re: survey: psql syntax errors abort my transactions

On 7/3/20 1:54 AM, Laurenz Albe wrote: On Thu, 2020-07-02 at 08:54 -0700, Jeremy Schneider wrote: Maybe it's just me, but I'm wondering if it's worth changing the default behavior of psql so it doesn't abort transactions in interactive mode when I mistakenly mis-spell &

Re: survey: psql syntax errors abort my transactions

On Thu, 2020-07-02 at 08:54 -0700, Jeremy Schneider wrote: > Maybe it's just me, but I'm wondering if it's worth changing the default > behavior > of psql so it doesn't abort transactions in interactive mode when I mistakenly > mis-spell "select" or some

Re: survey: psql syntax errors abort my transactions

On Thu, 2020-07-02 at 09:31 -0700, Adrian Klaver wrote: > I would say just add a message to the ERROR that points out > ON_ERROR_ROLLBACK = 'on' is available. For instance: > > test(5432)=# begin ; > BEGIN > test(5432)=# select 1/0; > ERROR: division by zero > test(5432)=# select 1; > ERROR: cu

Re: survey: psql syntax errors abort my transactions

On 7/2/20 9:44 AM, Jeremy Schneider wrote: On 7/2/20 09:28, David G. Johnston wrote: The status quo prevailed since no-one chose to contribute further arguments for change and the original patch was retracted.  What kind of "bike-shedding" (which seems to be used incorrectly here) would you ex

Re: survey: psql syntax errors abort my transactions

On 7/2/20 9:44 AM, Jeremy Schneider wrote: On 7/2/20 09:28, David G. Johnston wrote: The status quo prevailed since no-one chose to contribute further arguments for change and the original patch was retracted.  What kind of "bike-shedding" (which seems to be used incorrectly here) would you ex

Re: survey: psql syntax errors abort my transactions

On 7/2/20 8:54 AM, Jeremy Schneider wrote: Maybe it's just me, but I'm wondering if it's worth changing the default behavior of psql so it doesn't abort transactions in interactive mode when I mistakenly mis-spell "select" or something silly like that.  This is o

Re: survey: psql syntax errors abort my transactions

On Thu, Jul 2, 2020 at 8:54 AM Jeremy Schneider wrote: > > https://www.postgresql.org/message-id/flat/CABTbUpiAOKZ405uArt8cJFtC72RhzthmvWETQK_6Qw0Ad-HquQ%40mail.gmail.com > > This thread on hackers actually seemed kindof short to me. Not nearly > enough bike-shedding to call it a closed case. >

Re: Monitoring for long running transactions

Samuel Smith schrieb am 04.06.2020 um 21:59: Sorry, I should have clarified that I was aware of the pg_stat_activity table. That is how we found the problem in the first place. And yes I could just write a bash script and run it in cron. I just didn't know if there was a more "official" way to go

Re: Monitoring for long running transactions

## Samuel Smith (pg...@net153.net): > Sorry, I should have clarified that I was aware of the pg_stat_activity > table. That is how we found the problem in the first place. And yes I > could just write a bash script and run it in cron. I just didn't know if > there was a more "official" way to g

Re: Monitoring for long running transactions

On 6/4/20 12:59 PM, Samuel Smith wrote: On 6/4/20 2:29 PM, Adrian Klaver wrote: Sorry, I should have clarified that I was aware of the pg_stat_activity table. That is how we found the problem in the first place. And yes I could just write a bash script and run it in cron. I just didn't k

Re: Monitoring for long running transactions

On 6/4/20 2:29 PM, Adrian Klaver wrote: On 6/4/20 10:00 AM, Samuel Smith wrote: We had a customer complaining of random data loss for the last 6 months or so. We eventually tracked it down to a combination of bad coding and a couple of bugs with the ORM. Basically, 'BEGIN' was being emitted by

Re: Monitoring for long running transactions

On 6/4/20 10:00 AM, Samuel Smith wrote: We had a customer complaining of random data loss for the last 6 months or so. We eventually tracked it down to a combination of bad coding and a couple of bugs with the ORM. Basically, 'BEGIN' was being emitted by the web app when viewing a certain page

Monitoring for long running transactions

We had a customer complaining of random data loss for the last 6 months or so. We eventually tracked it down to a combination of bad coding and a couple of bugs with the ORM. Basically, 'BEGIN' was being emitted by the web app when viewing a certain page and 'COMMIT' was never emitted after tha

AW: Out of memory in big transactions after upgrade to 12.2

not sure this was clear from the stack trace. Von: Michael Lewis [mailto:mle...@entrata.com] Gesendet: Freitag, 3. April 2020 18:30 An: David Day Cc: Pavel Stehule ; Jan Strube ; pgsql-general@lists.postgresql.org Betreff: Re: Out of memory in big transactions after upgrade to 12.2 If you didn&#

Re: Out of memory in big transactions after upgrade to 12.2

If you didn't turn it off, you have parallel workers on by default with v12. If work_mem is set high, memory use may be much higher as each node in a complex plan could end up executing in parallel. Also, do you use a connection pooler such as pgbouncer or pgpool? What is max_connections set to?

RE: Row locks, SKIP LOCKED, and transactions

> (Or you could use serializable mode, but that feels like using a hammer to > swat a fly.) Do you mean the serializable transaction isolation level? Because that doesn't work either. Here (finally) is a tiny repro case. You'll need 2 psql sessions (S1, S2): S1: CREATE TABLE t (id integer): S1

Re: Row locks, SKIP LOCKED, and transactions

Steven Winfield writes: >> There are various ways you could deal with this, but I'd probably go for a >> simple scheme where you only have to consult a single row to know if you >> can claim it. You could still put the results into a separate table, but >> use job.state to find work, and set it t

RE: Row locks, SKIP LOCKED, and transactions

> Huh. SERIALIZABLE shouldn't allow two transactions to see no result row > for a given ID and then insert a result row for that ID. One of those > transactions should have to roll back, because otherwise it'd be > incompatible with both serial orderings of the two transa

RE: Row locks, SKIP LOCKED, and transactions

>https://www.postgresql.org/docs/11/transaction-iso.html#XACT-READ-COMMITTED > >"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ..." > >If I read correctly, transactions can see the effects of other >transactions that commit during their lifetime. Thanks

Re: Row locks, SKIP LOCKED, and transactions

On Wed, Dec 18, 2019 at 5:12 AM Steven Winfield wrote: > * I observe this even if I crank up the transaction isolation level to > repeatable read and serializable. Huh. SERIALIZABLE shouldn't allow two transactions to see no result row for a given ID and then insert a result row

Re: Row locks, SKIP LOCKED, and transactions

T FOR UPDATE, and SELECT FOR SHARE commands ..." If I read correctly, transactions can see the effects of other transactions that commit during their lifetime. Perhaps this is a misuse of the locking system, since I'm locking a row "FOR UPDATE" but not actually updating i

Row locks, SKIP LOCKED, and transactions

Hi all, I'm seeing some unexpected behaviour with SELECT ... FOR UPDATE SKIP LOCKED, and having finding it tricky to boil it down to a simple repro case as there's almost certainly a race condition somewhere (more later). So I thought I would ask if what I'm doing is unsupported (or just plain

Re: Extract transactions from wals ??

Yes ! We are looking for something providing a functionnality similar to Oracle's :-) Through PITR or a tool or extension around wals. Still, as wals are containing enough info for replication to work, It should be possible to extract from wals a list of objects that have been written, and elemen

Re: Extract transactions from wals ??

I think he wants to see data from different tables at different timestamp (like flashback query in Oracle). As per my understanding question here is can PITR be done for specific table and for specific timestamp. On Fri, Nov 22, 2019 at 2:37 PM Laurenz Albe wrote: > On Thu, 2019-11-21 at 17:35 +

Re: Extract transactions from wals ??

On Thu, 2019-11-21 at 17:35 +0100, Marc Millas wrote: > I was writing select from ""table"" as a template. We have to do this for a > bunch of tables. > So, to my understanding, what you suggest is to PITR up to the first > timestamp, > extract all meaningfull tables, and then pitr to the second

Re: Extract transactions from wals ??

> On Nov 21, 2019, at 9:35 AM, Marc Millas wrote: > > Hi Laurenz, > > > I was writing select from ""table"" as a template. We have to do this for a > bunch of tables. > So, to my understanding, what you suggest is to PITR up to the first > timestamp, extract all meaningfull tables, and then

Re: Extract transactions from wals ??

Hi Laurenz, I was writing select from ""table"" as a template. We have to do this for a bunch of tables. So, to my understanding, what you suggest is to PITR up to the first timestamp, extract all meaningfull tables, and then pitr to the second timestamp so as to be able to script a kind of "diff

Re: Extract transactions from wals ??

On Thu, 2019-11-21 at 17:07 +0100, Marc Millas wrote: > you say "extract the data you need" > That is exactly the point of my question, as the PITR step was obvious. > How to guess "what is the data" I need ?? Well, you asked for the contents of a table AS OF TIMESTAMP . That means you know which

Re: Extract transactions from wals ??

; > After that, quite a long set of valuables inserts and updates have been > done and needs to be kept. > > Obviously getting a backup and applying pitr will get us just before the > offending update. > > Now, we need to find a way of extracting, either from the ex prod db, or &

Re: Extract transactions from wals ??

ng a backup and applying pitr will get us just before the > offending update. > Now, we need to find a way of extracting, either from the ex prod db, or from > the wals, the "good" transactions to be able to re-apply them. > > This did already happen on a Prod Oracle D

Extract transactions from wals ??

, we need to find a way of extracting, either from the ex prod db, or from the wals, the "good" transactions to be able to re-apply them. This did already happen on a Prod Oracle DB, and recovering was possible with a : select * from table_name AS OF TIMESTAMP TO_TIMESTAMP('09052019

Re: Recover data from aborted transactions

I don't remember exactly what I read to get this idea. Perhaps it was this particular presentation slide: https://www.slideshare.net/pgdayasia/postgresql-wal-for-dbas/15 The great news is that it must. Some of the helpful folks on IRC introduced me to two different methods of retrieving the inform

Re: Recover data from aborted transactions

On Tue, Sep 10, 2019 at 7:18 PM Aaron Spike wrote: > I'm pretty sure that the records I'm looking for are part of one of these > aborted transactions. From what I read online, it seems that data from > uncommitted transactions exists in the Write-Ahead Logs. Is there anywa

Recover data from aborted transactions

and understand the output of pg_xlogdump, that would be greatly appreciated.) I'm pretty sure that the records I'm looking for are part of one of these aborted transactions. From what I read online, it seems that data from uncommitted transactions exists in the Write-Ahead Logs. Is there anyway to

Re: Transactions

ach...@matrix.gatewaynet.com> wrote: > On 9/4/19 12:26 μ.μ., Karl Martin Skoldebrand wrote: > > Hi, > > > > Is there a way to track “transactions” by default (i.e. without anyone > having set up anything specific). The problem I am facing is that users are > claiming that se

Re: Transactions

On 9/4/19 12:26 μ.μ., Karl Martin Skoldebrand wrote: Hi, Is there a way to track “transactions” by default (i.e. without anyone having set up anything specific). The problem I am facing is that users are claiming that settings are disappearing with them doing anything to affect them. It would

Sv: RE: Transactions

På tirsdag 09. april 2019 kl. 11:56:28, skrev Karl Martin Skoldebrand < ks0c77...@techmahindra.com >: How much impact on performance and disk space would this or set log_min_duration_statement=0 have? I have no idea as to how common this is, or when it happ

RE: Transactions

: 09 April 2019 11:41 To: pgsql-general@lists.postgresql.org Subject: Sv: Transactions På tirsdag 09. april 2019 kl. 11:26:29, skrev Karl Martin Skoldebrand mailto:ks0c77...@techmahindra.com>>: Hi, Is there a way to track “transactions” by default (i.e. without anyone having set up an

Re: Transactions

Hi Karl Martin, you could set log_min_duration_statement=0 at the global level (in the config file) or at session level too. regards, fabio pardi On 09/04/2019 11:26, Karl Martin Skoldebrand wrote: > Hi, > >   > > Is there a way to track “transactions” by default (i.e.

Sv: Transactions

På tirsdag 09. april 2019 kl. 11:26:29, skrev Karl Martin Skoldebrand < ks0c77...@techmahindra.com <mailto:ks0c77...@techmahindra.com>>: Hi, Is there a way to track “transactions” by default (i.e. without anyone having set up anything specific). The problem I am facing is th

Transactions

Hi, Is there a way to track "transactions" by default (i.e. without anyone having set up anything specific). The problem I am facing is that users are claiming that settings are disappearing with them doing anything to affect them. It would be good to be able to see what postgresql

Re: Logical replication - DDL sub transactions for script executed in single transaction?

pá 22. 3. 2019 v 10:49 odesílatel Aleš Zelený napsal: > Hello, > > I've learned that logical replication might have performance problem if > there are lot of sub transactions within transaction (at least because it > enforces spill files in pg_replslot and if there are many

Logical replication - DDL sub transactions for script executed in single transaction?

Hello, I've learned that logical replication might have performance problem if there are lot of sub transactions within transaction (at least because it enforces spill files in pg_replslot and if there are many - like 80mio, EXT4 did not perform well - in my case it was caused bu misu

Re: [External] Re: xmin and very high number of concurrent transactions

Thank you everyone for responding. Appreciate your help. Looks like I need to understand the concepts a little more in detail , to be able to ask the right questions, but atleast now I can look at the relevant docs. On Wed, 13 Mar 2019 at 2:44 PM Julien Rouhaud wrote: > On Wed, Mar 13, 2019 a

Re: xmin and very high number of concurrent transactions

On Wed, Mar 13, 2019 at 9:50 AM Laurenz Albe wrote: > > Vijaykumar Jain wrote: > > I was asked this question in one of my demos, and it was interesting one. > > > > we update xmin for new inserts with the current txid. > > now in a very high concurrent scenario where there are more than 2000 > > c

Re: xmin and very high number of concurrent transactions

s/transam/varsup.c for details. Transaction ID creation is serialized with a "light-weight lock", so it could potentially be a bottleneck. Often that is dwarfed by the I/O requirements from many concurrent commits, but if most of your transactions are rolled back or you use "synchrono

  1   2   >