date:20091119


Pedro Doria Meunier wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

I'm trying to get the array position for a given match as thus:

This gets me the record for a given match:
SELECT *  FROM garmin_units WHERE 'L' = ANY (protocol_tag);

Ok. so far so good...
But what about getting the array position at which 'L' is stored?

Searching the Postgresql docs gives me no answer... :(
Am I on a wild goose chase?

Any insight highly appreciated ;)

BR,
Pedro Doria Meunier.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAksFP6IACgkQ2FH5GXCfxAuasgCgu/d68fkg16r1OF/2QSLnmwhW
gjYAniyQ1Mn/72323NSznxgakF4dn98k
=tWbI
-END PGP SIGNATURE-


I wrote this a while ago. If you are on 8.4 use unnest instead. And if 
you are searching thru really big arrays, use plpgsql so you can 
terminate when you find a match.


CREATE OR REPLACE FUNCTION idx(text[], text)
RETURNS int AS
$$
SELECT MIN(CASE WHEN $1[i] = $2 THEN i
ELSE NULL END)::int
FROM generate_series(array_lower($1, 1),
array_upper($1, 1)) i;
$$
LANGUAGE 'sql' IMMUTABLE STRICT;

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

[GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP

Hello,


I'm trying to send some strings that have chars outside from standar ascii, 
like çñàèó

Once I'm connected, the client and server both uses UT8Encoding.

And I'm sending all the strings encoded in UTF8.

At least the received ones are working, as I get the text exactly as it is, 
with special chars.

But when I'm trying to update a row using some of them, I'm getting an error: 

ERROR
08P01
Invalid string in message
pqformat.c
691
pq_getmstring
Invalid Front End message type 0
postgres.c
408
socketbackend
you have been disconected

How should I encode 


thanks,

regards,


r.

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

Re: [GENERAL] obtaining ARRAY position for a given match

2009/11/19 Scott Bailey arta...@comcast.net:
 Pedro Doria Meunier wrote:

 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Hi,

 I'm trying to get the array position for a given match as thus:

 This gets me the record for a given match:
 SELECT *  FROM garmin_units WHERE 'L' = ANY (protocol_tag);

 Ok. so far so good...
 But what about getting the array position at which 'L' is stored?

 Searching the Postgresql docs gives me no answer... :(
 Am I on a wild goose chase?

 Any insight highly appreciated ;)

 BR,
 Pedro Doria Meunier.
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.9 (GNU/Linux)
 Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

 iEYEARECAAYFAksFP6IACgkQ2FH5GXCfxAuasgCgu/d68fkg16r1OF/2QSLnmwhW
 gjYAniyQ1Mn/72323NSznxgakF4dn98k
 =tWbI
 -END PGP SIGNATURE-

 I wrote this a while ago. If you are on 8.4 use unnest instead. And if you
 are searching thru really big arrays, use plpgsql so you can terminate when
 you find a match.

 CREATE OR REPLACE FUNCTION idx(text[], text)
 RETURNS int AS
 $$
 SELECT MIN(CASE WHEN $1[i] = $2 THEN i
 ELSE NULL END)::int
 FROM generate_series(array_lower($1, 1),
 array_upper($1, 1)) i;
 $$
 LANGUAGE 'sql' IMMUTABLE STRICT;


Hello

it should be little bit more effective:

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
RETURNS int AS $$
SELECT i
   FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)
  WHERE $1[i] = $2
  UNION ALL
  SELECT 0  -- return 0 as not found
  LIMIT 1; -- stop after first match
$$ LANGUAGE sql;

Regards
Pavel Stehule

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


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

Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP

2009-11-19 Thread Kovalevski Andrei


Hi

could it be that you have errors in your UTF8 string? For example you 
might use UTF16 encoding, it can explain why some characters force 
errors but others are not. Can you post here the string and its' encoded 
version?


Raimon Fernandez wrote:

Hello,


I'm trying to send some strings that have chars outside from standar ascii, 
like çñàèó

Once I'm connected, the client and server both uses UT8Encoding.

And I'm sending all the strings encoded in UTF8.

At least the received ones are working, as I get the text exactly as it is, 
with special chars.

But when I'm trying to update a row using some of them, I'm getting an error: 


