Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote:
I have a connection that is created with prepareThreshold=1 in the
connection string. I use a prepared statement that I fill with
addbatch() and that I execute with executeBatch() (for full source: see
application.java attachment).
LOG: statement: PREPARE S_2 AS update prototype.customers set title=
$1 , defaultcurrency=$2, defaulttermsofdelivery=$3 ,
defaulttermsofpayment=$4 where customernumber=$5
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 773.841 ms
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 377.981 ms
Does this output mean that the prepared statement with the name S_2 is
not used in the following 2 EXECUTE statements and that therefor each
execute statement is planned again?
No, this actually looks like a bug in the server side logging. The JDBC
driver issues:
FE= Parse(stmt=S_1,query=INSERT INTO tt VALUES ($1),oids={23})
FE= Bind(stmt=S_1,portal=null,$1=1)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Bind(stmt=S_1,portal=null,$1=2)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Sync
I assume the server side logging code is getting confused because it uses
a named statement, but the unnamed portal.
Kris Jurka
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
Hi Kris,
You have tested this with an insert statement. Could you do that also for an
update (or try to tell me how I can do that)? I am getting very strange
differences in running time between inserts and update ( 26 inserts are
measured in seconds, 26 updates over 1 column in the same table are
measured in minutes)
TIA
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
-Oorspronkelijk bericht-
Van: Kris Jurka [mailto:[EMAIL PROTECTED]
Verzonden: zondag 13 november 2005 10:27
Aan: Joost Kraaijeveld
CC: pgsql-jdbc@postgresql.org; pgsql-hackers@postgresql.org
Onderwerp: Re: [JDBC] prepareThreshold=1 and
statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote:
I have a connection that is created with prepareThreshold=1 in the
connection string. I use a prepared statement that I fill with
addbatch() and that I execute with executeBatch() (for full
source: see
application.java attachment).
LOG: statement: PREPARE S_2 AS update prototype.customers
set title=
$1 , defaultcurrency=$2, defaulttermsofdelivery=$3 ,
defaulttermsofpayment=$4 where customernumber=$5
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 773.841 ms
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 377.981 ms
Does this output mean that the prepared statement with the
name S_2 is
not used in the following 2 EXECUTE statements and that
therefor each
execute statement is planned again?
No, this actually looks like a bug in the server side
logging. The JDBC
driver issues:
FE= Parse(stmt=S_1,query=INSERT INTO tt VALUES ($1),oids={23})
FE= Bind(stmt=S_1,portal=null,$1=1)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Bind(stmt=S_1,portal=null,$1=2)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Sync
I assume the server side logging code is getting confused
because it uses
a named statement, but the unnamed portal.
Kris Jurka
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.
Normally I'd plump for following the standard ... but AFAIR, we have had
bucketloads of requests for REPLACE functionality, and not one request
for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a
whole lot harder and slower than REPLACE, it seems that we could do
worse than to concentrate on doing REPLACE for now. (We can always come
back to MERGE some other day.)
I would also like to add that MySQL's REPLACE is not exactly an INSERT
OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the
fields not specified in the query are set to their defaults:
i.e.
CREATE TABLE t (a int PRIMARY KEY, b int, c int);
INSERT INTO t (a, b, c) VALUES (1, 1, 2);
SELECT * FROM t;
+---+--+--+
| a | b| c|
+---+--+--+
| 1 |1 |2 |
+---+--+--+
REPLACE INTO t (a, b) VALUES (1, 1);
SELECT * FROM t;
+---+--+--+
| a | b| c|
+---+--+--+
| 1 |1 | NULL |
+---+--+--+
I wanted to point it out this because people are commonly mistaking this.
Wow, that seems ugly maybe there's a reason for it, but I'm not sure we
could deviate from my$ql's behavior on this even if we wanted... they are the
standard here.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] MERGE vs REPLACE
On 11/13/05, Robert Treat [EMAIL PROTECTED] wrote: On Saturday 12 November 2005 04:06, Matteo Beccati wrote: | 1 |1 | NULL | Wow, that seems ugly maybe there's a reason for it, but I'm not sure we could deviate from my$ql's behavior on this even if we wanted... they are the standard here. I don't think that's ugly, I think that's exactly working as advertised. Replace behaves exactly like deleting the record with the matching primary key and inserting the provided input. ... not merging together old data with new. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
On Sat, Nov 12, 2005 at 10:46:33PM -0800, Kevin Brown wrote: Hmm...but isn't the version number also something that can be stored in the shared library itself during link time (e.g., via the -soname option to the linker)? The manpage for ld under Linux implies that this will cause the executable that's linked against the shared object to look explicitly for a library with the soname specified by the shared object. I don't know if that just causes the dynamic linker to look for a file with the specified soname or if it will actually examine the shared object under consideration to make sure it has the DT_SONAME field in question, however. No, that's completely unrelated. The soname is what gets put in the DT_NEEDED field of programs that need it. Thus if you have libtermcap.so symlinked to libncurses.so, when you link with -ltermcap, the linker will include a reference to libncurses because that's what the soname is. The only place version numbers come in is when a library libfoo.8.2 has a soname libfoo.8 which means that at runtime it will accept any lib with that soname. None of this applies to PostgreSQL because we open the modules directly, and don't rely on the linker loader. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpHC9YzQmbe4.pgp Description: PGP signature
Re: [HACKERS] Multi-table-unique-constraint
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Maybe the solution is to make inherited tables actually the same table, and jank it with an extra per-row attribute to differentiate them or something :) Aside from destroying the inheritance-for-partitioning stuff, this wouldn't work for multiple inheritance, so I'm afraid it's not a very attractive alternative. Matt's idea about keeping the indexes separate seems that it probably *would* work, modulo some lingering worries about when to take what kind of lock on the index-set-as-a-whole. It seems worth pursuing, anyway. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
Oliver Jowett [EMAIL PROTECTED] writes: The driver does not actually issue PREPARE or EXECUTE statements; the server is pretending that the protocol-level Prepare/Bind/Execute messages are actually something issuing PREPARE/EXECUTE at the SQL level I had not realized that the logging code recently added to Parse/Bind/Execute deliberately obscures the difference between a Parse message and an SQL PREPARE (etc). This is a terrible decision IMHO and needs to be fixed forthwith. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Multi-table-unique-constraint
Tom Lane wrote: Matt Newell [EMAIL PROTECTED] writes: BTW, i'm on the list now, so no need to cc me. Common practice around here is to cc people anyway --- this has grown out of a history of occasionally-slow list mail delivery. If you don't want it, best to fix it in your mail filters rather than expecting people to change habits for you. You can also change your subscriptions so that you don't get a copy from the list if you are in the To: or Cc: lists. I find this is better then having to set up filters. Visit http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Supporting NULL elements in arrays
Joe Conway [EMAIL PROTECTED] writes: Jim C. Nasby wrote: Instead of bending over backwards to try and support older cases, would a compatability mode be possible? Seems that would solve a lot of problems. Last time I thought about this problem, that's what I concluded. I don't think there is a reasonable and backward compatible solution. I also think the best non-compatible solution is to require non-numeric elements to be delimited (double quotes, configurable?), and use NULL unadorned to represent NULL. After further thought I'm starting to agree with this point of view as well. I propose the following details: 1. A null element is represented as the unquoted string NULL (case-insensitive on input). Any use of quotes or backslashes turns it into a simple string value NULL instead. array_out will need to be careful to quote any string that matches NULL. 2. For backwards compatibility, we'll invent a GUC parameter enable_array_nulls that defeats recognition of NULL in array_in. (Any better ideas about the name of the parameter?) This isn't ideal because of the compatibility risk, but once we get past the transition period it's a reasonable definition. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] REPLACE implementation (was: Re: MERGE vs REPLACE)
On 11/12/05, Matteo Beccati [EMAIL PROTECTED] wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: It seems to me that it has always been implicitly assumed around here that the MERGE command would be a substitute for a MySQL-like REPLACE functionality. After rereading the spec it seems that this is not the case. MERGE always operates on two different tables, which REPLACE doesn't do. Normally I'd plump for following the standard ... but AFAIR, we have had bucketloads of requests for REPLACE functionality, and not one request for spec-compatible MERGE. If, as it appears, full-spec MERGE is also a whole lot harder and slower than REPLACE, it seems that we could do worse than to concentrate on doing REPLACE for now. (We can always come back to MERGE some other day.) I would also like to add that MySQL's REPLACE is not exactly an INSERT OR UPDATE, rather and INSERT OR (DELETE then INSERT): I mean that the fields not specified in the query are set to their defaults: This sounds a lot like postgres implementation of UPDATE... delete tuple (actually, mark it as dead and insert)... Maybe we can use this? or maybe some kind of merge between ExecDelete and ExecInsert? Also, the MySQL implementation require DELETE and INSERT permission. What about triggers? run before/after delete and insert? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote: I have a connection that is created with prepareThreshold=1 in the connection string. I use a prepared statement that I fill with addbatch() and that I execute with executeBatch() (for full source: see application.java attachment). LOG: statement: PREPARE S_2 AS update prototype.customers set title= $1 , defaultcurrency=$2, defaulttermsofdelivery=$3 , defaulttermsofpayment=$4 where customernumber=$5 LOG: statement: BIND LOG: statement: EXECUTE unnamed [PREPARE: update prototype.customers set title=$1 , defaultcurrency=$2, defaultter msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5] LOG: duration: 773.841 ms LOG: statement: BIND LOG: statement: EXECUTE unnamed [PREPARE: update prototype.customers set title=$1 , defaultcurrency=$2, defaultter msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5] LOG: duration: 377.981 ms Does this output mean that the prepared statement with the name S_2 is not used in the following 2 EXECUTE statements and that therefor each execute statement is planned again? The driver does not actually issue PREPARE or EXECUTE statements; the server is pretending that the protocol-level Prepare/Bind/Execute messages are actually something issuing PREPARE/EXECUTE at the SQL level (but in reality, nothing is issuing precisely the queries that are being logged -- the query that is submitted is just your plain update ... query). The PREPARE S_2 AS .. logs that a Prepare message was processed (for the query update ...). This does parsing/planning work and creates a named prepared statement called S_2 on the server. The BIND means that some previously prepared statement (you can't tell which statement from what is logged! -- but it's S_2 in this case) is being bound to parameter values via a Bind message, creating an unnamed portal. The EXECUTE unnamed means the unnamed portal is being executed via an Execute message. It also logs the underlying statement at that point, but not the statement name (!). So if I read the logs right, the single prepared statement S_2 *is* being reused in the case above. Yes, it's a horribly confusing way for the server to log things. I raised it on -hackers earlier in the 8.1 cycle, but I've not had time to work on it myself. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
On Sunday 13 November 2005 10:01, Gregory Maxwell wrote:
On 11/13/05, Robert Treat [EMAIL PROTECTED] wrote:
On Saturday 12 November 2005 04:06, Matteo Beccati wrote:
| 1 |1 | NULL |
Wow, that seems ugly maybe there's a reason for it, but I'm not sure
we could deviate from my$ql's behavior on this even if we wanted... they
are the standard here.
I don't think that's ugly, I think that's exactly working as
advertised. Replace behaves exactly like deleting the record with the
matching primary key and inserting the provided input. ... not merging
together old data with new.
I disagree in that REPLACE is advertised as a solution for the INSERT else
UPDATE problem, but has a different behavior than a true INSERT else UPDATE
would produce. Maybe that's a problem with the implementation, or maybe
it's a problem in the advertisment, but there is certainly a discrepency
there.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
Robert Treat [EMAIL PROTECTED] writes: I disagree in that REPLACE is advertised as a solution for the INSERT else UPDATE problem, but has a different behavior than a true INSERT else UPDATE would produce. Maybe that's a problem with the implementation, or maybe it's a problem in the advertisment, but there is certainly a discrepency there. Yeah. REPLACE fails to solve common examples like a web hit counter (if key doesn't exist, insert row with count 1; if it does exist, add 1 to the current count). IIRC, SQL's MERGE deals with this by offering two quite separate specifications of what to do when there is or isn't already a matching row. I don't necessarily feel that we have to slavishly duplicate what MySQL offers. I do think that it's reasonable to restrict the functionality to updating/replacing a row with matching primary key --- that gets us out of the problem of needing a full predicate-locking mechanism, while still covering most all of the practical use-cases that I can see. It'd be useful to look at what comparable functionality is offered by other DBs besides MySQL. Anyone know what DB2 or Oracle have in this area? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] syntax for drop if exists
I was just looking briefly at doing drop if exists as we discussed recently. The MySQL syntax is actually drop table if exists foo Implementing this unfortunately generates a shift/reduce conflict, unless I put IF in the func_name_keyword list, which strikes me as a bad idea. Alternatively, we could use the syntax drop if exists table foo ... which seems more natural to me, and generates no conflict. Or we could live with the conflict, which I think would be harmless unless you wanted to delete a table called if, in which case you might need to say drop table if exists if ;-) I'm inclined to live with it, annoying as it is. I looked around to see what other DBs do - but AFAICS most don't support this. Thoughts? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MERGE vs REPLACE
Tom Lane wrote: It'd be useful to look at what comparable functionality is offered by other DBs besides MySQL. Anyone know what DB2 or Oracle have in this area? IIRC they both have MERGE. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] MERGE vs REPLACE
Petr Jelinek wrote: Tom Lane wrote: It'd be useful to look at what comparable functionality is offered by other DBs besides MySQL. Anyone know what DB2 or Oracle have in this area? Oracle: http://www.psoug.org/reference/merge.html http://www.psoug.org/reference/translate_replace.html But the oracle replace seems completely different to the topic at hand. DB2: Merge: http://www.databasejournal.com/features/db2/article.php/10896_3322041_2 I was not able to easily find information on REPLACE. Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote: You have tested this with an insert statement. Could you do that also for an update (or try to tell me how I can do that)? I am getting very strange differences in running time between inserts and update ( 26 inserts are measured in seconds, 26 updates over 1 column in the same table are measured in minutes) Certainly there are different costs associated with inserts vs. updates. An insert just needs to jam a new row in somewhere, but the update must first search the table to find the existing row. If you do not have an index this will take a while. If you update the same row every time this will also take a while because you'll be creating a whole bunch of dead rows in the table. Some more information on the table and type of updates could bring the update cost down, but comparing insert vs. update times is not a reasonable thing to do. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 substring bug?
I wrote: Martijn van Oosterhout kleptog@svana.org writes: In this particular case the syntax makes it unclear that the substring is the problem. Perhaps here the solution would be to put a cast in the grammer, like so: ... But I think we could do this in substr_list in the case where we have just a_expr substr_for, because there are no variants of that where the FOR expression is supposed to be string. I've applied this patch as far back as 8.0. Not sure whether there's a need to back-patch further. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MERGE vs REPLACE
Tom Lane wrote: IIRC, SQL's MERGE deals with this by offering two quite separate specifications of what to do when there is or isn't already a matching row. In that regard, MERGE is quite flexible, but MERGE doesn't address the point of REPLACE, because MERGE requires *two* tables as input, whereas REPLACE only takes *one*. Unless someone can show that you can trick MERGE into doing the REPLACE job anyway, we're not discussing the same thing. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Supporting NULL elements in arrays
I wrote: ... Now, when the bitmap is present, it would be fairly tedious to calculate the length of the bitmap to determine the offset to the actual data; and that's an operation that we'd like to be cheap. What I'm thinking of doing is commandeering the whole flags field of ArrayType (which is currently unused and should be always zero), and redefining it thus: zero: bitmap not present (fully backwards-compatible case) not zero: bitmap is present; value is offset to start of data I wouldn't propose doing this if I thought we had any pressing reason to save some array flag bits for some other purpose; but I can't think of anything else we might want 'em for. Did anyone have any pet ideas this would foreclose? On trying to recompile things, I find that contrib/intarray is broken by this change, because it's using the flags field for its own purposes: /* * flags for gist__int_ops, use ArrayType-flags * which is unused (see array.h) */ #define LEAFKEY (131) #define ISLEAFKEY(x)( ((ArrayType*)(x))-flags LEAFKEY ) It seems likely that intarray is going to need some rather significant work anyway to deal with null elements, so this seems to me to be not necessarily a fatal objection. But why exactly does intarray need to play games with the contents of an array value? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] forcing returned values to be binary
Is there a way to force select * from foo to use binary values ? Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SIGSEGV taken on 8.1 during dump/reload
Martijn van Oosterhout wrote: None of this applies to PostgreSQL because we open the modules directly, and don't rely on the linker loader. Ah, right. I forgot the context was the server, not one of the utilities... Sorry for the waste of bandwidth... -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MERGE vs REPLACE
Peter Eisentraut wrote: In that regard, MERGE is quite flexible, but MERGE doesn't address the point of REPLACE, because MERGE requires *two* tables as input, whereas REPLACE only takes *one*. Unless someone can show that you can trick MERGE into doing the REPLACE job anyway, we're not discussing the same thing. I am really not db expert and I don't have copy of sql standard but you don't need to use 2 tables I think - USING part can also be subquery (some SELECT) and if I am right then you could simulate what REPLACE does because in PostgreSQL you are not forced to specify FROM clause in SELECT. So you could in theory do MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid) ... But I am not sure if this is what you want. -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MERGE vs REPLACE
On Sun, Nov 13, 2005 at 11:32:47PM +0100, Petr Jelinek wrote: I am really not db expert and I don't have copy of sql standard but you don't need to use 2 tables I think - USING part can also be subquery (some SELECT) and if I am right then you could simulate what REPLACE does because in PostgreSQL you are not forced to specify FROM clause in SELECT. So you could in theory do MERGE INTO tablename USING (SELECT 1 AS myid) ON (tablename.id = myid) ... But I am not sure if this is what you want. Well, the obvious extension to this is that the extire USING clause is in fact optional: MERGE INTO tablename ON id = 1 ... Which starts looking a lot simpler. BTW, my reading of the MERGE examples given earlier is that there no notes in there at all about guarenteeing concurrency. None of the documentation says that using MERGE will avoid duplicate key errors if someone else does the same thing concurrently. It seems more like a performence hack to avoid scanning the table twice. Basically, you could implement this by taking the USING clause, do a left outer join with the merge table and for the blank rows fill in a CTID for insert and instead of NULLs the values of the INSERT portion. Which is kind of a bummer for the people who want to do the insert zero if not there else add 1 thing a lot and expecting this to solve the concurrency for them. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpFeF7xQDptG.pgp Description: PGP signature
Re: [HACKERS] syntax for drop if exists
Andrew Dunstan [EMAIL PROTECTED] writes: The MySQL syntax is actually drop table if exists foo Implementing this unfortunately generates a shift/reduce conflict, What did you try exactly? I don't see any fundamental reason for a conflict here. You may just need to rearrange the grammar to postpone the reduction a bit. Or we could live with the conflict, Utterly unacceptable; see previous discussions. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] forcing returned values to be binary
Dave Cramer [EMAIL PROTECTED] writes: Is there a way to force select * from foo to use binary values ? In what context? There is a provision for that in the V3 extended-query protocol. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] forcing returned values to be binary
Yeah, I finally read it through and found it. There's some anecdotal evidence of up to 2x performance, so I'm interested in how to implement this in the jdbc driver. ( I'm a little skeptical about 2x) I was thinking that it may be necessary to issue a describe before the execute, but I'm thinking now that the driver can only handle specific types, so anything outside of what it knows about would be an error anyway. I gather it's not possible to mix the return format? For example all known types would be binary, others would be text ? At this point I'm not even sure it would help. Thx, Dave On 13-Nov-05, at 10:21 PM, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Is there a way to force select * from foo to use binary values ? In what context? There is a provision for that in the V3 extended-query protocol. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] forcing returned values to be binary
Dave Cramer [EMAIL PROTECTED] writes: I was thinking that it may be necessary to issue a describe before the execute, but I'm thinking now that the driver can only handle specific types, so anything outside of what it knows about would be an error anyway. I gather it's not possible to mix the return format? For example all known types would be binary, others would be text ? At this point I'm not even sure it would help. You can ask for mixed return formats; see the description of the Bind message. The sticky spot is that you can't really do that without first having gotten the list of output columns (via Describe Statement). Without that, you don't even know how many output columns there are, let alone which ones have datatypes you understand. I'm not sure that this is a fatal objection, at least not for prepared-in-advance statements. You can put a Describe Statement into the same network packet exchange as the original Parse message, so there isn't any reason that you can't know the column types. It is problematic if you want to Parse/Bind/Execute in just one round trip. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
