SV: SV: SV: Problem with ssl and psql in Postgresql 13

[Gustavsson Mikael]

Hi,

I did some more tests to try to narrow it down. For me it only added to the 
confusion but maybe it tells you something.


Test 1:

I changed my pg_hba.conf from hostssl to host.

Now I can connect but SSL is not used even if i use require.


pgsql-13:

$ /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver
Password for user kalle:
psql (13.1)
Type "help" for help.

postgres=>

pgsql-13 with require:
$ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server 
sslmode=require"
Password for user kalle:
psql (13.1)
Type "help" for help.

postgres=>

pgsql-11 for reference:
$ /usr/pgsql-11/bin/psql -d postgres -Ukalle -hserver
Password for user kalle:
psql (11.10, server 13.1)
WARNING: psql major version 11, server major version 13.
 Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
compression: off)
Type "help" for help.

postgres=>



Test2:

It works when i connect pgsql-13 client to a postgresql-11 server. So it´s only 
the combination pgsql-13 client and postgresql-13 server that does not work.


$  /usr/pgsql-13/bin/psql -d postgres -Ukalle -hserver11
Password for user kalle:
psql (13.1, server 11.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
compression: off)
Type "help" for help.

postgres=>

KR Mikael Gustavsson, SMHI



Från: externaly-forwar...@smhi.se  för Gustavsson 
Mikael 
Skickat: den 17 december 2020 17:33:13
Till: Tom Lane
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; 
Svensson Peter
Ämne: SV: SV: SV: Problem with ssl and psql in Postgresql 13


Here is the result.

ldd /usr/pgsql-13/bin/psql
linux-vdso.so.1 (0x7ffd714d5000)
libpq.so.5 => /usr/pgsql-13/lib/libpq.so.5 (0x7f2d1700a000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x7f2d16dea000)
libreadline.so.7 => /lib64/libreadline.so.7 (0x7f2d16b9b000)
libm.so.6 => /lib64/libm.so.6 (0x7f2d16819000)
libc.so.6 => /lib64/libc.so.6 (0x7f2d16456000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x7f2d161c2000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x7f2d15cdc000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7f2d15a87000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7f2d1583)
/lib64/ld-linux-x86-64.so.2 (0x7f2d1725b000)
libtinfo.so.6 => /lib64/libtinfo.so.6 (0x7f2d15603000)
libz.so.1 => /lib64/libz.so.1 (0x7f2d153ec000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f2d151e8000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f2d14eff000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f2d14ce8000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f2d14ae4000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7f2d148d3000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f2d146cf000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7f2d144b8000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f2d142a8000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f2d1408a000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x7f2d13e6)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f2d13c37000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x7f2d139b3000)

ldd /usr/pgsql-13/lib/libpq.so.5
linux-vdso.so.1 (0x7fff51f79000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x7f88432d1000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x7f8842deb000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x7f8842b96000)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7f884293f000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x7f884271f000)
libc.so.6 => /lib64/libc.so.6 (0x7f884235c000)
libz.so.1 => /lib64/libz.so.1 (0x7f8842145000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f8841f41000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f8841c58000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f8841a41000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f884183d000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x7f884162c000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f8841428000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7f8841211000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f8841001000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f8840de3000)
/lib64/ld-linux-x86-64.so.2 (0x7f88437b6000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x7f8840bb9000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f884099)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x7f884070c000)


/Mikael


Från: Tom Lane 
Skickat: den 17 december 2020 17:25:31
Till: Gustavsson Mikael
Kopia: Magnus Hagander; Kyotaro Horiguchi; pgsql-gene...@postgresql.org; 
Svensson Peter
Ämne: Re: SV: SV: Problem with ssl and psql in Postgresql 13

Gustavsson Mikael  writes:
> $ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server 
> sslmode=require"
> psql: error: FATAL:  no pg_hba.conf entry for host "nn.nnn.n.nnn", user 
> "kalle", database "postgres", SSL

Missing rows after logical replication in new primary

[Lars Vonk]

Hi,

We migrated from postgres 11 to 12 using logical replication. Today we
noticed that one table is missing 1252 rows after the replication finished
and we flipped to the new primary (we still have the old so we can recover).

We see that these rows were inserted in the table after starting the
initial copy of the table. Most of the missing rows seem from new inserts
happening **during the initial copy** (1230) and the rest (22) from inserts
**during the period the replication ran** (7 days).

