Re: [GENERAL] Inserting Unicode into Postgre
Firestar wrote: Hi, I'm currently using PostgreSQL 7.0 on Solaris. My Java program receives strings in Big5 encoding and will store them in PostgreSQL (via JDBC). However, the inserted strings become multiple '?' (question marks) instead everytime i do a insert command. And when i retrieve them, via JDBC, the string becomes those question marks. Is the problem due to the Unicode encoding that Java String uses, or must i enable multibyte-support in my postgre installation? If i enable multibyte support, should i create my table with Unicode support, or Big5? Upgrade to just released 7.1, now postgres can do unicode conversion to you. (thanks to Mr. Tatsuo Ishii) I think you should enable both enable-multibyte enable-unicode-conversion switch. when building postgresql. regards Laser ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Non-english articles on the techdocs.postgresql.org website
On 12 Apr 2001, at 0:00, Justin Clift wrote: Any there any people who can understand both english and non-english languages, who wouldn't mind translating an article or two on the techdocs.postgresql.org website to a different language? I would gladly do some translation in italian. Who should I contact to volunteer? bye! /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Schema Import
hi Bob, Try psql -h ur_machine db_name -U user_name -f the_schema.sql HTH Anand On Mon, Apr 16, 2001 at 12:36:06PM -0700, Bob McCormick wrote: All, I've found a case tool that will generate a schema based on PostgreSQL syntax and am wondering if it is possible to run this generated SQL script to have the database schema created? If so, could someone knowledgeable give me a clue??? Thanks...Bobbo ;-) Bob McCormick Intuitive Software Technology ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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
[GENERAL] View and function
Hello.
I am trying to insert a tupla in a view with rules for inserting from a
function. This is my data:
---
create table direccin (
cod serial primary key,
calle text,
va int2references va(cod),
localidad int4references localidad(cod),
provincia int4references provincia(cod)
);
create index dir_calle_ndx on direccin (calle);
create index dir_via_ndx on direccin (va);
create index dir_localidad_ndx on direccin (localidad);
create index dir_provincia_ndx on direccin (provincia);
create view v_direccin as
select * from direccin
;
create rule v_direccin_ins as on insert to v_direccin do instead
(insert into direccin(calle,va,localidad,provincia) values (
NEW.calle,
NEW.va,
NEW.localidad,
NEW.provincia);
select currval('direccin_cod_seq'))
;
create function pilpot(calle) returns integer as '
declare
c alias for $1;
n integer;
begin
insert into v_direccin(calle) values (c);
get diagnostics n = result_oid;
return n;
end;
' language 'plpgsql';
--
I get an error when creating function of type:
ERROR: ProcedureCreate: arg type 'calle' is not defined
but when I try that insert from psql prompt, all works well.
Where is my mental bug? :)
Thanks.
David
---(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
[GENERAL] Select
Hi All, I'm in trouble to build a select... I have a table (for example) like this: Table: Employee empCod | empName | empDepth -+---+--- 1 | Anand |any 2 | Oliver |any 3 |Peter |any 4 | Bob |any 5 |David | any 6 | Paul |any Now I would like to select all employees which name begins with the letter "P". Select * from employee where "name-begin-with-letter-P" :-) How can I do it ??? Thanks in advance, Marcelo Pereira Unicamp - Brazil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Select
SELECT * FROM employee WHERE empname LIKE 'P%' the above would work only if you want to search for people w/ names starting w/ P Mike - Original Message - From: "Marcelo Pereira" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 5:23 AM Subject: Select Hi All, I'm in trouble to build a select... I have a table (for example) like this: Table: Employee empCod | empName | empDepth -+---+--- 1 | Anand |any 2 | Oliver |any 3 |Peter |any 4 | Bob |any 5 |David | any 6 | Paul |any Now I would like to select all employees which name begins with the letter "P". Select * from employee where "name-begin-with-letter-P" :-) How can I do it ??? Thanks in advance, Marcelo Pereira Unicamp - Brazil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Select
On Tue, Apr 17, 2001 at 09:23:02AM -0300, Marcelo Pereira wrote: Hi All, Now I would like to select all employees which name begins with the letter "P". Select * from employee where "name-begin-with-letter-P" :-) select * from employee where email ~ '^P'; or if case does not matter select * from employee where upper(email) ~ '^P'; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] failed sanity check, table answers was not found
I receive this error when trying to pg_dump -s failed sanity check, table answers was not found the failed table name varies. All the problematic tables seem to work. vacuumdb (-z) also works without any comment. This is a production database on 7.0.3 I'd like to migrate to 7.1, but I am affraid that the dumps are corrupt. regards -- Marek Ptlicki [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] local security
"David M. Kaplan" [EMAIL PROTECTED] writes: Even better would be that postgres uses the standard unix security and that on connecting it asks for the appropriate unix password unless you are a superuser in which case it just connects. This would tie Postgres usernames to usernames of the surrounding system, which is something that we've explicitly avoided doing. There are many scenarios where you don't want to have to grant every database user a shell account on the database host machine. Another serious problem is how do you know what Unix userid is at the other end of the connection? If it's a localhost TCP connection then maybe you can trust IDENT protocol to find out, but AFAIK there's no portable equivalent for Unix-socket connections. Finally, I don't much care for the assumption that superuserness on the Unix side should automatically translate to superuserness in Postgres land. We've worked hard to ensure that routine Postgres administration does not require system root privileges, and I don't think that the access-control scheme should encourage people to break that compartmentalization. If you're doing Postgres-related work as root, you're too likely to slip up and give something root privs that shouldn't have 'em. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] index not used with subselect in where clause ?
Christian Fritze [EMAIL PROTECTED] writes: explain select * from allmain where exists (select distinct dokids_as_int from allslwfull where dokids_as_int = idn and wort_nouml_lower like 'gen%') Try dropping the "distinct" on the inner select. As a moment's thought will reveal, it's not buying you anything; and it's costing you sort and unique passes over the subplan result. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] parse error at or near $1
Michael McDonnell [EMAIL PROTECTED] writes: And when I run "SELECT contactable_name(1);" I get the error message "parse error at or near $1". $1 is a parameter placeholder; probably your problem is unexpected substitution (or lack of substitution) of a plplgsql variable or parameter into a query that's being sent to the underlying SQL engine. In 7.1 the easiest way to debug this type of problem is to do SET debug_print_query TO 1; then execute the problem function call, and then look in the postmaster log to see what queries got generated by the function. In older PG versions there is no debug_print_query variable, so you have to resort to setting environment variable PGOPTIONS to "-d2" before starting psql to get query logging. plpgsql needs more support for debugging --- some kind of trace mechanism that doesn't require access to the postmaster log would be awfully nice ... 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: [GENERAL] local security
"David M. Kaplan" [EMAIL PROTECTED] writes: ... If you do that, you can no longer do things like restores from backups without editing the configuration file because postgresql wont let you change users. This is of course not a great problem, but it is a bit annoying. What I really want is that normal users can only logon as themselves, but superusers can logon as anyone. BTW, this has already been discussed (look in the pghackers archives), and I believe the consensus was that the most useful approach is to make Postgres distinguish between "real" userid (what you logged in as) and "effective" userid (what's used for object creation and permission checks). For unprivileged users these would be equal and unchanging throughout a session, but if your real ID is a Postgres superuser then you would be allowed to SET the effective-userid variable to different values. Then, for example, a pg_dump script would use "SET effective_userid" instead of "\connect" commands to change user identity, and a whole lot of the problems with executing pg_dump and pg_dumpall scripts under secure authentication models would go away. We need a notion of current effective userid anyway to allow rules and functions to execute as though they are "setuid" programs. I think Peter E. has laid some of the groundwork for this mechanism in 7.1, but work is still needed. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] pg_dump error
I'm trying to back up a table by dumping to a text file, but in the proccess of dumping I get the following error dumpSequence(pilgram_en_id_seq): different sequence name returned by SELECT: pilgram_cross_id_seq I'm not sure what this means, and I have no idea how to corect it. Mike
Re: [GENERAL] index not used with subselect in where clause ?
Adding a LIMIT 1 in the subplan may also help -- as you only need a single match to make it true so additional finds are useless -- it'll stop sooner or will be more likely to use an index than a full table scan. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Christian Fritze" [EMAIL PROTECTED] Cc: "Stephan Szabo" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 10:33 AM Subject: Re: [GENERAL] index not used with subselect in where clause ? Christian Fritze [EMAIL PROTECTED] writes: explain select * from allmain where exists (select distinct dokids_as_int from allslwfull where dokids_as_int = idn and wort_nouml_lower like 'gen%') Try dropping the "distinct" on the inner select. As a moment's thought will reveal, it's not buying you anything; and it's costing you sort and unique passes over the subplan result. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] failed sanity check, table answers was not found
=?iso-8859-2?Q?Marek_P=EAtlicki?= [EMAIL PROTECTED] writes: I receive this error when trying to pg_dump -s failed sanity check, table answers was not found I suspect you have a table whose owner no longer exists in pg_shadow. Check the pg_class.relowner value for that table, and create a user with the matching usesysid. 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: [GENERAL] index not used with subselect in where clause ?
"Rod Taylor" [EMAIL PROTECTED] writes: Adding a LIMIT 1 in the subplan may also help -- as you only need a single match to make it true so additional finds are useless -- it'll stop sooner or will be more likely to use an index than a full table scan. I believe this is not necessary; the WHERE EXISTS(...) context is sufficient to cue the planner that only one tuple will be retrieved, and it will alter the subselect's plan accordingly. 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: [GENERAL] failed sanity check, table answers was not found
I receive this error when trying to pg_dump -s failed sanity check, table answers was not found the failed table name varies. All the problematic tables seem to work. vacuumdb (-z) also works without any comment. I had some similar problems with pg_dump on 7.0.3 recently. Tom Lane was able to give me some very specific things to look at. The error I was getting was: -- dumping out user-defined functions failed sanity check, type with oid 101993741 was not found And Tom told me, "Looks like you have a function that refers to a since-deleted type. You'll need to find and drop the function (which may mean manually deleting its pg_proc row, since there's no way to name the function to DROP FUNCTION if one of its parameters is a now-unknown type). Another possibility is that the type still exists but you deleted its owning user from pg_shadow; that will confuse pg_dump too. In that case you can just create a new user with the same usesysid, or you can update the type's typowner field in pg_type to refer to some existing user. Try "select * from pg_type where oid = 101993741" to figure out which situation applies ..." Don't know if that will help at all. Seems this is usally something where something references soemthign that doesnt' exist any more, such as a function that returns a datatyp that no longer exists. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] bpchar type
On Mon, Apr 16, 2001 at 04:39:50PM +0200, Peter Eisentraut wrote: Patrick Dunford writes: Zeos Database Explorer shows the type of these fields to be "bpchar", is this a Postgres data type? bpchar is the internal name for char(). short for "blank-padded character". as opposed to varchar, which is either "null-padded char" or "variable-length char" but we don't have to worry about which. then there's text... hmm! -- don't visit this page. it's bad for you. take my expert word for it. http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problem with function invocation
On Mon, Apr 16, 2001 at 03:43:23PM +0200, DaVinci wrote: Hello. When I create next function: create function pilpot() returns integer as ' declare foo integer; begin foo = insert into aviso(user) values(1); return foo; end; ' language 'plpgsql'; don't assignments have to be pascal-style, with a colon? var := expression + some / value ; and then, doesn't the rhs (right-hand side) have to return a value? i don't think insert does return anything pertinent (except in psql where you can see the oid and the 'record count' [or is it more like an 'activity count'?]). == do i get a prize for the most-consecutive punctuation within an english-text paragraph? :) -- don't visit this page. it's bad for you. take my expert word for it. http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] index not used with subselect in where clause ?
Adding a LIMIT 1 in the subplan may also help -- as you only need a
single match to make it true so additional finds are useless -- it'll
stop sooner or will be more likely to use an index than a full table
scan.
--
Rod Taylor
I'm not sure if I understand you correctly here: the subplan uses an
index scan already. It's the seq. scan in the outer query that makes
me whine.
There are always four sides to every story: your side, their side, the
truth, and what really happened.
Off Topic: I like that .sig, thoughts of "The man who shot Liberty Valance"
come to mind... :-)
From: "Tom Lane" [EMAIL PROTECTED]
[...]
Christian Fritze [EMAIL PROTECTED] writes:
explain select * from allmain where exists (select distinct
dokids_as_int from allslwfull where dokids_as_int = idn and
wort_nouml_lower like 'gen%')
Try dropping the "distinct" on the inner select. As a moment's
Yep, that increases performance...
...by about 0.35 % according to EXPLAIN :-{
Well, what I'm doing right now is the following:
I perform the inner query (which is reasonably fast) and pump the result
through the JDBC driver into my application. There I build the outer query
with an explicit list of integers for the WHERE clause and hand that query
back to the data base.
But that doesn't seem very smart either: in cases where the inner query
returns only a few results it's not really necessary. In cases where it
returns a few thousands, I need to split the outer query in order not to
run into a 'query too long' error (which comes from the jdbc driver rather
than from pgsql if I'm right? Maybe I should try to tweak that driver? Hmmm...)
That splitting however eats away much (if not all) of the intended
performance gain.
greetings...
Christian
--
"The sky above the port was the color of television,
tuned to a dead channel."
-- W.G. --
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: View and function
DaVinci wrote: create function pilpot(calle) returns integer as ' this is your problem. try this: create function pilpot(text) returns integer as ' the syntax for creating postgres functions is to declare the types, not the identifiers of any parameters. -tfo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] drastic reduction in speed of inserts as the table grows
Hi, I was comparing speed of inserts in C vs JDBC and found that as the table size increases the speed differential decreases till there is no difference (no problem). However inserts kept getting slower and slower as the table size increased and the performance became quite poor. Here is the data including the table descriptions - CREATE TABLE some_table( idx serial, a_idx int4 NOT NULL, b_idx int4 NOT NULL, c_address varchar(20) NOT NULL, d_address varchar(20) NOT NULL, PRIMARY KEY(idx), CONSTRAINT a_fkey1 FOREIGN KEY(a_idx) REFERENCES a_ref(idx), CONSTRAINT b_fkey2 FOREIGN KEY(b_idx) REFERENCES b_ref(idx) ); CREATE INDEX some_index on some_table (a_idx, b_idx, c_address, d_address); Here is the performance statistics on the same table. Note the fall in performance as the test proceeds. # of inserts C (in sec) JDBC (in sec) (as 1 transaction) 500 1 1.7 1000 3 3.4 2000 6 7.5 another 6000 inserts ... then 1 70.8 1283 (ran vacuum at this point to see if it helped) 1355 1000 3637 100 3.8 3.8 I ran these tests on a Linux machine (299 MHz). I used postgres v7.0.3 but then I even tried grouping a large number of inserts in one transaction to reduce the number of hard-disk writes (it did not make a difference as shown in the above data) I am concerned about the drastic fall in performance with increase of table size. Is this expected behavior ? Would this be related to indexes existing on the table? I would expect indexes to make inserts slower but I do not see how it explains such a great fall in performance with increasing table-size. Is there a way to avoid this drop in performance ? Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(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: [GENERAL] drastic reduction in speed of inserts as the table grows
--- Tom Lane [EMAIL PROTECTED] wrote: Rini Dutta [EMAIL PROTECTED] writes: Here is the performance statistics on the same table. Note the fall in performance as the test proceeds. Try 7.1. I think you are running into the lots-of-pending-triggers problem that was found and fixed awhile back. regards, tom lane I'll try it out. Just for my understanding, is the 'lots-of-pending-triggers' problem related to indexes, or to foreign keys ? Rini __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(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
