[HACKERS] Synchronization Primitives
Hi all: I am a fresh men in PostgreSQL. And i work on benchmark study these days using PostgreSQL. Now i have a question: Is there some way to show the lock contention of PostgreSQL? As I know, you can use *show mutex status* in MySQL to find which mutex is hot. But i don't know in PostgreSQL. Any ideas? Thanks!
[HACKERS] Oprofile with postgresql
Hi all:
Recently i do a test of postgresql. To get more information of the
functions in PostgreSQL, i use
opgprof and opannotate, which are two tools in Oprofile. But i can't work
with the tools correctly.
PostgreSQL is compiled with -g option and the errors are like this:
opgprof error: parse_filename() invalid filename:
/var/lib/oprofile/samples/current/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{dep}/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{cg}/{root}/usr/bin/oprofiled/CPU_CLK_UNHALTED.9.0.all.all.all
and opannotate
warning: [heap] (tgid:7302 range:0x8e7000-0xa18000) could not be found.
warning: [vdso] (tgid:7295 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7296 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7297 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7298 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7299 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7300 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7301 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7302 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
opannotate (warning): unable to open for reading: aset.c
opannotate (warning): unable to open for reading: heaptuple.c
opannotate (warning): unable to open for reading: bufmgr.c
opannotate (warning): unable to open for reading: execQual.c
opannotate (warning): unable to open for reading: list.c
opannotate (warning): unable to open for reading: nbtree.c
opannotate (warning): unable to open for reading: fmgr.c
opannotate (warning): unable to open for reading: catcache.c
opannotate (warning): unable to open for reading: nodeIndexscan.c
opannotate (warning): unable to open for reading: clauses.c
opannotate (warning): unable to open for reading: ri_triggers.c
opannotate (warning): unable to open for reading: lock.c
opannotate (warning): unable to open for reading: planner.c
opannotate (warning): unable to open for reading: selfuncs.c
opannotate (warning): unable to open for reading: postgres.c
opannotate (warning): unable to open for reading: trigger.c
...
if i want to use the two tools on PostgreSQL , what should i do?
Best wishes
Yyan
Re: [HACKERS] Script binaries renaming
Bruce Momjian napsal(a): Where are we on this? Tom thinks we don't want this. TODO has: I plan to send survey on general list about it today. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Pl/Java broken since Postgresql 8.3-rc1
Hi there, as you now is plJava broken with the actual security releases. There is a pljava.dll at http://www.ejurka.com/pgsql/pljava/83rc1/ to fix it for version 8.3RC1. Is a pljava.dll for version 8.2.6 out? It's very important for me, need it for my office. Juergen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] data access automatic filter
Hi, I'm evaluating the option of write a PG-extension which allow the application to apply some per-connection filters. This filters work at table level and remove records which aren't allowed from a select result-set. Example: CREATE CONNECTION DATA FILTER my_filter CHECKING my_table.my_field=5; A SELECT * FROM my_table will only return all the rows with field=5 Could someone tell me which is the PG source files which I could change? TIA Tiscali Tandem Free (Telefono+Adsl). Attiva entro il giorno 15/11/07: chiami in tutta Italia e navighi senza limiti a SOLI 9,95 € AL MESE FINO A PRIMAVERA 2008! http://abbonati.tiscali.it/adsl/prodotti/tc/tandemfree_tel_cc/ ---(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] status of PlPython
I wonder if I should use PlPython in my projects or not. Browsing on the Python list it seems that nobody is using it. Browsing on Google Groups I find worrysome threads talking about removing support from it in future versions of PostgreSQL. So, I would like to know what's the current status of PlPython. Assuming it is in good health, I would like to know how people are using it, how do you keep your code in subversion, how do you debug it, what are the pitfalls, etc. What sources of information are out there, apart from the postgres manual? TIA, Michele Simionato ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] return varchar from C function
Hi, just for fun, I wrote a little postgresql contrib, who has a C function called myfun inside it. The function myfun returns a value , now I return a cstring type value, and it works fine if I run from psql shell: select value from myfun(paramteres); but I can't do an insert like: insert (charfield) select value from myfun(paramteres); becuse I have an error, exactly casting error. I want that myfun returns a char,varchar, or text type. Where I can find more documentation about differences between cstring,char,text etc...? Regards, Enrico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Problem in buidind Postgres with mingw
Hello we've some trouble with buildinq postgreSQL (8.1.4.tar.gz) from code with Mingw on a windows xp home machine. We've installed mingw 5.0.3 and msys 1.0.10. From the msys console we've typed the following command: $ LDFLAGS=-lstdc++ configure --without-zlib and that's seems ok. then we do: $make the outcome is the following: Paolo [EMAIL PROTECTED] /d/msys/1.0/home/src/postgresql-8.1.4 $ make make -C doc all make[1]: Entering directory `/d/msys/1.0/home/src/postgresql-8.1.4/doc' gzip -d -c man.tar.gz | /bin/tar xf - for file in man1/*.1; do \ mv $file $file.bak \ sed -e 's/\\fR(l)/\\fR(7)/' $file.bak $file \ rm -f $file.bak || exit; \ done /bin/sh.exe ../config/mkinstalldirs man7 mkdir -p -- man7 for file in manl/*.l; do \ sed -e '/^\.TH/s/l/7/' \ -e 's/\\fR(l)/\\fR(7)/' \ $file man7/`basename $file | sed 's/.l$/.7/'` || exit; \ done make[1]: Leaving directory `/d/msys/1.0/home/src/postgresql-8.1.4/doc' make -C src all make[1]: Entering directory `/d/msys/1.0/home/src/postgresql-8.1.4/src' make -C port all make[2]: Entering directory `/d/msys/1.0/home/src/postgresql-8.1.4/src/port' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../. ./src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/po rt/win32 -c -o crypt.o crypt.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../. ./src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/po rt/win32 -c -o fseeko.o fseeko.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after- statement -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../. ./src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../src/include/po rt/win32 -c -o getrusage.o getrusage.c In file included from ../../src/include/rusagestub.h:17, from getrusage.c:18: d:/MinGW/bin/../lib/gcc/mingw32/3.4.2/../../../../include/sys/time.h:27: error: redefinition of `struct timezone' d:/MinGW/bin/../lib/gcc/mingw32/3.4.2/../../../../include/sys/time.h:40: error: conflicting types for 'gettimeofday' ../../src/include/port.h:266: error: previous declaration of 'gettimeofday' was here d:/MinGW/bin/../lib/gcc/mingw32/3.4.2/../../../../include/sys/time.h:40: error: conflicting types for 'gettimeofday' ../../src/include/port.h:266: error: previous declaration of 'gettimeofday' was here make[2]: *** [getrusage.o] Error 1 make[2]: Leaving directory `/d/msys/1.0/home/src/postgresql-8.1.4/src/port' make[1]: *** [all] Error 2 make[1]: Leaving directory `/d/msys/1.0/home/src/postgresql-8.1.4/src' make: *** [all] Error 2 Please can somebody help us? Claudio. Maurizio. Paolo. -- Chi punta sullinglese naturale Wall Street Institute, vince un English Box! Scopri come ritirare i tuoi premi, clicca qui! http://click.libero.it/wallstreet14nov ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] hstore isexists
'exists' isn't a good name for function :(. Yeah, that isn't going to work. Perhaps ifexists? Or just leave well enough alone. Darn. Can't have been thinking clearly this morning. How about exist (no s)? Maybe 'found', 'present', or 'contains'? (no, I haven't checked for a grammar conflict) -- Korry
Re: [HACKERS] find the template of a database in SQL
When I have a table that I don't know if it inherits from template 1, how can I find the 'super' ? I do not believe that tables have templates in 7.4 unless you specifically use a query like: create table foo as select * from bar; I think Walter wants to know if a given table was defined in the current database, or if it was created from the template database (template0 or template1) when the database was created. I don't think you can tell. When you create a new database from a template (typically template1), you're just making a copy of that template. It's not really inheritance (meaning that a change to the table definition in the template won't affect any databases cloned from that template). To find out where the table came from, you'd have to know which template your current database was cloned from and I don't think we store that info anywhere. -- Korry -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] [PATCHES] PL instrumentation plugin and Rendezvous variable
Applied with a few small changes --- I renamed the GUC variables after a
suggestion by Simon Riggs, and fixed things so that
backend_load_libraries could actually do something useful (you had it as
PGC_POSTMASTER, making it effectively no more flexible than the existing
preload_libraries list).
'Doh! That was a cut/paste error on my part. Thanks.
The only change that will directly impact your code is that I thought
it'd be better to provide plpgsql_exec_error_callback and
exec_assign_expr as separate fields instead of arguments to func_setup,
viz
if (*plugin_ptr)
{
(*plugin_ptr)-error_callback = plpgsql_exec_error_callback;
(*plugin_ptr)-assign_expr = exec_assign_expr;
if ((*plugin_ptr)-func_setup)
((*plugin_ptr)-func_setup)(estate, func);
}
I'm not totally wedded to this if you don't like it, but my thought was
that passing these as arguments to func_setup would mean a lot of pain
anytime we wanted to change the set of function pointers provided:
every plugin would need textual changes whether it actually used these
functions or not.
Good idea, those two function pointers are sort of necessary-evil required only by the debugger plugin (other plugins presumably won't need them).
I have not implemented any support for unloading shared libraries. Once
we've finalized the design for rendezvous variables, I'll submit a
separate documentation patch.
I added docs for the GUC variables but didn't do more than that. I
think the code comments are probably sufficient as far as rendezvous
variables and PLpgSQL_plugin go ... did you have something else in mind?
I'll look around the existing documentation to see if I can find an appropriate place, but we don't really have an implementor's guide.
Thanks for your help and suggestions.
-- Korry
--
Korry Douglas [EMAIL PROTECTED]
EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Plugins redux (was Re: [PATCHES] PL instrumentation plugin
As for forcing the library load to occur, I propose a new GUC variable backend_load_libraries that is much like the postmaster's preload_libraries, except that the requested library loads happen at backend start time instead of in the postmaster. Then we need write and document the code only once, and there are other possible uses for it besides PL plugins. Any thoughts about where to put the call to process_backend_libraries() (the new function to handle backend_load_libraries)? I'm thinking that it should go in PostgresMain(), just after (before?) the call to BeginReportingGUCOptions() - by that time, we know whether we are a superuser and we have processed all GUC options. Also, should we create an on_proc_exit() handler that would unload all dynamic libraries (specifically to call the _PG_fini() functions)? -- Korry -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Plugins redux (was Re: [PATCHES] PL instrumentation
Also, should we create an on_proc_exit() handler that would unload all dynamic libraries (specifically to call the _PG_fini() functions)? Yeah, I thought about that too, but I'm inclined not to do it; it seems like just excess cycles. The process is quitting anyway, so the only reason this would be useful is if the library thinks it's going to update external or shared state during _PG_fini ... and on the whole that sounds like a bad idea. Besides, if a library really needs this it can add its own on_proc_exit handler. It seems a little dangerous for a dynamic library to register an on_proc_exit() handler. If we ever add support for unloading a dynamic library, we'll have to add an unregister_on_proc_exit() too. Otherwise, a dynamic library might register a function pointer with on_proc_exit() and then leave a dangling pointer when it gets unloaded. Given that, I assume you don't feel the need to unload old shared libraries if the user (a superuser) removes an entry from backend_load_libraries, right? In fact, it looks _PG_fini() is only called if you *reload* a library, unless I'm missing something somwhere. -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] 8.2 features status
There's a LOT of unnecessary overhead in that process: having a simple web app that lists who claimed what todo and when, any status updates if they've voluntarily provided them, and links to archive discussions, we could reduce the above to a 3-step process making it vastly easier for new hackers to get started. A developers' wiki with links into the list archives would be great. My thoughts exactly... -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] proposal for PL packages for 8.3.
Well, it would be nice to have some clarification about the expected scope and lifetimes of these variables. If two different sessions change the values, what's supposed to happen? Right, I am confused whether these are session or schema-local variables. What does Oracle support? Looking at this: http://thinkoracle.blogspot.com/2005/10/oracle-packages.html it seems varaiable are per-session. Package variables are per-session. Don't forget package initializers too... -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Plugins redux (was Re: [PATCHES] PL instrumentation plugin
, so that plugins could be switched intra-session without superuser privileges.) How about a combination of plan A and plan B? Make backend_load_libraries a USERSET variable, but you can't *add* libraries outside of $libdir/plugins/ unless you are a superuser. BTW, is anyone up for renaming the existing preload_libraries variable to postmaster_load_libraries? This would be more symmetrical with backend_load_libraries, and so perhaps clearer about which does what Makes sense to me, of course that breaks existing postgresql.conf files. Do you want me to do any of this coding? -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Plugins redux (was Re: [PATCHES] PL instrumentation
I'm not sure you'll be able to convince everyone that the penalty is so
negligible --- any high-rate access to shared memory is potentially very
expensive, see nearby threads for examples. Even if this is affordable
for the debugger, what of more-invasive plugins such as the performance
monitor? I think a credible general-purpose plugin design has to
address the problem of enabling plugins on-the-fly.
I have no problem with your solution - I think its a very nice design.
I don't think it could crash because there's no way to unload a plugin
(there is no UNLOAD statement, is there?).
What we actually have at the moment is that you can LOAD a library
again, which causes an unload of the prior version and then loading the
new. I suppose this feature was intended to speed library development by
letting you recompile and then update into your existing backend session.
Not sure how many people are using it, but it's there ...
Right, but you still end up with a plugin loaded afterwards so no crash (of course you could do something stupid like load a new plugin with the same name that isn't really a plugin).
Which reminds me, you haven't proposed a way to unload a shared-library.
This depends on the semantics we want to assign to
backend_shared_libraries --- I could imagine defining it as if you
remove an entry from the value then we'll unload that library.
That's what I was thinking too.
How about a combination of plan A and plan B? Make
backend_load_libraries a USERSET variable, but you can't *add* libraries
outside of $libdir/plugins/ unless you are a superuser.
Not sure how easy that is (ie, can we track which part of the list came
from where), but if doable it'd be OK with me. We might have to split
it into two list variables to make it work, and I'm not sure it's worth
the complication.
The GUC assign hook would parse through backend_load_libraries...
for each library,
{
if ( the library is already loaded )
{
// it's not a new library, doesn't matter where it lives, doesn't matter if we're a superuser
load( library)
}
else
{
// it's a new entry in backed_load_libraries
if( library lives in $libdir/plugins )
load( library )
else
{
if( is_superuser())
load( library )
else
throw an error
}
}
}
We already broke them by removing the init-function name...
Right...
Do you want me to do any of this coding?
Up to you --- I can do it if you don't want to.
I'll take a stab at it... thanks for your help so far.
-- Korry
--
Korry Douglas [EMAIL PROTECTED]
EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] [PATCHES] PL instrumentation plugin support (i.e. PL/pgSQL
In view of the other patch submitted to support init/fini functions for shared libraries, I'm inclined to change this one to depend on that; in particular it seems like we could eliminate the necessity for users to specify the correct setup-function names. Thoughts? I think that would be great. Can you point me to the patch you're referring to? I can convert my patch if you prefer. -- Korry -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] pg_terminate_backend
I am not sure how you prove the non-existance of a bug. Ideas? I do that by deleting all of my code (usually by accident :-) No code, no bugs! -- Korry
Re: [HACKERS] Possible explanation for Win32 stats regression test
Is anyone working on this?
Tom Lane wrote:
korry [EMAIL PROTECTED] writes:
The problem is that, each time you go through
pgwin32_waitforsinglesocket(), you tie the *same* kernel object
(waitevent is static) to each socket.
The fix is pretty simple - just call WSAEventSelect( s, waitevent, 0 )
after WaitForMultipleObjectsEx() returns. That disassociates the socket
from the Event (it will get re-associated the next time
pgwin32_waitforsingleselect() is called.
Hmm. Presumably we don't do this a whole lot (use multiple sockets) or
we'd have noticed before. Perhaps better would be to keep an additional
static variable saying which socket the event is currently associated
to, and only issue the extra WSAEventSelect calls if we need to change
it. Or is WSAEventSelect fast enough that it doesn't matter?
Here's a simple patch that fixes the problem (I haven't explored the performance of this patch compared to Tom's suggestion).
-- Korry
Index: src/backend/port/win32/socket.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/port/win32/socket.c,v
retrieving revision 1.11
diff -w -c -r1.11 socket.c
*** src/backend/port/win32/socket.c 5 Mar 2006 15:58:35 - 1.11
--- src/backend/port/win32/socket.c 29 Jul 2006 12:13:19 -
***
*** 132,137
--- 132,154
events[1] = waitevent;
r = WaitForMultipleObjectsEx(2, events, FALSE, INFINITE, TRUE);
+
+ /*
+ * NOTE: we must disassociate this socket from waitevent - if we don't, then
+ * we may accidentally fire waitevent at some point in the future if,
+ * for example, the socket is closed. That normally would not be a
+ * problem, but if you ever have two (or more) sockets in a single
+ * backend, they *ALL* share the same waitevent. So, if you pass through
+ * this function for socket1 and socket2, a close on EITHER socket will
+ * trigger an FD_CLOSE event, regardless of whether you're waiting for
+ * socket1 or socket2. That means that if you are waiting for socket1
+ * and socket2 gets some interesting traffic (an FD_CLOSE or FD_READ
+ * event for example), the above call to WaitForMultipleObjectsEx()
+ * will return even though nothing actually happened to socket1. Nasty...
+ */
+
+ WSAEventSelect(s, waitevent, 0 );
+
if (r == WAIT_OBJECT_0 || r == WAIT_IO_COMPLETION)
{
pgwin32_dispatch_queued_signals();
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Possible explanation for Win32 stats
heh. I was just doing it the way Tom suggested - see attached. With a little more trouble we could also keep track if the listened for events and sometimes save ourselves a second call to WSAEventSelect, but I'm not sure it's worth it. It all depends on the overhead of WSAEventSelect(). I'm sure your version would run faster, but I just don't know if slower would be measurable. BTW: I would suggest changing your comment to: /* * make sure we don't multiplex this kernel event object with a different socket * from a previous call */ Thanks for tackling this problem too. -- Korry Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] how can i get the binary format of timestamp?
Hello,Tom！
Thanks for your answer.
To invoid missing something of the timestamp, i just copy the tm2timestamp
func into my program.(Of course, some other related functions are copied, too),
result is still refusing me.
What i need is: 41A7 7DBA D400 stands for 2006-03-30 18:18:18 ( 41A7
7DBA D400 is got from the Copy to File)
When using tm2timestamp, result is:
if define HAVE_INT64_TIMESTAMP
result is: 4E66 E642 0030 C274
if not define HAVE_INT64_TIMESTAMP
{
if define INT64_IS_BUSTED result is: 6972 2142 00DB
if not define INT64_IS_BUSTED result is: 4221 7269 DB00
}
So, maybe I forget to define something??
/**
* my codes
*
*/
my program is listed:
time_t time = 1143713898;
struct tm* tmField = gmtime(time);// I know that struct tm dislikes the
struct tm in time.h
tmField-tm_year += 1900;
tmField-tm_mon += 1; // So tmFiled is same as pg_tm
int tzp = 8;
char buffer[256];
timestamp timestampField ;
tm2timestamp(tmField,0,tzp,timestampField);
timestamp_send(timestampField,buffer); // I changed some codes here, to get
the binary format of timestamp
/*/
so ,what switch should i turn on?
=== 2006-04-18 21:47:42 You Wrote：===
[EMAIL PROTECTED] [EMAIL PROTECTED] writes:
timestamp is defined as int64 or float8, when it is defined as int64, it
looks like timeval, first 32 bits for seconds,second 32 bits for usec.
No, it's seconds times 100, not times 2^32.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
.
= = = = = = = = = = = = = = = = = = = =
---(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] how can i get the binary format of timestamp?
Hello:
I find that the real timestamp format(got from the file which is produced by
copying binary to ) is different from what i find in timestamp_send func.
i do think that the binary format of a timestamp 2006-04-18 11:20:20 should
be 44 2B B0 6A 00 00 00 00 standing for secs:1143713898, usecs:0 but in
fact it is:41A7 7DBA D400
timestamp is defined as int64 or float8, when it is defined as int64, it looks
like timeval, first 32 bits for seconds,second 32 bits for usec.
and from the timestamp_send func, it just changes the first 32 bits to binary
then combines the next 32 bits
/* codes from timestamp_send
*/
Datum
timestamp_send(PG_FUNCTION_ARGS)
{
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
StringInfoData buf;
pq_begintypsend(buf);
#ifdef HAVE_INT64_TIMESTAMP
pq_sendint64(buf, timestamp);
#else
pq_sendfloat8(buf, timestamp);
#endif
PG_RETURN_BYTEA_P(pq_endtypsend(buf));
}
/* codes from pq_sendint64
*/
void
pq_sendint64(StringInfo buf, int64 i)
{
uint32 n32;
/* High order half first, since we're doing MSB-first */
#ifdef INT64_IS_BUSTED
/* don't try a right shift of 32 on a 32-bit word */
n32 = (i 0) ? -1 : 0;
#else
n32 = (uint32) (i 32);
#endif
n32 = htonl(n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
/* Now the low order half */
n32 = (uint32) i;
n32 = htonl(n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
}
so i do think that the binary format of a timestamp 2006-04-18 11:20:20
should be 44 2B B0 6A 00 00 00 00 standing for secs:1143713898, usecs:0
but in fact it is: 41A7 7DBA D400 , i don't know why.
maybe, i have looked into a wrong func, then which is the right one?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] postgres questions (semi-joins, algebraic space)
Good Morning, I'm an italian student and I'm working on a project involving postgres. I'm sorry for my very bad english. I've some questions: 1)Does postgres upport semi-joins? 2)What about projections or selections in query tree writing? Does Postgres support algebraic space (in other word when/how Postgres process algebraic operators? Are they used as common to reduce the size of the space that the search strategy has to explore?). Thanks you all Lucky OffroCerco: CASA, LAVORO, VACANZE, ELETTRONICA, INCONTRI. Tutti gli annunci sono online su Kataweb all'indirizzo http://www.offroecerco.it OffroCerco: CASA, LAVORO, VACANZE, ELETTRONICA, INCONTRI. Tutti gli annunci sono online su Kataweb all'indirizzo http://www.offroecerco.it ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS]
Good Morning, I'm an italian student and I'm working on a project involving postgres. I'm sorry for my very bad english. I've some questions: 1)Does postgres upport semi-joins? 2)What about projections or selections in query tree writing? Does Postgres support algebraic space (in other word when/how Postgres process algebraic operators? Are they used as common to reduce the size of the space that the search strategy has to explore?). Thanks you all Lucky OffroCerco: CASA, LAVORO, VACANZE, ELETTRONICA, INCONTRI. Tutti gli annunci sono online su Kataweb all'indirizzo http://www.offroecerco.it ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS]
unsubscribe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Opinions on Usenet ...
[EMAIL PROTECTED] (Gevik Babakhani) writes: Maybe it is me but, I am trying to send a posting regarding a solution proposal for a TODO item. My posting has a .tgz attachment but it seems that it never arives at hackers list! This is very frustrating. I even ask Bruce for help. how big is the message? *raised eyebrow* depending on size, it might get caught up in the queue to be approved ... the only other possibility is that the anti-virus or anti-spam checkers are picking it up ... what i'd recommend is putting it up on a URL and posting the URL so that ppl can download it ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: Monday, November 29, 2004 9:24 PM To: Gevik Babakhani Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Opinions on Usenet ... On Mon, 29 Nov 2004, Gevik Babakhani wrote: I was wondering if there is a better solution than mailing lists. My experience is that mailing lists are somewat combersome to use. Especially when your postings do not arrive! When they don't arrive? *raised eyebrow* You having a problem? :( I don't know about everyone else, but my personal preference for mailing lists is due to the lack of spam that gets to them, something that you can't really do easily on Usenet ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] NOT LOGGED options (was Point in Time Recovery )
Tom Lane wrote [EMAIL PROTECTED] [EMAIL PROTECTED] writes: It would be my intention (in 8.1) to make those available via switches e.g. NOT LOGGED options on CREATE INDEX and COPY, to allow users to take advantage of the no logging optimization without turning off PITR system wide. (Just as this is possible in Oracle and Teradata). Isn't this in direct conflict with your opinion above? And I cannot say that I think this one is a good idea. We do not have support for selective catalog xlogging; if you do something like this then you *will* have a broken database after recovery, because it will contain those indexes but with invalid contents. No, its not in direct conflict. Turning OFF archive_mode would have a system wide effect. The options described allow individual applications to make a choice about whether certain very large operations are recoverable, or not. I don't ever personally want to turn off system wide PITR, but there will be times when I choose to avoid overhead on individual ops when the situation dictates. This goes with your oft-mentioned dislike of systems that think they know better than you do... The first two optimizations have been included in 8.0 when archive_mode is off. If there is a problem, then it will effect crash recovery of those systems also. I suggest using exactly this optimisation, though under user (application) control, rather than sysadmin control. The challenges you mention have a solution. I wanted to add these to TODO, not yet to discuss detailed implementation. I would also aim to make the first Insert Select into an empty table not logged (optionally). This is an important optimization for Oracle, teradata and DB2 (which uses NOT LOGGED INITIALLY). This is even worse: not only do you have a broken database, but you have no way to recover. (At least with an unlogged index you could fix it by REINDEX.) If you don't care about longevity of the table, then make it a temp table. It is frequently possible to use that route, though the option remains in frequent use in other situations. The fact that Oracle does it does not automatically make it a good idea. Amen to that. You will note that unless compatability has been a requirement, there have been times I have not followed the Oracle path, e.g. PITR design. I admit it must seem strange that I tried so hard to put PITR in place, only to suggest removing it, optionally... Overall, the options I describe here have been in production use in major enterprise Data Warehouse systems for almost 15 years now. Oracle and DB2 copied the original Teradata implementation; slowly because, they too, didn't quickly or easily accept the wisdom. There is abosultely no doubt of the true value of these optimisations - the TPC-H tests for all vendors make use of those (hidden in the details of which load utility options are used, or simply the default behaviour). Logging only has value when the mean time to recover is low enough to make recovery worthwhile. This can catch you in a bind because you have to decide whether to reduce MTTR at the expense of 100% data recovery. For some big systems, recovery is only an option if you exclude the biggest table(s). In a Data Warehouse, where data is loaded in large volumes, it may only be feasible to load it when you have this optimisation. In a recovery situation, re-loading the largest fact tables from their original source data files is more likely to be the best option, or in some cases, skipped entirely in favour of loading new data. I don't claim that everybody would want this, only that it is an extremely beneficial optimisation for many very large databases - which is much of my focus. You've pointed out that I'm new round here, which is certainly true - but I have been many places... There are and will be many differences in thinking that emerge from this; I regard all of this as synergy, not argument. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] will PITR in 8.0 be usable for hot spare/log shipping type of replication
Tom Lane Eric Kerin [EMAIL PROTECTED] writes: The issues I've seen are: 1. Knowing when the master has finished the file transfer transfer to the backup. The standard solution to this is you write to a temporary file name (generated off your process PID, or some other convenient reasonably- unique random name) and rename() into place only after you've finished the transfer. If you are paranoid you can try to fsync the file before renaming, too. File rename is a reasonably atomic process on all modern OSes. 2. Handling the meta-files, (.history, .backup) (eg: not sleeping if they don't exist) Yeah, this is an area that needs more thought. At the moment I believe both of these will only be asked for during the initial microseconds of slave-postmaster start. If they are not there I don't think you need to wait for them. It's only plain ol' WAL segments that you want to wait for. (Anyone see a hole in that analysis?) Agreed. 3. Keeping the backup from coming online before the replay has fully finished in the event of a failure to copy a file, or other strange errors (out of memory, etc). Right, also an area that needs thought. Some other people opined that they want the switchover to occur only on manual command. I'd go with that too if you have anything close to 24x7 availability of admins. If you *must* have automatic switchover, what's the safest criterion? Dunno, but let's think ... That's fairly straightforward. You use a recovery_command that sleeps when it discovers a full log file isn't available - i.e. it has requested the last or master-current WAL file. The program wakes when the decision/operator command to switchover is taken. That way, when switchover occurs, you're straight up. No code changes... This is important because it will allow us to test recovery for many systems by creating a continuously rolling copy. Implementing this will be the best way to stress-test the recovery code. I'm not hugely in favour of copying partially filled log files, but if that's what people want...as long as we don't change the basic code to implement it, because then we'll have just created another code path that will leave PITR untested for most people. [I discussed all of this before as Automatic Standby Database functionality] Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization
On Thu, Aug 12, 2004 at 01:13:46PM -0400, Tom Lane wrote: Kenneth Marshall [EMAIL PROTECTED] writes: On Thu, Aug 12, 2004 at 09:58:56AM -0400, Tom Lane wrote: How would a read-only action work to block out the checkpoint? The latch+version number is use by the checkpoint process. The other processes can do a read of the latch to determine if it has been set. This does not cause a cache invalidation hit. If the latch is set, the competing processes read until it has been cleared and the version updated. This makes the general case of no checkpoint not incur a write and the consequent cache-line invalidation and reload by all processors on an SMP system. Except that reading the latch and finding it clear offers no guarantee that a checkpoint isn't about to start. The problem is that we are performing two separate actions (write a COMMIT xlog record and update transaction status in clog) and we have to prevent a checkpoint from starting in between those actions. I don't see that there's any way to do that with a read-only latch. ...just caught up on this. ISTM that more heavily loading the checkpoint process IS possible if the checkpoint uses a two-phase lock. That would replace 1 write lock with 2 lock reads...which is likely to be beneficial for SMP, given I have faith that the other two problems you mention will succumb to some solution in the mid-term. The first lock is an intent lock followed by a second, heavyweight lock just as you now have it. Comitter: 1. prior to COMMIT: reads for an intent lock, if found then it attempts to take heavyweight lock...if that is not possible, then the commit waits until after the checkpoint, just as you currently suggest 2. prior to update clog: reads for an intent lock, if found then takes heavyweight lock...if that is not possible, then report a server error Checkpointer: (straight to step 4 for a shutdown checkpoint) 1. writes an intent lock (it always can) 2. wait for the group commit timeout 3. wait for 0.5 second more 4. begins to wait on an exclusive heavyweight lock, before starting checkpoint proper This is not a provably correct state machine, but the error message should not occur under current normal situations. (It is possible that an intent lock could be written by Checkpointer (step 1), after a Committer reads for it (step 1), then a very long delay occurs before Committer's step 2), such that Checkpointer step 4 begins before Committer step 2.) It is very likely that this would be noticed by Comitter step 2 and reported upon, in the unlikely event that it occurs. Is a longer term solution for pg to use a background log writer? That would make group commit much easier to perform automatically without the false-delay model currently available. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Development Schedule Page
Please could we have a development schedule page on the web site...? The information it should contain would be like this (and others) Current Development Release: (Coordinator: Bruce Momjian) 8.0:Beta1, released 2 August 2004 Beta2, deadline 2 September 2004 Doc Freeze, deadline 12 September 2004 Release, planned 2 October 2004 Stable Releases: (Coordinator: Bruce Momjian) 7.4 Stable: 7.4.3, released XYZ Security advisory(s) exist for 7.4.1 7.3 Stable: 7.3.6, released XYZ Security advisory(s) exist for 7.3.5 and below Previous Releases: (No Coordinator) 7.2 Stable: 7.2.4, released XYZ Advice: Upgrade now to 7.4 stable 7.1 Stable: 7.1.3, released XYZ Advice: Upgrade now to 7.4 stable Next Release: (Coordinator: Bruce Momjian) 8.1 Beta Freeze, deadline 1 June 2005 Once we have the page, we can start to discuss the details on it. I remain mostly in the dark about how many beta phases there will be what their deadlines are etc. If we know this, its easy to write down, surely? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Development Schedule Page
Tom Lane [EMAIL PROTECTED] [EMAIL PROTECTED] writes: Please could we have a development schedule page on the web site...? You haven't been around here long, have you? Gee, Tom, you noticed? What gave it away? :) (I wasn't in a legal position to contribute before late 2003 - binding IP clauses and all that). There is no schedule. Hmm. I did think that at first. But I don't go with the mystical stuff. (There IS a spoon, Matrix-fans, IMHO.) Now, I think the answer is close to Tom and/or Bruce know, but maybe they don't agree yet My observed group behaviour for what days is eventually agreed is something like: (Tom+Bruce+Marc)/2 in julian days I see this: there is no PUBLISHED schedule, there probably is no AGREED schedule, but everybody's working to some reasonable working assumptions which mean that there is in fact an IMPLICIT schedule. (Whether or not that changes over time) I argued as strongly as anyone to NOT follow the agreed plan (just recently). But I'm in favour of a published plan, so we all know. Just like my recipe book says 90 mins for a cake, but we keep cooking it till it looks good and don't try to flame Mrs.Beeton because it took 95 mins. My interest is in allowing others to contribute. I have many other ideas in this vein. We can put things to the nearest month, if thats all we know/decide we can hold ourselves to Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: We have got a serious problem with pg_clog/WAL synchronization
Tom Lane [EMAIL PROTECTED] [EMAIL PROTECTED] writes: This is not a provably correct state machine I think the discussion ends right there. Yes... Negative results are worth documenting too, IMHO. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PITR on Windows?
I notice that PITR doesn't function correctly on Windows. Has that been reported elsewhere? The archive_command parameter %p resolves to a full path containing slashes rather than backslashes. This is not a Windows file, so any attempt to copy it fails. There isn't any way to avoid that. I'm surely not the first to report that? Am I? [There isn't any way of telling, by default, since the log goes nowhere] Is there a PostgreSQL approved way of saying for Windows port, use backslashes in pathnames? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] pgscripts improvement
Fixed up alignment problems and improved some translations. Regards, Fabrizio Mazzoni # SOME DESCRIPTIVE TITLE. # FIRST AUTHOR [EMAIL PROTECTED], YEAR. # msgid msgstr Project-Id-Version: PostgreSQL v7.4\n POT-Creation-Date: 2003-10-05 13:26-0300\n PO-Revision-Date: 2003-10-09 23:58+0100\n Last-Translator: Fabrizio Mazzoni [EMAIL PROTECTED]\n Language-Team: Fabrizio Mazzoni [EMAIL PROTECTED]\n MIME-Version: 1.0\n Content-Type: text/plain; charset=iso-8859-1\n Content-Transfer-Encoding: 8bit\n #: clusterdb.c:96 #: clusterdb.c:111 #: createdb.c:101 #: createdb.c:120 #: createlang.c:94 #: createlang.c:115 #: createlang.c:157 #: createuser.c:116 #: createuser.c:131 #: dropdb.c:83 #: dropdb.c:92 #: dropdb.c:100 #: droplang.c:88 #: droplang.c:109 #: droplang.c:151 #: dropuser.c:83 #: dropuser.c:98 #: vacuumdb.c:112 #: vacuumdb.c:127 #, c-format msgid Try \%s --help\ for more information.\n msgstr Prova \%s --help\ per avere più informazioni.\n #: clusterdb.c:109 #: createdb.c:118 #: createlang.c:113 #: createuser.c:129 #: dropdb.c:98 #: droplang.c:107 #: dropuser.c:96 #: vacuumdb.c:125 #, c-format msgid %s: too many command-line arguments (first is \%s\)\n msgstr %s: troppi parametri dalla riga di comando (il primo è \%s\)\n #: createdb.c:128 #, c-format msgid %s: \%s\ is not a valid encoding name\n msgstr %s: \%s\ non è un nome di codifica valido\n #: createdb.c:170 #, c-format msgid %s: database creation failed: %s msgstr %s: creazione del database fallita: %s #: createdb.c:195 #, c-format msgid %s: comment creation failed (database was created): %s msgstr %s: creazione del commento fallita (il database è stato creato): %s #: createdb.c:213 #, c-format msgid %s creates a PostgreSQL database.\n \n msgstr %s crea un database PostgreSQL.\n \n #: clusterdb.c:232 #: createdb.c:214 #: createlang.c:272 #: createuser.c:236 #: dropdb.c:143 #: droplang.c:242 #: dropuser.c:143 #: vacuumdb.c:256 msgid Usage:\n msgstr Utilizzo:\n #: createdb.c:215 #, c-format msgid %s [OPTION]... [DBNAME] [DESCRIPTION]\n msgstr %s [OPZIONE]... [NOME_DB] [DESCRIZIONE]\n #: clusterdb.c:234 #: createdb.c:216 #: createlang.c:274 #: createuser.c:238 #: dropdb.c:145 #: droplang.c:244 #: dropuser.c:145 #: vacuumdb.c:258 msgid \n Options:\n msgstr \n Opzioni:\n #: createdb.c:217 msgid -D, --location=PATH alternative place to store the database\n msgstr -D, --location=PERCORSO percorso alternativo in cui salvare il database\n #: createdb.c:218 msgid -E, --encoding=ENCODING encoding for the database\n msgstr -E, --encoding=CODIFICA codifica del database\n #: createdb.c:219 msgid -O, --owner=OWNER database user to own the new database\n msgstr -O, --owner=UTENTEutente proprietario del database\n #: createdb.c:220 msgid -T, --template=TEMPLATE template database to copy\n msgstr -T, --template=MATRICEcopia il database MATRICE\n #: clusterdb.c:238 #: createdb.c:221 #: createlang.c:276 #: createuser.c:247 #: dropdb.c:146 #: droplang.c:246 #: dropuser.c:146 msgid -e, --echoshow the commands being sent to the server\n msgstr -e, --echomostra i comandi inviati al server\n #: clusterdb.c:239 #: createdb.c:222 #: createuser.c:248 #: dropdb.c:148 #: dropuser.c:148 msgid -q, --quiet don't write any messages\n msgstr -q, --quiet non stampare messaggi\n #: clusterdb.c:240 #: createdb.c:223 #: createlang.c:283 #: createuser.c:249 #: dropdb.c:153 #: droplang.c:252 #: dropuser.c:153 msgid --helpshow this help, then exit\n msgstr --helpmostra questo aiuto e poi esci\n #: clusterdb.c:241 #: createdb.c:224 #: createlang.c:284 #: createuser.c:250 #: dropdb.c:154 #: droplang.c:253 #: dropuser.c:154 msgid --version output version information, then exit\n msgstr --version mostra informazioni sulla versione, poi esci\n #: clusterdb.c:242 #: createdb.c:225 #: createuser.c:251 #: vacuumdb.c:269 msgid \n Connection options:\n msgstr \n Opzioni di connessione:\n #: clusterdb.c:243 #: createdb.c:226 #: createlang.c:279 #: createuser.c:252 #: dropdb.c:149 #: droplang.c:248 #: dropuser.c:149 #: vacuumdb.c:270 msgid -h, --host=HOSTNAME database server host or socket directory\n msgstr -h, --host=NOME_HOST indirizzo database o directory socket\n #: clusterdb.c:244 #: createdb.c:227 #: createlang.c:280 #: createuser.c:253 #: dropdb.c:150 #: droplang.c:249 #: dropuser.c:150 #: vacuumdb.c:271 msgid -p, --port=PORT database server port\n msgstr -p, --port=PORTA porta del server database\n #: clusterdb.c:245 #: createdb.c:228 #: createlang.c:281 #: dropdb.c:151 #: droplang.c:250 #: vacuumdb.c:272 msgid -U, --username=USERNAME user name to connect as\n msgstr -U, --username=UTENTE nome utente da utilizzare per la connessione\n #: clusterdb.c:246 #: createdb.c:229 #: createlang.c:282 #: dropdb.c:152
[HACKERS] pg_resetxlog fixed
Changed a couple of lines and traslated them in a better way. Regards, Fabrizio Mazzoni # SOME DESCRIPTIVE TITLE. # FIRST AUTHOR [EMAIL PROTECTED], YEAR. # msgid msgstr Project-Id-Version: PostgreSQL v7.4\n POT-Creation-Date: 2003-10-05 13:25-0300\n PO-Revision-Date: 2003-10-09 23:35+0100\n Last-Translator: Fabrizio Mazzoni [EMAIL PROTECTED]\n Language-Team: Fabrizio Mazzoni [EMAIL PROTECTED]\n MIME-Version: 1.0\n Content-Type: text/plain; charset=iso-8859-1\n Content-Transfer-Encoding: 8bit\n #: pg_resetxlog.c:142 #, c-format msgid %s: invalid argument for option -x\n msgstr %s: parametro errato per l'opzione -x\n #: pg_resetxlog.c:143 #: pg_resetxlog.c:158 #: pg_resetxlog.c:173 #: pg_resetxlog.c:180 #: pg_resetxlog.c:186 #: pg_resetxlog.c:194 #, c-format msgid Try \%s --help\ for more information.\n msgstr Prova \%s --help\ per maggiori informazioni.\n #: pg_resetxlog.c:148 #, c-format msgid %s: transaction ID (-x) must not be 0\n msgstr %s: l'ID della transazione (-x) non deve essere 0\n #: pg_resetxlog.c:157 #, c-format msgid %s: invalid argument for option -o\n msgstr %s: parametro errato per l'opzione -o\n #: pg_resetxlog.c:163 #, c-format msgid %s: OID (-o) must not be 0\n msgstr %s: l'OID (-o) non deve essere 0\n #: pg_resetxlog.c:172 #: pg_resetxlog.c:179 #, c-format msgid %s: invalid argument for option -l\n msgstr %s: parametro errato per l'opzione -l\n #: pg_resetxlog.c:193 #, c-format msgid %s: no data directory specified\n msgstr %s: non è stata specificata una directory per i dati\n #: pg_resetxlog.c:213 #: pg_resetxlog.c:309 #, c-format msgid %s: could not open file \%s\ for reading: %s\n msgstr %s: impossibile aprire il file \%s\ per la lettura: %s\n #: pg_resetxlog.c:219 #, c-format msgid %s: lock file \%s\ exists\n Is a server running? If not, delete the lock file and try again.\n msgstr %s: il lock file \%s\ esiste\n Il server è in esecuzione? Se non lo è, eliminate il lock file e riprovate.\n #: pg_resetxlog.c:258 msgid \n If these values seem acceptable, use -f to force reset.\n msgstr \n Se questi parametri sembrano accettabili, utilizzate -f per forzare un reset.\n #: pg_resetxlog.c:270 msgid The database server was not shut down cleanly.\n Resetting the transaction log may cause data to be lost.\n If you want to proceed anyway, use -f to force reset.\n msgstr Il server database non è stato chiuso correttamente.\n Resettare il registro delle trasazioni può causare una perdita di dati.\n Se volete continuare, utilizzate -f per forzare il reset.\n #: pg_resetxlog.c:283 msgid Transaction log reset\n msgstr registro delle transazioni riavviato\n #: pg_resetxlog.c:312 #, c-format msgid If you are sure the data directory path is correct, execute\n touch %s\n and try again.\n msgstr Se sei sicuro che il percorso della cartella è corretto, esegui\n touch %s\n e riprova.\n #: pg_resetxlog.c:325 #, c-format msgid %s: could not read file \%s\: %s\n msgstr %s: impossibile leggere il file \%s\: %s\n #: pg_resetxlog.c:348 #, c-format msgid %s: pg_control exists but has invalid CRC; proceed with caution\n msgstr %s: pg_control esiste ma ha un CRC invalido; procedere con cautela\n #: pg_resetxlog.c:357 #, c-format msgid %s: pg_control exists but is broken or unknown version; ignoring it\n msgstr %s: pg_control esiste ma è inutilizzabile o è una versione sconosciuta; verrà ignorato\n #: pg_resetxlog.c:408 #, c-format msgid %s: invalid LC_COLLATE setting\n msgstr %s: settaggio errato per LC_COLLATE\n #: pg_resetxlog.c:415 #, c-format msgid %s: invalid LC_CTYPE setting\n msgstr %s: settaggio errato per LC_CTYPE\n #: pg_resetxlog.c:437 msgid Guessed pg_control values:\n \n msgstr Valori pg_control indovinati:\n \n #: pg_resetxlog.c:439 msgid pg_control values:\n \n msgstr Valori pg_control:\n \n #: pg_resetxlog.c:441 #, c-format msgid pg_control version number:%u\n msgstr Numero versione pg_control:%u\n #: pg_resetxlog.c:442 #, c-format msgid Catalog version number: %u\n msgstr Numero versione catalogo: %u\n #: pg_resetxlog.c:443 #, c-format msgid Current log file ID: %u\n msgstr ID file di registro corrente: %u\n #: pg_resetxlog.c:444 #, c-format msgid Next log file segment:%u\n msgstr Prossimo segmento del file di registro:%u\n #: pg_resetxlog.c:445 #, c-format msgid Latest checkpoint's StartUpID:%u\n msgstr Ultimo checkpoint StartUpID:%u\n #: pg_resetxlog.c:446 #, c-format msgid Latest checkpoint's NextXID: %u\n msgstr Ultimo checkpoint NextXID: %u\n #: pg_resetxlog.c:447 #, c-format msgid Latest checkpoint's NextOID: %u\n msgstr Ultimo checkpoint NextOID: %u\n #: pg_resetxlog.c:448 #, c-format msgid Database block size: %u\n msgstr Dimensione blocco database: %u\n #: pg_resetxlog.c:449 #, c-format msgid Blocks per segment of large relation: %u\n
[HACKERS] Statement-level Triggers
Hello everybody, how far have you got with statement-level triggers development? Regards, Claudio. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Fwd: Re: [HACKERS] [GENERAL] Extracting time from timestamp
On Friday 21 Mar 2003 11:38 am, Christopher Kings-Lynne wrote: phd=# select time(abstime(timestamp 'now')) from bookings; ERROR: parser: parse error at or near abstime at character 13 phd=# select time(timestamp 'now') from bookings; ERROR: parser: parse error at or near timestamp at character 13 phd=# select version(); version Try: select time(abstime(timestamp 'now')) from bookings; select time(timestamp 'now') from bookings; First of all, thanks, it worked.. And What's so holy about if it is a function? That was bummer, I admit.. Spent almost a day on it.. Shridhar P.S. Sorry, I didn't realized that I handn' CC hackers.. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Fwd: Re: [HACKERS] [GENERAL] Extracting time from timestamp
On Friday 21 Mar 2003 12:25 pm, Tom Lane wrote: Shridhar Daithankar[EMAIL PROTECTED] [EMAIL PROTECTED] writes: And What's so holy about if it is a function? The problem is that TIME(n) is a datatype name, not a function call, according to the SQL spec. Likewise for TIMESTAMP(n), INTERVAL(n), NUMERIC(m,n), and maybe one or two other special cases I've forgotten. The SQL spec's love of special-purpose syntaxes is one of its worst features IMHO ... In this case, I would vote for overload as SQL extension in postgresql if people feel it is feasible and/or sensible.. Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Another naive question, inheritance and foreign key
Hi,
Just stumbled upon this. Is it correct to conclude that foreign keys are not
inherited from this text?
phd=# create table perbookings(type smallint) inherits (bookings);
CREATE TABLE
phd=# \d perbookings;
Table public.perbookings
Column |Type |Modifiers
+-+--
rid| integer |
uid| integer |
stime | timestamp without time zone |
etime | timestamp without time zone |
bid| integer | default nextval('bid_seq'::text)
type | smallint|
Check constraints: bookings_etime (etime stime)
phd=# \d bookings;
Table public.bookings
Column |Type |Modifiers
+-+--
rid| integer |
uid| integer |
stime | timestamp without time zone |
etime | timestamp without time zone |
bid| integer | default nextval('bid_seq'::text)
Check constraints: bookings_etime (etime stime)
Foreign Key constraints: $1 FOREIGN KEY (rid) REFERENCES resource(id) ON
UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (uid) REFERENCES users(userid) ON
UPDATE CASCADE ON DELETE CASCADE
phd=# select version();
version
-
PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
(1 row)
TIA..
Shridhar
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Primary key and references
Hi, Today I discovered that if there is a compund primary key on a table, I can not create a reference from another table to one of the fields in the primary key.. Look at this.. phd=# create table tmp1(a integer,b integer,primary key(a,b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table tmp1 not found phd=# drop table tmp1; DROP TABLE phd=# create table tmp1(a integer unique,b integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE phd=# select version(); version - PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4 (1 row) Note that I do not require unique check on tmp2. It is perfectly acceptable to have duplicate values in table tmp2. However no duplicates are allowed in table tmp1. I consider this as a bug but given my understanding of sql, I won't count on it. Any comments? The workaround shown here is acceptable as I don't really need a compound primary key. But If I need, I know it won't work.. TIA.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Thread safe ecpg
Hi all, I was just wondering.The patches for making ecpg thread safe that were floating around few days back, are they going to make in any near future releases? I am badly bitten by libpq as code i my multithreaded app. is growing steadily. I find myself making stupid mistakes every now and then.Of course I don't expect any language to solve them for me but with ecpg it would be bit easier. Just thinking.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Friday 14 Feb 2003 9:05 pm, you wrote: Martin Coxall [EMAIL PROTECTED] writes: Here's a pretty topic for a flamewar: should it be /etc/postgres/ or /etc/postgresql/ ? I vote for /etc/pgsql. Keeping in line of unix philosophy of cryptic and short names. Who wants a descriptive names anyway..:-) Seriously, the traffic on last three days ahd very high noise ratio. Especially the whole discussion of PGDATA stuff fails to register as significant IMO. Right now, I can do things the way I want to do and I guess it is pretty much same with everyone else. Is it last topic left to improve? Keep it simple and on tpoic guys. This is hackers. Keep it low volume otherwise, two years down the lines, archives will be unsearchable.. Shridhar ---(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: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
On Tuesday 11 Feb 2003 8:01 pm, Mario Weilguni wrote: Hrm. I just saw that the PHP ADODB guy just published a bunch of database benchmarks. It's fairly evident to me that benchmarking PostgreSQL on Win32 isn't really fair: http://php.weblogs.com/oracle_mysql_performance And why is the highly advocated transaction capable MySQL 4 not tested? That's the problem, for every performance test they choose ISAM tables, and when transactions are mentioned it's said MySQL has transactions. But why no benchmarks? I did benchmark mysql/postgresql/oracle sometime back. Mysql with transaction is 90% as fast as postgresql. But it dies down with increased number of users no matter how much resources you throw at it. Oracle is 130% of postgresql. This was postgresql 7.2.x series so things have changed for sure, but you got the idea, right? Shridhar ---(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] Changing the default configuration (was Re: [pgsql-advocacy]
On Tuesday 11 Feb 2003 10:56 pm, you wrote: Josh Berkus [EMAIL PROTECTED] writes: What if we supplied several sample .conf files, and let the user choose which to copy into the database directory? We could have a high read performance profile, and a transaction database profile, and a workstation profile, and a low impact profile. Uh ... do we have a basis for recommending any particular sets of parameters for these different scenarios? This could be a good idea in the abstract, but I'm not sure I know enough to fill in the details. Let's take very simple scenario to supply pre-configured postgresql.conf. Assume that SHMMAX=Total memory/2 and supply different config files for 64MB/128Mb/256MB/512MB and above. Is it simple enough? Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ECPG, threading and pooling
Hi all, I would like to use ECPG as it is relatively easy to code. However my application is multithreaded and also uses connecion pools. I would like to know if ECPG is thread safe and I can use an arbitrary PGconn* object pulled from a connection pool. Do I need to use connection name? I mean how do I store connection name in an array or so? Otherwise I would have to fiddle with libpq which is bit more difficult than ECPG. TIA Shridhar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Windows Build System was: [HACKERS] Win32 port patches
Firebird uses a set of Borland command line tools and Borland's make, which they give away as a free download. Even if you're compiling for Windows, the build process uses Borland's command line make. A batch build script copies makefiles from a single source directory and spreads them around the tree, then kicks off Borland's make. For things to work successfully, you must download Borland's tools and install them together with setting a few environment variables by hand. Borland command line tools are just a set of Unix utilities like grep, sed, make, (sh?) etc. Once upon a time they required cygwin utilities, but managed to purge themselves of cygwin with the Borland utilities. When they required cygwin, they also required some Borland utilities anyway. So they had a real reason for purging cygwin. If someone thinks the cygwin package is too big, we could require the Borland utilities instead :) For my 2 cents, I would say the project files should be a separate download. Let someone build, test, and contribute them for particular versions of PostgreSQL. I would only try to make the Visual Studio files work on true releases. I would _not_ try to keep them updated in CVS or build them on the fly. W3.org's libwww does it something like this. bbaker ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Threads
I am sure, many of you would like to delete this message before reading, hold on. :-) I'm afraid most posters did not read the message. Those who replied Why bother? did not address your challenge: I think threads are useful in difference situations namely parallelising blocking conditions and using multiple CPUs. This is indeed one of the few good reasons for threads. Indeed, large/robust systems use a mix. The consensus of the group is that those who do the work are not ready for threads. Which is fine. Looking into my crystal ball, I see that it will happen, though it appears so far away. bbaker ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] urgent needed
Hi Were two doctoring students and we have a little problem to resolve. Were using Grass5pre3 and PostgreSQL 7.2 (Linux)to map vehicular pollution of our city. We have a map of the streets and we have to assign 24 values (+ the label) to each street. What would be a smart way to solve this problem using Postgres? Thanx a lot for your help, Alberto Massimo. Salve. Siamo due studenti laureandi e avremmo un problema da risolvere. Stiamo utilizzando Grass5pre3 e PostgreSQL 7.2 (Linux)per realizzare una mappatura dellinquinamento veicolare nella nostra città. Abbiamo una mappa delle vie e dobbiamo assegnare ad ogni via 24 valori (più letichetta). Qual e il modo più furbo e veloce per farlo, utilizzando Postgres? Grazie Mille per laiuto, Alberto Massimo. -- Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f Sponsor: Conto Arancio. Zero spese, stessa banca, più interessi. Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=657d=12-7 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_access
I'm pleased to see some renewed interest in pg_access. It seems obvious to me that MS Access is not currently...and probably never will be able to handle data in a robust and reliable fashion. MS Access' apparent success is due to the user interface quality and "ease of use" for "non-programmers". The "Relationships View" window, for example, is one of the best and most useful features ever invented for any database toolset. In reality PostGreSQL is in a "strong position" to fill the "reliability void" left byMS Access. However, the general public doesn't know much about the short comings ofAccess, due to MS advertising and sales efforts. It seems clear to me that the best way to "promote" the use of PostGreSQL is to offer more "ease of use" GUI interfaces for changing table structures, indexes, relationships, and upgrading older versions of files. Although it would be nice to have a native Windows version of PostGreSQL, as well as a Linux version, I expect Linux to replace Windows on a large number of PCs in the near future. I think that "having a Windows version" will not be a significant issue at that point. However, GUI based "ease of use" features WILL be an extremely important issue and will increase in importance for the rest of the forseeable future. Using a "browser" to implement the GUI toolset is a good start, but it probably won't support the same degree of user friendliness that is seen in the "Relationships View" window of MS Access, where a relationship can be instantly "drawn" with a mouse, and fields added to the Table with a simple "right click" on the Table header. If we do a good job of providingGUI based tools, similar to MS Access, as well as conversion tools from Access to PostGreSQL for existing data, then PostGreSQL and Linux should quickly become the "defacto standard" toolset for all website servers. It seems to me like PostGreSQL is already on this pathway, "like it or not", and that focussing on the GUI toolset is essential to maintaining a good relationship with those who are new to the Linux world. Whether you realize it or not, there is a humongous tidal wave of MS Access users currently gathering enough database theory expertise to "realize" the MS "snow job" they've been given about its reliability. They will be forced into finding another solution and chances are VERY good they won't opt for MS SQLServer or Oracle. If we are ready to givea solutionto them...greatsorry MS, but they seem to "like us better".If we are not ready, then our future won't have anything to do with MS, only our own lack of vision. At our current level of GUI tools, we can't expect any positive response even from fairly talented self taught computer programmers who have been interested in Linux since 1998 or later. Soon, there will be many Windows IT Specialists who will be seriously investigating the Linux OS and the "best database tools" available for it. Add to this list "end users" who are fed up with daily Windows crashes and are experimenting with hosting their own DSL based website serversand well...there's your tidal wave! Ready or notthe wave is directly behind ustime to "paddle" for all we're worth! Sincerely, Arthur Baldwin
Re: [HACKERS] pg_access
Dear Eric, Thanks for your input! I'm still in favor of developing a Windows version of PostGreSQL. But I don't think that intelligent people are going to want to use any version of Windows (especially since the news of DRMOS and TCPA included in Windows XP and soon to be implemented in all future releases of Windows 2000 Server) to host their ownwebsite via their DSL line. Another few reasons for this choice are the constant crashes of ALL versions of Windows and the increasing lack of responsiveness to fix bugs or admit to "less than advertised" limitations. Also, people who don't appreciate the far less than honest approach to Access users that MS has taken, are not about to spend thousands of dollars on MS SQL Server. At least not at a time when all signs point to abandoning Windows in favor of Linux, as a "necessary" step, albeit painful. "Necessary" because of the improved security that is possible, with sufficient Linuxexpertise and because of the lowered risk of being at only one company's mercy...as pointed out by the senator from Peru and many others since. Allowing MS to continue a 90 + percent market share (by remaining blissfully ignorant) until TCPA is fully implemented in all supported versions of Windows is nothing short of total insanity. I believe that most Americans are intelligent enough to realize this and avoid that scenario by making the switch to Linux. I don't believe that we Americans are stupid enough to let a company like MS take away almost every freedom that now exists in the field of technological development! If we are, then we will shortly lose our other freedoms as well...through ignorance and apathy. I believe that we will see many mfgs of motherboards that don't include the "Fritz chip" that are designed for those who vote for Linux and make the switch to an unfamiliar OS...primarily due to their discovery of the TCPA/MS agenda. Am I sure about this? Yes! Look at the intense interest shown by other countries in Linux application development for the GUI desktop. Do you think they will abandon their investment? I don't think so. Especially not in Taiwan or China where flexible and compatible "hard Real Time" versions of Linux are a primary development focus...to be marketed here in the US! (Examples: http://www.redsonic.com and China Soft...their website link can be found at the Redsonic websitehope you can read Chinese!) Redsonic's toolsetwill beat WinCE in the near future...without any doubt. The performance improvement and user friendliness of their tools far exceed any version of WinCE. And Redsonic isn't the only "Real Time" competitor. There are many, many others...and a steady flow of new ones. If we as Americans don't make the switch to Linux instead of Windows, including end users at home, then I can think of one Biblical phrase that fits, "Don't cast your pearls before swine, lest they turn and rend you". In other words, "send back your PC to the store"...we're too stupid and self absorbed to have the privilege of using them or to enjoy our current freedoms. Switching to Linux is currently a "pain" to be sure...but it is less painful than what will surely follow if the MS plan works. "A word to the wise is sufficient". And it won't be long until it is much less painful to make the switch. Sincerely, Arthur Baldwin Im afraid that I dont hold as much faith as you that Linux will become the defacto standard toolset for all website servers. MS, despite its major shortcomings, is fairly slow and steady when it comes to improvements to its OS. That said, Access is crap because no one uses it for what it was built to be used for. And I would imagine that MS would rather spend their time/money on SQL Server development. I agree with you that pgsql needs a more powerful, GUI interface. The QBE interface in Access is nice. However, I dont agree that it is unimportant to have a Windows version. Point being, that Linux users are used to and sadly often expect poor interfaces with the programs they use. Windows users are far less forgiving. If, what you are talking about, is truly wide spread use for PCs and small-time web-servers then a Windows interface is damn near necessary. Eric -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of [EMAIL PROTECTED]Sent: Tuesday, July 09, 2002 4:05 PMTo: PostGreSQL HackersSubject: Re: [HACKERS] pg_access I'm pleased to see some renewed interest in pg_access. It seems obvious to me that MS Access is not currently...and probably never will be able to handle data in a robust and reliable fashion. MS Access' apparent success is due to the user interface quality and "ease of use" for "non-programmers". The "Relationships View" window, for example, is one of the best and most useful f
[HACKERS] mV database tools
Dear Team, I've read your comments. No, I don't think that MVD's are the best thing since sliced breadbut they do have a certain "simplicity" that seems to hold the key to high speed analysis of large volumes of streaming data from the "eyes" of a robot. This stream of data must be quickly analyzed and subdivided into recognizable objects...I realize this area is wide open to debate...howeveran MVD might at least offer a "useful" theoretical "angle" in a "pre-PostGreSQL" stage of processing...just a thought...
[HACKERS] mV database tools
Dear Team, I'm wide open to other ideas for the support of robotic vision through tools already built into PostGreSQL. But you've already admitted to certain speed limitations...and robotic vision is going to require much more intense processing power. An MVD might allow the data stream to be quickly "re-arranged" during the recognition processing and then converted to RDBMS form later...after object recognition is "stabilized"... I really would love to see little "Short Circuits" running around teaching themselves...ok? Just a dream of mine...
[HACKERS] mV database tools
Dear Team, I have been monitoring this list for quite some time now and have been studying PostGreSQL for a while. I also did some internet research on the subject of "multi valued" database theory. I know that this is the basis for the "Pick" database system, FileMaker Pro, "D3", and a few other database systems. After carefully reviewing the theoretical arguments in favor of this type of database, I am thoroughly convinced that there are certain advantages to it that will never be matched by a traditional "relational database". I won't waste your time in reviewing the technical advantages here, because you can do your own research. However, I will say that it is obvious to me that an mV database will be an integral part of any truly practical AI robotics system. It will probably be necessary to "marry" the technologies of both relational databases and mV databases in such a system. IMHO, this is something that you, as the leaders in the most advanced database system ever developed, should carefully consider. The Linux community needs to be aware of the special advantages that an mV database offers, the way to interface an mV system with a traditional RDBMS, and the potential application theory as it relates to AI systems. We, as a community of leaders in GPL'd software need to make sure that this technology is part of the "knowledge base" of our community. Thanks for listening. Arthur
[HACKERS] Can't subscribe or get CVS
I just tried to get PostgreSQL from CVS , but it rejected the password 'postgresql' for user 'anoncvs': $ export CVSROOT=:pserver:[EMAIL PROTECTED]:/home/projects/pgsql/cvsroot $ cvs login (Logging in to [EMAIL PROTECTED]) CVS password: cvs login: authorization failed: server postgresql.org rejected access to /home/projects/pgsql/cvsroot for user anoncvs Then, I tried to post this to pgsql-hackers, but my scubscrption failed, too! From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Majordomo results Date: Thu, 20 Sep 2001 11:55:29 -0400 (EDT) subscribe Illegal command! Skipped 1 line of trailing unparseable text. No valid commands processed. Is majordomo and CVS broken, or do I need different instructions? --Noel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: OID wraparound (was Re: pg_depend)
[EMAIL PROTECTED] (Lamar Owen) wrote in message news:[EMAIL PROTECTED]... [trimmed cc:list] On Wednesday 18 July 2001 17:09, Bruce Momjian wrote: OK, we need to vote on whether Oid's are optional, and whether we can have them not created by default. [All the below IMHO] OID's should be optional. yep. we don't depend upon oids 32 bits. that's pretty standard practice for serious db apps. however, tx limit is a real problem. my vote is for solving the tx limit before chaning the oid problem. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
