RE: How to use 32 bit ODBC driver

[Aditya Bhardwaj]

Yes, I installed it. However, following installation, it shows up in the 64-bit 
ODBC Data Source but not the 32-bit.

Regards,
Aditya Bhardwaj
From: Rino Mardo 
Sent: 21 June 2022 06:57 PM
To: Aditya Bhardwaj 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to use 32 bit ODBC driver

hi. you didn't mention, after downloading did you install it?

On Tue, 21 Jun 2022, 9:11 pm Aditya Bhardwaj 
mailto:aditya.bhard...@datatheta.com>> wrote:
Hi,
For Postgres, I'd like to utilize a 32-bit ODBC driver. I attempted to download 
it, but it does not appear in the 32-bit ODBC Data Source. Please assist me in 
locating or downloading the PostgresSql 32Bit OBDC driver.

Regards,
Aditya Bhardwaj
Disclaimer: The content of this email is confidential and intended for the 
recipient specified in message only. It is strictly forbidden to share any part 
of this message with any third party, without a written consent of the sender. 
If you received this message by mistake, please reply to this message and 
follow with its deletion, so that we can ensure such a mistake does not occur 
in the future.
Disclaimer: The content of this email is confidential and intended for the 
recipient specified in message only. It is strictly forbidden to share any part 
of this message with any third party, without a written consent of the sender. 
If you received this message by mistake, please reply to this message and 
follow with its deletion, so that we can ensure such a mistake does not occur 
in the future.
Disclaimer: The content of this email is confidential and intended for the 
recipient specified in message only. It is strictly forbidden to share any part 
of this message with any third party, without a written consent of the sender. 
If you received this message by mistake, please reply to this message and 
follow with its deletion, so that we can ensure such a mistake does not occur 
in the future.

PostgreSQL with Patroni not replicating to all nodes after adding 3rd node (another secondary)

[Zb B]

Hi,
I am new to Patroni and PostgreSQL.We have set up a cluster with etcd (3
nodes), Patroni (2 nodes) and PostgreSQL (2 nodes) with replication from
primary to secondary.In SYNC mode. Seemed to work fine. Then I added a
third DB node without Patroni - just to replicate the data from the primary
using:
1) added another slot in patroni.yml:
slots:
  bdc2b:
type: physical

2) used
pg_basebackup -v -R -h 10.17.5.211,10.17.5.83 -U replication --slot=bdc2b
-D 14/data

As a result the primary DB was showing two replication slots and the
Patroni cluster looked healthy by executing:
patronictl -c /etc/patroni/patroni.yml list

(the Leader and replica were running)

But when I started my remote test application that was executing small
insert transactions I noticed the records are replicated to the 3rd node
only (the secondary without Patroni). They are not replicated to secondary
node (the Replica with Patroni)
Some debugging using
journalctl -f
shows that the replica is not healthy and after a while the replication
slot becomes inactive. See the log below:

Jun 22 08:06:35 xyzd3riardb02 patroni[12495]: 2022-06-22 08:06:35,280 INFO:
Got response from xyzd3riardb01 http://10.17.5.211:8008/patroni: {"state":
"running", "postmaster_start_time": "2022-06-22 05:05:37.382607-04:00",
"role": "master", "server_version": 140004, "xlog": {"location":
117558448}, "timeline": 4, "replication": [{"usename": "replication",
"application_name": "test1b", "client_addr": "10.17.5.56", "state":
"streaming", "sync_state": "async", "sync_priority": 0}, {"usename":
"replication", "application_name": "xyzd3riardb02", "client_addr":
"10.17.5.83", "state": "streaming", "sync_state": "sync", "sync_priority":
1}], "dcs_last_seen": 1655899566, "database_system_identifier":
"7111967488904966919", "patroni": {"version": "2.1.4", "scope": "test1b"}}
Jun 22 08:06:35 xyzd3riardb02 patroni[12495]: 2022-06-22 08:06:35,375
WARNING: Master (xyzd3riardb01) is still alive
Jun 22 08:06:35 xyzd3riardb02 patroni[12495]: server signaled
Jun 22 08:06:35 xyzd3riardb02 patroni[12495]: 2022-06-22 08:06:35,400 INFO:
following a different leader because i am not the healthiest node
Jun 22 08:07:05 xyzd3riardb02 patroni[12495]: 2022-06-22 08:07:05,279 INFO:
Got response from xyzd3riardb01 http://10.17.5.211:8008/patroni: {"state":
"running", "postmaster_start_time": "2022-06-22 05:05:37.382607-04:00",
"role": "master", "server_version": 140004, "xlog": {"location":
117558448}, "timeline": 4, "replication": [{"usename": "replication",
"application_name": "test1b", "client_addr": "10.17.5.56", "state":
"streaming", "sync_state": "async", "sync_priority": 0}], "dcs_last_seen":
1655899596, "database_system_identifier": "7111967488904966919", "patroni":
{"version": "2.1.4", "scope": "test1b"}}
Jun 22 08:07:05 xyzd3riardb02 patroni[12495]: 2022-06-22 08:07:05,374
WARNING: Master (xyzd3riardb01) is still alive
Jun 22 08:07:05 xyzd3riardb02 patroni[12495]: 2022-06-22 08:07:05,393 INFO:
following a different leader because i am not the healthiest node

