[HACKERS] Current CVS compile problem
Hi! xor% uname -a FreeBSD xor 5.0-RELEASE FreeBSD 5.0-RELEASE #7: Thu Mar 6 21:53:07 MSK 2003 [EMAIL PROTECTED]:/usr/src/sys/i386/compile/XOR i386 xor% gcc -v Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.2.1 [FreeBSD] 20021119 (release) xor% ./configure --enable-cassert --enable-debug xor% gmake gmake[4]: *** No rule to make target `../lib/typename.o', needed by `ecpg'. Stop. gmake[4]: Leaving directory `/spool/home/teodor/pgsql/src/interfaces/ecpg/preproc' gmake[3]: *** [all] Error 2 gmake[3]: Leaving directory `/spool/home/teodor/pgsql/src/interfaces/ecpg' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/spool/home/teodor/pgsql/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/spool/home/teodor/pgsql/src' gmake: *** [all] Error 2 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] who can help me?
Hi, all hackers, I can startup database using command /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data. But when I debug it using gdb and set args -D /usr/local/pgsql/data, it can't startup database. it says; FATAL: Database postgres does not exist in the system catalog. Is there any difference between them? Thanks a lot. Jinqiang Han ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] transaction abort
Hi all. I have read some codes on transaction abort operation. When the transaction abort, it seem that all the tuples related in the transaction have not been deal with. it XMIN equals to the tuple create transaction ID. ItsXMAX equals null. Of cource ,It make some records on the pg_log. So I have one question, how the system know which tuple is valid? do it need the help of pg_log? That is ,we check for a tuple with XMIN is valid and the sign in pg_log means COMMIT? Thank for any comments Josh
Re: [HACKERS] who can help me?
gdb doesn't like dealing with arguments (for me anyway). Try setting the data directory via the PGDATA environment variable. On Mon, 2003-03-17 at 04:35, Jinqiang Han wrote: Hi, all hackers, I can startup database using command /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data. But when I debug it using gdb and set args -D /usr/local/pgsql/data, it can't startup database. it says; FATAL: Database postgres does not exist in the system catalog. Is there any difference between them? Thanks a lot. Jinqiang Han ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] ALTER USER
Is it just the password that expires or the account? The comment for valid until says the password is valid until that time. However, one of the examples says the account is valid until that time. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] who can help me?
On Mon, Mar 17, 2003 at 17:35:10 +0800, Jinqiang Han [EMAIL PROTECTED] wrote: Hi, all hackers, I can startup database using command /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data. But when I debug it using gdb and set args -D /usr/local/pgsql/data, it can't startup database. it says; FATAL: Database postgres does not exist in the system catalog. Is there any difference between them? I think postmaster checks what its name is (from argv[0]) and runs differently depending on what it is. If there is an option to gdb to set arg 0, try using it to set it to postmaster. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Current CVS compile problem
Teodor Sigaev [EMAIL PROTECTED] writes: gmake[4]: *** No rule to make target `../lib/typename.o', needed by `ecpg'. Stop. Yeah, me too. I think the correct fix is '../lib' should become '../ecpglib' in ecpg/preproc/Makefile, but am waiting on Michael to confirm. 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] transaction abort
postgresql [EMAIL PROTECTED] writes: SGkgYWxsLg0KICAgIEkgaGF2ZSByZWFkIHNvbWUgY29kZXMgb24gdHJhbnNh Y3Rpb24gYWJvcnQgb3BlcmF0aW9uLiAgV2hlbiB0aGUgdHJhbnNhY3Rpb24g YWJvcnQsIGl0IHNlZW0gdGhhdCANCmFsbCB0aGUgdHVwbGVzIHJlbGF0ZWQg aW4gdGhlIHRyYW5zYWN0aW9uIGhhdmUgbm90IGJlZW4gZGVhbCB3aXRoLiBp dCBYTUlOIGVxdWFscyB0byB0aGUgdHVwbGUgY3JlYXRlIHRyYW5zYWN0aW9u [snip] Please don't MIME-encode your mail unnecessarily. It makes it difficult to quote it :-( Anyway, to answer your question: tuples inserted by a failed transaction need not be deleted right away. No other transaction will consider them good, since their XMIN isn't committed (and yes, we need pg_log for that). The next VACUUM will get rid of them. 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] ALTER USER
Bruno Wolff III [EMAIL PROTECTED] writes: Is it just the password that expires or the account? The comment for valid until says the password is valid until that time. However, one of the examples says the account is valid until that time. Given the current implementation, I think it's correct to say that the password expires not the account: 1. the userid isn't deleted or anything like that. 2. validuntil is only checked in password authentication methods; if you are able to connect via a non-password auth method (eg IDENT) then it's not checked. I've never been quite sure whether #2 is a bug or a feature, though. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error message style guide
On Fri, 14 Mar 2003, Steve Crawford wrote:
One thing that would be great from a user's perspective (and which might
reduce the volume of support questions as well) is to uniquely number all
errors as in:
Error 1036: the foo could not faz the fleep
I agree with the unique codes.
It does make googling for help easier.
This is how informix does it - you get a sqlstate and what they call a
'native error'. Using SQLError (odbc) you can get a one liner about it,
but the real meat comes from either the documentation or from the command
line program finderr. You give it the native error and it gives you a
paragraph of information about the problem and what options you have.
Plus, if you have a numeric code sent back you can have an error handler
that looks quite a bit nicer -
switch(pgErrorCode)
{
case PG_HDD_ON_FIRE:
die_horrifically();
break;
case PG_UNKNOWN_USER:
tell_user_he_is_dumb();
break;
}
instead of a big pile of strcmp's.
From an efficiency standpoint, I don't know if it would have any benefit
of sending back a native code and have the client ask for the details if
it wants it.
--
Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/
Ronald McDonald, with the help of cheese soup,
controls America from a secret volkswagon hidden in the past
---
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Error-message infrastructure: what about location in PL functions?
I thought of something I'd overlooked in my original proposal for error-
handling upgrades: what about reporting where an error occurs in a PL
function?
Currently, plpgsql has a hack that prints a separate WARNING giving
the error location, but this is pretty darn ugly. It should be part of
the error report message to begin with.
I can see how to create a mechanism that gathers the data (details below
if you care), but I'm not too sure how it should be reported to the
client --- ie, what's the protocol message?
This seems nonobvious because (a) the appropriate info might vary
depending on the PL language involved, and (b) when there are several
nested PL functions, we probably want to include the whole stack trace.
Here is an example of what you get right now:
regression=# create function foo (int, int) returns int as '
regression'# begin
regression'# return $1 / $2;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo(10,0);
WARNING: Error occurred while executing PL/pgSQL function foo
WARNING: line 2 at return
ERROR: division by zero
regression=# create function bar (int, int) returns int as '
regression'# begin
regression'# return foo($1,$2);
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select bar(10,0);
WARNING: Error occurred while executing PL/pgSQL function foo
WARNING: line 2 at return
ERROR: division by zero
regression=#
Note the lack of any info about bar() in the second case --- this seems
like a bad omission.
I am visualizing some display on the order of
ERROR: division by zero
WHERE: PL/pgSQL function foo, line 2 at return
WHERE: PL/pgSQL function bar, line 2 at return
but I'm not sure exactly how it should look, nor about the
protocol-message representation. Should the 'where' entries be sent as
repeated instances of a single field type? (This seems unpleasant; I'd
prefer not to place any semantic significance on the ordering of fields
within an error message, yet we'd certainly have to do so if we repeat
a field to represent a call stack.) The alternative is to put all the
call stack levels into a single field, which seems to mean multiple
lines in that field, which goes against the notion that we should avoid
attaching any semantic meaning to the formatting of the contents of a
text field. In any case, we probably can't use any representation more
specific than a text string for each entry in the call stack, because
of the likelihood that the contents will vary across PLs.
Any thoughts about how to handle all this? And what should we call this
field, anyway?
As for actually collecting the info, I'm thinking of a stack of error
context records, say
typedef struct ErrorContext {
struct ErrorContext *previous;
void (*callback) (void *arg);
void *arg;
} ErrorContext;
extern ErrorContext *error_context_stack;
Any given PL would push an item onto this stack at function entry, and
pop it off again at function return. (No palloc is needed: the record
is just a local variable in the PL handler.) If an error needs to be
reported, after errstart() sets up the error parameter collection
structure it traverses the error_context_stack chain and calls each
callback with the specified arg (which the callback can use to access
the PL's info about the current function). The callback can then use
the usual optional-error-info-addition routines to add information to
the error parameters, something like
errfunclocation(PL/pgSQL function %s, line %d at %s,
funcstate-name, funcstate-lineno, ...);
We'd have to define errfunclocation to do the Right Thing when called
multiple times in a single error report, but that seems easy.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] fixups for 7.3 to contrib directories
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Gregory Stark wrote: Just some fixups to a couple contrib directories I was trying out. . replace CREATE OR REPLACE AGGREGATE with a separate DROP and CREATE . add DROP for all CREATE OPERATORs . use IMMUTABLE and STRICT instead of WITH (isStrict) . add IMMUTABLE and STRICT to int_array_aggregate's accumulator function [ Attachment, skipping... ] [ Attachment, skipping... ] -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Sat, Mar 15, 2003 at 09:23:28AM -0600, Bruno Wolff III wrote: On Fri, Mar 14, 2003 at 14:19:46 -0600, Taral [EMAIL PROTECTED] wrote: Same setup, different query: test= explain select max(time) from test where id = '1'; NOTICE: QUERY PLAN: Aggregate (cost=5084.67..5084.67 rows=1 width=0) - Index Scan using idx on test (cost=0.00..5081.33 rows=1333 width=0) Since the index is (id, time), why isn't the index being used to retrieve the maximum value? It looks like an index scan is being done. If the index was on (time, id) instead of (id, time), then you could get a further speed up by rewriting the query as: select time from test where id = '1' order by time desc limit 1; Yes, that's exactly it. It's an index _scan_. It should simply be able to read the maximum straight from the btree. -- Taral [EMAIL PROTECTED] This message is digitally signed. Please PGP encrypt mail to me. Most parents have better things to do with their time than take care of their children. -- Me pgp0.pgp Description: PGP signature
Re: No index maximum? (was Re: [HACKERS] No merge sort?)
On Mon, Mar 17, 2003 at 11:23:47 -0600, Taral [EMAIL PROTECTED] wrote: On Sat, Mar 15, 2003 at 09:23:28AM -0600, Bruno Wolff III wrote: On Fri, Mar 14, 2003 at 14:19:46 -0600, Taral [EMAIL PROTECTED] wrote: Same setup, different query: test= explain select max(time) from test where id = '1'; NOTICE: QUERY PLAN: Aggregate (cost=5084.67..5084.67 rows=1 width=0) - Index Scan using idx on test (cost=0.00..5081.33 rows=1333 width=0) Since the index is (id, time), why isn't the index being used to retrieve the maximum value? It looks like an index scan is being done. If the index was on (time, id) instead of (id, time), then you could get a further speed up by rewriting the query as: select time from test where id = '1' order by time desc limit 1; Yes, that's exactly it. It's an index _scan_. It should simply be able to read the maximum straight from the btree. max and min don't use indexes. They are generic aggregate functions and postgres doesn't have the special knowledge to know that for those aggregate functions and index can be used. You can get around this by rewriting the query as I previously indicated. For more details on why things are this way, search the archives. This topic comes up a lot. I was also mistaken about have to switch the index around for this case. It should work the way you have it (if you rewrite the query). ---(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] Formatting Intervals
On the SQL list earlier today, I posted the following, and Josh Berkus rightfully told me to post it here I was looking for the source for this a month or so back, and couldn't find it. I needed similar stuff. If someone could guide me, I **MIGHT** find the round tuit's for it for 7.4. this is in relation for to_char support for intervals. LER -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Tom Lane writes: * Backend's ReadyForQuery message (Z message) should carry an indication of current transaction status (idle/in transaction/in aborted transaction) so that frontend need not guess at state. Perhaps also indicate autocommit status. If we do this, could we get rid of the messy autocommit GUC option and handle autocommit in the client? Before sending a command, the client could check the transaction status and automatically choose to start a new transaction. That way we could get rid of the current mess that every client needs to send a SET autocommit command before it can safely do anything. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] who can help me?
On Tue, Mar 18, 2003 at 09:02:51 +0800, Jinqiang Han [EMAIL PROTECTED] wrote: Bruno Wolff III, Did you notice that postmaster is a link to postgres. So argv[0] is postgres not postmaster... I wonder if postmaster.c is still in use. It is really weird. argv[0] gets set from what you use for a command, not what the actual hard link name of the file is. ---(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] [INTERFACES] Roadmap for FE/BE protocol redesign
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: * Backend's ReadyForQuery message (Z message) should carry an indication of current transaction status (idle/in transaction/in aborted transaction) so that frontend need not guess at state. Perhaps also indicate autocommit status. If we do this, could we get rid of the messy autocommit GUC option and handle autocommit in the client? Hmm ... that's a thought ... not very backwards-compatible with 7.3, but I think I like it better than continuing on with the GUC option. As you say, that path is looking messier all the time. Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cursors outside transactions
Folks, I'm currently working on an implementation of cursors that can function outside the transaction that created them (the SQL spec calls them holdable cursors). I can see 2 main ways to implement this: (1) During the transaction that created the holdable cursor, don't do anything special. When that transaction ends (and we'd normally be cleaning up cursor resources), fetch all the rows from the cursor and store them in a Tuplestore. When subsequent FETCHs for the cursor are received, handle them by retrieving rows from the Tuplestore. Pros: - simple to implement - doesn't acquire locks (etc.) on any database objects queried by the cursor, so later database operations can continue in parallel with the retrieval of rows from the holdable cursor Cons: - doesn't allow for updates to the cursor - doesn't allow for sensitive/asensitive cursors (i.e. the cursor cannot witness changes made to its result set by other clients -- see 4.34 of SQL 2003) - inefficient if the result set the cursor is fetching is enormous, as it must be stored on disk prior to committing the transaction (2) Use MVCC to ensure that the snapshot of the database that the transaction had is still valid, even after the transaction itself has committed. This would require: (a) changing VACUUM so that it's aware the tuples visible to the cursor can't be removed yet (b) holding locks on database objects, so that future database operations don't cause problems for the cursor (e.g. you can't allow someone to drop a table still in use by a holdable cursor). Another example is the row-level locks used for updated tuples, if updatedable cursors are implemented -- they would be need to be held for much longer than normal. (c) probably more changes: the assumption that a transaction's resources can be cleaned up once it commits is a fairly fundamental one, so there are surely additional things that will need to be kept locked while the holdable cursor is still valid (likely, until the client connection is terminated). Pros: - efficient for large result sets (just like normal cursors) - updateable and sensitive cursors would be easier to implement Cons: - really complex, difficult to get right - would hurt concurrent performance, due to long-term locks I'm currently planning to implement (1), as it is sufficient for the immediate need that I'm facing. Any comments? Is there another way to implement this that I'm not seeing? In particular, I'd like to know if the list would object to integrating (1) into the mainline sources (perhaps until someone gets around to doing something similar to (2), which may be never). Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cursors outside transactions
On Mon, 2003-03-17 at 22:01, Alvaro Herrera wrote: What about opening a pseudo-transaction that exists only to serve the cursor? What exactly do you mean by a pseudo-transaction? Keep in mind we don't have nested transactions (yet?), and that the holdable cursor needs to be accessible both inside and outside its creating transaction. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Little problem with tsearch
Having a little problem with 7.3's tsearch: usa=# select brand,description, ftiidx from food_foods where description ilike '%frapp%'; brand | description | ftiidx ---+ ---+ - Starbucks | Ice Blended Drinks: Creme Frappuccino® Coconut | 'ice' 'blend' 'creme' 'drink' 'coconut' 'starbuck' 'frappuccino®' Starbucks | Ice Blended Beverages: Frappuccino® Choc. Brownie | 'ice' 'choc' 'blend' 'browni' 'beverag' 'starbuck' 'frappuccino®' Starbucks | Ice Blended Beverages: Frappuccino® Coffee | 'ice' 'blend' 'coffe' 'beverag' 'starbuck' 'frappuccino®' Starbucks | Ice Blended Beverages: Frappuccino® Espresso | 'ice' 'blend' 'beverag' 'espresso' 'starbuck' 'frappuccino®' See how the (r) symbol is not being stemmed? That means that it's not possible for anyone to search for the word 'frappacino'. Is there any fix for this? A similar thing happens with the tradmark symbol. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] anyone? CREATELANG in pgsql 7.3.2 failing
hi, i've successfully installed Postgresql 7.3.2 on Mac OSX 10.2.4, and can access/administer as necessary except, i can't seem to CREATELANG, specifically plpgsql . the command: createlang --pglib=/usr/local/pgsql/lib --dbname=apache_auth plpgsql simply returns: ERROR: Load of file /usr/local/pgsql/lib/plpgsql.dylib failed: no error message available createlang: language installation failed my LOG file has nothing further: ... LOG: database system is ready ERROR: Load of file /usr/local/pgsql/lib/plpgsql.dylib failed: no error message available no error message is not very helpful :-( i tried a clean install -- all went without a hitch . anyone have any suggestions here? thanks! richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Nested transactions
Hackers,
I've been looking at what is involved into making nested transactions
possible. So far I've identified the following things. Please comment.
Additions are welcome.
Resource Management
---
We will create and select a new memory context for each subtransaction.
This context will be deleted on transaction abort/commit.
Some At{Abort,Commit,EOX}act_*() actions will be executed at the end of the
subtransaction (indexes, GUC, Locks. Memory), while others will be delayed
(on_commit_actions, smgrDoPendingDeletes) till toplevel transaction commit.
Most of there routines will have to be revised so they do The Right Thing.
Locking
---
The ProcReleaseLocks()/LockReleaseAll() interaction will have to be
modified to not just release all locks on abort. It currently does
the right thing on commit, but the check for abort will have to be more
fine-grained.
Transaction State
-
We will add a field to TransactionStateData with the xid of the parent
transaction. If it's set to InvalidTransactionId, then the transaction is a
parent transaction [maybe we only need a boolean, since we can get the
parent transaction from the subtransaction tree anyway -- another idea
would be using a integer to denote nesting level, but how is that
useful?]
Whenever a transaction aborts, the backend is put into TRANS_ABORT state
only if it is a toplevel transaction. If it is not, the backend is returned
to TRANS_INPROGRESS, the TransactionId goes back to the parent
(sub)transaction Id, and the pg_clog records the transaction as aborted.
The subtransaction tree may delete the corresponding branch.
Commit/abort protocol in pg_clog
For a toplevel transaction, commit is:
- write 01 as state of xid
- write 01 as state of each non-aborted subtransaction
For a toplevel transaction, abort is:
- write 10 as state of xid
- write 10 as state of each non-aborted subtransaction
For a non-toplevel transaction, commit does nothing.
For a non-toplevel transaction, abort is:
- write 10 as state of xid
- write 10 as state of each non-aborted subtransaction.
Tuple Visibility
We keep the xmix/xmax protocol for tuple visibility. To determine the
state of a transaction, we determine if it's a toplevel transaction. If it
is, report the pg_clog value directly.
For a non-toplevel transaction, the protocol is as follows:
- if the state is 01, it is committed
- if the state is 10, it is aborted
- if the state is 00:
- if it's toplevel, it's in progress
- if parent is 01, start again
- if parent is 10, it is aborted
- if parent is 00, check parent
Subtransaction tree
---
I don't know how this will be, but the subsystem will have to answer the
following questions:
- Xid of my parent transaction
- List of Xids of non-aborted child transactions
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentre de él no son, por desgracia,
nada idílicas (Ijon Tichy)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] anyone? CREATELANG in pgsql 7.3.2 failing
the command: createlang --pglib=/usr/local/pgsql/lib --dbname=apache_auth plpgsql Try just this: createlang plpgsql apache_auth Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PQescapeBytea on Win32
Hi - there is a problem with PQescapeBytea for Win32. Since libpq is a DLL, all memory allocated from within the DLL needs to be freed from within the dll. PQescapeBytea allocates memory, but there is no function call back into the DLL to free this memory. This causes heap corruption when the main program tries to free the memory itself. The alternative is to just leak the memory and never free it. Also bad -Dave Brown [EMAIL PROTECTED] _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(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] cursors outside transactions
Bruce Momjian [EMAIL PROTECTED] writes: Why don't you like (1)? It seems fine to me, and I don't see how we are magically going to do any better in the future. The restrictions of (1) seem pretty obvious to me ... but I don't see any prospect of doing better in the near future, either. Cross-transaction cursors are a *hard* problem for us. The question here is do we want to offer a half-baked solution, recognizing that it's some improvement over no solution at all? Or do we feel it doesn't meet our standards? I could be talked into seeing it either way ... 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] anyone? CREATELANG in pgsql 7.3.2 failing
--On Tuesday, March 18, 2003 12:22 PM +0800 Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Try just this: createlang plpgsql apache_auth Chris hi! ok, but i wouldn't expect the result to be any different here we go . [EMAIL PROTECTED]/usr/local/pgsql/lib createlang plpgsql apache_auth ERROR: Load of file /usr/local/pgsql/lib/plpgsql.dylib failed: no error message available createlang: language installation failed [EMAIL PROTECTED]/usr/local/pgsql/lib same problem, same error . :-( ---(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] regression failure - horology
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Jeroen T. Vermeulen wrote: On Sat, Feb 22, 2003 at 03:09:13AM -0500, Tom Lane wrote: Mph. It fails for me too when I use --enable-integer-datetimes. Looks like that patch still needs some work... Yeah. I'm really, really, *really* sorry for submitting it in the state it was in. I shouldn't have done that just before moving to another country. I found the problem last night, but couldn't get to a Net connection until now. The problem is in src/bin/psql/common.c, around line 250-335 somewhere depending on the version. The 2nd and 3rd clauses of the while loop condition: (rstatus == PGRES_COPY_IN) (rstatus == PGRES_COPY_OUT)) should of course be: (rstatus != PGRES_COPY_IN) (rstatus != PGRES_COPY_OUT)) Jeroen ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Peter Eisentraut [EMAIL PROTECTED] writes: ... So the application already knows that foo is the table and a is the column. So if the application wants to know about details on the column a, it can execute SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a'; With this proposed change, it can replace that with SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y; Dave will correct me if I'm wrong --- but I think the issue here is that the client-side library (think ODBC or JDBC) needs to gain this level of understanding of a query that is presented to it as an SQL-source string. So no, it doesn't already know that foo is the table and a is the column. To find that out, it has to duplicate a lot of backend code. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] analyze after a database restore?
Attached is a committed patch to add a recommendation for ANALYZE after restore. It is a shame we only have vacuumdb -a to do analyze _and_ vacuum, and no analyze-only option. --- Tom Lane wrote: mlw [EMAIL PROTECTED] writes: From an ease of use perspective, it would be one less step. There is something to be said for that. As Rod notes, this has been considered and rejected before --- but I think that was back when ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on scanning the whole table. The current implementation is vastly lighter-weight than what we were looking at back then. Perhaps it's time to reconsider. Although I suggested doing a single unconditional ANALYZE at the end of the script, second thought leads me to think the per-table ANALYZE (probably issued right after the table's data-load step) might be better. That way you'd not have any side-effects on already-existing tables in the database you are loading to. OTOH, that way would leave the system catalogs un-analyzed, which might be bad. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v retrieving revision 2.24 diff -c -c -r2.24 backup.sgml *** doc/src/sgml/backup.sgml11 Nov 2002 20:14:02 - 2.24 --- doc/src/sgml/backup.sgml17 Mar 2003 23:58:37 - *** *** 126,131 --- 126,138 /para para + Once restored, it is wise to run commandANALYZE/ on each + database so the optimizer has useful statistics. You + can also run commandvacuumdb -a -z/ to commandANALYZE/ all + databases. +/para + +para The ability of applicationpg_dump/ and applicationpsql/ to write to or read from pipes makes it possible to dump a database directly from one server to another, for example Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.56 diff -c -c -r1.56 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 13 Feb 2003 04:54:15 - 1.56 --- doc/src/sgml/ref/pg_dump.sgml 17 Mar 2003 23:58:38 - *** *** 650,655 --- 650,660 /programlisting /para +para + Once restored, it is wise to run commandANALYZE/ on each + restored object so the optimizer has useful statistics. +/para + para applicationpg_dump/application has a few limitations: *** *** 682,687 --- 687,698 other output formats is not limited, except possibly by the operating system. /para + + para +Once restored, it is wise to run commandANALYZE/ on each +restored object so the optimizer has useful statistics. + /para + /refsect1 refsect1 id=pg-dump-examples Index: doc/src/sgml/ref/pg_dumpall.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dumpall.sgml,v retrieving revision 1.36 diff -c -c -r1.36 pg_dumpall.sgml *** doc/src/sgml/ref/pg_dumpall.sgml6 Jan 2003 18:53:24 - 1.36 --- doc/src/sgml/ref/pg_dumpall.sgml17 Mar 2003 23:58:38 - *** *** 258,267 applicationpg_dumpall/application will need to connect several times to the productnamePostgreSQL/productname server. If password authentication is configured, it will ask for a password each time. In !that case it would be convenient to set up a password file. /para - commentBut where is that password file documented?/comment /refsect1 --- 258,274 applicationpg_dumpall/application will need to connect several times to the productnamePostgreSQL/productname server. If password authentication is configured, it will ask for a password each time. In !that case it would be convenient to set up a filename.pgpass/ !password file. ! /para ! ! para !Once restored, it is wise to run commandANALYZE/ on each !database so the optimizer has useful statistics. You !can also run commandvacuumdb -a -z/ to commandANALYZE/ all !databases. /para /refsect1 Index: doc/src/sgml/ref/pg_restore.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.35
Re: [HACKERS] Roadmap for FE/BE protocol redesign
Tom Lane writes: Yes, Dave did answer --- basically, he's happy with not providing any column identity data in the cases where it's not obvious what the answer should be. And in particular he doesn't want the mechanism to drill down into view definitions (which is less than obviously right to me, but if that's what he wants it sure eliminates a lot of definitional issues). I don't get it. Say I execute SELECT a, b, c FROM foo;. In order to update that query, the application needs to create some update statement, say UPDATE foo SET a = entered_value;. So the application already knows that foo is the table and a is the column. So if the application wants to know about details on the column a, it can execute SELECT whatever FROM pg_attribute, pg_class WHERE relname = 'foo' AND attname = 'a'; With this proposed change, it can replace that with SELECT whatever FROM pg_attribute, pg_class WHERE oid = X AND attnum = Y; With the difference that the first version always works and the second version sometimes works, and when that sometimes is is determined by the rule that it should be obvious. That doesn't seem right to me. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Peter Eisentraut wrote: Tom Lane writes: * Backend's ReadyForQuery message (Z message) should carry an indication of current transaction status (idle/in transaction/in aborted transaction) so that frontend need not guess at state. Perhaps also indicate autocommit status. If we do this, could we get rid of the messy autocommit GUC option and handle autocommit in the client? Before sending a command, the client could check the transaction status and automatically choose to start a new transaction. That way we could get rid of the current mess that every client needs to send a SET autocommit command before it can safely do anything. What if folks want all their connections autocommit off. Seems it is best in the server. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cursors outside transactions
I think (1) is fine. When I used Informix, we did lots of huge cursors that we pulled from for reports, and they consumed huge amounts of RAM before we could do a fetch --- and we expected that. It doesn't seem worth adding complexity to avoid that, especially since even if (2) was done, there would be downsides to it. One question is how sensitive these cursors should be. Actually, looking at the DECLARE manual page, I see: varlistentry termINSENSITIVE/term listitem para acronymSQL92/acronym keyword indicating that data retrieved from the cursor should be unaffected by updates from other processes or cursors. Since cursor operations occur within transactions in productnamePostgreSQL/productname this is always the case. This keyword has no effect. /para /listitem /varlistentry which seems inaccurate. Surely we see commits of other transactions during our multi-statement transaction in the default READ COMMITTED isolation level, so why do the docs say insensitive is meaningless for us? Does sensitivity only apply outside the transaction somehow? So, my question is how do cursors behave now? Do they see commits by other transactions while in a multi-statement transaction? (1) is predictable in terms of sensitivity, or at least frozen at commit. --- Neil Conway wrote: Folks, I'm currently working on an implementation of cursors that can function outside the transaction that created them (the SQL spec calls them holdable cursors). I can see 2 main ways to implement this: (1) During the transaction that created the holdable cursor, don't do anything special. When that transaction ends (and we'd normally be cleaning up cursor resources), fetch all the rows from the cursor and store them in a Tuplestore. When subsequent FETCHs for the cursor are received, handle them by retrieving rows from the Tuplestore. Pros: - simple to implement - doesn't acquire locks (etc.) on any database objects queried by the cursor, so later database operations can continue in parallel with the retrieval of rows from the holdable cursor Cons: - doesn't allow for updates to the cursor - doesn't allow for sensitive/asensitive cursors (i.e. the cursor cannot witness changes made to its result set by other clients -- see 4.34 of SQL 2003) - inefficient if the result set the cursor is fetching is enormous, as it must be stored on disk prior to committing the transaction (2) Use MVCC to ensure that the snapshot of the database that the transaction had is still valid, even after the transaction itself has committed. This would require: (a) changing VACUUM so that it's aware the tuples visible to the cursor can't be removed yet (b) holding locks on database objects, so that future database operations don't cause problems for the cursor (e.g. you can't allow someone to drop a table still in use by a holdable cursor). Another example is the row-level locks used for updated tuples, if updatedable cursors are implemented -- they would be need to be held for much longer than normal. (c) probably more changes: the assumption that a transaction's resources can be cleaned up once it commits is a fairly fundamental one, so there are surely additional things that will need to be kept locked while the holdable cursor is still valid (likely, until the client connection is terminated). Pros: - efficient for large result sets (just like normal cursors) - updateable and sensitive cursors would be easier to implement Cons: - really complex, difficult to get right - would hurt concurrent performance, due to long-term locks I'm currently planning to implement (1), as it is sufficient for the immediate need that I'm facing. Any comments? Is there another way to implement this that I'm not seeing? In particular, I'd like to know if the list would object to integrating (1) into the mainline sources (perhaps until someone gets around to doing something similar to (2), which may be never). Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PQescapeBytea on Win32
Yes, I am aware of that limitation. If you link libpq as a Multithreaded DLL, it will not link libc into each DLL, but have only one libc that can free from anywhere. Is that acceptable or do we need a Win32 specific memory free function? --- Key88 SF wrote: Hi - there is a problem with PQescapeBytea for Win32. Since libpq is a DLL, all memory allocated from within the DLL needs to be freed from within the dll. PQescapeBytea allocates memory, but there is no function call back into the DLL to free this memory. This causes heap corruption when the main program tries to free the memory itself. The alternative is to just leak the memory and never free it. Also bad -Dave Brown [EMAIL PROTECTED] _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
Can someone comment on this bug report?
---
[EMAIL PROTECTED] wrote:
Jiri Langr ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Deallocating of prepared statement in ECPG at COMMIT
Long Description
When I prepare a statement in ECPG it lives only to first explicit transaction
block. At the COMMIT it seems to be deallocated.
It is not good behavior because the main advantage of prepared statements is once
prepare and many times execute!
Sample Code
ESQL/C code
#include stdio.h
#include stdlib.h
#include string.h
#define DBNAME langr
/* Testing of any SQL error, printing error message and exit */
void test(int l_exit) {
if(sqlca.sqlcode 0) {
printf(Error: %s\n, sqlca.sqlerrm.sqlerrmc);
if(l_exit) {
EXEC SQL DISCONNECT;
exit(-1);
}
}
}
/* Demonstration of error concerning the DEALLOCATEing of PREPARED statement
at the COMMIT in PostgreSQL ESQL/C */
int main() {
EXEC SQL BEGIN DECLARE SECTION;
char dbname[64] = DBNAME;
char sql_string[1000];
int l_id;
char l_name[10 + 1];
EXEC SQL END DECLARE SECTION;
ECPGdebug(1, stderr);
/* Creating DB and connecting to them */
/* strcpy(dbname, DBNAME); */
EXEC SQL CONNECT TO :dbname;
test(1);
EXEC SQL SET AUTOCOMMIT TO ON;
test(1);
/* Creating table */
EXEC SQL DROP TABLE test;
test(0); /* no exiting because it has not to exist yet */
EXEC SQL CREATE TABLE test (
id INTEGER NOT NULL,
nameCHAR(10)
);
test(1);
/* Preparing INSERT statement */
strcpy(sql_string, INSERT INTO test VALUES(?, ?));
EXEC SQL PREPARE prep_ins FROM :sql_string;
test(1);
/* Inserting several rows in one transaction */
EXEC SQL BEGIN;
test(1);
for(l_id = 0; l_id 3; l_id++) {
switch(l_id) {
case 0: strcpy(l_name, First); break;
case 1: strcpy(l_name, Second); break;
case 2: strcpy(l_name, Third); break;
}
EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
test(1);
}
EXEC SQL COMMIT;
test(1);
/* It did work well, but the statement was DEALLOCATED automatically - WHY?? */
/* Inserting next line in separate transaction */
l_id = 3;
strcpy(l_name, Fourth);
EXEC SQL BEGIN;
test(1);
EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
EXEC SQL COMMIT;
test(1);
EXEC SQL BEGIN;
test(1);
EXEC SQL DECLARE cur_test CURSOR FOR SELECT * FROM test;
test(1);
EXEC SQL OPEN cur_test;
test(1);
while(1) {
EXEC SQL FETCH cur_test INTO :l_id, :l_name;
test(1);
if(sqlca.sqlcode == ECPG_NOT_FOUND) break;
printf(%d: %s\n, l_id, l_name);
}
EXEC SQL CLOSE cur_test;
test(1);
EXEC SQL COMMIT;
test(1);
EXEC SQL FREE prep_ins;
test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
EXEC SQL DROP TABLE test;
test(1);
EXEC SQL DISCONNECT;
test(1);
return(0);
}
SQL code doing the same funcionality and work well!!
*
/* Demonstration of the same functionality in SQL, where it DOES work well */
/* Creating table */
CREATE TABLE test (
id INTEGER NOT NULL,
nameCHAR(10)
);
/* Preparing INSERT statement */
PREPARE prep_ins(INTEGER, CHAR) AS INSERT INTO TEST VALUES($1, $2);
/* Inserting several rows in one transaction */
BEGIN;
EXECUTE prep_ins (0, 'First');
EXECUTE prep_ins (1, 'Second');
EXECUTE prep_ins (2, 'Third');
COMMIT;
/* In SQL in the difference of ESQL/C the DEALLOCATION was not don, it is well *
/
/* Inserting next line in separate transaction */
BEGIN;
EXECUTE prep_ins (3, 'Fourth');
COMMIT;
/* Reading data from the table */
SELECT * FROM test;
/* Deallocating of the statement */
DEALLOCATE prep_ins;
DROP TABLE test;
No file was uploaded with this report
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
