Re: Code of Conduct plan
On 5 June 2018 at 17:34, Ozz Nixon wrote: > Sorry... > > > 1) CoC might result in developers leaving projects > > > I know this on going regurgitation is going to cause my team to > leave the project, right around 100 posts on this off topic topic it > was bad enough when the original idea came up (2 years ago I think). It > used to be exciting to sit back and review the day or weeks posts... not > much anymore. > With all due respect, it is completely unreasonable to quit just because there has been some discussion of the rules for co-existing within the project. The intent of codes of conduct is usually supposed to be to make it clear that bullying and harassment are not permitted, something that is not always clear to everybody. That doesn't mean that any particular position on them is required, only that discussion of them is definitely *not* off topic. In any event, if you aren't interested in a thread, you can easily mute it. Personally, I have about 95% of pgsql-hackers muted, because I simply don't have time to be interested in every topic that is discussed, and I suspect many subscribers are similar. If somebody is so sensitive to even being aware of a discussion of the issue that they feel they have to leave, then I would expect them to leave at some point anyway due to becoming offended by some trivial matter that nobody else would even notice.
Re: jsonb_set() strictness considered harmful to data
On Sun, 20 Oct 2019 at 08:32, Andrew Dunstan wrote: > > Understood. I think the real question here is what it should do instead > when the value is NULL. Your behaviour above is one suggestion, which I > personally find intuitive. Another has been to remove the associated > key. Another is to return the original target. And yet another is to > raise an exception, which is easy to write but really punts the issue > back to the application programmer who will have to decide how to ensure > they never pass in a NULL parameter. Possibly we could even add an extra > parameter to specify what should be done. > I vote for remove the key. If we make NULL and 'null'::jsonb the same, we're missing an opportunity to provide more functionality. Sometimes it's convenient to be able to handle both the "update" and "remove" cases with one function, just depending on the parameter value supplied. Also, the question will arise what to do when any of the other > parameters are NULL. Should we return NULL in those cases as we do now? > I would argue that only if the target parameter (the actual json value) is NULL should the result be NULL. The function is documented as returning the target, with modifications to a small part of its structure as specified by the other parameters. It is strange for the result to suddenly collapse down to NULL just because another parameter is NULL. Perhaps if the path is NULL, that can mean "don't update". And if create_missing is NULL, that should mean the same as not specifying it. I think. At a minimum, if we don't change it, the documentation needs to get one of those warning boxes alerting people that the functions will destroy their input entirely rather than slightly modifying it if any of the other parameters are NULL. My only doubt about any of this is that by the same argument, functions like replace() should not return NULL if the 2nd or 3rd parameter is NULL. I'm guessing replace() is specified by SQL and also unchanged in many versions so therefore not eligible for re-thinking but it still gives me just a bit of pause.
recovery.signal not being removed when recovery complete
I use a script to restore a backup to create a testing copy of the database. I set the following in postgresql.auto.conf: recovery_target = 'immediate' recovery_target_action = 'promote' In the logs I get "recovery stopping after reaching consistency" then a moment later "database system is ready to accept read-only connections", then some entries about restoring log files, then "database system is ready to accept connections". I am able to make changes (e.g. CREATE TABLE), yet recovery.signal is still present. My understanding is that recovery.signal should be removed when recovery is finished (i.e., more or less when "database system is ready to accept connections" is logged?), unless recovery_target_action is set to 'shutdown'. Any ideas? Even just confirming/denying I understand the above correctly would help.
Re: \dt shows table but \d says the table doesn't exist ?
On Fri, 3 May 2024 at 17:28, Tom Lane wrote: This is one of the places where it's unfortunate that our English-text > rule for quoting a string to set it off from the rest of the error > message collides with SQL's rule for quoting an identifier. Leaving > out the outer quotes would be contrary to our style guide, but having > them there can be confusing too to people who know SQL well. I'm not sure if this is a serious suggestion or not: enclose the name of the table, as you would type it in psql, between curly quotes. So for example: “table_name” “"TableNameWithUpperCaseLetters"” In a context where the curly quotes and surrounding message could be in a non-monospaced typeface and the table name (including double quote marks if needed) in a monospaced typeface this would be more natural.
Re: Finding "most recent" using daterange
On Wed, 22 May 2024 at 10:15, Greg Sabino Mullane wrote: > This is a good candidate for a window function. Also note that nulls > already get sorted correctly by the DESC so no need to get 'infinity' > involved, although you could write 'DESC NULLS FIRST' to be explicit about > it. > > with x as (select *, row_number() over (partition by id order by > upper(dates) desc, lower(dates) desc) from example) > select id,value,dates from x where row_number = 1; > Don’t you need NULLS LAST for the lower bounds? There NULL means something closer to -infinity and should appear after the non-NULL values in a descending sort. Actually it strikes me that this sorting issue could be a reason to avoid NULL bounds on ranges and prefer the use of +/-infinity if the underlying data type supports it.
Re: Finding "most recent" using daterange
On Wed, 22 May 2024 at 11:36, Greg Sabino Mullane wrote: > Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity. > :) > NULLS LAST for lower bound, NULLS FIRST for upper bound. The other way around if you were doing an ascending sort.
Re: search_path and SET ROLE
On Wed, 22 May 2024 at 13:48, Ron Johnson wrote: As a superuser administrator, I need to be able to see ALL tables in ALL > schemas when running "\dt", not just the ones in "$user" and public. And I > need it to act consistently across all the systems. > \dt *.* But I am skeptical how often you really want this in a real database with more than a few tables. Surely \dn+ followed by \dt [schemaname].* for a few strategically chosen [schemaname] would be more useful?
Re: Does trigger only accept functions?
On Tue, 11 Jun 2024 at 18:25, Ron Johnson wrote: Since all the functions are going to be similar, I'd write a shell script > to generate all the triggers, one per relevant. If you're going to record > every field, then save effort, and don't bother enumerating them. You'll > need to dig into the PG catalog's guts to list columns in the correct > order, but Google and Stack Exchange makes that easy enough. > I'd use a DO block and write a loop in PL/PGSQL. Then everything stays in Postgres and you have all the support of Postgres when writing your SQL-writing code (quote_ident, the reg* types, etc.).
Re: current_role of caller of a DEFINER function
On Wed, 26 Jun 2024 at 08:42, David G. Johnston wrote: > On Wednesday, June 26, 2024, Dominique Devienne > wrote: > >> Only session_user >> is representative of the caller, and reliable (modulo SUPERUSER and >> SET AUTHORIZATION, but that's a different story and kinda normal) >> > > Why can you not use session_user then? > Speaking only for myself, if I am writing a security definer and I go to check the calling role, I want to know the role which was used in the privilege check as to whether the function would even be permitted to be called. What I would be looking for is to behave differently depending on who called me. The original role which connected to the database is totally irrelevant, and could even be a security problem: if superuser does a set role, I shouldn't then be doing security checks which report back that the current role is superuser. Imagine code like this: select objects from table where owner = [calling role] … I think this ties into the related discussions on questions like what search_path should be in effect during trigger execution and during REFRESH MATERIALIZED VIEW and other maintenance commands. It also relates into the question of what role executes triggers and performs calculations during REFRESH MATERIALIZED VIEW and other maintenance commands. Essentially the current behaviour is quirky and built up over time by a series of individual decisions, and does not appear to have any systematic theory of operation which would answer all these questions all at once.
Re: search_path for PL/pgSQL functions partially cached?
On Fri, 3 Jan 2025 at 18:22, Jan Behrens wrote: > Instead, I plan to expect the function to receive a query string that > will get the data that is being processed by the function. > > That query string should be allowed to refer to tables in the > search_path at the caller's side. > > Therefore, I cannot use the "SET search_path FROM CURRENT" in my > "CREATE FUNCTION" statement, because it would overwrite the current > search_path on each call of the function. > I wonder if it would help if EXECUTE took an optional search_path to use while executing the query.
Re: Request for new column in pg_namespace
On Sun, 15 Dec 2024 at 12:29, Tom Lane wrote: > What I'd suggest as an improvement that could be implemented > immediately is to wrap the checks in a user-defined function > like "is_system_schema(nspname name)". > Would it make sense to make the parameter be of type regnamespace?
Re: Request for new column in pg_namespace
On Sun, 15 Dec 2024 at 14:20, Tom Lane wrote: > Isaac Morland writes: > > On Sun, 15 Dec 2024 at 12:29, Tom Lane wrote: > >> What I'd suggest as an improvement that could be implemented > >> immediately is to wrap the checks in a user-defined function > >> like "is_system_schema(nspname name)". > > > Would it make sense to make the parameter be of type regnamespace? > > Meh ... you could, but what the function really needs is the name. > Getting from regnamespace (which is an OID) to the name would incur > an extra syscache lookup. Admittedly, if it removes the need for > the calling query to join to pg_namespace at all, you'd probably > come out about even --- the net effect would be about like a > hashjoin to pg_namespace, I think, since the syscache would act > like the inner hashtable of a hashjoin. Thanks for the critique. It occurs to me that this function is perhaps just as much about “is this name a system-reserved name?” as “is this schema a system schema?”. So putting in a name that doesn’t actually exist in the database should be perfectly valid, which of course only works if it takes a string. Generally speaking I am a big fan of the reg* data types but in this specific case I think it’s not a clear win. I might still suggest providing both versions using function overloading.
Re: Automatic upgrade of passwords from md5 to scram-sha256
On Sun, 12 Jan 2025 at 17:59, Tom Lane wrote: > "Peter J. Holzer" writes: > > The web framework Django will automatically and transparently rehash any > > password with the currently preferred algorithm if it isn't stored that > > way already. > > Really? That implies that the framework has access to the original > cleartext password, which is a security fail already. It happens upon user login. If the user's password is hashed with an old algorithm, it is re-hashed during login when the Django application running on the Web server has the password sent by the user: https://docs.djangoproject.com/en/5.1/topics/auth/passwords/#password-upgrading But of course this only works if the old method in use involves sending the password to the server.
