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

At 00:21 12/07/00 +1000, Philip Warner wrote:

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?

Putting this another way, does anyone object to this being implemented, *at
least* in the case of single row updates?

Secondly, can anyone suggest likely problems that would occur in a naieve
'do a select after an insert' or 'keep a list of affected oids' approach?

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

Philip Warner <pjw@rhyme.com.au> writes:

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

Putting this another way, does anyone object to this being implemented, *at
least* in the case of single row updates?

Provide a specification *first*. What exactly do you expect to do,
and how will the code behave in the case of multiple affected rows,
zero affected rows, same row affected multiple times (possible with
a joined UPDATE), inherited UPDATE that affects rows in multiple tables,
inserts/updates that are suppressed or redirected or turned into
multiple operations (possibly on multiple tables) by rules or triggers,
etc etc? Not to mention the juicy topics of access permissions and
possible errors. Also, how will this affect the frontend/backend
protocol and what are the risks of breaking existing frontend code?
Finally, how does your spec compare to similar features in other DBMSs?

I don't have any fundamental objection to it given a well-thought-out
specification and implementation ... but I don't want to find us stuck
with supporting a half-baked nonstandard feature. We have quite enough
of those already ;-)

regards, tom lane

-----Original Message-----
From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
Behalf Of Tom Lane

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

Is OID really sufficient ?
I've wondered why people love OID so much.
PostgreSQL provides no specific access method using OID.
We couldn't assume that every table has its OID index,
when we need to handle general resultsets.
In fact,I've never created OID indexes on user tables.

I've forgotten to propose that INSERT returns TID together
with OID before 7.0. This has been in my mind since
I planned to implement Tid scan. Different from OID
,TID has its specific (fast) access method now.

Comments ?

Regards.

Hiroshi Inoue

At 21:28 11/07/00 -0400, Tom Lane wrote:

Putting this another way, does anyone object to this being implemented, *at
least* in the case of single row updates?

Provide a specification *first*.

Picky, picky, picky...

The basic pholosophy would be:

insert into x ... returning f1, f2

should produce the same results as:

insert into x ...
select f1,f2 from x where x.oid in (oid's of affected rows).

So the returned fields must be in the target table, and multiple rows could
be returned.

The only commercial DB that implements this kind of behaviour does it on
update only, and restricts it to updates that only affect one row. As a
first pass, it would satisfy 99.9% of users needs to only allow this
feature on inserts & updates that affected one row.

What exactly do you expect to do,
and how will the code behave in the case of multiple affected rows,

Ideally, return the rowset as per a select. But as above, it might be a lot
simpler to raise an error if more than one row is affected.

zero affected rows,

Do whatever 'update' does with zero rows affected.

same row affected multiple times (possible with
a joined UPDATE),

Return the most recent version of the row.

inherited UPDATE that affects rows in multiple tables,

I don't know much about inherited stuff, which is why I posted the original
question about non-trivial problems with the implementation.

In this case I would say it should fall back on trying to reproduce the
behaviour of an 'insert into x*' followed by a 'select ... from x*'

inserts/updates that are suppressed or redirected or turned into
multiple operations (possibly on multiple tables) by rules or triggers,
etc etc?

This is why I mentioned the 'maintain a list of affected oids' option; it
should only return rows of the target table that were affected by the
statement. When I do an 'insert' statement in psql, it reports the number
of rows inserted: whatever is used to show this number should be used to
determine the rows returned.

Not to mention the juicy topics of access permissions and
possible errors.

Can't one fall back here on the 'insert followed by select' analogy? Or is
there a specific example that you have in mind?

Also, how will this affect the frontend/backend
protocol and what are the risks of breaking existing frontend code?

I have absolutely no idea - hence why I asked what people who knew PG
thought of the suggestion.

I had naievely assumed that the fe would pass a query off to the be, and
handle the result based on what the be tells it to do. ie. I assumed that
the fe would not know that it was passing an 'insert' statement, and
therefor would not die when it got a set of tuples returned.

Finally, how does your spec compare to similar features in other DBMSs?

See above.

I don't have any fundamental objection to it given a well-thought-out
specification and implementation ... but I don't want to find us stuck
with supporting a half-baked nonstandard feature. We have quite enough
of those already ;-)

