Saturday, 27 January 2007
|
| VC2005 build and pthreads Gevik Babakhani 00:10:20 |
| | Folks,
I would like to build pg on VC2005. How do I use pthreads that is mentioned in the README file. Do I need the DLL? Sources? LIB? Where do I install or copy them..
Regards, Gevik
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Re: [PERFORM] how to plan for vacuum? Alvaro Herrera 00:07:45 |
| | Jim C. Nasby wrote:
I'll generally start with a cost delay of 20ms and adjust based on IO> utilization. I've been considering set a default autovacuum cost delay to 10ms; does this sound reasonable?
-- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 6 answers | Add comment |
Friday, 26 January 2007
|
| [pgsql-patches] scrollable cursor support for plpgsql Pavel Stehule 23:51:59 |
| | Hello
this patch contains ansi sql scrollable cursors's support for plpgsql. Add three function to SPI and plpgsql scrollable cursor sup. is first test app of this functionality.
Regards Pavel Stehule
_________________________________________________________________ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
|
| | Add comment |
|
| Can you specify the pg_xlog location from a config file? Karen Hill 23:50:09 |
| | Windows doesn't support symlinks. Is it possible instead for there to be a config file that lets one set where the pg_xlog directory will sit?
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
|
| | 4 answer | Add comment |
|
| NULL value in subselect in UNION causes error Jan Wieck 23:46:50 |
| | Checked it against HEAD and 8.2:
postgres=# select 1, 1, 1 union select * from (select 2, null, 2) two; ERROR: failed to find conversion function from "unknown" to integer
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
|
| | 2 answer | Add comment |
|
| Problems with MFC AddNew Guest 23:29:11 |
| | Hello,
I'm having some difficulty with adding new records using the ODBC driver with MFC. This is only occurring is certain situations...
I created a simple table:
CREATE TABLE project (project_id INT PRIMARY KEY);
Using Visual Studio C++ I created a CRecordset class (CProject) to exchange data with the project table. I then execute the following code:
CDatabase database; database.OpenEx(_T("DSN=PostgreSQL30")); CProject project(&database); project.Open(); project.AddNew(); project.m_project_id = 1; project.Update();
I get an Access Violation on the Update(). The log file shows the following error:
psqlodbc_3100.log: conn=01104290, query='SELECT "project_id" , "ctid", "project_id" FROM "public"."project"' [ fetched 0 rows ]
mylog_3100.log: [1800-4.186]extend_putdata_info: entering ... self=016C5EC4, parameters_allocated=0, num_params=1 [1800-4.191]extend_putdata_info: unable to create 1 new pdata from 0 old pdata
Now I've been able to get the Update() to work in the following situations:
- remove the primary key: CREATE TABLE project (project_id INT);
- change the int to bigint: CREATE TABLE project (project_id BIGINT PRIMARY KEY);
- make sure at least one record exists in the table: CREATE TABLE project (project_id INT PRIMARY KEY); INSERT INTO project (project_id) VALUES (2);
So what's going on? I would like to use int, but if I have to I can switch to bigint. Thanks for your help.
Ian
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
| | 1 answer | Add comment |
|
| DBI support for pg native arrays? Louis-David Mitterrand 23:26:26 |
| | Hello,
For a "select array(...) as col1, col2, col3 from table" I'd like the DBI driver to output col1 as a perl array instead of a scalar "{res1,res2,etc.}" representation of it.
Is that somehow possible? I looked at the docs without finding anything.
Thanks,
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
|
| | 1 answer | Add comment |
|
| Query regarding PostgreSQL date/time binary format for libpq Shoaib Naazir 23:21:21 |
| | Hi, I am working on an application using PostgreSQL. I need to send date to PostgreSQL in binary format using the libpq interface. I have walked through the PostgreSQL code base, did find some reference to the date/time binary format but didn't get the exact format. To much code to digest in one parse . Can you please provide me with any good reference to the exact binary format dbserver expect for date/time. I just can't use the text format . Any reference material, paper, web page or a mail specifying the exact binary date/time format will do. Thanks in advance. I really appreciate your time. regards,Shoaib Naazir. Artificial intelligence is no match for natural stupidity. _________________________________________________________________ Get into the holiday spirit, chat with Santa on Messenger. http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us |
| | 1 answer | Add comment |
|
| Intersection of two date interval Suha Onay 23:11:24 |
| | Hi,
How can i find the number of days in the intersection of 2 date interval? For example: 1st interval: (10.01.2007, 20.01.2007) 2nd interval: (13.01.2007, 21.01.2007) The intersection dates are: 13,14,15,16,17,18,19, 20 The result is: 8
How can i find the result, 8 in an sql query without using CASE statements?
Table columns are id PK, date1 date, date2 date.
Thanks for all your help.
Suha
|
| | 3 answer | Add comment |
|
| Re: too many trigger records found for relation "item" - Csaba Nagy 23:10:39 |
| | On Mon, 2007-01-22 at 20:56, Lenorovitz, Joel wrote: [snip]> ERROR: too many trigger records found for relation "item"
I've got this error on a development data base where we were continuously creating new child tables referencing the same parent table. The responsible code is in src/backend/commands/trigger.c, and I think it only happens if you manage to create/drop a new trigger (which also could be a FK trigger created by a new foreign key referencing that table, as in our case) exactly between that code gets the count of the triggers and processes them. In any case it should be a transient error, i.e. it should only happen when you heavily create/drop triggers... our integration test case was actually heavily creating new child tables, so that's how it happened for us.
In a production scenario I won't be creating all the time new triggers in parallel with other heavy activities, so it doesn't bother me.
Cheers, Csaba.
---------------------------(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
|
| | 11 answers | Add comment |
|
| Default fillfactor question (index types other than btree) Tony Caduto 23:09:24 |
| | Does anyone know what the default fillfactor is for index types other than btree?
I found in the docs that the default for btree is 90, but can't seem to find what it is for the other index types.
Thanks in advance,
Tony
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
|
| | Add comment |
|
| Re: Installation on Web Server codeWarrior 22:42:19 |
| | In theory -- yes. In practicality -- no -- And yes... yopu are corerct -- postgreSQL needs to be installed PHP. YOu will find this to be the case with ANY dependencies in PHP, including things like jpeg supprt, curl, etc. So this is NOT a postgreSQL problem, not really anyway.
This is realy a PHP / ./configure --with-postgres problem -- the problem being that you don't have the postgreSQL shared libraries installed because you did not install postgreSQL on the web server. PHP can't really be compiled with postgreSQL support without being able to link against the .so's [shared objects].
Your best bet: The easiest, most reliable solution is to do a full install of postgreSQL on your web server then rebuild PHP. You dont have to run postgeSQL on the web server -- the added benefit is that you have a readily available postgreSQL server.
The mendium level alternative -- I think you might be able to use the Pear::DB libs in PHP and not necessarily have to compile / build PHP against the postgreSQL libs. I am not positive on this -- I don't do things this way.
The hard way -- you woud have to identify the postgreSQL shared libs that PHP needs to link against, retrieve the postgreSQL source code, modigy the build script for postgreSQL so you build just the shared libs, then build / install those [the shared libs].
"Richard Hayward" <richard@tortoise.demon.co.uk> wrote in message news:5mnjr2p4d337h2ljhgiemlrf7vu21qg4lr@4ax.com...> I'm wanting to install PHP on a Apache/Linux server with support for> PostgreSQL.>
It seems that I need to install PostgreSQL on the machine first,> before the PHP installation will work.> The way I've done this previously is simply to install PostgreSQL.> However, the database server is in fact another machine, so another> full install of it on the web server seems redundant. All the> instructions I've found assume that web and database servers are> running on the same host.> Is there some (easy !) way to install just the minimum parts of> PostgreSQL I need on the web server?> Regards> Richard
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 4 answer | Add comment |
|
| Re: Can you specify the pg_xlog location from a config Dave Page 22:06:40 |
| | Karen Hill wrote:> Windows doesn't support symlinks. Is it possible instead for there to> be a config file that lets one set where the pg_xlog directory will sit?>
You can use a junction point for this instead of a symlink. Google for utilities to create them.
Regards, Dave.
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
|
| | 1 answer | Add comment |
|
| Rollback using WAL files? M.A. Oude Kotte 21:12:07 |
| | Hi all!
First of all I'm new to this list, please be gentle Next I'd like to mention that I've already searched the documentation and the archives, but couldn't find the answer to my question.
I'm running a production/development database using PostgreSQL 8.1 on a Debian server. Due to some bad code in one of our applications who use this database, some of the data was modified incorrectly the last few days. The idea is that I would like to restore the entire database as much as possible, meaning I would like to undo all transactions that were performed on it.
Now I've found the WAL files in the pg_xlog directory, and started browsing around for documentation on what I can do with those. But all I can find is that you can use them to restore your database after a crash or a custom backup. But I would like to do it the other way around (not use them to restore a database, but to roll it back entirely a few days). I have 4 WAL files, from last Tuesday to today. Can I use these files to ROLLBACK the current database, so that it's restored to the situation it was in on Tuesday?
Thanks a lot for any help,
Regards,
Marc
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | 4 answer | Add comment |
|
| [pgsql-patches] Getting rid of warnings Magnus Hagander 20:46:01 |
| | Attached patch gets rid of most of the remaining warnings on a VC++ build. Summary is: * A bunch of places that had different const specifyer in the header and in the body of the function. (contrib/intarray, src/timezone) * 1.2 and such constants are double and cause warning. Define as floats (contrib/pg_trgm and contrib/tsearch2) * HAVE_STRERROR is defined by python, so only conditionally redefine it in pg_config.h * NULL function pointer in SSL call cast to the correct pointer type * ssize_t is defined in pg_config_os.h, remove from libpq-int.h * Always skip warning 4102 ("label nnn: unreferenced label") caused by bison. * Support for ignoring linker warnings, and ignore the warning about PRIVATE on DllRegisterServer. Can't fix properly because PRIVATE is not supported by mingw.
Index: contrib/intarray/_int_tool.c =================================================================== RCS file: /projects/cvsroot/pgsql/contrib/intarray/_int_tool.c,v retrieving revision 1.8 diff -c -r1.8 _int_tool.c *** contrib/intarray/_int_tool.c4 Oct 2006 00:29:45 -00001.8 --- contrib/intarray/_int_tool.c25 Jan 2007 12:16:46 -0000 *************** *** 188,194 ****
/* len >= 2 */ bool ! isort(int4 *a, int len) { int4tmp, index; --- 188,194 ----
/* len >= 2 */ bool ! isort(int4 *a, const int len) { int4tmp, index; Index: contrib/pg_trgm/trgm_op.c =================================================================== RCS file: /projects/cvsroot/pgsql/contrib/pg_trgm/trgm_op.c,v retrieving revision 1.5 diff -c -r1.5 trgm_op.c *** contrib/pg_trgm/trgm_op.c30 May 2006 22:12:13 -00001.5 --- contrib/pg_trgm/trgm_op.c25 Jan 2007 12:18:05 -0000 *************** *** 5,11 ****
PG_MODULE_MAGIC;
! float4trgm_limit = 0.3;
PG_FUNCTION_INFO_V1(set_limit); Datumset_limit(PG_FUNCTION_ARGS); --- 5,11 ----
PG_MODULE_MAGIC;
! float4trgm_limit = 0.3f;
PG_FUNCTION_INFO_V1(set_limit); Datumset_limit(PG_FUNCTION_ARGS); Index: contrib/tsearch2/rank.c =================================================================== RCS file: /projects/cvsroot/pgsql/contrib/tsearch2/rank.c,v retrieving revision 1.21 diff -c -r1.21 rank.c *** contrib/tsearch2/rank.c28 Dec 2006 01:09:01 -00001.21 --- contrib/tsearch2/rank.c25 Jan 2007 12:19:30 -0000 *************** *** 37,43 **** PG_FUNCTION_INFO_V1(get_covers); Datumget_covers(PG_FUNCTION_ARGS);
! static float weights[] = {0.1, 0.2, 0.4, 1.0};
#define wpos(wep)( w[ WEP_GETWEIGHT(wep) ] )
--- 37,43 ---- PG_FUNCTION_INFO_V1(get_covers); Datumget_covers(PG_FUNCTION_ARGS);
! static float weights[] = {0.1f, 0.2f, 0.4f, 1.0f};
#define wpos(wep)( w[ WEP_GETWEIGHT(wep) ] )
*************** *** 59,65 **** word_distance(int4 w) { if (w > 100) ! return 1e-30;
return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2)); } --- 59,65 ---- word_distance(int4 w) { if (w > 100) ! return (float4)1e-30;
return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2)); } *************** *** 331,337 **** calc_rank_and(w, t, q) : calc_rank_or(w, t, q);
if (res < 0) ! res = 1e-20;
if ((method & RANK_NORM_LOGLENGTH) && t->size > 0) res /= log((double) (cnt_length(t) + 1)) / log(2.0); --- 331,337 ---- calc_rank_and(w, t, q) : calc_rank_or(w, t, q);
if (res < 0) ! res = (float)1e-20;
if ((method & RANK_NORM_LOGLENGTH) && t->size > 0) res /= log((double) (cnt_length(t) + 1)) / log(2.0); Index: src/include/pg_config.h.win32 =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/pg_config.h.win32,v retrieving revision 1.39 diff -c -r1.39 pg_config.h.win32 *** src/include/pg_config.h.win325 Jan 2007 20:54:39 -00001.39 --- src/include/pg_config.h.win3225 Jan 2007 13:10:02 -0000 *************** *** 366,372 **** --- 366,374 ---- #define HAVE_STRDUP 1
/* Define to 1 if you have the `strerror' function. */ + #ifndef HAVE_STRERROR #define HAVE_STRERROR 1 + #endif
/* Define to 1 if you have the `strerror_r' function. */ /* #undef HAVE_STRERROR_R */ Index: src/interfaces/libpq/fe-secure.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-secure.c,v retrieving revision 1.90 diff -c -r1.90 fe-secure.c *** src/interfaces/libpq/fe-secure.c5 Jan 2007 22:20:01 -00001.90 --- src/interfaces/libpq/fe-secure.c25 Jan 2007 13:21:17 -0000 *************** *** 642,648 **** return 0; } #endif ! if (PEM_read_PrivateKey(fp, pkey, cb, NULL) == NULL) { char *err = SSLerrmessage();
--- 642,648 ---- return 0; } #endif ! if (PEM_read_PrivateKey(fp, pkey, (pem_password_cb *)cb, NULL) == NULL) { char *err = SSLerrmessage();
Index: src/interfaces/libpq/libpq-int.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-int.h,v retrieving revision 1.117 diff -c -r1.117 libpq-int.h *** src/interfaces/libpq/libpq-int.h5 Jan 2007 22:20:01 -00001.117 --- src/interfaces/libpq/libpq-int.h25 Jan 2007 12:12:57 -0000 *************** *** 38,48 **** #include <signal.h> #endif
- #ifdef WIN32_ONLY_COMPILER - typedef int ssize_t;/* ssize_t doesn't exist in VC (at least not - * VC6) */ - #endif - /* include stuff common to fe and be */ #include "getaddrinfo.h" #include "libpq/pqcomm.h" --- 38,43 ---- Index: src/timezone/ialloc.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/timezone/ialloc.c,v retrieving revision 1.7 diff -c -r1.7 ialloc.c *** src/timezone/ialloc.c15 Oct 2005 02:49:51 -00001.7 --- src/timezone/ialloc.c25 Jan 2007 12:26:43 -0000 *************** *** 14,20 **** #define nonzero(n)(((n) == 0) ? 1 : (n))
char * ! imalloc(const int n) { return malloc((size_t) nonzero(n)); } --- 14,20 ---- #define nonzero(n)(((n) == 0) ? 1 : (n))
char * ! imalloc(int n) { return malloc((size_t) nonzero(n)); } *************** *** 28,34 **** }
void * ! irealloc(void *pointer, const int size) { if (pointer == NULL) return imalloc(size); --- 28,34 ---- }
void * ! irealloc(void *pointer, int size) { if (pointer == NULL) return imalloc(size); Index: src/timezone/zic.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/timezone/zic.c,v retrieving revision 1.19 diff -c -r1.19 zic.c *** src/timezone/zic.c24 Oct 2006 15:11:03 -00001.19 --- src/timezone/zic.c25 Jan 2007 12:20:42 -0000 *************** *** 104,113 **** };
extern intlink(const char *fromname, const char *toname); ! static void addtt(pg_time_t starttime, int type); static int addtype(long gmtoff, const char *abbr, int isdst, int ttisstd, int ttisgmt); ! static void leapadd(pg_time_t t, int positive, int rolling, int count); static void adjleap(void); static void associate(void); static intciequal(const char *ap, const char *bp); --- 104,113 ---- };
extern intlink(const char *fromname, const char *toname); ! static void addtt(const pg_time_t starttime, int type); static int addtype(long gmtoff, const char *abbr, int isdst, int ttisstd, int ttisgmt); ! static void leapadd(const pg_time_t t, int positive, int rolling, int count); static void adjleap(void); static void associate(void); static intciequal(const char *ap, const char *bp); *************** *** 146,152 **** const char *typep, const char *monthp, const char *dayp, const char *timep); static void setboundaries(void); ! static pg_time_t tadd(pg_time_t t1, long t2); static void usage(void); static void writezone(const char *name); static intyearistype(int year, const char *type); --- 146,152 ---- const char *typep, const char *monthp, const char *dayp, const char *timep); static void setboundaries(void); ! static pg_time_t tadd(const pg_time_t t1, long t2); static void usage(void); static void writezone(const char *name); static intyearistype(int year, const char *type); Index: src/tools/msvc/Project.pm =================================================================== RCS file: /projects/cvsroot/pgsql/src/tools/msvc/Project.pm,v retrieving revision 1.7 diff -c -r1.7 Project.pm *** src/tools/msvc/Project.pm24 Jan 2007 19:24:28 -00001.7 --- src/tools/msvc/Project.pm25 Jan 2007 13:32:08 -0000 *************** *** 23,29 **** includes => '', defines => ';', solution => $solution, ! disablewarnings => '4018;4244;4273', };
bless $self; --- 23,30 ---- includes => '', defines => ';', solution => $solution, ! disablewarnings => '4018;4244;4273;4102', ! disablelinkerwarnings => '' };
bless $self; *************** *** 242,247 **** --- 243,255 ---- $self->AddFile("$dir\\win32ver.rc"); }
+ sub DisableLinkerWarnings { + my ($self, $warnings) = @_; + + $self->{disablelinkerwarnings} .= ';' unless ($self->{disablelinkerwarnings} eq ''); + $self->{disablelinkerwarnings} .= $warnings; + } + sub Save { my ($self) = @_;
*************** *** 390,395 **** --- 398,406 ---- GenerateMapFile="FALSE" MapFileName=".\\$cfgname\\$self->{name}\\$self->{name}.map" SubSystem="1" TargetMachine="1" EOF + if ($self->{disablelinkerwarnings}) { + print $f "\t\tAdditionalOptions=\"/ignore self->{disablelinkerwarnings}\"\n"; + } if ($self->{implib}) { my $l = $self->{implib}; $l =~ s/__CFGNAME__/$cfgname/g; Index: src/tools/msvc/mkvcbuild.pl =================================================================== RCS file: /projects/cvsroot/pgsql/src/tools/msvc/mkvcbuild.pl,v retrieving revision 1.11 diff -c -r1.11 mkvcbuild.pl *** src/tools/msvc/mkvcbuild.pl24 Jan 2007 19:24:28 -00001.11 --- src/tools/msvc/mkvcbuild.pl25 Jan 2007 13:31:27 -0000 *************** *** 135,140 **** --- 135,141 ---- $pgevent->AddResourceFile('src\bin\pgevent','Eventlog message formatter'); $pgevent->RemoveFile('src\bin\pgevent\win32ver.rc'); $pgevent->UseDef('src\bin\pgevent\pgevent.def'); + $pgevent->DisableLinkerWarnings('4104');
my $psql = AddSimpleFrontend('psql', 1); $psql->AddIncludeDir('src\bin\pg_dump');
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
| | 11 answers | Add comment |
|
| Make find what field active Andrus 20:24:56 |
| | If I press Ctrl+F in Query window, Cancel button is active.
Can you change find dialog, that Find what: field is active by default like in other applications, please.
Andrus.
---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 3 answer | Add comment |
|
| HAVING push-down Simon Riggs 20:07:37 |
| | I've just read a paper that says PostgreSQL doesn't do this. My reading of the code is that we *do* evaluate the HAVING clause prior to calculating the aggregates for it. I thought I'd check to resolve the confusion.
- - -
If not, it seems fairly straightforward to push down some or all of a HAVING clause so that the qual clause is tested prior to aggregation, not after aggregation. This could, for certain queries, significantly reduce the amount of effort that the final Agg node performs.
We might think about deeper push-down within the query, but since the Agg node already has the havingQual, it seems a straightforward act to decide whether to apply it before or after the aggregation.
We already do find_unaggregated_cols(), so little additional analysis seems required.
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
|
| | 6 answers | Add comment |
|
| Issues with roles Andy Shellam 19:52:33 |
| | A couple of niggly little issues with the roles in PgAdmin 1.6.2 on an 8.2.1 server.
When you click Login/Group Roles in the object explorer, you cannot right-click and do "new role" like you can with Databases and Tablespaces. Also the "New object of this type" button is not enabled when you click New group/login role - you have to add a new role by right-clicking the server connection, New Object... New Group/Login Role.
Any chance of having this sorted for the next release?
Thanks,
-- Andy Shellam NetServe Support Team
the Mail Network "an alternative in a standardised world"
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 4 answer | Add comment |
|
| wrong parsing of "select version();" Eberhard Schulte 19:43:26 |
| | Hello,
if I excute "select version();", then I recieve: "7.3.4-RH". In the JDBC-Driver code however two of token are expected. A NoSuchElementException could be thrown. I think, there is a bug!
postgresql-jdbc-8.2-504.src.tar.gz: ConnectionFactoryImpl.java line 426:
StringTokenizer versionParts = new StringTokenizer(rawDbVersion); versionParts.nextToken(); /* "PostgreSQL" */ String dbVersion = versionParts.nextToken(); /* "X.Y.Z" */
Regards,
Eberhard Schulte
------------------------------------------------ Eberhard Schulte Senior Software Engineer Pixelboxx GmbH esc@pixelboxx.de Ostenhellweg 56-58 http://www.pixelboxx.de/ 44135 Dortmund (02 31) 5 34 63-207 ------------------------------------------------
---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 2 answer | Add comment |
|
| Implied Functional index use (redux) Simon Riggs 19:37:29 |
| | In a thread in July last year, I raised the possibility of transforming a query to allow functional indexes to be utilised automatically. http://archives.postgresql.org/pgsql-hackers/2006-07/msg00323.php
This idea can work and has many benefits, but there are some complexities. I want to summarise those issues first, then make a more practical and hopefully more acceptable proposal.
Taken together the complexities would have lead us to have additional TRANSFORMABLE clauses on TYPEs, FUNCTIONs and potentially encoding schemes. All of which, I agree, just too much complexity to allow this to be specified.
One example of this was FLOAT, where -0 and +0 are equal but not the same in a binary form. That would normally mean we couldn't use FLOAT for TRANSFORMABLE indexes, but of course what happens if we specify a partial functional index, where we only index values > 0. In that case, we *can* use the transform technique again. Worse still we may have a full (non-partial) index where there is a constraint on the column(s) such as CHECK (value > 0). So we'd need another heavy dose of catalog-complexity to catch all the special cases. Yuck and double Yuck.
Even if we did that, it isn't easy for a data type author to tell whether their type is transformable, or not **in all cases**. That would probably lead to people saying DISABLE TRANSFORM for their data type, just in case. Which means no benefit in practice with this feature.
- - -
A simpler, alternate proposal is to allow the user to specify whether a functional index is transformable or not using CREATE or ALTER INDEX, with a default of not transformable. That then leaves the responsibility for specifying this with the user, who as we have seen is the really only person really capable of judging the whole case on its merits.
e.g. CREATE INDEX fooidx ON foo (foofunc(foocol1)) [TABLESPACE ...] [ENABLE|DISABLE TRANSFORM] [WHERE ...];
ENABLE TRANSFORM is only possible for functional indexes.
Suggestions for better syntax/naming welcome.
Placing the TRANSFORM clause on the index as a simple boolean makes utilising the feature more streamlined at planning time too. This would be an extra initial check in create_index_paths() to see if the query might benefit from transform. Most indexable WHERE clauses would be able to be transformed, if the index allows.
The feature would be enabled by default with a GUC, but as stated above, the default for each index would be to *not* transform unless specifically requested by the user.
enable_index_transform = on (default)| off
EXPLAIN would not need alteration, since the modified query would show up clearly in the output. (I can add explicit visibility if people want that).
Overall, a fairly isolated patch, with little user interface changes.
All of the complexities would be very clearly documented as part of this feature. That is essential to avoid user error, of which I am mindful. But the technique has much promise, so I would like to make this option available to designers and DBAs.
If we can agree this smoothly, then it seems possible for 8.3.
Comments?
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 4 answer | Add comment |
|
| Re: [Fwd: [PORTS] M$ SQL server DTS package equivalent in Jeremy Haile 18:22:40 |
| | I've also used Pentaho Data Integration (previously known as Kettle) quite extensively, and can recommend it. It supports many different databases and has fairly good documentation (although thin in some areas). It has a GUI drag-and-drop tool that can be used to configure transformations and is very flexible. It also has an active community that responds when you have issues.
I use it as part of a regular job that runs every 5 minutes and hourly to copy and transform data from a SQL Server DB to a PostgreSQL DB. I use COPY when I can simply select data into a CSV and load it into another DB - but as Tomi said, when you have to do primary key generation, row merging, data cleanup, and data transformations - I would use some sort of ETL tool over just SQL.
My 2 cents, Jeremy Haile
On Fri, 26 Jan 2007 15:14:22 +0000, "Tomi N/A" <hefest@gmail.com> said:> > Besides being easy to schedule and very flexible, manipulating data> > with queries is extremely powerful and fairly easy to maintain> > assuming you know a little SQL -- thanks to postgresql's huge array of> > built in string manipulation functions. Your skills learned here will> > pay off using the database as well for other things.> >
Not only that, but this approach will be fast since it is declarative> > and handles entire tables at once as opposed to DTS-ish solutions> > which tend to do processing record by record. Not to mention they are> > overcomplicated and tend to suck. (DTS does have the ability to read> > from any ODBC source which is nice...but that does not apply here).> Different strokes for different folks, it seems.> I'd argue that COPY followed by a barrage of plpgsql statements can't> be used for anything but the most trivial data migration cases (where> it's invaluable) where you have line-organized data input for a> hand-full of tables at most.> In my experience (which is probably very different from anyone> else's), most real world situations include data from a number of very> different sources, ranging from the simplest (.csv and, arguably,> .xml) to the relatively complex (a couple of proprietary databases,> lots of tables, on-the fly row merging, splitting or generating> primary keys, date format problems and general pseudo-structured,> messed up information).> Once you've got your data in your target database (say, pgsql), using> SQL to manipulate the data makes sense, but it is only the _final_> step of an average, real world data transformation.> Cheers,> t.n.a.> ---------------------------(end of broadcast)---------------------------> TIP 4: Have you searched our list archives?> ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | Add comment |
|
| [pgsql-patches] Docs improvements Teodor Sigaev 18:17:16 |
| | 1) mvcc.sgml.patch Update comments about installation of DocBook on FreeBSD. DocBook v4.2 is present in ports now.
2) docguide.sgml.patch Table of compatibility of table-lock modes. IMHO, it's useful, clear for understanding. Text view of the table is: | AS | RS | RE | SUE | S | SRE | E | AE | ----+-------+-------+-------+-------+-------+-------+-------+-------+ AS | O | O | O | O | O | O | O | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ RS | O | O | O | O | O | O | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ RE | O | O | O | O | X | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ SUE | O | O | O | X | X | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ S | O | O | X | X | O | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ SRE | O | O | X | X | X | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ E | O | X | X | X | X | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+ AE | X | X | X | X | X | X | X | X | ----+-------+-------+-------+-------+-------+-------+-------+-------+
Upper row and left column contain abbreviation of lock modes by the first characters( AS - ACCESS SHARE ) -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
*** mvcc.sgml.origFri Jan 26 16:27:27 2007 --- mvcc.sgmlFri Jan 26 17:49:29 2007 *************** *** 741,746 **** --- 741,863 ---- releases locks acquired within it. </para> + <table tocentry="1" id="table-lock-compatibility"> + <title> Compatibility of lock modes</title> + <tgroup cols="9"> + <colspec colnum="1" colwidth="1*"> + <colspec colnum="2" colwidth="1*"> + <colspec colnum="3" colwidth="1*"> + <colspec colnum="4" colwidth="1*"> + <colspec colnum="5" colwidth="1*"> + <colspec colnum="6" colwidth="1*"> + <colspec colnum="7" colwidth="1*"> + <colspec colnum="8" colwidth="1*"> + <colspec colnum="9" colwidth="1*"> + <thead> + <row> + <entry>Modes</entry> + <entry>AS</entry> + <entry>RS</entry> + <entry>RE</entry> + <entry>SUE</entry> + <entry>S</entry> + <entry>SRE</entry> + <entry>E</entry> + <entry>AE</entry> + </row> + </thead> + <tbody> + <row> + <entry>AS</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>RS</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>RE</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>SUE</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>S</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>SRE</entry> + <entry align="center">Y</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>E</entry> + <entry align="center">Y</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + <row> + <entry>AE</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + <entry align="center">N</entry> + </row> + </tbody> + </tgroup> + </table> </sect2> <sect2 id="locking-rows">
*** docguide.sgml.origFri Jan 26 15:59:29 2007 --- docguide.sgmlFri Jan 26 16:08:58 2007 *************** *** 212,220 **** <listitem> <para><filename>textproc/dsssl-docbook-modular</filename></para> </listitem> </itemizedlist> - Apparently, there is no port for the DocBook V4.2 SGML DTD - available right now. You will need to install it manually. </para> <para> --- 212,221 ---- <listitem> <para><filename>textproc/dsssl-docbook-modular</filename></para> </listitem> + <listitem> + <para><filename>textproc/docbook-420</filename></para> + </listitem> </itemizedlist> </para> <para> *************** *** 225,234 **** <para> It's possible that the ports do not update the main catalog file ! in <filename>/usr/local/share/sgml/catalog</filename>. Be sure to ! have the following line in there: <programlisting> ! CATALOG "/usr/local/share/sgml/docbook/4.2/docbook.cat" </programlisting> If you do not want to edit the file you can also set the environment variable <envar>SGML_CATALOG_FILES</envar> to a --- 226,238 ---- <para> It's possible that the ports do not update the main catalog file ! in <filename>/usr/local/share/sgml/catalog.ports</filename> or order ! isn't proper . Be sure to have the following lines in begining of file: <programlisting> ! CATALOG "openjade/catalog" ! CATALOG "iso8879/catalog" ! CATALOG "docbook/dsssl/modular/catalog" ! CATALOG "docbook/4.2/catalog" </programlisting> If you do not want to edit the file you can also set the environment variable <envar>SGML_CATALOG_FILES</envar> to a
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
| | 1 answer | Add comment |
|
| [Fwd: [PORTS] M$ SQL server DTS package equivalent in Postgres] Paul Lambert 18:14:22 |
| | Sorry, posted this to the wrong list
-------- Original Message --------
Subject:
[PORTS] M$ SQL server DTS package equivalent in Postgres
Date:
Tue, 23 Jan 2007 10:15:06 +0900
From:
Paul Lambert <paul.lambert@autoledgers.com.au> [mailto aul.lambert@autoledgers.com.au]
To:
pgsql-ports@postgresql.org [mailto gsql-ports@postgresql.org]
G'day, Is there an equivalent in Postgres to the DTS Packages available in M$ SQL server. I use these in SQL server to pre-load data from CSV files prior to enabling replication from my primary application. Any pointers on where best to go for this would be appreciated. I'm reading about something called EMS, is that the way to go? Sample of one of the files I use: DEALER_ID^DATE_CHANGED^TIME_CHANGED^BILLING_CODE_ID^DES^BILLING_CODE_TYPE^LABOUR_RATE^LABOUR_SALES_GROUP^CUSTOMER_NO^PARTS_SALES_GRO f UP^COMEBACK^WORKSHOP^FRANCHISE^LOCATION^DELETEFLAG F65^23-Jan-2007^10:13^AA^ADVERSITING ADMIN^I^45^40^2196^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AN^ADV NEW^I^45^40^1636^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AP^ADV PARTS^I^45^40^1919^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AS^ADV SERV^I^45^40^2057^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AU^ADV USED^I^45^40^1775^18^^0^BLANK^0^N F65^23-Jan-2007^10:13^BA^B R&M ADM^I^45^40^2823^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BG^BUILDING MAINTENANCE GM HOLDEN^I^45^40^1311^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BN^B R&M NEW^I^45^40^2268^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BP^B R&M PART^I^45^40^2541^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BS^B R&M SERV^I^45^40^2680^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BU^B R&M USED^I^45^40^2401^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^F^FLEET^C^50^27^0^17^^0^BLANK^0^ F65^23-Jan-2007^10:13^FC^FORD COMEBACK MECHANIC^I^65^21^140^19^Y^0^BLANK^0^ Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
-- Paul Lambert Technical Support Team Leader and Database Administrator AutoLedgers Level 3, 823 Wellington Street, West Perth, W.A. 6005 Postal: P.O. Box 106, West Perth, W.A. 6872 Ph: 08 9217 5086 Fax: 08 9217 5055 AutoLedgers Technical Support Desk: 1800 649 987 (Free call) 08 9217 5050 (Perth local and mobile) Email: paul.lambert@autoledgers.com.au [mailto aul.lambert@autoledgers.com.au] <http://www.reynolds.com.au> [http://www.reynolds.com.au] ------------------------------------------------------------------------------------ For AutoLedgers technical support, please send an email to helpdesk@autoledgers.com.au [mailto:helpdesk@autoledgers.com.au] .
|
| | 3 answer | Add comment |
|
| Re: "no unpinned buffers available" ? why? (hstore and plperl involved) Dave Cramer 18:12:00 |
| | Tom,
I've also got a customer getting this error message.
the OS is OSX 10.3 they are using plpgsql, and shared buffers is set very low
shared_buffers = 16
Dave
On 3-Jan-07, at 10:19 AM, Tom Lane wrote:
"hubert depesz lubaczewski" <depesz@gmail.com> writes:>> and - after some time of this "create table", postmaster process >> eats all>> the memory (over 1.8g), and dies with:>> psql:133.sql:125: ERROR: error from Perl function: no unpinned >> buffers>> available at line 5.> Could you reduce this to a self-contained example please? Your> functions depend on a bunch of tables that you have not provided> definitions or data for ...> regards, tom lane> ---------------------------(end of > broadcast)---------------------------> TIP 6: explain analyze is your friend>
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
| | 1 answer | Add comment |
|