PostgreSQL Bugs - pgsql8.1: About COPY Command and system clomn oid

Author

pgsql8.1: About COPY Command and system clomn oid

wangshj

2005-12-09, 3:24 am

The following is my test.

oidtest=# CREATE TABLE oidtest( name text ) WITH OIDS;
CREATE TABLE
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name1' ) ;
INSERT 54512 1
oidtest=# COPY oidtest (name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.[color=darkred]
oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name2' ) ;
INSERT 54513 1
oidtest=# SELECT oid,name from oidtest ;
oid | name
-------+----------
54512 | name1
54513 | copyname
54513 | name2
(3 rows)

oidtest=#
Then I got two records with same oid(54513).

In the second test, I created two tables and added primary key (oid) to
them. As below:

oidtest=# CREATE TABLE oidtestpk (name text, PRIMARY KEY (oid) ) WITH OIDS ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"oidtestpk_pkey" for table "oidtestpk"
CREATE TABLE
oidtest=# CREATE TABLE oidtestpk_another (name text, PRIMARY KEY (oid) )
WITH OIDS ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
" oidtestpk_another_pk
ey" for table "oidtestpk_another"
CREATE TABLE
oidtest=# INSERT INTO oidtestpk(name) VALUES ('name0');
INSERT 54562 1
oidtest=# COPY oidtestpk(name) WITH OIDS FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.[color=darkred]
oidtest=# INSERT INTO oidtestpk_another(na
me) VALUES ('name63');
INSERT 54563 1
oidtest=# INSERT INTO oidtestpk_another(na
me) VALUES ('name64');
INSERT 54564 1
oidtest=# SELECT oid,name FROM oidtestpk where oid=54564;
oid | name
-------+------------
54564 | copyname64
(1 row)

oidtest=# SELECT oid,name FROM oidtestpk_another where oid=54564;
oid | name
-------+--------
54564 | name64
(1 row)

And then I got records with same oid(54564).
Is it a bug?
....

Qingqing Zhou

2005-12-10, 1:24 pm

"wangshj" <wangshj@sduept.com> wrote

>
> oidtest=# COPY oidtest (name) WITH OIDS FROM stdin;
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself.
> oidtest=# INSERT INTO oidtest ( name ) VALUES( 'name2' ) ;
> INSERT 54513 1
> oidtest=# SELECT oid,name from oidtest ;
> oid | name
> -------+----------
> 54512 | name1
> 54513 | copyname
> 54513 | name2
> (3 rows)
>
> oidtest=#
> Then I got two records with same oid(54513).
>

This is not a bug. You can specify any valid number here as Oids. If you
don't want duplicated Oids or strange Oids, then don't specify WITH OIDS
clause.

Regards,
Qingqing

Sponsored Links