Thread: Bug #787: Backup Files Output Constraints Before Functions

David Wheeler (david@wheeler.net) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Backup Files Output Constraints Before Functions

Long Description
Bricolage has a table with a constraint that uses a custom function. The code to build it is functionally equivalent to
this:

CREATE TABLE "testing" (
    first varchar(32),
    try_me numeric(1,0)
);

CREATE   FUNCTION try_it()
         RETURNS BOOLEAN
AS       'SELECT TRUE'
LANGUAGE 'sql'
WITH     (isstrict);

ALTER TABLE testing ADD CONSTRAINT ck_testing
  CHECK (try_it());


When backing up, however, this call to pg_dump:

% pg_dump -U postgres -Oxd testing > test-backup.sql

Outputs this:

--
-- PostgreSQL database dump
--

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 517307)
-- Name: testing; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE testing (
    first character varying(32),
    try_me numeric(1,0),
    CONSTRAINT ck_testing CHECK (try_it())
);


--
-- TOC entry 3 (OID 517309)
-- Name: try_it (); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION try_it () RETURNS boolean
    AS 'SELECT TRUE'
    LANGUAGE sql STRICT;


--
-- Data for TOC entry 4 (OID 517307)
-- Name: testing; Type: TABLE DATA; Schema: public; Owner: postgres
--

Any attempt to restore this database will unfortunately fail. The testing table won't exist because the ck_testing
constraintreferences the try_it() function, which of course doesn't exist yet: 

% psql -U postgres testing < ~/Desktop/test-backup.sql
SET
ERROR:  Function try_it() does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
CREATE FUNCTION

Would it be possible for pg_dump to do the right thing here? This issue exists with 7.1 and later. I just ran the above
testson 7.3b1. 

Thanks!

David

Sample Code


No file was uploaded with this report

W dniu dzisiejszym jestem na urlopie.

Jesli sa jakies sprawy nie cierpiace zwloki - prosze skontaktowac sie ze mna pod numerem telefonu 607225738.

Adam Doligalski

pgsql-bugs@postgresql.org writes:
> Would it be possible for pg_dump to do the right thing here?

Not real soon :-(.  It's possible that 7.4 pg_dump will be smart enough
to handle circular dependencies like this one.  The current pg_dump
doesn't even know there is a problem, much less have a strategy for
coping.

I assume you can't simply create the try_it() function before the table
that uses it?  (If you rewrote it as a plpgsql function, you probably
could do that, since the body of a PL function isn't checked when it's
defined.  Perhaps this can serve as a workaround until pg_dump gets
smarter about dependencies.)

            regards, tom lane

Another one who decided to turn on the vacation auto reply.
Can some one unsubscribe him temporarely.




Adam Doligalski wrote:
>
> W dniu dzisiejszym jestem na urlopie.
>
> Jesli sa jakies sprawy nie cierpiace zwloki - prosze skontaktowac sie ze mna pod numerem telefonu 607225738.
>
> Adam Doligalski
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org