Re: Query inside transaction
But if someone else runs the same transaction in the same time what value does "currval('sequence')" return? That one of the first transaction or that one of the other transaction? Moreover, field id is a unique primary key, it does not accept duplicates.
Show quoted text
On 25/06/2004 11.58, Michal Táborský <michal@taborsky.cz> wrote:
Ago wrote:
I have a PHP script that runs this query on Postgresql 7.2.3. Once
it inserts the record in the e_catalog table it takes the id value
and then inserts it in the e_catalog_cache table, the two tables
must have the same rows and values.I thought rhat inside a transaction block the subquery SELECT
MAX(id) FROM e_catalog was safe from concurrent same transactions,
that is the id value from SELECT MAX(id) FROM e_catalog was exactly
that one inserted in the previous statement, but reading some threads
in this mailing list I have some doubt now.This is the query:
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'),'$Name', '$Descr');
INSERT INTO e_catalog_cache(id, name, descr) VALUES ((SELECT MAX(id)
FROM e_catalog), '$Name', '$Descr');
COMMIT WORK;
You want:
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'),
'$Name', '$Descr');
INSERT INTO e_catalog_cache(id, name, descr) VALUES currval('sequence'),
'$Name', '$Descr');
COMMIT WORK;--
Michal Taborsky
http://www.taborsky.cz
NMB Webmaster wrote:
But if someone else runs the same transaction in the same time what
value does "currval('sequence')" return? That one of the first
transaction or that one of the other transaction? Moreover, field
id is a unique primary key, it does not accept duplicates.
That's the beauty of sequences. They are transaction-safe. Co
"currval('sequence')" will always return the same value of the previous
nextval call within that transaction, no matter how many other
transactions picked the numbers in between.
To demonstrate:
Transaction 1 Transaction 2
BEGIN; --
nextval('seq') = 1 BEGIN;
do something.. nextval('seq') = 2
do something else... COMMIT;
currval('seq') = 1
COMMIT;
--
Michal Taborsky
http://www.taborsky.cz
OK, thanks Michal, I did not know this issue. I thought I should use LOCK table inside the transaction to pick up the correct id value from SELECT MAX(id) FROM e_catalog.
Show quoted text
On 25/06/2004 12.38, Michal Táborský <michal@taborsky.cz> wrote:
NMB Webmaster wrote:
But if someone else runs the same transaction in the same time
what
value does "currval('sequence')" return? That one of the first
transaction or that one of the other transaction? Moreover, field
id is a unique primary key, it does not accept duplicates.That's the beauty of sequences. They are transaction-safe. Co
"currval('sequence')" will always return the same value of the previousnextval call within that transaction, no matter how many other
transactions picked the numbers in between.To demonstrate:
Transaction 1 Transaction 2
BEGIN; --
nextval('seq') = 1 BEGIN;
do something.. nextval('seq') = 2
do something else... COMMIT;
currval('seq') = 1
COMMIT;--
Michal Taborsky
http://www.taborsky.cz
Import Notes
Resolved by subject fallback
On Fri, Jun 25, 2004 at 12:48:43 +0200,
Ago <ago@nmb.it> wrote:
OK, thanks Michal, I did not know this issue. I thought I should use LOCK table inside the transaction to pick up the correct id value from SELECT MAX(id) FROM e_catalog.
It depends on what you want. Sequences should be used to produce unique
values. If you want to get consecutively numbered rows then they shouldn't
be used.
On 25/06/2004 12.38, Michal T�borsk� <michal@taborsky.cz> wrote:
NMB Webmaster wrote:
But if someone else runs the same transaction in the same time
what
value does "currval('sequence')" return? That one of the first
transaction or that one of the other transaction? Moreover, field
id is a unique primary key, it does not accept duplicates.That's the beauty of sequences. They are transaction-safe. Co
"currval('sequence')" will always return the same value of the previousnextval call within that transaction, no matter how many other
transactions picked the numbers in between.
It is actually a bit stronger promise than that. Currval returns that last
assigned value from the current session, which may span multiple transactions.
Then what have I to do, in your opinion, if I execute this transaction :
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'), '$Name', '$Descr');
INSERT INTO e_catalog_cache(id, name, descr) VALUES ((SELECT MAX(id) FROM e_catalog), '$Name', '$Descr');
COMMIT WORK;
and I want that the second statement takes the same id value of the first one in safe mode, that is, even if someone else insert a new row (and then a new id) in the meantime?
Show quoted text
On 25/06/2004 14.54, Bruno Wolff III <bruno@wolff.to> wrote:
On Fri, Jun 25, 2004 at 12:48:43 +0200,
Ago <ago@nmb.it> wrote:OK, thanks Michal, I did not know this issue. I thought I should
use LOCK table inside the transaction to pick up the correct id value
from SELECT MAX(id) FROM e_catalog.It depends on what you want. Sequences should be used to produce
unique
values. If you want to get consecutively numbered rows then they
shouldn't
be used.On 25/06/2004 12.38, Michal Táborský <michal@taborsky.cz> wrote:
NMB Webmaster wrote:
But if someone else runs the same transaction in the same time
what
value does "currval('sequence')" return? That one of the first
transaction or that one of the other transaction? Moreover,field
id is a unique primary key, it does not accept duplicates.
That's the beauty of sequences. They are transaction-safe. Co
"currval('sequence')" will always return the same value of the
previous
nextval call within that transaction, no matter how many other
transactions picked the numbers in between.
It is actually a bit stronger promise than that. Currval returns
that last
assigned value from the current session, which may span multiple
transactions.
Import Notes
Resolved by subject fallback
On Fri, Jun 25, 2004 at 15:30:55 +0200,
Ago <ago@nmb.it> wrote:
Then what have I to do, in your opinion, if I execute this transaction :
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'), '$Name', '$Descr');INSERT INTO e_catalog_cache(id, name, descr) VALUES ((SELECT MAX(id) FROM e_catalog), '$Name', '$Descr');
COMMIT WORK;
and I want that the second statement takes the same id value of the first one in safe mode, that is, even if someone else insert a new row (and then a new id) in the meantime?
The second insert should use currval. My comment about currval working within
a session said that even if the two inserts weren't in the same transaction,
but were in the same session, you could still use currval. (Note that you
still want them to be in the same transaction for other reasons.)
My other comment was that you shouldn't count on id to be numbered from
1 to however many records you currently have in the table. Under simple
conditions (e.g. no rollbacks, no deleted rows, no grabbing sequence values
in blocks greater than 1) that may be the case, but you shouldn't count on
that.
The jdbc archive( http://archives.postgresql.org/pgsql-jdbc ) appears to
be inaccessible? Has the jdbc list moved?
Import Notes
Resolved by subject fallback
On Fri, 25 Jun 2004, Reid Thompson wrote:
The jdbc archive( http://archives.postgresql.org/pgsql-jdbc ) appears to
be inaccessible? Has the jdbc list moved?
No, the archives for all mailing lists were down this morning. They are
back up now.
Google groups is a good back for searching for things, but not so good at
random browsing.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql
Kris Jurka
This subject has been discussed literally a thousand times before. If
currval didn't work properly with parallel transactions I'm pretty sure
someone would have noticed and fixed it by now.
http://www.postgresql.org/docs/7.4/static/functions-sequence.html
explains all about sequences.
Show quoted text
On Fri, 2004-06-25 at 04:27, NMB Webmaster wrote:
But if someone else runs the same transaction in the same time what value does "currval('sequence')" return? That one of the first transaction or that one of the other transaction? Moreover, field id is a unique primary key, it does not accept duplicates.
On 25/06/2004 11.58, Michal Táborský <michal@taborsky.cz> wrote:
Ago wrote:
I have a PHP script that runs this query on Postgresql 7.2.3. Once
it inserts the record in the e_catalog table it takes the id value
and then inserts it in the e_catalog_cache table, the two tables
must have the same rows and values.I thought rhat inside a transaction block the subquery SELECT
MAX(id) FROM e_catalog was safe from concurrent same transactions,
that is the id value from SELECT MAX(id) FROM e_catalog was exactly
that one inserted in the previous statement, but reading some threads
in this mailing list I have some doubt now.This is the query:
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'),'$Name', '$Descr');
INSERT INTO e_catalog_cache(id, name, descr) VALUES ((SELECT MAX(id)
FROM e_catalog), '$Name', '$Descr');
COMMIT WORK;
You want:
BEGIN WORK;
INSERT INTO e_catalog(id, name, descr) VALUES (nextval('sequence'),
'$Name', '$Descr');
INSERT INTO e_catalog_cache(id, name, descr) VALUES currval('sequence'),
'$Name', '$Descr');
COMMIT WORK;--
Michal Taborsky
http://www.taborsky.cz---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?