[GENERAL] DBA Book - Not postgresql book - practical or something newer?
Unfortunately there isn't a current version of Joshua Drake's book out there (which I do own along with PostgreSQL Essential Reference by Stinson), so alternatively, can anyone recommend a good DBA book outlining best practices, physical design, etc? I would like something that is relevant to PostgreSQL even though it won't likely be PostgreSQL specific. Based on current best practices I could then investigate which tools postgres has available to implement them. Currently our small company has a single redhat server for the database and web server, backups obviously are on a separate box. We want to add hardware over the next few months and would like to/need to separate functions and take advantage of things like: replication, PITR, clustering, failover, plgpsql, material view perhaps, and likely more which would be useful to speed up and protect our data. I want to increase my DBA and postgres skills prior to the upgrade, and would like some direction from somewhere (book, group) on where to go from here. Our current database is about 50G large and is a fairly straightforward setup. I am the DBA and am also one of the developers, and my practical DBA knowledge comes from setting up our database and administering it. I make use of views and plpgsql, but from following the lists realize that there are many more tools available in postgres that could be used. Two possible references I found are: - “Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more” by S. Lightstone, T. Teorey, T. Nadeau http://www.amazon.com/Physical-Database-Design-professionals-exploiting/dp/0123693896 - Database Administration: The Complete Guide to Practices and Procedures by Craig S. Mullins http://www.amazon.com/Database-Administration-Complete-Practices-Procedures/dp/0201741296/ref=sr_1_1?ie=UTF8s=booksqid=1202257162sr=8-1 Can anyone recommend these, or others? Any and all suggestions welcome. Thanks Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DBA Book - Not postgresql book - practical or something newer?
Olexandr Melnyk wrote: Joe Celko's SQL Programming Style is a good not vendor-specific book. As for PostgreSQL-specific books, I can only speak for one I read before: Bruce Momjian's PostgreSQL: Introduction and Concepts. It is a good introductionary book, although it isn't based on PostgreSQL 8 series. Thanks Olexandr. I've got one of Celko's SQL books which I use as a reference. I'm looking for something that will help me as we move from our all-in-one server to something more robust. I want something that will give me pointers on what's needed and maybe best practices. I don't have any associates available to advise me on where my priorities should be: failover of course, but which RAID for the db, when and how to __ (cluster, use material views, numerous other things which postgres has available, etc). While there's a lot of information in the docs and flowing through the lists, it doesn't provide me with what I need. Since there isn't an updated version of Practical PostgreSQL to use as a guideline/reference I have to look for a non-DBMS specific reference. Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] top posting
Thomas Hart wrote: Andrew Sullivan wrote: I don't think top posting is always the crime it's made to be (and I get a little tired of lectures to others about it on these lists). A I agree. Obviously there is convention, and I will post in the style generally accepted in the list, but to me it always made more sense to top post. If you're keeping up on the conversation, then the relevant information is right there, and if you weren't, it's not that difficult to go through and catch up I agree that top-posting can sometimes be easier to read. However, from the perspective of someone who *often* searches the archives for answers it is usually *much* easier to find a complete problem/solution set when the responses are bottom posted and/or interleaved. Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to redirect output to a file
A. Kretschmer wrote: am Tue, dem 04.12.2007, um 20:19:29 -0800 mailte pc folgendes: Hi, How to redirect the output of an sql command to a file? Thanks in advance within psql you can use \o filename, from the shell you can use this: [EMAIL PROTECTED]:~$ echo select now() | psql test now.txt [EMAIL PROTECTED]:~$ cat now.txt now --- 2007-12-06 14:21:58.963405+01 (1 row) Regards, Andreas This is similar to Andreas' solution, and which we use in our shell scripts: [EMAIL PROTECTED] ~]$ psql mydb -c SELECT cola, colb, description FROM myfile; myOutFile.txt If the sql string contains multiple commands, they will be executed within a single transaction, unless you use BEGIN/COMMIT within it to split it up into multiple transactions. Ron ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] getting list of tables from command line
Craig White wrote: I wrote a little script to individually back up table schemas, table data and then vacuum the table and it works nicely but I wanted a way to query a database and get a text file with just the table names and cannot figure out a way to do that. my script looks like this... (all I want is to get a list of the tables into a text file pg_tables) #/bin/sh # DB_NAME=whatever # for i in `cat pg_tables` do pg_dump --username=postgres \ --schema=db --table=$i \ --schema-only \ $DB_NAME schemas/$i.sql pg_dump --username=postgres \ --schema=db \ --table=$i \ --data-only \ $DB_NAME data/$i.sql vacuumdb --username=postgres \ --dbname=$DB_NAME \ --table=db.$i \ --verbose \ --full done Is there a way to do that? From the command line you can run: psql mydbname -c \d to get a list of all public table names. Or just select the table names from pg_tables and send it to a file: psql myDBname -c SELECT tablename FROM pg_tables WHERE schemaname = 'someschema'; my_tables.txt This works on my 8.1 database on RHEL. You could also use something similar inside of your script to generate the table names and send them to pg_dump. As far as I know, if you specify a table name, but don't specify a schema name to pg_dump, it will automatically dump all tables with that name, irregardless of what schema they belong to. I'm sure someone will let me know if that's not correct... hth Ron Craig PS there's a lack of cohesion between various commands such as vacuumdb and pg_dump for things like '--schema' ---(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 ---(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]
carter ck wrote: Hi all, I was trying to create function in postgres, but it returns error mentioning the language is NOT defined. The function is as following: CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ DECLARE d_word ALIAS FOR $1; d_phrase ALIAS FOR $2; BEGIN IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; RETURN 1; END; $$ LANGUAGE plpgsql; ERROR: language plpgsql does not exist HINT: Use CREATE LANGUAGE to load the language into the database. I was wonderring why it is not included by default? Or have I missed out something in the configuration! According to the documentation, you have to explicitly create the language in order to register the language with the database: CREATE LANGUAGE plpgsql; Assuming everything else is set up properly, this will allow you to use the plpgsql language. Ron ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Debian problem...
There's likely someone here that can help you, if you can give us some more info. To start with, did the uninstall even work? Ron Tom Allison wrote: Ran into a problem. I hosed up postgresql by deleting the data directory. So I thought I would just uninstall and reinstall postgres using Debian packages. Now I have nothing working. Wondering if anyone here has any suggestions on what to do with a Debian installation. If not, I'm checking that list too, but it might be a while... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Duplicate Unique Key constraint error
Harpreet Dhaliwal wrote:
Hi,
I keep getting this duplicate unique key constraint error for my
primary key even
though I'm not inserting anything duplicate. It even inserts the
records properly
but my console throws this error that I'm sure of what it is all about.
Corruption of my Primary Key can be one of the possibilities but I'm
really not sure how
to get rid of this corruption and how to re-index the primary key.
Also, I was wondering what could be the cause of this PK corruption,
if possible and what does can this corruption lead to.
I mean what are its cons.
Thanks,
~Harpreet
You haven't really given any useful information about your primary key,
but if you are using SERIAL as the column type (INT type with a
sequence) you may just be having a problem with its current value (but
then inserts shouldn't work).
If you are using a sequence here, see what it's current value is and
compare it to the highest value in the column. If its value is less than
the columns max() value, just reset the value in the sequence.
imp=# CREATE TABLE dup_pkey (id SERIAL PRIMARY KEY, insert_order int);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (1);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (2);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (3);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (4);
imp=# SELECT * FROM dup_pkey;
id | insert_order
+--
1 |1
2 |2
3 |3
4 |4
(4 rows)
Now, if you set the value below what the max() column value is, you will
have a problem with inserts.
imp=# SELECT setval('dup_pkey_id_seq',3);
setval
3
(1 row)
imp=# INSERT INTO dup_pkey (insert_order) VALUES (5);
ERROR: duplicate key violates unique constraint dup_pkey_pkey
If this is the case, use setval() to update the value of the sequence to
the max() value of your primary key. You can use \d to get information
about your table, including the sequence name. However if, as you say,
it IS inserting records properly, then this ISN'T going to help.
hth
Ron
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [Fwd: Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum]
Andrew Sullivan wrote: On Fri, Jun 01, 2007 at 01:27:14PM -0700, Ron St-Pierre wrote: imp=# select age(datfrozenxid) from pg_database where datname = 'imp'; age 1571381411 (1 row) Time to start VACUUM FULL ANALYZE over the weekend. I guess this comes too late, but you don't need VACUUM FULL for that. Yes, I know that VACUUM FULL isn't required here, but because the weekend is our slow time on the server I thought that I would perform a full vacuum. VACUUM FULL _does not_ mean vacuum everything! What do you mean by this? I wanted to do both a VACUUM ANALYZE and a VACUUM FULL, so ran VACUUM FULL ANALYZE. Is there something odd about VACUUM FULL, other than locking the table it's working on? A ---(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
[Fwd: Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum]
I stopped using autovacuum months ago because of similar problems (version 8.1.4). Because we do some major inserts and updates about four times a day, there were a few tables that I didn't want autovacuumed. Even after I turned autovacuum off for these tables it still tried to vacuum them while the updates were running. Instead, I just created cron jobs to vacuum the two tables with the most updates daily, and the one with the most inserts weekly. Performance has been pretty good. Most other tables don't have nearly as many updates, however when I checked the transaction IDs, I see that a problem is creeping closer as we slowly approach the 2 billion mark. imp=# select age(datfrozenxid) from pg_database where datname = 'imp'; age 1571381411 (1 row) Time to start VACUUM FULL ANALYZE over the weekend. Ron Original Message select age(datfrozenxid) from pg_database where datname = 'your database' 2 billions and you are screwed. Autovacuum starts panicking way before that, to have enough slack. dbname=# select age(datfrozenxid) from pg_database where datname = 'dbname'; age 1648762992 (1 row) Sooo... looks like time to quickly upgrade to 8.1 head. Thanks for the help, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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: [Fwd: Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum]
Alvaro Herrera wrote: FYI, in 8.2 and up the Xid wraparound problem is considered on a table by table basis, which means that only the tables that have not been vacuumed recently need to be vacuumed. The need for database wide vacuuming is gone. That's good. Time to start VACUUM FULL ANALYZE over the weekend. For Xid wraparound you don't need FULL anyway, in any release I realize that I don't need a FULL VACUUM for the xid wraparound, but since it hasn't been run for a while I'll do a FULL. Thanks! Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Pattern Matching - Range of Letters
I'm sure that others have solved this but I can't find anything with my (google and archive) searches. I need to retrieve data where the text field is within a certain range e.g. A-An Am-Bc Bc-Eg Yi-Zz Does anyone know of a good approach to achieve this? Should I be looking into regular expressions, or maybe converting them to their ascii value first? Any comments are appreciated. postgres 8.2.4, RHEL Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Pattern Matching - Range of Letters
Thanks Richard and Joshua, I had no idea that BETWEEN worked for text. SELECT * FROM Your_table AS YT WHERE YT.text_field BETWEEN 'Aa' AND 'An'; postgres=# select * from test where test between 'A' and 'An'; test -- A Ab Ac (3 rows) Ron Ron St-Pierre wrote: I'm sure that others have solved this but I can't find anything with my (google and archive) searches. I need to retrieve data where the text field is within a certain range e.g. A-An Am-Bc Bc-Eg Yi-Zz Does anyone know of a good approach to achieve this? Should I be looking into regular expressions, or maybe converting them to their ascii value first? Any comments are appreciated. postgres 8.2.4, RHEL Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Pattern Matching - Range of Letters
William Garrison wrote:
That won't work if you have a value Anz in there. It would be in
the gap between An and Am.
Yes, I realized that too. My solution to it is a bit of a hack, but it's
easy and it works for me in this case. I translate everything to
uppercase and simply append 'ZZ' to the end of the second string.
None of the strings I am comparing to are longer than 6 characters, and
there are no numerical values in them.
Ron
create table test (test text);
insert into test values ('A');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
insert into test values ('e');
insert into test values ('Ab');
insert into test values ('Ac');
insert into test values ('Amz');
insert into test values ('Az');
select * from test where test between 'A' and 'Am';
A
Ab
Ac
select * from test where test between 'An' and 'Bc';
Az
I wouldn't use between in this case. I'd suggest this:
select * from test where test = 'A' and test 'Am';
A
Ab
Ac
select * from test where test = 'Am' and test 'Bc';
Amz
Az
The end will be tricky because is not zz so you will need
the last select to be
select * from test where test = 'Yi';
The beginning will be tricky too if you allow things that come before
A such as 0-9 or spaces.
Richard Broersma Jr wrote:
--- Ron St-Pierre [EMAIL PROTECTED] wrote:
I'm sure that others have solved this but I can't find anything with
my (google and archive) searches. I need to retrieve data where the
text field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
Yi-Zz
Does anyone know of a good approach to achieve this? Should I be
looking into regular expressions, or maybe converting them to their
ascii value first?
Regular expressions would work, but a between statement should work
also.
SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Excluding a table from pg_dump
Greg Sabino Mullane wrote: The database contains several schemas and excluding comment_archive by moving it to different schema doesn't sound very convenient. pg_dump doesn't have an option to dump multiple schemas at once. Are there any working -X patches for pg_dump or does anyone have other possible solutions? The next version of Postgres (8.2) will have the ability to do everything you want. It's active now in cvs, if you want to try it out. You can exclude one or more tables with the -T flag, and can include or exclude schemas with the -n and -N resepectively. Any of the four flags can be used multiple times, and they all accept POSIX-style regular expressions as well. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation That's excellent news! I've needed this feature for a while now too. Regards Ron St.Pierre ---(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] PL/pgSQL Problem
Hi, I'm having a problem with one of my functions, where I delete all rows containing a particular date and then re-insert a row with that same date. When I try this I get a constraint error. This just started recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL). here's the function: CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS ' DECLARE compDate DATE; currCount INT; BEGIN compDate := current_date::date; LOOP DELETE FROM dm.systemCounts WHERE updateDate::date = compDate::date; INSERT INTO dm.systemCounts (updateDate) VALUES (compDate::date); . and here's the error: ERROR: duplicate key violates unique constraint systemcounts_pkey CONTEXT: SQL statement INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date) PL/pgSQL function updatesystemcounts line 8 at SQL statement The only explanation I can think of is that maybe the newer version of postgres needs a COMMIT inside the function. Any ideas? Thanks Ron St.Pierre ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PL/pgSQL Problem
Michael Fuhr wrote: On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote: ERROR: duplicate key violates unique constraint systemcounts_pkey CONTEXT: SQL statement INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date) PL/pgSQL function updatesystemcounts line 8 at SQL statement Is the table's primary key a serial column? If so then the sequence might be out of sync with the values in the table, in which case you'll need to adjust the sequence's value with ALTER SEQUENCE or setval(). Aha, that was exactly what the problem was. Thanks! Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Version Discrepancy
Douglas McNaught wrote: rstp [EMAIL PROTECTED] writes: pg_config is telling us that we are running version 7.3.6-RH, but when we start psql it shows that we are running 8.1.4 (which is the correct version). [EMAIL PROTECTED] bin]$ pg_config --version PostgreSQL 7.3.6-RH [EMAIL PROTECTED] bin]$ which postmaster ~/bin/postmaster [EMAIL PROTECTED] bin]$ psql imp Welcome to psql 8.1.4, the PostgreSQL interactive terminal. I'm guessing that for some reason the pg_config from 7.X is found first in your PATH. What does 'which pg_config' print? That's it. 'which pg_config' returns /usr/bin/pg_config I guess that I ran it too early on a Saturday morning, I must have blindly assumed that my '[EMAIL PROTECTED] bin]$ pg_config --version' command would look at the current directory first by default. Thanks Doug! Ron -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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
[GENERAL] Autovacuum Logging Info?
Hi, I've been trying to see whether or not autovacuum is vacuuming all of my tables, and how often (for my peace of mind). I can see that it is running, but I don't know what it's doing. There are a handful of key tables in our database which suffer quite a bit if their not vacuumed regularly (at least once a day). I see that there's been quite a bit of discussion recently about this, and in a previous discussion about this, Bruce Momjian raised the question of what people want to see. I would like to see which tables were autovacuumed, when they were vacuumed, and maybe some of the summary information which is displayed in the last few lines of the current vacuum analyze verbose. This is information I could get currently by running vacuum analyze verbose (summary info from last few lines) and through my cron settings (db, tables, and when vacuum is run). Until this is resolved, I guess that I'll turn up my logging and start to browse log files. My two cents :) Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Schema is Missing
We received the following error on our development server this morning (postgresql 7.4.1, debian woody): org.postgresql.util.PSQLException: ERROR: schema customer does not exist When I login to postgres it looks as if the other schemas are okay, but the customer schema is gone. I have a backup from midnight last night which I can restore, but I want to find out the cause of the problem first. .psql_history doesn't display anything useful, just some queries that I ran today and yesterday. I looked at /var/log/messages and /var/log/syslog, and there aren't any zipped backups in the directory, which makes me suspicious. The files contents are: [EMAIL PROTECTED]:/var/log# head messages Feb 16 10:21:43 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 16 10:21:45 ** All firewall rules applied ** Feb 17 10:23:20 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 17 10:23:21 ** All firewall rules applied ** Feb 19 9:59:15 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 19 9:59:17 ** All firewall rules applied ** Feb 22 9:58:10 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 22 9:58:13 ** All firewall rules applied ** Mar 10 06:25:52 imperial syslogd 1.4.1#10: restart. Mar 10 06:30:13 imperial postgres[6330]: [9-1] ERROR: schema customer does not exist and [EMAIL PROTECTED]:/var/log# head syslog Mar 10 06:25:52 imperial syslogd 1.4.1#10: restart. Mar 10 06:30:13 imperial postgres[6330]: [9-1] ERROR: schema customer does not exist Mar 10 06:36:03 imperial postgres[9058]: [9-1] ERROR: schema customer does not exist Where should I look to see if data corruption was the problem? I am holding off restoring the customer schema for now. Thanks Ron St.Pierre -- ___ Play 100s of games for FREE! http://games.mail.com/ ---(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] Schema is Missing
- Original Message - From: Michael Fuhr [EMAIL PROTECTED] To: Ron St-Pierre [EMAIL PROTECTED] Subject: Re: [GENERAL] Schema is Missing Date: Fri, 10 Mar 2006 11:27:54 -0700 On Fri, Mar 10, 2006 at 12:57:17PM -0500, Ron St-Pierre wrote: We received the following error on our development server this morning (postgresql 7.4.1, debian woody): That's pretty old. If you must run 7.4 then at least consider upgrading to the latest minor release, currently 7.4.12. Lots of bugs have been fixed since 7.4.1. We will be upgrading our servers soon, most likely within a month or two, and are planning on upgrading the database at the same time. org.postgresql.util.PSQLException: ERROR: schema customer does not exist When was the last time you know the schema existed? Have you been doing database-wide vacuums? What's the output of the following command? Vacuums have not been run on this database for a while now :( I know that the customer schema was there yesterday, I added data to a few tables, logins to the web pages which it supports worked, etc. It also looks as if the backup from midnight (last night) is okay. SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database; imperial=# SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database; datname |age|age --+---+ impimp | 298777961 | 1372519784 imp TEST | 332548272 | 332548272 testdb | 332548272 | 332548272 imp | 2228730 | 1075970551 fsynchtest | 332548272 | 332548272 template1| 332548272 | 332548272 template0| 332548272 | 332548272 test1| 332548272 | 332548272 (8 rows) .psql_history doesn't display anything useful, just some queries that I ran today and yesterday. I looked at /var/log/messages and /var/log/syslog, and there aren't any zipped backups in the directory, which makes me suspicious. When was the last time you saw those zipped files? Do you know for sure that your system does that? No, not 100% sure. However, the drive was almost full a few weeks ago, and I may have dropped them then, now that I think about it. Who all has access to the server? Could somebody have dropped the schema without your knowing about it? Possible, but not very likely, I' have to say extremely unlikely. The files contents are: [...] Feb 22 9:58:13 ** All firewall rules applied ** Mar 10 06:25:52 imp syslogd 1.4.1#10: restart. Mar 10 06:30:13 imp postgres[6330]: [9-1] ERROR: schema customer does not exist Is the gap between 22 Feb and 10 Mar expected? No, I'm 99% certain it's not. What made syslogd restart? Is that an unusual event for that time? Any hardware problems? Full disk? Has anything else out of the ordinary happened on that system lately? I don't know why syslogd restarted. The disk has about 13G of free space, and nothing unusual has occured lately that we've noticed (otherwise). I've been moving a lot of data into and out of the database over the last two weeks, probably in excess of 25G out and 10GB in, but in a different schema. Ron -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend -- ___ Play 100s of games for FREE! http://games.mail.com/ ---(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] Importing Many XML Records
Thanks George. I just returned from the bookstore and was looking at an XSLT solution in one of the books there. I want to import the data into the DB as regular data, not as XML. I'll look into Saxon and TagSoup as well as the perl module you mentioned. As far as this being outside the scope of the list, I wasn't sure whether or not there were postgres modules to deal with this. Thanks for pointing me to possible solutions. Ron - Original Message - From: George Pavlov [EMAIL PROTECTED] To: Ron St-Pierre [EMAIL PROTECTED] Subject: Re: [GENERAL] Importing Many XML Records Date: Fri, 27 Jan 2006 16:03:20 -0800 I'm sure that this has been asked before but I can't find any reference to it in google, and the search facility on postgresql.org is currently down. http://groups.google.com/groups?q=group%3Apgsql.* provides the same with a slight delay but arguably a better user interface. I have a large number of entries (possibly 10,000+) in an XML file that I need to import into the database (7.4 on Debian) on a daily basis. Does anyone have any recommendations concerning the best way to do this? Is there some tool I should use or should I create the code in java to parse and import the data? If anyone has done this before, I would appreciate hearing how they did this. This is generally outside the scope of this list. I am guessing (since I don't know much about your data format or goals), but you probably want to first transform the XML into a format suitable for importation into the database using COPY, or (much less desirable) a bunch of insert statements. In either case you should become familiar with XSLT processing and write yourself an XSLT template to do the job. I deal with a similar task using Saxon and TagSoup (which I highly recommend for XML that is not well-formatted) and create a CSV file out of a multitude of XML files (or a single XML file), which can then be COPY-ed into a PG table. Instead of a CSV file one could create a SQL script file of INSERT statements. I recommend Jeni Tennison's Beginning XSLT book as an excellent reference on the subject of XSLT. Depending on what your XML looks like you may get away without XSLT at all, but just preprocess it with awk, sed, perl (Template::Extract is a useful module) or whatever strikes your fancy. Other questions to answer are do you want the records to stay as XML in the database or do you want to import them into a regular table format? If the former you may want to get familiar with the pgxml (aka xml2 module) so you can query the XML data once inside your database. George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- ___ Play 100s of games for FREE! http://games.mail.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reusable database design
Joachim Zobel wrote: Hi. I can't be the first to think about this. There is a million online shops out there, which all more or less have the same database design. Has anybody thought about creating generic reusable/customizable designs for such cases? Thx, Joachim Check out your favourite bookstore for resources. Amazon also lists a few books: * Data Model Patterns: Conventions of Thought by David C. Hay: Analyzes data structures common to many types of businesses in areas such as accounting, material requirements planning, process manufacturing, contracts, laboratories, and documents, for analysts who have learned the basics of data modeling (or entity/relationship modeling) but who need more insight to prepare a model of a real business. Includes bw diagrams, and a set of ready-to-use models for typical applications in many industries. Annotation copyright Book News, Inc. Portland, Or. from Amazon.com * The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises by Len Silverston * The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries by Len Silverston * Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design by Terry Halpin * The Data Modeling Handbook : A Best-Practice Approach to Building Quality Data Models by Michael C. Reingruber * Data Modeler's Workbench: Tools and Techniques for Analysis and Design by Steve Hoberman * Analysis Patterns : Reusable Object Models (Addison-Wesley Object Technology: Addison-Wesley Object Technology Series) by Martin Fowler I'm sure that there are even more available. Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] INTERVAL in a function
Michael Fuhr wrote: On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote: SELECT INTO exptime current_timestamp + INTERVAL ''intval''; You're using the literal value 'intval' instead of its value, thus the syntax error. Of course, I should have caught that. You can simplify the statement to this: exptime := current_timestamp + intval; But I think the entire function can be shortened to: CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS ' SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength FROM customer.groups WHERE groupsID = $1 ' LANGUAGE sql; You don't need to check for NULL because the result of the addition will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP is necessary to avoid a return type mismatch error. Perfect. Thanks Michael! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] INTERVAL in a function
I have a simple function which I use to set up a users' expiry date. If a field in a table contains an interval then this function returns a timestamp some time in the future (usually two weeks), null otherwise. I can't pass the interval from the table into a variable properly within the function. Any ideas? CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS timestamp AS ' DECLARE grpID ALIAS FOR $1; intval INTERVAL; exptime TIMESTAMP; BEGIN SELECT INTO intval unitTimeLength::INTERVAL FROM customer.groups WHERE groupsID = grpID; IF intval IS NULL THEN RETURN NULL; ELSE SELECT INTO exptime current_timestamp + INTERVAL ''intval''; RETURN exptime; END IF; END; ' LANGUAGE 'plpgsql'; SELECT getUnitTimeLength(55); ERROR: invalid input syntax for type interval: intval CONTEXT: PL/pgSQL function getunittimelength line 11 at select into variables However if I change the else clause to this: ELSE SELECT INTO exptime current_timestamp; RETURN exptime; END IF; it works: 2004-11-08 16:14:40.273597 (1 row) Thanks Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Reading a text file into PostgreSQL
Eyinagho Newton wrote: Hiya Everyone, Can anyone explain how postgreSQL reads from a text file into tables already created in PostgreSQL? I am also checking the thread in the Forum just to see if someone has written about it in the past. Thanks. Newton Do you mean when you read it in using the COPY command, or something different? Ron ---(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] PostgreSQL 8.0 install woes
Taber, Mark wrote: I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. Im even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host nnn.nnn.nnn.nnn and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. **Mark Taber** State of California Department of Finance Infrastructure Architecture Unit 916.323.3104 //x//294 On the off-chance that you're running ZoneAlarm, make sure that postgres is allowed to run as a server. Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] disk performance benchmarks
oops, sent this to performance by mistake. Jeffrey W. Baker wrote: All these replies are really interesting, but the point is not that my RAIDs are too slow, or that my CPUs are too slow. My point is that, for long stretches of time, by database doesn't come anywhere near using the capacity of the hardware. And I think that's odd and would like to config it to false. What motherboard are you using, and what distro? Earlier you mentioned that you're on linux 2.6.7 and a 64-bit Opteron 244 machine with 8GB main memory, two 4-disk RAID5 arrays (one for database, one for xlogs). Also, did you have a chance to test performance before you implemented RAID? Ron ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [PERFORM] Table UPDATE is too slow
Ron St-Pierre wrote: We have a web based application with data that is updated daily. The biggest bottleneck occurs when we try to update one of the tables. This table contains 58,000 rows and 62 columns, and EVERY column is indexed. Every column is queryable (?) by the users through the web interface so we are reluctant to remove the indexes (recreating them would be time consuming too). The primary key is an INT and the rest of the columns are a mix of NUMERIC, TEXT, and DATEs. A typical update is: UPDATE dataTable SET field01=44.5, field02=44.5, field03='Bob', field04='foo', ... , field60='2004-08-30', field61='2004-08-29' WHERE id = 1234; Also of note is that the update is run about 10 times per day; we get blocks of data from 10 different sources, so we pre-process the data and then update the table. We also run VACUUM FULL ANALYZE on a nightly basis. It now appears that VACUUM wasn't running properly. A manual VACUUM FULL ANALYZE VEBOSE told us that approximately 275000 total pages were needed. I increased the max_fsm_pages to 30, VACUUMED, renamed the database and re-created it from backup, vacuumed numerous times, and the total fsm_pages needed continued to remain in the 235000 - 27 range. This morning I deleted the original (renamed) database, and a VACUUM FULL ANALYZE VEBOSE now says that only about 9400 pages are needed. One question about redirecting VACUUMs output to file though. When I run: psql -d imperial -c vacuum full verbose analyze; vac.info vac.info contains only the following line: VACUUM I've been unable to capture the VERBOSE output to file. Any suggestions? snip Also, thanks for everyone's input about my original posting, I am investigating some of the options mentioned to further increase performance. Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [PERFORM] Table UPDATE is too slow
Tom Lane wrote: Ron St-Pierre [EMAIL PROTECTED] writes: One question about redirecting VACUUMs output to file though. When I run: psql -d imperial -c vacuum full verbose analyze; vac.info vac.info contains only the following line: VACUUM I've been unable to capture the VERBOSE output to file. Any suggestions? You need to catch stderr not only stdout. (I'd be less vague if I knew which shell you were running, but sh- and csh-derived shells do it differently.) Oops, I'm running bash. I just redirected stderr to the file psql -d imperial -c vacuum full verbose analyze; 2 /usr/local/pgsql/vac.info which gives me exactly what I want. Thanks again Tom Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] psql \o weirdness
Peter Eisentraut wrote: Ron St-Pierre wrote: the line in the sql script to SELECT * FROM myFunction() \o /dev/null; output from this is suppressed. HOWEVER, I get an error when it tries to process the next line; psql:/usr/local/pgsql/quiet.sql:2: ERROR: syntax error at or near SELECT at character 26 No, what actually happens is that the first SELECT is never executed, because there is no terminating semicolon. The semicolon at the end of the line belongs to the \o command. So when it processes the next line, it appends the text to the previous command and tries to execute that invalid concatenation. What you really want to use instead is the \g command. You're right Peter, the \g works. Thanks for the explanation, I can now see why using \o wouldn't work. Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] cannot reach http:/archives.postgresql.org
You can also try pgsql.ru Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Dump and Restore
Eduardo S. Fontanetti wrote: How can I do a test if my dumping is really working, I can't apply the dump in my database, because it will overwrite a lot of data. I was thinking about restore in another database name, but I can't, it always restore on the original database. Somebody have a cooking recipe about to test if my backup is working fine, and will help if I need sometime? snip OK here's a recipe: If you're using pg_dump as in: pg_dump dbName fileName create your new database: createdb myNewDB (don't forget your encoding) and then restore from the dump: psql -d myNewDB -f fileName Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] New to Postgres
Darkcamel wrote: Hello all, I am new to postgres and don't really understand how the database is set-up. I am very fluent with mysql and sql2000, but postgres is new to me. If anyone can point me to some good links I would appreciate it very much. Thanks, Darkcamel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Well then I suggest you check out the docs at http://www.postgresql.org/docs/7.4/static/index.html. Specifically you can start with the Tutorial - Getting Started to show you how to create a database in postgres and access it. Then check out The SQL Language - Data Definition and SQL Language - Data Manipulation to see postgres's syntax for accessing / modifying the database and data. hth Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] most idiomatic way to update or insert?
Richard Huxton wrote: An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought clearly about something. Can you give an actual example of where you need this? How about stocks for a simple example? Let's say you have a simple table with the stock symbol, stock exchange, high, low, open, close and volume. Every day you update the data for each stock. But there are always new stocks listed on an exchange, so when a new stock shows up you have to do an insert instead of an update. Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] most idiomatic way to update or insert?
Richard Huxton wrote: Ron St-Pierre wrote: Richard Huxton wrote: An update or insert would be useful sometimes, but it's not always necessary. Indeed, if I find I don't know whether I'm adding or updating something I take a long hard look at my design - it ususally means I've not thought clearly about something. Can you give an actual example of where you need this? How about stocks for a simple example? Let's say you have a simple table with the stock symbol, stock exchange, high, low, open, close and volume. Every day you update the data for each stock. But there are always new stocks listed on an exchange, so when a new stock shows up you have to do an insert instead of an update. If it is just a simple table then delete all of them and insert from scratch. If you wanted to track changes over time (perhaps more likely), you'd have a separate table with the company name/address etc and a log table. At which point you'll want to know if it's a new company or not... Okay, this simple example really exists, but the simple table also includes a date that the stock was last traded, so we have: stock symbol, stock exchange, high, low, open, close, volume, date, plus a few more fields But the data isn't always updated at one time, as we can update all stocks for one exhange and possibly only some of the stocks for a particular exchange in one go. Even if the data is received for only one exchange we could delete all stocks for that exchange and insert new ones, which would work fine. However some stocks are not traded every day, so we need to show the trading information for the last date that it was traded, so we can't delete them en masse even for the one exchange. BTW these updates do take longer than we'd like so I would appreciate more input on how this setup could be redesigned. Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] most idiomatic way to update or insert?
Greg Stark wrote: Ron St-Pierre [EMAIL PROTECTED] writes: BTW these updates do take longer than we'd like so I would appreciate more input on how this setup could be redesigned. Where is the input coming from? One option is to batch changes. snip Something like update current_stock_price set price = log.price, timestamp = log.timestamp from stock_price log where current_stock_price.stock = stock_price_log.stock and stock_price_log.timestamp between ? and ? We check for new stocks and add them, and initially were using a procedure to do something similar to your code: CREATE OR REPLACE FUNCTION updateData() RETURNS SETOF datatype AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT symbol, tradeDate, tickDate, high, low , open, close, volume FROM exchangedata LOOP RETURN NEXT rec; UPDATE stockdata SET high=rec.high, low=rec.low, open=rec.low, close=rec.close, volume=rec.volume, tradeDate=rec.tradeDate WHERE symbol=rec.symbol; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ... but it took too long. Off hand, do you know if your approach above would be quicker? Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] FATAL: the database system is in recovery mode
We're developing a java app and are using postgres as the database. On our dev server I started the app, closed it, but the java process was still open so I killed it, which caused the above error. I've had to do this in the past but have not had this happen before. I've searched the archives and found a message/reply from Andrew Sullivan about this which implies that it may be more a file system problem. We're using postgresql 7.4.1 on a debian box (and yes, ext2). And fsync is turned on. Aside from not killing processes which hang, is there anything I can do to avoid this problem in the future? Thanks Ron ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ERROR: tables can have at most 1600 columns
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote: STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; ERROR: tables can have at most 1600 columns STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER; ERROR: tables can have at most 1600 columns Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than, say, 1500 times? Because a dropped column is actually only hidden from the user, but it's still present to the system and it will still affect the 1600 limit. That is a good theory, but it doesn't quite explain why Ron's getting the error from DROP COLUMN --- AFAICS, the places that would issue such an error won't get called in that path. I tried to reproduce this and could not: after 1600 cycles of adding and dropping a column, I did indeed start to get tables can have at most 1600 columns from ADD, but DROP continued to behave normally. Ron, are you sure these errors were coming from the DROPs and not only the ADDs? Can you exhibit a test case? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org I re-checked the logs and saw that the error only ocurred on trying to add the column, sorry for the cut-and-paste mistake. But before that I tried a test script and had the same result that Tom did. I think that I'll take Scott's advice and probably use INSERT INTO to re-create the table each time and avoid the 1600 column error. I am currently using TRUNCATE to clear the table before each use but it obviously doesn't re-set the column count. Thanks again for your help. Ron ---(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] ERROR: tables can have at most 1600 columns
I found this error in /var/log/messages yesterday after a cron job wouldn't complete: STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; ERROR: tables can have at most 1600 columns STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER; ERROR: tables can have at most 1600 columns ...etc... The columns didn't exist at the time I tried to drop them, and \dt showed that the table only contained the normal dozen columns. Because I use the COPY command I remove columns to match the input from an ftp'ed file, add the columns, continue processing, etc. After dropping and then re-creating the table the error didn't recur and processing continued. My question is this: every night the database is vacuumed-full-analyze: wouldn't that prevent this condition from happening? Or is there some other regular maintenance I should be running? I'll be away for the next week but I look forward to reading any insight on this when I return. Thanks Ron -- running Postgresql 7.4.2 on debian stable ---(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] Function - sequence - cast
Richard Huxton wrote:
Ron St-Pierre wrote:
I am trying to use a sequence value in a function but I keep getting
an error message:
WARNING: Error occurred while executing PL/pgSQL function
correctaddress
WARNING: line 8 at SQL statement
ERROR: column addressid is of type integer but expression is of
type character varying
You will need to rewrite or cast the expression
And the function looks like:
CREATE FUNCTION correctAddress(INT) RETURNS INT AS '
DECLARE
user_id ALIAS FOR $1;
old_addr INT; new_addr INT;BEGIN
PERFORM nextval(''public.address_addressid_seq'');
If you've set up addressID as a SERIAL then this nextval() isn't
necessary.
INSERT INTO address (SELECT strProvince, strAddress FROM
address WHERE addressID = (SELECT addressID FROM companies WHERE
companyID = (SELECT companyID FROM users WHERE userID=user_id)));
I'm using the force here, but the problem might be here instead. What
are the columns on the address table, and if addressID is the first
one is strProvince a varchar?
WOW! Amazing use of the force, strProvince is a text field :-) You're
correct, the first column is an in and strProvince is text. Thanks for
pointing out the obvious, my 'real' function has about 15 more fields
and I was too busy looking at other things to notice.
UPDATE users SET adressID =
CAST(currval(''public.tbladdress_addressid_seq'') AS INTEGER) WHERE
userID=user_id;
-- --- ^ ^
^^^ ^= ?
Thanks Richard.
Ron
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] composite type and assignment in plpgsql
Ivan Sergio Borgonovo wrote: what's wrong with this? create type tSession as ( ty_found boolean, ty_Session char(32) ); create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin --HERE!!! thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type - assign each variable of type tSession to its corresponding value: thisSession.ty_found := ''t''; thisSession.ty_session := md5(CAST((now( )) AS TEXT)); I haven't looked up the rand() function, but you can see from this how you would cast it and now() to text. return thisSession; end; ' language plpgsql; thx ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings And then you can get the results: select * from getsessionid(1); imperial=# select * from getsessionid(1); ty_found |ty_session --+-- t| cf76cca2b562a0ead48d3eb3810f51cc (1 row) hth Ron ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] composite type and assignment in plpgsql
Ron St-Pierre wrote: Ivan Sergio Borgonovo wrote: what's wrong with this? create type tSession as ( ty_found boolean, ty_Session char(32) ); create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin --HERE!!! thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type - assign each variable of type tSession to its corresponding value: thisSession.ty_found := ''t''; thisSession.ty_session := md5(CAST((now( )) AS TEXT)); I haven't looked up the rand() function, but you can see from this how you would cast it and now() to text. return thisSession; end; ' language plpgsql; thx ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings And then you can get the results: select * from getsessionid(1); imperial=# select * from getsessionid(1); ty_found |ty_session --+-- t| cf76cca2b562a0ead48d3eb3810f51cc (1 row) hth Ron In the above reply, I forgot to mention that you are not using the integer you are passing in as an argument. If you need it (rand()?) you'll have to declare it: myInt ALIAS FOR $1; or use it explicitly with just the name: $1 Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] composite type and assignment in plpgsql
Ivan Sergio Borgonovo wrote: On Tue, 27 Apr 2004 10:12:13 -0700 thisSession := ( ''t'', md5( now( ) || rand( ) ) ); - md5 takes TEXT as an argument, not a numeric type Since it works you surely fixed my code but this should't be an issue since I tried test1=# select md5( now( ) || random( ) ); md5 -- 154e804967451148bba5f28e044be828 (1 row) and test1=# select md5( random( ) ); md5 -- 31313f537b69d5ffe61be024a40b807e (1 row) and they worked. Yeah, they worked for me too. I was just looking at the docs and saw the TEXT argument. and yeah I messed up remembering mySQL code and wrote rand( ) inspite of random( ) Can't user composite type be initialized in a shortest way? eg. ( ( ), ( ), , ( ), , , ( ), ...) I thought they could. I saw a similar syntax somewhere in the docs. Am I daydreaming? I don't know. One more thing about the first example presented in this page: http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html I just tried create or replace function GetSessionID( integer ) returns tSession as ' declare thisSession tSession; begin thisSession.ty_Found := ''t''; thisSession.ty_Session := now( ); return thisSession; end; ' language plpgsql; and it returns execution time not plan time. Does plan time is strictly referred to SQL statements? I'm not sure I understand what you're asking here. CURRENT_TIMESTAMP and now() return the start time of the current transaction, would that be the plan time? The timeofday() function returns the wall clock time and advances during transactions. I think that this would be the execution time. Hope that helps Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Suppress output from function?
Whenever I run certain functions, such as the example below, the output is either displayed in the terminal or emailed to be by cron, depending on how I run it. Is there any way I can re-write the function, set some psql parameters, etc, to ensure that the results aren't displayed? I've checked some of the postgresql.conf logging properties and running psql with quiet = true, but all to no avail. Anyone have any suggestions, hints, ? CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum NUMERIC); CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF employeeType AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT empID, updateDate, bDate, val1, val2 , val3, val4, favNum FROM newData LOOP RETURN NEXT rec; UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate WHERE empID=rec.empID; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; SELECT * FROM updateEmployeeData(); Thanks Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] \s paging?
CSN wrote: Is it possible to have less-type paging with psql's \s command? Or other ways like display the last 50 commands backwards? __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Since no one has had a go at this, I'll try. The docs say that it is no longer necessary to use the \s command because all commands are saved on program termination. However, if you want to read/cycle through previous commands while the app is still running you have to configure postres with readline. Then you can just use your up/down arrows on your keyboard to display previous commands. Also available in the few linux distros I've used is the shift-pgUp/shift-pgDwn functionality in a terminal to view input and output that's already scrolled off of the screen. HTH Ron ---(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] Setting up Postgresql on Linux
Phil Campaigne wrote: Hi Ron, I had a couple of questions on your instructions: 1. what is this for? #make install-all-headers According to the docs you need it if you are going to create your own functions, however the documentation is a bit *vague*. If you plan to do any server-side program development (such as custom functions or data types written in C), then you may want to install the entire PostgreSQL include tree into your target include directory. I am working under the assumption that custom functions include any functions you create in plpgsql, sql, c, etc and not just C functions. I would recommend using this if you will be working with the database a lot, because you will probably eventually want to create some of your own functions. 2. is it advisable for me to use UNICODE for defalt encoding? initdb -E UNICODE -D /usr/local/psql/data Some of our databases were originally encoded using SQL-ASCII and we still occasionally have problems when someone enters a character with an accent (eg in cafe). The jdbc driver won't convert it and the app 'breaks' wrt that data set. So I would recommend that you UNICODE, I don't believe that it really adds much in the way of overhead, etc. 3. My version of postgresql's linux script file instructs me to create symbolic links to: /etc/rc.d/rc2.d/K02postgresql /etc/rc.d/rc3.d/S98postgresql However the init script file for starting up the PostgreSQL server that I have is K15postgresql. Is it ok that I substitute a link to K15postgresql for rc2.d and rc3.d? I suppose that's okay. My very limited understanding of these directories is that they are used during startup/shutdown to ensure that services are started and stopped correctly and in the proper order. thanks, Phil Please respond to the list for more questions and cc me (or vice versa) if you have more questions. This way if anyone else is in a similar predicament they can read this, and others can contribute and/or correct me. Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting up Postgresql on Linux
Phil Campaigne wrote: Hello, I originally installed postgresql as root user and now I am setting up a development environment with cvs and a java ide and tomcat. I have everything with the exception of postgresql integreted using a non-root user. THe process I am using is to logon as postges and start the database and do queries from the command line using psql. Then I logoff and logon as phil and start tomcat and my java ide. 1.Is there a better way to start the database and the web application? 2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql directoy tree to postgres? 3.However my development environment(i.e. jdbc connection from my application) doesn't seem to have permission to access to all the files it needs in postgres? 4. Is is best to install postgresql as user phil in order to gain access for my integrated develoment environment? thanks, Phil ---(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 For aditional reference you can check out the docs at http://www.postgresql.org/docs/7.4/static/postmaster-start.html especially section 16.3 as it pertains to linux (assuming that's your os). Below are some of the steps I use when I install postgres on a new machine, check out steps 10 and 11 specifically: my instructions 8. Install: # cd /usr/local/postgresql-7.x # make install # make install-all-headers # cd /usr/local # chown -R postgres.postgres pgsql 9. Initialize the database as user postgres: # su - postgres # initdb -E UNICODE -D /usr/local/pgsql/data 10. Configure (as root) the PostgreSQL SysV Script. This script is useful for starting, stopping, and checking the status of PostgreSQL. # cd /usr/local/src/postgresql-7.x # cp contrib/start-scripts/linux /etc/init.d/postgres # chmod 755 /etc/init.d/postgres To have PostgreSQL start automatically when the computer boots add symbolic links from the correct /etc/rc*.d/ directories to /etc/init.d/postgres. If the normal runlevel is 3 then you really only need to add it to rc3.d: # ln -s /etc/init.d/postgres /etc/rc2.d/S85postgres # ln -s /etc/init.d/postgres /etc/rc3.d/S85postgres etc ... 11. Start PostgreSQL for the first time: # /etc/init.d/postgres start 12. Create a new db: # su - postgres $ createdb testdb $ psql testdb \ my instructions Ron *Note:* these are taken from the book Practical Postgres by Worsley and Drake and are slightly(? can't remember, it's been so long since I made the list) modified. ---(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] How to drop sequence?
Igor Kryltsov wrote:
Hi,
I have table:
# \d category;
category_id | integer| not null default
nextval('public.category_category_id_seq'::text)
category_name | character varying(100) | not null
Indexes: category_pkey primary key btree (category_id)
My goal is to remove sequence from category_id column and remove it after
from DB.
First I tried:
DROP SEQUENCE category_category_id_seq - fails saying that table category
column category_id uses it
Than I tried:
ALTER TABLE category ALTER COLUMN category_id DROP DEFAULT;
Now category_id column is shown as integer not null only but :
DROP SEQUENCE category_category_id_seq - fails saying that table category
column category_id uses it again
Any suggestions?
Thank you,
Igor
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Try
DROP SEQUENCE category_category_id_seq CASCADE;
Ron
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] change db encoding
Alexander Cohen wrote: How would i go about changing a databases encoding? Is this at all possible? There does not seem to be much i can with ALTER DATABASE except change its name! You could try to: -pg_dump the database to file, -drop the database, -recreate the database with unicode encoding (createdb mynewdb -e unicode), -check the dumped file for any 'strange' characters and change as needed, -restore to the new database PS I will have to do this soon to several databases. If you do convert yours, please post a follow-up message on this thread outlining your experience. Here are the steps from a previous thread for a conversion to a different language (I couldn't find the link so I'm pasting it into this message): Re: [GENERAL] Converting SQL-ASCII encoding database to UNICODE Jean-Michel POURE wrote: Le Dimanche 9 Novembre 2003 19:39, Rajesh Kumar Mallah a écrit : If so what is the process The advantage of using a Unicode database is that UTF-8 supports/includes all known encodings at once. Therefore, in the process of development, it can help you save time. When using a Unicode database, if the client does not support Unicode, it is always possible to recode data streams on the fly with set client_encoding = foo_encoding. Therefore, there is no real drawback in using Unicode. It may only be a little slower, but there is no real proof. The process of conversion is as follows: - dump the whole database using pg_dump: pg_dump database_name pg_data_dump.sql - Do no drop the old database. Make a backup of the dump: cp pg_data_dump.sql pg_data_dump.sql.bak - recode the dump using the GNU recode package: recode ..u8 pg_data_dump.sql recode will try to guess the encoding the original ASCII database. - Open the file in an UTF-8 editor like Kate and verify that all characters are preserved and display well (in Hindi?). If it does not work, it may be a problem of original encoding. Try to guess it and retry using: cp -f source_encoding..u8.bak source_encoding..u8 recode source_encoding..u8 pg_data_dump.sql - create an empty Unicode database: psql template1 create database new_database with encoding=Unicode; reload the dump: psql new_database pg_data_dump.sql GNU/recode is available in most GNU/Linux distributions. By the way, as you live in India, do not hesitate to visit http://pgadmin.postgresql.org/pgadmin3/translation.php if you can help us. Most translations in languages for India are stalled, do not hesitate to take over the translation in Hindi for example. Cheers, Jean-Michel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org \ hth Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] resource monitoring
Rick Gigger wrote: I am running a few web based applications with postgres on the backend. We have a few app servers load balanced all connecting to a dedicated postgres server. As usage on the applications increases I want to monitor my resources so that I can anticipate when I will hit bottlenecks on the db server. That way we can do upgrades or optimizations before our performance get's unacceptable. We are monitoring cpu usage, memory usage, and network traffic. I would also like to monitor io utilization but am not quite sure how to do that. Does anyone here know to effetively monitor io to the raid (I'm guessing that this could be measured with the raid management software but was hoping their were some standard system commands.) or other io that I should be measuring? What bottlenecks could I hit and what are some good stats to check to anticipate when I am moving towards one of those bottlenecks. Thanks, Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html I use top to measure cpu and memory usage, vmstat to measure disk io, and pg_stat_activity to see which query is hogging the cpu, memory or io. We don't use raid so I'm not sure how vmstat deals with that. So your mileage may vary :-) Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How to determine current database?
I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. Is there a query I can use along the lines of: UPDATE tblUpdates SET xxx=1234 WHERE pg_current = TRUE; or UPDATE tblUpdates SET xxx=1234 WHERE pg_current = thisDBname; We have about 15 databases all set up identically and when the structure changes I run scripts to update them to ensure that they are all the same. I need to create two new databases which have slight changes (different default values - for now) and want to be able to have my scripts be able to determine which database their running from. Thanks Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to determine current database?
Eric Ridge wrote: On Feb 13, 2004, at 6:05 PM, Ron St-Pierre wrote: I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. Is there a query I can use along the lines of: The built-in function current_database() returns the current database name. =# select current_database(); current_database -- testing (1 row) Use it in your update statements too. eric That's exactly what I need! Joe Conway wrote: See: http://www.postgresql.org/docs/7.3/static/functions-misc.html Thanks guys. I was searching the docs looking for functions starting with pg_ Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause
ezra epstein wrote: Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function test gets this: psql:temp3.sql:10: ERROR: syntax error at or near % at character 135 the function test2 gets this: psql:temp3.sql:10: ERROR: syntax error at or near ROWTYPE at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? And when we schema qualify the name of the table then the % is ok, but ROWTYPE is not. Is this a well-known limitation or a new (7.4) bug? I tried combing the docs to no avail. Thanks, Ezra E. code /* CREATE TABLE doof ( pk_id serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Try replacing the rowtype with SETOF doof: CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS SETOF doof AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; Hope that helps. Ron ---(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] COPY - Ignore Problems
I wish to insert data into a table from a very large text file (from a cron script) using COPY. However if the lName (TEXT), fName(TEXT), workDate(DATE) already exist I don't want to insert data and just want to move onto the next record. Is there any way I can tell my bash script/COPY to ignore the case where the unique constraint exists (lName,fName,workDate), and move on to the next record? ps 7.4, debian stable TIA Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Function Returning SETOF Problem
On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function updatecurrentcata line 6 at return next I've googled and tried variations on the function, but without success. Can anyone help? Here's the function: CREATE TYPE place_finish AS (first NUMERIC, second NUMERIC, third NUMERIC, grandttl INTEGER, lname TEXT, fname TEXT); CREATE OR REPLACE FUNCTION updateCurrentData() RETURNS SETOF place_finish AS ' DECLARE rec RECORD; updstmt TEXT; BEGIN FOR rec IN SELECT first, second, third, grandttl, lname, fname FROM dailyList LOOP RETURN NEXT rec; updstmt := ''UPDATE currentList SET first=rec.first, second=rec.second, third=rec.third, grandttl=rec.grandttl, lname=rec.lname, fname=rec.fname WHERE lname=rec.lname AND fname=rec.fname;''; EXECUTE updstmt; END LOOP; RETURN 1; END; ' LANGUAGE 'plpgsql'; Thanks Ron ps postgres 7.4, debian stable ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Function Returning SETOF Problem
Stephan Szabo wrote: On Wed, 17 Dec 2003, Ron St-Pierre wrote: On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function updatecurrentcata line 6 at return next I've googled and tried variations on the function, but without success. Can anyone help? This probably means that you're calling it like: select updateCurrentData(); and you'll need to instead call it with the function in the FROM clause, something like: select * from updateCurrentData(); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] aha, that's part of it. I now get this error: ERROR: wrong record type supplied in RETURN NEXT Any ideas on this one? TIA Ron ---(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] Constraint Problem
Greg Stark wrote: Ron St-Pierre [EMAIL PROTECTED] writes: This is not quite what I need. I need to create a constraint to allow only -one- of company-association-default(=true) value but any number of company-association-default(=false) values So a unique index on (company,association) where default doesn't do what you want? No it doesn't. For example, after I create the unique index I can still input: company10 association7 true company10 association7 true company10 association7 true I want to prevent this from happening, but still allow multiple company10 association7 false company10 association7 false entries for example. The idea of using NULLs is a good idea, but this is a production database and would require changes to the web-based front end (7 of them), not to mention each database. That's why I want to change the behaviour to only allow one unique company--association--TRUE combination. Right now there are a number of companies which have multiple default associations in the database, so I am going to have to back-trace and find out which association is actally the correct default. BTW I am using postgresql 7.3.4 Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Constraint Problem
I want to create a constraint that's a little different, but don't know how to implement it. I have an intermediate table with a compound PK and a boolean 'ysnDefault' column: comanyID INTEGER REFERENCES companies, assocationID INTEGER REFERENCES associations, ysnDefault BOOLEAN I just want to constrain the ysnDefault field to only allow one TRUE value for any companyID/associationID pair, with no restrictions on the number of FALSES. At first glance I thought it would be easy, but I can't see how to do it. Any suggestions? Thanks Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Constraint Problem
Tom Lane wrote: Ron St-Pierre [EMAIL PROTECTED] writes: I just want to constrain the ysnDefault field to only allow one TRUE value for any companyID/associationID pair, with no restrictions on the number of FALSES. You could do that with a partial unique index. There is an example at the bottom of http://www.postgresql.org/docs/7.3/static/indexes-partial.html regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Thanks Tom. That's exactly what I need. Ron ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
