pgadmin-org / pgadmin-org/pgadmin4

SchemaDiffTool: schema sync script error when changing output data type or OUT argument list in function

Open
#6,652 10 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Bug
Dominant language
Python
Stars
3.8k
Forks
891
Avg merge
4d 7h
Merged PRs (30d)
8

Description

Changing the output data type or OUT argument list in the source schema function causes the target schema synchronization script to fail. This change is not considered an overload in PostgreSQL and cannot be applied until the function is removed.
Problem reproduction:
Scheme source:

CREATE OR REPLACE FUNCTION class_del(
	iid bigint)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    --source FUNCTION new API 
DECLARE
    action_state character varying DEFAULT 'none';
BEGIN
    ---Code Function
	--************************************************************
	----------------THROW EXCEPTION
	--************************************************************
	IF (action_state <> 'action_allowed') THEN
		RAISE EXCEPTION 'Error message';
	END IF;	
END;
$BODY$;

Scheme target:

CREATE OR REPLACE FUNCTION class_del(
	iid bigint,
	OUT Result INT)
    RETURNS INT
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
    --target FUNCTION old API 
DECLARE
    action_state character varying DEFAULT 'none';
BEGIN
	Result = 0;
    ---Code Function
	--************************************************************
	----------------THROW EXCEPTION
	--************************************************************
	IF (action_state <> 'action_allowed') THEN
		Result = 144; --Set error code
	END IF;	
END;
$BODY$;

PS:
I do not think that this can be separated into a separate topic. I want to draw your attention to the fact that the state of the selection filter does not affect the script generator in any way and has a purely cosmetic (ostentatious) use. If this behavior could be controlled, the severity of the problem would decrease. I have more than 700 functions in the API and I can only edit the full script in third-party editors, since yours just freezes (on my computer). Applying a filter would allow me to step-by-step sync by changing the accepted sync strategy.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SchemaDiffTool script generator and reproduce the failure using the source and target function definitions in the issue, changing the return type or adding an OUT argument. Trace how function differences become synchronization statements; done means the generated script can apply this change without manual removal, and investigate whether the selection filter affects generation as a separate concern.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.