Обсуждение: pg_dump, pg_restore.
Hello,
I have some question about pg_dump, pg_restore.
At the end of this text is full dump of database db_test.
This database has one table with one field named id_kotuc.
Default value for this field is function named fn_sq_id_kotuc().
Function and table is in same schema named moja_schema.
Before pg_dump, default value is: ... DEFAULT moja_schema.fn_sq_id_kotuc() ...
After pg_restore, default value is: ... DEFAULT fn_sq_id_kotuc() ...
The name of the scheme is missing, it is cut off.
I need first variant of default value (with name of the schema), because second variant raise exception if I insert two or more records.
I don't know how can I use command pg_dump, if I want to dump it with the name of the schema.
Can someone help me ?
THIS IS FULL DUMP OF DATABASE db_test (Win32, PostgreSQL v8.3.0)
------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- 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
--
"Emil J." <EmilJ@pyton.sk> writes:
> Before pg_dump, default value is: ... DEFAULT moja_schema.fn_sq_id_kotuc() ...
> After pg_restore, default value is: ... DEFAULT fn_sq_id_kotuc() ...
> The name of the scheme is missing, it is cut off.
> I need first variant of default value (with name of the schema), because second variant raise exception if I insert
twoor more records.
No, you don't need that. The two versions you claim are different are
in fact exactly the same thing. Please show us the actual problem
you're having, not an uninformed guess as to the cause.
regards, tom lane
<!--
body {
margin: 5px 5px 5px 5px;
background-color: #ffffff;
}
/* ---------- Text Styles ---------- */
hr { color: #000000}
body, table /* Normal text */
{
font-size: 10pt;
font-family: 'Tahoma';
font-style: normal;
font-weight: normal;
color: #000000;
text-decoration: none;
}
span.rvts1 /* Heading */
{
font-family: 'Arial';
font-weight: bold;
color: #0000ff;
}
span.rvts2 /* Subheading */
{
font-family: 'Arial';
font-weight: bold;
color: #000080;
}
span.rvts3 /* Keywords */
{
font-family: 'Arial';
font-style: italic;
color: #800000;
}
a.rvts4, span.rvts4 /* Jump 1 */
{
font-family: 'Arial';
color: #008000;
text-decoration: underline;
}
a.rvts5, span.rvts5 /* Jump 2 */
{
font-family: 'Arial';
color: #008000;
text-decoration: underline;
}
span.rvts6
{
font-size: 9pt;
}
span.rvts7
{
font-size: 9pt;
font-weight: bold;
}
span.rvts8
{
font-size: 9pt;
font-weight: bold;
color: #0000ff;
}
span.rvts9
{
font-size: 9pt;
font-weight: bold;
color: #0000ff;
text-decoration: underline;
}
span.rvts10
{
font-size: 9pt;
font-family: 'courier new';
color: #800000;
}
a.rvts11, span.rvts11
{
font-size: 9pt;
font-family: 'courier new';
color: #0000ff;
text-decoration: underline;
}
span.rvts12
{
font-size: 9pt;
font-family: 'courier new';
color: #800000;
}
span.rvts13
{
font-size: 9pt;
font-family: 'courier new';
color: #800080;
}
span.rvts14
{
font-size: 8pt;
font-family: 'arial';
font-style: italic;
color: #808080;
}
/* ---------- Para Styles ---------- */
p,ul,ol /* Paragraph Style */
{
text-align: left;
text-indent: 0px;
padding: 0px 0px 0px 0px;
margin: 0px 0px 0px 0px;
}
.rvps1 /* Centered */
{
text-align: center;
}
-->
First, i apologize, my english skills is very, very poor.
-------
i have many tables in many schemas with default value like that: DEFAULT schema.function(...).
i need backup all databases and i need restore it.
IF YOU WANT UNDERSTAND ME, YOU NEED DO THIS:
YOU TRY RESTORE _DB_TEST_.PGB FILE TO DB_TEST DATABASE.
AFTER RESTORE, YOU TRY TO INSERT TWO OR MORE ROWS/RECORDS IN TABLE TB_TABULKA.
IF FIELD MOJA_SCHEMA.TB_TABULKA.ID_KOTUC CONTAIN VALUES LIKE KT00000001, KT00000002, ...3, ...4, ....
ALL IS OK, IF NOT - MAY BE WRONG RESTORED ?
i have many tables with fields that have function as default value.
Another side of same problem:
I have scheme named moja_schema.
I have table in scheme named tb_tabulka.
I have function named my_function() in scheme moja_schema.
I want set default value for field id_kotuc to my_function().
It is not work, because my_function() is in scheme named moja_schema, not in scheme public.
ALTER TABLE "moja_schema"."tb_tabulka"
ALTER COLUMN "id_kotuc" SET DEFAULT my_function();
ERROR: function my_function() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Yes, it is true, because my_function() is not in public scheme. It is
in moja_schema scheme.
It require full path to function:
ALTER TABLE "moja_schema"."tb_tabulka"
ALTER COLUMN "id_kotuc" SET DEFAULT moja_schema.my_function();
It works fine.
Problem is, when i do backup database and restore database.
Full path is cut off - table can not generate default value for field id_kotuc, because: "function my_function() does
notexist".
Thank you for your help.
Thursday, February 14, 2008, 5:03:31 AM, si napisal:
TL> "Emil J." <EmilJ@pyton.sk> writes:
>> Before pg_dump, default value is: ... DEFAULT moja_schema.fn_sq_id_kotuc() ...
>> After pg_restore, default value is: ... DEFAULT fn_sq_id_kotuc() ...
>> The name of the scheme is missing, it is cut off.
>> I need first variant of default value (with name of the schema), because second variant raise exception if I
inserttwo or more records.
TL> No, you don't need that. The two versions you claim are different are
TL> in fact exactly the same thing. Please show us the actual problem
TL> you're having, not an uninformed guess as to the cause.
TL> regards, tom lane
--
This e-mail as well as any files transmitted with it is confidential and may well contain information which is legally
privileged.It is intended solely for the use of the individual or the entity to whom it is addressed. If you are not
theintended recipient of this e-mail, you are hereby on notice of this status. Any disclosure, copying, distribution,
disseminationor publication of the information contained therein is strictly prohibited, unless you have been permitted
theretoby the sender, and might be a breach of confidence. If you are not the intended recipient, please return this
e-mailimmediately to the sender and then delete this message from your system. The sender is not liable for the proper
transmissionof this information nor for any delay in its receipt.<SPAN lang=EN-GB style="FONT-SIZE: 8pt; FONT!
-FAMILY: Arial; mso-ansi-language: EN-GB">