Re: Practical maximum max_locks_per_transaction?

[Ron]

On 11/25/22 17:56, Tom Lane wrote:

Ron  writes:

How "raised" is too raised?  I just had to increase
max_locks_per_transaction from 640 to 1024 on an instance with many child
tables and against which is run many large reports.  Another instance has
zero children, but had to increase the value to 1280 to prevent reports from
failing.

max_locks_per_transaction in isolation means nothing.  What counts
is the product max_locks_per_transaction * max_connections, which is
(more or less) the number of slots allocated in the shared lock table.

Having said that, you can probably make it as big as you want
on any reasonably modern machine.  I wouldn't blink at a few
million locktable entries, at least not on 64-bit hardware.


Really good to know.  I wonder how that can be worded in the documentation.

--
Angular momentum makes the world go 'round.

Re: Practical maximum max_locks_per_transaction?

[Tom Lane]

Ron  writes:
> How "raised" is too raised?  I just had to increase 
> max_locks_per_transaction from 640 to 1024 on an instance with many child 
> tables and against which is run many large reports.  Another instance has 
> zero children, but had to increase the value to 1280 to prevent reports from 
> failing.

max_locks_per_transaction in isolation means nothing.  What counts
is the product max_locks_per_transaction * max_connections, which is
(more or less) the number of slots allocated in the shared lock table.

Having said that, you can probably make it as big as you want
on any reasonably modern machine.  I wouldn't blink at a few
million locktable entries, at least not on 64-bit hardware.

regards, tom lane

Practical maximum max_locks_per_transaction?

[Ron]

v9.6.24 (being retired next year)

https://www.postgresql.org/docs/9.6/runtime-config-locks.html

"The default, 64, has historically proven sufficient, but you might need to 
raise this value if you have queries that touch many different tables in a 
single transaction, e.g., query of a parent table with many children."


How "raised" is too raised?  I just had to increase 
max_locks_per_transaction from 640 to 1024 on an instance with many child 
tables and against which is run many large reports.  Another instance has 
zero children, but had to increase the value to 1280 to prevent reports from 
failing.


--
Angular momentum makes the world go 'round.

How to create hot standby of Postgres 12/Debian 10.3 in Windows 11

[Andrus]

Hi!

Postgres 12 from Postgres repository is running in Debian 10.3 VPS server:

PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit


(its upgrade is currently not an option).

Cluster should mirrored to Windows 11 workstation in real time over 
internet.


I installed Debian in Windows 11  using Linux Subsystem for Windows with

wsl --install Debian

This installs Debian 11.5

I tried to install Postgres 12 for replication in this Debian using

sudo apt install postgresql-12

but this fails with

E: Unable to locate package postgresql-12

How to install Postgres 12 in Debian 11.5 ?

Will this replication work since Debian versions are different but 
hopefully locale implementations are same ?


Which is most reasonable way to replicate whole Postgres 12/Debian 10.3  
cluster in Windows 11 ?


Andrus.

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

[Kirk Wolak]

On Tue, Nov 22, 2022 at 5:51 PM Thomas Munro  wrote:

> On Tue, Nov 22, 2022 at 4:25 AM Kirk Wolak  wrote:
> >   In researching this problem, it appears that the decision was made
> like 17yrs ago, when windows did not have a realistic "terminal" type
> interface.
>
> FWIW PostgreSQL 16 will require Windows 10+.  Not a Windows user
>
> Re configuration flags: don't waste time with the old perl-based build
> system.  The configuration should be done only with the new *meson*
> ...

love this feature.  It would be good if the tests in
> src/bin/psql/t/010_tab_completion.pl pass on Windows, but if that's
> hard, don't let that stop you sharing a patch.
>

Okay, my "hacked" version actually passed all the tests (manually) except
FILE completion.
(2 versions of readdir(), readline() called it's own, but landed in ours
[linker symbol issues]).

I understand the problems well enough.  I am going to change gears, and
move to meson.
Without that, when I link readline() w/o any library/approach changes
(mingw), arrow keys don't work.
(but Ctrl Keys, History and Tab do).

Thanks for the support!

Re: Index-only scan not working when IN clause has 2 or more values

[Tom Lane]

