Re: [GENERAL] Why is wal_writer_delay limited to 10s?

2014-01-15 Thread Clemens Eisserer 
Hi,

Unfortunately the question is still open - is there any technical
reason why wal_writer_delay is limited to 10s?
I am using postgresql in an embedded system and writing every 10s
burns flash at an amazing rate (a 32GB SDHC card survived 4 days with
only a few GB written).

Thank you in advance, Clemens


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

[GENERAL] how is text-equality handled in postgresql?

2014-01-15 Thread Gábor Farkas 
hi,

when i create an unique-constraint on a varchar field, how exactly
does postgresql compare the texts? i'm asking because in UNICODE there
are a lot of complexities about this..

or in other words, when are two varchars equal in postgres? when their
bytes are? or some algorithm is applied?

thanks,
gabor


-- 
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 is text-equality handled in postgresql?

2014-01-15 Thread Ivan Voras 
On 15/01/2014 10:10, Gábor Farkas wrote:
> hi,
> 
> when i create an unique-constraint on a varchar field, how exactly
> does postgresql compare the texts? i'm asking because in UNICODE there
> are a lot of complexities about this..
> 
> or in other words, when are two varchars equal in postgres? when their
> bytes are? or some algorithm is applied?

By default, it is "whatever the operating system thinks it's right".
PostgreSQL doesn't have its own collation code, it uses the OS's locale
support for this.

(which breaks on certain systems which don't have complete UTF-8 support
- I'm in favour of importing ICU at least as an optional dependancy,
similar to what the FreeBSD's patch does:
http://people.freebsd.org/~girgen/postgresql-icu/).



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] pg_basebackup failing

2014-01-15 Thread Sameer Kumar 
>
> The error you are seeing is triggered because this relation file
> exceeds MAX_TAR_MEMBER_FILELEN or 8GB for a single tar member, which
> is as well the norm for tar.


I thought PostgreSQL would break the file if it grows beyond 1GB (1GB is
segment size which one can modify while compiling). Am I missing something
or have I got it totally wrong?


> Is there a solution/workaround?
> Not use a pg_basebackup but an external backup solution or a custom
> script. Break this relation file into several pieces by doing some
> partitions on it.
> > Can I break up the referenced file somehow?
> Partitions on the parent table.
>
>
Which might have other implications on the performance if the queries do
not use Partition key in where clause.


Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD. *101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
<>

Re: [GENERAL] pg_basebackup failing

2014-01-15 Thread Magnus Hagander 
On Jan 15, 2014 12:07 PM, "Sameer Kumar"  wrote:
>>
>> The error you are seeing is triggered because this relation file
>> exceeds MAX_TAR_MEMBER_FILELEN or 8GB for a single tar member, which
>> is as well the norm for tar.
>
>
> I thought PostgreSQL would break the file if it grows beyond 1GB (1GB is
segment size which one can modify while compiling). Am I missing something
or have I got it totally wrong?
>

Yeah, how did you actually end up with a file that size? Do you have any
non standard compiler options set when you built your server?

/Magnus

Re: [GENERAL] how is text-equality handled in postgresql?

2014-01-15 Thread Amit Langote 
On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras  wrote:
> On 15/01/2014 10:10, Gábor Farkas wrote:
>> hi,
>>
>> when i create an unique-constraint on a varchar field, how exactly
>> does postgresql compare the texts? i'm asking because in UNICODE there
>> are a lot of complexities about this..
>>
>> or in other words, when are two varchars equal in postgres? when their
>> bytes are? or some algorithm is applied?
>
> By default, it is "whatever the operating system thinks it's right".
> PostgreSQL doesn't have its own collation code, it uses the OS's locale
> support for this.
>

Just to add to this, whenever strcoll() (a locale aware comparator)
says two strings are equal, postgres re-compares them using strcmp().
See following code snippet off
src/backend/utils/adt/varlena.c:varstr_cmp() -

#ifdef HAVE_LOCALE_T
if (mylocale)
result = strcoll_l(a1p, a2p, mylocale);
else
#endif
result = strcoll(a1p, a2p);

/*
 * In some locales strcoll() can claim that
nonidentical strings are
 * equal.  Believing that would be bad news for a
number of reasons,
 * so we follow Perl's lead and sort "equal" strings
according to
 * strcmp().
 */
if (result == 0)
result = strcmp(a1p, a2p);

--
Amit Langote


-- 
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 is text-equality handled in postgresql?

