Re: [GENERAL] installation problem with postgres password
yes, many other programs. although none of them needed to create an account On Wed, Jul 18, 2012 at 6:19 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On 07/17/2012 03:29 AM, KOTa wrote: so nobody can help? :( If I followed correctly the problem is: 1) You are on Windows 7 Home 2) You have an admin account and Guest account. 3) You are using the One-Click Installer 4) Using either account the install fails when it asks for the superuser password with the following message. The password specified does not meet the local or domain policy. Check the minimum length, password complexity and password history requirements. My question 1) Have you installed any other program successfully on this machine? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Chicken/egg problem with range types
On 18 Jul 2012, at 5:08, Tom Lane wrote: Scott Bailey artacu...@gmail.com writes: I'm trying to create a discrete range type and I'm having trouble with the canonical function. I wonder whether we could improve this by postponing the no-shell-types check from creation to function runtime. It would be annoying to have to make an additional catalog lookup at runtime just for typisdefined, but I think that probably we could fold it in with an existing fetch of the pg_type row during parsing of the calling query, so that no run-time overhead is added. I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of the transaction. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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] index update
Pawel Veselov pawel.vese...@gmail.com wrote: Hi. If I have a lot (10k) tables, and each table has a btree index, and all the tables are being constantly inserted into, would all the indexes have to be in memory, and would effectively start fighting for space? Thank you, Pawel. -- 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] Can't reset password
On Tue, 17 Jul 2012 22:42:22 -0400, Keith Chen wrote: Hello, I deleted my Postgresql and try to reinstall it. I try to reset the password by typing in net user postgres *. However, the new password doesn't work when the installer prompt me to input the password. Could you please help me? Thanks, Keith I changed postgesql password using mmc.exe and users groups snap-in, then reset password. This is safe unless you have some encrypted files. If you have simple editions of Vista or Windows 7 then this snap-in may be not available. Uncheck anything like password expired, user need to change password on log-in. Regards -- 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] Can I create a trigger to add another record based on the inserted record in the same table?
On Jul 17, 2012, at 20:59, Mohd Shaiza Ibrahim mohdsha...@gmail.com wrote: Hi, Can you guys please help me? My question sounds like this. When I insert a new record in a table, can I create a trigger to add another record based on the inserted record in the same table? For example, INSERT INTO employee (emp_id, emp_name) VALUES (0001, 'Jack'); The result: Select * from employee; emp_id | emp_name 0001 | Jack 0002 | Bob I've tried running the statement below but it doesn't work. Infinite loop i'm guessing. Infinite loop is correct. You need to fix your logic to solve that problem or consider a new design. Maybe restrict inserts to the table to a security definer function and put you dual insert logic into it. David J. -- 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] Can I create a trigger to add another record based on the inserted record in the same table?
On Jul 18, 2012, at 8:37, David Johnston pol...@yahoo.com wrote: On Jul 17, 2012, at 20:59, Mohd Shaiza Ibrahim mohdsha...@gmail.com wrote: Hi, Can you guys please help me? My question sounds like this. When I insert a new record in a table, can I create a trigger to add another record based on the inserted record in the same table? For example, INSERT INTO employee (emp_id, emp_name) VALUES (0001, 'Jack'); The result: Select * from employee; emp_id | emp_name 0001 | Jack 0002 | Bob I've tried running the statement below but it doesn't work. Infinite loop i'm guessing. Infinite loop is correct. You need to fix your logic to solve that problem or consider a new design. Maybe restrict inserts to the table to a security definer function and put you dual insert logic into it. It may not be infinite trigger but you do not show the CREATE TRIGGER statement you are using so it is impossible to know. You mention same table but it appears you are trying to do audit logging which uses different tables. You also do not say what you mean by it doesn't work. David J. -- 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] installation problem with postgres password
update. managed to run it via command prompt postgres_install.exe --serviceaccount postgres it did start. it still asks for a password, but because i could not create any password for this user and installation does not accept empty password, i am still stuck On Wed, Jul 18, 2012 at 11:27 AM, KOTa kota.a...@gmail.com wrote: thank, this is a good idea. 1. i created account postgres 2. i tried to create a password for it, but got the same error message 3. i tried to run postgres installation with --serviceaccount option, but can't figure out a way to do it in win 7. (i tried to do it as i did in win XP - from command prompt, or creating shortcut with option included, but win 7 did't allow it) On Wed, Jul 18, 2012 at 8:57 AM, Sachin Srivastava sachin.srivast...@enterprisedb.com wrote: You can use an already existing account by specifying --serviceaccount CLI option. So you can: i) Create a new account and then use it. ii) Use an already existing account. See if this helps. On Wed, Jul 18, 2012 at 11:07 AM, KOTa kota.a...@gmail.com wrote: yes, many other programs. although none of them needed to create an account On Wed, Jul 18, 2012 at 6:19 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On 07/17/2012 03:29 AM, KOTa wrote: so nobody can help? :( If I followed correctly the problem is: 1) You are on Windows 7 Home 2) You have an admin account and Guest account. 3) You are using the One-Click Installer 4) Using either account the install fails when it asks for the superuser password with the following message. The password specified does not meet the local or domain policy. Check the minimum length, password complexity and password history requirements. My question 1) Have you installed any other program successfully on this machine? -- Adrian Klaver adrian.kla...@gmail.com -- Regards, Sachin Srivastava EnterpriseDB, India -- 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] installation problem with postgres password
thank, this is a good idea. 1. i created account postgres 2. i tried to create a password for it, but got the same error message 3. i tried to run postgres installation with --serviceaccount option, but can't figure out a way to do it in win 7. (i tried to do it as i did in win XP - from command prompt, or creating shortcut with option included, but win 7 did't allow it) On Wed, Jul 18, 2012 at 8:57 AM, Sachin Srivastava sachin.srivast...@enterprisedb.com wrote: You can use an already existing account by specifying --serviceaccount CLI option. So you can: i) Create a new account and then use it. ii) Use an already existing account. See if this helps. On Wed, Jul 18, 2012 at 11:07 AM, KOTa kota.a...@gmail.com wrote: yes, many other programs. although none of them needed to create an account On Wed, Jul 18, 2012 at 6:19 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On 07/17/2012 03:29 AM, KOTa wrote: so nobody can help? :( If I followed correctly the problem is: 1) You are on Windows 7 Home 2) You have an admin account and Guest account. 3) You are using the One-Click Installer 4) Using either account the install fails when it asks for the superuser password with the following message. The password specified does not meet the local or domain policy. Check the minimum length, password complexity and password history requirements. My question 1) Have you installed any other program successfully on this machine? -- Adrian Klaver adrian.kla...@gmail.com -- Regards, Sachin Srivastava EnterpriseDB, India -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where is diskchecker.pl ?
Hi, The PostgreSQK documentation refers to diskchecker.pl on the page http://brad.livejournal.com/2116715.html But on this page, the given link for diskchecker.pl does not exist anymore. After some unsuccessfull queries on Google to find the missing file, I wonder if one of you have a lin or a copy of this file. Thank you. -- Cordialement, Jean-Gérard Pailloncy -- 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] installation problem with postgres password
On 18 July 2012 10:33, KOTa kota.a...@gmail.com wrote: update. managed to run it via command prompt postgres_install.exe --serviceaccount postgres it did start. it still asks for a password, but because i could not create any password for this user and installation does not accept empty password, i am still stuck Perhaps you can't create more than one account because it's Windows 7 Home Edition? It sounds like the type of limitation to expect on that edition. In that case, use your own account to run Postgres as, or install a less limited OS. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Chicken/egg problem with range types
Alban Hertroys haram...@gmail.com writes: On 18 Jul 2012, at 5:08, Tom Lane wrote: I wonder whether we could improve this by postponing the no-shell-types check from creation to function runtime. I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of the transaction. You mean, after we've already crashed, or allowed a security breach to happen? Doesn't sound very helpful. In any case, my concern is that there not be any added overhead, not about moving it around. 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] installation problem with postgres password
On 07/18/2012 10:13 PM, Alban Hertroys wrote: On 18 July 2012 10:33, KOTa kota.a...@gmail.com wrote: update. managed to run it via command prompt postgres_install.exe --serviceaccount postgres it did start. it still asks for a password, but because i could not create any password for this user and installation does not accept empty password, i am still stuck Perhaps you can't create more than one account because it's Windows 7 Home Edition? It sounds like the type of limitation to expect on that edition. Maybe if it were Starter. Home typically refers to Home Premium which is certainly not so restricted. Even if it were Home Basic, that can't create homegroups, lacks Aero, and lacks media center, but it's perfectly happy with multiple users. There's something funky about this person's Windows install, it's just a matter of working out what. -- 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] Re: BUG #6742: pg_dump doesn't convert encoding of DB object names to OS encoding
Hello! May I to propose a solution and to step up? I've read a discussion of the bug #5800 and here is my 2 cents. To make things clear let me give an example. I am a PostgreSQL hosting provider and I let my customers to create any databases they wish. I have clients all over the world (so they can create databases with different encoding). The question is - what I (as admin) want to see in my postgresql log, containing errors from all the databases? IMHO we should consider two requirements for the log. First, The file should be readable with a generic text viewer. Second, It should be useful and complete as possible. Now I see following solutions. A. We have different logfiles for each database with different encodings. Then all our logs will be readable, but we have to look at them one by onе and it's inconvenient at least. Moreover, our log reader should understand what encoding to use for each file. B. We have one logfile with the operating system encoding. First downside is that the logs can be different for different OSes. The second is that Windows has non-Unicode system encoding. And such an encoding can't represent all the national characters. So at best I will get ??? in the log. C. We have one logfile with UTF-8. Pros: Log messages of all our clients can fit in it. We can use any generic editor/viewer to open it. Nothing changes for Linux (and other OSes with UTF-8 encoding). Cons: All the strings written to log file should go through some conversation function. I think that the last solution is the solution. What is your opinion? In fact the problem exists even with a simple installation on Windows when you use non-English locale. So the solution would be useful for many of us. Best regards, Alexander On 05/23/2012 09:15 AM, yi huang wrote: I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is /var/log/postgresql/postgresql-9.1-main.log) contains ??? which indicate some sort of charset encoding problem. It's a known issue, I'm afraid. The PostgreSQL postmaster logs in the system locale, and the PostgreSQL backends log in whatever encoding their database is in. They all write to the same log file, producing a log file full of mixed encoding data that'll choke many text editors. If you force your editor to re-interpret the file according to the encoding your database(s) are in, this may help. In the future it's possible that this may be fixed by logging output to different files on a per-database basis or by converting the text encoding of log messages, but no agreement has been reached on the correct approach and nobody has stepped up to implement it. -- 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
Re: [GENERAL] main log encoding problem
Hello! May I to propose a solution and to step up? I've read a discussion of the bug #5800 and here is my 2 cents. To make things clear let me give an example. I am a PostgreSQL hosting provider and I let my customers to create any databases they wish. I have clients all over the world (so they can create databases with different encoding). The question is - what I (as admin) want to see in my postgresql log, containing errors from all the databases? IMHO we should consider two requirements for the log. First, The file should be readable with a generic text viewer. Second, It should be useful and complete as possible. Now I see following solutions. A. We have different logfiles for each database with different encodings. Then all our logs will be readable, but we have to look at them one by onе and it's inconvenient at least. Moreover, our log reader should understand what encoding to use for each file. B. We have one logfile with the operating system encoding. First downside is that the logs can be different for different OSes. The second is that Windows has non-Unicode system encoding. And such an encoding can't represent all the national characters. So at best I will get ??? in the log. C. We have one logfile with UTF-8. Pros: Log messages of all our clients can fit in it. We can use any generic editor/viewer to open it. Nothing changes for Linux (and other OSes with UTF-8 encoding). Cons: All the strings written to log file should go through some conversation function. I think that the last solution is the solution. What is your opinion? In fact the problem exists even with a simple installation on Windows when you use non-English locale. So the solution would be useful for many of us. Best regards, Alexander P.S. sorry for the wrong subject in my previous message sent to pgsql-general On 05/23/2012 09:15 AM, yi huang wrote: I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is /var/log/postgresql/postgresql-9.1-main.log) contains ??? which indicate some sort of charset encoding problem. It's a known issue, I'm afraid. The PostgreSQL postmaster logs in the system locale, and the PostgreSQL backends log in whatever encoding their database is in. They all write to the same log file, producing a log file full of mixed encoding data that'll choke many text editors. If you force your editor to re-interpret the file according to the encoding your database(s) are in, this may help. In the future it's possible that this may be fixed by logging output to different files on a per-database basis or by converting the text encoding of log messages, but no agreement has been reached on the correct approach and nobody has stepped up to implement it. -- 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
Re: [GENERAL] Where is diskchecker.pl ?
On Wednesday 18 July 2012 16:11:21 jg wrote: Hi, The PostgreSQK documentation refers to diskchecker.pl on the page http://brad.livejournal.com/2116715.html But on this page, the given link for diskchecker.pl does not exist anymore. After some unsuccessfull queries on Google to find the missing file, I wonder if one of you have a lin or a copy of this file. Thank you. -- Cordialement, Jean-Gérard Pailloncy -- Gosh, can't find it either =-( You can find me on twitter @iCodeiExist -- 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] Chicken/egg problem with range types
On 07/17/2012 08:08 PM, Tom Lane wrote: I wonder whether we could improve this by postponing the no-shell-types check from creation to function runtime. It would be annoying to have to make an additional catalog lookup at runtime just for typisdefined, but I think that probably we could fold it in with an existing fetch of the pg_type row during parsing of the calling query, so that no run-time overhead is added. This would limit what checking could be performed on the function body at creation time, but surely no worse than, say, a reference to a nonexistent table, which we allow. How about using ALTER TYPE to set it after both the function and the type have been created? Scott -- 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] Chicken/egg problem with range types
On 18 Jul 2012, at 16:15, Tom Lane wrote: Alban Hertroys haram...@gmail.com writes: On 18 Jul 2012, at 5:08, Tom Lane wrote: I wonder whether we could improve this by postponing the no-shell-types check from creation to function runtime. I don't suppose it'd be possible to treat it as a deferred constraint? Then the check would be moved to the end of the transaction. You mean, after we've already crashed, or allowed a security breach to happen? Doesn't sound very helpful. In any case, my concern is that there not be any added overhead, not about moving it around. I did mean the transaction in which the dependency got created, not some later transaction in which it gets used. I'm fairly sure that'd be before any crashes or security breaches. Or is that not what you're implying? Basically: BEGIN; CREATE TYPE dt_range AS (..., CANONICAL = dt_range_canonical); -- check for CANONICAL function deferred CREATE FUNCTION dt_range_canonical(dt_range) ...; -- now it exists COMMIT; -- here the check for the CANONICAL function is performed I did make an assumption in there that the dependency doesn't get used before the creating transaction committed. Using the dependency in the same transaction that created it should probably not be allowed, or the check should be moved to function runtime if that happens (like in your original suggestion). Another alternative, which would probably require a major effort to implement, would be to make CREATE FUNCTION inlinable (is that the right word?) by making the DDL statement return the function identifier of the function it just created. With that, the dependency of the OP would be created somewhat like: CREATE TYPE dt_range AS (..., CANONICAL = (CREATE FUNCTION dt_range_canonical(dt_range) ...)); Some languages use similar constructs, for example to assign methods to object prototypes in the case of Javascript. I know, SQL is not object oriented (but neither is Javascript, strictly speaking). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trouble with NEW
Hi I would appreciate some fresh eyes on this expression - update p_id.fluids set fluid_short = (select shape.text from shape, num_search where (select st_within(shape.wkb_geometry, st_geometryn(num_search.the_geom4, 1)) = 'true') and text !~ '[0-9]') where p_id.fluids.fluid_id = NEW.fluid_id; I receive an error ”record “new” has no field “fluid_id” “. Bob
Re: [GENERAL] Trouble with NEW
On 07/18/2012 12:07 PM, Bob Pawley wrote: Hi I would appreciate some fresh eyes on this expression - update p_id.fluids set fluid_short = (select shape.text from shape, num_search where (select st_within(shape.wkb_geometry, st_geometryn(num_search.the_geom4, 1)) = 'true') and text !~ '[0-9]') where p_id.fluids.fluid_id = NEW.fluid_id; I receive an error ”record “new” has no field “fluid_id” “. Bob Are you in a trigger? -- 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] Trouble with NEW
On Wednesday, July 18, 2012 11:07:34 AM Bob Pawley wrote: Hi I would appreciate some fresh eyes on this expression - update p_id.fluids set fluid_short = (select shape.text from shape, num_search where (select st_within(shape.wkb_geometry, st_geometryn(num_search.the_geom4, 1)) = 'true') and text !~ '[0-9]') where p_id.fluids.fluid_id = NEW.fluid_id; I receive an error ”record “new” has no field “fluid_id” “. Is that run within an insert or update trigger function? Does the table it's on have a field named fluid_id? Bob -- When the Athenians finally wanted not to give to society but for society to give to them, when the freedom they wished for most was freedom from responsibility, then Athens ceased to be free and was never free again.” -- Edward Gibbon -- 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] Trouble with NEW
It's an insert after trigger function. The table has a column named fluid_id. Bob -Original Message- From: Alan Hodgson Sent: Wednesday, July 18, 2012 11:15 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with NEW On Wednesday, July 18, 2012 11:07:34 AM Bob Pawley wrote: Hi I would appreciate some fresh eyes on this expression - update p_id.fluids set fluid_short = (select shape.text from shape, num_search where (select st_within(shape.wkb_geometry, st_geometryn(num_search.the_geom4, 1)) = 'true') and text !~ '[0-9]') where p_id.fluids.fluid_id = NEW.fluid_id; I receive an error ”record “new” has no field “fluid_id” “. Is that run within an insert or update trigger function? Does the table it's on have a field named fluid_id? Bob -- When the Athenians finally wanted not to give to society but for society to give to them, when the freedom they wished for most was freedom from responsibility, then Athens ceased to be free and was never free again.” -- Edward Gibbon -- 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] Trouble with NEW
On 07/18/2012 12:28 PM, Bob Pawley wrote: It's an insert after trigger function. The table has a column named fluid_id. Can we see the table schema. What I am looking for is quoted column name that would preserve case. Bob -- Adrian Klaver adrian.kla...@gmail.com -- 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 with dblink and drop owned by
Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink' from the contrib package (also 8.4.12-1) and am now running into a strange problem when I run drop owned by. First, as the superuser I run 'dblink.sql' against the database I'll be using. Then I connect and do the following (this is against a new empty database 'foo'): foo=# create user somelocaluser with password 'somelocaluser'; CREATE ROLE foo=# create foreign data wrapper postgresql validator postgresql_fdw_validator; CREATE FOREIGN DATA WRAPPER foo=# grant usage on foreign data wrapper postgresql to somelocaluser; GRANT foo=# grant execute on function dblink_connect(text,text) to somelocaluser; GRANT foo=# \c foo somelocaluser Password for user somelocaluser: psql (8.4.12) You are now connected to database foo as user somelocaluser. foo= drop owned by somelocaluser; WARNING: no privileges could be revoked for dblink_connect ERROR: unexpected object type 2328 Dropping the foreign data wrapper as the superuser does work, but is highly inconvenient because I need to be able to run the drop owned by as a non-privileged user as part of an integration testing procedure. If I don't create the foreign data wrapper, I can do drop owned by somelocaluser while connected as the non-privileged user. James -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Some feedback on range types
I'm testing range types and I've come up with a couple of curiosities. 1) I'll start off easy. In the wild, discrete ranges tend to be closed-closed [] while continuous ranges tend to be closed-open [). For instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed [2009-06-01, 2012-04-15] or Sally lived [1934, 2001]. But these ranges are all converted to [). So Sally's tombstone ends up reading [1934-2002). Not a huge deal, but it is difficult for users to change this behavior. 2) Typemod doesn't work for subtypes. So say I'm working on a stock-trading app and I want to create a numeric range with a base type of numeric(8,2) and a granularity of 0.01. CREATE TYPE num_range AS RANGE (SUBTYPE = numeric(8,2)); SELECT num_range(0.2, 2/3.0); -- [0.2,0.6667) 3) Continuing with the above example, I make a canonical function then hack it in to the system catalog to temporarily get around the chicken/egg problem mentioned earlier. CREATE OR REPLACE FUNCTION num_range_canonical(num_range) RETURNS num_range AS $$ SELECT num_range( (CASE WHEN lower_inc($1) THEN lower($1) ELSE lower($1) + 0.01 END)::numeric(8,2), (CASE WHEN upper_inc($1) THEN upper($1) ELSE upper($1) - 0.01 END)::numeric(8,2), '[]'); $$ LANGUAGE 'sql' IMMUTABLE STRICT; However, the built in range types are automatically canonicalized while a user created one is not, even with the canonical function set on the type. Not a huge problem, but not an expected behavior either. 4) No editing in place. This is a problem when trying to create functions that will work with anyrange. Some missing functionality was the ability to do set difference when the first range extends on both sides of the second. The function range_minus throws an exception in that situation. So I set about to add the functions range_ldiff and range_rdiff to pull out the left or right piece in this situation. Because users can add any number of range types it would be very to create a new instance of the correct type. It would be much easier to just edit the upper or lower bounds of one of the input parameters. But that doesn't seem to be supported. -- 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] Trouble with NEW
On Wednesday, July 18, 2012 12:28:00 PM Bob Pawley wrote: It's an insert after trigger function. The table has a column named fluid_id. Bob Could you post the whole function? And a \d on the table? -- 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] problem with dblink and drop owned by
James W. Wilson jww1...@gmail.com writes: Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink' from the contrib package (also 8.4.12-1) and am now running into a strange problem when I run drop owned by. You're running into a old bug, which is that DROP OWNED BY doesn't know what to do with foreign data wrappers. According to the commit logs, this was fixed a couple of years ago in 9.0 and up, but we did not bother to fix 8.4 because foreign data wrappers aren't actually useful for anything in 8.4. If you want to work with FDWs, I'd suggest moving forward to a release where they have some real functionality ... 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] Trouble with NEW
When I substitute new.fluid_id for the actual fluid)id the expression returns the right value. Following is the table - CREATE TABLE p_id.fluids ( p_id_id integer, fluid_id serial, text_arrow geometry, line geometry, ip_op_equipment character varying(3), pipe_number character varying(100), pipe_size character varying, pipe_size_unit varchar (30), pipe_schedule varchar (30), dest_process varchar (30), dest_pump varchar (30), dest_pid varchar (30), source_process varchar (30), source_pump varchar (30), source_pid varchar (30), fluid_short character varying (10), fluid_name character varying(100), pump1 character varying(5), pump2 character varying(5), CONSTRAINT fluid_pk PRIMARY KEY (fluid_id) ); Bob -Original Message- From: Adrian Klaver Sent: Wednesday, July 18, 2012 1:07 PM To: Bob Pawley Cc: Alan Hodgson ; pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with NEW On 07/18/2012 12:28 PM, Bob Pawley wrote: It's an insert after trigger function. The table has a column named fluid_id. Can we see the table schema. What I am looking for is quoted column name that would preserve case. Bob -- Adrian Klaver adrian.kla...@gmail.com -- 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] [BUGS] main log encoding problem
C. We have one logfile with UTF-8. Pros: Log messages of all our clients can fit in it. We can use any generic editor/viewer to open it. Nothing changes for Linux (and other OSes with UTF-8 encoding). Cons: All the strings written to log file should go through some conversation function. I think that the last solution is the solution. What is your opinion? I am thinking about variant of C. Problem with C is, converting from other encoding to UTF-8 is not cheap because it requires huge conversion tables. This may be a serious problem with busy server. Also it is possible some information is lossed while in this conversion. This is because there's no gualntee that there is one-to-one-mapping between UTF-8 and other encodings. Other problem with UTF-8 is, you have to choose *one* locale when using your editor. This may or may not affect handling of string in your editor. My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] problem with dblink and drop owned by
I'm confused. I thought foreign data wrappers were required to create database links from one Postgresql server to another. Is there some way to create a database link without using them? I was working off of this example: http://www.postgresql.org/docs/8.4/static/contrib-dblink-connect.html I'm stuck with 8.4 for now, unfortunately. James On Wed, Jul 18, 2012 at 6:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: James W. Wilson jww1...@gmail.com writes: Hi, I'm using Postgresql 8.4.12-1 on Debian. I've installed 'dblink' from the contrib package (also 8.4.12-1) and am now running into a strange problem when I run drop owned by. You're running into a old bug, which is that DROP OWNED BY doesn't know what to do with foreign data wrappers. According to the commit logs, this was fixed a couple of years ago in 9.0 and up, but we did not bother to fix 8.4 because foreign data wrappers aren't actually useful for anything in 8.4. If you want to work with FDWs, I'd suggest moving forward to a release where they have some real functionality ... 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
[GENERAL] data from the table is getting dropped when I am restarting my application after making changes in the objects created in my application in play
I am creating one application using play in the models I had created a table and everything is working fine except when i make changes in any of the object(fields) all the previous data in the database is getting dropped how to recover that -- 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] Problem running ALTER TABLE..., ALTER TABLE waiting
Hi Raghu, I don't get any rows returned back from that query. I'm running it while connected to the DB in question. Am I supposed to substitute values for any of the variables in the query? -- Brian McNally On 07/17/2012 07:23 PM, raghu ram wrote: On Wed, Jul 18, 2012 at 1:24 AM, Brian McNally bmcna...@uw.edu mailto:bmcna...@uw.edu wrote: Hello, I'm running Postgres 9.0.2 on RHEL 5.6 x86_64 and trying to run the following statement: alter table samples add column in_esp1234 text; When I do this the command just hangs. ps output indicates that it's waiting: postgres: postgres exomeSNP [local] ALTER TABLE waiting When I look to see what might have that table locked I don't see anything: select * from pg_locks where relation=(select oid from pg_class where relname='samples'); I have a nearly identical server where this command works. It seems like something has this table locked, but I haven't been able to find it. Any ideas? Please find more information using below query: \x SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.current_query AS waiting_query, waiting.mode AS waiting_mode, waiting.pidAS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.current_queryAS other_query, other.mode AS other_mode, other.pid AS other_pid, other.granted AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.procpid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting.database = other.database AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid ) JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid ) WHERE NOT waiting.granted AND waiting.pid other.pid; -- Thanks Regards, Raghu Ram EnterpriseDB Corporation Blog:http://raghurc.blogspot.in/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Segmentation fault
Server stopped due to Segmentation Fault. Server was running successfully for an year. PostgreSQL: 9.0.3 from /var/log/messages Jul 18 19:00:03 ip-10-136-22-193 kernel: [18643442.660032] postgres[6818]: segfault at 170a8c6f ip 0044c94d sp 7fff9fee5b80 error 4 in postgres[40+495000] from pg log LOG: server process (PID 6818) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes Please suggest if there is a way to find out the issue. Suggestions to avoid. Regards Amod
Re: [GENERAL] installation problem with postgres password
thanks! that solved a problem. i used my own account. but it does not seem to be a limitation in number of accounts. because i can create new account, but without password. the problem comes out when i try to add password to it. On Wed, Jul 18, 2012 at 5:13 PM, Alban Hertroys haram...@gmail.com wrote: On 18 July 2012 10:33, KOTa kota.a...@gmail.com wrote: update. managed to run it via command prompt postgres_install.exe --serviceaccount postgres it did start. it still asks for a password, but because i could not create any password for this user and installation does not accept empty password, i am still stuck Perhaps you can't create more than one account because it's Windows 7 Home Edition? It sounds like the type of limitation to expect on that edition. In that case, use your own account to run Postgres as, or install a less limited OS. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] data from the table is getting dropped when I am restarting my application after making changes in the objects created in my application in play
On 07/18/2012 12:19 PM, rajonr...@gmail.com wrote: I am creating one application using play in the models I had created a table and everything is working fine except when i make changes in any of the object(fields) all the previous data in the database is getting dropped how to recover that This is going to need a lot more information to get an answer. In the meantime I am going to assume when you say play you mean: http://www.playframework.org/ My guess is you will find an answer sooner using the mailing list for the above: https://groups.google.com/forum/#!forum/play-framework -- Adrian Klaver adrian.kla...@gmail.com -- 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] [BUGS] main log encoding problem
Tatsuo Ishii is...@postgresql.org writes: My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. Um ... but ... (1) nothing whatsoever can read MULE, except emacs and xemacs. (2) there is more than one version of MULE (emacs versus xemacs, not to mention any possible cross-version discrepancies). (3) from a log volume standpoint, this could be pretty disastrous. I'm not for a write-only solution, which is pretty much what this would be. 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] [BUGS] main log encoding problem
Tatsuo Ishii is...@postgresql.org writes: My idea is using mule-internal encoding for the log file instead of UTF-8. There are several advantages: 1) Converion to mule-internal encoding is cheap because no conversion table is required. Also no information loss happens in this conversion. 2) Mule-internal encoding can be handled by emacs, one of the most popular editors in the world. 3) No need to worry about locale. Mule-internal encoding has enough information about language. Um ... but ... (1) nothing whatsoever can read MULE, except emacs and xemacs. (2) there is more than one version of MULE (emacs versus xemacs, not to mention any possible cross-version discrepancies). (3) from a log volume standpoint, this could be pretty disastrous. I'm not for a write-only solution, which is pretty much what this would be. I'm not sure how long xemacs will survive (the last stable release of xemacs was released in 2009). Anyway, I'm not too worried about your points, since it's easy to convert back from mule-internal code encoded log files to original encoding mixed log file. No information will be lost. Even converting to UTF-8 should be possible. My point is, once the log file is converted to UTF-8, there's no way to convert back to original encoding log file. Probably we treat mule-internal encoded log files as an internal format, and have a utility which does conversion from mule-internal to UTF-8. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] problem with dblink and drop owned by
James W. Wilson jww1...@gmail.com writes: I'm confused. I thought foreign data wrappers were required to create database links from one Postgresql server to another. contrib/dblink doesn't require them. It does have an option to use an FDW instead of a libpq connection string to specify the connection target. I had forgotten that that option got added in 8.4, before we had any other working use for FDWs --- so maybe we should rethink the decision not to backport this fix? Author: Heikki Linnakangas heikki.linnakan...@iki.fi Branch: master Release: REL9_1_BR [e356743f3] 2010-11-12 15:29:23 +0200 Branch: REL9_0_STABLE Release: REL9_0_2 [533073cf2] 2010-11-12 15:30:19 +0200 Add missing support for removing foreign data wrapper / server privileges belonging to a user at DROP OWNED BY. Foreign data wrappers and servers don't do anything useful yet, which is why no-one has noticed, but since we have them, seems prudent to fix this. Per report from Chetan Suttraway. Backpatch to 9.0, 8.4 has the same problem but this patch didn't apply there so I'm not going to bother. In the meantime, though, you do not *need* an FDW. dblink worked fine for many releases before FDWs existed, and it still does work without 'em. 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
[GENERAL] Odd corruption issue reported on dba.stackexchange.com, need advice
Hi all I've been following a strange issue on dba.stackexchange.com and I could use opinions from those more clueful than I on the log files posted there. http://dba.stackexchange.com/questions/20959/recover-postgresql-database-from-wal-errors-on-startup/20961#comment34356_20961 The short version is that the person did a bulk-load of some PostGIS data using the osm2pgsql data-loader tool ( http://wiki.openstreetmap.org/wiki/Osm2pgsql) to populate a Pg 9.1 database. This appeared to succeed, but when the server was restarted it failed to come up, complaining that WAL contains references to invalid pages and page 1493172 of relation base/16385/477861 was uninitialized (for many different pages). The logs of the shutdown suggest that a backend probably crashed, but that shouldn't cause the WAL and heap corruption observed by the OP. It's interesting to observe the presence of both a fast and an immediate shutdown request in the log. A trimmed log follows, original linked to in the article above: LOG: received fast shutdown request LOG: aborting any active transactions LOG: received immediate shutdown request WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: could not send data to client: Broken pipe WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: could not send data to client: Broken pipe [Note: The posted log of the shutdown ends here. The poster asserts that this is the complete log and that the following statement is the next one in their log. Very strange.] LOG: database system was interrupted; last known up at 2012-07-13 00:15:20 UTC LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 4D/A25FE548 LOG: unexpected pageaddr 4D/6F5C6000 in log file 77, segment 196, offset 6053888 LOG: redo done at 4D/C45C5278 WARNING: page 1493172 of relation base/16385/477861 was uninitialized WARNING: page 2247965 of relation base/16385/477861 was uninitialized WARNING: page 1493172 of relation base/16385/477861 was uninitialized PANIC: WAL contains references to invalid pages LOG: startup process (PID 21574) was terminated by signal 6: Aborted LOG: aborting startup due to startup process failure -- Craig Ringer
Re: [GENERAL] Segmentation fault
On 07/19/2012 12:37 AM, Amod Pandey wrote: Server stopped due to Segmentation Fault. Server was running successfully for an year. PostgreSQL: 9.0.3 from /var/log/messages Jul 18 19:00:03 ip-10-136-22-193 kernel: [18643442.660032] postgres[6818]: segfault at 170a8c6f ip 0044c94d sp 7fff9fee5b80 error 4 in postgres[40+495000] from pg log LOG: server process (PID 6818) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes Please suggest if there is a way to find out the issue. Did the crash produce a core file ? You haven't mentioned what Linux distro or kernel version you're on, and defaults vary. Look in your PostgreSQL datadir and see if there are any files with core in the name. Unfortunately most Linux distros default to not producing core files. Without a core file it'll be nearly impossible because the segfault message reported by the kernel only contains the instruction pointer and stack pointer. The stack pointer is invalid and useless without a core file, and with address space layout randomisation active the instruction pointer offsets are all randomised for each execution, so the ip doesn't tell you much on ASLR systems either. If you can show more of the PostgreSQL logs from around the incident that would possibly be helpful. -- Craig Ringer
Re: [GENERAL] data from the table is getting dropped when I am restarting my application after making changes in the objects created in my application in play
On 07/19/2012 03:19 AM, rajonr...@gmail.com wrote: I am creating one application using play in the models I had created a table and everything is working fine except when i make changes in any of the object(fields) all the previous data in the database is getting dropped how to recover that You're better off asking the Play! framework people. If you want to investigate it from the database side, set log_statement='all' in postgresql.conf, reload or restart PostgreSQL, and run your test. Now examine the logs and trace what the framework did. Check to see if: - It ever connects to PostgreSQL at all. You might be using an in-memory h2 or derby instance and just think you're using PostgreSQL - It ever inserts any data - It ever commits its transactions If you confirm that it's getting its data into PostgreSQL, you then need to look and see if it's deleting it again by checking the log for DELETE and TRUNCATE statements. It could also be re-creating your tables every time it runs. Check for CREATE TABLE statements. -- 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
Re: [GENERAL] main log encoding problem
On 07/18/2012 11:16 PM, Alexander Law wrote: Hello! May I to propose a solution and to step up? I've read a discussion of the bug #5800 and here is my 2 cents. To make things clear let me give an example. I am a PostgreSQL hosting provider and I let my customers to create any databases they wish. I have clients all over the world (so they can create databases with different encoding). The question is - what I (as admin) want to see in my postgresql log, containing errors from all the databases? IMHO we should consider two requirements for the log. First, The file should be readable with a generic text viewer. Second, It should be useful and complete as possible. Now I see following solutions. A. We have different logfiles for each database with different encodings. Then all our logs will be readable, but we have to look at them one by onе and it's inconvenient at least. Moreover, our log reader should understand what encoding to use for each file. B. We have one logfile with the operating system encoding. First downside is that the logs can be different for different OSes. The second is that Windows has non-Unicode system encoding. And such an encoding can't represent all the national characters. So at best I will get ??? in the log. C. We have one logfile with UTF-8. Pros: Log messages of all our clients can fit in it. We can use any generic editor/viewer to open it. Nothing changes for Linux (and other OSes with UTF-8 encoding). Cons: All the strings written to log file should go through some conversation function. I think that the last solution is the solution. What is your opinion? Implementing any of these isn't trivial - especially making sure messages emitted to stderr from things like segfaults and dynamic linker messages are always correct. Ensuring that the logging collector knows when setlocale() has been called to change the encoding and translation of system messages, handling the different logging output methods, etc - it's going to be fiddly. I have some performance concerns about the transcoding required for (b) or (c), but realistically it's already the norm to convert all the data sent to and from clients. Conversion for logging should not be a significant additional burden. Conversion can be short-circuited out when source and destination encodings are the same for the common case of logging in utf-8 or to a dedicated file. I suspect the eventual choice will be all of the above: - Default to (b) or (c), both have pros and cons. I favour (c) with a UTF-8 BOM to warn editors, but (b) is nice for people whose DBs are all in the system locale. - Allow (a) for people who have many different DBs in many different encodings, do high volume logging, and want to avoid conversion overhead. Let them deal with the mess, just provide an additional % code for the encoding so they can name their per-DB log files to indicate the encoding. The main issue is just that code needs to be prototyped, cleaned up, and submitted. So far nobody's cared enough to design it, build it, and get it through patch review. I've just foolishly volunteered myself to work on an automated crash-test system for virtual plug-pull testing, so I'm not stepping up. -- 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