=?UTF-8?B?QW5uYSBCLg==?=  writes:
> create index "ix-transaction-client-trans_dttm-division"
> on transaction (client_id,
> trans_dttm desc,
> division_code);
>  
> EXPLAIN (ANALYZE, BUFFERS)
> select *
> from transaction
> where client_id = 123456
>   and (trans_dttm between TO_DATE('01.01.2020', 'dd.mm.') and 
> TO_DATE('31.12.2022', 'dd.mm.'))
>   and (division_code in
>('not_existing_code1', 'not_existing_code2'))
> order by trans_dttm desc
> limit 50 offset 0;

The reason you get a plan like this:

> "  ->  Index Scan using ""ix-transaction-client-trans_dttm-division"" on 
> transaction  (cost=0.57..8350814.66 rows=28072 width=2675) (actual 
> time=703291.834..703291.835 rows=0 loops=1)"
> "Index Cond: ((client_id = '123456'::numeric) AND (trans_dttm >= 
> to_date('01.01.2020'::text, 'dd.mm.'::text)) AND (trans_dttm <= 
> to_date('31.12.2022'::text, 'dd.mm.'::text)))"
> "Filter: ((division_code)::text = ANY 
> ('{not_existing_code1,not_existing_code2}'::text[]))"

is that if the =ANY clause were an index condition, it would result
in multiple scans of the index, therefore the output would (in all
probability) not be sorted in index order.  To produce the demanded
result, the plan would have to read the entire index scan and sort
its output.  The planner estimates that that would be slower than
what it has done here.  In practice it looks like you're reading
the whole scan output anyway because there are less than 50
matching rows, but the planner didn't know that.

The problem with =ANY producing unordered output can be dodged if
the =ANY is on the first index column; but I suppose that does not
help you here, since making division_code the first index column
would defeat getting output that's sorted by trans_dttm anyway.

You might try making extended stats on these three columns to see
if that helps the planner to get a better rowcount estimate.
If it understood that there were fewer than 50 matching rows,
it might opt for the use-the-=ANY-and-sort plan type.

regards, tom lane

Re: [BeginnerQuestion]Why the postgres_fe.h not found?

[Adrian Klaver]

On 11/25/22 03:04, Wen Yi wrote:

Hello everyone,
I want to study the source of the postgres,
but now I faced some problem:
(The lsp server can't find the postgres_fe.h)


~src/include/postgres_fe.h



image.png

// 


6:54 
And the VSCODE also can't find the position of the postgres_fe.h
image.png
image.png

// 


6:55 
How can I solve this problem?
Thanks in advance!



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

Re: Configure my pgadmin

[Adrian Klaver]

On 11/25/22 05:34, William Torrez Corea wrote:

How can I configure my pgadmin?


Supply the password in the connection settings.



Trying to connect to a server but fail *the attempt.
*

I get the following message:
*
*

*Unable to connect to server: connection to server at "localhost"
(::1), port 5432 failed: fe_sendauth: no password supplied
*

--

With kindest regards, William.

⢀⣴⠾⠻⢶⣦⠀
⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system
⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org 
⠈⠳⣄




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

Re: How to select unique records in PostgreSQL

[David G. Johnston]

On Fri, Nov 25, 2022 at 7:50 AM Ron  wrote:

> Honestly, you do it *in PostgreSQL* the same way you do it in all the
> other SQL RDBMSs.
>

Emphasizing "in PostgreSQL" is nonsensical - it isn't like the OP specified
that they know how to do it in some other RDBMS and are trying to convert
their knowledge to PostgreSQL.



> On 11/24/22 06:01, Rama Krishnan wrote:
>
>
>
> I want to get the unique wallet_id from this table even it was repeated on
> multiple occasions I should calculate only once as well as if the wallet_id
> was calculated on previous month it shouldn't be calculate on next months
>
>
You need a subquery to compute the month in which each wallet_id should
appear (group by wallet_id with min(date) probably), then you can group on
the min(date) column and count the wallets.

David J.

Re: table inheritance partition and indexes

[Ted Toth]

Docs can always be better, right ;) When I went back and looked at the page
I did realize that the ATTACH INDEX command was only mentioned in the
description of declarative partitioning and not in the inheritance section
so I should have paid closer attention to the location.

