Re: [GENERAL] Postgresql Database Lock Problem
The statement that creates lock and waiting is in following. This is from pg_stats and pg_stat_activity view. Here AD_Sequence is a table that maintains sequence number for all database objects. It automatically generates primary key value for all table insert. I think it also generates transaction related ID. For that reason it needs to update next sequence value for transaction ID. Statment: == SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF AD_Sequence AD_Sequence Table Definition = adempiere=# \d ad_sequence Table "adempiere.ad_sequence" Column |Type | Modifiers +-+-- ad_sequence_id | numeric(10,0) | not null ad_client_id | numeric(10,0) | not null ad_org_id | numeric(10,0) | not null isactive | character(1)| default 'Y'::bpchar created| timestamp without time zone | not null default now() createdby | numeric(10,0) | not null updated| timestamp without time zone | not null default now() updatedby | numeric(10,0) | not null name | character varying(60) | not null description| character varying(255) | vformat| character varying(40) | isautosequence | character(1)| not null default 'Y'::bpchar incrementno| numeric(10,0) | not null startno| numeric(10,0) | not null currentnext| numeric(10,0) | not null currentnextsys | numeric(10,0) | not null isaudited | character(1)| default 'N'::bpchar istableid | character(1)| default 'N'::bpchar prefix | character varying(255) | suffix | character varying(255) | startnewyear | character(1)| default 'N'::bpchar datecolumn | character varying(60) | decimalpattern | character varying(40) | Indexes: "ad_sequence_pkey" PRIMARY KEY, btree (ad_sequence_id) "ad_sequence_name" UNIQUE, btree (ad_client_id, name) Check constraints: "ad_sequence_isactive_check" CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_isaudited_check" CHECK (isaudited = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_isautosequence_check" CHECK (isautosequence = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_istableid_check" CHECK (istableid = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) "ad_sequence_startnewyear_check" CHECK (startnewyear = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) Foreign-key constraints: "sequenceclient" FOREIGN KEY (ad_client_id) REFERENCES ad_client(ad_client_id) DEFERRABLE INITIALLY DEFERRED "sequenceorg" FOREIGN KEY (ad_org_id) REFERENCES ad_org(ad_org_id) DEFERRABLE INITIALLY DEFERRED With Thanks & Regards: - Shohorab Hossain - Original Message From: Scott Marlowe To: shohorab hossain Cc: pgsql-ad...@postgresql.org; pgsql-d...@postgresql.org; pgsql-general@postgresql.org Sent: Wednesday, November 18, 2009 2:55:25 Subject: Re: [GENERAL] Postgresql Database Lock Problem Next time this is happening join the pg_lock table to the pg_stat_activity table to see which query is holding the lock for a bazillion milliseconds, while it's happening. That query / statement may give you some clue what's wrong. Get your preferred Email name! Now you can @ymail.com and @rocketmail.com. http://mail.promotions.yahoo.com/newdomains/aa/ -- 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 Database Lock Problem
At first I would like to thank you all for your quick response. Now my question is why two processes ID 19181 and 19196 are trying to access the same transaction 18386574 at the same time. Here I have got Process ID, Transaction ID and SQL statement from pg_locks and pg_stat_activity view. Is it possible to find from which Application form/window the query is being running. With Thanks & Regards: - Shohorab Hossain - Original Message From: Tom Lane To: shohorab hossain Cc: pgsql-general@postgresql.org Sent: Wednesday, November 18, 2009 1:47:53 Subject: Re: [GENERAL] Postgresql Database Lock Problem [ cc's trimmed a bit ] shohorab hossain writes: > LOG: process 19181 still waiting for ShareLock on transaction 18025221 > after 1002.251 ms > STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, > DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND > AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND > IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF > AD_Sequence > LOG: process 19181 acquired ShareLock on transaction 18025221 after > 1298870.572 ms > STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, > DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND > AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND > IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF > AD_Sequence It looks to me like the database is doing exactly what you are telling it to, ie, waiting until it can get an update lock on the selected row(s) of AD_Sequence. What you need to look into is what the other transaction was doing that caused it to sit on uncommitted changes to those rows for 1300 seconds. Most likely this is an application bug or bad application design (like failing to commit changes reasonably promptly, or maybe not understanding the semantics of FOR UPDATE in the first place). regards, tom lane Get your preferred Email name! Now you can @ymail.com and @rocketmail.com. http://mail.promotions.yahoo.com/newdomains/aa/ -- 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 Database Lock Problem
Now I am using the following parameter to temporary resolve the locking problem statment_timeout = 20s With Thanks & Regards: - Shohorab Hossain - Original Message From: shohorab hossain To: Tom Lane Cc: pgsql-general@postgresql.org Sent: Wednesday, November 18, 2009 14:41:09 Subject: Re: [GENERAL] Postgresql Database Lock Problem At first I would like to thank you all for your quick response. Now my question is why two processes ID 19181 and 19196 are trying to access the same transaction 18386574 at the same time. Here I have got Process ID, Transaction ID and SQL statement from pg_locks and pg_stat_activity view. Is it possible to find from which Application form/window the query is being running. With Thanks & Regards: - Shohorab Hossain - Original Message From: Tom Lane To: shohorab hossain Cc: pgsql-general@postgresql.org Sent: Wednesday, November 18, 2009 1:47:53 Subject: Re: [GENERAL] Postgresql Database Lock Problem [ cc's trimmed a bit ] shohorab hossain writes: > LOG: process 19181 still waiting for ShareLock on transaction 18025221 > after 1002.251 ms > STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, > DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND > AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND > IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF > AD_Sequence > LOG: process 19181 acquired ShareLock on transaction 18025221 after > 1298870.572 ms > STATEMENT: SELECT CurrentNext, CurrentNextSys, IncrementNo, Prefix, Suffix, > DecimalPattern, AD_Sequence_ID FROM AD_Sequence WHERE Name = $1 AND > AD_Client_ID = $2 AND IsActive='Y' AND IsTableID='N' AND > IsAutoSequence='Y' ORDER BY AD_Client_ID DESC FOR UPDATE OF > AD_Sequence It looks to me like the database is doing exactly what you are telling it to, ie, waiting until it can get an update lock on the selected row(s) of AD_Sequence. What you need to look into is what the other transaction was doing that caused it to sit on uncommitted changes to those rows for 1300 seconds. Most likely this is an application bug or bad application design (like failing to commit changes reasonably promptly, or maybe not understanding the semantics of FOR UPDATE in the first place). regards, tom lane Get your preferred Email name! Now you can @ymail.com and @rocketmail.com. http://mail.promotions.yahoo.com/newdomains/aa/ New Email addresses available on Yahoo! Get the Email name you've always wanted on the new @ymail and @rocketmail. Hurry before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/ -- 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 parameters to multiple statements
On Tue, 17 Nov 2009 20:16:36 -0800 David Fetter wrote: > On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov > wrote: > > Some companies have policy to stay DB agnostic, i.e. use standard > > SQL only. > That's called shooting yourself in the head. I'm a small fish. I use just Free software and still I think that departing from agnosticity has its cost even if you don't have to pay license costs. Especially if you did it without knowing it or with no reason. Many times departing from agnostic code is caused by: - lack of knowledge of standards/more than one DB - early optimization It's just a matter of where you're going to compromise and why, but you've to do it consciously. eg. a lot of code could run on mysql and postgresql as well at no cost, but many people just ignore there is something else other than mysql. That's shooting yourself in the head without even knowing the reason. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Data Directory size increasing abnormally
> A strange behaviour is observerd in the physical files with respect to > this table. The size of the file is growing abnormally in GBs. Suppose > the file name (oid of relation )with respect to the table is "18924" I > could find entries of 1 GB files like 18924, 18924.1, 18924.2 , > 18924.3..18924.40 in the data directory, though there are on 10k > records in the table. Hello, your application is similar to mine and has similar problems. In mine I have average 10 Updates per second with peaks of 100 updates per second... this kind of applications is known to cause un undesireable behaviour in PostgresSQL called (I hope I'm not confusing things here) table bloat and if you have indexes/primary keys, also index bloat; the solution is somewhere in the correct balance of: autovacuum, periodic vacuuming, vacuum full once in a while, and re-indexing; of course upgrading to 8.3.x or higher is very very much recommended. but with this kind of application (very frequent updates) vacuum/autovacuum/reindex/cluster will always be good friends to have around. My best advice is to quickly read the documentation regarding those commands and start applying them to your test database; when you get the feeling of it configure autovacuum and plan weekly (to start with) vacuum/reindex operations; vacuum full is typically not needed but let the experience tell you how it goes in your case. I'm not a postgres expert but my application, similar in behaviour to yours, teached me these things. So I'm sorry if I'm not being totally cientific regarding my suggestions. cheers Joao > -- 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 parameters to multiple statements
2009/11/18 Ivan Sergio Borgonovo : > On Tue, 17 Nov 2009 20:16:36 -0800 > David Fetter wrote: > >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov >> wrote: >> > Some companies have policy to stay DB agnostic, i.e. use standard >> > SQL only. > >> That's called shooting yourself in the head. > > I'm a small fish. I use just Free software and still I think that > departing from agnosticity has its cost even if you don't have to > pay license costs. > Especially if you did it without knowing it or with no reason. > Many times departing from agnostic code is caused by: > - lack of knowledge of standards/more than one DB > - early optimization > > It's just a matter of where you're going to compromise and why, but > you've to do it consciously. > > eg. a lot of code could run on mysql and postgresql as well at no > cost, but many people just ignore there is something else other than > mysql. > That's shooting yourself in the head without even knowing the reason. Sorry, but David has true. I understand, so management is happy, when could to save some money. But it is very wrong for customers - and for programmers too. Only very trivial application should be designed generally and with same SQL code for all database engines. Why: a) you cannot use a stored procedures - it should have very significant impact on effectivity b) you cannot use a fulltext function - if you use LIKE, then your application is dead on bigger data. c) you cannot use a triggers - then all audit and check logic have to be processed on client part - your application will be monolithic and heavy. This is very significant, because fixing bugs and enhancing is more expensive. When your application isn't trivial, then is very good to use decomposition, identify database layer API and creating and maintaining separate modules for different databases. Using common code for all engines is very expensive - then you don't develop, then you searching common space - but it is very difficult. It is true, so db engines shared some functionality now, but they doesn't shared same bugs. And you have to put all together. Debugging, fixing of this applications is very very expensive. Pavel > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] vacuumdb: vacuuming of database "xy" failed: PANIC: corrupted item pointer: 19227
Hello! How do I location of this pointer and how do I zero it so I can access the rest of the data? "zero_damaged_pages = true" did not help in this case, because I always get same numbers being zeroed. This is with 8.4.0 and 8.4.1. 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] passing parameters to multiple statements
On Wed, 18 Nov 2009 11:38:46 +0100 Pavel Stehule wrote: > 2009/11/18 Ivan Sergio Borgonovo : > > On Tue, 17 Nov 2009 20:16:36 -0800 > > David Fetter wrote: > > > >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov > >> wrote: > >> > Some companies have policy to stay DB agnostic, i.e. use > >> > standard SQL only. > > > >> That's called shooting yourself in the head. > > > > I'm a small fish. I use just Free software and still I think that > > departing from agnosticity has its cost even if you don't have to > > pay license costs. > > Especially if you did it without knowing it or with no reason. > > Many times departing from agnostic code is caused by: > > - lack of knowledge of standards/more than one DB > > - early optimization > > > > It's just a matter of where you're going to compromise and why, > > but you've to do it consciously. > > > > eg. a lot of code could run on mysql and postgresql as well at no > > cost, but many people just ignore there is something else other > > than mysql. > > That's shooting yourself in the head without even knowing the > > reason. > Sorry, but David has true. I understand, so management is happy, I didn't say he was wrong. As usual it is a matter of knowledge and trade off. How can you say what's better if: - you don't know what is standard - you don't know the performance impact of writing something in a dialect of SQL rather than in a standard way One thing is saying you accept the need of breaking compatibility for some DB another is saying that pursuing writing standard code is reckless since it makes all projects too complex and bloated. Ignorance and dogmatism are strict relatives, but I'd say the former is the root of the later. In fact what I generally observe is: - we just know [this] (ignorance) - this *looks* like it will run faster/be easier to write if we write it this way - we tried it on another DB and it performed really bad/was really complicated to rewrite - everything else other than [this] is bad, why should we care (dogmatism) Depending on the domain of the application the DB may not be such a critical part of the overall, and still many things may easily be written in a way that is independent from the DB. In my experience you may end up writing 90% of code that could easily be written in a standard way and with no appreciable difference in costs (performance/coding). Writing stuff in a way that it will make cheaper porting code may even protect you from the changes in the DB you chose as a target. A policy that mandates the use of portable SQL code for any part of any application that you're going to write in a company is equally insane as a policy that mandates to write all code for all applications in python and make them in such a way that they could be automatically translated in any language whose name start with p ;) But I think such kind of policy is rarer than the programmers that know more than a couple of SQL dialects. I don't think companies with such an high level of dogmatism can survive enough long to get involved in something that is not trivial, while it is far more frequent to see applications that don't have such an high coupling with the DB that still are dependent on it just for lack of knowledge of SQL. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] vacuumdb: vacuuming of database "xy" failed: PANIC: corrupted item pointer: 19227
2009/11/18 Tech 2010 : > Hello! > > How do I location of this pointer and how do I zero it so I can access > the rest of the data? > > "zero_damaged_pages = true" did not help in this case, because I > always get same numbers being zeroed. This is with 8.4.0 and 8.4.1. > > Thanks. > You probably just need to reindex a table. Try vacuuming each table individually until you get this error, or just check the vacuum log output to identify it, then just run a REINDEX on it. Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creating new database
Dear All, I've been using a single database for many years. I'd now like to create a new separate database with most of the same tables, so that I don't have to re-write my code, I'd like to just use a different Tomcat datasource to access it. I had imagined this would be as simple as : 1) using createdb, to create a database with a different name, maybe with a different postgres user assigned to it, 2) using a pg_dump from the original database to import the table creation. However it doesn't appear to be that simple. Let's say the old database is called database1, and I've created a new database2. When I import the pg_dump into database2, it tells me that the tables already exist (obviously it's talking about the tables in database1). But I surely I've created a new database called database2? Why is it stopping me from creating the same tables in the new database? Same sort of problem with psql: If I run 'psql database2' then I see everything from database1 as well. What am I missing here? Thanks for your time. Malcolm Warren
Re: [GENERAL] Creating new database
On 18/11/2009 11:48, Malcolm Warren wrote: > Let's say the old database is called database1, and I've created a > new database2. > > When I import the pg_dump into database2, it tells me that the tables > already exist (obviously it's talking about the tables in > database1). But I surely I've created a new database called > database2? Why is it stopping me from creating the same tables in the > new database? > > Same sort of problem with psql: If I run 'psql database2' then I see > everything from database1 as well. What am I missing here? Did you use database1 as a template for database 2? createdb -T database1 If so, then database2 will have the same structure as database1. If not, did the tables get created in the database "template1" at some point? If you don't specify a template, CREATE DATABASE (which createdb wraps) uses template1 as the template. In any case, CREATE DATABASE always uses another database as a template from which to copy the structure. By default, it uses template1, and if that somehow gets polluted (happens to us all betimes) then you also have template0 to fall back on. I'm guessing that one of these scenarios is causing your problem. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] column does not exist error
Tearing my hair out, can anyone see what I'm doing wrong? SELECT title FROM node WHERE type=client; ERROR: column "client" does not exist LINE 1: SELECT title FROM node WHERE type=client; Yet this works: SELECT type FROM node; type client client client client client (5 rows) -- 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] column does not exist error
On 18/11/2009 13:23, Dave Coventry wrote: > Tearing my hair out, can anyone see what I'm doing wrong? > > SELECT title FROM node WHERE type=client; You need to quote literal values: SELECT title FROM node WHERE type='client'; Otherwise PG thinks you're referring to a column called "client", as you saw. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] column does not exist error
SELECT title FROM node WHERE type='client'; Would this work? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dave Coventry Sent: Wednesday, November 18, 2009 6:24 AM To: pgsql-general General Subject: [GENERAL] column does not exist error Tearing my hair out, can anyone see what I'm doing wrong? SELECT title FROM node WHERE type=client; ERROR: column "client" does not exist LINE 1: SELECT title FROM node WHERE type=client; Yet this works: SELECT type FROM node; type client client client client client (5 rows) -- 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] column does not exist error
Dave Coventry, 18.11.2009 14:23: Tearing my hair out, can anyone see what I'm doing wrong? SELECT title FROM node WHERE type=client; ERROR: column "client" does not exist LINE 1: SELECT title FROM node WHERE type=client; You are missing the quotes to identify a character literal: SELECT title FROM node WHERE type='client'; Thomas -- 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 parameters to multiple statements
2009/11/18 Ivan Sergio Borgonovo : > On Wed, 18 Nov 2009 11:38:46 +0100 > Pavel Stehule wrote: > >> 2009/11/18 Ivan Sergio Borgonovo : >> > On Tue, 17 Nov 2009 20:16:36 -0800 >> > David Fetter wrote: >> > >> >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov >> >> wrote: >> >> > Some companies have policy to stay DB agnostic, i.e. use >> >> > standard SQL only. >> > >> >> That's called shooting yourself in the head. >> > >> > I'm a small fish. I use just Free software and still I think that >> > departing from agnosticity has its cost even if you don't have to >> > pay license costs. >> > Especially if you did it without knowing it or with no reason. >> > Many times departing from agnostic code is caused by: >> > - lack of knowledge of standards/more than one DB >> > - early optimization >> > >> > It's just a matter of where you're going to compromise and why, >> > but you've to do it consciously. >> > >> > eg. a lot of code could run on mysql and postgresql as well at no >> > cost, but many people just ignore there is something else other >> > than mysql. >> > That's shooting yourself in the head without even knowing the >> > reason. > >> Sorry, but David has true. I understand, so management is happy, > > I didn't say he was wrong. > > As usual it is a matter of knowledge and trade off. > How can you say what's better if: > - you don't know what is standard > - you don't know the performance impact of writing something in a > dialect of SQL rather than in a standard way > Standard is good. And I prefere standard everywhere, where is possible. But standard has some parts, thats are not respected and not well implemented. For example - stored procedures and fulltext. I had to posibility to see some applications developed by programmers without knowleadge of these. Nothing good. When I ask, why they wrote it, they replyed so needs support for T-SQL and Oracle. Any dogmatism is wrong - yes. But minimally me - and probably David has very bad experience with design ala "all sql code for all databases". And I have good experience with different strategy - early decomposition and separation application and database (engine specific) layer. Nothing less, nothing more. Regards Pavel > One thing is saying you accept the need of breaking compatibility > for some DB another is saying that pursuing writing standard code is > reckless since it makes all projects too complex and bloated. > > Ignorance and dogmatism are strict relatives, but I'd say the former > is the root of the later. > > In fact what I generally observe is: > - we just know [this] (ignorance) > - this *looks* like it will run faster/be easier to write if we write > it this way > - we tried it on another DB and it performed really bad/was really > complicated to rewrite > - everything else other than [this] is bad, why should we care > (dogmatism) > > Depending on the domain of the application the DB may not be such a > critical part of the overall, and still many things may easily be > written in a way that is independent from the DB. > > In my experience you may end up writing 90% of code that could easily > be written in a standard way and with no appreciable difference in > costs (performance/coding). > > Writing stuff in a way that it will make cheaper porting code may > even protect you from the changes in the DB you chose as a target. > > A policy that mandates the use of portable SQL code for any part of > any application that you're going to write in a company is equally > insane as a policy that mandates to write all code for all > applications in python and make them in such a way that they could > be automatically translated in any language whose name start with > p ;) > > But I think such kind of policy is rarer than the programmers that > know more than a couple of SQL dialects. > > I don't think companies with such an high level of dogmatism can > survive enough long to get involved in something that is not > trivial, while it is far more frequent to see applications that > don't have such an high coupling with the DB that still are dependent > on it just for lack of knowledge of SQL. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > 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] Creating new database
can you specify the exact commands and the sequence. With this set up it should work. how do you import the data. Vidhya On Wed, Nov 18, 2009 at 5:18 PM, Malcolm Warren wrote: > Dear All, > > > I've been using a single database for many years. > I'd now like to create a new separate database with most of the same > tables, so that I don't have to re-write my code, I'd like to just use a > different Tomcat datasource to access it. > > I had imagined this would be as simple as : > 1) using createdb, to create a database with a different name, maybe with a > different postgres user assigned to it, > 2) using a pg_dump from the original database to import the table creation. > > However it doesn't appear to be that simple. > > Let's say the old database is called database1, and I've created a new > database2. > > When I import the pg_dump into database2, it tells me that the tables > already exist (obviously it's talking about the tables in database1). > But I surely I've created a new database called database2? Why is it > stopping me from creating the same tables in the new database? > > Same sort of problem with psql: > If I run 'psql database2' then I see everything from database1 as well. > What am I missing here? > > > > Thanks for your time. > > Malcolm Warren > > > > >
Re: [GENERAL] column does not exist error
Can you try reframing it as : SELECT title FROM node WHERE type='client; Regards Vidhya On Wed, Nov 18, 2009 at 6:53 PM, Dave Coventry wrote: > Tearing my hair out, can anyone see what I'm doing wrong? > > SELECT title FROM node WHERE type=client; > > ERROR: column "client" does not exist > LINE 1: SELECT title FROM node WHERE type=client; > > Yet this works: > > SELECT type FROM node; > type > > client > client > client > client > client > (5 rows) > > -- > 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] column does not exist error
Hi, try SELECT title FROM node WHERE type='client'; hth Thomas Dave Coventry schrieb: > Tearing my hair out, can anyone see what I'm doing wrong? > > SELECT title FROM node WHERE type=client; > > ERROR: column "client" does not exist > LINE 1: SELECT title FROM node WHERE type=client; > > Yet this works: > > SELECT type FROM node; > type > > client > client > client > client > client > (5 rows) > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] advocating pgsql was:passing parameters to multiple statements
On Wed, 18 Nov 2009 14:39:05 +0100 Pavel Stehule wrote: > Standard is good. And I prefere standard everywhere, where is [snip] > Any dogmatism is wrong - yes. But minimally me - and probably David > has very bad experience with design ala "all sql code for all > databases". And I have good experience with different strategy - > early decomposition and separation application and database (engine > specific) layer. Nothing less, nothing more. [snip] I'm not competing on the technical position of the issue, yours is very respected. I'd like to put the accent on the "social" part of it and on the fall back on our beloved DB. I think there are far more people knowing just one DB and badly than companies with such strict (insane) policies and... well PostgreSQL is very standard compliant. I'd beat another horse ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] column does not exist error
Type may be a reserved keyword and need double quoting: where "type"='client'; On Wed, Nov 18, 2009 at 6:23 AM, Dave Coventry wrote: > Tearing my hair out, can anyone see what I'm doing wrong? > > SELECT title FROM node WHERE type=client; > > ERROR: column "client" does not exist > LINE 1: SELECT title FROM node WHERE type=client; > > Yet this works: > > SELECT type FROM node; > type > > client > client > client > client > client > (5 rows) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be intolerance sold as diversity. -- 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 parameters to multiple statements
Konstantin Izmailov, 17.11.2009 17:33: This is why they want to use multiple statements Which is not portable as well. Actually the only database I know which permits sending more than one statement in "one string" is SQL Server... Thomas -- 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 parameters to multiple statements
Konstantin Izmailov wrote: > Some companies have policy to stay DB agnostic, i.e. use standard SQL only. Good luck with that. For example, querying the lastval of a sequence, as your sample code does, already falls outside of standard SQL, AFAIK. > If PQexecParams does not support multiple statements, it needs to be > extended for the support, or new function created for the purpose. If I do > the change in libpq, may I submit the code to community? That change would not be in libpq but in the server. libpq doesn't parse SQL statements. I wish I could say that in future versions you'd be likely to use the DO clause to the effect of grouping statements in one SQL block, in a modern and supported way (DO is in the 8.5 alpha release). Unfortunately DO doesn't seem to accept parameters, which makes it less useful than it could be otherwise. Personally I know I won't really be able to use the DO blocks if I can't pass parameters to them like if it was a single INSERT or UPDATE statement, the use cases being the same to me. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] Creating new database
I'dd suggest: pgdumpall --clean > dump.sql edit the dump.sql file by hand replacing database name and owners and so... then reload into the new DB with psql -f dump.sql postgres this does all the work except creation of users and databases should give you an exact replica with all data inside see "man pgdumpall" Joao On Wed, 2009-11-18 at 12:48 +0100, Malcolm Warren wrote: > Dear All, > > > I've been using a single database for many years. > I'd now like to create a new separate database with most of the same > tables, so that I don't have to re-write my code, I'd like to just use > a different Tomcat datasource to access it. > > I had imagined this would be as simple as : > 1) using createdb, to create a database with a different name, maybe > with a different postgres user assigned to it, > 2) using a pg_dump from the original database to import the table > creation. > > However it doesn't appear to be that simple. > > Let's say the old database is called database1, and I've created a new > database2. > > When I import the pg_dump into database2, it tells me that the tables > already exist (obviously it's talking about the tables in database1). > But I surely I've created a new database called database2? Why is it > stopping me from creating the same tables in the new database? > > Same sort of problem with psql: > If I run 'psql database2' then I see everything from database1 as > well. > What am I missing here? > > > > Thanks for your time. > > Malcolm Warren > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Fwd: Re: [GENERAL] Creating new database]
--- Begin Message --- Thank you very much for your replies. It's clear to me from your replies that something very odd is going on, not least because I now see that if I connect with: psql template1 I see everything in database1, including data, whereas template1 should be empty. Luckily this is my test database, and the production database behaves normally, so it's clear that I've set up my test database all wrong (which was done from a partial pg_dump). So I'm going to do a pg_dumpall from the production database, drop the test database and start from scratch, and try again. Thanks, Malcolm Vidhya Bondre ha scritto: can you specify the exact commands and the sequence. With this set up it should work. how do you import the data. Vidhya On Wed, Nov 18, 2009 at 5:18 PM, Malcolm Warren mailto:malc...@villeinitalia.com>> wrote: Dear All, I've been using a single database for many years. I'd now like to create a new separate database with most of the same tables, so that I don't have to re-write my code, I'd like to just use a different Tomcat datasource to access it. I had imagined this would be as simple as : 1) using createdb, to create a database with a different name, maybe with a different postgres user assigned to it, 2) using a pg_dump from the original database to import the table creation. However it doesn't appear to be that simple. Let's say the old database is called database1, and I've created a new database2. When I import the pg_dump into database2, it tells me that the tables already exist (obviously it's talking about the tables in database1). But I surely I've created a new database called database2? Why is it stopping me from creating the same tables in the new database? Same sort of problem with psql: If I run 'psql database2' then I see everything from database1 as well. What am I missing here? Thanks for your time. Malcolm Warren --- End Message --- -- 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] Creating new database
On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail wrote: > I'dd suggest: > > pgdumpall --clean > dump.sql I'd think he'd be much better off with pg_dump, not pg_dumpall. pg_dump srcdb | psql destdb you can add -s as a pg_dump switch if all you want is the schema. -- 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] Creating new database
On Wed, 2009-11-18 at 08:39 -0700, Scott Marlowe wrote: > On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail > wrote: > > I'dd suggest: > > > > pgdumpall --clean > dump.sql > > I'd think he'd be much better off with pg_dump, not pg_dumpall. yes, agree. sorry. joao > > pg_dump srcdb | psql destdb > > you can add -s as a pg_dump switch if all you want is the schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of sync Primary Key Index
I have deleted a record from a table and am now getting errors:
INSERT INTO content_node_field (field_name, type, global_settings,
required, multiple, db_storage, module, db_columns, active, locked)
VALUES ('field_client_name', 'text',
'a:4:{s:15:"text_processing";s:0:"";s:10:"max_length";s:0:"";s:14:"allowed_values";s:0:"";s:18:"allowed_values_php";s:0:"";}',
0, 0, 1, 'text',
'a:1:{s:5:"value";a:5:{s:4:"type";s:4:"text";s:4:"size";s:3:"big";s:8:"not
null";b:0;s:8:"sortable";b:1;s:5:"views";b:1;}}', 1, 0);
ERROR: duplicate key value violates unique constraint "content_node_field_pkey"
My table is as follows:
\d content_node_field;
Table "public.content_node_field"
Column | Type | Modifiers
-++
field_name | character varying(32) | not null default
''::character varying
type| character varying(127) | not null default
''::character varying
global_settings | text | not null
required| smallint | not null default 0
multiple| smallint | not null default 0
db_storage | smallint | not null default 1
module | character varying(127) | not null default
''::character varying
db_columns | text | not null
active | smallint | not null default 0
locked | smallint | not null default 0
Indexes:
"content_node_field_pkey" PRIMARY KEY, btree (field_name)
>From googling I need to reset my Primary Key, but
reindex index content_node_field_pkey;
REINDEX
Doesn't make any difference (no error is returned)
Nor does
reindex table content_node_field;
REINDEX
--
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] Out of sync Primary Key Index
- "Dave Coventry" wrote:
> I have deleted a record from a table and am now getting errors:
>
> INSERT INTO content_node_field (field_name, type, global_settings,
> required, multiple, db_storage, module, db_columns, active, locked)
> VALUES ('field_client_name', 'text',
> 'a:4:{s:15:"text_processing";s:0:"";s:10:"max_length";s:0:"";s:14:"allowed_values";s:0:"";s:18:"allowed_values_php";s:0:"";}',
> 0, 0, 1, 'text',
> 'a:1:{s:5:"value";a:5:{s:4:"type";s:4:"text";s:4:"size";s:3:"big";s:8:"not
> null";b:0;s:8:"sortable";b:1;s:5:"views";b:1;}}', 1, 0);
> ERROR: duplicate key value violates unique constraint
> "content_node_field_pkey"
>
> My table is as follows:
>
> \d content_node_field;
> Table "public.content_node_field"
> Column | Type | Modifiers
> -++
> field_name | character varying(32) | not null default
> ''::character varying
> type| character varying(127) | not null default
> ''::character varying
> global_settings | text | not null
> required| smallint | not null default 0
> multiple| smallint | not null default 0
> db_storage | smallint | not null default 1
> module | character varying(127) | not null default
> ''::character varying
> db_columns | text | not null
> active | smallint | not null default 0
> locked | smallint | not null default 0
> Indexes:
> "content_node_field_pkey" PRIMARY KEY, btree (field_name)
>
> From googling I need to reset my Primary Key, but
>
> reindex index content_node_field_pkey;
> REINDEX
>
> Doesn't make any difference (no error is returned)
>
> Nor does
> reindex table content_node_field;
> REINDEX
>
No what it is telling you is that the field_name ('field_client_name') is
already in the table.
Adrian Klaver
akla...@comcast.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] Out of sync Primary Key Index
In response to Dave Coventry :
> I have deleted a record from a table and am now getting errors:
>
> INSERT INTO content_node_field (field_name, type, global_settings,
> required, multiple, db_storage, module, db_columns, active, locked)
> VALUES ('field_client_name', 'text',
> 'a:4:{s:15:"text_processing";s:0:"";s:10:"max_length";s:0:"";s:14:"allowed_values";s:0:"";s:18:"allowed_values_php";s:0:"";}',
> 0, 0, 1, 'text',
> 'a:1:{s:5:"value";a:5:{s:4:"type";s:4:"text";s:4:"size";s:3:"big";s:8:"not
> null";b:0;s:8:"sortable";b:1;s:5:"views";b:1;}}', 1, 0);
> ERROR: duplicate key value violates unique constraint
> "content_node_field_pkey"
What do you see if you do:
SELECT * FROM content_node_field WHERE field_name='field_client_name';
> My table is as follows:
>
> \d content_node_field;
> Table "public.content_node_field"
> Column | Type | Modifiers
> -++
> field_name | character varying(32) | not null default
> ''::character varying
> type| character varying(127) | not null default
> ''::character varying
> global_settings | text | not null
> required| smallint | not null default 0
> multiple| smallint | not null default 0
> db_storage | smallint | not null default 1
> module | character varying(127) | not null default
> ''::character varying
> db_columns | text | not null
> active | smallint | not null default 0
> locked | smallint | not null default 0
> Indexes:
> "content_node_field_pkey" PRIMARY KEY, btree (field_name)
>
> From googling I need to reset my Primary Key, but
>
> reindex index content_node_field_pkey;
> REINDEX
>
> Doesn't make any difference (no error is returned)
>
> Nor does
> reindex table content_node_field;
> REINDEX
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Totally fresh uninstall/reinstall of Postgres on MacBook Pro running Snow Leopard?
Hi -- I'm having a world of trouble getting Postgres to work with a number of ruby libraries, and was thinking that, to be sure that I'm eliminating all that could be wrong, I need to ensure that my Postgres install is fresh, correct, and as canonical as possible. Could someone advise me on the best way to ensure that I have first *totally* uninstalled Postgres from my machine, then secondly the best way to install it so that everything is most likely to be set up the way a third-party developer might expect (ie, what is the most vanilla Postgres install)? Thankyou very much for any & all advice. Doug.
Re: [GENERAL] passing parameters to multiple statements
Ok, I accept reasoning that DB agnostic development is propbably a bad idea. The question should probably be re-introduced as "Stored Procedures against multiple statements in Postgres". Here is my client opinion: "SP’s have their place, as with any development, there’s many reasons for and against any method and there are many methods. I’ve always taken the view – use the right tool for the job. If you always use stored procedures, you end up with tons and organising them is a nightmare, they are good if you need to reuse the SQL <<>> Yes especially flexibility – we don’t want to have a sp for every insert on every table in our db that inserts then returns the currValue and I would like to create a temp db then query that as a second query instead of using retain connection which will give me better performance – only one db connection attempt as opposed to two and I don’t want to create 50 sp’s to do it on each table. I think their good enough reasons, business or otherwise. I notice this attitude a lot in postgres community – it’s like the reasoning for not allowing cross db queries – “blah blah should have designed db better blah blah”, what they don’t realise is, that some people might want to have an archive db or warehouse and to get data into it would be a lot easier with cross db queries. <<>>" Anyway, here is what I understood: 1. If client app needs support for multiple statements with parameters in PostgreSQL, I have to provide a software layer above libpq that includes parser, metadata cache, etc. 2. "BEGIN; INSERT ...; SELECT lastval(); COMMIT;" would work but is not portable because of lastval(). 3. No change is needed in libpq since parser can split the multiple statements in the layer above. Thank you for the valuable discussion! Konstantin On Tue, Nov 17, 2009 at 9:16 PM, David Fetter wrote: > On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote: > > Some companies have policy to stay DB agnostic, i.e. use standard > > SQL only. > > That's called shooting yourself in the head. > > Unless you have a very, very specific, business-critical reason to pay > this huge cost, you should never attempt it. That some companies have > silly, self-destructive policies is not a reason for anybody not > working there to pay attention to same. > > More details on why it's so inevitably expensive below: > > > http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html > > http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html > > Cheers, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
Re: [GENERAL] Totally fresh uninstall/reinstall of Postgres on MacBook Pro running Snow Leopard?
On Wed, Nov 18, 2009 at 10:37 AM, doug livesey wrote: > Hi -- I'm having a world of trouble getting Postgres to work with a number > of ruby libraries, and was thinking that, to be sure that I'm eliminating > all that could be wrong, I need to ensure that my Postgres install is fresh, > correct, and as canonical as possible. > Could someone advise me on the best way to ensure that I have first > *totally* uninstalled Postgres from my machine, then secondly the best way > to install it so that everything is most likely to be set up the way a > third-party developer might expect (ie, what is the most vanilla Postgres > install)? Not sure what you're trying to do, but on a Windows machine I've never been able to get the Ruby Gems to recognize / find / work with a Postgres install that was not integrated with them from the get go. I've always had to use the integrated Postgres / Ruby packages to have Ruby work... -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Totally fresh uninstall/reinstall of Postgres on MacBook Pro running Snow Leopard?
What packages are those? Maybe I should look them up. (Sorry for the double-post, Peter.)
Re: [GENERAL] Totally fresh uninstall/reinstall of Postgres on MacBook Pro running Snow Leopard?
Ah, do you mean the postgres-pr gem? That seems to have done it, for now -- I shall see if it falls over when I use it in anger. Cheers, Doug. 2009/11/18 doug livesey > What packages are those? Maybe I should look them up. > > (Sorry for the double-post, Peter.) >
Re: [GENERAL] Creating new database - SOLVED
Dear All, Thank you for your emails which were very helpful. I've finally solved it. template1 was full of data, presumably somehow from a badly-run dump and restore. So when I created the new database, that too was full of data from the template. I ran a clean pg_dump on template1 from my production database and restored it on my test machine, and finally everything is normal. Thanks again, Malcolm Joao Ferreira gmail ha scritto: On Wed, 2009-11-18 at 08:39 -0700, Scott Marlowe wrote: On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail wrote: I'dd suggest: pgdumpall --clean > dump.sql I'd think he'd be much better off with pg_dump, not pg_dumpall. yes, agree. sorry. joao pg_dump srcdb | psql destdb you can add -s as a pg_dump switch if all you want is the schema.
Re: [GENERAL] passing parameters to multiple statements
2009/11/18 Konstantin Izmailov :
> Ok, I accept reasoning that DB agnostic development is propbably a bad idea.
>
> The question should probably be re-introduced as "Stored Procedures against
> multiple statements in Postgres".
>
> Here is my client opinion:
> "SP’s have their place, as with any development, there’s many reasons for
> and against any method and there are many methods. I’ve always taken the
> view – use the right tool for the job.
>
> If you always use stored procedures, you end up with tons and organising
> them is a nightmare, they are good if you need to reuse the SQL <<>>
>
> Yes especially flexibility – we don’t want to have a sp for every insert on
> every table in our db that inserts then returns the currValue and I would
> like to create a temp db then query that as a second query instead of using
> retain connection which will give me better performance – only one db
> connection attempt as opposed to two and I don’t want to create 50 sp’s to
> do it on each table. I think their good enough reasons, business or
> otherwise.
>
> I notice this attitude a lot in postgres community – it’s like the reasoning
> for not allowing cross db queries – “blah blah should have designed db
> better blah blah”, what they don’t realise is, that some people might want
> to have an archive db or warehouse and to get data into it would be a lot
> easier with cross db queries. <<>>"
>
> Anyway, here is what I understood:
>
> 1. If client app needs support for multiple statements with parameters in
> PostgreSQL, I have to provide a software layer above libpq that includes
> parser, metadata cache, etc.
>
> 2. "BEGIN; INSERT ...; SELECT lastval(); COMMIT;" would work but is not
> portable because of lastval().
>
> 3. No change is needed in libpq since parser can split the multiple
> statements in the layer above.
>
> Thank you for the valuable discussion!
>
> Konstantin
there are lot of myth about stored procedures.
a) wrap every SQL statement to procedure is technique used on T-SQL and Sybase.
b) on Oracle and DB2 procedures are used together with views
c) if you use trigger, you don't need some strange multistatments.
d) what is more readable code (on client)?
d1:
BEGIN;
insert into table students values($1,$2,$3);
insert into audit values('students', lastval(), .)
COMMIT;
or
d2:
SELECT register_new_student($1,$2,$3);
thats all;
try to look on http://www.postgres.cz/index.php/PL/pgSQL_%28en%29
mainly on
http://www.postgres.cz/index.php/PL/pgSQL_%28en%29#Recommendation_for_design_of_saved_procedures_in_PL.2FpqSQL_language
Best regards
Pavel Stehule
>
>
>
>
>
>
>
> On Tue, Nov 17, 2009 at 9:16 PM, David Fetter wrote:
>>
>> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote:
>> > Some companies have policy to stay DB agnostic, i.e. use standard
>> > SQL only.
>>
>> That's called shooting yourself in the head.
>>
>> Unless you have a very, very specific, business-critical reason to pay
>> this huge cost, you should never attempt it. That some companies have
>> silly, self-destructive policies is not a reason for anybody not
>> working there to pay attention to same.
>>
>> More details on why it's so inevitably expensive below:
>>
>>
>> http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
>>
>> http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html
>>
>> Cheers,
>> David.
>> --
>> David Fetter http://fetter.org/
>> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
>> Skype: davidfetter XMPP: david.fet...@gmail.com
>> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>>
>> Remember to vote!
>> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
>
--
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] Totally fresh uninstall/reinstall of Postgres on MacBook Pro running Snow Leopard?
Is that a custom version of Ruby & Rails to work with Postgres, or the other way around? If the latter, that could be cool, but if the former, then not as useful, as it is with DataMapper & Sinatra I'd really like to use the postgres db. But the postgres-pr is a fine compromise for the time being, cheers! I'll use ActiveRecord with Sinatra, and see if I can change to DM somewhen later, maybe when there's a new release. Thanks a lot for your help! Doug.
[GENERAL] DB terminating
Hello. I have been having an issue with a database. The logs consistently show this: 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with exit code 128 2009-11-17 16:03:55 ESTLOCATION: LogChildExit, .\src\backend\postmaster\postmaster.c:2705 2009-11-17 16:03:55 ESTLOG: 0: terminating any other active server processes 2009-11-17 16:03:55 ESTLOCATION: HandleChildCrash, .\src\backend\postmaster\postmaster.c:2552 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect
Re: [GENERAL] DB terminating [on Windows]
Magnus, any thoughts? On Wed, Nov 18, 2009 at 9:53 AM, Scott Felt wrote: > > Hello. I have been having an issue with a database. The logs consistently > show this: > > 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with > exit code 128 > 2009-11-17 16:03:55 ESTLOCATION: LogChildExit, > .\src\backend\postmaster\postmaster.c:2705 > 2009-11-17 16:03:55 ESTLOG: 0: terminating any other active server > processes > 2009-11-17 16:03:55 ESTLOCATION: HandleChildCrash, > .\src\backend\postmaster\postmaster.c:2552 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit, because another > server process exited abnormally and possibly corrupted shared memory. > 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to > the database and repeat your command. > 2009-11-17 16:03:55 ESTLOCATION: quickdie, > .\src\backend\tcop\postgres.c:2495 > 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of > crash of another server process > 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server > proce
Re: [GENERAL] DB terminating
Scott Felt writes: > Hello. I have been having an issue with a database. The logs consistently > show this: > 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with > exit code 128 This looks like a fairly garden-variety backend crash, but with only this much information there's no way to identify the cause. You might try setting log_statements = all and see if there's any consistency in what the process was doing just before it crashed. (You'd need to add PID to log_line_prefix so you could associate the log entries with the crash report.) 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] Creating new database - SOLVED
Malcolm Warren wrote: template1 was full of data, presumably somehow from a badly-run dump and restore. So when I created the new database, that too was full of data from the template. I ran a clean pg_dump on template1 from my production database and restored it on my test machine, and finally everything is normal. you can clean up template 1 2 ways... A) drop all the objects in it as shown by \d in psql or B) drop template1 and recreate it using template0 which is sacrosanct. to do this latter, while logged on as user postgres, do something like... $ psql postgres postgres=# drop database template1; postgres=# create database template1 with template=template0; to copy your database without data, I'd use something like.. $ createdb --owner=someuser newdb $ pg_dump --schema-only olddb | psql newdb -- 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] DB terminating
Thanks, Tom. I will alter the config file to give greater details as you suggest. Perhaps that will be sufficiently informative to point me toward a fix. Thank you. --Scott Tom Lane-2 wrote: > > Scott Felt writes: >> Hello. I have been having an issue with a database. The logs >> consistently >> show this: > >> 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with >> exit code 128 > > This looks like a fairly garden-variety backend crash, but with only > this much information there's no way to identify the cause. You might > try setting log_statements = all and see if there's any consistency in > what the process was doing just before it crashed. (You'd need to add > PID to log_line_prefix so you could associate the log entries with the > crash report.) > > 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 > > -- View this message in context: http://old.nabble.com/DB-terminating-tp26412532p26413707.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] Can anyone help setting up pgbouncer?
Nick wrote:
> So now the only step I have left is actually connecting. Im trying to
> connect a php script to pgbouncer with
>
> $conn = pg_connect("host=127.0.0.1 dbname=bouncer1 port=6543
> user=nboutelier password=password");
>
> which results in
>
> Warning: pg_connect(): Unable to connect to PostgreSQL server: could
> not connect to server: Connection refused Is the server running on
> host "127.0.0.1" and accepting TCP/IP connections on port 6543
OK - so we have one of two possibilities:
1. pgbouncer isn't listening on localhost, port 6543
2. Some firewall is in the way
If you have root access then you can check #1. You'll get something like
this:
lsof -i | grep pgbouncer
pgbouncer ... TCP localhost:6432
For #2, check your configuration settings and logs to see if anything is
blocking you.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_standby instructions
Hi All - I would like to know if any one has instructions on how to setup pg_standby, if so can you please share? Thanks for the help Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby instructions
On Wed, 2009-11-18 at 16:46 -0500, akp geek wrote: > Hi All - > > I would like to know if any one has instructions on how > to setup pg_standby, if so can you please share? If you are on a *nix platform, I would suggest PITRTools which utilizes pg_standby but gives you a more complete solution. Check out: https://projects.commandprompt.com/public/pitrtools Its BSD licensed. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can anyone help setting up pgbouncer?
On Nov 18, 12:43 pm, d...@archonet.com (Richard Huxton) wrote:
> Nick wrote:
> > So now the only step I have left is actually connecting. Im trying to
> > connect a php script topgbouncerwith
>
> > $conn = pg_connect("host=127.0.0.1 dbname=bouncer1 port=6543
> > user=nboutelier password=password");
>
> > which results in
>
> > Warning: pg_connect(): Unable to connect to PostgreSQL server: could
> > not connect to server: Connection refused Is the server running on
> > host "127.0.0.1" and accepting TCP/IP connections on port 6543
>
> OK - so we have one of two possibilities:
> 1.pgbouncerisn't listening on localhost, port 6543
> 2. Some firewall is in the way
>
> If you have root access then you can check #1. You'll get something like
> this:
>
> lsof -i | greppgbouncerpgbouncer ... TCP localhost:6432
>
> For #2, check your configuration settings and logs to see if anything is
> blocking you.
>
> --
> Richard Huxton
> Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
lsof -i | grep pgbouncer
results in no output.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB terminating
Hello. I have been having an issue with a database. The logs consistently show this: 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with exit code 128 2009-11-17 16:03:55 ESTLOCATION: LogChildExit, .\src\backend\postmaster\postmaster.c:2705 2009-11-17 16:03:55 ESTLOG: 0: terminating any other active server processes 2009-11-17 16:03:55 ESTLOCATION: HandleChildCrash, .\src\backend\postmaster\postmaster.c:2552 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-11-17 16:03:55 ESTLOCATION: quickdie, .\src\backend\tcop\postgres.c:2495 2009-11-17 16:03:55 ESTWARNING: 57P02: terminating connection because of crash of another server process 2009-11-17 16:03:55 ESTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-11-17 16:03:55 ESTHINT: In a moment you should be able to reconnect to the database and repeat your command
Re: [Fwd: Re: [GENERAL] Creating new database]
pg_dumpall dumps a cluster of databases. Malcolm Warren wrote: > -- > 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] Re: vacuumdb: vacuuming of database "xy" failed: PANIC: corrupted item pointer: 19227
On 18 nov., 13:40, thombr...@gmail.com (Thom Brown) wrote: > 2009/11/18 Tech 2010 : > > > Hello! > > > How do I location of this pointer and how do I zero it so I can access > > the rest of the data? > > > "zero_damaged_pages = true" did not help in this case, because I > > always get same numbers being zeroed. This is with 8.4.0 and 8.4.1. > > > Thanks. > > You probably just need to reindex a table. Try vacuuming each table > individually until you get this error, or just check the vacuum log > output to identify it, then just run a REINDEX on it. xy=# reindex table xy_data; ERROR: concurrent insert in progress -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can anyone help setting up pgbouncer?
Nick wrote: > > lsof -i | grep pgbouncer > > results in no output. Then it's either not running or not listening on a port (you did run that as root, didn't you?). Does "ps aux | grep pgbouncer" show it? Your pgbouncer logfile should show the "File descriptor limit" line followed by one or more "listening on ..." lines. These lines should mention the port you're trying to connect to. If that all looks fine, but you still can't connect, stop pgbouncer and start it again but without the "-d" option and with the "-v" option. This will stop it from running as a daemon but make it more verbose. Hopefully we can see what it's doing then. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enum on-disk format
I'm trying to better understand the internals of Postgres, and I'm looking at the enum type. The docs say that an enum value is stored on disk as 4 bytes. But enum_send() returns a bytea representing the actual text of the value and not the index of that value. So what step am I missing here? Also, is there a way to see the raw data for the tuple on a page? I was using pageinspect to try to figure out what was happening on the disk. The get_raw_page function returns the entire page and heap_page_items will allow me to find the substring that represents a given tuple. But the rows were much wider than I thought they would be, 28 bytes + 2 byte spacer to store 4 bytes of data. Is there any way to see which bytes of an item pointer actually map to columns in a table? And where can I find more info on how Postgres stores tuples? Scott Bailey -- 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] Enum on-disk format
Scott Bailey writes: > I'm trying to better understand the internals of Postgres, and I'm > looking at the enum type. The docs say that an enum value is stored on > disk as 4 bytes. But enum_send() returns a bytea representing the actual > text of the value and not the index of that value. So what step am I > missing here? The wire format isn't necessarily the on-disk format. In this case we concluded that the internal OID value wouldn't be of any use to clients. > Also, is there a way to see the raw data for the tuple on a page? Try contrib/pageinspect, and read http://developer.postgresql.org/pgdocs/postgres/storage-page-layout.html 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] Customize the install directory of the postgres DB
Hi all, Thanks for the suggestion, i have tried out the option Thomas has mentioned by unzipping the db, and it works in our scenario. Just a question regarding the shared library. I have run the following command so that postgres can locate the shared library /sbin/ldconfig /home/app/pgsql/lib Below is the result after i executed it, are the following message expected? will it cause any issue on the postgres db? */sbin/ldconfig: /home/app/pgsql/lib/libpgtypes.so.3 is not a symbolic link /sbin/ldconfig: /home/app/pgsql/lib/libuuid.so.16 is not a symbolic link /sbin/ldconfig: /home/app/pgsql/lib/libpq.so.5 is not a symbolic link /sbin/ldconfig: /home/app/pgsql/lib/libecpg.so.6 is not a symbolic link /sbin/ldconfig: /home/app/pgsql/lib/libecpg_compat.so.3 is not a symbolic link * Thanks again. Wei Xiang On Sat, Nov 14, 2009 at 7:10 AM, Greg Smith wrote: > Tom Lane wrote: > >> The real problem that I think the OP hasn't considered is whether >> his "bundled" RPM package isn't going to conflict with a preinstalled >> postgresql RPM. Relocating the RPM, either dynamically as you suggest >> or by just changing the install paths while building it, isn't a very >> palatable solution since e.g. you really want libpq.so in /usr/lib, >> psql in /usr/bin/, etc. >> >> > It sounds like they really do want all of those things to be installed in a > subdirectory of their app, so I don't see a problem with them being there > instead of the standard locations. As long as they know how to run psql > etc., not having them in the global PATH might be a feature rather than a > problem. The only detail I'm aware of they may not have considered is that > a subdirectory install has the potential for the binaries to not be able to > find their associated libraries, so they might either have to add those to > the system loader configuration or set LD_LIBRARY_PATH before calling > database binaries. Ideally you'd find them via rpath or something so this > isn't an issue, but it's easy to miss that the first time you make a change > like this. > > > -- > Greg Smith2ndQuadrant Baltimore, MD > PostgreSQL Training, Services and Support > g...@2ndquadrant.com www.2ndQuadrant.com > >
Re: [GENERAL] DB terminating
On 19/11/2009 1:23 AM, Scott Felt wrote: > > Hello. I have been having an issue with a database. The logs consistently > show this: [snip] > Prior to this, the logs show nothing other than AutoVacWorkerMain > activities. This database is installed on a Windows OS. The version of > PostgreSQL is 8.4.1 (which I was hoping had a fix since I saw references to > a bug fix for a shared memory problem). Does anybody have any suggestions > on how to determine what causes these shutdowns? Should I be considering > dialing up the shared_buffers size in the config file? Currently it is set > at 32mb. The shared memory issue - "cannot reattach to shared memory" - doesn't appear to be what's happening here. It'd really help to know why one of the server processes crashed in the first place: 2009-11-17 16:03:55 ESTLOG: 0: server process (PID 9644) exited with exit code 128 Do you know what process this was? Was it autovacuum? A query backend? Is there anything in particular you're doing when it crashes? Or does it just crash randomly? Even when left idle? If so, does it sometimes crash almost immediately, or does it always take a while before it crashes? ie: is the delay between startup and crash predictable? Do you have any antivirus software installed? Even if it is deactivated? -- 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] Information of streaming about PostgreSQL Conference 2009 Japan
Hi, All. PostgreSQL Conference 2009 Japan will be held from tomorrow. PostgreSQL Cluster Developer's Meeting is held today. PostgreSQL Conference 2009 Japan made a official twitter tag. #pgcon09j We perform the sessions on the 21st in streaming. URL is the following. 21st - Track A http://www.ustream.tv/channel/pgcon09j-a 21st - Track B http://www.ustream.tv/channel/pgcon09j-b Please check about conference information here. http://www.postgresql.jp/events/pgcon09j/e/pgcon2009j/ Regards, -- PostgreSQL Conference 2009 Japan Committee Kaori Inaba -- 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] Enum on-disk format
Tom Lane wrote:
Scott Bailey writes:
I'm trying to better understand the internals of Postgres, and I'm
looking at the enum type. The docs say that an enum value is stored on
disk as 4 bytes. But enum_send() returns a bytea representing the actual
text of the value and not the index of that value. So what step am I
missing here?
The wire format isn't necessarily the on-disk format. In this case
we concluded that the internal OID value wouldn't be of any use to
clients.
Also, is there a way to see the raw data for the tuple on a page?
Try contrib/pageinspect, and read
http://developer.postgresql.org/pgdocs/postgres/storage-page-layout.html
regards, tom lane
Thanks Tom that did the trick. The only I/O functions I'm aware of are
send, recv, in and out. What controls converting from/to wire and
on-disk formats? And why is wire format little endian and disk big endian?
And for posterity, here's how to get to the raw tuple data.
SELECT substring(page, lp_off + t_hoff + 1, lp_len - t_hoff) AS tuple_data,
sub.*
FROM (
SELECT (heap_page_items(page)).*, page
FROM (
SELECT get_raw_page('test', 0) page
) s
) sub
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can anyone help setting up pgbouncer?
On Nov 18, 3:28 pm, d...@archonet.com (Richard Huxton) wrote: > Nick wrote: > > > lsof -i | greppgbouncer > > > results in no output. > > Then it's either not running or not listening on a port (you did run > that as root, didn't you?). Does "ps aux | greppgbouncer" show it? > > Yourpgbouncerlogfile should show the "File descriptor limit" line > followed by one or more "listening on ..." lines. These lines should > mention the port you're trying to connect to. > > If that all looks fine, but you still can't connect, stoppgbouncerand > start it again but without the "-d" option and with the "-v" option. > This will stop it from running as a daemon but make it more verbose. > Hopefully we can see what it's doing then. > > -- > Richard Huxton > Archonet Ltd > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general Solved. I restarted with the -v option and noticed a permission error with the .pid file. I guess I installed pgbouncer as root and that created the permission conflict. Ive updated the permissions and everythings running smoothly now. Thank you so much Richard for you help and patience! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
