[GENERAL] Setting up replication
I have a question about setting up replication between my postgresql-9.3.6 servers. If I'm using pg_basebackup on my FreeBSD 10.1 slave server, the postgresql.conf file is in the data directory, which pg_basebackup insists must be empty. I can't find any info about how to relocate the postgresql.conf file and tell the init script its new location. If I setup PITR prior to replication setup and share the same postgresql.conf file transferred by pg_basebackup, how can both servers see the archive directory? It is local for the slave, nfs mount for the master. Obviously there is something I'm missing or not understanding, can anyone help? Thanks! -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using functions to calc field values in other table
I was wondering if it is possible to store a function in a table for
calculating a value for a field in another table. I am creating a sales
quote tool where users can pick a service to add to the quote_services
table when building the quote. I have a services table where some
services have a static price and others need to be a calculation. The
services table has a numeric price field to use when static pricing is
needed. Would it be possible to add a couple of other fields, say
service_options where I could enter the value 'calc' that would tell my
before TRIGGER on the quote_services table to use a function also stored
in the services table in another field called service_func? When someone
selects the service for the quote, it would execute the function to
calculate and enter the resulting price from the function.
My TRIGGER on the quote_services table would look something like this...
SELECT INTO aRec service_options, service_func FROM services WHERE
service_id = NEW.quote_service;
IF aRec.service_options = 'calc' THEN
NEW.service_price := aRec.service_func || '(' || NEW.service_id || ')';
END IF;
RETURN NEW;
I realize the syntax above may not work, just trying to get across my
idea and hope for some guidance how all this could be done, if possible.
Thanks for any pointers!
--
Robert
--
Robert rob...@webtent.org
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not load plperl library SOLVED
Paolo Saudin Wednesday, April 03, 2013 3:10 PM Try to check the perl version against Postgres version athttp://forums.enterprisedb.com/posts/list/3295.page Thanks! I'm using postgresql 9.2 and needed 5.14, luckily still available.
[GENERAL] could not load plperl library
I have a Windows XP laptop I've loaded postgres on for dev purposes. When I try to create the plperl language on a db, I get an error 'cannot load library' referencing the location where plperl.dll does exist. It has the lastest version of ActiveState Perl, but I remember (it's been a while) that I need an older version. I found a 5.8 installer, uninstalled the 5.16 and then installed the older 5.8, but still getting the error. I restarted the postgres service, do I need to reinstall? I used the Enterprise DB installer. Hope this is the right list for this, let me know if not, thanks -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Test for cascade delete in plpgsql
On 10/13/2011 5:45 PM, David Johnston wrote: the company record should not be visible if you execute a SELECT against the companies table using the given company_id value. The previous is not tested and I am not totally sure about the visibility rules in this situation (mainly whether the cascade delete occurs before or after the statement delete) Yes, you understood exactly what I am trying to do, and it appears the cascade delete occurs after, I didn't even think of that. If I PERFORM a query on the companies table to test if the record exists in the DELETE AFTER trigger of the contacts table and base my restriction on IF FOUND, the record is allowed to be deleted. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Test for cascade delete in plpgsql
My contacts table has a FK with cascade delete to foreign table companies using the company_id column. I have a DELETE AFTER trigger on my contacts table that checks to see if there are any contacts left with an email address or it won't allow you to delete the record for a company. However, if the company is being deleted, is there a way I can test for the cascade delete reason and have my trigger allow the contact to be deleted? --Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperl.dll on windows with postgresql 9.0.3
On 2/8/2011 12:03 PM, Robert Fitzpatrick wrote: On 2/8/2011 11:58 AM, Robert Fitzpatrick wrote: could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 Sorry the correct error I am getting is... could not load library “C:/Program Files/PostgreSQL/9.0/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 The previous was copied from a post found on the net when searching for solutions. Finally found the solution to this issue, it seems PostgreSQL is looking for perl510.dll, or earlier perhaps... http://stackoverflow.com/questions/4129479/strawberry-perl-5-12-as-postgresql-9-0s-plperl-on-win32 I also renamed my ActivePerl library from perl512.dll and perl510.dll to solve. Does this mean I may run into issues with the new Perl? I also tried 5.8.x and could not get plperl.dll installed with same error. -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl.dll on windows with postgresql 9.0.3
I am upgrading a Windows install from 8.2.x to 9.0.3. ActivePerl 5.8 was already installed, but when I run the createlang command to install into my database, I get... could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 I see the plperl.dll in that very location in the error. I looked at the functions of pg_catalog and the plperl functions are not there like plpgsql call handler, etc. I thought this was due to the older version of ActivePerl, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres, and restarted yet again to still get the error. I did a search and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plperl-td2264970.html I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller, so I'm not sure what else needs to be done for PostgreSQL to find the module. Since I see the module dll in the lib folder, I assume a separate package is not needed like I am accustomed to under BSD, is that right and it should find without further config or installs? Thanks. -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plperl.dll on windows with postgresql 9.0.3
On 2/8/2011 11:58 AM, Robert Fitzpatrick wrote: could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 Sorry the correct error I am getting is... could not load library “C:/Program Files/PostgreSQL/9.0/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 The previous was copied from a post found on the net when searching for solutions. Thanks for any help! -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl.dll on windows with postgresql 9.0
I am upgrading a Windows install for a client of mine from 8.2.x to 9.0.3 and understand the pginstaller does not provide plperl for this version. ActivePerl 5.8 was already installed and after uninstalling 8.2 and installing 9.0.3, there is no plperl.dll in the lib folder. I thought this was due to the older version, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres to still not find the dll. How do I get the lib to install? I did a google and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plperl-td2264970.html Can someone help with how to get plperl module installed or confirm the version required? I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller. Is there a plperl package that I need to install similar to Unix? Thanks. -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl.dll on windows with postgresql 9.0
I am upgrading a Windows install for a client of mine from 8.2.x to 9.0.3 and understand the pginstaller does not provide plperl for this version. ActivePerl 5.8 was already installed and after uninstalling 8.2 and installing 9.0.3, there is no plperl.dll in the lib folder. I thought this was due to the older version, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres to still not find the dll. How do I get the lib to install? I did a google and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plperl-td2264970.html Can someone help with how to get plperl module installed or confirm the version required? I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller. Is there a plperl package that I need to install similar to Unix? Thanks. -- Robert rob...@webtent.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] key=value imports
I was wondering if someone could help with my new task of trying to import from an API into PostgreSQL 9.0. An example query would respond like this... transactid=164d8355b0f4fc2eb051344d3b6b0b5f status=SUCCESS domaincount=3 domain_0_name=example1.com domain_0_expiration=2011/06/16 domain_0_status=ok domain_0_registrarlock=enabled domain_0_transferauthinfo=stye-4284 domain_1_name=example2.com domain_1_expiration=2011/02/12 domain_1_status=ok domain_1_registrarlock=enabled domain_1_transferauthinfo=plap-4093 domain_2_name=example3.com domain_2_expiration=2011/02/13 domain_2_status=ok domain_2_registrarlock=enabled domain_2_transferauthinfo=etas-1176 Based on the domain name, I want to update the other fields in our table. I can do split routines in plperl, but after I build an hash, I'm finding it hard to figure out how to feed that back to my plpgsql function for updates? Or is there a better way or some contrib that would help better? Thanks, Robert -- Robert rob...@webtent.org This e-mail message was delivered to you by a WebTent ESMTP mail gateway after it has been filtered for spam and viruses, see the headers of this message for details. http://www.webtent.com/email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Invalid byte sequence
I am getting the following error when trying to migrate a db from 8.4.0/FreeBSD 7.1 to 9.0.1/FreeBSD 8.1, both are VMware virtual machines with FreeBSD amd64... data1# psql -U pgsql template1 /data/maia.sql snip template1 /data/incoming.sql ERROR: invalid byte sequence for encoding UTF8: 0xe93532 CONTEXT: COPY awl, line 7884 I checked the build options for both pgsql port installations and found only difference being WITH_INTDATE=true in the destination server. I checked the line in the source file created by pg_restore and see no date involved in the record... data1# head -7884 /data/incoming.sql | tail -1 vscan geva...@prolongedinspire.com216.188 2 25.8380001 pg_restore -l on the source file dump file... ; Archive created at Thu Dec 9 03:03:27 2010 ; dbname: maia ; TOC Entries: 161 ; Compression: -1 ; Dump Version: 1.11-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 8.4.0 ; Dumped by pg_dump version: 8.4.0 Is it the WITH_INTDATE option and I should rebuild or something else causing this error? --Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Setting NEW in AFTER trigger
Is it not possible to set the value of a field by reference of NEW.field in an AFTER trigger? I have a trigger where I set NEW.field := myfunction and NEW.field shows the resulting value in my RAISE NOTICE as shown here... CREATE OR REPLACE FUNCTION public.registrants_tr_test_func () RETURNS trigger AS $body$ BEGIN NEW.egw_id := egw_add_account(NEW.id); RAISE NOTICE 'TEST: %', NEW.egw_id; RETURN NEW; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; CREATE TRIGGER registrants_tr_test AFTER UPDATE ON public.registrants FOR EACH ROW EXECUTE PROCEDURE public.registrants_tr_test_func(); update registrants set registrant_email = 'li...@webtent.net' where id = 14135; NOTICE: Groupware user already exists: 3058 CONTEXT: PL/pgSQL function registrants_tr_test_func line 2 at assignment NOTICE: TEST: 3058 maxegen= SELECT public.registrants.egw_id FROM public.registrants WHERE public.registrants.id = 14135; egw_id (1 row) The Groupware NOTICE is output from the function if a lookup is found and returns the existing id in place of an INSERT with the new id returned if not found. I get no errors, but NEW.field remains NULL after updating the record as shown above. Hopefully I'm doing something wrong? -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgcrypto contrib
I am trying to develop a trigger that will post a new account into a table in another db sing dblink that is part of the egroupware web app that uses tripledes as the algorithm. I can't seem to find a combination for gen_salt that produces the correct crypt password, however, my knowledge in this area is limited. Does anyone know if pgcrypto is able to produce this type of algorithm and have suggestions how I might be able to get it done? -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Post to another db using pl/pgsql
I've worked with pgsql for a while, but never needed to post from a database trigger to another db. Is this possible? And if so, can someone offer a pointer to the docs on how to refer to other db's in my script, etc? -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Post to another db using pl/pgsql
On Wed, 2008-11-12 at 18:02 +0100, [EMAIL PROTECTED] wrote: I've worked with pgsql for a while, but never needed to post from a database trigger to another db. Is this possible? And if so, can someone offer a pointer to the docs on how to refer to other db's in my script, etc? What do you mean by 'other db'? Does that mean other PostgreSQL database, or a completely different db (say MySQL for example)? Sorry, yes, I'm wanting to post to another pgsql db on the same server. -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using dblink to connect as non-superuser
I try to use dblink_connect while logged into the database as the
superuser and it returns OK, but if I disconnect and use the same
command as follows, it give and error, password is required. Even if I
try to use the superuser login in the connect string below while
connected as a non-superuser, I get the error...
select dblink_connect('dbname=egroupware user=myuser password=mypass');
Does this mean I have to be connected to the db as the superuser? I
assume not since reading the docs and having the ability to use user and
password in my connection string. I can try the same command as above
with only the dbname while logged in as the superuser and works. All
works fine with superuser, but not any non-superuser. I am trying to
develop a trigger pl/pgsql on this 8.3 server, but the connections to
the db will be from a non-superuser from a web site connection. What am
I doing wrong?
My non-superuser owns both db's. The error hints that the target
server's authentication method must be changed. I have tried to setup
localhost to trust in pg_hba.conf, still no help.
--
Robert
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] worker took too long to start; cancelled
I have a FreeBSD server with Postfix that filters mail using amavisd-maia+SA+ClamAV. It crashed when we received an SMTP attack that traced back to a compromised user login and a flood a messages were sent to this smarthost. After getting it back up, I find this in the logs... Nov 4 08:09:50 esmtp postgres[769]: [6-1] WARNING: worker took too long to start; cancelled I have this every minute prior to the crash about 5 or 6 times. However, this server is not accessing a local database. All filtering is using a production db server over our private network. This server was just brought online with 6GB of memory using the PAE kernel option for FreeBSD. I copied over a working pgsql configuration on our existing production db server and some boot settings and wondering if memory or memory settings may be the issue? esmtp# cat /boot/loader.conf #console=comconsole kern.ipc.semmni=32 kern.ipc.semmns=512 esmtp# cat /etc/sysctl.conf # $FreeBSD: src/etc/sysctl.conf,v 1.8 2003/03/13 18:43:50 mux Exp $ # #security.bsd.see_other_uids=0 # tuning for PostgreSQL kern.ipc.shm_use_phys=1 kern.ipc.shmmax=1073741824 kern.ipc.shmall=262144 kern.ipc.semmsl=512 kern.ipc.semmap=256 Also, from postgresql.conf, here are my custom settings... max_connections = 250 shared_buffers = 512MB work_mem = 128MB maintenance_work_mem = 256MB max_fsm_pages = 179200 max_fsm_pages = 512000 wal_buffers = 256kB checkpoint_segments = 100 effective_cache_size = 1028MB Again, I'm wondering why the local postgresql was even involved since the mail system does not use the localhost pg server for filtering. Unless the error is related to the production db server it is hitting. I did not find any errors isolated to this time period, but I do see the following throughout those logs... ERROR: deadlock detected As I mentioned, this server and the production db server both filter using Maia Mailguard which is the only use of of pgsql on those servers. -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing debugger
I have built posgresql 8.3.1 on a FreeBSD 6.1 via the ports system WITH_DEBUG=true and also on a Windows machine with the debugger selected. I see the plugin_debugger.dll under the lib/plugins folder on the Windows machine as well. But when I look for the procs, they are not there template1=# select * from pg_proc where proname like 'pldbg%'; proname | pronamespace | proowner | prolang | procost | prorows | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | proallargtypes | proargmodes | proargnames | prosrc | probin | proconfig | proacl -+--+--+-+-+-+--+---+-+---+-+--++-++-+-+++---+ (0 rows) Can someone help or point me to some docs that can tell me what else I need to do to enable debugging plpgsql? -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing debugger
On Fri, 2008-05-16 at 14:27 +0100, Dave Page wrote: There is a README with the plugin which tells you how to modify postgresql.conf to preload the libraries at server start (which you must do). Once you've done that, just run the pldbgapi.sql script that's also included to load the api functions into the database you wish to debug. Thanks for the help, I guess this is my problem. I do not have anything in the Windows server plugins folder except plugin_debugger.dll and plugin_profiler.dll. On the FreeBSD box, cannot find plugin_debugger.so anywhere or the pldbgapi.sql script on either server. More suggestions? I did find this link, so I believe I know how to get it setup once I get the needed lib and script files... http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/edb-debugger/server/Attic/pldebugger.README?rev=1.1content-type=text/x-cvsweb-markup -- Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Test text value as interval
Been searching for a way to do this, but haven't found what I was hoping to find. Is there any way in pl/pgsql to test a text value to see if it would be a valid interval without having to try and store in a field? In a trigger, I'd like to test a NEW text type field. Right now, I have just the following to generate an error... test := NEW.textfield::interval; I'd like to test the field and RAISE EXCEPTION if not valid interval. -- Robert ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Test text value as interval
On Thu, 2008-02-07 at 16:58 -0800, Jeff Davis wrote: On Thu, 2008-02-07 at 19:37 -0500, Robert Fitzpatrick wrote: Been searching for a way to do this, but haven't found what I was hoping to find. Is there any way in pl/pgsql to test a text value to see if it would be a valid interval without having to try and store in a field? In a trigger, I'd like to test a NEW text type field. Right now, I have just the following to generate an error... test := NEW.textfield::interval; I'd like to test the field and RAISE EXCEPTION if not valid interval. Trap the error and do what you want with it: http://www.postgresql.org/docs/8.3/static/plpgsql-control- structures.html#PLPGSQL-ERROR-TRAPPING Yes, this looks like it might work, thanks! But not sure which condition to look for or if I'm doing this correctly. I tried syntax_error condition, but I'm still receiving the same cast error trying this in a trigger function... begin begin test := NEW.textfield::interval; EXCEPTION WHEN syntax_error THEN RAISE NOTICE 'Invalid Duration'; return null; end; snip other code return new; end; Although: why do you want to generate your own error? It seems like it would probably be about the same as the error produced by the casting failure. My application will display whatever I can return via raise exception, hence, that's why I'm trying this. Looking for a way to translate to the user... update events set event_duration = '3ho' where event_id = 2; ERROR: invalid input syntax for type interval: 3ho -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Getting all tables into memory
I have a couple of servers running Postfix with amavisd-maia+SA+clamav on FreeBSD 6.2 which use a central db server running PgSQL 8.2.4. My issue is the bayes database causing SA TIMED OUT in the logs and want to make sure I am getting everything into memory. The disk activity is high on the db server, this is the average systat status... /0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10 Load Average | /0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100 cpu user| nice| system| interrupt| idle| /0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100 aacd0 MB/s tps|X I know I'm running RAID-5 and that is set to change, right now I'm just focusing on getting my tables into memory. I have 4GB of memory in the db server now, but the server indicates about only 3GB in dmesg, not sure why this is, FreeBSD warns a small amount of over 4GB will not be used when booting... real memory = 3220635648 (3071 MB) avail memory = 3150565376 (3004 MB) Here is my conf... mx1# cat postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; max_connections = 250 shared_buffers = 500MB # min 128kB or max_connections*16kB work_mem = 64MB # min 64kB maintenance_work_mem = 256MB# min 1MB max_fsm_pages = 256000 # min max_fsm_relations*16, 6 bytes each checkpoint_segments = 100 # (value * 2 + 1) * 16MB effective_cache_size = 1000MB log_destination = 'syslog' silent_mode = on stats_start_collector = on # needed for block or row stats stats_row_level = on autovacuum = off# enable autovacuum subprocess? datestyle = 'iso, mdy' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C' # locale for time formatting I vacuum every night and expire my bayes db for SA. While I have plenty of memory installed, it just does not seem to be using it considering my disk status above? How can I tell if PgSQL is using memory or not and how much? Excuse my ignorance on the matter, just learning how to properly tune PostgreSQL. My top 20 tables sizes are as follows... maia= SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC limit 20; relname| reltuples | relpages ---+-+-- pg_toast_70736| 846647 | 257452 maia_mail | 375574 |63639 maia_sa_rules_triggered | 4.52118e+06 |38526 bayes_token | 447008 |20033 maia_sa_rules_triggered_pkey | 4.52118e+06 |17821 bayes_token_idx1 | 447008 |11437 maia_mail_recipients | 377340 | 9867 maia_sa_rules |1578 | 8501 token_idx | 377340 | 8053 envelope_to_received_date_idx | 375574 | 7202 pg_toast_70736_index | 846647 | 4719 maia_mail_idx_received_date | 375574 | 3703 maia_mail_recipients_pkey | 377340 | 3471 bayes_token_pkey | 447008 | 3200 awl_pkey | 189259 | 2965 maia_mail_recipients_idx_recipient_id | 377340 | 2696 awl | 189259 | 2599 maia_stats| 185 | 2545 bayes_seen_pkey | 174501 | 2433 bayes_seen| 174501 | 2238 (20 rows) -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Getting all tables into memory
On Thu, 2008-01-24 at 10:46 -0500, Bill Moran wrote: In response to Robert Fitzpatrick [EMAIL PROTECTED]: How can I tell if PgSQL is using memory or not and how much? Well, top is helpful. Also, consider installing the pg_buffercache addon so you can see how much of your shared_buffers is being used. Well, all of it I guess from looking below? Again, just learning here... maia=# select count(*) from pg_buffercache; count --- 64000 (1 row) maia=# select count(*) from pg_buffercache where relfilenode is null; count --- 0 (1 row) maia=# SELECT c.relname, count(*) AS buffers FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; relname | buffers -+- bayes_token | 16684 bayes_token_idx1| 10264 maia_sa_rules |8501 pg_toast_70736 |5898 maia_mail |4361 maia_sa_rules_triggered |3913 maia_mail_recipients|3603 bayes_token_pkey|3199 maia_stats |2545 token_idx |2442 (10 rows) Thanks again for any insight? -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Creating indexes
I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown to almost 10,000 loading 25 per page. There is a filter feature atop all seven columns in the table listing (all varchar except one date column). Also, sorting can be done by clicking any column header. Some complain of speed during filtering or clearing the filter. I want to create some indexes to see if this will help as I'm sure it will since there are none currently. Now my question, would it be better to create one index with all columns in the table -or- a separate index for each column field? I was assuming the latter, but would the index with all columns be beneficial as well? Thanks in advance! -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] delta copies of pg_dump files
Trying to format our pg_dump files so that we can take advantage of
rsync delta copies and the problem seems to be the sequences and maybe
very active tables not allowing much matched data in the transfer. I
have a 3GB dump file in plain text and broke off the head of two dump
files into their own and diff'd to see the following. I assume these
differences are what is causing me to get a very low percentage of
matched data in my transfer?
esmtp# ls -la data/maia.sql
-rw-r--r-- 1 root wheel 2906254629 Sep 23 22:46 data/maia.sql
esmtp# ls -la data.bak/maia.sql
-rw-r--r-- 1 root wheel 2935474571 Sep 23 19:01 data.bak/maia.sql
esmtp# head -500 data/maia.sql new.sql
esmtp# head -500 data.bak/maia.sql old.sql
esmtp# diff new.sql old.sql
38c38
SELECT pg_catalog.setval('maia_mail_id_seq', 567254, true);
---
SELECT pg_catalog.setval('maia_mail_id_seq', 565121, true);
52c52
SELECT pg_catalog.setval('maia_stats_history_id_seq', 77273, true);
---
SELECT pg_catalog.setval('maia_stats_history_id_seq', 76573, true);
80c80
SELECT pg_catalog.setval('maia_viruses_id_seq', 320, true);
---
SELECT pg_catalog.setval('maia_viruses_id_seq', 319, true);
167d166
vscan [EMAIL PROTECTED] 88.247 1 7.8096
256d254
vscan [EMAIL PROTECTED] 84.255 1 17.5770002
403d400
vscan [EMAIL PROTECTED] 103.188 1 20.0620001
467d463
vscan [EMAIL PROTECTED] 216.75 1 7.1713
500a497,500
vscan [EMAIL PROTECTED]209.44 2 51.0130005
vscan [EMAIL PROTECTED] 59.94 2 0.58196
vscan [EMAIL PROTECTED]66.248 2 14.827
vscan [EMAIL PROTECTED] 216.188 2 17.859
This is a very active database used as a mail cache for a couple of mail
gateways using Postfix+amavisd-maia+SA+clamAV on FreeBSD. Can I move
sequences to the end and will this help my cause? Seems the records are
changing as well, I'm not sure why there is so much changing in the
front of these dumps. Does pg_dump sort by OID? Looking in the dump
file, I see these records are coming from the AWL table, could this be
changing drastically all the time, I guess a question for my amavisd or
maia lists?
What can be done to best prepare dumps for this type of data transfer,
the file sizes are not much different and we want to save that bandwidth
to our remote facility? Thanks for the help!
--
Robert
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] autovacuum
I have a large database used with our mail filter. The pg_dumpall results in about 3GB with this being the only database in the system besides templates and postgres. I do a vacuum every night after backup and it takes about an hour, is this normal for this size db? I also have autovacuum enabled and when it is running during the day, our mail queues will tend to fill up with slow response to the server. Should I have autovacuum on even if I am vacuuming the db every night? Let me know if you need more specifics. Just trying to get some feedback on if my vacuum is taking too long or if both are necessary...thanks for the help! -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] autovacuum
On Thu, 2007-09-20 at 16:38 -0400, Bill Moran wrote: In response to Robert Fitzpatrick [EMAIL PROTECTED]: Why does everyone leave of the IO subsystem? It's almost as if many people don't realize that disks exist ... With 2G of RAM, and a DB that's about 3G, then there's at least a G of database data _not_ in memory at any time. As a result, disk speed is important, and _could_ be part of your problem. You're not using RAID 5 are you? Yes, using RAID 5, not good? RAID 5 with hot fix total of 4 drives. All SATA 80GB drives giving me little under 300GB to work with. Also, my nightly backup does a pg_dump of the one database and vacuums only that database as there are no other except template#'s. Then it does a pg_dumpall. Now, I noticed that we have the -dD flags on pg_dumpall, not sure why, I took them off. But the strange thing I am finding is while my one database using a 'pg_dump -F c' only comes out at 930MB while the pg_dumpall results in 3GB, is that due to the use of INSERTS by using -dD? max_connections = 250 max_fsm_pages = 204800 shared_buffers = 128MB Unless this machine runs programs other than PostgreSQL, raise this to about 650MB. You might get better performance from even higher values. The rule of thumb is allocate 1/4 - 1/3 of the available RAM to shared_buffers ... subtract the RAM that other programs are using first. Yes, it runs a few other things like Postfix+amavisd-maia+SA+clamAV, but low priority MX so it gets little unless the primary is not responding. Other than that, I use it to run the web GUI (php) for this amavisd-maia mail server where users can view spam/ham caches. Can I determine the amount of memory everything else is running by stopping postgres and look in top to see what is being used? Thanks for the other pointers...! -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Getting result from EXECUTE
I have a trigger function that I want to apply to several tables, hence
my use of TG_RELNAME. I just want the record to get inserted if an
UPDATE comes from my view rule if the record for the client doesn't
already exist. This is what I have, but I'm finding the FOUND is not
returned for EXECUTE. How can I accomplish what I need?
CREATE OR REPLACE FUNCTION public.create_fldclientnumber_trigg_func ()
RETURNS trigger AS'
begin
EXECUTE ''SELECT * FROM '' || TG_RELNAME || '' WHERE fldclientnumber = '' ||
NEW.fldclientnumber;
IF NOT FOUND THEN
EXECUTE ''INSERT INTO '' || TG_RELNAME || '' (fldclientnumber) VALUES (''
|| NEW.fldclientnumber || '')'';
END IF;
RETURN NEW;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Thanks for the help.
--
Robert
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Left joining table to setof function
I have a function that returns a set of a custom type...
trax=# \d client_search
Composite type public.client_search
Column | Type
+
clientno | bigint
client | character varying(100)
contactno | bigint
city | character varying(50)
state | character varying(50)
contact_info | text
contact_title | character varying(100)
contact_phone | character varying(20)
contact_cell | character varying(20)
clientnamesort | text
lastnamesort | text
...and need to left join this with a table that can tell me if each
record exists in that table depending on certain values. When I get to
tens of thousands of records returned by the function, while my runtime
of the function itself is little changed, I am struggling to lower the
runtime as it gets very high when there are more and more matches in the
join. I have gone through to make sure all my comparisons in the join
are of same type, here is the table I am joining...
trax=# \d tblsearch_selections
Table public.tblsearch_selections
Column| Type |
Modifiers
-+---+
search_selection_id | bigint| not null default
nextval('tblsearch_selections_search_selection_id_seq'::regclass)
search_id | integer |
user_id | character varying(12) |
selection_value | text |
selected_value | bigint|
selection_type | character varying(10) | default 'client'::character
varying
Indexes:
tblsearch_selections_pkey PRIMARY KEY, btree (search_selection_id)
search_selection_unique_idx UNIQUE, btree (search_id, user_id,
selection_value, selection_type)
Foreign-key constraints:
tblsearch_selections_search_fk FOREIGN KEY (search_id) REFERENCES
tblclientsearch(search_id) ON UPDATE CASCADE ON DELETE CASCADE
tblsearch_selections_user_fk FOREIGN KEY (user_id) REFERENCES
tbluser(flduserid) ON UPDATE CASCADE ON DELETE CASCADE
Here is the analysis of the query with over 12000 join matches...
trax=# explain analyze select * from client_search_id_func(62) left join
tblsearch_selections ON search_id = 62 and user_id = 'RF' and ((selected_value
= clientno and selection_type = 'client') or (selected_value = contactno and
selection_type = 'contact'));
---
Nested Loop Left Join (cost=301.98..9434.48 rows=1000 width=426) (actual
time=288.130..234578.634 rows=18202 loops=1)
Join Filter: (((tblsearch_selections.selected_value =
client_search_id_func.clientno) AND
((tblsearch_selections.selection_type)::text = 'client'::text)) OR
((tblsearch_selections.selected_value = client_search_id_func.contactno) AND
((tblsearch_selections.selection_type)::text = 'contact'::text)))
- Function Scan on client_search_id_func (cost=0.00..12.50 rows=1000
width=382) (actual time=253.805..265.752 rows=18202 loops=1)
- Materialize (cost=301.98..305.02 rows=304 width=44) (actual
time=0.001..4.931 rows=12747 loops=18202)
- Seq Scan on tblsearch_selections (cost=0.00..301.68 rows=304
width=44) (actual time=0.040..12.749 rows=12747 loops=1)
Filter: ((search_id = 62) AND ((user_id)::text = 'RF'::text))
Total runtime: 234673.875 ms
(7 rows)
Perhaps I need to use subselects or some other approach?
trax=# explain analyze select *, (select search_selection_id from
tblsearch_selections where search_id = 62 and user_id = 'RF' and
((selected_value = clientno and selection_type = 'client') or (selected_value =
contactno and selection_type = 'contact'))) from client_search_id_func(62);
---
Function Scan on client_search_id_func (cost=0.00..315837.50 rows=1000
width=382) (actual time=259.520..148524.590 rows=18202 loops=1)
SubPlan
- Seq Scan on tblsearch_selections (cost=0.00..315.82 rows=1 width=8)
(actual time=3.555..8.131 rows=1 loops=18202)
Filter: ((search_id = 62) AND ((user_id)::text = 'RF'::text) AND
(((selected_value = $0) AND ((selection_type)::text = 'client'::text)) OR
((selected_value = $1) AND ((selection_type)::text = 'contact'::text
Total runtime: 148540.593 ms
And with no matches, the winner so far is the left
[GENERAL] Using COALESCE nside function
Is it possible to use COALESCE function inside a function as a cursor variable? test cursor (myvar varchar) for (coalesce(SELECT...snip,0)); I get a syntax error when trying this...ERROR: syntax error at or near COALESCE...is there a way to do this? -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Generate random password
Can anyone suggest how one might be able to do this? I want to be able to generate an 8 character random password for users in their password field. Perhaps through the default setting of the field or a trigger function. I found the following, but is there anything that can be used on both Windows and *nix or can this be used on Windows somehow? http://pgfoundry.org/forum/forum.php?forum_id=994 -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Restoring 8.2 to 8.0
I have a dump from 8.2 restored to file that was pg_dump'd with format c from a production server. I want to know if it is possible for me to restore this to a 8.0 development server where I am not able to upgrade at this time. Trying to do some testing, but this is the only other server that's available right now. I tried a normal restore and start getting errors relating to regclass: honeypot:~ # psql -U postgres trax pgtrax.sql snip ERROR: cannot cast type text to regclass ERROR: relation public.tblmenu does not exist ERROR: function nextval(regclass) does not exist Is there a way I can prepare my dump to work? There is a lot of data, so I need to maintain COPY methods of inserting. -- Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Restoring 8.2 to 8.0
On Fri, 2007-06-01 at 19:24 +0200, RW wrote: I've never tried this but maybe it works if you use pg_dump from 8.0 to do the dump. Greetings Robert Robert Fitzpatrick wrote: I have a dump from 8.2 restored to file that was pg_dump'd with format c from a production server. I want to know if it is possible for me to restore this to a 8.0 development server where I am not able to upgrade at this time. Trying to do some testing, but this is the only other server that's available right now. I tried a normal restore and start getting errors relating to regclass: honeypot:~ # psql -U postgres trax pgtrax.sql snip ERROR: cannot cast type text to regclass ERROR: relation public.tblmenu does not exist ERROR: function nextval(regclass) does not exist Is there a way I can prepare my dump to work? There is a lot of data, so I need to maintain COPY methods of inserting. Thanks for the idea, but it didn't work. It warned me when trying, I had to add the -i option to make it dump and I am getting the same ERROR's. -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Referencing any field in a trigger
How can I reference any NEW field in an insert/update trigger function? When someone inserts or updates any field with a single asterisk (*), I need it to become '%%%'. But if they use an asterisk in any combination with other fields, then I want to TRANSLATE those asterisks to a single '%'. I was hoping not to have to test every field in the table. -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Referencing any field in a trigger
On Fri, 2007-05-25 at 11:17 -0400, Robert Fitzpatrick wrote: But if they use an asterisk in any combination with other fields I meant to say 'But if they use an asterisk in any combination with other *values* in the field...'. For instance, if they enter '*test*', it will be TRANSLATE'd to '%test%'. -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Referencing any field in a trigger
On Fri, 2007-05-25 at 12:00 -0400, Alvaro Herrera wrote:
Robert Fitzpatrick wrote:
How can I reference any NEW field in an insert/update trigger function?
When someone inserts or updates any field with a single asterisk (*), I
need it to become '%%%'. But if they use an asterisk in any combination
with other fields, then I want to TRANSLATE those asterisks to a single
'%'.
You can't do that with PL/pgSQL. You can with other languages like
PL/Perl though (which has better tools for string treatment, so it is a
good idea anyway).
Great! I use Perl for a lot of string functions now, but I've never used
pl/perl for triggers. I did not know I could use pl/perl in
triggers...but do now :)
http://www.postgresql.org/docs/8.0/static/plperl-triggers.html
But still, how would I reference all fields using the pl/perl? Can I
specify column numbers versus names as in '$_TD-{new}{1}' for the first
column and loop or something? For instance, I would like to be able to
say if any NEW column has a single asterisk only, set it to '%%%'.
--
Robert
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Referencing any field in a trigger
On Fri, 2007-05-25 at 13:45 -0400, Alvaro Herrera wrote:
Robert Fitzpatrick wrote:
But still, how would I reference all fields using the pl/perl? Can I
specify column numbers versus names as in '$_TD-{new}{1}' for the first
column and loop or something? For instance, I would like to be able to
say if any NEW column has a single asterisk only, set it to '%%%'.
Well, do a foreach ($_TD-{new}) or foreach (keys $_TD-{new}) (not sure
of the exact syntax but if you're used to Perl you can figure it out).
You gotta love pgsql, took a bit, but done...
foreach $i (keys %{$_TD-{new}}) {
if (${$_TD-{new}}{$i} eq '*') {
${$_TD-{new}}{$i} = '%%%';
} else {
${$_TD-{new}}{$i} =~ s/\*/\%/g;
}
}
return MODIFY;
--
Robert
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Searching data across tables, some large
Thanks to Richard for the help earlier this week with performance questions, once I was able to get pgsql and mssql using the same resources and doing some tuning, I was able to get comparable results. The issue still though, I have this view that I designed with the thought in mind to provide all fields the user will want to search in the PHP web app. However, I have found when joining a couple of large tables, the view count reaches the 40+ million records, this does not seem to work in pgsql (or in mssql), especially if distinct is needed. Should I even be attempting this approach? I have never used TSearch and wonder if that is the solution to this type of search? From a quick read of some TSearch info I see indexes are setup on a column basis in a table? So, I could create an index column for say the first name and last name fields in a contact table, but cannot create a field with information from different tables? Also, not all fields are text that I need to search, some boolean for instance, can I search TSearch index fields and other fields at the same time. Not sure how TSearch works, just wanted to get an opinion that that may be what I need before delving into it too much. -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Searching data across tables, some large
On Wed, 2007-05-23 at 18:53 -0400, Ericson Smith wrote: Yeah, we've used Tsearch with joins and searches on other fields on the tsearch table no problem. Tsearches are just another part of a WHERE clause. And can there be Tsearch fields built based on fields in different tables? Where can I find the best docs for TSearch2? I'm looking for information pertaining to how TSearch can help in my situation, instead of building a view with a lot of joins causing absurd amount of rows returned. Or am I going to need to limit the tables a user can search at one time regardless? For instance, seems if I join my table of clients with contacts to return all the contacts with company info, no problem. I can also join my clients with activity *or* comments table. But if I try to left join all three of those tables in one view against clients, seems to return way too many rows. Of course, I will not want all those rows, but I can't get it to respond to queries. Meanwhile, I can create a view with just the comments table joined to clients, while the count is over 1 million records, select statements execute quickly. Is TSearch for me? Thanks for the advice! -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Searching data across tables, some large
On Wed, 2007-05-23 at 19:48 -0400, Robert Fitzpatrick wrote: On Wed, 2007-05-23 at 18:53 -0400, Ericson Smith wrote: Yeah, we've used Tsearch with joins and searches on other fields on the tsearch table no problem. Tsearches are just another part of a WHERE clause. And can there be Tsearch fields built based on fields in different tables? Where can I find the best docs for TSearch2? I'm looking for information pertaining to how TSearch can help in my situation, instead of building a view with a lot of joins causing absurd amount of rows returned. Of course, what was I thinking! After reading through the TSearch stuff I see the objective is to index multiple columns. There would be no way to reference a unique record from an index of columns over multiple tables. So, it is looking like a build of the query string is going to have to take place on whichever tables the fields are in while limiting what fields the user can search (depending whether fields are in different tables), that versus a view with everything is what I should be doing? -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Permance issues with migrated db
I posted an issue I was having with a migration from MS SQL server to pgsql over the weekend. Tom's suggestion for the query I posted was right on, I made the appropriate updates to column types by dumping, changing and restoring the database. I then analyze'd the db and my query performance was equal to that of MS SQL. This is my first migration and also my first time trying to spot performance issues, mainly because all the previous db's I've worked with were built from scratch, never an issue with performance, but never worked with so much data either (not sure if that has anything to do with my issues). I have developed a view in pgsql that takes over 160K ms to execute, but when copied into MS SQL against the old database (with syntax mods of course), runs in a few seconds. Seems the issues are with tblcontactinfo and tblclientactivitytag. Only if I remove all references to *both* tables do I get good performance from the query. Thanks for any help! SELECT tblclientmaster.fldclientnumber, tblclientmaster.fldclientname, tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus, tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice, tblclientproductpreference.fldlimitedservice, tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget, tblclientproductpreference.fldconference, tblclientproductpreference.fldresort, tblclientproductpreference.flddailyfee, tblclientproductpreference.fldsemiprivate, tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal, tblclientroomsize.fldsize149, tblclientroomsize.fldsize299, tblclientroomsize.fldsize449, tblclientroomsize.fldsize599, tblclientroomsize.fldsize600, tblgeopreference.fldsw, tblgeopreference.fldnw, tblgeopreference.fldmw, tblgeopreference.fldw, tblgeopreference.fldma, tblgeopreference.fldse, tblgeopreference.flds, tblgeopreference.fldne, tblproductmaster.fldproductname, tblproductmaster.fldproductcode, tblcontactinfo.fldcontactnumber, tblcontactinfo.fldcontactfirstname, tblcontactinfo.fldcontactlastname, (tblcontactinfo.fldcontactaddress1::text || ' '::text) || tblcontactinfo.fldcontactaddress2::text AS fldcontactaddress, tblcontactinfo.fldcontactcity, tblcontactinfo.fldcontactstate, tblcontactinfo.fldcontactzipcode, tblcontactinfo.fldcontacttitle, tblcontactinfo.fldcontactphone2_type, tblcontactinfo.fldcontactphone2_num, tblcontactinfo.fldcontactphone3_num, tblcontactinfo.fldcontactphone4_num, tblcontactinfo.fldcontactphone5_num, tblcontactinfo.fldcontactemail, tblcontactinfo.fldenable, tblcontactinfo.fldcontactphone1_num, tblcontactinfo.fldperscomments, tblclientactivitytag.fldcontactactivitytag FROM tblclientmaster LEFT JOIN tblclientproductpreference ON tblclientmaster.fldclientnumber = tblclientproductpreference.fldclientnumber LEFT JOIN tblclientroomsize ON tblclientmaster.fldclientnumber = tblclientroomsize.fldclientnumber LEFT JOIN tblgeopreference ON tblclientmaster.fldclientnumber = tblgeopreference.fldclientnumber LEFT JOIN tblclientproductrelation ON tblclientmaster.fldclientnumber = tblclientproductrelation.fldclientnumber JOIN tblproductmaster ON tblclientproductrelation.fldproductnumber = tblproductmaster.fldproductnumber LEFT JOIN tblcontactinfo ON tblclientmaster.fldclientnumber = tblcontactinfo.fldclientnumber LEFT JOIN tblclientactivitytag ON tblclientmaster.fldclientnumber = tblclientactivitytag.fldclientnumber ORDER BY tblclientmaster.fldclientnumber, tblclientmaster.fldclientname, tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus, tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice, tblclientproductpreference.fldlimitedservice, tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget, tblclientproductpreference.fldconference, tblclientproductpreference.fldresort, tblclientproductpreference.flddailyfee, tblclientproductpreference.fldsemiprivate, tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal, tblclientroomsize.fldsize149, tblclientroomsize.fldsize299, tblclientroomsize.fldsize449, tblclientroomsize.fldsize599, tblclientroomsize.fldsize600, tblgeopreference.fldsw, tblgeopreference.fldnw, tblgeopreference.fldmw, tblgeopreference.fldw, tblgeopreference.fldma, tblgeopreference.fldse, tblgeopreference.flds, tblgeopreference.fldne, tblproductmaster.fldproductname, tblproductmaster.fldproductcode, tblcontactinfo.fldcontactnumber, tblcontactinfo.fldcontactfirstname, tblcontactinfo.fldcontactlastname, (tblcontactinfo.fldcontactaddress1::text || ' '::text) || tblcontactinfo.fldcontactaddress2::text, tblcontactinfo.fldcontactcity, tblcontactinfo.fldcontactstate, tblcontactinfo.fldcontactzipcode, tblcontactinfo.fldcontacttitle, tblcontactinfo.fldcontactphone2_type, tblcontactinfo.fldcontactphone2_num, tblcontactinfo.fldcontactphone3_num, tblcontactinfo.fldcontactphone4_num, tblcontactinfo.fldcontactphone5_num,
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote: Robert Fitzpatrick wrote: I have developed a view in pgsql that takes over 160K ms to execute, but when copied into MS SQL against the old database (with syntax mods of course), runs in a few seconds. Your query seems to produce 41.8 million rows. Are you sure MS-SQL is returning that many rows in a few seconds? I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100 PERCENT' after SELECT in the query. The Enterprise Manager does not indicate how many rows come back. I save it as a VIEW in MS SQL and do a 'select count(*)...' and, yes, it comes back 42164877 records. Just to be sure MS SQL hasn't done something to the structure (I noticed dbo prefixes, etc.), I pasted back into pgadmin, took off 'top 100 percent'. Then saved as a view and did a count(*) in pgsql, got 41866801. The difference in the number of records could vary due to the dataset age I'm using in pgsql for migration. The MS SQL db is still actively used. I hope I'm just missing something here...this are the queries for mssql and pgsql, respectively... SELECT TOP 100 PERCENT dbo.tblClientMaster.fldClientNumber, dbo.tblClientMaster.fldClientName, dbo.tblClientMaster.fldClientType, dbo.tblClientMaster.fldBuyingStatus, dbo.tblClientMaster.fldSellingStatus, dbo.tblClientProductPreference.fldFullService, dbo.tblClientProductPreference.fldLimitedService, dbo.tblClientProductPreference.fldAllSuite, dbo.tblClientProductPreference.fldBudget, dbo.tblClientProductPreference.fldConference, dbo.tblClientProductPreference.fldResort, dbo.tblClientProductPreference.fldDailyFee, dbo.tblClientProductPreference.fldSemiPrivate, dbo.tblClientProductPreference.fldPrivate, dbo.tblClientProductPreference.fldMunicipal, dbo.tblClientRoomSize.fldSize149, dbo.tblClientRoomSize.fldSize299, dbo.tblClientRoomSize.fldSize449, dbo.tblClientRoomSize.fldSize599, dbo.tblClientRoomSize.fldSize600, dbo.tblGeoPreference.fldSW, dbo.tblGeoPreference.fldNW, dbo.tblGeoPreference.fldMW, dbo.tblGeoPreference.fldW, dbo.tblGeoPreference.fldMA, dbo.tblGeoPreference.fldSE, dbo.tblGeoPreference.fldS, dbo.tblGeoPreference.fldNE, dbo.tblProductMaster.fldProductName, dbo.tblProductMaster.fldProductCode, dbo.tblContactInfo.fldContactNumber, dbo.tblContactInfo.fldContactFirstName, dbo.tblContactInfo.fldContactLastName, dbo.tblContactInfo.fldContactCity, dbo.tblContactInfo.fldContactState, dbo.tblContactInfo.fldContactZipCode, dbo.tblContactInfo.fldContactTitle, dbo.tblContactInfo.fldContactPhone2_Type, dbo.tblContactInfo.fldContactPhone2_Num, dbo.tblContactInfo.fldContactPhone3_Num, dbo.tblContactInfo.fldContactPhone4_Num, dbo.tblContactInfo.fldContactPhone5_Num, dbo.tblContactInfo.fldContactEMail, dbo.tblContactInfo.fldEnable, dbo.tblContactInfo.fldContactPhone1_Num, dbo.tblContactInfo.fldPersComments, dbo.tblClientActivityTag.fldContactActivityTag FROM dbo.tblClientMaster LEFT OUTER JOIN dbo.tblClientProductPreference ON dbo.tblClientMaster.fldClientNumber = dbo.tblClientProductPreference.fldClientNumber LEFT OUTER JOIN dbo.tblClientRoomSize ON dbo.tblClientMaster.fldClientNumber = dbo.tblClientRoomSize.fldClientNumber LEFT OUTER JOIN dbo.tblGeoPreference ON dbo.tblClientMaster.fldClientNumber = dbo.tblGeoPreference.fldClientNumber LEFT OUTER JOIN dbo.tblClientProductRelation ON dbo.tblClientMaster.fldClientNumber = dbo.tblClientProductRelation.fldClientNumber INNER JOIN dbo.tblProductMaster ON dbo.tblClientProductRelation.fldProductNumber = dbo.tblProductMaster.fldProductNumber LEFT OUTER JOIN dbo.tblContactInfo ON dbo.tblClientMaster.fldClientNumber = dbo.tblContactInfo.fldClientNumber LEFT OUTER JOIN dbo.tblClientActivityTag ON dbo.tblClientMaster.fldClientNumber = dbo.tblClientActivityTag.fldClientNumber SELECT tblclientmaster.fldclientnumber, tblclientmaster.fldclientname, tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus, tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice, tblclientproductpreference.fldlimitedservice, tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget, tblclientproductpreference.fldconference, tblclientproductpreference.fldresort, tblclientproductpreference.flddailyfee, tblclientproductpreference.fldsemiprivate, tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal, tblclientroomsize.fldsize149, tblclientroomsize.fldsize299, tblclientroomsize.fldsize449, tblclientroomsize.fldsize599
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 19:04 +0200, PFC wrote: I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100 PERCENT' after SELECT in the query. The Enterprise Manager does not indicate how many rows come back. I save it as a VIEW in MS SQL and do a 'select count(*)...' and, yes, it comes back 42164877 records. No, it comes back 1 record with the count in it, the ORDER BY is useless for a count(*), etc. What is it that you are trying to do exactly ? Yes, one record indicating over 42 million records available from the view, correct? I realized this after my first post, there is no 'ORDER BY' in my last post with the two query examples. I'm trying to my query in pgsql to return a result in the same amount of time (approx) than it does in the existing mssql db. The query comes back with results using MS SQL Enterprise Manager in seconds and the same query in pgadmin takes super long. I just tried running the query now and it is still going with over 200K ms clocked. If I stop the query, remove all references to tblcontactinfo and tblactivitytag, the query comes back in less than 6000 ms. -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 13:10 -0400, Tom Lane wrote: Robert Fitzpatrick [EMAIL PROTECTED] writes: On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote: Your query seems to produce 41.8 million rows. Are you sure MS-SQL is returning that many rows in a few seconds? I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100 PERCENT' after SELECT in the query. The Enterprise Manager does not indicate how many rows come back. I save it as a VIEW in MS SQL and do a 'select count(*)...' and, yes, it comes back 42164877 records. Just to be sure MS SQL hasn't done something to the structure (I noticed dbo prefixes, etc.), I pasted back into pgadmin, took off 'top 100 percent'. Then saved as a view and did a count(*) in pgsql, got 41866801. How much time do the two select count(*) operations take? That would be a reasonably fair comparison of the query engines, as opposed to whatever might be happening on the client side (in particular, I wonder whether the MS client is actually fetching all the rows or just the first few). Takes 25K ms in pgsql, don't see a timer in MS Ent Manager, but only 5 seconds clocked. Maybe I should put together a php script to operate on each to be using the exact same client. I am doing all this all on the same server with PostgreSQL 8.2 loaded in Windows Server 2003 also with MS SQL server 2000. -- Robert ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote: 4. We're still 5 x slower than MS-SQL (with the count). That might well be down to having to check visibility on each row with our MVCC rather than just going to the index. Tips? I'd love to know how to see inside MVCC. I really appreciate the help! Hmm... How much of your machine is PG getting to use vs. MS-SQL? What are your shared_buffers, work_mem, effective_cache_size (and how much RAM on this box)? 3.5GB of RAM on a Xeon 2.8GHz server. I have default shared_buffers 32MB, no defaults changed except listen_addresses. How can I check work_mem and effective_cache_size? -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 14:30 -0400, Robert Fitzpatrick wrote: On Tue, 2007-05-22 at 19:03 +0100, Richard Huxton wrote: Hmm... How much of your machine is PG getting to use vs. MS-SQL? What are your shared_buffers, work_mem, effective_cache_size (and how much RAM on this box)? 3.5GB of RAM on a Xeon 2.8GHz server. I have default shared_buffers 32MB, no defaults changed except listen_addresses. How can I check work_mem and effective_cache_size? I did some googling and came up with some ideas, I have it now with these settings and after restarting PG, no help. work_mem = 5MB shared_buffers = 128MB effective_cache_size = 800MB -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Permance issues with migrated db
On Tue, 2007-05-22 at 20:13 +0100, Richard Huxton wrote: Robert Fitzpatrick wrote: I did some googling and came up with some ideas, I have it now with these settings and after restarting PG, no help. work_mem = 5MB My last post missed yours - you're ahead of my previous reply :-) Try 32MB, then 64MB, then perhaps 128MB. If it looks like you're not using stupid amounts of RAM on this one sort go higher still. This really has me perplexed now :\ I closed pgadmin, set it to 128MB work_mem and restarted PG, went back into pgadmin and created script from my saved view (again, this view is identical/copied from the same mssql view with only syntax changes). I started the query and then I started looking over that doc you sent me (again, since I just read through it from a google search)...after over 30 ms, still going, not even finishing. I'm beginning to worry something is wrong with the dataset migrated from mssql? I can migrate again tonight, last done about a month ago. Since then it has been dumped from a linux server and loaded on this Windows server without issue. -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Performance tuning?
We have a MS SQL server db that we successfully migrated to pgsql 8.2 and I am now working on some views and notice select queries being very slow. I have vacuumed last night and running the exact same query (with minor changes to syntax of course), it runs in just a few seconds compared to several minutes in pgsql. Since I've never dealt with MS SQL, I wanted to ask here if this is expected performance for the type of query *or* do I need to just learn how to properly tune my performance on the pgsql server? Perhaps some commands or tests may help me determine where issues may lie? I am running the following query on a linux server with comparable processor and memory as the windows server. The query was just taken from the SQL server as is and adjusted teh syntax...the query only returns 3 records, but several tables have tens of thousands of records, the tblactivitytag table has over 100K... SELECT distinct A.fldClientNumber as cNumber, A.fldClientName as cName, B.fldContactNumber as contactNumber, B.fldContactCity as cCity, B.fldContactState as cState, B.fldContactFirstName as contactFName, B.fldContactLastName as contactLName, B.fldContactEmail as ContactEmail, B.fldContactTitle as cTitle, B.fldContactPhone1_Num as B1Phonenumber, B.fldContactPhone4_Type as Num4Type, B.fldContactPhone4_Num as CellNum FROM tblClientMaster A, tblContactInfo B,tblClientProductPreference C, tblClientRoomSize D,tblProductMaster F, tblClientProductRelation G, tblclientcomments H, tblgeopreference E ,tblClientActivityTag WHERE A.fldClientNumber = B.fldClientNumber AND A.fldClientNumber = C.fldClientNumber AND A.fldClientNumber = D.fldClientNumber AND A.fldClientName ilike '%ADVISOR%' AND B.fldContactFirstName ilike '%%%' AND A.fldClientNumber = G.fldClientNumber AND G.fldProductNumber = F.fldProductNumber AND F.fldProductName ilike '%%%' AND A.fldClientNumber = H.fldClientNumber AND H.fldenable = 't' AND H.fldcontactnumber = b.fldcontactnumber AND H.fldClientcomments ilike '%%%' AND (A.fldBuyingStatus = 'Now' ) AND (A.fldSellingStatus = 'Now' ) AND (C.fldFullService = 't' ) AND (D.fldSize149 = 't' ) AND (E.fldW = 't' ) AND A.fldClientNumber = E.fldClientNumber AND A.fldclientnumber = tblClientActivityTag.fldclientnumber AND tblClientActivityTag.fldcontactnumber = b.fldcontactnumber AND tblClientActivityTag.fldcontactactivitytag like 'A%' AND b.fldcontactnumber in (select fldcontactnumber from tblclientcomments where tblclientcomments$ A.fldEnable = 't' AND B.fldEnable = 't' ORDER BY A.fldClientName, B.fldContactLastName; -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Performance tuning?
On Sat, 2007-05-19 at 17:05 -0400, Tom Lane wrote: Robert Fitzpatrick [EMAIL PROTECTED] writes: I am running the following query on a linux server with comparable processor and memory as the windows server. Show us the table definitions and the EXPLAIN ANALYZE output, please. Thanks Tom... --- Unique (cost=2326081.07..2354383.40 rows=12445 width=998) (actual time=71931.967..71989.731 rows=3 loops=1) - Sort (cost=2326081.07..2328258.17 rows=870841 width=998) (actual time=71931.959..71943.845 rows=9110 loops=1) Sort Key: a.fldclientname, b.fldcontactlastname, a.fldclientnumber, b.fldcontactnumber, b.fldcontactcity, b.fldcontactstate, b.fldcontactfirstname, b.fldcontactemail, b.fldcontacttitle, b.fldcontactphone1_num, b.fldcontactphone4_type, b.fldcontactphone4_num - Merge Join (cost=55798.98..60543.68 rows=870841 width=998) (actual time=46902.686..70218.041 rows=9110 loops=1) Merge Cond: (outer.fldclientnumber = inner.fldclientnumber) - Merge Join (cost=679.89..4617.75 rows=224283 width=8) (actual time=17.104..74.653 rows=125 loops=1) Merge Cond: (outer.fldclientnumber = inner.fldclientnumber) - Index Scan using ix_tblgeopreference_fldclientnumber on tblgeopreference e (cost=0.00..556.87 rows=6699 width=4) (actual time=0.205..56.266 rows=143 loops=1) Filter: (fldw = true) - Sort (cost=679.89..696.63 rows=6696 width=4) (actual time=16.844..17.005 rows=247 loops=1) Sort Key: c.fldclientnumber - Seq Scan on tblclientproductpreference c (cost=0.00..254.39 rows=6696 width=4) (actual time=0.084..15.884 rows=663 loops=1) Filter: (fldfullservice = true) - Materialize (cost=55119.09..55127.13 rows=804 width=1014) (actual time=46827.886..70028.280 rows=9110 loops=1) - Merge Join (cost=53060.03..55118.29 rows=804 width=1014) (actual time=46827.877..69956.976 rows=9110 loops=1) Merge Cond: (outer.fldclientnumber = inner.fldclientnumber) - Nested Loop (cost=53060.03..54565.61 rows=24 width=1010) (actual time=37189.898..69232.176 rows=25048 loops=1) - Nested Loop (cost=53060.03..54420.94 rows=24 width=1014) (actual time=37148.445..67472.468 rows=25048 loops=1) - Nested Loop IN Join (cost=53060.03..53581.73 rows=1 width=1006) (actual time=37129.788..66642.591 rows=1017 loops=1) Join Filter: (inner.fldcontactnumber = outer.fldcontactnumber) - Nested Loop (cost=53060.03..53565.72 rows=1 width=1040) (actual time=36584.031..37402.166 rows=1017 loops=1) Join Filter: (outer.fldcontactnumber = (inner.fldcontactnumber)::numeric) - Merge Join (cost=53060.03..53087.19 rows=1 width=210) (actual time=36561.298..36603.979 rows=1873 loops=1) Merge Cond: ((outer.fldclientnumber = inner.fldclientnumber) AND (outer.fldcontactnumber = inner.fldcontactnumber)) - Sort (cost=50577.52..50585.04 rows=3008 width=189) (actual time=36156.473..36159.932 rows=6167 loops=1) Sort Key: a.fldclientnumber, h.fldcontactnumber - Nested Loop (cost=0.00..50403.74 rows=3008 width=189) (actual time=6.180..36110.024 rows=6167 loops=1) Join Filter: ((outer.fldclientnumber)::numeric = inner.fldclientnumber) - Seq Scan on tblclientmaster a (cost=0.00..728.70 rows=1 width=172) (actual time=0.680..197.224 rows=4 loops=1) Filter: (((fldclientname)::text ~~* '%ADVISOR%'::text) AND ((fldbuyingstatus)::text = 'Now'::text) AND ((fldsellingstatus)::text = 'Now'::text) AND (fldenable = true)) - Seq Scan on tblclientcomments h (cost=0.00..40651.36 rows=601579 width=34) (actual time=0.019..7026.388 rows=1202169 loops=4) Filter: ((fldenable = true) AND ((fldclientcomments
Re: [GENERAL] Performance tuning?
On Sat, 2007-05-19 at 19:19 -0400, Tom Lane wrote: You're comparing fields of distinct types, which not only incurs run-time type conversions but can interfere with the ability to use some plan types at all. Looking at the table definitions, you've got primary keys declared as SERIAL (ie, integer) and the referencing columns declared as NUMERIC(18,0). This is just horrid for performance :-( --- NUMERIC arithmetic is pretty slow, and it's really pointless when the referenced columns are only integers. I suspect you should have translated these column types as BIGINT (and BIGSERIAL). Thanks again, I'll be sure to get this straightened out and tested again tomorrow. I thought my nightly backup was analyze'ing the database afterward, I'll be sure to check that as well. I really appreciate your analysis! It is my first migration from another SQL database. -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Constructing a SELECT statement in pl/pgsql
I have a function that returns a set of records based on one of my views. The function takes two arguments of user_id and saved search name, looks up the search values from a table previously saved and performs a SELECT query on my view to return my set of records found. However, I don't want to SELECT all columns of the view in my query, only the fields that have values specified to search for. So, if a value in my search table for a clientname field is blank, omit this field from my query so I can use DISTINCT and pull only those records. I hope this is not confusing. I guess my question is how to replace the following... FOR searchresults IN SELECT * FROM my_view WHERE snip LOOP With something like this... FOR searchresults IN SELECT DISTINCT clientname FROM my_view WHERE snip LOOP But I don't know if the field I want to search is going to be clientname or clienttype or even other types of fields until I test them for values. Is there a way for me to construst a comma separated list of columns to search in my pl/pgsql script and then use it in my FOR LOOP? I know I can use Perl to help build my list of columns, if needed, but then how can I pass that off to my SELECT query? If I have a variable called 'myfields' and try to place that in my SELECT query, it thinks there is a field named myfields, of course. Maybe a way to evaluate the comman sepeated list? Thanks in advance for any help! Or suggestion for a better way to develop user saved searches. -- Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Building a record in a function
Haven't done a whole lot of plsql returning records, only those based on a query. I was wondering, can I build a record from the results of the query using other values for some fields in the record? I know 'return next' requires a record and want to build my own record to include some argument values in the record. I have my record declared and my query results in that record. So, how do you construct a record in a function? Below is my work... period := $1; rep := $2; FOR dateval IN SELECT (period::date+(s||'month')::interval)::date from generate_series(0,11)s loop SELECT INTO picks COUNT(public.view_pick_1_data.units)::numeric AS units FROM public.view_pick_1_data WHERE (public.view_pick_1_data.rep = rep) AND (public.view_pick_1_data.start_date = dateval.date) GROUP BY public.view_pick_1_data.rep, public.view_pick_1_data.start_date; return next picks; end loop; return; I need the period in the record. My record type has three fields of text, date and numeric where I need the word units in the first field, the period from the loop query in the second and count from the picks query in the last field. If you could return fields, which I see you cannot, it would look like this: return next 'units', dateval.date, picks.units The final objective is a crosstab based on the return of this function. -- Robert ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Querying all months even if don't exist
I have a query that pulls totals for the month and from there I am building a crosstab to show all months. My dilemma is that sometimes there is no data for a month and the crosstab becomes skewed. I made a table with all the 12 months in it and joined to the query in my view to get all the months for any year there was sales to show in the query results, surely there is a better way? But when spanning different years like in the query below, that does not work as I only get the 12 months of the years where sales occurred in my query leaving out 2005 since this user had no sales in 2005. primepay=# select * from view_pick1 WHERE rep = 'aespinal' and nmonth = '12/01/2005' and nmonth = '11/30/2006' ORDER BY 1; rep| nmonth | units | revenue --++---+- aespinal | 2006-01-01 | | aespinal | 2006-02-01 | | aespinal | 2006-03-01 | | aespinal | 2006-04-01 | | aespinal | 2006-05-01 | 4 | aespinal | 2006-06-01 | 3 | aespinal | 2006-07-01 | | aespinal | 2006-08-01 | | aespinal | 2006-09-01 | | aespinal | 2006-10-01 | | aespinal | 2006-11-01 | | (11 rows) I need to make sure there is always 12 rows with all months for each type. Is there any kind of query I could make to build a list of all months whether they had sales in that year or not? Right now, this query below is what I'm using to get all the months of any year there were sales. The view_pick1_data view is the query where the totals are built. The view_pick1 shown in the above query takes all the months in the result of the query below and joins the view_pick1_data. I know there must be a better way, I'm struggling to figure it out. SELECT DISTINCT view_pick1_data.rep, view_pick1_data.nyear, months.month FROM view_pick1_data, months ORDER BY view_pick1_data.rep, view_pick1_data.nyear, months.month; Thanks for any help in advance! -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Expanding the crosstab function to extra rows
Finally figured out what was wrong with my crosstab that I posted for
help yesterday. I was really close, just need to set the right types. I
have it working using the crosstab(text sql, int N) function. This
displays a crosstab from my view below for units sold by each sales rep
under each month...
SELECT view_pick1_months.rep, view_pick1_months.month, view_pick1_data.units,
view_pick1_data.revenue
FROM (view_pick1_months LEFT JOIN view_pick1_data ON
view_pick1_months.rep)::text = (view_pick1_data.rep)::text) AND
(view_pick1_months.month = view_pick1_data.nmonth
ORDER BY view_pick1_months.rep, view_pick1_months.month;
primepay=# select * from view_pick1 where rep ='aespinal';
rep| month | units | revenue
--+---+---+-
aespinal | 1 |10 | 500
aespinal | 2 | 9 | 100
aespinal | 3 | 8 | 250
aespinal | 4 | 7 | 1000
aespinal | 5 | 6 | 500
aespinal | 6 | 5 | 250
aespinal | 7 | 4 | 300
aespinal | 8 | 3 | 150
aespinal | 9 | 2 | 100
aespinal |10 | 1 | 250
aespinal |11 | 2 | 5000
aespinal |12 | 3 | 2500
In my crosstab, I only use units right now and it works fine...
primepay=# select * from crosstab('select rep, month, units from view_pick1
where rep =''aespinal'' order by 1,2;', 12) AS view_pick1(rep varchar, jan
bigint, feb bigint, mar bigint, apr bigint, may bigint, jun bigint, jul bigint,
aug bigint, sep bigint, oct bigint, nov bigint, dec bigint);
rep| jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov |
dec
--+-+-+-+-+-+-+-+-+-+-+-+-
aespinal | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 2 |
3
Now, what I'd like to do is use the synopsis crosstab(text source_sql,
text category_sql) and include revenue on another line with units and
revenue being the extra_col in the README example for that function. So,
according to the readme, I need to produce the following data, but I am
perplexed at how to do this, can anyone help me produce the following
data:
rep| month | extra | amount
--+---+-+-
aespinal | 1 | units | 10
aespinal | 1 | revenue | 500
aespinal | 2 | units | 9
aespinal | 2 | revenue | 100
aespinal | 3 | units | 8
aespinal | 3 | revenue | 250
aespinal | 4 | units | 7
aespinal | 4 | revenue | 1000
aespinal | 5 | units | 6
aespinal | 5 | revenue | 500
aespinal | 6 | units | 5
aespinal | 6 | revenue | 250
aespinal | 7 | units | 4
aespinal | 7 | revenue | 300
aespinal | 8 | units | 3
aespinal | 8 | revenue | 150
aespinal | 9 | units | 2
aespinal | 9 | revenue | 100
aespinal |10 | units | 1
aespinal |10 | revenue | 250
aespinal |11 | units | 2
aespinal |11 | revenue | 5000
aespinal |12 | units | 3
aespinal |12 | revenue | 2500
If I can accomplish the above, then I think my new crosstab would output
like this:
rep| extra | jan | feb | mar | apr | may | jun | jul | aug | sep |
oct | nov | dec
--+-+-+-+-+-+--+-+-+-+-+-+--+-
aespinal | units | 10 | 9 | 8 |7 | 6 | 5 | 4 | 3 | 2 |
1 |2 |3
aespinal | revenue | 500 | 100 | 250 | 1000 | 500 | 250 | 300 | 150 | 100 |
250 | 5000 | 2500
--
Robert
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Complex search advice?
I want to be able to build complex search and reporting capabilities in to our PHP5 application. We want to be able to save searches for later use. We also want to build queries from virtually any field in certain tables from our PHP app. I hope to do as much within postgresql as possible. Then I start looking for how to enumerate field names, etc. Before I spend countless hours on seeing if some of my ideas will work and coding them, I hoped to receive some guidance here as to where I should start and possibly what are the elements of something like this? Will anything in contrib help? -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Crosstab
I am trying to use the crosstab function of the contrib tablefunc.
Reading the README, I believe I am supposed to be using crosstab(sql, N)
for my situation and wondering if the SQL can be based on a view? I have
this view created that gives me each sales rep and their total number of
units sold and total revenue for each month:
CREATE VIEW public.view_pick1 (
rep,
month,
units,
revenue)
AS
SELECT users.user_login AS rep, date_part('month'::text,
current_clients.start_date) AS month, count(companies.company_id) AS
units, sum(companies.company_revenue) AS revenue
FROM ((companies JOIN current_clients ON ((companies.company_id =
current_clients.client_id))) JOIN users ON ((companies.company_sales_rep =
users.user_id)))
GROUP BY users.user_login, date_part('month'::text, current_clients.start_date)
ORDER BY users.user_login, date_part('month'::text, current_clients.start_date);
Trying to make a crosstab, let's say just for units, this is what I'm
attempting, which is wrong of course, can someone enlighten me as this
is my first crosstab.
select * from crosstab('select rep, month, units from view_pick1 order by
1,2;', 12) AS view_pick1(rep varchar, jan double precision, feb double
precision, mar double precision, apr double precision, may double precision,
jun double precision, jul double precision, aug double precision, sep double
precision, oct double precision, nov double precision, dec double precision);
Error is: ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601
Not sure what that means, I tried to match up the view field types with
the returned fields. My sql produces the following after which is what I
would like to get. Am I even going about this correctly?
rep | month | units
--+---+---
aespinal | 5 | 4
aespinal | 6 | 3
asmith | 1 | 1
athranow | 1 | 5
athranow | 2 | 1
athranow | 3 | 2
athranow | 4 | 1
repjan feb mar apr may jun etc...
-+-+-+-+-+-+-+-
aespinal4 3
asmith 1
athranow5 1 2 1
Thanks for the help!
--
Robert
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] MSSQL/ASP migration
I have a customer who is wants to migrate his MSSQL database to PostgreSQL and we'll replace his application ASP with PHP. The issues should be limited as there are no stored procedures or triggers in MSSQL, just structure and data should be all that is needed to migrate. I have never migrated from MSSQL or to PostgreSQL, but have handled database migration in the past for other DB's. I know there is mssql2pgsql script out there somewhere and I find lots of info on the subject. Of course, when we rebuild his application, some db structure will change, I was planning to do all the changes to structure after a successful migration of his current structure now w/o data. After the new application is done, then create a migration path for the data. Is that the best way to handle these types of migrations? The customer will want to continue working on the old system during the several months of development in the new. Any docs or other helpful info is welcome, just looking for some advise. Thanks in advance, -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] MSSQL/ASP migration
On Mon, 2007-01-22 at 16:32 +, Peter Rosenthal wrote: Wanting to do something similar I recently submitted a large patch to the mysql2pgsql project. It will now handle conversion of a mysqldump file complete with data for the quite large and diverse DB I was using it with. I'm sure there are still corner cases, but you should give it a try: http://gborg.postgresql.org/project/mysql2psql/projdisplay.php Thanks, but my project has to do with Microsoft SQL server. -- Robert ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] MSSQL/ASP migration
On Mon, 2007-01-22 at 12:13 -0500, Ted Byers wrote: Is the original application ASP or SP.NET? It makes a difference, particularly if it was developed to take advantage of ASP.NET 2. It might conceivably be ASP.NET 3, but since that is brand new I can't see anyone paying to replace an ASP.NET 3 application that was just created. If it is ASP.NET 2, and you can't find a PostgreSQL provider class, your simplest approach after migrating the data might be to write your own provider (check a recent, decent reference on ASP.NET 2 for details - there are several). OTOH, if it is ASP.NET 1.x or the earlier ASP, your planned conversion to PHP might be worth comparing to developing it de novo with ASP.NET 3. Thanks for the response, the existing app is completely in just ASP, done several years ago. The current app only handles one division of the company and is still small and simple enough to migrate inexpensively. There are several divisions now, the security needs to be re-written to allow for more diverse access levels, etc. They are interested in PostgreSQL/PHP first so they can run it on basically any platform with relative ease and second, because we are more experienced and already have lots of tools to support the rapid development under PHP, we're old Perl hackers. I am not an MS advocate, and I don't like tying myself to one vendor, but for obvious commercial reasons I have to be aware of the options including MS options. I recently, therefore, started studying all things .NET, and comparing to other application frameworks I've worked with, MS seems to have done a decent job with ASP.NET 2 and 3. Therefore, if I have a client running mostly MS software, They have the MS SQL server (SMB 2003) also running the IIS web application and a file sharing server (W2K), that's it. No current major commitment to MS. We have loaded Linux on a third server now being used for some mail duties where we can build the new application and decide on its production server specs later. and time is of the essence, I would probably make .NET, ASP.NET3 or a Windows .NET app, as the case may be, my first choice; that is unless I find a public domain framework in Perl or PHP that is competitive with .NET. That said, I've not had an opportunity to see how it performs in a production setting, so YMMV. Yeah, so far, time has not been the main concern, more of a concern that they have lots of options going forward and scalability and availability using any browser. -- Robert ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Process won't start in Windows
On Tue, 2007-01-16 at 20:00 -0600, Adam Rich wrote: Robert, Open Computer Management and find the postgres service. There should be an item saying Run As. You want that to be a non-privledged account. Typically, postgres asks you what account to use when you install it. Just make sure that account has not been granted administrative rights (you can check that under Computer Management as well) Thanks, yes, I verified the services has 'postgres' as the account being used under the Log On tab of the PostgreSQL service. But still, the service will not start with the administrative permissions error previously posted. Any other ideas? -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Process won't start in Windows
On Wed, 2007-01-17 at 14:13 +, Dave Page wrote: Raymond O'Donnell wrote: Robert Fitzpatrick wrote: Thanks, yes, I verified the services has 'postgres' as the account being used under the Log On tab of the PostgreSQL service. But still, the service will not start with the administrative permissions error previously posted. Any other ideas? Did you check that the postgres user is not part of the local Administrators group? Or any other group it might have inherited admin rights from. the check is recursive - if you have *any* admin right, no matter how many parent groups they were inheirted form, it'll find them and refuse to run. The local administrators group has the Administrator, Domain Admins for the domain, and one other user (not postgres). The postgres user is a Member Of only the Users group. -- Robert ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Process won't start in Windows
I am running PostgreSQL 8.1.5 under Windows Server 2003 Standard edition. All has been running for weeks now, don't know if it has been restarted since we installed. Today we installed a program, Paradox database, which required a restart, and now the PostgreSQL Server service won't start with the Event description that the service cannot start by a user with administrative permsissions: Execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. I tried checking permissions on the data folder for the postgres user, resetting the password, even re-created the user and reset perms on the data and program files. Still won't start with the event error above. What should I look for? I am used to running PostgreSQL on our BSD boxes and have this setup for a customer. -- Robert ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Error code 1063
We have PostgreSQL 8.1 running on Windows 2000 for a few weeks now, when we try to start the service, it could not start claiming no error returned. So, I go to the command prompt and run the following: C:\Program Files\PostgreSQL\8.1\bin\pg_ctl.exe runservice -N pgsql-8.1 -D C:\Program Files\PostgreSQL\8.1\data\ pg_ctl: could not start service psql-8.1: error code 1063 I tried googling that error code, but come up with nothing. Can someone tell us what this code means? -- Robert ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Error code 1063
We have PostgreSQL 8.1 running on Windows 2000 for a few weeks now. Don't know what happened, the users reported a connection issue to the database and I found the service will not start. When we try to start the service, it could not start claiming no error returned. So, I go to the command prompt and run the following: C:\Program Files\PostgreSQL\8.1\bin\pg_ctl.exe runservice -N pgsql-8.1 -D C:\Program Files\PostgreSQL\8.1\data\ pg_ctl: could not start service psql-8.1: error code 1063 I tried to Google that error code, but come up with nothing. Can someone tell us what this code means? Last few things in the logs is: 2006-06-12 08:50:18 LOG: autovacuum: processing database postgres 2006-06-12 08:51:18 LOG: autovacuum: processing database ohc 2006-06-12 08:51:18 LOG: received fast shutdown request 2006-06-12 08:51:18 LOG: shutting down 2006-06-12 08:51:19 LOG: database system is shut down 2006-06-12 08:51:19 LOG: logger shutting down I guess it has been down sine then, but the users have not used the database. -- Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Division
I am having a problem gettig a percent via division. Below is the first
part of my trigger function where pct returns 0.00, instead of the
expected 0.50. If I try without dimensions to the numeric variable, I
just get 0. What is the correct way to accomplish the percent?
CREATE OR REPLACE FUNCTION public.issue (varchar) RETURNS numeric
AS'
DECLARE
repar text[];
pct numeric(3,2);
noreps integer;
BEGIN
repar := string_to_array($1,''-'');
noreps := array_upper(repar,1);
pct := 1/noreps;
RETURN pct;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
bachman=# select issue('AA-BB');
issue
---
0.00
(1 row)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Looping through arrays
I have a field with 'AA-BB-CC-DD' and I want to pull those four values into an array and then loop through the array inserting records into a table for each element. Can you someone point me to an example of this in pl/pgsql? -- Robert ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Create a pg table from CSV with header rows
Anyone know a package that can do this? Perferrably a Unix/Linux package. -- Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Password authentication failed for user
I have a database that is used every day for the past year and all of a sudden, this morning, I get a report that a user cannot login. I have doubled checked the pg_hba.conf file, which has not been changed in several months. But only this one user even after resetting the password, any other user works fine and if I update the pg_hba.conf file to trust that user, it works. What can cause this? I don't know if I should post by pg_hba.conf file here, but like I said, nothing has changed in several months and this problem just appeared today. -- Robert ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Sorting by constant values
I have a column that I want to sort by certain values. The values are Unit, Exterior and Common. I want all the records with Unit first, Common second and Exterior last in the sort order. These are the only 3 possible values, is there a way to sort manually like that with the alphanumeric values? -- Robert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Dropping sequences
I have changed the default sequence on a primary key integer (created as SERIAL) field in a table, but it will not let me drop the old sequence and continues to tell me that the sequence is required by the table. Can someone tell me what is required to get this dropped? I am just paranoid that it could be using the old sequence still even though it seems all is coming from the new sequence. -- Robert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Posting COPY data with shared sequences
I share the same sequences across 3 tables and have a COPY that send data to one of those tables. That table has a before insert trigger function that looks for a condition and redirect records to the other two depending on that condition result. Sharing the sequence is needed to act as a primary key for the view that UNION ALL's the tables. Since I'm posting all the data from one COPY command, the sequence number is duplicated in the tables. Should I move the moving the trigger function to after, which would require me to delete entries that get moved -or- separate the different data into separate incoming COPY commands? Anyone have suggestions on how I would best achieve what I'm looking to do. Thanks, -- Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Rules WHERE condition
I have a view that used union all to merge three tables together. I was hoping to create a rule, one for each table using the WHERE condition of the rule to determine which table gets updated. Is this possible? This is what I have, assume the view here is a merge of three tables using union all: CREATE RULE update_xrf AS ON UPDATE TO public.viewdeterioratedlbp WHERE ((new.note)::text = 'Unit'::text) DO INSTEAD (UPDATE tblxrf SET deterioration = new.deterioration;); The note column contains a value that can trigger which table needs to be updated. I would like to make one of these rules for each table to update. But when I run the update, it says I have to have an unconditional rule, is that right? Any suggestions? ohc=# update viewdeterioratedlbp set note = 'Unit', deterioration = 'test' where xrf_id = 733; ERROR: cannot update a view HINT: You need an unconditional ON UPDATE DO INSTEAD rule. Thanks, -- Robert ---(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] Rules WHERE condition
On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote: On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote: I have a view that used union all to merge three tables together. I was hoping to create a rule, one for each table using the WHERE condition of the rule to determine which table gets updated. Is this possible? See the CREATE RULE documentation: http://www.postgresql.org/docs/7.4/static/sql-createrule.html Thanks, that explains a lot, but still not able to get my rule to work, this is what I have now: CREATE RULE update_unconditional AS ON UPDATE TO public.viewdeterioratedlbp DO INSTEAD NOTHING; CREATE RULE update_xrf AS ON UPDATE TO public.viewdeterioratedlbp WHERE ((new.note)::text = 'Unit'::text) DO (UPDATE tblxrf SET deterioration = new.deterioration WHERE (tblxrf.xrf_id = new.xrf_id);); ohc=# update viewdeterioratedlbp set deterioration = 'test' where xrf_id = 143; UPDATE 0 This is the first rule I have tried to setup, I read through the doc, but don't seem to be able to catch what I'm doing wrong. Do I have to update all fields for it to work? -- Robert ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Rules WHERE condition
On Wed, 2004-11-17 at 12:49, Robert Fitzpatrick wrote: On Wed, 2004-11-17 at 11:49, Michael Fuhr wrote: On Wed, Nov 17, 2004 at 11:20:41AM -0500, Robert Fitzpatrick wrote: I have a view that used union all to merge three tables together. I was hoping to create a rule, one for each table using the WHERE condition of the rule to determine which table gets updated. Is this possible? See the CREATE RULE documentation: http://www.postgresql.org/docs/7.4/static/sql-createrule.html Thanks, that explains a lot, but still not able to get my rule to work, this is what I have now: Forget that last post, it is working even though the UPDATE 0 is returned. The record did update :) Please someone let me know if you see any issues with that? Like I said, my first rule let alone with a WHERE condition. -- Robert ---(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] DROP DATABASE, but still there
What does it mean when you drop a database and then recreate with the same name and all the objects are still there. I want to wipe out the db and put back from pg_restore. After I re-create the db, all the old tables are back before I run pg_restore. -- Robert ---(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] DROP DATABASE, but still there
On Thu, 2004-11-11 at 14:03, Doug McNaught wrote: Robert Fitzpatrick [EMAIL PROTECTED] writes: What does it mean when you drop a database and then recreate with the same name and all the objects are still there. I want to wipe out the db and put back from pg_restore. After I re-create the db, all the old tables are back before I run pg_restore. Check the 'template1' database to see if the tables got put in there by mistake at some point. Yeah, I was just starting to realize that these objects that keep coming back are in the template1 as well.any reason why I shouldn't drop the template1 and re-create like any other? -- Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Converting number to words
I searched through the net quickly and the list archives, but could not find anything doing this. Is it possible? There is a Perl module for doing this, but I guess cannot use modules for security reasons. -- Robert ---(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] Copy command and import - MS SQL Server to Postgres
On Fri, 2004-11-05 at 16:48, Allen Landsidel wrote: On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri [EMAIL PROTECTED] wrote: Iam trying to import data from ms-sql server to postgres. I export the data which has datetime columns in sql server using BCP. I use the following to import back into postgres. copy tablename from 'c:\\bcpdata\\mcfa\\tablename.txt' with delimiter as '\t' I get the following error !! invalid input syntax for type timestamp: My input file has the timestamp value like 2004-09-30 11:31:00.000 What about the .000 on the end? I am not able to enter that format in a timestamp field in 7.4.5, it is invalid. -- Robert ---(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] Sorting street addresses
Thanks to some help here on the list, I've been able to get addresses sorting pretty well, but now I have a issue with same addresses on different streets not grouping the streets. This is what I'm using a substring search in the ORDER BY statement now like in this view: SELECT tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id, tblhudsimilargroups.similar_group_id, tblhudbuildings.address, tblhudbuildings.hud_building_id, is_bldg_lbp(tblhudbuildings.hud_building_id) AS is_lbp, is_bldg_lbp_hazard(tblhudbuildings.hud_building_id) AS is_lbp_hazard FROM (tblhudsimilargroups LEFT JOIN tblhudbuildings ON ((tblhudsimilargroups.similar_group_id = tblhudbuildings.similar_group_id))) ORDER BY tblhudsimilargroups.rems_id, tblhudsimilargroups.group_id, (substring((tblhudbuildings.address)::text, '[^0-9]+'::text))::character varying, (substring((tblhudbuildings.address)::text, '^[0-9]+'::text))::integer; And getting this result: ohc= SELECT public.viewbldginfo.group_id, public.viewbldginfo.address FROM public.viewbldginfo WHERE (public.viewbldginfo.rems_id ='84136'); group_id | address --+-- A| 3606 ROYALTY COURT A| 3601/3603 ROYALTY COURT A| 3602/3604 ROYALTY COURT A| 3605/3607 ROYALTY COURT A| 3701/3703 MCKINLEY COURT A| 3702/3704 MCKINLEY COURT A| 3705/3707 MCKINLEY COURT A| 3709/3711 MCKINLEY COURT A| 7801/7803 SOCIAL CIRCLE A| 7801/7803 ANDALUSIA A| 7801/7803 HAVERSHAM A| 7802/7804 ANDALUSIA A| 7802/7804 HAVERSHAM A| 7805/7807 SOCIAL CIRCLE A| 7806/7808 HAVERSHAM A| 7811/7813 SOCIAL CIRCLE A| 7815/7817 SOCIAL CIRCLE A| 7825/7827 SOCIAL CIRCLE A| 7833/7835 SOCIAL CIRCLE I would like all those on the same street grouped together. Is there any tricks to getting the street names sorted first, possibly where numbers and strings separate? -- Robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] COPY command with PHP
I have a PHP script that was having problems using the COPY command with files around 1500 lines in size. The script will build the copy data from incoming CSV file into a temp file, then start a COPY command and loop through the copy data using pg_put_line to insert and then pg_end_copy after posting the last line as '\.'. Worked well under that 1500 line area. What I found was if I issue a pg_connection_busy($dbh) before the pg_put_line(...) in the while statement processing the lines of the temp file handle, it works. Don't ask me why, that is what I'd like to know. If I report back if busy is true, I get nothing. Maybe it is just giving a millisecond to breathe or something while checking to see if the connection is busy? One thing though, don't know if it had anything to do with it, the script is on an old box with an AMD 300 processor with maybe 256MB memory while the database is on an up to date dual Pentium 4 processor with 1GB mem. Of course, if I took the copy data file it is trying to COPY into PostgreSQL and psql to bring it in on the server directly, no issues. Not that it doesn't work with the busy check, just curious as to what is causing the need for it since I spent many hours to finally find the answer. No one on the PHP list seemed to know. -- Robert ---(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] Outliers of data
I have a project where it is necessary to determine Outliers of lab results and looking for some pointers on the best way to handle this type of calculation with PostgreSQL. Possibly an operator? I have no experience with that. I found some info on the web for calculating Outliers, here is one of them... http://cc.uoregon.edu/cnews/spring2000/outliers.html -- Robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Returning a list of fields in a composite type
Is it possible to return the field names of a composite type using a function? Any examples or pointers? -- Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Returning recordsets with functions
Can someone point me to some more information or perhaps show an example of returning a recordset from a plpgsql function. I'd like to send an argument or arguments to the function, do some queries to return a set of records. I've done several functions that return one value of one type, but nothing that returns a set. -- Robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Returning recordsets with functions
On Thu, 2004-09-23 at 18:28, Tim Penhey wrote:
Robert Fitzpatrick wrote:
Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.
Ah ha. Someone else trying to find out what I had hunted for. I could
not find an example on the web, so I started writing one.
Not fully complete yet, but here is what is there and I hope it helps.
http://www.scorefirst.com/postgresql.html
Thanks to all, that is very helpful. But when I create your function or
a small test function, I get the following error. I am running
PostgreSQL 7.4.5, do you know what this means or how I can define the
list?
ERROR: a column definition list is required for functions returning
record
I have another question. I was getting a syntax error when trying to
create the function on the page linked above:
ERROR: syntax error at or near INTEGER at character 64
I made my own test function with trying to put my own variable names in
the arguments list and it created without the syntax error, but now back
to the first problem. Here is what I have so far:
CREATE OR REPLACE FUNCTION public.test (varchar) RETURNS SETOF
pg_catalog.record AS'
DECLARE
row_ RECORD;
cursor_ CURSOR FOR SELECT * FROM tblhudreports WHERE rems_id=$1;
BEGIN
OPEN cursor_;
LOOP
FETCH cursor_ INTO row_;
EXIT WHEN NOT FOUND;
RETURN NEXT row_;
END LOOP;
CLOSE cursor_;
RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
ohc=# select * from test('80061');
ERROR: a column definition list is required for functions returning
record
ohc=#
--
Robert
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Updating another table using a trigger
I am running PostgreSQL 7.4.5 and have a trigger on a table called tblriskassessors which inserts, updates or delete a corresponding record in tblinspectors by lookup of a contact id and license number match. The INSERT and DELETE work fine. The UPDATE works good unless I update the license number. The error, at the bottom of this message, suggests the primary key violation. But my UPDATE in no way alters the primary key, which is inspector_contact_id. A manual update on tblinspectors using the same values works fine. There is a foreign key on tblriskassessors assessor_contact_id field to the primary key above. The structures of the two tables can be found below as well. Can anyone see here what may be causing my problem? CREATE TABLE public.tblriskassessors ( assessor_contact_id INTEGER NOT NULL, assessor_certification_state CHAR(2) NOT NULL, assessor_license VARCHAR(50) NOT NULL, assessor_certificate TEXT, assessor_expiration_date DATE, CONSTRAINT tblriskassessors_assessor_license_key UNIQUE(assessor_license), CONSTRAINT tblriskassessors_pkey PRIMARY KEY(assessor_contact_id), CONSTRAINT tblinspectors_tblriskassessors_fk FOREIGN KEY (assessor_contact_id) REFERENCES public.tblinspectors(inspector_contact_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE, CONSTRAINT tblriskassessorstblstates_fk FOREIGN KEY (assessor_certification_state) REFERENCES public.tblstates(state_abbreviation) ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE ) WITH OIDS; CREATE TRIGGER tblriskassessors_set_inspecor_trigger BEFORE INSERT OR UPDATE OR DELETE ON public.tblriskassessors FOR EACH ROW EXECUTE PROCEDURE public.tblriskassessors_set_inspecor_trigger_func(); CREATE TABLE public.tblinspectors ( inspector_contact_id INTEGER NOT NULL, inspector_certification_state CHAR(2) NOT NULL, inspector_license VARCHAR(50) NOT NULL, inspector_certificate TEXT, inspector_expiration_date DATE, CONSTRAINT tblinsepectors_pkey PRIMARY KEY(inspector_contact_id), CONSTRAINT tblcontacts_tblinspectors_fk FOREIGN KEY (inspector_contact_id) REFERENCES public.tblcontacts(contact_id) ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE, CONSTRAINT tblinsepectorstblstates_fk FOREIGN KEY (inspector_certification_state) REFERENCES public.tblstates(state_abbreviation) ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE ) WITH OIDS; COMMENT ON TABLE public.tblinspectors IS 'Risk assessors details tied to contact entry.'; CREATE UNIQUE INDEX tblinspectors_activity_license_key ON public.tblinspectors USING btree (inspector_license); CREATE TRIGGER tblriskassessors_set_inspecor_trigger BEFORE INSERT OR UPDATE OR DELETE ON public.tblriskassessors FOR EACH ROW EXECUTE PROCEDURE public.tblriskassessors_set_inspecor_trigger_func(); CREATE OR REPLACE FUNCTION public.tblriskassessors_set_inspecor_trigger_func () RETURNS trigger AS' DECLARE checkit record; contactid integer; license varchar; BEGIN IF (TG_OP = ''DELETE'') THEN contactid := OLD.assessor_contact_id; license := OLD.assessor_license; ELSE contactid := NEW.assessor_contact_id; license := NEW.assessor_license; END IF; SELECT into checkit public.tblinspectors.inspector_contact_id, public.tblinspectors.inspector_certification_state, public.tblinspectors.inspector_license, public.tblinspectors.inspector_certificate, public.tblinspectors.inspector_expiration_date, public.tblcontacts.displayas FROM public.tblinspectors INNER JOIN public.tblcontacts ON (public.tblinspectors.inspector_contact_id = public.tblcontacts.contact_id) WHERE (public.tblinspectors.inspector_contact_id = contactid) AND (public.tblinspectors.inspector_license = license); IF NOT FOUND THEN -- insert inspector if id does not exist INSERT INTO tblinspectors VALUES (NEW.assessor_contact_id, NEW.assessor_certification_state, NEW.assessor_license, NULL, NEW.assessor_expiration_date); IF NOT FOUND THEN RAISE EXCEPTION ''Could not insert inspector''; END IF; ELSE -- update inspector if id does not exist IF (TG_OP = ''UPDATE'') THEN UPDATE tblinspectors set inspector_certification_state = NEW.assessor_certification_state, inspector_license = NEW.assessor_license, inspector_expiration_date = NEW.assessor_expiration_date WHERE inspector_contact_id = NEW.assessor_contact_id; IF NOT FOUND THEN RAISE EXCEPTION ''Could not update inspector''; END IF; END IF; IF (TG_OP = ''DELETE'') THEN DELETE FROM tblinspectors WHERE inspector_contact_id = OLD.assessor_contact_id; IF NOT FOUND THEN RAISE EXCEPTION ''Could not update inspector''; END IF; END IF; END IF; IF (TG_OP = ''DELETE'') THEN RETURN OLD; ELSE RETURN NEW; END IF; END; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; Transaction failed! Your SQL:
[GENERAL] Sorting varchar w/single digits
I have varchar column with both numbers and letters, like 1 thru 10 and 5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100, since it is a varchar field, it sorts like 1,10,11... instead of 1,2,3... Is there any way to handle this without having to make a sort order column? -- Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] postmaster does not shut down
I am getting 'postmaster does not shut down' when trying to stop the database with '.../pg_ctl -D /path/to/datadir stop -m fast. How should I proceed to get the database shut down and restarted? Are there other options besides immediate shutdown flag? I dont' want to lose everything I've worked on today :( -- Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] DELETE and UPDATE in same function with foriegn keys
I have tblhud74b that has a foreign key to tblhudunits with Restrict Updates. I have a function that DELETEs corresponding records before doing an UPDATE right after the DELETE statement and receive the following error: ERROR: update or delete on tblhudunits violates foreign key constraint tblhudunitstblhud74b_fk on tblhud74b DETAIL: Key (similar_group_id,sort_order)=(18,1) is still referenced from table tblhud74b. It seems the DELETE is not posted before the UPDATE happens, hence the error, right? I tried setting the Foreign Key to DEFERRABLE INITIALLY DEFERRED, but does not seem to work. Is there a way to do this without having to set my Foreign Key to Update instead of Restrict. That works if I do that. -- Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] tracking down foreign key constraint violation error
I have a function that does varous things, I can post it if necessary. Basically, it deletes any related similar_group_id in tblhud74b, sets sort_order field in tblhudunits to NULL and then proceeds to re-populate tblhud74b based on certain calculations. The function runs fine through all loops to the end. At the end, the following ERROR appears and the transactions in the function all fail. ERROR: update or delete on tblhudunits violates foreign key constraint tblhudunitstblhud74b_fk on tblhud74b DETAIL: Key (similar_group_id,sort_order)=(18,10) is still referenced from table tblhud74b. I even tried running the script with tblhud74b empty. I can get a RAISE NOTICE just before the END keyword in the function on the last line. No triggers on tblhud74b, only a insert/delete after trigger on tblhudunits, which is only updated with NULL sort_order's in the function, inserts or deletes are being done to tblhudunits in the function. All other edits in the function occur on tblhud74b. The only thing I've been able to find related is the record with the Key (similar_group_id,sort_order)=(18,10) is the first record in tblhudunits. Can anyone see something I am missing? -- Robert ---(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] SELECT based on function result
I have a function that tells me if a record is positive and negative based on several field values. I use it in select statements: ohc= SELECT sample_id, is_wipe_positive(tblleadwipe.sample_id) AS positive FROM tblleadwipe WHERE hud_building_id IS NOT NULL; sample_id | positive ---+-- 73 | f 81 | t (2 rows) I see that I cannot change my WHERE statement to WHERE positive = 't' because the column positive does not exist. Now I am looking for the best way to return all records that are found positive or negative using a query. Can anyone offer any guidance to how I can return all the positives (or negatvies)? Or do I need to write another function that does that? -- Robert ---(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] Trigger to update records out of memory
On 7.4.2 I have a trigger that I want to update any existing boolean values to false if a new one in that group is declare true by inserting a new record or updating an existing record: ohc=# CREATE OR REPLACE FUNCTION public.clear_common_groups () RETURNS trigger AS' ohc'# BEGIN ohc'# IF NEW.common_area = ''t'' THEN ohc'# UPDATE tblhudunits SET common_area = ''f'' WHERE hud_building_id = NEW.hud_building_id; ohc'# END IF; ohc'# IF NEW.exterior_area = ''t'' THEN ohc'# UPDATE tblhudunits SET exterior_area = ''f'' WHERE hud_building_id = NEW.hud_building_id; ohc'# END IF; ohc'# RETURN NULL; ohc'# END; ohc'# 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; CREATE FUNCTION ohc=# CREATE TRIGGER new_common_area BEFORE INSERT OR UPDATE ohc-# ON public.tblhudunits FOR EACH ROW ohc-# EXECUTE PROCEDURE public.clear_common_groups(); CREATE TRIGGER ohc=# update tblhudunits set common_area = 't' where sort_order = 2; ERROR: out of memory DETAIL: Failed on request of size 1048576. Can someone point out what I am obviously doing wrong? -- Robert ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trigger to update records out of memory
On Fri, 2004-06-18 at 13:17, Robert Fitzpatrick wrote: ohc=# CREATE OR REPLACE FUNCTION public.clear_common_groups () RETURNS trigger AS' ohc'# BEGIN ohc'# IF NEW.common_area = ''t'' THEN ohc'# UPDATE tblhudunits SET common_area = ''f'' WHERE hud_building_id = NEW.hud_building_id; ohc'# END IF; ohc'# IF NEW.exterior_area = ''t'' THEN ohc'# UPDATE tblhudunits SET exterior_area = ''f'' WHERE hud_building_id = NEW.hud_building_id; ohc'# END IF; ohc'# RETURN NULL; ohc'# END; ohc'# 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; CREATE FUNCTION ohc=# CREATE TRIGGER new_common_area BEFORE INSERT OR UPDATE ohc-# ON public.tblhudunits FOR EACH ROW ohc-# EXECUTE PROCEDURE public.clear_common_groups(); CREATE TRIGGER ohc=# update tblhudunits set common_area = 't' where sort_order = 2; ERROR: out of memory DETAIL: Failed on request of size 1048576. After getting doing some NOTICEs, I find it I'm looping my update funtion. But I change the TRIGGER to 'BEFORE INSERT' instead and now I have a peculiar problem. The first time I ran the UPDATE query, I receive an good response, ever since I receive 'INSERT 0 0'. But neither time did the record get inserted. ohc=# insert into tblhudunits (hud_building_id, sort_order, common_area) values (21, 10, 't'); NOTICE: 21 INSERT 1304826 1 ohc=# insert into tblhudunits (hud_building_id, sort_order, common_area) values (21, 10, 't'); NOTICE: 21 INSERT 0 0 Same 'INSERT 0 0' no matter what valid values I use. Can someone tell me what it means to receive 'INSERT 0 0'? -- Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Database accesss using plperl
On Wed, 2004-06-16 at 19:05, Paul Thomas wrote: On 16/06/2004 21:27 Robert Fitzpatrick wrote: I have plperl installed my PostgreSQL 7.4.2 server, but from what I understand in chapter 39.3 of the docs, you cannot access the databases without DBD::PgSPI. According to the readme for that module, it will only run on the untrusted plperlu. Is this the only way to run queries (SELECT, INSERT, UPDATE) using plperl? Are you talking about writing functions/stored procedures in PERL or accessing the database from a PERL program because what you have read is specific to functions/stored procedures. (sorry for the bad/absent punctuation but I've just come back from the pub after a generous quantity of Old Speckled Hen) I'm talking about writing PostgreSQL stored procedures using Perl that access the database via queries (like pl/pgsql, maybe using DBI:Pg) for SELECTs, INSERTs, UPDATEs, etc. -- Robert ---(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] Database accesss using plperl
I have plperl installed my PostgreSQL 7.4.2 server, but from what I understand in chapter 39.3 of the docs, you cannot access the databases without DBD::PgSPI. According to the readme for that module, it will only run on the untrusted plperlu. Is this the only way to run queries (SELECT, INSERT, UPDATE) using plperl? What are the consequences and things to look out for when running untrusted languages? -- Robert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Intalling PHP 4.3.7 with PGSQL support and Heimdal
Trying to portupgrade my PHP 4.3.6 packagewith PostgreSQL 7.4.2 support and getting this error below. Trying to setup postgresql with Heimdal Kerberos 5 support, believe I have, but not tried anything with it, yet. /usr/local/lib/libpq.so: undefined reference to `krb5_cc_get_principal'/usr/local/lib/libpq.so: undefined reference to `krb5_sname_to_principal'/usr/local/lib/libpq.so: undefined reference to `krb5_cc_default'/usr/local/lib/libpq.so: undefined reference to `krb5_cc_close'/usr/local/lib/libpq.so: undefined reference to `error_message'/usr/local/lib/libpq.so: undefined reference to `krb5_free_error'/usr/local/lib/libpq.so: undefined reference to `krb5_sendauth'/usr/local/lib/libpq.so: undefined reference to `krb5_free_principal'/usr/local/lib/libpq.so: undefined reference to `krb5_unparse_name'/usr/local/lib/libpq.so: undefined reference to `krb5_free_context'/usr/local/lib/libpq.so: undefined reference to `krb5_init_context'*** Error code 1 Stop in /usr/ports/lang/php4/work/php-4.3.7. I've posted this around a few weeks ago on some lists and still no success. I had a suggestion to add lines to the spec file, but not sure which file that is. Seems PHP is having a problem with the PGSQL support and its libraries for krb5. Nonetheless, from the looks of the Makefile for postgresql, everything should be there for it to build with krb5 support. I receive no errors when building or installing posgresql with heimdal support and have tried 'make deinstall' and 'make reinstall'. I have the WITH_HEIMDAL_KRB5, HEIMDAL_HOME is set to /usr/local where it resides with libs and includes. Someone also suggested adding a -lkrb5, but again, I have not been able to figure out where to place this setting in the file. Should I add it to the line that produces the error building PHP above (I think the libtool command) or in the postgresql Makefile. esmtp# ldd /usr/local/bin/psql/usr/local/bin/psql: libpq.so.3 = /usr/local/lib/libpq.so.3 (0x28096000) libkrb5.so.20 = /usr/local/lib/libkrb5.so.20 (0x280b2000) ... Anyone know where I can make these changes needed or what to try next? -- Robert
[GENERAL] Upgrade from 7.3.4 to 7.4.2 went wrong
I get this error after doing the upgrade on FreeBSD 5.2.1: The data directory was initialized by PostgreSQL version 7.3, which is not compatible with this version 7.4.2 Should I just re-initdb and the import my dump file? -- Robert ---(end of broadcast)--- TIP 8: explain analyze is your friend