This table is a (for us) high volume table (> 400.000.000 rows), with daily
> 150.000  new inserts.

We took a per-table approach for the replication and this table was the
last table we started in our replication.

We did some sanity checks before we switched to the new master, like
comparing max(id) to see if the replica was up to date (including this
table) and counts on some tables and that all checked out okay.

So how can this happen? For now it seems that only this table suffered from
it, but we are pretty 'scared' more tables are affected, so we will have to
check them all.

Lars

Re: Unexpected result count from update statement on partitioned table

[Laurenz Albe]

On Thu, 2020-12-17 at 12:21 -0500, Craig McIlwee wrote:
> Our application uses a queue-like table to assign tasks to users and this has 
> worked well for us for a few years.  Now we are in the process of adding some 
> restrictions to which tasks a user can
> work on and that is based on an attribute of each task that does not change 
> for the task's lifespan.  Users may have access to work on one or more or 
> types of tasks.  To improve query time when
> finding the set of tasks that we assign, we are introducing partitioning into 
> our task queue table.  When assigning tasks, we issue an update statement to 
> mark the tasks as reserved using a subquery
> that orders the tasks by age.  With the introduction of partitioning, we are 
> seeing that the update statement affects more rows than expected.  An example 
> query is:
> 
> ---
> update task_parent
> set reserved = true
> from (
>   select id
>   from task_parent
>   where reserved = false
> and task_type = 1 or task_type = 2
>   order by task_timestamp
>   limit 50
>   for update skip locked) as sub
> where sub.id = task_parent.id
> returning task_parent.id
> ---  
> 
> In the statement above, we have a subquery to limit the number of tasks to 50 
> yet the update statement sometimes returns more than 50 records.  I have 
> narrowed this down to a small, reproducible
> example shown below.  The first time I run the update statement I get ~65 
> records, then typically ~53 the next few runs, and then it starts 
> consistently giving me 50 records after that.  Then if I
> bump the limit to 100 I will get more than 100 initially and after several 
> executions it starts to settle into always giving the expected 100.
> 
> Below is the full setup that can be used to reproduce what I'm seeing.  It 
> was initially observed on PostgreSQL 11.8 but I can also reproduce it on 13.0.
> 
> ---
> create table task_parent (
>   id bigint not null,
>   task_type smallint not null,
>   reserved boolean not null,
>   task_timestamp timestamp not null
> ) partition by list (task_type);
> 
> create table task_child_1
> partition of task_parent for values in (1);
> 
> create table task_child_2
> partition of task_parent for values in (2);
> 
> insert into task_parent
> select
>   generate_series(1, 50),
>   case when random() < 0.5 then 1 else 2 end,
>   false,
>   now() - (random() * '1 day'::interval);
>   
> create index task_parent_task_time_idx
> on task_parent (task_timestamp);
> 
> update task_parent
> set reserved = true
> from (
>   select id
>   from task_parent
>   where reserved = false
> and task_type = 1 or task_type = 2
>   order by task_timestamp
>   limit 50
>   for update skip locked) as sub
> where sub.id = task_parent.id
> returning task_parent.id;
> ---
> 
> A couple of interesting observations:
> 1) If I remove the order by clause I always get the expected number of results
> 2) If I rewrite the query to use a CTE for the task IDs instead of a subquery 
> then I always get the expected number of results
> 
> At its surface, this seems like it could be a bug but maybe there is 
> something about this usage pattern that is known/expected to cause this 
> behavior.  So that's the question - is this a bug that
> should be reported to pgsql-bugs, or is this expected and if so, why?

Yes, this must be a bug:

EXPLAIN (COSTS OFF) update task_parent
set reserved = true
from (
  select id
  from task_parent
  where reserved = false
and task_type = 1 or task_type = 2
  order by task_timestamp
  limit 50
  for update skip locked) as sub
where sub.id = task_parent.id
returning task_parent.id;

QUERY PLAN  
  
--
 Update on task_parent
   Update on task_child_1 task_parent_1
   Update on task_child_2 task_parent_2
   ->  Hash Join
 Hash Cond: (task_parent_1.id = sub.id)
 ->  Seq Scan on task_child_1 task_parent_1
 ->  Hash
   ->  Subquery Scan on sub
 ->  Limit
   ->  LockRows
 ->  Merge Append
   Sort Key: task_parent_3.task_timestamp
   ->  Index Scan using 
task_child_1_task_timestamp_idx on task_child_1 task_parent_4
 Filter: (((NOT reserved) AND 
(task_type = 1)) OR (task_type = 2))
   ->  Index Scan using 
task_child_2_task_timestamp_idx on task_child_2 task_parent_5
 Filter: (((NOT reserved) AND 
(task_type = 1)) OR (task_type = 2))
   ->  Hash Join
 Hash Cond: (task_parent_2.id = sub_1.id)
 ->  Seq Scan on task_child_2 task_parent_2
 ->  Hash
   ->  Subquery Scan on sub_1

Re: Unexpected result count from update statement on partitioned table

[Craig McIlwee]

Despite looking at this query on and off for a couple of days, it wasn't
until seeing it in Lauenz's reply that I noticed  a logical issue with the
query that changes things a bit.  There should be parenthesis around the
task_type predicates, otherwise you end up getting reserved rows in the
result set.  After looking at it more, I see that when the original query
settles in to consistently returning the expected number of results they
happen to be the exact same results each time.  Correcting the logical
error in the query solves that and now consistently gives too many results,
regardless of the number of executions.

Corrected query (but still not working properly):

update task_parent
set reserved = true
from (
  select id
  from task_parent
  where reserved = false
and (task_type = 1 or task_type = 2)
  order by task_timestamp
  limit 50
  for update skip locked) as sub
where sub.id = task_parent.id
returning *;



> Yes, this must be a bug:
>
> EXPLAIN (COSTS OFF) update task_parent
> set reserved = true
> from (
>   select id
>   from task_parent
>   where reserved = false
> and task_type = 1 or task_type = 2
>   order by task_timestamp
>   limit 50
>   for update skip locked) as sub
> where sub.id = task_parent.id
> returning task_parent.id;
>
> QUERY PLAN
>
>
> --
>  Update on task_parent
>Update on task_child_1 task_parent_1
>Update on task_child_2 task_parent_2
>->  Hash Join
>  Hash Cond: (task_parent_1.id = sub.id)
>  ->  Seq Scan on task_child_1 task_parent_1
>  ->  Hash
>->  Subquery Scan on sub
>  ->  Limit
>->  LockRows
>  ->  Merge Append
>Sort Key:
> task_parent_3.task_timestamp
>->  Index Scan using
> task_child_1_task_timestamp_idx on task_child_1 task_parent_4
>  Filter: (((NOT reserved) AND
> (task_type = 1)) OR (task_type = 2))
>->  Index Scan using
> task_child_2_task_timestamp_idx on task_child_2 task_parent_5
>  Filter: (((NOT reserved) AND
> (task_type = 1)) OR (task_type = 2))
>->  Hash Join
>  Hash Cond: (task_parent_2.id = sub_1.id)
>  ->  Seq Scan on task_child_2 task_parent_2
>  ->  Hash
>->  Subquery Scan on sub_1
>  ->  Limit
>->  LockRows
>  ->  Merge Append
>Sort Key:
> task_parent_6.task_timestamp
>->  Index Scan using
> task_child_1_task_timestamp_idx on task_child_1 task_parent_7
>  Filter: (((NOT reserved) AND
> (task_type = 1)) OR (task_type = 2))
>->  Index Scan using
> task_child_2_task_timestamp_idx on task_child_2 task_parent_8
>  Filter: (((NOT reserved) AND
> (task_type = 1)) OR (task_type = 2))
> (29 rows)
>
> The subquery is executed twice, and the two executions obviously don't
> return the same results.  I am at a loss for an explanation ...


I did notice that the subquery is executed twice and this is the difference
between the subquery and CTE since the CTE is only executed once and then
the CTE result is used in the joins against the child tables.  Beyond that,
I was unable to rationalize what was going on.  One thing I don't know is
if the subquery should be executed more than once.  If yes, then like you
said, I would think that they should give the same results.

Another interesting thing I noticed is that multiple executions give tasks
with overlapping time ranges.  Given 2 executions, the task_time ordering
should give the oldest 50 first and then the next oldest 50 after that.  In
reality, I see that the second execution has tasks that are older than the
newest timestamp of the first execution.  Wrapping the update statement in
a CTE and then picking out the min/max shows this:

