Re: [HACKERS] tsearch thoughts
I mean, when the index is created over the text column, instead of just indexing the text as-is, index the txt2txtidx'd version...? For two reasons: 1. gist_txtidx_ops create with loss information (for less size), so any operation with index must be checked with original txtidx value. The way REATE INDEX my_idx ON test USING gist(txt2txtidx(a)) may decreas performance :( 2 OpenFTS. We wanted that txtidx works with OpenFTS. And adding dictionaries, txt2txtidx, trigger, type mquery_txt etc was an experiment. -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 7.4 Wishlist
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 Released http://developers.slashdot.org/article.pl?sid=02/11/30/1815200 Lee. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] One SQL to access two databases.
On 2 Dec 2002 at 12:05, Karel Zak wrote: On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: As I said, this is all very preliminary; comments, suggestions, requests are all welcome. Only idea/dream: what implement dblink as virtual schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Well, that is an excellent solution but I want to suggest a further modification.. How would you select from a table in certain schema in remote database? i.e. does current implementation supports nested schemas? Like select * from remotedb.schemaa.tablea will work? If it does, this might be the best transparency we could ever get.. Bye Shridhar -- pension:A federally insured chain letter. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Native Windows port Plz don't forget poor victims of Microsoft !!!
Re: [HACKERS] [GENERAL] One SQL to access two databases.
On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: As I said, this is all very preliminary; comments, suggestions, requests are all welcome. Only idea/dream: what implement dblink as virtual schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Am Sonntag, 1. Dezember 2002 06:47 schrieb Tom Lane: Joe Conway [EMAIL PROTECTED] writes: Someone asked earlier about how to change a bunch of existing tables int the PUBLIC schema to some other schema. For grins I tried: regression=# update pg_class set relnamespace=556829 where relname = 'foo' and relnamespace=2200; UPDATE 1 and it seemed to work fine (i.e. moved foo from schema public to schema bar). But it didn't fix the pg_depend entries linking the table to its schema :-( But it made me wonder if we shouldn't have: ALTER TABLE table SCHEMA TO new_schema I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y I don't see anything in the SQL spec about this; anyone know what precedent is in Oracle or other DBMSes? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Here is, what DB2 has to offer: DB2: Syntax DB2: DB2:.-TABLE-. DB2: -RENAME--+---+--table-name--TO--new-table-identifier- DB2: DB2: Description DB2: DB2: |table-name DB2: Names the existing table that is to be renamed. The name, including the DB2: schema name, must identify a table that already exists in the database DB2: (SQLSTATE 42704). It can be an alias identifying the table. It must not DB2: be the name of a catalog table (SQLSTATE 42832), a summary table, a DB2: typed table (SQLSTATE 42997), a nickname, or an object of other than DB2: table or alias (SQLSTATE 42809). DB2: DB2: |new-table-identifier DB2: |Specifies the new name for the table without a schema name. The |schema DB2: name of the table-name is used to qualify the new name for the |table. DB2: The qualified name must not identify a table, view, |or alias that DB2: already exists in the database (SQLSTATE 42710). It looks like it is not possible to move a table from one schema to another. ALTER TABLE don't handle schemas either. But I like the RENAME a.x to b.x-syntax. Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
How about giving OLAP (Dimension / Measure) functionality to PG. Catch all the cricket action. Download Yahoo! Score tracker
Re: [HACKERS] 7.4 Wishlist
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 [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
-Original Message- From: Karel Zak [mailto:[EMAIL PROTECTED]] Sent: 02 December 2002 11:26 To: Christopher Kings-Lynne Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] 7.4 Wishlist 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... I do :-) Regards, Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
my wish: * error codes. It's very interesting that nobody other wants it... I do :-) Me too. It is a must in my opinion.. Regards, Nic. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Joe Conway wrote: Christopher Kings-Lynne wrote: possible. We should probably just go with your suggestion. Anything else beyond the relnamespace and pg_depend entries that need to be dealt with? What about sequences for serial columns? What about views or types that depend on the table? Yeah, good point. I think properly dealing with the pg_depends issues will catch anything of that nature, but what to do with them? Probably should move dependent type, constraint, index entries to the same new namespace. We might want to move related sequences, but I'm not sure we'd want to do that silently, since the sequence could be in use for other tables as well. And we should probably restrict the change if there are dependent functions or views. Does this capture the issues? Why not just leave the sequence and types in the original schema and make sure the table refers to them _there_? We just need to make sure we have schema qualified references to the sequences and types. Indexes, triggers (and constraints), toast tables etc. are related to just one table so they can migrate together, I think. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3 gotchas for applications and client libraries
Tom/Hackers,
Going back a bit, but relevant with 7.3's release...
Tom Lane writes on 03 Sep 2002:
Lee Kindness [EMAIL PROTECTED] writes:
[ original post was regarding the mileage in adding utility
functions to PostgreSQL to cut-out common catalog lookups, thus
making apps less fragile to catalog changes ]
CREATE OR REPLACE FUNCTION column_exists(NAME, NAME) RETURNS BOOLEAN AS '
CREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS'
Obviously these need attention when our application targets 7.3 (and
thanks for the heads-up), but all changes are localised.
They are? What will your policy be about schema names --- won't you
have to touch every caller to add a schema name parameter?
As it turns out, no. And thinking about i'm sure this is right
approach too, assuming:
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.foo(f1 INT, f2 TEXT);
CREATE TABLE b.foo(f1 TEXT, f2 NUMERIC(10,1));
then:
SELECT column_exists('foo', 'f1');
should return 'f', however:
SELECT column_exists('a.foo', 'f1');
should return 't', likewise with:
SET SEARCH_PATH TO a,public;
SELECT column_exists('foo', 'f1');
I can't see any use in a separate parameter - the user will want the
current - in scope - table, or explicitly specify the schema with the
table name.
I'm not averse to trying to push logic over to the backend, but I think
the space of application requirements is wide enough that designing
general-purpose functions will be quite difficult.
On the whole I'd agree, but I think determining if a table/column
exists has quite a high usage... More so with things like
current_database() added to 7.3. Anyway, for reference here are
column_exists(table, column) and table_exists(table) functions for
PostgreSQL 7.3, changes from 7.3 version maked by ' -- PG7.3':
\echo creating function: column_exists
CREATE OR REPLACE FUNCTION column_exists(NAME, NAME) RETURNS BOOLEAN AS '
DECLARE
tab ALIAS FOR $1;
col ALIAS FOR $2;
rec RECORD;
BEGIN
SELECT INTO rec *
FROM pg_class c, pg_attribute a
WHERE c.relname = tab
AND pg_table_is_visible(c.oid) -- PG7.3
AND c.oid = a.attrelid
AND a.attnum 0
AND a.attname = col;
IF NOT FOUND THEN
RETURN false;
ELSE
RETURN true;
END IF;
END;
' LANGUAGE 'plpgsql';
\echo creating function: table_exists
CREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS '
DECLARE
tab ALIAS FOR $1;
rec RECORD;
BEGIN
SELECT INTO rec *
FROM pg_class c
WHERE c.relname = tab;
AND pg_table_is_visible(c.oid) -- PG7.3
IF NOT FOUND THEN
RETURN false;
ELSE
RETURN true;
END IF;
END;
' LANGUAGE 'plpgsql';
Of course, thanks for the original email in this thread:
http://www.ca.postgresql.org/docs/momjian/upgrade_tips_7.3
Thanks, Lee Kindness.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Patch to make Turks happy.
Hi,
Yet another problem with Turkish encoding. clean_encoding_name()
in src/backend/utils/mb/encnames.c uses tolower() to convert locale
names to lower-case. This causes errors if locale name contains
capital I and current olcale is Turkish. Some examples:
aaa=# \l
List of databases
Name| Owner | Encoding
---+---+--
aaa | pgsql | LATIN5
bbb | pgsql | LATIN5
template0 | pgsql | LATIN5
template1 | pgsql | LATIN5
(4 rows)
aaa=# CREATE DATABASE ccc ENCODING='LATIN5';
ERROR: LATIN5 is not a valid encoding name
aaa=# \encoding
SQL_ASCII
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
Patch, is a simple change to use ASCII-only lower-case conversion
instead of locale-dependent tolower()
Best regards,
Nic.
*** ./src/backend/utils/mb/encnames.c.orig Mon Dec 2 15:58:49 2002
--- ./src/backend/utils/mb/encnames.c Mon Dec 2 18:13:23 2002
***
*** 407,413
for (p = key, np = newkey; *p != '\0'; p++)
{
if (isalnum((unsigned char) *p))
! *np++ = tolower((unsigned char) *p);
}
*np = '\0';
return newkey;
--- 407,416
for (p = key, np = newkey; *p != '\0'; p++)
{
if (isalnum((unsigned char) *p))
! if (*p = 'A' *p = 'Z')
! *np++ = *p + 'a' - 'A';
! else
! *np++ = *p;
}
*np = '\0';
return newkey;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Fernando Nasser wrote: Why not just leave the sequence and types in the original schema and make sure the table refers to them _there_? We just need to make sure we have schema qualified references to the sequences and types. Well, the type entry for the relation *is* related to just one table, so I'd be inclined to move it also. But leaving the sequence alone might be the best thing to do. Although, I think sequences created via SERIAL are dropped with their referencing table now, aren't they? test=# create table myserial(id serial); NOTICE: CREATE TABLE will create implicit sequence 'myserial_id_seq' for SERIAL column 'myserial.id' CREATE TABLE test=# \ds myserial_id_seq List of relations Schema | Name | Type | Owner +-+--+-- public | myserial_id_seq | sequence | postgres (1 row) test=# drop table myserial; DROP TABLE test=# \ds myserial_id_seq No matching relations found. Maybe that's an argument that they ought to also move to the new schema when the dependency exists. Indexes, triggers (and constraints), toast tables etc. are related to just one table so they can migrate together, I think. I agree. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgres 7.3 announcement on postgresql.org
On Fri, 29 Nov 2002, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Has there been a release? It certainly hasn't been announced in the usual places that I monitor. Marc claimed he'd put out the announcement on pgsql-announce, but that copy of the message never arrived here (it did show up on pgsql-general though). Evidently you and Vince never got it either ... I'll resend it (albeit late), but I know I did go in and approve the posting to -annouce within seconds of sending it :( ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgres 7.3 announcement on postgresql.org
On Fri, 29 Nov 2002, Vince Vielhaber wrote: On Fri, 29 Nov 2002, Bruce Momjian wrote: FYI, Vince, I started reading all my email (using elm) in a special 120 column wide, 38 row xterm. There was just too much detail in those subjects i was missing. Doesn't do me much good if too often I don't have the luxury of a large screen 'cuze I'm reading from a remote site with horrible resolution or just an 80x25 screen. Sorry, but definitely sounds like a personal problem here :( The press release was extensively discussed on the -advocacy mailing list, and *repeatedly* Josh and Robert asked for feedback on it ... you are right, I could have shorten'd the subject a wee bit, error on my part that I will try not to repeat in the future ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Postgres 7.3 announcement on postgresql.org
On Mon, 2 Dec 2002, Marc G. Fournier wrote: On Fri, 29 Nov 2002, Vince Vielhaber wrote: On Fri, 29 Nov 2002, Bruce Momjian wrote: FYI, Vince, I started reading all my email (using elm) in a special 120 column wide, 38 row xterm. There was just too much detail in those subjects i was missing. Doesn't do me much good if too often I don't have the luxury of a large screen 'cuze I'm reading from a remote site with horrible resolution or just an 80x25 screen. Sorry, but definitely sounds like a personal problem here :( The press release was extensively discussed on the -advocacy mailing list, and *repeatedly* Josh and Robert asked for feedback on it ... you are right, I could have shorten'd the subject a wee bit, error on my part that I will try not to repeat in the future ... I'm not on the advocacy list, nor do I want to be. Alot of people watch hackers for things like release notices. Why send a sales oriented document that was written for people who know nothing about postgresql to hackers anyway? Save the fluff for the other lists and send a normal announcement here. BTW, I got the copy of the announcement that went to the ANNOUNCE list. I'm guessing most folks blew it off as junkmail due to it's format. Vince. -- http://www.meanstreamradio.com http://www.unknown-artists.com Internet radio: It's not file sharing, it's just radio. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] One SQL to access two databases.
Karel Zak wrote: On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: As I said, this is all very preliminary; comments, suggestions, requests are all welcome. Only idea/dream: what implement dblink as virtual schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Yeah, something along these lines is in my long term vision, but I don't think it will happen for 7.4. I'd like one more contrib/dblink release for the code to mature, and to solidify the features and understand the common usage issues. Hopefully for the release *after* 7.4 I'll be ready to make a proposal to integrate dblink into the backend, get it accepted, and get it implemented. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] numeric to text (7.3)
Hello In PostgreSQL 7.2/7.1: template1=# select text(2.000::numeric); text -- 2 (1 row) In 7.3: template1=# select text(2.000::numeric); text --- 2.000 (1 row) The text(numeric) function doesn't round numbers. :( This is bug or feature? :) -Sygma ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
I wonder if the sequences created by SERIAL should not be going into a pg_sequence schema and protected like the toast tables are. One could still share sequences by explicitly creating them and using a DEFAULT clause with nextval(). We could even stop printing that annoying NOTICE ;-) Regards, Fernando Joe Conway wrote: Fernando Nasser wrote: Why not just leave the sequence and types in the original schema and make sure the table refers to them _there_? We just need to make sure we have schema qualified references to the sequences and types. Well, the type entry for the relation *is* related to just one table, so I'd be inclined to move it also. But leaving the sequence alone might be the best thing to do. Although, I think sequences created via SERIAL are dropped with their referencing table now, aren't they? test=# create table myserial(id serial); NOTICE: CREATE TABLE will create implicit sequence 'myserial_id_seq' for SERIAL column 'myserial.id' CREATE TABLE test=# \ds myserial_id_seq List of relations Schema | Name | Type | Owner +-+--+-- public | myserial_id_seq | sequence | postgres (1 row) test=# drop table myserial; DROP TABLE test=# \ds myserial_id_seq No matching relations found. Maybe that's an argument that they ought to also move to the new schema when the dependency exists. Indexes, triggers (and constraints), toast tables etc. are related to just one table so they can migrate together, I think. I agree. Joe -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.3: Change in cursor behaviour?
I've been getting reports of one of my test scenarios for libpqxx failing with postgres 7.3. At the moment I can't reproduce this (I'm still on 7.2) and I can't find anything pertinent in CVS commit messages, mailing lists etc. so I'd really appreciate any lucidity from this list. My problem appears to be related to moving a cursor backwards beyond its point of origin. I realize this is a tacky thing to do, but I sort of got the impression that moving backwards by some number larger than the current position would be equivalent to MOVE BACKWARD ALL. Which makes my code a hell of a lot simpler. It makes a good portion of my life a hell of a lot simpler, come to think of it, so I'd really appreciate having this property in the future. The scenario boils down to: Create a cursor, fetch n rows, move minus 2 billion or so rows, fetch 1 row. That last fetch used to give me the row I was hoping for (the original first row again), but with 7.3 it appears to yield nothing. Is this intentional? Should I change my code? Have I been a bad boy? Jeroen ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] numeric to text (7.3)
template1=# select text(2.000::numeric); text --- 2.000 (1 row) The text(numeric) function doesn't round numbers. :( This is bug or feature? :) I'd say feature in that it doesn't reduce the precision of the number. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] numeric to text (7.3)
On Mon, Dec 02, 2002 at 01:35:47PM -0500, Rod Taylor wrote:
template1=# select text(2.000::numeric);
text
---
2.000
(1 row)
The text(numeric) function doesn't round numbers. :(
This is bug or feature? :)
I'd say feature in that it doesn't reduce the precision of the number.
... and, of course, you can round with:
joel@joel=# select round('2.000'::numeric);
round
---
2
(1 row)
joel@joel=# select round('2.000'::numeric,2);
round
---
2.00
(1 row)
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
We could even stop printing that annoying NOTICE ;-) Agreed with this part :) -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.3: Change in cursor behaviour?
On Mon, 2002-12-02 at 10:20, Jeroen T. Vermeulen wrote: The scenario boils down to: Create a cursor, fetch n rows, move minus 2 billion or so rows, fetch 1 row. That last fetch used to give me the row I was hoping for (the original first row again), but with 7.3 it appears to yield nothing. Seems to work the fine for me: rbt=# select version(); version PostgreSQL 7.4devel on i386-unknown-freebsd4.7, compiled by GCC 2.95.4 (1 row) rbt=# begin; BEGIN rbt=# declare c cursor for select * from pg_attribute; DECLARE CURSOR rbt=# fetch 5 from c; attrelid | attname| atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount --+--+--+---+++--+-+---+--++--+--++---+--++- 1247 | typname | 19 |-1 | 64 | 1 |0 | -1 |-1 | f| p | f | i| t | f | f| t | 0 1247 | typnamespace | 26 |-1 | 4 | 2 |0 | -1 |-1 | t| p | f | i| t | f | f| t | 0 1247 | typowner | 23 | 0 | 4 | 3 |0 | -1 |-1 | t| p | f | i| t | f | f| t | 0 1247 | typlen | 21 | 0 | 2 | 4 |0 | -1 |-1 | t| p | f | s| t | f | f| t | 0 1247 | typbyval | 16 | 0 | 1 | 5 |0 | -1 |-1 | t| p | f | c| t | f | f| t | 0 (5 rows) rbt=# move -15 in c; MOVE 4 rbt=# fetch 1 from c; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount --+-+--+---+++--+-+---+--++--+--++---+--++- 1247 | typname | 19 |-1 | 64 | 1 |0 | -1 |-1 | f| p | f | i| t | f | f| t | 0 (1 row) -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] numeric to text (7.3)
This feature is missing from 7.3..
(new round function is good idea (e.g. fround(numeric))
double precision | pg_catalog | round | double precision
numeric | pg_catalog | round | numeric
numeric | pg_catalog | round | numeric, integer
Looks like round still exists to me.
rbt=# select round('2.4555', 2);
round
---
2.46
(1 row)
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] 7.3: Change in cursor behaviour?
On Mon, Dec 02, 2002 at 02:29:03PM -0500, Rod Taylor wrote: Seems to work the fine for me: Puzzling... Would you mind, if you have time, downloading libpqxx from GBorg and doing a ./configure; make; make check and telling me if tests 19 38 succeed? I expect them to have identical results, so you're likely to see either two failures or none. Be aware that the test run will add two tables, events and orgevents to your default database and delete any contents found in them. Download is at http://gborg.postgresql.org/project/libpqxx/download/download.php and direct URL for the source tarball is ftp://gborg.postgresql.org/pub/libpqxx/stable/libpqxx-1.1.4.tar.gz TIA! Jeroen ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] numeric to text (7.3)
On Mon, Dec 02, 2002 at 08:23:24PM +0100, Szima Gábor wrote:
OK, but:
template1=# select round('2.001'::numeric);
round
---
2
(1 row)
template1=# select round('2.001'::numeric,2);
round
---
2.00
(1 row)
The good idea (in 7.2):
template1=# select text('2.000'::numeric);
text
--
2
(1 row)
template1=# select text('2.001'::numeric);
text
---
2.001
(1 row)
This feature is missing from 7.3..
Not sure I'd call it a feature -- ISTM that text(numeric) should show
all the precision you gave it, and not shave it down to the
least-precise number that is still equal.
Anyhoo, you can get what you want with some ugly-but-straightforward
trimming:
(in 7.3):
joel@joel=# select rtrim(rtrim('2.000'::numeric, '0'),'.');
rtrim
---
2
(1 row)
joel@joel=# select rtrim(rtrim('2.001'::numeric, '0'),'.');
rtrim
---
2.001
(1 row)
Easy enough to make this into a function trim_as_much(numeric) or
somesuch.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PG 7.3: Query Meta Data with the JDBC-driver
Hi, Just compiled the 7.3 branch from source and made some tests using the JDBC driver coming with it. I did some tests with the henplus JDBC-shell and noticed some problems quering the database meta data: o the foreign key name is 'wierd' --- DatabaseMetaData meta = conn.getMetaData(); ResultSet rset = meta.getImportedKeys(null, null, 'bar'); rset.next(); String foreignKeyName=rset.getString(12); --- results in names that seemingly contains the internal representation: fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 (see below for an example) o It takes _ages_ to retrieve the meta data. While doing a 'describe', the postmaster process runs on 100% CPU. And: it takes extremly different amounts of time. Executing the describe-command below, it took (56.285 sec, 12.799 sec, 5 min 13.468, 12.203 sec) to execute the same command. This look very like a missing or random break-condition somewhere in a loop ? o this might be a minor point, but annoying as well: the columns are not ordered in the sequence the're created in the table. If this cannot be reproduced, I'll try to track this down, but probably this seems simple to you (BTW: doing this with the current 7.4development CVS on my machine, this results in a segmentation fault on the postmaster side - this indicates, that there indeed is a problem ..) ===8== pg create table foo (id int4 constraint pk_foo primary key); pg create table bar ( id int4 constraint pk_bar primary key, fooref int4 constraint fk_foo_id references foo(id) ); pg describe bar catalog: postgres '-' : referencing +-+--+-+++ column | type | null | default | pk | fk | +-+--+-+++ fooref | int4(4) | YES | [NULL] || fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 | | | | || - foo(id) | id | int4(4) | NO | [NULL] | pk_bar | | +-+--+-+++ 56.285 sec === ciao, -hen BTW: henplus JDBC-Shell can be found http://henplus.sourceforge.net/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Yeah, good point. I think properly dealing with the pg_depends issues will catch anything of that nature, but what to do with them? Probably should move dependent type, constraint, index entries to the same new namespace. We might want to move related sequences, but I'm not sure we'd want to do that silently, since the sequence could be in use for other tables as well. And we should probably restrict the change if there are dependent functions or views. Does this capture the issues? Why just restrict them to moving tables? What if someone wants to move a function or an aggregate to another schema? What if they want to copy it? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] toast table growing indefinitely? Known
Good call; they are still default (1 pages) and we are seeing 26000 pages per day being updated: NOTICE: Removed 102226 tuples in 26002 pages. CPU 2.02s/1.87u sec elapsed 69.85 sec. NOTICE: Pages 201819: Changed 26128, Empty 0; Tup 315227: Vac 102226, Keep 0, UnUsed 393793. Total CPU 9.57s/3.07u sec elapsed 189.32 sec. I've increased the value to 4 since we have much busier days, and I'll se what happens. Hang on - so when vacuum runs it knows exactly how many pages it cleaned up but if the fsm value is low, it doesn't help? Why don't we just automaticlaly set the FSM value to the max that vacuum has to clean up? Why don't we introduce: max_fsm_pages = auto or something? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] [GENERAL] PostgreSQL Global Development Group Announces Version 7.3(fwd)
Wow, this sounds great. Where can I get a copy? Why would anyone use anything else? ;-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---BeginMessage--- For Immediate Release November 28th, 2002 Contacts: Justin Clift [EMAIL PROTECTED] +61.3 9363 1313 (Australia) Marc Fournier [EMAIL PROTECTED] +1.902 542 0713 (Canada) PostgreSQL Global Development Group Announces Version 7.3 The PostgreSQL Global Development Group proudly announces the release of version 7.3 of the PostgreSQL object-relational database management system (ORDBMS). PostgreSQL, the world's most advanced open source database, provides solutions for many of the most demanding applications in use today, saving businesses and governments millions of dollars each year. Here is what some current PostgreSQL users have gone on record to say about this technology: PostgreSQL has scaled perfectly with our rapidly expanding business, and we recommend it over every other DB. -- TrustCommerce, California PostgreSQL provided sales.org with a solution that was $70,000 less expensive to create, and over 70% lower in cost to operate and maintain than any of the commercial DBMS offerings we looked at. -- sales.org Inc., Toronto PostgreSQL handles virtually all the standard SQL constructs. It is easy (relatively speaking) to administer, it is fast, it is efficient, it has a great API, and it supports ODBC, why would you choose something else? -- Mohawk Software, Massachusetts The worldwide PostgreSQL community is very excited about this release, which includes numerous modifications and enhancements thanks to the contributions of over 500 developers and thousands of volunteer testers from more than 50 countries. PostgreSQL 7.3 is full of new, oft requested features such as SQL '92 schemas, prepared statements, and stored procedures that can return record sets. And under the hood there is a new dependency tracking system that allows PostgreSQL to *safely* support many more subtle enhancements like the ability to drop columns, said Neil Conway, a member of the PostgreSQL Global Development Team. Among the advances in PostgreSQL version 7.3 are: Schemas PostgreSQL now joins the handful of ORDBMS's to support the SQL 92 Schema specification, improving both enterprise database management and security through the use of namespaces. Table Functions PostgreSQL version 7.3 has greatly simplified returning result sets of rows and columns in database functions. This significantly enhances the useability of stored procedures in PostgreSQL, and will make it even easier to port Oracle applications to PostgreSQL. Security Advances In response to community demands, PostgreSQL has added schema, function, and other permissions and settings to increase the database administrator's granular control over security. Other Enhancements to PostgreSQL Version 7.3 includes: - Enhanced dependency tracking for complex databases. - Prepared queries for maximized performance on common requests. - Expanded logging options - Supports data in many international characters sets (UNICODE, EUC_JP, EUC_CN, EUC_KR, JOHAB, EUC_TW, ISO 8859-1 ECMA-94, KOI8, WIN1256, etc...) - Dozens of performance enhancements to maintain PostgreSQL's leading position in ORDMBSs. Source for this release is available at: http://advocacy.postgresql.org/download/ More information on PostgreSQL is available in nine languages on the PostgreSQL Advocacy website: http://advocacy.postgresql.org A complete list of changes in PostgreSQL version 7.3 can be found in the HISTORY file included with the release, or available on the web at: http://advocacy.postgresql.org/changes/73/ About PostgreSQL: With more than 16 years of development by hundreds of the world's most generous and brilliant minds from the open source community, PostgreSQL is the world's most advanced open source database. With its long time support of an enterprise level feature set including transactions, stored procedures, triggers, and subqueries, PostgreSQL is being used by many of today's most demanding businesses. Corporations such as BASF, Red Hat, Afilias Limited (suporting the technical backend of the .org and .info domains), Cisco, Chrysler, and 3Com rely on PostgreSQL's rock solid performance record and open development process. PostgreSQL is available under a BSD License for both commercial and non-commercial use. To find out more about PostgreSQL or to download it, please visit: http://advocacy.postgresql.org ---(end of
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG 7.3: Query Meta Data with the JDBC-driver
On Mon, 2 Dec 2002, Henner Zeller wrote: Hi, Just compiled the 7.3 branch from source and made some tests using the JDBC driver coming with it. I did some tests with the henplus JDBC-shell and noticed some problems quering the database meta data: o the foreign key name is 'wierd' --- DatabaseMetaData meta = conn.getMetaData(); ResultSet rset = meta.getImportedKeys(null, null, 'bar'); rset.next(); String foreignKeyName=rset.getString(12); --- results in names that seemingly contains the internal representation: fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 (see below for an example) In September, I proposed a patch to change this to the foreign key name. This was rejected because = 7.2 servers don't enforce unique constraint names per table, so it was decided to keep the above behavior to guaranteee a unique name. I think this should be changed. See the original discussion at... http://archives.postgresql.org/pgsql-patches/2002-09/msg00150.php o It takes _ages_ to retrieve the meta data. While doing a 'describe', the postmaster process runs on 100% CPU. And: it takes extremly different amounts of time. Executing the describe-command below, it took (56.285 sec, 12.799 sec, 5 min 13.468, 12.203 sec) to execute the same command. This look very like a missing or random break-condition somewhere in a loop ? The query to generate the ResultSet is a monster and has enough tables involved to enable the genetic query optimizer which is neither consistent nor particularly good. I was able to solve this using an ANALYZE, but the long term solution is to state the desired join order explicitly in the query using JOIN statements. I will submit a patch to this effect later this week. o this might be a minor point, but annoying as well: the columns are not ordered in the sequence the're created in the table. I have already submitted a patch to fix this because of a previous complaint. http://fts.postgresql.org/db/mw/msg.html?mid=1359758 If this cannot be reproduced, I'll try to track this down, but probably this seems simple to you (BTW: doing this with the current 7.4development CVS on my machine, this results in a segmentation fault on the postmaster side - this indicates, that there indeed is a problem ..) Will investigate as well. ===8== pg create table foo (id int4 constraint pk_foo primary key); pg create table bar ( id int4 constraint pk_bar primary key, fooref int4 constraint fk_foo_id references foo(id) ); pg describe bar catalog: postgres '-' : referencing +-+--+-+++ column | type | null | default | pk | fk | +-+--+-+++ fooref | int4(4) | YES | [NULL] || fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 | | | | || - foo(id) | id | int4(4) | NO | [NULL] | pk_bar | | +-+--+-+++ 56.285 sec === ciao, -hen BTW: henplus JDBC-Shell can be found http://henplus.sourceforge.net/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] ALTER .. ADD PRIMARY KEY
Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Why just restrict them to moving tables? What if someone wants to move a function or an aggregate to another schema? What if they want to copy it? Copying might be tricky, but I'd be happy to help with moving everything else around. Though I don't think sequences can move (until we can properly track their dependencies) but everything else should be able to. Copy is another story all together. But I'd like a CREATE SCHEMA ... AS COPY schemaname; -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] toast table growing indefinitely? Known
I've increased the value to 4 since we have much busier days, and I'll se what happens. Hang on - so when vacuum runs it knows exactly how many pages it cleaned up but if the fsm value is low, it doesn't help? Why don't we just automaticlaly set the FSM value to the max that vacuum has to clean up? Why don't we introduce: max_fsm_pages = auto or something? I assume it uses shared memory, so I doubt it's very easy to increase on the fly -- without having to reduce something else anyway. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] protecting prosrc (was Re: [GENERAL] USAGE on schema allowed by
NOTE: redirecting to hackers On Mon, 2002-12-02 at 12:50, Joe Conway wrote: Tom Lane wrote: For example, something I've heard repeatedly is that people would like to hide the source code of their SQL or PLxxx functions from users who are nonetheless allowed to call those functions. A row-wise selective view of pg_proc can't fix that. In many cases it's less than clear which rows of which catalogs to hide anyway. It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe aes) using the owner's passwd from pg_shadow. We would need a new bool column in pg_proc (proisencrypted?) and some logic in fmgr.c. Is there sufficient interest to justify the effort? I think this would be a good idea, though there becomes a question of what type of performance hit comes into play when doing this. I suppose if you have an option whether to encrypt it or not that would help. One other thing is that it needs to be decryptable by owners and superusers. Robert Treat ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER .. ADD PRIMARY KEY
Just check out the AlterTableSetNotNull function in tablecmds.c to see a quick and easy example of checking for NULL values. Chris - Original Message - From: Rod Taylor [EMAIL PROTECTED] To: PostgreSQL-development [EMAIL PROTECTED] Sent: Monday, December 02, 2002 12:31 PM Subject: [HACKERS] ALTER .. ADD PRIMARY KEY Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER .. ADD PRIMARY KEY
Just check out the AlterTableSetNotNull function in tablecmds.c to see a quick and easy example of checking for NULL values. Chris - Original Message - From: Rod Taylor [EMAIL PROTECTED] To: PostgreSQL-development [EMAIL PROTECTED] Sent: Monday, December 02, 2002 12:31 PM Subject: [HACKERS] ALTER .. ADD PRIMARY KEY Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] protecting prosrc (was Re: [GENERAL] USAGE on schema allowed by
It wouldn't be terribly difficult to encrypt prosrc with 3des (or maybe aes) using the owner's passwd from pg_shadow. We would need a new bool column in pg_proc (proisencrypted?) and some logic in fmgr.c. Is there sufficient interest to justify the effort? I think this would be a good idea, though there becomes a question of what type of performance hit comes into play when doing this. I suppose if you have an option whether to encrypt it or not that would help. One other thing is that it needs to be decryptable by owners and superusers. Surely a more generic column privileges implementation would be better? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Christopher Kings-Lynne [EMAIL PROTECTED] 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 /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s 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
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PG 7.3: Query Meta Data with the JDBC-driver
We need contrib/array in 7.4 and unique name in pg_constraint Whelp. I'd like to make the constraint name unique too, but how do you coax everyone into renaming their existing constraints -- especially when there isn't an ALTER CONSTRAINT ... RENAME type statement? -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.4 Wishlist
On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: Stephan Szabo [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
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 [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
[HACKERS] unofficial 7.3 RPMs
I've hacked the spec file from a 7.2.x source RPM to produce a 7.3 source RPM. I've also created a set of i686 binary RPMs. These are *not* official PGDG RPMs, and I'm not an RPM expert by any means (so use at your own risk!), but I've posted them in case anyone is interested. I'll leave them up until Lamar gets time to create the official set. http://www.joeconway.com/ Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Croatian language file for 7.3
Darko Prenosil writes: OK, here it is. I do not know if this will help to use postgres some more here in Croatia, but ... I'll put this in the 7.3 branch, so it will be released when 7.3.1 comes out. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
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
Re: [HACKERS] Auto Vacuum Daemon (again...)
- Original Message - From: Shridhar Daithankar [EMAIL PROTECTED] To: Matthew T. O'Connor [EMAIL PROTECTED] Sent: Monday, December 02, 2002 11:12 AM Subject: Re: [HACKERS] Auto Vacuum Daemon (again...) On 28 Nov 2002 at 3:02, Matthew T. O'Connor wrote: I went thr. it today and I have some comments to make. 1. The idea of using single database is real great. I really liked that idea which keeps configuration simple. I'm no longer think this is a good idea. Tom Lane responded to our thread on the hacker list saying that it would never be a good idea to have more than one vacuum process running at a time, even on different databases as vacuum is typically io bound. Since never want to run more than one vacuum at a time, it is much simpler to have it all managed by one AVD, rather than one AVD for each database on a server. 2. You are fetching all the statistics in the list. This could get big if there are thousands of table or for a hosting companies where there are tons of databases. That is the reason I put a table in there.. Of course not that it won't work, but by putting a table I thought it cause some less code in the app. I don't see how putting a table in is any different than checking the view. First I don't like the idea of having to have tables in someones database, I find that intrusive. I know that some packages such as PGAdmin do this, and I never liked it as a developer. Second, the only reason that it would be less work for the server is that you may not have an entry in your table for all tables in the database. This can be accomplished through some type of exclusion list that could be part of the configuration system. I will hack in a add-on for parallel vacuums by tom. and send you. Just put a command line switch(never played with getopt). Basically,after list of database is read, fork a child that sleeps and vacuums only one database. See comments above. Besides I have couple of bugreports which I will check against your version as well.. Please let me know what you find, I know it's far from a polished piece of work yet :-) After a thorough look of code, I will come up with more of these but next time I will send you patched rather than comments.. I look forward to it. Also, I wanted to let you know that I am working on integrating it into the main Postgres source tree right now. From what I have heard on the hackers list it seems that they are hoping to have this be a core feature that they can depend on so that they can guarantee that databases are vacuumed every so often as required for 24x7 operation. Basically I will still have it as a separate executable, but the postmaster will take care of launching it with proper arguments, restarting it if it dies (much like the stats collector) and stop the AVD on shutdown. This should be fairly easy to do, I still don't know if others think this is a good idea, as I got to response to that part of my other email, but it is the best idea I have right now. Sorry for late reply. Still fighting with some *very* stupid bugs in my daytime jobs ( like 'if (k 60)' evaluating to false for k=0 in release version only etc..) Good luck with your work, I hope you find all the bugs quickly, Its not the fun part of coding. Thanks again for the feedback, I really want this feature in postgres. Matthew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: PostgreSQL in Universities (Was: Re: [HACKERS] 7.4 Wishlist)
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
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 /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s what is the time for select id,x from , (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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
Hannu Krosing [EMAIL PROTECTED] 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 /dev/null real0m0.694s user0m0.147s sys 0m0.025s # time psql genline -c select id,id||'/'||(select count(*) from ) as x from /dev/null real0m2.202s user0m0.263s sys 0m0.040s # time psql genline -c select id,(select count(*) from bildsekvens) as x from /dev/null real0m1.479s user0m0.254s sys 0m0.047s what is the time for select id,x from , (select count(*) as x from bildsekvens) c ; time psql genline -c select id,x from , (select count(*) as x from ) c ; /dev/null real0m1.354s user0m0.268s sys 0m0.028s The 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
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=enlr=ie=UTF-8oe=UTF-8threadm=3DB99C0A.70900%40mascari.comrnum=1prev=/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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Shrinkwrap Windows Product, any issues? Anyone?
I am working on getting a shrink-wrapped version of PostgreSQL for Windows Currently it installs a customized version of Cygwin, PostgreSQL 7.2.3, cygipc, psqlodbc, and pgadminII I currently have the setup done. The target audience is not the enterprise, it is aimed at people using Access wanting to upgrade. I've looked long and hard and can't find any license issues. Does anyone know of any that I may have missed? As far as I can see, as long as I maintain GPL restrictions, I should be fine. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] toast table growing indefinitely? Known
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Hang on - so when vacuum runs it knows exactly how many pages it cleaned up but if the fsm value is low, it doesn't help? Why don't we just automaticlaly set the FSM value to the max that vacuum has to clean up? Because we can't resize shared memory on-the-fly. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
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) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Magnus Naeslund(f) [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
Bruce Momjian [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] 7.4 Wishlist
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 x
WHERE x.date_part 20;
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (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
Re: [HACKERS] 7.4 Wishlist
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 foo20;
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.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
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...). :)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