But the Patroni cluster still looks healthy after executing
patronictl -c /etc/patroni/patroni.yml list

while not replicating the records to the replica.
What can be the reason? Where to look for the problem?

Thanks,

Zbigniew

RE: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mahendrakar, Prabhakar - Dell Team]

Hello there,

Please find my response below

> You should not be using 13.4, but the latest minor release, at this point 
> 13.7.
May be in the near future we would migrate to 13.7 and not now at 
present.

> Anyway, the error message looks like somebody removed the contents of the 
> "pg_wal"
directory.  How exactly did you upgrade PostgreSQL?
We are using the pg_upgrade utility for doing the upgrade.

Thanks,
Prabhakar


Internal Use - Confidential

-Original Message-
From: Laurenz Albe  
Sent: Friday, June 17, 2022 4:10 PM
To: Mahendrakar, Prabhakar - Dell Team; pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL] 

On Fri, 2022-06-17 at 06:35 +, Mahendrakar, Prabhakar - Dell Team wrote:
> We are facing checkpoint related issues from PostGreSQL 13.4 ( could not 
> locate a valid checkpoint record) and Postgres service fails to come up.
> ==
> ==
> LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc 
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
> LOG:  listening on IPv4 address "127.0.0.1", port 9003
> LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003
> LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"
> LOG:  database system was shut down at 2022-06-09 10:19:24 CEST
> LOG:  invalid primary checkpoint record
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 8773) was terminated by signal 6: Aborted
> LOG:  aborting startup due to startup process failure
> LOG:  database system is shut down
>  
> This issue is seen in both Windows and Linux OS platforms. 
>  
> To Brief on the Scenario: Our product say example with Version A1 uses 
> Postgres 10 and in the latest version of our product (Say A2) we upgraded the 
> Postgres to 13.
> When we try to upgrade our Product through InstallAnyWhere from A1 to 
> A2, Postgres service fails with above mentioned error.

You should not be using 13.4, but the latest minor release, at this point 13.7.

Anyway, the error message looks like somebody removed the contents of the 
"pg_wal"
directory.  How exactly did you upgrade PostgreSQL?

Yours,
Laurenz Albe
--
Cybertec | 
https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!LpKI!ihYHnSm7w4UDJHWNlSnVnxBN704J-jkctGZIQ9NPj3f9UeHxY5pPnRnpPcWN5mmrWxlXc9hVyRd8AkHaAPLaXuRtVz0WxIaDVoTQ$
 [cybertec-postgresql[.]com]

RE: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mahendrakar, Prabhakar - Dell Team]

Hello there,

Please find my response as below:


  *   Also, are you able to reproduce the issue?

No we are not able to reproduce at our end. But this is happening occasionally 
at customer environment that has large DB size.

  *   I.e. try restoring the database to state before upgrade, try to upgrade 
again. Does this corruption happen again?

  Could you please let us know the process to perform this 
operations. Can I get some document to do the same.


Thanks,
Prabhakar

