How to drop a subscription inside a stored procedure?
I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. For that, I essentially want to drop and re-create the subscription. In order to be able to do that, the tables need to be empty. So the approach is: Run a query to get all replicated tables, store this in an array. Run "drop subscription ..." Truncate all tables Run "create subscription ..." However, the "drop subscription" part results in this error: ERROR: DROP SUBSCRIPTION cannot be executed from a function CONTEXT: SQL statement "drop subscription if exists my_replication" I first thought that the initial SELECT to fetch all replicated tables, starts an implicit transaction, so I removed everything else from the procedure, including the dynamic SQL. But even this very simple implementation: create or replace procedure drop_subscription() as $$ begin drop subscription if exists test_subscription; end; $$ security definer language plpgsql; fails with that error. Is there any way, I can provide a stored procedure to do this? Regards Thomas
Re: How to drop a subscription inside a stored procedure?
On 6/10/22 05:57, Thomas Kellerer wrote: I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. However, the "drop subscription" part results in this error: ERROR: DROP SUBSCRIPTION cannot be executed from a function CONTEXT: SQL statement "drop subscription if exists my_replication" I first thought that the initial SELECT to fetch all replicated tables, starts an implicit transaction, so I removed everything else from the procedure, including the dynamic SQL. But even this very simple implementation: create or replace procedure drop_subscription() as $$ begin drop subscription if exists test_subscription; end; $$ security definer language plpgsql; fails with that error. Is there any way, I can provide a stored procedure to do this? From the docs: https://www.postgresql.org/docs/current/sql-dropsubscription.html "DROP SUBSCRIPTION cannot be executed inside a transaction block if the subscription is associated with a replication slot. (You can use ALTER SUBSCRIPTION to unset the slot.)" I have not tested but you might try the ALTER SUBSCRIPTION first, though note the caveats here: https://www.postgresql.org/docs/current/sql-altersubscription.html Regards Thomas -- Adrian Klaver adrian.kla...@aklaver.com
How to get response message
Hi All, I am want to delete old records using function so my senior has function like below but I want to get response of this particular inside query wheter it is successful or failure How to get response of the function status create or replace function data_purge() returns void as$$ Declare Begin Drop table test_old; Create table test_old as select * from sales where bill_date
Re: How to get response message
On 6/10/22 09:37, Rama Krishnan wrote: Hi All, I am want to delete old records using function so my senior has function like below but I want to get response of this particular inside query wheter it is successful or failure How to get response of the function status create or replace function data_purge() returns void as$$ Declare Begin Drop table test_old; This should probably be: Drop table IF EXISTS test_old; Just in case the table has already been dropped. Create table test_old as select * from sales where bill_date-interval '1 year'; Delete table sales where sales_id in (select sales_id from test_old; Delete from sales where sales_id in (select sales_id from test_old); See DELETE for more information: https://www.postgresql.org/docs/current/sql-delete.html As to getting execution information see: https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS End; $$ language plpgsql; -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to get response message
On Fri, 10 Jun 2022 at 18:38, Rama Krishnan wrote: > > Hi All, > > I am want to delete old records using function so my senior has function like > below but I want to get response of this particular inside query wheter it is > successful or failure > How to get response of the function status > Drop table test_old; > Create table test_old as select * from sales where bill_date '1 year'; > Delete table sales where sales_id in (select sales_id from test_old; I do a similar thing routinely and use a "move", ( insert into archive delete from live where yadayada returning whatever ). I suppose you could do a simiar trick. drop table test_old; -- Beware of this, it makes your functiondangerous, if you execute it twice you loose data. create table test_old as delete from sales where bill_date
Re: How to get response message
On Fri, Jun 10, 2022 at 9:38 AM Rama Krishnan wrote: > I am want to delete old records using function so my senior has function > like below but I want to get response of this particular inside query > wheter it is successful or failure > If it doesn't error, it was successful. That is basically the API for a void returning function. > > How to get response of the function status > > create or replace function data_purge() returns void as$$ > Declare > Begin > Drop table test_old; > Create table test_old as select * from sales where bill_date -interval '1 year'; > > Delete table sales where sales_id in (select sales_id from test_old; > > Unless you are inspecting test_old outside the function you should just get rid of the table altogether. Delete has a USING clause, I'd suggest that, with the sales subquery, instead of using IN. If you want to return a useful count I'd move the delete into a CTE, add RETURNING, count(*) the results, and return that (changing the function output to either integer, text, or json as you desire). David J.
Need optimization in query
Hi Team.
*I have a use case to get the result as follows:*
1. asin_ymm is never null.
2. If there is more than 1 entry for an asin_ymm with both null and
non-null submodelId, I should return rows with non-null submodelId only,
otherwise if there is no submodelid present for a asin_ymm, then return
that row with null submodelid.
3. Also if submodelid is null , assuming fitment_key would always be null
in the table.
4. Using that resultset, If there is more than 1 entry for an (asin_ymm
,SubmodelID) with both null and non-null fitment_key, I should return rows
with non-null fitment_key only, otherwise if there is no fitment_key
present for a (asin_ymm,submodelId), then return that row with null
fitment_key.
5. Using that resultset, i need to return those rows having maximum values
in fitment_key(ie. for eg.(out of these two 'A', 'D','1--3-4' and A',
'D','1-2-3-4-7', i should return row having A', 'D','1-2-3-4-7)
create table fitment_records(asin_ymm varchar(50), SubmodelID varchar(50),
fitment_key varchar(50));
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B',
null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key ) values('C',
null,null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
'D','1--3-4');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B',
'E','2-3-4-5');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('B',
'E', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
'F','2-3');
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
'E', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
'D', null);
insert into fitment_records(asin_ymm, SubmodelID,fitment_key) values('A',
'D','1-2-3-4-7');
output should be:
asin_ymm | submodelid | fitment_key
--++-
A| D | 1-2-3-4-7
A| E | null
A| F | 2-3
B| E | 2-3-4-5
C| null | null
Currently i have written these queries for this usecase. Can we
optimise it further? Considering data is in millions
create temporary view tv1 as (SELECT *
FROM fitment_records fr_1
WHERE fitment_key IS NOT NULL OR
(fitment_key IS NULL AND
NOT EXISTS (SELECT 1 FROM fitment_records fr_2
WHERE fr_2.asin_ymm = fr_1.asin_ymm AND
fr_2.SubmodelID = fr_1.SubmodelID and
fr_2.fitment_key IS NOT NULL)));
create temporary view tv2 as (select *
FROM tv1 fr_1
WHERE SubmodelID IS NOT NULL OR
(SubmodelID IS NULL AND
NOT EXISTS (SELECT 1 FROM fitment_records fr_2
WHERE fr_2.asin_ymm = fr_1.asin_ymm AND
fr_2.SubmodelID IS NOT NULL) ));
create temporary view fitment_records_with_fitment_key_size as (
select asin_ymm, SubmodelID, fitment_key,
Array_Length(string_to_array(fitment_key, '-'),1) as fitment_key_size
from tv2
where SubmodelID is not null
and fitment_key is not null
group by asin_ymm, SubmodelID, fitment_key
);
create temporary view fitment_records_with_fitment_key_max_size as (
select asin_ymm, SubmodelID, max(fitment_key_size) as max_fitment_key_size
from fitment_records_with_fitment_key_size
group by asin_ymm, SubmodelID
);
select * from tv2
except
select f2.*
from fitment_records_with_fitment_key_size frws,
fitment_records_with_fitment_key_max_size frwms,
tv2 f2
where frws.asin_ymm = frwms.asin_ymm
AND frws.SubmodelID = frwms.SubmodelID
AND frws.fitment_key_size < frwms.max_fitment_key_size
AND frws.SubmodelID = f2.SubmodelID
AND frws.asin_ymm = f2.asin_ymm
AND frws.fitment_key = f2.fitment_key;
Thanks & Regards
Re: How to drop a subscription inside a stored procedure?
Adrian Klaver schrieb am 10.06.2022 um 16:58: On 6/10/22 05:57, Thomas Kellerer wrote: I am trying to write a stored procedure (Postgres 13) to enable non-superusers to re-create a subscription. However, the "drop subscription" part results in this error: ERROR: DROP SUBSCRIPTION cannot be executed from a function CONTEXT: SQL statement "drop subscription if exists my_replication" From the docs: https://www.postgresql.org/docs/current/sql-dropsubscription.html "DROP SUBSCRIPTION cannot be executed inside a transaction block if the subscription is associated with a replication slot. (You can use ALTER SUBSCRIPTION to unset the slot.)" I have not tested but you might try the ALTER SUBSCRIPTION first, though note the caveats here: https://www.postgresql.org/docs/current/sql-altersubscription.html Unsetting the slot works (after disabling the subscription), but then I have an "orphaned" slot on the publisher. I don't see a way how I could get rid of that replication slot from the subscriber side.