2014-01-15 Thread Ivan Voras 
On 15/01/2014 12:36, Amit Langote wrote:
> On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras  wrote:
>> On 15/01/2014 10:10, Gábor Farkas wrote:
>>> hi,
>>>
>>> when i create an unique-constraint on a varchar field, how exactly
>>> does postgresql compare the texts? i'm asking because in UNICODE there
>>> are a lot of complexities about this..
>>>
>>> or in other words, when are two varchars equal in postgres? when their
>>> bytes are? or some algorithm is applied?
>>
>> By default, it is "whatever the operating system thinks it's right".
>> PostgreSQL doesn't have its own collation code, it uses the OS's locale
>> support for this.
>>
> 
> Just to add to this, whenever strcoll() (a locale aware comparator)
> says two strings are equal, postgres re-compares them using strcmp().
> See following code snippet off
> src/backend/utils/adt/varlena.c:varstr_cmp() -

> /*
>  * In some locales strcoll() can claim that
> nonidentical strings are
>  * equal.  Believing that would be bad news for a
> number of reasons,
>  * so we follow Perl's lead and sort "equal" strings
> according to
>  * strcmp().
>  */
> if (result == 0)
> result = strcmp(a1p, a2p);

That seems odd and inefficient. Why would it be necessary? I would think
indexing (and other collation-sensitive operations) don't care what the
actual collation result is for arbitrary blobs of strings, as long as
they are stable?



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] how is text-equality handled in postgresql?

2014-01-15 Thread Amit Langote 
On Wed, Jan 15, 2014 at 9:02 PM, Ivan Voras  wrote:
> On 15/01/2014 12:36, Amit Langote wrote:
>> On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras  wrote:
>>> On 15/01/2014 10:10, Gábor Farkas wrote:
 hi,

 when i create an unique-constraint on a varchar field, how exactly
 does postgresql compare the texts? i'm asking because in UNICODE there
 are a lot of complexities about this..

 or in other words, when are two varchars equal in postgres? when their
 bytes are? or some algorithm is applied?
>>>
>>> By default, it is "whatever the operating system thinks it's right".
>>> PostgreSQL doesn't have its own collation code, it uses the OS's locale
>>> support for this.
>>>
>>
>> Just to add to this, whenever strcoll() (a locale aware comparator)
>> says two strings are equal, postgres re-compares them using strcmp().
>> See following code snippet off
>> src/backend/utils/adt/varlena.c:varstr_cmp() -
>
>> /*
>>  * In some locales strcoll() can claim that
>> nonidentical strings are
>>  * equal.  Believing that would be bad news for a
>> number of reasons,
>>  * so we follow Perl's lead and sort "equal" strings
>> according to
>>  * strcmp().
>>  */
>> if (result == 0)
>> result = strcmp(a1p, a2p);
>
> That seems odd and inefficient. Why would it be necessary? I would think
> indexing (and other collation-sensitive operations) don't care what the
> actual collation result is for arbitrary blobs of strings, as long as
> they are stable?
>

This is the behavior since quite some time introduced by this commit

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=656beff59033ccc5261a615802e1a85da68e8fad

--
Amit Langote


-- 
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 is text-equality handled in postgresql?

2014-01-15 Thread Ivan Voras 
On 15/01/2014 13:29, Amit Langote wrote:
> On Wed, Jan 15, 2014 at 9:02 PM, Ivan Voras  wrote:
>> On 15/01/2014 12:36, Amit Langote wrote:

>>>  * In some locales strcoll() can claim that
>>> nonidentical strings are
>>>  * equal.  Believing that would be bad news for a
>>> number of reasons,
>>>  * so we follow Perl's lead and sort "equal" strings
>>> according to
>>>  * strcmp().
>>>  */
>>> if (result == 0)
>>> result = strcmp(a1p, a2p);
>>
>> That seems odd and inefficient. Why would it be necessary? I would think
>> indexing (and other collation-sensitive operations) don't care what the
>> actual collation result is for arbitrary blobs of strings, as long as
>> they are stable?
>>
> 
> This is the behavior since quite some time introduced by this commit
> 
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=656beff59033ccc5261a615802e1a85da68e8fad

Ok, the commit comment is:

"Adjust string comparison so that only bitwise-equal strings are considered
equal: if strcoll claims two strings are equal, check it with strcmp, and
sort according to strcmp if not identical.  This fixes inconsistent
behavior under glibc's hu_HU locale, and probably under some other locales
as well.  Also, take advantage of the now-well-defined behavior to speed up
texteq, textne, bpchareq, bpcharne: they may as well just do a bitwise
comparison and not bother with strcoll at all."

... so it's just another workaround for OS specific locale issues - to
me it looks like just another reason to use ICU.



signature.asc
Description: OpenPGP digital signature

[GENERAL] Londiste3 (SkyTools3)

