Pgadmin - Bug in CHECK constraints statement reverse engineering.

Author

Bug in CHECK constraints statement reverse engineering.

Ivan

2005-05-19, 11:24 am

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_IntegerGreater
Zero"(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_IntegerGreater
Zero"(test))
)
WITHOUT OIDS;

CREATE DOMAIN "TestDomain"
AS int4
CONSTRAINT TestDomain_check CHECK " Check_IntegerGreater
Zero"(VALUE);

-- Then just rename function
ALTER FUNCTION " Check_IntegerGreater
Zero"(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_constraintde
f
--------------------------------------------------------------------------------------------------------------------------------
cardinal_number_doma
in_check |(VALUE >= 0) |CHECK ((VALUE>= 0))
CHK_TestTable_Test |" Check_IntegerGreater
Zero"(test) |CHECK("Check_IGZ"(test))
TestDomain_check |" Check_IntegerGreater
Zero"(VALUE) |CHECK("Check_IGZ"(VALUE))

I suppose that PgAdmin shows values from consrc.

Another feature request. The function described above (" Check_IntegerGreater
Zero") in PgAdmin
right bottom pane shows as
-------------------------------------------------------------------------------------

-- Function: Check_IntegerGreater
Zero(in_Value int4)

-- DROP FUNCTION " Check_IntegerGreater
Zero"(int4);

CREATE OR REPLACE FUNCTION " Check_IntegerGreater
Zero"(int4)
RETURNS bool AS
$BODY$
select $1 > 0;
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION " Check_IntegerGreater
Zero"(int4) OWNER TO postgres;

-------------------------------------------------------------------------------------

It will be convenient for me if the first line will be

-- Function: " Check_IntegerGreater
Zero"("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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Dave Page

2005-05-20, 3:24 am

> -----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_IntegerGreater
Zero"("in_Value" int4)
>
> - quoted function name and argument names. It is useful for
> copy / paste purposes :)

Thanks, fix commited.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Ivan

2005-05-20, 7:23 am

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_IntegerGreater
Zero"(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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dave Page

2005-05-20, 7:23 am

> -----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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Sponsored Links