ERROR
08P01
Invalid string in message
pqformat.c
691
pq_getmstring
Invalid Front End message type 0
postgres.c
408
socketbackend
you have been disconected

How should I encode 


thanks,

regards,


r.

  



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

Re: [GENERAL] obtaining ARRAY position for a given match

On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
 it should be little bit more effective:

I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only got 8.3 for testing at the
moment.  I'm also not sure why you want to return zero when you don't
find the element.  The code also exploits an implementation artifact of
PG that the zero (i.e. the RHS of your UNION ALL) will be after the
real index.

This raises a small and interesting optimization for PG, when it does
the plan it could notice that a UNION ALL followed by a LIMIT won't need
to return all rows and hence it may be better to run the quicker one
first.  Or would this end up breaking more code than it helps?

 CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
 RETURNS int AS $$
 SELECT i
FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)

Quality typo :)  ^^^

   WHERE $1[i] = $2
   UNION ALL
   SELECT 0  -- return 0 as not found
   LIMIT 1; -- stop after first match
 $$ LANGUAGE sql;

I'd do something like:

  CREATE OR REPLACE FUNCTION firstidx(anyarray, anyelement)
  RETURNS int AS $$
SELECT i FROM (
  SELECT generate_series(array_lower($1,1),array_upper($1,1))) g(i)
WHERE $1[i] = $2
LIMIT 1;
  $$ LANGUAGE sql IMMUTABLE;

You can replace the call to array_upper with some large number to check
either function's behavior with large arrays.

-- 
  Sam  http://samason.me.uk/

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

Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP

On 19/11/2009, at 17:27, Kovalevski Andrei wrote:

could it be that you have errors in your UTF8 string? For example you might
use UTF16 encoding, it can explain why some characters force errors but
others are not.

It only happens with values like àéïçñ I think UTF8 can handle this ...

Can you post here the string and its' encoded version?

Original string:
QFUpdate transactions set description='Test ValuesdÇ' where id=113

UTF:

QFUpdate transactions set description='Test ValuesdÇ' where id=113
510046557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133

It has also the header Q and the length ...

thanks,

regards,

Raimon Fernandez wrote:
Hello,

I'm trying to send some strings that have chars outside from standar ascii,
like çñàèó

Once I'm connected, the client and server both uses UT8Encoding.

And I'm sending all the strings encoded in UTF8.

At least the received ones are working, as I get the text exactly as it is,
with special chars.

But when I'm trying to update a row using some of them, I'm getting an
error:
ERROR
08P01
Invalid string in message
pqformat.c
691
pq_getmstring
Invalid Front End message type 0
postgres.c
408
socketbackend
you have been disconected

How should I encode

thanks,

regards,

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

Re: [GENERAL] obtaining ARRAY position for a given match


   FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)


Pavel,

Don't get me wrong, I enjoy coding, but I think you've taken it too far 
here ;)


Yes, definitely more effective for large arrays. Thanks. Would probably 
be a good snippet for the wiki.


Scott



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

Re: [GENERAL] obtaining ARRAY position for a given match

2009-11-19 Thread Merlin Moncure

On Thu, Nov 19, 2009 at 12:15 PM, Scott Bailey arta...@comcast.net wrote:
   FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)

 Pavel,

 Don't get me wrong, I enjoy coding, but I think you've taken it too far here
 ;)

 Yes, definitely more effective for large arrays. Thanks. Would probably be a
 good snippet for the wiki.

this was actually a pretty typical solution to dealing with arrays
until we got 'unnest()'.  See information_schema._pg_expand_array for
example.

I need this functionality quite often...maybe we could use a version
of unnest that works like that  (returns idx, elem)?  It would be a
small efficiency win over generate_series based approaches.

merlin

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

Re: [GENERAL] obtaining ARRAY position for a given match


Sam Mason wrote:

On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:

it should be little bit more effective:


I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only got 8.3 for testing at the
moment.  I'm also not sure why you want to return zero when you don't
find the element.  The code also exploits an implementation artifact of
PG that the zero (i.e. the RHS of your UNION ALL) will be after the
real index.

This raises a small and interesting optimization for PG, when it does
the plan it could notice that a UNION ALL followed by a LIMIT won't need
to return all rows and hence it may be better to run the quicker one
first.  Or would this end up breaking more code than it helps?


CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
RETURNS int AS $$
SELECT i
   FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)


Quality typo :)  ^^^


  WHERE $1[i] = $2
  UNION ALL
  SELECT 0  -- return 0 as not found
  LIMIT 1; -- stop after first match
$$ LANGUAGE sql;


I'd do something like:

  CREATE OR REPLACE FUNCTION firstidx(anyarray, anyelement)
  RETURNS int AS $$
SELECT i FROM (
  SELECT generate_series(array_lower($1,1),array_upper($1,1))) g(i)
WHERE $1[i] = $2
LIMIT 1;
  $$ LANGUAGE sql IMMUTABLE;

You can replace the call to array_upper with some large number to check
either function's behavior with large arrays.


I agree that it should return null when the item is not found. So I 
tested both and Sam is correct. His function performs the same whether 
there are 500 elements or 50,000.


We had an idx() function in the _int contrib module. I wonder if it 
would be useful to write this in C now that _int is deprecated?


Scott



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

Re: [GENERAL] obtaining ARRAY position for a given match

On Thu, Nov 19, 2009 at 09:46:42AM -0800, Scott Bailey wrote:
 We had an idx() function in the _int contrib module. I wonder if it 
 would be useful to write this in C now that _int is deprecated?

Is idx really the best name for this? there could be multiple
occurrences of a value in an array (i.e. it's not a set) and hence why I
used firstidx for the function name.  If it's replacing an existing
function, then compatibility is a good reason.

-- 
  Sam  http://samason.me.uk/

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

Re: [GENERAL] obtaining ARRAY position for a given match




this was actually a pretty typical solution to dealing with arrays
until we got 'unnest()'.  See information_schema._pg_expand_array for
example.


Oh I know. I was just having a laugh at the array_lover function. Now 
that I think about it, we could replace array_agg() with array_orgy() 
and unnest() with oh_crap_her_husband_is_home()... I'll probably get in 
trouble for that.


Scott

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

Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP


On 19/11/2009, at 18:13, Raimon Fernandez wrote:

 
 On 19/11/2009, at 17:27, Kovalevski Andrei wrote:
 
 Hi
 
 could it be that you have errors in your UTF8 string? For example you might 
 use UTF16 encoding, it can explain why some characters force errors but 
 others are not.
 
 It only happens with values like àéïçñ I think UTF8 can handle this ...


yes, It can handle it ...

if I send the decoding by hand in a very simple update, it works, so there's 
something with UTF8 conversion that dosn't work ...

for example, instead of sending Ç i send their equivalent in UTF8 HC3+H87 and 
it works ...

thanks,

regards,




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

Re: [GENERAL] obtaining ARRAY position for a given match

On Thu, Nov 19, 2009 at 12:43:38PM -0500, Merlin Moncure wrote:
 we could use a version
 of unnest that works like that  (returns idx, elem)?  It would be a
 small efficiency win over generate_series based approaches.

What would idx look like for multidimensional arrays?  I think PG
needs a sensible way of dealing with these before you attempt to modify
unnest.

-- 
  Sam  http://samason.me.uk/

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

Re: [GENERAL] obtaining ARRAY position for a given match

2009/11/19 Sam Mason s...@samason.me.uk:
 On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
 it should be little bit more effective:

 I'm not sure if it will be much more; when you put a set returning
 function into a FROM clause PG will always run the function to
 completion---as far as I know, but I've only got 8.3 for testing at the
 moment.

yes, but generate_series is very cheap, this is protection before not
necessary string equation.

 I'm also not sure why you want to return zero when you don't
 find the element.  The code also exploits an implementation artifact of
 PG that the zero (i.e. the RHS of your UNION ALL) will be after the
 real index.


this is only convention. Somebody like 0 or -1 as result. Somebody can
live with NULL.

 This raises a small and interesting optimization for PG, when it does
 the plan it could notice that a UNION ALL followed by a LIMIT won't need
 to return all rows and hence it may be better to run the quicker one
 first.  Or would this end up breaking more code than it helps?

 CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
 RETURNS int AS $$
 SELECT i
    FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)

 Quality typo :)                  ^^^

   WHERE $1[i] = $2
   UNION ALL
   SELECT 0  -- return 0 as not found
   LIMIT 1; -- stop after first match
 $$ LANGUAGE sql;

 I'd do something like:

  CREATE OR REPLACE FUNCTION firstidx(anyarray, anyelement)
      RETURNS int AS $$
    SELECT i FROM (
      SELECT generate_series(array_lower($1,1),array_upper($1,1))) g(i)
    WHERE $1[i] = $2
    LIMIT 1;
  $$ LANGUAGE sql IMMUTABLE;

 You can replace the call to array_upper with some large number to check
 either function's behavior with large arrays.