2014-01-15 Thread Leonardo M . Ramé 
Hi, I've installed SkyTools3 on Ubuntu 12.04 Server, and got stuck when
trying to execute "pgqadm.py" (Step 5:
http://manojadinesh.blogspot.com.ar/2012/11/skytools-londiste-replication.html).

Does anyone know if pgqadm.py was replaced in SkyTools3?. Any up-to-date
tutorial?.

Regards,
-- 
Leonardo M. Ramé
http://leonardorame.blogspot.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] How to know server status variable in postgresql?

2014-01-15 Thread ambilalmca 
because that is also one of the important counter to know postgresql server
status. thats why i am asking @sameer

Thanks & Regards,
A.Mohamed Bilal


On Sat, Jan 11, 2014 at 7:15 PM, Sameer Kumar [via PostgreSQL] <
ml-node+s1045698n5786458...@n5.nabble.com> wrote:

>
> On Fri, Jan 10, 2014 at 2:57 PM, ambilalmca <[hidden 
> email]
> > wrote:
>
>> @ sameer khan,
>
> That's Sameer Kumar :-)
>
>> i got query for all except
>
>
>
>> *Number of cached blocks read,
>>
> check pg_stat_all_tables
>
>> Number of cached index blocks read,
>
> check pg_stat_all_indexes
>
>> Number of cached sequence blocks read*.
>>
> Why do you need this info?
>
>> can you tell query for these three counters only?
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-general mailing list ([hidden 
>> email]
>> )
>>
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786458.html
>  To unsubscribe from How to know server status variable in postgresql?, click
> here
> .
> NAML
>




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5787156.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] How to know server status variable in postgresql?

2014-01-15 Thread ambilalmca 
@sameer, can you tell me the full query for that? because in pg_stat_all_tables
contains many fields. i dont know whats the correct one to get the result.

Thanks & Regards,
A.Mohamed Bilal


On Wed, Jan 15, 2014 at 10:57 AM, Mohamed Bilal wrote:

> because that is also one of the important counter to know postgresql
> server status. thats why i am asking @sameer
>
> Thanks & Regards,
> A.Mohamed Bilal
>
>
> On Sat, Jan 11, 2014 at 7:15 PM, Sameer Kumar [via PostgreSQL] <
> ml-node+s1045698n5786458...@n5.nabble.com> wrote:
>
>>
>> On Fri, Jan 10, 2014 at 2:57 PM, ambilalmca <[hidden 
>> email]
>> > wrote:
>>
>>> @ sameer khan,
>>
>> That's Sameer Kumar :-)
>>
>>> i got query for all except
>>
>>
>>
>>> *Number of cached blocks read,
>>>
>> check pg_stat_all_tables
>>
>>> Number of cached index blocks read,
>>
>> check pg_stat_all_indexes
>>
>>> Number of cached sequence blocks read*.
>>>
>> Why do you need this info?
>>
>>>  can you tell query for these three counters only?
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786228.html
>>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list ([hidden 
>>> email]
>>> )
>>>
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>>
>> --
>>  If you reply to this email, your message will be added to the
>> discussion below:
>>
>> http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5786458.html
>>  To unsubscribe from How to know server status variable in postgresql?, click
>> here
>> .
>> NAML
>>
>
>




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5787159.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] pg_depend OBJID not found

2014-01-15 Thread saggarwal 
 hi

Any help appreciated (newbie to pgsql)
I have a function f_Sanjeev and create a view
create view v_sanjeev as select * from f_sanjeev()

the view has and OBJID of  5134719 

oid reltype relname relnamespacereltype relownerrelam   
relfilenode
reltablespace   relpagesreltuples   reltoastrelid   reltoastidxid   
relhasindex
relisshared relkind relnattsrelexternid relisreplicated 
relispinned
reldiststylerelprojbaseid   relchecks   reltriggers relukeys
relfkeysrelrefs
relhasoids  relhaspkey  relhasrules relhassubclass  relacl
5134719 5134720 v_sanjeev   4497152 5134720 104 0   5134719 0   
0   0.0010  0   0   false
false   v   1   0   false   false   0   0   0   0   
0   0   0   false   false   truefalse   (null)

when I then check what dependencies there are on the Function f_Sanjeev
using
select * from pg_depend where refobjid = (select oid from pg_proc where
proname='f_sanjeev');

I get the following from pg_Depend
classid objid   objsubidrefclassid  refobjidrefobjsubid 
deptype
16412   5134721 0   12554497477 0   n

the OBJID is 5134721 which I cannot find anywhere. This number is always 1
more than the ID in the pg_class.

So I search pg_class oid=5134720 the view v_Sanjeev is found but if I search
the OID=5134721 nothing is found

this may be a known issue or I am missing a link somewhere

any help greatly appreciated

thanks



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-depend-OBJID-not-found-tp5787214.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to know server status variable in postgresql?

2014-01-15 Thread Marti Raudsepp 
On Wed, Jan 15, 2014 at 7:36 AM, ambilalmca  wrote:
> can you tell me the full query for that? because in pg_stat_all_tables 
> contains many fields. i dont know whats the correct one to get the result.

 *Number of cached blocks read,

 Number of cached index blocks read,

