Melvin Call | 27 Feb 19:32 2015
Picon

Re: Hex characters in COPY input

On Fri, Feb 27, 2015 at 12:02 PM, Adam Hooper <adam <at> adamhooper.com> wrote:
> On Fri, Feb 27, 2015 at 9:39 AM, Melvin Call <melvincall979 <at> gmail.com> wrote:
>> On 2/26/15, Vick Khera <vivek <at> khera.org> wrote:
>>> On Thu, Feb 26, 2015 at 9:50 PM, Melvin Call <melvincall979 <at> gmail.com>
>>> wrote:
>>>
>>>> I get an error "ERROR:  invalid byte sequence for
>>>> encoding "UTF8": 0xe9616c"
>>>
>>> Character code E9 is not UTF8. Don't tell Postgres you're importing UTF8 if
>>> you're not.
>>
>> Regardless, can you point me to some reading that would have clued
>> me in that e9 is not a UTF8 character? Or is the clue the fact that it was not
>> preceeded with 0x00?
>
> The error message Postgres gave you is a pretty darned good clue :).
>
> But your question has an error, and it's worth expounding a bit. 0xe9
> is not a UTF8 character because 0xe9 is not a _character_. 0xe9 is a
> _byte_. Characters are not bytes. Characters can be _encoded_ into
> bytes, and that's not the same thing.
>
> UTF-8 encoding is a bit confusing: any byte in the range [0x00-0x7f]
> represents the same character as in ASCII encoding (an extremely
> popular encoding). Any byte in the range [0x80-0xff] is the "leading
> byte" in a sequence of bytes that represents a single character.
> "Continuation" bytes are in the range [0x80-0xbf]. (Why not the range
> [0x00-0xff]? Because UTF-8 was designed to generate errors when fed
> non-UTF8 byte sequences.) The first four bits of the leading byte
(Continue reading)

Alan Nilsson | 27 Feb 18:59 2015
Picon

'missing' data on replicate

I have 4 machines in a cluster: 1 master & 2 replicates (hot standby mode) on OSX, 1 replicate (hot standby
mode) on Linux (centos6.5).  All replicates were created with pg_basebackup (+ X option).

I have a table that returns no results when queried by the primary key on the replicate running on Linux.  The
same query returns data on all other machines in the cluster.  The data is there, if I query on anything other
than the PK, I get the results I expect.

a) Is this a corrupt index?  Something entirely different?
b) How can I rebuild index on slave?  Rebuilding on master did not fix.
c) What are the possible ways of getting this way?

I’ve never seen this before and I’m not really sure how to proceed.  I can take the Linux machine offline
and rebuild the whole thing but I would rather know how this happened & fix it in place.  We are in the process
of migrating everything to Linux and this box is a test bed.  Before I commit everything to a new linux
cluster I’ld like to understand what I am seeing.

thanks
alan

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Call | 27 Feb 16:50 2015
Picon

Re: Hex characters in COPY input

