pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPQoidValue - get last ID of primary key after INSERT - small fix
4+ messages / 3 participants
[nested] [flat]
* PQoidValue - get last ID of primary key after INSERT - small fix
@ 2006-08-09 11:38 Georgi Kolev <geo_kolev@mail.ru>
2006-08-10 18:40 ` Re: PQoidValue - get last ID of primary key after INSERT - small fix Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Georgi Kolev @ 2006-08-09 11:38 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
In psycopg2 the value of ID after INSERT statement is derived by
'cursor.lastrowid' attribute which in turn calls PQoidValue().
The easiest way to fix the problem were to fix heap_insert() to get the
right value in case there is no OID column declared in the table.
In case there is an OID, the function works the 'old' manner.
If there is no OID, we serach for PKEY column which is declared
'SERIAL' (at the moment I check the type to be 'int4').
I think the best way is to develop brand new functions PQid4Value and
PQid8Value similar to PQoidValue (or something else) to solve the whole
problem with SERIAL and BIGSERIAL.
Bellow is the piece of the code I have noticed. I can propose it as a
diff patch or else as needed. If there are some better ideas of fixing
the problem I will be very glad to discuss them :)
/backend/access/heap/heapam.c:1097
Oid
heap_insert(Relation relation, HeapTuple tup, CommandId cid,
bool use_wal, bool use_fsm)
{
...
if (!OidIsValid(HeapTupleGetOid(tup)))
{
TupleDesc tupdesc = RelationGetDescr(relation);
int natts = tupdesc->natts;
int varattno;
Form_pg_attribute att;
List *indexoidlist;
ListCell *indexoidscan;
indexoidlist = RelationGetIndexList(relation);
foreach(indexoidscan, indexoidlist)
{
Oid indexoid = lfirst_oid(indexoidscan);
HeapTuple indexTuple;
Form_pg_index indexStruct;
int i;
indexTuple = SearchSysCache(INDEXRELID,
ObjectIdGetDatum(indexoid),
0, 0, 0);
if (!HeapTupleIsValid(indexTuple))
elog(ERROR, "cache lookup failed for index %u", indexoid);
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
if (indexStruct->indisprimary && indexStruct->indnatts == 1)
{
for (varattno = 0; varattno < natts; varattno++)
{
att = tupdesc->attrs[varattno];
if (indexStruct->indkey.values[0] == att->attnum)
{
Oid typoid;
HeapTuple typeTuple;
char *atttype;
typoid = tupdesc->attrs[varattno]->atttypid;
typeTuple = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typoid),
0, 0, 0);
if (!HeapTupleIsValid(typeTuple))
return NULL;
atttype = pstrdup(NameStr(((Form_pg_type)
GETSTRUCT(typeTuple))->typname));
ReleaseSysCache(typeTuple);
if (strcmp(atttype, "int4")==0)
{
Datum origval;
bool isnull;
origval = heap_getattr(tup, varattno+1, tupdesc, &isnull);
ReleaseSysCache(indexTuple);
list_free(indexoidlist);
return DatumGetInt32(origval);
}
}
}
}
ReleaseSysCache(indexTuple);
}
list_free(indexoidlist);
}
return HeapTupleGetOid(tup);
}
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PQoidValue - get last ID of primary key after INSERT - small fix
2006-08-09 11:38 PQoidValue - get last ID of primary key after INSERT - small fix Georgi Kolev <geo_kolev@mail.ru>
@ 2006-08-10 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-11 15:51 ` Re: PQoidValue - get last ID of primary key after INSERT - small Georgi Kolev <geo_kolev@mail.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-08-10 18:40 UTC (permalink / raw)
To: Georgi Kolev <geo_kolev@mail.ru>; +Cc: pgsql-interfaces
Georgi Kolev <geo_kolev@mail.ru> writes:
> The easiest way to fix the problem were to fix heap_insert() to get the
> right value in case there is no OID column declared in the table.
Not a chance. I could make a list of objections to that that's longer
than your patch, but the short of it is that this is a completely
inappropriate level of the system to be doing that kind of work in.
What we *are* working on is INSERT RETURNING (in fact I'm about to start
work on reviewing/committing that patch right now...), which is a much
more general solution to the problem.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PQoidValue - get last ID of primary key after INSERT - small
2006-08-09 11:38 PQoidValue - get last ID of primary key after INSERT - small fix Georgi Kolev <geo_kolev@mail.ru>
2006-08-10 18:40 ` Re: PQoidValue - get last ID of primary key after INSERT - small fix Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-08-11 15:51 ` Georgi Kolev <geo_kolev@mail.ru>
2006-08-17 00:34 ` Re: PQoidValue - get last ID of primary key after INSERT - small Gregory Stark <gsstark@mit.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Georgi Kolev @ 2006-08-11 15:51 UTC (permalink / raw)
To: pgsql-interfaces
Hi Tom,
Tom Lane wrote:
> Georgi Kolev <geo_kolev@mail.ru> writes:
>> The easiest way to fix the problem were to fix heap_insert() to get the
>> right value in case there is no OID column declared in the table.
>
> Not a chance. I could make a list of objections to that that's longer
> than your patch, but the short of it is that this is a completely
> inappropriate level of the system to be doing that kind of work in.
>
My basic idea was to implement such functionality as in Sybase and MSSQL
Server, I mean the @@identity system variable, which is filled with the
identity column of the last INSERT statement executed. I just needed
some simple solution to fix the problem. Now it works perfectly for me.
> What we *are* working on is INSERT RETURNING (in fact I'm about to start
> work on reviewing/committing that patch right now...), which is a much
> more general solution to the problem.
>
> regards, tom lane
>
I agree that the solution INSERT...RETURNING is more general and
flexible. But I think if we have such a solution as @@identity as in
Sybase, PostgreSQL will be much more compatible and adoptable for
migrations.
Regards, Georgi Kolev
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PQoidValue - get last ID of primary key after INSERT - small
2006-08-09 11:38 PQoidValue - get last ID of primary key after INSERT - small fix Georgi Kolev <geo_kolev@mail.ru>
2006-08-10 18:40 ` Re: PQoidValue - get last ID of primary key after INSERT - small fix Tom Lane <tgl@sss.pgh.pa.us>
2006-08-11 15:51 ` Re: PQoidValue - get last ID of primary key after INSERT - small Georgi Kolev <geo_kolev@mail.ru>
@ 2006-08-17 00:34 ` Gregory Stark <gsstark@mit.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Gregory Stark @ 2006-08-17 00:34 UTC (permalink / raw)
To: Georgi Kolev <geo_kolev@mail.ru>; +Cc: pgsql-interfaces
Georgi Kolev <geo_kolev@mail.ru> writes:
> I agree that the solution INSERT...RETURNING is more general and flexible. But
> I think if we have such a solution as @@identity as in Sybase, PostgreSQL will
> be much more compatible and adoptable for migrations.
What characteristic does @@identity have that lastval() lacks?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-08-17 00:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-09 11:38 PQoidValue - get last ID of primary key after INSERT - small fix Georgi Kolev <geo_kolev@mail.ru>
2006-08-10 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-11 15:51 ` Re: PQoidValue - get last ID of primary key after INSERT - small Georgi Kolev <geo_kolev@mail.ru>
2006-08-17 00:34 ` Re: PQoidValue - get last ID of primary key after INSERT - small Gregory Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox