[HACKERS] How to deal with order by, group by, distinct for user-defined types
Hi, I implemented a user-defined type, say X, and when I want to do the following queries, Postgresql will complain CREATE TABLE Table1 ( id integer, object X ); SELECT X, count(*) from Table1 GROUP BY X; ERROR: could not identify an ordering operator for type X HINT: Use an explicit ordering operator or modify the query. My question is: how do I find or define ordering operators for my user-defined type? Thanks. Ruey-Lung Hsiao ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Usenet Discussion Proposal
This was posted in news.groups, but it was not posted to the list. REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.postgresql.admin unmoderated group comp.databases.postgresql.general unmoderated group comp.databases.postgresql.hackers unmoderated group comp.databases.postgresql.novice unmoderated group comp.databases.postgresql.sql This is a formal Request For Discussion (RFD) for the creation of the following worldwide unmoderated Usenet newsgroups: comp.databases.postgresql.admin, comp.databases.postgresql.general, comp.databases.postgresql.hackers, comp.databases.postgresql.novice, comp.databases.postgresql.sql. This is not a Call for Votes (CFV); you cannot vote at this time. Procedural details are below. CHANGES: The changes from the previous RFD (for comp.databases.postgresql.general) are the inclusion of the following groups to the RFD: unmoderated group comp.databases.postgresql.admin unmoderated group comp.databases.postgresql.hackers unmoderated group comp.databases.postgresql.novice unmoderated group comp.databases.postgresql.sql The charter has been changed for the comp.databases.postgresql.general group to the "official" one located at www.postgresql.org. Charters are added for the admin, hackers, novice and sql groups. The charters are the official ones located at www.postgresql.org RATIONALE: all groups To make the listed postgresql groups proper members of the big eight hierarchy. Currently, they are considered "bogus". A successful vote for this will result in the named PostgreSQL groups being available on most well-managed usenet servers. This will also end potential confusion to new users as to why their well managed news provider does not carry the groups. This will also raise the profile of PostgreSQL by having it listed among the other Relational Database Management Systems in the offical big eight comp.databases.* hierarchy. It will also provide compliance with the usenet rule that those under the big eight hierarchy have gone through RFD and CFV. CHARTER: comp.databases.postgresql.admin This is a general discussion area for users pertaining to the administration, compilation and installation of PostgreSQL. END CHARTER. CHARTER: comp.databases.postgresql.general This is a general discussion area for users. Do not include any SQL related, administration, installation, or compilation questions in this group. There are other groups that can accommodate those needs. END CHARTER. CHARTER: comp.databases.postgresql.hackers The PostgreSQL developer's team lives here. This is for the discussion of current development issues, problems and bugs and the discussion of proposed new features. If people in the other groups don't know the answer to a question and it is likely that only a developer will know the answer, you may re-post that questionhere. You must try elsewhere first! END CHARTER. CHARTER: comp.databases.postgresql.novice This group is for beginners in using the PostgreSQL database system. It serves to supplement the other groups, and help answer basic questions. END CHARTER. CHARTER: comp.databases.postgresql.sql This is a discussion area for users on SQL related matters. END CHARTER. PROCEDURE: This is a request for discussion, not a call for votes. In this phase of the process, any potential problems with the proposed newsgroups should be raised and resolved. The discussion period will continue for a minimum of 21 days (starting from when the first RFD for this proposal is posted to news.announce.newgroups), after which a Call For Votes (CFV) may be posted by a neutral vote taker if the discussion warrants it. Please do not attempt to vote until this happens. All discussion of this proposal should be posted to news.groups. This RFD attempts to comply fully with the Usenet newsgroup creation guidelines outlined in "How to Create a New Usenet Newsgroup" and "How to Format and Submit a New Group Proposal." Please refer to these documents (available in news.announce.newgroups) if you have any questions about the process. DISTRIBUTION: This RFD has been posted to the following newsgroups: news.announce.newgroups, news.groups Additionally, pointers will be posted to the affected groups via another provider: comp.databases.postgresql.admin comp.databases.postgresql.general comp.databases.postgresql.hackers comp.databases.postgresql.novice comp.databases.postgresql.sql Proponent: Mike Cox <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] How to make lo_import and lo_export to use file on client local machine?
I try to use lo_import and lo_export for manage large object on PostgreSQL. I found that all files must be process on server with these commands. Could I make lo_import and lo_export to use file on client local machine? And How? Or Is it has other ways to manage large object that process is between client local machine and PostgreSQL database? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] How to check the postgresql version
Hi, Sorry if this problem seems stupid but I would appreciate it if someone helps. I've been implementing several user-defined types and their corresponding GiST index in postgresql 7.4.3. They worked well. However, I have noticed there are several changes in SPI functions and their parameters in 8.0beta. I want to be able to compile my codes in Postgresql 7.4.X and 8.0.X, so the only way to do that is to use the C preprocessor directives to check PG_VERSION (defined in pg_config.h). My problem is: I can't find a way to compare strings in C preprocessor directive since PG_VERSION is defined as something like "7.4.3" or "7.4.6". I ultimately want to do the following things: #if PG_VERSION starts with "8.X" ... call 8.0 compatible functions #else ... call 7.4.x compatible functions #endif Could anyone tell me how to do this kind of version check task? Thank you very much. Ruey-Lung Hsiao ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] "no snapshot has been set" error
Hi all, I encountered a problem when I'm implementing my user-defined type. My user-defined type is defined as CREATE TYPE X ( INTERNALLENGTH = 10 INPUT = X_in, OUTPUT = X_out ); In my X_in() function, I want to access other table to do some bookkeeping stuff. this table keeps track of the number of calls to X_in(). However, while SPI_connect() returns SPI_OK_CONNECT, whenever I execute SPI_exec( "SELECT count FROM XTABLE", 0), postgres stops executing and issues "Error: no snapshot has been set". Does anyone here knows what happens? Am I not allowed to use SPI functions in this situation? Thank you for your advice and suggestions in advance. Ruey-Lung Hsiao ---(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] bug/issue tracking system?
I was wondering if there's a bug/issue tracking system for pgsql? (e.g. something like http://www.bugzilla.org) Is the TODO list all that there is? Best Regards, -arman ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Minor problem with Makefile.shlib
Tom, Yes, -D I think. See the manual. (Note this is "sticky", so you probably want to do it in a scratch checkout rather than overwriting your regular tree.) Something lingered allright, but not in the PostgreSQL installation that I removed. Sigh... Anyway, the patch that was applied tonight removes the warnings which is fine, but the patch submitted by Fabien is more important since it actually enables me to build. I would be very greatful if that patch could make it before release of beta5. Thanks, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Minor problem with Makefile.shlib
Thomas Hallgren <[EMAIL PROTECTED]> writes: > Nope. I removed my previous installation. It's easy enough to check if > CVS have some way to check out a snapshot based on a timestamp. Does it? Yes, -D I think. See the manual. (Note this is "sticky", so you probably want to do it in a scratch checkout rather than overwriting your regular tree.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Minor problem with Makefile.shlib
Fabien COELHO wrote: Dear Thomas, Something that Fabien Coelho fixed recently broke tonight. I can no longer compile PL/Java on win32 using pgxs since the directory "pgxs/src/include/port/win32/*" is missing (again). AFAICS, the patch is in the queue waiting for a review. Well, the CVS HEAD did work as if it indeed had been applied until yesterday evening. I'm quite sure of that since I took a new snapshot yesterday and created a brand new installation. PL/Java compiled fine on win32 (aside from the warnings I mentioned at the beginning of this thread). Regards, Thomas Hallgren ---(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] Minor problem with Makefile.shlib
> >I guess the magic is explained because you installed *over* the previous > >test install you did to check my patch? So the header files were there > >from this previous install. > > Nope. I removed my previous installation. It's easy enough to check if > CVS have some way to check out a snapshot based on a timestamp. Does it? > If so, tell me and I'll try it. Do not bother. Let it be magic and wait for the patch to be applied;-) -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Minor problem with Makefile.shlib
Fabien COELHO wrote: I guess the magic is explained because you installed *over* the previous test install you did to check my patch? So the header files were there from this previous install. Nope. I removed my previous installation. It's easy enough to check if CVS have some way to check out a snapshot based on a timestamp. Does it? If so, tell me and I'll try it. - thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] New compile warnings
Andrew Dunstan wrote: This message can actually be fixed with the addition of the following 2 lines to SPI.xs: *BOOT*: *items* = 0; /* avoid '*unused **variable*' warning */ Argghh!! Mozilla mailer C&P strikes again. BOOT: items = 0; /* avoid 'unused variable' warning */ cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New compile warnings
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: I am seeing the following compile warnings in CVS. I am using for perl: Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: I believe these two: plperl.c:948: warning: `ret_hv' might be used uninitialized in this function plperl.c:949: warning: `ret_av' might be used uninitialized in this function indicate an actual bug --- at least, it's far from clear that the code can't try to use an uninitialized value. I trust that the authors of plperl will step up and fix it; I'm not sufficiently clear on what cases they are trying to support to want to touch it. I have just mentally walked through the code and I am fairly sure this is harmless, i.e. we should only refer to the one of these that has actually been set in any of the relevant code paths. I agree it is sloppy and will have discussions with one or two fellow perpetrators er I mean collaborators about cleaning it up. The others indicate sloppiness in the C code generated by perl's XS functionality. There's nothing we can do about them. FWIW, less obsolete versions of Perl generate fewer warnings --- the only one of these that I see on 5.8.0 and up is SPI.c:158: warning: unused variable `items' This message can actually be fixed with the addition of the following 2 lines to SPI.xs: *BOOT*: *items* = 0; /* avoid '*unused **variable*' warning */ These messages are confusing: /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used AFAICT sv_2pv_nolen should actually be used (by SPI.c) on Bruce's machine, so it's a bit strange. cheers andrew ---(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] cvs web errors
Tom Lane wrote: Did you repoint your bookmark to http://developer.postgresql.org/cvsweb.cgi/pgsql/ instead of pgsql-server? Doooh! That was it -- thanks! Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error handling in plperl and pltcl
James William Pye <[EMAIL PROTECTED]> writes: > I have been playing with RollbackToSavepoint and ReleaseSavepoint, but > per Neil's comments on IRC and the fact that I have to annoyingly > construct a List containing the savepoint name. I get the feeling that I > am not meant to use them. You're right. You can *not* expose those as user-callable operations in a PL language. Consider for example what will happen if the user tries to roll back to a savepoint that was established outside your function call, or tries to exit the function while still inside a local savepoint. You have to enforce strict nesting of functions and subtransactions; therefore it's a lot easier to present an API that looks like an exception-block construct (per plpgsql), or that just hides the whole deal in the SPI calling interface (as I'm proposing for plperl/pltcl). There's been some discussion of creating a "stored procedure" language that would execute outside the database engine, but still on the server side of the network connection. In that sort of context it would be reasonable to let the user do SAVEPOINT/ROLLBACK (or any other SQL command). But our existing PLs most definitely execute inside the engine, and therefore they can't expose facilities that imply arbitrary changes in the subtransaction state stack. 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] cvs web errors
Joe Conway <[EMAIL PROTECTED]> writes: > Anyone else seeing problems with cvs web? For at least the past week, > I'm seeing: > Error: pgsql/pgsql/src/Makefile.shlib.diff: no such file or directory > whenever I try to do "Diff to previous". It is also gawdawful slow in > arriving at that error. It works for me, though I have noticed it is a great deal slower than it was a few weeks ago. (I see response times of 5-15 seconds instead of a couple seconds.) Did you repoint your bookmark to http://developer.postgresql.org/cvsweb.cgi/pgsql/ instead of pgsql-server? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error handling in plperl and pltcl
On Fri, 2004-11-19 at 16:58 -0500, Tom Lane wrote: > What I think we ought to do is change both PL languages so that every > SPI call is executed as a subtransaction. If the call elogs, we can > clean up by aborting the subtransaction, and then we can report the > error message as a Perl or Tcl error condition, which the function > author can trap if he chooses. If he doesn't choose to, then the > language interpreter will return an error condition to plperl.c or > pltcl.c, and we can re-throw the error. I do this already in my plpy, save the subtransaction handling "feature". In plpy, all Postgres ERRORs are caught and transformed into Python exceptions, then when the interpreter exits with a Python exception, it is transformed back into a Postgres ERROR and raised. I even created a class of Python exceptions for Postgres ERRORs(e.g. raise Postgres.ERROR('msg', code=someErrCode, hint='foo')). (And more specific classes as well, putting errcodes to good use.) I plan(well, already working on it) to create Python interfaces to Postgres transaction facilities so that the author can start, rollback, and commit subxacts as needed for use/cleanup. Of course, I feel that this is the best way to go AFA subxacts are concerned; leaving the details to the author. I have been playing with RollbackToSavepoint and ReleaseSavepoint, but per Neil's comments on IRC and the fact that I have to annoyingly construct a List containing the savepoint name. I get the feeling that I am not meant to use them. If they are provided for possible use, shouldn't they take a string instead of a List? (Is a List used here to discourage use?) -- Regards, James William Pye signature.asc Description: This is a digitally signed message part
Re: [HACKERS] pgxs regression
Joe Conway <[EMAIL PROTECTED]> writes: > Indeed, backing out Bruce's commit > ( http://archives.postgresql.org/pgsql-committers/2004-10/msg00158.php ) > makes my problems go away. I have no idea how to fix the win32 stuff > though :( Looking more closely, those dependencies were wrong anyway since they didn't cause changes to propagate. I've committed corrections. The win32 pthread stuff looks to me like it still doesn't work, but that is someone else's problem to fix. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error handling in plperl and pltcl
I wrote: > I get about 6900 vs 12800 msec, so for a simple pre-planned query > it's not quite a 50% overhead. However, that was yesterday ;-). I did some profiling and found some easy-to-knock-off hotspots. Today I'm measuring about 25% overhead for a simple SELECT, which I think is entirely acceptable considering the cleanliness of definition that we're buying. I changed my test cases to be create or replace function foo(int,int) returns int as ' declare x int; begin for i in 1 .. $1 loop select into x unique1 from tenk1 where unique2 = $2; end loop; return x; end' language plpgsql; create or replace function foos(int,int) returns int as ' declare x int; begin for i in 1 .. $1 loop begin select into x unique1 from tenk1 where unique2 = $2; exception when others then null; end; end loop; return x; end' language plpgsql; so as to minimize the extraneous overhead --- I think this is a harder test (gives a higher number) than what I was doing yesterday. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pgxs regression
Tom Lane wrote: My inclination is to get rid of the dependency of the Makefile.shlib targets on pg_config_paths.h; Bruce has never satisfactorily explained why he added that. There may be a need for a dependency on pg_config_paths.h someplace, but surely this is the wrong place. I don't understand what $(PTHREAD_H_WIN32) is doing there either --- the only definition of it that I can see is PTHREAD_H_WIN32 = yes in libpq/Makefile, which does not look like a build target. (The comment just above that saying "this doesn't work yet" doesn't increase my faith any...) Indeed, backing out Bruce's commit ( http://archives.postgresql.org/pgsql-committers/2004-10/msg00158.php ) makes my problems go away. I have no idea how to fix the win32 stuff though :( Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error handling in plperl and pltcl
Tom Lane <[EMAIL PROTECTED]> writes: > (I'm sure we can do more to speed up subtransaction entry/exit than we have > so far.) Is there anything that can be done to short circuit the _first_ layer of subtransaction? I'm thinking there will be many cases like this where there's one implicit subtransaction that users don't even know is there. in particular I'm thinking of psql introducing a subtransaction on every query to allow recovery from typos and other errors. Drivers may do something similar to allow the application to catch errors using language constructs like exceptions and recover. In many environments there will be one layer of subtransaction on every query. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pgxs regression
Joe Conway <[EMAIL PROTECTED]> writes: > I'll go take a look at the thread (haven't yet) but as of the moment > this is not fixed in cvs. Here are two examples from contrib (in each > case I inserted "USE_PGXS = 1" into the Makefile): > # cd contrib/dblink/ > # vi Makefile > # make > make: *** No rule to make target > `/usr/local/pgsql-dev/lib/pgxs/src/makefiles/../../src/port/pg_config_paths.h', > > needed by `all-static-lib'. Stop. My inclination is to get rid of the dependency of the Makefile.shlib targets on pg_config_paths.h; Bruce has never satisfactorily explained why he added that. There may be a need for a dependency on pg_config_paths.h someplace, but surely this is the wrong place. I don't understand what $(PTHREAD_H_WIN32) is doing there either --- the only definition of it that I can see is PTHREAD_H_WIN32 = yes in libpq/Makefile, which does not look like a build target. (The comment just above that saying "this doesn't work yet" doesn't increase my faith any...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] cvs web errors
Anyone else seeing problems with cvs web? For at least the past week, I'm seeing: Error: pgsql/pgsql/src/Makefile.shlib.diff: no such file or directory whenever I try to do "Diff to previous". It is also gawdawful slow in arriving at that error. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error handling in plperl and pltcl
Thomas Hallgren <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> That's what pltcl has always done, and IMHO it pretty well sucks :-( >> it's neither intuitive nor useful. >> > Given that most SPI actions that you do doesn't elog (most of them are > typically read-only), it's far more useful than imposing the overhead of > a subtransaction on all calls. That IMHO, would really suck :-( I don't think we really have any alternative --- certainly not if you want to continue to regard plperl as a trusted language. I haven't bothered to develop a test case, but I'm sure it's possible to crash the backend by exploiting the lack of reasonable error handling in spi_exec_query. There's an ancient saying "I can make this code arbitrarily fast ... if it doesn't have to give the right answer". I think that applies here. Fast and unsafe is not how the Postgres project customarily designs things. I'd rather get the semantics right the first time and then look to optimize later. (I'm sure we can do more to speed up subtransaction entry/exit than we have so far.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pgxs regression
Thomas Hallgren wrote: Joe Conway wrote: The problem is related specifically to Makefiles using MODULE_big. I tested a few contribs that use MODULES and they seem to work fine under pgxs. Seems you have the same issue that I have with PL/Java. I had to explicitly change my target from: all: all_lib to: all: $(shlib) The thread "Problems with pgxs" started in hackers on 10/31 may be of some interest. I'll go take a look at the thread (haven't yet) but as of the moment this is not fixed in cvs. Here are two examples from contrib (in each case I inserted "USE_PGXS = 1" into the Makefile): # cd contrib/dblink/ # vi Makefile # make make: *** No rule to make target `/usr/local/pgsql-dev/lib/pgxs/src/makefiles/../../src/port/pg_config_paths.h', needed by `all-static-lib'. Stop. # cd ../pgcrypto/ # vi Makefile # make sed 's,MODULE_PATHNAME,$libdir/pgcrypto,g' pgcrypto.sql.in >pgcrypto.sql make: *** No rule to make target `/usr/local/pgsql-dev/lib/pgxs/src/makefiles/../../src/port/pg_config_paths.h', needed by `all-static-lib'. Stop. As stated above, *all* contribs using MODULE_big fail in this same manner. Do they all need the workaround mentioned above? Seems to me like we need a better solution than that. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SRF related and other questions
Katsaros Kwn/nos wrote: Either set it to: "setof _some_predifined_type" or set it to: "setof records" and then define the expected results with "as(attr1 type, ..., attr_n type)". Right? Correct. Except it should be "setof record", not "setof records". If these are indeed the only ways, is it possible to write an SRF whose return type is defined inside the code (something like the second way mentioned above but altering TupleDesc or something like that?) based on the executed query? What I want to do is to write an SRF, which will execute a query maybe different than (but derived from) the original one passed to this function. Obviously the first way is not suitable since I cannot know a priori (before entering my SRF) what the result type will exactly be. If I understand correctly what you are asking, the answer is yes ;-) When returning "setof record", the column definition must exist in the query, and must match what ultimately is returned. This means that whatever logic you use in your application to write the sql statement must be able to derive the appropriate column types. That said, inside your function you have two choices (at least): -- you can directly determine the column definition used in the sql statement, as in dblink_record() /* get the requested return tuple description */ tupdesc = CreateTupleDescCopy(rsinfo->expectedDesc); -- you can use the same logic that your application did to derive the column desc and build it yourself, similar to make_crosstab_tupledesc() in contrib/tablefunc (see line 1636 in cvs HEAD sources) Second, could you please tell me where in the code an incoming request, from a remote dblink_record() call, is handled? I'm a little lost here :-) I don't understand what you're asking here. Can you elaborate? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] SRF related and other questions
Hi, First, I have the following question on Set Returning Functions: Regarding the return type, there are two ways of defining it: Either set it to: "setof _some_predifined_type" or set it to: "setof records" and then define the expected results with "as(attr1 type, ..., attr_n type)". Right? If these are indeed the only ways, is it possible to write an SRF whose return type is defined inside the code (something like the second way mentioned above but altering TupleDesc or something like that?) based on the executed query? What I want to do is to write an SRF, which will execute a query maybe different than (but derived from) the original one passed to this function. Obviously the first way is not suitable since I cannot know a priori (before entering my SRF) what the result type will exactly be. In general, (having the Query/Plan etc. available) are there any functions (or anything) that could help defining the type of the expected results? A solution I suppose would be to parse the Query but in this case I would like to know if there are any functions that would help create the TupleDesc and anything else requiered. Second, could you please tell me where in the code an incoming request, from a remote dblink_record() call, is handled? I'm a little lost here :-) Regards, Ntinos Katsaros
Re: [HACKERS] Relation does not exist
Title: RE: [HACKERS] Relation does not exist > "Brusser, Michael" <[EMAIL PROTECTED]> writes: > > Our customer reported a problem with Postgres v.7.3.2 > server on Solaris 8 > > I asked them to run a quick query and it looks like the database is > > corrupted: > > > select count (1) from t_revisioncontrol > > ERROR: Relation 17236 does not exist > > If you are lucky, the problem is just one of corrupted indexes on > the system catalogs (most likely pg_class_oid_index) and can be fixed > by REINDEXing. > > What happened to lead up to this? Usually this sort of thing doesn't > appear out of the blue. Any system crashes or anything? > > regards, tom lane All they said was that "they did not have power outage" REINDEX helped, many thanks! Mike.
Re: [HACKERS] Fwd: SELECT MAX with char => BUG?
Rodrigo Carvalhaes <[EMAIL PROTECTED]> writes: > My problem is the "select max(id) FROM test" the result is 20 but the > right is 1020. Is this a BUG or I am crazy ?? You seem to be confused about the difference between numbers and character strings. max() on a char(n) column returns the lexicographically largest item. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] New compile warnings
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am seeing the following compile warnings in CVS. I am using for perl: > Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: I believe these two: > plperl.c:948: warning: `ret_hv' might be used uninitialized in this function > plperl.c:949: warning: `ret_av' might be used uninitialized in this function indicate an actual bug --- at least, it's far from clear that the code can't try to use an uninitialized value. I trust that the authors of plperl will step up and fix it; I'm not sufficiently clear on what cases they are trying to support to want to touch it. The others indicate sloppiness in the C code generated by perl's XS functionality. There's nothing we can do about them. FWIW, less obsolete versions of Perl generate fewer warnings --- the only one of these that I see on 5.8.0 and up is > SPI.c:158: warning: unused variable `items' regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Fwd: SELECT MAX with char => BUG?
Rodrigo Carvalhaes wrote: Hi ! I am quite confused of the results on a SELECT max... My environment: Conectiva Linux 10, PostgreSQL 7.4.6 (compiled from the sources) My problem is the "select max(id) FROM test" the result is 20 but the right is 1020. Is this a BUG or I am crazy ?? Crazy. And posting to the wrong list - try the general/sql lists for this sort of thing. teste=# CREATE TABLE test ( id char(15), name char(80) ); CREATE You've defined "id" as char - so it's sorting alphabetically, not numerically, so < 2 Just defined "id" as a numeric type. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Trouble with plpgsql on 7.4.6
"D'Arcy J.M. Cain" <[EMAIL PROTECTED]> writes: > I thought that this would have sent everything to both the log and the > screen but I found that the syslog has much more detail. I have > attached that output. We still need to look at the stderr output. All this says is that you're getting repeated abort()s. It's unlikely that anything would be coded to abort() without emitting any gripe at all --- but the gripe is not appearing in syslog output, so stderr is the next place to look. Also see about getting a stack trace from one of the core dumps. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
Simon Riggs <[EMAIL PROTECTED]> writes: > On Thu, 2004-11-18 at 22:55, Tom Lane wrote: >> If it is a problem, the LockBuffer calls in RelationGetBufferForTuple >> would be the places showing contention delays. > You say this as if we can easily check that. I think this can be done with oprofile ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Fwd: SELECT MAX with char => BUG?
Hi ! I am quite confused of the results on a SELECT max... My environment: Conectiva Linux 10, PostgreSQL 7.4.6 (compiled from the sources) My problem is the "select max(id) FROM test" the result is 20 but the right is 1020. Is this a BUG or I am crazy ?? Cheers, Rodrigo Carvalhaes The SQL... teste=# CREATE TABLE test ( id char(15), name char(80) ); CREATE TABLE teste=# \d test Table "public.test" Column | Type | Modifiers +---+--- id | character(15) | name | character(80) | teste=# INSERT INTO test VALUES ( '10', 'luidgi'); INSERT 15303727 1 teste=# INSERT INTO test VALUES ( '20', 'luis'); INSERT 15303728 1 teste=# INSERT INTO test VALUES ( '1010', 'ruan'); INSERT 15303729 1 teste=# INSERT INTO test VALUES ( '1020', 'lion'); INSERT 15303730 1 teste=# SELECT * FROM test; id| name -+-- 10 | luidgi 20 | luis 1010| ruan 1020| lion (4 rows) teste=# SELECT max(id) FROM test; max - 20 (1 row) teste=# select max(id) FROM test; max - 20 (1 row) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Interesting parsing problem ...
I am willing to add NOWAIT to a couple of commands and I have tried to resolve a bison problem for quite some time now: As a first step I wanted to add NOWAIT to DELETE: DELETE FROM ... WHERE ... NOWAIT; Therefore I used: /* * * QUERY: * DELETE STATEMENTS * */ DeleteStmt: DELETE_P FROM relation_expr where_clause opt_nowait opt_nowait is already defined and used by Tatsuo's LOCK TABLE NOWAIT. I got ... [EMAIL PROTECTED] parser]$ bison -y -d -v gram.y conflicts: 6 shift/reduce ... 6 errors: State 1197 conflicts: 1 shift/reduce State 1198 conflicts: 1 shift/reduce State 1201 conflicts: 1 shift/reduce State 1852 conflicts: 1 shift/reduce State 1853 conflicts: 1 shift/reduce State 1855 conflicts: 1 shift/reduce ... 1196 | BCONST 1197 | XCONST ... 1201 | TRUE_P ... state 1852 1024 b_expr: b_expr '%' . 1030 | b_expr '%' . b_expr ABORT_Pshift, and go to state 146 ABSOLUTE_P shift, and go to state 147 ACCESS shift, and go to state 148 ACTION shift, and go to state 149 The interesting thing here is - if I change opt_nowait to DeleteStmt: DELETE_P FROM relation_expr where_clause opt_lock (just for tesing), I still get an error ... [EMAIL PROTECTED] parser]$ bison -y -d -v gram.y conflicts: 1 shift/reduce In my understanding of bison DELETE ... WHERE ... IN some_mode MODE; should work ... Can anybody provide me a fix? Basically all I wanted to do was SELECT FOR UPDATE NOWAIT, DELETE NOWAIT and UPDATE ... NOWAIT. The rest of the patch seems to be fairly simple but can anybody lead me out of "parser's hell"? Best regards, Hans ---(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] New compile warnings
I am seeing the following compile warnings in CVS. I am using for perl: Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration: --- gmake[2]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/test/regress' gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/test' plperl.c:948: warning: `ret_hv' might be used uninitialized in this function plperl.c:949: warning: `ret_av' might be used uninitialized in this function /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used SPI.c:25: warning: no previous prototype for `XS__elog' SPI.c:40: warning: no previous prototype for `XS__DEBUG' SPI.c:55: warning: no previous prototype for `XS__LOG' SPI.c:70: warning: no previous prototype for `XS__INFO' SPI.c:85: warning: no previous prototype for `XS__NOTICE' SPI.c:100: warning: no previous prototype for `XS__WARNING' SPI.c:115: warning: no previous prototype for `XS__ERROR' SPI.c:130: warning: no previous prototype for `XS__spi_exec_query' SPI.c:157: warning: no previous prototype for `boot_SPI' SPI.c:158: warning: unused variable `items' /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] [JDBC] Strange server error with current 8.0beta driver
Barry Lind wrote: Environment #1: WinXP 8.0beta4 server, 8.0jdbc client 2004-11-19 12:19:06 ERROR: unrecognized node type: 25344832 Environment #2: Sun Solaris 7.4.3 server, 8.0jdbc client ERROR: no value found for parameter 1 From memory the 7.4.3 behaviour you see can happen if you DECLARE CURSOR with a parameterized query (executes OK) then try to FETCH from it (fails with the above error, as the original parameter values from DECLARE execution are not stored with the portal). The parameterization can happen either via V3 protocol Parse/Bind or via function execution, IIRC. I put together a patch to fix this that made it into 8.0 (with some changes IIRC), perhaps the bug lies in there somewhere. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Testperf-general] Re: [HACKERS] ExclusiveLock
On Thu, 2004-11-18 at 22:55, Tom Lane wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > >> The main problem on INSERTs is that it is usually the same few pages: > >> the lead data block and the lead index block. There are ways of > >> spreading the load out across an index, but I'm not sure what happens on > >> the leading edge of the data relation, but I think it hits the same > >> block each time. > > > I actually have several test cases for this, can you give me a trace or > > profile suggestion that would show if this is happening? > > If it is a problem, the LockBuffer calls in RelationGetBufferForTuple > would be the places showing contention delays. You say this as if we can easily check that. My understanding is that this would require a scripted gdb session to instrument the executable at that point. Is that what you mean? That isn't typically regarded as a great thing to do on a production system. You've mentioned about performance speculation, which I agree with, but what are the alternatives? Compile-time changes aren't usually able to be enabled, since many people from work RPMs. > It could also be that the contention is for the WALInsertLock, ie, the > right to stuff a WAL record into the shared buffers. This effect would > be the same even if you were inserting into N separate tables. ...and how do we check that also. Are we back to simulated workloads and fully rigged executables? -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Beta5 Scheduale
Marc G. Fournier schrieb: Just a quick note, since we obviously passed the previous date we were aiming for ... we're aiming for Sunday evening to roll Beta5 ... all the major stuff that we felt were outstanding have been committed, and a *large* # of the smaller patches, but Bruce is working through his list and would like to get as many in as possible before Beta5 ... I'll also roll out a new cygwin beta5 test package for wider test audience. The last I did was between beta2 and beta3. -- Fear is that little darkroom where negatives are developed. (Michael Pritchard) ---(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] Error handling in plperl and pltcl
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: My approach with PL/Java is a bit different. While each SPI call is using a try/catch they are not using a subtransaction. The catch will however set a flag that will ensure two things: 1. No more calls can be made from PL/Java to the postgres backend. 2. Once PL/Java returns, the error will be re-thrown. That's what pltcl has always done, and IMHO it pretty well sucks :-( it's neither intuitive nor useful. Given that most SPI actions that you do doesn't elog (most of them are typically read-only), it's far more useful than imposing the overhead of a subtransaction on all calls. That IMHO, would really suck :-( Ideally, the behavior should be managed so that if a subtransaction is started intentionally, crash recovery would be possible and the function should be able to continue after it has issued a rollback of that subtransaction. I'm suprised you say that this is not useful. I've found that in most cases when you encounter an elog, this is the most intuitive behavior. Either you don't do any cleanup, i.e. just return and let the elog be re-thrown, or you close some files, free up some resources or whatever, then you return. Not many functions would continue executing after an elog, unless of course, you *intentionally* started a subtransaction. I'll investigate what's entailed in handling SPI calls performed in a subtransaction differently so that calls are blocked only until the subtransaction is rolled back. Since I have my own JDBC driver, that doesn't sound too hard. I guess PL/Perl and PL/Tcl has something similar where they could track this. Such handling, in combination with a "recoverable" status in the elog's error structure, would create a really nice (end efficient) subsystem. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]