Re: [HACKERS] Problems with extended-Query logging code
On Wed, 6 Sep 2006, Tom Lane wrote: I thought somebody had mentioned that integers were also sent in binary in the latest driver code? Can't find the archive entry right now though. Using the fastpath protocol integers and oids are sent as binary. I don't know if that is related to this or logged at all. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] cygwin initdb failure
The cygwin buildfarm is failing: http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=eeldt=2006-07-21%2015:00:01 initdb failed Examine ./log/initdb.log for the reason. initdb.log is empty, but a dialog box pops up saying: initdb.exe - Unable To Locate DLL The dynamic link library cygpq.dll could not be found in the specified path [where I checked out the source]\src\test\regress\tmp_check\install\home\mingfarm\tmp\inst\bin; .;C:\WINNT\system; C:\WINNT; C:\cygwin\usr\local\bin; C:\cygwin\bin; C:\cygwin\usr\usr\X11R6\bin; [The rest of my %PATH% environment setting] Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Windows buildfarm support, or lack of it
On Sun, 16 Jul 2006, Tom Lane wrote: [windows buildfarm machines run irregularly] For my part the difficulty is scheduling. As a primarily unix user I understand cron, but have no idea what the windows equivalent is. For my cygwin buildfarm member I setup cron, but the make step failed for every build for unknown reasons while succeeding if not run from cron. That further demotivated me from scheduling mingw builds. Perhaps snake's maintainer could share his configuration? Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Three weeks left until feature freeze
On Thu, 13 Jul 2006, Tom Lane wrote: The people who think PL/Java is an essential checklist item undoubtedly also think JDBC is an essential checklist item, but I'm not seeing any groundswell of support for putting JDBC back into core. Instead we expect packagers (like the RPM set) to make JDBC available alongside the core postgres packages. That's how PL/Java ought to be handled, too, IMHO. The fact that the JDBC driver requires no compilation for anyone on any platform is one reason for that. Anyone can visit the website and be working within minutes with no understanding of the build environment or installation. You drop the provided JAR file in your classpath and you are done. The same cannot be said for pl/java. Yes, it would be good if there were packages for it, but it's very unlikely that pl/java will be able to maintain up to date binary packages for every platform. Another benefit the JDBC project enjoys is a website (jdbc.postgresql.org) and mailing list (pgsql-jdbc@postgresql.org) that are sponsored directly by the project instead of off on a more obscure site (gborg/pgfoundry). Having the archives with the core projects certainly makes them easier to find. Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] buildfarm future
Andrew Dunstan wrote: I intended to post this anyway as a followup to the conference session, but I have been spurred to do it nowby the Pl/J(ava) discussion. Another thought I had today was the ability to attach notes to the web site. For example a recent commit broke the 7.3 branch for everybody. I took a look at it and sent a patch for it to -patches, but it would be nice to flag that somehow in the web interface so that others would know it is being worked on and not to waste time on it. You could attach notes at either the branch or machine-branch level and clear them when a clean build came through. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Three weeks left until feature freeze
On Thu, 13 Jul 2006, D'Arcy J.M. Cain wrote: Wouldn't that be the job of the platform providers? Certainly I would expect NetBSD to make it available as a package, both source and binary, on every platform they support as they do for the thousands of other packages they deal with. Well NetBSD doesn't offer pl/java now so I'm not sure what point you are trying to make. Sure it would be nice if every OS provided every version of every package, but when they don't what are you going to do about it? Provide a complete package or require manual assembly? Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] include compile problems
On Fri, 14 Jul 2006, Tom Lane wrote: I think that Bruce thought that math.h defines INT_MAX and related symbols, whereas the spec is perfectly clear that they're in limits.h. However, that's where they are on my machines, and yet CVS tip is not failing for me. I'm not clear why not... What platform are you using? I tested on Solaris 9 with Sun compiler and Debian unstable with gcc-4.1.1. Debian only failed on contrib while Solaris failed on both core and contrib. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Going for all green buildfarm results
Original Message From: Tom Lane [EMAIL PROTECTED] kudu HEAD: one-time failure 6/1/06 in statement_timeout test, never seen before. Is it possible system was under enough load that the 1-second timeout fired before control reached the exception block? The load here was no different than any other day. As to whether it's a real issue or not I have no idea. It is a virtual machine that is subject to the load on other VMs, but none of them were scheduled to do anything at the time. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Solaris ASM problem
On Fri, 28 Apr 2006, Theo Schlossnagle wrote: What platform is that? (OS rev, architecture and word size)? I tested the changes I submitted on Solaris 10 amd64. $ uname -a SunOS albert 5.9 Generic_112234-03 i86pc i386 i86pc $ cc -V cc: Sun WorkShop 6 update 2 C 5.3 Patch 111680-09 2003/05/18 Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Solaris ASM problem
On Fri, 28 Apr 2006, Theo Schlossnagle wrote: The file that uses the spinlocks: /src/backend/storage/lmgr/s_lock.c can be compiled standalone with -DS_LOCK_TEST To get the test to compile I had to link in tas.o as the attached patch shows. Unfortunately this doesn't work for platforms that don't have a tas.o, bailing out with file not found. Perhaps it's not important to fix this test, but I thought I'd mention it. Anyway the test exits with Stuck spinlock (80618e9) detected at ./s_lock.c:355. on a linux gcc build this exits with Stuck spinlock (0x5013ad) detected at ./s_lock.c:402. Kris Jurka? src/backend/storage/lmgr/s_lock_test Index: src/backend/storage/lmgr/Makefile === RCS file: /projects/cvsroot/pgsql/src/backend/storage/lmgr/Makefile,v retrieving revision 1.20 diff -c -r1.20 Makefile *** src/backend/storage/lmgr/Makefile 7 Oct 2004 00:08:04 - 1.20 --- src/backend/storage/lmgr/Makefile 28 Apr 2006 21:13:52 - *** *** 21,26 --- 21,27 s_lock_test: s_lock.c $(top_builddir)/src/port/libpgport.a $(CC) $(CPPFLAGS) $(CFLAGS) -DS_LOCK_TEST=1 $(srcdir)/s_lock.c \ + $(top_builddir)/src/backend/port/tas.o \ -L $(top_builddir)/src/port -lpgport -o s_lock_test check: s_lock_test ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Solaris ASM problem
On Fri, 28 Apr 2006, Theo Schlossnagle wrote: Kris Jurka wrote: Anyway the test exits with Stuck spinlock (80618e9) detected at ./s_lock.c:355. on a linux gcc build this exits with Stuck spinlock (0x5013ad) detected at ./s_lock.c:402. This seems like a different problem, no? The patch I sent in didn't touch any of the linux assembly bits. The linux test should pass to the end without an issue right? No, that's the desired ending. It prints: S_LOCK_TEST: this will print 1000 stars and then exit with a 'stuck spinlock' message if S_LOCK() and TAS() are working. The solaris version is just getting stuck before at another point before the expected stuck point. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 2x compile warning
On Mon, 24 Apr 2006, Gevik Babakhani wrote: I noticed the following compile warnings. Perhaps someone is interested to know about them. Also I was testing a gcc 4.2 snapshot (20060419) and it has a whole lot of warnings stemming from heap_getattr's isnull check: aclchk.c:791: warning: the address of 'isNull', will always evaluate as 'true' aclDatum = heap_getattr(tuple, Anum_pg_database_datacl, RelationGetDescr(relation), isNull); #define heap_getattr(tup, attnum, tupleDesc, isnull) \ ( \ AssertMacro((tup) != NULL), \ ( \ ((attnum) 0) ? \ ( \ ((attnum) (int) (tup)-t_data-t_natts) ? \ ( \ ((isnull) ? (*(isnull) = true) : (dummyret)NULL), \ (Datum)NULL \ ) \ : \ fastgetattr((tup), (attnum), (tupleDesc), (isnull)) \ ) \ : \ heap_getsysattr((tup), (attnum), (tupleDesc), (isnull)) \ ) \ ) Removing the check for (isnull) before (*(isnull) = true) as in the attached patch passes make check, but I have not looked at every heap_getattr call site to ensure it's passing a valid isnull pointer. Kris JurkaIndex: src/include/access/heapam.h === RCS file: /projects/cvsroot/pgsql/src/include/access/heapam.h,v retrieving revision 1.107 diff -c -r1.107 heapam.h *** src/include/access/heapam.h 24 Mar 2006 04:32:13 - 1.107 --- src/include/access/heapam.h 24 Apr 2006 18:12:16 - *** *** 45,51 #define fastgetattr(tup, attnum, tupleDesc, isnull) \ ( \ AssertMacro((attnum) 0), \ ! ((isnull) ? (*(isnull) = false) : (dummyret)NULL), \ HeapTupleNoNulls(tup) ? \ ( \ (tupleDesc)-attrs[(attnum)-1]-attcacheoff = 0 ? \ --- 45,51 #define fastgetattr(tup, attnum, tupleDesc, isnull) \ ( \ AssertMacro((attnum) 0), \ ! (*(isnull) = false), \ HeapTupleNoNulls(tup) ? \ ( \ (tupleDesc)-attrs[(attnum)-1]-attcacheoff = 0 ? \ *** *** 61,67 ( \ att_isnull((attnum)-1, (tup)-t_data-t_bits) ? \ ( \ ! ((isnull) ? (*(isnull) = true) : (dummyret)NULL), \ (Datum)NULL \ ) \ : \ --- 61,67 ( \ att_isnull((attnum)-1, (tup)-t_data-t_bits) ? \ ( \ ! (*(isnull) = true), \ (Datum)NULL \ ) \ : \ *** *** 100,106
Re: [HACKERS] 2x compile warning
On Mon, 24 Apr 2006, Martijn van Oosterhout wrote: Perhaps someone could check if changing the test explicitly check against NULL: ((attnum) (int) (tup)-t_data-t_natts) ? \ ( \ (((isnull) != NULL)? (*(isnull) = true) : (dummyret)NULL), \ (Datum)NULL \ removes the warning. It seems silly for the GCC people to add warnings for this kind of stuff without a simple way to bypass it... Yes, this coding removes the warning. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 2x compile warning
On Mon, 24 Apr 2006, Bruce Momjian wrote: Great, fix attached and applied. You also need to change lines 48 and 64 of heapam.h to use the same coding. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for SYNONYMS
On Thu, 9 Mar 2006, Jonah H. Harris wrote: 2) For my comprehension, what's the difference between a SYNONYM and a single-object (possibly updatable) view? Not a whole lot actually. If we had updateable views, I'd suggest that people change their create synonym syntax to create view. One key difference would be that synonyms track schema updates, like adding a column, to the referenced object that a view would not. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upcoming re-releases
On Wed, 8 Feb 2006, Tom Lane wrote: The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? I still think this should be applied to back branches. The patches queue is really quite a bit behind. http://archives.postgresql.org/pgsql-hackers/2006-01/msg00175.php Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upcoming re-releases
Devrim GUNDUZ wrote: So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. I think the other important thing to consider is that this patch went into debian's unstable branch, not stable. Kris Jurka ---(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
[HACKERS] latin1 unicode conversion errors
Why is latin1 special in its conversion from unconvertible unicode data?
Other latin character sets add a warning, but latin1 errors out.
jurka=# create database utf8 with encoding ='utf8';
CREATE DATABASE
jurka=# \c utf8
You are now connected to database utf8.
utf8=# create table t(a text);
CREATE TABLE
utf8=# insert into t values ('\346\231\243');
INSERT 0 1
utf8=# set client_encoding = 'latin2';
SET
utf8=# select * from t;
WARNING: ignoring unconvertible UTF-8 character 0xe699a3
a
---
(1 row)
utf8=# set client_encoding = 'latin1';
SET
utf8=# select * from t;
ERROR: could not convert UTF8 character 0x00e6 to ISO8859-1
Kris Jurka
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Heads up: upcoming back-branch re-releases
On Wed, 4 Jan 2006, Tom Lane wrote: Any last-minute issues out there? Not a last minute issue or a big deal, but I see no reason for this patch not to be applied to back branches. http://archives.postgresql.org/pgsql-patches/2005-12/msg00128.php It fixes this problem: http://archives.postgresql.org/pgsql-bugs/2005-12/msg00048.php Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote:
I have a connection that is created with prepareThreshold=1 in the
connection string. I use a prepared statement that I fill with
addbatch() and that I execute with executeBatch() (for full source: see
application.java attachment).
LOG: statement: PREPARE S_2 AS update prototype.customers set title=
$1 , defaultcurrency=$2, defaulttermsofdelivery=$3 ,
defaulttermsofpayment=$4 where customernumber=$5
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 773.841 ms
LOG: statement: BIND
LOG: statement: EXECUTE unnamed [PREPARE: update
prototype.customers set title=$1 , defaultcurrency=$2, defaultter
msofdelivery=$3, defaulttermsofpayment=$4 where customernumber=$5]
LOG: duration: 377.981 ms
Does this output mean that the prepared statement with the name S_2 is
not used in the following 2 EXECUTE statements and that therefor each
execute statement is planned again?
No, this actually looks like a bug in the server side logging. The JDBC
driver issues:
FE= Parse(stmt=S_1,query=INSERT INTO tt VALUES ($1),oids={23})
FE= Bind(stmt=S_1,portal=null,$1=1)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Bind(stmt=S_1,portal=null,$1=2)
FE= Describe(portal=null)
FE= Execute(portal=null,limit=1)
FE= Sync
I assume the server side logging code is getting confused because it uses
a named statement, but the unnamed portal.
Kris Jurka
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [JDBC] prepareThreshold=1 and statement.executeBatch() ??
On Sun, 13 Nov 2005, Joost Kraaijeveld wrote: You have tested this with an insert statement. Could you do that also for an update (or try to tell me how I can do that)? I am getting very strange differences in running time between inserts and update ( 26 inserts are measured in seconds, 26 updates over 1 column in the same table are measured in minutes) Certainly there are different costs associated with inserts vs. updates. An insert just needs to jam a new row in somewhere, but the update must first search the table to find the existing row. If you do not have an index this will take a while. If you update the same row every time this will also take a while because you'll be creating a whole bunch of dead rows in the table. Some more information on the table and type of updates could bring the update cost down, but comparing insert vs. update times is not a reasonable thing to do. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GSSAPI or Kerberos authentication problems
Mike Warnecke wrote: Does the postgresql-jdbc driver support Kerberos/GSSAPI authentication? If not, what is required to get it done? Doing a little further reading and testing shows that the server would need to be extended to provide GSSAPI support even though the underlying authentication would be done using Kerberos. Java has Kerberos support, but not the ability to speak it directly, only through the GSSAPI interface. Phil Dodderidge claimed to have this up and working quite some time ago, perhaps he can share some more info... http://archives.postgresql.org/pgsql-hackers/2002-04/msg01461.php Although I'm not sure people want GSSAPI support in the backend given the comments on adding SASL support here: http://archives.postgresql.org/pgsql-hackers/2002-05/msg00760.php Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Intermittent stats test failures on buildfarm
On Tue, 30 Aug 2005, Tom Lane wrote: What we are left with turns out to be multiple occurrences of the first pathology on exactly three buildfarm members: ferret Cygwin kuduSolaris 9, x86 dragonfly Solaris 9, x86 So what to make of this? Dunno, but it is clearly a very platform-specific behavior. Anyone see a connection between Cygwin and Solaris? One thing to note about kudu and dragonfly is that they are running under vmware. This, combined with cygwin's reputation, makes me suspect that the connection is that they are both struggling under load. Although canary (NetBSD 1.6 x86) is setup in the same fashion and has shown no such failures. I'm also in the process of moving, so I put this machine in a box last night and it won't be up and running for a week or two. I do have very similar copies of the OS image running on other machines if you'd like me to test something specific. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO list comments
On Wed, 24 Aug 2005, Tom Lane wrote: * Fetch heap pages matching index entries in sequential order Rather than randomly accessing heap pages based on index entries, mark heap pages needing access in a bitmap and do the lookups in sequential order. Another method would be to sort heap ctids matching the index before accessing the heap rows. This is done (see bitmap index scans). Will the optimizer ever choose this plan when dealing with only one index? Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Simplifying wal_sync_method
On Mon, 8 Aug 2005, Andrew Dunstan wrote: So the short answer is possibly You build the tests and we'll run 'em. Automated performance testing seems like a bad idea for the buildfarm. Consider in my particular case I've got three members that all happen to be running in virtual machines on the same host. What virtualization does for performance and what happens when all three members are running at the same time renders any results beyond useless. Certainly soliciting the [EMAIL PROTECTED] list is good idea, but I don't think automating this testing is a good idea without more knowledge of the machines and their other workloads. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Buildfarm issues on specific machines
On Sun, 17 Jul 2005, Tom Lane wrote:
The short answer is that you should install flex 2.5.4, or else forget
about testing the 7.2 branch. I don't think anyone will be very
interested in making 7.2 work with flex 2.5.31.
Actually there are problems in the 7.3 branch as well in the cube,
tsearch, and seg modules. Here are some patches for the 7.2 version check
and 7.2 and 7.3 tsearch code. I'll work on getting cube and seg up to
speed as well if people agree we want these fixes.
Kris JurkaIndex: config/programs.m4
===
RCS file: /projects/cvsroot/pgsql/config/programs.m4,v
retrieving revision 1.7
diff -c -r1.7 programs.m4
*** config/programs.m4 28 Aug 2001 14:59:11 - 1.7
--- config/programs.m4 16 Jul 2005 18:11:24 -
***
*** 26,32
then
echo '%%' conftest.l
if $pgac_candidate -t conftest.l 2/dev/null | grep FLEX_SCANNER
/dev/null 21; then
! if $pgac_candidate --version | grep '2\.5\.3' /dev/null 21; then
pgac_broken_flex=$pgac_candidate
continue
fi
--- 26,32
then
echo '%%' conftest.l
if $pgac_candidate -t conftest.l 2/dev/null | grep FLEX_SCANNER
/dev/null 21; then
! if $pgac_candidate --version | grep '2\.5\.3$' /dev/null 21; then
pgac_broken_flex=$pgac_candidate
continue
fi
? contrib/tsearch/libtsearch.so.0.0
? contrib/tsearch/parser.c
? contrib/tsearch/results
? contrib/tsearch/tsearch.sql
Index: contrib/tsearch/parser.h
===
RCS file: /projects/cvsroot/pgsql/contrib/tsearch/Attic/parser.h,v
retrieving revision 1.3
diff -c -r1.3 parser.h
*** contrib/tsearch/parser.h28 Oct 2001 06:25:41 - 1.3
--- contrib/tsearch/parser.h16 Jul 2005 18:11:53 -
***
*** 5,11
int tokenlen;
int tsearch_yylex(void);
void start_parse_str(char *, int);
- void start_parse_fh(FILE *, int);
void end_parse(void);
#endif
--- 5,10
Index: contrib/tsearch/parser.l
===
RCS file: /projects/cvsroot/pgsql/contrib/tsearch/Attic/parser.l,v
retrieving revision 1.1
diff -c -r1.1 parser.l
*** contrib/tsearch/parser.l12 Oct 2001 23:19:09 - 1.1
--- contrib/tsearch/parser.l16 Jul 2005 18:11:53 -
***
*** 1,56
%{
#include string.h
#include deflex.h
#include parser.h
- /* postgres allocation function */
- #include postgres.h
- #define free pfree
- #define mallocpalloc
- #define realloc repalloc
-
- #ifdef strdup
- #undef strdup
- #endif
- #define strduppstrdup
-
-
char *token = NULL; /* pointer to token */
char *s = NULL; /* for returning full defis-word */
YY_BUFFER_STATE buf = NULL; /* buffer to parse; it need for parse from string
*/
- int lrlimit = -1; /* for limiting read from filehandle ( -1 - unlimited
read ) */
- int bytestoread = 0; /* for limiting read from filehandle */
-
- /* redefine macro for read limited length */
- #define YY_INPUT(buf,result,max_size) \
- if ( yy_current_buffer-yy_is_interactive ) { \
- int c = '*', n; \
- for ( n = 0; n max_size \
- (c = getc( tsearch_yyin )) != EOF c != '\n';
++n ) \
- buf[n] = (char) c; \
- if ( c == '\n' ) \
- buf[n++] = (char) c; \
- if ( c == EOF ferror( tsearch_yyin ) ) \
- YY_FATAL_ERROR( input in flex scanner failed ); \
- result = n; \
- } else { \
- if ( lrlimit == 0 ) \
- result=YY_NULL; \
- else { \
- if ( lrlimit0 ) { \
- bytestoread = ( lrlimit max_size ) ? max_size
: lrlimit; \
- lrlimit -= bytestoread; \
- } else \
- bytestoread = max_size; \
- if ( ((result = fread( buf, 1, bytestoread,
tsearch_yyin )) == 0) \
-ferror( tsearch_yyin ) ) \
- YY_FATAL_ERROR( input in flex scanner failed
); \
- } \
- }
-
- #define YY_NO_UNPUT
%}
/* parser's state for parsing defis-word */
--- 1,14
%{
+ #include postgres.h
#include string.h
#include deflex.h
#include parser.h
char *token = NULL; /* pointer to token */
char *s = NULL; /* for returning full defis-word */
YY_BUFFER_STATE buf = NULL; /* buffer to parse; it need for parse from string
*/
%}
/* parser's state for parsing defis-word */
***
*** 308,320
BEGIN INITIAL;
}
- /* start
Re: [HACKERS] pg_get_prepared?
On Sat, 16 Jul 2005, Christopher Kings-Lynne wrote: This has been covered before, but to reiterate: why would you need this? Any application worth its salt should be tracking which statements it has already prepared (after all, they cannot span connections). Seems a waste of resources to make a separate call to the database for information you should already know. Erm, websites...use persistent connections...you have no idea if you're dealing with a new connection or a reused one, and if the statement is prepared or not. I think the point is that this is the driver's problem, not the applications. If you are using SQL level PREPARE/EXECUTE in your code that's your problem, but if you are using an api like: $stmt = $conn-prepare(SELECT * FROM tab WHERE x = ?); $result = $stmt-execute(71); Then the driver itself should know if the above query has been prepared previously and further what type it has been prepared for so that it can cast the 71 or prepare a new statement. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Sat, 16 Jul 2005, Tom Lane wrote: Marko Kreen marko@l-t.ee writes: I googled a bit and found two suggestions: 1. http://curl.haxx.se/mail/lib-2002-01/0092.html (Use -mimpure-text on linking line) This sure seems like a crude band-aid rather than an actual solution. The bug as I see it is that gcc is choosing to link libz.a rather than libz.so --- why is that happening? The link line says -L/usr/local/lib -lz and libz.a is in /usr/local/lib while libz.so is in /usr/lib. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Sat, 16 Jul 2005, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: The link line says -L/usr/local/lib -lz and libz.a is in /usr/local/lib while libz.so is in /usr/lib. Well, that is a flat-out configuration error on the local sysadmin's part. I can't think of any good reason for the .so and .a versions of a library to live in different places. We certainly shouldn't hack our build process to build deliberately-inefficient object files in order to accommodate such a setup. Well the OS only came with the shared library and I needed the static one for some reason, so I installed it alone under /usr/local. This works fine with Sun's cc and Marko's research indicates that this will also work fine using GNU ld instead of Sun's ld. This is certainly an unusual thing to do, but I don't believe it is a flat-out configuration error, consider what would happen if the shared library didn't exist at all and only a static version were available. Until this recent batch of pgcrypto changes everything built fine. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Fri, 15 Jul 2005, Marko Kreen wrote: [buildfarm machine dragonfly] On Tue, Jul 12, 2005 at 01:06:46PM -0500, Kris Jurka wrote: Well the buildfarm machine kudu is actually the same machine just building with the Sun compiler and it works fine. It links all of libz.a into libpgcrypto.so while gcc refuses to. I googled a bit and found two suggestions: 1. http://curl.haxx.se/mail/lib-2002-01/0092.html (Use -mimpure-text on linking line) The attached patch does #1. Could you try it and see if it fixes it? This patch works, pgcrypto links and passes its installcheck test now. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
On Tue, 12 Jul 2005, Marko Kreen wrote: On Mon, Jul 11, 2005 at 04:47:18PM -0700, Kris Jurka wrote: Marko Kreen wrote: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dragonflydt=2005-07-11%2003:30:04 Linking problem with zlib on Solaris 9/x86. I am clueless about this. I can anyone look into it? It appears to be finding the static /usr/local/lib/libz.a instead of the dynamic /usr/lib/libz.so. So it is a local problem? I see that the configure line contains: --with-includes=/usr/local/include --with-libs=/usr/local/lib explicitly. Well the buildfarm machine kudu is actually the same machine just building with the Sun compiler and it works fine. It links all of libz.a into libpgcrypto.so while gcc refuses to. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 4 pgcrypto regressions failures - 1 unsolved
Marko Kreen wrote: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dragonflydt=2005-07-11%2003:30:04 Linking problem with zlib on Solaris 9/x86. I am clueless about this. I can anyone look into it? It appears to be finding the static /usr/local/lib/libz.a instead of the dynamic /usr/lib/libz.so. Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposed TODO: --encoding option for pg_dump
On Tue, 28 Jun 2005, Josh Berkus wrote: The TODO: add an --encoding=[encoding name] option to pg_dump. This would set client_encoding for pg_dump's session(s). What about just using the PGCLIENTENCODING environment variable? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Cost of XLogInsert CRC calculations
On Mon, 16 May 2005, Tom Lane wrote: I did some experimentation and concluded that gcc is screwing up big-time on optimizing the CRC64 code for 32-bit Intel. It does much better on every other architecture though. Anyone want to try it with non-gcc compilers? Solaris 9 x86 - Sun Workshop 6 update 2 C 5.3, gcc 3.2.3 gcc -O1 crctest.c .251422 gcc -O3 crctest.c .240223 gcc -O1 crctest64.c.281369 gcc -O3 crctest64.c.631290 cc -O crctest.c.268905 cc -fast crctest.c .242429 cc -O crctest64.c .283278 cc -fast crctest64.c .255560 Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Feature freeze date for 8.1
On Mon, 2 May 2005, Jim C. Nasby wrote: FWIW, I've found myself wishing I could set statement_timeout on a per user or per group basis. Likewise for log_min_duration_statement. See ALTER USER ... SET Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] Increased company involvement
On Sat, 30 Apr 2005, Nicolai Petri (lists) wrote: We also use PostgreSQL as our primary db so it would be more than likely that we would donate money for something similar with postgresql if either : a) we can direct the money at one or more specific tasks or b) the tasks founded will be related to core postgresql features e.g. generel performance or other benefits that fits all. The problem is organization. Who decides who gets what money? What about features that are paid for and worked on and not accepted into the community codebase? This was something I hoped the PostgreSQL Foundation http://thepostgresqlfoundation.org/ would step in and do, but we seem much more focused on advocacy efforts rather than developemnt ones. Kris Jurka ---(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] Increased company involvement
On Fri, 29 Apr 2005, Andrew Dunstan wrote: One thing that might help is a more open sponsorship clearing house. Example (not meant as a bid, but just to illustrate): the JDBC driver needs a scanner overhaul - it breaks on dollar quoting and a bunch of other stuff. I could do that work (as could others, of course) but I don't have time, unless someone buys some of my professional time. Someone might want to do just that, but how would they find me? I don't think this is a big issue. I don't know of any companies who were desperate for a feature and willing to throw money at the problem who couldn't find a developer to take them up on it. Right now this seems to be a kind of behind the scenes operation that relies heavily on knowing the right people, but I think most of our sponsor contact points are able to point sponsors to the right people. Could this process be more open? Depends on how the sponsor wants to handle it, they probably don't just want to throw the task out there and see who comes calling, they want an assurance from someone they trust that the chosen developer is capable. One thing that definitely would be nice would be to be able to combine funds from various sponsors for various features. Alone a company can't spring for it, but by pooling resources it could get done. This is a lot tougher to coordinate and unless there is a complete spec in place different sponsors will pull in different directions. Other bounty type schemes don't seem to produce results, largely from a lack of cash. (Here's $500 for two weeks of work). Anyone care to shed some light on how it works now? Kris Jurka ---(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: [HACKERS] can plpgsql returns more flexibe value ?
On Sun, 23 Jan 2005, Arnold.Zhu wrote: Can I use DataAdapter.Fill() with refcursor. :-( I have no idea what DataAdapter is, you will need to check your client interface for support (and this probably isn't the place to do that), but it's certainly possible. See for example The world's most advanced PostgreSQL client interface http://jdbc.postgresql.org/documentation/80/ch06.html#callproc-resultset-refcursor Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] can plpgsql returns more flexibe value ?
On Sat, 22 Jan 2005, Arnold.Zhu wrote: Yeah, you just make your function return 'SETOF record' and specify the types when you do the select: select * from func() as (a int, b text); This is not a good idea when I use C# to program, I want to Fill the resultset directly into Dataset, this method will should use more sql to get data, it lose function's convenience like stored procedure. Perhaps you should look into the refcursor type, which will allow you to return anything you want without specifying it. You can't do things like a join between to refcursor outputs, but it does allow for more return flexibility. Kris Jurka ---(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] Time span conversion function
On Sat, 15 Jan 2005, Brendan Jurd wrote: SELECT time_span( 'minute', now(), interval '10:43:55' ); 643 The timestamp argument to this version of the function seems completely irrelevent. Kris Jurka ---(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] Top-k optimizations?
On Thu, 13 Jan 2005, David Fetter wrote: 3. What kinds of top-k optimizations might (eventually) be included in PostgreSQL? See the TODO item: Allow ORDER BY ... LIMIT 1 to select high/low value without sort or index using a sequential scan for highest/lowest values If only one value is needed, there is no need to sort the entire table. Instead a sequential scan could get the matching value. There is some discussion of this on the -general list here: http://groups-beta.google.com/group/comp.databases.postgresql.general/messages/08c615cc2cbdf143,fe626a7cc9021d12,4f1d0575be60c26f,5c44463d8ef0e1ef,ceff42f0dae09272,dc9da98adcb6142c,7f34133e99b38825,28b43c5e79924da6,98be76099ea6513f,5d3f19a69e3b5a93?thread_id=7d35d3eb00ffd0e8mode=threadnoheader=1q=oleg+limit+sort#doc_7f34133e99b38825 Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgresql-7.4.5
On Mon, 6 Dec 2004, ElayaRaja S wrote: I am using postgresql-7.4.5. I nee to use the jdbc connection So i downloaded 4 versions of driver( pg74.215.jdbc1.jar, pg74.215.jdbc2.jar, pg74.215.jdbc2ee.jar, pg74.215.jdbc3.jar). I am uanble to connect it. Please let me know which version of driver i have to use for postgresql-7.4.5 ? This question is inappropriate for the -hackers list. Please keep it on -interfaces or the more appropriate -jdbc list. As mentioned on the page you downloaded these drivers from, you should use the driver version that matches your JVM. Without any information on what your actual problem is, no one will be able to help you. Kris Jurka ---(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] somebody working on: Prevent default re-use of sysids
On Fri, 3 Dec 2004, Tom Lane wrote: 2) Prevent dropping user that still owns objects, or auto-drop the objects No one has any idea how to do this reasonably --- the problem is you have no visibility into databases other than the one you're connected to, so you can't tell what the user owns in other databases. What about Alvaro's shared dependencies work: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] somebody working on: Prevent default re-use of sysids
On Fri, 3 Dec 2004, Bruce Momjian wrote: What about Alvaro's shared dependencies work: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php That is for allowing comments on global tables like pg_shadow and pg_database. I don't think it relates to finding if someone owns objects in another database. I quote from the first paragraph of the given link: I'm currently playing with implementing a shared dependency catalog, to keep track of objects pointing to global objects, currently users and tablespaces. So it is forbidden to drop a user that owns tables (or whatever objects) on other databases. Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] readline/libedit selection
On Thu, 2 Dec 2004, Tom Lane wrote: Reading between the lines, I wonder if your problem is that your copy of editline puts its headers in include/readline rather than include/editline? Yes, this is the actual problem readline.h is indeed in /usr/include/readline.. The missing symbols I mentioned earlier are found in both libtermcap and libcurses, so I imagine linking to curses is still the correct thing to do. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] readline/libedit selection
This recent change to readline/libedit selection isn't quite right. http://archives.postgresql.org/pgsql-committers/2004-11/msg00330.php It doesn't link in libtermcap with libedit which leads to: checking for readline.h... no configure: error: readline header not found If you have libedit already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable libedit support. And in config.log: configure:5987: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing conftest.c -ledit -lcrypt -lresolv -lcompat -lm -lutil 5 /usr/lib/libedit.so: undefined reference to `tgetnum' /usr/lib/libedit.so: undefined reference to `tgoto' /usr/lib/libedit.so: undefined reference to `tgetflag' /usr/lib/libedit.so: undefined reference to `tputs' /usr/lib/libedit.so: undefined reference to `tgetent' /usr/lib/libedit.so: undefined reference to `tgetstr' This is on x86 NetBSD 1.6. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] multiline CSV fields
On Tue, 30 Nov 2004, Greg Stark wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The advantage of having it in COPY is that it can be done serverside direct from the file system. For massive bulk loads that might be a plus, although I don't know what the protocol+socket overhead is. Actually even if you use client-side COPY it's *still* more efficient than any more general client-side alternative. The idea would be to still use COPY just from a program that did additional processing, not as direct SQL. As Tom pointed out to me a while back, neither the protocol nor libpq allow for having multiple queries in flight simultaneously. That makes it impossible to stream large quantities of data to the server efficiently. Each record requires a round-trip and context switch overhead. Multiplexing queries is different than having multiple queries in flight. You can have multiple queries on the wire now, they are just processed sequentially. In an ideal world the client should be able to queue up enough records to fill the socket buffers and allow the kernel to switch to a more batch oriented context switch mode where the server can process large numbers of records before switching back to the client. Ideally this would apply to any kind of query execution. This is possible now with the V3 protocol (and used by the JDBC driver). For an executeBatch() on a PreparedStatement, the driver sends a parse message, then any number of bind/execute pairs, but with a buffered stream nothing happens until a Sync message is sent and the stream is flushed. Then it collects the results of all of the executes. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] multiline CSV fields
On Mon, 29 Nov 2004, Andrew Dunstan wrote: Longer term I'd like to be able to have a command parameter that specifies certain fields as multiline and for those relax the line end matching restriction (and for others forbid multiline altogether). That would be a TODO for 8.1 though, along with optional special handling for first line column headings. Endlessly extending the COPY command doesn't seem like a winning proposition to me and I think if we aren't comfortable telling every user to write a script to pre/post-process the data we should instead provide a bulk loader/unloader that transforms things to our limited COPY functionality. There are all kinds of feature requests I've seen along these lines that would make COPY a million option mess if we try to support all of it directly. - skipping header rows - skipping certain data file columns - specifying date formats - ignoring duplicates - outputting an arbitrary SELECT statement Kris Jurka ---(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] IpcSemaphoreLock/Unlock and proc_exit on 7.2.6
On Sun, 14 Nov 2004, Tom Lane wrote: The comment in ProcGetNewSemIdAndNum suggests that you might be able to suppress the problem in 7.2 by using a different max_connections value. Is your current value one less than a multiple of 16, by any chance? Currently 32. It is unclear whether you think 31 is the failure case your thinking of or whether 31 might help. Kris Jurka ---(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
[HACKERS] IpcSemaphoreLock/Unlock and proc_exit on 7.2.6
I have an underpowered server running 7.2.6 that backs a website which occasionally gets hit by a bunch of traffic and starts firing off FATAL 1: Sorry, too many clients already messages. This is all as expected, but sometimes it just crashes. I had no clue what was going on until I checked the stderr log (because I had set it up to use syslog). In there I find a whole bunch of these: IpcSemaphoreLock: semop(id=-1) failed: Invalid argument IpcSemaphoreLock: semop(id=-1) failed: Invalid argument IpcSemaphoreLock: semop(id=-1) failed: Invalid argument IpcSemaphoreLock: semop(id=-1) failed: Invalid argument IpcSemaphoreUnlock: semop(id=-1) failed: Invalid argument IpcSemaphoreLock: semop(id=-1) failed: Invalid argument IpcSemaphoreUnlock: semop(id=-1) failed: Invalid argument IpcSemaphoreLock: semop(id=-1) failed: Invalid argument Looking at the source I see proc_exit as the failure path for these two functions (IpcSemaphoreLock, IpcSemaphoreUnlock). I've read the comments around the code, but must admit that I can't really follow what's going on. Could anyone shed some light on what is going on? Certainly the semId of -1 looks a little suspicious. This is on freebsd 4.5 Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [JDBC] 8.0.0beta4: copy and client_encoding
Well, lets ask -hackers... When COPYing data from a file, the file encoding is taken from the client_encoding parameter. The JDBC driver always uses UNICODE as the client_encoding and wants to prevent people from changing it by monitoring ParameterStatus messages and erroring out if it's changed. This presents a problem when you want to COPY to or from a file with a different encoding. It seems reasonable to add an ENCODING specification to the COPY command instead of relying on the somewhat unrelated client_encoding setting. Oliver Jowett also noted that copying from a file with LATIN1 data into a table whose name contained UNICODE characters could not be done. Does this seem like a reasonable thing to do? Kris Jurka On Mon, 8 Nov 2004, Barry Lind wrote: I am assuming this will get addressed in the backend in 8.1 and that would be the upgrade path. (I agree if there isn't agreement on the server side that this is appropriate for the server, then this wouldn't be the correct parameter). --Barry ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] unnest
On Fri, 5 Nov 2004, John Hansen wrote: Does anyone know how to check individual array elements for NULL values? PG_ARG_ISNULL() seems to return true if ANY array element is null; ex:: array[1,2,3,null,4,5] Arrays cannot store NULL elements, check your above statement and see that the whole thing is NULL when you introduce a NULL element: # select array[1,2,3,null,4,5]; array --- (1 row) or # select array[1,2,3,null,4,5] IS NULL; ?column? -- t (1 row) Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] making pdf of docs
On Tue, 26 Oct 2004, Dennis Bjorklund wrote: Is there something wrong that makes it impossible to build the doc as a pdf? My experience is that the latest openjade crashes. The latest jade takes about 10 days on an Athlon 1600, but I can build it in a very reasonable timeframe with an older version of jade. Well scratch that. I just checked the jade versions and they are identical, perhaps different sytlesheets or something? For now all I know is that it works on debian stable, but takes forever on unstable. I'll have to do some more investigating to try and find the real difference. Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] postgres and Jdbc 2.0
2) If I want to add these features to JDBC driver, is there anything that has to go to database itself. Generally JDBC questions are best discussed on the [EMAIL PROTECTED] list. First make sure you are working with the latest source code available from http://gborg.postgresql.org/project/pgjdbc/projdisplay.php which as Dave mentioned has implemented some of these methods already. As to whether anything needs to be added to the server, that depends on your implementation. The Blob/Clob positioning seems like it could be done more efficiently on the server side. Also the server doesn't have a Ref datatype, so you really can't implement get/setRef without it. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [JDBC] V3 protocol + DECLARE problems
On Wed, 21 Jul 2004, Oliver Jowett wrote: It's going to be fun using anything more than very basic cursors via the V3 protocol in the JDBC driver. DECLARE does not work with parameters passed via a Parse/Bind combination -- which is how we currently always pass parameters when talking V3. I'm reluctant to have two implementations of the parameter logic for V3 (one that does direct substitution, one that uses Bind) since that's extra unnecessary code and a recipe for inconsistent behaviour. I see where you are going in the WITH HOLD case, I don't see how this extends to scrollable cursors without other major changes as the Execute protocol message assumes forward only operation. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] nested-xacts cursors (was Re: Performance with new
On Thu, 1 Jul 2004, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Well, my opinion is that cursors and other resources should at least be usable from a inner subtransaction in its parent -- because if that can't be done we are wasting some of the benefits, because we can't just stick everything in a subtransaction to be able to retry if it fails. It is a pity that we can't roll back FETCH or lo_close() but at least we can keep them declared/open across a subtransaction commit. AFAICS we can't allow an inner transaction to use a cursor that was declared in an outer transaction, because if the inner transaction fails then it's not just a matter of the FETCH not rolling back; the subtransaction abort will restore state in the bufmgr and other places that is simply inconsistent with the state of the cursor's plantree. This isn't just directly declared CURSORs, but also V3 protocol portals which makes it very difficult for a driver to use. An individual writing direct BEGIN, DECLARE CURSOR, and so on statements can work around the restrictions here because they know exactly what they are doing and exactly what statements are sent to the backend. From a driver perspective it has no idea what the end user's intention is and therefore cannot do things like transform a query to a cursor based statement or even use the V3 protocol because it has no idea if the caller is going to use a subtransaction at some point. Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Postgresql JDBC-Driver
On Fri, 5 Mar 2004, Rudolpho Gian-Franco Gugliotta wrote: Hi, i'm using the jdbc postgresql driver. I need to fetch the oid of a just insertet row (getGeneratedKeys() feature). That' why i ask you to provide me the source code to implement this feature.It would be glad if you tell me how and where to get these sources. The driver source is included in the main source tree for the 7.4 series, but for the upcoming 7.5 release the driver is being developed independently here: http://gborg.postgresql.org/project/pgjdbc/projdisplay.php Some discussion of the problems with implementing getGeneratedKeys is here: http://archives.postgresql.org/pgsql-jdbc/2003-12/threads.php#00193 Finally you don't necessarily need to implement getGeneratedKeys if you don't mind using some pg specific code along the lines of the following: Statement stmt = conn.createStatement(); stmt.executeUpdate(INSERT INTO t VALUES (1)); long oid = ((org.postgresql.PGStatement)stmt).getLastOID(); Kris Jurka ---(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] question about information_schema
On Tue, 18 May 2004, Dave Cramer wrote: If I do create type testint8 as (i int8) and then select typbasetype from pg_type where typname='testint8' the value is 0? You've created a complex type here, not a domain. See typtype and typrelid for this case. create domain testint8 as int8; will do what you were expecting. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] executing prepared select, missing RowDescription info
When executing a prepared select statement, the returned RowDescription protocol message does not have any information for the table oid or column position. Running the equivalent select without prepare provides this information, so I don't see why the act of preparing and executing the statement removes this valuable data. Any insight on why it isn't there or how to fix it? Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bad timestamp external representation
On Fri, 19 Mar 2004, Denis Khabas wrote: Hi everyone: I am using Postgresql 7.3.4 and found a problem inserting Timestamp objects through JDBC Prepared Statements when the time zone is set to Canada/Newfoundland (3 hours and 30 minutes from MGT). I am trying to insert new Timestamp(0L) into one of the fields. The database replies with an error message: This has been fixed in the 7.4 driver which is compatible with 7.3 servers. Try downloading it from http://jdbc.postgresql.org/download.html Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] execute command tag including affected rows count
Would it be possible to have the command completion tag for EXECUTE return the affected row count? The JDBC API says you should be able to get the affected row count for any statement and I'm working on integrating a patch that transforms regular statements to server side prepared statements, and I'm stuck on this issue. CREATE TABLE t (a int); INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); PREPARE mystatement AS DELETE FROM t; EXECUTE mystatement; would ideally return EXECUTE 2 Kris Jurka ---(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] execute command tag including affected rows count
On Sun, 21 Mar 2004, Tom Lane wrote: On 21-Mar-04, at 11:39 AM, Kris Jurka wrote: Would it be possible to have the command completion tag for EXECUTE return the affected row count? However, does this really solve Kris' problem? JDBC generally likes to think that it works with old Postgres versions; will a fix that only works in = 7.5 be good enough for them? I oversimplified the description of the current patch I'm working on, server side prepared statement support has been in the driver since the 7.3 days. So this problem already exists and has simply been unnoticed. With the current code it is difficult to enable server prepared statements and requires writing pg specific code outside of the standard JDBC API. The patch I'm reviewing makes it simple to turn on server prepared statements globally and brought this bug to my attention. I would say better late than never. Kris Jurka ---(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] grants
On Wed, 10 Mar 2004, Andreas Pflug wrote: Edgar Mares wrote: hi there i'm having troubles to find how to GRANT SELECT ON all-tables-onmydb TO specificuser this is just to give the access to specificuser to query the database and find troubles on it pgAdmin II has a tool for that (Security wizard; pgAdmin III has it on the todo-list) The problem that cannot be solved with either this or a function that loops and grants on each table is that it is not a permanent grant of what the admin had in mind. If a new table is added or an existing table is dropped and recreated, the grants must be done again. The real use of a SELECT ANY TABLE permission is ignorance of schema updates. Kris Jurka ---(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] grants
On Wed, 10 Mar 2004, Andreas Pflug wrote:
Kris Jurka wrote:
On Wed, 10 Mar 2004, Andreas Pflug wrote:
The problem that cannot be solved with either this or a function that
loops and grants on each table is that it is not a permanent grant of what
the admin had in mind. If a new table is added or an existing table is
dropped and recreated, the grants must be done again. The real use of a
SELECT ANY TABLE permission is ignorance of schema updates.
Hm, does this exist in other DBMS?
As soon as roles are implemented, there might be a default role
('public') for this. Until then, using groups solves most of the
problems (well, you certainly still need to GRANT rights to your
preferred group).
Groups help, but only if you want to GRANT to more than one user, and you
still need to do it on after schema changes. I know this is implemented
in at least Oracle, SELECT ANY TABLE is in fact the permission
name used.
Kris Jurka
---(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] session IDs
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I did think about using a cluster-wide sequence, if we can make such a thing (might also be useful for system generated UIDs too). Not a good idea IMHO. If you do that, then there will be no such thing as a purely read-only transaction, because *every* transaction will include a nextval() call. That means even read-only transactions cannot commit till the disk spins. A sequence could be used if it was created with a sufficiently large CACHE value, so a read only transaction would only have to hit the disk if it happened to be the one to hit an exhausted cache. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY from question
On Tue, 3 Feb 2004, Slavisa Garic wrote: My understanding is that COPY workes FROM 'filename' or STDIN where the last characters are '.\\n'. I tried using the copy from 'filename' and as I said NetBSD is not complaining where I get the following error on Linux machine even if permissions on the data file are 777: _pg.error: ERROR: COPY command, running in backend with effective uid 26, could not open file '/home/slavisa/.nimrod/experiments/demo/ejdata' for reading. Errno = Permission denied (13). This is probably a permissions problem at a higher level, check the permissions on the directories in the path. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] rule and JDBC
On Thu, 29 Jan 2004, Tatsuo Ishii wrote: Hi, It seems JDBC driver does not handle if a INSERT SQL statement performed by executeUpdate() is actually a SELECT, which is rewritten by the rule system. The JDBC spec says an exception should be thrown if the given SQL statement produces a ResultSet object which it does. As you note using executeQuery works, but won't if there isn't a rule. Perhaps using plain execute() would be the most appropriate thing to do. Kris Jurka ---(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] Make SHOW command subqueriable?
On Fri, 16 Jan 2004, Christopher Kings-Lynne wrote: Is this a neat idea? SELECT * FROM (SHOW ALL); So neat in fact that it has been implemented. SELECT * FROM pg_settings; Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Setting client encoding with jdbc
On 7 Nov 2003, Brad wrote:
I'm interested in changing the client encoding from within a java
program. I'm using connection pooling and need to change it depending
on the client's language preference. Is there a function I can call
which changes the client encoding. Something like
pStmt = con.prepareStatement(select
pg_set_client_encoding('LATIN5'); );
rs = pStmt.executeQuery();
I can set the client encoding from within psql by /encoding LATIN5 but
not sure how to execute something like this from java. Any help would
be greatly appreciated.
Please direct JDBC questions to the pgsql-jdbc list.
You may not set the client encoding from JDBC. The JDBC driver requires a
unicode client encoding to work properly. You may then reencode the data
in your charset of preference using the standard java APIs.
Kris Jurka
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Mapping Oracle types to PostgreSQL types
On Fri, 17 Oct 2003, Tom Lane wrote: Since varchar(n) is SQL-standard syntax, can't you simply adopt the more standard name for both databases? A long time ago Oracle made the varchar type equivalent to char and once people complained about the excess space used by short entries they came out with varchar2 which they've maintained every since valuing backwards compatability more than the sql standard. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Quick question
On Fri, 3 Oct 2003, Christopher Kings-Lynne wrote: Hi guys, If someone could help me with this, it would be cool. How do I query the catalogs to find the underlying index for a constraint? (Assuming the constraint is primary or unique) For a primary key you can do: SELECT cls.relname AS index_name FROM pg_class cls, pg_constraint con, pg_index i WHERE cls.oid = i.indexrelid AND con.conrelid = i.indrelid AND i.indisprimary AND con.conname='constraint name'; This is not possible for a unique constraint because you can have multiple unique constraints per table. So you are left trying to match pg_constraint.conkey to pg_index.indkey (for which no default operator exists), but even this can fail if you have the unlikely situation of two unique indexes covering the same columns. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PREPARE/EXECUTE across backends?
On Wed, 1 Oct 2003, Jingren Zhou wrote: Hi, From the document, it seems that PREPARE/EXECUTE works only in the same session. I am wondering whether postgres can prepare a query (save the plan) for difference backends. I am working on a project which requires executing psql -c 'query' in command line multiple times. Since the performance is critical, it would be nice to prepare the same query first to avoid being parsed/optimized each time. But psql opens a new backend each time, it looks like that PREPARE/EXECUTE doesn't work. Is there any workaround? Your real overhead here isn't from having to prepare the query each time, it's from having to start psql and open a new connection each time. Perhaps you need to rethink your design and go with something that will maintain a persistent connection. Kris Jurka ---(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] [JDBC] initdb failure (was Re: [GENERAL] sequence's plpgsql)
On Fri, 26 Sep 2003, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Perhaps we can throw a warning rather than an error, and adjust initdb to be consistent. I like the idea of reducing the newline consistency check to a warning. There is one thing we'd have to watch for though (it's already an issue but would become a bigger one): client-side COPY code had better be prepared to absorb backend Notice messages while processing COPY IN. Currently libpq doesn't read input data at all during a COPY IN loop, which means that if the COPY generates more than a few K of warning messages, the backend gets blocked on a full pipe and the whole operation locks up. I have been meaning to fix that in libpq anyway, but what other client libraries might have the same issue? Anyone know whether JDBC would need a similar fix? JDBC does not support the COPY protocol at the moment. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] observations about temporary tables and schemas
On Tue, 16 Sep 2003, Merlin Moncure wrote: I have been playing with temporary tables a little bit and noticed some interesting things. Something else I've noticed about temp tables is that you are prohibited from having a permanent table contain a foreign key reference to a temp table, but you are allowed to reference a permanent table from a temp table. The triggers don't work correctly when the table is modified by another backend: Backend 1: CREATE TABLE t1(a int PRIMARY KEY); CREATE TEMP TABLE t2(a int REFERENCES t1 ON DELETE CASCADE); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); Backend 2: DELETE FROM t1; Backend 1: SELECT * FROM t2 WHERE a NOT IN (SELECT a FROM t1); After some further investigation this problem can also be generated by two temp tables: BEGIN; CREATE TEMP TABLE t3 (a int PRIMARY KEY) ON COMMIT DELETE ROWS; CREATE TEMP TABLE t4 (a int REFERENCES t3 ON DELETE CASCADE); INSERT INTO t3 VALUES(1); INSERT INTO t4 VALUES(1); COMMIT; SELECT * FROM t4 WHERE a NOT IN (SELECT a FROM t3); Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Missing 7.3.3 cvs tag
I don't see a tag for in cvs for the 7.3.3 release. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Help needed in testing my code.
I'm guessing his problem is that he's expecting this to come out of psql and not go to the backend's stdout. Kris Jurka On Wed, 28 May 2003, Christopher Kings-Lynne wrote: Hi, This is a much better way than printf: elog(NOTICE, this is my test: %s, string); Chris - Original Message - From: Srikanth M [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 3:08 PM Subject: [HACKERS] Help needed in testing my code. Dear Sir, I want to know the compilaiton and execution of my code. Suppose if i add a test printf statement in the src/backend/tcop/postgres.c and after compilation and execution i should get that statement every time i give a query to psql. After running make I even added the executables in the /usr/bin/ directory but i couldn't see the test statement after giving a query to psql. please tell me the procedure of adding a test printf statement in postgres.c(or in any file), and compiling and running it so that i get the test statement as an output when i give the query. Sincerely Srikanth M ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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 ---(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: [HACKERS] Detecting corrupted pages earlier
On Fri, 28 Mar 2003, Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: How about adding a new option to skip corrupted pages ? I have committed changes to implement checking for damaged page headers, along the lines of last month's discussion. It includes a GUC variable to control the response as suggested by Hiroshi. Is zeroing the pages the only / best option? Hiroshi suggested skipping the pages as I recall. Is there any chance of recovering data from a trashed page manually? If so perhaps the GUC variable should allow three options: error, zero, and skip. Kris Jurka ---(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] Open 7.4 features
On Wed, 19 Mar 2003, Bruce Momjian wrote: Here are a list of features that might be in 7.4. I know there are several people involved in each of these items. Tablespaces I haven't seen any proposal for tablespaces recently. Is this something that is actively being worked on? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_get_constraintdef
On Mon, Jan 13, 2003 at 11:59:33AM +0800, Christopher Kings-Lynne wrote: I will, but unfortunately the damage has already been done...since I have to support 7.3 anyway, fixing the above problem will actually make my life harder, not easier... Another issue to consider is that when a dump from 7.2 is loaded into 7.3 no foreign keys are listed in pg_constraint, so some backwards compatibility will be required because even if the 7.3 server supports this functionality it does not mean it is being used. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG 7.3: Query Meta Data with the JDBC-driver
On Mon, 2 Dec 2002, Henner Zeller wrote: Hi, Just compiled the 7.3 branch from source and made some tests using the JDBC driver coming with it. I did some tests with the henplus JDBC-shell and noticed some problems quering the database meta data: o the foreign key name is 'wierd' --- DatabaseMetaData meta = conn.getMetaData(); ResultSet rset = meta.getImportedKeys(null, null, 'bar'); rset.next(); String foreignKeyName=rset.getString(12); --- results in names that seemingly contains the internal representation: fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 (see below for an example) In September, I proposed a patch to change this to the foreign key name. This was rejected because = 7.2 servers don't enforce unique constraint names per table, so it was decided to keep the above behavior to guaranteee a unique name. I think this should be changed. See the original discussion at... http://archives.postgresql.org/pgsql-patches/2002-09/msg00150.php o It takes _ages_ to retrieve the meta data. While doing a 'describe', the postmaster process runs on 100% CPU. And: it takes extremly different amounts of time. Executing the describe-command below, it took (56.285 sec, 12.799 sec, 5 min 13.468, 12.203 sec) to execute the same command. This look very like a missing or random break-condition somewhere in a loop ? The query to generate the ResultSet is a monster and has enough tables involved to enable the genetic query optimizer which is neither consistent nor particularly good. I was able to solve this using an ANALYZE, but the long term solution is to state the desired join order explicitly in the query using JOIN statements. I will submit a patch to this effect later this week. o this might be a minor point, but annoying as well: the columns are not ordered in the sequence the're created in the table. I have already submitted a patch to fix this because of a previous complaint. http://fts.postgresql.org/db/mw/msg.html?mid=1359758 If this cannot be reproduced, I'll try to track this down, but probably this seems simple to you (BTW: doing this with the current 7.4development CVS on my machine, this results in a segmentation fault on the postmaster side - this indicates, that there indeed is a problem ..) Will investigate as well. ===8== pg create table foo (id int4 constraint pk_foo primary key); pg create table bar ( id int4 constraint pk_bar primary key, fooref int4 constraint fk_foo_id references foo(id) ); pg describe bar catalog: postgres '-' : referencing +-+--+-+++ column | type | null | default | pk | fk | +-+--+-+++ fooref | int4(4) | YES | [NULL] || fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 | | | | || - foo(id) | id | int4(4) | NO | [NULL] | pk_bar | | +-+--+-+++ 56.285 sec === ciao, -hen BTW: henplus JDBC-Shell can be found http://henplus.sourceforge.net/ ---(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 ---(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] xBSD shmem doc deficiency
Apparently only some settings are adjustable. root@dev:~# uname -smr FreeBSD 4.2-RELEASE i386 root@dev:~# sysctl -a | grep kern.ipc.semm kern.ipc.semmap: 30 kern.ipc.semmni: 10 kern.ipc.semmns: 60 kern.ipc.semmnu: 30 kern.ipc.semmsl: 60 root@dev:~# sysctl -w kern.ipc.semmap=50 kern.ipc.semmap: 30 - 50 root@dev:~# sysctl -w kern.ipc.semmni=50 sysctl: oid 'kern.ipc.semmni' is read only root@dev:~# On 20 Nov 2002, Neil Conway wrote: The documentation on changing shared memory kernel settings on xBSD (namely FreeBSD, possibly others as well) isn't ideal, IMHO. It says: %% The options SYSVSHM and SYSVSEM need to be enabled when the kernel is compiled. (They are by default.) The maximum size of shared memory is determined by the option SHMMAXPGS (in pages). The following shows an example of how to set the various parameters: options SYSVSHM options SHMMAXPGS=4096 options SHMSEG=256 options SYSVSEM options SEMMNI=256 options SEMMNS=512 options SEMMNU=256 options SEMMAP=256 (On NetBSD and OpenBSD the key word is actually option singular.) You may also want to use the sysctl setting to lock shared memory into RAM and prevent it from being paged out to swap. %% However, it appears that shared memory semaphore settings can also be controlled via sysctls -- at least on a FreeBSD 4.7-RELEASE box, I can see: kern.ipc.semmap: 30 kern.ipc.semmni: 10 kern.ipc.semmns: 60 kern.ipc.semmnu: 30 kern.ipc.semmsl: 60 kern.ipc.semopm: 100 kern.ipc.semume: 10 kern.ipc.semusz: 92 kern.ipc.semvmx: 32767 kern.ipc.semaem: 16384 kern.ipc.shmmax: 33554432 kern.ipc.shmmin: 1 kern.ipc.shmmni: 192 kern.ipc.shmseg: 128 kern.ipc.shmall: 8192 kern.ipc.shm_use_phys: 0 However, the FreeBSD box I'm playing with isn't mine, so I'm not too keen to change sysctls (well, that and I don't have root :-) ). Would a kind BSD user confirm that: (a) the sysctls above *can* be used to change kernel shared memory settings, and the default value of the sysctl is the kernel option referred to in the docs. (b) do the above sysctls work on NetBSD and OpenBSD as well? (c) the 'prevent shared memory paging' sysctl vaguely referred to in the docs is 'kern.ipc.shm_use_phys', right? (d) does the above sysctl also work on NetBSD and OpenBSD? Thanks in advance, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Unique functional index and FK constraints
Tom, When you restructured the unique index location and validation for foreign key constraints around 9/22 you added the restriction that the supporting unique index may not be functional. I believe that this restriction is not necessary. Suppose I had a unique index on LOWER(login). That would imply that login is unique as well. Any function which returns different results given the same input is no good for a functional index anyway. http://archives.postgresql.org/pgsql-committers/2002-09/msg00293.php Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Kris Jurka wrote: On Thu, 19 Sep 2002, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? Yes, it does, but I don't understand why. The query is entirely against pg_catalog tables which have had all of three tables added to them. How can the new ANALYZE stats be significantly different than what came from the ANALYZED template1. Kris Jurka Looking at the differences in statistics before and after the ANALYZE the only differences are in correlation. This comes from initdb around line 1046... $PGPATH/postgres $PGSQL_OPT template1 /dev/null EOF ANALYZE; VACUUM FULL FREEZE; EOF Could this be done better in the one step VACUUM FULL FREEZE ANALYZE or ANALYZING after the VACUUM FULL? Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Optimizer generates bad plans.
While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. Kris Jurka The query tries to determine what foreign keys exists between the following tables. create table people (id int4 primary key, name text); create table policy (id int4 primary key, name text); create table users (id int4 primary key, people_id int4, policy_id int4, CONSTRAINT people FOREIGN KEY (people_id) references people(id), constraint policy FOREIGN KEY (policy_id) references policy(id)); SELECT DISTINCT n.nspname as pnspname, n2.nspname as fnspname, c.relname as prelname, c2.relname as frelname, t.tgconstrname, a.attnum as keyseq, ic.relname as fkeyname, t.tgdeferrable, t.tginitdeferred, t.tgnargs,t.tgargs, p1.proname as updaterule, p2.proname as deleterule FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE (t.tgrelid=c.oid AND t.tgisconstraint AND t.tgconstrrelid=c2.oid AND t.tgfoid=p1.oid and p1.proname like 'RI\_FKey\_%\_upd') AND (t1.tgrelid=c.oid and t1.tgisconstraint and t1.tgconstrrelid=c2.oid AND t1.tgfoid=p2.oid and p2.proname like 'RI\_FKey\_%\_del') AND i.indrelid=c.oid AND i.indexrelid=ic.oid AND ic.oid=a.attrelid AND i.indisprimary AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid AND c2.relname='users' ORDER BY prelname,keyseq ; Unique (cost=1.06..1.10 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) - Sort (cost=1.06..1.07 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) Sort Key: c.relname, a.attnum, n.nspname, n2.nspname, c2.relname, t.tgconstrname, ic.relname, t.tgdeferrable, t.tginitdeferred, t.tgnargs, t.tgargs, p1.proname, p2.proname - Merge Join (cost=1.03..1.05 rows=1 width=594) (actual time=844522. 00..845786.00 rows=2 loops=1) Merge Cond: (outer.tgconstrrelid = inner.tgconstrrelid) Join Filter: ((inner.tgfoid = outer.oid) AND (inner.tgrelid = outer.oid)) - Nested Loop (cost=0.00..27709.41 rows=1 width=510) (actual time=844522.00..845786.00 rows=12 loops=1) Join Filter: ((inner.indexrelid = outer.oid) AND (inner.indrelid = outer.oid)) - Nested Loop (cost=0.00..27706.67 rows=1 width=502) (actual time=843375.00..843954.00 rows=10620 loops=1) Join Filter: ((inner.tgconstrrelid = outer.oid) AND (outer.relnamespace = inner.oid)) - Index Scan using pg_class_oid_index on pg_class c2 (cost=0.00..15.67 rows=1 width=72) (actual time=1.00..1.00 rows=1 loops=1) Filter: (relname = 'users'::name) - Materialize (cost=27690.93..27690.93 rows=4 width=430) (actual time=843374.00..843781.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27690.93 rows=4 width=430) (actual time=614674.00..843125.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27689.85 rows=1 width=362) (actual time=614674.00..842368.00 rows=10620 loops=1) Join Filter: ((outer.tgfoid = inner.oid) AND (outer.tgrelid = inner.oid)) - Seq Scan on pg_trigger t1 (cost=0.00..1.02 rows=1 width=12) (actual time=0.00..1.00 rows=6 loops=1) Filter: tgisconstraint - Materialize (cost=26180.37..26180.37 rows=100564 width=350) (actual time=83492.50..135359.33 rows=3637350 loops=6) - Nested Loop (cost=0.00..26180.37 rows=100564 width=350) (actual time=68978.00..481414.00 rows=3637350 loops=1) Join Filter: (inner.relnamespace = outer.oid) - Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=68) (actual time=0.00..0.00 rows=4 loops=1) - Materialize (cost=5287.78..5287.78 rows=100564 width=282) (actual time=17273.75..110243.25 rows=3637350 loops=4
Re: [HACKERS] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? Yes, it does, but I don't understand why. The query is entirely against pg_catalog tables which have had all of three tables added to them. How can the new ANALYZE stats be significantly different than what came from the ANALYZED template1. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer generates bad plans.
Well I was really hoping pg_constraint would solve all my problems, but since contrib/array is not installed by default the conkeys and confkeys columns aren't terribly useful because they can't be joined to pg_attribute. Also there is not a column to tell you the unique constraint that supports a given foreign key constraint. See my post to bugs: http://fts.postgresql.org/db/mw/msg.html?mid=1074855 Kris Jurka On Thu, 19 Sep 2002, Bruce Momjian wrote: Congratulations. That is the largest plan I have ever seen. ;-) --- Kris Jurka wrote: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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
