Re: Where to store Blobs?

[Jamesie Pic]

Storing files in the database has already been demonstrated as not an
efficient trade-off [0]. As such, this post provides a subjective story
about files in the database, rather than an objective compare.

So, I have this ridiculous situation where dumping a database with 12k
user-submitted forms with files, made by 3000 users, which I hope we can
agree “is absolutely nothing”, yet, it eats 35G of PG data.

As a result, dumping the database takes already 32 minutes and is extremely
write-intensive, causing more than 5 second disk backlogs during the
operation at the same time.

If I had stored file paths, like I should have, the db would take a few MBs
and the dump a few seconds even less than one second ?

Also, the backup could just read the file tree from the file system and
synchronize only new files: we don’t have programmatic modifications of
written files for legal reasons: which makes it even more ridiculous to
dump them every time we do a backup.

As such, my biggest regret is to have listened to the manager who imposed
this design decision, but I trusted him at the time I was a fresh hire.

Today, we have the choice of two poisons for deployment:

not shutting down the service during the backup, to save 32 minutes of
downtime, but that’s 32 minutes of writes that are at risk in the case of a
faulty destructive operation, yes that doesn’t happen 99% of the time,
shutting down the service during the backup, as we should, but that
means 32 minutes of extra downtime

In my experience, storing files in the database is a critical mistake. I
mean, if you know what you’re doing maybe, and I thought the said manager
would know what he’s doing.

But at the end of a year the manager decided to ignore all monitoring
alarms concerning disk space that were raised during the backup. As such, I
kept going and freeing as much space as possible when a backup failed to
happen, which hopefully canceled the full deployment, leaving the service
online, even though with a full disk.

I have raised the warning to the customer for months and but the manager
kept insisting that we close our eyes on it, and kept on doing palliative
fixes when needed.

Of course, piling up palliatives fixes in production eventually created the
situation where the disk was too full to make a backup. The manager that
had installed the server OS had put 3 disks in a RAID1 array with extremely
poor partitioning.

As such, i had to spend a night repartitioning the RAID1, so that the /
would be on 10G instead of 40G. Which kept us going a bit more, but
unfortunnately one week shorter than expected, because I had forgot to
include the exponential growth in the math of the estimation.

Leading to even more incidents. If you store files in the database, which
you shouldn’t unless you’re 100% sure about what you’re doing, then do not
ignore disk space warnings during backups. Or else … well what do you think
happens when an airplane pilot ignores the alarms on their dashboard ?

99% of incidents are a suite of predictable events.

https://habiletechnologies.com/blog/better-saving-files-database-file-system/
https://softwareengineering.stackexchange.com/questions/150669/is-it-a-bad-practice-to-store-large-files-10-mb-in-a-database
https://blog.yourlabs.org/post/184290880553/story-of-a-database-with-files-in-it

Re: PostgreSQL ping/pong to client

[Peter J. Holzer]

On 2019-04-17 18:41:57 +0200, Francisco Olarte wrote:
> On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap  
> wrote:
> > Correction: I meant when my java application dies postgres should break all 
> > the connections that were associated with that peer.
> 
> And how is the server supposed to detect that without keepalives?

Ajay was taliking about an application crashing. If that happens, the OS
(on the application machine) should close all connections the
application had open. So in that case PostgreSQL just gets EOF on any
attempt to read from or write to the socket.

I'm sure this works for any unixoid OS and fairly sure it works for
Windows, too.

If for some reason this doesn't work for Ajay, I suspect that the
problem isn't what he thinks it is and should investigate the cause
further.

> TCP is dessigned to survice for extended period of times without
> traffic, I used that a lot in the dial up times.
> 
> And what makes you think keepalives are impactful and unrealistic? I
> use them a lot, they do not impact my workloads measurably.

Right. But keepalives solve a different problem (detecting loss of
network connectivity or the peer machine being turned off).

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Primary key data type: integer vs identity

[Rich Shepard]

