Обсуждение: any way for a transaction to "see" inserts done earlier in the transaction?
Thanks,
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On Apr 16, 2014, at 4:27 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > Is there any way to let a transaction "see" the inserts that were done earlier in the transaction? I want to insert arow, then later use it within the same transaction. > > If not, I will have to commit after each insert, and I don't want to do that until add the rows are added, if I can possiblyavoid it. Did you try it? This is already how it works, unless I misunderstand your question… postgres=# create temporary table foo (i integer primary key); CREATE TABLE postgres=# begin; BEGIN postgres=# insert into foo values(1); INSERT 0 1 postgres=# select * from foo; i --- 1 (1 row) postgres=# commit; COMMIT
Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes: > Is there any way to let a transaction "see" the inserts that were done > earlier in the transaction? It works that way automatically, as long as you're talking about separate statements within one transaction. regards, tom lane
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
> Is there any way to let a transaction "see" the inserts that were done
> earlier in the transaction?
It works that way automatically, as long as you're talking about separate
statements within one transaction.
regards, tom lane
Re: any way for a transaction to "see" inserts done earlier in the transaction?
>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes: >> > Is there any way to let a transaction "see" the inserts that were done >> > earlier in the transaction? >> >> It works that way automatically, as long as you're talking about separate >> statements within one transaction. >> >> regards, tom lane > On Apr 16, 2014, at 4:53 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a newprimary key, and when I SELECT it, it isn't found. > Can you share the code that does not work with us? Preferably as a small self-contained example.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 4/16/2014 4:53 PM, Susan Cassidy wrote: > Well, it isn't working for me right now. It can't "see" a row that > was inserted earlier in the transaction. It is a new primary key, and > when I SELECT it, it isn't found. are you using the same connection ? it won't be visible to a different connection until its committed. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: any way for a transaction to "see" inserts done earlier in the transaction?
>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
>> > Is there any way to let a transaction "see" the inserts that were done
>> > earlier in the transaction?
>>
>> It works that way automatically, as long as you're talking about separate
>> statements within one transaction.
>>
>> regards, tom lane
> On Apr 16, 2014, at 4:53 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
> Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
>
Can you share the code that does not work with us? Preferably as a small self-contained example.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 4/16/2014 4:53 PM, Susan Cassidy wrote:Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
are you using the same connection ? it won't be visible to a different connection until its committed.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: any way for a transaction to "see" inserts done earlier in the transaction?
SusanCould it be because the insert is done inside a function?It is a fairly large and complex Perl program, so no, not really.I do an insert via a function, which returns the new id, then later I try to SELECT on that id, and it doesn't find it.On Wed, Apr 16, 2014 at 4:58 PM, Steven Schlansker <steven@likeness.com> wrote:
>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
>> > Is there any way to let a transaction "see" the inserts that were done
>> > earlier in the transaction?
>>
>> It works that way automatically, as long as you're talking about separate
>> statements within one transaction.
>>
>> regards, tom lane
> On Apr 16, 2014, at 4:53 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
> Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
>
Can you share the code that does not work with us? Preferably as a small self-contained example.
On 04/16/14 17:08, Susan Cassidy wrote: > The function does a select to see if the id number exists, and it fails. > NOT FOUND causes a RAISE EXCEPTION. Is it returning the right id? I seem to remember a recent thread about Perl DBI returning the wrong id's for certain operations. Just at thought. Bosco.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 04/16/2014 06:08 PM, Susan Cassidy wrote:
The function does a select to see if the id number exists, and it fails. NOT FOUND causes a RAISE EXCEPTION.SusanOn Wed, Apr 16, 2014 at 5:05 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:SusanCould it be because the insert is done inside a function?It is a fairly large and complex Perl program, so no, not really.I do an insert via a function, which returns the new id, then later I try to SELECT on that id, and it doesn't find it.On Wed, Apr 16, 2014 at 4:58 PM, Steven Schlansker <steven@likeness.com> wrote:
>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
>> > Is there any way to let a transaction "see" the inserts that were done
>> > earlier in the transaction?
>>
>> It works that way automatically, as long as you're talking about separate
>> statements within one transaction.
>>
>> regards, tom lane
> On Apr 16, 2014, at 4:53 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
> Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
>
Can you share the code that does not work with us? Preferably as a small self-contained example.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
On 04/16/14 17:08, Susan Cassidy wrote:
> The function does a select to see if the id number exists, and it fails.
> NOT FOUND causes a RAISE EXCEPTION.
Is it returning the right id? I seem to remember a recent thread
about Perl DBI returning the wrong id's for certain operations.
Just at thought.
Bosco.
Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes: > It is a fairly large and complex Perl program, so no, not really. > I do an insert via a function, which returns the new id, then later I try > to SELECT on that id, and it doesn't find it. > Could it be because the insert is done inside a function? Is the SELECT also inside a database function, and if so is that function marked stable or immutable? That might explain it --- non-volatile functions are intentionally designed not to notice updates that happen after they start. regards, tom lane
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
> It is a fairly large and complex Perl program, so no, not really.
> I do an insert via a function, which returns the new id, then later I try
> to SELECT on that id, and it doesn't find it.
> Could it be because the insert is done inside a function?
Is the SELECT also inside a database function, and if so is that function
marked stable or immutable? That might explain it --- non-volatile
functions are intentionally designed not to notice updates that happen
after they start.
regards, tom lane
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Thanks,It isn't marked as one of those as all, so whatever the default is.That could be it. I'll look up the default.
SusanOn Wed, Apr 16, 2014 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
> It is a fairly large and complex Perl program, so no, not really.
> I do an insert via a function, which returns the new id, then later I try
> to SELECT on that id, and it doesn't find it.
> Could it be because the insert is done inside a function?
Is the SELECT also inside a database function, and if so is that function
marked stable or immutable? That might explain it --- non-volatile
functions are intentionally designed not to notice updates that happen
after they start.
regards, tom lane
Re: any way for a transaction to "see" inserts done earlier in the transaction?
One possibility is that the INSERT is going to a different table (having the same name but existing in a different schema) that is visible/default to the function but not outside of it. Or the function on the server is not "current" and thus isn't doing what you think it is. > I do an insert via a function, which returns the new id, then later I try > to SELECT on that id, and it doesn't find it. > > Could it be because the insert is done inside a function? Not by itself; but that factor could be interacting with something else to cause the observed behavior. As noted above functions are able to maintain their own "schema" environment so what is executed in one and outside of one can indeed target different physical objects - which has nothing to do with transaction visibility. Susan Cassidy-3 wrote > It is a fairly large and complex Perl program, so no, not really. Then you need to recreate a functionally similar, but limited, test case that either exhibits the behavior in question or causes you to realize what you are doing in wrong in the "large and complex Perl program". David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 04/16/2014 05:24 PM, Susan Cassidy wrote: > I marked it volatile, and still the next time I call the function after > the first insert, using the previous new id as as input parameter, it > still can't "find" the newly inserted id for the next go-round. Nor can > any regular SELECTs in the main program find it. A suggestion would be to tail -f the postgres log file and see if there is more than one connection happening when you run the program. This assumes the database activity is quiet enough to distinguish connections. > > Susan > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
One possibility is that the INSERT is going to a different table (having the
same name but existing in a different schema) that is visible/default to the
function but not outside of it.
Or the function on the server is not "current" and thus isn't doing what you
think it is.Not by itself; but that factor could be interacting with something else to
> I do an insert via a function, which returns the new id, then later I try
> to SELECT on that id, and it doesn't find it.
>
> Could it be because the insert is done inside a function?
cause the observed behavior. As noted above functions are able to maintain
their own "schema" environment so what is executed in one and outside of one
can indeed target different physical objects - which has nothing to do with
transaction visibility.
Susan Cassidy-3 wrote> It is a fairly large and complex Perl program, so no, not really.Then you need to recreate a functionally similar, but limited, test case
that either exhibits the behavior in question or causes you to realize what
you are doing in wrong in the "large and complex Perl program".
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan Cassidy-3 wrote > Nor can any regular SELECTs in the main program find it. Ever? If this is a same transaction visibility issue then when your Perl program stops you should be able to go find that ID manually to confirm it was inserted and committed properly. If you still cannot find the ID then this whole line of exploration (i.e., same session visibility) is pointless since we know beyond doubt committed data is visible to all other sessions. This would also further support the mistaken object identity theory I proposed up-thread. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800463.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
I'm presuming the OP is using the typical model of: conn = getConnection() id = doInsert(conn) rst = doSelect(conn, id) doSomething(rst) conn.commit() conn.relrease() Robert DiFalco wrote > Two common cases I can think of: > > 1. The PERL framework is only caching the insert and does not actually > perform it until commit is issued. Wouldn't the same mechanism cache the corresponding SELECT? > 2. You really are not on the same transaction even though it appears you > are and the transaction isolation is such that you cannot see the insert > until it is fully committed. Doubtful given the way most programs are coded (see assumption above) - the SELECT should be able to see the prior statement results whether committed or not. The only thing I can think of on this line-of-though is that auto-commit is off and while the original INSERT succeeded the transaction it was in was not "COMMIT"ed and the connection used closed/returned-to-pool with an implicit ROLLBACK. Thus when the subsequent SELECT occurred the INSERT never appeared to happen. Not knowing the whether the ID is visible post-program-completion limits the ability to diagnose, though. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800466.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 04/16/2014 07:06 PM, Susan Cassidy wrote: > Yes, it is the same connection. It is all the same transaction. > > Susan > > > On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>> wrote: > > On 4/16/2014 4:53 PM, Susan Cassidy wrote: > > Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. Itis a new primary key, and when I SELECT it, it isn't found. > > > are you using the same connection ? it won't be visible to a different connection until its committed. > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > Any chance you accidentally fired of a rollback? -Andy
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 04/16/2014 05:24 PM, Susan Cassidy wrote: > I marked it volatile, and still the next time I call the function after > the first insert, using the previous new id as as input parameter, it > still can't "find" the newly inserted id for the next go-round. Nor can > any regular SELECTs in the main program find it. To expand on my earlier post and to address all the different scenarios proposed, it might to be a good idea to expand on what you are logging. The caveat is whether you are working against a production server or a development. In the development case you would presumably be able to more easily limit the scope of what you are observing. In that case turning up the log_statement to 'all' in postgresql.conf would give you a picture of what is actually hitting the database. So you could see if there where multiple connections happening or multiple transactions in a connection or if different tables where being used, etc. That would help create some anchor points from which you could backwards engineer to possible causes. > > Susan > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com> wrote: > Robert DiFalco wrote >> Two common cases I can think of: >> >> 1. The PERL framework is only caching the insert and does not actually >> perform it until commit is issued. > > Wouldn't the same mechanism cache the corresponding SELECT? Not likely, or if it did it wouldn’t be able to know what id was returned from the function (which calls nextval(), but thatisn’t relevant here since it’s marked volatile). That makes it a possible scenario for what’s being witnessed here. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
Two common cases I can think of:1. The PERL framework is only caching the insert and does not actually perform it until commit is issued.2. You really are not on the same transaction even though it appears you are and the transaction isolation is such that you cannot see the insert until it is fully committed.On Wed, Apr 16, 2014 at 5:28 PM, David G Johnston <david.g.johnston@gmail.com> wrote:One possibility is that the INSERT is going to a different table (having the
same name but existing in a different schema) that is visible/default to the
function but not outside of it.
Or the function on the server is not "current" and thus isn't doing what you
think it is.Not by itself; but that factor could be interacting with something else to
> I do an insert via a function, which returns the new id, then later I try
> to SELECT on that id, and it doesn't find it.
>
> Could it be because the insert is done inside a function?
cause the observed behavior. As noted above functions are able to maintain
their own "schema" environment so what is executed in one and outside of one
can indeed target different physical objects - which has nothing to do with
transaction visibility.
Susan Cassidy-3 wrote> It is a fairly large and complex Perl program, so no, not really.Then you need to recreate a functionally similar, but limited, test case
that either exhibits the behavior in question or causes you to realize what
you are doing in wrong in the "large and complex Perl program".
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan Cassidy-3 wrote
> Nor can any regular SELECTs in the main program find it.
Ever?
If this is a same transaction visibility issue then when your Perl program
stops you should be able to go find that ID manually to confirm it was
inserted and committed properly. If you still cannot find the ID then this
whole line of exploration (i.e., same session visibility) is pointless since
we know beyond doubt committed data is visible to all other sessions.
This would also further support the mistaken object identity theory I
proposed up-thread.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800463.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
begin transaction
Do some selects, including id on second iteration of the inserted id
I'm presuming the OP is using the typical model of:
conn = getConnection()
id = doInsert(conn)
rst = doSelect(conn, id)
doSomething(rst)
conn.commit()
conn.relrease()
Robert DiFalco wrote
> Two common cases I can think of:
>
> 1. The PERL framework is only caching the insert and does not actually
> perform it until commit is issued.
Wouldn't the same mechanism cache the corresponding SELECT?
> 2. You really are not on the same transaction even though it appears you
> are and the transaction isolation is such that you cannot see the insert
> until it is fully committed.
Doubtful given the way most programs are coded (see assumption above) - the
SELECT should be able to see the prior statement results whether committed
or not.
The only thing I can think of on this line-of-though is that auto-commit is
off and while the original INSERT succeeded the transaction it was in was
not "COMMIT"ed and the connection used closed/returned-to-pool with an
implicit ROLLBACK. Thus when the subsequent SELECT occurred the INSERT
never appeared to happen.
Not knowing the whether the ID is visible post-program-completion limits the
ability to diagnose, though.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800466.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
On 04/16/2014 07:06 PM, Susan Cassidy wrote:Yes, it is the same connection. It is all the same transaction.
Susan
On Wed, Apr 16, 2014 at 5:00 PM, John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>> wrote:
On 4/16/2014 4:53 PM, Susan Cassidy wrote:
Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
are you using the same connection ? it won't be visible to a different connection until its committed.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
Any chance you accidentally fired of a rollback?
-Andy
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com> wrote:
> Robert DiFalco wrote
>> Two common cases I can think of:
>>
>> 1. The PERL framework is only caching the insert and does not actually
>> perform it until commit is issued.
>
> Wouldn't the same mechanism cache the corresponding SELECT?
Not likely, or if it did it wouldn’t be able to know what id was returned from the function (which calls nextval(), but that isn’t relevant here since it’s marked volatile).
That makes it a possible scenario for what’s being witnessed here.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: any way for a transaction to "see" inserts done earlier in the transaction?
On 04/16/2014 05:24 PM, Susan Cassidy wrote:I marked it volatile, and still the next time I call the function after
the first insert, using the previous new id as as input parameter, it
still can't "find" the newly inserted id for the next go-round. Nor can
any regular SELECTs in the main program find it.
To expand on my earlier post and to address all the different scenarios proposed, it might to be a good idea to expand on what you are logging. The caveat is whether you are working against a production server or a development. In the development case you would presumably be able to more easily limit the scope of what you are observing. In that case turning up the log_statement to 'all' in postgresql.conf would give you a picture of what is actually hitting the database. So you could see if there where multiple connections happening or multiple transactions in a connection or if different tables where being used, etc. That would help create some anchor points from which you could backwards engineer to possible causes.
Susan
--
Adrian Klaver
adrian.klaver@aklaver.com
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com> wrote:
> Robert DiFalco wrote
>> Two common cases I can think of:
>>
>> 1. The PERL framework is only caching the insert and does not actually
>> perform it until commit is issued.
>
> Wouldn't the same mechanism cache the corresponding SELECT?
Not likely, or if it did it wouldn’t be able to know what id was returned from the function (which calls nextval(), but that isn’t relevant here since it’s marked volatile).
That makes it a possible scenario for what’s being witnessed here.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
So any chance of a self-contained test case so we're not all chasing our tails? On Thu, Apr 17, 2014 at 9:06 AM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > Except for the fact that I get the new id returned from the first insert, > which means that the insert probably did happen. > > Susan > > > On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys <haramrae@gmail.com> wrote: >> >> On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com> >> wrote: >> >> > Robert DiFalco wrote >> >> Two common cases I can think of: >> >> >> >> 1. The PERL framework is only caching the insert and does not actually >> >> perform it until commit is issued. >> > >> > Wouldn't the same mechanism cache the corresponding SELECT? >> >> Not likely, or if it did it wouldn’t be able to know what id was returned >> from the function (which calls nextval(), but that isn’t relevant here since >> it’s marked volatile). >> That makes it a possible scenario for what’s being witnessed here. >> >> Alban Hertroys >> -- >> If you can't see the forest for the trees, >> cut the trees and you'll find there is no forest. >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- To understand recursion, one must first understand recursion.
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Sent from my iPhone
Except for the fact that I get the new id returned from the first insert, which means that the insert probably did happen.
SusanOn Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys <haramrae@gmail.com> wrote:On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com> wrote:
> Robert DiFalco wrote
>> Two common cases I can think of:
>>
>> 1. The PERL framework is only caching the insert and does not actually
>> perform it until commit is issued.
>
> Wouldn't the same mechanism cache the corresponding SELECT?
Not likely, or if it did it wouldn’t be able to know what id was returned from the function (which calls nextval(), but that isn’t relevant here since it’s marked volatile).
That makes it a possible scenario for what’s being witnessed here.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Please note that everyone here but you is bottom-posting; please follow the convention and list standard. Susan Cassidy-3 wrote > It is never committed, because the lookup for the insert fails. So, alter the code so only the first insert happens then stop further processing and go explore that state of the database. Or, add a "commit" after the initial insert so at least that is memorialized for future research even if the rest of the code fails. You are going to have to do some leg-work here; a pure thought experiment is not going to cut it. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800567.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: any way for a transaction to "see" inserts done earlier in the transaction?
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Bosco Rama wrote: > Is it returning the right id? I seem to remember a recent thread > about Perl DBI returning the wrong id's for certain operations. Er...can you point me to that thread, please? I'd be very interested in such a bug. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201404171231 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlNQAe8ACgkQvJuQZxSWSshObwCglozBhwT4ddf7XacITKlucaou 7iEAoOXyK+SvYn5dBiBnrLyAkmzvDyB+ =SrCC -----END PGP SIGNATURE-----
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
So any chance of a self-contained test case so we're not all chasing our tails?
On Thu, Apr 17, 2014 at 9:06 AM, Susan Cassidy
<susan.cassidy@decisionsciencescorp.com> wrote:
> Except for the fact that I get the new id returned from the first insert,
> which means that the insert probably did happen.
>
> Susan
>
>
> On Wed, Apr 16, 2014 at 11:55 PM, Alban Hertroys <haramrae@gmail.com> wrote:
>>
>> On 17 Apr 2014, at 2:49, David G Johnston <david.g.johnston@gmail.com>
>> wrote:
>>
>> > Robert DiFalco wrote
>> >> Two common cases I can think of:
>> >>
>> >> 1. The PERL framework is only caching the insert and does not actually
>> >> perform it until commit is issued.
>> >
>> > Wouldn't the same mechanism cache the corresponding SELECT?
>>
>> Not likely, or if it did it wouldn’t be able to know what id was returned
>> from the function (which calls nextval(), but that isn’t relevant here since
>> it’s marked volatile).
>> That makes it a possible scenario for what’s being witnessed here.
>>
>> Alban Hertroys
>> --
>> If you can't see the forest for the trees,
>> cut the trees and you'll find there is no forest.
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
To understand recursion, one must first understand recursion.
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
On Thu, 17 Apr 2014 10:02:00 -0700 Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > I moved the code in the function inline into the code, and I still cannot > find the newly inserted id the next time through the loop. I suppose you use DBD::Pg, whose current default isolation transaction level is ``Serializable'' Don't know if it applies, but the Postgresql's documentation says this : SERIALIZABLE All statements of the current transaction can only see rows committed before the first query or data-modification statementwas executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions wouldcreate a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, oneof them will be rolled back with a serialization_failure SQLSTATE. -- Regards, Vincent Veyron http://libremen.com/ Legal case, contract and insurance claim management software
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
Susan
On Thu, 17 Apr 2014 10:02:00 -0700
Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
> I moved the code in the function inline into the code, and I still cannot
> find the newly inserted id the next time through the loop.
I suppose you use DBD::Pg, whose current default isolation transaction level is ``Serializable''
Don't know if it applies, but the Postgresql's documentation says this :
SERIALIZABLE
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure SQLSTATE.
--
Regards, Vincent Veyron
http://libremen.com/
Legal case, contract and insurance claim management software
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
On Thu, 17 Apr 2014 10:02:00 -0700 Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > I moved the code in the function inline into the code, and I still cannot > find the newly inserted id the next time through the loop. I suppose you use DBD::Pg, whose current default isolation transaction level is ``Serializable'' Don't know if it applies, but the Postgresql's documentation says this : SERIALIZABLE All statements of the current transaction can only see rows committed before the first query or data-modification statementwas executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions wouldcreate a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, oneof them will be rolled back with a serialization_failure SQLSTATE. -- Regards, Vincent Veyron http://libremen.com/ Legal case, contract and insurance claim management software
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
My apologies to all, I posted with the wrong id; reposting -- Regards, Vincent Veyron http://libremen.com/ Legal case, contract and insurance claim management software
Re: Re: any way for a transaction to "see" inserts done earlier in the transaction?
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Vincent Veyron wrote: > I suppose you use DBD::Pg, whose current default isolation > transaction level is ``Serializable'' Just to set the record straight for the archives, DBD::Pg makes no changes at all to the isolation level. The only way to change your level when using DBD::Pg would be to issue a SQL command: $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'); (The *default* transaction isolation level can be changed in your postgresql.conf, and it is set to 'read committed' when the postgresql.conf is created via initdb.) - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201404181453 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlNRdVkACgkQvJuQZxSWSsiuRwCg1Xi3r8eS1bdKmkfpRvQiKY2j yEwAoOwf8aeQqS9xczgKyKkUbgqJq9aV =rjAZ -----END PGP SIGNATURE-----
Re: any way for a transaction to "see" inserts done earlier in the transaction?
Was the function doing INSERT operation was successful? I suspect may be INSERT function has failed and hence INSERT got ABORTED.
Also you can try to commit whole transaction and see if you are able to find the new id (This will prove that whether really new id was inserted or not).
Thanks and Regards,
Kumar Rajeev Rastogi
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Susan Cassidy
Sent: 17 April 2014 05:36
To: Steven Schlansker
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] any way for a transaction to "see" inserts done earlier in the transaction?
It is a fairly large and complex Perl program, so no, not really.
I do an insert via a function, which returns the new id, then later I try to SELECT on that id, and it doesn't find it.
Could it be because the insert is done inside a function?
Susan
On Wed, Apr 16, 2014 at 4:58 PM, Steven Schlansker <steven@likeness.com> wrote:
>> On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Susan Cassidy <susan.cassidy@decisionsciencescorp.com> writes:
>> > Is there any way to let a transaction "see" the inserts that were done
>> > earlier in the transaction?
>>
>> It works that way automatically, as long as you're talking about separate
>> statements within one transaction.
>>
>> regards, tom lane
> On Apr 16, 2014, at 4:53 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:
> Well, it isn't working for me right now. It can't "see" a row that was inserted earlier in the transaction. It is a new primary key, and when I SELECT it, it isn't found.
>
Can you share the code that does not work with us? Preferably as a small self-contained example.