Date: Thu, 15 Dec 2016 00:23:24 -0800
From: David Fetter <david@fetter.org>
To: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Transaction oddity with list partition of a list partition
Message-ID: <20161215082324.GA20659@fetter.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0F1p//8PRICkK4MW"
Content-Disposition: inline
User-Agent: Mutt/1.7.1 (2016-10-04)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--0F1p//8PRICkK4MW
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Folks,

I'm having some trouble understanding what's going on here.  When I \i
the file in 55caaaeba877eac1feb6481fb413fa04ae9046ac without starting
a transaction explicitly, it produces the expected results.  When I \i
it after a BEGIN, not so much.

What's going on?

Best,
David.

shackle@shackle=# BEGIN;
BEGIN
shackle@shackle=# \i ten_plus.sql 
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TABLE
CREATE TRIGGER
psql:ten_plus.sql:66: ERROR:  no partition of relation "the_log" found for row
DETAIL:  Failing row contains (2016-12-15 00:17:46.579357-08, shackle, INSERT, public, city, null, {"id": 1, "name": "Oakland", "population": 419267}).
CONTEXT:  SQL statement "INSERT INTO the_log(
        action,
        table_schema,
        table_name,
        old_row,
        new_row)
    VALUES (
        TG_OP,
        TG_TABLE_SCHEMA,
        TG_TABLE_NAME, 
        CASE TG_OP WHEN 'INSERT' THEN NULL ELSE row_to_json(OLD)::jsonb END,
        CASE TG_OP WHEN 'DELETE' THEN NULL ELSE row_to_json(NEW)::jsonb END
    )"
PL/pgSQL function log_change() line 3 at SQL statement
shackle@shackle=# ROLLBACK;

-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--0F1p//8PRICkK4MW
Content-Type: application/sql
Content-Disposition: attachment; filename="ten_plus.sql"
Content-Transfer-Encoding: quoted-printable

CREATE TABLE the_log (=0A=
    ts TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,=0A=
    "user" TEXT NOT NULL DEFAULT current_user,=0A=
    action TEXT NOT NULL,=0A=
    table_schema TEXT NOT NULL,=0A=
    table_name TEXT NOT NULL,=0A=
    old_row JSONB,=0A=
    new_row JSONB,=0A=
    CHECK(=0A=
        CASE action=0A=
            WHEN 'INSERT' THEN old_row IS NULL AND new_row IS NOT NULL=0A=
            WHEN 'UPDATE' THEN old_row IS NOT NULL AND new_row IS NOT NULL=
=0A=
            ELSE /*DELETE, and maybe TRUNCATE, if that's supported by acces=
s to old rows */=0A=
                old_row IS NOT NULL AND new_row IS NULL=0A=
        END=0A=
    )=0A=
) PARTITION BY LIST(table_schema);=0A=
=0A=
CREATE TABLE public_log=0A=
    PARTITION OF the_log FOR VALUES IN ('public')=0A=
    PARTITION BY LIST(table_name);=0A=
=0A=
CREATE TABLE public_city_log=0A=
    PARTITION OF public_log FOR VALUES IN ('city');=0A=
=0A=
CREATE FUNCTION log_change()=0A=
RETURNS TRIGGER=0A=
LANGUAGE plpgsql=0A=
STRICT=0A=
AS $$=0A=
BEGIN=0A=
    INSERT INTO the_log(=0A=
        action,=0A=
        table_schema,=0A=
        table_name,=0A=
        old_row,=0A=
        new_row)=0A=
    VALUES (=0A=
        TG_OP,=0A=
        TG_TABLE_SCHEMA,=0A=
        TG_TABLE_NAME,=20=0A=
        CASE TG_OP WHEN 'INSERT' THEN NULL ELSE row_to_json(OLD)::jsonb END=
,=0A=
        CASE TG_OP WHEN 'DELETE' THEN NULL ELSE row_to_json(NEW)::jsonb END=
=0A=
    );=0A=
    IF TG_OP =3D 'DELETE' THEN=0A=
        RETURN OLD;=0A=
    END IF;=0A=
    RETURN NEW;=0A=
END;=0A=
$$;=0A=
=0A=
CREATE TABLE city (=0A=
    id SERIAL PRIMARY KEY,=0A=
    name TEXT NOT NULL,=0A=
    CHECK(name !~ '^\s*$'),=0A=
    population BIGINT NOT NULL, /* Over 2 BILLION people in *one* city?!? *=
/=0A=
    CHECK(population >=3D 0)=0A=
);=0A=
=0A=
CREATE TRIGGER city_log=0A=
    AFTER INSERT OR UPDATE OR DELETE ON city=0A=
    FOR EACH ROW=0A=
        EXECUTE PROCEDURE log_change();=0A=
=0A=
INSERT INTO city (name, population)=0A=
VALUES('Oakland',419267);=0A=
=0A=
SELECT * FROM the_log;=0A=

--0F1p//8PRICkK4MW
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--0F1p//8PRICkK4MW--