Re: Upgrading to v12
On 11/28/22 17:02, Tom Lane wrote: Brad White writes: I tried to run initdb after re-installing pg 12 using postgresql-12.10-2-windows-x64.exe. But the runas I'm using to execute it as pguser seems to be swallowing all the output, so I can't see any errors. I was able to run pg_checksums and get those enabled. Is there anything else I want from initdb? If you can connect to the new installation, then you're done with that part, and can get on with the dump-and-restore part. Yeah this is coming from this SO question: https://stackoverflow.com/questions/74607304/what-results-should-i-see-from-running-initdb#comment131694419_74607304 Brad was double clutching on the initdb. regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
Re: Upgrading to v12
Brad White writes: > I tried to run initdb after re-installing pg 12 > using postgresql-12.10-2-windows-x64.exe. > But the runas I'm using to execute it as pguser seems to be swallowing all > the output, so I can't see any errors. > I was able to run pg_checksums and get those enabled. > Is there anything else I want from initdb? If you can connect to the new installation, then you're done with that part, and can get on with the dump-and-restore part. regards, tom lane
Re: Upgrading to v12
Tom, I tried to run initdb after re-installing pg 12 using postgresql-12.10-2-windows-x64.exe. But the runas I'm using to execute it as pguser seems to be swallowing all the output, so I can't see any errors. I was able to run pg_checksums and get those enabled. Is there anything else I want from initdb? Or can I skip that? On Fri, Nov 11, 2022 at 4:27 PM Tom Lane wrote: > Nonetheless, > your path forward is clear: use pg_dump (or better pg_dumpall) > and then load the output into a *freshly initdb'd v12 installation.* > It'll be a bit slower than the pg_upgrade way, but it'll work. > > regards, tom lane >
Re: delete statement returning too many results
"David G. Johnston" writes: > There is a nice big caution regarding the default read committed isolation > mode, order by, and for update, in the documentation, but I cannot work out > exactly why this example seems to be triggering it. The is talking about a rather different scenario. I managed to reproduce this locally. I find that initially, with an empty queue table, you get a query plan like Delete on queue (cost=0.38..8.42 rows=1 width=38) -> Nested Loop (cost=0.38..8.42 rows=1 width=38) -> HashAggregate (cost=0.23..0.24 rows=1 width=40) Group Key: "ANY_subquery".id -> Subquery Scan on "ANY_subquery" (cost=0.15..0.22 rows=1 width=40) -> Limit (cost=0.15..0.21 rows=1 width=14) -> LockRows (cost=0.15..74.15 rows=1200 width=14) -> Index Scan using queue_pkey on queue queue_1 (cost=0.15..62.15 rows=1200 width=14) -> Index Scan using queue_pkey on queue (cost=0.15..8.17 rows=1 width=14) Index Cond: (id = "ANY_subquery".id) which is fine because the LockRows bit will be run only once. However, after the table's been stomped on for awhile (and probably not till after autovacuum runs), that switches to Delete on queue (cost=0.25..16.31 rows=1 width=38) -> Nested Loop Semi Join (cost=0.25..16.31 rows=1 width=38) Join Filter: (queue.id = "ANY_subquery".id) -> Index Scan using queue_pkey on queue (cost=0.12..8.14 rows=1 width=14) -> Subquery Scan on "ANY_subquery" (cost=0.12..8.16 rows=1 width=40) -> Limit (cost=0.12..8.15 rows=1 width=14) -> LockRows (cost=0.12..8.15 rows=1 width=14) -> Index Scan using queue_pkey on queue queue_1 (cost=0.12..8.14 rows=1 width=14) and then you start to get failures, because each re-execution of the subquery produces a fresh row thanks to the silent SKIP LOCKED. So basically it's unsafe to run the sub-select more than once, but the query as written leaves it up to the planner whether to do that. I'd suggest rephrasing as WITH target_rows AS MATERIALIZED ( SELECT id FROM queue ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED ) DELETE FROM queue WHERE id IN (SELECT * FROM target_rows) RETURNING *; regards, tom lane
Re: delete statement returning too many results
On Mon, Nov 28, 2022 at 7:18 AM Ron wrote: > On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: > > Hello everyone, > > > > I am seeing weird behaviour of a delete statement that is returning more > results than I am expecting. > > > > This is the query: > > > > DELETE FROM queue > > WHERE > > id IN ( > > SELECT id > > FROM queue > > ORDER BY id > > LIMIT 1 > > FOR UPDATE > > SKIP LOCKED > > ) > > RETURNING *; > > > > My understanding is that the limit in the sub-select should prevent this > query from ever > > returning more than one result. Sadly I am seeing cases where there is > more than one result. > > > > This repository has a Java setup that pretty reliably reproduces my > issue: > > https://github.com/ArloL/postgres-query-error-demo > > > > I checked the docs for select and delete and couldn’t find any hint for > cases > > where the behaviour of limit might be surprising. > > > > Am I missing something? > > More than one row will be deleted if there in more than one record in > "queue" for the specific value of "id" (i.e "id" is not unique). > > Given that the example code provided has "ID" as a PK on the queue table this fact, while true, is unhelpful for this specific question. There is a nice big caution regarding the default read committed isolation mode, order by, and for update, in the documentation, but I cannot work out exactly why this example seems to be triggering it. https://www.postgresql.org/docs/current/sql-select.html David J.
Re: Re: how to implement add using upsert and trigger?
Thank you, this result is what I want yin@163.com From: Pavel Luzanov Date: 2022-11-28 21:26 To: yin@163.com; pgsql-general Subject: Re: how to implement add using upsert and trigger? Hi, I think you need: insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1) on conflict(itemid) do update set value = stat_result.value + new.value, cnt = stat_result.cnt +1; excluded.value and new.value is the same value from inserted record, but your need a current value from stat_result. On 28.11.2022 15:37, yin@163.com wrote: Hi, all： when I using upsert and trigger to update other table automatically： create table stat_detail(itemid bigint not null, value bigint, captime bigint); create table stat_result(itemid bigint primary key, value bigint, cnt bigint); create or replace function inertfunc() returns trigger as $$ begin insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1) on conflict(itemid) do update set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid; return new; end; $$ language plpgsql; create trigger tri_insert after insert on stat_detail for each row execute function inertfunc(); postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 1 | 1 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) But I want it is "100 3 3". So how I can do? yin@163.com -- Pavel Luzanov Postgres Professional: https://postgrespro.com The Russian Postgres Company
Re: delete statement returning too many results
On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: Hello everyone, I am seeing weird behaviour of a delete statement that is returning more results than I am expecting. This is the query: DELETE FROM queue WHERE id IN ( SELECT id FROM queue ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING *; My understanding is that the limit in the sub-select should prevent this query from ever returning more than one result. Sadly I am seeing cases where there is more than one result. This repository has a Java setup that pretty reliably reproduces my issue: https://github.com/ArloL/postgres-query-error-demo I checked the docs for select and delete and couldn’t find any hint for cases where the behaviour of limit might be surprising. Am I missing something? More than one row will be deleted if there in more than one record in "queue" for the specific value of "id" (i.e "id" is not unique). -- Angular momentum makes the world go 'round.
Re: Get table catalog from pg_indexes
On 11/28/22 00:04, Thomas Kellerer wrote: Igor Korot schrieb am 27.11.2022 um 23:13: I've never heard of a database referred to as a catalog. (That's always been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) In the ODBC terminology the DB is usually referenced as catalog. JDBC uses the same term and the SQL standard as well. That's good to know. -- Angular momentum makes the world go 'round.
Standby restore_command
Hi, I have configured standby using restore_command to restore WAL files. I can check in postgresql log files which WAL file was restored. Is possible to check which last WAL files was restored using SQL Query ? Best regards Jack
Re[2]: Index-only scan not working when IN clause has 2 or more values
Hi Tom and community,
Thank you very much!
After digging how Postgres planner uses statistics, I have increased table
statistics from 100 to 1000. It was enough for planner to use multiple scans of
the index and then sort!
(Also I have added dependency extended stats on the three columns as you
suggested).
Can I ask one more question. I am also testing same index but covering version:
create index "ix-transaction-client-trans_dttm-include-division"
on transaction (client_id,
trans_dttm desc)
include (division_code);
Why tuned statistics does not improved it?
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
"Index Scan using ""ix-transaction-client-trans_dttm-include-division"" on
transaction (cost=0.57..8243559.04 rows=240 width=921) (actual
time=23920.988..23920.989 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[]))"
Rows Removed by Filter: 1000
Buffers: shared hit=8021895 read=2038341
I/O Timings: read=8902.706
Planning Time: 1.278 ms
Execution Time: 23921.026 ms
Yes, I have read about covering indexes in Postgres, about why it has to check
rows visibility. But do not understand why Postgres prefers to filter 1000
table rows instead of filtering in index + using visibility map.
Btw, visibility map is up to date:
relpages, reltuples, relallvisible
23478634, 210520464, 23478634
Thank you in advance,
Dmitry
>Пятница, 25 ноября 2022, 18:40 +03:00 от Tom Lane :
>
>=?UTF-8?B?QW5uYSBCLg==?= < te...@bk.ru > 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
delete statement returning too many results
Hello everyone, I am seeing weird behaviour of a delete statement that is returning more results than I am expecting. This is the query: DELETE FROM queue WHERE id IN ( SELECT id FROM queue ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING *; My understanding is that the limit in the sub-select should prevent this query from ever returning more than one result. Sadly I am seeing cases where there is more than one result. This repository has a Java setup that pretty reliably reproduces my issue: https://github.com/ArloL/postgres-query-error-demo I checked the docs for select and delete and couldn’t find any hint for cases where the behaviour of limit might be surprising. Am I missing something? Thanks, Arlo
Re: how to implement add using upsert and trigger?
Hi, I think you need: insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1) on conflict(itemid) do update set value = stat_result.value + new.value, cnt = stat_result.cnt +1; excluded.value and new.value is the same value from inserted record, but your need a current value from stat_result. On 28.11.2022 15:37, yin@163.com wrote: Hi, all： when I using upsert and trigger to update other table automatically： create table stat_detail(itemid bigint not null, value bigint, captime bigint); create table stat_result(itemid bigint primary key, value bigint, cnt bigint); create or replace function inertfunc() returns trigger as $$ begin insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1) on conflict(itemid) do update set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid; return new; end; $$ language plpgsql; create trigger tri_insert after insert on stat_detail for each row execute function inertfunc(); postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 1 | 1 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) But I want it is "100 3 3". So how I can do? yin@163.com -- Pavel Luzanov Postgres Professional:https://postgrespro.com The Russian Postgres Company
Re: Re: how to implement add using upsert and trigger?
em， which is does not matter yin@163.com From: Dominique Devienne Date: 2022-11-28 20:47 To: yin@163.com CC: pgsql-general Subject: Re: how to implement add using upsert and trigger? On Mon, Nov 28, 2022 at 1:37 PM yin@163.com wrote: > on conflict(itemid) do update > set value = excluded.value + new.value, cnt = excluded.cnt +1 where > excluded.itemid = new.itemid; OT, but isn't `where excluded.itemid = new.itemid` redundant, given `on conflict(itemid)`? I'm asking more because I'm not sure, for my own education. Thanks, --DD
Re: how to implement add using upsert and trigger?
On Mon, Nov 28, 2022 at 1:37 PM yin@163.com wrote: > on conflict(itemid) do update > set value = excluded.value + new.value, cnt = excluded.cnt +1 where > excluded.itemid = new.itemid; OT, but isn't `where excluded.itemid = new.itemid` redundant, given `on conflict(itemid)`? I'm asking more because I'm not sure, for my own education. Thanks, --DD
how to implement add using upsert and trigger?
Hi, all： when I using upsert and trigger to update other table automatically： create table stat_detail(itemid bigint not null, value bigint, captime bigint); create table stat_result(itemid bigint primary key, value bigint, cnt bigint); create or replace function inertfunc() returns trigger as $$ begin insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1) on conflict(itemid) do update set value = excluded.value + new.value, cnt = excluded.cnt +1 where excluded.itemid = new.itemid; return new; end; $$ language plpgsql; create trigger tri_insert after insert on stat_detail for each row execute function inertfunc(); postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 1 | 1 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) postgres=# insert into stat_detail values(100,1,1); INSERT 0 1 postgres=# select * from stat_result ; itemid | value | cnt +---+- 100 | 2 | 2 (1 row) But I want it is "100 3 3". So how I can do? yin@163.com
Re: Replicating an existing (huge) database
You can set up wal archiving and set restore_command before you start the replica. Then you can use pg_basebackup with `-Xnone --no-slot`. Alternatively or in combination, use a quicker backup as Laurenz explained. On Mon, Nov 28, 2022 at 11:17 AM Srinivasa T N wrote: > Hi All, >I am using postgresql 12. As part of streaming replication setup, I > run pg_basebackup on the slave which copies the database from master to > slave. But the database is huge and it takes around 2 to 3 days for the > pg_basebackup to finish. When pg_basebackup is running, a huge number of > wal files are generated on the master which occupies a lot of space (even > though it is for 2-3 days, disk space is low). Is there any other way to > start replication without using pg_baseback? > > Regards, > Seenu. >
Re: Replicating an existing (huge) database
On Mon, 2022-11-28 at 15:47 +0530, Srinivasa T N wrote: > I am using postgresql 12. As part of streaming replication setup, I run > pg_basebackup > on the slave which copies the database from master to slave. But the > database is huge > and it takes around 2 to 3 days for the pg_basebackup to finish. When > pg_basebackup > is running, a huge number of wal files are generated on the master which > occupies a lot > of space (even though it is for 2-3 days, disk space is low). > Is there any other way to start replication without using pg_baseback? There is the "low level backup API" (pg_start_backup() and pg_stop_backup()) that allows you to copy the data yourself, for example with storage snapshots, which can be much faster. The essential process is like with pg_basebackup, but you can use more efficient methods to copy the data. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Replicating an existing (huge) database
Hi All, I am using postgresql 12. As part of streaming replication setup, I run pg_basebackup on the slave which copies the database from master to slave. But the database is huge and it takes around 2 to 3 days for the pg_basebackup to finish. When pg_basebackup is running, a huge number of wal files are generated on the master which occupies a lot of space (even though it is for 2-3 days, disk space is low). Is there any other way to start replication without using pg_baseback? Regards, Seenu.
