Re: how to install plprofiler

[Pavel Stehule]

Hi


so 29. 10. 2022 v 8:44 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:

> Hello,
>
> I am in a process of upgrading postgresql 12 to version 13, when I am
> testing it getting the below error
>  could not load library $carlib/plprofiler
>   "/usr/lib/postgresql/13/lib/plprofiler.so":
> /usr/lib/postgresql/13/lib/plprofiler.so: undefined symbol: hash_uint32
>
> Could you please let me know how to install plprofiler on version 13 to
> pass this error.
>

Did you install plprofiler for PostgreSQL 13?

If yes, then you need to uninstall plprofiler before upgrade. Some
extensions doesn't support upgrade between major versions, or doesn't
supports upgrade Postgres and upgrade the extension in one step.

Regards

Pavel


> OS is Ubuntu
> --
> Shashidhar
>

Re: how to install plprofiler

[shashidhar Reddy]

Pavel,

After Uninstall plprofiler where can find this to install again.

On Sat, 29 Oct, 2022, 12:42 pm Pavel Stehule, 
wrote:

> Hi
>
>
> so 29. 10. 2022 v 8:44 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Hello,
>>
>> I am in a process of upgrading postgresql 12 to version 13, when I am
>> testing it getting the below error
>>  could not load library $carlib/plprofiler
>>   "/usr/lib/postgresql/13/lib/plprofiler.so":
>> /usr/lib/postgresql/13/lib/plprofiler.so: undefined symbol: hash_uint32
>>
>> Could you please let me know how to install plprofiler on version 13 to
>> pass this error.
>>
>
> Did you install plprofiler for PostgreSQL 13?
>
> If yes, then you need to uninstall plprofiler before upgrade. Some
> extensions doesn't support upgrade between major versions, or doesn't
> supports upgrade Postgres and upgrade the extension in one step.
>
> Regards
>
> Pavel
>
>
>> OS is Ubuntu
>> --
>> Shashidhar
>>
>

Re: how to install plprofiler

[shashidhar Reddy]

I am sorry I have not installed it for version 13 as I am not able to find
the source

On Sat, 29 Oct, 2022, 12:55 pm shashidhar Reddy, <
shashidharreddy...@gmail.com> wrote:

> Pavel,
>
> After Uninstall plprofiler where can find this to install again.
>
> On Sat, 29 Oct, 2022, 12:42 pm Pavel Stehule, 
> wrote:
>
>> Hi
>>
>>
>> so 29. 10. 2022 v 8:44 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Hello,
>>>
>>> I am in a process of upgrading postgresql 12 to version 13, when I am
>>> testing it getting the below error
>>>  could not load library $carlib/plprofiler
>>> "/usr/lib/postgresql/13/lib/plprofiler.so":
>>> /usr/lib/postgresql/13/lib/plprofiler.so: undefined symbol: hash_uint32
>>>
>>> Could you please let me know how to install plprofiler on version 13 to
>>> pass this error.
>>>
>>
>> Did you install plprofiler for PostgreSQL 13?
>>
>> If yes, then you need to uninstall plprofiler before upgrade. Some
>> extensions doesn't support upgrade between major versions, or doesn't
>> supports upgrade Postgres and upgrade the extension in one step.
>>
>> Regards
>>
>> Pavel
>>
>>
>>> OS is Ubuntu
>>> --
>>> Shashidhar
>>>
>>

Re: how to install plprofiler

[Pavel Stehule]

so 29. 10. 2022 v 9:25 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:

> Pavel,
>
> After Uninstall plprofiler where can find this to install again.
>

 https://github.com/bigsql/plprofiler/blob/master/doc/installation.md



> On Sat, 29 Oct, 2022, 12:42 pm Pavel Stehule, 
> wrote:
>
>> Hi
>>
>>
>> so 29. 10. 2022 v 8:44 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Hello,
>>>
>>> I am in a process of upgrading postgresql 12 to version 13, when I am
>>> testing it getting the below error
>>>  could not load library $carlib/plprofiler
>>> "/usr/lib/postgresql/13/lib/plprofiler.so":
>>> /usr/lib/postgresql/13/lib/plprofiler.so: undefined symbol: hash_uint32
>>>
>>> Could you please let me know how to install plprofiler on version 13 to
>>> pass this error.
>>>
>>
>> Did you install plprofiler for PostgreSQL 13?
>>
>> If yes, then you need to uninstall plprofiler before upgrade. Some
>> extensions doesn't support upgrade between major versions, or doesn't
>> supports upgrade Postgres and upgrade the extension in one step.
>>
>> Regards
>>
>> Pavel
>>
>>
>>> OS is Ubuntu
>>> --
>>> Shashidhar
>>>
>>

