Re: [HACKERS] like/ilike improvements
On 9/21/07, Andrew Dunstan [EMAIL PROTECTED] wrote: It applied cleanly for me. Yes, it seems something was screwed in my tree. I didn't notice you commited the patch I applied before Greg's patch. Anyway, I'm starting with a clean tree containing your fix and what Tom commited but I have to import the data again due to the catalog version bump :). New results coming soon. -- Guillaume ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On 9/19/07, Decibel! [EMAIL PROTECTED] wrote: You missed my point... what we'd want to happen is for the analyze to take place while that table had a good chance of still being in memory. It seems to be a bit too intrusive for 8.3 at this point. It might be worth looking into creating a different lock for ALTERs that actually change database page layout vs ALTERs that don't, since there's no reason you couldn't run ANALYZE while adding a PK (for example). It seems to be a good idea. As I restore a lot my test database (a real life 3.1 GB database) to test the patches on like/ilike, here are some figures to show the problem a bit more: ** 8.3 (autovacuum on as it's the default) ** Time taken by psql to load my SQL dump (the database is analyzed at the end of the restore due to autovacuum launcher launching up to 3 processes in parallel): real52m55.398s user0m37.727s sys 0m3.114s ** 8.2 (autovacuum off as it's the default) ** Time taken by psql to load the SQL dump: real15m5.032s user0m37.881s sys 0m3.040s Time to analyze the database: cityvox=# \timing Timing is on. cityvox=# ANALYZE; ANALYZE Time: 45882.049 ms So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to have the database in the same state. All settings except autovacuum are identical on both clusters. -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] like/ilike improvements
On 9/22/07, Guillaume Smet [EMAIL PROTECTED] wrote: Anyway, I'm starting with a clean tree containing your fix and what Tom commited but I have to import the data again due to the catalog version bump :). I have some good news. After Andrew's and Greg's patches, CVS HEAD is as fast as 8.2 with latin1 encoding: cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%'; numeve (0 rows) Time: 102.731 ms cityvox_latin1=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 120.399 ms So the only regression left is that from 8.2, ILIKE with UTF-8 encoding is really slower than before but it doesn't seem easy to solve (if possible). Regards, -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Guillaume Smet [EMAIL PROTECTED] writes: So a total of: 16 minutes for 8.2 compared to 53 minutes for 8.3 to have the database in the same state. Please try that experiment with all three configurations on both versions: * autovacuum off * autovacuum on, autovacuum_vacuum_cost_delay = 0 * autovacuum on, autovacuum_vacuum_cost_delay = 20 Comparing apples and oranges isn't real helpful in determining what's happening. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On 9/22/07, Tom Lane [EMAIL PROTECTED] wrote: Guillaume Smet [EMAIL PROTECTED] writes: Please try that experiment with all three configurations on both versions: * autovacuum off * autovacuum on, autovacuum_vacuum_cost_delay = 0 * autovacuum on, autovacuum_vacuum_cost_delay = 20 I'll do it during the week-end. Comparing apples and oranges isn't real helpful in determining what's happening. I'm not exactly comparing apples and oranges, I'm comparing default configuration of autovacuum for both versions. IMHO, the point is not to compare both versions but to see what we can do to improve the fact that 3 autovacuum processes analyzing the data while restoring them introduces a lot of overhead. -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] msvc, build and install with cygwin in the PATH
Hannes Eder wrote: Magnus Hagander wrote: Hannes Eder wrote: Is it worth doing this the Perl-way and using File::Find? If so, I can work an a patch for that. It's certainly cleaner that way, but I don't find it a major issue. But I'd rather see that fix than the other one. Here we go. See attached patch. Your comments are welcome. I have committed a fix that is somewhat similar to this. The Install.pm module needs some love, but that will have to wait till the next cycle. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Add function for quote_qualified_identifier?
I had some spare cycles so I went ahead and patched this.
Patch includes documentation and new regression tests. While I was in
there I also added regression tests for quote_ident(), which appeared
to be absent.
quote_literal doesn't seem to have any regression tests either, but I
decided to leave that for another patch.
With thanks to Neil Conway for his assistance on IRC.
Cheers
BJ
On 9/15/07, Bruce Momjian [EMAIL PROTECTED] wrote:
This has been saved for the 8.4 release:
Brendan Jurd wrote:
Hi hackers,
I note that we currently expose the usefulness of the quote_identifier
function to the user with quote_ident(text).
Is there any reason we shouldn't do the same with
quote_qualified_identifier?
We could just add a quote_qualified_ident(text, text) ... it would
make forming dynamic queries more convenient in databases that use
multiple schemas.
Clearly a DBA could just create this function himself in SQL (and it
wouldn't be difficult), but is that a good reason not to have it in
our standard set of functions?
Would be happy to cook up a patch for this.
Cheers,
BJ
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.397
diff -c -r1.397 func.sgml
*** doc/src/sgml/func.sgml 19 Sep 2007 03:13:57 - 1.397
--- doc/src/sgml/func.sgml 22 Sep 2007 03:07:26 -
***
*** 1276,1281
--- 1276,1284
primaryquote_ident/primary
/indexterm
indexterm
+ primaryquote_qualified_ident/primary
+/indexterm
+indexterm
primaryquote_literal/primary
/indexterm
indexterm
***
*** 1541,1546
--- 1544,1563
/row
row
+
entryliteralfunctionquote_qualified_ident/function(parameterschema/parameter
typetext/type, parameteridentifier/parameter
typetext/type)/literal/entry
+entrytypetext/type/entry
+entry
+ Return the given schema and identifier suitably quoted to be
used as a
+ fully qualified identifier in an acronymSQL/acronym
statement
+ string. Quoting is performed as for
functionquote_ident/function,
+ but parameterschema/parameter and
parameteridentifier/parameter
+ are quoted separately.
+/entry
+entryliteralquote_ident('Some schema','A table')/literal/entry
+entryliteralSome schema.A table/literal/entry
+ /row
+
+ row
entryliteralfunctionquote_literal/function(parameterstring/parameter)/literal/entry
entrytypetext/type/entry
entry
Index: src/backend/utils/adt/quote.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/quote.c,v
retrieving revision 1.22
diff -c -r1.22 quote.c
*** src/backend/utils/adt/quote.c 27 Feb 2007 23:48:08 - 1.22
--- src/backend/utils/adt/quote.c 22 Sep 2007 03:07:26 -
***
*** 46,51
--- 46,77
}
/*
+ * quote_qualified_ident -
+ *returns a properly quoted, schema-qualified identifier
+ */
+ Datum
+ quote_qualified_ident(PG_FUNCTION_ARGS)
+ {
+ text*schema = PG_GETARG_TEXT_P(0);
+ text*ident = PG_GETARG_TEXT_P(1);
+ text*result;
+ const char *quoted;
+ char*schema_s;
+ char*ident_s;
+
+ schema_s = DatumGetCString(DirectFunctionCall1(textout,
+
PointerGetDatum(schema)));
+ ident_s = DatumGetCString(DirectFunctionCall1(textout,
+
PointerGetDatum(ident)));
+
+ quoted = quote_qualified_identifier(schema_s, ident_s);
+
+ result = DatumGetTextP(DirectFunctionCall1(textin,
+
CStringGetDatum(quoted)));
+ PG_RETURN_TEXT_P(result);
+ }
+
+ /*
* quote_literal -
* returns a properly quoted literal
*
Index: src/include/catalog/pg_proc.h
===
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.471
diff -c -r1.471 pg_proc.h
*** src/include/catalog/pg_proc.h 20 Sep 2007 17:56:32
Re: [PATCHES] [HACKERS] Add function for quote_qualified_identifier?
Brendan Jurd [EMAIL PROTECTED] writes: Patch includes documentation and new regression tests. While I was in there I also added regression tests for quote_ident(), which appeared to be absent. This seems rather pointless, since it's equivalent to quote_ident(schemaname) || '.' || quote_ident(relname). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Text - C string
On 9/22/07, Tom Lane [EMAIL PROTECTED] wrote: Brendan Jurd [EMAIL PROTECTED] writes: I just noticed a couple of macros defined in src/include/tsearch/ts_utils.h: #define TextPGetCString(t) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(t))) #define CStringGetTextP(c) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(c))) I think if you look around you'll find several similar things in various contrib modules. It would make some sense to try to unify all this. I'm not particularly for making it macros in postgres.h though --- that's no help if the macros require referencing stuff in builtins.h. On grounds of code-space savings I think it might be worth making these things be simple functions declared in builtins.h; that would also make it much easier to change their implementations. You're right about finding similar things in various places. Even varlena.c has a set of these macros (PG_TEXT_GET_STR etc), but it doesn't look they've really been utilised. I'm happy to take a swing at this. Declaring in builtins.h makes sense. The thing that's got me confused at the moment is what naming convention to use for the functions. Looking in builtins.h you might get the impression that we use lower_underscore for functions that are called via fmgr, UPPER_UNDERSCORE for macros and CamelCase for ordinary internal C functions, but there are plenty of exceptions to disprove that rule. I see camel cased macros and lowercased internal functions. Camel cased identifiers sometimes start with uppercase, sometimes lowercase. So the name for the text - cstring function could be any of: text_cstr text_to_cstr textToCString TextToCString Is there any kind of authoritative naming convention I can refer to? Thanks for your time, BJ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Add function for quote_qualified_identifier?
On 9/23/07, Tom Lane [EMAIL PROTECTED] wrote: This seems rather pointless, since it's equivalent to quote_ident(schemaname) || '.' || quote_ident(relname). Yes it is, and I brought that up in the OP: I wrote: Clearly a DBA could just create this function himself in SQL (and it wouldn't be difficult), but is that a good reason not to have it in our standard set of functions? But since nobody arced up about it I thought I might as well move things along and produce a patch. Many of the functions provided by postgres are easy to write yourself. That doesn't mean they shouldn't be there. After all, there is *exactly* one way to do quote_qualified_ident. Why require every DBA who needs this functionality to go through the motions? I'll admit that it's a minor improvement, but that seems reasonable given it has a miniscule cost. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Text - C string
Brendan Jurd [EMAIL PROTECTED] writes: The thing that's got me confused at the moment is what naming convention to use for the functions. Well, almost any convention you like has some precedent somewhere in the PG code, given all the contributors over the years. Almost the only thing we actively discourage is Hungarian notation, and I think there's even some of that in some corners. Personally I would vote against something like TextPGetCString because it would look like one of the family of macros that are named FooGetBar. Maybe use text_to_cstring and cstring_to_text? It's not real important though. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
