Re: Getting data from a record variable dynamically

[Alban Hertroys]

> On 5 Jul 2022, at 1:02, Rhys A.D. Stewart  wrote:
> 
> Greetings All,
> 
> I have a trigger that is attached to several different tables. In the
> trigger function I retrieve a single row and I want the info from a
> specific column. This column is dependent on the table in question.
> and I have the column name stored in a variable as well. Without
> writing a conditional for each table, what is the best way to
> dynamically get the data from the record variable?

I would create a simple trigger function for each of those tables that just 
extracts the value(s) from the field(s) you mentioned, and then pass those 
values on to a generic function that does the actual table-independent work.

The usual trigger pseudo-columns and variables wouldn’t be available in that 
generic function, but considering that your triggers fire from different 
tables, you could either pass them on or they (probably) don’t make sense in 
the context of the generic function.

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

lifetime of the old CTID

[Matthias Apitz]

Hello,

We're using the SQL function currtid2() to get the new CTID of a row
when this was UPDATEd.

Investigating cases of failing updates, it turns out that the old CTID
has only a limited lifetime; one can check this with SQL:

sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
ctid|   d01gsi
+-
 (29036,11) | 0240564

now I update the row and afterwards pick up the new CTID based on the
old one (29036,11):

sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
UPDATE 1
sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
 currtid2  
---
 (29036,7)

Now I go and pick up a coffe in our kitchen and check again:

sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
  currtid2  

 (29036,11)

i.e. the function now only returns it argument. and not the new CTID
anymore.

Why is this? And what triggers exactly that the old CTID can't be used
anymore?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Laurenz Albe]

On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> We're using the SQL function currtid2() to get the new CTID of a row
> when this was UPDATEd.
> 
> Investigating cases of failing updates, it turns out that the old CTID
> has only a limited lifetime; one can check this with SQL:
> 
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
>     ctid    |   d01gsi    
> +-
>  (29036,11) | 0240564
> 
> now I update the row and afterwards pick up the new CTID based on the
> old one (29036,11):
> 
> sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> UPDATE 1
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>  currtid2  
> ---
>  (29036,7)
> 
> Now I go and pick up a coffe in our kitchen and check again:
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>   currtid2  
> 
>  (29036,11)
> 
> i.e. the function now only returns it argument. and not the new CTID
> anymore.
> 
> Why is this? And what triggers exactly that the old CTID can't be used
> anymore?

It is probably the fault of your coffee.

Another explanation could be that the HOT chain was pruned while you were away.

Yours,
Laurenz Albe

General Inquiry

[Cloete, F. (Francois)]

Good morning,
Can you please confirm if I need to contact the postgresql community fro some 
assistance can I use this e-mail address or another e-mail ?

We keep on seeing the below entries in our postgresql.log file getting written 
what seems to be every millisecond.

[cid:image001.png@01D8905C.ED5E97A0]

Regards
Francois



Nedbank disclaimer and confidentiality notice:

This email may contain information that is confidential, privileged or 
otherwise protected from disclosure. If you are not an intended recipient of 
this email or all or some of the information contained therein, do not 
duplicate or redistribute it by any means. Please delete it and any attachments 
and notify the sender that you have received it in error. Unless specifically 
indicated, this email is neither an offer or a solicitation to buy or sell any 
securities, investment products or other financial product or service, nor is 
it an official confirmation of any transaction or an official statement of 
Nedbank. Any views or opinions presented are solely those of the author and do 
not necessarily represent those of Nedbank. Nedbank Ltd Reg No 1951/09/06.

The following link displays the names of the Nedbank Board of Directors and 
Company Secretary. [http://www.nedbank.co.za/terms/DirectorsNedbank.htm]

If you do not want to click on a link, please type the relevant address in your 
browser

Re: General Inquiry

[Tim Clarke]

On 05/07/2022 09:49, Cloete, F. (Francois) wrote:
Good morning,
Can you please confirm if I need to contact the postgresql community fro some 
assistance can I use this e-mail address or another e-mail ?

We keep on seeing the below entries in our postgresql.log file getting written 
what seems to be every millisecond.

[cid:part1.48305BC8.064CE40A@minerva.info]

Regards
Francois


It appears that you have a process running on your Postgres server that is 
repeatedly trying to connect to the database. Is this running on a server or a 
workstation? Check your users, cron jobs?

Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/

Watch our latest Minerva Briefings on 
BrightTALK



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: lifetime of the old CTID

[Matthias Apitz]

El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:

> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> > 
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >     ctid    |   d01gsi    
> > +-
> >  (29036,11) | 0240564
> > 
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> > 
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > ---
> >  (29036,7)
> > 
> > Now I go and pick up a coffe in our kitchen and check again:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > 
> >  (29036,11)
> > 
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> > 
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
> 
> It is probably the fault of your coffee.

Correct, the coffee machine is broken and never does show when the
milk powder is empty already :-)

> Another explanation could be that the HOT chain was pruned while you were 
> away.

What exactly means "the HOT chain was pruned"? What is the HOT chain?

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Matthias Apitz]

El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:

> On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > We're using the SQL function currtid2() to get the new CTID of a row
> > when this was UPDATEd.
> > 
> > Investigating cases of failing updates, it turns out that the old CTID
> > has only a limited lifetime; one can check this with SQL:
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >     ctid    |   d01gsi    
> > +-
> >  (29036,11) | 0240564
> > 
> > now I update the row and afterwards pick up the new CTID based on the
> > old one (29036,11):
> > 
> > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > UPDATE 1
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > ---
> >  (29036,7)
> > 
> > Now I go and pick up a coffe in our kitchen and check again:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > 
> >  (29036,11)
> > 
> > i.e. the function now only returns it argument. and not the new CTID
> > anymore.
> > 
> > Why is this? And what triggers exactly that the old CTID can't be used
> > anymore?
> 
> It is probably the fault of your coffee.
> 
> Another explanation could be that the HOT chain was pruned while you were 
> away.

I've read now about HOT and understand that autovacuum will prune the
HOT chain. But also a simple SELECT seems to remove it:

sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
 currtid2  
---
 (29036,7)


sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
   ctid|   d01gsi
---+-
 (29036,7) | 0240564


sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
  currtid2  

 (29036,11)


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Laurenz Albe]

On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote:
> El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió:
> > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote:
> > > We're using the SQL function currtid2() to get the new CTID of a row
> > > when this was UPDATEd.
> > > 
> > > Investigating cases of failing updates, it turns out that the old CTID
> > > has only a limited lifetime; one can check this with SQL:
> > > 
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > >     ctid    |   d01gsi    
> > > +-
> > >  (29036,11) | 0240564
> > > 
> > > now I update the row and afterwards pick up the new CTID based on the
> > > old one (29036,11):
> > > 
> > > sisis=# UPDATE d01buch set d01ort='Wolfratshausen' where d01gsi='0240564';
> > > UPDATE 1
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >  currtid2  
> > > ---
> > >  (29036,7)
> > > 
> > > Now I go and pick up a coffe in our kitchen and check again:
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >   currtid2  
> > > 
> > >  (29036,11)
> > > 
> > > i.e. the function now only returns it argument. and not the new CTID
> > > anymore.
> > > 
> > > Why is this? And what triggers exactly that the old CTID can't be used
> > > anymore?
> > 
> > Another explanation could be that the HOT chain was pruned while you were 
> > away.
> 
> I've read now about HOT and understand that autovacuum will prune the
> HOT chain. But also a simple SELECT seems to remove it:
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>  currtid2  
> ---
>  (29036,7)
> 
> 
> sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
>    ctid    |   d01gsi    
> ---+-
>  (29036,7) | 0240564    
> 
> 
> sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
>   currtid2  
> 
>  (29036,11)

Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
suspected it might have been that.

Yours,
Laurenz Albe

Re: lifetime of the old CTID

[Matthias Apitz]

El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe escribió:

> > > Another explanation could be that the HOT chain was pruned while you were 
> > > away.
> > 
> > I've read now about HOT and understand that autovacuum will prune the
> > HOT chain. But also a simple SELECT seems to remove it:
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >  currtid2  
> > ---
> >  (29036,7)
> > 
> > 
> > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> >    ctid    |   d01gsi    
> > ---+-
> >  (29036,7) | 0240564    
> > 
> > 
> > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> >   currtid2  
> > 
> >  (29036,11)
> 
> Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
> suspected it might have been that.

Hi Laurenz, ist there any way to keep/freeze such tuples until the run
of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
even a code change for this? We compile on Linux from the sources and
have already some code changes in place...

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Laurenz Albe]

On Tue, 2022-07-05 at 17:52 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe 
> escribió:
> 
> > > > Another explanation could be that the HOT chain was pruned while you 
> > > > were away.
> > > 
> > > I've read now about HOT and understand that autovacuum will prune the
> > > HOT chain. But also a simple SELECT seems to remove it:
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >  currtid2  
> > > ---
> > >  (29036,7)
> > > 
> > > 
> > > sisis=# select ctid, d01gsi from d01buch where d01gsi = '0240564';
> > >    ctid    |   d01gsi    
> > > ---+-
> > >  (29036,7) | 0240564    
> > > 
> > > 
> > > sisis=# SELECT currtid2('d01buch'::text, '(29036,11)'::tid);
> > >   currtid2  
> > > 
> > >  (29036,11)
> > 
> > Right.  Heap-Only tuples can also vanish without autovacuum; that is why I
> > suspected it might have been that.
> 
> Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> even a code change for this? We compile on Linux from the sources and
> have already some code changes in place...

That should be possible, but why do you want that?  Perhaps there is a better
solution for your problem.

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

How to upgrade postgres version 8 to 13

[shashidhar Reddy]

Hello,

Could some please let me know how to upgrade postgresql version 8.2 to 13,
as the database size is 20 TB, backup and restore is not possible.

Regards,
Shashidhar

Re: How to upgrade postgres version 8 to 13

[Adrian Klaver]

On 7/5/22 9:54 AM, shashidhar Reddy wrote:

Hello,

Could some please let me know how to upgrade postgresql version 8.2 to 
13, as the database size is 20 TB, backup and restore is not possible.


This is going to need more information:

1) Are you looking to upgrade on same machine or between machines.

2) What are the hardware specs for the machine or machines?

3) If between machines:
   a) The OS and version of each.
   b) The network distance between them

4) What is the desired down time limit?



Regards,
Shashidhar



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

Re: How to upgrade postgres version 8 to 13

[Adrian Klaver]

On 7/5/22 10:48 AM, shashidhar Reddy wrote:

Reply to list also

Ccing list


Hello Adrian,

Os is Ubuntu  not sure about the version I am looking for the low 
downtime method it may be on same or different machine but same machine 
is preferable

Hardware specs?

How low is low for downtime?






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

Re: How to upgrade postgres version 8 to 13

[shashidhar Reddy]

Down time 10 to 15 hrs
Hardware specs are also not sure for now as it is client machine.

On Tue, 5 Jul, 2022, 11:22 pm Adrian Klaver, 
wrote:

> On 7/5/22 10:48 AM, shashidhar Reddy wrote:
>
> Reply to list also
>
> Ccing list
>
> > Hello Adrian,
> >
> > Os is Ubuntu  not sure about the version I am looking for the low
> > downtime method it may be on same or different machine but same machine
> > is preferable
> Hardware specs?
>
> How low is low for downtime?
>
>
>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to upgrade postgres version 8 to 13

[Adrian Klaver]

On 7/5/22 10:56 AM, shashidhar Reddy wrote:

Down time 10 to 15 hrs
Hardware specs are also not sure for now as it is client machine.


This is going to be a difficult task for the following reasons:

1) pg_upgrade(https://www.postgresql.org/docs/current/pgupgrade.html) 
only goes back to 8.4. So you will need to use other tools if you want 
to emulate it.


2) 8.2 --> 13 represent 14 years of changes. In particular the implicit 
type cast changes that happened in 8.3. You will need to testing to 
determine whether what you are doing in 8.2 is going to work in 13. Also 
why version 13?


3) The size of the data set.

In other words there is going to be a lot of preliminary work and 
testing that is going to need to be done before you even start the 
transfer. The first of that is determining what your setup is. My guess 
is you will end up doing this in stages of using intervening Postgres 
versions.




On Tue, 5 Jul, 2022, 11:22 pm Adrian Klaver, > wrote:


On 7/5/22 10:48 AM, shashidhar Reddy wrote:

Reply to list also

Ccing list

 > Hello Adrian,
 >
 > Os is Ubuntu  not sure about the version I am looking for the low
 > downtime method it may be on same or different machine but same
machine
 > is preferable
Hardware specs?

How low is low for downtime?






-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Seems to be impossible to set a NULL search_path

[Bryn Llewellyn]

The section "Writing SECURITY DEFINER Functions Safely":

https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2

explains the risk brought if a bad actor creates an object that preemps what 
the developer intended by putting it in a schema that's ahead of the intended 
object in the search_path.

You can avoid this risk by always using fully qualified object names. It seems 
strange that the section doesn't mention this obvious approach. Is it 
vulnerable to subversion in a way that I haven't spotted?

I suppose that there are use cases where the actual plan is to resolve to the 
first object that has the right name as the search_path is traversed. (But this 
seems not to be the common case.) This is where setting the search_path as an 
attribute of a subprogram helps.

I wondered about a self-documenting belt-and-braces approach: use fully 
qualified object names in the subprograms source code and declare that I want 
no risk of mis-use of the search_path by setting it to null. But this seems not 
to be possible. Am I right?

I've confirmed that even a superuser cannot create objects in a "special" 
schema like "pg_catalog" or "pg_toast". So this gives me a workaround to the 
limitation that I cannot force the use of fully qualified names by setting a 
null search_path: I could set the attribute of my subprogram to "pg_catalog".

Apart from the fact that, as I suppose, this would be a rare and therefore 
possibly puzzling pattern (so clear doc about the purpose would be needed), are 
there any risks that I haven't spotted?

Finally, what do you think of a possible future enhancement to allow setting a 
null search_path?

Re: How to upgrade postgres version 8 to 13

[David G. Johnston]

On Tue, Jul 5, 2022 at 11:05 AM Adrian Klaver 
wrote:

> On 7/5/22 10:56 AM, shashidhar Reddy wrote:
> > Down time 10 to 15 hrs
> > Hardware specs are also not sure for now as it is client machine.
>
> This is going to be a difficult task for the following reasons:
>
> In other words there is going to be a lot of preliminary work and
> testing that is going to need to be done before you even start the
> transfer. The first of that is determining what your setup is. My guess
> is you will end up doing this in stages of using intervening Postgres
> versions.
>
>
I would seriously try and make this work by performing, even if not
entirely consistent, a dump of the minimal subset of the database needed to
meet most of the day-to-day needs of the applications using the database.
Create a v13 (or whatever supported version you want really) database
schema from scratch (dump schema only restore or whatever you've got in
version/migration control).  Load the data into it and make sure that
everything is working properly.  Then, resync the newest stuff and go live
while incurring additional downtime for migrating the likely decade plus of
historical data that is out there probably generally unused.  Setting up
partitions, or maybe even some separate archival database to be linked into
the new one via Foreign Data Wrappers, can also be considered.

David J.

Re: Seems to be impossible to set a NULL search_path

[Adrian Klaver]

