Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of
> The right way to do this at the C level would be to use the same > infrastructure as nextval() does to accept arguments like 'foo' and > '"Foo"."Bar"'. There's no reason to restrict the two-argument form > to the current search_path. Is it possible to do that in SQL? eg. is there anything you can do to go: select '"Foo"."Bar"'::regclassoid; Or something? I'm trying to avoid doing it in C as it seems like it would be a pita. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] I just got it: PostgreSQL Application Server -- a new project.
"Carl E. McMillin" <[EMAIL PROTECTED]> wrote: >> ...That's one of the reasons I wrote Pl/Java. > > More power too you! I'd really like to hear more about this project. Is > > http://gborg.postgresql.org/project/pljava/projdisplay.php > > your URL? > Yes, it is. > I'm now in complete agreement: app-server doesn't fit. Do you have any > suggestions? Would a postgreslet be out of bounds, do you think? > "PostgreSQL Advanced Storage Server" perhaps :-) > I admit my almost complete ignorance of how sensitive the postgres backend > is to all the hazards of process-control: is the postgres process REALLY > just another UNIX process? Can I "exec" on top of it? Can I fork? > Yes (on a Unix platform), yes, and yes (again, on Unix. Windows doesn't have fork). > Can I have a child-process using IPC wait for 10 mins for its connected process do > its work without hosing the postmaster with its shared memory locks and all > that? I've held off any serious development along these lines since I don't > have the time to do heavy code-trawling, that seeming the only way of > obtaining the level of detail necessary to do the job well. > I think so although I haven't tried it so I'm not completely sure about timeouts. I guess that if there indeed are such timeouts, they are configurable. The main concern is probably not the stuff that you address. The really hard part is transaction coordination. What if the process you start have some side effects? What if the call that was issued through PostgreSQL is rolled back? PostgreSQL currently lacks a way to subscribe to transactional events so there's no way your code can detect the outcome of a transaction. > I would most definitely use embedded java if it could do at-minimum SRF's > and spawn processes. Something similar to SPI for Java would be pretty > useful too, I imagine. > It's there already. Pl/Java comes with a JDBC driver implemented on top of SPI. And using it, you are of course running in the same transaction as the origin of the call to Java. Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Coding question
If typTup is of type Form_pg_type, is this use of ObjectIdGetDatum legal? tuple = SearchSysCache(RELOID, ObjectIdGetDatum(typTup->typrelid), 0, 0, 0); If not, how do I turn ->typrelid into an Oid type for safe passage through ObjectIdGetDatum? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera wrote: > On Tue, Jun 01, 2004 at 06:40:07PM -0400, Bruce Momjian wrote: > > > When "DELETE a" happens, we remove the xmin=1 from the tuple header and > > replace it with xmin=3. xid=3 will be marked as committed if xid2 > > aborts, and will be marked as aborted if xid3 commits. > > > > So, if xid2 aborts, the insert of xid1 should be honored, and xid3 is > > marked as committed, and the opposite if xid2 commits. > > Ok, I've been looking at implementing this. However it just occurred to > me that a transaction, different from the one modifying the tuple, could > try to see its xmax. First, I assume it isn't a problem to change the cmin because we have a lock on the tuple while we are modifying it. > Since the xmin signals the tuple as being updated concurrently by > another transaction (it's in progress), this can only happen if the > other transaction tries to read it using SnapshotDirty. As far as someone trying to read the xmax when it isn't there, I assumed we had a tuple bit set indicating we have xmin/cmin/cmax, and that the outside transaction doesn't need to look up the xmax. However, for ourselves, we need to look that phantom xid up in our local memory and find the xmin/xmax for ourselves, right? > One such possible caller is EvalPlanQual. It could go to sleep using > XactLockTableWait() on the SnapshotDirty's xmax. But the tuple has > something strange in its xmax -- it's the tuple's cmin actually. > Leaving this would be probably a bug. Again, the bit is set, everyone has to look up the phantom xid in their own phantom xid list, or look in pg_subtrans to find out if they own that xid, and if so, then lookup the xmin/xmax in their local memory. As far as SnapshotDirty(), can you explain why that is used and if a tuple being created in an open transaction is subject to that? I assume it would be if we were updating a tuple and need to sleep on it. Can't we use pg_subtrans to find the main transaction xid and sleep on that? Once the main transaction is done, we then need to relook at the phantom xid to see if it was marked as committed/aborted because it might not match the main transactions status. Sorry I didn't reply earlier. I had to think on this one. -- 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 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] logfile rotation
Tom doesn't like returning the server's logfile using a pgsql function unless logfile rotation is implemented, so here it is. This proposal doesn't include a daemon that triggers pg_logfile_rotate when appropriate (timestamp and/or length dependent), pg_autovacuum might be a good place to do that. I'd also like to see a table pg_logfile which contains all logfile names, to be able to access older logfiles. Comments? Regards, Andreas Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.206 diff -u -r1.206 func.sgml --- doc/src/sgml/func.sgml 2 Jun 2004 21:34:49 - 1.206 +++ doc/src/sgml/func.sgml 13 Jun 2004 15:57:06 - @@ -7308,6 +7308,80 @@ columns do not have OIDs of their own. + + pg_logfile_get + + + pg_logfile_length + + + pg_logfile_name + + + pg_logfile_rotate + + +The functions shown in + deal with the server log file if configured with log_destination + file. + + + +Server Logfile Functions + + + Name Return Type Description + + + + + pg_logfile_get(size_int4, + offset_int4,filename_text) + cstring + get a part of the current server log file + + + pg_logfile_length(filename_text) + int4 + return the current length of the server log file + + + pg_logfile_rotate() + cstring + rotates the server log file and returns the new log file + name + + + pg_logfile_name() + cstring + returns the current server log file name + + + pg_logfile_rotate() + cstring + rotates the server log file and returns the previous log file + name + + + + + +The pg_logfile_get function will return the + contents of the current server log file, limited by the size + parameter. If size is NULL, a server internal limit (currently + 5) is applied. The position parameter specifies the + starting position of the server log chunk to be returned. A + positive number or 0 will be counted from the start of the file, + a negative number from the end; if NULL, -size is assumed + (i.e. the tail of the log file). + + +Both pg_logfile_get and + pg_logfile_length have a filename + parameter which may specify the logfile to examine or the + current logfile if NULL. + + Index: doc/src/sgml/runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.266 diff -u -r1.266 runtime.sgml --- doc/src/sgml/runtime.sgml 10 Jun 2004 22:26:17 - 1.266 +++ doc/src/sgml/runtime.sgml 13 Jun 2004 15:57:17 - @@ -1721,14 +1721,25 @@ PostgreSQL supports several methods -for loggning, including stderr and -syslog. On Windows, -eventlog is also supported. Set this +for logging, including stderr, +file> and syslog. + On Windows, eventlog is also supported. Set this option to a list of desired log destinations separated by a comma. The default is to log to stderr only. This option must be set at server start. + + + + log_filename (string) + + + This option sets the target filename for the log destination + file option. It may be specified as absolute + path or relative to the cluster directory. + + Index: src/backend/postmaster/postmaster.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v retrieving revision 1.403 diff -u -r1.403 postmaster.c --- src/backend/postmaster/postmaster.c 11 Jun 2004 03:54:43 - 1.403 +++ src/backend/postmaster/postmaster.c 13 Jun 2004 15:57:24 - @@ -727,6 +727,11 @@ reset_shared(PostPortNumber); /* +* Opens alternate log file +*/ + LogFileInit(); + + /* * Estimate number of openable files. This must happen after setting * up semaphores, because on some platforms semaphores count as open * files. Index: src/backend/storage/ipc/ipc.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/storage/ipc/ipc.c,v retrieving revision 1.87 diff -u -r1.87 ipc.c --- src/backend/storage/ipc/ipc.c 12 Dec 2003 18:45:09 - 1.87 +++ src/backend/storage/ipc/ipc.c 13 Jun 2004 15:57:24 - @@ -111,6 +111,8 @@ on_proc_exit_list[on_proc_exit_index].arg); elog(DEBUG3, "exit(%d)", co
Re: [HACKERS] logfile rotation
Andreas Pflug <[EMAIL PROTECTED]> writes: > Tom doesn't like returning the server's logfile using a pgsql function > unless logfile rotation is implemented, so here it is. I'll repeat what I said in response to your other posting: This uses a shared memory area with no lock, which seems a bad design; but the alternative of having a lock is even worse, since the postmaster would also have to take the lock. We agreed long ago that the postmaster should never depend on the correctness of any shared memory data structure; but this patch would make it do so. I really don't think this is an acceptable solution. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Release 7.4.3 branded
Joe Conway <[EMAIL PROTECTED]> writes: > I just built from a fresh cvs checkout (7.4 stable) and get these warnings: OK, this looks to be the same issue Oliver Elphick reported a couple weeks ago against CVS tip. I've backpatched his fix into 7.4 branch (there was a warning in ecpg too, according to him). regards, tom lane ---(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] logfile rotation
Tom Lane wrote: I'll repeat what I said in response to your other posting: Hm? I never posted something with shared mem usage before, what do you mean? This uses a shared memory area with no lock, which seems a bad design; AFAICS there should be no lock necessary. We agreed long ago that the postmaster should never depend on the correctness of any shared memory data structure; but this patch would make it do so. I understand that, so what's the suggested way to store data common for all backends? Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested transactions and tuple header info
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Not sure how to fix this -- I'm not even sure what the exact problem is, > because it's trying to insert the oid of a toast table in > pg_class_oid_index during the first ALTER TABLE ... CREATE TOAST TABLE. > Why would it see an old tuple with the same value, I don't know. This is probably an UPDATE operation not an INSERT. There are quite a few paths through CREATE TABLE that involve repeated updates of the new pg_class row. I would think however that these changes would occur as successive commands of a single transaction, not as subtransactions, unless you've done something odd to CommandCounterIncrement. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I'm trying to avoid doing it in C as it seems like it would be a pita. I think it would be simpler in C than this mess in SQL is ;-). You would not of course implement it in any way that would look like the SQL query ... but there are existing utility subroutines for most of the bits that are being done with joins here. regards, tom lane ---(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] logfile rotation
Andreas Pflug wrote: We agreed long ago that the postmaster should never depend on the correctness of any shared memory data structure; but this patch would make it do so. I understand that, so what's the suggested way to store data common for all backends? Answering my own question, the distribution of the current logfile name could be done trough a file handle. So the only thing remaining in shared mem would be the "reopen logfile" flag, which seems nonproblematic. In case of garbled sharedmem, a backend would reopen the logfile, which does no harm at all, or continue writing the outdated logfile which would be annoying but not harmful either. Acceptable? Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] File leak?
On Sat, 12 Jun 2004, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > I wonder if we could clean up those lost files on database recovery or > > vacuum. > > There is a TODO for this, but it seems exceedingly low priority to me. Are you sure? I read through the TODO list but couldn't find it. > In any case I'd not recommend troubling to work on the problem until > the tablespaces merry-go-round comes to a complete stop, since that > restructuring will likely change what you'd need to do to identify > unreferenced files. Ok. I'll to take a look at this later. - Heikki ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] File leak?
Heikki Linnakangas <[EMAIL PROTECTED]> writes: > On Sat, 12 Jun 2004, Tom Lane wrote: >> Heikki Linnakangas <[EMAIL PROTECTED]> writes: >>> I wonder if we could clean up those lost files on database recovery or >>> vacuum. >> >> There is a TODO for this, but it seems exceedingly low priority to me. > Are you sure? I read through the TODO list but couldn't find it. Well, there used to be: 7.4 TODO has * Remove unreferenced table files and temp tables during database vacuum or postmaster startup (Bruce) Now that I think about it, I believe Bruce recently removed this on my advice; I was thinking that the problem shouldn't occur anymore now that we WAL-log file creation and deletion. But actually the present form of the WAL entries doesn't ensure that a file created by a transaction that crashes before committing will go away, because file deletion actions are only logged (and replayed) at transaction commit/abort. So it probably should go back in. Or else we could add more WAL logging (viz, log at the instant of file creation, and the replayer would have to keep track of whether it sees the creating transaction commit and delete the file if not). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Can get GiST RECHECK clause to work
Hi everyone, I'm trying to mark a GiST index as lossy using the RECHECK operator as part of some work on PostGIS, but what happens is that the original operator function is never reapplied to the results of the index scan. The operator class and operator definitions looks like this: CREATE OPERATOR && ( LEFTARG = GEOMETRY, RIGHTARG = GEOMETRY, PROCEDURE = geometry_overlap, COMMUTATOR = '&&', RESTRICT = postgis_gist_sel, JOIN = positionjoinsel ); and: CREATE OPERATOR CLASS gist_geometry_ops DEFAULT FOR TYPE geometry USING gist AS OPERATOR1 << RECHECK, OPERATOR2 &< RECHECK, OPERATOR3 && RECHECK, OPERATOR4 &> RECHECK, OPERATOR5 >> RECHECK, OPERATOR6 ~= RECHECK, OPERATOR7 ~ RECHECK, OPERATOR8 @ RECHECK, FUNCTION1 ggeometry_consistent (internal, geometry, int4), FUNCTION2 gbox_union (bytea, internal), FUNCTION3 ggeometry_compress (internal), FUNCTION4 rtree_decompress (internal), FUNCTION5 gbox_penalty (internal, internal, internal), FUNCTION6 gbox_picksplit (internal, internal), FUNCTION7 gbox_same (box, box, internal); What I'm expecting is that since RECHECK is specified, PostgreSQL will identify the index entries using the && operator and then call geometry_overlap() function with the full tuples from the heap. However, it seems geometry_overlap() is never called from an index scan made using the && operator :( Can anyone point out where I'm going wrong? Many thanks, Mark. --- Mark Cave-Ayland Webbased Ltd. Tamar Science Park Derriford Plymouth PL6 8BX England Tel: +44 (0)1752 764445 Fax: +44 (0)1752 764446 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Releasing 7.4.3 ...
Bruce Momjian wrote: > Removing README.CVS from the tarball is something Marc handles, as > far as I know. I just added it to CVS and never worked on having it > removed from the tarballs. I've added a rule to remove README.CVS when making a distribution. But I seem to be missing any rules to build the plain-text documentation files. What happened to those? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Releasing 7.4.3 ...
Marc G. Fournier wrote: > Anyone else, please test the tar ball for any bug/nits ... > specifically, Peter, can you check that I've built/included the right > documentation? Try reading the list of supported platforms at the bottom of the INSTALL file... ---(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] Releasing 7.4.3 ...
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Bruce Momjian wrote: >> Removing README.CVS from the tarball is something Marc handles, as >> far as I know. I just added it to CVS and never worked on having it >> removed from the tarballs. > I've added a rule to remove README.CVS when making a distribution. But > I seem to be missing any rules to build the plain-text documentation > files. What happened to those? You mean HISTORY and INSTALL? They are there in the tarball (or were yesterday anyway), so the build rule exists someplace ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested transactions and tuple header info
On Sun, Jun 13, 2004 at 01:22:05PM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Not sure how to fix this -- I'm not even sure what the exact problem is, > > because it's trying to insert the oid of a toast table in > > pg_class_oid_index during the first ALTER TABLE ... CREATE TOAST TABLE. > > Why would it see an old tuple with the same value, I don't know. > > This is probably an UPDATE operation not an INSERT. There are quite a > few paths through CREATE TABLE that involve repeated updates of the > new pg_class row. Huh, right. > I would think however that these changes would occur as successive > commands of a single transaction, not as subtransactions, unless you've > done something odd to CommandCounterIncrement. Right, but I've taken the XMAX_IS_XMIN bit and replaced it with the phantom Ids idea. Probably this problem wouldn't have shown up if I hadn't done that, but we need to cope anyway. -- Alvaro Herrera () "La principal característica humana es la tontería" (Augusto Monterroso) ---(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] Can get GiST RECHECK clause to work
"Mark Cave-Ayland" <[EMAIL PROTECTED]> writes:
> I'm trying to mark a GiST index as lossy using the RECHECK operator as
> part of some work on PostGIS, but what happens is that the original
> operator function is never reapplied to the results of the index scan.
You sure? I'm pretty sure that a number of the contrib gist index
opclasses would fail their regression tests if this were broken.
As of 7.5 you cannot see the reapplication in the generated plan's
filter condition; perhaps that got you confused?
2004-01-05 23:31 tgl
* src/: backend/executor/nodeIndexscan.c,
backend/nodes/copyfuncs.c, backend/nodes/outfuncs.c,
backend/optimizer/path/costsize.c,
backend/optimizer/plan/createplan.c,
backend/optimizer/plan/setrefs.c, include/nodes/execnodes.h,
include/nodes/plannodes.h: Instead of rechecking lossy index
operators by putting them into the regular qpqual ('filter
condition'), add special-purpose code to nodeIndexscan.c to recheck
them. This ends being almost no net addition of code, because the
removal of planner code balances out the extra executor code, but
it is significantly more efficient when a lossy operator is
involved in an OR indexscan. The old implementation had to recheck
the entire indexqual in such cases.
regards, tom lane
---(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: [PATCHES] [HACKERS] Configuration patch
> > Where are we on this? That's a good question. Tom doesn't like the syntax of "include" and there are a couple bugs he is concered it. I'm pretty agnostic about the syntax, but I wouldn't get overly worried about the metaphor presented either. "include='...'" doesn't bother me at all, but some people have a problem with it. Then there is the design of using a callable function for a configuration parameter, personally, I think this feature is useful for the future, Tom seems to have a problem it it. After that, the discussion sort of ends. I'm willing to adress the bugs. I don't think the syntax is a huge deal, IMHO at most it is a documentation problem. > > --- > > Tom Lane wrote: >> Bruce Momjian <[EMAIL PROTECTED]> writes: >> > One interesting idea would be for "SET include" to work like this: >> >SET include '/var/run/xx' >> > Notice there is no equals here. This would allow users to create >> files >> > with various settings and enable them all with one SET command. >> > However, this does open a security issue. >> >> More than one, in fact. In the first place, as the code presently >> works, anything coming in from the file would be treated on an equal >> footing with values sourced from postgresql.conf, thereby allowing >> unprivileged users to set things they shouldn't. This is potentially >> fixable, but the other issue isn't: such a facility would allow anyone >> to ask the backend to read any file the Postgres user account can >> access. Not very successfully, perhaps, but even the error messages >> might give useful info about the file's contents to an attacker. This >> is the same reason that "COPY FROM file" is a privileged operation. >> >> I think it's important that include be restricted to appear only in >> config files, and not be in any way shape or form a SETtable thing. >> >> > In summary, I think we need to treat include specially in >> > postgresql.conf (no equals) and remove it as an actual GUC parameter >> and >> > just have it do includes immediately. (This will probably require >> > special-casing it in the guc-file grammar.) >> >> Yes. In fact, it'll be a less-than-trivial change in guc-file, at least >> if you want the thing to act intuitively (that is, "include" acts like >> the target file is actually included right here). This will mean >> splitting ProcessConfigFile into a recursive read step followed by a >> nonrecursive apply step. Also, I think that invoking the flex lexer >> recursively will take a little bit of work. >> >> I would suggest splitting the patch into two separate patches, one that >> handles "include" and one that handles the other changes. The other >> stuff is reasonably close to being ready to apply (modulo docs and >> fixing the standalone-backend case), but "include" I think is still a >> ways off. >> >> regards, tom lane >> > > -- > 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 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
> >> > -Original Message- >> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> > >> > > I surely hope not. Especially not multi-gig databases. The folks >> running >> > > those should know better than to use Windows, and if they do not, >> I'll >> > > be happy to tell them so. > > You know, it makes you wonder. Tom must not have enough work to do if he's > so > bored that he wants to spice up the postgres mailing lists this way :) > > -- > greg It is the creative mind. We all suffer from "Engineer's Tourettes Syndrome" (The uncontrollable need to express contrarian and margenally related opinions.) and at some point. ---(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 get GiST RECHECK clause to work
Hi Tom,
As far as I can tell this is the case. What I've done to test this is to
put an elog(NOTICE, ".") in geometry_overlap() so I can tell when
it's being called and this is the result I get:
shapefile=# select * from tgr1 where the_geom &&
GeometryFromText('BOX3D(1000 10
0, 2000 2000)'::box3d, -2);
NOTICE: postgis_gist_sel called
NOTICE: search_box does not overlaps histogram, returning 0
NOTICE: returning computed value: 0.00
NOTICE: IN GEOMETRY OVERLAP
ERROR: Operation on two GEOMETRIES with different SRIDs
shapefile=# create index tgr1_idx on tgr1 using gist (the_geom
gist_geometry_ops
);
CREATE INDEX
shapefile=# select * from tgr1 where the_geom &&
GeometryFromText('BOX3D(1000 10
0, 2000 2000)'::box3d, -2);
NOTICE: postgis_gist_sel called
NOTICE: search_box does not overlaps histogram, returning 0
NOTICE: returning computed value: 0.00
gid | tlid | fnode | tnode | length | fedirp | fename | fetype | fedirs
| cfcc
| fraddl | toaddl | fraddr | toaddr | zipl | zipr | census1 | census2 |
cfcc1 |
cfcc2 | source | the_geom
-+--+---+---+++++---
-+--
+++++--+--+-+-+-
--+-
--++--
(0 rows)
shapefile=# explain analyze select * from tgr1 where the_geom &&
GeometryFromTex
t('BOX3D(1000 100, 2000 2000)'::box3d, -2);
NOTICE: postgis_gist_sel called
NOTICE: search_box does not overlaps histogram, returning 0
NOTICE: returning computed value: 0.00
QUERY PLAN
---
Index Scan using tgr1_idx on tgr1 (cost=0.00..6.01 rows=1 width=327)
(actual t
ime=30.000..30.000 rows=0 loops=1)
Index Cond: (the_geom && 'SRID=-2;BOX3D(1000 100 0,2000 2000
0)'::geometry)
Total runtime: 30.000 ms
(3 rows)
shapefile=# select * from pg_amop where amopclaid=(SELECT oid FROM
pg_opclass W
ERE opcname = 'gist_geometry_ops');
amopclaid | amopsubtype | amopstrategy | amopreqcheck | amopopr
---+-+--+--+-
17456 | 0 |1 | t| 17410
17456 | 0 |2 | t| 17412
17456 | 0 |3 | t| 17413
17456 | 0 |4 | t| 17411
17456 | 0 |5 | t| 17409
17456 | 0 |6 | t| 17414
17456 | 0 |7 | t| 17415
17456 | 0 |8 | t| 17416
(8 rows)
So before the index is created, the geometry_overlap() function is
called, but whenever an index scan is used then it's never called?
I've had a look at contrib/rtree_gist but it doesn't make much sense;
from what I can see the RECHECK clause is specified for a couple of
operators in the operator class but there is no operator defined in the
SQL file - so I'm guessing that in this case the RECHECK won't do
anything anyway? I'm wondering if I'm missing some sort of mapping
between && used for CREATE OPERATOR and the && listed in CREATE OPERATOR
CLASS?
This is happening with current CVS as of earlier today, however it looks
as if it doesn't work in 7.4 either (see
http://postgis.refractions.net/pipermail/postgis-users/2004-June/004973.
html where I was trying to get the person in question to alter the
catalogues manually to enforce the RECHECK which didn't solve the
problem for him either).
Many thanks,
Mark.
---
Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England
Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: 13 June 2004 23:09
> To: Mark Cave-Ayland
> Cc: [EMAIL PROTECTED]
> Subject: Re: [HACKERS] Can get GiST RECHECK clause to work
>
>
> "Mark Cave-Ayland" <[EMAIL PROTECTED]> writes:
> > I'm trying to mark a GiST index as lossy using the RECHECK
> operator as
> > part of some work on PostGIS, but what happens is that the original
> > operator function is never reapplied to the results of the
> index scan.
>
> You sure? I'm pretty sure that a number of the contrib gist
> index opclasses would fail their regression tests if this were broken.
>
> As of 7.5 you cannot see the reapplication in the generated
> plan's filter condition; perhaps that got you confused?
>
> 2004-01-05 23:31 tgl
>
> * src/: backend/executor/nodeIndexscan.c,
> backend/nodes/copyfuncs.c, backend/nodes/outf
Re: [HACKERS] [PATCHES] Compiling libpq with VisualC
>
> [ Thread moved to hackers and win32.]
>
> Andreas Pflug wrote:
>> Bruce Momjian wrote:
>>
>> >
>> >
>> >Agreed. My pthread book says pthread_mutex_init() should be called
>> only
>> >once, and we have to guarantee that. If the Windows implentation
>> allows
>> >it to be called multiple times, just create a function to be called
>> only
>> >by Win32 that does that and leave the Unix safe.
>> >
>> >
>> >
>> Ok, so here's the win32 workaround with the unix stuff left untouched.
>> There's no memory interlocking api in win32 that wouldn't need some
>> initializing api call itself, so we'd have to go for assembly level
>> test-and-set code or introduce a mandatory global libpq initializing
>> api. Considering the probably quite low usage of kerberos/ssl together
>> with threads under win32, and the very low probability of two
>> threads/processors (!) trying to initiate a connection at the same time,
>> it doesn't seem to be worth the compiler hassle with assembly inline.
>
> What is the recommended way to create mutex objects (CreateMutex) from
> Win32 libraries? There must be a clean way like there is in pthreads.
A mutex is inherently a global object. CreateMutex(NULL, FALSE, NULL) will
return a handle to an unowned mutex.
>
> ---
>
> In the patch Win32, pthread_mutex_init() == CreateMutex():
>
> +#ifndef WIN32
> static pthread_mutex_t singlethread_lock =
> PTHREAD_MUTEX_INITIALIZER;
> +#else
> + static pthread_mutex_t singlethread_lock;
> +static int mutex_initialized = 0;
> +if (!mutex_initialized)
> +{
> +mutex_initialized = 1;
> +pthread_mutex_init(&singlethread_lock, NULL);
> +}
> +#endif
>
> --
> 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Can get GiST RECHECK clause to work
"Mark Cave-Ayland" <[EMAIL PROTECTED]> writes: > As far as I can tell this is the case. What I've done to test this is to > put an elog(NOTICE, ".") in geometry_overlap() Well, I can easily prove that CVS tip does call the operator function and honor its result. regression=# create table foo (f1 float8 unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo" CREATE TABLE regression=# insert into foo values(1); INSERT 480998 1 regression=# insert into foo values(2); INSERT 480999 1 regression=# select * from foo where f1 = 1; f1 1 (1 row) With gdb, I set a breakpoint at float8eq, and determine that it is called exactly once (during _bt_checkkeys' scan setup) in this query. Next, after some fooling about to determine which row in pg_amop describes float8eq: regression=# update pg_amop set amopreqcheck = true regression-# where amopclaid = 1972 and amopsubtype = 0 and amopstrategy = 3; UPDATE 1 Now the select calls float8eq twice, once from _bt_checkkeys and once from IndexNext. Moreover I can force a zero result from float8eq in the second call, and if I do then no rows are returned. My guess is that your problem occurs because the index is not returning the row in the first place, and thus there is nothing to recheck. This would point to a bug somewhere in your GIST support functions. regards, tom lane ---(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] simple_heap_update: tuple concurrently updated -- during INSERT
Dear Tom, I'll try my best. Also, I'll try to describe the situation more precisely, in case it may give you another idea. INSERT INTO p_items; -> p_items before: INSERT, UPDATE and/or DELETE other tuples in p_items -> p_items after: UPDATE p SET touch_time, toucher; > p after: INSERT INTO p_ny ---> p_ny after: NOTIFY p May it be that more "NOTIFY p"'s come from the same transaction (since I change more than one tuples in p_items)? Based on the error text, I assume this error comes only when two different transactions clash. I think it's very unlikely anyway since it happens ~100-1000 times a day and so far (~2 years of 7.3.3) this is the only occurence of this error. A final question: as far as you can remember, may this be an issue already fixed in later versions? Thanks again, HTH, and I'll report back if I encounter the error again. G. %--- cut here ---% \end - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> Subject: Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT > "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <[EMAIL PROTECTED]> writes: > > Q1. So is this everything that can be said -- NOTIFY calls > > simple_heap_update that is concurrently updated by a different transaction? > > If that's what it is, then there's still a question: why? The notify > code has enough locking that this failure shouldn't happen. If you can > reproduce this I'd like to look into it. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] simple_heap_update: tuple concurrently updated -- during INSERT
Dear Tom, You did it again! The all-amazing-Tom-Lane-clearsight ;) I could reproduce this. I can imagine this practically as: (session 1) someone shutting down one of our Windows clients, while (session 2) another one did the INSERT at the very same moment. This thing caused session 2 to abort. The only thing I still don't understand is the "not in in-progress state" thing. After all, it's the very end of quite a long transaction. Thanks again. I'll lobby to upgrade our production server to 7.4 :) Yours, G. %--- cut here ---% \end - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> Sent: Thursday, June 10, 2004 3:57 PM > session one: > listen foo; > begin; > unlisten foo; > session two: > notify foo; > -- hangs > session one: > end; > -- session two now says > WARNING: AbortTransaction and not in in-progress state > ERROR: simple_heap_update: tuple concurrently updated > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] File leak?
Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > On Sat, 12 Jun 2004, Tom Lane wrote: > >> Heikki Linnakangas <[EMAIL PROTECTED]> writes: > >>> I wonder if we could clean up those lost files on database recovery or > >>> vacuum. > >> > >> There is a TODO for this, but it seems exceedingly low priority to me. > > > Are you sure? I read through the TODO list but couldn't find it. > > Well, there used to be: 7.4 TODO has > > * Remove unreferenced table files and temp tables during database vacuum > or postmaster startup (Bruce) > > Now that I think about it, I believe Bruce recently removed this on my > advice; I was thinking that the problem shouldn't occur anymore now that True. > we WAL-log file creation and deletion. But actually the present form of > the WAL entries doesn't ensure that a file created by a transaction that > crashes before committing will go away, because file deletion actions > are only logged (and replayed) at transaction commit/abort. So it > probably should go back in. Or else we could add more WAL logging Wording updated to: * Remove unreferenced table files created by a transactions that were in-progress when the server crashed > (viz, log at the instant of file creation, and the replayer would have > to keep track of whether it sees the creating transaction commit and > delete the file if not). I don't see how we could WAL log it because we don't fsync the WAL until our transaction completes, right, or are you thinking we would do a special fsync when we add the record? -- 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 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Configuration patch
[EMAIL PROTECTED] wrote: > > > > Where are we on this? > > That's a good question. > > Tom doesn't like the syntax of "include" and there are a couple bugs he is > concered it. > > I'm pretty agnostic about the syntax, but I wouldn't get overly worried > about the metaphor presented either. > > "include='...'" doesn't bother me at all, but some people have a problem > with it. > > Then there is the design of using a callable function for a configuration > parameter, personally, I think this feature is useful for the future, Tom > seems to have a problem it it. > > After that, the discussion sort of ends. > > > I'm willing to adress the bugs. > I don't think the syntax is a huge deal, IMHO at most it is a > documentation problem. Well, it seems pretty clear were we need to go on this. First, we could just add the documentation to the non-include part of the patch based on the version I posted and apply that to be sure it gets into 7.5. Then, for "include", it needs to be an operation and not a variable, probably something in guc-file.l. It should not use an equals and not be something you can say SHOW with. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] File leak?
Bruce Momjian <[EMAIL PROTECTED]> writes: >> (viz, log at the instant of file creation, and the replayer would have >> to keep track of whether it sees the creating transaction commit and >> delete the file if not). > I don't see how we could WAL log it because we don't fsync the WAL until > our transaction completes, right, or are you thinking we would do a > special fsync when we add the record? Right, we would have to XLogFlush the file-creation WAL record before we could actually create the file. This is in line with the standard WAL rule: the WAL record must hit disk before the data file change it describes does. Assuming that the filesystem fsync's the created inode immediately, that means we have to flush first. I'm not sure what the performance implications of this would be; it's likely that pushing the cost somewhere else would be better. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] [PATCHES] Compiling libpq with VisualC
[EMAIL PROTECTED] wrote: > > > > [ Thread moved to hackers and win32.] > > > > Andreas Pflug wrote: > >> Bruce Momjian wrote: > >> > >> > > >> > > >> >Agreed. My pthread book says pthread_mutex_init() should be called > >> only > >> >once, and we have to guarantee that. If the Windows implentation > >> allows > >> >it to be called multiple times, just create a function to be called > >> only > >> >by Win32 that does that and leave the Unix safe. > >> > > >> > > >> > > >> Ok, so here's the win32 workaround with the unix stuff left untouched. > >> There's no memory interlocking api in win32 that wouldn't need some > >> initializing api call itself, so we'd have to go for assembly level > >> test-and-set code or introduce a mandatory global libpq initializing > >> api. Considering the probably quite low usage of kerberos/ssl together > >> with threads under win32, and the very low probability of two > >> threads/processors (!) trying to initiate a connection at the same time, > >> it doesn't seem to be worth the compiler hassle with assembly inline. > > > > What is the recommended way to create mutex objects (CreateMutex) from > > Win32 libraries? There must be a clean way like there is in pthreads. > > A mutex is inherently a global object. CreateMutex(NULL, FALSE, NULL) will > return a handle to an unowned mutex. Yes, but consider that two threads could both call it, with one perhaps using the first value, and the second overwriting the first. Obviously not something we want. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Configuration patch
>> Tom doesn't like the syntax of "include" I said more than once that I didn't care about the syntax; it's the implementation I was objecting to. However, given that we are going to push it into guc-file.l, it'll be easier all around if we choose a syntax that doesn't look exactly like a variable assignment. include 'file' with no equal sign would probably work as well as anything else. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Delaying the planning of unnamed statements until Bind
Tom Lane wrote: I've applied the patch you sent in for this, with some editorializations --- you were being too aggressive about substituting constants, with the net effect that the plan was not still parameterized as it was supposed to be. Thanks. This should make my JDBC driver changes easier to sell. I realized along the way that what we're really doing here is inventing a notion of constant-folding expressions "for estimation purposes only". As such, we don't have to be as rigid about making only provably safe transformations as eval_const_expressions normally has to be. I didn't do anything with the idea yet, but I'd like to look into having this mode do more than just substitute Param values. An example that's been causing us trouble for a long while is that the planner can't make any nondefault selectivity estimate for SELECT ... WHERE timestampcol > now() - '1 day'; because eval_const_expressions dare not reduce now() to current time. But I think it would be entirely reasonable to do so "for estimation purposes". Something related I was pondering was adding a "constant expression at execution" flag to various expression nodes. eval_const_expressions would use this to mark expressions that are constant for a particular execution, but can't be constant-folded safely at planning time (essentially a STABLE modifier for expression trees). The evaluate-for-estimation logic could use this to determine when it's safe to evaluate the whole expression as constant. I think this handles the now() case too, as STABLE functions are "constant at execution" if their arguments are. At execution time the executor can cache the results of expressions flagged as constant at execution, assuming there's somewhere safe to cache the result for just that execution (ExprState?). This should make queries that use parameters in complex expressions go faster. I took a quick look through the executor code, but couldn't see where STABLE function results are cached (for the same arguments). Does this currently happen? If not, we'd get that as well. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Delaying the planning of unnamed statements until Bind
Oliver Jowett <[EMAIL PROTECTED]> writes: > At execution time the executor can cache the results of expressions > flagged as constant at execution, assuming there's somewhere safe to > cache the result for just that execution (ExprState?). That would be the problem; there isn't anyplace appropriate. I'm not sure this is really worth pursuing... > I took a quick look through the executor code, but couldn't see where > STABLE function results are cached (for the same arguments). They aren't. STABLE is not a cache-enabling modifier: what it is actually for is to license the function to be used in an indexscan qualification. Consider where timestampcol > now() - '1 day'; If now() were considered volatile (ie, we had no guarantees at all about its behavior --- consider random() as an example) then we could not generate an indexscan on timestampcol, because an indexscan assumes that the compared-to value is going to hold still throughout the indexscan. Remember that the logical model defined by the SQL spec is that the WHERE condition is evaluated at every row of the table --- we can only optimize this if we can be sure that we know what the optimized-away evaluations would produce. This is why the definition of STABLE refers to holding still throughout a table scan, rather than other reasonable alternatives one might consider (such as being constant throughout one transaction). I suppose you could envision the indexscan machinery as caching the right-hand-side value in the index ScanKey, but this is far from being a general purpose expression caching mechanism. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] New stable snapshot built ...
Just for the few changes that went in today docs related ... will build the release on in the morning, but this gives a few hours for ppl to see if anything appears to b emissing .. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Releasing 7.4.3 ...
On Sun, 13 Jun 2004, Peter Eisentraut wrote: Marc G. Fournier wrote: Anyone else, please test the tar ball for any bug/nits ... specifically, Peter, can you check that I've built/included the right documentation? Try reading the list of supported platforms at the bottom of the INSTALL file... k, and that is supposed to tell me what? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I just got it: PostgreSQL Application Server -- a
OK, perhaps application server is not an appropriate name, but what should we call it? Two issues: (1) We should get this off hackers, but to where? (2)My vision for this thing is that it is more than just PostgreSQL, it is PG plus a lot of the popular add-ons and some new ones, sample code, all with the feel of a "product." At the end of it, you'll be able to identify the PostgreSQL components, but not the whole. This is not a slam against the core team. The core team does a great job, but the is a gulf between products like MSSQL and Oracle and PostgreSQL. Yea, sure, you can get and use a lot of add-ons for PostgreSQL to do what these systems can do, but many people can't or won't do that. The "PostgreSQL Enhanced Server" (How's that name? Maybe we call it Zerver and use PEZ?) idea is how to take the excellent core of PostgreSQL and productize it in much the same way distributions take the Linux kernel and may a GNU/Linux system. > Even if I find the concepts as such very interesting, I think the term > "Application Server" is very misleading. People would get very confused > and > place PostgreSQL in the same category as JBoss, Jonas, Apache Geronimo, > IBM > Websphere, BEA Weblogic to name a few well known App-servers. > > IMHO, you really need some other umbrella name for this. > > Kind regards, > > Thomas Hallgren > > > ""Carl E. McMillin"" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > Jumping on that bandwagon with all 6 feet! > > Carl <|};-)> > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: Friday, June 11, 2004 9:38 PM > To: [EMAIL PROTECTED] > Subject: [HACKERS] I just got it: PostgreSQL Application Server -- a new > project. > > > I have been harping for the last few days (years, actually) about tweaks > and > changes to PostgreSQL for a number of reasons ranging from session > management to static tables. I even had a notion to come up with msession > on > PostgreSQL. > > I have been incorporating full text search, recommendations, and a slew of > other features into PostgreSQL, but you know what? While it does touch > Postgre in a real sense, it is not strictly SQL. It is about how to create > applications with PostgreSQL. That's what we're missing, Coneptually, > PostgreSQL is strictly a database and the core team (rightly so) is > fundimentally happy with that aspect of it. > > Maybe we need a pgfoundary project called "PostgreSQL Application Server." > Like Apache Tomcat or regular apache or PHP, PostgreSQL could form the SQL > base of a far more intricate and flexable framework that encompases a lot > of > the various features that could provide "application sever" features from > PostgreSQL. > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Delaying the planning of unnamed statements until Bind
Tom Lane wrote: Oliver Jowett <[EMAIL PROTECTED]> writes: At execution time the executor can cache the results of expressions flagged as constant at execution, assuming there's somewhere safe to cache the result for just that execution (ExprState?). That would be the problem; there isn't anyplace appropriate. I'm not sure this is really worth pursuing... Bear with me for a moment then :) .. It'd be nice to have for cases where there's a frequently evaluated expensive expression that could be constant-folded if it wasn't parameterized. I guess that ExprState does not live long enough to be useful. How about a single-entry cache in the expression node itself, with cache validity tied to the ExprContext it was evaluated in? i.e. something like: use a global counter to assign a sufficiently-unique ID to each ExprContext, copy the context's ID to the cache when filling the cache, and only treat the cache as valid when the cache's ID matches the current context's ID. I took a quick look through the executor code, but couldn't see where STABLE function results are cached (for the same arguments). They aren't. STABLE is not a cache-enabling modifier: what it is actually for is to license the function to be used in an indexscan qualification. This is why the definition of STABLE refers to holding still throughout a table scan, rather than other reasonable alternatives one might consider (such as being constant throughout one transaction). Ok. The CREATE FUNCTION documentation is a bit misleading: it does specify the requirement to be immutable during a single table scan, but the text and examples that follow describe a stronger requirement. How about introducing a function modifier that provides stronger guarantees than STABLE, along the lines of "immutable during execution of a single SQL statement"? From a quick skim of pg_proc, it looks like most if not all of the existing STABLE functions also meet the stronger requirement. -O ---(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] Weird 'bit' type behaviour
Is there any reason for this behaviour: test=# select 1::bit; bit - 0 (1 row) test=# select '1'::bit; bit - 1 (1 row) Chris ---(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] Other bit oddities
What about these? test=# select B'1' << 4; ?column? -- 0 (1 row) test=# select B'1'::bit varying << 4; ?column? -- 0 (1 row) test=# select '1'::bit varying << 4; ?column? -- 0 (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I just got it: PostgreSQL Application Server -- a
The "PostgreSQL Enhanced Server" (How's that name? Maybe we call it Zerver and use PEZ?) idea is how to take the excellent core of PostgreSQL and productize it in much the same way distributions take the Linux kernel and may a GNU/Linux system. It would seem to me that this is more correct in the commercial space. Of course I am biased but what you are talking about sounds a whole lot like RedHat Enterprise versus Fedora etc J Even if I find the concepts as such very interesting, I think the term "Application Server" is very misleading. People would get very confused and place PostgreSQL in the same category as JBoss, Jonas, Apache Geronimo, IBM Websphere, BEA Weblogic to name a few well known App-servers. IMHO, you really need some other umbrella name for this. Kind regards, Thomas Hallgren ""Carl E. McMillin"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Jumping on that bandwagon with all 6 feet! Carl <|};-)> -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of [EMAIL PROTECTED] Sent: Friday, June 11, 2004 9:38 PM To: [EMAIL PROTECTED] Subject: [HACKERS] I just got it: PostgreSQL Application Server -- a new project. I have been harping for the last few days (years, actually) about tweaks and changes to PostgreSQL for a number of reasons ranging from session management to static tables. I even had a notion to come up with msession on PostgreSQL. I have been incorporating full text search, recommendations, and a slew of other features into PostgreSQL, but you know what? While it does touch Postgre in a real sense, it is not strictly SQL. It is about how to create applications with PostgreSQL. That's what we're missing, Coneptually, PostgreSQL is strictly a database and the core team (rightly so) is fundimentally happy with that aspect of it. Maybe we need a pgfoundary project called "PostgreSQL Application Server." Like Apache Tomcat or regular apache or PHP, PostgreSQL could form the SQL base of a far more intricate and flexable framework that encompases a lot of the various features that could provide "application sever" features from PostgreSQL. ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Re: [HACKERS] [PATCHES] Compiling libpq with VisualC
[EMAIL PROTECTED] wrote: What is the recommended way to create mutex objects (CreateMutex) from Win32 libraries? There must be a clean way like there is in pthreads. A mutex is inherently a global object. CreateMutex(NULL, FALSE, NULL) will return a handle to an unowned mutex. That's not the problem. Under pthread, it's possible to initialize a mutex from compile time: static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER; This means that the mutex is immediately valid, no races with the initialization. I couldn't find an equivalent Win32 feature. -- Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] File leak?
On Sun, 13 Jun 2004, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> (viz, log at the instant of file creation, and the replayer would have > >> to keep track of whether it sees the creating transaction commit and > >> delete the file if not). > > > I don't see how we could WAL log it because we don't fsync the WAL until > > our transaction completes, right, or are you thinking we would do a > > special fsync when we add the record? > > Right, we would have to XLogFlush the file-creation WAL record before we > could actually create the file. This is in line with the standard WAL > rule: the WAL record must hit disk before the data file change it > describes does. Assuming that the filesystem fsync's the created inode > immediately, that means we have to flush first. I'm afraid that's not enough. Checkpoints spoil it, think: 1. CREATE TABLE foobar ... 2. INSERT 3. 4. The replay would not see the file-creation WAL record. We need some additional stash for the pending file-creations to make them survive checkpoints. > I'm not sure what the performance implications of this would be; it's > likely that pushing the cost somewhere else would be better. I don't think that file creation is that common for it to matter.. - Heikki ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Releasing 7.4.3 ...
Marc G. Fournier wrote: > On Sun, 13 Jun 2004, Peter Eisentraut wrote: > > Marc G. Fournier wrote: > >> Anyone else, please test the tar ball for any bug/nits ... > >> specifically, Peter, can you check that I've built/included the > >> right documentation? > > > > Try reading the list of supported platforms at the bottom of the > > INSTALL file... > > k, and that is supposed to tell me what? There is supposed to be a table there; I just see unaligned gargage. The INSTALL file looks quite bad. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
