Re: [GENERAL] Difference between varchar and text?

[John R Pierce]

On 11/17/12 11:48 PM, Abel Abraham Camarillo Ojeda wrote:

As far as I know varchar(n) with n being less or equal than 126 cannot be
toasted, and have only one octet of overhead.

http://www.postgresql.org/docs/9.2/static/storage-toast.html


the same is true for varchar(n) with larger values of N as long as the 
actual string in the field is under 126 characeters, and the same is 
true for text if the actual string is under 126 characters.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast



--
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] user aggregate function ( calculate the average value of each index of an array column )

[Vincent Veyron]

Le jeudi 15 novembre 2012 à 19:10 +0100, Myoung-Ah KANG a écrit :
>  
> 
> I have a table with several lines as following; 
> 
>  
> 
> -  Create table mytable (type number ,  values  integer [2]) ;
> 
>  
> 
> -  Insert into mytable values (1,  ‘{ 10, 0 }’ );
> 
> -  Insert into mytable values (1,  ‘{ 20, 30 }’ );
> 
> -  Insert into mytable values (2,  ‘{30,  60}’ );
> 
>  
> 
> (In fact, the array size is very big (ex. values [1]) but the size
> is fix.   In order to simplify the example, I used an array integer
> [2]).
> 
>  
> 
>  
> 
> I would like to obtain the average value of each index of values
> column.
> 

-- create new 'expanded' table with values unnested
select id, generate_series(1,2), unnest(values) into expanded from
mytable ;

-- calculate the average for each value of the array 
-- and re-aggregate the result into an array
with t1 as (
select generate_series, avg(unnest) as average from expanded group by
generate_series order by generate_series
)
select array_agg(average) from t1;


 array_agg 
---
 {20.,30.}


>  
> 
> Is it possible to create an aggregate function which can works as
> following ? : 
> 
> (Suppose that avg_mytable is the aggregation function name.)
> 
>  
> 
> Ex1)  Select  avg_mytable (values)  from mytable ;
> 
>  
> 
> avg_mytable (values)  
> 
>    
> 
> { 20,  30}
> 
>  
> 
>  
> 
> (- Explication of the results: 20 because (10+20+30)/3 , 30 because (0
> +30+60)/3)
> 
>  
> 
>  
> 
> Ex2)  Select type, avg_mytable (values)  from mytable  Group by type ;
> 
>  
> 
> Type  |  avg_mytable (values)  
> 
> -
> 
> 1|  { 15, 15}
> 
> 2|  { 30, 60} 
> 
>  
> 
>  
> 
> I searched in the documentation for “array functions” but I could not
> find functions useful for me... 
> 
>  
> 
> Thank you so much,
> 
>  
> 
>Lea
> 
> 

-- 
Vincent Veyron
http://marica.fr/
Logiciel de gestion des assurances sinistres et des dossiers contentieux pour 
le service juridique



-- 
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] PG_TERMINATE_BACKEND not working.

[Craig Ringer]

On 11/16/2012 02:34 PM, Harry wrote:
> I am facing problem i.e. connections after execution completed are residing
> in pg_stat_activity and pg_stat_database.
> but when i am trying to kill them manually using pg_terminate_backend (All
> IDLE connections are getting killed but, others like declare, select etc.)
> are not getting killed getting result false.
> Kindly help to resolve this issue.

More detail is needed.

Pg version?

OS and version?

Exact command output?

What the queries that're not terminated are doing?

Why you're terminating them / what problem you're trying to solve?




-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



-- 
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] PG_TERMINATE_BACKEND not working.

[Chris Angelico]

On Sun, Nov 18, 2012 at 10:25 PM, Craig Ringer  wrote:
> On 11/16/2012 02:34 PM, Harry wrote:
>> I am facing problem i.e. connections after execution completed are residing
>> in pg_stat_activity and pg_stat_database.
>> but when i am trying to kill them manually using pg_terminate_backend (All
>> IDLE connections are getting killed but, others like declare, select etc.)
>> are not getting killed getting result false.
>> Kindly help to resolve this issue.
>
> More detail is needed.
>
> Pg version?
>
> OS and version?
>
> Exact command output?
>
> What the queries that're not terminated are doing?
>
> Why you're terminating them / what problem you're trying to solve?

