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 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 : >> 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 : > On 23/07/10 20:55, Pavel Stehule wrote: >> Hello >> >> 2010/7/23 Jan Urbański : >>> 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 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 : > > 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 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 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 : > 2010/7/25 PostgreSQL - Hans-Jürgen Schönig : >> >> 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 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 wrote: > 2010/7/25 Robert Haas : >> 2010/7/25 PostgreSQL - Hans-Jürgen Schönig : >>> >>> 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 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 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 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 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 :
> 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 :
> 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 :
>> 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 : > 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 :
> 2010/7/26 Pavel Stehule :
>> 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 wrote: > >> Rajanikant Chirmade 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. > > > >
Re: [HACKERS] bg worker: overview
Hey Dimitri, On 07/24/2010 07:26 PM, Dimitri Fontaine wrote: Trying to figure out how it would fit the PGQ and pgagent needs. But maybe user defined daemons should be sub-coordinators (I used to think about them as "supervisors") able to talk to the coordinator to get a backend connected to some given database and distribute work to it. Hm.. sounds like an awful lot of work to me, but if you need the separation and security of a separate process... To simplify, you might want to start a bgworker on database 'postgres', which then acts as a sub-coordinator (and doesn't really need to use its database connection). You're using iMessage as the data exchange, how are you doing the work distribution? What do you use to tell the backend what is the processing you're interrested into? Well, there are different types of imessages defined in imsg.h. If you are coding something within Postgres, you'd just add all the required messages types there. There's no such thing as an external registration for new message types. For example, for autovacuum, there are two message types: IMSGT_PERFORM_VACUUM, that's sent from the coordinator to a bgworker, and initiates a vacuum job there. Then there's IMSGT_FORCE_VACUUM, which is sent from a backend to the coordinator to inform it that a certain database urgently needs vacuuming. For Postgres-R, things are a bit more complicated. The first IMSGT_CSET messages starts the application of a remote transaction. Further IMSGT_CSET messages may follow. The IMSGT_ORDERING message finally completes the job. So, imessage types cannot be mapped to jobs directly. See include/postmaster/coordinator.h, enum worker_state. Those are the possible states a worker can be in (job types). Adding a job would consist of adding a worker_state, plus at least one imessage type. Once the worker is done with its job, it returns IMSGT_READY to the coordinator. I'm open to refinements, such as assigning a certain range of message types to external use or some such. However, have no idea how to avoid clashing message type ids, then. Maybe those should still be part of imsg.h? Go ahead, re-use the background workers. That's what I've published them for Hehe :) The aim of this thread would be to have your input as far as designing an API would go, now that we're about on track as to what the aim is. Oh, sure. :-) In very short, the idea is a clock that ticks and associate current_txid() to now(), so that you're able to say "give me 3s worth of transactions activity from this queue". It then provides facilities to organise a queue into batches at consumer request, and for more details, see there: http://github.com/markokr/skytools-dev/blob/master/sql/ticker/pgqd.c http://github.com/markokr/skytools-dev/blob/master/sql/ticker/ticker.c Okay, thanks for the pointers. However, comments are relatively sparse in there as well... But the important thing as far as making it a child of the coordinator goes would be, I guess, that it's some C code running as a deamon and running SQL queries from time to time. The SQL queries are calling C user defined functions, provided by the PGQ backend module. You could certainly define jobs, which don't ever terminate. And calling SQL queries certainly sounds more like a background job to me, than something belonging to the sphere of the coordinator. Sorry, if my first impulse has been misleading. So, the bgworker infrastructure could probably satisfy the internal communication needs. But how does this ticker daemon talk to the outside? Does it need to open a socket and listen there? Or do the requests to that queue come in via SQL? Regards Markus -- 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] Synchronous replication
On Thu, Jul 22, 2010 at 5:37 PM, Yeb Havinga wrote: > Fujii Masao wrote: >> >> How should the synchronous replication behave when the number of connected >> standby servers is less than quorum? >> >> 1. Ignore quorum. The current patch adopts this. If the ACKs from all >> connected standbys have arrived, transaction commit is successful >> even if the number of standbys is less than quorum. If there is no >> connected standby, transaction commit always is successful without >> regard to quorum. >> >> 2. Observe quorum. Aidan wants this. Until the number of connected >> standbys has become more than or equal to quorum, transaction commit >> waits. >> >> Which is the right behavior of quorum commit? Or we should add new >> parameter specifying the behavior of quorum commit? >> > > Initially I also expected the quorum to behave like described by > Aidan/option 2. OK. But some people (including me) would like to prevent the master from halting when the standby fails, so I think that 1. also should be supported. So I'm inclined to add new parameter specifying the behavior of quorum commit when the number of synchronous standbys becomes less than quorum. > Also, IMHO the name "quorom" is a bit short, like having > "maximum" but not saying a max_something. > > quorum_min_sync_standbys > quorum_max_sync_standbys What about quorum_standbys? > The question remains what are the sync standbys? Does it mean not-async? It's the standby which sets replication_mode to "recv", "fsync", or "replay". > Intuitively by looking at the enumeration of replication_mode I'd think that > the sync standbys are all standby's that operate in a not async mode. That > would be clearer with a boolean sync (or not) and for sync standbys the > replication_mode specified. You mean that something like synchronous_replication as the recovery.conf parameter should be added in addition to replication_mode? Since increasing the number of similar parameters would confuse users, I don't like do that. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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
