[HACKERS] Batch Operations
hi, im currently working on a project that requires batch operations - eg. Batch insert/update etc. The database im using is PostgreSQL. However, i cannot find any documentation for batch opeartions on PostgreSQL. Does anyone know how to do this, or if it is possible? Thanx in advance regards rahul ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oversight?
On Mon, 2003-08-11 at 19:16, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: rbt=# ALTER USER rbt SET CONSTRAINTS ALL DEFERRED; ERROR: syntax error at or near ALL at character 32 rbt=# ALTER USER rbt SET CONSTRAINTS = DEFERRED; ERROR: constraints is not a recognized option SET CONSTRAINTS ALL DEFERRED is a SQL-spec-mandated command syntax. Any similarity to Postgres' SET var = value syntax ends with the initial keyword. Yes, but we don't support 'SET constraints = deferred' either. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Oversight?
rbt=3D# ALTER USER rbt SET CONSTRAINTS ALL DEFERRED; ERROR: syntax error at or near ALL at character 32 rbt=3D# ALTER USER rbt SET CONSTRAINTS =3D DEFERRED; ERROR: constraints is not a recognized option SET CONSTRAINTS ALL DEFERRED is a SQL-spec-mandated command syntax. Any similarity to Postgres' SET var = value syntax ends with the initial keyword. I assume his point is how do we set all of a user's constraints deferred by default? 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] sql99 compat list
Tom Lane wrote: Shachar Shemesh [EMAIL PROTECTED] writes: Also, I think a pretty simple workaround would be to have PSQL search for the upper case identifier, and if not found, search for the lower case. This should allow a migration path while the tools and functions are being translated (and should, perhaps, stay around forever as a session option for old databases) You're assuming that we plan to change this. We don't. Most of us prefer to look at lower-case identifiers. regards, tom lane While I cannot argue with personal preferences, of course, I will try to point out two things: 1. It's an SQL incompatibility, and therefor must be documented (at the very least). 2. It is causing pains when performing migrations from other databases At the very least, a session option that will allow me to search for upper case if a lowercase identifier was not found would be apretiated, optionally with a warning (so I can track down the places in the migrated application that are still misbehaving, and fix them). Shachar -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(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] Change Request: \pset pager off in pg_dumpall
Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Are you manually starting psql, then doing \i dumpfile? Why would you do that rather than psql template1 dumpfile? -- 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] problem with RH7.3 Pg7.3.4 binaries
Mark Cave-Ayland said: Hi there, I have a RedHat 7.3 machine that can build the 7.3.4 RPMs if required - it only contains RPMs from the vanilla CD or from updates.redhat.com. I've just done a test build and everything seems OK except that the C compiler is passed the -mcpu=i686 flag - I'm guessing I need to somehow change this to i386 so it will binaries will run on actual i386 machines? Can someone point me in the right direction? The -mcpu flag doesn't do what you seems to think it does. It still generates i386 compatible code, but favours i686 processor timings etc. Cheers, Mark. Magnus ---(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] RE : Oracle to PostgreSQL
What are the legal implications of copying Oracle's own PL/SQL procedures code and porting them to PgSQL as you suggested? Bruno BAGUETTE wrote: Marco Roda [EMAIL PROTECTED] wrote .. Hi, I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have scripts to create the base on Oracle and PostgreSQL (it is about 40 tables), but I need to port a great amount of data to PostgreSQL. Can anybody help me? I never see any documents about migrating from Oracle8 to PostgreSQL, but I've already done this kind of migration, here's my warrior's way : - Make a SQL dump of the tables (structure + constraints + sequences + data), and try to add this dump file into a PostgreSQL database (via psql). You will probably need to do several search and replaces in the SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try to follow the SQL norm, it shouldn't be very difficult. - The most difficult step is to migrate the stored procedures. The biggest work is to put PL/SQL packages into PL/PGSQL functions (Unfortunately, PostgreSQL don't manages PL/PGSQL packages, that's still in the TODO list). So your packages functions contacts.add() (for example) will have to be renamed to contacts_add(); and you will also have to patch all the clients that have to access this database. This migration may be quite long, so leave the Oracle database running. Keep a note of all the changes you've done to have your SQL dump valid for PostgreSQL, and make a script (in Perl, PHP, or shell,...). When everything is OK, stop writes to the Oracle DB (allows only select queries), do the SQL dump (with the latests datas), execute your script on the sql DUMP, do the migration and switch the users to the new database. (Be sure to have the new clients ready before doing the big jump). The job of migrating from Oracle to PostgreSQL is a serious job (and sometimes difficult) but the migration wins are really interesting. I Hope this will help and that you will understand my english. :-) Regards, ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Here are the changes for 7.4. I am looking for any improvements. This will be adjusted as we move through beta. Some notes on specific items: Update /tmp socket files regularly to avoid their removal (Tom) More specifically, update the file mod times. Updated: Update /tmp socket mod. times regularly to avoid their removal (Tom) New client/server protocol: faster, no username length limit, allow clean exit allow clean exit from failed COPY is what's meant I think. Not sure I'd claim faster, either. There are a lot of other features you could mention instead, such as allowing separation between SQL commands and data values. New text is: New client/server protocol: faster, no username length limit, allow clean exit from COPY (Tom) How would you explain allowing separation between SQL commands and data values? Cleaner prepared statement implementation? Improve reverse index scan performance (Tom) I have no idea what this entry is talking about --- I could not find anything in the CVS log that seemed to match. I know I saw it, but after looking for 20 minutes, I can't find it. Removed. postgres --help-config now dumps server config variables (Tom) This was not my work, I just applied it. Please credit Aizaz Ahmed. Updated. Prevent assign_session_authorization() from being confused by all-numeric user names (Tom) This one is already in 7.3 patch releases, should not appear here. Removed. Allow ORDER BY in FROM subqueries to be honored by outer queries (Tom) honored by? Better wording: Avoid redundant sort when order of a subquery result matches outer query's needs Updated to: Avoid sort when subquery ORDER BY matches upper query (Tom) Also, this belongs under Performance, I think. Moved. Print key name in foreign-key violation messages (Dmitry Tkach) Print key name and value ... Updated. Allow subquery aggregates to reference upper query columns (?) (Tom) Poorly phrased. How about: Execute aggregates that reference outer-query values correctly per SQL spec Updated: Fix subquery aggregates of upper query columns to match SQL spec. (Tom) Allow dollar signs in identifiers, except as first character (Tom) Why is this not together with the mention that dollar signs are *not* allowed anymore in operator names? It might also be worth pointing out that x=$1 works without spaces now. Updated to: Disallow dollar signs in operator names, so x=$1 works (Tom) They are in different places because one relates to operator names (Object Manipulation section) while the other relates to queries (Queries section). Should they be moved into the same section? Allow zero-column tables (Tom) We allowed this already in 7.3, though I may have fixed a few more bugs. Updated to: Fix several zero-column table bugs (Tom) Add pg_trigger.tgenabled to disable triggers? (Neil) tgenabled was there already; the patch just caused it to be checked in more places. I am removing the item --- it doesn't seem ready for publication, or we would have added a utility to access it. Have parser honor foreign-key constraints if created via ALTER TABLE ADD COLUMN? (Tom) This was fixed in a 7.3.* release, should not be listed here. Removed. Improve DOMAIN automatic type casting (Tom) I think Rod had something to do with that, too. Added Rod. Object owners can allow grantees to grant privilege to others? Better Implement GRANT OPTION privileges, per SQL spec (Peter) Updated: Add WITH GRANT OPTION clause to GRANT, per SQL spec (Peter) Properly handle SCROLL with cursors, or report an error (Tom) Someone else (Neil I think) did the bulk of the work for scrollable cursors. Right, Neil. Updated. FYI, he is back and will be online in a few more days. Allow CLUSTER without tablename clusters all tables (Alvaro Herrera) Better worded Allow ... to cluster all tables Updated. Reduce memory used by COPY (Tom) Better Prevent possible memory leaks in COPY. I don't think that patch actually reduced normal memory consumption at all. Updated. Syntax errors now reported as 'syntax error' rather than 'parse error' (Tom) Should credit the bison guys ;-) ... that was their change not ours. But really I see no need to list it at all --- there are vastly more wording changes in the error messages than just this one. Yes, but it seemed one most likely to be checked by applications, no? Have COMMENT ON DATABASE on non-local database generate a warning (Tom) I think that was someone else's work ... Rod maybe? Name removed. Anyone know? New hostmast() function (Greg Wickham) hostmask() I think you meant? Right, updated. Allow polymorphic SQL functions (Tom, Joe) I think Joe should get the bulk of the credit on this one. Updated. Allow array concatenation with '||'
Re: [HACKERS] dropping a user causes pain (#2)
If you can suggest a plausible way that DROP USER is going to change the contents of other databases (which might well contain things owned by the target user), this might get onto the TODO list --- although I'd personally prefer RESTRICT/CASCADE options. So far, since no one has the foggiest idea how to implement cross-database removal, it's just been left as-is. Ya ya. I had forgotten that aspect. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] message at end of install
On 12 Aug 2003 at 9:58, Robert Treat wrote: IIRC the message at the end of install used to echo out the startup command (pg_ctl or postmaster), but now it gives some nice information on how to get help. Should the startup message be put back in? Seems like it is the most likely thing someone who just installed would want to know. IIRC initdb is the one which shows pg_ctl database usage. Why would install show up pg_ctl usage? There might be quite a bit of time between install and initdb. This happens with initdb too? Bye Shridhar -- Weinberg's First Law: Progress is only made on alternate Fridays. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Farewell
It has been a big pleasure to me to get to know you, and a big honor to work with you. Do swidanie i bolshoi sbaseebo, Vadim. Jan --- Vadim Mikheev [EMAIL PROTECTED] wrote: FarewellIt's time for formal acknowledgement that I'm not in The Project any more. I'm not interested in small features/fixes and have no time for big ones. It was this way for very long time and I don't see how/when that could change. My participation in The Project was one of the greatest adventures in my life. Thanks to everyone! Good luck on your ways. And - long live to Postgres!!! Vadim __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgstats_initstats() cost
On Tue, 12 Aug 2003, Rod Taylor wrote: world. It just seemed interesting that the numbers were three times slower than other databases I ran it on. Here is the script which generates the You were comparing against databases with similar safety nets to guarantee against dataloss? I am in the process of reading through the logging/versioning code of them and the others definately do. My main interest is in determining how to reduce the cost of pgstats_initstats(). Thanks, Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] TODO items
Joe, They are done (at least the array declarations and array element assignment part): Way cool! How'd I miss that one? Time to test o Add PL/PgSQL PROCEDURES that can return multiple values Hmmm ... I know how this got on the TODO, but it's a fragment of a larger suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much sense on its own without the other elements; maybe we should take it off until I can make a full proposal? Is this somehow different from table functions (SRFs)? Yes. Reference T-SQL's OUTPUT parameters. Mind you, with the implementation of SRFs, it's not as necessary as it once was. Pretty much sure this has not been done. I'll be happy to work with someone if they want to pick this up, but I don't use them enough to feel comfortable doing it myself. I'd be happy to test PL/Perl. I won't be any help with the others ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TODO: trigger features
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: I wonder why you are suggesting workarounds for features that other databases provide. The fact that other databases provide 'em doesn't make them good ideas. In particular, writing a trigger that assumes that only the fields changed by the original UPDATE syntax have really changed seems like an excellent way to shoot yourself in the foot. Why should we go out of our way to provide support for error-prone programming techniques? So you want to make a system fool-proof by not providing features? Working with nested triggers is certainly nothing to be considered for the newbie. It's very easy to find these fields, because they can be identified from old.field new.field. My concern is about fields that can *not* be identified by this comparision. This needs special handling, just as NULL is handled in a special way (you wouldn't like a suggestion to handle NULL as zero or empty string, and have an additional bool column to designate the empty state, would you?!?) Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] reuse sysids security hole?
I like the sequence generator idea too. I know Unix is bad in this area - but that's no reason for us to be bad too. This is actually one of the (few) areas where Windows is better than Unix. Let's go for best practice. (new todo item Prevent automatic reuse of sysids ?) andrew Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: On Tue, 12 Aug 2003, Andrew Dunstan wrote: Is this a security hole? Looks like one to me. Would it be better to use a sequence generator for sysids instead of using max+1 on the user table? Or else store the last sysid used somewhere? This issue has been discussed before and it was agreed that since most UNIX systems will behave in the same way, there's no way to know. Also, it is not possible for a given database to know the max(sysid) of pg_user in another database. You forget that pg_shadow is a shared (cluster-wide) table. I believe we could make a shared sequence object, too, if we wanted to go the sequence route. Right at the moment I like both ideas: a shared sequence to generate new sysids, and don't ever delete pg_shadow rows. One attraction of the sequence generator is that scans over pg_shadow could get rather tedious if we follow the latter policy. But with a sequence, CREATE USER wouldn't need to do a scan. Something else that should be factored into any redesign of pg_shadow is the notion of combining users and groups, at least to the extent of having a common sysid space for both. See discussion started by Peter a month or two back (I think thread title mentioned roles). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] message at end of install
Robert Treat [EMAIL PROTECTED] writes: IIRC the message at the end of install used to echo out the startup command (pg_ctl or postmaster), but now it gives some nice information on how to get help. Should the startup message be put back in? Nothing's been removed AFAIK. Sure you're not confusing make install with initdb? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] ALTER TABLE table RENAME COLUMN x TO y
Tom Lane wrote: Donald Fraser [EMAIL PROTECTED] writes: When issuing the following type of command: ALTER TABLE table RENAME COLUMN x TO y The column name change is not cascading through to RULEs on a VIEW. More specifically, INSERTs and UPDATEs contained in rules don't have their target column names adjusted. This is because the resname fields in their targetlists contain the original column names, and those fields are actually looked at to determine the target columns. I think this behavior is vestigial, and we could both simplify the code and make it RENAME-proof by using just the resno fields to determine the target columns. resname would then have just one purpose: to carry the AS alias of targetlist entries in SELECTs. There is already code in ruleutils.c to allow resname to be overridden by the current column name of a view (thus handling RENAME applied to the view itself), and I don't think resname is user-visible in any other way. Anyone see a problem with this plan? I regard this as something we should fix for 7.4, mainly because if you Oh, man, you are reaching with that one, but I like it. :-) use --enable-cassert then the backend actually dumps core when trying to execute the outdated rule (there are Asserts in there that notice the resname mismatch). -- 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
