Re: Duplicate key error

[Ron]

On 3/4/21 12:14 AM, Andrus wrote:

Hi!
says something else is inserting/updating using that key value. So 
obviously your script is not catching all the conflicts.


> At this point your best bet is to monitor the Postgres log and see what 
else is happening at the time of the error. I'm guessing you will find 
another process working on that table.


It looks like other process has added same row during trancaction.

How to change script so that it works starting at Postgres 9.0 and does 
not cause error in this case ?




Having all the processes which try to update that table explicitly set a 
serializable transaction would probably eliminate the duplicate key error.  
There's no free lunch, though: you'd have to handle the blocking.


--
Angular momentum makes the world go 'round.

Re: Duplicate key error

[Andrus]

Hi!
says something else is inserting/updating using that key value. So 
obviously your script is not catching all the conflicts.


> At this point your best bet is to monitor the Postgres log and see 
what else is happening at the time of the error. I'm guessing you will 
find another process working on that table.


It looks like other process has added same row during trancaction.

How to change script so that it works starting at Postgres 9.0 and does 
not cause error in this case ?


Andrus.

Re: Code of Conduct: Russian Translation for Review

[Stacey Haysler]

All of the comments are forwarded to the original translation team for review.

We really appreciate how many people are offering ideas for this translation!

Regards,
Stacey

Stacey Haysler
Chair
PostgreSQL Community Code of Conduct Committee

On Mar 3, 2021, at 2:09 PM, Valeria Kaplan  wrote:

Hi Alexander,
Attached. Overall I agree with most of your comments. 
Perhaps, whoever did the initial translation could take a look and finalise it. 
It's quite hard to review it in this format with so many comments.

Thanks,
Valeria


On Wed, Mar 3, 2021 at 7:22 PM Alexander Lakhin mailto:exclus...@gmail.com>> wrote:
Hello Valeria,