Re: how to install plprofiler

[shashidhar Reddy]

Thank you Pavel it worked for me.

On Sat, 29 Oct, 2022, 12:59 pm Pavel Stehule, 
wrote:

>
>
> so 29. 10. 2022 v 9:25 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Pavel,
>>
>> After Uninstall plprofiler where can find this to install again.
>>
>
>  https://github.com/bigsql/plprofiler/blob/master/doc/installation.md
>
>
>
>> On Sat, 29 Oct, 2022, 12:42 pm Pavel Stehule, 
>> wrote:
>>
>>> Hi
>>>
>>>
>>> so 29. 10. 2022 v 8:44 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
 Hello,

 I am in a process of upgrading postgresql 12 to version 13, when I am
 testing it getting the below error
  could not load library $carlib/plprofiler
 "/usr/lib/postgresql/13/lib/plprofiler.so":
 /usr/lib/postgresql/13/lib/plprofiler.so: undefined symbol: hash_uint32

 Could you please let me know how to install plprofiler on version 13 to
 pass this error.

>>>
>>> Did you install plprofiler for PostgreSQL 13?
>>>
>>> If yes, then you need to uninstall plprofiler before upgrade. Some
>>> extensions doesn't support upgrade between major versions, or doesn't
>>> supports upgrade Postgres and upgrade the extension in one step.
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
 OS is Ubuntu
 --
 Shashidhar

>>>

Upgrade error from version 12 to 23

[shashidhar Reddy]

I am upgrading postures from version 12 to 13 and getting below error when
using pg_upgrade

Error is checking database user is the install user only the install user
can be defined in the new cluster.

Note: I am using postures user

access method xxx does not exist

[jack...@gmail.com]

I'm trying to add a new index, but when I finish it, I use “ create index 
xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not 
exist
And I don't know where this message is from, can you grve me its position? I do 
like this. I add oid in pg_am.dat and pg_proc.dat for my index. And I add codes 
in contrib and backend/access/myindex_name, is there any other places I need to 
add some infos? Who can help me?


jack...@gmail.com

回复: access method xxx does not exist

[jack...@gmail.com]

I'm trying to add a new index, but when I finish it, I use “ create index 
xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not 
exist
And I don't know where this message is from, can you grve me its position? I do 
like this. I add oid in pg_am.dat and pg_proc.dat for my index. And I add codes 
in contrib and backend/access/myindex_name, is there any other places I need to 
add some infos? Who can help me?


jack...@gmail.com

Error while upgrading from v12 to v13

[shashidhar Reddy]

I am getting below error while upgrading postgres from version 12 to 13 and
not sure how to enable the function before upgrading the cluster. Can you
please help me to resolve this.

 error: could not execute query: ERROR:  could not find function
"pl_profiler_enable" in file
"/usr/lib/postgresql/13/lib/plprofiler.so"
Command was: CREATE FUNCTION "public"."pl_profiler_enable"("enabled"
boolean) RETURNS boolean
LANGUAGE "c"
AS '$libdir/plprofiler', 'pl_profiler_enable';
Shashidhar

Re: access method xxx does not exist

[Peter J. Holzer]

On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote:
> I'm trying to add a new index, but when I finish it, I use “ create index
> xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not
> exist
> And I don't know where this message is from, can you grve me its position?

See https://www.postgresql.org/docs/current/sql-createindex.html

The syntax for CREATE INDEX is 

CREATE INDEX ON table_name [ USING method ] ( column_name ... )

