Re: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
On T, 2005-08-16 at 18:26 -0400, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Once more: > > I would like to get at least some answer, why my patch for enabling > > concurrent VACUUM was left out from 8.1. > > You did not respond to this: > http://archives.postgresql.org/pgsql-patches/2005-08/msg00238.php Somehow this did not reach me :( I'll answer this here: > Bruce Momjian writes: > >> Is there any particular reason for not putting it in 8.1 ? > > > I thought there was still uncertainty about the patch. Is there? > > Considerable uncertainty, in my mind. What we've got here is some > pretty fundamental hacking on the transaction visibility logic, and > neither Hannu nor anyone else has produced a convincing argument > that it's correct. "It hasn't failed yet in my usage" isn't enough > to give me a good feeling about it. Agreed. > Some specific concerns: > > * Given that VACUUM ANALYZE does create new output tuples stamped with > its xid, I'm unclear on what happens in pg_statistic with this code in > place. Actually any VACUUM, not only VACUUM ANALYSE, updates pg_class at the end. That's why I exclude only one of the transactions of the VACUUM command, and that transaction does not create any new tuples, it only removes old ones. > It seems entirely possible that someone might conclude the > analyze tuples are from a crashed transaction and mark them invalid > before the analyze can commit (notice TransactionIdIsInProgress does not > bother looking in PGPROC when the tuple xmin is less than RecentXmin). Once more, only 2nd transaction of LAZY VACUUM is affected, and that one does only (heap scan + clean indexes + clean heap) and _only_ removes old tuples. > * If the vacuum xact is older than what others think is the global xmin, > it could have problems with other vacuums removing tuples it should > still be able to see (presumably only in the system catalogs, so maybe > this isn't an issue, but I'm unsure). The cleanup transaction does no lookups in system catalogs. > A related scenario that I don't > think can be dismissed is someone truncating off part of pg_subtrans or > pg_multixact that the vacuum still needs. In my patch I specifically exclude TruncateSUBTRANS from using the inVacuum flag At the time I originally submitted my patch, GetOldestXmin was only used in VACUUM and CREATE INDEX, others had other means of getting oldest Xmin, and these were not affected by my patch. When I reworked it before last submit, i changed the only nwe use (in xlog.c, line 5165) to use a new version of GetOldestXmin with an extra flag tu tell it to NOT exlude transactions running vacuum. The transaction running the heap scan/cleanup part of the vacuum command only sets a new isVacuum flag, and this is only used by GetOldestXmin functions. Other means of getting OldestXmin still get exactly the old behaviour. GetOldestXmin() does exclude xmin's with isVacuum set only when called by other VACUUMS. this is controlled by the (new) second argument of GetOldestXmin(). So I think that both your concerns expressed here are _already_ addressed by the latest patch in: http://archives.postgresql.org/pgsql-patches/2005-07/msg00086.php Please check the actual patch and advise if anything is still missing. -- Hannu Krosing <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
David Fetter wrote: On Tue, Aug 16, 2005 at 01:17:27PM -0400, Gregory Maxwell wrote: I promise that the aggregate work required for all coders who know Python to switch to ruby is far far greater than the work required to fix the issues with pl/python. :) Are you certain? See above in re: what Guido had to say. I find the whole argument that, lack of an untrusted version of the PL means it should be deprecated, crazy. There are plenty of situations where you don't care that the PL is untrusted. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Josh Berkus wrote:
People:
How about we draft some criteria for inclusion of a PL in the main distro?
Suggestions:
1) The PL must be "stable" (that is, not capable of crashing the backend)
Check. (well, a more humble statement is perhaps to say that any bug
that would cause a crash would be considered critical and get immediate
attention. Shit happens).
2) The PL must be buildable only using --with-{lang} and createlang
(assuming that the user has the correct libraries)
PL/Java builds using the pgx stuff and needs no further config then an
environment setting that appoints the JVM. Adding a --with-java is
probably very easy once the code is included in the distro (I say
probably because I have no idea of how to do it).
3) There must be a regression test included, which tests both creating the
lang and creating+executing a small function in it.
PL/Java includes a bunch of tests today. I guess you have some test
harness where such tests can be plugged in?
4) The PL must have at least one maintainer who subscribes to
pgsql-hackers.
Check. And if more people wants to step in then I'm all for it.
5) It must be possible to build the PL without changing the licensing of
PostgreSQL (this excludes PL/R, unfortunately).
Check.
Controversial Criterion:
6) The PL should be buildable in "trusted" mode. (I vote no on this one)
Check. PL/Java always enables two language handlers, java and javaU.
Nevertheless, my vote would also be to exclude this criteria. The
important thing is that the user of an untrusted PL knows the implications.
I'd like to add one other criteria that PL/Java is lacking today but I
think every PL should have.
7) The PL language handler(s) must be created with an associated
VALIDATOR function.
I, myself, do not think that either popularity or inclusion of the language
in Linux distros should be a criterion. If PL/Haskell or PL/Smalltalk
catches on with *our* community it should be good enough for us. Heck,
were it not for the licensing and build issues, I'd be advocating strongly
fro PL/R.
I agree. Even if Java is very popular in general it is less so within
this community and that is what counts. A criterion that I think would
be valid though (and also likely relate to popularity) is of course if a
sponsor made a commitment and secured the continued evolution and
maintenance of the PL.
There's also another point that has not been brought up yet. Most PL's
use code that's inlined in the SQL function body. Java (like C) cannot
do that. So there are two categories of PL's; the ones that allow inline
code and the ones that require modules that contain the code to be
loaded somehow. PL/Java belongs to the latter. Not everyone is in favor
of that approach.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
On Tue, Aug 16, 2005 at 01:46:26PM -0700, David Fetter wrote: > On Tue, Aug 16, 2005 at 11:39:04PM +0300, Marko Kreen wrote: > > On Tue, Aug 16, 2005 at 10:38:37AM -0700, David Fetter wrote: > > > If somebody has figured out a way to make a PL/Python (without the > > > U), that's great, but nothing has happened on this front in a > > > couple of years, and Guido said that it was a problem with the > > > language that he wasn't going to fix. > > > > Could you provide a reference to that? > > Here's the word from Guido > > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00687.php Thanks. Although this does not seem as definite as you said it be, rather current Python architecture makes it harder than it should be. Btw, at least Zope guys have 'figured a out a way': http://marc.theaimsgroup.com/?l=python-dev&m=107666724918647&w=2 Only problem with their implementation is that they haven't updated it yet for Python 2.4. -- marko ---(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] Upcoming back-branch releases
On Mon, Aug 15, 2005 at 01:06:15PM -0400, Tom Lane wrote: > So, if you've got any pending patches for the back branches, now would > be a good time to get 'em done up and sent in. http://archives.postgresql.org/pgsql-patches/2005-07/msg00291.php -- marko ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] obtaining row locking information
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > To accomplish this I need to add following function into > > storage/ipc/procarray.c. This is similar to BackendPidGetProc() except > > that it accepts xid as an argument. Any objection? > > > if (xid == 0) /* never match dummy PGPROCs */ > > return NULL; > > I think this test should be against InvalidTransactionId, not "0", and > the comment is wrong (you are suppressing matches against idle PGPROCs). > > Also note the comment at the top of the function: once you release > ProcArrayLock you have no guarantee that the result means anything at > all; and unlike ProcSendSignal, you have no reason to think that the > target backend can't quit before you get another cycle. It might be > better to return the pid directly rather than assuming it'll still be > meaningful to indirect through a returned pointer. Agreed. > Also, what are you going to do about prepared transactions? They can > hold locks but they don't have PIDs. On the whole, I'm not sure this > is a good idea at all, because of that. For prepared transactions, just showing "0" pids are enough, I think. Assuming that in practice most transactions are not prepared ones, I think the function is not perfect, but is usefull enough for most DBAs. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming back-branch releases
Tom Lane wrote: The core committee has agreed that we need to do a set of releases in the back branches soon --- certainly 8.0 has accumulated a critical mass of changes since 8.0.3, and probably there's enough to justify updates of the 7.* branches too. We hope to get these out sometime next week, after the first 8.1 beta release is done. So, if you've got any pending patches for the back branches, now would be a good time to get 'em done up and sent in. I am going to look urgently at fixing the "no IPv6 on Windows" bug that came up yesterday. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] transactions not working properly ?
Hi, can any one describe how the transaction are being handled in postgres. i.e. function given below should actually insert the desire values in test table but it do not save them. START TRANSACTION; create or replace function testFunc() returns int as $$ declare x integer; begin x := 1; insert into test values (210,20); x := x/0; RETURN 0; exception when others then raise info 'error generated '; commit; RETURN 0; end; $$ language plpgsql; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
As there are two java procedural languages which are available for postgreSQL Josh asked for an explanation as to their differences. They are quite similar in that both of them run the function in a java vm, and are pre-compiled. Neither attempt to compile the code. The biggest difference is how they connect to the java VM. PL/Java uses Java Native Interfaces (JNI) and does a direct call into the java VM from the language handler. PL-J uses a network protocol to connect to a java VM. There are advantages and disadvantages to both approaches. + JNI is simpler, doesn't require a protocol, or an application container to manage the User Defined Functions - JNI requires that the vm runs on the server machine, and a separate vm be instantiated for every connection that calls a function. This is mitigated somewhat in java 1.5, by sharing data, however this may or may not be a Sun only feature ( does anyone know ); either way a separate vm is required for each connection. - startup time for the vm on the first call for the connection. - Possible ( not as likely any more ) for the java VM to take the server down. Using a network protocol such as a pl-j does has the following ( basically the opposite of the JNI (dis)advantages ) + The java VM does not have to run on the server. + Only one vm per server - More complex, requires a micro kernel application server to manage the UDF's currently http://loom.codehaus.org/ Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] do we need inet_ntop check?
Currently the IPv6 check in configure.in says this: HAVE_IPV6=no AC_CHECK_TYPE([struct sockaddr_in6], [AC_CHECK_FUNC(inet_ntop, [AC_DEFINE(HAVE_IPV6, 1, [Define to 1 if you have support for IPv6.]) HAVE_IPV6=yes])], [], [$ac_includes_default #include ]) AC_SUBST(HAVE_IPV6) However, we don't use inet_ntop anywhere in our code that I can see, either in the HEAD or REL8_0_STABLE branch. So why do we need that extra check (which fails on Windows)? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] transactions not working properly ?
Ali Baba <[EMAIL PROTECTED]> writes: > exception > when others then > raise info 'error generated '; > commit; > RETURN 0; > end; You can't COMMIT inside a function. -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] transactions not working properly ?
[This question would probably be more appropriate in pgsql-general than in pgsql-hackers.] On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba wrote: > can any one describe how the transaction are being > handled in postgres. I think you're talking about how PL/pgSQL exception handlers work with transactions. See the documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > function given below should actually insert the desire > values in test table but it do not save them. A complete test case would make it easier help. All we see in the example is the start of a transaction and the creation of a function -- we don't see how you're actually using it nor what output (e.g., error messages) it produces. > begin > x := 1; > insert into test values (210,20); > x := x/0; > > RETURN 0; > > exception > when others then > raise info 'error generated '; > commit; > RETURN 0; > end; The "Trapping Errors" documentation states: When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. Since the divide-by-zero error is in the same block as the INSERT, the INSERT is rolled back. Also, you can't issue COMMIT inside a function -- see the "Structure of PL/pgSQL" documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] transactions not working properly ?
Ali Baba wrote: > can any one describe how the transaction are being > handled in postgres. Pretty much the same as in any other SQL implementation, and you'd have the same problem in any database. Is this a homework assignment? Jeroen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Dave Cramer wrote: As there are two java procedural languages which are available for postgreSQL Josh asked for an explanation as to their differences. They are quite similar in that both of them run the function in a java vm, and are pre-compiled. Neither attempt to compile the code. The biggest difference is how they connect to the java VM. PL/Java uses Java Native Interfaces (JNI) and does a direct call into the java VM from the language handler. PL-J uses a network protocol to connect to a java VM. There are advantages and disadvantages to both approaches. + JNI is simpler, doesn't require a protocol, or an application container to manage the User Defined Functions - JNI requires that the vm runs on the server machine, and a separate vm be instantiated for every connection that calls a function. This is mitigated somewhat in java 1.5, by sharing data, however this may or may not be a Sun only feature ( does anyone know ); either way a separate vm is required for each connection. - startup time for the vm on the first call for the connection. - Possible ( not as likely any more ) for the java VM to take the server down. Using a network protocol such as a pl-j does has the following ( basically the opposite of the JNI (dis)advantages ) + The java VM does not have to run on the server. + Only one vm per server - More complex, requires a micro kernel application server to manage the UDF's currently http://loom.codehaus.org/ That's a pretty good explanation and ought to be published more widely. It's almost a pity that we couldn't have one project with a server setting saying how we want it to run. I seem to recall hearing of a Sun gadget in the works that would let a process connect to a running VM and load classes and run them. I have been a bit out of it on Java lately - does anyone know of such a thing, or is my memory failing again? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
Hannu Krosing <[EMAIL PROTECTED]> writes: > On T, 2005-08-16 at 18:26 -0400, Tom Lane wrote: >> Some specific concerns: >> >> * Given that VACUUM ANALYZE does create new output tuples stamped with >> its xid, I'm unclear on what happens in pg_statistic with this code in >> place. > Actually any VACUUM, not only VACUUM ANALYSE, updates pg_class at the end. > That's why I exclude only one of the transactions of the VACUUM command, and > that > transaction does not create any new tuples, it only removes old ones. vac_update_relstats isn't the issue because it doesn't create a new tuple. I was concerned about ANALYZE --- but since that's done in a separate transaction that's not marked inVacuum, it's not at risk. So OK, that's all right. >> * If the vacuum xact is older than what others think is the global xmin, >> it could have problems with other vacuums removing tuples it should >> still be able to see (presumably only in the system catalogs, so maybe >> this isn't an issue, but I'm unsure). > The cleanup transaction does no lookups in system catalogs. Oh? It certainly has to open relations and indexes. I think that all of that stuff may be done with SnapshotNow, rather than an xmin-related snap, but it's still nervous-making. >> A related scenario that I don't >> think can be dismissed is someone truncating off part of pg_subtrans or >> pg_multixact that the vacuum still needs. > In my patch I specifically exclude TruncateSUBTRANS from using the > inVacuum flag You missed vac_truncate_clog, though. > So I think that both your concerns expressed here are _already_ > addressed by the latest patch in: > http://archives.postgresql.org/pgsql-patches/2005-07/msg00086.php I have to admit that in my earlier message, I was looking at the version of the patch that Bruce had on his patch page --- which I now see was not the latest. The idea of making GetOldestXmin only conditionally ignore vacuums certainly makes it a lot safer. > Please check the actual patch and advise if anything is still missing. There's still a fair amount of breakage in this patch --- eg, in the VACUUM FULL case it manages to invoke *both* full_vacuum_rel and lazy_vacuum_rel --- but I think it can probably be made to work. I'll take another pass at it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries in non-default
Unfortunately, it looks like the allow_non_pic_in_shlib setting broke platypus: http://lnk.nu/pgbuildfarm.org/3l3.pl If I back that part of the patch out, playtypus works fine. On Fri, Aug 12, 2005 at 04:57:58PM -0400, Bruce Momjian wrote: > > Patch applied. Thanks. If we made plpython worse, we will hear about > it soon enough. The freebsd-specific changes seem safe, considering > they came from the FreeBSD port maintainers themselves. > > --- > > > Jim C. Nasby wrote: > > On Wed, Jul 13, 2005 at 01:24:17PM -0400, Andrew Dunstan wrote: > > > >In any case, I've cleared the conflict and I'm running a build right > > > >now. > > > > octopus is building again, and is back to the behavior I mentioned in > > http://archives.postgresql.org/pgsql-bugs/2005-07/msg00096.php. Is this > > something that should be fixed in code? There are two patches in the > > FreeBSD ports tree for postgresql 8: > > > > [EMAIL PROTECTED]:18]/usr/ports/databases/postgresql80-server/files:47>cat > > patch-plpython-Makefile patch-src-makefiles-Makefile.freebsd > > --- src/pl/plpython/Makefile.orig Fri Nov 19 20:23:01 2004 > > +++ src/pl/plpython/MakefileTue Dec 28 23:32:16 2004 > > @@ -9,7 +9,7 @@ > > # shared library. Since there is no official way to determine this > > # (at least not in pre-2.3 Python), we see if there is a file that is > > # named like a shared library. > > -ifneq (,$(wildcard $(python_libdir)/libpython*$(DLSUFFIX)*)) > > +ifneq (,$(wildcard $(python_libdir)/../../libpython*$(DLSUFFIX)*)) > > shared_libpython = yes > > endif > > > > --- src/makefiles/Makefile.freebsd.orig Fri Nov 19 01:41:39 2004 > > +++ src/makefiles/Makefile.freebsd Tue Dec 21 02:44:09 2004 > > @@ -11,7 +11,7 @@ > > ifeq ($(findstring sparc,$(host_cpu)), sparc) > > CFLAGS_SL = -fPIC -DPIC > > else > > -CFLAGS_SL = -fpic -DPIC > > +CFLAGS_SL = -fPIC -DPIC > > endif > > > > > > @@ -29,3 +29,5 @@ > > endif > > > > sqlmansect = 7 > > + > > +allow_nonpic_in_shlib = yes > > > > The first of these patches makes me think that octopus might actually be > > finding the wrong library, though things are fine on platypus with > > python 2.3 (octopus is running 2.4). > > > > I'm upgrading platypus to 2.4 right now to see what that changes, if > > anything. > > -- > > Jim C. Nasby, Database Consultant [EMAIL PROTECTED] > > Give your computer some brain candy! www.distributed.net Team #1828 > > > > Windows: "Where do you want to go today?" > > Linux: "Where do you want to go tomorrow?" > > FreeBSD: "Are you guys coming, or what?" > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > Bruce Momjian| http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries in non-default
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Unfortunately, it looks like the allow_non_pic_in_shlib setting broke > platypus: http://lnk.nu/pgbuildfarm.org/3l3.pl > If I back that part of the patch out, playtypus works fine. So what's different between platypus and the machines where it works? We might need a version check or something ... regards, tom lane ---(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] [GENERAL] Cascades Failing
On Tuesday 16 August 2005 09:17, Stephan Szabo wrote: > On Tue, 16 Aug 2005, Tom Lane wrote: > > [ redirected to -hackers ] > > > > I wrote: > > > This suggests that we need a way to prevent immediate execution of > > > freshly queued triggers at the end of a command fired by an FK trigger. > > > If we could move them to the end of the trigger queue that the FK > > > operation itself is in, things would work reasonably well I think. > > > > After a quick look through the code, it seems like the way to do this > > is to add an extra bool parameter "nest_triggers" to _SPI_pquery, which > > when false would simply suppress its calls to AfterTriggerBeginQuery > > and AfterTriggerEndQuery --- thus causing any queued triggers to be > > queued in the same trigger list the FK is in. We'd then expose this > > parameter (only) via SPI_execute_snapshot, which is intended only for > > RI trigger use anyway. > > This seems right to me. I'd thought that SQL wanted the user triggers to > be run after the updating directly, but reading it again, SQL03 at least > seems to just talk about adding state changes for after triggers to the > current trigger context AFAICS which means that the above seems to be what > is requested by the spec in general. > > > I think this would take some generalization of afterTriggerInvokeEvents, > > which now might or might not find the target rel in the EState it's > > passed, but otherwise it doesn't seem too invasive. Thoughts? > > That doesn't seem too bad really, looking at afterTriggerInvokeEvents it > doesn't look like it'd be that much work to change it to handle that case. > I can put a patch together to see what it looks like. I have a realworld test case of delete cascade (approx 90 cascaded tables, some more than 8 levels deep) failing on 8.0.3 (and 8.1) , this is one of a few issues that is preventing me from upgrading a couple of 7.4 boxen to 8.x, if you need testers for this patch, please let me know and I'll be glad to try it out and see if it solves the cascade problems I am experiencing. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER INDEX OWNER TO
Where are we on this? --- Christopher Kings-Lynne wrote: > > Yeah, I suppressed that alternative a few weeks ago, thinking that it > > was not sensible since we don't really support having indexes owned > > by anyone except the owner of the parent table. Not sure what to do > > about the fact that pg_dump has been emitting it though. Maybe reduce > > the error to a warning, and make it a no-op? > > Hmmm I so don't remember having pg_dump issue that - but I guess I must > have... > > Chris > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 5: don't forget to increase your free space map settings
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Andrew Dunstan wrote: Dave Cramer wrote: As there are two java procedural languages which are available for postgreSQL Josh asked for an explanation as to their differences. They are quite similar in that both of them run the function in a java vm, and are pre-compiled. Neither attempt to compile the code. The biggest difference is how they connect to the java VM. PL/Java uses Java Native Interfaces (JNI) and does a direct call into the java VM from the language handler. PL-J uses a network protocol to connect to a java VM. There are advantages and disadvantages to both approaches. + JNI is simpler, doesn't require a protocol, or an application container to manage the User Defined Functions - JNI requires that the vm runs on the server machine, and a separate vm be instantiated for every connection that calls a function. This is mitigated somewhat in java 1.5, by sharing data, however this may or may not be a Sun only feature ( does anyone know ); either way a separate vm is required for each connection. - startup time for the vm on the first call for the connection. - Possible ( not as likely any more ) for the java VM to take the server down. Using a network protocol such as a pl-j does has the following ( basically the opposite of the JNI (dis)advantages ) + The java VM does not have to run on the server. + Only one vm per server - More complex, requires a micro kernel application server to manage the UDF's currently http://loom.codehaus.org/ I think Dave miss a couple of important points. 1. Speed. One major reason for moving code from the middle tier down to the database is that you want to execute the code close to the actual persistence mechanisms in order to minimize network traffic and maximize throughput. 2. A growing percentage of db-clients utilize some kind of connection pool (an overwelming amount of the java clients certanly do), which minimizes the problem with startup times. 3. Transaction visiblity. A function that in turn issues new SQL calls must do that wihtin the scope of the caller transaction. A remote process must hence call back into it's caller. PL/Java has its own JDBC driver that interacts directly with SPI. 4. Isolation. Using separate VM's, instabilities in the VM can only affect one single connecton. One VM can be debugged or monitored without affecting the others. No data can be inadvertidely moved between connections, etc. I try to shed more light on the pros and cons here: http://gborg.postgresql.org/project/pljava/genpage.php?jni_rationale That's a pretty good explanation and ought to be published more widely. It's almost a pity that we couldn't have one project with a server setting saying how we want it to run. There are a couple of reasons that make me a bit reluctant to join the projects: PL/Java have no dependencies at all besides a Java Runtime Environment (or GCJ). PL/J reqires a fair amount of other modules just to compile. PL/Java is at release 1.1 and have a community of users. To my knowledge, PL/J has not reached its first release yet. PL/Java and PL/J use completely different approaches and share almost no code. The code that we do share (public interfaces, manly for trigger management) is published at the Maven repository at ibiblio.org. I think it's better to keep the two projects separate. But I also think that it is extremely important that we ensure that the user experience is similar for both projects so that there's nothing to prevent a server setting that decides which one to use provided both are present. Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
I find the whole argument that, lack of an untrusted version of the PL means it should be deprecated, crazy. There are plenty of situations where you don't care that the PL is untrusted. Yes you are absolutely correct. However my argument was more than that. It contained: The fact that it was only untrusted Not moving forward. plPython is basically in a static state, I can't do (AFAIK) in plPython today that I couldn't do 2 years ago. PostgreSQL is moving forward at an increasing rate. The pl languages that are in core should at least try to keep up with the feature set. Also there is the maintainability perspective. I may write a one time function in plpython because python is my prefered language. I would not however use it as my primary language for procedures because it can't be trusted. Believe me, if plPython could be trusted I would be all over anyone who suggested deprecating it. Python is my preferred language. Sincerely, Joshua D. Drake Joe -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries
Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: Unfortunately, it looks like the allow_non_pic_in_shlib setting broke platypus: http://lnk.nu/pgbuildfarm.org/3l3.pl If I back that part of the patch out, playtypus works fine. So what's different between platypus and the machines where it works? We might need a version check or something ... platypus is amd64, not x86. AFAIK, amd64 does not allow non-PIC code to be mixed with PIC code in the same object, whereas it's just fine for x86. In NetBSD pkgsrc, we've had to fix a lot of software that makes this same assumption. Cheers, -- Johnny Lam <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Crash while trying to log in with nonexistent role
I can reproduce this with a couple-of-days-old CVS tip: alvherre=# \c - test FATAL: role "test" does not exist And the server log says FATAL: role "test" does not exist TRAP: BadState(«!(((bool) ((OuterUserId) != ((Oid) 0», Archivo: «/pg/source/00orig/src/backend/utils/init/miscinit.c», Línea: 336) -- Alvaro Herrera () "[PostgreSQL] is a great group; in my opinion it is THE best open source development communities in existence anywhere."(Lamar Owen) ---(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: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
Hannu Krosing <[EMAIL PROTECTED]> writes: > Please check the actual patch and advise if anything is still missing. While testing this I realized that it does not in fact work as advertised. It will only exclude long-running VACUUMs from other VACUUMs' OldestXmin if *all* the transactions in the system are lazy VACUUMs. If there is even one regular transaction in the system, that transaction will include the VACUUMs in its MyProc->xmin, and thence GetOldestXmin will have to include them in its result. AFAICS the only way to fix this would be to exclude inVacuum transactions from GetSnapshotData's calculations as well. That makes the patch far more invasive, and I'm not confident I can work out all the implications. (In particular, the consequences for TransactionIdIsInProgress look bad. I don't think we want a VACUUM to be seen as not-in-progress.) So I'm bouncing this patch again... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
On 17-Aug-05, at 12:40 PM, Thomas Hallgren wrote: Andrew Dunstan wrote: Dave Cramer wrote: As there are two java procedural languages which are available for postgreSQL Josh asked for an explanation as to their differences. They are quite similar in that both of them run the function in a java vm, and are pre-compiled. Neither attempt to compile the code. The biggest difference is how they connect to the java VM. PL/Java uses Java Native Interfaces (JNI) and does a direct call into the java VM from the language handler. PL-J uses a network protocol to connect to a java VM. There are advantages and disadvantages to both approaches. + JNI is simpler, doesn't require a protocol, or an application container to manage the User Defined Functions - JNI requires that the vm runs on the server machine, and a separate vm be instantiated for every connection that calls a function. This is mitigated somewhat in java 1.5, by sharing data, however this may or may not be a Sun only feature ( does anyone know ); either way a separate vm is required for each connection. - startup time for the vm on the first call for the connection. - Possible ( not as likely any more ) for the java VM to take the server down. Using a network protocol such as a pl-j does has the following ( basically the opposite of the JNI (dis)advantages ) + The java VM does not have to run on the server. + Only one vm per server - More complex, requires a micro kernel application server to manage the UDF's currently http://loom.codehaus.org/ I think Dave miss a couple of important points. 1. Speed. One major reason for moving code from the middle tier down to the database is that you want to execute the code close to the actual persistence mechanisms in order to minimize network traffic and maximize throughput. I think until there are actual benchmarks, there are too many variables here to suggest one is faster than the other. The overhead of having multiple java vm's is not easily estimated. Even with a connection pool, consider the memory footprint of even 10 java VM's 2. A growing percentage of db-clients utilize some kind of connection pool (an overwelming amount of the java clients certanly do), which minimizes the problem with startup times. 3. Transaction visiblity. A function that in turn issues new SQL calls must do that wihtin the scope of the caller transaction. A remote process must hence call back into it's caller. PL/Java has its own JDBC driver that interacts directly with SPI. PL-J maintains transaction visibility, it has it's own JDBC driver as well. The protocol between the language handler and the java portion is based upon the FE/BE protocol which made it easy to use pg's JDBC driver with some modification. 4. Isolation. Using separate VM's, instabilities in the VM can only affect one single connecton. One VM can be debugged or monitored without affecting the others. No data can be inadvertidely moved between connections, etc. Loom deals with data integrity, debugging would have to be done by a remote debug connection and can connect to any thread. I try to shed more light on the pros and cons here: http:// gborg.postgresql.org/project/pljava/genpage.php?jni_rationale That's a pretty good explanation and ought to be published more widely. It's almost a pity that we couldn't have one project with a server setting saying how we want it to run. There are a couple of reasons that make me a bit reluctant to join the projects: PL/Java have no dependencies at all besides a Java Runtime Environment (or GCJ). PL/J reqires a fair amount of other modules just to compile. PL-J requires one other module, which the build environment will fetch automatically to compile. PL/Java is at release 1.1 and have a community of users. To my knowledge, PL/J has not reached its first release yet. PL/Java and PL/J use completely different approaches and share almost no code. The code that we do share (public interfaces, manly for trigger management) is published at the Maven repository at ibiblio.org. I think it's better to keep the two projects separate. But I also think that it is extremely important that we ensure that the user experience is similar for both projects so that there's nothing to prevent a server setting that decides which one to use provided both are present. Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] SPI: ERROR: no snapshot has been set
For future reference, I got around this error (no snapshot has been set) by removing the use of SPI and just using heap_open / heap_beginscan / heap_endscan / heap_close. It's only slightly more code but it works irrespective of the state of the backend. Have a nice day, On Sat, Aug 13, 2005 at 03:59:56PM +0200, Martijn van Oosterhout wrote: > Good guess, I am inside a type input function trying to use SPI. My > questions are: -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpAelbGsnm5E.pgp Description: PGP signature
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Thomas, Dave, I did *NOT* want to start another discussion about what approach is superior. Keep in mind that for us non-Java geeks most of your argument is pure ancient Greek. What I wanted to establish is: potentially, we will have two Java PLs with Postgres. If we do, we need to have a clear documentation section explaining why there are two and why a user might want to choose one or the other. This explanation should be comprehensible to a neophyte Java programmer and even to a DBA who doesn't do Java but has to install it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] do we need inet_ntop check?
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Currently the IPv6 check in configure.in says this: > HAVE_IPV6=no > AC_CHECK_TYPE([struct sockaddr_in6], > [AC_CHECK_FUNC(inet_ntop, > [AC_DEFINE(HAVE_IPV6, 1, [Define to 1 if > you have support for IPv6.]) > HAVE_IPV6=yes])], > [], > [$ac_includes_default > #include ]) > AC_SUBST(HAVE_IPV6) > However, we don't use inet_ntop anywhere in our code that I can see, > either in the HEAD or REL8_0_STABLE branch. So why do we need that extra > check (which fails on Windows)? I can't see any reason for it either. AFAICT, all we actually depend on to compile the #ifdef HAVE_IPV6 code is (a) struct sockaddr_in6 and (b) the macro AF_INET6. Arguably we should have an explicit test for the latter, but unless someone exhibits a header file that has the struct but not the macro, the struct test seems sufficient. I'll remove the configure test. I assume you want it gone from the 8.0 branch too... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] do we need inet_ntop check?
Tom Lane wrote: AFAICT, all we actually depend on to compile the #ifdef HAVE_IPV6 code is (a) struct sockaddr_in6 and (b) the macro AF_INET6. Arguably we should have an explicit test for the latter, but unless someone exhibits a header file that has the struct but not the macro, the struct test seems sufficient. I'll remove the configure test. I assume you want it gone from the 8.0 branch too... Yes please. Unfortunately, this doesn't get us over the IPv6 hump on Windows, not even if we include ws2tcpip.h. It appears that we need either to pull in getaddrinfo from ws2_32.dll (which Windows platforms have this? It should be available for them all, according to MSDN). or make out own routines decipher ipv6 addresses. So far today I haven't had any luck with the former. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
Hannu Krosing <[EMAIL PROTECTED]> writes: > On K, 2005-08-17 at 14:48 -0400, Tom Lane wrote: >> While testing this I realized that it does not in fact work as >> advertised. It will only exclude long-running VACUUMs from other >> VACUUMs' OldestXmin if *all* the transactions in the system are lazy >> VACUUMs. If there is even one regular transaction in the system, >> that transaction will include the VACUUMs in its MyProc->xmin, and >> thence GetOldestXmin will have to include them in its result. > Only if these regular transactions are running in SERIALIZABLE isolation > level, else MyProc->xmin is not set inside GetSnapshotData. Better read the code again. The first snap in *any* transaction sets MyProc->xmin. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] bitmap scan issues 8.1 devel
Hello,
Doing some testing on upcoming 8.1 devel and am having serious issues
with new bitmap index scan feature. It is easy to work around (just
disable it) but IMO the planner is using it when a regular index scan
should be strongly favored. The performance of the bitmapscan in my
usage is actually quite a bit worse than a full sequential scan.
here is a query which does this:
explain analyze execute
data1_read_next_product_structure_file_0('012241', '', '', '002', 1);
Here is the 8.0/bitmap off plan:
Limit (cost=0.00..45805.23 rows=5722 width=288) (actual
time=0.070..0.072 rows=1 loops=1)
-> Index Scan using product_structure_file_pkey on
product_structure_file (cost=0.00..45805.23 rows=5722 width=288)
(actual time=0.063..0.063 row
s=1 loops=1)
Index Cond: ((ps_parent_code)::text >= ($1)::text)
Filter: ps_parent_code)::text > ($1)::text) OR
(ps_group_code >= $2)) AND (((ps_parent_code)::text > ($1)::text) OR
(ps_group_code > $2)
OR ((ps_section_code)::text >= ($3)::text)) AND (((ps_parent_code)::text
> ($1)::text) OR (ps_group_code > $2) OR ((ps_section_code)::text >
($3)::tex
t) OR ((ps_seq_no)::smallint > $4)))
Total runtime: 0.185 ms
Here is the 8.1 with bitamp on:
Limit (cost=3768.32..3782.63 rows=5722 width=288) (actual
time=2287.488..2287.490 rows=1 loops=1)
-> Sort (cost=3768.32..3782.63 rows=5722 width=288) (actual
time=2287.480..2287.480 rows=1 loops=1)
Sort Key: ps_parent_code, ps_group_code, ps_section_code,
ps_seq_no
-> Bitmap Heap Scan on product_structure_file
(cost=187.84..3411.20 rows=5722 width=288) (actual time=19.977..514.532
rows=47355 loops=1)
Recheck Cond: ((ps_parent_code)::text >= ($1)::text)
Filter: ps_parent_code)::text > ($1)::text) OR
(ps_group_code >= $2)) AND (((ps_parent_code)::text > ($1)::text) OR
(ps_group_code
> $2) OR ((ps_section_code)::text >= ($3)::text)) AND
(((ps_parent_code)::text > ($1)::text) OR (ps_group_code > $2) OR
((ps_section_code)::text > ($3
)::text) OR ((ps_seq_no)::smallint > $4)))
-> Bitmap Index Scan on product_structure_file_pkey
(cost=0.00..187.84 rows=18239 width=0) (actual time=19.059..19.059
rows=47356 loo
ps=1)
Index Cond: ((ps_parent_code)::text >= ($1)::text)
Total runtime: 2664.034 ms
Here is the prepared statement definition:
prepare data1_read_next_product_structure_file_0 (character varying,
character, character varying, int4, int4)
as select 1::int4, * from data1.product_structure_file
where ps_parent_code >= $1 and
(ps_parent_code > $1 or ps_group_code >= $2) and
(ps_parent_code > $1 or ps_group_code > $2 or
ps_section_code >= $3) and
(ps_parent_code > $1 or ps_group_code > $2 or
ps_section_code > $3 or ps_seq_no > $4)
order by ps_parent_code, ps_group_code, ps_section_code,
ps_seq_no
limit $5
Aside: this is the long way of writing
select 1::int4, * from data1.product_structure_file where
(ps_parent_code, ps_group_code, ps_section_code, ps_seq_no) > ($1, $2,
$3, $4) limit %5
which is allowed in pg but returns the wrong answer.
Merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
On K, 2005-08-17 at 14:48 -0400, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Please check the actual patch and advise if anything is still missing. > > While testing this I realized that it does not in fact work as > advertised. It will only exclude long-running VACUUMs from other > VACUUMs' OldestXmin if *all* the transactions in the system are lazy > VACUUMs. If there is even one regular transaction in the system, > that transaction will include the VACUUMs in its MyProc->xmin, and > thence GetOldestXmin will have to include them in its result. Only if these regular transactions are running in SERIALIZABLE isolation level, else MyProc->xmin is not set inside GetSnapshotData. As my transactions mostly run in READ COMMITTED mode I did not care about those and later forgot about it. > AFAICS the only way to fix this would be to exclude inVacuum > transactions from GetSnapshotData's calculations as well. That > makes the patch far more invasive, and I'm not confident I can work > out all the implications. (In particular, the consequences for > TransactionIdIsInProgress look bad. I don't think we want a VACUUM > to be seen as not-in-progress.) > > So I'm bouncing this patch again... Please don't. Even with current functionality it is part of solving the problem of being able to vacuum small tables while vacuuming big ones at the same time. In a scenario, where I use it, there are lot of OLTP (30-50ms) transactions. These are run in READ COMMITTED mode, but even if I needed them to be in SERIALIZABLE mode (or my reasoning about MyProc->xmin is wrong), I can find (or force if needed) a few ms window where no other transaction is running to start my VACUUM LAZY in a mode it can actually clean up the tables. This is still much better than not being able to do it at all. I'm ready to write the documentation explaining it all in detail to those really needing it. -- Hannu Krosing <[EMAIL PROTECTED]> ---(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: Missing CONCURRENT VACUUM (Was: [HACKERS] Release notes for
On K, 2005-08-17 at 16:45 -0400, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > On K, 2005-08-17 at 14:48 -0400, Tom Lane wrote: > >> While testing this I realized that it does not in fact work as > >> advertised. It will only exclude long-running VACUUMs from other > >> VACUUMs' OldestXmin if *all* the transactions in the system are lazy > >> VACUUMs. If there is even one regular transaction in the system, > >> that transaction will include the VACUUMs in its MyProc->xmin, and > >> thence GetOldestXmin will have to include them in its result. > > > Only if these regular transactions are running in SERIALIZABLE isolation > > level, else MyProc->xmin is not set inside GetSnapshotData. > > Better read the code again. The first snap in *any* transaction sets > MyProc->xmin. Can't find the place :( Could you point to the file / function that does this. -- Hannu Krosing <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] SHM_LOCK under Linux ... do we use this?
Under FreeBSD, we have an option (kern.ipc.shm_use_phys=1) that doesn't allow shared memory to be swap'd ... under Linux, there is apparently an application level option that can be used for this purpose: A privileged user can prevent or allow swapping of a shared memory segment with the following cmds: SHM_LOCKprevents swapping of a shared memory segment. The user must fault in any pages that are required to be present after ... I've done a grep through the code, to see if its something that we do use, and it doesn't seem to come back with anything ... I believe its considered common knowledge that 'swapping' for a database is evil, so am wondering if there is some way that we can make use of this to help reduce/eliminate that from happening? The "A privileged user can..." part makes me think no, but figured I'd ask ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries
On Wed, Aug 17, 2005 at 01:22:16PM -0400, Johnny Lam wrote: > Tom Lane wrote: > >"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > >>Unfortunately, it looks like the allow_non_pic_in_shlib setting broke > >>platypus: http://lnk.nu/pgbuildfarm.org/3l3.pl > > > > > >>If I back that part of the patch out, playtypus works fine. > > > > > >So what's different between platypus and the machines where it works? > >We might need a version check or something ... > > platypus is amd64, not x86. AFAIK, amd64 does not allow non-PIC code to > be mixed with PIC code in the same object, whereas it's just fine for > x86. In NetBSD pkgsrc, we've had to fix a lot of software that makes > this same assumption. Damn, I'm sorry, I totally mis-interpreted this. Turns out the failures are due to a perl problem. They appear to be from http://lnk.nu/developer.postgresql.org/3l8.c. http://lnk.nu/pgbuildfarm.org/3l9.pl is the log for the latest failure. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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] gettime() - a timeofday() alternative
Jim C. Nasby wrote: > On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > > Brendan Jurd <[EMAIL PROTECTED]> writes: > > > Regarding the statement_timestamp() ... if the entire query path is > > > parser -> rewriter -> planner/optimiser -> executor, what point in > > > that path would be considered the true start of the "statement"? > > > > IIRC, what we actually intended that to mean is the time of receipt of > > the current interactive command --- that is, it gets set in the > > postgres.c outer loop, not anywhere in the parser/etc path. Otherwise > > there's not a unique answer (consider statements issued inside SQL > > functions for instance). > > ISTM that it would be useful to be able to use timestamp_statement > within a function though... although I guess timestamp_clock might > suffice in most cases. Another consideration is that this is a potential > source of confusion; people could easily think that timestamp_statement > would operate the same inside a function as it would outside. > > Would it be reasonable to add one more timestamp that works the same > inside and outside a function? In either case, can anyone think of a > less-ambiguous name for timestamp_statement? timestamp_client_statement? That highlights it is when the client sends the statement. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: 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] gettime() - a timeofday() alternative
Bruce Momjian writes: > Jim C. Nasby wrote: >> On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: >>> IIRC, what we actually intended that to mean is the time of receipt of >>> the current interactive command --- that is, it gets set in the >>> postgres.c outer loop, not anywhere in the parser/etc path. Otherwise >>> there's not a unique answer (consider statements issued inside SQL >>> functions for instance). >> Would it be reasonable to add one more timestamp that works the same >> inside and outside a function? In either case, can anyone think of a >> less-ambiguous name for timestamp_statement? > timestamp_client_statement? That highlights it is when the client sends > the statement. timestamp_command, maybe, would convey the right image. (I don't think we need yet a fourth flavor of this, nor do I see anything about it that "works differently inside and outside a function".) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] gettime() - a timeofday() alternative
On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > Brendan Jurd <[EMAIL PROTECTED]> writes: > > Regarding the statement_timestamp() ... if the entire query path is > > parser -> rewriter -> planner/optimiser -> executor, what point in > > that path would be considered the true start of the "statement"? > > IIRC, what we actually intended that to mean is the time of receipt of > the current interactive command --- that is, it gets set in the > postgres.c outer loop, not anywhere in the parser/etc path. Otherwise > there's not a unique answer (consider statements issued inside SQL > functions for instance). ISTM that it would be useful to be able to use timestamp_statement within a function though... although I guess timestamp_clock might suffice in most cases. Another consideration is that this is a potential source of confusion; people could easily think that timestamp_statement would operate the same inside a function as it would outside. Would it be reasonable to add one more timestamp that works the same inside and outside a function? In either case, can anyone think of a less-ambiguous name for timestamp_statement? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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] SHM_LOCK under Linux ... do we use this?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > A privileged user can prevent or allow swapping of a shared memory > segment with the following cmds: > SHM_LOCKprevents swapping of a shared memory segment. The user > must fault in any pages that are required to be present > after ... > I've done a grep through the code, to see if its something that we do use, and > it doesn't seem to come back with anything ... No, we don't. "privileged user" means root, so it's not possible for us to set that. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] gettime() - a timeofday() alternative
> From: Tom Lane [mailto:[EMAIL PROTECTED] > Bruce Momjian writes: > > Jim C. Nasby wrote: > >> On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > >>> IIRC, what we actually intended that to mean is the time > of receipt of > >>> the current interactive command --- that is, it gets set in the > >>> postgres.c outer loop, not anywhere in the parser/etc > path. Otherwise > >>> there's not a unique answer (consider statements issued inside SQL > >>> functions for instance). > > >> Would it be reasonable to add one more timestamp that > works the same > >> inside and outside a function? In either case, can anyone > think of a > >> less-ambiguous name for timestamp_statement? > > > timestamp_client_statement? That highlights it is when the > client sends > > the statement. > > timestamp_command, maybe, would convey the right image. > > (I don't think we need yet a fourth flavor of this, nor do I > see anything > about it that "works differently inside and outside a function".) Here's what I thought was going to happen: psql> BEGIN; -- sets timestamp_transaction(); psql> SELECT func1(); -- sets timestamp_statement() func1: SELECT something; -- doesn't set timestamp_statement(), because it's in a function call func2(); return; psql> SELECT something; -- sets timestamp_statement() again Maybe I just mis-understood and each statement that's issued will update timestamp_statement(). If that's the case I think we're fine (I can't really think of a use-case for timestamp_command() myself...). OTOH, if the intention is to do what I outlined in the above timeline, I think we should also have timestamp_command(), and change timestamp_statement() so that it always indicates the timestamp at the start of the current statement. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Dave, Some responses inline. As a reaction to what Josh just wrote - "Keep in mind that for us non-Java geeks most of your argument is pure ancient Greek" - I'll try to talk in generic terms from now on and not mention Java since the difference between our solutions have nothing whatsoever to do with Java. Java is what we have in common :-) I'm all in favor of writing a good descriptive chapter that explains the differences between the solutions. Dave Cramer wrote: 1. Speed. One major reason for moving code from the middle tier down to the database is that you want to execute the code close to the actual persistence mechanisms in order to minimize network traffic and maximize throughput. I think until there are actual benchmarks, there are too many variables here to suggest one is faster than the other. The overhead of having multiple java vm's is not easily estimated. Even with a connection pool, consider the memory footprint of even 10 java VM's I think it would be a very good idea to jointly create a test bench where we can measure performance. Not only could we make just comparisons between our solutions, we could also use it to improve on them. The results could also be included in the documentation section that Josh requests and serve as facts for decision making. The reason I brougth the speed issue up is that I felt that you mentioned PL/Java's two weakest points (memory consumption and startup time) but failed to mention the weakest point of PL-J (slow inter-process calls). A side-note: The footprint of 10 VM's doesn't scare me that much. A modern VM that doesn't run an app-server and no GUI doesn't consume that much (they sure used to though). On my Windows-XP box, one VM typically consumes about 20-40Mb virtual memory and 6-13Mb real memory. I currently have 50 VM's running simultaniously without problems. 3. Transaction visiblity. A function that in turn issues new SQL calls must do that wihtin the scope of the caller transaction. A remote process must hence call back into it's caller. PL/Java has its own JDBC driver that interacts directly with SPI. PL-J maintains transaction visibility, it has it's own JDBC driver as well. The protocol between the language handler and the java portion is based upon the FE/BE protocol which made it easy to use pg's JDBC driver with some modification. Ok, Bad heading. My point was that for each call you make from the backend to the VM, the VM must make a call back to the caller, receive the results, and then propagate those results back to the caller that actually had them in the first place. That's a lot of unnecessary network traffic. The relevance of this should of course also be tested in the test bench. PL-J requires one other module, which the build environment will fetch automatically to compile. PL-J Requires a specific build environment. Thats one dependency. In addition there are 6 dependencies listed in its project descriptor. The problems that arise when you depend heavily on other modules are not just related to how they are obtained. You need to keep track of bugs that concern you, their fixes and release versions. You want to know about new features that you might want to use (while still maintaining backward compatibility of course), and you must watch out for inter-component dependencies and version conflicts that might arise every time you bump a version of something. There might be licensing issues, etc. When PL/Java was designed I made a serious effort to avoid all that. Hence my concern. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bitmap scan issues 8.1 devel
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Doing some testing on upcoming 8.1 devel and am having serious issues > with new bitmap index scan feature. It is easy to work around (just > disable it) but IMO the planner is using it when a regular index scan > should be strongly favored. I think blaming the bitmap code is the wrong response. What I see in your example is that the planner doesn't know what the LIMIT value is, and accordingly is favoring a plan that isn't going to get blown out of the water if the LIMIT is large. I'd suggest not parameterizing the LIMIT. (But hmm ... I wonder if we could use estimate_expression_value for LIMIT items, instead of handling only simple Consts as the code does now?) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Cascades Failing
On Tue, 16 Aug 2005, Stephan Szabo wrote: > On Tue, 16 Aug 2005, Tom Lane wrote: > > > I think this would take some generalization of afterTriggerInvokeEvents, > > which now might or might not find the target rel in the EState it's > > passed, but otherwise it doesn't seem too invasive. Thoughts? > > That doesn't seem too bad really, looking at afterTriggerInvokeEvents it > doesn't look like it'd be that much work to change it to handle that case. > I can put a patch together to see what it looks like. I did some work on this, and I'm getting a couple of other failures from other parts of the foreign key regression test (specifically an error that is no longer erroring in a multi-column on update set default). I'm going to need to look more closely to see if I can figure out why. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with libraries
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: >>> Unfortunately, it looks like the allow_non_pic_in_shlib setting broke >>> platypus: http://lnk.nu/pgbuildfarm.org/3l3.pl > Damn, I'm sorry, I totally mis-interpreted this. Turns out the failures > are due to a perl problem. Yeah, but the nonpic change caused that. Can anyone tell me which machine types (host_cpu values) FreeBSD does support non-PIC code in shlibs for? Is it only x86, and if so exactly what's the host_cpu setting? We need to throw a conditional into Makefile.freebsd. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pthread stack on FreeBSD WAS: HEAD doesn't cope with
Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: Damn, I'm sorry, I totally mis-interpreted this. Turns out the failures are due to a perl problem. Yeah, but the nonpic change caused that. Can anyone tell me which machine types (host_cpu values) FreeBSD does support non-PIC code in shlibs for? Is it only x86, and if so exactly what's the host_cpu setting? We need to throw a conditional into Makefile.freebsd. Judging from the config.guess script that the PostgreSQL distribution uses, the host_cpu value should be "x86_64". I think this mixing of non-relocatable and relocatable code is a problem that exists for all amd64 machines, regardless of the operating system. When Googling for the linker error message, I've seen it crop up for users running NetBSD, FreeBSD and Linux. I think you'll want to add the same conditional setting of allow_nonpic_in_shlib in src/makefiles/Makefile.linux based on the host_cpu value. Cheers, -- Johnny Lam <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Crash while trying to log in with nonexistent role
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I can reproduce this with a couple-of-days-old CVS tip: > TRAP: BadState(«!(((bool) ((OuterUserId) != ((Oid) 0», Archivo: > «/pg/source/00orig/src/backend/utils/init/miscinit.c», Línea: 336) Ooops, should have tested the full-AbortTransaction-to-clean-up change a bit more. Will fix. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pl/Ruby, deprecating plPython and Core
Joshua D. Drake wrote: I find the whole argument that, lack of an untrusted version of the PL means it should be deprecated, crazy. There are plenty of situations where you don't care that the PL is untrusted. Yes you are absolutely correct. However my argument was more than that. Right. I was responding to the entire thread that was headed in the direction of saying that just because a language doe not have a trusted PL version, it should be removed. As others have said, I find myself using PL/pgSQL when I need trusted, and frequently use other languages when I need untrusted. And in most of my experience, I don't even care if the language is trusted or untrusted. There are plenty of use cases for both. Joe ---(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] still looking for getaddrinfo
[redirecting to -hackers in the hope of more eyes ;-) ]
(goal - to use native ip6 support in Windows)
First, after the change Tom committed earlier, I added a test for
ws2_32.dll to the win32 port section of config.in:
AC_CHECK_LIB(ws2_32, main)
That worked fine.
The I added this line to src/port/win32/sys/socket.h:
#include
This brought me to the getaddrinfo test.
config.in has this:
# system's version of getaddrinfo(), if any, may be used only if we found
# a definition for struct addrinfo; see notes in src/include/getaddrinfo.h
if test x"$ac_cv_type_struct_addrinfo" = xyes ; then
AC_REPLACE_FUNCS([getaddrinfo])
else
AC_LIBOBJ(getaddrinfo)
fi
Which generates the code below with the failure show.
As Petr Jelinek showed me, If we remove the declaration "char
getaddrinfo();" and instead do "#include "
the test succeeds, as we want it to.
My question is, for the autoconf gurus, how do we write an autoconf test
that does this right? Do we need a custom test?
cheers
andrew
Original Message
Subject:[pgsql-hackers-win32] looking for getaddrinfo
Date: Wed, 17 Aug 2005 17:35:17 -0400
From: Andrew Dunstan <[EMAIL PROTECTED]>
To: pgsql-hackers-win32 <[EMAIL PROTECTED]>
I'm having trouble with a configure test for getaddrinfo on Windows. The
relevant part of config.log is as below. Any help would be appreciated.
According to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/winsock/getaddrinfo_2.asp
ws2_32.dll is what I should need, and is what I am using.
thanks
andrew
configure:14625: checking for getaddrinfo
configure:14682: gcc -o conftest.exe -O2 -Wall -Wmissing-prototypes
-Wpointer-arith -fno-strict-aliasing -I./src/include/port/win32
-DEXEC_BACKEND -Wl,--allow-multiple-definition
conftest.c -lz -lws2_32 -lwsock32 -lm >&5
C:/DOCUME~1/pgrunner/LOCALS~1/Temp/ccGQbaaa.o(.text+0x1a):conftest.c:
undefined reference to `getaddrinfo'
C:/DOCUME~1/pgrunner/LOCALS~1/Temp/ccGQbaaa.o(.data+0x0):conftest.c:
undefined reference to `getaddrinfo'
configure:14688: $? = 1
configure: failed program was:
| /* confdefs.h. */
|
| #define PACKAGE_NAME "PostgreSQL"
| #define PACKAGE_TARNAME "postgresql"
| #define PACKAGE_VERSION "8.1devel"
| #define PACKAGE_STRING "PostgreSQL 8.1devel"
| #define PACKAGE_BUGREPORT "pgsql-bugs@postgresql.org"
| #define PG_VERSION "8.1devel"
| #define DEF_PGPORT 5432
| #define DEF_PGPORT_STR "5432"
| #define PG_VERSION_STR "PostgreSQL 8.1devel on i686-pc-mingw32,
compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1)"
| #define PG_KRB_SRVNAM "postgres"
| #define HAVE_LIBM 1
| #define HAVE_LIBWSOCK32 1
| #define HAVE_LIBWS2_32 1
| #define HAVE_LIBZ 1
| #define HAVE_SPINLOCKS 1
| #define STDC_HEADERS 1
| #define HAVE_SYS_TYPES_H 1
| #define HAVE_SYS_STAT_H 1
| #define HAVE_STDLIB_H 1
| #define HAVE_STRING_H 1
| #define HAVE_MEMORY_H 1
| #define HAVE_STRINGS_H 1
| #define HAVE_INTTYPES_H 1
| #define HAVE_STDINT_H 1
| #define HAVE_UNISTD_H 1
| #define HAVE_GETOPT_H 1
| #define HAVE_PWD_H 1
| #define HAVE_SYS_SOCKET_H 1
| #define HAVE_SYS_TIME_H 1
| #define HAVE_UTIME_H 1
| #define HAVE_WCHAR_H 1
| #define HAVE_WCTYPE_H 1
| #define HAVE_NETINET_IN_H 1
| #define HAVE_STRINGIZE 1
| #define HAVE_FUNCNAME__FUNC 1
| #define HAVE_STRUCT_SOCKADDR_STORAGE 1
| #define HAVE_STRUCT_SOCKADDR_STORAGE_SS_FAMILY 1
| #define HAVE_STRUCT_ADDRINFO 1
| #define HAVE_STRUCT_OPTION 1
| #define HAVE_INT_TIMEZONE
| #define ACCEPT_TYPE_RETURN unsigned int PASCAL
| #define ACCEPT_TYPE_ARG1 unsigned int
| #define ACCEPT_TYPE_ARG2 struct sockaddr *
| #define ACCEPT_TYPE_ARG3 int
| #define HAVE_CBRT 1
| #define HAVE_FCVT 1
| #define HAVE_MEMMOVE 1
| #define HAVE_TOWLOWER 1
| #define HAVE_UTIME 1
| #define HAVE_WCSTOMBS 1
| #define HAVE_DECL_FDATASYNC 0
| #define HAVE_IPV6 1
| #define HAVE_SNPRINTF 1
| #define HAVE_VSNPRINTF 1
| #define HAVE_DECL_SNPRINTF 1
| #define HAVE_DECL_VSNPRINTF 1
| #define HAVE_ISINF 1
| #define HAVE_GETOPT 1
| #define HAVE_RINT 1
| #define HAVE_STRDUP 1
| #define HAVE_STRERROR 1
| #define HAVE_STRTOL 1
| #define HAVE_STRTOUL 1
| /* end confdefs.h. */
| /* Define getaddrinfo to an innocuous variant, in case
declares getaddrinfo.
|For example, HP-UX 11i declares gettimeofday. */
| #define getaddrinfo innocuous_getaddrinfo
|
| /* System header to define __stub macros and hopefully few prototypes,
| which can conflict with char getaddrinfo (); below.
| Prefer to if __STDC__ is defined, since
| exists even on freestanding compilers. */
|
| #ifdef __STDC__
| # include
| #else
| # include
| #endif
|
| #undef getaddrinfo
|
| /* Override any gcc2 internal prototype to avoid an error. */
| #ifdef __cplusplus
| extern "C"
| {
| #endif
| /* We use char because int might match the return type of a gcc2
|builtin and then its argument prototype would still apply. */
| char getaddrinfo ();
| /* The GNU C library defines this for functions which it implements
| to always f
Re: [HACKERS] SHM_LOCK under Linux ... do we use this?
On Wed, 17 Aug 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: A privileged user can prevent or allow swapping of a shared memory segment with the following cmds: SHM_LOCKprevents swapping of a shared memory segment. The user must fault in any pages that are required to be present after ... I've done a grep through the code, to see if its something that we do use, and it doesn't seem to come back with anything ... No, we don't. "privileged user" means root, so it's not possible for us to set that. Ya, that's what I was figuring, but figured someone with more Linux knowledge might know a 'loophole' we could exploit :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] How to secure PostgreSQL Data for distribute?
Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise.
Re: [HACKERS] How to secure PostgreSQL Data for distribute?
Premsun, > I need to distribute my application that use PostgreSQL as database to > my customer. But I still have some questions in my mind on database > security. I understand that everybody who get my application database will > be have a full control permission on my database in case that PostgreSQL > already installed on their computer and they are an administrator on > PostgreSQL. So that mean data, structure and any ideas contain in database > will does not secure on this point. Is my understanding correct? > > What is the good way to make it all secure? Please advise. I think you have a different definition of "security" from most of us. There are techniques you could use that would prevent your customer from having easy direct access to the database on his machine -- but they would also prevent him from making backups or recovering from a computer problem. They'd also require a lot of work on your part. Overall, if your goal is to prevent your customer from having control of their own data, I don't think an open-source database is the best choice for you. ;-b -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to secure PostgreSQL Data for distribute?
Am Donnerstag, den 18.08.2005, 09:56 +0700 schrieb Premsun Choltanwanich: > Dear All, > > I need to distribute my application that use PostgreSQL as > database to my customer. But I still have some questions in my mind on > database security. I understand that everybody who get my application > database will be have a full control permission on my database in case > that PostgreSQL already installed on their computer and they are an > administrator on PostgreSQL. So that mean data, structure and any > ideas contain in database will does not secure on this point. Is my > understanding correct? > > What is the good way to make it all secure? Please advise. Postgres is secure as it garanties your data integrity (as long as the underlying os plays well). It is also secure in a way to protect unauthorized access from 3rd party users if the DBA doesnt want it. (Usuall account and access). However, if you deliver software to a customer, the software is the product and the customer can technically do whatever she wants with it. Your copyright on the application protects you legally from someone using exactly your table layout etc. in another application to sell to another customer (provided the model isnt too simple or so state of the art that anybody must trivially come to the same solution) All the other ideas of IP (intellectual property) seem very silly when you talk software. Personally I've yet to see a model worth any thoughts about 'protecting' or obfuscating it. (That means, something not any talented database designer can create, faced with the same problem) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
