[GENERAL] Configure default for sorting of null-values?
Hi, From the docs I see that you can control null values being sorted before or after all non-null-values using 'NULLS LAST' or 'NULLS FIRST' in the order by clause. The default behaviour is to act as though nulls are larger than non-nulls. My question is, is there a way to configure this default, at connection level or at server level? Thanks, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem in getting Output
Dear all, First of all Sorry If i break any rule while posting this problem. I have a table named *naxalite_entity1(source_id integer,obj_type_id integer,obj_text character varying). *It has the following form of data 45454545 1 Adarsh 45454545 1 Rahul 45454545 1 Yahoo 45454545 3 College 45454545 3 Organization 45454545 9 attracts 45454545 9 dissatisfied and so on and think U guessed the form of data . It has 11000 rows with sourc_id repeating with 1,3,9 . Now I want to display the obj_text in column form for respective source_ids accordind obj_type_id as source_id person event organization e.g 45454545 Adarsh attracts Yahoo. and when there is no value for person or event there is Null enetered. Thanks in Advance * * -- 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] On-disk size of db increased after restore
On Tue, 2010-08-31 at 18:08 -0600, Scott Marlowe wrote: ny chance you've restored to different dbs and have two copies? Or double the data in one db? Nope. This is a single database, and I restored only once.. # of rows in tables match to the ones in prod... -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] On-disk size of db increased after restore
2010/9/1 Devrim GÜNDÜZ dev...@gunduz.org: On Tue, 2010-08-31 at 18:08 -0600, Scott Marlowe wrote: ny chance you've restored to different dbs and have two copies? Or double the data in one db? Nope. This is a single database, and I restored only once.. # of rows in tables match to the ones in prod... Have you run this on each server? SELECT datname, pg_database_size(datname) FROM pg_catalog.pg_database ORDER BY 2 DESC And if a single database size differs, run this against the database: SELECT tablename, pg_table_size(schemaname || '.' || tablename) FROM pg_catalog.pg_tables ORDER BY 2 DESC Should at least narrow down where the space is being used. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Table update problem works on MySQL but not Postgres
-Original Message- From: Raymond C. Rodgers [mailto:sinful...@gmail.com] Sent: Tuesday, August 31, 2010 7:56 PM To: pgsql-general@postgresql.org Subject: Table update problem works on MySQL but not Postgres Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. I'm working on a project for a client where I have a table for arbitrary categories to be applied to their data, and they need to be able to set the order in which the categories appear. A simplified version of the table as I created is as follows: create table mydemo (cat_id int not null, cat_name varchar(25) not null, cat_order int not null, primary key(cat_id,cat_order)); During my coding, I unwittingly backed myself into a corner, fully expecting to issue queries such as: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 in order to insert categories at the top of the sorted list for example. As you can probably guess, this query doesn't work very well. On both MySQL and PostgreSQL I get a constraint violation. That makes sense; I screwed up. But out of pure curiosity to see if I could circumvent this issue, I added an order clause, making that query this instead: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 order by cat_order desc This is where the interesting thing happens: On MySQL the query actually works as intended, but it doesn't on PostgreSQL. As I said, I'm sure this is not a bug in PostgreSQL, but the lack of a stupid user trick. While my project is on MySQL, and I could theoretically leave my code as is to take advantage of this trick, I'm sure I'd be a complete idiot to leave it instead of fixing it. However, I wanted to share this little tidbit with the PostgreSQL community. Raymond What you need for your update to work is deferred unique constraints. I think, this feature appears in 9.0. Regards, Igor Neyman -- 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] Scalar subquery
Thanks, Tom Can this be clarified in docs? It is stated there now that scalar subquery is one of the kinds of expressions and it is somewhat counter-intuitive that an expression may sometimes not respect its own degree of volatility. On Wed, Sep 1, 2010 at 2:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: Vyacheslav Kalinin v...@mgcp.com writes: I just got my hands on mysql (5.0.something) and it does not cache the scalar subquery result. So... now I'm completely puzzled whether this is a bug, a desired result or just a loosely standardized thing. It's loosely standardized. AFAICS, the spec doesn't address the detailed semantics of subqueries at all, except in wording to this effect: Each subquery in the search condition is effectively executed for each row of T and the results used in the ap- plication of the search condition to the given row of T. If any executed subquery contains an outer reference to a column of T, the reference is to the value of that column in the given row of T. There is wording like this for subqueries in WHERE and HAVING, but I haven't found anything at all that mentions the behavior for subqueries in the SELECT targetlist. In any case, the fact that they said effectively executed and not simply executed seems to be meant to leave implementors a lot of wiggle room. In particular, there isn't any wording that I can find suggesting that the presence of volatile (or in the spec's classification, nondeterministic) functions ought to affect the behavior. PG's interpretation is that if there is no outer reference in a subquery, it's okay to implement it as an initplan, meaning it gets evaluated at most once per call of the containing query. We don't pay attention to whether there are volatile functions in there. regards, tom lane
[GENERAL] Running Total with a Set Maximum
Hi, Could someone out there help me. Given the following set of values +3 +3 0 +3 +3 -3 0 +3 I want to have a maximum sum of 6 at any one point , in other words I want the following +3 -- 3 +3 -- 6 0 -- 6 +3 -- 6 +3 -- 6 -3 -- 3 0 -- 3 +3 -- 6 How can I do this ? Thank you Paul Newman Disclaimer This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification not withstanding, any comments or opinions expressed are those of the originator, not of Tripoint Ltd, unless otherwise explicitly stated.
[GENERAL] postgreSQL problem
hello! I just recently installed PostgreSQL for my HoldemManager application, and after a restart, there was actually a new windows user account named postgre on the startup along with my admin account for windows (if i didnt make it clear -- the account to log in to windows, before windows start). So is that a normal thing or not, because i know i didnt do anything wrong with installation or the setup. Thanks in advance for answers, Luka
Re: [GENERAL] Running Total with a Set Maximum
On Wed, Sep 1, 2010 at 5:20 AM, Paul Newman pnew...@prohire.co.uk wrote: Hi, Could someone out there help me. Given the following set of values +3 +3 0 +3 +3 -3 0 +3 I want to have a maximum sum of 6 at any one point , in other words I want the following +3 -- 3 +3 -- 6 0 -- 6 +3 -- 6 +3 -- 6 -3 -- 3 0 -- 3 +3 -- 6 How can I do this ? Looks like what you really want is a running total where the max is no more than 6 at any time. Except it's not really a running total since the current total is dependent on the last max? You might be able to pull this off with some combination of window and max, but really this is a rather odd mathematical operation so it's not easy to code up directly in a single query. If you can add another column that is the running total to date and if you can use a sequence for another column (primary key perhaps?) then the problem becomes pretty trivial: at insert time you can simply pick the running total from the most recent row (found via the max sequence) and create the new (pseudo) running total with a case statement. If you can't do that, then I think it would be easiest to code this up in a procedure, but before anyone jumps on that you might want to let us know if you are free to add columns to the schema? -- Peter Hunsberger -- 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] Problems with ODBC Driver
Hi. There is very little information which explores a problem. What thing is an actual error message? The following is my imagination As for specific field, it may be the LOWER problem of a character. example) -- CREATE TABLE xx(Field TEXT); postgres=# SELECT Field FROM xx; ERROR: column field does not exist LINE 1: SELECT Field FROM xx; postgres=# SELECT Field FROM xx; Field --- (0 rows) If my misunderstand, the error message which you show will be able to be solved. Regards, Hiroshi Saito - Original Message - From: Phil Jackson sapi...@clear.net.nz I have a notebook that I am using as a server for testing purposes and it has the official ODBC driver installed. I can access this and use it connect to PostreSql. On a second machine on the same network - I also have the same ODBC driver installed. The behaviour of this one is quite different. I have a test program that works on the server but when I run it on this workstation, it can create a file with a couple of fields which I can verify by looking inside PostgreSQL. But when I go to open up that file and refer to one of its fields, I get an error message saying the field doesn't exist. I am running XP Professional on both machines. I have no idea what is happening - I have opened up the firewall on both machines to allow port 5432 to be used by all applications with TCP (also tried TCP and UDP). Does anyone have any suggestions? Cheers Phil Jackson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connection question
I have a two-user point-of-sale application on Windows XP PRO. The DB runs on the cash register. The second user is a manager's computer. They are connected through a wired router which is also connected to an internet cable modem. The manager's computer just checks statistics on the DB and maintains a connection all the time. The cash register application does not do any network operations and is not even aware of the network. About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error Unable to write to the backend or something very close to that. The DB itself seems fine as we can restart the cash register and all is well. Anyone have any idea why this happens? We are running PostgreSQL 8.3.1-1 and the applications are Java 1.6.14. Bayless
Re: [GENERAL] Connection question
On 01/09/10 16:22, Bayless Kirtley wrote: I have a two-user point-of-sale application on Windows XP PRO. The DB runs on the cash register. The second user is a manager's computer. They are connected through a wired router which is also connected to an internet cable modem. The manager's computer just checks statistics on the DB and maintains a connection all the time. The cash register application does not do any network operations and is not even aware of the network. About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error Unable to write to the backend or something very close to that. The DB itself seems fine as we can restart the cash register and all is well. Anyone have any idea why this happens? We are running PostgreSQL 8.3.1-1 and the applications are Java 1.6.14. Bayless It would be a good option to replace your entire system. However, if you don't wish to do that, just simply connect the manager's pc and cash register with a network switch, which is separate from the router. So when your router restarts, the switch won't do down, so the 2 machines remain connected. That is only a work around though, and ultimately you'd want a better solution Cheers
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On 09/01/10 16:13, Igor Neyman wrote: -Original Message- From: Raymond C. Rodgers [mailto:sinful...@gmail.com] Sent: Tuesday, August 31, 2010 7:56 PM To: pgsql-general@postgresql.org Subject: Table update problem works on MySQL but not Postgres update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order= 0 in order to insert categories at the top of the sorted list for example. As you can probably guess, this query doesn't work very well. On both MySQL and PostgreSQL I get a constraint violation. That makes sense; I screwed up. What you need for your update to work is deferred unique constraints. I think, this feature appears in 9.0. Yes: http://www.postgresql.org/docs/9.0/static/sql-set-constraints.html Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately. In 8.4 it says: Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable. Triggers that are declared as constraint triggers are also affected. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to defer ON DELETE CASCADE
Hi, If I create a DEFERRED ON DELETE CASCADE constraint, it doesn't really work as I expected. I expected it to defer the deletion to the end of the transaction, but it dosn't. Is there a way to replace the contents of a table which has foreign keys? There's no MERGE/UPSERT/whatever either. = SELECT version(); version - PostgreSQL 8.4.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit (1 row) CREATE TABLE product (id INT PRIMARY KEY); CREATE TABLE product_item (product_id INT REFERENCES product(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED); INSERT INTO product VALUES (5); INSERT INTO product_item VALUES (5); BEGIN; DELETE FROM product; INSERT INTO product VALUES (5); COMMIT; SELECT * FROM product_item; product_id (0 rows) =
Re: [GENERAL] Connection question
On Wed, Sep 01, 2010 at 10:22:20AM -0500, Bayless Kirtley wrote: About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error Unable to write to the backend or something very close to that. The DB itself seems fine as we can restart the cash register and all is well. Anyone have any idea why this happens? We are running PostgreSQL 8.3.1-1 and the applications are Java 1.6.14. I'd guess you're connecting to PG using the network and not staying within Windows. What does your connection string look like? Getting it using localhost would be my suggestion. -- Sam http://samason.me.uk/ -- 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] Problems with ODBC Driver
On Thu, 2010-09-02 at 14:04 +1200, Phil Jackson wrote: Does anyone have any suggestions? What does the ODBC log say? What does the PostgreSQL Log (or event viewer) say? JD Cheers Phil Jackson -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] How to defer ON DELETE CASCADE
Arjen Nienhuis a.g.nienh...@gmail.com writes: If I create a DEFERRED ON DELETE CASCADE constraint, it doesn't really work as I expected. I expected it to defer the deletion to the end of the transaction, but it dosn't. Yeah, this is per SQL spec as far as we can tell. Constraint checks can be deferred till end of transaction, but referential actions are not deferrable. They always happen during the triggering statement. For instance SQL99 describes the result of a cascade deletion as being that the referencing row is marked for deletion immediately, and then 15) All rows that are marked for deletion are effectively deleted at the end of the SQL-statement, prior to the checking of any integrity constraints. (see 11.8 referential constraint definition general rules) regards, tom lane -- 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] On-disk size of db increased after restore
On 31/08/10 22:17, Devrim GÜNDÜZ wrote: I have seen the opposite of this tons of times before, but I haven't seen an increase after restore before. Does anyone know what may cause this? Where should I look at? Could you have changed the fillfactor on some big tables/indexes in the live database after populating them? Is the locale the same on each machine/db? -- Richard Huxton Archonet Ltd -- 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] On-disk size of db increased after restore
Hi, On Wed, 2010-09-01 at 21:13 +0100, Richard Huxton wrote: Could you have changed the fillfactor on some big tables/indexes in the live database after populating them? Nope. Even a pg_dump -h prod|psql backup_node resulted with the same issue Is the locale the same on each machine/db? These are generic RPM installations, and locales are the same... Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] On-disk size of db increased after restore
On 01/09/10 21:32, Devrim GÜNDÜZ wrote: On Wed, 2010-09-01 at 21:13 +0100, Richard Huxton wrote: Could you have changed the fillfactor on some big tables/indexes in the live database after populating them? Nope. Even a pg_dump -h prod|psql backup_node resulted with the same issue Is the locale the same on each machine/db? These are generic RPM installations, and locales are the same... OK - so not fillfactor and not some unicode-related padding. I can't see how a 32 vs 64-bit architecture change could produce anything like a doubling of database size. Is it that each file is doubled in size, or are some much larger while others are about the same? If the indexes are to blame it's presumably something to do with the order of row access during index creation. -- Richard Huxton Archonet Ltd -- 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] On-disk size of db increased after restore
Excerpts from Richard Huxton's message of mié sep 01 16:39:55 -0400 2010: OK - so not fillfactor and not some unicode-related padding. I can't see how a 32 vs 64-bit architecture change could produce anything like a doubling of database size. Depending on table schemas, why not? e.g. consider a table with a single bool column. It will waste 7 bytes on 8-byte MAXALIGN machine but only 3 on a 4-byte MAXALIGN machine. Of course, this is a corner case. Devrim didn't specify the platform on each server AFAICS. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] On-disk size of db increased after restore
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Richard Huxton's message of mié sep 01 16:39:55 -0400 2010: OK - so not fillfactor and not some unicode-related padding. I can't see how a 32 vs 64-bit architecture change could produce anything like a doubling of database size. Depending on table schemas, why not? e.g. consider a table with a single bool column. It will waste 7 bytes on 8-byte MAXALIGN machine but only 3 on a 4-byte MAXALIGN machine. Yeah, but after you account for row header overhead, the worst-case percentage bloat still should be a lot less than 2X. It would help if Devrim could break down the bloat to the level of individual tables/indexes. regards, tom lane -- 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] postgreSQL problem
Excerpts from Luka Novak's message of mié sep 01 06:35:13 -0400 2010: hello! I just recently installed PostgreSQL for my HoldemManager application, and after a restart, there was actually a new windows user account named postgre on the startup along with my admin account for windows (if i didnt make it clear -- the account to log in to windows, before windows start). So is that a normal thing or not, because i know i didnt do anything wrong with installation or the setup. Yes, the PostgreSQL installation creates a postgres Windows user account. This is normal and expected. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] On-disk size of db increased after restore
On Wed, 2010-09-01 at 16:50 -0400, Alvaro Herrera wrote: Devrim didn't specify the platform on each server AFAICS. Both are Red Hat /CentOS 5.5, x86_64, running with identical software versions... I first inclined to blame LVM+storage, however I could duplicate this issue on local disks, too. This happened recently -- restoring data on the same machine about 3 weeks ago did not have this issue. I need to figure out what may happened since then... Alvaro, this may be a stupid question but: I enabled custom autovac settings for some tables. These changes are included in the dump. May this affect on-disk size? Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Problems with ODBC Driver
Hi Hiroshi The same program works on the server unchanged - that's why I think it is something to do with the driver. The error message says that the field name doesn't exist when it does as can be verified by looking at the database in Postgresql. Cheers Phil Jackson On 9/2/2010 3:19 AM, Hiroshi Saito wrote: Hi. There is very little information which explores a problem. What thing is an actual error message? The following is my imagination As for specific field, it may be the LOWER problem of a character. example) -- CREATE TABLE xx(Field TEXT); postgres=# SELECT Field FROM xx; ERROR: column field does not exist LINE 1: SELECT Field FROM xx; postgres=# SELECT Field FROM xx; Field --- (0 rows) If my misunderstand, the error message which you show will be able to be solved. Regards, Hiroshi Saito - Original Message - From: Phil Jackson sapi...@clear.net.nz I have a notebook that I am using as a server for testing purposes and it has the official ODBC driver installed. I can access this and use it connect to PostreSql. On a second machine on the same network - I also have the same ODBC driver installed. The behaviour of this one is quite different. I have a test program that works on the server but when I run it on this workstation, it can create a file with a couple of fields which I can verify by looking inside PostgreSQL. But when I go to open up that file and refer to one of its fields, I get an error message saying the field doesn't exist. I am running XP Professional on both machines. I have no idea what is happening - I have opened up the firewall on both machines to allow port 5432 to be used by all applications with TCP (also tried TCP and UDP). Does anyone have any suggestions? Cheers Phil Jackson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] On-disk size of db increased after restore
Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= dev...@gunduz.org writes: Alvaro, this may be a stupid question but: I enabled custom autovac settings for some tables. These changes are included in the dump. May this affect on-disk size? Doesn't seem likely that that would matter to the state immediately after restoring; autovac should only affect things after you've done some deletes/updates in the tables. But are you sure there aren't some fillfactor tweaks in there too? regards, tom lane -- 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] On-disk size of db increased after restore
On Wed, 2010-09-01 at 17:32 -0400, Tom Lane wrote: But are you sure there aren't some fillfactor tweaks in there too? I'm sure. fillfactor related changes are on the radar, but I did not commit them yet... -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] postgreSQL problem
On 09/01/10 2:01 PM, Alvaro Herrera wrote: Excerpts from Luka Novak's message of mié sep 01 06:35:13 -0400 2010: hello! I just recently installed PostgreSQL for my HoldemManager application, and after a restart, there was actually a new windows user account named postgre on the startup along with my admin account for windows (if i didnt make it clear -- the account to log in to windows, before windows start). So is that a normal thing or not, because i know i didnt do anything wrong with installation or the setup. Yes, the PostgreSQL installation creates a postgres Windows user account. This is normal and expected. however, that user shouldn't show up on the Welcome friendly style login screen.OTOH, I'm not sure what mechanism is used to prevent this. ah, a little googling... on 32bit windows.. registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList has a value with the username and a DWORD of 0x to block the user from being displayed. on 64bit Win7 at least, this is in HKLM\Software\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList instead. -- 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] On-disk size of db increased after restore
On Wed, 2010-09-01 at 16:59 -0400, Tom Lane wrote: It would help if Devrim could break down the bloat to the level of individual tables/indexes. While setting up this data (by anonymizing table names, etc), I saw that almost all relations are smaller on backup server, as compared to prod. Yeah, there is a little bloat on master, but at the end of the day, total size is expected to be smaller on backup. See 5 top disk space eaters (in bytes): Prod: idx1|1441636352 bytes tbl3|3248930816 bytes tbl4|9065570304 bytes tbl5|10850549760 bytes Backup: idx1|1215463424 bytes tbl3|3189325824 bytes tbl4|8910422016 bytes tbl5|10814955520 bytes Almost all relations are smaller on backup. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Problems with ODBC Driver
Hi. Um, it is very strange. Can you send us the mylog of psqlODBC? Regards, Hiroshi Saito Hi Hiroshi The same program works on the server unchanged - that's why I think it is something to do with the driver. The error message says that the field name doesn't exist when it does as can be verified by looking at the database in Postgresql. Cheers Phil Jackson On 9/2/2010 3:19 AM, Hiroshi Saito wrote: Hi. There is very little information which explores a problem. What thing is an actual error message? The following is my imagination As for specific field, it may be the LOWER problem of a character. example) -- CREATE TABLE xx(Field TEXT); postgres=# SELECT Field FROM xx; ERROR: column field does not exist LINE 1: SELECT Field FROM xx; postgres=# SELECT Field FROM xx; Field --- (0 rows) If my misunderstand, the error message which you show will be able to be solved. Regards, Hiroshi Saito - Original Message - From: Phil Jackson sapi...@clear.net.nz I have a notebook that I am using as a server for testing purposes and it has the official ODBC driver installed. I can access this and use it connect to PostreSql. On a second machine on the same network - I also have the same ODBC driver installed. The behaviour of this one is quite different. I have a test program that works on the server but when I run it on this workstation, it can create a file with a couple of fields which I can verify by looking inside PostgreSQL. But when I go to open up that file and refer to one of its fields, I get an error message saying the field doesn't exist. I am running XP Professional on both machines. I have no idea what is happening - I have opened up the firewall on both machines to allow port 5432 to be used by all applications with TCP (also tried TCP and UDP). Does anyone have any suggestions? Cheers Phil Jackson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] postgreSQL problem
On Wed, Sep 1, 2010 at 3:52 PM, John R Pierce pie...@hogranch.com wrote: however, that user shouldn't show up on the Welcome friendly style login screen. OTOH, I'm not sure what mechanism is used to prevent this. ah, a little googling... on 32bit windows.. registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList has a value with the username and a DWORD of 0x to block the user from being displayed. on 64bit Win7 at least, this is in HKLM\Software\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList Is there a more universal, trans-windows platform api for a call to make such a change? Especially a switch at account creation time. -- To understand recursion, one must first understand recursion. -- 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] postgreSQL problem
On 09/01/10 7:40 PM, Scott Marlowe wrote: On Wed, Sep 1, 2010 at 3:52 PM, John R Piercepie...@hogranch.com wrote: however, that user shouldn't show up on the Welcome friendly style login screen.OTOH, I'm not sure what mechanism is used to prevent this. ah, a little googling... on 32bit windows.. registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList has a value with the username and a DWORD of 0x to block the user from being displayed. on 64bit Win7 at least, this is in HKLM\Software\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList Is there a more universal, trans-windows platform api for a call to make such a change? Especially a switch at account creation time. it appears to sort of be an afterthought I don't think there's any API or whatever.If I'd been designing it, I likely would have done it with group membership... like anyone who was a member of 'Login Users' or something. but hindsight is always 20-20. -- 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] Connection question
On 09/01/2010 11:22 PM, Bayless Kirtley wrote: About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. With modern OSes, and many much older ones, it's not of course at all. Windows XP Pro is quite odd in that it breaks TCP/IP connections when an interface goes down and comes back up with the same IP address. Windows 7 even retains my SSH connections, made over wifi, when suspended and resumed! They only break if PuTTY tries to send a packet while the interface is still down after resume. Really, resetting a switch, unplugging a network cable and plugging it back in, etc shouldn't break TCP/IP connections, unless it triggers the connected host to do a new DHCP request, and the DHCP server hands out a different IP. No decent DHCP server will do that, but some cheap and nasty modem/router units don't store DHCP leases across a reboot so they forget their MAC address to IP address mappings. Anyway, that's a bit of a tangent, since you're on XP Pro and stuck with its rather less than ideal handling of connection loss. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error Unable to write to the backend or something very close to that. Is the register application connecting to localhost (127.0.0.1) or to the public IP address assigned by DHCP to the register's ethernet interface? If the latter, you're being bitten by Windows XP tossing out all TCP/IP connections involving that IP. If you're not sure, the easiest way I can think of to find out is to unplug the register from the network, restart the router and see if it can still connect. It should be able to. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Double iteration in the buffer cache code
Hello, Did someone fix the double iteration in the buffer cache code as discussed at in the meantime: http://www.mail-archive.com/pgsql-general@postgresql.org/msg137230.html Thnx. Ciao, Gerhard -- http://www.wiesinger.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
