Re: postgres TODO

At 09:14 8/07/00 -0400, Bruce Momjian wrote:

hi,

threre are a postgresql/mysql comparative.
You can get something for the TODO:

http://www.phpbuilder.com/columns/tim20000705.php3?page=1

regards,

Thanks. Yes, I have added to the TODO list:

* Add function to return primary key value on INSERT

I had a look at the page and could not see the reference, so this
suggestion may be inappropriate, but...

How about something more general - an incredibly useful feature of Dec/Rdb is:

insert into t1(...) values(...) returning attr-list

which is like performing a select directly after the insert. The same kind
of syntax applies to updates as well, eg.

update t1 set f1 = 2 where <stuff> returning f1, f2, f3;

Perhaps your original suggestion is a lot easier, but this is a convenient
feature...

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval(), for oids there's PQoidValue().

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

[ Charset ISO-8859-1 unsupported, converting... ]

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval(), for oids there's PQoidValue().

Yes, item removed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Peter Eisentraut wrote:

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://www.sevenseas.org/~alessio
Nicosia, Cyprus phone: +357-2-750652

"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925

On Mon, 10 Jul 2000, Alessio Bragadini wrote:

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

Sequences are transaction safe.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

Peter Eisentraut wrote:

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

No curval() is per-backend value that is not affected by other users.
My book has a mention of that issue, and so does the FAQ.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 09:14 10/07/00 -0400, eisentrp@csis.gvsu.edu wrote:

On Mon, 10 Jul 2000, Alessio Bragadini wrote:

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

Sequences are transaction safe.

Really? I thought I read somewhere that they did not rollback so that
locking could be avoided, hence they would not be a major source of
contention. If that is true, it does seem to imply that they can be updated
by other processes (Otherwise they would present a locking problem). Or do
you mean that they maintain a 'curval' that was the last value use in the
current TX?

Either way it's still not a help, consider:

create table t1(f1 int4, f2 text);

create trigger t1_ir_tg1 after insert on t1
(
insert into t1_audit(t1.id, nextval('id'), "Row created");
) for each row;

insert into t1(nextval('id'), "my main row");

Not necessarily a real case, and fixed by using two sequences. But with a
more complex set of triggers or rules, there is a real chance of stepping
on curval().

How hard would it be to implement:

insert into t1(nextval('id'), "my main row") returning f1, f2;

or similar?

[in the above case, the insert statement should be identical to:

insert into t1(nextval('id'), "my main row") returning f1, f2;
select f1, f2 from t1 where oid=<new row oid>
]

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Peter Eisentraut wrote:

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you

inserted? For

sequences there's curval()

Mmmhhh... it means that we can assume no update to the

sequence value

between the insert and the curval selection?

No curval() is per-backend value that is not affected by other users.
My book has a mention of that issue, and so does the FAQ.

Not all default values need to be a sequence, thus imho
this function is a useful extension. ODBC has it too.

Andreas

Thus spake Alessio Bragadini

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

We can within one connection so this is safe but there are other problems
which I am not sure would be solved by this anyway. With rules, triggers
and defaults there are often changes to the row between the insert and the
values that hit the backing store. This is a general problem of which
the primary key is only one example.

In fact, the OID of the new row is returned so what stops one from just
using it to get any information required. This is exactly what PyGreSQL
does in its insert method. After returning, the dictionary used to store
the fields for the row have been updated with the actual contents of the
row in the database. It simply does a "SELECT *" using the new OID to
get the row back.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Thus spake Alessio Bragadini

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you

inserted? For

sequences there's curval()

Mmmhhh... it means that we can assume no update to the

sequence value

between the insert and the curval selection?

We can within one connection so this is safe but there are
other problems
which I am not sure would be solved by this anyway. With
rules, triggers
and defaults there are often changes to the row between the
insert and the
values that hit the backing store. This is a general problem of which
the primary key is only one example.

In fact, the OID of the new row is returned so what stops one
from just
using it to get any information required. This is exactly
what PyGreSQL
does in its insert method. After returning, the dictionary
used to store
the fields for the row have been updated with the actual
contents of the
row in the database. It simply does a "SELECT *" using the new OID to
get the row back.

OID access is not indexed by default, only if the dba created a
corresponding
index. Thus OID access is a seq scan in a default environment.

Andreas

At 15:58 10/07/00 +0200, Zeugswetter Andreas SB wrote:

OID access is not indexed by default, only if the dba created a
corresponding
index. Thus OID access is a seq scan in a default environment.

Sticking my head out even further, this seems like a good reason to use
'insert/update...returning' - isn't the tuple already on the heap, and
easily available at the end of the insert/update?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Mon, 10 Jul 2000, Zeugswetter Andreas SB wrote:

Peter Eisentraut wrote:

Bruce Momjian writes:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you

inserted? For

sequences there's curval()

Mmmhhh... it means that we can assume no update to the

sequence value

between the insert and the curval selection?

No curval() is per-backend value that is not affected by other users.
My book has a mention of that issue, and so does the FAQ.

Not all default values need to be a sequence, thus imho
this function is a useful extension. ODBC has it too.

actually, had thought about this too over the weekend ... if I define a
'serial' type, it right now creates a sequence for that ... if I recall
correctly, that was purely a kludge until someone built a better 'serial'
...

having an INSERT return the value of 'serial' that was used would save a
second SELECT call *and* eliminate the requirement for an app programmer
to have to know to do a 'SELECT curval('table_field_seq');' ...

Alessio Bragadini <alessio@albourne.com> writes:

Peter Eisentraut wrote:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

