Re: [HACKERS] Avoid MVCC using exclusive lock possible?
On Tuesday 02 March 2004 06:29, Paul Tillotson wrote:
> However, for this to be useful, your table must not have any indexes,
> views, foreign keys, sequences, triggers, etc., or else you must be
> prepared to re-create all of them using application level code.
Which isn't a big deal is it? You can write a single function to create entire
object and it's dependency. It is one time job but can save lots of time at
runtime.
> I imagine this would break lots of things, but it would be nice if
> instead of Shridhar's rename step (see below) one could do this:
>
> $table1node = query("SELECT relfilenode FROM pg_class WHERE relname =
> '$old_table';");
> $table2node = query("SELECT relfilenode FROM pg_class WHERE relname =
> '$new_table';");
> exec("UPDATE pg_class SET relfilenode = $table2node WHERE relname =
> '$old_table';");
> exec("UPDATE pg_class SET relfilenode = $table1node WHERE relname =
> '$new_table';");
>
> You would of course need to change the relfilenode for all of the
> toasted columns and indexes as well in the same atomic step, but it
> seems like this might be more compatible with postgresql's MVCC model
> than other ideas suggested.
I still don't understand what is not so good about rename. All the indexes
remain there. Views need updation, I agree. Missed that last time. But what
you are suggesting is a part of rename if not complete of it.
I would always prefer to let PG handle these kind of details. Not very
comfortable with mucking around catalogs especially if there exists an
alternative.
Shridhar
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] IN and ANY
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > Hmm, the draft seems to be broken since I can only find ANY defined for > subqueries in other sections, and not for value lists. Strange but not > uncommon. Now I don't know what the standard says about this. Maybe > someone with the sql99 spec wants to check. I think you are reading the term "equivalent" as meaning an equivalence in both directions. It looks to me that the spec's definition of is (mis)using the term to mean "is defined as". In SQL92 I see 1) Let IVL be an . ( IVL ) is equivalent to the : ( VALUES IVL ) ... 4) The expression RVC IN IPV is equivalent to RVC = ANY IPV These two rules together define both forms of IN in terms of the "= ANY (subquery)" construct. But surely the first rule is not meant to say that VALUES is a noise word. So this has to be a one-way implication. Accordingly I think you are in error to suggest that "= ANY (valuelist)" is supposed to work. I think ANY is only supposed to have a table subquery to the right. I don't have a strong opinion about "IN array", but am worried that allowing it would create ambiguity about which interpretation is meant. Is the left-hand side supposed to be compared against the whole array or each array member? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Pl/Java - next step?
On Mon, Feb 23, 2004 at 05:14:09PM +0100, Peter Eisentraut wrote: > Thomas Hallgren wrote: > > 1. Select Pl/Java_JNI. > > 2. Select Pl/Java_remote > > 3. Choose both and agree on the SQL + Java semantics > > 4. Make the postmaster spawn threads rather than processes > > (controversial? Nah :-) ) > > Option 5 (or 0) would be to use GCJ. This is likely to be the fastest > and most lightweight solution, but perhaps not the most featureful. Hm, last time I tried this it just SIGSEGV'd the backend after loading libgcj.so or something like that. I didn't peek further because I feel strange in Java land. -- Alvaro Herrera () La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander) ---(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] IN and ANY
The IN and ANY constructs works with different kinds of collections to the
right. This is the current situation:
SUB QUERY VALUE LIST ARRAY
IN X X
ANY X X
The SQL standard specifies that = ANY should be equivalent to IN
(including the value list case) but it is not.
Take for example this:
select 'a' = ANY ('{a,b}');
which according to the SQL spec should evaluate to False but in pg
evaluates to True. Look for at this run in pg:
dennis=# select 'a' IN ('{a,b}');
?column?
--
f
(1 rad)
dennis=# select 'a' = ANY ('{a,b}');
?column?
--
t
I only have the SQL200x draft to check. And in section 8.4 it's
clearly stated that "rvc IN ipv" is equivalent to "rvc = ANY ipv" and an
ipv is defined to be either a subquery or a value list.
*** some time later ***
Hmm, the draft seems to be broken since I can only find ANY defined for
subqueries in other sections, and not for value lists. Strange but not
uncommon. Now I don't know what the standard says about this. Maybe
someone with the sql99 spec wants to check.
The question is also what makes sense. I'm not too fond of the above
situation, but I'm not sure if anything can be done now.
--
/Dennis Björklund
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.3.6 bundled ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > sizes all look about right, if someone wants to download and confirm? The full gz and bz2 tarballs match what I have here. Didn't check the partial tarballs. 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] Check Constraints and pg_dump
On Mon, 1 Mar 2004, Tom Lane wrote: > Curt Sampson <[EMAIL PROTECTED]> writes: > > Can you explain how to do this? There is no reference to a plan in the > > contract table; the constraint just checks to see that, if a contract > > exists, there is at least one plan referencing that contract. > > There is of course a foreign key constraint used in the plan table to > > make sure that the contract exists. > > If so, how would it be possible to create a plan before creating the > contract? I don't think the OP's requirements are clearly thought out. You create the contract and its first plan at the same time, inserting the plan first. It works fine. > > At any rate, I am not sure why pg_dump has to know or care what check > > constraints do; if it simply treated them as it does all the other > > constraints, and applied them after all the data are loaded, wouldn't > > the problem just go away? > > If we did that we'd be slowing bulk loads (since each added check > constraint would incur an additional scan over the table) Certainly, but do we not already pay that price for all non-check constraints? And it slows loads a lot more when you have to edit your dumps because they are otherwise unloadable. At any rate, this being a database, I'm inclined more towards correctness than speed. > and decreasing legibility of the dumped schema (surely you will agree > that it's more readable to keep the constraint in the CREATE TABLE > command). I agree that it's more readable, yes. But again, do we not already pay that price for all non-check constraints? > There is code in CVS tip pg_dump to split out a check constraint from > the parent table when this is the only way to break a circular > dependency. But I'm disinclined to apply that transformation all the > time, especially when the only reason to do so is to support a misuse > of check constraints. I'm having great difficulty understanding why this is a "misuse." What is the proper way to check that a contract cannot exist without at least one plan? > Check constraints are not intended to handle > cross-table checks, and I'm unwilling to buy into any suggestion that > we should consider that a supported use. So how do we handle it? > We have talked in the past about supporting SQL's "CREATE ASSERTION" > command, which *is* intended to describe cross-table conditions. > I don't recall that anyone had good ideas about a reasonably efficient > implementation though. I would happily settle for an inefficent implementation; that would give me the choice of correctness versus efficiency, rather than having no choice at all. > In the meantime, if what's wanted is a one-time check at row insertion, > the right way to express that behavior is with an ON INSERT trigger. That's not an adequate check; it would allow you later to delete the plan without deleting the contract. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] CHECK constraints inconsistencies
Rod Taylor <[EMAIL PROTECTED]> writes: > On Mon, 2004-03-01 at 20:43, Bruno Wolff III wrote: >> Tom Lane <[EMAIL PROTECTED]> wrote: >>> Michael Glaesemann <[EMAIL PROTECTED]> writes: In both cases, the CHECK constraint uses a function that is stable or volatile. It was suggested that functions used in CHECK constraints be restricted to immutable, >>> >>> This seems reasonable to me. I'm a bit surprised we do not have such a >>> check already. >> >> There may be times you want to do this. For example you may want a timestamp >> to be in the past. In this case as long as it was in the past when the > Agreed that this is useful behaviour, but a trigger is usually a better > mechanism for confirming such data as you really only want to check it > when the value is changed. Yes. I was just arguing in a different thread that triggers are the right way to express one-time checks. A constraint notionally expresses an always-true condition. (The SQL spec defines this more formally as a condition that must hold at the completion of every statement or every transaction, depending on the "deferrability" property of the constraint.) We presently support only constraints that are of a form that need only be checked at row insert or update time. It would be inconsistent with the spec to consider that part of the fundamental semantics of check constraints, though --- it's just an implementation restriction. Someday we might want to remove that restriction. Requiring CHECK functions to be immutable is consistent with the existing implementation restriction. Misusing them in the way Bruno suggests is a cute trick, but I think we have to consider it to be gaming the implementation, not a supported way to do things. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Check Constraints and pg_dump
Curt Sampson <[EMAIL PROTECTED]> writes: > Can you explain how to do this? There is no reference to a plan in the > contract table; the constraint just checks to see that, if a contract > exists, there is at least one plan referencing that contract. > There is of course a foreign key constraint used in the plan table to > make sure that the contract exists. If so, how would it be possible to create a plan before creating the contract? I don't think the OP's requirements are clearly thought out. > At any rate, I am not sure why pg_dump has to know or care what check > constraints do; if it simply treated them as it does all the other > constraints, and applied them after all the data are loaded, wouldn't > the problem just go away? If we did that we'd be slowing bulk loads (since each added check constraint would incur an additional scan over the table) and decreasing legibility of the dumped schema (surely you will agree that it's more readable to keep the constraint in the CREATE TABLE command). There is code in CVS tip pg_dump to split out a check constraint from the parent table when this is the only way to break a circular dependency. But I'm disinclined to apply that transformation all the time, especially when the only reason to do so is to support a misuse of check constraints. Check constraints are not intended to handle cross-table checks, and I'm unwilling to buy into any suggestion that we should consider that a supported use. We have talked in the past about supporting SQL's "CREATE ASSERTION" command, which *is* intended to describe cross-table conditions. I don't recall that anyone had good ideas about a reasonably efficient implementation though. In the meantime, if what's wanted is a one-time check at row insertion, the right way to express that behavior is with an ON INSERT trigger. 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
[HACKERS] 7.3.6 bundled ...
Will create all the linages in the morning, but the bundle is created under /pub/source/v7.3.6 on the main web site (soon to be on the mirrors) ... I added code to the mk script for the 7.3.x branch so that bz2 copies are also made ... sizes all look about right, if someone wants to download and confirm? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] CHECK constraints inconsistencies
On Mon, 2004-03-01 at 20:43, Bruno Wolff III wrote: > On Mon, Mar 01, 2004 at 20:28:02 -0500, > Tom Lane <[EMAIL PROTECTED]> wrote: > > Michael Glaesemann <[EMAIL PROTECTED]> writes: > > > In both cases, the CHECK constraint uses a function that is stable or > > > volatile. It was suggested that functions used in CHECK constraints be > > > restricted to immutable, > > > > This seems reasonable to me. I'm a bit surprised we do not have such a > > check already. > > There may be times you want to do this. For example you may want a timestamp > to be in the past. In this case as long as it was in the past when the Agreed that this is useful behaviour, but a trigger is usually a better mechanism for confirming such data as you really only want to check it when the value is changed. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Check Constraints and pg_dump
On Thu, 26 Feb 2004, Tom Lane wrote: > Jonathan Scott <[EMAIL PROTECTED]> writes: > > The functions and tables create just fine, but when it gets to the > > COPY part of the sql script, it tries to load tables in what really is > > the wrong order. The check constraint is making sure there is a "plan" > > before there is a "contract", yet pg_dump is trying to load the > > contract table before there is anything in the plan table. > > Shouldn't you be using a foreign key for that? Can you explain how to do this? There is no reference to a plan in the contract table; the constraint just checks to see that, if a contract exists, there is at least one plan referencing that contract. There is of course a foreign key constraint used in the plan table to make sure that the contract exists. > I don't see any reasonable way that pg_dump can be expected to > understand that a check constraint expresses a relationship between two > tables. The semantics of check constraints aren't right for it anyway. What other constraint could one use for a situation like this? At any rate, I am not sure why pg_dump has to know or care what check constraints do; if it simply treated them as it does all the other constraints, and applied them after all the data are loaded, wouldn't the problem just go away? cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CHECK constraints inconsistencies
On Mon, Mar 01, 2004 at 20:28:02 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Michael Glaesemann <[EMAIL PROTECTED]> writes: > > In both cases, the CHECK constraint uses a function that is stable or > > volatile. It was suggested that functions used in CHECK constraints be > > restricted to immutable, > > This seems reasonable to me. I'm a bit surprised we do not have such a > check already. There may be times you want to do this. For example you may want a timestamp to be in the past. In this case as long as it was in the past when the data was entered it will continue to be in the past (barring someone resetting the system time). This is something someone might actually check unlike comparing to random numbers. I think just noting that check constraints are only checked on inserts and updates and that this means that check constraints using volatile or stable functions need to be well thought out. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
Paul Tillotson <[EMAIL PROTECTED]> writes: > I use this type of approach when mirroring data from a foxpro database > (yuck) to a read-only postgres database. It is quicker and cleaner than > deleting all of the rows and inserting them again (TRUNCATE is not > transaction safe, which I need). Just FYI, TRUNCATE *is* transaction safe as of 7.4. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CHECK constraints inconsistencies
Michael Glaesemann <[EMAIL PROTECTED]> writes: > In both cases, the CHECK constraint uses a function that is stable or > volatile. It was suggested that functions used in CHECK constraints be > restricted to immutable, This seems reasonable to me. I'm a bit surprised we do not have such a check already. Of course, a user could easily get into the sort of situation you describe anyway, just by lying about the volatility labeling of a user-defined function. But at least we could say it was his fault then ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Tablespaces
"Simon Riggs" <[EMAIL PROTECTED]> writes: > Gavin Sherry wrote: >> I do not intend to work on such a system for the initial introduction of >> table spaces. The problem is, of course, knowing when you're actually out >> of space in a table space in any given transaction. Given that WAL is on a >> different partition (at least for the moment) the table space will not >> have transaction X's data written to it until after transaction X is >> finished. And we cannot error out a transaction which is already >> commited. As long as the kernel doesn't lie about file extension, we will not commit any transaction that requires a disallowed increase in the allocated size of data files, because allocation of another table page is checked with the kernel during the transaction. So on most filesystems (maybe not NFS) the problem Gavin is worried about doesn't exist. > You're absolutely right about the not-knowing when you're out of space > issue. However, if the xlog has been written then it is not desirable, > but at least acceptable that the checkpoint/bgwriter cannot complete on > an already committed txn. It's not the txn which is getting the error, > that's all. Right. This is in fact not a fatal situation, as long as you don't run out of preallocated WAL space. For a recent practical example of our behavior under zero-free-space conditions, see this thread: http://archives.postgresql.org/pgsql-hackers/2004-01/msg00530.php particularly the post-mortem here: http://archives.postgresql.org/pgsql-hackers/2004-01/msg00606.php Barring one small bug, the database would likely have stayed up, and continued to service at least the read-only transactions, until Chris got around to freeing some disk space. I think it is sufficient (at least in the near term) to expect people to use partition size limits if they want to control database size --- that is, make a partition of the desired size and put the database directory in there. Tablespaces as per the design we are discussing would make it easier to apply such a policy to a sub-area of a database cluster than it is today, but they needn't in themselves implement the restriction. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
I use this type of approach when mirroring data from a foxpro database
(yuck) to a read-only postgres database. It is quicker and cleaner than
deleting all of the rows and inserting them again (TRUNCATE is not
transaction safe, which I need).
However, for this to be useful, your table must not have any indexes,
views, foreign keys, sequences, triggers, etc., or else you must be
prepared to re-create all of them using application level code.
I imagine this would break lots of things, but it would be nice if
instead of Shridhar's rename step (see below) one could do this:
$table1node = query("SELECT relfilenode FROM pg_class WHERE relname =
'$old_table';");
$table2node = query("SELECT relfilenode FROM pg_class WHERE relname =
'$new_table';");
exec("UPDATE pg_class SET relfilenode = $table2node WHERE relname =
'$old_table';");
exec("UPDATE pg_class SET relfilenode = $table1node WHERE relname =
'$new_table';");
You would of course need to change the relfilenode for all of the
toasted columns and indexes as well in the same atomic step, but it
seems like this might be more compatible with postgresql's MVCC model
than other ideas suggested.
Regards,
Paul Tillotson
Shridhar Daithankar wrote:
I am sure people have answered the approach you have suggested so let me
suggest a workaround for your problem.
You could run following in a transaction.
- begin
- Create another table with exact same structure
- write a procedure that reads from input table and updates the value in
between
- drop the original table
- rename new table to old one
- commit
- analyze new table
Except for increased disk space, this approach has all the good things
postgresql offers. Especially using transactable DDLs it is huge benefit. You
certainly do save on vacuum.
If the entire table is updated then you can almost certainly get things done
faster this way.
HTH
Shridhar
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(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] 7.3.6 for Monday ... still a go?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > On Sat, 28 Feb 2004, Tom Lane wrote: >> AFAIK we are good to go in terms of the code --- there are no open >> issues that I'd want to back-patch to 7.3. We just need release notes. >> But I don't have time this weekend to do the release notes either... > 'k, once you or Bruce get a sec to do the release notes, I'll tag/bundle > it up ... Okay, I've committed release notes and updated the version numbers. She's ready to tag. 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] Tablespaces
>Gavin Sherry > On Fri, 27 Feb 2004 [EMAIL PROTECTED] wrote: > > Most people I know want tablespaces in order to limit or preallocate the > > disk space used by a table or database in addition to controlling the > > physical location of a table or database. > I do not intend to work on such a system for the initial introduction of > table spaces. The problem is, of course, knowing when you're actually out > of space in a table space in any given transaction. Given that WAL is on a > different partition (at least for the moment) the table space will not > have transaction X's data written to it until after transaction X is > finished. And we cannot error out a transaction which is already commited. > > The solution is to keep track of free space and error out at some > percentage of free space remaining. But I don't want to complicate > tablespaces too much in 7.5. You're absolutely right about the not-knowing when you're out of space issue. However, if the xlog has been written then it is not desirable, but at least acceptable that the checkpoint/bgwriter cannot complete on an already committed txn. It's not the txn which is getting the error, that's all. Hmmm...I'm not sure that we'll be able or should avoid the out of space situation completely. The question is...what will we do when we hit it? It doesn't matter whether you stop at 100% or 90% or whatever, you still have to stop and then what? Stay up as long as possible hopefully: If there wasn't enough space to write to the tablespace, going into recovery won't help the situation either; youre still out of space until you fix that. We now have the option not to crash, since it might be perfectly viable to keep on chugging away on one Tablespace even though all txn work on the out-of-space tablespace is frozen/barred etc. Sounds like a refinement, but something to keep in mind at the design stage if we can. The problem is that tablespaces do complicate space management (that's what people want though, so that's OK). That complicates admin and so pg will hit many more out of space errors than we've seen previously. Trying to work out how to spot these ahead of time, accept user defined limits on each tablespace etc sounds like extra complexity for the initial drop. I guess my own suggested approach is to start by handling the error cases, then go back and try to avoid some of them. All of this exposes for me the complication that doing PITR and tablespaces at the same time is likely to be more complex for us both than either had envisaged. The reduced complexity for PITR was what I was shooting for, also! I'm happy to work together on any issues that arise. For PITR, I think we would need: - a very accessible list of tablespace locations, so taking a full physical database backup can be easily accomplished using OS utilities. Hopefully a list maintained external to the database? We have the equivalent now with env variables. - decisions about what occurs when for-whatever-reason one or more tablespaces are not recoverable from backup? - it might be desirable to allow recovery with less than all of the original tablespces - it might also be desirable to allow recovery when the tablespaces txn Ids don't match (though that is forbidden on many other dbms) Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
>Shridhar Daithankar > > Recently, I ran a huge update on an Integer column affecting 100 million > > rows in my database. What happened was my disk space increased in size > and > > my IO load was very high. It appears that MVCC wants to rewrite each row > > (each row was about 5kB due to a bytea column). In addition, VACUUM > needs > > to run to recover space eating up even more IO bandwidth. > > I am sure people have answered the approach you have suggested so let me > suggest a workaround for your problem. > > You could run following in a transaction. > > - begin > - Create another table with exact same structure > - write a procedure that reads from input table and updates the value in > between > - drop the original table > - rename new table to old one > - commit > - analyze new table > > Except for increased disk space, this approach has all the good things > postgresql offers. Especially using transactable DDLs it is huge benefit. > You > certainly do save on vacuum. > > If the entire table is updated then you can almost certainly get things > done > faster this way. I second this approach. Space usage similar, time faster. For a procedure, you can use an Insert Select with a Case statement instead of the UPDATE step. This type of approach is in use in many other DBMS. I would vote strongly (how is that possible?) against doing anything at all to MVCC. It works, it's brilliant and doesn't fall foul of anybody else's patent rights. It would be very hard to re-write anything as good and the time spent would double other requirements. MySQL has multiple storage managers and that must slow them down enormously trying to test or even fine tune things. Best Regards, Simon Riggs ---(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] Server Side PL support
The Pl/Java (the JNI version) will utilize a class loader that uses tables in the SQLJ schema. Java has a fairly extensive mechanism for "module" loading so I think it's hard to collaborate on that. The Java standard came up with an idea that I think is pretty neat that other pl's might use. When a module (a jar file) is initially loaded into the database, it may contain a deployment descriptor that contains SQL statements that will be executed. Typically those statements creates functions, grants access etc. A corresponding descriptor exists for unload. Regarding the lib.so as such, I think it would be very nice if PostgreSQL followed the standard and prepended "lib" to the library name on a Unix machine (I want the SQL create statement for the module should refer to "pljava" regardless of operating system). I can't name the library pljava.so on Unix since the Java loader will expect the standard name. Regards, Thomas Hallgren "Andrew Dunstan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Yes. I am looking at a few of these things (preloading, intra-perl > calling, array and tuple return), and I understand that CommandPrompt is > doing some plperl work too. They already have a plperl which does > triggers. My question was not "what functionality do we need from PLs?" > but rather "what do PLs need from the core for good support?" I > particularly had catalog support in mind, but there could be other areas > too. > > I'm glad to see you support the efforts to make plperl something more > useful. One idea I had for a GForge/GBorg project when that gets going > is a place for collaborating on experimental plperl work, before it gets > merged back to the core. > > cheers > > andrew > > > > elein wrote: > > >This is a very interesting topic. Joe Conway > >has a very good idea of pl requirements since > >he just implemented pl/R. > > > >Some requirements for pl languages are these: > > * support query execution > > * support trigger functions > > * allocating storage for per statement function calls > > This is like the SD[] dictionary in plpythonu. > > * support for all built-in datatypes, e.g. easy > > array support for pl languages which have > > natural arrays, sets or dictionaries. > > * enable easy fastpath functionality > > or similar pl to pl function calls > > > >Note that array support, trigger and query support > >for plperl does not yet exist. > > > >IMHO extended support for plperl should have a relatively > >high priority. We are actively reaching out to the > >perl community and full support of the interface is > >important. Collaboration on the implementation is > >also possible--it has been discussed with some perl folks. > > > >elein > >[EMAIL PROTECTED] > > > > > >On Sun, Feb 29, 2004 at 02:20:19PM -0500, Andrew Dunstan wrote: > > > > > >>I have been taking a brief look at pltcl, and particularly its ability > >>to preload modules. By comparison with most of the core product this > >>seems to be somewhat out of date and unpolished (e.g. hardcoded path to > >>libpgtcl.so, no use of schemas for the supporting tables, lack of > >>comments). Since my understanding of tcl is extremely rusty, I didn't > >>dig further than that. However, I am interested in getting a similar > >>facility working for plperl, and thus wanted to start a discussion on > >>what general facilities could/should be made available to server side > >>PLs. Or should we just assume that each PL will create it's own support > >>tables? > >> > >>cheers > >> > >>andrew > >> > >> > >>---(end of broadcast)--- > >>TIP 7: don't forget to increase your free space map settings > >> > >> > > > > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hey, ya! =))
Larry Rosenman <[EMAIL PROTECTED]> writes: >> okay, if I'm reading you right here, this is one that virus checkers >> wouldn't pick up?? or is clamav's signatures just not updated yet? :( > I had to pick up an extra.dat for McAfee, and this is BRAND NEW (3/1/4). > I don't think ClamAV's been updated yet for it. Something with the forged return address of "[EMAIL PROTECTED]" got through earlier today, too: http://archives.postgresql.org/pgsql-general/2004-03/msg00013.php Looks like another instance of the same virus. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Hey, ya! =))
--On Monday, March 01, 2004 15:19:17 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: On Mon, 1 Mar 2004, Larry Rosenman wrote: --On Monday, March 01, 2004 15:02:50 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: > > are you and larryr the same person? *scratch head* No, but when I got the file, I fed it to www.webimmune.net and after feeding the .zip the password from the message, it told me that it was a virus. okay, if I'm reading you right here, this is one that virus checkers wouldn't pick up?? or is clamav's signatures just not updated yet? :( I had to pick up an extra.dat for McAfee, and this is BRAND NEW (3/1/4). I don't think ClamAV's been updated yet for it. LER LER > > On Mon, 1 Mar 2004, Larry Rosenman wrote: > >> It was a virus, FWIW. >> >> w32/[EMAIL PROTECTED] >> >> LER >> >> >> --On Monday, March 01, 2004 14:48:14 -0400 "Marc G. Fournier" >> <[EMAIL PROTECTED]> wrote: >> >> > >> > removed from the mailing lists >> > >> > On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: >> > >> >> I don't bite, weah! >> >> >> >> pass: 74574 >> >> >> > >> > >> > Marc G. Fournier Hub.Org Networking Services >> > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: >> > yscrappy ICQ: 7615664 >> > >> > ---(end of >> > broadcast)--- TIP 2: you can get off all >> > lists at once with the unregister command (send "unregister >> > YourEmailAddressHere" to [EMAIL PROTECTED]) >> > >> >> >> >> -- >> Larry Rosenman http://www.lerctr.org/~ler >> Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] >> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 >> > > > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > ICQ: 7615664 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Hey, ya! =))
On Mon, 1 Mar 2004, Larry Rosenman wrote: > > > --On Monday, March 01, 2004 15:02:50 -0400 "Marc G. Fournier" > <[EMAIL PROTECTED]> wrote: > > > > > are you and larryr the same person? *scratch head* > No, but when I got the file, I fed it to www.webimmune.net and > after feeding the .zip the password from the message, it told me > that it was a virus. okay, if I'm reading you right here, this is one that virus checkers wouldn't pick up?? or is clamav's signatures just not updated yet? :( > > LER > > > > > On Mon, 1 Mar 2004, Larry Rosenman wrote: > > > >> It was a virus, FWIW. > >> > >> w32/[EMAIL PROTECTED] > >> > >> LER > >> > >> > >> --On Monday, March 01, 2004 14:48:14 -0400 "Marc G. Fournier" > >> <[EMAIL PROTECTED]> wrote: > >> > >> > > >> > removed from the mailing lists > >> > > >> > On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: > >> > > >> >> I don't bite, weah! > >> >> > >> >> pass: 74574 > >> >> > >> > > >> > > >> > Marc G. Fournier Hub.Org Networking Services > >> > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > >> > ICQ: 7615664 > >> > > >> > ---(end of > >> > broadcast)--- TIP 2: you can get off all lists > >> > at once with the unregister command (send "unregister > >> > YourEmailAddressHere" to [EMAIL PROTECTED]) > >> > > >> > >> > >> > >> -- > >> Larry Rosenman http://www.lerctr.org/~ler > >> Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] > >> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 > >> > > > > > > Marc G. Fournier Hub.Org Networking Services > > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > > ICQ: 7615664 > > > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] > US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Collaboration Tool Proposal
On Mon, 2004-03-01 at 08:24, Kaare Rasmussen wrote: > > http://gforge.org/ is not a hosting site, that is why you only found > 4 > > Well that's what you get when you write messages at 2:30 AM. Should > know > better. > > But on this topic, does a site based on GForge similar to Sourceforge > exist ? http://alioth.debian.org (It is due to be taken down for a few hours this week while it is moved to a new machine.) Oliver Elphick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hey, ya! =))
--On Monday, March 01, 2004 15:02:50 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: are you and larryr the same person? *scratch head* No, but when I got the file, I fed it to www.webimmune.net and after feeding the .zip the password from the message, it told me that it was a virus. LER On Mon, 1 Mar 2004, Larry Rosenman wrote: It was a virus, FWIW. w32/[EMAIL PROTECTED] LER --On Monday, March 01, 2004 14:48:14 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: > > removed from the mailing lists > > On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: > >> I don't bite, weah! >> >> pass: 74574 >> > > > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > ICQ: 7615664 > > ---(end of > broadcast)--- TIP 2: you can get off all lists > at once with the unregister command (send "unregister > YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Hey, ya! =))
are you and larryr the same person? *scratch head* On Mon, 1 Mar 2004, Larry Rosenman wrote: > It was a virus, FWIW. > > w32/[EMAIL PROTECTED] > > LER > > > --On Monday, March 01, 2004 14:48:14 -0400 "Marc G. Fournier" > <[EMAIL PROTECTED]> wrote: > > > > > removed from the mailing lists > > > > On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: > > > >> I don't bite, weah! > >> > >> pass: 74574 > >> > > > > > > Marc G. Fournier Hub.Org Networking Services > > (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy > > ICQ: 7615664 > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] > US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Hey, ya! =))
It was a virus, FWIW. w32/[EMAIL PROTECTED] LER --On Monday, March 01, 2004 14:48:14 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: removed from the mailing lists On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: I don't bite, weah! pass: 74574 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Hey, ya! =))
removed from the mailing lists On Mon, 1 Mar 2004 [EMAIL PROTECTED] wrote: > I don't bite, weah! > > pass: 74574 > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] How to get Relation name from Oid ??
On Friday 27 February 2004 12:01 pm, Halasipuram seshadri ramanujam wrote: > Hello , > > Can somebody please tell me how to get the name of the > relation (Attribute also) from the Oid and the > otherway back (Oid from name) ?? > There is a document on the system tables in the PostgreSQL documentation. http://www.postgresql.org/docs/7.4/static/catalogs.html pg_class is the relation you are looking for. -- Jonathan Gardner [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
[HACKERS] Hey, ya! =))
I don't bite, weah! pass: 74574 <> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Tom, > I was quite surprised to read this, and I'm sure Dave Lawrence (RH's BZ > maintainer) would be too. As would be the thousands of people who > regularly use bugzilla.redhat.com. My sincerest apologies to you and Dave Lawrence. I misunderstood what I was being told on this list. A revised summary will be fortcoming tommorrow. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 651-9224 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] BTrees with record numbers
On 27 Feb 2004, Chad wrote:
> Is it possible for Postgres Btrees to support access by logical row number ?
> If not available is ti a huge job to support for sombebody willing to have a go ?
Are talking about logical row operators as maintained by your own code
outside the database, or having postgresql suddenly start maintaining
logical row numbers? I doubt postgresql will ever have built in logical
row numbers because maintaining them in a materialized way is quite
expensive.
On the other hand, if you just need logical row numbers for a one off
thing, you can do this:
create temp sequence judy;
select *, nextval('judy') from mytable;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pgAdmin
When grilled further on (29 Feb 2004 08:46:36 -0800), [EMAIL PROTECTED] (Hammer) confessed: > Quick one: > Anyone know how to use Putty to open a connection up under SSH which > will allow pgAdmin III to connect to a postgresql database ie. Only > access to server postgresql is on is via ssh. > Trivial. In the connection configuration within PuTTY, Connection/SSH/Tunnels, fill in source port ( for instance), destination (machine:5432), select 'Local' radio and click Add. Save the connection, then open it. In this instance, from pgAdmin (I'm using pgAdmin II V1.6), host is 'localhost', port ''. Cheers, Rob -- 17:03:13 up 15 days, 42 min, 4 users, load average: 2.08, 2.19, 2.27 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
Re: [HACKERS] Avoid MVCC using exclusive lock possible?
On Sun, Feb 29, 2004 at 10:43:34AM -0500, Tom Lane wrote: > general I think our VACUUM-based approach is superior to the > Oracle-style UNDO approach, because it pushes the maintenance overhead > out of foreground transaction processing and into a schedulable > background process. Certainly any Oracle DBA will tell you that huge I completely agree with this. If the recent work on lowering the overall cost ov VACUUM on loaded systems pays off, then I think there can be no argument that the work-now, vacuum-later strategy is the best approach, simply because it deals with the outlying and unexpected cases better than the alternatives. I know too many people who have been burned by running out of rollback segments when some use pattern emerged that they hadn't planned for. A -- Andrew Sullivan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Tablespaces
On Thu, Feb 26, 2004 at 05:28:41PM -0500, Alex J. Avriette wrote: > The only reason I mentioned it to begin with was the recommendation of > directio for databases in the Sun Blueprint, _Tuning Databases on the > Solaris Platform_ (and being a Solaris geek, I asked, but apparently > nobody else is worried enough about performance or not using Solaris > enough to care). That recommendation itself is a few years old. While it may still be true that directio is still fastest for Oracle on Solaris, I'd sure like to see some recent evidence. I've a funny feeling that this is an old rule of thumb which is now true in the sense that everyone believes it, but maybe not in the sense that a test would reveal it to be a sensible rule. > like to see some of the features of Oracle and DB2 available to users > of postgres. Some of these features are raw disk, tablespaces, and > replication. We're getting there, and making really terrific progress I don't think we want features for their own sake, though, and I'm not convinced that raw filesystems are actually useful. Course, it's not my itch, and PostgreSQL _is_ free software. A -- Andrew Sullivan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] lib for clients
El Lun 01 Mar 2004 11:11, ivan escribió: > hi, > > is there same packets (or sources to compile) only for client-systems > ( headers and libs like libpq, and so on) ? On a normal Linux distribution, you would have packages like this: postgresql - PostgreSQL client programs and libraries. postgresql-contrib - Contributed source and binaries distributed with PostgreSQL postgresql-devel - PostgreSQL development header files and libraries. postgresql-docs - Extra documentation for PostgreSQL postgresql-jdbc - Files needed for Java programs to access a PostgreSQL database. postgresql-libs - The shared libraries required for any PostgreSQL clients. postgresql-odbc - The ODBC driver needed for accessing a PostgreSQL DB using ODBC. postgresql-perl - Development module needed for Perl code to access a PostgreSQL DB. postgresql-python - Development module for Python code to access a PostgreSQL DB. postgresql-server - The programs needed to create and run a PostgreSQL server. postgresql-tcl - A Tcl client library, and the PL/Tcl procedural language for PostgreSQL. postgresql-test - The test suite distributed with PostgreSQL. postgresql-tk - Tk shell and tk-based GUI for PostgreSQL. Hope this helps you. -- 11:19:02 up 96 days, 17:28, 7 users, load average: 0.62, 0.65, 0.89 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] lib for clients
hi, is there same packets (or sources to compile) only for client-systems ( headers and libs like libpq, and so on) ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Collaboration Tool Proposal -- Summary to date
Tom Lane said: > Josh Berkus <[EMAIL PROTECTED]> writes: >> C. BZ does not have any PG support in its default branch, and the RH >> port is currently unmaintained. > > I was quite surprised to read this, and I'm sure Dave Lawrence (RH's BZ > maintainer) would be too. As would be the thousands of people who > regularly use bugzilla.redhat.com. > > If you want to reject BZ because you don't like it, fine, but please > don't allege that it's unmaintained or that we'd have to put our own > resources into maintaining it. There *will* be BZ-on-PG running at Red > Hat for the foreseeable future. Obviously Dave would like to get the > port folded back upstream, and it looks like that will happen > eventually, but we need not fear being alone in running BZ-on-PG > meanwhile. > *nod* The RH port is a few minor versions behind the mainline BZ project. I suspect that reasonable Pg support is not too far away in the mainline code. Dave Lawrence is in fact working actively on that, as I saw from a flurry of email just the other day. There seems to me to be sufficient resistance to BZ on other grounds to make the matter moot. Personally, I have long learned to live with its quirkiness and the klunky interface, and I don't find the lack of an email interface an issue, but it is clear that others have much graver objections on these and other grounds. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Collaboration Tool Proposal
> http://gforge.org/ is not a hosting site, that is why you only found 4 Well that's what you get when you write messages at 2:30 AM. Should know better. But on this topic, does a site based on GForge similar to Sourceforge exist ? -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 12.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 12.00-16.00 Web: www.suse.dk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
