Re: Not able to purge partition

[sud]

On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe 
wrote:

>
> > [create some partitions, then drop a partition of the referenced table]
> >
>
> > SQL Error [P0001]: ERROR: cannot drop table
> schema1.test_part_drop_parent_p2024_02_01 because other objects depend on it
> > CONTEXT: SQL statement "DROP TABLE
> schema1.test_part_drop_parent_p2024_02_01"
>
> That's normal.  If you create a foreign key constraint to a partitioned
> table, you
> can no longer drop a partition of the referenced table.
>
> What you *can* do is detach the partition and then drop it, but detatching
> will

be slow because PostgreSQL has to check for referencing rows.
>


*The best solution is to create the foreign key *not* between the
partitioned*
*tables, but between the individual table partitions.  *

Interesting, even my thought was that the detach+drop parent partition will
only look into the specific child partition but not the whole child table.

However, out of curiosity, does this default foreign key setup i.e. foreign
keys between the table (but not between the partitions) also make the data
load into the child partitions slower ( as it must be then looking and
validating the presence of the keys across all the partitions of the parent
table)?

Timestamp conversion Error in dynamic sql script

[sud]

Hello ,
I am trying to create a block which will create a few partitions
dynamically and also insert ~1million rows into each of those partitions.
Not able to figure out why it's giving below error during timezone
conversion while defining the partitions even though I used the typecast?

CREATE TABLE parent_table (
id Numeric,
col1 TEXT,
col2 TEXT,
partition_key TIMESTAMP,
primary key (partition_key, id)
)
PARTITION BY RANGE (partition_key);

**

DO $$
DECLARE
start_date TIMESTAMP := '2022-01-01';
begin
FOR i IN 0..10 LOOP

EXECUTE format('
CREATE TABLE parent_table_%s (
CHECK (partition_key >= DATE ''%s'' AND partition_key < DATE ''%s''
)
) INHERITS (parent_table);',
TO_CHAR(start_date + i, '_MM_DD'),
TO_CHAR(start_date + i, '-MM-DD')::timestamp ,
TO_CHAR(start_date + i + INTERVAL '1 day', '-MM-DD')::timestamp
);
EXECUTE format('
ALTER TABLE parent_table ATTACH PARTITION parent_table_%s
FOR VALUES FROM (''%s'') TO (''%s'');',
TO_CHAR(start_date + i, '_MM_DD'),
TO_CHAR(start_date + i, '-MM-DD')::timestamp,
TO_CHAR(start_date + i + INTERVAL '1 day', '-MM-DD') ::timestamp
);

END LOOP;

  FOR i IN 0..10 LOOP
EXECUTE format('
INSERT INTO parent_table_%s (id,col1, col2,  partition_key)
SELECT
generate_series(1, 100),
md5(random()::text),
md5(random()::text),
TIMESTAMP ''%s'' + INTERVAL ''%s days''
FROM generate_series(1, 100);',
TO_CHAR(start_date + i, '_MM_DD'),
start_date,
i
);
END LOOP;
END $$;

***




*SQL Error [42883]: ERROR: operator does not exist: timestamp without time
zone + integerHint: No operator matches the given name and argument types.
You might need to add explicit type casts.Where: PL/pgSQL function
inline_code_block line 7 at EXECUTEError position:*