And: Are you logged in as superuser when you try to kill them, and if
not, are the other sessions from your own user?

ChrisA


-- 
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] [BUGS] Prepared Statement Name Truncation

[David Johnston]

On Nov 18, 2012, at 2:24, Tom Lane  wrote:

> "Greg Sabino Mullane"  writes:
>>> If it's a postgres bug, what is the fix? Make the identifier max size
>>> longer?
> 
>> I'd also be in favor of this, in addition to upgrading from a NOTICE.
> 
> On the whole I'm not too excited about changing this.
> 

Then I'd agree with the OP and think the notice should go away on usage in DML; 
though it should be kept for DDL.

Can the system be made smart enough to not allow intra-schema collisions in 
addition to same schema ones?  That would seem to be the area of greatest 
concern - particularly around the usage of truncate/delete/drop.

Thought: would there be some way to flag a table like this to always require 
the use of a schema prefix to be accessed (since right now truncated names only 
have to be schema unique) in certain conditions (drop, delete, truncate)?

David J.


-- 
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] Difference between varchar and text?

[Vick Khera]

On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane  wrote:

> I'd generally recommend using "text" if you don't have any interest in
> enforcing a specific length limit.
>

Will there be any table re-writing if I do an alter to change the column
type from varchar(N) to text?  I have some really old (from 2000 and 2001)
schemas that have a metric boatload of data in them, and I'd like to remove
the old artificial limit on them.

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

[Gavan Schneider]

On Sunday, November 18, 2012 at 01:10, David Johnston wrote:

> 
> Can the system be made smart enough to not allow intra-schema
> collisions in addition to same schema ones?  That would seem to be the
> area of greatest concern - particularly around the usage of
> truncate/delete/drop.
> 
> 
My summary FWIW:
1. Potential exists for internally generated names to exceed maxlen; and
2. this maxlen is shorter than the SQL standard specification; but
3. it may not be worth the performance hit to be SQL compliant in this; with
4. potential for (undetected) name collision and unintended consequences.

May I suggest an idea from the days when memory was counted in (tiny int) kB:
represent the over maxlen identifiers "as is" up to maxlen-8 bytes
use those last 8 bytes for a 40bit hash in Base32 for disambiguation
and,
if 1:10^^12 residual collision risk is considered too high
a side list of overlong names would allow for a second hash disambiguation 
process.

Notes:
1.  The choice of Base32 encoding may be a matter of personal preference
, and, if so, I suggest using the
Crockford encoding .
(I am impressed his design is excellent, while also averting some
accidental obscenities. None of the others offer this feature :)
2.  Something along these lines, with the side table to track the
(hopefully) occasional overlong identifiers, could give standards
compliance in identifier length while still keeping the working
tables compact.
3.  (Wild speculation) There may be a "sweet spot" using even shorter
identifiers than is the case now, with full disambiguation, which
might improve overall performance.

Regards
Gavan Schneider



-- 
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] [HACKERS] Parser - Query Analyser

[Craig Ringer]

On 11/18/2012 09:57 PM, Michael Giannakopoulos wrote:
> Hi guys,
>
> Thanks for your answers. Yes, what I meant is to create a function
> that takes as an input rows of a specific relation, does something and
> returns as an output rows with different attributes. I am
> experimenting right now with the 'CREATE TYPE' and 'CREATE FUNCTION'
> commands in order to see what I can get out of them!
>

Michael:

- Please reply in-line with posts to preserve the thread of the
conversation. It makes it easier for others to understand.

- Reply to the mailing list, not directly to me. Use "Reply list" if
your mail client supports it, or "Reply to all" otherwise.

I've moved this conversation to pgsql-general. There is no sign that
it's a development issue at this point. Please follow up here, not on
pgsql-hackers.

If you wish to follow this up, please provide a DETAILED EXAMPLE showing
what you are trying to do, as I explained in my previous post. Table
definitions, ample data, desired output, detailed explanation of how you
get from the input data to the desired output, etc.list

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



-- 
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] Difference between varchar and text?

[Craig Ringer]

