any way for a transaction to "see" inserts done earlier in the transaction?
Is there any way to let a transaction "see" the inserts that were done
earlier in the transaction? I want to insert a row, 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 possibly avoid it.
Thanks,
Susan
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 a row, 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 possibly avoid 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Susan
On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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:
Show quoted text
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.
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> wrote:
Show quoted text
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
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:
Show quoted text
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.
Not to say that perl and complex are redundant, but does the id go away
after the NOT FOUND exception?
Show quoted text
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.Susan
On Wed, Apr 16, 2014 at 5:05 PM, Susan Cassidy
<susan.cassidy@decisionsciencescorp.com
<mailto: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 <mailto:steven@likeness.com>> wrote:On Wed, Apr 16, 2014 at 4:31 PM, Tom Lane
<tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
Susan Cassidy <susan.cassidy@decisionsciencescorp.com
<mailto: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
<mailto: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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
It seems to be returning the right id. It should be next for the serial
datatype.
Susan
On Wed, Apr 16, 2014 at 5:13 PM, Bosco Rama <postgres@boscorama.com> wrote:
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Thanks,
Susan
On Wed, Apr 16, 2014 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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
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.
Susan
On Wed, Apr 16, 2014 at 5:18 PM, Susan Cassidy <
susan.cassidy@decisionsciencescorp.com> wrote:
Show quoted text
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.
Thanks,
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 Itry
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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
