pgadmin-org / pgadmin-org/pgadmin4
SchemaDiffTool: schema sync script error when changing output data type or OUT argument list in function
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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