Re: [HACKERS]
On Wed, 2002-09-11 at 05:20, Tom Lane wrote:
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.
I ran pg_dumpall from 7.3 on the 7.2 database. So I am talking about
the pg_dump that is now being beta-tested. Because of the major changes
in 7.3, the 7.2 dump is not very useful. I am *not* complaining about
7.2's pg_dump!
Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:
1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.
2. The dump produced:
CREATE TABLE cust_alloc_history (
...
year integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
3. A view was created before one of the tables to which it referred.
--
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
I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me.
Galatians 2:20
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Script to compute random page cost
On Wed, 11 Sep 2002, Mark Kirkwood wrote: 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) : Hm, it appears we've both been working on something similar. However, I've just released version 0.2 of randread, which has the following features: Written in C, uses read(2) and write(2), pretty much like postgres. Reads or writes random blocks from a specified list of files, treated as a contiguous range of blocks, again like postgres. This allows you to do random reads from the actual postgres data files for a table, if you like. You can specify the block size to use, and the number of reads to do. Allows you to specify how many blocks you want to read before you start reading again at a new random location. (The default is 1.) This allows you to model various sequential and random read mixes. If you want to do writes, I suggest you create your own set of files to write, rather than destroying postgresql data. This can easily a be done with something like this Bourne shell script: for i in 1 2 3 4; do dd if=/dev/zero of=file.$i bs=1m count=1024 done However, it doesn't calculate the random vs. sequential ratio for you; you've got to do that for yourself. E.g.,: $ ./randread -l 512 -c 256 /u/cjs/z? 256 reads of 512 x 8.00 KB blocks (4096.00 KB) totalling 131072 blocks (1024.00 MB) from 524288 blocks (4092.00 MB) in 4 files. 256 reads in 36.101119 sec. (141019 usec/read, 7 reads/sec, 29045.53 KB/sec) $ ./randread -c 4096 /u/cjs/z? 4096 reads of 1 x 8.00 KB blocks (8.00 KB) totalling 4096 blocks (32.00 MB) from 524288 blocks (4095.99 MB) in 4 files. 4096 reads in 34.274582 sec. (8367 usec/read, 120 reads/sec, 956.04 KB/sec) In this case, across 4 GB in 4 files on my 512 MB, 1.5 GHz Athlon with an IBM 7200 RPM IDE drive, I read about 30 times faster doing a full sequential read of the files than I do reading 32 MB randomly from it. But because of the size of this, there's basically no buffer cache involved. If I do this on a single 512 MB file: $ ./randread -c 4096 /u/cjs/z1:0-65536 4096 reads of 1 x 8.00 KB blocks (8.00 KB) totalling 4096 blocks (32.00 MB) from 65536 blocks (511.99 MB) in 1 files. 4096 reads in 28.064573 sec. (6851 usec/read, 146 reads/sec, 1167.59 KB/sec) $ ./randread -l 65535 -c 1 /u/cjs/z1:0-65536 1 reads of 65535 x 8.00 KB blocks (524280.00 KB) totalling 65535 blocks (511.99 MB) from 65536 blocks (0.01 MB) in 1 files. 1 reads in 17.107867 sec. (17107867 usec/read, 0 reads/sec, 30645.55 KB/sec) $ ./randread -c 4096 /u/cjs/z1:0-65536 4096 reads of 1 x 8.00 KB blocks (8.00 KB) totalling 4096 blocks (32.00 MB) from 65536 blocks (511.99 MB) in 1 files. 4096 reads in 19.413738 sec. (4739 usec/read, 215 reads/sec, 1687.88 KB/sec) Well, there you see some of the buffer cache effect from starting with about half the file in memory. If you want to see serious buffer cache action, just use the first 128 MB of my first test file: $ ./randread -c 4096 /u/cjs/z1:0-16536 4096 reads of 1 x 8.00 KB blocks (8.00 KB) totalling 4096 blocks (32.00 MB) from 16536 blocks (129.18 MB) in 1 files. 4096 reads in 20.220791 sec. (4936 usec/read, 204 reads/sec, 1620.51 KB/sec) $ ./randread -l 16535 -c 1 /u/cjs/z1:0-16536 1 reads of 16535 x 8.00 KB blocks (132280.00 KB) totalling 16535 blocks (129.18 MB) from 16536 blocks (0.01 MB) in 1 files. 1 reads in 3.469231 sec. (3469231 usec/read, 0 reads/sec, 38129.49 KB/sec) $ ./randread -l 16535 -c 64 /u/cjs/z1:0-16536 64 reads of 16535 x 8.00 KB blocks (132280.00 KB) totalling 1058240 blocks (8267.50 MB) from 16536 blocks (0.01 MB) in 1 files. 64 reads in 23.643026 sec. (369422 usec/read, 2 reads/sec, 358072.59 KB/sec) For those last three, we're basically limited completely by the CPU, as there's not much disk I/O going on at all. The many-block one is going to be slower because it's got to generate a lot more random numbers and do a lot more lseek operations. Anyway, looking at the real difference between truly sequential and truly random reads on a large amount of data file (30:1 or so), it looks to me that people getting much less than that are getting good work out of their buffer cache. You've got to wonder if there's some way to auto-tune for this sort of thing Anyway, feel free to download and play. If you want to work on the program, I'm happy to give developer access on sourceforge. http://sourceforge.net/project/showfiles.php?group_id=55994 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]
-Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 07:29 To: Tom Lane Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette Cisneros; [EMAIL PROTECTED] Subject: Re: [HACKERS] Let me reiterate. I got these problems dumping 7.2 data with 7.3's pg_dumpall: I wonder how many people would do something more like: pg_dumpall db.sql make install psql -e template1 db.sql rather than manually installing pg_dumpall from 7.3 first? Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Script to compute random page cost
AMD Athlon 500 512MB Ram IBM 120GB IDE Tested with: BLCKSZ=8192 TESTCYCLES=50 Result: Collecting sizing information ... Running random access timing test ... Running sequential access timing test ... Running null loop timing test ... random test: 2541 sequential test: 2455 null timing test: 2389 random_page_cost = 2.303030 Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(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
Curt Sampson wrote: On Wed, 11 Sep 2002, Mark Kirkwood wrote: Hm, it appears we've both been working on something similar. However, I've just released version 0.2 of randread, which has the following features: funny how often that happens...( I think its often worth the effort to write your own benchmarking / measurement tool in order to gain an good understanding of what you intend to measure) Anyway, feel free to download and play. If you want to work on the program, I'm happy to give developer access on sourceforge. http://sourceforge.net/project/showfiles.php?group_id=55994 I'll take a look. best wishes Mark ---(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
On Wed, Sep 11, 2002 at 12:45:06AM -0400, Tom Lane wrote: No? If there are bugs in it, they will break the main SQL parser, not only ecpg. I am scared. Actually there is one more problem. The backend introduced the EXECUTE command just recently. However, this clashes with the embedded SQL EXECUTE command. Since both may be called just with EXECUTE name, there is no way to distinguish them. I have no idea if there's a standard about execution of a plan but couldn't/shouldn't it be named EXECUTE PLAN instead of just EXECUTE? 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. Actually I think it will need quite some effort, in particular since I stay away from the backend grammar as much as possible. Once I change the backend compatible part of the grammar I either have to make the same changes to the backends parser or ecpg will soon be unmaintainable. 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
Re: [HACKERS] 7.3beta and ecpg
On Wed, Sep 11, 2002 at 12:56:59AM -0400, Alvaro Herrera wrote: 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: `,' No big deal. Just remove all the ','. The new bison does not like them as seperators anymore. We will have to make that change in the near future anyway. 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] Proposal: Solving the Return proper effected tuple count from complex commands [return] issue
On Sun, 8 Sep 2002 19:50:21 -0300, Steve Howe [EMAIL PROTECTED] wrote: Proposal #1 (author: Steve Howe): - PQcmdStatus() == Should return the last executed command #1a or the same as the original command #1b = #2 PQcmdTuples() == should return the sum of modified rows of all commands executed by the rule (DELETE / INSERT / UPDATE). = #2c PQoidValue() == should return the value for the last INSERT executed command in the rule (if any). Proposal #2 (author: Tom lane): --- PQcmdStatus() == Should always return the same command type original submitted by the client. PQcmdTuples() == If no INSTEAD rule, return same output as for original command, ignoring other commands in the rule.If there is INSTEAD rules, use result of last command in the rewritten series, #2a use result of last command of same type as original command #2b or sum up the results of all the rewritten commands. #2c PQoidValue() == If the original command was not INSERT, return 0. otherwise, if one INSERT, return it's original PQoidValue(). If more then one INSERT command applied, use last #2A or other possibilities #2B; one of these possibilities is: return 0 (#2C). On Sun, 8 Sep 2002 21:52:45 -0400 (EDT), Bruce Momjian [EMAIL PROTECTED] wrote: :So, to summarize #2, we have: : : if no INSTEAD, : return value of original command : : if INSTEAD, : return tag of original command : return sum of all affected rows with the same tag this is a new interpretation: #2d : return OID if all INSERTs in the rule insert only one row, else zero this is #2C Proposal #3 (author: Steve Howe): - Another possibility (which does not go against the other proposals but extends them) would be returning a stack of all commands executed and returning it on new functions, whose extend the primary's functionality; let's say these new functions are called PQcmdStatusEx(), PQcmdTuplesEx() and PQoidValueEx(). Proposal #4 (author: Hiroshi Inoue): Hiroshi's proposal consist in a makeshift solution as stated on http://archives.postgresql.org/pgsql-general/2002-05/msg00170.php. Please refer to that thread for details. Proposal #5: On Sun, 08 Sep 2002 19:54:45 -0700, Joe Conway [EMAIL PROTECTED] wrote: : if no INSTEAD, : return value of original command : : if INSTEAD, : return tag MUTATED : return sum of sum of tuple counts of all replacement commands this equals #2c : return OID if sum of all replacement INSERTs in the rule inserted : only one row, else zero this is #2C On Mon, 9 Sep 2002 20:41:41 +0200 (CEST), Peter Eisentraut [EMAIL PROTECTED] wrote: :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. ISTM this is one of those problems where there is no generic solution. Whatever you implement, it is easy to come up with an example that shows that the implementation is broken (for a suitable definition of broken), because there are so many different ways to use this feature. Here is just another bad idea: As it is impossible to *guess* the correct behaviour, let the dba *define* what he wants. There is no CREATE RULE statement in SQL92, so we can't break any standard by changing its syntax. CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ INSTEAD ] action where action can be: NOTHING | rulequery | ( rulequery; rulequery ... ) where rulequery is: [ COUNT ] query (or any other keyword instead of COUNT) Proposal #6: If no INSTEAD, return value of original command (this is compatible to #2), else ... PQcmdStatus() == Always return tag of original command (this equals #2). PQcmdTuples() == Sum up the results of all the rewritten commands marked as COUNTed. PQoidValue() == If the original command was not INSERT, return 0. otherwise, if all COUNTed rewritten INSERTs insert exactly one row, then return its OID, else 0. Proposal #7 (a variation of #6): If no INSTEAD, treat the original command the same as a COUNTed rewritten command. +/- for both #6 and #7 Pro: Regarding PQcmdTuples this can emulate #1 and all variants of #2. Con: need to store COUNTed flag for rule queries == catalog change == initdb == not for 7.3 (except we can
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wednesday 11 September 2002 04:58 am, Stephan Szabo wrote: 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? Function in select list mustn't change any data. What if function change data in from clause ? ---(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
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? Function in select list mustn't change any data. What if function change data in from clause ? Why can't the function change data? I've done this one a number of times through views to log the user pulling out information from the system, and what it was at the time (time sensitive data). -- Rod Taylor ---(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 03:14 am, Stephan Szabo wrote: 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. If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and 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)) 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. Question ? All select in one transaction return same data - no matter if any change and commit data ? 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. Can I find sql99 spec anywhere ? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 04:58 am, Stephan Szabo wrote: 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? Function in select list mustn't change any data. What if function change data in from clause ? There is no such restriction. The behavior is not necessarily well defined in all cases, but postgresql certainly doesn't require that the functions not change data especially given that postgresql takes: select func(); as the way to call to func(); Example session from 7.3 just pre-beta included below. sszabo=# create table b(a int); CREATE TABLE sszabo=# create table a(a int); CREATE TABLE sszabo=# create function f(int) returns int as 'insert into b values ($1); select $1;' language 'sql'; CREATE FUNCTION sszabo=# insert into a values (1); INSERT 17010 1 sszabo=# select f(a) from a; f --- 1 (1 row) sszabo=# select * from b; a --- 1 (1 row) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wednesday 11 September 2002 02:38 pm, Rod Taylor wrote: 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? Function in select list mustn't change any data. What if function change data in from clause ? Why can't the function change data? I've done this one a number of times through views to log the user pulling out information from the system, and what it was at the time (time sensitive data). Scenario : Func change data in table in form clause I fetch 3 (after row 1 and 2) and then change row 1 What result expect ? ---(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 Wednesday 11 September 2002 03:14 am, Stephan Szabo wrote: 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. If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and nothing happen Are you saying that it's okay for: insert into nonexistant values (3); and insert into existant values (3); where 3 is invalid for existant to work differently? I think that'd be tough to get past some people, but you might want to write a proposal for why it should act that way. (Don't expect anything for 7.3, but 7.4's devel will start sometime.) 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. Question ? All select in one transaction return same data - no matter if any change and commit data ? It depends on the isolation level of the transaction I believe. This sequence in read committed (in postgresql) and serializable give different results. T1: begin; T1: select * from a; T2: begin; T2: insert into a values (3); T2: commit; T1: select * from a; In serializable mode, you can't get non-repeatable read effects: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value of discover that the row has been deleted. ---(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 Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 02:38 pm, Rod Taylor wrote: 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? Function in select list mustn't change any data. What if function change data in from clause ? Why can't the function change data? I've done this one a number of times through views to log the user pulling out information from the system, and what it was at the time (time sensitive data). Scenario : Func change data in table in form clause I fetch 3 (after row 1 and 2) and then change row 1 What result expect ? Just because the behavior is sometimes undefined by the spec doesn't mean the construct should be disallowed. Grouped character string columns also could have implementation-dependent behavior (which never needs to be specified), but we don't disallow that either. ---(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 02:55 pm, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 03:14 am, Stephan Szabo wrote: 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. If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and nothing happen Are you saying that it's okay for: insert into nonexistant values (3); and insert into existant values (3); where 3 is invalid for existant to work differently? I think that'd be tough to get past some people, but you might want to write a proposal for why it should act that way. (Don't expect anything for 7.3, but 7.4's devel will start sometime.) I don't understand all, but when I tell 'error' I think syntax error If error is contraint error again nothin change, only error return 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. Question ? All select in one transaction return same data - no matter if any change and commit data ? It depends on the isolation level of the transaction I believe. This sequence in read committed (in postgresql) and serializable give different results. T1: begin; T1: select * from a; T2: begin; T2: insert into a values (3); T2: commit; T1: select * from a; In serializable mode, you can't get non-repeatable read effects: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value of discover that the row has been deleted. If serialization strict connect with transaction then ok. haris peco ---(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
On Wed, Sep 11, 2002 at 11:23:45AM +0200, Zeugswetter Andreas SB SD wrote: I know this is not really related, but wouldn't the plan be to make ecpg actually use the backend side execute ... now that it is available ? Maybe I misunderstood something. Do you mean I could use the backend PREPARE/EXECUTE to prepare and execute any statement I can PREPARE/EXECUTE with the ecpg part? Can I use PREPARE to prepare a cursor? In that case I will gladly remove the ecpg stuff. I just looked into the backend any further and wonder why I didn't understand earlier. For some reason I was believing this was just an optimization command. It seems I can use larger parts of this thus reducing ecpg parser's complexity as well. ecpg needs eighter 'execute :idvar' or 'execute id', so either idvar is a declared variable or id a statement id. I don't know if that is something a parser can check though :-( Actually ecpg needs 'execute id using ... into ...'. I did not see any mention of using in the backend execute command. The 'execute :idvar' part is easier since this correctly is named 'execute immediate :idvar' I think. AFAIK the standard is execute ID using value and not execute ID(value). Please correct me if I'm wrong, but right now ecpg uses the first syntax the backend uses the second. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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
I know this is not really related, but wouldn't the plan be to make ecpg actually use the backend side execute ... now that it is available ? Maybe I misunderstood something. Do you mean I could use the backend PREPARE/EXECUTE to prepare and execute any statement I can PREPARE/EXECUTE with the ecpg part? Can I use PREPARE to prepare a cursor? In that case I will gladly remove the ecpg stuff. That is how I understood it so far. I just looked into the backend any further and wonder why I didn't understand earlier. For some reason I was believing this was just an optimization command. Well, yes and no. For programs the reuse a prepared statement it is good, for those that only use it once it can be a loss. Simple tests in prev posts to this list showed, that with longer data cstrings the parser was so slow, that prepare + execute actually sped up the overall exec time. (At least that was my interpretation) It seems I can use larger parts of this thus reducing ecpg parser's complexity as well. Hopefully :-) ecpg needs eighter 'execute :idvar' or 'execute id', so either idvar is a declared variable or id a statement id. I don't know if that is something a parser can check though :-( Actually ecpg needs 'execute id using ... into ...'. I did not see any mention of using in the backend execute command. The 'execute :idvar' part is easier since this correctly is named 'execute immediate :idvar' I think. The using clause is optional, I just left it out. My ESQL/C precompiler can also use an id variable for execute :idvar using That is actually how we use esql/c here. AFAIK the standard is execute ID using value and not execute ID(value). Please correct me if I'm wrong, but right now ecpg uses the first syntax the backend uses the second. I think it should be the intention to keep those identical, which would mean, that the backend syntax is currently wrong :-( 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]
Oliver Elphick [EMAIL PROTECTED] writes:
Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:
1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.
Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.
2. The dump produced:
CREATE TABLE cust_alloc_history (
...
year integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
Hmm ... what was the original coding of the default?
3. A view was created before one of the tables to which it referred.
This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS]
On Wed, 2002-09-11 at 14:59, Tom Lane wrote:
Oliver Elphick [EMAIL PROTECTED] writes:
Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:
1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.
Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.
In 7.2, this will identify the functions that need to be dumped as
language handlers:
junk=# SELECT p.proname
junk-# FROM pg_proc AS p, pg_language AS l
junk-# WHERE l.lanplcallfoid = p.oid AND l.lanplcallfoid != 0;
proname
--
plperl_call_handler
plpgsql_call_handler
pltcl_call_handler
(3 rows)
2. The dump produced:
CREATE TABLE cust_alloc_history (
...
year integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
Hmm ... what was the original coding of the default?
year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)
3. A view was created before one of the tables to which it referred.
This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.
Is it not enough to defer all views until the end? Why would they be
needed any sooner?
--
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
I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me.
Galatians 2:20
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.2 - 7.3 activity
On 05 Sep 2002 20:27:23 +0500, Hannu Krosing [EMAIL PROTECTED] wrote: Has anyone run any speed tests to see how 7.2 and 7.3 compare ? Running a modified OSDB (CREATE TABLE ... WITHOUT OIDS) with 400 MB data on a Pentium III 1 GHz, 382 MB RAM, 7200 rpm IBM 14 GB HD under Linux, this is what I got so far: Testname Wo8 Old8wo721b Nr17 18 19 Test MB 400 400 400 System mem 382 382 382 Tuple header smalllargelarge WITH / WITHOUT OIDS WITHOUT WITHOUT WITHOUT (populate + single user) Elapsed hh:mm:ss05:04:36 06:54:18 06:27:26 User mm:ss.00 00:19.32 00:19.61 00:17.72 System mm:ss.00 00:15.97 00:17.37 00:15.90 Xlog...5B...5B...5A Size KB 1,038,5641,070,6561,069,652 CTIME postmaster mmm:ss 284:22 391:44 363:09 Updates 2,0092,0092,009 VAC, ANA VAC, ANA VAC, ANA *1 (multi user) *2 Elapsed hh:mm:ss31:34:17 51:33:54 User mm:ss.00 130:31.22222:08.98 System mm:ss.00 92:59.18159:24.81 Xlog5B...1,8F5B...2,21 Size KB 1,143,0801,193,536 CTIME postmaster mmm:ss 1640:00 2680:00 Updates 243,390 341,233 create_tables() 0.08 0.08 0.06 load() 633.30 681.91 725.79 create_idx_uniques_key_bt()320.90 344.45 305.63 create_idx_updates_key_bt()321.23 351.97 327.52 create_idx_hundred_key_bt()319.26 349.17 327.87 create_idx_tenpct_key_bt() 318.78 349.05 326.82 create_idx_tenpct_key_code_bt() 65.4094.3470.69 create_idx_tiny_key_bt() 3.15 0.10 4.69 create_idx_tenpct_int_bt() 23.4427.0421.60 create_idx_tenpct_signed_bt() 25.1625.8125.45 create_idx_uniques_code_h()118.48 138.47 122.57 create_idx_tenpct_double_bt() 32.0329.7829.49 create_idx_updates_decim_bt() 130.92 149.37 136.27 create_idx_tenpct_float_bt()28.7129.6628.88 create_idx_updates_int_bt() 55.0562.6256.90 create_idx_tenpct_decim_bt()52.1454.0552.41 create_idx_hundred_code_h()116.09 136.30 122.34 create_idx_tenpct_name_h() 40.9142.9439.28 create_idx_updates_code_h() 73.5481.8075.48 create_idx_tenpct_code_h() 36.5137.9936.17 create_idx_updates_double_bt() 64.0271.1867.72 create_idx_hundred_foreign() 135.44 140.54 131.18 Sum 2,914.54 3,198.62 3,034.81 populateDataBase() 2,914.69 3,195.71 3,034.89 sel_1_cl() 0.09 0.07 0.08 join_3_cl() 0.10 0.10 0.10 sel_100_ncl()2.60 2.62 2.53 table_scan()36.7241.3237.74 agg_func() 100.06 137.39 113.70 agg_scal() 37.9341.6837.69 sel_100_cl() 2.5929.53 2.54 join_3_ncl() 231.39 234.77 239.32 sel_10pct_ncl() 51.5020.68 133.47 agg_simple_report() 8,734.7614,222.0713,132.75 agg_info_retrieval()46.03 133.41 131.11 agg_create_view()0.69 0.67 0.47 agg_subtotal_report() 98.67 146.6987.07 agg_total_report() 94.19 132.59 120.86 join_2_cl() 0.12 0.11 0.08 join_2()96.67 108.61 101.16 sel_variable_select_low() 21.9235.7520.35 sel_variable_select_high() 30.1229.5728.33 join_4_cl() 0.02 0.01 0.01 proj_100() 100.81 144.07 114.83 join_4_ncl() 282.74 368.88 315.62 proj_10pct() 109.96 144.27 124.76 sel_1_ncl() 0.14 0.09 0.07 join_2_ncl()94.76 113.95 105.70 integrity_test() 5.61 6.00 5.60 drop_updates_keys() 0.38 0.36 0.48 bulk_save() 0.25 0.30 0.26 bulk_modify()2,464.31 2,647.28 2,552.16
Re: [HACKERS] problem with new autocommit config parameter and jdbc
On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and nothing happen Are you saying that it's okay for: insert into nonexistant values (3); and insert into existant values (3); where 3 is invalid for existant to work differently? I think that'd be tough to get past some people, but you might want to write a proposal for why it should act that way. (Don't expect anything for 7.3, but 7.4's devel will start sometime.) I don't understand all, but when I tell 'error' I think syntax error If error is contraint error again nothin change, only error return I don't understand what you mean here. Are you saying that both of those queries should not start transactions? Then that wouldn't be starting between the parser and execute since you won't know that the row violates a constraint until execution time. 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. Question ? All select in one transaction return same data - no matter if any change and commit data ? It depends on the isolation level of the transaction I believe. This sequence in read committed (in postgresql) and serializable give different results. T1: begin; T1: select * from a; T2: begin; T2: insert into a values (3); T2: commit; T1: select * from a; In serializable mode, you can't get non-repeatable read effects: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value of discover that the row has been deleted. If serialization strict connect with transaction then ok. I again am not sure I understand, are you saying that under serializable select should start a transaction but it shouldn't under read committed? That seems like a bad idea to me, either it should or it shouldn't in my opinion. Perhaps it'd be better if you wrote up what you think it should do in all these cases and then we could look at them as a whole. (Cases I can see right now are, select under serializable, select under read committed, garbage command, select to non existant table, insert to non existant table, insert that fails due to unique constraint, insert that fails due to exception raised by a before trigger, insert that fails due to exception raised by an after trigger, insert that does nothing due to before trigger, update that fails due to any of those after some rows have already successfully been modified and probably some others). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] New pgaccess
I wanted people to see a screen shot of the new pgaccess to be releases with 7.3: ftp://candle.pha.pa.us/pub/postgresql/pgaccess.gif It looks amazing. The main pgaccess page is: http://www.pgaccess.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Wed, 2002-09-11 at 08:20, Dave Page wrote: -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 07:29 To: Tom Lane Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette Cisneros; [EMAIL PROTECTED] Subject: Re: [HACKERS] Let me reiterate. I got these problems dumping 7.2 data with 7.3's pg_dumpall: I wonder how many people would do something more like: pg_dumpall db.sql make install psql -e template1 db.sql rather than manually installing pg_dumpall from 7.3 first? I suppose that what people will do unless told otherwise, but the introduction of schemas means that it is much better to use 7.3's dump, otherwise, for example, all functions will be private rather than public. Perhaps a note should be added to INSTALL. At the moment it says: 2. To dump your database installation, type: pg_dumpall outputfile ... Make sure that you use the pg_dumpall command from the version you are currently running. 7.2's pg_dumpall should not be used on older databases. But now we should be telling people to use 7.3's pg_dumpall, at least for 7.2 data. (How far back can it go?) Make sure you use pg_dumpall from the new 7.3 software to dump your data from 7.2. To do this, you must have the 7.2 postmaster running and run the 7.3 pg_dumpall by using its full pathname. 7.2's pg_dumpall is unsuitable because of the introduction of schemas in 7.3 which make it necessary to grant public access to features that will, if created from a 7.2 dump, be given access by their owner only. (Have I got that right?) -- 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 I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
Oliver Elphick wrote: But now we should be telling people to use 7.3's pg_dumpall, at least for 7.2 data. (How far back can it go?) Make sure you use pg_dumpall from the new 7.3 software to dump your data from 7.2. To do this, you must have the 7.2 postmaster running and run the 7.3 pg_dumpall by using its full pathname. 7.2's pg_dumpall is unsuitable because of the introduction of schemas in 7.3 which make it necessary to grant public access to features that will, if created from a 7.2 dump, be given access by their owner only. That's a pretty big hurtle. I think we are better off giving them an SQL UPDATE to run. -- 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] 7.3beta and ecpg
On Wed, Sep 11, 2002 at 03:42:44PM +0200, Zeugswetter Andreas SB SD wrote: That is how I understood it so far. I will dig into this as soon as I find time, i.e. definitely for 7.3. Actually ecpg needs 'execute id using ... into ...'. I did not see any mention of using in the backend execute command. The 'execute :idvar' part is easier since this correctly is named 'execute immediate :idvar' I think. The using clause is optional, I just left it out. My ESQL/C precompiler Correct, using is optional with ecpg as well. can also use an id variable for execute :idvar using That is actually how we use esql/c here. And how we used Pro*C when I was still working with Oracle. AFAIK the standard is execute ID using value and not execute ID(value). Please correct me if I'm wrong, but right now ecpg uses the first syntax the backend uses the second. I think it should be the intention to keep those identical, which would mean, that the backend syntax is currently wrong :-( Which of course means we should change it. :-) Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] SIMILAR TO
Bruce Momjian writes: Is this a TODO? It's a must-fix for 7.3, but frankly I don't see how we could justify making the required extensive changes during beta. I suggest that we keep the parser support and throw an error when it's invoked. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS]
Is it not enough to defer all views until the end? Why would they be needed any sooner? I would think that views of views, or permissions on views, or prepared statements might need the right view to be declared first. There may be other examples as well. Your solution might be better than the current situation, however. Regards, Jeff ---(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
Jan Wieck writes: 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. Maybe we should return something to the effect of unknown, but something happened. I can see that returning 0 in case of doubt might confuse applications. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS]
yes, deferring views to the end will also break if you have SQL functions defined that use views. The dependencies is (are?) a really hard problem. elein At 12:41 PM 9/11/02, Jeff Davis wrote: Is it not enough to defer all views until the end? Why would they be needed any sooner? I would think that views of views, or permissions on views, or prepared statements might need the right view to be declared first. There may be other examples as well. Your solution might be better than the current situation, however. Regards, Jeff ---(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 :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: [EMAIL PROTECTED] (510)543-6079 Taking a Trip. Not taking a Trip. --anonymous :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS]
Oliver Elphick [EMAIL PROTECTED] writes:
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
Hmm ... what was the original coding of the default?
year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)
Well, date_part has always yielded double, so what we are really looking
at here is a side-effect of the tightening of implicit casting in 7.3.
It wants you to cast down to integer explicitly.
There was some discussion of allowing implicit explicit casting of
INSERT and UPDATE values to the target column's datatype, ie, allow a
cast path to be used even if it is not marked as implicitly castable.
If we did that then it's be reasonable to do it for default values as
well, and that would allow this coding to keep working. But we did not
have a consensus to do it AFAIR.
3. A view was created before one of the tables to which it referred.
This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.
Is it not enough to defer all views until the end? Why would they be
needed any sooner?
Well, one counterexample is where the view is being used as a substitute
for a standalone composite type: there might be a function somewhere
that uses the view's rowtype as an input or result datatype. I recall
seeing exactly that coding technique in some of Joe Conway's contrib
stuff (though it's now been superseded by use of standalone types).
In any case, such a rule won't ensure getting cross-references between
views to work.
The only real solution to pg_dump's ordering woes is to examine the
database dependency graph and do a topological sort to determine a
safe dump order. As of 7.3 we have the raw materials to do this (in
the form of the pg_depend system table), but making pg_dump actually
do it is a major rewrite that didn't get done, and IMHO shouldn't be
tackled during beta. (I sure want to see it for 7.4 though.)
In the meantime, I think that we shouldn't mess with pg_dump's basically
OID-order-driven dump ordering. It works in normal cases, and adding
arbitrary rules to it to fix one corner case is likely to accomplish
little except breaking other corner cases.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3beta and ecpg
Michael Meskes [EMAIL PROTECTED] writes: On Wed, Sep 11, 2002 at 03:42:44PM +0200, Zeugswetter Andreas SB SD wrote: I think it should be the intention to keep those identical, which would mean, that the backend syntax is currently wrong :-( Which of course means we should change it. :-) IIRC, the conclusion of our earlier debate about backend PREPARE/EXECUTE syntax was that since it was not implementing exactly the behavior specified for embedded SQL (and couldn't, not being an embedded operation) it would be better to deliberately avoid using exactly the same syntax. See thread starting at http://archives.postgresql.org/pgsql-hackers/2002-07/msg00814.php We can revisit that decision if you like, but you must convince us that it was wrong, not just say of course we should change it. 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
On Wednesday 11 September 2002 06:11 pm, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote: On Wed, 11 Sep 2002, snpe wrote: If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and nothing happen Are you saying that it's okay for: insert into nonexistant values (3); and insert into existant values (3); where 3 is invalid for existant to work differently? I think that'd be tough to get past some people, but you might want to write a proposal for why it should act that way. (Don't expect anything for 7.3, but 7.4's devel will start sometime.) I don't understand all, but when I tell 'error' I think syntax error If error is contraint error again nothin change, only error return I don't understand what you mean here. Are you saying that both of those queries should not start transactions? Then that wouldn't be starting between the parser and execute since you won't know that the row violates a constraint until execution time. 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. Question ? All select in one transaction return same data - no matter if any change and commit data ? It depends on the isolation level of the transaction I believe. This sequence in read committed (in postgresql) and serializable give different results. T1: begin; T1: select * from a; T2: begin; T2: insert into a values (3); T2: commit; T1: select * from a; In serializable mode, you can't get non-repeatable read effects: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value of discover that the row has been deleted. If serialization strict connect with transaction then ok. I again am not sure I understand, are you saying that under serializable select should start a transaction but it shouldn't under read committed? That seems like a bad idea to me, either it should or it shouldn't in my opinion. Perhaps it'd be better if you wrote up what you think it should do in all these cases and then we could look at them as a whole. (Cases I can see right now are, select under serializable, select under read committed, garbage command, select to non existant table, insert to non existant table, insert that fails due to unique constraint, insert that fails due to exception raised by a before trigger, insert that fails due to exception raised by an after trigger, insert that does nothing due to before trigger, update that fails due to any of those after some rows have already successfully been modified and probably some others). One question first ? What mean ? ERROR: current transaction is aborted, queries ignored until end of transaction block I am tried next (autocommit=true in postgresql.conf) 1. begin; 2. select * from tab; query work 3. show t; -- force stupid syntax error 4. select * from tab; ERROR: current transaction is aborted, queries ignored until end of transaction block 5.end; 6. select * from tab; query work I must rollback or commit transaction when I make stupid syntax error. This is same with autocommit=false It is maybe ok, I don't know. For rest is ok (if level serializable select start transaction) Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3beta and ecpg
We can revisit that decision if you like, but you must convince us that it was wrong, not just say of course we should change it. I am sorry, but at that time I did not have time for the discussion, and now is also very tight for me :-( Four reasons I can give: 1. execute xx(...); looks like xx is a procedure which it definitely is not. 2. imho ecpg should use the backend side feature and thus the syntax should be the same. iirc the syntax was chosen to separate it from esql, but if it gets to be the same why separate it ? 3. I think a close comparison is possible for dynamically prepared statements where you don't directly use host variables in the statement, but placeholders (?). 4. we did use the esql standard for declare cursor, why not now ? Are the () mandatory for the backend side feature ? If yes, it would at least be possible to differentiate ecpg from it. Actually exec sql execute is only for statements not returning a result set (e.g. update). selects would need 'declare curid cursor for ...' and fetch, but that would imho be an improvement because you can then choose a named portal. Andreas ---(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 06:11 pm, Stephan Szabo wrote: I again am not sure I understand, are you saying that under serializable select should start a transaction but it shouldn't under read committed? That seems like a bad idea to me, either it should or it shouldn't in my opinion. Perhaps it'd be better if you wrote up what you think it should do in all these cases and then we could look at them as a whole. (Cases I can see right now are, select under serializable, select under read committed, garbage command, select to non existant table, insert to non existant table, insert that fails due to unique constraint, insert that fails due to exception raised by a before trigger, insert that fails due to exception raised by an after trigger, insert that does nothing due to before trigger, update that fails due to any of those after some rows have already successfully been modified and probably some others). One question first ? What mean ? ERROR: current transaction is aborted, queries ignored until end of transaction block I am tried next (autocommit=true in postgresql.conf) The transaction has encountered an unrecoverable error (remember, all errors are currently considered unrecoverable) and the transaction is in a potentially unsafe state. 1. begin; 2. select * from tab; query work 3. show t; -- force stupid syntax error 4. select * from tab; ERROR: current transaction is aborted, queries ignored until end of transaction block 5.end; 6. select * from tab; query work I must rollback or commit transaction when I make stupid syntax error. Note that even with end you get effectively a rollback in this case since the transaction as a whole ended in an error state. This is same with autocommit=false It is maybe ok, I don't know. Well, at least until we have savepoints or nested transactions, there's only a limited amount of freedom in the implementation. For rest is ok (if level serializable select start transaction) Like I said above, having the transaction starting of select being dependent on the isolation level variable sounds like a bad idea. In addition that still doesn't deal with select statements with side effects. ---(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]
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 18:21 To: Oliver Elphick Cc: Dave Page; Tom Lane; Lamar Owen; Philip Warner; Laurette Cisneros; [EMAIL PROTECTED] Subject: Re: [HACKERS] Oliver Elphick wrote: But now we should be telling people to use 7.3's pg_dumpall, at least for 7.2 data. (How far back can it go?) Make sure you use pg_dumpall from the new 7.3 software to dump your data from 7.2. To do this, you must have the 7.2 postmaster running and run the 7.3 pg_dumpall by using its full pathname. 7.2's pg_dumpall is unsuitable because of the introduction of schemas in 7.3 which make it necessary to grant public access to features that will, if created from a 7.2 dump, be given access by their owner only. That's a pretty big hurtle. I think we are better off giving them an SQL UPDATE to run. How would that massage a dump file though? I can't think of any SQL that might make 7.2 output 'language_handler' correctly, and we already know 7.3 will barf on opaque. Regards, Dave. ---(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] New pgaccess
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 17:38 To: PostgreSQL-development Cc: [EMAIL PROTECTED] Subject: [HACKERS] New pgaccess I wanted people to see a screen shot of the new pgaccess to be releases with 7.3: ftp://candle.pha.pa.us/pub/postgresql/pgaccess.gif It looks amazing. It looks very similar to pgAdmin :-) Regards, Dave. ---(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]
Dave Page wrote: That's a pretty big hurtle. I think we are better off giving them an SQL UPDATE to run. How would that massage a dump file though? I can't think of any SQL that might make 7.2 output 'language_handler' correctly, and we already know 7.3 will barf on opaque. Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? -- 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]
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 22:13 To: Dave Page Cc: Oliver Elphick; [EMAIL PROTECTED] Subject: Re: [HACKERS] Dave Page wrote: That's a pretty big hurtle. I think we are better off giving them an SQL UPDATE to run. How would that massage a dump file though? I can't think of any SQL that might make 7.2 output 'language_handler' correctly, and we already know 7.3 will barf on opaque. Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? I guess so. It seems to me that upgrading to 7.3 is going to be the stuff of nightmares, so my first thought is to try to avoid getting people to run a 7.3 utility on their 7.x database. It would be nice to see such a script run on old version dump files - but what else will break? Oliver has found a couple of things, and I wouldn't be surprised if my main installation falls over as well. If I get a chance I'll try it tomorrow. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS]
Dave Page wrote: Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? I guess so. It seems to me that upgrading to 7.3 is going to be the stuff of nightmares, so my first thought is to try to avoid getting people to run a 7.3 utility on their 7.x database. It would be nice to see such a script run on old version dump files - but what else will break? Oliver has found a couple of things, and I wouldn't be surprised if my main installation falls over as well. If I get a chance I'll try it tomorrow. Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? -- 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]
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 22:28 To: Dave Page Cc: Oliver Elphick; [EMAIL PROTECTED] Subject: Re: [HACKERS] Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? I can see that working for the opaque/language_handler thing, but would/should it work for tweaking casts that are no longer implicit? Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] - pg_dump issues
On Wed, 2002-09-11 at 21:19, Tom Lane wrote: In the meantime, I think that we shouldn't mess with pg_dump's basically OID-order-driven dump ordering. It works in normal cases, and adding arbitrary rules to it to fix one corner case is likely to accomplish little except breaking other corner cases. I can see that Lamar and I are going to have major problems dealing with users who fall over these problems. There are some things that simply cannot be handled automatically, such as user-written functions that return opaque. Then there are issues of ordering; and finally the fact that we need to use the new pg_dump with the old binaries to get a useful dump. It seems to me that I shall have to make the new package such that it can exist alongside the old one for a time, or else possibly separate 7.3 pg_dump and pg_dumpall into a separate package. It is going to be a total pain! -- 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 I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] timestamp column default changed?
If you define a column as: coltimestamp In 7.2.x didn't it default to timestamp with timezone? And now in 7.3(b1) it defaults to timestamp without timezone? Is this right? -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS]
On Wed, 2002-09-11 at 22:27, Bruce Momjian wrote: Dave Page wrote: Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? I guess so. It seems to me that upgrading to 7.3 is going to be the stuff of nightmares, so my first thought is to try to avoid getting people to run a 7.3 utility on their 7.x database. It would be nice to see such a script run on old version dump files - but what else will break? Oliver has found a couple of things, and I wouldn't be surprised if my main installation falls over as well. If I get a chance I'll try it tomorrow. Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? Surely you will have to leave the remapping in for the benefit of anyone who jumps from = 7.2 to = 7.4 -- 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 I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(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]
Oliver Elphick wrote: On Wed, 2002-09-11 at 22:27, Bruce Momjian wrote: Dave Page wrote: Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? I guess so. It seems to me that upgrading to 7.3 is going to be the stuff of nightmares, so my first thought is to try to avoid getting people to run a 7.3 utility on their 7.x database. It would be nice to see such a script run on old version dump files - but what else will break? Oliver has found a couple of things, and I wouldn't be surprised if my main installation falls over as well. If I get a chance I'll try it tomorrow. Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? Surely you will have to leave the remapping in for the benefit of anyone who jumps from = 7.2 to = 7.4 Well, our whole goal was to get rid of the opaque thing entirely so I am not sure if we want to keep that going. In fact, I am not sure it is even possible to remap opaque because it now is represented by so many other values. -- 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] timestamp column default changed?
Laurette Cisneros wrote: If you define a column as: coltimestamp In 7.2.x didn't it default to timestamp with timezone? And now in 7.3(b1) it defaults to timestamp without timezone? /HISTORY says right at the top: * TIMESTAMP and TIME data types now default to WITHOUT TIMEZONE -- 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] timestamp column default changed?
I'm sure you all have discussed this ad-nauseum but this sure does create a pain in the butt when converting. Ok, I had my say. Thanks for all your hard work, L. On Wed, 11 Sep 2002, Bruce Momjian wrote: Laurette Cisneros wrote: If you define a column as: coltimestamp In 7.2.x didn't it default to timestamp with timezone? And now in 7.3(b1) it defaults to timestamp without timezone? /HISTORY says right at the top: * TIMESTAMP and TIME data types now default to WITHOUT TIMEZONE -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timestamp column default changed?
I think the SQL standards required the change. --- Laurette Cisneros wrote: I'm sure you all have discussed this ad-nauseum but this sure does create a pain in the butt when converting. Ok, I had my say. Thanks for all your hard work, L. On Wed, 11 Sep 2002, Bruce Momjian wrote: Laurette Cisneros wrote: If you define a column as: coltimestamp In 7.2.x didn't it default to timestamp with timezone? And now in 7.3(b1) it defaults to timestamp without timezone? /HISTORY says right at the top: * TIMESTAMP and TIME data types now default to WITHOUT TIMEZONE -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... -- 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]
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 22:28 To: Dave Page Cc: Oliver Elphick; [EMAIL PROTECTED] Subject: Re: [HACKERS] Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? I can see that working for the opaque/language_handler thing, but would/should it work for tweaking casts that are no longer implicit? OK, I am going to add these items to the open items list because I am having trouble keeping track of all the compatibility changes for pg_dump. I have: Loading 7.2 pg_dumps opaque language handler no longer recognized What else is there? Do cast problems related to pg_dump loading or to working with the data after the load? Is it casts in user functions? -- 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] pg_dump problems in upgrading
At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote: 3. A view is being created before one of the tables it refers to. Should not views be created only at the very end? This would be trivial (and we already put several items at the end), but I am not sure it would fix the problem since views can also be on other views. I presume the bad ordering happened as a result of a drop/create on a table? Or is there some other cause? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schemas not available for pl/pgsql %TYPE....
Patch applied. Thanks.
---
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 word.word.word%TYPE
+ * --
+ */
+ #define TYPE_JUNK_LEN 5
+
+ int
+ plpgsql_parse_tripwordtype(char *word)
+ {
+ Oid
Re: [HACKERS] contrib/ intarray, ltree, intagg broken(?) by array changes
Patch applied. Thanks. --- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] - pg_dump issues
On Wednesday 11 September 2002 05:40 pm, Oliver Elphick wrote: On Wed, 2002-09-11 at 21:19, Tom Lane wrote: In the meantime, I think that we shouldn't mess with pg_dump's basically OID-order-driven dump ordering. It works in normal cases, and adding arbitrary rules to it to fix one corner case is likely to accomplish little except breaking other corner cases. I can see that Lamar and I are going to have major problems dealing with users who fall over these problems. Yes, we are. Thankfully, with RPM dependencies I can prevent blind upgrades. But that doe not help the data migration issue this release is going to be. Guys, migration that is this shabby is, well, shabby. This _has_ to be fixed where a dump of 7.2.2 data (not 7.2.0, Tom) can be cleanly restored into 7.3. That is, after all, our only migration path. I think this upgrade/migration nightmare scenario warrants upping the version to 8.0 to draw attention to the potential problems. It seems to me that I shall have to make the new package such that it can exist alongside the old one for a time, or else possibly separate 7.3 pg_dump and pg_dumpall into a separate package. It is going to be a total pain! I had planned on making just such a 'pg_dump' package -- but if the 7.3 pg_dump isn't going to produce useful output, it seems like a waste of time. However, the jury is still out -- what sort of percentages are involved? That is, how likely are problems going to happen? Bruce, I mentioned a sed/perl/awk script already to massage the dump into a 7.3-friendly form -- but we need to gather the cases that are involved. Methinks every single OpenACS installation will hit this issue. How big is the problem? It's looking bigger with each passing day, ISTM. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] timestamp column default changed?
I understand. Thanks for pointing that out. L. On Wed, 11 Sep 2002, Bruce Momjian wrote: I think the SQL standards required the change. --- Laurette Cisneros wrote: I'm sure you all have discussed this ad-nauseum but this sure does create a pain in the butt when converting. Ok, I had my say. Thanks for all your hard work, L. On Wed, 11 Sep 2002, Bruce Momjian wrote: Laurette Cisneros wrote: If you define a column as: coltimestamp In 7.2.x didn't it default to timestamp with timezone? And now in 7.3(b1) it defaults to timestamp without timezone? /HISTORY says right at the top: * TIMESTAMP and TIME data types now default to WITHOUT TIMEZONE -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- A wiki we will go... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open items
On Wed, 11 Sep 2002, Bruce Momjian wrote: Marc G. Fournier wrote: On Wed, 11 Sep 2002, Bruce Momjian wrote: On Hold --- Point-in-time recovery Win32 port Security audit Why is the security audit on hold? This is the best time to do it, while the code is reasonably static :( It is on hold in the sense it is not a item that has to be completed for 7.3 but is in on-going, like the other items. The other items have to be specifically marked as done before the 7.3 release. Ah, k ... maybe put it under a 'Non Critical' or 'Ongoing' category? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump problems in upgrading
At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote: CREATE FUNCTION plperl_call_handler () RETURNS opaque ^^ AS '/usr/local/pgsql/lib/plperl.so', 'plperl_call_handler' LANGUAGE C; ... CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler; ERROR: function plperl_call_handler() does not return type language_handler This is reminiscent of the mess with language definitions in the last version, prior to the more sensible function manager definition system. A similar solution could be adopted here: extend the function manager definition macros to also (optionally) capture the return type; then when the function is defined, the function manager could check the real return type, issue a warning, and define it properly. This could be extended to args as well, if we felt so inclined. This solution obviously only works for languages since (I assume) they will be the only ones modified to use the improved macros; but it will fix 90% of problems. ERROR: Column year is of type integer but default expression is of type double precision You will need to rewrite or cast the expression This does seem like a problem to me - has anything been done about this? There does not seem to be much traffic in this thread. 3. A view is being created before one of the tables it refers to. Should not views be created only at the very end? Unless this is a 7.3-specific problem, I'd put this at a lower priority; as I suggested in an earlier post, moving the views to the end won't necessarily fix the problem; and pre-7.3 databases don't know about dependencies, so we can't use the rudimentary support for dependencies in pg_dump. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] - pg_dump issues
On Wednesday 11 September 2002 09:44 pm, Bruce Momjian wrote: Lamar Owen wrote: Bruce, I mentioned a sed/perl/awk script already to massage the dump into a 7.3-friendly form -- but we need to gather the cases that are involved. Methinks every single OpenACS installation will hit this issue. How big is the problem? It's looking bigger with each passing day, ISTM. That is exactly what I want to know and document on the open items page. I am having trouble understanding some of the failures because no one is showing the failure messages/statements, just describing them. Well, I am going to _try_ to lay aside an hour or two tomorrow or Friday and try to import a 7.2.2 OpenACS dump into a 7.3 installation. I'll try to get very verbose with the errors... :-) -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Long weekend
FYI, I am going to be away from Thursday night to Sunday on a retreat. I will be checking my email but may not be able to reply quickly. -- 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
[HACKERS] PGXLOG variable worthwhile?
Hi everyone, Am just wondering if we've ever considered adding a PGXLOG environment variable that would point to the pg_xlog directory? In a Unix environment it's not real necessary as filesystem links can be created, but in other environments (i.e. the Native windows port) it's looking like it might be useful. :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PGXLOG variable worthwhile?
We dealt this this (painfully) during 7.3 development. Some wanted a -X flag to initdb/postgres/postmaster that would identify the pg_xlog directory while others wanted the flag only on initdb and have initdb create a symlink. Finally, we decided to do nothing. and continue to recommend manually moving pg_xlog using symlinks. Also, I have heard symlinks are available in native Windows but the interface to them isn't clearly visible. Can someone clarify that? --- Justin Clift wrote: Hi everyone, Am just wondering if we've ever considered adding a PGXLOG environment variable that would point to the pg_xlog directory? In a Unix environment it's not real necessary as filesystem links can be created, but in other environments (i.e. the Native windows port) it's looking like it might be useful. :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- 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]
[HACKERS] MySQL wins award - makes amusing statement
MySQL wins Prestigious Linux Journal's Editors' Choice Award: http://www.mysql.com/news/article-109.html An amusing quote from the article: If you're one of the people who has been saying, 'I can't use MySQL because it doesn't have [feature you need here]', it's time to read up on MySQL 4.0 and try it out on a development system. Can you say, 'full support for transactions and row- level locking'? 'UNION'? 'Full text search'? *sigh* Well, at least they have an easy and fast upgrade process ;) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MySQL wins award - makes amusing statement
On Thu, Sep 12, 2002 at 01:56:19PM +0800, Christopher Kings-Lynne wrote: *sigh* Well, at least they have an easy and fast upgrade process ;) Right, fewer pesky features to get in the way of the upgrade ;- Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