When I created the database I set primary key data types as integer; for
example:

Column|   Type  | Collation | Nullable |  Default
--+---+---+--+-
 org_id   | integer |   | not null |
nextval('organizations_org_id_seq'::regclass)

I probably should have used the serial type, but didn't.

If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.

Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.

Regards,

Rich

Re: Primary key data type: integer vs identity

[Adrian Klaver]

On 4/19/19 10:55 AM, Rich Shepard wrote:

When I created the database I set primary key data types as integer; for
example:

Column    |   Type  | Collation | Nullable |  Default
--+---+---+--+-
  org_id   | integer |   | not null |
nextval('organizations_org_id_seq'::regclass)

I probably should have used the serial type, but didn't.


If you want it to be like serial(assuming the sequences are unique to 
each table) then:


https://www.postgresql.org/docs/11/sql-altersequence.html
"OWNED BY table_name.column_name
OWNED BY NONE

The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) is 
dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence “free-standing”.

"





If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.


If it is working for you now I see no reason to switch.



Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.


IDENTITY is a SQL standard.

The difference:

https://www.postgresql.org/docs/11/sql-createtable.html
"GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

This clause creates the column as an identity column. It will have 
an implicit sequence attached to it and the column in new rows will 
automatically have values from the sequence assigned to it.


The clauses ALWAYS and BY DEFAULT determine how the sequence value 
is given precedence over a user-specified value in an INSERT statement. 
If ALWAYS is specified, a user-specified value is only accepted if the 
INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is 
specified, then the user-specified value takes precedence. See INSERT 
for details. (In the COPY command, user-specified values are always used 
regardless of this setting.)


The optional sequence_options clause can be used to override the 
options of the sequence. See CREATE SEQUENCE for details.

"

Basically the ALWAYS/DEFAULT choices.



Regards,

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Primary key data type: integer vs identity

[Michel Pelletier]

On Fri, Apr 19, 2019 at 10:55 AM Rich Shepard 
wrote:

> When I created the database I set primary key data types as integer; for
> example:
>
> Column|   Type  | Collation | Nullable |  Default
> --+---+---+--+-
>   org_id   | integer |   | not null |
> nextval('organizations_org_id_seq'::regclass)
>
> I probably should have used the serial type, but didn't.
>
> If it would be advisable for me to convert from integer to identity please
> point me to the appropriate work flow.
>
>
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types, but
merely a notational convenience for creating unique identifier columns"

so really there's no difference, what you have is fine.

-Michel



Reading the CREATE TABLE pages in the manual did not give me sufficient
> insights to appreciate the differences or indicate how to change the
> column's data type.
>
> Regards,
>
> Rich
>
>
>

Re: Primary key data type: integer vs identity

[Rich Shepard]

On Fri, 19 Apr 2019, Michel Pelletier wrote:


https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
"The data types smallserial, serial and bigserial are not true types, but
merely a notational convenience for creating unique identifier columns"
so really there's no difference, what you have is fine.


Michel,

That's what I thought. And confirmation is always valuable.

Regards,

Rich

Re: Primary key data type: integer vs identity

[Rich Shepard]

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.

Regards,

Rich

Re: Primary key data type: integer vs identity

[Adrian Klaver]

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.




Regards,

Rich






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Primary key data type: integer vs identity

[Ken Tanzer]

On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
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

(1)  https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

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
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

[Rich Shepard]

On Fri, 19 Apr 2019, Adrian Klaver wrote:


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.


Thank you, Adrian. I saw that it was a column specification and your
explanation of its application is really helpful.

Carpe weekend,

Rich

Re: Primary key data type: integer vs identity

[Adrian Klaver]

On 4/19/19 11:32 AM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
mailto:adrian.kla...@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:

test=> create table serial_test(id serial);
CREATE TABLE
test=> \d serial_test
Table "public.serial_test"
 Column |  Type   | Collation | Nullable | Default 


+-+---+--+-
 id | integer |   | not null | 