From: Mateusz Henicz 
Sent: Tuesday, June 21, 2022 5:59 PM
To: Mahendrakar, Prabhakar - Dell Team
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL]
Could you also answer on questions asked by Laurenz Albe a few emails back? 
That could shed some light into your issue. We would know if the upgrade was 
performed properly and possibly point where the issue can be.
Also, are you able to reproduce the issue? I.e. try restoring the database to 
state before upgrade, try to upgrade again. Does this corruption happen again?
About pg_resetwal, you are right that it should be done as a last resort. On 
the other hand, if your production database does not start after upgrade and 
you do not have any way to rollback your changes it might be it.

Cheers,
Mateusz

wt., 21 cze 2022 o 14:02 Mahendrakar, Prabhakar - Dell Team 
mailto:prabhakar.mahendr...@dellteam.com>> 
napisał(a):
Could you please provide some insights in the below query. We are in need of 
this as it this issue is seen at some of our customer's site.

Thanks,
Prabhakar



Internal Use - Confidential


Internal Use - Confidential
From: Mahendrakar, Prabhakar - Dell Team
Sent: Monday, June 20, 2022 4:22 PM
To: Mateusz Henicz
Cc: 
pgsql-general@lists.postgresql.org
Subject: RE: Postgresql error : PANIC: could not locate a valid checkpoint 
record

Thanks for the response.
Yes, we have taken care of proper shut down of Postgres before initiating the 
Upgrade.
pg_resetwal - I have read that using pg_resetwal may cause the Database more 
inconsistent and should be used only as a last resort.

Also this problem ( checkpoint related issue -could not locate a valid 
checkpoint record ) is not happening frequently. This issue is seen with the 
large size of Data base.
Please let me know if you require any more information.

Thanks,
Prabhakar

From: Mateusz Henicz mailto:mateuszhen...@gmail.com>>
Sent: Friday, June 17, 2022 3:39 PM
To: Mahendrakar, Prabhakar - Dell Team
Cc: 
pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL]
Assuming you have shut down your postgres properly before upgrading, it should 
be safe for you to run pg_resetwal.
https://www.postgresql.org/docs/current/app-pgresetwal.html 
[postgresql.org]
It should help in this case.

pt., 17 cze 2022 o 12:03 Mahendrakar, Prabhakar - Dell Team 
mailto:prabhakar.mahendr...@dellteam.com>> 
napisał(a):
Yes, We are using the pg_upgrade utility of Postgres.

From: Mateusz Henicz mailto:mateuszhen...@gmail.com>>
Sent: Friday, June 17, 2022 3:31 PM
To: Mahendrakar, Prabhakar - Dell Team
Cc: 
pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL]
Hi,
Have you done pg_upgrade post Postgres 13 installation?

https://www.postgresql.org/docs/13/pgupgrade.html 
[postgresql.org]

Cheers,
Mateusz

pt., 17 cze 2022 o 11:20 Mahendrakar, Prabhakar - Dell Team 
mailto:prabhakar.mahendr...@dellteam.com>> 
napisał(a):
Hello,

Good Morning !

We are facing checkpoint related issues from PostGreSQL 13.4 ( could not locate 
a valid checkpoint record) and Postgres service fails to come up.

LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
LOG:  listening on IPv4 address "127.0.0.1", port 9003
LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003
LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"
LOG:  database system was shut down at 2022-06-09 10:19:24 CEST
LOG:  invalid primary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 8773) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

This issu

ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note that 
`pg_dump.dump` contains:


CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING = 
'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do not 
know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) I 
get:


CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?
*t

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Adrian Klaver]

On 6/22/22 12:17, Tomas Pospisek wrote:

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note that 
`pg_dump.dump` contains:


     CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING = 
'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do not 
know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) I 
get:


     CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

     CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?


Are dumping/restoring from one version of Postgres to another?

If from older to newer then use the new version of pg_dump(13) to dump 
the older(12) database. Then the 13 version of restore to load the 
version 13 database.




*t





--
Adrian Klaver
adrian.kla...@aklaver.com

Tuning a query with ORDER BY and LIMIT

[Dirschel, Steve]

I am fairly new to tuning Postgres queries.  I have a long background tuning 
Oracle queries.

Posrgres version 10.11

