Re: [HACKERS] insert throw error when year field len > 4 for timestamptz datatype
On Thu, Aug 15, 2013 at 1:08 AM, Tom Lane wrote: > Rushabh Lathia writes: > > PFA patch and share your input/suggestions. > > I think this needs review. Please add it to the next commitfest. > Done. Here is latest patch with testcase added to regression. > > regards, tom lane > Regards, Rushabh Lathia www.EnterpriseDB.com timestamptz_fix_with_testcase.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] undefined symbol: PQescapeLiteral
amul sul writes: > While testing performance of PG9.2.4 using DBT5, I am getting error in > *BrokerageHouseMain: symbol lookup error: BrokerageHouseMain: undefined > symbol: PQescapeLiteral* You're linking against a pre-9.0 copy of libpq.so. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] undefined symbol: PQescapeLiteral
Hi,
While testing performance of PG9.2.4 using DBT5, I am getting error in
/bh/bh.out file as :
*BrokerageHouseMain: symbol lookup error: BrokerageHouseMain: undefined symbol:
PQescapeLiteral*
So i tried with CPP code as follow
-- test.cpp --
#include
#include
#include
using namespace std;
int main(){
PGconn *db;
char connstring[] = "dbname=postgres";
db = PQconnectdb(connstring);
char url[] = "https://www.google.co.jp/";;
cout<< PQescapeLiteral(db, (const char *)url, (size_t) strlen(url))<<"\n";
}
-
While compilation using GCC,
gcc test.cpp
throws error :
test.cpp: In function ‘int main()’:
test.cpp:22: error: ‘PQescapeLiteral’ was not declared in this scope
Do i missing something?
Thanks & regards,
Amul Sul
Re: [HACKERS] System catalog vacuum issues
On 08/15/2013 05:06 AM, Sergey Konoplev wrote: On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov wrote: I used to use VACUUM FULL periodically to resolve the issue, but the problem arises again in 2-3 months. Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07. date| relpages | reltuples | table_len | tuple_count | tuple_percent | dead_tuple_count | dead_tuple_len | free_space | free_percent | autovacuum_count +--+---+---+-+---+--+++--+-- 2013-08-08 |39029 |109096 | 319725568 | 37950 | 1.66 |52540 |7355600 | 296440048 |92.72 | Are you sure you did "VACUUM FULL pg_attribute" on Aug 7, could you please confirm that free_percent arises from 0 to 92% in one day? Do you have some processes that intensively create tables or columns and then delete them or create them in transaction and rollback the transaction? Absolutely. Here is 1-minute statistics on this table. VACUUM FULL was done on 2013-08-16 00:35:00. There are many processes that create and drop temporary tables. time | reltuples | table_len | tuple_count | tuple_percent | dead_tuple_count | dead_len | free_space | free_percent | autovacuum_count ---+-+---+-+---+--+++--+-- 2013-08-16 00:33:01.977405+09 | 1.50021e+07 | 2211 MB | 38981 | 0.24 | 15505917 | 2070 MB| 8339 kB | 0.37 | 7463 2013-08-16 00:34:01.718696+09 | 1.50021e+07 | 2211 MB | 38875 | 0.23 | 15505952 | 2070 MB| 8349 kB | 0.37 | 7463 2013-08-16 00:35:01.570965+09 | 38875 | 5664 kB | 38875 | 93.84 | 46 | 6440 bytes | 19 kB | 0.34 | 7463 2013-08-16 00:36:01.658131+09 | 38875 | 5664 kB | 38875 | 93.84 | 46 | 6440 bytes | 19 kB | 0.34 | 7463 ... 2013-08-16 08:10:01.201473+09 | 47950 | 52 MB | 47685 | 12.22 | 318481 | 43 MB | 229 kB | 0.43 | 7493 2013-08-16 08:11:01.411891+09 | 47950 | 54 MB | 47776 | 11.86 | 329589 | 44 MB | 333 kB | 0.6 | 7493 2013-08-16 08:12:01.623495+09 | 48036 | 56 MB | 47816 | 11.47 | 343932 | 46 MB | 199 kB | 0.35 | 7495 2013-08-16 08:13:01.837192+09 | 48036 | 58 MB | 47903 | 11.11 | 356488 | 48 MB | 286 kB | 0.48 | 7495 2013-08-16 08:14:02.041228+09 | 48036 | 59 MB | 47899 | 10.82 | 366939 | 49 MB | 370 kB | 0.61 | 7495 2013-08-16 08:15:01.254325+09 | 48036 | 61 MB | 48065 | 10.61 | 376192 | 50 MB | 420 kB | 0.68 | 7495 2013-08-16 08:16:01.557785+09 | 48210 | 62 MB | 48290 | 10.36 | 386019 | 52 MB | 696 kB | 1.09 | 7496 2013-08-16 08:17:01.774188+09 | 48210 | 64 MB | 48330 | 10.14 | 392236 | 52 MB | 1188 kB | 1.82 | 7496 2013-08-16 08:18:01.977503+09 | 48210 | 65 MB | 48370 | 9.87 |79643 | 11 MB | 46 MB |70.07 | 7496 2013-08-16 08:19:01.154589+09 | 48210 | 68 MB | 48550 | 9.55 |27483 | 3757 kB| 55 MB |81.55 | 7496 2013-08-16 08:20:01.321973+09 | 48333 | 69 MB | 48694 | 9.41 |42512 | 5812 kB| 54 MB |78.83 | 7497 2013-08-16 08:21:01.48612+09 | 48333 | 69 MB | 48831 | 9.43 |43172 | 5902 kB| 54 MB |78.67 | 7497 2013-08-16 08:22:01.668103+09 | 48926 | 69 MB | 48947 | 9.46 |22677 | 3100 kB| 57 MB |82.72 | 7498 2013-08-16 08:23:01.83524+09 | 48962 | 69 MB | 48914 | 9.45 | 8655 | 1183 kB| 59 MB | 85.5 | 7499 ... 2013-08-16 10:22:01.590888+09 | 52114 | 131 MB| 52395 | 5.33 | 866015 | 116 MB | 1045 kB| 0.78 | 7550 2013-08-16 10:23:01.908792+09 | 52114 | 133 MB| 52579 | 5.29 | 560495 | 75 MB| 44 MB | 33.44 | 7550 2013-08-16 10:24:01.207538+09 | 52114 | 134 MB| 52566 | 5.22 | 222138 | 30 MB| 92 MB | 68.77 | 7550 2013-08-16 10:25:01.485565+09 | 52114 | 136 MB| 52637 | 5.17 |25493 | 3485 kB | 121 MB | 88.95
Re: [HACKERS] System catalog vacuum issues
On 08/16/2013 10:44 AM, Vlad Arkhipov wrote: On 08/15/2013 03:27 AM, Jim Nasby wrote: On 8/14/13 12:31 AM, Vlad Arkhipov wrote: I used to use VACUUM FULL periodically to resolve the issue, but the problem arises again in 2-3 months. Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07. How much non-temporary DDL do you do? It's possible that you end up with a tuple at the end of the table for a non-temporary object. One of those would stay valid for quite some time, and if you're unlucky then you'll end up with another long-lived row farther down the table, etc, etc. Depending on how frequently you're creating temp objects, autovac might not be able to keep up. Assuming that a manual vacuum doesn't take too long it might be a good idea to cron a manual vacuum (NOT FULL) of that table once a minute. Not much. 1-2 tables per day. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_ctl initdb takes options, but pg_ctl --help doesn't document them?
Taking a look at PostgreSQL HEAD today, I noticed that pg_ctl documents that pg_ctl initdb takes OPTIONS but doesn't document them (unlike for start and others). Is this intentional? -- Jon -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
Apologies, but this sub-thread got lost when I changed email accounts. I found it in a final review to make sure nothing had fallen through the cracks. Noah Misch wrote: > On Thu, Jan 24, 2013 at 01:09:28PM -0500, Noah Misch wrote: >> There's no documented support for table constraints on MVs, but >> UNIQUE constraints are permitted: >> >> [local] test=# alter materialized view mymv add unique (c); >> ALTER MATERIALIZED VIEW Fix pushed. > Also, could you explain the use of RelationCacheInvalidateEntry() > in ExecRefreshMatView()? CacheInvalidateRelcacheByRelid() > followed by CommandCounterIncrement() is the typical pattern; > this is novel. I suspect, though, neither is necessary now that > the relcache does not maintain populated status based on a fork > size reading. Yeah, that was part of the attempt to support unlogged materialized views while also not returning bogus results if the view had not been populated, using heap file size. I agree that this line can just come out. If there are no objections real soon now, I will remove it in master and the 9.3 branch before the release candidate. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 regression
> Where are we on this issue? I've been able to replicate it pretty easily with PostgreSQL and continue to look into it. I've contacted Theodore Ts'o and have gotten some useful information, however I'm unable to replicate the behavior with the test program (even one that's been modified). What I've learned is: - XLogWrite appears to take approx. 2.5 times longer when writing to a file allocated with posix_fallocate, but only the first time the file contents are overwritten. This is partially explained by how ext4 handles extents and uninitialized data, but 2.5x is MUCH more expensive than anticipated or expected here. - Writing zeroes to a file allocated with posix_fallocate (essentially adding a posix_fallocate step before the usual write-zeroes-in-a-loop approach) not only doesn't seem to hurt performance, it seems to help or at least have parity, *and* the space is guaranteed to exist on disk. At the very least that seems useful. -- Jon -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: CREATE TRANSFORM syntax (was Re: [HACKERS] [PATCH] Add transforms feature)
On 08/13/2013 07:16 PM, Peter Eisentraut wrote: > My next best idea is CREATE TRANSFORM FOR hstore SERVER LANGUAGE plperl, > which preserves the overall idea but still distinguishes server from > client languages. > > Comments? My thinking is that TRANSFORMS will almost certainly be managed by installer/puppet scripts by users, so it doesn't really matter how ugly the syntax is, as long as it's unambiguous. Which is a roundabout way of saying "whatever syntax you implement is fine with me from a usability perspective". -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO request: multi-dimensional arrays in PL/pythonU
> There is no way to know how many dimensions the function expects to get > back. (float[][] doesn't actually mean anything.) So when converting > the return value back to SQL, you'd have to guess, is the first element > convertible to float (how do you know?), if not, does it support the > sequence protocol, if yes, so let's try to construct a multidimensional > array. What if the first element is a float but the second is not? > > It would be useful to have a solution for that, but it would need to be > more principled than what I just wrote. Well, PL/R is able to return multi-dim arrays. So we have some code precedent for this. Mind you, there's fewer checks required for PL/R, because like Postgres it requires each dimension of the array to have identical length and all items to be the same type. Given that, it might be easier to support this first for numpy, which also has the same restrictions. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql missing tab completion for extensions
On Thu, Aug 15, 2013 at 5:26 PM, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Magnus Hagander writes: >> > psql is missing tab completion for \dx (or more usfully, for \dx+). >> > Attached patch fixes this. >> >> > Do we consider this a bugfix and backpatch (at least to 9.3? even >> > though the problem goes back further), or head only? >> >> Sounds like a feature to me. I wouldn't object to sneaking it into >> 9.3 though. > > Agreed. Done. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql missing tab completion for extensions
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Magnus Hagander writes: > > psql is missing tab completion for \dx (or more usfully, for \dx+). > > Attached patch fixes this. > > > Do we consider this a bugfix and backpatch (at least to 9.3? even > > though the problem goes back further), or head only? > > Sounds like a feature to me. I wouldn't object to sneaking it into > 9.3 though. Agreed. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] psql missing tab completion for extensions
Magnus Hagander writes: > psql is missing tab completion for \dx (or more usfully, for \dx+). > Attached patch fixes this. > Do we consider this a bugfix and backpatch (at least to 9.3? even > though the problem goes back further), or head only? Sounds like a feature to me. I wouldn't object to sneaking it into 9.3 though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO request: multi-dimensional arrays in PL/pythonU
On Wed, Aug 14, 2013 at 9:34 PM, Peter Eisentraut wrote: > On Tue, 2013-08-13 at 14:30 -0700, Josh Berkus wrote: >> Currently PL/python has 1 dimension hardcoded for returning arrays: >> >> create or replace function nparr () >> returns float[][] >> language plpythonu >> as $f$ >> from numpy import array >> x = ((1.0,2.0),(3.0,4.0),(5.0,6.0),) >> return x >> $f$; > > There is no way to know how many dimensions the function expects to get > back. (float[][] doesn't actually mean anything.) So when converting > the return value back to SQL, you'd have to guess, is the first element > convertible to float (how do you know?), if not, does it support the > sequence protocol, if yes, so let's try to construct a multidimensional > array. What if the first element is a float but the second is not? > > It would be useful to have a solution for that, but it would need to be > more principled than what I just wrote. ndarray has a shape attribute. Perhaps they could be supported if they follow the ndarray-like protocol? (ie: have a shape attribute) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH ORDINALITY planner improvements
I wrote: > I've reworked on the patch. Attached is an updated version of the patch. In that version the code for the newly added function build_function_pathkeys() has been made more simple by using the macro INTEGER_BTREE_FAM_OID. Thanks, Best regards, Etsuro Fujita ordinality-path-20130815.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql missing tab completion for extensions
psql is missing tab completion for \dx (or more usfully, for \dx+). Attached patch fixes this. Do we consider this a bugfix and backpatch (at least to 9.3? even though the problem goes back further), or head only? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ psql_extensions_complete.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
