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: 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: 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: \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.
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: 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.
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.