They're in pg_statio_all_tables, it's all documented at:
http://www.postgresql.org/docs/current/static/monitoring-stats.html

Regards,
Marti


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

[GENERAL] How are pg_operator and pg_type related with each other?

2014-01-15 Thread Felix . 徐 
Hi all,
I'm studying pg_statistic table and I find that column staop is related to
pg_operator, and different data types relate to different staop, but I
don't know where pgsql stores the mapping between pg_type and pg_operator,
does anyone have any idea about it? thanks!

Re: [GENERAL] pg_depend OBJID not found

2014-01-15 Thread Amit Langote 
On Wed, Jan 15, 2014 at 8:37 PM, saggarwal  wrote:
>  hi
>
> Any help appreciated (newbie to pgsql)
> I have a function f_Sanjeev and create a view
> create view v_sanjeev as select * from f_sanjeev()
>
> the view has and OBJID of  5134719
>
> oid reltype relname relnamespacereltype relownerrelam   
> relfilenode
> reltablespace   relpagesreltuples   reltoastrelid   reltoastidxid 
>   relhasindex
> relisshared relkind relnattsrelexternid relisreplicated 
> relispinned
> reldiststylerelprojbaseid   relchecks   reltriggers relukeys  
>   relfkeysrelrefs
> relhasoids  relhaspkey  relhasrules relhassubclass  relacl
> 5134719 5134720 v_sanjeev   4497152 5134720 104 0   5134719 0 
>   0   0.0010  0   0   false
> false   v   1   0   false   false   0   0   0   0 
>   0   0   0   false   false   truefalse   (null)
>
> when I then check what dependencies there are on the Function f_Sanjeev
> using
> select * from pg_depend where refobjid = (select oid from pg_proc where
> proname='f_sanjeev');
>
> I get the following from pg_Depend
> classid objid   objsubidrefclassid  refobjidrefobjsubid   
>   deptype
> 16412   5134721 0   12554497477 0   n
>
> the OBJID is 5134721 which I cannot find anywhere. This number is always 1
> more than the ID in the pg_class.
>
> So I search pg_class oid=5134720 the view v_Sanjeev is found but if I search
> the OID=5134721 nothing is found
>
> this may be a known issue or I am missing a link somewhere
>
> any help greatly appreciated
>
> thanks
>
>

There would be "pg_rewrite" in between.

A possibly sloppy way you could get to the view is:

select c.*

   from pg_class c, pg_rewrite rw, pg_depend d,
pg_proc p
   where c.oid = rw.ev_class and
   rw.oid = d.objid and
   d.refobjid = p.oid and
   p.proname = 'f_sanjeev';

here,

d.objid => oid of the rewrite rule
d.refobjid => oid of the function

So, the referencing object for 'f_sanjeev' is really a "rewrite rule"
(and not the view directly).

"pg_rewrite.ev_class" is the oid of the table that a given rewrite
rule is for which in this case is the view 'v_sanjeev'.

--
Amit


-- 
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_basebackup failing

2014-01-15 Thread Michael Paquier 
On Wed, Jan 15, 2014 at 8:05 PM, Sameer Kumar wrote:

> The error you are seeing is triggered because this relation file
>> exceeds MAX_TAR_MEMBER_FILELEN or 8GB for a single tar member, which
>> is as well the norm for tar.
>
>
> I thought PostgreSQL would break the file if it grows beyond 1GB (1GB is
> segment size which one can modify while compiling). Am I missing something
> or have I got it totally wrong?
>
No, you are missing nothing. Thanks for reminding :) (This is settable with
./configure --with-segsize)
So the build has been done with some uncommon option.
Regards,
-- 
Michael

Re: [GENERAL] how is text-equality handled in postgresql?

2014-01-15 Thread Tom Lane 
Ivan Voras  writes:
> On 15/01/2014 12:36, Amit Langote wrote:
>> Just to add to this, whenever strcoll() (a locale aware comparator)
>> says two strings are equal, postgres re-compares them using strcmp().

> That seems odd and inefficient. Why would it be necessary? I would think
> indexing (and other collation-sensitive operations) don't care what the
> actual collation result is for arbitrary blobs of strings, as long as
> they are stable?

If we didn't do it like this, we could not use hashing techniques for
text --- at least not unless we could find a hash function guaranteed
to yield the same values for any two strings that strcoll() claims are
equal.

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] How are pg_operator and pg_type related with each other?

2014-01-15 Thread Amit Langote 
On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐  wrote:
> Hi all,
> I'm studying pg_statistic table and I find that column staop is related to
> pg_operator, and different data types relate to different staop, but I don't
> know where pgsql stores the mapping between pg_type and pg_operator, does
> anyone have any idea about it? thanks!

