[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? 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?
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
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). image002.jpg
Re: [GENERAL] pg_basebackup failing
On Jan 15, 2014 12:07 PM, Sameer Kumar sameer.ku...@ashnik.com 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?
On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras ivo...@freebsd.org 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?
On 15/01/2014 12:36, Amit Langote wrote: On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras ivo...@freebsd.org 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?
On Wed, Jan 15, 2014 at 9:02 PM, Ivan Voras ivo...@freebsd.org wrote: On 15/01/2014 12:36, Amit Langote wrote: On Wed, Jan 15, 2014 at 7:39 PM, Ivan Voras ivo...@freebsd.org 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?
On 15/01/2014 13:29, Amit Langote wrote: On Wed, Jan 15, 2014 at 9:02 PM, Ivan Voras ivo...@freebsd.org 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)
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?
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]http://user/SendEmail.jtp?type=nodenode=5786458i=0 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]http://user/SendEmail.jtp?type=nodenode=5786458i=1 ) 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 herehttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5785677code=YW1iaWxhbG1jYUBnbWFpbC5jb218NTc4NTY3N3wtMTY2NzIwNTA1Mg== . NAMLhttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.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?
@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 ambilal...@gmail.comwrote: 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]http://user/SendEmail.jtp?type=nodenode=5786458i=0 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]http://user/SendEmail.jtp?type=nodenode=5786458i=1 ) 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 herehttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5785677code=YW1iaWxhbG1jYUBnbWFpbC5jb218NTc4NTY3N3wtMTY2NzIwNTA1Mg== . NAMLhttp://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.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
http://postgresql.1045698.n5.nabble.com/file/n5787214/ScreenShot.jpg 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?
On Wed, Jan 15, 2014 at 7:36 AM, ambilalmca ambilal...@gmail.com 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?
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 sanj.aggar...@gmail.com wrote: http://postgresql.1045698.n5.nabble.com/file/n5787214/ScreenShot.jpg 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
On Wed, Jan 15, 2014 at 8:05 PM, Sameer Kumar sameer.ku...@ashnik.comwrote: 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?
Ivan Voras ivo...@freebsd.org 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?
On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 ygnhz...@gmail.com 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
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?
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 amitlangot...@gmail.com On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 ygnhz...@gmail.com 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?
On Wed, Jan 15, 2014 at 4:10 AM, Gábor Farkas gabor.far...@gmail.comwrote: 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
On Wed, Jan 15, 2014 at 10:48 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Jan 15, 2014 at 1:26 AM, Ming Li mli89...@gmail.com 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?
=?GB2312?B?RmVsaXgu0Ow=?= ygnhz...@gmail.com 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
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 itishree.su...@gmail.com 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
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;i3nelems;i3++)
{
cstring_array[i3] = text_to_cstring( vals[i3]);
elog(INFO, elem %d of dim_array : %s\n,i3,cstring_array[i3]);
}
I crashes postgres because of a segfault.
Any help appreciated =)
Cheers,
Rémi-C
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.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
On 01/13/2014 01:38 PM, Francisco Olarte wrote: Hi: On Mon, Jan 13, 2014 at 5:26 PM, Tirthankar Barari tbar...@verizon.com 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
Thanks all alan On Jan 15, 2014, at 6:30 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Wed, Jan 15, 2014 at 8:05 PM, Sameer Kumar sameer.ku...@ashnik.com 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?
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?
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?
Lists li...@benjamindsmith.com 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?
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?
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?
On 01/15/2014 04:24 PM, Tom Lane wrote: Lists li...@benjamindsmith.com 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
