Thread: Re: postgres TODO
> 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 -- 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, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
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 |/
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.
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 provideit; 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
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
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.
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 targetlistrestricted to Var nodes. This doesn't require any changes in the FE/BE protocol. And a client using this new feature just expects TUPLES_OKinstead 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 #
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 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, Pennsylvania19026