Обсуждение: pg_upgrade: What is changed?

I'm working on pg_upgrade concept. I try to determine what is changed
from 8.1 to 8.2. There is list of important areas for upgrade and
suggested action.

1) BKI - catalog.
    There are a lot of changes. See attached file.
    a) There is new table pg_shdescription
        action: create
    b) There is new record in some tables.
        action: insert
        question: Should be any OID conflict during insertion?
    c) Some records are changed
        action: ???
    d) Some record are removed
        action: keep it
        question : Is it safe to keep for example record in the pg_proc?

2) GUC - postgresql.conf
    a) New variable.
        action: nothing
    b) Some changes (default, context)
        action: nothing
    c) backslash_quote is removed
        action: removed from postgresql.conf

   There is list of GUC changes:
    ssl (assign hook)
    array_nulls   (new)
    escape_string_warning (default false->true, context
PGC_INTERNAL->PGC_USERSET)
    allow_system_table_mods (new)
    ignore_system_indexes (new)
    post_auth_delay (new)
    join_collapse_limit (description)
    backslash_quote (removed)
    search_path (default $user,public -> \"$user\",public)


3) pg_hba.conf, pg_ident.conf
    no changes


4) Disk layout is same - version 3

5) Tuples
      question: Does have data types some disk representation?
                Does have tupleheader same structure?

6) Indexes
      question: Any changes on disk representation?
           Should be possible remove all index before upgrade and recreate
them on the new version instead upgrade index structure?

7) Change PG_VERSION files

8) WAL/XLOG
     Question: Should be deleted?

9) ...

     Any other ideas?



Zdenek

On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote:

<snip>

> 1) BKI - catalog.
>    c) Some records are changed
>        action: ???

They just need to be changed. In principle the datalog needs to be
updated so it looks like a database initdb'd with the new version.

> 5) Tuples
>      question: Does have data types some disk representation?
>                Does have tupleheader same structure?

I think only the inet/cidr types changed format this release. Ways to
handle that have been discussed:

1. Have server contain code for old versions under old OID. This was
mentioned as a possibility.
2. ALTER TYPE ALTER TYPE from old to new type, possibly using text as
intermediary.
3. Fiddle with bits on disk (not sure if this is even possible)

> 6) Indexes
>      question: Any changes on disk representation?
>            Should be possible remove all index before upgrade and
>            recreate them on the new version instead upgrade index structure?

Maybe, maybe not. Why risk it? Just REINDEX the whole database
afterwards.

> 8) WAL/XLOG
>     Question: Should be deleted?

I imagine you should probably force a checkpoint and then wipe the wal
records. The WAL isn't going to be able to cover some of the stuff done
during the upgrade, so it'd be useless after anyway.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote:
> 
> <snip>
> 
>> 1) BKI - catalog.
>>    c) Some records are changed
>>        action: ???
> 
> They just need to be changed. In principle the datalog needs to be
> updated so it looks like a database initdb'd with the new version.

Yes I agree but The question is if some OID collision should appears for 
example collision between user function created in the 8.1 and build-in 
function in 8.2. There are some changes like this:

! insert OID = 1838 ( numeric_variance 11 10 12 f f t f i 1 1700 "1231" 
_null_ _null_ _null_ numeric_variance - _null_ )

! insert OID = 1838 ( numeric_var_samp 11 10 12 f f t f i 1 1700 "1231" 
_null_ _null_ _null_ numeric_var_samp - _null_ )

Is only renaming or is it different proc?

> 
>> 5) Tuples
>>      question: Does have data types some disk representation?
>>                Does have tupleheader same structure?
> 
> I think only the inet/cidr types changed format this release. Ways to
> handle that have been discussed:
> 
> 1. Have server contain code for old versions under old OID. This was
> mentioned as a possibility.
> 2. ALTER TYPE ALTER TYPE from old to new type, possibly using text as
> intermediary.
> 3. Fiddle with bits on disk (not sure if this is even possible)

It looks like that name is same but data representation is different. 
I'm not sure if possible use same data type names with different OID. I 
think there is unique index. We can rename this type to inet_old, but 
some application should confused. I quickly looked in network.c and It 
looks that data size is same (4 or 16). Option 3 is possible in this 
case and I should be easy to implement it.

Does anybody know what exactly has been changed?

>> 6) Indexes
>>      question: Any changes on disk representation?
>>            Should be possible remove all index before upgrade and 
>>            recreate them on the new version instead upgrade index structure?
> 
> Maybe, maybe not. Why risk it? Just REINDEX the whole database
> afterwards.

Will be possible run REINDEX database with "damaged/old" index data 
structure? For example on inet/cidr index?

Zdenek

Zdenek Kotala wrote:
> Martijn van Oosterhout wrote:
> >On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote:
> >
> ><snip>
> >
> >>1) BKI - catalog.
> >>   c) Some records are changed
> >>       action: ???
> >
> >They just need to be changed. In principle the datalog needs to be
> >updated so it looks like a database initdb'd with the new version.
> 
> Yes I agree but The question is if some OID collision should appears for 
> example collision between user function created in the 8.1 and build-in 
> function in 8.2.
> 
> ! insert OID = 1838 ( numeric_variance 11 10 12 f f t f i 1 1700 "1231" 
> _null_ _null_ _null_ numeric_variance - _null_ )

