Greetings,
* Michael Holzman (michaelholz...@gmail.com) wrote:
> I have two applications A and B. A runs SELECT statements only and only on
> tableA. B actively updates tableB, A never looks into tableB. B has nothing
> to do with tableA.
In an ideal world, such distinct applications would probab
On Tue, Sep 8, 2020 at 6:33 PM Magnus Hagander wrote:
>
> Per his session list, 11.2.
>
11.2 is on my personal testing machine. The real tests run with 12.1
Oh, now *cursors* is definitely something I didn't think of. And especially
> in the context of ODBC, I wonder if it might be creating curs
On Tue, Sep 8, 2020 at 6:15 PM Tom Lane wrote:
> Well, we don't know what isolation level the OP is using.
We use the default. We do not explicitly set the isolation level.
We also don't know what PG version he's using.
it's 12.1
>From memory, it hasn't been that long
> since we fixed things
On Tue, Sep 8, 2020 at 5:16 PM Tom Lane wrote:
> Magnus Hagander writes:
> > On Tue, Sep 8, 2020 at 4:38 PM Tom Lane wrote:
> >> The reason that's not so is that whether or not transaction A *has*
> >> touched table B is irrelevant. It *could* read table B at any moment,
> >> for all autovacuu
Magnus Hagander writes:
> On Tue, Sep 8, 2020 at 4:38 PM Tom Lane wrote:
>> The reason that's not so is that whether or not transaction A *has*
>> touched table B is irrelevant. It *could* read table B at any moment,
>> for all autovacuum knows. Therefore we cannot remove rows that should
>> st
On Tue, Sep 8, 2020 at 5:47 PM Magnus Hagander wrote:
>
> I feel there is still some piece of information missing there, that could
> explain the problem better...
>
I gave all the information I have (without real application and table
names, of course).
Both applications are C++ demons working w
On Tue, Sep 8, 2020 at 4:38 PM Tom Lane wrote:
> Magnus Hagander writes:
> > Oh sure, but there is clearly *something* going on, so we should try to
> > figure that out. Because a transaction running multiple independent
> selects
> > with the defaults settings will not actually block autovacuum
On Tue, Sep 8, 2020 at 5:38 PM Tom Lane wrote:
> Magnus Haganderwrites:
> > Oh sure, but there is clearly *something* going on, so we should try to
> > figure that out. Because a transaction running multiple independent
> selects
> > with the defaults settings will not actually block autovacuum.
>
On 9/8/20 3:27 AM, Michael Paquier wrote:
On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote:
Autovacuum does not clean dead tuples of closed transactions in tableB
while there is an open transaction on tableA.
But the tables have nothing in common. They are handled by separate
a
Magnus Hagander writes:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.
I don't think the OP is claiming that autovacuum is bloc
On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman
wrote:
>
>
> On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:
>
>>
>>
>> Whether you have autocommit on or off, you can *always* control things
>> explicitly. And you can certainly run "multi-statement transactions" in
>> autocommit on -- in fac
On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:
>
>
> Whether you have autocommit on or off, you can *always* control things
> explicitly. And you can certainly run "multi-statement transactions" in
> autocommit on -- in fact, it's what most people do since it's the default
> configuration o
(Please don't drop the mailinglist from CC, as others are likely interested
in the responses)
On Tue, Sep 8, 2020 at 3:06 PM Michael Holzman
wrote:
>
>
> On Tue, Sep 8, 2020 at 3:03 PM Magnus Hagander wrote:
>
>> A PostgreSQL SELECT does *not* open a transaction past the end of the
>> statement,
This is assuming other sessions change the same block your session is trying to
read.
===
It's been a while since I worked with Oracle as a developer. But my
understanding
is that even a read-only transaction, like the one you described above, requires
a point in time consistent image of th
>>Interesting. Are you telling the Oracle version of the code had no
>>intermittent COMMIT and relied on one final COMMIT at the end. Even
>>in Oracle developers must have planned for commit since a long running
>>open transaction can lead to “snapshot too old” error.
>Yes, I am saying just
On Tue, Sep 8, 2020 at 1:52 PM Ravi Krishna wrote:
>
>
> Interesting. Are you telling the Oracle version of the code had no
> intermittent COMMIT and relied on one
> final COMMIT at the end. Even in Oracle developers must have planned for
> commit since a long running
> open transaction can lead t
This is the problem. A and B were developed for Oracle where SELECT does not
open a transaction. We moved them to PG
and now we have to very accurately add COMMITs without breaking the flow. It is
quite a complex thing. I hoped we can
avoid that.
Interesting. Are you telling the Oracle vers
On Tue, Sep 8, 2020 at 12:28 PM Pavel Stehule wrote:
>
> You can try to reduce length of transactions, if possible.
>
>
> This is the problem. A and B were developed for Oracle where SELECT does
not open a transaction. We moved them to PG and now we have to very
accurately add COMMITs without brea
út 8. 9. 2020 v 10:42 odesílatel Michael Holzman
napsal:
>
>
> On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote:
>
>>
>> This is called MVCC, which applies to a session as a whole. The point
>> here is that even if your application knows that only tableA is used
>> by a given transaction, P
On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote:
>
> This is called MVCC, which applies to a session as a whole. The point
> here is that even if your application knows that only tableA is used
> by a given transaction, Postgres cannot know that, as it could be
> possible that data from tab
On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote:
> Autovacuum does not clean dead tuples of closed transactions in tableB
> while there is an open transaction on tableA.
> But the tables have nothing in common. They are handled by separate
> applications and there are no transaction
On Tue, Sep 8, 2020 at 10:46 AM Pavel Stehule wrote:
>
> autovacuum does cleaning of changes related to finished transactions. It
> does nothing if possible dead tuples are assigned to open transactions.
>
This is the point.
Autovacuum does not clean dead tuples of closed transactions in tableB
w
Hi
út 8. 9. 2020 v 9:32 odesílatel Michael Holzman
napsal:
> Hi,
>
> I have two applications A and B. A runs SELECT statements only and only on
> tableA. B actively updates tableB, A never looks into tableB. B has nothing
> to do with tableA.
>
> Still, if A is inside a long running transaction,
23 matches
Mail list logo
