Horton
Menu

Re: Avoid orphaned objects dependencies, take 3

Ashutosh Sharma <ashu.coek88@gmail.com>

View thread
From: Ashutosh Sharma <ashu.coek88@gmail.com>
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Michael Paquier <michael@paquier.xyz>, Alexander Lakhin <exclusion@gmail.com>, pgsql-hackers@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2024-06-19T11:49:28Z
Lists: pgsql-hackers 
Hi,

If the dependency is more, this can hit max_locks_per_transaction
limit very fast. Won't it? I just tried this little experiment with
and without patch.

1) created some UDTs (I have just chosen some random number, 15)
do $$
declare
    i int := 1;
    type_name text;
begin
    while i <= 15 loop
        type_name := format('ct_%s', i);

        -- check if the type already exists
        if not exists (
            select 1
            from pg_type
            where typname = type_name
        ) then
            execute format('create type %I as (f1 INT, f2 TEXT);', type_name);
        end if;

        i := i + 1;
    end loop;
end $$;

2) started a transaction and tried creating a table that uses all udts
created above:
begin;
create table dep_tab(a ct_1, b ct_2, c ct_3, d ct_4, e ct_5, f ct_6, g
ct_7, h ct_8, i ct_9, j ct_10, k ct_11, l ct_12, m ct_13, n ct_14, o
ct_15);

3) checked the pg_locks entries inside the transaction both with and
without patch:

-- with patch:
select count(*) from pg_locks;
 count
-------
    23
(1 row)

-- without patch:
select count(*) from pg_locks;
 count
-------
     7
(1 row)

With patch, it increased by 3 times. Won't that create a problem if
many concurrent sessions engage in similar activity?

--
With Regards,
Ashutosh Sharma.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Avoid orphaned objects dependencies

  2. Don't try to record dependency on a dropped column's datatype