On 7/5/22 11:12 AM, Bryn Llewellyn wrote:

The section "Writing SECURITY DEFINER Functions Safely":

https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2




Finally, what do you think of a possible future enhancement to allow setting a 
null search_path?



set search_path = '';

show search_path ;
 search_path
-
 ""

\d
Did not find any relations.


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

Re: Seems to be impossible to set a NULL search_path

[Christophe Pettus]

> On Jul 5, 2022, at 11:12, Bryn Llewellyn  wrote:
> Finally, what do you think of a possible future enhancement to allow setting 
> a null search_path?

You use the empty string, rather than NULL, but it works right now:

xof=# show search_path;
   search_path   
-
 "$user", public
(1 row)

xof=# select * from t;
 i | d1 | d2 
---++
(0 rows)

xof=# set search_path='';
SET
xof=# show search_path;
 search_path 
-
 ""
(1 row)

xof=# select * from t;
ERROR:  relation "t" does not exist
LINE 1: select * from t;
  ^
xof=# 

Re: Seems to be impossible to set a NULL search_path

[Pavel Stehule]

út 5. 7. 2022 v 20:18 odesílatel Adrian Klaver 
napsal:

> On 7/5/22 11:12 AM, Bryn Llewellyn wrote:
> > The section "Writing SECURITY DEFINER Functions Safely":
> >
> >
> https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2
> >
>
> > Finally, what do you think of a possible future enhancement to allow
> setting a null search_path?
> >
>
> set search_path = '';
>
> show search_path ;
>   search_path
> -
>   ""
>
> \d
> Did not find any relations.
>

But still in this case, there is pg_catalog in search path.

Regards

Pavel


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

Re: Seems to be impossible to set a NULL search_path

[Adrian Klaver]

On 7/5/22 11:24 AM, Pavel Stehule wrote:


 >

set search_path = '';

show search_path ;
   search_path
-
   ""

\d
Did not find any relations.


But still in this case, there is pg_catalog in search path.


Yes but from OP:

"I've confirmed that even a superuser cannot create objects in a 
"special" schema like "pg_catalog" or "pg_toast". So this gives me a 
workaround to the limitation that I cannot force the use of fully 
qualified names by setting a null search_path: I could set the attribute 
of my subprogram to "pg_catalog"."


So Bryn Llewellyn does not seem to be concerned about that.



Regards

Pavel



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: How to upgrade postgres version 8 to 13

[Michael Nolan]

I had a client that was stuck on 8.2 for a long time, when they finally
upgraded to 10, it took several weeks of testing to find things that needed
to be changed in scripts, functions and PHP programs.  And even then we
were still finding type cast issues for another year in seldom-used SQL
code.

You need to do your advance work on this conversion.
--
Mike Nolan

Re: Seems to be impossible to set a NULL search_path

[Bryn Llewellyn]

>>> adrian.kla...@aklaver.com wrote:
>>> 
>>> set search_path = '';
>>> show search_path ;
>>> search_path
>>>-
>>>   ""
>> pavel.steh...@gmail.com
>> 
>> ...But still in this case, there is pg_catalog in search path.
> 
> Yes but from OP:
> 
> « I've confirmed that even a superuser cannot create objects in a "special" 
> schema like "pg_catalog" or "pg_toast". So this gives me a workaround to the 
> limitation that I cannot force the use of fully qualified names by setting a 
> null search_path: I could set the attribute of my subprogram to "pg_catalog". 
> »
> 
> So Bryn Llewellyn does not seem to be concerned about that.

Thanks, all, for your replies. I'd assumed that the arguments of "set 
search_path" had to be SQL names. so I tried "". But that caused an error. I 
didn't try the ordinary empty string because I'd assumed that, as an illegal 
SQL name, it would be illegal in "set search_path". Hmm...

I'm slightly troubled by "works right now":

> x...@thebuild.com wrote:
> 
> You use the empty string, rather than NULL... it works right now: 

But because you experts all recommend it, I'll go with it. It's more expressive 
of what I mean than is using "pg_catalog".

(I'm assuming that having such an unwritable schema inevitably on the 
search_path is simply an entirely benign benefit. But it could give a strange 
message to the reader in my use case.)

Re: Seems to be impossible to set a NULL search_path

[Tom Lane]

Bryn Llewellyn  writes:
> Thanks, all, for your replies. I'd assumed that the arguments of "set 
> search_path" had to be SQL names. so I tried "". But that caused an error. I 
> didn't try the ordinary empty string because I'd assumed that, as an illegal 
> SQL name, it would be illegal in "set search_path". Hmm...

search_path's value is not a SQL name.  It's a list of SQL names
wrapped in a string ... and the list can be empty.

A bit off topic: I'm not sure how you came to the conclusion that
superusers can't write into pg_catalog.  They can.  But I don't see
much point in being paranoid about whether the contents of pg_catalog
are trustworthy.  If an adversary has already obtained superuser
privileges, he needn't bother with anything as complicated as
trojan-horsing something you might call later.

regards, tom lane

Re: Seems to be impossible to set a NULL search_path

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com writes:
>> 
>> ...I'd assumed that the arguments of "set search_path" had to be SQL names...
> 
> search_path's value is not a SQL name. It's a list of SQL names wrapped in a 
> string ... and the list can be empty.