You use USING to specify the method (e.g. btree or gin), not the table
and/or columns. The columns (or expressions come in parentheses after
that.

So if you wanted an index on column a of table t1 you would simply
write:

CREATE INDEX ON t1 (a);

Or if you have a function xxx and you want a function based index on
xxx(a) of that table:

CREATE INDEX ON t1 (xxx(a));

(You can specify the name of the index, but why would you?)

> I do like this. I add oid in pg_am.dat and pg_proc.dat for my index.
> And I add codes in contrib and backend/access/myindex_name, is there
> any other places I need to add some infos?

What? Why are you doing these things?

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: Re: access method xxx does not exist

[jack...@gmail.com]

On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote:
> I'm trying to add a new index, but when I finish it, I use “ create index
> xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does not
> exist
> And I don't know where this message is from, can you grve me its position?

See https://www.postgresql.org/docs/current/sql-createindex.html

The syntax for CREATE INDEX is 

CREATE INDEX ON table_name [ USING method ] ( column_name ... )

You use USING to specify the method (e.g. btree or gin), not the table
and/or columns. The columns (or expressions come in parentheses after
that.

So if you wanted an index on column a of table t1 you would simply
write:

CREATE INDEX ON t1 (a);

Or if you have a function xxx and you want a function based index on
xxx(a) of that table:

CREATE INDEX ON t1 (xxx(a));

(You can specify the name of the index, but why would you?)

> I do like this. I add oid in pg_am.dat and pg_proc.dat for my index.
> And I add codes in contrib and backend/access/myindex_name, is there
> any other places I need to add some infos?

What? Why are you doing these things?

hp

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

Re: access method xxx does not exist

[Julien Rouhaud]

Hi,

On Sat, Oct 29, 2022 at 08:15:54PM +0800, jack...@gmail.com wrote:
> On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote:
> > I'm trying to add a new index, but when I finish it, I use “ create index
> > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does 
> > not
> > exist

You should look at the bloom contrib in postgres source tree for an example of
how to write a custom index AM.

Re: Re: access method xxx does not exist

[jack...@gmail.com]

Hi,

On Sat, Oct 29, 2022 at 08:15:54PM +0800, jack...@gmail.com wrote:
> On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote:
> > I'm trying to add a new index, but when I finish it, I use “ create index
> > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does 
> > not
> > exist

You should look at the bloom contrib in postgres source tree for an example of
how to write a custom index AM.

Re: access method xxx does not exist

[Julien Rouhaud]

Hi,

On Sat, Oct 29, 2022 at 09:43:51PM +0800, jack...@gmail.com wrote:
> --=_001_NextPart037628267087_=
> Content-Type: text/html; charset="utf-8"
> Content-Transfer-Encoding: quoted-printable
> 
> Yes,I just want to know if I add a=
> m in pg_am.dat, after I make install, it means the new access method is bui=
> lt? right?  lor=3D"#b5c4df" size=3D"1" align=3D"left"> 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt">jack...@gmail.com div> margin-Left: 0.5em; margin-Right: inherit">  order:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm"> tyle=3D"PADDING-RIGHT: 8px; PADDING-LEFT: 8px; FONT-SIZE: 12px;FONT-FAMILY:=
> tahoma;COLOR:#00; BACKGROUND: #efefef; PADDING-BOTTOM: 8px; PADDING-TOP=
> : 8px">From: mailto:rjuju...@gmail.com";>Julien =
> RouhaudDate: 2022-10-29 21:27=
> To: mailto:jack...@gmail.com";>jack...@gmail.com v>CC: mailto:hjp-pg...@hjp.at";>Peter J. Holzer<=
> /a>; mailto:pgsql-general@lists.postgresql.org";>pgsql-general >Subject: Re: access method xxx does not exist=
> div.FoxDIV_20221029212802774 { font=
> -size: 10.5pt }
> https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics),
many people (and the archives) can't work with it.

Then, yes it should but it's impossible to know what you did wrong without more
details.  And in any case, if you want to add a new index AM, you should
probably create it as an extension, like bloom, rather than putting in postgres
source code.

Re: Error while upgrading from v12 to v13

[shashidhar Reddy]

Any help in this?

On Sat, 29 Oct, 2022, 5:09 pm shashidhar Reddy, <
shashidharreddy...@gmail.com> wrote:

>
> I am getting below error while upgrading postgres from version 12 to 13
> and not sure how to enable the function before upgrading the cluster. Can
> you please help me to resolve this.
>
>  error: could not execute query: ERROR:  could not find function
> "pl_profiler_enable" in file
> "/usr/lib/postgresql/13/lib/plprofiler.so"
> Command was: CREATE FUNCTION "public"."pl_profiler_enable"("enabled"
> boolean) RETURNS boolean
> LANGUAGE "c"
> AS '$libdir/plprofiler', 'pl_profiler_enable';
> Shashidhar
>

Re: Error while upgrading from v12 to v13

[Imre Samu]

> ERROR:  could not find function "pl_profiler_enable" in file

maybe obsolete function?

-- Drop obsolete function
DROP FUNCTION pl_profiler_enable(bool);
https://github.com/bigsql/plprofiler/blob/master/plprofiler--3.5--4.0.sql

Regards,
 Imre

shashidhar Reddy  ezt írta (időpont: 2022.
okt. 29., Szo, 13:40):

>
> I am getting below error while upgrading postgres from version 12 to 13
> and not sure how to enable the function before upgrading the cluster. Can
> you please help me to resolve this.
>
>  error: could not execute query: ERROR:  could not find function
> "pl_profiler_enable" in file
> "/usr/lib/postgresql/13/lib/plprofiler.so"
> Command was: CREATE FUNCTION "public"."pl_profiler_enable"("enabled"
> boolean) RETURNS boolean
> LANGUAGE "c"
> AS '$libdir/plprofiler', 'pl_profiler_enable';
> Shashidhar
>

Re: Error while upgrading from v12 to v13

[shashidhar Reddy]

I have dropped the function on all databases on version 12,but while
upgrading I am still getting the same error

On Sat, 29 Oct, 2022, 7:56 pm Imre Samu,  wrote:

> > ERROR:  could not find function "pl_profiler_enable" in file
>
> maybe obsolete function?
>
> -- Drop obsolete function
> DROP FUNCTION pl_profiler_enable(bool);
> https://github.com/bigsql/plprofiler/blob/master/plprofiler--3.5--4.0.sql
>
> Regards,
>  Imre
>
> shashidhar Reddy  ezt írta (időpont: 2022.
> okt. 29., Szo, 13:40):
>
>>
>> I am getting below error while upgrading postgres from version 12 to 13
>> and not sure how to enable the function before upgrading the cluster. Can
>> you please help me to resolve this.
>>
>>  error: could not execute query: ERROR:  could not find function
>> "pl_profiler_enable" in file
>> "/usr/lib/postgresql/13/lib/plprofiler.so"
>> Command was: CREATE FUNCTION "public"."pl_profiler_enable"("enabled"
>> boolean) RETURNS boolean
>> LANGUAGE "c"
>> AS '$libdir/plprofiler', 'pl_profiler_enable';
>> Shashidhar
>>
>

Does it equal to execute "CREATE ACCESS METHOD"?

[jack...@gmail.com]

Sorry, I open another mail list to ask this question.

When I add 
"{ oid => '6015', oid_symbol => 'SPB_AM_OID',
  descr => 'SPB index access method',
  amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
in pg_am.dat
and  add
"{ oid => '388', descr => 'spb index access method handler',
  proname => 'spbhandler', provolatile => 'v',
  prorettype => 'index_am_handler', proargtypes => 'internal',
  prosrc => 'spbhandler' }," in pg_proc.dat,

