Re: [GENERAL] delete with self join
garry saddington wrote: I am trying this syntax which is my interpretation of the docs: delete from siblings s1 using siblings s2 WHERE s1.principal = s2.principal and s1.sibling=175 Can anyone tell me where I am going wrong? 1. What's happening - are you getting an error? 2. What is the query supposed to do? I can't see why you're not just doing: DELETE FROM siblings WHERE sibling=175; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] delete with self join
On Tue, 2007-04-17 at 09:21 +0100, Richard Huxton wrote: > garry saddington wrote: > > I am trying this syntax which is my interpretation of the docs: > > > > delete from siblings s1 using siblings s2 > > WHERE s1.principal = s2.principal > > and s1.sibling=175 > > > > Can anyone tell me where I am going wrong? > > 1. What's happening - are you getting an error? I am getting a syntax error (via psycopg) at or near s1 - perhaps this is a psycopg problem? > 2. What is the query supposed to do? I can't see why you're not just doing: > DELETE FROM siblings WHERE sibling=175; > I am keeping a record of siblings in a school. The user chooses one student and there siblings such that id's are entered into a table as such: TABLE SIBLINGS: principal sibling 809 234 809 785 809 345 809 809 809 is a sibling of all of them, but of course 234 is a sibling of 785. To retrieve siblings I use this query: SELECT students.studentid,students.firstname,students.surname,students.year,students.pastoralgroup,students.dob FROM siblings c, siblings c2,students WHERE c.principal = c2.principal and c.sibling=234 (this value is supplied in a variable) and c2.sibling=students.studentid What I am trying to do is to allow the user to correct input mistakes by deleting all the siblings of one family at the same time by choosing just one of the siblings. I hope this clears things up. Regards Garry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] delete with self join
On Apr 17, 2007, at 11:07, garry saddington wrote: I am keeping a record of siblings in a school. The user chooses one student and there siblings such that id's are entered into a table as such: TABLE SIBLINGS: principal sibling 809 234 809 785 809 345 809 809 809 is a sibling of all of them, but of course 234 is a sibling of 785. To retrieve siblings I use this query: SELECT students.studentid,students.firstname,students.surname,students.year,s tudents.pastoralgroup,students.dob FROM siblings c, siblings c2,students WHERE c.principal = c2.principal and c.sibling=234 (this value is supplied in a variable) and c2.sibling=students.studentid What I am trying to do is to allow the user to correct input mistakes by deleting all the siblings of one family at the same time by choosing just one of the siblings. I hope this clears things up. Regards Garry What about a DELETE FROM siblings WHERE principal IN (SELECT principal FROM siblings WHERE sibling = 42) ? Thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] delete with self join
garry saddington wrote: On Tue, 2007-04-17 at 09:21 +0100, Richard Huxton wrote: garry saddington wrote: I am trying this syntax which is my interpretation of the docs: delete from siblings s1 using siblings s2 WHERE s1.principal = s2.principal and s1.sibling=175 Can anyone tell me where I am going wrong? 1. What's happening - are you getting an error? I am getting a syntax error (via psycopg) at or near s1 - perhaps this is a psycopg problem? 2. What is the query supposed to do? I can't see why you're not just doing: DELETE FROM siblings WHERE sibling=175; I am keeping a record of siblings in a school. The user chooses one student and there siblings such that id's are entered into a table as such: TABLE SIBLINGS: principal sibling 809 234 809 785 809 345 809 809 What I am trying to do is to allow the user to correct input mistakes by deleting all the siblings of one family at the same time by choosing just one of the siblings. I hope this clears things up. Ah, OK. The error then is that you're testing against s1.sibling not s2.sibling. "delete from siblings s1 ... and s1.sibling=175" which means you're not using s2 at all. You might find it clearer with a subquery: DELETE FROM siblings WHERE principal = ( SELECT principal FROM siblings WHERE sibling=234 ); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] delete with self join
On Tue, 2007-04-17 at 10:15 +0100, Richard Huxton wrote: > garry saddington wrote: > > On Tue, 2007-04-17 at 09:21 +0100, Richard Huxton wrote: > >> garry saddington wrote: > >>> I am trying this syntax which is my interpretation of the docs: > >>> > >>> delete from siblings s1 using siblings s2 > >>> WHERE s1.principal = s2.principal > >>> and s1.sibling=175 > >>> > >>> Can anyone tell me where I am going wrong? > >> 1. What's happening - are you getting an error? > > I am getting a syntax error (via psycopg) at or near s1 - perhaps this is a > > psycopg problem? > >> 2. What is the query supposed to do? I can't see why you're not just doing: > >> DELETE FROM siblings WHERE sibling=175; > >> > > > > I am keeping a record of siblings in a school. The user chooses one > > student and there siblings such that id's are entered into a table as > > such: > > TABLE SIBLINGS: > > > > principal sibling > > 809 234 > > 809 785 > > 809 345 > > 809 809 > > > What I am trying to do is to allow the user to correct input mistakes by > > deleting all the siblings of one family at the same time by choosing > > just one of the siblings. I hope this clears things up. > > Ah, OK. The error then is that you're testing against s1.sibling not > s2.sibling. "delete from siblings s1 ... and s1.sibling=175" which means > you're not using s2 at all. > > You might find it clearer with a subquery: > DELETE FROM siblings WHERE principal = ( > SELECT principal FROM siblings WHERE sibling=234 > ); Thanks, can't think why I went the complicated route! Regards Garry ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] seeking PITR archive_command advice
Hello All, I am trying to set up continues archiving of WAL files. I have some doubt about the archive command %p and %f parameter. I am havining all 5/6 WAL files in following directory. usr/local/pgsql/data/pg_xlog My questions are 1) what will be the value for %p parameter will it be /usr/local/pgsql/data/pg_xlog ? is yes it is giving me following error cp: omitting directory `/usr/local/pgsql/data/pg_xlog/ 2) which file name i should give in place of %f parameter as there are 5/6 files already? Thanks in Advance With Regards Ashish Karalkar
Re: [GENERAL] Dynamic DDL
On Apr 16, 6:24 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
> "Ketema" <[EMAIL PROTECTED]> writes:
> > I have an example were I have to build a string in the below manner:
> > values (' || new.tpv_success_id || ',''' || new.order_date || ''',' ||
> > new.tpv_id || ',' || new.ver_code || ',''' || new.agent_name || ''','
> > || new.agent_id || ','
> >|| new.acct_id || ',''' || new.first_name || ''',''' ||
> > new.last_name || ''',''' || new.ssn || ''',''' ||
> > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > || ''',''' || coalesce(new.spouse_ssn,'') || ''',''' etc...
>
> This looks to me like you're simply willfully ignoring the easy path.
> There's nothing there that wouldn't work just as well without EXECUTE,
> viz
>
> values (new.tpv_success_id, new.order_date, new.tpv_id, new.ver_code,
> new.agent_name, new.agent_id, new.acct_id, new.first_name,
> new.last_name, new.ssn, new.dl_number, new.spouse_name, new.spouse_ssn,
> etc...
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
I am sorry for being lazy TomThis part of the dynamix statement
your right is simple and would work.
ITs actuall in the the beginning INSERT INTO _dynamic_table
Its substituting the table name for the insert that does not work and
I was wondering the technical reasons behind that. I had thought
about a rule, but on a rule the table has to already exist in order to
do an instead of insert. My purpose is I am trying to implement
partitioning. I included the full trigger function below. The
function does work as is, my only complaint is that on the columns I
have to coalesce i get '' (null string) inserted instead of an actual
null and this has made me have to make some columns text or varchar
instead of numeric or other data types. (for example cancel_date
should be a date type, but if this comes thorugh as null i have to
coalesce it or the whole string becomes null, and '' is not a valid
date type so I had to make the table column a varchar)
CREATE OR REPLACE FUNCTION frontier.order_details_partitioner()
RETURNS "trigger" AS
$BODY$
declare
_month text;
_year text;
_schema text;
_table text;
_table_exists text;
_sql text;
begin
_month := (select trim(to_char(new.order_date, 'month')));
_year := (select trim(to_char(new.order_date, '')));
_schema := 'frontier';
_table := 'order_details_' || _month || '_' || _year;
_table_exists := (select schemaname || '.' || tablename from
pg_tables
where schemaname = _schema and tablename = _table);
if _table_exists is null then
_sql := 'create table ' || _schema || '.' || _table || '
(CONSTRAINT
"C_partition_rule" CHECK ( trim(to_char(order_date, ''month'')) = '''
|| _month || ''' and trim(to_char(order_date, '''')) = '''
|| _year || ''')) inherits (frontier.order_details);';
raise notice '%', _sql;
execute(_sql);
end if;
_sql := 'insert into ' || _schema || '.' || _table ||
' (tpv_success_id, order_date, tpv_id, ver_code,
agent_name, agent_id, acct_id, first_name, last_name, ssn,
dl_number,
spouse_name, spouse_ssn, day_phone, evening_phone,
svc_address,
svc_city, svc_state, svc_zip, billing_address,
billing_city,
billing_state, billing_zip, order_number, order_status,
provisioned_date,
promotion, products, data_requirement_titles,
data_requirement_values, cancel_date,
cancel_note, issue_notes, has_dish, has_dish_billing_info,
dish_order_number,
dish_install_date, dish_customer_contacted, personnel_id,
call_id,
marketer_division_id, existing_status, app_id) values (' ||
new.tpv_success_id || ',''' || new.order_date || ''',' || new.tpv_id
|| ',' || new.ver_code || ',''' || new.agent_name || ''',' ||
new.agent_id || ','
|| new.acct_id || ',''' || new.first_name || ''',''' ||
new.last_name || ''',''' || new.ssn || ''',''' ||
coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
|| ''',''' || coalesce(new.spouse_ssn,'') || ''','''
|| new.day_phone || ''',''' || coalesce(new.evening_phone,'') ||
''',''' || new.svc_address || ''',''' || new.svc_city || ''',''' ||
new.svc_state || ''',''' || new.svc_zip || ''',''' ||
new.billing_address || ''',''' || new.billing_city || ''','''
|| new.billing_state || ''',''' || new.billing_zip || ''',''' ||
coalesce(new.order_number,'') || ''',''' || new.order_status ||
''',''' || coalesce(new.provisioned_date,'') || ''',''' ||
coalesce(new.promotion,'') || ''',''' || coalesce(new.products,'') ||
''',''' || coalesce(new.data_requirement_titles,'') || ''','''
|| coalesce(new.data_requirement_values,'') || ''',''' ||
coalesce(new.cancel_date,'') || '''
[GENERAL] unique ID across all columns
Hi, I need a unique ID across all columns I create. Is it o.k. to achive this by combining pg_attribute.attnum of a column together with the OID of the table the column is in? While the table's OID should be unique according to the manual, I have found no hints about the uniqueness of attnum -- I removed and added some columns and it seems that already used values are not used again. Felix ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] unique ID across all columns
Felix Kater wrote: > Hi, > > I need a unique ID across all columns I create. Is it o.k. to > achive this by combining pg_attribute.attnum of a column together with > the OID of the table the column is in? > > While the table's OID should be unique according to the manual, I have > found no hints about the uniqueness of attnum -- I removed and added > some columns and it seems that already used values are not used again. Yes, the table OID is unique, and pg_attribute.attnum is unique as well. They are not reused, not even when columns are dropped. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] unique ID across all columns
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Felix Kater wrote: >> I need a unique ID across all columns I create. Is it o.k. to >> achive this by combining pg_attribute.attnum of a column together with >> the OID of the table the column is in? > Yes, the table OID is unique, and pg_attribute.attnum is unique as well. > They are not reused, not even when columns are dropped. The OID could be re-used after the table is dropped, though. It's not clear whether Felix needs uniqueness across time or just at any one instant. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Incremental backups
hi everyone, please any one give any methods to do incremental backups. it is urgent .. help me Regards J Mageshwaran ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail & notify us immediately at [EMAIL PROTECTED] Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! . ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_restore "out of memory" - big problem :(
Never mind this, the file was mangled when sent non-binary over ftp (windows ftp servers apparently still send ascii unless set type i is specified :) Cheers, Palle --On tisdag, april 17, 2007 12.49.46 +0200 Palle Girgensohn <[EMAIL PROTECTED]> wrote: Hi, I get out of memory immediately when running pg_restore on a file from March 28: $ pg_restore pgdump_pp_2007-03-28T03_03_33 pg_restore: [archiver] out of memory ... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dynamic DDL
On Apr 17, 11:19 am, Ketema <[EMAIL PROTECTED]> wrote:
> On Apr 17, 7:35 am, Ketema <[EMAIL PROTECTED]> wrote:
>
>
>
> > On Apr 16, 6:24 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
>
> > > "Ketema" <[EMAIL PROTECTED]> writes:
> > > > I have an example were I have to build a string in the below manner:
> > > > values (' || new.tpv_success_id || ',''' || new.order_date || ''',' ||
> > > > new.tpv_id || ',' || new.ver_code || ',''' || new.agent_name || ''','
> > > > || new.agent_id || ','
> > > >|| new.acct_id || ',''' || new.first_name || ''',''' ||
> > > > new.last_name || ''',''' || new.ssn || ''',''' ||
> > > > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > > > || ''',''' || coalesce(new.spouse_ssn,'') || ''',''' etc...
>
> > > This looks to me like you're simply willfully ignoring the easy path.
> > > There's nothing there that wouldn't work just as well without EXECUTE,
> > > viz
>
> > > values (new.tpv_success_id, new.order_date, new.tpv_id, new.ver_code,
> > > new.agent_name, new.agent_id, new.acct_id, new.first_name,
> > > new.last_name, new.ssn, new.dl_number, new.spouse_name, new.spouse_ssn,
> > > etc...
>
> > > regards, tom lane
>
> > > ---(end of broadcast)---
> > > TIP 6: explain analyze is your friend
>
> > I am sorry for being lazy TomThis part of the dynamix statement
> > your right is simple and would work.
>
> > ITs actuall in the the beginning INSERT INTO _dynamic_table
> > Its substituting the table name for the insert that does not work and
> > I was wondering the technical reasons behind that. I had thought
> > about a rule, but on a rule the table has to already exist in order to
> > do an instead of insert. My purpose is I am trying to implement
> > partitioning. I included the full trigger function below. The
> > function does work as is, my only complaint is that on the columns I
> > have to coalesce i get '' (null string) inserted instead of an actual
> > null and this has made me have to make some columns text or varchar
> > instead of numeric or other data types. (for example cancel_date
> > should be a date type, but if this comes thorugh as null i have to
> > coalesce it or the whole string becomes null, and '' is not a valid
> > date type so I had to make the table column a varchar)
>
> > CREATE OR REPLACE FUNCTION frontier.order_details_partitioner()
> > RETURNS "trigger" AS
> > $BODY$
> > declare
> > _month text;
> > _year text;
> > _schema text;
> > _table text;
> > _table_exists text;
> > _sql text;
> > begin
> > _month := (select trim(to_char(new.order_date, 'month')));
> > _year := (select trim(to_char(new.order_date, '')));
> > _schema := 'frontier';
> > _table := 'order_details_' || _month || '_' || _year;
> > _table_exists := (select schemaname || '.' || tablename from
> > pg_tables
> > where schemaname = _schema and tablename = _table);
> > if _table_exists is null then
> > _sql := 'create table ' || _schema || '.' || _table || '
> > (CONSTRAINT
> > "C_partition_rule" CHECK ( trim(to_char(order_date, ''month'')) = '''
> > || _month || ''' and trim(to_char(order_date, '''')) = '''
> > || _year || ''')) inherits (frontier.order_details);';
> > raise notice '%', _sql;
> > execute(_sql);
> > end if;
> > _sql := 'insert into ' || _schema || '.' || _table ||
> > ' (tpv_success_id, order_date, tpv_id, ver_code,
> > agent_name, agent_id, acct_id, first_name, last_name, ssn,
> > dl_number,
> > spouse_name, spouse_ssn, day_phone, evening_phone,
> > svc_address,
> > svc_city, svc_state, svc_zip, billing_address,
> > billing_city,
> > billing_state, billing_zip, order_number, order_status,
> > provisioned_date,
> > promotion, products, data_requirement_titles,
> > data_requirement_values, cancel_date,
> > cancel_note, issue_notes, has_dish, has_dish_billing_info,
> > dish_order_number,
> > dish_install_date, dish_customer_contacted, personnel_id,
> > call_id,
> >marketer_division_id, existing_status, app_id) values (' ||
> > new.tpv_success_id || ',''' || new.order_date || ''',' || new.tpv_id
> > || ',' || new.ver_code || ',''' || new.agent_name || ''',' ||
> > new.agent_id || ','
> > || new.acct_id || ',''' || new.first_name || ''',''' ||
> > new.last_name || ''',''' || new.ssn || ''',''' ||
> > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > || ''',''' || coalesce(new.spouse_ssn,'') || ''','''
> > || new.day_phone || ''',''' ||
> > coalesce(new.evening_phone,'') ||
> > ''',''' || new.svc_address || ''',''' || new.svc_city || ''',''' ||
> > new.svc_state || ''',''' || new.svc_zip || ''',''' ||
> > new.billing_address |
Re: [GENERAL] Dynamic DDL
On Apr 17, 7:35 am, Ketema <[EMAIL PROTECTED]> wrote:
> On Apr 16, 6:24 pm, [EMAIL PROTECTED] (Tom Lane) wrote:
>
>
>
> > "Ketema" <[EMAIL PROTECTED]> writes:
> > > I have an example were I have to build a string in the below manner:
> > > values (' || new.tpv_success_id || ',''' || new.order_date || ''',' ||
> > > new.tpv_id || ',' || new.ver_code || ',''' || new.agent_name || ''','
> > > || new.agent_id || ','
> > >|| new.acct_id || ',''' || new.first_name || ''',''' ||
> > > new.last_name || ''',''' || new.ssn || ''',''' ||
> > > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > > || ''',''' || coalesce(new.spouse_ssn,'') || ''',''' etc...
>
> > This looks to me like you're simply willfully ignoring the easy path.
> > There's nothing there that wouldn't work just as well without EXECUTE,
> > viz
>
> > values (new.tpv_success_id, new.order_date, new.tpv_id, new.ver_code,
> > new.agent_name, new.agent_id, new.acct_id, new.first_name,
> > new.last_name, new.ssn, new.dl_number, new.spouse_name, new.spouse_ssn,
> > etc...
>
> > regards, tom lane
>
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
>
> I am sorry for being lazy TomThis part of the dynamix statement
> your right is simple and would work.
>
> ITs actuall in the the beginning INSERT INTO _dynamic_table
> Its substituting the table name for the insert that does not work and
> I was wondering the technical reasons behind that. I had thought
> about a rule, but on a rule the table has to already exist in order to
> do an instead of insert. My purpose is I am trying to implement
> partitioning. I included the full trigger function below. The
> function does work as is, my only complaint is that on the columns I
> have to coalesce i get '' (null string) inserted instead of an actual
> null and this has made me have to make some columns text or varchar
> instead of numeric or other data types. (for example cancel_date
> should be a date type, but if this comes thorugh as null i have to
> coalesce it or the whole string becomes null, and '' is not a valid
> date type so I had to make the table column a varchar)
>
> CREATE OR REPLACE FUNCTION frontier.order_details_partitioner()
> RETURNS "trigger" AS
> $BODY$
> declare
> _month text;
> _year text;
> _schema text;
> _table text;
> _table_exists text;
> _sql text;
> begin
> _month := (select trim(to_char(new.order_date, 'month')));
> _year := (select trim(to_char(new.order_date, '')));
> _schema := 'frontier';
> _table := 'order_details_' || _month || '_' || _year;
> _table_exists := (select schemaname || '.' || tablename from
> pg_tables
> where schemaname = _schema and tablename = _table);
> if _table_exists is null then
> _sql := 'create table ' || _schema || '.' || _table || '
> (CONSTRAINT
> "C_partition_rule" CHECK ( trim(to_char(order_date, ''month'')) = '''
> || _month || ''' and trim(to_char(order_date, '''')) = '''
> || _year || ''')) inherits (frontier.order_details);';
> raise notice '%', _sql;
> execute(_sql);
> end if;
> _sql := 'insert into ' || _schema || '.' || _table ||
> ' (tpv_success_id, order_date, tpv_id, ver_code,
> agent_name, agent_id, acct_id, first_name, last_name, ssn,
> dl_number,
> spouse_name, spouse_ssn, day_phone, evening_phone,
> svc_address,
> svc_city, svc_state, svc_zip, billing_address,
> billing_city,
> billing_state, billing_zip, order_number, order_status,
> provisioned_date,
> promotion, products, data_requirement_titles,
> data_requirement_values, cancel_date,
> cancel_note, issue_notes, has_dish, has_dish_billing_info,
> dish_order_number,
> dish_install_date, dish_customer_contacted, personnel_id,
> call_id,
>marketer_division_id, existing_status, app_id) values (' ||
> new.tpv_success_id || ',''' || new.order_date || ''',' || new.tpv_id
> || ',' || new.ver_code || ',''' || new.agent_name || ''',' ||
> new.agent_id || ','
> || new.acct_id || ',''' || new.first_name || ''',''' ||
> new.last_name || ''',''' || new.ssn || ''',''' ||
> coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> || ''',''' || coalesce(new.spouse_ssn,'') || ''','''
> || new.day_phone || ''',''' || coalesce(new.evening_phone,'')
> ||
> ''',''' || new.svc_address || ''',''' || new.svc_city || ''',''' ||
> new.svc_state || ''',''' || new.svc_zip || ''',''' ||
> new.billing_address || ''',''' || new.billing_city || ''','''
> || new.billing_state || ''',''' || new.billing_zip || ''','''
> ||
> coalesce(new.order_number,'') || ''',''' || new.order_status ||
> ''',''' || coalesce(new.provisioned_date,'') |
[GENERAL] rename constraints ?
I didn't see anything in the docs. I saw something in the FAQ suggesting this was in a TODO. Any idea when? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] bug in schema export?
i believe i found a bug psql> create table test_failure ( id integer primary key ); psql> alter table test_failure alter id rename to id_new ; # not relevant, just illustrating the reason how i got to step 2 psql> alter table test_failure_pkey rename to test_failure_id_new_pkey; $ pg_dump --schema-only -ttest_failure -Utest > test.sql the pkey in the schema is added as 'test_failure_pkey' , not 'test_failure_id_new_pkey'; it happens whether or not you use 'alter table' or 'alter index' on the pkey name ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] bug in schema export?
Jonathan Vanasco <[EMAIL PROTECTED]> writes: > i believe i found a bug > psql> create table test_failure ( id integer primary key ); > psql> alter table test_failure alter id rename to id_new ; # not > relevant, just illustrating the reason how i got to step 2 > psql> alter table test_failure_pkey rename to test_failure_id_new_pkey; > $ pg_dump --schema-only -ttest_failure -Utest > test.sql > the pkey in the schema is added as 'test_failure_pkey' , not > 'test_failure_id_new_pkey'; You didn't rename the constraint. If we were going to do anything about this, it'd be to forbid directly renaming indexes that're owned by constraints. However, since we don't currently have a RENAME CONSTRAINT command, the effect of that would be that you couldn't do it at all ... In the meantime you can probably do an UPDATE on pg_constraint if you really care. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgresql 8.1.4 to 8.2.3
On Sat, 2007-04-14 at 22:01 +0200, Anton Melser wrote: > On 14/04/07, Alain Roger <[EMAIL PROTECTED]> wrote: > > After clicking on your link i got "invalid project" page :-( > > and the whole page is empty... > > Ouch Alain... > Try > http://pgfoundry.org/projects/pg-migrator/ > :-) > But ask a single postgres oldskool cat (which I am patently not!) and > you will get exactly the same answer "Do you value your data?". This > is for people who want to tinker, or who simply can't dump/reload. > Until it gets included in postgres core of course! > > > > > > RTFM :-). Between major versions (8.1 -> 8.2) you DO need to dump and > > > > reload. So do that... > > > > > > Actually, this isn't strictly true: > > http://pgfoundry.org/projects/pg- > > > migrator/ So from the docs of pg-migrator: "PostgreSQL version 8.2 changes the layout for values of type INET and CIDR" - thus any tables with columns of those data types would have to be dump/restored. My questions would be a) are there any other data types that have had changes made to their on-disk representation? and b) have there been any changes made to the table infrastucture layout from 8.1.x to 8.2 (that would cause pg-migrator not to work) ? Sven ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] rename constraints ?
Jonathan Vanasco wrote: > I didn't see anything in the docs. > I saw something in the FAQ suggesting this was in a TODO. > > Any idea when? TODO has: o %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME http://archives.postgresql.org/pgsql-patches/2006-02/msg00168.php -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Status of Postgres 8.2.4 and pg_standby
Hello, What is the ETA of postgresql 6.2.4? Also, will pg_standby make it to 8.2.xbranch? Thanks!
Re: [GENERAL] bug in schema export?
On Apr 17, 2007, at 2:51 PM, Tom Lane wrote: Jonathan Vanasco <[EMAIL PROTECTED]> writes: i believe i found a bug psql> create table test_failure ( id integer primary key ); psql> alter table test_failure alter id rename to id_new ; # not relevant, just illustrating the reason how i got to step 2 psql> alter table test_failure_pkey rename to test_failure_id_new_pkey; $ pg_dump --schema-only -ttest_failure -Utest > test.sql the pkey in the schema is added as 'test_failure_pkey' , not 'test_failure_id_new_pkey'; You didn't rename the constraint. If we were going to do anything about this, it'd be to forbid directly renaming indexes that're owned by constraints. However, since we don't currently have a RENAME CONSTRAINT command, the effect of that would be that you couldn't do it at all ... In the meantime you can probably do an UPDATE on pg_constraint if you really care. Extremely annoying. In the example above, \d test_failure will not show a separate constraint named by the old value. it only shows the current name of the index. Either renaming primary keys should be disabled, or that constraint forced to be visible. Under the current system, there is no intuitive way for someone to be aware of an old constraint on a renamed index like this. I discovered this while trying to re-merge 2 databases that were split onto a new bigger cluster and having them complain about conflicting constraints. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Status of Postgres 8.2.4 and pg_standby
CAJ CAJ wrote: > Hello, > > What is the ETA of postgresql 6.2.4? Also, will pg_standby make it to > 8.2.xbranch? pg_standby will not be in 8.2.X. It is a new feature. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] deadlock
I've asked russian postgresql users to proceed my deadlock example. http://sql.ru/forum/actualthread.aspx?tid=418296 Here is the results. OS, version, build PG version, build Status (Person) SUSE Linux 10.1 8.1.4 from official SUSE distribution deadlock between /*1*/ and /*3*/ detected (Nalbat) SUSE Linux 10.1 8.2.3 self-made package /*3*/ waits for /*1*/, deadlock not detected (Nalbat) itanium2(Debian) 8.2.3 deadlock not detected (SergeyK) laptop(Slackware) 8.2.3 deadlock not detected (SergeyK) SUSE 10.0 8.1.4 self-made deadlock detected (SergeyK) Windows 2000 Pro Rus 8.0.4 /*2*/ waits, deadlock not detected (ZemA) Windows 2000 Pro Rus 8.1.3 deadlock detected (ZemA) win xp sp2 8.2.0, i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) /*3*/ waits for /*1*/, deadlock not detected (st_serg) It seems that deadlock rises for Postgresql 8.1 at any operating system. Tom Lane wrote: > Alexey Nalbat <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Your example doesn't deadlock for me ... > > > With default value "deadlock_timeout=1000" error raises in first > > transaction: > > Then there's a typo in your example --- please recheck it. -- Alexey A. Nalbat Price Express http://www.price.ru/ http://www.tyndex.ru/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PITR - "Rewind to snapshot" scheme
On 4/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: Seems overly complicated --- why don't you just shut down the postmaster and take a tarball archive of the PGDATA tree? Then to revert, stop postmaster and untar. Thanks for the tip! cheers martin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Procurando Freelancer PostgreSQL -- Rio de Janeiro
Procurando Freelancer PostgreSQL -- Rio de Janeiro Estamos abrindo a contratação de 1 profissional PostgreSQL: Projeto: Desenvolvimento de Web Aplicação na área de e-commerce. Local: Rio de Janeiro (zona sul) -- teria que trabalhar nosso escritório com a equipe. Agenda: A partir de agora -- TEMPO INTEGRAL até o fim (4-6 semanas) Só candidatos de muito experiência. Teria que reunir com equipe no RIO DE JANEIRO -- zona sul Salário: a combinar Interessados: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Status of Postgres 8.2.4 and pg_standby
On Apr 17, 6:56 pm, [EMAIL PROTECTED] (Bruce Momjian) wrote: > CAJ CAJ wrote: > > Hello, > > > What is the ETA of postgresql 6.2.4? Also, will pg_standby make it to > > 8.2.xbranch? > > pg_standby will not be in 8.2.X. It is a new feature. As I understand it, though, pg_standby as distributed in contrib for 8.3 is designed to be backward compatible with 8.2.x. http://archives.postgresql.org/pgsql-admin/2007-04/msg00069.php I'm currently having good success in testing. -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005