Rather, different "kinds" of statistics are related to different
operators. So, "staop2" would refer to an operator suitable/applicable
for the statistics of kind "stakind2".

For example stakind2 for some attribute could be value "3" which
refers to statistic kind "histogram". In this case, staop2 for the
same attribute could refer to operator, say, "<" because this
particular operator could benefit from histogram distribution of
values. (off course, "<" may be overloaded for different types; but
point to take from this is that any "<" uses the statistic called
histogram.)

--
Amit Langote


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

[GENERAL] Need Help to implement Proximity search feature

2014-01-15 Thread itishree sukla 
Deal all,

In my project, there is a requirement to implement proximity search
feature. We are running a mobile app, for which proximity search is
require. Can any one guide me how i can achieve this using postgis, or is
there any other way i can achieve this.

We are using postgresql 9.2.

Thanks in advance .

Regards,
Itishree

Re: [GENERAL] How are pg_operator and pg_type related with each other?

2014-01-15 Thread Felix . 徐 
Hi Amit
I understand, I've read the source code of analyze.c and implemented a java
version.
Stakind1(most common values) indicates "=" operator and stakind2(histogram)
indicates "<" operator by default,
I'm wondering where I can find the corresponding operatorID of eq/lt for a
specific data type.
For example,
"=" operator for the type "varchar" is "texteq" and "<" operator for
varchar is "text_lt"
"=" operator for the type "int4" is "int4eq" and "<" operator for int4 is
"int4lt"  etc.

And another question is that how to update or insert a column with type of
"anyarray", since I want to mock the statistic data of tables, the type of
stavalues in pg_statistic is anyarray, is there any way to manually modify
that column, by some kind of function or hook?
If I ran the query like:

UPDATE pg_statistic
   SET stavalues2=array[1,2,8]
 WHERE ...

Then I will get error 42804 indicates that the expected type is anyarry
but text[] is found in the query.


Thanks very much!



2014/1/15 Amit Langote 

> On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐  wrote:
> > Hi all,
> > I'm studying pg_statistic table and I find that column staop is related
> to
> > pg_operator, and different data types relate to different staop, but I
> don't
> > know where pgsql stores the mapping between pg_type and pg_operator, does
> > anyone have any idea about it? thanks!
>
> Rather, different "kinds" of statistics are related to different
> operators. So, "staop2" would refer to an operator suitable/applicable
> for the statistics of kind "stakind2".
>
> For example stakind2 for some attribute could be value "3" which
> refers to statistic kind "histogram". In this case, staop2 for the
> same attribute could refer to operator, say, "<" because this
> particular operator could benefit from histogram distribution of
> values. (off course, "<" may be overloaded for different types; but
> point to take from this is that any "<" uses the statistic called
> histogram.)
>
> --
> Amit Langote
>

Re: [GENERAL] how is text-equality handled in postgresql?

2014-01-15 Thread Vick Khera 
On Wed, Jan 15, 2014 at 4:10 AM, Gábor Farkas wrote:

> or in other words, when are two varchars equal in postgres? when their
> bytes are? or some algorithm is applied?
>

On this topic, when I write my strings to the DB and search from the DB,
should I canonicalize them first as NKFC (or some other), or just let the
DB figure it out? In my specific case I use perl DBI with place holders to
submit my queries.

Re: [GENERAL] non-zero xmax yet visible

2014-01-15 Thread Ming Li 
On Wed, Jan 15, 2014 at 10:48 AM, Michael Paquier
 wrote:
> On Wed, Jan 15, 2014 at 1:26 AM, Ming Li  wrote:
>> I'm a little bit confused by the meaning of xmax.
>>
>> The documentation at
>> http://www.postgresql.org/docs/current/static/ddl-system-columns.html
>> says
>> "xmax
>>
>> The identity (transaction ID) of the deleting transaction, or zero for
>> an undeleted row version. It is possible for this column to be nonzero
>> in a visible row version. That usually indicates that the deleting
>> transaction hasn't committed yet, or that an attempted deletion was
>> rolled back."
>>
>> According to this, it seems a committed change should result in an
>> xmax value of zero. But a huge number of rows in our database have
>> non-zero xmax values and are still visible.
> Not exactly, this is only the case of a tuple that has been only
> inserted in a transaction. To put it in simple words an inserted row
> will have its xmin set to the current transaction ID with xman set at
> 0, and a deleted row will have its xmax updated to the transaction ID
> of the transaction that removed it.
> An updated row is the combination of a deletion and an insertion.

Well, an update statement generates a new row version. The deletion
xmax is set on the old row version and not on the new one?

If only session 1 is doing an update concurrently, xmax of the final
row version is set to 0 as expected.

=> update test_data set value = 3 where id = 1;
=> commit;
=> select xmin, xmax, id, value from test_data;
   xmin| xmax | id | value