I was informed by this precedent:

truncate table u1.t1, t2;

It uses a comma-separated list of optionally qualified  SQL names.

And this:

«
CREATE SCHEMA schema_name ...
»

It requires a single unqualified SQL name.

And then this:

«
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | 
DEFAULT }

value — New value of parameter. Values can be specified as... identifiers... or 
comma-separated lists of these, as appropriate for the particular parameter...
»

Notice that the token « value » in the syntax is not in quotes. I took it to 
mean (for "set search_path" a SQL name that you would type bare when it's 
simple and surrounded by double quotes when it's exotic—in line with the much 
broader general rule.

And I did ad hoc tests like these.

create schema "s1, s2";
create table "s1, s2".t(k int);
insert into "s1, s2".t(k) values(42);
set search_path = "s1, s2";
select k from t;

After all, "s1, s2" is a perfectly legal SQL name—even though folks usually 
have rules of practice to avoid exotic names like these.

I saw that the test behaves the same if I use this:

set search_path = 's1, s2';

I put that down to an unnecessary and confusing forgiveness that got 
grandfathered it.

So I'm very confused by your comment. What am I missing?.

> A bit off topic: I'm not sure how you came to the conclusion that superusers 
> can't write into pg_catalog.  They can.


With tests like these:

\c demo postgres
create table pg_catalog.t(n int);

It fails with this:

42501: permission denied to create "pg_catalog.t"

I did note this detail: "System catalog modifications are currently 
disallowed." Is there a configuration parameter that controls this?

> I don't see much point in being paranoid... if an adversary has already 
> obtained superuser privileges

Yes, that point is very well taken. But I like to know the limit's of what's 
technically possible.

Re: Seems to be impossible to set a NULL search_path

[Adrian Klaver]

On 7/5/22 15:08, Bryn Llewellyn wrote:


I was informed by this precedent:

*truncate table u1.t1, t2;
*
It uses a comma-separated list of optionally qualified  SQL names.

And this:

«
*CREATE SCHEMA schema_name ...
*»


Those are creating objects. Set search_path is setting a configuration 
value.




And then this:

«
*SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 
'value' | DEFAULT }*


value — New value of parameter. Values can be specified as... 
identifiers... or comma-separated lists of these, as appropriate for the 
particular parameter...

»

Notice that the token « value » in the syntax is not in quotes. I took 


Pretty sure it is:

{ TO | = } { value | 'value' | DEFAULT }





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

Re: General Inquiry

[Kyotaro Horiguchi]

At Tue, 5 Jul 2022 08:49:40 +, "Cloete, F. (Francois)" 
 wrote in 
> Good morning,
> Can you please confirm if I need to contact the postgresql community fro some 
> assistance can I use this e-mail address or another e-mail ?

pgsql-general is the right place for that. Welcome.

> We keep on seeing the below entries in our postgresql.log file getting 
> written what seems to be every millisecond.

> UTF [32640]: [1-1] user=[unknown],db=[unknown],app=[unknown],client=[local] L

Unfortunately the screen capture is missing the most significant
part. But I guess that they are "LOG: connection received".

If this is correct, log_connection is set to on on your server, which
makes server record every incoming connection like this.  The frequent
log lines mean that someone on the local host is frequently (trying)
making connections to the server.

If they are unexpected incoming connections, one possibility is that
something like health-check or keep alive thing is having a wrong
interval setting.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Seems to be impossible to set a NULL search_path

[David G. Johnston]

On Tue, Jul 5, 2022 at 12:13 PM Tom Lane  wrote:

> Bryn Llewellyn  writes:
> > Thanks, all, for your replies. I'd assumed that the arguments of "set
> search_path" had to be SQL names. so I tried "". But that caused an error.
> I didn't try the ordinary empty string because I'd assumed that, as an
> illegal SQL name, it would be illegal in "set search_path". Hmm...
>
> search_path's value is not a SQL name.  It's a list of SQL names
> wrapped in a string ... and the list can be empty.
>
>
This doesn't seem to be correct - wrapping them in single quotes in the SET
command ends up behaving as if you wrapped them in double quotes anywhere
else (and wrapping them individually in double quotes here works just fine
too).

("testschema" exists, schemas "a" and "b" do not)
 ^
postgres=# set search_path to 'a, b, testschema';
SET
postgres=# show search_path;
search_path

 "a, b, testschema"
(1 row)

postgres=# create table inab (id serial primary key);
ERROR:  no schema has been selected to create in
LINE 1: create table inab (id serial primary key);
 ^
postgres=# set search_path to a, b, testschema;
SET
postgres=# show search_path;
   search_path
--
 a, b, testschema
(1 row)

postgres=# create table inab (id serial primary key);
CREATE TABLE
postgres=#

postgres=# set search_path to '';
SET
postgres=# show search_path;
 search_path
-
 ""
(1 row)

postgres=# set search_path to "";
ERROR:  zero-length delimited identifier at or near 
LINE 1: set search_path to "";
   ^
postgres=# create table testtable."" (id serial primary key);
ERROR:  zero-length delimited identifier at or near 
LINE 1: create table testtable."" (id serial primary key);
   ^
