Thread: 7.4 Wishlist
Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? My ones are: * Compliant ADD COLUMN * Integrated full text indexes * pg_dump dependency ordering What would you guys do? Even if it isn't feasible right now... Chris
Christopher Kings-Lynne kirjutas R, 29.11.2002 kell 23:51: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > > My ones are: > > * Compliant ADD COLUMN > * Integrated full text indexes > * pg_dump dependency ordering > > What would you guys do? Even if it isn't feasible right now... As I don't have a permanent job starting next year (my main employer went bust), I'm planning to do more on postgreSQL anyway (at least until I run out of money ;) I have done some (or sometimes a lot of) brain-twitching on items in the following list, but very little actual useful coding on most. My personal todo list is: "Application server support" ---------------------------- * better XML integration - XML(*) aggregate function returning XML representation of subquery - XML input/output to/from tables - XML searchable/indexable in fields) * Overhaul of OO features (moving closer to SQL99) - type/table inheritance, table inheritance would be done using SQL99's UNDER and would be single inheritance, stored in single logical table,possibly subdivided in physical tables reusing our current huge table 1GB split mechanisms type inheritance would be done using SQL99's LIKE and would be multiple inheritance and would reuse as much as possiblethe current code for ADD/DROP/RENAME column - check constraints would apply to both type and table inheritance - pk/fk constraints would apply only to table inheritance - types as base of tables, - study feasibility of reference types, - dynamic invocation of table function on queries over hierarchies * WITH (as part of query/view) * WITH RECURSIVE for recursive queries * better NOTIFY (with optional argument, using shared memory instead of tables) General stuff ------------- * making array types btree-indexable in a general way * study feasibility of using SQL99's ARRAY syntax for arrays Data warehousing ---------------- * bitmap indexes, - using bitmap "indexes" internally for star joins - real bitmap indexes - clustered multiple bitmap indexes especially clustering on group of bitmap indexes * clustering in general - specifying pages to be filled only to a certain percentage in clustered tables so that updatedtuples can be placed near original ones if needed and parallel vacuum can then reclaim the space and keep table clusteredwith less shuffling. * OLAP features - WINDOW clause, PARTITION BY - GROUPING SETS, ROLLUP, CUBE, () WAL-based master-slave replication ---------------------------------- * if someone is not doing it (which I hope is not true ;) UNICODE / Localization ---------------------- * UTEXT, UCHAR, UVARCHAR types using IBM's ICU, stored in UTF-16 or SCSU * fast LIKE, ILIKE, REGEX code for UTF-16, possibly lifted from python2 * field-level localization, again using ICU FE/BE protocol -------------- all can be worked on independently * try to find a better wire protocol from existing ones (X-window system seems simple enough, perhaps DB2's DRDA) or fixthe existing one for high performance (mainly make sure that as big chunks as possible have preceeding length), make iteasy to send out-of-band/optional data (Notifications, info on actual query performance (so one can visualize it for user), ...) * standardize a fire-level binary protocol for field types (currently whatever is stored is sent) * work on making python use this protocol and port some postgres datatypes (initially timestamp/date/time and varbit)to python Really Dark Arts ------------------ * making backend internals available to a scripting language (for me it means python ;) for making more parts (especiallyplanner/optimizer) more easily hackable * using the stuff from previous point ;) And that's all ;) ---------------- Hannu Krosing
pg_dump, our upgrade process is painful enough having to do a dump, reload. I think we should be able to guarantee (or at least let much closer to it) that the process works in all cases. Personally pg_upgrade would be even nicer. ----- Original Message ----- From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> To: <pgsql-hackers@postgresql.org> Sent: Friday, November 29, 2002 1:51 PM Subject: [HACKERS] 7.4 Wishlist > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > > My ones are: > > * Compliant ADD COLUMN > * Integrated full text indexes > * pg_dump dependency ordering > > What would you guys do? Even if it isn't feasible right now... > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Christopher Kings-Lynne wrote: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Replication. Well, jokes apart, I think this is one of the most needed features to me. Currently I'm using strange voodoo to replicate some tables on other machines in order to spread load and resilency. Compared to what I am doing now a good master to slave replication would be heaven. I understand that a good replication is painful but in my experience, if you start by integrating some rude, experimental implementation in the mainstream PostgreSQL the rest will come by itself. For example, RI was something I wouldn't consider "production level" in 7.2, but was a start, now in 7.3 is much much better, probably complete in the most important parts. Other wishes (not as important as the replication issue) are: - Better granularity of security and access control, like in mysql. - Ability to reset the state of an open backend, including aborting open transaction to allow for better connection pooling and reusing, maybe giving the client the ability to switch between users... Bye! -- Daniele Orlandi Planet Srl
Wow Hannu - your list puts mine to shame!
> "Application server support"
> ----------------------------
> * better XML integration
>
> - XML(*) aggregate function returning XML representation of subquery
>
> - XML input/output to/from tables
>
> - XML searchable/indexable in fields)
I've had thoughts about XML too. Since XML is hierachical, imagine being
able to index xml using contrib/ltree or something!
ie. We create a new 'xml' column type.
We create a new indexing scheme for it based on ltree & gist.
You index the xml column.
Then you can do sort of XPath queries:
SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
'Bob';
And it would be indexed. Imaging being able to pull up all XML documents
that had certain properties, etc.
MS-SQL has a SELECT ... FOR XML clause, but we could always just create
function called xml_select() or something now that we can return recordsets.
Chris
At 10:51 AM 29/11/2002 -0800, Christopher Kings-Lynne wrote: >* pg_dump dependency ordering I've actually started working on pg_dump in the background, but if you want to do it let me know. In terms of things I would like to see: - background/integrated vacuum (not just an overwriting storage manager) - insert/update...returning - function result caches -- assuming I can demonstrate that they are a Good Thing. - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful for making metadata changes then reloading data (Inserts are much slower). ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> I've actually started working on pg_dump in the background, but if you want > to do it let me know. Nah - you can do it! I don't have much time to code Postgres as it is. I'll stick to working on making ADD COLUMN compliant. > In terms of things I would like to see: > > - background/integrated vacuum (not just an overwriting storage manager) Work has started on this already, which is neat. > - insert/update...returning That would be so useful for our web app where we're always needing to get the primary key as opposed to oid. We keep needing to requery. > - function result caches -- assuming I can demonstrate that they are a Good > Thing. > > - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful for > making metadata changes then reloading data (Inserts are much slower). We do already have it in 7.3: http://developer.postgresql.org/docs/postgres/sql-copy.html Chris
At 04:56 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote: > > > > - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful >for > > making metadata changes then reloading data (Inserts are much slower). > >We do already have it in 7.3: Excellent. Then I just need to add support in pg_dump. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> > > - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful > >for > > > making metadata changes then reloading data (Inserts are much slower). > > > >We do already have it in 7.3: > > Excellent. Then I just need to add support in pg_dump. Hmmm. I could have sworn that someone (Neil?) already did that? Chris
Comment in {identifier} section in src/backend/parser/scan.l states: [...]
*Note: here we use a locale-dependent case conversion, * which seems appropriate under SQL99 rules, whereas
* the keyword comparison was NOT locale-dependent. */
And in ScanKeywordLookup() in src/backend/parser/keywords.c:
/* * Apply an ASCII-only downcasing. We must not use tolower()
since it * may produce the wrong translation in some locales (eg, Turkish), * and we don't trust
isupper()very much either. In an ASCII-based * encoding the tests against A and Z are sufficient, but we also
* check isupper() so that we will work correctly under EBCDIC. The * actual case conversion step should
workfor either ASCII or
EBCDIC. */
And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as
you
may know our "I" is not your "I":
pgsql=# create table a(x char(1)); CREATE TABLE pgsql=# grant SELECT ON a to PUBLIC; ERROR: user "public"
doesnot exist pgsql=#
Oracle, the second best database I have does seem to convert relation names
in
locale-dependent fassion:
SQL> alter session set NLS_LANGUAGE='TURKISH'; Session altered. SQL> create table a(x char(1)); Table created.
SQL>grant select on a to PUBLIC; Grant succeeded.
Further, if I try to create a table in oracle using Turkish-specific
characters,
it is creating it alright, without trying to make them upper-case as it
usually does.
So I have changed lower-case conversion code in scan.l to make it purely
ASCII-based
as in keywords.c. Mini-patch is given below. Please bear in mind that it is
my first
attempt at hacking PostgreSQL code, so there can be some mistakes.
Regards,
Nick
diff -Nur src/backend/parser/scan.l.orig src/backend/parser/scan.l
--- src/backend/parser/scan.l.orig Sat Nov 30 02:54:06 2002
+++ src/backend/parser/scan.l Sat Nov 30 02:57:45 2002
@@ -551,9 +551,12 @@ ident = pstrdup(yytext);
for (i = 0; ident[i]; i++) {
- if (isupper((unsigned char)
ident[i]))
- ident[i] =
tolower((unsigned char) ident[i]);
+ char ch =
ident[i];
+ if (ch >= 'A' && ch <= 'Z'
&& isupper((unsigned char) ch))
+ ch += 'a' - 'A';
+ ident[i] = ch; }
+ ident[i] = '\0'; if (i >= NAMEDATALEN)
{ int len;
At 05:33 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote: >Hmmm. I could have sworn that someone (Neil?) already did that? Not AFAICT - at least based on looking at the manual. I'll check the code. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> So I have changed lower-case conversion code in scan.l to make it purely
> ASCII-based.
> as in keywords.c. Mini-patch is given below.
Rather than offering a patch, you need to convince us why our reading of
the SQL standard is wrong. ("Oracle does it that way" is not an
argument that will carry a lot of weight.)
SQL99 states that identifier case conversions are done on the basis of
the Unicode upper/lower case equivalences, so it seems clear that they
intend more than ASCII-only conversion for identifiers. Locale-based
conversion might not be an exact implementation of the spec, but it's
surely closer than ASCII-only.
regards, tom lane
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? Well, nobody is paying me, but I want to - fix the btree problem leaking unused pages (I think I'm getting near, I just haven't had free time during the last month). This one is a must to me. - try different regexp algorithms, compare efficiency. Both Henry Spencer's new code for Tcl, and Baeza-Navarro shift-orapproach (can be much faster than "traditional" regex engines) (do people care for allowing "search with errors",similar to what agrep and nrgrep do?) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo" (Barón Vladimir Harkonnen)
By no means I would try to convince that your reading of
the SQL standards is wrong. What I am trying to tell is
that Turkish alphabet is broken beyond repair. And since
there is absolutely no way to change our alphabet, we
may can code a workaround in the code.
So i do not claim that your code is wrong. It is
behaviang according to specification. But unfortunately
folks at SQL99 probably were not aware of the woes
of Turkish "I".
The very special case of letter "I" in Turkish is not
only PostgreSQL's problem. Many java programs have
failed miserably trying to open files with "I"s in
pathnames.
So basically, there are two letters "I" in Trukish.
The wone is with dot on top and another is without.
The with dot on top walways has the dot and the one
without never has it. Simple. The problem is
with the standard Latin "I". So why small "i" does
have a dot and capital "I" does not?
Standard conversion is
Lower: "I" -> "y'" and "Y'" -> "i".
Upper: "y'" -> "I" and "i" -> "Y'".
(font may not be displayed correctly in your mail reader)
Historically programs that operate in Turkish locale have
chosen to hardcode the capitalisation of "i" in system
messages and identifier names like this:
Lower: "I" -> "i" and "Y'" -> "i".
Upper: "y'" -> "I" and "i" -> "I".
With this, no matter what kind of "I" you used in names,
it is always going to end up a valid ASCII character.
Would it be acceptable if I submit a path that applies this
special logic in src/backend/parser/scan.l if the locale is "tr_TR"?
Because for many folks setting locale to Turkish would
render their database unusable. For, god forbid, if your
sql has a column name written in capitlas including "I".
It is not working. So I deeply believe that PostgreSQL community
have to provide a workaround for this problem.
So what should I do?
Best regards,
Nick
Tom Lane wrote:
> "Nicolai Tufar" <ntufar@apb.com.tr> writes:
>
>>So I have changed lower-case conversion code in scan.l to make it purely
>>ASCII-based.
>>as in keywords.c. Mini-patch is given below.
>
>
> Rather than offering a patch, you need to convince us why our reading of
> the SQL standard is wrong. ("Oracle does it that way" is not an
> argument that will carry a lot of weight.)
>
> SQL99 states that identifier case conversions are done on the basis of
> the Unicode upper/lower case equivalences, so it seems clear that they
> intend more than ASCII-only conversion for identifiers. Locale-based
> conversion might not be an exact implementation of the spec, but it's
> surely closer than ASCII-only.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
On Fri, 2002-11-29 at 18:06, Daniele Orlandi wrote: > - Better granularity of security and access control, like in mysql. Can you be more specific on exactly what features you'd like to see? > - Ability to reset the state of an open backend, including aborting open > transaction to allow for better connection pooling and reusing IIRC, it's been suggested that we can implement this by passing back the transaction state as part of the FE/BE protocol -- if we're doing a protocol change for 7.4, this could be part of it. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On Fri, 2002-11-29 at 13:51, Christopher Kings-Lynne wrote: > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? Here's some of my current TODO list: * FOR EACH STATEMENT triggers (already done) * column lists for UPDATE triggers (will be done soon) * Improve the buffer manager's replacement algorithm (LRU-K, perhaps?) * Implement support for hugetlb pages on linux 2.5 * Finish off PITR (if no one else does ...) * Asynchronous notification improvements (optional message, store notifications in shared memory) * Rowtype assignment in PL/PgSQL Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
What I'd like to have in future versions of PostgreSQL: - replication, replication, ... (you have seen that before). i guess most people would like to see that. - a dblink like system for connecting to remote database systems (not just PostgreSQL???) something like CREATE REMOTE VIEW would be damn good. it would solve many problem whenit comes to migration - tablespaces (the directory based stuff which has been discussed on the list) - somebody has mentioned XML before. the XPath stuff would be really cool - PL/Sh should be in contrib. i know that the core team has decided not to put it in the core but contrib would be fine (I keep forgetting the URL of Peters website :( ...) - packages: is there a way to define a set of functions as a package so that they can be removed using just one DROP PACKAGE or so? would be nice for huge projects - urgent: being able to use PL/Perl in combination with SPI (There is a Pg-SPI but it is 0.01 - see http://search.cpan.org/author/APILOS/DBD-PgSPI-0.01/PgSPI.pm). a full and reliable implementation would be fine. - preforking for faster startup - declare MySQL as evil *g*. Thanks a lot :) Hans
Oops, there is something I have forgotten: - "nicing" backends: this would be nice for administration tasks - CREATE DATABASE ... WITH MAXSIZE (many providers would like to see that; quotas are painful in this case - especially when porting the database to a different or a second server) Hans
My list is; Point to Point and Broadcast replication ---------------------------------------- With point to point you specify multiple endpoints, with broadcast you can specify a subnet address and the updates are broadcast over that subnet. The difference being that point to point works well for cross network replication, or where you have a few replicants. I have multiple database servers which could have a deadicated class C network that they are all on, by broadcasting updates you can cutdown the amount of traffic on that net by a factor of n minus 1 (where n is the number of servers involved). Ability to use raw partitions ---------------------------- I've not seen an install of PostgreSQL yet that didn't put the database files onto a filesystem, so I'm assuming it's the only way of doing it. By using the filesystem the files are at the mercy of filesystem handler code as to where they end up on the disk, and thus the speed of access will always have some dependancy on the speed of the filesystem. With a raw partition it would be possible to use two devices (e.g. /dev/hde and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then ensure the WALs were located on one the disk with the entries running sequentally, and that the database files were located on the other disk in the most appropriate location (e.g. index data starting near the center of the disk, and user table data starting near the outside). Win32 Port ------------ I've explained the reasons before. Apart from that it's always useful to open PostgreSQL up to a larger audience. ----- Original Message ----- From: "Daniele Orlandi" <daniele@orlandi.com> To: <pgsql-hackers@postgresql.org> Sent: Friday, November 29, 2002 11:06 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist > Christopher Kings-Lynne wrote: > > Hi guys, > > > > Just out of interest, if someone was going to pay you to hack on Postgres > > for 6 months, what would you like to code for 7.4? > > Replication. Replication. Replication. Replication. Replication. > Replication. Replication. Replication. Replication. Replication. > Replication. Replication. Replication. Replication. Replication. > > Well, jokes apart, I think this is one of the most needed features to > me. Currently I'm using strange voodoo to replicate some tables on other > machines in order to spread load and resilency. Compared to what I am > doing now a good master to slave replication would be heaven. > > I understand that a good replication is painful but in my experience, if > you start by integrating some rude, experimental implementation in the > mainstream PostgreSQL the rest will come by itself. > > For example, RI was something I wouldn't consider "production level" in > 7.2, but was a start, now in 7.3 is much much better, probably complete > in the most important parts. > > Other wishes (not as important as the replication issue) are: > > - Better granularity of security and access control, like in mysql. > > - Ability to reset the state of an open backend, including aborting open > transaction to allow for better connection pooling and reusing, maybe > giving the client the ability to switch between users... > > Bye! > > -- > Daniele Orlandi > Planet Srl > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
> And that's all ;) > ---------------- > Hannu Krosing - and what will you do after January? ;-) Just kidding. I hope you have a big fat bank account if you want to finish all that! -- Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582 Kaki Data tshirts, merchandize Fax: 3816 2501 Howitzvej 75 Åben 12.00-18.00 Email: kar@kakidata.dk 2000 Frederiksberg Lørdag 12.00-16.00 Web: www.suse.dk
Hi, although I'm just a novice in this mailing list I'd like to give my contribution to the 7.4 wishlist. I'd like to add to the PostgreSQL code some new low-level, primitive fuctions in order to give native support to FP-based algorithms for rule mining (Frequent Pattern Growth and extensions such as CLOSET and so on). As a matter of fact, this is more than just a wish to me... this is the task I have to accomplish for my thesis (I'm going to degree in Informatics Engineering at the Politecnico di Torino, Italy on next July), and so I can assure you that this will be done (and working) by the end of June. Obviously, any kind of hint and suggestion by you guruz is welcome! :) Bye, alice ----- Original Message ----- From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> To: <pgsql-hackers@postgresql.org> Sent: Friday, November 29, 2002 7:51 PM Subject: [HACKERS] 7.4 Wishlist > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > > My ones are: > > * Compliant ADD COLUMN > * Integrated full text indexes > * pg_dump dependency ordering > > What would you guys do? Even if it isn't feasible right now... > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ______________________________________________________________________ Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità. http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/
On Friday 29 November 2002 06:51 pm, Christopher Kings-Lynne wrote: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > > My ones are: > > * Compliant ADD COLUMN > * Integrated full text indexes > * pg_dump dependency ordering > > What would you guys do? Even if it isn't feasible right now... > > Chris > My wishlist :- savepoint- cursor out of a transaction- distributed databases and replication (two phase commit)- only sharelock in foreign keys - prepare/execute on backend level- error in a statement break a statement, not complete transaction regards Haris Peco
On Sat, 2002-11-30 at 01:40, Nicolai Tufar wrote: > And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as > you > may know our "I" is not your "I": > > pgsql=# create table a(x char(1)); > CREATE TABLE > pgsql=# grant SELECT ON a to PUBLIC; > ERROR: user "public" does not exist > pgsql=# > > Oracle, the second best database I have does seem to convert relation names > in > locale-dependent fassion: > > SQL> alter session set NLS_LANGUAGE='TURKISH'; > Session altered. > SQL> create table a(x char(1)); > Table created. > SQL> grant select on a to PUBLIC; > Grant succeeded. could it just be that we store identifiers in lower case, whereas most others (including SQL spec IIRC)have them in upper case ? Could you try the grant in both databases also in lower case ? i.e.: grant select on a to public; ------------------ Hannu
On Sat, 2002-11-30 at 07:57, Nicolai Tufar wrote: > With this, no matter what kind of "I" you used in names, > it is always going to end up a valid ASCII character. > > Would it be acceptable if I submit a path that applies this > special logic in src/backend/parser/scan.l if the locale is "tr_TR"? > > Because for many folks setting locale to Turkish would > render their database unusable. For, god forbid, if your > sql has a column name written in capitlas including "I". > It is not working. So I deeply believe that PostgreSQL community > have to provide a workaround for this problem. > > So what should I do? In SQL in general and in postgreSQL in particular, you can always use quoted names and thus escape the stupidities of case conversion: grant SELECT ON "a" to "public"; should work everywhere (except Oracle and other DB's where it should begrant SELECT ON "A" to "PUBLIC"; ) I can't help you on Win32/VMS filenames ;) --------------- Hannu
Someone know any ODBC driver without problem with UPPER CASE ?
-- Saludos Horacio Miranda. hmiranda@yale.cl
------------------------------------------------------------
PostgreSQL. Because life's too short to learn Oracle. :) Billy O'Connor
IBM --> Immer Backup Machen
On Sat, 2002-11-30 at 05:55, Alvaro Herrera wrote: > On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: > > > Just out of interest, if someone was going to pay you to hack on Postgres > > for 6 months, what would you like to code for 7.4? > > Well, nobody is paying me, but I want to > > - fix the btree problem leaking unused pages (I think I'm getting near, > I just haven't had free time during the last month). This one is a > must to me. > > - try different regexp algorithms, compare efficiency. Both Henry > Spencer's new code for Tcl, and Baeza-Navarro shift-or approach (can > be much faster than "traditional" regex engines) Perhaps bigger effect could be possible if we could could make LIKE/REGEXP use indexes - perhaps some approach based on trigrams could be usable here ? > (do people care for allowing "search with errors", similar to what > agrep and nrgrep do?) Yes, especially if integrated with some full text index scheme. -- Hannu Krosing <hannu@tm.ee>
Nicolai Tufar <ntufar@apb.com.tr> writes:
> Historically programs that operate in Turkish locale have
> chosen to hardcode the capitalisation of "i" in system
> messages and identifier names like this:
> Lower: "I" -> "i" and "Y'" -> "i".
> Upper: "y'" -> "I" and "i" -> "I".
If that's the behavior you want, why don't you set up a variant locale
definition that does it that way? That would fix *all* your locale-
dependent programs, not just Postgres ...
> Would it be acceptable if I submit a path that applies this
> special logic in src/backend/parser/scan.l if the locale is "tr_TR"?
It really seems like an inappropriate wart to me :-(
> Because for many folks setting locale to Turkish would
> render their database unusable. For, god forbid, if your
> sql has a column name written in capitlas including "I".
> It is not working.
I am not seeing why this is any worse than the universal problems of
using upper-case letters without double-quoting 'em. If you
consistently spell the name the same way, you will not have a problem;
if you don't, you might have a problem, but why is it worse than
anyone else's?
regards, tom lane
Hans-J�rgen Sch�nig wrote: > What I'd like to have in future versions of PostgreSQL: > > - PL/Sh should be in contrib. i know that the core team has decided > not to put it in the core but contrib would be fine (I keep forgetting > the URL of Peters website :( ...) I like PL/Sh too, but too many people are concerned it isn't transaction-safe and has poor performance. I want it in /contrib, but Peter, the author, doesn't want it in there, so there isn't much we can do. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote: > Hans-Jürgen Schönig wrote: > > What I'd like to have in future versions of PostgreSQL: > > > > - PL/Sh should be in contrib. i know that the core team has decided > > not to put it in the core but contrib would be fine (I keep forgetting > > the URL of Peters website :( ...) You could put the URL in /contrib > I like PL/Sh too, but too many people are concerned it isn't > transaction-safe and has poor performance. I want it in /contrib, but > Peter, the author, doesn't want it in there, so there isn't much we can > do. perhaps the URL and a file WARNING.TXT ;) -- Hannu Krosing <hannu@tm.ee>
Hannu Krosing wrote: > On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote: > > Hans-J?rgen Sch?nig wrote: > > > What I'd like to have in future versions of PostgreSQL: > > > > > > - PL/Sh should be in contrib. i know that the core team has decided > > > not to put it in the core but contrib would be fine (I keep forgetting > > > the URL of Peters website :( ...) > > You could put the URL in /contrib The URL used to be main site under: http://www.us.postgresql.org/interfaces.html but I don't see it there anymore. In fact, that page needs updating because some projects have moved. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote: > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > What would you guys do? Even if it isn't feasible right now... Hmm, mine would probably be fixing foreign keys (along with making it work with inheritance and match partial) and check constraints with subselects.
Hi All, here is my wishlist /very short/ : * Oracle syntax support in OUTER JOIN Thanks, Gabor > Hans-Jürgen Schönig wrote: > > What I'd like to have in future versions of PostgreSQL: > > > > - PL/Sh should be in contrib. i know that the core team has decided > > not to put it in the core but contrib would be fine (I keep forgetting > > the URL of Peters website :( ...) > > I like PL/Sh too, but too many people are concerned it isn't > transaction-safe and has poor performance. I want it in /contrib, but > Peter, the author, doesn't want it in there, so there isn't much we can > do. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, > Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Me and Teodor hope to work on contrib/ltree to add support for sort of
xml. Any ideas are welcome !
Regards,
Oleg
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:
> Wow Hannu - your list puts mine to shame!
>
> > "Application server support"
> > ----------------------------
> > * better XML integration
> >
> > - XML(*) aggregate function returning XML representation of subquery
> >
> > - XML input/output to/from tables
> >
> > - XML searchable/indexable in fields)
>
> I've had thoughts about XML too. Since XML is hierachical, imagine being
> able to index xml using contrib/ltree or something!
>
> ie. We create a new 'xml' column type.
>
> We create a new indexing scheme for it based on ltree & gist.
>
> You index the xml column.
>
> Then you can do sort of XPath queries:
>
> SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
> 'Bob';
>
> And it would be indexed. Imaging being able to pull up all XML documents
> that had certain properties, etc.
>
> MS-SQL has a SELECT ... FOR XML clause, but we could always just create
> function called xml_select() or something now that we can return recordsets.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> * Compliant ADD COLUMN
I've missed the thread (if there was one), how is it non-compliant?
Thanks,
- Brandon
----------------------------------------------------------------------------c: 917-697-8665
h: 201-798-4983b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5
On Sat, 2002-11-30 at 15:06, bpalmer wrote: > > * Compliant ADD COLUMN > > I've missed the thread (if there was one), how is it non-compliant? ALTER TABLE .. ADD COLUMN colname integer DEFAULT 42 NOT NULL CHECK(colname <= 42) REFERENCES tab2 ON DELETE CASCADE; Can't do the above in a single statement. It takes five statements. It's something I'd like to see added as well. -- Rod Taylor <rbt@rbt.ca>
Can you see this tying in with my recent hack of contrib/ltree to work with a wider range of node names? On Sat, 30 Nov 2002, Oleg Bartunov wrote: > Me and Teodor hope to work on contrib/ltree to add support for sort of > xml. Any ideas are welcome !
----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Nicolai Tufar" <ntufar@apb.com.tr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, November 30, 2002 5:41 PM
Subject: Re: [HACKERS] Locale-dependent case conversion in {identifier}
[ ... ]
>
> could it just be that we store identifiers in lower case, whereas most
others
> (including SQL spec IIRC)have them in upper case ?
That seem to be the case. All the databases I used, automaticaly convert
identifiers to upper case.
And they all do it using ASCII-only conversion.
>
> Could you try the grant in both databases also in lower case ?
>
> i.e.:
>
> grant select on a to public;
The statement works in both databases. But the problem is that it was
pg_dumpall who created SQL statements with PUBLIC. Why
pg_dumpall does not enclose identifiers in quotes, like:
REVOKE ALL ON TABLE "tamara2" FROM "public"; insted of
REVOKE ALL ON TABLE tamara2 FROM PUBLIC;
as it does now.
I will make an attempt to modify pg_dump accordingly, and will send a
patch to the list.
Regards,
Nic
Christopher Kings-Lynne wrote: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? > > My ones are: > > * Compliant ADD COLUMN > * Integrated full text indexes > * pg_dump dependency ordering > > What would you guys do? Even if it isn't feasible right now... Well, I might as well join in the fun. Here's my personal TODO for 7.4 (with much wishing that I'll actually have the time to do most if not all of it ;-)) * continue to improve usability of bytea datatype - easier explicit casting between bytea and text * stored procedures (procedures not functions) - no return value, but allow projection of results similar to SHOW ALL (i.e. also similar to MSSQL and Sybase) - CREATE PROCEDURE sp_my_stored_proc() AS '...' LANGUAGE '...'; - CALL sp_my_stored_proc; * array related improvements (note: some of this may exist in contrib as I haven't looked too close yet, but I'm aimingfor these to be internal backend functions) - function to return users in a group as rows instead of as an array - generic table function to unspool an array into rows [and columns for 2 dim array] - split -- split string intoarray on delimiter - implode -- join array elements into a string using given string delimiter - array_contains --Return TRUE if a value exists in an array - array_search -- Searches the array for a given value and returns the correspondingkey if successful * PL/R - new PL interface to R (statistical analysis package based on the S language) * improvements to contrib/tablefunc - enhanced crosstab functionality - possibly enhanced connectby functionality (maynot be worth it if RECURSIVE JOIN functionality makes it into 7.4) * improvements to dblink - see details on other recently sent message (ability to connect to non-PostgreSQL databases) * revisit table function scan issues (i.e. tuplestore vs streaming vs portal based) Things not on my list, but that I'm keenly interested in (in no particular order): - native win32 port - two-phase commit - PITR - replication - recursive joins (CONNECT BY PRIOR ... START WITH ...) Joe
On Saturday, November 30, 2002, at 01:44 PM, Joe Conway wrote: > * continue to improve usability of bytea datatype > - easier explicit casting between bytea and text This wouldn't happen to include the idea of somehow eliminating the difference between how text strings are delimited and how bytea strings are delimited, would it? Best, David -- David Wheeler AIM: dwTheory david@wheeler.net ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: Theory@jabber.org
Cross-db queries. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
David Wheeler wrote: > On Saturday, November 30, 2002, at 01:44 PM, Joe Conway wrote: >> * continue to improve usability of bytea datatype >> - easier explicit casting between bytea and text > > This wouldn't happen to include the idea of somehow eliminating the > difference between how text strings are delimited and how bytea strings > are delimited, would it? Not quite sure what you mean by delimiter -- are you referring to double escaping vs single escaping? Joe
On Saturday, November 30, 2002, at 04:14 PM, Joe Conway wrote: > Not quite sure what you mean by delimiter -- are you referring to > double > escaping vs single escaping? Oh crap, yes, that's exactly what I meant. s/delimited/escaped/g; Sorry. :-) David -- David Wheeler AIM: dwTheory david@wheeler.net ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: Theory@jabber.org
> > My ones are: > > > > * Compliant ADD COLUMN > > * Integrated full text indexes > > * pg_dump dependency ordering > > > > What would you guys do? Even if it isn't feasible right now... Actually - I think I might add MODIFY COLUMN to that list. Just look at the list of poor buggers in the interactive docs who can't change their column types. Guess that means I'd need to bring in attlognum's tho. Chris
src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
It completly breaks dumps when run with Turksh locale setting. In my
opinion making it lower-case would do much good and no harm. A mini
patch is given below.
On the other hand, I was thinking about wrapping all the identifiers in
dump files in single quotes. It is done in "SET SESSION AUTHORIZATION"
clause. Is there a reason for not doing this with table and colum names?
Regards,
Nic
*** ./src/bin/pg_dump/pg_dump.c.orig Sun Dec 1 03:23:56 2002
--- ./src/bin/pg_dump/pg_dump.c Sun Dec 1 03:24:48 2002
***************
*** 4918,4924 **** * wire-in knowledge about the default public privileges for different * kinds of objects.
*/
! appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM PUBLIC;\n", type, name); /* Make a
workingcopy of acls so we can use strtok */
--- 4918,4924 ---- * wire-in knowledge about the default public privileges for different * kinds of objects.
*/
! appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM public;\n", type, name); /* Make a
workingcopy of acls so we can use strtok */
***************
*** 4980,4986 **** if (eqpos == tok) { /* Empty left-hand side
means"PUBLIC" */
! appendPQExpBuffer(sql, "PUBLIC;\n"); } else if (strncmp(tok,
"group", strlen("group ")) == 0) appendPQExpBuffer(sql, "GROUP %s;\n",
--- 4980,4986 ---- if (eqpos == tok) { /* Empty left-hand side
means"PUBLIC" */
! appendPQExpBuffer(sql, "public;\n"); } else if (strncmp(tok,
"group", strlen("group ")) == 0) appendPQExpBuffer(sql, "GROUP %s;\n",
> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name. > It completly breaks dumps when run with Turksh locale setting. In my > opinion making it lower-case would do much good and no harm. A mini > patch is given below. Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word fix it? > On the other hand, I was thinking about wrapping all the identifiers in > dump files in single quotes. It is done in "SET SESSION AUTHORIZATION" > clause. Is there a reason for not doing this with table and colum names? You can't put single quotes around table and column names. You need to use double quotes as they are identifiers rather than literals. Bear in mind that some improvements have been made in Postgres 7.3 with regards to quoting, so have you checked 7.3? Chris
----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Nicolai Tufar" <ntufar@apb.com.tr>; <pgsql-hackers@postgresql.org>
Sent: Sunday, December 01, 2002 4:05 AM
Subject: Re: [HACKERS] Hard-coded PUBLIC in pg_dump
>
> Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word
fix
> it?
No: apb=> GRANT SELECT ON TABLE maras2.esya TO "PUBLIC"; ERROR: user "PUBLIC" does not exist apb=> GRANT SELECT
ONTABLE maras2.esya TO 'PUBLIC'; ERROR: parser: parse error at or near "'PUBLIC'" at character 38 apb=> GRANT
SELECTON TABLE maras2.esya TO public; GRANT apb=>
The problem here is case conversion from capital "I" to lower-case "i".
In Turkish locale tolower('I') is not equal to 'i'. So, since "public" role
is lower-case internally, why would we not make it lower-case in dump file.
>
> You can't put single quotes around table and column names. You need to
use
> double quotes as they are identifiers rather than literals.
>
> Bear in mind that some improvements have been made in Postgres 7.3 with
> regards to quoting, so have you checked 7.3?
I stand corrected. It is indeed has to be double-quoted.
7.3 is quoting only SET SESSION AUTHORIZATION 'role' clause in my dump.
Possibly,
because it has been added recently. Old code does not quote anything.
>
> Chris
Regards,
Nic.
On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: > check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
David Wheeler wrote: > On Saturday, November 30, 2002, at 04:14 PM, Joe Conway wrote: >> Not quite sure what you mean by delimiter -- are you referring to double >> escaping vs single escaping? > > Oh crap, yes, that's exactly what I meant. > > s/delimited/escaped/g; > That is one thing I'd like to take a look at. I think the problem is that certain byte-sequence/multibyte-encoding combinations are illegal, so it's not as simple an issue as it might first appear. Joe
On Saturday, November 30, 2002, at 07:24 PM, Joe Conway wrote: > That is one thing I'd like to take a look at. I think the problem is > that certain byte-sequence/multibyte-encoding combinations are > illegal, so it's not as simple an issue as it might first appear. My understanding is that the nul character is legal in a byte sequence, but if it's not properly escaped, it'll be parsed as the end of the statement. Unfortunately, I think that it's a very tough problem to solve. David -- David Wheeler AIM: dwTheory david@wheeler.net ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: Theory@jabber.org
Nicolai Tufar <ntufar@apb.com.tr> writes:
> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
As it should. I think the real problem here is the hack in gram.y:
grantee: ColId { PrivGrantee *n = makeNode(PrivGrantee); /* This
hacklets us avoid reserving PUBLIC as a keyword*/ if (strcmp($1, "public") == 0)
n->username = NULL; else n->username = $1; n->groupname =
NULL; $$ = (Node *)n; }
If the parser treated PUBLIC as an actual keyword, you'd not be having
this problem, because keywords are case-folded on an ASCII-only basis
(which is consistent with the SQL99 spec, amazingly enough).
We put in the above hack after someone complained that PUBLIC didn't use
to be a reserved word ... but considering that SQL92 clearly lists it as
a reserved word, there's not a lot of ground for that complaint to stand
on.
I'd prefer shifting PUBLIC back to the true-keyword category over any
of the other workarounds you've suggested ...
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > ... but considering that SQL92 clearly lists it as > a reserved word, there's not a lot of ground for that complaint to stand > on. > > I'd prefer shifting PUBLIC back to the true-keyword category over any > of the other workarounds you've suggested ... It will work for me. But why not change PUBLIC in pg_dump output to lower-case as well? > > regards, tom lane Nic.
Joe Conway <mail@joeconway.com> writes:
> - possibly enhanced connectby functionality (may not be worth it if
> RECURSIVE JOIN functionality makes it into 7.4)
Several of my Red Hat cohorts are pretty interested in making the
RECURSIVE query stuff work for 7.4. (The fact that they're ex-DB2
folk might explain their preference for the SQL99 syntax, nee DB2
syntax, over Oracle's CONNECT BY ... but I'm with them ;-(. Oracle's
recursive-join syntax is nearly as bad as their outer-join syntax.)
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > Ohhh ... > > Nicolai, are you running with a client encoding different from server > encoding? Got it! Gentlemen, thank you very much for assistance. The body of evidence was slowly growing, then, finaly Tom Lan's message have enlightened me. It all started with initdb's warning that it can not set client's connection to 'LATIN5'. Okay, I said, maybe some system files are not installed. Then I tried to create a database with ENCODING='LATIN5'. I did not pay much attention either since my template1 and template0 are in LATIN5. Then on Tom's suggestion I tried to change client encoding: aaa=# \encoding LATIN5 aaa=# \encoding SQL_ASCII SQL_ASCII: invalid encoding name or conversion procedure not found aaa=# \encoding LATIN5 LATIN5: invalid encoding name or conversion procedure not found aaa=# \encoding SQL_ASCii aaa=# \encoding SQL_ASCII aaa=# \encoding LATiN5 aaa=# \encoding LATIN5 aaa=# So it all falls back to Turkish "I" problem I mentioned earlier. To perform COPY successfully I had to set client locale to 'LATiN5' (note quotes and lower-case "i"). Any other combinations result in segmentation fault. Now, would it be right to change locale name handling to use ASCII-only lower and upper-case conversions? Many thanks to Tom Lane and Joe Conway. I've got to get some sleep :-) Regards, Nic.
On 30 Nov 2002, Neil Conway wrote: > On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: > > check constraints with subselects. > > Have we decided how this would even work? Last I heard, Tom still had > some major reservations about the practicality of implementing these -- > for example, would you re-evaluate all constraints that SELECT from a > table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. I can't see any justification for doing it as insert/update on main table only since by my reading of the spec the constraint is logically checked at the end of each statement (or transaction) even if we would normally not do so in practice when we know the constraint shouldn't be violated. Of course this was in the general set of, if I had months and months and nothing else to do (like work) then I'd want to look at it because I think it'd be useful.
"Matthew T. O'Connor" <matthew@zeut.net> writes: > pg_dump, our upgrade process is painful enough having to do a dump, > reload. I think we should be able to guarantee (or at least let > much closer to it) that the process works in all cases. I would already be happy if pg_dump backed up my databases correctly, so that I dont have to reorder SQL statements manually in the dump before psql can execute it. -- Florian Weimer Weimer@CERT.Uni-Stuttgart.DE University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On 30 Nov 2002, Neil Conway wrote:
>> Have we decided how this would even work? Last I heard, Tom still had
>> some major reservations about the practicality of implementing these --
>> for example, would you re-evaluate all constraints that SELECT from a
>> table when the table changes?
> You'd have to either do it in all cases or come up with something that was
> smart enough to limit the cases to some extent based on the expression. I
> doubt that it'd perform terribly well, especially at first.
Note that you can get the "stupid" semantics (run the subselect only
when the constrained table changes) today: just hide the subselect in
a user-defined function that's called from the constraint expression.
Or put the whole check in a trigger instead of using a constraint.
I don't think we should bother with direct support of subselects in
constraints unless we can come up with an implementation that is
significantly better than what you can accomplish with these
workarounds.
regards, tom lane
"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> Ohhh ...
>>
>> Nicolai, are you running with a client encoding different from server
>> encoding?
> Got it!
Okay, the problem is a double free of memory in COPY OUT. I've applied
the following patch for 7.3.1. (A better fix would be for COPY OUT to
run in a local memory context that it could reset every so often, like
once per row, and then we could remove these risky retail pfree's entirely.
I plan to do that instead in development sources.)
regards, tom lane
*** src/backend/commands/copy.c.orig Fri Oct 18 20:25:36 2002
--- src/backend/commands/copy.c Sun Dec 1 12:28:24 2002
***************
*** 1470,1478 ****
char *string;
char c;
char delimc = delim[0];
-
bool same_encoding;
! char *string_start;
int mblen;
int i;
--- 1470,1477 ----
char *string;
char c;
char delimc = delim[0];
bool same_encoding;
! char *string_start = NULL;
int mblen;
int i;
***************
*** 1481,1492 ****
{
string = (char *) pg_server_to_client((unsigned char *) server_string,
strlen(server_string));
! string_start = string;
}
else
{
string = server_string;
- string_start = NULL;
}
for (; (c = *string) != '\0'; string += mblen)
--- 1480,1491 ----
{
string = (char *) pg_server_to_client((unsigned char *) server_string,
strlen(server_string));
! if (string != server_string)
! string_start = string;
}
else
{
string = server_string;
}
for (; (c = *string) != '\0'; string += mblen)
On Sun, 1 Dec 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On 30 Nov 2002, Neil Conway wrote: > >> Have we decided how this would even work? Last I heard, Tom still had > >> some major reservations about the practicality of implementing these -- > >> for example, would you re-evaluate all constraints that SELECT from a > >> table when the table changes? > > > You'd have to either do it in all cases or come up with something that was > > smart enough to limit the cases to some extent based on the expression. I > > doubt that it'd perform terribly well, especially at first. > > Note that you can get the "stupid" semantics (run the subselect only > when the constrained table changes) today: just hide the subselect in > a user-defined function that's called from the constraint expression. > Or put the whole check in a trigger instead of using a constraint. > > I don't think we should bother with direct support of subselects in > constraints unless we can come up with an implementation that is > significantly better than what you can accomplish with these > workarounds. Well, the problem is that user defined triggers trying to do the real semantics for update/insert on the "other" tables of the constraint seem to me like they'll have the same issues as foreign keys do currently, either you'll be forced to write something too strong and deadlock alot, or you'll write something too weak and end up with constraint violations with concurrent transactions unless you basically write a very low level C function to do it for you. I guess this, since in general, the non-action foreign keys really are just check constraints with a subselect effectively.
Tom Lane wrote: > If the parser treated PUBLIC as an actual keyword, you'd not be having > this problem, because keywords are case-folded on an ASCII-only basis > (which is consistent with the SQL99 spec, amazingly enough). > > We put in the above hack after someone complained that PUBLIC didn't use > to be a reserved word ... but considering that SQL92 clearly lists it as > a reserved word, there's not a lot of ground for that complaint to stand > on. > > I'd prefer shifting PUBLIC back to the true-keyword category over any > of the other workarounds you've suggested ... PUBLIC doesn't seem like a very common column name --- seems safe to make it reserved. We made 'value' reserved in 7.3, and that was a much more common one. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> PUBLIC doesn't seem like a very common column name --- seems safe to
> make it reserved. We made 'value' reserved in 7.3, and that was a much
> more common one.
I'm still quite unhappy about 'value', and would like to look into
making it unreserved again. This business does show that there are some
pitfalls in that, though :-(
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We made 'value' reserved in 7.3, and that was a much
> more common one.
BTW, you mean "current" not "7.3". That patch has still got some
serious problems anyway:
7.3:
regression=# select value;
ERROR: Attribute "value" not found
HEAD:
regression=# select value;
server closed the connection unexpectedly This probably means the server terminated abnormally before or
whileprocessing the request.
The connection to the server was lost. Attempting reset: Failed.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > We made 'value' reserved in 7.3, and that was a much > > more common one. > > BTW, you mean "current" not "7.3". That patch has still got some > serious problems anyway: Yes, I realized later it was current. I was fixing the dbdpg regression tests, and git bitten by that, and forgot I was using current and not 7.3. > > 7.3: > > regression=# select value; > ERROR: Attribute "value" not found > > HEAD: > > regression=# select value; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. Yow! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sunday, December 1, 2002, at 10:49 AM, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: >> PUBLIC doesn't seem like a very common column name --- seems safe to >> make it reserved. We made 'value' reserved in 7.3, and that was a >> much >> more common one. > > I'm still quite unhappy about 'value', and would like to look into > making it unreserved again. This business does show that there are > some > pitfalls in that, though :-( Actually, I don't think it's reserved in 7.3, only in the 7.4 development sources. Otherwise, Bricolage would fail hard, and it doesn't. So there's some time to play with this issue, I think. David -- David Wheeler AIM: dwTheory david@wheeler.net ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: Theory@jabber.org
> > regression=# select value; > > ERROR: Attribute "value" not found > > > > HEAD: > > > > regression=# select value; > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > Yow! I believe these are fixed in the patch I sent in last week. -- Rod Taylor <rbt@rbt.ca>
> > regression=# select value; > > ERROR: Attribute "value" not found > > > > HEAD: > > > > regression=# select value; > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > Yow! I believe these are fixed in the patch I sent in last week. -- Rod Taylor <rbt@rbt.ca>
David Wheeler wrote: > My understanding is that the nul character is legal in a byte sequence, > but if it's not properly escaped, it'll be parsed as the end of the > statement. Unfortunately, I think that it's a very tough problem to solve. No question wrt '\0' bytes -- they would have to be escaped when casting from bytea to text. The harder issue is that there are apparently many other multiple byte sequences that, while valid in an ASCII encoding, are not valid in one or more multibyte encodings. See this thread: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php This is why currently all "non printable characters" are escaped (which I think is all bytes > 127). Text on the other hand is already known to be valid for a particular encoding, so it doesn't need escaping. I'm not sure what happens when the backend encoding and client encoding don't match -- I'd guess there is some probability of invalid byte sequences in that case too. Joe
Christopher Kings-Lynne writes:> Just out of interest, if someone was going to pay you to hack on Postgres> for 6 months,what would you like to code for 7.4? Well judging by the hoards on Slashdot, it would appear that replication is the hot enhancement... Slashdot | PostgreSQL 7.3 Releasedhttp://developers.slashdot.org/article.pl?sid=02/11/30/1815200 Lee.
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? This is interesting discussion.. my wish: * error codes. It's very interesting that nobody other wants it... Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
<p>How about giving OLAP (Dimension / Measure) functionality to PG.<p><img height="28" src="http://sg.yimg.com/i/aa/icons/28/cricket.gif"width="28" /> Catch all the cricket action. Download <a href="http://in.sports.yahoo.com/cricket/tracker.html"target="_blank"> Yahoo! Score tracker</a>
Joe Conway wrote: > David Wheeler wrote: > > My understanding is that the nul character is legal in a byte sequence, > > but if it's not properly escaped, it'll be parsed as the end of the > > statement. Unfortunately, I think that it's a very tough problem to solve. > > No question wrt '\0' bytes -- they would have to be escaped when casting from > bytea to text. > > The harder issue is that there are apparently many other multiple byte > sequences that, while valid in an ASCII encoding, are not valid in one or more > multibyte encodings. See this thread: > > http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php > > This is why currently all "non printable characters" are escaped (which I > think is all bytes > 127). Text on the other hand is already known to be valid > for a particular encoding, so it doesn't need escaping. > > I'm not sure what happens when the backend encoding and client encoding don't > match -- I'd guess there is some probability of invalid byte sequences in that > case too. I think there is some idea of changing the frontend/backend protocol to prevent the need for escaping > \127 characters. I believe it is currently only required when the frontend/backend protocol have different encodings. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Mysql is planning on making this work:
SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
Do we have anything like it (After a discussion with Tom i figure no).
User variables is nice, especially in these kind of queries.
Nice would be to be able to use them as in C (almost everywhere):
SELECT id, @x FROM table_name t where (@x := date_part('days'. now() -
f.created)) > 100;
As Tom said in earlier mail, it might not be that big of a win in this
case, but if uses expensive functions, it will.
Magnus
> Mysql is planning on making this work: > > SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. > > Do we have anything like it (After a discussion with Tom i figure no). > User variables is nice, especially in these kind of queries. Well of course they have to make that work - they don't have subselects :P Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: >> Mysql is planning on making this work: >> >> SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY >> id. >> >> Do we have anything like it (After a discussion with Tom i figure >> no). User variables is nice, especially in these kind of queries. > > Well of course they have to make that work - they don't have > subselects :P > > Chris Yeah, but there is a point about running count(*) one time too many. Say if i would like to get a prettyprinting query like this: SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; That would be DAMN expensive doing with a subselect: SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM table_name; I know this example suck eggs, but you get the point where it hurts, right? Magnus - sorry for the dupe, chris
> Yeah, but there is a point about running count(*) one time too many. > Say if i would like to get a prettyprinting query like this: > > SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; > > That would be DAMN expensive doing with a subselect: > > SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM > table_name; > > I know this example suck eggs, but you get the point where it hurts, > right? Are you sure that postgres evaluates that subselect more than once? It looks to me like it returns a constant result for every row and hence it will be evaluated once per statement, not once per row. I'm no expert tho. Can someone answer this? And if the subselect changes for each row (ie. it's a correlated subquery) then you cannot use the variable anyway. It seems to me that if postgres doesn't consider count(*) as a constant then perhaps it should be taught to? Should be safe shouldn't it? I guess if a function in your select statemnt is inserting a row then there's trouble. But if there is, then the sum/count(*) is nonsensical anyway. Chris
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > >> Mysql is planning on making this work: > >> > >> SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY > >> id. > >> > >> Do we have anything like it (After a discussion with Tom i figure > >> no). User variables is nice, especially in these kind of queries. > > > > Well of course they have to make that work - they don't have > > subselects :P > > > > Chris > > Yeah, but there is a point about running count(*) one time too many. > Say if i would like to get a prettyprinting query like this: > > SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name; > > That would be DAMN expensive doing with a subselect: > > SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM > table_name; > > I know this example suck eggs, but you get the point where it hurts, > right? If you use a scalar subquery, yes, but I think a subselect in from would help, maybe something like (if you want the total count) select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select count(*) as count from table_name) as t2 group by table_name.id,t2.count; or (if you want each count the counter per group) either select id, sum(sum_col)||'/'||count(*) from table_name group by id; or select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select id, count(*) as count from table_name group by id) as t2 where table_name.id=t2.id group by table_name.id,t2.count;
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > Are you sure that postgres evaluates that subselect more than once? > It looks to me like it returns a constant result for every row and > hence it will be evaluated once per statement, not once per row. I'm > no expert tho. Can someone answer this? > > And if the subselect changes for each row (ie. it's a correlated > subquery) then you cannot use the variable anyway. > > It seems to me that if postgres doesn't consider count(*) as a > constant then perhaps it should be taught to? Should be safe > shouldn't it? I guess if a function in your select statemnt is > inserting a row then there's trouble. But if there is, then the > sum/count(*) is nonsensical anyway. > > Chris > It looks like it (7.2.x): # time psql genline -c "select id from xxxx" > /dev/null real 0m0.694s user 0m0.147s sys 0m0.025s # time psql genline -c "select id,id||'/'||(select count(*) from xxxx) as x from xxxx" > /dev/null real 0m2.202s user 0m0.263s sys 0m0.040s # time psql genline -c "select id,(select count(*) from bildsekvens) as x from xxxx" > /dev/null real 0m1.479s user 0m0.254s sys 0m0.047s They were taken from a busy system, but i ran the several times showing about the same result. Magnus
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > If you use a scalar subquery, yes, but I think a subselect in from > would help, maybe something like (if you want the total count) > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > (select count(*) as count from table_name) as t2 group by > table_name.id,t2.count; > > or (if you want each count the counter per group) either > > select id, sum(sum_col)||'/'||count(*) from table_name > group by id; > > or > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > (select id, count(*) as count from table_name group by id) as t2 where > table_name.id=t2.id group by table_name.id,t2.count; > Give it up already, i was MAKING A POINT, not trying to make an optimized count(*) thing :) There are other examples that you cannot get around, that will be evaluated more than once when a local "user variable" would make it not need to. Magnus
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > > > If you use a scalar subquery, yes, but I think a subselect in from > > would help, maybe something like (if you want the total count) > > > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > > (select count(*) as count from table_name) as t2 group by > > table_name.id,t2.count; > > > > or (if you want each count the counter per group) either > > > > select id, sum(sum_col)||'/'||count(*) from table_name > > group by id; > > > > or > > > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > > (select id, count(*) as count from table_name group by id) as t2 where > > table_name.id=t2.id group by table_name.id,t2.count; > > > > Give it up already, i was MAKING A POINT, not trying to make an > optimized count(*) thing :) > There are other examples that you cannot get around, that will be > evaluated more than once when a local "user variable" would make it not > need to. For most cases sticking an expression in a subselect in from works to not re-evaluate it (unless it's correlated in which case I don't think local variables help). It might not be as optimal in all cases, but probably is sufficient in most cases.
Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18: > It looks like it (7.2.x): > > # time psql genline -c "select id from xxxx" > /dev/null > real 0m0.694s > user 0m0.147s > sys 0m0.025s > # time psql genline -c "select id,id||'/'||(select count(*) from xxxx) > as x from xxxx" > /dev/null > > real 0m2.202s > user 0m0.263s > sys 0m0.040s > > # time psql genline -c "select id,(select count(*) from bildsekvens) as > x from xxxx" > /dev/null > > real 0m1.479s > user 0m0.254s > sys 0m0.047s what is the time for select id,x from xxxx, (select count(*) as x from bildsekvens) c ; > They were taken from a busy system, but i ran the several times showing > about the same result. > > Magnus > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Hannu Krosing <hannu@tm.ee>
Hannu Krosing <hannu@tm.ee> wrote: > Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18: >> It looks like it (7.2.x): >> >> # time psql genline -c "select id from xxxx" > /dev/null >> real 0m0.694s >> user 0m0.147s >> sys 0m0.025s >> # time psql genline -c "select id,id||'/'||(select count(*) from >> xxxx) as x from xxxx" > /dev/null >> >> real 0m2.202s >> user 0m0.263s >> sys 0m0.040s >> >> # time psql genline -c "select id,(select count(*) from bildsekvens) >> as x from xxxx" > /dev/null >> >> real 0m1.479s >> user 0m0.254s >> sys 0m0.047s > > what is the time for > > select id,x > from xxxx, > (select count(*) as x from bildsekvens) c ; > > > time psql genline -c "select id,x from xxxx, (select count(*) as x from xxxx) c ;" > /dev/null real 0m1.354s user 0m0.268s sys 0m0.028s The xxxx and the other table is the same table, sorry i screwed up in the last cut'n'paste operation. As i said it's a loaded system, the figures vary a little bit between runs. Magnus
I want to see: i) proper resource management a-la Oracle. This would allow a DBA to limited the amount of time any given user spends in the parser, planner or executor. It would be limited with a more sophisticated user system, including things like CREATE USER PROFILE ... ii) Auditing. Who accessed what, how, when and for how long with the ability to store queries, planner stats, executor stats and what ever else we can tie to a query. Very useful for debugging and security. You can get this from the logs but it is non trivial to perform adhoc reporting and statistical analysis. Why not store it.. in a database? :-) iii) SQL99 error code iv) Updatable and insertable-into views (per SQL99). Perhaps a two stage implementation: i) rules ii) have the planner/executor handle it, instead of the rewriter. The latter will take more coding, and might touch too much of the code, considering the other significant changes planned for 7.4. v) Better PL/PgSQL parser and memory handling vi) A larger number of case studies on the advocacy site, with a larger degree of financial and ROI analysis, all that jazz vii) Collections of information about migrating from other significant platforms: oracle, db2, sybase, interbase, SQL server -- a very popular request on irc.openprojects.net viii) General advocacy, particularly in pushing mainstream IT media coverage, conferences and university usage -- both for teaching SQL and for teach database engineering concepts for senior undergrads. I've no idea how much time I can put into these, but they're on my TODO list. Gavin
> i) proper resource management a-la Oracle. This would allow a DBA to > limited the amount of time any given user spends in the parser, planner or > executor. It would be limited with a more sophisticated user system, > including things like CREATE USER PROFILE ... Hehehe - yeah this would be neat. Would be somewhat better than MySQL's MAX_QUERIES_PER_HOUR setting :P > vii) Collections of information about migrating from other significant > platforms: oracle, db2, sybase, interbase, SQL server -- a very popular > request on irc.openprojects.net There's lots of good information on this on techdocs.postgresql.org. BTW, what happened to the PostgreSQL portal site that someone was working on? It'd be very interested in taking that on... > viii) General advocacy, particularly in pushing mainstream IT media > coverage, conferences and university usage -- both for teaching SQL and > for teach database engineering concepts for senior undergrads. Definitely. How about a resource for college/uni professors on how to use PostgreSQL in their courses? Chris
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: > > viii) General advocacy, particularly in pushing mainstream IT media > > coverage, conferences and university usage -- both for teaching SQL and > > for teach database engineering concepts for senior undergrads. > > Definitely. How about a resource for college/uni professors on how to use > PostgreSQL in their courses? I might get together with some of the lecturers I've worked with in Sydney to give such a document some weight. I must say, the problem is not a technical one though. I've given talks to 3rd and 4th year students about PostgreSQL -- technical, conceptual, political talks... you name it. Out of 200 odd students, only about 5-10 actually seem interested. Its terrible. Why aren't they interested? They think that if they study Oracle (instead) for 6 months they'll walk straight into a job with an extremely high salary. Its a myth, but I cannot shake that perception. In fact, things got very heated when two universities in Sydney moved their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will be down next year for the courses and Australian universities are heavily geared toward bums on seats not facilitation of education. Gavin
Gavin Sherry wrote: > I want to see: > > i) proper resource management a-la Oracle. This would allow a DBA to > limited the amount of time any given user spends in the parser, planner or > executor. It would be limited with a more sophisticated user system, > including things like CREATE USER PROFILE ... Amen: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3DB99C0A.70900%40mascari.com&rnum=1&prev=/groups%3Fq%3DCREATE%2BPROFILE%2BMike%2BMascari%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8 To avoid unnecessary cycles being spent on loading the profile after session authorization, we could have a GUC as was suggested to turn the feature on or off. This mirrors Oracle, where you have to set RESOURCE_LIMIT in your init[SID].ora file before PROFILEs are enforced. Some people like sticking everything in postgresql.conf though, including resource limits. I'm not sure how remote administration is supposed to work under such a scenario though... Mike Mascari mascarm@mascari.com
> I might get together with some of the lecturers I've worked with in > Sydney to give such a document some weight. I must say, the problem is not > a technical one though. I've given talks to 3rd and 4th year students > about PostgreSQL -- technical, conceptual, political talks... you name > it. Out of 200 odd students, only about 5-10 actually seem interested. Its > terrible. I've given a talk in the 2002 honours lecture series at UWA about Postgres and some of the things it can do. All of those guys were interested. Especially since the deptartment does a lot of work in genetic algoriithms. Tell me when you start working on a document - I'm happy to help. Since I'm only just out of Uni, I'd like to write a set of possible assignments and learning outcomes and how you can use postgres to support them. My girlfriend is a PhD student at UWA CS dept :) plus I won the honours scholarship there a year or two back, so I can get interest from the dept, including the databases lecturer. Might help for another point of view and feedback. > Why aren't they interested? They think that if they study Oracle > (instead) for 6 months they'll walk straight into a job with an extremely > high salary. Its a myth, but I cannot shake that perception. That's tragic. Teaching kids to admin oracle is something you do in Tafe, or an Oracle course, not a university. Anyway, what kind of course teaches you about how to admin oracle as opposed to teaching you about ACID properties, MVCC, distributed transactions and partitioning? Most of which can be demonstrated with Postgres. We learnt about relational model, algebra and calculus well before learning about SQL! Hell, my Uni (UWA) actually uses MS Access for crying out loud! We learn heaps of theory for 'real' databases (as above), but then our semester project is to implement in MS Access a bunch of tables and queries for a UN aid mission, for example. Not once do you have to use SQL - you just use the query builder. How lame! I have friends who have worked with people who've gone thru the oracle course. They say it's frustrating because they only understand what they've been told to understand and have a lack of knowledge about basic, database principles. > In fact, things got very heated when two universities in Sydney moved > their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will > be down next year for the courses and Australian universities are heavily > geared toward bums on seats not facilitation of education. Universities are supposed to have a tradition of open source support. Just imagine if the professors could not only teach about how to do SQL, but ALSO teach kids how a parser and executor and rewriter work by looking at the actual source code! Imagine those kids who go on to do honours, masters and PHD's in database theory, indexing or whatever who could end up contributing to Postgres? ;) What a sell! (For a real uni, that is ;) ) Chris
On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: > I've given a talk in the 2002 honours lecture series at UWA about Postgres > and some of the things it can do. All of those guys were interested. > Especially since the deptartment does a lot of work in genetic algoriithms. Excellent. Can you put that talk online somewhere? > Tell me when you start working on a document - I'm happy to help. Since I'm > only just out of Uni, I'd like to write a set of possible assignments and > learning outcomes and how you can use postgres to support them. > > My girlfriend is a PhD student at UWA CS dept :) plus I won the honours > scholarship there a year or two back, so I can get interest from the dept, > including the databases lecturer. Might help for another point of view and > feedback. Excellent. Are there any other people involved in PostgreSQL and universities or educational institutions? If so we could put something together about experiences for the advocacy Web site. > That's tragic. Teaching kids to admin oracle is something you do in Tafe, > or an Oracle course, not a university. Anyway, what kind of course teaches > you about how to admin oracle as opposed to teaching you about ACID > properties, MVCC, distributed transactions and partitioning? Most of which > can be demonstrated with Postgres. We learnt about relational model, > algebra and calculus well before learning about SQL! Your interest in this is clearly the same as mine: Universities (should) teach concept not product. I'm disgusted that this is not the case. If other people are interested we could work on this in January when I am over your way, as discussed in private email. Gavin
Gavin Sherry wrote: > On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: > > >>I've given a talk in the 2002 honours lecture series at UWA about Postgres >>and some of the things it can do. All of those guys were interested. >>Especially since the deptartment does a lot of work in genetic algoriithms. > > > Excellent. Can you put that talk online somewhere? > > >>Tell me when you start working on a document - I'm happy to help. Since I'm >>only just out of Uni, I'd like to write a set of possible assignments and >>learning outcomes and how you can use postgres to support them. >> >>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours >>scholarship there a year or two back, so I can get interest from the dept, >>including the databases lecturer. Might help for another point of view and >>feedback. > > > Excellent. Are there any other people involved in PostgreSQL and > universities or educational institutions? If so we could put something > together about experiences for the advocacy Web site. Is this the kind of thing that the Techdocs Guides area would be good for? (http://techdocs.postgresql.org/guides) :-) Regards and best wishes, Justin Clift <snip> > > Gavin > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Magnus Naeslund(f) wrote: > > select id, sum(sum_col)||'/'||count(*) from table_name > > group by id; > > > > or > > > > select table_name.id, sum(sum_col)||'/'||t2.count from table_name, > > (select id, count(*) as count from table_name group by id) as t2 where > > table_name.id=t2.id group by table_name.id,t2.count; > > > > Give it up already, i was MAKING A POINT, not trying to make an > optimized count(*) thing :) > There are other examples that you cannot get around, that will be > evaluated more than once when a local "user variable" would make it not > need to. Here's an even slimmer query that makes a var:test=> select var1.* from (select 1) as var1; ?column? ---------- 1(1row) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Magnus Naeslund(f) wrote:
>>> select id, sum(sum_col)||'/'||count(*) from table_name
>>> group by id;
>>>
>>> or
>>>
>>> select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
>>> (select id, count(*) as count from table_name group by id) as t2
>>> where table_name.id=t2.id group by table_name.id,t2.count;
>>>
>>
>> Give it up already, i was MAKING A POINT, not trying to make an
>> optimized count(*) thing :)
>> There are other examples that you cannot get around, that will be
>> evaluated more than once when a local "user variable" would make it
>> not need to.
>
> Here's an even slimmer query that makes a var:
>
> test=> select var1.* from (select 1) as var1;
> ?column?
> ----------
> 1
> (1 row)
Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:
select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) > 20;
I hope you all are kidding me in not seeing the real issue i'm trying to
show here.
Cheers
Magnus
> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
>
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
Yes, it's a bit of a bugger that one.
> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.
I see, yes. I guess the MySQL idea is to make it explicit to the parser
which parts of the expression are constant and equivalent.
Still, if the parser was smart enough, it could handle the above example
automatically. However, I think it would be an O(n^2) problem to solve -
matching all subexpressions against all other subexpressions to find the
ones that match...so the MySQL idea is a valid one.
One trick that many people don't know is that you can do it fast in the
GROUP BY clause, just not the WHERE clause:
select t.id, date_part('days',now()-t.stamp) from table_name t group by 2;
Which is something I just discovered recently.
Chris
Magnus Naeslund(f) wrote:
> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
>
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
>
> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.
>
> Cheers
> Magnus
>
>
>
Does this work?
SELECT t.id, x.date_part FROM table_name t, (select date_part('days',now()-t.stamp)) as xWHERE x.date_part > 20;
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
That's not a good idea as long as t.stamp varies from row to row. ;)
Perhaps once per row, maybe... :)
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
Potentially I think something like this would do it:
select t.id, t.foo from (select id, date_part('days', now()-stamp)
as foo from table_name except select null, null) as t where foo>20;
It's not really an optimization given the required except, but if there
was some way to tell the system not to push clauses down into a subselect
you wouldn't even need that.
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote: > >> Now convert this query so that it only evaluates the date_part thing >> ONCE: > > That's not a good idea as long as t.stamp varies from row to row. ;) > Perhaps once per row, maybe... :) > I give up
On Tue, 3 Dec 2002, Bruce Momjian wrote:
> Magnus Naeslund(f) wrote:
> > Good!
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
> >
> > I hope you all are kidding me in not seeing the real issue i'm trying to
> > show here.
>
> Does this work?
>
> SELECT t.id, x.date_part
> FROM table_name t, (select date_part('days',now()-t.stamp)) as x
> WHERE x.date_part > 20;
No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from. In
addition the optimizer is "smart" enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)
"Magnus Naeslund(f)" <mag@fbab.net> writes:
> Mysql is planning on making this work:
> SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
We're supposed to spend our time emulating nonstandard features that
don't even exist yet? I think I have better things to do ...
regards, tom lane
On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
>
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
Something like this could work:
select * from (select t.id, date_part('days',now()-t.stamp) AS d from table_name t) AS t1where t1.d > 20;
That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):
let t1 = select * from foo; t2 = select * from bar;
in select * from t1 natural join t2;
But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.
--
/Dennis
On Mon, Dec 02, 2002 at 12:48:38PM -0800, Christopher Kings-Lynne wrote: > But if there is, then the sum/count(*) is nonsensical anyway. You must to use it in SERIALIZABLE transaction isolation. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On Tue, 2002-12-03 at 09:20, Dennis Björklund wrote:
> On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
>
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
>
> Something like this could work:
>
> select *
> from (select t.id, date_part('days',now()-t.stamp) AS d
> from table_name t) AS t1
> where t1.d > 20;
>
> That aside I also would like some sort of local names. Something like the
> let construct used in many functional languages (not exaclty what you want
> above, but still):
>
> let t1 = select * from foo;
> t2 = select * from bar;
> in select * from t1 natural join t2;
>
> But even though I would like to give name to subexpressions like above, I
> still think postgresql should stick to standards as close as possible.
the standard way of doing it would be SQL99's WITH :
with t1 as (select * from foo) t2 as (select * from bar)
select * from t1 natural join t2;
you can even use preceeding queries
with t1 as (select a,b from foo) t1less as (select a,b from t1 where a < 0) t1zero as (select a,b from t1 where a
=0)
select * from t1zero, t1less, where t1zero.b = t1less.a;
Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)
I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.
OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)
I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree-->plan part ;).
I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.
--
Hannu Krosing <hannu@tm.ee>
I'm certainly interested! I am working here on Geographical Information Systems with PostgreSQL/PostGIS with the Minnesota MapServer, with a lot of regular database work thrown in. PostgreSQL has great potential for teaching databases and SQL, and when the native Windows port is ready, it will also be ideal for smaller, individual teaching projects (e.g. at home). Jan Hartmann Department of Geography University of Amsterdam jhart@frw.uva.nl Gavin Sherry wrote: > On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote: > > >>I've given a talk in the 2002 honours lecture series at UWA about Postgres >>and some of the things it can do. All of those guys were interested. >>Especially since the deptartment does a lot of work in genetic algoriithms. > > > Excellent. Can you put that talk online somewhere? > > >>Tell me when you start working on a document - I'm happy to help. Since I'm >>only just out of Uni, I'd like to write a set of possible assignments and >>learning outcomes and how you can use postgres to support them. >> >>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours >>scholarship there a year or two back, so I can get interest from the dept, >>including the databases lecturer. Might help for another point of view and >>feedback. > > > Excellent. Are there any other people involved in PostgreSQL and > universities or educational institutions? If so we could put something > together about experiences for the advocacy Web site. > > >>That's tragic. Teaching kids to admin oracle is something you do in Tafe, >>or an Oracle course, not a university. Anyway, what kind of course teaches >>you about how to admin oracle as opposed to teaching you about ACID >>properties, MVCC, distributed transactions and partitioning? Most of which >>can be demonstrated with Postgres. We learnt about relational model, >>algebra and calculus well before learning about SQL! > > > Your interest in this is clearly the same as mine: Universities > (should) teach concept not product. I'm disgusted that this is not the > case. > > If other people are interested we could work on this in January when I am > over your way, as discussed in private email. > > Gavin > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On 3 Dec 2002, Hannu Krosing wrote: > the standard way of doing it would be SQL99's WITH : Great! I havn't looked too much at sql99 yet so I've missed this. It's exactly what I want. Now I know what I will use in the future (when it's all implemented). -- /Dennis
Is WITH a TODO item?
---------------------------------------------------------------------------
Hannu Krosing wrote:
> On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> >
> > > Now convert this query so that it only evaluates the date_part thing
> > > ONCE:
> > >
> > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > date_part('days',now()-t.stamp) > 20;
> >
> > Something like this could work:
> >
> > select *
> > from (select t.id, date_part('days',now()-t.stamp) AS d
> > from table_name t) AS t1
> > where t1.d > 20;
> >
> > That aside I also would like some sort of local names. Something like the
> > let construct used in many functional languages (not exaclty what you want
> > above, but still):
> >
> > let t1 = select * from foo;
> > t2 = select * from bar;
> > in select * from t1 natural join t2;
> >
> > But even though I would like to give name to subexpressions like above, I
> > still think postgresql should stick to standards as close as possible.
>
> the standard way of doing it would be SQL99's WITH :
>
> with t1 as (select * from foo)
> t2 as (select * from bar)
> select * from t1 natural join t2;
>
> you can even use preceeding queries
>
> with t1 as (select a,b from foo)
> t1less as (select a,b from t1 where a < 0)
> t1zero as (select a,b from t1 where a = 0)
> select * from t1zero, t1less, where t1zero.b = t1less.a;
>
> Having working WITH clause is also a prerequisite to implementing SQL99
> recursive queries (where each query in WITH clause sees all other
> queries in the WITH clause)
>
> I sent a patch to this list recently that implements the above syntax,
> but I currently dont have knowledge (nor time to aquire it), so if
> someone else does not do it it will have to wait until January.
>
> OTOH, I think that turning my parsetree to a plan would be quite easy
> for someone familiar with turning parestrees into plans ;)
>
> I offer to check if it works in current (and make it work again if it
> does not) if someone would be willing to hold my hand in implementation
> parsetree-->plan part ;).
>
> I think that for non-recursive queries this is all that needs to be
> done, i.e. the plan would not care if the subqueries were from FROM,
> from WITH or from separately defined views.
>
> --
> Hannu Krosing <hannu@tm.ee>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> Is WITH a TODO item?
It is disguised as
Exotic Features
===============
* Add sql3 recursive unions
Which was added at my request in dark times, possibly when PostgreSQL
was called postgres95 ;)
This should be changed to two items
* Add SQL99 WITH clause to SELECT
* Add SQL99 WITH RECURSIVE to SELECT
> ---------------------------------------------------------------------------
>
> Hannu Krosing wrote:
> > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > >
> > > > Now convert this query so that it only evaluates the date_part thing
> > > > ONCE:
> > > >
> > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > date_part('days',now()-t.stamp) > 20;
> > >
> > > Something like this could work:
> > >
> > > select *
> > > from (select t.id, date_part('days',now()-t.stamp) AS d
> > > from table_name t) AS t1
> > > where t1.d > 20;
> > >
> > > That aside I also would like some sort of local names. Something like the
> > > let construct used in many functional languages (not exaclty what you want
> > > above, but still):
> > >
> > > let t1 = select * from foo;
> > > t2 = select * from bar;
> > > in select * from t1 natural join t2;
> > >
> > > But even though I would like to give name to subexpressions like above, I
> > > still think postgresql should stick to standards as close as possible.
> >
> > the standard way of doing it would be SQL99's WITH :
> >
> > with t1 as (select * from foo)
> > t2 as (select * from bar)
> > select * from t1 natural join t2;
> >
> > you can even use preceeding queries
> >
> > with t1 as (select a,b from foo)
> > t1less as (select a,b from t1 where a < 0)
> > t1zero as (select a,b from t1 where a = 0)
> > select * from t1zero, t1less, where t1zero.b = t1less.a;
> >
> > Having working WITH clause is also a prerequisite to implementing SQL99
> > recursive queries (where each query in WITH clause sees all other
> > queries in the WITH clause)
> >
> > I sent a patch to this list recently that implements the above syntax,
> > but I currently dont have knowledge (nor time to aquire it), so if
> > someone else does not do it it will have to wait until January.
> >
> > OTOH, I think that turning my parsetree to a plan would be quite easy
> > for someone familiar with turning parestrees into plans ;)
> >
> > I offer to check if it works in current (and make it work again if it
> > does not) if someone would be willing to hold my hand in implementation
> > parsetree-->plan part ;).
> >
> > I think that for non-recursive queries this is all that needs to be
> > done, i.e. the plan would not care if the subqueries were from FROM,
> > from WITH or from separately defined views.
> >
> > --
> > Hannu Krosing <hannu@tm.ee>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
--
Hannu Krosing <hannu@tm.ee>
TODO updated. Thanks for the clarification.
---------------------------------------------------------------------------
Hannu Krosing wrote:
> On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> > Is WITH a TODO item?
>
> It is disguised as
>
> Exotic Features
> ===============
>
> * Add sql3 recursive unions
>
> Which was added at my request in dark times, possibly when PostgreSQL
> was called postgres95 ;)
>
> This should be changed to two items
>
> * Add SQL99 WITH clause to SELECT
>
> * Add SQL99 WITH RECURSIVE to SELECT
>
>
> > ---------------------------------------------------------------------------
> >
> > Hannu Krosing wrote:
> > > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > >
> > > > > Now convert this query so that it only evaluates the date_part thing
> > > > > ONCE:
> > > > >
> > > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > > date_part('days',now()-t.stamp) > 20;
> > > >
> > > > Something like this could work:
> > > >
> > > > select *
> > > > from (select t.id, date_part('days',now()-t.stamp) AS d
> > > > from table_name t) AS t1
> > > > where t1.d > 20;
> > > >
> > > > That aside I also would like some sort of local names. Something like the
> > > > let construct used in many functional languages (not exaclty what you want
> > > > above, but still):
> > > >
> > > > let t1 = select * from foo;
> > > > t2 = select * from bar;
> > > > in select * from t1 natural join t2;
> > > >
> > > > But even though I would like to give name to subexpressions like above, I
> > > > still think postgresql should stick to standards as close as possible.
> > >
> > > the standard way of doing it would be SQL99's WITH :
> > >
> > > with t1 as (select * from foo)
> > > t2 as (select * from bar)
> > > select * from t1 natural join t2;
> > >
> > > you can even use preceeding queries
> > >
> > > with t1 as (select a,b from foo)
> > > t1less as (select a,b from t1 where a < 0)
> > > t1zero as (select a,b from t1 where a = 0)
> > > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > >
> > > Having working WITH clause is also a prerequisite to implementing SQL99
> > > recursive queries (where each query in WITH clause sees all other
> > > queries in the WITH clause)
> > >
> > > I sent a patch to this list recently that implements the above syntax,
> > > but I currently dont have knowledge (nor time to aquire it), so if
> > > someone else does not do it it will have to wait until January.
> > >
> > > OTOH, I think that turning my parsetree to a plan would be quite easy
> > > for someone familiar with turning parestrees into plans ;)
> > >
> > > I offer to check if it works in current (and make it work again if it
> > > does not) if someone would be willing to hold my hand in implementation
> > > parsetree-->plan part ;).
> > >
> > > I think that for non-recursive queries this is all that needs to be
> > > done, i.e. the plan would not care if the subqueries were from FROM,
> > > from WITH or from separately defined views.
> > >
> > > --
> > > Hannu Krosing <hannu@tm.ee>
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> --
> Hannu Krosing <hannu@tm.ee>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Tue, 3 Dec 2002, Justin Clift wrote: > > Excellent. Are there any other people involved in PostgreSQL and > > universities or educational institutions? If so we could put something > > together about experiences for the advocacy Web site. > > Is this the kind of thing that the Techdocs Guides area would be good > for? (http://techdocs.postgresql.org/guides) Seems that any discussions about "experiences" belongs on Advocacy, no?
On Tue, 3 Dec 2002, Marc G. Fournier wrote: > On Tue, 3 Dec 2002, Justin Clift wrote: > > > > Excellent. Are there any other people involved in PostgreSQL and > > > universities or educational institutions? If so we could put something > > > together about experiences for the advocacy Web site. > > > > Is this the kind of thing that the Techdocs Guides area would be good > > for? (http://techdocs.postgresql.org/guides) > > Seems that any discussions about "experiences" belongs on Advocacy, no? Where have you been? The lines of distinction between all of the lists have gotten so blurred it hardly makes a difference. Vince. -- http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it'sjust radio.
On Tue, 3 Dec 2002, Vince Vielhaber wrote: > On Tue, 3 Dec 2002, Marc G. Fournier wrote: > > > On Tue, 3 Dec 2002, Justin Clift wrote: > > > > > > Excellent. Are there any other people involved in PostgreSQL and > > > > universities or educational institutions? If so we could put something > > > > together about experiences for the advocacy Web site. > > > > > > Is this the kind of thing that the Techdocs Guides area would be good > > > for? (http://techdocs.postgresql.org/guides) > > > > Seems that any discussions about "experiences" belongs on Advocacy, no? > > Where have you been? The lines of distinction between all of the lists > have gotten so blurred it hardly makes a difference. Actually, there are lines, Justin just occasionally appears to 'blur' them until I get a chance to refresh them ... eh Justin?:)
Al Sutton wrote: > Point to Point and Broadcast replication > ---------------------------------------- > With point to point you specify multiple endpoints, with broadcast you can > specify a subnet address and the updates are broadcast over that subnet. > > The difference being that point to point works well for cross network > replication, or where you have a few replicants. I have multiple database > servers which could have a deadicated class C network that they are all on, > by broadcasting updates you can cutdown the amount of traffic on that net by > a factor of n minus 1 (where n is the number of servers involved). Yech. Now you can't use TCP anymore, so the underlying replication code has to handle all the issues that TCP deals with transparently, like error checking, retransmits, data windows, etc. I don't think it's wise to assume that your transport layer is 100% reliable. Further, this doesn't even address the problem of bringing up a leaf server that's been down a while. It can be significantly out of date relative to the other servers on the subnet. I suspect you'll be better off implementing a replication protocol that has the leaf nodes keeping each other up to date, to minimize the traffic coming from the next level up. Then you can use TCP for the connections but minimize the traffic generated by any given node. > Ability to use raw partitions > ---------------------------- > > I've not seen an install of PostgreSQL yet that didn't put the database > files onto a filesystem, so I'm assuming it's the only way of doing it. By > using the filesystem the files are at the mercy of filesystem handler code > as to where they end up on the disk, and thus the speed of access will > always have some dependancy on the speed of the filesystem. > > With a raw partition it would be possible to use two devices (e.g. /dev/hde > and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then > ensure the WALs were located on one the disk with the entries running > sequentally, and that the database files were located on the other disk in > the most appropriate location (e.g. index data starting near the center of > the disk, and user table data starting near the outside). Yeah, but now you have to worry about optimizing placement of blocks, optimizing writes, etc. These are things the OS should worry about, not the database server. If you're really that concerned about these issues, store the WAL on one (empty) filesystem and the tables on another (empty and separate) filesystem. With any reasonable filesystem you'll get reasonably close to optimal performance, especially if the filesystem code is capable of analyzing the write patterns and adapting itself accordingly. In short, I'd much rather spend the effort improving the filesystem (where everyone can benefit) than improving PostgreSQL (where only PostgreSQL users can benefit) for this item. The one good reason for making it possible to use raw partitions is to make it possible to use the PostgreSQL engine as a filesystem! :-) > Win32 Port > ------------ > I've explained the reasons before. Apart from that it's always useful to > open PostgreSQL up to a larger audience. Agreed. - Kevin Brown
On 3 Dec 2002 at 15:08, Vince Vielhaber wrote: > Where have you been? The lines of distinction between all of the > lists have gotten so blurred it hardly makes a difference. So consider this a wake up call. -- Dan Langille : http://www.langille.org/
Hi ... I just wanted to admit that an important collegue in Vienna already uses PostgreSQL instead of Oracle which makes me really proud :). We have done a training course this year and they use PostgreSQL instead of free Oracle I am happy that Austrian students are tortured with the things I have taught this year *g*.. Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at <http://cluster.postgresql.at>, www.cybertec.at <http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>
> The one good reason for making it possible to use raw partitions is to > make it possible to use the PostgreSQL engine as a filesystem! :-) Hmm.. Something just doesn't seem right about that thought ;) CREATE DATABASE filesystem; \c filesystem CREATE EXPORT /mnt AS NFS; \q mount_nfs -o port=5432 localhost:/mnt /mnt ls /mnt/pg_class -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Joe Conway writes: > That is one thing I'd like to take a look at. I think the problem is that > certain byte-sequence/multibyte-encoding combinations are illegal, so it's not > as simple an issue as it might first appear. The bytea type really shouldn't come even close to having to care about this. Actually, if you want to improve the ugly bytea literal syntax, implement the standard BLOB type. -- Peter Eisentraut peter_e@gmx.net
Marc G. Fournier wrote: > On Tue, 3 Dec 2002, Justin Clift wrote: > > >>>Excellent. Are there any other people involved in PostgreSQL and >>>universities or educational institutions? If so we could put something >>>together about experiences for the advocacy Web site. >> >>Is this the kind of thing that the Techdocs Guides area would be good >>for? (http://techdocs.postgresql.org/guides) > > > Seems that any discussions about "experiences" belongs on Advocacy, no? Good point. Have put a *really basic* Zwiki framework at: http://advocacy.postgresql.org/documents It's the same collaborative software used for the PostgreSQL Guides section, but without the look+feel added. If you want to start editing stuff right away, then feel free to use it. If you'd like it to look better first though, thenit'll be a few days... :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Marc G. Fournier wrote: <snip> > Actually, there are lines, Justin just occasionally appears to 'blur' them > until I get a chance to refresh them ... eh Justin?:) [innocent whistle] + Justin
Hi: We at the Department of Information Technology of the Mindanao State University-Iligan Institute of Technology (MSU-IIT) in Iligan City, Philippines had been using PostgreSQL since 1998 in teaching courses in Databases, SQL, and as a support tool in teaching Software Engineering and Web Application Development. We are even utilizing it as our database backend in all applications we developed in-house like Payroll, Student Enrollment, Financial Applications, etc. At the rate PostgreSQL is performing, we are not for any reason tempted to migrate to another database for the next ten years. THANKS TO THE POSTGRESQL DEVELOPMENT TEAM. We Salute YOU! PROF. MANUEL C. CABIDO Chair Department of Information Technology MSU-IIT Iligan City 9200 Philippines
----- Original Message ----- From: "Kevin Brown" <kevin@sysexperts.com> To: <pgsql-hackers@postgresql.org> Sent: Tuesday, December 03, 2002 8:49 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist > Al Sutton wrote: > > Point to Point and Broadcast replication > > ---------------------------------------- > > With point to point you specify multiple endpoints, with broadcast you can > > specify a subnet address and the updates are broadcast over that subnet. > > > > The difference being that point to point works well for cross network > > replication, or where you have a few replicants. I have multiple database > > servers which could have a deadicated class C network that they are all on, > > by broadcasting updates you can cutdown the amount of traffic on that net by > > a factor of n minus 1 (where n is the number of servers involved). > > Yech. Now you can't use TCP anymore, so the underlying replication > code has to handle all the issues that TCP deals with transparently, > like error checking, retransmits, data windows, etc. I don't think > it's wise to assume that your transport layer is 100% reliable. > > Further, this doesn't even address the problem of bringing up a leaf > server that's been down a while. It can be significantly out of date > relative to the other servers on the subnet. > > I suspect you'll be better off implementing a replication protocol > that has the leaf nodes keeping each other up to date, to minimize the > traffic coming from the next level up. Then you can use TCP for the > connections but minimize the traffic generated by any given node. > I wasn't saying that ALL replication traffic must be broadcast, if a specific server needs a refresh when it comes then point to point is fine because only one machine needs the data, and thus broadcasting it to all would load machines with data they didn't need. The aim of using broadcast is to cut down the ongoing traffic, say, for example, I have a cluster of ten database servers I can connect them onto a dedicated LAN shared only by database servers and I would see 10% of the traffic I would get if I were using point to point (this is assuming that the addition of error checking, retransmits, etc. to the broadcast protocol adds a similiar overhead per packet as TCP point to point). If others wish to know more about this I can prepare an overview for how I see it working. [Other points snipped]
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > Hi guys, > > Just out of interest, if someone was going to pay you to hack on Postgres > for 6 months, what would you like to code for 7.4? Well, on top of the oft-requested replication support and savepoint support, I'd like to see UPDATE, er, updated to be able to make use of cursors. I'd also like to see (if this is even possible) a transaction isolation mode that would make it possible for multiple concurrent updates to the same row to happen without blocking each other (I imagine one way to make this possible would be for the last transaction to commit to be the one that "wins". Each transaction that commits gets its updates written so that other transactions that begin after they commit will see them, of course). Neither "read committed" nor "serialized" modes offer this. Don't know if it's possible, but it would be nice (such that a transaction sees the database as if it has it all to itself and doesn't block on updates)... - Kevin
Kevin Brown wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > > Hi guys, > > > > Just out of interest, if someone was going to pay you to hack on Postgres > > for 6 months, what would you like to code for 7.4? > > Well, on top of the oft-requested replication support and savepoint > support, I'd like to see UPDATE, er, updated to be able to make use of > cursors. I think this could be easily done by using the tid of the cursor row for the update, assuming there is a clear tid for the SELECT. Jan has talked about doing that. > I'd also like to see (if this is even possible) a transaction > isolation mode that would make it possible for multiple concurrent > updates to the same row to happen without blocking each other (I > imagine one way to make this possible would be for the last > transaction to commit to be the one that "wins". Each transaction > that commits gets its updates written so that other transactions that > begin after they commit will see them, of course). Neither "read > committed" nor "serialized" modes offer this. Don't know if it's > possible, but it would be nice (such that a transaction sees the > database as if it has it all to itself and doesn't block on > updates)... How would you do the update if you don't know of the transaction commits or aborts? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Kevin Brown wrote: > > I'd also like to see (if this is even possible) a transaction > > isolation mode that would make it possible for multiple concurrent > > updates to the same row to happen without blocking each other (I > > imagine one way to make this possible would be for the last > > transaction to commit to be the one that "wins". Each transaction > > that commits gets its updates written so that other transactions that > > begin after they commit will see them, of course). Neither "read > > committed" nor "serialized" modes offer this. Don't know if it's > > possible, but it would be nice (such that a transaction sees the > > database as if it has it all to itself and doesn't block on > > updates)... > > How would you do the update if you don't know of the transaction commits > or aborts? Maybe I should explain what I'm thinking a little further. What I'm proposing is a transaction isolation model where each transaction thinks that it's the only one making changes to the database. That assumption obviously fails *outside* of a transaction, but inside a transaction it should be possible to maintain the illusion. We already get this with serialized transaction isolation mode, with one caveat: when an update conflicts with that of another transaction that committed while the transaction of interest is in progress, the transaction of interest gets rolled back immediately with a serialization error. What I'm proposing is to extend the illusion to updates. A transaction running in this new isolation mode (call it "full isolation" or something) never sees updates that other committed transactions perform, whether they would conflict or not. The view the running transaction sees of the database is a snapshot in time, as it were, plus any updates the transaction itself has made. Now, there are a couple of approaches we can take at transaction commit that I can think of right now: 1. If there were any serialization errors, abort the transaction at commit time. This allows transactions to safely makechanges to their local view of the database without compromising serialization. This probably wouldn't yield anybenefits over the serializable isolation level except that it would make it possible to perform experiments on a databasethat currently can't be performed (since serializable isolation aborts the transaction at the first serialization error). Applications would be easier to write since there would be only one point during a transactionthat the application would have to check for unexpected errors: the commit. 2. We commit the changed rows. Updates only happen to rows that exist at commit time. Rows which the transaction deletedand which still exist at commit time are deleted. Referential integrity rules are enforced, of course, so it'spossible for the database to retain some of its sanity even in the face of this model. But the overall state ofthe database will probably be unpredictable (in this scenario, the last concurrent transaction to commit "wins", moreor less). 3. We do something more sophisticated than 1 or 2. Perhaps something analogous to the branch merge functions that CVSand other concurrent version control systems can perform, where multiple branches are finally merged into a singleunified source snapshot. I have no idea how this would work for real, or if it's even possible (I believe CVSrequires manual intervention to resolve branch conflicts during a merge, an option that would obviously not be availableto us). How useful would it be? Beats me. Like I said, you could perform some "what if" games with a database this way that you currently can't, but I don't know how useful that would be. On thinking about it a bit, it seems option 1 would be the most useful and perhaps the most sensible. Of course, perhaps the whole thing is just another stupid idea...
Tom Lane wrote: > "Magnus Naeslund(f)" <mag@fbab.net> writes: > > Mysql is planning on making this work: > > SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id. > > We're supposed to spend our time emulating nonstandard features that > don't even exist yet? I think I have better things to do ... MySQL doesn't have it, but I believe MS SQL does (and thus Sybase probably does as well). I agree with others that variables would be quite handy, especially if they persist between statements (and they might be even handier if they persist between transactions). That's not to say that you don't have better things to work on, though. :-) -- Kevin Brown kevin@sysexperts.com This is your .signature virus: < begin 644 .signature (9V]T8VAA(0K0z end > This is your .signature virus on drugs: <> Any questions?
Kevin Brown wrote: > How useful would it be? Beats me. Like I said, you could perform > some "what if" games with a database this way that you currently > can't, but I don't know how useful that would be. On thinking about > it a bit, it seems option 1 would be the most useful and perhaps the > most sensible. > > > Of course, perhaps the whole thing is just another stupid idea... We would need to have some people who want this first. We don't add stuff of questionable value because then the feature set becomes confusing to end users. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Hi, if I may add to the wishlist for 7.4 in order of importance. Some items may have been mentioned or disputed already but I think they are quite important: 1. Avoid needing REINDEX after large insert/deletes or make REINDEX not use exclusive lock on table. 2. Automate VACUUM in background and make database more interactive/responsive during long VACUUMs 3. Replication 4. Point-in-time recovery 5. Maintain automatic clustering (CLUSTER) even after subsequent insert/updates. 6. Compression between client/server interface like in MySQL Thanks, Stephen jleelim(at)hotmail.com
On Tue, 2002-12-10 at 09:36, Stephen L. wrote: > 6. Compression between client/server interface like in MySQL > Mammoth is supposed to be donating their compression efforts back to this project, or so I've been told. I'm not exactly sure of their time-line as I've slept since my last conversation with them. The initial feedback that I've gotten back from them on this subject is that the compression has been working wonderfully for them with excellent results. IIRC, in their last official release, they announced their compression implementation. So, I'd think that it would be available for 7.4 of 7.5 time frame. -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
Would it be possible to make compression an optional thing, with the default being off? I'm in a position that many others may be in where the link between my app server and my database server isn't the bottleneck, and thus any time spent by the CPU performing compression and decompression tasks is CPU time that is in effect wasted. If a database is handling numerous small queries/updates and the request/response packets are compressed individually, then the overhead of compression and decompression may result in slower performance compared to leaving the request/response packets uncompressed. Al. ----- Original Message ----- From: "Greg Copeland" <greg@CopelandConsulting.Net> To: "Stephen L." <jleelim@hotmail.com> Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org> Sent: Tuesday, December 10, 2002 4:56 PM Subject: [mail] Re: [HACKERS] 7.4 Wishlist > On Tue, 2002-12-10 at 09:36, Stephen L. wrote: > > 6. Compression between client/server interface like in MySQL > > > > Mammoth is supposed to be donating their compression efforts back to > this project, or so I've been told. I'm not exactly sure of their > time-line as I've slept since my last conversation with them. The > initial feedback that I've gotten back from them on this subject is that > the compression has been working wonderfully for them with excellent > results. IIRC, in their last official release, they announced their > compression implementation. So, I'd think that it would be available > for 7.4 of 7.5 time frame. > > > -- > Greg Copeland <greg@copelandconsulting.net> > Copeland Computer Consulting > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Tue, 2002-12-10 at 11:25, Al Sutton wrote: > Would it be possible to make compression an optional thing, with the default > being off? > I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see what appears. What I originally had envisioned was a per database and user permission model which would better control use. Since compression can be rather costly for some use cases, I also envisioned it being negotiated where only the user/database combo with permission would be able to turn it on. I do recall that compression negotiation is part of the Mammoth implementation but I don't know if it's a simple capability negotiation or part of a larger scheme. The reason I originally imagined a user/database type approach is because I would think only a subset of a typical installation would be needing compression. As such, this would help prevent users from arbitrarily chewing up database CPU compressing data because:o datasets are uncompressable or poorly compresseso environmentcpu is at a premiumo is in a bandwidth rich environment > I'm in a position that many others may be in where the link between my app > server and my database server isn't the bottleneck, and thus any time spent > by the CPU performing compression and decompression tasks is CPU time that > is in effect wasted. Agreed. This is why I'd *guess* that Mammoth's implementation does not force compression. > > If a database is handling numerous small queries/updates and the > request/response packets are compressed individually, then the overhead of > compression and decompression may result in slower performance compared to > leaving the request/response packets uncompressed. Again, this is where I'm gray on their exact implementation. It's possible they implemented a compressed stream even though I'm hoping they implemented a per packet compression scheme (because adaptive compression becomes much more capable and powerful; in both algorithmically and logistical use). An example of this would be to avoid any compression on trivially sized result sets. Again, this is another area where I can imagine some tunable parameters. Just to be on the safe side, I'm cc'ing Josh Drake at Command Prompt (Mammoth) to see what they can offer up on it. Hope you guys don't mind. Greg > ----- Original Message ----- > From: "Greg Copeland" <greg@CopelandConsulting.Net> > To: "Stephen L." <jleelim@hotmail.com> > Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org> > Sent: Tuesday, December 10, 2002 4:56 PM > Subject: [mail] Re: [HACKERS] 7.4 Wishlist > > > > On Tue, 2002-12-10 at 09:36, Stephen L. wrote: > > > 6. Compression between client/server interface like in MySQL > > > > > > > Mammoth is supposed to be donating their compression efforts back to > > this project, or so I've been told. I'm not exactly sure of their > > time-line as I've slept since my last conversation with them. The > > initial feedback that I've gotten back from them on this subject is that > > the compression has been working wonderfully for them with excellent > > results. IIRC, in their last official release, they announced their > > compression implementation. So, I'd think that it would be available > > for 7.4 of 7.5 time frame. > > > > > > -- > > Greg Copeland <greg@copelandconsulting.net> > > Copeland Computer Consulting > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
Greg Copeland wrote: > On Tue, 2002-12-10 at 11:25, Al Sutton wrote: > > Would it be possible to make compression an optional thing, with the default > > being off? > > > > I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see > what appears. What I originally had envisioned was a per database and > user permission model which would better control use. Since compression > can be rather costly for some use cases, I also envisioned it being > negotiated where only the user/database combo with permission would be > able to turn it on. I do recall that compression negotiation is part of > the Mammoth implementation but I don't know if it's a simple capability > negotiation or part of a larger scheme. I haven't heard anything about them contributing it. Doesn't mean it will not happen, just that I haven't heard it. I am not excited about per-db/user compression because of the added complexity of setting it up, and even set up, I can see cases where some queries would want it, and others not. I can see using GUC to control this. If you enable it and the client doesn't support it, it is a no-op. We have per-db and per-user settings, so GUC would allow such control if you wish. Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, meaning it would determine if there was value in the compression and do it only when it would help. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Tue, 2002-12-10 at 13:38, Bruce Momjian wrote: > I haven't heard anything about them contributing it. Doesn't mean it > will not happen, just that I haven't heard it. > This was in non-mailing list emails that I was told this by Joshua Drake at Command Prompt. Of course, that doesn't have to mean it will be donated for sure but nonetheless, I was told it will be. Here's a quote from one of the emails. I don't think I'll be too far out of line posting this. On August 9, 2002, Joshua Drake said, "One we plan on releasing this code to the developers after 7.3 comes out. We want to be good members of the community but we have to keep a slight commercial edge (wait to you see what we are going to do to vacuum)." Obviously, I don't think that was official speak, so I'm not holding them to the fire, nonetheless, that's what was said. Additional follow ups did seem to imply that they were very serious about this and REALLY want to play nice as good shared source citizens. > I am not excited about per-db/user compression because of the added > complexity of setting it up, and even set up, I can see cases where some > queries would want it, and others not. I can see using GUC to control > this. If you enable it and the client doesn't support it, it is a > no-op. We have per-db and per-user settings, so GUC would allow such > control if you wish. > I never thought beyond the need for what form an actual implementation of this aspect would look like. The reason for such a concept would be to simply limit the number of users that can be granted compression. If you have a large user base all using compression or even a small user base where very large result sets are common, I can imagine your database server becoming CPU bound. The database/user thinking was an effort to allow the DBA to better manage the CPU effect. > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, > meaning it would determine if there was value in the compression and do > it only when it would help. Yes, that makes sense and was something I had originally envisioned. Simply stated, some installations may never want compression while others may want it for every connection. Beyond that, I believe there needs to be something of a happy medium where a DBA can better control who and what is taking his CPU away (e.g. only that one remote location being fed via ISDN). If GUC can fully satisfy, I certainly won't argue against it. -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
Without getting into too many details, why not send toast data to non-local clients? Seems that would be the big win. The data is already compressed, so the server wouldn't pay cpu time to recompress anything. And since toast data is relatively large anyway, it's the stuff you'd want to compress before putting it on the wire anyway. If this is remotely possible let me know, I might be interested in taking a look at it. -Kyle Bruce Momjian wrote: > > I am not excited about per-db/user compression because of the added > complexity of setting it up, and even set up, I can see cases where some > queries would want it, and others not. I can see using GUC to control > this. If you enable it and the client doesn't support it, it is a > no-op. We have per-db and per-user settings, so GUC would allow such > control if you wish. > > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, > meaning it would determine if there was value in the compression and do > it only when it would help.
This has been brought up a couple of times now. Feel free to search the old archives for more information. IIRC, it would of made the implementation more problematic, or so I think it was said. When I originally brought the topic (compression) up, it was not well received. As such, it may of been thought that additional effort on such an implementation would not be worth the return on a feature which most seemingly didn't see any purpose in supporting in the first place. You need to keep in mind that many simply advocated using a compressing ssh tunnel. Seems views may of changed some since then so it may be worth revisiting. Admittedly, I have no idea what would be required to move the toast data all the way through like that. Any idea? Implementing a compression stream (which seems like what was done for Mammoth) or even packet level compression were both something that I could comfortably put my arms around in a timely manner. Moving toast data around wasn't. Greg On Tue, 2002-12-10 at 18:45, Kyle wrote: > Without getting into too many details, why not send toast data to > non-local clients? Seems that would be the big win. The data is > already compressed, so the server wouldn't pay cpu time to recompress > anything. And since toast data is relatively large anyway, it's the > stuff you'd want to compress before putting it on the wire anyway. > > If this is remotely possible let me know, I might be interested in > taking a look at it. > > -Kyle > > Bruce Momjian wrote: > > > > I am not excited about per-db/user compression because of the added > > complexity of setting it up, and even set up, I can see cases where some > > queries would want it, and others not. I can see using GUC to control > > this. If you enable it and the client doesn't support it, it is a > > no-op. We have per-db and per-user settings, so GUC would allow such > > control if you wish. > > > > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, > > meaning it would determine if there was value in the compression and do > > it only when it would help. -- Greg Copeland <greg@copelandconsulting.net> Copeland Computer Consulting
I'd like to show/register interest. I can see it being very useful when combined with replication for situations where the replicatiant databases are geographically seperated (i.e. Disaster Recover sites or systems maintaining replicants in order to reduce the distance from user to app to database). The bandwidth cost savings from compressing the replication information would be immensly useful. Al. ----- Original Message ----- From: "Joshua D. Drake" <jd@commandprompt.com> To: "Bruce Momjian" <pgman@candle.pha.pa.us> Cc: "Greg Copeland" <greg@CopelandConsulting.Net>; "Al Sutton" <al@alsutton.com>; "Stephen L." <jleelim@hotmail.com>; "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org> Sent: Tuesday, December 10, 2002 8:04 PM Subject: Re: [mail] Re: [HACKERS] 7.4 Wishlist > Hello, > > We would probably be open to contributing it if there was interest. > There wasn't interest initially. > > Sincerely, > > Joshua Drake > > > Bruce Momjian wrote: > > Greg Copeland wrote: > > > >>On Tue, 2002-12-10 at 11:25, Al Sutton wrote: > >> > >>>Would it be possible to make compression an optional thing, with the default > >>>being off? > >>> > >> > >>I'm not sure. You'd have to ask Command Prompt (Mammoth) or wait to see > >>what appears. What I originally had envisioned was a per database and > >>user permission model which would better control use. Since compression > >>can be rather costly for some use cases, I also envisioned it being > >>negotiated where only the user/database combo with permission would be > >>able to turn it on. I do recall that compression negotiation is part of > >>the Mammoth implementation but I don't know if it's a simple capability > >>negotiation or part of a larger scheme. > > > > > > I haven't heard anything about them contributing it. Doesn't mean it > > will not happen, just that I haven't heard it. > > > > I am not excited about per-db/user compression because of the added > > complexity of setting it up, and even set up, I can see cases where some > > queries would want it, and others not. I can see using GUC to control > > this. If you enable it and the client doesn't support it, it is a > > no-op. We have per-db and per-user settings, so GUC would allow such > > control if you wish. > > > > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO, > > meaning it would determine if there was value in the compression and do > > it only when it would help. > > > > -- > <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> > <CONTACT> <PHONE>+1.503.222-2783</PHONE> </CONTACT> > >