Here is the DDL for the index the query is using:

create index workflow_execution_initial_ui_tabs
on workflow_execution (workflow_id asc, status asc, result asc, 
completed_datetime desc);


explain (analyze, verbose, costs, buffers, timing, summary, hashes)
select * from workflow_execution
where workflow_id = 14560 and
  status = 'COMPLETED' and
  result in 
('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')
order by completed_datetime desc limit 50;

--
Limit  (cost=56394.91..56395.04 rows=50 width=1676) (actual 
time=3400.608..3400.622 rows=50 loops=1)
"  Output: execution_id, state_machine_id, workflow_id, started_datetime, 
completed_datetime, status, execution_context_s3_arn, ol_version, 
created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, 
correlation_id, result, state_machine_execution_arn, created_by_id, 
updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, 
acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, 
notification_trigger_workflow_id, acquired_object_name, subscription_guid"
  Buffers: shared hit=142368
  ->  Sort  (cost=56394.91..56432.71 rows=15118 width=1676) (actual 
time=3400.607..3400.615 rows=50 loops=1)
"Output: execution_id, state_machine_id, workflow_id, started_datetime, 
completed_datetime, status, execution_context_s3_arn, ol_version, 
created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, 
correlation_id, result, state_machine_execution_arn, created_by_id, 
updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, 
acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, 
notification_trigger_workflow_id, acquired_object_name, subscription_guid"
Sort Key: workflow_execution.completed_datetime DESC
Sort Method: top-N heapsort  Memory: 125kB
Buffers: shared hit=142368
->  Index Scan using workflow_execution_initial_ui_tabs on 
workflow.workflow_execution  (cost=0.69..55892.70 rows=15118 width=1676) 
(actual time=0.038..2258.579 rows=2634718 loops=1)
"  Output: execution_id, state_machine_id, workflow_id, 
started_datetime, completed_datetime, status, execution_context_s3_arn, 
ol_version, created_datetime, updated_datetime, deleted_millis, 
acquisition_channel_id, correlation_id, result, state_machine_execution_arn, 
created_by_id, updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, 
trigger_message, acquired_gcs_s3_object_uuid, api_trigger_id, 
scheduled_trigger_id, notification_trigger_workflow_id, acquired_object_name, 
subscription_guid"
"  Index Cond: ((workflow_execution.workflow_id = 14560) AND 
((workflow_execution.status)::text = 'COMPLETED'::text) AND 
((workflow_execution.result)::text = ANY 
('{SUCCEEDED,REEXECUTED,ABORTED,DISCONTINUED,FAILED,PARTIAL_SUCCESS}'::text[])))"
  Buffers: shared hit=142368
Planning time: 0.217 ms
Execution time: 3400.656 ms

With Oracle for a query like this since the index is on the 3 columns matching 
the WHERE clause and the ORDER BY clause is in the 4th position Oracle would be 
able to scan that index and as soon as it finds the first matching 50 rows.  
But as you can see above Postgres is finding 2,634,718 matching rows for the 
WHERE clause , sorts them, and then returns the first 50 rows.

I was questioning if the result IN clause was causing the issue so I ran the 
query with result = and see the same results:

explain (analyze, verbose, costs, buffers, timing, summary, hashes)
select * from workflow_execution
where workflow_id = 14560 and
  status = 'COMPLETED' and
  result = 'SUCCEEDED'
order by completed_datetime desc limit 50;

Limit  (cost=54268.09..54268.22 rows=50 width=1676) (actual 
time=3372.453..3372.467 rows=50 loops=1)
"  Output: execution_id, state_machine_id, workflow_id, started_datetime, 
completed_datetime, status, execution_context_s3_arn, ol_version, 
created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, 
correlation_id, result, state_machine_execution_arn, created_by_id, 
updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, 
acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id, 
notification_trigger_workflow_id, acquired_object_name, subscription_guid"
  Buffers: shared hit=140313
  ->  Sort  (cost=54268.09..54304.46 rows=14547 width=1676) (actual 
time=3372.452..3372.460 rows=50 loops=1)
"Output: execution_id, state_machine_id, workflow_id, started_datetime, 
completed_datetime, status, execution_context_s3_arn, ol_version, 
created_datetime, updated_datetime, deleted_millis, acquisition_channel_id, 
correlation_id, result, state_machine_execution_arn, created_by_id, 
updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message, 
acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_