so when I use the make install && cd contrib;make install;
whether it equals to execute create access method?

by the way, I've added the spb codes in src/access/spb, so don't worry about 
the spbhandler.

And Sorry for the another mail "access method xxx does not exist", you suggest 
me add new 
Am Index in contrib, But I need to modify gist to spb, so that's not my 
require. And I need to know
add those in pg_proc.dat and pg_am.dat, if it won't create access method for 
spb, what else I need to 
do?


--



jack...@gmail.com

Re: Does it equal to execute "CREATE ACCESS METHOD"?

[jack...@gmail.com]

Sorry, I open another mail list to ask this question.

When I add 
"{ oid => '6015', oid_symbol => 'SPB_AM_OID',
  descr => 'SPB index access method',
  amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
in pg_am.dat
and  add
"{ oid => '388', descr => 'spb index access method handler',
  proname => 'spbhandler', provolatile => 'v',
  prorettype => 'index_am_handler', proargtypes => 'internal',
  prosrc => 'spbhandler' }," in pg_proc.dat,

so when I use the make install && cd contrib;make install;
whether it equals to execute create access method?

by the way, I've added the spb codes in src/access/spb, so don't worry about 
the spbhandler.

And Sorry for the another mail "access method xxx does not exist", you suggest 
me add new 
Am Index in contrib, But I need to modify gist to spb, so that's not my 
require. And I need to know
add those in pg_proc.dat and pg_am.dat, if it won't create access method for 
spb, what else I need to 
do?