On 11/19/2012 12:57 AM, Vick Khera wrote:
>
>
> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane  > wrote:
>
> I'd generally recommend using "text" if you don't have any interest in
> enforcing a specific length limit.
>
>
> Will there be any table re-writing if I do an alter to change the
> column type from varchar(N) to text?  I have some really old (from
> 2000 and 2001) schemas that have a metric boatload of data in them,
> and I'd like to remove the old artificial limit on them.
That depends on the PostgreSQL version. Some changes were made to
improve that recently; from memory, it used to require rewriting, so
people would sometimes work around it with (dodgy and unsafe) hacks
directly to the system catalogs. I'm not sure if "recently" is 9.2 or 9.3.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] Difference between varchar and text?

[Vick Khera]

On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer  wrote:

>  On 11/19/2012 12:57 AM, Vick Khera wrote:
>
>
>
> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane  wrote:
>
>> I'd generally recommend using "text" if you don't have any interest in
>> enforcing a specific length limit.
>>
>
>  Will there be any table re-writing if I do an alter to change the column
> type from varchar(N) to text?  I have some really old (from 2000 and 2001)
> schemas that have a metric boatload of data in them, and I'd like to remove
> the old artificial limit on them.
>
> That depends on the PostgreSQL version. Some changes were made to improve
> that recently; from memory, it used to require rewriting, so people would
> sometimes work around it with (dodgy and unsafe) hacks directly to the
> system catalogs. I'm not sure if "recently" is 9.2 or 9.3.
>
>
I'm looking at 9.0 in production right now..  Perhaps I will just use this
as an opportunity to upgrade to 9.2 and slony 2.1. :)

Re: [GENERAL] Difference between varchar and text?

[Jeff Janes]

On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera  wrote:
> On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer  wrote:
>>
>> On 11/19/2012 12:57 AM, Vick Khera wrote:
>>
>>
>>
>> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane  wrote:
>>>
>>> I'd generally recommend using "text" if you don't have any interest in
>>> enforcing a specific length limit.
>>
>>
>> Will there be any table re-writing if I do an alter to change the column
>> type from varchar(N) to text?  I have some really old (from 2000 and 2001)
>> schemas that have a metric boatload of data in them, and I'd like to remove
>> the old artificial limit on them.
>>
>> That depends on the PostgreSQL version. Some changes were made to improve
>> that recently; from memory, it used to require rewriting, so people would
>> sometimes work around it with (dodgy and unsafe) hacks directly to the
>> system catalogs. I'm not sure if "recently" is 9.2 or 9.3.
>>
>
> I'm looking at 9.0 in production right now..  Perhaps I will just use this
> as an opportunity to upgrade to 9.2 and slony 2.1. :)
>

Just looking at the timing of the below, I'd say the optimization of
varchar(n) to text took place in 9.1.


9.0:

jjanes=# create table foo as select generate_series::text from
generate_series(1,100);
jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20);
ALTER TABLE
Time: 936.150 ms
jjanes=# alter table foo alter COLUMN generate_series set data type text;
ALTER TABLE
Time: 1093.047 ms
jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20);
ALTER TABLE
Time: 826.622 ms

9.1

jjanes=# create table foo as select generate_series::text from
generate_series(1,100);
jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20);
ALTER TABLE
Time: 996.532 ms
jjanes=# alter table foo alter COLUMN generate_series set data type text;
ALTER TABLE
Time: 4.729 ms
jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20);
ALTER TABLE
Time: 981.990 ms
jjanes=# alter table foo alter COLUMN generate_series set data type text;
ALTER TABLE
Time: 4.277 ms


-- 
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] Difference between varchar and text?

[Tom Lane]

Jeff Janes  writes:
> On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera  wrote:
>> On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer  wrote:
>>> That depends on the PostgreSQL version. Some changes were made to improve
>>> that recently; from memory, it used to require rewriting, so people would
>>> sometimes work around it with (dodgy and unsafe) hacks directly to the
>>> system catalogs. I'm not sure if "recently" is 9.2 or 9.3.

> Just looking at the timing of the below, I'd say the optimization of
> varchar(n) to text took place in 9.1.

9.1 is bright enough to optimize that specific case; 9.2 covers some
additional cases like varchar(m) to varchar(n) for m <= n.  I think also
that 9.2 avoids rebuilding indexes on the affected column in some of
these cases, but 9.1 did not.

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

[GENERAL] Fuzzystrmatch contrib module on RHEL63

[Ike Nnabugwu]

I am building an rpm package fuzzystrmatch to be installed on RHEL63 but  I
do not know  where to get the modules for it.I will appreciate any pointers
that will assist with this task.