Mmmhhh... it means that we can assume no update to the sequence value
between the insert and the curval selection?

Yes, we can --- currval is defined to tell you the last sequence value
allocated *in this backend*.

Actually you could still get burnt if you had a sufficiently complicated
set of rules and triggers ... there could be another update of the
sequence induced by one of your own triggers, and if you forget to allow
for that you'd have a problem. But you don't have to worry about other
backends.

However, I still prefer the SELECT nextval() followed by INSERT approach
over INSERT followed by SELECT currval(). It just feels cleaner.

To get back to Peter's original question, you don't necessarily "know
what you inserted" if you allow columns to be filled with default values
that are calculated by complicated functions. A serial column is just
the simplest example of that. Whether this situation is common enough
to justify a special hack in INSERT is another question. I kinda doubt
it. We already return the OID which is sufficient info to select the
row again if you need it. Returning the primary key would be
considerably more work for no visible gain in functionality...

regards, tom lane

Tom Lane wrote:

Alessio Bragadini <alessio@albourne.com> writes:

Peter Eisentraut wrote:

* Add function to return primary key value on INSERT

I don't get the point of this. Don't you know what you inserted? For
sequences there's curval()

To get back to Peter's original question, you don't necessarily "know
what you inserted" if you allow columns to be filled with default values
that are calculated by complicated functions. A serial column is just
the simplest example of that. Whether this situation is common enough
to justify a special hack in INSERT is another question. I kinda doubt
it. We already return the OID which is sufficient info to select the
row again if you need it. Returning the primary key would be
considerably more work for no visible gain in functionality...

It's definitely not a crucial functionality gain, IMO, but it is
nonetheless a gain when you consider that *every* pgsql developer on
the planet could then do something in one query that currently takes
two (plus the requisite error-handling code). A few other counter-
arguments for returning the autoincrement/serial/pkey:

1) it earns bad press w/r/t usability;
2) it is an FAQ on the lists;
3) it is an extremely common operation;
4) other DBs provide it;

Regards,
Ed Loehr

Tom Lane wrote:

it. We already return the OID which is sufficient info to select the
row again if you need it. Returning the primary key would be
considerably more work for no visible gain in functionality...

But OID is not available for views. I have already run into
this situation. I have a view which is a join across 3 tables.
two of the underlying tables have serial fields as primary keys.

INSERT ... RETURNING ... would be very nice indeed.

--

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

actually, had thought about this too over the weekend ... if I define a
'serial' type, it right now creates a sequence for that ... if I recall
correctly, that was purely a kludge until someone built a better 'serial'
...

having an INSERT return the value of 'serial' that was used would save a
second SELECT call *and* eliminate the requirement for an app programmer
to have to know to do a 'SELECT curval('table_field_seq');' ...

Yes, I can imagine that the table_field_seq name is kind of flakey. It
will choose a different name if there is a conflict, so it is an chance
for failure.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Mon, 10 Jul 2000, Tom Lane wrote:

However, I still prefer the SELECT nextval() followed by INSERT approach
over INSERT followed by SELECT currval(). It just feels cleaner.

Just an aside. We use a system similar to MySQL's "auto_increment" system to
get the value. What we do is have a function that will return CURRVAL of the
first defaulted int4 column of the table in question. This query gets the
default clause:

SELECT d.adsrc, a.attnum, a.attname
FROM pg_class c, pg_attribute a, pg_attrdef d, pg_type t
WHERE c.relname = ?
AND a.attnum > 0
AND a.attrelid = c.oid
AND d.adrelid = c.oid
AND a.atthasdef = true
AND d.adnum = a.attnum
AND a.atttypid = t.oid
AND t.typname = 'int4'
ORDER BY a.attnum
LIMIT 1

Then we just pull out the part in the nextval('.....') and return the currval
of that string. Works like a charm. This is done in perl, so when we need the
last insert id, we just call:

$id = get_insert_id($dbh, $table);

Anyways, its easy enough to get at the information this way without making your
application depend on OID values. Yes, you might still get bunt by triggers.
I am not sure if there is an easy solution to that.

Mike

At 09:07 11/07/00 -0500, Michael J Schout wrote:

Anyways, its easy enough to get at the information this way without making

your

application depend on OID values. Yes, you might still get bunt by triggers.
I am not sure if there is an easy solution to that.

Well, not wanting to sound too much like a broken record,

insert...returning...

would seem to fix the problem.

Is there some obvious (to anyone who knows something about pg internals)
reason why this is *not* a good idea?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Tom Lane wrote:

However, I still prefer the SELECT nextval() followed by INSERT approach
over INSERT followed by SELECT currval(). It just feels cleaner.

This is the way I have been doing it, so I'm pleased to see you
endorsing it :-)

What I don't like about this way though is that I have to (A) do two
statements and (B) set up the permissions on my sequence as well as on
my table. If I could just get the inserted tuple back somehow it would
definitely simplify my application.

To get back to Peter's original question, you don't necessarily "know
what you inserted" if you allow columns to be filled with default values
that are calculated by complicated functions. A serial column is just
the simplest example of that. Whether this situation is common enough
to justify a special hack in INSERT is another question. I kinda doubt
it. We already return the OID which is sufficient info to select the
row again if you need it. Returning the primary key would be
considerably more work for no visible gain in functionality...

For some reason I find almost every situation in which I INSERT with a
SERIAL I want to provide user feedback that includes that allocated
SERIAL. The use of primary keys is not restricted purely to in-database
storage - they can get transferred into people's brains and e-mailed
around the place and so on.

Getting that back from an INSERT would definitely be useful to me.

Thanks,
Andrew.

--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267