---+--++---
 363072457 |0 |  1 | 3

If session 1 and 2 perform overlapping update transactions as in my
previous example, xmax of the final committed row version is not zero.
This is what I find confusing. Why is the new row version of an update
transaction associated with a non-zero xmax? The new row version
itself is neither deleted nor updated.

>
> The data visible from other sessions depends as well on the isolation level:
> http://www.postgresql.org/docs/current/static/transaction-iso.html
> The default, read committed, means that the query will see data
> committed by other sessions before the *query* began.
>
>> I did the following experiment with 2 sessions.
>>
>> Session 1
>>
>> => create table test_data (id int, value int);
>> => insert into test_data(id) values(1);
>> => commit;
>> => update test_data set value = 1 where id = 1;
>> => select txid_current();
>>  txid_current
>> --
>> 362938838
>>
>> Session 2
>>
>> => select xmin, xmax, id, value from test_data;
>>xmin|   xmax| id | value
>> ---+---++---
>>  362938803 | 362938838 |  1 |
> This session is using a transaction ID between 362938803 and
> 362938838, explaining why it is the one visible. You are also not
> giving all the information of session 2, a transaction began there as
> well.
>
>> => update test_data set value = 2 where id = 1;
>>
>> Session 1
>>
>> => commit;
>>
>> Session 2
>>
>> => select txid_current();
>>  txid_current
>> --
>> 362938861
>>
>> => commit;
>> => select xmin, xmax, id, value from test_data;
>>xmin|   xmax| id | value
>> ---+---++---
>>  362938861 | 362938861 |  1 | 2
> In this case what this session
>
>> So in this case, xmax is equal to xmin. I've also seen cases where
>> xmax is larger than xmin and the row is visible.
> With the isolation level read committed, changes committed by other
> sessions during a transaction are visible.
>
>> Is this an expected behavior? How shall we interpret xmax in these cases?
> This is part of how MVCC works in Postgres, xman is the transaction ID
> until when this tuple is visible for other sessions.
> Regards,
> --
> Michael


-- 
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 are pg_operator and pg_type related with each other?

2014-01-15 Thread Tom Lane 
=?GB2312?B?RmVsaXgu0Ow=?=  writes:
> I'm wondering where I can find the corresponding operatorID of eq/lt for a
> specific data type.

The ones ANALYZE uses are the members of the default btree opclass for
the datatype.  If there isn't one, it doesn't build a histogram.

> And another question is that how to update or insert a column with type of
> "anyarray", since I want to mock the statistic data of tables, the type of
> stavalues in pg_statistic is anyarray, is there any way to manually modify
> that column, by some kind of function or hook?

I don't believe that's possible from SQL; as you found out, the type
system won't allow it, and it'd be a security hole if it did (since
there wouldn't be any check that the data you store actually matches
the type of the column the pg_statistic row claims to be about).

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] Need Help to implement Proximity search feature

2014-01-15 Thread Oleg Bartunov 
check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf

On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla
 wrote:
> Deal all,
>
> In my project, there is a requirement to implement proximity search feature.
> We are running a mobile app, for which proximity search is require. Can any
> one guide me how i can achieve this using postgis, or is there any other way
> i can achieve this.
>
> We are using postgresql 9.2.
>
> Thanks in advance .
>
> Regards,
> Itishree


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

[GENERAL] reading array[text] in C extension function

2014-01-15 Thread Rémi Cura 
Hey,
I'm trying to use an array of text as input in a C function in a custom
extension.

the prototype of the sql function is  :
CREATE OR REPLACE FUNCTION pc_subset(  dimensions TEXT[])


it is called like :
pc_subset(  ARRAY['X'::text,'Y'::text])

and the C function trying to read the text array (converting it to cstring
) :

text ** vals;

char ** cstring_array;
 nelems = ARR_DIMS(arrptr)[0];
