Re: Querying one partition in a function takes locks on all partitions

2025-03-23 Thread David Rowley
On Sat, 22 Mar 2025 at 05:27, Evgeny Morozov wrote: > select read_partition(1); -- This takes shared locks on entity_1 AND > entity_2 > > -- select count(*) from entity where part_id = 1; -- but this would only > take a shared lock only on entity_1 > > If another session tries something that takes

Re: Need help understanding has_function_privilege

2025-03-23 Thread David G. Johnston
On Friday, March 21, 2025, Cosimo Simeone wrote: > Hi, and thanks (both of you!) > Shouldn't the > create role my_user NOINHERIT; > avoid this? And since not, why? :-) > > We might need to improve documentation surrounding the public pseudo-role a bit. Since it’s not a true group role I suspect

Re: Determine server version from psql script

2025-03-23 Thread David G. Johnston
On Sunday, March 23, 2025, Pavel Stehule wrote: > Hi > > ne 23. 3. 2025 v 19:31 odesílatel Igor Korot napsal: > >> Hi, >> >> [code] >> SELECT current_setting('server_version_num')::int > 13 as v13 >> > > SELECT current_setting('server_version_num')::int > =14 as v14 > IOW, you can’t us

Experience and feedback on pg_restore --data-only

2025-03-23 Thread Dimitrios Apostolou
Rationale: When restoring a backup in an emergency situation, it's fine to run pg_restore as superuser and get an exact replica of the dumped db. AFAICT pg_restore (without --data-only) is optimised for such case. But pg_dump/restore can be used as a generic data-copying utility, and in those ca

Re: COLLATION update in 13.1

2025-03-23 Thread Laurenz Albe
On Mon, 2025-03-24 at 06:57 +0100, Matthias Apitz wrote: > El día lunes, febrero 24, 2025 a las 12:41:05p. m. +0100, Laurenz Albe > escribió: > > Perhaps I need not say that, but ALTER COLLATION ... REFRESH VERSION only > > makes the warning disappear. > > > > If you want to avoid data corruption

Re: Determine server version from psql script

2025-03-23 Thread Igor Korot
Hi, This is what : [code[ \else DROP TRIGGER IF EXISTS playersinleague_insert ON playersinleague; CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague WHEN new.current_rank IS NULL BEGIN UPDATE playersinleague SET current_rank = 1 + (SELECT coalesce( max( curre

Re: Determine server version from psql script

2025-03-23 Thread Christophe Pettus
> On Mar 23, 2025, at 18:08, Igor Korot wrote: > CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague > WHEN new.current_rank IS NULL The WHEN predicate has to be enclosed in parenthes: CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague WHEN (

Re: Determine server version from psql script

2025-03-23 Thread Igor Korot
Hi, Tom, On Sat, Mar 22, 2025, 10:01 PM Tom Lane wrote: > Igor Korot writes: > > On Sat, Mar 22, 2025, 8:58 PM David G. Johnston < > david.g.johns...@gmail.com> > > wrote: > >> Then read the psql docs. Your version has \if and you’ll find server > >> version listed as the available client va

Re: Best way to check if a table is empty

2025-03-23 Thread Christophe Pettus
> On Mar 23, 2025, at 21:15, David G. Johnston > wrote: > > No idea if we take that shortcut. I remember looking into that not too long ago, and the answer's no.

Re: Best way to check if a table is empty

2025-03-23 Thread Christophe Pettus
> On Mar 23, 2025, at 20:42, Marcelo Fernandes wrote: > Cons: > 1. Sequential Scan > 2. If the table is bloated, it reads more buffers. These concerns probably sound worse than they really are. Assuming the table is being vacuumed reliably, and is receiving inserts, those inserts will tend

Re: Best way to check if a table is empty

2025-03-23 Thread David G. Johnston
On Sunday, March 23, 2025, Christophe Pettus wrote: > > (And to be pedantic, #1 returns TRUE / FALSE while #2 returns / > NULL, so they aren't exactly equivalent. If you are able to handle / > NULL, you don't need the EXISTS clause.) > Leaving the limit in place, without exists #1 returns eit

Re: Determine server version from psql script

2025-03-23 Thread Pavel Stehule
Hi ne 23. 3. 2025 v 19:31 odesílatel Igor Korot napsal: > Hi, > > [code] > SELECT current_setting('server_version_num')::int > 13 as v13 > \gset > \if :v13 >CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT ON > playersinleague WHEN new.current_rank IS NULL >BEGIN >

Re: Determine server version from psql script

2025-03-23 Thread David G. Johnston
On Sunday, March 23, 2025, David G. Johnston wrote: > On Sunday, March 23, 2025, Igor Korot wrote: > >> >> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier >> version? >> > > No. You have to drop the trigger if it does exist and then create the new > one. > Well, you can alw

Re: COLLATION update in 13.1

2025-03-23 Thread Matthias Apitz
El día lunes, febrero 24, 2025 a las 12:41:05p. m. +0100, Laurenz Albe escribió: > Perhaps I need not say that, but ALTER COLLATION ... REFRESH VERSION only > makes the warning disappear. > > If you want to avoid data corruption, rebuild all indexes on strings, > then make the warning disappear.

Re: Determine server version from psql script

2025-03-23 Thread David G. Johnston
On Sunday, March 23, 2025, Igor Korot wrote: > > CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague > WHEN new.current_rank IS NULL > > When the syntax shows parentheses you are required to write them. [ WHEN ( *condition* ) ] David J.