with updated as (
  update task_parent
  set reserved = true
  from (
select id, task_type
from task_parent
where reserved = false
  and (task_type = 1 or task_type = 2)
order by task_timestamp
limit 50
for update skip locked) as sub
  where sub.id = task_parent.id
and sub.task_type = task_parent.task_type
  returning *
)
select min(task_timestamp), max(task_timestamp)
from updated;

Execution 1:
min |max
+
 2020-12-17 11:44:51.192119 | 2020-12-17 11:45:03.881409

Execution 2:
min |

Re: Unexpected result count from update statement on partitioned table

[Ron]

Would (task_type in (1,2)) make any logical difference?

On 12/18/20 6:11 AM, Craig McIlwee wrote:
Despite looking at this query on and off for a couple of days, it wasn't 
until seeing it in Lauenz's reply that I noticed  a logical issue with the 
query that changes things a bit.  There should be parenthesis around the 
task_type predicates, otherwise you end up getting reserved rows in the 
result set.  After looking at it more, I see that when the original query 
settles in to consistently returning the expected number of results they 
happen to be the exact same results each time.  Correcting the logical 
error in the query solves that and now consistently gives too many 
results, regardless of the number of executions.


Corrected query (but still not working properly):

update task_parent
set reserved = true
from (
  select id
  from task_parent
  where reserved = false
    and (task_type = 1 or task_type = 2)
  order by task_timestamp
  limit 50
  for update skip locked) as sub
where sub.id  = task_parent.id 
returning *;

Yes, this must be a bug:

EXPLAIN (COSTS OFF) update task_parent
set reserved = true
from (
  select id
  from task_parent
  where reserved = false
    and task_type = 1 or task_type = 2
  order by task_timestamp
  limit 50
  for update skip locked) as sub
where sub.id  = task_parent.id 
returning task_parent.id ;

                                                        QUERY PLAN

--
 Update on task_parent
   Update on task_child_1 task_parent_1
   Update on task_child_2 task_parent_2
   ->  Hash Join
         Hash Cond: (task_parent_1.id  =
sub.id )
         ->  Seq Scan on task_child_1 task_parent_1
         ->  Hash
               ->  Subquery Scan on sub
                     ->  Limit
                           ->  LockRows
                                 ->  Merge Append
                                       Sort Key:
task_parent_3.task_timestamp
                                       ->  Index Scan using
task_child_1_task_timestamp_idx on task_child_1 task_parent_4
                                             Filter: (((NOT reserved)
AND (task_type = 1)) OR (task_type = 2))
                                       ->  Index Scan using
task_child_2_task_timestamp_idx on task_child_2 task_parent_5
                                             Filter: (((NOT reserved)
AND (task_type = 1)) OR (task_type = 2))
   ->  Hash Join
         Hash Cond: (task_parent_2.id  =
sub_1.id )
         ->  Seq Scan on task_child_2 task_parent_2
         ->  Hash
               ->  Subquery Scan on sub_1
                     ->  Limit
                           ->  LockRows
                                 ->  Merge Append
                                       Sort Key:
task_parent_6.task_timestamp
                                       ->  Index Scan using
task_child_1_task_timestamp_idx on task_child_1 task_parent_7
                                             Filter: (((NOT reserved)
AND (task_type = 1)) OR (task_type = 2))
                                       ->  Index Scan using
task_child_2_task_timestamp_idx on task_child_2 task_parent_8
                                             Filter: (((NOT reserved)
AND (task_type = 1)) OR (task_type = 2))
(29 rows)

The subquery is executed twice, and the two executions obviously don't
return the same results.  I am at a loss for an explanation ... 



I did notice that the subquery is executed twice and this is the 
difference between the subquery and CTE since the CTE is only executed 
once and then the CTE result is used in the joins against the child 
tables.  Beyond that, I was unable to rationalize what was going on.  One 
thing I don't know is if the subquery should be executed more than once.  
If yes, then like you said, I would think that they should give the same 
results.


Another interesting thing I noticed is that multiple executions give tasks 
with overlapping time ranges.  Given 2 executions, the task_time ordering 
should give the oldest 50 first and then the next oldest 50 after that.  
In reality, I see that the second execution has tasks that are older than 
the newest timestamp of the first execution.  Wrapping the update 
statement in a CTE and then picking out the min/max shows this:


