Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

[Peter J. Holzer]

On 2022-11-18 16:21:18 -0600, Ron wrote:
> On 11/18/22 16:13, Peter J. Holzer wrote:
> > So you can give these credentials to you developers or devops folks
> > (whom you trust not attack the system -
> 
> They like to "fix" things without documenting what they did, and then, when
> something breaks, denying they did anything (or honestly not believing that
> whatever "trivial" thing they did could any major or deliterious impact).

Which is why you want to automate deployments. When the easiest way to
fix something in production is to just push the change into the repo,
this is what people will do. When doing it the "proper" way is much more
complicated than just fudging it, people will do the latter.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Duda sobre como imprimir un campo INTERVAL

[Alban Hertroys]

> On 19 Nov 2022, at 4:58, Ken Tanzer  wrote:
> 
> On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel 
>  wrote:

(…)

> don't fully understand it.  But what really confuses me is the example below. 
>  How can these two intervals be equal and still yield different output in the 
> to_char function?  And as a practical matter, and for the OPs question, how 
> can you convert from one to the other of these "equal" values?
> 
> WITH inters AS (
> SELECT
> '1 day 2 hours'::interval AS i1,
> '26 hours'::interval AS i2
> )
> SELECT
> *,
> to_char(i1,'HH24:MM:SS') AS i1_char,
> to_char(i2,'HH24:MM:SS') AS i2_char,
> i1=i2 AS "Equal?"
> FROM inters;
> 
>i1   |i2| i1_char  | i2_char  | Equal? 
> +--+--+--+
>  1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
> 
> Cheers,
> Ken

Those intervals are not identical. I think the reasoning is that due to DST 
changes, ‘1 day 2 hours’ is more specific than its conversion to ’26 hours’ (or 
25 or 27 at DST change).
And since you’re not converting the number of days in to_char, that information 
gets lost.

That problem doesn’t seem to arise in the OP’s question (as far as I understand 
his question), he does have dates to base the intervals on. However, converting 
the differences in dates to intervals decouples the difference from the dates 
(the intervals could, for example, subsequently be added to an entirely 
different date) and he ends up in the same boat.

It would seem that the way to do this is to convert the difference to (seconds 
since) epoch and do the math to convert that to a character string yourself.

See for example:
https://stackoverflow.com/questions/341384/how-to-convert-an-interval-like-1-day-013000-into-253000

That seems unnecessarily complicated, perhaps there is/could be a more 
convenient method? I’m sort of thinking of a "relative timestamp offset" type, 
that tracks an exact difference relative to a given timestamp?

Alban Hertroys
--
There is always an exception to always.

RES: RES: session_user different from current_user after normal login

[Murillo corvino rocha]

Could it be the role=group_read_only? The fact that user1 is a member of 
group_read_only is changing current_user variable?
I’m using a clean psql docker container everytime I need to connect to the 
database, so, I don’t think it’s related to psql.

\drds
List of settings
   Role| Database | Settings
--+--+---
user1| mydb   | role=group_read_only
rdsadmin | rdsadmin | log_min_messages=panic
rdsadmin |   | TimeZone=utc 
  +
   |   | log_statement=all  
   +
   |   | log_min_error_statement=debug5   +
   |   | log_min_messages=panic 
   +
   |   | exit_on_error=0
 +
   |   | statement_timeout=0
   +
   |   | role=rdsadmin  
  +
   |   | auto_explain.log_min_duration=-1   +
   |   | temp_file_limit=-1 
   +
   |   | search_path=pg_catalog, public 
+
   |   | pg_hint_plan.enable_hint=off   
  +
   |   | default_transaction_read_only=off+
   |   | default_tablespace=
   | rdsadmin | auto_explain.log_min_duration=-1
(4 rows)

Thanks.

Murillo.

De: Tom Lane
Enviado:sexta-feira, 18 de novembro de 2022 11:31
Para: Murillo corvino rocha
Cc:Adrian Klaver; 
pgsql-general@lists.postgresql.org
Assunto: Re: RES: session_user different from current_user after normal login

Murillo corvino rocha  writes:
> I am just logging in (using psql) and doing the select: SELECT session_user, 
> current_user;
> The PostgreSQL is an AWS RDS 12.12 instance. No script is being executed. No 
> one besides me is connected to the database. Could it be any server level 
> configuration? I’m pretty sure that few configurations were made to this 
> database.

I believe it's possible to do ALTER USER ... SET ROLE ... or
ALTER DATABASE ... SET ROLE ..., which'd produce this sort of
effect.  psql's "\drds" metacommand would help remind you of
any such settings.  I can't offhand think of any other server-side
configuration that would do it.

regards, tom lane

Re: Duda sobre como imprimir un campo INTERVAL

[Ken Tanzer]

On Sat, Nov 19, 2022 at 2:39 AM Alban Hertroys  wrote:

>
>
> > don't fully understand it.  But what really confuses me is the example
> below.  How can these two intervals be equal and still yield different
> output in the to_char function?  And as a practical matter, and for the OPs
> question, how can you convert from one to the other of these "equal" values?
> >
> > WITH inters AS (
> > SELECT
> > '1 day 2 hours'::interval AS i1,
> > '26 hours'::interval AS i2
> > )
> > SELECT
> > *,
> > to_char(i1,'HH24:MM:SS') AS i1_char,
> > to_char(i2,'HH24:MM:SS') AS i2_char,
> > i1=i2 AS "Equal?"
> > FROM inters;
> >
> >i1   |i2| i1_char  | i2_char  | Equal?
> > +--+--+--+
> >  1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
> >
>
> Those intervals are not identical. I think the reasoning is that due to
> DST changes, ‘1 day 2 hours’ is more specific than its conversion to ’26
> hours’ (or 25 or 27 at DST change).


Thanks.  I could understand that they're not identical.   But then what's
going on where Postgres evaluates them as equal?  (i1=i2 above.)  Are the
two intervals getting cast or converted to something else before they are
compared, with whatever makes them non-identical getting lost in the
conversion?

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Duda sobre como imprimir un campo INTERVAL

[Tom Lane]

Ken Tanzer  writes:
> Thanks.  I could understand that they're not identical.   But then what's
> going on where Postgres evaluates them as equal?  (i1=i2 above.)  Are the
> two intervals getting cast or converted to something else before they are
> compared, with whatever makes them non-identical getting lost in the
> conversion?

Hmm ... the code is pretty clear about what it thinks it's doing,
but I'm not sure if this info exists anywhere in the user-facing docs:

 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.

So once '1 day 2 hours' and '26 hours' are flattened into this linear
representation, they are indeed "equal".  There's precedent elsewhere
for values that are "equal" but not identical, so it's not a bug,
even if it's not the semantics you'd want for some particular use-case.

I think the main factor behind having done it this way is that we need
a linear sort order if we want to support btree indexes or ORDER BY
on intervals.

You can use justify_hours() to get from '26 hours' to '1 day 2 hours'.
I'm not sure if there's a compact way to go the other direction,
though you could always use extract() to get the components and
sum them up.

regards, tom lane