Re: [GENERAL]
On Thu, Nov 10, 2011 at 1:24 PM, daflmx wrote: > Hello,all. > I have installed the postgresql . > $/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > LOG:database system was shut down at 2011-11-10 15:36:14 CST > LOG:database system is ready to accept connections > LOG:autovacuum launcher started > To start/stop use pg_ctl utility which has good options. Eg:- $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop http://www.postgresql.org/docs/9.1/static/app-pg-ctl.html but when I want to connect to the server at another terminal > $/usr/local/pgsql/bin/createdb mydb > createdb:could nto connect to database postgres:could not connect to > server:No such file or directory > Is the server running locally and accepting connections on Unix domain > socket"/var/run/postgresql/.s.PGSQL.5432"? > I don't think you are trying to connect to the database, however you are creating the database in a cluster. For connection you need to use below command:- Eg:- $/usr/local/pgsql/bin/psql -U postgres -p 5432 -d postgres http://www.postgresql.org/docs/9.1/static/app-psql.html $/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > FATAL:lock file "postmaster.pid"already exists > HINT: Is another postmaster(PID 1950)running in data directory > "/usr/local/pgsql/data"? > This tell you cluster is already up and running and ready to accept connections. You can know the status of cluster whether running or not by below command. Eg:- $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] count (DISTINCT field) OVER ()
Tarlika Elisabeth Schmitz, 10.11.2011 00:52: I would like to implement the equivalent of "count (DISTINCT field) OVER ()": SELECT id, name, similarity(name, 'Tooneyvara') as delta, count (id) OVER() AS cnt FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.1 ORDER BY delta DESC produces result: 1787Toomyvara 0.5 4 1787Toomevara 0.4 4 1700Ardcroney 0.105 4 1788Townsfield 0.1 4 What I would like is a "3" in the cnt column (ignoring id duplicates). This should do it: SELECT id, name, delta, sum(case when rn = 1 then rn else null end) over() as distinct_id_count FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, row_number() OVER(partition by id) AS rn FROM vtown WHERE similarity(name, 'Tooneyvara') > 0.1 ) t ORDER BY delta DESC -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
Hello,all. I have installed the postgresql . $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start LOG:database system was shut down at 2011-11-10 15:36:14 CST LOG:database system is ready to accept connections LOG:autovacuum launcher started when I check the status of cluster $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status pg_ctl:server is running(PID 18432) but why I cannot create a database successfully? $/usr/local/pgsql/bin/createdb mydb createdb:could nto connect to database postgres:could not connect to server:No such file or directory Is the server running locally and accepting connections on Unix domain socket"/var/run/postgresql/.s.PGSQL.5432"? Thanks.
Re: [GENERAL] count (DISTINCT field) OVER ()
On Thu, 10 Nov 2011 10:02:36 +0100 Thomas Kellerer wrote: >Tarlika Elisabeth Schmitz, 10.11.2011 00:52: >> I would like to implement the equivalent of "count (DISTINCT id) >> OVER ()": >> >>[...] >> >> produces result: >> id, name, delta, cnt >> 1787 Toomyvara 0.5 4 >> 1787 Toomevara 0.4 4 >> 1700 Ardcroney 0.105 4 >> 1788 Townsfield 0.1 4 >> > >This should do it: > >SELECT id, >name, >delta, >sum(case when rn = 1 then rn else null end) over() as > distinct_id_count >FROM ( > SELECT > id, name, similarity(name, 'Tooneyvara') as delta, > row_number() OVER(partition by id) AS rn > FROM vtown > WHERE > similarity(name, 'Tooneyvara') > 0.1 >) t >ORDER BY delta DESC > I like you suggestion, Thomas. It is not that dissimilar from but cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id) attempt. It's also very slightly faster. Here's another, slightly shorter, variation of your suggestion: SELECT id, name, delta, max(rank) OVER() as cnt FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, dense_rank() OVER(ORDER BY id) AS rank FROM vtown WHERE similarity(name, 'Tooneyvara') > 0.1 ) t ORDER BY delta DESC -- 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] count (DISTINCT field) OVER ()
Tarlika Elisabeth Schmitz, 10.11.2011 11:24: SELECT id, name, delta, sum(case when rn = 1 then rn else null end) over() as distinct_id_count FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, row_number() OVER(partition by id) AS rn FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.1 ) t ORDER BY delta DESC I like you suggestion, Thomas. It is not that dissimilar from but cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id) attempt. It's also very slightly faster. Here's another, slightly shorter, variation of your suggestion: SELECT id, name, delta, max(rank) OVER() as cnt FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, dense_rank() OVER(ORDER BY id) AS rank FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.1 ) t ORDER BY delta DESC Nice trick with the dense_rank(), never thought of that. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Exp/Imp data with blobs
Hi, In one db that I have, there are a few columns that are blob datatype. This db has around 200MB of data today and as it a development db yet, so I am replicating data to another db for testing purposes using pg_dump and pg_restore. To export the data it is pretty fast, about 3~4 minutes, that means acceptable. However, when I import this data to another db (even on the same machine) it takes around 4 hours to perform the pg_restore. I see during the process that spend most of the time importing the blob records. So I stopped to think if I need to adjust this db with different parameters, or if this behavior is already expected when working with blobs. Does anyone have suggestions how can I tune this process?? Here is the basic info about my env. Windows 32; Postgres 9.1; shared_buffers = 256M maintenance_work_mem = 32M Any other question or doubt, please let me know. Thank you in advance. Alex -- 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] Foreign Keys and Deadlocks
Hi David, On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: > So, aside from removing the PKs do i have any other options? Sure you have: order the inserts by primary key inside each transaction. Then you will not get deadlocks, but inserting the same key again will fail of course (but that's the purpose of the primary key, right ?) Ordering inserts/updates by the columns which cause locks is the first thing to do to avoid dead-locks... Cheers, Csaba. -- 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 list installed pl languages
Is there a way to list the installed pl languages for a database and/or server cluster? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 9.0.5 RPM's for SLES - Where to get !?
I'm desperately trying to get a hold of the latest RPM's for PostgreSQL 9.0..5 for SLES 11 SP1 x86_64 ... I simply can not find these anywhere !! It seems that the good folk over at software.opensuse.org are only compiling 9.1.x now. Rather annoying to say the least for those of us who don't want to upgrade data format to keep up with bug fixes. Anyone have ideas where these can be found / built !?
[GENERAL] Learning to rephrase equivalent queries?
Sometimes the planner can't find the most efficient way to execute your query. Thanks to relational algebra, there may be other, logically equivalent queries that it DOES know how to optimize. But I don't know relational algebra. yet. (Date/Codd is a sleeping pill.) I need more experience first. Are there blogs, guides, rules of thumb, common refactoring patterns out there somewhere? I'm looking for a list of basic equalities, the SQL equivalent of: a^2 - b^2 = (a + b)(a - b) Such as: SELECT l.* FROMt_left l LEFT JOIN t_right r ON r.value = l.value WHERE r.value IS NULL = SELECT l.* FROMt_left l WHERE NOT EXISTS ( SELECT NULL FROMt_right r WHERE r.value = l.value ) All my searches for "SQL Refactoring" seem to lead to either (a) discussions about how many characters an alias should be and how you should indent things, or (b) tutorials on normalization. This isn't that. I want to learn ways to restate my queries. Any tips? -- 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 list installed pl languages
On Wed, Nov 09, 2011 at 10:58:01PM -0600, Dennis Ryan wrote: > Is there a way to list the installed pl languages for a database > and/or server cluster? \dL in psql or select * from pg_language; Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
I am thinking there is a better/simpler way, though this is what I have
working:
(postgres 9.1)
I would like to have the list of colors for each type of clothing to be
comma seperated in the end result.
like this:
typeorganized_by_type
pants red, blue, orange
shirt black, gray
though with my current solution it looks like this:
typeorganized_by_type
pants , red, , blue, ,orange,
shirt , black, ,gray,
I know I can add more logic in to get rid of the leading and ending commas,
etc, but it seem like there would be a cleaner more elegant solution.
table
-
-- Table: clothes
-- DROP TABLE clothes;
CREATE TABLE clothes
(
type character varying,
color character varying
)
WITH (
OIDS=FALSE
);
ALTER TABLE clothes
OWNER TO postgres;
insert into clothes values('shirt','red');
insert into clothes values('shirt','blue');
insert into clothes values('shirt','orange');
insert into clothes values('pants','black');
insert into clothes values('pants','gray');
create or replace function organized_by_type(input text) returns text
language plpgsql as $$
DECLARE
item alias for $1;
t text;
groups text;
r integer;
BEGIN
groups = '';
select into r count(color) from clothes where type = item;
for i in 1..r loop
select into t
color
from
clothes
where
type = item
limit 1 offset i-1;
groups = groups || ', ' || t || ', ';
RAISE NOTICE 'value groups: %value t: %',groups,t;
end loop;
return groups;
END
$$
Query with result
--
select
type,
organized_by_type(type)
from
clothes
group by type
typeorganized_by_type
pants red, blue, orange
shirt black, gray
Re: [GENERAL] How to list installed pl languages
On Wednesday, November 09, 2011 8:58:01 pm Dennis Ryan wrote: > Is there a way to list the installed pl languages for a database and/or > server cluster? aklaver@tucker:~$ createlang -l -U postgres test Procedural Languages Name| Trusted? ---+-- plpgsql | yes plpythonu | no > > thanks -- 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] Returning a row from a function with an appended array field
On 10 November 2011 02:54, Wes Cravens wrote: > On 11/9/2011 7:34 PM, David Johnston wrote: >> Use "WITH RECURSIVE" instead of a function. >> > > I apologize but I don't know how that would work. An example would help. There are fine examples in the documentation for the SELECT statement. -- 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] How to list installed pl languages
On Thursday 10 November 2011 05:58:01 Dennis Ryan wrote: > Is there a way to list the installed pl languages for a database and/or > server cluster? they are registered in the pg_language system catalog. Rgds, Jens -- 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] dll files missing in postgrsql bin folder in Windows
On 10 November 2011 08:56, Kalai R wrote: > please suggest, what are the configurations should I do in postgres to avoid > these problem. > Thank You None, it's not a Postgres problem. Most likely it is a problem with your Windows installation. You have files disappearing, something is doing that and it's not Postgres. Fix that and then reinstall Postgres and everything should be fine. -- 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
[GENERAL] Re: plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
Henry Drexler, 10.11.2011 14:22: I am thinking there is a better/simpler way, though this is what I have working: (postgres 9.1) I would like to have the list of colors for each type of clothing to be comma seperated in the end result. like this: typeorganized_by_type pants red, blue, orange shirt black, gray CREATE TABLE clothes ( type character varying, color character varying ) SELECT type, string_agg(color, ',') as organized_by_type FROM clothes GROUP BY type; -- 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] Re: plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
On Thu, Nov 10, 2011 at 8:34 AM, Thomas Kellerer wrote: > >> > SELECT type, > string_agg(color, ',') as organized_by_type > FROM clothes > GROUP BY type; > > > wow, yes that is cleaner. Thank you for taking the time - obviously I need to read through the string functions again.
Re: [GENERAL] plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
On Nov 10, 2011, at 8:22, Henry Drexler wrote: > I am thinking there is a better/simpler way, though this is what I have > working: > > (postgres 9.1) > > > I would like to have the list of colors for each type of clothing to be comma > seperated in the end result. > > like this: > > typeorganized_by_type > pants red, blue, orange > shirt black, gray > > > Use the STRING_AGG aggregate function instead of writing your own. 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]
"=?ISO-8859-1?B?ZGFmbG14?=" writes: > [ server is running but ] > $/usr/local/pgsql/bin/createdb mydb > createdb:could nto connect to database postgres:could not connect to > server:No such file or directory > Is the server running locally and accepting connections on Unix domain > socket"/var/run/postgresql/.s.PGSQL.5432"? That last line shows that psql (or more specifically, the libpq.so shared library) thinks it should connect to a local socket file at /var/run/postgresql/.s.PGSQL.5432. However, the common location for Postgres' local socket file is /tmp/.s.PGSQL.5432. I suspect if you look in /tmp, you'll find that the server did create a socket file there. In short: this problem occurs because you have a postmaster built one way and a client library built for a different convention. You could force things with the -h switch to psql, but it would be more convenient to be using postmaster and client library from the same distribution. 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] Fedora 16 note...
I upgraded to Fedora 16 yesterday… I thought I might have lost my 12 year old db when the system came up and I noticed the 9.1 had overwrote the old binaries. Then I read about pg_upgrade stuff and it worked! I found that postgresql would not start at boot time until I did: systemctl enable postgresql.service Jerry -- 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 inquiry a nest result?
--*Test SQL* CREATE TABLE tb(id integer primary key, name varchar(32), parent integer); INSERT INTO tb VALUES(1, 'iPhone',NULL); INSERT INTO tb VALUES(2, 'HTC', NULL); INSERT INTO tb VALUES(3, 'Motorola', NULL); INSERT INTO tb VALUES(4, 'iPhone3GS', 1); INSERT INTO tb VALUES(5, 'G7',2); INSERT INTO tb VALUES(6, 'G8',2); INSERT INTO tb VALUES(7, 'iPhone4', 1); INSERT INTO tb VALUES(8, 'iPhone4-white', 7); INSERT INTO tb VALUES(9, 'iPhone4-black', 7); INSERT INTO tb VALUES(10,'G7-A', 5); INSERT INTO tb VALUES(11,'G7-B', 5); *How to create a SQL to inquiry the result like this:* id name 1iPhone 4iPhone3GS 7iPhone4S 8iPhone4S-white 9iPhone4S-black 2HTC 5G7 10G7-A 11G7-B 3Motorola Thank you very much! -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-inquiry-a-nest-result-tp4981259p4981259.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to list installed pl languages
Thanks the select * from pg_language works and is exactly what I was looking for. \dL in psql does not in my version, 9.0.4.14, however \dL did work on another install of version 9.1.1. Thanks for the help. -Original Message- From: dep...@depesz.com [mailto:dep...@depesz.com] Sent: Thursday, November 10, 2011 7:07 AM To: Dennis Ryan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to list installed pl languages On Wed, Nov 09, 2011 at 10:58:01PM -0600, Dennis Ryan wrote: > Is there a way to list the installed pl languages for a database > and/or server cluster? \dL in psql or select * from pg_language; Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] troubleshooting PGError
Hello,
I am new to postgres, but need to resolve this error:
PGError: ERROR: current transaction is aborted, commands ignored
until end of transaction block
: SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
1
Don't know what is the problem, tried running the same query in
pgAdmin and the query runs fine..
I don't know what the error actually is since the error message is not
clear. Any ideas on how to investigate this?
thank you.
--
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] function within a function/rollbacks/exception handling
Richard, I manage to find one comment about an implicit rollback in a section of the developer's guide when porting from Oracle-to-Postgres: "when an exception is caught by an EXECPTION clause, all database changes since the block's BEGIN are automatically rolled back" Do you know of any other place in the documentation this discusses the implicit rollback in more detail? Or do you know of a good online site that contains some good examples or best-practices for these function-to-function calls? We are starting to port our Sybase database (200 stored procedures) over to Postgres and I am finding the online Postgres documentation and the Douglas book a bit lacking in some of the more specific examples that I am interested in finding. Thanks. Lori Richard Huxton wrote: On 07/11/11 19:18, Lori Corbani wrote: I have a function, call it 'functionMain'. And I have several tables that each have trigger functions. Each trigger function needs to call 'functionMain' (with different parameters). table A => trigger function A ==> functionMain table B => trigger function B ==> functionMain table C => trigger function C ==> functionMain 'functionMain' returns VOID (runs an insert statement). and has an exception/raise exception block. An insert transaction for table A is launched (insertA), trigger function A is called, 'functionMain' is called and 'functionMain' fails. Hence, trigger function A needs to rollback. Questions: a) I am assuming that the trigger functions should use 'PERFORM functionMain()'? If you don't want the result, yes. b) if 'functionMain' fails, then 'funtionMain' automatically performs an implicit rollback, correct? c) if 'functionMain' fails, should the trigger function also contain an exception handler or will the rollback from 'functionMain' cascade up to the original transaction (insertA)? Unless you catch the exception, it will roll back the whole transaction, so "yes" to b + c. If it helps to visualise what happens, exceptions are actually implemented using savepoints in plpgsql. -- Lori E. Corbani Scientific Software Engineer The Jackson Laboratory 600 Main Street Bar Harbor, ME 04609 USA (207) 288-6425 (V) ** lori.corb...@jax.org http://www.informatics.jax.org ** -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select from dynamic table names
I have 16 tables which created according to generated MD5 hash.
At the time of execution i tried to retrieve data from a table where
table name id dynamic.
table name can be expertdb.rate_AVAIL_[0 to F]
I tried to get this from ;
SELECT * FROM expertdb.rate_AVAIL_ ||
upper(substring(md5('2011-11-10')from 1 for 1))
but it is giving an error that is error with '||' appender. please
someone give me an idea..
--
Thanks and Regards,
Dinesh Kumara,
Software Engineer,
Reservation Gateway Inc,
Email:din...@rezgateway.com
www.rezgateway.com
Re: [GENERAL] How to inquiry a nest result?
On 10 November 2011 15:43, shuaixf wrote: > --*Test SQL* > CREATE TABLE tb(id integer primary key, > name varchar(32), > parent integer); > > INSERT INTO tb VALUES(1, 'iPhone', NULL); > INSERT INTO tb VALUES(2, 'HTC', NULL); > INSERT INTO tb VALUES(3, 'Motorola', NULL); > INSERT INTO tb VALUES(4, 'iPhone3GS', 1); > INSERT INTO tb VALUES(5, 'G7', 2); > INSERT INTO tb VALUES(6, 'G8', 2); > INSERT INTO tb VALUES(7, 'iPhone4', 1); > INSERT INTO tb VALUES(8, 'iPhone4-white', 7); > INSERT INTO tb VALUES(9, 'iPhone4-black', 7); > INSERT INTO tb VALUES(10,'G7-A', 5); > INSERT INTO tb VALUES(11,'G7-B', 5); > > *How to create a SQL to inquiry the result like this:* > id name > > 1 iPhone > 4 iPhone3GS > 7 iPhone4S > 8 iPhone4S-white > 9 iPhone4S-black > 2 HTC > 5 G7 > 10 G7-A > 11 G7-B > 3 Motorola > > Thank you very much! > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/How-to-inquiry-a-nest-result-tp4981259p4981259.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. You can do that with a recursive CTE (common table expression). See the documentation for SELECT in versions 8.4 or later, which contains examples of queries like these. -- 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] troubleshooting PGError
On 9 November 2011 06:02, slavix wrote:
> Hello,
> I am new to postgres, but need to resolve this error:
>
> PGError: ERROR: current transaction is aborted, commands ignored
> until end of transaction block
> : SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
> 'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
> 1
>
> Don't know what is the problem, tried running the same query in
> pgAdmin and the query runs fine..
> I don't know what the error actually is since the error message is not
> clear. Any ideas on how to investigate this?
A query earlier in your transaction failed and invalidated the
transaction. You should probably roll it back.
--
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] troubleshooting PGError
On Tuesday, November 08, 2011 9:02:40 pm slavix wrote:
> Hello,
> I am new to postgres, but need to resolve this error:
>
> PGError: ERROR: current transaction is aborted, commands ignored
> until end of transaction block
>
> : SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
>
> 'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
> 1
>
> Don't know what is the problem, tried running the same query in
> pgAdmin and the query runs fine..
> I don't know what the error actually is since the error message is not
> clear. Any ideas on how to investigate this?
What version of Postgres?
Where are you running the above from?
>
> thank you.
--
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] troubleshooting PGError
your transaction had an error, and any query after the first one that has failed will be ignored. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updating Geometry
Hello,
I am having trouble when attempting to update an existing geometry on a spatial
table. The table stores 2D Point geometry, with the SRID 101. The update
statement i am trying to use to update the geometry with the id 110 is as
follows:
UPDATE SET = GeometryFromText('POINT(44
31)', 101) WHERE =110
But I get an error that says the statement violates the "enforce_srid_position"
restriction. Though the SRID's are the same.
What is the right way to update an existing geometry?
Thank you.
Re: [GENERAL] How to inquiry a nest result?
Hello 2011/11/10 shuaixf : > --*Test SQL* > CREATE TABLE tb(id integer primary key, > name varchar(32), > parent integer); > > INSERT INTO tb VALUES(1, 'iPhone', NULL); > INSERT INTO tb VALUES(2, 'HTC', NULL); > INSERT INTO tb VALUES(3, 'Motorola', NULL); > INSERT INTO tb VALUES(4, 'iPhone3GS', 1); > INSERT INTO tb VALUES(5, 'G7', 2); > INSERT INTO tb VALUES(6, 'G8', 2); > INSERT INTO tb VALUES(7, 'iPhone4', 1); > INSERT INTO tb VALUES(8, 'iPhone4-white', 7); > INSERT INTO tb VALUES(9, 'iPhone4-black', 7); > INSERT INTO tb VALUES(10,'G7-A', 5); > INSERT INTO tb VALUES(11,'G7-B', 5); > > *How to create a SQL to inquiry the result like this:* > id name > > 1 iPhone > 4 iPhone3GS > 7 iPhone4S > 8 iPhone4S-white > 9 iPhone4S-black > 2 HTC > 5 G7 > 10 G7-A > 11 G7-B > 3 Motorola > > Thank you very much! > > postgres=# with recursive x as (select tb.*, tb.id::text as path from tb where parent is null union all select tb.*, path ||'|' || tb.id from tb join x on tb.parent = x.id) select id, name from x order by path; id │ name ┼─── 1 │ iPhone 4 │ iPhone3GS 7 │ iPhone4 8 │ iPhone4-white 9 │ iPhone4-black 2 │ HTC 5 │ G7 10 │ G7-A 11 │ G7-B 6 │ G8 3 │ Motorola (11 rows) > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/How-to-inquiry-a-nest-result-tp4981259p4981259.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > 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] pg_dump -n switch lock schema from dml/ddl?
I'm testing out various pg_dump scenarios using the -n switch and I have a few questions: - When using the -n switch, is the whole schema locked from all non-read DML/DDL operations? - If the whole schema is locked, once each table is dumped, is it then released for non-read DML/DDL operations? - Once the final table is dumped (i.e. pg_dump: dumping contents of table zip_data), are there additional background processes that are still performing maintenance tasks? Or is the entire process complete and all objects are released? I'm asking because I have a schema with a large table with many indexes that is consuming the majority of the dump. This version of the dump takes about 4 hours. As a solution, we run 2 separate dumps in parallel, one with the schema excluding the large table and one including only the large table. The option with just the large table takes 2.5 hours. However, the option with the schema excluding the large table still takes 4 hours. If pg_dump locks each table individually, then releases when the dump is completed, I must be encountering lock contention. Also, I use the -v switch, however I'm not getting any information on how long the dump of each object takes, is there an option that exists where I can collect this information in the log file? Thanks. Tony -- 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] troubleshooting PGError
On Thursday, November 10, 2011 8:47:39 am you wrote: > not sure about version.. (latest i presume because installed recently) > same problem on my local machine running Ubuntu and on Heroku server > what command to get version? > select version(); -- 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]
On 11/09/11 11:54 PM, daflmx wrote: I have installed the postgresql . $/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data . What version of postgres? What installation method? (compile from source? install prebuilt binary from where?) What operating system version and distribution? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] troubleshooting PGError
On Thursday, November 10, 2011 8:56:56 am you wrote: > "PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit" > Please reply to the list also. That gets your issue in front of more eyes:) The above answers my first question. The second question is still out there: Where are you running the above from? You say it runs fine in PgAdmin, so where are running the query to get the error message? -- 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] function within a function/rollbacks/exception handling
Hello 2011/11/8 Lori Corbani : > > Richard, > > I manage to find one comment about an implicit rollback in a section of the > developer's guide when porting from Oracle-to-Postgres: "when an exception > is caught by an EXECPTION clause, all database changes since the block's > BEGIN are automatically rolled back" > > Do you know of any other place in the documentation this discusses the > implicit rollback in more detail? Or do you know of a good online site that > contains some good examples or best-practices for these function-to-function > calls? > > We are starting to port our Sybase database (200 stored procedures) over to > Postgres and I am finding the online Postgres documentation and the Douglas > book a bit lacking in some of the more specific examples that I am > interested in finding. I am not sure if you will find what you need :( PostgreSQL has different model of exception handling inside procedures than other databases - it is based on fact, so Pg has only functions (not procedures) - void function is not equalent to procedures in sybase. This model is more simple - you don't need to thinking about COMMITs or ROLLBACKs inside PL - this is done outside procedures. This model has some advantages and some disadvantages - and mainly it is different Regards Pavel Stehule > > Thanks. > Lori > > > Richard Huxton wrote: >> >> On 07/11/11 19:18, Lori Corbani wrote: >> >>> >>> I have a function, call it 'functionMain'. And I have several tables >>> that each have trigger functions. Each trigger function needs to call >>> 'functionMain' (with different parameters). >>> >>> table A => trigger function A ==> functionMain >>> table B => trigger function B ==> functionMain >>> table C => trigger function C ==> functionMain >>> >>> 'functionMain' returns VOID (runs an insert statement). and has an >>> exception/raise exception block. >>> >>> An insert transaction for table A is launched (insertA), trigger function >>> A is called, >>> 'functionMain' is called and 'functionMain' fails. Hence, trigger >>> function A needs to rollback. >>> >>> Questions: >>> >>> a) I am assuming that the trigger functions should use 'PERFORM >>> functionMain()'? >> >> >> If you don't want the result, yes. >> >>> b) if 'functionMain' fails, then 'funtionMain' automatically performs an >>> implicit rollback, correct? >>> >>> c) if 'functionMain' fails, should the trigger function also contain an >>> exception handler >>> or will the rollback from 'functionMain' cascade up to the original >>> transaction (insertA)? >> >> >> Unless you catch the exception, it will roll back the whole transaction, >> so "yes" to b + c. If it helps to visualise what happens, exceptions are >> actually implemented using savepoints in plpgsql. >> > > -- > > Lori E. Corbani > Scientific Software Engineer > The Jackson Laboratory > 600 Main Street > Bar Harbor, ME 04609 USA > (207) 288-6425 (V) > ** > lori.corb...@jax.org > http://www.informatics.jax.org > ** > > -- > 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] Returning a row from a function with an appended array field
On 11/9/2011 7:19 PM, Wes Cravens wrote: > I have an adjacency list kind of table > > CREATE TABLE thingy ( > id int, > parent int > ); > > I'd like to be able to write a procedural function that returns a row or > rows from this table with an appended field that represents the children. Just in case someone else want's an answer to this tread... and despite the fact that the postgresql documentation is excellent and has plenty of examples, WITH RECURSIVE is still a bad solution... I already needed an independent get_children function: CREATE OR REPLACE FUNCTION get_children ( lookup_id INT ) RETURNS int[] AS $$ SELECT array_agg( id ) FROM ( SELECT id FROM thingy WHERE parent_id = $1 ORDER BY id ) t; $$LANGUAGE 'sql'; And I just used that in a view to get what I wanted: CREATE OR REPLACE VIEW thingy_view AS SELECT *,get_children(id) AS children FROM thingy; I then updated all of my other get_ accessor postgresql functions to use the view instead of the base table. FTW Wes -- 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] troubleshooting PGError
On Thursday, November 10, 2011 9:10:10 am Slava Mikerin wrote: > I am running Rails 3.1 that uses a db. I used phpmyadmin for > development and deployed to Heroku which uses postgres. When I got > this error on the Heroku server I switched to postgres locally to > investigate and got same error. So what do the postgres logs show? The message shows that something caused an error within a transaction block, at that point all over commands are ignored until a ROLLBACK is given. Look in the log and see if you find anything suspicious. > > ruby-1.9.2-p290 and pg-0.11.0 gem used > -- 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] Re: plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Henry Drexler Sent: Thursday, November 10, 2011 8:42 AM To: Thomas Kellerer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Re: plpgsql, I have a solution, want to see if there is a cleaner/better one. Taking vertical list and comma separating it onto a row On Thu, Nov 10, 2011 at 8:34 AM, Thomas Kellerer wrote: SELECT type, string_agg(color, ',') as organized_by_type FROM clothes GROUP BY type; wow, yes that is cleaner. Thank you for taking the time - obviously I need to read through the string functions again. It isn't a "String Function" but an "Aggregate Function"; classification can be a pain sometimes - especially when more than one category could readily apply. David J.
Re: [GENERAL] select from dynamic table names
Hello
2011/11/8 Dinesh Kumara
> **
> I have 16 tables which created according to generated MD5 hash.
> At the time of execution i tried to retrieve data from a table where table
> name id dynamic.
>
> table name can be expertdb.rate_AVAIL_[0 to F]
>
> I tried to get this from ;
> SELECT * FROM expertdb.rate_AVAIL_ ||
> upper(substring(md5('2011-11-10')from 1 for 1))
>
> but it is giving an error that is error with '||' appender. please someone
> give me an idea..
>
you can't do it on SQL level - column and table names must be constant
Regards
Pavel Stehule
>
> --
>
> *Thanks and Regards,*
> Dinesh Kumara,
> Software Engineer,
> Reservation Gateway Inc,
> Email:din...@rezgateway.com
> www.rezgateway.com
>
Re: [GENERAL] Returning a row from a function with an appended array field
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wes Cravens Sent: Thursday, November 10, 2011 11:54 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Returning a row from a function with an appended array field On 11/9/2011 7:19 PM, Wes Cravens wrote: > I have an adjacency list kind of table > > CREATE TABLE thingy ( > id int, > parent int > ); > > I'd like to be able to write a procedural function that returns a row > or rows from this table with an appended field that represents the children. Just in case someone else want's an answer to this tread... and despite the fact that the postgresql documentation is excellent and has plenty of examples, WITH RECURSIVE is still a bad solution... I already needed an independent get_children function: CREATE OR REPLACE FUNCTION get_children ( lookup_id INT ) RETURNS int[] AS $$ SELECT array_agg( id ) FROM ( SELECT id FROM thingy WHERE parent_id = $1 ORDER BY id ) t; $$LANGUAGE 'sql'; And I just used that in a view to get what I wanted: CREATE OR REPLACE VIEW thingy_view AS SELECT *,get_children(id) AS children FROM thingy; I then updated all of my other get_ accessor postgresql functions to use the view instead of the base table. FTW Wes -- If you only care about one level of hierarchy then, yes, WITH RECURSIVE is overkill. You want to use WITH RECURSIVE in those situations where the depth of the hierarchy is unknown. 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] Returning a row from a function with an appended array field
On 11/10/2011 12:05 PM, David Johnston wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wes Cravens > Sent: Thursday, November 10, 2011 11:54 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Returning a row from a function with an appended > array field > > On 11/9/2011 7:19 PM, Wes Cravens wrote: >> I have an adjacency list kind of table >> >> CREATE TABLE thingy ( >> id int, >> parent int >> ); >> >> I'd like to be able to write a procedural function that returns a row >> or rows from this table with an appended field that represents the > children. > > > If you only care about one level of hierarchy then, yes, WITH RECURSIVE is > overkill. You want to use WITH RECURSIVE in those situations where the > depth of the hierarchy is unknown. Yes agreed... WITH RECURSIVE would be handy for something like get_ancestors or get_descendents. Wes -- 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] Foreign Keys and Deadlocks
On Thu, Nov 10, 2011 at 09:09:06AM +0100, Csaba Nagy wrote: - Hi David, - - On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: - > So, aside from removing the PKs do i have any other options? - - Sure you have: order the inserts by primary key inside each transaction. - Then you will not get deadlocks, but inserting the same key again will - fail of course (but that's the purpose of the primary key, right ?) - - Ordering inserts/updates by the columns which cause locks is the first - thing to do to avoid dead-locks... - - Cheers, - Csaba. ah, hmmm. i'm not sure if that's an option based on how the program works but I'll forward the suggestion onto the devleoper. thanks! -- 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] troubleshooting PGError
On 10 Nov 2011, at 17:42, Slava Mikerin wrote:
> Thank you for reply Alban, I am new to postgres and don't know the
> right commands to use to troubleshoot this issue.. The error message
> is unclear and I don't know what caused the problem. can you tell me
> what exact command to use? I don't need to roll back, but to need to
> discover cause of error and eliminate.. thanks for advice.
The error you're seeing is the result of a previous error.
Without seeing that earlier error and the query that caused it nobody can tell
you what's wrong with your query, because we don't even know which query you're
talking about.
Errors in transactions behave like this:
postgres=> BEGIN;
BEGIN
postgres=> SELECT 1; ?column?
--
1(1 row)
postgres=> SELECT;
ERROR: syntax error at or near ";"
LINE 1: SELECT;
^
postgres=> SELECT 1;
ERROR: current transaction is aborted, commands ignored until end of
transaction block
As you can see, there is nothing wrong with the query that causes the error
"current transaction is aborted". It is a query before it that threw an error
and caused the transaction to be aborted.
The only ways to get out of the aborted transaction is to either roll it back
or to commit (which will also roll back).
You can also use SAVEPOINTs between blocks of queries that you don't want to
roll back (sub-transactions). With those you can just roll back to an earlier
savepoint and continue from there.
You really should read the relevant documentation at:
http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html
P.S. Please don't top-post on this list.
P.P.S. And please include the list in your replies (reply all).
> On Thu, Nov 10, 2011 at 8:30 AM, Alban Hertroys wrote:
>> On 9 November 2011 06:02, slavix wrote:
>>> Hello,
>>> I am new to postgres, but need to resolve this error:
>>>
>>> PGError: ERROR: current transaction is aborted, commands ignored
>>> until end of transaction block
>>> : SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
>>> 'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
>>> 1
>>>
>>> Don't know what is the problem, tried running the same query in
>>> pgAdmin and the query runs fine..
>>> I don't know what the error actually is since the error message is not
>>> clear. Any ideas on how to investigate this?
>>
>> A query earlier in your transaction failed and invalidated the
>> transaction. You should probably roll it back.
>>
>> --
>> If you can't see the forest for the trees,
>> Cut the trees and you'll see there is no forest.
>>
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] Returning a row from a function with an appended array field
On 10 Nov 2011, at 19:51, Wes Cravens wrote: > On 11/10/2011 12:05 PM, David Johnston wrote: >> On 11/9/2011 7:19 PM, Wes Cravens wrote: >>> I have an adjacency list kind of table >>> >>> CREATE TABLE thingy ( >>> id int, >>> parent int >>> ); >>> >>> I'd like to be able to write a procedural function that returns a row >>> or rows from this table with an appended field that represents the children. >> >> If you only care about one level of hierarchy then, yes, WITH RECURSIVE is >> overkill. You want to use WITH RECURSIVE in those situations where the >> depth of the hierarchy is unknown. > > Yes agreed... WITH RECURSIVE would be handy for something like > get_ancestors or get_descendents. If you only need one level of recursion, you can just use a self-join. SELECT parent.id AS parent_id, child.id as child_id FROM thingy AS parent LEFT OUTER JOIN thingy AS child ON (child.parent_id = parent.id) Alban Hertroys -- The scale of a problem often equals the size of an ego. -- 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] troubleshooting PGError
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of slavix
Sent: Wednesday, November 09, 2011 12:03 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] troubleshooting PGError
Hello,
I am new to postgres, but need to resolve this error:
PGError: ERROR: current transaction is aborted, commands ignored until end
of transaction block
: SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
1
Don't know what is the problem, tried running the same query in pgAdmin and
the query runs fine..
I don't know what the error actually is since the error message is not
clear. Any ideas on how to investigate this?
thank you.
The error message is very clear; if you understand what a transaction is.
You are executing a number of statements in a single transaction and one of
previous ones failed. You should see another error message earlier that was
the true failure. All this message is saying that because the transaction
had a failure all subsequent statements that belong to the same transaction
will be ignored since, at the end of the transaction, everything is going to
be rolled-back anyway.
If you are still confused you need to provide more details about your
programming environment and all the statements that you are bundling into
the same transaction at the statement shown; and a significant section of
the PostgreSQL log file would help as well.
This normally doesn't show up since you usually want to stop processing a
transaction as soon as a failure occurs. If you provide the entire
transaction to PostgreSQL all at once then PostgreSQL will normally stop
processing as soon as it encounters an error and will return that error.
However, if you take manual control of the transaction in your programming
environment then if you attempt to execute additional statements even after
an error is thrown PostgreSQL will throw the error you are seeing saying
that, in effect, your attempt to execute a statement is pointless.
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
[GENERAL] PostgreSQL uninstall fails
I have to do a lot of testing which involves uninstalling PostgreSQL completely sometimes, but there is a lot of crud still left around after an uninstall. 1) the installation directory with PostgreSQL droppings - I have to go manually remove it. I do not know why the uninstaller does not blow it away. 2) Registry Keys - dozens and dozens of keys that confuse the next installation - to test: Install 9.0.1 then uninstall - Install 8.4.2 and launch pgadmin III and you will see what I mean I do a search on every key that contains the word 'postgres' and have to delete that manually 3) The postgres user remains on the system Can anyone get me started on where to checkout the postgreSQL installer code and fix it so that it is no longer broken? I may need to ask a few questions so if I can be put in touch with the uninstaller team, would really like to get this fixed and go back and patch everything from 8.4 forwards so that it is fixed. It is very time consuming for me to do 1..3 and I am sure others as well and I want to get this fixed as soon as I can. Regards, J.V. -- 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 uninstall fails
On 11/10/11 1:30 PM, J.V. wrote: Can anyone get me started on where to checkout the postgreSQL installer code and fix it so that it is no longer broken? I may need to ask a few questions so if I can be put in touch with the uninstaller team, would really like to get this fixed and go back and patch everything from 8.4 forwards so that it is fixed. are you talking about the MS Windows installer from EnterpriseDB? about the only clue was the mention in passing of 'registry'. deleting the postgresql 'data' directory and the postgres user account should be optional. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Updating Geometry
Asli Akarsakarya writes:
> I am having trouble when attempting to update an existing geometry on a
> spatial table. The table stores 2D Point geometry, with the SRID 101. The
> update statement i am trying to use to update the geometry with the id 110 is
> as follows:
> UPDATE SET = GeometryFromText('POINT(44
> 31)', 101) WHERE =110
> But I get an error that says the statement violates the
> "enforce_srid_position" restriction. Though the SRID's are the same.
You'll probably have better luck asking about this on the postgis
mailing lists ...
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] Learning to rephrase equivalent queries?
Hi, On 11 November 2011 00:04, Jay Levitt wrote: > Sometimes the planner can't find the most efficient way to execute your > query. Thanks to relational algebra, there may be other, logically > equivalent queries that it DOES know how to optimize. > > But I don't know relational algebra. yet. (Date/Codd is a sleeping pill.) > I need more experience first. > > Are there blogs, guides, rules of thumb, common refactoring patterns out > there somewhere? I'm looking for a list of basic equalities, the SQL > equivalent of: Have a look here: http://en.wikipedia.org/wiki/Relational_algebra plus "External links" section -- Ondrej Ivanic (ondrej.iva...@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] PostgreSQL uninstall fails
yes, this is on windows. Currently removing the data directory and the postgresql user is not optional. It fails and does not remove those two items. There must be 30+ registry keys still there as well. What I am looking to do is to delete any registry entry: 1. that has a data value matching the pattern '*postgres*' 2. that has a directory value matching the pattern '*postgres*' 3. that has a key name matching the pattern '*postgres*' thanks J.V. On 11/10/2011 2:36 PM, John R Pierce wrote: On 11/10/11 1:30 PM, J.V. wrote: Can anyone get me started on where to checkout the postgreSQL installer code and fix it so that it is no longer broken? I may need to ask a few questions so if I can be put in touch with the uninstaller team, would really like to get this fixed and go back and patch everything from 8.4 forwards so that it is fixed. are you talking about the MS Windows installer from EnterpriseDB? about the only clue was the mention in passing of 'registry'. deleting the postgresql 'data' directory and the postgres user account should be optional.
[GENERAL] PL/pgSQL: SELECT INTO variables - no result
I go through a series of SELECT INTO in a trigger function. SELECT INTO country_id id FROM vcountry WHERE [...]; I might or o might not find a result. Next, I try to find a region within this country, if found previously, and if not anywhere. SELECT INTO country_id, region_id country_fk, id FROM vregion WHERE (country_id IS NULL OR country_fk = country_id) AND name ILIKE place; If the first search found a country, and the second search fails to find the region, the second search will set my variables to NULL. So, a previously populated country_id will now be NULL. Is there any way I can avoid this or do I have to use different sets of variables? -- Best Regards, Tarlika Elisabeth Schmitz -- 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 uninstall fails
On 11/10/11 2:18 PM, J.V. wrote: What I am looking to do is to delete any registry entry: 1. that has a data value matching the pattern '*postgres*' 2. that has a directory value matching the pattern '*postgres*' 3. that has a key name matching the pattern '*postgres*' these registry keys are likely under just a few reg keys, no? should be easy enough to write a .vbs or whatever cleanup script that just purges these left behind. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "idle in transaction" entry in pg_logs
Respected, All the time we see 'idle in transaction' in pg_stat_activity and dig details with process pid from pg_logs for the query,query execution time etc.. Instead of searching with process pid, am trying to pull the information with shell scripting for lines prefixed with 'idle in transaction' in pg_logs line. With log_line_prefix %i option I can see logs are prefixing 'idle in transaction'. log_line_prefix = '%m-%u@%d-[%x]-%p-%i' Case 1: [ log_min_duration_statement = 0, log_statement='all', log_duration=off or log_duration=on ] In logs: 2011-10-05 18:28:22.028 IST-postgres@postgres-[0]-22398-idle LOG: statement: begin; 2011-10-05 18:28:22.029 IST-postgres@postgres-[0]-22398-BEGIN LOG: duration: 0.703 ms 2011-10-05 18:28:39.847 IST-postgres@postgres-*[0]*-22398-idle in transaction LOG: statement: insert into abc VALUES (11); 2011-10-05 18:28:39.848 IST-postgres@postgres-[682]-22398-INSERT LOG: duration: 0.474 ms 2011-10-05 18:29:00.591 IST-postgres@postgres-[682]-22398-idle in transaction LOG: statement: end; 2011-10-05 18:29:00.595 IST-postgres@postgres-[0]-22398-COMMIT LOG: duration: 3.216 ms Case 2: [ log_min_duration_statement = -1, log_statement='all', log_duration=off ] In logs: 2011-10-05 18:20:04.612 IST-postgres@postgres-[0]-22398-idle LOG: statement: begin; 2011-10-05 18:20:15.922 IST-postgres@postgres-[0]-22398-idle in transaction LOG: statement: insert into abc VALUES (10); 2011-10-05 18:20:27.633 IST-postgres@postgres-[681]-22398-idle in transaction LOG: statement: end; Sample transaction did twice with above changes to log parameters: postgres=# begin; BEGIN postgres=# insert into abc VALUES (11); INSERT 0 1 postgres=# end; COMMIT Question: 1. Is it a standard behavior in Case 1, that every transaction will write two lines in logs ? (i.e., if log_statement='all' and log_duration=on and log_min_duration=0 and log_statement='all') 2. I used %x in log_line_prefix to get transaction id, but I see transaction-id written in logs only when its committed. You can see in both the cases. Why its not written at first occurrence ? Thanks in Advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] troubleshooting PGError
Thank you. I found the problem. It was with the previous query, not the one that was showing up in the prompt. I found the actual query in one of the log files. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] (bez temata)
Hello, I have problem with installation, I cannot install, cause there is a password, and I dont now what it is about, what kind a password, give me the answer please
Re: [GENERAL] troubleshooting PGError
I am running Rails 3.1 that uses a db. I used phpmyadmin for development and deployed to Heroku which uses postgres. When I got this error on the Heroku server I switched to postgres locally to investigate and got same error. ruby-1.9.2-p290 and pg-0.11.0 gem used On Thu, Nov 10, 2011 at 9:04 AM, Adrian Klaver wrote: > On Thursday, November 10, 2011 8:56:56 am you wrote: >> "PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real >> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit" >> > > Please reply to the list also. That gets your issue in front of more eyes:) > > The above answers my first question. > The second question is still out there: > Where are you running the above from? > You say it runs fine in PgAdmin, so where are running the query to get the > error > message? > > > -- > 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] "idle in transaction" entry in pg_logs
On Fri, Nov 11, 2011 at 4:18 AM, Raghavendra < raghavendra@enterprisedb.com> wrote: > Respected, > > All the time we see 'idle in transaction' in pg_stat_activity and dig > details with process pid from pg_logs for the query,query execution time > etc.. > Instead of searching with process pid, am trying to pull the information > with shell scripting for lines prefixed with 'idle in transaction' in > pg_logs line. > With log_line_prefix %i option I can see logs are prefixing 'idle in > transaction'. > > log_line_prefix = '%m-%u@%d-[%x]-%p-%i' > > Case 1: [ log_min_duration_statement = 0, log_statement='all', > log_duration=off or log_duration=on ] > In logs: > > 2011-10-05 18:28:22.028 IST-postgres@postgres-[0]-22398-idle LOG: > statement: begin; > 2011-10-05 18:28:22.029 IST-postgres@postgres-[0]-22398-BEGIN LOG: > duration: 0.703 ms > 2011-10-05 18:28:39.847 IST-postgres@postgres-*[0]*-22398-idle in > transaction LOG: statement: insert into abc VALUES (11); > 2011-10-05 18:28:39.848 IST-postgres@postgres-[682]-22398-INSERT LOG: > duration: 0.474 ms > 2011-10-05 18:29:00.591 IST-postgres@postgres-[682]-22398-idle in > transaction LOG: statement: end; > 2011-10-05 18:29:00.595 IST-postgres@postgres-[0]-22398-COMMIT LOG: > duration: 3.216 ms > > Case 2: [ log_min_duration_statement = -1, log_statement='all', > log_duration=off ] > In logs: > > 2011-10-05 18:20:04.612 IST-postgres@postgres-[0]-22398-idle LOG: > statement: begin; > 2011-10-05 18:20:15.922 IST-postgres@postgres-[0]-22398-idle in > transaction LOG: statement: insert into abc VALUES (10); > 2011-10-05 18:20:27.633 IST-postgres@postgres-[681]-22398-idle in > transaction LOG: statement: end; > > Sample transaction did twice with above changes to log parameters: > postgres=# begin; > BEGIN > postgres=# insert into abc VALUES (11); > INSERT 0 1 > postgres=# end; > COMMIT > > Question: > > 1. Is it a standard behavior in Case 1, that every transaction will write > two lines in logs ? (i.e., if log_statement='all' and log_duration=on and > log_min_duration=0 and log_statement='all') > > 2. I used %x in log_line_prefix to get transaction id, but I see > transaction-id written in logs only when its committed. You can see in both > the cases. Why its not written at first occurrence ? > > Thanks in Advance. > > --- > Regards, > Raghavendra > EnterpriseDB Corporation > Blog: http://raghavt.blogspot.com/ > > Apologies for not mentioning my PG version. Am using PG 9.0.1 --Raghav
Re: [GENERAL] VACUUM touching file but not updating relation
On 14 October 2011 12:12, Thom Brown wrote: > Hi, > > I just noticed that the VACUUM process touches a lot of relations > (affects mtime) but for one file I looked at, it didn't change. This > doesn't always happen, and many relations aren't touched at all. > > I had the following relation: > > -rw--- 1 thom staff 40960 13 Oct 16:06 11946 > > Ran MD5 over the file: > > MD5 (11946) = d6626f930f1fb6d77c3907d3279fe693 > > Then VACUUM ANALYSE'd all databases in full. > > This relation was supposedly affected: > > -rw--- 1 thom staff 40960 14 Oct 11:27 11946 > > But then I ran MD5 back over it: > > MD5 (11946) = d6626f930f1fb6d77c3907d3279fe693 > > This is the same as before. What is it doing? Does this happen > often? And I can't find out what this particular OID relates to > either. > > I'm using 9.2devel btw. Does anyone know what happened here? I'm just wondering if there's some action being performed on the file which can be avoided. Of course I haven't determined how often this happens. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Passing NULL to a function called with OidFunctionCall3
Hey all,
I'm trying to make use of OidFunctionCall3 and am wondering how to
resolve an issue. I need to be able to pass to the function called with
OidFunctionCall3 a NULL and am having difficulty figuring out how.
{{{
/* build fcnarg */
for (i = 0; i < set_count; i++) {
if (_haspixel[i]) {
fcnarg[i] = Float8GetDatum(_pixel[i]);
POSTGIS_RT_DEBUGF(4, "arg %d is %f", i, _pixel[i]);
}
else {
fcnarg[i] = (Datum) NULL;
POSTGIS_RT_DEBUGF(4, "arg %d is NULL", i);
}
}
datum = OidFunctionCall3(fcnoid, fcnarg[0], fcnarg[1], fcnuserarg);
}}}
The above does not work (segfault). What is the correct way to pass a
NULL to the function being called? Should I be using a different
function other than OidFunctionCall3?
Thanks,
Bborie
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
--
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] (bez temata)
On Thursday, November 10, 2011 12:09:26 pm Kaspars Zelgis wrote: > Hello, I have problem with installation, I cannot install, cause there is a > password, and I dont now what it is about, what kind a password, give me > the answer please Install from what to what? Examples: One Click installer on Windows Debian package RPM etc -- 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]
On 11/10/2011 05:04 PM, daflmx wrote: Is the server running locally and accepting connections on Unix domain socket"/var/run/postgresql/.s.PGSQL.5432"? Well? Does the socket file exist? Is PostgreSQL configured to use a different socket - perhaps in /tmp - in postgresql.conf? I'm guessing you're using `psql' from your OS's packages, which expects to find the PostgreSQL socket in /var/run/postgresql/ , but a PostgreSQL server you installed yourself that puts its socket in /tmp or somewhere else. Note that you should still be able to connect with `-H localhost' to use TCP/IP rather than unix domain sockets. -- Craig Ringer
Re: [GENERAL] PL/pgSQL: SELECT INTO variables - no result
On Thu, 10 Nov 2011 17:46:47 -0500 "David Johnston" wrote: >-Original Message- >From: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tarlika >Elisabeth Schmitz >Sent: Thursday, November 10, 2011 5:18 PM >To: pgsql-general@postgresql.org> >Subject: [GENERAL] PL/pgSQL: SELECT INTO variables - no result > >I go through a series of SELECT INTO in a trigger function. > >SELECT INTO country_id id >FROM vcountry WHERE [...]; > >I might or o might not find a result. > >Next, I try to find a region within this country, if found previously, >and if not anywhere. > >SELECT INTO > country_id, region_id > country_fk, id >FROM vregion >WHERE (country_id IS NULL OR country_fk = country_id) AND name ILIKE >place; > >If the first search found a country, and the second search fails to >find the region, the second search will set my variables to NULL. So, >a previously populated country_id will now be NULL. > > >Is there any way I can avoid this or do I have to use different sets of >variables? > > > >Don't return the "country_fk" field in your SELECT list, just the >[region_]id If the country search returns no results and the region search does, then I get the country_id via the region. If zero results nulls the variables, I suppose I have to use two sets of variables. -- 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] Passing NULL to a function called with OidFunctionCall3
Bborie Park writes: > I'm trying to make use of OidFunctionCall3 and am wondering how to > resolve an issue. I need to be able to pass to the function called with > OidFunctionCall3 a NULL and am having difficulty figuring out how. You can't. Those convenience functions are not designed to support null arguments (nor null results, for that matter). If they did, they'd be so much more complicated to use as to not be worth the bother. You'll need to write out something comparable to what OidFunctionCall3 does internally; look into fmgr.c. It would behoove you also to make sure that the function is not strict before you call it with a null, because a function that is strict is entirely entitled to dump core on you if you do that. 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] Passing NULL to a function called with OidFunctionCall3
On 11/10/2011 04:43 PM, Tom Lane wrote: Bborie Park writes: I'm trying to make use of OidFunctionCall3 and am wondering how to resolve an issue. I need to be able to pass to the function called with OidFunctionCall3 a NULL and am having difficulty figuring out how. You can't. Those convenience functions are not designed to support null arguments (nor null results, for that matter). If they did, they'd be so much more complicated to use as to not be worth the bother. You'll need to write out something comparable to what OidFunctionCall3 does internally; look into fmgr.c. It would behoove you also to make sure that the function is not strict before you call it with a null, because a function that is strict is entirely entitled to dump core on you if you do that. regards, tom lane Thanks Tom! -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu -- 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] VACUUM touching file but not updating relation
Thom Brown writes: > On 14 October 2011 12:12, Thom Brown wrote: >> I just noticed that the VACUUM process touches a lot of relations >> (affects mtime) but for one file I looked at, it didn't change. This >> doesn't always happen, and many relations aren't touched at all. No immmediate ideas as to why the mtime would change if the file contents didn't. It seems like there must be a code path that marked a buffer dirty without having changed it, but we're usually pretty careful about that. >> And I can't find out what this particular OID relates to >> either. Well, the generic method is regression=# select oid,relname from pg_class where relfilenode = 11946; oid |relname ---+ 11563 | pg_toast_11561 (1 row) This is a toast relation, so ... regression=# select oid,relname from pg_class where reltoastrelid = 11563; oid | relname ---+-- 11561 | sql_packages (1 row) So in my git-tip database, that relfilenode is information_schema.sql_packages' TOAST table. However, such OID assignments aren't terribly stable in development tip, and it was almost certainly something different a month ago (especially since sql_packages' TOAST table is generally empty, and your file is not). So you'll need to check this for yourself to see what it was, assuming you still have that database around. It's a safe bet that it was a system catalog or index or toast table belonging thereto, though, just based on the range of OIDs it's in. 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] VACUUM touching file but not updating relation
On 11 November 2011 00:55, Tom Lane wrote: > Thom Brown writes: >> On 14 October 2011 12:12, Thom Brown wrote: >>> I just noticed that the VACUUM process touches a lot of relations >>> (affects mtime) but for one file I looked at, it didn't change. This >>> doesn't always happen, and many relations aren't touched at all. > > No immmediate ideas as to why the mtime would change if the file > contents didn't. It seems like there must be a code path that marked > a buffer dirty without having changed it, but we're usually pretty > careful about that. > >>> And I can't find out what this particular OID relates to >>> either. > > Well, the generic method is > > regression=# select oid,relname from pg_class where relfilenode = 11946; > oid | relname > ---+ > 11563 | pg_toast_11561 > (1 row) > > This is a toast relation, so ... > > regression=# select oid,relname from pg_class where reltoastrelid = 11563; > oid | relname > ---+-- > 11561 | sql_packages > (1 row) > > So in my git-tip database, that relfilenode is > information_schema.sql_packages' TOAST table. However, such OID > assignments aren't terribly stable in development tip, and it was almost > certainly something different a month ago (especially since > sql_packages' TOAST table is generally empty, and your file is not). > So you'll need to check this for yourself to see what it was, assuming > you still have that database around. It's a safe bet that it was a > system catalog or index or toast table belonging thereto, though, just > based on the range of OIDs it's in. No, I don't still have the database, but tried the same thing on a pre-existing database and found a few files exhibiting the same change. I checked all files where the time stamp of the file had changed, but had the same MD5 sum. I used the list in the query you mentioned and get: test2=# select oid,relname from pg_class where relfilenode in (11682,11692,11707,11708,11725,11726,11727,11728,11740,11743,11744,11751,11752,11757,11761,11764,11765,11771,11776,11777,11778,11795,11816,11817,11854,11855,11858,11861,11862,11865,11866,11869,11870,11873,11874,11901,11902); oid | relname --+- 2664 | pg_constraint_conname_nsp_index 2651 | pg_am_name_index 2652 | pg_am_oid_index 2756 | pg_amop_oid_index 2757 | pg_amproc_oid_index 2650 | pg_aggregate_fnoid_index 2839 | pg_toast_2618_index 2660 | pg_cast_oid_index 3085 | pg_collation_oid_index 3164 | pg_collation_name_enc_nsp_index 2689 | pg_operator_oprname_l_r_n_index 2754 | pg_opfamily_am_name_nsp_index 2755 | pg_opfamily_oid_index 2681 | pg_language_name_index 2682 | pg_language_oid_index 2692 | pg_rewrite_oid_index 2693 | pg_rewrite_rel_rulename_index 2673 | pg_depend_depender_index 2674 | pg_depend_reference_index 3608 | pg_ts_config_cfgname_index 3712 | pg_ts_config_oid_index 3609 | pg_ts_config_map_index 3604 | pg_ts_dict_dictname_index 3605 | pg_ts_dict_oid_index 3606 | pg_ts_parser_prsname_index 3607 | pg_ts_parser_oid_index 3766 | pg_ts_template_tmplname_index 3767 | pg_ts_template_oid_index 3080 | pg_extension_oid_index 2840 | pg_toast_2619 2665 | pg_constraint_conrelid_index 2666 | pg_constraint_contypid_index 2667 | pg_constraint_oid_index 3081 | pg_extension_name_index (34 rows) An additional VACUUM shows up no such changes except for the case of a visibility map, although I suspect that's expected to happen. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PQexecParams with binary resultFormat vs BINARY CURSOR
Hi, Considering query for binary data stored directly in tables using libpq API, I'm trying to understand what is the difference between specifying binary format in functions like PQexecParams and use of BINARY CURSOR. For example, with query like this: SELECT large_image FROM tbl; where large_image is a custom type, is there a big difference between binary format specified to libpq and use of BINARY CURSOR? Is it client-side binary vs server-side binary processing? Simply, I'd like to avoid textual<->binary conversions at any stage. (Endianness is not an issue here.) Best regards, -- Mateusz Loskot, http://mateusz.loskot.net -- 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] Fedora 16 note...
On 11/10/2011 11:10 PM, Jerry Levan wrote: I upgraded to Fedora 16 yesterday… I thought I might have lost my 12 year old db when the system came up and I noticed the 9.1 had overwrote the old binaries. ... of course, you keep regular backups so you weren't too worried anyway right? Then I read about pg_upgrade stuff and it worked! Good to hear. I tend to dump and reload between versions as I have fairly small data, but it's good to hear people getting successful use out of pg_upgrade. I found that postgresql would not start at boot time until I did: systemctl enable postgresql.service That's Fedora policy: don't start a service unless the user asks for it to be started. -- 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] PostgreSQL uninstall fails
On 11/11/2011 06:18 AM, J.V. wrote: yes, this is on windows. Currently removing the data directory and the postgresql user is not optional. It fails and does not remove those two items. There must be 30+ registry keys still there as well. What I am looking to do is to delete any registry entry: 1. that has a data value matching the pattern '*postgres*' 2. that has a directory value matching the pattern '*postgres*' 3. that has a key name matching the pattern '*postgres*' I think that's a _bad_ idea for several reasons: - More than one PostgreSQL version can be installed concurrently - Other products include the name-part "postgres", such as "Postgres Plus" among others. - The installer cannot tell whether any other users of the mu "postgres" user account remain. A PgAgent install may still be present even after PostgreSQL has been uninstalled, for example, and the user won't want it uninstalled especially if they're about to reinstall PostgreSQL. Also, when more than one Pg version is present it's hard to be certain whether the running uninstaller is the _last_ one on the system and should remove the "postgres" user account. I don't think the usual uninstaller should behave as you describe. That said, I do see value in a "clean" uninstall option that strips out everything at the risk of possibly breaking parallel installs of other products or PostgreSQL versions. I guess in an ideal world PostgreSQL installers and uninstallers could refcount so they knew when the last product was uninstalled. In practice, people can't be relied on to use uninstallers properly, 3rd party products won't manage the refcount properly, etc, and it'll land up breaking things. -- 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] (bez temata)
On 11/11/2011 04:09 AM, Kaspars Zelgis wrote: Hello, I have problem with installation, I cannot install, cause there is a password, and I dont now what it is about, what kind a password, give me the answer please You have to make the password up. It is the password you will use to log in to your database as the "postgres" user once the install completes. If you are told the password is wrong, then you've had PostgreSQL installed before and you need to use the password you set then. If you don't know it, you can change the password for the "postgres" user account in Windows (using Administrative Tools) to one that you do know. -- 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]
Hello,all. I have installed the postgresql . $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start LOG:database system was shut down at 2011-11-10 15:36:14 CST LOG:database system is ready to accept connections LOG:autovacuum launcher started when I check the status of cluster $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status pg_ctl:server is running(PID 18432) but why I cannot create a database successfully? $/usr/local/pgsql/bin/createdb mydb createdb:could nto connect to database postgres:could not connect to server:No such file or directory Is the server running locally and accepting connections on Unix domain socket"/var/run/postgresql/.s.PGSQL.5432"? and I did not find the ".s.PGSQL.5432" file in the whole file system.what's the wrong? Thanks.
Re: [GENERAL] Fedora 16 note...
On Nov 10, 2011, at 9:56 PM, Craig Ringer wrote: > On 11/10/2011 11:10 PM, Jerry Levan wrote: >> I upgraded to Fedora 16 yesterday… >> >> I thought I might have lost my 12 year old db when the system came up >> and I noticed the 9.1 had overwrote the old binaries. > > ... of course, you keep regular backups so you weren't too worried anyway > right? > Not that I am paranoid or anything but I keep manually maintained clones on three other machines that are backed up via time machine to my NAS and I superduper the macs to separate disks. I also semi-periodically rsync many directories on the Fedora box to a separate disk. Dblink makes the manually cloning of the tables an easy task. I have written a bunch of tools to access postgresql, sorta like a PgAdmin light ( http://homepage.mac.com/levanj ) >> Then I read about pg_upgrade stuff and it worked! > > Good to hear. I tend to dump and reload between versions as I have fairly > small data, but it's good to hear people getting successful use out of > pg_upgrade. > >> I found that postgresql would not start at boot time until >> I did: >> >> systemctl enable postgresql.service > > That's Fedora policy: don't start a service unless the user asks for it to be > started. This is the first time I have had to manually enable a service like postgresql and httpd since Fedora 4. I guess this is mostly from the systemd take over... > > -- > 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]
You have sent this message at least once before. Pleas go back and read the replies to the first one, don't just re-send your message. On 11/11/2011 11:36 AM, daflmx wrote: Hello,all. I have installed the postgresql . $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start LOG:database system was shut down at 2011-11-10 15:36:14 CST LOG:database system is ready to accept connections LOG:autovacuum launcher started when I check the status of cluster $/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status pg_ctl:server is running(PID 18432) but why I cannot create a database successfully? $/usr/local/pgsql/bin/createdb mydb createdb:could nto connect to database postgres:could not connect to server:No such file or directory Is the server running locally and accepting connections on Unix domain socket"/var/run/postgresql/.s.PGSQL.5432"? and I did not find the ".s.PGSQL.5432" file in the whole file system.what's the wrong? Thanks.
Re: [GENERAL] Fedora 16 note...
Jerry Levan writes: > On Nov 10, 2011, at 9:56 PM, Craig Ringer wrote: >> On 11/10/2011 11:10 PM, Jerry Levan wrote: >>> I found that postgresql would not start at boot time until >>> I did: >>> systemctl enable postgresql.service >> That's Fedora policy: don't start a service unless the user asks for it to >> be started. > This is the first time I have had to manually enable a service like > postgresql and httpd > since Fedora 4. I guess this is mostly from the systemd take over... It's exactly from the systemd takeover. Traditionally a system upgrade would preserve your sysv "chkconfig" settings for which services to autostart, but there is a specific policy in place to not do that when a service is transitioned to systemd. The reasoning was that in many cases the configuration mechanisms are changing at the same time (for instance, postgresql no longer pays attention to /etc/sysconfig/) and autostarting a possibly-now-misconfigured daemon seemed like a bad idea. 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] strange behavior, hoping for an explanation
Hi;
I have found recently that tables in certain contexts seem to have a
name pseudocolumn. I was wondering if there is any documentation as
to what this is and what it signifies.
postgres=# CREATE table TEST2 (a text, b text);
CREATE TABLE
postgres=# INSERT INTO test2 values ('', '');
INSERT 0 1
postgres=# select t.name FROM test2 t;
name
-
(,)
(1 row)
However:
postgres=# select name FROM test2 t;
ERROR: column "name" does not exist
LINE 1: select name FROM test2 t;
This isn't making any sense to me. Are there certain circumstances
where a tuple is cast to something like varchar(63)? Does this pose
pitfals for any columns named 'name' in other contexts?
Best Wishes,
Chris Travers
--
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] strange behavior, hoping for an explanation
Chris Travers writes: > I have found recently that tables in certain contexts seem to have a > name pseudocolumn. I was wondering if there is any documentation as > to what this is and what it signifies. I/O conversion cast from composite type to string. You might find this 9.1 patch informative: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=543d22fc7423747afd59fe7214f2ddf6259efc62 There's also relevant discussion in the mailing lists shortly before that. 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] strange behavior, hoping for an explanation
See documentation, chapter Viii.E.2.2.2
2011/11/11, Chris Travers :
> Hi;
>
> I have found recently that tables in certain contexts seem to have a
> name pseudocolumn. I was wondering if there is any documentation as
> to what this is and what it signifies.
>
> postgres=# CREATE table TEST2 (a text, b text);
> CREATE TABLE
> postgres=# INSERT INTO test2 values ('', '');
> INSERT 0 1
> postgres=# select t.name FROM test2 t;
> name
> -
> (,)
> (1 row)
>
> However:
>
>
> postgres=# select name FROM test2 t;
> ERROR: column "name" does not exist
> LINE 1: select name FROM test2 t;
>
> This isn't making any sense to me. Are there certain circumstances
> where a tuple is cast to something like varchar(63)? Does this pose
> pitfals for any columns named 'name' in other contexts?
>
> Best Wishes,
> Chris Travers
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