--



jack...@gmail.com

Re: Does it equal to execute "CREATE ACCESS METHOD"?

[Tom Lane]

"jack...@gmail.com"  writes:
> When I add 
> "{ oid => '6015', oid_symbol => 'SPB_AM_OID',
>   descr => 'SPB index access method',
>   amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
> in pg_am.dat
> and  add
> "{ oid => '388', descr => 'spb index access method handler',
>   proname => 'spbhandler', provolatile => 'v',
>   prorettype => 'index_am_handler', proargtypes => 'internal',
>   prosrc => 'spbhandler' }," in pg_proc.dat,
> so when I use the make install && cd contrib;make install;
> whether it equals to execute create access method?

Did you run initdb afterwards?  What you describe here should
result in an updated postgres.bki file, but that isn't the
same as catalog entries in a live database.

> And Sorry for the another mail "access method xxx does not exist", you 
> suggest me add new 
> Am Index in contrib, But I need to modify gist to spb, so that's not my 
> require. And I need to know
> add those in pg_proc.dat and pg_am.dat, if it won't create access method for 
> spb, what else I need to 
> do?

To be very blunt, it doesn't sound to me that your skills with
Postgres are anywhere near up to the task of writing a new
index access method.  You should start with some less-ambitious
project to gain some familiarity with the code base.

regards, tom lane

Re: Re: Does it equal to execute "CREATE ACCESS METHOD"?

[jack...@gmail.com]

"jack...@gmail.com"  writes:
> When I add 
> "{ oid => '6015', oid_symbol => 'SPB_AM_OID',
>   descr => 'SPB index access method',
>   amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"
> in pg_am.dat
> and  add
> "{ oid => '388', descr => 'spb index access method handler',
>   proname => 'spbhandler', provolatile => 'v',
>   prorettype => 'index_am_handler', proargtypes => 'internal',
>   prosrc => 'spbhandler' }," in pg_proc.dat,
> so when I use the make install && cd contrib;make install;
> whether it equals to execute create access method?

Did you run initdb afterwards?  What you describe here should
result in an updated postgres.bki file, but that isn't the
same as catalog entries in a live database.

> And Sorry for the another mail "access method xxx does not exist", you 
> suggest me add new 
> Am Index in contrib, But I need to modify gist to spb, so that's not my 
> require. And I need to know
> add those in pg_proc.dat and pg_am.dat, if it won't create access method for 
> spb, what else I need to 
> do?

To be very blunt, it doesn't sound to me that your skills with
Postgres are anywhere near up to the task of writing a new
index access method.  You should start with some less-ambitious
project to gain some familiarity with the code base.

regards, tom lane

Re: Re: Does it equal to execute "CREATE ACCESS METHOD"?

[jack...@gmail.com]

thanks for your advice, I realize my problems, can you give me some materials 
like some study routine for pg-internal?






--



jack...@gmail.com



>"jack...@gmail.com"  writes:



>> When I add 



>> "{ oid => '6015', oid_symbol => 'SPB_AM_OID',



>>   descr => 'SPB index access method',



>>   amname => 'spb', amhandler => 'spbhandler', amtype => 'i' },"



>> in pg_am.dat



>> and  add



>> "{ oid => '388', descr => 'spb index access method handler',



>>   proname => 'spbhandler', provolatile => 'v',



>>   prorettype => 'index_am_handler', proargtypes => 'internal',



>>   prosrc => 'spbhandler' }," in pg_proc.dat,



>> so when I use the make install && cd contrib;make install;



>> whether it equals to execute create access method?



>



>Did you run initdb afterwards?  What you describe here should



>result in an updated postgres.bki file, but that isn't the



>same as catalog entries in a live database.



>



