lastval()

Dennis Bjorklund <db@zigo.dhs.org> writes:

Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

Why is that a good idea? In a complex application it'd be awfully easy
to break logic that depends on such a thing.

regards, tom lane

Tom Lane wrote:

Sent: Monday, May 09, 2005 8:37 AM
To: Dennis Bjorklund
Cc: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] lastval()

Dennis Bjorklund <db@zigo.dhs.org> writes:

Here is a small patch that implements a function lastval()

that works

just like currval() except that it give the current value

of the last

sequence used by nextval().

Why is that a good idea? In a complex application it'd be
awfully easy to break logic that depends on such a thing.

And you threw out the idea when I proposed it more than a year ago.
However I'd still like to see it, tho in the same shape as 'the other
database'.

Eg, succeeding when nextval has not been called, returning 0.
Thus, my suggestion for last_insert_id() still stands.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org

... John

Tom Lane wrote:

Why is that a good idea? In a complex application it'd be awfully easy
to break logic that depends on such a thing.

True, but I think it offers a usefully concise syntax for simpler
applications. Perhaps the documentation should be amended to mention the
potential risks? (e.g. additional nextval() calls in between the
nextval() you are interested in and the lastval()).

-Neil

Neil Conway <neilc@samurai.com> writes:

Tom Lane wrote:

Why is that a good idea? In a complex application it'd be awfully easy
to break logic that depends on such a thing.

True, but I think it offers a usefully concise syntax for simpler
applications. Perhaps the documentation should be amended to mention the
potential risks?

Like, say, the sequence being deleted before the lastval call?

If I thought it was a good idea at all, I'd bother to criticize the
patch itself --- it's got some problems.

regards, tom lane

On Sun, 8 May 2005, Tom Lane wrote:

Why is that a good idea? In a complex application it'd be awfully easy
to break logic that depends on such a thing.

Of course it can break. currval() can also break in a complex application
with triggers and rules that do things the developer does not expect.

There are however lots of cases where it is safe and useful. Not the least
when you want to port an application that uses similar features.

--
/Dennis Bj�rklund

On Sun, 8 May 2005, Tom Lane wrote:

Like, say, the sequence being deleted before the lastval call?

Then you get an error message. Same thing if you have revoked the rights
on the sequence before you call lastval().

In this case you can get a value that belong to a sequence that is
deleted. Is that better? To me it's a sign that something is wrong with
the application and an error is better to get. It's not like it's hard to
store a int64 value instead. It's in fact simpler, but I just don't see
that it solve any problem. If anything it can hide problems.

If you want lastval() to work just don't delete the sequence. It's as
simple as that.

The thing is that I don't care how it's implemented, it's the feature
itself that is more importent to decide if we want it or not. I'm sure the
code can be fixed so everybody is happy it in the end,

--
/Dennis Bj�rklund

On Mon, 9 May 2005, Dennis Bjorklund wrote:

The thing is that I don't care how it's implemented, it's the feature
itself that is more importent to decide if we want it or not. I'm sure the
code can be fixed so everybody is happy it in the end,

You could implement this on top of the current nextval without backend
changes.

Create a wrapper function on top of nextval that stores the value in a
temp table. Or a session variable if your PL language of choice has
them.

lastval would do a select on the temp table.

- Heikki

Heikki Linnakangas wrote:

On Mon, 9 May 2005, Dennis Bjorklund wrote:

The thing is that I don't care how it's implemented, it's the feature
itself that is more importent to decide if we want it or not. I'm
sure the
code can be fixed so everybody is happy it in the end,

You could implement this on top of the current nextval without backend
changes.

Create a wrapper function on top of nextval that stores the value in a
temp table. Or a session variable if your PL language of choice has them.

lastval would do a select on the temp table.

And this is making life easier for anybody? I don't think so.

cheers

andrew

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

I like the concept, but I haven't looked at the code -- I'd be happy to
review the implementation, although I won't waste my time if most people
are opposed to the idea itself.

-Neil

Neil Conway wrote:

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

I like the concept, but I haven't looked at the code -- I'd be happy to
review the implementation, although I won't waste my time if most people
are opposed to the idea itself.

I can't speak to the code but lastval is something that has been
requested by my customers many times.

Sincerely,

Joshua D. Drake

Neil Conway wrote:

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

I like the idea of lastval, though I would rather see us just use
currval() with no argument for it, rather than invent a new function
name. It does the same as currval('last sequence called') right?

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

I like the concept, but I haven't looked at the code -- I'd be happy to
review the implementation, although I won't waste my time if most people
are opposed to the idea itself.

It'd make implementing various PHP userland functions a real breeze...

Chris

Abhijit Menon-Sen said:

At 2005-05-11 10:55:37 +1000, neilc@samurai.com wrote:

Here is a small patch that implements a function lastval() [...]

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

For what it's worth, I think it's a bad idea.

In the MySQL wire protocol (hi Dennis!), the "last insert id" is sent
along with every "OK" message, and the client can just keep the value
in memory. Users call a function to retrieve that value, rather than
issuing a "SELECT nextval()".

You can do both - they have an SQL level function as well as supporting it
at the protocol layer. See
http://dev.mysql.com/doc/mysql/en/information-functions.html

