Horton

Thread

Commits

  1. Invalid parent's relcache after CREATE TABLE .. PARTITION OF.

  2. Improve handling of array elements as getdiag_targets and cursor_variables.

  1. Transaction oddity with list partition of a list partition

    David Fetter <david@fetter.org> — 2016-12-15T08:23:24Z

    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

  2. Re: Transaction oddity with list partition of a list partition

    David Fetter <david@fetter.org> — 2016-12-15T09:09:16Z

    On Thu, Dec 15, 2016 at 12:23:24AM -0800, David Fetter wrote:
> 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.


I've managed to get a shorter repro for the issue:

BEGIN;
CREATE TABLE the_log (
    ts TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "user" TEXT NOT NULL DEFAULT current_user,
    action TEXT NOT NULL,
    table_schema TEXT NOT NULL,
    table_name TEXT NOT NULL,
    old_row JSONB,
    new_row JSONB,
    CHECK(
        CASE action
            WHEN 'INSERT' THEN old_row IS NULL AND new_row IS NOT NULL
            WHEN 'UPDATE' THEN old_row IS NOT NULL AND new_row IS NOT NULL
            ELSE /*DELETE, and maybe TRUNCATE, if that's supported by access to old rows */
                old_row IS NOT NULL AND new_row IS NULL
        END
    )
) PARTITION BY LIST(table_schema);
CREATE TABLE public_log
    PARTITION OF the_log FOR VALUES IN ('public');
INSERT INTO the_log (action, table_schema, table_name, new_row)
VALUES ('INSERT','public','city','{"name": "Oakland", "population": 419267}');

leads to:

ERROR:  no partition of relation "the_log" found for row
DETAIL:  Failing row contains (2016-12-15 00:59:17.980094-08, shackle, INSERT, public, city, null, {"name": "Oakland", "population": 419267}).

Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... code
fails to run CacheInvalidateRelcache on its parent(s)?

Best,
David.
-- 
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

  3. Re: Transaction oddity with list partition of a list partition

    Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2016-12-15T09:20:04Z

    Hi David,

On 2016/12/15 18:09, David Fetter wrote:
> Per Thomas Munro, could it be that the CREATE ... PARTITION OF ... code
> fails to run CacheInvalidateRelcache on its parent(s)?

Thomas's right.  There is a patch posted for this issue [1]; I'm sending
an updated version of the patch later today in reply to [1].  Meanwhile,
could you try and see if the problem is fixed with the attached patch.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CA%2BTgmoZ86v1G%2Bzx9etMiSQaBBvYMKfU-iitqZArSh5z0n8Q4cA%40mail.gmail.com

  4. Re: Transaction oddity with list partition of a list partition

    David Fetter <david@fetter.org> — 2016-12-15T15:17:24Z

    On Thu, Dec 15, 2016 at 06:20:04PM +0900, Amit Langote wrote:
> 
> Hi David,
> 
> On 2016/12/15 18:09, David Fetter wrote:
> > Per Thomas Munro, could it be that the CREATE ... PARTITION OF ...
> > code fails to run CacheInvalidateRelcache on its parent(s)?
> 
> Thomas's right.  There is a patch posted for this issue [1]; I'm
> sending an updated version of the patch later today in reply to [1].
> Meanwhile, could you try and see if the problem is fixed with the
> attached patch.

That fixed both cases.  Thanks!

Best,
David.
-- 
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