Re: [GENERAL] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 11:00 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. I had already done this. Doesn't do anything. Pgbouncer starts (service pgbouncer restart) but when I try to connect, it tells me psql: ERROR: No such user: MYSITE_pgbouncer Where should i create the MYSITE_pgbouncer user? Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt Seems you already did this. I believe you are connecting as postgres user not from root, if yes, then check .pgpass file too (it will be in postgres user home directory). --Raghav
[GENERAL] Issue of upgrading from 9.0.4 to 9.1.3
Hi All,
We used 9.0.4 before and recently we plan to upgrade to 9.1.3. During
the test, we found a issue related to escape letter as below:
in 9.0.4, the sql is correct
insert into test values('abc\'a');
but in 9.1.3, the postgresql denied the same sql, then it worked after
I changed it to
insert into test values('abc''a');
How to configure 9.1.3 and let it also accept \ as the escpage? Could
someone help me out?
thanks very much.
--
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] Issue of upgrading from 9.0.4 to 9.1.3
On 04/12/12 11:09 PM, Zhidong She wrote:
but in 9.1.3, the postgresql denied the same sql, then it worked after
I changed it to
insert into test values('abc''a');
How to configure 9.1.3 and let it also accept \ as the escpage? Could
someone help me out?
use E'abc\'a', this is the SQL standard for escaped strings.
or even better, use $$ quoting, like $$abc'a$$
--
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] Issue of upgrading from 9.0.4 to 9.1.3
On Fri, Apr 13, 2012 at 11:39 AM, Zhidong She zhidong@gmail.com wrote:
Hi All,
We used 9.0.4 before and recently we plan to upgrade to 9.1.3. During
the test, we found a issue related to escape letter as below:
in 9.0.4, the sql is correct
insert into test values('abc\'a');
but in 9.1.3, the postgresql denied the same sql, then it worked after
I changed it to
insert into test values('abc''a');
How to configure 9.1.3 and let it also accept \ as the escpage? Could
someone help me out?
thanks very much.
-bash-4.1$ ./psql
psql (9.1.3)
Type help for help.
postgres=# select version();
version
--
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
20110731 (Red Hat 4.4.6-3), 64-bit
(1 row)
postgres=# select E'Raghav\'s';
?column?
--
Raghav's
(1 row)
or
change the parameter standard_confirming_strings to off; and retry your
example.
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.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] Two entries with the same primary key
Hi, our current version both on master and slave is PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit But as i remember, we start streaming replication on 9.1.2 and then upgrade to 9.1.3. My ops now on vacations, and we will make standby resync on Monday, and I'll check, if problem solved. Thanx you for answer. On Fri, Apr 13, 2012 at 00:48, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Apr 12, 2012 at 9:20 AM, Ivan Evtuhovich evtuhov...@gmail.com wrote: Hello, More then month ago we upgrade DB from 9.0 to 9.1 with pg_upgrade. Then we move DB to another server with standard pg streaming replication. Now we have two entries with the same primary key. And I do not know what to do. SELECT ctid, id from billing_invoices where id = 27362891; ctid |id --+-- (1112690,11) | 27362891 (1112438,26) | 27362891 Per some off-list conversation with Ivan, this is only happening on the standby. Ivan, what's the precise version of postgres you are using? When you first went to hs/sr? I bet your clog files are out of whack (and if so, probably upgrading to recent bugfix postgres and standby resync is the correct course of action). merlin
Re: [GENERAL] recommended schema diff tools?
Hi, about database schema migrations: I am very happy with south http://south.aeracode.org/ It is written for django (python web framework), but could be used for database migrations outside django, too. Thomas Güttler Am 12.04.2012 17:10, schrieb Chris Angelico: On Fri, Apr 13, 2012 at 12:57 AM, Welty, Richardrwe...@ltionline.com wrote: can anyone recommend an open source tool for diffing schemas? (it should go without saying that i'm looking for ddl to update production and QA DBs from development DBs, but i'll say it, just in case.) We toyed with this exact issue at work. In the end, we went the other direction, and created two files, both managed in source control: a .sql file with everything necessary to initialize the database from scratch, and a patch script. Every change gets done (by hand) to the primary .sql file, and the SQL statements needed to effect the transition (eg ALTER TABLE to add a column) get added to the patch script. ... -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] aggregate arrays
example:
a query returns a column that contains arrays:
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t;
column1 | column2
-+-
{1,2,3,3,4} | 1
{1,2,2,3,4} | 2
(2 rows)
and then we want aggregate that result.
example by column2:
WITH test AS (
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT array_agg(column2) column2 FROM test;
column2
-
{1,2}
(1 row)
and I want aggregate column1 arrays into one array. I want receive the
result:
column1| column2
--+-
{1,2,3,3,4,1,2,2,3,4} | {1,2}
I've tried the statement:
WITH test AS (
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT
array_agg(unnest(column1)) AS column1,
array_agg(column2) column2
FROM
test;
But I receive the error:
ERROR: set-valued function called in context that cannot accept a set
How can I aggregate arrays into one array?
--
. ''`. Dmitry E. Oboukhov
: :’ : email: un...@debian.org jabber://un...@uvw.ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
signature.asc
Description: Digital signature
Re: [GENERAL] Two entries with the same primary key
Hello Merlin, we've resynced slave and now everything is OK, thanks you for help. And only one last question, where to read about this bug, because my colleges want to know, what happens. On Fri, Apr 13, 2012 at 00:48, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Apr 12, 2012 at 9:20 AM, Ivan Evtuhovich evtuhov...@gmail.com wrote: Hello, More then month ago we upgrade DB from 9.0 to 9.1 with pg_upgrade. Then we move DB to another server with standard pg streaming replication. Now we have two entries with the same primary key. And I do not know what to do. SELECT ctid, id from billing_invoices where id = 27362891; ctid |id --+-- (1112690,11) | 27362891 (1112438,26) | 27362891 Per some off-list conversation with Ivan, this is only happening on the standby. Ivan, what's the precise version of postgres you are using? When you first went to hs/sr? I bet your clog files are out of whack (and if so, probably upgrading to recent bugfix postgres and standby resync is the correct course of action). merlin
Re: [GENERAL] Query optimization
Hi, On Wed, Apr 11, 2012 at 2:28 PM, niss...@wp.pl wrote: I'm looking for materials about query optimization in PostgreSQL. I read documentation but it doesnt contain much information. Can you recommend a good book or website when i could learn something about this? I think you can find some good info about it here http://wiki.postgresql.org/wiki/Performance_Optimization thanks in advance for your help -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray...@gmail.com Skype: gray-hemp -- 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] Value to long for type ....: Columnname missing
On Thu, Apr 12, 2012 at 12:16 PM, Thomas Guettler h...@tbz-pariv.de wrote: How can you report feature request? You can submit it here http://www.postgresql.org/support/submitbug/ Thomas Güttler -- Thomas Guettler, http://www.thomas-guettler.de/ E-Mail: guettli (*) thomas-guettler + de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray...@gmail.com Skype: gray-hemp -- 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] regexp operator for array element compares needed
On Thu, Apr 12, 2012 at 10:50 PM, Gauthier, Dave dave.gauth...@intel.com wrote: ... from 'a.,b.,c*,d*,ee' You can not do like this. Don't even know if this sort of thing is possible. I didn't see any operator like this in the docs. May have to split out each and compare in nested loops sith atomic regexp compare ~. Probably you will find the ltree extension useful http://www.postgresql.org/docs/9.1/interactive/ltree.html, though it does not provide fully functional regexps. -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray...@gmail.com Skype: gray-hemp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9.0 Streaming Replication and Load Balancing?
Hi! I have a Postgres 9.0 on CentOs 5.6 with a functional asynchronous streaming replication to a hot-standby slave (over a TCP connection). At the moment, the slave only makes the replication, but it accepts read-only queries. I need to load-balance the DB requests to both servers and was trying pgPool-II but it seems it has some problems if we already have connection pooling elsewhere. I have some application servers that already have their own connection pool functionalities and I wonder if anyone found a solution for that. Imagine I have some different deploys on a Glassfish or a Tomcat server, using the server connection pooling facilities and from that we would access the database, or the balancer. Has anyone managed to do this with pgPool-II? Any other options? Best regards, Paulo Correia -- 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] recommended schema diff tools?
Le 2012-04-12 10:57, Welty, Richard a écrit : can anyone recommend an open source tool for diffing schemas? (it should go without saying that i'm looking for ddl to update production and QA DBs from development DBs, but i'll say it, just in case.) thanks, richard Hello, Not free, but : - powerful - ease of use - nice gui - many more... EMS DB Comparer for PostgreSQL http://www.sqlmanager.net/en/products/postgresql/dbcomparer You can download and evaluate, sometimes it is worth the price. Hope that helps Bruno
[GENERAL] Updating pg_attribute to change field's data type from integer to bigint on very large table
Greetings, I have a very large table (approximately 1 billion records). I need to change a field's data type from integer to bigint. I started up an ALTER TABLE approach yesterday and it is still running (trying to rewrite the whole table?). I remember seeing mention of being able to do this in the pg_attribute table. Has anybody ever used the latter approach. If so, instructions would be greatly appreciated. Thanks... Jeff
Re: [GENERAL] aggregate arrays
Dmitry E. Oboukhov wrote:
example:
a query returns a column that contains arrays:
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t;
column1 | column2
-+-
{1,2,3,3,4} | 1
{1,2,2,3,4} | 2
(2 rows)
and then we want aggregate that result.
example by column2:
WITH test AS (
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT array_agg(column2) column2 FROM test;
column2
-
{1,2}
(1 row)
and I want aggregate column1 arrays into one array. I want receive the
result:
column1| column2
--+-
{1,2,3,3,4,1,2,2,3,4} | {1,2}
I've tried the statement:
WITH test AS (
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT
array_agg(unnest(column1)) AS column1,
array_agg(column2) column2
FROM
test;
But I receive the error:
ERROR: set-valued function called in context that cannot accept a set
How can I aggregate arrays into one array?
Create your own aggregate.
CREATE AGGREGATE array_union (anyarray) (SFUNC = array_cat, STYPE = anyarray);
WITH test AS (
select * FROM (VALUES (ARRAY[1,2,3,3,4], 1), (ARRAY[1,2,2,3,4], 2)) t
)
SELECT
array_union(column1) AS column1,
array_agg(column2) column2
FROM
test;
column1| column2
---+-
{1,2,3,3,4,1,2,2,3,4} | {1,2}
(1 row)
Yours,
Laurenz Albe
--
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 pg_attribute to change field's data type from integer to bigint on very large table
Jeff Adams jeff.ad...@noaa.gov writes: I have a very large table (approximately 1 billion records). I need to change a field's data type from integer to bigint. I started up an ALTER TABLE approach yesterday and it is still running (trying to rewrite the whole table?). I remember seeing mention of being able to do this in the pg_attribute table. Has anybody ever used the latter approach. If so, instructions would be greatly appreciated. Thanks... No, that is an actual on-disk change (making the field physically wider), so it's going to cost ya. There are some cases where the on-disk representation doesn't change and so a catalog update isn't needed, but int4-int8 isn't one of them. 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] Updating pg_attribute to change field's data type from integer to bigint on very large table
I wrote: No, that is an actual on-disk change (making the field physically wider), so it's going to cost ya. There are some cases where the on-disk representation doesn't change and so a catalog update isn't needed, but int4-int8 isn't one of them. Sheesh, hit send too quickly on that. Of course I meant and so a catalog update is all that's needed. 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] Updating pg_attribute to change field's data type from integer to bigint on very large table
so i can? if so, how do i go about? i should mention that, while i dabble in postgres dba activity, it is not my day job... On Fri, Apr 13, 2012 at 11:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: No, that is an actual on-disk change (making the field physically wider), so it's going to cost ya. There are some cases where the on-disk representation doesn't change and so a catalog update isn't needed, but int4-int8 isn't one of them. Sheesh, hit send too quickly on that. Of course I meant and so a catalog update is all that's needed. regards, tom lane -- Jeffrey D. Adams National Marine Fisheries Service Office of Protected Resources 1315 East West Hwy, Building SSMC3 Silver Spring, MD 20910-3282 phone: (301) 427-8434 fax: (301) 713-0376
Re: [GENERAL] Updating pg_attribute to change field's data type from integer to bigint on very large table
Hi Jeff, read carefully, he said it does stuff ON DISK, which means that all of your fields must be physically remade (so it a lot more than telling it it's an int4, Sheila, which is what just modifying the catalog would do). Since you are not a dba be aware that you are producing a new record for each of the existing ones, and thus bloating things quite a bit. You may want to vacuum it asap :) Bèrto On 13 April 2012 16:30, Jeff Adams jeff.ad...@noaa.gov wrote: so i can? if so, how do i go about? i should mention that, while i dabble in postgres dba activity, it is not my day job... On Fri, Apr 13, 2012 at 11:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: No, that is an actual on-disk change (making the field physically wider), so it's going to cost ya. There are some cases where the on-disk representation doesn't change and so a catalog update isn't needed, but int4-int8 isn't one of them. Sheesh, hit send too quickly on that. Of course I meant and so a catalog update is all that's needed. regards, tom lane -- Jeffrey D. Adams National Marine Fisheries Service Office of Protected Resources 1315 East West Hwy, Building SSMC3 Silver Spring, MD 20910-3282 phone: (301) 427-8434 fax: (301) 713-0376 -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] Updating pg_attribute to change field's data type from integer to bigint on very large table
On 04/13/2012 08:30 AM, Jeff Adams wrote: so i can? if so, how do i go about? i should mention that, while i dabble in postgres dba activity, it is not my day job... That really depends on details and your concerns. Is the database used for constant insert/update/select activity or is it a big table used for analysis and can be taken offline for some period? Is the column you want to update a primary or foreign key? How much available disk space do you have? Is a large portion of the data static (historical logs)? Some possible approaches: 1. Just let it run to completion if you can afford the maintenance time. 2. Add a new column of the appropriate type, copy the data into that column then drop the old one and rename the new one. If you do the update all at once you will have severe table bloat but you may be able to do the updates of the new column in batches so that vacuum can reclaim space between update batches. This approach may be useful if you do not have enough maintenance time to do the change all at once. 3. Dump the table data. Truncate the table and modify the column definition. Restore the data. This requires downtime but will probably be faster than in-place modification. However it's not something that you can easily cancel part-way through and not a friendly method if there are foreign-keys involved. 4. Rename the table and create a new table with the structure you want. Copy the old data back into the new table - perhaps in batches. This might be useful if you need to constantly keep collecting data but can afford a delay in analysis of the data. If partitioning the table would be beneficial, this might be a good time to consider that as well. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Subselect with incorrect column not a syntax error?
Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike -- 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] Subselect with incorrect column not a syntax error?
On 4/13/2012 11:39 AM, Mike Blackwell wrote: Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike Your subquery is correlated with the outer query. So the c2 in the subquery is referring to table x. -- Jack Christensen ja...@hylesanderson.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] Updating pg_attribute to change field's data type from integer to bigint on very large table
Thanks for the ideas Steve. I am actually working with a partitioned table and the field I am modifying is the id field (I have reached the cap on the integer data type and need to modify it to bigint - very poor planning on my part!), but no related tables exist. The id field in the partitioned tables is inherited, so I figured I needed to alter the column in the parent table. Does this information point towards an optimal solution? Thanks again... On Fri, Apr 13, 2012 at 12:31 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 04/13/2012 08:30 AM, Jeff Adams wrote: so i can? if so, how do i go about? i should mention that, while i dabble in postgres dba activity, it is not my day job... That really depends on details and your concerns. Is the database used for constant insert/update/select activity or is it a big table used for analysis and can be taken offline for some period? Is the column you want to update a primary or foreign key? How much available disk space do you have? Is a large portion of the data static (historical logs)? Some possible approaches: 1. Just let it run to completion if you can afford the maintenance time. 2. Add a new column of the appropriate type, copy the data into that column then drop the old one and rename the new one. If you do the update all at once you will have severe table bloat but you may be able to do the updates of the new column in batches so that vacuum can reclaim space between update batches. This approach may be useful if you do not have enough maintenance time to do the change all at once. 3. Dump the table data. Truncate the table and modify the column definition. Restore the data. This requires downtime but will probably be faster than in-place modification. However it's not something that you can easily cancel part-way through and not a friendly method if there are foreign-keys involved. 4. Rename the table and create a new table with the structure you want. Copy the old data back into the new table - perhaps in batches. This might be useful if you need to constantly keep collecting data but can afford a delay in analysis of the data. If partitioning the table would be beneficial, this might be a good time to consider that as well. Cheers, Steve -- Jeffrey D. Adams National Marine Fisheries Service Office of Protected Resources 1315 East West Hwy, Building SSMC3 Silver Spring, MD 20910-3282 phone: (301) 427-8434 fax: (301) 713-0376
[GENERAL] Tab completion not working on OSX Lion (10.7.3)
Prior to updating my Snow Leopard Mac to OSX Lion (10.7.3), I was able to open psql from a terminal and then use tab complete to auto-complete table or field names (i.e., select * from [TAB -- list of table names]). Unfortunately, something appears to have changed and tab complete now does nothing. Does anyone know of a fix for this problem?
[GENERAL] Problems with restoring data (using pg_restore)
Hi guys, I have a script exporting a series of schemas from one database (test) to another database (production). I´ve noticed that some of the tables are not correctly updated resulting in old datas in the productiondb. I´ve looked in the logfiles and noticed the following: CMD: E:\spatialsuite\app\postgres\8.4.6\pgsql\bin\pg_re store -c -h localhost -p 5432 -U k175 -d k175 E:\spatialsuite\sites\webgis\import\db\k175_test.r k_grundkort.dmp pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3344; 2606 1411043 CONSTRAINT husnr_pk k175_test pg_restore: [archiver (db)] could not execute query: ERROR: constraint husnr_pk of relation husnr does not exist Command was: ALTER TABLE ONLY rk_grundkort.husnr DROP CONSTRAINT husnr_pk; pg_restore: [archiver (db)] Error from TOC entry 2975; 1259 1411038 TABLE husnr k175_test pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop table husnr because other objects depend on it DETAIL: view husnr_view depends on table husnr HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP TABLE rk_grundkort.husnr; pg_restore: [archiver (db)] Error from TOC entry 10; 2615 25698 SCHEMA rk_grundkort k175_test pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema rk_grundkort because other objects depend on it DETAIL: table husnr depends on schema rk_grundkort view husnr_view depends on schema rk_grundkort HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA rk_grundkort; pg_restore: [archiver (db)] could not execute query: ERROR: schema rk_grundkort already exists Command was: CREATE SCHEMA rk_grundkort; pg_restore: [archiver (db)] Error from TOC entry 2975; 1259 1411038 TABLE husnr k175_test pg_restore: [archiver (db)] could not execute query: ERROR: relation husnr already exists Command was: CREATE TABLE husnr ( ogc_fid integer NOT NULL, wkb_geometry public.geometry, adr_id character(11), komnr int... pg_restore: [archiver (db)] Error from TOC entry 3355; 0 1411038 TABLE DATA husnr k175_test pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint husnr_pkey CONTEXT: COPY husnr, line 1: 1 010120E864295C8F0277E0254191ED7C27178F5741 1750702333 175 Rødovre Kommune ... pg_restore: [archiver (db)] Error from TOC entry 3344; 2606 1411043 CONSTRAINT husnr_pk k175_test pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for table husnr are not allowed Command was: ALTER TABLE ONLY husnr ADD CONSTRAINT husnr_pk PRIMARY KEY (ogc_fid); WARNING: errors ignored on restore: 7 The pg_dump command is: pg_dump -h localhost -p 5432 -U k175_test -Ft -v -c -x -O -n rk_grundkort -f E:\spatialsuite\sites\test-webgis\export2webgis\db\k175_test.rk_grundkort.dmp k175_test I´ve an idea that the problem could be related to the -n option. Could someone guide me in the right direction on how to solve this problem? Thanks Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems with restoring data (using pg_restore)
Hi guys, I have a script exporting a series of schemas from one database (test) to another database (production). I´ve noticed that some of the tables are not correctly updated resulting in old datas in the productiondb. I´ve looked in the logfiles and noticed the following: CMD: E:\spatialsuite\app\postgres\8.4.6\pgsql\bin\pg_re store -c -h localhost -p 5432 -U k175 -d k175 E:\spatialsuite\sites\webgis\import\db\k175_test.r k_grundkort.dmp pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3344; 2606 1411043 CONSTRAINT husnr_pk k175_test pg_restore: [archiver (db)] could not execute query: ERROR: constraint husnr_pk of relation husnr does not exist Command was: ALTER TABLE ONLY rk_grundkort.husnr DROP CONSTRAINT husnr_pk; pg_restore: [archiver (db)] Error from TOC entry 2975; 1259 1411038 TABLE husnr k175_test pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop table husnr because other objects depend on it DETAIL: view husnr_view depends on table husnr HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP TABLE rk_grundkort.husnr; pg_restore: [archiver (db)] Error from TOC entry 10; 2615 25698 SCHEMA rk_grundkort k175_test pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema rk_grundkort because other objects depend on it DETAIL: table husnr depends on schema rk_grundkort view husnr_view depends on schema rk_grundkort HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA rk_grundkort; pg_restore: [archiver (db)] could not execute query: ERROR: schema rk_grundkort already exists Command was: CREATE SCHEMA rk_grundkort; pg_restore: [archiver (db)] Error from TOC entry 2975; 1259 1411038 TABLE husnr k175_test pg_restore: [archiver (db)] could not execute query: ERROR: relation husnr already exists Command was: CREATE TABLE husnr ( ogc_fid integer NOT NULL, wkb_geometry public.geometry, adr_id character(11), komnr int... pg_restore: [archiver (db)] Error from TOC entry 3355; 0 1411038 TABLE DATA husnr k175_test pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint husnr_pkey CONTEXT: COPY husnr, line 1: 1 010120E864295C8F0277E0254191ED7C27178F5741 1750702333 175 Rødovre Kommune ... pg_restore: [archiver (db)] Error from TOC entry 3344; 2606 1411043 CONSTRAINT husnr_pk k175_test pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for table husnr are not allowed Command was: ALTER TABLE ONLY husnr ADD CONSTRAINT husnr_pk PRIMARY KEY (ogc_fid); WARNING: errors ignored on restore: 7 The pg_dump command is: pg_dump -h localhost -p 5432 -U k175_test -Ft -v -c -x -O -n rk_grundkort -f E:\spatialsuite\sites\test-webgis\export2webgis\db\k175_test.rk_grundkort.dmp k175_test I´ve an idea that the problem could be related to the -n option. Could someone guide me in the right direction on how to solve this problem? Thanks Christian -- 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] Value to long for type ....: Columnname missing
On Fri, 2012-04-13 at 17:19 +0400, Sergey Konoplev wrote: On Thu, Apr 12, 2012 at 12:16 PM, Thomas Guettler h...@tbz-pariv.de wrote: How can you report feature request? You can submit it here http://www.postgresql.org/support/submitbug/ No, this allows you to report a bug, not a feature request. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Subselect with incorrect column not a syntax error?
On Fri, Apr 13, 2012 at 11:59 AM, Jack Christensen ja...@hylesanderson.edu wrote: On 4/13/2012 11:39 AM, Mike Blackwell wrote: Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike Your subquery is correlated with the outer query. So the c2 in the subquery is referring to table x. This is a good example of why one should always use a table alias prefix when using subqueries. -- 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] Value to long for type ....: Columnname missing
On Thu, Apr 12, 2012 at 2:16 AM, Thomas Guettler h...@tbz-pariv.de wrote: Hi, I think it would be very good, if postgresql reports which column is too small: Value to long for type character varying(1024) (message translated from german to english) Is there a reason not to report the column name? What version of pg are you using? I think later versions do report the column. -- 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] Subselect with incorrect column not a syntax error?
Indeed. __ Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com On Fri, Apr 13, 2012 at 13:34, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Apr 13, 2012 at 11:59 AM, Jack Christensen ja...@hylesanderson.edu wrote: On 4/13/2012 11:39 AM, Mike Blackwell wrote: Could someone please explain to me why the following select does not result in a syntax error? (9.0.3) begin; create table x( c1 integer , c2 integer); create table y( c3 integer, c4 integer); select * from x where c2 in ( select c2 from y where c4 = 2 ); rollback; Mike Your subquery is correlated with the outer query. So the c2 in the subquery is referring to table x. This is a good example of why one should always use a table alias prefix when using subqueries. -- 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] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 2:59 PM, Raghavendra raghavendra@enterprisedb.com wrote: . Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt Seems you already did this. I believe you are connecting as postgres user not from root, if yes, then check .pgpass file too (it will be in postgres user home directory). Thanks, but this is not helping. I have the exact same info in three files: 1. The root .pgpass 2. The .pgpass for the postgres user 3. The authfile specified inside pgbouncer -- /var/lib/pgsql/pgbouncer.txt Now what? It's not telling me that the user is unrecognized, so it's likely not that md5 or trust or plain password issue. Pgbouncer is not even recognizing the user! What now? -- 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] Tab completion not working on OSX Lion (10.7.3)
On 04/13/2012 10:27 AM, Bryan Hughes wrote: Prior to updating my Snow Leopard Mac to OSX Lion (10.7.3), I was able to open psql from a terminal and then use tab complete to auto-complete table or field names (i.e., select * from [TAB -- list of table names]). Unfortunately, something appears to have changed and tab complete now does nothing. Does anyone know of a fix for this problem? Not familiar with PostgreSQL on Mac but I believe that tab-complete requires readline support so perhaps you ended up with a psql without readline or you are missing some necessary libraries. Cheers, Steve -- 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] Tab completion not working on OSX Lion (10.7.3)
Bryan Hughes huu...@gmail.com writes: Prior to updating my Snow Leopard Mac to OSX Lion (10.7.3), I was able to open psql from a terminal and then use tab complete to auto-complete table or field names (i.e., select * from [TAB -- list of table names]). Unfortunately, something appears to have changed and tab complete now does nothing. Does anyone know of a fix for this problem? Apple broke this (again, or should I say worse) in their Lion update of libedit. There's discussion of that in the PG archives. On track record so far, nothing will be done about it before Mountain Lion, and that version will have an all-new set of bugs instead. If you're using psql to any significant extent on OS X, I'd recommend installing GNU libreadline and then building a copy of psql linked to that. (Note: /usr/lib/libreadline.dylib is *not* GNU readline, despite the name; it's merely a symlink to libedit.) 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] Tab completion not working on OSX Lion (10.7.3)
On 04/13/12 12:07 PM, Tom Lane wrote: Apple broke this (again, or should I say worse) in their Lion update of libedit. libedit has a long and nasty track record of being quite buggy. I was using it briefly on a couple Unix systems we were trying to avoid gnupollution on and found it was way easy to coredump psql just by hitting keys too fast. meh. -- 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] Two entries with the same primary key
On Fri, Apr 13, 2012 at 7:36 AM, Ivan Evtuhovich evtuhov...@gmail.com wrote: Hello Merlin, we've resynced slave and now everything is OK, thanks you for help. And only one last question, where to read about this bug, because my colleges want to know, what happens. there are several standby related issues fixed: see release notes here: http://www.postgresql.org/docs/9.1/interactive/release.html for what I was thinking might have got you (which was just a wild guess and may have nothing to do with your actual issue -- just did a quick re-read), google the thread Hot Backup with rsync fails at pg_clog if under load merlin -- 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] Tab completion not working on OSX Lion (10.7.3)
John R Pierce pie...@hogranch.com writes: On 04/13/12 12:07 PM, Tom Lane wrote: Apple broke this (again, or should I say worse) in their Lion update of libedit. libedit has a long and nasty track record of being quite buggy. I was using it briefly on a couple Unix systems we were trying to avoid gnupollution on and found it was way easy to coredump psql just by hitting keys too fast. meh. Yeah, to be fair the bug in question is upstream's, not Apple's. I'm just griping because they seem to grab random snapshots of upstream's SCM and release them with hardly any testing. It would possibly be all right if they didn't then sit on that release for an entire OS major version cycle :-( 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] Tab completion not working on OSX Lion (10.7.3)
On 04/13/12 1:16 PM, Tom Lane wrote: Yeah, to be fair the bug in question is upstream's, not Apple's. I'm just griping because they seem to grab random snapshots of upstream's SCM and release them with hardly any testing. It would possibly be all right if they didn't then sit on that release for an entire OS major version cycle :-( hey, noone on Apple does any typing of commands, thats so 20th century. -- 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] Preventing an 'after' trigger from causing rollback on error
Is there any way I can stop a trigger which fires after a row is inserted into a table from causing a rollback of the entire transaction if something goes wrong? I have rows being written to a table, and I have triggers which calculate and update statistics automatically in stats tables based on the rows inserted. However, if something goes wrong in that trigger, right now it causes all the inserts to roll back, as well as the stats calculations. I would rather keep all the inserts, but just fail the stats calculates. I can always rematerialize the entire stats table with another procedure if all the rows are present. Thanks in advance for any assistance. -- Eliot Gable
Re: [GENERAL] Preventing an 'after' trigger from causing rollback on error
On Fri, 2012-04-13 at 17:58 -0400, Eliot Gable wrote: Is there any way I can stop a trigger which fires after a row is inserted into a table from causing a rollback of the entire transaction if something goes wrong? 1. Try using subtransactions ( http://www.postgresql.org/docs/9.1/static/sql-savepoint.html ). I suggest releasing or rolling back the savepoints that you no longer need because triggers can be executed many times. 2. You could have a separate connection that does the processing you need, and use LISTEN/NOTIFY to alert the other connection that new data is available to process. Regards, Jeff Davis -- 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] Preventing an 'after' trigger from causing rollback on error
On Fri, Apr 13, 2012 at 3:58 PM, Eliot Gable egable+pgsql-gene...@gmail.com wrote: Is there any way I can stop a trigger which fires after a row is inserted into a table from causing a rollback of the entire transaction if something goes wrong? Take look here: http://www.postgresql.org/docs/9.0/static/plpgsql-porting.html Specifically the part about: BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); EXCEPTION WHEN unique_violation THEN (2) -- don't worry if it already exists END; -- 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 all the schema and the privileges which is granted to users.THanks.
how to list all the schema and the privileges which is granted to users.THanks. I know how to list all the objects privileges : \dp But I don't know how to list the schema's privileges?? Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-list-all-the-schema-and-the-privileges-which-is-granted-to-users-THanks-tp5639852p5639852.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 all the schema and the privileges which is granted to users.THanks.
On Sat, Apr 14, 2012 at 6:47 AM, leaf_yxj leaf_...@163.com wrote:
how to list all the schema and the privileges which is granted to
users.THanks.
I know how to list all the objects privileges :
\dp
But I don't know how to list the schema's privileges??
Schema Privileges:
postgres=# \dn+
List of schemas
Name| Owner | Access privileges |
Description
+--+-+--
information_schema | postgres | {postgres=UC/postgres,=U/postgres} |
pg_catalog | postgres | {postgres=UC/postgres,=U/postgres} |
system catalog schema
pg_toast | postgres | |
reserved schema for TOAST tables
pg_toast_temp_1| postgres | |
public | postgres | {postgres=UC/postgres,=UC/postgres} |
standard public schema
(5 rows)
--
Thanks Regards,
Raghu Ram
EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] Tab completion not working on OSX Lion (10.7.3)
I now have libreadline.a in /usr/local/lib. Assuming that's the goal, would you be kind enough to walk me through the next step -- linking that lib to psql? bryan On Fri, Apr 13, 2012 at 3:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bryan Hughes huu...@gmail.com writes: Prior to updating my Snow Leopard Mac to OSX Lion (10.7.3), I was able to open psql from a terminal and then use tab complete to auto-complete table or field names (i.e., select * from [TAB -- list of table names]). Unfortunately, something appears to have changed and tab complete now does nothing. Does anyone know of a fix for this problem? Apple broke this (again, or should I say worse) in their Lion update of libedit. There's discussion of that in the PG archives. On track record so far, nothing will be done about it before Mountain Lion, and that version will have an all-new set of bugs instead. If you're using psql to any significant extent on OS X, I'd recommend installing GNU libreadline and then building a copy of psql linked to that. (Note: /usr/lib/libreadline.dylib is *not* GNU readline, despite the name; it's merely a symlink to libedit.) regards, tom lane
Re: [GENERAL] Tab completion not working on OSX Lion (10.7.3)
Bryan Hughes huu...@gmail.com writes: I now have libreadline.a in /usr/local/lib. Assuming that's the goal, would you be kind enough to walk me through the next step -- linking that lib to psql? I think it should work to just configure and build postgres the same as you otherwise would --- I believe Apple's gcc follows the usual convention of looking in /usr/local first. You can check the results with otool -L psql. If it mentions /usr/lib/libedit.dylib or /usr/lib/libreadline.dylib, you still have a dependency on libedit, else not. 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] Error with compile source code on CentOS5.7
Hi, all I have some errors when I compiling PostgreSQL9.1.3's source code (9.1.2 is also same as) on CentOS5.7(x86_64). The above are detail infomations. -- $ uname -a Linux centos57 2.6.18-274.el5 #1 SMP Fri Jul 22 04:43:29 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux $ make make -C src all : : : gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv pg_dump.o common.o pg_dump_sort.o keywords.o kwlookup.o pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o pg_backup_files.o pg_backup_null.o pg_backup_tar.o pg_backup_directory.o dumputils.o compress_io.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,'/usr/local/pgsql913/lib',--enable-new-dtags -lpgport -lz -lreadline -ltermcap -lcrypt -ldl -lm -o pg_dump compress_io.o: In function `cfgetc': compress_io.c:(.text+0xf5): undefined reference to `gzgetc_' -- Could someone guide me in the right direction on how to solve this problem? Thanks -- go.dbms go.d...@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] Error with compile source code on CentOS5.7
go.dbms go.d...@gmail.com writes: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv pg_dump.o common.o pg_dump_sort.o keywords.o kwlookup.o pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o pg_backup_files.o pg_backup_null.o pg_backup_tar.o pg_backup_directory.o dumputils.o compress_io.o -L../../../src/port -lpgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,'/usr/local/pgsql913/lib',--enable-new-dtags -lpgport -lz -lreadline -ltermcap -lcrypt -ldl -lm -o pg_dump compress_io.o: In function `cfgetc': compress_io.c:(.text+0xf5): undefined reference to `gzgetc_' Hm, where'd that underscore come from? What PG is calling is gzgetc(). I suspect what you've got here is a mismatch between include files and the actual zlib library. Check if you've got more than one version of zlib installed. 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
