Thread: Primary key data type: integer vs identity
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 11:14 AM, Rich Shepard wrote:
> On Fri, 19 Apr 2019, Adrian Klaver wrote:
>
>> If it is working for you now I see no reason to switch.
>
> Adrian,
>
> It does work. I just learned about the SQL identity type and want to learn
> when it's most appropriate to use. The databases I develop all work with
> integers as primary keys and reading about the type didn't clarify (for me)
> when it should be used.
Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.
It also sounds like it has advantages in terms of tying your sequence directly to the column. If you drop a serial column, it doesn't drop the sequence.
Once I've upgraded to 10+, I might look at converting my existing serial columns. Peter Eisentraut wrote a good piece(1) on identity columns, including a function for converting existing serial columns. I've copied the function below, but had two questions about it:
1) Would the function as written also reassign ownership to that table column? (I see the update to pg_depend and pg_attribute, but don't know enough about them to know if that changes ownership)
2) Would one have to be a superuser to do this?
Thanks,
Ken
CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
colnum smallint;
seqid oid;
count int;
BEGIN
-- find column number
SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;
IF NOT FOUND THEN
RAISE EXCEPTION 'column does not exist';
END IF;
-- find sequence
SELECT INTO seqid objid
FROM pg_depend
WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
AND classid = 'pg_class'::regclass AND objsubid = 0
AND deptype = 'a';
GET DIAGNOSTICS count = ROW_COUNT;
IF count < 1 THEN
RAISE EXCEPTION 'no linked sequence found';
ELSIF count > 1 THEN
RAISE EXCEPTION 'more than one linked sequence found';
END IF;
-- drop the default
EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';
-- change the dependency between column and sequence to internal
UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';
-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
END;
$$;
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Apr 19, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 4/19/19 11:14 AM, Rich Shepard wrote:
> > On Fri, 19 Apr 2019, Adrian Klaver wrote:
> >
> >> If it is working for you now I see no reason to switch.
> >
> > Adrian,
> >
> > It does work. I just learned about the SQL identity type and want
> to learn
> > when it's most appropriate to use. The databases I develop all
> work with
> > integers as primary keys and reading about the type didn't
> clarify (for me)
> > when it should be used.
>
> Mainly for folks that want cross database SQL compliance. It is not a
> type so much as a way of specifying an auto-increment column.
>
>
> It also sounds like it has advantages in terms of tying your sequence
> directly to the column. If you drop a serial column, it doesn't drop
> the sequence.
A serial column will:
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 12:35 PM, Ken Tanzer wrote:
>
> Thanks Adrian. You are as usual correct. (I had a bunch of tables
> created by a function that I assumed were serial, but were not.)
> Identity columns still seem tidier and more manageable. Can you tell if
> the function I referenced would change the ownership or not?
I believe in 'when it doubt try it, whats the worst that can happen?:)':
I agree, and if I had a copy of 10+ running, I probably would have! :)
<NOTE> I needed to be a superuser to run due to this:
ERROR: permission denied for table pg_depend
CONTEXT: SQL statement "UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
SQL statement
test=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)
test=# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------
(1 row)
test=# \d serial_test
Table "public.serial_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
fld_1 | text | | |
test=# \dp+ serial_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
public | serial_test | table | | |
(1 row)
Maybe I'm missing it, but I'm not really sure what that is supposed to be telling me about the ownership of the sequence.
The scenario I'm wondering about is:
Table A owned by User 1, and has column created as serial
The created sequence is altered to be owned by User 2 (with User 1 granted select & update)
upgrade_serial_to_identity applied to Table A
At that point, who owns the sequence?
I can wait until I've got 10+ running and try it myself, but I thought maybe someone would know the answer and be willing to share.
Thanks!
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 1:02 PM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 4/19/19 12:35 PM, Ken Tanzer wrote:
>
> >
> > Thanks Adrian. You are as usual correct. (I had a bunch of tables
> > created by a function that I assumed were serial, but were not.)
> > Identity columns still seem tidier and more manageable. Can you
> tell if
> > the function I referenced would change the ownership or not?
>
> I believe in 'when it doubt try it, whats the worst that can happen?:)':
>
>
> I agree, and if I had a copy of 10+ running, I probably would have! :)
>
> <NOTE> I needed to be a superuser to run due to this:
> ERROR: permission denied for table pg_depend
> CONTEXT: SQL statement "UPDATE pg_depend
> SET deptype = 'i'
> WHERE (classid, objid, objsubid) = ('pg_class'::regclass,
> seqid, 0)
> AND deptype = 'a'"
> PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
> SQL statement
>
>
> test=# create table serial_test(id serial, fld_1 text);
> CREATE TABLE
> test=# \dp serial_test
> Access privileges
> Schema | Name | Type | Access privileges | Column
> privileges |
> Policies
> --------+-------------+-------+-------------------+-------------------+----------
> public | serial_test | table | |
> |
> (1 row)
>
> test=# select upgrade_serial_to_identity('serial_test', 'id');
> upgrade_serial_to_identity
> ----------------------------
>
> (1 row)
>
> test=# \d serial_test
> Table "public.serial_test"
> Column | Type | Collation | Nullable | Default
>
> --------+---------+-----------+----------+----------------------------------
> id | integer | | not null | generated by default as
> identity
> fld_1 | text | | |
>
>
> test=# \dp+ serial_test
> Access privileges
> Schema | Name | Type | Access privileges | Column
> privileges |
> Policies
> --------+-------------+-------+-------------------+-------------------+----------
> public | serial_test | table | |
> |
> (1 row)
>
>
> Maybe I'm missing it, but I'm not really sure what that is supposed to
> be telling me about the ownership of the sequence.
>
> The scenario I'm wondering about is:
>
> Table A owned by User 1, and has column created as serial
> The created sequence is altered to be owned by User 2 (with User 1
> granted select & update)
> upgrade_serial_to_identity applied to Table A
> At that point, who owns the sequence?
>
> I can wait until I've got 10+ running and try it myself, but I thought
> maybe someone would know the answer and be willing to share.
select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.3.1 20180323 [gcc-7-branch revision 258812], 64-bit
select session_user, current_user;
session_user | current_user
--------------+--------------
aklaver | aklaver
create table serial_test(id serial, fld_1 text);
CREATE TABLE
\d
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklaver
test_(aklaver)> \c - postgres
You are now connected to database "test" as user "postgres".
test_(postgres)# select session_user, current_user;
session_user | current_user
--------------+--------------
postgres | postgres
(1 row)
test_(postgres)# select upgrade_serial_to_identity('serial_test', 'id');
upgrade_serial_to_identity
----------------------------
(1 row)
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | serial_test | table | aklaver
public | serial_test_id_seq | sequence | aklaver
The function is working directly on the system catalogs and I do not
anything that changes ownership:
UPDATE pg_depend
SET deptype = 'i'
WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
AND deptype = 'a';
-- mark the column as identity column
UPDATE pg_attribute
SET attidentity = 'd'
WHERE attrelid = tbl
AND attname = col;
Thanks, though I don't see what this shows, since there were not separate users involved. So I loaded up a copy of 11.2, and discovered that you actually can't change the ownership of a sequence created by serial.
ag_tz_test=# ALTER SEQUENCE t_serial_id_seq OWNER TO develop;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".
I also missed the part in the article where it talks about assigning ownership to the column. In psql, it's easy to miss because a \ds (or \ds+) (as opposed to a \d) shows the user that owns the sequence, not a column:
test=# \ds+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------+----------+-------+------------+-------------
public | t_serial_id_seq | sequence | u1 | 8192 bytes |
Whereas if you look at \d it shows the column:
test=# \d+ t_serial_id_seq
Sequence "public.t_serial_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.t_serial.id
(Side note: it is surprising that the Size and Description don't show up with \d+. I always thought that a \d+ was the best way to get all the detail on an object.)
But even if you drop the default on the column, it doesn't seem like you can change the sequence's owner:
test=# ALTER TABLE t_serial ALTER COLUMN id DROP DEFAULT;
ALTER TABLE
test=# alter sequence t_serial_id_seq OWNER TO u2;
ERROR: cannot change owner of sequence "t_serial_id_seq"
DETAIL: Sequence "t_serial_id_seq" is linked to table "t_serial".
Although you can drop it:
DROP SEQUENCE t_serial_id_seq;
DROP SEQUENCE
Anyhoo, I've learned a bit more today, and thanks for your help!
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.