Re: [HACKERS] Script to compute random page cost
On Tue, 10 Sep 2002, Bruce Momjian wrote: Interesting that random time is increasing, while the others were stable. I think this may have to do with other system activity at the time of the test. Actually, the random versus sequential time may also be different depending on how many processes are competing for disk access, as well. If the OS isn't maintaining readahead for whatever reason, sequential access could, in theory, degrade to being the same speed as random access. It might be interesting to test this, too. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Here are the proposals for solutioning the Return proper effected tuple count from complex commands [return] issue as seen on TODO. Any comments ?... This is obviously open to voting and discussion. We don't have a whole lot of freedom in this; this area is covered by the SQL standard. The major premise in the standard's point of view is that views are supposed to be transparent. That is, if SELECT * FROM my_view WHERE condition; return N rows, then a subsequently executed UPDATE my_view SET ... WHERE condition; returns an update count of N, no matter what happens behind the scenes. I don't think this matches Tom Lane's view exactly, but it's a lot closer than your proposal. Yes, exactly. I think it does match Tom's proposal as best we can. But we need a knowing dba that creates correct rules. Since you can create a lot more powerful views in pg than usual, I guess that is not such a farfetched demand. I do not know whether above extends to inserts ? In Informix you can create views WITH CHECK OPTION, then inserted and updated rows are guaranteed to still be visible by the view. If you don't add that clause, inserts and updates may produce rows that are not visible through the view. The affected row count still includes those though. Andreas ---(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] Rule updates and PQcmdstatus() issue
What is the difference between a trigger, a rule and an instead rule from a business process oriented point of view? I think there is none at all. They are just different techniques to do one and the same, implement business logic in the database system. The difference is how other db's work. They all ignore triggers and constraints in the sqlca.sqlerrd[2] number of processed rows count, that I see identical to our affected rows count. They all have views, but not many have rules :-) Pg's instead rules are the toolkit for views, and as such need special handling, imho. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PREPARE code notes
On Mon, Sep 09, 2002 at 11:51:08AM -0400, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: 1/ ExecuteQuery() (line 110). Why is needful use copyObject()? The PostgreSQL executor modify query planns? Yes, and yes. Unfortunately. Hmm, it's bad. Is there any way to fix executor? Maybe in far future we will save to cache all planns and copyObject() is not performance winning. 2/ Lines 236 -- 245. Why do you check for pre-existing entry of same name if you create hash table? I think better is use else for this block of code and check it only if hash table already exist. The code reads more cleanly as-is; changing it as you suggest would create an unnecessary interdependency between two logically distinct concerns. I don't believe :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Solving the Return proper effected tuple
Oh, this is bad news. The problem we have is that rules don't distinguish the UPDATE on the underlying tables of the rule from other updates that may appear in the query. If we go with Tom's idea and total just UPDATE's, we will get the right answer when there is only one UPDATE in the ruleset. As long as the rules don't overlap (1 row is handled by 1 instead statement, another row by a different one), it is ok. Again, you can create non instead rules or triggers for the other work needed. I am still in favor of not distinguishing the different tags. The dba needs to take responsibility anyway (as long as we don't autogenerate the rules for simple cases). Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Rule updates and PQcmdstatus() issue
On Mon, 2002-09-09 at 21:25, Ross J. Reedstrom wrote: And this has got to be trolling: PostgreSQL is one of the _most_ stability and correctness focused software projects I've ever known. In this particular case, the complaints about this issue where Your bugfix broke my tool! make it better! The answer was We can't just put it back, that's an actual bug in there (rules firing in an unpredicatable order). Why is rules firing in an unpredicatable order a bug but returned affected tuple count is wrong just a compatibility issue ? Afaik, rule firing order has never been promised, while pqCmdTuples() has. What's the _correct_ behavior? The people with the complaints then did not come up with a compelling, complete description of what the correct behavior should be. There's always been vague parts to the desired behavior like the phrase Tom pointed out: in the context of the view which was clarified to mean viewable by the view, which is nearly impossible to code, if not an example of the halting problem. One approach could be to expose the tuple count at SQL level and then let the user decide what to return. PostgreSQL as a project errs on the side of not coding the quick fix, in favor of waiting for the right answer. Sometimes too long, but this case isn't one of those, IMHO. You usually learn afterwards when it has been too long ;) -- Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
-Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED]] Sent: 10 September 2002 05:58 To: Sean Chittenden Cc: Tom Lane; [EMAIL PROTECTED] Subject: Re: [HACKERS] Optimization levels when compiling PostgreSQL... Sean Chittenden [EMAIL PROTECTED] writes: Has there been any talk of doing incremental -snapshots of the code base? I don't really see the point. Snapshots of development code are available from CVS anyway -- and if you're going to be running a pre-alpha version of a relational database, I don't think that knowledge of CVS is an onerous requirement. At any rate, the problem with releasing snapshots is that the system catalogs would change so often that upgrading between snapshots would be a headache. i.e. the changes required to upgrade from a 2 week old development snapshot to a current snapshot would still be non-trivial, significantly reducing the usefulness of snapshots, IMHO. Snapshots can be found here: ftp://ftp.postgresql.org/pub/dev/ Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Script to compute random page cost
OK, I have a better version at: The script is now broken, I get: Collecting sizing information ... Running random access timing test ... Running sequential access timing test ... Running null loop timing test ... random test: 14 sequential test: 16 null timing test: 14 random_page_cost = 0.00 ---(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] Location of database files?
Is there any way to determine the location of files in a database without being the postgres user? Essentially i'm after the setting of PGDATA so i can then show disk status (df) for that partition. The pg_database catalogue has 'datpath': If the database is stored at an alternative location then this records the location. It's either an environment variable name or an absolute path, depending how it was entered. so I'm really looking for the default location... I could knock together a C function to do this (and indeed another to return the usage stats too), but would like to check first there's no simple way already! Regards, Lee Kindness. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Script to compute random page cost
I was attempting to measure random page cost a while ago - I used three programs in this archive : http://techdocs.postgresql.org/markir/download/benchtool/ It writes a single big file and seems to give more realistic measurements ( like 6 for a Solaris scsi system and 10 for a Linux ide one...) Have a look and see if you can cannibalize it for your program Cheers Mark ---(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] Script to compute random page cost
On Mon, 2002-09-09 at 07:13, Bruce Momjian wrote: OK, turns out that the loop for sequential scan ran fewer times and was skewing the numbers. I have a new version at: ftp://candle.pha.pa.us/pub/postgresql/randcost Latest version: olly@linda$ random test: 14 sequential test: 11 null timing test: 9 random_page_cost = 2.50 olly@linda$ for a in 1 2 3 4 5 do ~/randcost done Collecting sizing information ... random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 10 sequential test: 10 null timing test: 10 Sequential time equals null time. Increase TESTCYCLES and rerun. Available memory (512M) exceeds the total database size, so sequential and random are almost the same for the second and subsequent runs. Since, in production, I would hope to have all active tables permanently in RAM, would there be a case for my using a page cost of 1 on the assumption that no disk reads would be needed? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Tue, 2002-09-10 at 00:50, Philip Warner wrote: ALTERNATIVELY, define the language in template1, then just edit dump1.lis to remove the line for the language definition, and run pg_restore -L dump1.lis. That doesn't work for a dump and reload, because 7.3's pg_dumpall writes a script to create the databases from template0 rather than template1. The 7.3 documentation for pg_dump says: Notes If your installation has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example: CREATE DATABASE foo WITH TEMPLATE = template0; but this seems to be out of date. pg_dumpall actually uses template0 itself. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] IN FIRE
HELP!!! I'm stuck for strange reason! This is my first attempt to use pg_lo concept in my apps: ... Oid oid; PGconn* dbcon = PQconnectdb(conninfo.c_str()); oid = lo_creat(dbcon, INV_WRITE | INV_READ); int pgfd = lo_open(dbcon, oid, INV_WRITE | INV_READ); ... lo_open ALWAYS returns -1 while oid is positive (I can even see oid in pg_largeobject system table) postmaster reports the following: ERROR: lo_lseek: invalid large obj descriptor (0) I realy NEED a prompt advice! Please find a couple of minutes for reply! TIA Stanislav ps I run FreeBSD-4.4 + ported PostgreSQL-7.1.3 pps my other pg-connected apps run OK ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] If there a bug in the psql or just a feature .
Dear all,
I'm currently working on my thesis and I chose psql. What I need to do
is defining a new type in psql.
It should be dynamic array.
| 1 | 2 | 3.0 | 4.5 | 2.1 | . .. . .
// This one is not working
typedef struct Myindex {
double *indexes;
int level;
int size;
} Myindex
Myindex *
Myindex_in {
}
Myindex *
Myindex_out {
However when I try to get back the data. It seems that the last
insertion always overwrite other previous insertion.
In particular, it overwrites all data from 2nd to n-1th record.
where n is the number of insertion but not the first one.
}
// This one work ok but the idea is to have dynamic array.
// This would defeat the purpose of this new structure.
typedef struct Myindex {
double indexes[10];
int level;
int size;
} Myindex;
Standalone debuging works for both cases.
However psql accepts only the static array.
Could anybody enlight me on this issue, please
regards,
Van
---(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: [JDBC] [HACKERS] problem with new autocommit config parameter
Dear PostgreSQL people, Sorry for jumping into this conversation in the middle. Autocommit is very important, as appservers may turn it on or off at will in order to support EJB transactions (being able to set them up, roll them back, commit them, etc. by using the JDBC API). If it is broken, then all EJB apps using PostgreSQL may be broken also. ...This frightens me a little. Could somebody please explain? Sincerely, Daryl. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Monday, September 09, 2002 2:54 PM To: Bruce Momjian Cc: Barry Lind; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc Bruce Momjian [EMAIL PROTECTED] writes: Barry Lind wrote: How should client interfaces handle this new autocommit feature? Is it best to just issue a set at the beginning of the connection to ensure that it is always on? Yes, I thought that was the best fix for apps that can't deal with autocommit being off. If autocommit=off really seriously breaks JDBC then I don't think a simple SET command at the start of a session is going to do that much to improve robustness. What if the user issues another SET to turn it on? I'd suggest just documenting that it is broken and you can't use it, until such time as you can get it fixed. Band-aids that only partially cover the problem don't seem worth the effort to me. In general I think that autocommit=off is probably going to be very poorly supported in the 7.3 release. We can document it as being work in progress, use at your own risk. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Tuesday 10 September 2002 04:16 am, Stephan Szabo wrote: On Tue, 10 Sep 2002, snpe wrote: On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote: On Tue, 10 Sep 2002, snpe wrote: On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: On Mon, 2002-09-09 at 17:04, snpe wrote: I'm use 'autocommit=false' and have problem with psql When any commnad is lost, then next commnad get error for transactions (simple select command).BTW snpe select * from org_ba; ERROR: relation org_ba does not exists snpe select * from org_ban; ERROR: current transactions is aborted, queries ignored until end of transaction block snpe rollback; ROLLBACK snpe select * from org_ban; Maybe I'm missing something, but isn't that the expected behaviour when autocommit is turned off? I get this every time.When exists command with error next command don't work without explicit rollback and commit (this is not for psql, this error get in with JDeveloper - JDBC driver).When autocommit=ture all is fine It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Error command don't start transaction - nothing hapen, only typing error If you do an insert that violates a constraint, does that start an transaction or not? I think we have to choose before we start doing the statement not after. This is typeing error.Nothing happen.That is not transaction. I don't know that is possible, but before start transaction we need parsing command and select or any error don't start transaction This is problem for every client (I know for JDBC) regards Haris Peco ---(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] Rule updates and PQcmdstatus() issue
Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 9 Sep 2002, Bruce Momjian wrote: All the problems here are coming from INSTEAD rules. We don't have INSTEAD triggers or contraints. Sure we do, well sort of. :) Make a before trigger that does a different statement and returns NULL to abort the original action on that row. I think we can reasonably leave the side-effects of triggers out of the discussion. PQcmdStatus numbers have never included side-effects of triggers in the past, and I see no reason for them to start now. I think it's reasonable to exclude both triggers and non-INSTEAD rules from the status count, on the grounds that these normally represent add-on actions and not the real action. The cases that get interesting are those that involve multiple INSTEAD actions (either from multiple INSTEAD rules, or a single rule with multiple commands in its body) and those cases where the INSTEAD action is a different type from the original command (eg, ON UPDATE DO INSTEAD INSERT...). 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] problem with new autocommit config parameter and jdbc
Curt Sampson [EMAIL PROTECTED] writes: From Date's _A Guide to the SQL Standard_ (Fourth Edition): ... The following SQL statements are _not_ transaction-initiating: CONNECT SET CONNECTION DISCONNECT SET SESSION AUTHORIZATION SET CATALOG SET SCHEMA SET NAMES SET TIME ZONE SET TRANSACTION SET CONSTRAINTS COMMIT ROLLBACK GET DIAGNOSTICS Hm. This brings up a thought I've been turning over for the past couple days. As of CVS tip, SET commands *do* initiate transactions if you have autocommit off. By your reading of Date, this is not spec compliant for certain SET variables: a SET not already within a transaction should not start a transaction block, at least for the variables mentioned above. It occurs to me that it'd be reasonable to make it act that way for all SET variables. An example of how this would simplify life: consider the problem of a client that wants to ensure autocommit is on. A simple SET autocommit TO on; doesn't work at the moment: if autocommit is off, then you'll need to issue a COMMIT as well to get out of the implicitly started transaction. But you don't want to just issue a COMMIT, because you'll get a nasty ugly WARNING message on stderr if indeed autocommit was on already. The only warning-free way to issue a SET right now if you are uncertain about autocommit status is BEGIN; SET ; COMMIT; Blech. But if SET doesn't start a transaction then you can still just do SET. This avoids some changes we'll otherwise have to make in libpq startup, among other places. Does anyone see any cases where it's important for SET to start a transaction? (Of course, if you are already *in* a transaction, the SET will be part of that transaction. The question is whether we want SET to trigger an implicit BEGIN or not.) Nor, of course, are the nonexecutable statements DECLARE CURSOR, DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE SECTIONS, and WHENEVER. Hmm. I think the spec's notion of DECLARE must be different from ours. Our implementation of DECLARE CURSOR both declares and opens the cursor, and as such it *must* be transaction-initiating; else it's useless. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Script to compute random page cost
Curt Sampson [EMAIL PROTECTED] writes: Well, for the sequential reads, the readahead should be trigerred even when reading from a raw device. That strikes me as an unportable assumption. Even if true, we can't provide a test mechanism that requires root access to run it --- raw-device testing is out of the question just on that basis, never mind that it is not measuring what we want to measure. Perhaps it's time to remind people that what we want to measure is the performance seen by a C program issuing write() and read() commands, transferring 8K at a time, on a regular Unix filesystem. A shell script invoking dd is by definition going to see a very different performance ratio, even if what dd does under the hood is 8K read() and write() (another not-well-supported assumption, IMHO). If you try to improve the results by using a raw device, you're merely moving even further away from the scenario of interest. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
Neil Conway [EMAIL PROTECTED] writes: Sean Chittenden [EMAIL PROTECTED] writes: Has there been any talk of doing incremental -snapshots of the code base? I don't really see the point. Snapshots of development code are available from CVS anyway -- and if you're going to be running a pre-alpha version of a relational database, I don't think that knowledge of CVS is an onerous requirement. There's also the nightly automatic snapshot tarball on the FTP server, if you don't want to learn CVS... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] IN FIRE
I'm stuck for strange reason! This is my first attempt to use pg_lo concept in my apps: ... Oid oid; PGconn* dbcon = PQconnectdb(conninfo.c_str()); oid = lo_creat(dbcon, INV_WRITE | INV_READ); int pgfd = lo_open(dbcon, oid, INV_WRITE | INV_READ); ... lo_open ALWAYS returns -1 while oid is positive (I can even see oid in pg_largeobject system table) Use transactions (BEGIN; END;). Large objects rely on this ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Script to compute random page cost
Bruce Momjian [EMAIL PROTECTED] writes: I will run it some more tomorrow but clearly we are seeing reasonable numbers now. ... which still have no provable relationship to the ratio we need to measure. See my previous comments to Curt; I don't think you can possibly get trustworthy results out of a shell script + dd approach, because we do not implement Postgres using dd. If you implemented a C testbed and then proved by experiment that the shell script got comparable numbers, then I'd believe its results. Without that confirmation, these are just meaningless numbers. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PREPARE code notes
Karel Zak [EMAIL PROTECTED] writes: On Mon, Sep 09, 2002 at 11:51:08AM -0400, Tom Lane wrote: PostgreSQL executor modify query planns? Yes, and yes. Unfortunately. Hmm, it's bad. Is there any way to fix executor? It should be fixed IMHO ... but it'll be a major restructuring and it's difficult to justify spending the time ... 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] Rule updates and PQcmdstatus() issue
Hannu Krosing [EMAIL PROTECTED] writes: Why is rules firing in an unpredicatable order a bug but returned affected tuple count is wrong just a compatibility issue ? Afaik, rule firing order has never been promised, while pqCmdTuples() has. There has never been any spec saying exactly what PQcmdTuples would give in complicated cases. The effective behavior pre-7.2 was that you'd get the result tag of the last action executed, but this was undocumented, and unsafe to rely on in multi-rule cases even then, considering that the firing order of rules was not predictable. What actually happened was this: in 7.2 we changed ON INSERT rule firing to execute non-INSTEAD rules after the original INSERT, rather than before it. In the old behavior, non-INSTEAD rules just plain did not work with an INSERT: they wouldn't see any NEW row, because it wasn't there yet when they ran. This is surely a bug fix in my book (and it is unrelated to the 7.3 change that provides predictable firing order of multiple rules). Now the side effect of that change was to change PQcmdTuples' behavior, because the last action was no longer the same thing as before. This broke various clients that were depending on the last action to be the original INSERT. The fix we applied was to redefine PQcmdTuples to return the result count of the original query regardless of firing order. This behavior is evidently not good for Steve, and I'm perfectly prepared to discuss modifying it some more --- but I don't want to have a PQcmdTuples behavior-of-the-month with new changes in every release. I want a discussed, agreed-to, well-defined behavior that we aren't going to revisit again in future releases. When we have that agreement we can implement it and forget it ... but if we apply a bandaid now and then change the behavior again later, we're just going to make life even harder for clients. I'd rather leave the behavior broken (by Steve's view anyway) but *the same as 7.2* than have a new but still- unsatisfactory definition in there for 7.3. I think the other developers have the same negative opinion about API churn as I do, and so when we couldn't get agreement about what to do back in May, we shelved the topic in hopes a fresh idea would come along. Now could we drop the name-calling and the bogus opinionating about how serious or not-serious this problem is, and concentrate on finding a satisfactory answer? 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] If there a bug in the psql or just a feature .
Vanmunin Chea [EMAIL PROTECTED] writes:
// This one is not working
typedef struct Myindex {
double *indexes;
int level;
int size;
} Myindex
You cannot use a pointer inside a Postgres datatype. The system will
have no idea that the pointer is there and so will not copy the
pointed-to data, nor update the pointer, when the datum is copied,
stored on disk, etc.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] problem with new autocommit config parameter and jdbc
It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. Error command don't start transaction - nothing hapen, only typing error If you do an insert that violates a constraint, does that start an transaction or not? I think we have to choose before we start doing the statement not after. This is typeing error.Nothing happen.That is not transaction. I don't know that is possible, but before start transaction we need parsing command and select or any error don't start transaction Why not? AFAICT it should, the transaction is initiated a statement is run and it fails. Now maybe we shouldn't be going into the wierd disabled statement state, but that's a different argument entirely. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
Has there been any talk of doing incremental -snapshots of the code base? I don't really see the point. Snapshots of development code are available from CVS anyway -- and if you're going to be running a pre-alpha version of a relational database, I don't think that knowledge of CVS is an onerous requirement. Agreed, however it's nice to have landmarks along the way, such as a point of stability or once a new feature gets rolled in and need some use (ex: schemas or auto-commit). At any rate, the problem with releasing snapshots is that the system catalogs would change so often that upgrading between snapshots would be a headache. i.e. the changes required to upgrade from a 2 week old development snapshot to a current snapshot would still be non-trivial, significantly reducing the usefulness of snapshots, IMHO. Don't doubt it at all, but that reminds me: I need to add a message reminding the developer to re-initdb when installing this version. This is for a -devel port that'd track the new features that are being rolled into postgresql so there's a large degree of competence assumed when someone installs this particular version from the tree. I've also slapped up some big warnings to make sure that it's developers only. At the moment, however, I think I'll probably roll my own tarballs when an island of stability has been found unless the snapshot server is holding onto its snaps for several months at a time. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] SRF and pg_group
I realise that this has already been done, by Joe Conway I think. Indeed I was
looking at this just before beta1 when I happened to notice the post giving the
plpgsql function. However, as I had started work on it and I was interested in
seeing how things should be done I continued, only not in so much of a rush.
In the interests on finding out if I have approached this the right way, or the
way a more experienced backend programmer would, I'd appreciate any comments on
the attached .c file. In particular, I'm not sure what I'm doing with regard to
memory contexts, I think I may have one unnecessary switch in there, and in
general I seem to be doing a lot of work just to find out tidbits of
information.
I based this on, i.e. started by editing, Joe Conway's tablefunc.c but I think
there's very little of the original left in there.
I've also attached the .h, Makefile and .sql.in files to make this work if
anyone is interested in giving it a run. The .sql.in shows the usage. I did
this in a directory called pggrouping, for the sake of a better name, under the
contrib directory in my tree, so that's probably the best place to build it.
Thanks, and sorry for adding to people's email and work load.
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
/*
* Derived from tablefunc.c, a sample to demonstrate C functions which
* return setof scalar and setof composite by Joe Conway [EMAIL PROTECTED]
*
* Copyright 2002 by PostgreSQL Global Development Group
*
* Permission to use, copy, modify, and distribute this software and its
* documentation for any purpose, without fee, and without a written agreement
* is hereby granted, provided that the above copyright notice and this
* paragraph and the following two paragraphs appear in all copies.
*
* IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY FOR
* DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
* LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
* DOCUMENTATION, EVEN IF THE AUTHOR OR DISTRIBUTORS HAVE BEEN ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*
* THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
* AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
* ON AN AS IS BASIS, AND THE AUTHOR AND DISTRIBUTORS HAS NO OBLIGATIONS TO
* PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
*
*/
#include stdlib.h
#include math.h
#include postgres.h
#include fmgr.h
#include funcapi.h
#include executor/spi.h
#include utils/builtins.h
#include utils/guc.h
#include utils/lsyscache.h
#include pggrouping.h
typedef struct unpack_array_fctx
{
SPITupleTable *spi_tuptable; /* sql results from user query */
TupleDesc tupdesc; /* TupleDesc for results */
int unpack_attrnum; /* attribute number to be unpacked */
int lastcall_cntr; /* previous call_cntr, invlaid = -1 */
int lastindex; /* index of the last array item sent, invalid 1 */
} unpack_array_fctx;
#define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
#define GET_STR(textp) DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(textp)))
static Datum expandArray_SRF(FunctionCallInfo info,
char *sql,
int unpackAttrNum);
static Datum expandArray_SRF_FirstCall(FunctionCallInfo fcinfo,
FuncCallContext *funcctx,
const char *sql,
const int unpackAttrNum);
static Datum expandArray_SRF_GetTuple(FunctionCallInfo fcinfo,
FuncCallContext *funcctx);
static TupleDesc makeUnpackedTupleDesc(TupleDesc src_tupdesc,
int unpack_attrnum);
static bool similarTupleDescs(TupleDesc ret_tupdesc,
TupleDesc sql_tupdesc);
/*
* pg_group_expandusers
*
* Return pg_group where each tuple has grolist attribute of int4[] type
* changed to be of type int4 and to hold only one user id.
*/
PG_FUNCTION_INFO_V1(pg_group_long);
Datum
pg_group_long(PG_FUNCTION_ARGS)
{
return expandArray_SRF(fcinfo,
select groname,grosysid,grolist from pg_group,
3);
}
/*
* expand_array_srf
*
* Return tuples such that the elements of an array attribute are
* extracted in turn and placed into the output instead of the array.
* Declared to fmgr as:
* CREATE FUNCTION the_name(text,integer) RETURNS SETOF RECORD ...
*
* where the text argument is the query string to obtain the source
* data and the integer argument gives the column number of the array
* to expand.
*
* Note, despite checking number of arguments this is in no way safe
* from some one creating a fmgr function which uses wrong argument types.
*/
PG_FUNCTION_INFO_V1(expand_array_srf);
Datum
expand_array_srf(PG_FUNCTION_ARGS)
{
if (fcinfo-nargs != 2)
elog(ERROR,
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
Sean Chittenden [EMAIL PROTECTED] writes: Don't doubt it at all, but that reminds me: I need to add a message reminding the developer to re-initdb when installing this version. The catversion check isn't good enough for you? It seems you are busily reinventing a bunch of decisions that have already been made, and in most cases have stood the test of time. Perhaps you should be less eager to make this Sean's Own Postgres Version, and more eager to be pushing out something that matches what everyone else is testing. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule updates and PQcmdstatus() issue
On Tue, 10 Sep 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Mon, 9 Sep 2002, Bruce Momjian wrote: All the problems here are coming from INSTEAD rules. We don't have INSTEAD triggers or contraints. Sure we do, well sort of. :) Make a before trigger that does a different statement and returns NULL to abort the original action on that row. I think we can reasonably leave the side-effects of triggers out of the discussion. PQcmdStatus numbers have never included side-effects of triggers in the past, and I see no reason for them to start now. I agree, I was just commenting on the instead trigger comment. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Script to compute random page cost
OK, what you are seeing here is that for your platform the TESTCYCLES size isn't enough; the numbers are too close to measure the difference. I am going to increase the TESTCYCLES from 5k to 10k. That should provide better numbers. --- Oliver Elphick wrote: On Mon, 2002-09-09 at 07:13, Bruce Momjian wrote: OK, turns out that the loop for sequential scan ran fewer times and was skewing the numbers. I have a new version at: ftp://candle.pha.pa.us/pub/postgresql/randcost Latest version: olly@linda$ random test: 14 sequential test: 11 null timing test: 9 random_page_cost = 2.50 olly@linda$ for a in 1 2 3 4 5 do ~/randcost done Collecting sizing information ... random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 10 sequential test: 10 null timing test: 10 Sequential time equals null time. Increase TESTCYCLES and rerun. Available memory (512M) exceeds the total database size, so sequential and random are almost the same for the second and subsequent runs. Since, in production, I would hope to have all active tables permanently in RAM, would there be a case for my using a page cost of 1 on the assumption that no disk reads would be needed? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 -- 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] Script to compute random page cost
Oliver Elphick wrote: Available memory (512M) exceeds the total database size, so sequential and random are almost the same for the second and subsequent runs. Since, in production, I would hope to have all active tables permanently in RAM, would there be a case for my using a page cost of 1 on the assumption that no disk reads would be needed? Yes, in your case random_page_cost would be 1 once the data gets into RAM. In fact, that is the reason I used only /data/base for testing so places where data can load into ram will see lower random pages costs. I could just create a random file and test on that but it isn't the same. -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
Agreed, however some of the loop-unrolling might prove to have some optimization, but we'll see. I'd like to think that there's some actual value in -O6 beyond the geek appeal of being able to say it's been compiled with all the optimizations possible. ::shrug:: BTW, -O3 is the highest GCC optimization level; anything higher than that is synonymous with -O3, I believe. Also, -O3 doesn't have anything to do with loop unrolling, AFAIK. In terms of instruction optimization, yes. Above that is where it does the loop unrolling, inlining, and other various tweaks. As for the value of enabling that flag, it depends IMHO on the performance gain you see. If there is a significance difference, let -hackers know, and it might be worth considering enabling it by default for certain platforms. If the performance difference is negligible (which is what I'd suspect), I don't think it's worth the code bloat, reduced debuggability, or the potential for running into more compiler bugs. Agreed. Later today I'll thump on my good SCSI system and let you know what happens. Also, if -O3 *is* a good compiler option, I dislike the idea of enabling it for your own packages but no one else's. IMHO distributors should not futz with packages more than is strictely necessary, and a change like this seems both unwarranted, and potentially dangerous. If -O3 is a good idea, we should make the change for the appropriate platforms in the official source, and let it get the widespread testing it requires. Agreed, but the testing's got to start someplace. :~) The -O3 is a tunable that you can optionally set or unset so it's not like I'm forcing it to be on (thought it will by default for the -devel port). -sc -- Sean Chittenden ---(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] Optimization levels when compiling PostgreSQL...
Don't doubt it at all, but that reminds me: I need to add a message
reminding the developer to re-initdb when installing this version.
The catversion check isn't good enough for you?
Nope, it's good enough and then some. I've gotten in the habit of
just re-initdb'ing and figured that's what the rest of the world did:
didn't realize there was a way of testing the catalog versions. My
life seems to be spent inside the DB and not playing with it from the
CLI.
It seems you are busily reinventing a bunch of decisions that have
already been made, and in most cases have stood the test of time.
Perhaps you should be less eager to make this Sean's Own Postgres
Version, and more eager to be pushing out something that matches
what everyone else is testing.
Ouch! I hope not. Testing gcc optimizations and adding a developers
port of PostgreSQL hopefully isn't for just myself.
PostgreSQL has a chunk of work that needs to happen when setting it up
or upgrading and I am trying to smooth out as much of that as possible
such that installing PostgreSQL gets to the point of having it
reasonably tuned for the OS its being installed on after installing
the port. Its not that install PostgreSQL is hard, far from, but
there's a reasonable checklist of things that needs to happen and that
requires a certain requisite knowledge of the database, tuning, and
the OS you're on: something, for better or worse, I assume most
users/DBA's don't have. In a typical install, I generally do some
variation of the following:
*) setenv CFLAGS '-g -O3'
*) make
*) pg_dumpall ~/db_dump
*) ${LOCALBASE}/etc/rc.d/010.pgsql.sh stop
*) make deinstall
*) make install
*) mv $PGDATA $PGDATA.old
*) initdb
*) diff -c $PGDATA.old/data/postgresql.conf $PGDATA/data/postgresql.conf
$PGDATA/data/postgresql.conf.patch
*) cd $PGSQL/data; patch -p0 postgresql.conf.patch
*) edit postgresql.conf
*) ${LOCALBASE}/etc/rc.d/010.pgsql.sh start
*) psql -f ~/db_dump
*) vacuumdb -a -f -z
*) tweak various sysctl's to increase fd's, etc.
*) hopefully don't have to recompile the kernel with more shmem, etc
On some hosts, I've even got a script that I run that does all of that
for me because it's the exact same procedure every time. :-/ Getting
as much of that done and taken care of as possible would probably be
appreciated and enjoyed by others. It's not fool-proof, don't get me
wrong, but there's certainly some of that that can be automated, and
with tunables I'd like to for usabilities sake.
::shrug:: Usability's a touchy subject though and none of this will be
on by default so as to not offend the power-users out there. -sc
--
Sean Chittenden
---(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 new autocommit config parameter and jdbc
On Tue, 10 Sep 2002, Stephan Szabo wrote: It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
I am waiting for this thread to conclude before deciding exactly what to do for the jdbc driver for 7.3. While using the 'set autocommit true' syntax is nice when talking to a 7.3 server, the jdbc driver also needs to be backwardly compatible with 7.2 and 7.1 servers. So it may just be easier to continue with the current way of doing things, even in the 7.3 case. thanks, --Barry Curt Sampson wrote: On Mon, 9 Sep 2002, Tom Lane wrote: If autocommit=off really seriously breaks JDBC then I don't think a simple SET command at the start of a session is going to do that much to improve robustness. What if the user issues another SET to turn it on? You mean, to turn it off again? The driver should catch this, in theory. In practice we could probably live with saying, Don't use SET AUTOCOMMIT; use the methods on the Connection class instead. Probably the driver should be changed for 7.3 just to use the server's SET AUTOCOMMIT functionality cjs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS]
I am confused. This wording seems fine to me. --- Oliver Elphick wrote: On Tue, 2002-09-10 at 00:50, Philip Warner wrote: ALTERNATIVELY, define the language in template1, then just edit dump1.lis to remove the line for the language definition, and run pg_restore -L dump1.lis. That doesn't work for a dump and reload, because 7.3's pg_dumpall writes a script to create the databases from template0 rather than template1. The 7.3 documentation for pg_dump says: Notes If your installation has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example: CREATE DATABASE foo WITH TEMPLATE = template0; but this seems to be out of date. pg_dumpall actually uses template0 itself. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
Tom Lane wrote: An example of how this would simplify life: consider the problem of a client that wants to ensure autocommit is on. A simple SET autocommit TO on; doesn't work at the moment: if autocommit is off, then you'll need to issue a COMMIT as well to get out of the implicitly started transaction. But you don't want to just issue a COMMIT, because you'll get a nasty ugly WARNING message on stderr if indeed autocommit was on already. The only warning-free way to issue a SET right now if you are uncertain about autocommit status is BEGIN; SET ; COMMIT; Blech. But if SET doesn't start a transaction then you can still just do SET. This avoids some changes we'll otherwise have to make in libpq startup, among other places. Does anyone see any cases where it's important for SET to start a transaction? (Of course, if you are already *in* a transaction, the SET will be part of that transaction. The question is whether we want SET to trigger an implicit BEGIN or not.) Uh, well, because we now have SET's rollback in an aborted transaction, there is an issue of whether the SET is part of the transaction or not. Seems it has to be for consistency with our rollback behavior. -- 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]
I do this to begin with (createdb -T template0 db). FYI: Here's what I've determined is the best thing to do: 1. create the database from template0 2. create the needed languages (plpgsql, plperl, plpython) in the database 3. create the needed tables, functions, types, etc. from script files. 4. restore only the data from the dump. Seems to be the easiest and safest way to convert the database(s) to 7.3b1 (we have a mirad of databases for different needs each having their own set of types, functions and languages that they use). I'll let you know if I run into problems with this - as this, in my opinion, should not! Thanks to all for the help, L. On Tue, 10 Sep 2002, Bruce Momjian wrote: I am confused. This wording seems fine to me. --- Oliver Elphick wrote: On Tue, 2002-09-10 at 00:50, Philip Warner wrote: ALTERNATIVELY, define the language in template1, then just edit dump1.lis to remove the line for the language definition, and run pg_restore -L dump1.lis. That doesn't work for a dump and reload, because 7.3's pg_dumpall writes a script to create the databases from template0 rather than template1. The 7.3 documentation for pg_dump says: Notes If your installation has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example: CREATE DATABASE foo WITH TEMPLATE = template0; but this seems to be out of date. pg_dumpall actually uses template0 itself. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Does anyone see any cases where it's important for SET to start a transaction? (Of course, if you are already *in* a transaction, the SET will be part of that transaction. The question is whether we want SET to trigger an implicit BEGIN or not.) Uh, well, because we now have SET's rollback in an aborted transaction, there is an issue of whether the SET is part of the transaction or not. Seems it has to be for consistency with our rollback behavior. Yeah, it must be part of the transaction unless we want to reopen the SET-rollback can of worms (which I surely don't want to). However, a SET issued outside any pre-existing transaction block could form a self-contained transaction without any logical difficulty, even in autocommit-off mode. The question is whether that's more or less convenient, or standards-conforming, than what we have. An alternative that I'd really rather not consider is making SET's behavior dependent on exactly which variable is being set ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Does anyone see any cases where it's important for SET to start a transaction? (Of course, if you are already *in* a transaction, the SET will be part of that transaction. The question is whether we want SET to trigger an implicit BEGIN or not.) Uh, well, because we now have SET's rollback in an aborted transaction, there is an issue of whether the SET is part of the transaction or not. Seems it has to be for consistency with our rollback behavior. Yeah, it must be part of the transaction unless we want to reopen the SET-rollback can of worms (which I surely don't want to). However, a SET issued outside any pre-existing transaction block could form a self-contained transaction without any logical difficulty, even in autocommit-off mode. The question is whether that's more or less convenient, or standards-conforming, than what we have. That seems messy. What you are saying is that if autocommit is off, then in: SET x=1; UPDATE ... SET y=2; ROLLBACK; that the x=1 doesn't get rolled back bu the y=2 does? I can't see any good logic for that. An alternative that I'd really rather not consider is making SET's behavior dependent on exactly which variable is being set ... Agreed. We discussed that in the SET rollback case and found it was more trouble that it was worth. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Tue, 10 Sep 2002, scott.marlowe wrote: On Tue, 10 Sep 2002, Stephan Szabo wrote: It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Yes, but I think it should still work the same as if it had failed in an explicit transaction if autocommit is false (or was that directed at someone else). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] ODBC problem/question
Hi, I was just contacted by a customer about the SQLProcedureColumns call in our odbc driver. It appears this call is undefined in the standard odbc driver but is available in odbcplus. Could anyone please enlighten me why this was forked and not merged into one driver? Is there a problem when I take the odbcplus code and put it into the odbc driver? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Tue, 10 Sep 2002, Stephan Szabo wrote: On Tue, 10 Sep 2002, scott.marlowe wrote: On Tue, 10 Sep 2002, Stephan Szabo wrote: It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Yes, but I think it should still work the same as if it had failed in an explicit transaction if autocommit is false (or was that directed at someone else). Sorry, I was agreeing with you, and disagreeing with the guy who was saying that selects shouldn't start a transaction. Should have mentioned that. :-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: On Tue, 10 Sep 2002, Stephan Szabo wrote: It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior. Select command don't start transaction - it is not good I think you need more justification than it is not good. If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Ok.Any lock or update,delete, insert (and all ddl command) start transaction (select for update, too), but simple select no.Select don't change data and no transaction - this process cannot lost consistency (any command with error too). And if transaction start, so what ... I will (maybe) continue transaction (I don't end transaction), but I get error. and I must end transaction I think that we must parse command, choose if 'start transaction' and start transaction or no. regards Haris Peco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
Bruce Momjian [EMAIL PROTECTED] writes: That seems messy. What you are saying is that if autocommit is off, then in: SET x=1; UPDATE ... SET y=2; ROLLBACK; that the x=1 doesn't get rolled back bu the y=2 does? Yes, if you weren't in a transaction at the start. I can't see any good logic for that. How about the SQL spec requires it? Date seems to think it does, at least for some variables (of course we have lots of variables that are not in the spec). I can't find anything very clear in the SQL92 or SQL99 documents, and I'm not at home at the moment to look at my copy of Date, but if Curt's reading is correct then we have spec precedent for acting this way. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: That seems messy. What you are saying is that if autocommit is off, then in: SET x=1; UPDATE ... SET y=2; ROLLBACK; that the x=1 doesn't get rolled back bu the y=2 does? Yes, if you weren't in a transaction at the start. I can't see any good logic for that. How about the SQL spec requires it? Date seems to think it does, at least for some variables (of course we have lots of variables that are not in the spec). I can't find anything very clear in the SQL92 or SQL99 documents, and I'm not at home at the moment to look at my copy of Date, but if Curt's reading is correct then we have spec precedent for acting this way. Spec or not, it looks pretty weird so I would question following the spec on this one. Do we want to say With autocommit off, SET will be in it's own transaction if it appears before any non-SET command, and SETs are rolled back except if autocommit off and they appear before any non-SET? I sure don't. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
On Tuesday 10 September 2002 09:55 pm, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: That seems messy. What you are saying is that if autocommit is off, then in: SET x=1; UPDATE ... SET y=2; ROLLBACK; that the x=1 doesn't get rolled back bu the y=2 does? Yes, if you weren't in a transaction at the start. I can't see any good logic for that. How about the SQL spec requires it? Date seems to think it does, at least for some variables (of course we have lots of variables that are not in the spec). I can't find anything very clear in the SQL92 or SQL99 documents, and I'm not at home at the moment to look at my copy of Date, but if Curt's reading is correct then we have spec precedent for acting this way. I know what Oracle do (default mode autocommit off except JDBC) : only DML and DDL command start transaction and DDL command end transaction. There is another problem: if select start transaction why error - I will continue transaction. Why invalid command start transaction ? regards haris peco ---(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] ODBC problem/question
-Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED]] Sent: 10 September 2002 20:42 To: PostgreSQL Interfaces; PostgreSQL Hacker Subject: [HACKERS] ODBC problem/question Hi, I was just contacted by a customer about the SQLProcedureColumns call in our odbc driver. It appears this call is undefined in the standard odbc driver but is available in odbcplus. Could anyone please enlighten me why this was forked and not merged into one driver? Is there a problem when I take the odbcplus code and put it into the odbc driver? Hi Michael, There are currently 3 variants of the driver. PostgreSQL - This is the current ODBC 2.5 compliant driver. PostgreSQL+ - This is a development version that is ODBC 3.0 compliant. PostgreSQL+ Unicode - This is PostgreSQL+ with Unicode support. We are aiming for PostgreSQL+ Unicode to be the only driver as soon as possible, but due to the way ODBC3 and Unicode support were added (ie. quickly, to solve immediate problems), we (Hiroshi I) felt it was best to keep them seperate until we were sure of their reliability. Currently, PostgreSQL+ seems pretty good as, does the Unicode version, though that is still missing some features iirc. HTH, Regards, Dave. ---(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 new autocommit config parameter and jdbc
On Tue, 10 Sep 2002, snpe wrote: On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Ok.Any lock or update,delete, insert (and all ddl command) start transaction (select for update, too), but simple select no.Select don't change data and no transaction - this process cannot lost consistency (any command with error too). At least in serializable isolation level you'll probably get different results if a transaction commits between those two selects based on whether a transaction is started or not. Should two serializable selects in the same session see the same snapshot when autocommit is off? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Bruce Momjian wrote: Jan Wieck wrote: We should surely keep this on a much more technical level and avoid any personal offendings. To do so, please explain to me why you think that triggers and constraints are out of focus here? What is the difference between a trigger, a rule and an instead rule from a business process oriented point of view? I think there is none at all. They are just different techniques to do one and the same, implement business logic in the database system. All the problems here are coming from INSTEAD rules. We don't have INSTEAD triggers or contraints. So a BEFORE INSERT trigger on table1 that does an UPDATE to table2 and then returns NULL is not effectively the same as an ON INSERT ... DO INSTEAD UPDATE ... rule? Hmmm, the end result is exactly the same so what do you call it? I think we will have no chance to really return the number of VIEW-tuples affected. So any implementation is only a guess and we could simply return fixed 42 if some tuples where affected at all. This return is as wrong (according to Steve) as everything else but at least we have a clear definition what it means. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS]
On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote: I am confused. This wording seems fine to me. The confusion was mine. Of course, pg_dump doesn't create the database. I was mixing it up with pg_dumpall. However, there is a problem in that recent changes have made it quite likely that an upgrade will fail and will requre the dump script to be edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS]
Oliver Elphick wrote: On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote: I am confused. This wording seems fine to me. The confusion was mine. Of course, pg_dump doesn't create the database. I was mixing it up with pg_dumpall. However, there is a problem in that recent changes have made it quite likely that an upgrade will fail and will requre the dump script to be edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. OK, can you specifically list them? -- 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]
On Tue, 2002-09-10 at 23:09, Bruce Momjian wrote: Oliver Elphick wrote: edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. OK, can you specifically list them? Message yesterday to pgsql-hackers Subject: [HACKERS] pg_dump problems in upgrading Date: 09 Sep 2002 12:31:39 +0100 Message-Id: 1031571099.24419.199.camel@linda -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Jan Wieck wrote: Bruce Momjian wrote: Jan Wieck wrote: We should surely keep this on a much more technical level and avoid any personal offendings. To do so, please explain to me why you think that triggers and constraints are out of focus here? What is the difference between a trigger, a rule and an instead rule from a business process oriented point of view? I think there is none at all. They are just different techniques to do one and the same, implement business logic in the database system. All the problems here are coming from INSTEAD rules. We don't have INSTEAD triggers or contraints. So a BEFORE INSERT trigger on table1 that does an UPDATE to table2 and then returns NULL is not effectively the same as an ON INSERT ... DO INSTEAD UPDATE ... rule? Hmmm, the end result is exactly the same so what do you call it? Well, yes, functionally it is the same and we would have trouble dealing with that too. I didn't know you could NULL return from a trigger and it would exit the statement. I think we will have no chance to really return the number of VIEW-tuples affected. So any implementation is only a guess and we could simply return fixed 42 if some tuples where affected at all. This return is as wrong (according to Steve) as everything else but at least we have a clear definition what it means. Yes, my guess is that accumulating everything with the same tags is the closest we are going to get and does return the proper values in simple multi-statement INSTEAD rules. -- 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] Optimization levels when compiling PostgreSQL...
Sean Chittenden writes: Hrm, I should go check the archives, but I thought what was used was one step below -C[fF] and was used because of size concerns for embedded databases. My memory for what happens on mailing lists seems to be fading though so I'll look it up. The particular decision was -CF vs. -CFa (a for alignment). The latter was about 2% faster in the test case but increased the size of the executable by 80 kB. Note that the test case was extremely contrived -- parsing of about 70 MB of uninteresting commands with little to no other activity. For a normal command the scanner overhead is really small. On the other hand, the test case was run on a x86 machine which is not known for being sensitive to alignment. So on a different architecture you might get more significant speedups. Try it if you like. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote: On Tue, 10 Sep 2002, snpe wrote: On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Ok.Any lock or update,delete, insert (and all ddl command) start transaction (select for update, too), but simple select no.Select don't change data and no transaction - this process cannot lost consistency (any command with error too). At least in serializable isolation level you'll probably get different results if a transaction commits between those two selects based on whether a transaction is started or not. Should two serializable selects in the same session see the same snapshot when autocommit is off? It is session, not transaction.My select don't change data and this is not transaction. We're going around in circles. Does it matter if data is changed? I don't think so, since at least in serializable isolation level the snapshot that is seen depends on whether you're in a transaction or not, and given autocommit=off I believe that you should get a consistent snapshot between them. If you believe it should matter, you need to give a reason. I don't think it's a spec reason given that my sql92 spec draft says: The following SQL-statements are transaction initiating SQL- statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: ... o select statement: single row o direct select statement: multiple rows unless it changed. There might be a compatibility reason, if so, with what and is it stronger than reasons to start a transaction. There might be another logical reason, if so, what is it and why does it matter? My abother question, agian : why error (bad typing) start transaction ? That depends. Given the way the spec is worded, it says nothing about other statements, so we need to decide those ourselves. I don't see anything that implies that a select statement that errors would be any different than a select statement that doesn't as far as starting a transaction goes in my sql92 spec draft. If you were to type in foo as a command, I could see a case that maybe that shouldn't be transaction initiating, but afair that wasn't the case you had, you had a select command against an invalid table name. ---(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] problem with new autocommit config parameter and jdbc
On Wednesday 11 September 2002 01:25 am, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote: On Tue, 10 Sep 2002, snpe wrote: On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects. Ok.Any lock or update,delete, insert (and all ddl command) start transaction (select for update, too), but simple select no.Select don't change data and no transaction - this process cannot lost consistency (any command with error too). At least in serializable isolation level you'll probably get different results if a transaction commits between those two selects based on whether a transaction is started or not. Should two serializable selects in the same session see the same snapshot when autocommit is off? It is session, not transaction.My select don't change data and this is not transaction. We're going around in circles. Does it matter if data is changed? I don't think so, since at least in serializable isolation level the snapshot that is seen depends on whether you're in a transaction or not, and given autocommit=off I believe that you should get a consistent snapshot between them. If you believe it should matter, you need to give a reason. I don't think it's a spec reason given that my sql92 spec draft says: The following SQL-statements are transaction initiating SQL- statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: ... o select statement: single row o direct select statement: multiple rows unless it changed. There might be a compatibility reason, if so, with what and is it stronger than reasons to start a transaction. There might be another logical reason, if so, what is it and why does it matter? My abother question, agian : why error (bad typing) start transaction ? That depends. Given the way the spec is worded, it says nothing about other statements, so we need to decide those ourselves. I don't see anything that implies that a select statement that errors would be any different than a select statement that doesn't as far as starting a transaction goes in my sql92 spec draft. If you were to type in foo as a command, I could see a case that maybe that shouldn't be transaction initiating, but afair that wasn't the case you had, you had a select command against an invalid table name. yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) It is not matter for me transaction or not.I get error for correct command after invalid I am sorry if I am confused.English is not my language. regards Haris Peco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) Right, that's a separate issue (I alluded to it earlier, but wasn't sure that's what you were interested in). PostgreSQL treats all errors as unrecoverable. It may be a little loose about immediately rolling back due to the fact that historically autocommit was on and it seemed better to not go into autocommit mode after the error. I doubt that 7.3 is going to change that behavior, but a case might be made that when autocommit is off the error immediately causes a rollback and new transaction will start upon the next statement (that would normally start a transaction). At some point in the future, you'll probably be able to do nested transactions or savepoints or error recovery and this will all be moot. It is not matter for me transaction or not.I get error for correct command after invalid ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
Neil Conway writes: Also, if -O3 *is* a good compiler option, I dislike the idea of enabling it for your own packages but no one else's. IMHO distributors should not futz with packages more than is strictely necessary, and a change like this seems both unwarranted, and potentially dangerous. If -O3 is a good idea, we should make the change for the appropriate platforms in the official source, and let it get the widespread testing it requires. I disagree. Choosing the compiler options is exactly the job of the installer, packager, or distributor. That's why you can specify CFLAGS on the command line after all, and most distributors' build environments make use of that. I don't think we're doing anyone a service if we spread wild speculations about how risky certain compiler options are. If your compiler creates broken code, don't use it. Packagers are expected to know about their compiler. If they create broken packages and behave irresponsibly about it they won't be making packages much longer. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) Right, that's a separate issue (I alluded to it earlier, but wasn't sure that's what you were interested in). PostgreSQL treats all errors as unrecoverable. It may be a little loose about immediately rolling back due to the fact that historically autocommit was on and it seemed better to not go into autocommit mode after the error. I doubt that 7.3 is going to change that behavior, but a case might be made that when autocommit is off the error immediately causes a rollback and new transaction will start upon the next statement (that would normally start a transaction). Why rollback.This is error (typing error).Nothing happen. I think that we need clear set : what is start transaction ? I think that transaction start with change data in database (what don't change data this start not transaction. Oracle dot this and I think that is correct)) P.S when I can find SQL 99 specification ? regards ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) Right, that's a separate issue (I alluded to it earlier, but wasn't sure that's what you were interested in). PostgreSQL treats all errors as unrecoverable. It may be a little loose about immediately rolling back due to the fact that historically autocommit was on and it seemed better to not go into autocommit mode after the error. I doubt that 7.3 is going to change that behavior, but a case might be made that when autocommit is off the error immediately causes a rollback and new transaction will start upon the next statement (that would normally start a transaction). Why rollback.This is error (typing error).Nothing happen. Postgresql currently has no real notion of a recoverable error. In the case of the error you had, probably nothing bad would happen if it continued, but what if that was a unique constraint violation? Continuing would currently probably let you see the table in an invalid state. I think that we need clear set : what is start transaction ? I think that transaction start with change data in database (what don't change data this start not transaction. Oracle dot this and I think that is correct)) I disagree because I think that two serializable select statements in autocommit=off (without a commit or rollback of course) should see the same snapshot. I'm trying to find something either way in a pdf copy of sql99. The multiple row select has gotten hidden somewhere, so it's possible that it's not, but all of opening a cursor, fetching from a cursor and the single row select syntax are labeled as transaction initiating. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
On Wed, 11 Sep 2002, Peter Eisentraut wrote: I disagree. Choosing the compiler options is exactly the job of the installer, packager, or distributor. If there is one, yes. I don't think we're doing anyone a service if we spread wild speculations about how risky certain compiler options are. If your compiler creates broken code, don't use it. Packagers are expected to know about their compiler. If they create broken packages and behave irresponsibly about it they won't be making packages much longer. However, many users are not as knowledgable as packagers, but may still be compiling from source. For those people, I don't think it's unreasonable to say, Use -O2 unless you know what you are doing. (I'm not sure we're actually disagreeing here, but I just wanted to make this point clear.) cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Script to compute random page cost
On Tue, 10 Sep 2002, Tom Lane wrote: Curt Sampson [EMAIL PROTECTED] writes: Well, for the sequential reads, the readahead should be trigerred even when reading from a raw device. That strikes me as an unportable assumption. Not only unportable: but false. :-) NetBSD, at least, does read-ahead only through the buffer cache. Thinking about it, you *can't* do read-ahead on a raw device, because you're not buffering. Doh! Perhaps it's time to remind people that what we want to measure is the performance seen by a C program issuing write() and read() commands, transferring 8K at a time, on a regular Unix filesystem. Right. Which is what randread does, if you give it a file rather than a raw device. I'm actually just now working on some modifications for it that will let you work against a bunch of files, rather than just one, so it will very accurately emulate a postgres random read of blocks from a table. There are two other tricky things related to the behaviour, however: 1. The buffer cache. You really need to be working against your entire database, not just a few gigabytes of its data, or sample data. 2. Multiple users. You really want a mix of simultaneous accesses going on, with as many processes as you normally have users querying the database. These can probably both be taken care of with shell scripts, though. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
On Tue, 10 Sep 2002, Barry Lind wrote: I am waiting for this thread to conclude before deciding exactly what to do for the jdbc driver for 7.3. While using the 'set autocommit true' syntax is nice when talking to a 7.3 server, the jdbc driver also needs to be backwardly compatible with 7.2 and 7.1 servers. Can you not check the server's version on connect? It would be ideal if the JDBC driver, without modification, ran all tests properly against 7.3, 7.2 and 7.1. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
On Tue, 10 Sep 2002, Bruce Momjian wrote: Do we want to say With autocommit off, SET will be in it's own transaction if it appears before any non-SET command, and SETs are rolled back except if autocommit off and they appear before any non-SET? Not really, I don't think. But I'm starting to wonder if we should re-think all SET commands being rolled back if a transaction fails. Some don't seem to make sense, such as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
On Tuesday 10 September 2002 09:31 pm, Curt Sampson wrote: On Wed, 11 Sep 2002, Peter Eisentraut wrote: I disagree. Choosing the compiler options is exactly the job of the installer, packager, or distributor. If there is one, yes. If the enduser is directly compiling the source, then that user is responsible for passing the flags desired -- they become their own packager. However, many users are not as knowledgable as packagers, but may still be compiling from source. For those people, I don't think it's unreasonable to say, Use -O2 unless you know what you are doing. I still remember when the Alpha port _required_ -O0. And it was documented that way, IIRC. Compiling from source implies certain knowledge. Automated from source builds, such as ports or linux distributions such as Gentoo can handle this in their own build systems. If someone can figure out how to override the default, then they can deal with the results, IMHO. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
Curt Sampson wrote: On Tue, 10 Sep 2002, Bruce Momjian wrote: Do we want to say With autocommit off, SET will be in it's own transaction if it appears before any non-SET command, and SETs are rolled back except if autocommit off and they appear before any non-SET? Not really, I don't think. But I'm starting to wonder if we should re-think all SET commands being rolled back if a transaction fails. Some don't seem to make sense, such as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back. Yes, but the question is whether it is better to be consistent and roll them all back, or to pick and choose which ones to roll back. Consistency is nice. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Tue, 2002-09-10 at 21:44, Curt Sampson wrote: But there were some issues with rolling back and SET commands, weren't there? I remember a long discussion about this that I'm not sure I want to go back to. :-) So.. Unless explicitly requested, a SET command should have immediate effect? The other constrictive value I can think of is search_path. -- Must be transaction safe BEGIN; CREATE SCHEMA newschema; SET search_path = newschema; ROLLBACK; CREATE TABLE... -- This should be ok BEGIN; SET autocommit = on; INSERT ... COMMIT; -- SET takes place on commit, as it was an explicit transaction -- This is requested behavior SET autocommit = off; SET autocommit = on; INSERT... -- immediate effect, since autocommit is on -- This gets interesting be ok as the schema must exist SET autocommit = off; CREATE SCHEMA newschema; SET search_path = newschema; -- implicit commit here? ROLLBACK; CREATE TABLE ... -- search_path must roll back or schema must have been created -- Similar to the above SET autocommit = off; CREATE TABLE ... SET autocommit = on; -- implicit commit here? ROLLBACK; -- Does this rollback anything? -- Was CREATE TABLE committed with the second SET statement? Well, I'm not going to go chase it down right now, but ISTR that DECLAREing a cursor just allocates a variable name or the storage for it or something like that; it doesn't actually create an active cursor. Indeed, this is how the cursor is able to cross transactions. It is closed at transaction commit, and re-created in next use. 4.29: For every declare cursor in an SQL-client module, a cursor is effectively created when an SQLtransaction (see Subclause 4.32, SQL-transactions ) referencing the SQL-client module is initiated. -- Rod Taylor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimization levels when compiling PostgreSQL...
On Tue, 10 Sep 2002, Lamar Owen wrote: I still remember when the Alpha port _required_ -O0. And it was documented that way, IIRC. Good. It would also be very nice if, in situations like this, the configure script could detect this and use -O0 when compiling on the alpha. Compiling from source implies certain knowledge. No it doesn't. All it means is that someone's using a system for which they don't have a package handy. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please rename split(text,text,int) to splitpart
What do people think if this change? --- Hannu Krosing wrote: It seems that my last mail on this did not get through to the list ;( Please consider renaming the new builtin function split(text,text,int) to something else, perhaps split_part(text,text,int) (like date_part) The reason for this request is that 3 most popular scripting languages (perl, python, php) all have also a function with similar signature, but returning an array instead of single element and the (optional) third argument is limit (maximum number of splits to perform) I think that it would be good to have similar function in (some future release of) postgres, but if we now let in a function with same name and arguments but returning a single string instead an array of them, then we will need to invent a new and not so easy to recognise name for the real split function. Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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/users-lounge/docs/faq.html
Re: [HACKERS] beta1 packaged
Rod, are you still seeing this failure? --- Rod Taylor wrote: On Wed, 2002-09-04 at 22:39, Marc G. Fournier wrote: will announce it on -announce tomorrow, if ppl want to take a quick look at it ... man pages weren't included, but I did regenerate the docs per Peter's suggested commands ... './configure make check' passes on i386 FreeBSD. SunOS control.shared2 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-5_10 shows an error in ALTER TABLE tests: c cat src/test/regress/regression.diffs *** ./expected/alter_table.out Fri Aug 30 12:23:20 2002 --- ./results/alter_table.out Thu Sep 5 07:44:18 2002 *** *** 367,374 -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE pktable cascade; - NOTICE: Drop cascades to constraint $2 on table fktable NOTICE: Drop cascades to constraint $1 on table fktable DROP TABLE fktable; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, --- 367,374 -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Relation pg_temp_5. does not exist DROP TABLE pktable cascade; NOTICE: Drop cascades to constraint $1 on table fktable DROP TABLE fktable; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- 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] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) Right, that's a separate issue (I alluded to it earlier, but wasn't sure that's what you were interested in). PostgreSQL treats all errors as unrecoverable. It may be a little loose about immediately rolling back due to the fact that historically autocommit was on and it seemed better to not go into autocommit mode after the error. I doubt that 7.3 is going to change that behavior, but a case might be made that when autocommit is off the error immediately causes a rollback and new transaction will start upon the next statement (that would normally start a transaction). Why rollback.This is error (typing error).Nothing happen. I think that we need clear set : what is start transaction ? I think that transaction start with change data in database (what don't change data this start not transaction. Another interesting case for a select is, what about select func(x) from table; Does func() have any side effects that might change data? At what point do we decide that the statement needs a transaction? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] beta1 packaged
Yep, and he couldn't reproduce it either, and on a different platform. I think that indicates we do have a problem in there, it just doesn't show very often. He even got ASCII garbage in the error message. --- Rod Taylor wrote: I've not been able to reproduce it, so no. But there was another report from someone else about the same failure on another platform. On Tue, 2002-09-10 at 22:50, Bruce Momjian wrote: Rod, are you still seeing this failure? --- Rod Taylor wrote: On Wed, 2002-09-04 at 22:39, Marc G. Fournier wrote: will announce it on -announce tomorrow, if ppl want to take a quick look at it ... man pages weren't included, but I did regenerate the docs per Peter's suggested commands ... './configure make check' passes on i386 FreeBSD. SunOS control.shared2 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-5_10 shows an error in ALTER TABLE tests: c cat src/test/regress/regression.diffs *** ./expected/alter_table.out Fri Aug 30 12:23:20 2002 --- ./results/alter_table.out Thu Sep 5 07:44:18 2002 *** *** 367,374 -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE pktable cascade; - NOTICE: Drop cascades to constraint $2 on table fktable NOTICE: Drop cascades to constraint $1 on table fktable DROP TABLE fktable; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, --- 367,374 -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Relation pg_temp_5. does not exist DROP TABLE pktable cascade; NOTICE: Drop cascades to constraint $1 on table fktable DROP TABLE fktable; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- 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 -- Rod Taylor -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Please rename split(text,text,int) to splitpart
I think it should be made. Don't force an initdb. Beta testers can run the update. 'split' is a pretty standard function these days... Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian Sent: Wednesday, 11 September 2002 10:33 AM To: Hannu Krosing Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Please rename split(text,text,int) to splitpart What do people think if this change? -- - Hannu Krosing wrote: It seems that my last mail on this did not get through to the list ;( Please consider renaming the new builtin function split(text,text,int) to something else, perhaps split_part(text,text,int) (like date_part) The reason for this request is that 3 most popular scripting languages (perl, python, php) all have also a function with similar signature, but returning an array instead of single element and the (optional) third argument is limit (maximum number of splits to perform) I think that it would be good to have similar function in (some future release of) postgres, but if we now let in a function with same name and arguments but returning a single string instead an array of them, then we will need to invent a new and not so easy to recognise name for the real split function. Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
On Tue, 10 Sep 2002, Barry Lind wrote: Yes I can check the server version on connect. In fact that is what the driver already does. However I can't check the version and then based on the version call set autocommit true in one round trip to the server. Since many people don't use connection pools, I am reluctant to add the overhead of an extra roundtrip to the database to set a variable that for most people will already be set to true. It would be ideal if I could in one hit to the database determine the server version and conditionally call set autocommit based on the version at the same time. Hmm. I don't think that there's any real way to avoid a second round trip now, but one thing we might do with 7.3 would be to add a standard stored procedure that will deal with setting appropriate variables and suchlike, and returning the version number and any other information that the JDBC driver needs. (Maybe it can return a key/value table.) That way, once we desupport 7.2 in the far future, we can reduce this to one round trip. Or perhaps we we could try to execute that stored procedure and, if it fails, create it. (Or, if creating it fails, do things the hard way.) That way the first connection you make where the SP is not there you have the overhead of adding it, but all connections after that can use it. (I assume you'd grant all rights to it to the general public.) And it could return its own version so that newer drivers could upgrade it if necessary. Or maybe just have a differently-named one for each version of the driver. This is a bit kludgy, but also sort of elegant, if you think about it On the other hand, perhaps we should just live with two round trips. So long as we've got command batching at some point, we can get the version, and then send all the setup commands we need as a single batch after that. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please rename split(text,text,int) to splitpart
Christopher Kings-Lynne wrote: I think it should be made. Don't force an initdb. Beta testers can run the update. 'split' is a pretty standard function these days... Me too. Patch already sent in, including doc and regression test. And as I said, I'll take a TODO to create a 'split' which either returns an array or maybe as an SRF, so the behavior is more like people will be expecting. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
Oliver Elphick [EMAIL PROTECTED] writes: However, there is a problem in that recent changes have made it quite likely that an upgrade will fail and will requre the dump script to be edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. AFAIK, we did what we could on that front in 7.2.1. If you have ideas on how we can retroactively make things better, I'm all ears ... 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]
On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: However, there is a problem in that recent changes have made it quite likely that an upgrade will fail and will requre the dump script to be edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. AFAIK, we did what we could on that front in 7.2.1. If you have ideas on how we can retroactively make things better, I'm all ears ... So this release is going to be the royal pain release to upgrade to? Not good. People may just not upgrade at all in that case. My datasets aren't complicated enough to trigger some of these problems; people who have complex datasets need to report all failures so that we can at least write a sed/perl/awk script to massage the things that need massaging, if it can be done that easily. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] beta1 packaged
Bruce Momjian [EMAIL PROTECTED] writes: Yep, and he couldn't reproduce it either, and on a different platform. I think that indicates we do have a problem in there, it just doesn't show very often. I agree, this looks a lot like a low-probability bug. But how to attack it when we can't reproduce it with even small probability? We need the reporters to try to figure out what environment made it happen for them. I can chase a bug if I can make it happen one-time-in-ten, or even one-time-in-a-hundred, but I can't do much with a bug that I've only heard secondhand reports of. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Please rename split(text,text,int) to splitpart
Bruce Momjian [EMAIL PROTECTED] writes: What do people think if this change? I'm not thrilled about renaming the function without forcing an initdb ... but the alternatives seem worse. Okay by me if we do it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please rename split(text,text,int) to splitpart
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What do people think if this change? I'm not thrilled about renaming the function without forcing an initdb ... but the alternatives seem worse. Okay by me if we do it. I am not either. How do you do the documentation when the function can be called two ways. I guess we can give the SQL query to fix it during beta2 _and_ add a regression test to make sure it is fix. That sounds like a plan. -- 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] 7.3beta and ecpg
I think we should stop playing around with ecpg. Let's get the beta bison on postgresql.org and package the proper ecpg version for 7.3beta2. If we don't, we are going to get zero testing for 7.3 final. Marc? We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. --- Michael Meskes wrote: Hi, I didn't download the beta but compared the CVS checkouts and it appears the ecpg directory is still the one from 7.2 not the one tagged big_bison. Will this one be moved into the mainstream source? Else we would be stuck with a non-compatible parser. If I shall move it, please tell me, I'm just not doing it before talking to you guys. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- 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
[HACKERS] TOAST docs
Hackers, Is there some documentation on TOAST? In the SGML docs there isn't even a description of it, and in the release notes I cannot find anything but very light mentions. I've seen descriptions scattered around the web while Googling, but they are very light and don't seem official. Any pointers will be appreciated, -- Alvaro Herrera (alvherre[a]atentus.com) Cuando no hay humildad las personas se degradan (A. Christie) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Schemas not available for pl/pgsql %TYPE....
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Joe Conway wrote: Tom Lane wrote: Sean Chittenden [EMAIL PROTECTED] writes: ::sigh:: Is it me or does it look like all of pl/pgsql is schema un-aware (ie, all of the declarations). -sc Yeah. The group of routines parse_word, parse_dblword, etc that are called by the lexer certainly all need work. There are some definitional issues to think about, too --- plpgsql presently relies on the number of names to give it some idea of what to look for, and those rules are probably all toast now. Please come up with a sketch of what you think the behavior should be before you start hacking code. Attached is a diff -c format proposal to fix this. I've also attached a short test script. Seems to work OK and passes all regression tests. Here's a breakdown of how I understand plpgsql's Special word rules -- I think it illustrates the behavior reasonably well. New functions added by this patch are plpgsql_parse_tripwordtype and plpgsql_parse_dblwordrowtype: Identifiers (represents)parsing function identifierplpgsql_parse_word tg_argv T_LABEL (label) T_VARIABLE (variable) T_RECORD(record) T_ROW (row) identifier.identifier plpgsql_parse_dblword T_LABEL T_VARIABLE (label.variable) T_RECORD(label.record) T_ROW (label.row) T_RECORD T_VARIABLE (record.variable) T_ROW T_VARIABLE (row.variable) identifier.identifier.identifier plpgsql_parse_tripword T_LABEL T_RECORD T_VARIABLE (label.record.variable) T_ROW T_VARIABLE (label.row.variable) identifier%TYPE plpgsql_parse_wordtype T_VARIABLE T_DTYPE (variable%TYPE) T_DTYPE (typname%TYPE) identifier.identifier%TYPE plpgsql_parse_dblwordtype T_LABEL T_VARIABLE T_DTYPE (label.variable%TYPE) T_DTYPE (relname.attname%TYPE) new identifier.identifier.identifier%TYPE plpgsql_parse_tripwordtype T_DTYPE (nspname.relname.attname%TYPE) identifier%ROWTYPE plpgsql_parse_wordrowtype T_DTYPE (relname%ROWTYPE) new identifier.identifier%ROWTYPE plpgsql_parse_dblwordrowtype T_DTYPE (nspname.relname%ROWTYPE) Parameters - parallels the above $#plpgsql_parse_word $#.identifier plpgsql_parse_dblword $#.identifier.identifier plpgsql_parse_tripword $#%TYPE plpgsql_parse_wordtype $#.identifier%TYPE plpgsql_parse_dblwordtype $#.identifier.identifier%TYPE plpgsql_parse_tripwordtype $#%ROWTYPE plpgsql_parse_wordrowtype $#.identifier%ROWTYPE plpgsql_parse_dblwordrowtype Comments? Thanks, Joe Index: src/pl/plpgsql/src/pl_comp.c === RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v retrieving revision 1.51 diff -c -r1.51 pl_comp.c *** src/pl/plpgsql/src/pl_comp.c 4 Sep 2002 20:31:47 - 1.51 --- src/pl/plpgsql/src/pl_comp.c 9 Sep 2002 04:22:24 - *** *** 1092,1097 --- 1092,1217 return T_DTYPE; } + /* -- + * plpgsql_parse_tripwordtype Same lookup for
Re: [HACKERS] contrib/ intarray, ltree, intagg broken(?) by array changes
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Teodor Sigaev wrote: intarray and ltree both seem to be mapping their own declarations onto arrays using largely-similar code. But while intarray fails its regression test, I find ltree still passes. So I'm confused about what that code is really doing and don't want to touch it. Please, apply attached patch, it solves the problem. -- Teodor Sigaev [EMAIL PROTECTED] [ application/gzip is not supported, skipping... ] -- 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] 7.3beta and ecpg
Dann Corbit wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 9:10 PM To: Michael Meskes Cc: PostgreSQL Hacker; Marc G. Fournier Subject: Re: [HACKERS] 7.3beta and ecpg I think we should stop playing around with ecpg. Let's get the beta bison on postgresql.org and package the proper ecpg version for 7.3beta2. If we don't, we are going to get zero testing for 7.3 final. Marc? We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. I have a dumb idea... Why not just package the output of the Bison beta version? It may not be comprehensible, but it does not need to be generated on any particular target machine does it? Sure, it would be nice to be able to process the original grammar on any client workstation. But if it will hold up the entire project, why not just ship the preprocessed output? We do ship just the preprocessed output. We need the new bison on postgresql.org and we need the CVS to be updated for the new version and then beta2 will hold the proper bison output. -- 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/users-lounge/docs/faq.html
Re: [HACKERS]
Lamar Owen [EMAIL PROTECTED] writes: On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote: AFAIK, we did what we could on that front in 7.2.1. If you have ideas on how we can retroactively make things better, I'm all ears ... So this release is going to be the royal pain release to upgrade to? pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have mixed-case DB names. AFAIK it's okay if you use a later-than-7.2 pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got info to the contrary then he'd better be more specific about what he thinks should be fixed for 7.3. Griping about the fact that 7.2.0 is broken is spectacularly unproductive at this point. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SIMILAR TO
Is this a TODO? --- Peter Eisentraut wrote: Thomas Lockhart writes: SIMILAR TO doesn't implement the SQL standard, it's only a wrapper around the POSIX regexp matching, which is wrong. I thought someone wanted to fix that, but if it's not happening it should be removed. Please be specific on what you would consider correct. I'm not recalling any details of past discussions so need some background. The pattern that should be accepted by SIMILAR TO (as defined in SQL99 part 2 clause 8.6) and the POSIX regular expressions that it accepts now are not the same. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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
[HACKERS] Open items
Here are the open items: P O S T G R E S Q L 7 . 3 O P E NI T E M S Current at ftp://candle.pha.pa.us/pub/postgresql/open_items. Source Code Changes --- Schema handling - ready? interfaces? client apps? Drop column handling - ready for all clients, apps? Fix BeOS and QNX4 ports Get bison upgrade on postgresql.org Fix vacuum btree bug (Tom) Fix client apps for autocommit = off Fix clusterdb to be schema-aware Change log_min_error_statement to be off by default Fix return tuple counts/oid/tag for rules On Hold --- Point-in-time recovery Win32 port Security audit Documentation Changes - Document need to add permissions to loaded functions and languages Move documation to gborg for moved projects -- 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] Please rename split(text,text,int) to splitpart
Bruce Momjian [EMAIL PROTECTED] writes: I am not either. How do you do the documentation when the function can be called two ways. You don't. There is only one supported name, so that's the only one you document. I guess we can give the SQL query to fix it during beta2 _and_ add a regression test to make sure it is fix. That sounds like a plan. That sounds like massive overkill. Just apply the patch. We don't need to institutionalize a regression test for this. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please rename split(text,text,int) to splitpart
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am not either. How do you do the documentation when the function can be called two ways. You don't. There is only one supported name, so that's the only one you document. I guess we can give the SQL query to fix it during beta2 _and_ add a regression test to make sure it is fix. That sounds like a plan. That sounds like massive overkill. Just apply the patch. We don't need to institutionalize a regression test for this. It would catch people who don't apply the patch. We could remove the test after 7.3. Just an idea. -- 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/users-lounge/docs/faq.html
Re: [HACKERS] 7.3beta and ecpg
Bruce Momjian [EMAIL PROTECTED] writes: We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. No? If there are bugs in it, they will break the main SQL parser, not only ecpg. I am scared. My idea of a reasonable fallback is to add prebuilt-with-the-beta-bison output files to the ecpg directory, but not anyplace else. That is ugly, but the effects of any bison problems will be limited to ecpg. I am also still wondering if we couldn't tweak the grammar to eliminate states so that ecpg would build with a standard bison. That would be a win all 'round, but it requires effort that we maybe don't have to spend. 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] 7.3beta and ecpg
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 9:10 PM To: Michael Meskes Cc: PostgreSQL Hacker; Marc G. Fournier Subject: Re: [HACKERS] 7.3beta and ecpg I think we should stop playing around with ecpg. Let's get the beta bison on postgresql.org and package the proper ecpg version for 7.3beta2. If we don't, we are going to get zero testing for 7.3 final. Marc? We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. I have a dumb idea... Why not just package the output of the Bison beta version? It may not be comprehensible, but it does not need to be generated on any particular target machine does it? Sure, it would be nice to be able to process the original grammar on any client workstation. But if it will hold up the entire project, why not just ship the preprocessed output? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.3beta and ecpg
Tom Lane dijo: Bruce Momjian [EMAIL PROTECTED] writes: We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. No? If there are bugs in it, they will break the main SQL parser, not only ecpg. I am scared. Just for the record: bison 1.49b reports lots of invalid character when processing plpgsql's grammar. However, the regression test passes. This is Linux/i686. $ make gram.c -C src/pl/plpgsql/src make: Entering directory `/home/alvherre/CVS/pgsql/src/pl/plpgsql/src' bison -y gram.y gram.y:101.24: invalid character: `,' gram.y:102.25: invalid character: `,' gram.y:104.26: invalid character: `,' gram.y:104.44: invalid character: `,' gram.y:106.24: invalid character: `,' gram.y:108.29: invalid character: `,' gram.y:108.46: invalid character: `,' gram.y:111.24: invalid character: `,' gram.y:112.22: invalid character: `,' gram.y:112.37: invalid character: `,' gram.y:117.25: invalid character: `,' gram.y:121.24: invalid character: `,' gram.y:121.36: invalid character: `,' gram.y:121.47: invalid character: `,' gram.y:122.23: invalid character: `,' gram.y:123.25: invalid character: `,' gram.y:123.34: invalid character: `,' gram.y:123.45: invalid character: `,' gram.y:123.57: invalid character: `,' gram.y:124.25: invalid character: `,' gram.y:124.43: invalid character: `,' gram.y:124.55: invalid character: `,' gram.y:125.23: invalid character: `,' gram.y:125.34: invalid character: `,' gram.y:125.47: invalid character: `,' gram.y:126.29: invalid character: `,' gram.y:126.43: invalid character: `,' gram.y:127.23: invalid character: `,' gram.y:127.35: invalid character: `,' gram.y:130.25: invalid character: `,' gram.y:134.26: invalid character: `,' -- Alvaro Herrera (alvherre[a]atentus.com) El conflicto es el camino real hacia la union ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Please rename split(text,text,int) to splitpart
Bruce Momjian wrote: Tom Lane wrote: That sounds like massive overkill. Just apply the patch. We don't need to institutionalize a regression test for this. It would catch people who don't apply the patch. We could remove the test after 7.3. Just an idea. The existing strings regression test will fail if the update patch isn't applied. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3beta and ecpg
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We will not find out if there are problems with the bison beta until we ship it as part of beta and I don't think we have to be scared of just because it is beta. No? If there are bugs in it, they will break the main SQL parser, not only ecpg. I am scared. My idea of a reasonable fallback is to add prebuilt-with-the-beta-bison output files to the ecpg directory, but not anyplace else. That is ugly, but the effects of any bison problems will be limited to ecpg. Yes, I assumed we would use the new bison only for ecpg. -- 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] Script to compute random page cost
Tom Lane wrote: Perhaps it's time to remind people that what we want to measure is the performance seen by a C program issuing write() and read() commands, transferring 8K at a time, on a regular Unix filesystem Yes...and at the risk of being accused of marketing ;-) , that is exactly what the 3 programs in my archive do (see previous post for url) : - one called 'write' creates a suitably sized data file (8k at a time - configurable), using the write() call - another called 'read' does sequential reads (8k at a time - configurable), using the read() call - finally one called 'seek' does random reads (8k chunks - configurable), using the lseek() and read() calls I tried to use code as similar as possible to how Postgres does its ioso the results *should* be meaningful ! Large file support in enabled too (as you need to use a file several times bigger than your RAM - and everyone seems to have 1G of it these days...) I think the code is reasonably readable too Its been *tested* on Linux, Freebsd, Solaris, MacosX. The only downer is that they don't automatically compute random_page_cost for you..(I was more interested in the raw sequential read, write and random read rates at the time). However it would be a fairly simple modification to combine the all 3 programs into one executable that outputs random_page_cost... regards Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