vals = (text**) ARR_DATA_PTR(arrptr);
cstring_array = (char **) pcalloc(nelems * sizeof(char * ) );
for (i3=0;i3

Re: [GENERAL] pg_depend OBJID not found

2014-01-15 Thread saggarwal 
thanks, exactly what  I needed




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-depend-OBJID-not-found-tp5787214p5787260.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Index space growing even after cleanup via autovacuum in Postgres 9.2

2014-01-15 Thread Tirthankar Barari 

On 01/13/2014 01:38 PM, Francisco Olarte wrote:

Hi:

On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari  wrote:

On 01/10/2014 07:06 AM, Francisco Olarte wrote:

Not related to your vacuum problem, but if your pattern is something
like deleting everything inserted 15 days ago you may want to think of
using partitioning or simple inheritance. Make the scheduled task
create  a new partition/child table, redirect insertions to it, drop
the oldest partition.

...

Thanks for your input. The rentention window was supposed to be variable and
dynamically changeable. So, partitioning is our last resort. Will try the
vacuum after delete instead of autovacuum.

The ability to use partition/inheritance does not depend on an static
window, but on wether your deletion pattern is as described. Supose
you do it daily. You can name your partitions / child_tables as
child_MMDD. Then to delete data that is >N days old you just build
the cutoff date, select from the system catalog relations whos name is
like child_\d\d\d\d\d\d\d\d , whose name is greater than
child_12345678 ( substitute the curoff date ) and  whose parent is the
appropiate table and drop all of them. If the retention window just
grew ( say from 15 to 20 ), the first 5 days you'll find no child
table ( same as when starting, this looks like the window grew from 0
to N ). If it shrank from 15 to 10 the first day you'll drop 10
tables. Depending on how you change the retention window you can also
delete just the appropiate partition, ignoring error in case it does
not exists ( to acomodate window growing cases, you can even use just
a drop if exists ) and when the window shrinks you can zap extra
tables manually or on the procedure which shrinks the window. The
advantage of this is avoiding system catalog query, but I personally
would use first alternative. The logic is much the same as a deleting,
just using partition drops.

Regards.

Francisco Olarte.



My tables are:

table test_metric (
id varchar(255) not null, // just auto generated uuid from app
timestamp timestamp not null,
version int4,
entity_id varchar(255) not null,
primary key (id, timestamp)
);
Indexes:
"test_metric_pkey1" PRIMARY KEY, btree (id)
"test_metric_entity_id_timestamp_key" UNIQUE CONSTRAINT, btree (entity_id, 
"timestamp")

AND

table test_metric_metrics (
metric_id varchar(255) not null,
metrics float8,
metrics_key varchar(255) not null,
primary key (metric_id, metrics_key)
);
Indexes:
"test_metric_metrics_pkey" PRIMARY KEY, btree (metric_id, metrics_key)
Foreign-key constraints:
"fk3b8e13abb63406d5" FOREIGN KEY (metric_id) REFERENCES test_metric(id)

Basically, test_metric holds the timestamp and some metadata and 
test_metric_metrics holds the set of key/value pairs for the give entity and 
timestamp in the parent table.

Is it possible to partition the second table by timestamp field from first 
table?

We are using postgres 9.2.2

Thanks,

- Tirthankar



--
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_basebackup failing

2014-01-15 Thread Alan Nilsson 
Thanks all

alan

On Jan 15, 2014, at 6:30 AM, Michael Paquier  wrote:

> 
> 
> 
> On Wed, Jan 15, 2014 at 8:05 PM, Sameer Kumar  wrote:
> The error you are seeing is triggered because this relation file
> exceeds MAX_TAR_MEMBER_FILELEN or 8GB for a single tar member, which
> is as well the norm for tar.
> 
> I thought PostgreSQL would break the file if it grows beyond 1GB (1GB is 
> segment size which one can modify while compiling). Am I missing something or 
> have I got it totally wrong?
> No, you are missing nothing. Thanks for reminding :) (This is settable with 
> ./configure --with-segsize)
> So the build has been done with some uncommon option. 
> Regards,
> -- 
> Michael

[GENERAL] Is http://www.postgresql.org/docs/9.3/static/row-estimation-examples.html up to date?

2014-01-15 Thread Torsten Förtsch 
Hi,

I am asking because ...

I have a table with

relpages  | 19164
reltuples | 194775

pg_relation_size / 8192 yields the same number as relpages. So, there is
no need to scale reltuples. Relcardinality is therefore 194775.

Statistics target is the default, 100. So, I assume each of the 100
buckets contains 1947.75 tuples.

Now, I have a timestamp column and a query for col>'2013-01-01'. There
are 27 buckets out of the 100 where col is >'2013-01-01'. The bucket
boundaries where 2013-01-01 falls into are

hist | 2013-01-08 20:48:52
hist | 2012-12-13 12:36:30

There is no / operation for INTERVAL types. So, I calculate in seconds:

select (27
+ extract('epoch' from
  '2013-01-08 20:48:52'::timestamp
   - '2013-01-01'::timestamp)
  / extract('epoch' from
'2013-01-08 20:48:52'::timestamp
- '2012-12-13 12:36:30'::timestamp))
   * 1947.75;

That results in 53170.9642980797 and would be rounded to 53171.

However, EXPLAIN shows:

  ->  Seq Scan on client  (cost=0.00..21731.03 rows=52939 width=29)
Filter: (date_joined > '2013-01-01 00:00:00'::timestamp
without time zone)

The numbers are of the same number of magnitude, but they are too
different to be rounding errors.

So, what did I wrong?

Thanks,
Torsten


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

