[HACKERS] Firebird 1.0 released
The Firebird guys have gotten around to releasing 1.0. If you read this front page spiel, you'll notice that they use MVCC, but with an overwriting storage manager. http://www.ibphoenix.com/ibp_act_db.html The relevant extract: "Multi-version concurrency control uses back versions of modified and deleted records to maintain a consistent view of data for read transactions. Each record version is tagged with the identifier of the transaction that created it. When a record is modified, the old version of the record is reduced to a "delta record" - a set of differences from the new version - and written to a new location, ordinarily on the same page where it was originally stored. Then the new record overwrites the old. The new record points to the old record. Unless the values of indexed fields are changed, there's no need to update the index. Even if the values have changed, the old values remain in the index to keep the record available to older transactions. The transaction identifier also permits update transactions to recognize updates by concurrent transactions and allows Firebird to dispense with write locks on records. When a transaction encounters a record updated by a concurrent transaction, it waits for the other transaction to complete. If the competing transaction commits, the waiting transaction gets an error. If the competing transaction rolls back, the waiting transaction succeeds. If the competing transaction attempts to update a record that the waiting transaction has modified, a deadlock exists and one or the other will receive an error. Multi-version concurrency replaces a before-image (rollback) log with versions stored in the database. When a transaction fails, its changes remain in the database. The next transaction that reads that record recognizes that the record version is invalid. Depending on the version of Firebird and architecture, that transaction either replaces the invalid record version with its back version or invokes a garbage collect thread. " Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Places to update when a new version is out
I remember someone mentioning on the list that we should collect a list of places that refer to postgres so that we can update them for a new release. I just submitted an update on Linux.com: http://software.linux.com/projects/postgresql/?topic=323,324,325 That location should be added to the list. I think ZDNet was the other place. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Places to update when a new version is out
Hmmm...where's that file in the CVS where the release process is listed (or at least the places where version numbers need to be updated, etc.?) I can't find it... Chris > -Original Message- > From: Justin Clift [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, 16 April 2002 3:23 PM > To: Christopher Kings-Lynne > Cc: Hackers > Subject: Re: [HACKERS] Places to update when a new version is out > > > Hi Chris, > > I don't have time at the moment to start making the needed document. :( > > Does anyone want to throw together the basics of it and put it somewhere > useful? > > :-) > > Regards and best wishes, > > Justin Clift > > > Christopher Kings-Lynne wrote: > > > > I remember someone mentioning on the list that we should > collect a list of > > places that refer to postgres so that we can update them for a > new release. > > > > I just submitted an update on Linux.com: > > > > http://software.linux.com/projects/postgresql/?topic=323,324,325 > > > > That location should be added to the list. > > > > I think ZDNet was the other place. > > > > Chris > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." >- Indira Gandhi > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
> I have bitched about the index stuff for a while, and always have > bumped up > against this problem. If I can sway anyone's opinion, I would say, unless > (using Tom's words) a "factor of 2" planner difference against, I > would use an > index. Rather than needing clear evidence to use an index, I > would say you need > clear evidence not too. I spend a lot of time answering questions on various database forums and I find that the single thing that most newbies just cannot understand is that a sequential scan is often a lot faster than an index scan. They just cannot comprehend that an index can be slower. Ever. For any query. That is not our problem... What we could offer tho, is more manual control over the planner. People can do this to a mild extend by disabling sequential scans, but it looks like it should be extended... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
> If the DBA notices that there is a problem with a query, he adds > an index, he > notices that there is no difference, then he notices that > PostgreSQL is not > using his index. First and foremost he gets mad at PostgreSQL for > not using his > index. If PostgreSQL decided to use an index which increases > execution time, > the DBA would delete the index. If PostgreSQL does not use an > index, he has to > modify the posgresql.conf file, which disallows PostgreSQL from > using an index > when it would be a clear loser. > > My assertion is this: "If a DBA creates an index, he has a basis for his > actions." What about a GUC parameter prefer_indexes = yes/no Which when set to yes, assumes the DBA knows what he's doing. Unless the table is really small, in which case it'll still scan. But then again, if the dba sets up a huge table (million rows) and does a select over an indexed field that will return 1/6 of all the rows, then postgres would be nuts to use the index... But then if the DBA does a query to return just 1 of the rows, postgres would be nuts NOT to use the index. How do you handle this situation? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
> TODO updated: > > > * Add BETWEEN ASYMMETRIC/SYMMETRIC (Christopher) > > * Christopher is Christopher Kings-Lynne <[EMAIL PROTECTED]> So should I go ahead and submit a patch for BETWEEN that adds SYMMETRY support in the old-style code, and then at a later stage submit a patch that makes BETWEEN a proper node? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
> > So should I go ahead and submit a patch for BETWEEN that adds SYMMETRY > > support in the old-style code, and then at a later stage submit > a patch that > > makes BETWEEN a proper node? > > Sure, I think that makes sense. The larger BETWEEN node code will be > tricky. Question: Why have you created a special case for NOT BETWEEN? Wouldn't you just need a BETWEEN node and the NOT node will handle the NOTing? Or is it because BETWEEN isn't a node at the moment? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
> "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > So should I go ahead and submit a patch for BETWEEN that adds SYMMETRY > > support in the old-style code, and then at a later stage submit > a patch that > > makes BETWEEN a proper node? > > I'd prefer to do it in one step. I have not noticed any large > groundswell of demand for BETWEEN SYMMETRIC ... so I don't see a good > reason for implementing a stopgap version. (It would be a stopgap > mainly because the planner wouldn't recognize it as a range query.) OK, I'll go for the whole change - just expect lots of questions :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] updated qCache
> Neil Conway <[EMAIL PROTECTED]> writes: > > I'm planning to re-implement PREPARE/EXECUTE with support only > > for locally-prepared plans, using the existing patch as a > > guide. The result should be a simpler patch -- once it's > > in CVS we can worry about more advanced plan caching > > techiques. Any complaints/comments on this plan? > > That's what I wanted from day one ;-) So with this scheme, people just have to be careful to use a connection pool / persistent connections? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] updated qCache
> Neil Conway <[EMAIL PROTECTED]> writes: > > I'm planning to re-implement PREPARE/EXECUTE with support only > > for locally-prepared plans, using the existing patch as a > > guide. The result should be a simpler patch -- once it's > > in CVS we can worry about more advanced plan caching > > techiques. Any complaints/comments on this plan? > > That's what I wanted from day one ;-) You know, if we had a threaded backend, we wouldn't have any of these problems :) Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Is this an IN bug?
Is this a bug?
usa=# SELECT * FROM palm_buyers WHERE buyer_id=in('150',210) ;
ERROR: Function 'in(unknown, int4)' does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Oops!
Ignore my previous post - for obvious reasons!!! Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schema (namespace) privilege details
> Will we be able to accomplish the equivelent of the below? > > > knight# ls -la > total 3 > drwxr-xr-x 2 rbt rbt 512 Apr 18 21:53 . > drwxr-xr-x 43 rbt rbt2048 Apr 18 21:36 .. > -rwx-- 1 root wheel 0 Apr 18 21:53 file > > knight# head /etc/group > # $FreeBSD: src/etc/group,v 1.19.2.1 2001/11/24 17:22:24 gshapiro Exp > $ > # > wheel:*:0:root > daemon:*:1:daemon > kmem:*:2:root > sys:*:3:root > tty:*:4:root > operator:*:5:root > mail:*:6: > bin:*:7: > > knight# exit > exit > > bash-2.05a$ whoami > rbt > > bash-2.05a$ rm file > override rwx-- root/wheel for file? y > > bash-2.05a$ ls -la > total 3 > drwxr-xr-x 2 rbt rbt 512 Apr 18 21:55 . > drwxr-xr-x 43 rbt rbt 2048 Apr 18 21:36 .. That is, of course, a BSD-ism that would confuse a lot of the SysV people... :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Odd(?) RI-trigger behavior
> But note that this is on TODO: > > * Allow user to control trigger firing order > > That probably means that the user should have some reasonable way to > change the name, besides fiddling with system catalogs. An ALTER TRIGGER command? Of course, it should not allow modification of constraint triggers... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] commands subdirectory continued -code cleanup
> and two macros: > > RECURSE_OVER_CHILDREN(relid); > AlterTableDoSomething(childrel,...); > RECURSE_OVER_CHILDREN_END; > > (this seems more straightforward than passing the text of the function > call as a macro parameter). The above all looks fine. The other stuff I wouldn't really know about. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] few probs with integer timestamps
On FreeBSD/Alpha, current CVS: gmake -C common SUBSYS.o gmake[4]: Entering directory `/home/chriskl/pgsql/src/backend/access/common' gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../.. /../src/include -c -o heaptuple.o heaptuple.c -MMD In file included from ../../../../src/include/utils/timestamp.h:24, from ../../../../src/include/utils/nabstime.h:21, from ../../../../src/include/access/xact.h:19, from ../../../../src/include/utils/tqual.h:19, from ../../../../src/include/access/relscan.h:17, from ../../../../src/include/access/heapam.h:18, from heaptuple.c:23: ../../../../src/include/utils/int8.h:35: warning: `INT64CONST' redefined ../../../../src/include/utils/pg_crc.h:85: warning: this is the location of the previous definition gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../.. /../src/include -c -o indextuple.o indextuple.c -MMD In file included from ../../../../src/include/utils/timestamp.h:24, from ../../../../src/include/utils/nabstime.h:21, from ../../../../src/include/access/xact.h:19, from ../../../../src/include/utils/tqual.h:19, from ../../../../src/include/access/relscan.h:17, from ../../../../src/include/access/heapam.h:18, from indextuple.c:19: ../../../../src/include/utils/int8.h:35: warning: `INT64CONST' redefined ../../../../src/include/utils/pg_crc.h:85: warning: this is the location of the previous definition gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../.. /../src/include -c -o indexvalid.o indexvalid.c -MMD In file included from ../../../../src/include/utils/timestamp.h:24, from ../../../../src/include/utils/nabstime.h:21, from ../../../../src/include/access/xact.h:19, from ../../../../src/include/utils/tqual.h:19, from ../../../../src/include/access/relscan.h:17, from ../../../../src/include/nodes/execnodes.h:17, from ../../../../src/include/nodes/plannodes.h:17, from ../../../../src/include/executor/execdesc.h:19, from ../../../../src/include/executor/executor.h:17, from ../../../../src/include/executor/execdebug.h:17, from indexvalid.c:19: ../../../../src/include/utils/int8.h:35: warning: `INT64CONST' redefined ../../../../src/include/utils/pg_crc.h:85: warning: this is the location of the previous definition ---(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] pg_constraint
> For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT > NULL (specific type of check constraint) I intend to create (as per > suggestion) pg_constraint. Hmmm...I don't see the need at all for NOT NULL constraint tracking. The spec doesn't seem to require it and we do not have names for them anyway. Even if they were given names, it'd be pointless, as there's only one per column. Primary keys and unique keys are SQL constraints - are you going to bother tracking them as well or leave them in the current format? Maybe you could do it with a view or something. Why not just create a pg_references table and leave pg_relcheck as is? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Civility of core/hackers group
> We have been very fortunate to have avoided such problems since we > started six years ago, and I hope it never happens. There sure are a lot of arguments in the hackers list tho :) I do wish people would be a little less 'ad hominem' in their argument styles, however. It would be an interesting thing to consider what would happen to the Postgres project if Tom left one day... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Mac OS X: system shutdown prevents checkpoint
I showed this to my friend who's a FreeBSD committer (Adrian Chadd) and he's actually setting up a MacOS/X box at the moment and will look into it - assuming you don't discover the problem first... Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane > Sent: Tuesday, 30 April 2002 1:26 PM > To: [EMAIL PROTECTED] > Cc: Francois Suter > Subject: [HACKERS] Mac OS X: system shutdown prevents checkpoint > > > I've been looking into Francois Suter's recent reports of Postgres not > shutting down cleanly on Mac OS X 10.1. I find that it's quite > reproducible. If you tell the system to shut down in the normal > fashion (eg, pick "Shut Down" from the Apple menu), the postmaster > does not terminate, leading to WAL recovery upon restart --- or > even worse, failure to restart if the postmaster PID recorded in the > lockfile happens to get assigned to some other daemon. > > Observe the normal trace of postmaster shutdown (running with -d4, > logging of timestamps and PIDs enabled): > > 2002-04-30 00:08:30 [315]DEBUG: pmdie 15 > 2002-04-30 00:08:30 [315]DEBUG: smart shutdown request > 2002-04-30 00:08:30 [331]DEBUG: shutting down > 2002-04-30 00:08:32 [331]DEBUG: database system is shut down > 2002-04-30 00:08:32 [331]DEBUG: proc_exit(0) > 2002-04-30 00:08:32 [331]DEBUG: shmem_exit(0) > 2002-04-30 00:08:32 [331]DEBUG: exit(0) > 2002-04-30 00:08:32 [315]DEBUG: reaping dead processes > 2002-04-30 00:08:32 [315]DEBUG: proc_exit(0) > 2002-04-30 00:08:32 [315]DEBUG: shmem_exit(0) > 2002-04-30 00:08:32 [315]DEBUG: exit(0) > > The postmaster (here PID 315) forks a subprocess to flush shared buffers > and checkpoint the WAL log. When the subprocess exits, the postmaster > removes its lockfile and shuts down. The subprocess takes a minimum of > 2 seconds because there's a sleep(2) in the checkpoint fsync code. > > Now here's what I see in the case of shutting down the OS X system: > > 2002-04-30 00:25:35 [376]DEBUG: pmdie 15 > 2002-04-30 00:25:35 [376]DEBUG: smart shutdown request > > ... and nothing more. Actual system shutdown (power down) occurred at > approximately 00:26:06 by my watch, over thirty seconds later than the > postmaster received SIGTERM. So there was plenty of time to do the > checkpoint subprocess. (Indeed, I believe that thirty seconds is the > grace period Darwin's init process allows SIGTERM'd processes before > giving up and hard-killing them. So the system was actually sitting and > waiting for the postmaster.) > > What we appear to have here is that the kernel is not allowing the > postmaster to fork a checkpoint subprocess. But there's no indication > that the postmaster got a fork() error return, either. Seems like it's > just hung. > > Does this ring a bell with anyone? Is it an OSX bug, or a "feature"; > and if the latter, how can we work around it? > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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] [RFC] Set Returning Functions
> Do we want this feature? > - > Based on the many posts on this topic, I think the answer to this is a > resounding yes. Definitely! > How do we want the feature to behave? > - > A SRF should behave similarly to any other table_ref (RangeTblEntry), > i.e. as a tuple source in a FROM clause. Currently there are three > primary kinds of RangeTblEntry: RTE_RELATION (ordinary relation), > RTE_SUBQUERY (subquery in FROM), and RTE_JOIN (join). SRF would join > this list and behave in much the same manner. Yes - I don't see any point in adhering to the SQL standard lame definition. We can just make "CALL proc()" map to "SELECT * FROM proc()" in the parser for compliance. > How do we want the feature implemented? (my proposal) > - > 1. Add a new table_ref node type: > - Current nodes are RangeVar, RangeSubselect, or JoinExpr > - Add new RangePortal node as a possible table_ref. The RangePortal > node will be extented from the current Portal functionality. > > 2. Add support for three modes of operation to RangePortal: >a. Repeated calls -- this is the existing API for SRF, but > implemented as a tuple source instead of as an expression. >b. Materialized results -- use a TupleStore to materialize the > result set. >c. Return query -- use current Portal functionality, fetch entire > result set. > > 3. Add support to allow the RangePortal to materialize modes 1 and 3, if > needed for a re-read. Looks cool. That's stuff outta my league tho. > 4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be > specified. This would default to mode a) for backward compatibility. Interesting idea. Didn't occur to me that we could specify it on a per-function level. How do Oracle and Firebird do it? What about the issue of people maybe wanting different behaviours at different times? ie. statement level, rather than function level? > 5. Ignore the current code which allows functions to return multiple > results as expressions; we can leave it there, but deprecate it with the > intention of eventual removal. What does the current 'setof' pl/pgsql business actually _do_? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [INTERFACES] Schemas: status report, call for developers
> > JDBC and ODBC metadata code is certainly broken; so are the > > catalog lookups in pgaccess, pgadmin, and so on. psql and > > pg_dump are broken as well (though I will take responsibility > > for fixing pg_dump, and will then look at psql if no one else > > has done it by then). I'm not even sure what else might need > > to change. phpPgAdmin (WebDB) will be broken as well. I think myself and at least a few other committers lurk here tho. Other things that will break: TOra Various KDE interfaces Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] PureFTPd
Hi, PureFTPd has got really good Postgres support: Authenticates off postgres, with definable queries to return stuff like homedirs, quotas, password hashes, etc. Cool. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
> test=# CREATE USER tgl; > CREATE USER > test=# CREATE SCHEMA tgl AUTHORIZATION tgl; > CREATE What about "CREATE USER tgl WITH SCHEMA;" ? Which will implicitly do a "CREATE SCHEMA tgl AUTHORIZATION tgl;" Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas: status report, call for developers
> produces a result like this: > > schema | object > + > public | abc > foo| abc > foo| xyz > bar| xyz > (4 rows) > > How can I restrict the query to the schemas in the > current search path, i.e. the schema names returned > by SELECT current_schemas() ? Now, if we had functions-returning-sets, this would all be easy as all you'd need to do would be to join it with the function returning the set of schemas in your search path :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Problem with restoring a 7.1 dump
Hi all, I'm having problems restoring a dump. I get this: You are now connected as new user chriskl. ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Pre-installed languages are SQL, C, and internal. Additional languages may be installed using 'createlang'. ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Pre-installed languages are SQL, C, and internal. Additional languages may be installed using 'createlang'. I've done a "createlang plpgsql template1" before starting my restore, but I just cannot get it to recognise the language. The dump format is the complete one that first tries to drop each database and then recreates it from scratch, so each of my databases is being dropped and then totally recreated. How do I get this to work? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 3 digit year problem
Hi, Is there any rhyme or reason to these ISO format date parsing rules? test=# select '1-1-1'::date; ERROR: Bad date external representation '1-1-1' test=# select '69-1-1'::date; date 2069-01-01 (1 row) test=# select '50-1-1'::date; date 2050-01-01 (1 row) test=# select '40-1-1'::date; date 2040-01-01 (1 row) test=# select '30-1-1'::date; ERROR: Bad date external representation '30-1-1' test=# select '100-1-1'::date; ERROR: Bad date external representation '100-1-1' test=# select '999-1-1'::date; ERROR: Bad date external representation '999-1-1' test=# select '1000-1-1'::date; date 1000-01-01 (1 row) Why can't someone store the year without having to pad with zeros for years between 100 and 999? What's wrong with 30-1-1 and below? Why does 40 work and not 30? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
> > Rather than propagating the SysV semaphore API still further, why don't > > we kill it now? (I'm willing to keep the shmem API, however.) > > Would this have the benefit of allow PostgreSQL to work properly in BSD > jails, since lack of really working SysV IPC was the problem there? I have postgresql working quite happily in FreeBSD jails! (Just make sure you go "sysctl jail.sysvipc_allowed=1"). Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
> (For others: apparently PG will work under BSD jails if you recompile the > BSD kernel w/some new settings, but my ISP for this project was > unwilling to > do that. Search the mailing list for messages on how to do this.) Works fine. You don't need to recompile - just use the sysctl. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Number of attributes in HeapTupleHeader
> IMHO, the current ADD COLUMN mechanism is a hack. Besides requiring > redundant on-disk data (t_natts), it isn't SQL compliant (because > default values or NOT NULL can't be specified), and depends on > a low-level kludge (that the storage system will return NULL for > any attnums > the # of the attributes stored in the tuple). > > While instantaneous ADD COLUMN is nice, I think it's counter- > productive to not take advantage of a storage space optimization > just to preserve a feature that is already semi-broken. I actually started working on modifying ADD COLUMN to allow NOT NULL and DEFAULT clauses. Tom's idea of having col > n_atts return the default instead of NULL is cool - I didn't think of that. My changes would have basically made the plain add column we have at the moment work instantly, but if they specified NOT NULL it would touch every row. That way it's up to the DBA which one they want (as good HCI should always do). However, now that my SET/DROP NOT NULL patch is in there, it's easy to do the whole add column process, just in a transaction: BEGIN; ALTER TABLE foo ADD bar int4; UPDATE foo SET bar=3; ALTER TABLE foo ALTER bar SET NOT NULL; ALTER TABLE foo SET DEFAULT 3; ALTER TABLE foo ADD FOREIGN KEY (bar) REFERENCES (noik); COMMIT; With the advantage that you have full control over every step... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
> I forwarded the suggestion to my ISP (imeme, a Zope provider), who said > that: > > "This will allow you to run a single postgres in a single jail only one > user would have access to it. If you try to run more then one it will > try to use the same shared memory and crash." Not true. But I'll avoid digging up any more on that old issue... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Set Returning Functions (SRF) - request for patch review and comment
Feedback: you're a legend!
I'll try to patch my CVS and test it at some point...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Joe Conway
> Sent: Tuesday, 7 May 2002 12:51 AM
> To: pgsql-hackers
> Subject: [HACKERS] Set Returning Functions (SRF) - request for patch
> review and comment
>
>
> I've been buried in the backend parser/planner/executor now for the last
> 2 weeks or so, and I now have a patch for a working implementation of
> SRFs as RTEs (i.e. "SELECT tbl.* FROM myfunc() AS tbl"). I think I'm at
> a good point to get review and comments. Not everything yet has been
> implemented per my proposal (see:
> http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
> support is in place.
>
> How it currently works:
> ---
> 1. At this point, FROM clause SRFs are used as a row source in a manner
> similar to the current API, i.e. one row at a time is produced without
> materializing.
>
> 2. The SRF may be either marked as returning a set or not. A function
> not marked as returning a set simply produces one row.
>
> 3. The SRF may either return a base data type (e.g. TEXT) or a composite
> data type (e.g. pg_class). If the function returns a base data type, the
> single result column is named for the function. If the function returns
> a composite type, the result columns get the same names as the
> individual attributes of the type.
>
> 4. The SRF *must* be aliased in the FROM clause. This is similar to the
> requirement for a subselect used in the FROM clause.
>
> 5. example:
> test=# CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
> key(fooid,foosubid));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> 'foo_pkey' for table 'foo'
> CREATE
> test=# INSERT INTO foo VALUES(1,1,'Joe');
> INSERT 16693 1
> test=# INSERT INTO foo VALUES(1,2,'Ed');
> INSERT 16694 1
> test=# INSERT INTO foo VALUES(2,1,'Mary');
> INSERT 16695 1
> test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
> foo WHERE fooid = $1;' LANGUAGE SQL;
> CREATE
> test=# SELECT * FROM getfoo(1) AS t1;
> fooid | foosubid | fooname
> ---+--+-
> 1 |1 | Joe
> 1 |2 | Ed
> (2 rows)
>
> test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
> fooname
> -
> Joe
> (1 row)
>
> test=# select * from dblink_get_pkey('foo') as t1;
> dblink_get_pkey
> -
> fooid
> foosubid
> (2 rows)
>
> What still needs to be done:
>
> 1. Add a new table_ref node type - DONE
> 2. Add support for three modes of operation to RangePortal:
> a. Repeated calls -- DONE
> b. Materialized results -- partially complete
> c. Return query -- I'm starting to wonder how/if this is really
>different than a.) above
> 3. Add support to allow the RangePortal to materialize modes a and c,
> if needed for a re-read -- partially complete.
> 4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
> specified -- not yet started.
>
>
> Request for help:
> -
> So far I've tested with SQL and C functions. I will also do some testing
> with PLpgSQL functions. I need testing and feedback from users of the
> other function PLs.
>
> Review, comments, feedback, etc. are appreciated.
>
> Thanks,
>
> Joe
>
>
>
---(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] non-standard escapes in string literals
> It is my experience that most other free software projects take > standards compliance more seriously than PostgreSQL, and my strong > opinion that both the project and its users (not to mention the > whole SQL database industry, eventually) would benefit from better > support for the SQL standard. Ummm - I think you'd be hard pressed to find a open source db team more committed to standards compliance. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] TRUNCATE
> I'm happy w/o the FORCE option (just let TRUNCATE do it), but if enough > people think that the FORCE keyword should be added to allow overriding of > triggers, that could be a good compromise. > > But, please, don't take away the ability to TRUNCATE. Doing it when there > are triggers is one the strengths of TRUNCATE, IMNSHO. It seems to me that there's more and more need for an 'SET CONSTRAINTS DISABLED' and 'SET CONSTRAINTS ENABLED' command that affects only foreign keys. This would basically make it ignore foreign key checks for the remainder of the transaction. This could be used before a TRUNCATE command, and would also be essential when we switch to dumping ALTER TABLE/FOREIGN KEY commands in pg_dump, and we don't want them to be checked... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] TRUNCATE
> > It seems to me that there's more and more need for an 'SET CONSTRAINTS > > DISABLED' and 'SET CONSTRAINTS ENABLED' command that affects > only foreign > > keys. > > I really dislike the idea of referring to "constraints" but only affecting > foreign key constraints. All the other SET CONSTRAINTS statments refer only to foreign keys... > And what would be the security/data-integrity ramifications of allowing > this? Well, if only super users could do it... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Discontent with development process (was:Re: [HACKERS] pgaccess
> Actually, even for those that wuldn't need the patch ... as long as the > "default behaviour" doesn't change, and unless there are no valid > technical arguments around it, there is no reason why a patch shouldn't be > included ... Unless it's going to interfere with implementing the general case in the future, making it a painful feature to keep backwards-compatibility with. Which is what the discussion was about IIRC... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] resetting stats on the fly
Hi All, I can't see that there's any way to reset the stats collector without HUPing the postmaster? Is there? Should there be? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] WIN32 native ... lets start?!?
Maybe Vince could set up a Win32 porting project page, and since we now seem to have a few interested parties willing to code on a native Win32 version, they should have their own project page. This could make communication easier for them and make sure the project doesn't die... Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Joerg > Hessdoerfer > Sent: Friday, 17 May 2002 4:36 AM > To: Magnus Naeslund(f) > Cc: [EMAIL PROTECTED] > Subject: Re: [HACKERS] WIN32 native ... lets start?!? > > > On Thursday 16 May 2002 22:10, you wrote: > [...] > > > > What is the biggest problem here? > > The Shmem/IPC stuff, or the fork() stuff? > > I'm think that we could do a fork() implementation in usermode > by copying > > the memory allocations. How fast that would be regarding the context > > switches, i don't know, but i'm willing to experiment some to see how > > feesible this is... > > > > Anyone tried this before? > > > > Magnus > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > The problem is not the fork() call itself, this has been done (MinGW and > cygwin I know of, possibly others) but the speed of fork() on > windows, it's > creepingly slow (due to usermode copy, I assume ;-). > > IPC needs to be done, I'm just about to start... > > Greetings, > Joerg > -- > Leading SW developer - S.E.A GmbH > Mail: [EMAIL PROTECTED] > WWW: http://www.sea-gmbh.com > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Me in California
Hi guys, Just in case anyone is around, I've recently arrived in Costa Mesa, California from Australia for a couple of weeks on business. So, if anyone's in the area - it might be cool to catch up... Chris
Re: [HACKERS] Think I see a btree vacuuming bug
Well, given that vacuum does its work in the background now - I think you'll be hard pressed to find a sys admin who'll vote for leaving it as is, no matter how small the chance of corruption. However - this isn't my area of expertise... Chris - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, May 25, 2002 11:21 AM Subject: [HACKERS] Think I see a btree vacuuming bug > If a VACUUM running concurrently with someone else's indexscan were to > delete the index tuple that the indexscan is currently stopped on, then > we'd get a failure when the indexscan resumes and tries to re-find its > place. (This is the infamous "my bits moved right off the end of the > world" error condition.) What is supposed to prevent that from > happening is that the indexscan retains a buffer pin (but not a read > lock) on the index page containing the tuple it's stopped on. VACUUM > will not delete any tuple until it can get a "super exclusive" lock on > the page (cf. LockBufferForCleanup), and the pin prevents it from doing > so. > > However: suppose that some other activity causes the index page to be > split while the indexscan is stopped, and that the tuple it's stopped > on gets relocated into the new righthand page of the pair. Then the > indexscan is holding a pin on the wrong page --- not the one its tuple > is in. It would then be possible for the VACUUM to arrive at the tuple > and delete it before the indexscan is resumed. > > This is a pretty low-probability scenario, especially given the new > index-tuple-killing mechanism (which renders it less likely that an > indexscan will stop on a vacuum-able tuple). But it could happen. > > The only solution I've thought of is to make btbulkdelete acquire > "super exclusive" lock on *every* leaf page of the index as it scans, > rather than only locking the pages it actually needs to delete something > from. And we'd need to tweak _bt_restscan to chain its pins (pin the > next page to the right before releasing pin on the previous page). > This would prevent a btbulkdelete scan from overtaking ordinary > indexscans, and thereby ensure that it couldn't arrive at the tuple > on which an indexscan is stopped, even with splitting. > > I'm somewhat concerned that the more stringent locking will slow down > VACUUM a good deal when there's lots of concurrent activity, but I don't > see another answer. Ideas anyone? > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Small changes to facilitate Win32 port
It's more likely that your changes will go through if you just submit a patch! cvs diff -c Chris - Original Message - From: "Katherine Ward" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 30, 2002 2:33 PM Subject: [HACKERS] Small changes to facilitate Win32 port > Hi there. I'm yet another developer working full-time on a native windows > port. I'm also working closely with Jan Wieck (next office). I know there is > a reluctance to modify the code base to support native win32, and I realize > that no decision has yet been made. However, ... > > A few of the identifier names used in postgres collide with WIN32 or MFC names. > To keep my working copy of the code as close to the released source as > possible, I do have some superficial changes that I would like to put in the > code base early: > > 1. Rename to avoid structures/functions with same name: > a. PROC => PGPROC > b. GetUserName() => GetUserNameFromId() > c. GetCurrentTime() => GetCurrentDateTime() > > 2. Add _P to the following lex/yacc tokens to avoid collisions > CONST, CHAR, DELETE, FLOAT, GROUP, IN, OUT > > 3. Rename two local macros > a. MEM_FREE => MEM_FREE_IT in backend/utils/hash/dynahash.c > b. IGNORE => IGNORE_TOK in include/utils/datetime.h & > backend/utils/adt/datetime.c > > Thanks, > Katie Ward > [EMAIL PROTECTED] > > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/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] Small changes to facilitate Win32 port
> Christopher Kings-Lynne wrote: > > It's more likely that your changes will go through if you just submit a > > patch! > > I suggested to discuss it first, since it's IMHO more likely > that the changes go through if they are commonly accepted in > the first place. Yep - sorry, didn't pick up on that... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] SRF rescan testing
> (OTOH one could make a good argument that now is the time to do it > if we're ever gonna do it --- clients that are not schema-aware will > be badly in need of work anyway for 7.3...) Maybe the attisdropped column should be created and added to the pg_attribute catalog now as well. It would always be false, but would mean only 1 round of mad postgres admin program hacking... Might be able to avoid catalog changes for a drop column implementation in 7.4... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] make_ctags problem
FreeBSD man page for ctags: -d Create tags for #defines that do not take arguments; #defines that take arguments are tagged automatically. -t Create tags for typedefs, structs, unions, and enums. Chris - Original Message - From: "Mattew T. O'Connor" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, June 02, 2002 1:12 AM Subject: [HACKERS] make_ctags problem > Hello, I have been trying to get the make_ctags script working. On Redhat 7.3 > (that is all I have access to at the moment.) the script generates the > following output: > > [matthew@zeutrh73 src]$ pwd > /home/matthew/src/pgsql/src > [matthew@zeutrh73 src]$ ./tools/make_ctags > ctags: Unknown option: -d > ctags: Unknown option: -d > ctags: Unknown option: -d > sort: open failed: tags: No such file or directory > [matthew@zeutrh73 src]$ cat /etc/redhat-release > Red Hat Linux release 7.3 (Valhalla) > [matthew@zeutrh73 src]$ ctags --version > Exuberant Ctags 5.2.2, Copyright (C) 1996-2001 Darren Hiebert > Compiled: Feb 26 2002, 04:51:30 > Addresses: <[EMAIL PROTECTED]>, http://ctags.sourceforge.net > Optional compiled features: +wildcards, +regex > > The ./tags file created is not created, so all the symlinks created throught > the source tree are broken. > > The make_ctags script runs without error if I change line 5 > > from: > -type f -name '*.[chyl]' -print|xargs ctags -d -t -a -f tags > to: > -type f -name '*.[chyl]' -print|xargs ctags -a -f tags > > The man page for ctags does not list -d or -t as valid options. > > Am I doing somthing wrong? Something with the version of ctags provided by > Redhat? > > Also when I attempt to use the tags file what was created by the modified > make_ctags script, I get the following errors from vi: > > E432: Tags file not sorted: tags > E426: tag not found: BUFFER_LOCK_UNLOCK > > Matthew > > ---(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] revised sample SRF C function; proposed SRF API
> Tom Lane wrote: > > Well, we're not doing that; and I see no good reason to make the thing > > be a builtin function at all. Since it's just an example, it can very > > well be a contrib item with a creation script. Probably *should* be, > > in fact, because dynamically created functions are what other people are > > going to be building; an example of how to do it as a builtin function > > isn't as helpful. > > True enough, although I could always create another example for contrib. > Returning GUC variable "SHOW ALL" results as a query result has been > discussed before, and I thought there was agreement that it was a > desirable backend feature. Sure would be. Means we can show config variables nicely in phpPgAdmin like phpMyAdmin does... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Efficient DELETE Strategies
> Given the plethora of mutually incompatible interpretations that MSSQL > evidently supports, though, I fear we can't use it as precedent for > making any choices :-(. > > Can anyone check out other systems? MySQL: 6.4.6 DELETE Syntax DELETE [LOW_PRIORITY | QUICK] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT rows] or DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...] FROM table-references [WHERE where_definition] or DELETE [LOW_PRIORITY | QUICK] FROM table_name[.*], [table_name[.*] ...] USING table-references [WHERE where_definition] DELETE deletes rows from table_name that satisfy the condition given by where_definition, and returns the number of records deleted. If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE. See section 6.4.7 TRUNCATE Syntax. In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records. If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form: mysql> DELETE FROM table_name WHERE 1>0; Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time. If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table. If you specify the word QUICK then the table handler will not merge index leaves during delete, which may speed up certain kind of deletes. In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section 4.4.6.10 Table Optimisation. The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2. The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching. The .* after the table names is there just to be compatible with Access: DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id or DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id In the above case we delete matching rows just from tables t1 and t2. ORDER BY and using multiple tables in the DELETE statement is supported in MySQL 4.0. If an ORDER BY clause is used, the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example: DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp LIMIT 1 This will delete the oldest entry (by timestamp) where the row matches the WHERE clause. The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Mac OS X shutdown
We've got an OSX machine set up now, however we haven't had time to look into the problem yet. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of David Santinoli > Sent: Tuesday, 11 June 2002 12:48 AM > To: [EMAIL PROTECTED] > Subject: [HACKERS] Mac OS X shutdown > > > > Hello, > is there any news about the Mac OS X shutdown issue? > It was discussed in a few April-May/2002 messages with the Subject > "Mac OS X: system shutdown prevents checkpoint". In short, during a > regular system shutdown on Mac OS X the postmaster is not terminated > gracefully, leading to troubles at the successive startup. > All OS X release I know of, up to the latest one (10.1.5), are prone to > this inconvenient. > > Thanks, > David > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature request: Truncate table
> > What is a TRUNCATE TABLE but a drop create anyway? Is there some > > technical difference? > > > It doesn't kill indexes/triggers/constraints/Foreign Key Stuff, etc. Hrm - last time I checked it did... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Regression Test Report
Just so you know, current CVS HEAD passes all tests on FreeBSD/Alpha (a 64bit machine) with this configure: ./configure --prefix=/home/chriskl/local --enable-integer-datetimes --enable -debug --enable-depend --enable-cassert --with-pam --with-openssl --with-CXX Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Feature request: Truncate table
> > Hrm - last time I checked it did... > > Two questions : > > When was the last time ? 7.1 > It did what ? Drops triggers and stuff. OK, I did a check and it looks like it's fixed in 7.2 at least. Sorry for the false alarm... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Making serial survive pg_dump
> Currently serial is dumped as a sequence and appropriate default > statement. > > With my upcoming dependency patch serials depend on the appropriate > column. Drop the column (or table) and the sequence goes with it. > The depencency information does not survive the pg_dump / restore > process however as it's recreated as the table and individual > sequence. What happens is the sequence is shared between several tables (eg. invoice numbers or something) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Making serial survive pg_dump
> I think that when SERIAL is used, the sequence should be tied > inextricably to the table which created it, and it should be hidden from > use for other purposes (perhaps similar to the way a toast table is). If > you *want* to use a sequence across several tables, then you don't use > SERIAL, you create a sequence. Agreed. Maybe an extra column in pg_attribute or something? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Patches for LOCALTIME and regexp, feature list
You wrote "was either to voluminous" instead of "was either too voluminous" in the first paragraph of the appendix... Chris - Original Message - From: "Thomas Lockhart" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers List" <[EMAIL PROTECTED]> Sent: Saturday, June 15, 2002 1:16 PM Subject: [HACKERS] Patches for LOCALTIME and regexp, feature list > I've just committed changes to include an SQL99 feature list as an > appendix in the User's Guide. While preparing that I noticed a feature > or two which would be trivial to implement, so we now have LOCALTIME and > LOCALTIMESTAMP function calls per spec (afaict; the spec is very vague > on the behaviors). > > I've also removed the ODBC-compatible parentheses on CURRENT_TIMESTAMP > etc and made sure that the ODBC driver handles the case correctly. > > More details from the CVS logs are below... > > - Thomas > > Add LOCALTIME and LOCALTIMESTAMP functions per SQL99 standard. > Remove ODBC-compatible empty parentheses from calls to SQL99 functions > for which these parentheses do not match the standard. > Update the ODBC driver to ensure compatibility with the ODBC standard > for these functions (e.g. CURRENT_TIMESTAMP, CURRENT_USER, etc). > Include a new appendix in the User's Guide which lists the labeled > features > for SQL99 (the labeled features replaced the "basic", "intermediate", > and "advanced" categories from SQL92). features.sgml does not yet split > this list into "supported" and "unsupported" lists. > Search the existing regular expression cache as a ring buffer. > Will optimize the case for repeated calls for the same expression, > which seems to be the most common case. Formerly, always searched > from the first entry. > May want to look at the least-recently-used algorithm to make sure it > is identifying the right slots to reclaim. Seems silly to do math when > it seems that we could simply use an incrementing counter... > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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] [COMMITTERS] pgsql/src/backend/parser gram.y
OK, the offending code is this:
case AF_INET6:
sin6 = (struct sockaddr_in6 *) &addr;
for (s = h->h_addr_list; *s != NULL; s++)
{
if (!memcmp(sin6->sin6_addr.s6_addr8, *s,
h->h_length))
return 0;
}
break;
It seems that this is how sin6_addr's type is defined:
struct in6_addr {
u_int8_ts6_addr[16];
};
so it's s6_addr, NOT s6_addr8. Is it still the same type?
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher
> Kings-Lynne
> Sent: Wednesday, 19 June 2002 2:30 PM
> To: Bruce Momjian
> Cc: Thomas Lockhart; Bruce Momjian - CVS;
> [EMAIL PROTECTED]
> Subject: Re: [COMMITTERS] pgsql/src/backend/parser gram.y
>
>
> Can you give me a hint where to look? I've looked at the code
> and throught
> he man pages and can't find the actual structure documented. It's version
> 4.4 of FreeBSD, and it has ipv6 compiled in (ifconfig -a proves that).
>
> I'm searching /usr/src right now...
>
> Chris
>
> > -Original Message-
> > From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, 19 June 2002 2:16 PM
> > To: Christopher Kings-Lynne
> > Cc: Thomas Lockhart; Bruce Momjian - CVS;
> > [EMAIL PROTECTED]
> > Subject: Re: [COMMITTERS] pgsql/src/backend/parser gram.y
> >
> >
> > Christopher Kings-Lynne wrote:
> > > CVS HEAD does not compile on FreeBSD/Alpha:
> > >
> > > ./configure --prefix=/home/chriskl/local
> > --enable-integer-datetimes --enable
> > > -debug --enable-depend --enable-cassert --with-pam --with-CXX
> > --with-openssl
> > >
> > > Gives:
> > >
> > > gmake[3]: Entering directory
> > `/home/chriskl/pgsql-head/src/interfaces/libpq'
> > > gcc -pipe -O -g -Wall -Wmissing-prototypes
> > -Wmissing-declarations -fpic -DPI
> > > C -I. -I../../../src/include -DFRONTEND
> > -DSYSCONFDIR='"/home/chriskl/local/
> > > etc/postgresql"' -c -o fe-secure.o fe-secure.c -MMD
> > > fe-secure.c: In function `verify_peer':
> > > fe-secure.c:417: structure has no member named `s6_addr8'
> > > gmake[3]: *** [fe-secure.o] Error 1
> > > gmake[3]: Leaving directory
> > `/home/chriskl/pgsql-head/src/interfaces/libpq'
> > > gmake[2]: *** [all] Error 2
> > > gmake[2]: Leaving directory `/home/chriskl/pgsql-head/src/interfaces'
> > > gmake[1]: *** [all] Error 2
> > > gmake[1]: Leaving directory `/home/chriskl/pgsql-head/src'
> > > gmake: *** [all] Error 2
> >
> > Of course, it compiles if you disable SSL. :-)
> >
> > Can you look at that line and see if there is something in your OS that
> > matches it? I have KAME here and I thought FreeBSD would have that too.
> >
> > --
> > Bruce Momjian| http://candle.pha.pa.us
> > [EMAIL PROTECTED] | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup.| Drexel Hill,
> Pennsylvania 19026
> >
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(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] [COMMITTERS] pgsql/src/backend/parser gram.y
Althought that is in the contrib/bind directory. Searching again...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher
> Kings-Lynne
> Sent: Wednesday, 19 June 2002 2:41 PM
> To: Bruce Momjian; Hackers
> Subject: Re: [HACKERS] [COMMITTERS] pgsql/src/backend/parser gram.y
>
>
> OK, the offending code is this:
>
> case AF_INET6:
> sin6 = (struct sockaddr_in6 *) &addr;
> for (s = h->h_addr_list; *s != NULL; s++)
> {
> if (!memcmp(sin6->sin6_addr.s6_addr8, *s,
> h->h_length))
> return 0;
> }
> break;
>
>
> It seems that this is how sin6_addr's type is defined:
>
> struct in6_addr {
> u_int8_ts6_addr[16];
> };
>
> so it's s6_addr, NOT s6_addr8. Is it still the same type?
>
> Chris
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher
> > Kings-Lynne
> > Sent: Wednesday, 19 June 2002 2:30 PM
> > To: Bruce Momjian
> > Cc: Thomas Lockhart; Bruce Momjian - CVS;
> > [EMAIL PROTECTED]
> > Subject: Re: [COMMITTERS] pgsql/src/backend/parser gram.y
> >
> >
> > Can you give me a hint where to look? I've looked at the code
> > and throught
> > he man pages and can't find the actual structure documented.
> It's version
> > 4.4 of FreeBSD, and it has ipv6 compiled in (ifconfig -a proves that).
> >
> > I'm searching /usr/src right now...
> >
> > Chris
> >
> > > -Original Message-
> > > From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, 19 June 2002 2:16 PM
> > > To: Christopher Kings-Lynne
> > > Cc: Thomas Lockhart; Bruce Momjian - CVS;
> > > [EMAIL PROTECTED]
> > > Subject: Re: [COMMITTERS] pgsql/src/backend/parser gram.y
> > >
> > >
> > > Christopher Kings-Lynne wrote:
> > > > CVS HEAD does not compile on FreeBSD/Alpha:
> > > >
> > > > ./configure --prefix=/home/chriskl/local
> > > --enable-integer-datetimes --enable
> > > > -debug --enable-depend --enable-cassert --with-pam --with-CXX
> > > --with-openssl
> > > >
> > > > Gives:
> > > >
> > > > gmake[3]: Entering directory
> > > `/home/chriskl/pgsql-head/src/interfaces/libpq'
> > > > gcc -pipe -O -g -Wall -Wmissing-prototypes
> > > -Wmissing-declarations -fpic -DPI
> > > > C -I. -I../../../src/include -DFRONTEND
> > > -DSYSCONFDIR='"/home/chriskl/local/
> > > > etc/postgresql"' -c -o fe-secure.o fe-secure.c -MMD
> > > > fe-secure.c: In function `verify_peer':
> > > > fe-secure.c:417: structure has no member named `s6_addr8'
> > > > gmake[3]: *** [fe-secure.o] Error 1
> > > > gmake[3]: Leaving directory
> > > `/home/chriskl/pgsql-head/src/interfaces/libpq'
> > > > gmake[2]: *** [all] Error 2
> > > > gmake[2]: Leaving directory
> `/home/chriskl/pgsql-head/src/interfaces'
> > > > gmake[1]: *** [all] Error 2
> > > > gmake[1]: Leaving directory `/home/chriskl/pgsql-head/src'
> > > > gmake: *** [all] Error 2
> > >
> > > Of course, it compiles if you disable SSL. :-)
> > >
> > > Can you look at that line and see if there is something in
> your OS that
> > > matches it? I have KAME here and I thought FreeBSD would
> have that too.
> > >
> > > --
> > > Bruce Momjian| http://candle.pha.pa.us
> > > [EMAIL PROTECTED] | (610) 853-3000
> > > + If your life is a hard drive, | 830 Blythe Avenue
> > > + Christ can be your backup.| Drexel Hill,
> > Pennsylvania 19026
> > >
> >
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>
> ---(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 4: Don't 'kill -9' the postmaster
[HACKERS] RULE regression failure on freebsd/alpha
This is the problem: *** ./expected/rules.out Fri May 3 08:32:19 2002--- ./results/rules.out Sun Jun 23 14:08:37 2002** 1005,1012 SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail +--++--+-- sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2 rows) CREATE TABLE shoelace_log (--- 1005,1012 SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail +--++--+- sh3 | 4 | sl7 | 7 | 4+ sh1 | 2 | sl1 | 5 | 2 (2 rows) CREATE TABLE shoelace_log ( ==
[HACKERS] Code questions
Hi All, Whereabouts in the code is the '*' expanded into the list of valid columns and also where are the columns specified in the select arguments (or whereever) checked for validity? Chris
Re: [HACKERS] pg_dump and ALTER TABLE / ADD FOREIGN KEY
> Some have expressed that this could be quite slow for large databases, > and want a type of: > > SET CONSTRAINTS UNCHECKED; > > However, others don't believe constraints other than foreign keys > should go unchecked. Well, at the moment remember taht all that other SET CONSTRAINTS commands only affect foreign keys. However, this is a TODO to allow deferrable unique constraints. > Or would the below be more appropriate?: > ALTER TABLE tab ADD FOREIGN KEY TRUST EXISTING DATA; Maybe instead of TRUST EXISTING DATA, it could be just be WITHOUT CHECK or something that uses existing keywords? Either way, it must be a superuser-only command. I'm kinda beginning to favour the latter now actually... Except if we could make all constraints uncheckable, then restoring a dump would be really fast (but risky!) Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Idea for the statistics collector
I was thinking of writing a command line tool like 'pgtune' that looks at the stats views and will generate SQL code for, or do automatically the following: * Dropping indices that are never used * Creating appropriate indices to avoid large, expensive sequential scans. This would put us in the 'mysql makes my indices for me by magic' league - but would be far more powerful and flexible. How to do multikey indices is beyond me tho. *sigh* I'm recovering from a septoplasty on my nose atm, so I might have some time to do some coding! Chris - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Martijn van Oosterhout" <[EMAIL PROTECTED]> Cc: "PostgreSQL-development" <[EMAIL PROTECTED]> Sent: Friday, June 21, 2002 10:50 AM Subject: Re: [HACKERS] [GENERAL] Idea for the statistics collector > Martijn van Oosterhout wrote: > > Since it's currently all for collecting statistics on tables, why can't it > > collect another type of statistic, like: > > > > - How often the estimator gets it wrong? > > > > At the end of an index scan, the executor could compare the number of rows > > returned against what was estimated, and if it falls outside a certain > > range, flag it. > > > > Also, the average ratio of rows coming out of a distinct node vs the number > > going in. > > > > For a join clause, the amount of correlation between two columns (hard). > > > > etc > > > > Ideally, the planner could then use this info to make better plans. > > Eventually, the whole system could become somewhat self-tuning. > > > > Does anyone see any problems with this? > > [ Discussion moved to hackers.] > > I have thought that some type of feedback from the executor back into > the optimizer would be a good feature. Not sure how to do it, but your > idea makes sense. It certainly could update the table statistics after > a sequential scan. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestions for implementing IS DISTINCT FROM?
> Please, please, do not do that. Make a new expression node tree type, > instead. We've made this mistake before (eg for BETWEEN) and I don't > want to do it again. I've actually already done almost all the work for converting BETWEEN to a node but I have a couple of questions: Should I use a boolean in the node to indicate whether it is SYMMETRIC or ASYMMETRIC, or should I use some sort of integer to indicate whether it is SYMMETRIC, ASYMMETRIC or DEFAULT (ASYMMETRIC). That way the reverse in rules and views could leave out the ASYMMETRIC if it wasn't specified originally, rather than always adding it in. Which is better? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nonrecursive ALTER TABLE ADD/RENAME COLUMN is wrong
> The second seems more user-friendly but also seems to violate the > principle of least surprise. Anyone have an opinion about what to do? Sounds like a logical argument, given normal OO behaviour. Hope it inspires someone to implement DROP COLUMN :) Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Democracy and organisation : let's make a revolution in
> OK, I want to know, does anyone see MySQL gaining in market share in > comparison to PostgreSQL, or is MySQL gaining against other databases? > Is MySQL gaining sites faster than we are gaining sites? > > Every indication I can see is that PostgreSQL is gaining on MySQL. > > The Linux/FreeBSD comparison is potent. Does PostgreSQL remain a niche > player? Does *BSD remain a niche player? In all honestly, I think that MySQL simply expands the market for Postgres. MySQL is widely promoted by every idiot out there. So everyone and their dog starts using MySQL. Then about 6 months later they realise it sucks (which is _exactly_ what happened at our business) and then they switch to Postgres. Every day on PHPBuilder's SQL forum there is someone asking why their subselect doesn't work in MySQL and how hard is it to migrate from MySQL. In fact, probably the best thing we can offer is an _excellent_ MySQL to PostgreSQL conversion tool. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Democracy and organisation : let's make a revolution
> What other development options do we have for soemthing that is GUI and > portable to all platforms that postgresql runs on? Java? wxWindows? Qt? > Gtk? I would think that Gtk is probably the most portable, and it has > bindings to many languages, but we would probalby want to use C. TOra uses QT and is cool. Unfortunately Windows version costs money. It is utterly, totally awesome though. Don't know how good its Postgres support is working at the moment, tho. http://www.globecom.se/tora/ Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Democracy and organisation : let's make a
> > I wasn't really comparing to MySQL here. I meant, in relationship > > to MS Access. Start it up and it just works. > > Yeah, a point-and-drool installation wizard for postgres under windows > would be great. I think, from looking at PGAdminII, that we've already > got great admin tools; it seems just as good as SQL Server Enterprise > Manager to me. Once we have a proper Win32 native version, the guy in our office who writes the Win32 installers for our Palm/PocketPC software said he'll do one for us no sweat. We use the free WinAmp installer which is really good... Says it only takes a couple of days... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Democracy and organisation : let's make a
> > Yeah, a point-and-drool installation wizard for postgres under windows > > would be great. I think, from looking at PGAdminII, that we've already > > got great admin tools; it seems just as good as SQL Server Enterprise > > Manager to me. > > Once we have a proper Win32 native version, the guy in our office who writes > the Win32 installers for our Palm/PocketPC software said he'll do one for us > no sweat. We use the free WinAmp installer which is really good... Says it > only takes a couple of days... BTW - here is the URL: http://www.nullsoft.com/free/nsis/ Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User-friendliness for DROP RESTRICT/CASCADE
> DROP TYPE widget RESTRICT; -- fail > NOTICE: operator <% depends on type widget > NOTICE: operator >% depends on type widget > NOTICE: operator >=% depends on type widget > ERROR: Cannot drop type widget because other objects depend on it > Use DROP ... CASCADE to drop the dependent objects too > > Any objections? That looks pretty sweet to me... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Support (was: Democracy and organisation)
Hmmm... I think this is a common fallacy. It's like arguing that if windoze crashes and you lose important data then you have some sort of legal recourse against Microsoft. Ever read one of their EULAs? $10 says that Oracle's license grants them absolute immunity to any kind of damages claim. Chris --- Tim Hart Wrote: If a catastrophic software failure results in a high percentage of lost revenue, a corporation might be able to seek monetary compensation from a commercial vendor. They could even be taken to court - depending upon licensing, product descriptions, promises made in product literature, etc. For cases like open source projects, like PostgreSQL, there is no legal recourse available. So - in the extreme case, if commercial Vendor V's database blows chunks, and causes company B to loose a lot of money. If Company B can prove that the fault lies squarely on the shoulders of Vendor V, Company C can sue Vendor V's a** off. Executive management isn't at fault - because they have performed due diligence and have forged a partnership with vendor V who has a legal responsibility for the claims of their product. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] mistake in sql99 compatibility?
The cvs docs say that we support the 'WITH CHECK OPTION' on views, but the TODO says we don't... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] BETWEEN SYMMETRIC
Hi, Based on recent discussion, I went thru and got together the work I'd done on the BETWEEN node. It's not as far along as I thought. I ran into a few hurdles: * ExecEvalBetweenExpr is probably beyond my powers - I've done my best and marked my hopelessness with '@@' symbols. I don't know how to actually evaluate the node properly, I don't know how to check that all the 3 types are coercible to the same type and I don't know how to make it take rowvars (sic?)instead of scalars, as per spec. Copy and Equal are done, I think. Out I've guessed at how to do it based on other examples, but I need feedback. Read I haven't done at all cos I don't quite understand when/why it's used or how to do it. The grammar has been updated to use the new BetweenExpr node, with new syntax options. The new keywords have been added in the relevant places, and they are reserved. nodes.h and parsenodes.h are aware of the new node. I have added a full regression test that I used in my old gram.y only implementation, that didn't use a new node - it will be helpful! Where do we go from here? Chris ? GNUmakefile ? between.diff.txt ? config.log ? config.status ? contrib/spi/.deps ? src/Makefile.global ? src/backend/postgres ? src/backend/access/common/.deps ? src/backend/access/gist/.deps ? src/backend/access/hash/.deps ? src/backend/access/heap/.deps ? src/backend/access/index/.deps ? src/backend/access/nbtree/.deps ? src/backend/access/rtree/.deps ? src/backend/access/transam/.deps ? src/backend/bootstrap/.deps ? src/backend/catalog/.deps ? src/backend/catalog/postgres.bki ? src/backend/catalog/postgres.description ? src/backend/commands/.deps ? src/backend/commands/tablecmds.c.mystuff ? src/backend/executor/.deps ? src/backend/lib/.deps ? src/backend/libpq/.deps ? src/backend/main/.deps ? src/backend/nodes/.deps ? src/backend/optimizer/geqo/.deps ? src/backend/optimizer/path/.deps ? src/backend/optimizer/plan/.deps ? src/backend/optimizer/prep/.deps ? src/backend/optimizer/util/.deps ? src/backend/parser/.deps ? src/backend/port/.deps ? src/backend/postmaster/.deps ? src/backend/regex/.deps ? src/backend/rewrite/.deps ? src/backend/storage/buffer/.deps ? src/backend/storage/file/.deps ? src/backend/storage/freespace/.deps ? src/backend/storage/ipc/.deps ? src/backend/storage/large_object/.deps ? src/backend/storage/lmgr/.deps ? src/backend/storage/page/.deps ? src/backend/storage/smgr/.deps ? src/backend/tcop/.deps ? src/backend/utils/.deps ? src/backend/utils/adt/.deps ? src/backend/utils/cache/.deps ? src/backend/utils/error/.deps ? src/backend/utils/fmgr/.deps ? src/backend/utils/hash/.deps ? src/backend/utils/init/.deps ? src/backend/utils/mb/.deps ? src/backend/utils/misc/.deps ? src/backend/utils/mmgr/.deps ? src/backend/utils/sort/.deps ? src/backend/utils/time/.deps ? src/bin/initdb/initdb ? src/bin/initlocation/initlocation ? src/bin/ipcclean/ipcclean ? src/bin/pg_config/pg_config ? src/bin/pg_ctl/pg_ctl ? src/bin/pg_dump/.deps ? src/bin/pg_dump/pg_dump ? src/bin/pg_dump/pg_dumpall ? src/bin/pg_dump/pg_restore ? src/bin/pg_encoding/.deps ? src/bin/pg_encoding/pg_encoding ? src/bin/pg_id/.deps ? src/bin/pg_id/pg_id ? src/bin/psql/.deps ? src/bin/psql/psql ? src/bin/scripts/createlang ? src/include/pg_config.h ? src/include/stamp-h ? src/interfaces/ecpg/lib/.deps ? src/interfaces/ecpg/lib/libecpg.so.3 ? src/interfaces/ecpg/preproc/.deps ? src/interfaces/ecpg/preproc/ecpg ? src/interfaces/libpgeasy/.deps ? src/interfaces/libpgeasy/libpgeasy.so.2 ? src/interfaces/libpq/.deps ? src/interfaces/libpq/libpq.so.2 ? src/interfaces/libpq++/.deps ? src/interfaces/libpq++/libpq++.so.4 ? src/pl/plpgsql/src/.deps ? src/pl/plpgsql/src/libplpgsql.so.1 ? src/test/regress/.deps ? src/test/regress/log ? src/test/regress/pg_regress ? src/test/regress/regression.diffs ? src/test/regress/regression.out ? src/test/regress/results ? src/test/regress/tmp_check ? src/test/regress/expected/constraints.out ? src/test/regress/expected/copy.out ? src/test/regress/expected/create_function_1.out ? src/test/regress/expected/create_function_2.out ? src/test/regress/expected/misc.out ? src/test/regress/sql/constraints.sql ? src/test/regress/sql/copy.sql ? src/test/regress/sql/create_function_1.sql ? src/test/regress/sql/create_function_2.sql ? src/test/regress/sql/misc.sql Index: src/backend/executor/execQual.c === RCS file: /projects/cvsroot/pgsql/src/backend/executor/execQual.c,v retrieving revision 1.94 diff -c -r1.94 execQual.c *** src/backend/executor/execQual.c 2002/06/20 20:29:27 1.94 --- src/backend/executor/execQual.c 2002/06/27 10:27:30 *** *** 60,65 --- 60,67 static Datum ExecEvalOr(Expr *orExpr, ExprContext *econtext, bool *isNull); static Datum ExecEvalCase(CaseExpr *caseExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); + static Datum ExecEvalBetweenExpr(BetweenExpr *btest, ExprContext
Re: [HACKERS] Non-standard feature request
> > Anyone else keen for this feature? > > Attached is a patch implementing this. The patch is against 7.2.1 > source. The grammar introduced is of the form: > > CREATE TEMP TABLE ... ON COMMIT DROP; > > Is this a desirable feature? Seems pretty useful to me. It's useful, there's a patch - what more do we want!!! Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] (A) native Windows port
> As for project coordination, I am willing to setup and maintain a page > similar to the (horribly outdated) ones that I did for Toast and RI. > Summarizing project status, pointing to resources, instructions, maybe a > roadmap, TODO, you name it. I am willing to supply a complete, friendly, powerful and pretty installer program, based on NSIS. http://www.winamp.com/nsdn/nsis/index.jhtml I suggest that pgAdmin is included in the install process. Imagine it - a win32 person downloads a single .exe, with contents bzip2'd. They run the installer, it asks them to agree to license, shows splash screen, asks them where to install it, gets them to supply an installation password and installs pgadmin. It could set up a folder in their start menu with start/stop, edit configs, uninstall and run pgadmin. It would all work out of the box and would do wonderful things for the Postgres community. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] DROP COLUMN Proposal
Hi All, I've been thinking about this DROP COLUMN business (sorry to start another spammy, flamey thread!). I'm taking ideas from lots of sources here. How does this sound for a process? 1. A new column is added to pg_attribute called 'attisdropped'. It, of course, defaults to false. 2. The column expansion (*) code and the code that checks for valid column references everywhere in the codebase is changed to also check the attisdropped field. Does someone have a comprehensive list of places to be changed? 3. The DROP COLUMN command does nothing but set the attisdropped of a column to true, and rename the column to something like DELETED_old_col_name. The column renaming will help people using non-attisdropped aware admin programs see what's what, plus it will allow people to create a new column with the same name as the column just dropped. Now the dropped column will be invisible. As you update rows, etc. the space will be reclaimed in the table as NULLs are put in where the old value used to be. Is this correct? 4. A new command, something like "ALTER TABLE tab RECLAIM;" will be able to be run on tables. It will basically go through the entire table and rewrite every row as is, NULLifying all dropped columns in the table. This gives the DBA the option of recovering his/her space if they want. Notes - a. What happens with TOASTed columns that are dropped? b. Would it be worth implementing an 'UNDROP' command...? c. Do we need an 'attisreclaimed' field in pg_attribute to indicate that a field as been fully reclaimed, or do we just let people run it whenever they want (even if it has no effect other than to waste time)? d. Are there any other comments? Basically, I would like to come up with a 'white paper' implementation that we can all agree on. Then, I will try to code some parts myself, and solicit help from others for other parts. Hopefully, together we can get a DROP COLUMN implementation. The most important step, however, is to agree on an implementation spec. Hopefully I can get the www person to set up a project page (like the proposed win32 project page) to coordinate things. Comments? Regards, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Changes in /contrib/fulltextindex
Hi Florian,
> > The most recent patches were submitted by me, so I guess you
> could call me
> > the defacto "maintainer".
>
> Okay - glad someone answered me :)
Actually, I replied to you 5 minutes after you posted, but I think my emails
were being stalled somewhere...
> I will - please give me a few days for an up to date documentation
> concerning the changed and new features.
>
> And yes - I really appreciate your offer for code review!
To generate the diff, do this:
cd contrib/fulltextindex
cvs diff -c > ftidiff.txt
Then email -hackers the ftidiff.txt.
> > > The changes made include:
> > >
> > > + Changed the split up behaviour from checking via isalpha to
> > > using a list of delimiters as isalpha is a pain used with
> > > data containing german umlauts, etc. ATM this list contains:
> > >
> > > " ,;.:-_#/*+~^°!?\"\\§$%&()[]{}=<>|0123456789\n\r\t@µ"
> >
> > Good idea. Is there a locale-aware version of isalpha anywhere?
>
> If there is - I couldn't find it. I did find a lot of frustated
> posts about
> isalpha and locale-awareness although.
Yeah, I can't find anything in the man pages either. Maybe we can ask the
list. People?
> > List: what should we do about the backward compatibility problem?
>
> I think the only reasonable way for keeping backward compatibiliy might be
> to leave the old fti function alone and introduce a new one with
> the changes
> (e.g. ftia). Even another fti parameter which activates the new features
> breaks the compatibility concerning the call. Activiation via DEFINE is
> another option, but this requires messing around with the source code
> (although very little) on the user side. Maybe a ./configure option is a
> good way (but this is beyond my C and friends skills).
I think that creating a new function, called ftia or ftix or something is
the best solution. I think I can handle doing that...
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DROP COLUMN Proposal
> 2. > The column expansion (*) code and the code that checks for valid column > references everywhere in the codebase is changed to also check the > attisdropped field. Does someone have a comprehensive list of > places to be > changed? Actually - did Hiroshi(?)'s original HACK have this code - we can re-use that. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Changes in /contrib/fulltextindex
> > I am not at all sure that this aspect of Florian's change is a good > > idea, as it appears to eliminate locale-awareness in favor of a hard > > coded delimiter list. > > Just tried your example - you're right of course! I will remove the hard > coded delimited list and replace it with the proper calls as shown in the > code you've sent. OK Florian, submit a diff with your changes and I'll give them a run. I forgot that we could just overload functions with different parameter lists! That sounds like a good idea. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN Proposal
> This part should fall out of Rod Taylor's pg_depend stuff pretty easily. > We still need to debate about the behavior, though. If for example there > is a unique index on column B, do you need "DROP B CASCADE" to get rid > of it, or is "DROP B RESTRICT" good enough? Does your answer change if > the unique index is on two columns (A,B)? I'm not real sure where the > boundary is between attributes of the column (okay to drop as part of > the column) and independent objects that ought to be treated as > requiring CASCADE. >From SQL92: "If RESTRICT is specified, then C shall not be referenced in the of any view descriptor or in the of any constraint descriptor other than a table con- straint descriptor that contains references to no other column and that is included in the table descriptor of T." So I guess that means that if the unique index is only on the dropped column, then restrict mode will still be able to drop it... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] (A) native Windows port
> > It would all work out of the box and would do wonderful things for the > > Postgres community. > > I like this idea, but let me just bring one little issue to note: are you > going to handle upgrades, and if so, how? How are you going to > do a major > version upgrade? Well, the easiest way would be to get them to uninstall the old version first, but I'm sure it can be worked out. Perhaps even we shouldn't overwrite the old version anyway? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Rule Regression Test Failure on FreeBSD/Alpha
OK, On HEAD, I am still seeing the attached failures. They didn't happen before, but appeared in the last couple of months. All other tests pass. It seems to just be a tuple ordering issue - I really don't know what caused it? If necessary, I can just modify the expected result, but I haven't seen anyone else with this issue - maybe there's more to it. For the impatient: *** ./expected/rules.outFri May 3 08:32:19 2002 --- ./results/rules.out Tue Jul 2 12:25:57 2002 *** *** 1005,1012 SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail +--++--+- - sh1|2 | sl1|5 | 2 sh3|4 | sl7|7 | 4 (2 rows) CREATE TABLE shoelace_log ( --- 1005,1012 SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail +--++--+- sh3|4 | sl7|7 | 4 + sh1|2 | sl1|5 | 2 (2 rows) CREATE TABLE shoelace_log ( == Chris regression.diffs Description: Binary data regression.out Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN Proposal
> The nice thing about this implementation approach is that most of the > backend need not be aware of deleted columns. There are a few places in > the parser (probably few enough to count on one hand) that will have to > explicitly check for and reject references to dropped columns, and > you're done. The rewriter, planner and executor are blissfully ignorant > of the whole deal. If you can enumerate these places without much effort, it'd be appreciated! I found: expandRTE() in parser/parse_relation.c What else? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] listen/notify argument (old topic revisited)
> Of course, a shared memory system probably is going to either do it > sequentailly or have its own index issues, so I don't see a huge > advantage to going to shared memory, and I do see extra code and a queue > limit. Is a shared memory implementation going to play silly buggers with the Win32 port? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Integrating libpqxx
Is it included now in the main build process? If so, I'll test it on FreeBSD/Alpha. > Libpqxx still needs to be integrated: > > The 'configure' tests need to be merged into our main configure > The documentation needs to be merged into our SGML docs. > The makefile structure needs to be merged into /interfaces. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] libpq++ build problems
OK, this is what I'm seeing on FreeBSD/Alpha for libpq++. I haven't figured out how to build libpqxx yet.: gmake[3]: Entering directory `/home/chriskl/pgsql-head/src/interfaces/libpq++' g++ -O2 -g -Wall -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/ include -c -o pgconnection.o pgconnection.cc -MMD cc1plus: warning: *** *** The -O2 flag TRIGGERS KNOWN OPTIMIZER BUGS ON THIS PLATFORM *** g++ -O2 -g -Wall -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/ include -c -o pgdatabase.o pgdatabase.cc -MMD cc1plus: warning: *** *** The -O2 flag TRIGGERS KNOWN OPTIMIZER BUGS ON THIS PLATFORM *** g++ -O2 -g -Wall -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/ include -c -o pgtransdb.o pgtransdb.cc -MMD cc1plus: warning: *** *** The -O2 flag TRIGGERS KNOWN OPTIMIZER BUGS ON THIS PLATFORM *** g++ -O2 -g -Wall -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/ include -c -o pgcursordb.o pgcursordb.cc -MMD cc1plus: warning: *** *** The -O2 flag TRIGGERS KNOWN OPTIMIZER BUGS ON THIS PLATFORM *** g++ -O2 -g -Wall -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/ include -c -o pglobject.o pglobject.cc -MMD cc1plus: warning: *** *** The -O2 flag TRIGGERS KNOWN OPTIMIZER BUGS ON THIS PLATFORM *** ar cr libpq++.a `lorder pgconnection.o pgdatabase.o pgtransdb.o pgcursordb.o pglobject.o | tsort` ranlib libpq++.a g++ -O2 -g -Wall -fpic -DPIC -shared -Wl,-x,-soname,libpq++.so.4 pgconnection.o pgdatabase.o pgtransdb.o pgcursordb.o lobject.o -L../../../src/interfaces/libpq -lpq -R/home/chriskl/local/lib - o libpq++.so.4 rm -f libpq++.so ln -s libpq++.so.4 libpq++.so gmake[3]: Leaving directory `/home/chriskl/pgsql-head/src/interfaces/libpq++' ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scope of constraint names
> One problem I see is that pg_constraint entries can *only* be associated > with relations; so the table has no way to represent constraints > associated with domains --- not to mention assertions, which aren't > associated with any table at all. I'm in no hurry to try to implement > assertions, but domain constraints are definitely interesting. We'd > probably have to put domain constraints into a separate table, which > is possible but not very attractive. Hmmm...there must be some sort of schema that can do both in one table? Even something nastly like: refid Oid of relation or domain type 'r' for relation and 'd' for domain ... > Our current implementation of SET CONSTRAINTS changes the behavior of > all constraints matching the specified name, which is pretty bogus > given the lack of uniqueness. If we don't go over to the SQL92 approach > then I think we need some other way of handling SET CONSTRAINTS that > allows a more exact specification of the target constraint. If we do go over to SQL92, what kind of problems will people have reloading their old schema? Should be excluded from the uniqueness check...? > A considerable advantage of per-relation constraint names is that a new > unique name can be assigned for a nameless constraint while holding only > a lock on the target relation. We'd need a global lock to create unique > constraint names in the SQL92 semantics. Surely adding a foreign key is what you'd call a 'rare' event in a database, occurring once once for millions or queries? Hence, we shouldn't worry about it too much? > The only way I can see around > that would be to use newoid(), or perhaps a dedicated sequence > generator, to construct constraint names. The resulting unpredictable > constraint names would be horribly messy to deal with in the regression > tests, so I'm not eager to do this. Surely you do the ol' loop and test sort of thing...? > Even per-relation uniqueness has some unhappiness: if you have a domain > with a named constraint, and you try to use this domain for two columns > of a relation, you'll get a constraint name conflict. Inheriting > similar constraint names from two different parent relations is also > troublesome. We could get around these either by going back to the > old no-uniqueness approach, or by being willing to alter constraint > names to make them unique (eg, by tacking on "_nnn" when needed). > But this doesn't help SET CONSTRAINTS. > > At the moment I don't much like any of the alternatives. Ideas anyone? If they're both equally evil, then maybe we should consider going the SQL92 way, for compatibilities sake? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] BETWEEN Node & DROP COLUMN
Hi All, I have given up working on the BETWEEN node. It got to the stage where I realised I was really out of my depth! Rod Taylor has indicated an interest in the problem and I have sent him my latest patch, so hopefully he'll be able to crack it. So instead, I've taken up with the DROP COLUMN crusade. It seems that the following are the jobs that need to be done: * Add attisdropped to pg_attribute - Looking for takers for this one, otherwise I'll look into it. * Fill out AlterTableDropColumn - I've done this, with the assumption that attisdropped exists. It sets attisdropped to true, drops the column default and renames the column. (Plus does all other normal ALTER TABLE checks) * Modify parser and other places to ignore dropped columns - This is also up for grabs. * Modify psql and pg_dump to handle dropped columns - I've done this. Once the above is done, we have a working drop column implementation. * Modify all other interfaces, JDBC, etc. to handle dropped cols. - I think this can be suggested to the relevant developers once the above is committed! * Modify VACUUM to add a RECLAIM option to reduce on disk table size. - This is out of my league, so it's up for grabs I have approached a couple of people off-list to see if they're interested in helping, so please post to the list if you intend to work on something. It has also occurred to me that once drop column exists, users will be able to change the type of their columns manually (ie. create a new col, update all values, drop the old col). So, there is no reason why this new attisdropped field shouldn't allow us to implement a full ALTER TABLE/SET TYPE sort of feature - cool huh? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> I've not looked in a while, but the column rename code did not account > for issues in foreign keys, etc. Those should be easier to ferret out > soon, but may not be so nice to change yet. Which is probably a good reason for us to offer it as an all-in-one command, rather than expecting them to do it manually... > It should also be noted that an ALTER TABLE / SET TYPE implemented with > the above idea with run into the 2x diskspace issue as well as take > quite a while to process. I think that if the 'SET TYPE' operation is ever to be rollback-able, it will need to use 2x diskspace. If it's overwritten in place, there's no chance of fallback... I think that a DBA would choose to use the command knowing full well what it requires? Better than not offering them the choice at all! Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > > > Yes, please remind me. Was your solution renumbering the > attno values? > > > > > > Yes though I don't intend to object to Christopher's proposal. Hiroshi, I am thinking of rolling back my CVS to see if there's code from your previous test implementation that we can use. Apart from the DropColumn function itself, what other changes did you make? Did you have modifications for '*' expansion in the parser, etc.? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Adding attisdropped
Hi,
I've attached the changes I've made to pg_attribute.h - I can't see what's
wrong but whenever I do an initdb it fails:
initdb -D /home/chriskl/local/data
The files belonging to this database system will be owned by user "chriskl".
This user must also own the server process.
The database cluster will be initialized with locale C.
creating directory /home/chriskl/local/data... ok
creating directory /home/chriskl/local/data/base... ok
creating directory /home/chriskl/local/data/global... ok
creating directory /home/chriskl/local/data/pg_xlog... ok
creating directory /home/chriskl/local/data/pg_clog... ok
creating template1 database in /home/chriskl/local/data/base/1...
initdb failed.
Removing /home/chriskl/local/data.
Chris
Index: pg_attribute.h
===
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.93
diff -c -r1.93 pg_attribute.h
*** pg_attribute.h 2002/06/20 20:29:44 1.93
--- pg_attribute.h 2002/07/04 02:08:29
***
*** 142,147
--- 142,150
/* Has DEFAULT value or not */
boolatthasdef;
+
+ /* Is dropped or not */
+ boolattisdropped;
} FormData_pg_attribute;
/*
***
*** 150,156
* because of alignment padding at the end of the struct.)
*/
#define ATTRIBUTE_TUPLE_SIZE \
! (offsetof(FormData_pg_attribute,atthasdef) + sizeof(bool))
/*
*Form_pg_attribute corresponds to a pointer to a tuple with
--- 153,159
* because of alignment padding at the end of the struct.)
*/
#define ATTRIBUTE_TUPLE_SIZE \
! (offsetof(FormData_pg_attribute,attisdropped) + sizeof(bool))
/*
*Form_pg_attribute corresponds to a pointer to a tuple with
***
*** 164,170
*
*/
! #define Natts_pg_attribute15
#define Anum_pg_attribute_attrelid1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid3
--- 167,173
*
*/
! #define Natts_pg_attribute16
#define Anum_pg_attribute_attrelid1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid3
***
*** 180,185
--- 183,189
#define Anum_pg_attribute_attalign13
#define Anum_pg_attribute_attnotnull 14
#define Anum_pg_attribute_atthasdef 15
+ #define Anum_pg_attribute_attisdropped16
***
*** 398,405
{ 1249, {"attstorage"}, 18, 0, 1, 11, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
{ 1249, {"attisset"}, 16, 0,1, 12, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
{ 1249, {"attalign"}, 18, 0,1, 13, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
! { 1249, {"attnotnull"}, 16, 0, 1, 14, 0, -1, -1, true, 'p', false, 'c', false,
false }, \
! { 1249, {"atthasdef"}, 16, 0, 1, 15, 0, -1, -1, true, 'p', false, 'c',
false, false }
DATA(insert ( 1249 attrelid 26 DEFAULT_ATTSTATTARGET 4 1 0 -1
-1 t p f i f f));
DATA(insert ( 1249 attname19 DEFAULT_ATTSTATTARGET NAMEDATALEN
2 0 -1 -1 f p f i f f));
--- 402,410
{ 1249, {"attstorage"}, 18, 0, 1, 11, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
{ 1249, {"attisset"}, 16, 0,1, 12, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
{ 1249, {"attalign"}, 18, 0,1, 13, 0, -1, -1, true, 'p', false, 'c',
false, false }, \
! { 1249, {"attnotnull"}, 16, 0, 1, 14, 0, -1, -1, true, 'p', false, 'c', false,
false }, \
! { 1249, {"atthasdef"},16, 0, 1, 15, 0, -1, -1, true, 'p', false,
'c', false, false }, \
! { 1249, {"attisdropped"}, 16, 0, 1, 16, 0, -1, -1, true, 'p', false, 'c', false,
false }
DATA(insert ( 1249 attrelid 26 DEFAULT_ATTSTATTARGET 4 1 0 -1
-1 t p f i f f));
DATA(insert ( 1249 attname19 DEFAULT_ATTSTATTARGET NAMEDATALEN
2 0 -1 -1 f p f i f f));
***
*** 416,421
--- 421,427
DATA(insert ( 1249 attalign 18 0 1 13 0 -1 -1 t p f c f f));
DATA(insert ( 1249 attnotnull 16 0 1 14 0 -1 -1 t p f c f f));
DATA(insert ( 1249 atthasdef 16 0 1 15 0 -1 -1 t p f c f f));
+ DATA(insert ( 1249 attisdropped 16 0 1 16 0 -1 -1 t p f c f f));
DATA(insert ( 1249 ctid 27 0 6 -1 0 -1 -1 f p f i f
f));
/* no OIDs in pg_attribute */
DATA(insert ( 1249 xmin 28 0 4 -3 0 -1 -1 t p f i f
f));
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > I am thinking of rolling back my CVS to see if there's code from your > > previous test implementation that we can use. Apart from the DropColumn > > function itself, what other changes did you make? Did you have > > modifications for '*' expansion in the parser, etc.? > > Yes, please review Hiroshi's work. It is good work. Can we have an > analysis of Hiroshi's approach vs the isdropped case. Yes, it is. I've rolled it back and I'm already incorporating his changes to the parser into my patch. I just have to grep all the source code for 'HACK' to find all the changes. It's all very handy. > Is it better to renumber the attno or set a column to isdropped. The > former may be easier on the clients. Well, obviously I prefer the attisdropped approach. I think it's clearer and there's less confusion. As a head developer for phpPgAdmin that's what I'd prefer... Hiroshi obviously prefers his solution, but doesn't object to mine/Tom's. I think that with all the schema-related changes that clients will have to handle in 7.3, we may as well hit them with the dropped column stuff in the same go, that way there's fewer rounds of clients scrambling to keep up with the server. I intend to email every single postgres client I can find and tell them about the new changes, well before we release 7.3... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > Well, obviously I prefer the attisdropped approach. I think > it's clearer > > and there's less confusion. As a head developer for phpPgAdmin > that's what > > I'd prefer... Hiroshi obviously prefers his solution, but > doesn't object to > > OK, can you explain the issues from a server and client perspective, > i.e. renumbering vs isdropped? Well in the renumbering case, the client needs to know about missing attnos and it has to know to ignore negative attnos (which it probably does already). ie. psql and pg_dump wouldn't have to be modified in that case. In the isdropped case, the client needs to know to exclude any column with 'attisdropped' set to true. So in both cases, the client needs to be updated. I personally prefer the explicit 'is dropped' as opposed to the implicit 'negative number', but hey. *sigh* Now I've gone and made an argument for the renumbering case. I'm going to have a good look at Hiroshi's old code and see which one is less complicated, etc. So far all I've really need to do is redefine Hiroshi's COLUMN_DROPPED macro. I'm sure that both methods could be made to handle a 'ALTER TABLE/SET TYPE' syntax. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> Unfortunately many apps rely on the fact that the attnos are > consecutive starting from 1. It was the main reason why Tom > rejected my trial. Nothing has changed about it. OK, I've been looking at Hiroshi's implementation. It's basically semantically equivalent to mine from what I can see so far. The only difference really is in how the dropped columns are marked. I've been ruminating on Hiroshi's statement at the top there. What was the reasoning for assuming that 'many apps rely on the fact that the attnos are consecutive'? Is that true? phpPgAdmin doesn't. In fact, phpPgAdmin won't require any changes with Hiroshi's implementaiton and will require changes with mine. Anyway, an app that relies on consecutive attnos is going to have pain skipping over attisdropped columns anyway??? In fact, I'm now beginning to think that I should just resurrect Hiroshi's implementation. I'm prepared to do that if people like... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > What's your point? No client that examines pg_attribute can be trusted > > until it's been examined pretty closely (as in, more closely than > > Christopher looked at pg_dump). I'd prefer to see us keep the backend > > simple and trustworthy, rather than pursue a largely-illusory idea that > > we might be saving some trouble on the client side. The clients are > > less likely to cause unrecoverable data corruption if something is > > missed. I'm prepared to admit I didn't look at pg_dump too hard. I have to say that I agree with Tom here, but that's personal opinion. If Tom reckons that there's places where Hiroshi's implementation needed work and that there would be messiness, then I'm inclined to believe him. In all honesty, the amount of changes clients have to make to support schemas makes checking dropped columns pale in significance. > > If we were willing to remap attnums so that clients would require *no* > > changes, it would be worth doing --- but I believe we've already > > rejected that approach as unworkable. I don't think "maybe you don't > > need to change, but you'd better study your code very carefully anyway" > > is a big selling point. Exactly. I like the whole 'explicit' idea of having attisdropped. There's no ifs and buts. It's not a case of, "oh, the attnum is negative, but it's not an arbitratily negative system column" sort of thing. > I will vote for the option that has the less pain for our users _and_ in > the backend, but if it is close, I will prefer to make things easier on > clients/users. I will vote for attisdropped. However, I'm not a main developer and I will go with the flow. In the meantime, I'm developing attisdropped but using some of Hiroshi's implementation... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BETWEEN Node & DROP COLUMN
By the way, What happens if someone drops ALL the columns in a table? Do you just leave it there as-is without any columns or should it be forbidden or should it be interpreted as a drop table? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> True enough, but that's not a trivial difference. The problem with > Hiroshi's implementation is that there's no longer a close tie between > pg_attribute.attnum and physical positions of datums in tuples. I think > that that's going to affect a lot of low-level code, and that he hasn't > found all of it. OK, I can see how that would be a problem actually. You'd have to regard attnum as a 'virtual attnum' and keep having to reverse the computation to figure out what its original attnum was... > Keeping the attisdropped marker separate from attnum is logically > cleaner, and IMHO much less likely to lead to trouble down the road. I'm a purist and I like to think that good, clean, well thought out code always results in more stable, bug free software. > We should not allow ourselves to put too much weight on the fact that > some clients use "attnum > 0" as a filter for attributes that they > (think they) need not pay attention to. That's only a historical > artifact, and it's far from clear that it will keep those clients > out of trouble anyway. It's also not 'every client app' that will need to be altered. Just DB admin apps, of which there aren't really that many. And remember, anyone who uses the catalogs directly always does so at their own risk. I think that once we have a proper INFORMATION_SCHEMA anyway, all clients should use that. Heck, if INFORMATION_SCHEMA gets in in 7.3, then clients might have a _heap_ of work to do... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > No, he only breaks even for client cleanliness --- either approach > > *will* require changes in clients that look at pg_attribute. > > Uh, Christopher already indicated three clients, psql, pg_dump, and > another that will not require changes for Hiroshi's approach, but will > require changes for isdropped. That doesn't seem "break even" to me. And Tom pointed out that I was wrong... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> My problem is that you are pushing the DROP COLUMN check out into almost > every client that uses pg_attribute. And we are doing this to keep our > backend cleaner. Seems we should do the work once, in the backend, and > not burden clients will all of this. As a user of Postgres, I found the following more painful: * Anti-varchar truncation in 7.2 * Making you have to quote "timestamp"(), etc. People mail the list every day with backwards compatibility problems. We've done it before, why not do it again? In fact, I'm sure there are already backwards compatibility problems in 7.3. > > We should not allow ourselves to put too much weight on the fact that > > some clients use "attnum > 0" as a filter for attributes that they > > (think they) need not pay attention to. That's only a historical > > artifact, and it's far from clear that it will keep those clients > > out of trouble anyway. > > Well, why shouldn't we use the fact that most/all clients don't look at > attno < 0, and that we have no intention of changing that requirement. > We aren't coding in a vacuum. We have clients, they do that already, > let's use it. > > Attno < 0 is not historical. It is in the current code, and will remain > so for the forseeable future, I think. Problem is, the current code actually assumes that attno < 0 means that the attribute is a system column, NOT a dropped user column. As an example, I'd have to change all of these in the Postgres source code: /* Prevent them from altering a system attribute */ if (attnum < 0) elog(ERROR, "ALTER TABLE: Cannot alter system attribute \"%s\"", colName); Who knows how many other things like this are littered through the source? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> BTW there seems a misunderstanding about my posting. > I'm not objecting to add attisdropped pg_attribute column. > They are essentially the same and so I used macros > like COLUMN_IS_DROPPED in my implementation so that > I can easily change the implementation to use isdropped > pg_attribute column. > I'm only correcting the unfair valuation for my > trial work. Hiroshi, I totally respect your trial work. In fact, I'm relying on it to do the attisdropped implementation. I think everyone's beginning to get a bit cranky here - I think we should all just calm down. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BETWEEN Node & DROP COLUMN
> > Well, what happens if you rename a column in a child table? > Same problem? > > It merrily renames the column in the child table (I tried it). When > SELECTing the parent, bogus data appears. Looks like a bug to me. > Maybe the ALTER TABLE ... RENAME COLUMN code should check for inherited > columns before renaming them. Hmmm...so how does one check if one is a child in an inheritance hierarchy? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP COLUMN Progress
> OK, my guess is that it is checks in parser/. I would issue each of
> these queries with a non-existant column name, find the error message in
> the code, and add an isdropped check in those places.
OK, I think I've narrowed it down to this block of code in scanRTEForColumn
in parse_relation.c:
/*
* Scan the user column names (or aliases) for a match. Complain if
* multiple matches.
*/
foreach(c, rte->eref->colnames)
{
/* @@ SKIP DROPPED HERE? @@ */
attnum++;
if (strcmp(strVal(lfirst(c)), colname) == 0)
{
if (result)
elog(ERROR, "Column reference \"%s\" is
ambiguous", colname);
result = (Node *) make_var(pstate, rte, attnum);
rte->checkForRead = true;
}
}
I'm thinking that I should put a 'SearchSysCacheCopy' where my @@ comment is
to retrieve the attribute by name, and then do a check to see if it's
dropped. Is that the best/fastest way of doing things? Seems unfortunate
to add a another cache lookup in every parsed query...
Comments?
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] DROP COLUMN Progress
> > I'm thinking that I should put a 'SearchSysCacheCopy' where my > @@ comment is > > to retrieve the attribute by name, and then do a check to see if it's > > dropped. Is that the best/fastest way of doing things? Seems > unfortunate > > to add a another cache lookup in every parsed query... > > I am still looking but perhaps you could supress dropped columns from > getting into eref in the first place. OK, that's done. I'm working on not allowing dropped columns in UPDATE targets now. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] DROP COLUMN Progress
> > I am still looking but perhaps you could supress dropped columns from > > getting into eref in the first place. > > OK, that's done. I'm working on not allowing dropped columns in UPDATE > targets now. OK, I've fixed it so that dropped columns cannot be targetted in an update statement, however now I'm running into this problem: If you issue an INSERT statement without qualifying any field names, ie: INSERT INTO tab VALUES (3); Although it will automatically insert NULL for the dropped columns, it still does cache lookups for the type of the dropped columns, etc. I noticed that when I tried setting the atttypid of the dropped column to (Oid)-1. Where is the bit of code that figures out the list of columns to insert into in an unqualified INSERT statement? I'm thinking that it would be nice if the dropped columns never even make it into the list of target attributes, for performance reasons... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP COLUMN Progress
> > test=# create table test (a int4, b int4, c int4, d int4); > > CREATE TABLE > > test=# insert into test values (1,2,3,4); > > INSERT 16588 1 > > test=# alter table test drop b; > > ALTER TABLE > > test=# select * from test; > > a | d | d > > ---+---+--- > > 1 | 3 | 4 > > (1 row) > > What of > > SELECT a,c,d FROM test > > I'll bet that doesn't work at all... Yeah, broken. Damn. test=# SELECT a,c,d FROM test; a | c | d ---+---+--- 1 | 2 | 3 (1 row) test=# SELECT a,d FROM test; a | d ---+--- 1 | 3 (1 row) test=# SELECT d,c,a FROM test; d | c | a ---+---+--- 3 | 2 | 1 (1 row) Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
