Sequence Dependency
Hi,
I did not find any relation after reading relevant documents so I need to
ask you:
When we create a table like this;
Method - 1
CREATE TABLE fruits(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
it automatically creates a sequence and for that sequence it also creates
an entry in the pg_depend table with value "a" on the "deptype" column. For
this kind of setup we can easily use following query(
https://dba.stackexchange.com/questions/260975/postgresql-how-can-i-list-the-tables-to-which-a-sequence-belongs)
to find sequence and related table pair:
SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S';
On the other hand, if we create table with sequence like this:
Method - 2
CREATE SEQUENCE public.actor_actor_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE public.actor (
actor_id integer DEFAULT
nextval('public.actor_actor_id_seq'::regclass) NOT NULL,
first_name text NOT NULL,
last_name text NOT NULL,
last_update timestamp with time zone DEFAULT now() NOT NULL
);
it does not create any pg_depend entry for this sequence and table
pair. So, it is not possible to track down to find the pairs. Is there
any other way to find the sequence and the table pairs created using
method 2?
Thanks!
Re: Sequence Dependency
On Saturday, June 10, 2023, Umut TEKİN wrote: > > it does not create any pg_depend entry for this sequence and table pair. So, > it is not possible to track down to find the pairs. Is there any other way to > find the sequence and the table pairs created using method 2? > > Parse the defaults and check for matching sequence names? You can alter a manually created sequence to be owned by a table and thus get the dependency added even in the second case. David J.
Re: Sequence Dependency
"David G. Johnston" writes: > On Saturday, June 10, 2023, Umut TEKİN wrote: >> it does not create any pg_depend entry for this sequence and table pair. So, >> it is not possible to track down to find the pairs. Is there any other way >> to find the sequence and the table pairs created using method 2? > You can alter a manually created sequence to be owned by a table and thus > get the dependency added even in the second case. Yeah, that would be the way to match what SERIAL does (see [1]). In the quoted example, there is a dependency from the column's default expression to the sequence, so you could still detect the connection without the ownership dependency; it's just harder. You have regression=# select pg_describe_object(classid,objid,objsubid) as obj, pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype from pg_depend where objid >= 'public.actor_actor_id_seq'::regclass order by objid, refobjid; obj | ref | deptype -+-+- sequence actor_actor_id_seq | schema public | n table actor | schema public | n type actor[]| type actor | i type actor | table actor | i default value for column actor_id of table actor| sequence actor_actor_id_seq | n default value for column actor_id of table actor| column actor_id of table actor | a default value for column last_update of table actor | column last_update of table actor | a toast table pg_toast.pg_toast_89174 | table actor | i index pg_toast.pg_toast_89174_index | column chunk_id of toast table pg_toast.pg_toast_89174 | a index pg_toast.pg_toast_89174_index | column chunk_seq of toast table pg_toast.pg_toast_89174 | a (10 rows) versus obj | ref | deptype -+-+- sequence fruits_id_seq | schema public | n sequence fruits_id_seq | column id of table fruits | a table fruits| schema public | n type fruits[] | type fruits | i type fruits | table fruits | i default value for column id of table fruits | sequence fruits_id_seq | n default value for column id of table fruits | column id of table fruits | a toast table pg_toast.pg_toast_89182 | table fruits | i index pg_toast.pg_toast_89182_index | column chunk_seq of toast table pg_toast.pg_toast_89182 | a index pg_toast.pg_toast_89182_index | column chunk_id of toast table pg_toast.pg_toast_89182 | a index fruits_pkey | constraint fruits_pkey on table fruits | i constraint fruits_pkey on table fruits | column id of table fruits | a (12 rows) regards, tom lane [1] https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
Further clarification in documentation: No deletion of unreferenced large objects
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-binary.html Description: When deleting a row that references (contains) a large object, I think that most users expect the DBMS to take care of the, now unreferenced, BLOB. It's good to know that PostgreSQL handles this differently and that one must periodically use vacuumlo to avoid BLOB data piling up. At least, that's what I understood. I think a small paragraph in the documentation about this would help many people. I can help with writing, if needed. Kind regards Oliver Marienfeld
Re: Further clarification in documentation: No deletion of unreferenced large objects
> On 12/06/2023 15:48 CEST PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/datatype-binary.html > Description: > > When deleting a row that references (contains) a large object, I think that > most users expect the DBMS to take care of the, now unreferenced, BLOB. It's > good to know that PostgreSQL handles this differently and that one must > periodically use vacuumlo to avoid BLOB data piling up. At least, that's > what I understood. > > I think a small paragraph in the documentation about this would help many > people. I can help with writing, if needed. bytea and large objects are two different ways of storing binary data. vacuumlo does not apply to bytea. Extension lo also mentions the issue with orphan objects already: https://www.postgresql.org/docs/15/lo.html Some further info on bytea vs large objects: https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/ -- Erik
Re: Further clarification in documentation: No deletion of unreferenced large objects
On Mon, Jun 12, 2023 at 8:32 AM PG Doc comments form wrote: You may be onto something, but: > > Page: https://www.postgresql.org/docs/15/datatype-binary.html This page isn't relevant to the discussion at hand as it doesn't have anything to do with large objects. Whether it should would be a different complaint. > When deleting a row that references (contains) a large object, I think that > most users expect the DBMS to take care of the, now unreferenced, BLOB. On what grounds? To me this looks just like any other foreign key situation and removing FK rows does not impact the PK. What would lead one to think large objects behave differently? David J.
Re: Further clarification in documentation: No deletion of unreferenced large objects
On Mon, 2023-06-12 at 13:48 +, PG Doc comments form wrote: > Page: https://www.postgresql.org/docs/15/datatype-binary.html > > When deleting a row that references (contains) a large object, I think that > most users expect the DBMS to take care of the, now unreferenced, BLOB. It's > good to know that PostgreSQL handles this differently and that one must > periodically use vacuumlo to avoid BLOB data piling up. At least, that's > what I understood. > > I think a small paragraph in the documentation about this would help many > people. I can help with writing, if needed. This is documented here: https://www.postgresql.org/docs/current/lo.html Admittedly, that is not the best place. Perhaps the introductory chapter in https://www.postgresql.org/docs/current/lo-intro.html would be a good place to mention that there is no referential integrity to large objects. Yours, Laurenz Albe