Re: Tuning a query with ORDER BY and LIMIT

[Michael van der Kolff]

What do you see when you remove the LIMIT clause? It may be possible to
rewrite this using ROW_NUMBER.

--Michael

On Thu, Jun 23, 2022 at 5:39 AM Dirschel, Steve <
steve.dirsc...@thomsonreuters.com> wrote:

> I am fairly new to tuning Postgres queries.  I have a long background
> tuning Oracle queries.
>
>
>
> Posrgres version 10.11
>
>
>
> Here is the DDL for the index the query is using:
>
>
>
> create index workflow_execution_initial_ui_tabs
>
> on workflow_execution (workflow_id asc, status asc, result asc,
> completed_datetime desc);
>
>
>
>
>
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>
> select * from workflow_execution
>
> where workflow_id = 14560 and
>
>   status = 'COMPLETED' and
>
>   result in
> ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')
>
> order by completed_datetime desc limit 50;
>
>
>
> --
>
> Limit  (cost=56394.91..56395.04 rows=50 width=1676) (actual
> time=3400.608..3400.622 rows=50 loops=1)
>
> "  Output: execution_id, state_machine_id, workflow_id, started_datetime,
> completed_datetime, status, execution_context_s3_arn, ol_version,
> created_datetime, updated_datetime, deleted_millis, acquisition_channel_id,
> correlation_id, result, state_machine_execution_arn, created_by_id,
> updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
>   Buffers: shared hit=142368
>
>   ->  Sort  (cost=56394.91..56432.71 rows=15118 width=1676) (actual
> time=3400.607..3400.615 rows=50 loops=1)
>
> "Output: execution_id, state_machine_id, workflow_id,
> started_datetime, completed_datetime, status, execution_context_s3_arn,
> ol_version, created_datetime, updated_datetime, deleted_millis,
> acquisition_channel_id, correlation_id, result,
> state_machine_execution_arn, created_by_id, updated_by_id,
> acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> Sort Key: workflow_execution.completed_datetime DESC
>
> Sort Method: top-N heapsort  Memory: 125kB
>
> Buffers: shared hit=142368
>
> ->  Index Scan using workflow_execution_initial_ui_tabs on
> workflow.workflow_execution  (cost=0.69..55892.70 rows=15118 width=1676)
> (actual time=0.038..2258.579 rows=2634718 loops=1)
>
> "  Output: execution_id, state_machine_id, workflow_id,
> started_datetime, completed_datetime, status, execution_context_s3_arn,
> ol_version, created_datetime, updated_datetime, deleted_millis,
> acquisition_channel_id, correlation_id, result,
> state_machine_execution_arn, created_by_id, updated_by_id,
> acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
> "  Index Cond: ((workflow_execution.workflow_id = 14560) AND
> ((workflow_execution.status)::text = 'COMPLETED'::text) AND
> ((workflow_execution.result)::text = ANY
> ('{SUCCEEDED,REEXECUTED,ABORTED,DISCONTINUED,FAILED,PARTIAL_SUCCESS}'::text[])))"
>
>   Buffers: shared hit=142368
>
> Planning time: 0.217 ms
>
> Execution time: 3400.656 ms
>
>
>
> With Oracle for a query like this since the index is on the 3 columns
> matching the WHERE clause and the ORDER BY clause is in the 4th position
> Oracle would be able to scan that index and as soon as it finds the first
> matching 50 rows.  But as you can see above Postgres is finding 2,634,718
> matching rows for the WHERE clause , sorts them, and then returns the first
> 50 rows.
>
>
>
> I was questioning if the result IN clause was causing the issue so I ran
> the query with result = and see the same results:
>
>
>
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>
> select * from workflow_execution
>
> where workflow_id = 14560 and
>
>   status = 'COMPLETED' and
>
>   result = 'SUCCEEDED'
>
> order by completed_datetime desc limit 50;
>
>
>
> Limit  (cost=54268.09..54268.22 rows=50 width=1676) (actual
> time=3372.453..3372.467 rows=50 loops=1)
>
> "  Output: execution_id, state_machine_id, workflow_id, started_datetime,
> completed_datetime, status, execution_context_s3_arn, ol_version,
> created_datetime, updated_datetime, deleted_millis, acquisition_channel_id,
> correlation_id, result, state_machine_execution_arn, created_by_id,
> updated_by_id, acquired_gcs_s3_object, sqs_trigger_id, trigger_message,
> acquired_gcs_s3_object_uuid, api_trigger_id, scheduled_trigger_id,
> notification_trigger_workflow_id, acquired_object_name, subscription_guid"
>
>   Buffers: shared hit=140313
>
>   ->  Sort  (cost=54268.09..54304.46 rows=14547 width=1676) (actual
> time=3372.452..3372.460 rows=50 loops

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

On 22.06.22 21:25, Adrian Klaver wrote:

On 6/22/22 12:17, Tomas Pospisek wrote:

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note 
that `pg_dump.dump` contains:


 CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING 
= 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do 
not know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) 
I get:


 CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

 CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?