postgres=#

So you cannot actually write an identifier that is double quoted empty
string but if you write  < SET search_path TO '' > that is basically what
the system thinks you have done.

postgres=# set search_path to '';
SET
postgres=# create table "es" (id serial primary key);
ERROR:  no schema has been selected to create in
LINE 1: create table "es" (id serial primary key);
 ^
postgres=#

Thus, it must be set using a list of identifiers, separated by commas, but
single quotes can be used instead of double quotes if desired, and single
quotes must be used to set it to a value where there are no additional
schemas added to the mandatory (and thus implied) < pg_temp, pg_catalog >
specification.

A bit off topic: I'm not sure how you came to the conclusion that
> superusers can't write into pg_catalog.  They can.


True, but they need to jump through the hoop of modifying a setting before
they are allowed to do so.

David J.

Re: How to upgrade postgres version 8 to 13

[shashidhar Reddy]

Thank you all for the inputs, any one tried upgrading from 8.2 to 8.4 and
then used pg_upgrade to upgrade it to any higher version.

On Wed, 6 Jul, 2022, 12:01 am Michael Nolan,  wrote:

> I had a client that was stuck on 8.2 for a long time, when they finally
> upgraded to 10, it took several weeks of testing to find things that needed
> to be changed in scripts, functions and PHP programs.  And even then we
> were still finding type cast issues for another year in seldom-used SQL
> code.
>
> You need to do your advance work on this conversion.
> --
> Mike Nolan
>
>

Re: Seems to be impossible to set a NULL search_path

[Bryn Llewellyn]

> david.g.johns...@gmail.com wrote:
> 
>> t...@sss.pgh.pa.us wrote:
>> 
>> search_path's value is not a SQL name.  It's a list of SQL names wrapped in 
>> a string ... and the list can be empty.
> 
> This doesn't seem to be correct - wrapping them in single quotes in the SET 
> command ends up behaving as if you wrapped them in double quotes anywhere 
> else (and wrapping them individually in double quotes here works just fine 
> too).

And then...

