Horton

Thread

Commits

  1. Ensure schema qualification in pg_restore DISABLE/ENABLE TRIGGER commands.

  1. BUG #15338: pg_restore --disable-triggers --data-only AND schema for table is not set.

    The Post Office <noreply@postgresql.org> — 2018-08-17T16:49:47Z

    The following bug has been logged on the website:

Bug reference:      15338
Logged by:          Oleg
Email address:      nekomata@olegk.ca
PostgreSQL version: 10.4
Operating system:   ubuntu 18.04
Description:        

for instance, there is a table "analytics.user_snapshot_data"
and it exists in its schema... 

when i do the restore here what is in the script:
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.5 (Ubuntu 10.5-1.pgdg16.04+1)
-- Dumped by pg_dump version 10.5 (Ubuntu 10.5-1.pgdg16.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Data for Name: user_snapshot_data; Type: TABLE DATA; Schema: analytics;
Owner: XXXXXXX
--

SET SESSION AUTHORIZATION DEFAULT;

ALTER TABLE user_snapshot_data DISABLE TRIGGER ALL;

COPY analytics.user_snapshot_data (report_date, user_id, company_id, tasks,
events) FROM stdin;
..... [data here] ....
-------------------------------------------------------------------------------------------------------------------

that fails with 
ERROR:  relation "user_snapshot_data" does not exist

so basically "ALTER TABLE user_snapshot_data DISABLE TRIGGER ALL;"
is missing proper schema.

  2. Re: BUG #15338: pg_restore --disable-triggers --data-only AND schema for table is not set.

    Sergei Kornilov <sk@zsrv.org> — 2018-08-17T18:01:31Z

    Hello
I can confirm this and i got reproducible testcase:
create database bug15338;
\c bug15338
create schema testschema;
create table testschema.test (value text);
insert into testschema.test values ('bug 15338 verify');

Plain format looks good:
/usr/lib/postgresql/10/bin/pg_dump bug15338  --disable-triggers --data-only # all ok!

But custom and dir formats are wrong:
/usr/lib/postgresql/10/bin/pg_dump bug15338  --disable-triggers --data-only -Fc --file bug15338.pgdump
/usr/lib/postgresql/10/bin/pg_restore bug15338.pgdump --disable-triggers --data-only
Produces wrong ALTER TABLE test DISABLE TRIGGER ALL, not testschema.test

Current master has this bug too.

regards, Sergei

  3. Re: BUG #15338: pg_restore --disable-triggers --data-only AND schema for table is not set.

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-08-17T20:12:09Z

    Sergei Kornilov <sk@zsrv.org> writes:
> Plain format looks good:
> /usr/lib/postgresql/10/bin/pg_dump bug15338  --disable-triggers --data-only # all ok!
> But custom and dir formats are wrong:
> /usr/lib/postgresql/10/bin/pg_dump bug15338  --disable-triggers --data-only -Fc --file bug15338.pgdump
> /usr/lib/postgresql/10/bin/pg_restore bug15338.pgdump --disable-triggers --data-only
> Produces wrong ALTER TABLE test DISABLE TRIGGER ALL, not testschema.test

That's weird [ digs ] ... oh, here's the problem:

    ahprintf(AH, "ALTER TABLE %s DISABLE TRIGGER ALL;\n\n",
             fmtQualifiedId(PQserverVersion(AH->connection),
                            te->namespace,
                            te->tag));

This code is fine as long as we're connected to a database (either
for dump or restore).  But if we're not, AH->connection is NULL.
PQserverVersion doesn't crash, but silently returns 0 ... which
fmtQualifiedId interprets as "too old for schemas", so it doesn't
emit the schema part.

We could make a narrow fix right here (and in the matching ENABLE stanza);
but having seen this, it seems like a potentially generic problem.
I think we'd be smarter to try to fix this by-design.

In particular, since no current version of pg_dump even pretends either
to dump from pre-8.0 servers or to produce output that could be loaded
into a pre-7.3 server without adjustment, it doesn't seem like
fmtQualifiedId should consider server version at all.  I was tempted
to remove its server-version argument in 64f3524e2, but refrained ---
now that seems like a mistake.

However, that doesn't work for pre-v10 since we still supported dumping
from pre-7.3 servers then, and some of the uses of fmtQualifiedId are
for commands to be sent to the source server rather than commands to
be emitted.  In the back branches, probably the best thing is just to
assume that the output is for >= 7.3 when producing DISABLE/ENABLE
TRIGGERS commands.

			regards, tom lane