-- 
Ike Nnabugwu

Re: [GENERAL] Fuzzystrmatch contrib module on RHEL63

[Peter Geoghegan]

On 19 November 2012 02:13, Ike Nnabugwu  wrote:
> I am building an rpm package fuzzystrmatch to be installed on RHEL63 but  I
> do not know  where to get the modules for it.I will appreciate any pointers
> that will assist with this task.

Do you mean that you need to install fuzzystrmatch? I think that you
should be able to install it by installing the package
postgresql-contrib or similar.

-- 
Peter Geoghegan   http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services


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

[GENERAL] EDB installer should check for valid %COMSPEC%

[Craig Ringer]

Hi folks

Some recent discussion on Stack Overflow has revealed another exciting
way for Windows computers to be subtly broken.

For as yet unknown reasons - probably related to security/virus scanner
software, since everything else seems to be - some Windows machines have
an invalid COMSPEC environment variable.

Two variants have been sighted in the wild:

%SystemRoot%\system32\cmd.exe;

(note the trailing semicolon), and:

C:\Windows\System32

Both will produce the delightfully helpful initdb failure:

initdb: could not execute command ""C:/Program
Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

while running:

cscript //NoLogo "C:\Program
Files\PostgreSQL\9.2/installer/server/initcluster.vbs" "NT
AUTHORITY\NetworkService" "postgres" "" "C:\Program
Files\PostgreSQL\9.2" "C:\Program Files\PostgreSQL\9.2\data" 5432 "DEFAULT"

which will exit with:

Script exit code: 1

In the one I was looking into, fixing COMSPEC in the System control
panel's Environment Variables page by removing the trailing semicolon
corrected the issue. It can be verified as correct by opening a new
command prompt after you've changed the variable (not just re-using an
existing already-open one) and running:

"%COMSPEC%" /C "echo test ok"

which should print:

test ok

not something like:

'"C:\Windows\System32\cmd.exe;"' is not recognized as an internal or
external command,
operable program or batch file."



Since I can find several reports of this spanning over a couple of
years, I'd love to see a test for this integrated into the EDB
installer. Just verify that popen() actually works before running the
initdb script, and if it doesn't, check %COMSPEC% to see if it really
points to cmd.exe .

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



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

[GENERAL] Full text search in Chinese

[Christian Jensen]

Can anyone shed any light on how I might go about implementing full text
search for Chinese?

I have heard I need to go down the NGRAM route but would love to get some
external feedback.

Our application has a per user 'ignore' list that we 'left outer join where
null' sort of deal on so I really rather not go out to Solr or anything
external if possible.

I bet there is something simple as it seems like each character is an
individual search term.

Thoughts? Hints? Am I posting in the right place?

Thanks!

-- 

*Christian Jensen*
724 Ioco Rd
Port Moody, BC V3H 2W8
+1 (778) 996-4283
christ...@jensenbox.com

Re: [GENERAL] How to list all schema names inside a PostgreSQL database through SQL

[Xiaobo Gu]

Thanks a lot.

On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič  wrote:
> Hi,
>
> On 15 November 2012 23:31, Xiaobo Gu  wrote:
>> How can I list  all schema names inside a PostgreSQL database through
>> SQL, especially thoese without any objects created inside it.
>
> Use -E psql's option:
>   -E, --echo-hiddendisplay queries that internal commands generate
>
> then you get SQL query for each internal command.
>
> The second option is to use information_schema.schemata view (this is
> works across databases)
>
>
> --
> Ondrej Ivanic
> (http://www.linkedin.com/in/ondrejivanic)


-- 
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] How to list all schema names inside a PostgreSQL database through SQL

[Xiaobo Gu]

Thanks a lot.

On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič  wrote:
> Hi,
>
> On 15 November 2012 23:31, Xiaobo Gu  wrote:
>> How can I list  all schema names inside a PostgreSQL database through
>> SQL, especially thoese without any objects created inside it.
>
> Use -E psql's option:
>   -E, --echo-hiddendisplay queries that internal commands generate
>
> then you get SQL query for each internal command.
>
> The second option is to use information_schema.schemata view (this is
> works across databases)
>
>
> --
> Ondrej Ivanic
> (http://www.linkedin.com/in/ondrejivanic)


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