3

I've got a table with 4 rows in it in a non-production database used for development. There are 2 varchar columns that I want to convert to bytea. I don't care about the contents so I could of course drop the columns and then add them back, but I became confused when I tried to just change the type:

alter table whatever
  alter column col_1 set data type bytea using null,
  alter column col_2 set data type bytea using null;

When I try that, the psql client just hangs. By that I mean that it just sits there giving no feedback until I eventually hit ^C and it aborts. I've tried that with a little test table and it works fine, but for some reason it doesn't work on the real table (which, really, is also just a "little test table").

The using clause doesn't seem to make a difference one way or the other; I can leave it out or give other values, and the command does the same thing.

I don't get an error, I just don't get anything. Is that what I should expect?

I'm on 9.1 on ubuntu 14.10 if it matters.

Pointy
  • 405,095
  • 59
  • 585
  • 614
  • All the relevant column values are null, by the way, and they're not in any indexes. – Pointy Apr 21 '15 at 20:39
  • Weird; I could alter the columns from `varchar` to `int` just fine, and then from `int` to `bytea` just fine after that. – Pointy Apr 21 '15 at 20:41
  • 1
    What happens if you cast a `varchar` to `bytea`? Does it produce an error, a meaningful result, or what? – Politank-Z Apr 21 '15 at 20:42
  • Check out `pg_stat_activity` and `pg_locks` when your query hangs (from a second SQL client). Most probably you have other transactions still referencing the table and your `alter table` is waiting for the exclusive lock. –  Apr 21 '15 at 20:45
  • 1
    How big is the underlying table? Do you have concurrent write load? To check for locks on the table: `SELECT * FROM pg_locks WHERE relation = whatever::regclass; ` More: https://wiki.postgresql.org/wiki/Lock_Monitoring – Erwin Brandstetter Apr 21 '15 at 20:52
  • @a_horse_with_no_name that kind-of makes sense; however why did it let me alter the type to `int`? Well I guess I could have shut down the server that'd have the database open without thinking about it. Your explanation makes sense and is pleasing because it's not weird :) – Pointy Apr 21 '15 at 20:52
  • Did you commit the change to type `int`? Maybe that's the transaction holding the lock. –  Apr 21 '15 at 20:54
  • @a_horse_with_no_name no what I did was issue the command from `psql` and then wait. I tried several variations, and none worked. After I posted this question, I went back and tried doing `int` instead of `bytea`, and it worked. These were just command-line `psql -c` commands. – Pointy Apr 21 '15 at 20:57
  • You may just have been lucky with `int` and no concurrent transactions interfered. I doubt you can reproduce the effect .. – Erwin Brandstetter Apr 21 '15 at 20:59
  • @ErwinBrandstetter I agree and I'm in no way inclined to attempt it :) Thanks everybody; if you feel like typing in answers I'll upvote. – Pointy Apr 21 '15 at 21:02
  • Possible duplicate of [Unable to convert PostgreSQL text column to bytea](https://stackoverflow.com/questions/19300113/unable-to-convert-postgresql-text-column-to-bytea) – Andrew Gaul Apr 12 '18 at 18:51

2 Answers2

2

I don't care about the contents

In that case, this works on an empty table:

ALTER TABLE tablename
    ALTER COLUMN colname TYPE bytea USING colname::bytea
;
YSC
  • 38,212
  • 9
  • 96
  • 149
  • 1
    This is incorrect -- you should use convert_to. See https://stackoverflow.com/questions/19300113/unable-to-convert-postgresql-text-column-to-bytea . – Andrew Gaul Apr 12 '18 at 18:52
  • @AndrewGaul From OP: _"I don't care about the contents"_. What I suggested worked for my case, and this is not incorrect. If you could provide a better answer, do it. – YSC Apr 13 '18 at 07:26
  • If you do not care about the contents then drop and re-add the column to make it obvious what you want. The suggested answer mangles the data and is dangerous. The linked SO answer correctly changes the column in place. – Andrew Gaul Apr 13 '18 at 15:20
  • @ang once again it seems you only read part of sentences. _"This worked for me on [an] **empty** table". – YSC Apr 14 '18 at 20:16
1

Simple:

Get the active locks from pg_locks:

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

Copy the pid(ex: 14210) from above result and substitute in the below command.

SELECT pg_terminate_backend('14210')
chiru
  • 789
  • 6
  • 5