Ted

On Wed, Nov 23, 2022 at 8:13 PM David Rowley  wrote:

> On Thu, 24 Nov 2022 at 11:34, Ted Toth  wrote:
> >
> > On Wed, Nov 23, 2022 at 4:01 PM Tom Lane  wrote:
> >> Then you're stuck managing it manually.  But ATTACH PARTITION is
> >> not relevant to INHERITS-style partitioning.
> >
> > That's the part that wasn't clear to me, thanks.
>
> Would this have been more clear if [1] didn't mention both declarative
> partitioning and inheritance partition on the same page? I've wondered
> before if we should split that into two separate pages.
>
> David
>
> [1]
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE
>

Re: How to select unique records in PostgreSQL

[Ron]

Honestly, you do it *in PostgreSQL* the same way you do it in all the other 
SQL RDBMSs.


On 11/24/22 06:01, Rama Krishnan wrote:


Hi All,


I want to get the unique wallet_id from this table even it was repeated on 
multiple occasions I should calculate only once as well as if the 
wallet_id was calculated on previous month it shouldn't be calculate on 
next months



create table student_id
(
student_wallet_id int,
card_id int,
created_date date
)

insert into student_id values ('w1', 'c1', '2022-04-01')


insert into student_id values ('w1', 'c2', '2022-04-03')


insert into student_id values ('w1', 'c3', '2022-04-04')

insert into student_id values ('w1', 'c4', '2022-05-01')
insert into student_id values ('w2', 'c1', '2022-05-01')
insert into student_id values ('w2', 'c2', '2022-05-04')
insert into student_id values ('w3', 'c4', '2022-05-05')
insert into student_id values ('w3', 'c6', '2022-06-03)
insert into student_id values ('w3', 'c7', '2022-06-05')
insert into student_id values ('w3', 'c8', '2022-06-07')
expected output:
---

months count_wallet_id
2022-04      1
2022-05     2
2022-05     0


Regards

A.Rama Krishnan


--
Angular momentum makes the world go 'round.

Configure my pgadmin

[William Torrez Corea]

How can I configure my pgadmin?

Trying to connect to a server but fail
*the attempt. *

I get the following message:


> *Unable to connect to server: connection to server at "localhost" (::1),
> port 5432 failed: fe_sendauth: no password supplied *
>
-- 

With kindest regards, William.

⢀⣴⠾⠻⢶⣦⠀
⣾⠁⢠⠒⠀⣿⡁ Debian - The universal operating system
⢿⡄⠘⠷⠚⠋⠀ https://www.debian.org
⠈⠳⣄

[BeginnerQuestion]Why the postgres_fe.h not found?

[Wen Yi]

Hello everyone,
I want to study the source of the postgres,
but now I faced some problem:
(The lsp server can't find the postgres_fe.h)
[cid:f46601f4-aa60-4ee4-8776-c1d744c9e4a9]

[image.png]

6:54
And the VSCODE also can't find the position of the postgres_fe.h
image.png

[image.png]

6:55
How can I solve this problem?
Thanks in advance!

Re: About row locking ordering

[Kirk Wolak]

My first question is why are you not using "WHERE CURRENT OF" cursor_name?

The link to the page follows.
But effectively, you are locking the row and that is the row you want to
update (the current row of the cursor).
I wonder if that addresses the problem...

https://www.postgresql.org/docs/current/sql-update.html

On Fri, Nov 25, 2022 at 2:58 AM Ryo Yamaji (Fujitsu) 
wrote:

> Hi All.
>
> When multiple sessions tried to acquire a row lock on the same tuple
> sequentially, I expected
> that the first session waiting for the lock would acquire the lock first
> (FIFO). However, when we
> actually tested it, there were cases where a session that was behind a
> first session acquired
> a row lock ahead of first session.
> Is this behavior is expected specifications or is it a bug?
>
> The following is a list of the cases where the next session gets the lock
> first.
> ・Multiple sessions request row locks for the same tuple
> ・Update occurs for target tuple
>
> The above behavior may have the following problems:
> ・If the lock acquiring order is FIFO in Postgres, it is not satisfied.
> ・Any sessions which cannot acquire a lock for a long time result in a long
> transaction
>
> If the above behavior is a specification, I think it is necessary to
> document design considerations
> when dealing with row locks.
>
> [Condition]
> ・Version
> - PostgreSQL 14.0 (compiled with LOCK_DEBUG)
> ・postgresql.conf
> - log_lock_waits = on
> - deadlock_timeout = 10
> - log_line_prefix = '%m [%p:%l:%x] '
> - trace_locks = on
> - trace_lock_oidmin = 16000
>
> [Table Definition]
> create table t1 (col1 int, col2 int, col3 char (10));
> insert into t1 values (1,0, ''); insert into t1 values (100, , '');
>
> [Application]
>  ---
> BEGIN;
> SELECT * FROM t1 WHERE col1 = 1 FOR UPDATE;
> SELECT current_timestamp AS TRANSTART, clock_timestamp() AS NOW;
> UPDATE t1 set col2 = :val, col3 = :valb WHERE col1 = 1;
> SELECT pg_sleep(1);
> COMMIT;
> 
>
> [Test]
> The test set is attached.
> 1. Run the following in 100 multiplex
> for i in `seq 100`; do psql postgres -f ./a.sql -v val=${i} -v
> valb="'a.sh'" &; done
> 2.After 10 seconds (1. After completion of execution), run the following
> in 10 multiplexes
> for i in `seq 10`; do ii=`expr ${i} + 900`; psql postgres -f ./a.sql -v
> val=${ii} -v valb="'b.sh'" &; done
>
> All a.sh were expected to complete processing first (FIFO). However, b.sh
> completed the process
> before part of a.sh.
>
> [Log]
> We are checking two types of logs.
>
> 1. ShareLock has one wait, the rest is in AccessExclusiveLock
>
> 1-1. Only 1369555 is aligned with ShareLock, the transaction ID obtained
> by 1369547, and the rest with
> AccessExclusiveLock, the tuple obtained by 1369555.
> This is similar to a pattern in which no updates have occurred to the
> tuple.
> --
> 2022-10-26 01:20:08.881 EDT [1369555:19:0] LOG: process 1369555 still
> waiting for ShareLock on transaction 2501 after 10.072 ms
> 2022-10-26 01:20:08.881 EDT [1369555:20:0] DETAIL: Process holding the
> lock: 1369547. Wait queue: 1369555.
> ～
> 2022-10-26 01:21:58.918 EDT [1369898:17:0] LOG: process 1369898 acquired
> AccessExclusiveLock on tuple (1, 0) of relation 16546 of database 13779
> after 10.321 ms
> 2022-10-26 01:21:58.918 EDT [1369898:18:0] DETAIL: Process holding the
> lock: 1369555. Wait queue: 1369558, 1369561, 1369564, 1369567, 1369570,
> 1369573, 1369576, ...
> --
>
>
> 2. All processes wait with ShareLock
>
> 2-1. With 1369558 holding the t1 (0, 4) lock, the queue head is 1369561.
> --
> 2022-10-26 01:22:27.230 EDT [1369623:46:2525] LOG: process 1369623 still
> waiting for ShareLock on transaction 2504 after 10.133 msprocess 1369623
> still waiting for ShareLock on transaction 2504 after 10.133 ms
> 2022-10-26 01:22:27.242 EDT [1369877:47:2604] DETAIL: Process holding the
> lock: 1369558. Wait queue: 1369561, 1369623, 1369626, ...
> --
>
> 2-2. When 1369558 locks are released, the first 1369561 in the Wait queue
> was expected to acquire the lock,
> but the process actually acquired 1369787
> --
> 2022-10-26 01:22:28.237 EDT [1369623:63:2525] LOG: process 1369623 still
> waiting for ShareLock on transaction 2577 after 10.028 ms
> 2022-10-26 01:22:28.237 EDT [1369623:64:2525] DETAIL: Process holding the
> lock: 1369787. Wait queue: 1369623, 1369610, 1369614, 1369617, 1369620.
> --
>
> 2-3. Checking that the 1369561 is rearranging.
> --
> 2022-10-26 01:22:28.237 EDT [1369629:64:2527] DETAIL: Process holding the
> lock: 1369623. Wait queue: 1369629,