|
 |
| Recent blog posts: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Friday, 26 January 2007
|
| 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 |
|
| 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 |
|
| 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 [mailtogsql-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 [mailtoaul.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 |
|
| Using Temporary Tables in postgres functions Mario Splivalo 17:57:32 |
| | When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
as a function language), I can't because postgres can't find that
temporary table. Consider this example:
CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$
CREATE TEMPORARY TABLE tmpTbl
AS
SELECT
message_id
FROM
cached_messages
WHERE
billing_status = 2;
UPDATE cached_messages SET billing_status = 1 WHERE message_id IN
(SELECT message_id FROM tmpTbl);
SELECT
*
FROM
v_messages_full
WHERE
message_id IN (SELECT message_id FROM tmpTbl);
$BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
When I try to execute above CREATE FUNCTION statement postgres gives me
this:
ERROR: relation "tmptbl" does not exist
CONTEXT: SQL function "func1"
If I rewrite func1() using 'plpgsq' I have no troubles creating
temporary tables, I just need to use EXEC when referencing to those
temporary tables (which is cumbersome, but there it is).
Am I doing something wrong here, or there is no way of using temporary
tables within 'sql' written functions?
Mike
---------------------------(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
|
| | 7 answers | Add comment |
|
| LEFT Join Question Rob V 17:44:43 |
| | Hello all,
Ive been racking my brain for a few hours now and need some help, please!!!
I have the following tables :
account
=account_id
=account_type_id
=account_username
vendor
=account_id
=vendor_status
contact_name
=account_id
=name
=domain_type_id
contact_address
=account_id
=address
=domain_type_id
contact_email
=account_id
=email
=domain_type_id
contact_phone
=account_id
=phone
=domain_type_id
account_type
=account_type_id
= account_type_tag
records :
1 VENDOR
2 SELLER
3 CONTRACTOR
domain_type
=domain_type_id
=domain_type_tag
records :
1 PRIMARY
2 SECONDARY
Im looking for a select that will give me all records where the
account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY"
even if the address, email and phone are blank.
Users WILL HAVE records in the following tables :
account,
vendor
contact_name
contact_email
they MAY or MAYNOT have records in the following tables :
contact_address
contact_phone
I know I have to use a left join - but I can seem to figure out the syntax
when dealing w/ different columns of the same table.
Im basically looking for this :
account_username vendor_status name address
email phone
---------------------------------------------------------------------------------------------------------------------------------------------------------
Rob123 ACTIVE ROB 123
Test Drive rob@here.com 555-1212
BILL123 ACTIVE Bill
NULL bill@here.com 456-4444
Steve1234 INACTIVE Steve 654
Hill St steve@here.com NULL
I know I have to use a left join - but I can seem to figure out the syntax
when dealing w/ different columns of the same table.
(do I still need the = in the where clause when using a left join?)
SELECT
account.account_id,
account.account_username,
vendor.vendor_status,
contact_name.name,
contact_address.address,
contact_email.email,
contact_phone.phone
FROM
account a
LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id)
LEFT JOIN contact_address on (contact_address.account_id = a.account_id),
vendor,
contact_email,
contact_name,
domain_type,
account_type,
WHERE
vendor.vendor_id = account.account_id AND
contact_email.account_id = account.account_id AND
contact_name.account_id = account.account_id AND
account.account_type_id = account_type.account_type_id AND
contact_email.domain_type_id = domain_type.domain_type_id AND
contact_name.domain_type_id = domain_type.domain_type_id AND
vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND
account_type.account_type_tag = 'ACCOUNT_VENDOR' AND
domain_type.domain_type_tag = 'VENDOR_PRIMARY'
The problem Im having is b/c Im only looking for specific domain types I
have a join on the contact address and phone tables where the
domain types match - but if there are no records - it causes the entire
record not to be show - so I need to do a left join on that table as well
but If I try to do it - I get an error " table name "contact_phone"
specified more than once"
Any help would be great!!!
Thanks
-Rob
|
| | 4 answer | Add comment |
|
| Evaluate a variable Lu s Sousa 17:19:11 |
| | Hi,
Is there anyway to evaluate a variable in plpgsql, like eval on PHP?
Suppose the example:
my_var:=''some value!'';
a:=''my_var'';
b:=<eval!!> a;
I already tried b:=EXECUTE a; without luck!
Best regards,
Lu s Sousa
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| trigger question. Alain Bruneau 16:31:48 |
| | Hi.
Has anyone migrated from Oracle to Postgresql there ?
I have a problem with trigger. I don't find a way to disable it.
Oracle statement :
ALTER TRIGGER "TRG_DT_ACTI" ENABLE
or
ALTER TRIGGER "TRG_DT_ACTI" DISABLE
I am looking for a way to do the same thing with postgresql.
I want to disable trigger before COPY and enable it after.
How can I do that?
Thank you for your help.
Regards,
Alain.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 30 answers | Add comment |
|
| [pgsql-patches] uuid patch 3.0 (8.3devel) Gevik Babakhani 15:11:56 |
| | Folks,
As commented by Peter, I have done some re-styling.
Some additional tests and format checking have been added to this patch.
Put your file at the end of the OBJS variable (or in some sort of > sensible order). Done.
Put your file at the end of the tests (or in some sort of sensible > order).
Done.
Refrain from self-evident comments, such as> + /*> + * function handles input for the uuid datatype> + */> + Datum uuid_in(PG_FUNCTION_ARGS)> You can probably delete all comments in your patch by that criterion.
Some changed, but not all has been removed for readability reasons.
This sort of super-verbose coding might be alright, but it gets tiring > when done systematically for no reason:> + result = DirectFunctionCall1(textin, uuid_str);> + return result;
Some changed, but not all.
The uuid.c file claims it is uuid.h.
Changed.
Move the stuff from builtins.h to uuid.h. Not changed. please see:
http://archives.postgresql.org/pgsql-patches/2007-01/msg00362.php
Move the stuff from uuid.h that is not needed anywhere else to uuid.c.
Done.
No // comments.
Done.
Don't number the tests. We might want to insert something later and > that would mess everything up.
Done.
Capitalize the SQL test scripts as in other files.
Done.
Remove gratuitous whitespace changes (there are many).
Done. AFAICS
Also remove the whitespace at the end of lines.
Done. AFAICS
Make some reasonable effort to align the catalog entries for > readability.>
Done.
Any more comments?
Regards,
Gevik.
---------------------------(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
|
| | Add comment |
|
| Duplicate key violation Brian Wipf 13:55:20 |
| | I got a duplicate key violation when the following query was performed:
INSERT INTO category_product_visible (category_id, product_id)
SELECT cp.category_id, cp.product_id
FROM category_product cp
WHERE cp.product_id = $1 AND
not exists (
select 'x'
from category_product_visible cpv
where cpv.product_id = cp.product_id and
cpv.category_id = cp.category_id
);
This is despite the fact the insert is written to only insert rows
that do not already exist. The second time the same query was run it
went through okay. This makes me think there is some kind of race
condition, which I didn't think was possible with PostgreSQL's MVCC
implementation. I'm unable to duplicate the problem now and the error
only occurred once in weeks of use. This is on PostgreSQL 8.2.1
running on openSUSE Linux 10.2. Slony-I 1.2.6 is being used for
replication to a single slave database.
I'll monitor the problem and if it recurs, I'll rebuild the primary
key index. Perhaps the category_product_visible_pkey index was/is
corrupted in some way.
Brian Wipf
<brian@clickspace.com>
The exact error was:
select process_pending_changes(); FAILED!!! Message: ERROR: duplicate
key violates unique constraint "category_product_visible_pkey"
CONTEXT: SQL statement "INSERT INTO category_product_visible
(category_id, product_id) SELECT cp.category_id, cp.product_id FROM
category_product cp WHERE cp.product_id = $1 AND not exists ( select
'x from category_product_visible cpv where cpv.product_id =
cp.product_id an cpv.category_id = cp.category_id);" PL/pgSQL
function "insert_cpv" line 3 at SQL statement PL/pgSQL function
"process_mp_change" line 15 at assignment PL/pgSQL function
"process_pending_changes" line 13 at assignment
The insert_cpv(...) function and table definitions follow. I can
provide any other information required.
CREATE FUNCTION insert_cpv(
my_product_id int
) RETURNS boolean AS $$
DECLARE
BEGIN
INSERT INTO category_product_visible (category_id, product_id)
SELECT cp.category_id, cp.product_id
FROM category_product cp
WHERE cp.product_id = $1 AND
not exists (
select 'x'
from category_product_visible cpv
where cpv.product_id = cp.product_id and
cpv.category_id = cp.category_id
);
return found;
END;
$$ LANGUAGE plpgSQL;
\d category_product
Table "public.category_product"
Column | Type | Modifiers
-------------+---------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"x_category_product_pk" PRIMARY KEY, btree (category_id,
product_id)
"x_category_product__category_id_fk_idx" btree (category_id)
"x_category_product__product_id_fk_idx" btree (product_id)
Foreign-key constraints:
"x_category_product_category_fk" FOREIGN KEY (category_id)
REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED
"x_category_product_product_fk" FOREIGN KEY (product_id)
REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED
Triggers:
_ssprod_replication_cluster_logtrigger_17 AFTER INSERT OR DELETE
OR UPDATE ON category_product FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'17', 'kk')
category_product_trigger BEFORE INSERT OR DELETE ON
category_product FOR EACH ROW EXECUTE PROCEDURE
category_product_trigger()
\d category_product_visible
Table "public.category_product_visible"
Column | Type | Modifiers
---------------------+------------------------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"category_product_visible_pkey" PRIMARY KEY, btree (category_id,
product_id)
"category_product_visible__product_id_fk_idx" btree (product_id)
Triggers:
_ssprod_replication_cluster_logtrigger_18 AFTER INSERT OR DELETE
OR UPDATE ON category_product_visible FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'18', 'kvvvvvkvvvvvv')
---------------------------(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
|
| | 3 answer | Add comment |
|
|
