[HACKERS] help needed with CREATE CONVERSION
I encountered a problem while implementing new CREATE CONVERSION. Since converion procs are dynamically invoked while doing an encoding conversion, it might fail for some reasons: (1) stale pg_conversion entry. If someone re-register that proc, the oid might be changed and the reference from pg_conversion to pg_proc becomes stale. (2) buggy conversion proc is defined by a user (3) schema search path changed. Since conversion is schema aware, if someone sets a wrong schema path, the conversion proc might not be found anymore. This is actually not a problem right now, since in this case a conversion search would be performed on pg_catalog name space which should always be exist. However I am a little bit worried about this. Problem is, in any case mentioned above, an ERROR is raised and backend tries to send an error message which again raise an ERROR. As a result, backend goes into an infinite loop. I have to do some syscache searches aginst pg_proc before calling conversion proc using fmgr, since there seems no API for checking that conversion proc surely exists without throwing an ERROR. This is ugly and is not ideal IMO. Any idea? -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pgbench questions
[Cc:ed to hackers] From: [EMAIL PROTECTED] (Neil Conway) Subject: pgbench questions Date: Sat, 13 Jul 2002 00:57:37 -0400 Message-ID: <[EMAIL PROTECTED]> > Hi, > > I was looking at doing some performance profiling on PostgreSQL, and > I had a few questions on pgbench. > > (1) Is there a reason you chose to use the TPC-B benchmark rather > than TPC-C or TPC-H, for example? Do you think there might be any > merit in converting pgbench to use TPC-H or AS3AP? Just easy to implement. Ideally pgbench should be able handle to several kinds of benchmarks (I don't have time to do that sigh...) BTW, TPC-H is very different from othe benchmarks. As far as I know, it focuses on Data Ware House. So TPC-H cannot be a replacement for TPC-B. > (2) At least in the current CVS version, the code to do a 'CHECKPOINT' > after creating a table has been #ifdef'ed out. Why is that? That is not after creation of a table, but while creating it, which is not necessary any more since Tom has fix the growth of WAL logs. > (3) Several people (Rod Taylor, Tom Lane, myself, perhaps others) have > noticed that the results obtained from pgbench can be somewhat > inconsistent (i.e. can vary between runs quite a bit). > > Have you found this to be the case in your own experience? > > Do you have any suggestions on how pgbench can be made more > consistent (either through special benchmarking procedures, or > through a change to pgbench) I believe it's a common problem with benchmark programs. I think Tom or Jan has posted a good summary to hackers list showing how to get a consistent result with pgbench. -- Tatsuo Ishii ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [COMMITTERS] pgsql/ oc/src/sgml/catalogs.sgml oc/src/sgml/r ...
Is it at all a problem that several columns in pg_conversion have the same name as columns in pg_constraint? Should the ones in pg_conversion become: convname instead of conname, etc. simply for clarity? Chris - Original Message - > Log message: > Second phase of committing Rod Taylor's pg_depend/pg_constraint patch. > pg_relcheck is gone; CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY > constraints all have real live entries in pg_constraint. pg_depend > exists, and RESTRICT/CASCADE options work on most kinds of DROP; > however, pg_depend is not yet very well populated with dependencies. > (Most of the ones that are present at this point just replace formerly > hardwired associations, such as the implicit drop of a relation's pg_type > entry when the relation is dropped.) Need to add more logic to create > dependency entries, improve pg_dump to dump constraints in place of > indexes and triggers, and add some regression tests. > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Unique and Primary Key Constraints
> > I prefer ...add constraint. After a while (release or 2) removal of > > create unique index all together. > > Remove CREATE UNIQUE INDEX entirely? Why? I was looking to encourage users to use core SQL as I spend more time than I want converting between systems -- thanks in part to users who create non-portable structures. Temporarily forgot there are index types other than btree :) Anyway, thanks for the answers. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Unique and Primary Key Constraints
Rod Taylor wrote: > I'm going to change the pg_dump command to pull these constraints out of > pg_constaint where possible, creating the appropriate alter table add > constraint command (see primary key). > > > Should unique constraints created with 'create index' (no entry in > pg_constraint) be re-created via alter table add constraint, or via > create unique index? CREATE UNIQUE INDEX has optimization purpose as well as an constraint purpose. I think CREATE UNIQUE INDEX is the way to go. > I prefer ...add constraint. After a while (release or 2) removal of > create unique index all together. Remove CREATE UNIQUE INDEX entirely? Why? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] test data for query optimizer
What about the TPC-H benchmark ? I cant recall if it has more than 10 tables, but it seemed like the queries were "quite good" for a benchmark. In addition it comes with a data generator. regards Mark >On Sat, 2002-07-13 at 04:05, Neil Conway wrote: > I'd like to look at the performance of the query optimizer (both the > traditional one and GEQO) when joining large numbers of tables: 10-15, > or more. In order to do that (and to get meaningful results), I'll > need to work with some data that actually requires joins of that > magnitude. Ideally, I'd like the data to be somewhat realistic -- so > that the performance I'm seeing will reflect the performance a typical > user might see. (i.e. I don't want an artificial benchmark) > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] test data for query optimizer
On Sat, Jul 13, 2002 at 11:18:14AM +0800, Christopher Kings-Lynne wrote: > What about the OSDB benchmark? Does that contain a large dataset? No -- it only uses 5 relations total, with the most complex query only involving 4 joins. Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] test data for query optimizer
What about the OSDB benchmark? Does that contain a large dataset? Chris - Original Message - From: "Neil Conway" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Saturday, July 13, 2002 12:05 AM Subject: [HACKERS] test data for query optimizer > I'd like to look at the performance of the query optimizer (both the > traditional one and GEQO) when joining large numbers of tables: 10-15, > or more. In order to do that (and to get meaningful results), I'll > need to work with some data that actually requires joins of that > magnitude. Ideally, I'd like the data to be somewhat realistic -- so > that the performance I'm seeing will reflect the performance a typical > user might see. (i.e. I don't want an artificial benchmark) > > However, I don't possess any data of that nature, and I'm unsure > where I can find some (or how to generate some of my own). Does > anyone know of: > > - a freely available collection of data that requires queries > of this type, and is reasonably representative of "real world" > applications > > - or, a means to generate programatically some data that > fits the above criteria. > > Thanks in advance, > > Neil > > -- > Neil Conway <[EMAIL PROTECTED]> > PGP Key ID: DB3C29FC > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Unique and Primary Key Constraints
I'm going to change the pg_dump command to pull these constraints out of pg_constaint where possible, creating the appropriate alter table add constraint command (see primary key). Should unique constraints created with 'create index' (no entry in pg_constraint) be re-created via alter table add constraint, or via create unique index? I prefer ...add constraint. After a while (release or 2) removal of create unique index all together. Since index names are unique, and all unique and primary key constraints have a matching name in pg_index there isn't a problem with name conflicts. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Memo on dropping practices
On Fri, 2002-07-12 at 15:17, Tom Lane wrote: > Now that the pg_depend mechanism is mostly in there, it is no longer > a good idea to delete things directly (for example, by calling > heap_drop_with_catalog or even just heap_delete'ing a catalog tuple). I noticed that SERIAL sequences aren't dropping with the application of the patch. Was this intentional? I know I didn't have a way of carrying sequence information across a dump (yet), but didn't think it would hurt to have. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Memo on dropping practices
Thanks, TODO updated. I split out "Make constraints clearer in dump file" into a foreign key version, which I marked as done, and a second version which I left as undone. Thanks. That's a heap of items completed. --- Rod Taylor wrote: > > * Add pg_depend table for dependency recording; use sysrelid, oid, > > depend_sysrelid, depend_oid, name > > * Auto-destroy sequence on DROP of table with SERIAL; perhaps a separate > > SERIAL type > > * Have SERIAL generate non-colliding sequence names when we have > > auto-destruction > > * Prevent column dropping if column is used by foreign key > > * Propagate column or table renaming to foreign key constraints > > * Automatically drop constraints/functions when object is dropped > > * Make constraints clearer in dump file > > * Make foreign keys easier to identify > > * Flush cached query plans when their underlying catalog data changes > > > > Which of these are done with the patch? > > Below is what I listed off as complete when submitting the patch. > > 'Make constraints clearer in dump file' is questionable. Foreign keys > are, others not yet, but they need to be. > > > # Add ALTER TABLE DROP non-CHECK CONSTRAINT > # Allow psql \d to show foreign keys > * Add pg_depend table for dependency recording; use sysrelid, oid, > depend_sysrelid, depend_oid, name > # Auto-destroy sequence on DROP of table with SERIAL > # Prevent column dropping if column is used by foreign key > # Automatically drop constraints/functions when object is dropped > # Make constraints clearer in dump file > # Make foreign keys easier to identify > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Memo on dropping practices
> * Add pg_depend table for dependency recording; use sysrelid, oid, > depend_sysrelid, depend_oid, name > * Auto-destroy sequence on DROP of table with SERIAL; perhaps a separate > SERIAL type > * Have SERIAL generate non-colliding sequence names when we have > auto-destruction > * Prevent column dropping if column is used by foreign key > * Propagate column or table renaming to foreign key constraints > * Automatically drop constraints/functions when object is dropped > * Make constraints clearer in dump file > * Make foreign keys easier to identify > * Flush cached query plans when their underlying catalog data changes > > Which of these are done with the patch? Below is what I listed off as complete when submitting the patch. 'Make constraints clearer in dump file' is questionable. Foreign keys are, others not yet, but they need to be. # Add ALTER TABLE DROP non-CHECK CONSTRAINT # Allow psql \d to show foreign keys * Add pg_depend table for dependency recording; use sysrelid, oid, depend_sysrelid, depend_oid, name # Auto-destroy sequence on DROP of table with SERIAL # Prevent column dropping if column is used by foreign key # Automatically drop constraints/functions when object is dropped # Make constraints clearer in dump file # Make foreign keys easier to identify ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Memo on dropping practices
Tom Lane wrote: > Now that the pg_depend mechanism is mostly in there, it is no longer > a good idea to delete things directly (for example, by calling > heap_drop_with_catalog or even just heap_delete'ing a catalog tuple). > > The correct thing to do is to call performDeletion() with a parameter Should it be called performDrop rather than Deletion? > The payoff for this seeming extra complexity is that we can get rid of > a lot of former hard-wired code in favor of letting dependencies do it. > For instance, heap_drop_with_catalog no longer does anything directly > about deleting indexes, constraints, or type tuples --- that's all > gotten rid of by dependency links when you do a DROP TABLE. Thus > heap.c is about 300 lines shorter than it used to be. We also have > much more control over whether to allow deletions of dependent objects. > For instance, you now get fairly sane behavior when you try to drop > the pg_type entry associated with a relation: Yes, this code now allows lots of cleanups we weren't able to do before. TODO has: Dependency Checking === * Add pg_depend table for dependency recording; use sysrelid, oid, depend_sysrelid, depend_oid, name * Auto-destroy sequence on DROP of table with SERIAL; perhaps a separate SERIAL type * Have SERIAL generate non-colliding sequence names when we have auto-destruction * Prevent column dropping if column is used by foreign key * Propagate column or table renaming to foreign key constraints * Automatically drop constraints/functions when object is dropped * Make constraints clearer in dump file * Make foreign keys easier to identify * Flush cached query plans when their underlying catalog data changes Which of these are done with the patch? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Memo on dropping practices
Now that the pg_depend mechanism is mostly in there, it is no longer a good idea to delete things directly (for example, by calling heap_drop_with_catalog or even just heap_delete'ing a catalog tuple). The correct thing to do is to call performDeletion() with a parameter specifying what it is you want to delete. Object deletion commands should be implemented in two routines: an outer wrapper that looks up the object, verifies permissions, and calls performDeletion, and an inner routine that actually deletes the catalog entry (plus any other directly-associated work). The inner routine is called from performDeletion() after handling any dependency processing that might be needed. A good example to look at is the way RemoveFunction() has been split into RemoveFunction() and RemoveFunctionById(). The payoff for this seeming extra complexity is that we can get rid of a lot of former hard-wired code in favor of letting dependencies do it. For instance, heap_drop_with_catalog no longer does anything directly about deleting indexes, constraints, or type tuples --- that's all gotten rid of by dependency links when you do a DROP TABLE. Thus heap.c is about 300 lines shorter than it used to be. We also have much more control over whether to allow deletions of dependent objects. For instance, you now get fairly sane behavior when you try to drop the pg_type entry associated with a relation: regression=# create table foo(f1 int); CREATE TABLE regression=# drop type foo; ERROR: Cannot drop type foo because table foo requires it You may DROP the other object instead I notice that Tatsuo recently committed DROP CONVERSION code that does things the old way. I didn't try to change it, but as-is it will not work to have any dependencies leading to or from conversions. I recommend changing it so that it can participate in dependencies. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
Thomas Lockhart wrote:
> (crossposted to -hackers, should follow up on that list)
> OK, this is in the "can't do it what we have" category. Should we have
> it accept a regular expression rather than a simple string? In either
> case it should probably go into the main distro. Except that I see
> "REPLACE" is mentioned as a reserved word in SQL99. But has no other
> mention in my copy of the draft standard. Anyone else have an idea what
> it might be used for in the standard?
Not sure, but I see what you mean. Perhaps because of Oracle pushing to
legitimize the "CREATE OR REPLACE" syntax? In any case, this works in 8i:
SQL> select replace('hello','l','x') from dual;
REPLACE('HELLO','L','X')
hexxo
and here it is in MSSQL 7:
select replace('hello','l','x')
hexxo
(1 row(s) affected)
and my proposed PostgreSQL function:
test=# select replace('hello','l','x');
replace
-
hexxo
(1 row)
so at least we would be consistant/compatable with these two.
>
> The other functions look useful too, unless to_char() and varbit can be
> evolved to support this functionality.
I will take a look at merging these into existing functions, but I have
a few other things ahead of this in my queue.
One of the reasons I wasn't pushing too hard to get replace() into the
backend is because my current solution is a bit of a hack. It uses the
builtin length, strpos and substr text functions (which I think makes
sense since they already know how to deal with mb strings), but because
they accept and return text, I'm doing lots of conversions back and
forth from (* text) to (* char). To do this "right" probably means
reworking the text string manipulation functions to be wrappers around
some equivalent functions accepting and returning C strings. That was
more work than I had time for when I wrote the current replace(). But as
I said, if there is support for getting this into the backend, I'll add
it to my todo list:
- Create new backend function replace()
- Either create new backend functions, or merge into existing functions:
to_hex() and extract_tok()
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] test data for query optimizer
[EMAIL PROTECTED] (Neil Conway) writes: > I'd like to look at the performance of the query optimizer (both the > traditional one and GEQO) when joining large numbers of tables: 10-15, > or more. In order to do that (and to get meaningful results), I'll > need to work with some data that actually requires joins of that > magnitude. The easiest way to construct a realistic many-way join is to use a star schema. Here you have a primary "fact table" that includes a lot of columns that individually join to the primary keys of other "detail tables". For example, you might have a column "State" in the fact table with values like "PA", "NY", etc, and you want to join it to a table states(abbrev,fullname,...) so your query can display "Pennsylvania", "New York", etc. It's easy to make up realistic examples that involve any number of joins. This is of course only one usage pattern for lots-o-joins, so don't put too much credence in it alone as a benchmark, but it's certainly a widely used pattern. Searching for "star schema" at Google turned up some interesting things last time I tried it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CHAR constants
Thomas Lockhart <[EMAIL PROTECTED]> writes: > So, what should the behavior be of a constant declared as > CHAR 'hi' > ? Right now it fails, since SQL9x asks that the char type defaults to a > length of one and our parser does not distinguish between usage as a > constant declaration and as a column definition (where you would want > the "char(1)" to be filled in). But istm that for a constant string, the > length should be whatever the string is, or unspecified. Seems we should convert that to char(2). Not sure how difficult it is to do though... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] test data for query optimizer
I'd like to look at the performance of the query optimizer (both the traditional one and GEQO) when joining large numbers of tables: 10-15, or more. In order to do that (and to get meaningful results), I'll need to work with some data that actually requires joins of that magnitude. Ideally, I'd like the data to be somewhat realistic -- so that the performance I'm seeing will reflect the performance a typical user might see. (i.e. I don't want an artificial benchmark) However, I don't possess any data of that nature, and I'm unsure where I can find some (or how to generate some of my own). Does anyone know of: - a freely available collection of data that requires queries of this type, and is reasonably representative of "real world" applications - or, a means to generate programatically some data that fits the above criteria. Thanks in advance, Neil -- Neil Conway <[EMAIL PROTECTED]> PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] CHAR constants
So, what should the behavior be of a constant declared as CHAR 'hi' ? Right now it fails, since SQL9x asks that the char type defaults to a length of one and our parser does not distinguish between usage as a constant declaration and as a column definition (where you would want the "char(1)" to be filled in). But istm that for a constant string, the length should be whatever the string is, or unspecified. Comments? - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] string cast/compare broken?
On Fri, Jul 12, 2002 at 03:48:59PM +0200, Zeugswetter Andreas SB SD wrote: > Imho the advantages of an automatic coercion would outweigh the few > corner cases where the behavior would not be intuitive to > everybody. How then would one get the correct behaviour from char()? A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug of PL/pgSQL parser
"eutm" <[EMAIL PROTECTED]> writes: > Dear Sirs!:)I encounted one small problem,working with > PostgreSQL 7.3devel.It can look a > bit strange,but i have to use whitespaces in names of databases,tables,fields > and so on(like "roomno jk").It's possible to create them all and work with them > (INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such > statements. Yeah, this is a known bug: the plpgsql lexer doesn't really handle quoted identifiers correctly. (It effectively acts like double-quote is just another letter, which of course falls down on cases like embedded whitespace.) If you have any experience with writing flex rules, perhaps you'd care to submit a patch. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] workaround for lack of REPLACE() function
(crossposted to -hackers, should follow up on that list)
> Well, OVERLAY is defined as:
> overlay(string placing string from integer [for integer])
> and replace() is defined (by me at least) as:
> replace(inputstring, old-substr, new-substr)
OK.
> OVERLAY requires that I know the "from" position and possibly the "for"
> in advance. Other functions (such as strpos() and substr()) can be used
> to help...
Right. So you can do your example pretty easily:
thomas=# select overlay(f1 placing '' from position('/local' in f1)
thomas-# for length('/local')) from strtest;
overlay
/usr/pgsql/data
/m1/usr/pgsql/data
And if you don't like that much typing you can do:
thomas=# create function replace(text, text, text) returns text as '
thomas'# select overlay($1 placing $3 from position($2 in $1) for
length($2));
thomas'# ' language 'sql';
CREATE FUNCTION
thomas=# select replace(f1, '/local', '') from strtest;
replace
/usr/pgsql/data
/m1/usr/pgsql/data
> But now what happens if you wanted to replace all of the '/' characters
> with '\'?...
> You can't do this at all with overlay(), unless you want to write a
> PL/pgSQL function and loop through each string. I started out with
> exactly this, using strpos() and substr(), but I thought a C function
> was cleaner, and it is certainly faster.
OK, this is in the "can't do it what we have" category. Should we have
it accept a regular expression rather than a simple string? In either
case it should probably go into the main distro. Except that I see
"REPLACE" is mentioned as a reserved word in SQL99. But has no other
mention in my copy of the draft standard. Anyone else have an idea what
it might be used for in the standard?
The other functions look useful too, unless to_char() and varbit can be
evolved to support this functionality.
- Thomas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Changes in /contrib/fulltextindex
Florian Helmberger wrote: > Hi. > > > Florian, I haven't seen this patch yet. Did you send it in? > > Yes, I sent it to Christopher for reviewing, as allready mentioned by > himself :) > I still had not the time to update the docs though, hope to get this done > next week. Yes, I had an email exchange with Christopher last night and he is working on the backward compatibility issues with overloaded function parameters. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can I have multiple cursors open ...
> Is this possible ? Sure. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] string cast/compare broken?
There is no comparison of varchar to char in Oracle too. Scott provided cast cases are some unique features in psql, each database MAY handle those casting differently. In good design/application, char should be replaced by varchar type unless you know the exact bytes. It would be not bad idea to get rid of char gradually in the future to avoid such inconsistency between databases, that's just my view. johnl > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Zeugswetter > Andreas SB SD > Sent: Friday, July 12, 2002 8:49 AM > To: Tom Lane > Cc: Hannu Krosing; Scott Royston; [EMAIL PROTECTED] > Subject: Re: [HACKERS] string cast/compare broken? > > > > > Has anyone studied how other DBMSs handle CHAR vs VARCHAR? Judging > > from the number of questions we get on this point, I have to wonder > > if we are not out of step with the way other systems do it. > > Well, I already gave the Informix example, that compares them as equal. > (they obviously coerce varchar to char) > > In nearly all cases I have seen so far the different handling of trailing > blanks is not wanted. In most of these varchar is simply used > instead of char to > save disk space. > > In Informix ESQL/C there is a host variable type CSTRINGTYPE that > automatically > rtrims columns of char type upon select. > > Imho the advantages of an automatic coercion would outweigh the > few corner cases > where the behavior would not be intuitive to everybody. > > Andreas > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] string cast/compare broken?
On Fri, 12 Jul 2002, Tom Lane wrote: > "Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: > >> If the conversion where varchar(5) --> char(5) then > >> they would compare equal. > > > I am not sure, since, if the varchar stored 'S ' then the comparison > > to a char 'S' should probably still fail, > > There is no comparison of varchar to char: > > regression=# select 'z'::char = 'z'::varchar; > ERROR: Unable to identify an operator '=' for types 'character' and 'character >varying' > You will have to retype this query using an explicit cast > regression=# > > I consider this a feature, not a bug, since it's quite unclear which > semantics ought to be used. > > The cases Scott originally posted all involved various forms of > coercion to force both sides to be the same type; I'm not sure > that he quite understood why he had to do that, but perhaps it's now > becoming clear. > > I wonder whether it would be a good idea to stop considering char > as binary-compatible to varchar and text. Instead we could set > things up so that there is a coercion function involved, namely > rtrim(). But that would probably make us diverge even further > from the spec. > > Has anyone studied how other DBMSs handle CHAR vs VARCHAR? Judging > from the number of questions we get on this point, I have to wonder > if we are not out of step with the way other systems do it. I don't think it's just a CHAR vs VARCHAR issue. AFAICT the spec defines all of this in terms of the collations used and there are (imho arcane) rules about converting between them for comparisons and operations. Technically I think varcharcol=charcol *is* illegal if we are saying that char has a collation with PAD SPACE and varchar has a collation with NO PAD, because they're different collations and character value expressions from column reference are implicit and that doesn't allow comparison between two different collations. Of course I could also be misreading it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] string cast/compare broken?
> Has anyone studied how other DBMSs handle CHAR vs VARCHAR? Judging > from the number of questions we get on this point, I have to wonder > if we are not out of step with the way other systems do it. Well, I already gave the Informix example, that compares them as equal. (they obviously coerce varchar to char) In nearly all cases I have seen so far the different handling of trailing blanks is not wanted. In most of these varchar is simply used instead of char to save disk space. In Informix ESQL/C there is a host variable type CSTRINGTYPE that automatically rtrims columns of char type upon select. Imho the advantages of an automatic coercion would outweigh the few corner cases where the behavior would not be intuitive to everybody. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] string cast/compare broken?
"Zeugswetter Andreas SB SD" <[EMAIL PROTECTED]> writes: >> If the conversion where varchar(5) --> char(5) then >> they would compare equal. > I am not sure, since, if the varchar stored 'S ' then the comparison > to a char 'S' should probably still fail, There is no comparison of varchar to char: regression=# select 'z'::char = 'z'::varchar; ERROR: Unable to identify an operator '=' for types 'character' and 'character varying' You will have to retype this query using an explicit cast regression=# I consider this a feature, not a bug, since it's quite unclear which semantics ought to be used. The cases Scott originally posted all involved various forms of coercion to force both sides to be the same type; I'm not sure that he quite understood why he had to do that, but perhaps it's now becoming clear. I wonder whether it would be a good idea to stop considering char as binary-compatible to varchar and text. Instead we could set things up so that there is a coercion function involved, namely rtrim(). But that would probably make us diverge even further from the spec. Has anyone studied how other DBMSs handle CHAR vs VARCHAR? Judging from the number of questions we get on this point, I have to wonder if we are not out of step with the way other systems do it. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug of PL/pgSQL parser
I see this on the TODO list:
# Fix PL/PgSQL to handle quoted mixed-case identifiers
Perhaps you could make a view (alias the names with spaces) to work on?
On Fri, 2002-07-12 at 06:31, eutm wrote:
>
> Dear Sirs!:)I encounted one small problem,working with
> PostgreSQL 7.3devel.It can look a
> bit strange,but i have to use whitespaces in names of databases,tables,fields
> and so on(like "roomno jk").It's possible to create them all and work with them
> (INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such
> statements.To explain the problem, I took and changed next examples from
> pgsql/src/pl/plpgsql/test:
>
> --
> -- * Tables for the patchfield test of PL/pgSQL
> -- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/tables.sql,v 1.1
>1998/08/24 19:16:27 momjian Exp $
> --
>
> create table Room (
> "roomno jk" char(8), --- common SQL parser eats it
> comment text
> );
> create unique index Room_rno on Room using btree ("roomno jk" bpchar_ops);
>
> create table WSlot (
> slotname char(20),
> "roomno jk" char(8), --- common SQL parser eats it
> slotlink char(20),
> backlink char(20)
> );
> create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
>
> You also can use such "roomno jk" in DECLARATION of PL/pgSQL procedures and
>functions :
>
> --
> -- * Trigger procedures and functions for the patchfield
> -- * test of PL/pgSQL
> -- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/triggers.sql,v 1.2
>2000/10/22 23:25:11 tgl Exp $
> --
> -- * AFTER UPDATE on Room
> -- * - If room no changes let wall slots follow
> --
>
> PL/pgSQL eats it,he will cry during execution.
>
> create function tg_room_au() returns opaque as '
> begin
> if new."roomno jk" != old."roomno jk" then
> update WSlot set "roomno jk" = new."roomno jk" where "roomno jk" =
>old."roomno jk";
> end if;
> return new;
> end;
> ' language 'plpgsql';
>
> create trigger tg_room_au after update
> on Room for each row execute procedure tg_room_au();
>
> --
> -- * BEFORE INSERT or UPDATE on WSlot
> -- * - Check that room exists
> --
>
> PL/pgSQL also eats it,he will cry during execution.
>
> create function tg_wslot_biu() returns opaque as '
> begin
> if count(*) = 0 from Room where "roomno jk" = new."roomno jk" then
> raise exception ''Room % does not exist'', new."roomno jk";
> end if;
> return new;
> end;
> ' language 'plpgsql';
>
>
> create trigger tg_wslot_biu before insert or update
> on WSlot for each row execute procedure tg_wslot_biu();
>
> Then do next:
> insert into Room values ('001', 'Entrance'); --Everything is ok
>
> Then do it and catch failure:
> insert into WSlot values ('WS.001.1a', '001', '', '');
>
> PostgreSQL returns :
>
> psql:/home/eu/SQL/plt/p_test.sql:19: ERROR: parse error at or near "new"
> psql:/home/eu/SQL/plt/p_test.sql:20: WARNING: plpgsql: ERROR during compile of
>tg_wslot_biu near line 3
>
> As you see there's no support for "roomno jk" in PL/pgSQL parser.
> To this moment i know nothing serious about flex,lex and yacc,but
> a simple look at PL/pgSQL parser shows,that situations of
> "roomno jk" are just undefined there.
> regards,Eugene
> P.S.In case you make patch,please,send me a copy.
>
>
>
>
>
>
>
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] urgent needed
On Fri, 12 Jul 2002, [EMAIL PROTECTED] wrote: > Hi > Were two doctoring students and we have a little problem to resolve. > Were using Grass5pre3 and PostgreSQL 7.2 (Linux)to map vehicular > pollution of our city. We have a map of the streets and we have to > assign 24 values (+ the label) to each street. > What would be a smart way to solve this problem using Postgres? > Thanx a lot for your help, Alberto & Massimo. > I'd use contrib/intarray module, which was developed exactly for such kind of problems (in our case we have messages assigned to several sections) Regards, Oleg > > > Salve. > Siamo due studenti laureandi e avremmo un problema da risolvere. > Stiamo utilizzando Grass5pre3 e PostgreSQL 7.2 (Linux)per realizzare > una mappatura dellinquinamento veicolare nella nostra città. > Abbiamo una mappa delle vie e dobbiamo assegnare ad ogni via 24 valori > (più letichetta). > Qual e il modo più furbo e veloce per farlo, utilizzando Postgres? > Grazie Mille per laiuto, Alberto & Massimo. > > > > > > > -- > Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f > > Sponsor: > Conto Arancio. Zero spese, stessa banca, più interessi. > Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=657&d=12-7 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Can I have multiple cursors open ...
.. inside of each other using ECPG ? I have a situation where it would be advantages to open a cursor, retrieve a tuple, then open another query based on the results of the first. Then when that query has been processed return to the first query and get the second tuple. Is this possible ? cheers, Jim Parker ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Bug of PL/pgSQL parser
Dear Sirs!:)I encounted one small problem,working with
PostgreSQL 7.3devel.It can look a
bit strange,but i have to use whitespaces in names of databases,tables,fields
and so on(like "roomno jk").It's possible to create them all and work with them
(INSERT,DELETE,UPDATE),but PL/pgSQL parser(compiler ?) can't execute such
statements.To explain the problem, I took and changed next examples from
pgsql/src/pl/plpgsql/test:
--
-- * Tables for the patchfield test of PL/pgSQL
-- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/tables.sql,v 1.1 1998/08/24
19:16:27 momjian Exp $
--
create table Room (
"roomno jk" char(8), --- common SQL parser eats it
comment text
);
create unique index Room_rno on Room using btree ("roomno jk" bpchar_ops);
create table WSlot (
slotnamechar(20),
"roomno jk" char(8), --- common SQL parser eats it
slotlinkchar(20),
backlinkchar(20)
);
create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
You also can use such "roomno jk" in DECLARATION of PL/pgSQL procedures and functions :
--
-- * Trigger procedures and functions for the patchfield
-- * test of PL/pgSQL
-- * $Header: /projects/cvsroot/pgsql/src/pl/plpgsql/test/triggers.sql,v 1.2
2000/10/22 23:25:11 tgl Exp $
--
-- * AFTER UPDATE on Room
-- *- If room no changes let wall slots follow
--
PL/pgSQL eats it,he will cry during execution.
create function tg_room_au() returns opaque as '
begin
if new."roomno jk" != old."roomno jk" then
update WSlot set "roomno jk" = new."roomno jk" where "roomno jk" = old."roomno
jk";
end if;
return new;
end;
' language 'plpgsql';
create trigger tg_room_au after update
on Room for each row execute procedure tg_room_au();
--
-- * BEFORE INSERT or UPDATE on WSlot
-- *- Check that room exists
--
PL/pgSQL also eats it,he will cry during execution.
create function tg_wslot_biu() returns opaque as '
begin
if count(*) = 0 from Room where "roomno jk" = new."roomno jk" then
raise exception ''Room % does not exist'', new."roomno jk";
end if;
return new;
end;
' language 'plpgsql';
create trigger tg_wslot_biu before insert or update
on WSlot for each row execute procedure tg_wslot_biu();
Then do next:
insert into Room values ('001', 'Entrance'); --Everything is ok
Then do it and catch failure:
insert into WSlot values ('WS.001.1a', '001', '', '');
PostgreSQL returns :
psql:/home/eu/SQL/plt/p_test.sql:19: ERROR: parse error at or near "new"
psql:/home/eu/SQL/plt/p_test.sql:20: WARNING: plpgsql: ERROR during compile of
tg_wslot_biu near line 3
As you see there's no support for "roomno jk" in PL/pgSQL parser.
To this moment i know nothing serious about flex,lex and yacc,but
a simple look at PL/pgSQL parser shows,that situations of
"roomno jk" are just undefined there.
regards,Eugene
P.S.In case you make patch,please,send me a copy.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] urgent needed
Hi Were two doctoring students and we have a little problem to resolve. Were using Grass5pre3 and PostgreSQL 7.2 (Linux)to map vehicular pollution of our city. We have a map of the streets and we have to assign 24 values (+ the label) to each street. What would be a smart way to solve this problem using Postgres? Thanx a lot for your help, Alberto & Massimo. Salve. Siamo due studenti laureandi e avremmo un problema da risolvere. Stiamo utilizzando Grass5pre3 e PostgreSQL 7.2 (Linux)per realizzare una mappatura dellinquinamento veicolare nella nostra città. Abbiamo una mappa delle vie e dobbiamo assegnare ad ogni via 24 valori (più letichetta). Qual e il modo più furbo e veloce per farlo, utilizzando Postgres? Grazie Mille per laiuto, Alberto & Massimo. -- Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f Sponsor: Conto Arancio. Zero spese, stessa banca, più interessi. Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=657&d=12-7 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Changes in /contrib/fulltextindex
Hi. > Florian, I haven't seen this patch yet. Did you send it in? Yes, I sent it to Christopher for reviewing, as allready mentioned by himself :) I still had not the time to update the docs though, hope to get this done next week. Florian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] string cast/compare broken?
> I guess the strangest part was that both a.foo = 'S' and b.foo = 'S' but > not a.foo=b.foo; (a.foo is varchar(5) , b.foo is char(5) ) > > I guess that tha 'S' that b.foo gets compared to is converted to 'S' > before comparison but when comparing varchar(5) and char(5) they are > both compared by converting them to varchar which keeps the trailing > spaces from char(5). Yes, I think this is inconvenient/unintuitive. If it is doable according to standards, this should imho be fixed. > If the conversion where varchar(5) --> char(5) then > they would compare equal. I am not sure, since, if the varchar stored 'S ' then the comparison to a char 'S' should probably still fail, since those spaces in the varchar are significant. Informix compares them equal, so I guess argumentation can be made in that direction too (that currently evades my understanding of intuitive reasoning :-). Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
