[GENERAL] Encountering NULLS in plpgsql
I wrote a simple pl to compute running sums, but when it encountered a null on a float4 value it caused pgadminIII to crash (exited abruptly). Is this intended behavior? -- % Randy Yates % I met someone who looks alot like you, %% Fuquay-Varina, NC% she does the things you do, %%% 919-577-9882% but she is an IBM. [EMAIL PROTECTED] %'Yours Truly, 2095', *Time*, ELO http://home.earthlink.net/~yatescr ---(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] Table Rows Not Properly Added
I have a no-OIDs database with a table that uses a serial primary key. I am using the View data option on the table within pgadminIII to enter new rows into the table. When I type enter into the last field of the last row, the * row counter increments to the next row. However, nothing is entered into the primary key field. I can place my cursor into the first (non-key) field and enter data, but then all subsequent fields lock me out so that I can't enter data. If I refresh the view data form, then a new primary key is applied and entered and I can edit the data. Why must I refresh the table before entering further data, and why isn't the primary key autoupdating immediately when I create a new row? -- % Randy Yates % ...the answer lies within your soul %% Fuquay-Varina, NC% 'cause no one knows which side %%% 919-577-9882% the coin will fall. [EMAIL PROTECTED] % 'Big Wheels', *Out of the Blue*, ELO http://home.earthlink.net/~yatescr ---(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] Table Rows Not Properly Added
PostgreSQL 8.0.3 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 4.0.0 20050505 (Red Hat 4.0.0-4) -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Encountering NULLS in plpgsql
dev@archonet.com (Richard Huxton) writes: Randy Yates wrote: I wrote a simple pl to compute running sums, but when it encountered a null on a float4 value it caused pgadminIII to crash (exited abruptly). Is this intended behavior? No, but we'll need more information to figure out what is going on. Sure - see below. What language did you use for the procedural code? Can you show us that code? CREATE TYPE vewCheckingRow AS ( fTransactionID integer, fType character varying(20), fDate timestamp with time zone, fDescription character varying(100), fAmount numeric(11,2), fClear boolean, fBookBalance numeric(11,2), fStatementBalance numeric(11,2) ); CREATE OR REPLACE FUNCTION fcnCheckingRow () RETURNS SETOF vewCheckingRow AS $$ DECLARE inprow RECORD; outrow vewCheckingRow; BEGIN outrow.fBookBalance := 0.0; outrow.fStatementBalance := 0.0; FOR inprow IN SELECT * FROM tblChecking ORDER BY fTransactionID LOOP outrow.fTransactionID := inprow.fTransactionID; outrow.fType := inprow.fType; outrow.fDate := inprow.fDate; outrow.fDescription := inprow.fDescription; outrow.fAmount := inprow.fAmount; outrow.fBookBalance := outrow.fBookBalance + inprow.fAmount; IF inprow.fClear '0' THEN outrow.fClear := TRUE; outrow.fStatementBalance := outrow.fStatementBalance + inprow.fAmount; ELSE outrow.fClear := FALSE; END IF; RETURN NEXT outrow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW vewChecking AS SELECT * FROM fcnCheckingRow(); What version of postgreSQL? PostgreSQL 8.0.3 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 4.0.0 20050505 (Red Hat 4.0.0-4) What version of pgAdmin III? 1.4.1 (under FC4/i386) Was there any error message? Not that I saw. Is there anything in the logs? Here's pgsql/data/pg_log/postgresql-Thu.log, when the error would've happened: ERROR: relation tblchecking does not exist ERROR: relation tblchecking does not exist ERROR: column ftransactionid does not exist ERROR: operator does not exist: double precision * character HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. ERROR: column float does not exist ERROR: column float8 does not exist ERROR: column float8::fClear does not exist ERROR: column float::fClear does not exist ERROR: column tblChecking.fTransactionID must appear in the GROUP BY clause or be used in an aggregate function ERROR: column tblChecking.fTransactionID must appear in the GROUP BY clause or be used in an aggregate function ERROR: column tblChecking.fTransactionID must appear in the GROUP BY clause or be used in an aggregate function FATAL: database rtpfcuasd does not exist ERROR: row outrow has no field fbookbalance CONTEXT: compile of PL/pgSQL function fcncheckingrow near line 5 ERROR: function fcncheckingrow() does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: syntax error at or near LOOP at character 973 ERROR: function fcncheckingrow() does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: syntax error at or near LOOP at character 970 ERROR: function fcncheckingrow() does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: syntax error at or near TYPE at character 19 ERROR: syntax error at or near LOOP at character 892 ERROR: function fcncheckingrow() does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: syntax error at or near TYPE at character 19 ERROR: syntax error at or near NEXT at character 872 ERROR: function fcncheckingrow() does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: syntax error at or near TYPE at character 19 LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection Were there other log files that may help? Please specify where they would be. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] psqlODBC Unicode 8.01.02.00 Driver Overflows On MS-Access Dates
Hi Folks, I'm trying to export a table from my MS-Access database to my postgresql database. I downloaded and installed the 8.01.02.00 driver on my Win2k system, created the datasource. Then when I try to export my table to the datasource, I get a single-world message overflow and the export aborts. I traced the cause of the overflow to a column that is in MS-Access's date/time format. Is this a known bug? Is there a workaround? -- % Randy Yates % Bird, on the wing, %% Fuquay-Varina, NC% goes floating by %%% 919-577-9882% but there's a teardrop in his eye... [EMAIL PROTECTED] % 'One Summer Dream', *Face The Music*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql documentation
Kris Jurka [EMAIL PROTECTED] writes: On Sun, 26 Feb 2006, Randy Yates wrote: I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. The 7.4 and 8.0 manuals are missing the bookmarks/TOC, but the 8.1 manual does have them. Kris Jurka I stand corrected - I was only using the 8.0 documentation. MUCH better! -- % Randy Yates % And all that I can do %% Fuquay-Varina, NC% is say I'm sorry, %%% 919-577-9882% that's the way it goes... [EMAIL PROTECTED] % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr ---(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] postgresql documentation
Hi, I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. -- % Randy Yates % Midnight, on the water... %% Fuquay-Varina, NC% I saw... the ocean's daughter. %%% 919-577-9882% 'Can't Get It Out Of My Head' [EMAIL PROTECTED] % *El Dorado*, Electric Light Orchestra http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql documentation
Joshua D. Drake [EMAIL PROTECTED] writes: Randy Yates wrote: Hi, I've noticed that the PDF version of the manuals for 8.0 and 8.1 are lacking bookmarks and/or TOC and document reference links. If this is generated via LaTeX, such links oculd easily be incorporated via the hyperref package. It would make the document much easier to navigate. The PDF version is docbook-tex-pdf .. if you can make bookmarks have at it :) Warning... docbook-tex with our documentation can take days. Joshua D. Drake Hi Joshua, Well, it's tempting! Can you tell me how, using FC4, I can grab the documentation build environment from the repository and download it to my machine? I would love to be able to make a small contribution to such a fantastic project. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.0.0 RC5-2 Processes/Interfaces Under Windows
I've installed this version on my Win2K machine and can connect using pgAdminIII just fine. I then installed libpqxx 2.4.2 via (i.e., for and under) cygwin and find that no matter what I do I cannot get the most basic test routine (test001.cxx) to execute. The problem appears to be in the connection string, but editing the source and specifying a relatively complete connection stream (i.e., including host, user, and password) does not work. I'm also getting translation errors from the library when I use a name for the host instead of the IP address. I've seen several interrelated, confusing, unstructured comments throughout both the postgresql and libpqxx installation documents that mention some things to watch out for under cygwin, but I am confused. My specific questions are as follows: 1. Is the postmaster process now a Windows process that gets started automatically at system bootup time? If so, is this postmaster process available from both native win32 applications (e.g., pgAdminIII) as well as applications which run inside a cygwin shell? 2. Do I still need to start some process when attempting to run the libpqxx test suite under cygwin? For example, there was a process (forget the name) that had to be started using either cygrunsrv or ipc-daemon2 on at least previous versions. The result of the SELECT verison(); query for my system is PostgreSQL 8.0.0rc5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Report Generation
At the risk of asking an ill-formed oft-asked question that's probably in the FAQ, is there any report generation tools that are particularly suited for use with postgres databases? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] C++ Class Library for ODBC?
Hi Folks, I'm looking for something that is completely independent of the MSVC++ Dev Studio environment, something that will compile and run under win32 using the mingw distribution. A class that allows schema definitions to be made and recordsets to be queried and updated would be wonderful. It doesn't have to include the kitchen sink! Simplificity and functionality are the prime desires. -- % Randy Yates % And all that I can do %% Fuquay-Varina, NC% is say I'm sorry, %%% 919-577-9882% that's the way it goes... [EMAIL PROTECTED] % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Free Unix-Like ODBC Driver Manager For Win32?
Hi, I want to do develop some C++ to interface with a remote postgresql database via ODBC on a win32 platform (windows 2000) via unix style. For example, I like using the mingw (minimum gnus for windows) distribution, gnumake, xemacs, cygwin, and non-IDE building. Will the iODBC driver manager work under such an environment in win32? If not, is there something that will? -- % Randy Yates % And all that I can do %% Fuquay-Varina, NC% is say I'm sorry, %%% 919-577-9882% that's the way it goes... [EMAIL PROTECTED] % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Foreign key order evaluation
Mike, I'm confused. Where is the lock? Is it on the 1 record in the model table? If so, why is that record locked? Is it possible in Postgresql to update the primary key of a record? --RY [EMAIL PROTECTED] (Michael Fuhr) writes: On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote: Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me... What do you mean by what order the foreign keys are locked? Can you give us an example of what you're doing and what problem you're trying to solve? As I mentioned in reply to your earlier message, foreign key locking and the potential for deadlock were recently brought up in pgsql-general: http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php My followup to that thread (the second link above) mentions somebody else's suggestion for a shared lock on the foreign key, but as far as I can tell, no such solution has been implemented as of 8.0.0beta3. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- % Randy Yates % And all that I can do %% Fuquay-Varina, NC% is say I'm sorry, %%% 919-577-9882% that's the way it goes... [EMAIL PROTECTED] % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Foreign key order evaluation
Randy Yates [EMAIL PROTECTED] writes: Mike, I'm confused. Where is the lock? Is it on the 1 record in the model table? If so, why is that record locked? Is it possible in Postgresql to update the primary key of a record? Let me also ask why this is a problem. It may be a lock situation but it isn't a DEADlock situation. I.e., the second transaction will just have to wait until the first completes, and the first should complete in milliseconds on a reasonable computer. Right? Or am I completely missing the boat? --Randy --RY [EMAIL PROTECTED] (Michael Fuhr) writes: On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote: Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me... What do you mean by what order the foreign keys are locked? Can you give us an example of what you're doing and what problem you're trying to solve? As I mentioned in reply to your earlier message, foreign key locking and the potential for deadlock were recently brought up in pgsql-general: http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php My followup to that thread (the second link above) mentions somebody else's suggestion for a shared lock on the foreign key, but as far as I can tell, no such solution has been implemented as of 8.0.0beta3. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- % Randy Yates % And all that I can do %% Fuquay-Varina, NC% is say I'm sorry, %%% 919-577-9882% that's the way it goes... [EMAIL PROTECTED] % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr -- % Randy Yates % Rollin' and riding and slippin' and %% Fuquay-Varina, NC% sliding, it's magic. %%% 919-577-9882% [EMAIL PROTECTED] % 'Living' Thing', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Foreign key order evaluation
Michael, Thank you for your responses. Further questions below. Michael Fuhr [EMAIL PROTECTED] writes: On Tue, Sep 28, 2004 at 01:30:08PM +, Randy Yates wrote: Randy Yates [EMAIL PROTECTED] writes: I'm confused. Where is the lock? Is it on the 1 record in the model table? Yes. If so, why is that record locked? Is it possible in Postgresql to update the primary key of a record? When you insert a row that has a foreign key reference, PostgreSQL does a SELECT FOR UPDATE on the referenced row in the foreign table; the lock prevents other transactions from changing the referenced row before this transaction completes. Unfortunately it also prevents other transactions from acquiring a lock on the same row, so those transactions will block until the transaction holding the lock completes. Well, yeah - sure it does. Given that the locking mechanism's granularity is record-level, it MUST if it is to guarantee referential integrity. I don't see this as a problem with the database unless you want to argue that the locking mechanism should have finer granularity. Given the granularity, the problem must be solved in the application or business rule logic, not the database. Let me also ask why this is a problem. It may be a lock situation but it isn't a DEADlock situation. I.e., the second transaction will just have to wait until the first completes, and the first should complete in milliseconds on a reasonable computer. Right? We don't know how long it will take for the first transaction to complete -- it might be part of a lengthy process, so performance might suffer. Also, there *is* the potential for deadlock. Take the table definitions in this message: http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php You can create a deadlock situation that raises an error, as shown in this message: http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php Here's what's happening: * Transaction 1 acquires a lock on foreign key 1. * Transaction 2 acquires a lock on foreign key 2. * Transaction 1 attempts to acquire a lock on foreign key 2, but that lock is already held by transaction 2 so transaction 1 blocks. * Transaction 2 attempts to acquire a lock on foreign key 1, but that lock is already held by transaction 1, so transaction 2 blocks. Transaction 1 is now waiting for a lock held by transaction 2, and transaction 2 is waiting for a lock held by transaction 1. Deadlock. PostgreSQL recognizes this and raises an exception in one of the transactions. The blocking and potential for deadlock can be avoided by deferring the foreign key constraints, but then foreign key violations won't be detected until the transaction attempts to commit. This just defers the problem. Yeah, it may help in some situations, but in either case the application level or business rule logic must decide what to do. In short, I don't see a problem with postgresql. The responsibility is on the developer to handle such cases. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign key order evaluation
[EMAIL PROTECTED] (Stephan Szabo) writes: On Tue, 28 Sep 2004, Randy Yates wrote: Michael Fuhr [EMAIL PROTECTED] writes: On Tue, Sep 28, 2004 at 01:30:08PM +, Randy Yates wrote: Randy Yates [EMAIL PROTECTED] writes: I'm confused. Where is the lock? Is it on the 1 record in the model table? Yes. If so, why is that record locked? Is it possible in Postgresql to update the primary key of a record? When you insert a row that has a foreign key reference, PostgreSQL does a SELECT FOR UPDATE on the referenced row in the foreign table; the lock prevents other transactions from changing the referenced row before this transaction completes. Unfortunately it also prevents other transactions from acquiring a lock on the same row, so those transactions will block until the transaction holding the lock completes. Well, yeah - sure it does. Given that the locking mechanism's granularity is record-level, it MUST if it is to guarantee referential integrity. But it doesn't need to prevent other transactions that want to just see if the row is there from continuing (as opposed to ones that want to actually modify that row). We just simply don't have that lock currently. I see the light. You mean it would be nice to be able to have a LOCK-FOR-UPDATE-ONLY lock as well as a LOCK-FOR-UPDATE-OR-READ lock, but all you have now is LOCK-FOR-UPDATE-OR-READ and that gets applied even when you don't care if someone else reads the record? -- % Randy Yates % So now it's getting late, %% Fuquay-Varina, NC%and those who hesitate %%% 919-577-9882%got no one... [EMAIL PROTECTED] % 'Waterfall', *Face The Music*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Another Security Question: User-based Roles vs. Application Business Rules
Forgive me if this is a basic and trivial (i.e., stupid) question. I haven't been using postgres very long, and I'm not an experienced database system developer. I noticed that there is a very powerful group-based security feature in postgres. Very nice - I like it alot. So one way to implement security constraints is to define appropriate groups, assign memobership of users to those groups, and then assign group-based permissions to the assorted database objects (e.g., tables). Fantastic! However, ... this requires each entity accessing the databse to be defined as a user. In the context of a web application, this paradigm doesn't necessarily make sense since there may be many unknown users. Somehow those users must be mapped to a role. I suppose you can map all unknown users into the user guest and then define guest privileges appropriately. Is this a good approach? Is there better way to do this? Is there an altnerate way to consider? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(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] building postgres for windows 2000?
Has anyone successfully done this? I've got cygwin and the very first step crashes - bash-2.05$ pwd pwd /cygdrive/e bash-2.05$ cd postgresql-7.4.5 cd postgresql-7.4.5 bash-2.05$ ./configure ./configure bash: ./configure: bad interpreter: No such file or directory bash-2.05$ -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(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] cygserver -S crashes under Windows 2000/cygwin
$ /usr/sbin/cygserver -S Segmentation fault (core dumped) [EMAIL PROTECTED] ~ $ -- % Randy Yates % ...the answer lies within your soul %% Fuquay-Varina, NC% 'cause no one knows which side %%% 919-577-9882% the coin will fall. [EMAIL PROTECTED] % 'Big Wheels', *Out of the Blue*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] initdb crashes under Windows
Can't get the postgres server started under Win2000/cygwin. Here's what happens: $ initdb -D /Gauss/rr/data -W The files belonging to this database system will be owned by user yates. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /Gauss/rr/data... ok creating directory /Gauss/rr/data/base... ok creating directory /Gauss/rr/data/global... ok creating directory /Gauss/rr/data/pg_xlog... ok creating directory /Gauss/rr/data/pg_clog... ok selecting default max_connections... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 10 selecting default shared_buffers... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 50 creating configuration files... ok creating template1 database in /Gauss/rr/data/base/1... FATAL: lock file /Gauss/rr/data/postmaster.pid already exists HINT: Is another postgres (PID 1672) running in data directory /Gauss/rr/data? initdb: failed [EMAIL PROTECTED] ~ -- % Randy Yates % Rollin' and riding and slippin' and %% Fuquay-Varina, NC% sliding, it's magic. %%% 919-577-9882% [EMAIL PROTECTED] % 'Living' Thing', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(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] Datatypes Documentation
It might be a good idea to place a reference to table 8-1 in the various subsections of the Datatypes section in the online postgresql documentation. Otherwise, when hyperjumping from the table of contents to a specific section, table 8-1 may not be in sight (as it is not for integer datatypes) and the user is left wondering why these datatypes aren't further defined. -- % Randy Yates % Remember the good old 1980's, when %% Fuquay-Varina, NC% things were so uncomplicated? %%% 919-577-9882% 'Ticket To The Moon' [EMAIL PROTECTED] % *Time*, Electric Light Orchestra http://home.earthlink.net/~yatescr ---(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] Firewall Security Requirements for Postgresql Access
Is opening up port 5432 (R/W both directions) all that is required of a firewall in order to access a postgres database outside the firewall? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] One Database per Data File?
I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes, I'd like to keep the database files for each of several projects separate. This means, e.g., that postmaster must have multiple instances going simultaneously? I'm thinking the answer is NO because, for one, the TCPIP connection seems to be to ONE instance of postmaster which then sorts out which database objects are in its container. Am I close? -- % Randy Yates % Maybe one day I'll feel her cold embrace, %% Fuquay-Varina, NC%and kiss her interface, %%% 919-577-9882%til then, I'll leave her alone. [EMAIL PROTECTED] %'Yours Truly, 2095', *Time*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Access MDB Schema Import Tool?
Is there a tool that allows the tables and relationships of an Access database to be moved into postgresql (7.4.5)? Sorry if this has been asked before. There also may be new tools that have recently come out. -- % Randy Yates % Watching all the days go by... %% Fuquay-Varina, NC% Who are you and who am I? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] One Database per Data File?
Christopher Browne [EMAIL PROTECTED] writes: Oops! Randy Yates [EMAIL PROTECTED] was seen spray-painting on a wall: I'm a complete newbie to postgres so please look the other way if these questions are really stupid. Is it legitimate to have one database per data file? For organizational and backup purposes, I'd like to keep the database files for each of several projects separate. This means, e.g., that postmaster must have multiple instances going simultaneously? I'm thinking the answer is NO because, for one, the TCPIP connection seems to be to ONE instance of postmaster which then sorts out which database objects are in its container. Am I close? Not terribly. For a given cluster (e.g. - an instance initialized using initdb), you have a set of databases, each of which is indicated by a directory under 'base/' in that cluster. That does not seem to be the case. I have three subdirectories in my base/ directory, but according to PGADMIN III, only one database. Within each database in the cluster, each table and index is indicated by one (or more, if size 1GB) files. Thus, each database will have numerous data files, essentially one per table and one per index. If you rummage around in the files, you can learn quite a lot about the structuring of things. Each file has a number; that number corresponds to the OID number in pg_class. Thus, if you find a file called 17441, then you could find out more about it by the query select * from pg_class where oid = 17441; -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://cbbrowne.com/info/internet.html I love the way Microsoft follows standards. In much the same manner that fish follow migrating caribou. -- Sinister Midget Ahh, ok. So how does this answer my question or help me achieve my goal of one database per initdb file set? You also have not answered whether or not postmaster can have multiple instances running, each pointing to a different initdb file set. -- % Randy Yates % How's life on earth? %% Fuquay-Varina, NC% ... What is it worth? %%% 919-577-9882% 'Mission (A World Record)', [EMAIL PROTECTED] % *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres and Tools Intro?
Hi Jeff, THANKS for the pointers - much appreciated! --Randy Jeff Eckermann [EMAIL PROTECTED] writes: --- Randy Yates [EMAIL PROTECTED] wrote: Hi Richard, Thanks for writing. Basically I'd like to know what the core applications/tools are and what some of the other more exotic tools are. For example, pgadmin looks like it's gone through a bazillion versions. Which is the latest/greatest? What exactly does it do? PgAdminIII is the latest. It is a graphical interface, especially useful for database administration and development. Many people use it and are happy. If I want to connect to postgres securely from a remote location over the net, what are my options? Is that part of the postgres ODBC driver, or is another layer required for the security part? You can use SSL with ODBC, but it requires a bit of fiddling about. Search the archives for information about that. Why should or shouldn't I use ODBC for connecting remotely? ODBC is working fine for lots of people right now. The other mature interfaces include libpq (C) and JDBC (Java). There are others which reportedly work well. It all depends on your choice of development platform. More in the exotic territory, are there tools for translating database schemas (I would like to move my MS Access database into postgres)? PgAdminII (the previous version) had a plugin Database Migration Wizard. I have kept my PgAdminII installation just because of this. I am very happy with the results I have had translating MS Access databases to PostgreSQL by using that Wizard. Is there a tool for converting a postgres query or table into an MS Access .mdb file? This would be very useful, the scenario being that the big database resides in postgres on a remote server with a browser interface, but users can download snippets of data (or maybe even the whole tamale) in Access format to their local machines. No such tool exists that I know of. You could do it in code from an existing .mdb file, e.g. something that runs when the file is opened. Got any suggestions? --Randy Richard Huxton [EMAIL PROTECTED] writes: Randy Yates wrote: This has probably been asked before so please be gracious. I have looked on the postgres site and didn't find anything satisfying. Is there *good* overview of postgres and associated utilities? Hmm - not for the family as a whole. Part of the problem is that there are quite a few add-on tools. The postgresql.org docs cover the database quite well - between the reference manuals and the FAQ it gives a good picture. What particular aspects are you interested in? I'm sure we can find people who can help. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- % Randy Yates % Maybe one day I'll feel her cold embrace, %% Fuquay-Varina, NC% and kiss her interface, %%% 919-577-9882%til then, I'll leave her alone. [EMAIL PROTECTED] %'Yours Truly, 2095', *Time*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com -- % Randy Yates % Rollin' and riding and slippin' and %% Fuquay-Varina, NC% sliding, it's magic. %%% 919-577-9882% [EMAIL PROTECTED] % 'Living' Thing', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