your code is very very exactly same as my code. First - there are
flattening stage. So if you don't use offset 0, then subselect is
transformed to select. I am not sure, if offset 0 should help here -
it have to do a materialisation (5ms for 1 items) more. This
function is relative fast:

postgres=# select idx(array(select generate_series(1,1)),1);
  idx
---
 1
(1 row)

Time: 40.070 ms

maybe - I cannot test it - there could be code

CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
RETURNS int AS $$
SELECT i
   FROM (SELECT generate_subscripts($1) as i, unnest($1) as v) s
  WHERE v = $2
  LIMIT 1;
$$ LANGUAGE sql;

but I am sure so C code should be faster

Regards
Pavel Stehule

 --
  Sam  http://samason.me.uk/

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


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

Re: [GENERAL] obtaining ARRAY position for a given match


Sam Mason wrote:

On Thu, Nov 19, 2009 at 09:46:42AM -0800, Scott Bailey wrote:
We had an idx() function in the _int contrib module. I wonder if it 
would be useful to write this in C now that _int is deprecated?


Is idx really the best name for this? there could be multiple
occurrences of a value in an array (i.e. it's not a set) and hence why I
used firstidx for the function name.  If it's replacing an existing
function, then compatibility is a good reason.


Well I used idx() because there was already a idx(int[], int) function 
with the _int contrib module. Obviously index is out of the question. 
In other languages, it is assumed you are looking for the first index. 
Some allow you to specify an offset to begin searching at. And some 
provide another function to get the last index of element.


Here is what other languages are using for similar concept.
PHP - array_search()
Python - index()
Ruby - index()/rindex()
Java - binarySearch()
JavaScript/ActionScript - indexOf()
MySQL - find_in_set()

Scott



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

[GENERAL] Possible bug with array_agg

On 8.4.0 I found that array_agg does not return a value when fed more 
than 12,000 values. (12,000 worked and 13,000 did not.)


Probably not a big deal because its not something you would typically 
do. I was testing something else and ran into it. But if there is a 
limit there, we should probably document it.


Scott Bailey

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

Re: [GENERAL] obtaining ARRAY position for a given match

On Thu, Nov 19, 2009 at 10:47:02AM -0800, Scott Bailey wrote:
 Sam Mason wrote:
 Is idx really the best name for this?

 Well I used idx() because there was already a idx(int[], int) function 
 with the _int contrib module.

I don't remember ever using that before, hence my question.

 In other languages, it is assumed you are looking for the first index. 

Huh, they seem to don't they, even my old stalwart of pedantry, Haskell,
follows form here.  Not sure why I'd never noticed before, idx is
looking more and more sensible!

-- 
  Sam  http://samason.me.uk/

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

Re: [GENERAL] obtaining ARRAY position for a given match


 CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
 RETURNS int AS $$
 SELECT i
   FROM (SELECT generate_subscripts($1) as i, unnest($1) as v) s
  WHERE v = $2
  LIMIT 1;
 $$ LANGUAGE sql;


there is bug

correct is

create or replace function idx(anyarray, anyelement)
returns int as $$
select i
   from (select generate_subscripts($1,1) i, unnest($1) v) s
  where v = $2
  limit 1
$$ language sql;

and it is 5% faster than older version

on integer array on my eeepc

Regards
Pavel

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

Re: [GENERAL] Possible bug with array_agg

Hello

2009/11/19 Scott Bailey arta...@comcast.net:
 On 8.4.0 I found that array_agg does not return a value when fed more than
 12,000 values. (12,000 worked and 13,000 did not.)


can you send a query?

postgres=# create table f(a int);
CREATE TABLE
postgres=# insert into f select * from generate_series(1,10);
INSERT 0 10
postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
 count

 10
(1 row)

it's look well

Regards
Pavel Stehule


 Probably not a big deal because its not something you would typically do. I
 was testing something else and ran into it. But if there is a limit there,
 we should probably document it.

 Scott Bailey

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


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

Re: [GENERAL] Possible bug with array_agg

2009-11-19 Thread Tom Lane

Scott Bailey arta...@comcast.net writes:
 On 8.4.0 I found that array_agg does not return a value when fed more 
 than 12,000 values. (12,000 worked and 13,000 did not.)

What do you mean by does not return a value?

In general, giving a specific test case is a lot more helpful than
this sort of vague report.  Whatever you're seeing could easily
depend on the specific data you're aggregating, for example.

regards, tom lane

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

Re: [GENERAL] Possible bug with array_agg


Pavel Stehule wrote:

Hello

2009/11/19 Scott Bailey arta...@comcast.net:

On 8.4.0 I found that array_agg does not return a value when fed more than
12,000 values. (12,000 worked and 13,000 did not.)



can you send a query?

postgres=# create table f(a int);
CREATE TABLE
postgres=# insert into f select * from generate_series(1,10);
INSERT 0 10
postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
 count

 10
(1 row)

it's look well


As you were. Above worked for me as well. It appears to be a problem 
with pgAdmin.


-- Returns nothing
SELECT a
FROM (
 select array_agg(a) a  from f
) s

-- Works
SELECT a
FROM (
 select array_agg(a) a  from f
 WHERE a  12000
) s

-- Returns nothing
SELECT a
FROM (
 select array_agg(a) a  from f
 WHERE a  13000
) s

When I ran in different client all queries returned results.

Scott

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

Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP

2009-11-19 Thread Kovalevski Andrei


Hi,

the string is ok, but the problem is inside the message. The length of 
the message is incorrect:


your message:

5100*46*557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133

it should be:

5100*45*557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133


Raimon Fernandez wrote:

On 19/11/2009, at 18:13, Raimon Fernandez wrote:

  

On 19/11/2009, at 17:27, Kovalevski Andrei wrote:



Hi

could it be that you have errors in your UTF8 string? For example you might use 
UTF16 encoding, it can explain why some characters force errors but others are 
not.
  

It only happens with values like àéïçñ I think UTF8 can handle this ...




yes, It can handle it ...

if I send the decoding by hand in a very simple update, it works, so there's 
something with UTF8 conversion that dosn't work ...

for example, instead of sending Ç i send their equivalent in UTF8 HC3+H87 and 
it works ...

thanks,

regards,

Re: [GENERAL] Possible bug with array_agg

2009-11-19 Thread Guillaume Lelarge

Le jeudi 19 novembre 2009 à 20:45:36, Scott Bailey a écrit :
 Pavel Stehule wrote:
  Hello
 
  2009/11/19 Scott Bailey arta...@comcast.net:
  On 8.4.0 I found that array_agg does not return a value when fed more
  than 12,000 values. (12,000 worked and 13,000 did not.)
 
  can you send a query?
 
  postgres=# create table f(a int);
  CREATE TABLE
  postgres=# insert into f select * from generate_series(1,10);
  INSERT 0 10
  postgres=# select count(*) from (select unnest(array_agg(a)) from f) s;
   count
  
   10
  (1 row)
 
  it's look well
 
 As you were. Above worked for me as well. It appears to be a problem
 with pgAdmin.
 
 -- Returns nothing
 SELECT a
 FROM (
   select array_agg(a) a  from f
 ) s
 
 -- Works
 SELECT a
 FROM (
   select array_agg(a) a  from f
   WHERE a  12000
 ) s
 
 -- Returns nothing
 SELECT a
 FROM (
   select array_agg(a) a  from f
   WHERE a  13000
 ) s
 

I'm not sure which release you use, but it works for me (1.10 and 1.11). The 
result of the first query is badly displayed (remember that it tries to 
display an array of 10 integers), but the query returns something that 
pgAdmin tries to display. The others work too but the display is much better.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

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

Re: [GENERAL] Issues with Redhat 4 Postgresql 8.4 and support of libxml

2009-11-19 Thread Joshua D. Drake

On Thu, 2009-11-19 at 12:05 -0800, ar...@esri.com wrote:
 What are the issues with Redhat 4 in terms of supporting libxml? I build 
 PostgreSQL 8.4 with --with-libxml option but still getting the following 
 error.

You probably need to tell postgresql to compile against the specific new
version of libxml that you built from source.

 
 STATEMENT:  INSERT INTO test VALUES (1, 'fooabc/foo');
 ERROR:  unsupported XML feature
 LINE 1: INSERT INTO test VALUES (1, 'fooabc/foo');
 ^
 DETAIL:  This functionality requires the server to be built with libxml 
 support.
 HINT:  You need to rebuild PostgreSQL using --with-libxml.
 
 This is what I have done.
 
 1- ./configure --prefix=/net/volks/volks2/pg84 --with-libxml 
 2- gmake
 3- gmake install
 
 This is where I have my libxml2 lib
 ls -l /usr/lib/libxml*.so*
 lrwxrwxrwx  1 root root25 Nov 19 11:08 /usr/lib/libxml2.so - 
 /usr/local/lib/libxml2.so
 lrwxrwxrwx  1 root root27 Nov 19 11:08 /usr/lib/libxml2.so.2 - 
 /usr/local/lib/libxml2.so.2
 lrwxrwxrwx  1 root root20 Dec 28  2007 /usr/lib/libxmlparse.so.0 - 
 libxmlparse.so.0.1.0
 -rwxr-xr-x  1 root root 32456 Nov 15  2006 /usr/lib/libxmlparse.so.0.1.0
 lrwxrwxrwx  1 root root18 Dec 28  2007 /usr/lib/libxmltok.so.0 - 
 libxmltok.so.0.1.0
 -rwxr-xr-x  1 root root 75576 Nov 15  2006 /usr/lib/libxmltok.so.0.1.0
 
 
  ls -l /usr/local/lib/libxml2.so
 lrwxrwxrwx  1 root root 17 Nov 19 10:29 /usr/local/lib/libxml2.so - 
 libxml2.so.2.6.30
  ls -l /usr/local/lib/libxml2.so.2
 lrwxrwxrwx  1 root root 17 Nov 19 10:29 /usr/local/lib/libxml2.so.2 - 
 libxml2.so.2.6.32
 
 I built libxml2.so (version 2.6.30) from source code. 
 
 Thanks for any help. 
 
 Ale Raza.
 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


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

Re: [GENERAL] Possible bug with array_agg





I'm not sure which release you use, but it works for me (1.10 and 1.11). The 
result of the first query is badly displayed (remember that it tries to 
display an array of 10 integers), but the query returns something that 
pgAdmin tries to display. The others work too but the display is much better.





Using 1.10. It displays nothing on Windows version. Displays a bunch of 
overlapping text on Linux. Oh well, that doesn't matter, as long as 
array_agg is doing what it should.


Scott

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

Re: [GENERAL] Books

2009-11-19 Thread Chris Browne

thomas.granv...@gmail.com (Thomas Løcke) writes:
 There's a new series of PostgreSQL books available:

 PostgreSQL 8.4 Official Documentation - Volume I
 PostgreSQL 8.4 Official Documentation - Volume II

 And so on, up to volume V I think.

 Would I be supporting the PostgreSQL project by buying these, or are they just
 a compilation of the online manuals, done by some random author calling him-/
 herself The PostgreSQL Global Development Group?  :o)