And I'm happy to do the leg work, if we can come to a design that people
who understand pg internals think will (a) not involve rewriting half of
pg, and (b) be clear, concise and easily supportable.

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

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

I've forgotten to propose that INSERT returns TID together
with OID before 7.0. This has been in my mind since
I planned to implement Tid scan. Different from OID
,TID has its specific (fast) access method now.

Couple of thoughts here ---

* OID is (nominally) unique across tables. TID is not. This is a
serious point in the presence of inheritance. I'd like to see the
return be table OID plus TID if we are going to rely on TID.

* TID identification of a row does not survive VACUUM, does it?
So you'd have to assume a vacuum didn't happen in between. Seems a
little risky. Vadim's overwriting smgr would make this issue a lot
worse. Might be OK in certain application contexts, but I wouldn't
want to encourage people to use it without thinking.

* I don't see any way to add TID (or table OID) to the default return
data without changing the fe/be protocol and breaking a lot of existing
client code.

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

regards, tom lane

At 12:15 12/07/00 +1000, Philip Warner wrote:

The only commercial DB that implements this kind of behaviour does it on
update only, and restricts it to updates that only affect one row. As a
first pass, it would satisfy 99.9% of users needs to only allow this
feature on inserts & updates that affected one row.

The more I think about this, the more I think they probably had a good
reason for doing it. The cleanest solution seems to be that updates &
inserts affecting more than one row should produce an error.

I'd be very interested in how people think rules and triggers should be
handled.

My initial inclination is that if a trigger prevents the insert, then it is
the responsibility of the programmer to check the number of rows affected
after the update (the returned fields would either not exist, or be null).

If a rule rewrites the insert as an insert into another table, then I am
not sure what is best: either raise an error, or return the fields from the
*real* target table. I *think* I prefer raising an error, since any other
behaviour could be very confusing.

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

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

I've forgotten to propose that INSERT returns TID together
with OID before 7.0. This has been in my mind since
I planned to implement Tid scan. Different from OID
,TID has its specific (fast) access method now.

Couple of thoughts here ---

* OID is (nominally) unique across tables. TID is not. This is a
serious point in the presence of inheritance. I'd like to see the
return be table OID plus TID if we are going to rely on TID.

* TID identification of a row does not survive VACUUM, does it?
So you'd have to assume a vacuum didn't happen in between. Seems a
little risky. Vadim's overwriting smgr would make this issue a lot
worse. Might be OK in certain application contexts, but I wouldn't
want to encourage people to use it without thinking.

VACUUM would invalidate keeped TIDs. Even OIDs couldn't
survive 'drop and create table'.
So I would keep [relid],oid,tid-s for fetched rows and reload
the rows using tids (and [relid]). If the OID != keeped OID,then
I would refresh the resultset entirely.

BTW,wouldn't TIDs be more stable under overwriting smgr ?
Unfortunately TIDs are transient under current no overwrite
smgr and need to follow update chain of tuples.

* I don't see any way to add TID (or table OID) to the default return
data without changing the fe/be protocol and breaking a lot of existing
client code.

I've thought backends could return info
'INSERT oid count tid'
to their frontends but is it imposiible ?
Should (tuples)count be the 3rd and the last item to return on INSERT ?

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

If commandInfo(cmdStatus) is unavailable,this seems to be
needed though I don't know how to implement it.

Regards.

Hiroshi Inoue

Tom Lane wrote:

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

I like that one alot more too. It should be relatively easy
to add a list of attributes (specified after RETURNING) to
the querytree. Then send out a regular result set of tuples
built from the requested attributes of the new tuple (at
INSERT/UPDATE) or the old one (at DELETE) during the executor
run. Or maybe both and specified as NEW.attname vs.
OLD.attnam? Then it needs AS too, making the attribute list
looking like a targetlist restricted to Var nodes.

This doesn't require any changes in the FE/BE protocol. And a
client using this new feature just expects TUPLES_OK instead
of COMMAND_OK when using the new functionality.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Thus spake Philip Warner

Not to mention the juicy topics of access permissions and
possible errors.

