pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe-order columns?
14+ messages / 9 participants
[nested] [flat]
* Re-order columns?
@ 2020-07-29 20:41 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Wells Oliver @ 2020-07-29 20:41 UTC (permalink / raw)
To: pgsql-admin
I know this kind of dumb, but is there a way to re-order columns? You know,
you have your awesome table all defined, your favorite modified_at column
at the end where you like it, then some guy comes along and is like "oh
let's add X Y and Z" and your OCD really really really wants the
modified_at col at the end, and blowing away/re-populating the table takes
way too long.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 20:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2020-07-29 20:45 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
> I know this kind of dumb, but is there a way to re-order columns? You
> know, you have your awesome table all defined, your favorite modified_at
> column at the end where you like it, then some guy comes along and is like
> "oh let's add X Y and Z" and your OCD really really really wants the
> modified_at col at the end, and blowing away/re-populating the table takes
> way too long
>
There is not. Every so often this gets looked at but has yet to result in
a commit.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 20:50 Holger Jakobs <holger@jakobs.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 3 replies; 14+ messages in thread
From: Holger Jakobs @ 2020-07-29 20:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org,"David G. Johnston" <david.g.johnston@gmail.com>; Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It's the same as the order of rows in a table - undefined (unless you use order by).
That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.
Of you prefer a different order, use a view.
Am 29. Juli 2020 22:45:06 MESZ schrieb "David G. Johnston" <david.g.johnston@gmail.com>:
>On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com>
>wrote:
>
>> I know this kind of dumb, but is there a way to re-order columns? You
>> know, you have your awesome table all defined, your favorite
>modified_at
>> column at the end where you like it, then some guy comes along and is
>like
>> "oh let's add X Y and Z" and your OCD really really really wants the
>> modified_at col at the end, and blowing away/re-populating the table
>takes
>> way too long
>>
>
>There is not. Every so often this gets looked at but has yet to result
>in
>a commit.
>
>David J.
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 20:52 Wells Oliver <wells.oliver@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 14+ messages in thread
From: Wells Oliver @ 2020-07-29 20:52 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org, "David G. Johnston" <david.g.johnston@gmail.com>; pgsql-admin
The order of columns is important! I can't go to sleep at night knowing my
modified_at isn't at the end of the table! This is serious business.
On Wed, Jul 29, 2020 at 1:50 PM Holger Jakobs <holger@jakobs.com> wrote:
> Actually, the order of columns is of _no_ importance, since columns are
> identified by name and not by column number. It's the same as the order of
> rows in a table - undefined (unless you use order by).
>
> That columns appear to have some kind of order and show in tools with a
> stable order has technical reasons and is not rooted in relational theory.
>
> Of you prefer a different order, use a view.
>
>
> Am 29. Juli 2020 22:45:06 MESZ schrieb "David G. Johnston" <
> david.g.johnston@gmail.com>:
>>
>> On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
>>
>>> I know this kind of dumb, but is there a way to re-order columns? You
>>> know, you have your awesome table all defined, your favorite modified_at
>>> column at the end where you like it, then some guy comes along and is like
>>> "oh let's add X Y and Z" and your OCD really really really wants the
>>> modified_at col at the end, and blowing away/re-populating the table takes
>>> way too long
>>>
>>
>> There is not. Every so often this gets looked at but has yet to result
>> in a commit.
>>
>> David J.
>>
>>
> --
> Holger Jakobs, Bergisch Gladbach
> +49 178 9759012
> - sent from mobile, therefore short -
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 20:56 David G. Johnston <david.g.johnston@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
2 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2020-07-29 20:56 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> wrote:
>
> That columns appear to have some kind of order and show in tools with a
> stable order has technical reasons and is not rooted in relational theory.
>
Technical reasons are real reasons. The order of columns is very real,
guaranteed (beware of inherits), thing in PostgreSQL whereas there is no
guarantee regarding row order outside of a select query order by. That
said I do agree that fulfilling “ocd” drives isn’t a strong use case to
work on the feature.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 21:10 Olivier Gautherot <ogautherot@gautherot.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Olivier Gautherot @ 2020-07-29 21:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
Hi,
On Wed, Jul 29, 2020 at 10:45 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wednesday, July 29, 2020, Wells Oliver <wells.oliver@gmail.com> wrote:
>
>> I know this kind of dumb, but is there a way to re-order columns? You
>> know, you have your awesome table all defined, your favorite modified_at
>> column at the end where you like it, then some guy comes along and is like
>> "oh let's add X Y and Z" and your OCD really really really wants the
>> modified_at col at the end, and blowing away/re-populating the table takes
>> way too long
>>
>
> There is not. Every so often this gets looked at but has yet to result in
> a commit.
>
It is an issue that pops up regularly and there are technical issues
behind. It has been fixed in other engines but not yet here.
If it is critical for your application, I could suggest 2 ways:
1) run a SELECT INTO taking care of the proper column order everytime you
ALTER the table (it may or may not be doable depending on your specific
case)
2) place a view in front of the table with the expected order
The latter will surely reduce the maintenance, overhead, downtime... Now if
the order is really critical, you should probably specify the columns list
when you access the table (knowing it can be a pain if there are many).
Note that if it gets solved, it will most probably be done in the catalog
and not the table itself.
Hope it helps
Olivier
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 21:11 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2020-07-29 21:11 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
On Wednesday, July 29, 2020, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> wrote:
>
>>
>> That columns appear to have some kind of order and show in tools with a
>> stable order has technical reasons and is not rooted in relational theory.
>>
>
> Technical reasons are real reasons. The order of columns is very real,
> guaranteed (beware of inherits), thing in PostgreSQL whereas there is no
> guarantee regarding row order outside of a select query order by. That
> said I do agree that fulfilling “ocd” drives isn’t a strong use case to
> work on the feature.
>
As for the theory, a tuple is an ordered list so the ordering of columns is
a precise application of that definition and a relation is just a set
(i.e., unordered) of tuples. So the claim this isn’t rooted in relational
theory just seems wrong.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 21:18 Olivier Gautherot <ogautherot@gautherot.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Olivier Gautherot @ 2020-07-29 21:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
David,
On Wed, Jul 29, 2020 at 11:11 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wednesday, July 29, 2020, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>
>> On Wednesday, July 29, 2020, Holger Jakobs <holger@jakobs.com> wrote:
>>
>>>
>>> That columns appear to have some kind of order and show in tools with a
>>> stable order has technical reasons and is not rooted in relational theory.
>>>
>>
>> Technical reasons are real reasons. The order of columns is very real,
>> guaranteed (beware of inherits), thing in PostgreSQL whereas there is no
>> guarantee regarding row order outside of a select query order by. That
>> said I do agree that fulfilling “ocd” drives isn’t a strong use case to
>> work on the feature.
>>
>
> As for the theory, a tuple is an ordered list so the ordering of columns
> is a precise application of that definition and a relation is just a set
> (i.e., unordered) of tuples. So the claim this isn’t rooted in relational
> theory just seems wrong.
>
The issue here is to ensure that a given column always shows up at the end,
even after you added other columns. In other words, it is a kind of "ALTER
TABLE ... INSERT COLUMN AT ..." instead of "... ADD COLUMN ...". It is more
about data modeling than relational theory...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re-order columns?
@ 2020-07-29 21:20 Stephen Frost <sfrost@snowman.net>
parent: Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 14+ messages in thread
From: Stephen Frost @ 2020-07-29 21:20 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org, "David G. Johnston" <david.g.johnston@gmail.com>; Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
Greetings,
* Holger Jakobs (holger@jakobs.com) wrote:
> Actually, the order of columns is of _no_ importance, since columns are identified by name and not by column number. It's the same as the order of rows in a table - undefined (unless you use order by).
>
> That columns appear to have some kind of order and show in tools with a stable order has technical reasons and is not rooted in relational theory.
There's very practical reasons to think about the ordering of columns,
at least in PostgreSQL- you can end up creating holes in your row if you
order the columns in the wrong way and that can lead to wasted space.
There was a patch floating around to allow re-ordering, which would also
then allow us to re-order the row underneath to avoid wasting space (as
much as possible, anyway) which would be really rather nice, but sadly
it didn't get enough push on it to make it happen.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (818B, ../../20200729212039.GT12375@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Postgres upgrade from 9.6.9 to postgresql 16 version
@ 2024-02-13 16:22 Krishnaswamy <krishpostgresql@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Krishnaswamy @ 2024-02-13 16:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All
We have a postgreSQL db with version *9.6.9* version and planning to
upgrade to latest postgresql 16 version.
Database size is around 4 TB .
Please suggest
1) options/methods to upgrade
2) best practices to note while doing this much major version upgrade
Thanks
Krishnaswamy
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgres upgrade from 9.6.9 to postgresql 16 version
@ 2024-02-13 16:27 Chris Hoover <chrish@aweber.com>
parent: Krishnaswamy <krishpostgresql@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Chris Hoover @ 2024-02-13 16:27 UTC (permalink / raw)
To: Krishnaswamy <krishpostgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
We went from 9.0 -> v15 in a single step. We used Londiste to do the replication. It’s older technology, but made upgrading in a single move possible. DB is +2TB. We had about 1 hr of downtime between final steps and validation of our applications.
Thanks,
Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com
> On Feb 13, 2024, at 11:22 AM, Krishnaswamy <krishpostgresql@gmail.com> wrote:
>
> Hi All
>
> We have a postgreSQL db with version 9.6.9 version and planning to upgrade to latest postgresql 16 version.
>
> Database size is around 4 TB .
>
> Please suggest
> 1) options/methods to upgrade
> 2) best practices to note while doing this much major version upgrade
>
> Thanks
> Krishnaswamy
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgres upgrade from 9.6.9 to postgresql 16 version
@ 2024-02-13 17:26 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Krishnaswamy <krishpostgresql@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Ron Johnson @ 2024-02-13 17:26 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Feb 13, 2024 at 11:23 AM Krishnaswamy <krishpostgresql@gmail.com>
wrote:
> Hi All
>
> We have a postgreSQL db with version *9.6.9* version and planning to
> upgrade to latest postgresql 16 version.
>
> Database size is around 4 TB .
>
> Please suggest
> 1) options/methods to upgrade
> 2) best practices to note while doing this much major version upgrade
>
First, update to the latest patch version: 9.6.24.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgres upgrade from 9.6.9 to postgresql 16 version
@ 2024-02-16 07:57 Krishnaswamy <krishpostgresql@gmail.com>
parent: Chris Hoover <chrish@aweber.com>
0 siblings, 1 reply; 14+ messages in thread
From: Krishnaswamy @ 2024-02-16 07:57 UTC (permalink / raw)
To: Chris Hoover <chrish@aweber.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi All
As per the documentation wal_level= logical supported in 9.6 version.
Can we use logical replication in that use to upgrade.
But the issue here is *test_decoding* plugin seems to be missing in the
library..is it possible to install manually the plugin...please let me know
any refer links or steps for that....
Thanks
Krishnaswamy
On Tue, Feb 13, 2024, 9:57 PM Chris Hoover <chrish@aweber.com> wrote:
> We went from 9.0 -> v15 in a single step. We used Londiste to do the
> replication. It’s older technology, but made upgrading in a single move
> possible. DB is +2TB. We had about 1 hr of downtime between final steps
> and validation of our applications.
>
> Thanks,
>
>
> Chris Hoover
> Senior DBA
> AWeber.com
> Cell: (803) 528-2269
> Email: chrish@aweber.com
>
>
>
> On Feb 13, 2024, at 11:22 AM, Krishnaswamy <krishpostgresql@gmail.com>
> wrote:
>
> Hi All
>
> We have a postgreSQL db with version *9.6.9* version and planning to
> upgrade to latest postgresql 16 version.
>
> Database size is around 4 TB .
>
> Please suggest
> 1) options/methods to upgrade
> 2) best practices to note while doing this much major version upgrade
>
> Thanks
> Krishnaswamy
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Postgres upgrade from 9.6.9 to postgresql 16 version
@ 2024-02-16 12:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Krishnaswamy <krishpostgresql@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2024-02-16 12:33 UTC (permalink / raw)
To: Krishnaswamy <krishpostgresql@gmail.com>; Chris Hoover <chrish@aweber.com>; +Cc: pgsql-admin@lists.postgresql.org
On Fri, 2024-02-16 at 13:27 +0530, Krishnaswamy wrote:
> As per the documentation wal_level= logical supported in 9.6 version.
>
> Can we use logical replication in that use to upgrade.
>
> But the issue here is test_decoding plugin seems to be missing in the library..
> is it possible to install manually the plugin...please let me know any refer
> links or steps for that....
You cannot use logical replication with version 9.6.
test_decoding is just for testing and cannot help with logical replication.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-02-16 12:33 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-29 20:41 Re-order columns? Wells Oliver <wells.oliver@gmail.com>
2020-07-29 20:45 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-29 20:50 ` Holger Jakobs <holger@jakobs.com>
2020-07-29 20:52 ` Wells Oliver <wells.oliver@gmail.com>
2020-07-29 20:56 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-29 21:11 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-29 21:18 ` Olivier Gautherot <ogautherot@gautherot.net>
2020-07-29 21:20 ` Stephen Frost <sfrost@snowman.net>
2020-07-29 21:10 ` Olivier Gautherot <ogautherot@gautherot.net>
2024-02-13 16:22 Postgres upgrade from 9.6.9 to postgresql 16 version Krishnaswamy <krishpostgresql@gmail.com>
2024-02-13 16:27 ` Re: Postgres upgrade from 9.6.9 to postgresql 16 version Chris Hoover <chrish@aweber.com>
2024-02-16 07:57 ` Re: Postgres upgrade from 9.6.9 to postgresql 16 version Krishnaswamy <krishpostgresql@gmail.com>
2024-02-16 12:33 ` Re: Postgres upgrade from 9.6.9 to postgresql 16 version Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-13 17:26 ` Re: Postgres upgrade from 9.6.9 to postgresql 16 version Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox