Re: [HACKERS] Timestamp to time_t
On Sep 15, 2009, at 11:01 AM, Andrew Gierth wrote: If you want to store both a timestamp and an associated timezone you can do it right now, using a composite type or two columns, with the advantage that you get semantics that you can rely on. How would a composite work in practice? Can you index it on the timestamp? Or would you have to use two columns for that? I could see a real advantage to a type that stored the TZ with which it was created, with the ability to fetch it back out. Internally the data could be stored just like it is with timestamptz, and by default, perhaps, it would display in $PGTZ, but if $PGTZ was set to a value like original or something, it should display the originals. Now *that* would be really useful IMHO. 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] updated hstore patch
On Sep 15, 2009, at 8:31 PM, Andrew Gierth wrote:
Gah. rerolled to fix a missing file. includes the docs too this time.
Yay, thank you Andrew! Here are my review notes.
Testing
===
Here's what I did to try out the patch, paying special attention to in-
place upgrading:
* I built a simple database with a table with an (old) hstore column
from an unpatched Git checkout.
* I ran the script I developed for testing the previous patch in July,
commenting out the new features, just to test the existing
implementation.
* I applied your patch, rebuilt hstore, installed the DSO, and
restarted PotgreSQL. I did *not* dump the previous database or restore
it, I just just used the existing cluster. The only thing that changed
was the new hstore.
* I ran the hstore `make installcheck` and all tests passed.
* I ran the following to update the SQL functions in my simple database:
psql -d try --set hstore_xact='--' -f hstore.sql
The use of `--set hstore_xact='--' was on Andrew's advice via IRC,
because otherwise the entire update takes place in a single
transaction, and thus would fail since I already had the old hstore
installed. By using this psql variable hack to disable the
transactions, I was able to install over the old hstore.
* I connected to my simple database and did a select on the table I
created before installing the new hstore, and all the old data showed
up fine in psql.
* I uncommented the new stuff in my test script (attached) and ran it.
Everything worked as I expected.
Notes and minor issues:
* `hstore - hstore` resolves before `hstore - text`, meaning that this
failed:
SELECT 'a=1, b =2'::hstore - 'a' = 'b=2';
ERROR: Unexpected end of string
LINE 1: SELECT 'a=1, b =2'::hstore - 'a' = 'b=2';
But it works if I cast it:
SELECT 'a=1, b =2'::hstore - 'a'::text = 'b=2';
Not sure if there's anything to be done about that. I mentioned this
issue back in July, as well.
* Maybe it's time to kill off `...@` and `~`, eh? Or could they generate
warnings for a release or something? How are operators properly
deprecated?
* The documentation for `populate_record()` is wrong. It's still just
a cut-and-paste of `delete()`
* The NOTE about `populate_record()` says that it takes anyelement
instead of record and just throws an error if it's not a record. I
thought that C functions could take record arguments. Why do the extra
work here?
* Your original submission say that the new version offers btree and
hash support, but the docs still mention only GIN and GIST.
* I'd like to see more examples of the new functionality in the
documentation. I'd be happy to contribute them once the main patch is
committed.
* I think that there should be some exmples in the docs of the use of
the backslash with and without standard_conforming_strings and with
and without E''. That stuff is confusing enough, it should all be
spelled out, IMHO.
* The use of the `:hstore_xact` variable hack needs to be documented,
along with detailed instructions for those upgrading (in-place) from
8.4. You might consider documenting your `:hstore_default_schema`
variable as well: if I understand correctly, it's a way to specify a
schema on the command-line without having to edit the file, yes?
Currently, there are no installation instructions in the documentation.
Comments
* So the main objection to the original patch from the July
CommitFest, that it wasn't backwards compatible, seems to have been
addressed, assuming that the structure currently used in HEAD is just
like what's in 8.4, so in-place upgrade should work, yes? It apears
that you've taken the approach, in hstore_compat.c, of reading both
the old and the new formats. Does it also upgrade the old formats as
it reads them, or only as they're updated? (I'm assuming the latter.)
* I'm not qualified to talk about the approach taken to maintaining
compatibility, but would like to know if it imposes an overhead on the
use of the data type, and if so, how much?
* Also, just as an aside, I'm wondering if the approach you've taken
could be used for core data types going forward, so as to work towards
true in-place upgrades in the future?
* Regarding the bug you found in the old hstore format (mentioned
[here](http://archives.postgresql.org/pgsql-hackers/2009-07/msg01422.php)
), has that been fixed? Is it a regression that should be back-patched?
* Does there need to be documentation with upgrade instructions for
hstore-new users (the pgFoundry version)? Or will you handle that via
a new pgFoundry release?
* In addition to the functions to get all the keys and all the values
as arrays, I'd love a function (or, dare I say, a cast?) to get all
the rows and keys in an array. Something like this:
try=# select 'a = 1, b = 2'::hstore::text[];
array
---
{a,1,b,2}
I'd find this
Re: [HACKERS] updated hstore patch
On Sep 19, 2009, at 7:03 PM, Tom Lane wrote: Given the number of questions in your review, I don't think this is actually ready to commit. I'm marking it waiting on author instead. Yes, actually, I had second thoughts about that and meant to change it myself. Thanks Tom. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] updated hstore patch
On Sep 18, 2009, at 6:27 PM, Andrew Gierth wrote:
However, I would prefer to keep the ability to do this:
psql --set hstore_schema='myschema' -f hstore.sql dbname
The logic to do it is a bit ugly, but editing the file to set what
schema to
use is even uglier...
Yes, this should perhaps be generalized into a separate patch. I
completely agree that it'd be useful and desirable.
The only open question I can see is what delete(hs,$1) resolves to
when $1 is
an unknown-type placeholder; this is probably an incompatibility
with the old
version if anyone is relying on that (but I don't see why they would
be).
Given your examples, I think it probably should resolve to text as it
does, as deleting a single key is likely to be a common case. It
should otherwise be cast.
Because record doesn't express the fact that the return type of
populate_record is the SAME record type as its parameter type, whereas
anyelement does.
The lack of expressivity in records is beginning to annoy me.
David * I'd like to see more examples of the new functionality in
David the documentation. I'd be happy to contribute them once the
David main patch is committed.
Thanks. I'll do some (especially for populate_record, which really
needs
them), but more can be added later.
Agreed. As I said, once this is committed I'll likely go over the docs
and submit a patch myself.
Old values are converted to new values in core, but they can't be
changed on-disk unless something actually updates them.
Right, of course, thanks.
The overhead is possibly non-negligible for reading old values, but
old values can be promoted to new ones fairly simply (e.g. using
ALTER TABLE).
So then it's negligible for new values?
David * Regarding the bug you found in the old hstore format
(mentioned
David [here](http://archives.postgresql.org/pgsql-hackers/2009-07/msg01422.php
)
David ), has that been fixed? Is it a regression that should be
David back-patched?
That has not been fixed.
Should it be? I realize that it's a separate issue from this patch,
and maybe it's too edge-case to bother with, given that no one has
complained and it obviously won't exist once your patch is applied.
Right?
Davidtry=# select 'a = 1, b = 2'::hstore::text[];
David array
David---
David {a,1,b,2}
DavidI'd find this especially useful in my Perl applications, as
Davidthen I could easily fetch hstores as arrays and turn them
Davidinto hashes in my Perl code by simply doing `my %h = @{
David$row-{hstore} };`. Of course, the inverse would be useful
Davidas well:
Davidtry=# select ARRAY[ 'a', '1', 'b', '2']::hstore;
David hstore
David
David a=1, b=2
DavidA function would work as well, failing community interest
Davidin an explicit cast.
I'm not sure I like these as casts but I'm open to opinions. Having
them
as functions is certainly doable.
I think a function would be great here. A cast is something we can
decide to add later, or one can add manually using the function.
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] updated hstore patch
On Sep 20, 2009, at 8:43 AM, Tom Lane wrote: If the perfect solution is too complex, I'd also kinda hope this isn't a show-stopper for this patch, but rather a TODO for the future modules feature. Yeah, this is a long-standing generic issue, and not really hstore's problem to fix. So then does there need to be some documentation for how to deal with this, for those doing an in-place upgrade from an existing hstore data type? Or would that discussion be in Bruce's tool's docs? 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] updated hstore patch
On Sep 20, 2009, at 1:03 AM, Andrew Gierth wrote:
I will resubmit (hopefully in a day or two) with the following
changes:
- removal of the \set schema variable stuff from the .sql script
- documentation fixes as mentioned in my previous email
- additional documentation for some of the newer features
- more internal code documentation covering the handling of old
formats
+1. And maybe a discussion about upgrading old hstore values?
I'd appreciate public feedback on:
- whether conversions to/from a {key,val,key,val,...} array are needed
(and if there's strong opinions in favour of making them casts; in
the
absence of strong support for that, I'll stick to functions)
Definitely functions. I'm strongly in favor of an explicit cast, as
well, but I'm spoiled by Perl, so I may be overly biased. Functions
will do to start.
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] updated hstore patch
On Sep 20, 2009, at 3:14 PM, Andrew Gierth wrote: I think you're missing the point here; I can't control what it resolves to, since that's the job of the function overload resolution code. Yeah, but I think that the existing behavior is probably the best. But I checked, and delete(hstore,$1) still resolves to delete(hstore,text) when the type of $1 is not specified, so there's no compatibility issue there that I can see. (I'm not sure I understand _why_ it resolves to that rather than being ambiguous...) Right, but it does seem like it might be the best choice for now. I'd add a regression test to make sure it stays that way. David So then it's negligible for new values? Yes. (One bit test, done inline) Excellent, thanks. 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] updated hstore patch
On Sep 20, 2009, at 12:15 PM, Tom Lane wrote: That recipe doesn't actually work for cases like this. What *would* work is loading the module *before* restoring from your old dump, then relying on the CREATEs from the incoming dump to fail. Jesus this is hacky, either way. :-( I believe we have already discussed the necessity for pg_upgrade to support this type of subterfuge. A module facility would be a lot better of course, but we still need something for upgrading existing databases that don't contain the module structure. Yeah, it's past time for a real module facility. 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] updated hstore patch
On Sep 21, 2009, at 4:57 PM, Andrew Gierth wrote:
I don't think there's any way to do that from the regression tests.
The output that you demonstrated a few messages back should do nicely
for delete(), at least:
contrib_regression=# explain verbose select delete(('a' =
now()::text),'a');
QUERY PLAN
---
Result (cost=0.00..0.02 rows=1 width=0)
Output: delete(('a'::text = (now())::text), 'a'::text)
(2 rows)
contrib_regression=# explain verbose select delete(('a' =
now()::text),'a=1'::hstore);
QUERY PLAN
Result (cost=0.00..0.02 rows=1 width=0)
Output: delete(('a'::text = (now())::text), 'a=1'::hstore)
(2 rows)
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] Unicode Normalization
Hackers, I just had a discussion on IRC about unicode normalization in PostgreSQL. Apparently there is not support for it, currently. Andrew Gierth points out that it's part of the SQL spec to support it, though: RhodiumToad:e.g. NORMALIZE(foo,NFC,len) justatheory:Oh, just a function then, really. RhodiumToad:where the normal form can be any of NFC, NFD, NFKC, NFKD RhodiumToad:except that the normal form is an identifier, not a string RhodiumToad:also the normal form and length are optional RhodiumToad:so NORMALIZE(foo) is equivalent to NORMALIZE(foo,NFC) I looked around and found the Public Software Group's utf8proc project, which even includes some PostgreSQL support (not, alas, for normalization). It has an MIT-licensed C library that offers these functions: uint8_t utf8proc_NFD(uint8_t str) Returns a pointer to newly allocated memory of a NFD normalized version of the null-terminated stringstr. uint8_t utf8proc_NFC(uint8_t str) Returns a pointer to newly allocated memory of a NFC normalized version of the null-terminated stringstr. uint8_t utf8proc_NFKD(uint8_t str) Returns a pointer to newly allocated memory of a NFKD normalized version of the null-terminated stringstr. uint8_t utf8proc_NFKC(uint8_t str) Returns a pointer to newly allocated memory of a NFKC normalized version of the null-terminated stringstr. Anyone got any interest in porting these functions to PostgreSQL? I guess the parser would need to be updated to support the use of identifiers in the NORMALIZE() function, but otherwise it should be a fairly straight-forward port for an experienced C coder, no? 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] Unicode Normalization
On Sep 23, 2009, at 11:08 AM, David E. Wheeler wrote: I just had a discussion on IRC about unicode normalization in PostgreSQL. Apparently there is not support for it, currently. BTW, the only reference I found on the [to do list](http://wiki.postgresql.org/wiki/Todo ) was: More sensible support for Unicode combining characters, normal forms I think that should probably be changed to talk about the unicode standard support. 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] Unicode Normalization
On Sep 23, 2009, at 11:08 AM, David E. Wheeler wrote: I looked around and found the Public Software Group's utf8proc project, which even includes some PostgreSQL support (not, alas, for normalization). It has an MIT-licensed C library that offers these functions: Sorry, forgot the link: http://www.public-software-group.org/utf8proc 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] latest hstore patch
On Sep 22, 2009, at 7:18 PM, Andrew Gierth wrote:
Hstore patch incorporating changes as previously discussed.
In addition the requested new features of conversions to and from
array formats have been added (with docs).
Thanks Andrew.
Just a few thoughts for discussion:
* From my previous posts: Is it time to kill off `...@` and `~`,? Not
necessarily for your patch to handle, just wondering what others think.
* I like the %% operator for converting to arrays. Though I think
maybe I would have liked %@ better, but maybe that's just the Perl
hacker in me.
* I also like the new %# operator to convert to two-dimensional
arrays. But if you adopted %@ for arrays, maybe %@@ better indicates a
2-dimensional array? I'm just thinking out lout here, I'm happy to
have them no matter what they're called.
* More name stuff: Why `hstore_to_list` rather than `hstore_to_array`?
And I'm not sure about `hstore_to_matrix` for the 2-dimensional array.
I guess that's better than `hstore_to_multidimensional_array` would
be. ;-)
For those following along at home, here's what these guys look like:
SELECT %% 'a=foo, b=bar'::hstore as array_op,
hstore_to_list('a=foo, b=bar'::hstore),
%# 'a=foo, b=bar'::hstore as matrix_op,
hstore_to_matrix('a=foo, b=bar'::hstore);
array_op| hstore_to_list | matrix_op |
hstore_to_matrix
---++---
+---
{a,foo,b,bar} | {a,foo,b,bar} | {{a,foo},{b,bar}} | {{a,foo},
{b,bar}}
(1 row)
Pretty cool!
* Thanks for updating the docs with:
+ BTREE and HASH index support
+ A fix for the populate_hash() pasto
+ A link to a discussion of backslashing and SQL standard strings
+ A note on the overhead of reading the old binary format
+ Notes on how to update from the old binary format
In the attached patch, I made a few tweaks to the hstore docs, after
applying your patch. I would have created a new patch with everything,
but ran out of time trying to convince Git to create a context diff.
This is a unified diff, but short, with just these changes:
* Fixed doc pasto for %#.
* Noted in docs that the format is new in 8.5, rather than this
version.
* Eliminated a redundant However, .
* Added an example for creating a HASH index.
In sum: Modulo a discussion of the names of the array casting
operators and functions, I think this patch is ready for committer
review.
Thanks,
David
hstore-doc.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode Normalization
On Sep 24, 2009, at 6:24 AM, p...@thetdh.com wrote: In a context using normalization, wouldn't you typically want to store a normalized-text type that could perhaps (depending on locale) take advantage of simpler, more-efficient comparison functions? That might be nice, but I'd be wary of a geometric multiplication of text types. We already have TEXT and CITEXT; what if we had your NTEXT (normalized text) but I wanted it to also be case-insensitive? Whether you're doing INSERT/UPDATE, or importing a flat text file, if you canonicalize characters and substrings of identical meaning when trivial distinctions of encoding are irrelevant, you're better off later. User-invocable normalization functions by themselves don't make much sense. Well, they make sense because there's nothing else right now. It's an easy way to get some support in, and besides, it's mandated by the SQL standard. (If Postgres now supports binary- or mixed-binary-and-text flat files, perhaps for restore purposes, the same thing applies.) Don't follow this bit. 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] Unicode Normalization
On Sep 24, 2009, at 8:59 AM, Andrew Dunstan wrote: That might be nice, but I'd be wary of a geometric multiplication of text types. We already have TEXT and CITEXT; what if we had your NTEXT (normalized text) but I wanted it to also be case-insensitive? Actually, I don't think it's necessarily a good idea at all. If a user inputs a perfectly valid piece of UTF8 text, we should be able to give it back to them exactly, whether or not it's in normalized form. The normalized forms are useful for certain comparison purposes, but they don't affect the validity of the text. CITEXT doesn't mangle what is stored, just how it's compared. Right, I don't think there's a need for a normalized TEXT type. 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] latest hstore patch
On Sep 23, 2009, at 5:27 PM, Andrew Gierth wrote: I intentionally avoided hstore_to_array because it would be unclear which one it meant (the 1-d or 2-d result). Thanks Andrew. Given these replies, unless anyone else wants to weigh in on the array conversion operator and function names, this patch is ready for committer review (along with my tiny doc patch). I'll update the commitfest site to that effect. Thanks, 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] Hot Standby on git
On Sep 26, 2009, at 12:33 PM, Josh Berkus wrote: There's always pgtap. Whenever we find a new corner case, we add it to the development test suite. Also, for C TAP, there's [libtap](http://jc.ngo.org.uk/trac-bin/trac.cgi/wiki/LibTap ). You can then use `prove` which you likely already have on your system, to harness the test output. 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] navigation menu for documents
On Sep 29, 2009, at 8:55 AM, Richard Huxton wrote: For the browser, does the following match what you're after, Andrew? - clicking chapter title opens the browser panel - panel stays open until you click close icon - panel contains collapsable tree of chapter/section headings Alternatively, could just auto-open the browser panel if javascript is enabled and window is wider than N pixels. Why wouldn't the entire TOC be in a collapsed list? In addition we'll presumably want to meet: - no external js libraries (or do we care, if we just reference it from google?) Save yourself the hassle and just bundle jQuery. That's what I've done for Pod::Site (module that builds the Bricolage API browser). - navigation is optional, disabling js leaves docs as at present As long as there's a way to get the nav back from a link on each doc page. - works on all reasonable browsers (anything not IE6) +1 (IE6--) - works online and in downloaded docs (except Windows .chm of course) That'd be nice, too. 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] latest hstore patch
On Sep 29, 2009, at 4:11 PM, Andrew Gierth wrote: I don't feel particularly strongly about the name (I've also intentionally held off on updating the pgfoundry version of the code until this is settled so no-one else should care either). I'm down with hstore_to_array() and hstore_to_matrix(). My own expectation is that the operator should normally be used in preference (though obviously people's tastes will vary in this respect). Sure. But I realized that I forgot to ask for array_to_hstore() and matrix_to_hstore(). :-) Would love to have those, too. Not sure about the operators… 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] latest hstore patch
On Sep 29, 2009, at 5:00 PM, Andrew Gierth wrote: David Sure. But I realized that I forgot to ask for David array_to_hstore() and matrix_to_hstore(). :-) Would love to David have those, too. Not sure about the operators… hstore(text[]) (which is also present as an explicit cast) covers both of those cases since it can figure out from the array dimensions which is intended. Oooh! RhodiumToad++ Thanks, 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] latest hstore patch
On Sep 30, 2009, at 12:52 PM, Tom Lane wrote: Applied with some mostly-cosmetic editorialization. And there was much rejoicing… 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] navigation menu for documents
On Oct 1, 2009, at 1:12 AM, Richard Huxton wrote: Why wouldn't the entire TOC be in a collapsed list? Permanently on-screen? My only concern there would be for people viewing on phones etc. I have to admit that I'm never looking at the Pg docs on my iPhone. This is mainly because I use them as a reference while hacking, and I'm not (yet) hacking PostgreSQL on my phone. It's MIT licensed (well MIT+GPL) which is BSD compatible, but I don't know if that's acceptable. It would be easier for me if it could be bundled and presumably make it easier for other contributors in the future too. Agreed. IIUC, the MIT license is compatible. 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] CREATE OR REPLACE FUNCTION vs ownership
On Oct 1, 2009, at 3:42 PM, Tom Lane wrote: My inclination is to think that the right behavior for REPLACE FUNCTION is to keep the old proowner and proacl values, because that's what it always has done and nobody's complained. But I suppose a case could be made that you're completely replacing the function and so you should replace its ownership/permissions too. The CREATE FUNCTION reference page fails to specify either way, which is a documentation bug as well. Comments? The latter, I think. If I replace a function, I should be the new owner. To me it makes no sense for someone else to own it. 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] CREATE OR REPLACE FUNCTION vs ownership
On Oct 2, 2009, at 7:49 AM, Tom Lane wrote: But in fact CREATE OR REPLACE is *not* meant to be the same as DROP followed by CREATE. What it is meant to do is allow you to replace the implementation of the function while existing callers see it as still being the same function. Thus we prevent you from changing the name, arguments, or result type of the function. If we were to replace the permissions that would result in a more insidious but still real reason why former callers would suddenly stop working. In effect, permissions are part of the function's API. Okay, this convinces me otherwise. But is it not in fact the case that CREATE OR REPLACE FUNCTION doesn't expire the old version of the function in the cache of other processes? Don't those processes have to reconnect in order to see the new function? 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] latest hstore patch
On Oct 2, 2009, at 8:20 AM, Bruce Momjian wrote: Most modules just install functions, which are easily uninstalled/reinstalled. A data type like hstore is more complicated assuming it is the data type that is changing and not the support functions. Lots of modules install data types. From contrib: * hstore * uin * citext * cube * inarray * ltree Plus lots of stuff on pgFoundry. It's a problem that needs to be solved. Surely someone, somewhere, has solved this problem, no? 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] CREATE OR REPLACE FUNCTION vs ownership
On Oct 2, 2009, at 8:49 AM, Tom Lane wrote: The ideal is that backends will start using the new function implementation on the next call after the REPLACE commits (but any evaluations already in progress must of course continue with the text they have). We have been gradually getting closer to that ideal over the years, although I think there are still cases where it will take a little longer --- for instance if a SQL function gets inlined I think the inlined code will persist for the duration of the query's execution. I don't believe there are still any cases where you actually have to reconnect to get it to notice the update. (At least this is true for plpgsql --- not sure if all the other PLs are equally up to speed.) Ah, good to know. Perhaps an audit is in order… 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] latest hstore patch
On Oct 2, 2009, at 9:43 AM, Alvaro Herrera wrote: Plus lots of stuff on pgFoundry. It's a problem that needs to be solved. Surely someone, somewhere, has solved this problem, no? Dump reload? Hahahahaha. No, really. Dump reload is a phrase that end users will not put up with for much longer. 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] latest hstore patch
On Oct 2, 2009, at 10:04 AM, Alvaro Herrera wrote: The point is it's *not* solved in the context of using pg_migrator. Yes, that's my point too, against David's argument that surely someone must have solved it. What we have here is a new problem, so it's not so clear that there's any solution at all (yet). Yeah, I didn't mean that someone must've solved it for PostgreSQL, but that this sort of problem must have been solved before, wherever binary data storage is an issue. 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] latest hstore patch
On Oct 2, 2009, at 11:14 AM, Bruce Momjian wrote: Well, if it is just changed syntax, we could wack around the system catalogs. If storage changes, we have to dump/reload that data type. Andrew solved this problem for hstore by making the new version able to read the old representation. It will also update to the new representation when you update a value. 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] Rules: A Modest Proposal
On Oct 4, 2009, at 1:57 PM, David Fetter wrote: It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? Perhaps, given concrete examples of issues with RULEs, we could look at addressing those problems rather than throwing out the baby (let alone put the baby in concrete -- sorry, the metaphors are getting away from me). 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] Feature Suggestion: PL/Js
On Oct 7, 2009, at 7:55 AM, Andrew Dunstan wrote: BTW I've seen requests for PL/Js so I'm sure it'll be welcome. What license is v8 under? It's a BSD license, but it's a C++ API. While it looks cool, I think SpiderMonkey is possibly a better bet. SquirrelFish? http://webkit.org/blog/189/announcing-squirrelfish/ 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] Concurrency testing
On Oct 7, 2009, at 9:53 AM, David Fetter wrote: At the moment, we have no way to test this, although with certain Perl modules, it would be pretty trivial. No non-core modules necessary. Just use Test::More and file handles opened via pipes to two or more psql sessions. 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] Concurrency testing
On Oct 7, 2009, at 10:38 AM, David Fetter wrote: When did Test::More become core? I believe we support back to Perl 5.6 :/ Module::CoreList says 5.006002, though I would have sworn it was added much earlier than that. You could always use Test.pm, I suppose, which has been in core since 5.00405. 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] Concurrency testing
On Oct 7, 2009, at 5:18 PM, Jeff Janes wrote: I'd much rather live without Test::More and use DBD::Pg, then have Test::More but need to open pipes to psql to talk to the database, rather than using DBI to do it. But I guess we would need to worry about whether we can make DBD::Pg work with the installation being tested, rather than finding some other install. The test architecture depends on Perl, but not on the DBI. I don't think that Andrew wants to add any dependencies. Therefore we'd need to use file handles. That's not to say that we couldn't write a nice little interface for it such that the implementation could later change. Do we need to restrict ourselves to core? Developers already need flex and bison, which aren't needed when installing from the tarball. Couldn't we also have make dev-check that has higher requirements than make check does, but does a more thorough job? flex and bison are not Perl modules. 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] Issues for named/mixed function notation patch
On Oct 7, 2009, at 9:00 PM, Steve Prentice wrote: Committed with a fair amount of corner-case cleanup and refactoring. Woot! Thanks for all the hard work getting this committed (Pavel, Bernd, Jeff, Tom and others)! I've been really looking forward to this feature. Still hoping a solution is found to the plpgsql parser issue. If not, I'll have to resubmit my rejected AS patch. :) +1 Thanks for getting this done. Now, does this just apply to PL/pgSQL? If so, what needs to happen for other PLs to support the feature? 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] Issues for named/mixed function notation patch
On Oct 8, 2009, at 9:47 AM, Jeff Davis wrote: It's just the call notation -- the function only needs to know what arguments it got for which parameters. So they're still ordered? I'm thinking of PL/Perl here… 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] CommitFest 2009-09: how do we close this one out?
On Oct 10, 2009, at 7:55 PM, Robert Haas wrote: After some thought and reflection, I'm inclined to suggest that on 10/15 we move all the remaining patches to the next CommitFest, declare this one closed, and stamp alpha2 on schedule. +1, FWIW. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 7:54 AM, Stephen Frost wrote: 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. +1, just what I was thinking. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 8:36 AM, Robert Haas wrote:
I think warnings are too easy to miss, but I agree your other
suggestion. I know you can write function_name.variable_name, but
that's often massively long-winded. We either need a short, fixed
prefix, or some kind of sigil. I previously suggested ? to parallel
ECPG, but Tom didn't like it. I still do. :-)
I suppose that $ would interfere with dollar quoting. What about @ or
@@ (sorry, I did mess with MSSQL back in the 90s).
Hrm…PostgreSQL is starting to have the same problem as Perl: running
out of characters because they're used for operators. :var would be
perfect, if it wasn't for psql. ?var is okay, I guess, if a bit…
questionable. Are {braces} used for anything?
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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:29 AM, Stephen Frost wrote: Uh, what dollar quoting? $_$ is what I typically use, so I wouldn't expect a $ prefix to cause a problem. I think it'd be more of an issue because pl/pgsql still uses $1 and whatnot internally (doesn't it?). Yes, but that's no more an issue than it is in Perl, where the same $n variables are globals. The issue with dollar quoting is that you can put anything between the dollar signs. So if you have two $variables, they can get in the way. Potentially. But perhaps the lexer and/or Parser won't be confused by that, Tom? I'd sure love $, as it's like shell, Perl, and other stuff. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:49 AM, Tom Lane wrote: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. Party pooper. I'd be in favor of a GUC that I could turn on to throw an error when there's an ambiguity. As for which way it should go, I have no dog in that pony hunt. Or something. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 11:47 AM, Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) What about adopting the modifier syntax you're adding to COPY? 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 12:05 PM, Tom Lane wrote: What about adopting the modifier syntax you're adding to COPY? Where exactly would you put the modifier, and why is that better than the existing #option convention? CREATE OR REPLACE FUNCTION foo() RETURNS BOOLEAN LANGUAGE plpgsql WITH opt1, opt2 AS $$...$$; That is, the specification of options is made outside of the language in question. It might only effect a particular language (plpgsql in this case) and be ignored otherwise (or trigger an exception), but it's clean and very much like what you have elsewhere. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 12:23 PM, Tom Lane wrote: That is, the specification of options is made outside of the language in question. I don't think I particularly care for this. It's inventing a global mechanism to cover a problem that we currently have one instance of in one PL. That's a mighty thin justification. Also, I tend to think that you should have several instances of a problem before you venture to design a global solution --- else your one-size-fits-all solution might turn out to be a lot less general than you thought. Sure, just an idea to keep in mind for when you do have a second and a third option to add… 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] Could regexp_matches be immutable?
On Oct 21, 2009, at 7:27 AM, Tom Lane wrote: Huh, it looks to me like that's an error in the declaration of the citext versions of regexp_matches --- they should be declared to return setof text[], the same as the underlying text functions. David, do you agree? Ooh, yeah, dunno how I missed that. 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] Could regexp_matches be immutable?
On Oct 21, 2009, at 9:37 AM, Tom Lane wrote: Ooh, yeah, dunno how I missed that. I think we're probably stuck in 8.4, but we should fix it going forward. Would you make a quick check if any of the other citext functions have the same bug? I've fixed it in my [version for 8.3](https://svn.kineticode.com/citext/trunk ). Is there a straight-foward way to check such a thing programmatically, with a query perhaps? Or should I just put aside an hour to do an audit? 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] Could regexp_matches be immutable?
On Oct 21, 2009, at 9:40 AM, David E. Wheeler wrote: On Oct 21, 2009, at 9:37 AM, Tom Lane wrote: Ooh, yeah, dunno how I missed that. I think we're probably stuck in 8.4, but we should fix it going forward. Would you make a quick check if any of the other citext functions have the same bug? I've fixed it in my [version for 8.3](https://svn.kineticode.com/citext/trunk ). Is there a straight-foward way to check such a thing programmatically, with a query perhaps? Or should I just put aside an hour to do an audit? FWIW, I think that this is a bug, and that the variation from the text version will be unexpected. I recommend fixing it for 8.4.2. 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] Could regexp_matches be immutable?
On Oct 21, 2009, at 9:48 AM, Tom Lane wrote: I was wondering whether you could query pg_proc to look for functions with the same name and different arguments/results. It's a bit tricky though because you'd expect s/citext/text/ in at least some positions (maybe not all)? Yeah, almost all. I'll poke around, though it might be a day or two… 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] Controlling changes in plpgsql variable resolution
On Oct 21, 2009, at 11:37 AM, Robert Haas wrote: That's like saying that it's less of a risk than a group of rabid tyrannosaurs in a kindergarten classroom. I'm not sure, but I kind of doubt that tyrannosaurs can get rabies. I mean, if they were even around anymore. Which, you know, they're not. 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] [ANNOUNCE] PostgreSQL 8.5alpha2 Now Available
On Oct 24, 2009, at 10:44 AM, Peter Eisentraut wrote: More detail is available in the Release Notes included with each alpha: http://developer.postgresql.org/pgdocs/postgres/release-8.5.html That seems to just have alpha1 at the moment. 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] Anonymous Code Blocks as Lambdas?
Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) 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] Anonymous Code Blocks as Lambdas?
Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) 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] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 1:16 PM, Pavel Stehule wrote: I have a idea about migration of outer (psql) variables, and custom shell variables. some like: psql --allow_custom_variables --table_name=mytable inside psql we should to use :table_name variable with mytable as content. then we can use syntax do (table_name varchar) $$ begin raise notice 'TABLENAME IS %', table_name; return; end; $$ so with this mechanism we can to simply parametrise plpgsql do scripts from outer environment. How is this different from psql :variables? And why would a `DO` feature be tied directly to psql? Confused, 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] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 1:21 PM, Tom Lane wrote: A lambda facility would require being able to pass arguments and return results, which we intentionally left out of DO to keep it simple. By the time you add all that notation, it's far from clear that you shouldn't just define a function. Well sometimes I want to do something like that as an expression, rather than having to write a separate statement that declares a function. Also, DO is (intended to be) optimized for execute-once behavior. A lambda block inside a query shouldn't assume that. So it would not be the same facility from either a syntax or an implementation standpoint. Perhaps lambda isn't the proper term. 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] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 2:12 PM, Pavel Stehule wrote: it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. I see, because `DO` is a statement, not an expression. Thus arguments don't really make much sense (I wish it was an expression!). I don't think it's a good idea to tie SQL syntax to a feature of a client, though. 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] EOL for 7.4?
On Nov 3, 2009, at 10:32 AM, Josh Berkus wrote: So I'm going to make a case in favor of EOL'ing 7.4. In fact, I'd be in favor of doing so in, say, February after an announcement this month +1 And, frankly, I think that we still need a published deprecation policy -- or at least a set of guidelines. That was my point in starting this discussion back in July. 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] Shall we just get rid of plpgsql's RENAME?
On Nov 4, 2009, at 5:34 PM, Tom Lane wrote: According to http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-RENAMING-VARS the RENAME declaration in plpgsql has been known broken since PG 7.3. Nobody has bothered to fix it. Shall we just rip it out? +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] operator exclusion constraints
On Nov 5, 2009, at 11:09 AM, Jeff Davis wrote: I think EXCLUDING conflicts with the EXCLUDING in LIKE. Also, it becomes a little more difficult to place the access method clause, because EXCLUDING USING gist doesn't sound great. Well that's clearly a verb. So perhaps EXCLUDE USING gist (EXCLUDING USING gist is a little weirder). 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] operator exclusion constraints
On Nov 7, 2009, at 11:08 AM, Tom Lane wrote: EXCLUDE probably flows most nicely with the optional USING clause or without. My only complaint was that it's a transitive verb, so it seems to impart more meaning than it actually can. I doubt anyone would actually be more confused in practice, though. If a couple of people agree, I'll change it to EXCLUDE. EXCLUDE sounds good to me. +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] operator exclusion constraints
On Nov 8, 2009, at 7:43 PM, Jeff Davis wrote: Either of those names are fine with me, too. The current name is a somewhat shortened version of the name operator-based exclusion constraints, so we can also just use that name. Or, just exclusion constraints. (exclusion constraints)++ 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] array_to_string bug?
On Nov 12, 2009, at 10:46 AM, David Fetter wrote: My question boils down to, why is this string concatenation different from all other string concatenations? Does it have something to do with afikoman? 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] operator exclusion constraints
On Nov 13, 2009, at 8:39 PM, Robert Haas wrote: alter table foo add constraint bar exclude (a check with =, b check with =); I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. 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] operator exclusion constraints
On Nov 14, 2009, at 8:55 AM, Tom Lane wrote: I've been meaning to comment on this syntax one more time; apologies for the bike-shedding. But I'm wondering if the CHECK is strictly necessary there, since the WITH seems adequate, and there was some discussion before about the CHECK keyword possibly causing confusion with check constraints. I had been manfully restraining myself from re-opening this discussion, but yeah I was thinking the same thing. The original objection to using just WITH was that it wasn't very clear what you were doing with the operator; but that was back when we had a different initial keyword for the construct. EXCLUDE ... WITH ... seems to match up pretty naturally. You're more man than I, Tom, but yeah, with EXCLUDE, WITH works well on its own, methinks. 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] Shouldn't Natural JOINs Follow FK Constraints?
Howdy, I just saw this in the docs: Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of exactly those column names that appear in both input tables. As with USING, these columns appear only once in the output table. That sounds useful if I happen to have named my columns exactly the same between the two tables, but couldn't a NATURAL JOIN follow the FK constraints, instead? That would be so much more useful and much less magical, I should think. Thanks, 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] Shouldn't Natural JOINs Follow FK Constraints?
Howdy, I just saw this in the docs: Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of exactly those column names that appear in both input tables. As with USING, these columns appear only once in the output table. That sounds useful if I happen to have named my columns exactly the same between the two tables, but couldn't a NATURAL JOIN follow the FK constraints, instead? That would be so much more useful and much less magical, I should think. Thanks, 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] Overhauling GUCS
On May 31, 2008, at 09:23, Tom Lane wrote: 1. Most people have no idea how to set these. 2. The current postgresql.conf file is a huge mess of 194 options, the vast majority of which most users will never touch. 3. GUCS lists are kept in 3 different places (guc.c, postgresql.conf, and the settings.sgml), which are only synched with each other manually. 4. We don't seem to be getting any closer to autotuning. The proposal doesn't actually solve any of those problems. It solves #2 at least. I disagree with doing any of this. It doesn't result in any useful reduction in maintenance effort, and what it does do is make it impossible to keep control over the detailed layout, formatting, commenting etc in a sample postgresql.conf. Nor do I think that generate a whole config file from scratch is going to be a useful behavior for tuning problems --- how will you merge it with what you had before? I'd love to see these issues resolved. The current postgresql.conf is way over the top. Might you have a better idea? Thanks, 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] Overhauling GUCS
On May 31, 2008, at 12:36, Gregory Stark wrote: What this sounds like is a sly way to try to get rid of postgresql.conf entirely and replace it with parameters stored in the database so admins would adjust the parameters using an SQL syntax rather than a text file. There are pros and cons of such a system but I think for newbie admins that would be a thousand times *more* baffling. You would have to learn new commands and have no holistic view of what parameters had been set, what related parameters might exist. You also have no way to keep the file in a version control system or sync across servers etc. FWIW, this has not been a barrier to MySQL adoption. 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] Overhauling GUCS
On May 31, 2008, at 15:32, Peter Eisentraut wrote: 1. Most people have no idea how to set these. Could you clarify this? I can't really believe that people are incapable of editing a configuration file. I've been using PostgreSQL on and off, mostly on, for almost 10 years. I still have no idea what 75% of those settings in postgresql.conf mean or are for. There are an overwhelming number of them. I know that 5-8 of them I always touch, thanks largely to assistance now and then from Josh Berkus, but the rest are just complexity to me. I don't doubt that the vast majority of them are useful in one situation or another, but unless I'm in one of those situations, I really don't need to see them there and be confused by them. Does that help? 2. The current postgresql.conf file is a huge mess of 194 options, the vast majority of which most users will never touch. My opinion has always been that we should provide a default file with only the essential options instead of all of them. I see this as a the major problem, because people are overwhelmed and consequently don't set anything. +1 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] Case-Insensitve Text Comparison
Howdy,
I'm sure I'm just showing off my ignorance here, but here goes…
I really need case-insensitive string comparison in my database.
Ideally there'd be a nice ITEXT data type (and friends, ichar,
ivarchar, etc.). But of course there isn't, and for years I've just
used LOWER() on indexes and queries to get the same result.
Only it turns out that I'm of course not getting the same result. This
script:
#!/usr/local/bin/perl -w
use strict;
use warnings;
use utf8;
binmode STDOUT, ':utf8';
use DBI;
my $dbh = DBI-connect('dbi:Pg:dbname=try', 'postgres', '',
{ pg_enable_utf8 = 1 });
for my $char qw( À Á Â Ã Ä Å Ç Ć Č Ĉ Ċ Ď Đ A B C D ) {
print $char: , $dbh-selectrow_array('SELECT LOWER(?)', undef,
$char ), $/;
}
Yields this output:
À: À
Á: Á
Â: Â
Ã: Ã
Ä: Ä
Å: Å
Ç: Ç
Ć: Ć
Č: Č
Ĉ: Ĉ
Ċ: Ċ
Ď: Ď
Đ: Đ
A: a
B: b
C: c
D: d
So it doesn't really work on anything other than ASCII, it looks like.
So I have two questions:
1. Does the use of the tolower() C function in the citext data type on
pgfoundry basically give me the same results as using lower() in my
SQL has for all these years? IOW, does it convert letters to lowercase
in the same way that the LOWER() SQL function does? If so, I think I
might start to use it for my case-insensitive columns and simplify my
SQL a bit.
http://pgfoundry.org/projects/citext/
2. Isn't the ICU library distributed with PostgreSQL? And if so, could
it not be used to create proper case conversions in LOWER() and
friends and, ultimately, to create a case-insensitive text type in
core? I'm seeing that it has a constant named U_COMPARE_IGNORE_CASE
that can be used with its unorm_compare() function:
http://www.icu-project.org/apiref/icu4c/unorm_8h.html#6cc4c8b0d5be7ce1ac4b600ace7817f5
http://www.icu-project.org/apiref/icu4c/unorm_8h.html#991e0fe6f0d062dd6e8e924517f3f437
I don't really know C, but if that's stuff there, can't we take
advantage of it for proper case-insensitive comparisons (and
conversions)?
Thanks,
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] Case-Insensitve Text Comparison
On Jun 1, 2008, at 21:08, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: I really need case-insensitive string comparison in my database. Okay ... according to whose locale? I'm using C. Of course you're correct that it depends on the locale, I always forget that. But does not the Unicode standard offer up some sort locale-independent case-insensitivity, so that it gets it right some large percentage of the time? Ideally there'd be a nice ITEXT data type (and friends, ichar, ivarchar, etc.). But of course there isn't, and for years I've just used LOWER() on indexes and queries to get the same result. Only it turns out that I'm of course not getting the same result. I think that means you're not using the right locale. What locale is right? If I have a Web app, there could be data in many different languages in a single table/column. 1. Does the use of the tolower() C function in the citext data type on pgfoundry basically give me the same results as using lower() in my SQL has for all these years? [ broken record... ] Kinda depends on your locale. However, tolower() is 100% guaranteed not to work for multibyte encodings, so citext is quite useless if you're using UTF8. This is fixable, no doubt, but it's not fixed in the project as it stands. Right, okay; thanks. I'm thinking about using it for email addresses and domain names, however, so it might be adequate for those applications. 2. Isn't the ICU library distributed with PostgreSQL? Nope, it is not, and we have already pretty much determined that we do not want to make Postgres depend on ICU. See the archives. Damn. Okay, thanks. 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] Case-Insensitve Text Comparison
On Jun 1, 2008, at 22:18, Tom Lane wrote: I'm using C. Of course you're correct that it depends on the locale, I always forget that. But does not the Unicode standard offer up some sort locale-independent case-insensitivity, so that it gets it right some large percentage of the time? Not really, and in any case the C locale completely disables any knowledge of Unicode. C locale knows about 7-bit ASCII and nothing more. And the locale can only be set by initdb? I don't suppose that there are any collations that sort and index case- insensitively, are there? I don't see anything suggestive in `locale - a`… Thanks, 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] Case-Insensitve Text Comparison
On Jun 1, 2008, at 22:21, Oleg Bartunov wrote: David, we wrote contrib module (mchar) for one customer, which ports its application from mssql to postgres. It does case-insensitive comparison for new data type 'mchar' and linked with ICU for system independent locale. That sounds promising. I don't suppose that it has been released, has it? Thanks, 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] Case-Insensitve Text Comparison
On Jun 2, 2008, at 06:51, Andrew Sullivan wrote: On Sun, Jun 01, 2008 at 10:13:07PM -0700, David E. Wheeler wrote: What locale is right? If I have a Web app, there could be data in many different languages in a single table/column. I think the above amounts to a need for per-session locale settings or something, no? Yes, that's what I was getting at. Thanks, 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] Case-Insensitve Text Comparison
On Jun 1, 2008, at 21:08, Tom Lane wrote: 1. Does the use of the tolower() C function in the citext data type on pgfoundry basically give me the same results as using lower() in my SQL has for all these years? [ broken record... ] Kinda depends on your locale. However, tolower() is 100% guaranteed not to work for multibyte encodings, so citext is quite useless if you're using UTF8. This is fixable, no doubt, but it's not fixed in the project as it stands. Would the use of str_tolower() in formatting.c fix that? Thanks, 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] Case-Insensitve Text Comparison
On Jun 2, 2008, at 09:33, Tom Lane wrote:
Would the use of str_tolower() in formatting.c fix that?
Yeah, you need something equivalent to that. I think that whole area
is due for refactoring, though --- we've got kind of a weird
collection
of upper/lower/initcap APIs spread through a couple of different
files.
And I just ran into this on 8.3 when trying to install citext:
psql:citext.sql:350: ERROR: there is no built-in function named
oid_text
I'm assuming that this is because a lot of automatic casts were
removed in 8.3 or 8.2; There are a bunch of these:
CREATE FUNCTION citext(oid) RETURNS citext AS 'oid_text' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION citext(oid) TO PUBLIC;
COMMENT ON FUNCTION citext(oid) IS 'convert oid to citext';
CREATE FUNCTION oid(citext) RETURNS oid AS 'text_oid' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION oid(citext) TO PUBLIC;
COMMENT ON FUNCTION oid(citext) IS 'convert citext to oid';
CREATE CAST (citext AS oid) WITH FUNCTION oid(citext);
CREATE CAST (oid AS citext) WITH FUNCTION citext(oid);
CREATE FUNCTION citext(int2) RETURNS citext AS 'int2_text' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION citext(int2) TO PUBLIC;
COMMENT ON FUNCTION citext(int2) IS 'convert int2 to citext';
CREATE FUNCTION int2(citext) RETURNS int2 AS 'text_int2' LANGUAGE
'internal' IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION int2(citext) TO PUBLIC;
COMMENT ON FUNCTION int2(citext) IS 'convert citext to int2';
CREATE CAST (citext AS int2) WITH FUNCTION int2(citext);
CREATE CAST (int2 AS citext) WITH FUNCTION citext(int2);
And on and on. Clearly this module needs updating for newer
PostgreSQLs. I tried removing them all in order to get the data type
and tried it out with this script:
my $dbh = DBI-connect('dbi:Pg:dbname=try', 'postgres', '',
{ pg_enable_utf8 = 1 });
for my $char qw( À Á Â Ã Ä Å Ç Ć Č Ĉ Ċ Ď Đ A B C D ) {
print $char: , $dbh-selectrow_array('SELECT LOWER(?::citext)',
undef, $char ), $/;
}
Naturally it didn't work:
À: Ã
Á: á
Â: â
Ã: ã
Ä: ä
Å: Ã¥
Ç: ç
Ć: Ä
Č: Ä
Ĉ: Ä
Ċ: Ä
Ď: Ä
Đ: Ä
A: a
B: b
C: c
D: d
BTW, I rebuilt my cluster with --locale en_US.UTF-8 and the script
works on a text type, so having a locale is key.
Thanks,
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] Case-Insensitve Text Comparison
On Jun 3, 2008, at 02:27, Zdenek Kotala wrote: The proposal of GSoc is there: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00857.php It should create basic framework for full SQL COLLATION support. All comments are welcome. That looks great, Zdenek. I'm very excited to have improved SQL COLLATION support in core. But if I could ask a dumb question, how would I specify a case-insensitive collation? Or maybe the Unicode Collation Algorithm is case-insensitive or has case-insensitive support? Thanks, 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] Case-Insensitve Text Comparison
On Jun 3, 2008, at 12:06, Zdenek Kotala wrote: It is simple. SQL standard does not specify notation for that (chapter 11.34). But there is proposed notation: CREATE COLLATION collation name FOR character set specification FROM existing collation name [ pad characteristic ] [ case sensitive ] [ accent sensitive ] [ LC_COLLATE lc_collate ] [ LC_CTYPE lc_ctype ] pad characteristic := NO PAD | PAD SPACE case sensitive := CASE SENSITIVE | CASE INSENSITIVE accent sensitive := ACCENT SENSITIVE | ACCENT INSENSITIVE You can specify for each collation if it is case sensitive or not and collation function should be responsible to correctly handle this flag. Wooo! Now if only i could apply that on a per-column basis. Still, it'll be great to have this for a whole database. Thanks, looking forward to it. 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] Overhauling GUCS
On Jun 3, 2008, at 20:48, Greg Smith wrote: Correct, but completely off-topic regardless. One problem to be solved here is to take PostgreSQL tuning from zero to, say, 50% automatic. Wander the user lists for a few months; the number of completely misconfigured systems out there is considerable, partly because the default values for many parameters are completely unreasonable for modern hardware and there's no easy way to improve on that without someone educating themselves. Getting distracted by the requirements of the high-end systems will give you a problem you have no hope of executing in a reasonable time period. Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I think that right now postgresql.conf is designed for full-time DBAs, rather than folks who might want to target PostgreSQL for an application they're developing. We want to attract the latter (without, of course, any expense with the former). Changing how configuration works so that it's easier to understand and, if possible, at least partly automatically tunable would go a long way towards making PostgreSQL friendlier for developers, IMHO. 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] Overhauling GUCS
On Jun 4, 2008, at 07:19, Andreas Pflug wrote: IMHO the best compromise in machine and human readability is an XML format. It's easily decorateable with comments, easily interpreted and a pg_settings view could enhance it with even more comments, so an editor using pgsql functions (to read and write postresql.conf.xml) could be enabled to supply comprehensive help. I hope that was a joke… 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] Overhauling GUCS
On Jun 4, 2008, at 11:22, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I take it you haven't looked at mysql's configuration file lately. I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. I'm not saying that this is necessarily admirable -- it's kind of the opposite end of the spectrum (PostgreSQL: Here is every configuration tweak you could ever possibly want, have fun! vs MySQL: There is no configuration until you need one, then you have to find the docs for it. They aren't actually in any better shape than we are, except that they supply several preconfigured sample files for people to choose from. Which would be a good start, if nothing else… 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] Overhauling GUCS
On Jun 4, 2008, at 12:48, Andrew Dunstan wrote: I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. I'm not saying that this is necessarily admirable -- it's kind of the opposite end of the spectrum (PostgreSQL: Here is every configuration tweak you could ever possibly want, have fun! vs MySQL: There is no configuration until you need one, then you have to find the docs for it. Tell me how that's better. If that's what you want, simply remove all the comment lines from your config file. Problem solved. I didn't say it was better. The point is that it seems to be less confusing to non-DBAs. Which would be a good start, if nothing else… It's been suggested in the past. It is highly debatable that it would actually be an advance. Agreed; we've bandied around some better ideas here. Well, I haven't, I've just kibbitzed. But we can surely do better. 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] Overhauling GUCS
On Jun 4, 2008, at 13:31, Pavel Stehule wrote: do you thing, so any better config can help? It's not possible. And you can't tune database without well knowledge of applications that use database. Any automatic tools are joy for child. But some default PostgreSQL parameters are not optimal. Agreed. But, speaking as an app developer, I'm child-like in my DBA abilities. I could use a toy to help me with it. :-) 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] Overhauling GUCS
On Jun 4, 2008, at 13:12, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. Postgres will start happily with an empty configuration file, too. What's your point? That it's less daunting for inexperienced users to start with that. I'm not talking about how things work, I'm talking about what configurations are present to start with. That's all. 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] Overhauling GUCS
On Jun 4, 2008, at 13:57, Tom Lane wrote: So I think we should stop worrying about the file format and think about these two problems: * Can we present the config options in a more helpful way (this is 99% a documentation problem, not a code problem)? * Can we build a configuration wizard to tell newbies what settings they need to tweak? Amen, Tom. Nicely put. Thanks, 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] ERROR: operator is not unique with Custom Data Type
Howdy, I'm working on a custom data type based on TEXT that does case- insensitive, locale-aware comparisons, essentially by calling LOWER() to compare values. I'll have more to ask about this later, when I want to get feedback on the implementation. But right now I'm just writing tests and trying to get it all to work the way I think it should. So I've implemented operators and an operator class for the new type, and they work great. I've also added implicit casts between the other string data types: CREATE CAST (lctext AS text)WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (text AS lctext)WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (lctext AS varchar) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (varchar AS lctext) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (lctext AS bpchar) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (bpchar AS lctext) WITHOUT FUNCTION AS IMPLICIT; However, thanks to the implicit cast PostgreSQL finds more than one candidate operator when I compare properly casted values: try=# select 'a'::lctext = 'a'::text; ERROR: operator is not unique: lctext = text LINE 1: select 'a'::lctext = 'a'::text; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. So is there a way to resolve this? Would I need to add explicit operators between lctext and text (and more, betwein text and lctext), assuming that PostgreSQL would find those to be the best candidate operators? I'm kind of hoping that there's a simpler answer, because otherwise I'd have to create operators and classes for all of: ( lctext, lctext ) ( lctext,text ) ( text, lctext ) ( lctext, lctext ) ( lctext, varchar ) ( varchar, lctext ) ( lctext, bpchar ) ( bpchar, lctext ) And then I supposed that I'd have to do the same not only for the comparison operators in the operator class, but also any other binary operators (concatenation, regular expression, LIKE, etc.). This sounds like somewhat of a PITA, though I'd of course just do the cut-and- paste work to make it so if that was what's required. But is it? Is there no simpler way to do it? Many thanks, 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] ERROR: operator is not unique with Custom Data Type
On Jun 5, 2008, at 11:28, Martijn van Oosterhout wrote: On Thu, Jun 05, 2008 at 11:18:26AM -0700, David E. Wheeler wrote: I'm working on a custom data type based on TEXT that does case- insensitive, locale-aware comparisons, essentially by calling LOWER() to compare values. What makes this different from the citext project? citext is not locale-aware; please Tom's comments in the Case- Insensitve Text Comparison thread. However, thanks to the implicit cast PostgreSQL finds more than one candidate operator when I compare properly casted values: try=# select 'a'::lctext = 'a'::text; ERROR: operator is not unique: lctext = text LINE 1: select 'a'::lctext = 'a'::text; ^ What would you want postgresql to choose in this case. I was thinking that the ::text should be cast to ::lctext, as that's how `'a'::lctext = 'a'` works, but I keep going back and forth in my mind. Maybe 'a'::lctext should not equal 'A'::text. Whichever way you want it, make that direction implicit and the other direction assignment. I'm sure I'm missing something simple here. How do I make it assignment? Having A-B and B-A both as implicit just leads to ambiguity. Huh. That's what citext has, too: CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (text AS citext) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (bpchar AS citext) WITHOUT FUNCTION AS IMPLICIT; But I agree that there is confusion for PostgreSQL here. Thanks, 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] ERROR: operator is not unique with Custom Data Type
On Jun 5, 2008, at 11:51, Tom Lane wrote: I was thinking that the ::text should be cast to ::lctext, as that's how `'a'::lctext = 'a'` works, but I keep going back and forth in my mind. Maybe 'a'::lctext should not equal 'A'::text. It seems to me that lctext is sort of like a more-constrained version of text (like a domain), Yes, exactly. which suggests that the lctext - text direction can be implicit but the other direction should not be. Ah, okay. That's a good way of putting it. So I should just eliminate the implicit text - lctext cast, then? That will solve the problem? Moreover, if you don't have lctext - text be implicit then you will find that none of the non-comparison text functions work on lctext except with a cast; which is not the place you want to be. No, quite right. I concur with Martijn that having both directions implicit is a Bad Idea. BTW, I would encourage you to think of this project as citext version 2, rather than inventing a new name for the datatype. All you'll accomplish with that is make it hard for users of citext to transition. Fair enough. It was a working title, anyway. 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] ERROR: operator is not unique with Custom Data Type
On Jun 5, 2008, at 14:07, Martijn van Oosterhout wrote: I'm sure I'm missing something simple here. How do I make it assignment? # \h create cast Command: CREATE CAST Description: define a new cast Syntax: snip CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] I need to read up on the CAST documentation. Thanks. Huh. That's what citext has, too: CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (text AS citext) WITHOUT FUNCTION AS IMPLICIT; And citext probably doesn't work with 8.3? The casting rules wrt text have changed... Yes, that is correct. It builds, but the SQL doesn't all run properly. I'll be wading through all those failures once I get the basics worked out with v2. Thanks, 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] Overhauling GUCS
On Jun 5, 2008, at 14:47, Greg Smith wrote: This is why there's the emphasis on preserving comments as they pass into the GUC structure and back to an output file. This is one of the implementation details I haven't fully made up my mind on: how to clearly label user comments in the postgresql.conf to distinguish them from verbose ones added to the file. I have no intention of letting manual user edits go away; what I'm trying to do here (and this part is much more me than Josh) is make them more uniform such that they can co-exist with machine edits without either stomping on the other. Right now doing that is difficult, because it's impossible to tell the default comments from the ones the users added and the current comment structure bleeds onto the same lines as the settings. How about a simple rule, such as that machine-generated comments start with ##, while user comments start with just #? I think that I've seen such a rule used before. At any rate, I think that, unless you have some sort of line marker for machine-generated comments, there will be no way to tell them apart from user comments. Other possibilities for machine-comments: ## Machine comment ### Machine comment #! Machine comment #@ Machine comment #$ Machine comment #^ Machine comment # Machine comment I actually kinda like #!. It's distinctive and unlikely to appear in a user comment. Anyway, just food for thought. 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] Overhauling GUCS
On Jun 5, 2008, at 17:53, Greg Smith wrote: I was already considering keeping user comments as # while making all system-inserted ones #! ; many people are already used to #! having a special system-related meaning from its use in UNIX shell scripting which makes it easier to remember. Oooh, yeah. I hadn't even thought of that! I was just looking at characters on my keyboard and typing them in to see which ones I thought were most distinctive. This may be part of the reason I thought that #! was distinctive. :-) 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] Overhauling GUCS
On Jun 5, 2008, at 23:08, Heikki Linnakangas wrote: What comments do we consider machine-generated? Just the ones used to comment out settings, like #shared_buffers = 32MB or something else? Those and documentation comments. If the automatic tool lets alone all other kind of comments, I think we're fine. In fact, it wouldn't necessarily need to modify those comments either, it could simply add a new setting line below that: #shared_buffers = 32MB shared_buffers = 1024MB Well, we've been talking about having varying levels of documentation in the comments of the file based on the options passed to the configuration program. I think that these are the primary concern, though Greg, please do correct me if I'm mistaken. For extra safety, it could comment out old settings, perhaps with something like this: #shared_buffers = 32MB #shared_buffers = 1024MB # commented out by wizard on 2008-06-05 shared_buffers = 2048MB This would preserve a full change history in the file. It would become quite messy after a lo of changes, of course, but a user can trim the history by hand if he wants to. I guess that could be a feature. Personally, I use a vcs system for that. 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] Overhauling GUCS
On Jun 6, 2008, at 01:50, Andreas Pflug wrote: Two heretical questions: Do we need user generated comments at all? I can't remember ever having used any comment in postgresql.conf. That's a valid point. I've used comments to note by whom and when when a setting was changed. Why do so many people here insist on editing postgresql.conf as primary means of changing config params? Isn't a psql -c SET foo=bar; MAKE PERSISTENT just as good as sed'ing postgresql.conf or doing it manually? I think that there has been enough pushback against housing all the settings in the database, not to mention that it calls for an API, that just starting with something simpler to parse the file and rewrite it from the command-line might be a better first step. Looking around for different approaches, network appliances come to my mind, e.g. Cisco routers and PIX. You have 3 ways to configure a pix: - use a command line (using ssh or telnet, eqivalent to psql); WRITE MEMORY to make the changes survive a reboot. - use a web interface (or similar tool) - use tftp to up/download the complete config in and out, editing the file. User comments will be lost, with the exception of those that have been applied with special comment commands (equivalent to comment on). I think the first option there is the one that's been getting the most support here. 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] Better default_statistics_target
On Jun 12, 2008, at 17:55, Greg Sabino Mullane wrote: Glad to hear that, although I think this is only in HEAD, not backpatched, right? Well at any rate, I withdraw my strong support for 100 and join in the quest for a good number. The anything but 10 campaign I vote for 11. That's one louda, in'it? 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] Question about Encoding a Custom Type
Howdy,
Possibly showing my ignorance here, but as I'm working on updating
citext to be locale-aware and to work on 8.3, I've run into this
peculiarity:
try=# \encoding
UTF8
try=# select setting from pg_settings where name = 'lc_collate';
setting
-
en_US.UTF-8
(1 row)
try=# create table try (name citext);
try=# insert into try (name) values ('aardvark'), ('AAA');
try=# select name, name = 'aaa' from try;
name | ?column?
--+--
aardvark | f
AAA | t
(2 rows)
try=# insert into try (name) values ('aba'), ('ABC'), ('abc');
try=# select name, name = 'aaa' from try;
name | ?column?
--+--
aardvark | f
AAA | t
aba | f
ABC | f
abc | f
(5 rows)
try=# insert into try (name) values ('');
try=# select name, name = 'aaa' from try;
ERROR: invalid byte sequence for encoding UTF8: 0xf6bd
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
I've no idea what could be different about '' vs. any other value.
And if I do either of these:
select name, name = 'aaa'::text from try;
select name, name::text = 'aaa' from try;
It just works. I'm mystified.
My casts:
CREATE CAST (citext AS text)WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (text AS citext)WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (bpchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
Question about the code? It's all here (for now):
https://svn.kineticode.com/citext/trunk/
Hrm. Fiddling a bit more, I find that this fails, too:
try=# select citext_smaller( 'aardvark'::citext,
'AARDVARKasdfasdfasdfasdf'::citext );
ERROR: invalid byte sequence for encoding UTF8: 0xc102
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
So I guess that something must be up with citext_smaller(). It's quite
simple, though:
PG_FUNCTION_INFO_V1(citext_smaller);
Datum citext_smaller (PG_FUNCTION_ARGS) {
text * left = PG_GETARG_TEXT_P(0);
text * right = PG_GETARG_TEXT_P(1);
PG_RETURN_TEXT_P( citextcmp( PG_ARGS ) 0 ? left : right );
}
Context:
https://svn.kineticode.com/citext/trunk/citext.c
Anyone have any idea? Feedback would be *most* appreciated.
Thanks,
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] Question about Encoding a Custom Type
On Jun 16, 2008, at 02:52, Martijn van Oosterhout wrote: The only odd thing I see is the use of PG_ARGS to pass the arguments to citextcmp. But I can't see why it would break either. Can you attach a debugger and see where it goes wrong? Yes, I can do that, although I'm pretty new to C (let alone gdb), so I'm not sure exactly how to go about it. I'll try to get on IRC later today to see if anyone can help me along. As to the comment about freeing stuff, it's usually nice if btree comparison functions free memory because that way index rebuilds on large tables don't run you out of memory. Thanks. I'll add that to my list. 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] Question about Encoding a Custom Type
On Jun 16, 2008, at 09:24, David E. Wheeler wrote: On Jun 16, 2008, at 02:52, Martijn van Oosterhout wrote: The only odd thing I see is the use of PG_ARGS to pass the arguments to citextcmp. But I can't see why it would break either. Can you attach a debugger and see where it goes wrong? Yes, I can do that, although I'm pretty new to C (let alone gdb), so I'm not sure exactly how to go about it. I'll try to get on IRC later today to see if anyone can help me along. What's even weirder is that it can not work and then suddenly work: try=# select citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ); ERROR: invalid byte sequence for encoding UTF8: 0xe02483 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. try=# select citext_smaller( 'aardvark'::citext, 'AARDVARK'::citext ); citext_smaller AARDVARK (1 row) WTF? Logging onto IRC now… https://svn.kineticode.com/citext/trunk/ 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] Question about Encoding a Custom Type
On Jun 16, 2008, at 13:06, Tom Lane wrote:
David E. Wheeler [EMAIL PROTECTED] writes:
What's even weirder is that it can not work and then suddenly work:
Smells like uninitialized-memory problems to me. Perhaps you are
miscalculating the length of the input data?
Entirely possible. Here are the two functions in which I calculate size:
char * cilower(text * arg) {
// Do I need to free anything here?
char * str = VARDATA_ANY( arg );
#ifdef USE_WIDE_UPPER_LOWER
// Have wstring_lower() do the work.
return wstring_lower( str );
# else
// Copy the string and process it.
intinex, len;
char * result;
index = 0;
len= VARSIZE(arg) - VARHDRSZ;
result = (char *) palloc( strlen( str ) + 1 );
for (index = 0; index = len; index++) {
result[index] = tolower((unsigned char) str[index] );
}
return result;
#endif /* USE_WIDE_UPPER_LOWER */
}
int citextcmp (PG_FUNCTION_ARGS) {
// Could we do away with the varlena struct here?
text * left = PG_GETARG_TEXT_P(0);
text * right = PG_GETARG_TEXT_P(1);
char * lstr = cilower( left );
char * rstr = cilower( right );
intllen = VARSIZE_ANY_EXHDR(left);
intrlen = VARSIZE_ANY_EXHDR(right);
return varstr_cmp(lstr, llen, rstr, rlen);
}
Are you testing in an --enable-cassert build? The memory clobber
stuff can help to make it more obvious where such problems lurk.
I've just recompiled with --enable-cassert and --enable-debug, but got
no more information when I triggered the error, neither in psql nor in
the log. :-(
Thanks,
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] Question about Encoding a Custom Type
On Jun 16, 2008, at 13:41, Martijn van Oosterhout wrote:
Actually, real dumb question but: arn't you assume that text* values
are NULL terminated, because they're not...
char * cilower(text * arg) {
// Do I need to free anything here?
char * str = VARDATA_ANY( arg );
str here is not null terminated. You need text_to_cstring or something
similar.
Ah! That makes sense. I changed it to this:
#define GET_TEXT_STR(textp) DatumGetCString( \
DirectFunctionCall1( textout, PointerGetDatum( textp ) ) \
)
char * cilower(text * arg) {
// Do I need to free anything here?
char * str = GET_TEXT_STR( arg );
...
And now I don't get that error anymore. W00t! Many thanks.
Now I have just one more bizarre error: PostgreSQL thinks that a
citext column is not in an aggregate even when it is:
try=# CREATE AGGREGATE array_accum (anyelement) (
try(# sfunc = array_append,
try(# stype = anyarray,
try(# initcond = '{}'
try(# );
try=# CREATE TEMP TABLE srt ( name CITEXT );
try=#
try=# INSERT INTO srt (name)
try-# VALUES ('aardvark'),
try-#('AAA'),
try-#('â');
try=# select array_accum(name) from srt order by name;
ERROR: column srt.name must appear in the GROUP BY clause or be
used in an aggregate function
Um, what? Again, I'm sure I'm just missing something really stupid.
What might cause this?
Many thanks all,
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] Question about Encoding a Custom Type
On Jun 16, 2008, at 14:38, Tom Lane wrote: It's complaining about the use in ORDER BY. Okay, so stupid question: How can I get an array of the values in a given order? I guess this works: select array_accum(b) from ( select name from srt order by name ) AS A(b); Thanks, 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] Question about Encoding a Custom Type
On Jun 16, 2008, at 16:48, David Fetter wrote: select array_accum(b) from ( select name from srt order by name ) AS A(b); SELECT ARRAY(SELECT name FROM srt ORDER BY name); -- also works. Wow, somehow I'd missed that syntax over the years. Thanks David! 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
