Adding table partition slow when there is default partition with data (primary key not used to check partition condition)

[Sasa Vilic]

Hi all,

I am doing following:

CREATE TABLE "change" (
  transaction_id uuid NOT NULL,
  id int4 NOT NULL,
  change_type varchar NOT NULL,
  object_type varchar NOT NULL,
  object_content jsonb NOT NULL,
  category_id uuid NOT NULL,
  CONSTRAINT change_pkey PRIMARY KEY (transaction_id, id)
) partition by list(transaction_id);

create table change_default
partition of "change" default;

insert into "change"
select * from old_change; -- 17437300 rows

create table change_bf6840c7_3e7b_4100_b0e4_f5844fb7635d
partition of "change"
for values in ('bf6840c7-3e7b-4100-b0e4-f5844fb7635d');

Adding this last partition takes around 19 seconds. I understand that
postgres has to check that 'bf6840c7-3e7b-4100-b0e4-f5844fb7635d' is not
present in the default partition, but it can use the primary key for that
and it shouldn't take that long, right? This new table is currently not
being used, so it can't be table lock, so the only reasonable conclusion is
that it does full table scan. Can that be optimized to just do primary key
lookup?

Server:
PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit
8 cores, 32 GiB RAM, 1TB SSD

Thanks in advance!
Sasa

Empty ./share/timezone/UTC and failure to set UTC as timezone

[Sasa Vilic]

Hello,

