Обсуждение: 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

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

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

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.

The function does a select to see if the id number exists, and it fails.  NOT FOUND causes a RAISE EXCEPTION.

Susan

On Wed, Apr 16, 2014 at 5:05 PM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote:

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.

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

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.

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

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.

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.

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

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

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.

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.

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

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.

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

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

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

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

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