Re: Querying one partition in a function takes locks on all partitions
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 an exclusive lock on > another partition, like > > alter table entity_2 add column new_column text; Is this just an example command? You can't add a column to a partition directly. > I would expect that to be able to run concurrently, but it blocks due to > the shared lock on entity_2. (The way I originally found the problem was > the opposite: once one client took an exclusive lock on a partition many > others were blocked from reading from ANY partition.) > > This seems like quite the "gotcha", especially when the query plan for > the function call (logged via autoexplain) shows it only accessing one > partition (entity_1). Is this expected behavior? If so, is it documented > somewhere? It is expected behaviour and, unfortunately, not really documented anywhere outside of the source code. What's going on is that PostgreSQL is creating a generic plan for your query, that is, a plan that will work with any parameter value that you give to your function. When the generic plan is executed and the locks are taken for the relations mentioned in the query, and since the plan is generic, it includes all partitions that could match any possible parameter value you could pass. When the locks are taken, it's not yet known which partition will be needed as the partition pruning that occurs only does so after the locks are taken. There has been work done for PostgreSQL 18 which swaps the order of these operations and makes it so that the executor only obtains the locks on the partitions which will actually be scanned. Hopefully, we'll see that feature released with PostgreSQL 18 toward the end of 2025. As for the documentation, it might be worth adding a mention of this at the end of the following paragraph in [1]: "During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE. It is possible to determine the number of partitions which were removed during this phase by observing the “Subplans Removed” property in the EXPLAIN output." Perhaps something like. "It's important to note that any partitions removed by the partition pruning done at this time are still locked at the beginning of execution". David [1] https://www.postgresql.org/docs/17/ddl-partitioning.html
Re: Need help understanding has_function_privilege
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 inherit/noinherit doesn’t apply. (You also cannot SET to it, nor admin it - not tested.) Losing the execute privilege on every built-in function would be way too annoying. David J.
Re: Determine server version from psql script
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 use >13 because that will match v13.1 which is 130001 in
integer format.
David J.
Experience and feedback on pg_restore --data-only
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 cases it makes often sense to get rid of the churn and create a clean database by running the SQL schema definition from version control, and then copy the data for only the tables created. For this case, I choose to run pg_restore --data-only, and run it as the user who owns the database (dbowner), not as a superuser, in order to avoid changes being introduced under the radar. Things that made my life hard: * plenty of permission denials for both ALTER OWNER or SET SESSION AUTHORIZATION (depending on command line switches). Both of these require superuser privilege, but in my case this is not really needed. Dbowner has CREATEROLE and is the one who creates all the roles (WITH SET TRUE), and their private schemata in the specific database. Things would work if pg_restore did "SET ROLE" instead of "SET SESSION AUTHORIZATION" to switch user. Is this a straightforward change or there are issues I don't see? * After each failed attempt, I need to issue a TRUNCATE table1,table2,... before I try again. I wrote my own function for that. It would help if pg_restore would optionally truncate before COPY. I believe it would require superuser privilege for it, that could achieve using the --superuser=username option used today for disabling the triggers. Performance issues: (important as my db size is >5TB) * WAL writes: I didn't manage to avoid writing to the WAL, despite having setting wal_level=minimal. I even wrote my own function to ALTER all tables to UNLOGGED, but failed with "could not change table T to unlogged because it references logged table". I'm out of ideas on this one. * Indices: Could pg_restore have a switch to DROP indices before each COPY, and re-CREATE them after, exactly as they were? This would speed up the process quite a bit. Any feedback for improving my process? Should I put these ideas somewhere as ideas for improvement on pg_restore? Thank you in advance, Dimitris
Re: COLLATION update in 13.1
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, rebuild all indexes on strings, > > then make the warning disappear. > > One last question related to ALTER COLLATION ... In our clusters are also > the databases 'postgres', 'template0' and 'template1'. The latter we > never us and we CREATE new databases from 'template0'. Anyway, does it > make sense to ALTER COLLATION in these databases as well? I would say so, yes. At least on the template you are using for new databases. By the way, your signature makes Google move your e-mail to "spam". Just saying. Yours, Laurenz Albe
Re: Determine server version from psql script
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( current_rank ), 0 ) FROM playersinleague WHERE id = new.id) WHERE
rowid = NEW.rowid;
UPDATE playersinleague SET original_rank = current_rank WHERE rowid
= new.rowid;
END;
\endif
[/code]
And I'm getting this:
[code]
\else
DROP TRIGGER IF EXISTS playersinleague_insert ON playersinleague;
psql:draft_pg.sql:44269: NOTICE: trigger "playersinleague_insert" for
relation "playersinleague" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
WHEN new.current_rank IS NULL
BEGIN
UPDATE playersinleague SET current_rank = 1 + (SELECT coalesce(
max( current_rank ), 0 ) FROM playersinleague WHERE id = new.id) WHERE
rowid = new.rowid;
psql:draft_pg.sql:44272: ERROR: syntax error at or near "new"
LINE 1: ...eague_insert AFTER INSERT ON playersinleague WHEN new.curren...
^
[/code]
What is the problem niw?
Thank you.
On Sun, Mar 23, 2025 at 2:27 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> 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 use >13 because that will match v13.1 which is 130001
in integer format.
>
> David J.
>
Re: Determine server version from psql script
> 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 ( new.current_rank IS NULL )
Re: Determine server version from psql script
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 variables.
>
> > I was hoping for something like
>
> > If server_version >= X:
> > CREATE OR REPLACE TRIGGER...
>
> psql's \if doesn't (yet) have any native expression evaluation
> ability, so you have to farm out the ">=" comparison. The
> psql docs suggest relying on the server to do it, which would
> go along the lines of
>
> select current_setting('server_version_num')::int >= 13 as v13
> \gset
> \if :v13
>... do something
> \else
>... do something else
> \endif
>
Thank for the code.
2 things, however.
1. Apparently CREATE OR RELACE TRIGGER syntax is available since v17, which
is the current one. So I hadto adjust the numbers.. 😀
2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier version?
Thank you.
>
> You could also do the comparison client-side, along the lines of
>
> \set v13 `expr :SERVER_VERSION_NUM \>= 13`
> \if :v13
> ... etc
>
> But that introduces assorted platform dependencies and requires
> close attention to correct shell quoting, so it's seldom
> preferable IMO.
>
> regards, tom lane
>
Re: Best way to check if a table is empty
> 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
> 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 to be at the start of the table, and so you'll hit a live tuple pretty fast. There are pathological cases where it will take a long time (someone just delete a DELETE FROM t; from a huge table and the entire thing is dead tuples but no free space), but that's relatively unlikely to happen in a real-world system. > Pros: > 1. Does an index-only scan on a field that presumably has a PK index. > 2. Works well even if the table is bloated. #1 is not necessarily true. You're never guaranteed an index-only scan; PostgreSQL may decide that the visibility map information indicates that a regular index scan (which means heap fetches to check visibility) will still happen. #2 is not necessarily true either; it's pretty much false in the same situation that a sequential scan for the first tuple will be slow. Indexes get bloated, too, and if there are a large number of dead unreclaimed tuples, there are also going to be a lot of dead index entries for it to walk over. (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.) > SELECT count(*) FROM foo; There's no situation in which this will be faster than #1 or #2.
Re: Best way to check if a table is empty
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 either an empty set or an , never NULL. Always returning a Boolean seems like a better API choice though; but standardizing on that final transform doesn’t change the base comparison. David J.
Re: Determine server version from psql script
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
>UPDATE playersinleague SET current_rank = 1 + (SELECT
> coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =
> new.id) WHERE rowid = new.rowid;
> psql:draft_pg.sql:44265: ERROR: syntax error at or near "TRIGGER"
> LINE 1: CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSER...
> ^
> [/code]
>
> What am I doing wrong?
>
the test should be
SELECT current_setting('server_version_num')::int > =14 as v14
\if :v14
...
CREATE OR REPLACE is supported from PostgreSQL 14
https://www.postgresql.org/docs/14/sql-createtrigger.html
Regards
Pavel
>
> Thank you.
>
>
> On Sun, Mar 23, 2025 at 12:53 PM David G. Johnston
> wrote:
> >
> > 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 always query the catalogs directly to answer the question
> “does this trigger exist”.
> >
> > David J.
> >
>
>
>
Re: Determine server version from psql script
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 always query the catalogs directly to answer the question “does this trigger exist”. David J.
Re: COLLATION update in 13.1
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. > > Yours, > Laurenz Albe One last question related to ALTER COLLATION ... In our clusters are also the databases 'postgres', 'template0' and 'template1'. The latter we never us and we CREATE new databases from 'template0'. Anyway, does it make sense to ALTER COLLATION in these databases as well? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Annalena Baerbock: "We are fighting a war against Russia ..." (25.1.2023) I, Matthias, I am not at war with Russia. Я не воюю с Россией. Ich bin nicht im Krieg mit Russland.
Re: Determine server version from psql script
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.
