Re: [pgsql-admin] "Soft-hitting" the 1600 column limit

Is there a pointer to some deeper explanation of this? It's news to me and
kind of fascinating that dropped columns don't disappear. I did this stupid
test, which obviously failed:

mydb=# create table wells.foo (col1 text, col2 text);
CREATE TABLE
mydb=# insert into wells.foo values ('a','b'),('c','d');
INSERT 0 2
mydb=# alter table wells.foo drop column col2;
ALTER TABLE
mydb=# insert into wells.foo (col1,col2) values('a','b');
ERROR: column "col2" of relation "foo" does not exist
LINE 1: insert into wells.foo (col1,col2) values('a','b');

Just curious then, in what meaningful way do dropped columns persist, what
are the reasons?

On Wed, Jun 6, 2018 at 10:13 AM, nunks <nunks(dot)lol(at)gmail(dot)com> wrote:

> Thank you all for your answers.
>
> David and Tom's answers are more than enough to feed my argument to the
> developers. =)
>
> Scott, you're right of course. My first action to reestablish their
> development database was to do a dump/restore, but by logging DML
> statements I can see the application is dropping and adding the same column
> over and over again, so it's a matter of time before it hangs. I hope
> showing this thread to the developers will make them see some sense.
>
> @Wells, don't ask me, I have no idea, hahah, but it's a small table and
> the application keeps dropping/readding the column, actually. I figure the
> developer is using some kind of crazy ORM that drops the column when he
> deletes an object property and re-adds it when he sets the property back or
> something.
>
> @Ervin, a vacuum full doesn't "fix" this behavior, since it's expected
> after all =)
>
> collimittest=# \d add_drop_text
> Table "public.add_drop_text"
> Column | Type | Collation | Nullable | Default
> --------+------+-----------+----------+---------
> fixed | text | | |
>
> collimittest=# vacuum FULL ANALYZE VERBOSE add_drop_text ;
> INFO: vacuuming "public.add_drop_text"
> INFO: "add_drop_text": found 0 removable, 0 nonremovable row versions in
> 0 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
> INFO: analyzing "public.add_drop_text"
> INFO: "add_drop_text": scanned 0 of 0 pages, containing 0 live rows and 0
> dead rows; 0 rows in sample, 0 estimated total rows
> VACUUM
> collimittest=# alter table add_drop_text add column x int;
> ERROR: tables can have at most 1600 columns
>
>
> ----------
> “Life beats down and crushes the soul and art reminds you that you have
> one.”
>
> - Stella Adler
>
> On Wed, Jun 6, 2018 at 1:57 PM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
> wrote:
>
>> He's *NOT* doing anything that requires 1600 columns--that was pretty
>> much the point of his post.
>>
>> --
>> Scott Ribe
>> scott_ribe(at)elevated-dev(dot)com
>> https://www.linkedin.com/in/scottribe/
>>
>>
>>
>> > On Jun 6, 2018, at 10:53 AM, Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>> wrote:
>> >
>> > let's just stop for a moment and talk about what you're doing that
>> requires *1600 columns* because my jaw is hitting the floor.
>>
>>
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>