Lists: | pgsql-bugs |
---|
From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug #787: Backup Files Output Constraints Before Functions |
Date: | 2002-09-27 20:52:21 |
Message-ID: | 20020927205221.CA95F4760EC@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
David Wheeler (david(at)wheeler(dot)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 constraint references 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 tests on 7.3b1.
Thanks!
David
Sample Code
No file was uploaded with this report
From: | Adam Doligalski <drsky(at)atm(dot)com(dot)pl> |
---|---|
To: | david(at)wheeler(dot)net, pgsql-bugs(at)postgresql(dot)org |
Subject: | Nie ma mnie w biurze |
Date: | 2002-09-27 20:53:12 |
Message-ID: | E17v26q-000InU-00@merkury.atm.com.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
W dniu dzisiejszym jestem na urlopie.
Jesli sa jakies sprawy nie cierpiace zwloki - prosze skontaktowac sie ze mna pod numerem telefonu 607225738.
Adam Doligalski
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | david(at)wheeler(dot)net, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug #787: Backup Files Output Constraints Before Functions |
Date: | 2002-09-27 21:10:00 |
Message-ID: | 7005.1033161000@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
pgsql-bugs(at)postgresql(dot)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
From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Nie ma mnie w biurze |
Date: | 2002-09-27 21:19:44 |
Message-ID: | 3D94CB70.555C3C4D@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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