we are using "embedded" PostgreSQL for integration tests (see
https://github.com/yandex-qatools/postgresql-embedded) and we have noticed
that our (java) clients fail to connect when their timezone is UTC, with PG
server giving following error message:

invalid value for parameter "TimeZone": "UTC"

To double check, I took official 9.6.7 sources, compiled and installed it
locally (/tmp/pgsql) and tried connecting with client again and I again we
got same error message.

When using psql, the same happens:

postgres=# set timezone='UTC';
ERROR:  invalid value for parameter "TimeZone": "UTC"

In order to further investigate this, I have attached strace on backend and
I have noticed that PG is able to find and read file ./share/timezone/UTC.
But right after reading the file, we get error message from above.

We noticed that UTC file is empty! Is this on purpose or is this is bug? Am
I missing something?

Kind Regards,

Sasa Vilic

Re: Empty ./share/timezone/UTC and failure to set UTC as timezone

[Sasa Vilic]

Hi Tom,

thanks for quick response. I have to apologize to you, PG from source is
fine. Sorry for wasting your time.

It only happens that after I put it into archive (tar czf
postgresql-9.6.7-linux-x64-binaries.tar.gz pgsql) and extract it, that it
is empty and it looks like it is a bug in java library implementation of
tar.gz.

Once again, sorry for wasting your time. Thank you very much for your
support.

Kind Regards,

Sasa Vilic

2018-02-19 17:33 GMT+00:00 Tom Lane :

> Sasa Vilic  writes:
> > To double check, I took official 9.6.7 sources, compiled and installed it
> > locally (/tmp/pgsql) and tried connecting with client again and I again
> we
> > got same error message.
>
> Hm, what platform and compiler are you using, exactly?
>
> > In order to further investigate this, I have attached strace on backend
> and
> > I have noticed that PG is able to find and read file
> ./share/timezone/UTC.
> > But right after reading the file, we get error message from above.
> > We noticed that UTC file is empty! Is this on purpose or is this is bug?
> Am
> > I missing something?
>
> It should certainly not be empty.  On my machine it's 127 bytes long:
>
> $ ls -l share/timezone/UTC
> -rw-r--r--. 6 postgres postgres 127 Feb 19 12:27 share/timezone/UTC
>
> and file(1) knows what it is:
>
> $ file share/timezone/UTC
> share/timezone/UTC: timezone data, version 2, 1 gmt time flag, 1 std time
> flag, no leap seconds, no transition times, 1 abbreviation char
>
> I wonder if you've tripped over some portability issue in the zic
> compiler.  Another idea, seeing that this file is multiply-linked
> in the timezone install tree, is that maybe you're on a filesystem
> that does strange things with hard links.
>
> It'd be interesting to see the rest of your timezone directory.  Mine
> looks like
>
> total 248
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Africa
> drwxr-xr-x.  6 postgres postgres 4096 Feb 19 12:27 America
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Antarctica
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Arctic
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Asia
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Atlantic
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Australia
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Brazil
> -rw-r--r--.  1 postgres postgres 2102 Feb 19 12:27 CET
> -rw-r--r--.  1 postgres postgres 2294 Feb 19 12:27 CST6CDT
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Canada
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Chile
> -rw-r--r--.  2 postgres postgres 2437 Feb 19 12:27 Cuba
> -rw-r--r--.  1 postgres postgres 1876 Feb 19 12:27 EET
> -rw-r--r--.  1 postgres postgres  127 Feb 19 12:27 EST
> -rw-r--r--.  1 postgres postgres 2294 Feb 19 12:27 EST5EDT
> -rw-r--r--.  2 postgres postgres 1972 Feb 19 12:27 Egypt
> -rw-r--r--.  2 postgres postgres 3543 Feb 19 12:27 Eire
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Etc
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Europe
> -rw-r--r--.  1 postgres postgres  148 Feb 19 12:27 Factory
> -rw-r--r--.  7 postgres postgres 3687 Feb 19 12:27 GB
> -rw-r--r--.  7 postgres postgres 3687 Feb 19 12:27 GB-Eire
> -rw-r--r--. 10 postgres postgres  127 Feb 19 12:27 GMT
> -rw-r--r--. 10 postgres postgres  127 Feb 19 12:27 GMT+0
> -rw-r--r--. 10 postgres postgres  127 Feb 19 12:27 GMT-0
> -rw-r--r--. 10 postgres postgres  127 Feb 19 12:27 GMT0
> -rw-r--r--. 10 postgres postgres  127 Feb 19 12:27 Greenwich
> -rw-r--r--.  1 postgres postgres  128 Feb 19 12:27 HST
> -rw-r--r--.  2 postgres postgres 1189 Feb 19 12:27 Hongkong
> -rw-r--r--.  2 postgres postgres 1188 Feb 19 12:27 Iceland
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Indian
> -rw-r--r--.  2 postgres postgres 1718 Feb 19 12:27 Iran
> -rw-r--r--.  3 postgres postgres 2265 Feb 19 12:27 Israel
> -rw-r--r--.  2 postgres postgres  507 Feb 19 12:27 Jamaica
> -rw-r--r--.  2 postgres postgres  318 Feb 19 12:27 Japan
> -rw-r--r--.  2 postgres postgres  259 Feb 19 12:27 Kwajalein
> -rw-r--r--.  2 postgres postgres  655 Feb 19 12:27 Libya
> -rw-r--r--.  1 postgres postgres 2102 Feb 19 12:27 MET
> -rw-r--r--.  1 postgres postgres  127 Feb 19 12:27 MST
> -rw-r--r--.  1 postgres postgres 2294 Feb 19 12:27 MST7MDT
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Mexico
> -rw-r--r--.  4 postgres postgres 2460 Feb 19 12:27 NZ
> -rw-r--r--.  2 postgres postgres 2087 Feb 19 12:27 NZ-CHAT
> -rw-r--r--.  4 postgres postgres 2453 Feb 19 12:27 Navajo
> -rw-r--r--.  5 postgres postgres  414 Feb 19 12:27 PRC
> -rw-r--r--.  1 postgres postgres 2294 Feb 19 12:27 PST8PDT
> drwxr-xr-x.  2 postgres postgres 4096 Feb 19 12:27 Pacific
> -rw-r--r--.

Concurrent delete and insert on same key, potentially a bug

[Sasa Vilic]

Hello,

Please excuse my ignorance, because I might be missing something obvious,
but how is this not a bug?

I simply open 2 terminals and connect to the same database.

**TERMINAL 1:**

sasa=# create table tst1(id int primary key);
CREATE TABLE
sasa=# insert into tst1(id) values(1);
INSERT 0 1
sasa=# begin;
BEGIN
sasa=# select * from tst1 where id = 1;
 id

  1
(1 row)

**TERMINAL 2:**

sasa=# begin;
BEGIN
sasa=# select * from tst1 where id = 1;
 id

  1
(1 row)

**TERMINAL 1:**

sasa=# delete from tst1 where id = 1;
DELETE 1
sasa=# insert into tst1(id) values(1);
INSERT 0 1

**TERMINAL 2:**

sasa=# delete from tst1 where id = 1;
// client is blocked due to row lock/index lock

**TERMINAL 1:**

sasa=# commit;
COMMIT

**TERMINAL 2:**
// resumes because terminal 1 committed transaction
DELETE 0
sasa=# insert into tst1(id) values(1);
ERROR:  duplicate key value violates unique constraint "tst1_pkey"
DETAIL:  Key (id)=(1) already exists.

To my understanding, the second client (terminal 2) should already see
changes from the first client, because the transaction isolation level is
READ COMMITTED and the first client did commit its transaction.

Thanks in advance!

Kind Regards
Sasa Vilic