[HACKERS] More schema queries
Hi, I have some schema queries/thoughts that I would appreciate some help/insights/fixes with/for please! (Apologies if these have been asked before or have been addressed in a recent snapshot - my ISP's been having routing problems recently I can't reach postgresql.org via http right now). 1) All the system views are currently part of the public namespace. Not a problem for me, but shouldn't they be in pg_catalog? 2) pgAdmin needs to be able to find out the namespace search path for the current connection through an SQL query - is this possible yet or can/will a suitable function be written? There were more than that when I started typing this but I had a flash of inspiration and they went away :-) TIA, Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] interfaces/ecpg/preproc reduce/reduce conflicts
On Thu, 16 May 2002, Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: Just noticed this a few minutes ago on build from cvs tip: make -C preproc all make[4]: Entering directory `/opt/src/pgsql/src/interfaces/ecpg/preproc' bison -y -d preproc.y conflicts: 2 reduce/reduce Yeah, the ECPG grammar has been broken for awhile. I'm expecting Michael to do something about it sooner or later ... It's not just the grammar. Last time I tried to compile OSDB to get some benchmarking done, ecpg segfaulted on it (before having any reduce conflict). I tried to do some investigation, but my knowledge was too limited and couldn't even generate a decent bug report. -- Alvaro Herrera (alvherre[@]dcc.uchile.cl) Nunca se desea ardientemente lo que solo se desea por razon (F. Alexandre) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Queries using rules show no rows modified?
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Michael seems to feel that the tuple count should be nonzero if any of the replacement operations did anything at all. Here we usually add triggers, for replication, accounting, setting of calculated rows ... In all of our cases we want the addition of a trigger (or rule on a table) to be transparent to the client. Yeah. Triggers wouldn't affect this anyway, unless they tell the system to suppress insertion/update/deletion of some tuples, in which case I think it is correct not to count those tuples (certainly that's how the code has always acted). As far as rules go, the last proposal that I made would return the tuple count of the original query as long as there were no INSTEAD rules --- if you have only actions *added* by rules then they are transparent. The hard case is where the original query is not executed because of an INSTEAD rule. As the code presently stands, you get UPDATE 0 (or INSERT or DELETE 0) in that case, regardless of what else was done instead by the rule. I thought that was OK when we put the change in, but it seems clear that people do not like that behavior. The notion of keep it transparent doesn't seem to help here. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Poster(s) needed
Hi, since we will show PostgreSQL related stuff on Linuxtag in Germany next month, I'd like to get some PostgreSQL posters for the booth. But I have no idea where to find some. Do we have that kind of stuff? Or where could I get it? Preferable of course as file so I can print it myself. Thanks in advance Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Trouble with pg_encoding_to_char
Hi, I've been developing a program with the postgres jdbc 2 driver, jdk-1.3.0 and postgres 6.5. When I start my program up it bombs like so: Something unusual has occured to cause the driver to fail. Please report this exception: Exception: java.sql.SQLException: ERROR: No such function 'pg_encoding_to_char' with the specified attributes Stack Trace: java.sql.SQLException: ERROR: No such function 'pg_encoding_to_char' with the specified attributes at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94) at org.postgresql.Connection.ExecSQL(Connection.java:398) at org.postgresql.Connection.ExecSQL(Connection.java:381) at org.postgresql.Connection.openConnection(Connection.java:314) at org.postgresql.Driver.connect(Driver.java:149) Does anyone know what any of this means...? Regards, Youenn Université de Bretagne sud http://www.univ-ubs.fr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WIN32 native ... lets start?!?
On Fri, 17 May 2002, Christopher Kings-Lynne wrote: Maybe Vince could set up a Win32 porting project page, and since we now seem to have a few interested parties willing to code on a native Win32 version, they should have their own project page. This could make communication easier for them and make sure the project doesn't die... Might be an idea to create a pgsql-hackers-win32 list also? Or just pgsql-win32? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Joerg Hessdoerfer Sent: Friday, 17 May 2002 4:36 AM To: Magnus Naeslund(f) Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] WIN32 native ... lets start?!? On Thursday 16 May 2002 22:10, you wrote: [...] What is the biggest problem here? The Shmem/IPC stuff, or the fork() stuff? I'm think that we could do a fork() implementation in usermode by copying the memory allocations. How fast that would be regarding the context switches, i don't know, but i'm willing to experiment some to see how feesible this is... Anyone tried this before? Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster The problem is not the fork() call itself, this has been done (MinGW and cygwin I know of, possibly others) but the speed of fork() on windows, it's creepingly slow (due to usermode copy, I assume ;-). IPC needs to be done, I'm just about to start... Greetings, Joerg -- Leading SW developer - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updated CREATE FUNCTION syntax
-Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:37 AM To: Joel Burton Cc: PostgreSQL Development Subject: RE: [HACKERS] Updated CREATE FUNCTION syntax Joel Burton writes: Is there any standardized way of handling the single-quotes within function definition? Rather than doubling them up (which can make for very messy code when your scripting language uses single quotes!), allowing another symbol to be used, with that symbol be declared in the CREATE FUNCTION line? Interbase uses a system like this: you can set the delimiter to anything you want and use that instead of '. I think we need something like that. How exactly does Interbase set the delimiter? Keep in mind that our lexer and parser are static. Actually, now that I've thought about it for a moment, Interbase doesn't use a different delimiter, it allows a different end-of-line character. I've forgotten the exact syntax, but it's something like (Interbase doesn't allow functions like this, it uses these for stored procedures, but the basic idea is here): SELECT * FROM SOMETHING; SET EOL TO ; CREATE FUNCTION() RETURNS ... AS BEGIN; END; LANGUAGE plpgsql SET EOL TO ; SELECT * FROM SOMETHING; So that it's legal to use ; in the function, since the parser is looking for a different character to end the complete statement. I think it would be more straightforward to see something like: CREATE FUNCTION XXX() RETURNS ... AS # BEGIN; END; # LANGUAGE plpgsql DELIMITER #; But, with a static lexer/parser, that would be tricky, wouldn't it? Would it work to allow, rather than free choice of delimiters, to allow something other than single quote? Probably 95% of functions contain single quotes (and many scripting languages/development environments treat them specially), guaranteeing that you'll almost always have to double (or quad- or oct- or whatever!) your single quotes. If it's not too offensive, would something like CREATE FUNCTION XXX() RETURNS AS [[ BEGIN; END; ]] LANGUAGE plpgsql DELIMITED BY BRACES; work? Without the delimited by braces, the functions would be parsed the same (single quotes), with this, it would allow [[ and ]]. Someone who used [[ or ]] in their functions (perhaps as a custom operator or in a text string) would have to quote these (\[\[ and \]\]), but this would be __much__ less frequent than having to deal with single quotes. Nothing should break, since they have to choose to use the 'delimited by braces' option. It's not as nice as getting to choose your own delimiter, but it would solve the problem for most of us just fine and wouldn't seem too hard to implement. Functions are in SQL99, aren't they? Does the standard suggest anything here? - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] www.pgaccess.org - the official story (the way I saw it)
What about http://sourceforge.net/projects/pgaccess/? It looks inactive but somebody did set it up on 2002-04-25. I think I found it from Teo's website. MikE To sum it up - - pgaccess has not been officially updated since January 2001 = there is no real interest in it or the interest is not public - the author has no time = the project has no leader - there are several people actively working on it = there is some interest - the author gives us the chance to bring life = if we like it we must get it So we did. We took the www.pgaccess.org domain (on the name of Teo). We set up a server. And we started searching for the latest pgaccess versioin to insert it into the cvs. First I thought Teo should have the latest version. He said - no, it should be with the PostgreSQL distribution. I went there, but it did not seem very fresh. Then I continued my investigation and wrote to the [EMAIL PROTECTED] - my goal was to really find all patches and intersted people and to bring the project to some useful place. Vince Vielhaber wrote back that I should ask the HACKERS. So I did. And now we are here. We heard a lot of opinions from different sides. I would make the following summary - 1] During the last 1 year there has not been an active interest in and/or development of pgaccess. Or if it has been - it has not been very official. 2] Currently there are at least four people who actively need pgaccess and write for it - Bartus, Chris, Boyan and myself. 3] To talk about pgaccess without talking about PostgreSQL is a nonsense - pgaccess has one purpose and this is PostgreSQL. 4] PostgreSQL is too much bigger than pgaccess (organizationwize) - the proximity kills pgaccess. PostgreSQL is PostgreSQL. It is great - that's why we spent so much time trying to do something about it. Bug pgaccess is not PostgreSQL - it is one of the great tools around PostgreSQL and must be independent. 5] gborg is a mess (I hope I do not hurt anybody's feelings) - just see the broken images on first page that have not been fixed for at least several days. And the missing search. I have been searching in gborg for pgaccess several times - and I could not find it. I have the feeling that before gborg there was a very pretty postgresql.org style page with the projects - what happened to it? PROPOSAL What pgaccess needs is some fresh air - it needs a small and fresh team. It needs own web site, own cvs, own mailing list. So that the people who love it, write for it and really need it can be easy to identify and to talk to. This will not break its relationship to PostgreSQL in any way (see 3] above) At the end - I am not experienced how decisions are taken in an open source community - I have no idea what is next. May be one can write a summary what are the bad sides of the above proposal. And if there are no such really - we should just proceed and have this nice tool alive and running. Thanks everybody, Iavor -- www.pgaccess.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [INTERFACES] libpgtcl - backend version information patch
Nigel J. Andrews writes: I've attached a patch for libpgtcl which adds access to backend version numbers. This is via a new command: pg_version db channel major varname ?minor varname? ?patch varname? This doesn't truly reflect the way PostgreSQL version numbers are handled. Say for 7.2.1, the major is really 7.2 and the minor is 1. With the interface you proposed, the information major == 7 doesn't really convey any useful information. I envisage this patch applied to 7.3 tip and to 7.2 for the 7.2.2 release mentioned a couple of days ago. The only problem with doing this for 7.2 that I can see is where people doing the 'package -exact require Pgtcl 1.x' thing, and how many of those are there? Even PgAccess doesn't use that. Normally we only put bug fixes in minor releases. PgAccess may get an exception, but bumping the version number of a library is stretching it a little. If you're intending to use the function for PgAccess, why not make it internal to PgAccess? That way you can tune the major/minor thing exactly how you need it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error on PQputline()
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 4:10 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Error on PQputline()
Dann Corbit [EMAIL PROTECTED] writes:
The contents of the error message are:
conn-errorMessage.data 0x00312440 pqFlush() --
couldn't send data:
errno=0
No error A non-blocking socket operation could not be completed
immediately.
You're running libpq with the nonblocking mode selected?
Actually no. It should be the default mode for a connection made by
PQconnectdb(). That's what made the error so puzzling.
What is the correct recovery action?
Redesign libpq's nonblock mode :-(. It's a mess; a quick hack that
doesn't even try to cover all cases, and is unreliable in the ones it
does cover. You can find my previous rants on the subject in the
archives from a couple years back (around Jan '00 I believe). IMHO
we should never have accepted that patch at all.
Short of that, don't use the COPY code with nonblock.
I am trying to figure out if it is faster to bulk copy from a file on
the server or using an API from the client. It boils down to this:
Would it be faster to write a file to disk and read it again on the
local host for the server or to send the calls via libpq client
messages?
It could be that the TCP/IP overhead exceeds the overhead of writing the
file to disk and reading it again.
I have a data statement (in test.h) that consists of 1.6 million rows of
data to spin into the database.
Here is the complete program:
#include windows.h
#include stdlib.h
#include time.h
#include libpq-fe.h
#include glob.h /* member variables in the objects */
#include test.h
int init_comm(void)
{
WORDwVersionRequested;
WSADATA wsaData;
int err;
wVersionRequested = MAKEWORD(2, 2);
err = WSAStartup(wVersionRequested, wsaData);
if (err != 0) {
/* Tell the user that we could not find a usable */
/* WinSock DLL. */
return 0;
}
return 1;
}
voidProcessTuples(void);
int ExecuteImmediate(char *command, Qtype q_t)
{
int problem = 0;
#ifdef _DEBUG
printf(%s\n, command);
#endif
result = PQexec(conn, command);
switch (rc = PQresultStatus(result)) {
/* We should never actually call this. Left in for debugging...
*/
/* All tuple processing is handled low-level to pass data back
to
* CONNX */
case PGRES_TUPLES_OK: /* Data set successfully created */
#ifdef _DEBUG
printf(#rows affected %s\n, PQcmdTuples(result));
#endif
ProcessTuples();
break;
case PGRES_EMPTY_QUERY: /* Empty query supplied -- do nothing...
*/
case PGRES_COMMAND_OK: /* Query succeeds, but returns no
results */
/* If we did a select, we should (at least) have a result set of
* empty tuples. */
if (q_t == QUERY_TYPE_SELECT)
problem = 1;
break;
case PGRES_BAD_RESPONSE:
case PGRES_NONFATAL_ERROR:
case PGRES_FATAL_ERROR:
{
problem = 1;
}
}
if (q_t == QUERY_TYPE_INSERT) {
InsertedOID = PQoidValue(result);
#ifdef _DEBUG
printf(OID of inserted row is %lu\n, (unsigned long)
InsertedOID);
#endif
}
PQclear(result);
return problem;
}
voidHandleProblem(void)
{
const char *m1 = PQresStatus(rc);
const char *m2 = PQresultErrorMessage(result);
#ifdef __cplusplus
String err = m1;
err = err + m2;
throw Mcnew CPOSTGRESQLException(conn, rc, (LPCSTR) err,
szSQLState);
#endif
#ifdef _DEBUG
printf(status is %s\n, m1);
printf(result message: %s\n, m2);
#endif
}
voidBeginTrans(void)
{
int problem;
problem = ExecuteImmediate(BEGIN work, QUERY_TYPE_TRANSACT);
if (problem)
HandleProblem();
}
voidCommitTrans(void)
{
int problem;
problem = ExecuteImmediate(COMMIT work, QUERY_TYPE_TRANSACT);
if (problem)
HandleProblem();
}
voidRollbackTrans(void)
{
int problem;
problem = ExecuteImmediate(ROLLBACK work, QUERY_TYPE_TRANSACT);
if (problem)
HandleProblem();
}
voidProcessTuples()
{
nrows = PQntuples(result);
nfields = PQnfields(result);
#ifdef _DEBUG
printf(number of rows returned = %d\n, nrows);
printf(number of fields returned = %d\n, nfields);
#endif
for (r = 0; r nrows; r++) {
for (n = 0; n nfields; n++)
printf( %s = %s(%d),,
PQfname(result, n),
PQgetvalue(result, r, n),
PQgetlength(result, r, n));
printf(\n);
}
}
static long cursor_number = 0;
int main(void)
{
int problem;
int i = 0;
struct tm
Re: [HACKERS] Queries using rules show no rows modified?
On Fri, 10 May 2002 10:51:05 -0400, Tom Lane [EMAIL PROTECTED] wrote: Thoughts, different proposals, alternative ways of breaking down the problem? Well, you asked for it, so here is my wishlist :-) From a user POV I expect a command to return the number of rows it has processed successfully. By rows I mean rows of the table (or view or whatever) my command (seemingly) handles, I'd not be interested in any side effects my command has because of triggers and/or rules. Suppose there is a user called Al B. If, for example, his DB designer gives him a table foo (id int, name text) to store his data, he may consider this table as a black box. Al does not want to (and probably even should not) know about rules and triggers. So when he enters INSERT INTO foo VALUES (10, 'ten'); he expects to get INSERT nnn 1 or an error message. He doesn't care for any INSERTs into changelogs or UPDATEs to accounting data, he just wants to know whether *his* INSERT was successful. Next, if Al enters INSERT INTO foo SELECT ... FROM bar WHERE ... and the SELECT statement returns 47 rows, he expects INSERT 0 47 if there is no problem. UPDATE foo ... WHERE ... Here the WHERE clause identifies a certain number of rows which are to be updated. Again this number should be returned as the tuple count. Same for DELETE. A. If original command is executed (no INSTEAD), return its tag as-is, regardless of commands added by rules. Yes, please. This is fully compatible with my wishes. B. If original command is not executed, then return its tag name Agreed. plus required fields defined as follows: tuple count is sum of tuple counts of all replacement commands. No, please don't care about replacement commands. If a rule can be viewed as something that is executed for each row, then simply let each row that is processed successfully contribute 1 to the tuple count. (Well, I know, this is not always easy. I guess it's easier for INSERT and harder for UPDATE and DELETE. But isn't it a nice goal?) While I'm fairly sure about my preferences up to here, there are some points I don't have a strong opinion on: OIDs: With an ordinary table the OID returned by INSERT can be used to retrieve the new row with SELECT ... WHERE oid=nnn. Ideally this would hold for tables and views with rules, but there is no easy way for the backend to know the correct OID, when there are more than 1 INSERT statements in the rule. So here's one more idea for your sub-case 2c: Let the programmer specify which OID to return, maybe by an extension to the INSERT syntax, allowed only in rules: INSERT INTO ... VALUES (...) RETURNING OID ??? DO INSTEAD NOTHING: Should this be considered successful execution or should it contribute 0 to the tuple count? I don't know which one is less surprising. I tend to the latter. Just my 0.02. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] Bug #659: lower()/upper() bug on
Tatsuo Ishii wrote: Are you sure that say, de_DE.utf8 locale produce meaningful results for any other languages? there are often subtle differences, but upper() and lower() are much more likely to produce right results than collation order or date/money formats. in fact seem to be only 10 distinct LC_CTYPE files for ~110 locales with most european-originated languages having the same and only tr_TR, zh_??, fr_??,da_DK, de_??, ro_RO, sr_YU, ja_JP and ko_KR having their own. I see. So the remaining problem would be how to detect the existence of *.utf8 collation at the configure time. If so, why are there so many *.utf8 locales? As I understand it, a locale should cover all locale-specific issues btw, does Japanese language have distinct upper and lower case letters ? There are full width alphabets in Japanese. Thoes include not only ASCII letters but also some European characters. Are these ASCII and European characters uppercased in some Japanese-specific way ? Probably not, but I'm not sure since my Linux box does not have *.utf8 locales. Could you give me the UTF-8 bytecode for one japanese upper case char and for the same char the lower case? I will check in de_DE locale if this translations works. Michael ---(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] Updated CREATE FUNCTION syntax
On Fri, 17 May 2002 09:57:39 -0400, Joel Burton [EMAIL PROTECTED] wrote: -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:37 AM To: Joel Burton Cc: PostgreSQL Development Subject: RE: [HACKERS] Updated CREATE FUNCTION syntax I think we need something like that. How exactly does Interbase set the delimiter? Keep in mind that our lexer and parser are static. Actually, now that I've thought about it for a moment, Interbase doesn't use a different delimiter, it allows a different end-of-line character. Actually it's the end-of-command delimiter, called terminator in Interbase speech. And it doesn`t have to be a single character, e.g. SET TERM !! ; SELECT * FROM SOMETHING; SET EOL TO ; CREATE FUNCTION() RETURNS ... AS BEGIN; END; LANGUAGE plpgsql You could even enter any number of commands here, each terminated by the current terminator: SELECT * FROM MYTABLE DROP TABLE MYTABLE SET TERM ! SELECT * FROM ANOTHERTABLE ! ... before you eventually return to the standard terminator: SET TERM ; ! SELECT * FROM WHATEVER ; Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WIN32 native ... lets start?!?
Marc G. Fournier [EMAIL PROTECTED] writes: Might be an idea to create a pgsql-hackers-win32 list also? Or just pgsql-win32? Actually, I think that'd be a bad idea. The very last thing we need is for these discussions to get fragmented. The issues affect the whole backend AFAICS. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] More schema queries
Dave Page [EMAIL PROTECTED] writes: 1) All the system views are currently part of the public namespace. Not a problem for me, but shouldn't they be in pg_catalog? Say what? They *are* in pg_catalog. initdb creates nothing in public. 2) pgAdmin needs to be able to find out the namespace search path for the current connection through an SQL query - is this possible yet or can/will a suitable function be written? Either 'show search_path' or 'select current_schemas()' might do what you want; or perhaps not. Why do you want to know the search path? What's the scenario in which pgAdmin wouldn't set the search path for itself? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] +AFs-HACKERS+AF0- More schema queries
+AD4- -Original Message- +AD4- From: Tom Lane +AFs-mailto:tgl+AEA-sss.pgh.pa.us+AF0- +AD4- Sent: 17 May 2002 21:26 +AD4- To: Dave Page +AD4- Cc: pgsql-hackers+AEA-postgresql.org +AD4- Subject: Re: +AFs-HACKERS+AF0- More schema queries +AD4- +AD4- +AD4- +ACI-Dave Page+ACI- +ADw-dpage+AEA-vale-housing.co.uk+AD4- writes: +AD4- +AD4- 1) All the system views are currently part of the public namespace. +AD4- +AD4- Not a problem for me, but shouldn't they be in pg+AF8-catalog? +AD4- +AD4- Say what? They +ACo-are+ACo- in pg+AF8-catalog. initdb creates nothing +AD4- in public. You'll have to take my word for it that I haven't played with pg+AF8-class - is it possible I got a snapshot that was built at precisely the wrong moment? helpdesk+AD0AIw- select +ACo- from pg+AF8-namespace+ADs- oid +AHw- nspname +AHw- nspowner +AHw- nspacl ---+--+---+ 11 +AHw- pg+AF8-catalog +AHw-1 +AHw- +AHsAPQ-U+AH0- 99 +AHw- pg+AF8-toast+AHw-1 +AHw- +AHsAPQB9- 2200 +AHw- public +AHw-1 +AHw- +AHsAPQ-UC+AH0- 16563 +AHw- pg+AF8-temp+AF8-1 +AHw-1 +AHw- 40071 +AHw- Test Schema +AHw-1 +AHw- 48273 +AHw- flurb +AHw-1 +AHw- 40072 +AHw- test+AHw-1 +AHw- +AHsAPQ-UC,postgres+AD0-UC+AH0- 48276 +AHw- dave2 +AHw-1 +AHw- 48277 +AHw- Gulp+AHw-1 +AHw- +AHsAPQ-UC,postgres+AD0-UC+AH0- (9 rows) helpdesk+AD0AIw- select relnamespace, relname from pg+AF8-class where relname like 'pg+AF8AJQ-'+ADs- relnamespace +AHw- relname --+-- 11 +AHw- pg+AF8-largeobject 11 +AHw- pg+AF8-aggregate 11 +AHw- pg+AF8-trigger 11 +AHw- pg+AF8-listener 11 +AHw- pg+AF8-namespace 11 +AHw- pg+AF8-attrdef 11 +AHw- pg+AF8-database 11 +AHw- pg+AF8-xactlock 11 +AHw- pg+AF8-description 11 +AHw- pg+AF8-group 11 +AHw- pg+AF8-proc 11 +AHw- pg+AF8-relcheck 11 +AHw- pg+AF8-rewrite 2200 +AHw- pg+AF8-user 2200 +AHw- pg+AF8-rules 2200 +AHw- pg+AF8-views 2200 +AHw- pg+AF8-tables 2200 +AHw- pg+AF8-indexes 2200 +AHw- pg+AF8-stats 2200 +AHw- pg+AF8-stat+AF8-all+AF8-tables 2200 +AHw- pg+AF8-stat+AF8-sys+AF8-tables 11 +AHw- pg+AF8-aggregate+AF8-fnoid+AF8-index 11 +AHw- pg+AF8-am+AF8-name+AF8-index 11 +AHw- pg+AF8-am+AF8-oid+AF8-index 11 +AHw- pg+AF8-amop+AF8-opc+AF8-opr+AF8-index 11 +AHw- pg+AF8-amop+AF8-opc+AF8-strategy+AF8-index 11 +AHw- pg+AF8-amproc+AF8-opc+AF8-procnum+AF8-index 11 +AHw- pg+AF8-attrdef+AF8-adrelid+AF8-adnum+AF8-index 11 +AHw- pg+AF8-attribute+AF8-relid+AF8-attnam+AF8-index 11 +AHw- pg+AF8-attribute+AF8-relid+AF8-attnum+AF8-index 11 +AHw- pg+AF8-class+AF8-oid+AF8-index 11 +AHw- pg+AF8-class+AF8-relname+AF8-nsp+AF8-index 11 +AHw- pg+AF8-database+AF8-datname+AF8-index 11 +AHw- pg+AF8-database+AF8-oid+AF8-index 11 +AHw- pg+AF8-description+AF8-o+AF8-c+AF8-o+AF8-index 11 +AHw- pg+AF8-group+AF8-name+AF8-index 11 +AHw- pg+AF8-group+AF8-sysid+AF8-index 11 +AHw- pg+AF8-index+AF8-indrelid+AF8-index 11 +AHw- pg+AF8-index+AF8-indexrelid+AF8-index 11 +AHw- pg+AF8-inherits+AF8-relid+AF8-seqno+AF8-index 11 +AHw- pg+AF8-language+AF8-name+AF8-index 11 +AHw- pg+AF8-language+AF8-oid+AF8-index 11 +AHw- pg+AF8-largeobject+AF8-loid+AF8-pn+AF8-index 11 +AHw- pg+AF8-namespace+AF8-nspname+AF8-index 11 +AHw- pg+AF8-namespace+AF8-oid+AF8-index 11 +AHw- pg+AF8-opclass+AF8-am+AF8-name+AF8-nsp+AF8-index 11 +AHw- pg+AF8-opclass+AF8-oid+AF8-index 11 +AHw- pg+AF8-operator+AF8-oid+AF8-index 11 +AHw- pg+AF8-operator+AF8-oprname+AF8-l+AF8-r+AF8-n+AF8-index 11 +AHw- pg+AF8-proc+AF8-oid+AF8-index 11 +AHw- pg+AF8-proc+AF8-proname+AF8-args+AF8-nsp+AF8-index 11 +AHw- pg+AF8-relcheck+AF8-rcrelid+AF8-index 11 +AHw- pg+AF8-rewrite+AF8-oid+AF8-index 11 +AHw- pg+AF8-rewrite+AF8-rel+AF8-rulename+AF8-index 11 +AHw- pg+AF8-shadow+AF8-usename+AF8-index 11 +AHw- pg+AF8-shadow+AF8-usesysid+AF8-index 11 +AHw- pg+AF8-statistic+AF8-relid+AF8-att+AF8-index 11 +AHw- pg+AF8-trigger+AF8-tgconstrname+AF8-index 11 +AHw- pg+AF8-trigger+AF8-tgconstrrelid+AF8-index 11 +AHw- pg+AF8-trigger+AF8-tgrelid+AF8-tgname+AF8-index 11 +AHw- pg+AF8-trigger+AF8-oid+AF8-index 11 +AHw- pg+AF8-type+AF8-oid+AF8-index 11 +AHw- pg+AF8-type+AF8-typname+AF8-nsp+AF8-index 2200 +AHw- pg+AF8-stat+AF8-user+AF8-tables 2200 +AHw-
Re: [HACKERS] Error on PQputline()
Dann Corbit [EMAIL PROTECTED] writes: You're running libpq with the nonblocking mode selected? Actually no. It should be the default mode for a connection made by PQconnectdb(). That's what made the error so puzzling. I'm confused too. For starters, I cannot find that error message string about 'A non-blocking socket operation could not be completed immediately' anywhere. Got any idea what's producing that? Exactly which version of libpq are you using, anyway? Would it be faster to write a file to disk and read it again on the local host for the server or to send the calls via libpq client messages? Good question. I'd recommend the messaging approach since it eliminates lots of headaches about file access privileges and so forth. But on some platforms the overhead could be high. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error on PQputline()
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 4:38 PM
To: Dann Corbit
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Error on PQputline()
Dann Corbit [EMAIL PROTECTED] writes:
You're running libpq with the nonblocking mode selected?
Actually no. It should be the default mode for a connection made by
PQconnectdb(). That's what made the error so puzzling.
I'm confused too. For starters, I cannot find that error message
string about 'A non-blocking socket operation could not be completed
immediately' anywhere. Got any idea what's producing that? Exactly
which version of libpq are you using, anyway?
7.1.3. Sorry for running on fossil PostgreSQL.
/* -
*/
/* pqFlush: send any data waiting in the output buffer
*/
int
pqFlush(PGconn *conn)
{
char *ptr = conn-outBuffer;
int len = conn-outCount;
if (conn-sock 0)
{
printfPQExpBuffer(conn-errorMessage,
pqFlush() --
connection not open\n);
return EOF;
}
/*
* don't try to send zero data, allows us to use this function
without
* too much worry about overhead
*/
if (len == 0)
return (0);
/* while there's still data to send */
while (len 0)
{
/* Prevent being SIGPIPEd if backend has closed the
connection. */
#ifndef WIN32
pqsigfunc oldsighandler = pqsignal(SIGPIPE,
SIG_IGN);
#endif
int sent;
#ifdef USE_SSL
if (conn-ssl)
sent = SSL_write(conn-ssl, ptr, len);
else
#endif
sent = send(conn-sock, ptr, len, 0);
#ifndef WIN32
pqsignal(SIGPIPE, oldsighandler);
#endif
if (sent 0)
{
/*
* Anything except EAGAIN or EWOULDBLOCK is
trouble. If it's
* EPIPE or ECONNRESET, assume we've lost the
backend
* connection permanently.
*/
switch (errno)
{
#ifdef EAGAIN
case EAGAIN:
break;
#endif
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK !=
EAGAIN))
case EWOULDBLOCK:
break;
#endif
case EINTR:
continue;
case EPIPE:
#ifdef ECONNRESET
case ECONNRESET:
#endif
printfPQExpBuffer(conn-errorMessage,
pqFlush() -- backend closed the channel unexpectedly.\n
\tThis probably means the backend terminated abnormally
before or while
processing the request.\n);
/*
* We used to close the socket
here, but that's a bad
* idea since there might be
unread data waiting
* (typically, a NOTICE message
from the backend
* telling us it's committing
hara-kiri...). Leave
* the socket open until
pqReadData finds no more data
* can be read.
*/
return EOF;
/*
vvv
!!!
*/
default:
printfPQExpBuffer(conn-errorMessage,
pqFlush() -- couldn't send
data: errno=%d\n%s\n,
errno, strerror(errno));
/* We don't assume it's a fatal
error... */
return EOF;
/*
^^^
!!!
*/
}
}
else
{
ptr += sent;
len -= sent;
}
if (len 0)
{
/* We didn't send it all, wait till we can send
more */
/*
* if the socket is in non-blocking mode we may
need to abort
* here
*/
#ifdef USE_SSL
/* can't
Re: [HACKERS] Error on PQputline()
Dann Corbit [EMAIL PROTECTED] writes: I'm confused too. For starters, I cannot find that error message string about 'A non-blocking socket operation could not be completed immediately' anywhere. Got any idea what's producing that? Exactly which version of libpq are you using, anyway? 7.1.3. Sorry for running on fossil PostgreSQL. No such string in 7.1.3 either. printfPQExpBuffer(conn-errorMessage, pqFlush() -- couldn't send data: errno=%d\n%s\n, errno, strerror(errno)); /* We don't assume it's a fatal error... */ return EOF; /* ^^^ !!! */ Unless your strerror is really weird, that message is only going to have produced pqFlush() -- couldn't send data: errno=0\nNo error\n. The bit about a non-blocking socket could not have come from strerror AFAICS; it hasn't got enough context to know that. 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] More schema queries
Dave Page [EMAIL PROTECTED] writes: helpdesk=# select relnamespace, relname from pg_class where relname like 'pg_%'; relnamespace | relname --+- ... 2200 | pg_user 2200 | pg_rules 2200 | pg_views 2200 | pg_tables 2200 | pg_indexes 2200 | pg_stats 2200 | pg_stat_all_tables 2200 | pg_stat_sys_tables Bizarre. It's not that way here. Would you mind updating to CVS tip, rebuilding, and seeing if you can duplicate that? Also, make sure you're using the right initdb script ... ... One of the tests is to figure out if one of the base datasources in the query is a view - currently this is easy, but in 7.3 we could have a table a view with the same name in different schemas, hence by using the path we can figure out what object we're actually using. Actually, I'd venture that you do *not* want to do namespace search resolution for yourself; have you thought about how messy the SQL query would be? The new datatypes regclass, etc are intended to handle it for you. For example select 'foo'::regclass::oid;-- get OID of table foo in search path select 'foo.bar'::regclass::oid; -- get OID of table foo.bar select relkind from pg_class where oid = 'foo'::regclass; -- is foo a view? Incidently if you're interested at the moment, you may remember that in 7.2 beta there was a problem with slow startup under Cygwin which was down to a few seconds by release... The last 2 snapshots I've run take well over a minute for postmaster startup on a P3M 1.13GHz/512Mb under little load. There is virtually no disk activity during this time. Curious. I have not noticed much of any change in postmaster startup time on Unix. Can you run a profile or something to see where the time is going? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] More schema queries
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 23:24 To: Dave Page Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] More schema queries Dave Page [EMAIL PROTECTED] writes: helpdesk=# select relnamespace, relname from pg_class where relname like 'pg_%'; relnamespace | relname --+- ... 2200 | pg_user 2200 | pg_rules 2200 | pg_views 2200 | pg_tables 2200 | pg_indexes 2200 | pg_stats 2200 | pg_stat_all_tables 2200 | pg_stat_sys_tables Bizarre. It's not that way here. Would you mind updating to CVS tip, rebuilding, and seeing if you can duplicate that? Also, make sure you're using the right initdb script ... No problem, but it won't be until Monday now. I'll let you know what I find. ... One of the tests is to figure out if one of the base datasources in the query is a view - currently this is easy, but in 7.3 we could have a table a view with the same name in different schemas, hence by using the path we can figure out what object we're actually using. Actually, I'd venture that you do *not* want to do namespace search resolution for yourself; have you thought about how messy the SQL query would be? The new datatypes regclass, etc are intended to handle it for you. For example select 'foo'::regclass::oid; -- get OID of table foo in search path select 'foo.bar'::regclass::oid; -- get OID of table foo.bar select relkind from pg_class where oid = 'foo'::regclass; -- is foo a view? It doesn't work quite like that anyway. pgAdmin has a base library (pgSchema) which is a hierarchy of collections of objects which represent an entire server. It populates itself on demand, so the first time you access a collection of views (for example), pgSchema queries the database to build the collection of views in that database (now schema of course as there's an extra level in the hierarchy). Future accesses to that part of the hierarchy are *very* quick (not that initial ones are particularly slow). The only downside is that you may not notice new objects from other developers immediately (though the user can manually refresh any part of the hierarchy). Anyway, long story short, once I know the search path is testschema,public I'll just do: If svr.Databases(dbname).Namespaces(testschema).Views.Exists(viewname ) Then ... If svr.Databases(dbname).Namespaces(public).Views.Exists(viewname) Then ... Anyway, current_schemas() seems ideal, thanks. Incidently if you're interested at the moment, you may remember that in 7.2 beta there was a problem with slow startup under Cygwin which was down to a few seconds by release... The last 2 snapshots I've run take well over a minute for postmaster startup on a P3M 1.13GHz/512Mb under little load. There is virtually no disk activity during this time. Curious. I have not noticed much of any change in postmaster startup time on Unix. Can you run a profile or something to see where the time is going? Probably, but I'd need hand-holding as I don't have a clue how to do that. If you can send some instructions I'll give it a go though it'll probably be tomorrow now as I'm starting to fall asleep. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] More schema queries
Dave Page [EMAIL PROTECTED] writes:
It doesn't work quite like that anyway.
Oh, so essentially you want to simulate the namespace search on the
application side. I see.
Anyway, current_schemas() seems ideal, thanks.
It may not be exactly what you need, because it doesn't tell you about
implicitly searched schemas --- which always includes pg_catalog and
will include a temp namespace if you've activated one. For instance,
if current_schemas claims the search path is
regression= select current_schemas();
current_schemas
-
{tgl,public}
(1 row)
then the real path is effectively {pg_catalog,tgl,public}, or possibly
{pg_temp_NNN,pg_catalog,tgl,public}.
There was already some discussion about making a variant version of
current_schemas() that would tell you the Whole Truth, including the
implicitly searched schemas. Seems like we'd better do that; otherwise
we'll find people hardwiring knowledge of these implicit search rules
into their apps, which is probably a bad idea.
Anyone have a preference about what to call it? I could see making a
version of current_schemas() that takes a boolean parameter, or we
could choose another function name for the implicit-schemas-too version.
Curious. I have not noticed much of any change in postmaster
startup time on Unix. Can you run a profile or something to
see where the time is going?
Probably, but I'd need hand-holding as I don't have a clue how to do
that.
I'm not sure how to do it on Cygwin, either. On Unix you'd build a
profilable backend executable using
cd pgsql/src/backend
gmake clean
gmake PROFILE=-pg all
install same, run it, and then use gprof on the gmon.out file dumped
at postmaster termination. Dunno if it has to be done differently
on Cygwin.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Redhat 7.3 time manipulation bug
Hi,
Something is pretty broken in redhat 7.3 but I'm not sure what and I
don't have time to dig further
masm@test=# select cast('1967-04-18' as timestamptz);
timestamptz
1967-04-17 18:00:00-06
(1 row)
masm@test=# select cast(cast('1967-04-18' as date) as timestamp);
ERROR: Unable to convert date to tm
masm@test=#
Both cases works correctly in Redhat 7.2. Sorry if this is not the
correct forum however an alert is nice for people planning an Redhat
upgrade. I hope to see pretty soon an update since I don't want to
downgrade my server.
Regards,
Manuel.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Poster(s) needed
Not that I'm aware of anyone making ... On Fri, 17 May 2002, Michael Meskes wrote: Hi, since we will show PostgreSQL related stuff on Linuxtag in Germany next month, I'd like to get some PostgreSQL posters for the booth. But I have no idea where to find some. Do we have that kind of stuff? Or where could I get it? Preferable of course as file so I can print it myself. Thanks in advance Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
