Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Chris (and others), thank you for the good explanation! =) I will try to use database as you recommend, thank you again for the advices! Regards, /Alexandre. On Apr 20, 2005, at 17:39, Chris Browne wrote: I think you're missing two points: 1. With careful design, the ISAM wrapper can _avoid_ most of the costs you suggest. For instance, one might set up a prepared query which would only parse, plan, and compile the query _once_. Further, I'd expect that most of the behaviour could be hidden in stored procedures which would further hide the need to parse, plan, and compile things. The ISAM-congruent abstraction would presumably make it easier to use, to boot. 2. Dan Sugalski indicated that he actually found the overhead to be ignorable. As a datapoint, that's pretty useful. He actually went thru the effort of building the ISAM wrapper, and discovered that the overhead wasn't material. You ought to consider the possibility that perhaps he is right, and that perhaps you are trying to optimize something that does not need to be optimized. Remember Michael Jackson's _First Rule of Software Optimization_, which is expressed in one word: Don't. (And then there's his second rule, for experts: Don't do it yet.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] electronic-izing unicode texts
A. Cropi wrote: my objectives: (1) allow users to make query through the web (i guess i will do this via PHP interacting with the postgresql) my questions are: (1) is it reasonable to put the bookcontent into the CONTENT column? (2) the content of the book can be very long (some of them have nearly 1 milloin words), so, what kind of considerations should i be making? (3) how should i design something like this? there must be someone outthere that has done somethign similar to this.. if so, please share your experiences. You might be better off with a web-indexing package. http://freshmeat.net/search/?q=web+indexingsection=projects Since you're not structuring the content of the book, most of the advantages of a RDBMS don't apply. If you're going to treat it as text, just use one of the text indexing systems above. I would convert each book into one or more web-pages (perhaps one page per section/chapter) and then use htdig or swish. -- Richard Huxton Archonet Ltd ---(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] Windows install/uninstall as a service
[EMAIL PROTECTED] schrieb: When I did the windows install (NOT as a windows service) but as a task to be manually started and stopped, it did not creat a DATA directory, nor did it create a postgresql.conf file. I even used windows explorer search to confirm that no such file exists. I did the uninstall and then reinstalled several times, to see if there was any different choice I could make. My next step would be to configure it as a Windows service. I am just concerned whether the uninstall of such a windows service would be a clean uninstall. I would appreciate any advice on how to get this postgresql windows install working on my Dell with XP Professional OS. I feel that IF I could achieve an install which actually creates a DATA folder, and a posgresql.conf file, that I could then attempt to add the listening command. Thanks in advance for any advice or suggestions. It seems that you have installed only binarys. So you have to initialize your databasecluster with initdb. Take a look at initdb --help. This will create both, Datadirectory and postgresql.conf files. Daniel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Functions returning RECORD
I have a question about syntax and feasibility. I do the following: CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my RECORD definition? Is it possible at all? Ideally I'd like to write something like this: SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc; but that yields a syntax error. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Raise exception from regular SQL?
I'm trying to add some clarity to the rules I have on my tables. When a rule is broken, I want to give a more informative message, so that the client side can deal with it in a nicer manner. Right now I'm creating plpgsql functions, and raising an exception in there with the customized text. Is there a way to raise exceptions with regular SQL so that I don't have to go to plpgsql? Thanks, Steve ---(end of broadcast)--- TIP 8: explain analyze is your friend
(Fwd) Re: [GENERAL] windows, cant find ostgresql.conf
On 21 Apr 2005 at 0:50, [EMAIL PROTECTED] wrote: I just did a windows install of postgresql. I cannot find or edit the postgresql.conf. This is a new Dell machine running XP Professional I'm running it on my Dell laptop with XP Professional, and it's in C:\Program Files\PostgreSQL\8.0\data - IIRC, this is the default installation directory. OS. I chose not to install as a windows service, but as a job which I can start and stop. Where should I look for help. Is it easy to It's very easy to uninstall - use the uninstaller in Control Panel - Add or remove programs, and it does it all itself. The uninstaller does (sensibly) leave the data directory behind, so if you want to lose that too you have to delete it by hand. uninstall? Should I uninstall and then reinstall as a windows service? Depends on what you want to do. If you usually log in as a privileged user, you can't start or stop the Postmaster manually - it won't run from a privileged account - so in this case it might make sense to install it as a service which you can then start or stop using the shortcuts which the installer sets up for you. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] transaction started without BEGIN??? Please help!
Hi! I have a huge problem here. On a busy server (a web system, mainly java servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden, one of the postgres processes gets into the state idle in transation (revealed by `ps axU pgsql'). We use transaction very sparesly, so this is a mystery to me. I've swithed on logging of all statements, and also added the transaction id. part of the log is attached. As you can see, the transaction id is incremented, for every query, until suddenly, it is not incremented anymore, but stays the same. It seems to me, from lookin at the logs, that postgres enters a transaction although no BEGIN statement was issued? I hope I'm doing a misinterpretation of the logs here? So, in a couple of minutes, all connections are taken and a hundred postgres procs sit here waiting for the transaction to finish so they can issue their inserts. First, there shouldn't be a transaction here at all, how come it starts? And how come there's no BEGIN in the log, if one is actually started? My solution when this happens (has happened a couple of times a day on a busy server) is to kill the process that is reported idle in transaction. That way we loose data, I'd rather get it to commit. Is there a way to do that? Here are all the logging directives in postgresql.conf: log_destination = 'syslog' silent_mode = true log_line_prefix = '%x' log_statement = 'all' The attached log was grepped on the problematic PID, btw. postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 The icu patch is applied (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how that would interfere with transaction handling? Thanks, Palle Apr 21 09:50:23 postgres[22588]: [15213-8] (nfold.node_id = n.node_id) left outer join node_item ni on (ni.node_id = n.node_id) left outer join node_portfolio np on Apr 21 09:50:23 postgres[22588]: [15213-9] (np.node_id = n.node_id) left outer join node_objective nop on (nop.node_id = np.objective_id) left outer join component nopc Apr 21 09:50:23 postgres[22588]: [15213-10] on (nopc.id = nop.objective_type_id) where nc.node_id = $1 order by nc.seq Apr 21 09:50:23 postgres[22588]: [15214-1] 14243871LOG: statement: select p.meta_name, p.meta_info, h.start_file_id, h.use_pp_css, p.htype, p.scorm_type, p.derived_from Apr 21 09:50:23 postgres[22588]: [15214-2] from part p left outer join html_part h on (p.id = h.part_id) where p.id = $1 Apr 21 09:50:23 postgres[22588]: [15215-1] 14243872LOG: statement: select pq.q_submit_id, qs.max_time, qs.lock_other_content from part_quiz pq join q_submit qs on Apr 21 09:50:23 postgres[22588]: [15215-2] (qs.id = pq.q_submit_id) where pq.part_id = $1 Apr 21 09:50:23 postgres[22588]: [15216-1] 14243873LOG: statement: select count(*) from discuss_mail_queue Apr 21 09:50:23 postgres[22588]: [15217-1] 14243873LOG: statement: select qsc.q_submit_id, qs.lock_other_content from q_submit_comp qsc join published_part_components Apr 21 09:50:23 postgres[22588]: [15217-2] ppc on (qsc.q_comp_id = ppc.component_id) join q_submit qs on (qs.id = qsc.q_submit_id) where ppc.part_id = $1 and qs.max_time Apr 21 09:50:23 postgres[22588]: [15217-3] is not null limit 1 Apr 21 09:50:23 postgres[22588]: [15218-1] 14243873LOG: statement: select distinct p.email, p.default_language, ds.name, m.heading, m.content, m.created, Apr 21 09:50:23 postgres[22588]: [15218-2] cp.discuss_mail_only_info, c.meta_name, m.creator_name from person p join discuss_subject_subscribe dss on (dss.userid = Apr 21 09:50:23 postgres[22588]: [15218-3] p.userid) join discuss_subject ds on (ds.subject_id = dss.subject_id) join discuss_mail_queue dmq on (dmq.subject_id = Apr 21 09:50:23 postgres[22588]: [15218-4] ds.subject_id) join discuss_course dc on (dc.subject_id = ds.subject_id) join course c on (c.id = dc.course_id) join Apr 21 09:50:23 postgres[22588]: [15218-5] course_prefs cp on (cp.course_id = c.id) join message m on (m.message_id = dmq.message_id) left outer join message_read mr on Apr 21 09:50:23 postgres[22588]: [15218-6] (mr.message_id = m.message_id and mr.userid = p.userid) join group_data gd on (gd.this_group_id = dc.group_id and gd.item_text Apr 21 09:50:23 postgres[22588]: [15218-7] = p.userid) where mr.userid is null union select p.email, p.default_language, ds.name, m.heading, m.content, m.created, Apr 21 09:50:23 postgres[22588]: [15218-8] cp.discuss_mail_only_info, c.meta_name, m.creator_name from person p join discuss_subject_subscribe dss on (dss.userid = Apr 21 09:50:23 postgres[22588]: [15218-9] p.userid) join discuss_subject ds on (ds.subject_id = dss.subject_id) join discuss_mail_queue dmq on (dmq.subject_id = Apr 21 09:50:23 postgres[22588]: [15218-10] ds.subject_id) join discuss_group dg on (dg.subject_id = ds.subject_id) join group_group gg on (gg.group_id = dg.group_id) join Apr 21 09:50:23 postgres[22588]: [15218-11] course c on (c.groups_group_id =
Re: [GENERAL] Filesystem options for storing pg_data
[I've got a private reply from Scott, which I won't quote here, which can be fairly (I hope) summarized as search the pgsql-performance list. Well, I've done it, and I feel it's due to bring the issue back in public. So if I seems I'm replying to myself, it's not, I'm replying to Scott. I've realized the reply was private only just before sending this out.] On Wed, 2005-04-20 at 12:07, Marco Colombo wrote: On Wed, 2005-04-20 at 11:18 -0500, Scott Marlowe wrote: Generally XFS and JFS are considered superior to ext2/3. Do you mind posting a reference? I'm really interested in the comparison but everytime I asked for a pointer, I got no valid resource, so far. [...] Well, my point being the ones I find lead to the conclusion that EXT3 is considered superior to XFS and JFS. One for all: http://www.oracle.com/technology/oramag/webcolumns/2002/techarticles/scalzo_linux02.html It's reassuring when various industry-standard benchmarks yield similar results. In case you're wondering, I obtained similar results with Benchmark Factory's other half dozen or so database benchmarks-so for me, it'll be ext3. Have a look at the graphs, EXT3 is almost twice as fast in these (database) benchmarks. Another one is: http://www.kerneltraffic.org/kernel-traffic/kt20020401_160.html#8 Again ext3 is the winner (among journalled fs), but by a small edge only. And again, there are a lot of variables. Using for example data=journal with a big journal file on a different disk would be extremely interesting, just as using a different disk for WALs is at PostgreSQL level (the result might be the same). All the other benchmarks I've found, with a simple search for 'filesystem benchmark' on the pgsql-performance list, either are the usual Bonnie/iozone irrelevant benchmarks, or don't seem to care to tune ext3 mount options and use the defaults (thus comparing apples to oranges). I'm not stating that EXT3 is better. My opinion on the matter is that you shouldn't care about the filesystem much (EXT3, JFS, XFS being the same for _most_ purposes with PostgreSQL). That is, it's a small little spot in the big picture of performance tuning. You'd better look at the big picture. I'm only countering your claim: Generally XFS and JFS are considered superior to ext2/3. There's no general agreement on the lists about that, so just handwaving and saying look at the lists isn't enough. Mind posting a pointer to _any_ serious PostegreSQL (or any database, at least) based benchmark that consistently shows XFS and JFS as superior? One that cares to show ext3/noatime/data=ordered,data=writeback,data=journal results, too? If I were to choose based on the results posted on the list (that I've managed to find), ext3 would be the winner. Maybe I've missed something. Having used ext3 quite a bit, I'd say it's fairly stable and reliable, but I have seen references here to know, possibly unfixable bugs. Again, mind posting a reference? [...] I've searched for 'EXT3 bug' but got nothing. I'm (loosely) following l-k, and never heard of possibly unfixable bugs in EXT3 by any developer. Care to post any real reference? There have been bugs of course, but that holds true for everything, XFS and JFS included. Having re-read many many messages right now, I'm under a even stronger impression that _all_ negative comments on both the stability and the performance of EXT3 start with I've heard that... w/o almost noone providing direct experience. Many comments display little understanding of the subject: some don't know about data= mount option (there's little point in comparing to XFS, if you don't use data=writeback), some have misconceptions about what the option does, and what difference it makes when the application keeps _syncing_ the files (I don't know well either). See the data=journal case. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] windows, cant find ostgresql.conf
On Apr 21, 2005, at 12:50 AM, [EMAIL PROTECTED] wrote: I just did a windows install of postgresql. I cannot find or edit the postgresql.conf. This is a new Dell machine running XP Professional OS. I chose not to install as a windows service, but as a job which I can start and stop. Where should I look for help. I have not installed without the service, but on my system there is a link to the postgresql.conf file right in the Start menu program group for PostgreSQL. Is it easy to uninstall? Yes, just go to the Add or Remove programs control panel. Should I uninstall and then reinstall as a windows service? Yes, it will likely make your life easier. If I install as a windows service, is that reversable, or might it permanently change my computer in some undesirable fashion. The uninstall process is designed to reverse everything done by the installer. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] Regular expression. How to disable ALL meta-character
Thanks for your help!
Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they form non valid
expression)
1) Using like: select * from mytable where lower(mycol) LIKE lower(%
|| lower(X) || %);
Mostly perfect solution. Don't crash but % still have a special
meaning. Wich means anything
2)Using regular expression: select * from mytable where mycol ~*
('***=' || X)
For the test I did it doesn't, fit all my need. No meta character and
no escaping to do on X before launching the SQL request.
Thanks for your help!!!
Have a great day
/David
Chris Travers wrote:
David Gagnon wrote:
Maybe there is a simple way to to this but I want find string X in
different column. The search must not be case sensitive.
So that searching aBc in abcDef return true. I don't want
META-CHaracter. Or at least I don't want meta-character to cause
errors (i.e.: No
Ok, how about a better way to do this?
select * from mytable where lower(mycol) LIKE lower(% || lower(X) ||
%);
Does this work? It seems that this may be the best way to handle this
sort of thing.
Best Wishes,
Chris Travers
Metatron Technology Consulting
ERROR: invalid regular expression: brackets [] not balanced.
Thanks for your help
/David
I found this in the manual .. but haven't found example :-(:
: with ***=, the rest of the RE is taken to be a literal
string, with all characters considered ordinary characters.
Read again the entire sentence, especially the first few words:
If an RE begins with ***=, the rest of the RE is taken to be a
literal string, with all characters considered ordinary characters.
Here are some examples:
SELECT 'test string' ~ 'test[*';
ERROR: invalid regular expression: brackets [] not balanced
SELECT 'test string' ~ '***=test[*';
?column? --
f
(1 row)
SELECT 'test[* string' ~ '***=test[*';
?column? --
t
(1 row)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Filesystem options for storing pg_data
On 4/21/05, Marco Colombo [EMAIL PROTECTED] wrote: Generally XFS and JFS are considered superior to ext2/3. Do you mind posting a reference? I'm really interested in the comparison but everytime I asked for a pointer, I got no valid resource, so far. Well, my point being the ones I find lead to the conclusion that EXT3 is considered superior to XFS and JFS. One for all: First of all, my workload is not IO bound, so don't consider what I write as solutions for IO heavy setups. Personally I use ext3 (with ~128 KB per inode ratio, to save some space and keep inodes more closely together), with noatime option. I've tried JFS some time ago and got away from it soon after. The reasons were that: 1. JFS dynamic inode allocation left less free space for apps than ext3 (I usually decrease inode ratio to some reasonable limit (like 4 times current ratio for given directory set)). (Yeah, not a serious issue, yet I admit I tend to consider it). 2. FSCK. Back then JFS had an ugly feature of mounting only 'clean' filesystems, i.e. fsck had to be done in userspace (unlike ext3 which does it as a part of mount process). I don't know if it is still that way. 3. Performance. For my workload, mostly single threaded and bursty, ext3 appeared a bit faster. Yet it was a good while ago, JFS might have changed a good bit since then. I have no experience with XFS, but I've heard a lot of good about it. Again ext3 is the winner (among journalled fs), but by a small edge only. And again, there are a lot of variables. Using for example data=journal with a big journal file on a different disk would be extremely interesting, just as using a different disk for WALs is at PostgreSQL level (the result might be the same). Some time ago I thought it could be nice thought experiment to 'tune' ext3 for PostgreSQL needs. (Mark WAL files for immediate updates, journal other updates (filesize changes, creations etc), and keep journal close to WAL files... ;) I'm not stating that EXT3 is better. My opinion on the matter is that you shouldn't care about the filesystem much (EXT3, JFS, XFS being the same for _most_ purposes with PostgreSQL). That is, it's a small little spot in the big picture of performance tuning. You'd better look at the big picture. I'm only countering your claim: Generally XFS and JFS are considered superior to ext2/3. You can certainly say that XFS/JFS are more complex and were engineered to better handle high work load. Ext3 is relatively simple; and its simplicity may also be a big advantage when handling high load. Summary: I'm not arguing JFS/XFS are worser/same. All I want to say is that ext3 is a decent filesystem. Ext3's greatest advantage, I guess, is the ease of deployment -- it comes out of the box with most distributions. With a little tuning it can perform reasonably well for most needs. Regards, Dawid ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Try SELECT timeofday()::TIMESTAMP; Regards, Ben Christopher J. Bottaro [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? Is it possible to create a column with DEFAULT value evaluated to the actual current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the current transaction). What I do now to get it to work is do a COMMIT right before the insert, that way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that is so crappy and doesn't work if I actually need to use transactional features (i.e. rollback). Thanks for the help, -- C ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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
[GENERAL] PLEASE HELP ME
Hello sir
actually i am working with Qt using postgresql i have to get connection
{(database(trainee),user(trainee))}
from the network (database remote accessing) so i have configured
postgresql.conf and pg_hba.conf upto some extents
the fields that i have configured r as follows even then
while connecting by giving any address (even localhost) it flashes error
no pg_hba.conf entry for host 127.0.0.1 usertrainee database trainee
SSL off
QPSQL:Unable to connect
in pg_hba.conf
# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)
local all all ident sameuser
#local all all 127.0.0.1 sameuser
only this entry i have made
in postgresql.conf
# - Connection Settings -
tcpip_socket = true
max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = '/home/trainee/trainee'
#unix_socket_group = 'trainee'
#authentication_timeout = 60 # 1-600, in seconds
#ssl =true
if i do ssl true active then even ir is not giving psql prompt
please help me i am in trouble
[GENERAL] List of Functions
I have no idea if this is the correct list to use. How do you obtain a list of the functions and their argument lists that have been created on a Postgres database? I have tried searching various lists and archives plus the documentation. All I want to find is a list of:- Function name return valuearguments. After you run the create or replace script, this data has to be saved somewhere. Just the name of the table would do. Thanks, Robert Stone CONFIDENTIALITY NOTICE AND DISCLAIMER - The information in this transmission may be confidential and/or protected by legal privilege, and is intended only for the person or persons to whom it is addressed. If you are NOT such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received this transmission in error, please advise Park Lane Information Technology -- +61 (03) 9813 1000 -- and permanently delete all copies of this transmission from your mail server(s), mail client(s) and from any physical records. Park Lane Information Technology has no liability (including liability in negligence) for any unauthorised use of the information contained in this transmission. ---(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] Regular expression. How to disable ALL meta-character
David Gagnon wrote: Maybe there is a simple way to to this but I want find string X in different column. The search must not be case sensitive. So that searching aBc in abcDef return true. I don't want META-CHaracter. Or at least I don't want meta-character to cause errors (i.e.: No Ok, how about a better way to do this? select * from mytable where lower(mycol) LIKE lower(% || lower(X) || %); Does this work? It seems that this may be the best way to handle this sort of thing. Best Wishes, Chris Travers Metatron Technology Consulting ERROR: invalid regular expression: brackets [] not balanced. Thanks for your help /David I found this in the manual .. but haven't found example :-(: : with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters. Read again the entire sentence, especially the first few words: If an RE begins with ***=, the rest of the RE is taken to be a literal string, with all characters considered ordinary characters. Here are some examples: SELECT 'test string' ~ 'test[*'; ERROR: invalid regular expression: brackets [] not balanced SELECT 'test string' ~ '***=test[*'; ?column? -- f (1 row) SELECT 'test[* string' ~ '***=test[*'; ?column? -- t (1 row) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] CURRENT_TIMESTAMP and actual time
Hi, I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the _actual_ time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? Is it possible to create a column with DEFAULT value evaluated to the actual current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the current transaction). What I do now to get it to work is do a COMMIT right before the insert, that way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that is so crappy and doesn't work if I actually need to use transactional features (i.e. rollback). Thanks for the help, -- C ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Binary or compiled version?
I haven't had any joy trying to install the Redhat RPMs on mandrake 10.1. It might be me but I did take some time trying. I also tried using the SRPMs and building my own but that didn't work either. Since then I've compiled my version and it works great. The only thing I needed to do was mess around with the startup scripts a bit. Regards, Ben Jaqui Greenlees [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Alejandro D. Burne wrote: Hi, I'm installing 8.0.2 on Mandrake and I saw binarys rpms only for RH. Can someone tell me if is better install a rpm version or compile from source in this case? Thanks, Alejandro the rpm should work fine, even though it's rh. if not, rpmdrake ( urpmi ) will remove 8.0.2 so building from sources with clean system will be possible. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Use of temporary tables in functions
Hi, I had some issues with previous versions of Postgresql using temporary tables in functions. The main issue was that the both indexes pg_attribute_relid_attnam_index and pg_attribute_relid_attnum_index were always growing and I had to restart postgresql in single mode and run a REINDEX command to free some space on the disk. Does the problem still exist in version 8 ? Thanks for your answers. Patrick --- Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 18 ---
Re: [GENERAL] List of Functions
\df \df functionname Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Stone Sent: Thursday, April 21, 2005 9:48 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] List of Functions I have no idea if this is the correct list to use. How do you obtain a list of the functions and their argument lists that have been created on a Postgres database? I have tried searching various lists and archives plus the documentation. All I want to find is a list of:- Function name return valuearguments. After you run the create or replace script, this data has to be saved somewhere. Just the name of the table would do. Thanks, Robert Stone CONFIDENTIALITY NOTICE AND DISCLAIMER - The information in this transmission may be confidential and/or protected by legal privilege, and is intended only for the person or persons to whom it is addressed. If you are NOT such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received this transmission in error, please advise Park Lane Information Technology -- +61 (03) 9813 1000 -- and permanently delete all copies of this transmission from your mail server(s), mail client(s) and from any physical records. Park Lane Information Technology has no liability (including liability in negligence) for any unauthorised use of the information contained in this transmission. ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] transaction started without BEGIN??? Please help!
One thing I can think of is some kind of inadvertent SQL injection. Somebody put ; begin; into a string that got sent to the server unescaped? The only other thing I can think of is if there was a hole in the logic in one of your explicitly started transactions that allowed the handle to be returned to the pool with a transaction open. I don't know anything about java or tomcat, but it should be possible. Good luck. On 4/21/05, Palle Girgensohn [EMAIL PROTECTED] wrote: Hi! I have a huge problem here. On a busy server (a web system, mainly java servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden, one of the postgres processes gets into the state idle in transation (revealed by `ps axU pgsql'). We use transaction very sparesly, so this is a mystery to me. I've swithed on logging of all statements, and also added the transaction id. part of the log is attached. As you can see, the transaction id is incremented, for every query, until suddenly, it is not incremented anymore, but stays the same. It seems to me, from lookin at the logs, that postgres enters a transaction although no BEGIN statement was issued? I hope I'm doing a misinterpretation of the logs here? So, in a couple of minutes, all connections are taken and a hundred postgres procs sit here waiting for the transaction to finish so they can issue their inserts. First, there shouldn't be a transaction here at all, how come it starts? And how come there's no BEGIN in the log, if one is actually started? My solution when this happens (has happened a couple of times a day on a busy server) is to kill the process that is reported idle in transaction. That way we loose data, I'd rather get it to commit. Is there a way to do that? Here are all the logging directives in postgresql.conf: log_destination = 'syslog' silent_mode = true log_line_prefix = '%x' log_statement = 'all' The attached log was grepped on the problematic PID, btw. postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 The icu patch is applied (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how that would interfere with transaction handling? Thanks, Palle ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Suggestions
Hi We are likely to build a desktop application in VB.Net and we are in a hunt to go for a database.We need a free version basically if it dosnt meets our demand then we can go for the developer versions. For our application it may run as client server application may be of 10 concurrent users,with 40 tables of 1 rows for 2 r 3 tables and others mostly of 1000 rows. We heard about the MSDE runtime version which is free.. How far this PostgreSql suits us comparing to the MSDE or MySql which is also free.. Kindly give me the status.. Regards Syed __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] transaction started without BEGIN??? Please help!
--On torsdag, april 21, 2005 13.43.27 + Ian Harding [EMAIL PROTECTED] wrote: One thing I can think of is some kind of inadvertent SQL injection. Somebody put ; begin; into a string that got sent to the server unescaped? The only other thing I can think of is if there was a hole in the logic in one of your explicitly started transactions that allowed the handle to be returned to the pool with a transaction open. I don't know anything about java or tomcat, but it should be possible. Thanks for the input. I'm leaning towards exactly this type of behaviour. BTW, how come the BEGINs are not logged? Is there no way to log the start of a transaction? I thought there would be a BEGIN, but there's not. /Palle Good luck. On 4/21/05, Palle Girgensohn [EMAIL PROTECTED] wrote: Hi! I have a huge problem here. On a busy server (a web system, mainly java servlets/jsp running on tomcat) running postgresql-8.0.2, all of a sudden, one of the postgres processes gets into the state idle in transation (revealed by `ps axU pgsql'). We use transaction very sparesly, so this is a mystery to me. I've swithed on logging of all statements, and also added the transaction id. part of the log is attached. As you can see, the transaction id is incremented, for every query, until suddenly, it is not incremented anymore, but stays the same. It seems to me, from lookin at the logs, that postgres enters a transaction although no BEGIN statement was issued? I hope I'm doing a misinterpretation of the logs here? So, in a couple of minutes, all connections are taken and a hundred postgres procs sit here waiting for the transaction to finish so they can issue their inserts. First, there shouldn't be a transaction here at all, how come it starts? And how come there's no BEGIN in the log, if one is actually started? My solution when this happens (has happened a couple of times a day on a busy server) is to kill the process that is reported idle in transaction. That way we loose data, I'd rather get it to commit. Is there a way to do that? Here are all the logging directives in postgresql.conf: log_destination = 'syslog' silent_mode = true log_line_prefix = '%x' log_statement = 'all' The attached log was grepped on the problematic PID, btw. postgresql-8.0.2 running on FreeBSD 4.10. jdbc 8.0.311 The icu patch is applied (http://people.freebsd.org/~girgen/postgresql-icu/) but I can't see how that would interfere with transaction handling? Thanks, Palle ---(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 ---(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
[GENERAL] bigserial field in a view, nextval function ?
I need to have an identity column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success How can I put a bigserial column in a view ? Thanks. ---(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] CURRENT_TIMESTAMP vs actual time
John DeSoi wrote: On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... -- C ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: John DeSoi wrote: On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a timestamp with time zone, whereas casting to timestamp unadorned returns a timestamp without time zone. Try cast(timeofday() as timestamptz) or cast(timeofday() as timestamp with time zone) It may not matter a lot but you may as well be aware of the difference ... -- Alvaro Herrera ([EMAIL PROTECTED]) En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica (Irulan) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Suggestions
syed magthum wrote: Hi We are likely to build a desktop application in VB.Net and we are in a hunt to go for a database.We need a free version basically if it dosnt meets our demand then we can go for the developer versions. For our application it may run as client server application may be of 10 concurrent users,with 40 tables of 1 rows for 2 r 3 tables and others mostly of 1000 rows. That's easily do-able with PostgreSQL. Or tables ten or a hundred times larger. There are ODBC and .Net client libraries - details and download at http://www.postgresql.org/download/ PostgreSQL is licenced under the BSD licence which lets you use, modify and distribute the system pretty much without restriction. http://www.postgresql.org/about/licence It is of course free, runs on most Unix variants (including MacOS-X) and with version 8, Windows 2000/XP. The only thing is, it isn't suitable as an embedded database - it needs to be its own server process. If that's acceptable to you, then I think you'll like PostgreSQL. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PLEASE HELP ME
On Apr 21, 2005, at 1:22 AM, amit kumar awasthi wrote: # Using sockets credentials for improved security. Not available everywhere, # but works on Linux, *BSD (and probably some others) local all all ident sameuser #local all all 127.0.0.1 sameuser only this entry i have made local is for unix domain sockets. You need a setting for host to enable TCP/IP connections via 127.0.0.1. Something like: hostall all 127.0.0.1 255.255.255.255 ident John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Suggestions
The native win32 version of Postgres will work for you without issues and be superior to the other choices you mentioned. The MSDE runtime version of MS SQL server is crippled and only accepts a few conncurrent connections. Mysql is fast, but lacks things you will probably need such as triggers, stored procedures etc etc Also Mysql is technically not free unless you use it for non commercial apps. You didn't mention if you are planning on using VB classic or VB.net? With VB.net you can use the native .net data provider which works well, with classic VB you will have to use ODBC or the ADO drivers, last time I checked the PG ado driver was pretty weak. Also if this is a new project, I might suggest you use Borland Delphi instead of VB. You get the same ease of use but you gain the incredible database support it provides and you can use the native Zeos components to access Postgresql http://www.zeoslib.net nothing like this exists for the VB world. Not to mention you get single exe files that do not require a runtime. There are also very nice commercial components available from www.microolap.com You can dowload the native win32 version of Postgres 8.x here: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=win32%2Fpostgresql-8.0.2.zip Good luck, -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql 8.x http://www.amsoftwaredesign.com syed magthum wrote: Hi We are likely to build a desktop application in VB.Net and we are in a hunt to go for a database.We need a free version basically if it dosnt meets our demand then we can go for the developer versions. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Finding cardinality of an index
All, Is there a way to determine cardinality (size) of an index? In general how to you query the 'attributes' (for lack of a better word) of an index. thanks, Bill __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] bigserial field in a view, nextval function ?
Zlatko Matic wrote: I need to have an identity column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success How can I put a bigserial column in a view ? What do you mean by an identity column in a view? A view is just a named query, so doesn't hold any data of its own. Can you explain what you are trying to achieve? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Christopher J. Bottaro wrote: Alvaro Herrera wrote: On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: John DeSoi wrote: On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a timestamp with time zone, whereas casting to timestamp unadorned returns a timestamp without time zone. Try cast(timeofday() as timestamptz) or cast(timeofday() as timestamp with time zone) It may not matter a lot but you may as well be aware of the difference ... Ahh, thanks for the tip. I guess I'll just stick with timeofday()::timestamp...its more concise anyways... Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Filesystem options for storing pg_data
References: http://archives.postgresql.org/pgsql-performance/2005-01/msg00131.php http://archives.postgresql.org/pgsql-performance/2004-05/msg00130.php http://archives.postgresql.org/pgsql-performance/2003-08/msg00191.php http://groups-beta.google.com/group/comp.os.linux.misc/msg/b299a71fd540c2b8?q=ext2+corrupt+%22power+failure%22hl=enlr=ie=UTF-8rnum=9 http://oss.sgi.com/projects/xfs/papers/filesystem-perf-tm.pdf http://www.oracle.com/technology/oramag/webcolumns/2002/techarticles/scalzo_linux02.html http://jamesthornton.com/hotlist/linux-filesystems/ It took me all of about 10 minutes to find all of those. But I've got work to do, so I'll leave further research here to the rest of the list. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] bigserial field in a view, nextval function ?
Well, my front-end is MS Access, and Access sees views as tables. When I have forms with subforms there is a problem with linking them if table has no primary key. As Access thinks that a view is a table, I need a primary key in the view. Also, Access doesn't like text field of ODBC-linked table to be primary key (#Deleted phenomena#). Instead, it should be a numeric field. Therefore, I would like to have an autoincrement field, which Access will consider as primary key...I need a calculated bigserial field... Can I accomplish it whith nextval ? Greetings, Zlatko - Original Message - From: Richard Huxton dev@archonet.com To: Zlatko Matic [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Thursday, April 21, 2005 5:06 PM Subject: Re: [GENERAL] bigserial field in a view, nextval function ? Zlatko Matic wrote: I need to have an identity column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success How can I put a bigserial column in a view ? What do you mean by an identity column in a view? A view is just a named query, so doesn't hold any data of its own. Can you explain what you are trying to achieve? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Suggestions
On Thu, 2005-04-21 at 08:42, syed magthum wrote: Hi We are likely to build a desktop application in VB.Net and we are in a hunt to go for a database.We need a free version basically if it dosnt meets our demand then we can go for the developer versions. For our application it may run as client server application may be of 10 concurrent users,with 40 tables of 1 rows for 2 r 3 tables and others mostly of 1000 rows. We heard about the MSDE runtime version which is free.. How far this PostgreSql suits us comparing to the MSDE or MySql which is also free.. PostgreSQL could work fine, make sure you have your application setup to vacuum the database every so often. Firebird is also a good choice for what you're doing, and may be easier to install along side your own software. If you're doing very simple SQL work, then SQLLite might be a good choice. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
On Thu, 2005-04-21 at 09:52, Christopher J. Bottaro wrote: Alvaro Herrera wrote: On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: John DeSoi wrote: On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a timestamp with time zone, whereas casting to timestamp unadorned returns a timestamp without time zone. Try cast(timeofday() as timestamptz) or cast(timeofday() as timestamp with time zone) It may not matter a lot but you may as well be aware of the difference ... Ahh, thanks for the tip. I guess I'll just stick with timeofday()::timestamp...its more concise anyways... 2 points: 1: cast(timeofday() as timestamptz) is the SQL standard way of doing it, and it's more portable. 2: I think Alvaro's point was about timestamp with timezone, not the format for casting. i.e. if you use postgresql's shorthand for casting, you could use this for timestamptz: select timeofday()::timestamptz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Thomas Hallgren wrote: Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. For the same reason that the OP couldn't use CURRENT_TIMESTAMP. It returns the timestamp of the start of the transaction. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(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
[GENERAL] Most specific match using between
Hi people, is there a swtich or something to instruct a between statement hat it must perform a most specific match ? These are the details, i have these table name| start| end ---+---+- general | 266 | 266 specific | 2660124 | 2660124 (2 rows) unsing the query: select name where '2660124' between start and end i got: name - general specific How can i get only specific? thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
On Thu, 2005-04-21 at 10:21, Thomas Hallgren wrote: Christopher J. Bottaro wrote: Alvaro Herrera wrote: Ahh, thanks for the tip. I guess I'll just stick with timeofday()::timestamp...its more concise anyways... Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. I think you missed the first part of the conversation, which was he needed a type that updated inside a transaction: # begin; # select timeofday()::timestamptz; 2005-04-21 10:59:58.181834-05 # select now(); 2005-04-21 10:59:50.286865-05 # select timeofday()::timestamptz; 2005-04-21 11:00:04.821057-05 # select now(); 2005-04-21 10:59:50.286865-05 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Most specific match using between
On Thu, 2005-04-21 at 10:51, [EMAIL PROTECTED] wrote: Hi people, is there a swtich or something to instruct a between statement hat it must perform a most specific match ? These are the details, i have these table name| start| end ---+---+- general | 266 | 266 specific | 2660124 | 2660124 (2 rows) unsing the query: select name where '2660124' between start and end i got: name - general specific How can i get only specific? Do you want the ONE row that's closest, or a set of rows that are fairly close? Not sure what your specification it exactly, but how about: select name where '2660124' between start and end order by abs (start-end) limit 1; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Scott Marlowe wrote: I think you missed the first part of the conversation... I sure did. Sorry... Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] User def. Functions for sysadmin tasks?
Hi, i like to use postgresql for managing my postfix mailserver via lookup tables. for each mailbox domain i have a system account to have quotas per domain. (i know there are other solutions like postfix-vda and so on) When i add a domain to the mailsystem i have to add a user account for this domain and afterwards do one INSERT statement to add the domain to the postfix tables. Does it make sense to write a C Function which triggers before insert of a domain and adds the appropiate user to the system? The C Function runs as user postgres, right?. So i have to add postgres to /etc/sudoers file to allow execution of /usr/sbin/adduser as root, right? The same way you could manage web accounts. The C Functions writes or updates httpd.conf files to disk. Or write .htpasswd files and so on. Most administration systems are running a cron job to do those tasks but it would be nicer to have the webserver running in the moment the transaction succeeds. And another benefit: you have all logic inside your database. I thought about any security reasons not to do it. One Thought: If you can become postgres you could add users to the system. But if you can become postgres you could delete all data files anyway which would be much more serious than adding or deleting users.!? Did anybody tried something like this before? Does it make sense? kind regards, janning ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Most specific match using between
On Thu, Apr 21, 2005 at 09:51:16AM -0600, [EMAIL PROTECTED] wrote: Hi people, is there a swtich or something to instruct a between statement hat it must perform a most specific match ? These are the details, i have these table name| start| end ---+---+- general | 266 | 266 specific | 2660124 | 2660124 (2 rows) unsing the query: select name where '2660124' between start and end i got: name - general specific How can i get only specific? ORDER BY end-start ASC LIMIT1; ? Cheers, Steve ---(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] Most specific match using between
On Thu, 2005-04-21 at 10:51, [EMAIL PROTECTED] wrote: These are the details, i have these table name| start| end ---+---+- general | 266 | 266 specific | 2660124 | 2660124 (2 rows) unsing the query: select name where '2660124' between start and end i got: name - general specific How can i get only specific? Do you want the ONE row that's closest, or a set of rows that are fairly close? Not sure what your specification it exactly, but how about: select name where '2660124' between start and end order by abs (start-end) limit 1; I want the one row that is closest, your query seems to work... --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] tsearch2 trigger
I am writing a tsearch2 trigger function in plperl. I would like to
run a query similar to this:
setweight(to_tsvector(col1),'b') || setweight(to_tsvector(col2),'a')
and insert the result into the tsvector column. I know I can call
spi_exec_query('select ...') and insert the result, but is this the
best way of doing it? I need the trigger to be plperl since there is
some additional foo I am doing.
Thanks! --Joel
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] Get timestamp as UTC
I was under the impression that using timezone('UTC', now())::timestamptz
would give me the current UTC time, with timezone offset. Instead I am
getting the UTC time, but with an offset of -07(my local time). How do I get
UTC time, with the 0 offset that it should be?
Thanks,
Steve
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] List of Functions
On Thu, 21 Apr 2005, Dinesh Pandey wrote: \df \df functionname also, \df function* Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Stone Sent: Thursday, April 21, 2005 9:48 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] List of Functions I have no idea if this is the correct list to use. How do you obtain a list of the functions and their argument lists that have been created on a Postgres database? I have tried searching various lists and archives plus the documentation. All I want to find is a list of:- Function name return valuearguments. After you run the create or replace script, this data has to be saved somewhere. Just the name of the table would do. Thanks, Robert Stone CONFIDENTIALITY NOTICE AND DISCLAIMER - The information in this transmission may be confidential and/or protected by legal privilege, and is intended only for the person or persons to whom it is addressed. If you are NOT such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received this transmission in error, please advise Park Lane Information Technology -- +61 (03) 9813 1000 -- and permanently delete all copies of this transmission from your mail server(s), mail client(s) and from any physical records. Park Lane Information Technology has no liability (including liability in negligence) for any unauthorised use of the information contained in this transmission. ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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
[GENERAL] debug levels in server
I'm trying to track down a hang in a slony "lock set" command, which creates triggers on a set of tables. I assume that it's getting blocked by a lock on a table somewhere, and I'd like to print out all the "create trigger" statements in the postgres log. I have log_min_messages = DEBUG5, and I'm seeing a lot of logging, but I don't see the create trigger statements showing up in the log. What do I need to set to get this output? Thanks. - DAP--David Parker Tazz Networks (401) 709-5130
[GENERAL] Had a problem with pg_clog
Hi List, I had a database with a 8 Mb pg_dump file with 1.7 Gb in PG_DATA. I dropped the database and tried to recreate the db with this step: psql template1 data.sql All tables and all data was restored and to help accessing the db I tried to vacuum analyze it, yielding this error message: vacuumdb -z miwabar PANIC: open of /var/lib/pgsql/data/pg_clog/ failed: Keine Berechtigung server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost vacuumdb: vacuum miwabar failed The panic message with -v gave PANIC: read of clog file 0, offset 16384 failed: Erfolg which lead me to the mailing-list archive and the following fix: dd bs=32k count=1 /dev/zero /var/lib/pgsql/data/pg_clog/ 1+0 records in 1+0 records out afterwards my vacuum works. Had I done the right thing? Should I have expected this error? -- Peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] lots of puzzling log messages
On Wed, 20 Apr 2005, Tom Lane wrote: On Tue, Apr 19, 2005 at 05:39:42PM -0700, Dennis Sacks wrote: I am Seeing twelve of these messages every five to ten seconds in the Postgresql serverlog when my java application is running: 2005-04-19 16:43:03 LOG: 0: statement: rollback; begin; LOCATION: pg_parse_query, postgres.c:464 I think older versions of the JDBC driver will do that behind your back ... try the latest driver, and if you still see it then inquire on pgsql-jdbc. The latest driver will do that as well, but in two statements now that transaction end doesn't automatically start a new one immediately. The difference is that the server doesn't log it for the 8.0 driver because it prepares the begin/commit/rollback statements just once and executes them from then on. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Waiting for Disconnect
Hi All,
I have some tests that create a database, run a bunch of tests against
it, and then drop it. But I was running into an issue where I'd get
this error even after I disconnected from the test database:
ERROR: source database foo is being accessed by other users
And no, no other users are connecting to the database, just me. Some
time ago, someone on IRC pointed me to doing a select in the
pg_stat_activity table, and that seemed to work:
sleep 1 while $dbh-selectrow_array(
'SELECT 1 FROM pg_stat_activity where datname = ?',
undef, 'foo'
);
However, I found that I could get the above error even after this query
returns no rows if I'm running my tests on a really fast box. My
assumption is that the back end is deleting the record from this table
before the connection is actually fully dropped. So, to get around this
I'm both checking pg_stat_activity and trapping the error. Here's what
my code looks like:
# Wait until the other connection has been dropped. Throw in an
extra
# query to kill a bit of time, just to make sure that we really
are
# fully disconnted. It seems like it sometimes thinks there are
still
# connections even after the query returns false.
sleep 1 while $dbh-selectrow_array(
'SELECT 1 FROM pg_stat_activity where datname = ?',
undef, $self-{conf}{pg}{db_name}
);
for (my $i = 0; $i 5; $i++) {
# This might fail a couple of times as we wait for the
database
# connection to really drop. It might be sometime *after*
the above
# query returns false!
eval { $dbh-do(qq{DROP DATABASE
$self-{conf}{pg}{db_name}}) };
if (my $err = $@) {
die $err
if $i = 5 || $err !~ /is being accessed by other
users/;
sleep 1, next;
}
last;
}
So, my question is, is this the only reasonable approach to resolving
this issue? I'm running these tests against 8.0.x and 7.4.x.
Thanks,
David
PS: Please Cc me on all replies, as I am not actually on the
pgsql-general list. Thanks!
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Filesystem options for storing pg_data
On Thu, 21 Apr 2005, Scott Marlowe wrote: References: http://archives.postgresql.org/pgsql-performance/2005-01/msg00131.php http://archives.postgresql.org/pgsql-performance/2004-05/msg00130.php http://archives.postgresql.org/pgsql-performance/2003-08/msg00191.php http://groups-beta.google.com/group/comp.os.linux.misc/msg/b299a71fd540c2b8?q=ext2+corrupt+%22power+failure%22hl=enlr=ie=UTF-8rnum=9 http://oss.sgi.com/projects/xfs/papers/filesystem-perf-tm.pdf http://www.oracle.com/technology/oramag/webcolumns/2002/techarticles/scalzo_linux02.html http://jamesthornton.com/hotlist/linux-filesystems/ It took me all of about 10 minutes to find all of those. But I've got work to do, so I'll leave further research here to the rest of the list. Thanks for your precious time, but when I say I searched the archives I really mean it. If you cared to read _my_ message, I was looking for any benchmark (or comment) under the following conditions: 1) PostgreSQL load - that is, a benchmarck based on PostgreSQL, or, alternatively, on another database, or on artificial write+fsync load. Any other (cached) write load is _meaningless_ to our purposes. 2) the author was aware of mount options, and actually used them. I think there's enough evidence that ext3 default mount options are on the safe side (_safer_ than other fses, it seems), so there's no point in comparing default ext3 alone (comparing all modes _is_ interesting, tho). I've spend much more than 10 minutes of my time, and found nothing, but the links that _I_ posted. I'll invest more time, and comment on the links you posted (which I had read already, of course): http://archives.postgresql.org/pgsql-performance/2005-01/msg00131.php it's not clear at all, it possibly fails both 1) and 2). The authors says nothing about a write+fsync benchmark or about ext3 mount options. http://archives.postgresql.org/pgsql-performance/2004-05/msg00130.php that's the one I got Bert Scalzo's article from. Other links fail to meet 1) and some 2). Note that fsync is likely to disrupt most optimizations. The fact that a filesystem scales better under normal (cached) load, means nothing when it comes to fsyncing. http://archives.postgresql.org/pgsql-performance/2003-08/msg00191.php this _defends_ ext2 from the accusation of being buggy. The author prefers XFS, but I only have fuzzy reasons, as opposed to metrics. I was looking for metrics. It's says nothing about ext3, so does not apply. These are not from postgresql lists, but anyway: http://groups-beta.google.com/group/comp.os.linux.misc/msg/b299a71fd540c2b8?q=ext2+corrupt+%22power+failure%22hl=enlr=ie=UTF-8rnum=9 People are referring to the old ext2 filesystem here. The new ext3 is very resistant to this issue. If you're referring to what Jinny said, well all the evidence is ...recently I have come to know from a reliable group that Linux is not so stable. Does not meet 1) and 2), sorry. http://oss.sgi.com/projects/xfs/papers/filesystem-perf-tm.pdf Yes, surprisingly enough I've read this one, too. The only interesting part is [XFS] Perfomance features include asynchronous write ahead logging (similar to Ext2 - no, ext3 - with data=writeback), This confirms my comment about comparing apples and oranges, and completely justifies my requirement 2) - and comes from a XFS paper! It's not clear at all if what they call OLTP Workload really performs fsync after write. Anyway, there's only _one_ graph in the results (how weird) and all filesystems are pretty close. No tests with data=journal. All other graphs in the Appendix fail requirement 1). http://www.oracle.com/technology/oramag/webcolumns/2002/techarticles/scalzo_linux02.html thanks, this is the like that _I_ posted. Have _you_ read it? It shows that EXT3 is almost twice as fast as JFS. Too bad there's no XFS here. BTW, this meets 1), I'm not sure about 2), but the options they used seem enough to outperform JFS. http://jamesthornton.com/hotlist/linux-filesystems/ this is just a collection of links. It's not clear which one would back up your claim of XFS and JFS being _generally_ considered superior for PostgreSQL or other database usage. Let's see: http://www-106.ibm.com/developerworks/linux/library/l-fs8.html data=ordered mode effectively solves the corruption problem found in data=writeback mode and _most other journaled filesystems_, and it does so without requiring full data journaling (emphasis mine) interesting enough, most journaled filesystems do have a corruption problem, ext3 in default mode doesn't. But this does not really apply to us, this refers to normal writes not write+fsyncs. I think any fs has to be badly broken if it looses data after fsycn, anyway. http://www-106.ibm.com/developerworks/library/l-fs9.html Other than that, XFS performance was very close to that of ReiserFS and generally surpasses that of ext3... uh, this sounds interesting... but wait... ... One of the nicest things about XFS is that, like ReiserFS, it doesn't
[GENERAL] timezone() with timeofday() converts the wrong direction?
These attempts were run at 4/21/2005 13:15:00 -07.
UTC: 4/21/2005 20:15:00
SELECT timeofday()::timestamp
04/21/2005 13:15:00
SELECT timezone('UTC', now())
04/21/2005 20:15:00 PM
SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
04/21/2005 06:15:00 - What the heck is this?!
Why does the conversion to UTC using timeofday() go wrong? It seems like the
conversion went 7 hours in the wrong direction. Did I forget a parameter or
switch somewhere?
Thanks,
Steve
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Filesystem options for storing pg_data
Whoa, hold on. My original post was this: QUOTE: Generally XFS and JFS are considered superior to ext2/3. ext3, in my experience, isn't much slower than ext2. Plus the decreased time required to bring up a server after a power outage is worth something too. Having used ext3 quite a bit, I'd say it's fairly stable and reliable, but I have seen references here to know, possibly unfixable bugs. I've used XFS a few years back, and there was no great gain for what we were doing at the time, as we were CPU, not I/O bound. ENDQUOTE: So where do you get off saying I'm such a big fan of XFS and am trashing ext3. You do the research, I'm tired of trying to have a civilized conversation with you. If you wanna argue, go pay someone a quarter to do it, I'm done. ---(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] timezone() with timeofday() converts the wrong
On Thu, 2005-04-21 at 15:35, Steve - DND wrote:
These attempts were run at 4/21/2005 13:15:00 -07.
UTC: 4/21/2005 20:15:00
SELECT timeofday()::timestamp
04/21/2005 13:15:00
SELECT timezone('UTC', now())
04/21/2005 20:15:00 PM
SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
04/21/2005 06:15:00 - What the heck is this?!
Why does the conversion to UTC using timeofday() go wrong? It seems like the
conversion went 7 hours in the wrong direction. Did I forget a parameter or
switch somewhere?
What version of PostgreSQL are you running? I seem to remember a few
versions ago the offset having the wrong sign.
---(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] timezone() with timeofday() converts the wrongdirection?
What version of PostgreSQL are you running? I seem to remember a few versions ago the offset having the wrong sign. I'm running 8.0.2 Win32. Steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Thu, Apr 21, 2005 at 01:35:16PM -0700, Steve - DND wrote:
These attempts were run at 4/21/2005 13:15:00 -07.
UTC: 4/21/2005 20:15:00
SELECT timeofday()::timestamp
04/21/2005 13:15:00
SELECT timezone('UTC', now())
04/21/2005 20:15:00 PM
SELECT timezone('UTC', timeofday()::timestamp)::timestamp without time zone
04/21/2005 06:15:00 - What the heck is this?!
According to the Date/Time Functions and Operators documentation,
timezone(zone, timestamp) is equivalent to timestamp AT TIME ZONE
zone, and timestamp without time zone AT TIME ZONE zone means
Convert local time in given time zone to UTC and has a return
type of timestamp with time zone. It therefore seems to me that
you're converting timeofday() from UTC to UTC and that the output
is converted for display to your local time zone, which you then
strip off. See the example in the documentation:
Examples (supposing that the local time zone is PST8PDT):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
The first example takes a zone-less time stamp and interprets it as
MST time (UTC-7) to produce a UTC time stamp, which is then rotated to
PST (UTC-8) for display. The second example takes a time stamp
specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
We could rewrite the example using your values as follows:
SELECT TIMESTAMP '2005-04-21 13:15:00' AT TIME ZONE 'UTC';
Result: 2005-04-21 06:15:00-07
The first example takes a zone-less time stamp and interprets it as
UTC time to produce a UTC time stamp, which is then rotated to PDT
(UTC-7) for display.
Perhaps this is what you want:
SELECT timezone('UTC', timeofday()::timestamptz);
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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] timezone() with timeofday() converts the wrong direction?
Perhaps this is what you want:
SELECT timezone('UTC', timeofday()::timestamptz);
That did it. Strangely, I thought I had tried that already, but I must not
have. My next question would be if I did:
SELECT timezone('UTC', timeofday()::timestamptz):timestamptz;
Why do I get the timezone value as being -07(my local offset), instead
of -00?
Thanks,
Steve
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Thu, Apr 21, 2005 at 04:11:57PM -0700, Steve - DND wrote:
Perhaps this is what you want:
SELECT timezone('UTC', timeofday()::timestamptz);
That did it. Strangely, I thought I had tried that already, but I must not
have. My next question would be if I did:
SELECT timezone('UTC', timeofday()::timestamptz):timestamptz;
Why do I get the timezone value as being -07(my local offset), instead
of -00?
Again looking at the documentation, we see that timestamp with
time zone AT TIME ZONE zone means Convert UTC to local time in
given time zone and has a return type of timestamp without time
zone. So if we run the above command without the final cast
around 16:25 PDT / 23:25 UTC, we get this:
SELECT timezone('UTC', timeofday()::timestamptz);
timezone
2005-04-21 23:25:12.868212
(1 row)
This result is a timestamp without time zone, so there's no
indication that it's UTC or PDT or anything else. Since it has no
time zone, casting it to timestamptz puts it in your local time
zone:
SELECT '2005-04-21 23:25:12.868212'::timestamptz;
timestamptz
---
2005-04-21 23:25:12.868212-07
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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] timezone() with timeofday() converts the wrongdirection?
Steve - DND wrote:
What version of PostgreSQL are you running? I seem to remember a few
versions ago the offset having the wrong sign.
I'm running 8.0.2 Win32.
BSD Unix looks strange too:
test= select current_timestamp;
timestamptz
---
2005-04-21 19:45:55.553635-04
(1 row)
test= SELECT timezone('UTC', current_timestamp::timestamp);
timezone
---
2005-04-21 15:46:12.740201-04
(1 row)
test= SELECT timezone('UTC', current_timestamp::timestamp)::timestamp
without time zone;
timezone
2005-04-21 15:46:14.333257
(1 row)
But these look fine:
test= SELECT timezone('UTC', current_timestamp);
timezone
---
2005-04-21 23:48:18.60604
(1 row)
test= SELECT current_timestamp::timestamp;
timestamp
2005-04-21 19:51:25.867765
(1 row)
test= SELECT current_timestamp::timestamp with time zone;
timestamptz
---
2005-04-21 19:51:30.178186-04
(1 row)
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 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] Install development headers/libraries Win32 v 8.0.2
How do I install the headers and libraries for extending postgreSql? They are not installed by the windows installer (see the following post: http://archives.postgresql.org/pgsql-hackers-win32/2005-03/msg00169.php ). Regards, Mark Miller
[GENERAL] Returning a RECORD, not SETOF RECORD
I just discovered that my previous post concerning this had the same subject line as a discussion that took place in January. I'm not asking the same question though, so here I go again with my question about syntax and feasibility. I do the following: CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my RECORD definition? Is it possible at all? Ideally I'd like to write something like this: SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc; but that yields a syntax error. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Record as a parameter to a function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I would like to write a trigger that will do complex calculations on a row, so the idea is to slipt the work throught several functions, and as the row has 23 fields, it's not an option to pass them one by one, so I tried to pass the record itself (NEW)... While doing the home work before posting I found a piece of code that ilustrastes this problem (http://groups.google.pt/groups?hl=en-USlr=threadm=200201210419.2467%40th00.opsion.frrnum=1prev=/groups%3Fhl%3Dpt-PT%26lr%3D%26q%3Drecord%2Bparameter%26btnG%3DPesquisar%26meta%3Dgroup%253Dmuc.lists.postgres.questions) The code (from cnliou): CREATE TABLE test(c1 TEXT); CREATE FUNCTION test1(test) RETURNS BOOL AS ' BEGIN ~ InRec ALIAS FOR $1; ~ RAISE NOTICE ''%'',InRec.c1; ~ RETURN TRUE; END;' LANGUAGE 'plpgsql'; CREATE FUNCTION tftest() RETURNS OPAQUE AS ' BEGIN ~ PERFORM test1(NEW); ~ RETURN NEW; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER TriggerTest AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE tftest(); ...and the error I get when inserting a row: carlos=# insert into test (c1) values( 'test'); ERROR: NEW used in query that is not in a rule CONTEXT: PL/pgSQL function tftest line 2 at perform carlos=# In the comments to the original post it is said that Postgres doesn't work very well with composite (rowtype) parameters... since the posts are more then 2 years old, any one can tell me what's the problem with this code or if there is another kind of solution to this problem? Thanks, Carlos -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCaEjn90uzwjA1SJURAvc0AJwPfh6QzwLOgGXnaVUEkhsQxu/+1ACgpTAC GRjw2uEDM/RXd/WKd9NjzIM= =26wD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
Again looking at the documentation, we see that timestamp with
time zone AT TIME ZONE zone means Convert UTC to local time in
given time zone and has a return type of timestamp without time
zone. So if we run the above command without the final cast
around 16:25 PDT / 23:25 UTC, we get this:
SELECT timezone('UTC', timeofday()::timestamptz);
timezone
2005-04-21 23:25:12.868212
(1 row)
This result is a timestamp without time zone, so there's no
indication that it's UTC or PDT or anything else. Since it has no
time zone, casting it to timestamptz puts it in your local time
zone:
SELECT '2005-04-21 23:25:12.868212'::timestamptz;
timestamptz
---
2005-04-21 23:25:12.868212-07
(1 row)
Okay, I understand what you're saying now, but then is a time without a
timezone implicitly assumed to be UTC? Is there a way to explicitly make the
timezone on the stamp be UTC, if the prior is not the case?
Steve
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Table modifications with dependent views - best practices?
Ok, I've been using postgres for a-while now, and am curious how you guys handle this. What is the best way to make modifications to tables that have lots of dependent objects, like views? Do you just do the obvious drop...cascade and manually re-create your views? Do you keep your create view statements in a text file for this purpose or just pull them from a pg_dump file? Is there a better way to handle this sort of thing? I know on a properly designed database, changes to the schema are usually not necessary. However, during development, database tweaks (at least for me) are pretty common. Any thoughts? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Record as a parameter to a function
On Fri, Apr 22, 2005 at 01:44:24AM +0100, Carlos Correia wrote:
carlos=# insert into test (c1) values( 'test');
ERROR: NEW used in query that is not in a rule
CONTEXT: PL/pgSQL function tftest line 2 at perform
PostgreSQL 8.0 and later have improved support for composite types.
Here's the example you posted, slightly rewritten:
CREATE TABLE test (c1 text);
CREATE FUNCTION test1(InRec test) RETURNS boolean AS $$
BEGIN
RAISE NOTICE '%', InRec.c1;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION tftest() RETURNS trigger AS $$
BEGIN
PERFORM test1(NEW);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER TriggerTest AFTER INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE tftest();
If I execute the above statements in an 8.0.2 database, I get the
following when I insert a new record:
test= INSERT INTO test (c1) VALUES ('Test');
NOTICE: Test
CONTEXT: SQL statement SELECT test1( $1 )
PL/pgSQL function tftest line 2 at perform
INSERT 0 1
In psql you can change the verbosity so you don't see the context
messages:
test= \set VERBOSITY terse
test= INSERT INTO test (c1) VALUES ('Test');
NOTICE: Test
INSERT 0 1
Hope this helps.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Record as a parameter to a function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Fuhr wrote: | On Fri, Apr 22, 2005 at 01:44:24AM +0100, Carlos Correia wrote: | |carlos=# insert into test (c1) values( 'test'); |ERROR: NEW used in query that is not in a rule |CONTEXT: PL/pgSQL function tftest line 2 at perform | | | PostgreSQL 8.0 and later have improved support for composite types. Thanks... but does this means that you can't do that with version 7.4.x? Carlos -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCaGIe90uzwjA1SJURAth1AJwJwKTO2kEQXCDAEtgHo8HsbhhU7wCfUCmh FsjjfW9J8plrHc1oT8JsCwA= =KFaE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Thu, Apr 21, 2005 at 05:56:41PM -0700, Steve - DND wrote:
Okay, I understand what you're saying now, but then is a time without a
timezone implicitly assumed to be UTC? Is there a way to explicitly make the
timezone on the stamp be UTC, if the prior is not the case?
See Date/Time Types in the Data Types chapter of the documentation:
http://www.postgresql.org/docs/8.0/interactive/datatype-datetime.html
Conversions between timestamp without time zone and timestamp with
time zone normally assume that the timestamp without time zone value
should be taken or given as timezone local time. A different zone
reference can be specified for the conversion using AT TIME ZONE.
All timezone-aware dates and times are stored internally in UTC.
They are converted to local time in the zone specified by the
timezone configuration parameter before being displayed to the
client.
As far as I know, it's not possible to get output like the following
from the same query if the data type is timestamp with time zone:
2005-04-21 15:00:00-07
2005-04-21 22:00:00+00
However, I suppose you could muck around with the TimeZone configuration
variable and cast the timestamps to text:
CREATE FUNCTION tzconvert(timestamp with time zone, text) RETURNS text AS '
DECLARE
ts ALIAS FOR $1;
tz ALIAS FOR $2;
tmptz text;
retval text;
BEGIN
tmptz := current_setting(''TimeZone'');
PERFORM set_config(''TimeZone'', tz, TRUE);
retval := ts;
PERFORM set_config(''TimeZone'', tmptz, TRUE);
RETURN retval;
END;
' LANGUAGE plpgsql VOLATILE STRICT;
SELECT tzconvert('2005-04-21 15:00:00-07', 'PST8PDT') AS pacific,
tzconvert('2005-04-21 15:00:00-07', 'UTC') AS utc;
pacific | utc
+
2005-04-21 15:00:00-07 | 2005-04-21 22:00:00+00
(1 row)
Maybe somebody knows of an easier way to do that.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Table modifications with dependent views - best practices?
On Thu, Apr 21, 2005 at 09:24:49PM -0500, John Browne wrote: Ok, I've been using postgres for a-while now, and am curious how you guys handle this. What is the best way to make modifications to tables that have lots of dependent objects, like views? Do you just do the obvious drop...cascade and manually re-create your views? Do you keep your create view statements in a text file for this purpose or just pull them from a pg_dump file? Is there a better way to handle this sort of thing? I put CREATE statements in a file so I can easily reload them. Sometimes I put views and functions in their own files so they can be reloaded separately, since dropping and recreating them is usually a trivial, non-destructive operation, unlike dropping and recreating tables. I usually put DDL statements in a transaction, for a couple of reasons: so that a mistake doesn't leave me with half-done work (any error will cause the entire transaction to roll back), and to make the changes atomic for the benefit of other transactions. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[GENERAL] listing all tables
I finally got my windows install of postgresql to work (as a service). It would not work when I did the other install (not as a windows service). Now, I am working through some tutorial examples. My question is, what command can I issue to list all the tables in a database? I found the command ANALYZE VERBOSE ... but is there something else, analogous to the DIR command in DOS, to show all the tables (files)? Thanks
Re: [GENERAL] Regular expression. How to disable ALL meta-character
On Thu, Apr 21, 2005 at 09:03:43AM -0400, David Gagnon wrote:
Just want to share the solution I got to solve my problem. I wanted to
be eable to search a string (say X) (non case sensitive) without having
meta-character involved. The X string come directy from the web so any
[%]* may cause error in regular expression (because they form non valid
expression)
1) Using like: select * from mytable where lower(mycol) LIKE lower(%
|| lower(X) || %);
Mostly perfect solution. Don't crash but % still have a special
meaning. Wich means anything
Have you considered using position() or strpos()? They do simple
substring searches without any metacharacters.
SELECT position(lower('AbC') in lower('aBcDeF'));
position
--
1
(1 row)
SELECT position(lower('xYz') in lower('aBcDeF'));
position
--
0
(1 row)
You might also want to look at the contrib/pg_trgm module to see
if it would be useful.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] listing all tables
On Thu, Apr 21, 2005 at 11:33:07PM -0400, [EMAIL PROTECTED] wrote: My question is, what command can I issue to list all the tables in a database? If you're using psql then you can use the \dt command, although that will show only the tables in your search path (which is probably what you're interested in). See Meta-Commands in the psql documentation for more info; see also the System Catalogs and The Information Schema chapters. http://www.postgresql.org/docs/8.0/interactive/app-psql.html http://www.postgresql.org/docs/8.0/interactive/catalogs.html http://www.postgresql.org/docs/8.0/interactive/information-schema.html One way to learn more about the system catalogs is to run psql -E or execute \set ECHO_HIDDEN from within psql so you can see the queries psql sends for commands like \dt, \d tablename, etc. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] psqlodbc MSAccess and Postgresql
Thanks for the help with /dt... Mike, your post helped me to discover psql command window, I had been trying out the pgAdmin ... I found an interesting article regarding MSAccess as a front end to Postgresql, so I downloaded the psqlodbc installer. I will be getting up the nerve to run the install and go into windows odbc and install that driver so I can use MSAccess with Postgresql ... any tips or caveats appreciated This is the link which is guiding me http://database.sarang.net/database/postgres/postodbc/faq.html
[GENERAL] How to install Postgres that supports 64-bit integer/date-time.
How to install Postgres 8.0.1 that supports 64-bit integer/date-time. # ./configure --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc while installation I am getting this message: checking whether to build with 64-bit integer date/time support... no Thanks
Re: [GENERAL] debug levels in server
David Parker [EMAIL PROTECTED] writes: I'm trying to track down a hang in a slony lock set command, which creates triggers on a set of tables. I assume that it's getting blocked by a lock on a table somewhere, and I'd like to print out all the create trigger statements in the postgres log. I have log_min_messages = DEBUG5, and I'm seeing a lot of logging, but I don't see the create trigger statements showing up in the log. What do I need to set to get this output? log_statement ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
