Re: [HACKERS] 7.5 backend crash
Gaetano Mendola [EMAIL PROTECTED] writes: kalman=# create table test ( a integer, b integer ); CREATE TABLE kalman=# create table test1 ( c integer , d test ); CREATE TABLE kalman=# alter table test drop column b; ALTER TABLE --- Here I think the server shall complain about Yeah, the design intention is that the ALTER should refuse to do it. Looks like I missed a case. Will fix, thanks for the report. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG 7.4.3 optimizer choosing sequential scan. Why?
Barry S [EMAIL PROTECTED] writes: * The table contains one index: P1_NRN_ROAD_V (v, sobjid) (The index includes the column sobjid because the query projects this col, and its inclusion in the index allows it to be serviced without accessing the underlying table) (Unlike Oracle) Postgres *always* has to access the underlying table. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.5 release notes
Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Jul 26, 2004 at 02:22:21AM +0200, Gaetano Mendola wrote: In the release not I can read: This allows columns to contain arbitrary composite types like rows from other tables. [SNIPPED] Wasn't this alread true on 7.4 ? No, this is a new feature. Did you try inserting anything into the tables? Postgres has always accepted this syntax, but it has not done anything useful in many years, and when it last did do something useful the semantics were quite a bit different :-( Elein has standing to bash me about the change of semantics, but I don't think the rest of you do ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Weird...but correct?
CREATE TYPE test (a int4, b int4); COMMENT ON COLUMN test.a IS 'A column'; Seems harmless, but should we allow it? Actually, currently it's bad because such comments will not be dumped by pg_dump. Shall I fix pg_dump? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Improvements to PostgreSQL
Hi, I am already a member of hackers group and I get all the messages posted to the group. In fact I get 2 copies of each!! I think I subscribed twice. But I am unable to send messages to the group. Here are the feature list which we are planning to implement. This list is obtained by comparing the features of Oracle 8i and Postgresql 7.4 . We know that a lot more work has to be done in finding the exact features and the way to implement them. Anyway this is our initial list : Our basic idea is to divide the task of migrating from Oracle to postgresql in 3 parts 1. Automation of migration from Oracle to postgresql :- there is already the tool ora2pg - but we came to know that a lot of things have to be improved to totally automate it. 2. Core functionality add-ons to postgresql :- -Table joins -Java stored procedures -Backup and recovery (incremental backup/restore) -Query optimization * Indexing techniques * Star query optimization - Data Types * BLBL * Images * Long Some of the above features are already present, but may not be complete(as compared to Oracle). 3. Application compatibility -Oracle Compatible JDBC driver -PL/SQL Compatibility * Named parameter * Parameterized Cursors * Output Paramters -Query compatibility * Oracle DML/DDL Compatibility * Behavioral differences like null handling * Nested Queries * Manual Optimization Hints I know that this list is not totally explainative, but we will provide you further details as we investigate deeper in this regard. All your sugestions are welcome. Please help us to implement these features. Our aim is to make postgresql enterprise level. Thanks, Suresh --- Bruce Momjian [EMAIL PROTECTED] wrote: I am forwarding this to hackers on behalf of some people who want to improve PostgreSQL in India. I suggest you read the developers FAQ on the developers page: http://developers.postgresql.org then subscribe to hackers to discuss feature additions. Thanks. --- Suresh Tri wrote: Hi all, We are a bunch(about 10)of enthusiastic software engineers from Bangalore who strongly believe in opensource. We are currently looking at the prospect of improving the PostgreSQL to the enterprise level. At first we would like to take PostgreSQL atleast to the level of Oracle 7i or 8i. We already have the difflist of features differences between Oracle 8i and the current PostgreSQL(7.4). But we are finding it hard to prioritize the features. What do you think are the important features that are present in Oracle 8i but absent in the current PostgreSQL ehich the enterprises look at. It would be of the great help if you can tell us about these featues and also the way in which we can contribute to the PostgreSQL development. -- 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]) ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://www.allnewmessenger.com ---(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] CVS web interface error
On Mon, 2004-07-26 at 05:57, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: CVS web interface has not been working for a few days. Not important right now, but something to fix soon-ish. It works okay for me (modulo $PostgreSQL$ issue, see other thread). What URL are you visiting to get the stated error? No, this isn't a CVSup issue. Go to http://developer.postgresql.org/ then pick PostgreSQL Server CVS web interface. That successfully displays you list of files. Pick one, click on it and that file's history appears. Click download and it fails consistently with the error: -- Error Error: Unexpected output from cvs co: cvs [checkout aborted]: Absolute module reference invalid: `/pgsql-server/COPYRIGHT' Check whether the directory /cvsroot//CVSROOT exists and the script has write-access to the CVSROOT/history file if it exists. The script needs to place lock files in the directory the file is in as well. -- The error is received no matter which file you pick. I've got no problems with CVS, just the web interface CVSweb. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Improvements to PostgreSQL
Suresh Tri wrote: 2. Core functionality add-ons to postgresql :- -Table joins Already done. -Java stored procedures Already done. -Backup and recovery (incremental backup/restore) Will appear in next release. -Query optimization * Indexing techniques * Star query optimization Good luck with that. :-) - Data Types * BLBL * Images * Long These are probably redundant with bytea. 3. Application compatibility -Oracle Compatible JDBC driver JDBC drivers should be compatible with the JDBC standard, not with some vendor. -Query compatibility * Oracle DML/DDL Compatibility Like what? * Behavioral differences like null handling Not gonna happen. * Nested Queries Do you mean recursive queries? * Manual Optimization Hints These are probably not going to be compatible with Oracle anyway. All your sugestions are welcome. Please help us to implement these features. Our aim is to make postgresql enterprise level. PostgreSQL is already enterprise-level. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Stored procedures - Oracle vs postgresql
Hi all, I am currently trying to estimate the effort required to implement Oracle type stored procedure in PostgreSQL. As I understood Oracle supports both functions and procedures, but postgres only functions. ALso there are no OUT parameter in postgres. I got some info from http://www.compiere.org/technology/pg/porting.html Can anyone please help me estimate the effort / feasibility in implementing Oracle type procedures in postgres? Any pointers are welcome. Thanks, Suresh ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://www.allnewmessenger.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] group by query?
In 7.4.3, my workmate came across the following situation he thought was odd. Basically, we want to know why the group by and order by in the outer query get their variables from the inner query and not the select parameters in the outer query? Chris -- shouldn't the first SELECT query perform the GROUP BY and ORDER BY upon the date -- in the outer SELECT as in the second query BEGIN; CREATE TABLE test_dates ( date TIMESTAMP ); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 0); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 1); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 2); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 3); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 4); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 5); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 6); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 7); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 8); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 9); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 10); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 11); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 12); INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 13); SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM test_dates) AS sub GROUP BY date ORDER BY date; SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM test_dates) AS sub GROUP BY 1 ORDER BY 1; ROLLBACK; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Improvements to PostgreSQL
Suresh Tri wrote: snip All your sugestions are welcome. Please help us to implement these features. Our aim is to make postgresql enterprise level. Hi Suresh, From reading your post, I feel your team is approaching the goal of making PostgreSQL Enterprise Level in a non-optimal way. With the soon to be released version 7.5 of PostgreSQL, the core database engine itself is already very good. This is not the area needing to be worked upon for the next level of Enterprise Functionality. Your team will likely have a lot more effect if they concentrate on what Enterprises really need that PostgreSQL is missing: + An SNMP agent to report on PostgreSQL's status and allows remote control of the PostgreSQL daemon. From an Oracle perspective, this would be the equivalent of Oracle Intelligent Agents, part of the core features of the Oracle Enterprise Manager (OEM). + Tools to allow control of PostgreSQL databases from one central place. Again, the same as OEM. + Starting and stopping the database + Managing Users + Backup and Recovery + Alerts and submitting jobs + etc Oracle does this by having a centralised information repository that a management GUI connects too, and having Oracle Intelligent Agents running on each server the database software is on. These Oracle Intelligent Agents keep the centralised repository aware of the status of the Oracle server software, perform actions on the Oracle servers as directed by the centralised repository (jobs running on there, instructions by the GUI, etc), and more. There's more to what the OEM GUI does, but that's a good start. + Something else that would be useful is a GUI tool to automatically setup PostgreSQL replication. The PostgreSQL Slony-I project would be the first one to look at, and probably equivalent to something like Oracle's Data Guard. They use the different approach, but the end result is having a master and standby databases. Hope this is helpful. Regards and best wishes, Justin Clift Thanks, Suresh snip ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Improvements to PostgreSQL
Thanks Peter for your reply, we will investigate further into your comments. We understand that postgresql is already enterprise-level. But as our observation many of the propriety databse users in the enterprise level are reluctant to switch to postgresql. We wanted to to bridge the gap. Thanks once again, Suresh --- Peter Eisentraut [EMAIL PROTECTED] wrote: Suresh Tri wrote: 2. Core functionality add-ons to postgresql :- -Table joins Already done. -Java stored procedures Already done. -Backup and recovery (incremental backup/restore) Will appear in next release. -Query optimization * Indexing techniques * Star query optimization Good luck with that. :-) - Data Types * BLBL * Images * Long These are probably redundant with bytea. 3. Application compatibility -Oracle Compatible JDBC driver JDBC drivers should be compatible with the JDBC standard, not with some vendor. -Query compatibility * Oracle DML/DDL Compatibility Like what? * Behavioral differences like null handling Not gonna happen. * Nested Queries Do you mean recursive queries? * Manual Optimization Hints These are probably not going to be compatible with Oracle anyway. All your sugestions are welcome. Please help us to implement these features. Our aim is to make postgresql enterprise level. PostgreSQL is already enterprise-level. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://www.allnewmessenger.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Triggers on TRUNCATE?
G'day folks, Forgive this if it's totally out there but I was wondering why there's no support for triggers on a TRUNCATE operation. I note that a COPY IN fires an INSERT trigger (if defined). The only real use I can see for a TRUNCATE trigger would be for automatic audit trail generation - I usually have all operations on core tables audited. FYI, I'm using 7.4.3. As always, thanks for your time. Ciao. -- ---+- Daniel Baldoni BAppSc, PGradDipCompSci | Technical Director require 'std/disclaimer.pl'| LcdS Pty. Ltd. ---+ 856B Canning Hwy Phone/FAX: +61-8-9364-8171| Applecross Mobile: 041-888-9794 | WA 6153 URL:http://www.lcds.com.au/| Australia ---+- Any time there's something so ridiculous that no rational systems programmer would even consider trying it, they send for me.; paraphrased from King Of The Murgos by David Eddings. (I'm not good, just crazy) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] group by query?
In 7.4.3, my workmate came across the following situation he thought was odd. Basically, we want to know why the group by and order by in the outer query get their variables from the inner query and not the select parameters in the outer query? Ah, don't worry - I get it now :) Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.5 backend crash
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |kalman=# create table test ( a integer, b integer ); |CREATE TABLE |kalman=# create table test1 ( c integer , d test ); |CREATE TABLE | | |kalman=# alter table test drop column b; |ALTER TABLE--- Here I think the server shall complain about | | | Yeah, the design intention is that the ALTER should refuse to do it. | Looks like I missed a case. Will fix, thanks for the report. Hi, in the mean time that you are working on it I believe that this is ugly too: kalman=# create table test ( a integer ); CREATE TABLE kalman=# alter table test add columb b test; ALTER TABLE kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); INSERT 33639 1 kalman=# select * from test; ~ a | b - ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) The uglyness is on that series of '' why not the following result ? kalman=# select * from test; ~ a | b - ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBMRp7UpzwH2SGd4RAuV+AJsFBLv0pD8U9UaXQKwxnFvqSqWacACgzipD 0ZdJ2csaQ5kBEI3ADnMX4zk= =iKgR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Sketch of extending error handling for subtransactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola [EMAIL PROTECTED] writes: | |Tom Lane wrote: || I was just looking around the net to see exactly what Oracle's PL/SQL || syntax is. It doesn't seem too unreasonable syntax-wise: || [ snip pl/sql syntax ] | | |Is this sintax SQL standard driven ? | | | No, AFAIK it's just Oracle's syntax. | | |If not I'd prefere this one: | [ some other syntax ] | | | Can you point to any SQL standard or existing database that uses your | suggestion? Oracle is certainly the de facto standard in this area, | and plpgsql in particular is an unabashed effort to follow their PL/SQL | implementation... I didn't know we where following the Oracle syntax indeed. | | er ... I'm not clear why type names would have anything to do with | exceptions. What's your vision here exactly? | | regards, tom lane Because I believe that exception handler need to have some informations in order to handle the exception and only the exception_name is not enough, so who generate the exception can throw a type that the handler can use ( I have in mind the C++/Java exception handler ): CATCH INTEGER THEN ~ Work with the integer $1 in order to manage what is going on CATCH VARCHAR THEN ~ Insert in the logs table the message $1 ... but may be I'm completely missing the target that you are going to achieve. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBMsf7UpzwH2SGd4RAqM1AKCSg4jOT7J52nZRU98Np/JAIcAz4wCbB/O3 J94yQ7NFB/JE3uUCR/OgkUs= =oWOX -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] Sketch of extending error handling for subtransactions
I was just looking around the net to see exactly what Oracle's PL/SQL syntax is. It doesn't seem too unreasonable syntax-wise: BEGIN ... controlled statements ... EXCEPTION WHEN exception_name THEN ... error handling statements ... WHEN exception_name THEN ... error handling statements ... ... WHEN OTHERS THEN ... error handling statements ... END; There's nothing here we couldn't do. However, it seems that Oracle thinks you should throw in explicit SAVEPOINT and ROLLBACK statements on top of this! That's just weird. It might be that we should deliberately *not* adopt the exact syntax they are using, just so we don't create compatibility gotchas. That is because they usually use this to handle the exception of only one potentially failing statement, which does not rollback any prev statements (except in pg). Thus in Oracle you need savepoints in a lot fewer cases. It is only in those seldom cases, that you add savepoints on top of blocks with exceptions in Oracle. But yes, I think doing implicit savepoints for plpgsql blocks that contain an exception ... handler is absolutely the way to proceed. For maximum protability that savepoint should probably travel along with every successful statement after the BEGIN (that of course is debateable). BEGIN --implicit savepoint x update 1-- succeeds --implicit release old x, new savepoint x update 2-- fails --position y update 3-- succeeds EXCEPTION -- rollback to savepoint x WHEN -- transfer control to position y END;-- implicit RELEASE savepoint x Doing this only for blocks with an exception handler would not impose any overhead for conventional plpgsql funcs. Andreas PS: can someone please help me get through the lists spam blocker, get Marc to contact me, or I don't know what else I can do ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Stored procedures - Oracle vs postgresql
Suresh Tri wrote: Hi all, I am currently trying to estimate the effort required to implement Oracle type stored procedure in PostgreSQL. As I understood Oracle supports both functions and procedures, but postgres only functions. ALso there are no OUT parameter in postgres. I got some info from http://www.compiere.org/technology/pg/porting.html Please be careful taking information from other websites as given. The view described there is Oracle-centric, and doesn't necessarily reflect PostgreSQL's need. After a quick glance, just one aspect: PostgreSQL indeed does not have default values for function parameters, but there are overloaded functions. so instead of function foo(bar text, nextbar text='someDefault') you have function foo(bar text, nextbar text) and function foo(bar text) as 'select function(bar, 'someDefault') which gives the same result. That's why PostgreSQL doesn't need default values. Stored procedures: There actually is a way to return multiple values from a function, using a set. So procedure foo(bar int IN, result1 text OUT, result2 text OUT) can be converted to a function foo(bar) RETURNS record which can be retrieved as SELECT result1, result2 FROM foo(bar) AS prc(result1 text, result2 text) This might be supportable by wrapping it syntactically. Some weeks ago we had a discussion about Oracle syntax support (or loadable personality support). The thread is named LinuxTag wrapup and started July 3rd, you might want to review it. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CVS web interface error
Simon Riggs wrote: On Mon, 2004-07-26 at 05:57, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: CVS web interface has not been working for a few days. Not important right now, but something to fix soon-ish. It works okay for me (modulo $PostgreSQL$ issue, see other thread). What URL are you visiting to get the stated error? No, this isn't a CVSup issue. Go to http://developer.postgresql.org/ then pick PostgreSQL Server CVS web interface. That successfully displays you list of files. Pick one, click on it and that file's history appears. Click download and it fails consistently with the error: -- Error Error: Unexpected output from cvs co: cvs [checkout aborted]: Absolute module reference invalid: `/pgsql-server/COPYRIGHT' Check whether the directory /cvsroot//CVSROOT exists and the script has write-access to the CVSROOT/history file if it exists. The script needs to place lock files in the directory the file is in as well. -- The error is received no matter which file you pick. I've got no problems with CVS, just the web interface CVSweb. This is probably related to some hardening Marc did recently to cope with some CVS security issues. It can be seen at this URL, for example: http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql-server/README?rev=1.29content-type=text/plain FWIW, cvsweb download is working fine (after a recent fix) on pgfoundry. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CVS web interface error
Simon Riggs [EMAIL PROTECTED] writes: Click download and it fails consistently with the error: Hm, you're right; I hadn't tried that lately but it does fail for me too. Weird. I'd not have thought that download would make a different request to CVS than the revision 1.n link does ... but those links still work ... I'm betting it's some simple oversight in the cvsweb configuration. Marc? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] storage engine , mysql syntax CREATE TABLE t (i INT)
Scott Marlowe [EMAIL PROTECTED] writes: On Sun, 2004-07-25 at 22:23, Tom Lane wrote: I don't think it's either practical or interesting to try to introduce an equivalent layering into Postgres. I can possibly see a use for a row locking storage system, i.e. non MVCC for some applications. But I can't see it being worth the amount of work it would require. I can't either. The implications of such a thing really are so far-reaching that it could not be isolated in a nice little layered API. To take one example, we'd have to reexamine the locking and crash-safety behavior for every single command that updates the system catalogs. For better or for worse, I think we're married to MVCC. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Weird...but correct?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: It's bizarre how you can comment on columns in composite types! CREATE TYPE test (a int4, b int4); COMMENT ON COLUMN test.a IS 'A column'; And not only that, but: regression=# \d+ test Composite type public.test Column | Type | Description +-+- a | integer | A column b | integer | Seems harmless, but should we allow it? Why not? Shall I fix pg_dump? Yes please, if it doesn't seem outlandishly hard. (From memory it might just be adding a subroutine call to dumpType...) 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] Improvements to PostgreSQL
On Mon, Jul 26, 2004 at 06:13:30PM +1000, Justin Clift wrote: + An SNMP agent to report on PostgreSQL's status and allows remote control of the PostgreSQL daemon. From an Oracle perspective, this would be the equivalent of Oracle Intelligent Agents, part of the core features of the Oracle Enterprise Manager (OEM). Is there more to remote control than setting GUC variables? Tell me more! Cheers, Patrick ---(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] Triggers on TRUNCATE?
Daniel Baldoni [EMAIL PROTECTED] writes: Forgive this if it's totally out there but I was wondering why there's no support for triggers on a TRUNCATE operation. Because the entire point of TRUNCATE is not to scan the table contents. If you want to fire triggers, just use DELETE FROM foo. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] CVS web interface error
Working on it ... it was a *really* old version, and from pgfoundry, I imagine it has some issues with the newer CVS ... will post once I have a new one in place that appears to work ... On Mon, 26 Jul 2004, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Click download and it fails consistently with the error: Hm, you're right; I hadn't tried that lately but it does fail for me too. Weird. I'd not have thought that download would make a different request to CVS than the revision 1.n link does ... but those links still work ... I'm betting it's some simple oversight in the cvsweb configuration. Marc? regards, tom lane Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cvsweb upgraded
just upgraded to 3.0.1 ... it has a neat 'graphing' function: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/COPYRIGHT?graph=1 but, I seem to have lost the colors the old one had in the process :( Anyone used/configured cvsweb before? I can't seem to find an option to enable the colors, but there are *alot* of options in the cvsweb.conf file as it is :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] CVS web interface error
On Mon, 2004-07-26 at 16:34, Marc G. Fournier wrote: Working on it ... it was a *really* old version, and from pgfoundry, I imagine it has some issues with the newer CVS ... will post once I have a new one in place that appears to work ... On Mon, 26 Jul 2004, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Click download and it fails consistently with the error: Hm, you're right; I hadn't tried that lately but it does fail for me too. Weird. I'd not have thought that download would make a different request to CVS than the revision 1.n link does ... but those links still work ... I'm betting it's some simple oversight in the cvsweb configuration. Marc? Not too important to me, and I guess I was the only user. ...only used when need to access code when no laptop, so rarely. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Binary Cursors, and the COPY command
OK, I wrote a utility for 7.3 that takes the output of a select command in a Binary cursor and creates a binary COPY file. The premise of the utility is to take the results of two or more selects from external databases and create a single unified table. Here are the issues: In 7.3, COPY BINARY was machine specific and so was the output of a binary cursor. Everything just worked fine. In 7.4, COPY BINARY uses network byte order, i.e. native data types are altered to big endian if nessisary. The documentation for binary cursors does not specify whether or not the binary data is native or network byte order. I have a few issues with COPY BINARY using network byte order, first, it is pointless. The problem it intends to solve, i.e. transfering across different machine types is already answered using the tried and true ascii method. Second, it actually makes the COPY functionality less usable. You can not create the data outside of the database because all the data type definitions and manipulation functions are inside the database. (Unless you only use simple data types, of course.) Third, if a binary cursor does not encode the binary data as network byte order a binary copy can ONLY communicate between two postgreSQL databases because the information required to go from native ordering to network ordering is only in the database. Lastly, the vast majority of machines in use today are intel. Meaning that they are small endian. Except in a very rare circumstance, two machines that would normally be able to communicate in native byte order, will ALWAYS have to convert data. The only use case network byte order fixes is a BINARY COPY between different machine types, but in doing that, it forces anyone trying to add value to postgresql or create a utility that uses COPY to reimplement all the data type handlers outside of the database, even if they never need to interpret or inspect the values, because they have to do this to put them in network byte order. I would say that the history of the word BINARY would tend more to indicate incompatible machine specific data. I would submit that the 7.4 format of data, i.e. one data size int32 instead of an int16 followed by the optional int32 is cleaner, but I would remove the network byte order and put the byte order int32 back in the header for 7.5 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] CVS web interface error
On Mon, 26 Jul 2004, Simon Riggs wrote: On Mon, 2004-07-26 at 16:34, Marc G. Fournier wrote: Working on it ... it was a *really* old version, and from pgfoundry, I imagine it has some issues with the newer CVS ... will post once I have a new one in place that appears to work ... On Mon, 26 Jul 2004, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Click download and it fails consistently with the error: Hm, you're right; I hadn't tried that lately but it does fail for me too. Weird. I'd not have thought that download would make a different request to CVS than the revision 1.n link does ... but those links still work ... I'm betting it's some simple oversight in the cvsweb configuration. Marc? Not too important to me, and I guess I was the only user. ...only used when need to access code when no laptop, so rarely. new one is up, found missing css file for the colors and whatnot ... new one, I think, is alot nicer then the old ... or, at least, it has some nifty new features :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Binary Cursors, and the COPY command
[EMAIL PROTECTED] writes: Lastly, the vast majority of machines in use today are intel. Meaning that they are small endian. Except in a very rare circumstance, two machines that would normally be able to communicate in native byte order, will ALWAYS have to convert data. Quite honestly, that is exactly one of the reasons for using network order. If we standardized on little-endian then 90% of programmers would ignore the need to do anything about byte ordering issues, and their code would be unportable to big-endian machines. Putting a byte order flag into the header doesn't improve matters; it just forces everybody to deal with *both* orders, which is not simpler. (Except for those who would like not to be bothered with portability, which is a position I have zero sympathy for.) If you don't want to deal with this, don't use binary data. It's as simple as that. BTW, for your stated use-case of combining binary output from separate databases, I should think you'd be happy about having a not-so-machine- dependent data format. It would still work if the databases were running on servers of different endianness. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] WARNING: buffer refcount leak
I'm working on a new machine, and i think it's got possible bad hardware, since that seems more likely than a bug in postgresql. I'm wondering if someone has any idea what kind of hardware failure might cause this message: WARNING: buffer refcount leak: [424] (freeNext=425, freePrev=423, rel=0/0, blockNum=4294967295, flags=0x1c, refcount=-631 30464) The one time this happened, postmaster displayed the refcount leak, or it would segfault or it crashed with messages like these: (free(): invalid pointer 0xa06ffc0!). Usually it just works fine, this appears to be a very intermittent problem. We've already replaced the SCA backplane, the SCSI cables, the RAID controller, and the motherboard. The only components not replaced are the memory and the CPUs. I've run Memtest86 on the box for several days without it finding any bad memory. It's the first test I run on any new machine. Can anyone recommend any good (free) diagnostics programs like Memtest86 that check CPUs, PCI bus, etc, etc. The machine is a dual xeon 2.8, 4gb ECC ram, and 14 15k 36G U320 drives with a megaraid 320-2x controller. running fedora core 1, postgres 7.3.4 thanks for any advice, and i hope this isn't too off topic. --brian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.5 backend crash
Gaetano Mendola [EMAIL PROTECTED] writes: kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); INSERT 33639 1 kalman=# select * from test; ~ a | b ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) Omg. This is pretty cool and frankly amazing that it works. But I don't really think it's tenable is it? What happens when you try to pg_dump this? I expect other tools will have trouble dealing with this as well. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CVS web interface error
On Mon, 2004-07-26 at 19:10, Marc G. Fournier wrote: On Mon, 26 Jul 2004, Simon Riggs wrote: On Mon, 2004-07-26 at 16:34, Marc G. Fournier wrote: Working on it ... it was a *really* old version, and from pgfoundry, I imagine it has some issues with the newer CVS ... will post once I have a new one in place that appears to work ... On Mon, 26 Jul 2004, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Click download and it fails consistently with the error: Hm, you're right; I hadn't tried that lately but it does fail for me too. Weird. I'd not have thought that download would make a different request to CVS than the revision 1.n link does ... but those links still work ... I'm betting it's some simple oversight in the cvsweb configuration. Marc? Not too important to me, and I guess I was the only user. ...only used when need to access code when no laptop, so rarely. new one is up, found missing css file for the colors and whatnot ... new one, I think, is alot nicer then the old ... or, at least, it has some nifty new features :) Yes, it works and the new coat of paint looks good too. Thanks. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.5 backend crash
Greg Stark [EMAIL PROTECTED] writes: Gaetano Mendola [EMAIL PROTECTED] writes: kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); INSERT 33639 1 kalman=# select * from test; ~ a | b ---+ ~ 1 | (2,(3,(4,(5,(6,) (1 row) Omg. This is pretty cool and frankly amazing that it works. It shouldn't work --- the ALTER TABLE code should reject it. Looks like there are a few holes to be plugged here ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Binary Cursors, and the COPY command
[EMAIL PROTECTED] wrote: The only use case network byte order fixes is a BINARY COPY between different machine types, but in doing that, it forces anyone trying to add value to postgresql or create a utility that uses COPY to reimplement all the data type handlers outside of the database, even if they never need to interpret or inspect the values, because they have to do this to put them in network byte order. This is not true if you happen to be using Java on the client side, which has no idea (unless you grot around in the guts of the JVM) what the native byte order is. This actually means that Java clients have the opposite problem -- it's a lot of work to try to use the 7.3-style binary formats. I would say that the history of the word BINARY would tend more to indicate incompatible machine specific data. Binary implies not plaintext to me.. What about binary parameters in Bind or binary resultsets from Execute? They follow the same format as binary COPY values. Are you suggesting those should be changed too? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] WARNING: buffer refcount leak
Brian Hirt [EMAIL PROTECTED] writes: I'm working on a new machine, and i think it's got possible bad hardware, since that seems more likely than a bug in postgresql. I'm wondering if someone has any idea what kind of hardware failure might cause this message: WARNING: buffer refcount leak: [424] (freeNext=425, freePrev=423, rel=0/0, blockNum=4294967295, flags=0x1c, refcount=-631 30464) My money is on bad RAM. That refcount is ridiculous, and I can't see any way for a disk problem to cause that. (Unless this shared-buffer header got swapped out and back in, which seems unlikely considering we use the shared buffer headers a lot.) Seems like it's got to be bad RAM, bad CPU, or some part directly between them --- and you already replaced all those parts. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.5 backend crash
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | Greg Stark [EMAIL PROTECTED] writes: | |Gaetano Mendola [EMAIL PROTECTED] writes: | |kalman=# insert into test values (1 ,(2,(3,(4,(5,(6, null)); |INSERT 33639 1 |kalman=# select * from test; |~ a | b |---+ |~ 1 | (2,(3,(4,(5,(6,) |(1 row) | | |Omg. | | |This is pretty cool and frankly amazing that it works. | | | It shouldn't work --- the ALTER TABLE code should reject it. Looks like | there are a few holes to be plugged here ... The SQL standard forbid it ? I was believing that was a pretty cool feature! :-) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBBYB47UpzwH2SGd4RAoyfAJ9B5AIHC8A8nyWLOt+Xw6GXJa3kFACgxkzu kBxnvugWhzjNvc+UboAwAjE= =xEBD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Unexpected subquery behaviour
Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. test= SELECT version(); version - PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) test= CREATE TABLE foo (id INT PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE test= CREATE TABLE bar (bar_id INT, foo_id INT REFERENCES foo(id)); CREATE TABLE test= INSERT into foo values(1); INSERT 7493530 1 test= INSERT into foo values(2); INSERT 7493531 1 test= INSERT into bar values(2,1); INSERT 7493532 1 test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 2 (2 rows) test= EXPLAIN SELECT * FROM foo WHERE id IN (SELECT id FROM bar); QUERY PLAN --- Seq Scan on foo (cost=0.00..2.04 rows=1 width=4) Filter: (subplan) SubPlan - Seq Scan on bar (cost=0.00..1.01 rows=1 width=0) (4 rows) test= SELECT id FROM bar; ERROR: column id does not exist test= SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar); ERROR: column bar.id does not exist test= ALTER TABLE bar RENAME foo_id TO id; ALTER TABLE test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 (1 row) Is this known behaviour, and is there a rationale behind it? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unexpected subquery behaviour
On Tue, 27 Jul 2004, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. Unfortunately, as far as we can tell, the spec allows subselects to contain references to outer columns and that those can be done without explicitly referencing the outer table. As such, the above is effectively equivalent to SELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar) in the case where foo has an id column and bar does not. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] WARNING: buffer refcount leak
On Mon, 26 Jul 2004, Brian Hirt wrote: I'm working on a new machine, and i think it's got possible bad hardware, since that seems more likely than a bug in postgresql. I'm wondering if someone has any idea what kind of hardware failure might cause this message: WARNING: buffer refcount leak: [424] (freeNext=425, freePrev=423, rel=0/0, blockNum=4294967295, flags=0x1c, refcount=-631 30464) The refcount number strongly suggests hardware. Since the memory is ECC i'd say it might be CPU (cache) related. I cannot think of any tools to help you diagnose this, but try disabling/pulling out a CPU then swap. If all is fine, it doesn't necessarily mean its the CPU but it narrows the field. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Unexpected subquery behaviour
On Tuesday 27 July 2004 01:15, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. [snip] test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 2 (2 rows) This, however, does not work: andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b); ERROR: column b.id does not exist -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| - a tool should do one job, and do it well. | Hoffsveien 17 | | PO. Box 425 Skøyen | | 0213 Oslo | | NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgp69RNqmy7ba.pgp Description: PGP signature
Re: [HACKERS] Unexpected subquery behaviour
On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: On Tuesday 27 July 2004 01:15, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. [snip] test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar); id 1 2 (2 rows) This, however, does not work: andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b); ERROR: column b.id does not exist yes, I had that further down in the original example: test= SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar); ERROR: column bar.id does not exist Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Unexpected subquery behaviour
On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 27 Jul 2004, Ian Barwick wrote: Apologies if this has been covered previously. Given a statement like this: SELECT * FROM foo WHERE id IN (SELECT id FROM bar) I would expect it to fail if bar does not have a column id. The test case below (tested in 7.4.3 and 7.4.1) shows this statement will however appear succeed, but produce a cartesian join (?) if bar contains a foreign key referencing foo.id. The foreign key is not relevant, I just realized. Unfortunately, as far as we can tell, the spec allows subselects to contain references to outer columns and that those can be done without explicitly referencing the outer table. As such, the above is effectively equivalent to SELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar) in the case where foo has an id column and bar does not. Aha, interesting to know, though it looks somewhat odd. The reason I came up with this is because I was referencing the wrong column, which happened to exist in the outer table, which was producing unexpected results. Thanks Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.5 backend crash
Gaetano Mendola [EMAIL PROTECTED] writes: Tom Lane wrote: | It shouldn't work --- the ALTER TABLE code should reject it. Looks like | there are a few holes to be plugged here ... The SQL standard forbid it ? I was believing that was a pretty cool feature! :-) I don't think we can support it sanely ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Triggers on TRUNCATE?
On E, 2004-07-26 at 17:59, Tom Lane wrote: Daniel Baldoni [EMAIL PROTECTED] writes: Forgive this if it's totally out there but I was wondering why there's no support for triggers on a TRUNCATE operation. Because the entire point of TRUNCATE is not to scan the table contents. Maybe he was after statement level triggers ? We don't support them, and one also can't define a RULE for TRUNCATE - so the advice would be to use TRUNCATE very sparingly ;) Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a superuser to TRUNCATE ? --- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Improvements to PostgreSQL
Patrick Welche wrote: snip Is there more to remote control than setting GUC variables? Tell me more! Sure: + starting/restarting/stopping the database server software + the normal DBA type work - creating/altering/dropping databases, users, functions, languages, permissions (etc) + Remote backup and recovery + Submitting jobs to run remotely on the server. i.e. reindexing or vacuuming scripts Remote Monitoring: + Alerts for specified events. i.e. The database server is getting near to capacity in it's filesystem(s), or there have been too many invalid PG authorisation attempts, or there are connections getting rejected because the max_connections parameter isn't high enough Groups + Defining arbitrary groups of servers for the above to speed things up when working with many servers Roles + Having multiple administrators with different permissions (role based is generally good), all communicating through the centralised info repository so things don't get out of sync (possibly) + loading additional PG packages. i.e. rolling out oid2name or pgbench (or other PG utils) to servers. Could be viewed as something that should be done with the OS packaging mechanism(s) instead. Any of the PG GUI's (I generally use pgAdmin) could likely be extended to do all of this in a nice, user friendly way. As an aside, SNMP is important in enterprise settings as it allows PG to be plugged into the monitoring capabilities of enterprise management frameworks. i.e. Concord's eHealth, and probably Tivoli, OpenView, etc Hope that's useful. Regards and best wishes, Justin Clift Cheers, Patrick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cvsweb upgraded
On Mon, Jul 26, 2004 at 01:00:43PM -0300, Marc G. Fournier wrote: just upgraded to 3.0.1 ... it has a neat 'graphing' function: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/COPYRIGHT?graph=1 but, I seem to have lost the colors the old one had in the process :( Anyone used/configured cvsweb before? I can't seem to find an option to enable the colors, but there are *alot* of options in the cvsweb.conf file as it is :( Looks good ... I wonder if it can be configured to show tabs as 4 spaces, to match Postgres current practice? Also, as Tom already pointed out, the $PostgreSQL$ header is not honored. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Saca el libro que tu religión considere como el indicado para encontrar la oración que traiga paz a tu alma. Luego rebootea el computador y ve si funciona (Carlos Duclós) ---(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] Binary Cursors, and the COPY command
[EMAIL PROTECTED] writes: Lastly, the vast majority of machines in use today are intel. Meaning that they are small endian. Except in a very rare circumstance, two machines that would normally be able to communicate in native byte order, will ALWAYS have to convert data. Quite honestly, that is exactly one of the reasons for using network order. If we standardized on little-endian then 90% of programmers would ignore the need to do anything about byte ordering issues, and their code would be unportable to big-endian machines. So what you are saying is that you should inconvenience 90% of your users to make sure they do something right? When you *really* think about it, by forcing a big endian byte order over machine dependent byte order, you are inconveniencing 90% of the users, but only helping the very small amount of people who run in mixed environments where the server is intel and the client is big endian. Putting a byte order flag into the header doesn't improve matters; it just forces everybody to deal with *both* orders, which is not simpler. (Except for those who would like not to be bothered with portability, which is a position I have zero sympathy for.) While I understand your lack of sympathy, I think you are too removed from the trenches. There is a viable argument in many projects for machine dependent constructs. Sometimes there are real-world performance and delivery goals with absolutely stated objectives that the project is not heterogenus. If you don't want to deal with this, don't use binary data. It's as simple as that. That's not a good answer either. Come on, what the hell good is having a BINARY if the data is not usable? Using ascii isn't good either because you need a text parser for every possible interpretation of the various simple types. BTW, for your stated use-case of combining binary output from separate databases, I should think you'd be happy about having a not-so-machine- dependent data format. It would still work if the databases were running on servers of different endianness. Few deployments will *ever* really have different endian-ness amongst their servers. 99% of all deployments will be the same or compatible hardware, and probably intel at that. OK, so you like the idea of binary being big endian, what about the idea of adding a keyword that is exclusive of binary called native or something like that? DECLARE fubar NATIVE CURSOR ... COPY fubar {FROM | TO} {'filename' | STD..} WITH [NATIVE | BINARY] ... ---(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] cvsweb upgraded
Looks good ... I wonder if it can be configured to show tabs as 4 spaces, to match Postgres current practice? Also, as Tom already pointed out, the $PostgreSQL$ header is not honored. I bet you the $PostgreSQL$ header can be configured into it -- it seems to accommodate $FreeBSD$ and other custom headers at other places. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Triggers on TRUNCATE?
Hannu Krosing [EMAIL PROTECTED] writes: Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a superuser to TRUNCATE ? You have to own the table (or be superuser of course). This implies the right to drop its triggers, so TRUNCATE could be seen as dropping triggers, DELETE, recreate triggers. 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] Triggers on TRUNCATE?
G'day folks, Tom Lane wrote: Daniel Baldoni [EMAIL PROTECTED] writes: Forgive this if it's totally out there but I was wondering why there's no support for triggers on a TRUNCATE operation. Because the entire point of TRUNCATE is not to scan the table contents. If you want to fire triggers, just use DELETE FROM foo. Personally, I've never used TRUNCATE within a production system (I can't even recall using an unqualified DELETE). How do you then audit a TRUNCATE performed by somebody else (who, for political reasons, has superuser access)? Such actions aren't limited to attacks - but may simply be the result of I thought it was a good idea at the time. :-( Ciao. -- ---+- Daniel Baldoni BAppSc, PGradDipCompSci | Technical Director require 'std/disclaimer.pl'| LcdS Pty. Ltd. ---+ 856B Canning Hwy Phone/FAX: +61-8-9364-8171| Applecross Mobile: 041-888-9794 | WA 6153 URL:http://www.lcds.com.au/| Australia ---+- Any time there's something so ridiculous that no rational systems programmer would even consider trying it, they send for me.; paraphrased from King Of The Murgos by David Eddings. (I'm not good, just crazy) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cvsweb upgraded
On Mon, 26 Jul 2004, Alvaro Herrera wrote: On Mon, Jul 26, 2004 at 01:00:43PM -0300, Marc G. Fournier wrote: just upgraded to 3.0.1 ... it has a neat 'graphing' function: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/COPYRIGHT?graph=1 but, I seem to have lost the colors the old one had in the process :( Anyone used/configured cvsweb before? I can't seem to find an option to enable the colors, but there are *alot* of options in the cvsweb.conf file as it is :( Looks good ... I wonder if it can be configured to show tabs as 4 spaces, to match Postgres current practice? Should already be set: # Tabstop used to expand tabs in colored diffs. If undefined then # tabs are always expanded to 8 spaces. # $tabstop = 4; Also, as Tom already pointed out, the $PostgreSQL$ header is not honored. 'k, if someone knows how to fix this, I'm all ears ... the OS conf file examples all have the following set (with PostgreSQL switched for the OS): $ENV{'RCSLOCALID'} = 'PostgreSQL=Id'; Since I'm not sure example where in cvsweb you guys are looking, can you test and let me know if this fixes it? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Triggers on TRUNCATE?
How do you then audit a TRUNCATE performed by somebody else (who, for political reasons, has superuser access)? Such actions aren't limited to attacks - but may simply be the result of I thought it was a good idea at the time. :-( Easily enough, have the logs record the pid, connection startup, timestamps, statement,etc. That should give everything required to track down a unique user who performed random actions. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cvsweb upgraded
On Tue, 27 Jul 2004, Marc G. Fournier wrote: 'k, if someone knows how to fix this, I'm all ears ... the OS conf file examples all have the following set (with PostgreSQL switched for the OS): $ENV{'RCSLOCALID'} = 'PostgreSQL=Id'; Since I'm not sure example where in cvsweb you guys are looking, can you test and let me know if this fixes it? Nope, that doesn't appear to do it ... I changed it to what is in CVSROOT/options, and it causes cvsweb to generate an error about rlog ... getting rid of the line reverts it back to the form that works ... We're running cvsweb 3.0.1, if anyone wants to see what is settable ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cvsweb upgraded
On Tue, Jul 27, 2004 at 12:42:51AM -0300, Marc G. Fournier wrote: # Tabstop used to expand tabs in colored diffs. If undefined then # tabs are always expanded to 8 spaces. # $tabstop = 4; Apparently the plain files (as opposed to diffs) do not honour this setting. At least I still see one tab char (not eight, nor four, spaces) for example here: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/access/transam/subtrans.c?rev=1.1 Also, as Tom already pointed out, the $PostgreSQL$ header is not honored. 'k, if someone knows how to fix this, I'm all ears ... the OS conf file examples all have the following set (with PostgreSQL switched for the OS): $ENV{'RCSLOCALID'} = 'PostgreSQL=Id'; Since I'm not sure example where in cvsweb you guys are looking, can you test and let me know if this fixes it? Dunno ... appears to be good in the files, because I see the correct version number here: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/access/transam/clog.c?rev=1.22 However, note that this one displays 1.21 and 1.20 in the text, while I am requesting diff between 1.22 and 1.21 (and indeed the differences are 1.22 vs 1.21) http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/access/transam/clog.c.diff?r1=texttr1=1.22r2=texttr2=1.21 -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) There was no reply (Kernel Traffic) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]