Re: [HACKERS] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)
I, for one, think it would be great if the JSON datatype were all in core :-) However, if and how much JSON code should go into core is up for discussion. Thoughts, anyone? in my opinion: As soon as possible. Spinning PostgreSQL as the Ajax-enabled-database has many great uses. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night. -- 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] including backend ID in relpath of temp rels - updated patch
On Fri, Jul 23, 2010 at 10:05 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Thu, Jun 10, 2010 at 3:39 PM, Robert Haas robertmh...@gmail.com wrote: I believe that this patch will clear away one major obstacle to implementing global temporary and unlogged tables: it enables us to be sure of cleaning up properly after a crash without relying on catalog entries or XLOG. Based on previous discussions, however, I believe there is support for making this change independently of what becomes of that project, just for the benefit of having a more robust cleanup mechanism. Hi, i was looking at v3 of this patch... Ok, i like what you did in smgrextend, smgrwrite, and others... changing isTemp for skipFsync is more descriptive but i have a few questions, maybe is right what you did i only want to understand it: - you added this in include/storage/smgr.h, so why is safe to assume that if the backend != InvalidBackendId it must be a temp relation? +#define SmgrIsTemp(smgr) \ + ((smgr)-smgr_rnode.backend != InvalidBackendId) - you added a question like this if (rel-rd_backend == MyBackendId) in a few places... why are you assuming that? that couldn't be a new created relation (in current session of course)? is that safe? -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de 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] non-overlapping, consecutive partitions
On Fri, Jul 23, 2010 at 10:04:00PM +0200, Hans-Jürgen Schönig wrote: create table foo ( x date ); create table foo_2010 () INHERITS (foo) create table foo_2009 () INHERITS (foo) create table foo_2008 () INHERITS (foo) now we add constraints to make sure that data is only in 2008, 2009 and 2010. we assume that everything is indexed: SELECT * FROM foo ORDER BY bar will now demand an ugly sort for this data. this is not an option if you need more than a handful of rows ... I think the right way to approach this is to teach the planner about merge sorts. This is, if the planner has path to foo_* all ordered by the same key (because they have the same indexes) then it has a path to the UNION of those tables simply by merging the results of those paths. This would be fairly straight forward to implement I think, you may even be able to reuse the merge sort in the normal sort machinery. (You'll need to watch out for UNION vs UNION ALL.) The real advantage of this approach is that you no longer have to prove anything about the constraints or various datatypes and it is more general. Say you have partitioned by start_date but you want to sort by end_date, simple index scanning won't work while a merge sort will work beautifully. You're also not limited to how the partitioning machinery will eventually work. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
[HACKERS] psql \timing output supressed in quiet mode
In psql's quiet mode, the output of the \timing option is suppressed, except in the \copy command. That inconsistency should be fixed in any case. It seems to me that if I explicitly turn on timing, then that should be unaffected by the quiet mode. Comments? -- 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] non-overlapping, consecutive partitions
On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote: On Fri, Jul 23, 2010 at 10:04:00PM +0200, Hans-Jürgen Schönig wrote: create table foo ( x date ); create table foo_2010 () INHERITS (foo) create table foo_2009 () INHERITS (foo) create table foo_2008 () INHERITS (foo) now we add constraints to make sure that data is only in 2008, 2009 and 2010. we assume that everything is indexed: SELECT * FROM foo ORDER BY bar will now demand an ugly sort for this data. this is not an option if you need more than a handful of rows ... I think the right way to approach this is to teach the planner about merge sorts. This is, if the planner has path to foo_* all ordered by the same key (because they have the same indexes) then it has a path to the UNION of those tables simply by merging the results of those paths. This would be fairly straight forward to implement I think, you may even be able to reuse the merge sort in the normal sort machinery. (You'll need to watch out for UNION vs UNION ALL.) The real advantage of this approach is that you no longer have to prove anything about the constraints or various datatypes and it is more general. Say you have partitioned by start_date but you want to sort by end_date, simple index scanning won't work while a merge sort will work beautifully. You're also not limited to how the partitioning machinery will eventually work. Hope this helps, i think this is excellent input. i will do some research going into that direction. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- 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] [JDBC] Trouble with COPY IN
On Sat, 24 Jul 2010, James William Pye wrote: On Jul 23, 2010, at 7:11 AM, Tom Lane wrote: I can't help thinking that the JDBC driver must be being overly cute if this breaks it ... I was wondering the same thing when I first saw Kris' message. However, iff I understand what JDBC is trying to achieve, I don't think I would call it overly. Is this a problem because JDBC is trying to detect failures as early as possible during a COPY IN? Or, is it just JDBC's normal MO to always be reading? The JDBC driver reads server messages for multiple reasons. One of them is indeed to do early failure detection. Another is to pickup NoticeResponse messages to avoid a network buffer deadlock. If someone puts a trigger on the table you're copying data into that does RAISE NOTICE 'received row X' for each row, to avoid a full network buffer deadlock, the client must regularly read from the backend. So as we are reading along, supposing that we're still mid-copy, we get a command complete message. So this is possible to work around driver side by peeking into the network stream and delaying processing of the end of copy until the driver agrees that the copy is done, but I still maintain that this is a server bug. It is not OK for the server to assume that the client is done and move on, the client must tell the server what it wants done. Kris Jurka -- 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] review: psql: edit function, show function commands patch
On 23/07/10 20:55, Pavel Stehule wrote: Hello 2010/7/23 Jan Urbański wulc...@wulczer.org: On 21/07/10 14:43, Pavel Stehule wrote: Hello I am sending a actualised patch. OK, thanks. This time the only thing I'm not happy about is the error message from doing: \ef func 0 \e /etc/passwd xxx which gives: line number is unacceptable where I think it should do: \ef: line number is unacceptable \e: line number is unacceptable but that's too trivial to go through another round of review and I think the committer can fix this easily. The documentation likely needs some spelling fixes, but I'll leave that to a native English speaker. I'm setting this as ready for committer. Thanks, Jan -- 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] review: psql: edit function, show function commands patch
2010/7/25 Jan Urbański wulc...@wulczer.org: On 23/07/10 20:55, Pavel Stehule wrote: Hello 2010/7/23 Jan Urbański wulc...@wulczer.org: On 21/07/10 14:43, Pavel Stehule wrote: Hello I am sending a actualised patch. OK, thanks. This time the only thing I'm not happy about is the error message from doing: \ef func 0 \e /etc/passwd xxx which gives: line number is unacceptable where I think it should do: \ef: line number is unacceptable \e: line number is unacceptable but that's too trivial to go through another round of review and I think the committer can fix this easily. The documentation likely needs some spelling fixes, but I'll leave that to a native English speaker. I'm setting this as ready for committer. Thank you very much Pavel Thanks, Jan -- 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] including backend ID in relpath of temp rels - updated patch
On Sun, Jul 25, 2010 at 2:37 AM, Jaime Casanova ja...@2ndquadrant.com wrote: but i have a few questions, maybe is right what you did i only want to understand it: - you added this in include/storage/smgr.h, so why is safe to assume that if the backend != InvalidBackendId it must be a temp relation? +#define SmgrIsTemp(smgr) \ + ((smgr)-smgr_rnode.backend != InvalidBackendId) That's pretty much the whole point of the patch. Instead of identifying relations as simply temporary or not temporary, we identify as a temporary relation owned by backend X or as not temporary. - you added a question like this if (rel-rd_backend == MyBackendId) in a few places... why are you assuming that? that couldn't be a new created relation (in current session of course)? is that safe? Again, rd_backend is not the creating backend ID unless the relation is a temprel. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] non-overlapping, consecutive partitions
2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote: On Fri, Jul 23, 2010 at 10:04:00PM +0200, Hans-Jürgen Schönig wrote: create table foo ( x date ); create table foo_2010 () INHERITS (foo) create table foo_2009 () INHERITS (foo) create table foo_2008 () INHERITS (foo) now we add constraints to make sure that data is only in 2008, 2009 and 2010. we assume that everything is indexed: SELECT * FROM foo ORDER BY bar will now demand an ugly sort for this data. this is not an option if you need more than a handful of rows ... I think the right way to approach this is to teach the planner about merge sorts. This is, if the planner has path to foo_* all ordered by the same key (because they have the same indexes) then it has a path to the UNION of those tables simply by merging the results of those paths. This would be fairly straight forward to implement I think, you may even be able to reuse the merge sort in the normal sort machinery. (You'll need to watch out for UNION vs UNION ALL.) The real advantage of this approach is that you no longer have to prove anything about the constraints or various datatypes and it is more general. Say you have partitioned by start_date but you want to sort by end_date, simple index scanning won't work while a merge sort will work beautifully. You're also not limited to how the partitioning machinery will eventually work. Hope this helps, i think this is excellent input. i will do some research going into that direction. Greg Stark had a patch to do this a while back called merge append, but it never got finished... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gincostestimate
On 02/07/10 14:33, Teodor Sigaev wrote:
Patch implements much more accuracy estimation of cost for GIN index
scan than generic cost estimation function.
Hi,
I'm reviewing this patch, and to begin with it I tried to reproduce the
problem that originally came up on -performance in
http://archives.postgresql.org/pgsql-performance/2009-10/msg00393.php
The links from that mail are now dead, so I set up my own test environment:
* one table testfts(id serial, body text, body_fts tsvector)
* 5 rows, each with 1000 random words taken from
/usr/share/dict/british-english-insane (the wbritish-insane Debian
package) separated by a single space
* each row also had the word commonterm at the end, 80% had
commonterm80, 60% had commonterm60 etc (using the same methodology as
Jesper, that commonterm60 can appear only if commonterm80 is in the row)
* a GIN index on the tsvectors
I was able to reproduce his issue, that is: select id from ftstest where
body_fts @@ to_tsquery('commonterm80'); was choosing a sequential scan,
which was resulting in much longer execution than the bitmap index plan
that I got after disabling seqscans.
I then applied the patch, recompiled PG and tried again... and nothing
changed. I first tried running ANALYSE and then dropping and recreating
the GIN index, but the planner still chooses the seq scan.
Full explains below (the NOTICE is a debugging aid from the patch, which
I temporarily enabled to see if it's picking up the code).
I'll continue reading the code and trying to understand what it does,
but in the meantime: am I doing something wrong that I don't see the
planner switching to the bitmap index plan? I see that the difference in
costs is small, so maybe I just need to tweak the planner knobs a bit?
Is the output below expected?
Cheers,
Jan
wulczer=# explain analyse select id from ftstest where body_fts @@
to_tsquery('commonterm80');
NOTICE: GIN stats: nEntryPages: 49297.00 nDataPages: 16951.00
nPendingPages :0.00 nEntries: 277521.00
QUERY PLAN
--
Seq Scan on ftstest (cost=0.00..1567.00 rows=39890 width=4) (actual
time=221.893..33179.794 rows=39923 loops=1)
Filter: (body_fts @@ to_tsquery('commonterm80'::text))
Total runtime: 33256.661 ms
(3 rows)
wulczer=# set enable_seqscan to false;
SET
Time: 0.257 ms
wulczer=# explain analyse select id from ftstest where body_fts @@
to_tsquery('commonterm80');
NOTICE: GIN stats: nEntryPages: 49297.00 nDataPages: 16951.00
nPendingPages :0.00 nEntries: 277521.00
QUERY PLAN
Bitmap Heap Scan on ftstest (cost=449.15..1864.50 rows=39890 width=4)
(actual time=107.421..181.284 rows=39923 loops=1)
Recheck Cond: (body_fts @@ to_tsquery('commonterm80'::text))
- Bitmap Index Scan on ftstest_gin_idx (cost=0.00..439.18
rows=39890 width=0) (actual time=97.057..97.057 rows=39923 loops=1)
Index Cond: (body_fts @@ to_tsquery('commonterm80'::text))
Total runtime: 237.218 ms
(5 rows)
Time: 237.999 ms
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \timing output supressed in quiet mode
On Sun, Jul 25, 2010 at 5:58 AM, Peter Eisentraut pete...@gmx.net wrote: In psql's quiet mode, the output of the \timing option is suppressed, except in the \copy command. That inconsistency should be fixed in any case. It seems to me that if I explicitly turn on timing, then that should be unaffected by the quiet mode. Comments? +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TwoPO: experimental join order algorithm
On Sat, Jul 24, 2010 at 9:20 AM, Adriano Lange alange0...@gmail.com wrote: I'd like to release the last version of my experimental join order algorithm (TwoPO - Two Phase Optimization [1]): http://git.c3sl.ufpr.br/gitweb?p=lbd/ljqo.git;a=summary This algorithm is not production-ready, but an experimental set of ideas, which need to be refined and evaluated. As the join order optimization is a hard problem, the evaluation of a search strategy is also a hard task. Therefore, I think the most important TODO item related to replacement of GEQO algorithm is to define a set of evaluation criteria considered as relevant. As you may know, we're in the middle of a CommitFest right now; I'd suggest adding this patch to the next one. https://commitfest.postgresql.org/action/commitfest_view/open Someone might have time to look at it sooner, but at least if you add it here we'll not lose track of it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] non-overlapping, consecutive partitions
2010/7/25 Robert Haas robertmh...@gmail.com: 2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote: I think the right way to approach this is to teach the planner about merge sorts. For what it's worth I think this is a belt-and-suspenders type of situation where we want two solutions which overlap somewhat. I would really like to have merge-append nodes because there are all sorts of plans where append nodes destroying the ordering of their inputs eliminates a lot of good plans. Those cases can be UNION ALL nodes, or partitions where there's no filter on the partition key at all. But for partitioned tables like the OPs the real solution would be to have more structured meta-data about the partitions that allows the planner to avoid needing the merge at all. It would also means the planner wouldn't need to look at every node; it could do a binary search or equivalent for the right partitions. Greg Stark had a patch to do this a while back called merge append, but it never got finished... I was basically in over my head with the planner. I don't understand how equivalent classes are used or should be used and didn't understand the code I was pointed at as being analogous. It's probably not so complicated as all that, but I never really wrapped my head around it and moved onto tasks I could make more progress on. -- greg -- 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] TwoPO: experimental join order algorithm
Em 25-07-2010 17:44, Robert Haas escreveu: On Sat, Jul 24, 2010 at 9:20 AM, Adriano Lange alange0...@gmail.com wrote: I'd like to release the last version of my experimental join order algorithm (TwoPO - Two Phase Optimization [1]): http://git.c3sl.ufpr.br/gitweb?p=lbd/ljqo.git;a=summary This algorithm is not production-ready, but an experimental set of ideas, which need to be refined and evaluated. As the join order optimization is a hard problem, the evaluation of a search strategy is also a hard task. Therefore, I think the most important TODO item related to replacement of GEQO algorithm is to define a set of evaluation criteria considered as relevant. As you may know, we're in the middle of a CommitFest right now; I'd suggest adding this patch to the next one. https://commitfest.postgresql.org/action/commitfest_view/open Someone might have time to look at it sooner, but at least if you add it here we'll not lose track of it. Yes, I know. This is only a notice, not a patch. As I said, this algorithm is experimental, which do not match with the CommitFest life cycle. -- Adriano Lange -- 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] non-overlapping, consecutive partitions
On Sun, Jul 25, 2010 at 6:40 PM, Greg Stark gsst...@mit.edu wrote: 2010/7/25 Robert Haas robertmh...@gmail.com: 2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote: I think the right way to approach this is to teach the planner about merge sorts. For what it's worth I think this is a belt-and-suspenders type of situation where we want two solutions which overlap somewhat. I would really like to have merge-append nodes because there are all sorts of plans where append nodes destroying the ordering of their inputs eliminates a lot of good plans. Those cases can be UNION ALL nodes, or partitions where there's no filter on the partition key at all. But for partitioned tables like the OPs the real solution would be to have more structured meta-data about the partitions that allows the planner to avoid needing the merge at all. It would also means the planner wouldn't need to look at every node; it could do a binary search or equivalent for the right partitions. Agreed on all points. Greg Stark had a patch to do this a while back called merge append, but it never got finished... I was basically in over my head with the planner. I don't understand how equivalent classes are used or should be used and didn't understand the code I was pointed at as being analogous. It's probably not so complicated as all that, but I never really wrapped my head around it and moved onto tasks I could make more progress on. Yeah, I don't fully understand those either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TwoPO: experimental join order algorithm
On Sun, Jul 25, 2010 at 6:45 PM, Adriano Lange alange0...@gmail.com wrote: Yes, I know. This is only a notice, not a patch. As I said, this algorithm is experimental, which do not match with the CommitFest life cycle. It matches just fine - you just want a review and some good feedback, rather than an actual commit. It's just... I don't have time to do that tonight. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add column if not exists (CINE)
On Fri, Jul 23, 2010 at 2:46 AM, Bernd Helmle maili...@oopsware.de wrote: Yes, i agree. I would like to mark this patch Ready for Committer, if that's okay for you (since you are a committer you might want to commit it yourself). I see that it is so marked, so, committed, with a minor correction to my original docs. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add column if not exists (CINE)
On Thu, Jul 22, 2010 at 4:34 PM, Kjell Rune Skaaraas kjell...@yahoo.no wrote: At least from a performance point of view CINE should never cause a table rewrite, it should either execute as a plain CREATE or as nothing. I don't mind if the CINE fails if the column already exists but with a different definition, so maybe it could be worded differently to make it clearer what you get? That's what I want, too. The people saying we should implement COR for columns seem to be, by and large, people who have never wished for this feature and have no particular use case for either one. I have stated my use case in the past, but it has been dismissed as stupid or contrived. I can live with the possibility that I'm dumb, but, for the record, I'm not making this up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TwoPO: experimental join order algorithm
Em 25-07-2010 19:17, Robert Haas escreveu: On Sun, Jul 25, 2010 at 6:45 PM, Adriano Lange alange0...@gmail.com wrote: Yes, I know. This is only a notice, not a patch. As I said, this algorithm is experimental, which do not match with the CommitFest life cycle. It matches just fine - you just want a review and some good feedback, rather than an actual commit. It's just... I don't have time to do that tonight. :-) :-) Just a notice of an experimental algorithm for now. I think there are no clear criteria for review this kind of algorithm yet. -- Adriano Lange -- 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 (for 9.1) string functions
I merged and enhanced some part of your patch:
- contrib/stringfunc are merged in the core patch
- Old format() is replaced with sprintf(), but the function name is
still format().
- Support %q as alias for %iq.
2010/7/25 Pavel Stehule pavel.steh...@gmail.com:
fixed - it depends on INT64_FORMAT now.
I modified the code a bit not to expect 'll' or 'l'.
%lq ... literal quoted
%iq ... ident quoted
I also modified 'q' without specifier, i.e, %q is handled as same as %lq.
But I found there is a design issue in format() :
I prefer a current behave - RAISE statement uses same and it is not
reported as bug for ten years
I think RAISE is badly designed. Using % as a placeholder has a limitation
to format strings. For example, format() cannot work as concat():
SELECT format('%%', 123, 456) = ERROR
So, my proposal is renaming stringfunc//sprintf() to format(),
and moving it into the core. I think sprintf() is superior to format()
in every aspect; '%s%s' works as concat(), and '%s%%' can append
% without blanks.
Then, concat_ws() will be moved into core because contrib/stringfunc
only has the function now. In addition, I'd like to include the function for
the compatibility to MySQL. Also, concat() and concat_ws() can share
the implementation.
Comments?
--
Itagaki Takahiro
stringfunc_core-20100726.diff
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch (for 9.1) string functions
2010/7/26 Itagaki Takahiro itagaki.takah...@gmail.com:
I merged and enhanced some part of your patch:
- contrib/stringfunc are merged in the core patch
- Old format() is replaced with sprintf(), but the function name is
still format().
- Support %q as alias for %iq.
2010/7/25 Pavel Stehule pavel.steh...@gmail.com:
fixed - it depends on INT64_FORMAT now.
I modified the code a bit not to expect 'll' or 'l'.
%lq ... literal quoted
%iq ... ident quoted
I also modified 'q' without specifier, i.e, %q is handled as same as %lq.
But I found there is a design issue in format() :
I prefer a current behave - RAISE statement uses same and it is not
reported as bug for ten years
I think RAISE is badly designed. Using % as a placeholder has a limitation
to format strings. For example, format() cannot work as concat():
SELECT format('%%', 123, 456) = ERROR
So, my proposal is renaming stringfunc//sprintf() to format(),
and moving it into the core. I think sprintf() is superior to format()
in every aspect; '%s%s' works as concat(), and '%s%%' can append
% without blanks.
Sorry, I am strong against. Using a format just for string string
concation is bad idea - there are a concat function, there are ||
operator. Look on complexity of format/RAISE and sprintf. More - it
can be strange, when we have a format function and it is almost
sprintf. I still prefer simplicity of format - you have a true - it
has a issue, but there are simply workaround
format('%', 123||345)
format is very simple - but usually you don't need to specify a with,
a precision.
sprintf has some issue based on common sprintf implementation and
expecting too. For example a precision is used very dynamically - it
has a different sense for integers and for floats, so I wouldn't have
a sprintf in core.
Then, concat_ws() will be moved into core because contrib/stringfunc
only has the function now. In addition, I'd like to include the function for
the compatibility to MySQL. Also, concat() and concat_ws() can share
the implementation.
Comments?
I disagree - please, return to prev variant
Regards
Pavel Stehule
--
Itagaki Takahiro
--
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 (for 9.1) string functions
2010/7/26 Pavel Stehule pavel.steh...@gmail.com: sprintf has some issue based on common sprintf implementation and expecting too. For example a precision is used very dynamically - it has a different sense for integers and for floats, so I wouldn't have a sprintf in core. Why do we need to have similar functions in core and contrib? It will just confuse users. If you want to RAISE-version of format(), I don't want to have stringfunc in contrib. sprintf() is cool! So, I'd like to use sprintf() by default rather than format() which has limited features. Almost all users don't know well about contrib modules. Books about functions in inter-databases don't consider about postgres' contrib modules. That's why I want to move the useful features into core rather than contrib modules. -- Itagaki Takahiro -- 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 (for 9.1) string functions
2010/7/26 Itagaki Takahiro itagaki.takah...@gmail.com: 2010/7/26 Pavel Stehule pavel.steh...@gmail.com: sprintf has some issue based on common sprintf implementation and expecting too. For example a precision is used very dynamically - it has a different sense for integers and for floats, so I wouldn't have a sprintf in core. Why do we need to have similar functions in core and contrib? It will just confuse users. If you want to RAISE-version of format(), I don't want to have stringfunc in contrib. :( please, look back to discus about this module. There was desided, so format will be in core and sprintf in contrib. One reason for this decision was complexity of printf's implementation. sprintf() is cool! So, I'd like to use sprintf() by default rather than format() which has limited features. Almost all users don't know well about contrib modules. Books about functions in inter-databases don't consider about postgres' contrib modules. That's why I want to move the useful features into core rather than contrib modules. I have a different opinion and I am not alone. sprintf is good for c language, but it is problematic in scripting environments, where are not pointers, where we have more info about variables - where we can use a introspection - it is like dinosaurus in IT. My implementation is little bit simple, bacause it is use a buildin functionality - but still it has more then hundred rows. The full implementation has about thousand rows. More sprintf is little bit slower than format - it have to do little bit more work - and it can be confusing for people who doesn't known it well. for example - sprintf(%d, 10.2) --- 10. next - sprintf respect common standard - but this standard doesn't calculate with PostgreSQL datatypes - there are not support for date, timestemp for example. Function format is designed to work with builtin function to_char. This is simple and full functional combination - I have not a plan to replace it. Regards Pavel Stehule -- Itagaki Takahiro -- 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] multibyte-character aware support for function downcase_truncate_identifier()
Since discussion stopped in discussion thread http://archives.postgresql.org/pgsql-bugs/2006-09/msg00128.php Are there any implications of this change in handling identifiers ? Thanks Regards, Rajanikant Chirmade On Tue, Jul 13, 2010 at 12:10 AM, Rajanikant Chirmade rajanikant.chirm...@enterprisedb.com wrote: On Wed, Jul 7, 2010 at 7:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Rajanikant Chirmade rajanikant.chirm...@enterprisedb.com writes: Every identifier is downcase truncated by function downcase_truncate_identifier() before using it. But since the function downcase_truncate_identifier() is not multibyte-charecter aware, it is not able to downcase some of special charecters in identifier like my_SchemÄ. IIRC this is intentional. Please consult the archives for previous discussions. regards, tom lane I got one discussion thread on same issue. But it stopped without any conclusion. http://archives.postgresql.org/pgsql-bugs/2006-09/msg00128.php Thanks Regards, Rajanikant Chirmade.
