Re: [HACKERS] RFC: Restructuring pg_aggregate
> Actually, what we need to do to reclaim space is to enable table > recreation without the column, now that we have relfilenode for file > renaming. It isn't hard to do, but no one has focused on it. I want to > focus on it, but have not had the time, obviously, and would be very > excited to assist someone else. > > Hiroshi's fine idea of marking certain columns as unused would not have > reclaimed the missing space, just as my idea of physical/logical column > distinction would not reclaim the space either. Again, my > physical/logical idea is more for fixing other problems and > optimization, not DROP COLUMN. Hmmm. Personally, I think that a DROP COLUMN that cannot reclaim space is kinda useless - you may as well just use a view!!! So how would this occur?: 1. Lock target table for writing (allow reads) 2. Begin a table scan on target table, writing a new file with a particular filenode 3. Delete the attribute row from pg_attribute 4. Point the table in the catalog to the new filenode 5. Release locks 6. Commit transaction 7. Delete orhpan filenode i. Upon postmaster startup, remove any orphaned filenodes The real problem here is the fact that there are now missing attnos in pg_attribute. Either that's handled or we renumber the attnos - which is also quite hard? This, of course, suffers from the double size data problem - but I believe that it does not matter - we just need to document it. Interestingly enough, Oracle support ALTER TABLE foo SET UNUSED col; Which invalidates the attribute entry, and: ALTER TABLE foo DROP col CHECKPOINT 1000; Which actually reclaims the space. The optional CHECKPOINT [n] clause tells Oracle to do a checkpoint every [n] rows. "Checkpointing cuts down the amount of undo logs accumulated during the drop column operation to avoid running out of rollback segment space. However, if this statement is interrupted after a checkpoint has been applied, the table remains in an unusable state. While the table is unusable, the only operations allowed on it are DROP TABLE, TRUNCATE TABLE, and ALTER TABLE DROP COLUMNS CONTINUE (described below). " 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] Make text output more generic
Hi,
I'm working on making the SHOW command dump its output as if it were a
select result.
Tom's declared the following as static ("private") methods?
static TextOutputState *begin_text_output(CommandDest dest, char *title);
static void do_text_output(TextOutputState *tstate, char *aline);
static void do_text_output_multiline(TextOutputState *tstate, char *text);
static void end_text_output(TextOutputState *tstate);
I should really move these off somewhere else and make them a bit more
global and generic. I will also use Joe's version (private email) that
should allow more columns in the output. What should I name these
functions? I notice a tendency towards TextDoOutput, TextDoOuputMultiline,
TextEndOutput sort of naming conventions for "global" functions.
Is this a good idea? Where I should put them?
Regards,
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Make text output more generic
> > I should really move these off somewhere else and make them a bit more > > global and generic. > > What's insufficiently generic about them for you? Well, at a _quick_ glance they're designed only for one column output... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] command.c breakup
> Fine. I'll work on that basis. I'll prepare a full-blown patch which can > be applied Monday -unless anyone else is sitting on uncommitted changes > to the directory that they want me to wait for? Nothing important. Shall I suggest that you do the rearrangement first, and then once everything's happy, we can work on removing redundant code? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Various issues
> To my humble opinion, KDE needs a real database abstraction layer (like > pgSchema) with a multi-vendor interface. The only solution today is Gnome > libgda. Unfortunately, libgda is not well-written. A good > abstraction layer > needs inheritence (C++, not C) and XML to handle specfic features of each > database provider. Not quite true. QT3 that KDE3 is based on has full database support. It supports MySQL, Postgres and ODBC I think. As for KDE DB admin software: apps.kde.com , search for 'postgres'. There's at least 4 frontends already in progress... 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] RFC: Restructuring pg_aggregate
> Updating pg_attribute per se is not so hard --- just store new copies of > all the rows for the table. However, propagating the changes into other > places could be quite painful (I'm thinking of column numbers in stored > constraints, rules, etc). > > It seems to me that reducing the column to NULLs already gets you the > majority of the space savings. I don't think there is a case to be made > that getting back that last bit is worth the pain involved, either in > implementation effort or direct runtime costs (do you really want a DROP > COLUMN to force an immediate rewrite of the whole table?) OK, sounds fair. However, is there a more aggressive way of reclaiming the space? The problem with updating all the rows to null for that column is that the on-disk size is doubled anyway, right? So, could a VACUUM FULL process do the nulling for us? Vacuum works outside of normal transaction constraints anyway...? Also, it seems to me that at some point we are forced to break client compatibility. Either we add attisdropped field to pg_attribute, or we use Hiroshi's (-1 * attnum - offset) idea. Both Tom and Hiroshi have good reasons for each of these - would it be possible for you guys to post with your reasons for and against both the techniques. I just want to get to an implementation specification we all agree on that can be written up and then the coding can proceed. Maybe we should add it to the 'Postgres Major Projects' page - and remove those old ones that have already been implemented. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3 schedule
> > thought out way of predicting/limiting their size. (2) How the heck do > > you get rid of obsoleted cached plans, if the things stick around in > > shared memory even after you start a new backend? (3) A shared cache > > requires locking; contention among multiple backends to access that > > shared resource could negate whatever performance benefit you might hope > > to realize from it. I don't understand all these locking problems? Surely the only lock a transaction would need on a stored query is one that prevents the cache invalidation mechanism from deleting it out from under it? Surely this means that there would be tonnes of readers on the cache - none of them blocking each other, and the odd invalidation event that needs a complete lock? Also, as for invalidation, there probably could be just two reasons to invalidate a query in the cache. (1) The cache is running out of space and you use LRU or something to remove old queries, or (2) someone runs ANALYZE, in which case all cached queries should just be flushed? If they specify an actual table to analyze, then just drop all queries on the table. Could this cache mechanism be used to make views fast as well? You could cache the queries that back views on first use, and then they can follow the above rules for flushing... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] numeric/decimal docs bug?
> Jan, regression is not a test of the level a developer would use to make > sure his code works. It is merely to make sure the install works on a > limited number of cases. News to me! If anything, I don't think a lot of the current regression tests are comprehensive enough! For the SET/DROP NOT NULL patch I submitted, I included a regression test that tests every one of the preconditions in my code - that way if anything gets changed or broken, we'll find out very quickly. I personally don't have a problem with the time taken to regression test - and I think that trimming the numeric test _might_ be a false economy. Who knows what's going to turn around and bite us oneday? > Having seen zero reports of any numeric > failures since we installed it, and seeing it takes >10x times longer > than the other tests, I think it should be paired back. Do we really > need 10 tests of each complex function? I think one would do the trick. A good point tho, I didn't submit a regression test that tries to ALTER 3 different non-existent tables to check for failures - one test was enough... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] experimental pg_qcache patch
Does it cache all queries or just explicitly prepared ones? Does is check for cached queries all the time or just explicitly EXECUTED ones? Chris - Original Message - From: "Neil Conway" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 6:47 AM Subject: [HACKERS] experimental pg_qcache patch > Hi all, > > I've attached an updated version of Karel Zak's pg_qcache patch, which > adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL > statements). It should apply cleanly against CVS HEAD, and compile > properly -- beyond that, cross your fingers :-) > > Please take a look at the code, play around with using PREPARE and > EXECUTE, etc. Let me know if you have any suggestions for improvement > or if you run into any problems -- I've probably introduced some > regressions when I ported the code from 7.0 to current sources. > > BTW, if you run the regression tests, I'd expect (only) the "prepare" > test to fail: I've only written partial regression tests so far. If > any other tests fail, please let me know. > > The basic syntax looks like: > > PREPARE AS ; > EXECUTE USING ; > DEALLOCATE PREPARE ; > > To get a look at what's being stored in the cache, try: > > SELECT qcache_state(); > > For more information on the qCache code, see the README that > Karel posted to the list a few days ago. > > There are still lots of things that need to be improved. Here's > a short list: (the first 3 items are the most important, any help > on those would be much appreciated) > > (1) It has a tendancy to core-dump when executing stored queries, > particularly if the EXECUTE has an INTO clause -- it will work > the first time, but subsequent attempts will either dump core or > claim that they can't find the plan in the cache. > > (2) Sometimes executing a PREPARE gives this warning: > > nconway=> prepare q1 as select * from pg_class; > WARNING: AllocSetFree: detected write past chunk end in TransactionCommandContext 0x83087ac > PREPARE > > Does anyone know what problem this indicates? > > (3) Preparing queries with parameters doesn't work: > > nconway=> PREPARE sel USING text AS SELECT * FROM pg_class WHERE relname ~~ $1; > ERROR: Parameter '$1' is out of range > > (4) Add a mechanism for determining if there is already a > cached plan with a given name. > > (5) Finish regression tests > > (6) Clean up some debugging messages, correct Karel's English, > code cleanup, etc. > > (7) IMHO, the number of qcache buffers should be configurable > in postgresql.conf, not as a command-line switch. > > (8) See if the syntax can be adjusted to be more compatible > with the SQL92 syntax. Also, some of the current syntax is > ugly, in order to make parsing easier. > > Cheers, > > Neil > > -- > Neil Conway <[EMAIL PROTECTED]> > PGP Key ID: DB3C29FC > > > ---(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] experimental pg_qcache patch
Does it cache all queries or just explicitly prepared ones? Does is check for cached queries all the time or just explicitly EXECUTED ones? Chris - Original Message - From: "Neil Conway" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Sunday, April 14, 2002 6:47 AM Subject: [HACKERS] experimental pg_qcache patch > Hi all, > > I've attached an updated version of Karel Zak's pg_qcache patch, which > adds PREPARE/EXECUTE support to PostgreSQL (allowing prepared SQL > statements). It should apply cleanly against CVS HEAD, and compile > properly -- beyond that, cross your fingers :-) > > Please take a look at the code, play around with using PREPARE and > EXECUTE, etc. Let me know if you have any suggestions for improvement > or if you run into any problems -- I've probably introduced some > regressions when I ported the code from 7.0 to current sources. > > BTW, if you run the regression tests, I'd expect (only) the "prepare" > test to fail: I've only written partial regression tests so far. If > any other tests fail, please let me know. > > The basic syntax looks like: > > PREPARE AS ; > EXECUTE USING ; > DEALLOCATE PREPARE ; > > To get a look at what's being stored in the cache, try: > > SELECT qcache_state(); > > For more information on the qCache code, see the README that > Karel posted to the list a few days ago. > > There are still lots of things that need to be improved. Here's > a short list: (the first 3 items are the most important, any help > on those would be much appreciated) > > (1) It has a tendancy to core-dump when executing stored queries, > particularly if the EXECUTE has an INTO clause -- it will work > the first time, but subsequent attempts will either dump core or > claim that they can't find the plan in the cache. > > (2) Sometimes executing a PREPARE gives this warning: > > nconway=> prepare q1 as select * from pg_class; > WARNING: AllocSetFree: detected write past chunk end in TransactionCommandContext 0x83087ac > PREPARE > > Does anyone know what problem this indicates? > > (3) Preparing queries with parameters doesn't work: > > nconway=> PREPARE sel USING text AS SELECT * FROM pg_class WHERE relname ~~ $1; > ERROR: Parameter '$1' is out of range > > (4) Add a mechanism for determining if there is already a > cached plan with a given name. > > (5) Finish regression tests > > (6) Clean up some debugging messages, correct Karel's English, > code cleanup, etc. > > (7) IMHO, the number of qcache buffers should be configurable > in postgresql.conf, not as a command-line switch. > > (8) See if the syntax can be adjusted to be more compatible > with the SQL92 syntax. Also, some of the current syntax is > ugly, in order to make parsing easier. > > Cheers, > > Neil > > -- > Neil Conway <[EMAIL PROTECTED]> > PGP Key ID: DB3C29FC > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DROP COLUMN (was RFC: Restructuring pg_aggregate)
> No, VACUUM has the same transactional constraints as everyone else > (unless you'd like a crash during VACUUM to trash your table...) Seriously, you can run VACUUM in a transaction and rollback the movement of a tuple on disk? What do you mean by same transactional constraints? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] experimental pg_qcache patch
> Just explicitly prepared ones. Caching all queries opens a can of > worms that I'd rather not deal with at the moment (volunteers to > tackle this problem are welcome). I definitely agree. I think that the optimisation possiblities offered to the DBA for shared prepared statements are quite large enough to offer exciting possibilities. Also, it will minimise the locking contentions Tom speaks of. > > Does is check for cached queries all the time or just explicitly EXECUTED > > ones? > > A cached query plan is only used for EXECUTE queries -- it is > not used all the time. My gut feeling WRT to caching everything > is similar to my response to your first question. It'll be interesting to have VIEWs automatically prepared and executed from the cache... 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] That CREATE OPERATOR CLASS patch
If Bruce is thinking of applying outstanding patches - whatever happened with Bill Studenmund's CREATE OPERATOR CLASS patch? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Importing Large Amounts of Data
> 1. Postgres appears to have a fairly high row overhead (40 bytes > or so according to the FAQ), which grieves me slightly, as that's > actually larger than the size of the data in my tuples. It would > seem that in my case some of the items in that header (the OID and > the NULL bitfield) are not used; would it be possible to avoid > allocating these in this relations that don't use them? CREATE TABLE WITHOUT OIDS ... > As well, index builds seem to take about 20% longer (using -F), and they > seem to be about 10% larger as well. > > Does anybody have any suggestions as to how I can improve performance > here, and reduce disk space requirements? If not, I'll probably have > to suggest to the client that he move to MySQL for this particular > application, unless he needs any of the features that Postgres provides > and MySQL doesn't. This conclusion seems to me to be remarkably shortsighted. Does the initial data load into the database occur just once or quite often? If just once, then the initial loading time doesn't matter. It's a bit hard to say "just turn off all the things that ensure your data integrity so it runs a bit faster", if you actually need data integrity. Anyway, from what I understand an OLTP application is all about selects and memoising certain aggregate results. Since Postgres has far more advanced indexing and trigger support than MySQL, surely you need to take this kind of difference into account??? The fact that you can load stuff quicker in MySQL and it takes up less disk space seems totally irrelevant. Just wait until your MySQL server crashes and your client finds that half his data is corrupted... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Importing Large Amounts of Data
> As you can see from the schema I gave later in my message, that's > exactly what I did. But does this actually avoid allocating the > space in the on-disk tuples? What part of the code deals with this? > It looks to me like the four bytes for the OID are still allocated > in the tuple, but not used. OK, well I guess in that case they are - I'm no expert on the file format. > But from the looks of it, the production system will be doing daily > imports of fresh data ranging in size from a copule of million rows > to a couple of tens of millions of rows. Well that definitely makes a difference then... > > It's a bit hard to say "just turn off all the things that > ensure your data > > integrity so it runs a bit faster", if you actually need data integrity. > > I'm not looking for "runs a bit faster;" five percent either way > makes little difference to me. I'm looking for a five-fold performance > increase. > Anyway, from the looks of it, this is going to be fairly simple > stuff. (Unfortunately, I don't have details of the real application > the client has in mind, though I sure wish I did.) What I'm trying > to indicate when I say "OLAP" is that it's basically selecting > across broad swaths of a large data set, and doing little or nothing > in the way of updates. (Except for the daily batches of data, of > course.) OK, well now it depends on what kind of selects you're doing. Do you regularly select over a certain subset of the data, in which case using partial indices might give you significant speedup. Do you select functions of columns? If so, then you'll need functional indices. MySQL doesn't have either of these. However, if you're always doing full table scans, then MySQL will probably do these faster. Now, here's another scenario. Suppose you're often querying aggregate data over particular subsets of the data. Now instead of requerying all the time, you can set up triggers to maintain your aggregates for you on the fly. This will give O(1) performance on select compared to O(n). MySQL's new query cache might help you with this, however. > I don't want to start a flamewar here, because personally I don't > even like MySQL and would prefer always to use PostgreSQL. But it > makes it a lot harder to do so when people keep insisting that > import speed is not important. Rather than say that, why don't we > just admit that PosgreSQL is a fairly crap performer in this regard > at the moment (at least the way I'm doing it), and work out ways > to fix this? It depends on your definition. You have to accept a certain overhead if you're to have data integrity and MVCC. If you can't handle that overhead, then you can't have data integrity and vice versa. BTW, instead of: CREATE UNIQUE INDEX bigone_pkey ON bigone (rec_no); do: ALTER TABLE bigone ADD PRIMARY KEY(rec_no); And remember to run "VACUUM ANALYZE bigone;" or just "ANALYZE bigone;" after the COPY and before trying to use the table. I'm not sure if it's better to analyze before or after the indexes are added, but it's definitely better to vaccum before the indexes are added. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Importing Large Amounts of Data
>b) In fact, at times I don't need that data integrity. I'm > prefectly >happy to risk the loss of a table during import, if it > lets me do the >import more quickly, especially if I'm taking the database off line >to do the import anyway. MS SQL server in fact allows me to specify >relaxed integrity (with attendant risks) when doing a BULK > IMPORT; it >would be cool if Postgres allowed that to. Well I guess a TODO item would be to allow COPY to use relaxed constraints. Don't know how this would go over with the core developers tho. > Thanks. This is the kind of useful information I'm looking for. I > was doing a vacuum after, rather than before, generating the indices. That's because the indexes themselves are cleaned out with vacuum, as well as the tables. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Importing Large Amounts of Data
> On Monday 15 April 2002 03:53, Christopher Kings-Lynne wrote: > > BTW, instead of: > > > > CREATE UNIQUE INDEX bigone_pkey ON bigone (rec_no); > > > > do: > > > > ALTER TABLE bigone ADD PRIMARY KEY(rec_no); > > I am sorry, could you please elaborate more on the difference? They have the same _effect_, it's just that the first sytnax does not mark the index as the _primary_ index on the relation. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Importing Large Amounts of Data
> Yes, I know. I mean how does this affect performance? How this can change > planner decision? Does it have any effect except cosmetical one? Only cosmetic. In the example he gave, he wanted a primary key, so I showed him how to make one properly. 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] RFC: Generating useful names for foreign keys and checks
Hi, I'm thinking of doing a patch to generate foo_fkey and foo_chk names for fk's and checks. I know that this will make using DROP CONSTRAINT a whole heck of a lot easier. There have also been a few people who've complained on the list about all the foreign keys, etc. I know Tom had some fears, but I don't know if they still apply, or if they're any worse than the current situation? Can I go ahead? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RFC: Generating useful names for foreign keys and checks
> Actually I'm in favor of it. I have a proposal outstanding to require > constraints to have names that are unique per-table, for consistency > with triggers (already are that way) and rules (will become that way, > rather than having globally unique names as now). AFAIR the only > significant concern was making sure that the system wouldn't generate > duplicate constraint names by default. Yeah, that's what's giving me pain - foreign key names are generated in the rewriter or something somewhere, so I'm not sure exactly what I have access to for checking duplicates... The other interesting issue is the the little suffix we append is just in the name. ie. someone can create an index called '_pkey' and cause confusion. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] [SQL] 16 parameter limit
> > Anyway, how does one measure the perfomance impact of such a change? > > By merely changing the constant definition, or also by actually using > > long identifiers? I can do that if it's of any help, for various values > > perhaps. > > I think I would measure disk size change in a newly created database, > and run regression for various values. That uses a lot of identifier > lookups. With schemas, maybe there'd be less name lookups and comparisons anyway, since there's more reliance on oids instead of names? 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] 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]
