Re: [HACKERS] [SQL] How would I get rid of trailing blank line?
Hi Andrew,
> Right. There's a simple pipeline way to get rid of it:
> psql -t -f query.sql | sed -e '$d' > query.out
Hi Scott,
> Tired of those blank lines in your text files? Grep them away:
> psql -tf query.sql mydatabase | grep -v "^$" > query.out
Thank you Both.
Regards,
Tena Sakai
tsa...@gallo.ucsf.edu
-Original Message-
From: Andrew Dunstan [mailto:and...@dunslane.net]
Sent: Thu 4/2/2009 6:34 PM
To: Tom Lane
Cc: Tena Sakai; pgsql-...@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [SQL] How would I get rid of trailing blank line?
Tom Lane wrote:
> "Tena Sakai" writes:
>
>> I often use a line like:
>> psql -tf query.sql mydatabase > query.out
>>
>
>
>> -t option gets rid of the heading and count
>> report at the bottom. There is a blank line
>> at the bottom, however. Is there any way to
>> have psql not give me that blank line?
>>
>
> Doesn't look like it --- the final fputc('\n', fout); seems to be
> done unconditionally in all the output formats. I wonder if we should
> change that? I'm afraid it might break programs that are used to it :-(
>
>
>
Right. There's a simple pipeline way to get rid of it:
psql -t -f query.sql | sed -e '$d' > query.out
cheers
andrew
Re: [HACKERS] a few crazy ideas about hash joins
Robert Haas wrote: While investigating some performance problems recently I've had cause to think about the way PostgreSQL uses hash joins. So here are a few thoughts. Some of these have been brought up before. 1. When the hash is not expected to spill to disk, it preserves the pathkeys of the outer side of the join. If the optimizer were allowed to assume that, it could produce significantly more efficient query plans in some cases. The problem is what to do if we start executing the query and find out that we have more stuff to hash than we expect, such that we need multiple batches? Now the results won't be sorted. I think we could handle this as follows: Don't count on the hash join to preserve pathkeys unless it helps, and only rely on it when it seems as if the hash table will still fit even if it turns out to be, say, three times as big as expected. But if you are counting on the hash join to preserve pathkeys, then pass that information to the executor. When the executor is asked to perform a hash join, it will first hash the inner side of the relation. At that point, we know whether we've succesfully gotten everything into a single batch, or not. If we have, perform the join normally. If the worst has happened and we've gone multi-batch, then perform the join and sort the output before returning it. The performance will suck, but at least you'll get the right answer. Previous in-passing reference to this idea here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg00806.php Hmm, instead of a sorting the output if the worst happens, a final merge step as in a merge sort would be enough. 2. Consider building the hash table lazily. I often see query planner pick a hash join over a nested inner indexscan because it thinks that it'll save enough time making hash probes rather than index probes to justify the time spent building the hash table up front. But sometimes the relation that's being hashed has a couple thousand rows, only a tiny fraction of which will ever be retrieved from the hash table. We can predict when this is going to happen because n_distinct for the outer column will be much less than the size of the inner rel. In that case, we could consider starting with an empty hash table that effectively acts as a cache. Each time a value is probed, we look it up in the hash table. If there's no entry, we use an index scan to find the matching rows and insert them into the hash table. Negative results must also be cached. Yeah, that would be quite nice. One problem is that our ndistinct estimates are not very accurate. 3. Avoid building the exact same hash table twice in the same query. This happens more often you'd think. For example, a table may have two columns creator_id and last_updater_id which both reference person (id). If you're considering a hash join between paths A and B, you could conceivably check whether what is essentially a duplicate of B has already been hashed somewhere within path A. If so, you can reuse that same hash table at zero startup-cost. That seems like a quite simple thing to do. But would it work for a multi-batch hash table? 4. As previously discussed, avoid hashing for distinct and then hashing the results for a hash join on the same column with the same operators. This seems essentially an extension of idea 3. -- 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
Re: [HACKERS] Documentation Update: Document pg_start_backup checkpoint behavior
Bruce Momjian wrote:
Michael Renner wrote:
+ processing. Unfortunately it's currently not possible to expedite
+ the checkpointing done by pg_start_backup.
I have combined the above patch with another change that reports a
checkpoint is taking place:
test=> select pg_start_backup('12');
NOTICE: performing checkpoint
pg_start_backup
-
0/220
(1 row)
Rather than deplore that you can't expedite the checkpoint, why don't we
just make it possible? It's trivial to do, and in hindsight I think we
should've implemented that option when we got smoothed checkpoints.
Let's just decide what the command should look like.
The first question is what the default behavior should be? We've seen
enough complaints and I've been bitten by that myself during development
of other stuff often enough that I think we should change the default to
immediate. From backwards-compatibility point of view, we shouldn't
change the default, but then again an immediate checkpoint was what you
got before 8.3.
For the interface, I can see two options:
1. New function
pg_start_backup('label') -> immediate checkpoint
pg_start_backup_lazy('label') -> lazy checkpoint
2. New argument
pg_start_backup('label') -> immediate checkpoint
pg_start_backup('label', false) -> immediate checkpoint
pg_start_backup('label', true) -> lazy checkpoint
The first looks nicer, IMHO, because the word 'lazy' makes it
self-documenting. In the second form, you have to look at the manual to
figure out what the 2nd argument does.
--
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
Re: [HACKERS] Documentation Update: Document pg_start_backup checkpoint behavior
Bruce Momjian writes:
> + ereport(NOTICE,
> + (errmsg("performing checkpoint")));
You've *got* to be kidding.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] patch for small omission in psql \? help
Happened to notice this while looking for something else; the \ef
command appears to be missing from \? output. Suggested patch below.
--
Andrew (irc:RhodiumToad)
Index: src/bin/psql/help.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.144
diff -c -r1.144 help.c
*** src/bin/psql/help.c 25 Mar 2009 13:15:55 - 1.144
--- src/bin/psql/help.c 3 Apr 2009 04:16:51 -
***
*** 175,180
--- 175,181
fprintf(output, _("Query Buffer\n"));
fprintf(output, _(" \\e [FILE] edit the query buffer (or file) with external editor\n"));
+ fprintf(output, _(" \\ef [FUNCNAME] edit a function definition with external editor\n"));
fprintf(output, _(" \\p show the contents of the query buffer\n"));
fprintf(output, _(" \\r reset (clear) the query buffer\n"));
#ifdef USE_READLINE
--
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] Documentation Update: Document pg_start_backup checkpoint behavior
Michael Renner wrote:
> Hi,
>
> small patch for the documentation describing the current pg_start_backup
> checkpoint behavior as per
> http://archives.postgresql.org//pgsql-general/2008-09/msg01124.php .
>
> Should we note down a TODO to revisit the current checkpoint handling?
>
> best regards,
> Michael
> diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
> index 02545f1..6ea9488 100644
> --- a/doc/src/sgml/backup.sgml
> +++ b/doc/src/sgml/backup.sgml
> @@ -737,12 +737,8 @@ SELECT pg_start_backup('label');
> (see the configuration parameter
> ). Usually
> this is what you want because it minimizes the impact on query
> - processing. If you just want to start the backup as soon as
> - possible, execute a CHECKPOINT command
> - (which performs a checkpoint as quickly as possible) and then
> - immediately execute pg_start_backup. Then there
> - will be very little for pg_start_backup's checkpoint
> - to do, and it won't take long.
> + processing. Unfortunately it's currently not possible to expedite
> + the checkpointing done by pg_start_backup.
>
>
>
I have combined the above patch with another change that reports a
checkpoint is taking place:
test=> select pg_start_backup('12');
NOTICE: performing checkpoint
pg_start_backup
-
0/220
(1 row)
Patch attached.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/backup.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.123
diff -c -c -r2.123 backup.sgml
*** doc/src/sgml/backup.sgml 5 Mar 2009 19:50:03 - 2.123
--- doc/src/sgml/backup.sgml 3 Apr 2009 03:35:42 -
***
*** 737,748
(see the configuration parameter
). Usually
this is what you want because it minimizes the impact on query
! processing. If you just want to start the backup as soon as
! possible, execute a CHECKPOINT command
! (which performs a checkpoint as quickly as possible) and then
! immediately execute pg_start_backup. Then there
! will be very little for pg_start_backup's checkpoint
! to do, and it won't take long.
--- 737,744
(see the configuration parameter
). Usually
this is what you want because it minimizes the impact on query
! processing. Unfortunately it's currently not possible to expedite
! the checkpointing done by pg_start_backup.
Index: src/backend/access/transam/xlog.c
===
RCS file: /cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.334
diff -c -c -r1.334 xlog.c
*** src/backend/access/transam/xlog.c 11 Mar 2009 23:19:24 - 1.334
--- src/backend/access/transam/xlog.c 3 Apr 2009 03:35:42 -
***
*** 6977,6982
--- 6977,6984
/* Ensure we release forcePageWrites if fail below */
PG_ENSURE_ERROR_CLEANUP(pg_start_backup_callback, (Datum) 0);
{
+ ereport(NOTICE,
+ (errmsg("performing checkpoint")));
/*
* Force a CHECKPOINT. Aside from being necessary to prevent torn
* page problems, this guarantees that two successive backup runs will
--
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] More message encoding woes
Hiroshi Inoue wrote: Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas writes: Tom Lane wrote: Maybe use a special string "Translate Me First" that doesn't actually need to be end-user-visible, just so no one sweats over getting it right in context. Yep, something like that. There seems to be a magic empty string translation at the beginning of every po file that returns the meta-information about the translation, like translation author and date. Assuming that works reliably, I'll use that. At first that sounded like an ideal answer, but I can see a gotcha: suppose the translation's author's name contains some characters that don't convert to the database encoding. I suppose that would result in failure, when we'd prefer it not to. A single-purpose string could be documented as "whatever you translate this to should be pure ASCII, never mind if it's sensible". I just tried that, and it seems that gettext() does transliteration, so any characters that have no counterpart in the database encoding will be replaced with something similar, or question marks. Assuming that's universal across platforms, and I think it is, using the empty string should work. It also means that you can use lc_messages='ja' with server_encoding='latin1', but it will be unreadable because all the non-ascii characters are replaced with question marks. For something like lc_messages='es_ES' and server_encoding='koi8-r', it will still look quite nice. Attached is a patch I've been testing. Seems to work quite well. It would be nice if someone could test it on Windows, which seems to be a bit special in this regard. Unfortunately it doesn't seem to work on Windows. Is it unappropriate to call iconv_open() to check if the codeset is valid for bind_textdomain_codeset()? regards, Hiroshi Inoue -- 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] benchmarking the query planner
On Thu, Mar 19, 2009 at 4:04 AM, ITAGAKI Takahiro
wrote:
> Robert Haas wrote:
>> >> Works for me. Especially if you want to think more about ANALYZE before
>> >> changing that.
>> >
>> > Well, it's something that would be sane to contemplate adding in 8.4.
>> > It's way too late for any of this other stuff to happen in this release.
>>
>> I'm thinking about trying to implement this, unless someone else is
>> already planning to do it. I'm not sure it's practical to think about
>> getting this into 8.4 at this point, but it's worth doing whether it
>> does or not.
>
> Can we use get_relation_stats_hook on 8.4? The pg_statistic catalog
> will be still modified by ANALYZEs, but we can rewrite the statistics
> just before it is used.
>
> your_relation_stats_hook(root, rte, attnum, vardata)
> {
> Call default implementation;
> if (rte->relid = YourRelation && attnum = YourColumn)
> ((Form_pg_statistic) (vardata->statsTuple))->stadistinct =
> YourNDistinct;
> }
I don't know, can you run a query from inside the stats hook? It
sounds like this could be made to work for a hard-coded relation and
column, but ideally you'd like to get this data out of a table
somewhere.
I started implementing this by adding attdistinct to pg_attribute and
making it a float8, with 0 meaning "don't override the results of the
normal stats computation" and any other value meaning "override the
results of the normal stats computation with this value". I'm not
sure, however, whether I can count on the result of an equality test
against a floating-point zero to be reliable on every platform.It
also seems like something of a waste of space, since the only positive
values that are useful are integers (and presumably less than 2^31-1)
and the only negative values that are useful are > -1. So I'm
thinking about making it an integer, to be interpreted as follows:
0 => compute ndistinct normally
positive value => use this value for ndistinct
negative value => use this value * 10^-6 for ndistinct
Any thoughts?
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] a few crazy ideas about hash joins
While investigating some performance problems recently I've had cause to think about the way PostgreSQL uses hash joins. So here are a few thoughts. Some of these have been brought up before. 1. When the hash is not expected to spill to disk, it preserves the pathkeys of the outer side of the join. If the optimizer were allowed to assume that, it could produce significantly more efficient query plans in some cases. The problem is what to do if we start executing the query and find out that we have more stuff to hash than we expect, such that we need multiple batches? Now the results won't be sorted. I think we could handle this as follows: Don't count on the hash join to preserve pathkeys unless it helps, and only rely on it when it seems as if the hash table will still fit even if it turns out to be, say, three times as big as expected. But if you are counting on the hash join to preserve pathkeys, then pass that information to the executor. When the executor is asked to perform a hash join, it will first hash the inner side of the relation. At that point, we know whether we've succesfully gotten everything into a single batch, or not. If we have, perform the join normally. If the worst has happened and we've gone multi-batch, then perform the join and sort the output before returning it. The performance will suck, but at least you'll get the right answer. Previous in-passing reference to this idea here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg00806.php 2. Consider building the hash table lazily. I often see query planner pick a hash join over a nested inner indexscan because it thinks that it'll save enough time making hash probes rather than index probes to justify the time spent building the hash table up front. But sometimes the relation that's being hashed has a couple thousand rows, only a tiny fraction of which will ever be retrieved from the hash table. We can predict when this is going to happen because n_distinct for the outer column will be much less than the size of the inner rel. In that case, we could consider starting with an empty hash table that effectively acts as a cache. Each time a value is probed, we look it up in the hash table. If there's no entry, we use an index scan to find the matching rows and insert them into the hash table. Negative results must also be cached. 3. Avoid building the exact same hash table twice in the same query. This happens more often you'd think. For example, a table may have two columns creator_id and last_updater_id which both reference person (id). If you're considering a hash join between paths A and B, you could conceivably check whether what is essentially a duplicate of B has already been hashed somewhere within path A. If so, you can reuse that same hash table at zero startup-cost. 4. As previously discussed, avoid hashing for distinct and then hashing the results for a hash join on the same column with the same operators. http://archives.postgresql.org/message-id/4136ffa0902191346g62081081v8607f0b92c206...@mail.gmail.com Thoughts on the value and/or complexity of implementation of any of these? ...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] [SQL] How would I get rid of trailing blank line?
Tom Lane wrote:
"Tena Sakai" writes:
I often use a line like:
psql -tf query.sql mydatabase > query.out
-t option gets rid of the heading and count
report at the bottom. There is a blank line
at the bottom, however. Is there any way to
have psql not give me that blank line?
Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats. I wonder if we should
change that? I'm afraid it might break programs that are used to it :-(
Right. There's a simple pipeline way to get rid of it:
psql -t -f query.sql | sed -e '$d' > query.out
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] [SQL] How would I get rid of trailing blank line?
Hi Tom,
I am a bit surprised to hear that that '\n'
is there unconditionally. But I am sure
there are more pressing things for you to
work on. It's something I can live with.
Regards,
Tena Sakai
tsa...@gallo.ucsf.edu
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Thu 4/2/2009 4:01 PM
To: Tena Sakai
Cc: pgsql-...@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [SQL] How would I get rid of trailing blank line?
"Tena Sakai" writes:
> I often use a line like:
> psql -tf query.sql mydatabase > query.out
> -t option gets rid of the heading and count
> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?
Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats. I wonder if we should
change that? I'm afraid it might break programs that are used to it :-(
regards, tom lane
Re: [HACKERS] [SQL] How would I get rid of trailing blank line?
"Tena Sakai" writes:
> I often use a line like:
> psql -tf query.sql mydatabase > query.out
> -t option gets rid of the heading and count
> report at the bottom. There is a blank line
> at the bottom, however. Is there any way to
> have psql not give me that blank line?
Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats. I wonder if we should
change that? I'm afraid it might break programs that are used to 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
All, Please: A. Stop cc'ing this thread to the WWW list. B. Please change the topic of this thread to "Duration" or "Time types". --Josh Berkus -- 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] Additional DTrace Probes
Bruce Momjian writes: > After talking to Tom, I have reverted this patch. It does not contain > documentation, and are not properly placed, and was submitted in > February. I was hoping it would be an easy addition but obviously not > so please resubmit for 8.5. Sorry. Aside from the lack of documentation, I thought the executor probes were suffering from a lack of design clarity. If the intention was to probe every executor node, why did the patch miss a lot of node types? Why not solve it once and for all with a single probe in ExecProcNode()? Perhaps the idea was to make it less painful to trace the usage of particular node types, which is sensible, but then why did you put a probe in execScan rather than the individual calling scan-type nodes? Surely the ability to easily count seqscan vs indexscan would have to rank mighty high in any evaluation of whether it's easy to count particular node types. I was also pretty unhappy with passing the node pointers to the probes. What are probe routines realistically going to do with those? We feel free to whack the contents of executor node types around often, even in stable branches sometimes. I do *not* want to encourage probe authors to write code that depends on the contents of those struct types. (Heck, we probably shouldn't encourage them to assume the values of enum NodeTag even, so having a single probe in ExecProcNode is likely not a good solution. I will certainly not hold still for any future suggestions that we should avoid renumbering NodeTag or changing struct types because someone has written a probe that depends on it.) So this needs much more thought about where executor probes should go and what their arguments should be. I'm also a bit worried about the speed issue. The existing DTrace probes are tracking relatively expensive operations like I/O calls. We *know* that a kernel call per ExecProcNode iteration is highly expensive; see experience with EXPLAIN ANALYZE overhead on any platform that hasn't got a fast path for gettimeofday. I don't think that dropping probes into the executor loop is something we should do without careful analysis of what they're good for and whether anyone would really use them. 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] fix - function call with variadic parameter for type "any"
2009/4/2 Tom Lane : > Pavel Stehule writes: >> actually, there isn't any difference between a) and b) > >> a) select somevariadicwithany(10); >> b) select somevariadicwithany(variadic 10); > >> in this case the keyword VARIADIC is ignored. > > Well, in my mind what the VARIADIC keyword does is it prevents the parser > from building an ARRAY[] expression around the remaining parameters. > Which would be incorrect for a VARIADIC ANY function because such a > function presumably doesn't want to force all the actual arguments > to be the same type --- if it did, it could use VARIADIC ANYARRAY. > > VARIADIC ANY basically exists to allow an ANY-argument function to > accept any number of ANY parameters. As such, PG_NARGS() is all it > really needs to know, plus the already-existing support for obtaining > the parameters' actual datatypes. > > it can be good idea - I see only one disadvantage. I will have two functions with same body - but it isn't significant problem 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] Additional DTrace Probes
After talking to Tom, I have reverted this patch. It does not contain documentation, and are not properly placed, and was submitted in February. I was hoping it would be an easy addition but obviously not so please resubmit for 8.5. Sorry. --- bruce wrote: > > OK, updated patch attached and applied; thanks. > > --- > > Zdenek Kotala wrote: > > It looks OK now. > > > > Zdenek > > > > Dne 8.03.09 21:58, Robert Lor napsal(a): > > > > >> 2) Maybe > > >> > > >> TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(true, -1, -1); > > >> > > >> would be better. Because slru_errcause, slru_errno can contains garbage > > >> in situation when everything goes fine. Same for write. > > > > > > I've made the changes per your suggestion although one can argue that > > > the script can check arg0, and if it's true, avoid using arg1 and arg2 > > > as they are meaningless. > > > > Thanks. > > > > >> > > >> I think it is committable for 8.4. > > >> > > > > > > That would be awesome! > > > > yeah, but it depends on commiters :-) > > > > Zdenek > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] fix - function call with variadic parameter for type "any"
Pavel Stehule writes: > actually, there isn't any difference between a) and b) > a) select somevariadicwithany(10); > b) select somevariadicwithany(variadic 10); > in this case the keyword VARIADIC is ignored. Well, in my mind what the VARIADIC keyword does is it prevents the parser from building an ARRAY[] expression around the remaining parameters. Which would be incorrect for a VARIADIC ANY function because such a function presumably doesn't want to force all the actual arguments to be the same type --- if it did, it could use VARIADIC ANYARRAY. VARIADIC ANY basically exists to allow an ANY-argument function to accept any number of ANY parameters. As such, PG_NARGS() is all it really needs to know, plus the already-existing support for obtaining the parameters' actual datatypes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: If we expect a hash join to be performed in multiple batches,
On Thu, 2009-04-02 at 15:49 -0400, Tom Lane wrote: > Simon Riggs writes: > > Can we add "batches=N" to the EXPLAIN output for Hash and/or Hash Join? > > Are you talking about expected batches, or actual batches? Expected batches for EXPLAIN, both for EXPLAIN ANALYZE. > If the > former, would it be sufficient to distinguish "1" from "more than 1"? > If so, maybe changing the node title to "Batched Hash" would do. Hmmm, knowing the number of batches is beneficial since it helps you to calculate the required memory to get best performance. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] fix - function call with variadic parameter for type "any"
2009/4/2 Tom Lane : > Pavel Stehule writes: >> Our implementation of variadic parameters are not complete. The >> support of "any" type is incomplete. Modificator VARIADIC for funccall >> parameters needs transformation from ArrayExpr to standard parameters >> list. > > I don't think I agree with this change. The point of an ANY function > is that the function is going to do its own coping with the presented > arguments. This patch takes away the flexibility to do that and instead > enforces one very restrictive view of what the intended behavior is. ok, but then an function have to be noticed about using VARIADIC parameter's modificator. actually, there isn't any difference between a) and b) a) select somevariadicwithany(10); b) select somevariadicwithany(variadic 10); in this case the keyword VARIADIC is ignored. 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
On Thu, Apr 2, 2009 at 2:58 PM, Heikki Linnakangas wrote: > Also, it would be nice to generalize the thing so that it works not only > with intervals of time, but also floats, integers, numerics etc. The concept > of an interval is not really tied to timestamps, even though that's probably > the most common use case in the business world. Suddenly this thread has my undivided attention. A does-not-overlap operator would be awesome. A does-not-overlap index on a column whose value is a range would be awesome beyond words. As a simple example, consider an application whose job is to allocate subnets out of some larger IP block. Today, I typically handle cases of this type by defining triggers that generate the ends of the range and all the intermediate values and insert them into a side table with a unique index. It's really the pits, and unworkable for large ranges. ...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] Crash in gist insertion on pathological box data
Andrew Gierth writes: > I think that not only does there need to be another IS_BADRATIO check, > but also there needs to be some sort of backstop in gistSplit or > gistUserPicksplit to either recover or (as a last resort) error out > cleanly rather than crash the entire db in cases that would result in > infinite recursion. +1. This is not just a problem in one picksplit method, it's a generic hazard for all of them. The core code should be defending against a pathological split. 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] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:50 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane wrote: >>> If there's a camp that actually *wants* a NULL result for this case, >>> I missed the reasoning. > >> So that we don't break existing apps because of an issue that is >> trivial to work around. > > We would only be breaking them if a NULL result were actually the > correct behavior for the application's requirements, which seems > a bit unlikely. But that's completely untrue. If the most useful behavior is either ARRAY[''] or ARRAY[], then there are presumably lots and lots of people out there who have apps that do COALESCE(string_to_array(...), something). Whichever way you change string_to_array() will break all of the people doing this who wanted the opposite behavior for no good reason. ...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] Crash in gist insertion on pathological box data
> "Teodor" == Teodor Sigaev writes:
>> even further. And what confidence do you have that this change
>> eliminates all forms of the problem, anyway?
Teodor> Yes, I think. Because that part of code ( if (IS_BADRATIO)
Teodor> {...} ) is a corner case itself. In example from Andrew, all
Teodor> boxes are placed to one page because of floating-point
Teodor> rounding.
Yes, it's a corner case, but it arose in real-world data (the test
data set is contrived, but that's simply because it was the easiest
way to demonstrate the bug without access to the real data, which
had a much larger variation in box sizes).
Teodor> We could check IS_BADRATIO again and if it's just put one
Teodor> half of all boxes on one page and another half to the another
Teodor> page as it does if all boxes are equal. But FPeq() seemed to
Teodor> me a simpler solution and FP* comparisons are widely used in
Teodor> geometry.
I think that not only does there need to be another IS_BADRATIO check,
but also there needs to be some sort of backstop in gistSplit or
gistUserPicksplit to either recover or (as a last resort) error out
cleanly rather than crash the entire db in cases that would result in
infinite recursion.
--
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] Unsupported effective_io_concurrency platforms
Bruce Momjian wrote:
> Tom Lane wrote:
> > Peter Eisentraut writes:
> > > Joshua D. Drake wrote:
> > > Do we want to give a more informative error message, like "not supported
> > > on this platform?"
> >
> > > The trick will be to fit this into the GUC framework.
> >
> > You could do it by enforcing the limit in an assign hook, but I'm
> > not convinced it's worth the trouble.
>
> I have created a patch to at least display a more helpful message,
> without being specific:
>
> test=> set effective_io_concurrency = 1;
> ERROR: parameter "effective_io_concurrency" cannot be changed from 0
I fixed this an easier way, by making the parameter PGC_INTERNAL on
systems that don't have posix_fadvise().
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/misc/guc.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.497
diff -c -c -r1.497 guc.c
*** src/backend/utils/misc/guc.c 9 Mar 2009 14:34:34 - 1.497
--- src/backend/utils/misc/guc.c 2 Apr 2009 19:52:49 -
***
*** 1713,1719
},
{
! {"effective_io_concurrency", PGC_USERSET, RESOURCES,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
gettext_noop("For RAID arrays, this should be approximately the number of drive spindles in the array.")
},
--- 1713,1725
},
{
! {"effective_io_concurrency",
! #ifdef USE_PREFETCH
! PGC_USERSET,
! #else
! PGC_INTERNAL,
! #endif
! RESOURCES,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
gettext_noop("For RAID arrays, this should be approximately the number of drive spindles in the array.")
},
--
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] [COMMITTERS] pgsql: If we expect a hash join to be performed in multiple batches,
Simon Riggs writes: > Can we add "batches=N" to the EXPLAIN output for Hash and/or Hash Join? Are you talking about expected batches, or actual batches? If the former, would it be sufficient to distinguish "1" from "more than 1"? If so, maybe changing the node title to "Batched Hash" would do. 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] More message encoding woes
On Monday 30 March 2009 15:52:37 Heikki Linnakangas wrote: > What is happening is that gettext() returns the message in the encoding > determined by LC_CTYPE, while we expect it to return it in the database > encoding. Starting with PG 8.3 we enforce that the encoding specified in > LC_CTYPE matches the database encoding, but not for the C locale. > > In CVS HEAD, we call bind_textdomain_codeset() in SetDatabaseEncoding() > which fixes that, but we only do it on Windows. In earlier versions we > called it on all platforms, but only for UTF-8. It seems that we should > call bind_textdomain_codeset on all platforms and all encodings. > However, there seems to be a reason why we only do it for Windows on CVS > HEAD: we need a mapping from our encoding ID to the OS codeset name, and > the OS codeset names vary. > > How can we make this more robust? Another approach might be to create a new configuration parameter that basically tells what encoding to call bind_textdomain_codeset() with, say server_encoding_for_gettext. If that is not set, you just use server_encoding as is and hope that gettext() takes it (which it would in most cases, I guess). -- 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
On Thu, 2009-04-02 at 21:58 +0300, Heikki Linnakangas wrote: > > I have not yet implemented temporal join. > > That, and temporal union and difference. You have a union operator, but > that's not enough for a temporal union, as in: Ok, so you were talking about the relational operators, not interval predicates or interval operators. I agree that the relational operators are non-trivial. > Also, it would be nice to generalize the thing so that it works not only > with intervals of time, but also floats, integers, numerics etc. The > concept of an interval is not really tied to timestamps, even though > that's probably the most common use case in the business world. Yeah. I thought about how to do that with typmod, but it doesn't allow storing an entire OID for the constituent types. It may be possible to work around that. > > A big open question is whether we do new syntax, and if so, what. A lot > > of the literature for temporal types out there (from people basing their > > suggestions on SQL, like Snodgrass, et al., not C.J. Date) suggests > > syntax extensions which seem pretty specialized and unnecessary to me, > > but perhaps convenient. > > I can't imagine how you would implement temporal joins and unions > without syntax extensions. If there is a way, that would be great, > because that might allow us to implement them without backend changes. I still didn't know you were talking about relational operators at that point. Temporal join, union, difference, and also probably table logs all require syntax (not "require" maybe, but it would help a lot). The unnecessary syntax I was referring to is the SQL-ish syntax suggested by Snodgrass, et al, which involves words for things like "overlaps", which we really don't need. > > The only thing I really think needs better syntax is a constructor that > > can easily represent [ ), [ ], ( ), ( ] -- i.e. inclusive/exclusive. > > Right now I have 4 functions to do that, but it's awkward and overly > > verbose. > > Can't the input function handle those? Or you could have just one > constructor with an extra argument indicating whether each end of the > range is exclusive or inclusive. Constructing from a single string is easy. What happens when you want to say ( 2009-01-01, now() ], or pass a timestamptz from a table? Ideas welcome. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: If we expect a hash join to be performed in multiple batches,
On Thu, 2009-03-26 at 17:15 +, Tom Lane wrote: > Log Message: > --- > If we expect a hash join to be performed in multiple batches, suppress > "physical tlist" optimization on the outer relation (ie, force a projection > step to occur in its scan). This avoids storing useless column values when > the outer relation's tuples are written to temporary batch files. Can we add "batches=N" to the EXPLAIN output for Hash and/or Hash Join? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Path case sensitivity on windows
On Thursday 02 April 2009 18:29:45 Tom Lane wrote: > Hmm. Well, if we use pg_tolower then it will only do the right thing > for ASCII letters, but it seems like non-ASCII in the path leading to > the postgres binaries would be pretty dang unusual. Well, Windows localizes the directory names like C:\Program Files, so it is entirely plausible to have non-ASCII path names across the board in certain locales. -- 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] Any reason not to return row_count in cursor of plpgsql?
Thanks, patch applied. --- Andrew Gierth wrote: > > "Bruce" == Bruce Momjian writes: > > >> hi all, > >> > >> I read the code that it seems easy for the cursor in plpgsql to > >> return ROW_COUNT after MOVE LAST etc. The SPI_processed variable > >> already there, but didn't put it into estate structure, any reason > >> for that? > >> > >> thanks and best regards > > Bruce> Sorry, we have decided against this change because it might > Bruce> break existing applications. > > As they say on wikipedia, [citation needed] > > GET DIAGNOSTICS ROW_COUNT is documented as working for all commands; > if it doesn't work for MOVE (and FETCH), that's a bug. It might be one > that's not appropriate to backpatch, but that's no excuse for not > fixing it in a new release. > > It's especially egregious in that MOVE _does_ set FOUND. > > diff -c -r1.235 pl_exec.c > *** pl_exec.c 23 Feb 2009 10:03:22 - 1.235 > --- pl_exec.c 27 Mar 2009 10:44:08 - > *** > *** 3368,3373 > --- 3368,3375 > exec_set_found(estate, n != 0); > } > > + estate->eval_processed = n; > + > return PLPGSQL_RC_OK; > } > > -- > Andrew (irc:RhodiumToad) -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Crash in gist insertion on pathological box data
> "Tom" == Tom Lane writes: > Teodor Sigaev writes: >> Look at the patch, it fixes the problem by comparing for equality >> by FPeq() macros which is used everywhere in geometry calculation. Tom> Ick. FPeq() is a crock; I'd like to see us get rid of it, not Tom> spread it even further. And what confidence do you have that Tom> this change eliminates all forms of the problem, anyway? Here is a test case that crashes even with the patch: create table floats3(x float8, y float8); -- same badfloats.txt data as before \copy floats3 from 'badfloats.txt' update floats3 set x = x * pow(2::float8,33), y = y * pow(2::float8,33); create table boxes1 (b box); create index boxes1_idx on boxes1 using gist (b); insert into boxes1 select box(point(x,x),point(y,y)) as b from floats3; [crash] -- 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] [GENERAL] string_to_array with empty input
On Apr 2, 2009, at 11:24 AM, Sam Mason wrote:
Yes, I'd be tempted to pick one and go with it. It's seems a
completely
arbitrary choice one way or the other but the current behaviour is
certainly wrong.
I'd go with returning a zero element array because it would do
the "right thing" more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
"array_to_string" and it seems to do the "right" thing for the
majority
of the cases.
Forgive me if I'm missing something, but it seems to me that
array_to_string() works either way, no?
try=# select '"' || array_to_string('{}'::text[], ',') || '"'; ?column?
--
""
(1 row)
Time: 72.129 ms
try=# select '"' || array_to_string('{""}'::text[], ',') || '"';
?column?
--
""
(1 row)
Best,
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] Additional DTrace Probes
OK, updated patch attached and applied; thanks.
---
Zdenek Kotala wrote:
> It looks OK now.
>
> Zdenek
>
> Dne 8.03.09 21:58, Robert Lor napsal(a):
>
> >> 2) Maybe
> >>
> >> TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(true, -1, -1);
> >>
> >> would be better. Because slru_errcause, slru_errno can contains garbage
> >> in situation when everything goes fine. Same for write.
> >
> > I've made the changes per your suggestion although one can argue that
> > the script can check arg0, and if it's true, avoid using arg1 and arg2
> > as they are meaningless.
>
> Thanks.
>
> >>
> >> I think it is committable for 8.4.
> >>
> >
> > That would be awesome!
>
> yeah, but it depends on commiters :-)
>
> Zdenek
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/access/transam/slru.c
===
RCS file: /cvsroot/pgsql/src/backend/access/transam/slru.c,v
retrieving revision 1.45
diff -c -c -r1.45 slru.c
*** src/backend/access/transam/slru.c 1 Jan 2009 17:23:36 - 1.45
--- src/backend/access/transam/slru.c 2 Apr 2009 19:01:55 -
***
*** 57,62
--- 57,63
#include "storage/fd.h"
#include "storage/shmem.h"
#include "miscadmin.h"
+ #include "pg_trace.h"
/*
***
*** 372,377
--- 373,379
{
SlruShared shared = ctl->shared;
+ TRACE_POSTGRESQL_SLRU_READPAGE_START((uintptr_t)ctl, pageno, write_ok, xid);
/* Outer loop handles restart if we must wait for someone else's I/O */
for (;;)
{
***
*** 399,404
--- 401,407
}
/* Otherwise, it's ready to use */
SlruRecentlyUsed(shared, slotno);
+ TRACE_POSTGRESQL_SLRU_READPAGE_DONE(slotno);
return slotno;
}
***
*** 446,451
--- 449,455
SlruReportIOError(ctl, pageno, xid);
SlruRecentlyUsed(shared, slotno);
+ TRACE_POSTGRESQL_SLRU_READPAGE_DONE(slotno);
return slotno;
}
}
***
*** 470,475
--- 474,481
SlruShared shared = ctl->shared;
int slotno;
+ TRACE_POSTGRESQL_SLRU_READPAGE_READONLY((uintptr_t)ctl, pageno, xid);
+
/* Try to find the page while holding only shared lock */
LWLockAcquire(shared->ControlLock, LW_SHARED);
***
*** 511,516
--- 517,524
int pageno = shared->page_number[slotno];
bool ok;
+ TRACE_POSTGRESQL_SLRU_WRITEPAGE_START((uintptr_t)ctl, pageno, slotno);
+
/* If a write is in progress, wait for it to finish */
while (shared->page_status[slotno] == SLRU_PAGE_WRITE_IN_PROGRESS &&
shared->page_number[slotno] == pageno)
***
*** 525,531
--- 533,542
if (!shared->page_dirty[slotno] ||
shared->page_status[slotno] != SLRU_PAGE_VALID ||
shared->page_number[slotno] != pageno)
+ {
+ TRACE_POSTGRESQL_SLRU_WRITEPAGE_DONE();
return;
+ }
/*
* Mark the slot write-busy, and clear the dirtybit. After this point, a
***
*** 569,574
--- 580,587
/* Now it's okay to ereport if we failed */
if (!ok)
SlruReportIOError(ctl, pageno, InvalidTransactionId);
+
+ TRACE_POSTGRESQL_SLRU_WRITEPAGE_DONE();
}
/*
***
*** 593,598
--- 606,613
SlruFileName(ctl, path, segno);
+ TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_START((uintptr_t)ctl, path, pageno, slotno);
+
/*
* In a crash-and-restart situation, it's possible for us to receive
* commands to set the commit status of transactions whose bits are in
***
*** 607,612
--- 622,628
{
slru_errcause = SLRU_OPEN_FAILED;
slru_errno = errno;
+ TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno);
return false;
}
***
*** 614,619
--- 630,636
(errmsg("file \"%s\" doesn't exist, reading as zeroes",
path)));
MemSet(shared->page_buffer[slotno], 0, BLCKSZ);
+ TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(true, -1, -1);
return true;
}
***
*** 622,627
--- 639,645
slru_errcause = SLRU_SEEK_FAILED;
slru_errno = errno;
close(fd);
+ TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno);
return false;
}
***
*** 631,636
--- 649,655
slru_errcause = SLRU_READ_FAILED;
slru_errno = errno;
close(fd);
+ TRACE_POSTGRESQL_SLRU_READPAGE_PHYSICAL_DONE(false, slru_errcause, slru_errno);
return false;
}
***
*** 638,646
--- 657,668
{
slru_errcause = SLRU_CLOSE_FA
Re: [HACKERS] fix - function call with variadic parameter for type "any"
Pavel Stehule writes: > Our implementation of variadic parameters are not complete. The > support of "any" type is incomplete. Modificator VARIADIC for funccall > parameters needs transformation from ArrayExpr to standard parameters > list. I don't think I agree with this change. The point of an ANY function is that the function is going to do its own coping with the presented arguments. This patch takes away the flexibility to do that and instead enforces one very restrictive view of what the intended behavior is. 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
Jeff Davis wrote: On Thu, 2009-04-02 at 21:19 +0300, Heikki Linnakangas wrote: The data type itself is quite trivial. It's all the operators that are more difficult to implement, and also immensely useful. That part is still incomplete. Can you please let me know what you find lacking (note: the SVN repo is the most current one)? I've implemented a pretty standard set of operators, and a GiST opclass to make things like overlaps, etc., indexable. I have not yet implemented temporal join. That, and temporal union and difference. You have a union operator, but that's not enough for a temporal union, as in: SELECT 'foo', (10, 20) as when UNION temporal on when -- imaginary syntax.. SELECT 'foo', (15, 30) as when -> 'foo', (10, 30) Also, it would be nice to generalize the thing so that it works not only with intervals of time, but also floats, integers, numerics etc. The concept of an interval is not really tied to timestamps, even though that's probably the most common use case in the business world. I'd love to see that implemented. I volunteer to mentor if someone wants to tackle it. A big open question is whether we do new syntax, and if so, what. A lot of the literature for temporal types out there (from people basing their suggestions on SQL, like Snodgrass, et al., not C.J. Date) suggests syntax extensions which seem pretty specialized and unnecessary to me, but perhaps convenient. I can't imagine how you would implement temporal joins and unions without syntax extensions. If there is a way, that would be great, because that might allow us to implement them without backend changes. The only thing I really think needs better syntax is a constructor that can easily represent [ ), [ ], ( ), ( ] -- i.e. inclusive/exclusive. Right now I have 4 functions to do that, but it's awkward and overly verbose. Can't the input function handle those? Or you could have just one constructor with an extra argument indicating whether each end of the range is exclusive or inclusive. -- 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
Re: [HACKERS] Crash in gist insertion on pathological box data
even further. And what confidence do you have that this change
eliminates all forms of the problem, anyway?
Yes, I think. Because that part of code ( if (IS_BADRATIO) {...} ) is a corner
case itself. In example from Andrew, all boxes are placed to one page because of
floating-point rounding.
We could check IS_BADRATIO again and if it's just put one half of all boxes on
one page and another half to the another page as it does if all boxes are equal.
But FPeq() seemed to me a simpler solution and FP* comparisons are widely used
in geometry.
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
--
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] [GENERAL] string_to_array with empty input
Robert Haas writes: > On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane wrote: >> If there's a camp that actually *wants* a NULL result for this case, >> I missed the reasoning. > So that we don't break existing apps because of an issue that is > trivial to work around. We would only be breaking them if a NULL result were actually the correct behavior for the application's requirements, which seems a bit unlikely. 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] 8.4 open items list
Heikki Linnakangas writes: > Now, what about the idea of providing a shorthand LOCALE='foo', > mirroring --locale=foo initdb option? It seems like a good idea, because > you almost never want to set LC_COLLATE and LC_CTYPE differently. If we > do that, should LOCALE=foo also imply a per-database lc_messages, > lc_monetary, lc_numeric and lc_time settings? It seems like it should > for the sake of consistency. The comment upthread was that we can/should leave that for 8.5. I agree with that at this point. I think the above proposal is not as straightforward as it looks (in particular per-DB lc_messages has unpleasant implications for the postmaster log) and we should not tackle it in a hasty manner. 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
On Thu, 2009-04-02 at 21:19 +0300, Heikki Linnakangas wrote: > The data type itself is quite trivial. It's all the operators that are > more difficult to implement, and also immensely useful. That part is > still incomplete. Can you please let me know what you find lacking (note: the SVN repo is the most current one)? I've implemented a pretty standard set of operators, and a GiST opclass to make things like overlaps, etc., indexable. I have not yet implemented temporal join. > I'd recommend a book called Temporal Data and the > Relational Model by C.J. Date, Hugh Darwen and Nikos Lorentzos for > anyone interested in this topic. That book gives a guideline on how the > data type and operators should behave. Agreed! That is a _very_ good book, and it's what I based my PERIOD type on (I used to call it t_interval because I agree with Date that's a better word -- but the conflict with SQL was too great so I changed it). > I'd love to see that implemented. I volunteer to mentor if someone wants > to tackle it. A big open question is whether we do new syntax, and if so, what. A lot of the literature for temporal types out there (from people basing their suggestions on SQL, like Snodgrass, et al., not C.J. Date) suggests syntax extensions which seem pretty specialized and unnecessary to me, but perhaps convenient. The only thing I really think needs better syntax is a constructor that can easily represent [ ), [ ], ( ), ( ] -- i.e. inclusive/exclusive. Right now I have 4 functions to do that, but it's awkward and overly verbose. In a related topic, an index that can implement a non-overlapping constraint is important to temporal databases. I have done some implementation work on this already, based on my proposal here: http://archives.postgresql.org//pgsql-hackers/2008-06/msg00404.php and I have adjusted my design to address some of the concerns Tom brings up here: http://archives.postgresql.org//pgsql-hackers/2008-06/msg00427.php I already have some code written, so if anyone else is thinking of working on this please contact me first. I will post my progress in the next couple weeks. Regards, Jeff Davis -- 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] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane wrote: >>> Right at the moment, if we stick with the historical definition >>> of the function, *both* camps have to write out their choice of >>> the above. Seems like this is the worst of all possible worlds. >>> We should probably pick one or the other. > >> ISTM there are three camps. > > If there's a camp that actually *wants* a NULL result for this case, > I missed the reasoning. AFAICS we can either say that every application > is going to have to put in a CASE wrapper around this function, or say > that we'll make it do the right thing for some of them and the rest have > to put the same wrapper around it. So that we don't break existing apps because of an issue that is trivial to work around. ...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] about hacking postgresql
Hi, I don't know exactly what you want to do but you can begin by looking here http://neilconway.org/talks/hacking/ (in particular Handout with excerpts from patch). can you tell us more ? Amine abdelhak benmohamed a écrit : Hello, I am learning how to hack PostgreSQL, but it is still so difficult in fact, I am a novice! I am trying to change syntax of postgresql (for my project). I access to gram.y, I add the changes but I still receive message error (syntax error at near …). Is that only change to do or are their other steps to accomplish? I need your help It is very important for me to have your advices, help. Thanks very much. Friendly
Re: [HACKERS] Crash in gist insertion on pathological box data
Teodor Sigaev writes: > Look at the patch, it fixes the problem by comparing for equality by FPeq() > macros which is used everywhere in geometry calculation. Ick. FPeq() is a crock; I'd like to see us get rid of it, not spread it even further. And what confidence do you have that this change eliminates all forms of the problem, anyway? 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] 8.4 open items list
Bruce Momjian wrote:
Tom Lane wrote:
Magnus Hagander writes:
Tom Lane wrote:
Personally I think the naming decision is close enough to be a coin
toss, and so either choice is fine with me. However, I think it is
Clearly Unacceptable for createdb's switches to be spelled differently
than the underlying SQL command's options. So it's not really "let's
not change this" but "which one do you consider it more important to not
change"?
pgAdmin uses the SQL commands, not the external commands. IIRC the only
external commands that are used are pg_dump[all], pg_restore, pg_ctl and
initdb. Is initdb on the list of tools that might be changed?
Hm, that's a good point. initdb has these switches (and has had 'em for
a good long time):
--locale=LOCALE set default locale for new databases
--lc-collate=, --lc-ctype=, --lc-messages=LOCALE
--lc-monetary=, --lc-numeric=, --lc-time=LOCALE
set default locale in the respective category for
new databases (default taken from environment)
--no-locale equivalent to --locale=C
So createdb is consistent with longstanding history in initdb, and
that seems to mean that we should leave it alone and change
CREATE DATABASE to match (modulo underscore instead of dash).
Agreed, I see them back to Postgres 8.0:
8.0/pgsql/src/bin/initdb/initdb.c:
printf(_(" --lc-collate, --lc-ctype, --lc-messages=LOCALE\n"
Ok, it looks like we have a consensus on changing the CREATE DATABASE
options to LC_COLLATE and LC_CTYPE.
Now, what about the idea of providing a shorthand LOCALE='foo',
mirroring --locale=foo initdb option? It seems like a good idea, because
you almost never want to set LC_COLLATE and LC_CTYPE differently. If we
do that, should LOCALE=foo also imply a per-database lc_messages,
lc_monetary, lc_numeric and lc_time settings? It seems like it should
for the sake of consistency.
--
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
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 02, 2009 at 02:04:41PM -0400, Tom Lane wrote:
> A correct fix
> outside-the-function would look more like
>
> case when str = '' then '{}'::text[] else string_to_array(str, ',') end
>
> which should correctly yield NULL for NULL input and an empty array
> for empty input. Similarly, if someone wanted to force the
> single-empty-string result, they should do
>
> case when str = '' then '{""}'::text[] else string_to_array(str, ',') end
>
> which also still yields NULL if str is NULL.
>
> Right at the moment, if we stick with the historical definition
> of the function, *both* camps have to write out their choice of
> the above. Seems like this is the worst of all possible worlds.
> We should probably pick one or the other.
Yes, I'd be tempted to pick one and go with it. It's seems a completely
arbitrary choice one way or the other but the current behaviour is
certainly wrong.
I'd go with returning a zero element array because it would do
the "right thing" more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
"array_to_string" and it seems to do the "right" thing for the majority
of the cases.
--
Sam http://samason.me.uk/
--
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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
Josh Berkus wrote: On 4/2/09 8:48 AM, David E. Wheeler wrote: On Apr 2, 2009, at 7:20 AM, Steven Lembark wrote: Note that since the Internships are not required to be project code, we can also take student projects to contribute to our WWW infrastructure and other areas the project needs some work. Would introducing a Duration (i.e., time-series a'la Date, et al)) data type be considered useful? Jeff Davis has already done a lot of this work; it's on pgFOundry somewhere. The data type itself is quite trivial. It's all the operators that are more difficult to implement, and also immensely useful. That part is still incomplete. I'd recommend a book called Temporal Data and the Relational Model by C.J. Date, Hugh Darwen and Nikos Lorentzos for anyone interested in this topic. That book gives a guideline on how the data type and operators should behave. I'd love to see that implemented. I volunteer to mentor if someone wants to tackle it. -- 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
Re: [HACKERS] [GENERAL] string_to_array with empty input
Robert Haas writes: > On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane wrote: >> Right at the moment, if we stick with the historical definition >> of the function, *both* camps have to write out their choice of >> the above. Seems like this is the worst of all possible worlds. >> We should probably pick one or the other. > ISTM there are three camps. If there's a camp that actually *wants* a NULL result for this case, I missed the reasoning. AFAICS we can either say that every application is going to have to put in a CASE wrapper around this function, or say that we'll make it do the right thing for some of them and the rest have to put the same wrapper around 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] Crash in gist insertion on pathological box data
The nature of the problem is this: if gist_box_picksplit doesn't find a good disposition on the first try, then it tries to split the data again based on the positions of the box centers. But there's a problem here with floating-point rounding; it's possible for the average of N Look at the patch, it fixes the problem by comparing for equality by FPeq() macros which is used everywhere in geometry calculation. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ gist.patch.gz Description: Unix tar archive -- 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] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane wrote: > Right at the moment, if we stick with the historical definition > of the function, *both* camps have to write out their choice of > the above. Seems like this is the worst of all possible worlds. > We should probably pick one or the other. ISTM there are three camps. ...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] hstore bug and repair method
Thank you, fixed I’ve found a bug about hstore, example below: -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] [GENERAL] string_to_array with empty input
"David E. Wheeler" writes:
>> Or we could stick to the current behavior and say "use COALESCE() to
>> resolve the ambiguity, if you need to".
> Steve has a point that leaving it as-is leaves it as impossible to
> tell the difference between string_to_array(NULL, ',') and
> string_to_array('', ','). The former properly handles an unknown
> value, while the latter, where '' is a known value, seems weird to be
> returning NULL.
Yeah, COALESCE is an abuse of a convenient notation, which will fall
over if you also want NULL to yield NULL. A correct fix
outside-the-function would look more like
case when str = '' then '{}'::text[] else string_to_array(str, ',') end
which should correctly yield NULL for NULL input and an empty array
for empty input. Similarly, if someone wanted to force the
single-empty-string result, they should do
case when str = '' then '{""}'::text[] else string_to_array(str, ',') end
which also still yields NULL if str is NULL.
Right at the moment, if we stick with the historical definition
of the function, *both* camps have to write out their choice of
the above. Seems like this is the worst of all possible worlds.
We should probably pick one or the other.
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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
On 4/2/09 8:48 AM, David E. Wheeler wrote: On Apr 2, 2009, at 7:20 AM, Steven Lembark wrote: Note that since the Internships are not required to be project code, we can also take student projects to contribute to our WWW infrastructure and other areas the project needs some work. Would introducing a Duration (i.e., time-series a'la Date, et al)) data type be considered useful? Jeff Davis has already done a lot of this work; it's on pgFOundry somewhere. --Josh -- 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] about hacking postgresql
On Thu, Apr 2, 2009 at 10:44 AM, abdelhak benmohamed wrote: > I am trying to change syntax of postgresql (for my project). I access to > gram.y, I add the changes but I still receive message error (syntax error at > near …). > > Is that only change to do or are their other steps to accomplish? You're evidently doing something wrong, but since you've provided no information about what you did or what didn't work, I can't even begin to speculate as to what it is. ...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] global index - work in progress
On Thu, Apr 2, 2009 at 10:59 AM, K. Srinath wrote: > > 5. gbl_indx_wip1: patch file w.r.t. anoncvs checkout dated 1 Dec 08. > please, update your patch to current HEAD... and add it to http://wiki.postgresql.org/wiki/CommitFestInProgress -- 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] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 12:10 PM, David E. Wheeler wrote:
> On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
>
>>> my @ints = map { $_ || 0 } split ',', $string;
>>>
>>> This ensures that I get the proper number of records in the example of
>>> something like '1,2,,4'.
>>
>> I can't see that there's any way to do this in SQL regardless of how
>> we define this operation.
>
> It's easy enough to write a function to do it:
>
> CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
> SELECT ARRAY(
> SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE $1[i] END
> FROM generate_series(1, array_upper($1, 1)) s(i)
> ORDER BY i
> );
> $$ LANGUAGE SQL IMMUTABLE;
Ah! Thanks for the tip.
...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] global index - work in progress
"K. Srinath" writes: > I'm implementing "global index" - an index that indexes all tables in > an inheritance hierarchy. This is probably not a good project to tackle as your first Postgres patch. 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] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 12:17 PM, David E. Wheeler wrote:
> On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:
>
>> Another way to state the point is that we can offer people a choice of
>> two limitations: string_to_array doesn't work for zero-length lists,
>> or string_to_array doesn't work for empty strings (except most of the
>> time, it does). The former is sounding less likely to bite people
>> unexpectedly.
>
> Right, very well put.
>
>> Or we could stick to the current behavior and say "use COALESCE() to
>> resolve the ambiguity, if you need to".
>
> Steve has a point that leaving it as-is leaves it as impossible to tell the
> difference between string_to_array(NULL, ',') and string_to_array('', ',').
> The former properly handles an unknown value, while the latter, where '' is
> a known value, seems weird to be returning NULL.
*shrug* CASE WHEN blah IS NOT NULL THEN string_to_array(blah, ',') END
More and more I'm leaning toward leaving this alone. No matter how
you define it, the behavior can be changed to whichever alternative
you prefer with a 1-line case statement.
...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] psql \d commands and information_schema
Martin Pihlak wrote:
> Attached is a patch that modifies psql \dX commands to treat objects
> in information_schema as "system objects". This prevents them from
> showing up in \dX *.* and polluting the user objects list. This is
> especially annoying if user objects are in multiple schemas, and
> one wants to get a quick overview by running \dX *.*
I have applied a simplified version of your patch, attached, that adds
just a "AND <>" line to the query; I was a little concerned that IN
might affect performance, and the macros seemed kind of complicated.
Also, since my patch this morning any pattern will also trigger
information_schema lookups, not just 'S'.
Thanks.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/psql/describe.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.204
diff -c -c -r1.204 describe.c
*** src/bin/psql/describe.c 2 Apr 2009 15:15:32 - 1.204
--- src/bin/psql/describe.c 2 Apr 2009 17:34:23 -
***
*** 95,101
gettext_noop("Description"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 95,102
gettext_noop("Description"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***
*** 282,288
" AND NOT p.proisagg\n");
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 283,290
" AND NOT p.proisagg\n");
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***
*** 373,379
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 375,382
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***
*** 428,434
gettext_noop("Description"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
"n.nspname", "o.oprname", NULL,
--- 431,438
gettext_noop("Description"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
"n.nspname", "o.oprname", NULL,
***
*** 632,638
gettext_noop("aggregate"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 636,643
gettext_noop("aggregate"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***
*** 655,661
gettext_noop("function"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
--- 660,667
gettext_noop("function"));
if (!showSystem && !pattern)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
! " AND n.nspname <> 'information_schema'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***
Re: [HACKERS] questions about not-null constraints and inheritance
"K. Srinath" writes: > Why is this so? Because we don't track inheritance state for attnotnull. There is no way you can make it work right with marginal hacking on the ATExec code, because there just isn't enough state. The correct fix is probably the one alluded to in another thread today: make NOT NULL constraints have pg_constraint entries like CHECK constraints do. As of 8.4 there is enough info tracked for check constraints (cf conislocal and coninhcount) to make inheritance handle all these corner cases. 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] Sort a column that does not exist
Hi, the problem goes on. I think the problem is in the planner.c on
grouping_planner function, because when I do a regular sort it gets to it:
/*
* If we were not able to make the plan come out in the right order, add
* an explicit sort step.
*/
if (parse->sortClause)
{
if (!pathkeys_contained_in(sort_pathkeys, current_pathkeys))
{
result_plan = (Plan *) make_sort_from_pathkeys(root,
result_plan,
sort_pathkeys,
limit_tuples);
current_pathkeys = sort_pathkeys;
}
}
and do the make_sort_from_pathkeys, but when I do the sort by grmemb it does
not. So I change it in order to pass through make_sort_from_pathkey, but it
drops an error like this "invalid attnum", so when I go to heaptuple and
force to get to ObjectIdGetDatum in heap_getsysattr, the server hang out.
What can I do? How can I assign a valid attrnum?
2009/4/1 Hitoshi Harada
> 2009/4/1 Werner Echezuria :
> > As you can see if someone do this: SELECT * FROM table WHERE
> > field=some_value ORDER BY grmemb, postgresql creates a new target entry
> and
> > then assigned to the targetlist as a sort node. I know that it creates
> the
> > node on the parser, but it does not work, it seems the executor don't see
> > it.
>
> See include/nodes/primnodes.h around line 1075:
>boolresjunk;/* set to true to eliminate
> the attribute from
> * final
> target list */
>
> If the TargetEntry is set resjunk = false, the final result is
> filtered as junk. So more accurately the executor sees but drops it.
>
> >
> > How could I sort a column like this?, I know i'm missing something, but i
> > just don't see it. What is the process to sort a column?
> >
>
> Use makeTargetEntry in makefuncs.c
> TargetEntry *
> makeTargetEntry(Expr *expr,
>AttrNumber resno,
>char *resname,
>bool resjunk)
>
> by the 4th argument you can set resjunk = false if you don't want it
> to be in the result.
>
> Regards,
>
>
> --
> Hitoshi Harada
>
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:
Another way to state the point is that we can offer people a choice of
two limitations: string_to_array doesn't work for zero-length lists,
or string_to_array doesn't work for empty strings (except most of the
time, it does). The former is sounding less likely to bite people
unexpectedly.
Right, very well put.
Or we could stick to the current behavior and say "use COALESCE() to
resolve the ambiguity, if you need to".
Steve has a point that leaving it as-is leaves it as impossible to
tell the difference between string_to_array(NULL, ',') and
string_to_array('', ','). The former properly handles an unknown
value, while the latter, where '' is a known value, seems weird to be
returning NULL.
Best,
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] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
my @ints = map { $_ || 0 } split ',', $string;
This ensures that I get the proper number of records in the example
of something like '1,2,,4'.
I can't see that there's any way to do this in SQL regardless of how
we define this operation.
It's easy enough to write a function to do it:
CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE
$1[i] END
FROM generate_series(1, array_upper($1, 1)) s(i)
ORDER BY i
);
$$ LANGUAGE SQL IMMUTABLE;
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] questions about not-null constraints and inheritance
Consider two tables foo(x int not null) and bar (x int). I fire the sql SQL1: alter table bar inherit foo; Above sql causes "ERROR: column "x" in child table must be marked NOT NULL". This looks logically fine to me. So, I fire the sql SQL2: alter table bar alter column x set not null; Then I make bar a child of foo by firing SQL1. So now I have foo (x int not null), and bar (x int not null) inherits (foo). >From this state, the questions: --- Q1. Why can I now successfully fire the following sql? SQL3: alter table bar alter column x drop not null; --- Q2. Back to baseline, where we had not null constraints on both foo and bar. I can remove the not null constraints by SQL4: alter table foo alter column x drop not null; But now, I can successfully fire SQL5: alter table only foo alter column x set not null; Why is this so? --- (The behavior seen in SQL3 and SQL5 seems contradictory to the behavior seen in SQL1. Shouldn't SQL1, SQL3 and SQL5 all share the same fate?) >From code perspective, the relevant methods are ATExecDropNotNull and ATExecSetNotNull. If the behavior seen above is incorrect/inconsistent, then following changes may have to be made: 1. ATExecDropNotNull: May have to see attinhcount of x, and deduce that not-null cannot be dropped. 2. ATExecSetNotNull: May have to always recurse; specifying ONLY during a SET NOT NULL may have to be treated as erroneous. Thanks, Srinath. -- 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
On Apr 2, 2009, at 7:20 AM, Steven Lembark wrote: Note that since the Internships are not required to be project code, we can also take student projects to contribute to our WWW infrastructure and other areas the project needs some work. Would introducing a Duration (i.e., time-series a'la Date, et al)) data type be considered useful? I'd be happy to mentor someone doing it instead of having to write the entire thing myself. +1 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] A trivial doc patch for pgstattuple
Hitoshi Harada writes: > The sgml doc mentions about HeapTupleSatisfiesNow() but currently we > have HeapTupleSatisfiesVisibility() instead. I think the wording is fine (and more useful) as-is. Remember HeapTupleSatisfiesVisibility is just a macro, and since we are calling it with SnapshotNow, the actual decision is made by HeapTupleSatisfiesNow. 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] Path case sensitivity on windows
Magnus Hagander writes: > And this only shows up when the binary is found in the PATH and not > through a fully specified directory. This is, AFAICT, the only case > where they can differ. This is the reason why we haven't had any reports > of this before - nobody using the installer, or doing even a "normal > style" install would ever end up in this situation. Hmm. Well, if we use pg_tolower then it will only do the right thing for ASCII letters, but it seems like non-ASCII in the path leading to the postgres binaries would be pretty dang unusual. (And I am not convinced tolower() would get it right either --- it certainly won't if the encoding is multibyte.) On balance I'd suggest just using pg_tolower and figuring it's close enough. 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] psql \d* and system objects
Attached patch applied, including documentation updates; I think this is the best we are going to do to balance usability and consistency. I have removed this as an open 8.4 item. With this change \dfS and \df * do the same thing; I assume we don't want to remove the 'S' modifier and tell people to just use '*', or support '*' as the modifier instead of 'S', e.g. \df*. --- Bruce Momjian wrote: > Robert Treat wrote: > > Actually I find the inconsistency to hurt usability, which is typically > > what > > you get with inconsistent interfaces. > > > > I'm not certain, but I think I would be happier if we did: > > > > \d*user space objects > > \d*S include system objects > > > > For those that want system only, do > > \d*S pg_catalog. > > ( if you want to argue temp/toast, adjust the search accordingly) > > > > I think the trick to getting this working is to enforce this with search > > patterns *and* tab completion as well. Yes, this means that Tom's desire > > for > > I talked to Robert on IM and found two new reasons to make 'S' assumed > if a pattern is supplied. The first is the use of tab completion: > > test=> \df si > signsimilar_escape sin > test=> \df sin > List of functions >Schema | Name | Result data type | Argument data types > +--+--+- > (0 rows) > > As you can see tab-tab assumes system tables are visible, but current CVS > does not without 'S'. The second case is: > > test=> \df pg_catalog.sin > List of functions >Schema | Name | Result data type | Argument data types > +--+--+- > (0 rows) > > Only \dfS works in this case. > > I think we should move forward and assume 'S' for \d* patterns; it is > inconsistent, but usability requires it. > > Objections? > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/psql-ref.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.220 diff -c -c -r1.220 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 26 Feb 2009 16:02:37 - 1.220 --- doc/src/sgml/ref/psql-ref.sgml 2 Apr 2009 15:03:01 - *** *** 853,861 more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table. ! The letter S adds the listing of system ! objects; without S, only non-system ! objects are shown. --- 853,861 more information is displayed: any comments associated with the columns of the table are shown, as is the presence of OIDs in the table. ! By default, only user-created objects are shown; supply a ! pattern or the S modifier to include system ! objects. *** *** 879,887 return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. ! The letter S adds the listing of system ! objects; without S, only non-system ! objects are shown. --- 879,887 return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. ! By default, only user-created objects are shown; supply a ! pattern or the S modifier to include system ! objects. *** *** 910,918 If pattern is specified, only conversions whose names match the pattern are listed. ! The letter S adds the listing of system ! objects; without S, only non-system ! objects are shown. --- 910,918 If pattern is specified, only conversions whose names match the pattern are listed. ! By default, only user-created objects are shown; supply a ! pattern or the S modifier to include system ! objects. *** *** 939,947 class="parameter">pattern, or of all visible objects
Re: [HACKERS] Path case sensitivity on windows
Tom Lane wrote: > Magnus Hagander writes: >> Tom Lane wrote: >>> It depends on tolower(), which is going to have LC_CTYPE-dependent >>> behavior, which is surely wrong? > >> Or are you just saying we should be using pg_tolower()? (which I forgot >> about yet again) > > Well, I'd be happier with pg_tolower, because I know what it does. > But the real question here is what does "case insensitivity" on > file names actually mean in Windows --- ie, what happens to non-ASCII > letters? The filesystem itself is UTF-16. I would assume the "system default" locale controls the case insensitivity, but I'm not sure about that. Reading up some, it seems the collation is actually stored in a hidden file on the NTFS volume... It seems to differ between different versions of windows from what I can tell, but since this is written to the fs, it's ok. I have not found a way to actually *get* the locale.. Or even to compare two filenames. There is a function called GetFullPathName(), but I'm not sure how to use it for this. However. I don't think it's really critical that we deal with all corner cases for this. It's not likely that the user would be using any really weird locale-specific combinations *differently* in the PATH variable vs the commandline, or something like that... And this only shows up when the binary is found in the PATH and not through a fully specified directory. This is, AFAICT, the only case where they can differ. This is the reason why we haven't had any reports of this before - nobody using the installer, or doing even a "normal style" install would ever end up in this situation. //Magnus -- 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] 8.4 open items list
Dave Page wrote: > On Thu, Apr 2, 2009 at 3:46 PM, Tom Lane wrote: > > Dave Page writes: > >> In this case, createdb - however, this particular case is of very > >> minor impact to us. My gripe is more on the general issue of being > >> potentially forced to add support for a new version and beta test > >> tools in the same timeframe that PostgreSQL has for beta. > > > > I hear you, but really the only way we could promise that would be to > > have a period before beta of nothing happening in the core project while > > tools authors do their thing. ?That doesn't seem productive. ?We're > > trying to get some parallelism here, not serialize all the development > > and testing. > > Agreed - I certainly don't want to see things stand still. I just > chatted with Bruce, and we discussed the idea of prioritising patches > with API changes in the last commitfest to try to make things as > painless as possible. I suggest we discuss this in Ottawa, when we > review the whole commitfest idea. Good. Also, as I said before, I think having the open items list on a wiki is the best interface we are going to get; I can't possibily keep such a list current myself. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] 8.4 open items list
On Thu, Apr 2, 2009 at 3:46 PM, Tom Lane wrote: > Dave Page writes: >> In this case, createdb - however, this particular case is of very >> minor impact to us. My gripe is more on the general issue of being >> potentially forced to add support for a new version and beta test >> tools in the same timeframe that PostgreSQL has for beta. > > I hear you, but really the only way we could promise that would be to > have a period before beta of nothing happening in the core project while > tools authors do their thing. That doesn't seem productive. We're > trying to get some parallelism here, not serialize all the development > and testing. Agreed - I certainly don't want to see things stand still. I just chatted with Bruce, and we discussed the idea of prioritising patches with API changes in the last commitfest to try to make things as painless as possible. I suggest we discuss this in Ottawa, when we review the whole commitfest idea. -- Dave Page EnterpriseDB UK: 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
Re: [HACKERS] 8.4 open items list
Tom Lane wrote: > Dave Page writes: > > In this case, createdb - however, this particular case is of very > > minor impact to us. My gripe is more on the general issue of being > > potentially forced to add support for a new version and beta test > > tools in the same timeframe that PostgreSQL has for beta. > > I hear you, but really the only way we could promise that would be to > have a period before beta of nothing happening in the core project while > tools authors do their thing. That doesn't seem productive. We're > trying to get some parallelism here, not serialize all the development > and testing. I am talking to Dave Page via IM; I think the best we can do is to focus on API changes that affect tools early in the last commit-fest and open items closing stage. The locale flags issue wasn't in the last commit-fest but rather an item that we never really resolved during primary development. We would have to be more focused on getting those nailed down earlier, rather than allow them to have the same priority as other items. I think it would also require a new "alpha" stage where we said the tool API was stable (mostly SQL and system catalogs). This wouldn't affect the psql \dfS behavior, for example, but would perhaps affect the libpq PQinitSSL() change we made last week. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] about hacking postgresql
Hello, I am learning how to hack PostgreSQL, but it is still so difficult in fact, I am a novice! I am trying to change syntax of postgresql (for my project). I access to gram.y, I add the changes but I still receive message error (syntax error at near …). Is that only change to do or are their other steps to accomplish? I need your help It is very important for me to have your advices, help. Thanks very much. Friendly
Re: [HACKERS] question about deparsing const node and its typmod
Tom Lane wrote: > "Tao Ma" writes: >> Is it really important to show the 'bpchar' if there is no any >> explicit casting for the column default value. > > Yeah. We cannot say "char" because per SQL spec, that means > "char(1)", but there mustn't be a restriction to a single character > here. Tangential point: Maybe bpchar should be better documented for users, since it does occassionally show up in user visible places. The only current description that I could find (short of digging into the source code) is here: http://www.postgresql.org/docs/8.3/interactive/typeconv-query.html Perhaps a mention should be added here, as it might be where people would go looking for it?: http://www.postgresql.org/docs/8.3/interactive/datatype-character.html -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] 8.4 open items list
Tom Lane wrote:
> Magnus Hagander writes:
> > Tom Lane wrote:
> >> Personally I think the naming decision is close enough to be a coin
> >> toss, and so either choice is fine with me. However, I think it is
> >> Clearly Unacceptable for createdb's switches to be spelled differently
> >> than the underlying SQL command's options. So it's not really "let's
> >> not change this" but "which one do you consider it more important to not
> >> change"?
>
> > pgAdmin uses the SQL commands, not the external commands. IIRC the only
> > external commands that are used are pg_dump[all], pg_restore, pg_ctl and
> > initdb. Is initdb on the list of tools that might be changed?
>
> Hm, that's a good point. initdb has these switches (and has had 'em for
> a good long time):
>
> --locale=LOCALE set default locale for new databases
> --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
> --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
> set default locale in the respective category for
> new databases (default taken from environment)
> --no-locale equivalent to --locale=C
>
> So createdb is consistent with longstanding history in initdb, and
> that seems to mean that we should leave it alone and change
> CREATE DATABASE to match (modulo underscore instead of dash).
Agreed, I see them back to Postgres 8.0:
8.0/pgsql/src/bin/initdb/initdb.c:
printf(_(" --lc-collate, --lc-ctype, --lc-messages=LOCALE\n"
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
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] 8.4 open items list
Dave Page writes: > In this case, createdb - however, this particular case is of very > minor impact to us. My gripe is more on the general issue of being > potentially forced to add support for a new version and beta test > tools in the same timeframe that PostgreSQL has for beta. I hear you, but really the only way we could promise that would be to have a period before beta of nothing happening in the core project while tools authors do their thing. That doesn't seem productive. We're trying to get some parallelism here, not serialize all the development and testing. 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] 8.4 open items list
Magnus Hagander writes: > Tom Lane wrote: >> Personally I think the naming decision is close enough to be a coin >> toss, and so either choice is fine with me. However, I think it is >> Clearly Unacceptable for createdb's switches to be spelled differently >> than the underlying SQL command's options. So it's not really "let's >> not change this" but "which one do you consider it more important to not >> change"? > pgAdmin uses the SQL commands, not the external commands. IIRC the only > external commands that are used are pg_dump[all], pg_restore, pg_ctl and > initdb. Is initdb on the list of tools that might be changed? Hm, that's a good point. initdb has these switches (and has had 'em for a good long time): --locale=LOCALE set default locale for new databases --lc-collate=, --lc-ctype=, --lc-messages=LOCALE --lc-monetary=, --lc-numeric=, --lc-time=LOCALE set default locale in the respective category for new databases (default taken from environment) --no-locale equivalent to --locale=C So createdb is consistent with longstanding history in initdb, and that seems to mean that we should leave it alone and change CREATE DATABASE to match (modulo underscore instead of dash). 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] Path case sensitivity on windows
Magnus Hagander writes: > Tom Lane wrote: >> It depends on tolower(), which is going to have LC_CTYPE-dependent >> behavior, which is surely wrong? > Or are you just saying we should be using pg_tolower()? (which I forgot > about yet again) Well, I'd be happier with pg_tolower, because I know what it does. But the real question here is what does "case insensitivity" on file names actually mean in Windows --- ie, what happens to non-ASCII letters? 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] 8.4 open items list
On Thu, Apr 2, 2009 at 3:23 PM, Tom Lane wrote: > Personally I think the naming decision is close enough to be a coin > toss, and so either choice is fine with me. However, I think it is > Clearly Unacceptable for createdb's switches to be spelled differently > than the underlying SQL command's options. So it's not really "let's > not change this" but "which one do you consider it more important to not > change"? In this case, createdb - however, this particular case is of very minor impact to us. My gripe is more on the general issue of being potentially forced to add support for a new version and beta test tools in the same timeframe that PostgreSQL has for beta. -- Dave Page EnterpriseDB UK: 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
Re: [HACKERS] "failed to commit client_encoding" explained
Magnus Hagander writes: > Tom Lane wrote: >> Comments? > Certainly seems like a reasonable compromise. From what I understand, > you'll get this "failed to commit..." message *if* you have changedf > things in pg_conversion. I think that's acceptable - it's not like > people modify pg_conversion all the time (at least I hope they don't). Yeah, that's a corner case on a corner case. A further thought here is that even though the change I propose is pretty localized, it's still complicated enough to possibly introduce new bugs. And it's fixing a case that I think doesn't occur in practice; people don't really make local-in-transaction changes of client_encoding. (Remember the bug was only discovered by accident.) So my inclination is to fix it now in HEAD so it can go through a cycle of beta testing, but leave 8.3 alone. We can back-patch it after testing if we get any additional complaints. 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] 8.4 open items list
Tom Lane wrote: > Dave Page writes: >> On Thu, Apr 2, 2009 at 8:47 AM, Heikki Linnakangas >> wrote: >>> Now is the time to decide, before the PostgreSQL beta is out. I understand >>> the pain inflicted on tools, but I don't think that's a good reason to not >>> change it. People using a beta version of pgAdmin will should understand >>> that it's not final yet and there can be small glitches like that. > >> It's not about the changes the users see, it's about invalidating the >> testing that's been done on the tools & interfaces and requiring last >> minute changes to be made followed by re-testing. > > Personally I think the naming decision is close enough to be a coin > toss, and so either choice is fine with me. However, I think it is > Clearly Unacceptable for createdb's switches to be spelled differently > than the underlying SQL command's options. So it's not really "let's > not change this" but "which one do you consider it more important to not > change"? pgAdmin uses the SQL commands, not the external commands. IIRC the only external commands that are used are pg_dump[all], pg_restore, pg_ctl and initdb. Is initdb on the list of tools that might be changed? //Magnus -- 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] [pgsql-www] Mentors needed urgently for SoC & PostgreSQL Student Internships
>> Note that since the Internships are not required to be project code, >> we can also take student projects to contribute to our WWW >> infrastructure and other areas the project needs some work. Would introducing a Duration (i.e., time-series a'la Date, et al)) data type be considered useful? I'd be happy to mentor someone doing it instead of having to write the entire thing myself. -- Steven Lembark85-09 90th St. Workhorse Computing Woodhaven, NY, 11421 lemb...@wrkhors.com +1 888 359 3508 -- 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] 8.4 open items list
Dave Page writes: > On Thu, Apr 2, 2009 at 8:47 AM, Heikki Linnakangas > wrote: >> Now is the time to decide, before the PostgreSQL beta is out. I understand >> the pain inflicted on tools, but I don't think that's a good reason to not >> change it. People using a beta version of pgAdmin will should understand >> that it's not final yet and there can be small glitches like that. > It's not about the changes the users see, it's about invalidating the > testing that's been done on the tools & interfaces and requiring last > minute changes to be made followed by re-testing. Personally I think the naming decision is close enough to be a coin toss, and so either choice is fine with me. However, I think it is Clearly Unacceptable for createdb's switches to be spelled differently than the underlying SQL command's options. So it's not really "let's not change this" but "which one do you consider it more important to not change"? 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] question about deparsing const node and its typmod
"Tao Ma" writes: > Is it really important to show the > 'bpchar' if there is no any explicit casting for the column default value. Yeah. We cannot say "char" because per SQL spec, that means "char(1)", but there mustn't be a restriction to a single character here. regression=# select 'abc'::char; bpchar a (1 row) regression=# select 'abc'::bpchar; bpchar abc (1 row) 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] Path case sensitivity on windows
Tom Lane wrote: > Magnus Hagander writes: >> Attached patch fixes this in my testcase. Can anybody spot something >> wrong with it? > > It depends on tolower(), which is going to have LC_CTYPE-dependent > behavior, which is surely wrong? Not sure, really :) That's the encoding we'd get the paths in in the first place, is it not? Or are you just saying we should be using pg_tolower()? (which I forgot about yet again) //Magnus -- 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] 8.4 open items list
Dave Page wrote: > On Thu, Apr 2, 2009 at 8:47 AM, Heikki Linnakangas > wrote: > > >> As Dave Page pointed > >> out, other people have already started designing tools based on CVS > >> HEAD. > > > > Now is the time to decide, before the PostgreSQL beta is out. I understand > > the pain inflicted on tools, but I don't think that's a good reason to not > > change it. People using a beta version of pgAdmin will should understand > > that it's not final yet and there can be small glitches like that. > > It's not about the changes the users see, it's about invalidating the > testing that's been done on the tools & interfaces and requiring last > minute changes to be made followed by re-testing. Because of the > unpredictability of the PostgreSQL release timing, we need to work in > advance of PostgreSQL to ensure tools are going to be ready on release > day, because I'm sure you wouldn't want to delay to allow us to catch > up. For a significant percentage of the users, having a working set of > tools and interfaces with the server release is a essential. We are having enough trouble getting to beta without having to concern ourselves with what tools are using our API before beta; beta means beta, meaning the interface is stable, but before that, anything can be changed. If tool makers want a stable API before beta, they can ask for that and we can debate it but I am not ready to have every change we make pre-beta go through an API-change discussion. We need to be sure we have an API at beta time that we can live with for years, and having pre-beta tools affect that seems unwise; we have put off API decisions assuming we have until beta to make them right. If we don't have tools by beta release, then so be it; beta will be long enough for sufficient testing before the final release. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Path case sensitivity on windows
Magnus Hagander writes: > Attached patch fixes this in my testcase. Can anybody spot something > wrong with it? It depends on tolower(), which is going to have LC_CTYPE-dependent behavior, which is surely wrong? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A trivial doc patch for pgstattuple
The sgml doc mentions about HeapTupleSatisfiesNow() but currently we have HeapTupleSatisfiesVisibility() instead. Regards, diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml index 821f631..0bba5fe 100644 --- a/doc/src/sgml/pgstattuple.sgml +++ b/doc/src/sgml/pgstattuple.sgml @@ -117,7 +117,7 @@ free_percent | 1.95 pgstattuple judges a tuple is dead if - HeapTupleSatisfiesNow returns false. + HeapTupleSatisfiesVisibility returns false. -- Hitoshi Harada -- 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] Bug of ALTER TABLE DROP CONSTRAINT
Robert Haas writes: > Actually it's more complicated than that. You'd need to remember > whether or not the NOT NULL was added when the primary key was added, > or whether it was there before, and only drop it if it wasn't there > before. We've discussed before the idea that NOT NULL constraints should be explicitly represented in pg_constraint, just like general CHECK constraints (this would allow them to be named, have sane inheritance behavior, etc). If we had that, then pg_attribute.attnotnull could indicate the OR of "there is a NOT NULL on this column" and "there is a pkey constraint on this column", and you'd just have to recompute it properly after dropping either kind of constraint. Not happening for 8.4, but maybe someday someone will get around to 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] Bug of ALTER TABLE DROP CONSTRAINT
Hi, > >> > >> Making a column into the primary key forces the column to NOT NULL. > >> You'll need to DROP NOT NULL separately. > >> > >> It's probably possible to beat on the code hard enough to fix this, > > > > Yeah it will be a matter of finding the affected column entries and > invoking > > the removal of the not null entry from their corresponding pg_attribute > > rows. > > Actually it's more complicated than that. You'd need to remember > whether or not the NOT NULL was added when the primary key was added, > or whether it was there before, and only drop it if it wasn't there > before. > Hmm, and maybe that is the reason why this is not clubbed with the removal of the primary constraint. Otherwise it seems to be a matter of decompiling the conkey and generating AT_DropNotNull nodes for the involved columns within ATPrepCmd and the rest should happen automatically. So I guess we can let it be. Regards, Nikhils -- http://www.enterprisedb.com
[HACKERS] Path case sensitivity on windows
Bug #4694
(http://archives.postgresql.org/message-id/200903050848.n258mvgm046...@wwwmaster.postgresql.org)
shows a very strange behaviour on windows when you use a different case PATH
>From what I can tell, this is because dir_strcmp() is case sensitive,
but paths on windows are really case-insensitive.
Attached patch fixes this in my testcase. Can anybody spot something
wrong with it? If not, I'll apply once I've finished my test runs:-)
//Magnus
diff --git a/src/port/path.c b/src/port/path.c
index 708306d..d7bd353 100644
--- a/src/port/path.c
+++ b/src/port/path.c
@@ -427,7 +427,12 @@ dir_strcmp(const char *s1, const char *s2)
{
while (*s1 && *s2)
{
+#ifndef WIN32
if (*s1 != *s2 &&
+#else
+ /* On windows, paths are case-insensitive */
+ if (tolower(*s1) != tolower(*s2) &&
+#endif
!(IS_DIR_SEP(*s1) && IS_DIR_SEP(*s2)))
return (int) *s1 - (int) *s2;
s1++, s2++;
--
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] Bug of ALTER TABLE DROP CONSTRAINT
On Thu, Apr 2, 2009 at 8:24 AM, Nikhil Sontakke wrote: > Hi, >> >> > Considering the following sequence: >> > create table t(a int primary key); >> > alter table t drop constraint t_pkey; >> > insert into t values(null); >> > ERROR: null value in column "a" violates not-null constraint >> > >> > My question is, why "null" is not allowed to be inserted after primary >> > key >> > constraint has been dropped. >> >> Making a column into the primary key forces the column to NOT NULL. >> You'll need to DROP NOT NULL separately. >> >> It's probably possible to beat on the code hard enough to fix this, > > Yeah it will be a matter of finding the affected column entries and invoking > the removal of the not null entry from their corresponding pg_attribute > rows. Actually it's more complicated than that. You'd need to remember whether or not the NOT NULL was added when the primary key was added, or whether it was there before, and only drop it if it wasn't there before. ...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] Bug of ALTER TABLE DROP CONSTRAINT
Hi, > > Considering the following sequence: > >create table t(a int primary key); > >alter table t drop constraint t_pkey; > >insert into t values(null); > >ERROR: null value in column "a" violates not-null constraint > > > > My question is, why "null" is not allowed to be inserted after primary > key > > constraint has been dropped. > > Making a column into the primary key forces the column to NOT NULL. > You'll need to DROP NOT NULL separately. > > It's probably possible to beat on the code hard enough to fix this, Yeah it will be a matter of finding the affected column entries and invoking the removal of the not null entry from their corresponding pg_attribute rows. > but I'm not really sure there's much point, since the situation is > rare and the workaround is easy. > Yeah and it is documented already. Although it is not obvious immediately that a not-null attribute gets tagged onto the involved columns separately for primary, unique-not-null types of constraints. Regards, Nikhils -- http://www.enterprisedb.com
Re: [HACKERS] More message encoding woes
Heikki Linnakangas wrote:
Tom Lane wrote:
Heikki Linnakangas writes:
Tom Lane wrote:
Maybe use a special string "Translate Me First" that
doesn't actually need to be end-user-visible, just so no one sweats
over
getting it right in context.
Yep, something like that. There seems to be a magic empty string
translation at the beginning of every po file that returns the
meta-information about the translation, like translation author and
date. Assuming that works reliably, I'll use that.
At first that sounded like an ideal answer, but I can see a gotcha:
suppose the translation's author's name contains some characters that
don't convert to the database encoding. I suppose that would result in
failure, when we'd prefer it not to. A single-purpose string could be
documented as "whatever you translate this to should be pure ASCII,
never mind if it's sensible".
I just tried that, and it seems that gettext() does transliteration, so
any characters that have no counterpart in the database encoding will be
replaced with something similar, or question marks. Assuming that's
universal across platforms, and I think it is, using the empty string
should work.
It also means that you can use lc_messages='ja' with
server_encoding='latin1', but it will be unreadable because all the
non-ascii characters are replaced with question marks. For something
like lc_messages='es_ES' and server_encoding='koi8-r', it will still
look quite nice.
Attached is a patch I've been testing. Seems to work quite well. It
would be nice if someone could test it on Windows, which seems to be a
bit special in this regard.
Unfortunately it doesn't seem to work on Windows.
First any combination of valid lc_messages and non-existent encoding
passes the test strcmp(gettext(""), "") != 0 .
Second for example the combination of ja(lc_messages) and ISO-8859-1
passes the the test but the test fails after I changed the last_trans
lator part of ja message catalog to contain Japanese kanji characters.
regards,
Hiroshi Inoue
--
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] Bug of ALTER TABLE DROP CONSTRAINT
On Thu, Apr 2, 2009 at 3:25 AM, Jacky Leng wrote: > Considering the following sequence: > create table t(a int primary key); > alter table t drop constraint t_pkey; > insert into t values(null); > ERROR: null value in column "a" violates not-null constraint > > My question is, why "null" is not allowed to be inserted after primary key > constraint has been dropped. Making a column into the primary key forces the column to NOT NULL. You'll need to DROP NOT NULL separately. It's probably possible to beat on the code hard enough to fix this, but I'm not really sure there's much point, since the situation is rare and the workaround is easy. ...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] [GENERAL] string_to_array with empty input
>> I'm speaking primarily of functions as first-class objects, though >> closures would be nice too. But consider an operation like >> >> UPDATE rel SET col1 = MAP ( f OVER col2 ) >> >> We need to be able to determine whether this is well-typed, just as we >> do now for any other SQL query. Specifically, we need to check that f >> is a one argument function whose argument type is that of col2 and >> whose return type is that of col1. My understanding is that right now >> types are represented as 32-bit OIDs. I think they'd need to be some >> sort of more complex structure in order to handle cases like this. > > Would above query not be written as > > UPDATE rel SET col1 = f(col2); > > anyway or am I missing something? Ah, sorry, I mis-stated it slightly. I was imagining that col2 and col1 where arrays, and f was a function between the base types, not the array types. > imho, having generic tuple tables as we have in INSERT INTO (...) > VALUES (...),(...),(...) > > to be useable in all places like a real table would be helpful in > many cases. > > But this might be completely unrelated :) Probably. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.
Tom Lane wrote: > Magnus Hagander writes: >> Tom Lane wrote: >>> What makes more sense to me is to add a table to encnames.c that >>> provides the gettext name of every encoding that we support. > >> Do you mean a separate table there, or should we add a new column to one >> of the existing tables? > > Whichever seems to make more sense is fine with me. I just don't want > add-an-encoding maintenance requirements spread across N different > source files. I was about to start looking at this when that other thread (http://archives.postgresql.org//pgsql-hackers/2009-03/msg01270.php) started about related issues on other platforms. Seems we should have a "coordinated fix" for this, so I'm going to want and see what come sout of that one. Unless I'm misunderstanding thigns and they're not related? //Magnus -- 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] Path separator
Tom Lane wrote: > Magnus Hagander writes: >> I've seen a couple of reports that the new SSL error messages on windows >> look strange with paths the wrong way. For example: > >> root certificate file "C:\Documents and Settings\\Application >> Data/postgresql/root.crt" does not exist. > >> The issue being the mix of forward and backwards slashes. Attached patch >> should fix this. > >> Is this worth doing? Comments? > > In view of the way that canonicalize_path() works, I can't help thinking > this is going in precisely the wrong direction. In a way, yes. But canonicalize_path() runs only in the backend, and this is only in the frontend. I think the requirements on the frontend are slightly different than the backend. But the most important thing is to be consistent within the same path as we report it I think, so we could switch all to forward slashes as well if you think that's better. > > Also, don't we already have a macro someplace for the platform's > preferred path separator? I looked for a macro for it, didn't find it. It seems to be hardcoded. We have macros for SYSTEM_QUOTE for example, but not for the path separator AFAICF. I just realized we have a make_native_path() function, I had completely missed that one. So we could possibly use that instead. In the end it does the same thing //Magnus -- 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] "failed to commit client_encoding" explained
Tom Lane wrote: > A problem with such caching is that it'd fail to respond to changes in > the content of pg_conversion. Now the code is already pretty > insensitive in that respect, because if you're not doing any fresh "SET > client_encoding" commands it won't ever notice changes in that catalog > anyway. But this'd make it worse. We could ameliorate the issue > somewhat by doing fresh lookups (and updating the cache) whenever doing > SetClientEncoding with IsTransactionState() true, and only relying on > the cache when IsTransactionState() is false. > > Comments? Certainly seems like a reasonable compromise. From what I understand, you'll get this "failed to commit..." message *if* you have changedf things in pg_conversion. I think that's acceptable - it's not like people modify pg_conversion all the time (at least I hope they don't). //Magnus -- 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] 8.4 open items list
On Thu, Apr 2, 2009 at 8:47 AM, Heikki Linnakangas wrote: >> As Dave Page pointed >> out, other people have already started designing tools based on CVS >> HEAD. > > Now is the time to decide, before the PostgreSQL beta is out. I understand > the pain inflicted on tools, but I don't think that's a good reason to not > change it. People using a beta version of pgAdmin will should understand > that it's not final yet and there can be small glitches like that. It's not about the changes the users see, it's about invalidating the testing that's been done on the tools & interfaces and requiring last minute changes to be made followed by re-testing. Because of the unpredictability of the PostgreSQL release timing, we need to work in advance of PostgreSQL to ensure tools are going to be ready on release day, because I'm sure you wouldn't want to delay to allow us to catch up. For a significant percentage of the users, having a working set of tools and interfaces with the server release is a essential. -- Dave Page EnterpriseDB UK: 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] Bug of ALTER TABLE DROP CONSTRAINT
Considering the following sequence: create table t(a int primary key); alter table t drop constraint t_pkey; insert into t values(null); ERROR: null value in column "a" violates not-null constraint My question is, why "null" is not allowed to be inserted after primary key constraint has been dropped. -- 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] 8.4 open items list
Robert Haas wrote: On Sat, Mar 28, 2009 at 12:25 PM, Tom Lane wrote: Robert Haas writes: On Fri, Mar 27, 2009 at 11:42 AM, Tom Lane wrote: Both of those things are related to 8.4 feature changes, so we should either do them now or decide we won't do them. Well, "Should we have a LOCALE option in CREATE DATABASE?" has to do with making: CREATE DATABASE foo WITH LOCALE = bar equivalent to... CREATE DATABASE foo WITH COLLATE = bar, CTYPE = bar That might be nice to have, but since it's just syntactic sugar, I disagree that it's now or never. The reason I wanted it considered now is that part of the discussion was about whether to rename the existing options (add or remove LC_, I forget which). Once 8.4 is out it'll be too late to reconsider that. The current situation is not horribly consistent because createdb uses --lc-foo and the SQL syntax uses FOO. I'm not sure which is better, or whether it's worth making them consistent. I pondered for a long time whether to call the options "COLLATE" and "CTYPE", or "LC_COLLATE" and "LC_CTYPE". I went with COLLATE and CTYPE in the end, one reason being that there was discussion on having ICU support as an option in the future. Calling the option LC_COLLATE would be misleading, since the collation would actually be implemented by ICU, with no connection to the operating system LC_COLLATE environment variable. If we get finer grained collations (column level etc.) it would be nice to not call it "LC_COLLATE" everywhere, but just "collation". In fact, perhaps the keyword should be "COLLATION" instead of COLLATE? But what to do with CTYPE then? I'm still not sure which is actually better, though. On the other hand, now that LC_COLLATE does refer to the environment variable, it would be nice to spell it "LC_COLLATE". I think I'm leaning more towards LC_COLLATE now that I've lived with it for a while... As Dave Page pointed out, other people have already started designing tools based on CVS HEAD. Now is the time to decide, before the PostgreSQL beta is out. I understand the pain inflicted on tools, but I don't think that's a good reason to not change it. People using a beta version of pgAdmin will should understand that it's not final yet and there can be small glitches like that. At any rate, I don't think we can make LC-FOO a keyword - it would have to be LC_FOO or something. Yeah, LC_COLLATE is what the environment variable is called too. -- 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