nextval('serial_test_id_seq'::regclass)


test=> select * from serial_test_id_seq ;
 last_value | log_cnt | is_called
+-+---
  1 |   0 | f
(1 row)

test=> drop table serial_test ;
DROP TABLE 



test=> select * from serial_test_id_seq ; 



ERROR:  relation "serial_test_id_seq" does not exist 



LINE 1: select * from serial_test_id_seq ;

If you just use a sequence as a default value it will not  unless you 
make it OWNED by the table per the link I posted upstream.



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




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Primary key data type: integer vs identity

[Adrian Klaver]

On 4/19/19 11:32 AM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
mailto:adrian.kla...@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.


Misread your post the first time still:

 create table serial_test(id serial, fld_1 text); 



CREATE TABLE 



test=> \d serial_test 



Table "public.serial_test" 



 Column |  Type   | Collation | Nullable | Default 



+-+---+--+- 



 id | integer |   | not null | 
nextval('serial_test_id_seq'::regclass) 

 fld_1  | text|   |  | 






test=> alter table serial_test drop column id; 



ALTER TABLE 



test=> \d serial_test 



   Table "public.serial_test" 



 Column | Type | Collation | Nullable | Default 



+--+---+--+- 



 fld_1  | text |   |  | 






test=> select * from serial_test_id_seq ; 


ERROR:  relation "serial_test_id_seq" does not exist
LINE 1: select * from serial_test_id_seq ;

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


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Primary key data type: integer vs identity

[Ken Tanzer]

On Fri, Apr 19, 2019 at 12:02 PM Adrian Klaver 
wrote:

> On 4/19/19 11:32 AM, Ken Tanzer wrote:
> > On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
> > mailto:adrian.kla...@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:
>
>
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?

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

[Adrian Klaver]

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 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)



Cheers,
Ken




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Primary key data type: integer vs identity

[Ken Tanzer]

On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver 
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! :)


>  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
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

[Adrian Klaver]

On 4/19/19 1:02 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver 
mailto:adrian.kla...@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! :)

 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!




Ken


--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn mo

Re: Primary key data type: integer vs identity

[Ken Tanzer]

On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver 
wrote:

> On 4/19/19 1:02 PM, Ken Tanzer wrote:
> > On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
> > mailto:adrian.kla...@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! :)
> >
> >  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_attri

Re: Primary key data type: integer vs identity

[Adrian Klaver]

On 4/19/19 2:31 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver 



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 


Well the table was created by one user and the serial was changed to 
IDENTITY by another.


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".


That is covered here:

https://www.postgresql.org/docs/11/sql-altersequence.html

"OWNED BY table_name.column_name
...
The specified table must have the same owner and be in the same schema 
as the sequence. ..."


Which to me means the opposite is true also.

Though you can:

"Specifying OWNED BY NONE removes any existing association, making 
the sequence “free-standing”.

"



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
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Where to store Blobs?

[Jamesie Pic]

I forgot to mention that my deployments include automated migrations as
often as possible, sometimes destructive for refactoring purpose, as such,
to maintain PostgreSQL on a basic linux box I am:

- for having an automated backup prior in the automated deployment script
that may play destructive migrations,
- against the needless overhead of coupling both binary and relational data
in operations that slows the whole thing down or makes it less reliable

Also got supposedly many new points against, mixed with more detail on the
points briefly exposed in my previous email, going deeper in detail, about
how it fits in the big picture of my personal practice ... and how this has
destabilized my prod for months:
https://blog.yourlabs.org/post/184290880553/storing-hd-photos-in-a-relational-database-recipe

tl;dr
If you store media files in PostgreSQL on a production server, then do take
disk space alarms seriously even if they happen only during backups.
Otherwise I fail to see how to avoid a pattern of recurring incidents,
"manually unblocking automated deployments" (double debt interest cost
because also defeats the purpose of automating deployment), when not
filling up a disk during the nightly backup dump ...

Hope this helps,

Have a great day