[GENERAL] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Lists 
Our app makes extensive use of temp tables, and this causes a 
significant amount of bloat that can often only be cleared with a manual 
vacuum process. We're looking for a better way that doesn't involve 
locking, we found pg_repack and pg_reorg  and were wondering if anybody 
here could weigh in on using this instead of using vacuum?


pg_repack:
https://github.com/reorg/pg_repack

pg_reorg
http://reorg.projects.pgfoundry.org/

Thanks in advance,

Ben


--
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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Tom Lane 
Lists  writes:
> Our app makes extensive use of temp tables, and this causes a 
> significant amount of bloat that can often only be cleared with a manual 
> vacuum process. We're looking for a better way that doesn't involve 
> locking, we found pg_repack and pg_reorg  and were wondering if anybody 
> here could weigh in on using this instead of using vacuum?

A temp table is only accessible to the owning process, so if you're hoping
for vacuuming of it to happen silently in background, you'll be sadly
disappointed.  The speed advantage of a temp table come exactly from not
having to worry about concurrent access, so this isn't a tradeoff that can
easily be adjusted.

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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Andrew Sullivan 
On Wed, Jan 15, 2014 at 04:09:28PM -0800, Lists wrote:
> Our app makes extensive use of temp tables, and this causes a
> significant amount of bloat that can often only be cleared with a

Note what Tom Lane said, but why do you have bloat that can only be
cleared by vacuum?  Why not drop them or whatever (I presume they're
not going away because your connection is long lived)?  They're
supposed to be temporary, after all: cheap and disposable.

A


-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread John R Pierce 

On 1/15/2014 4:09 PM, Lists wrote:
Our app makes extensive use of temp tables, and this causes a 
significant amount of bloat that can often only be cleared with a 
manual vacuum process.


whats the persistence of these temporary tables?by design, they are 
meant for relatively short lifespan uses, and as Tom said are only 
accessible by the connection that created them, and when you're done 
with them you should drop them.


now, if you mean 'temporary table' in another sense, like regular tables 
you're using for storing temporary data, how are you cleaning this 
'temporary data' out of them?truncate should free the disk space 
they use where delete won't.



--
john r pierce  37N 122W
somewhere on the middle of the 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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Lists 

On 01/15/2014 04:24 PM, Tom Lane wrote:

Lists  writes:

Our app makes extensive use of temp tables, and this causes a
significant amount of bloat that can often only be cleared with a manual
vacuum process. We're looking for a better way that doesn't involve
locking, we found pg_repack and pg_reorg  and were wondering if anybody
here could weigh in on using this instead of using vacuum?

A temp table is only accessible to the owning process, so if you're hoping
for vacuuming of it to happen silently in background, you'll be sadly
disappointed.  The speed advantage of a temp table come exactly from not
having to worry about concurrent access, so this isn't a tradeoff that can
easily be adjusted.

regards, tom lane


Tom,

The process(es) creating the temp tables are not persistent, so the 
issue isn't trying to clean up bloat from a long running process, it's 
clearing out the cruft that results from creating temp tables, loading a 
bunch of data, then dropping the table, either explicitly or when the 
connection is terminated. This causes PG disk usage to climb without 
causing any change in pg_dump output.


I was wondering if anybody else had used either of these projects 
(pg_repack or pg_reorg, though reorg seems to be unsupported) and if so, 
how successful they had been.


-Ben


--
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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Tom Lane 
Lists  writes:
> The process(es) creating the temp tables are not persistent, so the 
> issue isn't trying to clean up bloat from a long running process, it's 
> clearing out the cruft that results from creating temp tables, loading a 
> bunch of data, then dropping the table, either explicitly or when the 
> connection is terminated. This causes PG disk usage to climb without 
> causing any change in pg_dump output.

Oh, you're worried about system catalog bloat from lots of temp tables?

I'd have thought that autovacuum could manage that, though you might need
to dial up its aggressiveness.  Not sure that things like pg_reorg can
safely be applied to system catalogs.  (That's not to say it wouldn't
work, but I'd sure test it on disposable installations first.)

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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread John R Pierce 

On 1/15/2014 5:37 PM, Lists wrote:
it's clearing out the cruft that results from creating temp tables, 
loading a bunch of data, then dropping the table,


there shoudl be zero cruft.  when the table is dropped, it should pooferate.



--
john r pierce  37N 122W
somewhere on the middle of the 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] vacuum vs pg_repack for clearing bloat?

2014-01-15 Thread Andrew Sullivan 
On Wed, Jan 15, 2014 at 05:37:27PM -0800, Lists wrote:
> it's clearing out the cruft that results from creating temp tables,
> loading a bunch of data, then dropping the table, either explicitly
> or when the connection is terminated. This causes PG disk usage to
> climb without causing any change in pg_dump output.

Oh.  You need to up your autovacuum settings for the system catalog
tables.  I built a system that did this sort of thing.  If your
autovacuum settings are correct, this will stabilize.  

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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