pg_dump, pg_restore

[aktivo]

pg_dump remove name of schema from default value.
i don't know why. how can i solve this problem ?

For example:
BEFORE PG_DUMP:

CREATE TABLE "is8"."tb_kotuce" (
"id_kotuc" CHAR(10) DEFAULT is8.fn_sq_id_kotuc() NOT NULL,
...
...

AFTER PG_RESTORE

CREATE TABLE "is8"."tb_kotuce" (
"id_kotuc" CHAR(10) DEFAULT fn_sq_id_kotuc() NOT NULL,
...
...

SCHEMA NAME is8 IS REMOVED AFTER PG_RESTORE.

AFTER INSERT ROW TO TABLE is8.tb_kotuce, ERROR OCCURRED, BECAUSE NAME OF SCHEMA IS MISSING IN FRONT OF FUNCTION NAME 'fn_sq_id_kotuc()'.

I NEED RESTORE WITH NAME OF SCHEMA.

 

[gwinn7]

When I tried something very similar with the new release 8.3, this problem seemed to go away.

Also, I noticed this same problem when I had the same sequence named in another schema.

If you can try upgrading to 8.3, be sure to post back and let me know how it went.

Gary
gwinn7

 

[aktivo]

I did following steps:
- upgrade PostgreSQL from version 8.1.4 to 8.1.11
- pg_dump
- install version 8.3.0 on another computer
- pg_restore

It's same problem.
All fields which had as default value some function, that function don't have schema name.

for example:
BEFORE PG_DUMP:
....
"id_kotuc" CHAR(10) DEFAULT schema.function() NOT NULL
....

AFTER PG_RESTORE:
....
"id_kotuc" CHAR(10) DEFAULT function() NOT NULL
....

SCHEMA MISSING.
PLEASE HELP.

 

[gwinn7]

Ah, in your pg_dump script, did you notice if the search_path variable was set?

It seems that pg_dump removes the schema references, but if you set the search_path, it should work fine on the restore.

Example...

SET search_path = myschemaname;

By setting the search_path, specifying the schema really becomes irrelevant if you are backing up a schema. pg_restore should bring back the schema properly.

Did you look into this?

Gary
gwinn7

 

[aktivo]

This is part of pg_dump file:

.........
.........
.........
SET search_path = moja_schema, pg_catalog;

CREATE FUNCTION fn_sq_id_kotuc() RETURNS character varying
AS $$
DECLARE t_id VARCHAR;
BEGIN
t_id := 'KT' || LTrim( to_char( nextval( 'moja_schema.sq_id_kotuc' ), '00000000' ) );
RETURN t_id;
END;
$$
LANGUAGE plpgsql;


ALTER FUNCTION moja_schema.fn_sq_id_kotuc() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = true;

CREATE TABLE tb_tabulka (
id_kotuc character(10) DEFAULT fn_sq_id_kotuc() NOT NULL
);
........
........
........

---------------------------------------------------
pg_dump set search_path automaticaly during backup.
how can i disable automatic set search path ?
Is it solution ?

if you need pg_dump file from my db, i can send it to you by e-mail. File generated automaticaly by pg_dump. You can try to restore it. i was not success.
i don't know what i have to "tell" to pg_dump or pg_restore for correct output. Which options or switches of pg_dump must i set ?

 

[gwinn7]

I cannot reproduce your problem.

When I specify the schema name, it regenerates in the pg_dump file for both of my test functions.

Below is my test dump of a quick test schema I created for this issue and as you can see, the schema names did not drop off.

PGDUMP COMMAND USED...
> pg_dump -n b -U postgres mydatabase > myfile.txt
-- Note 'b' was my schema name.

DUMP FILE RESULT...
--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: b; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA b;


ALTER SCHEMA b OWNER TO postgres;

SET search_path = b, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: mytable; Type: TABLE; Schema: b; Owner: postgres; Tablespace:
--

CREATE TABLE mytable (
id integer DEFAULT nextval('ltjet_duediligence.datacomment_seq'::regclass) NOT NULL,
mytext character varying(14) DEFAULT 'NOTSET'::character varying NOT NULL
);


ALTER TABLE b.mytable OWNER TO postgres;

--
-- Name: myfunction(); Type: FUNCTION; Schema: b; Owner: postgres
--

CREATE FUNCTION myfunction() RETURNS character varying
AS $$
DECLARE
t_id varchar;
BEGIN
t_id = 'KT' || nextval('ltjet_duediligence.datacomment_seq'::regclass)::text;
RETURN t_id;

END;
$$
LANGUAGE plpgsql;


ALTER FUNCTION b.myfunction() OWNER TO postgres;

--
-- Name: myfunction2(); Type: FUNCTION; Schema: b; Owner: postgres
--

CREATE FUNCTION myfunction2() RETURNS character varying
AS $$
DECLARE
t_id varchar;
BEGIN
t_id = 'KT' || nextval('b.mytable_seq'::regclass)::text;
RETURN t_id;

END;
$$
LANGUAGE plpgsql;


ALTER FUNCTION b.myfunction2() OWNER TO postgres;

--
-- Name: mytable_seq; Type: SEQUENCE; Schema: b; Owner: postgres
--

CREATE SEQUENCE mytable_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE b.mytable_seq OWNER TO postgres;

--
-- Name: mytable_seq; Type: SEQUENCE SET; Schema: b; Owner: postgres
--

SELECT pg_catalog.setval('mytable_seq', 1, false);


--
-- Data for Name: mytable; Type: TABLE DATA; Schema: b; Owner: postgres
--

COPY mytable (id, mytext) FROM stdin;
\.


--
-- Name: mytable_pk; Type: CONSTRAINT; Schema: b; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY mytable
ADD CONSTRAINT mytable_pk PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

Gary
gwinn7

 

[aktivo]

You don't reproduce my problem because you don't use function for DEFAULT VALUE in TABLE. You use nextval - this works ok.

THIS IS FULL PG_DUMP FILE:
See below...
--
-- PostgreSQL database dump
--

-- Started on 2008-02-12 12:20:56

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1740 (class 1262 OID 36229)
-- Name: db_test; Type: DATABASE; Schema: -; Owner: postgres
-- Data Pos: 0
--

CREATE DATABASE db_test WITH TEMPLATE = template0 ENCODING = 'UTF8';


ALTER DATABASE db_test OWNER TO postgres;

\connect db_test

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 6 (class 2615 OID 36230)
-- Name: moja_schema; Type: SCHEMA; Schema: -; Owner: postgres
-- Data Pos: 0
--

CREATE SCHEMA moja_schema;


ALTER SCHEMA moja_schema OWNER TO postgres;

--
-- TOC entry 1741 (class 0 OID 0)
-- Dependencies: 3
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
-- Data Pos: 0
--

COMMENT ON SCHEMA public IS 'standard public schema';


--
-- TOC entry 294 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
-- Data Pos: 0
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = moja_schema, pg_catalog;

--
-- TOC entry 21 (class 1255 OID 36238)
-- Dependencies: 6 294
-- Name: fn_sq_id_kotuc(); Type: FUNCTION; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--

CREATE FUNCTION fn_sq_id_kotuc() RETURNS character varying
AS $$
DECLARE t_id VARCHAR;
BEGIN
t_id := 'KT' || LTrim( to_char( nextval( 'moja_schema.sq_id_kotuc' ), '00000000' ) );
RETURN t_id;
END;
$$
LANGUAGE plpgsql;


ALTER FUNCTION moja_schema.fn_sq_id_kotuc() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- TOC entry 1466 (class 1259 OID 36231)
-- Dependencies: 1734 6
-- Name: tb_tabulka; Type: TABLE; Schema: moja_schema; Owner: postgres; Tablespace:
-- Data Pos: 0
--

HERE IS PROBLEM !
I NEED RESTORE: '... DEFAULT moja_schema.fn_sq_id_kotuc() NOT NULL ....'
NOT: '... DEFAULT fn_sq_id_kotuc() NOT NULL ...'

I NEED RESTORE FUNCTION WITH SCHEMA NAME, NOT WITHOUT SCHEMA NAME.

CREATE TABLE tb_tabulka (
id_kotuc character(10) DEFAULT fn_sq_id_kotuc() NOT NULL
);


ALTER TABLE moja_schema.tb_tabulka OWNER TO postgres;

--
-- TOC entry 1467 (class 1259 OID 36236)
-- Dependencies: 6
-- Name: sq_id_kotuc; Type: SEQUENCE; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--

CREATE SEQUENCE sq_id_kotuc
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE moja_schema.sq_id_kotuc OWNER TO postgres;

--
-- TOC entry 1743 (class 0 OID 0)
-- Dependencies: 1467
-- Name: sq_id_kotuc; Type: SEQUENCE SET; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--

SELECT pg_catalog.setval('sq_id_kotuc', 2, true);


--
-- TOC entry 1737 (class 0 OID 36231)
-- Dependencies: 1466
-- Data for Name: tb_tabulka; Type: TABLE DATA; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--

COPY tb_tabulka (id_kotuc) FROM stdin;
\.


--
-- TOC entry 1736 (class 2606 OID 36235)
-- Dependencies: 1466 1466
-- Name: tb_tabulka_pkey; Type: CONSTRAINT; Schema: moja_schema; Owner: postgres; Tablespace:
-- Data Pos: 0
--

ALTER TABLE ONLY tb_tabulka
ADD CONSTRAINT tb_tabulka_pkey PRIMARY KEY (id_kotuc);


--
-- TOC entry 1742 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
-- Data Pos: 0
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2008-02-13 00:08:39

--
-- PostgreSQL database dump complete
--

 

[gwinn7]

Could you also provide the pg_dump command string that you are invoking?

In the meantime, you would probably be better served by attempting to post your issue at the support forum "General" on the Postgres web site.

When I found a bug in PL, Tom Lane responded very very swiftly.

Gary
gwinn7

 

[aktivo]

pg_dump.exe --file=_db_test_c.pgb --format=c --verbose --encoding=UTF8 --host=172.16.19.120 --username=postgres db_test