Re: [GENERAL] Help with tools...
"Cristian Prieto" <[EMAIL PROTECTED]> writes: > I've tryied with some tools as pgEditor and EMS PostgreSQL Manager, > but I need a really good pgsql and database Editor to use in Linux > or Windows. Options? Have you take a look at pgAdmin III? http://www.pgadmin.org/ What sort of features are you looking for? Jason ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Pg 8.0rc5 to 8.0.1 update
Hi, Just a quick question. Do I need to do an initdb to upgrade a cluster from v8.0rc5 to v8.0.1 or can I just do a make install. TIA Ben ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Help with tools...
I've tryied with some tools as pgEditor and EMS PostgreSQL Manager, but I need a really good pgsql and database Editor to use in Linux or Windows. Options?
[GENERAL] maximum size
Hello, could you be so kind to help me with following requests ? I need the maximum size of the following types : varchar(n) numeric(n,p) text bytea Could you please give me the name of articles or publications and where I can find it for these requests ? I want to transfer a oracle database 8i to postgreSQL 7.3.2. Is there an article or publication which related about this ? Thanks MOLINET S. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Tracking down cause of duplicated oid in table.
We have a table that we use to store aggregated data, nightly
we add a row for the previous day, and update all the rows in
the table with the new information. The table is then vacuumed
full.
Our setup is:
Linux Kernel 2.6.4
SCSI Drives Hardware Raid 10 (4 Drives)
Reiserfs
PostgreSQL 7.4.5
The table looks like:
qualitysmith=# \d admin_report_materialized_view
Table
"public.admin_report_materialized_view"
Column| Type |
Modifiers
--+-+
id | integer | not null default
nextval('public.admin_report_materialized_view_id_seq'::text)
new_order_count | integer | not null default 0
out_of_area_count| integer | not null default 0
fake_order_count | integer | not null default 0
fake_zip_count | integer | not null default 0
in_area_zip_count| integer | not null default 0
customer_estimates | integer | not null default 0
total_estimate_count | integer | not null default 0
cancelled_appointment_count | integer | not null default 0
gross_estimate_count | integer | not null default 0
total_repair_estimate_count | integer | not null default 0
total_replace_estimate_count | integer | not null default 0
active_contractors_count | integer | not null default 0
estimate_percentage | numeric | not null default 0
trades_id| integer | not null
master_day | date| not null
Indexes:
"admin_report_materialized_view_pkey" primary key, btree (id)
"admin_report_materialized_view_unique_day_trades_id" unique, btree
(master_day, trades_id)
Yesterday the following popped up in our log files:
Feb 22 06:12:34 bigbrother postgres[9832]: [162-1] WARNING: index
"admin_report_materialized_view_pkey" contains 18677 row versions, but table
contains 18674 row versions
Feb 22 06:12:34 bigbrother postgres[9832]: [162-2] HINT: Rebuild the index
with REINDEX.
Feb 22 06:12:34 bigbrother postgres[9832]: [163-1] WARNING: index
"admin_report_materialized_view_unique_day_trades_id" contains 18677 row
versions, but table contains 18674 row
Feb 22 06:12:34 bigbrother postgres[9832]: [163-2] versions
Feb 22 06:12:34 bigbrother postgres[9832]: [163-3] HINT: Rebuild the index
with REINDEX.
Reindex then gives:
qualitysmith=# reindex index admin_report_materialized_view_pkey;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
So digging through the table I found the following rows:
qualitysmith=# select tableoid, oid, ctid, xmin, xmax, cmin, cmax, id from
admin_report_materialized_view where oid = '104649735';
tableoid |oid| ctid | xmin| xmax| cmin| cmax
| id
---+---+-+---+---+---+---+--
104471713 | 104649735 | (13,42) | 704184382 | 704967810 | 704967810 |
15 | 2284
104471713 | 104649735 | (14,68) | 704184382 |15 |15 |
707367279 | 2284
And pg_filedump -i -f -R 13 $PGDATA/base/29539533/104471713 gives (partial):
Item 42 -- Length: 96 Offset: 4160 (0x1040) Flags: USED
XID: min (704184382) CMIN|XMAX: 704967810 CMAX|XVAC: 15
Block Id: 470 linp Index: 65 Attributes: 16 Size: 28
infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
1040: 3e00f929 82f4042a 0f00 d601 >..)...*
1050: 41001000 12291c00 07d43c06 ec08 A)<.
1060:
1070:
1080:
1090: 0800 0a00 e006
pg_filedump -i -f -R 14 $PGDATA/base/29539533/104471713 gives (partial):
Item 68 -- Length: 96 Offset: 1440 (0x05a0) Flags: USED
XID: min (704184382) CMIN|XMAX: 15 CMAX|XVAC: 707367279
Block Id: 14 linp Index: 68 Attributes: 16 Size: 28
infomask: 0x2912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
05a0: 3e00f929 0f00 6f91292a 0e00 >..)o.)*
05b0: 44001000 12291c00 07d43c06 ec08 D)<.
05c0:
05d0:
05e0:
05f0: 0800 0a00 e006
I am not positive my calculations here are correct. Finding the appropriate
place in the CLOG for the XMIN I come up with file 029F byte offset 2400F which
is 10. For XMAX (704967810) I come up with file 02A0 offset 13D20 which has
value 20. (I don't know what these mean so they are included for anyone who
may.)
Ok, so trying to hazard a best guess as to what happened. At this
pgsql-general@postgresql.org
max out your checkpoint_segments. 128 or 256 is good if you have plenty of spare space for the pg_xlog directory. you will also want to increase checkpoint_timeout to something large. I like 900 seconds personally. if you can put pg_xlog on a separate physical RAID you're best off. increase the amount of ram your index operations can use (sort_mem or work_mem depending on PG version) to a very large number and then reset that to a normal value when you're done. then just let pg_dump + pg_restore do its work. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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] Recovering db from cracked server
Richard Huxton wrote: > You're close - the only thing it was complaining about was the missing > "postgres" database. That's because it defaults to using the same > database as the username. Try >postgres -D copy_of_old_data_dir template1 > > Or use the name of your old database. Woohoo! Thanks a million; I've got my data back. Don Doumakes Email: doumakes at loganet.net Do not reply to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Seguimos buscando webdeveloper...
Aunque parezca increible, seguimos buscando webdeveloper, se ofrece buen sueldo y excelente ambiente de trabajo. Patojo, vos me dijiste que tenias a alguien, que paso? Si alguien mas esta interesado (el sueldo es en $) porfavor, porfavor, envienme un correo... Xaludos...
Re: [GENERAL] Emacs and postgres
Sean Davis <[EMAIL PROTECTED]> writes: > This is a bit off-topic > > Does anyone know of an interface between emacs and psql? I currently use it > as > my default editor and do my share of save and then \i. I just gave pgEdit a > try and liked many aspects of it, but I still like Emacs as an editor and > wondered if anyone else has tricks/plugins for emacs users. You could just set EDITOR to emacsclient and run M-x server-start and use \e. Personally I just use psql directly under something like shell-mode. That doesn't give me completion but it gives me a history and editing. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] multiple runs of the same query cause out of memory - WAS [Re: capturing/viewing sort_mem utilization on a per query basis]
List & Tom, Following up on this thread from a weeks ago: http://archives.postgresql.org/pgsql-general/2005-02/msg00106.php We have found that this query can indeed cause terrible things to happen to postgresql - it can run several times with response times of a few seconds, and then the next time cause postgres to go out of control on memory usage. (I've had to reboot my machine twice to get control). On a beefier machine with more memory and two CPUs, the query worked okay for a few dozen repetitions (with a few in parallel), then eventually one instance would cause the postgres process to spiral out of control and consume more and more memory at the rate of a megabyte every second or so. I'm not quite sure where to go from here, but this is definitely reproducable now. Help?! -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] multiple runs of the same query cause out of memory - WAS [Re: capturing/viewing sort_mem utilization on a per query basis]
On Wed, 23 Feb 2005 19:56:56 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Lonni J Friedman <[EMAIL PROTECTED]> writes: > > We have found that this query can indeed cause terrible things to > > happen to postgresql - it can run several times with response times of > > a few seconds, and then the next time cause postgres to go out of > > control on memory usage. (I've had to reboot my machine twice to get > > control). On a beefier machine with more memory and two CPUs, the > > query worked okay for a few dozen repetitions (with a few in > > parallel), then eventually one instance would cause the postgres > > process to spiral out of control and consume more > > and more memory at the rate of a megabyte every second or so. > > > I'm not quite sure where to go from here, but this is definitely > > reproducable now. Help?! > > That's a bit hard to believe --- if nothing is changing, the query > should get processed the same way every time. Its definitely not the same every time after this additional testing. > > Can you package up a test case for other people to look at? I could try, but i'm not sure what you'd need. Right now, i can only replicate this behavior with a snapshot of this one customer's DB. Running on the same schema, with little or no data, it always completes fine, so it looks to be partially data driven in nature, or perhaps its the volume of data. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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] multiple runs of the same query cause out of memory - WAS [Re: capturing/viewing sort_mem utilization on a per query basis]
Lonni J Friedman <[EMAIL PROTECTED]> writes: > We have found that this query can indeed cause terrible things to > happen to postgresql - it can run several times with response times of > a few seconds, and then the next time cause postgres to go out of > control on memory usage. (I've had to reboot my machine twice to get > control). On a beefier machine with more memory and two CPUs, the > query worked okay for a few dozen repetitions (with a few in > parallel), then eventually one instance would cause the postgres > process to spiral out of control and consume more > and more memory at the rate of a megabyte every second or so. > I'm not quite sure where to go from here, but this is definitely > reproducable now. Help?! That's a bit hard to believe --- if nothing is changing, the query should get processed the same way every time. Can you package up a test case for other people to look at? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ideally a non-volatile function?
Noel Whelan wrote: The information on what 'volatile' indicates with a function includes the following: 'VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday().' I've got a function that I would like to be optimized if it could be; but which currently is identified as volatile (I never indicated otherwise when creating it). I would just like to be clearer on what the effects could be of changing this before I do. If your function is written in plpgsql, you it should be always safe to mark it STABLE (instead of VOLATLE), if the following conditions are met .) Your function doesn't use functions marked VOLATILE .) You don't use "select ... limit x" without also including an "order by" clause. VOLATILE means that the return value of the function is in a way "unpredictable" - the function may return different return values, even if it is passed the same arguments, and sees the same data in the database. The function I've got is essentially based on a view which gives back info from a variety of tables on an item with an ID of 1. I created the view itself only in order to give a 'format' to the value I get back from this function, in which I create a variable that's given that view as a type. The other variable is an integer which is the ID of the item I would like information on (instead of '1'). I'm not certain, firstly, whether the 'function value' in this case would be the integer passed to the function, or the value it gives back. If the former, it will not change within the function; but it could if it's the latter of these, because in the query I get a few pieces of info with an 'offset 0 limit 1' in the where clause in order to identify only the latest entry (based on an order by date/time). If the item with the ID passed to the function were being edited while the function itself was executing, this value could change; but I can imagine no other ill effects (it would just give back a value that was incorrect; but very infrequently and therefore a non-issue, imho). STABLE (as opposed to VOLATILE) only means that your function returns the same result when passed the same parameters _and_ the database has not changed (or the changes are not visible to you), as far as I know. You should therefore be able to mark your function STABLE. greetings, Florian Pflug ---(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] [PATCHES] A way to let Vacuum warn if FSM settings are low.
Ron Mayer <[EMAIL PROTECTED]> writes:
> +if (needed > MaxFSMPages)
> +ereport(WARNING,
> +(errmsg("max_fsm_pages(%d) is smaller than total pages
> needed(%.0f)",
> + MaxFSMPages, needed)));
An unconditional WARNING seems a bit strong to me for a case that is not
necessarily wrong. Depending on the needs of the installation, this
might be a perfectly acceptable situation --- for example if you have
lots of large read-mostly tables.
On the other side of the coin, the test could pass (ie no warning) in
situations where in fact MaxFSMPages is too small, because what we are
comparing it to is the number of pages requested for relations that are
being tracked. If MaxFSMRelations is too small then we can't really
tell whether MaxFSMPages is adequate.
> +if (numRels > MaxFSMRelations)
> +ereport(WARNING,
> +(errmsg("max_fsm_relations(%d) is smaller than the number of
> relations (%d)",
> + MaxFSMRelations, numRels)));
This part is just plain dead code, since it's not possible for numRels
to exceed MaxFSMRelations.
I think it might be useful to warn when numRels == MaxFSMRelations,
since if you don't have even one spare fsmrel slot then you probably
have too few (it's unlikely you got it on the nose). But I don't know
how to produce a warning about MaxFSMPages that's worth anything.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] stats collector appears to be dying
Shelby Cain <[EMAIL PROTECTED]> writes: > I'm having an issue with what appears to be the stats > collector process dying on Postgresql 8.0.1 running on > Windows XP w/sp1. > 2005-02-23 14:59:17 FATAL: could not read from statistics collector pipe: No > such file or directory > 2005-02-23 14:59:17 LOG: statistics collector process (PID 2748) was > terminated by signal 1 Odd. We probably shouldn't take the message entirely at face value because of the error code mapping that happens under Windows. Looking at backend/port/win32/error.c, there seem to be several Windows codes that are translated to ENOENT: ERROR_FILE_NOT_FOUND ERROR_PATH_NOT_FOUND ERROR_INVALID_DRIVE ERROR_NO_MORE_FILES ERROR_BAD_NETPATH ERROR_BAD_NET_NAME ERROR_BAD_PATHNAME ERROR_FILENAME_EXCED_RANGE I wonder if any of those throws more light on it ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] view/pgpgsql functions bug
Joe Maldonado <[EMAIL PROTECTED]> writes: > After a create or replace view, the new view definition is not being > used by plpgsql functions that use the view. Is this a known bug ? Is > there a workaround it ? Start a fresh backend session. The old query plan is presumably being cached by plpgsql. (Yes, this is something we'd like to fix.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Emacs and postgres
> "Sean" == Sean Davis <[EMAIL PROTECTED]> writes: Sean> Does anyone know of an interface between emacs and psql? I currently Sean> use it as my default editor and do my share of save and then \i. I Sean> just gave pgEdit a try and liked many aspects of it, but I still like Sean> Emacs as an editor and wondered if anyone else has tricks/plugins for Sean> emacs users. sql-mode works fairly nice, and has a postgres submode. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(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] library files
Mohsen Pahlevanzadeh wrote: > Dears,Now i can't link any library to my executable file,Please guide > me that i can use mysql along with pgsql.(mysql needs to add library > to executable file) Yours,Mohsen Have you read the section of the documentation dealing with building C programs to talk with PostgreSQL? See section 27.15. Building libpq Programs. Seems pretty clearly laid out there. -- Guy Rouillier ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] A way to let Vacuum warn if FSM settings are low.
Short summary:
I find this tiny (9-line) patch useful to help my clients know
when FSM settings may need updating.
Some of the more frequently asked questions here are in regards to FSM
settings. One hint I've seen is to run "vacuum verbose;". At the end
of thousands of lines of INFO and DETAIL messages vacuum verbose has 2
separate lines with some numbers to compare ("total pages needed" and
"FSM size...pages") that help indicate too low fsm settings.
I've gotten into the habit of always installing the following patch
(below) that automatically does this comparison for me, and if
max_fsm_pages is too small, it logs a warning as shown here:
patched=# vacuum;
WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832)
VACUUM
I find this much nicer than the existing output (
clean=# vacuum verbose;
[. thousands of lines of INFO and DETAIL messages ]
INFO: free space map: 77 relations, 470 pages stored; 2832 total pages needed
DETAIL: Allocated FSM size: 100 relations + 1601 pages = 19 kB shared memory.
) for many reasons:
* First, because it's a warning, lots of people will notice it before
their asking the FAQ again.
* Second, because all the information is on a single line and actually
contains the string "max_fsm_relations", it gives people a clue what
to do about it. (note that vacuum verbose uses similar phrases but
from the number of questions here, it must not be obvious)
* Third, I don't need the 'verbose' setting.
* And most importantly, our clients let us know about WARNINGs,
but not about INFOs or DETAILs in their log page; so it gives
us a chance to respond before their system drags to a halt.
If a patch like this could get into the standard distro, that'd be
awesome - just let me know what additional work is needed (I didn't
look at docs or internationalization yet). If not, I'd like to post
it here to patches just in case anyone else will benefit from the
same thing.
==
% diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c
postgresql-patched/src/backend/storage/freespace/freespace.c
--- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31
14:00:54.0 -0800
+++ postgresql-patched/src/backend/storage/freespace/freespace.c2005-02-23
14:58:50.638745744 -0800
@@ -704,6 +704,15 @@
/* Convert stats to actual number of page slots needed */
needed = (sumRequests + numRels) * CHUNKPAGES;
+
+if (needed > MaxFSMPages)
+ereport(WARNING,
+(errmsg("max_fsm_pages(%d) is smaller than total pages
needed(%.0f)",
+ MaxFSMPages, needed)));
+if (numRels > MaxFSMRelations)
+ereport(WARNING,
+(errmsg("max_fsm_relations(%d) is smaller than the number of
relations (%d)",
+ MaxFSMRelations, numRels)));
ereport(elevel,
(errmsg("free space map: %d relations, %d pages stored; %.0f total
pages needed",
==
Thoughts?
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] Emacs and postgres
Joe Healy wrote: Sean Davis wrote: This is a bit off-topic Does anyone know of an interface between emacs and psql? I currently use it as my default editor and do my share of save and then \i. I just gave pgEdit a try and liked many aspects of it, but I still like Emacs as an editor and wondered if anyone else has tricks/plugins for emacs users. I have used sql-postgres mode in the past, under both emacs and xemacs. It is quite good. Haven't got it working currently under windows, I think I need to tell it where i have a psql binary. I have now told emacs where my psql binary is, but I do not get the prompt. Does anyone know how to change this? Thanks Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] library files
Dears,Now i can't link any library to my executable file,Please guide me that i can use mysql along with pgsql.(mysql needs to add library to executable file) Yours,Mohsen ---(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] PQsetdb
On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > pgsql_LIBS=-L/usr/lib You need to specify to link to an actual library here (probably -lpq at least). Just giving a library directory isn't going to be good enough. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] stats collector appears to be dying
--- "Matthew T. O'Connor" wrote: > Windows firewall perhaps? > I double checked to make sure it was disabled. Regardless, if a firewall were preventing the connection from occuring I'm fairly certain the error would be something other than "target machine actively refused" which implies trying to connect() to a port that is open but doesn't have a listen socket bound to it. Anyway, the first line in my log mentioning the stats collector process is the following: "2005-02-23 14:59:17 FATAL: could not read from statistics collector pipe: No such file or directory" I suspect that is the critical clue as to what might be happening on this box.This is a default installation with a few deviations from the supplied default config. Any other ideas? Regards, Shelby Cain __ 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] PQsetdb
My Makefile is : mysql_INCS=-I/usr/include/mysql pgsql_INCS=-I/usr/include pgsql_LIBS=-L/usr/lib mysql_LIBS=-L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm CXX=g++ sql2sql : sql2sql.o $(CXX) $(mysql_LIBS) $(pgsql_LIBS) -o sql2sql sql2sql.o ; rm -rf sql2sql.o ; sql2sql.o : sql2sql.cpp $(CXX) -c $(mysql_INCS) $(pgsql_INCS) sql2sql.cpp; clean : rm -rf sql2sql.o rm -rf sql2sql ~ > [EMAIL PROTECTED] > > On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > >> If you see my orginal email,I mentioned to link to inc & lib for >> pgsql.Even when i use PQsetdb,I didn't recieve error message for using >> PQsetdb. > > > The fragment of execution of make you gave had no mention of them in the > commandlines shown. What does your Makefile look like? > > > ---(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] Emacs and postgres
Hi Sean, On Feb 23, 2005, at 4:19 PM, Sean Davis wrote: Does anyone know of an interface between emacs and psql? I currently use it as my default editor and do my share of save and then \i. I just gave pgEdit a try and liked many aspects of it, but I still like Emacs as an editor and wondered if anyone else has tricks/plugins for emacs users. Thanks for trying pgEdit. I know it can't ever be emacs, but let me know if there are some particular things in the emacs compatibility features that add, improve, or fix. I'm keeping a blog now if you are interested in following pgEdit design and development. http://pgedit.com/blog/1 John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Emacs and postgres
Sean Davis wrote: This is a bit off-topic Does anyone know of an interface between emacs and psql? I currently use it as my default editor and do my share of save and then \i. I just gave pgEdit a try and liked many aspects of it, but I still like Emacs as an editor and wondered if anyone else has tricks/plugins for emacs users. I have used sql-postgres mode in the past, under both emacs and xemacs. It is quite good. Haven't got it working currently under windows, I think I need to tell it where i have a psql binary. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PQsetdb
[EMAIL PROTECTED] On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > If you see my orginal email,I mentioned to link to inc & lib for > pgsql.Even when i use PQsetdb,I didn't recieve error message for using > PQsetdb. The fragment of execution of make you gave had no mention of them in the commandlines shown. What does your Makefile look like? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] stats collector appears to be dying
Windows firewall perhaps? Shelby Cain wrote: I'm having an issue with what appears to be the stats collector process dying on Postgresql 8.0.1 running on Windows XP w/sp1. I've enabled stats_command_string and stats_row_level in my config file. During bulk inserts the stats collector process appears to encounter some sort of problem and shuts down. The end result is the log file being spammed with "FATAL" entries regarding the process. I've attached a small excerpt from my log files. Any ideas as to what is going on? Regards, Shelby Cain __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] PQsetdb
If you see my orginal email,I mentioned to link to inc & lib for pgsql.Even when i use PQsetdb,I didn't recieve error message for using PQsetdb. Yours,Mohsen > > On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > >> My program is using both MySQL & PostgreSQL.I don't have problem with >> Mysql.But i have problem with pgsql. > > That could be because you don't appear to be linking to the PostgreSQL > libraries (as Richard was hinting at). > >> > Mohsen Pahlevanzadeh wrote: >> >> I recieve following error: >> >> [EMAIL PROTECTED] sql2sql]# make >> >> g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; >> > ^^ >> >> g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib >> -o >> > ^ ^^^ >> > Those aren't going to help, are they? >> > >> > -- >> >Richard Huxton >> >Archonet Ltd >> > >> >> >> ---(end of broadcast)--- >> TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org >> > ---(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] PQsetdb
Mohsen Pahlevanzadeh wrote: > My program is using both MySQL & PostgreSQL.I don't have problem with > Mysql. But i have problem with pgsql. I think Richard's point is that you are not linking in any PostgreSQL libs. >> Mohsen Pahlevanzadeh wrote: >>> I recieve following error: >>> [EMAIL PROTECTED] sql2sql]# make >>> g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; >> ^^ >>> g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib >>> g++ -o >> ^ ^^^ >> Those aren't going to help, are they? >> >> -- >>Richard Huxton >>Archonet Ltd >> -- Guy Rouillier ---(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] PQsetdb
On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > My program is using both MySQL & PostgreSQL.I don't have problem with > Mysql.But i have problem with pgsql. That could be because you don't appear to be linking to the PostgreSQL libraries (as Richard was hinting at). > > Mohsen Pahlevanzadeh wrote: > >> I recieve following error: > >> [EMAIL PROTECTED] sql2sql]# make > >> g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; > > ^^ > >> g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -o > > ^ ^^^ > > Those aren't going to help, are they? > > > > -- > >Richard Huxton > >Archonet Ltd > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(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] stats collector appears to be dying
I'm having an issue with what appears to be the stats collector process dying on Postgresql 8.0.1 running on Windows XP w/sp1. I've enabled stats_command_string and stats_row_level in my config file. During bulk inserts the stats collector process appears to encounter some sort of problem and shuts down. The end result is the log file being spammed with "FATAL" entries regarding the process. I've attached a small excerpt from my log files. Any ideas as to what is going on? Regards, Shelby Cain __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail psql.log.gz Description: psql.log.gz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Emacs and postgres
This is a bit off-topic Does anyone know of an interface between emacs and psql? I currently use it as my default editor and do my share of save and then \i. I just gave pgEdit a try and liked many aspects of it, but I still like Emacs as an editor and wondered if anyone else has tricks/plugins for emacs users. Sean ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ADO and timestamp/date errors
Hi Craig,
Out of curiosity, beside portability, why haven't you use Npgsql?
regards,
-
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 23, 2005, at 1:48 PM, Craig Bryden wrote:
Hi Shachar
Unfortunately due to a Non disclose agreement that I have on the
project, I
cannot send the whole table creation statement. But I have included
some
fields below:
CREATE TABLE tb_Player (
PlayerID SERIAL NOT NULL PRIMARY KEY ,
Firstname varchar (100) NOT NULL ,
Lastname varchar (100) NOT NULL ,
Initials varchar (15) NULL ,
EMail varchar (255) NOT NULL ,
DateCreated timestamp NOT NULL DEFAULT current_timestamp
) WITHOUT OIDS;
The C# code is:
string connString = "Location=MyDB;Provider=\"PostgreSQL.1\";User
ID=XXX;Data Source=localhost;Extended Properties=;Password=XXX";
System.Data.OleDb.OleDbConnection dbConn = new
System.Data.OleDb.OleDbConnection(connString);
dbConn.Open();
DSet1 = new DataSet();
System.Data.OleDb.OleDbCommand comm = new
System.Data.OleDb.OleDbCommand("select datecreated from
pr_GetPlayerByID(2500 )",dbConn);
System.Data.OleDb.OleDbDataAdapter da = new
System.Data.OleDb.OleDbDataAdapter(comm);
DataTable dt = new DataTable("Table1");
DSet1.Tables.Add(dt);
da.Fill(DSet1.Tables["Table1"]);
dgResults.DataSource = DSet1;
dgResults.DataMember = "Table1";
The type that is returned by the function is:
CREATE TYPE pr_getplayerbyid_returntype AS
(playerid int4,
firstname varchar(100),
lastname varchar(100),
initials varchar(15),
email varchar(255),
datecreated timestamp);
Thanks
Craig
- Original Message -
From: "Shachar Shemesh" <[EMAIL PROTECTED]>
To: "Craig Bryden" <[EMAIL PROTECTED]>
Cc: "pgsql"
Sent: Tuesday, February 22, 2005 9:26 PM
Subject: Re: [GENERAL] ADO and timestamp/date errors
Craig Bryden wrote:
Hi Shachar
I have confirmed that I am running the latest version.
The error that I get (in ADO .Net) is :
***
*
**
An unhandled exception of type 'System.ArgumentOutOfRangeException'
occurred in system.data.dll
Additional information:
Specified argument was out of the range of valid values.\r\nParameter
name: Year, Month, and Day parameters describe an unrepresentable
DateTime
Can you please send me the table creation command, and the command
that
failed? It sounds to me like a bug in the time handling by the OLE DB.
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html
---(end of
broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] PQsetdb
My program is using both MySQL & PostgreSQL.I don't have problem with Mysql.But i have problem with pgsql. > Mohsen Pahlevanzadeh wrote: >> I recieve following error: >> [EMAIL PROTECTED] sql2sql]# make >> g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; > ^^ >> g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -o > ^ ^^^ > Those aren't going to help, are they? > > -- >Richard Huxton >Archonet Ltd > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PQsetdb
Mohsen Pahlevanzadeh wrote: I recieve following error: [EMAIL PROTECTED] sql2sql]# make g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; ^^ g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -o ^ ^^^ Those aren't going to help, are they? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] PQsetdb
Dears,I have added following constructor to my class:
class CPgsql : public CCore
{
public:
char *pghost,
*pgport,
*pgoptions,
*pgtty;
char *dbName;
PGconn *connection2db;
const char * conninfo;
CPgsql(const char *dbname);
};
CPgsql::CPgsql(const char *dbname)
{
pghost=NULL;
pgport=NULL;
pgoptions=NULL;
pgtty=NULL;
dbName=(char*)dbname;
connection2db=PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
}//end of constructor
But when i call it ,for example :
CPgsql p("x");
I recieve following error:
[EMAIL PROTECTED] sql2sql]# make
g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp;
g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -o
sql2sql sql2sql.o ;
sql2sql.o(.text+0x6c): In function `CPgsql::CPgsql[not-in-charge](char
const*)':
: undefined reference to `PQsetdbLogin'
sql2sql.o(.text+0x10e): In function `CPgsql::CPgsql[in-charge](char const*)':
: undefined reference to `PQsetdbLogin'
collect2: ld returned 1 exit status
make: *** [sql2sql] Error 1
Please help me..
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] ADO and timestamp/date errors
Hi Shachar
Unfortunately due to a Non disclose agreement that I have on the project, I
cannot send the whole table creation statement. But I have included some
fields below:
CREATE TABLE tb_Player (
PlayerID SERIAL NOT NULL PRIMARY KEY ,
Firstname varchar (100) NOT NULL ,
Lastname varchar (100) NOT NULL ,
Initials varchar (15) NULL ,
EMail varchar (255) NOT NULL ,
DateCreated timestamp NOT NULL DEFAULT current_timestamp
) WITHOUT OIDS;
The C# code is:
string connString = "Location=MyDB;Provider=\"PostgreSQL.1\";User
ID=XXX;Data Source=localhost;Extended Properties=;Password=XXX";
System.Data.OleDb.OleDbConnection dbConn = new
System.Data.OleDb.OleDbConnection(connString);
dbConn.Open();
DSet1 = new DataSet();
System.Data.OleDb.OleDbCommand comm = new
System.Data.OleDb.OleDbCommand("select datecreated from
pr_GetPlayerByID(2500 )",dbConn);
System.Data.OleDb.OleDbDataAdapter da = new
System.Data.OleDb.OleDbDataAdapter(comm);
DataTable dt = new DataTable("Table1");
DSet1.Tables.Add(dt);
da.Fill(DSet1.Tables["Table1"]);
dgResults.DataSource = DSet1;
dgResults.DataMember = "Table1";
The type that is returned by the function is:
CREATE TYPE pr_getplayerbyid_returntype AS
(playerid int4,
firstname varchar(100),
lastname varchar(100),
initials varchar(15),
email varchar(255),
datecreated timestamp);
Thanks
Craig
- Original Message -
From: "Shachar Shemesh" <[EMAIL PROTECTED]>
To: "Craig Bryden" <[EMAIL PROTECTED]>
Cc: "pgsql"
Sent: Tuesday, February 22, 2005 9:26 PM
Subject: Re: [GENERAL] ADO and timestamp/date errors
> Craig Bryden wrote:
>
> > Hi Shachar
> >
> > I have confirmed that I am running the latest version.
> > The error that I get (in ADO .Net) is :
> >
**
> > An unhandled exception of type 'System.ArgumentOutOfRangeException'
> > occurred in system.data.dll
> >
> > Additional information:
> > Specified argument was out of the range of valid values.\r\nParameter
> > name: Year, Month, and Day parameters describe an unrepresentable
DateTime
>
> Can you please send me the table creation command, and the command that
> failed? It sounds to me like a bug in the time handling by the OLE DB.
>
> Shachar
>
> --
> Shachar Shemesh
> Lingnu Open Source Consulting ltd.
> Have you backed up today's work? http://www.lingnu.com/backup.html
>
>
>
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] view/pgpgsql functions bug
After a create or replace view, the new view definition is not being used by plpgsql functions that use the view. Is this a known bug ? Is there a workaround it ? For instance, selecting from afunc() still returns the old view's results. create table c ( a int ); create or replace view a as select * from c; insert into c values (1); create or replace function afunc() returns integer AS' declare val int; begin select into val a from A; return val; end; ' language 'plpgsql'; select * from afunc(); afunc --- 1 (1 row) create table d ( a int ); create or replace view a as select * from d; insert into d values (2); select * from afunc(); afunc --- 1 (1 row) Thanks. ---(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] Store procedure -------------- !!!!!!!!!!!!!! URGENTE """""""""""""""
i guess you can create a simple sql function: http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html or more complex pl/pgsql functions http://www.postgresql.org/docs/8.0/interactive/xplang.html On Tue, 22 Feb 2005 14:37:21 -0300, Adelson - Informática <[EMAIL PROTECTED]> wrote: > > Por favor gostaria de saber como utilizo store procedure no postgre ... > > não quero retornar valor , apenas que o banco de dados me execute uma > instrução ... > > Exemplo : insert into cliente (cnp,nom) values ( '21231231','CLIENTE TESTE') > > sem mais, > > Adelson > > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] reltuples < # of rows
Eric Brown <[EMAIL PROTECTED]> writes: > I thought that the number of tuples in a table must be greater than the > number of rows? For one of my tables, I have the following: > # analyze t_stats; > ANALYZE > siteserverdb=# select reltuples, relpages from pg_class where relname = > 't_stats'; > reltuples | relpages > ---+-- >1760 |21119 > (1 row) > siteserverdb=# select count(*) from t_stats; > count > --- >1861 > (1 row) > How is this possible? ANALYZE produces only an estimated reltuples. To be exact it would have to scan the entire table. regards, tom lane ---(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] Rephrased: Possible to run the server without backslash (C-style) escaping?
More specifically, I'd like to disable the non-standard backslash
escaping behavior of the server, so that backslashes aren't discarded, like:
insert into table values ('This won''t work when inserting a \
character'); -> 'This won't work when inserting a character'
Instead I'd like for only single quotes (apostrophes) to server as the
escape character (for themselves), as in the iso/ansi recommendations
-so that the server behaves the same as Oracle, ms, and most other
databases in this regard.
I am living testament to all the users who switched from the 'big'
databases to pg and had the unpleasant surprise of having lost all the
backslashes chars -as well as having to tweak apps to get them to work.
If nothing else, a VERY prominently placed warning on the docs front
page should warn new users of the nonstandard behavior, imo. This can be
a life saver.
ken
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Help with a very newbie question...
On Wed, 23 Feb 2005, Cristian Prieto wrote: > RETURNS SETOF Users gave me: > > getuser > - > (cristian,hola) > Yes, it's ok, but for SETOF function You have to use different style of calling SELECT * FROM getuser(...). If function call in normal centext then SRF function returns only one column. ps ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] reltuples < # of rows
I thought that the number of tuples in a table must be greater than the number of rows? For one of my tables, I have the following: # analyze t_stats; ANALYZE siteserverdb=# select reltuples, relpages from pg_class where relname = 't_stats'; reltuples | relpages ---+-- 1760 |21119 (1 row) siteserverdb=# select count(*) from t_stats; count --- 1861 (1 row) How is this possible? I'm running postgres 8.0 on a redhat ws3. Clearly I'm not vacuuming enough, but that seems to be a separate issue to me. Am I seeing data corruption? Thanks, Eric Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] equivalent in postgres of over(partition) function in db2
Hi, I have the following query in db2: "insert into xyz (a,b) select 10, MIN(CM_LEDGERITEM.tril_gid) OVER(PARTITION BY CM_LEDGERITEM.MMPAYEE, CM_PROMOTION.MMCOMPTYPE, CM_LEDGERITEM.MODEL) from " I am not able to find a corresponding thing for min(...) over(partition by col1, col2, col3..) in postgres.What will be its equivalent in postgres?? Regards, Vinita Bansal _ Manage information better. Optimise your tasks. http://www.microsoft.com/india/office/experience/ Experience MS Office System. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Recovering db from cracked server
"Don Doumakes" <[EMAIL PROTECTED]> writes: > latest version of postgresql is incompatible with 7.2.x, so I compiled > postgresql-7.2.7 and attempted to run > postgres -D copy_of_old_data_dir You probably wanted to say "postmaster" not "postgres". The quoted result is about what I'd expect from trying to run a standalone backend with the wrong arguments ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Help with a very newbie question...
RETURNS SETOF Users gave me:
getuser
-
(cristian,hola)
- Original Message -
From: "Richard Huxton"
To: "Pavel Stehule" <[EMAIL PROTECTED]>
Cc: "Cristian Prieto" <[EMAIL PROTECTED]>;
Sent: Wednesday, February 23, 2005 10:34 AM
Subject: Re: [GENERAL] Help with a very newbie question...
Pavel Stehule wrote:
If I did the following:
SELECT * FROM "Users";
I get:
userid | passwd
--+
cristian | hola
AND I execute:
SELECT getuser('cristian', 'hola');
I get:
getuser
-
(cristian,hola)
There are two posibilities
SELECT getuser('cristian','hola');
SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd
varchar);
Or, define your function as ... RETURNS SETOF Users
--
Richard Huxton
Archonet Ltd
---(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] Help with a very newbie question...
Pavel Stehule wrote:
If I did the following:
SELECT * FROM "Users";
I get:
userid | passwd
--+
cristian | hola
AND I execute:
SELECT getuser('cristian', 'hola');
I get:
getuser
-
(cristian,hola)
There are two posibilities
SELECT getuser('cristian','hola');
SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd
varchar);
Or, define your function as ... RETURNS SETOF Users
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with a very newbie question...
>
> If I did the following:
> SELECT * FROM "Users";
> I get:
>
> userid | passwd
> --+
> cristian | hola
>
> AND I execute:
> SELECT getuser('cristian', 'hola');
>
> I get:
> getuser
> -
> (cristian,hola)
>
There are two posibilities
SELECT getuser('cristian','hola');
SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd
varchar);
Regards
Pavel
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Possible to run the server with ANSI/ISO string escapeing instead of C-style escapes?
Greetings, Does anyone know if it will be possible to run the server with ANSI/ISO string escaping instead of C-style escapes? The C style escaping is a shoot-down for our adoption of postgres, since its non-standard. Thanks, ken ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simple client messages from within pgPL/SQL
> This may be too simplistic an answer, but can't you just have the function > return a text string and return the message you want to deliver back to the > client? Thanks for your reply. However, this won't work because the INFO messages are not the result or the function. They are a notification about the procedure's progress (which is running for hours in the worst case). And there are many different of them, indicating what's going on. Andre ---(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] Help with a very newbie question...
In my table
userid | passwd
--+
cristian | hola
If I did the following:
SELECT * FROM "Users";
I get:
userid | passwd
--+
cristian | hola
But If I create the following Function
CREATE OR REPLACE FUNCTION getuser(varchar, varchar) RETURNS SETOF RECORD AS
$body$
SELECT * FROM "Users" WHERE userid=$1 AND passwd=$2;
$body$
LANGUAGE SQL;
AND I execute:
SELECT getuser('cristian', 'hola');
I get:
getuser
-
(cristian,hola)
What is wrong? I need to return the data as a simple SELECT * FROM "Users"
but inside a SP, how could I do it?
- Original Message -
From: "Jan Poslusny"
To: "Cristian Prieto" <[EMAIL PROTECTED]>
Cc:
Sent: Wednesday, February 23, 2005 8:36 AM
Subject: Re: [GENERAL] Help with a very newbie question...
1.
Plpgsql-function can return record (or set of records).
Sql-function returns result of the last query in it's body.
2.
I prefer simple solutions, though I write sql-functions whenever
requirement likes as "parametrized view". But I know that in some special
situations plpgsql-function with clever loops may have better performance
than simple sql-function with very complicated joins.
3.
Could you explain your question it in some example?
Cristian Prieto wrote:
Well, I will ask a little more clear:
1. A function could be declare to return a RECORD value? (the manual
doesn't put it so clear, mention only simple return values and
declaration values, not return values).
2. It will be better declare that function as a parametrized view or as a
pgsql function?
3. If the record value is not a right return value, what kind of return
value could I use for it?
Thanks a lot for your help...
- Original Message - From: "Jan Poslusny"
To: "Cristian Prieto" <[EMAIL PROTECTED]>
Cc:
Sent: Wednesday, February 23, 2005 6:44 AM
Subject: Re: [GENERAL] Help with a very newbie question...
I think that sql-functions may serve as parametrized views for you...
http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
Cristian Prieto wrote:
I want to create a view or a sp which returns NULL if nothing is found
and a recordset if the user is found
I wrote something like:
CREATE sp_getuser(name, pass) RETURNS record AS
$body$
DECLARE
retval RECORD;
BEGIN
SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass;
IF NOT FOUND THEN
RETURN NULL;
ELSE
RETURN retval;
END;
$body$
LANGUAGE plpgsql;
What is wrong with that function? I guess I could create it as a View
but I don't know how to pass parameters in a view, somebody could help
me with this?
Thanks a lot!
---(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 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Help with a very newbie question...
1. Plpgsql-function can return record (or set of records). Sql-function returns result of the last query in it's body. 2. I prefer simple solutions, though I write sql-functions whenever requirement likes as "parametrized view". But I know that in some special situations plpgsql-function with clever loops may have better performance than simple sql-function with very complicated joins. 3. Could you explain your question it in some example? Cristian Prieto wrote: Well, I will ask a little more clear: 1. A function could be declare to return a RECORD value? (the manual doesn't put it so clear, mention only simple return values and declaration values, not return values). 2. It will be better declare that function as a parametrized view or as a pgsql function? 3. If the record value is not a right return value, what kind of return value could I use for it? Thanks a lot for your help... - Original Message - From: "Jan Poslusny" To: "Cristian Prieto" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, February 23, 2005 6:44 AM Subject: Re: [GENERAL] Help with a very newbie question... I think that sql-functions may serve as parametrized views for you... http://www.postgresql.org/docs/8.0/static/xfunc-sql.html Cristian Prieto wrote: I want to create a view or a sp which returns NULL if nothing is found and a recordset if the user is found I wrote something like: CREATE sp_getuser(name, pass) RETURNS record AS $body$ DECLARE retval RECORD; BEGIN SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass; IF NOT FOUND THEN RETURN NULL; ELSE RETURN retval; END; $body$ LANGUAGE plpgsql; What is wrong with that function? I guess I could create it as a View but I don't know how to pass parameters in a view, somebody could help me with this? Thanks a lot! ---(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 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] Help with a very newbie question...
Well, I will ask a little more clear: 1. A function could be declare to return a RECORD value? (the manual doesn't put it so clear, mention only simple return values and declaration values, not return values). 2. It will be better declare that function as a parametrized view or as a pgsql function? 3. If the record value is not a right return value, what kind of return value could I use for it? Thanks a lot for your help... - Original Message - From: "Jan Poslusny" To: "Cristian Prieto" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, February 23, 2005 6:44 AM Subject: Re: [GENERAL] Help with a very newbie question... I think that sql-functions may serve as parametrized views for you... http://www.postgresql.org/docs/8.0/static/xfunc-sql.html Cristian Prieto wrote: I want to create a view or a sp which returns NULL if nothing is found and a recordset if the user is found I wrote something like: CREATE sp_getuser(name, pass) RETURNS record AS $body$ DECLARE retval RECORD; BEGIN SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass; IF NOT FOUND THEN RETURN NULL; ELSE RETURN retval; END; $body$ LANGUAGE plpgsql; What is wrong with that function? I guess I could create it as a View but I don't know how to pass parameters in a view, somebody could help me with this? Thanks a lot! ---(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 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] Help with a very newbie question...
I think that sql-functions may serve as parametrized views for you... http://www.postgresql.org/docs/8.0/static/xfunc-sql.html Cristian Prieto wrote: I want to create a view or a sp which returns NULL if nothing is found and a recordset if the user is found I wrote something like: CREATE sp_getuser(name, pass) RETURNS record AS $body$ DECLARE retval RECORD; BEGIN SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass; IF NOT FOUND THEN RETURN NULL; ELSE RETURN retval; END; $body$ LANGUAGE plpgsql; What is wrong with that function? I guess I could create it as a View but I don't know how to pass parameters in a view, somebody could help me with this? Thanks a lot! ---(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] Help with a very newbie question...
On Feb 23, 2005, at 6:14 AM, Andre Schnoor wrote:
CREATE sp_getuser(name, pass) RETURNS record AS
$body$
DECLARE
retval RECORD;
BEGIN
SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass;
IF NOT FOUND THEN
RETURN NULL;
ELSE
RETURN retval;
END;
$body$
LANGUAGE plpgsql;
Almost
First, note the declaration for the function--slightly different
arguments. Then, note the declare section--two new variables there to
replace those in the arguments. You need to END IFs everywhere.
Otherwise, looks good. Test given below:
create table users (
userid varchar,
passwd varchar);
CREATE TABLE
insert into users values('joe','joepass');
INSERT 156196622 1
insert into users values('susan','susanpass');
INSERT 156196623 1
CREATE OR REPLACE FUNCTION sp_getuser(varchar,varchar) RETURNS record
AS $$
DECLARE
retval RECORD;
name_lu ALIAS FOR $1;
pass_lu ALIAS FOR $2;
BEGIN
SELECT INTO retval * FROM Users WHERE userid=name_lu AND
passwd=pass_lu;
IF NOT FOUND THEN
RETURN NULL;
ELSE
RETURN retval;
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
select sp_getuser('joe','joepass');
sp_getuser
---
(joe,joepass)
(1 row)
select sp_getuser('joe','notjoepass');
sp_getuser
(1 row)
---(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] Help with a very newbie question...
>>> I wrote something like: CREATE sp_getuser(name, pass) RETURNS record AS $body$ DECLARE retval RECORD; BEGIN SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass; IF NOT FOUND THEN RETURN NULL; ELSE RETURN retval; END; $body$ LANGUAGE plpgsql; What is wrong with that function? I guess I could create it as a View but I don't know how to pass parameters in a view, somebody could help me with this? <<< END IF; is missing after the ELSE statement. I often make this mistake too. Andre ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Catching internal error ID's to throw custom errors.
[EMAIL PROTECTED] wrote: I have a lot of plpgsql functions and I would like to catch the original postgresql error and, based on that value, to throw with a raise or something my custom error. The section "Trapping Errors" in the plpgsql chapter of the manuals covers this. Does this not do what you want? -- 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
[GENERAL] Catching internal error ID's to throw custom errors. Is it possib le in PostGreSQL?
Title: Catching internal error ID's to throw custom errors. Is it possible in PostGreSQL? I have a lot of plpgsql functions and I would like to catch the original postgresql error and, based on that value, to throw with a raise or something my custom error. In SQL Server, for example, I have the sysmessages table in which I have all the possible values if the original errors. I catch that value (named @@error) and I call a function who have a parameter this value and who will return my custom value. This kind of error treatment permit me to have the same error ID's in Oracle, SQL Server and DB2, using a raise with the same error value. Is it possible to do the same in PostGreSQL using plpgsql language. Thanks in advanced. Stefan Ardeleanu Database Expert / Business Analyst SIVECO Romania SA Business Address: 8-10 Bd. Mareşal Averescu 71316 Bucureşti, Sector 1, România Web: http://www.siveco.ro Business 1: +40 (21) 224 2531 Business 2: +40 (21) 224 2577 Business Fax: +40 (21) 224 4108
Re: [GENERAL] Simple client messages from within pgPL/SQL
This may be too simplistic an answer, but can't you just have the function return a text string and return the message you want to deliver back to the client? On Wednesday 23 February 2005 05:00 am, Andre Schnoor saith: > > I'm desparately seeking for a simple way to send messages to the client > > during the execution of large stored procedures. > > > > RAISE INFO 'Now Processing %', thisCustomer; > > > > Doesn't do it, because the client console is cluttered with verbose > > context > > > information (e.g. backtrace). It's impossible to follow these "messages". > > > > I'm afraid that lowering the verbosity level in the server config will > > also > > > affect error messages (where verbosity is helpful). Is there another way > > to > > > output strings to the "console"? > > Example: > > > INFO: Total of 25130 listed songs found, 13046 of them did not perform > today > CONTEXT: SQL statement "SELECT sp_song_performance_reset( $1 )" > PL/pgSQL function "sp_song_performance_batch" line 41 at select into > variables > SQL statement "SELECT sp_song_performance_batch( $1 )" > PL/pgSQL function "sp_daily" line 39 at select into variables > SQL statement "SELECT sp_daily()" > PL/pgSQL function "sp_test_charts" line 14 at select into variables > > > I only want to have the first line appear and nothing else. > How do I suppress the CONTEXT clutter? > > > Andre > > > > ---(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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simple client messages from within pgPL/SQL
> I'm desparately seeking for a simple way to send messages to the client > during the execution of large stored procedures. > > RAISE INFO 'Now Processing %', thisCustomer; > > Doesn't do it, because the client console is cluttered with verbose context > information (e.g. backtrace). It's impossible to follow these "messages". > > I'm afraid that lowering the verbosity level in the server config will also > affect error messages (where verbosity is helpful). Is there another way to > output strings to the "console"? Example: INFO: Total of 25130 listed songs found, 13046 of them did not perform today CONTEXT: SQL statement "SELECT sp_song_performance_reset( $1 )" PL/pgSQL function "sp_song_performance_batch" line 41 at select into variables SQL statement "SELECT sp_song_performance_batch( $1 )" PL/pgSQL function "sp_daily" line 39 at select into variables SQL statement "SELECT sp_daily()" PL/pgSQL function "sp_test_charts" line 14 at select into variables I only want to have the first line appear and nothing else. How do I suppress the CONTEXT clutter? Andre ---(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] tsearch2 problems / limitations
On Tue, Feb 22, 2005 at 01:29:45PM +, James Croft wrote:
> One of the tables holds reasonable amounts of text, some fields hold up
> to 2Mb. When I try and run
>
> UPDATE table SET idxfti=to_tsvector('default', field);
>
> it runs for a while then aborts with the following message
>
> ERROR: value is too big
README.tsearch2 says that the maximum size of a tsvector is 2^20
bytes (1M). You can see that limit enforced in the makevalue()
function in tsvector.c (similarly in pushquery() in query.c):
if (cur - str > MAXSTRPOS)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("value is too big")));
MAXSTRPOS is defined in tsvector.h:
#define MAXSTRPOS ( 1<<20 )
Maybe Oleg will reply and say whether it's safe to change that
or not.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Different execution time from psql and JDBC
Richard Huxton wrote: Andreas Hartmann wrote: Dear postgresql community, I have a quite complex statement. When I execute it directly via psql, the execution time is approx. 2000 ms. When I execute it via JDBC (Apache Cocoon), the execution time is either 600..1000 ms or approx. 10.000 ms, based on a certain value in a table. An interesting point is that the value has a big impact on the JDBC execution time, but the psql execution time is not affected at all. This suggests to me the problem is with a parametered query. If in psql I have two queries: SELECT * FROM people WHERE surname='Huxton'; SELECT * FROM people WHERE surname='Smith'; If the statistics suggest there are many Smiths, then I might get two different plans. With a parameterised query: SELECT * FROM people WHERE surname=? The planner has to come up with one plan that will suit all cases. You can simulate this with PREPARE ... EXECUTE from psql - see if that does it. Thanks for your answer! Actually I found the cause of the two results differing so much. The query is executed using a set of values (IN (x1, x2, ..., xn)) I use a LIMIT..OFFSET query to get the value set to invoke the described query on. With psql, I used always the same set, but with the JDBC application the query was invoked on different sets, resulting in different execution times. When I repeated the test with psql using the correct data, I could reproduce the difference. But what bothers me is the overhead generated by the JDBC connection: JDBCpsql data set A 1000 ms 300 ms data set B 6000 ms 2000 ms The JDBC execution times are determined using profiling in my application. psql seems to be approx. 3 times faster than JDBC ... Can I do something about this? Is there a way to output the query plan (like EXPLAIN ANALYZE) in the log files? How can I trace down the problem? You can turn on DEBUG_PRINT_PARSE - see the runtime configuration for details. This doesn't exactly produce an EXPLAIN but it will let you compare the two plans. Yes, I've tried that, but the verbose query plan was too complicated. But you're right, I could at least use diff to compare them. Thanks again! -- Andreas ---(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] Recovering db from cracked server
Don Doumakes wrote: I own a server that was recently cracked by, I presume, an incompetent script kiddie. Minutes after he bravely tagged the web site, the server went down hard and would not boot. I've built a new system. I need to recover a postgresql 7.2 database from the old hard drive, which is still readable. The postgresql data directory is intact. The latest version of postgresql is incompatible with 7.2.x, so I compiled postgresql-7.2.7 and attempted to run postgres -D copy_of_old_data_dir The error response (on stdout) is DEBUG: database system was shut down at 2005-02-20 21:54:33 CST DEBUG: checkpoint record is at 0/5CCA0AC DEBUG: redo record is at 0/5CCA0AC; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 73360; next oid: 59092 DEBUG: database system is ready FATAL 1: Database "postgres" does not exist in the system catalog. DEBUG: shutting down DEBUG: database system is shut down Am I close? or is this approach doomed? Suggestions gratefully accepted. You're close - the only thing it was complaining about was the missing "postgres" database. That's because it defaults to using the same database as the username. Try postgres -D copy_of_old_data_dir template1 Or use the name of your old database. -- Richard Huxton Archonet Ltd ---(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] Different execution time from psql and JDBC
Andreas Hartmann wrote: Dear postgresql community, I have a quite complex statement. When I execute it directly via psql, the execution time is approx. 2000 ms. When I execute it via JDBC (Apache Cocoon), the execution time is either 600..1000 ms or approx. 10.000 ms, based on a certain value in a table. An interesting point is that the value has a big impact on the JDBC execution time, but the psql execution time is not affected at all. This suggests to me the problem is with a parametered query. If in psql I have two queries: SELECT * FROM people WHERE surname='Huxton'; SELECT * FROM people WHERE surname='Smith'; If the statistics suggest there are many Smiths, then I might get two different plans. With a parameterised query: SELECT * FROM people WHERE surname=? The planner has to come up with one plan that will suit all cases. You can simulate this with PREPARE ... EXECUTE from psql - see if that does it. Is there a way to output the query plan (like EXPLAIN ANALYZE) in the log files? How can I trace down the problem? You can turn on DEBUG_PRINT_PARSE - see the runtime configuration for details. This doesn't exactly produce an EXPLAIN but it will let you compare the two plans. -- 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] tsearch2 problems / limitations
On Tue, 22 Feb 2005, James Croft wrote:
Hi,
I've successfully added tsearch2 to an existing database and the speedup of
searches is brilliant. I'm now trying to extend this to other parts of our
system.
One of the tables holds reasonable amounts of text, some fields hold up to
2Mb. When I try and run
UPDATE table SET idxfti=to_tsvector('default', field);
it runs for a while then aborts with the following message
ERROR: value is too big
Does anyone know what the problem might be here? Does tsearch2 have
configurable (or compilable) settings that could get around this?
this is tsearch2 limitation on length of ts_vector about 1mb.
you may stop using postional information for this vector.
Many Thanks,
James
---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