Are dumping/restoring from one version of Postgres to another?


Yes, indeed!

If from older to newer then use the new version of pg_dump(13) to dump 
the older(12) database. Then the 13 version of restore to load the 
version 13 database.


I will. Thanks a lot Adrian!
*t

Re: Tuning a query with ORDER BY and LIMIT

[Peter J. Holzer]

On 2022-06-22 19:39:33 +, Dirschel, Steve wrote:
> Posrgres version 10.11
> 
> Here is the DDL for the index the query is using:
> 
> create index workflow_execution_initial_ui_tabs
> on workflow_execution (workflow_id asc, status asc, result asc,
> completed_datetime desc);
> 
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
> select * from workflow_execution
> where workflow_id = 14560 and
>   status = 'COMPLETED' and
>   result in
> ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')
> order by completed_datetime desc limit 50;
[...]
> With Oracle for a query like this since the index is on the 3 columns matching
> the WHERE clause and the ORDER BY clause is in the 4^th position Oracle would
> be able to scan that index and as soon as it finds the first matching 50 
> rows. 
> But as you can see above Postgres is finding 2,634,718 matching rows for the
> WHERE clause , sorts them, and then returns the first 50 rows.   

The index cannot be used for sorting, since the column used for sorting
isn't in the first position in the index. That's just how btree indexes
work and Oracle will have the same limitation. What would be possible is
to use an index only scan (returning 2,634,718 matching results), sort
that to find the 50 newest entries and retrieve only those from the
table. That should be faster since the index contains only 4 of 28 (if I
counted correctly) columns and should be quite a bit smaller. It's
possible that Oracle does this. But I'm not sure whether you could tell
that from the execution plan.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Tuning a query with ORDER BY and LIMIT

[Peter J. Holzer]

On 2022-06-22 19:39:33 +, Dirschel, Steve wrote:
> create index workflow_execution_initial_ui_tabs
> on workflow_execution (workflow_id asc, status asc, result asc,
> completed_datetime desc);
[...]
> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
> select * from workflow_execution
> where workflow_id = 14560 and
>   status = 'COMPLETED' and
>   result = 'SUCCEEDED'
> order by completed_datetime desc limit 50;

This query should actually be able to use the index, since the first
columns in the index are all compared to single values. So the you can
just jump to the first matching index and then get the next 50 entries.

> Is Postgres unable to optimize the query similar to Oracle?  Is it possible
> this is possible but we are running on too old of a version?

PostgreSQL 10 is quite old, so that's a possibility.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Tuning a query with ORDER BY and LIMIT

[Peter J. Holzer]

On 2022-06-22 23:48:37 +0200, Peter J. Holzer wrote:
> On 2022-06-22 19:39:33 +, Dirschel, Steve wrote:
> > Posrgres version 10.11
> > 
> > Here is the DDL for the index the query is using:
> > 
> > create index workflow_execution_initial_ui_tabs
> > on workflow_execution (workflow_id asc, status asc, result asc,
> > completed_datetime desc);
> > 
> > explain (analyze, verbose, costs, buffers, timing, summary, hashes)
> > select * from workflow_execution
> > where workflow_id = 14560 and
> >   status = 'COMPLETED' and
> >   result in
> > ('SUCCEEDED','REEXECUTED','ABORTED','DISCONTINUED','FAILED','PARTIAL_SUCCESS')
> > order by completed_datetime desc limit 50;
> [...]
> The index cannot be used for sorting, since the column used for sorting
> isn't in the first position in the index.