>> And Sorry for the another mail "access method xxx does not exist", you 
>> suggest me add new 



>> Am Index in contrib, But I need to modify gist to spb, so that's not my 
>> require. And I need to know



>> add those in pg_proc.dat and pg_am.dat, if it won't create access method for 
>> spb, what else I need to 



>> do?



>



>To be very blunt, it doesn't sound to me that your skills with



>Postgres are anywhere near up to the task of writing a new



>index access method.  You should start with some less-ambitious



>project to gain some familiarity with the code base.



>



>   regards, tom lane

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

[Adrian Klaver]

On 10/28/22 17:23, Bryn Llewellyn wrote:
Adrian, thank you for your reply to my « Seeking the correct term of art 
for the (unique) role that is usually called "postgres"... » thread here:




It got me in without error. (And, as hoped for, there was no password 
challenge.) But "select current_role" showed that the mapping had been 
ignored and that I was connected again as "bob".


*What am I doing wrong?*


You skipped over this part of my post and the documentation:

Section 21.2

"The map-name is an arbitrary name that will be used to refer to this 
mapping in pg_hba.conf."


This example below id for the ident auth method but the same syntax 
applies to peer.



https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

# TYPE  DATABASEUSERADDRESS METHOD
hostall all 192.168.0.0/16  ident 
map=omicron



pg_ident.conf and pg_hba.conf are two separate files and the only way 
information gets from the former to the latter is if you explicitly 
include the map name under METHOD for the the auth line.


--
Adrian Klaver
adrian.kla...@aklaver.com

How to add a variable to a timestamp.

[Eagna]

Hi,

I'm trying to do something like this.

SELECT
d.i,
h.i,

'2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY'
FROM
GENERATE_SERIES(0, 6) AS d(i),
GENERATE_SERIES(0, 23) AS h(i);

where I add d.i days (and also h.i hours) to a timestamp.

I can't seem to get this to work. Any ideas appreciated.

E.

Sent with [Proton Mail](https://proton.me/) secure email.

Re: How to add a variable to a timestamp.

[Erik Wienhold]

> On 29/10/2022 19:35 CEST Eagna  wrote:
>
> I'm trying to do something like this.
>
> SELECT
> d.i,
> h.i,
>
> '2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY'
> FROM
> GENERATE_SERIES(0, 6) AS d(i),
> GENERATE_SERIES(0, 23) AS h(i);
>
> where I add d.i days (and also h.i hours) to a timestamp.
>
> I can't seem to get this to work. Any ideas appreciated.

Create the interval with make_interval(days => d.i, hours => h.i).

https://www.postgresql.org/docs/15/functions-datetime.html#id-1.5.8.15.6.2.2.28.1.1.1

--
Erik

Re: How to add a variable to a timestamp.

[Adrian Klaver]

On 10/29/22 10:35, Eagna wrote:


Hi,

I'm trying to do something like this.

SELECT
   d.i,
   h.i,

  '2022-10-31 00:00:00'::TIMESTAMP  + INTERVAL 'd.i DAY'
FROM
   GENERATE_SERIES(0,  6) AS d(i),
   GENERATE_SERIES(0, 23) AS h(i);

where I add d.i days (and also h.i hours) to a timestamp.

I can't seem to get this to work. Any ideas appreciated.


SELECT
  d.i,
  h.i,
 '2022-10-31 00:00:00'::TIMESTAMP  + (d.i::text || ' DAY ' || h.i::text 
|| ' HOUR')::interval

FROM
  GENERATE_SERIES(0,  6) AS d(i),
  GENERATE_SERIES(0, 23) AS h(i);




E.

Sent with Proton Mail  secure email.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to add a variable to a timestamp.

[Tom Lane]

Adrian Klaver  writes:
> On 10/29/22 10:35, Eagna wrote:
>> I'm trying to do something like this.
>>   '2022-10-31 00:00:00'::TIMESTAMP + INTERVAL 'd.i DAY'

That will not work.  A literal is a literal, you can't expect that
the system will interpret parts of it as variable references.

>   '2022-10-31 00:00:00'::TIMESTAMP  + (d.i::text || ' DAY ' || h.i::text 
> || ' HOUR')::interval

That'll work, but my what a kluge.  More recommendable is

'2022-10-31 00:00:00'::TIMESTAMP + d.i * '1 day'::interval
+ h.i * '1 hour'::interval

(Or you can spell the constants like INTERVAL '1 day',
if you prefer.)

regards, tom lane

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

[Bryn Llewellyn]

> adrian.kla...@aklaver.com  wrote:
> 
>> b...@yugabyte.com  wrote:
>> 
>> ...What am I doing wrong?
> 
> You skipped over this part of my post and the documentation (Section 21.2):
> https://www.postgresql.org/docs/current/auth-pg-hba-conf.html 
> 
> 
> "The map-name is an arbitrary name that will be used to refer to this mapping 
> in pg_hba.conf."
> 
> This example below is for the ident auth method but the same syntax applies 
> to peer.
> 
> # TYPE  DATABASEUSERADDRESS METHOD
> hostall all 192.168.0.0/16  ident 
> map=omicron
> 
> pg_ident.conf and pg_hba.conf are two separate files and the only way 
> information gets from the former to the latter is if you explicitly include 
> the map name under METHOD for the the auth line.

Yes, Adrian, I see that I did slip up. Thanks, David, for pointing out this 
same error in your separate reply. I’m advertising my stupidity in this area 
rather effectively. My problem stems from the fact that the goal statement that 
my inner voice expresses seems so simple to state. This is what I want:

1. I want to do this at the O/S prompt on the machine where my PG cluster has 
been started: "su mary".

2. Then I want to start a session (I use "psql" here an an example) like this: 
"psql -d postgres".

3. Then, at the "psql" prompt, I want "select session_user" to show "bob".

It would have seemed to me, knowing just that the goal is achievable, that I 
could express this declaratively in one place—without needing to name the 
mapping between the system user's name and the cluster role's name, thus:

authentication type:local
authentication method:  peer
system user:mary
cluster role:   bob

I know that it isn't like this. But I have no intuition for why it could not be 
like this—and so it's easy for me to get muddled.

For the purpose of the tests that follow, I set up the O/S users "bob" and 
"mary" so that "id bob mary postgres" shows this:

id=1002(bob)   gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)
uid=1001(postgres) gid=1001(postgres) 
groups=1001(postgres),27(sudo),114(ssl-cert)

