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

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

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

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? tha

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

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? w

Re: [GENERAL] pg_basebackup failing

> > 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). A

Re: [GENERAL] pg_basebackup failing

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

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

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

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

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

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

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 postgres

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

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 fo

[GENERAL] Londiste3 (SkyTools3)

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

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

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

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

@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

[GENERAL] pg_depend OBJID not found

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 re

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

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_st

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

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

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 o

Re: [GENERAL] pg_basebackup failing

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 (1G

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

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

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

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 > anyon

[GENERAL] Need Help to implement Proximity search feature

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

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

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.

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

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 oth

Re: [GENERAL] non-zero xmax yet visible

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 (

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

=?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 t

Re: [GENERAL] Need Help to implement Proximity search feature

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.

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

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

Re: [GENERAL] pg_depend OBJID not found

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.or

Re: [GENERAL] Index space growing even after cleanup via autovacuum in Postgres 9.2

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 thin

Re: [GENERAL] pg_basebackup failing

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 fo

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

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 buck

[GENERAL] vacuum vs pg_repack for clearing bloat?

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

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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 anybod

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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 wh

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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_reo

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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

Re: [GENERAL] vacuum vs pg_repack for clearing bloat?

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