Re: [HACKERS] [PATCHES] ALTER SEQUENCE
Rod Taylor [EMAIL PROTECTED] writes: Might get somewhere by making a special domain thats marked as being serial, and using that in the column. I recall some discussion last year about making serial et al. into domains over int4 and int8, rather than their current utter-hack implementation. Can't recall if we found a problem with the idea, or no one got around to doing it, or it just didn't seem to clean things up enough to be worth the trouble. (AFAICS you'd still need special-case code to set up the appropriate default expression for each column; the domain constraint mechanism wouldn't handle that for you.) Seems worth looking at, though. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] ALTER SEQUENCE
On Tue, 2003-03-04 at 19:14, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Might get somewhere by making a special domain thats marked as being serial, and using that in the column. I recall some discussion last year about making serial et al. into domains over int4 and int8, rather than their current utter-hack implementation. Can't recall if we found a problem with the idea, or no one got around to doing it, or it just didn't seem to clean things up enough to be worth the trouble. (AFAICS you'd still need special-case code to set up the appropriate default expression for each column; the domain constraint mechanism wouldn't handle that for you.) Slightly different thought. I had actually submitted a patch for the above, but would have to dig through the archives to determine what the problem was. CREATE TABLE tab (column SERIAL); Generates: table(column serial_table_column) domain serial_table_column as int4 default nextval(serial_table_column) Sequence serial_table_column. Now, rather than having knowedge of the contents of nextval, we simply trace the dependencies of the column through the domain to the sequence -- since these will be known to exist. Thus the below command could function fairly easily on serials: ALTER SEQUENCE ON table(column) The alternative is to simply implement the proposed 200N sequence generator spec, which includes 'NEXT VALUE FOR sequence'. With that in place, our default would then depend on the sequence, and the ALTER SEQUENCE ON table(column) would function. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Best setup for RAM drive
Why not just run PostgreSQL like everyone else does (from a hard drive) and simply give it heaps of buffers - let PostgreSQL manage its RAM itself? Unless you have your xlog on physical storage, you are asking for trouble. Actually, unless you have all your database in physical storage, you are asking for trouble! (At least until we have point in time recovery, but that still requires you to have your xlog on hard drive) Chris - Original Message - From: Chris Sutton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 11:03 PM Subject: [HACKERS] Best setup for RAM drive Hello, I need some insight on the best way to use a RAM drive in a Postgresql installation. Here is our situation and current setup: Postgresql 7.2.1 Dual PIII 800 RAID 5 SCSI disks Platypus 8GB PCI QikDrive (the RAM drive). http://www.platypus.net The Platypus RAM drive is a PCI card with 8GB of ram onboard with an external power supply so if the main power to the server goes off, the RAM is still powered, so it's persistent between reboots. Currently the disk size of our database is 3.2GB, so we put the whole pgsql directory on the RAM drive. Current preformance is very snappy with the bottleneck being the CPUs. The concern of course is if something happends to the RAM drive we are S.O.L. and have to go to the last backup (pg_dump happens each night). The other concern is if the disk size of the database grows past or near 8gb, we would either have to get a bigger RAM drive or somehow split things betten SCSI and RAM drive. I don't quite grasp the full inner workings of Postgresql, but for those of you who obviously do, is there a better way of setting things up where you could still use the RAM drive for portions of the pgsql directory structure while putting the rest on disk where it's safer? Should we just put pgsql/data/pg_xlog on the RAM drive? Also, in the very near future we will be upgrading to another server, pg7.3.2 with dual P4 2.4 xenon's. The RAM drive will go into this new server. Any suggestions? Thanks Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Win32 Powerfail testing
We are developing a Win32 port of PostgreSQL 7.3(different from Jan's implementaion, in that we are using a thread model. In the future I hope we could contribute the source code). We have done a power failure testing using the test tool made by Dave Page: Subject: [HACKERS] Win32 Powerfail testing - results From: Dave Page [EMAIL PROTECTED] Date: Mon, 3 Feb 2003 16:51:33 - So far we found interesting facts. Our Win32 port passes his test in most cases. However if power of the machine is turned off right after (10 to 20 seconds) the Checkpoint has been made, it does not passes his test. So we are thinking that there is someting wrong with the checkpoint implementaion for Win32 port, which is essentially same as Jan's implementation. i.e. using _flushall() instead of sync(). We were looking for a fix or an alternative implementaion of sync() without success. BTW, we found that Cygwin port of PostgreSQL does not pass his test neither. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. This util has been very helpful to me in developing a document mangement application. Rather than writing insert/update logic every time the db or xml schema changes, I just tweak the config file and it will generated the inserts, updates, and deletes by comparing the XML document with the tables to which the XML elements are mapped. I've been able to handle tree structures tolerably well. I am currently rewriting the code in C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updateable views...
Eric D Nielsen [EMAIL PROTECTED] writes: The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hackers (including one linked from the ToDo list), but they all seem to die out without really reaching any sort of conclusion. That's because we've discussed it about as far as is interesting, until someone actually steps up and does the work ;-). We know how it should be implemented: in Postgres terms, an updateable-view facility would simply mean generating the appropriate ON INSERT/UPDATE/DELETE rules automatically, whenever a view is created that is simple enough that the code can figure out what said rules ought to be. (Hopefully this condition will be pretty nearly equivalent to the rules the SQL spec lays down for whether a view is updatable.) Are there people working on this topic? I'ld be interested in helping out. AFAIR, no one has done anything about it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Updateable views...
On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hackers (including one linked from the ToDo list), but they all seem to die out without really reaching any sort of conclusion. I've also seen that in many cases it appears possible to use triggers/rules to simulate updateable views, but that feels like an inelegant solution to me. How so? A view is defined by ON SELECT rules; it seems natural, then, that an updateable view would be defined ON INSERT / ON UPDATE rules. AFAIK the only deficiency with the status quo is that the system does not automatically define those insertion rules for you (in the subset of cases where rules actually *can* be defined: for example, the view can't include aggregation/grouping, calls to a user-defined function, etc.) If you'd like to work on getting PostgreSQL to make views updateable automatically, that would be cool -- AFAIK no one else is currently working on it. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
XSLT could be used to convert virtually any xml table format directly into an insert statement. For me, this is better than using a programming language plus a parser. XSLT is quite powerful and fast and is build on top of xpath, and is a closer fit to the declarative programming model of sql. Validation could be done at the xslt stage or with schemas, which I prefer. Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. ---(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] Updateable views...
Neil Conway [EMAIL PROTECTED] writes: On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hackers (including one linked from the ToDo list), but they all seem to die out without really reaching any sort of conclusion. I've also seen that in many cases it appears possible to use triggers/rules to simulate updateable views, but that feels like an inelegant solution to me. How so? A view is defined by ON SELECT rules; it seems natural, then, that an updateable view would be defined ON INSERT / ON UPDATE rules. AFAIK the only deficiency with the status quo is that the system does not automatically define those insertion rules for you (in the subset of cases where rules actually *can* be defined: for example, the view can't include aggregation/grouping, calls to a user-defined function, etc.) If you'd like to work on getting PostgreSQL to make views updateable automatically, that would be cool -- AFAIK no one else is currently working on it. Would the rules approach be able to handle inline views? Ie, queries like: UPDATE (select * from a natural join b) set a.foo = b.foo On Oracle this is often the most efficient way to write update queries where the data is coming from other tables. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] XML ouput for psql
I would like to see PostgreSQL eventually support XQuery: http://www.w3.org/TR/xquery/ http://www.w3.org/TR/query-datamodel/ I see potentially an alternative front end called xsql, providing substantially the same functionality as psql, only using XQuery syntax and optionally returning recordsets as XML. Anybody want to put together a team to explore this seriously? There are probably several non-trivial semantic issues on the back end, but I only dimly grasp them at this point. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 8:16 AM %% To: Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% XSLT could be used to convert virtually any xml table format directly %% into an insert statement. For me, this is better than using a %% programming language plus a parser. XSLT is quite powerful and fast and %% is build on top of xpath, and is a closer fit to the declarative %% programming model of sql. Validation could be done at the xslt stage or %% with schemas, which I prefer. %% %% %% Acually, the difficult part has been getting the information back %% into the database. Getting it out is a very simple query. I imagine %% that every language/environment has an SQL-XML library somewhere, %% but I wasn't able to find something that would go from XML to SQL. %% %% %% ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Error codes revisited
[EMAIL PROTECTED] writes: What about a variable that allowed the codes to be switched on so a number is returned instead of a string? This would be off by default so as not to break existing applications. Similarly, we can return other information (FILE, LINE, etc.) with different variables. This should all be doable without a protocol change, as long as everything is returned as a string in a standard format. The *last* thing we need is a half-baked stopgap solution that we'll have to be backwards-compatible with forevermore. Fix it right or don't do it at all, is MHO. There is still barely enough time to do the long-threatened protocol revision for 7.4, if we suck it up and get started on that now. I've been avoiding the issue myself, because it seems generally boring and thankless work, but maybe it's time to face up to it? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updateable views...
On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hac kers (including one linked from the ToDo list), but they all seem to die out wit hout really reaching any sort of conclusion. I've also seen that in many cases it appears possible to use triggers/rules to simulate updateable view s, but that feels like an inelegant solution to me. How so? A view is defined by ON SELECT rules; it seems natural, then, that an updateable view would be defined ON INSERT / ON UPDATE rules. AFAIK the only deficiency with the status quo is that the system does not automatically define those insertion rules for you (in the subset of cases where rules actually *can* be defined: for example, the view can't include aggregation/grouping, calls to a user-defined function, etc.) Using user-written rules seems inelegant to me because they force the user to do something the DBMS should be able to do itself. Should the rules be auto-generated by the DBMS then I wouldn't consider it inelegant. If you'd like to work on getting PostgreSQL to make views updateable automatically, that would be cool -- AFAIK no one else is currently working on it. I'm definately willing to look into it, can anyone offer any advice for getting situated in the code? Are there paticular areas I should focus on understanding/areas I should be able to safely ignore? All my PostGreSQL experiences have been in user-land so far. Is there a good place to view the SQL99 standard without shelling out the $20 to ASNI? I know I'll have more questions later, but until then, happy coding... Eric ---(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] Error codes revisited
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The *last* thing we need is a half-baked stopgap solution that we'll have to be backwards-compatible with forevermore. Fix it right or don't do it at all, is MHO. I agree. There is still barely enough time to do the long-threatened protocol revision for 7.4, if we suck it up and get started on that now. I've been avoiding the issue myself, because it seems generally boring and thankless work, but maybe it's time to face up to it? Definitely. Sure seems to be a lot involved, looking at the TODO page. Which brings up another question - if a protocol change doesn't warrant a bump to 8.0, what does? :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200303040645 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+ZC1LvJuQZxSWSsgRAkJLAKDUE54ZELrPc4ASqEtwUCk7CYJH/ACfZ7nQ bLRqMde1T9MDjzmejF+PBis= =Plww -END PGP SIGNATURE- ---(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] XML ouput for psql
This is just about a total conversion of the backend to an xml document server. The marriage of xml and sql is awkward and not easily retrofitted to existing databases. Its pretty much proven that hierarchal storage techniques (xml included) are more difficult to manage and use than traditional sql databases. However, xml does have some very powerful supplemental technologies for document generation on the client end, especially xslt. Unless there is a compelling reason to use those tools, you are 99% likely better off not using xml at all. XML has also found a niche in the edi world, but in this case you can get away with using the blob technique below. Before thinking about xquery you have to think about the role tuples and relations play in storing xml data. The simplest route is to store your xml data in a blob object and use little tricks like xslt to extract elements out of the document into separate fields for index purposes and use vanilla sql queries to get the data. This is a very loose coupling of sql and xml and leads to very brittle designs because at the very least you have to store two separate definitions of your data as well as your marshalling xslt scripts. I wrote a little blob based xml server which you can see here if you are interested: http://www.radiosoft.com/freetools.php?Location=Directional+Patterns The real magic was in the xslt which you can't see, though. A tighter coupling would involve decomposing your xml structure into discrete objects and building xml power into the backend. I think it is yet to be proven if this is even reasonably possible. The big problem with xml is that there is too many ways to do things, for example the choice of dtd or schemas. I think, the problem with this approach is recomposing your document involves complex or inefficient queries. If you think this is good idea, good luck, many companies have tried and I've personally not seen one that seems to work very well. The next major version of MS SQL server is rumored to be something of an xml document server, and that's been several years in development. Merlin -Original Message- From: Bob Calco [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 9:10 AM To: Merlin Moncure; Alan Gutierrez Cc: [EMAIL PROTECTED] Subject: RE: [HACKERS] XML ouput for psql I would like to see PostgreSQL eventually support XQuery: http://www.w3.org/TR/xquery/ http://www.w3.org/TR/query-datamodel/ I see potentially an alternative front end called xsql, providing substantially the same functionality as psql, only using XQuery syntax and optionally returning recordsets as XML. Anybody want to put together a team to explore this seriously? There are probably several non-trivial semantic issues on the back end, but I only dimly grasp them at this point. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 8:16 AM %% To: Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% XSLT could be used to convert virtually any xml table format directly %% into an insert statement. For me, this is better than using a %% programming language plus a parser. XSLT is quite powerful and fast and %% is build on top of xpath, and is a closer fit to the declarative %% programming model of sql. Validation could be done at the xslt stage or %% with schemas, which I prefer. %% %% %% Acually, the difficult part has been getting the information back %% into the database. Getting it out is a very simple query. I imagine %% that every language/environment has an SQL-XML library somewhere, %% but I wasn't able to find something that would go from XML to SQL. %% %% %% ---(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
Re: [HACKERS] Updateable views...
Gavin Sherry [EMAIL PROTECTED] writes: I haven't had time to look into it further, but it occurs to me that handling views which rely on joins would be far from trivial. Views containing joins would not be updatable; problem solved. The set of views the automatic-rule-generation machinery needs to handle are those defined as updatable by the SQL spec. SQL92 says 12)A query specification QS is updatable if and only if the fol- lowing conditions hold: a) QS does not specify DISTINCT. b) Every value expression contained in the select list imme- diately contained in QS consists of a column reference, and no column reference appears more than once. c) The from clause immediately contained in the table ex- pression immediately contained in QS specifies exactly one table reference and that table reference refers either to a base table or to an updatable derived table. Note: updatable derived table is defined in Subclause 6.3, table reference. d) If the table expression immediately contained in QS imme- diately contains a where clause WC, then no leaf generally underlying table of QS shall be a generally underlying table of any query expression contained in WC. e) The table expression immediately contained in QS does not include a group by clause or a having clause. The reference to 6.3 appears to be pointing at this: 8) A derived table is an updatable derived table if and only if the query expression simply contained in the subquery of the table subquery of the derived table is updatable. I haven't quite wrapped my head around what clause 12d means, but 12c is perfectly clear that you only get one table reference. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updateable views...
Gavin Sherry [EMAIL PROTECTED] writes: I haven't had time to look into it further, but it occurs to me that handling views which rely on joins would be far from trivial. Views containing joins would not be updatable; problem solved. I see how that is what the spec says, but aren't the majority of joins that people use/want to update a join of some type? I thought that SQL99 allowed updating view created by joins. In either case is this a place where exceeding the spec would be a good thing or a bad thing? Eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updateable views...
Eric D Nielsen [EMAIL PROTECTED] writes: In either case is this a place where exceeding the spec would be a good thing or a bad thing? Unless there is an obvious definition of what updating a join means (obvious not only to the implementor, but to the user) I think this is dangerous territory. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
I've done a lot with XML lately, so I'll throw in my $0.02 worth. One thing I have noticed about the schemes that are being advanced is that they seem to be inherently unspecifiable, formally, because column names are being used as tags. An alternative might look something like this: ?xml version=1.0? RESULTSET statement=select * from xmltest COLUMNS COLUMN name=scoops type=int / COLUMN name=flavor type=varchar(40) / /COLUMNS ROW FIELD name=scoops isNull=false3/FIELD FIELD name=flavor isNull=falsechocolate/FIELD /ROW ROW FIELD name=scoops isNull=false2/FIELD FIELD name=flavor isNull=falsevanilla/FIELD /ROW /RESULTSET Numbering the rows should be redundant (XPath will give it to you using position(), for example). OTOH, reporting out a null value as opposed to an empty one is probably a good idea. The formal DTD would be something like this (courtesy of the wonderful tools at http://www.hitsw.com/xml_utilites/: !ELEMENT RESULTSET ( COLUMNS, ROW* ) !ATTLIST RESULTSET statement CDATA #REQUIRED !ELEMENT COLUMNS ( COLUMN+ ) !ELEMENT COLUMN EMPTY !ATTLIST COLUMN name NMTOKEN #REQUIRED !ATTLIST COLUMN type CDATA #REQUIRED !ELEMENT ROW ( FIELD+ ) !ELEMENT FIELD ( #PCDATA ) !ATTLIST FIELD isNull ( false| true ) false !ATTLIST FIELD name NMTOKEN #REQUIRED or the equivalent in a schema:?xml version=1.0 encoding=UTF-8 ? xs:schema xmlns:xs=http://www.w3.org/2001/XMLSchema; xs:element name=COLUMN xs:complexType xs:attribute name=type type=xs:string use=required / xs:attribute name=name type=xs:NMTOKEN use=required / /xs:complexType /xs:element xs:element name=COLUMNS xs:complexType xs:sequence xs:element ref=COLUMN minOccurs=1 maxOccurs=unbounded / /xs:sequence /xs:complexType /xs:element xs:element name=FIELD xs:complexType mixed=true xs:attribute name=isNull use=optional default=false xs:simpleType xs:restriction base=xs:NMTOKEN xs:enumeration value=false / xs:enumeration value=true / /xs:restriction /xs:simpleType /xs:attribute xs:attribute name=name type=xs:NMTOKEN use=required / /xs:complexType /xs:element xs:element name=RESULTSET xs:complexType xs:sequence xs:element ref=COLUMNS minOccurs=1 maxOccurs=1 / xs:element ref=ROW minOccurs=0 maxOccurs=unbounded / /xs:sequence xs:attribute name=statement type=xs:string use=required / /xs:complexType /xs:element xs:element name=ROW xs:complexType xs:sequence xs:element ref=FIELD minOccurs=1 maxOccurs=unbounded / /xs:sequence /xs:complexType /xs:element /xs:schema ---(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] Error codes revisited
On Tue, Mar 04, 2003 at 11:04:03PM -0500, Tom Lane wrote: There is still barely enough time to do the long-threatened protocol revision for 7.4, if we suck it up and get started on that now. I've been avoiding the issue myself, because it seems generally boring and thankless work, but maybe it's time to face up to it? Given the repeatedly-asked-for functionalities (like error codes) for which the stopper has been the long-threatened protocol revision, I'd think it might be boring, but would hardly be thankless. Heck, I'd expect a few whoops of joy around the lists. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Aggregate rollup
mlw wrote:
I had written a piece of code about two years ago that used the
aggregate feature of PostgreSQL to create an array of integers from an
aggregate, as:
select int_array_aggregate( column ) from table group by column
While it seems pointless to create an array on a select, it has a
purpose in OLAP. For instance, suppose you do this:
create table fast_lookup as select reference,
int_array_aggregate(result) from table group by result
The fast_lookup table now has all the result entries as an array in a
single row. In the systems that I have used this, it has provided a
dramatic improvement, especially when you have a high number of
identical reference entries in a classic one to many table.
The question is, would a more comprehensive solution be wanted?
Possible? Something like:
create table fast_lookup as select reference, aggregate_array( field )
from table group by field
Where the function aggregate_array takes any number of data types.
Any thoughts? I think I need to fix the code in the current
/contrib/intagg anyway, so is it worth doing the extra work to included
multiple data types?
It's also useful in conjunction with statistically processing. There is
a array_accum function in PL/R; I just made a post to the SQL list the
other day on this.
(http://archives.postgresql.org/pgsql-sql/2003-03/msg00124.php)
Here's the meat of it:
CREATE OR REPLACE FUNCTION array_accum (_name, name)
RETURNS name[]
AS '$libdir/plr','array_accum'
LANGUAGE 'C';
CREATE AGGREGATE accumulate (
sfunc = array_accum,
basetype = name,
stype = _name
);
regression=# SELECT accumulate(tablename) as cruft FROM pg_tables WHERE
tablename LIKE 'c%';
cruft
---
{connectby_int,connectby_text,ct,cth}
(1 row)
See:
http://www.joeconway.com/plr/doc/plr-aggregate-funcs.html
and download at:
http://www.joeconway.com/plr/
I'd be happy to split the array functions out of PL/R and sumbit them to
PATCHES if there is any interest.
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PGTTY?
Peter Eisentraut [EMAIL PROTECTED] writes: Is the so-called debug TTY functionality that you can set in libpq in various ways still existing? I can't seem to activate it and the code doesn't show much reference to it. (a) I believe the TTY option is disabled in the normal postmaster context, on the theory that it's a security hole. The switch is probably still accessible for a standalone backend. (b) I'm not sure it does anything anymore anyway. Didn't you rip out the support for it in elog.c? All it was was code to redirect stderr during startup. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
[EMAIL PROTECTED] writes:
I think all psql needs is a simple output, similar to the ones used by
Oracle, Sybase, and MySQL; the calling application should then process
it in some way as needed (obviously this is not for interactive use).
Where can one find a standard table model?
I think for processing-oriented output, the system described in the
SQL/XML standard draft is the way to go. Considering the people who wrote
it, it's probably pulled from, or bound to appear in, a major commercial
database.
I also think that psql is not the place to implement something like this.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
Then any interface and application that likes it, not just psql-based
ones, can use it.
--
Peter Eisentraut [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] XML ouput for psql
Andrew Dunstan writes: One thing I have noticed about the schemes that are being advanced is that they seem to be inherently unspecifiable, formally, because column names are being used as tags. The SQL/XML draft addresses this by specifying that a mapping from SQL things to XML things spits out both the specification (XML Schema, IIRC) and the data in one operation. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML ouput for psql
Bob Calco writes: I would like to see PostgreSQL eventually support XQuery: The specification is here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.txt Go for it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Who puts the Windows binaries on the FTP server?
There are Windows binaries on the PostgreSQL FTP server mirrors, for example, http://ftp.de.postgresql.org/mirror/postgresql/binary/v7.3.1/Windows/ that users are having problems with. Apparently there is no name or address of any creator available. So who did this and would like to fix the packaging? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Who puts the Windows binaries on the FTP server?
Justin put them up, but I believe that any bug reports for them should be sent to [EMAIL PROTECTED] ... On Wed, 5 Mar 2003, Peter Eisentraut wrote: There are Windows binaries on the PostgreSQL FTP server mirrors, for example, http://ftp.de.postgresql.org/mirror/postgresql/binary/v7.3.1/Windows/ that users are having problems with. Apparently there is no name or address of any creator available. So who did this and would like to fix the packaging? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML ouput for psql
Thanks, Merlin, for your thoughtful comments. There is no question that the marriage of XML and SQL is wrought with semantic difficulties. I'm not sure the win is compelling enough to justify re-writing the PostgreSQL back end; on the other hand, it is a juicy technical challenge! I like PostgreSQL just as it is, and truth be told, I'd like to see some additional features in PL/pgSQL that are completely unrelated to the whole XML issue. But I see some interesting possibilities for PostgreSQL to make inroads in enterprise development if it were the first open source database to do something truly useful with XQuery concepts. So I had this idea. If I can find a few good co-conspirators, I'm prepared to get involved seriously with developing this add-on capability, whatever it takes. Mostly, I suspect, time... Sincerely, Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 10:19 AM %% To: [EMAIL PROTECTED] %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% This is just about a total conversion of the backend to an xml document %% server. The marriage of xml and sql is awkward and not easily %% retrofitted to existing databases. %% %% Its pretty much proven that hierarchal storage techniques (xml included) %% are more difficult to manage and use than traditional sql databases. %% However, xml does have some very powerful supplemental technologies for %% document generation on the client end, especially xslt. Unless there %% is a compelling reason to use those tools, you are 99% likely better off %% not using xml at all. XML has also found a niche in the edi world, but %% in this case you can get away with using the blob technique below. %% %% Before thinking about xquery you have to think about the role tuples and %% relations play in storing xml data. The simplest route is to store your %% xml data in a blob object and use little tricks like xslt to extract %% elements out of the document into separate fields for index purposes and %% use vanilla sql queries to get the data. This is a very loose coupling %% of sql and xml and leads to very brittle designs because at the very %% least you have to store two separate definitions of your data as well as %% your marshalling xslt scripts. %% %% I wrote a little blob based xml server which you can see here if you are %% interested: %% http://www.radiosoft.com/freetools.php?Location=Directional+Patterns %% The real magic was in the xslt which you can't see, though. %% %% %% A tighter coupling would involve decomposing your xml structure into %% discrete objects and building xml power into the backend. I think it is %% yet to be proven if this is even reasonably possible. The big problem %% with xml is that there is too many ways to do things, for example the %% choice of dtd or schemas. I think, the problem with this approach is %% recomposing your document involves complex or inefficient queries. If %% you think this is good idea, good luck, many companies have tried and %% I've personally not seen one that seems to work very well. The next %% major version of MS SQL server is rumored to be something of an xml %% document server, and that's been several years in development. %% %% Merlin %% %% -Original Message- %% From: Bob Calco [mailto:[EMAIL PROTECTED] %% Sent: Wednesday, March 05, 2003 9:10 AM %% To: Merlin Moncure; Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: RE: [HACKERS] XML ouput for psql %% %% I would like to see PostgreSQL eventually support XQuery: %% %% http://www.w3.org/TR/xquery/ %% http://www.w3.org/TR/query-datamodel/ %% %% I see potentially an alternative front end called xsql, providing %% substantially the same functionality as psql, only using XQuery syntax %% and %% optionally returning recordsets as XML. %% %% Anybody want to put together a team to explore this seriously? There %% are %% probably several non-trivial semantic issues on the back end, but I %% only %% dimly grasp them at this point. %% %% - Bob Calco %% %% %% -Original Message- %% %% From: [EMAIL PROTECTED] %% %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin %% Moncure %% %% Sent: Wednesday, March 05, 2003 8:16 AM %% %% To: Alan Gutierrez %% %% Cc: [EMAIL PROTECTED] %% %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% %% %% %% XSLT could be used to convert virtually any xml table format %% directly %% %% into an insert statement. For me, this is better than using a %% %% programming language plus a parser. XSLT is quite powerful and %% fast %% and %% %% is build on top of xpath, and is a closer fit to the declarative %% %% programming model of sql. Validation could be done at the xslt %% stage %% or %% %% with schemas, which I prefer. %% %% %% %% %% %% Acually, the difficult part has been getting the information back %% %% into the database. Getting it out is a
Re: [HACKERS] XML ouput for psql
Thanks for the link - I think I just may give it a go. :) - Bob %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Peter Eisentraut %% Sent: Wednesday, March 05, 2003 5:39 PM %% To: Bob Calco %% Cc: Merlin Moncure; Alan Gutierrez; [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% Bob Calco writes: %% %% I would like to see PostgreSQL eventually support XQuery: %% %% The specification is here: %% %% ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-1 %% 4-XML-2002-03.txt %% %% Go for it. %% %% -- %% Peter Eisentraut [EMAIL PROTECTED] %% %% %% ---(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] XML ouput for psql
Peter Eisentraut [EMAIL PROTECTED] writes:
I also think that psql is not the place to implement something like this.
Agreed.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
That seems a little bizarre. Wouldn't we want to have a switch that
just flips the SELECT output format from one style to the other?
This is also a good time to stop and ask whether the frontend/backend
protocol needs to change to support this. Not having read the spec,
I have no idea what the low-level transport needs are for XML output,
but I suspect our present protocol is not it ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
I like PostgreSQL just as it is, and truth be told, I'd like to see some additional features in PL/pgSQL that are completely unrelated to the whole XML issue. But I see some interesting possibilities for PostgreSQL to make inroads in enterprise development if it were the first open source database to do something truly useful with XQuery concepts. Um, why change the backend at all? Why not have libpq do the interference mapping between the front end and backend so that we can leave the backend alone? Seems like a simple application of a good SAX parser to me. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
Tom Lane wrote: This is also a good time to stop and ask whether the frontend/backend protocol needs to change to support this. Not having read the spec, I have no idea what the low-level transport needs are for XML output, but I suspect our present protocol is not it ... It might be interesting to modify the protocol (and the backend at the point of projection to the front end) so that a user defined formating function could be applied and either accepted or rejected by the front end. Perhaps one flavor of XML output is a start, but I could imagine wanting a custom or even different standard output format. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML ouput for psql
If it turns out to be that simple, great - my first goal would be not to touch the backend at all. But I suspect there are some significant semantic issues lurking in the spec that may make that goal unattainable. In any event, I agree with the impulse to change nothing in the backend unless absolutely necessary to do it right - and only then if we determine upon careful consideration that it really is worth doing. I *think* it *might* be well worth it - but only real effort will yield a definitive answer. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Sean Chittenden %% Sent: Wednesday, March 05, 2003 7:51 PM %% To: Bob Calco %% Cc: Merlin Moncure; [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% I like PostgreSQL just as it is, and truth be told, I'd like %% to see some %% additional features in PL/pgSQL that are completely unrelated %% to the whole %% XML issue. But I see some interesting possibilities for %% PostgreSQL to make %% inroads in enterprise development if it were the first open %% source database %% to do something truly useful with XQuery concepts. %% %% Um, why change the backend at all? Why not have libpq do the %% interference mapping between the front end and backend so that we can %% leave the backend alone? Seems like a simple application of a good %% SAX parser to me. -sc %% %% -- %% Sean Chittenden %% %% ---(end of broadcast)--- %% TIP 4: Don't 'kill -9' the postmaster %% ---(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] XML ouput for psql
Peter Eisentraut [EMAIL PROTECTED] writes:
I also think that psql is not the place to implement something like this.
Agreed.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
That seems a little bizarre. Wouldn't we want to have a switch that
just flips the SELECT output format from one style to the other?
Ah, but this approach has the merit that it doesn't require pushing out
a completely new set of tools.
This is also a good time to stop and ask whether the frontend/backend
protocol needs to change to support this. Not having read the spec, I
have no idea what the low-level transport needs are for XML output,
but I suspect our present protocol is not it ...
That could be; there's enough variation in what one might want to do
with XML that it is not trivial to suggest an 'ideal' answer.
We have already seen the proposal of:
record a=b c=d e=f
record a=c c=e e=g
record a=d c=f e=h
record a=e c=g e=i
I would rather prefer something like:
tablea
record
ab/a cd/c ef/e
/record
record
ac/a cd/c ef/e
/record
record
ad/a cd/c ef/e
/record
tablea
(Note that both approaches are quite rational possibilities.)
I'd think that the protocol would involve passing back a row-as-string
for each row in the result set.
--
output = (cbbrowne @cbbrowne.com)
http://www.ntlug.org/~cbbrowne/xml.html
There are two major products that come out of Berkeley: LSD and Unix.
We don't believe this to be a coincidence. - Jeremy S. Anderson
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pgsql.com website store
Hi, I tried to go buy a shirt off the pgsql.com site, but when it comes to shipping it just has: international shipping zone 1 international shipping zone 2 international shipping zone 3 international shipping zone 4 international shipping zone 5 How am I supposed to know which zone Australia is in? 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] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:02]: Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. XSLT could be used to convert virtually any xml table format directly into an insert statement. For me, this is better than using a programming language plus a parser. XSLT is quite powerful and fast and is build on top of xpath, and is a closer fit to the declarative programming model of sql. Validation could be done at the xslt stage or with schemas, which I prefer. XSLT, or Perl, or anything. That's not a problem. It becomes a problem when I have to hand write insert/update statements for every type of element in an XML document. person first-nameAlan/first-name last-nameGutierrez/last-name ssn1234565789/ssn /person If I feed this document to a database I want it to absorb the document, inserting if doesn't already exists, updating it if it does. There is no way to test for the existstence of a record in a person table during an XSLT transformation. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Aggregate rollup
mlw [EMAIL PROTECTED] writes: I had written a piece of code about two years ago that used the aggregate feature of PostgreSQL to create an array of integers from an aggregate, as: select int_array_aggregate( column ) from table group by column I found this and am using it extensively. It's extremely helpful, thank you. It goes well with either the *= operators in contrib/array or the gist indexing in contrib/intarray. One problem I've found though is that the optimizer doesn't have any good statistics for estimating the number of matches of such operators. It seems like fixing that would require a lot of changes to the statistics gathered. create table fast_lookup as select reference, aggregate_array( field ) from table group by field Where the function aggregate_array takes any number of data types. Sure, that seems logical. Actually I already bumped into a situation where I wanted an array of char(1). I just kludged it to use ascii() of that first character, but it would be cleaner and perhaps better for unicode later to use the actual character. Someone else on the list already asked for an function that gave an array of varchar. I think they were pointed at a general purpose function from plr. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Purpose of core group
Just a reminder on the purpose of the core group --- it is to handle items that require confidentiality, like confidential discussions with companies and for discipline. Marc wants the core group to do as little as possible, so that almost all activity is done in the open, and the other core group members follow that lead. I did have some confidential discussions on my recent trip, and these discussions have been shared with the core group --- nothing super-secret, just things people are doing with PostgreSQL that they don't want to be public yet. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] My contract has been renewed
FYI, my contract with SRA has been renewed for another year, meaning I will continue the next year working on PostgreSQL full time. I want to thank SRA for their support of the PostgreSQL effort by employing me. As you may know, Tatsuo Ishii also works for SRA full time and he is a great help to the effort as well. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Who puts the Windows binaries on the FTP server?
Marc G. Fournier wrote: Justin put them up, but I believe that any bug reports for them should be sent to [EMAIL PROTECTED] ... Yep, that's the first Proof of Concept build, and it *prominently* has a message at the start of the installation that says to email me with any problems about it. I'm open to suggestions for making a more visible way for people to know how to contact us, if needed. :) Regards and best wishes, Justin Clift On Wed, 5 Mar 2003, Peter Eisentraut wrote: There are Windows binaries on the PostgreSQL FTP server mirrors, for example, http://ftp.de.postgresql.org/mirror/postgresql/binary/v7.3.1/Windows/ that users are having problems with. Apparently there is no name or address of any creator available. So who did this and would like to fix the packaging? -- Peter Eisentraut [EMAIL PROTECTED] -- 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 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 Powerfail testing
Tatsuo Ishii wrote: Sorry, but it does not help. The page says we could use FlushFileBuffers() to sync the kernel buffer to the disk. Unfortunately, it requires a file descriptor to flush for its argument. Thus it could not be a replacement of sync(). Actually I have modified the buffer manager so that it remembers all file descriptors those have not been synced yet to the disk at the checkpoint time to sync them later. However I found this modification does not help at all with some reason I don't know. It would be an interesting comparison for you to roll the file descriptor tracking changes into the Unix side of the tree and use fsync() or fdatasync() in place of FlushFileBuffers() on the Unix side (you'd have to remove or disable the code that does a sync() of course). If the end result yields no data corruption issues during powerfail testing on various Unix platforms then it's reasonably likely that the problem you're experiencing on the Windows side is with the underlying Windows platform and not with your code. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Row level stats
It would be nice if PGAvd could receive row level stats without a large
hit to simple queries.
Ran a simple test. Calling pgstat_report_tabstat() at a frequency of
once per second reduces the time taken for row level stats to be
negligible:
500k select TRUE statements took:
6:50 with stats off
8:35 with row level stats on
6:52 with the below applied and row level stats on
Anyone object to delaying the submission of stats by that timeframe?
Are row level stats enough for PGAvd?
More or less the below for the change on line 1912 in postgres.c
currtme = GetCurrentAbsoluteTime();
if (lasttme currtme)
{
pgstat_report_tabstat();
lasttme = currtme;
}
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:03]: This is just about a total conversion of the backend to an xml document server. The marriage of xml and sql is awkward and not easily retrofitted to existing databases. Its pretty much proven that hierarchal storage techniques (xml included) are more difficult to manage and use than traditional sql databases. However, xml does have some very powerful supplemental technologies for document generation on the client end, especially xslt. Unless there is a compelling reason to use those tools, you are 99% likely better off not using xml at all. XML has also found a niche in the edi world, but in this case you can get away with using the blob technique below. I suppose this was in response to my comments, hard to tell with all the top posting... I am not advocating adding XML storage to the PostgreSQL backend. XML is just a stupid little tag language. PostgreSQL is so much more. I adopted XML in my application to generated HTML via XSLT in Internet Explorer. XSLT is very useful when the interface is expressed in HTML. It has been as good a format as any for transmitting documents and storing them on file system. Yes, mapping XML to SQL requires developer intervention. The little program I wrote makes it very simple to express the mapping from and XML element to db table. It seems like it might be a useful add on. Maybe it could be kept with the back end, and written in C. Getting the information out PostgreSQL has been simple, I've used Perl. Getting information back in has been simple with my utility which will check if the element is already stored as a row, and update, otherwise it will insert. It can also delete rows based on the document and keep track of placeholder values used by the client program to represent SERIAL values in database. What I've got is a system where I post an XML document to a URL and it gets mapped out to my normalized PostgreSQL schema. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(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] Row level stats
Rod Taylor [EMAIL PROTECTED] writes: [ optimizing for small frequent queries ] What if the client doesn't come back with another query for awhile? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ETA for PostgreSQL 7.3.3?
Hi guys, Feels like we've been isolating a whole bunch of bugs in 7.3.2 recently, some of which are causing crashes out in the real world. Wondering when we feel it'd be good to start assembling a 7.3.3? I'm thinking in about two weeks or so, to give a bit more time to catch bugs and stuff. Any thoughts/suggestions? Regards and best wishes, Justin Clift -- 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] ETA for PostgreSQL 7.3.3?
Feels like we've been isolating a whole bunch of bugs in 7.3.2 recently, some of which are causing crashes out in the real world. Wondering when we feel it'd be good to start assembling a 7.3.3? I'm thinking in about two weeks or so, to give a bit more time to catch bugs and stuff. I really should fix this rowtype problem for 7.3.3 - here's hoping I find some time... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [GENERAL] problems with dropped columns
Christopher Kings-Lynne wrote: I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Here is a self contained example using cvs tip: CREATE TABLE fk_test (f1 int, f2 int); insert into fk_test(f1, f2) values(1, 21); insert into fk_test(f1, f2) values(2, 22); ALTER TABLE fk_test DROP COLUMN f2; ALTER TABLE fk_test ADD COLUMN f3 int; insert into fk_test(f1, f3) values(3, 33); insert into fk_test(f1, f3) values(4, 34); regression=# select * from fk_test ; f1 | f3 + 1 | 2 | 3 | 33 4 | 34 (4 rows) CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) 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] [GENERAL] problems with dropped columns
Christopher Kings-Lynne wrote: I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Taking it a bit further... CREATE TABLE fk_test (f1 int, f2 int); insert into fk_test(f1, f2) values(1, 21); insert into fk_test(f1, f2) values(2, 22); ALTER TABLE fk_test DROP COLUMN f2; ALTER TABLE fk_test ADD COLUMN f3 int; insert into fk_test(f1, f3) values(3, 33); insert into fk_test(f1, f3) values(4, 34); CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) regression=# ALTER TABLE fk_test DROP COLUMN f3; ALTER TABLE regression=# select * from test(); f1 1 2 3 4 (4 rows) regression=# ALTER TABLE fk_test ADD COLUMN f3 int; ALTER TABLE regression=# select * from test(); WARNING: Error occurred while executing PL/pgSQL function test WARNING: line 5 at return next ERROR: Wrong record type supplied in RETURN NEXT CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] problems with dropped columns
Joe Conway [EMAIL PROTECTED] writes: Taking it a bit further... There are (at least) two distinct problems involved here. One is getting plpgsql to deal correctly with rowtypes that include dropped columns. The other is getting it to react when someone alters a table whose rowtype is relied on by already-compiled functions. The former problem is just a small matter of programming in plpgsql; I'm not sure what the best way to do it is, but it's clearly just plpgsql's issue. The latter problem calls for a ton of infrastructure that we haven't got :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] I am back
I am back from China and Japan. Most of my visitations were private, but I did speak at Renmin University in China. I will catch up on my email in the next few days. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
While I can see a subquery in UPDATE as working in most cases:
UPDATE tab
SET col - t.col
FROM (SELECT col from xx) AS t
WHERE ...
but I don't see that working for correlated subqueries, where you want
to set a column based on a value you are updating. (Many use correlated
subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
subqueries? I can't see how they would because you don't have a row
being processed at the FROM stage of the query.
I did look at the SQL99 standards and ROW does appear there:
update statement: positioned ::=
UPDATE target table
SET set clause list
WHERE CURRENT OF cursor name
set clause list ::=
set clause [ { comma set clause }... ]
set clause ::=
update target equals operator update source
| mutated set clause equals operator update source
update target ::=
object column
-- | ROW
| object column
left bracket or trigraph simple value specification right
bracket or trigraph
and later it says:
a) If update target specifies ROW, then let CL be the set of
all columns of T.
The TODO item would be:
Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
This also gets into that weird Informix syntax where you have to
double-paren when you want to use a subquery. Basically, this thing
keeps getting wierder and wierder.
---
Dave Cramer wrote:
Given that the direction of the spec seems to be headed towards the
desired syntax, can we put this on the TODO list?
Dave
On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
Scott,
I can't find page 858 in that document, is it the right one?
also the link s/b ?
ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
On Thu, 20 Feb 2003, Tom Lane wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
Are you against it just on grounds of cleanliness and ANSI compliance,
or do you see more serious problems in letting it in ?
At this point it seems there are two different things being tossed
about. I originally understood Dave to be asking for parens to be
allowed around individual target column names, which seems a useless
frammish to me. What Bruce has pointed out is that a syntax that lets
you assign multiple columns from a single rowsource would be an actual
improvement in functionality, or at least in convenience and efficiency.
(It would also be a substantial bit of work, which is why I think this
isn't what Dave was offering a quick patch to do...) What I'd like to
know right now is which interpretation Informix actually implements.
I don't like adding nonstandard syntaxes that add no functionality ---
but if Informix has done what Bruce is talking about, that's a different
matter altogether.
Tom, I was purusing the wild and wonderfully exciting new SQL
(found here:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
ANSI TC NCITS H2
ISO/IEC JTC 1/SC 32/WG 3
Database
document to see what it had to say, and on this subject, and it looks like
update is going to be supporing this same style we're discussing here.
Look on or around p. 858 in that doc.)
--
Dave Cramer [EMAIL PROTECTED]
Cramer Consulting
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Rod Taylor [EMAIL PROTECTED] writes: 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Anyway, once again we could extend to include: NEXT VALUE ON table(column)? This is looking messier and messier. And, you are all conveniently ignoring the fact that any change in sequence naming conventions will break existing applications. Offering some completely new syntax that they're supposed to use instead won't make people any happier. I think we should stick with the existing naming convention. The only actual problem that's been pointed out here is that an ALTER TABLE (or COLUMN) RENAME on a serial column doesn't update the sequence name to match. Seems to me we could fix that with less effort than any of these solutions would take, and it wouldn't break existing applications. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Alpha-2 of contrib/tsearch
Is this to be applied to CVS? My guess is no. --- Teodor Sigaev wrote: Changes: 1 Fixed compile problem on Solaris 2 Add search by weight of lexem. Readme: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/README-V2.txt Tar: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch.tar.gz We still need a documentation... Any comments please send to Oleg Bartunov ([EMAIL PROTECTED]) and Teodor Sigaev ( [EMAIL PROTECTED] ). -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
On Wed, 2003-03-05 at 10:36, Bruce Momjian wrote:
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
We are :)
CREATE TABLE tab (col SERIAL);
ALTER TABLE tab RENAME TO tab2;
CREATE TABLE tab (col SERIAL);
There is a chance we could do something like:
ALTER SEQUENCE ON table(col) RESTART WITH value
instead of
SELECT setval(sequence, value);
The ALTER SEQUENCE syntax isn't any worse than setval... I'd be willing
to implement the feature in order to get this patch accepted.
Oh, and welcome back!
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] Win32 Powerfail testing
On Wed, 5 Mar 2003, Dave Page wrote: -Original Message- From: Tatsuo Ishii [mailto:[EMAIL PROTECTED] Sent: 05 March 2003 02:23 To: [EMAIL PROTECTED] Subject: [HACKERS] Win32 Powerfail testing So far we found interesting facts. Our Win32 port passes his test in most cases. However if power of the machine is turned off right after (10 to 20 seconds) the Checkpoint has been made, it does not passes his test. So we are thinking that there is someting wrong with the checkpoint implementaion for Win32 port, which is essentially same as Jan's implementation. i.e. using _flushall() instead of sync(). We were looking for a fix or an alternative implementaion of sync() without success. Hi Tatsuo, Does this help: http://support.microsoft.com/default.aspx?scid=kb;en-us;66052 OMG, I'm rolling. You have to connect to the COMMODE.OBJ to fix a flushing problem. Someone at MS has a sense of humor. I thought running PHP on crack was funny (i.e. --with-crack switch to turn on cracklib) but this one is even better. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Alpha-2 of contrib/tsearch
On Wed, 5 Mar 2003, Bruce Momjian wrote: Is this to be applied to CVS? My guess is no. me too. I think we could submit new version after we'll have documentation. --- Teodor Sigaev wrote: Changes: 1 Fixed compile problem on Solaris 2 Add search by weight of lexem. Readme: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/README-V2.txt Tar: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch.tar.gz We still need a documentation... Any comments please send to Oleg Bartunov ([EMAIL PROTECTED]) and Teodor Sigaev ( [EMAIL PROTECTED] ). -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 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] [PATCHES] Non-colliding auto generated names
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Interesting idea. The bigger problem is that apps who use the sequence
name also would have problems running after the restore. Seems we need
column.nextval() so you can increment the sequence without knowing the
sequence name, just the column name. Of course, this related to this
TODO item:
* Have sequence dependency track use of DEFAULT sequences,
seqname.nextval
Comments?
---
Rod Taylor wrote:
-- Start of PGP signed section.
On Wed, 2003-03-05 at 10:36, Bruce Momjian wrote:
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
We are :)
CREATE TABLE tab (col SERIAL);
ALTER TABLE tab RENAME TO tab2;
CREATE TABLE tab (col SERIAL);
There is a chance we could do something like:
ALTER SEQUENCE ON table(col) RESTART WITH value
instead of
SELECT setval(sequence, value);
The ALTER SEQUENCE syntax isn't any worse than setval... I'd be willing
to implement the feature in order to get this patch accepted.
Oh, and welcome back!
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
On Wed, 2003-03-05 at 14:37, Bruce Momjian wrote: Interesting idea. The bigger problem is that apps who use the sequence name also would have problems running after the restore. Seems we need column.nextval() so you can increment the sequence without knowing the sequence name, just the column name. Of course, this related to this TODO item: * Have sequence dependency track use of DEFAULT sequences, seqname.nextval 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Other than that it should be a fairly simple task. Anyway, once again we could extend to include: NEXT VALUE ON table(column)? An application that was simply interested in the next value of a table column could simply evaluate the default value -- which should be easily retrievable and more portable in most interfaces (jdbc, odbc, etc.). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Yes, our sequence name/column linkage should be much more automatic than it is now, and if we do that, we can start to think about sequence name collision avoidance. --- Rod Taylor wrote: -- Start of PGP signed section. On Wed, 2003-03-05 at 14:37, Bruce Momjian wrote: Interesting idea. The bigger problem is that apps who use the sequence name also would have problems running after the restore. Seems we need column.nextval() so you can increment the sequence without knowing the sequence name, just the column name. Of course, this related to this TODO item: * Have sequence dependency track use of DEFAULT sequences, seqname.nextval 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Other than that it should be a fairly simple task. Anyway, once again we could extend to include: NEXT VALUE ON table(column)? An application that was simply interested in the next value of a table column could simply evaluate the default value -- which should be easily retrievable and more portable in most interfaces (jdbc, odbc, etc.). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
I think we should stick with the existing naming convention. The only actual problem that's been pointed out here is that an ALTER TABLE (or COLUMN) RENAME on a serial column doesn't update the sequence name to match. Seems to me we could fix that with less effort than any of these solutions would take, and it wouldn't break existing applications. Non-colliding? Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I think we should stick with the existing naming convention. ^^ Non-colliding? No; see above. Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Why? We already rename the table's rowtype, ergo you can fail a table rename because there is a conflicting datatype name. I don't see anything much wrong with failing a table or column rename because there is a conflicting sequence name. The whole point here is to have a non-surprising mapping between the names of serial columns and the names of their associated sequences. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] problems with dropped columns
I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Chris - Original Message - From: Damjan Pipan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 9:36 PM Subject: [GENERAL] problems with dropped columns Hi! I have following problem: I have created a table with some fields, then I dropped last field (integer) and added one extra field (integer). Then I have created a function which returns record of table type. I have selected a record from table and returned it, but the values in last field are wrong (missing). It looks like that it takes the dropped field instead of the last field. Damjan CREATE OR REPLACE FUNCTION damjan_test111(integer) RETURNS public.fk_test AS ' DECLARE rec fk_test%ROWTYPE; siteid ALIAS FOR $1; BEGIN FOR rec IN SELECT * FROM public.fk_test WHERE i = siteid LOOP RETURN rec; END LOOP; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] [PATCHES] Non-colliding auto generated names
Added to TODO: o Have ALTER TABLE rename SERIAL sequences Seems we at least need this. Doesn't dependency tracking make this easy to do now? --- Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: I think we should stick with the existing naming convention. ^^ Non-colliding? No; see above. Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Why? We already rename the table's rowtype, ergo you can fail a table rename because there is a conflicting datatype name. I don't see anything much wrong with failing a table or column rename because there is a conflicting sequence name. The whole point here is to have a non-surprising mapping between the names of serial columns and the names of their associated sequences. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
