Обсуждение: Bug in CHECK constraints statement reverse engineering.
Hello, PgAdmin 1.3.0 (Apr 24 2005) Wrong CHECK reverse engineering. In PostgreSQL documentation i found: ---------------------------------------------------------------------- 41.10. pg_constraint Note consrc is not updated when referenced objects change; for example, it won't track renaming of columns. Rather than relying on this field, it's best to use pg_get_constraintdef() to extract the definition of a check constraint. ---------------------------------------------------------------------- Here the script to demonstraint this behaviour: -- First create function and constraints on table field and domain CREATE OR REPLACE FUNCTION "Check_IntegerGreaterZero"(int4) RETURNS bool AS $BODY$ select $1 > 0; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; CREATE TABLE "TestTable" ( test int4, CONSTRAINT "CHK_TestTable_Test" CHECK ("Check_IntegerGreaterZero"(test)) ) WITHOUT OIDS; CREATE DOMAIN "TestDomain" AS int4 CONSTRAINT TestDomain_check CHECK "Check_IntegerGreaterZero"(VALUE); -- Then just rename function ALTER FUNCTION "Check_IntegerGreaterZero"(int4) RENAME TO "Check_IGZ"; --Watch pg_constraint and result of pg_get_constraintdef() select conname, consrc, pg_get_constraintdef(pg_constraint.oid) from pg_constraint conname |consrc |pg_get_constraintdef -------------------------------------------------------------------------------------------------------------------------------- cardinal_number_domain_check |(VALUE >= 0) |CHECK ((VALUE>= 0)) CHK_TestTable_Test |"Check_IntegerGreaterZero"(test) |CHECK("Check_IGZ"(test)) TestDomain_check |"Check_IntegerGreaterZero"(VALUE) |CHECK("Check_IGZ"(VALUE)) I suppose that PgAdmin shows values from consrc. Another feature request. The function described above ("Check_IntegerGreaterZero") in PgAdmin right bottom pane shows as ------------------------------------------------------------------------------------- -- Function: Check_IntegerGreaterZero(in_Value int4) -- DROP FUNCTION "Check_IntegerGreaterZero"(int4); CREATE OR REPLACE FUNCTION "Check_IntegerGreaterZero"(int4) RETURNS bool AS $BODY$ select $1 > 0; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; ALTER FUNCTION "Check_IntegerGreaterZero"(int4) OWNER TO postgres; ------------------------------------------------------------------------------------- It will be convenient for me if the first line will be -- Function: "Check_IntegerGreaterZero"("in_Value" int4) - quoted function name and argument names. It is useful for copy / paste purposes :) Thank you for support. -- Best regards,Ivan mailto:Ivan-Sun1@mail.ru
> -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Ivan > Sent: 19 May 2005 16:37 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] Bug in CHECK constraints statement > reverse engineering. > > Hello, > > PgAdmin 1.3.0 (Apr 24 2005) > Wrong CHECK reverse engineering. Hi, pgAdmin does do this correctly. In order to run at a reasonable speed, pgAdmin caches details of objects read from the database, rather than running queries every time you select one. If you rename an object such as a function, it doesn't always know that that action may cause a property of another object to be changed, thus pgAdmin may continue to show the old definition. To force a reload, right-click a node in the treeview and select the 'Refresh' option. > > It will be convenient for me if the first line will be > > -- Function: "Check_IntegerGreaterZero"("in_Value" int4) > > - quoted function name and argument names. It is useful for > copy / paste purposes :) Thanks, fix commited. Regards, Dave
Hello Dave, DP> Hi, DP> pgAdmin does do this correctly. In order to run at a reasonable speed, DP> pgAdmin caches details of objects read from the database, rather than DP> running queries every time you select one. If you rename an object such DP> as a function, it doesn't always know that that action may cause a DP> property of another object to be changed, thus pgAdmin may continue to DP> show the old definition. DP> To force a reload, right-click a node in the treeview and select the DP> 'Refresh' option. You were right on a half - pgAdmin do this correctly for tables check constraints, but for domain definition 'Refresh' and even closing pgAdmin and restarting postmaster doesn't help :) - I see: CREATE DOMAIN "TestDomain" AS int4 CONSTRAINT TestDomain_check CHECK "Check_IntegerGreaterZero"(VALUE); though function were renamed. By the way it will be great to add quoting of domain's constraint name in the definition pane (right bottom). Thank you for support. -- Best regards,Ivan mailto:Ivan-Sun1@mail.ru
> -----Original Message----- > From: Ivan [mailto:Ivan-Sun1@mail.ru] > Sent: 20 May 2005 10:01 > To: Dave Page > Cc: pgadmin-support@postgresql.org > Subject: Re[2]: [pgadmin-support] Bug in CHECK constraints > statement reverse engineering. > > You were right on a half - pgAdmin do this correctly for tables check > constraints, but for domain definition 'Refresh' and even closing > pgAdmin and restarting postmaster doesn't help :) - Ack, sorry - missed that. Fixed in SVN. Regards, Dave