with updated as (
  update task_parent
  set reserved = true
  from (
    select id, task_type
    from task_parent
    where reserved = false
      and (task_type = 1 or task_type = 2)
    order by task_

Avoid undesired flattening of jsonb arrays?

[Joel Jacobson]

The || operator for the jsonb type has a surprising behaviour.

Instead of appending the right operand "as is" to the left operand,
it has a magic behaviour if the right operand is an array,
in which case it will append the items of the array,
instead of appending the array itself as a single value.

Example:

SELECT '[10,20]'::jsonb || '30'::jsonb;
[10, 20, 30]

SELECT '[10,20]'::jsonb || '[30]'::jsonb;
[10, 20, 30]

Since [10, 20, [30]] is desired in our case, we must use jsonb_insert() to 
work-around the problem in a not very nice way:

SELECT jsonb_insert('[10,20]'::jsonb,'{-1}','[30]'::jsonb,TRUE);
[10, 20, [30]]

Suggestions welcome if there is a better way to solve this problem.

Best regards,

Joel

Re: Avoid undesired flattening of jsonb arrays?

[David G. Johnston]

On Fri, Dec 18, 2020 at 8:24 AM Joel Jacobson  wrote:

> The || operator for the jsonb type has a surprising behaviour.
>
> Instead of appending the right operand "as is" to the left operand,
> it has a magic behaviour if the right operand is an array,
> in which case it will append the items of the array,
> instead of appending the array itself as a single value.
>
>
It's not magic, and it is documented clearly.

I'll agree that the description could discuss the case explicitly, and the
array||scalar case could be added to the examples.


> Suggestions welcome if there is a better way to solve this problem.
>
>
As you are writing literals just put an array in the to-be-merged array.

select '["a","b"]'::jsonb || '[["c","d"]]'::jsonb

David J.

Missing rows after migrating from postgres 11 to 12 with logical replication

[Lars Vonk]

Hi,

We migrated from postgres 11 to 12 using logical replication (over local
network). Today we noticed that one table is missing 1252 rows after the
replication finished and we flipped to the new primary (we still have the
old master database so we can recover).

We see that these rows were inserted in the table after starting the
initial copy of the table. Most of the missing rows seem from new inserts
happening **during the initial copy** (1230) and the rest (22) from inserts
**during the period the replication ran** (7 days).

After further investigation unfortunately more tables have missing rows,
all of them are after the initial table copy phase. We took a per-table
approach for the replication, starting with creating an empty publication
and adding tables via

ALTER PUBLICATION pg12_migration ADD TABLE FOO

After that we refreshed the publication on the "new postgres 12 primary"
using

ALTER SUBSCRIPTION pg12_migration REFRESH PUBLICATION;

We only added new tables after the the initial copy of the previous was
done (the internal state was replicating).

We never stopped the subscriptions during all this and we started with a
fresh schema.

We did some sanity checks before we switched to the new master, like
comparing max(id) to see if the replica was up to date (including this
table) and counts on some smaller tables and that all checked out okay, we
never thought of missing rows somewhere in between

So how can this happen?

Lars

Re: Avoid undesired flattening of jsonb arrays?

[Tom Lane]

"David G. Johnston"  writes:
> I'll agree that the description could discuss the case explicitly, and the
> array||scalar case could be added to the examples.

Yeah, the documentation completely fails to explain what happens
when the inputs aren't two arrays or two objects.  I'd kind of assumed
that that's an error, but it isn't.  Some experimentation indicates
that the behavior in all cases except two objects is to convert any
non-array input to a one-element array, reducing the situation to the
two-array case.

regards, tom lane

Re: Avoid undesired flattening of jsonb arrays?

[Joel Jacobson]

The following nicer work-around was suggested to me by Andreas Karlsson:

-jsonb_insert(x.jsonb_array,'{-1}',next_item.item,TRUE)
+x.jsonb_array || jsonb_build_array(next_item.item)


On Fri, Dec 18, 2020, at 17:20, Tom Lane wrote:
> "David G. Johnston"  writes:
> > I'll agree that the description could discuss the case explicitly, and the
> > array||scalar case could be added to the examples.
> 
> Yeah, the documentation completely fails to explain what happens
> when the inputs aren't two arrays or two objects.  I'd kind of assumed
> that that's an error, but it isn't.  Some experimentation indicates
> that the behavior in all cases except two objects is to convert any
> non-array input to a one-element array, reducing the situation to the
> two-array case.
> 
> regards, tom lane
> 

Re: Unexpected result count from update statement on partitioned table

[Tom Lane]

Laurenz Albe  writes:
> The subquery is executed twice, and the two executions obviously don't
> return the same results.  I am at a loss for an explanation ...

Yeah, this is a fairly fundamental shortcoming in inheritance_planner():
it supposes that it can duplicate the whole query for each target table.
If you have a sub-SELECT that generates unstable results, then the
duplicated copies don't necessarily generate the same results.
And multiple executions of a sub-SELECT with "for update skip locked"
are guaranteed to not give the same results, because the second one
will skip the row(s) already locked by the first one.

It seems to work as desired if you stick the unstable result into a CTE:

=# explain
with sub as (select id
  from task_parent
  where reserved = false
and task_type = 1 or task_type = 2
  order by task_timestamp
  limit 50
  for update skip locked)
update task_parent
set reserved = true
from sub
where sub.id = task_parent.id
returning task_parent.id;
  QUERY 
PLAN  
--
 Update on task_parent  (cost=6.30..10069.93 rows=100 width=57)
   Update on task_child_1 task_parent_1
   Update on task_child_2 task_parent_2
   CTE sub
 ->  Limit  (cost=0.85..4.68 rows=50 width=26)
   ->  LockRows  (cost=0.85..38252.82 rows=50 width=26)
 ->  Merge Append  (cost=0.85..33252.82 rows=50 width=26)
   Sort Key: task_parent_3.task_timestamp
   ->  Index Scan using task_child_1_task_timestamp_idx on 
task_child_1 task_parent_4  (cost=0.42..14123.60 rows=249960 width=26)
 Filter: (((NOT reserved) AND (task_type = 1)) OR 
(task_type = 2))
   ->  Index Scan using task_child_2_task_timestamp_idx on 
task_child_2 task_parent_5  (cost=0.42..14129.20 rows=250040 width=26)
 Filter: (((NOT reserved) AND (task_type = 1)) OR 
(task_type = 2))
   ->  Hash Join  (cost=1.62..5032.07 rows=50 width=57)
 Hash Cond: (task_parent_1.id = sub.id)
 ->  Seq Scan on task_child_1 task_parent_1  (cost=0.00..4092.60 
rows=249960 width=24)
 ->  Hash  (cost=1.00..1.00 rows=50 width=40)
   ->  CTE Scan on sub  (cost=0.00..1.00 rows=50 width=40)
   ->  Hash Join  (cost=1.62..5033.18 rows=50 width=57)
 Hash Cond: (task_parent_2.id = sub.id)
 ->  Seq Scan on task_child_2 task_parent_2  (cost=0.00..4093.40 
rows=250040 width=24)
 ->  Hash  (cost=1.00..1.00 rows=50 width=40)
   ->  CTE Scan on sub  (cost=0.00..1.00 rows=50 width=40)
(22 rows)

It's been obvious for some time that inheritance_planner() needs to
be nuked from orbit, because aside from this fundamental semantic
issue it's got horrible performance problems with large inheritance
trees (ie many partitions).  We might finally get that done for v14
--- at least, there's a patch in the queue about it.  In existing
releases, I recommend the CTE solution.

regards, tom lane

Re: SV: SV: SV: Problem with ssl and psql in Postgresql 13

[Tom Lane]

Gustavsson Mikael  writes:
> pgsql-13 with require:
> $ /usr/pgsql-13/bin/psql "dbname=postgres user=kalle host=server 
> sslmode=require"
> Password for user kalle:
> psql (13.1)
> Type "help" for help.

That is just bizarre.  libpq should not ignore the sslmode=require option
like that, unless it thinks it's making a Unix-socket connection, which
it should not think given the host specification.  (There's not a slash
in your server's real name, is there?  But if there was, v11 should
misbehave too.)

It seems like there must be some environment setting, or maybe a service
file, changing the behavior from what it should be on its face.  But
that theory has big flaws too: an explicit sslmode=require setting should
not be overridable from environment, and even if it was, why wouldn't v11
act the same?

The only other conclusion I can think of is that your copy of libpq.so
is broken.  Maybe you should try redownloading/reinstalling v13.

regards, tom lane

Re: Unexpected result count from update statement on partitioned table

[Michael Lewis]

On Fri, Dec 18, 2020 at 12:16 PM Tom Lane  wrote:

> Laurenz Albe  writes:
> > The subquery is executed twice, and the two executions obviously don't
> > return the same results.  I am at a loss for an explanation ...
>
> Yeah, this is a fairly fundamental shortcoming in inheritance_planner():
> it supposes that it can duplicate the whole query for each target table.
> If you have a sub-SELECT that generates unstable results, then the
> duplicated copies don't necessarily generate the same results.
> And multiple executions of a sub-SELECT with "for update skip locked"
> are guaranteed to not give the same results, because the second one
> will skip the row(s) already locked by the first one.
>

Are there other examples of gotchas with this? Would it be any volatile
function (or behavior like skip locked) in a sub-query? It isn't apparent
to me why the subquery is executed twice for this example either and since
that is a pre-req for hitting this unexpected situation... what is the
factor that means the sub-query would be executed multiple times?

With the behavior change for CTEs to no longer be materialized by default
in PG12... why does the CTE still mean it is executed only once? Is it
because it is NOT side effect free (locking) so it cannot be in-lined? If
it were a volatile function instead, might we have gotten more than 50 rows
updated?

Re: Unexpected result count from update statement on partitioned table

[Tom Lane]

Michael Lewis  writes:
> On Fri, Dec 18, 2020 at 12:16 PM Tom Lane  wrote:
>> Yeah, this is a fairly fundamental shortcoming in inheritance_planner():
>> it supposes that it can duplicate the whole query for each target table.

> Are there other examples of gotchas with this? Would it be any volatile
> function (or behavior like skip locked) in a sub-query?

Right, anything that causes multiple executions to not deliver identical
results.  The different executions will use the same snapshot, so there's
not a hazard from external changes to the DB, but internal sources of
nonrepeatability are a problem.

> ... what is the
> factor that means the sub-query would be executed multiple times?

If it's in the FROM clause of an UPDATE or DELETE on a table with
inheritance children (either traditional inheritance or partitioning).

Actually, after further thought, I'm not entirely sure that the issue is
confined to inherited UPDATE/DELETE.  If you had such a sub-SELECT in
an ordinary join, and the planner chose to put it on the inside of a
nestloop, you'd have a problem.  I do not think there's any check to
avoid doing that just because the subquery's results are potentially
volatile.  Probably evaluation-cost considerations would discourage
such a plan in most cases, but there's no direct defense AFAIR.

> With the behavior change for CTEs to no longer be materialized by default
> in PG12... why does the CTE still mean it is executed only once? Is it
> because it is NOT side effect free (locking) so it cannot be in-lined?

Exactly.

regards, tom lane

Upgrade check failed from 11.5 to 12.1

[Lu, Dan]

Hello PostgreSQL experts,

I am trying to upgrade an instance of PostgreSQL in unix from 11.5 to 12.1.

I got the following error on upgrade verification step.

Is this not allowed?


UNIX:> pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc/data -b 
/hostname/pg/PostgreSQL-11.5/bin -B /hostname/pg/PostgreSQL-12.1/bin -p 5432 -P 
9432 -c -v
Performing Consistency Checks
-
Checking cluster versions
New cluster data and binary directories are from different major versions.
Failure, exiting



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

Re: Upgrade check failed from 11.5 to 12.1

[Tom Lane]

"Lu, Dan"  writes:
> I am trying to upgrade an instance of PostgreSQL in unix from 11.5 to 12.1.

> UNIX:> pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc/data -b 
> /hostname/pg/PostgreSQL-11.5/bin -B /hostname/pg/PostgreSQL-12.1/bin -p 5432 
> -P 9432 -c -v

No, you can't use the same directory to hold old and new versions at the
same time.  After you're done with the upgrade, you could move the new
data directory to be where the old one had been.

regards, tom lane

Avoid excessive inlining?

[Joel Jacobson]

Is there a way to avoid excessive inlining when writing pure SQL functions, 
without having to use PL/pgSQL?

The JOIN LATERAL and Nested Subqueries versions run much slower than the 
PL/pgSQL version:

Execution Times:
JOIN LATERAL: 12198.010 ms
Nested Subqueries: 12250.077 ms
PL/pgSQL: 312.493 ms

The three functions below are equivalent, they all compute the Eastern date for 
a given year.

CREATE OR REPLACE FUNCTION easter_lateral(year integer)
RETURNS DATE
LANGUAGE sql
AS $$
SELECT make_date(year, easter_month, easter_day)
FROM (VALUES (year % 19, year / 100)) AS Q1(g,c)
JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30)) AS Q2(h) ON 
TRUE
JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11 AS 
Q3(i) ON TRUE
JOIN LATERAL (VALUES ((year + year/4 + i + 2 - c + c/4) % 7)) AS Q4(j) ON TRUE
JOIN LATERAL (VALUES (i - j)) AS Q5(p) ON TRUE
JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) % 31)) AS 
Q6(easter_month, easter_day) ON TRUE
$$;