On Fri, Feb 27, 2015 at 8:58 AM, Adam Hooper <adam <at> adamhooper.com> wrote:
> On Fri, Feb 27, 2015 at 9:39 AM, Melvin Call <melvincall979 <at> gmail.com> wrote:
>> On 2/26/15, Vick Khera <vivek <at> khera.org> wrote:
>
>>> Character code E9 is not UTF8. Don't tell Postgres you're importing UTF8 if
>>> you're not.
>>>
>> Thank you Vic, adding the ENCODING 'latin1' option to the COPY command worked
>> perfectly.
>>
>> Your suggestion did not even come close to crossing my mind because the
>> MySQL table and database are encoded in UTF8. I assume the conversion to latin1
>> happened because I was putting the MySQL query output into a locally stored
>> text file?
>
> The reality is confusing: when moving text around, every step of the
> process involves a character set conversion.
>
> I'm guessing your MySQL client is defaulting to character_set_client =
> 'iso-8859-1' or some-such. Depending on your client, that could be
> because your _terminal_ is set to iso-8859-1 encoding. (If you're on
> Unix, type `locale` and if you don't see lots of "UTF-8"s your
> terminal probably isn't using UTF-8.)

Ah, that makes perfect sense.

>
> But really, there are so many variables it's only an issue if you're
> trying to change the way the MySQL client is behaving. And I don't
> think this is the list for that.
(Continue reading)

Melvin Call | 27 Feb 15:41 2015
Picon

Re: Hex characters in COPY input

On 2/27/15, Adam Hooper <adam <at> adamhooper.com> wrote:
> On Thu, Feb 26, 2015 at 9:50 PM, Melvin Call <melvincall979 <at> gmail.com>
> wrote:
>
>> So my question is, how do I sanitize the hex character in the middle of a
>> word
>> to be able to copy in Montreal with an accented e? Or am I going about
>> this at
>> the wrong point?
>
> Hi Melvin,
>
> This is not a Postgres problem, and it is not a regex problem. So yes,
> you're going about it at the wrong point: you're trying to modify a
> _character_ at a time, but you _should_ be trying to modify a _byte_
> at a time. Text replacement cannot do what you want it to do.
>
> If you're on Linux or Mac, uconv will work -- for instance, `iconv
> --from-code=windows-1252 --to-code=utf-8 < input-file.txt >
> output-file.txt`
>
> Otherwise, you can use a text editor. Be sure to open the file
> properly (such that é appears) and then save it as utf-8.
>
> Alternatively, you could tell Postgres to use your existing encoding
> -- judging from the \xe9, any of "windows-1252", "iso-8859-15" or
> "iso-8859-1" will be accurate. But I always prefer my data to be
> stored as "utf-8", and you should, too.
>
> Read up on character sets here:
(Continue reading)

Clodoaldo Neto | 27 Feb 15:36 2015
Picon

Pass a URI as a pgAdmin parameter

I can connect to Heroku with psql passing a URI as generated by http://api.postgression.com:

$ psql postgres://hcnkrxukvhqiww:LryFxzcmlBoYtGtXPsvlA8H9sI <at> ec2-107-21-93-97.compute-1.amazonaws.com:5432/dearu2qcqcmi7t

I want to do the same with pgAdmin:

$ pgadmin3 -qc postgres://hcnkrxukvhqiww:LryFxzcmlBoYtGtXPsvlA8H9sI <at> ec2-107-21-93-97.compute-1.amazonaws.com:5432/dearu2qcqcmi7t

but I get the error message: Unknown token in connection string: <the passed string>

Is it possible to pass a URI as parameter to pgAdmin? Fedora 21, pgadmin3_94.x86_64 1.20.0-2.f21 from pgdg-94-fedora.repo

Regards, Clodoaldo

gmb | 27 Feb 15:07 2015
Picon

Performance on DISABLE TRIGGER

Hi all

I'm doing some maintenance - which is done quite often, never had this
problem before - which requires me to disable triggers, run some updates and
then re-enable the triggers. 
Where the whole process normally take 30 sec , it took much longer today and
I cancelled after 5 minutes.

After running the statements individually to pinpoint the issue , I
identified that the problem is on the first line.
 >> ALTER TABLE tab DISABLE TRIGGER trig;
I have not been able to run this successfully -- after 10 minutes the
connection ( to remote DB ) got severed .
Any ideas on where I can start to look for the cause of the problem ? 

Thanks

--
View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Call | 27 Feb 03:50 2015
Picon

Hex characters in COPY input

Good evening list,

I am taking the output of a MySQL query and saving it into a text file for COPY
input into a PostgreSQL database. The query gives me a list of addresses. One of
the addresses is in Montreal, and was input using the correct spelling of
Montreal where the e is an accented e. The output ends up in the text file as
Montr\xe9al, where the xe9 is a single character. When I try to copy that into
my PostgreSQL table, I get an error "ERROR:  invalid byte sequence for encoding
"UTF8": 0xe9616c", which makes sense since the hex character has not been
sanitized.

Now if I run the output through sed first, and substitute the \xe9 character for
something, say the word TEST, I end up with MontrTESTal in my input file, and
naturally that imports just fine. So this tells me that I can perform a
substitute on the hex character. But I have been pulling my hair out trying to
figure out how to substitute in a properly escaped representation of the
accented e. For instance, this: s/\(\xe9\)/U\&'\1'/g gives me MontrU&'\xe9'al
in my input file, but that just causes a different invalid byte sequence error.

So my question is, how do I sanitize the hex character in the middle of a word
to be able to copy in Montreal with an accented e? Or am I going about this at
the wrong point?

Thanks,
Melvin

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Semyon Reyfman | 26 Feb 17:54 2015
Picon

ANALYZE after CREATE TABLE AS SELECT...

Hi,

 

When I create a table with “CREATE TABLE name AS SELECT…” statement and immediately afterward use this new table in a query does it make sense to run ANALYZE on the table in between?  It appears that postgres should be able to collect very detailed statistics while emitting the table but I am not sure if this is done.

 

Thanks

 

Simon

Tim Uckun | 26 Feb 22:54 2015
Picon

Triggers and scalability in high transaction tables.

I want to write a trigger which runs semi-complicated code after each insert.  I have done some reading and from what I can gather this could cause problems because after insert triggers "don't spill to the disk" and can cause queue problems.   Many people suggest LISTEN NOTIFY but that's not going to help me because my daemons could be offline and I would lose records. 

I have two questions.

There are some hints out there that it could be possible to do asynchronous triggers based on dblink but I haven't seen any documentation or examples of this.   Is there a writeup someplace about this?

Secondly I had the idea of "partitioning" the trigger processing by partitioning the table and then putting a trigger on each child table.  This way theoretically I could be running the triggers in parallel.  Is my presumption correct here?  If I only have one table the trigger calls get queued up one at a time but if I partition my table into N tables I am running N triggers simultaneously?

Thanks.
Tong Michael | 26 Feb 21:23 2015
Picon

how to do merge in postgres ("with upsert as" not supported)


hey, guys, I came across a merge statement when I'm trying to convert stored procedures from Mysql to Postgres:

 

merge into db.ChargePeriod d

using (

    select ba.ClientID

         , ba.BillingAccountID

         , bs.BillingScheduleID

         , <at> CodeWithholdD as WithholdTypeID

      from db.ClientPartyIDConfiguration cpc

      join db.BillingAccount ba

        on ba.Deleted = 0

       and ba.ClientID = cpc.ClientID

       and ba.PartyID = cpc.PartyID

       and convert(date,getdate()) between ba.EffectiveDate and ba.ExpireDate

      join db.BillingSchedule bs

        on bs.Deleted = 0

       and bs.ClientID = ba.ClientID

       and bs.CoverageBeginDate >= ba.EffectiveDate

       and bs.CoverageBeginDate <= ba.ExpireDate

    where cpc.Deleted = 0

       and cpc.ClientID = <at> ClientID

) s on d.Deleted = 0

   and d.ClientID = s.ClientID

   and d.BillingAccountID = s.BillingAccountID

   and d.BillingScheduleID = s.BillingScheduleID

  when matched

   and isNull(d.WithholdTypeID,-1) <> isNull(s.WithholdTypeID,-1)

  then update

   set WithholdTypeID = s.WithholdTypeID

     , UpdateUser     = <at> UpdateUser

     , UpdateDate     = <at> UpdateDate

  when not matched then insert (

           ClientID

         , BillingAccountID

         , BillingScheduleID

         , WithholdTypeID

         , CreateUser

         , CreateDate

         , Deleted

         , CancelDate

       ) values (

           s.ClientID

         , s.BillingAccountID

         , s.BillingScheduleID

         , s.WithholdTypeID

         , <at> UpdateUser

         , <at> UpdateDate

         , 0

         , '9999-12-31'

       )

;

 

I saw some people use "with upsert as", but my pgAdmin version(1.8) doesn't support it. Anyone has any ideas how to do merge in postgres?

 

Thanks.


John Turner | 26 Feb 21:11 2015

range type expression syntax

Seems I'm missing a trick trying to get rangetypes working:

No problem building the string:
select concat('''[', now()::date, ',', now()::date, ']''') testrange;
           testrange
---------------------------
  '[2015-02-26,2015-02-26]'
(1 row)

Bombed-out trying to turn this into a daterange:
postgres=# select concat('''[', now()::date, ',', now()::date,  
']''')::daterange testrange;
ERROR:  malformed range literal: "'[2015-02-26,2015-02-26]'"
DETAIL:  Missing left parenthesis or bracket.

Is there a specific casting I need to apply in order to render a literal  
daterange from parameterized range elements?

/john

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Gmane