compared to a single value

>That's just how btree indexes work and Oracle will have the same
>limitation. What would be possible is to use an index only scan
>(returning 2,634,718 matching results), sort that to find the 50 newest
>entries and retrieve only those from the table. That should be faster
>since the index contains only 4 of 28 (if I counted correctly) columns
>and should be quite a bit smaller.

Another - better - optimization would be to fetch the first 50 results
for each of the 6 possible values of result, then choose the 50 largest
of those. That sounds tricky to generalize, though.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Tuning a query with ORDER BY and LIMIT

[Peter J. Holzer]

On 2022-06-23 00:19:19 +0200, Peter J. Holzer wrote:
> On 2022-06-22 23:48:37 +0200, Peter J. Holzer wrote:
> > The index cannot be used for sorting, since the column used for sorting
> > isn't in the first position in the index.
> 
> compared to a single value
  ^ not

E-Mail really needs a "Supercedes" header.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Tuning a query with ORDER BY and LIMIT

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2022-06-22 19:39:33 +, Dirschel, Steve wrote:
>> create index workflow_execution_initial_ui_tabs
>> on workflow_execution (workflow_id asc, status asc, result asc,
>> completed_datetime desc);
> [...]
>> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>> select * from workflow_execution
>> where workflow_id = 14560 and
>> status = 'COMPLETED' and
>> result = 'SUCCEEDED'
>> order by completed_datetime desc limit 50;

> This query should actually be able to use the index, since the first
> columns in the index are all compared to single values. So the you can
> just jump to the first matching index and then get the next 50 entries.

Yeah, that sure looks like it should work.  Since we've not been
shown the whole table's DDL, it's hard to guess at why it isn't;
maybe there's a data type issue?

>> Is Postgres unable to optimize the query similar to Oracle?  Is it possible
>> this is possible but we are running on too old of a version?

> PostgreSQL 10 is quite old, so that's a possibility.

That's worked for many ages.

regards, tom lane

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tom Lane]

Tomas Pospisek  writes:
> On 22.06.22 21:25, Adrian Klaver wrote:
>> On 6/22/22 12:17, Tomas Pospisek wrote:
>>> If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) 
>>> I get:
>>> 
>>>  CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';
>>> 
>>> When I do the same on the old server (12.8-1.pgdg20.04+1) I get:
>>> 
>>>  CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
>>> LC_CTYPE = 'en_US.UTF-8';

>> Are dumping/restoring from one version of Postgres to another?

> Yes, indeed!

This is probably more about dumping from different operating systems.
The spelling of the locale name is under the control of the OS,
and Postgres doesn't know very much about the semantics of it
(so I think we conservatively assume that any difference in
spelling is significant).

Best bet might be to edit the dump file to adjust the locale
spellings to match your new system.

regards, tom lane

Re: Tuning a query with ORDER BY and LIMIT

[Jeff Janes]

On Wed, Jun 22, 2022 at 3:39 PM Dirschel, Steve <
steve.dirsc...@thomsonreuters.com> wrote:


> explain (analyze, verbose, costs, buffers, timing, summary, hashes)
>

'hashes', what's that?  Are you using community PostgreSQL, or someones
fork?


> With Oracle for a query like this since the index is on the 3 columns
> matching the WHERE clause and the ORDER BY clause is in the 4th position
> Oracle would be able to scan that index and as soon as it finds the first
> matching 50 rows.
>

Can you show the plan from Oracle?  I'm not an expert at reading their
plans, but certainly they are easier to attempt to read if you can at least
see them.


> I was questioning if the result IN clause was causing the issue so I ran
> the query with result = and see the same results:
>
>
I can't reproduce this at all.  With simple equality it absolutely uses the
index for ordering in that case, even in v10.

Cheers,

Jeff

