Re: [HACKERS] Index/Function organized table layout
James Rogers kirjutas N, 02.10.2003 kell 23:44: On Thu, 2003-10-02 at 12:09, Hannu Krosing wrote: So what you really need is the CLUSTER command to leave pages half-empty and the tuple placement logic on inserts/updates to place new tuples near the place where they would be placed by CLUSTER. I.e. the code that does actual inserting should be aware of CLUSTERing. Not exactly. What you are describing is more akin to partitioning or hash-organized tables i.e. sorting insert/update tuples to various pages according to some hash function. What I actually thought I was describing is how CLUSTER should work in a postgres flavour of MVCC storage ;). Not the CLUSTER command, but the whole feature. B-Tree organized tables basically make the table and the primary index the same thing, and tend to be most useful for tables that use a single multi-column primary key index for queries. This has the effect of putting all the tuples for a typical query in the same small number of heap pages (and therefore buffers), allowing very efficient access in the typical case with the caveat that non-indexed queries will be quite slow. AFAICS we could resolve this problem (querying indexes only) by keeping a copy of visibility info (tmin,tmax,...) in index tuples. This would make index updates bigger and thus slower, so this should be optional. If you then put all fields in primary key, then the main table could be dropped. If there is no data table then no other indexes would then be allowed, or they must be double-indexes referencing the primary key, not tuple and thus even bigger ... B-Tree organized tables are particularly useful when the insert order is orthogonal to the typical query order. As I mentioned before, tables that store parallel collections of time-series data are classic examples. Often the data is inserted into the pages in order that can roughly be described as (timestamp, id), but is queried using (id, timestamp) as the index. If you have enough ids, you end up with the pathological case where you typically have one relevant tuple per page for a given query. But if we had clustered the table on (id, timestamp), then the data would be in right order for queries, if cluster worked well. The nuisance would be keeping track of which pages are collecting which tuples. Knowing the CLUSTER index doesn't tell you much about which pages would currently be a good place to put a new tuple. You could always markup the index that CLUSTER uses to keep track of good candidates (plus some additional structures), but the more I think about that, the more it looks like a nasty hack. Yeah, index-organized tables seems exact fit for your problem, but then my abstract idea of what clustering should do is exactly that - keep tuples in roughly the same order as an index ;) So what really is needed is a smart tuple-placer which can keep tuples that are close (as defined by index) together in a small number of pages. These pages themselves need not be coninuous, they can be sprinkled around in the whole data table, but they need to stay clusters of index-close tuples. Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Quick question
On Fri, 3 Oct 2003, Christopher Kings-Lynne wrote: Hi guys, If someone could help me with this, it would be cool. How do I query the catalogs to find the underlying index for a constraint? (Assuming the constraint is primary or unique) For a primary key you can do: SELECT cls.relname AS index_name FROM pg_class cls, pg_constraint con, pg_index i WHERE cls.oid = i.indexrelid AND con.conrelid = i.indrelid AND i.indisprimary AND con.conname='constraint name'; This is not possible for a unique constraint because you can have multiple unique constraints per table. So you are left trying to match pg_constraint.conkey to pg_index.indkey (for which no default operator exists), but even this can fail if you have the unlikely situation of two unique indexes covering the same columns. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ecpg doesn't compile (datetime.h/dtime_t)
On Mon, Sep 29, 2003 at 06:41:48PM +0100, Patrick Welche wrote: Today's cvs doesn't compile. I think it is due to cvs diff -r1.7 -r1.8 src/interfaces/ecpg/include/datetime.h I have dtime_t defined in my sys/types.h. The old version of datetime.h used I tried too hide these type definitions from our build process. It should compile now. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Weird locking situation
Christopher Kings-Lynne [EMAIL PROTECTED] writes: OK, I tried it again and it still seems buggy to me... I wonder if it's something to do with the tsearch trigger on food_foods? I tried a table with a simple BEFORE trigger and it didn't fail. But when I added a GIST index, it did: [ install contrib/btree_gist ] regression=# create index gindex on foo using gist (f2); CREATE INDEX ... regression=# UPDATE foo SET f2=now() WHERE f1=1; ERROR: deadlock detected DETAIL: Process 18122 waits for AccessExclusiveLock on relation 154635 of database 17139; blocked by process 18133. Process 18133 waits for ShareLock on transaction 6330; blocked by process 18122. The trouble here is that GIST indexes are not concurrency-safe. This is on the TODO list but I fear it's not a small task ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Using backslash in query
I'm afraid I'm confused about something very simple... but anyway I need to run a query on a varchar field containing a backslash. My first attempt looked like this: SELECT smth. FROM tbl WHERE situation LIKE '%\\%'; This did not returned any rows. I looked up for a reference, confirmed that ... double-backslash is required to represent a literal backslash. http://www.postgresql.org/docs/aw_pgsql_book/node139.html#copy_backslash_han dling But when I doubled the number of backslashes: SELECT smth. FROM tbl WHERE situation LIKE '%%'; - it actually worked fine. Same thing happens with using regex: situation ~ '\\'; Could someone shed some light on this, please. Mike. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] timestamp.c is broken (probably by ecpg) in 7.4
I get the error message below when trying to 'make' current cvs: gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../../../../src/interfaces/ecpg/include -I../../../../src/include/utils -I../../../../src/include -D_GNU_SOURCE -g -c timestamp.c -o timestamp.o In file included from ../../../../src/interfaces/ecpg/include/compatlib.h:7, from ../../../../src/interfaces/ecpg/include/datetime.h:4, from timestamp.c:14: ../../../../src/interfaces/ecpg/include/ecpglib.h:9: libpq-fe.h: No such file or directory make[4]: *** [timestamp.o] Error 1 My config is: ./configure --prefix=/usr/local/pgsql --enable-integer-datetimes --with-openssl --with-pgport=5433 This is on a redhat 6.1 system. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] minor view creation weirdness
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I'd almost argue that we should change this message to an error I agree. Except that it would totally break backwards-compatibility? Well, that's a possible problem. How many such views do you think are out there, given the existence of the warning? regards, tom lane ---(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] Quick question
Christopher Kings-Lynne [EMAIL PROTECTED] writes: If someone could help me with this, it would be cool. How do I query the catalogs to find the underlying index for a constraint? (Assuming the constraint is primary or unique) A first approximation is that the constraint and the index have the same name, but I suppose someone could break that association by renaming the index. Look in pg_depend for an internal dependency entry from the index to the constraint if you want to be sure. regards, tom lane ---(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] ecpg doesn't compile (datetime.h/dtime_t)
On Fri, Oct 03, 2003 at 12:59:19PM +0200, Michael Meskes wrote: On Mon, Sep 29, 2003 at 06:41:48PM +0100, Patrick Welche wrote: Today's cvs doesn't compile. I think it is due to cvs diff -r1.7 -r1.8 src/interfaces/ecpg/include/datetime.h I have dtime_t defined in my sys/types.h. The old version of datetime.h used I tried too hide these type definitions from our build process. It should compile now. Now I get: gcc -O2 -pipe -g -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../../src/interfaces/ecpg/include -I../../../../src/include/utils -I../../../../src/include -g -c -o timestamp.o timestamp.c In file included from ../../../../src/interfaces/ecpg/include/compatlib.h:7, from ../../../../src/interfaces/ecpg/include/datetime.h:4, from timestamp.c:14: ../../../../src/interfaces/ecpg/include/ecpglib.h:9:22: libpq-fe.h: No such file or directory In file included from ../../../../src/interfaces/ecpg/include/compatlib.h:7, from ../../../../src/interfaces/ecpg/include/datetime.h:4, from timestamp.c:14: ../../../../src/interfaces/ecpg/include/ecpglib.h:75: error: parse error before PGresult ../../../../src/interfaces/ecpg/include/ecpglib.h:79: warning: `enum ECPGttype' declared inside parameter list ../../../../src/interfaces/ecpg/include/ecpglib.h:79: warning: its scope is only this definition or declaration, which is probably not what you want ../../../../src/interfaces/ecpg/include/ecpglib.h:79: warning: parameter has incomplete type ../../../../src/interfaces/ecpg/include/ecpglib.h:80: warning: `enum ECPGttype' declared inside parameter list ../../../../src/interfaces/ecpg/include/ecpglib.h:80: warning: parameter has incomplete type In file included from timestamp.c:14: ../../../../src/interfaces/ecpg/include/datetime.h:6: error: conflicting types for `dtime_t' /usr/include/sys/types.h:184: error: previous declaration of `dtime_t' gmake[4]: *** [timestamp.o] Error 1 gmake[4]: Leaving directory `/usr/src/local/pgsql/src/interfaces/ecpg/pgtypeslib I did do a gmake distclean beforehand.. % cd src/interfaces/ecpg/include % more datetime.h #ifndef _ECPG_DATETIME_H #define _ECPG_DATETIME_H #include compatlib.h typedef timestamp dtime_t; typedef interval intrvl_t; #endif /* ndef _ECPG_DATETIME_H */ % Cheers, Patrick ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Oracle/PostgreSQL incompatibilities
Ladies and Gentemen, in a database project I ported an Oracle database definition to PostgreSQL 7.3.2 as an aside. During this process I found a couple of incompatibilities in the SQL dialects of both DBMS. I compiled the following list for whatever it might be good for - for example to transscribe Oracle DD statements to PostgreSQL DD statements. Or to enhance PostgreSQL to increase Oracle compatibility - as far as this might be sensible and desirable. Or to establish a more comprehensive Oracle/PostgreSQL list. Since this is nothing I am actively working on I don't expect any response. However, if it should be useful for you, I'd appreciate some feedback. + At least the following names are a) different and b) PostgreSQL does not understand the Oracle equivalent: Oracle: PostgreSQL: VARCHAR2 varchar NUMBER int CLOB bytea sysdate current_date + CREATE SCHEMA: Sometimes a schema created in PostgreSQL disappears if there is nothing in it. + CREATE INDEX: PostgreSQL should allow specifying a namespace for the index, even if the namespace is required to be the same as the parent table. This would increase Oracle compatibility. + CREATE SEQUENCE: Oracle allows (or requires) INCREMENT BY instead of just INCREMENT. Same for START WITH vs. START. Oracle allows explicit NOCYCLE and NOCACHE. It also has a keyword ORDER. + Indexes and table constraints share the same namespace. + Oracle's DISABLE in foreign key specification in table constraint is unknown by PostgreSQL. + PostgreSQL does not support Oracle's CREATE PUBLIC SYNONYM + PostgreSQL does not support the NUMBER keyword without (...) i.e. something in parenthesis following it. + Oracle's SEQ_KATALOGID.nextval should be translated to nextval('SEQ_KATALOGID'). Rainer Klute IT-Consulting GmbH Dipl.-Inform. Rainer Klute E-Mail: [EMAIL PROTECTED] Körner Grund 24 Telefon: +49 172 2324824 D-44143 Dortmund Telefax: +49 231 5349423 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] calling functions through a pointer
Hallo, I wonder if there is a way to call functions in plpgsql having their oid. As an example: suppose i have a table that contains oids to functions and i have some statements that select some rows and must call the corresponding functions. Of course i can do it using dynamic sql (building the statement string on the fly), but this is much less performing than direct sql written in plpgsq... Thanks, Max. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Question regarding coopting Database Engine
Hello, I'd like to know if its possible to coopt the postgres storage subsystem to rely entirely on ram based structures, rather than disk. Any documentation or ideas would be appreciated. Cheers, Steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Thoughts on maintaining 7.3
On Thu, Oct 02, 2003 at 02:15:33PM -0500, Bruno Wolff III wrote: It might be better to split into two different trees. One just gets bug fixes, the other gets bug fixes plus enhancements that won't require an initdb. Yes, please. Please, please do not force all users to accept new features in stable trees. -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(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] Weird locking situation
On Fri, 3 Oct 2003, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: OK, I tried it again and it still seems buggy to me... I wonder if it's something to do with the tsearch trigger on food_foods? I tried a table with a simple BEFORE trigger and it didn't fail. But when I added a GIST index, it did: [ install contrib/btree_gist ] regression=# create index gindex on foo using gist (f2); CREATE INDEX ... regression=# UPDATE foo SET f2=now() WHERE f1=1; ERROR: deadlock detected DETAIL: Process 18122 waits for AccessExclusiveLock on relation 154635 of database 17139; blocked by process 18133. Process 18133 waits for ShareLock on transaction 6330; blocked by process 18122. The trouble here is that GIST indexes are not concurrency-safe. This is on the TODO list but I fear it's not a small task ... You're right. We hoped to work on concurrency this year and already did some research. But life is so complicated :( regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using backslash in query
Michael Brusser [EMAIL PROTECTED] writes: But when I doubled the number of backslashes: SELECT smth. FROM tbl WHERE situation LIKE '%%'; - it actually worked fine. Backslash is special to both the string-literal parser and the LIKE code. So when you write the above, the pattern value that arrives at the LIKE processor has one less level of backslashing: %\\% and the LIKE processor interprets this as percent, a literal backslash, and another percent. regards, tom lane ---(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] Oracle/PostgreSQL incompatibilities
+ CREATE SCHEMA: Sometimes a schema created in PostgreSQL disappears if there is nothing in it. If true, this would be a bug. Do you have a reproducible test case? + CREATE INDEX: PostgreSQL should allow specifying a namespace for the index, even if the namespace is required to be the same as the parent table. This would increase Oracle compatibility. Agreed for 7.5. + CREATE SEQUENCE: Oracle allows (or requires) INCREMENT BY instead of just INCREMENT. Same for START WITH vs. START. Oracle allows explicit NOCYCLE and NOCACHE. It also has a keyword ORDER. 7.4 implements the 200N proposed sequence names -- which are nearly equivalent to the Oracle definition (I believe RESTART WITH is missing in Oracle). + Indexes and table constraints share the same namespace. Well.. some constraints are implemented via indexes. The index doesn't conflict with the constraint name, it conflicts with the index name -- but they share the same name. Anyway, the way to do this is better hide the implementation of a unique or primary key constraint. Or allow for empty, invalid or missing names in those cases. For example, constraint index names could be the OID of the constraint. Since a fully numerical name is invalid, this would effectively remove the problem. + PostgreSQL does not support the NUMBER keyword without (...) i.e. something in parenthesis following it. From what I can tell, PostgreSQL doesn't support NUMBER at all. Numeric is the SQL specified version. dev_iqdb=# select 5::numeric; numeric - 5 (1 row) + Oracle's SEQ_KATALOGID.nextval should be translated to nextval('SEQ_KATALOGID'). nextval('') has dependency tracking issues, so needs to be changed. The debate is whether to support Oracle or DB2 syntax for next value of indexes. Oracle syntax is more common, DB2 syntax is in the SQL 200N proposal. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Question regarding coopting Database Engine
On Tue, 30 Sep 2003, Steve Yalovitser wrote: Hello, I'd like to know if its possible to coopt the postgres storage subsystem to rely entirely on ram based structures, rather than disk. Any documentation or ideas would be appreciated. Sure, create a ram disk. Set $PGDATA to it with proper permissions, initdb, and restore. Not sure why'd you wanna do it, as any crash loses all data, and postgresql's strengths lie in its ability to survive the most brutal power off situations mid-transaction etc... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Question regarding coopting Database Engine
On Tue, 2003-09-30 at 00:10, Steve Yalovitser wrote: Hello, I'd like to know if its possible to coopt the postgres storage subsystem to rely entirely on ram based structures, rather than disk. Any documentation or ideas would be appreciated. Just so you know, this isn't going to make the system any faster. I assume you are trying to do something like put the database onto a bootable CD that fires up some static application with static information? signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Oracle/PostgreSQL incompatibilities
Rainer Klute [EMAIL PROTECTED] writes: [ some good comments, but a few things I want to respond to ] + CREATE SCHEMA: Sometimes a schema created in PostgreSQL disappears if there is nothing in it. This is more than a bit hard to believe. Can you give an example? + CREATE SEQUENCE: Oracle allows (or requires) INCREMENT BY instead of just INCREMENT. Same for START WITH vs. START. Oracle allows explicit NOCYCLE and NOCACHE. It also has a keyword ORDER. It looks like much of this has been done as of 7.4. I dunno what ORDER is for though. + PostgreSQL does not support the NUMBER keyword without (...) i.e. something in parenthesis following it. Don't follow this one either. We don't have NUMBER --- are you speaking of NUMERIC? If so, I'm not aware of any context where you're required to put a precision on NUMERIC. Again, may we see an example? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Oracle/PostgreSQL incompatibilities
+ CREATE SCHEMA: Sometimes a schema created in PostgreSQL disappears if there is nothing in it. This is more than a bit hard to believe. Can you give an example? We use schema's ALOT in our applications. I have yet to see this happen. + PostgreSQL does not support the NUMBER keyword without (...) i.e. something in parenthesis following it. Don't follow this one either. We don't have NUMBER --- are you speaking of NUMERIC? If so, I'm not aware of any context where you're required to put a precision on NUMERIC. Again, may we see an example? Ditto. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Thoughts on maintaining 7.3
Yes, please. Please, please do not force all users to accept new features in stable trees. What if the feature does break compatibility with old features? What if it is truly a new feature? One example would be that we are considering reworking pg_dump/restore a bit to support batch uploads and interactive mode. It would not break compatibility with anything but would greatly enhance one's ability to actually backup and restore large volume sets. Sincerely, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts on maintaining 7.3
Joshua D. Drake [EMAIL PROTECTED] writes: Yes, please. Please, please do not force all users to accept new features in stable trees. What if the feature does break compatibility with old features? What if it is truly a new feature? One example would be that we are considering reworking pg_dump/restore a bit to support batch uploads and interactive mode. It would not break compatibility with anything but would greatly enhance one's ability to actually backup and restore large volume sets. Well, since those are separate programs and not intimately tied to the backend, you could distribute them separately for people who need them... -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Thoughts on maintaining 7.3
On Fri, 3 Oct 2003, Joshua D. Drake wrote: Yes, please. Please, please do not force all users to accept new features in stable trees. What if the feature does break compatibility with old features? What if it is truly a new feature? One example would be that we are considering reworking pg_dump/restore a bit to support batch uploads and interactive mode. It would not break compatibility with anything but would greatly enhance one's ability to actually backup and restore large volume sets. for stuff like this, why not just break off a gborg project for it, seperate from the distros? We could pull in the changes as beta starts on a dev cycle, but then pg_dump/pg_restore could be maintained on its own release cycle, and you could easily get 'back features' in like this ... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] minor view creation weirdness
On Fri, 2003-10-03 at 00:50, Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I'd almost argue that we should change this message to an error I agree. Motion proposed and seconded; any objections out there? Uhm, doesn't the spec have anything to say about this? I mean, the view sure looks like standard SQL on its face. In any case, I would sure think there was something strange about a query working fine as a select but not working in a view: slo= select 'foo' as a,'bar' as b; a | b -+- foo | bar (1 row) slo= create view x as select 'foo' as a,'bar' as b; WARNING: Attribute a has an unknown type Proceeding with relation creation anyway WARNING: Attribute b has an unknown type Proceeding with relation creation anyway CREATE VIEW slo= select * from x; a | b -+- foo | bar (1 row) or the create table case: rms=# create table x as select 'foo' as a, 'bar' as b; WARNING: 42P16: attribute a has type UNKNOWN DETAIL: Proceeding with relation creation anyway. LOCATION: CheckAttributeType, heap.c:427 WARNING: 42P16: attribute b has type UNKNOWN DETAIL: Proceeding with relation creation anyway. LOCATION: CheckAttributeType, heap.c:427 WARNING: attribute a has type UNKNOWN DETAIL: Proceeding with relation creation anyway. WARNING: attribute b has type UNKNOWN DETAIL: Proceeding with relation creation anyway. SELECT rms=# select * from x; a | b -+- foo | bar (1 row) rms=# \d x Table public.x Column | Type| Modifiers +---+--- a | unknown | b | unknown | Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] back from Washington, still busy
I am back and reading email now. --- Bruce Momjian wrote: I have returned from Washington, but one of my sons is in the hospital with a mild pneumonia. I think he is coming home tomorrow, so I will read all my email this weekend. It might seems strange I am reporting this, but I went away a while ago and didn't inform the hackers list, and some people got concerned when email requests weren't handled promptly. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] minor view creation weirdness
Greg Stark [EMAIL PROTECTED] writes: Uhm, doesn't the spec have anything to say about this? I mean, the view sure looks like standard SQL on its face. Well, you might read the spec as requiring the view column to have datatype CHAR(n) where n is the length of the unknown literal. I see in SQL92: 9) The data type of a character string literal is fixed-length character string. The length of a character string literal is the number of character representations that it contains. ... Note: character string literals are allowed to be zero-length strings (i.e., to contain no characters) even though it is not permitted to declare a data type that is CHARACTER with length zero. The NOTE would appear to imply that CREATE VIEW v AS SELECT ''; is illegal, which is not a conclusion I care to follow blindly. In any case, in Postgres I would think we would want to take the type as text not CHAR(n), spec or no spec. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] initdb
I now have a C implementation of initdb, which successfully runs with make check on my several linux machines, and compiles on Windows/MinGW too (can't run make check on Windows because we haven't got a native postgres yet - I'm going to create a small dummy Windows postgres that will let me check if this program works there). There's a little work still to go (see below), but I'd appreciate some eyeballs on it to see if I have made any major booboos, or could have done things better. What's the best way to proceed? (All told it's about 2500 lines of C.) cheers andrew From the heading comment: / * * initdb * * This is a C implementation of the previous shell script for setting up a * PostgreSQL cluster location, and should be highly compatible with it. * * TODO: * - signal handling * - more error checking, partiularly on the file i/o * - check if we need workaround for timing error on win32 rmdir()? * - clean up find_postgres code and return values * - free up used memory? (probably not worth it - if we can't load this * much data into memory how will we ever run postgres anyway?) */ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4 status
While reviewing someone else's translation of pg_dump I noted that the phrase ACL list is used in a couple of places. However ACL stands for Access Control List, so the term ACL list seems redundant. Maybe it should be replaced with plain ACL? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) I think my standards have lowered enough that now I think 'good design' is when the page doesn't irritate the living f*ck out of me. (JWZ) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange behavior regarding temporary sequences
Harald Fuchs [EMAIL PROTECTED] writes: test=# create temp sequence ts; CREATE SEQUENCE test=# create table tt1 (id int not null default nextval ('ts'), str varchar(255) not null); CREATE TABLE Although what PostgreSQL (7.3.4) does is perfectly reasonable, I find it somewhat unclean. Since we now disallow FOREIGN KEYs between temp and normal tables, we might also disallow using temp sequences with normal tables. That's fairly impractical given that PG doesn't know that nextval('ts') represents a sequence reference at all. (The nextval() function knows it, but I'd strongly resist any attempt to hard-wire assumptions about nextval() into the rest of the system.) There has been some talk of supporting the Oracle sequence syntax ts.nextval, which would expose the sequence reference in a form the system could recognize. In the present state of the system, that would cause your DEFAULT expression to get dropped when the temp sequence went away (same result as DROP ... CASCADE issued manually). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts on Maintaining 7.3
Joshua, et. al. Sorry for weighing in on this discussion so late; I've gotten behind on the Hackers digests, since there are some 90 messages a day. I don't see anything wrong with the idea of maintaining a 7.3 tree for bug fixes and testing if /contrib modules can be backported. Heck, I'm pretty sure that tsearch, pgavd, and erserver already have 7.3 compatible downloads on Gborg. What I would resist is the idea that any contributors be distracted from work on new+improved features for 7.4 7.5 for this. That is, whoever maintains the 7.3 tree should be someone new, or someone who's not up to hacking new features, yet. Actually, this might be a great way for a new hacker to get up to speed on the PostgreSQL codebase. Comprehensive backward compatibility is the hobgoblin of commercial software development. I support one legal services database (Bruce knows who I mean) which after 10 years of development still sport the same ill-concieved and poorly normalized schema, which leads them to constant serious performance and scalability issues. Why? Because 80% of their customers still use 2-6 year old versions of their software, and thus their development team spends 75% or more of its time supporting old versions instead of creating new ones, and they're not allowed to re-structure the schema becuase that makes upgrades more costly. We don't want to go this way. So: yes to keeping a 7.3 tree; No to having Bruce, Tom, Joe, Neil, Sean, Peter, Alvero, Jan, or any other current major contributor bothered with it. (Hmmm ... don't we have even one woman on the contributors list? Geeks are us) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Thoughts on maintaining 7.3
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I think what Tom is concerned about is that this hasn't been tested enough with big datasets. Also there a little loss of index pages but it's much less (orders of magnitude, I think) than what was before. This is because the index won't shrink vertically. The fact that we won't remove levels shouldn't be meaningful at all --- I mean, if the index was once big enough to require a dozen btree levels, and you delete everything, are you going to be upset that it drops to 13 pages rather than 2? I doubt it. The reason I'm waffling about whether the problem is completely fixed or not is that the existing code will only remove-and-recycle completely empty btree pages. As long as you have one key left on a page it will stay there. So you could end up with ridiculously low percentage-filled situations. This could be fixed by collapsing together adjacent more-than-half-empty pages, but we ran into a lot of problems trying to do that in a concurrent fashion. So I'm waiting to find out if real usage patterns have a significant issue with this or not. Though the new code will put empty index pages into the free-space map, will it also shrink the index file to remove those pages? For example, if I have 200M rows in a table, and I delete all of them except 100, does the index shrink, or the pages just become available for reuse. With VACUUM FULL, we have a way to shrink the heap. Do we shrink the index? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Thoughts on maintaining 7.3
Andrew Sullivan wrote: On Thu, Oct 02, 2003 at 02:15:33PM -0500, Bruno Wolff III wrote: It might be better to split into two different trees. One just gets bug fixes, the other gets bug fixes plus enhancements that won't require an initdb. Yes, please. Please, please do not force all users to accept new features in stable trees. One word of warning --- PostgreSQL has grown partially because we gain people but rarely lose them, and our stable releases help that. I was talking to someone about OS/X recently and the frequent breakage in their OS releases is hurting their adoption rate --- you hit one or two buggy releases in a row, and you start thinking about using something else --- same is true for buggy Linux kernels, which Andrew described earlier. If we are going to back-patch more aggressively, we _have_ to be sure that those back-patched releases have the same quality as all our other releases. I know people already know this, but it is worth mentioning specifically --- my point is that more agressive backpatching has risks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dreaming About Redesigning SQL
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Seun Osewa) belched out...: This is for relational database theory experts on one hand and imlementers of real-world alications on the other hand. If there was a chance to start again and design SQL afresh, for best cleaness/power/performance what changes would you make? What would _your_ query language (and the underlying database concept) look like? There are two notable 'projects' out there: 1. There's Darwen and Date's Tutorial D language, defined as part of their Third Manifesto about relational databases. 2. newSQL http://newsql.sourceforge.net/, where they are studying two syntaxes, one based on Java, and one based on a simplification (to my mind, oversimplification) of SQL. The newSQL project suffers from their definition being something of a chip away everything that doesn't look like an elephant definition. They aren't defining, in mathematical terms, what their language is supposed to be able to express; they are instead defining a big grab-bag of minor syntactic features, and seem to expect that a database system will emerge from that. In contrast, Tutorial D is _all_ about mathematical definition of what it is supposed to express, and the text is a tough read, irrespective of other merits. -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com'). http://cbbrowne.com/info/thirdmanifesto.html DOS: n., A small annoying boot virus that causes random spontaneous system crashes, usually just before saving a massive project. Easily cured by Unix. See also MS-DOS, IBM-DOS, DR-DOS. -- from David Vicker's .plan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Thoughts on maintaining 7.3
On Fri, 3 Oct 2003, Andrew Sullivan wrote: On Thu, Oct 02, 2003 at 02:15:33PM -0500, Bruno Wolff III wrote: It might be better to split into two different trees. One just gets bug fixes, the other gets bug fixes plus enhancements that won't require an initdb. Yes, please. Please, please do not force all users to accept new features in stable trees. I wanted to say something similar earlier in this thread. To me the stable branches are not for feature introduction. If features are going to be introduced it is better to not have them applied in a manner which means a pure bug fix only version can't be obtained. Obviously this means having two branches if features are going to be introduced. I agree sometimes one looks at new developments and thinks how good it would be to have that feature, imagine what it'll be like when tablespaces are introduced and you're using the previous stable version, but those features need to be kept separate from the version that fixes that particularly nasty index corruption someone only provided a fix for 12 months after the version you have based your system around was released. One could argue that what is really needed is a collection of patches providing a pick and choose facility for features, with dependecies where unavoidable of course. The patches being applicable to the latest bug patched version of the stable branch. As an example take tsearch2. If that were core code, not optional, contrib material, and one was running a 7.3 series server but wanted the nifty features of tsearch2 instead of tsearch, would you expect all people upgrading within the stable 7.3 branch for bug fixes to be forced to use tsearch2 and not tsearch? -- Nigel J. Andrews ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts on maintaining 7.3
If we are going to back-patch more aggressively, we _have_ to be sure that those back-patched releases have the same quality as all our other releases. I know that I am probably being semantic here but I in know way want to be more aggressive with back patching. My thoughts for 98% of things in on bugfixes within the existing tree only. Although I am sure for some things we can use (at least as a guide) code being written in 7.4. My whole purpose in bringing the idea up is to increase the adoption rate. My thought isn't to be more agressive per say, but more responsible in our releases. Like I said, I may be, being semantic. Sincerely, Joshua Drake I know people already know this, but it is worth mentioning specifically --- my point is that more agressive backpatching has risks. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.4 status
Alvaro Herrera writes: While reviewing someone else's translation of pg_dump I noted that the phrase ACL list is used in a couple of places. However ACL stands for Access Control List, so the term ACL list seems redundant. These kinds of redundancies are pretty common for the sake of clarity and the flow of the language. Even major so-called Usage Panels accept them. The thing being parsed here is an array of datums of type aclitem, so the term ACL list is rather unclear anyway. But I hesitate to change it now because we wanted to call a string freeze. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] String freeze
I believe we have agreed to call a string freeze as of beta 4. That means that any string changes for the sake of prettiness are now to be avoided. (Here, strings means any strings that compose messages seen by the user.) This also includes adding new strings. String changes because of bug fixes, either in the code or in the string itself, are of course still allowed. The purpose of all this is to allow translators to finish their translations in time for the final release. -- Peter Eisentraut [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] is_superuser parameter creates inconsistencies
Bruce Momjian writes: Allow SET SESSION AUTHORIZATION to update the psql %n user display This already works. -- Peter Eisentraut [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] Fix for PL/Tcl
Jan Wieck wrote: Bruce Momjian wrote: Jan Wieck wrote: Just committed a small fix for PL/Tcl. I don't find it on the TODO, but you might want to add it to the release notes. * Fixed PL/Tcl's spi_prepare to accept full qualified type names in the parameter type list. Oops, properly added to release notes, removed from TODO. Should this be backpatched into 7.3 as well? I don't think we will be releasing on 7.3.X anymore, and probably not to anyone using pl/tcl. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] is_superuser parameter creates inconsistencies
OK, item remvoed. --- Peter Eisentraut wrote: Bruce Momjian writes: Allow SET SESSION AUTHORIZATION to update the psql %n user display This already works. -- Peter Eisentraut [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]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] updating INSTALL file
Thanks. INSTALL file updated. Peter will do the final one, but at least we have a more current one in there now. --- Peter Eisentraut wrote: Bruce Momjian writes: I would rebuild it right now but the cross-links I added to INSTALL to allow a mention of shared_buffers and sort_mem as part of the tuning recommendation has broken the INSTALL build: You need to do it like this: para commandpg_dumpall/command does not save large objects. Check ![%standalone-include[the documentation]] ![%standalone-ignore[xref linkend=backup-dump-caveats]] if you need to do this. /para -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Oracle/PostgreSQL incompatibilities
I think he means that you can do this in oracle : CREATE TABLE test (id NUMBER); Oracle treats NUMBER as NUMBER(40) I think. This seems to be an example of Oracle making up standards as they go along - do we want to copy this sort of thing ? I usually just run a substitution of NUMBER(..) - NUMERIC(..) and NUMBER - INTEGER when transporting schemas from Oracle to Pg. (This needs a little care - as NUMBER in Oracle has bigger scale than INTEGER in Pg) regards Mark + PostgreSQL does not support the NUMBER keyword without (...) i.e. something in parenthesis following it. Don't follow this one either. We don't have NUMBER --- are you speaking of NUMERIC? If so, I'm not aware of any context where you're required to put a precision on NUMERIC. Again, may we see an example? Ditto. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] Dreaming About Redesigning SQL
Christopher Browne wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Seun Osewa) belched out...: This is for relational database theory experts on one hand and imlementers of real-world alications on the other hand. If there was a chance to start again and design SQL afresh, for best cleaness/power/performance what changes would you make? What would _your_ query language (and the underlying database concept) look like? There are two notable 'projects' out there: 1. There's Darwen and Date's Tutorial D language, defined as part of their Third Manifesto about relational databases. I read the Third Manifesto. There are many ideas in the TTM that have strong arguments, although I most confess I haven't read any critiques. A few (of many) points: 1) Strict adherence to the relational model, where all of SQL's short-comings are addressed: A) No attribute ordering B) No tuple ordering (sets aren't ordered) C) No duplicate tuples (relations are sets) D) No nulls (2VL sufficient. Missing information is meta-data) E) No nullogical mistakes (ex: SUM of an empty relation is zero, AVG is an error) F) Generalized transitive closure G) Declared attribute, relation variable, and database constraints, including transition constraints H) Candidate keys required (this has positive logical consequences for the DBMS implementor) I) Tuple and relation-valued attributes J) No tuple-level operations a bunch more... 2) The query language should be computationally complete. The user should be able to author complete applications in the language, rather than the language being a sublanguage. This reverses Codd's query sublanguage proposed in A Relational Model of Data for Large Shared Data Banks http://www.acm.org/classics/nov95/s1p5.html sarcasm Thanks ACM for just putting part of the paper on-line, complete with broken links and spelling errors! /sarcasm 3) The language (a D implementation) would ensure a separation between the logical design of the application and the physical implementation. The programmer should think in terms of the evaluation of relational algebraic expressions, not manipulating physical records in disk blocks in a file. 4) The type system should separate the actual, internal representation from the possible representation, of which there might be many. For example, a POINT may be internally expressed in cartesian coordinates but may supply both polar and cartensian THE_ operators. 5) The type system should implement D D's view of multiple inheritance, where read-operators are inherited but write-operators aren't. This eliminates the Is a Circle an Ellipse? dilemma imposed by C++, for example. IOW, in a D language, a Circle is an Ellipse. They reject Stonebreaker's ideas of OIDs and relation variable inheritance, which of course, are in PostgreSQL. It's a very provocative read. At a minimum, one can learn what to avoid with SQL. The language looks neat on paper. Perhaps one day someone will provide an open source implementation. One could envision a D project along the same lines as the same sort of project that added SQL to Postgres... But I'd rather have PITR :-) Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Thoughts on maintaining 7.3
Bruce Momjian [EMAIL PROTECTED] writes: Though the new code will put empty index pages into the free-space map, will it also shrink the index file to remove those pages? If there are free pages at the end, yes --- but it won't move pages around. This is about the same story as for plain VACUUM ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Thoughts on maintaining 7.3
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Though the new code will put empty index pages into the free-space map, will it also shrink the index file to remove those pages? If there are free pages at the end, yes --- but it won't move pages around. This is about the same story as for plain VACUUM ... I know indexes behave the same as heap for vacuum. My point was that the vacuum full case is different. Vacuum full moves heap tuples from the end to fill slots and then frees the pages at the end via truncation. (100% compaction, guaranteed.) We can't move index tuples around like that, of course, so that leaves us with partially filled pages. Do we move empty index pages to the end before truncation during vacuum full? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgadmin-hackers] GPL code issue?
Dave Page wrote: /* A Bison parser, made by GNU Bison 1.875. */ Given that this file appears to be produced *by* Bison, and given this exception: /* As a special exception, when this file is copied by Bison into a Bison output file, you may use that output file without restriction. This special exception was added by the Free Software Foundation in version 1.24 of Bison. */ it would appear that the file can be used without restriction. Any output from bison or flex is non-GPL, AIUI. Any input *into* bison, which is GPL'd before processing, *is* restricted. Given that bison doesn't take .h files as input, doesn't it make sense that this was produced *by* bison, and thus unrestricted? ahp ---(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] [SQL] HeapTuple-t_tableOid==0 after SPI_exec
[EMAIL PROTECTED] writes: When HeapTuple is populated by SPI_exec(select * from foobar when id=667); tuple = SPI_tuptable-tvals[0] (id is PK and row with 667 exists) then tuple-t_tableOid is always 0. The result of a SELECT is never a raw table tuple, not even when it's a straight select * from foo. It's a constructed tuple that belongs to no particular table --- which makes sense because in general it wouldn't match any particular table's rowtype. I think in 7.4 there may be an optimization that skips the tuple projection step in this particular case, but if you can in fact see t_tableOid in 7.4, it'd be an implementation artifact rather than something we will promise to support in future. The correct way if you want to see tableoid is to select it: select tableoid,* from foobar where ... and then extract it from the result using the usual field-access routines. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [SQL] HeapTuple-t_tableOid==0 after SPI_exec
[EMAIL PROTECTED] writes: are there gonna be changes in SPI or internal structs in 7.4? No more than usual ;-). You will need to recompile shared libraries, but (in theory) source code changes shouldn't be needed. You might want to think about upgrading elog() calls to ereport() though. 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