[GENERAL] Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

2011-03-09 Thread Vlad Romascanu
Hello, I need to perform "conversions" (transcoding) between BYTEA and TEXT columns in a UTF-8 database. I searched for existing solutions and was unable to find one for 8.x or 9.x, so I cam up with something I'd like to validate with the more enlightened members of this list... Case 1: reinterp

Re: [GENERAL] Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

2011-03-10 Thread Vlad Romascanu
Aha! :) Why haven't I used convert_from() -- my ultimate abominable goal is to do an in-place migration of an SQL_ASCII database (LC_TYPE/COLLATION="C") to UTF8 (LC_TYPE/COLLATION="C"), where the string data in the SQL_ASCII database is in some encoding, say LATIN1, and where the bulk of the data

[GENERAL] converting E'C:\\something' to bytea

2011-03-16 Thread Vlad Romascanu
Hello, Is there any way of casting (reinterpreting) a varchar/text field containing arbitrary backslashes to bytea without making an escaped copy of the varchar/text first? In the examples below I am using a constant E'...' for clarity, the value normally comes from a varchar/text column in a tab

Re: [GENERAL] converting E'C:\\something' to bytea

2011-03-16 Thread Vlad Romascanu
TION and casting via the domain, assuming the lot would then behave like the aforementioned trick in the pg_convert_from implementation, but it doesn't seem to work that way. :( V. On Wed, Mar 16, 2011 at 11:51 AM, Bruce Momjian wrote: > Vlad Romascanu wrote: >> Hello,

Re: [GENERAL] converting E'C:\\something' to bytea

2011-03-16 Thread Vlad Romascanu
Lane wrote: > Bruce Momjian writes: >> Vlad Romascanu wrote: >>> Is there any way of casting (reinterpreting) a varchar/text field >>> containing arbitrary backslashes to bytea without making an escaped >>> copy of the varchar/text first? > >> Well, the

[GENERAL] One-off attempt at catalog hacking to turn bytea column into text

2011-05-10 Thread Vlad Romascanu
Hello, As a one-off attempt to change a large table's 'bytea' column to 'text' with minimal I/O (where the 'bytea' contents is already valid UTF8 and the database encoding is also UTF8, and the column is not part of any index or anything involving collation), how unsafe is the following? UPDATE p

[GENERAL] Any plans to expose publicly (via stored proc) relation_needs_vacanalyze, or some flavour of do_autovacuum?

2010-09-13 Thread Vlad Romascanu
Imagine the following sequence of events: 1. a "writer" session begins a transaction, growing the number of live tuples in several tables (e.g. via COPY) from mere tens (or hundreds) to tens of thousands of tuples, then COMMITs 2. one or more "reader" sessions perform a SELECT ... JOIN on the very

[GENERAL] Why the mismatch? {row size reported by VACUUM} vs. {table size on disk / number of rows} vs. {sum of column type sizes}

2009-11-07 Thread Vlad Romascanu
PostgreSQL 8.3.3, on Win32. I have a table with 8 fixed-size (integer-type) columns: 6 x int8, 1 x integer, 1 x int2. All columns have storage=plain. Have explicitly set FILLFACTOR=100 and WITHOUT OIDS just so there's no misunderstanding. Expected row size, based solely on data types and mi