Lulu.com has some books of this form...

http://www.lulu.com/product/e-book/postgresql-84-official-documentation---volume-i-the-sql-language/5912706#detailsSection

Looking at publishing information:

ISBN978-1-59682-163-7
Publisher   Fultus Corporation
LanguageEnglish
Publication Date 8 January 2009
Format  Adobe Encrypted PDF

Apparently this is a way of providing proprietary encrypted
documentation...

While I'd be fairly prepared to pay something for a printed copy of the
documentation (and would not be loathe to have someone profit from the
service of publishing it for me), I don't think I'll be buying encrypted
PDF files from anyone just now!
-- 
(format nil ~...@~s cbbrowne acm.org)
http://linuxfinances.info/info/slony.html
Bother, said Pooh, as he deleted his root directory.

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

Re: [GENERAL] Encoding using the Frontend/Backend Protocol TCP/IP


On 19/11/2009, at 21:21, Kovalevski Andrei wrote:

 Hi,
 
 the string is ok, but the problem is inside the message. The length of the 
 message is incorrect:
 
 your message:
 510046557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133
 it should be:
 510045557064617465207472616E73616374696F6E7320736574206465736372697074696F6E3D27546573742056616C75657364C387272077686572652069643D313133

ok, thanks.

Finally it's working, there was a mistake from my part sending the encoding ...

