Thread: Using oid as pkey
What are the concerns with using oid as the column for a primary key declaration for use in trigger-based replication? TIA, Ed
On Aug 20, 2007, at 16:58 , Ed L. wrote: > What are the concerns with using oid as the column for a primary > key declaration for use in trigger-based replication? Just don't. oids are intended to be used by the database server itself rather than as part of the user-defined data. If you're looking for a auto-generated integer to use as a primary key, use SERIAL. You'd have to specify your table WITH OIDS anyway as they're no longer used by default for table rows, so there's really nothing to be gained by using oids. Michael Glaesemann grzm seespotcode net
On 8/20/07, Michael Glaesemann <grzm@seespotcode.net> wrote: > > On Aug 20, 2007, at 16:58 , Ed L. wrote: > > > What are the concerns with using oid as the column for a primary > > key declaration for use in trigger-based replication? > > Just don't. oids are intended to be used by the database server > itself rather than as part of the user-defined data. If you're > looking for a auto-generated integer to use as a primary key, use > SERIAL. You'd have to specify your table WITH OIDS anyway as they're > no longer used by default for table rows, so there's really nothing > to be gained by using oids. And they wrap around, so there's a chance of collision with oids.
Michael Glaesemann wrote:
>
> On Aug 20, 2007, at 16:58 , Ed L. wrote:
> You'd have to specify your table WITH OIDS anyway as they're no longer used by
> default for table rows, so there's really nothing to be gained by using
> oids.
How exactly can you get rid of OIDs when using a language like PHP? The
"magic" of SERIAL and BIGSERIAL is that they are supposed to be like
MySQL's AUTO INCREMENT feature and they create their own SEQUENCE for
you automatially to handle the serialization. Yet, I can't get the
value of the serial column without knowing the name of the serial sequence.
Using a brain-dead sample table that looks like this:
CREATE table some_table (
col0 SERIAL,
col1 VARCHAR,
col2 VARCHAR
);
I want to do something like this:
INSERT INTO some_table (col1, col2)
VALUES ('val1', 'val2');
I want the value of col0 returned to the application and I don't want to
know the name of the sequence involved in the SERIAL column. I just
want the value inserted into the column by using just it's column name.
In PHP with PDO, I've only been able to get this by first finding the
OID value from 'lastInsertId' and then using that OID to run this select:
SELECT $column AS last_inserted_id
FROM $table
WHERE oid = ?
How else could this be done without the round-trip back the db server or
knowing too much about the SERIAL internals that I shouldn't really need
to know?
-- Dante
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
D. Dante Lorenso wrote:
> Michael Glaesemann wrote:
>>
>> On Aug 20, 2007, at 16:58 , Ed L. wrote:
>> You'd have to specify your table WITH OIDS anyway as they're no
>> longer used by default for table rows, so there's really nothing to be
>> gained by using oids.
> Using a brain-dead sample table that looks like this:
>
> CREATE table some_table (
> col0 SERIAL,
> col1 VARCHAR,
> col2 VARCHAR
> );
>
> I want to do something like this:
>
> INSERT INTO some_table (col1, col2)
> VALUES ('val1', 'val2');
>
> I want the value of col0 returned to the application and I don't want to
> know the name of the sequence involved in the SERIAL column. I just
> want the value inserted into the column by using just it's column name.
lastval()
Sincerely,
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGyhwOATb/zqfZUUQRArlgAJ9F0exnHPJmM5r8eSASb1qGIl7DtQCfbZZh
UhYLXWZxr2zKiJYBiJnc4rM=
=z7N/
-----END PGP SIGNATURE-----
On Aug 20, 2007, at 17:51 , D. Dante Lorenso wrote:
> Michael Glaesemann wrote:
>> On Aug 20, 2007, at 16:58 , Ed L. wrote:
>> You'd have to specify your table WITH OIDS anyway as they're no
>> longer used by default for table rows, so there's really nothing
>> to be gained by using oids.
>
> How exactly can you get rid of OIDs when using a language like PHP?
I've never used OIDs when programming in PHP (or any other language,
IIRC)
> The "magic" of SERIAL and BIGSERIAL is that they are supposed to
> be like MySQL's AUTO INCREMENT feature and they create their own
> SEQUENCE for you automatially to handle the serialization.
I don't know the exact history of sequences, but I believe they have
more to do with Oracle and/or the SQL spec than MySQL. But I could be
wrong here.
> Using a brain-dead sample table that looks like this:
>
> CREATE table some_table (
> col0 SERIAL,
> col1 VARCHAR,
> col2 VARCHAR
> );
You can with fair certainty predict the name of the sequence, you can
look it up using the system tables, or use the pg_get_serial_sequence
system information function.
> I want to do something like this:
>
> INSERT INTO some_table (col1, col2)
> VALUES ('val1', 'val2');
INSERT INTO some_table (col1, col2)
VALUES ('val1', 'val2')
RETURNING col0;
> In PHP with PDO, I've only been able to get this by first finding
> the OID value from 'lastInsertId' and then using that OID to run
> this select:
>
> SELECT $column AS last_inserted_id
> FROM $table
> WHERE oid = ?
If you're using an ORM, I'm surprised it doesn't already incorporate
something like pg_get_serial_sequence already.
> How else could this be done without the round-trip back the db
> server or knowing too much about the SERIAL internals that I
> shouldn't really need to know?
I hope I've given you some options here.
Michael Glaesemann
grzm seespotcode net
D. Dante Lorenso wrote: > I want the value of col0 returned to the application and I don't want to > know the name of the sequence involved in the SERIAL column. I just want > the value inserted into the column by using just it's column name. Use pg_get_serial_sequence(). Don't use lastval() if you can help it, because it's a foot gun. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "I love the Postgres community. It's all about doing things _properly_. :-)" (David Garamond)
"D. Dante Lorenso" wrote:
>Using a brain-dead sample table that looks like this:
>
> CREATE table some_table (
> col0 SERIAL,
> col1 VARCHAR,
> col2 VARCHAR
> );
>
>I want to do something like this:
>
> INSERT INTO some_table (col1, col2)
> VALUES ('val1', 'val2');
>
>I want the value of col0 returned to the application and I don't want to
>know the name of the sequence involved in the SERIAL column. I just
>want the value inserted into the column by using just it's column name.
Using 8.2 or above:
INSERT INTO some_table (col1, col2) VALUES ('val1', 'val2') RETURNING col0;
Rainer
Rainer Bauer wrote:
> "D. Dante Lorenso" wrote:
>
>> Using a brain-dead sample table that looks like this:
>>
>> CREATE table some_table (
>> col0 SERIAL,
>> col1 VARCHAR,
>> col2 VARCHAR
>> );
>>
>> I want to do something like this:
>>
>> INSERT INTO some_table (col1, col2)
>> VALUES ('val1', 'val2');
>>
>> I want the value of col0 returned to the application and I don't want to
>> know the name of the sequence involved in the SERIAL column. I just
>> want the value inserted into the column by using just it's column name.
>
> Using 8.2 or above:
> INSERT INTO some_table (col1, col2) VALUES ('val1', 'val2') RETURNING col0;
Oh ... VERY NICE ;-) This is even BETTER than just returning the value
of the SERIAL column since you can return any column even if there are
more than one SERIAL columns in a table! No need for OID, no need for
LASTVAL() ...
I see this from the documentation of 8.2:
---------- 8< -------------------- 8< ----------
The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily useful
for obtaining values that were supplied by defaults, such as a serial
sequence number. However, any expression using the table's columns is
allowed. The syntax of the RETURNING list is identical to that of the
output list of SELECT.
---------- 8< -------------------- 8< ----------
Exactly what I was looking for. Looks like I need to make moves to get
from 8.1 onto 8.2 ;-)
Thanks, Rainer!
-- Dante
On Mon, Aug 20, 2007 at 07:00:32PM -0500, D. Dante Lorenso wrote:
> Exactly what I was looking for. Looks like I need to make moves to get
> from 8.1 onto 8.2 ;-)
in any pg you should simply use select currval('sequence_name'); and be
happy with it.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)