Thread: Re: postgres TODO

Re: postgres TODO

From
Bruce Momjian
Date:
> 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
 


Re: Re: postgres TODO

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
Peter Eisentraut
Date:
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



Re: Re: postgres TODO

From
Bruce Momjian
Date:
[ 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
 


Re: Re: postgres TODO

From
Alessio Bragadini
Date:
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


Re: Re: postgres TODO

From
eisentrp@csis.gvsu.edu
Date:
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



Re: Re: postgres TODO

From
Bruce Momjian
Date:
> 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
 


Re: Re: postgres TODO

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Re: postgres TODO

From
Tom Lane
Date:
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


Re: Re: postgres TODO

From
Ed Loehr
Date:
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


Re: Re: postgres TODO

From
"Mark Hollomon"
Date:
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


Re: Re: postgres TODO

From
Michael J Schout
Date:
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



Re: Re: postgres TODO

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
Andrew McMillan
Date:
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


Insert..returning (was Re: Re: postgres TODO)

From
Philip Warner
Date:
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   |/


Re: Insert..returning (was Re: Re: postgres TODO)

From
Tom Lane
Date:
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


RE: Re: postgres TODO

From
"Hiroshi Inoue"
Date:
> -----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


Re: Insert..returning (was Re: Re: postgres TODO)

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
Tom Lane
Date:
"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


Re: Insert..returning (was Re: Re: postgres TODO)

From
Philip Warner
Date:
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   |/


RE: Re: postgres TODO

From
"Hiroshi Inoue"
Date:
> -----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


Re: Insert..returning (was Re: Re: postgres TODO)

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Re: postgres TODO

From
JanWieck@t-online.de (Jan Wieck)
Date:
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 #




Re: Insert..returning (was Re: Re: postgres TODO)

From
Philip Warner
Date:
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   |/


Re: Insert..returning (was Re: Re: postgres TODO)

From
Tom Lane
Date:
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


Re: Insert..returning (was Re: Re: postgres TODO)

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
Philip Warner
Date:
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   |/


Re: Re: postgres TODO

From
Tom Lane
Date:
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


Re: Re: postgres TODO

From
Bruce Momjian
Date:
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