[HACKERS] Logging conflicted queries on deadlocks
Hello,
We see error logs something like following on deadlocks:
| ERROR: deadlock detected
| DETAIL: Process 1612 waits for ShareLock on transaction 407; blocked by
process 3388.
| Process 3388 waits for ShareLock on transaction 406; blocked by
process 1612.
| STATEMENT: UPDATE test SET i = i WHERE i = 2;
It shows which query is killed, but we cannot get which query is conflicted.
The opponent's queries are useful for deadlock debug, but the only thing
we can do for now is polling pg_locks and pg_stat_sctivity with hope to
take a snapshot of the system just on deadlocks.
I wrote a small patch to log conflicted queries.
With my patch, deadlock message will change as following:
| ERROR: deadlock detected
| DETAIL: Process 3468 waits for ShareLock on transaction 451; blocked by
process 4068.
| STATEMENT: UPDATE test SET i = i WHERE i = 1;- ***conflicted
query***
| Process 4068 waits for ShareLock on transaction 450; blocked by
process 3468.
| STATEMENT: UPDATE test SET i = i WHERE i = 2;
There are some open issues. One of the issues is that the killed query is
logged by log_min_error_statement, but conflicted query is logged in DETAIL.
Killed query is logged only on server but conflicted queries are
sent to both server and client. In addition, if log_min_error_statement
is less than ERROR level, only the conflicted queries are logged.
-- it's inconsistent and unsymmetric.
Are there any better format to dump conflicted queries?
Comments welcome.
[[10-line patch to log conflicted query on deadlocks]]
Index: src/backend/storage/lmgr/deadlock.c
===
--- src/backend/storage/lmgr/deadlock.c (HEAD)
+++ src/backend/storage/lmgr/deadlock.c (working copy)
@@ -911,6 +913,18 @@
info-lockmode),
buf2.data,
nextpid);
+
+ if (nextpid != MyProcPid)
+ {
+ PGPROC* proc = BackendPidGetProc(nextpid);
+ if (proc != NULL (superuser() || proc-roleId ==
GetUserId()))
+ {
+ PgBackendStatus *beentry;
+ beentry =
pgstat_fetch_stat_beentry(proc-backendId);
+ if (beentry beentry-st_activity[0] != '\0')
+ appendStringInfo(buf, \nSTATEMENT:
%s, beentry-st_activity);
+ }
+ }
}
ereport(ERROR,
(errcode(ERRCODE_T_R_DEADLOCK_DETECTED),
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] CREATE TABLE, load and freezing
I had this idea sometime back. Not sure if this has been discussed before In a typical scenario, user might create a table and load data in the table as part of a single transaction (e.g pg_restore). In this case, it would help if we create the tuples in the *frozen* state to avoid any wrap-around related issues with the table. Without this, very large read-only tables would require one round of complete freezing if there are lot of transactional activities in the other parts of the database. And when that happens, it would generate lots of unnecessary IOs on these large tables. I don't know if this is a real problem for anybody, but I could think of its use case, at least in theory. Is it worth doing ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CREATE TABLE, load and freezing
Pavan Deolasee wrote: In a typical scenario, user might create a table and load data in the table as part of a single transaction (e.g pg_restore). In this case, it would help if we create the tuples in the *frozen* state to avoid any wrap-around related issues with the table. Without this, very large read-only tables would require one round of complete freezing if there are lot of transactional activities in the other parts of the database. And when that happens, it would generate lots of unnecessary IOs on these large tables. If that works, then we might also want to set the visibility hint bits. Not because lookup of that information is expensive - the tuples all came from the same transaction, virtually guaranteeing that the relevent pg_clog page stays in memory after the first few pages. But by setting them immediatly we'd save some IO, since we won't dirty all pages during the first scan. I don't know if this is a real problem for anybody, but I could think of its use case, at least in theory. A cannot speak for freeze-on-restore, but in a project I'm currently working on, the IO caused (I guess) by hint-bit updates during the first scan of the table is at least noticeably... regards, Florian Pflug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Varlena Type Creation
Le mercredi 27 février 2008, Martijn van Oosterhout a écrit : I see no-one responded to this: a varlena has no fixed header size, so you can't fit it in a structure anyway. Once you're passed a pointer you use the LEN/PTR macros to extract what you want. Once the type exists and the code gets some varlena kind type of objects to play with, I think I'll use the same macros as for text usage... My problem is more how to define a new composite varlena, that is a new varlena type composed of several base type... I'm not sure I'm using a good vocabulary, please forgive me if it's all unclear... Not sure what the chars are for Maybe the input syntax would help getting what the chars are for. To say a prefix range begins with '012' and any entry between '3' and '6', you'd write e.g. '012[3-6]'::prefix_range. The chars are respectively '3' and '6' and the greatest prefix of the prefix range is '012' here. Here, '012[3-6]' @ '01234' is true but '012[3-6]' @ '0122' is false. , but perhaps it would be easiest to treat it as a single text object with the two leading characters signifying something? I like your idea of using a single text datum for this and encode into it the information I need: it makes it all simple for me to start working. But still does not answer the question... not that the answer is needed any more... Thanks, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] CREATE TABLE, load and freezing
Pavan Deolasee [EMAIL PROTECTED] wrote: In a typical scenario, user might create a table and load data in the table as part of a single transaction (e.g pg_restore). In this case, it would help if we create the tuples in the *frozen* state to avoid any wrap-around related issues with the table. Sounds cool. I recommended users to do VACUUM FREEZE just after initial loading, but we can avoid it with your method. Without this, very large read-only tables would require one round of complete freezing if there are lot of transactional activities in the other parts of the database. And when that happens, it would generate lots of unnecessary IOs on these large tables. To make things worse, the freezing day comes at once because the first restore is done in a single or near transactions; The wraparound timings of many tables are aligned at the same time. Freezing copy will be the solution. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATE TABLE, load and freezing
On Thu, Feb 28, 2008 at 3:05 PM, Florian G. Pflug [EMAIL PROTECTED] wrote: If that works, then we might also want to set the visibility hint bits. Oh yes. Especially because random time-scattered index scans on the table can actually generate multiple writes of a page of a read-only table. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE TABLE, load and freezing
On Thu, Feb 28, 2008 at 3:25 PM, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Sounds cool. I recommended users to do VACUUM FREEZE just after initial loading, but we can avoid it with your method. Yeah, and the additional step of VACUUM FREEZE adds up to the restore time. To make things worse, the freezing day comes at once because the first restore is done in a single or near transactions; The wraparound timings of many tables are aligned at the same time. Freezing copy will be the solution. If we can start with a freezed table and even if the table is subsequently updated, hopefully DSM (or something of that sort) will help us reduce the vacuum freeze time whenever its required. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
On Thu, Feb 28, 2008 at 1:19 AM, Tom Lane wrote: I think the question we have to answer is whether we want to be complicit in the spreading of a nonstandard UUID format. I don't. I have patched the UUID input and output functions to be compatible with Adobe ColdFusion (http://adobe.com/products/coldfusion/ uses 8x-4x-4x-16x), and while I have released them I have deliberately made the changes incompatible with other formats and will not submit them to PostgreSQL because I want Adobe to fix ColdFusion to use the standard format. Jochem ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: wildcards in pg_service.conf
Tom Lane wrote: I'd like to extend the libpq service file by allowing wildcards, e.g. like this: [%] host=dbhost.mycompany.com dbname=% Such an entry would match all service parameters, and all ocurrences of the wildcard right of a = would be replaced with the service parameter. This seems poorly thought out. How could you have any other service entries besides this one? What is the point of doing it like this and not simply overriding the service's database selection? You could have other service entries if you put them _before_ the wildcard entry. Your second critizism is valid - you could handle this case without wildcards. The special case for LDAP makes it even more obvious that this is a kluge. The LDAP case is the main motivation why I would like to have wildcards, so that all our databases could be handled with one entry in the service file. Currently we have to add an entry to the file for every new database we want to access. Do you think that the idea of wildcards for the service file is a bad one in general? Or could there be a more generally useful realization of that concept? Yours, Laurenz Albe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CREATE TABLE, load and freezing
Pavan Deolasee wrote: On Thu, Feb 28, 2008 at 3:05 PM, Florian G. Pflug [EMAIL PROTECTED] wrote: If that works, then we might also want to set the visibility hint bits. Oh yes. Especially because random time-scattered index scans on the table can actually generate multiple writes of a page of a read-only table. I remember that Simon tried to set hint bits as well when he wrote the skip WAL on new table optimization, but there was some issues with it. I can't remember the details, but I think it was related to commands in the same transaction seeing the tuples too early. Like triggers, or portals opened before the COPY. Hint bits is the critical part of the issue. If you can set the hint bits, then you can freeze as well, but freezing without setting hint bits doesn't buy you much. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] OSSP can be used in the windows environment now!
On Thu, Feb 28, 2008 at 09:38:00AM +0900, Hiroshi Saito wrote: Hi. - Original Message - From: Magnus Hagander [EMAIL PROTECTED] I take it you are in contact with them, since you helped them with the port? Can you ask them if they are interested in distributing that file? Yes, However, It is not discussing about MSVC. It is because it needed to think with correspondence of the generation process (xx.in) of the source code. Furthermore, uuid_cli had a problem more... Although I don't have the margin time now, it is taken as my TODO. I have applied the patch that we have for now. Thanks! And keep us posted on the progress with the official msvc build for them. If not, what do other people think about adding this Makefile and a README file to our contrib directory? If there is no contrary opinion, I believe that it is help to many people's Comments from others? Objections? //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] OSSP can be used in the windows environment now!
On Thu, Feb 28, 2008 at 12:22 PM, Magnus Hagander [EMAIL PROTECTED] wrote: If not, what do other people think about adding this Makefile and a README file to our contrib directory? If there is no contrary opinion, I believe that it is help to many people's Comments from others? Objections? If it's well documented which versions of MSVC++ work with it, and which versions of ossp-uuid, I don't see it as a major problem to include it. It's annoying for sure, but it's not the end of the world. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk ---(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] Logging conflicted queries on deadlocks
ITAGAKI Takahiro wrote: I wrote a small patch to log conflicted queries. With my patch, deadlock message will change as following: | ERROR: deadlock detected | DETAIL: Process 3468 waits for ShareLock on transaction 451; blocked by process 4068. | STATEMENT: UPDATE test SET i = i WHERE i = 1;- ***conflicted query*** | Process 4068 waits for ShareLock on transaction 450; blocked by process 3468. | STATEMENT: UPDATE test SET i = i WHERE i = 2; Cute. There are some open issues. One of the issues is that the killed query is logged by log_min_error_statement, but conflicted query is logged in DETAIL. Killed query is logged only on server but conflicted queries are sent to both server and client. In addition, if log_min_error_statement is less than ERROR level, only the conflicted queries are logged. -- it's inconsistent and unsymmetric. Perhaps it could be shown in CONTEXT, like so: | ERROR: deadlock detected | DETAIL: Process 3468 waits for ShareLock on transaction 451; blocked by process 4068. | Process 4068 waits for ShareLock on transaction 450; blocked by process 3468. | STATEMENT: UPDATE test SET i = i WHERE i = 2; | CONTEXT: process 3468: UPDATE test SET i = i WHERE i = 1; I think it's useful to show the PID of each statement, for the case where there are more than two processes deadlocked. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
[HACKERS] Silly Newbie question
To all, I am brand new to writing functions for PostgreSQL and understand that this question has been asked one hundred and one times. Please be patient with me, I do learn quickly. My question is: I wish to create a an aggregate function and have read and re-read the 34.10 section of the 8.3 documentation as well as scanned through the fmgr header file. My problem is that I know certain structures are created with the idea of retaining information for use in the state function but I don't really understand which structures I should be looking at and how to initilize them (if needed) in the sfunc. Any pushes in the right direction would be very appreciated, I am using the Version 1 calling convention. Thanks in advance. Aaron _ It's simple! Sell your car for just $30 at CarPoint.com.au http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT
Re: [HACKERS] Logging conflicted queries on deadlocks
ITAGAKI Takahiro wrote: There are some open issues. One of the issues is that the killed query is logged by log_min_error_statement, but conflicted query is logged in DETAIL. Killed query is logged only on server but conflicted queries are sent to both server and client. In addition, if log_min_error_statement is less than ERROR level, only the conflicted queries are logged. -- it's inconsistent and unsymmetric. Hm, your patches crashes for me when there are 3 sessions deadlocked. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Buildfarm member gypsy_moth seems not to like alignment patch
Tom Lane wrote:
This is unfortunate and surprising, since that patch was intended to
prevent compilers from making unsafe alignment assumptions, but it sure
looks like this compiler has instead added a new one. Could you poke
into it --- at least get a stack trace from the core dump?
Forgot some information about local variables:
(dbx) dump
toasttupDesc = 0xcf9538
chunk_size = 1996
t_values = ARRAY
toast_pointer = RECORD
chunk_seq = 1
rel = 0xcd8ff8
toastidx = 0xcf9858
toasttup = 0x8
toastrel = 0xcf9748
use_wal = '\001'
result = 0x1
data_p = 0xdbd354
use_fsm = '\001'
data_todo = 2286
mycid = 10U
__func__ = toast_save_datum
t_isnull = ARRAY
value = 14406480U
chunk_data = RECORD
(dbx) print toast_pointer
toast_pointer = {
va_rawsize= 11963
va_extsize= 2286
va_valueid= 10953U
va_toastrelid = 2838U
}
(dbx) print t_values
t_values = (10953U, 0, 4290673827U)
(dbx) print t_isnull
t_isnull =
(dbx) print chunk_data
chunk_data = {
hdr = {
vl_len_ =
vl_dat =
}
data =
}
-J
--
Jørgen Austvik, Software Engineering - QA
Sun Microsystems Database Technology Group
begin:vcard
fn;quoted-printable:J=C3=B8rgen Austvik
n;quoted-printable:Austvik;J=C3=B8rgen
org:Sun Microsystems;Database Technology Group
adr:;;Haakon VII gt. 7b;Trondheim;;NO-7485;Norway
email;internet:[EMAIL PROTECTED]
title:Senior Engineer
tel;work:+47 73 84 21 10
tel;fax:+47 73 84 21 01
tel;cell:+47 901 97 886
x-mozilla-html:FALSE
url:http://www.sun.com/
version:2.1
end:vcard
---(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] Buildfarm member gypsy_moth seems not to like alignment patch
Tom Lane wrote: It looks like gypsy_moth has been failing like this: creating directory /export/home/tmp/pg-test/build-suncc/HEAD/pgsql.21325/src/test/regress/./tmp_check/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 32MB/204800 creating configuration files ... ok creating template1 database in /export/home/tmp/pg-test/build-suncc/HEAD/pgsql.21325/src/test/regress/./tmp_check/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... Bus Error - core dumped child process exited with exit code 138 initdb: data directory /export/home/tmp/pg-test/build-suncc/HEAD/pgsql.21325/src/test/regress/./tmp_check/data not removed at user's request since I put in this patch: http://archives.postgresql.org/pgsql-committers/2008-02/msg00270.php This is unfortunate and surprising, since that patch was intended to prevent compilers from making unsafe alignment assumptions, but it sure looks like this compiler has instead added a new one. Could you poke into it --- at least get a stack trace from the core dump? Running initdb with debug: --888888-- $./initdb -D /export/home/tmp/test -d -n snip DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/35, nestlvl: 1, children: DEBUG: commit transaction DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... Bus Error - core dumped child process exited with exit code 138 initdb: data directory /export/home/tmp/test not removed at user's request --888888-- Stack trace: --888888-- $ /opt/SUNWspro8/bin/dbx long_path/postgres core program terminated by signal BUS (invalid address alignment) Current function is toast_save_datum 1171 SET_VARSIZE(chunk_data, chunk_size + VARHDRSZ); (dbx) where =[1] toast_save_datum(rel = 0xcd8ff8, value = 14406480U, use_wal = \001', use_fsm = '\001'), line 1171 in tuptoaster.c [2] toast_insert_or_update(rel = 0xcd8ff8, newtup = 0xdba3e8, oldtup = (nil), use_wal = '\001', use_fsm = '\001'), line 700 in tuptoaster.c [3] heap_insert(relation = 0xcd8ff8, tup = 0xdba3e8, cid = 10U, use_wal = '\001', use_fsm = '\001'), line 1815 in heapam.c [4] simple_heap_insert(relation = 0xcd8ff8, tup = 0xdba3e8), line 1937 in heapam.c [5] InsertRule(rulname = 0xdaf730 _RETURN, evtype = 1, eventrel_oid = 10950U, evslot_index = -1, evinstead = '\001', event_qual = (nil), action = 0xdaf760, replace = '\0'), line 134 in rewriteDefine.c [6] DefineQueryRewrite(rulename = 0xdaf730 _RETURN, event_relid = 10950U, event_qual = (nil), event_type = CMD_SELECT, is_instead = '\001', replace = '\0', action = 0xdaf760), line 461 in rewriteDefine.c [7] DefineViewRules(viewOid = 10950U, viewParse = 0xdab070, replace = '\0'), line 275 in view.c [8] DefineView(stmt = 0xd3f920, queryString = 0xd9b888 /*\n * PostgreSQL System Views\n *\n * Copyright (c) 1996-2008, PostgreSQL Global Development Group\n *\n * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.47 2007/10/22 20:13:37 tgl Exp $\n */\n\nCREATE VIEW pg_roles AS \nSELECT \n rolname,\nrolsuper,\nrolinherit,\n rolcreaterole,\nrolcreatedb,\nrolcatupdate,\n rolcanlogin,\nrolconnlimit,\n''::text as rolpassword,\nrolvaliduntil,\nrolconfig,\noid\n FROM pg_aut ...), line 447 in view.c [9] ProcessUtility(parsetree = 0xd3f920, queryString = 0xd9b888 /*\n * PostgreSQL System Views\n *\n * Copyright (c) 1996-2008, PostgreSQL Global Development Group\n *\n * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.47 2007/10/22 20:13:37 tgl Exp $\n */\n\nCREATE VIEW pg_roles AS \nSELECT \n rolname,\nrolsuper,\nrolinherit,\n rolcreaterole,\nrolcreatedb,\nrolcatupdate,\n rolcanlogin,\nrolconnlimit,\n''::text as rolpassword,\nrolvaliduntil,\nrolconfig,\noid\n FROM pg_aut ..., params = (nil), isTopLevel = '\0', dest = 0xbb1534, completionTag = 0xffbef64c ), line 894 in utility.c [10] PortalRunUtility(portal = 0xd39e68, utilityStmt = 0xd3f920, isTopLevel = '\0', dest = 0xbb1534, completionTag = 0xffbef64c ), line 1178 in pquery.c [11] PortalRunMulti(portal = 0xd39e68, isTopLevel = '\0', dest = 0xbb1534, altdest = 0xbb1534, completionTag = 0xffbef64c ), line 1266 in pquery.c [12] PortalRun(portal = 0xd39e68, count = 2147483647, isTopLevel = '\0', dest = 0xbb1534, altdest = 0xbb1534, completionTag = 0xffbef64c ), line 814 in pquery.c [13] exec_simple_query(query_string = 0xd2fe38 /*\n * PostgreSQL System Views\n
Re: [HACKERS] CREATE TABLE, load and freezing
ITAGAKI Takahiro wrote: Without this, very large read-only tables would require one round of complete freezing if there are lot of transactional activities in the other parts of the database. And when that happens, it would generate lots of unnecessary IOs on these large tables. To make things worse, the freezing day comes at once because the first restore is done in a single or near transactions; The wraparound timings of many tables are aligned at the same time. Freezing copy will be the solution. Hm.. Couldn't we eliminate that particular concern easily by adding some randomness to the freeze_age? regards, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Silly Newbie question
Aaron Spiteri wrote: To all, I am brand new to writing functions for PostgreSQL and understand that this question has been asked one hundred and one times. Please be patient with me, I do learn quickly. My question is: I wish to create a an aggregate function and have read and re-read the 34.10 section of the 8.3 documentation as well as scanned through the fmgr header file. My problem is that I know certain structures are created with the idea of retaining information for use in the state function but I don't really understand which structures I should be looking at and how to initilize them (if needed) in the sfunc. Any pushes in the right direction would be very appreciated, I am using the Version 1 calling convention. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT This is not the correct forum for such questions. Please ask again on pgsql-general. pgsql-hackers is about development of postgresql itself. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Producer/Consumer Issues in the COPY across network
On Thu, Feb 28, 2008 at 01:57:49AM +, Simon Riggs wrote: AIUI the server merely streams the rows to you, the client doesn't get to say how many :) Right, but presumably we generate a new message per PQgetCopyData() request? So my presumption is we need to wait for that to be generated each time? No, PQgetCopyData() doesn't send anything. It merely reads what's in the kernel socket buffer to a local buffer and when it has a complete line it mallocs a string and returns it to you. Similarly, PQputCopyData() doesn't expect anything from the server during transmission. That's why I was wondering about the rows per packet. Sending bigger packets reduces overall overhead. (The malloc/free per row doesn't seem too efficient.) Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Tom, I think the question we have to answer is whether we want to be complicit in the spreading of a nonstandard UUID format. Even if we answer yes for this HP case, it doesn't follow that we should create a mechanism for anybody to do anything with 'em. That way lies the madness people already have to cope with for datetime data :-( Well, I guess the question is: if we don't offer some builtin way to render non-standard formats built into company products, will those companies fix their format or just not use PostgreSQL? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(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] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
On Thu, Feb 28, 2008 at 08:58:01AM -0800, Josh Berkus wrote: Well, I guess the question is: if we don't offer some builtin way to render non-standard formats built into company products, will those companies fix their format or just not use PostgreSQL? Well, there is an advantage that Postgres has that some others don't: you can extend Postgres pretty easily. That suggests to me a reason to be conservative in what we build in. This is consistent with the principle, Be conservative in what you send, and liberal in what you accept. A ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Google SoC 2008?
Where can I see a list of projects? Thanks! Joey
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Well, I guess the question is: if we don't offer some builtin way to render non-standard formats built into company products, will those companies fix their format or just not use PostgreSQL? Well, there is an advantage that Postgres has that some others don't: you can extend Postgres pretty easily. That suggests to me a reason to be conservative in what we build in. This is consistent with the principle, Be conservative in what you send, and liberal in what you accept. Well, then the uuid input function should most likely disregard all -, and accept the 4x-, 8x- formats and the like on input. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] OSSP can be used in the windows environment now!
Hi. - Original Message - From: Dave Page [EMAIL PROTECTED] On Thu, Feb 28, 2008 at 12:22 PM, Magnus Hagander [EMAIL PROTECTED] wrote: If not, what do other people think about adding this Makefile and a README file to our contrib directory? If there is no contrary opinion, I believe that it is help to many people's Comments from others? Objections? If it's well documented which versions of MSVC++ work with it, and which versions of ossp-uuid, I don't see it as a major problem to include it. It's annoying for sure, but it's not the end of the world. Yeah:-) Ok, I will write README and desire to be applied by your refereeing and an arrangement.! Thanks. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
On Thu, Feb 28, 2008 at 08:06:46PM +0100, Zeugswetter Andreas ADI SD wrote: Well, I guess the question is: if we don't offer some builtin way to render non-standard formats built into company products, will those companies fix their format or just not use PostgreSQL? Well, there is an advantage that Postgres has that some others don't: you can extend Postgres pretty easily. That suggests to me a reason to be conservative in what we build in. This is consistent with the principle, Be conservative in what you send, and liberal in what you accept. Well, then the uuid input function should most likely disregard all -, and accept the 4x-, 8x- formats and the like on input. Andreas We need to support the standard definition. People not using the standard need to know that and explicitly acknowledge that by implementing the conversion process themselves. Accepting random input puts a performance hit on everybody following the standard. It is the non-standard users who should pay that cost. Cheers, Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Producer/Consumer Issues in the COPY across network
On Thu, 2008-02-28 at 15:39 +0100, Martijn van Oosterhout wrote: That's why I was wondering about the rows per packet. Sending bigger packets reduces overall overhead. (The malloc/free per row doesn't seem too efficient.) I guess neither of us know then. Oh well. That's good 'cos it sounds like something worth looking into if anybody has a protocol sniffer and some time. I'll skip on that test 'cos its not really my area. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Google SoC 2008?
Joey, Where can I see a list of projects? You can look at last year's. We're updating for this year. http://www.postgresql.org/developer/summerofcode -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Need Mentors for Google Summer of Code!
Hackers, Over the last 2 years, Google Summer of Code has been responsible for several new features of PostgreSQL: XML, ghost XID, phpPgAdmin upgrade, and more, as well as some new long-term contributors to the project. We want to have an equally good summer this year. So: we need PostgreSQL contributors to volunteer as mentors. As well as the core code, *any* project in the PostgreSQL family is eligible, including drivers, GUIs, documentation, replication, applications and tools. Don't be shy! Pass this along to sub-projects of PostgreSQL which might be eligible. Mentors must be patient, friendly, easy to stay in touch with, knowledgeable about their project areas, and able to commit to spending several hours a week helping their student(s) from May to August. GSOC is an unparalled opportunity for our project to recruit new contributors, and get students interested in databases. We have less than week to get everything together. So please respond soon! -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] CREATE TABLE, load and freezing
On Thu, Feb 28, 2008 at 3:08 AM, in message [EMAIL PROTECTED], Pavan Deolasee [EMAIL PROTECTED] wrote: I had this idea sometime back. Not sure if this has been discussed before There was a thread discussing the problems you're looking to address: http://archives.postgresql.org/pgsql-performance/2007-12/msg00230.php I don't know if this is a real problem for anybody, but I could think of its use case, at least in theory. Yeah, it's real. We are now doing a VACUUM FREEZE of a table or database which has been freshly loaded. If you can load them frozen and/or with hint bits, that would reduce the time to bring a database online. It would be much appreciated here. -Kevin ---(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] Google SoC 2008?
On Thu, Feb 28, 2008 at 3:43 PM, in message [EMAIL PROTECTED], Josh Berkus [EMAIL PROTECTED] wrote: http://www.postgresql.org/developer/summerofcode That page contains a link which is dead. This line: More information on these projects can be found on Google's PostgreSQL SoC page. Links to: http://code.google.com/soc/postgres/about.html Perhaps it should link to?: http://code.google.com/soc/2007/postgres/about.html Of course, that page gave me pause, with this sentence: We are widely recognized as the second best known open source database. Second best -Kevin ---(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] Need Mentors for Google Summer of Code!
On Thu, Feb 28, 2008 at 4:48 PM, Josh Berkus [EMAIL PROTECTED] wrote: Mentors must be patient, friendly, easy to stay in touch with, knowledgeable about their project areas, and able to commit to spending several hours a week helping their student(s) from May to August. Unlike last time, I now have a lot more time I can devote to this, so count me in. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Kenneth Marshall wrote: conversion process themselves. Accepting random input puts a performance hit on everybody following the standard. Why is that necessarily the case? Why not have a liberal parser and a configurable switch that determines whether non-standard forms are liberally accepted, accepted with a logged warning, or rejected? James ---(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] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
James Mansion wrote: Kenneth Marshall wrote: conversion process themselves. Accepting random input puts a performance hit on everybody following the standard. Why is that necessarily the case? Why not have a liberal parser and a configurable switch that determines whether non-standard forms are liberally accepted, accepted with a logged warning, or rejected? I recall there being a measurable performance difference between the most liberal parser, and the most optimized parser, back when I wrote one for PostgreSQL. I don't know how good the one in use for PostgreSQL 8.3 is. As to whether the cost is noticeable to people or not - that depends on what they are doing. The problem is that a UUID is pretty big, and parsing it liberally means a loop. My personal opinion is that this is entirely a philosophical issue, and that both sides have merits. There is no reason for PostgreSQL to support all formats, not matter how non-standard, for every single type. So, why would UUID be special? Because it's easy to do is not necessarily a good reason. But then, it's not a bad reason either. Cheers, mark -- Mark Mielke [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Mark Mielke wrote: I recall there being a measurable performance difference between the most liberal parser, and the most optimized parser, back when I wrote one for PostgreSQL. I don't know how good the one in use for PostgreSQL 8.3 is. As to whether the cost is noticeable to people or not - that depends on what they are doing. The problem is that a UUID is pretty big, and parsing it liberally means a loop. It just seems odd - I would have thought one would use re2c or ragel to generate something and the performance would essentially be O[n] on the input length in characters - using either a collection of allowed forms or an engine that normalises case and discards the '-' characters between any hex pairs. So yes these would have a control loop. Is that so bad? Either way its hard to imagine how parsing a string of this length could create a measurable performance issue compared to what will happen with the value post parse. James ---(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] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
On Thu, Feb 28, 2008 at 06:45:18PM -0500, Mark Mielke wrote: My personal opinion is that this is entirely a philosophical issue, and that both sides have merits. I think it depends on what you're optimising for: initial development time, maintaince time or run time. There is no reason for PostgreSQL to support all formats, not matter how non-standard, for every single type. So, why would UUID be special? Because it's easy to do is not necessarily a good reason. But then, it's not a bad reason either. I never really buy the performance argument. I much prefer the correctness argument, if the code is doing something strange I'd prefer to know about it as soon as possible. This generally means that I'm optimising for maintaince. It's a similar argument to why lots of automatic casts were removed from 8.3, it generally doesn't hurt but the few times it does it's going to be bad and if you're doing something strange to start with it's better to be explicit about it. Sam ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: wildcards in pg_service.conf
On Thu, Feb 28, 2008 at 11:27 AM, Albe Laurenz [EMAIL PROTECTED] wrote: The LDAP case is the main motivation why I would like to have wildcards, so that all our databases could be handled with one entry in the service file. Currently we have to add an entry to the file for every new database we want to access. I am very much +1 for it. I think it would be very useful. Do you think that the idea of wildcards for the service file is a bad one in general? Or could there be a more generally useful realization of that concept? The use of [%] in pg_service.conf is well... ugly. :) (At the same time I would have come up with exactly the same idea for the syntax...) As for LDAP string expansion I think it would be convenient to provide a subset of log_line_prefix %x expansions, like: %u User name %d Database name %r Remote host name or IP address, and remote port %h Remote host name or IP address %% Literal % ...I am not sure if %r or %h is a good idea. Thoughts? As for the syntax of configuration file, using [%] implies that things like [proj%db] would also be valid, which would be harder to implement. Perhaps empty bracers [] would be better? Ugly aswell, but would not suggest you can use [pr%db%test] and expect it to work. And could be made as 'last match' regardless of the in-file order. Regards, Dawid ---(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
[HACKERS] could not open relation 1663/16384/16584: No such file or directory in a specific combination of transactions with temp tables
Architecture: Intel Core 2 Duo OS: linux-2.6.20-gentoo-r8 Filesystem: ext3 Postgres v8.2.3 compiled with gcc 4.1.1-r3 RAM - 2GB Shared buffers - 24MB [All other Postgres configuration parameters are default values] Problem description: COPY into temp table fails using a specific combination of create/insert on temp tables, prepare/commit in subsequent transactions. The could not open relation error occurs reliably. Steps to reproduce: Existing schema (scripts to create and populate these tables are uploaded to http://upload2.net/page/download/rfsLfnuVlYjEcCJ/basetables.tgz.html ): In the scenario, the following 4 tables exist already in the database: postgres=# \d order_detail Table public.order_detail Column |Type | Modifiers --+-+--- order_id| integer | not null item_id | integer | not null order_date| timestamp without time zone | emp_id | integer | promotion_id | integer | qty_sold | integer | unit_price | bigint| unit_cost | bigint| discount | integer | customer_id | integer | Indexes: order_detail_pkey PRIMARY KEY, btree (order_id, item_id) postgres=# select count(*) from order_detail; count --- 34352 (1 row) postgres=# \d lu_call_ctr Table public.lu_call_ctr Column | Type | Modifiers -+---+--- call_ctr_id| integer | not null center_name | character(50) | region_id | integer | manager_id | integer | country_id | integer | dist_ctr_id | bigint | Indexes: lu_call_ctr_pkey PRIMARY KEY, btree (call_ctr_id) postgres=# select count(*) from lu_call_ctr; count --- 1 (1 row) postgres=# \d lu_employee Table public.lu_employee Column|Type | Modifiers +-+--- emp_id | integer | not null emp_last_name | character(50) | emp_first_name | character(50) | emp_ssn | character(50)| birth_date | timestamp without time zone | hire_date | timestamp without time zone | salary | integer | country_id| integer | dist_ctr_id| integer | manager_id | integer | call_ctr_id| integer | fte_flag| character(50)| Indexes: lu_employee_pkey PRIMARY KEY, btree (emp_id) postgres=# select count(*) from lu_employee; count --- 2 (1 row) postgres=# \d city_ctr_sls Table public.city_ctr_sls Column | Type | Modifiers --+-+--- cust_city_id | integer| not null call_ctr_id | integer| not null tot_dollar_sales | integer | tot_unit_sales | integer| tot_cost | integer | Indexes: city_ctr_sls_pkey PRIMARY KEY, btree (cust_city_id, call_ctr_id) postgres=# select count(*) from city_ctr_sls; count --- 548 (1 row) Given the the data in these base tables, the following set of SQLs always generates the Could not open relation error. The insert*.log files that we try to COPY into Postgres in the SQLs below are uploaded as: http://upload2.net/page/download/gADZqQvOIntLRpI/insert.tgz.html - -- Note: If the amount of data being inserted is decreased significantly, -- the error disappears. -- First transaction begin transaction; -- Temp table 1 and insert 1582 records create temp table temp1 as select customer_id, emp_id from order_detail limit 0; copy temp1 from '/tmp/relationError/insert_1.log'; -- Create temp table 2 and populate with select. -- Note: Even changing the order of these columns causes the error to -- disappear. create temp table temp2 as select temp1.customer_id, temp1.emp_id as temp1__emp_id, le.emp_id as le__emp_id, le.emp_last_name, le.emp_first_name, le.emp_ssn, le.birth_date, le.hire_date, le.salary, le.country_id, le.dist_ctr_id, le.manager_id, le.call_ctr_id, le.fte_flag from temp1, lu_employee le where temp1.emp_id = le.emp_id; -- Create temp table 3 and insert 13832 records. create temp table temp3 as select temp2.call_ctr_id, temp2.temp1__emp_id, temp2.customer_id,
[HACKERS] A couple of PG schedule reminders
Don't forget that our first commit fest for 8.4 development starts Saturday March 1. If you've got a patch that you'd like reviewed in this fest, it's time to send it in. (But there's plenty more fests to come, so don't panic about it.) Also, 8.3.0 has been out for almost a month, so it's time to start thinking about 8.3.1. The current plan is to freeze 8.3.1 on Thursday Mar 13 for public announcement Monday Mar 17; though of course this could change in the event of discovery of disastrous bugs. A note about the relationship of these events: IMHO commit fest should not affect our handling of maintenance of existing releases. Investigation and patching of bugs that are in the field has always had high priority regardless of where we are in a development/release cycle, and I don't see commit fests as changing that. But if anyone wants to argue for a different policy, feel free to do so... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Batch update of indexes on data loading
ITAGAKI Takahiro [EMAIL PROTECTED] writes: BTW, why REINDEX requires access exclusive lock? Read-only queries are forbidden during the operation now, but I feel they are ok because REINDEX only reads existing tuples. Can we do REINDEX holding only shared lock on the index? No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CREATE TABLE, load and freezing
Pavan Deolasee [EMAIL PROTECTED] writes: In a typical scenario, user might create a table and load data in the table as part of a single transaction (e.g pg_restore). In this case, it would help if we create the tuples in the *frozen* state to avoid any wrap-around related issues with the table. We've heard that idea before, and it's just as bad as it was when proposed before. Pre-frozen tuples eliminate any possibility of tracking when a tuple was inserted; which is extremely important to know when you are trying to do forensic analysis of a broken table. The point of the current design is to not throw away information about tuple insertion time until the tuple is old enough that the info is (probably) not interesting anymore. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Andrew Sullivan [EMAIL PROTECTED] writes: Be conservative in what you send, and liberal in what you accept. Yeah, I was about to quote that same maxim myself. I don't have a big problem with allowing uuid_in to accept known format variants. (I'm not sure about allowing a hyphen *anywhere*, because that could lead to accepting things that weren't meant to be a UUID at all, but this HP format seems regular enough that that's not a serious objection to it.) What I was really complaining about was Josh's suggestion that we invent a function to let users *output* UUIDs in random-format-of-the-week. I can't imagine much good coming of that. I think we should keep uuid_out emitting only the RFC-standardized format. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] OSSP can be used in the windows environment now!
Dave Page [EMAIL PROTECTED] writes: On Thu, Feb 28, 2008 at 12:22 PM, Magnus Hagander [EMAIL PROTECTED] wrote: If not, what do other people think about adding this Makefile and a README file to our contrib directory? Comments from others? Objections? If it's well documented which versions of MSVC++ work with it, and which versions of ossp-uuid, I don't see it as a major problem to include it. It's annoying for sure, but it's not the end of the world. I'd like somebody to close the loop with upstream OSSP authors first. If they don't see anything broken about the makefile, and indicate intention to incorporate it in some future release, then it's okay to put it in our CVS temporarily. If they don't like it then we'd better understand why. There's also the possibility that they put out a release including it next week, in which case we hardly need it in our CVS. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATE TABLE, load and freezing
On Fri, Feb 29, 2008 at 9:15 AM, Tom Lane [EMAIL PROTECTED] wrote: We've heard that idea before, and it's just as bad as it was when proposed before. Pre-frozen tuples eliminate any possibility of tracking when a tuple was inserted; which is extremely important to know when you are trying to do forensic analysis of a broken table. The point of the current design is to not throw away information about tuple insertion time until the tuple is old enough that the info is (probably) not interesting anymore. Understood. But if we consider a special case of creation and loading of a table in a single transaction, we can possibly save the information that the table was loaded with pre-frozen tuples with xmin equals to the transaction creating the table. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Silly Newbie question
Aaron Spiteri [EMAIL PROTECTED] writes: I wish to create a an aggregate function and have read and re-read the 34.10 section of the 8.3 documentation as well as scanned through the fmgr header file. My problem is that I know certain structures are created with the idea of retaining information for use in the state function but I don't really understand which structures I should be looking at and how to initilize them (if needed) in the sfunc. Any pushes in the right direction would be very appreciated, I am using the Version 1 calling convention. Are there any existing aggregates that do something similar to what you need? If so, try taking a look at their source code. The general rule in writing C code for Postgres is that it's an open-book test ;-) --- you are expected to look at the code, not only the documentation. 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] Silly Newbie question
Andrew Dunstan [EMAIL PROTECTED] writes: Aaron Spiteri wrote: I wish to create a an aggregate function and have read and re-read the 34.10 section of the 8.3 documentation as well as scanned through the fmgr header file. This is not the correct forum for such questions. Please ask again on pgsql-general. pgsql-hackers is about development of postgresql itself. In the past we've encouraged people who are writing C-code additions to discuss on -hackers. I'm not sure that -general is a better place for that; what fraction of -general readers do you think are interested or qualified to help? I recall seeing similar questions lately on -novice, where they *surely* seem out of place. If we've scared people away from -hackers to the extent that they post C-code-hacking questions on -novice, I think there's something wrong... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Silly Newbie question
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Aaron Spiteri wrote: I wish to create a an aggregate function and have read and re-read the 34.10 section of the 8.3 documentation as well as scanned through the fmgr header file. This is not the correct forum for such questions. Please ask again on pgsql-general. pgsql-hackers is about development of postgresql itself. In the past we've encouraged people who are writing C-code additions to discuss on -hackers. I'm not sure that -general is a better place for that; what fraction of -general readers do you think are interested or qualified to help? I recall seeing similar questions lately on -novice, where they *surely* seem out of place. If we've scared people away from -hackers to the extent that they post C-code-hacking questions on -novice, I think there's something wrong... OK, fair enough. cheers andrew ---(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] Batch update of indexes on data loading
2008/2/29, Tom Lane [EMAIL PROTECTED]: ITAGAKI Takahiro [EMAIL PROTECTED] writes: BTW, why REINDEX requires access exclusive lock? Read-only queries are forbidden during the operation now, but I feel they are ok because REINDEX only reads existing tuples. Can we do REINDEX holding only shared lock on the index? No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. Can a shared lock be taken at first, and when the new index is ready, in order to delete the old index, elevate that lock to an exclusive one? Markus -- Markus Bertheau Blog: http://www.bluetwanger.de/blog/ ---(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] Batch update of indexes on data loading
Markus Bertheau [EMAIL PROTECTED] writes: 2008/2/29, Tom Lane [EMAIL PROTECTED]: No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. Can a shared lock be taken at first, and when the new index is ready, in order to delete the old index, elevate that lock to an exclusive one? You could try, but lock upgrades are generally a recipe for increasing your risk of deadlock failure. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
James Mansion wrote: Mark Mielke wrote: I recall there being a measurable performance difference between the most liberal parser, and the most optimized parser, back when I wrote one for PostgreSQL. I don't know how good the one in use for PostgreSQL 8.3 is. As to whether the cost is noticeable to people or not - that depends on what they are doing. The problem is that a UUID is pretty big, and parsing it liberally means a loop. It just seems odd - I would have thought one would use re2c or ragel to generate something and the performance would essentially be O[n] on the input length in characters - using either a collection of allowed forms or an engine that normalises case and discards the '-' characters between any hex pairs. Instruction level parallelism allows for multiple hex values to be processed in parallel, whereas a loop relies on branch prediction and speculative load and store? :-) The liberal version is difficult to unroll. The strict version is easy to unroll. So yes these would have a control loop. Is that so bad? Either way its hard to imagine how parsing a string of this length could create a measurable performance issue compared to what will happen with the value post parse. I think so too. Cheers, mark -- Mark Mielke [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
On Fri, Feb 29, 2008 at 9:26 AM, Tom Lane [EMAIL PROTECTED] wrote:
Andrew Sullivan [EMAIL PROTECTED] writes:
Be conservative in what you send, and liberal in what you accept.
Yeah, I was about to quote that same maxim myself. I don't have a big
problem with allowing uuid_in to accept known format variants. (I'm
not sure about allowing a hyphen *anywhere*, because that could lead to
accepting things that weren't meant to be a UUID at all, but this HP
format seems regular enough that that's not a serious objection to it.)
This seems like a good enough opportunity to mention an idea that I
had while/after doing the enum patch. The patch was fairly intrusive
for something that was just adding a type because postgresql isn't
really set up for parameterized types other than core types. The idea
would be to extend the enum mechanism to allow UDTs etc to be
parameterized, and enums would just become one use of the mechanism.
Other obvious examples that I had in mind were allowing variable
lengths for that binary data type with hex IO for e.g. differently
sized checksums that people want, and allowing different formats for
uuids.
So the idea as applied to this case would be to do the enum-style
typesafe thing, ie:
create type coldfusion_uuid as generic_uuid('---');
...then just use that. I had some thoughts about whether it would be
worth allowing inline declarations of such types inside table creation
statements as well, and there are various related issues and thoughts
on implementation which I won't go into in this email. Do people think
the idea has legs, though?
What I was really complaining about was Josh's suggestion that we invent
a function to let users *output* UUIDs in random-format-of-the-week.
I can't imagine much good coming of that. I think we should keep
uuid_out emitting only the RFC-standardized format.
Well, if the application is handing them to us in that format, it
might be a bit surprised if it gets back a fixed one. The custom
type approach wouldn't have that side effect.
Cheers
Tom
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] UUID data format 4x-4x-4x-4x-4x-4x-4x-4x
Tom Dunstan [EMAIL PROTECTED] writes: This seems like a good enough opportunity to mention an idea that I had while/after doing the enum patch. The patch was fairly intrusive for something that was just adding a type because postgresql isn't really set up for parameterized types other than core types. The idea would be to extend the enum mechanism to allow UDTs etc to be parameterized, and enums would just become one use of the mechanism. Isn't this reasonably well covered by Teodor's work to support typmods for user-defined types? We've discussed how the typmod could be effectively a key into a system catalog someplace, thus allowing it to represent more than just an int32 worth of stuff. I'm not seeing where your proposal accomplishes more than that can. 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] Batch update of indexes on data loading
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Can we do REINDEX holding only shared lock on the index? No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. Hmm... Is it ok if the index will *not* instantaneously disappear? It's not impossible but I really question whether it'd be worth the complexity. There was something very closely related just yesterday about whether DROP INDEX has to take exclusive lock ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Batch update of indexes on data loading
Tom Lane [EMAIL PROTECTED] wrote: Can we do REINDEX holding only shared lock on the index? No. When you commit the reindex, the old copy of the index will instantaneously disappear; it will not do for someone to be actively scanning that copy. Hmm... Is it ok if the index will *not* instantaneously disappear? Keeping the old copy for a while and removing it after all transactions are finished. It would be pending truncate entries, that is something like pending unlink entries in 8.3. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