03.03.2021 20:23, Valeria Kaplan wrote:
>
> attached are my comments (I used Alexander's file for ease of review).
>
Thanks for your comments!
Please look at my responses.
I hope we'll finalize the translation soon.

Best regards,
Alexander

Re: Code of Conduct: Russian Translation for Review

[Stacey Haysler]

Hi, Boris - A redline or similar mark up in a standard document format (.odt, 
.pages, .doc, etc.) is the most common method. As long as it is readable by a 
human and the edits are easy to see, we can probably work with it.  Thank you!

Regards,
Stacey

Stacey Haysler
Chair
PostgreSQL Community Code of Conduct Committee

On Mar 3, 2021, at 11:40 AM, Boris Epstein  wrote:

Hello Stacey,

I took a quick look and it looks OK overall. I could go over it in more detail 
- but before I do, is there a code control procedure I need to follow to offer 
edits, etc?

Thanks.

Regards,

Boris.


On Fri, Feb 26, 2021 at 7:51 PM Stacey Haysler  wrote:
The PostgreSQL Community Code of Conduct Committee has received a draft of the 
Russian translation of the Code of Conduct Policy updated August 18, 2020 for 
review.

The English version of the Policy is at:
https://www.postgresql.org/about/policies/coc/ 


The translation was created by:
Anastasia Raspopina

The translation was reviewed by:
Anastasia Lubennikova (Please note: she is a member of the CoC Committee)
Alexander Lakhin

The proposed translation is attached to this message in various formats.

If you have any comments or suggestions for the translation, please bring them 
to our attention no later than 5:00 PM PST on  Friday, March 5, 2021.

Thank you.

Regards,
Stacey

Stacey Haysler
Chair
PostgreSQL Community Code of Conduct Committee

Re: Schema in trigger in logical replication

[Adrian Klaver]

On 3/3/21 2:35 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:

Using postgres 12.5 in DBA schema, this trigger is executed when the table
is updated through a logical replication. Why is it necessary to name the
schema for it to work?


Because the search_path does include the schema?


When I update the table manually, if it Works.

Example.

This trigger function does not work

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
 RETURNS trigger
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO pos_sync_eco_rx
  ( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
  VALUES (  new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
   fecha_y_hora_rx,
   dato,
   usuario,
   fecha_y_hora_proceso ) =
  (new.fecha_y_hora_tx,
   localtimestamp,
   new.dato,
   new.usuario,
   new.fecha_y_hora_proceso );

return  new;

end
$BODY$;

This trigger function, if it works

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
 RETURNS trigger
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO dba.pos_sync_eco_rx
  ( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
  VALUES (  new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
   fecha_y_hora_rx,
   dato,
   usuario,
   fecha_y_hora_proceso ) =
  (new.fecha_y_hora_tx,
   localtimestamp,
   new.dato,
   new.usuario,
   new.fecha_y_hora_proceso );

return  new;

end
$BODY$;





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

Schema in trigger in logical replication

[Fontana Daniel C. (Desartec S.R.L.)]

Using postgres 12.5 in DBA schema, this trigger is executed when the table
is updated through a logical replication. Why is it necessary to name the
schema for it to work?
When I update the table manually, if it Works.

Example.

This trigger function does not work

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO pos_sync_eco_rx
  ( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
  VALUES (  new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
   fecha_y_hora_rx,
   dato,
   usuario,
   fecha_y_hora_proceso ) =
  (new.fecha_y_hora_tx,
   localtimestamp,
   new.dato,
   new.usuario,
   new.fecha_y_hora_proceso );

return  new;

end
$BODY$;

This trigger function, if it works

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO dba.pos_sync_eco_rx
  ( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
  VALUES (  new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
   fecha_y_hora_rx,
   dato,
   usuario,
   fecha_y_hora_proceso ) =
  (new.fecha_y_hora_tx,
   localtimestamp,
   new.dato,
   new.usuario,
   new.fecha_y_hora_proceso );

return  new;

end
$BODY$;


--
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Code of Conduct: Russian Translation for Review

[Valeria Kaplan]

Hi Alexander,
Attached. Overall I agree with most of your comments.
Perhaps, whoever did the initial translation could take a look and finalise
it. It's quite hard to review it in this format with so many comments.

Thanks,
Valeria


On Wed, Mar 3, 2021 at 7:22 PM Alexander Lakhin  wrote:

> Hello Valeria,
>
> 03.03.2021 20:23, Valeria Kaplan wrote:
> >
> > attached are my comments (I used Alexander's file for ease of review).
> >
> Thanks for your comments!
> Please look at my responses.
> I hope we'll finalize the translation soon.
>
> Best regards,
> Alexander
>


PostgreSQL Code of Conduct - Russian Translation Feb 26 2021_VK2 .docx
Description: MS-Word 2007 document

Re: Duplicate key error

[Adrian Klaver]

On 3/3/21 11:59 AM, Andrus wrote:

Hi!


And if that name already exists there would be a duplicate key error.


Name cannot exist:  First delete deletes its value. Tables are not 
changed by external process during transaction. As double insurance, 
insert perfoms additional existence check and adds only if key does not 
exist.


The error message:

"
duplicate key value violates unique constraint "session_pkey"
Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.
"

says something else is inserting/updating using that key value. So 
obviously your script is not catching all the conflicts. At this point 
your best bet is to monitor the Postgres log and see what else is 
happening at the time of the error. I'm guessing you will find another 
process working on that table.




 >Last inserted row data should remain.


I'm not understanding the above.


This table should contain last login time, user and ip address. It 
should be updated by every process on login. For this old entry is 
removed if it exists and new entry with same primary key is added.


Andrus.





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

Re: Duplicate key error

[Andrus]

Hi!

>It sounds like this should be re-written as 'insert on conflict do 
update' statement.


It should work in Postgres 9 also. on confilct is not available in 
postgres 9.


This code does not insert duplicate key valuse ? Why it throws error ?

You can create testcase to run this code from multiple processes to try 
reproduce the error.


Andrus.

Re: Duplicate key error

[Andrus]

Hi!


And if that name already exists there would be a duplicate key error.


Name cannot exist:  First delete deletes its value. Tables are not 
changed by external process during transaction. As double insurance, 
insert perfoms additional existence check and adds only if key does not 
exist.


>Last inserted row data should remain.


I'm not understanding the above.


This table should contain last login time, user and ip address. It 
should be updated by every process on login. For this old entry is 
removed if it exists and new entry with same primary key is added.


Andrus.

Re: Code of Conduct: Russian Translation for Review

[Boris Epstein]

Hello Stacey,

I took a quick look and it looks OK overall. I could go over it in more
detail - but before I do, is there a code control procedure I need to
follow to offer edits, etc?

Thanks.

Regards,

Boris.


On Fri, Feb 26, 2021 at 7:51 PM Stacey Haysler  wrote:

> The PostgreSQL Community Code of Conduct Committee has received a draft of
> the Russian translation of the Code of Conduct Policy updated August 18,
> 2020 for review.
>
> The English version of the Policy is at:
> https://www.postgresql.org/about/policies/coc/
>
> The translation was created by:
> Anastasia Raspopina
>
> The translation was reviewed by:
> Anastasia Lubennikova (Please note: she is a member of the CoC Committee)
> Alexander Lakhin
>
> The proposed translation is attached to this message in various formats.
>
> If you have any comments or suggestions for the translation, please bring
> them to our attention no later than 5:00 PM PST on  Friday, March 5, 2021.
>
> Thank you.
>
> Regards,
> Stacey
>
> Stacey Haysler
> Chair
> PostgreSQL Community Code of Conduct Committee
>
>
>
>
>
>
>

Re: Code of Conduct: Russian Translation for Review

[Alexander Lakhin]

Hello Valeria,

03.03.2021 20:23, Valeria Kaplan wrote:
>
> attached are my comments (I used Alexander's file for ease of review).
>
Thanks for your comments!
Please look at my responses.
I hope we'll finalize the translation soon.

Best regards,
Alexander


PostgreSQL Code of Conduct - Russian Translation Feb 26 2021_VK+.docx
Description: MS-Word 2007 document

Re: Duplicate key error

[Michael Lewis]

It sounds like this should be re-written as 'insert on conflict do update'
statement.

Re: Code of Conduct: Russian Translation for Review

[Valeria Kaplan]

Hi All,

attached are my comments (I used Alexander's file for ease of review).

thank you,
Valeria

On Mon, Mar 1, 2021 at 2:26 PM Alexander Lakhin  wrote:

> Hello,
>
> 27.02.2021 03:51, Stacey Haysler wrote:
>
> The PostgreSQL Community Code of Conduct Committee has received a draft of
> the Russian translation of the Code of Conduct Policy updated August 18,
> 2020 for review.
>
> The English version of the Policy is at:
> https://www.postgresql.org/about/policies/coc/
>
> The translation was created by:
> Anastasia Raspopina
>
> The translation was reviewed by:
> Anastasia Lubennikova (Please note: she is a member of the CoC Committee)
> Alexander Lakhin
>
> Please look at my additional comments.
>
> Best regards,
> Alexander
>


PostgreSQL Code of Conduct - Russian Translation Feb 26 2021_VK.docx
Description: MS-Word 2007 document

Re: permission denied for pg_temp_XX when vacuuming

[Tom Lane]

Tobias Lott  writes:
> There were no crashes of the instance, but some issues with the connected
> application, resulting in 'could not receive data from client: Connection
> reset by peer' and 'unexpected EOF on client connection with an open
> transaction'.

No, that sort of thing would still allow the backend to shut down cleanly.

> So if this might have left behind temp tables causing the errors, should I
> try to delete these pg_temp tables?

It would definitely be worth looking around to see if you have any temp
tables that shouldn't be there.  I think you can just do
\dt pg_temp_*.*
in psql (as superuser) to see what's out there.  Figuring out which ones
are live sessions' temp tables might be harder, though.

regards, tom lane

Re: Duplicate key error

[Adrian Klaver]

On 3/3/21 8:08 AM, Andrus wrote:

Hi!


There is no other process inserting  to this table?

There may be other processes in this server trying to insert same 
primary key value (server name).


And if that name already exists there would be a duplicate key error.



Last inserted row data should remain.


I'm not understanding the above.



Andrus.





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

Re: permission denied for pg_temp_XX when vacuuming

[Tom Lane]

Tobias Lott  writes:
> Yes that's strange. A lot of pg_XX tables are skipped, but some of these
> pg_temp schemas cause errors.
> Could it be connected to a migration of the database (from an instance
> running PostgreSQL 9.6 to an instance running PostgreSQL 12) done a few
> weeks ago?

I wouldn't have expected a migration to bring any temp tables forward.

Have you had any crashes on this instance (post-migration)?  A possible
theory is that crashed backend(s) left behind some temp tables, and then
if vacuumdb's backend process re-uses the backend ID (which determines
the NN in pg_temp_NN) of one of those sessions, it would think those
temp tables are its own and try to vacuum them.  Or at least I think
it might.  That still fails to explain the permissions errors in any
detail, but at least it offers a reason why vacuumdb would even be
going anywhere near a temp table.

regards, tom lane

Re: Duplicate key error

[Andrus]

Hi!


There is no other process inserting  to this table?

There may be other processes in this server trying to insert same 
primary key value (server name).


Last inserted row data should remain.

Andrus.

Re: Duplicate key error

[Adrian Klaver]

On 3/3/21 7:23 AM, Andrus wrote:

Hi!

Sometimes duplicate key error

     duplicate key value violates unique constraint "session_pkey"
     Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.

occurs in script:

     delete from session where workplace='WIN-N9BSKUNKBC8' ;
     INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
     SELECT 'WIN-N9BSKUNKBC8' , 
inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER 
WHERE NOT EXISTS (SELECT 1 FROM session WHERE workplace='WIN-N9BSKUNKBC8' )


Sript is running form windows task scheduler on every 10 minutes. Error 
occurs only sometimes.


There is no other process inserting  to this table?




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

Duplicate key error

[Andrus]

Hi!

Sometimes duplicate key error

    duplicate key value violates unique constraint "session_pkey"
    Key (workplace)=(WIN-N9BSKUNKBC8 ) already exists.

occurs in script:

    delete from session where workplace='WIN-N9BSKUNKBC8' ;
    INSERT INTO session (workplace,ipaddress,logintime,loggeduser)
    SELECT 'WIN-N9BSKUNKBC8' , 
inet_client_addr()::CHAR(14),current_timestamp::CHAR(28),CURRENT_USER 
WHERE NOT EXISTS (SELECT 1 FROM session WHERE workplace='WIN-N9BSKUNKBC8' )


Sript is running form windows task scheduler on every 10 minutes. Error 
occurs only sometimes.


How to fix this ?
Table is defined as

    CREATE TABLE public.session
    (
    loggeduser character(10) ,
    workplace character(16) NOT NULL,
    ipaddress character(20) ,
    logintime character(28) ,
    activity timestamp with time zone,
    CONSTRAINT session_pkey PRIMARY KEY (workplace)
    )


Environment:

    PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
    Windows server 2019
    psqlODBC driver 13.00.


I already asked this in this list many years ago. In this case Tom 
replies that is looks like index race condition bug which was fixed in 
later Postgres 9 version.


However this issue still exists in Postgres 13.1

Andrus.

Re: permission denied for pg_temp_XX when vacuuming

[Tom Lane]

Laurenz Albe  writes:
> On Wed, 2021-03-03 at 09:58 +0100, Tobias Lott wrote:
>> I have a database in a PostgreSQL 12 instance.
>> When running vacuumdb -z on the database with the user set as owner of the 
>> DB, I sometimes, not always, get errors such as:
>> error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" failed: 
>> ERROR: permission denied for schema pg_temp_93
>> The specific number of the pg_temp schema (pg_temp_XX) is always changing. 
>> What could be the reason for this error?

> Those are schemas for temporary tables, which you cannot VACUUM outside the
> session that created them, so you can safely ignore these errors.

Indeed, but I'm wondering why vacuumdb isn't skipping them
automatically.  Something fishy seems to be happening there.

regards, tom lane

Re: problems with my community account on www.postgresql.org

[Magnus Hagander]

On Wed, Mar 3, 2021 at 12:23 PM Mathias Zarick
 wrote:
>
> Hi there,
>
>
>
> I created a community account some weeks ago, mainly to be able to subscribe 
> to this email lists.
>
> Now I want to edit my subscriptions but I am unable to login. I chose Twitter 
> to be used for authentication.
>
> When I do this again, and click on “Sign in with Twitter” I get into an 
> infinite loop which goes to
>
> https://api.twitter.com/oauth/authorize  where I can click “Authorize app” 
> but I land on api.twitter.com again and so on.
>
> However, when I try to reset my password for my account using the password 
> reset function on
>
> https://www.postgresql.org/account/reset/ I get “Account error”,
>
> “This account cannot change password as it's connected to a third party login 
> site.”
>
>
>
> Can anybody from the mailing list admins help me out here?

Hi!

You seem to have hit a bug in our oauth1 processing (twitter is the
only provider we use oauth1 for, the others use oauth2). I believe
it's been fixed now, please try again.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: permission denied for pg_temp_XX when vacuuming

[Tobias Lott]

Hi Laurenz,

good to know, thank you very much!

Best regards,
Tobias

On Wed, 3 Mar 2021 at 12:10, Laurenz Albe  wrote:

> On Wed, 2021-03-03 at 09:58 +0100, Tobias Lott wrote:
> > I have a database in a PostgreSQL 12 instance.
> > When running vacuumdb -z on the database with the user set as owner of
> the DB, I sometimes, not always, get errors such as:
> > error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050"
> failed: ERROR: permission denied for schema pg_temp_93
> > The specific number of the pg_temp schema (pg_temp_XX) is always
> changing. What could be the reason for this error?
>
> Those are schemas for temporary tables, which you cannot VACUUM outside the
> session that created them, so you can safely ignore these errors.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

-- 
Tobias Lott
Technical Consultant
Region South West
+49 151 23649035 <+49+151+23649035>
tobias.l...@devoteam.com
[image: Banner EVP Intelligence]
[image: Devoteam at Linkedin] 
[image: Devoteam at Youtube]

[image: Devoteam at Twitter] 

problems with my community account on www.postgresql.org

[Mathias Zarick]

Hi there,

I created a community account some weeks ago, mainly to be able to subscribe to 
this email lists.
Now I want to edit my subscriptions but I am unable to login. I chose Twitter 
to be used for authentication.
When I do this again, and click on "Sign in with Twitter" I get into an 
infinite loop which goes to
https://api.twitter.com/oauth/authorize  where I can click "Authorize app" but 
I land on api.twitter.com again and so on.
However, when I try to reset my password for my account using the password 
reset function on
https://www.postgresql.org/account/reset/ I get "Account error",
"This account cannot change password as it's connected to a third party login 
site."

Can anybody from the mailing list admins help me out here?
Thanks in advance
Mathias Zarick

Re: permission denied for pg_temp_XX when vacuuming

[Laurenz Albe]

On Wed, 2021-03-03 at 09:58 +0100, Tobias Lott wrote:
> I have a database in a PostgreSQL 12 instance.
> When running vacuumdb -z on the database with the user set as owner of the 
> DB, I sometimes, not always, get errors such as:
> error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" failed: 
> ERROR: permission denied for schema pg_temp_93
> The specific number of the pg_temp schema (pg_temp_XX) is always changing. 
> What could be the reason for this error?

Those are schemas for temporary tables, which you cannot VACUUM outside the
session that created them, so you can safely ignore these errors.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: self-made certs not quite right

[Tiemen Ruiten]

Hello Rob,

With root.crt in ~/.postgresql, testing the jdbc connection from a
> tomcat server generates this failure (again either localhost or
> $(hostname))
> Blow out on db connection to jdbc:postgresql://localhost:5432/postgres;
> SSL error: PKIX path building failed:
> sun.security.provider.certpath.SunCertPathBuilderException: unable to
> find valid certification path to requested target
> org.postgresql.util.PSQLException: SSL error: PKIX path building failed:
> sun.security.provider.certpath.SunCertPathBuilderException: unable to
> find valid certification path to requested target
>

Java has its own certificate keystore, you would need to add your
certificate to it: https://tomcat.apache.org/tomcat-8.5-doc/ssl-howto.html

Hope this helps.

-- 
Tiemen Ruiten
Infrastructure Engineer

Re: Locks in creating a partition in CREATE TABLE vs ALTER TABLE

[Asaf Flescher]

Right, I see I was unclear - I did know it was a new feature in Postgres
12, I just thought it applied to both cases, since the documentation makes
no such distinction. Wanted to make sure I wasn't missing something basic
before using the ALTER TABLE thing as a workaround.

Thanks!

On Tue, Mar 2, 2021 at 5:29 PM Alvaro Herrera 
wrote:

> On 2021-Mar-02, Asaf Flescher wrote:
>
> > I'm not sure if this is a bug or I'm missing something regarding how
> > partitioning is supposed to work but I've noticed (in Postgres 12.6) that
> > if I have a partitioned table, and then try to add a partition to it via
> > CREATE TABLE ... PARTITION OF, the statement will grab an AccessExclusive
> > lock on the partitioned table. Meanwhile, if I create that same table
> > normally, then attach it to the partitioned table via ALTER table - no
> > AccessExclusive lock.
>
> It's a new feature in Postgres 12 actually -- we went great lengths to
> be able to do ALTER TABLE .. ATTACH PARTITION without a full
> AccessExclusive lock.  However, it's just not possible to do the same
> for CREATE TABLE .. PARTITION AS.
>
> If you try the same in Postgres 11, you'll notice that both use an
> AccessExclusive lock.
>
> --
> Álvaro Herrera   Valdivia, Chile
> Syntax error: function hell() needs an argument.
> Please choose what hell you want to involve.
>

permission denied for pg_temp_XX when vacuuming

[Tobias Lott]

Hi,

I have a database in a PostgreSQL 12 instance.
When running vacuumdb -z on the database with the user set as owner of the
DB, I sometimes, not always, get errors such as:
error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" failed:
ERROR: permission denied for schema pg_temp_93
The specific number of the pg_temp schema (pg_temp_XX) is always changing.
What could be the reason for this error?

Thanks and best regards,
Tobias

Re: Localizing stored functions by replacing placeholders in their body

[Alexander Farber]

Thanks for your input

Actually, yes, that is what I have right now a translate() like stored
function, with format %s sometimes.

But that is "at runtime" and I would like to have a "at compile
time"/"deploy once and forget" solution, that is why I have asked about
approaches for modifying the bodys of my stored functions.

After some more thinking yesterday I have decided to embed the SQL files
with stored functions declarations in my servlet JAR file - that is where
the rest and most of my translations are.

I will just load them from servlet resources when the servlet start,
replace the placeholder by Java and the execute them with CREATE OR REPLACE
FUNCTION ...