Re: [HACKERS] request for sql3 compliance for the update command
-Original Message- From: Mike Aubury [mailto:[EMAIL PROTECTED]] Sent: 20 February 2003 19:10 To: Dave Page; Tom Lane; Hannu Krosing Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers Subject: Re: [HACKERS] request for sql3 compliance for the update command Informix supports 2 different styles for the update - your one would have to be written : UPDATE djp SET(col1, col2) = ((SELECT col1,col2 FROM some_other_table)) Notice the double brackets ! The first signifies a list of values - the second is the brackets around the subquery... (NB If you try to reference the same table in the Update - you'll get an error) Ahh, of course. I tried double brackets 'cos I figured I might need one pair to indicate the set and one to indicate the subselect, but I didn't think to try a different table. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Loss of cluster status
Hi, The new cluster is cool in that : 1. It works 2. It saves the indisclustered status However, after a dump and restore, this indisclustered status will be lost. Would it be an idea to issue a CLUSTER command after the CREATE TABLE statement in SQL dumps for tables that have an indisclustered index? Chris ---(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] Loss of cluster status
The new cluster is cool in that : 1. It works 2. It saves the indisclustered status However, after a dump and restore, this indisclustered status will be lost. Would it be an idea to issue a CLUSTER command after the CREATE TABLE statement in SQL dumps for tables that have an indisclustered index? Actually, rather than a full-blown CLUSTER, how about...: UPDATE pg_index SET indisclustered=true WHERE indrelid=(SELECT oid FROM pg_class WHERE relname='mytable' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname=CURRENT_SCHEMA())); Hmmm...need something for index name as well tho... Is that an idea? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ecpg vs. libpq
I am wondering if there is any difference in performance between using ecpg and libpq. If I understand the concept of ecpg correctly, calls to the lecpg interface are internally converted to calls to libpq. So there is no big difference at all. Is this right? Regards, Christoph ---(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] ecpg vs. libpq
On 21 Feb 2003 at 11:26, Christoph Haller wrote: I am wondering if there is any difference in performance between using ecpg and libpq. If I understand the concept of ecpg correctly, calls to the lecpg interface are internally converted to calls to libpq. So there is no big difference at all. Is this right? That is correct but there are some other differences. 1. Obviously ecpg is simpler to use. 2. libpq is almost thread safe. ecpg is not. It is work in progress. 3. ecpg can not be C++ in itself, libpq can be. Just about it.. Bye Shridhar -- Drew's Law of Highway Biology: The first bug to hit a clean windshield lands directly in front of your eyes. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ecpg vs. libpq
I am wondering if there is a fundamental difference in performance between using embedded SQL or libpq functions in a C application. If I understand the documentation correctly, calls to lecpg are simply transferred to calls to libpq. So, the difference in performance is, if any, marginal. Is this right? Regards, Christoph PS Sorry if this comes twice, the local mail host seems to be in trouble. ---(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] contrib Makefile's and OS X
Adam Witney wrote: PL/R compiles and installs ok on my OS X 10.2.4, the corresponding line is gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -flat_namespace -bundle -undefined suppress plr.o pg_conversion.o pg_backend_support.o pg_userfuncs.o pg_rsupport.o -L/sw/lib -L/sw/lib/R/bin -lR -o libplr.so.0.0 Thanks Adam, Peter, and Tom for your replies. I'll make the minor change Tom recommended, try to find out what distribution the person is using, and recommend they switch to the standard one. Joe ---(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] Loss of cluster status
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Would it be an idea to issue a CLUSTER command after the CREATE TABLE statement in SQL dumps for tables that have an indisclustered index? Yeah... Actually, rather than a full-blown CLUSTER, how about...: UPDATE pg_index SET indisclustered=true WHERE indrelid=(SELECT oid FROM pg_class WHERE relname='mytable' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname=CURRENT_SCHEMA())); No; directly manipulating the system catalogs in dump scripts is a crummy idea, because (a) it only works if you're superuser, and (b) it creates a nasty backwards-compatibility problem if we change the catalogs involved. A CLUSTER command issued just after table creation, while it's still empty, would be cheap ... but we don't put the index in place until we've loaded the data, do we? Darn. 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] possibly spurious `EXCEPT ... may not refer to other relation...'
Brandon Craig Rhodes [EMAIL PROTECTED] writes: The current CVS version of PostgreSQL gives us the error: ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level when given the following test case, despite the fact that the EXCEPT clause does not refer to any other relation involved in the same query. CREATE OR REPLACE RULE current_delete AS ON DELETE TO current DO INSERT INTO former (number) SELECT number FROM current WHERE number = OLD.number EXCEPT SELECT number FROM trash; But in fact OLD is a relation reference. You have to remember that when you issue, say, DELETE FROM current WHERE date '2002-02-01' (I'm just making up an example of a WHERE-condition here), the rule action gets rewritten to something like INSERT INTO former (number) SELECT current.number FROM current, current OLD WHERE current.number = OLD.number AND OLD.date '2002-02-01' I've left off the EXCEPT part in my example of the rewritten query, because I'm not sure where the OLD reference could get put if the rule action involves an EXCEPT. The code doesn't know either :-( You may find that the most practical way to handle this requirement is to put the insertion-into-former command into a trigger procedure rather than a rule. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib Makefile's and OS X
Joe Conway writes: Below is the Makefile. The key problem is that I need to get a bundle built instead of a dynamiclib, or so I am told. The PostgreSQL makefiles do use bundle. Tell the user to use the standard distribution. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] fixups for 7.3 to contrib directories
Just some fixups to a couple contrib directories I was trying out. . replace CREATE OR REPLACE AGGREGATE with a separate DROP and CREATE . add DROP for all CREATE OPERATORs . use IMMUTABLE and STRICT instead of WITH (isStrict) . add IMMUTABLE and STRICT to int_array_aggregate's accumulator function diff Description: Binary data diff Description: Binary data -- greg ---(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] ecpg vs. libpq
On Fri, Feb 21, 2003 at 11:26:02AM +0100, Christoph Haller wrote: I am wondering if there is any difference in performance between using ecpg and libpq. If I understand the concept of ecpg correctly, calls to the lecpg interface are internally converted to calls to libpq. That's correct. There shouldn't be much of a performance penalty. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] request for sql3 compliance for the update command
On Thu, 20 Feb 2003, Kevin Brown wrote: Tom Lane wrote: UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; As long as any individual item that you can express in the parenthesized (Informix) syntax can also be expressed as an element in a SELECT, then the above is equivalent in every way to the Informix syntax. And since SELECT allows subselects, it seems to me that the PG syntax is complete. My question is whether or not there's likely to be an approved standard way of accomplishing what either syntax does. Is there anything in the current draft that addresses this? Yes there is. I've posted the URL on the hackers list a while back, but here it is again: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf pp 851 to 862, in particular, p 858 defines the the set clause list as supporting multiple column assignment as supporting something like: (target1, target2, target3) = (value1, value2, value3) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Postgres and 'directio' on Solaris/UFS
So I'm reading Sun's _Configuring Tuning Databases on the Solaris
Platform_ book (ISBN: 0-13-083417-3). One of the first things mentioned
in the book is that UFS is, well, slow. Especially because of how
Solaris utilizes it. The authors contend that enabling directio(3C) can
increase performance substantially. It can lead to problems with
filesystems where strange things are done with files. Since databases
tend to manage their files very well to begin with, the authors say
this isn't normally a problem.
I searched the web for it, but all I found was some references to Zend.
Additionally, I grepped through source only to realize that direction
is used a lot. So I used the following find :
[goro:~/postgresql-7.3.1] alex% find . -type f -exec egrep -il
'directio[^Nn]' {} \;
And also didn't find anything. The manpage for directio (Solaris 9) is
here:
http://docs.sun.com/db/doc/816-0213/6m6ne37so?a=view
I haven't gotten my ultrasparc database server up yet, so I can't run
any benchmarks against this. Is it possible somebody with some spare
time could mount a filesystem with directio forced (forcedirectio) and
run some benchmarks on a Solaris/UFS database?
Thanks
alex
--
alex avriette, unix geek for hire
http://envy.posixnap.net/
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib Makefile's and OS X
PL/R compiles and installs ok on my OS X 10.2.4, the corresponding line is
gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes
-Wmissing-declarations -flat_namespace -bundle -undefined suppress plr.o
pg_conversion.o pg_backend_support.o pg_userfuncs.o pg_rsupport.o -L/sw/lib
-L/sw/lib/R/bin -lR -o libplr.so.0.0
adam
I've written PL/R to make use of the contrib build system, and modelled
its Makefile after other contrib modules. One user who tried installing
PL/R under OS X sent me this:
The makefile does
gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes
-Wmissing-declarations -fno-common -install_name
/usr/local/pgsql/lib/libplr.0.dylib -dynamiclib plr.o
pg_conversion.o pg_backend_support.o pg_userfuncs.o pg_rsupport.o
-L../../src/interfaces/libpq -L/usr/local/lib/R/bin -lR -o
libplr.0.0.dylib
In OS X this should be
gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes
-Wmissing-declarations -fno-common -bundle -flat_namespace -undefined
suppress plr.o pg_conversion.o pg_backend_support.o pg_userfuncs.o
pg_rsupport.o -L../../src/interfaces/libpq -L/usr/local/lib/R/bin -lR
-o plr.so
Below is the Makefile. The key problem is that I need to get a bundle
built instead of a dynamiclib, or so I am told.
Any idea what I'm doing wrong?
Thanks,
Joe
8-
r_libdir = ${R_HOME}/bin
r_includespec = ${R_HOME}/include
subdir = contrib/plr
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
override CPPFLAGS := -I$(srcdir) -I$(r_includespec) $(CPPFLAGS)
override CPPFLAGS += -DPKGLIBDIR=\$(pkglibdir)\ -DDLSUFFIX=\$(DLSUFFIX)\
rpath :=
MODULE_big := plr
PG_CPPFLAGS := -I$(r_includespec)
SRCS+= plr.c pg_conversion.c pg_backend_support.c
pg_userfuncs.c pg_rsupport.c
OBJS:= $(SRCS:.c=.o)
SHLIB_LINK := -L$(r_libdir) -lR
DATA_built := plr.sql
DOCS:= README.plr
REGRESS := plr
EXTRA_CLEAN := doc/HTML.index
include $(top_srcdir)/contrib/contrib-global.mk
8-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
---(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] Simplifying timezone support
On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: question about pgsql's time zone parsers. It appears there's at least two, since SET TIME ZONE accepts strings like 'US/Eastern', while general timestamp parsing doesn't: The TIME ZONE string is fed to libc (via TZ environment variable); the other cases are not. SET TIME ZONE will silently accept any string at all, and fall back to providing GMT when a timestamptz is requested. Provide a portable way of getting libc to tell us whether it likes TZ, and I'll be glad to fix this. Dang that lovely word 'portable'. However, given your proposed change, perhaps the hurdle for portable time handling is now lower: it seems we've not been exposed to as broad a range of broken systems as in the past. I'll look at it. but no promises. Ultimately we should probably get rid of our dependence on the libc time routines altogether ... but I have no intention of opening that can of worms right now. See past discussions in the archives. Agreed. I see we're inheriting the actually misleading case from the OS/libc, as well: wallace$ unset TZ wallace$ date Thu Feb 20 15:00:04 CST 2003 wallace$ export TZ=US/Central wallace$ date Thu Feb 20 15:00:16 CST 2003 wallace$ export TZ=US/Zanzibar wallace$ date Thu Feb 20 21:00:33 US/Zanzibar 2003 wallace$ export TZ=CST wallace$ date Thu Feb 20 21:00:42 CST 2003 wallace$ Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Simplifying timezone support
Ross J. Reedstrom [EMAIL PROTECTED] writes: If the time zone came back UNKOWN, we go ahead and see if tzset() can interpret it. Criteria for failure: if the timezone offset came back 0, and the reported tzname[0] is the same as the string that we passed in. If it does, we fire a NOTICE about an unknown spelling of GMT. Note that we would have already caught all _known_ spellings of GMT in the first step, so we won't be spamming the DBA with warnings about 'GMT' and 'UTC', etc. I'm worried about cases like Africa/Benin for places that just happen to be on the prime meridian, but don't call their time GMT or UTC. Looking at a globe, it also seems possible that there are places an hour west of Greenwich, for which this could fail during daylight-savings season. An extension to this would be to use the tzset() trick above directly in the datetime constant parser, as a fallback after not matching the table. In that case, we'd probably want to treat the unknown spelling of GMT as an error, though (as it currently does). I think tzset() is probably much too slow to consider calling on every pass through timestamptz_in ... 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
[HACKERS] regression failure - horology
I'm seeing a regression failure on the horology test on two different machines. I'd venture a guess that it is related to this change: http://archives.postgresql.org/pgsql-committers/2003-02/msg00166.php Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] SSL warning in CVS HEAD
When compiling CVS HEAD with SSL enabled, I get this warning: be-secure.c: In function `open_server_SSL': be-secure.c:707: warning: assignment from incompatible pointer type The code in question is: /* set up debugging/info callback */ SSL_CTX_set_info_callback(SSL_context, info_cb); Can someone send in a patch fixing this? (I'd fix it myself, but I know nothing about OpenSSL) BTW, this is using CFLAGS='-Wall -O0', with OpenSSL 0.9.7 Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] ILIKE
AFAICT, ILIKE cannot use an index. So why does ILIKE even exist, when lower(expr) LIKE 'foo' provides a solution that can use an index and is more standard, too? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bytea misconceptions
Peter Eisentraut writes: Example: Create a cluster with non-C CTYPE, create a LATIN1 database, create a table with a bytea column, and store something with non-ASCII characters in it. Then change the client encoding (to UNICODE, say) and read the data. I stored 'ätsch bätsch' and got 'ätsch bätsch', which is not a suitable result for bytea data. Another point that occured to me is that if you send bytea input that does not exclusively contain escape sequences to the server, then you really don't know what the server will store. Since character set conversion is supposed to be transparent, the bytea type is broken from the ground up and should be replaced (probably by the standard blob type). -- Peter Eisentraut [EMAIL PROTECTED] ---(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