:-)

regards,

raimon

Re: [GENERAL] Books

2009-11-19 Thread Greg Smith


Thomas Løcke wrote:


Would I be supporting the PostgreSQL project by buying these, or are 
they just a compilation of the online manuals, done by some random 
author calling him-/herself The PostgreSQL Global Development 
Group?  :o)
They're a printout of the manuals that come with the database.  As you 
can see at http://www.linbrary.com/postgresql/840/index.html Fultus 
Corporation is not affiliated with

The PostgreSQL Global Development Group.

--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


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

Re: [GENERAL] Issues with Redhat 4 Postgresql 8.4 and support of libxml

2009-11-19 Thread Devrim GÜNDÜZ

On Thu, 2009-11-19 at 12:05 -0800, ar...@esri.com wrote:
 What are the issues with Redhat 4 in terms of supporting libxml? I
 build PostgreSQL 8.4 with --with-libxml option but still getting the
 following error.

If you are using libxml2-devel package from Red Hat repository, it won't
work. The libxml2 version in RHEL4 is not enough for xml support.

Either upgrade to RHEL5, or compile a newer version of libxml2 on your
system and build PostgreSQL against it.

Regards,

(The PostgreSQL packager)
-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Issues with Redhat 4 Postgresql 8.4 and support of libxml

