Re: [GENERAL] PostgreSQL's XML support comparison against other RDBMSes
Ted Byers schrieb: May I ask a question about this? I will be working on an older database in which the original developer stored XML documents as a single variable length text field. To process it, it has to be retrieved in full and parsed. But the structure of it is simple in that it has an element for each field in the DB that replaced the earily version. But people are still using the earlier one because they still need access to the old data and no tool has yet been written by my predecessors to move the old data over to the new DB. Does the XML support you're all talking about make it less tedious to develop tools to parse these old XML files and put their data into the right field of the right table? I can develop the tool our users need using the resources I have at hand, but the proces of parsing these XML files is certain to be very tedious; something I am not looking forward to. There is a reason I don't do much with XML even though I know how. Hm. well if my mind does not fool me you wrote the exact posting sometime ago ;) and I also remember answers in the region of parsing it with a stored function and for your legacy application put a view. Otoh, if you want it smarter and nicer you could write a whole datatype with default input/output is your plain XML text. However - since XML is very generic you cannot expect some standards implementation to cover all _your_ needs. I would just use a dom or sax parser in my favourite scripting language and distribute the items over some tables as dom nodes and out the whole logic into a custom datatype. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgpool ABORT + no transaction warning
The problem is not reproduced here. Do you have any idea how to reproduce it? -- Tatsuo Ishii SRA OSS, Inc. Japan On Apr 1, 2006, at 8:01 AM, Tatsuo Ishii wrote: A while back, I posted about seeing a number of warnings from pgpool: http://archives.postgresql.org/pgsql-admin/2005-03/msg00305.php A typical pair of statements in my postgres logs looks like this: WARNING: there is no transaction in progress STATEMENT: ABORT Tatsuo Ishii declared that these were harmless, and I took (and still take) his word for it. At some point in the past with my old configuration (postgres 8.1.3 / pgpool 2.5.1) I did something that prevented the warnings from showing up. On a system running postgres 8.1.3, I recently upgraded pgpool from 2.5.1 to 3.0.1. When I upgraded pgpool, I began seeing the warnings again after a long window of not having seen them. My configuration files for pgpool and postgres didn't change during the upgrade, so I'm wondering what I might've been doing to avoid polluting my logs with them previously and why they are showing up again. Could you tell me how to reproduce the problem? Here's what happened: I performed an on-line backup and recovery to move postgres from one server to another. On the new server, I installed pgpool 3.0.1 and copied over my old configuration files. I have two instances of pgpool running on the server and am only using pgpool for connection pooling, not load balancing or replication. The only three settings that are different between the two configurations are port, logdir, and num_init_children. Below is one pgpool.conf (with a few paths changed back to the defaults just for obfuscation), which doesn't include child_max_connections, insert_lock, or ignore_leading_white_space because I just used my 2.5.1 configuration file. I assume it would just use defaults for these values if they weren't specified in the file? listen_addresses = '*' port = socket_dir = '/tmp' backend_host_name = '' backend_port = 5432 backend_socket_dir = '/tmp' secondary_backend_host_name = '' secondary_backend_port = 0 num_init_children = 64 max_pool = 4 child_life_time = 300 connection_life_time = 10 logdir = '/tmp/pgpool1' replication_mode = false replication_strict = true replication_timeout = 5000 load_balance_mode = false weight_master = 0.5 weight_secondary = 0.5 replication_stop_on_mismatch = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pl/pgsql uniq varchar[] sort?
Hello list. I'm trying to get a unique, sorted varchar array in pl/pgsql. Essentially a "group by" and "order by"'d varchar[]. Anyone got any ideas or point me in the right direction? Thanks. New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [GENERAL] Grouping aggregate functions
Thanks! That did the trick. SELECT avg(minprice) FROM (SELECT min(price) as minprice FROM weekly_supply_prices GROUP BY month ); This came up with an error too, but it pointed me in the right direction and was easy to fix. I needed to use an alias for the entire subquery too, so what finally worked is this: SELECT avg(minprice) FROM (SELECT min(price) AS minprice FROM weekly_supply_prices GROUP BY month) AS minprice_table; Thanks again, Rich On Apr 2, 2006, at 4:51 AM, Martijn van Oosterhout wrote: On Sun, Apr 02, 2006 at 04:03:03AM -0700, Richard Connamacher wrote: I've got a question, if anyone can help me out. I know how to use an aggregate function to, say, find the lowest price ever listed for a product. I also know how to combine that with a SELECT ... GROUP BY statement to find, say, the lowest price reported for each month. Now, what if I want to find the *average* of all the lowest prices for each month? Plopping that SELECT statement inside parentheses and inside an avg( ) function produces an error. Use a subquery. ie.e not: SELECT avg( ( SELECT min(price) FROM weekly_supply_prices GROUP BY month ) ) But SELECT avg(minprice) FROM (SELECT min(price) as minprice FROM weekly_supply_prices GROUP BY month ); Anyone have any idea how to do this? Or do I have to compute the average in another program? Use SQL to calculate both :) One way to think about it is by think of the subquery producing a temporary table which you then use in another query. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/ kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] MediaWiki and Postgresql?
Hi [EMAIL PROTECTED] wrote: Has anyone put MediaWiki up using the current version of Postgresql? I have, although our Mediawiki installation is currently not openly accessible. Can't say that it went like a breeze, but the obstacles where more or less minor and writing a little custom auth-plugin wasn't really too hard either. If your question was just along the lines of does it run, I can confirm that, yes, it does. I cannot say though how well it would stand up to high loads and if every query and index has been fully tweaked. Kind regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] how to create script of database in postgres..sql(winxp)
hi i am trying to make script file for my database by using pg_dump in windows as u say i open psqlto postgres then a prompt postgres# open then i write \i pg_dump it shows error.what should i do...plz hepl
[GENERAL] Encoding problem in psql (Windows)
Hej, though there are a few posts in the mailing lists archive with similar or the same error messages, I couldn't find a solution for my problem with psql there. So here's my encoding issue: I'm using a fresh install of 8.1.3 on a German Windows 2000. I have set the codepage (1252) and font (Lucida Console) for cmd.exe as recommended by the *Notes for Windows Users* in the man page for psql. Now after creating a database with UTF8-encoding, switching to it via \c and trying to list all users with \du I always get the following error message: ERROR: invalid UTF-8 byte sequence detected near byte 0xe4 The error persists after switching the client character set encoding to SQL_ASCII - though I have no idea if that could be of help anyway. Now the strange thing is that there is no such error when querying all users after switching to the template1 database, which is in my case encoded in SQL_ASCII (though I have read that is no encoding at all, is it?). Another strange thing: When accessing the very same Windows server from my Linux box and trying to list all users from its version of psql there is no such error at all. IMHO this indicates that this has to be an issue of Windows or the Windows version of psql... Now my question is whether one of you has run into similar problems and might be able to help me to solve it? Thank you very much in advance... Regards, Markus Reinhold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to create script of database in postgres..sql(winxp)
am 03.04.2006, um 17:20:56 +0530 mailte deepak pal folgendes: hi i am trying to make script file for my database by using pg_dump in If you want to write your own scriptfiles, you should better use a editor for this. windows as u say i open psql to postgres then a prompt postgres# open then i write \i pg_dump it shows error.what should i do...plz hepl No. You should type \i your_script, and replace your_script with the filename (and maybe the path to this file). HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: [GENERAL] how to create script of database in postgres..sql(winxp)
-- Forwarded message --From: deepak pal [EMAIL PROTECTED]Date: Apr 3, 2006 2:05 PM Subject: Re: [GENERAL] how to create script of database in postgres..sql(winxp)To: Tomi NA [EMAIL PROTECTED]it do not recognize that \i and pg_dump i use \i pg_dump mydbnamenewdb command From the examples in man pg_dump: To dump a database: $ pg_dump mydb db.out To reload this database: $ psql -d database -f db.out To dump a database called mydb to a tar file: $ pg_dump -Ft mydb db.tar To reload this dump into an existing database called newdb: $ pg_restore -d newdb db.tarHave you tried to dump the database this way? Does it work?t.n.a.
Re: [GENERAL] Creating serial ID on Windows.
On Monday 27 March 2006 19:32, Chris wrote:
Ardian Xharra (Boxxo) wrote:
I'm having some troubles restoring a database on Windows and I found
this difference between PostgreSQL running on Linux and Windows.
When I create a table like this:
CREATE TABLE fee_payment1(id_fee_payment1 serial NOT NULL) WITH OIDS;
On Linux platform it will be:
CREATE TABLE fee_payment1 ( id_fee_payment1 serial NOT NULL) WITH OIDS;
And on Windows platform is:
CREATE TABLE fee_payment1
( id_fee_payment1 int4 NOT NULL DEFAULT
nextval('fee_payment1_id_fee_payment1_seq'::regclass) ) WITH OIDS;
So, I would like to know if this would have an impact throught restoring
database.
Postgres doesn't care (in this regard anyway) what platform you are
running.
There will be no difference between creating the table on windows to linux.
While what Chris has said is true (PostgreSQL doesn't care about your
underlying OS in most scenarios) if you use different syntax on each platform
as you have laid out here, you will introduce a difference between your
platforms. In the above scenario you will likely have differences in
dependency information when declaring a default nextval call on a sequence
compared to declaring a true sequence type.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: 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] how to create script of database in postgres..sql(winxp)
On Monday 03 April 2006 07:50, deepak pal wrote:
hi
i am trying to make script file for my database by using pg_dump in
windows as u say i open psql to postgres then a prompt postgres# open then
i write \i pg_dump it shows error.what should i do...plz hepl
you need to use the pg_dump program, which is a seperate program from psql.
IIRC it is in your start menu, but if not you will need to access it from a
command line window.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Performance Killer 'IN' ?
Marko Kreen wrote:
On 3/31/06, Kai Hessing [EMAIL PROTECTED] wrote:
The second one (UPDATE xyz WHERE id IN (xyz1, xyz2, ) AND
status-1;) returns:
--
Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual
time=369563.565..369563.565 rows=0 loops=1)
Just a shot in the dark: does the plan stay the same,
when you remove the ' AND status -1' ?
No difference: I skipped the 'AND status -1' and have the following
results...
Using 2000x 'UPDATE phon SET status=-6 WHERE ' returns each time:
Index Scan using phon_phon_idx on phon (cost=0.00..5179.80 rows=1587
width=148) (actual time=31.452..31.470 rows=1 loops=1)
Index Cond: ((phon)::text = '[EMAIL PROTECTED]'::text)
Total runtime:3.414 ms
(Total runtime for all 2000 Updates: 23335.393 ms
Using the 'UPDATE xyz WHERE id IN ('xyz1', 'xyz2', other 2000
values.)' returns:
Seq Scan on phon (cost=0.00..1564960.67 rows=317227 width=148) (actual
time=68.315..365621.761 rows=2522 loops=1)
Filter: (((phon)::text = '.
Total runtime: 393182.745 ms
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance Killer 'IN' ?
Tom Lane wrote:
Kai Hessing [EMAIL PROTECTED] writes:
Index Scan using phon_phon_idx on phon (cost=0.00..5193.83 rows=530
width=148) (actual time=0.146..0.146 rows=0 loops=1)
...
Seq Scan on phon (cost=0.00..1573304.58 rows=105931 width=148) (actual
time=369563.565..369563.565 rows=0 loops=1)
You need to look into the discrepancy between estimated and actual row
counts. (I suppose the reason you're showing 0 rows here is that you
already did these UPDATEs and so none of the rows in question pass the
status filter --- but how many rows are there matching the phon index
conditions?) Perhaps a larger statistics target for the phon column
would be a good idea.
Yes... The 0 rows are there because I did the command before. Now I
resetted the test database to a previous state and dopped the 'AND
status-1' in the SQL-syntax:
Using the 'UPDATE xyz WHERE id IN ('xyz1', 'xyz2', other 2000
values.)' returns:
Seq Scan on phon (cost=0.00..1564960.67 rows=317227 width=148) (actual
time=68.315..365621.761 rows=2522 loops=1)
Filter: (((phon)::text = '.
Total runtime: 393182.745 ms
(please see also the other post [EMAIL PROTECTED] )
What do you mean with larger statistics target?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] what is this error
Dear List, I have created a database called dhis13 and created a login role with user name venu and pwd gis now i have created an sql file using shp2pgsql command it worked successfully when i m importing the same to the dhis13 database i was giving with the following error all the database and loginroles is created at pgadminIII (not at command prompt) When i try to import i was getting the following errorC:\Program Files\PostgreSQL\8.1\binpsql -U venu -d dhis13 -f e:\venu\postgischittoorshp.sqlPassword for user venu:BEGINpsql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE will create implicit sequence "chittoor_ver0_gid_seq" for serial column "chittoor_ver0.gid"psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE /
PRIMARY KEY willcreate implicit index "chittoor_ver0_pkey" for table "chittoor_ver0"CREATE TABLEpsql:e:/venu/postgischittoorshp.sql:3: ERROR: function addgeometrycolumn("unknown", "unknown", "unknown", "unknown", "unknown", integer) does not existHINT: No function matches the given name and argument types. You may need to add explicit type casts.psql:e:/venu/postgischittoorshp.sql:4: ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:5: ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:6: ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:7: ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:8:
ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:9: ERROR: current transaction is aborted, commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:10: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:11: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:12: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:13: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:14: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:15: ERROR:
current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:16: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:17: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:18: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:19: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:20: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:21: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:22: ERROR: current
transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:23: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:24: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:25: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:26: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:27: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:28: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:29: ERROR: current transaction is
aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:30: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:31: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:32: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:33: ERROR: current transaction is aborted,commands ignored until end of transaction blockpsql:e:/venu/postgischittoorshp.sql:34: ERROR: current transaction is aborted,commands ignored until end of transaction
Re: [GENERAL] what is this error
am 03.04.2006, um 13:40:45 + mailte venu gopal folgendes: Dear List, I have created a database called dhis13 and created a login role with user name venu and pwd gis now i have created an sql file using shp2pgsql command it worked successfully when i m importing the same to the dhis13 database i was giving with the following error all the database and loginroles is created at pgadminIII (not at command prompt) When i try to import i was getting the following error C:\Program Files\PostgreSQL\8.1\binpsql -U venu -d dhis13 -f e:\venu\postgischi ttoorshp.sql Password for user venu: BEGIN psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE will create implici t sequence chittoor_ver0_gid_seq for serial column chittoor_ver0.gid psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index chittoor_ver0_pkey for table chittoor_ver0 CREATE TABLE All okay. psql:e:/venu/postgischittoorshp.sql:3: ERROR: function addgeometrycolumn(unkno wn, unknown, unknown, unknown, unknown, integer) does not exist HINT: No function matches the given name and argument types. You may need to ad d explicit type casts. This is the error: function addgeometrycolumn() does not exist. psql:e:/venu/postgischittoorshp.sql:4: ERROR: current transaction is aborted, c ommands ignored until end of transaction block All errors now are only aftereffect. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] what is this error
On 4/3/06, venu gopal [EMAIL PROTECTED] wrote: Dear List, I have created a database called dhis13 and created a login role with user name venu and pwd gis now i have created an sql file using shp2pgsql command it worked successfully when i m importing the same to the dhis13 database i was giving with the following error all the database and loginroles is created at pgadminIII (not at command prompt) When i try to import i was getting the following error C:\Program Files\PostgreSQL\8.1\binpsql -U venu -d dhis13 -f e:\venu\postgischi ttoorshp.sql Password for user venu: BEGIN psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE will create implici t sequence chittoor_ver0_gid_seq for serial column chittoor_ver0.gid psql:e:/venu/postgischittoorshp.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index chittoor_ver0_pkey for table chittoor_ver0 CREATE TABLE psql:e:/venu/postgischittoorshp.sql:3: ERROR: function addgeometrycolumn(unkno wn, unknown, unknown, unknown, unknown, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. You're missing a function called addgeometrycolumn. It's not a native postgres function (as far as I'm aware) - you'll have to find where it comes from and import it. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance Killer 'IN' ?
On 4/3/06, Kai Hessing [EMAIL PROTECTED] wrote: Marko Kreen wrote: Just a shot in the dark: does the plan stay the same, when you remove the ' AND status -1' ? No difference: I skipped the 'AND status -1' and have the following results... Ok. Thanks. I once had similar query and it seemed that postgres got confused if there was big IN and alse something else in WHERE clause. Dropping the rest got postgres to use index for IN. But I did not have time to research it then and with your result seems it's not a problem in recent versions. -- marko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance Killer 'IN' ?
Kai Hessing [EMAIL PROTECTED] writes:
Yes... The 0 rows are there because I did the command before. Now I
resetted the test database to a previous state and dopped the 'AND
status-1' in the SQL-syntax:
Using the 'UPDATE xyz WHERE id IN ('xyz1', 'xyz2', other 2000
values.)' returns:
Seq Scan on phon (cost=0.00..1564960.67 rows=317227 width=148) (actual
time=68.315..365621.761 rows=2522 loops=1)
Filter: (((phon)::text = '.
Well, here's our problem it would seem: the planner is estimating the IN
clause to match 317227 rows, rather than the actual 2522. That's
naturally going to bias it against choosing an indexscan. You need to
get that estimate closer before there's going to be much chance of
choosing the right plan.
What do you mean with larger statistics target?
See ALTER TABLE SET STATISTICS, or just change default_statistics_target
and re-ANALYZE.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] database design questions
Hello,
I'm designing a database and I'm having some problems, so I ask you a
suggestion.
1) The database I'm going to develop is a big list with a catalog of
items and I want to store subsets of this list representing the
available items in several places.
My idea is to create the big table with all the elements and then to
create another table, where each row holds a pair (id_item, id_place)
and thanks to this create several views, joining the two tables
and selecting the rows with a give id_place.
Do you think it's too heavy? Is there a simpler way to do it?
2) do you think it's possible in a plpgsql procedure select the name of
a table into a variable and use that variable in the query?
I mean, can I do something like
SELECT INTO table_name get_table_name();
SELECT * FROM table_name;
?
3) faq 4.11.1 says
CREATE TABLE person (
id SERIAL,
name TEXT
);
is automatically translated into this:
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
how can I do it with a INT8 instead of a INT4?
Thank you
--
Non c'è più forza nella normalità, c'è solo monotonia.
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] database design questions
On 4/3/06, Ottavio Campana [EMAIL PROTECTED] wrote:
3) faq 4.11.1 saysCREATE TABLE person (id SERIAL,name TEXT);is automatically translated into this:CREATE SEQUENCE person_id_seq;
CREATE TABLE person (id INT4 NOT NULL DEFAULT nextval('person_id_seq'),name TEXT);how can I do it with a INT8 instead of a INT4?Thank you
Is there a reason not to write explicitly?CREATE SEQUENCE person_id_seq;CREATE TABLE person (id INT8 NOT NULL DEFAULT nextval('person_id_seq'),name TEXT);Tomislav
Re: [GENERAL] How to delete all operators
You can make a function to do this. CREATE FUNCTION drop_operators(text) RETURNS BOOL AS $$ DECLARE op record; BEGIN FOR op IN SELECT opname FROM pg_operator as o left join pg_namespace as n on (o.oprnamespace = n.oid) WHERE nspname = $1 LOOP EXECUTE 'DROP OPERATOR '||quote_literal(op.opname)|| ';'; END LOOP; RETURN TRUE; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; And so: SELECT drop_operators( 'public');On 3/31/06, Martin Pohl [EMAIL PROTECTED] wrote: Hi,I have a database with operators and functions in plpgsql.To update the data to the latest version I would like to drop all operators.There might be some, that I don't know. I don't have access to the database, but have to write a script, that will update the data.Is there any way to drop all operators (given they are all in the schemapublic) in a script?Something like (pseudocode):Drop all operators in schema public Thanks in advance for answers--E-Mails und Internet immer und überall!11 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb ---(end of broadcast)---TIP 1: 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-- William Leite Araújo
Re: [GENERAL] pl/pgsql uniq varchar[] sort?
On 4/3/06, Matthew Peter [EMAIL PROTECTED] wrote: Hello list. I'm trying to get a unique, sorted varchar array in pl/pgsql. Essentially a group by and order by'd varchar[]. Anyone got any ideas or point me in the right direction? Thanks. If your data is not an array type coming off the table but you want it to end up that way, check out array_accum at http://www.postgresql.org/docs/8.1/static/xaggr.html. All you have to do is order the data going into the aggregate: select array_accum(d) from ( select d from t order by... ) if your data is starting off as an array type, you have a few options. you might get the most milage out of a pl/perl procedure to sort the type. If the arrays are small and you absolutely had to do it in plpgsql you could copy the values into a temp table, sort it via query, and resinsert into an array using the above technique. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] database design questions
--- Tomi NA [EMAIL PROTECTED] wrote:
On 4/3/06, Ottavio Campana [EMAIL PROTECTED] wrote:
3) faq 4.11.1 says
CREATE TABLE person (
id SERIAL,
name TEXT
);
is automatically translated into this:
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
how can I do it with a INT8 instead of a INT4?
Thank you
Is there a reason not to write explicitly?
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT8 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
you could also do:
CREATE TABLE person (
id BIGSERIAL,
name TEXT
);
Regards,
Richard
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] database design questions
on 4/3/06 7:38 AM, [EMAIL PROTECTED] purportedly said:
1) The database I'm going to develop is a big list with a catalog of
items and I want to store subsets of this list representing the
available items in several places.
My idea is to create the big table with all the elements and then to
create another table, where each row holds a pair (id_item, id_place)
and thanks to this create several views, joining the two tables
and selecting the rows with a give id_place.
Do you think it's too heavy? Is there a simpler way to do it?
On the surface, perhaps. Depending on your implementation details, you may
be adding unnecessary overhead. No one can really say since we don't know
what you are trying to accomplish.
2) do you think it's possible in a plpgsql procedure select the name of
a table into a variable and use that variable in the query?
I mean, can I do something like
SELECT INTO table_name get_table_name();
SELECT * FROM table_name;
Yes, kind of. I.e., you can probably do what you want but not with the
syntax you are showing. See SELECT INTO and EXECUTE in chapter 36 of the
online docs.
3) faq 4.11.1 says
CREATE TABLE person (
id SERIAL,
name TEXT
);
is automatically translated into this:
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
how can I do it with a INT8 instead of a INT4?
Use BIGSERIAL instead.
Best,
Keary Suska
Esoteritech, Inc.
Demystifying technology for your home or business
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] RAISE function misuse ?
Hi, I obtain the following output: idns_target=# select test(); INFO: hello ! CONTEXT: SQL statement SELECT hello() PL/pgSQL function test line 2 at perform Using the two following functions: CREATE OR REPLACE FUNCTION hello() RETURNS void AS $$ BEGIN RAISE INFO 'hello !'; RETURN; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ BEGIN PERFORM hello(); RETURN; END; $$ LANGUAGE plpgsql; I would like to know: 1. Is there anything wrong using RAISE this way. 2. Is there a way to get rid of the output: CONTEXT: SQL statement SELECT hello() PL/pgSQL function test line 2 at perform I use PostgreSQL/psql 8.1.0 Thanks, Patrick ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] database design questions
Ottavio Campana wrote: CREATE TABLE person ( id SERIAL, name TEXT ); how can I do it with a INT8 instead of a INT4? Do you really expect that sequence to reach over 2 billion? Otherwise I'd stick with the SERIAL, nothing wrong with that unless you're selling electrons seperately or something like that (hmm... how much are they? I sure could use a few extra). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: 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] database design questions
create table person (id serial8,name text);AlexOn 4/3/06, Alban Hertroys [EMAIL PROTECTED] wrote:Ottavio Campana wrote: CREATE TABLE person ( id SERIAL, name TEXT ); how can I do it with a INT8 instead of a INT4?Do you really expect that sequence to reach over 2 billion? OtherwiseI'd stick with the SERIAL, nothing wrong with that unless you're selling electrons seperately or something like that (hmm... how much are they? Isure could use a few extra).--Alban Hertroys[EMAIL PROTECTED]magproductions b.v.T: ++31(0)534346874F: ++31(0)534346876M:I: www.magproductions.nlA: Postbus 4167500 AK Enschede// Integrate Your World //---(end of broadcast)--- TIP 1: 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] pg 8.1.2 performance issue
On Mar 31, 2006, at 1:51 PM, Ed L. wrote: This indeed appears to be locking problem from within Apache::Session where it deletes a row from the DB but fails to commit the change for an extended period while another And you should read well the notes in the Pg driver for Apache::Session where it requires you to define your transaction model. Ie, this is pilot error more than Apache::Session problem. Apache::Session works extremely well with Postgres. ---(end of broadcast)--- TIP 1: 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] database design questions
Alban Hertroys wrote: Ottavio Campana wrote: CREATE TABLE person ( id SERIAL, name TEXT ); how can I do it with a INT8 instead of a INT4? Do you really expect that sequence to reach over 2 billion? Otherwise I'd stick with the SERIAL, nothing wrong with that unless you're selling Depends on what the dynamics of his design are. I.e. if he frequently creates and deletes people, then he can consume a lot of id-space even though there aren't many real people in the database itself. Personally, I doubt this is the case. But, can see other applications where this could be true. Since you can't reuse old id's, it's easier to just use a bigger size datum than having to worry how the database will react when/if you run out of them! electrons seperately or something like that (hmm... how much are they? I sure could use a few extra). Three for a quark... keep the charge! [sic] : --don ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgpool ABORT + no transaction warning
On Apr 3, 2006, at 3:05 AM, Tatsuo Ishii wrote: The problem is not reproduced here. Do you have any idea how to reproduce it? If I did, I'd resolve it. :P I suppose I can try restarting the connection pools on this server just to see what happens. If I am able to do that, I will post the results. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) On Apr 1, 2006, at 8:01 AM, Tatsuo Ishii wrote: A while back, I posted about seeing a number of warnings from pgpool: http://archives.postgresql.org/pgsql-admin/2005-03/msg00305.php A typical pair of statements in my postgres logs looks like this: WARNING: there is no transaction in progress STATEMENT: ABORT Tatsuo Ishii declared that these were harmless, and I took (and still take) his word for it. At some point in the past with my old configuration (postgres 8.1.3 / pgpool 2.5.1) I did something that prevented the warnings from showing up. On a system running postgres 8.1.3, I recently upgraded pgpool from 2.5.1 to 3.0.1. When I upgraded pgpool, I began seeing the warnings again after a long window of not having seen them. My configuration files for pgpool and postgres didn't change during the upgrade, so I'm wondering what I might've been doing to avoid polluting my logs with them previously and why they are showing up again. Could you tell me how to reproduce the problem? Here's what happened: I performed an on-line backup and recovery to move postgres from one server to another. On the new server, I installed pgpool 3.0.1 and copied over my old configuration files. I have two instances of pgpool running on the server and am only using pgpool for connection pooling, not load balancing or replication. The only three settings that are different between the two configurations are port, logdir, and num_init_children. Below is one pgpool.conf (with a few paths changed back to the defaults just for obfuscation), which doesn't include child_max_connections, insert_lock, or ignore_leading_white_space because I just used my 2.5.1 configuration file. I assume it would just use defaults for these values if they weren't specified in the file? listen_addresses = '*' port = socket_dir = '/tmp' backend_host_name = '' backend_port = 5432 backend_socket_dir = '/tmp' secondary_backend_host_name = '' secondary_backend_port = 0 num_init_children = 64 max_pool = 4 child_life_time = 300 connection_life_time = 10 logdir = '/tmp/pgpool1' replication_mode = false replication_strict = true replication_timeout = 5000 load_balance_mode = false weight_master = 0.5 weight_secondary = 0.5 replication_stop_on_mismatch = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql and streams
OK - Now I see the COPY patch, adding the CopyManager class to the jdbc driver. This is exactly the functionality I'm looking for. Has anyone gotten this to work with the latest codebase? Is there a new patch available? Thanks for your help, Chris -Original Message- From: Tino Wildenhain [mailto:[EMAIL PROTECTED] Sent: Thursday, March 30, 2006 1:07 AM To: Christopher Condit Cc: pgsql-general Subject: Re: [GENERAL] pgsql and streams Christopher Condit schrieb: Back to this thread - I realize that in the COPY TO documentation, it states the CSV file used for loading must be on the server's disk. If I can't put files on the server's disk, I'm curious if I can use the STDIN option from my JDBC client to load the data to the server? Using psql I can stream data after the call. Is there anyway to accomplish the same thing with JDBC? Not sure about JDBC but you can in fact stream your data to COPY via STDIN - which is the network socket of your database connection. I'm using this with python for example. Regards Tino ---(end of broadcast)--- TIP 1: 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] Performance Killer 'IN' ?
Tom Lane wrote: Well, here's our problem it would seem: the planner is estimating the IN clause to match 317227 rows, rather than the actual 2522. That's naturally going to bias it against choosing an indexscan. You need to get that estimate closer before there's going to be much chance of choosing the right plan. What do you mean with larger statistics target? See ALTER TABLE SET STATISTICS, or just change default_statistics_target and re-ANALYZE. Thanks, that definitly looks like a starting point. I will test it and post my results. Btw. what happens if the estimation would be to low? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Which error constant to use for permission deny error when updating a table that user is not allowd to
Hello, I am trying to catch permission denied exception. For example, user 1 is not allowed to update table 2, when user1 updated table 2 there is a permission excetpion. In http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html, I tried modifying_sql_data_not_permitted , sql_routine_exception, external_routine_exception but none of them catch the permission deny error. Could someone tell me which error constant should I use to catch the permission denied error please? Thanks a lot! Ying ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Which error constant to use for permission deny error when updating a table that user is not allowd to
Why doesn't test before update? IF EXISTS( SELECT * FROM information_schema.table_privileges WHERE grantee = '1' AND table_name = '2' AND privilege_type = 'UPDATE') THEN ... ELSE ... END IF;On 4/3/06, Emi Lu [EMAIL PROTECTED] wrote: Hello,I am trying to catch permission denied exception. For example, user 1 isnot allowed to update table 2, when user1 updated table 2 there is apermission excetpion.In http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html,I tried modifying_sql_data_not_permitted , sql_routine_exception,external_routine_exception but none of them catch the permission deny error.Could someone tell me which error constant should I use to catch thepermission denied error please?Thanks a lot!Ying---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match-- William Leite Araújo
[GENERAL] Log Slow Queries
Hi Guys, Is there a feature in PostgreSQL like --log-slow-queries(MySQL) ??? Regards, Marcelo P. ___ Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz. http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
On Thu, 30 Mar 2006, kurt _ wrote: I am having a problem with Sun Java Studio Creator because the latest version of the JDBC driver returns a field length of -1 for text fields. You should try the latest development driver, 8.2dev-501. Kris Jurka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql string parsing
On Mar 29, 2006, at 12:19 PM, Tony Caduto wrote: [EMAIL PROTECTED] wrote: Hi Folks, I'm looking for the fatest way to parse string in a postgresql function and insert each parsed chunk in a table. Something like that: You might be able to use the |string_to_array function which | splits a string into array elements using the provided delimiter which could be a EOL marker. Then use a loop to iterate through the array and insert into your table. Using a language other than plpgsql might also be a good idea. Perl, for example. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Create an index with a sort condition
On Mar 29, 2006, at 2:57 PM, Simon Riggs wrote: On Wed, 2006-03-29 at 03:14 -0800, sylsau wrote: I use PostgreSQL 8.1 and I would like create and index on a table's field with a sort condition on this field. For example, I have a table named books and I want create an index on the fields title and id_book with an order by id_book descendant. I know how to create the index without this condition : create index book_index on books(id_book, title); The index is already sorted and can be scanned forwards or backwards. I believe he's talking about something like CREATE INDEX books__id_title ON books(id_book, title DESC); which of course we don't support. But you can define a custom set of operators that work backwards and use those to define the index, and then use them in the ORDER BY. BTW, is there a TODO for this? Second request for it I've seen in a week... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Comparing 7.4 with 8.0
On Mar 29, 2006, at 12:58 PM, Reimer wrote: Hello, We would like to have only one PostgreSQL version at our clients, currently some are using 7.4.6 and others 8.0.6. The first thing is to migrate those 7.4.6 clients to 8.0.6. But before, I´ve to convince them that such migration will be worth for them. My idea is to write a document with all enhancements they will have if they migrate to 8.0.6. If nothing else they absolutely should upgrade to the latest 7.4; 7.4.6 had data corruption bugs in it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
On Mar 31, 2006, at 12:51 AM, Tom Lane wrote: Well, if you are looking for the lowest-common-denominator textual column datatype, then varchar(255) is probably it ... I think even Bill Gates would feel ashamed to sell a database that could not handle that. But my reading of the OP's question was about whether there's a usefully large value of N for which every available DB will take varchar(N). I'm not real sure what the practical limit of N is in that question, other than being pretty confident that Postgres isn't holding down last place. Comments anyone? Not sure if it's still true, but DB2 used to limit varchar to 255. I don't think anyone limits it lower than that. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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] How to delete all operators
On Mar 31, 2006, at 5:17 AM, Martin Pohl wrote: Is there any way to drop all operators (given they are all in the schema public) in a script? Something like (pseudocode): Drop all operators in schema public Nope, though information_schema or the newsysviews project on pgFoundry might make it easier to get that list of operators. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] FAQ 1.1
On Mar 31, 2006, at 4:17 AM, Dave Page wrote: Given the tendency people have to remove the capitalised bits to get 'postgre', we'd probably end up with 'ostres' Man I hate when people do that. I think we should just rename the database to 'Fred'. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Getting more information about errorcodes such as when these error1 happen
On Mar 29, 2006, at 11:13 AM, Emi Lu wrote: I found errorcodes info here: http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html But I am afraid that I could not imagine when and under what possible circumstances some errorcodes may happen just by their names such as : locator_exception, invalid_grantor, active_sql_transaction , and so on. I tried to search the online docs in order to get more info such as when will errorcode X happens. But I could not find it. Could someone tell me some links that I can find more information about these errorcodes please? Unfortunately I suspect the only answer is the source code. Any improved documentation you could provide would be most welcome. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Log Slow Queries
MaRCeLO PeReiRA wrote: Hi Guys, Is there a feature in PostgreSQL like --log-slow-queries(MySQL) ??? Unfortunately not. What you can do is edit your postgresql.conf file and set: log_min_duration_statement See http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN for more info. This will go into the main log file, it won't create a separate log (as slow-queries allows you to do in mysql). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Database restoration performance issue on PostgreSQL 7.4.7
Hi all, the database restoration on my PosgreSQL 7.4.7 Linux installation is terribly poor. It takes more than one and half hour to restore a 61M restored database. Here are my commands to backup and restore. - backup: pg_dump -d $DB_NAME db.bak - restore: psql -d $DB_NAME db.bak Is the problem related to my backup and restore command, or it's the problem of my system, or PostgreSQL installation? Best regards, Leon __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] what datatype is for autonumbering.in postgress
hi
i have to make ine field autonumber field what datatype should be used..??
plz reply meee..
On 4/3/06, William Leite Araújo [EMAIL PROTECTED] wrote:
You can make a function to do this.
CREATE FUNCTION drop_operators(text) RETURNS BOOL AS
$$
DECLARE
op record;
BEGIN
FOR op IN SELECT opname
FROM pg_operator as o left join pg_namespace as n on (
o.oprnamespace = n.oid)
WHERE nspname = $1
LOOP
EXECUTE 'DROP OPERATOR '||quote_literal(op.opname)||';';
END LOOP;
RETURN TRUE;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
And so: SELECT drop_operators('public');
On 3/31/06, Martin Pohl [EMAIL PROTECTED] wrote:
Hi,
I have a database with operators and functions in plpgsql.
To update the data to the latest version I would like to drop all
operators.
There might be some, that I don't know. I don't have access to the
database,
but have to write a script, that will update the data.
Is there any way to drop all operators (given they are all in the schema
public) in a script?
Something like (pseudocode):
Drop all operators in schema public
Thanks in advance for answers
--
E-Mails und Internet immer und überall!
11 PocketWeb, perfekt mit GMX: http://www.gmx.net/de/go/pocketweb
---(end of broadcast)---
TIP 1: 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
--
William Leite Araújo
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Database restoration performance issue on PostgreSQL 7.4.7
Leon Pu [EMAIL PROTECTED] writes: the database restoration on my PosgreSQL 7.4.7 Linux installation is terribly poor. It takes more than one and half hour to restore a 61M restored database. Here are my commands to backup and restore. - backup: pg_dump -d $DB_NAME db.bak - restore: psql -d $DB_NAME db.bak Try dropping the -d switch to pg_dump. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] what datatype is for autonumbering.in postgress
deepak pal wrote: hi i have to make ine field autonumber field what datatype should be used..?? serial. http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] what datatype is for autonumbering.in postgress
am 04.04.2006, um 10:07:58 +0530 mailte deepak pal folgendes: hi i have to make ine field autonumber field what datatype should be used..?? plz reply meee.. You can use serial data types for this, read http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL On 4/3/06, William Leite Araújo [EMAIL PROTECTED] wrote: You can make a function to do this. Please, no silly fullquote below your question. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] FAQ 1.1
[EMAIL PROTECTED] (Jim Nasby) writes: On Mar 31, 2006, at 4:17 AM, Dave Page wrote: Given the tendency people have to remove the capitalised bits to get 'postgre', we'd probably end up with 'ostres' Man I hate when people do that. I think we should just rename the database to 'Fred'. :) Yeah, someone at the office was asking me on the elevator about whether some Post-something was somehow up and coming. In retrospect, I think he was trying to pronounce Postgre, and arrived at something I had never heard before... -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://www3.sympatico.ca/cbbrowne/lisp.html When I was a boy of fourteen, my father was so ignorant I could hardly stand to have the old man around. But when I got to be twenty-one, I was astonished at how much the old man had learned in seven years. -- Mark Twain ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Log Slow Queries
On Tue, Apr 04, 2006 at 12:25:09PM +1000, Chris wrote: MaRCeLO PeReiRA wrote: Hi Guys, Is there a feature in PostgreSQL like --log-slow-queries(MySQL) ??? Unfortunately not. What you can do is edit your postgresql.conf file and set: log_min_duration_statement You can also set it per connection: test=# set log_min_duration_statement =1; SET If you only want it some times but not others. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Create an index with a sort condition
On Mon, Apr 03, 2006 at 12:48:22PM -0400, Jim Nasby wrote: I believe he's talking about something like CREATE INDEX books__id_title ON books(id_book, title DESC); which of course we don't support. But you can define a custom set of operators that work backwards and use those to define the index, and then use them in the ORDER BY. BTW, is there a TODO for this? Second request for it I've seen in a week... Well, if COLLATE support ever gets done, it'll fix this too and be SQL compliant to boot. Unfortunatly I havn't had much time to work on this recently and there hasn't been much interest externally. Last time I was bogged down by keeping up with the number of commits to the sort code which is ofcourse intimately related to this. One day... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Create an index with a sort condition
Martijn van Oosterhout kleptog@svana.org writes: [ Q about reverse-sort opclasses ] Well, if COLLATE support ever gets done, it'll fix this too and be SQL compliant to boot. I keep having a nagging feeling that COLLATE is a completely inappropriate way to deal with reverse-sort semantics for non-textual datatypes. Still waiting to see this patch ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks
I'm guessing that CREATE TABLE in itself doesn't take an ACCESS EXCLUSIVE lock because there's nothing yet to lock. But can CREATE TABLE ... INHERITS ... take an ACCESS EXCLUSIVE lock? Is it functioning as an ALTER TABLE? I'm dealing with an application that can potentially do ad hoc DDL. It uses a PG/pgSQL function, and the only DDL statements in the function are CREATE TABLE and CREATE INDEX statements. But I'm noticing that during the backup process (with pg_dump or pg_dumpall), the function is acquiring ACCESS EXCLUSIVE locks and bringing the application to its knees. This seems to be a result of connections backing up waiting for the DDL to finish, and the DDL can't finish until the backup process finishes because of the function's ACCESS EXCLUSIVE lock conflicting with the database-wide ACCESS SHARE locks acquired by the backup process. -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 3004 B Poston Avenue Nashville, TN 37203-1314 615-260-0005 (cell) 615-469-5150 (office) 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CREATE TABLE (with INHERITS) and ACCESS EXCLUSIVE locks
Thomas F. O'Connell [EMAIL PROTECTED] writes: I'm dealing with an application that can potentially do ad hoc DDL. It uses a PG/pgSQL function, and the only DDL statements in the function are CREATE TABLE and CREATE INDEX statements. But I'm noticing that during the backup process (with pg_dump or pg_dumpall), the function is acquiring ACCESS EXCLUSIVE locks and bringing the application to its knees. Please provide a test case. AFAIR neither of those should take any AccessExclusive locks --- except on the new table, which shouldn't matter because pg_dump won't see it. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Create an index with a sort condition
On Tue, Apr 04, 2006 at 01:40:52AM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: [ Q about reverse-sort opclasses ] Well, if COLLATE support ever gets done, it'll fix this too and be SQL compliant to boot. I keep having a nagging feeling that COLLATE is a completely inappropriate way to deal with reverse-sort semantics for non-textual datatypes. Still waiting to see this patch ... How so? All it does it invert the result of the comparison. If we do it for textual types it'll work automatically for all other types too. All the details of how it would work were described back here: http://archives.postgresql.org/pgsql-hackers/2005-12/msg01121.php The last patch unfortunatly no longer applies cleanly so you can't really test it. If someone really wants this feature, now's the time to pipe up. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