Can't one fall back here on the 'insert followed by select' analogy? Or is
there a specific example that you have in mind?

I think the thing he has in mind is the situation where one has insert
perms but not select. The decision is whether to have the insert fail
if the select fails. Or, do you allow the (virtual) select in this
case since it is your own inserted row you are trying to read?

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

At 05:14 12/07/00 -0400, D'Arcy J.M. Cain wrote:

Thus spake Philip Warner

Not to mention the juicy topics of access permissions and
possible errors.

Can't one fall back here on the 'insert followed by select' analogy? Or is
there a specific example that you have in mind?

I think the thing he has in mind is the situation where one has insert
perms but not select. The decision is whether to have the insert fail
if the select fails. Or, do you allow the (virtual) select in this
case since it is your own inserted row you are trying to read?

I would be inclined to follow the perms; is there a problem with that? You
should not let them read the row they inserted since it *may* contain
sensitive (automatically generated) data - the DBA must have had a reason
for preventing SELECT.

The next question is whether they should be allowed to do the insert, and
again I would be inclined to say 'no'. Can we check perms easily at the start?

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

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

Yes, and the current fe-be protocol can handle it, since an on insert rule
can also return a select result for an insert statement.

Andreas

Philip Warner <pjw@rhyme.com.au> writes:

I think the thing he has in mind is the situation where one has insert
perms but not select.

Exactly --- and that's a perfectly reasonable setup in some cases (think
blind mailbox). INSERT ... RETURNING should require both insert and
select privileges IMHO.

I would be inclined to follow the perms; is there a problem with that? You
should not let them read the row they inserted since it *may* contain
sensitive (automatically generated) data - the DBA must have had a reason
for preventing SELECT.

It would be a pretty stupid app that would be using INSERT ... RETURNING
to obtain the data that it itself is supplying. The only reason I can
see for the feature is to get hold of automatically-generated column
values. Thus, obeying select permissions is relevant.

regards, tom lane

At 12:47 12/07/00 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

I think the thing he has in mind is the situation where one has insert
perms but not select.

Exactly --- and that's a perfectly reasonable setup in some cases (think
blind mailbox). INSERT ... RETURNING should require both insert and
select privileges IMHO.

You won't get any argument from me.

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

At 11:05 12/07/00 +0200, Jan Wieck wrote:

Tom Lane wrote:

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

What sort of syntax would you use to request TID?

I like that one alot more too. It should be relatively easy
to add a list of attributes (specified after RETURNING) to
the querytree.

For you, maybe! If you feel like giving me a list of sources that will get
me into this, that would be great. I've looked through various executor
modules and the parser, but would appreciate any advice you have to offer...

Note: I am not plaaning on *making* changes, just yet. I'm mainly
interested in understanding the suggestions people are making!

Then send out a regular result set of tuples
built from the requested attributes of the new tuple (at
INSERT/UPDATE) or the old one (at DELETE) during the executor
run.

This sounds like what I want to do.

Or maybe both and specified as NEW.attname vs.
OLD.attnam? Then it needs AS too, making the attribute list
looking like a targetlist restricted to Var nodes.

This also sounds like a cute feature, so long as it fits naturally into the
changes.

This doesn't require any changes in the FE/BE protocol. And a
client using this new feature just expects TUPLES_OK instead
of COMMAND_OK when using the new functionality.

Sounds good.

Thanks everybody for the feedback, I'll try to understand it and then get
back with a revised plan...

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

Philip Warner <pjw@rhyme.com.au> writes:

At 11:05 12/07/00 +0200, Jan Wieck wrote:

Tom Lane wrote:

Philip's INSERT ... RETURNING idea could support returning TID and
table OID as a special case, and it has the saving grace that it
won't affect apps that don't use it...

What sort of syntax would you use to request TID?

... RETURNING ctid

This might be a little tricky; you'd have to be sure the RETURNING
code executes late enough that a TID has been assigned to the tuple.
Not sure if post-insert trigger time is late enough or not (Jan?)
but in principle it's not a special case at all, just a system
attribute the same as OID.

regards, tom lane

Restated in TODO:

* Allow [INSERT/UPDATE] ... RETURNING new.col or old.col (Philip)

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