So the server-side lastval() function is not enough for any meaningful
compatibility. The client would also need to be changed to provide the
pgsql_last_insert_id() or a similar function (which could do a "SELECT
lastval()" internally).

In this situation -- where both client changes AND a server round-trip
are required -- what's the point of adding cruft to the server? Might
as well confine changes to the client, and use nextval to implement the
feature.

I don't believ it can be sensibly done by the client alone. Either it needs
something like this or it shouldn't be done at all.

By the way, what would lastval() do if an insert trigger inserts a row
into a table with another serial column?

or more than one? Yes, it's not good in certain circumstances. That doesn't
make it useless in all circumstances.

I'm not jumping out of my seat to have this. But as Joshua points out, it is
frequently requested.

cheers

andrew

At 2005-05-11 10:55:37 +1000, neilc@samurai.com wrote:

Here is a small patch that implements a function lastval() [...]

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

For what it's worth, I think it's a bad idea.

In the MySQL wire protocol (hi Dennis!), the "last insert id" is sent
along with every "OK" message, and the client can just keep the value
in memory. Users call a function to retrieve that value, rather than
issuing a "SELECT nextval()".

So the server-side lastval() function is not enough for any meaningful
compatibility. The client would also need to be changed to provide the
pgsql_last_insert_id() or a similar function (which could do a "SELECT
lastval()" internally).

In this situation -- where both client changes AND a server round-trip
are required -- what's the point of adding cruft to the server? Might
as well confine changes to the client, and use nextval to implement
the feature.

By the way, what would lastval() do if an insert trigger inserts a row
into a table with another serial column?

-- ams

Neil Conway wrote:

Sent: Wednesday, May 11, 2005 10:56 AM
To: Dennis Bjorklund
Cc: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] lastval()

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval()

that works

just like currval() except that it give the current value

of the last

sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm
just wondering if there are other opinions out there.)

I'm all for it. Even more so if the 'currval(void) called before
nextval(seq_name)' error message could be supressed by a GUC variable
and return 0 instead.

I like the concept, but I haven't looked at the code -- I'd
be happy to review the implementation, although I won't waste
my time if most people are opposed to the idea itself.

-Neil

---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

.. John

John Hansen wrote:

Neil Conway wrote:

Sent: Wednesday, May 11, 2005 10:56 AM
To: Dennis Bjorklund
Cc: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] lastval()

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval()

that works

just like currval() except that it give the current value

of the last

sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm
just wondering if there are other opinions out there.)

I'm all for it. Even more so if the 'currval(void) called before
nextval(seq_name)' error message could be supressed by a GUC variable
and return 0 instead.

Why zero and no error?

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

Abhijit Menon-Sen wrote:

At 2005-05-11 10:55:37 +1000, neilc@samurai.com wrote:

Here is a small patch that implements a function lastval() [...]

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

For what it's worth, I think it's a bad idea.

In the MySQL wire protocol (hi Dennis!), the "last insert id" is sent
along with every "OK" message, and the client can just keep the value
in memory. Users call a function to retrieve that value, rather than
issuing a "SELECT nextval()".

So the server-side lastval() function is not enough for any meaningful
compatibility. The client would also need to be changed to provide the
pgsql_last_insert_id() or a similar function (which could do a "SELECT
lastval()" internally).

In this situation -- where both client changes AND a server round-trip
are required -- what's the point of adding cruft to the server? Might
as well confine changes to the client, and use nextval to implement
the feature.

By the way, what would lastval() do if an insert trigger inserts a row
into a table with another serial column?

It fails, just like it would fail now if the trigger inserted into the
same table that used the trigger, or a rule.

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

At 2005-05-10 23:30:05 -0400, pgman@candle.pha.pa.us wrote:

By the way, what would lastval() do if an insert trigger inserts
a row into a table with another serial column?

It fails, just like it would fail now if the trigger inserted into
the same table that used the trigger, or a rule.

I don't understand what you mean. "Just like it would fail now"? It
doesn't exist yet, how can it fail? And how would it know when to
fail anyway, rather than return a wrong value?

-- ams

Bruce Momjian wrote:

Sent: Wednesday, May 11, 2005 1:27 PM
To: John Hansen
Cc: Neil Conway; Dennis Bjorklund; pgsql-patches@postgresql.org
Subject: Re: [PATCHES] lastval()

John Hansen wrote:

Neil Conway wrote:

Sent: Wednesday, May 11, 2005 10:56 AM
To: Dennis Bjorklund
Cc: pgsql-patches@postgresql.org
Subject: Re: [PATCHES] lastval()

Dennis Bjorklund wrote:

Here is a small patch that implements a function lastval()

that works

just like currval() except that it give the current value

of the last

sequence used by nextval().

What do people think of this idea? (Tom seems opposed, I'm just
wondering if there are other opinions out there.)

I'm all for it. Even more so if the 'currval(void) called before
nextval(seq_name)' error message could be supressed by a

GUC variable

and return 0 instead.

Why zero and no error?

That's the exact behaviour of the "other database's" equivalent.
Makes porting easier, and avoids hugely annoying error messages in the
logfiles.