Re: [HACKERS] 7.4 Wishlist
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 [EMAIL PROTECTED] ---(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 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 [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])
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
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 [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
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 [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
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unofficial 7.3 RPMs
On Mon, 2002-12-02 at 23:59, Joe Conway wrote: 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/ Thanks, they seem to work fine. Except that I had to tweak the startup script - as distributed it defines the version to be 7.3b2 and checks database for version 7.2. It also expects/puts the database in nonstandard place. But otherways they saved me a lot of trouble ;) --- Hannu ---(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] [ADMIN] how to alter sequence.
Shridhar Daithankar kirjutas K, 04.12.2002 kell 20:51: On 4 Dec 2002 at 20:41, Hannu Krosing wrote: hannu=# update seq set max_value = 99; ERROR: You can't change sequence relation seq hannu=# update pg_class set relkind = 'r' where relname = 'seq'; UPDATE 1 hannu=# update seq set max_value = 99; UPDATE 1 hannu=# update pg_class set relkind = 'S' where relname = 'seq'; UPDATE 1 hannu=# select * from seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++--+---+---+-+-+---+--- seq | 1 |1 |99 | 1 | 1 | 1 | f | f I just discovered that changing these numbers does not change how the sequence behaves ;( Even after restarting the backend! Sorry! That makes me wonder. If sequense is treated like a single column single row table and it's value is guarenteed to be increasing even in case of aborted transaction, is it correct to say that postgresql already has nested transactions, albeit dormant? No. Sequences live outside of transactions. I have no idea why there is also a ingle column single row table created. The output of \d command is also weird, for all sequences I get: hannu=# \d seq Sequence public.seq Column | Type ---+- sequence_name | name last_value| bigint increment_by | bigint max_value | bigint min_value | bigint cache_value | bigint log_cnt | bigint is_cycled | boolean is_called | boolean with only the Sequence name changing ... --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] how to alter sequence.
Oliver Elphick kirjutas K, 04.12.2002 kell 19:06: On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Hackers: Could this be a TODO item for 7.4? This seems to work - as an example why we need the TODO ;) hannu=# update seq set max_value = 99; ERROR: You can't change sequence relation seq hannu=# update pg_class set relkind = 'r' where relname = 'seq'; UPDATE 1 hannu=# update seq set max_value = 99; UPDATE 1 hannu=# update pg_class set relkind = 'S' where relname = 'seq'; UPDATE 1 hannu=# select * from seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++--+---+---+-+-+---+--- seq | 1 |1 |99 | 1 | 1 | 1 | f | f (1 row) I can't really recommend it, because it may (or may not ;) have some unwanted behaviours as well; The easiest way to do this at present is probably to dump the database, edit the dump to change the sequence max_value and then recreate the database from the edited dump. I presume you used CREATE SEQUENCE in order to get such a low max_value. If it were created from a SERIAL datatype, you would also have to edit the table definition to use a pre-created sequence. There is no means of specifying a max_value using SERIAL. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Sequence Cleanup
Rod Taylor kirjutas T, 10.12.2002 kell 01:49:
Below is a short list of TODOs on sequences I wish to tackle over the
next week.
...
Ok, this is where it gets confusing. Right now setval() is implemented
in such a manner that it cannot be rolled back (see SETVAL NOTE below),
but I'd like ALTER SEQUENCE to be transaction safe.
All *val('seqname') functions are transaction-unsafe, i.e. live outside
transactions.
Why would you want alter transaction to be transaction safe ?
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql's \d commands --- end of the line for
Tom Lane kirjutas T, 10.12.2002 kell 02:05: [ moved to hackers from pgsql-patches ] Christopher Kings-Lynne [EMAIL PROTECTED] writes: Peter wrote: Christopher Kings-Lynne writes: \dc - list conversions [PATTERN] \dC - list casts What are we going to use for collations? \dn Is the only letter left in collations that hasn't been used! ... and that was already proposed for show schemas (namespaces). I'm inclined to think it's time to bite the bullet and go over to words rather than single characters to identify the \d target (viz, \dschema, \dcast, etc, presumably with unique abbreviations being allowed, as well as special cases for the historical single characters). The issue here is what do we do with the existing \d[istvS] behavior (for instance, \dsit means list sequences, indexes, and tables). Is that useful enough to try to preserve, or do we just bit-bucket it? If we do try to preserve it, how should it work? Why not use \D for long ids ? Somewhat similar to -? and --help for command line. -- 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] psql's \d commands --- end of the line for
On Mon, 2002-12-09 at 23:12, Philip Warner wrote: At 05:13 PM 9/12/2002 -0500, Tom Lane wrote: Seems like a fine idea to me. Ditto. \Dsomething works though.) Any objections out there? My only complaint here is being forced to use the 'shift' key on commands that will be common. On most european keyboards you alreday have to use AltGr to get to \ so using an extra shift is not too bad ;) -- Hannu Krosing [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])
Re: [HACKERS] Creating a zero-column table
Tom Lane kirjutas R, 13.12.2002 kell 02:08: I was bemused to notice that pg_dump is currently unable to dump the regression database. The domain regression test leaves an empty table (one with zero columns), which causes pg_dump to produce -- -- TOC entry 172 (OID 675837) -- Name: domnotnull; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE domnotnull ( ); This is rejected on restore: ERROR: DefineRelation: please inherit from a relation or define an attribute I believe that the table would be correctly restored if we simply removed that error check in DefineRelation. On the other hand, for ordinary hand-entered CREATE TABLE commands it seems like a useful error check. Should we remove this error check, thereby effectively making zero-column tables first-class citizens? I would vote for removing the check. I see no reason why one should not be able to define a zero-column table. While we cant currently do anything useful (except select oid :) with it now, it does not mean that it would not serve as a valid base table for inheritance hierarchies in future. I'm probably going to propose an implicit zero-column base table for all user defined tables (say any_table) so that one can get a list of all tuple ids in all tables by doing a simple select tableoid,oid from any_table. This will of course not be very useful for tables with no oids and where there is no index on oid. -- Hannu Krosing [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])
Re: [HACKERS] Big 7.4 items
On Fri, 2002-12-13 at 06:22, Bruce Momjian wrote: I wanted to outline some of the big items we are looking at for 7.4: Point-In-Time Recovery (PITR) J. R. Nield did a PITR patch late in 7.3 development, and Patrick MacDonald from Red Hat is working on merging it into CVS and adding any missing pieces. Patrick, do you have an ETA on that? How hard would it be to extend PITR for master-slave (hot backup) repliaction, which should then amount to continuously shipping logs to slave and doing nonstop PITR there :) It will never be usable for multi-master replication, but somehow it feels that for master-slave replication simple log replay would be most simple and robust solution. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: Duplicate oids!
On Fri, 2002-12-13 at 09:27, Steve King wrote: -Original Message- From: Steve King Sent: 12 December 2002 11:45 To: [EMAIL PROTECTED] Subject:Duplicate oids! Forgive me if this is a previous question but I cannot find any information on it in any of the mailing lists. I have a postgres database that contains a table with two identical records including the oid. What about ctid's, are they also the same ? Are the tuples on the same page ? It seems as though one insert statement (intending one record to be inserted) has caused two identical records to be inserted. The insert statement was done via the c++ library. Does anyone know anything about this? My info can be supplied if this is not a known problem! P.S. I am running Postgres 7.2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Information schema now available
Peter Eisentraut kirjutas L, 14.12.2002 kell 05:32: A basic version of the SQL information schema is now available in newly initdb'ed database installations. Could you also post it somewhere as a plain SQL script for 7.3 ? IMHO this should become the default way for \d, ODBC, JDBC, and other similar interfaces for getting at this information and making it available for 7.3 would give the implementors of those a head start. There's still a bunch of work to do to create all the views that the spec defines. I'm sure you will get more help if it is available as add-on for 7.3. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] using a function on result of subselect
I have the following problem I want to use using a function on result of subselect: I create the following function: hannu=# create or replace function pg_fields(pg_user) returns text as ' hannu'# tup = args[0] hannu'# return tup[usename] + : + str(tup[usesysid]) hannu'# ' LANGUAGE 'plpython'; CREATE FUNCTION And it runs fine straight on table/view: hannu=# select pg_fields(pg_user) from pg_user; pg_fields postgres:1 hannu:100 (2 rows) But I am unable to run it on a subselect, whatever I do: hannu=# hannu=# select pg_fields(pg_user) from (select * from pg_user) as pg_user; ERROR: Cannot pass result of sub-select or join pg_user to a function hannu=# hannu=# select pg_fields(pg_user) from (select pg_user from pg_user) as pg_user; ERROR: You can't use relation names alone in the target list, try relation.*. hannu=# select pg_fields(pg_user) from (select pg_user.* from pg_user) as pg_user; ERROR: Cannot pass result of sub-select or join pg_user to a function I there a way to: a) tell PostgreSQL that the funtion can take any row type as an argument or b) to cast the result of subquery to a known row type -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] python interface
Bruce Momjian kirjutas K, 18.12.2002 kell 00:10: I think the python interface in /interfaces/python should be moved to gborg. It already has its own web site: http://www.druid.net/pygresql/ and there is also another one, pyPgSQL, at: http://pypgsql.sourceforge.net/ And the active third one, psycopg at: http://initd.org/software/initd/psycopg And an old, seemingly abandoned one at: http://www.advogato.org/proj/python-postgresql/ But it would be nice if there were still some rpm's built by default for those, even from gborg. It would be good to get both of them listed in the gborg interfaces section. They don't need to move their web sites there. They can just provide a link to their main site from gborg. Also, other interface authors should consider adding a page for themselves on gborg too so there is one place people can look for PostgreSQL interfaces. I have CC'ed both python interface authors. -- Hannu Krosing [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])
Re: [HACKERS] Upgrading rant.
On Fri, 2003-01-03 at 13:45, mlw wrote: Tom Lane wrote: Personally, I feel that if we weren't working as hard as we could on features/performance/bugfixes, the upgrade issue would be moot because there wouldn't *be* any reason to upgrade. What about the standard Microsoft reason for upgrades - the bug fixes ;) So I'm not planning to redirect my priorities. But this is an open source project and every developer gets to set their own priorities. If you can persuade someone to put their time into that, go for it. Do not under estimate the upgrade issue. Very true! If upgrading is hard, users will surely expect us to keep maintaining all non-upgradable old versions for the foreseeable future ;( I think it is huge and a LOT of people have problems with it. Personally, I never understood why the dump/restore needed to happen in the first place. Can't the data and index file format be more rigidly defined and stuck too? I don't think the main issues are with file _formats_ but rather with system file structures - AFAIK it is a fundamental design decision (arguably a design flaw ;( ) that we use system tables straight from page cache via C structure pointers, even though there seems to be a layer called storage Manager which should hide the on-disk format completely. Can't there just be some BKI process to add new data entries? I had the same issues with 7.1 and 7.2, -- 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] MOVE LAST: why?
Tom Lane kirjutas N, 09.01.2003 kell 04:05: I said: Yeah, backwards scan is not implemented for quite a large number of plan node types :-(. I am not sure that it is practical to fix them all. I have been toying with the notion of making cursors on complex plans safe for FETCH BACKWARD by sticking a MATERIAL node atop the plan, How much work would it be do the MATERIAL node so that it is calculated at the time of initial forward scan (i.e. FETCH/MOVE) ? if the top plan node isn't one that can handle backwards scan. I forgot to mention plan B: make use of ReScan. This could work like so: 1. Cursor keeps track of row number (number of rows it's fetched). 2. To scan backwards when top plan type doesn't handle it, rewind all the way with ReScan, then move forward the appropriate number of rows. This would avoid any added overhead in the case where a backwards move is never requested, and it also would support MOVE BACKWARD ALL quite efficiently (much more so than now). On the other hand, it'd really suck if the user asks for backwards scan from a point far into the output. Perhaps we could do something with a hybrid technique: don't materialize the cursor output unless user actually asks for backwards scan. If he does, then create a tuplestore and put the data into it (rescanning the query output to do so), and finally supply the tuples from the tuplestore. How hard would it be to save snapshots of scan state at certain places, say at each 1000 tuples, so that a full re-scan is not neccessary ? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] \d type queries - why not views in system catalog?!?
Robert Treat kirjutas T, 14.01.2003 kell 01:50: One of the reasons that this was not feasible in the past was that we needed functions that could return multiple rows and columns easily. Now that we have that in 7.3, it might be worth revisiting. Also, we have schemas now, so it would be easier to avoid name clashes. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] \d type queries - why not views in system catalog?!?
On Tue, 2003-01-14 at 01:39, Christopher Kings-Lynne wrote: What about querying the information_schema? Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? -- 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] \d type queries - why not views in system catalog?!?
On Tue, 2003-01-14 at 15:47, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Will information_schema be strictly SQL99 or will it also have PostgreSQL specific views/fields ? If it's not strictly conformant to the spec, I see no value in it at all. I mean that it could have at least extra *views* for postgresql specific things. It could also have extra *fields* on standard views, but that might break some apps. I see no way how having extra views can break apps. We already have plenty of ability to query the catalogs via non-standard queries. But would it not be nice to have some standard ones, so that each and every DB management app does not need to invent its own ? I agree that this could be done as a project at gborg rather than in information_schema, but it would still be good to have one standard place for examples at least. And the only way to keep the examples up-to-date is by using them in live projects. -- 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] Foreign key wierdness
On Mon, 2003-01-20 at 15:47, Dave Page wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 15:28 To: Dave Page Cc: PostgreSQL Hackers Mailing List; Didier Moens Subject: Re: [HACKERS] Foreign key wierdness Dave Page [EMAIL PROTECTED] writes: A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only reason ADD FOREIGN KEY would take a long time is if (a) it has to wait awhile to get exclusive lock on either the referencing or referenced table; and/or (b) it takes a long time to verify that the existing entries in the referencing table all have matches in the referenced table. (that's the behind-the-scenes query you see) I'm betting that the table was busy, or there was a lot more data present in the one case, or you hadn't ever vacuumed/analyzed one or both tables and so a bad plan was chosen for the verification query. The schema reference is definitely not the issue. Thing is Tom, this issue can be reproduced *every* time, without fail. The difference is huge as well, it's a difference of a couple of seconds, the total migration will take around 1704.67 seconds without schema qualification, and 11125.99 with schema qualification to quote one test run. can you try running ANALYZE (or VACUUM ANALYZE) after importing data but before creating the foreign keys ? As I understand it, this has be tried on a test box, and a production box (running RedHat builds of 7.3.1), and is a migration of the same source Access database. I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. does this SELECT ... FOR UPDATE occur only when schemas are used ? These exactly follow *every* fkey creation, and are definately not issued by pgAdmin. If they were issued by another app or user, how come they exactly follow each fkey creation, and are on the reference table of the fkey? I think Tom was trying to tell that the backend code indeed runs this, but that it should not be that slow. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Windows Build System was: [HACKERS] Win32 port patches
On Wed, 2003-01-22 at 15:34, Curtis Faith wrote: tom lane writes: You think we should drive away our existing unix developers in the mere hope of attracting windows developers? Sorry, it isn't going to happen. Tom brings up a good point, that changes to support Windows should not add to the tasks of those who are doing the bulk of the work on Unixen. I don't think, however, that this necessarily means that having Windows developers use Cygwin is the right solution. We need to come up with a way to support Windows Visual C++ projects without adding work to the other developers. Does anyone know how MySQL and interbase/firebird do it ? POSSIBLE SOLUTIONS: The Visual C++ Workspaces and Projects files are actually text files that have a defined format. I don't think the format is published but it looks pretty easy to figure out. will probably change between releases (also I dont think you can easily compile C source on a C# compiler) ;/ -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Terrible performance on wide selects
Tom Lane kirjutas N, 23.01.2003 kell 02:18: Dann Corbit [EMAIL PROTECTED] writes: Why not waste a bit of memory and make the row buffer the maximum possible length? E.g. for varchar(2000) allocate 2000 characters + size element and point to the start of that thing. Surely you're not proposing that we store data on disk that way. The real issue here is avoiding overhead while extracting columns out of a stored tuple. We could perhaps use a different, less space-efficient format for temporary tuples in memory than we do on disk, but I don't think that will help a lot. The nature of O(N^2) bottlenecks is you have to kill them all --- for example, if we fix printtup and don't do anything with ExecEvalVar, we can't do more than double the speed of Steve's example, so it'll still be slow. So we must have a solution for the case where we are disassembling a stored tuple, anyway. I have been sitting here toying with a related idea, which is to use the heap_deformtuple code I suggested before to form an array of pointers to Datums in a specific tuple (we could probably use the TupleTableSlot mechanisms to manage the memory for these). Then subsequent accesses to individual columns would just need an array-index operation, not a nocachegetattr call. The trick with that would be that if only a few columns are needed out of a row, it might be a net loss to compute the Datum values for all columns. How could we avoid slowing that case down while making the wide-tuple case faster? make the pointer array incrementally for O(N) performance: i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus keep count of how many are actually valid, so the first call to get col[5] will fill first 5 positions in the array save said nr 5 and then access tuple[ptrarray[5]] next call to get col[75] will start form col[5] and fill up to col[75] next call to col[76] will start form col[75] and fill up to col[76] next call to col[60] will just get tuple[ptrarray[60]] the above description assumes 1-based non-C arrays ;) -- 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: [PERFORM] [HACKERS] Terrible performance on wide selects
Dann Corbit kirjutas N, 23.01.2003 kell 02:39: [snip] For the disk case, why not have the start of the record contain an array of offsets to the start of the data for each column? It would only be necessary to have a list for variable fields. So (for instance) if you have 12 variable fields, you would store 12 integers at the start of the record. You have to store this information anyway (for variable length objects). By storing it at the front of the record you would lose nothing (except the logical coupling of an object with its length). But I would think that it would not consume any additional storage. I don't think it will win much either (except for possible cache locality with really huge page sizes), as the problem is _not_ scanning over big strings finding their end marker, but instead is chasing long chains of pointers. There could be some merit in the idea of storing in the beginning of tuple all pointers starting with first varlen field (16 bit int should be enough) so people can minimize the overhead by moving fixlen fields to the beginning. once we have this setup, we no longer need the varlen fields /stored/ together with field data. this adds complexity of converting form (len,data) to ptr,...,data) when constructing the tuple as tuple (int,int,int,varchar,varchar) which is currently stored as (intdata1, intdata2, intdata3, (len4, vardata4), (len5,vardata5)) should be rewritten on storage to (ptr4,ptr5),(intdata1, intdata2, intdata3, vardata4,vardata5) but it seems to solve the O(N) problem quite nicely (and forces no storage growth for tuples with fixlen fields in the beginning of tuple) and we must also account for NULL fields in calculations . -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Options for growth
Curt Sampson kirjutas N, 23.01.2003 kell 17:42: If the OS can handle the scheduling (which, last I checked, Linux couldn't, When did you do your checking ? (just curious, not to start a flame war ;) at least not without patches), eight or sixteen CPUs will be fine. cjs -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Terrible performance on wide selects
Dann Corbit kirjutas N, 23.01.2003 kell 02:22: [snip] So (for instance) if you have 12 variable fields, you would store 12 integers at the start of the record. Additionally, you could implicitly size the integers from the properties of the column. A varchar(255) would only need an unsigned char to store the offset, but a varchar(8) would require an unsigned int. I guess that the pointer could always be 16-bit, as the offset inside a tuple will never be more (other issues constrain max page size to 32K) varchar(8) will use TOAST (another file) anyway, but this will be hidden inside the field storage in the page) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Foreign key wierdness
Tom Lane kirjutas K, 22.01.2003 kell 22:30:
Didier Moens [EMAIL PROTECTED] writes:
I did some extensive testing using PostgreSQL 7.3.1 (logs and results
available upon request), and the massive slowdown is NOT related to
qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the
following change :
pgAdminII 1.4.2 :
---
CREATE TABLE articles (
article_id integer DEFAULT
nextval('articles_article_id_key'::text) NOT NULL,
...
pgAdminII 1.4.12 :
CREATE TABLE articles (
article_id bigint DEFAULT nextval('articles_article_id_key'::text)
NOT NULL,
...
Ah-hah, and I'll bet that the column being linked to this one by the
foreign key constraint is still an integer?
This should at least give out a NOTICE or ABORT or generate a functional
index, not a plain one.
With two tables each containing some 20.000 entries, the fk creation
time between both of them increases from ~ 1.8 secs to ~ 221 secs.
Seems odd that the cost would get *that* much worse. Maybe we need to
look at whether the FK checking queries need to include explicit casts
...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE
Curt Sampson kirjutas N, 23.01.2003 kell 13:34: On Tue, 21 Jan 2003, Tom Lane wrote: We've gotten a couple of complaints now about the fact that 7.3 doesn't include an OID column in a table created via CREATE TABLE AS or SELECT INTO. Unless I hear objections, I'm going to revert it to including an OID, and back-patch the fix for 7.3.2 as well. I object. I personally think we should be moving towards not using OIDs as the default behaviour, inasmuch as we can, for several reasons: I re-object 1. It's not a relational concept. so are other system tuples (cid, tid, tableiod, ...). It is an OO concept. 2. The OID wraparound problem can get you. put an unique index on OID column. 3. Other SQL databases don't do this. Ask Date, hell tell you that SQL is evil, i.e. not relational ;) 4. It's hidden, rather than exposed, and hidden things are generally a bad idea. AFAIK carrying hidden weapons is forbidden in most of USA, in Europe you usually are forbidden to carry hand-weapons _exposed_ ;) 5. We should default to what gives us better performance, rather than worse. Not if it breaks anything ;) See discussion a couple days ago on pgsql-general, starting at http://archives.postgresql.org/pgsql-general/2003-01/msg00669.php There didn't seem to be many people clamouring to have it back. The ideal sitaution for me would be to have WITHOUT OIDS be the default for all table creations, and but of course allow WITH OIDS for backward compatability. But yeah, I know that this can introduce problems with old dumps, and may not be entirely easy to implement. If you need a no-OID table, and INSERT INTO it. cjs -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Terrible performance on wide selects
Tom Lane kirjutas N, 23.01.2003 kell 02:04: Dann Corbit [EMAIL PROTECTED] writes: Maybe I don't really understand the problem, but it seems simple enough to do it once for the whole query. We already do cache column offsets when they are fixed. The code that's the problem executes when there's a variable-width column in the table --- which means that all columns to its right are not at fixed offsets, and have to be scanned for separately in each tuple, AFAICS. Not only varlen columns, but also NULL columns forbid knowing the offsets beforehand. -- Hannu Krosing [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])
Re: [HACKERS] Terrible performance on wide selects
Hannu Krosing kirjutas N, 23.01.2003 kell 12:11: make the pointer array incrementally for O(N) performance: i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus keep count of how many are actually valid, Additionally, this should also make repeted determining of NULL fields faster - just put a NULL-pointer in and voila - no more bit-shifting and AND-ing to find out if the field is null. One has to watch the NULL bitmap on fist pass anyway. so the first call to get col[5] will fill first 5 positions in the array save said nr 5 and then access tuple[ptrarray[5]] next call to get col[75] will start form col[5] and fill up to col[75] next call to col[76] will start form col[75] and fill up to col[76] next call to col[60] will just get tuple[ptrarray[60]] the above description assumes 1-based non-C arrays ;) -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql source
[EMAIL PROTECTED] kirjutas N, 23.01.2003 kell 02:29: Can you please tell me how can I download all the source codes for postgresql?? ftp://ftp.postgresql.org/ -Radha Manohar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Win32 port patches submitted
Bruce Momjian kirjutas P, 26.01.2003 kell 05:07: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I don't see a strong reason not to stick with good old configure; make; make install. You're already requiring various Unix-like tools, so you might as well require the full shell environment. Indeed. I think the goal here is to have a port that *runs* in native Windows; but I see no reason not to require Cygwin for *building* it. Agreed. I don't mind Cygwin if we don't have licensing problems with distributing a Win32 binary that used Cygwin to build. I do have a problem with MKS toolkit, which is a commerical purchase. I would like to avoid reliance on that, though Jan said he needed their bash. IIRC mingw tools had win-native (cygwin-less) bash at http://sourceforge.net/projects/mingw/ -- 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] Recursive unions
Tom Lane kirjutas K, 29.01.2003 kell 17:58: Christopher Kings-Lynne [EMAIL PROTECTED] writes: What was the result of the recursive unions thread? I remember Tom maybe saying that the Redhat guys like the DB2 (SQL99) syntax the best, however was it said that that was going to be done by Redhat for 7.4? It'll be looked at; whether it will be done in time for 7.4 is anyone's guess. Is anyone actually working on it ? I had some work on it done in this direction for 7.2.x (yacc patches up to parse tree generation). If nobody is currently doing it, I would start pushing it by bringing my work to 7.4 and then doing small amounts of work and then bugging the list about what would be the best ways to continue, repeating it until it is done ;) -- 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: [mail] Re: [HACKERS] Windows Build System
On Thu, 2003-01-30 at 13:24, Vince Vielhaber wrote: On Thu, 30 Jan 2003, Dave Page wrote: On Wed, 29 Jan 2003, Ron Mayer wrote: Cool irony in the automated .sig on the mailinglist software... On Wed, 29 Jan 2003, Vince Vielhaber wrote: ... hammering the betas is a far cry from an industrial-strength solution. ... TIP 4: Don't 'kill -9' the postmaster Sounds like you're basically saying is _do_ 'kill -9' the postmaster... and make sure it recovers gracefully when testing for an industrial- strength solution. Not what I said at all. It's not far off, but it's quite amusing none the less. I agree with Tom on yanking the plug while it's operating. Do you know the difference between kill -9 and yanking the plug? Kill -9 seems to me _less_ severe than yanking the plug but much easier to automate, so that could be the first thing to test. You have no hope of passing the pull-the-plug test if you can't survive even kill -9. Perhaps we could have a special reliability-regression test that does kill -9 postmaster, repeatedly, at random intervals, and checks for consistency ? Maybe we will find even some options for some OS'es to force-unmount disks. I guess that setting IDE disk's to read-only with hdparm could possibly achieve something like that on Linux. What I read from your postings it that you are demanding more rigourous testing for a new major feature *prior* to it being comitted to CVS in a dev cycle than I think we ever gave any previous new feature even in the beta test phase. I don't object to testing, and have been thinking about coding something to address Tom's concerns, but let's demand heavy testing for the right reasons, not just to try to justify not doing a Win32 port. Nice try. I've demanded nothing, quit twisting my words to fit your argument. If you're going to test and call it conclusive, do some conclusive testing or call it something else. So we have no conclusive testing done that /proves/ postgres to be reliable ? I guess that such thing (positive conclusive reliability test) is impossible even in theory. But Dave has done some testing that could not prove the opposite and concluded that it is good enough for him. So I guess that his test were if fact conclusive, if only just for him ;) Sometimes it is very hard to do the pull-the-plug test - I've seen people pondering over a HP server they could not switch off after accidentally powering it up. Pulling the plug just made it beep, but did not switch it off ;) But I suspect that since you don't know the difference between yanking the plug and kill -9 this conversation is a waste of time. I assume you realize that U can't kill -9 the plug ;) -- 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: [mail] Re: [HACKERS] Windows Build System
On Thu, 2003-01-30 at 20:29, Tom Lane wrote:
Lamar Owen [EMAIL PROTECTED] writes:
While I understand (and agree with) your (and Vince's) reasoning on why
Windows should be considered less reliable, neither of you have provided a
sound technical basis for why we should not hold the other ports to the same
standards.
The point here is that Windows is virgin territory for us. We know
about Unix. When we port to a new Unix variant, we are dealing with the
same system APIs, and in many cases large chunks of the same system
code, that we've dealt with before. It's reasonable for us to have
confidence that Postgres will work the same on such a platform as it
does on other Unix variants. And the track record of reliability that
we have built up across a bunch of Unix variants gives us
cross-pollinating confidence in all of them.
Windows shares none of that heritage. It is the first truly new port,
onto a system without any Unix background, that we have ever done AFAIK.
I don't know how much Unix backgroun BeOS has. It does have a better
POSIX support than Win32, but I don't know how much of it is really from
Unix.
Claiming that it doesn't require an increased level of testing is
somewhere between ridiculous and irresponsible.
We should have at least _some_ platforms (besides Win32) that we could
clain to have run thorough test on.
I suspect that RedHat does some (perhaps even severe) testing for
RHAS/RHDB, but I don't know of any other thorough testing.
Or should reliability testing actually be something left for commercial
entities ?
I believe we should test every release as pathologically as Vince
has stated for Win32.
Great, go to it. That does not alter the fact that today, with our
existing port history, Windows has to be treated with extra suspicion.
I don't think that the pull-the-plug scenario happens enough in the wild
that even our seven-year track record can prove anything conlusive about
the reliability. I have not found instructions about providing that kind
of reliability in the docs either - things like what filesystems to use
on what OSes and with which mount options.
We just mention -f as a way to get non-reliable system ;)
I do not buy the argument you are making that we should treat all
platforms alike. If we had a ten-year-old Windows port, we could
consider it as stable as all our other ten-year-old Unix ports.
We don't. Given that we don't have infinite resources for testing,
it's simple rationality to put more testing emphasis on the places
that we suspect there will be problems. And if you don't suspect
there will be problems on Windows, you are being way too naive :-(
We don't have that old windows port, but I guess that there are native
windows ports at least a few years old.
Do we want to encourage Win32? (some obviously do, but I don't) Well, telling
people that we have tested PostgreSQL on Win32 much more thoroughly than on
Unix is in a way telling them that we think it is _better_ than the
time-tested Unix ports ('It passed a harder test on Win32. Are we afraid the
Unix ports won't pass those same tests?').
If it passes the tests, good for it. I honestly do not expect that it
will. My take on this is that we want to be able to document the
problems in advance, rather than be blindsided.
Where can I read such documentations for *nix ports ?
What I have read in this list is that losing different voltages in wrong
order can just write over any sectors on a disk, and that power-cycling
can blow up computers. I don't expect even Unix to survive that!
--
Hannu Krosing [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [mail] Re: [HACKERS] Windows Build System
On Thu, 2003-01-30 at 15:56, Tom Lane wrote: The reason the TIP is still there is that there are platforms on which that stuff doesn't work very nicely. It's better to let the postmaster exit cleanly so that that state gets cleaned up. I have no idea what the comparable issues are for a native Windows port, but I bet there are some... That's why I proposed an automated test for this too. It is mostly important when conquering new OS'es, but could also be nice to have when testing if changes to storage manager or some other important subsystem will break anything. regards, tom lane -- 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] Win32 port powerfail testing
Dave Page kirjutas R, 31.01.2003 kell 22:36: Despite some people's thoughts that a powerfail test is of little use, I going to spend some time doing one anyway because I think Tom's arguments for it are valid. I have lashed together the attached test program (the important bits are the setup, run and check functions) for review before I actually do anything next week. Comments, suggestions etc are welcome, though I don't have the time to write anything too complex, but do want to perform a valid test first time round if possible. I intend to run the tests on a Dual PIII 1GHz box, with 1Gb of Non-ECC RAM and a 20Gb (iirc) IDE disk. I will run on Windows 2000 Server with an NTFS filesystem, and again on Slackware Linux 8 with either ext3 or reiserfs (which is preferred?). I think that ext3 should be more reliable, or at least more mainstream - I have had bad experience with raiserfs not too long ago - a crash (similar to pull-the-plug) zeroed out completely unrelated files (files not recently written to, just read). As I don't use Slackware anymore (though I started usin linux on it in the dark ages before 1.0 kernel), I don't know if the issues are fixed there. The number of runs will be dictated by my workload next week, but I'd like to do at least 20 powerfails on each OS. Don't post if you happen to get better results for win32 ;) I have a worried lung-doctor (aka pulmonologist) friend who did some research/statistics on influence of smoking and he is desperate as his methodologically completely scientific studies ended up showing that smoking is healthy and not smoking is not ;), so he seems unable to publish any of his results in any respectable outlet ;( -- Hannu Krosing [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])
Re: [HACKERS] Win32 Powerfail testing - results
Dave Page kirjutas E, 03.02.2003 kell 18:51: Well the results are finally in. Hopefully we can concentrate on putting them right, rather than having a round of told you so's :-) I modified the test program slightly to improve the consistency checks. The updated version is attached. Regards, Dave. System == Gigabyte GA-6VTXD Motherboard Dual 1GHz PIII Processors 1Gb Non-ECC RAM Fujitsu MPG3240AH IDE Disk Drive Enhanced IDE Performance disabled in the BIOS. Test Test program run from a seperate machine. 20 Tests per OS. Powerfail randomly applied. Your hardware should also be able to run Postgres on BeOS http://www.bebits.com/app/2752 Being the only non-unix port before/besides win32, it could be an interesting excercise. You should be able to get and installable BeOS itself from SourceForge http://sourceforge.net/projects/crux/ Windows 2000 Testing Is this NTFS ? Any possibility of trying the same tests with SCSI disks ? Write back cache on IDE disk disabled. Clean installation of Windows 2000 Server with Service Pack 3 Run | Errors Detected = 01 | None 02 | None 03 | None 04 | None 05 | None 06 | None 07 | COUNT CHECK - Duplicate or missing rows detected (10262)!! 08 | None 09 | DISTINCT CHECK - Duplicate or missing rows detected (9893)!! I remember having problems with UNIQUE columns having duplicate values a few versions back on Linux-ext2-IDE. Could this be the same problem or must it be something completely different ? | COUNT CHECK - Duplicate or missing rows detected (9893)!! 10 | None 11 | None 12 | None 13 | None 14 | COUNT CHECK - Duplicate or missing rows detected (10024)!! 15 | None 16 | None 17 | None 18 | None 19 | None 20 | None Linux Testing = Clean installation of Slackware Linux 8.1 on ext3 Kernel 2.4.18 Run | Errors Detected = 01 | None ... 20 | None BTW, are the tests portable enough to run also on MSSQL, Oracle and DB2 ? I know that you can't publish exact results, but perhaps something like the GreatBridge results - the one that runs only on Win32 did so-and-so, the one that has 'i' at the end of version number this, and the one whose name consists of two letters and a number did that ? -- Hannu Krosing [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])
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
On Tue, 2003-02-04 at 16:59, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Given that this problem isn't a regression, I don't think we need to delay 7.3.2 to fix it (of course, a fix for 7.3.3 and 7.4 is essential, IMHO). No, I've had to abandon my original thought that it was a localized bug, so it's not going to be fixed in 7.3.2. The real problem is simply that we're up against design limitations of the existing regex package, which was never designed for wider-than-8-bit character sets. It's been rather crudely hacked while it was in our hands (Henry Spencer would probably disown the code if he saw it now ;-)) so that it sorta kinda does MULTIBYTE, but it's slow and I don't think it's complete either. I'm about to go off and look at whether we can absorb the Tcl regex package, which is Spencer's new baby. Why not PCRE ( http://pcre.sourceforge.net/ ) ? They claim at least utf-8 (I don't remember other multibyte charsets being mentioned) support and have a BSD-ish license, http://pcre.sourceforge.net/license.txt . -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
On Tue, 2003-02-04 at 18:21, Tom Lane wrote: 4. pcre looks like it's probably *not* as well suited to a multibyte environment. In particular, I doubt that its UTF8 compile option was even turned on for the performance comparison Neil cited --- and the man page only promises experimental, incomplete support for UTF-8 encoded strings. The Tcl code by contrast is used only in a multibyte environment, so that's the supported, optimized path. It doesn't even assume null-terminated strings (yay). If we are going into code-lifting business, we should also consider Pythons sre (a modified pcre, that works both on 8-bit and python's unicode (either 16 or 32 byte chars, depending on compile options)) It has no specific support for raw utf-8 or other variable-width encodings. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
Tom Lane kirjutas T, 04.02.2003 kell 21:18: Hannu Krosing [EMAIL PROTECTED] writes: If we are going into code-lifting business, we should also consider Pythons sre What advantages does it have to make it worth considering? Should be the same as pcre + support for wide chars. -- 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] POSIX regex performance bug in 7.3 Vs. 7.2
Tom Lane kirjutas K, 05.02.2003 kell 01:35: Neil Conway [EMAIL PROTECTED] writes: Speaking of which, is there (or should there be) some mechanism for increasing the size of the compiled pattern cache? Perhaps a GUC var? I thought about that while I was messing with the code, but I don't think there's much point in it, unless someone wants to invest the work to make the cache search much smarter (maybe a hash table?). At present a larger cache will cost you in extra search time, especially in the case where the pattern isn't in the cache. I did do the work last night to convert the cache management algorithm into a self-organizing list (a la Knuth) rather than a round-robin search as it was before. This should reduce the expected number of comparisons for cases where the cache is actually accomplishing something, but of course it's no help if you have too many patterns for the cache. Perhaps the decision weather to try to use the cache at all could be done at planning time depending on statistics information ? Another idea is to make special regex type and store the regexes pre-parsed (i.e. in some fast-load form) ? -- 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] Status report: regex replacement
Christopher Kings-Lynne kirjutas N, 06.02.2003 kell 03:56: set regex_flavor = advanced set regex_flavor = extended set regex_flavor = basic [snip] Any suggestions about the name of the parameter? Actually I think 'regex_flavor' sounds fine. Not more Americanisms in our config files!! :P Maybe support both, like for ANALYZE/ANALYSE ? While at it, could we make another variant - ANALÜÜSI - which would be native for me ;) -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] POSIX regex performance bug in 7.3 Vs. 7.2
Tom Lane kirjutas K, 05.02.2003 kell 08:12: Hannu Krosing [EMAIL PROTECTED] writes: Another idea is to make special regex type and store the regexes pre-parsed (i.e. in some fast-load form) ? Seems unlikely that going out to disk could beat just recompiling the regexp. We have to get _something_ from disk anyway. Currently we fetch regex source code, but if there were some format that is faster to load then that could be an option. -- Hannu Krosing [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])
Re: [HACKERS] Status report: regex replacement
Tatsuo Ishii kirjutas R, 07.02.2003 kell 04:03: UTF-8 seems to be the most popular, but even XML standard requires all compliant implementations to deal with at least both UTF-8 and UTF-16. I don't think PostgreSQL is going to natively support UTF-16. By natively, do you mean as backend storage format or as supported client encoding ? -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Status report: regex replacement
On Thu, 2003-02-06 at 13:25, Tatsuo Ishii wrote: I have just committed the latest version of Henry Spencer's regex package (lifted from Tcl 8.4.1) into CVS HEAD. This code is natively able to handle wide characters efficiently, and so it avoids the multibyte performance problems recently exhibited by Wade Klaver. I have not done extensive performance testing, but the new code seems at least as fast as the old, and much faster in some cases. I have tested the new regex with src/test/mb and it all passed. So the new code looks safe at least for EUC_CN, EUC_JP, EUC_KR, EUC_TW, MULE_INTERNAL, UNICODE, though the test does not include all possible regex patterns. Perhaps we should not call the encoding UNICODE but UTF8 (which it really is). UNICODE is a character set which has half a dozen official encodings and calling one of them UNICODE does not make things very clear. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Status report: regex replacement
Tatsuo Ishii kirjutas N, 06.02.2003 kell 17:05: Perhaps we should not call the encoding UNICODE but UTF8 (which it really is). UNICODE is a character set which has half a dozen official encodings and calling one of them UNICODE does not make things very clear. Right. Also we perhaps should call LATIN1 or ISO-8859-1 more precisely way since ISO-8859-1 can be encoded in either 7 bit or 8 bit(we use this). I don't know what it is called though. I don't think that calling 8-bit ISO-8859-1 ISO-8859-1 can confuse anybody, but UCS-2 (ISO-10646-1), UTF-8 and UTF-16 are all widely used. UTF-8 seems to be the most popular, but even XML standard requires all compliant implementations to deal with at least both UTF-8 and UTF-16. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Planning a change of representation in the planner
Tom Lane kirjutas R, 07.02.2003 kell 06:35: I've been thinking of doing this for a while just on efficiency grounds, but kept putting it off because I don't expect much of any performance gain on simple queries. (You need a dozen or so tables in a query before the inefficiencies of the list representation really start to hurt.) But tonight I'm thinking I'll do it anyway, because it'll also be impervious to duplicate-element bugs. Comments? Maybe the quicker way to avoid duplicate-element bugs (and get faster merges) is to keep the lists ordered, so instead of just appending the next int, you scan to the proper place and put it there (if it is not there already). Ordered lists are also much faster ( just O(N) ) to compare/union/intersect. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hash grouping, aggregates
Tom Lane kirjutas T, 11.02.2003 kell 18:39: Bruno Wolff III [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Greg Stark [EMAIL PROTECTED] writes: The neat thing is that hash aggregates would allow grouping on data types that have = operators but no useful operator. Hm. Right now I think that would barf on you, because the parser wants to find the '' operator to label the grouping column with, even if the planner later decides not to use it. It'd take some redesign of the query data structure (specifically SortClause/GroupClause) to avoid that. I think another issue is that for some = operators you still might not be able to use a hash. I would expect the discussion for hash joins in http://developer.postgresql.org/docs/postgres/xoper-optimization.html would to hash aggregates as well. Right, the = operator must be hashable or you're out of luck. But we could imagine tweaking the parser to allow GROUP BY if it finds a hashable = operator and no sort operator. The only objection I can see to this is that it means the planner *must* use hash aggregation, which might be a bad move if there are too many distinct groups. If we run out of sort memory, we can always bail out later, preferrably with a descriptive error message. It is not as elegant as erring out at parse (or even plan/optimise) time, but the result is /almost/ the same. Relying on hash aggregation will become essential if we are ever going to implement the other groupings (CUBE, ROLLUP, (), ...), so it would be nice if hash aggregation could also overflow to disk - I suspect that this will still be faster that running an independent scan for each GROUP BY grouping and merging the results. - Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.2 - 7.3 incompatibility
On Mon, 2003-02-10 at 19:13, Peter Eisentraut wrote: Christopher Kings-Lynne writes: I found an example of a casting problem in our source code now that we're running 7.3: SELECT CURRENT_DATE - EXTRACT(DOW FROM CURRENT_DATE); A mathematically sound way to write this would be: select current_date - extract(dow from current_data) * interval '1 day'; I'm not sure really why DOW needs to be double precision, but hey... Extract returns double precision. It can't morph itself based on the argument type at run time. Is this mandates by ANSI ? PostgreSQL _does_ select function based on argument type hannu=# create function f(int) returns int as 'select 1' language 'sql'; CREATE hannu=# create function f(int,int) returns float as 'select 3.1415927' language 'sql'; CREATE hannu=# select f(1),f(1,1); f | f ---+--- 1 | 3.1415927 (1 row) -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Brain dump: btree collapsing
Tom Lane kirjutas N, 13.02.2003 kell 20:10: Curtis Faith [EMAIL PROTECTED] writes: I don't dispute their conclusions in that context and under the circumstances they outline of random distribution of deletion and insertion values for the index keys. [But the random-distribution assumption doesn't always hold.] That's a fair point. Nonetheless, we have little choice: we cannot move keys around during concurrent operations. If we push keys to the right, we may cause an indexscan moving left to miss them, and vice versa. So we can only eliminate empty pages. But if we would allow the scans to find the same keys twice without ill effects (as was suggested earlier, for using btrees to index arrays), then we could possibly 1) copy the key to the right 2) wait for all right-to-left scans that have fallen between old and new values to pass and only then 3) delete the old left key. This could solve the concurrency issue as well. We could possibly allow VACUUM FULL to collapse partly-full pages, since in that case we know there are no concurrent scans. 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 ---(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] Brain dump: btree collapsing
Tom Lane kirjutas R, 14.02.2003 kell 01:13: Hannu Krosing [EMAIL PROTECTED] writes: But if we would allow the scans to find the same keys twice without ill effects (as was suggested earlier, for using btrees to index arrays), How is returning the same data twice not an ill effect? From earlier discussions I understood that there had been some work done on using btrees for indexing arrays by storing each separate element in a löeaf node. Surely that work must deal with not returning the same tuple twice. then we could possibly 1) copy the key to the right 2) wait for all right-to-left scans that have fallen between old and new values to pass and only then 3) delete the old left key. How will you wait for scans that you know nothing of to go past? Especially when they are going to be blocked by your own write lock on the left page? could we just not lock (for more than just to ensure atomic writes) the page but instead increment a page version on each write to detect changes? Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] postgresql and oracle, compatibility assessment
Martin Matusiak kirjutas E, 17.02.2003 kell 16:53: Greetings, I am doing a project for college developing a java system utilizing a RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering exactly how impossible would it be to make it compatible with both. Postgre is said to be completely ANSI SQL complaint, PostgreSQL is *much* more ANSI comliant than Oracle. is it feasible to imagine one could run dumps from Postgre into Oracle and vice versa? Not all data types are named the same (Oracles varchar is varchar2, etc.) Alternatively, run separate queries on both to set the structure and relations, then transfer only data? Much more likely to succeed. You may be interested in OpenACS ( http://openacs.org/ ) project, which runs on both Oracle and PostgreSQL. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Detecting corrupted pages earlier
Tom Lane kirjutas T, 18.02.2003 kell 17:21: Kevin Brown [EMAIL PROTECTED] writes: Tom Lane wrote: The cases I've been able to study look like the header and a lot of the following page data have been overwritten with garbage --- when it made any sense at all, it looked like the contents of non-Postgres files (eg, plain text), which is why I mentioned the possibility of disks writing data to the wrong sector. That also sounds suspiciously like the behavior of certain filesystems (Reiserfs, for one) after a crash when the filesystem prior to the crash was highly active with writes. I was bitten by it about a year ago as well. Isn't reiserfs supposed to be more crash-resistant than ext2, rather than less so? It's supposed to be, but when it is run in (default?) metadata-only-logging mode, then you can well get perfectly good metadata with unallocated (zero-filled) data pages. There had been some more severe errors as well. - Hannu ---(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] request for sql3 compliance for the update command
Tom Lane kirjutas K, 19.02.2003 kell 21:12: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: Agreed folks are going to have bigger problems from Informix than just this, and in fact I used Informix for years and didn't know they allowed this. However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. I think it makes sense to add it only if it adds functionality. It makes it easier (less keystrokes) to write as well as similar in appearance to INSERT, so the same code can be used to generate the queries. If we were at adding functionality then IMHO making VALUES(x,y,z) a proper rowsource would be a more worthy effort. --- Hannu ---(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] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: Agreed folks are going to have bigger problems from Informix than just this, and in fact I used Informix for years and didn't know they allowed this. However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. hannu=# create table target (id serial, a int, b int, c int); NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for SERIAL column 'target.id' CREATE TABLE hannu=# insert into target(a,b,c) values (0,0,0); INSERT 16983 1 hannu=# insert into target(a,b,c) values (1,1,1); INSERT 16984 1 hannu=# update target set hannu-# a = source.a1, b=source.a2, c=source.a3 hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source hannu-# where id = 1 hannu-# ; UPDATE 1 hannu=# select * from target; id | a | b | c +---+---+--- 2 | 1 | 1 | 1 1 | 1 | 2 | 3 (2 rows) hannu=# -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hard problem with concurrency
Peter Eisentraut kirjutas T, 18.02.2003 kell 21:02: Christopher Kings-Lynne writes: REPLACE INTO anyone? ;) The upcoming SQL 200x standard includes a MERGE command that appears to fulfill that purpose. Where is this upcoming standard available on net ? Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
Tom Lane kirjutas E, 24.02.2003 kell 19:30: Peter Eisentraut [EMAIL PROTECTED] writes: Hey, I don't want to take your ILIKE away. But at the time it was added the claim was that it was for compatibility and now we learn that that was wrong. This _is_ a compatibility feature, just not as straightforward as you may think, i.e. some databases have LIKE which behaves like our ILIKE. That is something to make people aware of, for example in the documentation. It already does say : The keyword ILIKE can be used instead of LIKE to make the match case : insensitive according to the active locale. This is not in the SQL : standard but is a PostgreSQL extension. What else would you want to say? Perhaps add (From the mail of Josh Berkus): 3) It's an easy search-and-replace operator for porting applications from SQL databases which automatically do case-insensitive comparisons using LIKE, such as MySQL and some installations of MSSQL. --- Hannu ---(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 datataypes as seperate library
Michael Meskes kirjutas K, 26.02.2003 kell 13:00: Did anyone ever think about creating a library that is able to handle our numeric datatype? I'm currently thinking about adding this datatype among others to the ones know to ecpg so no one is forced to convert them or work on the strings. On the other hand I'm not sure if anyone's interested in this feature as you could always keep the numbers as strings and let the database do all calculation stuff. But then you cannot use the datatype in C. I see at least 3 datatypes that would be nice to have libraries for using in client programs - NUMERIC, VARBIT and our DATE/TIME/TIMESTAMP. --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Best setup for RAM drive
Chris Sutton kirjutas T, 04.03.2003 kell 17:03: Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 Dual PIII 800 RAID 5 SCSI disks Platypus 8GB PCI QikDrive (the RAM drive). http://www.platypus.net The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. Currently the disk size of our database is 3.2GB, so we put the whole pgsql directory on the RAM drive. Current preformance is very snappy with the bottleneck being the CPUs. The concern of course is if something happends to the RAM drive we are S.O.L. and have to go to the last backup (pg_dump happens each night). The other concern is if the disk size of the database grows past or near 8gb, we would either have to get a bigger RAM drive or somehow split things betten SCSI and RAM drive. I don't quite grasp the full inner workings of Postgresql, but for those of you who obviously do, is there a better way of setting things up where you could still use the RAM drive for portions of the pgsql directory structure while putting the rest on disk where it's safer? Should we just put pgsql/data/pg_xlog on the RAM drive? Also, in the very near future we will be upgrading to another server, pg7.3.2 with dual P4 2.4 xenon's. The RAM drive will go into this new server. Any suggestions? The most obvious suggestion is to put the WAL files on RAM disk - these are the things that would most directly affect _write_ performance as these are the only ones the *must* hit the disk befor the transaction can be committed. If you are after read performance, then you just ;) have to determine which are the most frequently used files that are not reasonably cached . --- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Best setup for RAM drive
Hannu Krosing kirjutas T, 04.03.2003 kell 22:57: Chris Sutton kirjutas T, 04.03.2003 kell 17:03: Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 Dual PIII 800 RAID 5 SCSI disks Platypus 8GB PCI QikDrive (the RAM drive). http://www.platypus.net The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. ... Any suggestions? One more suggestion - you should surely tweak configuration parameters - for example ramdom read will no more be more expensive than sequential read. I'm not sure if postgresql will automatically compensate for possible reading of a data page several times when doing index scans so the above suggestion may not be entirely true. Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Best setup for RAM drive
mlw kirjutas K, 05.03.2003 kell 22:05: The idea of a RAM disk based database and reliable storage are in complete opposition. Forget it. I read from his post that the Platypus RAM disk _is_ his reliable storage, just with some peculiar characteristics, like big transfer speeds and uniform super-fast seeks. The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL99 ARRAY support proposal
Joe Conway kirjutas E, 10.03.2003 kell 05:35: CREATE OR REPLACE FUNCTION array_push (anyarray, anyscalar) RETURNS anyarray AS '$libdir/plr','array_push' LANGUAGE 'C'; could you make it RETURNS typeof($1) ? -- Hannu ---(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] Cursors and backwards scans and SCROLL
Tom Lane kirjutas P, 09.03.2003 kell 22:35: However, this is going to create backwards-compatibility issues. We have a few options for what to do: 1. Enforce the SQL spec requirement: error out if backwards fetch is done when SCROLL wasn't given. But this will surely break a lot of existing applications that work perfectly well. We could start by issuing a NOTICE/WARNING as a step towards the strict compliance and provide a GUC variable for those who are unable to cope even with the warning. The warning could be issued at two places - 1. ( more annoying ) issue it at cursor creation time when a plan is chosen that can't be fetched backwards. 2. like your #2, but just give a warning and then run the underlying query _again_, this toime with materialize on top and also do a Move to reposition the cursor. This will probably not work correctly for all tranasaction isolation levels though but it will penalize only these cases that absolutely need it. The penalty will of course be heavier ;( - Hannu ---(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] [INTERFACES] Roadmap for FE/BE protocol redesign
Greg Stark kirjutas K, 12.03.2003 kell 07:10: Marc G. Fournier [EMAIL PROTECTED] writes: Personally ... as long as a v8.x client can talk to a v7.x backend, you have my vote ... I'm more apt to upgrade my clients before my servers anyway ... Surely that's not true for a production environment. You have one database but potentially dozens of various programs around that access it. The main application, some backend scripts for batch jobs, your backup process, your monitoring systems... Not all of these are necessarily on the same machine. For more radical protocol changes a viable approach could be protocol proxies, i.e. set up a _separate_ daemon which listens on a separate port and translates v7.x wire protocol to v8.x of the database proper. Then those needing it can keep it around and those who need it not don't get the overhead. It could also be maintained by inerested parties long after being dropped by core developers. It's upgrading the database that's likely to be the driving motivation for new sql or storage features. People usually don't get excited about upgrading the client libraries :) But our SQL itself is slowly drifting towards ANSI/ISO compliance and that has often brought subtle changes that break _applications_. It is not a big issue to changes libraries if you have to change the application anyway. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Tom Lane kirjutas K, 12.03.2003 kell 18:19: Barry Lind [EMAIL PROTECTED] writes: One addition I would personally like to see (it comes up in my apps code) is the ability to detect wheather the server is big endian or little endian. When using binary cursors this is necessary in order to read int data. Actually, my hope is to eliminate that business entirely by standardizing the on-the-wire representation for binary data; note the reference to send/receive routines in the original message. For integer data this is simple enough: network byte order will be it. I'm not sure yet what to do about float data. Use IEEE floats or just report the representation in startup packet. the X11 protocol does this for all data, even integers - the client expresses a wish what it wants and the server tells it what it gets (so two intel boxes need not to convert to network byte order at both ends). 2) Better support for domains. Currently the jdbc driver is broken with regards to domains (although no one has reported this yet). The driver will treat a datatype that is a domain as an unknown/unsupported datatype. It would be great if the T response included the 'base' datatype for a domain attribute so that the driver would know what parsing routines to call to convert to/from the text representation the backend expects. I'm unconvinced that we need do this in the protocol, as opposed to letting the client figure it out with metadata inquiries. If we should, I'd be inclined to just replace the typeid field with the base typeid, and not mention the domain to the frontend at all. Comments? So I would request the ability of the client to set a max rows parameter for query results. If a query were to return more than the max number of rows, the client would be given a handle (essentially a cursor name) that it could use to fetch additional sets of rows. How about simply erroring out if the query returns more than X rows? Or just using prepare/execute - fetch - fetch - fetch ... 4) Protocol level support of PREPARE. In jdbc and most other interfaces, there is support for parameterized SQL. If you want to take advantage of the performance benefits of reusing parsed plans you have to use the PREPARE SQL statement. This argument seems self-contradictory to me. There is no such benefit unless you're going to re-use the statement many times. Nor do I see how pushing PREPARE down to the protocol level will create any improvement in its performance. I suspect that he actually means support for binary transmission of parameters for a previously-prepared statement here. So what I would like to see is the ability for the client to set a MAX VALUE size parameter. The server would send up to this amount of data for any column. If the value was longer than MAX VALUE, the server would respond with a handle that the client could use to get the rest of the value (in chunks of MAX VALUE) if it wanted to. I don't think I want to embed this in the protocol, either; especially not when we don't have even the beginnings of backend support for it. I think such a feature should be implemented and proven as callable functions first, and then we could think about pushing it down into the protocol. IIRC, Oracle has such a feature in its support for Large Objects (LONG datatype). If the object data is longer than xxx bytes you will need special ized access to it. also when stepping with single fetches, you will always get handles for LONG objects, if fetching more than one row you'll get raw data. BTW, I'm not advocating such behaviour . Hannu ---(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] SQL99 ARRAY support proposal
Tom Lane kirjutas N, 13.03.2003 kell 19:12: Peter Eisentraut [EMAIL PROTECTED] writes: OK, let's look at these more closely: array_push(anyarray, anyelement) returns anyarray The standard spelling for that appears to be somearray || ARRAY[element] which also has the nice property that it is commutative. Sure ... but that just means that || is the operator name for the underlying array_push function. We still need a way to declare this operation as a function. I think he mant that you just need to conacat for too arrays, no need for single-element push/append. OTOH a separate push may be more efficient contrib/intarray has the following functions (note that they use + for || above) OPERATIONS: int[] int[] - overlap - returns TRUE if arrays has at least one common elements. int[] @ int[] - contains - returns TRUE if left array contains right array int[] ~ int[] - contained - returns TRUE if left array is contained in right array # int[] - return the number of elements in array int[] + int - push element to array ( add to end of array) int[] + int[] - merge of arrays (right array added to the end of left one) int[] - int - remove entries matched by right argument from array int[] - int[] - remove right array from left int[] | int - returns intarray - union of arguments int[] | int[] - returns intarray as a union of two arrays int[] int[] - returns intersection of arrays int[] @@ query_int - returns TRUE if array satisfies query (like '1(2|3)') query_int ~~ int[] - -/- - Hannu ---(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] Roadmap for FE/BE protocol redesign
Hiroshi Inoue kirjutas N, 13.03.2003 kell 12:03: Dave Page wrote: Does looking up by the catalog keys take no cost ? Obviously there is cost, but doing a lookup only on demand, has got to be cheaper in the long run than including the entire column definition in the message whether it's wanted or not? So if there are 100 fields, should we ask the backend the column name 100 times ? or once for all columns if you prefer using IN. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Tom Lane kirjutas R, 14.03.2003 kell 19:15: Greg Stark [EMAIL PROTECTED] writes: So, just to throw out a wild idea: If you're talking about making large changes to the on-the-wire protocol. Have you considered using an existing database protocol? Yeah, I have. Didn't look promising --- there's no percentage unless we're 100% compatible, which creates a lot of problems (eg, can't ship type OIDs to frontend anymore). Surely there is a way to ship type info, even for UDT's (user defined types) as nowadays most big databases support those. What I actually looked into was RDA, but I doubt that TDS would be any closer to our needs... I remember someone started cleaning up IO in order to move it into a separate module with the aim of making multiple implementations (RDA, TDS, XML, native JDBC wire protocol if it ever becomes a reality, etc.) possible. While not exactly pertinent to new wire protocol this effort if completed would make it much easier to have backwards compatibility on the wire level. Hannu PS. Another feature I'd like is individually turning off warnings and notices. Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas E, 17.03.2003 kell 20:49: With no one replying on how to do correlated subqueries in FROM for UPDATE, Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug, so the way to do correlated subqueries in FROM for UPDATE would be to fix this bug ;) All common sense tells me that if I can update set col1=col2 and *not* get the value from the first col2 to all col1's then the same should be true for this hannu=# creatre table updtarget( hannu(# id int, val text); ERROR: parser: parse error at or near creatre at character 1 hannu=# create table updtarget(id int, val text); CREATE TABLE hannu=# create table updsource(id int, val text); CREATE TABLE hannu=# insert into updtarget(id) values (1); INSERT 16995 1 hannu=# insert into updtarget(id) values (2); INSERT 16996 1 hannu=# insert into updsource(id,val) values (1,'one'); INSERT 16997 1 hannu=# insert into updsource(id,val) values (2,'two'); INSERT 16998 1 hannu=# update updtarget set val = src.val hannu-# from (select s.val from updsource s hannu-#where s.id=updtarget.id) as src hannu-# ; NOTICE: Adding missing FROM-clause entry in subquery for table updtarget UPDATE 2 hannu=# select * from updtarget; id | val +- 1 | one 2 | one (2 rows) there should be no need to add missing FROM-clause entry and the result *should* be: hannu=# select * from updtarget; id | val +- 1 | one 2 | two (2 rows) Hannu ---(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] request for sql3 compliance for the update command
Tom Lane kirjutas K, 19.03.2003 kell 16:46: Bruce Momjian [EMAIL PROTECTED] writes: I wasn't sure it made logical sense to allow correlated subqueries in FROM because the FROM is processed before the WHERE. It doesn't; in fact it violates the whole semantic model of SQL, as far as I can see. Sub-selects in FROM are (in principle) evaluated separately and then joined. They can't have cross-references. Makes sense. What I was describing would have been akin to updatable queries where you first do all the joining and then update one of the underlying tables. the more accurate (nonstandard) syntax could have been SELECT src.val, tgt.val FROM updatesrc as src FOR UPDATE, updatetgd as tgt WHERE src.id = tgt.id SET src.val = tgt.val ; I think there is some weird construct in SQL99 that alters this behavior, though. You probably mean WITH, which acts like FROM but has lexically previous (or all in case of WITH RECURSIVE) sub-selects in its namespace. Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Fwd: AW: More UB-Tree patent information]
On Wed, 2002-04-10 at 21:55, Justin Clift wrote: Hannu Krosing wrote: On Wed, 2002-04-10 at 16:32, Justin Clift wrote: Hi everyone, This is Prof. Bayer's response to the question is it alright to use UB-Tree's in Open Source projects?. Have you found out _what_ exaclty is patented ? Is it just his concrete implementation of UB-Tree or something broader, like using one multi-dimensional index instead of multiple one-dimensional ones ? Is there any way of finding out instead of asking him directly? Maybe the patent places have online info? I did a quick search at USPTO at http://patft.uspto.gov/netahtml/search-bool.html on UB and Tree and index and database and found among other things a US patent no. 5,826,253 on mechanism very similar to LISTEN/NOTIFY, afforded to Borland on October 20, 1998 based on application from April 19, 1996. We should be safe as already Postgres95 had them ;) when I searched for UB and Tree and index and database and Bayer 0 results came back. when I omitted UB and searched for Tree and index and database and Bayer I got 27 results, first of them on Method and composition for improving sexual fitness ;) the one possibly related related to our Bayer was nr 6,219,662 on Supporting database indexes based on a generalized B-tree index which had reference to : Rudolf Bayer, The Universal B-Tree for Multidimensional Indexing: General Concepts, Worldwide Computing and Its Applications, International Conference, WWCA '97, Tsukuba, Japan, (Mar. 1997), pp. 198-209. and German patent 0 650 131 A1 which may be also relevant -- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3 schedule
On Thu, 2002-04-11 at 18:14, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: On the other hand, there are already a few reasons to make some changes to the FE/BE protocol (NOTIFY messages, transaction state, and now possibly PREPARE/EXECUTE -- anything else?). Passing EXECUTE parameters without having them go through the parser could possibly be done without a protocol change: use the 'fast path' function-call code to pass binary parameters to a function that is otherwise equivalent to EXECUTE. On the other hand, the 'fast path' protocol itself is pretty horribly misdesigned, and I'm not sure I want to encourage more use of it until we can get it cleaned up (see the comments in backend/tcop/fastpath.c). Aside from lack of robustness, I'm not sure it can work at all for functions that don't have prespecified types and numbers of parameters. The FE/BE COPY protocol is also horrible. So yeah, there are a bunch of things we *could* fix if we were ready to take on a protocol change. Also _universal_ binary on-wire representation for types would be a good thing. There already are slots in pg_type for functions to do that. By doing so we could also avoid parsing text representations of field data. My own thought is this might be better held for 7.4, though. We are already going to be causing application programmers a lot of pain with the schema changes and ensuing system-catalog revisions. That might be enough on their plates for this cycle. In any case, for the moment I think it's fine to be working on PREPARE/EXECUTE support at the SQL level. We can worry about adding a parser bypass for EXECUTE parameters later. IIRC someone started work on modularising the network-related parts with a goal of supporting DRDA (DB2 protocol) and others in future. - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3 schedule
On Thu, 2002-04-11 at 22:48, Tom Lane wrote: Barry Lind [EMAIL PROTECTED] writes: ... Since we don't currently provide any information to the user on the relative cost of the parse, plan and execute phases, the end user is going to be guessing IMHO. You can in fact get that information fairly easily; set show_parser_stats, show_planner_stats, and show_executor_stats to 1 and then look in the postmaster log for the results. One thing that seems to be missing is backend ids for query stats - if I set log_timestamp = true log_pid = true then I get pid for query but _not_ for stats If I have many long-running queries then it is impossible to know which stats are for which query ;( Hannu ---(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.3 schedule
On Fri, 2002-04-12 at 03:04, Brian Bruns wrote: On 11 Apr 2002, Hannu Krosing wrote: IIRC someone started work on modularising the network-related parts with a goal of supporting DRDA (DB2 protocol) and others in future. That was me, although I've been bogged down lately, and haven't been able to get back to it. Has any of your modularisation work got into CVS yet ? DRDA, btw, is not just a DB2 protocol but an opengroup spec that hopefully will someday be *the* standard on the wire database protocol. DRDA handles prepare/execute and is completely binary in representation, among other advantages. What about extensibility - is there some predefined way of adding new types ? Also, does it handle NOTIFY ? Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
On Sat, 2002-04-13 at 17:29, Tom Lane wrote: [ way past time to change the title of this thread ] Christopher Kings-Lynne [EMAIL PROTECTED] writes: OK, sounds fair. However, is there a more aggressive way of reclaiming the space? The problem with updating all the rows to null for that column is that the on-disk size is doubled anyway, right? So, could a VACUUM FULL process do the nulling for us? Vacuum works outside of normal transaction constraints anyway...? No, VACUUM has the same transactional constraints as everyone else (unless you'd like a crash during VACUUM to trash your table...) But can't it do the SET TO NULL thing if it knows that the transaction that dropped the column has committed. This could probably even be done in the light version of vacuum with a special flag (VACUUM RECLAIM). Of course running this this makes sense only if the dropped column had some significant amount of data . I do not think that we necessarily need to provide a special mechanism for this at all. The docs for DROP COLUMN could simply explain that the DROP itself doesn't reclaim the space, but that the space will be reclaimed over time as extant rows are updated or deleted. If you want to hurry the process along you could do UPDATE table SET othercol = othercol VACUUM FULL If only we could do it in namageable chunks: FOR i IN 0 TO (size(table)/chunk) DO UPDATE table SET othercol = othercol OFFSET i*chunk LIMIT chunk VACUUM FULL; END FOR; or even better - VACUUM FULL OFFSET i*chunk LIMIT chunk and then make chunk == 1 :) -- Hannu ---(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] PostgreSQL 7.2.1-2PGDG RPMs available for
On Sun, 2002-04-14 at 08:48, Lamar Owen wrote: Incidentally, the 7.2.93 (skipjack) public beta is a serious improvement over RHL 7.2, and I personally recommend it, as KDE 3 is worth the upgrade, even to a beta. Is the 7.2.93 (skipjack) public beta an improvement in raw postgresql performance or just in added stuff like KDE ? Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] [SQL] 16 parameter limit
On Tue, 2002-04-16 at 07:01, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: How about this: We store the first 16 parameters in some fixed array for fast access like now, and when you have more than 16 then 17 and beyond get stored in some variable array in pg_proc. itch What's this going to cost us in the function lookup code paths? If we can do it with little or no performance cost (at least for the normal case of fewer-than-N parameters) then I'm all ears. Perhaps we could use the 16-th element as an indicator of 16-or-more args. If it is 0 then there are = 15 args if it is something else, then this something else is hash of extra argument types that need to be looked up separately. Of course we will need some way of resolving multiple hash matches. -- Hannu ---(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] multibyte support by default
On Tue, 2002-04-16 at 03:20, Tatsuo Ishii wrote: In my understanding, our consensus was enabling multibyte support by default for 7.3. Any objection? Is there currently some agreed plan for introducing standard NCHAR/NVARCHAR types. What does ISO/ANSI say about multybyteness of simple CHAR types ? -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
On Wed, 2002-04-17 at 22:43, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: OTOH, it is also important where the file is on disk. As seen from disk speed test graphs on http://www.tomshardware.com , the speed difference of sequential reads is 1.5 to 2.5 between inner and outer tracks. True. But if we use the same test file for both the sequential and random-access timings, hopefully the absolute speed of access will cancel out. (Again, it's the sort of thing that could use some real-world testing...) What I was trying to say was thet if you test on one end you will get wrong data for the other end of the same disk. (The PG planner does try to account for caching effects, but that's a separate estimate; Will it make the random and seq read cost equal when cache size database size and enough queries are performed to assume that all data is in cache. There isn't any attempt to account for the effects of data having been read into cache by previous queries. I doubt that it would improve the model to try to keep track of what the recent queries were Perhaps some simple thing, like number of pages read * cache size / database size Or perhaps use some additional bookkeeping in cache logic, perhaps even on per-table basis. If this can be made to use the same locks ás cache loading/invalidation it may be quite cheap. It may even exist in some weird way already inside the LRU mechanism. --- for one thing, do you really want your plans changing on the basis of activity of other backends? If I want the best plans then yes. The other backends do affect performance so the best plan would be to account for their activities. If other backend is swapping like crazy the best plan may even be to wait for it to finish before proceeding :) Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] syslog support by default
On Fri, 2002-04-19 at 05:28, Peter Eisentraut wrote: Tom Lane writes: Tatsuo Ishii [EMAIL PROTECTED] writes: Can we enable syslog support by default for 7.3? AFAIR, we agreed to flip the default some time ago, we just didn't want to do it late in the 7.2 cycle. Go for it. I think if no one complains about the lack of syslog on his machine we should just remove the option in 7.3+1. My experience has been that logging to syslog makes postgres much slower. Can anyone confirm or refute this ? -- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] syslog support by default
On Fri, 2002-04-19 at 08:15, Tatsuo Ishii wrote: Can we enable syslog support by default for 7.3? AFAIR, we agreed to flip the default some time ago, we just didn't want to do it late in the 7.2 cycle. Go for it. I think if no one complains about the lack of syslog on his machine we should just remove the option in 7.3+1. My experience has been that logging to syslog makes postgres much slower. What's the problem with this? Even if that's true, you could easily turn off syslog logging by tweaking postgresql.conf. I was worried about the comment of removing the other options. in 7.3+1. At least this is how i interpreted that comment. - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Documentation on page files
On Tue, 2002-04-23 at 01:29, Martijn van Oosterhout wrote: The dumping is more of an extra, the original idea was to check for errors in the datafiles. Hence the working name of pgfsck. At the moment the dumping dumps only tuples where xmax == 0 but I'm not sure if that's correct. AFAIK it is not. As Tom once explained me, it is ok for tuples xmax to be !=0 and still have a valid tuple. The validity is determined by some bits in tuple header. But I think the most useful behaviour should be to dump system fields too, so mildly knowledgeable sysadmin can import the dump and do the right thing afterwards (like restore data as it was before transaction nr 7000) - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation on page files
On Tue, 2002-04-23 at 12:52, Martijn van Oosterhout wrote: Well, from my thinking about how you would use these fields in a logical way, it seems it's possible for xmax to be non-zero if the transaction numbered xmax was not committed. But in that case (unless it was a delete) there would be a newer tuple with the same oid but xmax == 0 (and this uncommitted transaction as xmin). Unless it was an uncommitted DELETE and not UPDATE. The problem is that inside the DB, you have a current transaction plus a list of committed transactions. Externally, you have no idea, so xmax == 0 is as valid a view as any other. This would have the effect of dumping out whatever would be visible if every transaction were committed. IIRC there are some bits that determine the commit status of tuple. I think. If anyone knows a good document on MVCC implementations, let me know. But I think the most useful behaviour should be to dump system fields too, so mildly knowledgeable sysadmin can import the dump and do the right thing afterwards (like restore data as it was before transaction nr 7000) Well, i didn't think you could have statements of the form: insert into table (xmin,xmax,cmin,cmax,...) values (...); but you can have insert into newtable values (...); So you are free to name your xmin,... whatever you like So you would have to leave it as a comment. In which case someone would have to go and by hand work out what would be in or out. I can make it an option but I don't think it would be particularly useful. I have written a small python script that does the above, and I did write it because I needed it, so it must have some use ;) After inserting the data to database I was then able to select all but latest (before delete) version of each tuple. -- Hannu ---(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] PostgreSQL index usage discussion.
On Thu, 2002-04-25 at 00:46, mlw wrote: We have had several threads about index usage, specifically when PostgreSQL has the choice of using one or not. There seems to be a few points of view: (1) The planner and statistics need to improve, so that erroneously using an index (or not) happens less frequently or not at all. (2) Use programmatic hints which allow coders specify which indexes are used during a query. (ala Oracle) (3) It is pretty much OK as-is, just use enable_seqscan=false in the query. My point of view is about this subject is one from personal experience. I had a database on which PostgreSQL would always (erroneously) choose not to use an index. Are my experiences typical? Probably not, but are experiences like it very common? I have currently 2 databases that run with enable_seqscan=false to avoid choosing plans that take forever. I don't know, but we see a number Why won't PostgreSQL use my index messages to at least conclude that it happens every now and then. In my experience, when it happens, it is very frustrating. I think statement (1) is a good idea, but I think it is optimistic to expect that a statistical analysis of a table will contain enough information for all possible cases. Perhaps we can come up with some special rules to avoid grossly pessimal plans. Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PostgreSQL index usage discussion.
On Thu, 2002-04-25 at 08:42, Luis Alberto Amigo Navarro wrote: (2) Use programmatic hints which allow coders specify which indexes are used during a query. (ala Oracle) As I said before it would be useful a way to improve(not force) using indexes on particular queries, i.e. lowering the cost of using this index on this query. Regards I was told that DB2 has per-table (or rather per-tablespace) knowledge of disk speeds, so keeping separate random and seqsqan costs for each table and index could be a good way here (to force use of a particular index make its use cheap) Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Sequential Scan Read-Ahead
On Thu, 2002-04-25 at 12:47, Curt Sampson wrote: On Thu, 25 Apr 2002, Lincoln Yeoh wrote: I think the raw partitions will be more trouble than they are worth. Reading larger chunks at appropriate circumstances seems to be the low hanging fruit. That's certainly a good start. I don't know if the raw partitions would be more trouble than they are worth, but it certainly would be a lot more work, yes. One could do pretty much as well, I think, by using the don't buffer blocks for this file option on those OSes that have it. I was on a short DB2 tuning course and was told that on Win NT turning off cache causes about 15-20% speedup. (I don't know what exacly is sped up :) [1] The theory was the drive typically has to jump around a lot more for metadata than for files. In practice it worked pretty well, if I do say so myself :). Not sure if modern HDDs do specialized O/S metadata caching (wonder how many megabytes would typically be needed for 18GB drives :) ). Sure they do, though they don't necessarially read it all. Most unix systems Do modern HDD's have unix inside them ;) have special cache for namei lookups (turning a filename into an i-node number), often one per-process as well as a system-wide one. And on machines with a unified buffer cache for file data, there's still a separate metadata cache. --- Hannu ---(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] WAL - Replication
On Fri, 2002-04-26 at 07:38, Curt Sampson wrote: On Thu, 25 Apr 2002, Bruce Momjian wrote: WAL files are kept only until an fsync(), checkpoint, then reused. One could keep them longer though, if one really wanted to. Also, the info is tied to direct locations in the file. You could do this for hot backup, but it would require quite bit of coding to make it work. That's kind of too bad, since log shipping is a very popular method of backup and replication. Now again from my just aquired DB2 knowledge: DB2 can run in two modes 1) similar to ours, where logs are reused after checkpoints/commits allow it. 2) with log archiving: logs are never reused, but when system determines it no longer needs them, it will hand said log over to archiving process that will archive it (usually do a backup to some other place and then delete it). This mode is used when online backup and restore functionality is desired. This is something that could be interesting for 24x7 reliability. - Hannu ---(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] WAL - Replication
On Fri, 2002-04-26 at 19:41, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: DB2 can run in two modes 1) similar to ours, where logs are reused after checkpoints/commits allow it. 2) with log archiving: logs are never reused, but when system determines it no longer needs them, it will hand said log over to archiving process that will archive it (usually do a backup to some other place and then delete it). There is in fact the skeleton of support in xlog.c for passing unwanted log segments over to an archiver, rather than recycling them. So far no one's done anything with the facility. I think the main problem is the one Bruce cited: because the WAL representation is tied to physical tuple locations and so forth, it's only useful to a slave that has an *exact* duplicate of the master's entire database cluster. That's not useless, but it's pretty restrictive. It is probably the fastest way to creating functionality for a hot spare database. If we could ship the log changes even earlier than whole logs are complete, we can get near-realtime backup server. It could be useful for incremental backup, though I'm not sure how efficient it is for the purpose. WAL logs tend to be pretty voluminous. But if they contain enough repeated data they should compress quite well. At the very least you'd probably want enough smarts in the archiver to strip out the page-image records. If we aim for ability to restore the last known good state and not any point of time in between, the archiving can be just playing back the logs over sparse files + keeping record (bitmap or list) of pages that have been updated and thus are really present in the file. Then doing full restore would be just restoring some point of time online backup plus copying over changed pages. Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 17:30, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: I've been thinking this over and over, and it seems to me, that the way SETS in transactions SHOULD work is that they are all rolled back, period, whether the transaction successfully completes OR NOT. This would make it impossible for SET to have any persistent effect at all. (Every SQL command is inside a transaction --- an implicitly-established one if necesary, but there is one.) It might well be useful to have some kind of LOCAL SET command that behaves the way you describe (effects good only for current transaction block), but I don't think it follows that that should be the only behavior available. What would you expect if LOCAL SET were followed by SET on the same variable in the same transaction? Presumably the LOCAL SET would then be nullified; or is this an error condition? Perhaps we could do SET SET TO LOCAL TO TRANSACTION; Which would affect itself and all subsequent SET commands up to SET SET TO GLOBAL; or end of transaction. - SET SET TO GLOBAL could also be written as SET SET TO NOT LOCAL TO TRANSACTION; to comply with genral verbosity of SQL ;) -- Hannu ---(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] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 17:53, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Perhaps we could do SET SET TO LOCAL TO TRANSACTION; Which would affect itself and all subsequent SET commands up to SET SET TO GLOBAL; or end of transaction. This makes my head hurt. If I do SET foo TO bar; begin; SET SET TO GLOBAL; SET foo TO baz; SET SET TO LOCAL TO TRANSACTION; end; (assume no errors) what is the post-transaction state of foo? should be baz I'm elaborating the idea of SET with transaction scope here with possibility to do global SETs as well. Any global SET will also affect local set (by either setting it or just unsetting the local one). What about this case? SET foo TO bar; begin; SET SET TO GLOBAL; SET foo TO baz; SET SET TO LOCAL TO TRANSACTION; SET foo TO quux; end; baz again, as local foo==quux disappears at transaction end Of course this last case also exists with my idea of a LOCAL SET command, SET foo TO bar; begin; SET foo TO baz; LOCAL SET foo TO quux; -- presumably SHOW foo will show quux here end; -- does SHOW foo now show bar, or baz? baz I assume here only two kinds of SETs - global ones that happen always and local ones that are valid only within the transaction Arguably you'd need to keep track of up to three values of a SET variable to make this work --- the permanent (pre-transaction) value, to roll back to if error; I started from the idea of not rolling back SETs as they do not affect data but I think that transaction-local SETs are valuable. If we go with your syntax I would prefer SET LOCAL to LOCAL SET , so that LOCAL feels tied more to variable rather than to SET . the SET value, which will become permanent if we commit; and the LOCAL SET value, which may mask the pending permanent value. This seems needlessly complex though. Could we get away with treating the above case as an error? In any case I find a LOCAL SET command more reasonable than making SET's effects depend on the value of a SETtable setting. There is circular logic there. If I do begin; SET SET TO LOCAL TO TRANSACTION; end; what is the post-transaction behavior of SET? It is always GLOBAL unless SET TO LOCAL I explicitly defined this command as applying to itself and all following commands in order to avoid this circularity so END would invalidate it But I already think that LOCAL SET / SET LOCAL is better and more clear. And if you say LOCAL, how do you justify it? Why wouldn't the effects of this SET be local? Hannu ---(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] Vote totals for SET in aborted transaction
On Mon, 2002-04-29 at 18:20, Tom Lane wrote: Thomas Lockhart [EMAIL PROTECTED] writes: Rather than dismissing this out of hand, try to look at what it *does* enable. It allows developers to tune specific queries without having to restore values afterwards. Values or settings which may change from version to version, so end up embedding time bombs into applications. I think it's a great idea. So do I. And I also think that this will solve the original issue, which iirc was rolling back SET TIMEOUT at ABORT. If we have LOCAL SET, there is no need to have any other mechanism for ROLLING BACK/COMMITing SET's - SET and DML can be kept totally separate, as they should be based on fact that SET does not directly affect data. -- Hannu ---(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] Temp tables are curious creatures....
On Tue, 2002-04-30 at 03:35, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Appears psql needs to know how to differentiate between it's own temp tables and those of another connection. More generally, psql is as yet clueless about schemas. regression=# create schema foo; CREATE regression=# create schema bar; CREATE regression=# create table foo.tab1 (f1 int); CREATE regression=# create table bar.tab1 (f2 int); CREATE regression=# \d tab1 Table tab1 Column | Type | Modifiers +-+--- f1 | integer | f2 | integer | This is ... um ... wrong. I am not real sure what the right behavior is, however. Should \d accept patterns like schema.table (and how should its wildcard pattern matching fit with that?) If you don't specify a schema, should it only show tables visible in your search path? Yes. For me the intuitive answer would be regression=# \d tab1 Table foo.tab1 Column | Type | Modifiers +-+--- f1 | integer | Table bar.tab1 Column | Type | Modifiers +-+--- f2 | integer | i.e. default wildcarding of missing pieces - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Temp tables are curious creatures....
On Tue, 2002-04-30 at 03:35, Bruce Momjian wrote: I think you have to use the backend pid to find your own. I think there is a libpq function that returns the backend pis so psql can frame the proper query. Is anyoune working on information schema (or pg_xxx views) for use in psql and other development frontends? Also, are there plans to have SQL-accessible backend_pid function in the backend by default ? On RH 7.1 I can create it as: CREATE FUNCTION getpid() RETURNS integer AS '/lib/libc.so.6','getpid' LANGUAGE 'C'; But I'd like it to be a builtin from the start so one can query it without relying on libpq --- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 05:33, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: So, how does one determine the current schema for temporary tables, i.e. what name would be in search_path if it wasn't implicitly included? The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. The non-portable way on Linux RH 7.2 : create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; CREATE select getpid() getpid1 - 31743 (1 row) I think that useful libc stuff things like this should be put in some special schema, initially available to superusers only. perhaps LIBC.GETPID() -- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 15:48, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: On Thu, 2002-05-02 at 05:33, Tom Lane wrote: The temp schema is pg_temp_nnn where nnn is your BackendId (PROC array slot number). AFAIK there isn't any exported way to determine your BackendId from an SQL query. The non-portable way on Linux RH 7.2 : create function getpid() returns int as '/lib/libc.so.6','getpid' language 'C'; But PID is not BackendId. Are you sure ? I was assuming that BackendId was the process id of current backend and that's what getpid() returns. What is the Backend ID then ? Is PROC array slot number something internal to postgres ? - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schemas: status report, call for developers
On Thu, 2002-05-02 at 16:52, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Is PROC array slot number something internal to postgres ? Yes. If we used PID then we'd eventually have 64K (or whatever the range of PIDs is on your platform) different pg_temp_nnn entries cluttering pg_namespace. Should they not be cleaned up at backend exit even when they are in range 1..MaxBackends ? But we only need MaxBackends different entries at any one time. So the correct nnn value is 1..MaxBackends. BackendId meets the need perfectly. -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
