Re: [HACKERS] search_path vs extensions
On Mon, May 25, 2009 at 11:16 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Hi, Preliminary note: I'm using the term extension as if it's what we already agree to call them, feel free to ignore this and use whatever term you see fit. We'll have the naming issue tackled, please not now though. [...] Few thoughts about the ideas: Basically I sort of don't like the idea of playing with search_path. In past I have set up a system where each extension had a separate schema. Maintaining per user search_path wasn't a very nice experience. And trying to alter it later on for whatever reason, especially from command line was even worse. :) I tend to avoid such designs now. :) I think it is much better to store objects in one schema (like public) and maintain access rights via roles. Like GRANT ltree_pkg TO userfoo; ...and build upon this idea. One of advantages of roles here is that you can DROP OWNED BY ltree_pkg; just as well as you did DROP SCHEMA ltree_pkg; And they take effect immediately, not requiring all sessions to restart to take up new search_path. Furthermore, I think it would be nice to have a cluster-wide pg_extension table which would list all the available (installed) packages available in the system (much like pg_database lists all databases present). This pg_extension should be used to rewrite extension objects into given schema using given role (which would be either fixed or user defined). The idea is that whenever user installs a RPM, DEB or whatever package the system registers the extension. Or she compiles from source and registers extension. Or we get a CPAN style utility which installs source, compiles and register the extension. Then administrator can copy over given extension into specific database, into specific schema. Simplest implementation would be that the pg_extension would contain a package name, package version (we can have multiple versions of the same package installed), install script (series of CREATE FUNCTION or whatever), uninstall script (may not be present) and some upgrade path would be needed as well. The installation would CREATE ROLE packagename_pkg and execute all CREATE FUNCTION inside schema PUBLIC. Then GRANT access. If administrator instructs so it might CREATE ROLE packagename_schema_pkg and execute all CREATE FUNCTION in schema schema. Uninstall would mean DROP OWNED BY packagename_pkg; OK, enough of my proposal. :-) Coming back to the pre_search_path -- it sounds somewhat like Oracle's PACKAGEs, only different (completely parallel hierarchy, but similar to schemas). I like the Oracle approach better though -- no messing with search_paths please... Best regards, Dawid -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : qne...@gmail.com : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent 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 if not exists (CINE)
On Wed, May 6, 2009 at 7:22 AM, Asko Oja asc...@gmail.com wrote: It was just yesterday when i wondering why we don't have this feature (i was trying to use it and it wasn't there :). The group of people who think it's unsafe should not use the feature. Clearly this feature would be useful when managing large amounts of servers and would simplify our release process. On Wed, May 6, 2009 at 5:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: [...] Yes, I did. I'm not any more convinced than I was before. In particular, the example you give is handled reasonably well without *any* new features, if one merely ignores object already exists errors. It sounds pretty amazing. Ignoring errors as a suggested way to use PostgreSQL. We run our release scripts inside transactions (with exception of concurrent index creation). So if something unexpected happens we are left still in working state. PostgreSQL ability to do DDL changes inside transaction was one of biggest surprises/improvements when switching from Oracle. Now you try to bring us down back to the level of Oracle :) Hm, You can do it easily today with help of PL/PgSQL, say like this: CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$ BEGIN BEGIN CREATE TABLE foo(i int, t text); EXCEPTION WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists'; END; BEGIN ALTER TABLE foo ADD COLUMN t text; EXCEPTION WHEN duplicate_column THEN RAISE NOTICE 'Column foo.t already exists'; END; END; ...the only drawback is that you need to have PL/PgSQL installed. :-) Personally I don't like 'CREATE IF NOT EXISTS'. I find it 'messy'. :-) What I wish PostgreSQL would have is ability to do conditional rollback to savepoint. This way one could write a PostgreSQL SQL script that would contain conditional behaviour similar to exceptions handling above. For instance backend could handle sort of EXCEPTION clause: SAVEPOINT create_foo; CREATE TABLE foo(i int, t text); START EXCEPTION WHEN duplicate_table; -- if there was duplicate_table exception, all -- commands within this block are executed. -- if there was no error, all commands are -- ignored, until we reach 'END EXCEPTION;' -- command. ROLLBACK TO create_foo; ALTER TABLE foo ADD COLUMN t text; END EXCEPTION; ...or some \conditional commands at psql client side. Just my 0.02 :) Best regards, Dawid -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : qne...@gmail.com : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Idea - fallback mode for psql backslash commands using information_schema
Hi! I think I've got a nice TODO item for psql client: When a client connects to a database which has unknown (newer) version it might be advisable to 'fallback' some commands to use INFORMATION_SCHEMA instead of system catalogs. For instance when connected to 8.4dev server using 8.3 client, after issuing \d foo_table you get: ERROR: column reltriggers does not exist LINE1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr Of course the information_schema will probably be less efficient and won't have PostgreSQL-specific items like 'Has OIDs', but the user experience should be better. My question is: does it look like a good TODO item? :) Best regards, Dawid -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : qne...@gmail.com : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] affected rows count
On Mon, Dec 22, 2008 at 9:07 PM, Bruce Momjian br...@momjian.us wrote: Grzegorz Jaskiewicz wrote: Hey folks, It doesn't stop to bug me, that postgres will return 0 number of affected rows, if table is triggered. Now, question is - is this fixable, but no one cares, or is it some sort of a design/implementation flaw and we just have to live with it. Would you show us an example of your problem? If I understand the problem correctly: atlantis= CREATE TABLE foo (i int PRIMARY KEY, t text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE atlantis= CREATE TABLE bar (i int PRIMARY KEY, t text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index bar_pkey for table bar CREATE TABLE atlantis= INSERT INTO foo (i,t) SELECT n, '#'||n FROM generate_series(0,99) AS g(n); INSERT 0 100 atlantis= INSERT INTO bar (i) SELECT i FROM foo; INSERT 0 100 atlantis= UPDATE foo SET t='##'||t; UPDATE 100 atlantis= CREATE OR REPLACE FUNCTION foo_trigger() RETURNS trigger AS $$ BEGIN UPDATE bar SET t=NEW.t WHERE i=NEW.i; RETURN NULL; END; $$ LANGUAGE plpgsql; atlantis= CREATE TRIGGER foo_update BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE foo_trigger(); CREATE TRIGGER CREATE FUNCTION atlantis= UPDATE foo SET t='##'||t; UPDATE 0 ^^ Grzegorz means such a situation. Personally I understand the current behavior to be correct -- since no row in that table is updated. OTOH when you use triggers for emulating table partitioning it leads to confusion (parent table was not updated, but the child table is (or isn't because there were really 0 rows updated -- you can't really tell)). Best regards, Dawid -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : qne...@gmail.com : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: Allow the UUID type to accept non-standard formats
On Fri, Oct 10, 2008 at 7:28 AM, Mark Mielke [EMAIL PROTECTED] wrote: Robert Haas wrote: While we could perhaps accept only those variant formats which we specifically know someone to be using, it seems likely that people will keep moving those pesky dashes around, and we'll likely end up continuing to add more formats and arguing about which ones are widely enough used to deserve being on the list. So my vote is - as long as they don't put a dash in the middle of a group of four (aka a byte), just let it go. I somewhat disagree with supporting other formats. Reasons include: 1) Reduced error checking. Hmm, I tend to disagree. If UUIDs were variable length (different number of digits), then perhaps yes. But as all UUIDs have same number of digits, the dashes inbetween them act as decorators. 2) The '-' is not the only character that people have used. ClearCase uses '.' and ':' as punctuation. I would be more in favor of accepting MAC-address style notation AA:BB:CC:DD also, in that case, but I think its going too far... So, I am for sticking with dashes and groups of four :) 3) People already have the option of translating the UUID from their application to a standard format. Regexp, the swiss-army knife of data manipulation. ;) While possible, it really is not that easy and efficient. At least we should accept dashless UUIDs, so instead of tediously reformatting UUID once could do s/-//g 4) As you find below, and is probably possible to improve on, a fixed format can be parsed more efficient. What I was thinking about is using the same lookup-table style approach as encode()/decode() pair uses. Should be faster than current implementation, and skipping over '-' (and even ':' or '.') is even simpler. I don't know internals good enough to know how that would work in encodings like UTF16... See http://doxygen.postgresql.org/encode_8c-source.html#l00107 Best regards, Dawid Kuroczko -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : [EMAIL PROTECTED] : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On Fri, Oct 3, 2008 at 3:36 PM, Brian Hurt [EMAIL PROTECTED] wrote: OK, I have a stupid question- torn pages are a problem, but only during recovery. Recovery is (I assume) a fairly rare condition- if data corruption is going to happen, it's most likely to happen during normal operation. So why not just turn off CRC checksumming during recovery, or at least treat it as a much less critical error? During recovery, if the CRC checksum matches, we can assume the page is good- not only not corrupt, but not torn either. If the CRC checksum doesn't match, we don't panic, but maybe we do more careful analysis of the page to make sure that only the hint bits are wrong. Or maybe not. It's only during normal operation that a CRC checksum failure would be considered critical. Well: 1. database half-writes the page X to disk, and there is power outage. 2. we regain the power 2. during recovery database replay all WAL-logged pages. The X page was not WAL-logged, thus it is not replayed. 3. when replaying is finished, everything looks OK at this point 4. user runs a SELECT which hits page X. Oops, we have a checksum mismatch. Best regards, Dawid Kuroczko -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : [EMAIL PROTECTED] : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On Thu, Oct 2, 2008 at 7:42 PM, Jonah H. Harris [EMAIL PROTECTED] wrote:
It's not the buffeting it's the checksum. The problem arises if a page is
read in but no wal logged modifications are done against it. If a hint bit
is modified it won't be wal logged but the page is marked dirty.
Ah. Thanks Greg. Let me look into this a bit before I respond :)
Hmm, how about, when reading a page:
read the page
if checksum mismatch {
flip the hint bits [1]
if checksum mismatch {
ERROR
} else {
emit a warning, 'found a torn page'
}
}
...that is assuming we know which bit to flip
and that we accept the check will be a bit
weaker. :) OTOH this shouldn't happen too
often, so performance should matter much.
My 0.02
Best regards,
Dawid Kuroczko
[1]: Of course it would be more efficient to flip
the checksum, but it would be tricky. :)
--
..``The essence of real creativity is a certain
: *Dawid Kuroczko* : playfulness, a flitting from idea to idea
: [EMAIL PROTECTED] : without getting bogged down by fixated demands.''
`..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?
On Mon, Jul 21, 2008 at 9:43 PM, Tom Lane [EMAIL PROTECTED] wrote: Comments? Tough question. First PL/Proxy. One objection against PL/Proxy is that it might interfere with SQL-MED implementation. I don't think its the case because both solve slightly different problems. SQL-MED brings remote tables local (importing whole schemas and such). PL/Proxy allows remote calls and load balancing/distribution. I think it might be even valuable to use these two together (building on strengths of these two). By the way, while reading SQL-MED standard I didn't find obvious way of calling ad-hoc remote tables (as in Oracle's db links for instance), only either creating remote tables or running in passthrough mode. I guess I did miss something, I was only skimming through it. As for citext I am less enthusiastic. While I understand the need for case insensitivity, it feels hacky. Like something which screams to be more general but fails to do so. And if citext, how about say rawtext (locale-less text)? [1] utf8text (utf8 compilant text available even if POSIX localle is used) and so on. ;) I would still want citext to get into contrib, but my heart is strongest with PL/Proxy here. Regards, Dawid [1]: Actually I think it would be better to upgrade bytea into something like locale-less, 8-byte, raw-text-alike. I mean, be able to do regex queries, LIKE queries, etc on it. I sometimes miss that kind of functionality. -- .. ``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : [EMAIL PROTECTED] : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFD: ALTER COLUMN .. SET STORAGE COMPRESSED;
Hello!
Currently the TOASTing code does its magic when whole tuple is
larger than TOAST_TUPLE_TARGET which happens to be around 2KB.
There are times though when one is willing to trade using (fast) CPU to
reduce amount of (slow) I/O. A data warehousing types of workload
most notably. Rarely used large columns which are likely to compress
well but are not large enough to trigger inline compression.
As we already have four types of ALTER COLUMN .. SET STORAGE
{ PLAIN | EXTERNAL | EXTENDED | MAIN } I would like to add
COMPRESSED which would force column compression (if column is
smaller than some minimun, I guess somwehwere between 16 and 32 bytes).
First of all, would such a feature be desirable? [1]
...as for implementation idea, so far I see it more or less like this:
* src/backend/access/common/heaptuple.c:
for tuples with COMPRESSED attributes, we set the infomask bit
HEAP_HASEXTERNAL, so that tuple will trigger TOAST regardless
of size.
* src/backend/access/heap/tuptoaster.c:
- add a bool need_compress = false; around line 425.
- while scanning the attributes (lines 472-575), mark the ones which
should be COMPRESSED
- if (need_compress), compress every marked column.
- perhaps refactor inline compression code (639-659) as a static
funcion shared with need_compress part above.
Does this sound reasonable?
PS: as a side note: I wonder if perhaps we could try compression erarlier,
at 1KB or event at 0.5KB, but leave TOASTing at 2KB limit)?
[1]: Actually some time ago I did write a system which stores tons of
real[0:59] (an hour's worth of every minute readings) data. Such column
takes approximately 246 bytes. For fun and experiment I did transform
the data into real[0:23][0:59] storing whole day's data. To my surprise
such column stores between 64 (!) and 5968. Also 66% of values were
taking less than 254 bytes (and 55% 128 bytes)... And as the data
is much larger than RAM and read randomly, having it shrunk by more
than 25% is tempting. Hence the idea of SET STORAGE COMPRESSED.
I know such schema is flawed by design, but I guess there are other types
of data which would also see benefit from such an option.
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal problems with heroin - at first it
sorta works, but after a while things just get out of hand.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFD: ALTER COLUMN .. SET STORAGE COMPRESSED;
On Tue, Jun 10, 2008 at 5:25 PM, Tom Lane [EMAIL PROTECTED] wrote:
Dawid Kuroczko [EMAIL PROTECTED] writes:
As we already have four types of ALTER COLUMN .. SET STORAGE
{ PLAIN | EXTERNAL | EXTENDED | MAIN } I would like to add
COMPRESSED which would force column compression (if column is
smaller than some minimun, I guess somwehwere between 16 and 32 bytes).
Please see previous discussions about per-column toasting parameters,
for instance
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
http://archives.postgresql.org/pgsql-general/2007-08/msg01129.php
I think the general consensus was that we want more flexible access to
the compression knobs than just another STORAGE setting.
Sounds like a right way to do it. Perhaps the syntax should be something like:
ALTER TABLE tab ALTER COLUMN x WITH (storage_parameter = value, ...);
With storage parameters like:
compress -- enable/disable compression (like PLAIN or EXTERNAL)
min_input_size -- don't compress if smaller than size
min_comp_rate -- leave uncompressed if rate is smaller than
toast -- for out-of-line storage parameters?
compression_algo -- for specifying alternative algorithms if any
(per Alvaro's suggestion).
Perhaps it would be wise to introduce GUCs with default values (as we have now
ALTER COLUMN .. SET STATISTICS and default_statistics_target), named
for example:
default_column_min_input_size (and so on).
ALTER COLUMN .. SET STORAGE ... should be aliases for WITH (...) and be
deprecated I guess.
The HEAP_HASEXTERNAL infomask bit should probably be used to trigger
TOASTing code. Perhaps it should be renamed then? I am worried if storage
parameters wouldn't introduce overhead in PostgreSQL's key parts.
...as for compression_algo, perhaps it could be an oid of compression
function(s)
(we need to decompress too). Also we would need to store information which
algo was used to compress the column. Perhaps a byte between varvarlena
herader and actual compressed data (this way we could have multiple algos
simultaneousley).
Speaking of algorithms, I think that e2compr (ext2 filesystem with transparent
compression) could be a nice source of input in this area.
http://e2compr.sourceforge.net/
(Having algos as plugins would allow us to use foreign licenses (gzip) or
event patented algos in countries where software patents are prohibited
without risking anything in core PostgreSQL)
OK, enough for today. Good night.
Regards,
Dawid
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal problems with heroin - at first it
sorta works, but after a while things just get out of hand.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
On Wed, May 7, 2008 at 7:52 AM, KaiGai Kohei [EMAIL PROTECTED] wrote: Tom, Thanks for your reviewing. The patch hasn't got a mode in which SELinux support is compiled in but not active. This is a good way to ensure that no one will ever ship standard RPMs with the feature compiled in, because they will be entirely nonfunctional for people who aren't interested in setting up SELinux. I think you need an enable_sepostgres GUC, or something like that. (Of course, the overhead of the per-row security column would probably discourage anyone from wanting to use such a configuration anyway, so maybe the point is moot.) We can turn on/off SELinux globally, not bounded to SE-PostgreSQL. The reason why I didn't provide a mode bit like enable_sepostgresql is to keep consistency in system configuration. Hmm, I think ACE should be a CREATE DATABASE parameter. If I were to create a SE-database I would wish that disabling it was more difficult than changing a GUC in database. And being able to set it on per-database basis would help get SE/ACE enabled by packagers. Regards, Dawid -- Solving [site load issues] with [more database replication] is a lot like solving your own personal problems with heroin - at first it sorta works, but after a while things just get out of hand. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Core team statement on replication in PostgreSQL
On Thu, May 29, 2008 at 4:12 PM, Tom Lane [EMAIL PROTECTED] wrote: The Postgres core team met at PGCon to discuss a few issues, the largest of which is the need for simple, built-in replication for PostgreSQL. [...] We believe that the most appropriate base technology for this is 1 probably real-time WAL log shipping, as was demoed by NTT OSS at PGCon. We hope that such a feature can be completed for 8.4. Ideally this would be coupled with the ability to execute read-only queries on the slave servers, but we see technical difficulties that might prevent that from being completed before 8.5 or even further out. (The big problem is that long-running slave-side queries might still need tuples that are vacuumable on the master, and so replication of vacuuming actions would cause the slave's queries to deliver wrong answers.) Again, this will not replace Slony, pgPool, Continuent, Londiste, or other systems for many users, as it will be not be highly scalable nor support long-distance replication nor replicating less than an entire installation. But it is time to include a simple, reliable basic replication feature in the core system. Hello! I thought I would share a few thoughts of my own about the issue. I have a hands-on experience with Oracle and MySQL apart from PostgreSQL so I hope it will be a bit interesting. The former has a feature called physical standby, which looks quite like our WAL-shipping based replication. Simply archived logs are replayed on the standby database. A primary database and standby database are connected, and can stream the logs directly. They either copy the log when its finished (as we do now) or can do it in coninuous manner (as I hope we will be able to). It is possible to have a synchronous replication (where COMMIT on primary database succeeds when the data is safely stored on the standby database). I think such a feature would be a great advantage for PostgreSQL (where you cannot afford to loose any transactions). Their standby database is not accessible. It can be opened read-only, but during that time replication stops. So PostgreSQL having read-only and still replicating standby database would be great. The other method is logical standby which works by dissecting WAL-logs and recreating DDLs/DMLs from it. Never seen anyone use it. ;-) Then we have a mysql replication -- done by replaying actual DDLs/DMLs on the slaves. This approach has issues, most notably when slaves are highly loaded and lag behind the master -- so you end up with infrastructure to monitor lags and turn off slaves which lag too much. Also it is painful to setup -- you have to stop, copy, configure and run. * Back to PostgreSQL world As for PostgreSQL solutions we have a slony-I, which is great as long as you don't have too many people managing the database and/or your schema doesn't change too frequently. Perhaps it would be maintainable more easily if there would be to get DDLs (as DDL triggers or similar). Its main advantages for me is ability to prepare complex setups and easily add new slaves). The pgpool solution is quite nice but then again adding a new slave is not so easy. And being a filtering layer between client and server it feels a bit fragile (I know it is not, but then again it is harder to convince someone that yes it will work 100% right all the time). * How I would like PostgreSQL WAL-replication to evolve: First of all it would be great if a slave/standby would contact the master and maintain the state with it (tell it its xmin, request a log to stream, go online-streaming). Especially I hope that it should be possible to make a switchover (where the two databases exchange roles), and in this the direct connection between the two should help. In detail, I think it should go like this: * A slave database starts up, checks that it works as a replica (hopefully it would not be a postgresql.conf constant, but rather some file maintained by the database). * It would connect to the master database, tell where in the WAL it is now, and request a log N. * If log N is not available, request a log from external supplied script (so that it could be fetched from log archive repository somewhere, recovered from a backup tape, etc). * Continue asking, until we get to the logs which are available at master database. * Continue replaying until we get within max_allowed_replication_lag time, and open our slave for read-only queries. * If we start lagging too much perhaps close the read-only access to the database (perhaps configurable?). I think that replication should be easy to set up. I think our archive_command is quite easy, but many a person come with a lot of misconceptions how it works (and it takes time to explain them how it actually work, especially what is archive_command for, and that pg_start_backup() doesn't actually _do_ backup, but just tells PostgreSQL that backup is being done). Easy to setup and easy to switchover (change the
Re: [HACKERS] Cached Query Plans
On Mon, Apr 14, 2008 at 5:01 PM, Csaba Nagy [EMAIL PROTECTED] wrote: On Mon, 2008-04-14 at 10:55 -0400, Mark Mielke wrote: The other ideas about automatically deciding between plans based on ranges and such strike me as involving enough complexity and logic, that to do properly, it might as well be completely re-planned from the beginning to get the most benefit. ... except if you hard-wire the most common alternative plans, you still get the benefit of cached plan for a wider range of parameter values. Not to mention that if you know you'll cache the plan, you can try harder planning it right, getting possibly better plans for complex queries... you could argue that complex queries tend not to be repeated, but we do have here some which are in fact repeated a lot in batches, then discarded. So I guess a cached plan discard/timeout mechanism would also be nice. I think ANALYZE on tables involved should _force_ replanning of cached query. After all, if ANALYZE was fired, then contents changed substantially and replanning feels like a good idea. As for planner getting smarter (and slower ;)) -- complex queries tend not to be repeated -- so it is worth the trouble to plan them carefully. These would benefit from smarter planer with or without caching. The problem is with simple queries, which can be argued are a majority of queries. its where the caching comes in. If you cache the queries, you can let the planner be smarter (and slower). If you don't cache, you probably don't want trade frequent simple query's speed for once in a while complex query. That stated, for me the most important feature is the possibility to have a good online query statistics. :) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Lessons from commit fest
On Mon, Apr 14, 2008 at 6:45 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: [...] As far as the Wiki page is concerned, it would be good to make sure the entries have a bit more info than just a header line -- things such as author, who reviewed and what did the reviewer say about it. Some of it is already there. Something else we learned is that the archives are central (well, we already knew that, but I don't think we had ever given them so broad use), and we've been making changes to them so that they are more useful to reviewers. Further changes are still needed on them, of course, to address the remaining problems. Lastly, I would say that pushing submitters to enter their sent patches into the Wiki worked -- we need to ensure that they keep doing it. I think this should be explained nicely in developer FAQ. The whole process preferably. As a first time contributor ;) I must say I was (and still am, a bit) confused about the process. The FAQ point 1.4 says to discuss it on -hakers unless its a trivial patch. I thought the patch would be trivial, sent it to -patches. Then, later on I thought that perhaps it should be discussed on the -hackers nonetheless, so I have written there also: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00147.php then the patch got rejected, if I understand correctly. Now assuming I want to prepare patch for something else, at what point does Wiki come in? Should I send it to -patches and put it on wiki? Or perhaps wait for some developer's suggestion put it on the wiki? Should I start discussion on -hackers or is -patches enough? I know that with time they look trivial -- but at least I felt quite uncertain about them when sending first patch. . Don't forget to update developer FAQ as well. :) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
On Sun, Apr 13, 2008 at 2:26 PM, PFC [EMAIL PROTECTED] wrote: Oracle keeps a statement/plan cache in its shared memory segment (SGA) that greatly improves its performance at running queries that don't change very often. Can we have more details on how Oracle does it ? For inspiration... Why limit ourselves with Oracle? How all major proprietary RDBMSs do it. Here is a nice presentation I've found on DB2, they call it Dynamic Statement Cache: http://www.tbrug.com/TB%20UG%20Dynamic%20Statement%20Cache.ppt Here is what I'm thinking about : Don't flame me too much about implementation issues, this is just throwing ideas in the air to see where they'll fall ;) * global plan cache in shared memory, implemented as hashtable, hash key being the (search_path, query_string) Doubt : Can a plan be stored in shared memory ? Will it have to be copied to local memory before being executed ? Well, Oracle uses terms hard parse and soft parse, the former being preparing the whole query, the latter reusing query plan prepared by some other session. More or less. See this link for more detailed description: http://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:2588723819082 (this is quite interesting read) This stores : - the plans (not for all keys, see below) - the stats : [...] I am not too sure that plans and statistical counters should be stored together... Probably plans should go in one place, and statistics should go to the stats collector (I know he's not quite ready for this ;)). There should be also a way to query this to display the statistics (ie what query is killing my server ?), and a way to purge old plans. Hm, a limit on how much memory can be used for plans (query_plan_cache_size GUC?), and a LRU/LFU expiration of old plans? * every time a Parse message comes up : - look if the (search_path, query_string) is in the cache - if it is in the cache : - if there is a cached plan, make the unnamed statement point to it, and we're done. - if there is no cached plan, prepare the query, and put it in the unnamed statement. Now, the query has been parsed, so we can decide if it is cacheable. Should this be done in Parse, in Bind, or somewhere else ? I have no idea. For instance, queries which contain VALUES() or IN( list of consts ) should not be cached, since the IN() is likely to change all the time, it would just trash the cache. Using =ANY( $1 ) instead will work with cached plans. Perhaps a GUC for controlling query cache should heve three values: none -- don't cache any statement smart -- use heuristics for deciding whether to cache it all -- force caching all queries -- for uncommon/statistical/testing purposes. Also, will a plan to be cached have to be prepared with or without the parameters ? That's also an interesting question... Perhaps the user should also be able to specify wether to cache a plan or not, or wether to use the params or not, with hint flags in the query string ? (like mysql, /* flags */ SELECT blah ) I don't like the hint flags. They tend to haunt later on (when the database gets smarter, but application forces it to be dumb). I would say a GUC. GUC gives freedom of change to the application, and can also be set per user with ALTER USER. Now, if the query is cacheable, store it in the cache, and update the stats. If we decided to store the plan, do that too. For instance we might decide to store the plan only if this query has been executed a certain number of times, etc. Interesting idea. I think I like it. * In the Execute message, if a cached plan was used, execute it and update the stats (time spent, etc). Now, about contention, since this is one shared hashtable for everyone, it will be fought for... However, the lock on it is likely to be held during a very small time (much less than a microsecond), so would it be that bad ? Also, GUC can be used to mitigate the contention, for instance if the user is not interested in the stats, the thing becomes mostly read-only I would say: keep the stats separate. For evey plan cached generate some unique id (Perhaps OID? I am not convinced), and use this ID as the key for the statistics. I tend to think of it as a temporary table, and temporary table stats. :) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans (was: global prepared statements)
On Sat, Apr 12, 2008 at 2:44 PM, Perez [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], PFC wrote: So, where to go from that ? I don't see a way to implement this without a (backwards-compatible) change to the wire protocol, because the clients will want to specify when a plan should be cached or not. Since the user should not have to name each and every one of the statements they want to use plan caching, I see the following choices : Doesn't Oracle do this now transparently to clients? That, I believe Oracle keeps a statement/plan cache in its shared memory segment (SGA) that greatly improves its performance at running queries that don't change very often. From that point of view, Oracle at least sees benefits in doing this. From my POV a transparent performance enhancer for all those PHP and Rails apps out there. There are other benefits as well. Oracle lets you see the statistics associated with given plans. So you can see how many times given (cached) query was executed, how much resources did it consume and do on. Right now the only way of getting such information from PostgreSQL is by logging all queries and analyzing logs. The current_query column of pg_stat_activity is useless as the (prepared) queries are usually so short lived that you will see one execution out of thousands happening. Nooow, suppose we do have cached plans. Then we can have a view pg_stat_queries + a stats collector which will track number of executions, number of blocks hit, blocks read, etc. Would be great! :) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \G command -- send query and output using extended format
On Thu, Apr 3, 2008 at 6:44 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Bruce Momjian escribió: It seems more helpful if there were \x option to use extended format only when the output is too wide. TODO already has: o Add auto-expanded mode so expanded output is used if the row length is wider than the screen width. Consider using auto-expanded mode for backslash commands like \df+. Some sort of \x auto? Sounds interesting ... Hmm, seems doable. While writing the \G patch I wanted to keep the changes to minimum, so it would be clear what gets done. What I don't like about that patch is that I've added in struct _printTableOpt another bool extend_once after the extend bool. I think there should be a format Enum, which would take values like NORMAL, EXTENDED, and EXTENDED_ONCE -- but this would be a much more invasive patch. Oh, and coincidentally its where AUTO format should go. :) Now, assuming we want \x auto there are couple of things to discuss before actually coding. 1. Adding \x auto changes how \x (and \pset). Currently we accept: \x - toggle between on and off \x off - turn extended format off \x anything - turn extended format on. if doing, auto we need to change it to something like, \x - toggle between on and off, if in auto, toggle to off \x on - extended format on \x off - extended format off \x auto - auto extended format \x anything else - extended format on with a depreciated warning. 2. Do we want \G? I would say yes. ;) But it should get discussed. pgsql-general perhaps? 3. We should decide how each of the commands work in auto mode. I think it should be something like: if output is less or equal than screen width -- use normal mode, else extended. I we have \g and \G, they should be handled a bit differently in auto mode: ; -- perform automatic format adjustment \g -- force normal mode (probably for file-output) \G -- force extended mode (probably for file-output). I especially would like an opinion on this from you. 4. And as for using \x for the one-shot expanded output, I think it would be possible to allow queries like: SELECT * FROM foo\x ...but I think fundamental problem with this approach is that it promotes \x to be able to submit query. I don't think its a good idea. Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \G command -- send query and output using extended format
Hi! I have sent a patch to pgsql-patches: http://archives.postgresql.org/pgsql-patches/2008-04/msg00050.php ...which adds \G command to psql client. The idea of \G command is to perform the query, but with printing query results using extended table output format. For example: postgres=# SELECT * FROM pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port ---+--+-+--+--+-+-+---+---+---+-+- 11511 | postgres | 11729 | 10 | postgres | SELECT * FROM pg_stat_activity; | f | 2008-04-03 14:40:15.277272+02 | 2008-04-03 14:40:15.277272+02 | 2008-04-03 14:39:50.050512+02 | | -1 (1 row) postgres=# SELECT * FROM pg_stat_activity\G -[ RECORD 1 ]-+--- datid | 11511 datname | postgres procpid | 11729 usesysid | 10 usename | postgres current_query | SELECT * FROM pg_stat_activity waiting | f xact_start| 2008-04-03 14:41:47.533763+02 query_start | 2008-04-03 14:41:47.533763+02 backend_start | 2008-04-03 14:39:50.050512+02 client_addr | client_port | -1 postgres=# SELECT * FROM pg_stat_activity\g datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port ---+--+-+--+--++-+---+---+---+-+- 11511 | postgres | 11729 | 10 | postgres | SELECT * FROM pg_stat_activity | f | 2008-04-03 14:42:09.940897+02 | 2008-04-03 14:42:09.940897+02 | 2008-04-03 14:39:50.050512+02 | | -1 (1 row) Comments anyone? Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \G command -- send query and output using extended format
On Thu, Apr 3, 2008 at 4:35 PM, Tom Lane [EMAIL PROTECTED] wrote: Dawid Kuroczko [EMAIL PROTECTED] writes: The idea of \G command is to perform the query, but with printing query results using extended table output format. Seems a bit useless --- if you prefer \x format, wouldn't you prefer it all the time? Or at least often enough that the toggling command is fine? I'm dubious that this is worth eating up a command letter for. No, the point is that I usually have mixed queries -- ones which are most comfortably viewed in normal format (many not-so-long rows), and ones which are best viewed expanded (little rows, many columns). Alternating between formats using \x is, at least for me, a bit cumbersome: usually _after_ I wrote a query I realize it would look more readable in expanded format, which is a bit too late. So I run the query, ctrl+c, \x, rerun the query... and forget to turn expanded mode off afterwards. I think that ability to decide about the format after the query, not before, can be quite useful especially when writing ad-hoc queries. Incidentally \g and \G is also used more or less similarily by our dolphin-loving friends -- which doesn't help using \G for other things. Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Lazy constraints / defaults
On Thu, Mar 20, 2008 at 4:56 PM, Decibel! [EMAIL PROTECTED] wrote: This would be very useful for me, and would satisfy the OP's request. Can we get a TODO? If you feel adventureous you may UPDATE catalog constrains directly, which will work more or less as enforce, don't validate, don't lock. ;-) Worked for me. ;-) I deliberately don't say where to update. ;-) But for a TODO feature it should LOUDLY INDICATE that given constraint is NOT VALIDATED, After all, PostgreSQL is famous for its high ACIDity standards. [ And event then... ;) ] Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rewriting Free Space Map
On Mon, Mar 17, 2008 at 6:23 PM, Tom Lane [EMAIL PROTECTED] wrote: I'm not wedded to forks, that's just the name that was used in the only previous example I've seen. Classic Mac had a resource fork and a data fork within each file. Don't think I like maps though, as (a) that prejudges what the alternate forks might be used for, and (b) the name fails to be inclusive of the data fork. Other suggestions anyone? Shadow? As each err, fork trails each relfilenode? (Or perhaps shade). Hints? As something more generic than map? Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idea: Comments on system catalogs?
On Fri, Mar 7, 2008 at 3:51 PM, Bruce Momjian [EMAIL PROTECTED] wrote: ~ Jim C. Nasby wrote: On Wed, Jul 04, 2007 at 01:03:20PM +0200, Dawid Kuroczko wrote: Hello. I think it could be a nice idea to put descriptions from http://www.postgresql.org/docs/8.2/static/catalogs.html into system catalogs itself. I.e., make a bunch of COMMENT ON COLUMN pg_class.relname IS 'Name of the table, index, view, etc.'; ... COMMENT ON COLUMN pg_class.relkind IS 'r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table'; Actually, this does exist for some things in the catalog; I suspect it just wasn't done in the past (perhaps Postgres didn't originally have comments). I think it would be a useful addition. But I think it'd need to be more than just a .sql file (initdb would probably need to be modified). Ideally, we'd be able to suck the info out of the appropriate .sgml files. Added to TODO: * Add comments on system tables/columns using the information in catalogs.sgml Ideally the information would be pulled from the SGML file automatically. Since I have raised the issue back then, I volunteer for doing this TODO. As I am lazy by nature, I am sure I will try to use SGML files instead of tediuos copypaste routine. ;-) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Lazy constraints / defaults
On Sun, Mar 9, 2008 at 7:25 PM, Tom Lane [EMAIL PROTECTED] wrote: =?ISO-8859-2?Q?Micha=B3_Zaborowski?= [EMAIL PROTECTED] writes: I would like to be able to add CONSTRAINT and/or DEFAULT with out affecting old rows. You mean without actually checking that the old rows satisfy the constraint? There's approximately zero chance that that proposal will be accepted. I think the problem here is to minimize the time when table is held by exclusive lock, Something similar to the CREATE INDEX CONCURRENTLY (i.e. hold exclusive lock for a jiffy, then do the actual work for the old tuples). So, the proposal would read as to add the ability to perform: ALTER TABLE CONCURRENTLY ALTER COLUMN foo SET NOT NULL ...where exclusive lock would be held to place the constraint (so all new tuples would satisfy it), lock would be released and the old tuples would be checked to make sure the constraint is valid. Should a NULL value be found or should the backend die, the constraint should disappear or be marked invalid. Yes, it sounds strange, but... Let's say I have big table, I want to add new column, with DEFAULT and NOT NULL. Normally it means long exclusive lock. So - right now I'm adding plain new column, then DEFAULT, then UPDATE on all rows in chunks, then NOT NULL... Can it be little simpler? Just do it all in one ALTER command. alter table tab add column col integer not null default 42 check (col 0); I think this will not solve the OP's problem. He wants to minimize the time a table is under exclusive lock, and this ALTER command will effectively rewrite the whole table (to add new not null column). Probably a workable solution would be to play with inheritance: -- Add the NULL col colum: ALTER TABLE tab ADD COLUMN col integer; -- Create a table which will have col NOT NULL CREATE TABLE tab_new (LIKE tab INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ) INHERITS (tab); ALTER TABLE tab_new ALTER COLUMN col SET NOT NULL; -- Make the new values go to tab_new, if simple enough same might be done for UPDATEs CREATE RULE insert_new AS ON INSERT TO tab DO INSTEAD INSERT INTO tab_new VALUES (NEW.*); -- Now, make a job which will do something like this: START TRANSACTION ISOLATON LEVEL SERIALIZABLE; UPDATE ONLY tab SET col = 42 WHERE id BETWEEN n AND n + 1000; INSERT INTO tab_new SELECT * FROM ONLY tab WHERE id BETWEEN n AND n + 1000; -- or better: -- INSERT INTO tab_new SELECT a,b,c,42 AS col FROM ONLY tab WHERE id BETWEEN n AND n + 1000 FOR UPDATE; DELETE FROM ONLY tab WHERE id BETWEEN n AND n + 1000; COMMIT; -- Finally, exhange parti^W^W get rid of old tab: SELECT count(*) FROM ONLY tab; -- should be zero ALTER TABLE tab RENAME TO tab_old; ALTER TABLE tab_new RENAME TO tab; ALTER TABLE tab NO INHERIT tab_old; Of course each step should be done in transaction, probably starting with explicit LOCK. And extra care should be taken with respect to the UNIQUE constraints. In short: unless you are 100% sure what you are doing, don't. :-) Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFD: hexstring(n) data type
Following the discussion on making UUID data type to be much more liberal ( http://archives.postgresql.org/pgsql-hackers/2008-02/msg01214.php ) I have decided to try to approach it from more general perspective. The current state of code is available at: http://qnex.net/hexstring-0.1-2008-03-03.tgz And now for more details: The idea is to have a data type HEXSTRING(n) which can have an optional typemod specifying the size of data (in bytes). Internally the datatype is binary compatible with bytea, but I/O is done as hex-encoded strings. The format is liberal, isspace() and ispunct() characters are skipped while the digits are read. I have played with two versions of hexstringin() function, one which uses strtoul() function and the other which uses home brew code. The latter appears to be faster, so I stayed with that. But I would appreciate comments on this from more experienced. So, what are the use cases? CREATE DOMAIN liberal_uuid AS hexstring(16); CREATE DOMAIN liberal_macaddr AS hexstring(6); ...it allows for creating other standard hex-types, as for example: CREATE DOMAIN wwn AS hexstring(8); -- http://en.wikipedia.org/wiki/World_Wide_Name Also it can be a convenient alternative to bytea format (I know, the encode()/decode() pair), especially when you have to format output data as some fancy hex-string. The code is currently just a bunch of input/output/typemod functions which appear to work. I will add casts, operators, etc -- they most likely will be nicked from bytea. What I would like to also add is ubiquitous to_char(hex, format) function. For an UUID-compatilbe format it would be called as: SELECT to_char(hex, '----') or SELECT to_char(hex, '8x-4x-4x-4x-12x') where x is expanded as [0-9a-f] digit and X is expanded as [0-9A-F]. I am not sure what to do about variable length hexstrings, I am considering something like to_char(hex, '8X-') which would produce something like '--' for a 12-byte hexstring (what to do about dangling '-' ?). ...but the original case against liberal UUID was that it would make the I/O slower. My simple test: postgres=# CREATE TABLE uuids AS SELECT uuid_generate_v4()::text AS u FROM generate_series(1,1000); CREATE postgres=# CREATE TEMP TABLE text ON COMMIT DROP AS SELECT u::hexstring(16) FROM uuids; SELECT Time: 13058.486 ms postgres=# CREATE TEMP TABLE test ON COMMIT DROP AS SELECT u::uuid FROM uuids; SELECT Time: 13536.816 ms ...now hexstring is varlena type but does not use strtoul. Perhaps uuid might be more liberal too. What do you think about it? Regards, Dawid -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Proposal: wildcards in pg_service.conf
On Thu, Feb 28, 2008 at 11:27 AM, Albe Laurenz [EMAIL PROTECTED] wrote: The LDAP case is the main motivation why I would like to have wildcards, so that all our databases could be handled with one entry in the service file. Currently we have to add an entry to the file for every new database we want to access. I am very much +1 for it. I think it would be very useful. Do you think that the idea of wildcards for the service file is a bad one in general? Or could there be a more generally useful realization of that concept? The use of [%] in pg_service.conf is well... ugly. :) (At the same time I would have come up with exactly the same idea for the syntax...) As for LDAP string expansion I think it would be convenient to provide a subset of log_line_prefix %x expansions, like: %u User name %d Database name %r Remote host name or IP address, and remote port %h Remote host name or IP address %% Literal % ...I am not sure if %r or %h is a good idea. Thoughts? As for the syntax of configuration file, using [%] implies that things like [proj%db] would also be valid, which would be harder to implement. Perhaps empty bracers [] would be better? Ugly aswell, but would not suggest you can use [pr%db%test] and expect it to work. And could be made as 'last match' regardless of the in-file order. Regards, Dawid ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Hello. I am currently playing with UUID data type and try to use it to store provided by third party (Hewlett-Packard) application. The problem is they format UUIDs as -------, so I have to replace(text,'-','')::uuid for this kind of data. Nooow, the case is quite simple and it might be that there are other applications formatting UUIDs too liberally. I am working on a patch to support this format (yes, it is a simple modification). And in the meanwhile I would like to ask you what do you think about it? Cons: Such format is not standard. Pros: This will help UUID data type adoption. [1] While good applications format their data well, there are others which don't follow standards. Also I think it is easier for a human being to enter UUID as 8 times 4 digits. Your thoughts? Should I submit a patch? Regards, Dawid [1]: My first thought when I received the error message was hey! this is not an UUID, it is too long/too short!, only later did I check that they just don't format it too well. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Permanent settings
On Feb 19, 2008 10:31 PM, Josh Berkus [EMAIL PROTECTED] wrote: Magnus, All, This is something I've been thinking about too, just because my efforts to write auto-config scripts have gotten bogged down in the need to parse and write .conf files in a paltform-agnostic way and preserve comments. I agree with Magnus that it's something we need to address. Having the ability to update .conf through an api other than reading writing a file one line will make developing future autotuning tools significanly easier. I think that the idea of just appending extra lines to the bottom of the file in chronoligical (or random) order is so messy and hackish that it's simply not worthy of consideration for the PostgreSQL project. I don't like it either. I think there is a place of chronological list of changes made to the configuration -- it is the log file. When configuration is changed remotely it must be logged, and an extra comment message might be nice. Instead, here's my proposal: 1) add to the top of postgresql.conf another file switch, like this: # auto_config_file = 'ConfigDir/postgresql.auto.conf' # if set, the auto config file will be read by the system and override the settings in the rest of this postgresql.conf file, which will be ignored. # to disable automated and SQL command-line-based configuration # comment the above or set it to an empty string 2) split the category column in pg_settings into two columns, and add a categories lookup table, so it can be sorted properly 3) have command line config write to postgresql.auto.conf, dumping the whole of pg_settings organized with headings in categories order. I think an arrangement like that will work well with pg_settings based config, autotuning, while still allowing backwards-compatible manual control via postgresql.conf. I kind of like the idea of having two files -- one user-managed and one database-managed. But let me first write few issues of general matter. 1) changes that cannot be done to live server: SET PERMANENT shared_buffers = '1GB'; Now, this is a setting that cannot be changed live, but it should be changeable. And we need a command to query what's permanent and what's current. 2) '1GB' -- If we are modifying postgres.conf I _think_ the format should be preserved, so not changed into number of pages but written 'as-is'. 3) If we do have two configuration files (+1), I think PostgreSQL should issue a BIG FAT WARNING saying that its overriding user-managed postgres.conf wih postgres.auto, on a per-setting basis. This way nobody would be surprised why their setting is not working. And the rollback of all remote changes would be one unlink away. 4) Saving actual file. Sometimes it could be nice to be able set work_mem globally (as if by postgres.conf) but not permanent (so you don't see these settings on next start), though I am not convinced the feature is worth the risks of people mixing up things. 5) if we have a file that is 100% PostgreSQL controlled, we could some day use it as an alternative to pg_hba.conf and pg_ident.conf. Regards, Dawid ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] ANALYZE to be ignored by VACUUM
On Feb 19, 2008 8:31 AM, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: In my workload, ANALYZE takes long time (1min at statistics_target = 10, and 5min at 100), but the updated table needs to be vacuumed every 30 seconds because seqscans run on the table repeatedly. There is something *seriously* wrong with that. If vacuum can complete in under 30 seconds, how can analyze take a minute? (I'm also wondering whether you'll still need such frantic vacuuming with HOT...) There are two tables here: [S] A small table, that is frequently updated and seqscan-ed [L] A large table, that takes a long time to be analyzed The table [S] should be vacuumed every 30 seconds, because dead tuples affects the performance of seqscan seriously. HOT and autovacuum are very useful here *unless* long transactions begins. Analyzing [L] takes 1-5 minutes and both HOT and vacuums don't work during it. I want to use statistics_target = 100 at heart for more accurate statistics, but I'm using 10 instead because of avoiding long transactions by analyze. I am sure the idea is not original, yet still I would like to know how hard would it be to support local (per table) oldest visible XIDs. I mean, when transaction start you need to keep all tuples with xmin = oldest_xid in all tables, because who knows what table will that transaction like to touch. But then again, there is relatively large list of cases when we don't need to hold vacuum on _all_ relations. These include: SELECTs in auto-commit mode -- provided the SELECT is not something fancy (not immutable PL-functions), we just need to keep a snapshot of affected tables. DMLs in auto-commit mode -- provided no PL-functions or triggers are in effect. WITH HOLD CURSORS. Yes, I know, WITH HOLD cursor on first COMMIT will create a copy of rows to be returned (which can take a looong time in some cases), but perhaps it could be possible to just lock the table from vacuuming and skip the temporary store. And lots of other, when done in auto-commit. Like ALTER TABLEs, CREATE TABLE AS SELECT, COPY, etc... I am sure that such an idea isn't original. What are the main obstacles in making it happen except timemoney? :) Regards, Dawid ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Permanent settings
On Wed, Feb 20, 2008 at 7:34 PM, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] writes: 2) allow *commenting* of pg_settings / SET PERMANENT. Thus: SET PERMANENT work_mem = '12mb' COMMENT '16mb too high; OOM'; Ugh :-( I think that putting this into SET is a pretty bad idea in any case. SET is, and always has been, a session-local operation. Providing a secondary option that transforms it into something completely different I think that's valid argument. We already have ALTER USER foo SET bar = baz, so why not something like: ALTER CLUSTER SET shared_buffers TO '2GB'; ...perhaps with some other word than CLUSTER? Regards, Dawid ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Permanent settings
On Thu, Feb 21, 2008 at 12:02 AM, Josh Berkus [EMAIL PROTECTED] wrote: All, I think we're failing to discuss the primary use-case for this, which is one reason why the solutions aren't obvious. And that use case is: multi-server management. ...and third-party management solutions. PostgreSQL is *easy* to manage on one server. For a single server, the existing text file editor GUIs are clunky but good enough. [...] I tried to ask myself -- what other similar systems do I know and what do they give me. Well, I know Oracle does have a concept of database managed configuration (a SPFILE), and it is preferred set up. If you are using SPFILE, you can issue: ALTER SYSTEM SET foo = 'bar' [ COMMENT = 'comment' ] SCOPE=SPFILE (or MEMORY or BOTH). ...SPFILE means changes take place upon next restart, MEMORY -- they are temporary (though global for the system). At any moment you can switch from one form to the other (CREATE PFILE FROM SPFILE) or vice versa. The idea is that human can edit PFILE, and that SPFILE is database-only (and database can store some extra hints there, if it wishes). OK, so what does it give Oracle? The management solutions use it a lot. You can easily change parameters from them. Combined with monitoring this gives full service solutions, say a PostgreSQL could diisplay a bgwriter statistics, suggest changes to the current settings, and a one click away solution to try them out. Would I like PostgreSQL to have such an option? Yes, having used it on Oracle, I think such an ability is nothing but beneficial (if done right). Regards, Dawid PS: And I think postgres.conf as it is today is one of the nicest application-provided configuration files. :) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] configurability of OOM killer
On Feb 7, 2008 11:59 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Thu, Feb 07, 2008 at 08:22:42PM +0100, Dawid Kuroczko wrote: Nw, I know work_mem is not total per process limit, but rather per sort/hash/etc operation. I know the scheme is a bit sketchy, but I think this would allow more memory-greedy operations to use memory, while taking in consideration that they are not the only ones out there. And that these settings would be more like hints than the actual limits. Given that we don't even control memory usage within a single process that accuratly, it seems a bit difficult to do it across the board. You just don't know when you start a query how much memory you're going to use... Of course. My idea does nothing to guarantee memory usage control. It is that backends a slightly more aware of their siblings when they allocate memory. There is nothing wrong with one backend taking 512MB of RAM for its use, when nobody else is needing it. There is something wrong with it taking 512MB of RAM when three others already did the same. Hmm, I guess it would be possible to emulate this with help of cron job which would examine current PostgreSQL's memory consumption, calculate the new suggested work_mem, write it into postgres.conf and reload the config file. Ugly at best (and calculating total memory used would be a pain), but could be used to test if this proposal has any merit at all. while we are at it -- one feature would be great for 8.4, an ability to shange shared buffers size on the fly. I expect it is not trivial, but would help fine-tuning running database. I think DBA would need to set maximum shared buffers size along the normal setting. Shared memory segments can't be resized... There's not even a kernel API to do it. That is true. However it is possible to allocate more than one shared memory segment. At simplest I would assume that DBA should specify minimum shared memory size (say, 1GB) and expected maximum (2GB). And that between minimum and maximum SHM should be allocated in reasonably sized chunks. Say 128MB chunks. So that DBA could resize shared buffers to 1.5GB, decide this was not a good idea after all and reduce it to 1280MB. From the allocation point of view it would be: 1) one big chunk of 1GB 2) one 128MB chunk 3) another 128MB chunk 4) 128MB chunk declared dead -- new pages are prohibited, old pages are there until every backend gets rid of them. 5) 128MB same as 4. I am not sure that chunk size should be constant -- but it should be something reasonably small IF we want to be able to deallocate them. Now, it would give DBA an ability to start with fail safe settings, and gradually increase share buffers without forcing a restart. And ability to (yes, it would be a slow process) rollback ;-) from overallocating memory. Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] configurability of OOM killer
On Feb 5, 2008 10:54 PM, Ron Mayer [EMAIL PROTECTED] wrote: Decibel! wrote: Yes, this problem goes way beyond OOM. Just try and configure work_memory aggressively on a server that might see 50 database connections, and do it in such a way that you won't swap. Good luck. That sounds like an even broader and more difficult problem than managing memory. If you have 50 connections that all want to perform large sorts, what do you want to have happen? a) they each do their sorts in parallel with small amounts of memory for each; probably all spilling to disk? b) they each get a big chunk of memory but some have to wait for each other? c) something else? Something else. :-) I think there could be some additional parameter which would control how much memory there is in total, say: process_work_mem = 128MB # Some other name needed... process_work_mem_percent = 20% # Yeah, defenately some other name... total_work_mem = 1024MB # how much there is for you in total. Your postgres spawns 50 processes which initially don't use much work_mem. They would all register their current work_mem usage, in shared memory. Each process, when it expects largish sort tries to determine how much memory there is for the taking, to calculate is own work_mem. work_mem should not exceed process_work_mem, and not exceed 20% of total available free mem. So, one backend needs to make a huge sort. Determines the limit for it is 128MB and allocates it. Another backend starts sorting. Deletermines the current free mem is about (1024-128)*20% =~ 179MB. Takes 128MB Some time passes, 700MB of total_work_mem is used, and another backend decides it needs much memory. It determines its current free mem to be not more than (1024-700) * 20% =~ 64MB, so it sets it work_mem to 64MB and sorts away. Nw, I know work_mem is not total per process limit, but rather per sort/hash/etc operation. I know the scheme is a bit sketchy, but I think this would allow more memory-greedy operations to use memory, while taking in consideration that they are not the only ones out there. And that these settings would be more like hints than the actual limits. while we are at it -- one feature would be great for 8.4, an ability to shange shared buffers size on the fly. I expect it is not trivial, but would help fine-tuning running database. I think DBA would need to set maximum shared buffers size along the normal setting. Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] LDAP service lookup
On 8/27/07, Albe Laurenz [EMAIL PROTECTED] wrote: Dawid Kuroczko wrote: Then again, apart from libpq I don't see it mentioned anywhere. [...] Looking at the 8.3devel documentation... I think it should be mentioned in 18. Server Configuration. probably somewhere in 18.3 Connections and Authentication, that there is a possibility of using Service names instead of traditional connect strings -- and a link pointing to libpq-ldap documentation. This would make people much less likely to miss this point, especially if they don't plan to code in libpq C library. :-) The server config options are not a good place. Which I do know, but it's just if it were there, I would have spotted it much earlier sort of argument. Incidentally, this is a question for people who have access to www access logs. Which sections of documentation have highest hit rate? My guess would be: Server Configuration and SQL Reference, but it would be interesting to see one. But it could be mentioned in the 'psql' man page, under 'Connection to a database': $ psql service=myservice sslmode=require I think it defenately should. If there are no objections, I'd create a documentation patch for this. And, while not belonging to PostgreSQL documentation, but defenately belonging iin techdocs, whould be a step-by-step guide of setting up pg_services in OID and other LDAP servers. Funny thing, I think I'll be looking at putting pg_services in OID as well. Hmm, a tutorial for configuring LDAP servers would be quite off topic. I think that the examples in Section 30.15 are sufficient for somebody who is familiar with LDAP. I have been playing with it for a few moments now. i think there should be mentioned in the documentation that pg_service.conf can also contain static service definitions, and it also would be valuable to add into pg_service.conf.sample an example ldap:// stanza, so if person opens the file, she will be enlightened. And a missing feature. Or rather treat it as feature request. :-) A wildcard entry. I would like to set my environment that, on each client I would put pg_service.conf having two and only two LDAP servers in it (second one for failover. I think the entry might look like: ### wildcard entry: [%] # or [*] ? ldap://ldap1.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s) ldap://ldap2.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=%s) Which, when given: psql service = foobarbaz ..would query ...?(cn=foobarbaz) Deploying a new database would be as simple as adding it into LDAP. Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Index Tuple Compression Approach?
On 8/14/07, Chris Browne [EMAIL PROTECTED] wrote: I recently had a chat with someone who was pretty intimate with Adabas for a number of years who's in the process of figuring things out about PostgreSQL. We poked at bits of the respective implementations, seeing some similarities and differences. He pointed out one aspect of index handling that could (in principle) be an interesting optimization. Evidently, in Adabas, index leaf nodes were not simply tuples, but lists where the index value would not be repeated. In PostgreSQL, if you have the index value 'abc', and there are 10 tuples with that value, then you'll have a page full of tuples of the following form: |abc|ptr[rec1]|abc|ptr[rec2]|abc|ptr[rec3]| ...and so forth... Now, the Adabas approach was rather different. It would only have the index value once, and then have the list of tuple pointers: |abc|ptr[rec1],ptr[rec2],ptr[rec3],...[ptr[rec10]| This could allow a fair bit of compression, for cases where the index value is not unique. Interesting. Some time ago I've played a little with quite a big table which constained path (file path) as a primary key. It did have sense to have a strucure (SELECTs were mostly ORDER BY path WHERE path '/foo' LIMIT n). The actual index was only a little bit smaller than the table (there were maybe 4 or 5 columns there). Some time ago I've had an idea that it might be possible to compress th index size, even if it is a unique index. Take the path example. My idea would be to to split indexed value to 8-byte chunks. For example: /var/lib/postgresql/8.2/main would be split into: /var/lib /postgre sql/8.2 -- these would be insertered into a tree as a scaffold, and only vacuum should remove them.. main -- this would be a leaf node. It could be repeated in non-unique indexes. [/etc/pas] -- scaffold-node |-swd-- leaf node [/etc/sha] |-dow [/var/lib] -- a problematic mixed scaffold/leaf node. [/postgre] |-sql |-sql/8.2 [sql/8.2/] |-main |-foobar The scaffold nodes would be there to guarantee that there is some place to attach leafs to. They should not be removed by DELETE (unless we are sure no other node depends on them). Advantages? The repeated values (as /var/lib/postgresql/8.2) are not repeated -- they are embedded into tree, as a scaffold, actual nodes that are significant (files, not directories, in my example) are put as actual leafs. I guess it would reduce large indexes size and at the same time it could remove limitation that B-tree index cannot index values larger than 1/3 of the database page. 8-byte chunks was given as an example here, perhaps larger value would be better. (Of course redesigning schema to put directories separate from files woul be useful, but it would not help with ORDER BY .. LIMIT queries -- they would have to be JOIN-ed and re-sorted in memory I'm afraid). Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Machine available for community use
On 7/31/07, Devrim GÜNDÜZ [EMAIL PROTECTED] wrote: Hi, On Mon, 2007-07-30 at 19:14 -0700, Joshua D. Drake wrote: and RHEL performed much better than CentOS. Not to be unkind, but I doubt that on an identical configuration. Since I don't have the permission to distribute the benchmark results, I will be happy to spend time for re-running these tests if someone provides me an identical machine. Each test took 1-2 days -- I will insist that CentOS performs poorer than RHEL. Would it be possibe to include Unbreakable Linux in such test? Out of curiosity of course. :-) Regards, Dawid ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Design: Escort info from WHERE clause to executor?
On 7/25/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Why? What are you trying to achieve?
I am implementing a technique that sorts a result set according to weight
annotations in the WHERE.
The query
SELECT * FROM cars
WHERE (cdChanger=1){2}
OR (mp3player=1){1}
would be sorted according to partial conditions that hold.
Cars that have both a CD changer AND a MP3 player get a weight of 3, i.e. (2+1).
Cars that only have a CD changer get a weight of 2.
Cars that only have a MP3 player get a weight of 1.
Hmm, any particular reason why not doing it this way: ?
SELECT * FROM cars
WHERE cdChanger=1 OR mp3player=1
ORDER BY CASE WHEN cdChanger=1 THEN 2 ELSE 0 END
+ CASE WHEN mp3player=1 THEN 1 ELSE 0 END DESC;
...perhaps wrapping the CASE into something like:
CREATE FUNCTION weight_if(boolean,int) RETURNS int AS $$ SELECT CASE
WHEN $1 THEN $2 ELSE 0 END $$ IMMUTABLE LANGUAGE SQL;
...and using it like:
SELECT * FROM cars
WHERE cdChanger=1 OR mp3player=1
ORDER BY weight_if(cdChanger=1,2) + weight_if(mp3player=1, 1) DESC;
Regards,
Dawid
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] Idea: Comments on system catalogs?
Hello. I think it could be a nice idea to put descriptions from http://www.postgresql.org/docs/8.2/static/catalogs.html into system catalogs itself. I.e., make a bunch of COMMENT ON COLUMN pg_class.relname IS 'Name of the table, index, view, etc.'; ... COMMENT ON COLUMN pg_class.relkind IS 'r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table'; and so on. I think it could be helpful, when you're writing your own selects on system catalogs. Perhaps it could be optional (as a contrib .sql file). If you like the idea, I could prepare a script which will convert documentation into .sql file with series of COMMENT ON .. IS ...; Regards, Dawid ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Stats not updated after rollback -- autovacuum confused.
Hello, I have a system where there are mostly COPYs, which insert data into a table. Ocasionally a COPY will fail (and thus, dead rows appear), but as far as I can tell ROLLBACK is not reflected anywhere in the pg_stats_user_tables. And since there are no rows n_tup_upd or n_tup_del, therefore autovacuum will not fire for that table. I see two possible solutions: 1) let rollback increment both n_tup_ins and n_tup_del (or maybe n_tup_upd, at least)? This would be a good safeguard, I guess. 2) ANALYZE is able to see wether table is accumulating dead rows. It might be a good idea to make ANALYZE able hint autovacuum that some tables need VACUUM (that they exceed limits set for autovacuum). The 2nd point could be a TODO item, perhaps? Something like: When ANALYZE runs, make it note removable dead rows and non-removable dead rows. If removable dead rows exceed some threshold, hint autovacuum at that table. Regards, Dawid ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Feature freeze progress report
On 4/28/07, Simon Riggs [EMAIL PROTECTED] wrote: I think the community has to come up with ideas on how to accomplish this. My thinking is to move to a two stage release process: Do one production release annually, and one dev release at the 6 month mid-point. That way each new release contains a manageable number of new features and we have a realistic chance of integrating them successfully. Support companies would then have the option to support both releases, or just the main production release. Leading edge users, of which we have many, would then benefit from more frequent additional features. This would mean we would have to have a very well tested upgrade path for odd releases (8.2 - 8.4). Also it probably would mean that analytical functions or recursive queries should be postponed until 8.5 (as they didn't end up inside 8.3, and 8.4 would be stable release). I think that with introducing stable/devel version we are risking that devel versions will be less used in production environments (meaning less testing) and as a result they can lengthen the development cycle. Currently every release is stable, therefore we don't accept experimental patches unless they are really good idea. Then there is beta sequence, and then a stable release. With introducing dev release, we give green light to more experimental patches, and then devote dev release as a ripening period for them (equivalent of current pre-releases, I imagine). And then we release stable relese (without experimental patches; experimental patches are postponed until devel release, and devel release twice the number of experimental patches). I think we should not go into stable/devel release cycle without carefully thinking if it will serve us good. I am afraid this will make many people stick with stable releases and will make upgrades harder (do you remember how hard it was to go from Linux 2.2 to 2.4, and from 2.4 to 2.6?). Regards, Dawid ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [RFC] CLUSTER VERBOSE
On 3/15/07, Grzegorz Jaskiewicz [EMAIL PROTECTED] wrote: I figure - I should start brand new thread for this one - so here you go. I am in a need for verbose CLUSTER. Ie. one that would give me feedback and progress. Because CLUSTER is divided into two major operations, (data reordering, index rebuild) - I see it this way: CLUSTER on I: index name T: table name, data reordering CLUSTER on I: index name T: table name, index rebuild and than: CLUSTER 10% CLUSTER 12% , etc Well, I'm afraid that would be inconsistent with other VERBOSE commands (VACUUM VERBOSE), which don't give a progress indication other than that of specific stage being finished. I think if you want to add VERBOSE to cluster, it should behave exactly like all other 'VERBOSE' commands. And as for progress indication, there has been proposals for more or less similar feature, like: http://archives.postgresql.org/pgsql-hackers/2006-07/msg00719.php As I recall the ideas which caught most traction were indicating current progress via shared memory (pg_stat_activity) and a GUC variable which instructs the server to send notices indicating the progress status. The latter is harder. I'm afraid creating such a feature 'just for CLUSTER' is not the greatest idea -- there a lots of other places where having a progress bar would be a great benefit. REINDEX, most ALTER TABLEs, CREATE INDEX, even long running SELECTs, UPDATEs and DELETEs not to mention VACUUM would equally benefit from it. I think you will be having hard time trying to push CLUSTER-specific extension when there is a need for more generic one. Regards, Dawid ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] RFC: Temporal Extensions for PostgreSQL
On 2/17/07, Tom Lane [EMAIL PROTECTED] wrote: Hannu Krosing [EMAIL PROTECTED] writes: How easy/hard would it be to create unique indexes on tinterval (unique here meaning non-overlapping) ? Overlapping is not an equality relation (it fails the transitive law), so I'm not entirely sure what unique means in this context ... but I can promise you you can't make it work with btree. Hmm, let's assume two time intervals: A (with a0 as start and a1 as end times) B (woth b0 as start and b1 as end times) Now, we'd define operators as: A is left of B when a0 b0 AND a1 b0 A is right of B when a0 b1 AND a1 b1 A is equal to B if (a0 = b0 AND a0 = b1) OR (a1 = b0 AND a1 = b1) OR (a0 b0 AND a1 b1) Actually equal doesn't mean equal here, rather it says overlaps. Now, assuming UNIQUE INDEX on such table, the order would be preserved since no two intervals can overlap. And no overlapping data could be inserted without breaking ovelapivity. And of course non-unique index would produce garbage (since left of/right of wouldn't make any sense anymore). Interestingly, such non-overlapping datatypes could also make sense for network addresses (with netmasks). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch in core patch, for inclusion
On 1/24/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: contrib is a horrible misnomer. Can we maybe bite the bullet and call it something else? plugins? How about 'modules' or 'extras' or 'extensions'? :) standard-plugins might be more informative. I think of them as being like perl's standard modules, things that are part of the standard perl distribution as opposed to all the other stuff on CPAN. Personally, I don't quite like 'plugins'. it may be that when I think of plugins, I think of 'GIMP plugins'. ;) And I think hosting providers would exclude plugins almost as often as they do with contrib. They are not 'core' so it's safe to exclude them Same with 'extras' or 'extensions' -- they seem to imply that you can do without them. This is the reason I like 'modules' best. It makes one think that it is something maybe part of core, maybe not, but it has been isolated into separate entity for maintenance reasons. My EUR 0.02 Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] savepoint improvements
On 1/22/07, Tom Lane [EMAIL PROTECTED] wrote:
The short version is I would like the ability to run some sql commands
and recover the transaction if an error occurs.
I'm getting tired of repeating this, but: neither of you have said
anything that doesn't appear to me to be handled by ON_ERROR_ROLLBACK.
What exactly is lacking in that feature?
I think the problem is with doing something like this:
BEGIN;
INSERT INTO foo VALUES ('1'); UPDATE status SET updated=now() WHERE tab='foo';
INSERT INTO bar VALUES ('2'); UPDATE status SET updated=now() WHERE tab='bar';
INSERT INTO baz VALUES ('3'); UPDATE status SET updated=now() WHERE tab='baz';
COMMIT;
This will issue three savepoints (if I understand how things wok correctly),
one for each INSERT+UPDATE block. This way eiher both of them succeed
or fail, within one transaction.
Now, I think the problem the OP wanted to solve was that keeping command
on one line just to have them inside one savepoint, and depending on psql(1)
to issue rollbacks for us. I think OPs idea was to be able to rollback if error
occured:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
ROLLBACK TO s1 ON ERROR;
INSERT..
UPDATE...
ROLLBACK TO s2 ON ERROR;
UPDATE job SET ts = now(); -- OK
COMMIT; -- notice lack of rollback -- whole transaction will fail on error
One solution would be a psql command which
would fire given command on error condition, like:
BEGIN;
SAVEPOINT s1;
INSERT...
UPDATE...
\on_error ROLLBACK TO s1; INSERT INTO errors
SAVEPOINT s2;
COMMIT;
Regards,
Dawid
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] quick review
On 12/24/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Dec 18, 2006 at 03:47:42AM +0100, Molle Bestefich wrote: [...] Simply put, a tool with just a single button named recover all the data that you can is by far the best solution in so many cases. Minimal fuzz, minimal downtime, minimal money spent on recovery. And perhaps there's even a good chance that any missing data could be entered back into the system manually. I think the point which has been made here was that the recovery tool *is already there*: i.e. all what can be done as an one-click recovery is done by the system at start-up. Beyond this no cookbook exists (and thus no way to put it under an one-click procedure). So this one-click thing would be mainly something to cater for the needs of marketing. Well start-up recovery is great and reliable. The only problem is that it won't help if you have some obscure hardware problem, you really have a problem. If you want to sleep well, you should know what to do when disaster happens. I really like the approach of XFS filesystem, which ships with fsck.xfs which is essentially equivalent to /bin/true. They write in their white paper that they did so, because journaling should recover from all failures. Yet they also wrote that some time after they learned that hardware corruption is not as unlikely as one might assume, so they provide xfs_check an xfs_repair utilities. I think there should be a documented way to recover from obscure hardware failure, with even more detailed information how this could result only from using crappy hardware... And I don't think this should be one click process -- some people might miss real (software) corruption, and this is a biggest drawback. Perhaps the disaster recoverer should leave a detailed log which would be enough to detect software-corruption even after the recovery [and users should be advised to send them]. Regards, Dawid Kuroczko ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [DOCS] [HACKERS] Replication documentation addition
On 10/25/06, Bruce Momjian [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: Bruce Momjian wrote: Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: I think this is a good reason not to list *any* of the products by name in the documentation, but instead refer to a page on say techdocs that can be more easily updated. I agree with that. If we have statements about other projects in our docs, we will have a problem with not being able to update those statements in a timely fashion when the other projects change. I mention only Slony and pgpool as examples of replication types. They seem to have risen to high enough visiblity to do that. I have not mentioned any other solutions. What about Slony-II or pgpool2? Which are fundamentally different from their v1 counterparts (o.k. slony-ii isn't out yet but still). I +1 that we move to have all of the replication documentation pushed to techdocs or other facility and just have a link from the docs. What I did was to mention Slony and pgpool as examples, so people realize there are many other soluions. It would be good to have a companion web site that could list them all, both open source and commercial. That is going to take a lot more work, but I think would have great value, especially since our documentation will clearly outline the terms. What you don't want to do is to throw up a list and have people try to figure out what solutions they cover. I'm in quite an unique situation right now, working with a few DBAs who have deep knowledge but no PostgreSQL background, so I have a good view how PostgreSQL is perceived by people with fair knowledge of other databases. What I have noticed is a deep respect for community. If they ask about replication solution, and I tell about Slony, they ask if Slony is provided with the postgresql-contrib. Well... no, and it won't be. Then they look back, think a while and say somethig on the lines of: well, $SOME_OTHER _DATABASE was using external replication solutions so it is all right. But then, before I talked with them, they did some quick research on PostgreSQL and their perception was that there's no replication / replication is shady in PostgreSQL. It would be quite convenient to tell them: No replication? Did you actually read the manual? here goes URL Well, pointing them to slony page is a solution but of a lesser caliber (how should they know about Slony anyway? They are newbies). Pointing them at The Documentation is a Good Argument (and it may cause them to look for some other information, like SQL syntax or PostgreSQL-specific catalog views there, which is Good). Enough background. Bruce, I've read Your documentation and I was left a bit with a feeling that it's a bit too generic. It's almost as if it could be about just about any major database, not PostgreSQL specific. I feel that, when I'm reading PostgreSQL docs I would like to know how to set up multi-master replication with PostgreSQL not an explanation what a multi-master replication is. It's not about the actual documentation content, but rather on accents distribution. Now it is something like: These are the types of replication solutions possible, some of them can be done with PostgreSQL, I think it should be rather: With PostgreSQL and some third-party tools you can achieve such and such replication solutions, oh and by the way, research is done on such and such replication method, but it's not a production quality yet. And I try to think as my DBA-mates would do if they read the documentation, I'm not sure they would end up enlighted after reading the docs -- thay would probably say: hey, I knew that, it's well structured there, but I still don't know what should I use, or maybe where can I read something about this slony thing anyway?. It may be my closed thinking schema though. What I feel is that such outsider, after reading these docs should end with Aha! I should be using Slony for my purposes. Or pgpool, if it's what she needs. I believe Tom's remark that it does NOT belong in the PostgreSQL documentation is quite right (though I wish there IS some reference to external replication packages, mainly because over and over again I need to prove PostgreSQL CAN be replicated, and it's not uncommon). However I'm still unconvinced about TechDocs -- TechDocs are good but still they are a bit scattered and unorganised. I am a PostgreSQL enthusiast, but it took me a while to learn about them, and for newbies not biased towards PostgreSQL it may take even more time. If it is linked from within the documentation, random DBAs might read it, and I wish they do. Right now I am more and more biased towards an additional documentation book for PostgreSQL, something like DBA guide or handbook. In format similar to the PostgreSQL documentation, but inside oriented around configuring other tools around and together with PostgreSQL. I shall send here some drafts withing 10-days time to seed a
Re: [HACKERS] New CRC algorithm: Slicing by 8
On 10/23/06, Tom Lane [EMAIL PROTECTED] wrote: It's not so much that I don't trust Intel as that a CRC algorithm is exactly the sort of nice little self-contained thing that people love to try to patent these days. What I am really afraid of is that someone else has already invented this same method (or something close enough to it) and filed for a patent that Intel doesn't know about either. I'd be wondering about that no matter where the code had come from. Given the numbers I posted earlier today, the proposal is dead in the water anyway, quite aside from any legal considerations. The horror, the horror. I wonder if changing to Slicing by 8 would be so self contained, so that people from software-patent free world would be able to just patch their distribution if they will. Regards, Dawid ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] GROUP BY on a large table -- an idea
Recently I've been playing with quite a big table (over 50mln rows), and did some SELECT ... sum(...) WHERE ... GROUP BY ... queries. The usual plan for these is to sort the entries according to GROUP BY specification, then to run aggregates one by one. If the data to be sorted is large enough, PostgreSQL has no other option than to spill to disk, which well, Isn't the fastest... Then I thought, why not skip the sorting, and do something like this, say a table is: kind tetx, sumkind text, cnt int, size int foo, bar, 2, 10 blah, argh, 23, 3 foo, baz, 1, 20 blah, argh, 23, 3 and the query would be: SELECT kind,subkind,sum(cnt),sum(size) FROM x GROUP BY kind,subkind; Instead of sorting, we would create an empty temporary state variable tree, looked up foo, bar in that tree -- if not found, enter there a new initialized state variables for sum(cnt) and sum(size). looked up blah, argh -- create the state variables looked up foo, baz -- create the state variables looked up blah,argh -- update the state variables there. And finally dump the whole tree as results of our query: foo, bar, 2, 10 foo, baz, 1, 20 blah, argh, 46,6 Of course first thing you'll notice is that the looking up part will probably eat all benefits from not spilling, and if group by columns have large cardinality we'd have to spill anyway. But then I thought, maybe a hybrid approach could be benefitial, and its' the resason I'm sending this message. The hybrid approach means: sort as much as you can without spilling to disk, then aggregate and store aggregate state variables in safe place (like a tree above), get more tuples from the table, sort them, update aggregate state variables, lather, rince, repeat. This should avoid the need to spill to disk. The cost of such operation depends on cardinality of GROUP BY part (and their correlation, doh), so it might be wise to try this approach for promising data only. I have yet almost no knowledge od PostgreSQL's internals, but I think the idea is feasible therefore I post it here. If it's been proposed before, forgive me. Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Opinion wanted on UUID/GUID datatype output formats.
On 9/14/06, Gevik Babakhani [EMAIL PROTECTED] wrote: At this moment we (almost) have a uuid/guid datatype. As suggested in earlier discussion we provide a raw/plain output of the uuid type: devdb=# select * from tbluuid; pk| --+ 6b13c5a1afb4dcf5ce8f8b4656b6c93c | 01e40a79b55b6e226bffb577e960453d | (2 rows) Which is a Good Format. I was wondering if we want to have a formatting function to be able to provide other common formats of the uuid/guid? something like: select format_uuid(mypk,'format2') from tbluuid; and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c Ehm, I would strongly suggest rather something similar to to_char() family of date-and-other-stuff formatting function, in the above example: SELECT to_char(mypk,'----') FROM tbluuid; ...or maybe some shorter syntax, like '8N-4N-4N-4N-12N'). This way it gains both flexibility (ANY format user wants is possible, say using slashes as separator (great for hash-like filename generator) and readability (no need to look for 'formatN' definition). Regards, Dawid ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Table clustering idea
There is a well known command called CLUSTER which organizes tablein specified index's order. It has a drawback, that new tuples added arenot in this order. Last night I had idea which could be interesting, I hope. The idea is to make use of 'histogram_bounds' collected statistical data.Instead of inserting row into first suitable spot in a table, a table wouldbe divided into sections, one for each of histogram_bounds ranges. When inserting, the database would try to find most suitable sectionto insert (using the histogram_bounds), and if there were free spotsthere, would insert there. If not, it would either look for a tuple in nearby sections, or first suitable place.What would it do? It would try to keep table somewhat organized,keeping rows of similar values close together (within SET STATISTICSresolution, so a common scenario would be 50 or 100 sections). It would make it a bit hard for a table to shrink (since new rows wouldbe added throughout the table, not at the beginning).Other idea than using histogram_bounds would be using the positionof key inside the index to determine the ideal place of row inside the table and find the closest free spot there. This would be of coursemuch more precise and wouldn't rely on statistic. Regards, Dawid
Re: Porting MSSQL to PGSQL (Was: [HACKERS] [OT] MySQL is bad, but THIS bad?)
On 5/22/06, Martijn van Oosterhout kleptog@svana.org wrote:
On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
T-SQL has statement-level triggers, and they get used a lot (some big apps
ONLY put code in triggers). Statement-level triggers are very efficient for
maintaining aggregates; the closest PG has are rewrite rules.
Yeah, I wish PostgreSQL had them. I've got clients that could certainly
make use of them.
What are you referring to that is not supported currently?
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table FOR EACH STATEMENT
EXECUTE PROCEDURE funcname ( arguments )
Each programming language that supports triggers has its own method for making
the trigger input data available to the trigger function. This input
data includes the
type of trigger event (e.g., INSERT or UPDATE) as well as any
arguments that were
listed in CREATE TRIGGER. For a row-level trigger, the input data also
includes the
NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and
DELETE triggers. Statement-level triggers do not currently have any way to
^^
examine the individual row(s) modified by the statement.
^^
So, if user types:
DELETE FROM foo WHERE doh ='bar' and baf 5;
(resulting, say with 5000 deleted rows)
...you can either create on delete trigger row level, which will:
UPDATE foo_stat SET count = count -1 WHERE doh='bar';
...which will be fired 5000 times.
The idea is that you could write a statement level trigger
which will count deleted rows and issue
UPDATE foo_stat SET count=count-5000 WHERE doh='bar';
Regards,
Dawid
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [HACKERS] [OT] MySQL is bad, but THIS bad?
On 5/22/06, Mark Woodward [EMAIL PROTECTED] wrote: Except that apt doesn't work on all platforms. Though it would certainly make sense to look at lifting the framework for CPgAN from somewhere, rather than coding it ourselves. A CPgAN would be a great idea in theory, but I have reservations. As a software developer, I'm fine with pgfoundery, but as a DB admin, and one who deploys data centers from time to time, I'd like to see something closer to the contrib. If I could have any influence at all, I'd like to see contrib essentially go away in the main distribution and replaced or renamed extensions. Then, some advisory group blesses extensions, and those extensions get packaged into a PostgreSQL extensions pack. I, again as a DB admin, would have NO problem with PostgreSQL playing favorites and picking best of breed for these extensions. The problem with contrib is that no actively developed projects should be there. It is a feature, not a bug. If it is actively developed, it may be buggy. If it is proven over time, it can be safely used. Also, for a contrib it is inefficient to release a whole -contrib whenever a subproject releases new release. This forces -contrib to use stable-and-unchanging packages. This also makes it extremaly hard to put new or niche projects. New are risky, because they may need immediate bugfixes. Niche projects used by a minority of users bloat -contrib and force more frequent releases, both of which are well, not preferred. Of course -contrib is great, we all know it. I think a CPgAN would be a good testbed/incubator for new packages, some of which should eventually get into -contrib. Also, assuming there is a pginstall dbanme packagename interface, a -contrib package should register all its subpackages within that system. So, you install postgresql-contrib, and then you can type: pg_package install mydb index/ltree and later, provided you change your mind: pg_package remove mydb index/ltree (with -f option to insert CASCADE whenever possible ;)). This would be somewhat similar to current createlang(1) and friends. :) Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
On 5/20/06, Lukas Smith [EMAIL PROTECTED] wrote: The improvements to the installer are great, but there simply needs to be a packaged solution that adds more of the things people are very likely to use. From my understanding Bizgres goes in that direction? I just think that whatever highly packaged solution PostgreSQL picks, this should be the download that is pushed at conferences, in articles and books. People with a clue will still know where they can get the clean base. Hmm, a Comprehensive PostgreSQL Archive Network? ;) I mean, something like CPAN, CTAN or CRAN? :) I mean, the -contrib is great, but pushing other things there is a bit tricky (to say the least) from the maintenance point of view. (Every bugfix, a new release of -contrib, etc, etc...). Then again PGfoundry is great to keep development centered, but finding and building a new package is not really a one-liner, and if you're unlucky you might get alpha-quality code installed. :) I think a CPgAN-like solution would be the best. A uniform method of getting approved Pg extensions. It would simplify installing the extensions, and would encourage distributions to package such extensions. Somebody suggested apt-get install postgresql-contrib. Imagine: apt-get install postgresql-datatype-fqdn apt-get install postgresql-gist-ltree ...and so on. Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
On 5/9/06, PFC [EMAIL PROTECTED] wrote:
You might consider just selecting your primary key or a set of
primary keys to involved relations in your search query. If you
currently use select * this can make your result set very large.
Copying all the result set to the temp. costs you additional IO
that you propably dont need.
It is a bit of a catch : I need this information, because the purpose of
the query is to retrieve these objects. I can first store the ids, then
retrieve the objects, but it's one more query.
Also you might try:
SELECT * FROM somewhere JOIN result USING (id)
Instead of:
SELECT * FROM somewhere WHERE id IN (SELECT id FROM result)
Yes you're right in this case ; however the query to retrieve the owners
needs to eliminate duplicates, which IN() does.
Well, you can either
SELECT * FROM somewhere JOIN (SELECT id FROM result GROUP BY id) AS
a USING (id);
or even, for large number of ids:
CREATE TEMPORARY TABLE result_ids AS SELECT id FROM RESULT GROUP BY id;
SELECT * FROM somewhere JOIN result_ids USING (id);
On the other hand if your search query runs in 10ms it seems to be fast
enough for you to run it multiple times. Theres propably no point in
optimizing anything in such case.
I don't think so :
- 10 ms is a mean time, sometimes it can take much more time, sometimes
it's faster.
- Repeating the query might yield different results if records were
added
or deleted in the meantime.
You may SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
though locking might bite you. :)
- Complex search queries have imprecise rowcount estimates ; hence the
joins that I would add to them will get suboptimal plans.
Using a temp table is really the cleanest solution now ; but it's too
slow so I reverted to generating big IN() clauses in the application.
A thought, haven't checked it though, but...
You might want to use PL to store values, say PLperl, or even C, say:
create or replace function perl_store(name text, val int) returns void
as $$ my $name = shift; push @{$foo{$name}}, shift; return $$ LANGUAGE
plperl;
select perl_store('someids', id) from something group by id;
(you may need to warp it inside count())
Then use it:
create or replace function perl_retr(name text) returns setof int as
$$ my $name = shift; return $foo{$name} $$ LANGUAGE plperl;
select * from someother join perl_retr('someids') AS a(id) using (id);
All is in the memory. Of course, you need to do some cleanup, test it,
etc, etc, etc. :)
Should work faster than a in-application solution :)
Regards,
Dawid
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Automatic free space map filling
On 5/1/06, Martijn van Oosterhout kleptog@svana.org wrote: On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote: ISTM that tying this directly to maintenance_work_mem is a bit confusing, since the idea is to keep vacuum transaction duration down so that it isn't causing dead tuples to build up itself. It seems like it would be better to have vacuum start a fresh transaction after a certain number of tuples have died. But since there's no way to actually measure that without having row level stats turned on, maybe number of transactions or length of time would be good surrogates.AIUI, vacuum starts a fresh cycle because it's accumulated a certainnumber of dead tuples to clean up. Isn't that what you're asking for?maintenance_work_mem is the limit on the amount of deleted tuple information that can be stored (amongst other things I'm sure)...Hmm, one idea, which may (or may not) be interesting for largetable vacuum is allowing a syntax similar to:VACUUM table WHERE some_col now()-'1 hour'::interval; I.e. Let vacuum run piggyback on some index. This would allowfor a quick vacuum of a fraction of a large table. Especially whenthe table is large, and only some data (new data) are being modified. The vacuum for such a table would:1. scan the index accoriding to the where criteria and create bitmap of blocks to look at.2. go through these blocks and vacuum them.Hmm, another perhaps silly idea -- a special index kind for tracking tuple deaths. Ie -- something like whenever tuple is updated/deleted,insert an entry into such index, using last session the tuple is visiblefor as a key. Then, perhaps, vacuum could scan such an index and find tuples which are candidates for removal. I lack the knowledge ofPostgreSQL's internals, so forgive me if I am writing somethingcompletely insane. :) Regards, Dawid
Re: [HACKERS] Call for 7.5 feature completion
On 8/26/05, Alvaro Herrera [EMAIL PROTECTED] wrote: Or, slightly different, what are people's most wanted features? One feature, or rather set of features which was missing from the list and I think it is important: i18n. :) I mean, PostgreSQL has a number of good features concerning internationalization, like UTF-8 support, transparent charset conversions, etc, but it also is area where new users are likely to get bit. One of the most gotcha-prone areas in PostgreSQL IMHO. If you stick with English, its OK. If you want different language, say Polish, German, whatever you'll probably careful enough to set a good locale. If you decide you want to make a hybrid Polish-German database -- you may run into problems, like indexes and ordering -- indexes are ordered using only one collation mechanism, so you should probably use C locale. If you're unlucky -- you have to recreate whole database. And then if you intend to use tsearch2, you have to set it up carefully for given needs. I'm not saying that mysqlish approach of setting collate per table would be a good solution. Frankly I don't think there is an ideal solution for this. Some time ago someone suggested using universal UTF-8 collation, which is good for most languages (and not for Turkish :)) -- I believe I've seen a patch for this on this list. Having some one size fits most solution could be helpful. Anyway, the i18n problem is a child-age illness, once you get over with it, you're most likely safe from it for the rest of your life. But some newbies may not get through it. ;) Regards, Dawid
Re: [HACKERS] psql SET/RESET/SHOW tab completion
On 13 Aug 2005 21:42:45 -0400, Greg Stark [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] writes: However, if you favor a no thought required approach, listing 'em all is certainly the path of least resistance. I'm just dubious that that maximizes the usefulness of tab completion. I'm not sure if you're interested, but my 2c speaking as a user would be for tab completion to include all variables. I often hit tab completion in new programs just to find out what's out there and would take something missing to be positive proof it didn't exist. Oh, I usually do the same thing. I guess my approach could summarized as: I assume tab-completion is not too smart -- it just completes one of valid values. And at the times where tab-completion is smart, it is smart and configurable -- as ZSH tab-completion. And were PostgreSQL's tab-completion go the smart way I would be for adding a GUC which allowed to fine-grain what it actually gives (all variables, settable variables, 'vacuum%' and 'enable%' variables, etc. ;))). Regards, Dawid ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Idea - optimising (left) joins?
Hello. I was just wondering, assume we have such tables: CREATE TABLE data ( foo text, somename_id integer not null references (somenames) ); CREATE TABLE somenames ( somename_id serial PRIMARY KEY somename text NOT NULL ); And a view: CREATE someview AS SELECT foo,somename FROM data NATURAL JOIN somenames; ...and a user does: SELECT foo FROM data order by foo LIMIT 1 OFFSET 1000; ...we could assume some of the things: 1. as somename_id references somenames, and it is joined with somenames, there will always be at least one row in somenames for each row of data. 2. as the somename_id is primary key, there will be exactly one row. 3. 1 and 2 together -- no matter if we join somenames or not, we'll get same number of rows 4. So if columns from somenames are not used for anything, we can skip them. No need to join at all. Other scenario: 1. someone_id is a simple integer, but the join is left join. The join is performed with somename_id in somenames (primary key), so the standard join would return 0 or 1 rows. left join returns 1 row for each row in data table. 2. If somenames columns are not used -- we can skip them. Why bother? There are cases where data is normalised and there is a view which joins main table with few tables similar to somenames mentioned here. If PostgreSQL could skip even looking at the tables that will not be used, it could give advantage; the bigger the data, the more advantage. What do you think about it? Is it worth the effort? Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] Planner doesn't look at LIMIT?
On 7/22/05, Tom Lane [EMAIL PROTECTED] wrote: This is quite strange. The nestloop plan definitely should be preferred in the context of the LIMIT, considering that it has far lower estimated cost. And it is preferred in simple tests for me. After a suitable period of contemplating my navel, I figured out what is going on here: the total costs involved are large enough that the still-fairly-high startup cost of the hash is disregarded by compare_fuzzy_path_costs(), and so the nestloop is discarded as not having any significant potential advantage in startup time. I think that this refutes the original scheme of using the same fuzz factor for both startup and total cost comparisons, and therefore propose the attached patch. Comments? Works great!!! With LIMIT below 4 000 000 rows (its 47-milion row table) it prefers nested loops, then it starts to introduce merge joins. Regards, Dawid ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Constraint Exclusion on all tables
On 7/21/05, Josh Berkus josh@agliodbs.com wrote: Also, it would be a good time to suggest some way for making EXPLAIN script-friendly. Like return data as a computer program convenient table? I'm already trying to kludge this in Perl. It would help automated testing considerably if explain analyze could be output as tab-delimited text instead of the human-readable version. Hmm, methinks it wouldn't be a very difficult beginner's project in PostgreSQL hacking, to add script-friendly format for EXPLAIN command. I am not sure if I'll make it, but I'm willing to try... If I'm jumping on a too big a beast, please tell me. :) Regards, Dawid ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Constraint Exclusion on all tables
On 7/20/05, Simon Riggs [EMAIL PROTECTED] wrote: Since it's also possible to do partitioning with UNION ALL, maybe it would be better if there was an option to explain that told it either to show or not show info about eliminated partitions. That would seem to serve the general case better than coding it according to table type. Can you think up the syntax, so we can comment on that proposal? hmm, maybe something like: EXPLAIN [ ANALYZE ] [ VERBOSE ] [ WITH EXCLUDED ] Where WITH EXCLUDED would mean to show tables eliminated? Also, it would be a good time to suggest some way for making EXPLAIN script-friendly. Like return data as a computer program convenient table? Regards, Dawid ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On 7/7/05, Bruce Momjian pgman@candle.pha.pa.us wrote: One idea would be to just tie its behavior directly to fsync and remove the option completely (that was the original TODO), or we can adjust it so it doesn't have the same risks as fsync, or the same lack of failure reporting as fsync. I wonder about one thing -- how much impact has the underlying filesystem? I mean, the problem with partial writes to pages is how to handle a situation when the machine looses power and we are not sure if the write was completed or not. But then again, imagine the data is on a filesystem with data journaling (like ext3 with data=journal). There, to my understanding, the data is first written into journal prior to be written to disk drive. Assuming the drive looses power during the process, I guess there would be two possible situations: 1) the modification was committed to journal completely, so we can replay the journal and we are sure the 8kb block is fine. (*) 2) the modification in the journal is not complete. It has not been fully committed to the filesystem journal. And we are safe to assume that drive has an old data. (*) I am not sure if it is true for 8kb-blocks, and of course, I haven't got good knowledge about ext3's journalling and its atomicity... Assuming above are true, it would be interesting to see how ext3 with data=journal and partial writes competes with ext3 data=someother without it. I don't have extensive knowledge with journalling internals, but I thought I would mention it, so people with wider knowledge could put their input here. Regards, Dawid ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
On 7/3/05, Andreas Pflug [EMAIL PROTECTED] wrote: Yup, attached. Per our earlier conversation, pg_dbfile_size() now returns the size of a table or index, and pg_relation_size() returns the total size of a relation and all associated indexes and toast tables etc. pg_relation_size's name is quite unfortunate, since the 8.0 contrib function does something different. And pg_dbfile_size sounds misleading, suggesting it takes a filename or relfilenode as parameter. Oh, I think pg_dbfile_size is best so far. Assuming someone gives it a filename, she'll get an error message. So practically it cannot be used wrong by mistake. It is not so with other names proposed for that function. Their names suggest they'll happily accept table/index/whatever and return some size... But what size, that is the question. At least pg_dbfile_size states that clearly. :) As for pg_relation_size. I think its good enough, or at least I don't know any better. I think it is better than pg_table_size, since people tend to have personalized ideas what a table size is (a table with TOAST and TOAST's indexes; a table with PRIMARY KEY,UNIQUE constraint indexes, a table with all indexes involved,. etc/). pg_relation_size seems. at least to me, to imply that its greedy and will take not only the table, and also things the table is closely related to, like all the indexes. The fun will begin when we'll have full working table partitioning and multitable indexes. ; Regards, Dawid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Dbsize backend integration
On 6/30/05, Dave Page dpage@vale-housing.co.uk wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 29 June 2005 12:46 To: Dave Page Cc: PostgreSQL-patches; PostgreSQL-development Subject: Re: [PATCHES] Dbsize backend integration I have a new idea --- pg_storage_size(). I'm not against that one, but I think Tom's point is vaild. I cannot think of anything better at the moment though (maybe pg_component_size, but that's equally random) :-( Anyone else? Please? Someone? Anyone? :-) pg_diskspace_size() pg_diskusage_size() pg_media_used_size() pg_allocated_size() pg_diskspace_used() Regards, Dawid PS: Yep, they aren't good... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [SQL] ENUM like data type
On 6/28/05, Martín Marqués martin@bugs.unl.edu.ar wrote: El Mar 28 Jun 2005 13:58, PFC escribió: Personnally I use one table which has columns (domain, name) and which stores all enum values for all different enums. I have then CHECK( is_in_domain( column, 'domain_name' )) which is a simple function which checks existence of the value in this domain (SELECT 1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance. You can also use integers. I personally think that the ENUM data type is for databases that are not well designed. So, if you see the need for ENUM, that means you need to re-think your data design. I seem to remember some discussion here, half a year ago perhaps which was about something similar (while not exactly). I mean it I think it someone said that DB2 (I am not sure about that one) has a feature that enables it to normalize the table behind the scenes. As I remember it, it works somewhere along the lines of: -- you create table CREATE TABLE foo ( when timestamptz, useragent some_data_type_perhaps ); ...and RDBMS will create a lookup table for useragents for you, with serial key, etc, etc. And in our foo table useragent will be kept as a reference to that lookup table. When you do a select, lookup table will be consulted behind the scenes, etc, etc. All this is doable with RULEs and VIEWs (and triggers for populating). Well, what MRB had in mind was more like a special subcase of such approach (lookup table with read-only keys), but I think such a lookup table would be benefitial for many users, especially when dealing with large tables. Incidentally, does it qualify for todo? Or maybe its already there? Regards, Dawid ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Tablespace-level Block Size Definitions
On 6/1/05, Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] wrote: You could create a separate bufferpool per page size. Of course that has other disadvantages. Is it really so difficult to create and attach another shmem segment ? Well, I don't think it is much different from having two database clusters, each with different block size. Hmm, perhaps it could be possible to make them all available through one virtual DB host/port using pg_pool even. :) It shouldn't be too difficult to create benchmarks testing performance of PostgreSQL under different block sizes, I guess. I wonder what perfromance win is possible... Regards, Dawid ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] NOLOGGING option, or ?
On 6/1/05, Hans-Jürgen Schönig [EMAIL PROTECTED] wrote: Personally I don't think that it is a good idea to do that. People will tend to corrupt their systems because they want speed (sometimes without thinking about the consequences). I can only think of one scenario where nologging would actually make sense: Many people use session tables to keep track of user level information on a website. corrupting a session table (usually not very large) would not cause a lot of problems. Well, from what I know, TEMPORARY tables are not WAL-logged, since they won't exist after restart (since none of the current sessions would exist). The problem with TEMPORARY tables is that they are not globally visible. I think it would be neat to be able to create server-life-time tables. I.e. table which is initially empty, can be used by all users, but is guaranteed to be empty (truncated) upon server restart. A place to store global temporary variables. A table type to put on a RAM-disk. Potential uses? Imagine a site which has a busy 'session' table and a relatively 'static' other tables. Most of WALs would consist on 'useless' updates to session table. And recovery using WAL files would take longer (as PostgreSQL would have to dump and restore whole a lot of session data). Having a global temporary table would be helpful in such a situation. And theoretically it wouldn't need to spill to disk at all, provided it was small enough. Regards, Dawid ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Views, views, views! (long)
On 5/6/05, Greg Sabino Mullane [EMAIL PROTECTED] wrote: As long as they are in a separate schema (like information_schema, but hopefully not as long). pg_views? pg_info? information_skema? :) But if you think that nobody needs these views, it's because you haven't had much contact with end users lately. Well, who really *does* need these? After all, end users should be using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's the job of the people writing those interfaces to know the system catalogs well and present them to the users in a pretty fashion. If people want an easy way to look up the information, they use an interface. If not, they should learn the system catalogs. /devilsadvocate We... Lets assume that young DBA needs to get a list of primary keys for each table. If she's smart she'll probably run psql -E and get queries like: SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '6338115' ORDER BY inhseqno ASC SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE a.attrelid = '6338117' AND a.attnum 0 AND NOT a.attisdropped AND a.attrelid = i.indexrelid ORDER BY a.attnum SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname, pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a WHERE i.indexrelid = c.oid AND c.oid = '6261315' AND c.relam = a.oid AND i.indrelid = c2.oid ...and so on. Then refashion them to do the needed query. Then again she may look inside information_schema.* (columns?), but it is not as natural as one would like. And then again, as most people are lazy, she would probably use: select schemaname,tablename,attname from pg_stats where n_distinct = -1 and schemaname='public'; Which is simply the stupidest way, and of course the wrong one. Yet it gives an illusion of returning quite right data the easy way... Sometimes it may be terribly tempting... I would certainly like to see these views in PostgreSQL. Maybe as a contrib package (just as there are tsearch2 or intarray). I think such views would not be of much use for, say pgAdmin. Yet again for querying from perl/php or over human carrier it would be benefitial, I guess. My 0.03 PLN. ;) Regards, Dawid ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Feature freeze date for 8.1
On 5/2/05, Heikki Linnakangas [EMAIL PROTECTED] wrote: What we can do in PostgreSQL is to introduce an application-level heartbeat. A simple Hello world message sent from server to client that the client would ignore would do the trick. Hmm, a quick-and-dirty implementation could be that a client issues LISTEN heartbeat; command, and there would be other client issuing NOTIFY heartbeat; every few minutes. I am not sure but this would probably make server send out these messages to the client, regardless of whether the client is doing something or not. Again, I am not sure. Ah, and probably so many NOTIFY messages wouldn't be very nice for system tables. Regards, Dawid ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump bug in 7.3.9 with sequences
On Wed, 2 Feb 2005 19:04:04 -0300, Alvaro Herrera
[EMAIL PROTECTED] wrote:
It is not pilot error if PostgreSQL allows it. There is
nothing illegal about the above commands in their execution.
The pg_dump application should recognize that the object has
changed and react accordingly.
ISTM this is a bug, but it's not clear to me what is the solution.
I can think of two:
1. Changing the default is forbidden
2. When the default is changed, the dependency on the sequence is
dropped, and the sequence itself is dropped.
In my humble opinion, the command:
ALTER TABLE ALTER COLUMN fooser SET DEFAULT nextval('aaa_seq');
should be equivalent to:
ALTER TABLE ALTER COLUMN fooser TYPE integer;
ALTER TABLE ALTER COLUMN fooser SET DEFAULT nextval('aaa_seq');
...ie, if we mess with 'default' it is no longer a serial, but a plain
integer with default sequence.
Regards,
Dawid
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Implementing Bitmap Indexes
On Sat, 29 Jan 2005 18:46:44 +, Mike Rylander [EMAIL PROTECTED] wrote: As a side note, wouldn't the in-memory bitmaps pretty much kill the need for multicolumn indexes? It seems that they would be able to join index scans on the same table, and then there would be no need for industrial strength cross-column correlation stats. The planner would be able to choose a multi index scan based on multiple single column stat entries and completely sidestep the need for precalculated cross-column correlations. Am I getting that right? I'm not too sure of that. Lets imagine big table with two columns, a and b. If we use multicolumn index (a,b), the search must go through a tree, find a value, and from there find b value. With in-memory bitmap, the search would start with index a, all matching rows would form the bitmap; then the second search would go through b index, forming another bitmap. Which then would be ANDed with previous bitmap. If I am correct, in case of in-memory bitmap PostgreSQL would have to read more index tuples (the less unique values, the more tuples to read) which in majority of cases would mean more work than multicolumn index. However in-memory bitmap would speed up many other cases (think: OR), but multicolumn indexes are there to stay. :) Regards, Dawid ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Implementing Bitmap Indexes
On Sat, 29 Jan 2005 21:54:39 +0200, Victor Yegorov [EMAIL PROTECTED] wrote: * Dawid Kuroczko [EMAIL PROTECTED] [29.01.2005 21:25]: With in-memory bitmap, the search would start with index a, all matching rows would form the bitmap; then the second search would go through b index, forming another bitmap. Which then would be ANDed with previous bitmap. Not only matching rows will form a bitmap, all rows should. And the physical order of rows in the table is important to form bitmap. My mistake -- when I said all matching rows would form the bitmap I meant all matching rows would form '1's in the bitmap. :) Gotta work on clarity of my messages. Regards, Dawid ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Locale agnostic unicode text
On Sat, 22 Jan 2005 17:09:42 -0500, Tom Lane [EMAIL PROTECTED] wrote: This time setlocale() was needed to get the behaviour I needed (database initdb'ed to 'C', my order set to 'pl_PL', or whatever locale I need at given moment). I would imagine that the performance is spectacularly awful :-(. Have you benchmarked it? A large sort on a unitext column, for instance, would be revealing. True. Yet it would be still better than nothing (C). Actually I was thinking that maybe functional indexes could be used to boost the speed (at least for ordering). ...but I would like to force ORDER BY using operators provided by me without this 'USING ' clause. Hmm, the existence of the default btree operator class should be sufficient. If You (or anyone) could try that SQL file and try to find missing clause... :) I guess that the case is that DOMAIN unitext is not quite another type, so text's default operators sometimes take precedence over unitext's own. :) CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$ utf8::decode($_[0]); return lc($_[0]); $$ LANGUAGE plperlu IMMUTABLE; AFAIK upper/lower cannot be considered to be locale-independent (see Turkish I/i business for a counterexample). I imagine it is not possible to make 'one size fits all' lower(), yet perl's uc()/lc() in my opinion for some cases is still better than choosing one locale or using C locale. Regards, Dawid ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] US Patents vs Non-US software ...
On Tue, 18 Jan 2005 23:05:57 +0100, Reinoud van Leeuwen [EMAIL PROTECTED] wrote: Contrary to popular misconception, virtually all countries grant software patents. The problem is that people have Thanks to the new European Union member Poland, the Dutch plan to put the software patents on the agenda 3 days before Christmas was revoked. So no software patents in Europe for now. (and the opposition against it seems to grow!) Since Poland's name has been called, Poland is a sample of a Eurpean country which does not grant software/algorithm/etc patents neither directly nor in form of 'technological method' (our patent office is well, very conservative institution :)). As for the EU voting, it was the first time I was really glad that Poland entered Union. Both ways. First that way that powers like USA cannot force their way with patents on Poland, second that Poland give positive input into EU. Ahhh, politics, enough of it. Let's end this thread. ;) Regards, Dawid ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Locale agnostic unicode text
Hello!
One of least liked by me features of PostgreSQL is a need to specify
LC_CTYPE an LC_COLLATE at initdb time. Especially if you intend to
put into DB texts in
different languages (say, Polish, French, German and Russian) and use
functions like lower() or ORDER BY these texts. :)
I guess the need to select these two locales at initdb time is to
avoid problems with corrupted indexes (column first indexed
with lower() function when setlocale('C'), then accessed when
setlocale('ru_RU')... oops. etc.). Probably there are more of
those. :)
To solve this I thought about creating function lower(text, locale), say:
lower ('Name', 'pl_PL.utf8');
Simple enough, I used plperl (plperlu actually) to make it happen and
while doing so I've noticed that perl does unicode-lowercasing/uppercasing on
its own accord, doesn't need locales to it, and does it pretty well.
So the resulting function is:
CREATE DOMAIN unitext text;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
And it seems to work fine regardless of locale set.
So... I thoght, why not use this unitext to sort texts?
So I've created functions, operators and operator class,
This time setlocale() was needed to get the behaviour
I needed (database initdb'ed to 'C', my order set to 'pl_PL',
or whatever locale I need at given moment).
I've attached a 'draft' of unitext,sql, which should create
unitext datatype capable of sorting according Polish locale.
It does not work as it should and I don't know how to make
it work. For example:
SELECT * FROM uni_tab ORDER BY uni_column;
...sorts according to 'C' (initdb's) locale.
I can force my way by issuing:
SELECT * FROM uni_tab ORDER BY uni_column USING ;
...but I would like to force ORDER BY using operators
provided by me without this 'USING ' clause.
Any hints how to do it?
Regards,
Dawid
PS: I like perl's lc() and uc() behaviour in utf8 mode. I'm thinking about
trying to port it from perl source as a C-language extension for PostgreSQL.
What do you think about it?
SET search_path = public;
CREATE DOMAIN unitext text;
BEGIN;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION upper(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return uc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION unitext_lt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] lt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_le(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] le $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_gt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] gt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ge(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ge $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_eq(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] eq $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ne(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ne $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_cmp(unitext,unitext) RETURNS integer AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = $_[0] cmp $_[1];
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
COMMIT;
CREATE OPERATOR (
LEFTARG = unitext,
RIGHTARG = unitext,
-- COMMUTATOR = ,
-- NEGATOR = =,
PROCEDURE = unitext_lt,
RESTRICT = scalarltsel,
JOIN =
Re: [HACKERS] MAX/MIN optimization via rewrite (plus query rewrites
On 15 Nov 2004 02:00:37 -0500, Greg Stark [EMAIL PROTECTED] wrote: I think people should get away from thinking about order by + limit. That isn't going to work for anything with a GROUP BY. And it isn't going to work for anything more complex than a single min() or max(). min() only needs the first record from whatever set of records it's operating on as long as they're provided in a specified order. This is just as true for a min() applied to only a single GROUP as it is for a min() applied to an entire table. But as far as I can tell there is no way of forcing such order, at least ORDER BY queries are doomed to fail: select max(val) from test_max order by val desc; ERROR: column test_max.val must appear in the GROUP BY clause or be used in an aggregate function Anyway I think that any optimization (supposedly imlicit order by when min() or max() is the only requested column) would at least stop people from using awkward syntax for performance reasons... Regards, Dawid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] '\0' characters in procedural languages.
Hello, recently I've been trying to write a plperlu function like this: CREATE FUNCTION foo RETURNS bytea AS ' use Storable qw(freeze thaw); my @a = (1,2,3,4,5); return freeze ([EMAIL PROTECTED]); ' LANGUAGE plperlu; In other words, serialize some data (maybe some rows, would be a great aggregate function :)) and store it in some table. And I also wrote similar function which thaws the data from bytea argument. PostgreSQL however seems to be doing two things: 1) when returning any data from function (including bytea return type), it copies it up to first '\0' character. Looking at the plperl.c sources, solution would be changing lines like this: result = FunctionCall3(prodesc-result_in_func, PointerGetDatum(SvPV(*svp, PL_na)), ObjectIdGetDatum(prodesc-result_typioparam), Int32GetDatum(-1)); into something like this: size_t ret_length; /* size_t? */ (...) result = FunctionCall3(prodesc-result_in_func, PointerGetDatum(SvPV(*svp, ret_length)), ObjectIdGetDatum(prodesc-result_typioparam), Int32GetDatum(-1)); In other words, use the fact that SvPV's second argument is used to pass string length ... but I don't know where to pass the returned length. I don't suppose (-1) is the right place... 2) When function receives bytea as an argument it converts it into \NNN-escaped string. I think it would be more natural to pass unescaped string to a perl function. Ah, and while we are at it -- I think it could be nice to embed Storable module (functions freeze, nfreeze and thaw) into plperl -- ability to pass raw serialized perl data between functions, and store it in tables could be quite useful. Regards, Dawid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