And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:

bob   || {}
mary  || {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I started with this in "pg_hba.conf":

...
# TYPE  DATABASEUSERMETHOD  [auth-options]
#   --  --  --  
  local all bob peer
  local all marypeer
...

together with en empty "pg_ident.conf". So far, after either "su bob" or "su 
mary", i was able to confirm that the bare "psql -d postgres" worked fine and 
that then "select session_user" showed, respectively, "bob" or "mary", too.

Then I changed  "pg_hba.conf" to add a mapping for "mary" thus:

# TYPE  DATABASEUSERMETHOD  [auth-options]
#   --  --  --  
  local all bob peer
  local all marypeermap=bllewell

But I left "pg_ident.conf" deliberately empty. I expected, now, that "psql -d 
postgres" would still work fine for "bob" but that if would fail for "mary". 
With this deliberate error in place, I found that after "su bob", the bare 
"psql -d postgres" worked fine. But after "su mary", the same command caused 
this error:

Peer authentication failed for user "mary"

I assume that the phrase « user "mary" » means the O/S user "mary".

It seems to me that the message « no entry for the mapping "bllewell" in 
"pg_ident.conf" » would be more helpful. But maybe that would need psychic 
powers.

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

# MAPNAMESYSTEM-USERNAME   PG-USERNAME
# ------   ---
  bllewell   mary  mary

So, "bob" is the simple case. And "mary" is one step harder. Now, the 
unqualified "psql -d postgres" works again for "mary" (and it still works, of 
course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in "pg_ident.conf" 
thus:

# MAPNAMESYSTEM-USERNAME   PG-USERNAME
# ------   ---
  bllewell   mary  bob

As I'd expect, O/S "bob" still works fine and ends up as cluster-role "bob". 
But now, the attempt by O/S "mary" to connect using "psql -d postgres" fails, 
as it had ealier, with what boils sown to "computer says No":

Peer authentication failed for user "mary"

I still don't have a mental model that can explain this. As I reason it, the 
name "mary" is

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

[David G. Johnston]

On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn  wrote:This
is what I want:

>
> 1. I want to do this at the O/S prompt on the machine where my PG cluster
> has been started: *"su mary"*.
>
> 2. Then I want to start a session (I use "psql" here an an example) like
> this: *"psql -d postgres"*.
>
> 3. Then, at the "psql" prompt, I want *"select session_user"* to show
> "bob".
>

I seriously do not understand why in the world you want an authentication
system where you tell the server "my user name is mary" and expect the
server to then say "ok, but I'm going to log you in as bob just for the fun
of it".  The way I see it, when I tell the server "my user name is mary",
upon successful completion of the login I am logged in as, wait for
it., mary.


> *Peer authentication failed for user "mary"*
>
> I assume that the phrase « user "mary" » means the O/S user "mary".
>

No, the server is never going to give you an error message with your o/s
user name showing.  It is going to tell you "no, you may not login as mary,
because your name is bob and I have not been informed that you, bob, are
allowed to login as the user mary in this cluster".


> Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:
>
> *# MAPNAMESYSTEM-USERNAME   PG-USERNAME*
>
>
> *# ------   ---  bllewell   mary
>   mary*
>

As has been said numerous times, it is utterly pointless to define a
mapping like this - you get mary-is-mary for free just by saying peer.


> So, "bob" is the simple case. And "mary" is one step harder. Now, the
> unqualified "psql -d postgres" works again for "mary" (and it still works,
> of course, for "bob").
>
> So far, so good. Now for the test, I mapped "mary" to "bob" in
> "pg_ident.conf" thus:
>
>
>
>
> *# MAPNAMESYSTEM-USERNAME   PG-USERNAME# ---
>   ---   ---  bllewell   mary  bob*
>

Now you are saying mary is allow to claim she is bob.  Which requires the
o/s user to be mary and her psql command line have "-U bob"

It is not possible to make an alias mapping work without specifying "-U" on
the psql command line.  Period.  The -U is precisely how you tell the
server you are using an alias - without it the server expects that the o/s
user is logging in using their own name as the requested login role.  In
that case either a peer entry for the user exists - and thus authentication
is successful - or it doesn't - and authentication will fail.

David J.

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

[Adrian Klaver]

On 10/29/22 20:38, David G. Johnston wrote:
On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn > wrote:This is what I want:



1. I want to do this at the O/S prompt on the machine where my PG
cluster has been started: *"su mary"*.

2. Then I want to start a session (I use "psql" here an an example)
like this: *"psql -d postgres"*.

3. Then, at the "psql" prompt, I want *"select session_user"* to
show "bob".


I seriously do not understand why in the world you want an 
authentication system where you tell the server "my user name is mary" 
and expect the server to then say "ok, but I'm going to log you in as 
bob just for the fun of it".  The way I see it, when I tell the server 
"my user name is mary", upon successful completion of the login I am 
logged in as, wait for it., mary.


*Peer authentication failed for user "mary"*

I assume that the phrase « user "mary" » means the O/S user "mary".


No, the server is never going to give you an error message with your o/s 
user name showing.  It is going to tell you "no, you may not login as 
mary, because your name is bob and I have not been informed that you, 
bob, are allowed to login as the user mary in this cluster".


Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME*
*# ---    ---   ---
   bllewell   mary              mary
*


As has been said numerous times, it is utterly pointless to define a 
mapping like this - you get mary-is-mary for free just by saying peer.


+1


**
So, "bob" is the simple case. And "mary" is one step harder. Now,
the unqualified "psql -d postgres" works again for "mary" (and it
still works, of course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in
"pg_ident.conf" thus:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME
# ---    ---   ---
   bllewell   mary              bob
*


Now you are saying mary is allow to claim she is bob.  Which requires 
the o/s user to be mary and her psql command line have "-U bob"


It is not possible to make an alias mapping work without specifying "-U" 
on the psql command line.  Period.  The -U is precisely how you tell the 
server you are using an alias - without it the server expects that the 
o/s user is logging in using their own name as the requested login 
role.  In that case either a peer entry for the user exists - and thus 
authentication is successful - or it doesn't - and authentication will fail.


+1



David J.



--
Adrian Klaver
adrian.kla...@aklaver.com