pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedundefine currval()
7+ messages / 5 participants
[nested] [flat]
* undefine currval()
@ 2003-09-08 21:12 Chris Gamache <cgg007@yahoo.com>
0 siblings, 3 replies; 7+ messages in thread
From: Chris Gamache @ 2003-09-08 21:12 UTC (permalink / raw)
To: pgsql-sql
I'm using sequences and currval() to retrieve the last inserted row in a table.
If currval() is undefined, as it is when a connection is made, then I know no
rows were inserted in that table and can take a different action. This is
problematic when using a connection pooling library, as the value of currval()
for any given sequence could possibly be set from a previous "connection".
One (theoretical) workaround would be to issue some sort of command to the
back-end database to wipe all values of currval() when a "new" connection is
made. I've done some digging in the system tables and source code, and can't
find an obvious solution. Perhaps one you you gurus can suggest a SQL statement
to do such a thing.
Alternately, if there is a better way to retrieve the last inserted row for any
given table, I'd be very grateful for the tip. It would need to be independent
of the connection history, and undefined if there has not been a row inserted
to the table during a definable interval of time (drop anchor when the
"connection" begins, raise anchor when the "connection" ends), and be
independant of the other connections inserting rows to the same table.
Any idaeas?
CG
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-08 21:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-09-08 21:33 UTC (permalink / raw)
To: cgg007@yahoo.com; +Cc: pgsql-sql
Chris Gamache <cgg007@yahoo.com> writes:
> One (theoretical) workaround would be to issue some sort of command to the
> back-end database to wipe all values of currval() when a "new" connection is
> made. I've done some digging in the system tables and source code, and can't
> find an obvious solution.
The state involved is in a linked list kept by commands/sequence.c.
Such a command would not be difficult to implement, if you could get
agreement on the syntax to use.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-08 22:41 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2003-09-08 22:41 UTC (permalink / raw)
To: cgg007@yahoo.com; +Cc: pgsql-sql
Chris Gamache wrote:
> I'm using sequences and currval() to retrieve the last inserted row in a table.
>
>
> If currval() is undefined, as it is when a connection is made, then I know no
> rows were inserted in that table and can take a different action. This is
> problematic when using a connection pooling library, as the value of currval()
> for any given sequence could possibly be set from a previous "connection".
>
> One (theoretical) workaround would be to issue some sort of command to the
> back-end database to wipe all values of currval() when a "new" connection is
> made. I've done some digging in the system tables and source code, and can't
> find an obvious solution. Perhaps one you you gurus can suggest a SQL statement
> to do such a thing.
>
> Alternately, if there is a better way to retrieve the last inserted row for any
> given table, I'd be very grateful for the tip. It would need to be independent
> of the connection history, and undefined if there has not been a row inserted
> to the table during a definable interval of time (drop anchor when the
> "connection" begins, raise anchor when the "connection" ends), and be
> independant of the other connections inserting rows to the same table.
I don't know how you could have an application that doesn't know if it
has issued a nextval() in the current connection. Unless you can explain
that, we have no intention of playing tricks with currval() for
connection pooling.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-08 23:03 scott.marlowe <scott.marlowe@ihs.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: scott.marlowe @ 2003-09-08 23:03 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: cgg007@yahoo.com; pgsql-sql
On Mon, 8 Sep 2003, Bruce Momjian wrote:
> I don't know how you could have an application that doesn't know if it
> has issued a nextval() in the current connection. Unless you can explain
> that, we have no intention of playing tricks with currval() for
> connection pooling.
Actually, I would think the very act of using connection pooling would
ensure that applications may well not know whether or not a nextval had
been called. In other words, how is an application supposed to know if
the previous bit of code that used this connection issued a nextval() when
you're connection pooling and any piece of code could have run before you.
On the other hand, using currval as a test to see if a value has been used
is probably not the best way of doing things either. I'd imagine some
kind of static or session var would be better suited to that task.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-08 23:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-09-08 23:35 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; cgg007@yahoo.com; pgsql-sql
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Mon, 8 Sep 2003, Bruce Momjian wrote:
>> I don't know how you could have an application that doesn't know if it
>> has issued a nextval() in the current connection. Unless you can explain
>> that, we have no intention of playing tricks with currval() for
>> connection pooling.
> Actually, I would think the very act of using connection pooling would
> ensure that applications may well not know whether or not a nextval had
> been called.
The point is that it's not very sensible to be using currval except
immediately after a nextval --- usually in the same transaction, I would
think. Certainly, not resetting currval implies that there is
*potential* coupling between different transactions that happen to share
a connection. But ISTM that such coupling would represent a bug in the
application.
Chris said he was using currval being undefined to know that no rows
were inserted, but this seems less than compelling to me (why not look
at the results of the insert commands you used?). I'd support adding a
currval-reset feature if someone can make a more compelling argument why
a connection-pooling application would need it.
There are big chunks of other state in the backend that are not
resettable --- prepared statements being one that I think will have much
more visibility in 7.4. Should we offer something to let all prepared
statements be dropped? Would connection poolers actually find it
useful? (I'd think it much more likely they want to re-use prepared
statements.)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-09 12:07 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Chris Gamache <cgg007@yahoo.com>
2 siblings, 0 replies; 7+ messages in thread
From: Achilleus Mantzios @ 2003-09-09 12:07 UTC (permalink / raw)
To: Chris Gamache <cgg007@yahoo.com>; +Cc: pgsql-sql
On Mon, 8 Sep 2003, Chris Gamache wrote:
> I'm using sequences and currval() to retrieve the last inserted row in a table.
>
>
> If currval() is undefined, as it is when a connection is made, then I know no
> rows were inserted in that table and can take a different action. This is
> problematic when using a connection pooling library, as the value of currval()
> for any given sequence could possibly be set from a previous "connection".
>
> One (theoretical) workaround would be to issue some sort of command to the
> back-end database to wipe all values of currval() when a "new" connection is
> made. I've done some digging in the system tables and source code, and can't
> find an obvious solution. Perhaps one you you gurus can suggest a SQL statement
> to do such a thing.
>
> Alternately, if there is a better way to retrieve the last inserted row for any
> given table, I'd be very grateful for the tip. It would need to be independent
> of the connection history, and undefined if there has not been a row inserted
> to the table during a definable interval of time (drop anchor when the
> "connection" begins, raise anchor when the "connection" ends), and be
> independant of the other connections inserting rows to the same table.
>
> Any idaeas?
Are you writing in java?
If yes, then implementing a wrapper around Connection would be a way.
>
> CG
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: undefine currval()
@ 2003-09-09 13:13 scott.marlowe <scott.marlowe@ihs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: scott.marlowe @ 2003-09-09 13:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; cgg007@yahoo.com; pgsql-sql
On Mon, 8 Sep 2003, Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > On Mon, 8 Sep 2003, Bruce Momjian wrote:
> >> I don't know how you could have an application that doesn't know if it
> >> has issued a nextval() in the current connection. Unless you can explain
> >> that, we have no intention of playing tricks with currval() for
> >> connection pooling.
>
> > Actually, I would think the very act of using connection pooling would
> > ensure that applications may well not know whether or not a nextval had
> > been called.
>
> The point is that it's not very sensible to be using currval except
> immediately after a nextval --- usually in the same transaction, I would
> think.
I'm pretty sure my second paragraph agreed with you on that.
> Certainly, not resetting currval implies that there is
> *potential* coupling between different transactions that happen to share
> a connection. But ISTM that such coupling would represent a bug in the
> application.
And that one too.
> Chris said he was using currval being undefined to know that no rows
> were inserted, but this seems less than compelling to me (why not look
> at the results of the insert commands you used?). I'd support adding a
> currval-reset feature if someone can make a more compelling argument why
> a connection-pooling application would need it.
I'd say that if someone is looking at that, it would be better to have
some kind of reset_connection call that makes a connection look like you
just established it.
Bit I'd never use it.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-09-09 13:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-08 21:12 undefine currval() Chris Gamache <cgg007@yahoo.com>
2003-09-08 21:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-08 22:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-08 23:03 ` scott.marlowe <scott.marlowe@ihs.com>
2003-09-08 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-09 13:13 ` scott.marlowe <scott.marlowe@ihs.com>
2003-09-09 12:07 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox