Re: [GENERAL] Strange problem with create table as select * from table;

hubert depesz lubaczewski writes: > On Fri, Nov 04, 2011 at 05:49:44PM -0400, Tom Lane wrote: >> You said that pg_dump does not show the corruption. That could be >> because the data is coming out through the COPY code path instead of >> the SELECT code path. Could you try a pg_dump with --inser

Re: [GENERAL] Strange problem with create table as select * from table;

On Fri, Nov 04, 2011 at 05:49:44PM -0400, Tom Lane wrote: > You said that pg_dump does not show the corruption. That could be > because the data is coming out through the COPY code path instead of > the SELECT code path. Could you try a pg_dump with --inserts (which > will fetch the data with SEL

Re: [GENERAL] Strange problem with create table as select * from table;

On Fri, Nov 04, 2011 at 06:18:55PM -0400, Tom Lane wrote: > BTW, did you try the separate INSERT/SELECT yet? Does that show > corruption? pg_dump --inserts is still working. i did create table (like), insert into ... select and it also shows the problem, as I showed (with other data) in email: 2

[GENERAL] PostgreSQL table history tracking

Dear PostgreSQL users, I have created set of functions functions which adds possibility to store full editing history of Your database tables, recover its state to any time, visualize diffs and place tags to mark particular table state. I would be very happy, if somebody will make a try and/or rev

Re: [GENERAL] Strange problem with create table as select * from table;

I wrote: > Good detective work. So now we at least have a believable theory about > *what* is happening (something is stomping the first 8 data bytes of > these particular rows), if not *why*. Scratch that: something is stomping the first *six* bytes of data. On a hunch I converted the original a

[GENERAL] inconsistent interval normalization

hi all, pgsql version: 9.0.5 intervalstyle: postgres I am stumped why I am seeing inconsistent interval normalization with a given query. select date_trunc('week', datetime_submitted), avg(datetime_modified - datetime_submitted) FROM interval_test group by 1 order by 1; returned rows that d

Re: [GENERAL] Strange problem with create table as select * from table;

On Fri, Nov 04, 2011 at 05:49:44PM -0400, Tom Lane wrote: > You said that pg_dump does not show the corruption. That could be > because the data is coming out through the COPY code path instead of > the SELECT code path. Could you try a pg_dump with --inserts (which > will fetch the data with SEL

Re: [GENERAL] Strange problem with create table as select * from table;

hubert depesz lubaczewski writes: > OK. So based on it all, it looks like for some rows, first two columns got > mangled. Good detective work. So now we at least have a believable theory about *what* is happening (something is stomping the first 8 data bytes of these particular rows), if not *w

Re: [GENERAL] Strange problem with create table as select * from table;

On 11/04/2011 01:47 PM, hubert depesz lubaczewski wrote: On Fri, Nov 04, 2011 at 01:43:55PM -0700, Adrian Klaver wrote: Does it tell you anything? You are very thorough. I hate mysteries. Especially the ones that break stuff. Know the feeling. I don't know enough about Postgres internal

Re: [GENERAL] Strange problem with create table as select * from table;

On Fri, Nov 04, 2011 at 01:43:55PM -0700, Adrian Klaver wrote: > >Does it tell you anything? > You are very thorough. I hate mysteries. Especially the ones that break stuff. > I don't know enough about Postgres internals to be much help there. > All I can point out is the problem seemed to appear

Re: [GENERAL] Strange problem with create table as select * from table;

On 11/04/2011 01:17 PM, hubert depesz lubaczewski wrote: On Thu, Nov 03, 2011 at 11:03:45PM +0100, hubert depesz lubaczewski wrote: looking for some other info. will post as soon as i'll gather it, but that will be in utc morning :( I looked closer at the rows that got -1 xobject_id. Does

Re: [GENERAL] Strange problem with create table as select * from table;

On Thu, Nov 03, 2011 at 11:03:45PM +0100, hubert depesz lubaczewski wrote: > looking for some other info. will post as soon as i'll gather it, but > that will be in utc morning :( I looked closer at the rows that got -1 xobject_id. $ select magic_id, count(*) from qqq where xobject_id = -1 group

Re: [GENERAL] psql is too slow to connect

On 11/04/2011 12:08 PM, Tom Lane wrote: > "Ing.Edmundo.Robles.Lopez" writes: >> Hi, I have a problem with psql, is very slow to connect. I Checked the >> status of my network and the server and the client respond ok. > > First thing that comes to mind is DNS lookup problems. It's hard to > spe

Re: [GENERAL] Explicitly adding a table to a schema.

On Fri, 2011-11-04 at 14:32 -0400, Demitri Muna wrote: > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > > > You can use ALTER TABLE > > (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > > the schema of existing tables: > > ALTER TABLE foo SET SCHEMA bar >

Re: [GENERAL] Explicitly adding a table to a schema.

2011/11/4 Demitri Muna : > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > >> You can use ALTER TABLE >> (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set >> the schema of existing tables: >> ALTER TABLE foo SET SCHEMA bar > > Thanks. I did try that, but that co

Re: [GENERAL] Explicitly adding a table to a schema.

On Fri, Nov 4, 2011 at 2:32 PM, Demitri Muna wrote: > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > > > You can use ALTER TABLE ( > http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > the schema of existing tables: > > ALTER TABLE foo SET SCHEMA bar > > Thanks.

Re: [GENERAL] Explicitly adding a table to a schema.

Hi, On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > You can use ALTER TABLE > (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > the schema of existing tables: > ALTER TABLE foo SET SCHEMA bar Thanks. I did try that, but that command moves the table to a different s

Re: [GENERAL] 9.1 replication on different arch

El día 4 de noviembre de 2011 13:15, Scott Marlowe escribió: > > I'd install postgresql in a 32 bit VM then. > We're looking into it. Look's like the only option available for now, at least for using WAL replication. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programad

Re: [GENERAL] Excessive planner time for some queries with high statistics

Stuart Bishop writes: > We also found this problem did not occur on one of our staging > systems, which had a default statistics target of 100. Lowering the > statistics on the relavant columns from 1000 to 100 and reanalyzing > made the overhead unnoticeable. eqjoinsel() is O(N^2) in the number

Re: [GENERAL] psql is too slow to connect

"Ing.Edmundo.Robles.Lopez" writes: > Hi, I have a problem with psql, is very slow to connect. I Checked the > status of my network and the server and the client respond ok. First thing that comes to mind is DNS lookup problems. It's hard to speculate more than that on such little information.

Re: [GENERAL] Explicitly adding a table to a schema.

On Fri, Nov 4, 2011 at 1:57 PM, wrote: > Hi, > > I have a database where I wasn't explicitly using schemas when I started > it (i.e. everything was simply under "public"). I've since created several > schemas and renamed the "public" schema to something else. When I look at > the definitions (in

Re: [GENERAL] Dump Error Message

"Bob Pawley" writes: > I am attempting to dump a database using PostgreDAC. > I am getting the following error message which I don’t understand. > Can someone shed some light on this? > Error message from server: ERROR: column "tgisconstraint" does not exist The pg_trigger.tgisconstraint co

[GENERAL] Explicitly adding a table to a schema.

Hi, I have a database where I wasn't explicitly using schemas when I started it (i.e. everything was simply under "public"). I've since created several schemas and renamed the "public" schema to something else. When I look at the definitions (in PGAdmin III), the CREATE statement for the old ta

[GENERAL] psql is too slow to connect

Hi, I have a problem with psql, is very slow to connect. I Checked the status of my network and the server and the client respond ok. Any other ideas? Perhaps, should i need review, connections, shared memory, or if a big table is being accessed?? El contenido de este correo electrónico y

Re: [GENERAL] Dump Error Message

On 11/04/11 10:22 AM, Bob Pawley wrote: I am attempting to dump a database using PostgreDAC. this postgresDAC? http://www.microolap.com/products/connectivity/postgresdac/ thats a commercial product, you probably should contact them for support. -- john r pierceN 3

[GENERAL] Dump Error Message

Hi I am attempting to dump a database using PostgreDAC. I am getting the following error message which I don’t understand. Can someone shed some light on this? “Error message from server: ERROR: column "tgisconstraint" does not exist LINE 1: ...c AS tgfname, tgtype, tgnargs, tgargs, tgenabled

Re: [GENERAL] Replication Across Two Servers?

Carlos, I would recommend you simply stick with Debian 6 and add the debian backports repository. Add the following to your /etc/apt/sources.list: deb http://backports.debian.org/debian-backports squeeze-backports main After adding that just do an 'apt-get update' and you will be able to in

Re: [GENERAL] 9.1 replication on different arch

2011/11/4 Hannes Erven : > 2011-11-04 16:24, Martín Marqués: >>> Have a look at "dpkg --force-architecture" . >> >> The thing is that perl needs libdbd-pg-perl to connect, which needs >> libpq5, all this in amd64, but the i386 of postgresql-9.1 needs an >> i386 version of libpq5 > > Oh, I see, that

Re: [GENERAL] Replication Across Two Servers?

On Fri, Nov 4, 2011 at 9:59 AM, Carlos Mennens wrote: > > On Fri, Nov 4, 2011 at 11:56 AM, Prashant Bharucha > wrote: >> >> Hi Carlos >> >> Use Slony "master to multiple slaves" replication system for PostgreSQL >> supporting cascading (e.g. - a node can feed another node which feeds >> anothe

Re: [GENERAL] Foreign Keys and Deadlocks

On Thu, Nov 03, 2011 at 03:30:20PM -0700, David Kerr wrote: - Howdy, - - We have a process that's deadlocking frequently. It's basically multiple threads inserting data into a single table. - - That table has FK constraints to 3 other tables. - - I understand how an FK check will cause a share

Re: [GENERAL] Replication Across Two Servers?

On Fri, Nov 4, 2011 at 11:56 AM, Prashant Bharucha < prashantbharu...@yahoo.ca> wrote: > Hi Carlos > > Use Slony "master to multiple slaves" replication system for PostgreSQL > supporting cascading (*e.g.* - a node can > feed another node which feeds another node...) a

Re: [GENERAL] Replication Across Two Servers?

On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps wrote: > Carlos, > > Streaming replication was introduced in PostgreSQL 9.0 and should do what > you want. > > http://wiki.postgresql.org/wiki/Streaming_Replication Oh great! I didn't see that in the 8.4 manual since that is what Debian 6 has as the

Re: [GENERAL] Replication Across Two Servers?

Carlos, Streaming replication was introduced in PostgreSQL 9.0 and should do what you want. http://wiki.postgresql.org/wiki/Streaming_Replication On 11/04/2011 11:47 AM, Carlos Mennens wrote: We had a 8.4.8 production server of PostgreSQL on a Dell blade server which ran for 3 years fine. T

[GENERAL] Replication Across Two Servers?

We had a 8.4.8 production server of PostgreSQL on a Dell blade server which ran for 3 years fine. The server housed all our database needs perfectly but sadly the entire machine died. The drives were dead and the motherboard was fried but we did have daily full backups of the entire machine. Today

Re: [GENERAL] 9.1 replication on different arch

2011-11-04 16:24, Martín Marqués: >> Have a look at "dpkg --force-architecture" . > > The thing is that perl needs libdbd-pg-perl to connect, which needs > libpq5, all this in amd64, but the i386 of postgresql-9.1 needs an > i386 version of libpq5 Oh, I see, that's a mess. Probably there really i

Re: [BULK] Re: [GENERAL] Streaming Replication woes

On 11/04/2011 11:31 AM, Simon Riggs wrote: On Fri, Nov 4, 2011 at 2:56 PM, Raghavendra wrote: # The standby server must have superuser access privileges. host replication postgres 192.168.0.20/22 trust I strongly recommend you don't use those settings, since they result in no security at

Re: [GENERAL] Streaming Replication woes

On 11/04/2011 11:25 AM, Thom Brown wrote: On 4 November 2011 17:19, Sean Patronis wrote: On 11/04/2011 10:59 AM, Thom Brown wrote: On 4 November 2011 16:50, Sean Patroniswrote: I am running Postgres 9.1 I have followed the howto here: http://wiki.postgresql.org/wiki/Streaming_Replication

Re: [GENERAL] Streaming Replication woes

On Fri, Nov 4, 2011 at 2:56 PM, Raghavendra wrote: > # The standby server must have superuser access privileges. > host replication postgres 192.168.0.20/22 trust I strongly recommend you don't use those settings, since they result in no security at all. It won't block you from getting repl

Re: [GENERAL] Streaming Replication woes

On 4 November 2011 17:19, Sean Patronis wrote: > On 11/04/2011 10:59 AM, Thom Brown wrote: >> >> On 4 November 2011 16:50, Sean Patronis  wrote: >>> >>> I am running Postgres 9.1 >>> >>> I have followed the howto here: >>> http://wiki.postgresql.org/wiki/Streaming_Replication >>> >>> I am attempti

Re: [GENERAL] 9.1 replication on different arch

2011/11/4 Hannes Erven : > Am 2011-11-03 02:40, schrieb Martín Marqués: >> >> Sad thing is that it's not so easy on Debian. With Fedora all I had to >> do is select the arch type and that's all. > > Have a look at "dpkg --force-architecture" . I'm having a lot of trouble with this. The server has

Re: [GENERAL] Recommendations for SSDs in production?

On 11/4/2011 8:26 AM, Yeb Havinga wrote: First, if your'e interested in doing a test like this yourself, I'm testing on ubuntu 11.10, but even though this is a brand new distribution, the smart database was a few months old. 'update-smart-drivedb' had as effect that the names of the values t

Re: [GENERAL] Streaming Replication woes

On 11/04/2011 10:59 AM, Thom Brown wrote: On 4 November 2011 16:50, Sean Patronis wrote: I am running Postgres 9.1 I have followed the howto here: http://wiki.postgresql.org/wiki/Streaming_Replication I am attempting to replicate an existing database. On the Master, I get the following error

Re: [GENERAL] Streaming Replication woes

* * On Fri, Nov 4, 2011 at 8:20 PM, Sean Patronis wrote: > I am running Postgres 9.1 > > I have followed the howto here: > http://wiki.postgresql.org/wiki/Streaming_Replication > > I am attempting to replicate an existing database. > > On the Master, I get the following error in the postgres log

[GENERAL] Streaming Replication woes

I am running Postgres 9.1 I have followed the howto here: http://wiki.postgresql.org/wiki/Streaming_Replication I am attempting to replicate an existing database. On the Master, I get the following error in the postgres log file: FATAL: must be replication role to start walsender On the sla

Re: [GENERAL] Recommendations for SSDs in production?

On 2011-11-04 04:21, Kurt Buff wrote: Oddly enough, Tom's Hardware has a review of the Intel offering today - might be worth your while to take a look at it. Kurt Thanks for that link! Seeing media wearout comparisons between 'consumer grade' and 'enterprise' disks was enough for me to stop t

Re: [GENERAL] equivalent to "replication_timeout" on standby server

Thanks Fuji for that I hint... I searched around on the internet for that trick and it looks like we can make the Standby close its connection to the master much earlier than it otherwise would;it is good for me now. But still there seems to be two problem areas that can be improved over time...

Re: [GENERAL] Memory Issue

Hi Scott, I followed your advise and I run the test with the changes suggested at points 1,2 and 3 below and my performance test run for 18 hours without swapping. I did have a 40% drop in performance but I think that is a different problem. I will run more tests and post the results if anyone

Re: [GENERAL] 9.1 replication on different arch

Am 2011-11-03 02:40, schrieb Martín Marqués: > > Sad thing is that it's not so easy on Debian. With Fedora all I had to > do is select the arch type and that's all. Have a look at "dpkg --force-architecture" . -hannes -- Sent via pgsql-general mailing list (pgsql-general@postgresql.or

Re: [GENERAL] ERROR from pg_restore - From OS X to Ubuntu

Got it. Thank you very much! On Fri, Nov 4, 2011 at 2:06 AM, Albe Laurenz wrote: > Naoko Reeves wrote: > > I dumped from: > > [...] PostgreSQL 9.0.4 [...] > > > to: > > [...] PostgreSQL 9.1.1 [...] > > > During the restoration I got the following errors: > > > > ERROR: could not access file "$li

Re: [GENERAL] Hint for a query

> I have this tables > > > Table: Contact > IdContact > First Name > Second Name > … other columns > > Table: Employee > IdEmployee > IdContact, related to Contact table > … other columns > > Table: Salesman > IdSaleman > IdEmployee, if salesman is employee, related to Employee table > IdCont

Re: [GENERAL] Recommendations for SSDs in production?

Am 03.11.2011 18:59, schrieb Robert Treat: On Wed, Nov 2, 2011 at 11:02 PM, Benjamin Smith wrote: On Wednesday, November 02, 2011 11:39:25 AM Thomas Strunz wrote: I guess go Intel route or some other crazy expensive enterprise stuff. It's advice about some of the "crazy expensive enterprise

Re: [GENERAL] ERROR from pg_restore - From OS X to Ubuntu

Naoko Reeves wrote: > I dumped from: > [...] PostgreSQL 9.0.4 [...] > to: > [...] PostgreSQL 9.1.1 [...] > During the restoration I got the following errors: > > ERROR: could not access file "$libdir/targetinfo": No such file or directory > ERROR: function public.pldbg_get_target_info(text, "c

Re: [GENERAL] ERROR from pg_restore - From OS X to Ubuntu

On Fri, Nov 4, 2011 at 05:40, Naoko Reeves wrote: > I dumped from: > OS: OS X 10.5.8 > pg version: PostgreSQL 9.0.4 on x86_64-apple-darwin, compiled by GCC > i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit > Installation Method: EDB installer > to: > OS: Ubuntu 10.04.3 64

[GENERAL] Excessive planner time for some queries with high statistics

I'm seeing some queries, possibly to do with using a UNIQUE index, that have fast time reported by EXPLAIN ANALYZE but the actual time as reported by \timing at 150ms+ higher. PostgreSQL 8.4.9 Simple example queries: http://paste.ubuntu.com/726131/ Table definitions: http://paste.ubuntu.com/72619