Re: [HACKERS] [PATCHES] Non-colliding auto generated names
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
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] request for sql3 compliance for the update command
Tom Lane <[EMAIL PROTECTED]> writes: > UPDATE totals SET > xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), > ... > > but that is awfully tedious and will be inefficiently implemented. This > is what Bruce is worried about. On the other hand, one could argue that > this is a wrongheaded way to go about it anyway, and the correct way is > > 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; ... > Of course this syntax isn't standard either ... but we already have it. This is nice, but I could see it being a big pain if the join clause wasn't so neat and tidy as a groupid column that you can group by. The Informix syntax has some appeal -- speaking from the point of view of someone who has had to write some awkward update statements like this in the past. (In Oracle where the best syntax is to create an updatable inline view which is pretty much equivalent in expressiveness to the Postgres syntax.) Consider how awkward this query would be if the iterations in the original query overlapped for example. You would have to introduce a another table to the select just to drive the join artificially. For example consider a hypothetical case: UPDATE networks set num_hosts = (select count(*) from hosts where addr << netblock) Where some hosts are on multiple nested netblocks. The only way I see to convert that to Postgres's syntax would be to join against the networks table again and then group by the primary key of the networks table. Ick. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> IIRC, the SET does *not* start a transaction, > > > Yes but doesn't autocommit-off mode mean that > > it implicitly begins a transaction in suitable > > places ? For example, 'set autocommit to off; > > declare .. cursor ..' works though it never > > work without BEGIN under autocommit-on mode. > > But the DECLARE would start a transaction --- AFAIR, Yes it's only because the behavior is useful for us. So isn't the problem if the warning message for 'set autocommit to off;commit' is useful or not ? IMHO it's rather a harmful message. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] contrib Makefile's and OS X
Joe Conway <[EMAIL PROTECTED]> 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. It's quite likely that the problem is in Makefile.shlib and isn't specific to plr at all. Ask the complainant if plperl, pltcl, or plpython work. If it is specific to plr, the only idea I have is that maybe you need to say SHLIB_LINK += not SHLIB_LINK := regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> IIRC, the SET does *not* start a transaction, > Yes but doesn't autocommit-off mode mean that > it implicitly begins a transaction in suitable > places ? For example, 'set autocommit to off; > declare .. cursor ..' works though it never > work without BEGIN under autocommit-on mode. But the DECLARE would start a transaction --- AFAIR, pretty much everything except SET, COMMIT, ROLLBACK will start a transaction in autocommit=off mode. I'm not sure what your point is? 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] bug in contrib/adddepend
Hi, I just noticed a bug in adddepend: The below commands will upgrade the foreign key style. Shall I execute them? DROP TRIGGER "RI_ConstraintTrigger_1105102" ON news_authors; DROP TRIGGER "RI_ConstraintTrigger_1105103" ON news_authors; DROP TRIGGER "RI_ConstraintTrigger_1105118" ON news_articles; ALTER TABLE news_articles ADD CONSTRAINT "" FOREIGN KEY (author) REFERENCES news_authors(id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION; See how it's HTMLised the foreign key name? I cannot find how $keyname in the code is being html escaped. Perhaps it's some weird taint mode thing? Rod - you got any ideas? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/adddepend
*sigh* My system was stuffed. Fixed now. Please disregard this post - sorry for spamming the list... Chris - Original Message - From: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> To: "Hackers" <[EMAIL PROTECTED]> Sent: Friday, February 21, 2003 10:28 AM Subject: [HACKERS] contrib/adddepend > When I run adddepend on my FreeBSD system, I get this: > > > /usr/local/bin/adddepend -d usa > install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (@INC contains: > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd > /usr/local/lib/perl5/site_perl/5.005 . /usr/libdata/perl/5.00503/mach > /usr/libdata/perl/5.00503) at (eval 1) line 3. > Perhaps the DBD::Pg perl module hasn't been fully installed, > or perhaps the capitalisation of 'Pg' isn't right. > Available drivers: ExampleP, Proxy, mysql. > at /usr/local/bin/adddepend line 123 > > Pg.pm is here: > > > locate Pg.pm > /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/Pg.pm > > So, it's not in @INC. I presume this is a problem with my system? I'm not > 100% up with my Perl, so what is the workaround for this? Is there > something we can put in adddepend itself, or do I have to hack in something > temporarily to put the correct include path? > > Chris > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] contrib/adddepend
When I run adddepend on my FreeBSD system, I get this: > /usr/local/bin/adddepend -d usa install_driver(Pg) failed: Can't locate DBD/Pg.pm in @INC (@INC contains: /usr/local/lib/perl5/site_perl/5.005/i386-freebsd /usr/local/lib/perl5/site_perl/5.005 . /usr/libdata/perl/5.00503/mach /usr/libdata/perl/5.00503) at (eval 1) line 3. Perhaps the DBD::Pg perl module hasn't been fully installed, or perhaps the capitalisation of 'Pg' isn't right. Available drivers: ExampleP, Proxy, mysql. at /usr/local/bin/adddepend line 123 Pg.pm is here: > locate Pg.pm /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/Pg.pm So, it's not in @INC. I presume this is a problem with my system? I'm not 100% up with my Perl, so what is the workaround for this? Is there something we can put in adddepend itself, or do I have to hack in something temporarily to put the correct include path? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] deleting dependencies
> "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > I've been looking at the dependency API and I notice that there is a > > function to delete ALL dependencies on an object and a function to add a > > dependency, but there doesn't seem to be any way of deleting a dependency > > between two _particular_ objects. > > In the ALTER cases that have been implemented so far, it seemed easiest > to wipe the full set of dependencies and then regenerate them from the > altered object. I think you will find the same is true of altering > column type. To take just one example, the column default expression > (if any) almost certainly has to be replaced too, and it may contain > dependencies. Phase 1 (which I'm currently doing) will do binary-compatible casts only. After that, I have to start using code from cluster.c to rewrite the table methinks...and everything gets a *lot* harder... Chris ---(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
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? -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Anyway should 'set autocommit to off;commit' cause > > a warning or an error in the first place ? > > IIRC, the SET does *not* start a transaction, Yes but doesn't autocommit-off mode mean that it implicitly begins a transaction in suitable places ? For example, 'set autocommit to off; declare .. cursor ..' works though it never work without BEGIN under autocommit-on mode. > so the COMMIT should raise > a warning. regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Anyway should 'set autocommit to off;commit' cause > a warning or an error in the first place ? IIRC, the SET does *not* start a transaction, so the COMMIT should raise a warning. I do not believe that eliminating the warning from COMMIT is a good idea. If we didn't have that warning in place, we'd have not known that we had a bug here. (On the other hand, I'm not in favor of making it a hard error, either.) 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] A bad behavior under autocommit off mode
Tom Lane wrote: > > "Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > > The simplest way seems to accept COMMIT any time under autocommit > > off mode. > > That's just hiding the most visible symptom. The real problem here is > that the SELECT is already committed, when it shouldn't be. The warning means that the transaction is not yet begun before the chained query is issued. The check seems originally for COMMIT without BEGIN under autocommit on mode. It also cancels a transaction for the query '..;..;commit;..' under autocommit on mode. It's also bad because it only reports a warning. Anyway should 'set autocommit to off;commit' cause a warning or an error in the first place ? regards, Hiroshi Inoue http://www.geocities.jp/inocchichichi/psqlodbc/ ---(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] possibly spurious `EXCEPT ... may not refer to other relation...'
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. We suspect this to be a bug. (This simple test case has been drastically reduced from the actual code we want to run, which is a more complicated INSERT INTO SELECT ... EXCEPT whose goal is to avoid inserting rows that are already in the destination table; so whereas here the except clause involves a third, dummy table, we would actually like to check for whether the row exists in the table which is the target of the INSERT.) CREATE TABLE current ( number INTEGER ); CREATE TABLE former ( number INTEGER ); CREATE TABLE trash ( number INTEGER) ; 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; -- Brandon Craig Rhodes http://www.rhodesmill.org/brandon Georgia Tech[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Simplifying timezone support
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote: >> 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. On this particular point my threshold of 'portable' is actually pretty low, as long as it's fail-soft. Failure to detect bad TZ on some systems would leave them no worse off than before, right? But I haven't seen *any* published API that directly tells you whether tzset liked TZ or not --- AFAICT it's supposed to just silently substitute GMT. Which would be okay if "GMT" were the only allowed spelling of GMT, but it ain't ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
The right URL (I'll get it eventually) is ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf That time I exactly copied the URL. sorry for the wrong one previously. On 20 Feb 2003, Dave Cramer wrote: > Scott, > > Thanks for the reference, I think the actual document is > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > and it is in section 14.12 > > > on or about page 839 > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) > ---(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
"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. 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. 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] [OpenFTS-general] Alpha-2 of contrib/tsearch
Hello Teodor, I'll check this maybe till the end of this week and try to write some doc. Thursday, February 20, 2003, 11:37:34 AM, you wrote: TS> Changes: TS> 1 Fixed compile problem on Solaris TS> 2 Add search by weight of lexem. TS> Readme: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/README-V2.txt TS> Tar: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch.tar.gz TS> We still need a documentation... TS> Any comments please send to Oleg Bartunov ([EMAIL PROTECTED]) and TS> Teodor Sigaev ( [EMAIL PROTECTED] ). -- Best regards, Uros ---(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] request for sql3 compliance for the update command
On Thu, Feb 20, 2003 at 09:31:21AM -0500, Tom Lane wrote: > about. I originally understood Dave to be asking for parens to be > allowed around individual target column names, which seems a useless > frammish to me. What Bruce has pointed out is that a syntax that lets > you assign multiple columns from a single rowsource would be an actual > improvement in functionality, or at least in convenience and efficiency. > (It would also be a substantial bit of work, which is why I think this > isn't what Dave was offering a quick patch to do...) What I'd like to > know right now is which interpretation Informix actually implements. Informix syntax is listed on http://www-3.ibm.com/software/data/informix/pubs/library/visionary/infoshelf/sqls/01start.fm.html#156200 It's more than just parens IMO. :-) Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Hard problem with concurrency
Hi, Vincent van Leeuwen wrote, On 2/19/2003 10:08 PM: On 2003-02-18 20:02:29 +0100, Peter Eisentraut wrote: Christopher Kings-Lynne writes: > REPLACE INTO anyone? ;) The upcoming SQL 200x standard includes a MERGE command that appears to fulfill that purpose. MySQL features a poor-mans aproach to this problem, their REPLACE command: http://www.mysql.com/doc/en/REPLACE.html REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. I'd love to see this kind of functionality in PG, I've got a database that caches data which only gets conditional INSERT/UPDATEs, so that would save a lot of wasted SQL commands. I think this replace function is stupid in mysql. It deletes the the row, and what if that row is linked into another table? You loose your connection, relation. However you can easy write a procedure which can make a real replace, cause it checks if same data (by keys) is in the table then makes an update, if not, do an insert. You can do everything, not like in mysql, just write it as you like. C. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Simplifying timezone support
On Wed, Feb 19, 2003 at 10:35:58PM -0500, Tom Lane wrote: > Any objections? Not to your suggestion per se, but looking at the bug report raises a 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: test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 CST'; timestamptz -- 2003-02-18 09:36:06.00933-06 (1 row) test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 08:36:06.00933-06 (1 row) test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 US/Eastern'; ERROR: Bad timestamp external representation '2003/02/18 09:36:06.00933 US/Eastern' Further testing says it's even worse that that: SET TIME ZONE will silently accept any string at all, and fall back to providing GMT when a timestamptz is requested. This includes the TLA TZ abbreviations that the constant parsing code understands, like CST and EST. test=# set TIME ZONE 'CST'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 14:36:06.00933+00 (1 row) test=# set TIME ZONE 'FOOBAR'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 14:36:06.00933+00 (1 row) Here's an especially fun one: with DATESTYLE set to 'Postgresql,US', whatever string is handed to SET TIME ZONE comes out the other end, if it can't be parsed: test=# set TIME ZONE 'FOOBAR'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz --- Tue Feb 18 14:36:06.00933 2003 FOOBAR (1 row) Leading to this erroneous pair: test=# set TIME ZONE 'US/Central'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz Tue Feb 18 08:36:06.00933 2003 CST (1 row) test=# set TIME ZONE 'CST'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz Tue Feb 18 14:36:06.00933 2003 CST (1 row) test=# Tom, since you're in (or near) that code right now, how painful would it be to unify the time zone parsing? What's the correct behavior? Certainly SET TIME ZONE should at leat NOTICE about invalide time zone names? Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
sorry, it's the -02 document. just change the last 01 to 02 and you'll get the right one. On 20 Feb 2003, Dave Cramer wrote: > Scott, > > I can't find page 858 in that document, is it the right one? > > also the link s/b ? > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf > > Dave > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > > On Thu, 20 Feb 2003, Tom Lane wrote: > > > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > > or do you see more serious problems in letting it in ? > > > > > > At this point it seems there are two different things being tossed > > > about. I originally understood Dave to be asking for parens to be > > > allowed around individual target column names, which seems a useless > > > frammish to me. What Bruce has pointed out is that a syntax that lets > > > you assign multiple columns from a single rowsource would be an actual > > > improvement in functionality, or at least in convenience and efficiency. > > > (It would also be a substantial bit of work, which is why I think this > > > isn't what Dave was offering a quick patch to do...) What I'd like to > > > know right now is which interpretation Informix actually implements. > > > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > > but if Informix has done what Bruce is talking about, that's a different > > > matter altogether. > > > > Tom, I was purusing the wild and wonderfully exciting new SQL > > > > (found here: > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > > > ANSI TC NCITS H2 > > ISO/IEC JTC 1/SC 32/WG 3 > > Database > > > > document to see what it had to say, and on this subject, and it looks like > > update is going to be supporing this same style we're discussing here. > > > > Look on or around p. 858 in that doc.) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] contrib Makefile's and OS X
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])
Re: [HACKERS] request for sql3 compliance for the update command
Scott, Thanks for the reference, I think the actual document is ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf and it is in section 14.12 on or about page 839 Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > On Thu, 20 Feb 2003, Tom Lane wrote: > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > or do you see more serious problems in letting it in ? > > > > At this point it seems there are two different things being tossed > > about. I originally understood Dave to be asking for parens to be > > allowed around individual target column names, which seems a useless > > frammish to me. What Bruce has pointed out is that a syntax that lets > > you assign multiple columns from a single rowsource would be an actual > > improvement in functionality, or at least in convenience and efficiency. > > (It would also be a substantial bit of work, which is why I think this > > isn't what Dave was offering a quick patch to do...) What I'd like to > > know right now is which interpretation Informix actually implements. > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > but if Informix has done what Bruce is talking about, that's a different > > matter altogether. > > Tom, I was purusing the wild and wonderfully exciting new SQL > > (found here: > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > ANSI TC NCITS H2 > ISO/IEC JTC 1/SC 32/WG 3 > Database > > document to see what it had to say, and on this subject, and it looks like > update is going to be supporing this same style we're discussing here. > > Look on or around p. 858 in that doc.) -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
"Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > The simplest way seems to accept COMMIT any time under autocommit > off mode. That's just hiding the most visible symptom. The real problem here is that the SELECT is already committed, when it shouldn't be. 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] 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) For single columns you could still write : UPDATE djp SET col1 = (SELECT col2 FROM some_other_table) Notice - one more set of brackets on the right as on the left > UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Scott, I can't find page 858 in that document, is it the right one? also the link s/b ? ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: > On Thu, 20 Feb 2003, Tom Lane wrote: > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > > Are you against it just on grounds of cleanliness and ANSI compliance, > > > or do you see more serious problems in letting it in ? > > > > At this point it seems there are two different things being tossed > > about. I originally understood Dave to be asking for parens to be > > allowed around individual target column names, which seems a useless > > frammish to me. What Bruce has pointed out is that a syntax that lets > > you assign multiple columns from a single rowsource would be an actual > > improvement in functionality, or at least in convenience and efficiency. > > (It would also be a substantial bit of work, which is why I think this > > isn't what Dave was offering a quick patch to do...) What I'd like to > > know right now is which interpretation Informix actually implements. > > > > I don't like adding nonstandard syntaxes that add no functionality --- > > but if Informix has done what Bruce is talking about, that's a different > > matter altogether. > > Tom, I was purusing the wild and wonderfully exciting new SQL > > (found here: > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) > > ANSI TC NCITS H2 > ISO/IEC JTC 1/SC 32/WG 3 > Database > > document to see what it had to say, and on this subject, and it looks like > update is going to be supporing this same style we're discussing here. > > Look on or around p. 858 in that doc.) -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
On Thu, 20 Feb 2003, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Are you against it just on grounds of cleanliness and ANSI compliance, > > or do you see more serious problems in letting it in ? > > At this point it seems there are two different things being tossed > about. I originally understood Dave to be asking for parens to be > allowed around individual target column names, which seems a useless > frammish to me. What Bruce has pointed out is that a syntax that lets > you assign multiple columns from a single rowsource would be an actual > improvement in functionality, or at least in convenience and efficiency. > (It would also be a substantial bit of work, which is why I think this > isn't what Dave was offering a quick patch to do...) What I'd like to > know right now is which interpretation Informix actually implements. > > I don't like adding nonstandard syntaxes that add no functionality --- > but if Informix has done what Bruce is talking about, that's a different > matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > There seems a bad behavior under autocommit off mode. > > > 1) psql -c 'set autocommit to off;select 1;commit' > > causes a WARNING: COMMIT: no transaction in progress > > Surely that's a bug: the SELECT ought to start a transaction block. > > Barry Lind reported what is probably a closely related issue: > http://archives.postgresql.org/pgsql-hackers/2003-01/msg00592.php > > I haven't gotten around to looking at this, but I suspect postgres.c > is doing something inside the per-querytree loop that it should be > doing outside it, or vice versa. Or possibly the problem is with > the klugy way that we hacked autocommit-off into the xact.c state > machine. Do you have time to look at it? I have little time. The transaction block state seems to be set just before returning from the chained query. I don't know if it's bad or not. The simplest way seems to accept COMMIT any time under autocommit off mode. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Query planner/stored procedure cost
Hello... I haven't been subscribed in a while, but I've got an issue and am trying to determine if the Right Way(tm) is the quickest way to fix it. Basically, I have some very expensive stored procedures that determine whether a user should have access to particular rows in a query (not a postgresql user, we only use one postgresql user... the user is passed as a parameter to the function). The logic--per row--contains about a dozen queries and probably averages eight queries per run, with short-circuiting and all. So it is _very_ expensive. Given that I use this function in lots of queries with hairy joins and all, I'd much like for the optimizer to know what to do with the function. Empirically, I deduce that the optimizer treats all procedures as inexpensive (it seems to always just tack it on to the `Filter' slot when scanning the related table). Currently I'm using stored procedures returning multiple rows to get around the planner on these and defer the expensive procedure until the last possible moment (so that joins and other table criteria have a chance to filter out a lot of records). This typically shaves 75% of the time off of these queries. So, the question is: What am I looking at in doing the following: 1) Adding a mechanism to tell PostgreSQL how expensive a procedure is (a system table which can be updated manually, or an existing system table if there is a logical place for it). 2) Updating the planner to consider the procedure's cost in estimates. 3) Changing the query planner to consider "bubbling up" the function to an outer filter slot. Possibly, also: 4) Changing the planner to order expressions in a `Filter' slot by cost. although I don't mind doing this manually and I know the order can determine which indices PostgreSQL uses. I'm still mulling it over, and I'm guessing the real problem here is if it is a wise generalization that we can "bubble-up" the function. What if the function has side effects? Does this break? We can at least do procedures with `iscachable' flag. Disclaimer: I haven't every really hacked the planner code, but I have a good feel for how it works from lots and _lots_ of experience with it -Jay 'Eraserhead' Felice ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: 20 February 2003 14:31 > To: Hannu Krosing > Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers > Subject: Re: [HACKERS] request for sql3 compliance for the > update command > > > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Are you against it just on grounds of cleanliness and ANSI > compliance, > > or do you see more serious problems in letting it in ? > > At this point it seems there are two different things being > tossed about. I originally understood Dave to be asking for > parens to be allowed around individual target column names, > which seems a useless frammish to me. What Bruce has pointed > out is that a syntax that lets you assign multiple columns > from a single rowsource would be an actual improvement in > functionality, or at least in convenience and efficiency. (It > would also be a substantial bit of work, which is why I think > this isn't what Dave was offering a quick patch to do...) > What I'd like to know right now is which interpretation > Informix actually implements. > > I don't like adding nonstandard syntaxes that add no > functionality --- but if Informix has done what Bruce is > talking about, that's a different matter altogether. Informix SE allows me to do: CREATE TABLE djp(col1 INTEGER, col2 INTEGER) INSERT INTO djp VALUES(1, 2) UPDATE djp SET(col1, col2) = (3, 4) However UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp) Results in a syntax error. I don't have Informix IDS so I don't know if that can do it. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] A bad behavior under autocommit off mode
"D'Arcy J.M. Cain" <[EMAIL PROTECTED]> writes: > On Thursday 20 February 2003 10:38, Tom Lane wrote: >> Hiroshi Inoue <[EMAIL PROTECTED]> writes: > There seems a bad behavior under autocommit off mode. > > 1) psql -c 'set autocommit to off;select 1;commit' > causes a WARNING: COMMIT: no transaction in progress >> >> Surely that's a bug: the SELECT ought to start a transaction block. > Sure but doesn't it also commit it? Not in autocommit-off mode. 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] A bad behavior under autocommit off mode
On Thursday 20 February 2003 10:38, Tom Lane wrote: > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > There seems a bad behavior under autocommit off mode. > > > > 1) psql -c 'set autocommit to off;select 1;commit' > > causes a WARNING: COMMIT: no transaction in progress > > Surely that's a bug: the SELECT ought to start a transaction block. Sure but doesn't it also commit it? There's still no transaction open coming out of the SELECT. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > There seems a bad behavior under autocommit off mode. > 1) psql -c 'set autocommit to off;select 1;commit' > causes a WARNING: COMMIT: no transaction in progress Surely that's a bug: the SELECT ought to start a transaction block. Barry Lind reported what is probably a closely related issue: http://archives.postgresql.org/pgsql-hackers/2003-01/msg00592.php I haven't gotten around to looking at this, but I suspect postgres.c is doing something inside the per-querytree loop that it should be doing outside it, or vice versa. Or possibly the problem is with the klugy way that we hacked autocommit-off into the xact.c state machine. Do you have time to look at it? 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] deleting dependencies
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > I've been looking at the dependency API and I notice that there is a > function to delete ALL dependencies on an object and a function to add a > dependency, but there doesn't seem to be any way of deleting a dependency > between two _particular_ objects. In the ALTER cases that have been implemented so far, it seemed easiest to wipe the full set of dependencies and then regenerate them from the altered object. I think you will find the same is true of altering column type. To take just one example, the column default expression (if any) almost certainly has to be replaced too, and it may contain dependencies. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
Hannu Krosing <[EMAIL PROTECTED]> writes: > Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: >> However, what solution do we have for UPDATE (coll...) = (select val...) >> for folks? It is awkward to repeat a query multiple times in an UPDATE. > hannu=# update target set > hannu-# a = source.a1, b=source.a2, c=source.a3 > hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source > hannu-# where id = 1 > hannu-# ; I've been trying to think of a case that can't be handled by transposing the sub-select into FROM. I'm not sure there are any. I thought for a minute that grouped aggregates would be an issue. For example, suppose table "totals" has one row for each distinct value of "groupid" appearing in table "details", and you use it to store group aggregate values. You can do UPDATE totals SET xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), xmin = (SELECT min(x) FROM details WHERE groupid = totals.groupid), ymax = (SELECT max(y) FROM details WHERE groupid = totals.groupid), ymin = (SELECT min(y) FROM details WHERE groupid = totals.groupid), ... but that is awfully tedious and will be inefficiently implemented. This is what Bruce is worried about. On the other hand, one could argue that this is a wrongheaded way to go about it anyway, and the correct way is 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; If there is indeed a row in "totals" for every groupid, then this will certainly beat out the first approach that has to run a separate query for each groupid, even if we avoid a separate query for each aggregate. (It could maybe lose if you only wanted to update the totals for a few groupids; but even then you could probably push the WHERE conditions restricting the groups into the sub-select.) Of course this syntax isn't standard either ... but we already have it. Right now I'm not convinced there is a functionality argument for supporting the Informix-style syntax, even with multiple columns. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Hannu Krosing <[EMAIL PROTECTED]> writes: > Are you against it just on grounds of cleanliness and ANSI compliance, > or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Open Source Development Lab resources
Hi guys, Had an interesting conversation earlier on today with Timothy Witham from the Open Source Development Lab (important place sponsored by IBM, HP, CA, etc) earlier on today. They've been basing their database performance suites on SAPDB, but are having problems with it and looking to move to a better database. This is an opportunity for us to get a lot of corporate-acceptable testing and similar done, if there are a few people willing to help out. Am very much interested in people's thoughts on this, and especially hoping that some people are willing to get together and get the needed bits done. Regards and best wishes, Justin Clift *** Original Message Subject: Re: OSDLabs and PostgreSQL Date: 19 Feb 2003 14:18:30 -0800 From: Timothy D. Witham <[EMAIL PROTECTED]> Organization: Open Source Development Lab, Inc. To: Justin Clift <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> Further thoughts, I think that we have hit a wall with our progress with SAPDB on the performance front. If you would check out the performance pages on the three database tests. These are fair use subsets of the TPC W,C and H benchmarks and they are open source. (www.osdl.org/projects/performance) I will be blunt with you. If we had somebody who was willing to: 1) Work on getting the kits ported over. 2) Work on performance issues we discovered 3) Work on enhancements that would help in both the real world and these tests I would be willing to move all of our work over to that RDBMS. Our goal is to make the overall infrastructure better and I think that we could do that working with just one database but we have to get the support from those database developers. Tim *** Randal L. Schwartz wrote: FYI... I recently attended a presentation by the director of the Open Source Development Lab (www.osdl.org). Apparently they have two things that are useful to open-source database developers: a) some ongoing work to make nice database test suites for benchmarking b) lots of hardware available for *free* for testing All you have to do is sign up. I'm about five minutes from the site, so if there's anything that needs to be done physically there, I'm game. But generally, it's all handled remote anyway. Did I say they have lots of hardware? Big disk arrays. 2-way, up to 32-way(!) processor setups. Fast pipes to the net. Did I say free? As long as you're working on open source stuff, you can take a number. Neat. -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi ---(end of broadcast)--- TIP 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] Alpha-2 of contrib/tsearch
Changes: 1 Fixed compile problem on Solaris 2 Add search by weight of lexem. Readme: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/README-V2.txt Tar: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch.tar.gz We still need a documentation... Any comments please send to Oleg Bartunov ([EMAIL PROTECTED]) and Teodor Sigaev ( [EMAIL PROTECTED] ). -- Teodor Sigaev [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: > Agreed folks are going to have bigger problems from Informix than just > this, and in fact I used Informix for years and didn't know they allowed > this. > > However, what solution do we have for UPDATE (coll...) = (select val...) > for folks? It is awkward to repeat a query multiple times in an UPDATE. hannu=# create table target (id serial, a int, b int, c int); NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for SERIAL column 'target.id' CREATE TABLE hannu=# insert into target(a,b,c) values (0,0,0); INSERT 16983 1 hannu=# insert into target(a,b,c) values (1,1,1); INSERT 16984 1 hannu=# update target set hannu-# a = source.a1, b=source.a2, c=source.a3 hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source hannu-# where id = 1 hannu-# ; UPDATE 1 hannu=# select * from target; id | a | b | c +---+---+--- 2 | 1 | 1 | 1 1 | 1 | 2 | 3 (2 rows) hannu=# -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: > Agreed folks are going to have bigger problems from Informix than just > this, and in fact I used Informix for years and didn't know they allowed > this. > > However, what solution do we have for UPDATE (coll...) = (select val...) > for folks? It is awkward to repeat a query multiple times in an UPDATE. > > I think it makes sense to add it only if it adds functionality. It makes it easier (less keystrokes) to write as well as similar in appearance to INSERT, so the same code can be used to generate the queries. If we were at adding functionality then IMHO making VALUES(x,y,z) a proper "rowsource" would be a more worthy effort. --- Hannu ---(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] request for sql3 compliance for the update command
Tom Lane kirjutas K, 19.02.2003 kell 21:12: > Dave Cramer <[EMAIL PROTECTED]> writes: > > Ok, if a patch were submitted to the parser to allow the syntax in > > question would it be considered? > > I would vote against it ... but that's only one vote. Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
On Wed, Feb 19, 2003 at 04:37:33PM +0100, Peter Eisentraut wrote: > That's not what my copy says. Strange. I just looked at all the docs I have and all have it listed the way Dave wrote. So I seem to have to update my docs. Peter, could you send me a copy? 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 Wed, Feb 19, 2003 at 12:29:12PM -0500, Tom Lane wrote: > SQL99. Looks like the parens got lost again by the time of the final > spec. I don't think the parens really matter. It's just the different ordering of columns and values. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
