[GENERAL] C function on Windows 2003/XP
I am trying to follow the instructions on the 8.0 interactive comments (http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html) to build a c function using MSDEV and PostgreSQL 8.3 on Windows XP/2003 I am running into errors around. fatal error C1083: Cannot open include file: 'libintl.h': No such file or directory and am guessing I am missing a library and or something else? I did find this http://www.gnu.org/software/gettext/gettext.html ? Is there a newer example than the one in the 8.0 comments or a sample solution/project available? Thanks in advance, Oisin -- 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] Documenting a DB schema
Shahaf Abileah wrote: I’m looking for a systematic way to document the schema for the database behind our website (www.redfin.com http://www.redfin.com/), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect. Any recommendations? It would be great if the documentation could be kept as close to the code as possible – that way we stand a chance of keeping it up to date. So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability: create table table_with_comments(a int comment 'this is column a...'); (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html) However, Postgres doesn’t support the “comment” keyword. Is there an alternative? Thanks, --S *Shahaf Abileah *|* Lead Software Developer * [EMAIL PROTECTED] | tel: 206.859.2869 | fax: 877.733.3469 Redfin Corporation 710 2nd Ave Suite 600 Seattle, WA 98104 Its probably best to try one list and see if you get a response rather than sending the same message to 3 lists. Comments are supported CREATE TABLE follow_me_destination ( mailbox_number character varying(10), -- Follow me users mailbox number. destination_number character varying(32), -- Follow me phone number. dest_id serial NOT NULL ) WITHOUT OIDS; ALTER TABLE follow_me_destination OWNER TO postgres; COMMENT ON TABLE follow_me_destination IS 'Stores follow me numbers for system users.'; COMMENT ON COLUMN follow_me_destination.mailbox_number IS 'Follow me users mailbox number.'; COMMENT ON COLUMN follow_me_destination.destination_number IS 'Follow me phone number.'; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG windows service issues
Yves Moisan wrote: Hi All, I'm new to this list. I've set up postgreSQL on windows about 4 times since the first 8.x series came out. The first time it was 8.0 beta3 IIRC. I believe I've installed another in the 8.0 series, then a 8.1 then just a couple of weeks ago 8.2.4. Every time I run into the same problem. I get the thing to work, find out I have to hack a couple of conf files to allow the port to work and then when I close my machine, trying to restart the service fails. (I know a server isn't meant to be shutdown like a desktop machine, but I'm trying out PG on my desktop ...) The only way I found to get rid of that problem is to reinitialize the password of the account that runs the service *every time I reboot my machine*. Then I can start the service. This is not only cumbersome, but it is outright dangerous. I wonder why this happens. I've installed a lot of software from the linux world as services (e.g.Zope) and I never encountered such problems. How can I get rid of those issues ? Sorry for the slightly whining tone of this email (I do like postgreSQL!), but I figured those types of issues had some time to settle since the 8.x series came out and they didn't. TIA, from a user who wants to bring folks to postgreSQL on windows, Yves Moisan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq The only similar issues I have seen was where the user was created for the DB with the user must change password at next login option and this caused some issues? Also if I removed and reinstalled PostgreSQL in some of the older 8.0.X?? versions it complained as it tried to create the postgres user account when it already existed. I am installing PostgreSQL 8.0.x 8.1.x and 8.2.x on Windows 2000 Pro and Server, Windows XP and Windows 2003 Server without issue. Are you deinstalling and reinstalling on the same machine over and over? Does the postgres windows user still exist after the deinstall? Oisin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql] Simple backup utility like mysqldump?
Bjorn Boulder wrote: Hello People, I'm running PostgreSQL 8.1.1 on my freebsd box. I'm curious if PostgreSQL has a utility for backing up small databases like mysqldump or Oracle's export utility. -b ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Perhaps google Postgresql dump very first result is http://www.postgresql.org/docs/8.1/static/backup.html - Oisin Glynn smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PostGreSQL for a small Desktop Application
David Gardner wrote: I'm not much of a .Net guy, but the pgsql-ODBC driver works rather well for me. Take a look at: http://psqlodbc.projects.postgresql.org/howto-csharp.html As for Windows XP, isn't there some limit to the number of incoming network connections? --- David Gardner, IT The Yucaipa Companies (310) 228-2855 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gabriele Sent: Monday, June 11, 2007 10:45 AM To: pgsql-general@postgresql.org Subject: [GENERAL] PostGreSQL for a small Desktop Application I'm going to develop a medium sized business desktop client server application which will be deployed mostly on small sized networks and later eventually, hopefully, on medium sized networks. It will probably be developed using C#. I do need a solid DBMS wich can work with .Net framework. I do know PostGreSQL is a good DBMS in general (it sports most of the advanced DBMS features, transactions and stored procedure included) but i wonder if it is suited for my application. Knowledge base of my users is very low and servers will be standard class desktop computers most probably ran on Windows XP (and Vista later on, i suspect). The service should be enough lightweight to be ran on such server and I need silent installation and configuration because i can't expect my user to be able to configure a DBMS. Additionally i need a passable to good data provider to interface PostGreSQL with .Net which possibly provide better performance than ODBC (don't know if it exists and i hope it is free). Anyway performance shoudn't be a big issue, i expect low concurrency level (less than 10 users) and low to medium volume of rows and queries. If more users and more data are needed for especially big customer i can simply suggest bigger and dedicated server. (different problems will arise for the aggregated data which will feed the web application, but for these we will have a real server). Is PostGreSQL suited for such use? If not which alternatives are there to be used? When using PostGreSQL in such a way is there any suggestion to be followed? Links to sources which i may find interesting (how to make a silent install, basic hardware requirements, so on). Thank you! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq On the network connections there are limits but the only one that I have hit is open file shares or named pipe connections. I have been able to get the number of open sockets very high in testing (though I may be breaking the license agreement!) to the point where I get an odd buffer space error (I actually posted on one of the lists about it previously) but that was under extreem load to breakpoint conditions. FYI We are running MS IIS, PostgreSQL, Oracle XE, all of our code(7-8 C++ Services) and a VM image of FC6 on a good XP Pro machine with no complaints so far and the system is providing a real time service in which delays/interruptions would be noticed. For high end systems we use Windows 2003 Server Oisin Glynn smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Oracle to PSQL function
, v_SerNoEnd, v_LotStart, v_LotEnd
FROM adempiere.M_AttributeSetInstance asi
INNER JOIN adempiere.M_AttributeSet a ON
(asi.M_AttributeSet_ID=a.M_AttributeSet_ID)
WHERE asi.M_AttributeSetInstance_ID=p_M_AttributeSetInstance_ID;
--
IF (v_SerNo IS NOT NULL) THEN
v_NameAdd := v_NameAdd || v_SerNoStart || v_SerNo || v_SerNoEnd ||
' ';
END IF;
IF (v_Lot IS NOT NULL) THEN
v_NameAdd := v_NameAdd || v_LotStart || v_Lot || v_LotEnd || ' ';
END IF;
IF (v_GuaranteeDate IS NOT NULL) THEN
v_NameAdd := v_NameAdd || v_GuaranteeDate || ' ';
END IF;
--
FOR a IN CUR_Attributes LOOP
v_NameAdd := v_NameAdd || a.Name || ':' || a.Value || ' ';
END LOOP;
--
IF (LENGTH(v_NameAdd) 0) THEN
v_Name := v_Name || ' (' || TRIM(v_NameAdd) || ')';
END IF;
END IF;
RETURN v_Name;
END adempiere.productAttribute;
/
_
Discover the new Windows Vista
http://search.msn.com/results.aspx?q=windows+vistamkt=en-USform=QBRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
I think you are missing the declare piece this is from the 8.1 docs.
Unfortunately the user comments (often nice examples) do not transfer
from version to version in the docs. I tend to change the url from 8.2
to 8.1 to 8.0 etc etc to see if any better user notes are already there.
Oisin
CREATE OR REPLACE FUNCTION foo()
RETURNS void
AS $$
DECLARE
V_StartTime timestamp with time zone;
V_EndTime timestamp with time zone;
BEGIN
SELECT INTO V_StartTime, V_EndTime
P_StartTime, P_EndTime
FROM normalize_time_period_limit(NULL::timestamp with time zone,
NULL::timestamp with time zone);
END;
$$ LANGUAGE PLPGSQL;
--
Oisin Glynn My status skype:oisinglynn?call
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [GENERAL] Predicted lifespan of different PostgreSQL branches
Tom Lane wrote: Dave Page [EMAIL PROTECTED] writes: I've been considering only maintaining the current and previous 2 versions in pgInstaller (the Windows binary distro). But that's a *lot* harder to maintain than just PostgreSQL because of all the bundled stuff. In other words, when 8.3 is out, the 8.0 series gets dropped. What do people think about that? Does anyone think it would be an unreasonable policy? Actually, I was just wondering last night about whether we should bother maintaining the 8.0/8.1 Windows ports at all. Everybody knew going in (or should have known) that 8.0 on Windows would be a pretty raw port with a lot of issues, as indeed it was, and so long-term support for it seems a bit pointless. Perhaps the 8.1 port was up to the point where it would be sane to use for production, or maybe not. I haven't tracked Windows-specific issues particularly, but I think there were at least some important patches we didn't back-port because of complexity. Certainly 8.0 and 8.1 src/backend/port/win32/ files look quite a bit different. Does anyone recall any specifics about Windows patches that were back-ported or not? Anyway I think that a fair case could be made for dropping the 8.0 branch now, and maybe 8.1 too, as far as Windows support goes. What you want to do going forward is a different decision --- these are edge cases because of the newness of the port. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly My 8.2c, Having 8.1 end of life this soon after the release of 8.2 seems pretty harsh. I am a Windows user with systems in the field in production and it would probably be disconcerting to customers to see/read that the version they are using will not have the benefits of full community support going forward. It should also be considered that it is probably more difficult to alleviate the concerns of people about using even the *best* Open Source database on a Windows platform (please don't bash us for using windows for now it is a necessary evil) It seems reasonable to expect 8.1 to be alive for more than 2 months after its replacement is released. For most production environments just the QA process for a major version change could take longer than 2 months to schedule and implement, let alone roll out to the hopefully growing numbers of customers. Oisin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NOTIFY QUESTION
Oisin Glynn wrote: From the Docs see below it implies that the WHERE clause of the update is ignored.( http://www.postgresql.org/docs/8.1/static/sql-createrule.html ) If I create a Rule as CREATE RULE foo_update_false AS ON UPDATE TO foo WHERE NEW.some_flag = TRUE DO NOTIFY foo_update; Does the WHERE clause in the rule get ignored as well? If so Could I create a regular trigger and do the notification from within the trigger having put a check in the trigger? Thanks, Oisin ---(end of broadcast)--- TIP 6: explain analyze is your friend This had to be done with a trigger. Oisin ---(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] NOTIFY QUESTION
From the Docs see below it implies that the WHERE clause of the update is ignored.( http://www.postgresql.org/docs/8.1/static/sql-createrule.html ) If I create a Rule as CREATE RULE foo_update_false AS ON UPDATE TO foo WHERE NEW.some_flag = TRUE DO NOTIFY foo_update; Does the WHERE clause in the rule get ignored as well? If so Could I create a regular trigger and do the notification from within the trigger having put a check in the trigger? Thanks, Oisin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL) 8.1.4
I am running postgres on Windows XP and have been for quite a while as the database engine for our application. On an ODBC connection the following error has started happening. The gap in activity is normal for our application. but the connection failing is a first we have this application live in approx 5 locations some for as long as 12 months (though only 2 live are on XP) We did enable the system to check for Windows updates automatically recently not sure if this could be a factor. There is another one of our apps connecting through the C++ API which is not seeing any loss of service. I suspect some sort of tcp-ip issue as I cannot connect with PgAdminIII locally on the server once the condition occurs. I have had this 3 times now and it seems to be happening very 2-3hours of operation. I am not getting any eventvwr or other Windows errors telling me I am hitting a limit or such? And when I restarted the Postgresql service I got the following as part of my log(See attached logs) 2007-01-05 14:38:28 LOG: pgpipe failed to bind: 10055i 2007-01-05 14:38:28 FATAL: could not create pipe for statistics buffer: No error I am attaching my postgresql.conf (perhaps I can turn on some more logging to make things easier to spot) and the log files I have from the first occurrence. These show my attempts to restart the postgresql service and the errors I received. This is a snipit of my applications log file showing the error pop up at 14:11:07 01/05/07 13:58:57.453 2296 DEBUG - Call No: 10131 SELECT SQL = SELECT * FROM zfunc_access_mailbox_cleanup(10131) 01/05/07 13:58:57.468 2296 DEBUG - Call No: 10131 SELECT Complete Return Code = $VC1 01/05/07 13:58:57.468 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT Free Statement Handle 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131DISCONNECT OK Voicemail 01/05/07 13:58:57.484 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Allocate DBC Handle Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Connect Voicemail 01/05/07 14:11:07.750 2296 DEBUG - Call No: 10132CONNECT SQLConnect failed 08001 could not connect to server: No buffer space available (0x2747/10055) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Error Message = 165Voicemail If there is anything I can do to generate better logging I am all ears, Oisin # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 max_connections = 100 # note: increasing
Re: [GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL)
Shelby Cain wrote: I'm just throwing this out here... but the usual suspects for strange connection issues like these would be a software firewall or antivirus (especially AV software that has anti-worm features). Also, Windows XP Pro isn't really viable as a server OS as Microsoft has intentionally limited certain features of the TCP/IP stack (e.g.: low limits for the TCP connection backlog). In theory, if you had a flood of incoming connections only some of them would be serviced correctly before the backlog limit was reached. Regards, Shelby Cain - Original Message From: Oisin Glynn [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Monday, January 8, 2007 1:33:54 PM Subject: [GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL) 8.1.4 I am running postgres on Windows XP and have been for quite a while as the database engine for our application. On an ODBC connection the following error has started happening. The gap in activity is normal for our application. but the connection failing is a first we have this application live in approx 5 locations some for as long as 12 months (though only 2 live are on XP) We did enable the system to check for Windows updates automatically recently not sure if this could be a factor. There is another one of our apps connecting through the C++ API which is not seeing any loss of service. I suspect some sort of tcp-ip issue as I cannot connect with PgAdminIII locally on the server once the condition occurs. I have had this 3 times now and it seems to be happening very 2-3hours of operation. I am not getting any eventvwr or other Windows errors telling me I am hitting a limit or such? And when I restarted the Postgresql service I got the following as part of my log(See attached logs) 2007-01-05 14:38:28 LOG: pgpipe failed to bind: 10055i 2007-01-05 14:38:28 FATAL: could not create pipe for statistics buffer: No error I am attaching my postgresql.conf (perhaps I can turn on some more logging to make things easier to spot) and the log files I have from the first occurrence. These show my attempts to restart the postgresql service and the errors I received. This is a snipit of my applications log file showing the error pop up at 14:11:07 01/05/07 13:58:57.453 2296 DEBUG - Call No: 10131 SELECT SQL = SELECT * FROM zfunc_access_mailbox_cleanup(10131) 01/05/07 13:58:57.468 2296 DEBUG - Call No: 10131 SELECT Complete Return Code = $VC1 01/05/07 13:58:57.468 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT Free Statement Handle 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131DISCONNECT OK Voicemail 01/05/07 13:58:57.484 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Allocate DBC Handle Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Connect Voicemail 01/05/07 14:11:07.750 2296 DEBUG - Call No: 10132CONNECT SQLConnect failed 08001 could not connect to server: No buffer space available (0x2747/10055) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Error Message = 165Voicemail If there is anything I can do to generate better logging I am all ears, Oisin # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir'# use data in another directory
Re: [GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL)
Brandon Aiken wrote: If you are suffering from this particular error, you will see an entry in the event log. Look for an error from Tcpip with an ID of 4226. The message will say TCP/IP has reached the security limit imposed on the number of concurrent (incomplete) TCP connect attempts.. If you do not see this message, you are not hitting this limit. See: http://www.microsoft.com/products/ee/transform.aspx?ProdName=Windows%20O perating%20SystemProdVer=5.1.2600.2180EvtID=4226EvtSrc=TcpipFileVer= 5.1.2600.2180FileName=xpsp2res.dllEvtType=WarningLCID= The limit is 10 outbound half-open connections. Typically, you will only see this limit if you're running a server or using P2P apps. The other limit is a maximum of 10 connections to the Server component (which does file and print sharing; people were using Win2k Pro as a file and print server). The only way to modify the limit is to manually modify binary files. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shelby Cain Sent: Monday, January 08, 2007 3:12 PM To: Oisin Glynn; pgsql-general@postgresql.org Subject: Re: [GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL) 8.1.4 I'm just throwing this out here... but the usual suspects for strange connection issues like these would be a software firewall or antivirus (especially AV software that has anti-worm features). Also, Windows XP Pro isn't really viable as a server OS as Microsoft has intentionally limited certain features of the TCP/IP stack (e.g.: low limits for the TCP connection backlog). In theory, if you had a flood of incoming connections only some of them would be serviced correctly before the backlog limit was reached. Regards, Shelby Cain - Original Message From: Oisin Glynn [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Monday, January 8, 2007 1:33:54 PM Subject: [GENERAL] Database Failure on Windows XP Pro psql (PostgreSQL) 8.1.4 I am running postgres on Windows XP and have been for quite a while as the database engine for our application. On an ODBC connection the following error has started happening. The gap in activity is normal for our application. but the connection failing is a first we have this application live in approx 5 locations some for as long as 12 months (though only 2 live are on XP) We did enable the system to check for Windows updates automatically recently not sure if this could be a factor. There is another one of our apps connecting through the C++ API which is not seeing any loss of service. I suspect some sort of tcp-ip issue as I cannot connect with PgAdminIII locally on the server once the condition occurs. I have had this 3 times now and it seems to be happening very 2-3hours of operation. I am not getting any eventvwr or other Windows errors telling me I am hitting a limit or such? And when I restarted the Postgresql service I got the following as part of my log(See attached logs) 2007-01-05 14:38:28 LOG: pgpipe failed to bind: 10055i 2007-01-05 14:38:28 FATAL: could not create pipe for statistics buffer: No error I am attaching my postgresql.conf (perhaps I can turn on some more logging to make things easier to spot) and the log files I have from the first occurrence. These show my attempts to restart the postgresql service and the errors I received. This is a snipit of my applications log file showing the error pop up at 14:11:07 01/05/07 13:58:57.453 2296 DEBUG - Call No: 10131 SELECT SQL = SELECT * FROM zfunc_access_mailbox_cleanup(10131) 01/05/07 13:58:57.468 2296 DEBUG - Call No: 10131 SELECT Complete Return Code = $VC1 01/05/07 13:58:57.468 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131 DISCONNECT Free Statement Handle 01/05/07 13:58:57.484 2296 DEBUG - Call No: 10131DISCONNECT OK Voicemail 01/05/07 13:58:57.484 2296 DEBUG - BX_SVR_TRP009 Sent TRP_IVR_ODBCOK for call no= 10131 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Allocate DBC Handle Voicemail 01/05/07 14:11:07.734 2296 DEBUG - Call No: 10132 CONNECT Connect Voicemail 01/05/07 14:11:07.750 2296 DEBUG - Call No: 10132CONNECT SQLConnect failed 08001 could not connect to server: No buffer space available (0x2747/10055) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Error Message = 165Voicemail If there is anything I can do to generate better logging I am all ears, Oisin # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line
Re: [GENERAL] Male/female
Steve Crawford wrote: Richard Troy wrote: On Fri, 8 Dec 2006, Raymond O'Donnell wrote: Yes, the table is used only for humans; it's part of some administrative software I'm writing for an educational institution, and the primary purpose of the gender column is to help the users cope with a problem new to the west of Ireland - the large influx of immigrants from Africa, eastern Europe and elsewhere means that it's no longer possible to tell a student's gender just from their name! --Ray. Ray, darest I point out that that's never been possible in English anyway? There are dozens if not hundreds of androgenous names - Pat and Tracy come immediately to mind, and there are countless others! Or with Irish names: Sheridan, Tara, Shay, Shannon, Rory, Ronan, Riley, Renny, Regan, Quinn, Murphy, Keverne, Keeley, Kane, Erin, Darby, Dara, Cary, ... Yes, I was researching baby names not all that long ago... :) Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend Man this thing has strayed off topic and I am joining in! Most of these Irish Names are family names that have been assumed as first names (Murphy, Quinn, Riley, etc) that FYI you will almost never find in Ireland! Although never heard of Ronán being used for a girl but I there is a boy called Eve and a girl called Adam somewhere! Oisín (A *real* Irish Name) smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] using a sequence as the functional equivalent to Oracle
Wm.A.Stafford wrote:
I'm trying to use a temporary sequence to duplicate the functionality
of the Oracle rownum pseudo-column
as suggested by Scott Marlow in the archives:
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.
The Oracle based application I'm porting to PostgreSQL used rownum to
select the 'next' block of rows to
process by specifying a where clause with something like where
rownumx and rownumy
My basic PostgreSQL query is:
drop sequence rownum ;
create temp sequence rownum;
select B.rownum , B.id from
(select nextval('rownum') as rownum, A.* from
(select distinct id from ... where ... order by ... DESC
) as A
) as B
where id0
This basic query produces the following result set:
rownum id
+-
1 10038
2 10809
3 10810
4 22549
5 23023
However, if I add a where clause referencing rownum for example: where
id0 and rownum0
I get the following:
rownum id
---+-
1110038
1210809
1310810
1422549
1523023
It appears as if rownum has been incremented as a result of three
passes over the five row result set.
Can someone explain what is going on? And more to to point, if this
is expected behavior, is there a standard PostgreSQL way to select a
'block' of rows from a result set based on row number?
Thanks,
-=bill
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I have done this using limit and offset like the following
select * from foo order by bar limit 10 offset 50;--giving the 10 rows
from position 51 onwards (offset is zero based)
Oisin
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [GENERAL] Simple stored procedure examples?
novnov wrote: And here is what shows in the SQL window for that proceedure CREATE FUNCTION proc_UpdateItemName(IN strItemName varchar) RETURNS void AS $BODY$update item set itemname = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; The error is always ERROR relation item does not exist. Not using the param, but it doesn't help to remove it. novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? Shoaib Mir wrote: You can use the following: #1 updating ItemName for all rows to 'fox' update tablename set itemname = 'fox'; #2 updating ItemName for row where ItemID = 2 to 'fox' update tablename set itemname = 'fox' where itemid = 2; #3 updating ItemName for row where ItemID = 3 to a param value passed in \set var1 3; update tablename set itemname = 'testing' where itemid = :var1; Hope this helps... Thanks, --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 11/4/06, novnov [EMAIL PROTECTED] wrote: I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot of bare bones simple example stored procs that I can learn from. It would be very helpful if someone could show me some simple code. In the pgAdmin interface I've been picking SQL as the language, that's the default it offers. Say I have a table Item, and fields ItemID (int4) and ItemName (varchar). What would be the code for #1 updating ItemName for all rows to 'fox' #2 updating ItemName for row where ItemID = 2 to 'fox' #3 updating ItemName for row where ItemID = 3 to a param value passed in That would be so helpful...I've tried and get errors like ERROR relation item does not exist, and of course the table Item and the column ItemName do exist, haven't been able to figure out what the error means. -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Is the table item spelled with some capital letters? PostgreSQL treats If the table is called Item with a capital I then select * from item will give you this error same with capitalization of column names and everything else. Oisin. ---(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] pg_proc Question
I have named all my functions in a pretty unique way and would like to export just my functions. Is there any way to move just these functions from one db to another.has anyone got a clever piece of SQL that spits out ddl from a select on pg_proc? Or could I use the copy command on pg_proc to spit my functions to file and then copy them into the new db? My issue is that I have allot of dblink and other things I do not want or need in the db and do not want to propagate this any further. Any help would be appreciated, Oisin smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] pg_proc Question
Martijn van Oosterhout wrote: On Thu, Nov 02, 2006 at 09:57:28AM -0500, Oisin Glynn wrote: I have named all my functions in a pretty unique way and would like to export just my functions. Is there any way to move just these functions from one db to another.has anyone got a clever piece of SQL that spits out ddl from a select on pg_proc? Or could I use the copy command on pg_proc to spit my functions to file and then copy them into the new db? My issue is that I have allot of dblink and other things I do not want or need in the db and do not want to propagate this any further. I would say the easiest is to use pg_dump and then filter the output. It's simple enough for sed I think: pg_dump database | sed -ne '/^CREATE FUNCTION tag/,/^--/p' works well here... Replace tag with a pattern matching your functions. If you're using pl/pgsql you may need to do something more clever with the end marker... Have a nice day, Thanks for the help. After a quick intro to sed (I'm a Windows user) I have this working a treat for me. I used the following slight mod and it seems to handle both sql and pl/pgsql on 8.1.4 pg_dump database | sed -ne '/^CREATE FUNCTION tag/,/^-- TOC entry/p' The -- TOC entry seems to act as a perfect end tag. I actually dumped the db as plain from pgAdmin and used this file as input to sed rather than piping the pg_dump into sed. The only difference was that I needed double quotes not single quotes in my sed command so command ended up like sed -ne /^CREATE FUNCTION tag/,/^-- TOC entry/p mybackupfile.backup myfunctions.sql Thanks, Oisin smime.p7s Description: S/MIME Cryptographic Signature
Re: [professionel] Re: [GENERAL] division by zero error in a request
Bernard Grosperrin wrote: Oisin SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)0 Thanks for your answer. The real request would be something like this: SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)0 My problem is that in fact I want to SUM those amounts, with a GROUP BY per location. But by doing so, any location where one row has where = 0 is eliminated. So, is there a way to SUM inside a subset returned by SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)0 Should I select into a temporary table, then SUM that table? Thanks, Bernard Please always copy the list on your responses as others may be interested. Something like this should work. I am not sure how inefficient it is. select location_id, SUM((sold_parts_amount_dly + sold_labor_amount_dly) - cost_amount_dly) / SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s1 where (select SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s2 where s2.location_id = s1.location_id) 0 group by location_id; smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] division by zero error in a request
Bernard Grosperrin wrote: I wants to make a view giving me some statistics. I am not sure to understand why something like this SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales give me a division by zero error? If that is not the way to go, should I write a function that I would call instead? Thanks, Bernard ---(end of broadcast)--- TIP 6: explain analyze is your friend If (sold_parts_amount_dly + sold_labor_amount_dly) equals zero you will get an error as you cannot divide by zero. Also in every case where (sold_parts_amount_dly + sold_labor_amount_dly) is not zero wont the answer be 1 as (sold_parts_amount_dly + sold_labor_amount_dly)/(sold_parts_amount_dly + sold_labor_amount_dly) is always 1?? To try and find the zero you could do the following: select count(*) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)=0; and if that is not a staggering amount of rows select * from sales where (sold_parts_amount_dly + sold_labor_amount_dly)=0; and try and identify why this is happening if it is not expected? or if you want to ignore rows where (sold_parts_amount_dly + sold_labor_amount_dly)=0; then SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales where (sold_parts_amount_dly + sold_labor_amount_dly)0 Oisin smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Windows install problem
Merlin Moncure wrote: On 10/12/06, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, Oct 11, 2006 at 01:32:29PM +0530, Ravindran G - TLS, Chennai. wrote: When I start PostgreSQL service, the below error message is displayed and finally service didn't started. The PostgreSQL Database Server 8.0 service of a local computer cannot begin. Error 1069: Service was not able to begin because it had failed in logon. Any idea about this error ?. Hope when we start service it checks for Windows authentication rather than DB authentication. Not Sure!. Hrm... certainly looks like a windows authentication issue. Hopefully someone else here can help, 'cause I don't know much about that. You might try searching the source code too... is this a fresh installation? the service was unable to login to the computer box. one way this happens is password expiration on the account. there are other things tho. more information would be helpful. merlin ---(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 Or I have been sucker punched by the user must change password setting on the account created by the install. Oisin smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Performance and Generic Config after install
As an aside to the [GENERAL] Advantages of PostgreSQL thread going on today, I have wondered why the initial on install config of PostgreSQL is (according to most posts) very conservative. I can see how this would be a plus when people may be getting PostgreSQL as part of an OS in the Linux world who may never/rarely use it. I know that in reality tuning and sizing all of the parameters is a very database specific thing, but it would seem that if some default choices would be available it would give those testing/evaluation and trying to get started a shot at quicker progress. Obviously they would still need to tune to your own application. Some dreadfully named, possibly pointless options? Unoptimized / low performance - Low load on Server (The current out of the box) Production Non Dedicated - PostgreSQL is one of the apps sharing server but is important. Production Dedicated Server - The only purpose of this box is to run PostgreSQL Perhaps this has already been suggested and or shot down? Oisin smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] File System Access:
All, I have a need to create/copy/delete files on my server from a function in PostreSql. I am running 8.x PostreSql on Windows 200x and my functions etc are currently only in PL/pgSQL. Is there a way to create files/copy them and delete them from PL/pgSQL or could i call an external bat file job from PL/pgSQL. Or do I need to branch into doing something with a different language (perhaps PL/PerlU)? If anyone has an example or experience they could share I would appreciate it. I had thought about having a table with a list of operations and then scheduling a job in the OS to call and execute them but these operations need to happen in real time. Thanks in advance, Oisin smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] LISTEN considered dangerous
Tom Lane wrote: Flemming Frandsen [EMAIL PROTECTED] writes: The listen should simply listen for events issued at the start of the transaction it's executed in. BEGIN; SELECT sleep(10); LISTEN foo; No, I don't think so. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I was just about to say that if someone was to do the following they would get the previously commited state of foo after the sleep not how foo looked before the sleep otherwise every begin would need an entire DB snapshot to be taken? Seems like it should be no different. I have been following this thread as I use LISTEN but not in as intensive way as the op and for me its working fine... BEGIN select sleep(10); select * from foo; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] doesn't recognize !=- (not equal to a negative
Scott Marlowe wrote: On Wed, 2006-07-12 at 05:55, Clodoaldo Pinto wrote: 2006/7/11, Oisin Glynn [EMAIL PROTECTED]: Paul Tilles wrote: Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Seems that postgres has a problem parsing a not equal negative value. Anybody know if this is fixed in later versions? Paul Tilles ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Just tested on 8.1.1 I was getting the same error but if i put a space between the = and the - it works! I have already counted 6 very similar answers. Is there some problem with the list software that prevents people from noticing it has already been answered? I think it's just the vagaries of the internet. By the time any one of us saw the other's response, we'd already written it. Plus, it's an easy question to answer, so there was little delay in thinking up a response for most folks. It's better than some other mailing lists I've been on, where such questions receive thundering silence for days on end... :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings I was somewhat confused it took my response about 5 hours to appear. I looked at the headers when my post eventually appeared it was odd. Other times the posts seem very quick. I seem to think that almost all the responses took a long time to appear. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] doesn't recognize !=- (not equal to a negative value)
Paul Tilles wrote: Version postgres 7.4.7: Following sql UPDATE tablename SET value = 0.0 where value!=-9.4; results in the error message ERROR: operator does not exist: smallint !=- integer HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Seems that postgres has a problem parsing a not equal negative value. Anybody know if this is fixed in later versions? Paul Tilles ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Just tested on 8.1.1 I was getting the same error but if i put a space between the = and the - it works! Oisin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL Rule
Could you create the table without the C column then create a view test_view with select a,b,a+b as c,id from test; Oisin Bert wrote: Hi list I have a table construction like the one seen below, when i am updating or inserting i get a recurion, logical. But how to manage it that the rule is just doing it one time. Or is it possible to do the sum of a and b in an other way? CREATE TABLE test ( a int2, b int2, c int2, id int2 NOT NULL, CONSTRAINT id_test PRIMARY KEY (id) ) WITHOUT OIDS; ALTER TABLE test OWNER TO postgres; CREATE OR REPLACE RULE sum_op AS ON INSERT TO test DO UPDATE test SET c = new.a + new.b WHERE test.id = new.id; CREATE OR REPLACE RULE sum_op_up AS ON UPDATE TO test DO UPDATE test SET c = test.a + test.b WHERE test.id = new.id; ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] COPY command documentation
I have driven myself to distraction for the last 30 minutes trying to get COPY to work on Windows XP. The Unix style c:/afolder/afile instead of c:\afolder\afile was a desperation attempt. I had tried all sorts of double slashes \\ putting the whole path in quotes basically all sorts of foolishness. I would suggest the there should be a Windows example(s) in the documents as well as a *NIX style one(s) where necessary. Did I miss this somewhere or should I put a comment on the doc or what can I do to help the next Windows user. Oisin P.S. I just discovered that the comments from 8.0 had the answer I was looking for but these comments are not in the 8.1 docs. Should the comments be rolled forward as new versions are created? Or if valid comments added to the docs themselves? http://www.postgresql.org/docs/8.1/interactive/sql-copy.html http://www.postgresql.org/docs/8.0/interactive/sql-copy.html Now happily using COPY, Oisin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [DOCS] [GENERAL] COPY command documentation
Scott Marlowe wrote: On Thu, 2006-03-23 at 14:12, David Fetter wrote: On Thu, Mar 23, 2006 at 07:00:13PM +0100, Jim Nasby wrote: On Mar 23, 2006, at 4:08 PM, Oisin Glynn wrote: I just discovered that the comments from 8.0 had the answer I was looking for but these comments are not in the 8.1 docs. Should the comments be rolled forward as new versions are created? Or if valid comments added to the docs themselves? http://www.postgresql.org/docs/8.1/interactive/sql-copy.html http://www.postgresql.org/docs/8.0/interactive/sql-copy.html No, comments don't roll forward. ...and it's unlikely that they will, now or later, without somebody whose whole job is to monitor those comments and make patches. I'd like to make a Modest Proposalâ„¢: Let's take down the interactive documents and, in their place, put up a request that doc patches be sent to -docs. Heck, why not a form that does it for somebody, takes their email address, and possibly even enrolls them in the -docs newsgroup. It can't be that hard to code up. ---(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 I am a Windows only developer (for my sins) and to be honest we are using postgres allot and are impressed by it but the *NIX centric examples in the docs can sometimes be a challenge, if there is a clear difference between Windows/*NIX operation and it is not obvious what the Windows equivalent is then there should be a Windows example or a note within the example explaining the difference. It seems like the viewing docs with notes is far less useful if we loose all the wisdom every time there is a new release, and personally I have found the notes useful more than once. Maybe a did you find this note useful button and useful notes get priority for doc inclusion? What is the process for submitting a doc patch? I am sure I should be asking that on the doc list. Oisin ---(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] PostgreSQL 8.1 v. Oracle 10g xe
Chris Travers wrote: Recently I have been attempting to install Compiere. After spending a lot of time attempting to get it to work, I have given up installing it on PostgreSQL. I did however, get a little experience installing JDBC and PL/Java in the mean time. I discovered in the process of attempting to install Oracle 10g express edition that it is an extraordinary memory hog. One might have thought that one might have something that might have lower system requirements than the full version. However, this is not the case. The installation itself is about 2GB (meaning you need at least 2GB of free space and 352 MB RAM. The installation failed because it said I needed at least 700MB swap space to install on Linux based on these resources. Am I the only one who finds this disturbing? Why should an RDBMS require so much swap space? After this experience, I have a much more profound appreciation for the quality RDBMS that is PostgreSQL. PostgreSQL will install fine on any of my computers if I need it to. In short, thanks guys for such an awesome RDBMS. PostgreSQL will always remain my favorite. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I come from an Oracle 8.X/9.X on Windows background and to be honest we are moving towards Postgres for as many applications/projects as we can. We have to keep Oracle but now version Xe for the tougher to migrate parts and I have been shocked at how Oracle Xe slows down a medium spec pc/server. Oracle 8.x 9.x (Standard Edition) sure was slimmer!! And both were a factor fatter than 7.X Seems like maybe all the bloat was included and the features were just disabled. Roll on the happy day we dispense with Oracle completely. Many times a paid up Oracle support contract has been less help than searching the Postgres mailing list archives. And the mailing lists are probably as responsive to inquiry but with a more competent person answering the Postgres question. Oisin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL on Windows not starting
Chris Travers wrote: I have a customer who is having issues starting PostgreSQL 8.1 on Windows. It worked for a while and now doesn't appear to be running. I thought it was probably a stale pidfile, but had him search and could not find it. Is the pid information still in a pidfile or is it in the registry somewhere on Windows? I am suggesting that he try to start the service manually and look for error messages. But in the mean time I thought I would ask. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Possile reason I hit a while ago was the account that was created for the PostgreSQL service was set to need a PWD change at login and so the service would not start, I was looking in all sorts of other areas before I spotted it, I think it was due to some domain policy as I had not seen it before or since. Or perhaps the account pwd is set to expire. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] [Fwd: Schema Question]
Hi,
I have some tables and functions in the public schema. This was
probably not the right thing to do.
I have a seperate schema with a function and table. The function does a
select on the table.
The table and function exist in public schema and betteridea schema. I
have a user betteridea who owns the betteridea schema.
The table in public schema has identical structure but different data to
the table in betteridea schema.
table employee_list
function zfunc_get_employee
If I connect as the betteridea user and do
select * from zfunc_get_employee('1234');
or
select * from betteridea.zfunc_get_employee('1234');
I get the correct result the result based on the records in the
betteridea.employee_list table in both cases.
If I connect as postgres user and do select * from
betteridea.zfunc_get_employee('1234');
or
select * from zfunc_get_employee('1234');
with no schema in front of it.
It is returning the data from the public.employee_list in both cases!
I would have expected that the function in the betteridea schema would
access the table in the betteridea schema without having to specifically
prefix the table name with the schema name.
My whole plan here is to have a schema per client and use the schemas to
segregate the data. I would like not to have to change the functions to
prefix table names with schema names.
The betteridea.zfunc_get_employee function below.
Any thoughts?
Oisin
CREATE OR REPLACE FUNCTION newschema.zfunc_get_employee( int4) RETURNS
varchar AS
$BODY$DECLARE
v_extn integer;
v_employee varchar;
BEGIN
v_extn:=$1;
select employee into v_employee from employee_list where extension =
v_extn;
return v_employee;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
---(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] Oracle purchases Sleepycat - is this the other shoe
Steve Manes wrote: Marc G. Fournier wrote: As of this moment, if Oracle buys Zend, they could effectively kill PHP ... the core engine that PHP is built around is a Zend engine, so if they were to revoke the license for that, PHP would be dead ... kinda like MySQL with InnoDB ... now, there was talk at one point time with replacying that engine with Parrot, so I'm not sure how hard/long it would take for them to do so if Zend got pulled out from under them ... FWIW, I know that Yahoo began quietly (though not quietly enough, obviously) organizing an Open PHP dev group a couple of years ago with the purpose of replacing the proprietary Zend engine. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster A secret Open group I like it! Sorry for spamming I couldn't resist, Oisin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] VACUUM Question
We have 2 tables we expect to grow byup to 50,000 rows per day eachdepending on the customer. In normal operation we will most likely never update or delete from these tables asthey arefor historical reporting. (Eventually we may but a limit on the amount of data and delete older than X months or such) We intend tocreate a number of indexes based upon the reporting search criteria. What would the best setup be for VACUUM, ANALYSE, REINDEX. Alot of the infor refers to data hanging around from deletes and updates which in normal course we will not do on these tables? Oisin
[GENERAL] DBLINK on WINDOWS?
Hi, I am trying to get some information on using DBLINK on windows? I am not having much luck searching the forums/google. Has anyone been using DBLINK on Windows. I amUsing 8.03 on Windows 2000. Ideally I would like to be able to send data from postgres to and from ODBC compliant databases from within the DB. Short of writing an app to connect to both DB'sand do this is this acheivable with DBlink or some other method? Oisin
Re: [GENERAL] How to pass the password for login in psql
BAT FILE SHOULD CONTAIN set PGPASSWORD=postgres psql.exe MYDATABASE -U postgres -f mysqlfile.sql Oisin - Original Message - From: Josef Springer [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Tuesday, July 19, 2005 1:26 PM Subject: [GENERAL] How to pass the password for login in psql Hi, i want to use psql using the --username option. But i do not find an option to pass the password. I want to use psql in a batch, so i want to automate it. Best Regards, Josef Springer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] System info in/through database on Windows 2000 Postres 8.x
Hi, I am wondering if there is a way to get simple (or more advanced ) system information from the database about the server. Free disk space, Memory usage, CPU usage. And any other health of system information in general. I have done some archive searching but not turned up very much. This is Postgres 8.x on Windows 2000 Thanks in advance, Oisin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Referential integrity using constant in foreign key
Is the goal to have code1 always equal 1 and code2 always to equal 2?
If this is your goal and you are trying to ensure no-one enters anything
other than a 1 in code1 or a 2 in code2 is a check constraint what you are
after?
I guess if the 2 columns code1 and code2 have fixed values 1 and 2 it seems
like they might not be even needed?
Not sure if that is what you are asking?
Oisin
- Original Message -
From: Thomas F.O'Connell [EMAIL PROTECTED]
To: Andrus Moor [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Monday, March 28, 2005 10:35
Subject: Re: [GENERAL] Referential integrity using constant in foreign key
Andrus, it's still not clear to me that you're understanding the role
of referential integrity in database design. It exists to guarantee
that the values in a column in a given table correspond exactly to the
values in a column in another table on a per-row basis. It does not
exist to guarantee that all values in a given column will have a
specific value.
Referential integrity never dictates the need for dummy columns. If
you have a column that you need to refer to a column in another table
so strongly that you want the values always to be in sync, you create a
foreign key, establishing referential integrity between a column (or
columns) in the table with the foreign key and a column in another
table (usually a primary key).
I don't understand what you're trying to accomplish well enough to be
able to make a specific recommendation based on your examples that
suits your needs.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source Open Your i
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 25, 2005, at 1:39 PM, Andrus Moor wrote:
Thomas,
thank you for reply. There was a typo in my code. Second table should
be
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
FOREIGN KEY ('1', code1) REFERENCES classifier,
FOREIGN KEY ('2', code2) REFERENCES classifier
);
I try to explain my problem more precicely.
I can implement the referential integrity which I need in the
following way:
CREATE TABLE classifier (
category CHAR(1),
code CHAR(10),
PRIMARY KEY (category,code) );
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
constant1 CHAR default '1',
constant2 CHAR default '2',
FOREIGN KEY (constant1, code1) REFERENCES classifier,
FOREIGN KEY (constant2, code2) REFERENCES classifier
);
This implementation requires 2 additional columns (constant1 and
constant2)
which have always same values, '1' and '2' respectively, in all info
table
rows.
I created those dummy columns since Postgres does not allow to write
REFERENCES clause like
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
FOREIGN KEY ('1', code1) REFERENCES classifier,
FOREIGN KEY ('2', code2) REFERENCES classifier
);
Is it possible to implement referential integrity without adding
additional
dummy columns to info table ?
It's somewhat unclear what you're attempting to do, here, but I'll
give a
shot at interpreting. Referential integrity lets you guarantee that
values
in a column or columns exist in a column or columns in another table.
With classifier as you've defined it, if you want referential
integrity in
the info table, you could do this:
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
FOREIGN KEY code1 REFERENCES classifier (category),
FOREIGN KEY code2 REFERENCES classifier (category)
);
But I'm not sure what you mean by references to category 1. There is
only a single category column in classifier, and referential
integrity is
not for ensuring that a column in one table contains only values of a
single row.
Regardless, your syntax doesn't seem to reflect reality. Read the
CREATE
TABLE reference thoroughly.
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source Open Your i
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 25, 2005, at 10:22 AM, Andrus Moor wrote:
I need to create referential integrity constraints:
CREATE TABLE classifier (
category CHAR(1),
code CHAR(10),
PRIMARY KEY (category,code) );
-- code1 references to category 1,
-- code2 references to category 2 from classifier table.
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
FOREIGN KEY ('1', category1) REFERENCES classifier,
FOREIGN KEY ('2', category2) REFERENCES classifier
);
Unfortunately, second CREATE TABLE causes error
ERROR: syntax error at or near '1' at character 171
Any idea how to implement referential integrity for info table ?
It seems that this is not possible in Postgres.
Andrus.
[GENERAL] Functions with more than 32 parameters
Hi, I am trying to keep database access encapsulated in functions from an insert and update point of view so as all of the applications writing to the database are using the same functions/statements. My problem is I was creating functions to do inserts/updates and now I have a table with more than 32 columns and the functions seem to only want to have 32 parameters? Is there any way around this? I have used stored procedures in this way with Oracle/SQL server before.Or is there another strategy I could use? I am using Windows 2000 Postgres 8.0.1 if it makes a difference Thanks in advance, Oisin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Functions with more than 32 parameters
Any advice for an opensource newbie running on Windows 2000? How hard is it to rebuild for windows? Any instructions? What compiler? Oisin - Original Message - From: Bradley D. Snobar [EMAIL PROTECTED] To: Oisin Glynn [EMAIL PROTECTED] Sent: Wednesday, February 09, 2005 10:40 PM Subject: RE: [GENERAL] Functions with more than 32 parameters I just had the same problem the other day. Change this file: /postgresql-8.0.0/src/include/pg_config_manual.h #define INDEX_MAX_KEYS 64 /*was 32 */ #define FUNC_MAX_ARGS INDEX_MAX_KEYS Recompile the server. make distclean ./configure make make install Oisin Glynn [EMAIL PROTECTED] wrote: Hi, I am trying to keep database access encapsulated in functions from an insert and update point of view so as all of the applications writing to the database are using the same functions/statements. My problem is I was creating functions to do inserts/updates and now I have a table with more than 32 columns and the functions seem to only want to have 32 parameters? Is there any way around this? I have used stored procedures in this way with Oracle/SQL server before.Or is there another strategy I could use? I am using Windows 2000 Postgres 8.0.1 if it makes a difference Thanks in advance, Oisin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.305 / Virus Database: 265.8.6 - Release Date: 2/7/2005 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Netstat - Lots of PG Connections
If this is a windows box I have noticed that the TIME_WAIT lasts far longer than on linux/unix. It is defined as a multiple of double of another TCPIP timer in the registry! Oisin - Original Message - From: Lonni J Friedman [EMAIL PROTECTED] To: CSN [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Tuesday, February 08, 2005 16:25 Subject: Re: [GENERAL] Netstat - Lots of PG Connections On Tue, 8 Feb 2005 13:17:38 -0800 (PST), CSN [EMAIL PROTECTED] wrote: Is this anything to worry about? Why are there so many Postgresql connections? 'ps axu' usually only shows about 5-10 postgres processes. # netstat tcp0 0 localhost.localdo:55547 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55780 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55777 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55778 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55779 localhost.loca:postgres ESTABLISHED tcp0 0 localhost.localdo:55764 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55760 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55761 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55772 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55773 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55774 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55768 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55769 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55770 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55748 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55749 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55750 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55751 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55744 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55746 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55747 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55756 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55758 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55752 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55729 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55731 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55741 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55742 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55736 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55737 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55716 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55718 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55719 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55712 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55714 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55715 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55724 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55725 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55727 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55720 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55721 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55722 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55700 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55701 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55703 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55696 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55697 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55698 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55699 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55708 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55709 localhost.loca:postgres TIME_WAIT tcp0 0 localhost.localdo:55710 localhost.loca:postgres TIME_WAIT tcp
Re: [GENERAL] Select from function for part of column results
I have something simlar working on windows in V 8.0 My select loks like: SELECT *,function_message_context(inbox.rowid) from inbox; Hope this helps. Oisin - Original Message - From: Rory Campbell-Lange [EMAIL PROTECTED] To: Postgresql General List pgsql-general@postgresql.org Sent: Thursday, February 03, 2005 15:04 Subject: [GENERAL] Select from function for part of column results I've written a function to provide information relating to each row in an inbox. I'd like to do a query returning some information from each row of the inbox + some columns from the function pertinent to each row returned. I've tried the following on 7.4.6: t4= select * from function_message_context(inbox.rowid), inbox; ERROR: function expression in FROM may not refer to other relations of same query level Is there a way I can get the function to provide some columns in the query? Thanks Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