CREATE OR REPLACE FUNCTION easter_nested_subqueries(year integer)
RETURNS DATE
LANGUAGE sql
AS $$
SELECT make_date(year, easter_month, easter_day)
FROM (
  SELECT *,
3 + (p + 26)/30 AS easter_month,
1 + (p + 27 + (p + 6)/40) % 31 AS easter_day
  FROM (
SELECT *,
  i - j AS p
FROM (
  SELECT *,
  (year + year/4 + i + 2 - c + c/4) % 7 AS j
  FROM (
SELECT *,
  h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i
FROM (
  SELECT *,
(c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h
  FROM (
SELECT
  year % 19 AS g,
  year / 100 AS c
  ) AS Q1
) AS Q2
  ) AS Q3
) AS Q4
  ) AS Q5
) AS Q6
$$;

CREATE OR REPLACE FUNCTION easter_plpgsql(year integer)
RETURNS date
LANGUAGE plpgsql
AS $$
-- Based on: 
https://github.com/christopherthompson81/pgsql_holidays/blob/master/utils/easter.pgsql
DECLARE
g CONSTANT integer := year % 19;
c CONSTANT integer := year / 100;
h CONSTANT integer := (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30;
i CONSTANT integer := h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11));
j CONSTANT integer := (year + year/4 + i + 2 - c + c/4) % 7;
p CONSTANT integer := i - j;
BEGIN
RETURN make_date(
  year,
  3 + (p + 26)/30,
  1 + (p + 27 + (p + 6)/40) % 31
);
END;
$$;

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT easter_lateral(year) AS 
easter FROM generate_series(1,10) AS year) AS x;
 QUERY PLAN

Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual 
time=12195.974..12195.974 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=10 
width=4) (actual time=15.840..12167.758 rows=10 loops=1)
Planning Time: 0.262 ms
Execution Time: 12198.010 ms
(4 rows)

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT 
easter_nested_subqueries(year) AS easter FROM generate_series(1,10) AS 
year) AS x;
 QUERY PLAN

Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual 
time=12248.316..12248.317 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=10 
width=4) (actual time=17.707..12219.500 rows=10 loops=1)
Planning Time: 0.277 ms
Execution Time: 12250.077 ms
(4 rows)

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT easter_plpgsql(year) AS 
easter FROM generate_series(1,10) AS year) AS x;
QUERY PLAN
--
Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual 
time=311.107..311.108 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=10 
width=4) (actual time=12.369..296.221 rows=10 loops=1)
Planning Time: 0.058 ms
Execution Time: 312.493 ms
(4 rows)

If we look at the plan for the lateral and subqueries versions, we can see how 
the inlining expands to huge expressions.

Could this be the reason they run so much slower than the PL/pgSQL version?

PREPARE q_lateral AS SELECT make_date($1, easter_month, easter_day)
FROM (VALUES ($1 % 19, $1 / 100)) AS Q1(g,c)
JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30)) AS
Q2(h) ON TRUE
JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 -
g)/11 AS Q3(i) ON TRUE
JOIN LATERAL (VALUES (($1 + $1/4 + i + 2 - c + c/4) % 7)) AS Q4(j) ON TRUE
JOIN LATERAL (VALUES (i - j)) AS Q5(p) ON TRUE
JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) % 31))
AS Q6(