>

Re: Tuning a query with ORDER BY and LIMIT

[Jeff Janes]

On Wed, Jun 22, 2022 at 6:19 PM Peter J. Holzer  wrote:

>
> >That's just how btree indexes work and Oracle will have the same
> >limitation. What would be possible is to use an index only scan
> >(returning 2,634,718 matching results), sort that to find the 50 newest
> >entries and retrieve only those from the table. That should be faster
> >since the index contains only 4 of 28 (if I counted correctly) columns
> >and should be quite a bit smaller.
>
> Another - better - optimization would be to fetch the first 50 results
> for each of the 6 possible values of result, then choose the 50 largest
> of those. That sounds tricky to generalize, though.
>

You don't even need to read 50 from each of the 6 branches.  If you use a
merge append operation, you would need to read  55 rows.  50 to be
returned, and one non-returned from each branch other than the one
returning the last row.  I suspect this may be what Oracle is doing.  With
some trickery, you can get PostgreSQL to do the same thing.

(select * from foo where a=4 order by b)
union all
(select * from foo where a=7 order by b)
order by b
limit 50

 QUERY PLAN

-
 Limit  (cost=0.86..131.25 rows=50 width=8)
   ->  Merge Append  (cost=0.86..26079.86 rows=1 width=8)
 Sort Key: foo.b
 ->  Index Only Scan using foo_a_b_idx on foo  (cost=0.42..12939.92
rows=5000 width=8)
   Index Cond: (a = 4)
 ->  Index Only Scan using foo_a_b_idx on foo foo_1
 (cost=0.42..12939.92 rows=5000 width=8)
   Index Cond: (a = 7)

Cheers,

Jeff

Re: function currtid2() in SQL and ESQL/C to get the new CTID of a row

[Matthias Apitz]

El día miércoles, junio 22, 2022 a las 08:39:31 +0200, Matthias Apitz escribió:

> > EXEC SQL SELECT currtid2(:table ::text, :oldCTID ::tid) INTO :newCTID;
> > 
> > ...
> 
> Hello Tom,
> 
> We came accross cases where the above SELECT returns as :newCTID the
> same as the :oldCTID. The :oldCTID was picked up with FETCH from the
> CURSOR and before locking/updating the row in question we're now checking if 
> its
> CTID has changed meanwhile we're cycling though the CURSOR. In some cases the
> CTID is returned as unchanged but a SELECT for UPDATE fails with the
> CTID. I have here an example of the ESQL/C log:
> 

We have been lucky to have the full contents of all columns of the row
in question we wanted to lock in our log files as the process saw the
row and could compare this afterwards with the actual row contents. It
turned out that the row was updated and perhaps the update transaction
not commit in the moment when the process was asking for the actual
:newCTID. We have still to investigate why this COMMIT took so long that
such a hit of two processes asking for the same row (which is like
winning the lottery on Sunday). In any case, for the moment we have no
evidence that currtid2() is the culprit.

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: INSERT ALL with DML ERROR Logging replacement in PostgreSQL

[Jagmohan Kaintura]

Hi ALl,

Any other thought on this thread.

On Tue, Jun 21, 2022 at 2:24 PM Gilles Darold  wrote:

> Le 21/06/2022 à 10:28, Jagmohan Kaintura a écrit :
>
> Hi Gilles,
>
> I was going though this earlier today but didn't compiled it as I read it
> may not be able to capture the errors if we have below type of statement
> and most of our statements are of INSERT .. SELECT statements only.
>
> The form INSERT INTO  SELECT ... will not have the same
> behavior than in Oracle. It will not stored the successful insert and
> logged the rows in error. This is not supported because it is a single
> transaction for PostgreSQL and everything is rolled back in case of error.
>
> Our all statements are of that form will it be still useful.
>
>
> Right, this was not obvious in your post, but yes if you are using INSERT
> + SELECT this is not possible with the current version of this extension.
> Maybe that could be possible by rewriting internally the query to loop over
> the result of the select and generate an insert per row returned, but with
> performances lost of courses.
>
>
> Best regards,
>
> --
> Gilles Daroldhttp://www.darold.net/
>
>

-- 
*Best Regards,*
Jagmohan