Re: [HACKERS] RFC for adding typmods to functions
> > 4. What about functions whose output typmod should depend on the input > typmod(s)? I mentioned earlier the example that concatenation of > varchar(M) and varchar(N) should produce varchar(M+N). We could possibly > punt on this for the time being; supporting only fixed output typmods for > now doesn't obviously foreclose us from adding support for computed > typmods later. However there is still one nasty case that we cannot > push off till later: given a function that takes and returns a polymorphic > type such as anyelement, and an actual argument with a typmod (eg > numeric(2)), is the result numeric(2) or just numeric? As things stand > we would have little choice but to say the latter, because we don't know > what the function might do with the value, and there are too many real > cases where the result might not have the same typmod. But there are > also a lot of cases where you *would* wish that it has the same typmod, > and this patch raises the stakes for throwing away typmods mid-expression. > Is this okay, and if not what could we do about it? polymorphic functions should to ignore typmnod :( - with current syntax - on output. I don't believe so we are able to find any mechanism usable for non typmod types and typmod types. We could to enhance syntax for using typmod from parameters - maybe some flag like STRICT, maybe TYPMOD? Regards Pavel > > Unless we have consensus on all of these points I don't think we should > proceed with the patch. Comments? > > 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] RFC for adding typmods to functions
> > So I guess really can't get worked up about the idea of propagating > this information through the type system. Even suppose we eventually > take the steps you suggesting and make it so that varchar(30) || > varchar(40) yields varchar(70). What good is that? I see main sense in enhancing warning system in plpgsql - or other SQL PL languages. When you use % reftypes - then there are potential risk so space (variable, column) isn't well dimensioned. Pavel -- 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] actualised funcs typmod patch
2009/11/17 Dimitri Fontaine :
> Le 17 nov. 2009 à 20:33, Tom Lane a écrit :
>>> We could to talk about it now. We are not hurry. But I would to see
>>> some progress in this area in next two months. This patch is simple
>>> and doesn't create any new rules or doesn't change behave.
>>
>> What do you mean it doesn't change the behavior? It establishes a
>> specific set of behaviors for functions with non-default typmods in
>> their arguments. If we just apply whatever was the easiest thing to
>> implement, without any discussion, we are very likely to regret it
>> later.
>>
>> It might be that what you've done is all fine, but I'd like some
>> discussion and consensus on the issues. Submitting an entirely
>> documentation-free patch is not the way to establish consensus.
>
> I'll try to help there, it's not really a review any more, but still it seems
> needed. Here's what I gather the specs of Pavel's work are by quick-reading
> through his patch:
>
> /*
> + * Don't allow change of input typmodes. Any change should
> break
> + * stored casts in prepared plans.
> + */
>
> The return type now can have a non -1 typmod given.
>
> [implementation details of parameterTypmodes and allParameterTypmodes left
> out, not well understood yet, does seem to be details rather than spec level
> things]
>
> + if (rettypmod != resttypmod && rettypmod != -1)
> + ereport(ERROR,
> +
> (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
> + errmsg("return type mismatch in function declared to
> return %s",
> + format_type_with_typemod(rettype,
> rettypmod)),
> + errdetail("Actual return type is
> %s.",
> +
> format_type_with_typemod(restype, resttypmod;
>
> So you need to return a decorated value I guess, or assign it to a retval
> which is of the right type, including typmod. Declaring a retval text to
> handle a RETURNS varchar(15) won't do it.
>
>
> + /* when typmodes are different, then foerce coercion too */
> + force_coerce = declared_typmod != -1 && declared_typmod !=
> actual_typmod;
>
> So if you declare typmods they are NOT part of the polymorphism (also per
> comment upthread) but you cannot change them and there's automatic coercion
> when only the typmod mismatches. I think that's what Tom wanted to avoid
> doing (because it breaks existing code assumptions and typmod coercion is not
> well defined).
>
> Here are some tests showing either the coercion of the argument (and failures
> to do it) or the return type typmod invalidity:
> + ERROR: cannot change parameter typmod of existing function
>
> + select typmodtest('a',''); -- outside plpgsql
> + ERROR: value too long for type character varying(3)
>
> + select typmodtest('','bbb'); -- return value
> + ERROR: value too long for type character varying(6)
> + CONTEXT: PL/pgSQL function "typmodtest" while casting return value to
> function's return type
>
>
> Then a great deal of changes that makes me cry in favor of having something
> human friendly around internal catalogs representation, all this BKI stuff
> IIUC.
>
> So the bulk of it is supporting return typemod declaration. This expands to
> OUT types, which can be cool:
>
> + create or replace function typmodtest(out a numeric(5,2),out b
> numeric(5,2), out c numeric(5,2))
>
>
> Hope this helps,
Thank you very much
> --
> dim
>
> PS: about the more than one anyelement type support in functions, I'd rather
> have a nice SQLish syntax around it. My proposal was sth like this:
>
> CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y)
> RETURNS anyelement y[]
> AS $$
> ...
> $$;
I would to wait with discussion about syntax. I am expecting similar
battle like about named parameters and I thing, so this can wait. But
I am sure, so We return to this and others ideas.
Regards
Pavel
--
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] plpgsql: open for execute - add USING clause
2009/11/17 Robert Haas : > On Tue, Nov 17, 2009 at 5:34 PM, Greg Smith wrote: >> Pavel Stehule wrote: >>> >>> I don't wont to apply these patches tomorrow, I don't sending these >>> patches for last moment. If I have to wait one weak or two weeks, ok. >>> Declare it. I'll respect it. But actually I respecting all rules, what >>> I know. >> >> If you're sending stuff intended for the next CommitFest in the middle of an >> active one (which we'd prefer not to see at all but you have your own >> schedule limitations), it would be helpful if you were to label those >> patches as such. It's difficult for the rest of us to tell which of the >> ones you're generating are in response to patches that are active during >> this one, and which are intended for future review but you're just dropping >> them off now. Had your new stuff been labeled "This is for the next >> CommitFest, I'm just sending it to the list now", it would have made it >> easier on everyone else to figure out which of your messages we need to pay >> attention to and what should be ignored for now. > > This expresses my feelings on the topic exactly, and perhaps merits > inclusion in a Wiki page someplace. Maybe we need to have a wiki page > on commitfest rules & expectations. Ok, It's my mistake. I didn't would to attack anybody. I though so is sufficient information is registration in commitfest application. Patch in mailing list is one thing, but registration in second - crucial. And when commitfest is closed, then is clean, so new patches goes to next commitfest. I agree - It should frustrating - and it means some work more (for reades of mailing list). I have not a problem with labeling, when patch isn't used for current commitfest. Pavel > > ...Robert > -- 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] plpgsql: open for execute - add USING clause
2009/11/17 Joshua D. Drake : > On Tue, 2009-11-17 at 15:40 -0600, Kevin Grittner wrote: >> Pavel Stehule wrote: >> >> > I never sent these (last two) patches to THIS commitfest. Is it >> > clean? >> >> I'm sure it would be much appreciated, and help to alleviate the >> frustration and burnout of some other contributors, if you could take >> a turn at reviewing -- at least one patch each commitfest. > > In short Pavel, > > Nobody is complaining about your patches. It would just be really nice > if you could help review some existing patches in this commit fest. > Would you be willing to do so? I understand so there are missing people who can do a review. I could to help with plpgsql or psql code - or some catalog code. Pavel > > Sincerely, > > Joshua D. Drake > > > -- > PostgreSQL.org Major Contributor > Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 > Consulting, Training, Support, Custom Development, Engineering > If the world pushes look it in the eye and GRR. Then push back harder. - > Salamander > > -- 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] Rejecting weak passwords
"Albe Laurenz" wrote: > Heikki Linnakangas wrote: > > I think it would better to add an explicit "isencrypted" parameter to > > the check_password_hook function, rather than require the module to do > > isMD5 on the password. > > I agree on the second point, and I changed the patch accordingly. > Here's the latest version. Looks good. I change status of the patch to "Ready for Committer". BTW, it might not be a work for this patch, we also need to reject too long "VALID UNTIL" setting. If the password is complex, we should not use the same password for a long time. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Syntax for partitioning
Marko Tiikkaja wrote: > this looks like a mistake: > partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); Oops, it should be "p"alloc. Thanks. > Maybe we should use something like > PARTITION bar VALUES OPERATOR 0 > when the user specifies the operator? I think we could have reasonable restrictions to the operator for future optimization. Is the VALUES OPERATOR syntax too freedom? For the same reason, USING operator also might be too freedom. RANGE (and maybe also LIST) partition keys should be sortable, operator class name might be better to the option instead of any operators. i.e., PARTITION BY RANGE ( foo [ USING operator ] ) should be: PARTITION BY RANGE ( foo [ btree_ops_name ] ) If we do so, there will be no inconsistency in LESS THAN syntax because btree_ops always have < operator. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Syntax for partitioning
Jaime Casanova wrote: > > * If a table with the same name already exists when a partition > > is created, the table is re-used as partition. This behavior > > is required for pg_dump to be simple. > > i guess the table must be empty, if not we should be throw an error... > and i actually prefer some more explicit syntax for this not just > reusing a table Yeah, an explicit syntax is better. I've researched other syntax, but I cannot find any good ones. * ALTER TABLE child INHERIT parent AS PARTITION => implemenation "PARTITION is an INHERIT" is revealed to user. * ALTER PARTITION child ATTACH TO parent => child is not a partition yet at that point. * ALTER TABLE parent ADD PARTITION child => "partition" need to be a full-reserved word. Are there better idea? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] UTF8 with BOM support in psql
Andrew Dunstan wrote: > Itagaki Takahiro wrote: > > I don't want user to check the encoding of scripts before executing -- > > it is far from fail-safe. > > That's what we require in all other cases. Why should UTF8 be special? No. I didn't think about UTF-8 nor BOM in that point. I assumed we are discussing the following line: > > I'd say we can always throw an error when we find queries that contain > > multi-byte characters if no prior encoding declaration. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Syntax for partitioning
Simon Riggs wrote: > Why not just wait until we have a whole patch and then apply? "A whole patch" can be written by many contributers instead of only one person, no? I think we need to split works for partitioning into serveral parts to encourage developing it. I just did one of the parts, "syntax". Anothe patch "Partitioning option for COPY" will do a good job in the field of "INSERT". Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] UTF8 with BOM support in psql
Itagaki Takahiro wrote: I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. That's what we require in all other cases. Why should UTF8 be special? If I have a script in Latin1 and Postgres thinks it's UTF8 it will probably explode. Same for the reverse situation. Second-guessing the user strikes me as being quite as dangerous as what you're trying to cure, for all the reasons Tom outline earlier today. What is more, you will teach Windows users to rely on the client encoding being set in UTF8 scripts without their doing anything, and then when they get on another platform they will not understand why it doesn't work because the BOMs will be missing. cheers andrew -- 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] Timezones (in 8.5?)
> "hernan" == hernan gonzalez writes:
>> Perhaps the OP should explain exactly what real-world problems
>> he's trying to solve. As noted in the discussion you linked,
>> there's not a lot of enthusiasm around here for getting closer to
>> the spec's datetime handling simply because it's the spec; that
>> part of the spec is just too broken for that to be a credible
>> argument.
hernan> I'm not much interested in the compliance with the ANSI SQL
hernan> spec, I agree in this regard it is unsatisfactory (to put it
hernan> midly). But I'm also disatisfied with the current Postgresql
hernan> implementation, the types TIMESTAMP and TIMESTAMP WITH
hernan> TIMEZONE are in the middle of being SQL compliant and being
hernan> really useful. The support of timezones is really crippled
hernan> now.
Crippled how?
The example you gave is easily handled in pg as follows:
hernan> - John records in his calendar a reminder for some event at
hernan> datetime 2010-Jul-27, 10:30:00, with TZ "Chile/Santiago",
hernan> (GMT+4 hence it corresponds to UTC time 2010-Jul-27
hernan> 14:30:00). But some days afterwards, his government decides
hernan> to change the country TZ to GMT+5.
hernan> Now, when the day comes... should that reminder trigger at
hernan> A) 2010-Jul-27 10:30:00 "Chile/Santiago" = UTC time 2009-Jul-27
15:30:00
hernan> or
hernan> B) 2010-Jul-27 9:30:00 "Chile/Santiago" = UTC time 2009-Jul-27
14:30:00 ?
hernan> There is no correct answer, unless one knows what John
hernan> actually meant when he said "please ring me at "2010-Jul-27,
hernan> 10:30:00 TZ=Chile/Santiago" Did he mean a "civil date-time"
hernan> ("when the clocks in my city tell 10:30")? In that case, A)
hernan> is the correct answer. Or did he mean a "physical instant of
hernan> time", a point in the continuus line of time of our universe,
hernan> say, "when the next solar eclipse happens". In that case,
hernan> answer B) is the correct one.
If he meant (A), then you store the event as:
(ts,tz) = (timestamp '2010-07-27 10:30:00',
'Chile/Santiago')
and decide when it happens using (ts at time zone tz), evaluated on
the fly. This way, when you install an update in your zic database to
cope with the change of tz, the computed value of the physical time
changes, but it still shows the same calendar time.
If he meant (B), then you store the event as
(tsz,tz) = (timestamp '2010-07-27 10:30:00' at time zone 'Chile/Santiago',
'Chile/Santiago')
(note that tsz is now of type timestamp with time zone). This fixes the
physical time, and when you install the zic update, the displayed calendar
time changes, in order to keep the physical time the same.
If you're writing a calendaring app that wants to allow storing both kinds
of events (I've yet to see such an app that actually makes this distinction,
most seem to work on the assumption that timezones don't change), all the
tools for it are currently available in postgres.
--
Andrew (irc:RhodiumToad)
--
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] Syntax for partitioning
Marko Tiikkaja wrote:
> Jaime Casanova wrote:
> >> PARTITION name VALUES LESS THAN { range_upper | MAXVALUE }
> >>| PARTITION name VALUES IN ( list_value [,...] | DEFAULT )
> >
> > i remember someone making a comment about actually using operators
> > instead of LESS THEN and family
>
> That doesn't sound like a bad idea..
I prefer to use widely-used syntax instead of postgres original one.
Oracle and MySQL already use "LESS THAN" and "IN" for partitioning.
I assume almost all user only use the default operators.
I don't want to break de facto standard for small utilization area.
I think truly what we want is a new partition "kind" in addition to
RANGE and LIST. If we want to split geometric data into paritions,
we need to treat the the partition key with gist-list operation.
I agree with a plan to add some additional parition kinds,
but want to keep RANGE and LIST partitions in the current syntax.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
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] operator exclusion constraints
On Sat, Nov 14, 2009 at 2:27 PM, Jeff Davis wrote:
> New patches attached.
Forgive me if this is discussed before, but why does this store the
strategy numbers of the relevant operators instead of the operators
themselves? It seems like this could lead to surprising behavior if
the user modifies the definition of the operator class.
I'm wondering if we can't use the existing
BuildIndexValueDescription() rather than the new function
tuple_as_string(). I realize there are two tuples, but maybe it makes
sense to just call it twice?
I'm attaching a revised doc patch for your consideration.
...Robert
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 082dfe4..a73c015 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -51,10 +51,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE column_name [, ... ] ) index_parameters |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
-[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
+[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] |
+ EXCLUDE [ USING index_method ]
+( { column | column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH operator [, ... ] )
+index_parameters [ WHERE ( predicate ) ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-index_parameters in UNIQUE and PRIMARY KEY constraints are:
+index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace ]
@@ -547,6 +550,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE index_method ]
+ ( {column | column | (expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH operator [, ... ] )
+ index_parameters [ WHERE ( predicate ) ] }
+
+
+ The EXCLUDE clause specifies an operator exclusion
+ constraint. An operator exclusion constraint guarantees that if any two
+ tuples are compared on the specified columns or expressions using the
+ specified operators, at least one such comparison will return false.
+ If all of the specified operators test for equality, it is equivalent
+ to a UNIQUE constraint, although an ordinary unique constraint will
+ normally be faster. However, operator exclusion constraints can use
+ index methods other than btree, and can specify more general constraints.
+ For instance, you can specify the constraint that no two tuples in the
+ table contain overlapping circles
+ (see ) by using the
+ && operator.
+
+
+
+ Operator exclusion constraints are implemented internally using
+ an index, so the specified operators must be associated with an
+ appropriate operator class for the given access method, and the
+ access method must support amgettuple (see
+ for details). The operators are also required to be their own
+ commutators (see ).
+
+
+
+ The predicate
+ allows you to specify a constraint on a subset of the table,
+ internally using a partial index.
+
+
+
+
+
DEFERRABLE
NOT DEFERRABLE
@@ -,6 +1151,18 @@ CREATE TABLE cinemas (
+
+ Create table circles with an operator exclusion
+ constraint that prevents overlapping circles within it:
+
+
+CREATE TABLE circles (
+ c circle,
+ EXCLUDE USING gist (c WITH &&)
+);
+
+
+
--
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] UTF8 with BOM support in psql
Andrew Dunstan wrote: > Itagaki Takahiro wrote: > > Multi-byte scripts > > without encoding are always dangerous whether BOM is present or not. > > I'd say we can always throw an error when we find queries that contain > > multi-byte characters if no prior encoding declaration. > > You will break a gazillion scripts that today work quite happily if you do. Sure. That's why I didn't send a patch for it :) If by any chance we do so, we'll have a boolean option to disable the check. > Maybe there is a case for a extra command line switch to set the initial > client encoding for psql, which would make that a little easier and less > obscure to do. Would that make things simpler for you? No. There are complex reasons on Windows in Japan. The client encoding is always SJIS because of Windows restriction, but the database is initialized with UTF8. Simple interactive works with psql are done under SJIS encoding, but some scripts are written in UTF8 because it matches the server encoding. (Of course the script is executed as "psql -f utf8.sql > out.txt") I don't want user to check the encoding of scripts before executing -- it is far from fail-safe. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] UTF8 with BOM support in psql
Peter Eisentraut wrote: > Together, that should cover a lot of cases. Not perfect, but far from > useless. For Japanese users on Windows, the client encoding are always set to SJIS because of the restriction of cmd.exe. But the script file can be written in UTF8 with BOM. I don't think we should depend on client encoding. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Very bad FTS performance with the Polish config
Wojciech Knapik writes: > I tested on 8.3.1 on G5/OSX 10.5.8 and Xeon/Gentoo AMD64-2008.0 > (2.6.21), then switched both installations to 8.3.8 (both packages > compiled from source, but provided by the distro - port/emerge). The > Polish dictionaries and config were created according to this article > (it's in Polish, but the code is self-explanatory): > http://www.depesz.com/index.php/2008/04/22/polish-tsearch-in-83-polski-tsearch-w-postgresie-83/ I tried to duplicate this test, but got no further than here: u8=# CREATE TEXT SEARCH DICTIONARY polish_ispell ( TEMPLATE = ispell, DictFile = polish, AffFile = polish, StopWords = polish ); ERROR: syntax error CONTEXT: line 174 of configuration file "/home/tgl/testversion/share/postgresql/tsearch_data/polish.affix": " L E C > -C,GÅEM #zalec (15a) " u8=# Seems there's something about the current version of the dictionary that we don't like. I used sjp-ispell-pl-20091117-src.tar.bz2 ... 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] plperl and inline functions -- first draft
Joshua Tolley wrote: + plperl_call_data *save_call_data = current_call_data; + boololdcontext = trusted_context; + + if (SPI_connect() != SPI_OK_CONNECT) + elog(ERROR, "could not connect to SPI manager"); ... + current_call_data = (plperl_call_data *) palloc0(sizeof(plperl_call_data)); + current_call_data->fcinfo = &fake_fcinfo; + current_call_data->prodesc = &desc; I don't think this is done in the right order. If it is then this comment in plperl_func_handler is wrong (as well as containing a typo): /* * Create the call_data beforing connecting to SPI, so that it is not * allocated in the SPI memory context */ cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] magic block in doc functions
Hi, I noticed that some example functions don't contain the magic block and that leads to error while loading those examples in 8.2 or later. Attached is a patch that adds it. I also add some missing header file. Don't have a strong opinion about backpatching it or not. -- Euler Taveira de Oliveira http://www.timbira.com/ Index: doc/src/sgml/plhandler.sgml === RCS file: /a/pgsql/dev/anoncvs/pgsql/doc/src/sgml/plhandler.sgml,v retrieving revision 1.8 diff -c -r1.8 plhandler.sgml *** doc/src/sgml/plhandler.sgml 8 Oct 2009 04:41:07 - 1.8 --- doc/src/sgml/plhandler.sgml 17 Nov 2009 02:44:26 - *** *** 106,111 --- 106,115 #include "catalog/pg_proc.h" #include "catalog/pg_type.h" + #ifdef PG_MODULE_MAGIC + PG_MODULE_MAGIC; + #endif + PG_FUNCTION_INFO_V1(plsample_call_handler); Datum Index: doc/src/sgml/trigger.sgml === RCS file: /a/pgsql/dev/anoncvs/pgsql/doc/src/sgml/trigger.sgml,v retrieving revision 1.59 diff -c -r1.59 trigger.sgml *** doc/src/sgml/trigger.sgml 14 Oct 2009 22:14:21 - 1.59 --- doc/src/sgml/trigger.sgml 17 Nov 2009 02:50:27 - *** *** 576,581 --- 576,585 #include "executor/spi.h" /* this is what you need to work with SPI */ #include "commands/trigger.h" /* ... and triggers */ + #ifdef PG_MODULE_MAGIC + PG_MODULE_MAGIC; + #endif + extern Datum trigf(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(trigf); Index: doc/src/sgml/xfunc.sgml === RCS file: /a/pgsql/dev/anoncvs/pgsql/doc/src/sgml/xfunc.sgml,v retrieving revision 1.140 diff -c -r1.140 xfunc.sgml *** doc/src/sgml/xfunc.sgml 8 Oct 2009 02:39:16 - 1.140 --- doc/src/sgml/xfunc.sgml 18 Nov 2009 03:13:07 - *** *** 1925,1930 --- 1925,1935
Re: [HACKERS] plperl and inline functions -- first draft
On Tue, Nov 17, 2009 at 06:05:19PM -0500, Andrew Dunstan wrote:
>
>
> Alexey Klyukin wrote:
>>
>> I've noticed that the patch doesn't install current_call_data before calling
>> plperl_call_perl_func, although it saves and restores its previous value.
>> This breaks spi code, which relies on current_call_data->prodesc, i.e.:
>>
>> postgres=# DO $$ $result = spi_exec_query("select 1"); $$ LANGUAGE plperl;
>>
>
> Yeah, good catch. We need to lift some stuff out of
> plperl_func_handler(), because this code bypasses that. Not only setting
> the call_data but also connectin g to the SPI manager and maybe one or
> two other things.
I kept thinking I had to test SPI, but I guess I hadn't ever done it. The
attached takes care of such stuff, I think.
>> Also, a call to to plperl_call_perl_func should be cast to void to avoid a
>> possible compiler warning (although It doesn't emit one on my system):
>>
>> (void) plperl_call_perl_func(&desc, &fake_fcinfo);
>
> Right.
I don't get the warning either, and didn't realize it could produce one.
Thanks -- that change is also in the attached version.
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 49631f2..ebcb608 100644
*** a/doc/src/sgml/plperl.sgml
--- b/doc/src/sgml/plperl.sgml
*** CREATE FUNCTION funcname
The body of the function is ordinary Perl code. In fact, the PL/Perl
!glue code wraps it inside a Perl subroutine. A PL/Perl function must
!always return a scalar value. You can return more complex structures
!(arrays, records, and sets) by returning a reference, as discussed below.
!Never return a list.
--- 59,81
# PL/Perl function body
$$ LANGUAGE plperl;
+
+PL/Perl also supports anonymous code blocks called with the
+
+statement:
+
+
+ DO $$
+ # PL/Perl function body
+ $$ LANGUAGE plperl;
+
+
The body of the function is ordinary Perl code. In fact, the PL/Perl
!glue code wraps it inside a Perl subroutine. Anonymous code blocks cannot
!return a value; PL/Perl functions created with CREATE FUNCTION must always
!return a scalar value. You can return more complex structures (arrays,
!records, and sets) by returning a reference, as discussed below. Never
!return a list.
diff --git a/src/include/catalog/pg_pltemplate.h b/src/include/catalog/pg_pltemplate.h
index 5ef97df..8cdedb4 100644
*** a/src/include/catalog/pg_pltemplate.h
--- b/src/include/catalog/pg_pltemplate.h
*** typedef FormData_pg_pltemplate *Form_pg_
*** 70,77
DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "$libdir/plpgsql" _null_ ));
DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" _null_ "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperl_call_handler" _null_ "plperl_validator" "$libdir/plperl" _null_ ));
DATA(insert ( "plpythonu" f f "plpython_call_handler" _null_ _null_ "$libdir/plpython" _null_ ));
#endif /* PG_PLTEMPLATE_H */
--- 70,77
DATA(insert ( "plpgsql" t t "plpgsql_call_handler" "plpgsql_inline_handler" "plpgsql_validator" "$libdir/plpgsql" _null_ ));
DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu" f f "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
DATA(insert ( "plpythonu" f f "plpython_call_handler" _null_ _null_ "$libdir/plpython" _null_ ));
#endif /* PG_PLTEMPLATE_H */
diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index a3c3495..2c32850 100644
*** a/src/pl/plperl/GNUmakefile
--- b/src/pl/plperl/GNUmakefile
*** OBJS = plperl.o spi_internal.o SPI.o
*** 38,45
SHLIB_LINK = $(perl_embed_ldflags)
! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl
! REGRESS = plperl plperl_trigger plperl_shared plperl_elog
# where to find psql for running the tests
PSQLDIR = $(bindir)
--- 38,45
SHLIB_LINK = $(perl_embed_ldflags)
! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl --load-language=plperlu
! REGRESS = plperl plperl_trigger plperl_shared plperl_elog plperl_do
# where to find psql for running the tests
PSQLDIR = $(bindir)
diff --git a/src/pl/plperl/expected/plperl_do.out b/src/pl/plperl/expected/plperl_do.out
index ...a955581 .
*** a/src/pl/plperl/expected/plperl_do.out
--- b/src/pl/plperl/expected/plperl_do.out
***
*** 0
--- 1
Re: [HACKERS] Very bad FTS performance with the Polish config
Wojciech Knapik escreveu: > > Euler Taveira de Oliveira wrote: > >>> PS. This issue is not related to the loading time of dictionaries, or >>> calls to ts_headline for results that won't be displayed. >> >> So what? Could you post the profiling of that query? > I was talking about gprof (--enable-profiling), oprofile [1] or similar tools. But it seems the slow step is the sort one. [1] http://wiki.postgresql.org/wiki/Profiling_with_OProfile -- Euler Taveira de Oliveira http://www.timbira.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] RFC for adding typmods to functions
On Tue, Nov 17, 2009 at 7:46 PM, Tom Lane wrote: > Robert Haas writes: >> So I guess really can't get worked up about the idea of propagating >> this information through the type system. Even suppose we eventually >> take the steps you suggesting and make it so that varchar(30) || >> varchar(40) yields varchar(70). What good is that? Why would anyone >> care? > > People have complained that we don't follow the spec on this. Not many > people, perhaps, and it's certainly arguable that fixing this will be > far more trouble than it's worth. But there is a constituency that > cares --- mainly people who use client-side code that tends to fall over > if it doesn't see a suitable maxlength attached to query result columns. > The first example I came across in the archives was > http://archives.postgresql.org/pgsql-sql/2002-06/msg00235.php > [ pokes around a bit more ... ] Hm, I don't see any *recent* examples. > Maybe all that code has gotten fixed? Nah ... > >> What would actually be really nice is the ability to have >> parameterized types (like list-of-, >> unordered-set-of-, hash-with-keys-of-> type>-and-values-of-, function-taking-arguments-of-> types>-returning-) which would let us do all kinds of neat >> things - but I don't see how improving support for typmods gets us any >> closer to that. > > Well, we could possibly implement hacks like the current one for > anonymous record types. But SQL isn't intended as a language for > manipulating arbitrary data types, and I think trying to make it > do stuff like the above will be an exercise in masochism. Unfortunately, I kind of agree with you. As much as I'd like to have this, I wouldn't like it enough to seriously consider working on it at this point. > typmod > is mainly intended for tweaking the properties of base types, and > it seems fairly useful for that. "tweaking" certainly describes how we're using it, and perhaps why it's not worth putting a lot of effort into it. If we're going to do a lot of work, I'd like to get something better than slightly-improved-tweaking out of it. ...Robert -- 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] Syntax for partitioning
On Tue, Nov 17, 2009 at 4:31 PM, Simon Riggs wrote: > On Thu, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > >> I think syntax support is a good start. > > I don't see a syntax-only patch as being any use at all to this > community. > > We go to enormous lengths in other areas to never allow patches with > restrictions. Why would we allow a patch that is essentially 100% > restriction? i.e. It does nothing at all. Worse than that, it will > encourage people to believe it exists in full, when that isn't the case. > > The syntax has never really been in question, so it doesn't really move > us forwards in any direction. This is exactly the kind of shallow > feature we have always shied away from and that other databases have > encouraged. > > The only reason I can see is that it allows people to develop non-open > source code that matches how Postgres will work when we get our act > together. That seems likely to discourage, rather than encourage the > funding of this work for open source. It may even muddy the water for > people that don't understand that the real magic happens in the > internals, not in the syntax. > > Why not just wait until we have a whole patch and then apply? Because big patches are really hard to get applied. Personally, I think a syntax-only patch makes a lot of sense, as long as the design is carefully thought about so that it can serve as a foundation for future work in this area. I don't think "the whole patch" is even necessarily a well-defined concept in this instance: different people could have very different ideas about what would constitute a complete solution, or which aspects of a complete solution are most important or should be pursued first. Settling on a syntax, and an internal representation for that syntax, seems like it will make subsequent discussions about those projects considerably more straightforward, and it has some value in and of itself since similar notation is used by other databases. At least, that's MHO. ...Robert -- 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] sgml and "empty" closing tags
Robert Haas escreveu: > On Tue, Nov 17, 2009 at 6:39 PM, Euler Taveira de Oliveira > wrote: >> Alex Hunsaker escreveu: >>> BTW anyone know how to escape < and > for google? >>> >> You can escape < and > using < and >, respectively. > > Searching fornot documents containing " Ops, didn't read 'for google'. I thought that the OP was asking about escaping those identifiers in SGML. -- Euler Taveira de Oliveira http://www.timbira.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] RFC for adding typmods to functions
Robert Haas writes: > So I guess really can't get worked up about the idea of propagating > this information through the type system. Even suppose we eventually > take the steps you suggesting and make it so that varchar(30) || > varchar(40) yields varchar(70). What good is that? Why would anyone > care? People have complained that we don't follow the spec on this. Not many people, perhaps, and it's certainly arguable that fixing this will be far more trouble than it's worth. But there is a constituency that cares --- mainly people who use client-side code that tends to fall over if it doesn't see a suitable maxlength attached to query result columns. The first example I came across in the archives was http://archives.postgresql.org/pgsql-sql/2002-06/msg00235.php [ pokes around a bit more ... ] Hm, I don't see any *recent* examples. Maybe all that code has gotten fixed? Nah ... > What would actually be really nice is the ability to have > parameterized types (like list-of-, > unordered-set-of-, hash-with-keys-of- type>-and-values-of-, function-taking-arguments-of- types>-returning-) which would let us do all kinds of neat > things - but I don't see how improving support for typmods gets us any > closer to that. Well, we could possibly implement hacks like the current one for anonymous record types. But SQL isn't intended as a language for manipulating arbitrary data types, and I think trying to make it do stuff like the above will be an exercise in masochism. typmod is mainly intended for tweaking the properties of base types, and it seems fairly useful for that. 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] Timezones (in 8.5?)
"Kevin Grittner" writes: > hernan gonzalez wrote: >> I believe that this distinction between two realms: one related to >> (say) "physical time" and the other to (say) "civil date-time", is >> the key to put some order... conceptually, at least (I'm not >> speaking about feasibility for now). > Congratulations on the most sane and thoughtful discussion of this > I've seen! Yeah. As Hernan says, our notion of timestamptz corresponds to physical time, although the input/output conventions for it blur that rather badly. You can use the AT TIME ZONE constructs to convert between physical and civil times, but only according to the system's current understanding of the civil calendar, which will change anytime you install an update of the zic database. We haven't got a datatype that corresponds directly to "an instant in civil time" --- you could store timestamp-without-tz and a time zone name, but it's not built in. I could see developing new types that correspond more directly to physical and civil time --- the first is probably exactly the same as timestamptz except it always displays in UTC, and the second needs two fields. I think that trying to substitute either of these for the existing types is probably a lost cause though. Trying to deal with different civil calendars (changes in zic database rules) seems way too hard for what it would buy us. I think if you're using the civil time type, you're assuming that "10AM Nov 17 2009" means "10AM local time", even if the powers that be change the GMT offset sometime during the period that the data value is of interest. > One thing you didn't address is the "end-of-month" issues -- how do > you handle an order that someone pay a set amount on a given date and > monthly thereafter, when the date might be past the 28th? This seems to be an arithmetic operator issue and not directly a property of the type --- you could imagine different "datetime + interval" operators giving different answers for this but still working on the same underlying civil-time type. 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] sgml and "empty" closing tags
On Tue, Nov 17, 2009 at 6:39 PM, Euler Taveira de Oliveira wrote: > Alex Hunsaker escreveu: >> BTW anyone know how to escape < and > for google? >> > You can escape < and > using < and >, respectively. Searching for
Re: [HACKERS] RFC for adding typmods to functions
On Tue, Nov 17, 2009 at 6:01 PM, Tom Lane wrote: > Andrew Dunstan writes: >> Apart from all these it's not clear to me what the major benefits of >> doing this would be. I'd like an explanation of that to start with. > > Well, aside from the issue about making "anyelement" more powerful > (which could be done in other ways), I can think of: > > If we don't start down this path then we are never going to satisfy the > spec's expectations about type modifiers (the varchar concatenation > example among others). The given patch doesn't do that or even come > close, but it's a necessary prelude. > > More generally, people have complained in the past about typmods being > red-headed stepchildren in the type system. At present, since the > majority of expression forms throw away typmod information, there's > not much hope of treating typmod on the same level as type proper. > (If you look at the history of the expression-tree code you'll notice > that we've gradually propagated typmods into more and more places. > Associating a typmod with function results is the last major holdout.) > > I'm not sure that these points fully justify the work involved, but > it certainly seems to be a logical avenue of development if we can > agree on the semantics. There's something a little weird about the whole typmod concept. In the case of varchar(n) and numeric(x,y), it seems as though it's perhaps intended to allow the database to optimize by limiting the amount of on-disk storage that needs to be set aside for those values. Or you could alternatively view it as a type of constraint, like "the length of this string is at most n characters". The thing is, in most modern programming languages, this type of information isn't part of the type system at all. You have a type called string, and it represents a string of any length at all (of course, in C strings are fixed size-arrays, but that's more the exception than the rule, and C is a much lower-level language than SQL). Typically, details like the maximum number of characters in the string or the desired scale and precision of a numeric value aren't dealt with until you try to print the value out. So I guess really can't get worked up about the idea of propagating this information through the type system. Even suppose we eventually take the steps you suggesting and make it so that varchar(30) || varchar(40) yields varchar(70). What good is that? Why would anyone care? What would actually be really nice is the ability to have parameterized types (like list-of-, unordered-set-of-, hash-with-keys-of--and-values-of-, function-taking-arguments-of--returning-) which would let us do all kinds of neat things - but I don't see how improving support for typmods gets us any closer to that. ...Robert -- 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] Very bad FTS performance with the Polish config
Euler Taveira de Oliveira wrote: PS. This issue is not related to the loading time of dictionaries, or calls to ts_headline for results that won't be displayed. So what? Could you post the profiling of that query? Polish: http://pastie.textmate.org/private/8lhmnbvde43lfjoxc52r1q English: http://pastie.textmate.org/private/4iaipottrmjmfxfykz94mw cheers, Wojciech Knapik PS. Sorry for the double post. -- 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] Very bad FTS performance with the Polish config
Wojciech Knapik escreveu: > PS. This issue is not related to the loading time of dictionaries, or > calls to ts_headline for results that won't be displayed. > So what? Could you post the profiling of that query? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Very bad FTS performance with the Polish config
Hello
This has been discussed in #postgresql and posted to -performance a
couple days ago, but no solution has been found. The discussion can be
found here:
http://archives.postgresql.org/pgsql-performance/2009-11/msg00162.php
I just finished implementing a "search engine" for my site and found
ts_headline extremely slow when used with a Polish tsearch
configuration, while fast with English. All of it boils down to a simple
testcase, but first some background.
I tested on 8.3.1 on G5/OSX 10.5.8 and Xeon/Gentoo AMD64-2008.0
(2.6.21), then switched both installations to 8.3.8 (both packages
compiled from source, but provided by the distro - port/emerge). The
Polish dictionaries and config were created according to this article
(it's in Polish, but the code is self-explanatory):
http://www.depesz.com/index.php/2008/04/22/polish-tsearch-in-83-polski-tsearch-w-postgresie-83/
Now for the testcase:
text = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad
minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip
ex ea commodo consequat. Duis aute irure dolor in reprehenderit in
voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur
sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt
mollit anim id est laborum.'
# explain analyze select ts_headline('polish', text,
plainto_tsquery('polish', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=6.407..6.470
rows=1 loops=1)
Total runtime: 6.524 ms
(2 rows)
# explain analyze select ts_headline('english', text,
plainto_tsquery('english', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.861..0.895
rows=1 loops=1)
Total runtime: 0.935 ms
(2 rows)
# explain analyze select ts_headline('simple', text,
plainto_tsquery('simple', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.627..0.660
rows=1 loops=1)
Total runtime: 0.697 ms
(2 rows)
#
As you can see, the results differ by an order of magnitude between
Polish and English. While in this simple testcase it's a non-issue, in
the real world this translates into enormous overhead.
One of the queries I ran testing my site's search function took
1870ms. When I took that query and changed all ts_headline(foo) calls to
just foo, the time dropped below 100ms. That's the difference between
something completely unacceptable and something quite useful.
I can post various details about the hardware, software and specific
queries, but the testcases speak for themselves. I'm sure you can easily
reproduce my results.
I'm putting my code into production tomorrow, since I can't wait
anymore. Hints would be very much appreciated!
cheers,
Wojciech Knapik
PS. This issue is not related to the loading time of dictionaries, or
calls to ts_headline for results that won't be displayed. A few other
details can be found here
http://pastie.textmate.org/private/hqnqfnsfsknjyjlffzmog along with
snippets of my conversations in #postgresql that lead to this testcase.
Big thanks to RhodiumToad for helping me with fts for the last couple
days ;]
--
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] CommitFest expectations
Robert Haas wrote: This expresses my feelings on the topic exactly, and perhaps merits inclusion in a Wiki page someplace. Maybe we need to have a wiki page on commitfest rules & expectations. I put a note at http://wiki.postgresql.org/wiki/Submitting_a_Patch#Submission_timing which seems the logical place to warn people about patch submission guidelines; there was already one there about avoiding submissions during the beta I moved into the new section. There's still some debris from the old wiki-based CommitFest approach floating around the wiki that makes it harder to figure out how things fit together than it should be. I started cleaning that up with refreshing http://wiki.postgresql.org/wiki/CommitFest , which is probably the right place to document general rules and expectations better. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] sgml and "empty" closing tags
Alex Hunsaker escreveu: > BTW anyone know how to escape < and > for google? > You can escape < and > using < and >, respectively. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Very bad FTS performance with the Polish config
Hello
This has been discussed in #postgresql and posted to -performance a
couple days ago, but no solution has been found. The discussion can be
found here:
http://archives.postgresql.org/pgsql-performance/2009-11/msg00162.php
I just finished implementing a "search engine" for my site and found
ts_headline extremely slow when used with a Polish tsearch
configuration, while fast with English. All of it boils down to a simple
testcase, but first some background.
I tested on 8.3.1 on G5/OSX 10.5.8 and Xeon/Gentoo AMD64-2008.0
(2.6.21), then switched both installations to 8.3.8 (both packages
compiled from source, but provided by the distro - port/emerge). The
Polish dictionaries and config were created according to this article
(it's in Polish, but the code is self-explanatory):
http://www.depesz.com/index.php/2008/04/22/polish-tsearch-in-83-polski-tsearch-w-postgresie-83/
Now for the testcase:
text = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad
minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip
ex ea commodo consequat. Duis aute irure dolor in reprehenderit in
voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur
sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt
mollit anim id est laborum.'
# explain analyze select ts_headline('polish', text,
plainto_tsquery('polish', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=6.407..6.470
rows=1 loops=1)
Total runtime: 6.524 ms
(2 rows)
# explain analyze select ts_headline('english', text,
plainto_tsquery('english', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.861..0.895
rows=1 loops=1)
Total runtime: 0.935 ms
(2 rows)
# explain analyze select ts_headline('simple', text,
plainto_tsquery('simple', 'foobar'));
QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.627..0.660
rows=1 loops=1)
Total runtime: 0.697 ms
(2 rows)
#
As you can see, the results differ by an order of magnitude between
Polish and English. While in this simple testcase it's a non-issue, in
the real world this translates into enormous overhead.
One of the queries I ran testing my site's search function took
1870ms. When I took that query and changed all ts_headline(foo) calls to
just foo, the time dropped below 100ms. That's the difference between
something completely unacceptable and something quite useful.
I can post various details about the hardware, software and specific
queries, but the testcases speak for themselves. I'm sure you can easily
reproduce my results.
I'm putting my code into production tomorrow, since I can't wait
anymore. Hints would be very much appreciated!
cheers,
Wojciech Knapik
PS. This issue is not related to the loading time of dictionaries, or
calls to ts_headline for results that won't be displayed. A few other
details can be found here
http://pastie.textmate.org/private/hqnqfnsfsknjyjlffzmog along with
snippets of my conversations in #postgresql that lead to this testcase.
Big thanks to RhodiumToad for helping me with fts for the last couple
days ;]
--
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] plperl and inline functions -- first draft
Alexey Klyukin wrote:
I've noticed that the patch doesn't install current_call_data before calling
plperl_call_perl_func, although it saves and restores its previous value. This
breaks spi code, which relies on current_call_data->prodesc, i.e.:
postgres=# DO $$ $result = spi_exec_query("select 1"); $$ LANGUAGE plperl;
Yeah, good catch. We need to lift some stuff out of
plperl_func_handler(), because this code bypasses that. Not only setting
the call_data but also connectin g to the SPI manager and maybe one or
two other things.
Also, a call to to plperl_call_perl_func should be cast to void to avoid a
possible compiler warning (although It doesn't emit one on my system):
(void) plperl_call_perl_func(&desc, &fake_fcinfo);
Right.
cheers
andrew
--
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] RFC for adding typmods to functions
Andrew Dunstan writes: > Apart from all these it's not clear to me what the major benefits of > doing this would be. I'd like an explanation of that to start with. Well, aside from the issue about making "anyelement" more powerful (which could be done in other ways), I can think of: If we don't start down this path then we are never going to satisfy the spec's expectations about type modifiers (the varchar concatenation example among others). The given patch doesn't do that or even come close, but it's a necessary prelude. More generally, people have complained in the past about typmods being red-headed stepchildren in the type system. At present, since the majority of expression forms throw away typmod information, there's not much hope of treating typmod on the same level as type proper. (If you look at the history of the expression-tree code you'll notice that we've gradually propagated typmods into more and more places. Associating a typmod with function results is the last major holdout.) I'm not sure that these points fully justify the work involved, but it certainly seems to be a logical avenue of development if we can agree on the semantics. 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] plpgsql: open for execute - add USING clause
On Tue, Nov 17, 2009 at 5:34 PM, Greg Smith wrote: > Pavel Stehule wrote: >> >> I don't wont to apply these patches tomorrow, I don't sending these >> patches for last moment. If I have to wait one weak or two weeks, ok. >> Declare it. I'll respect it. But actually I respecting all rules, what >> I know. > > If you're sending stuff intended for the next CommitFest in the middle of an > active one (which we'd prefer not to see at all but you have your own > schedule limitations), it would be helpful if you were to label those > patches as such. It's difficult for the rest of us to tell which of the > ones you're generating are in response to patches that are active during > this one, and which are intended for future review but you're just dropping > them off now. Had your new stuff been labeled "This is for the next > CommitFest, I'm just sending it to the list now", it would have made it > easier on everyone else to figure out which of your messages we need to pay > attention to and what should be ignored for now. This expresses my feelings on the topic exactly, and perhaps merits inclusion in a Wiki page someplace. Maybe we need to have a wiki page on commitfest rules & expectations. ...Robert -- 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] actualised funcs typmod patch
Le 17 nov. 2009 à 20:33, Tom Lane a écrit :
>> We could to talk about it now. We are not hurry. But I would to see
>> some progress in this area in next two months. This patch is simple
>> and doesn't create any new rules or doesn't change behave.
>
> What do you mean it doesn't change the behavior? It establishes a
> specific set of behaviors for functions with non-default typmods in
> their arguments. If we just apply whatever was the easiest thing to
> implement, without any discussion, we are very likely to regret it
> later.
>
> It might be that what you've done is all fine, but I'd like some
> discussion and consensus on the issues. Submitting an entirely
> documentation-free patch is not the way to establish consensus.
I'll try to help there, it's not really a review any more, but still it seems
needed. Here's what I gather the specs of Pavel's work are by quick-reading
through his patch:
/*
+* Don't allow change of input typmodes. Any change should break
+* stored casts in prepared plans.
+*/
The return type now can have a non -1 typmod given.
[implementation details of parameterTypmodes and allParameterTypmodes left out,
not well understood yet, does seem to be details rather than spec level things]
+ if (rettypmod != resttypmod && rettypmod != -1)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+errmsg("return type mismatch in function declared to
return %s",
+ format_type_with_typemod(rettype,
rettypmod)),
+errdetail("Actual return type is %s.",
+
format_type_with_typemod(restype, resttypmod;
So you need to return a decorated value I guess, or assign it to a retval which
is of the right type, including typmod. Declaring a retval text to handle a
RETURNS varchar(15) won't do it.
+ /* when typmodes are different, then foerce coercion too */
+ force_coerce = declared_typmod != -1 && declared_typmod !=
actual_typmod;
So if you declare typmods they are NOT part of the polymorphism (also per
comment upthread) but you cannot change them and there's automatic coercion
when only the typmod mismatches. I think that's what Tom wanted to avoid doing
(because it breaks existing code assumptions and typmod coercion is not well
defined).
Here are some tests showing either the coercion of the argument (and failures
to do it) or the return type typmod invalidity:
+ ERROR: cannot change parameter typmod of existing function
+ select typmodtest('a',''); -- outside plpgsql
+ ERROR: value too long for type character varying(3)
+ select typmodtest('','bbb'); -- return value
+ ERROR: value too long for type character varying(6)
+ CONTEXT: PL/pgSQL function "typmodtest" while casting return value to
function's return type
Then a great deal of changes that makes me cry in favor of having something
human friendly around internal catalogs representation, all this BKI stuff IIUC.
So the bulk of it is supporting return typemod declaration. This expands to OUT
types, which can be cool:
+ create or replace function typmodtest(out a numeric(5,2),out b numeric(5,2),
out c numeric(5,2))
Hope this helps,
--
dim
PS: about the more than one anyelement type support in functions, I'd rather
have a nice SQLish syntax around it. My proposal was sth like this:
CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y)
RETURNS anyelement y[]
AS $$
...
$$;
--
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] plperl and inline functions -- first draft
On Nov 9, 2009, at 6:07 PM, Joshua Tolley wrote:
>
> Ok, updated patch attached. As far as I know, this completes all outstanding
> issues:
>
> 1) weird comment in plperl.c is corrected and formatted decently
> 2) plperlu vs. plperl actually works (thanks again, Andrew)
> 3) docs included
> 4) regression tests included
>
> Some items of note include that this makes the regression tests add not only
> plperl to the test database but also plperlu, which is a new thing. I can't
> see why this might cause problems, but thought I'd mention it. The tests
> specifically try to verify that plperl doesn't allow 'use Data::Dumper', and
> plperlu does. Since Data::Dumper is part of perl core, that seemed safe, but
> it is another dependency, and perhaps we don't want to do that. If not, is
> there some other useful way of testing plperlu vs. plperl, and does it really
> matter?
I've noticed that the patch doesn't install current_call_data before calling
plperl_call_perl_func, although it saves and restores its previous value. This
breaks spi code, which relies on current_call_data->prodesc, i.e.:
postgres=# DO $$ $result = spi_exec_query("select 1"); $$ LANGUAGE plperl;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
rogram received signal EXC_BAD_ACCESS, Could not access memory.
Reason: KERN_INVALID_ADDRESS at address: 0x
0x0001006f0336 in plperl_spi_exec (query=0x1007ecb60 "select 1", limit=0)
at plperl.c:1895
warning: Source file is more recent than executable.
1895spi_rv = SPI_execute(query,
current_call_data->prodesc->fn_readonly,
(gdb) bt
#0 0x0001006f0336 in plperl_spi_exec (query=0x1007ecb60 "select 1",
limit=0) at plperl.c:1895
Also, a call to to plperl_call_perl_func should be cast to void to avoid a
possible compiler warning (although It doesn't emit one on my system):
(void) plperl_call_perl_func(&desc, &fake_fcinfo);
--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc
--
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] plperl and inline functions -- first draft
On Wed, Nov 18, 2009 at 09:35:35AM +1100, Brendan Jurd wrote: > 2009/11/17 Joshua Tolley : > > On Sun, Nov 15, 2009 at 12:10:33PM +1100, Brendan Jurd wrote: > >> I noticed that there was a fairly large amount of bogus/inconsistent > >> whitespace > ... > > > > Thanks -- I tend to forget whitespace :) > > > >> In the documentation you refer to this feature as "inline functions". > >> I think this might be mixing up the terminology > ... > > I can accept that argument. The attached patch modifies the documentation, > > and > > fixes another inconsistency I found. > > > > Cool. I have no gripes with the revised patch. I'm marking this as > ready for committer now. Thanks! Thanks to you, as well, and Andrew for his work. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] plperl and inline functions -- first draft
2009/11/17 Joshua Tolley : > On Sun, Nov 15, 2009 at 12:10:33PM +1100, Brendan Jurd wrote: >> I noticed that there was a fairly large amount of bogus/inconsistent >> whitespace ... > > Thanks -- I tend to forget whitespace :) > >> In the documentation you refer to this feature as "inline functions". >> I think this might be mixing up the terminology ... > I can accept that argument. The attached patch modifies the documentation, and > fixes another inconsistency I found. > Cool. I have no gripes with the revised patch. I'm marking this as ready for committer now. Thanks! Cheers, BJ -- 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] Syntax for partitioning
On Thu, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: > I think syntax support is a good start. I don't see a syntax-only patch as being any use at all to this community. We go to enormous lengths in other areas to never allow patches with restrictions. Why would we allow a patch that is essentially 100% restriction? i.e. It does nothing at all. Worse than that, it will encourage people to believe it exists in full, when that isn't the case. The syntax has never really been in question, so it doesn't really move us forwards in any direction. This is exactly the kind of shallow feature we have always shied away from and that other databases have encouraged. The only reason I can see is that it allows people to develop non-open source code that matches how Postgres will work when we get our act together. That seems likely to discourage, rather than encourage the funding of this work for open source. It may even muddy the water for people that don't understand that the real magic happens in the internals, not in the syntax. Why not just wait until we have a whole patch and then apply? -- Simon Riggs www.2ndQuadrant.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] plpgsql: open for execute - add USING clause
Pavel Stehule wrote: I don't wont to apply these patches tomorrow, I don't sending these patches for last moment. If I have to wait one weak or two weeks, ok. Declare it. I'll respect it. But actually I respecting all rules, what I know. If you're sending stuff intended for the next CommitFest in the middle of an active one (which we'd prefer not to see at all but you have your own schedule limitations), it would be helpful if you were to label those patches as such. It's difficult for the rest of us to tell which of the ones you're generating are in response to patches that are active during this one, and which are intended for future review but you're just dropping them off now. Had your new stuff been labeled "This is for the next CommitFest, I'm just sending it to the list now", it would have made it easier on everyone else to figure out which of your messages we need to pay attention to and what should be ignored for now. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Timezones (in 8.5?)
hernan gonzalez wrote: > I believe that this distinction between two realms: one related to > (say) "physical time" and the other to (say) "civil date-time", is > the key to put some order... conceptually, at least (I'm not > speaking about feasibility for now). This is the approach of some > Date-Time APIs, for example the "Joda" Java library > http://joda-time.sourceforge.net/ (headed to replace soon > https://jsr-310.dev.java.net/the original ugly JDK Date-Calendar > API) and I believe it's the right way. Congratulations on the most sane and thoughtful discussion of this I've seen! In our shop we had so many problems with the "physical time" based implementation of dates, times, and timestamps in Java that we wrote our own library to cover our needs. I hadn't heard about Joda; we should probably look at it to see if we can migrate from our home-grown solution. One thing you didn't address is the "end-of-month" issues -- how do you handle an order that someone pay a set amount on a given date and monthly thereafter, when the date might be past the 28th? I'm curious to hear your opinion on that topic. I have seen in this real-world financial applications several times. They have usually wanted to go to the last day of the month when there aren't enough days in a given month, but then go back out to the original day-of-month whenever possible; but sometimes the payment "one month" after the 31st of January has to be 30 days past the 1st of the next month. The SQL standard solution to this is much ridiculed here, even though I suspect many have seen monthly bills or statements at some point in their lives ;-) -Kevin -- 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] RFC for adding typmods to functions
Tom Lane wrote: Pavel submitted a patch to add typmods to function declarations, but there was no prior design discussion and it desperately needs some. Let me try to summarize the issues that seem to need agreement. [excellent summary of problem areas snipped] Unless we have consensus on all of these points I don't think we should proceed with the patch. Comments? Apart from all these it's not clear to me what the major benefits of doing this would be. I'd like an explanation of that to start with. cheers andrew -- 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] next CommitFest
On Nov 17, 2009, at 9:15 AM, Andrew Dunstan wrote: > Indeed. I once suggested only half jokingly that we should have a "Coder of > the month" award. I suggest that it be named "The Tom Lane" award, and disqualify Tom from winning (sorry Tom). ;-) David -- 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] plpgsql: open for execute - add USING clause
On Tue, 2009-11-17 at 15:40 -0600, Kevin Grittner wrote: > Pavel Stehule wrote: > > > I never sent these (last two) patches to THIS commitfest. Is it > > clean? > > I'm sure it would be much appreciated, and help to alleviate the > frustration and burnout of some other contributors, if you could take > a turn at reviewing -- at least one patch each commitfest. In short Pavel, Nobody is complaining about your patches. It would just be really nice if you could help review some existing patches in this commit fest. Would you be willing to do so? Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFC for adding typmods to functions
Pavel submitted a patch to add typmods to function declarations, but there was no prior design discussion and it desperately needs some. Let me try to summarize the issues that seem to need agreement. The proposed patch allows optional typmods to be attached to the declared argument and result types of a function; for example you could say "create function foo(numeric(2)) returns numeric(4)". (Note: in existing releases, this syntax works but the typmod information is simply discarded.) An immediate application, not implemented here but which we'd like to have for 8.5, is multiple anyelement types -- for example, create function foo(anyelement, anyelement, anyelement(1), anyelement(1)) returns anyelement(1) says that the first and second arguments must be of the same type, the third and fourth must also be of the same type but not necessarily the same as the first two, and the result is of this second type. I can see the following definitional issues: 1. Are the typmods of input arguments part of the function signature, ie, could foo(numeric(2)) and foo(numeric(3)) coexist? The proposed patch answers "no, they are the same function and you can have only one". This may be good enough, but there are some possible uses that we are foreclosing by doing this. Two sample applications: foo(numeric)a general-purpose function foo(numeric(2)) same definition but optimized for short inputs foo(anyelement, anyelement(1)) general case foo(anyelement, anyelement) optimized for identical input types The major obstacle to allowing such cases is that we'd need to invent new ambiguous-function resolution rules that would let us figure out which function to prefer for a given set of inputs, and it's not at all clear how to do that --- in particular deciding that one is preferable to another seems to require type-specific knowledge about the meaning of different typmods. So that looks like a major can of worms, probably requiring new APIs for custom data types. A possible compromise is to say that you can have only one now but leave the door open to allow more than one later. However, the function signature is the function identity for many purposes, so it's hard to be fuzzy about this. For example, given "CREATE FUNCTION foo(numeric(2))", which of the following should drop the function? DROP FUNCTION foo(numeric(2)); DROP FUNCTION foo(numeric); DROP FUNCTION foo(numeric(3)); The traditional behavior is that any of these would work, since the typmod was ignored anyway. If the typmod means something then the second one is a bit surprising and the third definitely doesn't satisfy the POLA. Are we prepared to possibly break existing apps now by disallowing the third and/or second? 2. What is the exact meaning of attaching a typmod to an input argument? As the patch has it, doing so means nothing at all for the purposes of resolving which function to call, and then once we have identified the function we will attempt to apply an implicit coercion to the actual input argument to make it match the typmod. The first part of that is probably reasonable if you accept the "there can be only one" answer to point #1; but if you don't then it's completely unworkable. In any case it's worth noting that foo(anyelement, anyelement) will accept two arguments of the same types and different typmods, which might surprise people. The second part is trickier, in particular the fact that the coercion is implicit. Up to now there have been only assignment and explicit coercions that could try to apply a typmod to a value. Our existing API for coercion functions (see the CREATE CAST man page if you don't recall details) doesn't even provide a way for the coercion function to distinguish implicit from assignment coercions. Maybe this is fine --- on that same page we say it's bad design for coercion functions to pay attention to the cast context anyhow. But we had better agree that it's okay for such coercions to behave more like assignment than like a traditional implicit cast. If you want to distinguish the cases, we need to break that API. 3. What is the exact meaning of attaching a typmod to a result or output argument? There are two fundamentally different views you can take on this point: that the typmod is an assertion that the function result matches the typmod, or that the typmod requests a run-time coercion step to make the result match the typmod. For C-level functions the first of these seems more natural; after all we take it on faith that the result is of the declared type. In particular, you *have to* adopt that viewpoint towards the coercion functions of the type, because the system has no other knowledge of what a typmod means than "the results of the type's coercion functions have the correct properties for the given typmod value". For PL functions I doubt we want to trust the function writer completely that his results match the typmo
Re: [HACKERS] plpgsql: open for execute - add USING clause
Pavel Stehule wrote: > I never sent these (last two) patches to THIS commitfest. Is it > clean? Counting the "In Progress" commitfest and the two preceding ones, you have submitted nine patches and contributed to the review of none. Surely you noticed recent threads about how the review and commit steps are the bottleneck, help is desperately needed for the review process, and the point of commitfests is to get everyone to take a break from coding to help review the work of others? Several regular contributors have expressed frustration that while they are taking time off from their preferred activity of coding to contribute to the review process, others are stacking up a pile of patches for the next review cycle. Robert in particular has been burning himself out trying to keep the patch reviews rolling through so that everyone's patches can get proper consideration. I certainly appreciate that you are making contributions of patches to help make PostgreSQL better; but since the review process is the bottleneck, if you don't help review patches, any time spent by someone reviewing your patches comes out of the time they would be writing patches themselves. I'm sure it would be much appreciated, and help to alleviate the frustration and burnout of some other contributors, if you could take a turn at reviewing -- at least one patch each commitfest. -Kevin -- 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] plpgsql: open for execute - add USING clause
2009/11/17 Joshua D. Drake : > On Tue, 2009-11-17 at 14:33 -0600, Kevin Grittner wrote: >> Pavel Stehule wrote: >> > 2009/11/17 Robert Haas : >> >> >> This is now the fourth patch you've submitted since the start of >> >> the CommitFest... >> >> >> > >> > These patches are for next commitfest. What I know, the current >> > commitfest is closed for new patches. Is it ok? >> >> Until this moment I was unconvinced of the need for a strict rule that >> patches from regular submitters who don't suspend patch development >> to contribute to the commitfest reviews should be ignored. > > I agree they should be ignored until the NEXT commitfest. I do not agree > that they should be dropped into a bucket. I never sent these (last two) patches to THIS commitfest. Is it clean? I am maybe crazy, but I know when commitfest starting. Have I next month be quite? First patch I resent, because patch was broken. But this patch was sent to 2009-11-04. Really, I don't would to push my patches to this commitfest. Pavel > > Joshua D. Drake > > >> >> -Kevin >> > > > -- > PostgreSQL.org Major Contributor > Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 > Consulting, Training, Support, Custom Development, Engineering > If the world pushes look it in the eye and GRR. Then push back harder. - > Salamander > > -- 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] Timezones (in 8.5?)
> Perhaps the OP should explain exactly what real-world problems he's
> trying to solve. As noted in the discussion you linked, there's not
> a lot of enthusiasm around here for getting closer to the spec's
> datetime handling simply because it's the spec; that part of the spec
> is just too broken for that to be a credible argument.
I'm not much interested in the compliance with the ANSI SQL spec, I
agree in this regard it is unsatisfactory (to put it midly).
But I'm also disatisfied with the current Postgresql implementation,
the types TIMESTAMP and TIMESTAMP WITH TIMEZONE are in the middle of
being SQL compliant and being really useful. The support of timezones
is really crippled now.
I understand, though, that backward compatibily is critical, and I'm
surely unaware of many implementation issues.
Anyway (long rambling follows - and excuse my english)...
We know that, even ignoring ANSI spec and postgresql compatibility for
one moment, even before considering date-time arithmetic and DTS
issues, date-time handling is notoriously difficult to formalize
satisfactorily. And, come to look at it, it's not a Postgresql
problem, nor a SQL problem: I believe there is NO standard for
store/serialize/represent a "date-time value", with all the
complexities that the concept has in human usage (ISO 8601, as
ANSI-SQL, just considers GMT offsets, not real timezones).
Let me present a simple real world scenario -to look at not from the
implementation point of view, but from the user:
- John records in his calendar a reminder for some event at datetime
2010-Jul-27, 10:30:00, with TZ "Chile/Santiago", (GMT+4 hence it
corresponds to UTC time 2010-Jul-27 14:30:00). But some days
afterwards, his government decides to change the country TZ to GMT+5.
Now, when the day comes... should that reminder trigger at
A) 2010-Jul-27 10:30:00 "Chile/Santiago" = UTC time 2009-Jul-27 15:30:00
or
B) 2010-Jul-27 9:30:00 "Chile/Santiago" = UTC time 2009-Jul-27 14:30:00 ?
There is no correct answer, unless one knows what John actually meant
when he said "please ring me at "2010-Jul-27, 10:30:00
TZ=Chile/Santiago"
Did he mean a "civil date-time" ("when the clocks in my city tell
10:30")? In that case, A) is the correct answer.
Or did he mean a "physical instant of time", a point in the continuus
line of time of our universe, say, "when the next solar eclipse
happens". In that case, answer B) is the correct one.
I believe that this distinction between two realms: one related to
(say) "physical time" and the other to (say) "civil date-time", is the
key to put some order... conceptually, at least (I'm not speaking
about feasibility for now). This is the approach of some Date-Time
APIs, for example the "Joda" Java library
http://joda-time.sourceforge.net/ (headed to replace soon
https://jsr-310.dev.java.net/ the original ugly JDK Date-Calendar
API) and I believe it's the right way.
In this approach, we would have two entirely different types (or
family of types) -no castings allowed.
An "instant" is a "physical time", a point in the time continuum.
A "partial date time spec" (or "partial civil datetime") is just a
tuple of values {year,month,day, hour,min,sec,usec,TZ_id} some of
which might be empty/unspecified.
Conversion from "instant" to "civil datetime" is only allowed if a TZ
is also specified (well, also a "Calendar" spec, if non-gregorian
dates are to be dealt with).
Conversion from "partial civil datetime" to "instant" is only allowed
if all fields are non-empty (again, assuming a "Calendar").
Similar distintion goes for "intervals" or "durations".
Postgresql implementation (and ANSI-SQL), for all date-time data,
revolves around the "physical time" concept: that is what it is
ultimately stored, that's what it's tought as the "real thing" (the
rest are input/output and arithmetic issues).
(Rather disgressing: even the DATE type is treated as a point in time,
as a DateTime with time=00:00:00 ; I think this is bad, conceptually,
when I think of "2010-Jul-27" I think of a date, not of the instant of
time "2010-Jul-27 00:00:00", they are different concepts; this is NOT
analogous to INT 10 => FLOAT 10.0 )
Because of this (IMHO) conceptual limitation, the availabily of the
two types "TIMESTAMP" "TIMESTAMP WITH TIME ZONE" results,
unfortunately, much less useful than it could have been.
If I were to reimplement the date-time data types, without much
regarding ANSI-SQL standard and Postgresql compatibility (a little too
much to ask, I know) I'd propose:
TIMESTAMP: ("instante") just a point in time, purely physical (as it
name suggest!). UTC encoded.
(input format could accept unix time or standard datetime format, with
default/server TZ; output format could output explicit GMT offset, to
support dump/restore robustly)
DATETIME: (call it "TIMESTAMP WITH TIME ZONE" if you wish but... is a
very different thing)
a full "civil" date time specification {year,month,day,
hour,min,sec,usec,TZ} (Of course, internally it c
Re: [HACKERS] plpgsql: open for execute - add USING clause
On Tue, 2009-11-17 at 14:33 -0600, Kevin Grittner wrote: > Pavel Stehule wrote: > > 2009/11/17 Robert Haas : > > >> This is now the fourth patch you've submitted since the start of > >> the CommitFest... > >> > > > > These patches are for next commitfest. What I know, the current > > commitfest is closed for new patches. Is it ok? > > Until this moment I was unconvinced of the need for a strict rule that > patches from regular submitters who don't suspend patch development > to contribute to the commitfest reviews should be ignored. I agree they should be ignored until the NEXT commitfest. I do not agree that they should be dropped into a bucket. Joshua D. Drake > > -Kevin > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] plpgsql: open for execute - add USING clause
2009/11/17 Kevin Grittner : > Pavel Stehule wrote: >> 2009/11/17 Robert Haas : > >>> This is now the fourth patch you've submitted since the start of >>> the CommitFest... >>> >> >> These patches are for next commitfest. What I know, the current >> commitfest is closed for new patches. Is it ok? > > Until this moment I was unconvinced of the need for a strict rule that > patches from regular submitters who don't suspend patch development > to contribute to the commitfest reviews should be ignored. what is wrong? Patches typmodes for functions and enhancing psql was notificated in proposal: *http://archives.postgresql.org/pgsql-hackers/2009-11/msg00934.php *http://archives.postgresql.org/pgsql-hackers/2009-10/msg00519.php (more than one moth old) patch for OPEN EXECUTE USING is reaction on Tom's mail http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php (for me it is like a proposal) and this patch is +/- bugfix. I don't wont to apply these patches tomorrow, I don't sending these patches for last moment. If I have to wait one weak or two weeks, ok. Declare it. I'll respect it. But actually I respecting all rules, what I know. I don't would to generate thousand patches now. Simply I have a time for postgres now. I wrote three patches. And I put it to commitfest, because I thing so this work is serious. So anybody can comment it, so anybody can test it. I put it to commitfest application, because this code is finished (or finished for reviewing) and I would lost these patches in this mailing list. Tomorrow I could be killed (maybe), tomorrow I could to lost data in my hardisc. I have not other patches. Don't afraid. Pavel > > -Kevin > -- 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] plpgsql: open for execute - add USING clause
Pavel Stehule wrote: > 2009/11/17 Robert Haas : >> This is now the fourth patch you've submitted since the start of >> the CommitFest... >> > > These patches are for next commitfest. What I know, the current > commitfest is closed for new patches. Is it ok? Until this moment I was unconvinced of the need for a strict rule that patches from regular submitters who don't suspend patch development to contribute to the commitfest reviews should be ignored. -Kevin -- 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] plpgsql: open for execute - add USING clause
2009/11/17 Pavel Stehule : > 2009/11/17 Robert Haas : >> On Tue, Nov 17, 2009 at 3:04 PM, Pavel Stehule >> wrote: >>> Hello, >>> >>> this small patch add missing USING clause to OPEN FOR EXECUTE statement >>> + cleaning part of exec_stmt_open function >>> >>> >>> see http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php >> >> This is now the fourth patch you've submitted since the start of the >> CommitFest... >> > > These patches are for next commitfest. What I know, the current > commitfest is closed for new patches. Is it ok? > typmode support is for this commitfest. Others for next commitfest. Pavel > Pavel > >> ...Robert >> > -- 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] plpgsql: open for execute - add USING clause
2009/11/17 Robert Haas : > On Tue, Nov 17, 2009 at 3:04 PM, Pavel Stehule > wrote: >> Hello, >> >> this small patch add missing USING clause to OPEN FOR EXECUTE statement >> + cleaning part of exec_stmt_open function >> >> >> see http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php > > This is now the fourth patch you've submitted since the start of the > CommitFest... > These patches are for next commitfest. What I know, the current commitfest is closed for new patches. Is it ok? Pavel > ...Robert > -- 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] plpgsql: open for execute - add USING clause
On Tue, Nov 17, 2009 at 3:04 PM, Pavel Stehule wrote: > Hello, > > this small patch add missing USING clause to OPEN FOR EXECUTE statement > + cleaning part of exec_stmt_open function > > > see http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php This is now the fourth patch you've submitted since the start of the CommitFest... ...Robert -- 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] actualised funcs typmod patch
2009/11/17 Kevin Grittner : > Pavel Stehule wrote: > >> sorry, it's problem in my english > > In hopes that it may help overcome the language barrier if someone > restates the issue in different words, I'll echo Tom's concerns, which > I share. > >> This cannot change an behave of older applications. > > Agreed. That's not the problem. > >> if somebody use explicit typmod in CREATE FUNCTION statement, then >> he get different behave. But he have to do it explicitly in 8.5. > > The problem here is that we are then committed to supporting that > behavior forever. There are many different options for what behavior > could and should be supported for this syntax, and we don't want to > get locked in to one of those options by happenstance. We want the > options to be discussed by the community prior to getting locked in to > something. It would be unusual for one individual to pick a direction > for something like this and to come up with a decision which is as > good, in the long run, as consensus coming out of the synergy of group > discussion could generate. I understand it well and I invite any discussion about this topic (additing typmod to function's interface). So please, check it, test it, write notes. Pavel > > -Kevin > -- 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] actualised funcs typmod patch
Pavel Stehule wrote: > sorry, it's problem in my english In hopes that it may help overcome the language barrier if someone restates the issue in different words, I'll echo Tom's concerns, which I share. > This cannot change an behave of older applications. Agreed. That's not the problem. > if somebody use explicit typmod in CREATE FUNCTION statement, then > he get different behave. But he have to do it explicitly in 8.5. The problem here is that we are then committed to supporting that behavior forever. There are many different options for what behavior could and should be supported for this syntax, and we don't want to get locked in to one of those options by happenstance. We want the options to be discussed by the community prior to getting locked in to something. It would be unusual for one individual to pick a direction for something like this and to come up with a decision which is as good, in the long run, as consensus coming out of the synergy of group discussion could generate. -Kevin -- 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] Syntax for partitioning
Jaime Casanova wrote:
* If a table with the same name already exists when a partition
is created, the table is re-used as partition. This behavior
is required for pg_dump to be simple.
i guess the table must be empty, if not we should be throw an error...
and i actually prefer some more explicit syntax for this not just
reusing a table
I'd be OK with only a notification - even if the table wasn't empty -,
similar to how inheritance combines rows currently. The patch currently
silently reuses the table unless it has rows which don't satisfy the
CHECK constraint, in which case it gives you the default CHECK
constraint error.
PARTITION name VALUES LESS THAN { range_upper | MAXVALUE }
| PARTITION name VALUES IN ( list_value [,...] | DEFAULT )
i remember someone making a comment about actually using operators
instead of LESS THEN and family
That doesn't sound like a bad idea..
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plpgsql: open for execute - add USING clause
Hello,
this small patch add missing USING clause to OPEN FOR EXECUTE statement
+ cleaning part of exec_stmt_open function
see http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2009-11-13 23:43:39.0 +0100
--- ./doc/src/sgml/plpgsql.sgml 2009-11-17 20:30:10.656208300 +0100
***
*** 2488,2494
OPEN FOR EXECUTE
! OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string;
--- 2488,2494
OPEN FOR EXECUTE
! OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string USING expression , ... ;
***
*** 2500,2506
command. As usual, this gives flexibility so the query plan can vary
from one run to the next (see ),
and it also means that variable substitution is not done on the
! command string.
The SCROLL and
NO SCROLL options have the same meanings as for a bound
cursor.
--- 2500,2507
command. As usual, this gives flexibility so the query plan can vary
from one run to the next (see ),
and it also means that variable substitution is not done on the
! command string. As with EXECUTE, parameter values
! can be inserted into the dynamic command via USING.
The SCROLL and
NO SCROLL options have the same meanings as for a bound
cursor.
***
*** 2509,2515
An example:
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
--- 2510,2516
An example:
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1) ' WHERE col1 = $1' USING var1;
*** ./src/pl/plpgsql/src/gram.y.orig 2009-11-13 23:43:40.0 +0100
--- ./src/pl/plpgsql/src/gram.y 2009-11-17 14:37:33.927208178 +0100
***
*** 1686,1692
tok = yylex();
if (tok == K_EXECUTE)
{
! new->dynquery = read_sql_stmt("SELECT ");
}
else
{
--- 1686,1712
tok = yylex();
if (tok == K_EXECUTE)
{
! int endtoken;
!
! new->dynquery = read_sql_construct(K_USING, ';', 0,
! "USING or ;",
! "SELECT ",
! true, true,
! NULL, &endtoken);
!
! /* If we found "USING", collect the argument(s) */
! if (endtoken == K_USING)
! {
! PLpgSQL_expr *expr;
!
! do
! {
! expr = read_sql_expression2(',', ';',
! ", or ;",
! &endtoken);
! new->params = lappend(new->params, expr);
! } while (endtoken == ',');
! }
}
else
{
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2009-11-09 01:26:55.0 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2009-11-17 19:48:47.209207349 +0100
***
*** 199,206
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
List *params);
static void free_params_data(PreparedParamsData *ppd);
! static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
! PLpgSQL_expr *query, List *params);
/* --
--- 199,206
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
List *params);
static void free_params_data(PreparedParamsData *ppd);
! static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate, char *posrtalname,
! PLpgSQL_expr *query, List *params, int cursorOption);
/* --
***
*** 2343,2350
{
/* RETURN QUERY EXECUTE */
Assert(stmt->dynquery != NULL);
! portal = exec_dynquery_with_params(estate, stmt->dynquery,
! stmt->params);
}
tupmap = convert_tuples_by_position(portal->tupDesc,
--- 2343,2350
{
/* RETURN QUERY EXECUTE */
Assert(stmt->dynquery != NULL);
! portal = exec_dynquery_with_params(estate, NULL, stmt->dynquery,
! stmt->params, 0);
}
tupmap = convert_tuples_by_position(portal->tupDesc,
***
*** 3123,3129
Portal portal;
int rc;
! portal = exec_dynquery_with_params(estate, stmt->query, stmt->params);
/*
* Execute the loop
--- 3123,3129
Portal portal;
int rc;
! portal = exec_dynquery_with_params(estate, NULL, stmt->query, stmt->params, 0);
/*
* Execute the loop
***
*** 3191,3234
* This is an OPEN refcursor FOR EXECUTE ...
* --
*/
! Datum queryD;
! Oid restype;
! char *querystr;
! SPIPlanPtr curplan;
!
! /* --
! * We evaluate the string expression after the
! * EXECUTE keyword. It's result is the querystring we have
! * to execute.
! * --
! */
! queryD = ex
Re: [HACKERS] patch - Report the schema along table name in a referential failure error message
Tom Lane escribió: > George Gensure writes: > > I've put together a small patch to provide a schema name in an fk > > violation in deference to the todo item "Report the schema along table > > name in a referential failure error message" > > This is not the way forward; if it were, we would have done it years > ago. Despite the poor wording of the TODO item, nobody is particularly > interested in solving this problem one error message at a time. FWIW see this thread http://archives.postgresql.org/pgsql-hackers/2009-08/msg00213.php -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] actualised funcs typmod patch
2009/11/17 Tom Lane :
> Pavel Stehule writes:
>> 2009/11/17 Tom Lane :
>>> My point is that if we release 8.5 with these semantics (which as far
>>> as I can tell were not designed, but just fell out of what made for the
>>> shortest patch) then we'll be stuck with them thereafter.
>
>> We could to talk about it now. We are not hurry. But I would to see
>> some progress in this area in next two months. This patch is simple
>> and doesn't create any new rules or doesn't change behave.
>
> What do you mean it doesn't change the behavior? It establishes a
> specific set of behaviors for functions with non-default typmods in
> their arguments. If we just apply whatever was the easiest thing to
> implement, without any discussion, we are very likely to regret it
> later.
sorry, it's problem in my english - too simply language. This cannot
change an behave of older applications. Sure - if somebody use
explicit typmod in CREATE FUNCTION statement, then he get different
behave. But he have to do it explicitly in 8.5. It is only main
opinion - I thing, so nobody can expect surprise. In plpgsql the
behave is practically same. The difference is one - neighbourhood has
more informations about function interface.
one positive point. It fixes one small bug:
postgres=# create or replace function foo(a varchar(3))
returns varchar as $$
begin
a := 'abcdef';
return a;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select foo('omega');
foo
abcdef
(1 row)
>
> It might be that what you've done is all fine, but I'd like some
> discussion and consensus on the issues. Submitting an entirely
> documentation-free patch is not the way to establish consensus.
>
yes, I am sorry. I hope, so there will be some people who help with
creating documentation. What I know, it's need:
* drop notices about dropping typmod in CREATE FUNCTION statement
* enhancing documentation about pg_proc
* add note to ALTER FUNCTION about impossibility to change typmod -
the reason is same like impossibility to change defaults or names.
The new behave should be transparent for user.
Regards
Pavel Stehule
> 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] Syntax for partitioning
On Thu, Nov 12, 2009 at 5:54 AM, Itagaki Takahiro
wrote:
> I added psql and pg_dump support to Partitioning Syntax patch.
> Paritioning information can be retrieved with a new system function
> pg_get_partitiondef(parentRelid). Both psql and pg_dump use it.
>
i haven't seen the patch but:
>
> * If a table with the same name already exists when a partition
> is created, the table is re-used as partition. This behavior
> is required for pg_dump to be simple.
>
i guess the table must be empty, if not we should be throw an error...
and i actually prefer some more explicit syntax for this not just
reusing a table
> PARTITION name VALUES LESS THAN { range_upper | MAXVALUE }
> | PARTITION name VALUES IN ( list_value [,...] | DEFAULT )
>
i remember someone making a comment about actually using operators
instead of LESS THEN and family
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
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] Syntax for partitioning
Hi, I'm reviewing your patch. The patch applies without problems and the feature works as advertised. I have yet to look at the code in detail, but it looks sane and seems to work. However, this looks like a mistake: partinfo = (PartitionInfo *) malloc(ntups * sizeof(PartitionInfo)); or am I missing something? The syntax itself seems a bit weird in some cases. Say you have: PARTITION BY RANGE ( foo USING > ) ( PARTITION bar VALUES LESS THAN 0 ); which translates to CHECK (bar > 0). That doesn't sound at all like LESS THAN to me. This syntax seems to be the same Oracle uses, and I think it's nice for the general case, but I think the reversed operator weirdness is a bit too much. Maybe we should use something like PARTITION bar VALUES OPERATOR 0 when the user specifies the operator? Regards, Marko Tiikkaja -- 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] actualised funcs typmod patch
Pavel Stehule writes: > 2009/11/17 Tom Lane : >> My point is that if we release 8.5 with these semantics (which as far >> as I can tell were not designed, but just fell out of what made for the >> shortest patch) then we'll be stuck with them thereafter. > We could to talk about it now. We are not hurry. But I would to see > some progress in this area in next two months. This patch is simple > and doesn't create any new rules or doesn't change behave. What do you mean it doesn't change the behavior? It establishes a specific set of behaviors for functions with non-default typmods in their arguments. If we just apply whatever was the easiest thing to implement, without any discussion, we are very likely to regret it later. It might be that what you've done is all fine, but I'd like some discussion and consensus on the issues. Submitting an entirely documentation-free patch is not the way to establish consensus. 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] Unpredictable shark slowdown after migrating to 8.4
Sergey Konoplev escribió:
> On Mon, Nov 16, 2009 at 9:56 PM, Alvaro Herrera
> wrote:
> > Sergey Konoplev escribió:
> >
> >> I tried to get locks with this queries
> >
> > Did you try pg_locks?
> >
>
> I tried monitor locks with pgrowlocks. Isn't it better way? If it
> isn't what points should I pay attention with pg_lock?
pgrowlocks shows row locks only. pg_locks shows all regular locks, i.e.
locks on tables, pages, transactions, etc. You should be concerned with
pgrowlocks only if you see a transaction waiting for locktype=transaction.
> I've just write the query
>
> SELECT pid, count(1) AS locks, current_query
> FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON pid = procpid
> WHERE l.mode IN ('RowExclusiveLock', 'ShareUpdateExclusiveLock',
> 'ExclusiveLock')
> GROUP BY 1,3 ORDER BY 2 DESC LIMIT 10;
Why only those modes? I'd search for locks with granted=false, then see
all the other locks held by the process that's holding the conflicting
lock with granted=true (i.e. the one you're waiting on).
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
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] actualised funcs typmod patch
2009/11/17 Tom Lane : > Pavel Stehule writes: >> 2009/11/17 Tom Lane : >>> There are other issues but these are the ones I think we'd need to >>> resolve before not after putting in function typmods. It'd be >>> extremely painful and non-backwards-compatible to change our minds >>> later about function identity or coercion rules. > >> I am sure, so this patch cannot break any current code. > > My point is that if we release 8.5 with these semantics (which as far > as I can tell were not designed, but just fell out of what made for the > shortest patch) then we'll be stuck with them thereafter. We could to talk about it now. We are not hurry. But I would to see some progress in this area in next two months. This patch is simple and doesn't create any new rules or doesn't change behave. Simply store explicitly defined typmod and use it. Nothing more. If you thing, so this is poor or problematic - please, show samples and use cases. Best regards Pavel Stehule > > 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] actualised funcs typmod patch
Pavel Stehule writes: > 2009/11/17 Tom Lane : >> There are other issues but these are the ones I think we'd need to >> resolve before not after putting in function typmods. Â It'd be >> extremely painful and non-backwards-compatible to change our minds >> later about function identity or coercion rules. > I am sure, so this patch cannot break any current code. My point is that if we release 8.5 with these semantics (which as far as I can tell were not designed, but just fell out of what made for the shortest patch) then we'll be stuck with them thereafter. 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] UTF8 with BOM support in psql
Andrew Dunstan writes: > Well, it might be a good idea to provide at least some support in libpq. > Making each client do it from scratch seems a bit inefficient. Encoding conversion seems far outside libpq's charter, and as for "from scratch" there are other libraries for that. 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] actualised funcs typmod patch
2009/11/17 Tom Lane : > Pavel Stehule writes: >> 2009/11/17 Tom Lane : >>> If the only immediate benefit we'd get is multiple anyelement types, >>> I think we'd be much better advised to just create a few separate >>> types for that. Function typmods are a big can of worms and we should >>> not lock ourselves into a half-baked solution. > >> When I started work on typmod support, my motivation was only a >> multiple anyelement types. Now, I thing, so typmodes are more >> important than parametrized polymorphic type. Now, PL are dynamic with >> minimal static checks. Step by step the static checks are richer. I >> could to imagine future warning like "declared varchar(40), but >> potential content is varchar(50)". These controls are not possible >> without using typmodes in function (explicitly declared). > > Yeah, there are some potential advantages, but that's all the more > reason not to jump into it on the basis of a patch that was cranked > out in a few days with no design discussion. I sent a proposal. Nobody replayed. But I invite any discussion. > > Aside from the point I already made about needing non-constant output > typmods, I would want to see some serious discussion about exactly how > typmods fit into function signatures in the first place. Are > f(numeric(2)) and f(numeric(3)) distinct signatures (ie, can you have > both of these functions at the same time)? If so, what are the > overloading resolution rules that will let us figure out which one to > call? If not, doesn't that put a rather serious crimp in the idea that > anyelement(0) and anyelement(1) can be used in the desired fashion? > a) I take typmod of argument similarly like name. It cannot be altered, and it isn't used as part of function's signature. These rules are similar like argname - from same reasons. So there cannot exists functions f(numeric(2)) and f(numeric(3)) in same time. b) polymorphic types are other - and are differently processed - now (and in future). anyelement(0) and enyelement(1) dosn't mean so A0 is different than A1 (it's my idea, should be different). It's mean, so A0 should be processed independently on A1. Basically - multiple enyelement type hasn't any bigger benefit to current situation. We have "any". The big limit of "any" type is hard specification of return type. I understand why and I would to create new tool for arounding this limit. So I can to prepare a polymorphic signature: FUNCTION foo(A0,A0, A1, A0, A1, ...) RETURNS A1, FUNCTION foo(IN A0, IN A1, IN A2) RETURNS table(A1, A2) I don't see an sense of parametrized types for input, for me - the possibility better to specify result type(s) of polymorphic functions is important. Sure - this feature should be implemented via real types, - maybe via domains - or via typmode. We should to have two any views on parametrised polymorphic types: a) A0, A1 are different types, b) A0 means use real type on first position, A1 - use real type on second position. I am inclined to b. For b. typmod is native. > For that matter, given f(numeric(2)), does an actual argument that > is numeric with some other typmod match it at all? We generally avoid > setting up implicit coercions that are likely or certain to fail, so > you could argue that an actual argument that's numeric(100) should not > be thought to be implicitly coercible to numeric(2). Again though > the degree of sanity of such things seems pretty type-specific ... > do we need per-type functions to determine coercibility of typmods? > How about a case like bigint -> numeric(2), is that supposed to be > implicitly coercible? The reason these are issues now is that before > this patch there was never a case of wanting an implicit coercion to > a specific typmod --- only explicit or assignment coercions could > have a target typmod other than -1. I am not sure that our API for > type coercion functions can even handle the case cleanly. > Look to proposed code, please. These situation are solved (if I understand well) and tested in regress tests. > There are other issues but these are the ones I think we'd need to > resolve before not after putting in function typmods. It'd be > extremely painful and non-backwards-compatible to change our minds > later about function identity or coercion rules. > I am sure, so this patch cannot break any current code. Why - current pg_dump ignore typmods in function signature - so older code use -1 as typmod for function arguments/result everywhere, on 8.4 and older databases could not be stored typmod in pg_proc, so these changes, cannot have impact on older code. regards Pavel Stehule > 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] UTF8 with BOM support in psql
Tom Lane wrote: Andrew Dunstan writes: Peter Eisentraut wrote: Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? Just about impossible. It would require a protocol bump, and removal of C-style string usage *everywhere* on the client side. Again, this is something that might be more feasible with encoding conversion inside psql --- translating UTF16 to UTF8 immediately upon reading it from any external file would confine the problem to possibly manageable bounds. Well, it might be a good idea to provide at least some support in libpq. Making each client do it from scratch seems a bit inefficient. cheers andrew -- 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] UTF8 with BOM support in psql
Andrew Dunstan writes: > Peter Eisentraut wrote: >> Well, someone could implement UTF-16 or UTF-whatever as client encoding. >> But I have not heard of any concrete proposals about that. > Doesn't the nul byte problem make that seriously hard? Just about impossible. It would require a protocol bump, and removal of C-style string usage *everywhere* on the client side. Again, this is something that might be more feasible with encoding conversion inside psql --- translating UTF16 to UTF8 immediately upon reading it from any external file would confine the problem to possibly manageable bounds. 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] actualised funcs typmod patch
Pavel Stehule writes: > 2009/11/17 Tom Lane : >> If the only immediate benefit we'd get is multiple anyelement types, >> I think we'd be much better advised to just create a few separate >> types for that. Function typmods are a big can of worms and we should >> not lock ourselves into a half-baked solution. > When I started work on typmod support, my motivation was only a > multiple anyelement types. Now, I thing, so typmodes are more > important than parametrized polymorphic type. Now, PL are dynamic with > minimal static checks. Step by step the static checks are richer. I > could to imagine future warning like "declared varchar(40), but > potential content is varchar(50)". These controls are not possible > without using typmodes in function (explicitly declared). Yeah, there are some potential advantages, but that's all the more reason not to jump into it on the basis of a patch that was cranked out in a few days with no design discussion. Aside from the point I already made about needing non-constant output typmods, I would want to see some serious discussion about exactly how typmods fit into function signatures in the first place. Are f(numeric(2)) and f(numeric(3)) distinct signatures (ie, can you have both of these functions at the same time)? If so, what are the overloading resolution rules that will let us figure out which one to call? If not, doesn't that put a rather serious crimp in the idea that anyelement(0) and anyelement(1) can be used in the desired fashion? For that matter, given f(numeric(2)), does an actual argument that is numeric with some other typmod match it at all? We generally avoid setting up implicit coercions that are likely or certain to fail, so you could argue that an actual argument that's numeric(100) should not be thought to be implicitly coercible to numeric(2). Again though the degree of sanity of such things seems pretty type-specific ... do we need per-type functions to determine coercibility of typmods? How about a case like bigint -> numeric(2), is that supposed to be implicitly coercible? The reason these are issues now is that before this patch there was never a case of wanting an implicit coercion to a specific typmod --- only explicit or assignment coercions could have a target typmod other than -1. I am not sure that our API for type coercion functions can even handle the case cleanly. There are other issues but these are the ones I think we'd need to resolve before not after putting in function typmods. It'd be extremely painful and non-backwards-compatible to change our minds later about function identity or coercion rules. 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] UTF8 with BOM support in psql
> -Original Message- > From: Andrew Dunstan [mailto:and...@dunslane.net] > Sent: Tuesday, November 17, 2009 9:15 AM > To: Peter Eisentraut > Cc: Chuck McDevitt; Itagaki Takahiro; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] UTF8 with BOM support in psql > > > > Peter Eisentraut wrote: > > On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: > > > >> Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, > >> so psql and PostgreSQL understand it? > >> (BTW, that would actually be nice on Windows, where UTF-16 is > common). > >> > > > > Well, someone could implement UTF-16 or UTF-whatever as client > encoding. > > But I have not heard of any concrete proposals about that. > > > > > > Doesn't the nul byte problem make that seriously hard? > Not really... You can't treat UTF-16 the same way you do UTF-8, but we are talking about it being a client_encoding, not a server_encoding. So, it's only the routines that look at the strings pre-conversion, and the conversion routines themselves, that need to understand UTF-16 strings are 16-bits at a time, and end with a 16 bit 0x. Obviously, it's more work than handing another 8-bit client_encoding, but doesn't seem insurmountable. And given the 1:1 mapping from UTF-16 to UTF-8, you don't have any new issues due to characters that can't be converted. -- 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] UTF8 with BOM support in psql
Peter Eisentraut wrote: On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, so psql and PostgreSQL understand it? (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. Doesn't the nul byte problem make that seriously hard? cheers andrew -- 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] UTF8 with BOM support in psql
> -Original Message- > From: Peter Eisentraut [mailto:pete...@gmx.net] > Sent: Tuesday, November 17, 2009 9:05 AM > To: Chuck McDevitt > Cc: Itagaki Takahiro; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] UTF8 with BOM support in psql > > On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: > > Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, > > so psql and PostgreSQL understand it? > > (BTW, that would actually be nice on Windows, where UTF-16 is common). > > Well, someone could implement UTF-16 or UTF-whatever as client encoding. > But I have not heard of any concrete proposals about that. Certainly that would be nice, given that UTF-16 is the "native" encoding for Java, C#, Visual Basic.net, JDBC, ODBC drivers >= ver 3.5, Microsoft Windows (all system calls use UTF-16, with a compatibility layer for old apps), and apps that Postgres users might switch from, such as MS SQLServer. But for the short term, a warning or error saying we don't support it is better than a confusing lexer error or syntax error. -- 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] UTF8 with BOM support in psql
On tis, 2009-11-17 at 00:59 -0800, Chuck McDevitt wrote: > Or is there a plan to read and convert the UTF-16 or UTF-32 to UTF-8, > so psql and PostgreSQL understand it? > (BTW, that would actually be nice on Windows, where UTF-16 is common). Well, someone could implement UTF-16 or UTF-whatever as client encoding. But I have not heard of any concrete proposals about that. -- 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] UTF8 with BOM support in psql
On tis, 2009-11-17 at 09:31 +0900, Itagaki Takahiro wrote: > Peter Eisentraut wrote: > > > OK, I think the consensus here is: > > - Eat BOM at beginning of file (as you implemented) > > - Only when client encoding is UTF-8 --> please fix that > > Are they AND condition? If so, this patch will be useless. > Please remember \encoding or SET client_encoding appear > *after* BOM at beginning of file. Presumably, if you have editors throwing in BOM marks without asking, you have an environment where either a) You can set the client encoding to UTF8 in the environment, so it applies by default, or b) The server encoding is UTF8, so the client encoding will default to that. Together, that should cover a lot of cases. Not perfect, but far from useless. -- 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] plpythonu DO support (inline call handler)
Valtonen, Hannu wrote: > I couldn't figure out how to get added files to show in git diff ... git add git add -u # to include modified files in the index git diff --cached -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plpythonu DO support (inline call handler)
Hi,
The attached patch adds support for DO clause in plpythonu. It was
heavily inspired by the plperl and plpgsql inline handler code.
I couldn't figure out how to get added files to show in git diff so the
two files that this patch adds: expected/plpython_do.out and
expected/plpython_do.sql are added as diff -uN 's in plpython_do_sql.diff
- Hannu
ps.
(This is a resend of the patch since I sent it a couple of days ago and
the original message seems to have been lost to the moderation queue so
now I subscribed.)
--- jee 1970-01-01 02:00:00.0 +0200
+++ sql/plpython_do.sql 2009-11-16 09:11:07.0 +0200
@@ -0,0 +1 @@
+DO $$ plpy.notice("This is plpythonu") $$ LANGUAGE plpythonu;
--- jee 1970-01-01 02:00:00.0 +0200
+++ expected/plpython_do.out2009-11-16 09:11:31.0 +0200
@@ -0,0 +1,3 @@
+DO $$ plpy.notice("This is plpythonu") $$ LANGUAGE plpythonu;
+NOTICE: This is plpythonu
+CONTEXT: PL/Python function "inline_plpythonu_function"
diff --git a/src/include/catalog/pg_pltemplate.h
b/src/include/catalog/pg_pltemplate.h
index 5ef97df..6eb6235 100644
--- a/src/include/catalog/pg_pltemplate.h
+++ b/src/include/catalog/pg_pltemplate.h
@@ -72,6 +72,6 @@ DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_
_null_ "$libdir/pltcl" _n
DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_
"$libdir/pltcl" _null_ ));
DATA(insert ( "plperl" t t "plperl_call_handler" _null_
"plperl_validator" "$libdir/plperl" _null_ ));
DATA(insert ( "plperlu"f f "plperl_call_handler" _null_
"plperl_validator" "$libdir/plperl" _null_ ));
-DATA(insert ( "plpythonu" f f "plpython_call_handler" _null_ _null_
"$libdir/plpython" _null_ ));
+DATA(insert ( "plpythonu" f f "plpython_call_handler"
"plpython_inline_handler" _null_ "$libdir/plpython" _null_ ));
#endif /* PG_PLTEMPLATE_H */
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 373bc79..9cae5f2 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -60,6 +60,7 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plpythonu
REGRESS = \
plpython_schema \
plpython_populate \
+plpython_do \
plpython_test \
plpython_global \
plpython_import \
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 6fd4aca..ced874d 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -195,14 +195,16 @@ typedef struct PLyResultObject
/* function declarations */
-/* Two exported functions: first is the magic telling Postgresql
- * what function call interface it implements. Second is for
- * initialization of the interpreter during library load.
+/* Three exported functions: first is the magic telling Postgresql
+ * the function call interface it implements. Second one is for the inline
call handler
+ * and the third one is for initialization of the interpreter during library
load.
*/
Datum plpython_call_handler(PG_FUNCTION_ARGS);
+Datum plpython_inline_handler(PG_FUNCTION_ARGS);
void _PG_init(void);
PG_FUNCTION_INFO_V1(plpython_call_handler);
+PG_FUNCTION_INFO_V1(plpython_inline_handler);
/* most of the remaining of the declarations, all static */
@@ -378,6 +380,65 @@ plpython_return_error_callback(void *arg)
}
Datum
+plpython_inline_handler(PG_FUNCTION_ARGS)
+{
+InlineCodeBlock *codeblock = (InlineCodeBlock *)
DatumGetPointer(PG_GETARG_DATUM(0));
+Datum retval;
+ FunctionCallInfoData fake_fcinfo;
+ FmgrInfo flinfo;
+
+ PLyProcedure *save_curr_proc;
+ PLyProcedure *volatile proc = NULL;
+ ErrorContextCallback plerrcontext;
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ MemSet(&fake_fcinfo, 0, sizeof(fake_fcinfo));
+ MemSet(&flinfo, 0, sizeof(flinfo));
+ fake_fcinfo.flinfo = &flinfo;
+ flinfo.fn_oid = InvalidOid;
+ flinfo.fn_mcxt = CurrentMemoryContext;
+
+ save_curr_proc = PLy_curr_procedure;
+ plerrcontext.callback = plpython_error_callback;
+ plerrcontext.arg = codeblock->source_text;
+ plerrcontext.previous = error_context_stack;
+ error_context_stack = &plerrcontext;
+
+ proc = PLy_malloc(sizeof(PLyProcedure));
+ proc->proname = PLy_strdup("inline_plpythonu_function");
+ proc->pyname = PLy_strdup("inline_plpythonu _function");
+ proc->result.is_rowtype = 1;
+ proc->me = PyCObject_FromVoidPtr(proc, NULL);
+
+ PG_TRY();
+ {
+ PLy_procedure_compile(proc, codeblock->source_text);
+ PLy_curr_procedure = proc;
+ retval = PLy_function_handler(&fake_fcinfo, proc);
+ }
+ PG_CATCH();
+ {
+
Re: [HACKERS] Partitioning option for COPY
Emmanuel Cecchet writes: > Tom Lane wrote: >> Cache? Why do you need a cache for COPY? > Actually the cache is only activated if you use the partitioning option. > It is just a list of oids of child tables where tuples were inserted. Umm ... why is that useful enough to be cached? > Why do I get this segfault if I use memory from CacheMemoryContext? Well, CacheMemoryContext will never be reset, so either you freed the data structure yourself or there's something wrong with the pointer you think is pointing at the data structure ... 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] Unpredictable shark slowdown after migrating to 8.4
On Mon, Nov 16, 2009 at 10:17 PM, Andres Freund wrote: > On Wednesday 11 November 2009 18:50:46 Sergey Konoplev wrote: >> Hello community, >> >> >> Second time after migration 8.3.7 --> 8.4.1 I was caught by this >> problem. Migration was 8 days ago. >> (note, I never seen such situation on 8.3) > Is 8.4 configured similarly to 8.3? > It is. 8.3 conf - http://pastie.org/702752 8.4 conf - http://pastie.org/702748 -- Regards, Sergey Konoplev -- PostgreSQL articles in english & russian http://gray-hemp.blogspot.com/search/label/postgresql/ -- 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] Unpredictable shark slowdown after migrating to 8.4
> > Can you show us the non-commented settings from your postgresql.conf? Working postgresql.conf http://pastie.org/702748 > > Can you show us what the vmstat output looks like when everything is > running normally? It looks like the blocks out are pretty high, but I > don't know how that compares to normal for you. > Here it is http://pastie.org/702742 -- Regards, Sergey Konoplev -- 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] Unpredictable shark slowdown after migrating to 8.4
On Mon, Nov 16, 2009 at 9:56 PM, Alvaro Herrera
wrote:
> Sergey Konoplev escribió:
>
>> I tried to get locks with this queries
>
> Did you try pg_locks?
>
I tried monitor locks with pgrowlocks. Isn't it better way? If it
isn't what points should I pay attention with pg_lock?
I've just write the query
SELECT pid, count(1) AS locks, current_query
FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON pid = procpid
WHERE l.mode IN ('RowExclusiveLock', 'ShareUpdateExclusiveLock',
'ExclusiveLock')
GROUP BY 1,3 ORDER BY 2 DESC LIMIT 10;
would it be what we need?
--
Regards,
Sergey Konoplev
--
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] Partitioning option for COPY
Tom Lane wrote: Emmanuel Cecchet writes: Actually the list is supposed to stay around between statement executions. You don't want to restart with a cold cache at every statement so I really want this structure to stay in memory at a more global level. Cache? Why do you need a cache for COPY? Repeated bulk loads into the same table within a single session doesn't seem to me to be a case that is common enough to justify a cache. Actually the cache is only activated if you use the partitioning option. It is just a list of oids of child tables where tuples were inserted. It is common to have multiple COPY operations in the same session when you are doing bulk loading in a warehouse. (BTW, the quoted code seems to be busily reinventing OID Lists. Don't do that.) Yes, I understood that I should use an OidList instead. But I was trying to understand what I did wrong here (besides reinventing the oid list ;-)). Why do I get this segfault if I use memory from CacheMemoryContext? Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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] Timezones (in 8.5?)
Robert Haas writes: > One random thought - I am not aware that we currently have a "time > zone" type in which to store a time zone in. Is there any value in > having such a thing vs. just using varchar? The main potential advantage seems to be faster lookup of the zone's associated data ... but I think we already keep the data in a hashtable indexed by hash of the zone name, so the gain might be pretty marginal. A specialized type *might* provide some notational advantage for writing operators, eg maybe "timestamp @ zone" would be sensible. But this is speculative without some clearer idea of what operations you'd want. And anyway it's not clear that text wouldn't work just as well there. Perhaps the OP should explain exactly what real-world problems he's trying to solve. As noted in the discussion you linked, there's not a lot of enthusiasm around here for getting closer to the spec's datetime handling simply because it's the spec; that part of the spec is just too broken for that to be a credible argument. 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] Partitioning option for COPY
Emmanuel Cecchet writes: > Actually the list is supposed to stay around between statement > executions. You don't want to restart with a cold cache at every > statement so I really want this structure to stay in memory at a more > global level. Cache? Why do you need a cache for COPY? Repeated bulk loads into the same table within a single session doesn't seem to me to be a case that is common enough to justify a cache. (BTW, the quoted code seems to be busily reinventing OID Lists. Don't do that.) 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] Writeable CTE patch
On Tue, Nov 17, 2009 at 03:54, Marko Tiikkaja wrote: >> Also, after reading through the previous threads; it was not >> immediately obvious that you dealt with >> http://archives.postgresql.org/pgsql-hackers/2009-10/msg00566.php by >> only allowing selects or values at the top level of with. > > This is actually just something missing from the current implementation. > The relevant posts are in the same thread: > http://archives.postgresql.org/pgsql-hackers/2009-10/msg00558.php and > the two follow-ups. The comment in ExecutePlan() is a bit misleading. Hrm I tried the various forms of: with x as ( ... ) insert/update/delete and could not get any of them to work. So I assumed the comment about only SELECT and values were allowed was correct. Maybe a function that does an insert or update at the top level could get it to break? > What I meant is that we don't call GetCurrentCommandId() in > standard_ExecutorStart(). Instead we get a new CID for every CTE with > INSERT/UPDATE/DELETE. That comment tried to point out the fact that > this strategy could fail if there was a non-SELECT query as the > top-level statement because we wouldn't increment the CID after the last > CTE. Right... Which I thought was more or less the recommendation? Guess Ill have to go re-read that discussion. > I did it this way because it works well for the purposes of this > patch and I didn't see an obvious way to determine whether we need a new > CID for the top-level statement or not. > > I'll send an updated patch in a couple of days. Peachy. -- 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] UTF8 with BOM support in psql
Peter Eisentraut writes: > I think I could support using the presence of the BOM as a fall-back > indicator of encoding in absence of any other declaration. It seems to > me, however, that the description above ignores the existence of > encodings other than SQL_ASCII and UTF8. Yeah. This entire proposal rests on the assumption that UTF8 is the only encoding that really matters, and introducing a possibility of breaking things for users of other encodings is acceptable damage. I do not think that supporting a deprecated-by-standards behavior is worth that. Even assuming that we had consensus on a behavior that involved silently changing client_encoding, I do not believe that it's practical to implement it in an acceptable fashion. Just issuing a SET behind the user's back will not work in a number of scenarios: * We are inside a transaction when \i is called, and the file contains a ROLLBACK. * We are inside a failed transaction when \i is called --- the SET won't even work at all. * Same two cases inside a savepoint. * The file contains a \c command. If you expect that the previous client_encoding should be restored at the end of the \i inclusion (as I certainly would) then you have the first three hazards at file end as well, except that now the odds of being inside a failed transaction are significantly higher. Also, what if the file contained a SET CLIENT_ENCODING command itself? How should that interact with this? Lastly, a silent change of client_encoding would also affect the encoding of notice and error messages that come out while the \i file is running. I fail to find that non-astonishing, either. I think that the only way this sort of behavior could be implemented without a bunch of broken corner cases would be if we put the responsibility of encoding conversion inside psql, so that switching its idea of the encoding was just a local change rather than something it had to ask the backend to do, and it could be careful to apply the encoding only to the data coming from the \i file. Which is possible, perhaps, but it hardly seems that slightly-more-convenient BOM handling is worth it. 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] Timezones (in 8.5?)
On Tue, Nov 17, 2009 at 10:21 AM, hernan gonzalez wrote: > Are there any plans to (is anybody working on) implement better > timezone support in postgresql > for 8.5 ? Specifically, store the timezone info -instead of just the > timestamp as UTC ? > http://wiki.postgresql.org/wiki/Todo#Dates_and_Times You might want to use the word "different" rather than the word "better", because the current behavior is quite useful and I think many people would be unhappy if it were to go away. I think there's also some debate about whether we want this at all. See here: http://archives.postgresql.org/pgsql-hackers/2009-09/msg00964.php One random thought - I am not aware that we currently have a "time zone" type in which to store a time zone in. Is there any value in having such a thing vs. just using varchar? ...Robert -- 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] Summary and Plan for Hot Standby
Robert Haas wrote: > On Mon, Nov 16, 2009 at 11:07 AM, Kevin Grittner > wrote: >> Tom Lane wrote: >> >>> I agree with Heikki that it would be better not to commit as long >>> as any clear showstoppers remain unresolved. >> >> I agree that it would be better not to commit as long as any of the >> following are true: >> >> (1) There are any known issues which would break things for >> clusters *not using* hot standby. >> >> (2) There isn't an easy way for to disable configuration of hot >> standby. >> >> (3) There is significant doubt that the vast majority of the patch >> will be useful in the eventually-enabled final solution. >> >> If none of these are true, I'm not sure what the down side of a >> commit is. > > Well, I think you wouldn't want to commit something that enabled Hot > Standby but caused Hot Standby queries to give wrong answers, or > didn't even allow some/all queries to be executed. That's fairly > pointless, and might mislead users into thinking we had a feature > when we really didn't. I might. Based on my project management experience and the tone of the posts on this feature, I suspect that there would be benefit to committing the code -- even if the ability to enable it was commented out. For starters, I suspect that most people won't be using it, so the most important thing for most users is that the patch breaks nothing when the feature is not configured. Also, if we have high confidence that the vast majority of this code will eventually be committed, and likely in 8.5, the sooner it is what people work against, the less likely that a late change will destabilize something. Having made that point, I'm happy to leave it to Heikki's judgment; it's definitely not something I care enough about to argue at any length -Kevin -- 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] Partitioning option for COPY
Tom Lane wrote: Emmanuel Cecchet writes: Tom Lane wrote: This looks like the patch is trying to create a data structure in a memory context that's not sufficiently long-lived for the use of the structure. If you do this in a non-cassert build, it will seem to work, some of the time, if the memory in question happens to not get reallocated to something else. I was using the CacheMemoryContext. Could someone tell me why this is wrong and what should have been the appropriate context to use? Well, (a) I doubt you really were creating the list in CacheMemoryContext, else it'd have not gotten clobbered; (b) creating statement-local data structures in CacheMemoryContext is entirely unacceptable anyway, because then they represent a permanent memory leak. Well I thought that this code would do it: child_table_lru = (OidLinkedList *)MemoryContextAlloc( + CacheMemoryContext, sizeof(OidLinkedList)); ... + /* Add the new entry in head of the list */ + new_head = (OidCell *) MemoryContextAlloc( + CacheMemoryContext, sizeof(OidCell)); The right context for statement-lifetime data structures is generally the CurrentMemoryContext the statement code is called with. Actually the list is supposed to stay around between statement executions. You don't want to restart with a cold cache at every statement so I really want this structure to stay in memory at a more global level. Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Timezones (in 8.5?)
Are there any plans to (is anybody working on) implement better timezone support in postgresql for 8.5 ? Specifically, store the timezone info -instead of just the timestamp as UTC ? http://wiki.postgresql.org/wiki/Todo#Dates_and_Times Hernán J. González -- 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] actualised funcs typmod patch
2009/11/17 Tom Lane : > Dimitri Fontaine writes: >> The idea to add support for typmods in function signatures came from the >> need to have more than one anyelement at a time in a function, and Pavel >> proposed that we spell that anyelement(0) and anyelement(1) e.g. > >> Is that how we want to solve it? > > TBD, really. It was one possibility. > >> Now, whatever the answer to that is, I guess the patch still has >> interest in itself for being able to have a function f(x numeric(10,4)) >> for example. Right? > > I think there are a large number of definitional details that would have > to be considered to determine exactly what that means or how things > should behave, and I'm quite distressed that Pavel seems to have taken a > code-first-think-later approach to this patch. > > The main stumbling block that we've run into in previous discussions is > that it's not tremendously useful to propagate typmod unless you have > some method for determining the appropriate *output* typmod for a > function; and just declaring it with a fixed typmod doesn't cover > anywhere near enough use-cases. A simple example is that concat'ing > varchar(20) and varchar(22) ought to yield varchar(42) --- in fact, that > is required by SQL spec --- but there's no possible way of determining > that without exceedingly type-specific and function-specific knowledge. > I recall some discussion of attaching parse-time "helper" functions to > every function that would embody such knowledge, but nobody particularly > wanted to follow through on that. typmod is applying on input and output too. When I working with varchar(20) inside some custom function, then using typmod in definition (input, result) eliminate some useless typmod conversions. Without typmods for result - non sql functions are blackbox - no body knows, what will be result. Without typmodes for input function cannot trust to outside, and have to recheck input. I thing so minimally for plpgsql programming (typmod support has a benefit), sure, only when people use typmode based types. > > If the only immediate benefit we'd get is multiple anyelement types, > I think we'd be much better advised to just create a few separate > types for that. Function typmods are a big can of worms and we should > not lock ourselves into a half-baked solution. > When I started work on typmod support, my motivation was only a multiple anyelement types. Now, I thing, so typmodes are more important than parametrized polymorphic type. Now, PL are dynamic with minimal static checks. Step by step the static checks are richer. I could to imagine future warning like "declared varchar(40), but potential content is varchar(50)". These controls are not possible without using typmodes in function (explicitly declared). > 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] Partitioning option for COPY
Emmanuel Cecchet writes: > Tom Lane wrote: >> This looks like the patch is trying to create a data structure in a >> memory context that's not sufficiently long-lived for the use of the >> structure. If you do this in a non-cassert build, it will seem to >> work, some of the time, if the memory in question happens to not >> get reallocated to something else. >> > I was using the CacheMemoryContext. Could someone tell me why this is > wrong and what should have been the appropriate context to use? Well, (a) I doubt you really were creating the list in CacheMemoryContext, else it'd have not gotten clobbered; (b) creating statement-local data structures in CacheMemoryContext is entirely unacceptable anyway, because then they represent a permanent memory leak. The right context for statement-lifetime data structures is generally the CurrentMemoryContext the statement code is called with. 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] Partitioning option for COPY
Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= writes: Program received signal SIGSEGV, Segmentation fault. 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 1821child_relation_id = child_oid_cell->oid_value; (gdb) p child_oid_cell $1 = (OidCell *) 0x7f7f7f7f This looks like the patch is trying to create a data structure in a memory context that's not sufficiently long-lived for the use of the structure. If you do this in a non-cassert build, it will seem to work, some of the time, if the memory in question happens to not get reallocated to something else. I was using the CacheMemoryContext. Could someone tell me why this is wrong and what should have been the appropriate context to use? Thanks Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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] Raising the geqo_threshold default
"Greg Sabino Mullane" writes: > Perhaps you mean the discussion from > July 6, 2009 started by Robert Haas? That was approximately 8000 > messages ago, and no consensus was reached that I could find. Precisely. > So I'd like to respectfully ask the hackers to consider raising the > default value from 12 to 16 (as Robert Haas first suggested) or > even higher (20?). Have you got any evidence whatsoever to back up those suggestions? (In particular, evidence that it's not going to cause serious performance degradations for some people.) Otherwise you're not advancing the discussion past where it left off. 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] actualised funcs typmod patch
Dimitri Fontaine writes: > The idea to add support for typmods in function signatures came from the > need to have more than one anyelement at a time in a function, and Pavel > proposed that we spell that anyelement(0) and anyelement(1) e.g. > Is that how we want to solve it? TBD, really. It was one possibility. > Now, whatever the answer to that is, I guess the patch still has > interest in itself for being able to have a function f(x numeric(10,4)) > for example. Right? I think there are a large number of definitional details that would have to be considered to determine exactly what that means or how things should behave, and I'm quite distressed that Pavel seems to have taken a code-first-think-later approach to this patch. The main stumbling block that we've run into in previous discussions is that it's not tremendously useful to propagate typmod unless you have some method for determining the appropriate *output* typmod for a function; and just declaring it with a fixed typmod doesn't cover anywhere near enough use-cases. A simple example is that concat'ing varchar(20) and varchar(22) ought to yield varchar(42) --- in fact, that is required by SQL spec --- but there's no possible way of determining that without exceedingly type-specific and function-specific knowledge. I recall some discussion of attaching parse-time "helper" functions to every function that would embody such knowledge, but nobody particularly wanted to follow through on that. If the only immediate benefit we'd get is multiple anyelement types, I think we'd be much better advised to just create a few separate types for that. Function typmods are a big can of worms and we should not lock ourselves into a half-baked solution. 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] patch - Report the schema along table name in a referential failure error message
George Gensure writes: > There's some tricky stuff in here to say the least. Doesn't look like > param names are kept anywhere past the parser - gonna have to have it > follow through a bunch of functions to reach > parse_(fixed|variable)_parameters. The p_post_columnref_hook you > alluded to will help once I have the names though, so thanks :) I'm not sure where you're looking, but I would think the place to start is with pulling the parameter names out of the pg_proc tuple in init_sql_fcache. 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
