Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Tom Lane wrote: >> It ought to be illegal to modify the loop control variable anyway, >> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. > > If modifying the loop variable is disallowed in PL/SQL, I'm all for > disallowing it in plpgsql, otherwise not. Anyone have a > recent copy of Oracle to try it on? I tried this on Oracle 10.2.0.2.0 (which is the most recent version): SET SERVEROUTPUT ON BEGIN FOR i IN 1..10 LOOP i := i + 1; DBMS_OUTPUT.PUT_LINE(i); END LOOP; END; / i := i + 1; * ERROR at line 3: ORA-06550: line 3, column 7: PLS-00363: expression 'I' cannot be used as an assignment target ORA-06550: line 3, column 7: PL/SQL: Statement ignored And the documentation also explicitly states that it is not allowed. By the way, PL/SQL screams if you want to do an assignment with '='. But I guess that the current behaviour of PL/pgSQL should not reflect that to maintain backward compatibility, right? Yours, Laurenz Albe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] audit table containing Select statements submitted
Just a small example of the fact that people need such functionality... and will devise other ways, albeit inefficient and dangerous, to implement the missing feature. The success of an RDBMS (or any other product for that matter) depends on how well it strikes the balance between the standards implementation, and what the users need. Gurjeet. On 17 May 2006 02:31:20 -0400, Greg Stark <[EMAIL PROTECTED]> wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: > Some users of PL/Java make use of a non-default connection from within a > Trigger in order to do this. In essence, they load the client JDBC package into > the backend to let the backend as such become a client. The second connection > is then maintained for the lifetime of the first. Perhaps not the most > efficient way of doing it but it works. And you can do the same thing with any of the PL languages that have database drivers like Perl or Python. It might be a little less inefficient using one of them -- and probably a lot less code. You should be aware of the risk of deadlocks if you touch the same resources. Because the database is unaware that your main transaction is waiting for this other session to complete it won't be able to detect any deadlocks that depend on this hidden dependency. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
Andrew Piskorski wrote: >>> Rod Taylor wrote: Disk storage is cheap. Disk bandwidth or throughput is very expensive. > > Oracle has included "table compression" since 9iR2. They report table > size reductions of 2x to 4x as typical, with proportional reductions > in I/O, and supposedly, usually low to negligible overhead for writes: [...] > The main tricks seem to be: One, EXTREMELY lightweight compression > schemes - basically table lookups designed to be as cpu friendly as > posible. Two, keep the data compressed in RAM as well so that you can > also cache more of the data, and indeed keep it the compressed until > as late in the CPU processing pipeline as possible. Oracle's compression seems to work as follows: - At the beginning of each data block, there is a 'lookup table' containing frequently used values in table entries (of that block). - This lookup table is referenced from within the block. There is a White Paper that describes the algorithm and contains praise for the effects: http://www.oracle.com/technology/products/bi/pdf/o9ir2_compression_perfo rmance_twp.pdf Oracle does not compress tables by default. This is what they have to say about it: Table compression should be used with highly redundant data, such as tables with many foreign keys. You should avoid compressing tables with much update or other DML activity. Although compressed tables or partitions are updatable, there is some overhead in updating these tables, and high update activity may work against compression by causing some space to be wasted. Yours, Laurenz Albe ---(end of broadcast)--- TIP 1: 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] Compression and on-disk sorting
On Wed, May 17, 2006 at 09:45:35AM +0200, Albe Laurenz wrote: > Oracle's compression seems to work as follows: > - At the beginning of each data block, there is a 'lookup table' > containing frequently used values in table entries (of that block). > - This lookup table is referenced from within the block. Clever idea, pity we can't use it (what's the bet it's patented?). I'd wager anything beyond simple compression is patented by someone. The biggest issue is really that once postgres reads a block from disk and uncompresses it, this block will be much larger than 8K. Somehow you have to arrange storage for this. I have some ideas though, but as Tom says, should go for the quick and dirty numbers first, to determine if it's even worth doing. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 12:03:15AM -0400, Tom Lane wrote: > AFAICS the only sane choice here is to use > src/backend/utils/adt/pg_lzcompress.c, on the grounds that (1) it's > already in the backend, and (2) data compression in general is such a > minefield of patents that we'd be foolish to expose ourselves in more > than one direction. Unfortunatly, the interface provided by pg_lzcompress.c is probably insufficient for this purpose. You want to be able to compress tuples as they get inserted and start a new block once the output reaches a certain size. pg_lzcompress.c only has the options compress-whole-block and decompress-whole-block. zlib allows you to compress as the data comes along, keeping an eye on the output buffer while you do it. For an initial test, using zlib directly would probably be easier. If it works out we can look into alternatives. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Compression and on-disk sorting
On Tue, May 16, 2006 at 11:48:21PM -0400, Greg Stark wrote: > There are some very fast decompression algorithms: > > http://www.oberhumer.com/opensource/lzo/ Sure, and for some tasks in PostgreSQL perhaps it would be useful. But at least as of July 2005, a Sandor Heman, one of the MonetDB guys, had looked at zlib, bzlib2, lzrw, and lzo, and claimed that: "... in general, it is very unlikely that we could achieve any bandwidth gains with these algorithms. LZRW and LZO might increase bandwidth on relatively slow disk systems, with bandwidths up to 100MB/s, but this would induce high processing overheads, which interferes with query execution. On a fast disk system, such as our 350MB/s 12 disk RAID, all the generic algorithms will fail to achieve any speedup." http://www.google.com/search?q=MonetDB+LZO+Heman&btnG=Search http://homepages.cwi.nl/~heman/downloads/msthesis.pdf > I think most of the mileage from "lookup tables" would be better implemented > at a higher level by giving tools to data modellers that let them achieve > denser data representations. Things like convenient enum data types, 1-bit > boolean data types, short integer data types, etc. Things like enums and 1 bit booleans certainly could be useful, but they cannot take advantage of duplicate values across multiple rows at all, even if 1000 rows have the exact same value in their "date" column and are all in the same disk block, right? Thus I suspect that the exact opposite is true, a good table compression scheme would render special denser data types largely redundant and obsolete. Good table compression might be a lot harder to do, of course. Certainly Oracle's implementation of it had some bugs which made it difficult to use reliably in practice (in certain circumstances updates could fail, or if not fail perhaps have pathological performance), bugs which are supposed to be fixed in 10.2.0.2, which was only released within the last few months. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
> Certainly, if you can't prototype a convincing performance win using > that algorithm, it's unlikely to be worth anyone's time to > look harder. That should be easily possible with LZO. It would need to be the lib that we can optionally link to (--with-lzo), since the lib is GPL. lzo even allows for inplace decompression and overlapping compression. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
Ühel kenal päeval, K, 2006-05-17 kell 12:20, kirjutas Zeugswetter Andreas DCP SD: > > Certainly, if you can't prototype a convincing performance win using > > that algorithm, it's unlikely to be worth anyone's time to > > look harder. > > That should be easily possible with LZO. It would need to be the lib > that > we can optionally link to (--with-lzo), since the lib is GPL. > > lzo even allows for inplace decompression and overlapping compression. Does being GPL also automatically imply that it is patent-free, so that we could re-implement it under BSD license if it gives good results? Hannu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Albe Laurenz said: > Tom Lane wrote: >>> It ought to be illegal to modify the loop control variable anyway, >>> IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. >> >> If modifying the loop variable is disallowed in PL/SQL, I'm all for >> disallowing it in plpgsql, otherwise not. Anyone have a >> recent copy of Oracle to try it on? > > I tried this on Oracle 10.2.0.2.0 (which is the most recent version): > > SET SERVEROUTPUT ON > BEGIN > FOR i IN 1..10 LOOP > i := i + 1; > DBMS_OUTPUT.PUT_LINE(i); > END LOOP; > END; > / > i := i + 1; > * > ERROR at line 3: > ORA-06550: line 3, column 7: > PLS-00363: expression 'I' cannot be used as an assignment target > ORA-06550: line 3, column 7: > PL/SQL: Statement ignored > > And the documentation also explicitly states that it is not allowed. > So should we if it can be done conveniently. That might be a big IF - IIRC many Pascal compilers ignore the similar language rule because implementing it is a pain in the neck. > By the way, PL/SQL screams if you want to do an assignment with '='. > But I guess that the current behaviour of PL/pgSQL should not reflect > that to maintain backward compatibility, right? > I think it should. The current behaviour is undocumented and more than icky. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
> Unfortunatly, the interface provided by pg_lzcompress.c is probably > insufficient for this purpose. You want to be able to compress tuples > as they get inserted and start a new block once the output reaches a I don't think anything that compresses single tuples without context is going to be a win under realistic circumstances. I would at least compress whole pages. Allow a max ratio of 1:n, have the pg buffercache be uncompressed, and only compress on write (filesystem cache then holds compressed pages). The tricky part is predicting whether a tuple still fits in a n*8k uncompressed 8k compressed page, but since lzo is fast you might even test it in corner cases. (probably logic that needs to also be in the available page freespace calculation) Choosing a good n is also tricky, probably 2 (or 3 ?) is good. You probably also want to always keep the header part of the page uncompressed. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
On 5/17/06, Martijn van Oosterhout wrote: Clever idea, pity we can't use it (what's the bet it's patented?). I'd wager anything beyond simple compression is patented by someone. Oracle's patent application 20040054858 covers the method itself including the process for storing and retrieving compressed data. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Return results for PQexec vs PQexecP*
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Someone posted something on the DBD::Pg mailing list recently that made me wonder if the user's problem is more of a "don't do that" or something that may be solvable with a libpq or protocol change. Basically, the user has a rule which switches an insert to a select. They then want to run the insert, and pull the resulting tuples from it. This works fine when using PQexec, as it returns the latest result, which is PGRES_TUPLES_OK. However, when using the newer PQexec family (PQexecParams and PQexecPrepared), the only thing returned is PGRES_COMMAND_OK, which prevents the drawing of any subsequent tuples. Can anyone think of an easy way around this (other than forcing PQexec), and if not, is this a problem that needs fixing? It would be nice if PQexec and PQexecParams had the exact same behavior (and ideally, returned TUPLES_OK, even though COMMAND_OK may be more correct). - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200605170839 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEaxqEvJuQZxSWSsgRAj2TAJ48s7kkzJqb44l6h2XrGxNfckEtcwCg9U8b ZpZjc6FLtdGu/CZcfsDaPi4= =dGLJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Return results for PQexec vs PQexecP*
On Wed, May 17, 2006 at 12:45:17PM -, Greg Sabino Mullane wrote: > Someone posted something on the DBD::Pg mailing list recently that > made me wonder if the user's problem is more of a "don't do that" > or something that may be solvable with a libpq or protocol change. > > Basically, the user has a rule which switches an insert to a select. > They then want to run the insert, and pull the resulting tuples > from it. This works fine when using PQexec, as it returns the latest > result, which is PGRES_TUPLES_OK. However, when using the newer > PQexec family (PQexecParams and PQexecPrepared), the only thing returned > is PGRES_COMMAND_OK, which prevents the drawing of any subsequent tuples. The main problem with PQexec and co is that they don't really do very well if a single query produces multiple result sets. I'm not sure if it's defined whether you get the first or the last. In any case, if you want all the result sets, you need to be looking at PQsendquery and co. Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] pg_dump and backslash escapes
The basic issue is that we need to set standard_conforming_strings to 'off' for loading into newer releases, >= 8.2, but that SET command is going to generate an error even dumping/loading into the same version of PostgreSQL, like 7.3 to 7.3. I don't think we want that, do we? I agree we can have errors when doing cross-version dumps, but have we ever generated errors when dumping/reloading into the same version? We don't need to set escape_string_warning because it is just a warning message and the warning will only happen when loading into 8.2 or later, so we could skip that part of the patch. But, I figured as long as we are suppressing warnings at that point, might as well add that too. --- Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian writes: > > > I have seen no reply to my suggestion below, so I assume it is the way > > > people want to go for 7.3, 7.4, and 8.0. > > > > I'm not particularly for it, if that's what you meant, and certainly not > > for hacking up old branches that way. For one thing, you can't > > retroactively cause servers that are already out there to not spit > > errors for GUC variables they've not heard of; and even if you had such > > a time-travel machine at hand, it's far from clear that it'd be a good > > idea. > > > > The pg_dump philosophy for cross-version updates is generally that the > > dump should load if you are willing to ignore errors and press on. Not > > that there will never be errors. See for example our previous handling > > of the without_oids business, or search_path, or tablespaces. > > So, we should SET the variables and allow people to get the errors on > load? And not supress them from the client and server logs? Is that > better than suppressing them? > > -- > Bruce Momjian http://candle.pha.pa.us > EnterpriseDBhttp://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] What default is - SET behavior
I'm interesting in problem "Allow commenting of variables in postgresql.conf to restore them to defaults". And I need some clarify of SET command behavior. Res_value is defined in the source code as highest overriding setting during startup (or reconfiguration) and it is used for store "default" value. Hovewer documentation of SET command (http://www.postgresql.org/docs/8.1/interactive/sql-set.html) talk about "DEFAULT can be used to specify resetting the parameter to its default value." And there is question what is the meaning of "default value". Because I understood "default" like value coded in source code - mentioned in the postgresql.conf. Thanks Zdenek
Re: [HACKERS] What default is - SET behavior
Zdenek Kotala wrote: > I'm interesting in problem "Allow commenting of variables in > postgresql.conf to restore them to defaults". And I need some clarify > of SET command behavior. > > Res_value is defined in the source code as highest overriding setting > during startup (or reconfiguration) and it is used for store "default" > value. Hovewer documentation of SET command > (http://www.postgresql.org/docs/8.1/interactive/sql-set.html) talk about > "DEFAULT can be used to specify resetting the parameter to its default > value." And there is question what is the meaning of "default value". > Because I understood "default" like value coded in source code - > mentioned in the postgresql.conf. DEFAULT in this case is session default, which might be the compiled in default, which can be over-ridden by postgresql.conf, or by a user user SET command on session start (ALTER USER SET). -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > Albe Laurenz said: >> ERROR at line 3: >> ORA-06550: line 3, column 7: >> PLS-00363: expression 'I' cannot be used as an assignment target >> ORA-06550: line 3, column 7: >> PL/SQL: Statement ignored >> >> And the documentation also explicitly states that it is not allowed. > So should we if it can be done conveniently. That might be a big IF - IIRC > many Pascal compilers ignore the similar language rule because implementing > it is a pain in the neck. Since we already have the notion of a "const" variable in plpgsql, I think it might work to just mark the loop variable as const. >> By the way, PL/SQL screams if you want to do an assignment with '='. >> But I guess that the current behaviour of PL/pgSQL should not reflect >> that to maintain backward compatibility, right? > I think it should. The current behaviour is undocumented and more than icky. The lack of documentation is easily fixed ;-). I don't think this is icky enough to justify breaking all the existing functions we'd undoubtedly break if we changed it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #2429: Explain does not report object's schema
Alvaro Herrera wrote: > Cristiano Duarte wrote: > >> SQL table aliases doesn't help locating the real place where the table >> is. If I have a table named "test" at the schema "place" and I do: >> >> "EXPLAIN SELECT * FROM place.test mytest" >> >> I will get: >> >> "Seq Scan on test mytest" >> >> With this output I know that "mytest" is an alias to "test", and that's >> great, much helpful than aliases only, but, where is "test"? > > Since you created the mytest alias, you sure know where it's pointing > to. In fact I didn't create the alias, I've got the query already made from a user function call, and now I have to know where the table is located. Also, the user may pass a query without the schema name and even on this scenario, I need to know the schema name and the "real" table name. > > In fact I'd argue that this should instead display > Seq Scan on mytest I agree with you if EXPLAIN should only be executed interactivelly(psql, pgadmin3, etc). But, since you can execute EXPLAIN as a regular query to the database, you may be "explaining" an user supplied query, and doing so, there is no way to previously know what the aliases mean. > > >> I don't see too much harm if the output was: >> >> "Seq Scan on place.test mytest" > > Not much harm there, but there will be plenty harm on other node types > where the output is already too wide. Jim C. Nasby suggested a verbosity level to EXPLAIN, using "VERBOSE". It may solve this issue without harming other node types where the output is already too wide. Regards, Cristiano Duarte ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Foreign key column reference ordering and information_schema
Now that I've got a little time again... Per the report from Clark C Evans a while back and associated discussion, it seems like recent versions of the SQL spec changed the rules for foreign key column references such that the columns of the referenced unique constraint must be named in order (this is somewhat silly since unique(a,b) really should imply unique(b,a) but...). The information_schema definition seems to require this in order for one to use the information to find out the column references. I don't think we can switch to the newer definition directly since that will break dumps, but we could provide a variable which controls whether we allow the set allowed by SQL92 (not necessarily ordered) with the default being true for compatibility. But, that still doesn't give us a path to being able to change the default, or for that matter making it safe to use information_schema (since it would still be possible to change the value, make a constraint and change it back). One way to do this would be to accept the SQL92 form and treat it as if the command had given it in the ordered form, in other words, given create table pk(a int, b int, unique(a,b)); create table fk(c int, d int, foreign key(d,c) references (b,a)); the constraint is stored as if it were given foreign key(c,d) references(a,b). Does anyone have objections to either or both parts of this, and for the first, does anyone have a good idea of a name for the variable that would control this? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
Martijn van Oosterhout writes: > Clever idea, pity we can't use it (what's the bet it's patented?). I'd > wager anything beyond simple compression is patented by someone. You're in for a rude awakening: even "simple compression" is anything but simple. As I said, it's a minefield of patents. I recall reading a very long statement by one of the zlib developers (Jean-loup Gailly, I think) explaining exactly how they had threaded their way through that minefield, and why they were different enough from half-a-dozen similar-looking patented methods to not infringe any of them. I feel fairly confident that zlib is patent-free, first because they did their homework and second because they've now been out there and highly visible for a good long time without getting sued. I've got no such confidence in any other random algorithm you might choose --- in fact, I'm not at all sure that pg_lzcompress.c is safe. If we were aggressively trying to avoid patent risks we might well consider dropping pg_lzcompress.c and using zlib exclusively. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Tom Lane wrote: By the way, PL/SQL screams if you want to do an assignment with '='. But I guess that the current behaviour of PL/pgSQL should not reflect that to maintain backward compatibility, right? I think it should. The current behaviour is undocumented and more than icky. The lack of documentation is easily fixed ;-). I don't think this is icky enough to justify breaking all the existing functions we'd undoubtedly break if we changed it. We have tightened behaviour in ways much harder to fix in the past, e.g. actually following UTF8 rules. Fixing breakage in this case would be pretty trivial, and nobody has any real right to expect the current behaviour to work. But I won't be surprised to be in a minority on this cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Return results for PQexec vs PQexecP*
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: > Someone posted something on the DBD::Pg mailing list recently that > made me wonder if the user's problem is more of a "don't do that" > or something that may be solvable with a libpq or protocol change. > Basically, the user has a rule which switches an insert to a select. > They then want to run the insert, and pull the resulting tuples > from it. This works fine when using PQexec, as it returns the latest > result, which is PGRES_TUPLES_OK. However, when using the newer > PQexec family (PQexecParams and PQexecPrepared), the only thing returned > is PGRES_COMMAND_OK, which prevents the drawing of any subsequent tuples. I'd call that a "don't do that" issue. The newer protocol is specifically designed to be more predictable than the old, and that includes not returning tuples from statements that clearly shouldn't return anything. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump and backslash escapes
Bruce Momjian writes: > The basic issue is that we need to set standard_conforming_strings to > 'off' for loading into newer releases, >= 8.2, but that SET command is > going to generate an error even dumping/loading into the same version of > PostgreSQL, like 7.3 to 7.3. I don't think we want that, do we? Why are you worried? Have you counted how many SETs there are currently that will generate errors in older versions of PG? As long as the older backend will load the data correctly after rejecting the SET, there's no functional problem, and I think trying to hide the error is a cosmetic thing that will likely do more harm than good in the long run. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
""Albe Laurenz"" <[EMAIL PROTECTED]> > Tom Lane wrote: ... > > If modifying the loop variable is disallowed in PL/SQL, I'm all for > > disallowing it in plpgsql, otherwise not. Anyone have a > > recent copy of Oracle to try it on? > > I tried this on Oracle 10.2.0.2.0 (which is the most recent version): > > SET SERVEROUTPUT ON > BEGIN >FOR i IN 1..10 LOOP > i := i + 1; > DBMS_OUTPUT.PUT_LINE(i); >END LOOP; > END; > / > i := i + 1; > * > ERROR at line 3: > ORA-06550: line 3, column 7: > PLS-00363: expression 'I' cannot be used as an assignment target > ORA-06550: line 3, column 7: > PL/SQL: Statement ignored > > And the documentation also explicitly states that it is not allowed. > > By the way, PL/SQL screams if you want to do an assignment with '='. > But I guess that the current behaviour of PL/pgSQL should not reflect > that to maintain backward compatibility, right? > I think Oracle's syntax and behaviour are better. As for this feature, breaking the backward compatibility is acceptable. Regards, William ZHANG ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump and backslash escapes
Tom Lane wrote: > Bruce Momjian writes: > > The basic issue is that we need to set standard_conforming_strings to > > 'off' for loading into newer releases, >= 8.2, but that SET command is > > going to generate an error even dumping/loading into the same version of > > PostgreSQL, like 7.3 to 7.3. I don't think we want that, do we? > > Why are you worried? Have you counted how many SETs there are currently > that will generate errors in older versions of PG? As long as the older > backend will load the data correctly after rejecting the SET, there's no > functional problem, and I think trying to hide the error is a cosmetic > thing that will likely do more harm than good in the long run. Fine. You mean dumping and reloading pg_dump output in 7.3 generates errors? I didn't know. Can you give an example? I wasn't aware of that. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The lack of documentation is easily fixed ;-). I don't think this is >> icky enough to justify breaking all the existing functions we'd >> undoubtedly break if we changed it. > We have tightened behaviour in ways much harder to fix in the past, e.g. > actually following UTF8 rules. True, but there were clear benefits from doing so. Disallowing "=" assignment in plpgsql wouldn't buy anything, just break programs. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump and backslash escapes
Bruce Momjian writes: > You mean dumping and reloading pg_dump output in 7.3 generates errors? > I didn't know. Can you give an example? I wasn't aware of that. Well, looking at the SETs already currently emitted: $ pg_dump -s regression | grep ^SET SET client_encoding = 'SQL_ASCII'; fails before 7.1 SET check_function_bodies = false; fails before 7.4 SET client_min_messages = warning; fails before 7.3 SET search_path = public, pg_catalog; fails before 7.3 SET default_tablespace = '';fails before 8.0 SET default_with_oids = false; fails before 8.0 so I'm not at all clear what you've got against this one. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What default is - SET behavior
Bruce Momjian writes: > Zdenek Kotala wrote: >> I'm interesting in problem "Allow commenting of variables in >> postgresql.conf to restore them to defaults". And I need some clarify >> of SET command behavior. > DEFAULT in this case is session default, which might be the compiled in > default, which can be over-ridden by postgresql.conf, or by a user user > SET command on session start (ALTER USER SET). More specifically, RESET and SET TO DEFAULT both have the behavior of establishing whatever value would now prevail if you had never issued any interactive SET in the current session. This might in fact be different from any value that actually had prevailed earlier in the session --- for instance if the new value comes from a postgresql.conf entry that's been changed since you did your SET. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
On Tue, May 16, 2006 at 06:48:25PM -0400, Greg Stark wrote: > Martijn van Oosterhout writes: > > > > It might be easier to switch to giving each tape it's own file... > > > > I don't think it would make much difference. OTOH, if this turns out to > > be a win, the tuplestore could have the same optimisation. > > Would giving each tape its own file make it easier to allow multiple temporary > sort areas and allow optimizing to avoid seeking when multiple spindles area > available? Only if those spindles weren't all in a single RAID array and if we went through the trouble of creating all the machinery so you could tell PostgreSQL where all those spindles were mounted in the filesystem. And even after all that work, there's not much that says it would perform better than a simple RAID10. What *might* make sense would be to provide two locations for pgsql_tmp, because a lot of operations in there involve reading and writing at the same time: Read from heap while writing tapes to pgsql_tmp read from tapes while writing final version to pgsql_tmp There's probably some gain to be had by writing the final version to a tablespace other than the default one (which is where pgsql_tmp would be, I think). But recent changes in -HEAD mean that the second step is now only performed in certain cases. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] Compression and on-disk sorting
If we're going to consider table-level compression, ISTM the logical first step is to provide greater control over TOASTing; namely thresholds for when to compress and/or go to external storage that can be set on a per-field or at least per-table basis. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 10:06:04AM +0200, Martijn van Oosterhout wrote: > On Wed, May 17, 2006 at 09:45:35AM +0200, Albe Laurenz wrote: > > Oracle's compression seems to work as follows: > > - At the beginning of each data block, there is a 'lookup table' > > containing frequently used values in table entries (of that block). > > - This lookup table is referenced from within the block. > > Clever idea, pity we can't use it (what's the bet it's patented?). I'd > wager anything beyond simple compression is patented by someone. > > The biggest issue is really that once postgres reads a block from disk > and uncompresses it, this block will be much larger than 8K. Somehow > you have to arrange storage for this. It's entirely possible that the best performance would be found from not un-compressing blocks when putting them into shared_buffers, though. That would mean you'd "only" have to deal with compression when pulling individual tuples. Simple, right? :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump and backslash escapes
Tom Lane wrote: > Bruce Momjian writes: > > You mean dumping and reloading pg_dump output in 7.3 generates errors? > > I didn't know. Can you give an example? I wasn't aware of that. > > Well, looking at the SETs already currently emitted: > > $ pg_dump -s regression | grep ^SET > SET client_encoding = 'SQL_ASCII';fails before 7.1 > SET check_function_bodies = false;fails before 7.4 > SET client_min_messages = warning;fails before 7.3 > SET search_path = public, pg_catalog; fails before 7.3 > SET default_tablespace = ''; fails before 8.0 > SET default_with_oids = false;fails before 8.0 > > so I'm not at all clear what you've got against this one. Very clear. The issue is that I can't find any of these emitted by a pg_dump version who's native backend doesn't understand them. I assume that it is expected that a cross-db dump/reload will generate errors, and it is done rarely for upgrades, but I assume same-version dump/restore is done more frequently and people don't expect errors. Is that not a significant distinction? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] Return results for PQexec vs PQexecP*
On Wed, May 17, 2006 19:53, Martijn van Oosterhout wrote: > The main problem with PQexec and co is that they don't really do very > well if a single query produces multiple result sets. I'm not sure if > it's defined whether you get the first or the last. In any case, if you > want all the result sets, you need to be looking at PQsendquery and co. AFAIK it's well-defined if you send multiple queries in a single string, separated by semicolons: PQexec() returns the result of the last query. Jeroen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On Tue, May 16, 2006 at 07:56:25PM -0700, David Wheeler wrote: > On May 16, 2006, at 19:52, Tom Lane wrote: > > >Distant ancestors aren't particularly relevant here. What plpgsql > >tries > >to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If > >modifying the loop variable is disallowed in PL/SQL, I'm all for > >disallowing it in plpgsql, otherwise not. > > Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL? > So that it's easier to migrate from PostgreSQL to Oracle? > > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. Well, I'd argue that if we were serious about the migration case we'd just add PL/SQL as a language. Presumably EnterpriseDB has done that, and might be willing to donate that to the community. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] PL/pgSQL 'i = i + 1' Syntax
On Tue, May 16, 2006 at 07:56:25PM -0700, David Wheeler wrote: > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. Oh, and PL/SQL is a lot more powerful than plpgsql. See packages for one thing... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On Wed, May 17, 2006 at 10:11:39AM -0400, Tom Lane wrote: > The lack of documentation is easily fixed ;-). I don't think this is > icky enough to justify breaking all the existing functions we'd > undoubtedly break if we changed it. I thought the suggestion was to complain loudly (presumably during CREATE FUNCTION), but not throw an error. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Foreign key column reference ordering and information_schema
Stephan Szabo <[EMAIL PROTECTED]> writes: > Per the report from Clark C Evans a while back and associated discussion, > it seems like recent versions of the SQL spec changed the rules for > foreign key column references such that the columns of the referenced > unique constraint must be named in order (this is somewhat silly since > unique(a,b) really should imply unique(b,a) but...). I do not believe that that reading is correct. If the SQL committee had intended such a change, it would surely have been called out as a compatibility issue in Annex E of SQL2003. Which it isn't. What I see in SQL99 is (11.8 ) If the specifies a , then the set of s contained in that shall be equal to the set of s contained in the of a unique constraint of the referenced table. Let referenced columns be the column or columns identified by that and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. where SQL2003 has If the specifies a , then there shall be a one-to-one correspondence between the set of s contained in that and the set of s contained in the of a unique constraint of the referenced table such that corresponding s are equivalent. Let referenced columns be the column or columns identified by that and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. I think SQL2003 is actually just trying to say the same thing in more precise language: you have to be able to match up the columns in the with some unique constraint. I don't think the "one to one" bit is meant to imply a left-to-right-ordered correspondence; that's certainly not the mathematical meaning of a one-to-one function for instance. > The information_schema definition seems to require this in order for > one to use the information to find out the column references. I'm more inclined to think that we've messed up the information_schema somehow ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump and backslash escapes
> Very clear. The issue is that I can't find any of these emitted by a > pg_dump version who's native backend doesn't understand them. > > I assume that it is expected that a cross-db dump/reload will generate > errors, and it is done rarely for upgrades, but I assume same-version > dump/restore is done more frequently and people don't expect errors. > Is that not a significant distinction? I thought the suggested procedure (see migration doc) was to use the new pg_dump to dump the older db version, so why backpatch ? Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump and backslash escapes
Zeugswetter Andreas DCP SD wrote: > > > Very clear. The issue is that I can't find any of these emitted by a > > pg_dump version who's native backend doesn't understand them. > > > > I assume that it is expected that a cross-db dump/reload will generate > > errors, and it is done rarely for upgrades, but I assume same-version > > dump/restore is done more frequently and people don't expect errors. > > Is that not a significant distinction? > > I thought the suggested procedure (see migration doc) was to use the > new pg_dump to dump the older db version, so why backpatch ? Uh, you can suggest it, but I would guess < 50% do it, and once the old database is gone, there is no way to re-do the dump. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump and backslash escapes
Bruce Momjian writes: > Very clear. The issue is that I can't find any of these emitted by a > pg_dump version who's native backend doesn't understand them. So? We're not doing anything differently in that regard either. 8.2 will understand the SET, what's the problem? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > What *might* make sense would be to provide two locations for pgsql_tmp, > because a lot of operations in there involve reading and writing at the > same time: > Read from heap while writing tapes to pgsql_tmp > read from tapes while writing final version to pgsql_tmp Note that a large part of the reason for the current logtape.c design is to avoid requiring 2X or more disk space to sort X amount of data. AFAICS, any design that does the above will put us right back in the 2X regime. That's a direct, measurable penalty; it'll take more than handwaving arguments to convince me we should change it in pursuit of unquantified speed benefits. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_dump and backslash escapes
> > I thought the suggested procedure (see migration doc) was to use the > > new pg_dump to dump the older db version, so why backpatch ? > > Uh, you can suggest it, but I would guess < 50% do it, and once the old > database is gone, there is no way to re-do the dump. But you can still load the dump if you execute the two statements in the new db psql session before loading the dump file, no ? > > > SET escape_string_warning = off; > > > SET standard_conforming_strings = off; Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 11:38:05AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > What *might* make sense would be to provide two locations for pgsql_tmp, > > because a lot of operations in there involve reading and writing at the > > same time: > > > Read from heap while writing tapes to pgsql_tmp > > read from tapes while writing final version to pgsql_tmp > > Note that a large part of the reason for the current logtape.c design > is to avoid requiring 2X or more disk space to sort X amount of data. > AFAICS, any design that does the above will put us right back in the 2X > regime. That's a direct, measurable penalty; it'll take more than > handwaving arguments to convince me we should change it in pursuit of > unquantified speed benefits. I certainly agree that there's no reason to make this change without testing, but if you've got enough spindles laying around to actually make use of this I suspect that requiring 2X the disk space to sort X won't bother you. Actually, I suspect in most cases it won't matter; I don't think people make a habit of trying to sort their entire database. :) But we'd want to protect for the oddball cases... yech. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign key column reference ordering and information_schema
On Wed, 17 May 2006, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > Per the report from Clark C Evans a while back and associated discussion, > > it seems like recent versions of the SQL spec changed the rules for > > foreign key column references such that the columns of the referenced > > unique constraint must be named in order (this is somewhat silly since > > unique(a,b) really should imply unique(b,a) but...). > > I do not believe that that reading is correct. If the SQL committee had > intended such a change, it would surely have been called out as a > compatibility issue in Annex E of SQL2003. Which it isn't. > > where SQL2003 has > > If the specifies a list>, then there shall be a one-to-one correspondence between the > set of s contained in that > and the set of s contained in the list> of a unique constraint of the referenced table such that > corresponding s are equivalent. Let referenced columns > be the column or columns identified by that list> and let referenced column be one such column. Each referenced > column shall identify a column of the referenced table and the same > column shall not be identified more than once. > > I think SQL2003 is actually just trying to say the same thing in more > precise language: you have to be able to match up the columns in the > with some unique constraint. I don't think the "one > to one" bit is meant to imply a left-to-right-ordered correspondence; > that's certainly not the mathematical meaning of a one-to-one function > for instance. No, but the part which says corresponding column names are equivalent seems to imply it to me. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Wed, May 17, 2006 at 11:38:05AM -0400, Tom Lane wrote: >> Note that a large part of the reason for the current logtape.c design >> is to avoid requiring 2X or more disk space to sort X amount of data. > Actually, I suspect in most cases it won't matter; I don't think people > make a habit of trying to sort their entire database. :) Well, some years ago we used something like 4X space to sort X amount of data (this is the native behavior of 7-way polyphase merge, it turns out) and we got yelled at. Which is what prompted the writing of logtape.c. Maybe disk space has gotten so cheap since then that it no longer matters ... but I suspect the nature of DB applications is that people are always pushing the envelope of what their hardware can do. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign key column reference ordering and information_schema
On Wed, 17 May 2006, Stephan Szabo wrote: > On Wed, 17 May 2006, Tom Lane wrote: > > > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > Per the report from Clark C Evans a while back and associated discussion, > > > it seems like recent versions of the SQL spec changed the rules for > > > foreign key column references such that the columns of the referenced > > > unique constraint must be named in order (this is somewhat silly since > > > unique(a,b) really should imply unique(b,a) but...). > > > > I do not believe that that reading is correct. If the SQL committee had > > intended such a change, it would surely have been called out as a > > compatibility issue in Annex E of SQL2003. Which it isn't. > > > > where SQL2003 has > > > > If the specifies a > list>, then there shall be a one-to-one correspondence between the > > set of s contained in that > > and the set of s contained in the > list> of a unique constraint of the referenced table such that > > corresponding s are equivalent. Let referenced columns > > be the column or columns identified by that > list> and let referenced column be one such column. Each referenced > > column shall identify a column of the referenced table and the same > > column shall not be identified more than once. > > > > I think SQL2003 is actually just trying to say the same thing in more > > precise language: you have to be able to match up the columns in the > > with some unique constraint. I don't think the "one > > to one" bit is meant to imply a left-to-right-ordered correspondence; > > that's certainly not the mathematical meaning of a one-to-one function > > for instance. > > No, but the part which says corresponding column names are equivalent > seems to imply it to me. Or are you thinking that the corresponding column names are equivalent is just a description of how to make the correspondence? That seems like a very odd way to phrase that since just saying that the sets of column names are equivalent would be enough for that and all the extra words seem to only obscure the point. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Foreign key column reference ordering and information_schema
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Wed, 17 May 2006, Tom Lane wrote: >> where SQL2003 has >> >> If the specifies a > list>, then there shall be a one-to-one correspondence between the >> set of s contained in that >> and the set of s contained in the > list> of a unique constraint of the referenced table such that >> corresponding s are equivalent. Let referenced columns >> be the column or columns identified by that > list> and let referenced column be one such column. Each referenced >> column shall identify a column of the referenced table and the same >> column shall not be identified more than once. >> >> I think SQL2003 is actually just trying to say the same thing in more >> precise language: you have to be able to match up the columns in the >> with some unique constraint. I don't think the "one >> to one" bit is meant to imply a left-to-right-ordered correspondence; >> that's certainly not the mathematical meaning of a one-to-one function >> for instance. > No, but the part which says corresponding column names are equivalent > seems to imply it to me. No, that's just saying the names have to actually match. Otherwise I could say that I can put "A B C" into a one-to-one correspondence with "D E F" because there are the same number of elements in each set. The whole sentence was written by a pedant and is not an improvement in intelligibility over the SQL92/99 wording, but I really think it is not intended to imply anything functionally different. Again, if they did intend to change the allowable matches, why doesn't Annex E mention it? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Foreign key column reference ordering and information_schema
Stephan Szabo <[EMAIL PROTECTED]> writes: > That seems like a very odd way to phrase that since just saying that the > sets of column names are equivalent would be enough for that and all the > extra words seem to only obscure the point. As an example of clear well-written English, it certainly fails miserably no matter which construction you put on it :-(. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Fri, May 12, 2006 at 08:38:07PM -0400, Bruce Momjian wrote:
> > Is this like detecting of libpq is SSL-enabled? I see PQgetssl(). Do
> > we need to add a libpq function to return true/false for threading?
> > Slony requires a threaded libpq, so it could do the test to prevent
> > wrong configurations. It would be nice to enabled threading by default,
> > but it is like SSL in that not all operating systems support it.
>
> PQgetssl() doesn't tell you if SSL is supported, it tells you if the
> *current connection* is using OpenSSL, which is similar but not the
> same.
>
> There is AFAIK no way to tell if SSL support is compiled in. Part of
> the patch I posted for GnuTLS support answered this question also
> (PQgettlsinfo()).
I thought about this. Attached is a patch you can use to
popen("pg_config") and then look for the thread flag to configure. One
idea would be to add this sample to our libpq documentation. The
problem with the example is popen() overhead, pg_config not in $PATH, or
pg_config's version not matching libpq's version.
A more comprehensive idea would be to embed the configure string in
libpq, like we do for pg_config, and allow that to be returned to the
caller so they can do a strstr() to see if a certain flag was used.
Having per-configure flag functions, like for threading, seems like it
could be a mess because there is a lot more application programmers might
care about in addition to threading, like SSL, multi-byte, etc.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
/*
* This function queries pg_config to check for specific
* configure flags used to build PostgreSQL. It can be
* easily modified to return true/false to the caller.
* This example tests thread safety.
*/
#include
#include
#define PG_CONFIG_CMD "pg_config --configure"
/*
* pg_config's output should fit in one string because we don't want
* the search string to split across reads.
*/
#define POPEN_READ 8192
int
main(int argc, char *argv[])
{
FILE*p;
charstr[POPEN_READ];
if ((p = popen(PG_CONFIG_CMD, "r")) == NULL ||
fgets(str, POPEN_READ, p) == NULL)
{
fprintf(stderr, "Cannot run \"%s\", perhaps incorrect $PATH",
PG_CONFIG_CMD);
if (p)
pclose(p);
exit(2);
}
pclose(p);
if (strstr(str, "--enable-thread-safety") != NULL)
{
puts("threading enabled");
return 0;
}
else
{
puts("threading not enabled");
return 1;
}
}
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Compression and on-disk sorting
> Actually, I suspect in most cases it won't matter; I don't think people > make a habit of trying to sort their entire database. :) But we'd want > to protect for the oddball cases... yech. I can make query result sets that are far larger than the database itself. create table fat_table_with_few_tuples(fat_status_id serial primary key, fat_1 text, fat_2 text); create table thin_table_with_many_tuples(fat_status_id integer references fat_table_with_few_tuples, thin_1 integer, thin_2 integer); SELECT * FROM thin_table_with_many_tuples NATURAL JOIN fat_table_with_few_tuples order by fat_1, thin_1, thin_2, fat_2; I would be asking the folks trying to use PostgreSQL for data warehousing what their opinion is. A few fact tables in an audit query could easily result in a very large amount of temporary diskspace being required. -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
For all those people not subscribed to -patches (should appear in archive soon), I just posted a patch there implemented zlib compression for logtape.c. If people have test machines for speed-testing this sort of stuff, please have at it. You can also download it here: http://svana.org/kleptog/temp/compress-sort.patch Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Foreign key column reference ordering and information_schema
On Wed, 17 May 2006, Tom Lane wrote: > I'm more inclined to think that we've messed up the information_schema > somehow ... As usual, you're right. ;) Actually, it wasn't precisely that we messed it up as much as the 99 defintion was wrong. It's pointed out in the 2003 schemata incompatibilities annex as having been incomplete and changed for 2003. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_dump and backslash escapes
Tom Lane wrote: > Bruce Momjian writes: > > Very clear. The issue is that I can't find any of these emitted by a > > pg_dump version who's native backend doesn't understand them. > > So? We're not doing anything differently in that regard either. 8.2 > will understand the SET, what's the problem? Uh, 8.2 doesn't need these flags. It is for back branches that assume escape processing. Anyway, I talked to Tom on the phone and he feels we just need to tell people loading pre-8.1 dumps to use PGOPTIONS to set escape_string_warning to false when loading the dump. Seeing that many pre-8.1 people are not going to upgrade the newest 8.0.X before upgrading to 8.2 or 8.3, I suppose the PGOPTIONS approach is OK. It would have to be documented in the release notes, actually starting with 8.2 because that's the first release where escape_string_warning can be true. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Compression and on-disk sorting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Only if those spindles weren't all in a single RAID array and if we went > through the trouble of creating all the machinery so you could tell > PostgreSQL where all those spindles were mounted in the filesystem. I think the way you do this is simply by documenting that the admin should create precisely one temp area per physical spindle (or raid array). -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Bruce Momjian writes:
> I thought about this. Attached is a patch you can use to
> popen("pg_config") and then look for the thread flag to configure. One
> idea would be to add this sample to our libpq documentation. The
> problem with the example is popen() overhead, pg_config not in $PATH, or
> pg_config's version not matching libpq's version.
Yeah, the last point seems like a killer objection :-(. It'd be better
to add some sort of libpq function to handle the issue.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Compression and on-disk sorting
Andrew Piskorski <[EMAIL PROTECTED]> writes: > Things like enums and 1 bit booleans certainly could be useful, but > they cannot take advantage of duplicate values across multiple rows at > all, even if 1000 rows have the exact same value in their "date" > column and are all in the same disk block, right? That's an interesting direction to go in. Generic algorithms would still help in that case since the identical value would occur more frequently than other values it would be encoded in a smaller symbol. But there's going to be a limit to how compressed it can get the data. The ideal way to handle the situation you're describing would be to interleave the tuples so that you have all 1000 values of the first column, followed by all 1000 values of the second column and so on. Then you run a generic algorithm on this and it achieves very high compression rates since there are a lot of repeating patterns. I don't see how you build a working database with data in this form however. For example, a single insert would require updating small pieces of data across the entire table. Perhaps there's some middle ground with interleaving the tuples within a single compressed page, or something like that? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Tom Lane wrote:
> Bruce Momjian writes:
>> I thought about this. Attached is a patch you can use to
>> popen("pg_config") and then look for the thread flag to configure.
>> One idea would be to add this sample to our libpq documentation. The
>> problem with the example is popen() overhead, pg_config not in
>> $PATH, or pg_config's version not matching libpq's version.
>
> Yeah, the last point seems like a killer objection :-(. It'd be
> better to add some sort of libpq function to handle the issue.
>
and when I've proposed libpq functions to expose compile-time
constants, I've been shot down.
How is this different?
--
Larry Rosenman
Database Support Engineer
PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531
Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Larry Rosenman wrote:
> Tom Lane wrote:
> > Bruce Momjian writes:
> >> I thought about this. Attached is a patch you can use to
> >> popen("pg_config") and then look for the thread flag to configure.
> >> One idea would be to add this sample to our libpq documentation. The
> >> problem with the example is popen() overhead, pg_config not in
> >> $PATH, or pg_config's version not matching libpq's version.
> >
> > Yeah, the last point seems like a killer objection :-(. It'd be
> > better to add some sort of libpq function to handle the issue.
> >
>
> and when I've proposed libpq functions to expose compile-time
> constants, I've been shot down.
>
> How is this different?
No idea, what is the URL of your proposal. Keep in mind this is not
option-specific.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 1: 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] [GENERAL] Querying libpq compile time options
Bruce Momjian wrote:
> Larry Rosenman wrote:
>> Tom Lane wrote:
>>> Bruce Momjian writes:
I thought about this. Attached is a patch you can use to
popen("pg_config") and then look for the thread flag to configure.
One idea would be to add this sample to our libpq documentation.
The problem with the example is popen() overhead, pg_config not in
$PATH, or pg_config's version not matching libpq's version.
>>>
>>> Yeah, the last point seems like a killer objection :-(. It'd be
>>> better to add some sort of libpq function to handle the issue.
>>>
>>
>> and when I've proposed libpq functions to expose compile-time
>> constants, I've been shot down.
>>
>> How is this different?
>
> No idea, what is the URL of your proposal. Keep in mind this is not
> option-specific.
I had made a proposal to expose the path used for pg_service.conf.
this was back a month or so ago.
Would it be better to make a structure that has ALL the options, and
return
that from ONE function?
If so, I can code it up. I have time available.
--
Larry Rosenman
Database Support Engineer
PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531
Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Bruce Momjian writes: >> Tom Lane wrote: >>> Yeah, the last point seems like a killer objection :-(. It'd be >>> better to add some sort of libpq function to handle the issue. >> >> and when I've proposed libpq functions to expose compile-time >> constants, I've been shot down. >> >> How is this different? > No idea, what is the URL of your proposal. Keep in mind this is not > option-specific. Hm, I was thinking of something like "bool PQisThreadSafe()". It sounds like Bruce is thinking of something that'd return a string literal containing configure flags and then apps would have to try to inspect that to determine what they want to know. That seems fairly messy to me; for one thing because it would imply wiring assumptions about default configure flags into apps, and that's something that could change over time. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Larry Rosenman wrote:
> Bruce Momjian wrote:
> > Larry Rosenman wrote:
> >> Tom Lane wrote:
> >>> Bruce Momjian writes:
> I thought about this. Attached is a patch you can use to
> popen("pg_config") and then look for the thread flag to configure.
> One idea would be to add this sample to our libpq documentation.
> The problem with the example is popen() overhead, pg_config not in
> $PATH, or pg_config's version not matching libpq's version.
> >>>
> >>> Yeah, the last point seems like a killer objection :-(. It'd be
> >>> better to add some sort of libpq function to handle the issue.
> >>>
> >>
> >> and when I've proposed libpq functions to expose compile-time
> >> constants, I've been shot down.
> >>
> >> How is this different?
> >
> > No idea, what is the URL of your proposal. Keep in mind this is not
> > option-specific.
>
> I had made a proposal to expose the path used for pg_service.conf.
Why would an application programmer care to know the location of
pg_service.conf?
> Would it be better to make a structure that has ALL the options, and
> return
> that from ONE function?
I can't think of an easy way to do that.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Tom Lane wrote: > Bruce Momjian writes: > >> Tom Lane wrote: > >>> Yeah, the last point seems like a killer objection :-(. It'd be > >>> better to add some sort of libpq function to handle the issue. > >> > >> and when I've proposed libpq functions to expose compile-time > >> constants, I've been shot down. > >> > >> How is this different? > > > No idea, what is the URL of your proposal. Keep in mind this is not > > option-specific. > > Hm, I was thinking of something like "bool PQisThreadSafe()". It sounds > like Bruce is thinking of something that'd return a string literal > containing configure flags and then apps would have to try to inspect > that to determine what they want to know. That seems fairly messy to > me; for one thing because it would imply wiring assumptions about > default configure flags into apps, and that's something that could > change over time. True, but if you go per-option, I can see adding a lot of them. That seemed more messy. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] [GENERAL] Querying libpq compile time options
"Larry Rosenman" <[EMAIL PROTECTED]> writes: > I had made a proposal to expose the path used for pg_service.conf. I don't remember that anymore, but my question about it would be "what's the use case?" I don't see a particularly good reason why an app would need to know that, whereas it's pretty clear why a thread-dependent app might wish to defend itself against a thread-unsafe libpq. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Bruce Momjian writes: > True, but if you go per-option, I can see adding a lot of them. That > seemed more messy. If there actually were a lot of options being proposed for addition, maybe, but I only see one on the table. regards, tom lane ---(end of broadcast)--- TIP 1: 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] [GENERAL] Querying libpq compile time options
Bruce Momjian wrote:
> Larry Rosenman wrote:
>> Bruce Momjian wrote:
>>> Larry Rosenman wrote:
Tom Lane wrote:
> Bruce Momjian writes:
>> I thought about this. Attached is a patch you can use to
>> popen("pg_config") and then look for the thread flag to
>> configure. One idea would be to add this sample to our libpq
>> documentation. The problem with the example is popen() overhead,
>> pg_config not in $PATH, or pg_config's version not matching
>> libpq's version.
>
> Yeah, the last point seems like a killer objection :-(. It'd be
> better to add some sort of libpq function to handle the issue.
>
and when I've proposed libpq functions to expose compile-time
constants, I've been shot down.
How is this different?
>>>
>>> No idea, what is the URL of your proposal. Keep in mind this is not
>>> option-specific.
>>
>> I had made a proposal to expose the path used for pg_service.conf.
>
> Why would an application programmer care to know the location of
> pg_service.conf?
The admin needs to know it to use it. Currently there is no
way to get what is compiled into a specific libpq.
>
>> Would it be better to make a structure that has ALL the options, and
>> return that from ONE function?
>
> I can't think of an easy way to do that.
I guess I'll just crawl under my rock again.
--
Larry Rosenman
Database Support Engineer
PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531
Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Tom, > If you only care about Oracle to PostgreSQL (and who wouldn't?), then > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. I agree with David here. We care about the ability to migrate PL/SQL --> PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as the Oracle syntax still works ... is in fact a good thing. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Tom Lane wrote: > Bruce Momjian writes: > > True, but if you go per-option, I can see adding a lot of them. That > > seemed more messy. > > If there actually were a lot of options being proposed for addition, > maybe, but I only see one on the table. Well, SSL is one, multibyte is another. I can see it expanding. Locale? NLS? If we think it just threading, then that is easy, just one function. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Larry Rosenman wrote:
> Bruce Momjian wrote:
> > Larry Rosenman wrote:
> >> Bruce Momjian wrote:
> >>> Larry Rosenman wrote:
> Tom Lane wrote:
> > Bruce Momjian writes:
> >> I thought about this. Attached is a patch you can use to
> >> popen("pg_config") and then look for the thread flag to
> >> configure. One idea would be to add this sample to our libpq
> >> documentation. The problem with the example is popen() overhead,
> >> pg_config not in $PATH, or pg_config's version not matching
> >> libpq's version.
> >
> > Yeah, the last point seems like a killer objection :-(. It'd be
> > better to add some sort of libpq function to handle the issue.
> >
>
> and when I've proposed libpq functions to expose compile-time
> constants, I've been shot down.
>
> How is this different?
> >>>
> >>> No idea, what is the URL of your proposal. Keep in mind this is not
> >>> option-specific.
> >>
> >> I had made a proposal to expose the path used for pg_service.conf.
> >
> > Why would an application programmer care to know the location of
> > pg_service.conf?
>
> The admin needs to know it to use it. Currently there is no
> way to get what is compiled into a specific libpq.
Uh, it is an _admin_ function, not an application programmer function.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Bruce Momjian wrote:
> Larry Rosenman wrote:
>> Bruce Momjian wrote:
>>> Larry Rosenman wrote:
Bruce Momjian wrote:
> Larry Rosenman wrote:
>> Tom Lane wrote:
>>> Bruce Momjian writes:
I thought about this. Attached is a patch you can use to
popen("pg_config") and then look for the thread flag to
configure. One idea would be to add this sample to our libpq
documentation. The problem with the example is popen()
overhead, pg_config not in $PATH, or pg_config's version not
matching libpq's version.
>>>
>>> Yeah, the last point seems like a killer objection :-(. It'd be
>>> better to add some sort of libpq function to handle the issue.
>>>
>>
>> and when I've proposed libpq functions to expose compile-time
>> constants, I've been shot down.
>>
>> How is this different?
>
> No idea, what is the URL of your proposal. Keep in mind this is
> not option-specific.
I had made a proposal to expose the path used for pg_service.conf.
>>>
>>> Why would an application programmer care to know the location of
>>> pg_service.conf?
>>
>> The admin needs to know it to use it. Currently there is no
>> way to get what is compiled into a specific libpq.
>
> Uh, it is an _admin_ function, not an application programmer function.
but libpq is the only thing that knows where it is, and I had proposed a
way
for psql to use the function to get it.
However, I'm going to forget about it, as obviously I won't get approval
for it.
--
Larry Rosenman
Database Support Engineer
PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531
Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com
---(end of broadcast)---
TIP 1: 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] PL/pgSQL 'i = i + 1' Syntax
Tom, > True, but there were clear benefits from doing so. Disallowing "=" > assignment in plpgsql wouldn't buy anything, just break programs. But it's already disallowed in most places. The i = i + 1 seems to be an exception. So what happens to "i" if I do: IF i = i + 1 THEN does "i" increment? If so, isn't that a bug? I don't think too many people are using that functionality intentionally; I probably write more PL/pgSQL than anyone and would regard any assignment without ":=" as a bug. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Josh Berkus writes: >> True, but there were clear benefits from doing so. Disallowing "=" >> assignment in plpgsql wouldn't buy anything, just break programs. > But it's already disallowed in most places. No it isn't. The plpgsql scanner treats := and = as *the same token*. They can be interchanged freely. This has nothing to do with the case of modifying a loop variable in particular. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
"Larry Rosenman" <[EMAIL PROTECTED]> writes: >> Uh, it is an _admin_ function, not an application programmer >> function. > but libpq is the only thing that knows where it is, and I had proposed a > way for psql to use the function to get it. It'd make more sense for pg_config to expose this as one of the available information bits. The difference from the thread-support case is that you'd typically want to get the pg_service.conf location manually, and that's exactly what pg_config is designed for. Verifying thread support, on the other hand, is something that a program would want to do. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Tom Lane wrote: > "Larry Rosenman" <[EMAIL PROTECTED]> writes: >>> Uh, it is an _admin_ function, not an application programmer >>> function. > >> but libpq is the only thing that knows where it is, and I had >> proposed a way for psql to use the function to get it. > > It'd make more sense for pg_config to expose this as one of the > available information bits. The difference from the thread-support > case is that you'd typically want to get the pg_service.conf location > manually, and that's exactly what pg_config is designed for. > Verifying thread support, on the other hand, is something that a > program would want to do. It still gets into the messiness of pg_config doesn't load libpq, and there could be a mis-match. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.231.6597 Email: [EMAIL PROTECTED] Web: www.pervasive.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Tom Lane wrote: > "Larry Rosenman" <[EMAIL PROTECTED]> writes: > >> Uh, it is an _admin_ function, not an application programmer > >> function. > > > but libpq is the only thing that knows where it is, and I had proposed a > > way for psql to use the function to get it. > > It'd make more sense for pg_config to expose this as one of the > available information bits. The difference from the thread-support We already do expose it: $ pg_config --sysconfdir /usr/var/local/postgres/etc -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Larry Rosenman wrote: > Tom Lane wrote: > > "Larry Rosenman" <[EMAIL PROTECTED]> writes: > >>> Uh, it is an _admin_ function, not an application programmer > >>> function. > > > >> but libpq is the only thing that knows where it is, and I had > >> proposed a way for psql to use the function to get it. > > > > It'd make more sense for pg_config to expose this as one of the > > available information bits. The difference from the thread-support > > case is that you'd typically want to get the pg_service.conf location > > manually, and that's exactly what pg_config is designed for. > > Verifying thread support, on the other hand, is something that a > > program would want to do. > > It still gets into the messiness of pg_config doesn't load libpq, and > there could be a mis-match. That is the administrator's job, to make sure they match. Applications programmers can't do that. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Bruce Momjian wrote: We already do expose it: $ pg_config --sysconfdir /usr/var/local/postgres/etc Speaking of this item, what do we want to do about the Windows situation where if the directory doesn't exist it reports nothing at all? I am inclined to send back the output from GetFullPathName() instead of GetShortPathName(). This should be fixed - reporting an empty string is fairly unsatisfactory. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Querying libpq compile time options
Andrew Dunstan wrote: > Bruce Momjian wrote: > > We already do expose it: > > > > $ pg_config --sysconfdir > > /usr/var/local/postgres/etc > > > > > > Speaking of this item, what do we want to do about the Windows situation > where if the directory doesn't exist it reports nothing at all? I am > inclined to send back the output from GetFullPathName() instead of > GetShortPathName(). This should be fixed - reporting an empty string is > fairly unsatisfactory. I was researching that and will report back. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
Greg Stark <[EMAIL PROTECTED]> writes: > The ideal way to handle the situation you're describing would be to interleave > the tuples so that you have all 1000 values of the first column, followed by > all 1000 values of the second column and so on. Then you run a generic > algorithm on this and it achieves very high compression rates since there are > a lot of repeating patterns. It's not obvious to me that that yields a form more compressible than what we have now. As long as the previous value is within the lookback window, an LZ-style compressor will still be able to use it. More importantly, the layout you describe would be unable to take advantage of any cross-column correlation, which in real data is likely to be a useful property for compression. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 12:55:53PM -0400, Greg Stark wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > Only if those spindles weren't all in a single RAID array and if we went > > through the trouble of creating all the machinery so you could tell > > PostgreSQL where all those spindles were mounted in the filesystem. > > I think the way you do this is simply by documenting that the admin should > create precisely one temp area per physical spindle (or raid array). And you still need some way to tell PostgreSQL about all of that. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 12:16:13PM -0400, Rod Taylor wrote: > > Actually, I suspect in most cases it won't matter; I don't think people > > make a habit of trying to sort their entire database. :) But we'd want > > to protect for the oddball cases... yech. > > I can make query result sets that are far larger than the database > itself. > > create table fat_table_with_few_tuples(fat_status_id serial primary key, > fat_1 text, fat_2 text); > > create table thin_table_with_many_tuples(fat_status_id integer > references fat_table_with_few_tuples, thin_1 integer, thin_2 integer); > > SELECT * FROM thin_table_with_many_tuples NATURAL JOIN > fat_table_with_few_tuples order by fat_1, thin_1, thin_2, fat_2; > > > I would be asking the folks trying to use PostgreSQL for data > warehousing what their opinion is. A few fact tables in an audit query > could easily result in a very large amount of temporary diskspace being > required. Note my last sentence: we'd need to provide for cases where this was a problem. How much that would complicate the code, I don't know... This is another case where someone (with more skills than me) would need to hack the backend enough to be able to test it and see how big a performance gain there was. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] Compression and on-disk sorting
Ühel kenal päeval, K, 2006-05-17 kell 10:01, kirjutas Jim C. Nasby: > If we're going to consider table-level compression, ISTM the logical > first step is to provide greater control over TOASTing; namely > thresholds for when to compress and/or go to external storage that can > be set on a per-field or at least per-table basis. also, we would get a lot of "compression", if we could get rid of index on toast table, and use the ctid directly. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Ühel kenal päeval, K, 2006-05-17 kell 10:22, kirjutas Josh Berkus: > Tom, > > > True, but there were clear benefits from doing so. Disallowing "=" > > assignment in plpgsql wouldn't buy anything, just break programs. > > But it's already disallowed in most places. The i = i + 1 seems to be an > exception. > > So what happens to "i" if I do: > > IF i = i + 1 THEN > > does "i" increment? If so, isn't that a bug? > > I don't think too many people are using that functionality intentionally; I > probably write more PL/pgSQL than anyone and would regard any assignment > without ":=" as a bug. I do occasionally write some pl/pgSQL, and have at some points written a lot of it. And most of it uses = instead of := , including all code written during last 1.5 years. Once I found out that = works for assignment, i completely stopped using := .I have treated := as "deprecated" for some time already. So allowing only := for assignment would make me very sad . -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On Wed, May 17, 2006 at 10:18:51AM -0700, Josh Berkus wrote: > Tom, > > > If you only care about Oracle to PostgreSQL (and who wouldn't?), then ? > > it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. > > I agree with David here. We care about the ability to migrate PL/SQL --> > PL/pgSQL. We do *not* care about the ability to migrate PL/pgSQL --> > PL/SQL. So supporting extra syntax which Oracle doesn't ... as long as > the Oracle syntax still works ... is in fact a good thing. Except someone did make a very good point that as soon as we add some feature that Oracle doesn't have, they can turn around and add the same feature using a different syntax, and then we'd have a real mess. If we're going to be serious about easing migration from Oracle we should really be adding PL/SQL as a language, because there's already some pretty non-trivial differences (off the top of my head, how you handle sending debug info back is a big one, as is our lack of packages). IMO, if the community is going to concentrate on a migration path, I think MySQL is a much better target: - There's already a commercial solution for migrating from Oracle, and there's probably more money there than in migrating from MySQL - Enabling migration from MySQL would be a tremendous benefit to the growth of the community, because there's a lot more people who would try that on a whim and stick with PostgreSQL than for any of the commercial RDBMSes - Having some kind of compatability mode would make it much easier on all the OSS projects that currently only support MySQL to add PostgreSQL support. It also makes it much easier for people to use PostgreSQL over MySQL -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] Compression and on-disk sorting
On Wed, May 17, 2006 at 10:55:19PM +0300, Hannu Krosing wrote: > ??hel kenal p??eval, K, 2006-05-17 kell 10:01, kirjutas Jim C. Nasby: > > If we're going to consider table-level compression, ISTM the logical > > first step is to provide greater control over TOASTing; namely > > thresholds for when to compress and/or go to external storage that can > > be set on a per-field or at least per-table basis. > > also, we would get a lot of "compression", if we could get rid of index > on toast table, and use the ctid directly. It'd also be damn handy to be able to ditch the 2-pass vacuum requirement. I've often wondered if treating the toast table as a real table was overkill; for example, it should be possible to include WAL information for it in with the parent table. That plus using ctid as a reference would hopefully allow for removing a lot of overhead from it. Presumably all the MVCC info could go, since that would be taken care of by the parent table. Of course the downside is that it'd mean a different set of code for handling toast storage... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Hannu Krosing wrote: I don't think too many people are using that functionality intentionally; I probably write more PL/pgSQL than anyone and would regard any assignment without ":=" as a bug. I do occasionally write some pl/pgSQL, and have at some points written a lot of it. And most of it uses = instead of := , including all code written during last 1.5 years. Once I found out that = works for assignment, i completely stopped using := .I have treated := as "deprecated" for some time already. So allowing only := for assignment would make me very sad . I can only comment that that seems crazy. := is . documented . consistent with pl/sql and ancestors Even C doesn't use the same operator for assignment and comparison. Sometimes I wonder if large parts of the IT world is trying to pretend that the Algol family never existed. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Cross-version dumps (was: [DOCS] Mention pg_dump version portability)
Moving to -hackers. On Wed, May 17, 2006 at 12:13:49PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > pg_dump -Fc | pg_restore will only match the output of pg_dump if > > everything's the same version though, right? Or wrong? What happens if > > pg_dump -Fc is and older version and pg_restore is the current version? > > There would probably be some small differences, but 99% of pg_restore's > output is just regurgitating SQL it finds in the dump file. Maybe it's time to look at improving pg_restore's ability to deal with cross-version issues, and make it the default for pg_dump. ISTM there's been a fair amount of contortions as of late around pg_dump SQL compatability. If the custom format at least included pg_dump version information, and pg_restore new what version of PostgreSQL it was restoring into, it could handle these cases more elegantly than is possible with just dumping straight into psql. Or it might make more sense for the custom format to be something that's less sensitive to some of these compatability issues. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] PL/pgSQL 'i = i + 1' Syntax
Ühel kenal päeval, K, 2006-05-17 kell 16:31, kirjutas Andrew Dunstan: > Hannu Krosing wrote: > >> I don't think too many people are using that functionality intentionally; > >> I > >> probably write more PL/pgSQL than anyone and would regard any assignment > >> without ":=" as a bug. > >> > > > > I do occasionally write some pl/pgSQL, and have at some points written a > > lot of it. And most of it uses = instead of := , including all code > > written during last 1.5 years. > > > > Once I found out that = works for assignment, i completely stopped > > using := .I have treated := as "deprecated" for some time already. > > > > So allowing only := for assignment would make me very sad . > > > > I can only comment that that seems crazy. > > := is > . documented > . consistent with pl/sql and ancestors OTOH * = is used in CONST declaration in PASCAL, whereas pl/pgSQL documents := (i.e. ASSIGNMENT) for const declaration * = is used by everybody else for assignment. It can be argued that the reason it is not used in pure SQL for assignment is just that SQL lacks assignment. * weather = is assignment statement or comparison operator is always clear from position, that is pl/pgsql does not have result for assignment statement, so everywhere the value of "A = B" is used, it must be comparison operator, whereas when its value is not used it must be statement. So no disambiguity. > Even C doesn't use the same operator for assignment and comparison. It can't, as in C both assignment and comparison are operators, so it allows you to use result of both as boolean. > Sometimes I wonder if large parts of the IT world is trying to pretend > that the Algol family never existed. And even bigger part is trying to pretend that LISP and Prolog never existed ;) And don't forget about QUEL and PostQUEL either. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Hannu Krosing said: > > * = is used in CONST declaration in PASCAL, whereas pl/pgSQL > documents := (i.e. ASSIGNMENT) for const declaration Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for constant initialisation. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Compression and on-disk sorting
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Wed, May 17, 2006 at 12:55:53PM -0400, Greg Stark wrote: > > > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > > > Only if those spindles weren't all in a single RAID array and if we went > > > through the trouble of creating all the machinery so you could tell > > > PostgreSQL where all those spindles were mounted in the filesystem. > > > > I think the way you do this is simply by documenting that the admin should > > create precisely one temp area per physical spindle (or raid array). > > And you still need some way to tell PostgreSQL about all of that. No, my point was that you tell Postges how many spindles you have and where to find them by creating precisely one temp area on each spindle. It then knows that it should strive to maximize sequential reads within one temp area and expect switching between temp areas (which represent multiple spindles) to be better than multiplexing multiple tapes within a single temp area (which represents a single spindle). -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On 5/17/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for constant initialisation. True dat. Almost all PL/SQL components come from ADA. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 17, 2006 at 05:44:22PM -0400, Greg Stark wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > On Wed, May 17, 2006 at 12:55:53PM -0400, Greg Stark wrote: > > > > > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > > > > > Only if those spindles weren't all in a single RAID array and if we went > > > > through the trouble of creating all the machinery so you could tell > > > > PostgreSQL where all those spindles were mounted in the filesystem. > > > > > > I think the way you do this is simply by documenting that the admin should > > > create precisely one temp area per physical spindle (or raid array). > > > > And you still need some way to tell PostgreSQL about all of that. > > No, my point was that you tell Postges how many spindles you have and where to > find them by creating precisely one temp area on each spindle. It then knows Which means we need all the interface bits to be able to tell PostgreSQL where every single temp storage area is. Presumably much of the tablespace mechanism could be used for this, but it's still a bunch of work. And you can't just say "I have 8 spindles", you have to tell PostgreSQL exactly where to put each temporary area (unless you just have it put one on every tablespace you have defined). > that it should strive to maximize sequential reads within one temp area and > expect switching between temp areas (which represent multiple spindles) to be > better than multiplexing multiple tapes within a single temp area (which > represents a single spindle). Which adds yet more complexity to all the code that uses the temp area. And as others have brought up, you still have to allow for the case when splitting all of this out into multiple files means you end up using substantially more disk space. That further drives up the complexity. My point is that unless someone shows that there's a non-trivial performance gain here, it's not going to happen. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Ühel kenal päeval, K, 2006-05-17 kell 17:51, kirjutas Jonah H. Harris: > On 5/17/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: > > Pascal is not the direct ancestor of PL/SQL, Ada is, and it uses := for > > constant initialisation. Does ADA have both assignment and comparison as operators, or is assignment a statement ? > True dat. Almost all PL/SQL components come from ADA. Maybe we need just pl/ADA ;) ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 1: 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] Foreign key column reference ordering and information_schema
Stephan Szabo wrote: > On Wed, 17 May 2006, Tom Lane wrote: > > >>Stephan Szabo <[EMAIL PROTECTED]> writes: >> >>>Per the report from Clark C Evans a while back and associated discussion, >>>it seems like recent versions of the SQL spec changed the rules for >>>foreign key column references such that the columns of the referenced >>>unique constraint must be named in order (this is somewhat silly since >>>unique(a,b) really should imply unique(b,a) but...). >> >>I do not believe that that reading is correct. If the SQL committee had >>intended such a change, it would surely have been called out as a >>compatibility issue in Annex E of SQL2003. Which it isn't. >> >>where SQL2003 has >> >>If the specifies a >list>, then there shall be a one-to-one correspondence between the >>set of s contained in that >>and the set of s contained in the >list> of a unique constraint of the referenced table such that >>corresponding s are equivalent. Let referenced columns >>be the column or columns identified by that >list> and let referenced column be one such column. Each referenced >>column shall identify a column of the referenced table and the same >>column shall not be identified more than once. >> >>I think SQL2003 is actually just trying to say the same thing in more >>precise language: you have to be able to match up the columns in the >> with some unique constraint. I don't think the "one >>to one" bit is meant to imply a left-to-right-ordered correspondence; >>that's certainly not the mathematical meaning of a one-to-one function >>for instance. > > > No, but the part which says corresponding column names are equivalent > seems to imply it to me. > The language quoted above uses the language "set of X contained in list Y" multiple times (substituting X and Y). The only reason to do so would be to invoke the mathematical distinction between lists and sets, which is that sets do not imply any specific ordering. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] semaphore usage "port based"?
On Tue, 9 May 2006, Max Khon wrote: Yes, there seems to be an awful lot of noise being made about the fact that the system does, in fact, work exactly as documented, and that the configuration being complained about is one that is specifically documented as being unsupported and undesirable. As commented elsewhere in this thread, currently, there is no virtualization support for System V IPC in the FreeBSD Jail implementation. That may change if/when someone implements it. Until it's implemented, it isn't going to be there, and the system won't behave as though it's there no matter how much jumping up and down is done. sysvipc has been implemented once, but it has been decided that it adds unnecessary bloat. That's sad. I'm not sure I follow the reasoning behind this statement. Could you direct me to the implementation, and at the specific claim that it adds unnecessary bloat? As far as I know, no implementation of jail support for system v ipc has ever been rejected on the basis that it adds bloat -- all discussion about it has centered on the fact that it is, in fact, a very difficult technical problem to solve, which requires careful consideration of the approach and tradeoffs, and that that careful consideration has not yet bene done. Robert N M Watson ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On 5/17/06, Hannu Krosing <[EMAIL PROTECTED]> wrote: Does ADA have both assignment and comparison as operators, or is assignment a statement ? Yes. Assignment is := and comparison is = Maybe we need just pl/ADA ;) ? Wouldn't hurt :) -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] [OT] MySQL is bad, but THIS bad?
Sorry to interrupt, but I have had the "opportinuty" to have to work with MySQL. This nice little gem is packed away in the reference for mysql_use_result(). "On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched." How do busy web sites work like this? What is the best way to go about creating a "plug and play," PostgreSQL replacement for MySQL? I think the biggest problem getting PostgreSQL accepted is that so much code is available for MySQL. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
Mark Woodward wrote: > Sorry to interrupt, but I have had the "opportinuty" to have to work with > MySQL. This nice little gem is packed away in the reference for > mysql_use_result(). > > "On the other hand, you shouldn't use mysql_use_result() if you are doing > a lot of processing for each row on the client side, or if the output is > sent to a screen on which the user may type a ^S (stop scroll). This ties > up the server and prevent other threads from updating any tables from > which the data is being fetched." > > How do busy web sites work like this? > > What is the best way to go about creating a "plug and play," PostgreSQL > replacement for MySQL? I think the biggest problem getting PostgreSQL > accepted is that so much code is available for MySQL. That reminds me of the Twilight Zone episode where the guy had a stopwatch that stopped time: http://en.wikipedia.org/wiki/A_Kind_of_a_Stopwatch_(The_Twilight_Zone) -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
On May 17, 2006, at 8:08 PM, Mark Woodward wrote: What is the best way to go about creating a "plug and play," PostgreSQL replacement for MySQL? I think the biggest problem getting PostgreSQL accepted is that so much code is available for MySQL. http://pgfoundry.org/projects/mysqlcompat/ John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] BEGIN inside transaction should be an error
Added to TODO: * Add a GUC to control whether BEGIN inside a transcation should abort the transaction. --- Jaime Casanova wrote: > On 5/12/06, Mario Weilguni <[EMAIL PROTECTED]> wrote: > > Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs: > > > On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote: > > > > Martijn van Oosterhout writes: > > > > > How do other database deal with this? Either they nest BEGIN/COMMIT or > > > > > they probably throw an error without aborting the transaction, which > > > > > is > > > > > pretty much what we do. Is there a database that actually aborts a > > > > > whole transaction just for an extraneous begin? > > > > > > > > Probably not. The SQL99 spec does say (in describing START TRANSACTION, > > > > which is the standard spelling of BEGIN) > > > > > > > > 1) If a statement is executed > > > > when > > > > an SQL-transaction is currently active, then an exception condition is > > > > raised: invalid transaction state - active SQL-transaction. > > > > > > > > *However*, they are almost certainly expecting that that condition only > > > > causes the START command to be ignored; not that it should bounce the > > > > whole transaction. So I think the argument that this is required by > > > > the spec is a bit off base. > > > > > > If you interpret the standard that way then the correct behaviour in the > > > face of *any* exception condition should be *not* abort the transaction. > > > In PostgreSQL, all exception conditions do abort the transaction, so why > > > not this one? Why would we special-case this? > > > > IMO it's ok to raise an exception - if this is configurable for at least one > > releasy cycle - giving developers time to fix applications. It's no good > > behaviour to change something like this without any (at least time-limited ) > > backward compatible option. > > > > if an option to change it is put in place, maybe it will be there > forever (with a different default behavior)... > > i am all in favor of a second begin to throw an exception "already in > transaction" or something else > (http://archives.postgresql.org/pgsql-hackers/2005-12/msg00813.php), > but if we do it we should do it the only behavior... i don't think > it's good to introduce a new GUC for that things (we will finish with > GUCs to turn off every fix) > > -- > regards, > Jaime Casanova > > "Programming today is a race between software engineers striving to > build bigger and better idiot-proof programs and the universe trying > to produce bigger and better idiots. > So far, the universe is winning." >Richard Cook > > ---(end of broadcast)--- > TIP 1: 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 > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Even C doesn't use the same operator for assignment and comparison. However, SQL *does*, so it seems to me that plsql is gratuitously inconsistent with its larger environment. regards, tom lane ---(end of broadcast)--- TIP 1: 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