2009-11-19 Thread Devrim GÜNDÜZ

On Thu, 2009-11-19 at 15:22 -0800, ar...@esri.com wrote:
 And where I can specify this version? I already tried --with-includes
 and --with-libraries option. 

Please show us complete configure line.
-- 
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] DB terminating [on Windows]

2009-11-19 Thread Magnus Hagander

Looks like your typical backend crash of PID 9644, causing a typical
restart. So something broke the backend  - I'd start by trying to
figure that out.

Clearly, it's failing to restart after the crash, due to shared memory
issues. At the risk of repeating, this often happens with antivirus on
the machine...

//Magnus


2009/11/18 Richard Broersma richard.broer...@gmail.com:
 Magnus,  any thoughts?

 On Wed, Nov 18, 2009 at 9:53 AM, Scott Felt scott.f...@gmail.com wrote:

 Hello.  I have been having an issue with a database.  The logs consistently
 show this:

 2009-11-17 16:03:55 ESTLOG:  0: server process (PID 9644) exited with
 exit code 128
 2009-11-17 16:03:55 ESTLOCATION:  LogChildExit,
 .\src\backend\postmaster\postmaster.c:2705
 2009-11-17 16:03:55 ESTLOG:  0: terminating any other active server
 processes
 2009-11-17 16:03:55 ESTLOCATION:  HandleChildCrash,
 .\src\backend\postmaster\postmaster.c:2552
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to reconnect to
 the database and repeat your command.
 2009-11-17 16:03:55 ESTLOCATION:  quickdie,
 .\src\backend\tcop\postgres.c:2495
 2009-11-17 16:03:55 ESTWARNING:  57P02: terminating connection because of
 crash of another server process
 2009-11-17 16:03:55 ESTDETAIL:  The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2009-11-17 16:03:55 ESTHINT:  In a moment you should be able to

[GENERAL] HA-JDBC

2009-11-19 Thread David Kerr

http://ha-jdbc.sourceforge.net/

Is anyone using this? I'm considering it to handle failover for my 
Active/Passive cluster.

(using Postgres + SLES + heartbeat)

Thanks

Dave

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

[GENERAL] pgbouncer connections used

2009-11-19 Thread Scott Ribe

I have pgbouncer running fine, but was wondering: I don't see any way to get
a report of the max # of connections actually used at once? I'm pretty sure
that I currently never come close to maxing out my pool, but for planning
purposes I'd like know what the actual peak is. Did I miss something easy in
the docs?

-- 
Scott Ribe
scott_r...@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice



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

Re: [GENERAL] Possible bug with array_agg