Those OIDs cannot be used for user types, because the OID counter skips
then when wrapping around.  See GetNewObjectId.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Aug 23, 2006 at 10:49:05AM +0200, Martijn van Oosterhout wrote:
> > 8) WAL/XLOG
> >     Question: Should be deleted?
> 
> I imagine you should probably force a checkpoint and then wipe the wal
> records. The WAL isn't going to be able to cover some of the stuff done
> during the upgrade, so it'd be useless after anyway.

Is there any way around that? If WAL can't be trusted that means if you
crash during update, you're hosed. Which means you need to backup the
database before upgrading, which greatly increases downtime. Same
applies to having to reindex everything.

Granted, *any* kind of upgrade not requiring a dump/restore is a major
improvement.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
> On Wed, Aug 23, 2006 at 10:49:05AM +0200, Martijn van Oosterhout wrote:
>>> 8) WAL/XLOG
>>>     Question: Should be deleted?
>> I imagine you should probably force a checkpoint and then wipe the wal
>> records. The WAL isn't going to be able to cover some of the stuff done
>> during the upgrade, so it'd be useless after anyway.
> 
> Is there any way around that? If WAL can't be trusted that means if you
> crash during update, you're hosed. Which means you need to backup the
> database before upgrading, which greatly increases downtime. Same
> applies to having to reindex everything.

By my opinion upgrade process should fail for example during catalog 
adjustment. This step probably will not have any record in the WAL and 
you will stay in the middle and ... you will start looking for backup.

> Granted, *any* kind of upgrade not requiring a dump/restore is a major
> improvement.

Any really good DBA never do upgrade without backup. It is too dangerous 
operation. By the way, you can do backup without downtime. Yes, you lost 
all data after backup. It is risk and DBA must make decision if it is 
acceptable or not.

Is possible play WAL to get lost data after backup? If yes, you can 
backup on-line database and first step of upgrade should be move WAL to 
the safe place.
    Zdenek

Zdenek Kotala napsal(a):
> Martijn van Oosterhout wrote:
>> On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote:

>>> 5) Tuples
>>>      question: Does have data types some disk representation?
>>>                Does have tupleheader same structure?
>>
>> I think only the inet/cidr types changed format this release. Ways to
>> handle that have been discussed:
>>
>> 1. Have server contain code for old versions under old OID. This was
>> mentioned as a possibility.
>> 2. ALTER TYPE ALTER TYPE from old to new type, possibly using text as
>> intermediary.
>> 3. Fiddle with bits on disk (not sure if this is even possible)
> 
> It looks like that name is same but data representation is different. 
> I'm not sure if possible use same data type names with different OID. I 
> think there is unique index. We can rename this type to inet_old, but 
> some application should confused. I quickly looked in network.c and It 
> looks that data size is same (4 or 16). Option 3 is possible in this 
> case and I should be easy to implement it.
> 
> Does anybody know what exactly has been changed?

I examined ODS (on disk structure) and inet/cidr use 1byte less size for 
storage information. PG8.1 uses two bytes for store INET type value 
instead PG8.2 uses only one byte. I did not find any other change in 
these types related to the ODS.

Because, attribute size is smaller then in the previous version, direct 
tuple "compact" is possible. We can move part of tuple one byte left and 
decrease size of tuple in the ItemIdData list  for each not null 
occurrence inet/cidr attribute in the tuple. I'm working on a prototype.

Indexes contain inet/cidr data type will be recreated by REINDEX command 
or dropped on the old version and create again on the 8.2.Zdenek

PS: There is attribute difference between 8.1 and 8.2    
tuple1 = 1.2.3.4/24
tuple2 = 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128


Postgres 8.2
           1) Offset=8152 Size=  38             xmin=1217 cmin=0 xmax=0 
cmax=0 natts=1 info=902h hoff=28 ctid=(0/1)
                        0a 00 00 00 02 18 01 02                        03 04
           2) Offset=8100 Size=  50             xmin=1219 cmin=0 xmax=0 
cmax=0 natts=1 info=902h hoff=28 ctid=(0/2)
                        16 00 00 00 03 80 20 01                        04 f8 00 03 00 ba 02 e0
81ff fe 22 d1 f1
 


Postgres 8.1

block=   0 pd_lsn=16278166570008576 pd_tli=1 pd_lower=32 pd_upper=8048 
pd_special=8192 page_size=8192 page_version=3           1) Offset=8152 Size=  39             xmin=594 cmin=0 xmax=0 
cmax=0 natts=1 info=902h hoff=28 ctid=(0/1)
                        0b 00 00 00 02 18 00 01                        02 03 04
           3) Offset=8048 Size=  51             xmin=1338 cmin=0 xmax=0 
cmax=0 natts=1 info=902h hoff=28 ctid=(0/3)
                        17 00 00 00 03 80 00 20                        01 04 f8 00 03 00 ba 02
e081 ff fe 22 d1 f1