> adrian.kla...@aklaver.com wrote:
> 
> Those are creating objects. Set search_path is setting a configuration value. 
> Pretty sure it is:
> 
> { TO | = } { value | 'value' | DEFAULT 

There's different use cases. For example:

set my_namspace.x = 'Dog house';
show my_namspace.x ;

I can't reconcile what you three (Tom, David, and Adrian) have said. I'm 
interested to hear how you interpret what I showed in this reply:

https://www.postgresql.org/message-id/48E1391E-5A21-4736-B4B1-8B9468ECAFD4%40yugabyte.com

and in particular to this:

create schema "s1, s2";
create table "s1, s2".t(k int);
insert into "s1, s2".t(k) values(42);
set search_path = "s1, s2";
show search_path;
select k from t;

OR (with single quotes in "set search_path":

create schema "s1, s2";
create table "s1, s2".t(k int);
insert into "s1, s2".t(k) values(42);
set search_path = 's1, s2';
show search_path;
select k from t;

I get a resounding 42 in both cases. Now try this:

set search_path = no_such_schema, "No Such Schema";
show search_path;

All outcomes accord with the mental model that you tell me is wrong.

RE: General Inquiry

[Cloete, F. (Francois)]

Hi,
Apologies here is an example of the entry,

UTC [1436]: [1-1] user=[unknown],db=[unknown],app=[unknown],client=[local] LOG: 
 connection received: host=[local]

Regards

-Original Message-
From: Kyotaro Horiguchi 
Sent: Wednesday, 06 July 2022 02:50
To: Cloete, F. (Francois) 
Cc: pgsql-gene...@postgresql.org
Subject: Re: General Inquiry

[You don't often get email from horikyota@gmail.com. Learn why this is 
important at https://aka.ms/LearnAboutSenderIdentification ]


CAUTION - EXTERNAL SENDER -  Please be careful when opening links and 
attachments. Nedbank - IT Information Security Department (ISD)


At Tue, 5 Jul 2022 08:49:40 +, "Cloete, F. (Francois)" 
 wrote in
> Good morning,
> Can you please confirm if I need to contact the postgresql community fro some 
> assistance can I use this e-mail address or another e-mail ?

pgsql-general is the right place for that. Welcome.

> We keep on seeing the below entries in our postgresql.log file getting 
> written what seems to be every millisecond.

> UTF [32640]: [1-1]
> user=[unknown],db=[unknown],app=[unknown],client=[local] L

Unfortunately the screen capture is missing the most significant part. But I 
guess that they are "LOG: connection received".

If this is correct, log_connection is set to on on your server, which makes 
server record every incoming connection like this.  The frequent log lines mean 
that someone on the local host is frequently (trying) making connections to the 
server.

If they are unexpected incoming connections, one possibility is that something 
like health-check or keep alive thing is having a wrong interval setting.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center



Nedbank disclaimer and confidentiality notice:

This email may contain information that is confidential, privileged or 
otherwise protected from disclosure. If you are not an intended recipient of 
this email or all or some of the information contained therein, do not 
duplicate or redistribute it by any means. Please delete it and any attachments 
and notify the sender that you have received it in error. Unless specifically 
indicated, this email is neither an offer or a solicitation to buy or sell any 
securities, investment products or other financial product or service, nor is 
it an official confirmation of any transaction or an official statement of 
Nedbank. Any views or opinions presented are solely those of the author and do 
not necessarily represent those of Nedbank. Nedbank Ltd Reg No 1951/09/06.

The following link displays the names of the Nedbank Board of Directors and 
Company Secretary. [http://www.nedbank.co.za/terms/DirectorsNedbank.htm]

If you do not want to click on a link, please type the relevant address in your 
browser

Re: lifetime of the old CTID

[Matthias Apitz]

El día martes, julio 05, 2022 a las 06:19:18p. m. +0200, Laurenz Albe escribió:

> > Hi Laurenz, ist there any way to keep/freeze such tuples until the run
> > of the next autovaccum? Some kind of config value in 13.x or 14.x? Or
> > even a code change for this? We compile on Linux from the sources and
> > have already some code changes in place...
> 
> That should be possible, but why do you want that?  Perhaps there is a better
> solution for your problem.

Here you can read the overall picture of the problem. Thanks in advance
for your time reading it.


The logic in the application in this case here goes like this:

In a table 'd01buch' (the table of all book items) a big list is
made with a call to the DB layer and this list is fetched row
by row (i.e. book by book) and each item is analyzed in other tables
if certain criteria are matched and if so the row is locked
and some of its columns get updated:

   read_where(d01buch, where ...);

   while ( fetch()==OK )   {

   ...
   if ()  {

 start_transaction();
 rewrite_actual_row();
 end_transaction();

   }

   }

The above visible C-functions are entries in a generic DB layer
which offers the same functionality for some 400 tables, written in
ESQL/C, and can be compiled with smaller changes by the C-preprocessor
for the DBS Oracle, Sybase and PostgreSQL.

Internally, in the DB layer, the read_where() builds the row list matching
the WHERE clause as a SCROLLED CURSOR of

SELECT ctid, * FROM d01buch WHERE ...

and each fetch() delivers the next row from this cursor. The functions
start_transaction() and end_transaction() do what their names suggest and
rewrite_actual_row() does a new SELECT based on the ctid of the actual row

SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
...
UPDATE ...

The ctid is the value from the time when the big CURSOR was made in 
read_where().
Its size is normally some 10 rows and the above while loop takes it time,
normally some 10 to 20 minutes. All this (and many other things like this) run
every night as some house keeping work of a Library Management System.

In parallel are other processes working in the same DB on the same tables,
esp. some OPAC (a web based Online Public Access Catalogue system) with which
library users (patrons) can make reservation of books, pay fees etc.

It happens that rows from the above table get modified within
the time frame of 10 to 20 minutes which means that the ctid changes
and the above SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE fails because
the ctid is what is in the (old) CURSOR. We tried to use currtid2() to get
the current ctid based on the old one, but as we see this does not help
always.

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Christophe Pettus]

> On Jul 5, 2022, at 22:35, Matthias Apitz  wrote:
> Internally, in the DB layer, the read_where() builds the row list matching
> the WHERE clause as a SCROLLED CURSOR of
> 
>SELECT ctid, * FROM d01buch WHERE ...
> 
> and each fetch() delivers the next row from this cursor. The functions
> start_transaction() and end_transaction() do what their names suggest and
> rewrite_actual_row() does a new SELECT based on the ctid of the actual row
> 
>SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
>...
>UPDATE ...

On first glance, it appears that you are using the ctid as a primary key for a 
row, and that's highly not-recommended.  The ctid is never intended to be 
stable in the database, as you have discovered.  There are really no particular 
guarantees about ctid values being retained.

I'd suggest having a proper primary key column on the table, and using that 
instead.

Re: lifetime of the old CTID

[Andreas Kretschmer]

Am 06.07.22 um 07:44 schrieb Christophe Pettus:



On Jul 5, 2022, at 22:35, Matthias Apitz  wrote:
Internally, in the DB layer, the read_where() builds the row list matching
the WHERE clause as a SCROLLED CURSOR of

SELECT ctid, * FROM d01buch WHERE ...

and each fetch() delivers the next row from this cursor. The functions
start_transaction() and end_transaction() do what their names suggest and
rewrite_actual_row() does a new SELECT based on the ctid of the actual row

SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
...
UPDATE ...

On first glance, it appears that you are using the ctid as a primary key for a 
row, and that's highly not-recommended.  The ctid is never intended to be 
stable in the database, as you have discovered.  There are really no particular 
guarantees about ctid values being retained.

I'd suggest having a proper primary key column on the table, and using that 
instead.



100% ACK.

Andreas


--
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

Re: lifetime of the old CTID

[Andreas Kretschmer]

Am 06.07.22 um 07:54 schrieb Andreas Kretschmer:



Am 06.07.22 um 07:44 schrieb Christophe Pettus:



On Jul 5, 2022, at 22:35, Matthias Apitz  wrote:
Internally, in the DB layer, the read_where() builds the row list 
matching

the WHERE clause as a SCROLLED CURSOR of

    SELECT ctid, * FROM d01buch WHERE ...

and each fetch() delivers the next row from this cursor. The functions
start_transaction() and end_transaction() do what their names 
suggest and
rewrite_actual_row() does a new SELECT based on the ctid of the 
actual row


    SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
    ...
    UPDATE ...
On first glance, it appears that you are using the ctid as a primary 
key for a row, and that's highly not-recommended.  The ctid is never 
intended to be stable in the database, as you have discovered.  There 
are really no particular guarantees about ctid values being retained.


I'd suggest having a proper primary key column on the table, and 
using that instead.



100% ACK.

Andreas




it reminds me somehow on how people used he OID in old times - and now 
we removed the OID completely.


Andreas

--
Andreas Kretschmer
Technical Account Manager (TAM)
www.enterprisedb.com

Re: lifetime of the old CTID

[Matthias Apitz]

El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus 
escribió:

> 
> 
> > On Jul 5, 2022, at 22:35, Matthias Apitz  wrote:
> > Internally, in the DB layer, the read_where() builds the row list matching
> > the WHERE clause as a SCROLLED CURSOR of
> > 
> >SELECT ctid, * FROM d01buch WHERE ...
> > 
> > and each fetch() delivers the next row from this cursor. The functions
> > start_transaction() and end_transaction() do what their names suggest and
> > rewrite_actual_row() does a new SELECT based on the ctid of the actual row
> > 
> >SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
> >...
> >UPDATE ...
> 
> On first glance, it appears that you are using the ctid as a primary key for 
> a row, and that's highly not-recommended.  The ctid is never intended to be 
> stable in the database, as you have discovered.  There are really no 
> particular guarantees about ctid values being retained.
> 
> I'd suggest having a proper primary key column on the table, and using that 
> instead.

Ofc, each table has its own primary key(s), used for example for the 
SELECT ctid, * FROM d01buch WHERE ...

As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has
for each table a so called SYB_IDENTITY_COLUMN which is static for the
table and its value does not change. When we would add now to some 400 tables an
additional INTEGER column (and triggers to fill this on INSERT) this
would be a big change in our DB layer and migration of databases in the
field. Your suggesting (thanks for it in any case) is not that easy to
implement, and no option at the moment.

At the moment the DB layer informs the application layer correctly when
the row can not be found by the ctid for an UPDATE and the application
must handle this situation correctly (logging, 2nd pass through with a
new CURSOR etc.).

If I understand Laurenz correctly, there seems to be a way to keep the
tuple old-ctid : new-ctid for some time, at least until the ney
autovacuum.


matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: lifetime of the old CTID

[Laurenz Albe]

On Wed, 2022-07-06 at 08:18 +0200, Matthias Apitz wrote:
> El día martes, julio 05, 2022 a las 10:44:23p. m. -0700, Christophe Pettus 
> escribió:
> 
> > 
> > 
> > > On Jul 5, 2022, at 22:35, Matthias Apitz  wrote:
> > > Internally, in the DB layer, the read_where() builds the row list matching
> > > the WHERE clause as a SCROLLED CURSOR of
> > > 
> > >    SELECT ctid, * FROM d01buch WHERE ...
> > > 
> > > and each fetch() delivers the next row from this cursor. The functions
> > > start_transaction() and end_transaction() do what their names suggest and
> > > rewrite_actual_row() does a new SELECT based on the ctid of the actual row
> > > 
> > >    SELECT * FROM d01buch WHERE ctid = ... FOR UPDATE
> > >    ...
> > >    UPDATE ...
> > 
> > On first glance, it appears that you are using the ctid as a primary key 
> > for a row, and that's highly not-recommended.  The ctid is never intended 
> > to be stable in the database, as you have
> > discovered.  There are really no particular guarantees about ctid values 
> > being retained.
> > 
> > I'd suggest having a proper primary key column on the table, and using that 
> > instead.
> 
> Ofc, each table has its own primary key(s), used for example for the 
> SELECT ctid, * FROM d01buch WHERE ...
> 
> As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has
> for each table a so called SYB_IDENTITY_COLUMN which is static for the
> table and its value does not change. When we would add now to some 400 tables 
> an
> additional INTEGER column (and triggers to fill this on INSERT) this
> would be a big change in our DB layer and migration of databases in the
> field. Your suggesting (thanks for it in any case) is not that easy to
> implement, and no option at the moment.
> 
> At the moment the DB layer informs the application layer correctly when
> the row can not be found by the ctid for an UPDATE and the application
> must handle this situation correctly (logging, 2nd pass through with a
> new CURSOR etc.).
> 
> If I understand Laurenz correctly, there seems to be a way to keep the
> tuple old-ctid : new-ctid for some time, at least until the ney
> autovacuum.

No, there isn't.  I said that you might be able to mutilate the PostgreSQL
code so that it does that, but I think that would be a bad idea.

Using the primary key is the proper solution.  To be safe from concurrent
modifications, use a logic like in this pseudo-code:

FOR b IN SELECT pk, other_columns FROM books WHERE condition
   UPDATE books SET ... WHERE pk = ... AND condition

Checking the condition again on the inner UPDATE will detect concurrent
data modifications.  If the UPDATE changes nothing, then a book has been
removed or updated by a concurrent transaction, and you ignore it.

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